Dr. Dobb's is part of the Informa Tech Division of Informa PLC

This site is operated by a business or businesses owned by Informa PLC and all copyright resides with them. Informa PLC's registered office is 5 Howick Place, London SW1P 1WG. Registered in England and Wales. Number 8860726.

Channels ▼


Query Anything with SQLite

Source Code Accompanies This Article. Download It Now.


The first callback function to implement is xCreate(), which creates the virtual table. This is called once—specifically, when the first database connection declares the virtual table via the CREATE VIRTUAL TABLE statement.

xCreate()'s job is to set up the virtual table environment and initialize any necessary resources. The implementation (vt_create()) is in Listing Three. It allocates a vtab struct and passes it back to SQLite using the vtable pointer reference it passes in (pp_vt). SQLite also passes in a reference to the database connection, so you store a reference to it in your vtab structure. In so doing, SQLite has a reference to a new vtab struct, which in turn has a reference to the database connection.

In addition to allocating a vtab structure, vt_create() also declares the table using sqlite3_declare_table(), passing SQLite the SQL (DDL) defining the virtual table's schema. Here, the SQL is contained in the global variable DDL in Listing Four (available online; see www.ddj.com/code/).

The xDestroy() function is essentially the reverse of this xCreate() process, and is called to clean up the resources associated with the virtual table.

static int vt_create( sqlite3 *db, void *pAux, int argc, const char *const*argv,
                      sqlite3_vtab **pp_vt, char **pzErr )
    int rc = SQLITE_OK;
    vtab* p_vt;
    /* Allocate the sqlite3_vtab/vtab structure itself */
    p_vt = (vtab*)sqlite3_malloc(sizeof(*p_vt));

    if(p_vt == NULL) {
        return SQLITE_NOMEM;
    p_vt->db = db;
    apr_pool_create(&p_vt->pool, NULL);
    /* Declare the vtable's structure */
    rc = sqlite3_declare_vtab(db, ddl);
    /* Success. Set *pp_vt and return */
    *pp_vt = &p_vt->base;
    return SQLITE_OK;
Listing Three

The xConnect() and xDisconnect() functions are similar to xCreate()/xDestroy() with one exception. xCreate() and xDestroy() are designed to do one-time initialization/destruction of the virtual table, while xConnect() and xDisconnect() are intended to connect/disconnect a database with an existing virtual table. The distinction only matters if you have shared resource(s) associated with the virtual table that must be available to all database connections. If you don't have any, then these functions are essentially identical.

Related Reading

More Insights

Currently we allow the following HTML tags in comments:

Single tags

These tags can be used alone and don't need an ending tag.

<br> Defines a single line break

<hr> Defines a horizontal line

Matching tags

These require an ending tag - e.g. <i>italic text</i>

<a> Defines an anchor

<b> Defines bold text

<big> Defines big text

<blockquote> Defines a long quotation

<caption> Defines a table caption

<cite> Defines a citation

<code> Defines computer code text

<em> Defines emphasized text

<fieldset> Defines a border around elements in a form

<h1> This is heading 1

<h2> This is heading 2

<h3> This is heading 3

<h4> This is heading 4

<h5> This is heading 5

<h6> This is heading 6

<i> Defines italic text

<p> Defines a paragraph

<pre> Defines preformatted text

<q> Defines a short quotation

<samp> Defines sample computer code text

<small> Defines small text

<span> Defines a section in a document

<s> Defines strikethrough text

<strike> Defines strikethrough text

<strong> Defines strong text

<sub> Defines subscripted text

<sup> Defines superscripted text

<u> Defines underlined text

Dr. Dobb's encourages readers to engage in spirited, healthy debate, including taking us to task. However, Dr. Dobb's moderates all comments posted to our site, and reserves the right to modify or remove any content that it determines to be derogatory, offensive, inflammatory, vulgar, irrelevant/off-topic, racist or obvious marketing or spam. Dr. Dobb's further reserves the right to disable the profile of any commenter participating in said activities.

Disqus Tips To upload an avatar photo, first complete your Disqus profile. | View the list of supported HTML tags you can use to style comments. | Please read our commenting policy.