4 ** The author disclaims copyright to this source code. In place of
5 ** a legal notice, here is a blessing:
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.
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.
16 #include "sqliteInt.h"
17 #include "tclsqlite.h"
21 #ifndef SQLITE_OMIT_VIRTUALTABLE
23 typedef struct echo_vtab echo_vtab
;
24 typedef struct echo_cursor echo_cursor
;
27 ** The test module defined in this file uses four global Tcl variables to
28 ** communicate with test-scripts:
31 ** $::echo_module_sync_fail
32 ** $::echo_module_begin_fail
33 ** $::echo_module_cost
35 ** The variable ::echo_module is a list. Each time one of the following
36 ** methods is called, one or more elements are appended to the list.
37 ** This is used for automated testing of virtual table modules.
39 ** The ::echo_module_sync_fail variable is set by test scripts and read
40 ** by code in this file. If it is set to the name of a real table in the
41 ** the database, then all xSync operations on echo virtual tables that
42 ** use the named table as a backing store will fail.
46 ** Errors can be provoked within the following echo virtual table methods:
48 ** xBestIndex xOpen xFilter xNext
49 ** xColumn xRowid xUpdate xSync
52 ** This is done by setting the global tcl variable:
54 ** echo_module_fail($method,$tbl)
56 ** where $method is set to the name of the virtual table method to fail
57 ** (i.e. "xBestIndex") and $tbl is the name of the table being echoed (not
58 ** the name of the virtual table, the name of the underlying real table).
62 ** An echo virtual-table object.
64 ** echo.vtab.aIndex is an array of booleans. The nth entry is true if
65 ** the nth column of the real table is the left-most column of an index
66 ** (implicit or otherwise). In other words, if SQLite can optimize
67 ** a query like "SELECT * FROM real_table WHERE col = ?".
69 ** Member variable aCol[] contains copies of the column names of the real
74 Tcl_Interp
*interp
; /* Tcl interpreter containing debug variables */
75 sqlite3
*db
; /* Database connection */
78 int inTransaction
; /* True if within a transaction */
79 char *zThis
; /* Name of the echo table */
80 char *zTableName
; /* Name of the real table */
81 char *zLogName
; /* Name of the log table */
82 int nCol
; /* Number of columns in the real table */
83 int *aIndex
; /* Array of size nCol. True if column has an index */
84 char **aCol
; /* Array of size nCol. Column names */
87 /* An echo cursor object */
89 sqlite3_vtab_cursor base
;
93 static int simulateVtabError(echo_vtab
*p
, const char *zMethod
){
97 sqlite3_snprintf(127, zVarname
, "echo_module_fail(%s,%s)", zMethod
, p
->zTableName
);
98 zErr
= Tcl_GetVar(p
->interp
, zVarname
, TCL_GLOBAL_ONLY
);
100 p
->base
.zErrMsg
= sqlite3_mprintf("echo-vtab-error: %s", zErr
);
106 ** Convert an SQL-style quoted string into a normal string by removing
107 ** the quote characters. The conversion is done in-place. If the
108 ** input does not begin with a quote character, then this routine
118 static void dequoteString(char *z
){
126 case '`': break; /* For MySQL compatibility */
127 case '[': quote
= ']'; break; /* For MS SqlServer compatibility */
130 for(i
=1, j
=0; z
[i
]; i
++){
146 ** Retrieve the column names for the table named zTab via database
147 ** connection db. SQLITE_OK is returned on success, or an sqlite error
150 ** If successful, the number of columns is written to *pnCol. *paCol is
151 ** set to point at sqlite3_malloc()'d space containing the array of
152 ** nCol column names. The caller is responsible for calling sqlite3_free
155 static int getColumnNames(
163 sqlite3_stmt
*pStmt
= 0;
167 /* Prepare the statement "SELECT * FROM <tbl>". The column names
168 ** of the result set of the compiled SELECT will be the same as
169 ** the column names of table <tbl>.
171 zSql
= sqlite3_mprintf("SELECT * FROM %Q", zTab
);
176 rc
= sqlite3_prepare(db
, zSql
, -1, &pStmt
, 0);
183 nCol
= sqlite3_column_count(pStmt
);
185 /* Figure out how much space to allocate for the array of column names
186 ** (including space for the strings themselves). Then allocate it.
188 nBytes
= sizeof(char *) * nCol
;
189 for(ii
=0; ii
<nCol
; ii
++){
190 const char *zName
= sqlite3_column_name(pStmt
, ii
);
195 nBytes
+= (int)strlen(zName
)+1;
197 aCol
= (char **)sqlite3MallocZero(nBytes
);
203 /* Copy the column names into the allocated space and set up the
204 ** pointers in the aCol[] array.
206 zSpace
= (char *)(&aCol
[nCol
]);
207 for(ii
=0; ii
<nCol
; ii
++){
209 sqlite3_snprintf(nBytes
, zSpace
, "%s", sqlite3_column_name(pStmt
,ii
));
210 zSpace
+= (int)strlen(zSpace
) + 1;
212 assert( (zSpace
-nBytes
)==(char *)aCol
);
219 sqlite3_finalize(pStmt
);
224 ** Parameter zTab is the name of a table in database db with nCol
225 ** columns. This function allocates an array of integers nCol in
226 ** size and populates it according to any implicit or explicit
227 ** indices on table zTab.
229 ** If successful, SQLITE_OK is returned and *paIndex set to point
230 ** at the allocated array. Otherwise, an error code is returned.
232 ** See comments associated with the member variable aIndex above
233 ** "struct echo_vtab" for details of the contents of the array.
235 static int getIndexArray(
236 sqlite3
*db
, /* Database connection */
237 const char *zTab
, /* Name of table in database db */
241 sqlite3_stmt
*pStmt
= 0;
246 /* Allocate space for the index array */
247 aIndex
= (int *)sqlite3MallocZero(sizeof(int) * nCol
);
250 goto get_index_array_out
;
253 /* Compile an sqlite pragma to loop through all indices on table zTab */
254 zSql
= sqlite3_mprintf("PRAGMA index_list(%s)", zTab
);
257 goto get_index_array_out
;
259 rc
= sqlite3_prepare(db
, zSql
, -1, &pStmt
, 0);
262 /* For each index, figure out the left-most column and set the
263 ** corresponding entry in aIndex[] to 1.
265 while( pStmt
&& sqlite3_step(pStmt
)==SQLITE_ROW
){
266 const char *zIdx
= (const char *)sqlite3_column_text(pStmt
, 1);
267 sqlite3_stmt
*pStmt2
= 0;
268 if( zIdx
==0 ) continue;
269 zSql
= sqlite3_mprintf("PRAGMA index_info(%s)", zIdx
);
272 goto get_index_array_out
;
274 rc
= sqlite3_prepare(db
, zSql
, -1, &pStmt2
, 0);
276 if( pStmt2
&& sqlite3_step(pStmt2
)==SQLITE_ROW
){
277 int cid
= sqlite3_column_int(pStmt2
, 1);
278 assert( cid
>=0 && cid
<nCol
);
282 rc
= sqlite3_finalize(pStmt2
);
285 goto get_index_array_out
;
292 int rc2
= sqlite3_finalize(pStmt
);
298 sqlite3_free(aIndex
);
306 ** Global Tcl variable $echo_module is a list. This routine appends
307 ** the string element zArg to that list in interpreter interp.
309 static void appendToEchoModule(Tcl_Interp
*interp
, const char *zArg
){
310 int flags
= (TCL_APPEND_VALUE
| TCL_LIST_ELEMENT
| TCL_GLOBAL_ONLY
);
311 Tcl_SetVar(interp
, "echo_module", (zArg
?zArg
:""), flags
);
315 ** This function is called from within the echo-modules xCreate and
316 ** xConnect methods. The argc and argv arguments are copies of those
317 ** passed to the calling method. This function is responsible for
318 ** calling sqlite3_declare_vtab() to declare the schema of the virtual
319 ** table being created or connected.
321 ** If the constructor was passed just one argument, i.e.:
323 ** CREATE TABLE t1 AS echo(t2);
325 ** Then t2 is assumed to be the name of a *real* database table. The
326 ** schema of the virtual table is declared by passing a copy of the
327 ** CREATE TABLE statement for the real table to sqlite3_declare_vtab().
328 ** Hence, the virtual table should have exactly the same column names and
329 ** types as the real table.
331 static int echoDeclareVtab(
337 if( pVtab
->zTableName
){
338 sqlite3_stmt
*pStmt
= 0;
339 rc
= sqlite3_prepare(db
,
340 "SELECT sql FROM sqlite_schema WHERE type = 'table' AND name = ?",
343 sqlite3_bind_text(pStmt
, 1, pVtab
->zTableName
, -1, 0);
344 if( sqlite3_step(pStmt
)==SQLITE_ROW
){
346 const char *zCreateTable
= (const char *)sqlite3_column_text(pStmt
, 0);
347 rc
= sqlite3_declare_vtab(db
, zCreateTable
);
348 rc2
= sqlite3_finalize(pStmt
);
353 rc
= sqlite3_finalize(pStmt
);
359 rc
= getColumnNames(db
, pVtab
->zTableName
, &pVtab
->aCol
, &pVtab
->nCol
);
362 rc
= getIndexArray(db
, pVtab
->zTableName
, pVtab
->nCol
, &pVtab
->aIndex
);
371 ** This function frees all runtime structures associated with the virtual
374 static int echoDestructor(sqlite3_vtab
*pVtab
){
375 echo_vtab
*p
= (echo_vtab
*)pVtab
;
376 sqlite3_free(p
->aIndex
);
377 sqlite3_free(p
->aCol
);
378 sqlite3_free(p
->zThis
);
379 sqlite3_free(p
->zTableName
);
380 sqlite3_free(p
->zLogName
);
385 typedef struct EchoModule EchoModule
;
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
396 static int echoConstructor(
399 int argc
, const char *const*argv
,
400 sqlite3_vtab
**ppVtab
,
407 /* Allocate the sqlite3_vtab/echo_vtab structure itself */
408 pVtab
= sqlite3MallocZero( sizeof(*pVtab
) );
412 pVtab
->interp
= ((EchoModule
*)pAux
)->interp
;
415 /* Allocate echo_vtab.zThis */
416 pVtab
->zThis
= sqlite3_mprintf("%s", argv
[2]);
418 echoDestructor((sqlite3_vtab
*)pVtab
);
422 /* Allocate echo_vtab.zTableName */
424 pVtab
->zTableName
= sqlite3_mprintf("%s", argv
[3]);
425 dequoteString(pVtab
->zTableName
);
426 if( pVtab
->zTableName
&& pVtab
->zTableName
[0]=='*' ){
427 char *z
= sqlite3_mprintf("%s%s", argv
[2], &(pVtab
->zTableName
[1]));
428 sqlite3_free(pVtab
->zTableName
);
429 pVtab
->zTableName
= z
;
430 pVtab
->isPattern
= 1;
432 if( !pVtab
->zTableName
){
433 echoDestructor((sqlite3_vtab
*)pVtab
);
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
]);
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.
447 rc
= echoDeclareVtab(pVtab
, db
);
449 echoDestructor((sqlite3_vtab
*)pVtab
);
453 /* Success. Set *ppVtab and return */
454 *ppVtab
= &pVtab
->base
;
459 ** Echo virtual table module xCreate method.
461 static int echoCreate(
464 int argc
, const char *const*argv
,
465 sqlite3_vtab
**ppVtab
,
469 appendToEchoModule(((EchoModule
*)pAux
)->interp
, "xCreate");
470 rc
= echoConstructor(db
, pAux
, argc
, argv
, ppVtab
, pzErr
);
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.
479 ** Note: The main point of this is to test that we can drop tables
480 ** from within an xDestroy method call.
482 if( rc
==SQLITE_OK
&& argc
==5 ){
484 echo_vtab
*pVtab
= *(echo_vtab
**)ppVtab
;
485 pVtab
->zLogName
= sqlite3_mprintf("%s", argv
[4]);
486 zSql
= sqlite3_mprintf("CREATE TABLE %Q(logmsg)", pVtab
->zLogName
);
487 rc
= sqlite3_exec(db
, zSql
, 0, 0, 0);
490 *pzErr
= sqlite3_mprintf("%s", sqlite3_errmsg(db
));
494 if( *ppVtab
&& rc
!=SQLITE_OK
){
495 echoDestructor(*ppVtab
);
500 (*(echo_vtab
**)ppVtab
)->inTransaction
= 1;
507 ** Echo virtual table module xConnect method.
509 static int echoConnect(
512 int argc
, const char *const*argv
,
513 sqlite3_vtab
**ppVtab
,
516 appendToEchoModule(((EchoModule
*)pAux
)->interp
, "xConnect");
517 return echoConstructor(db
, pAux
, argc
, argv
, ppVtab
, pzErr
);
521 ** Echo virtual table module xDisconnect method.
523 static int echoDisconnect(sqlite3_vtab
*pVtab
){
524 appendToEchoModule(((echo_vtab
*)pVtab
)->interp
, "xDisconnect");
525 return echoDestructor(pVtab
);
529 ** Echo virtual table module xDestroy method.
531 static int echoDestroy(sqlite3_vtab
*pVtab
){
533 echo_vtab
*p
= (echo_vtab
*)pVtab
;
534 appendToEchoModule(((echo_vtab
*)pVtab
)->interp
, "xDestroy");
536 /* Drop the "log" table, if one exists (see echoCreate() for details) */
537 if( p
&& p
->zLogName
){
539 zSql
= sqlite3_mprintf("DROP TABLE %Q", p
->zLogName
);
540 rc
= sqlite3_exec(p
->db
, zSql
, 0, 0, 0);
545 rc
= echoDestructor(pVtab
);
551 ** Echo virtual table module xOpen method.
553 static int echoOpen(sqlite3_vtab
*pVTab
, sqlite3_vtab_cursor
**ppCursor
){
555 if( simulateVtabError((echo_vtab
*)pVTab
, "xOpen") ){
558 pCur
= sqlite3MallocZero(sizeof(echo_cursor
));
559 *ppCursor
= (sqlite3_vtab_cursor
*)pCur
;
560 return (pCur
? SQLITE_OK
: SQLITE_NOMEM
);
564 ** Echo virtual table module xClose method.
566 static int echoClose(sqlite3_vtab_cursor
*cur
){
568 echo_cursor
*pCur
= (echo_cursor
*)cur
;
569 sqlite3_stmt
*pStmt
= pCur
->pStmt
;
572 rc
= sqlite3_finalize(pStmt
);
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.
580 static int echoEof(sqlite3_vtab_cursor
*cur
){
581 return (((echo_cursor
*)cur
)->pStmt
? 0 : 1);
585 ** Echo virtual table module xNext method.
587 static int echoNext(sqlite3_vtab_cursor
*cur
){
589 echo_cursor
*pCur
= (echo_cursor
*)cur
;
591 if( simulateVtabError((echo_vtab
*)(cur
->pVtab
), "xNext") ){
596 rc
= sqlite3_step(pCur
->pStmt
);
597 if( rc
==SQLITE_ROW
){
600 rc
= sqlite3_finalize(pCur
->pStmt
);
609 ** Echo virtual table module xColumn method.
611 static int echoColumn(sqlite3_vtab_cursor
*cur
, sqlite3_context
*ctx
, int i
){
613 sqlite3_stmt
*pStmt
= ((echo_cursor
*)cur
)->pStmt
;
615 if( simulateVtabError((echo_vtab
*)(cur
->pVtab
), "xColumn") ){
620 sqlite3_result_null(ctx
);
622 assert( sqlite3_data_count(pStmt
)>iCol
);
623 sqlite3_result_value(ctx
, sqlite3_column_value(pStmt
, iCol
));
629 ** Echo virtual table module xRowid method.
631 static int echoRowid(sqlite3_vtab_cursor
*cur
, sqlite_int64
*pRowid
){
632 sqlite3_stmt
*pStmt
= ((echo_cursor
*)cur
)->pStmt
;
634 if( simulateVtabError((echo_vtab
*)(cur
->pVtab
), "xRowid") ){
638 *pRowid
= sqlite3_column_int64(pStmt
, 0);
643 ** Compute a simple hash of the null terminated string zString.
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.
651 static int hashString(const char *zString
){
654 for(ii
=0; zString
[ii
]; ii
++){
655 val
= (val
<< 3) + (int)zString
[ii
];
657 return (int)(val
&0x7fffffff);
661 ** Echo virtual table module xFilter method.
663 static int echoFilter(
664 sqlite3_vtab_cursor
*pVtabCursor
,
665 int idxNum
, const char *idxStr
,
666 int argc
, sqlite3_value
**argv
671 echo_cursor
*pCur
= (echo_cursor
*)pVtabCursor
;
672 echo_vtab
*pVtab
= (echo_vtab
*)pVtabCursor
->pVtab
;
673 sqlite3
*db
= pVtab
->db
;
675 if( simulateVtabError(pVtab
, "xFilter") ){
679 /* Check that idxNum matches idxStr */
680 assert( idxNum
==hashString(idxStr
) );
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
]));
689 sqlite3_finalize(pCur
->pStmt
);
692 /* Prepare the SQL statement created by echoBestIndex and bind the
693 ** runtime parameters passed to this function to it.
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 rc
= sqlite3_bind_value(pCur
->pStmt
, i
+1, argv
[i
]);
701 /* If everything was successful, advance to the first row of the scan */
703 rc
= echoNext(pVtabCursor
);
711 ** A helper function used by echoUpdate() and echoBestIndex() for
712 ** manipulating strings in concert with the sqlite3_mprintf() function.
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().
720 ** If the third argument, doFree, is true, then sqlite3_free() is
721 ** also called to free the buffer pointed to by zAppend.
723 static void string_concat(char **pzStr
, char *zAppend
, int doFree
, int *pRc
){
725 if( !zAppend
&& doFree
&& *pRc
==SQLITE_OK
){
728 if( *pRc
!=SQLITE_OK
){
734 zIn
= sqlite3_mprintf("%s%s", zIn
, zAppend
);
737 zIn
= sqlite3_mprintf("%s", zAppend
);
745 sqlite3_free(zAppend
);
750 ** This function returns a pointer to an sqlite3_malloc()ed buffer
751 ** containing the select-list (the thing between keywords SELECT and FROM)
752 ** to query the underlying real table with for the scan described by
753 ** argument pIdxInfo.
755 ** If the current SQLite version is earlier than 3.10.0, this is just "*"
756 ** (select all columns). Or, for version 3.10.0 and greater, the list of
757 ** columns identified by the pIdxInfo->colUsed mask.
759 static char *echoSelectList(echo_vtab
*pTab
, sqlite3_index_info
*pIdxInfo
){
761 if( sqlite3_libversion_number()<3010000 ){
762 zRet
= sqlite3_mprintf(", *");
765 for(i
=0; i
<pTab
->nCol
; i
++){
766 if( pIdxInfo
->colUsed
& ((sqlite3_uint64
)1 << (i
>=63 ? 63 : i
)) ){
767 zRet
= sqlite3_mprintf("%z, %s", zRet
, pTab
->aCol
[i
]);
769 zRet
= sqlite3_mprintf("%z, NULL", zRet
);
778 ** The echo module implements the subset of query constraints and sort
779 ** orders that may take advantage of SQLite indices on the underlying
780 ** real table. For example, if the real table is declared as:
782 ** CREATE TABLE real(a, b, c);
783 ** CREATE INDEX real_index ON real(b);
785 ** then the echo module handles WHERE or ORDER BY clauses that refer
786 ** to the column "b", but not "a" or "c". If a multi-column index is
787 ** present, only its left most column is considered.
789 ** This xBestIndex method encodes the proposed search strategy as
790 ** an SQL query on the real table underlying the virtual echo module
791 ** table and stores the query in sqlite3_index_info.idxStr. The SQL
792 ** statement is of the form:
794 ** SELECT rowid, * FROM <real-table> ?<where-clause>? ?<order-by-clause>?
796 ** where the <where-clause> and <order-by-clause> are determined
797 ** by the contents of the structure pointed to by the pIdxInfo argument.
799 static int echoBestIndex(sqlite3_vtab
*tab
, sqlite3_index_info
*pIdxInfo
){
805 const char *zSep
= "WHERE";
806 echo_vtab
*pVtab
= (echo_vtab
*)tab
;
807 sqlite3_stmt
*pStmt
= 0;
808 Tcl_Interp
*interp
= pVtab
->interp
;
815 int isIgnoreUsable
= 0;
816 if( Tcl_GetVar(interp
, "echo_module_ignore_usable", TCL_GLOBAL_ONLY
) ){
820 if( simulateVtabError(pVtab
, "xBestIndex") ){
824 /* Determine the number of rows in the table and store this value in local
825 ** variable nRow. The 'estimated-cost' of the scan will be the number of
826 ** rows in the table for a linear scan, or the log (base 2) of the
827 ** number of rows if the proposed scan uses an index.
829 if( Tcl_GetVar(interp
, "echo_module_cost", TCL_GLOBAL_ONLY
) ){
830 cost
= atof(Tcl_GetVar(interp
, "echo_module_cost", TCL_GLOBAL_ONLY
));
833 zQuery
= sqlite3_mprintf("SELECT count(*) FROM %Q", pVtab
->zTableName
);
837 rc
= sqlite3_prepare(pVtab
->db
, zQuery
, -1, &pStmt
, 0);
838 sqlite3_free(zQuery
);
843 nRow
= sqlite3_column_int(pStmt
, 0);
844 rc
= sqlite3_finalize(pStmt
);
850 zCol
= echoSelectList(pVtab
, pIdxInfo
);
851 if( !zCol
) return SQLITE_NOMEM
;
852 zQuery
= sqlite3_mprintf("SELECT rowid%z FROM %Q", zCol
, pVtab
->zTableName
);
853 if( !zQuery
) return SQLITE_NOMEM
;
855 for(ii
=0; ii
<pIdxInfo
->nConstraint
; ii
++){
856 const struct sqlite3_index_constraint
*pConstraint
;
857 struct sqlite3_index_constraint_usage
*pUsage
;
860 pConstraint
= &pIdxInfo
->aConstraint
[ii
];
861 pUsage
= &pIdxInfo
->aConstraintUsage
[ii
];
863 if( !isIgnoreUsable
&& !pConstraint
->usable
) continue;
865 iCol
= pConstraint
->iColumn
;
866 if( iCol
<0 || pVtab
->aIndex
[iCol
] ){
867 char *zNewCol
= iCol
>=0 ? pVtab
->aCol
[iCol
] : "rowid";
870 switch( pConstraint
->op
){
871 case SQLITE_INDEX_CONSTRAINT_EQ
:
873 case SQLITE_INDEX_CONSTRAINT_LT
:
875 case SQLITE_INDEX_CONSTRAINT_GT
:
877 case SQLITE_INDEX_CONSTRAINT_LE
:
879 case SQLITE_INDEX_CONSTRAINT_GE
:
881 case SQLITE_INDEX_CONSTRAINT_MATCH
:
882 /* Purposely translate the MATCH operator into a LIKE, which
883 ** will be used by the next block of code to construct a new
884 ** query. It should also be noted here that the next block
885 ** of code requires the first letter of this operator to be
886 ** in upper-case to trigger the special MATCH handling (i.e.
887 ** wrapping the bound parameter with literal '%'s).
890 case SQLITE_INDEX_CONSTRAINT_LIKE
:
892 case SQLITE_INDEX_CONSTRAINT_GLOB
:
894 case SQLITE_INDEX_CONSTRAINT_REGEXP
:
895 zOp
= "regexp"; break;
899 zNew
= sqlite3_mprintf(" %s %s LIKE (SELECT '%%'||?||'%%')",
902 zNew
= sqlite3_mprintf(" %s %s %s ?", zSep
, zNewCol
, zOp
);
904 string_concat(&zQuery
, zNew
, 1, &rc
);
906 pUsage
->argvIndex
= ++nArg
;
912 /* If there is only one term in the ORDER BY clause, and it is
913 ** on a column that this virtual table has an index for, then consume
914 ** the ORDER BY clause.
916 if( pIdxInfo
->nOrderBy
==1 && (
917 pIdxInfo
->aOrderBy
->iColumn
<0 ||
918 pVtab
->aIndex
[pIdxInfo
->aOrderBy
->iColumn
]) ){
919 int iCol
= pIdxInfo
->aOrderBy
->iColumn
;
920 char *zNewCol
= iCol
>=0 ? pVtab
->aCol
[iCol
] : "rowid";
921 char *zDir
= pIdxInfo
->aOrderBy
->desc
?"DESC":"ASC";
922 zNew
= sqlite3_mprintf(" ORDER BY %s %s", zNewCol
, zDir
);
923 string_concat(&zQuery
, zNew
, 1, &rc
);
924 pIdxInfo
->orderByConsumed
= 1;
927 appendToEchoModule(pVtab
->interp
, "xBestIndex");;
928 appendToEchoModule(pVtab
->interp
, zQuery
);
933 pIdxInfo
->idxNum
= hashString(zQuery
);
934 pIdxInfo
->idxStr
= zQuery
;
935 pIdxInfo
->needToFreeIdxStr
= 1;
937 pIdxInfo
->estimatedCost
= cost
;
939 /* Approximation of log2(nRow). */
940 for( ii
=0; ii
<(sizeof(int)*8)-1; ii
++ ){
941 if( nRow
& (1<<ii
) ){
942 pIdxInfo
->estimatedCost
= (double)ii
;
946 pIdxInfo
->estimatedCost
= (double)nRow
;
952 ** The xUpdate method for echo module virtual tables.
954 ** apData[0] apData[1] apData[2..]
958 ** INTEGER NULL (nCol args) UPDATE (do not set rowid)
959 ** INTEGER INTEGER (nCol args) UPDATE (with SET rowid = <arg1>)
961 ** NULL NULL (nCol args) INSERT INTO (automatic rowid value)
962 ** NULL INTEGER (nCol args) INSERT (incl. rowid value)
968 sqlite3_value
**apData
,
971 echo_vtab
*pVtab
= (echo_vtab
*)tab
;
972 sqlite3
*db
= pVtab
->db
;
975 sqlite3_stmt
*pStmt
= 0;
976 char *z
= 0; /* SQL statement to execute */
977 int bindArgZero
= 0; /* True to bind apData[0] to sql var no. nData */
978 int bindArgOne
= 0; /* True to bind apData[1] to sql var no. 1 */
979 int i
; /* Counter variable used by for loops */
981 assert( nData
==pVtab
->nCol
+2 || nData
==1 );
983 /* Ticket #3083 - make sure we always start a transaction prior to
984 ** making any changes to a virtual table */
985 assert( pVtab
->inTransaction
);
987 if( simulateVtabError(pVtab
, "xUpdate") ){
991 /* If apData[0] is an integer and nData>1 then do an UPDATE */
992 if( nData
>1 && sqlite3_value_type(apData
[0])==SQLITE_INTEGER
){
994 z
= sqlite3_mprintf("UPDATE %Q", pVtab
->zTableName
);
999 bindArgOne
= (apData
[1] && sqlite3_value_type(apData
[1])==SQLITE_INTEGER
);
1003 string_concat(&z
, " SET rowid=?1 ", 0, &rc
);
1006 for(i
=2; i
<nData
; i
++){
1007 if( apData
[i
]==0 ) continue;
1008 string_concat(&z
, sqlite3_mprintf(
1009 "%s %Q=?%d", zSep
, pVtab
->aCol
[i
-2], i
), 1, &rc
);
1012 string_concat(&z
, sqlite3_mprintf(" WHERE rowid=?%d", nData
), 1, &rc
);
1015 /* If apData[0] is an integer and nData==1 then do a DELETE */
1016 else if( nData
==1 && sqlite3_value_type(apData
[0])==SQLITE_INTEGER
){
1017 z
= sqlite3_mprintf("DELETE FROM %Q WHERE rowid = ?1", pVtab
->zTableName
);
1024 /* If the first argument is NULL and there are more than two args, INSERT */
1025 else if( nData
>2 && sqlite3_value_type(apData
[0])==SQLITE_NULL
){
1030 zInsert
= sqlite3_mprintf("INSERT INTO %Q (", pVtab
->zTableName
);
1034 if( sqlite3_value_type(apData
[1])==SQLITE_INTEGER
){
1036 zValues
= sqlite3_mprintf("?");
1037 string_concat(&zInsert
, "rowid", 0, &rc
);
1040 assert((pVtab
->nCol
+2)==nData
);
1041 for(ii
=2; ii
<nData
; ii
++){
1042 string_concat(&zInsert
,
1043 sqlite3_mprintf("%s%Q", zValues
?", ":"", pVtab
->aCol
[ii
-2]), 1, &rc
);
1044 string_concat(&zValues
,
1045 sqlite3_mprintf("%s?%d", zValues
?", ":"", ii
), 1, &rc
);
1048 string_concat(&z
, zInsert
, 1, &rc
);
1049 string_concat(&z
, ") VALUES(", 0, &rc
);
1050 string_concat(&z
, zValues
, 1, &rc
);
1051 string_concat(&z
, ")", 0, &rc
);
1054 /* Anything else is an error */
1057 return SQLITE_ERROR
;
1060 if( rc
==SQLITE_OK
){
1061 rc
= sqlite3_prepare(db
, z
, -1, &pStmt
, 0);
1063 assert( rc
!=SQLITE_OK
|| pStmt
);
1065 if( rc
==SQLITE_OK
) {
1067 sqlite3_bind_value(pStmt
, nData
, apData
[0]);
1070 sqlite3_bind_value(pStmt
, 1, apData
[1]);
1072 for(i
=2; i
<nData
&& rc
==SQLITE_OK
; i
++){
1073 if( apData
[i
] ) rc
= sqlite3_bind_value(pStmt
, i
, apData
[i
]);
1075 if( rc
==SQLITE_OK
){
1076 sqlite3_step(pStmt
);
1077 rc
= sqlite3_finalize(pStmt
);
1079 sqlite3_finalize(pStmt
);
1083 if( pRowid
&& rc
==SQLITE_OK
){
1084 *pRowid
= sqlite3_last_insert_rowid(db
);
1086 if( rc
!=SQLITE_OK
){
1087 tab
->zErrMsg
= sqlite3_mprintf("echo-vtab-error: %s", sqlite3_errmsg(db
));
1094 ** xBegin, xSync, xCommit and xRollback callbacks for echo module
1095 ** virtual tables. Do nothing other than add the name of the callback
1096 ** to the $::echo_module Tcl variable.
1098 static int echoTransactionCall(sqlite3_vtab
*tab
, const char *zCall
){
1100 echo_vtab
*pVtab
= (echo_vtab
*)tab
;
1101 z
= sqlite3_mprintf("echo(%s)", pVtab
->zTableName
);
1102 if( z
==0 ) return SQLITE_NOMEM
;
1103 appendToEchoModule(pVtab
->interp
, zCall
);
1104 appendToEchoModule(pVtab
->interp
, z
);
1108 static int echoBegin(sqlite3_vtab
*tab
){
1110 echo_vtab
*pVtab
= (echo_vtab
*)tab
;
1111 Tcl_Interp
*interp
= pVtab
->interp
;
1114 /* Ticket #3083 - do not start a transaction if we are already in
1116 assert( !pVtab
->inTransaction
);
1118 if( simulateVtabError(pVtab
, "xBegin") ){
1119 return SQLITE_ERROR
;
1122 rc
= echoTransactionCall(tab
, "xBegin");
1124 if( rc
==SQLITE_OK
){
1125 /* Check if the $::echo_module_begin_fail variable is defined. If it is,
1126 ** and it is set to the name of the real table underlying this virtual
1127 ** echo module table, then cause this xSync operation to fail.
1129 zVal
= Tcl_GetVar(interp
, "echo_module_begin_fail", TCL_GLOBAL_ONLY
);
1130 if( zVal
&& 0==strcmp(zVal
, pVtab
->zTableName
) ){
1134 if( rc
==SQLITE_OK
){
1135 pVtab
->inTransaction
= 1;
1139 static int echoSync(sqlite3_vtab
*tab
){
1141 echo_vtab
*pVtab
= (echo_vtab
*)tab
;
1142 Tcl_Interp
*interp
= pVtab
->interp
;
1145 /* Ticket #3083 - Only call xSync if we have previously started a
1147 assert( pVtab
->inTransaction
);
1149 if( simulateVtabError(pVtab
, "xSync") ){
1150 return SQLITE_ERROR
;
1153 rc
= echoTransactionCall(tab
, "xSync");
1155 if( rc
==SQLITE_OK
){
1156 /* Check if the $::echo_module_sync_fail variable is defined. If it is,
1157 ** and it is set to the name of the real table underlying this virtual
1158 ** echo module table, then cause this xSync operation to fail.
1160 zVal
= Tcl_GetVar(interp
, "echo_module_sync_fail", TCL_GLOBAL_ONLY
);
1161 if( zVal
&& 0==strcmp(zVal
, pVtab
->zTableName
) ){
1167 static int echoCommit(sqlite3_vtab
*tab
){
1168 echo_vtab
*pVtab
= (echo_vtab
*)tab
;
1171 /* Ticket #3083 - Only call xCommit if we have previously started
1173 assert( pVtab
->inTransaction
);
1175 if( simulateVtabError(pVtab
, "xCommit") ){
1176 return SQLITE_ERROR
;
1179 sqlite3BeginBenignMalloc();
1180 rc
= echoTransactionCall(tab
, "xCommit");
1181 sqlite3EndBenignMalloc();
1182 pVtab
->inTransaction
= 0;
1185 static int echoRollback(sqlite3_vtab
*tab
){
1187 echo_vtab
*pVtab
= (echo_vtab
*)tab
;
1189 /* Ticket #3083 - Only call xRollback if we have previously started
1191 assert( pVtab
->inTransaction
);
1193 rc
= echoTransactionCall(tab
, "xRollback");
1194 pVtab
->inTransaction
= 0;
1199 ** Implementation of "GLOB" function on the echo module. Pass
1200 ** all arguments to the ::echo_glob_overload procedure of TCL
1201 ** and return the result of that procedure as a string.
1203 static void overloadedGlobFunction(
1204 sqlite3_context
*pContext
,
1206 sqlite3_value
**apArg
1208 Tcl_Interp
*interp
= sqlite3_user_data(pContext
);
1212 Tcl_DStringInit(&str
);
1213 Tcl_DStringAppendElement(&str
, "::echo_glob_overload");
1214 for(i
=0; i
<nArg
; i
++){
1215 Tcl_DStringAppendElement(&str
, (char*)sqlite3_value_text(apArg
[i
]));
1217 rc
= Tcl_Eval(interp
, Tcl_DStringValue(&str
));
1218 Tcl_DStringFree(&str
);
1220 sqlite3_result_error(pContext
, Tcl_GetStringResult(interp
), -1);
1222 sqlite3_result_text(pContext
, Tcl_GetStringResult(interp
),
1223 -1, SQLITE_TRANSIENT
);
1225 Tcl_ResetResult(interp
);
1229 ** This is the xFindFunction implementation for the echo module.
1230 ** SQLite calls this routine when the first argument of a function
1231 ** is a column of an echo virtual table. This routine can optionally
1232 ** override the implementation of that function. It will choose to
1233 ** do so if the function is named "glob", and a TCL command named
1234 ** ::echo_glob_overload exists.
1236 static int echoFindFunction(
1239 const char *zFuncName
,
1240 void (**pxFunc
)(sqlite3_context
*,int,sqlite3_value
**),
1243 echo_vtab
*pVtab
= (echo_vtab
*)vtab
;
1244 Tcl_Interp
*interp
= pVtab
->interp
;
1246 if( strcmp(zFuncName
,"glob")!=0 ){
1249 if( Tcl_GetCommandInfo(interp
, "::echo_glob_overload", &info
)==0 ){
1252 *pxFunc
= overloadedGlobFunction
;
1257 static int echoRename(sqlite3_vtab
*vtab
, const char *zNewName
){
1259 echo_vtab
*p
= (echo_vtab
*)vtab
;
1261 if( simulateVtabError(p
, "xRename") ){
1262 return SQLITE_ERROR
;
1266 int nThis
= (int)strlen(p
->zThis
);
1267 char *zSql
= sqlite3_mprintf("ALTER TABLE %s RENAME TO %s%s",
1268 p
->zTableName
, zNewName
, &p
->zTableName
[nThis
]
1270 rc
= sqlite3_exec(p
->db
, zSql
, 0, 0, 0);
1277 static int echoSavepoint(sqlite3_vtab
*pVTab
, int iSavepoint
){
1282 static int echoRelease(sqlite3_vtab
*pVTab
, int iSavepoint
){
1287 static int echoRollbackTo(sqlite3_vtab
*pVTab
, int iSavepoint
){
1293 ** A virtual table module that merely "echos" the contents of another
1294 ** table (like an SQL VIEW).
1296 static sqlite3_module echoModule
= {
1303 echoOpen
, /* xOpen - open a cursor */
1304 echoClose
, /* xClose - close a cursor */
1305 echoFilter
, /* xFilter - configure scan constraints */
1306 echoNext
, /* xNext - advance a cursor */
1308 echoColumn
, /* xColumn - read data */
1309 echoRowid
, /* xRowid - read data */
1310 echoUpdate
, /* xUpdate - write data */
1311 echoBegin
, /* xBegin - begin transaction */
1312 echoSync
, /* xSync - sync transaction */
1313 echoCommit
, /* xCommit - commit transaction */
1314 echoRollback
, /* xRollback - rollback transaction */
1315 echoFindFunction
, /* xFindFunction - function overloading */
1316 echoRename
, /* xRename - rename the table */
1319 0, /* xRollbackTo */
1320 0, /* xShadowName */
1324 static sqlite3_module echoModuleV2
= {
1331 echoOpen
, /* xOpen - open a cursor */
1332 echoClose
, /* xClose - close a cursor */
1333 echoFilter
, /* xFilter - configure scan constraints */
1334 echoNext
, /* xNext - advance a cursor */
1336 echoColumn
, /* xColumn - read data */
1337 echoRowid
, /* xRowid - read data */
1338 echoUpdate
, /* xUpdate - write data */
1339 echoBegin
, /* xBegin - begin transaction */
1340 echoSync
, /* xSync - sync transaction */
1341 echoCommit
, /* xCommit - commit transaction */
1342 echoRollback
, /* xRollback - rollback transaction */
1343 echoFindFunction
, /* xFindFunction - function overloading */
1344 echoRename
, /* xRename - rename the table */
1348 0, /* xShadowName */
1353 ** Decode a pointer to an sqlite3 object.
1355 extern int getDbPointer(Tcl_Interp
*interp
, const char *zA
, sqlite3
**ppDb
);
1356 extern const char *sqlite3ErrName(int);
1358 static void moduleDestroy(void *p
){
1359 EchoModule
*pMod
= (EchoModule
*)p
;
1360 sqlite3_create_function(pMod
->db
, "function_that_does_not_exist_0982ma98",
1361 SQLITE_ANY
, 1, 0, 0, 0, 0);
1366 ** Register the echo virtual table module.
1368 static int SQLITE_TCLAPI
register_echo_module(
1369 ClientData clientData
, /* Pointer to sqlite3_enable_XXX function */
1370 Tcl_Interp
*interp
, /* The TCL interpreter that invoked this command */
1371 int objc
, /* Number of arguments */
1372 Tcl_Obj
*CONST objv
[] /* Command arguments */
1378 Tcl_WrongNumArgs(interp
, 1, objv
, "DB");
1381 if( getDbPointer(interp
, Tcl_GetString(objv
[1]), &db
) ) return TCL_ERROR
;
1383 /* Virtual table module "echo" */
1384 pMod
= sqlite3_malloc(sizeof(EchoModule
));
1385 pMod
->interp
= interp
;
1387 rc
= sqlite3_create_module_v2(
1388 db
, "echo", &echoModule
, (void*)pMod
, moduleDestroy
1391 /* Virtual table module "echo_v2" */
1392 if( rc
==SQLITE_OK
){
1393 pMod
= sqlite3_malloc(sizeof(EchoModule
));
1394 pMod
->interp
= interp
;
1396 rc
= sqlite3_create_module_v2(db
, "echo_v2",
1397 &echoModuleV2
, (void*)pMod
, moduleDestroy
1401 Tcl_SetResult(interp
, (char *)sqlite3ErrName(rc
), TCL_STATIC
);
1406 ** Tcl interface to sqlite3_declare_vtab, invoked as follows from Tcl:
1408 ** sqlite3_declare_vtab DB SQL
1410 static int SQLITE_TCLAPI
declare_vtab(
1411 ClientData clientData
, /* Pointer to sqlite3_enable_XXX function */
1412 Tcl_Interp
*interp
, /* The TCL interpreter that invoked this command */
1413 int objc
, /* Number of arguments */
1414 Tcl_Obj
*CONST objv
[] /* Command arguments */
1419 Tcl_WrongNumArgs(interp
, 1, objv
, "DB SQL");
1422 if( getDbPointer(interp
, Tcl_GetString(objv
[1]), &db
) ) return TCL_ERROR
;
1423 rc
= sqlite3_declare_vtab(db
, Tcl_GetString(objv
[2]));
1424 if( rc
!=SQLITE_OK
){
1425 Tcl_SetResult(interp
, (char *)sqlite3_errmsg(db
), TCL_VOLATILE
);
1431 #endif /* ifndef SQLITE_OMIT_VIRTUALTABLE */
1434 ** Register commands with the TCL interpreter.
1436 int Sqlitetest8_Init(Tcl_Interp
*interp
){
1437 #ifndef SQLITE_OMIT_VIRTUALTABLE
1440 Tcl_ObjCmdProc
*xProc
;
1443 { "register_echo_module", register_echo_module
, 0 },
1444 { "sqlite3_declare_vtab", declare_vtab
, 0 },
1447 for(i
=0; i
<sizeof(aObjCmd
)/sizeof(aObjCmd
[0]); i
++){
1448 Tcl_CreateObjCommand(interp
, aObjCmd
[i
].zName
,
1449 aObjCmd
[i
].xProc
, aObjCmd
[i
].clientData
, 0);