From 5fac9f86ebabe619e852f5aee1abba0047de083f Mon Sep 17 00:00:00 2001 From: danielk1977 Date: Tue, 13 Jun 2006 14:16:58 +0000 Subject: [PATCH] Add simple tests for the xFilter and xBestIndex methods. (CVS 3227) FossilOrigin-Name: 0f4657ea69314e49bc7c9faf9a653ef072f5082d --- manifest | 22 ++-- manifest.uuid | 2 +- src/sqlite.h.in | 4 +- src/test8.c | 278 ++++++++++++++++++++++++++++++++++++++++-------- src/vdbe.c | 15 ++- src/where.c | 6 +- test/vtab1.test | 34 +++++- 7 files changed, 293 insertions(+), 68 deletions(-) diff --git a/manifest b/manifest index 617b03fa33..493d3d9481 100644 --- a/manifest +++ b/manifest @@ -1,5 +1,5 @@ -C Lemon\scorrectly\srecognizes\san\sempty\sgrammar\sand\serrors\sout.\nFix\sfor\sa\sbug\sreported\son\sthe\ssqlite-users\smailing\slist.\s(CVS\s3226) -D 2006-06-13T13:27:47 +C Add\ssimple\stests\sfor\sthe\sxFilter\sand\sxBestIndex\smethods.\s(CVS\s3227) +D 2006-06-13T14:16:59 F Makefile.in 56fd6261e83f60724e6dcd764e06ab68cbd53909 F Makefile.linux-gcc 74ba0eadf88748a9ce3fd03d2a3ede2e6715baec F README 9c4e2d6706bdcc3efdd773ce752a8cdab4f90028 @@ -72,7 +72,7 @@ F src/random.c d40f8d356cecbd351ccfab6eaedd7ec1b54f5261 F src/select.c 38eda11d950ed5e631ea9054f84a4a8b9e9b39d8 F src/server.c 087b92a39d883e3fa113cae259d64e4c7438bc96 F src/shell.c 55bf6335dae7146f7a300039f5d6bb35010f1996 -F src/sqlite.h.in 6fb7062da7e9c446623fa26b042bcf4b4050d702 +F src/sqlite.h.in 8a515d52a95aafff6eaaf13e6db38d3325b76af8 F src/sqlite3ext.h 127bd394c8eea481f2ac9b754bf399dbfc818b75 F src/sqliteInt.h 91cc3603fe2e1be18d52490a2fa65ed9640fb338 F src/table.c f64ec4fbfe333f8df925bc6ba494f55e05b0e75e @@ -84,7 +84,7 @@ F src/test4.c 8b784cd82de158a2317cb4ac4bc86f91ad315e25 F src/test5.c 7162f8526affb771c4ed256826eee7bb9eca265f F src/test6.c 60a02961ceb7b3edc25f5dc5c1ac2556622a76de F src/test7.c 03fa8d787f6aebc6d1f72504d52f33013ad2c8e3 -F src/test8.c d29427a0254f7609b0dd2561e62cf78e21c9d689 +F src/test8.c 61d89b51c3cd3407f860134bff9011b081afc69c F src/test_async.c e3deaedd4d86a56391b81808fde9e44fbd92f1d3 F src/test_md5.c 6c42bc0a3c0b54be34623ff77a0eec32b2fa96e3 F src/test_server.c a6460daed0b92ecbc2531b6dc73717470e7a648c @@ -94,7 +94,7 @@ F src/update.c 0186f09414a6578156d40666becc964f85c2a616 F src/utf.c ab81ac59084ff1c07d421eb1a0a84ec809603b44 F src/util.c ca6ee72772c0f5dc04d2e0ab1973fd3b6a9bf79d F src/vacuum.c 5b37d0f436f8e1ffacd17934e44720b38d2247f9 -F src/vdbe.c 1f68000f8690e13535106f1b1bbaedc2da045457 +F src/vdbe.c e2d6072b36a3b32310916dd8443d811eb1b65747 F src/vdbe.h f72e5c00af759b7ed6fd606d508036d732098cc3 F src/vdbeInt.h 6ccb7eaae76ebd761470f6a035501ff33aa92c20 F src/vdbeapi.c 7dc662e7c905ce666bb506dced932e0307115cbf @@ -102,7 +102,7 @@ F src/vdbeaux.c 0168d770d03f9815511780a49cd8360d9a5f1ec5 F src/vdbefifo.c 9efb94c8c3f4c979ebd0028219483f88e57584f5 F src/vdbemem.c 5f0afe3b92bb2c037f8d5d697f7c151fa50783a3 F src/vtab.c 0e39af5822bb17b0007b0df7fad527aa0e3ebd38 -F src/where.c c0f3cd3831cd9defb7c7788038d439a5922e7bd6 +F src/where.c f2e17c6f50e137c14f05f501f0ba15235a711986 F tclinstaller.tcl 046e3624671962dc50f0481d7c25b38ef803eb42 F test/aggerror.test a867e273ef9e3d7919f03ef4f0e8c0d2767944f2 F test/all.test 5df90d015ca63fcef2a4b62c24f7316b66c4bfd4 @@ -287,7 +287,7 @@ F test/vacuum.test 37f998b841cb335397c26d9bbc3457182af2565f F test/vacuum2.test 5aea8c88a65cb29f7d175296e7c819c6158d838c F test/varint.test ab7b110089a08b9926ed7390e7e97bdefeb74102 F test/view.test 16e2774fe35e47a07ac4471b7f0bcc948b1aa6d5 -F test/vtab1.test 67fed6f9b91b75d3e1988b47a972b30dd5ed2d97 +F test/vtab1.test 56b29fcdafec53841730933f99a927e23cd593a6 F test/where.test ee7c9a6659b07e1ee61177f6e7ff71565ee2c9df F test/where2.test a16476a5913e75cf65b38f2daa6157a6b7791394 F test/where3.test 3b5ad2c58069e12be2bd86bc5e211a82810521aa @@ -363,7 +363,7 @@ F www/tclsqlite.tcl bb0d1357328a42b1993d78573e587c6dcbc964b9 F www/vdbe.tcl 87a31ace769f20d3627a64fa1fade7fed47b90d0 F www/version3.tcl 890248cf7b70e60c383b0e84d77d5132b3ead42b F www/whentouse.tcl 97e2b5cd296f7d8057e11f44427dea8a4c2db513 -P 1273261ba7a21062217e7fbc3447b5dadf53010f -R 146414985fadd865b1b6e26a67559392 -U drh -Z f01f64f9187ce173db934fba71a8e2fd +P 55392922686691db3d41525a7f4cb7f976e73467 +R f0c782b5451cd45871a352c2a0318f22 +U danielk1977 +Z c4c527997467541782ddbca206039900 diff --git a/manifest.uuid b/manifest.uuid index 4c54077160..019fa4700a 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -55392922686691db3d41525a7f4cb7f976e73467 \ No newline at end of file +0f4657ea69314e49bc7c9faf9a653ef072f5082d \ No newline at end of file diff --git a/src/sqlite.h.in b/src/sqlite.h.in index 3d67cb9d56..023c90fff5 100644 --- a/src/sqlite.h.in +++ b/src/sqlite.h.in @@ -12,7 +12,7 @@ ** This header file defines the interface that the SQLite library ** presents to client programs. ** -** @(#) $Id: sqlite.h.in,v 1.171 2006/06/13 01:04:53 drh Exp $ +** @(#) $Id: sqlite.h.in,v 1.172 2006/06/13 14:16:59 danielk1977 Exp $ */ #ifndef _SQLITE3_H_ #define _SQLITE3_H_ @@ -1589,7 +1589,7 @@ struct sqlite3_module { ** Each term of aOrderBy records a column of the ORDER BY clause. ** ** The xBestIndex method must fill aConstraintUsage[] with information -** about what parameters to pass to xBestIndex. If argvIndex>0 then +** about what parameters to pass to xFilter. If argvIndex>0 then ** the right-hand side of the corresponding aConstraint[] is evaluated ** and becomes the argvIndex-th entry in argv. If aConstraintUsage[].omit ** is true, then the constraint is assumed to be fully handled by the diff --git a/src/test8.c b/src/test8.c index 0176d7b2e5..d95401ae53 100644 --- a/src/test8.c +++ b/src/test8.c @@ -13,7 +13,7 @@ ** is not included in the SQLite library. It is used for automated ** testing of the SQLite library. ** -** $Id: test8.c,v 1.8 2006/06/13 10:24:43 danielk1977 Exp $ +** $Id: test8.c,v 1.9 2006/06/13 14:16:59 danielk1977 Exp $ */ #include "sqliteInt.h" #include "tcl.h" @@ -24,12 +24,22 @@ typedef struct echo_vtab echo_vtab; typedef struct echo_cursor echo_cursor; -/* An echo vtab object */ +/* +** An echo virtual-table object +** +** If it is not NULL, the aHasIndex array is allocated so that it has +** the same number of entries as there are columns in the underlying +** real table. +*/ struct echo_vtab { sqlite3_vtab base; Tcl_Interp *interp; sqlite3 *db; - char *zStmt; + char *zStmt; /* "SELECT rowid, * FROM " */ + + int *aIndex; + int nCol; + char **aCol; }; /* An echo cursor object */ @@ -39,13 +49,112 @@ struct echo_cursor { int errcode; /* Error code */ }; +static int getColumnNames( + sqlite3 *db, + const char *zTab, + char ***paCol, + int *pnCol +){ + char **aCol = 0; + char zBuf[1024]; + sqlite3_stmt *pStmt = 0; + int rc = SQLITE_OK; + int nCol; + + sprintf(zBuf, "SELECT * FROM %s", zTab); + rc = sqlite3_prepare(db, zBuf, -1, &pStmt, 0); + if( rc==SQLITE_OK ){ + int ii; + nCol = sqlite3_column_count(pStmt); + aCol = sqliteMalloc(sizeof(char *) * nCol); + if( !aCol ){ + rc = SQLITE_NOMEM; + goto fail; + } + for(ii=0; ii=0 && cidzStmt = sqlite3MPrintf("SELECT rowid, * FROM %s ", argv[1]); + if( rc==SQLITE_OK ){ + rc = getIndexArray(db, argv[1], &pVtab->aIndex); + } + if( rc==SQLITE_OK ){ + rc = getColumnNames(db, argv[1], &pVtab->aCol, &pVtab->nCol); + } } return rc; @@ -137,39 +252,43 @@ static int echoConnect( return echoConstructor(db, pModule, argc, argv, ppVtab); } -static int echoDisconnect(sqlite3_vtab *pVtab){ +static int echoDestructor(sqlite3_vtab *pVtab){ + int ii; echo_vtab *p = (echo_vtab*)pVtab; - appendToEchoModule(p->interp, "xDisconnect"); sqliteFree(p->zStmt); + sqliteFree(p->aIndex); + for(ii=0; iinCol; ii++){ + sqliteFree(p->aCol[ii]); + } + sqliteFree(p->aCol); sqliteFree(p); return 0; } + +static int echoDisconnect(sqlite3_vtab *pVtab){ + appendToEchoModule(((echo_vtab *)pVtab)->interp, "xDisconnect"); + return echoDestructor(pVtab); +} static int echoDestroy(sqlite3_vtab *pVtab){ - echo_vtab *p = (echo_vtab*)pVtab; - appendToEchoModule(p->interp, "xDestroy"); - sqliteFree(p->zStmt); - sqliteFree(p); - return 0; + appendToEchoModule(((echo_vtab *)pVtab)->interp, "xDestroy"); + return echoDestructor(pVtab); } -static int echoOpen(sqlite3_vtab *pVTab, sqlite3_vtab_cursor **ppCursor) -{ +static int echoOpen(sqlite3_vtab *pVTab, sqlite3_vtab_cursor **ppCursor){ echo_cursor *pCur; pCur = sqliteMalloc(sizeof(echo_cursor)); *ppCursor = (sqlite3_vtab_cursor *)pCur; return SQLITE_OK; } -static int echoClose(sqlite3_vtab_cursor *cur) -{ +static int echoClose(sqlite3_vtab_cursor *cur){ echo_cursor *pCur = (echo_cursor *)cur; sqlite3_finalize(pCur->pStmt); sqliteFree(pCur); return SQLITE_OK; } -static int echoNext(sqlite3_vtab_cursor *cur) -{ +static int echoNext(sqlite3_vtab_cursor *cur){ int rc; echo_cursor *pCur = (echo_cursor *)cur; @@ -186,31 +305,7 @@ static int echoNext(sqlite3_vtab_cursor *cur) return rc; } -static int echoFilter( - sqlite3_vtab_cursor *pVtabCursor, - int idx, - int argc, - sqlite3_value **argv -){ - int rc; - - echo_cursor *pCur = (echo_cursor *)pVtabCursor; - echo_vtab *pVtab = (echo_vtab *)pVtabCursor->pVtab; - sqlite3 *db = pVtab->db; - - sqlite3_finalize(pCur->pStmt); - pCur->pStmt = 0; - rc = sqlite3_prepare(db, pVtab->zStmt, -1, &pCur->pStmt, 0); - - if( rc==SQLITE_OK ){ - rc = echoNext(pVtabCursor); - } - - return rc; -} - -static int echoColumn(sqlite3_vtab_cursor *cur, sqlite3_context *ctx, int i) -{ +static int echoColumn(sqlite3_vtab_cursor *cur, sqlite3_context *ctx, int i){ int iCol = i + 1; sqlite3_stmt *pStmt = ((echo_cursor *)cur)->pStmt; @@ -240,20 +335,109 @@ static int echoColumn(sqlite3_vtab_cursor *cur, sqlite3_context *ctx, int i) return SQLITE_OK; } -static int echoRowid(sqlite3_vtab_cursor *cur, sqlite_int64 *pRowid) -{ +static int echoRowid(sqlite3_vtab_cursor *cur, sqlite_int64 *pRowid){ sqlite3_stmt *pStmt = ((echo_cursor *)cur)->pStmt; *pRowid = sqlite3_column_int64(pStmt, 0); return SQLITE_OK; } +static int echoFilter( + sqlite3_vtab_cursor *pVtabCursor, + int idx, + int argc, + sqlite3_value **argv +){ + int rc; + char zBuf[32]; + int ii; + + echo_cursor *pCur = (echo_cursor *)pVtabCursor; + echo_vtab *pVtab = (echo_vtab *)pVtabCursor->pVtab; + sqlite3 *db = pVtab->db; + + sprintf(zBuf, "%d", idx); + appendToEchoModule(pVtab->interp, "xFilter"); + appendToEchoModule(pVtab->interp, zBuf); + for(ii=0; iiinterp, sqlite3_value_text(argv[ii])); + } + + sqlite3_finalize(pCur->pStmt); + pCur->pStmt = 0; + rc = sqlite3_prepare(db, pVtab->zStmt, -1, &pCur->pStmt, 0); + + if( rc==SQLITE_OK ){ + rc = echoNext(pVtabCursor); + } + + return rc; +} /* -** The xBestIndex method for the echo module always returns -** an index of 123. +** The echo module implements the subset of query constraints and sort +** orders that may take advantage of SQLite indices on the underlying +** real table. For example, if the real table is declared as: +** +** CREATE TABLE real(a, b, c); +** CREATE INDEX real_index ON real(b); +** +** then the echo module handles WHERE or ORDER BY clauses that refer +** to the column "b", but not "a" or "c". If a multi-column index is +** present, only it's left most column is considered. */ -static int echoBestIndex(sqlite3_vtab *pVtab, sqlite3_index_info *pIdxInfo){ +static int echoBestIndex(sqlite3_vtab *tab, sqlite3_index_info *pIdxInfo){ + int ii; + char *zWhere = 0; + char *zOrder = 0; + int nArg = 0; + echo_vtab *pVtab = (echo_vtab *)tab; + + for(ii=0; iinConstraint; ii++){ + const struct sqlite3_index_constraint *pConstraint; + struct sqlite3_index_constraint_usage *pUsage; + + pConstraint = &pIdxInfo->aConstraint[ii]; + pUsage = &pIdxInfo->aConstraintUsage[ii]; + + int iCol = pConstraint->iColumn; + if( pVtab->aIndex[iCol] ){ + char *zCol = pVtab->aCol[iCol]; + char *zOp = 0; + switch( pConstraint->op ){ + case SQLITE_INDEX_CONSTRAINT_EQ: + zOp = "="; break; + case SQLITE_INDEX_CONSTRAINT_LT: + zOp = "<"; break; + case SQLITE_INDEX_CONSTRAINT_GT: + zOp = ">"; break; + case SQLITE_INDEX_CONSTRAINT_LE: + zOp = "<="; break; + case SQLITE_INDEX_CONSTRAINT_GE: + zOp = ">="; break; + case SQLITE_INDEX_CONSTRAINT_MATCH: + zOp = "MATCH"; break; + } + if( zWhere ){ + char *zTmp = zWhere; + zWhere = sqlite3MPrintf("%s AND %s %s ?", zWhere, zCol, zOp); + sqliteFree(zTmp); + } else { + zWhere = sqlite3MPrintf("WHERE %s %s ?", zCol, zOp); + } + + pUsage->argvIndex = ++nArg; + pUsage->omit = 1; + } + } + + appendToEchoModule(pVtab->interp, "xBestIndex");; + appendToEchoModule(pVtab->interp, zWhere); + appendToEchoModule(pVtab->interp, zOrder); + + sqliteFree(zWhere); + sqliteFree(zOrder); + pIdxInfo->idxNum = 123; return SQLITE_OK; } diff --git a/src/vdbe.c b/src/vdbe.c index 95bd95d274..26caf2edb0 100644 --- a/src/vdbe.c +++ b/src/vdbe.c @@ -43,7 +43,7 @@ ** in this file for details. If in doubt, do not deviate from existing ** commenting and indentation practices when changing or adding code. ** -** $Id: vdbe.c,v 1.555 2006/06/13 10:24:43 danielk1977 Exp $ +** $Id: vdbe.c,v 1.556 2006/06/13 14:16:59 danielk1977 Exp $ */ #include "sqliteInt.h" #include "os.h" @@ -4588,11 +4588,13 @@ case OP_VOpen: { #endif /* SQLITE_OMIT_VIRTUALTABLE */ #ifndef SQLITE_OMIT_VIRTUALTABLE -/* Opcode: VFilter P1 P2 * +/* Opcode: VFilter P1 P2 P3 ** ** P1 is a cursor opened using VOpen. P2 is an address to jump to if ** the filtered result set is empty. ** +** P3 points to enough free space to use to marshall the arguments. +** ** This opcode invokes the xFilter method on the virtual table specified ** by P1. The index number parameter to xFilter is the top of the stack. ** Next down on the stack is the argc parameter. Beneath the @@ -4625,11 +4627,14 @@ case OP_VFilter: { /* Invoke the xFilter method if one is defined. */ if( pModule->xFilter ){ int res; - Mem *apArg; - apArg = &pTos[1-2-nArg]; + int ii; + Mem **apArg = pOp->p3; + for(ii = 0; iixFilter(pCur->pVtabCursor, iIndex, nArg, &apArg); + res = pModule->xFilter(pCur->pVtabCursor, iIndex, nArg, apArg); if( sqlite3SafetyOn(db) ) goto abort_due_to_misuse; if( res==0 ){ diff --git a/src/where.c b/src/where.c index 764b32a98d..2a047d5c1a 100644 --- a/src/where.c +++ b/src/where.c @@ -16,7 +16,7 @@ ** so is applicable. Because this module is responsible for selecting ** indices, you might also think of this module as the "query optimizer". ** -** $Id: where.c,v 1.212 2006/06/13 01:04:53 drh Exp $ +** $Id: where.c,v 1.213 2006/06/13 14:16:59 danielk1977 Exp $ */ #include "sqliteInt.h" @@ -1842,6 +1842,8 @@ WhereInfo *sqlite3WhereBegin( #ifndef SQLITE_OMIT_VIRTUALTABLE if( pLevel->pIdxInfo ){ + char *zSpace; /* Space for OP_VFilter to marshall it's arguments */ + /* Case 0: That table is a virtual-table. Use the VFilter and VNext. */ sqlite3_index_info *pIdxInfo = pLevel->pIdxInfo; @@ -1858,6 +1860,8 @@ WhereInfo *sqlite3WhereBegin( sqlite3VdbeAddOp(v, OP_Integer, i-1, 0); sqlite3VdbeAddOp(v, OP_Integer, pIdxInfo->idxNum, 0); sqlite3VdbeAddOp(v, OP_VFilter, iCur, brk); + zSpace = (char *)sqliteMalloc(sizeof(sqlite3_value*)*(i-1)); + sqlite3VdbeChangeP3(v, -1, zSpace, P3_DYNAMIC); for(i=0; inConstraint; i++){ if( pIdxInfo->aConstraintUsage[i].omit ){ disableTerm(pLevel, &wc.a[i]); diff --git a/test/vtab1.test b/test/vtab1.test index 32193562b0..30242907dd 100644 --- a/test/vtab1.test +++ b/test/vtab1.test @@ -11,7 +11,7 @@ # This file implements regression tests for SQLite library. The # focus of this file is creating and dropping virtual tables. # -# $Id: vtab1.test,v 1.8 2006/06/13 10:24:44 danielk1977 Exp $ +# $Id: vtab1.test,v 1.9 2006/06/13 14:16:59 danielk1977 Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl @@ -82,6 +82,7 @@ do_test vtab1-2.2 { # Test that the database can be unloaded. This should invoke # the xDisconnect() callback each of the two virtual tables - t1 and t2. do_test vtab1-2.3 { +breakpoint set echo_module [list] db close set echo_module @@ -92,6 +93,7 @@ do_test vtab1-2.3 { # table is still correct. do_test vtab1-2.4 { sqlite3 db test.db + db cache size 0 register_echo_module [sqlite3_connection_pointer db] execsql { PRAGMA table_info(t2); } } [list \ @@ -143,6 +145,7 @@ do_test vtab1-3.1 { set echo_module "" execsql { CREATE TABLE treal(a INTEGER, b VARCHAR(32), c); + CREATE INDEX treal_idx ON treal(b); CREATE VIRTUAL TABLE t1 USING echo(treal); } set echo_module @@ -173,5 +176,34 @@ do_test vtab1-3.5 { } } {1 2} +do_test vtab1-3.6 { + set echo_module "" + execsql { + SELECT * FROM t1; + } + set echo_module +} {xBestIndex {} {} xFilter 123} +do_test vtab1-3.7 { + set echo_module "" + execsql { + SELECT * FROM t1 WHERE b = 10; + } + set echo_module +} {xBestIndex {WHERE b = ?} {} xFilter 123 10} +do_test vtab1-3.8 { + set echo_module "" + execsql { + SELECT * FROM t1 WHERE b >= 5 AND b <= 10; + } + set echo_module +} {xBestIndex {WHERE b >= ? AND b <= ?} {} xFilter 123 5 10} +do_test vtab1-3.9 { + set echo_module "" + execsql { + SELECT * FROM t1 WHERE b BETWEEN 5 AND 10; + } + set echo_module +} {xBestIndex {WHERE b >= ? AND b <= ?} {} xFilter 123 5 10} + finish_test -- 2.47.2