SQLite: Referential Integrity and Thread Safe PRAGMAs

SQLite 3.6.19 added support for referential integrity (aka foreign key constraints). In current versions of SQLite, the support is optional and not enabled by default, but it can be turned on with the foreign_keys PRAGMA. SQLite recommends that code not depend on the default setting, so I had a choice about whether or not to enable this. Even though it does break backwards compatibility, I decided to enable it to gain better consistency to other databases (pretty much all databases except MySQL’s MyISAM tables).

Since I knew that only versions 3.6.19+ supported the foreign_keys PRAGMA, I decided that it would be best to do as most other adapters do and add a method to determine the version. On most shared adapters, this is the server_version method, but since SQLite is not a client/server database, that didn’t make sense. I decided to go with sqlite_version, since that is the underlying function name used.

Following the other exising PRAGMA related methods in the shared SQLite adapter, I added the foreign_keys and foreign_keys= methods. Shortly after doing so, I noticed that the PRAGMA methods were not thread safe. They basically would pick the next available connection from the connection pool, and execute the necessary PRAGMA SQL on it. However, if there was more than one connection in the pool, only one of the connections would be affected. It’s not possible to execute the PRAGMA SQL on all connections at once, since other threads could be using those connections.

The only way to execute SQL on all connections in a thread-safe manner is to execute the SQL before the connection is added to the pool. I recently added support for users to provide a proc to do this, but in this case I wanted something built into the SQLite adapters so that the PRAGMA methods could be specified with database options, and so the foreign_keys PRAGMA could be enabled by default. That took a little bit of work, but now you can set the PRAGMAs in a thread safe manner using either the native sqlite adapter, the sqlite do subadapter, or the sqlite jdbc subadapter.

Honestly, the thread-safe PRAGMA modifications are of limited benefit, since most Sequel SQLite users don’t modify the PRAGMAs, and most don’t run SQLite with multiple threads. However, since I wanted to enable the foreign_keys PRAGMA right after connecting, most of the work completed would have been required anyway.