1 /*

     2 ** 2006 June 10

     3 **

     4 ** The author disclaims copyright to this source code.  In place of

     5 ** a legal notice, here is a blessing:

     6 **

     7 **    May you do good and not evil.

     8 **    May you find forgiveness for yourself and forgive others.

     9 **    May you share freely, never taking more than you give.

    10 **

    11 *************************************************************************

    12 ** Code for testing the virtual table interfaces.  This code

    13 ** is not included in the SQLite library.  It is used for automated

    14 ** testing of the SQLite library.

    15 **

    16 ** $Id: test8.c,v 1.75 2008/08/31 00:29:08 shane Exp $

    17 */

    18 #include "sqliteInt.h"

    19 #include "tcl.h"

    20 #include <stdlib.h>

    21 #include <string.h>

    22 

    23 #ifndef SQLITE_OMIT_VIRTUALTABLE

    24 

    25 typedef struct echo_vtab echo_vtab;

    26 typedef struct echo_cursor echo_cursor;

    27 

    28 /*

    29 ** The test module defined in this file uses four global Tcl variables to

    30 ** commicate with test-scripts:

    31 **

    32 **     $::echo_module

    33 **     $::echo_module_sync_fail

    34 **     $::echo_module_begin_fail

    35 **     $::echo_module_cost

    36 **

    37 ** The variable ::echo_module is a list. Each time one of the following

    38 ** methods is called, one or more elements are appended to the list.

    39 ** This is used for automated testing of virtual table modules.

    40 **

    41 ** The ::echo_module_sync_fail variable is set by test scripts and read

    42 ** by code in this file. If it is set to the name of a real table in the

    43 ** the database, then all xSync operations on echo virtual tables that

    44 ** use the named table as a backing store will fail.

    45 */

    46 

    47 /*

    48 ** Errors can be provoked within the following echo virtual table methods:

    49 **

    50 **   xBestIndex   xOpen     xFilter   xNext   

    51 **   xColumn      xRowid    xUpdate   xSync   

    52 **   xBegin       xRename

    53 **

    54 ** This is done by setting the global tcl variable:

    55 **

    56 **   echo_module_fail($method,$tbl)

    57 **

    58 ** where $method is set to the name of the virtual table method to fail

    59 ** (i.e. "xBestIndex") and $tbl is the name of the table being echoed (not

    60 ** the name of the virtual table, the name of the underlying real table).

    61 */

    62 

    63 /* 

    64 ** An echo virtual-table object.

    65 **

    66 ** echo.vtab.aIndex is an array of booleans. The nth entry is true if 

    67 ** the nth column of the real table is the left-most column of an index

    68 ** (implicit or otherwise). In other words, if SQLite can optimize

    69 ** a query like "SELECT * FROM real_table WHERE col = ?".

    70 **

    71 ** Member variable aCol[] contains copies of the column names of the real

    72 ** table.

    73 */

    74 struct echo_vtab {

    75   sqlite3_vtab base;

    76   Tcl_Interp *interp;     /* Tcl interpreter containing debug variables */

    77   sqlite3 *db;            /* Database connection */

    78 

    79   int isPattern;

    80   int inTransaction;      /* True if within a transaction */

    81   char *zThis;            /* Name of the echo table */

    82   char *zTableName;       /* Name of the real table */

    83   char *zLogName;         /* Name of the log table */

    84   int nCol;               /* Number of columns in the real table */

    85   int *aIndex;            /* Array of size nCol. True if column has an index */

    86   char **aCol;            /* Array of size nCol. Column names */

    87 };

    88 

    89 /* An echo cursor object */

    90 struct echo_cursor {

    91   sqlite3_vtab_cursor base;

    92   sqlite3_stmt *pStmt;

    93 };

    94 

    95 static int simulateVtabError(echo_vtab *p, const char *zMethod){

    96   const char *zErr;

    97   char zVarname[128];

    98   zVarname[127] = '\0';

    99   sqlite3_snprintf(127, zVarname, "echo_module_fail(%s,%s)", zMethod, p->zTableName);

   100   zErr = Tcl_GetVar(p->interp, zVarname, TCL_GLOBAL_ONLY);

   101   if( zErr ){

   102     p->base.zErrMsg = sqlite3_mprintf("echo-vtab-error: %s", zErr);

   103   }

   104   return (zErr!=0);

   105 }

   106 

   107 /*

   108 ** Convert an SQL-style quoted string into a normal string by removing

   109 ** the quote characters.  The conversion is done in-place.  If the

   110 ** input does not begin with a quote character, then this routine

   111 ** is a no-op.

   112 **

   113 ** Examples:

   114 **

   115 **     "abc"   becomes   abc

   116 **     'xyz'   becomes   xyz

   117 **     [pqr]   becomes   pqr

   118 **     `mno`   becomes   mno

   119 */

   120 static void dequoteString(char *z){

   121   int quote;

   122   int i, j;

   123   if( z==0 ) return;

   124   quote = z[0];

   125   switch( quote ){

   126     case '\'':  break;

   127     case '"':   break;

   128     case '`':   break;                /* For MySQL compatibility */

   129     case '[':   quote = ']';  break;  /* For MS SqlServer compatibility */

   130     default:    return;

   131   }

   132   for(i=1, j=0; z[i]; i++){

   133     if( z[i]==quote ){

   134       if( z[i+1]==quote ){

   135         z[j++] = quote;

   136         i++;

   137       }else{

   138         z[j++] = 0;

   139         break;

   140       }

   141     }else{

   142       z[j++] = z[i];

   143     }

   144   }

   145 }

   146 

   147 /*

   148 ** Retrieve the column names for the table named zTab via database

   149 ** connection db. SQLITE_OK is returned on success, or an sqlite error

   150 ** code otherwise.

   151 **

   152 ** If successful, the number of columns is written to *pnCol. *paCol is

   153 ** set to point at sqlite3_malloc()'d space containing the array of

   154 ** nCol column names. The caller is responsible for calling sqlite3_free

   155 ** on *paCol.

   156 */

   157 static int getColumnNames(

   158   sqlite3 *db, 

   159   const char *zTab,

   160   char ***paCol, 

   161   int *pnCol

   162 ){

   163   char **aCol = 0;

   164   char *zSql;

   165   sqlite3_stmt *pStmt = 0;

   166   int rc = SQLITE_OK;

   167   int nCol = 0;

   168 

   169   /* Prepare the statement "SELECT * FROM <tbl>". The column names

   170   ** of the result set of the compiled SELECT will be the same as

   171   ** the column names of table <tbl>.

   172   */

   173   zSql = sqlite3_mprintf("SELECT * FROM %Q", zTab);

   174   if( !zSql ){

   175     rc = SQLITE_NOMEM;

   176     goto out;

   177   }

   178   rc = sqlite3_prepare(db, zSql, -1, &pStmt, 0);

   179   sqlite3_free(zSql);

   180 

   181   if( rc==SQLITE_OK ){

   182     int ii;

   183     int nBytes;

   184     char *zSpace;

   185     nCol = sqlite3_column_count(pStmt);

   186 

   187     /* Figure out how much space to allocate for the array of column names 

   188     ** (including space for the strings themselves). Then allocate it.

   189     */

   190     nBytes = sizeof(char *) * nCol;

   191     for(ii=0; ii<nCol; ii++){

   192       const char *zName = sqlite3_column_name(pStmt, ii);

   193       if( !zName ){

   194         rc = SQLITE_NOMEM;

   195         goto out;

   196       }

   197       nBytes += strlen(zName)+1;

   198     }

   199     aCol = (char **)sqlite3MallocZero(nBytes);

   200     if( !aCol ){

   201       rc = SQLITE_NOMEM;

   202       goto out;

   203     }

   204 

   205     /* Copy the column names into the allocated space and set up the

   206     ** pointers in the aCol[] array.

   207     */

   208     zSpace = (char *)(&aCol[nCol]);

   209     for(ii=0; ii<nCol; ii++){

   210       aCol[ii] = zSpace;

   211       zSpace += sprintf(zSpace, "%s", sqlite3_column_name(pStmt, ii));

   212       zSpace++;

   213     }

   214     assert( (zSpace-nBytes)==(char *)aCol );

   215   }

   216 

   217   *paCol = aCol;

   218   *pnCol = nCol;

   219 

   220 out:

   221   sqlite3_finalize(pStmt);

   222   return rc;

   223 }

   224 

   225 /*

   226 ** Parameter zTab is the name of a table in database db with nCol 

   227 ** columns. This function allocates an array of integers nCol in 

   228 ** size and populates it according to any implicit or explicit 

   229 ** indices on table zTab.

   230 **

   231 ** If successful, SQLITE_OK is returned and *paIndex set to point 

   232 ** at the allocated array. Otherwise, an error code is returned.

   233 **

   234 ** See comments associated with the member variable aIndex above 

   235 ** "struct echo_vtab" for details of the contents of the array.

   236 */

   237 static int getIndexArray(

   238   sqlite3 *db,             /* Database connection */

   239   const char *zTab,        /* Name of table in database db */

   240   int nCol,

   241   int **paIndex

   242 ){

   243   sqlite3_stmt *pStmt = 0;

   244   int *aIndex = 0;

   245   int rc;

   246   char *zSql;

   247 

   248   /* Allocate space for the index array */

   249   aIndex = (int *)sqlite3MallocZero(sizeof(int) * nCol);

   250   if( !aIndex ){

   251     rc = SQLITE_NOMEM;

   252     goto get_index_array_out;

   253   }

   254 

   255   /* Compile an sqlite pragma to loop through all indices on table zTab */

   256   zSql = sqlite3MPrintf(0, "PRAGMA index_list(%s)", zTab);

   257   if( !zSql ){

   258     rc = SQLITE_NOMEM;

   259     goto get_index_array_out;

   260   }

   261   rc = sqlite3_prepare(db, zSql, -1, &pStmt, 0);

   262   sqlite3_free(zSql);

   263 

   264   /* For each index, figure out the left-most column and set the 

   265   ** corresponding entry in aIndex[] to 1.

   266   */

   267   while( pStmt && sqlite3_step(pStmt)==SQLITE_ROW ){

   268     const char *zIdx = (const char *)sqlite3_column_text(pStmt, 1);

   269     sqlite3_stmt *pStmt2 = 0;

   270     zSql = sqlite3MPrintf(0, "PRAGMA index_info(%s)", zIdx);

   271     if( !zSql ){

   272       rc = SQLITE_NOMEM;

   273       goto get_index_array_out;

   274     }

   275     rc = sqlite3_prepare(db, zSql, -1, &pStmt2, 0);

   276     sqlite3_free(zSql);

   277     if( pStmt2 && sqlite3_step(pStmt2)==SQLITE_ROW ){

   278       int cid = sqlite3_column_int(pStmt2, 1);

   279       assert( cid>=0 && cid<nCol );

   280       aIndex[cid] = 1;

   281     }

   282     if( pStmt2 ){

   283       rc = sqlite3_finalize(pStmt2);

   284     }

   285     if( rc!=SQLITE_OK ){

   286       goto get_index_array_out;

   287     }

   288   }

   289 

   290 

   291 get_index_array_out:

   292   if( pStmt ){

   293     int rc2 = sqlite3_finalize(pStmt);

   294     if( rc==SQLITE_OK ){

   295       rc = rc2;

   296     }

   297   }

   298   if( rc!=SQLITE_OK ){

   299     sqlite3_free(aIndex);

   300     aIndex = 0;

   301   }

   302   *paIndex = aIndex;

   303   return rc;

   304 }

   305 

   306 /*

   307 ** Global Tcl variable $echo_module is a list. This routine appends

   308 ** the string element zArg to that list in interpreter interp.

   309 */

   310 static void appendToEchoModule(Tcl_Interp *interp, const char *zArg){

   311   int flags = (TCL_APPEND_VALUE | TCL_LIST_ELEMENT | TCL_GLOBAL_ONLY);

   312   Tcl_SetVar(interp, "echo_module", (zArg?zArg:""), flags);

   313 }

   314 

   315 /*

   316 ** This function is called from within the echo-modules xCreate and

   317 ** xConnect methods. The argc and argv arguments are copies of those 

   318 ** passed to the calling method. This function is responsible for

   319 ** calling sqlite3_declare_vtab() to declare the schema of the virtual

   320 ** table being created or connected.

   321 **

   322 ** If the constructor was passed just one argument, i.e.:

   323 **

   324 **   CREATE TABLE t1 AS echo(t2);

   325 **

   326 ** Then t2 is assumed to be the name of a *real* database table. The

   327 ** schema of the virtual table is declared by passing a copy of the 

   328 ** CREATE TABLE statement for the real table to sqlite3_declare_vtab().

   329 ** Hence, the virtual table should have exactly the same column names and 

   330 ** types as the real table.

   331 */

   332 static int echoDeclareVtab(

   333   echo_vtab *pVtab, 

   334   sqlite3 *db 

   335 ){

   336   int rc = SQLITE_OK;

   337 

   338   if( pVtab->zTableName ){

   339     sqlite3_stmt *pStmt = 0;

   340     rc = sqlite3_prepare(db, 

   341         "SELECT sql FROM sqlite_master WHERE type = 'table' AND name = ?",

   342         -1, &pStmt, 0);

   343     if( rc==SQLITE_OK ){

   344       sqlite3_bind_text(pStmt, 1, pVtab->zTableName, -1, 0);

   345       if( sqlite3_step(pStmt)==SQLITE_ROW ){

   346         int rc2;

   347         const char *zCreateTable = (const char *)sqlite3_column_text(pStmt, 0);

   348         rc = sqlite3_declare_vtab(db, zCreateTable);

   349         rc2 = sqlite3_finalize(pStmt);

   350         if( rc==SQLITE_OK ){

   351           rc = rc2;

   352         }

   353       } else {

   354         rc = sqlite3_finalize(pStmt);

   355         if( rc==SQLITE_OK ){ 

   356           rc = SQLITE_ERROR;

   357         }

   358       }

   359       if( rc==SQLITE_OK ){

   360         rc = getColumnNames(db, pVtab->zTableName, &pVtab->aCol, &pVtab->nCol);

   361       }

   362       if( rc==SQLITE_OK ){

   363         rc = getIndexArray(db, pVtab->zTableName, pVtab->nCol, &pVtab->aIndex);

   364       }

   365     }

   366   }

   367 

   368   return rc;

   369 }

   370 

   371 /*

   372 ** This function frees all runtime structures associated with the virtual

   373 ** table pVtab.

   374 */

   375 static int echoDestructor(sqlite3_vtab *pVtab){

   376   echo_vtab *p = (echo_vtab*)pVtab;

   377   sqlite3_free(p->aIndex);

   378   sqlite3_free(p->aCol);

   379   sqlite3_free(p->zThis);

   380   sqlite3_free(p->zTableName);

   381   sqlite3_free(p->zLogName);

   382   sqlite3_free(p);

   383   return 0;

   384 }

   385 

   386 typedef struct EchoModule EchoModule;

   387 struct EchoModule {

   388   Tcl_Interp *interp;

   389 };

   390 

   391 /*

   392 ** This function is called to do the work of the xConnect() method -

   393 ** to allocate the required in-memory structures for a newly connected

   394 ** virtual table.

   395 */

   396 static int echoConstructor(

   397   sqlite3 *db,

   398   void *pAux,

   399   int argc, const char *const*argv,

   400   sqlite3_vtab **ppVtab,

   401   char **pzErr

   402 ){

   403   int rc;

   404   int i;

   405   echo_vtab *pVtab;

   406 

   407   /* Allocate the sqlite3_vtab/echo_vtab structure itself */

   408   pVtab = sqlite3MallocZero( sizeof(*pVtab) );

   409   if( !pVtab ){

   410     return SQLITE_NOMEM;

   411   }

   412   pVtab->interp = ((EchoModule *)pAux)->interp;

   413   pVtab->db = db;

   414 

   415   /* Allocate echo_vtab.zThis */

   416   pVtab->zThis = sqlite3MPrintf(0, "%s", argv[2]);

   417   if( !pVtab->zThis ){

   418     echoDestructor((sqlite3_vtab *)pVtab);

   419     return SQLITE_NOMEM;

   420   }

   421 

   422   /* Allocate echo_vtab.zTableName */

   423   if( argc>3 ){

   424     pVtab->zTableName = sqlite3MPrintf(0, "%s", argv[3]);

   425     dequoteString(pVtab->zTableName);

   426     if( pVtab->zTableName && pVtab->zTableName[0]=='*' ){

   427       char *z = sqlite3MPrintf(0, "%s%s", argv[2], &(pVtab->zTableName[1]));

   428       sqlite3_free(pVtab->zTableName);

   429       pVtab->zTableName = z;

   430       pVtab->isPattern = 1;

   431     }

   432     if( !pVtab->zTableName ){

   433       echoDestructor((sqlite3_vtab *)pVtab);

   434       return SQLITE_NOMEM;

   435     }

   436   }

   437 

   438   /* Log the arguments to this function to Tcl var ::echo_module */

   439   for(i=0; i<argc; i++){

   440     appendToEchoModule(pVtab->interp, argv[i]);

   441   }

   442 

   443   /* Invoke sqlite3_declare_vtab and set up other members of the echo_vtab

   444   ** structure. If an error occurs, delete the sqlite3_vtab structure and

   445   ** return an error code.

   446   */

   447   rc = echoDeclareVtab(pVtab, db);

   448   if( rc!=SQLITE_OK ){

   449     echoDestructor((sqlite3_vtab *)pVtab);

   450     return rc;

   451   }

   452 

   453   /* Success. Set *ppVtab and return */

   454   *ppVtab = &pVtab->base;

   455   return SQLITE_OK;

   456 }

   457 

   458 /* 

   459 ** Echo virtual table module xCreate method.

   460 */

   461 static int echoCreate(

   462   sqlite3 *db,

   463   void *pAux,

   464   int argc, const char *const*argv,

   465   sqlite3_vtab **ppVtab,

   466   char **pzErr

   467 ){

   468   int rc = SQLITE_OK;

   469   appendToEchoModule(((EchoModule *)pAux)->interp, "xCreate");

   470   rc = echoConstructor(db, pAux, argc, argv, ppVtab, pzErr);

   471 

   472   /* If there were two arguments passed to the module at the SQL level 

   473   ** (i.e. "CREATE VIRTUAL TABLE tbl USING echo(arg1, arg2)"), then 

   474   ** the second argument is used as a table name. Attempt to create

   475   ** such a table with a single column, "logmsg". This table will

   476   ** be used to log calls to the xUpdate method. It will be deleted

   477   ** when the virtual table is DROPed.

   478   **

   479   ** Note: The main point of this is to test that we can drop tables

   480   ** from within an xDestroy method call.

   481   */

   482   if( rc==SQLITE_OK && argc==5 ){

   483     char *zSql;

   484     echo_vtab *pVtab = *(echo_vtab **)ppVtab;

   485     pVtab->zLogName = sqlite3MPrintf(0, "%s", argv[4]);

   486     zSql = sqlite3MPrintf(0, "CREATE TABLE %Q(logmsg)", pVtab->zLogName);

   487     rc = sqlite3_exec(db, zSql, 0, 0, 0);

   488     sqlite3_free(zSql);

   489     if( rc!=SQLITE_OK ){

   490       *pzErr = sqlite3DbStrDup(0, sqlite3_errmsg(db));

   491     }

   492   }

   493 

   494   if( *ppVtab && rc!=SQLITE_OK ){

   495     echoDestructor(*ppVtab);

   496     *ppVtab = 0;

   497   }

   498 

   499   if( rc==SQLITE_OK ){

   500     (*(echo_vtab**)ppVtab)->inTransaction = 1;

   501   }

   502 

   503   return rc;

   504 }

   505 

   506 /* 

   507 ** Echo virtual table module xConnect method.

   508 */

   509 static int echoConnect(

   510   sqlite3 *db,

   511   void *pAux,

   512   int argc, const char *const*argv,

   513   sqlite3_vtab **ppVtab,

   514   char **pzErr

   515 ){

   516   appendToEchoModule(((EchoModule *)pAux)->interp, "xConnect");

   517   return echoConstructor(db, pAux, argc, argv, ppVtab, pzErr);

   518 }

   519 

   520 /* 

   521 ** Echo virtual table module xDisconnect method.

   522 */

   523 static int echoDisconnect(sqlite3_vtab *pVtab){

   524   appendToEchoModule(((echo_vtab *)pVtab)->interp, "xDisconnect");

   525   return echoDestructor(pVtab);

   526 }

   527 

   528 /* 

   529 ** Echo virtual table module xDestroy method.

   530 */

   531 static int echoDestroy(sqlite3_vtab *pVtab){

   532   int rc = SQLITE_OK;

   533   echo_vtab *p = (echo_vtab *)pVtab;

   534   appendToEchoModule(((echo_vtab *)pVtab)->interp, "xDestroy");

   535 

   536   /* Drop the "log" table, if one exists (see echoCreate() for details) */

   537   if( p && p->zLogName ){

   538     char *zSql;

   539     zSql = sqlite3MPrintf(0, "DROP TABLE %Q", p->zLogName);

   540     rc = sqlite3_exec(p->db, zSql, 0, 0, 0);

   541     sqlite3_free(zSql);

   542   }

   543 

   544   if( rc==SQLITE_OK ){

   545     rc = echoDestructor(pVtab);

   546   }

   547   return rc;

   548 }

   549 

   550 /* 

   551 ** Echo virtual table module xOpen method.

   552 */

   553 static int echoOpen(sqlite3_vtab *pVTab, sqlite3_vtab_cursor **ppCursor){

   554   echo_cursor *pCur;

   555   if( simulateVtabError((echo_vtab *)pVTab, "xOpen") ){

   556     return SQLITE_ERROR;

   557   }

   558   pCur = sqlite3MallocZero(sizeof(echo_cursor));

   559   *ppCursor = (sqlite3_vtab_cursor *)pCur;

   560   return (pCur ? SQLITE_OK : SQLITE_NOMEM);

   561 }

   562 

   563 /* 

   564 ** Echo virtual table module xClose method.

   565 */

   566 static int echoClose(sqlite3_vtab_cursor *cur){

   567   int rc;

   568   echo_cursor *pCur = (echo_cursor *)cur;

   569   sqlite3_stmt *pStmt = pCur->pStmt;

   570   pCur->pStmt = 0;

   571   sqlite3_free(pCur);

   572   rc = sqlite3_finalize(pStmt);

   573   return rc;

   574 }

   575 

   576 /*

   577 ** Return non-zero if the cursor does not currently point to a valid record

   578 ** (i.e if the scan has finished), or zero otherwise.

   579 */

   580 static int echoEof(sqlite3_vtab_cursor *cur){

   581   return (((echo_cursor *)cur)->pStmt ? 0 : 1);

   582 }

   583 

   584 /* 

   585 ** Echo virtual table module xNext method.

   586 */

   587 static int echoNext(sqlite3_vtab_cursor *cur){

   588   int rc = SQLITE_OK;

   589   echo_cursor *pCur = (echo_cursor *)cur;

   590 

   591   if( simulateVtabError((echo_vtab *)(cur->pVtab), "xNext") ){

   592     return SQLITE_ERROR;

   593   }

   594 

   595   if( pCur->pStmt ){

   596     rc = sqlite3_step(pCur->pStmt);

   597     if( rc==SQLITE_ROW ){

   598       rc = SQLITE_OK;

   599     }else{

   600       rc = sqlite3_finalize(pCur->pStmt);

   601       pCur->pStmt = 0;

   602     }

   603   }

   604 

   605   return rc;

   606 }

   607 

   608 /* 

   609 ** Echo virtual table module xColumn method.

   610 */

   611 static int echoColumn(sqlite3_vtab_cursor *cur, sqlite3_context *ctx, int i){

   612   int iCol = i + 1;

   613   sqlite3_stmt *pStmt = ((echo_cursor *)cur)->pStmt;

   614 

   615   if( simulateVtabError((echo_vtab *)(cur->pVtab), "xColumn") ){

   616     return SQLITE_ERROR;

   617   }

   618 

   619   if( !pStmt ){

   620     sqlite3_result_null(ctx);

   621   }else{

   622     assert( sqlite3_data_count(pStmt)>iCol );

   623     sqlite3_result_value(ctx, sqlite3_column_value(pStmt, iCol));

   624   }

   625   return SQLITE_OK;

   626 }

   627 

   628 /* 

   629 ** Echo virtual table module xRowid method.

   630 */

   631 static int echoRowid(sqlite3_vtab_cursor *cur, sqlite_int64 *pRowid){

   632   sqlite3_stmt *pStmt = ((echo_cursor *)cur)->pStmt;

   633 

   634   if( simulateVtabError((echo_vtab *)(cur->pVtab), "xRowid") ){

   635     return SQLITE_ERROR;

   636   }

   637 

   638   *pRowid = sqlite3_column_int64(pStmt, 0);

   639   return SQLITE_OK;

   640 }

   641 

   642 /*

   643 ** Compute a simple hash of the null terminated string zString.

   644 **

   645 ** This module uses only sqlite3_index_info.idxStr, not 

   646 ** sqlite3_index_info.idxNum. So to test idxNum, when idxStr is set

   647 ** in echoBestIndex(), idxNum is set to the corresponding hash value.

   648 ** In echoFilter(), code assert()s that the supplied idxNum value is

   649 ** indeed the hash of the supplied idxStr.

   650 */

   651 static int hashString(const char *zString){

   652   int val = 0;

   653   int ii;

   654   for(ii=0; zString[ii]; ii++){

   655     val = (val << 3) + (int)zString[ii];

   656   }

   657   return val;

   658 }

   659 

   660 /* 

   661 ** Echo virtual table module xFilter method.

   662 */

   663 static int echoFilter(

   664   sqlite3_vtab_cursor *pVtabCursor, 

   665   int idxNum, const char *idxStr,

   666   int argc, sqlite3_value **argv

   667 ){

   668   int rc;

   669   int i;

   670 

   671   echo_cursor *pCur = (echo_cursor *)pVtabCursor;

   672   echo_vtab *pVtab = (echo_vtab *)pVtabCursor->pVtab;

   673   sqlite3 *db = pVtab->db;

   674 

   675   if( simulateVtabError(pVtab, "xFilter") ){

   676     return SQLITE_ERROR;

   677   }

   678 

   679   /* Check that idxNum matches idxStr */

   680   assert( idxNum==hashString(idxStr) );

   681 

   682   /* Log arguments to the ::echo_module Tcl variable */

   683   appendToEchoModule(pVtab->interp, "xFilter");

   684   appendToEchoModule(pVtab->interp, idxStr);

   685   for(i=0; i<argc; i++){

   686     appendToEchoModule(pVtab->interp, (const char*)sqlite3_value_text(argv[i]));

   687   }

   688 

   689   sqlite3_finalize(pCur->pStmt);

   690   pCur->pStmt = 0;

   691 

   692   /* Prepare the SQL statement created by echoBestIndex and bind the

   693   ** runtime parameters passed to this function to it.

   694   */

   695   rc = sqlite3_prepare(db, idxStr, -1, &pCur->pStmt, 0);

   696   assert( pCur->pStmt || rc!=SQLITE_OK );

   697   for(i=0; rc==SQLITE_OK && i<argc; i++){

   698     sqlite3_bind_value(pCur->pStmt, i+1, argv[i]);

   699   }

   700 

   701   /* If everything was successful, advance to the first row of the scan */

   702   if( rc==SQLITE_OK ){

   703     rc = echoNext(pVtabCursor);

   704   }

   705 

   706   return rc;

   707 }

   708 

   709 

   710 /*

   711 ** A helper function used by echoUpdate() and echoBestIndex() for

   712 ** manipulating strings in concert with the sqlite3_mprintf() function.

   713 **

   714 ** Parameter pzStr points to a pointer to a string allocated with

   715 ** sqlite3_mprintf. The second parameter, zAppend, points to another

   716 ** string. The two strings are concatenated together and *pzStr

   717 ** set to point at the result. The initial buffer pointed to by *pzStr

   718 ** is deallocated via sqlite3_free().

   719 **

   720 ** If the third argument, doFree, is true, then sqlite3_free() is

   721 ** also called to free the buffer pointed to by zAppend.

   722 */

   723 static void string_concat(char **pzStr, char *zAppend, int doFree, int *pRc){

   724   char *zIn = *pzStr;

   725   if( !zAppend && doFree && *pRc==SQLITE_OK ){

   726     *pRc = SQLITE_NOMEM;

   727   }

   728   if( *pRc!=SQLITE_OK ){

   729     sqlite3_free(zIn);

   730     zIn = 0;

   731   }else{

   732     if( zIn ){

   733       char *zTemp = zIn;

   734       zIn = sqlite3_mprintf("%s%s", zIn, zAppend);

   735       sqlite3_free(zTemp);

   736     }else{

   737       zIn = sqlite3_mprintf("%s", zAppend);

   738     }

   739     if( !zIn ){

   740       *pRc = SQLITE_NOMEM;

   741     }

   742   }

   743   *pzStr = zIn;

   744   if( doFree ){

   745     sqlite3_free(zAppend);

   746   }

   747 }

   748 

   749 /*

   750 ** The echo module implements the subset of query constraints and sort

   751 ** orders that may take advantage of SQLite indices on the underlying

   752 ** real table. For example, if the real table is declared as:

   753 **

   754 **     CREATE TABLE real(a, b, c);

   755 **     CREATE INDEX real_index ON real(b);

   756 **

   757 ** then the echo module handles WHERE or ORDER BY clauses that refer

   758 ** to the column "b", but not "a" or "c". If a multi-column index is

   759 ** present, only its left most column is considered. 

   760 **

   761 ** This xBestIndex method encodes the proposed search strategy as

   762 ** an SQL query on the real table underlying the virtual echo module 

   763 ** table and stores the query in sqlite3_index_info.idxStr. The SQL

   764 ** statement is of the form:

   765 **

   766 **   SELECT rowid, * FROM <real-table> ?<where-clause>? ?<order-by-clause>?

   767 **

   768 ** where the <where-clause> and <order-by-clause> are determined

   769 ** by the contents of the structure pointed to by the pIdxInfo argument.

   770 */

   771 static int echoBestIndex(sqlite3_vtab *tab, sqlite3_index_info *pIdxInfo){

   772   int ii;

   773   char *zQuery = 0;

   774   char *zNew;

   775   int nArg = 0;

   776   const char *zSep = "WHERE";

   777   echo_vtab *pVtab = (echo_vtab *)tab;

   778   sqlite3_stmt *pStmt = 0;

   779   Tcl_Interp *interp = pVtab->interp;

   780 

   781   int nRow;

   782   int useIdx = 0;

   783   int rc = SQLITE_OK;

   784   int useCost = 0;

   785   double cost;

   786   int isIgnoreUsable = 0;

   787   if( Tcl_GetVar(interp, "echo_module_ignore_usable", TCL_GLOBAL_ONLY) ){

   788     isIgnoreUsable = 1;

   789   }

   790 

   791   if( simulateVtabError(pVtab, "xBestIndex") ){

   792     return SQLITE_ERROR;

   793   }

   794 

   795   /* Determine the number of rows in the table and store this value in local

   796   ** variable nRow. The 'estimated-cost' of the scan will be the number of

   797   ** rows in the table for a linear scan, or the log (base 2) of the 

   798   ** number of rows if the proposed scan uses an index.  

   799   */

   800   if( Tcl_GetVar(interp, "echo_module_cost", TCL_GLOBAL_ONLY) ){

   801     cost = atof(Tcl_GetVar(interp, "echo_module_cost", TCL_GLOBAL_ONLY));

   802     useCost = 1;

   803   } else {

   804     zQuery = sqlite3_mprintf("SELECT count(*) FROM %Q", pVtab->zTableName);

   805     if( !zQuery ){

   806       return SQLITE_NOMEM;

   807     }

   808     rc = sqlite3_prepare(pVtab->db, zQuery, -1, &pStmt, 0);

   809     sqlite3_free(zQuery);

   810     if( rc!=SQLITE_OK ){

   811       return rc;

   812     }

   813     sqlite3_step(pStmt);

   814     nRow = sqlite3_column_int(pStmt, 0);

   815     rc = sqlite3_finalize(pStmt);

   816     if( rc!=SQLITE_OK ){

   817       return rc;

   818     }

   819   }

   820 

   821   zQuery = sqlite3_mprintf("SELECT rowid, * FROM %Q", pVtab->zTableName);

   822   if( !zQuery ){

   823     return SQLITE_NOMEM;

   824   }

   825   for(ii=0; ii<pIdxInfo->nConstraint; ii++){

   826     const struct sqlite3_index_constraint *pConstraint;

   827     struct sqlite3_index_constraint_usage *pUsage;

   828     int iCol;

   829 

   830     pConstraint = &pIdxInfo->aConstraint[ii];

   831     pUsage = &pIdxInfo->aConstraintUsage[ii];

   832 

   833     if( !isIgnoreUsable && !pConstraint->usable ) continue;

   834 

   835     iCol = pConstraint->iColumn;

   836     if( pVtab->aIndex[iCol] || iCol<0 ){

   837       char *zCol = pVtab->aCol[iCol];

   838       char *zOp = 0;

   839       useIdx = 1;

   840       if( iCol<0 ){

   841         zCol = "rowid";

   842       }

   843       switch( pConstraint->op ){

   844         case SQLITE_INDEX_CONSTRAINT_EQ:

   845           zOp = "="; break;

   846         case SQLITE_INDEX_CONSTRAINT_LT:

   847           zOp = "<"; break;

   848         case SQLITE_INDEX_CONSTRAINT_GT:

   849           zOp = ">"; break;

   850         case SQLITE_INDEX_CONSTRAINT_LE:

   851           zOp = "<="; break;

   852         case SQLITE_INDEX_CONSTRAINT_GE:

   853           zOp = ">="; break;

   854         case SQLITE_INDEX_CONSTRAINT_MATCH:

   855           zOp = "LIKE"; break;

   856       }

   857       if( zOp[0]=='L' ){

   858         zNew = sqlite3_mprintf(" %s %s LIKE (SELECT '%%'||?||'%%')", 

   859                                zSep, zCol);

   860       } else {

   861         zNew = sqlite3_mprintf(" %s %s %s ?", zSep, zCol, zOp);

   862       }

   863       string_concat(&zQuery, zNew, 1, &rc);

   864 

   865       zSep = "AND";

   866       pUsage->argvIndex = ++nArg;

   867       pUsage->omit = 1;

   868     }

   869   }

   870 

   871   /* If there is only one term in the ORDER BY clause, and it is

   872   ** on a column that this virtual table has an index for, then consume 

   873   ** the ORDER BY clause.

   874   */

   875   if( pIdxInfo->nOrderBy==1 && pVtab->aIndex[pIdxInfo->aOrderBy->iColumn] ){

   876     int iCol = pIdxInfo->aOrderBy->iColumn;

   877     char *zCol = pVtab->aCol[iCol];

   878     char *zDir = pIdxInfo->aOrderBy->desc?"DESC":"ASC";

   879     if( iCol<0 ){

   880       zCol = "rowid";

   881     }

   882     zNew = sqlite3_mprintf(" ORDER BY %s %s", zCol, zDir);

   883     string_concat(&zQuery, zNew, 1, &rc);

   884     pIdxInfo->orderByConsumed = 1;

   885   }

   886 

   887   appendToEchoModule(pVtab->interp, "xBestIndex");;

   888   appendToEchoModule(pVtab->interp, zQuery);

   889 

   890   if( !zQuery ){

   891     return rc;

   892   }

   893   pIdxInfo->idxNum = hashString(zQuery);

   894   pIdxInfo->idxStr = zQuery;

   895   pIdxInfo->needToFreeIdxStr = 1;

   896   if (useCost) {

   897     pIdxInfo->estimatedCost = cost;

   898   } else if( useIdx ){

   899     /* Approximation of log2(nRow). */

   900     for( ii=0; ii<(sizeof(int)*8); ii++ ){

   901       if( nRow & (1<<ii) ){

   902         pIdxInfo->estimatedCost = (double)ii;

   903       }

   904     }

   905   } else {

   906     pIdxInfo->estimatedCost = (double)nRow;

   907   }

   908   return rc;

   909 }

   910 

   911 /*

   912 ** The xUpdate method for echo module virtual tables.

   913 ** 

   914 **    apData[0]  apData[1]  apData[2..]

   915 **

   916 **    INTEGER                              DELETE            

   917 **

   918 **    INTEGER    NULL       (nCol args)    UPDATE (do not set rowid)

   919 **    INTEGER    INTEGER    (nCol args)    UPDATE (with SET rowid = <arg1>)

   920 **

   921 **    NULL       NULL       (nCol args)    INSERT INTO (automatic rowid value)

   922 **    NULL       INTEGER    (nCol args)    INSERT (incl. rowid value)

   923 **

   924 */

   925 int echoUpdate(

   926   sqlite3_vtab *tab, 

   927   int nData, 

   928   sqlite3_value **apData, 

   929   sqlite_int64 *pRowid

   930 ){

   931   echo_vtab *pVtab = (echo_vtab *)tab;

   932   sqlite3 *db = pVtab->db;

   933   int rc = SQLITE_OK;

   934 

   935   sqlite3_stmt *pStmt;

   936   char *z = 0;               /* SQL statement to execute */

   937   int bindArgZero = 0;       /* True to bind apData[0] to sql var no. nData */

   938   int bindArgOne = 0;        /* True to bind apData[1] to sql var no. 1 */

   939   int i;                     /* Counter variable used by for loops */

   940 

   941   assert( nData==pVtab->nCol+2 || nData==1 );

   942 

   943   /* Ticket #3083 - make sure we always start a transaction prior to

   944   ** making any changes to a virtual table */

   945   assert( pVtab->inTransaction );

   946 

   947   if( simulateVtabError(pVtab, "xUpdate") ){

   948     return SQLITE_ERROR;

   949   }

   950 

   951   /* If apData[0] is an integer and nData>1 then do an UPDATE */

   952   if( nData>1 && sqlite3_value_type(apData[0])==SQLITE_INTEGER ){

   953     char *zSep = " SET";

   954     z = sqlite3_mprintf("UPDATE %Q", pVtab->zTableName);

   955     if( !z ){

   956       rc = SQLITE_NOMEM;

   957     }

   958 

   959     bindArgOne = (apData[1] && sqlite3_value_type(apData[1])==SQLITE_INTEGER);

   960     bindArgZero = 1;

   961 

   962     if( bindArgOne ){

   963        string_concat(&z, " SET rowid=?1 ", 0, &rc);

   964        zSep = ",";

   965     }

   966     for(i=2; i<nData; i++){

   967       if( apData[i]==0 ) continue;

   968       string_concat(&z, sqlite3_mprintf(

   969           "%s %Q=?%d", zSep, pVtab->aCol[i-2], i), 1, &rc);

   970       zSep = ",";

   971     }

   972     string_concat(&z, sqlite3_mprintf(" WHERE rowid=?%d", nData), 1, &rc);

   973   }

   974 

   975   /* If apData[0] is an integer and nData==1 then do a DELETE */

   976   else if( nData==1 && sqlite3_value_type(apData[0])==SQLITE_INTEGER ){

   977     z = sqlite3_mprintf("DELETE FROM %Q WHERE rowid = ?1", pVtab->zTableName);

   978     if( !z ){

   979       rc = SQLITE_NOMEM;

   980     }

   981     bindArgZero = 1;

   982   }

   983 

   984   /* If the first argument is NULL and there are more than two args, INSERT */

   985   else if( nData>2 && sqlite3_value_type(apData[0])==SQLITE_NULL ){

   986     int ii;

   987     char *zInsert = 0;

   988     char *zValues = 0;

   989   

   990     zInsert = sqlite3_mprintf("INSERT INTO %Q (", pVtab->zTableName);

   991     if( !zInsert ){

   992       rc = SQLITE_NOMEM;

   993     }

   994     if( sqlite3_value_type(apData[1])==SQLITE_INTEGER ){

   995       bindArgOne = 1;

   996       zValues = sqlite3_mprintf("?");

   997       string_concat(&zInsert, "rowid", 0, &rc);

   998     }

   999 

  1000     assert((pVtab->nCol+2)==nData);

  1001     for(ii=2; ii<nData; ii++){

  1002       string_concat(&zInsert, 

  1003           sqlite3_mprintf("%s%Q", zValues?", ":"", pVtab->aCol[ii-2]), 1, &rc);

  1004       string_concat(&zValues, 

  1005           sqlite3_mprintf("%s?%d", zValues?", ":"", ii), 1, &rc);

  1006     }

  1007 

  1008     string_concat(&z, zInsert, 1, &rc);

  1009     string_concat(&z, ") VALUES(", 0, &rc);

  1010     string_concat(&z, zValues, 1, &rc);

  1011     string_concat(&z, ")", 0, &rc);

  1012   }

  1013 

  1014   /* Anything else is an error */

  1015   else{

  1016     assert(0);

  1017     return SQLITE_ERROR;

  1018   }

  1019 

  1020   if( rc==SQLITE_OK ){

  1021     rc = sqlite3_prepare(db, z, -1, &pStmt, 0);

  1022   }

  1023   assert( rc!=SQLITE_OK || pStmt );

  1024   sqlite3_free(z);

  1025   if( rc==SQLITE_OK ) {

  1026     if( bindArgZero ){

  1027       sqlite3_bind_value(pStmt, nData, apData[0]);

  1028     }

  1029     if( bindArgOne ){

  1030       sqlite3_bind_value(pStmt, 1, apData[1]);

  1031     }

  1032     for(i=2; i<nData && rc==SQLITE_OK; i++){

  1033       if( apData[i] ) rc = sqlite3_bind_value(pStmt, i, apData[i]);

  1034     }

  1035     if( rc==SQLITE_OK ){

  1036       sqlite3_step(pStmt);

  1037       rc = sqlite3_finalize(pStmt);

  1038     }else{

  1039       sqlite3_finalize(pStmt);

  1040     }

  1041   }

  1042 

  1043   if( pRowid && rc==SQLITE_OK ){

  1044     *pRowid = sqlite3_last_insert_rowid(db);

  1045   }

  1046   if( rc!=SQLITE_OK ){

  1047     tab->zErrMsg = sqlite3_mprintf("echo-vtab-error: %s", sqlite3_errmsg(db));

  1048   }

  1049 

  1050   return rc;

  1051 }

  1052 

  1053 /*

  1054 ** xBegin, xSync, xCommit and xRollback callbacks for echo module

  1055 ** virtual tables. Do nothing other than add the name of the callback

  1056 ** to the $::echo_module Tcl variable.

  1057 */

  1058 static int echoTransactionCall(sqlite3_vtab *tab, const char *zCall){

  1059   char *z;

  1060   echo_vtab *pVtab = (echo_vtab *)tab;

  1061   z = sqlite3_mprintf("echo(%s)", pVtab->zTableName);

  1062   if( z==0 ) return SQLITE_NOMEM;

  1063   appendToEchoModule(pVtab->interp, zCall);

  1064   appendToEchoModule(pVtab->interp, z);

  1065   sqlite3_free(z);

  1066   return SQLITE_OK;

  1067 }

  1068 static int echoBegin(sqlite3_vtab *tab){

  1069   int rc;

  1070   echo_vtab *pVtab = (echo_vtab *)tab;

  1071   Tcl_Interp *interp = pVtab->interp;

  1072   const char *zVal; 

  1073 

  1074   /* Ticket #3083 - do not start a transaction if we are already in

  1075   ** a transaction */

  1076   assert( !pVtab->inTransaction );

  1077 

  1078   if( simulateVtabError(pVtab, "xBegin") ){

  1079     return SQLITE_ERROR;

  1080   }

  1081 

  1082   rc = echoTransactionCall(tab, "xBegin");

  1083 

  1084   if( rc==SQLITE_OK ){

  1085     /* Check if the $::echo_module_begin_fail variable is defined. If it is,

  1086     ** and it is set to the name of the real table underlying this virtual

  1087     ** echo module table, then cause this xSync operation to fail.

  1088     */

  1089     zVal = Tcl_GetVar(interp, "echo_module_begin_fail", TCL_GLOBAL_ONLY);

  1090     if( zVal && 0==strcmp(zVal, pVtab->zTableName) ){

  1091       rc = SQLITE_ERROR;

  1092     }

  1093   }

  1094   if( rc==SQLITE_OK ){

  1095     pVtab->inTransaction = 1;

  1096   }

  1097   return rc;

  1098 }

  1099 static int echoSync(sqlite3_vtab *tab){

  1100   int rc;

  1101   echo_vtab *pVtab = (echo_vtab *)tab;

  1102   Tcl_Interp *interp = pVtab->interp;

  1103   const char *zVal; 

  1104 

  1105   /* Ticket #3083 - Only call xSync if we have previously started a

  1106   ** transaction */

  1107   assert( pVtab->inTransaction );

  1108 

  1109   if( simulateVtabError(pVtab, "xSync") ){

  1110     return SQLITE_ERROR;

  1111   }

  1112 

  1113   rc = echoTransactionCall(tab, "xSync");

  1114 

  1115   if( rc==SQLITE_OK ){

  1116     /* Check if the $::echo_module_sync_fail variable is defined. If it is,

  1117     ** and it is set to the name of the real table underlying this virtual

  1118     ** echo module table, then cause this xSync operation to fail.

  1119     */

  1120     zVal = Tcl_GetVar(interp, "echo_module_sync_fail", TCL_GLOBAL_ONLY);

  1121     if( zVal && 0==strcmp(zVal, pVtab->zTableName) ){

  1122       rc = -1;

  1123     }

  1124   }

  1125   return rc;

  1126 }

  1127 static int echoCommit(sqlite3_vtab *tab){

  1128   echo_vtab *pVtab = (echo_vtab*)tab;

  1129   int rc;

  1130 

  1131   /* Ticket #3083 - Only call xCommit if we have previously started

  1132   ** a transaction */

  1133   assert( pVtab->inTransaction );

  1134 

  1135   if( simulateVtabError(pVtab, "xCommit") ){