Using SQLite

SQLite Extensions

While custom functions are a very powerful feature, they can also introduce undesired dependencies between database files and custom SQLite environments. If a database uses a custom collation in a table definition or a custom function in a view definition, then that database can’t be opened by any application (including sqlite3) that does not have all the proper custom functions defined.

This normally isn’t a big deal for a custom-built application with just a few custom features. You can simply build all the custom code directly into your application. Anytime a database file is created or opened by your application, you can create the appropriate function bindings and make your custom function definitions available for use by the database files.

Where things get tricky is if you need to open your database files in a general purpose application, like the sqlite3 command-line shell, or one of the third-party GUI database managers. Without some way of bringing your custom functions and features with you, your only choice is to splice your custom-feature code into the source of whatever utilities you require, and build site-specific versions that support your SQL environment. That’s not very practical in most cases—especially if the source code to the utility is unavailable.

The solution is to build your custom content as an extension. Extensions come in two flavors: static and loadable (dynamic). The difference is in how the extension is built and linked into your main ...