Channels ▼
RSS

Tools

Query Anything with SQLite

Source Code Accompanies This Article. Download It Now.


Michael is a programmer and the author of the Apress book The Definitive Guide to SQLite. He can be contacted at mikeowens@gmail.com.


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 */
};

Listing One

/* 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;
};

Listing Two

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.


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.
 

Video