Table of Contents for

Using SQLite

Extensions are nothing more than a style of packaging your code. The SQLite API calls used to register and create custom function handlers, aggregate handlers, collations, or other custom features are completely unchanged in an extension. The only difference is in the initialization process that creates and binds your custom C functions to a database connection. The build process is also slightly different, depending if you want to build a statically linked extension or a dynamically loadable extension, but both types of extensions can be built from the same source code.

Extension architecture focuses on getting dynamically loadable extensions to operate correctly across multiple platforms. The biggest challenge for the dynamic extension architecture is making sure the loadable extension is provided access to the SQLite API. Without getting into a lot of details about how the runtime linker works on different operating systems, the basic issue is that code compiled into an extension and loaded at runtime may not be able to resolve link dependencies from the loadable extension back into the application where the SQLite library sits.

To avoid this problem, when an extension is initialized it is passed a large data structure that contains a C function pointer to every function in the SQLite API. Rather than calling the SQLite functions directly, an extension will dereference the required function pointer and use that. This provides a means to resolve any calls into the SQLite library without depending on the linker. While this isn’t fully required for a static extension, the mechanism works equally well with both static and dynamic extensions.

Thankfully, the details of how this big data structure works are all well hidden from the developer by using an alternate header file and a few preprocessor macros. These macros completely hide the whole linker and function pointer issue, but with one limitation: all the extension code that makes calls into the SQLite API must be in the same file, along with the extension initialization function. That code may call out to other files and other libraries, just as long as that “other code” doesn’t make any direct calls to any sqlite3_xxx() function.

In practice, this is rarely a significant limitation. Keeping your custom SQLite extensions in their own files, out of your application code, is a natural way to organize your code. Most SQLite extensions are a few hundred lines or less, especially if they are simply acting as a glue layer between SQLite and some other library. This can make them large, but usually not so large they become unmanageable as a single file.

To write an extension, we need to use the extension header file. Rather than using the more common sqlite.h file, an extension uses the sqlite3ext.h file:

#include "sqlite3ext.h"
SQLITE_EXTENSION_INIT1;  /* required by SQLite extension header */

The SQLite extension header defines two macros. The first of these is SQLITE_EXTENSION_INIT1, and should be referenced at the top of the C file that holds the extension source. This macro defines a file-scoped variable that holds a pointer to the large API structure.

Each extension needs to define an entry point. This acts as an initialization function for the extension. The entry point function looks like this:

The entry point has two main jobs. The first job is to finish the initialization process by calling the second extension macro. This should be done as the first bit of code in the entry point (the macro expands into a line of code, so if you’re working in pure C you will need to put any function-scope variables before the initialization macro). It must be done before any sqlite3_xxx() calls are made, or the application will crash:

int ext_init( sqlite3 *db, char **error, const sqlite3_api_routines *api )
{
    /* local variable definitions */

    SQLITE_EXTENSION_INIT2(api);
    /* ... */
}

This macro is the only time you should need to directly reference the api parameter. Once the entry function has finished the extension API initialization, it can proceed with its second main job, which is registering any and all custom functions or features provided by this extension.

Unlike a lot of functions, the name of the entry point function is somewhat important. When a dynamic extension is loaded, SQLite needs to ask the runtime linker to return a function pointer to the entry point function. In order to do this, the name of the entry point needs to be known.

As we’ll see when we look at the dynamic load functions, by default SQLite will look for an entry point named sqlite3_extension_init(). In theory, this is a good function name to use, since it will allow a dynamic extension to be loaded even if all you know is the filename.

Although the same application can load multiple dynamic extensions, even if they have the same entry point name, that is not true about statically linked extensions. If you need to statically link more than one extension into your application, the entry points must have unique names or the linker won’t be able to properly link in the extensions.

As a result, it is customary to name the entry point something that is unique to the extension, but fairly easy to document and remember. The entry point often shares the same name as the extension itself, possibly with an _init suffix. The example extension we’ll be looking at is named sql_trig, so good choices for the entry point would be sql_trig() or sql_trig_init().

For our example extension, we will be creating a pair of SQL functions that expose some simple trigonometric functions from the standard C math library. Since this is just an example, we’ll only be creating two SQL functions, but you could use the same basic technique to build SQL functions for nearly every function in the standard math library.

The first half of our sql_trig.c source file contains the two functions we will be defining in our example extension. The functions themselves are fairly simple, extracting one double-precision floating-point number, converting from degrees to radians, and then returning the result from the math library. I’ve also shown the top of the file with the required #include statements and initialization macros:

/* sql_trig.c */

#include "sqlite3ext.h"
SQLITE_EXTENSION_INIT1;

#include <stdlib.h>

/* this bit is required to get M_PI out of MS headers */
#if defined( _WIN32 )
#define _USE_MATH_DEFINES
#endif /* _WIN32 */

#include <math.h>

static void sql_trig_sin( sqlite3_context *ctx, int num_values, sqlite3_value **values )
{
    double a = sqlite3_value_double( values[0] );
    a = ( a / 180.0 ) * M_PI;   /* convert from degrees to radians */
    sqlite3_result_double( ctx, sin( a ) );
}

static void sql_trig_cos( sqlite3_context *ctx, int num_values, sqlite3_value **values )
{
    double a = sqlite3_value_double( values[0] );
    a = ( a / 180.0 ) * M_PI;   /* convert from degrees to radians */
    sqlite3_result_double( ctx, cos( a ) );
}

You’ll notice these are declared as static functions. Making them static hides them from the linker, eliminating any possible name conflicts between this extension and other extensions. As long as the extension entry point is in the same file (which, as we’ve already discussed, is required for other reasons), the entry point will still be able to properly register these functions. Declaring these functions static is not strictly required, but doing so is a good practice and can eliminate linking conflicts.

We then need to define our entry point. Here is the second part of the sql_trig.c file:

int sql_trig_init( sqlite3 *db, char **error, const sqlite3_api_routines *api )
{
    SQLITE_EXTENSION_INIT2(api);

    sqlite3_create_function( db, "sin", 1,
            SQLITE_UTF8, NULL, sql_sin, NULL, NULL );
    sqlite3_create_function( db, "cos", 1,
            SQLITE_UTF8, NULL, sql_cos, NULL, NULL );

    return SQLITE_OK;
}

This entry point function should not be declared static. Both the static linker (in the case of a static extension) and the dynamic linker (in the case of a loadable extension) need to be able to find the entry point function for the extension to work correctly. Making the function static would hide the function from the linker.

These two blocks of code make up our entire sql_trig.c source file. Let’s look at how to build that file as either a static extension or a dynamically loadable extension.

To statically link an extension into an application, you can simply build the extension source file into the application, just like any other .c file. If your application code was contained in the file application.c, you could build and link our example sql_trig extension using the commands shown here.

In the case of most Linux, Unix, and Mac OS X systems, our trig example requires that we explicitly link in the math library (libm). In some cases, the standard C library (libc) is also required. Windows includes the math functions in the standard runtime libraries, so linking in the math library is not required.

Unix and Mac OS X systems (with math lib):

$ gcc -o application application.c sqlite3.c sql_trig.c -lm

Windows systems, using the Visual Studio compiler:

> cl /Feapplication application.c sqlite3.c sql_trig.c

These commands should produce an executable named application (or application.exe under Windows).

Just linking the code together doesn’t magically make it integrate into SQLite. You still need to make SQLite aware of the extension so that the SQLite library can initialize the extension correctly:

This function is called by an application, typically right after calling sqlite3_initialize(). Once an extension’s entry point is registered with the SQLite library, SQLite will initialize the extension for each and every database connection it opens or creates. This effectively makes your extension available to all database connections managed by your application.

The only odd thing about sqlite3_auto_extension() is the declaration of the entry point function. The auto extension API call declares the function pointer to have a type of void entry_point( void ). That defines a function that takes no parameters and returns no value. As we’ve already seen, the actual extension entry point has a slightly more complex prototype.

The code that actually calls the extension first casts the provided function pointer to the correct type, so the fact that the types don’t match is only an issue for setting the pointer. Extensions typically don’t have header files, since the entry point function would typically be the only thing in a header. To get everything working, you can either provide the proper prototype for the entry point and then cast back to what the API is expecting, or you can simply declare the function prototype incorrectly, and let the linker match things up. Pure C doesn’t type-check function parameters when it links, so this will work, even if it isn’t the most elegant approach.

Here’s what the proper prototype with a cast might look like in our application code:

/* declare the (correct) function prototype manually  */
int sql_trig_init( sqlite3 *db, char **error, const sqlite3_api_routines *api );

/* ... */
    sqlite3_auto_extension( (void(*)(void))sql_trig_init ); /* needs cast */
/* ... */

Or, if you’re working in pure C, you can just declare a different prototype:

/* declare the (wrong) function prototype manually */
void sql_trig_init(void);

/* ... */
    sqlite3_auto_extension( sql_trig_init );
/* ... */

As long as the actual sql_trig_init() function is in a different file, this will compile and link correctly, resulting in the desired behavior.

If you want a quick practical example of how to add a static extension to an existing application, we can add our sql_trig extension to the sqlite3 shell with a minimum number of changes. We’ll need our sql_trig.c file, which contains the two SQL trig functions, plus the sql_trig_init() entry function. We’ll also need the shell.c source code for the sqlite3 command-line application.

First, we need to add some initialization hooks into the sqlite3 source. Make a copy of the shell.c file as shell_trig.c. Open your new copy and search for the phrase “int main(” to quickly locate the starting point of the application. Right before the main function, in global file scope, add a prototype for our sql_trig_init() entry point:

/* ... */
void sql_trig_init(void);  /* insert this line */

int main(int argc, char **argv){
/* ... */

Then, inside the existing main() function, search for a call to “open_db(” to find a good spot to insert our code. Right before the small block of code (and comments) that contains the first call to open_db(), add this line:

sqlite3_auto_extension( sql_trig_init );

With those two edits, you can save and close the shell_trig.c file. We can then recompile our modified shell_trig.c source into a custom sqlite3trig utility that has our extension built into it.

Unix/Linux and Mac OS X:

$ gcc -o sqlite3trig sqlite3.c shell_trig.c sql_trig.c -lm

Windows:

> cl /Fesqlite3trig sqlite3.c shell_trig.c sql_trig.c

Our new sqlite3trig application now has our extension built directly into it, and our functions are accessible from any database that is opened with our modified sqlite3trig utility:

$ ./sqlite3trig
SQLite version 3.X.XX
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> SELECT sin( 30 );
0.5
sqlite> SELECT cos( 30 );
0.866025403784439

Although we had to modify the source, the modifications were fairly small. While we needed to modify and recompile the main application (sqlite3trig, in this case) to integrate the extension, you can see how easy it would be to add additional extensions.

Dynamic extensions are loaded on demand. An application can be built without any knowledge or understanding of a specific extension, but can still load it when requested to do so. This means you can add new extensions without having to rebuild or recompile an application.

Loadable extension files are basically shared libraries in whatever format is appropriate for the platform. Loadable extensions package compiled code into a format that the operating system can load and link into your application at runtime. Table 9-1 provides a summary of the appropriate file formats on different platforms.

Loadable extensions are not supported on all platforms. Loadable extensions depend on the operating system having a well-supported runtime linker, and not all handheld and embedded devices offer this level of support. In general, if a platform supports some type of dynamic or shared library for application use, there is a reasonable chance the loadable extension interface will be available. If the platform does not support dynamic or shared libraries, you may be limited to statically linked extensions. However, in most embedded environments this isn’t a major limitation.

Although the file formats and extensions are platform dependent, it is not uncommon to pick a custom file extension that is used across all your supported platforms. Using a common file extension is not required, but it can keep the cross-platform C or SQL code that is responsible for loading the extensions a bit simpler. Like database files, there is no official extension for an SQLite loadable extension, but .sqlite3ext is sometimes used. That’s what I’ll use in our examples.

Generally, building a loadable extension is just like building a dynamic or shared library. The code must first be compiled into an object file (a .o or .obj file) and that file must be packaged into a shared or dynamic library. The process of building the object file is exactly the same for both static and dynamic libraries. You can build the object file directly with one of these commands.

Mac OS X and Unix/Linux:

$ gcc -c sql_trig.c

Windows:

> cl /c sql_trig.c

Once you have the object file, that needs to be converted into a dynamic or shared library using the linker. The commands for that are a bit more platform dependent.

First, the Unix and Linux command, which builds a shared object file and links in the standard math library:

$ ld -shared -o sql_trig.sqlite3ext sql_trig.o -lm

Mac OS X, which uses dynamic libraries, rather than shared object files:

$ ld -dylib -o sql_trig.sqlite3ext sql_trig.o -lm

And finally, Windows, where we need to build a DLL file. In this case, we need to tell the linker which symbols we want exported. For an extension, only the entry point needs to be exported, so we just include that on the command-line:

> link /dll /out:sql_trig.sqlite3ext /export:sql_trig_init sql_trig.obj

You can test out your dynamic extension in sqlite3 using the .load command. The command takes two parameters. The first is the filename of your loadable extension, and the second is the name of the entry point function:

$ sqlite3
SQLite version 3.X.XX
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> SELECT sin( 60 );
Error: no such function: sin
sqlite> .load sql_trig.sqlite3ext sql_trig_init
sqlite> SELECT sin( 60 );
0.866025403784439

As you can see, when we first start sqlite3, it has no awareness of our extension or the SQL functions it contains. The .load command is used to dynamically load the extension. Once loaded, our custom trig functions are available without any need to recompile or rebuild the sqlite3 utility.

There are some minor security concerns associated with loadable extensions. Because an extension might contain just about any code, a loadable extension might be used to override application values for the SQLite environment. In specific, if an application uses an authorization function to protect against certain types of queries or modifications, a loadable extension could clear the authorization callback function, eliminating any authorization step (see sqlite3_set_authorizer() in Appendix G for more details).

To prevent this, and other possible issues, an application must explicitly enable the ability to load external extensions. This has to be done each time a database connection is established.

Most general purpose applications, including the sqlite3 shell, automatically enable loadable extensions for every database they open. If your application will support loadable extensions, you will need to enable this as well. Extension loading needs to be enabled for each database connection, every time the database connection is opened.

There are two ways to load an extension. One is through a C API call, and one is through an SQL function that calls down into the same code as the C API function. In both cases, you provide a filename and, optionally, the name of the entry point function.

This function is typically called as soon as a database connection is opened, before any statements are prepared. Although it is legal to call sqlite3_load_extension() at any time, any API calls made by the extension entry point and initialization function are subject to standard restrictions. In specific, that means any calls to sqlite3_create_function() made by the extension entry point function will fail to redefine or delete a function if there are any executing SQL statements.

The other way to load a loadable extension is with the built-in SQL function load_extension().

This function is similar to the C sqlite3_load_extension() call, with one major limitation. Because this is an SQL function, when it is called there will be, by definition, an SQL statement executing when the extension is loaded. That means that any extension loaded with the load_extension() SQL function will be completely unable to redefine or delete a custom function, including the specialized set of like() functions.

To avoid this problem while testing your loadable extensions in the sqlite3 shell, use the .load command. This provides direct access to the C API call, allowing you to get around the limitations in the SQL function. See .load in Appendix B for more details.

No matter which mechanism you use to load a loadable extension, you’ll need to do it for each database connection your application opens. Unlike the sqlite3_auto_extension() function, there is no automatic way to import a set of loadable extensions for each and every database.

The only way to completely unload a loadable extension is to close the database connection.

Although most extensions have only a single entry point function, there is nothing that says this must be true. It is perfectly acceptable to define multiple entry points in a single extension—just make sure they each call SQLITE_EXTENSION_INIT2().

Multiple entry points can be used to control the number of imported functions. For example, if you have a very large extension that defines a significant number of functions in several different subcategories, you would likely define one main entry point that imports every extension, aggregation, collation, and other features with one call. You could also define an entry point for each subcategory of functionality, or one entry point for all the functions, one for all the collations, etc. You might also define one entry point to bind UTF-8 functions, and another for UTF-16.

No matter how you want to mix and match things, this allows an extension user to import just the functionality they need. There is no danger in redefining a function from two different entry points (assuming all of the entry points register similar functions in similar ways), so different entry points can register overlapping sets of functions without concern.

Even if your extension is not large and doesn’t really justify multiple entry points, a second one can still be handy. Some extensions define a “clear” entry point, for example, sql_trig_clear(). This would typically be very similar to the _init() entry point function, but rather than binding all the function pointers into a database connection, it would bind all NULL pointers. This effectively “unloads” the extension from the SQL environment—or at least removes all the functions it created. The extension file may still be in memory, but the SQL functions are no longer available to that database connection. The only thing to remember about a _clear() entry point is that it cannot be called while an SQL statement is being executed, because of the redefine/delete rules for functions like sqlite3_create_function(). This also means you cannot call a _clear() entry point using the SQL function load_extension().

Custom functions, aggregations, and collations can be an extremely powerful means to extend and expand the SQL environment to fit your own needs and designs. Extensions make for a relatively painless way to modularize and compartmentalize those custom features. This makes the extension code easier to test, support, and distribute.

In the next chapter, we’ll look at one of the more powerful customizations in SQLite: virtual tables. Virtual tables allow a developer to merge the SQLite environment to just about any data source. Like other custom features, the easiest way to write a virtual table is to use an extension.