Dr. Dobb's Journal - December 2007 - (Page 24)

d12owen_p4ma 10/15/07 8:36 AM Page 24 Core Technology by Michael Owens Query Anything with SQLite Virtual tables are the foundation of powerful features SQLite is an embedded relational database engine implemented in ANSI C. It supports a large Michael is a programmer and the author of the Apress book The Definitive Guide to SQLite. He can be contacted at mikeowens@gmail.com. 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; 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. Initialization 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 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. 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 24 Dr. Dobb’s Journal l www.ddj.com l December 2007 http://www.sqlite.org http://www.ddj.com

Table of Contents Feed for the Digital Edition of Dr. Dobb's Journal - December 2007

Dr. Dobb's Journal - December 2007 Contents Hmmmm Alia Vox Developer Diaries Developer’s Notebook Computer Books: Reading Between the Lines Conversations Query Anything with SQLite XQuery Web Maps with the Google Map API OpenALM and Its Manifesto Transactional Programming Effective Concurrency The Agile Edge Swaine’s Flames

Dr. Dobb's Journal - December 2007

For optimal viewing of this digital publication, please enable JavaScript and then refresh the page. If you would like to try to load the digital publication without using Flash Player detection, please click here.