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

d12owen_p4ma 10/15/07 8:38 AM Page 27 Perforce continued from page 25 SQLite passes xBestIndex() a reference to a sqlite3_index_info structure (p_info), which contains extensive information on the compiled query. This structure is organized into input and output sections. The input is an array of sqlite3_index_constraint pointers (Listing Five, available online). The output is an array of sqlite3_index_constraint_usage pointers (Listing Six, available online). The aConstraint and aConstraintUsage contain the same number of elements, and each of their elements corresponds to the other. That is, aConstraintUseage[i] and aConstraint[i] both refer to the same column. Consider the query: SELECT * from fs WHERE name= 'foo' AND path='/var/log' p_info->aConstraintUsage[0] = 1 p_info->aConstraintUsage[1] = 2 Fast Software Configuration Management The values assigned to the aConstraintUsage[] elements are significant. They specify the order in which they are passed to xFilter(). In the example, this says “pass xFilter() the name column first, then the path column.” To understand how this works, you need to know a little about xFilter(). The declaration of xFilter() is as follows: static int vt_filter ( sqlite3_vtab_cursor *p_vtc, int idxNum, const char *idxSint argc, sqlite3_value **argv ); 200,000 developers rely on Perforce to manage their code. Here’s what they’re saying “Perforce is absolutely the only tool I would trust to manage our source code.” The constraint name='foo' AND path='/var/log' would be represented in xBestIndex() as: p_info->aConstraint[0].iColumn = 0 p_info->aConstraint[0].op = SQLITE_INDEX_CONSTRAINT_EQ p_info->aConstraint[1].iColumn = 1 p_info->aConstraint[1].op = SQLITE_INDEX_CONSTRAINT_EQ The values from xBestIndex() are passed via the argc and argv arguments. argc contains the size of argv, and argv values themselves. The order of the values in argv follows the order specified in p_info>aConstraintUsage in xBestIndex(). So, in our example above, argc will be 2 and argv will be as follows: argv[0] = 'foo' argv[1] = '/var/log' Nick Triantos NVIDIA “Perforce has been a breath of fresh air; a product which does what it's supposed to do and does it well.” In this case, the name column is referenced in aConstraint[0]. We know it is the name column because its ordinal is 0 (given by aConstraint[0].iColumn), and according to our table schema, column ordinal 0 corresponds to name. The same follows for path. Furthermore, we are given the relational operator used in the expression as well. These operators are: #define SQLITE_INDEX_CONSTRAINT_EQ 2 #define SQLITE_INDEX_CONSTRAINT_GT 4 #define SQLITE_INDEX_CONSTRAINT_LE 8 #define SQLITE_INDEX_CONSTRAINT_LT 16 #define SQLITE_INDEX_CONSTRAINT_GE 32 #define SQLITE_INDEX_CONSTRAINT_MATCH 64 Now that xFilter() has this information, its job is to set up the cursor object so it can iterate through a specific set of data. In this case, we know that since path='/var/log', we will be limiting our search to just that directory (and not searching the whole filesystem). From that point forward, the cursor basically holds the state and iterates through all the files and directories in /var/log. The implementation is straightforward—it’s just a standard recursive directory search using the APR library. Scott Buchholz Sybase “Perforce is the cornerstone of Monster’s software production lines.” Mark Conway Monster Worldwide “The bottom line is that Perforce has proven itself to be a reliable platform for global development at Symantec.” The match() Function Here’s where things get tricky. Consider the query: SELECT * FROM fs WHERE path='/var/log' OR path='/usr/lib'; Russell Jackson Symantec Thus, we have the flexibility of choosing not only the column of interest, but the equality conditions under which it is being used. Here, xBestIndex() reads through the inputs (p_info->aConstraint[]) and specifies which columns it wants to use in the output (p_info->aConstraintUsage[]). To use both columns as indexes in xFilter(), you would do this in xBestIndex(): 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 See for yourself Free evaluation at www.perforce.com December 2007 l www.ddj.com l Dr. Dobb’s Journal 27 http://www.perforce.com 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.