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 application. The same source can be used to build both a static extension and a loadable extension.
Static extensions can be built and linked directly into an application, not unlike
a static C library. Loadable extensions act as external libraries, or
“plug-ins,” to the SQLite engine. If you build your extension as an external
loadable extension, you can load the extension into (almost) any SQLite
environment, making your custom functions and SQL environment available to
sqlite3
or any other database
manager.
In both cases, extensions are a handy way to package a set of related functions into one deployable unit. This is particularly useful if you’re writing an SQL support library that is used by a large number of applications, or if you’re writing an SQLite interface to an existing library. Structuring your code as an extension also provides a standard way to distribute a set of custom functions to other SQLite users. By providing your code as an extension, each developer can choose to build and integrate the extension to best suit their needs, without having to worry about the format or design of the extension code.
Even if you plan on statically linking all of your custom function and feature code directly into your application, there is still great value in packaging your code as an extension. By writing an extension, you don’t lose the ability to build and statically link your extension directly into your application, but you gain the ability to build an external loadable module.
Having your extended environment available as a loadable
module allows you to recreate your application’s SQL environment in the
sqlite3
command-line tool, or any
other general purpose database manager. This opens up the ability to
interactively examine your database files in order to design and test
queries, debug problems, and track down customer support issues. This alone
is a strong reason to consider writing all your custom functions as loadable
extensions, even if you never plan on releasing or distributing the
standalone loadable extensions.
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.
For an SQLite extension to work correctly, every function that interacts with the SQLite library must be in the same C source file as the initialization 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:
int ext_entry_point( sqlite3 *db, char **error,
const sqlite3_api_routines *api )
This is the prototype of an extension entry point. The first parameter is the database connection that is loading this extension. The second parameter can be used to pass back a reference to an error message, should the extension be unable to properly initialize itself. The last parameter is used to convey a block of function pointers to assist in the linking process. We’ll see how this is used in a moment.
This function is called by the SQLite engine when it loads a static or dynamic extension. Typically, this function will create and register any custom functions or other custom extensions with the database connection.
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:
int sqlite3_auto_extension( entry_point_function );
Registers an extension entry point function with the SQLite library. Once this is done, SQLite will automatically call an extension’s entry point function for every database connection that is opened. The only parameter is a function pointer to the entry point.
This function only works with statically linked extensions and does not work with dynamically loadable extensions. This function can be called as many times as is necessary to register as many unique entry points as are required.
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.
$ gcc -o sqlite3trig sqlite3.c shell_trig.c sql_trig.c -lm
> 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.
Platform | File type | Default file extension |
Linux and most Unix | Shared object file | .so |
Mac OS X | Dynamic library | .dylib |
Windows | Dynamically linked library | .DLL |
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.
$ gcc -c sql_trig.c
> 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.
int sqlite3_enable_load_extension( sqlite3 *db, int onoff )
Enables or disables the ability to load dynamic extensions. Loadable extensions are off by default.
The first parameter is the
database connection to set. The second parameter
should be true (nonzero) to enable extensions, or
false (zero) to disable them. This function always
returns SQLITE_OK
.
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.
int sqlite3_load_extension( sqlite3 *db, const char *ext_name,
const char *entry_point, char **error )
Attempts to load a loadable extension and associate it to
the given database connection. The first parameter
is the database connection to associate with this
extension.
The second parameter is the filename of the
extension. The third parameter is the name of the
entry point function. If the entry point name is
NULL, the entry point sqlite3_extension_init
is used. The
fourth parameter is used to pass back an error
message if something goes wrong. This string buffer
should be released with sqlite3_free()
. This last parameter is
optional and can be set to NULL.
This will return either SQLITE_OK
, to indicate
the extension was loaded and the initialization
function was successfully called, or it may return
SQLITE_ERROR
to
indicate something went wrong. If an error condition
is returned, there may or may not be a valid error
string.
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.
18.219.86.155