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 #if defined(INCLUDE_SQLITE_TCL_H)
18 # include "sqlite_tcl.h"
25 #ifndef SQLITE_OMIT_VIRTUALTABLE
27 typedef struct echo_vtab echo_vtab
;
28 typedef struct echo_cursor echo_cursor
;
31 ** The test module defined in this file uses four global Tcl variables to
32 ** commicate with test-scripts:
35 ** $::echo_module_sync_fail
36 ** $::echo_module_begin_fail
37 ** $::echo_module_cost
39 ** The variable ::echo_module is a list. Each time one of the following
40 ** methods is called, one or more elements are appended to the list.
41 ** This is used for automated testing of virtual table modules.
43 ** The ::echo_module_sync_fail variable is set by test scripts and read
44 ** by code in this file. If it is set to the name of a real table in the
45 ** the database, then all xSync operations on echo virtual tables that
46 ** use the named table as a backing store will fail.
50 ** Errors can be provoked within the following echo virtual table methods:
52 ** xBestIndex xOpen xFilter xNext
53 ** xColumn xRowid xUpdate xSync
56 ** This is done by setting the global tcl variable:
58 ** echo_module_fail($method,$tbl)
60 ** where $method is set to the name of the virtual table method to fail
61 ** (i.e. "xBestIndex") and $tbl is the name of the table being echoed (not
62 ** the name of the virtual table, the name of the underlying real table).
66 ** An echo virtual-table object.
68 ** echo.vtab.aIndex is an array of booleans. The nth entry is true if
69 ** the nth column of the real table is the left-most column of an index
70 ** (implicit or otherwise). In other words, if SQLite can optimize
71 ** a query like "SELECT * FROM real_table WHERE col = ?".
73 ** Member variable aCol[] contains copies of the column names of the real
78 Tcl_Interp
*interp
; /* Tcl interpreter containing debug variables */
79 sqlite3
*db
; /* Database connection */
82 int inTransaction
; /* True if within a transaction */
83 char *zThis
; /* Name of the echo table */
84 char *zTableName
; /* Name of the real table */
85 char *zLogName
; /* Name of the log table */
86 int nCol
; /* Number of columns in the real table */
87 int *aIndex
; /* Array of size nCol. True if column has an index */
88 char **aCol
; /* Array of size nCol. Column names */
91 /* An echo cursor object */
93 sqlite3_vtab_cursor base
;
97 static int simulateVtabError(echo_vtab
*p
, const char *zMethod
){
100 zVarname
[127] = '\0';
101 sqlite3_snprintf(127, zVarname
, "echo_module_fail(%s,%s)", zMethod
, p
->zTableName
);
102 zErr
= Tcl_GetVar(p
->interp
, zVarname
, TCL_GLOBAL_ONLY
);
104 p
->base
.zErrMsg
= sqlite3_mprintf("echo-vtab-error: %s", zErr
);
110 ** Convert an SQL-style quoted string into a normal string by removing
111 ** the quote characters. The conversion is done in-place. If the
112 ** input does not begin with a quote character, then this routine
122 static void dequoteString(char *z
){
130 case '`': break; /* For MySQL compatibility */
131 case '[': quote
= ']'; break; /* For MS SqlServer compatibility */
134 for(i
=1, j
=0; z
[i
]; i
++){
150 ** Retrieve the column names for the table named zTab via database
151 ** connection db. SQLITE_OK is returned on success, or an sqlite error
154 ** If successful, the number of columns is written to *pnCol. *paCol is
155 ** set to point at sqlite3_malloc()'d space containing the array of
156 ** nCol column names. The caller is responsible for calling sqlite3_free
159 static int getColumnNames(
167 sqlite3_stmt
*pStmt
= 0;
171 /* Prepare the statement "SELECT * FROM <tbl>". The column names
172 ** of the result set of the compiled SELECT will be the same as
173 ** the column names of table <tbl>.
175 zSql
= sqlite3_mprintf("SELECT * FROM %Q", zTab
);
180 rc
= sqlite3_prepare(db
, zSql
, -1, &pStmt
, 0);
187 nCol
= sqlite3_column_count(pStmt
);
189 /* Figure out how much space to allocate for the array of column names
190 ** (including space for the strings themselves). Then allocate it.
192 nBytes
= sizeof(char *) * nCol
;
193 for(ii
=0; ii
<nCol
; ii
++){
194 const char *zName
= sqlite3_column_name(pStmt
, ii
);
199 nBytes
+= (int)strlen(zName
)+1;
201 aCol
= (char **)sqlite3MallocZero(nBytes
);
207 /* Copy the column names into the allocated space and set up the
208 ** pointers in the aCol[] array.
210 zSpace
= (char *)(&aCol
[nCol
]);
211 for(ii
=0; ii
<nCol
; ii
++){
213 sqlite3_snprintf(nBytes
, zSpace
, "%s", sqlite3_column_name(pStmt
,ii
));
214 zSpace
+= (int)strlen(zSpace
) + 1;
216 assert( (zSpace
-nBytes
)==(char *)aCol
);
223 sqlite3_finalize(pStmt
);
228 ** Parameter zTab is the name of a table in database db with nCol
229 ** columns. This function allocates an array of integers nCol in
230 ** size and populates it according to any implicit or explicit
231 ** indices on table zTab.
233 ** If successful, SQLITE_OK is returned and *paIndex set to point
234 ** at the allocated array. Otherwise, an error code is returned.
236 ** See comments associated with the member variable aIndex above
237 ** "struct echo_vtab" for details of the contents of the array.
239 static int getIndexArray(
240 sqlite3
*db
, /* Database connection */
241 const char *zTab
, /* Name of table in database db */
245 sqlite3_stmt
*pStmt
= 0;
250 /* Allocate space for the index array */
251 aIndex
= (int *)sqlite3MallocZero(sizeof(int) * nCol
);
254 goto get_index_array_out
;
257 /* Compile an sqlite pragma to loop through all indices on table zTab */
258 zSql
= sqlite3_mprintf("PRAGMA index_list(%s)", zTab
);
261 goto get_index_array_out
;
263 rc
= sqlite3_prepare(db
, zSql
, -1, &pStmt
, 0);
266 /* For each index, figure out the left-most column and set the
267 ** corresponding entry in aIndex[] to 1.
269 while( pStmt
&& sqlite3_step(pStmt
)==SQLITE_ROW
){
270 const char *zIdx
= (const char *)sqlite3_column_text(pStmt
, 1);
271 sqlite3_stmt
*pStmt2
= 0;
272 if( zIdx
==0 ) continue;
273 zSql
= sqlite3_mprintf("PRAGMA index_info(%s)", zIdx
);
276 goto get_index_array_out
;
278 rc
= sqlite3_prepare(db
, zSql
, -1, &pStmt2
, 0);
280 if( pStmt2
&& sqlite3_step(pStmt2
)==SQLITE_ROW
){
281 int cid
= sqlite3_column_int(pStmt2
, 1);
282 assert( cid
>=0 && cid
<nCol
);
286 rc
= sqlite3_finalize(pStmt2
);
289 goto get_index_array_out
;
296 int rc2
= sqlite3_finalize(pStmt
);
302 sqlite3_free(aIndex
);
310 ** Global Tcl variable $echo_module is a list. This routine appends
311 ** the string element zArg to that list in interpreter interp.
313 static void appendToEchoModule(Tcl_Interp
*interp
, const char *zArg
){
314 int flags
= (TCL_APPEND_VALUE
| TCL_LIST_ELEMENT
| TCL_GLOBAL_ONLY
);
315 Tcl_SetVar(interp
, "echo_module", (zArg
?zArg
:""), flags
);
319 ** This function is called from within the echo-modules xCreate and
320 ** xConnect methods. The argc and argv arguments are copies of those
321 ** passed to the calling method. This function is responsible for
322 ** calling sqlite3_declare_vtab() to declare the schema of the virtual
323 ** table being created or connected.
325 ** If the constructor was passed just one argument, i.e.:
327 ** CREATE TABLE t1 AS echo(t2);
329 ** Then t2 is assumed to be the name of a *real* database table. The
330 ** schema of the virtual table is declared by passing a copy of the
331 ** CREATE TABLE statement for the real table to sqlite3_declare_vtab().
332 ** Hence, the virtual table should have exactly the same column names and
333 ** types as the real table.
335 static int echoDeclareVtab(
341 if( pVtab
->zTableName
){
342 sqlite3_stmt
*pStmt
= 0;
343 rc
= sqlite3_prepare(db
,
344 "SELECT sql FROM sqlite_master WHERE type = 'table' AND name = ?",
347 sqlite3_bind_text(pStmt
, 1, pVtab
->zTableName
, -1, 0);
348 if( sqlite3_step(pStmt
)==SQLITE_ROW
){
350 const char *zCreateTable
= (const char *)sqlite3_column_text(pStmt
, 0);
351 rc
= sqlite3_declare_vtab(db
, zCreateTable
);
352 rc2
= sqlite3_finalize(pStmt
);
357 rc
= sqlite3_finalize(pStmt
);
363 rc
= getColumnNames(db
, pVtab
->zTableName
, &pVtab
->aCol
, &pVtab
->nCol
);
366 rc
= getIndexArray(db
, pVtab
->zTableName
, pVtab
->nCol
, &pVtab
->aIndex
);
375 ** This function frees all runtime structures associated with the virtual
378 static int echoDestructor(sqlite3_vtab
*pVtab
){
379 echo_vtab
*p
= (echo_vtab
*)pVtab
;
380 sqlite3_free(p
->aIndex
);
381 sqlite3_free(p
->aCol
);
382 sqlite3_free(p
->zThis
);
383 sqlite3_free(p
->zTableName
);
384 sqlite3_free(p
->zLogName
);
389 typedef struct EchoModule EchoModule
;
395 ** This function is called to do the work of the xConnect() method -
396 ** to allocate the required in-memory structures for a newly connected
399 static int echoConstructor(
402 int argc
, const char *const*argv
,
403 sqlite3_vtab
**ppVtab
,
410 /* Allocate the sqlite3_vtab/echo_vtab structure itself */
411 pVtab
= sqlite3MallocZero( sizeof(*pVtab
) );
415 pVtab
->interp
= ((EchoModule
*)pAux
)->interp
;
418 /* Allocate echo_vtab.zThis */
419 pVtab
->zThis
= sqlite3_mprintf("%s", argv
[2]);
421 echoDestructor((sqlite3_vtab
*)pVtab
);
425 /* Allocate echo_vtab.zTableName */
427 pVtab
->zTableName
= sqlite3_mprintf("%s", argv
[3]);
428 dequoteString(pVtab
->zTableName
);
429 if( pVtab
->zTableName
&& pVtab
->zTableName
[0]=='*' ){
430 char *z
= sqlite3_mprintf("%s%s", argv
[2], &(pVtab
->zTableName
[1]));
431 sqlite3_free(pVtab
->zTableName
);
432 pVtab
->zTableName
= z
;
433 pVtab
->isPattern
= 1;
435 if( !pVtab
->zTableName
){
436 echoDestructor((sqlite3_vtab
*)pVtab
);
441 /* Log the arguments to this function to Tcl var ::echo_module */
442 for(i
=0; i
<argc
; i
++){
443 appendToEchoModule(pVtab
->interp
, argv
[i
]);
446 /* Invoke sqlite3_declare_vtab and set up other members of the echo_vtab
447 ** structure. If an error occurs, delete the sqlite3_vtab structure and
448 ** return an error code.
450 rc
= echoDeclareVtab(pVtab
, db
);
452 echoDestructor((sqlite3_vtab
*)pVtab
);
456 /* Success. Set *ppVtab and return */
457 *ppVtab
= &pVtab
->base
;
462 ** Echo virtual table module xCreate method.
464 static int echoCreate(
467 int argc
, const char *const*argv
,
468 sqlite3_vtab
**ppVtab
,
472 appendToEchoModule(((EchoModule
*)pAux
)->interp
, "xCreate");
473 rc
= echoConstructor(db
, pAux
, argc
, argv
, ppVtab
, pzErr
);
475 /* If there were two arguments passed to the module at the SQL level
476 ** (i.e. "CREATE VIRTUAL TABLE tbl USING echo(arg1, arg2)"), then
477 ** the second argument is used as a table name. Attempt to create
478 ** such a table with a single column, "logmsg". This table will
479 ** be used to log calls to the xUpdate method. It will be deleted
480 ** when the virtual table is DROPed.
482 ** Note: The main point of this is to test that we can drop tables
483 ** from within an xDestroy method call.
485 if( rc
==SQLITE_OK
&& argc
==5 ){
487 echo_vtab
*pVtab
= *(echo_vtab
**)ppVtab
;
488 pVtab
->zLogName
= sqlite3_mprintf("%s", argv
[4]);
489 zSql
= sqlite3_mprintf("CREATE TABLE %Q(logmsg)", pVtab
->zLogName
);
490 rc
= sqlite3_exec(db
, zSql
, 0, 0, 0);
493 *pzErr
= sqlite3_mprintf("%s", sqlite3_errmsg(db
));
497 if( *ppVtab
&& rc
!=SQLITE_OK
){
498 echoDestructor(*ppVtab
);
503 (*(echo_vtab
**)ppVtab
)->inTransaction
= 1;
510 ** Echo virtual table module xConnect method.
512 static int echoConnect(
515 int argc
, const char *const*argv
,
516 sqlite3_vtab
**ppVtab
,
519 appendToEchoModule(((EchoModule
*)pAux
)->interp
, "xConnect");
520 return echoConstructor(db
, pAux
, argc
, argv
, ppVtab
, pzErr
);
524 ** Echo virtual table module xDisconnect method.
526 static int echoDisconnect(sqlite3_vtab
*pVtab
){
527 appendToEchoModule(((echo_vtab
*)pVtab
)->interp
, "xDisconnect");
528 return echoDestructor(pVtab
);
532 ** Echo virtual table module xDestroy method.
534 static int echoDestroy(sqlite3_vtab
*pVtab
){
536 echo_vtab
*p
= (echo_vtab
*)pVtab
;
537 appendToEchoModule(((echo_vtab
*)pVtab
)->interp
, "xDestroy");
539 /* Drop the "log" table, if one exists (see echoCreate() for details) */
540 if( p
&& p
->zLogName
){
542 zSql
= sqlite3_mprintf("DROP TABLE %Q", p
->zLogName
);
543 rc
= sqlite3_exec(p
->db
, zSql
, 0, 0, 0);
548 rc
= echoDestructor(pVtab
);
554 ** Echo virtual table module xOpen method.
556 static int echoOpen(sqlite3_vtab
*pVTab
, sqlite3_vtab_cursor
**ppCursor
){
558 if( simulateVtabError((echo_vtab
*)pVTab
, "xOpen") ){
561 pCur
= sqlite3MallocZero(sizeof(echo_cursor
));
562 *ppCursor
= (sqlite3_vtab_cursor
*)pCur
;
563 return (pCur
? SQLITE_OK
: SQLITE_NOMEM
);
567 ** Echo virtual table module xClose method.
569 static int echoClose(sqlite3_vtab_cursor
*cur
){
571 echo_cursor
*pCur
= (echo_cursor
*)cur
;
572 sqlite3_stmt
*pStmt
= pCur
->pStmt
;
575 rc
= sqlite3_finalize(pStmt
);
580 ** Return non-zero if the cursor does not currently point to a valid record
581 ** (i.e if the scan has finished), or zero otherwise.
583 static int echoEof(sqlite3_vtab_cursor
*cur
){
584 return (((echo_cursor
*)cur
)->pStmt
? 0 : 1);
588 ** Echo virtual table module xNext method.
590 static int echoNext(sqlite3_vtab_cursor
*cur
){
592 echo_cursor
*pCur
= (echo_cursor
*)cur
;
594 if( simulateVtabError((echo_vtab
*)(cur
->pVtab
), "xNext") ){
599 rc
= sqlite3_step(pCur
->pStmt
);
600 if( rc
==SQLITE_ROW
){
603 rc
= sqlite3_finalize(pCur
->pStmt
);
612 ** Echo virtual table module xColumn method.
614 static int echoColumn(sqlite3_vtab_cursor
*cur
, sqlite3_context
*ctx
, int i
){
616 sqlite3_stmt
*pStmt
= ((echo_cursor
*)cur
)->pStmt
;
618 if( simulateVtabError((echo_vtab
*)(cur
->pVtab
), "xColumn") ){
623 sqlite3_result_null(ctx
);
625 assert( sqlite3_data_count(pStmt
)>iCol
);
626 sqlite3_result_value(ctx
, sqlite3_column_value(pStmt
, iCol
));
632 ** Echo virtual table module xRowid method.
634 static int echoRowid(sqlite3_vtab_cursor
*cur
, sqlite_int64
*pRowid
){
635 sqlite3_stmt
*pStmt
= ((echo_cursor
*)cur
)->pStmt
;
637 if( simulateVtabError((echo_vtab
*)(cur
->pVtab
), "xRowid") ){
641 *pRowid
= sqlite3_column_int64(pStmt
, 0);
646 ** Compute a simple hash of the null terminated string zString.
648 ** This module uses only sqlite3_index_info.idxStr, not
649 ** sqlite3_index_info.idxNum. So to test idxNum, when idxStr is set
650 ** in echoBestIndex(), idxNum is set to the corresponding hash value.
651 ** In echoFilter(), code assert()s that the supplied idxNum value is
652 ** indeed the hash of the supplied idxStr.
654 static int hashString(const char *zString
){
657 for(ii
=0; zString
[ii
]; ii
++){
658 val
= (val
<< 3) + (int)zString
[ii
];
660 return (int)(val
&0x7fffffff);
664 ** Echo virtual table module xFilter method.
666 static int echoFilter(
667 sqlite3_vtab_cursor
*pVtabCursor
,
668 int idxNum
, const char *idxStr
,
669 int argc
, sqlite3_value
**argv
674 echo_cursor
*pCur
= (echo_cursor
*)pVtabCursor
;
675 echo_vtab
*pVtab
= (echo_vtab
*)pVtabCursor
->pVtab
;
676 sqlite3
*db
= pVtab
->db
;
678 if( simulateVtabError(pVtab
, "xFilter") ){
682 /* Check that idxNum matches idxStr */
683 assert( idxNum
==hashString(idxStr
) );
685 /* Log arguments to the ::echo_module Tcl variable */
686 appendToEchoModule(pVtab
->interp
, "xFilter");
687 appendToEchoModule(pVtab
->interp
, idxStr
);
688 for(i
=0; i
<argc
; i
++){
689 appendToEchoModule(pVtab
->interp
, (const char*)sqlite3_value_text(argv
[i
]));
692 sqlite3_finalize(pCur
->pStmt
);
695 /* Prepare the SQL statement created by echoBestIndex and bind the
696 ** runtime parameters passed to this function to it.
698 rc
= sqlite3_prepare(db
, idxStr
, -1, &pCur
->pStmt
, 0);
699 assert( pCur
->pStmt
|| rc
!=SQLITE_OK
);
700 for(i
=0; rc
==SQLITE_OK
&& i
<argc
; i
++){
701 rc
= sqlite3_bind_value(pCur
->pStmt
, i
+1, argv
[i
]);
704 /* If everything was successful, advance to the first row of the scan */
706 rc
= echoNext(pVtabCursor
);
714 ** A helper function used by echoUpdate() and echoBestIndex() for
715 ** manipulating strings in concert with the sqlite3_mprintf() function.
717 ** Parameter pzStr points to a pointer to a string allocated with
718 ** sqlite3_mprintf. The second parameter, zAppend, points to another
719 ** string. The two strings are concatenated together and *pzStr
720 ** set to point at the result. The initial buffer pointed to by *pzStr
721 ** is deallocated via sqlite3_free().
723 ** If the third argument, doFree, is true, then sqlite3_free() is
724 ** also called to free the buffer pointed to by zAppend.
726 static void string_concat(char **pzStr
, char *zAppend
, int doFree
, int *pRc
){
728 if( !zAppend
&& doFree
&& *pRc
==SQLITE_OK
){
731 if( *pRc
!=SQLITE_OK
){
737 zIn
= sqlite3_mprintf("%s%s", zIn
, zAppend
);
740 zIn
= sqlite3_mprintf("%s", zAppend
);
748 sqlite3_free(zAppend
);
753 ** This function returns a pointer to an sqlite3_malloc()ed buffer
754 ** containing the select-list (the thing between keywords SELECT and FROM)
755 ** to query the underlying real table with for the scan described by
756 ** argument pIdxInfo.
758 ** If the current SQLite version is earlier than 3.10.0, this is just "*"
759 ** (select all columns). Or, for version 3.10.0 and greater, the list of
760 ** columns identified by the pIdxInfo->colUsed mask.
762 static char *echoSelectList(echo_vtab
*pTab
, sqlite3_index_info
*pIdxInfo
){
764 if( sqlite3_libversion_number()<3010000 ){
765 zRet
= sqlite3_mprintf(", *");
768 for(i
=0; i
<pTab
->nCol
; i
++){
769 if( pIdxInfo
->colUsed
& ((sqlite3_uint64
)1 << (i
>=63 ? 63 : i
)) ){
770 zRet
= sqlite3_mprintf("%z, %s", zRet
, pTab
->aCol
[i
]);
772 zRet
= sqlite3_mprintf("%z, NULL", zRet
);
781 ** The echo module implements the subset of query constraints and sort
782 ** orders that may take advantage of SQLite indices on the underlying
783 ** real table. For example, if the real table is declared as:
785 ** CREATE TABLE real(a, b, c);
786 ** CREATE INDEX real_index ON real(b);
788 ** then the echo module handles WHERE or ORDER BY clauses that refer
789 ** to the column "b", but not "a" or "c". If a multi-column index is
790 ** present, only its left most column is considered.
792 ** This xBestIndex method encodes the proposed search strategy as
793 ** an SQL query on the real table underlying the virtual echo module
794 ** table and stores the query in sqlite3_index_info.idxStr. The SQL
795 ** statement is of the form:
797 ** SELECT rowid, * FROM <real-table> ?<where-clause>? ?<order-by-clause>?
799 ** where the <where-clause> and <order-by-clause> are determined
800 ** by the contents of the structure pointed to by the pIdxInfo argument.
802 static int echoBestIndex(sqlite3_vtab
*tab
, sqlite3_index_info
*pIdxInfo
){
808 const char *zSep
= "WHERE";
809 echo_vtab
*pVtab
= (echo_vtab
*)tab
;
810 sqlite3_stmt
*pStmt
= 0;
811 Tcl_Interp
*interp
= pVtab
->interp
;
818 int isIgnoreUsable
= 0;
819 if( Tcl_GetVar(interp
, "echo_module_ignore_usable", TCL_GLOBAL_ONLY
) ){
823 if( simulateVtabError(pVtab
, "xBestIndex") ){
827 /* Determine the number of rows in the table and store this value in local
828 ** variable nRow. The 'estimated-cost' of the scan will be the number of
829 ** rows in the table for a linear scan, or the log (base 2) of the
830 ** number of rows if the proposed scan uses an index.
832 if( Tcl_GetVar(interp
, "echo_module_cost", TCL_GLOBAL_ONLY
) ){
833 cost
= atof(Tcl_GetVar(interp
, "echo_module_cost", TCL_GLOBAL_ONLY
));
836 zQuery
= sqlite3_mprintf("SELECT count(*) FROM %Q", pVtab
->zTableName
);
840 rc
= sqlite3_prepare(pVtab
->db
, zQuery
, -1, &pStmt
, 0);
841 sqlite3_free(zQuery
);
846 nRow
= sqlite3_column_int(pStmt
, 0);
847 rc
= sqlite3_finalize(pStmt
);
853 zCol
= echoSelectList(pVtab
, pIdxInfo
);
854 if( !zCol
) return SQLITE_NOMEM
;
855 zQuery
= sqlite3_mprintf("SELECT rowid%z FROM %Q", zCol
, pVtab
->zTableName
);
856 if( !zQuery
) return SQLITE_NOMEM
;
858 for(ii
=0; ii
<pIdxInfo
->nConstraint
; ii
++){
859 const struct sqlite3_index_constraint
*pConstraint
;
860 struct sqlite3_index_constraint_usage
*pUsage
;
863 pConstraint
= &pIdxInfo
->aConstraint
[ii
];
864 pUsage
= &pIdxInfo
->aConstraintUsage
[ii
];
866 if( !isIgnoreUsable
&& !pConstraint
->usable
) continue;
868 iCol
= pConstraint
->iColumn
;
869 if( iCol
<0 || pVtab
->aIndex
[iCol
] ){
870 char *zNewCol
= iCol
>=0 ? pVtab
->aCol
[iCol
] : "rowid";
873 switch( pConstraint
->op
){
874 case SQLITE_INDEX_CONSTRAINT_EQ
:
876 case SQLITE_INDEX_CONSTRAINT_LT
:
878 case SQLITE_INDEX_CONSTRAINT_GT
:
880 case SQLITE_INDEX_CONSTRAINT_LE
:
882 case SQLITE_INDEX_CONSTRAINT_GE
:
884 case SQLITE_INDEX_CONSTRAINT_MATCH
:
885 /* Purposely translate the MATCH operator into a LIKE, which
886 ** will be used by the next block of code to construct a new
887 ** query. It should also be noted here that the next block
888 ** of code requires the first letter of this operator to be
889 ** in upper-case to trigger the special MATCH handling (i.e.
890 ** wrapping the bound parameter with literal '%'s).
893 case SQLITE_INDEX_CONSTRAINT_LIKE
:
895 case SQLITE_INDEX_CONSTRAINT_GLOB
:
897 case SQLITE_INDEX_CONSTRAINT_REGEXP
:
898 zOp
= "regexp"; break;
901 zNew
= sqlite3_mprintf(" %s %s LIKE (SELECT '%%'||?||'%%')",
904 zNew
= sqlite3_mprintf(" %s %s %s ?", zSep
, zNewCol
, zOp
);
906 string_concat(&zQuery
, zNew
, 1, &rc
);
909 pUsage
->argvIndex
= ++nArg
;
914 /* If there is only one term in the ORDER BY clause, and it is
915 ** on a column that this virtual table has an index for, then consume
916 ** the ORDER BY clause.
918 if( pIdxInfo
->nOrderBy
==1 && (
919 pIdxInfo
->aOrderBy
->iColumn
<0 ||
920 pVtab
->aIndex
[pIdxInfo
->aOrderBy
->iColumn
]) ){
921 int iCol
= pIdxInfo
->aOrderBy
->iColumn
;
922 char *zNewCol
= iCol
>=0 ? pVtab
->aCol
[iCol
] : "rowid";
923 char *zDir
= pIdxInfo
->aOrderBy
->desc
?"DESC":"ASC";
924 zNew
= sqlite3_mprintf(" ORDER BY %s %s", zNewCol
, zDir
);
925 string_concat(&zQuery
, zNew
, 1, &rc
);
926 pIdxInfo
->orderByConsumed
= 1;
929 appendToEchoModule(pVtab
->interp
, "xBestIndex");;
930 appendToEchoModule(pVtab
->interp
, zQuery
);
935 pIdxInfo
->idxNum
= hashString(zQuery
);
936 pIdxInfo
->idxStr
= zQuery
;
937 pIdxInfo
->needToFreeIdxStr
= 1;
939 pIdxInfo
->estimatedCost
= cost
;
941 /* Approximation of log2(nRow). */
942 for( ii
=0; ii
<(sizeof(int)*8)-1; ii
++ ){
943 if( nRow
& (1<<ii
) ){
944 pIdxInfo
->estimatedCost
= (double)ii
;
948 pIdxInfo
->estimatedCost
= (double)nRow
;
954 ** The xUpdate method for echo module virtual tables.
956 ** apData[0] apData[1] apData[2..]
960 ** INTEGER NULL (nCol args) UPDATE (do not set rowid)
961 ** INTEGER INTEGER (nCol args) UPDATE (with SET rowid = <arg1>)
963 ** NULL NULL (nCol args) INSERT INTO (automatic rowid value)
964 ** NULL INTEGER (nCol args) INSERT (incl. rowid value)
970 sqlite3_value
**apData
,
973 echo_vtab
*pVtab
= (echo_vtab
*)tab
;
974 sqlite3
*db
= pVtab
->db
;
977 sqlite3_stmt
*pStmt
= 0;
978 char *z
= 0; /* SQL statement to execute */
979 int bindArgZero
= 0; /* True to bind apData[0] to sql var no. nData */
980 int bindArgOne
= 0; /* True to bind apData[1] to sql var no. 1 */
981 int i
; /* Counter variable used by for loops */
983 assert( nData
==pVtab
->nCol
+2 || nData
==1 );
985 /* Ticket #3083 - make sure we always start a transaction prior to
986 ** making any changes to a virtual table */
987 assert( pVtab
->inTransaction
);
989 if( simulateVtabError(pVtab
, "xUpdate") ){
993 /* If apData[0] is an integer and nData>1 then do an UPDATE */
994 if( nData
>1 && sqlite3_value_type(apData
[0])==SQLITE_INTEGER
){
996 z
= sqlite3_mprintf("UPDATE %Q", pVtab
->zTableName
);
1001 bindArgOne
= (apData
[1] && sqlite3_value_type(apData
[1])==SQLITE_INTEGER
);
1005 string_concat(&z
, " SET rowid=?1 ", 0, &rc
);
1008 for(i
=2; i
<nData
; i
++){
1009 if( apData
[i
]==0 ) continue;
1010 string_concat(&z
, sqlite3_mprintf(
1011 "%s %Q=?%d", zSep
, pVtab
->aCol
[i
-2], i
), 1, &rc
);
1014 string_concat(&z
, sqlite3_mprintf(" WHERE rowid=?%d", nData
), 1, &rc
);
1017 /* If apData[0] is an integer and nData==1 then do a DELETE */
1018 else if( nData
==1 && sqlite3_value_type(apData
[0])==SQLITE_INTEGER
){
1019 z
= sqlite3_mprintf("DELETE FROM %Q WHERE rowid = ?1", pVtab
->zTableName
);
1026 /* If the first argument is NULL and there are more than two args, INSERT */
1027 else if( nData
>2 && sqlite3_value_type(apData
[0])==SQLITE_NULL
){
1032 zInsert
= sqlite3_mprintf("INSERT INTO %Q (", pVtab
->zTableName
);
1036 if( sqlite3_value_type(apData
[1])==SQLITE_INTEGER
){
1038 zValues
= sqlite3_mprintf("?");
1039 string_concat(&zInsert
, "rowid", 0, &rc
);
1042 assert((pVtab
->nCol
+2)==nData
);
1043 for(ii
=2; ii
<nData
; ii
++){
1044 string_concat(&zInsert
,
1045 sqlite3_mprintf("%s%Q", zValues
?", ":"", pVtab
->aCol
[ii
-2]), 1, &rc
);
1046 string_concat(&zValues
,
1047 sqlite3_mprintf("%s?%d", zValues
?", ":"", ii
), 1, &rc
);
1050 string_concat(&z
, zInsert
, 1, &rc
);
1051 string_concat(&z
, ") VALUES(", 0, &rc
);
1052 string_concat(&z
, zValues
, 1, &rc
);
1053 string_concat(&z
, ")", 0, &rc
);
1056 /* Anything else is an error */
1059 return SQLITE_ERROR
;
1062 if( rc
==SQLITE_OK
){
1063 rc
= sqlite3_prepare(db
, z
, -1, &pStmt
, 0);
1065 assert( rc
!=SQLITE_OK
|| pStmt
);
1067 if( rc
==SQLITE_OK
) {
1069 sqlite3_bind_value(pStmt
, nData
, apData
[0]);
1072 sqlite3_bind_value(pStmt
, 1, apData
[1]);
1074 for(i
=2; i
<nData
&& rc
==SQLITE_OK
; i
++){
1075 if( apData
[i
] ) rc
= sqlite3_bind_value(pStmt
, i
, apData
[i
]);
1077 if( rc
==SQLITE_OK
){
1078 sqlite3_step(pStmt
);
1079 rc
= sqlite3_finalize(pStmt
);
1081 sqlite3_finalize(pStmt
);
1085 if( pRowid
&& rc
==SQLITE_OK
){
1086 *pRowid
= sqlite3_last_insert_rowid(db
);
1088 if( rc
!=SQLITE_OK
){
1089 tab
->zErrMsg
= sqlite3_mprintf("echo-vtab-error: %s", sqlite3_errmsg(db
));
1096 ** xBegin, xSync, xCommit and xRollback callbacks for echo module
1097 ** virtual tables. Do nothing other than add the name of the callback
1098 ** to the $::echo_module Tcl variable.
1100 static int echoTransactionCall(sqlite3_vtab
*tab
, const char *zCall
){
1102 echo_vtab
*pVtab
= (echo_vtab
*)tab
;
1103 z
= sqlite3_mprintf("echo(%s)", pVtab
->zTableName
);
1104 if( z
==0 ) return SQLITE_NOMEM
;
1105 appendToEchoModule(pVtab
->interp
, zCall
);
1106 appendToEchoModule(pVtab
->interp
, z
);
1110 static int echoBegin(sqlite3_vtab
*tab
){
1112 echo_vtab
*pVtab
= (echo_vtab
*)tab
;
1113 Tcl_Interp
*interp
= pVtab
->interp
;
1116 /* Ticket #3083 - do not start a transaction if we are already in
1118 assert( !pVtab
->inTransaction
);
1120 if( simulateVtabError(pVtab
, "xBegin") ){
1121 return SQLITE_ERROR
;
1124 rc
= echoTransactionCall(tab
, "xBegin");
1126 if( rc
==SQLITE_OK
){
1127 /* Check if the $::echo_module_begin_fail variable is defined. If it is,
1128 ** and it is set to the name of the real table underlying this virtual
1129 ** echo module table, then cause this xSync operation to fail.
1131 zVal
= Tcl_GetVar(interp
, "echo_module_begin_fail", TCL_GLOBAL_ONLY
);
1132 if( zVal
&& 0==strcmp(zVal
, pVtab
->zTableName
) ){
1136 if( rc
==SQLITE_OK
){
1137 pVtab
->inTransaction
= 1;
1141 static int echoSync(sqlite3_vtab
*tab
){
1143 echo_vtab
*pVtab
= (echo_vtab
*)tab
;
1144 Tcl_Interp
*interp
= pVtab
->interp
;
1147 /* Ticket #3083 - Only call xSync if we have previously started a
1149 assert( pVtab
->inTransaction
);
1151 if( simulateVtabError(pVtab
, "xSync") ){
1152 return SQLITE_ERROR
;
1155 rc
= echoTransactionCall(tab
, "xSync");
1157 if( rc
==SQLITE_OK
){
1158 /* Check if the $::echo_module_sync_fail variable is defined. If it is,
1159 ** and it is set to the name of the real table underlying this virtual
1160 ** echo module table, then cause this xSync operation to fail.
1162 zVal
= Tcl_GetVar(interp
, "echo_module_sync_fail", TCL_GLOBAL_ONLY
);
1163 if( zVal
&& 0==strcmp(zVal
, pVtab
->zTableName
) ){
1169 static int echoCommit(sqlite3_vtab
*tab
){
1170 echo_vtab
*pVtab
= (echo_vtab
*)tab
;
1173 /* Ticket #3083 - Only call xCommit if we have previously started
1175 assert( pVtab
->inTransaction
);
1177 if( simulateVtabError(pVtab
, "xCommit") ){
1178 return SQLITE_ERROR
;
1181 sqlite3BeginBenignMalloc();
1182 rc
= echoTransactionCall(tab
, "xCommit");
1183 sqlite3EndBenignMalloc();
1184 pVtab
->inTransaction
= 0;
1187 static int echoRollback(sqlite3_vtab
*tab
){
1189 echo_vtab
*pVtab
= (echo_vtab
*)tab
;
1191 /* Ticket #3083 - Only call xRollback if we have previously started
1193 assert( pVtab
->inTransaction
);
1195 rc
= echoTransactionCall(tab
, "xRollback");
1196 pVtab
->inTransaction
= 0;
1201 ** Implementation of "GLOB" function on the echo module. Pass
1202 ** all arguments to the ::echo_glob_overload procedure of TCL
1203 ** and return the result of that procedure as a string.
1205 static void overloadedGlobFunction(
1206 sqlite3_context
*pContext
,
1208 sqlite3_value
**apArg
1210 Tcl_Interp
*interp
= sqlite3_user_data(pContext
);
1214 Tcl_DStringInit(&str
);
1215 Tcl_DStringAppendElement(&str
, "::echo_glob_overload");
1216 for(i
=0; i
<nArg
; i
++){
1217 Tcl_DStringAppendElement(&str
, (char*)sqlite3_value_text(apArg
[i
]));
1219 rc
= Tcl_Eval(interp
, Tcl_DStringValue(&str
));
1220 Tcl_DStringFree(&str
);
1222 sqlite3_result_error(pContext
, Tcl_GetStringResult(interp
), -1);
1224 sqlite3_result_text(pContext
, Tcl_GetStringResult(interp
),
1225 -1, SQLITE_TRANSIENT
);
1227 Tcl_ResetResult(interp
);
1231 ** This is the xFindFunction implementation for the echo module.
1232 ** SQLite calls this routine when the first argument of a function
1233 ** is a column of an echo virtual table. This routine can optionally
1234 ** override the implementation of that function. It will choose to
1235 ** do so if the function is named "glob", and a TCL command named
1236 ** ::echo_glob_overload exists.
1238 static int echoFindFunction(
1241 const char *zFuncName
,
1242 void (**pxFunc
)(sqlite3_context
*,int,sqlite3_value
**),
1245 echo_vtab
*pVtab
= (echo_vtab
*)vtab
;
1246 Tcl_Interp
*interp
= pVtab
->interp
;
1248 if( strcmp(zFuncName
,"glob")!=0 ){
1251 if( Tcl_GetCommandInfo(interp
, "::echo_glob_overload", &info
)==0 ){
1254 *pxFunc
= overloadedGlobFunction
;
1259 static int echoRename(sqlite3_vtab
*vtab
, const char *zNewName
){
1261 echo_vtab
*p
= (echo_vtab
*)vtab
;
1263 if( simulateVtabError(p
, "xRename") ){
1264 return SQLITE_ERROR
;
1268 int nThis
= (int)strlen(p
->zThis
);
1269 char *zSql
= sqlite3_mprintf("ALTER TABLE %s RENAME TO %s%s",
1270 p
->zTableName
, zNewName
, &p
->zTableName
[nThis
]
1272 rc
= sqlite3_exec(p
->db
, zSql
, 0, 0, 0);
1279 static int echoSavepoint(sqlite3_vtab
*pVTab
, int iSavepoint
){
1284 static int echoRelease(sqlite3_vtab
*pVTab
, int iSavepoint
){
1289 static int echoRollbackTo(sqlite3_vtab
*pVTab
, int iSavepoint
){
1295 ** A virtual table module that merely "echos" the contents of another
1296 ** table (like an SQL VIEW).
1298 static sqlite3_module echoModule
= {
1305 echoOpen
, /* xOpen - open a cursor */
1306 echoClose
, /* xClose - close a cursor */
1307 echoFilter
, /* xFilter - configure scan constraints */
1308 echoNext
, /* xNext - advance a cursor */
1310 echoColumn
, /* xColumn - read data */
1311 echoRowid
, /* xRowid - read data */
1312 echoUpdate
, /* xUpdate - write data */
1313 echoBegin
, /* xBegin - begin transaction */
1314 echoSync
, /* xSync - sync transaction */
1315 echoCommit
, /* xCommit - commit transaction */
1316 echoRollback
, /* xRollback - rollback transaction */
1317 echoFindFunction
, /* xFindFunction - function overloading */
1318 echoRename
/* xRename - rename the table */
1321 static sqlite3_module echoModuleV2
= {
1328 echoOpen
, /* xOpen - open a cursor */
1329 echoClose
, /* xClose - close a cursor */
1330 echoFilter
, /* xFilter - configure scan constraints */
1331 echoNext
, /* xNext - advance a cursor */
1333 echoColumn
, /* xColumn - read data */
1334 echoRowid
, /* xRowid - read data */
1335 echoUpdate
, /* xUpdate - write data */
1336 echoBegin
, /* xBegin - begin transaction */
1337 echoSync
, /* xSync - sync transaction */
1338 echoCommit
, /* xCommit - commit transaction */
1339 echoRollback
, /* xRollback - rollback transaction */
1340 echoFindFunction
, /* xFindFunction - function overloading */
1341 echoRename
, /* xRename - rename the table */
1348 ** Decode a pointer to an sqlite3 object.
1350 extern int getDbPointer(Tcl_Interp
*interp
, const char *zA
, sqlite3
**ppDb
);
1351 extern const char *sqlite3ErrName(int);
1353 static void moduleDestroy(void *p
){
1358 ** Register the echo virtual table module.
1360 static int SQLITE_TCLAPI
register_echo_module(
1361 ClientData clientData
, /* Pointer to sqlite3_enable_XXX function */
1362 Tcl_Interp
*interp
, /* The TCL interpreter that invoked this command */
1363 int objc
, /* Number of arguments */
1364 Tcl_Obj
*CONST objv
[] /* Command arguments */
1370 Tcl_WrongNumArgs(interp
, 1, objv
, "DB");
1373 if( getDbPointer(interp
, Tcl_GetString(objv
[1]), &db
) ) return TCL_ERROR
;
1375 /* Virtual table module "echo" */
1376 pMod
= sqlite3_malloc(sizeof(EchoModule
));
1377 pMod
->interp
= interp
;
1378 rc
= sqlite3_create_module_v2(
1379 db
, "echo", &echoModule
, (void*)pMod
, moduleDestroy
1382 /* Virtual table module "echo_v2" */
1383 if( rc
==SQLITE_OK
){
1384 pMod
= sqlite3_malloc(sizeof(EchoModule
));
1385 pMod
->interp
= interp
;
1386 rc
= sqlite3_create_module_v2(db
, "echo_v2",
1387 &echoModuleV2
, (void*)pMod
, moduleDestroy
1391 Tcl_SetResult(interp
, (char *)sqlite3ErrName(rc
), TCL_STATIC
);
1396 ** Tcl interface to sqlite3_declare_vtab, invoked as follows from Tcl:
1398 ** sqlite3_declare_vtab DB SQL
1400 static int SQLITE_TCLAPI
declare_vtab(
1401 ClientData clientData
, /* Pointer to sqlite3_enable_XXX function */
1402 Tcl_Interp
*interp
, /* The TCL interpreter that invoked this command */
1403 int objc
, /* Number of arguments */
1404 Tcl_Obj
*CONST objv
[] /* Command arguments */
1409 Tcl_WrongNumArgs(interp
, 1, objv
, "DB SQL");
1412 if( getDbPointer(interp
, Tcl_GetString(objv
[1]), &db
) ) return TCL_ERROR
;
1413 rc
= sqlite3_declare_vtab(db
, Tcl_GetString(objv
[2]));
1414 if( rc
!=SQLITE_OK
){
1415 Tcl_SetResult(interp
, (char *)sqlite3_errmsg(db
), TCL_VOLATILE
);
1421 #endif /* ifndef SQLITE_OMIT_VIRTUALTABLE */
1424 ** Register commands with the TCL interpreter.
1426 int Sqlitetest8_Init(Tcl_Interp
*interp
){
1427 #ifndef SQLITE_OMIT_VIRTUALTABLE
1430 Tcl_ObjCmdProc
*xProc
;
1433 { "register_echo_module", register_echo_module
, 0 },
1434 { "sqlite3_declare_vtab", declare_vtab
, 0 },
1437 for(i
=0; i
<sizeof(aObjCmd
)/sizeof(aObjCmd
[0]); i
++){
1438 Tcl_CreateObjCommand(interp
, aObjCmd
[i
].zName
,
1439 aObjCmd
[i
].xProc
, aObjCmd
[i
].clientData
, 0);