Query Anything with SQLite

SQLite is an embedded relational database engine implemented in ANSI C. It supports a large subset of ANSI SQL and many other features common to relational databases such as triggers, indexes, and auto-increment primary keys. It is known for its small size (256 Kb), reliability, ease-of-use, and elegant design. Also, SQLite's code is public domain and can be used free of charge for any purpose. SQLite (www.sqlite.org) is used in a variety of software, such as Mozilla Firefox, PHP5, Google Gears, and Adobe AIR, as well as embedded devices such as cell phones using Symbian OS.

SQLite has a number of unique features, one of the most interesting of which is virtual tables. With virtual tables, you query not only what is in a database, but what is outside of it as well. For instance, with a little coding, you could use SQLite to search through your filesystem and issue queries such as:

create virtual table fs using filesystem;
SELECT 
  prot, uid, gid, size/(1024*1024)      as 'size (Mb)', 
  dev, path || '/' || name as file from fs 
WHERE 
  path = '/usr/lib'
  AND name like '%.so'"
  AND size > 1024*1024*4 
ORDER BY size desc;  

You could also write a virtual table to read your log files or filter SNMP data. Basically, anything your program can parse, read, or grab is fair game.

In this article, I present a virtual table that interfaces with the filesystem. It uses the Apache Portable Runtime, which enables it to work with multiple operating systems. Because SQLite is portable, it only makes sense to try to keep virtual tables portable as well, and the APR is helpful in this regard.

The API

You implement a virtual table using three structures. The first is the module structure, which is an array of function pointers. These are callbacks you implement to let SQLite operate on your table as if it were a native table. Some of these functions are mandatory, many are optional. For example, if you don't need to support transactions, you don't have to implement the related functions. You just set the respective callbacks to null.

Listing One is the callback structure for our virtual table. The other two structures are the vtable and cursor structures (Listing Two).

static sqlite3_module example_module = 
{
    0,              /* iVersion */
    vt_create,      /* xCreate       - create a vtable */
    vt_connect,     /* xConnect      - associate a vtable with a connection */
    vt_best_index,  /* xBestIndex    - best index */
    vt_disconnect,  /* xDisconnect   - disassociate a vtable with a connection */
    vt_destroy,     /* xDestroy      - destroy a vtable */
    vt_open,        /* xOpen         - open a cursor */
    vt_close,       /* xClose        - close a cursor */
    vt_filter,      /* xFilter       - configure scan constraints */
    vt_next,        /* xNext         - advance a cursor */
    vt_eof,         /* xEof          - indicate end of result set*/
    vt_column,      /* xColumn       - read data */
    vt_rowid,       /* xRowid        - read data */
    NULL,           /* xUpdate       - write data */
    NULL,           /* xBegin        - begin transaction */
    NULL,           /* xSync         - sync transaction */
    NULL,           /* xCommit       - commit transaction */
    NULL,           /* xRollback     - rollback transaction */
    NULL,           /* xFindFunction - function overloading */
};

/* vtab: represents a virtual table. */
struct vtab
{
    sqlite3_vtab base;
    sqlite3 *db;
};
/* vtab: represents a singe cursor with which it iterate over the virtual table. */
struct vtab_cursor
{
    sqlite3_vtab_cursor base;
};

Because all of the callback functions use pointers to reference these structures, you are free to extend them. SQLite only needs the sqlite_vtab and sqlite3_vtab_cursor portions of the structures to operate.