Database

Query Anything with SQLite

Source Code Accompanies This Article. Download It Now.

Virtual tables are the foundation of some of SQLite's larger features, including full text search.

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

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.

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.

Video

This month's Dr. Dobb's Journal

This month,
Dr. Dobb's Journal is devoted to mobile programming. We introduce you to Apple's new Swift programming language, discuss the perils of being the third-most-popular mobile platform, revisit SQLite on Android
, and much more!