The virtual table API is one of the more advanced SQLite APIs. In specific, it does very little hand-holding and will often fool those that make assumptions. The functions you need to write are often required to do a very specific set of operations. If you fail to do any one of those, or forget to initialize a data structure field, the result might very well be a bus error or segmentation fault.
That street goes two ways, however. While the SQLite core expects you to do your job, it does a very good job of always doing its job in a very predictable and documented way. Most of this code operates fairly deeply in the SQLite core, and SQLite does a solid job of protecting your code against odd user behavior. For example, none of the example code checks for NULL parameter values, as you can be sure SQLite will never allow a NULL database pointer (or some equally critical parameter) to be passed into your function.
Implementing a virtual table module is a bit like developing an aggregate function, only a lot more complex. You must write a series of functions that, taken together, define the behavior of the module. This block of functions is then registered under a module name.
int sqlite3_create_module( sqlite3 *db, const char *name,
const sqlite3_module *module, void *udp )
Creates and registers a virtual table module with a database connection. The second parameter is the name of the module. The third parameter is a block of function pointers that implements the virtual table. This pointer must remain valid until the SQLite library is shut down. The final parameter is a generic user-data pointer that is passed to some of the module functions.
int sqlite3_create_module_v2( sqlite3 *db, const char *name,
const sqlite3_module *p, void *udp,
destroy_callback )
The v2 version of this function is identical to the original
function, except for an additional fifth parameter. This
version adds a destroy callback of the form void callback(void *udp)
.
This function can be used to release or otherwise clean up
the user-data pointer, and is called when the entire
module is unloaded. This is done when the database is shut
down, or when a new module with the same name is
registered in place of this one. The destroy function
pointer is optional, and can be set to NULL.
Function pointers are passed in through an sqlite3_module
structure. The main reason
for this is that there are nearly 20 functions that define a virtual table.
All but a few of those functions are mandatory.
A module defines a specific type of virtual table. Once
a module has been successfully registered, an actual table instance of that
type must be created using the SQL command
CREATE VIRTUAL TABLE
. A single database
may have multiple instances of the same type of virtual table. A single
database may also have different types of virtual tables, just as long as
all the modules are properly registered.
The syntax for the CREATE
VIRTUAL TABLE
command looks something like this:
CREATE VIRTUAL TABLEtable_name
USINGmodule_name
(arg1
,arg2
, ... )
A virtual table is named, just like any other table. To define the table, you must provide the module name and any arguments the module requires. The argument block is optional, and the exact meaning of the arguments is up to the individual module implementations. It is the responsibility of the module to define the actual structure (column names and types) of the table. The arguments have no predefined structure and do not need to be valid SQL expressions or column definitions. Each argument is passed as a literal text value to the module, with only the leading and trailing whitespace trimmed. Everything else, including whitespace within the argument, is passed as a single text value.
Here is a quick overview of the different functions that
are defined by
sqlite3_module
structure. When we look at
the example modules, we’ll go back through these one at a time in much more
detail. The module functions are divided up into three rough groups. The
first set of functions operate on table instances. The second set includes
the functions that scan a table and return data values. The last group of
functions deals with implementing transaction control. To implement a
virtual table module, you will need to write a C function that performs each
of these tasks.
Functions that deal with individual table instances include:
xCreate()
Required.
Called when a virtual table instance is first created
with the CREATE
VIRTUAL
TABLE
command.
xConnect()
Required, but frequently the same as
xCreate()
. Very
similar to xCreate()
,
this is called when a database with an existing
virtual table instance is loaded. Called once for each
table instance.
xDisconnect()
Required. Called when a database containing a virtual table instance is detached or closed. Called once for each table instance.
xDestroy()
Required,
but frequently the same as xDisconnect()
. Very similar to xDisconnect()
,
this is called when a virtual table instance
is destroyed with the DROP
TABLE
command.
xBestIndex()
Required. Called, sometimes several times, when the database engine is preparing an SQL statement that involves a virtual table. This function is used to determine how to best optimize searches and queries made against the table. This information helps the optimizer understand how to get the best performance out of the table.
xUpdate()
Optional. Called to modify (INSERT
, UPDATE
, or DELETE
) a table row. If this function is
not defined, the virtual table will be read-only.
xFindFunction()
Optional. Called when preparing an SQL statement that uses
virtual table values as parameters to an SQL function.
This function allows the module to override the default
implementation of any SQL function. This is typically used
in conjunction with the SQL functions like()
or match()
to define
module-specific versions of these functions (and, from
that, module-specific versions of the LIKE
and MATCH
SQL
expressions).
xRename()
Required. Called when a virtual table is renamed using the
ALTER
TABLE...RENAME
command.
The second group of functions deals with processing table scans. These functions operate on a table cursor, which holds all of the state information required to perform a table scan. As the database engine scans a table and steps through each individual row, the cursor is responsible for keeping track of which row is being processed.
A single virtual table instance may be involved in more than one table scan at a time. To function correctly, the module must keep all state information in the table cursor, and cannot use user-data pointers or static variables. Consider, for example, a virtual table instance that is self-joined, and must have more than one scan active at the same time.
xOpen()
xClose()
xFilter()
Required. Called to initiate a table scan and provide information
about any specific conditions put on this particular table
scan. Conditions typically come from WHERE
constraints on the
query. The xFilter()
function is designed to work in conjunction with xBestIndex()
to allow a
virtual table to pre-filter as many rows as it can. After
readying the module for a table scan, xFilter()
should also look
up the first row. This may be called more than once
between xOpen()
and
xClose()
.
xNext()
xEof()
Required. Called to see if a table cursor has reached the end of
the table or not. EOF is traditional shorthand for
end-of-file. This function is
always called right after a call to xFilter()
or xNext()
.
xRowid()
Required. Called to extract the virtual ROWID
of the current row.
xColumn()
Required. Called to extract a column value for the current row. Normally called multiple times per row.
Finally, we have the transaction control functions. These allow external data sources to take part in the transaction control process, and include:
If this sounds confusing, don’t give up just yet. As we start to work through the code examples, we will go back through each function and take a closer look at all the details.
You may be surprised to see that the transactional functions are optional. The reason for this is that internal modules don’t need or require their own transactional control. When an internal module modifies any standard table in response to a virtual table operation, the normal transactional engine is already protecting those changes and updates. Additionally, external read-only modules don’t require transactional control because they aren’t driving any modifications to their external data sources. The only type of module that really needs to implement transactional control are those that provide transaction-safe read/write support to external data sources.
13.58.247.31