Dr. Dobb's is part of the Informa Tech Division of Informa PLC

This site is operated by a business or businesses owned by Informa PLC and all copyright resides with them. Informa PLC's registered office is 5 Howick Place, London SW1P 1WG. Registered in England and Wales. Number 8860726.

Channels ▼


Query Anything with SQLite

Source Code Accompanies This Article. Download It Now.

The match() Function

Here's where things get tricky. Consider the query:

SELECT * FROM fs WHERE    path='/var/log' OR      path='/usr/lib';

What happens here? Well, you might think that you simply intercept the path column in xBestIndex() and pass it on to xFilter(), but as it turns out SQLite never calls xBestIndex() here.

Why? Because xBestIndex() is subject to SQLite's index conditions just like any other query. There are times when SQLite will use indexes, and times when it will use sequential scans. When you use OR for multiple constraints on a given column, SQLite uses a sequential scan. That puts us right back into the situation where we have to search the whole filesystem again, instead of just two directories.

Fortunately, there is a way around this—the match() function. If you notice , one of the defined relational operators is SQLITE_INDEX_CONSTRAINT_MATCH. So for example, if we recast the aforementioned query to

SELECT * from fs WHERE path    match('/var/log', '/usr/lib');

then SQLite calls xBestIndex() with the path column, and specifies that the SQLITE_INDEX_CONSTRAINT_MATCH operator was used. This is our solution.

To do this, however, you must register a function to handle the match() SQL function. By default it is unimplemented and will trigger an error if you try to use it. To register it, we use the xFindFunction() callback, which we implement as vt_find_function() (Listing Seven, also available online).

When a SQL function uses a column from a virtual table, SQLite calls xFindFunction() to give the virtual table an opportunity to overload the function. The first three parameters are inputs: the virtual table, the number of arguments to the function, and the name of the function. To overload the function, xFindFunction() passes a function pointer back via *pxFunc (and any user data via *ppArg) and returns 1. If it doesn't want to overload it, then xFindFunction() simply returns 0.

In this case, you just want SQLite to see match() as a valid function so it will use xBestIndex(). You don't really care what match() does. In fact, our implementation of match always returns true (meaning everything matches no matter what). What matters here is that you get the values of match() into xFilter() so you can narrow your search path. So what you look for in xBestIndex() is any column whose ordinal is 1 (p_info->aConstraint[x].iColumn == 1) and whose relational operators are equality (SQLITE_INDEX_CONSTRAINT_EQ) or match (SQLITE_INDEX_CONSTRAINT_MATCH). If you see that, then you pass that column on to xFilter().

So xFilter() simply looks for a string containing one or more paths in argv. In the example, argc is 1 and argv will be:

argv[0] = "'/var/log','/usr/lib'"

It is your responsibility to interpret the argument passed to match(). Thus, you have to parse the string looking for paths. In this implementation, I use the convention that paths are separated by commas. I parse everything between commas and build a list of directories to search. This is then stored in the cursor structure. The search works just as before, except instead of recursively searching one directory, I search multiple directories. The search logic is the same, I just repeat it for every directory passed into match().

Again, for each row in the result set, SQLite calls xColumn() to get at the values for each row in the result set. It passes xColumn() the ordinal of the column whose value is to return. To fulfill the request, you just implement a switch statement covering all ordinals in our virtual table, passing back the appropriate value for each column. This works similar to SQLite's user-defined function interface.

This then is the basic workings of an optimized virtual table. The full implementation is given in the file fs.c (available online; see www.ddj.com/code/). Additionally, there is a stripped down, bare-bones virtual table that does nothing in the file example.c.


While powerful in themselves, virtual tables are the foundation of some of SQLite's larger features, such as full text search (FTS). Even more powerful is the idea that you can join virtual tables like any other tables, thus cross referencing disparate information. Also, you can use virtual tables as a way to aggregate information. You could, for example, use the filesystem table to generate a reference list of all files in the filesystem, and then issue queries each day to scan for changes. The possibilities abound. With virtual tables, the world is your database.

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.