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

d12owen_p4ma 10/15/07 8:36 AM Page 25 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 “Resource Center,” page 5). The xDestroy() function is essentially the reverse of this xCreate() process, and is called to clean up the resources associated with the virtual table. 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. Using Indexes Without xBestIndex(), the query process is really just a glorified for loop that iterates over the same set of data each time. In this case, it would just be a matter of starting at the root filesystem and recursively descending into each directory until you reach the end. In this case, however, it would be horrible performance-wise to have to search the entire filesystem every time you did a query. What if you only wanted to search your home directory? It would be a complete waste of time for the virtual table to search every other directory outside of it as part of the search. This is where xBestIndex() comes in. In some ways, xBestIndex() may be the most important part of the virtual table implementation. It is the only place where you can really dramatically reduce the size of your search path. And it may strongly influence the rest of your implementation, such as the contents of your vtab and cursor structures. Therefore, this is the area where we want to concentrate. xBestIndex() and xFilter() work together, implementing a specific protocol whereby a set of columns can be identified as indexes. xBestIndex() works at query compile time and identifies the columns to be used as indexes. xFilter() works at runtime and obtains the constraint values for those columns. To start, the xBestIndex() implementation is given by vt_best_index(), whose declaration is: static int vt_best_index(sqlite3_vtab *tab, sqlite3_index_info *p_info) Listing One 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 */ }; Querying xOpen() is called when SQLite opens a cursor on a virtual table and prepares to iterate over it. xOpen() more or less just allocates a cursor object and passes it back. Similarly, when SQLite is done iterating over the table, it uses xClose() to clean up the cursor. All of this so far is intuitive. The query process, however, is more involved. SQLite’s query process consists of three phases: compilation, execution, and finalization. The virtual table module interface addresses all three. In compilation (when you call sqlite3_prepare()), SQLite calls xBestIndex(). This is where you can, to a degree, tell SQLite about certain optimizations that can be made to narrow the size of the result set. In execution, SQLite calls xFilter() to initiate the search, followed by xNext() to iterate over each row of the result set. xNext() is called as a result of calling sqlite3_step(). xColumn() provides the values of each column of the current row. xColumn() is called as a result of calling one of the sqlite3_column_xxx() functions. SQLite uses xRowid() to obtain the primary key value for any given row, and xEof() to tell whether it has reached the end of the result set. Listing Two /* 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 Three 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; } December 2007 l www.ddj.com l Dr. Dobb’s Journal 25 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.