Module API

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 TABLE table_name USING module_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.

Cursor functions include:

xOpen()

Required. Called to create and initialize a table cursor.

xClose()

Required. Called to shut down and release a table cursor.

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()

Required. Called to advance a table cursor to the next row.

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:

xBegin()

Optional. Called when a transaction is started.

xSync()

Optional. Called to start committing a transaction.

xCommit()

Optional. Called to finalize a database transaction.

xRollback()

Optional. Called to roll back a database transaction.

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.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
13.58.247.31