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: test_schema.c,v 1.15 2008/07/07 14:50:14 drh Exp $

    17 */

    18 

    19 /* The code in this file defines a sqlite3 virtual-table module that

    20 ** provides a read-only view of the current database schema. There is one

    21 ** row in the schema table for each column in the database schema.

    22 */

    23 #define SCHEMA \

    24 "CREATE TABLE x("                                                            \

    25   "database,"          /* Name of database (i.e. main, temp etc.) */         \

    26   "tablename,"         /* Name of table */                                   \

    27   "cid,"               /* Column number (from left-to-right, 0 upward) */    \

    28   "name,"              /* Column name */                                     \

    29   "type,"              /* Specified type (i.e. VARCHAR(32)) */               \

    30   "not_null,"          /* Boolean. True if NOT NULL was specified */         \

    31   "dflt_value,"        /* Default value for this column */                   \

    32   "pk"                 /* True if this column is part of the primary key */  \

    33 ")"

    34 

    35 /* If SQLITE_TEST is defined this code is preprocessed for use as part

    36 ** of the sqlite test binary "testfixture". Otherwise it is preprocessed

    37 ** to be compiled into an sqlite dynamic extension.

    38 */

    39 #ifdef SQLITE_TEST

    40   #include "sqliteInt.h"

    41   #include "tcl.h"

    42 #else

    43   #include "sqlite3ext.h"

    44   SQLITE_EXTENSION_INIT1

    45 #endif

    46 

    47 #include <stdlib.h>

    48 #include <string.h>

    49 #include <assert.h>

    50 

    51 typedef struct schema_vtab schema_vtab;

    52 typedef struct schema_cursor schema_cursor;

    53 

    54 /* A schema table object */

    55 struct schema_vtab {

    56   sqlite3_vtab base;

    57   sqlite3 *db;

    58 };

    59 

    60 /* A schema table cursor object */

    61 struct schema_cursor {

    62   sqlite3_vtab_cursor base;

    63   sqlite3_stmt *pDbList;

    64   sqlite3_stmt *pTableList;

    65   sqlite3_stmt *pColumnList;

    66   int rowid;

    67 };

    68 

    69 /*

    70 ** None of this works unless we have virtual tables.

    71 */

    72 #ifndef SQLITE_OMIT_VIRTUALTABLE

    73 

    74 /*

    75 ** Table destructor for the schema module.

    76 */

    77 static int schemaDestroy(sqlite3_vtab *pVtab){

    78   sqlite3_free(pVtab);

    79   return 0;

    80 }

    81 

    82 /*

    83 ** Table constructor for the schema module.

    84 */

    85 static int schemaCreate(

    86   sqlite3 *db,

    87   void *pAux,

    88   int argc, const char *const*argv,

    89   sqlite3_vtab **ppVtab,

    90   char **pzErr

    91 ){

    92   int rc = SQLITE_NOMEM;

    93   schema_vtab *pVtab = sqlite3_malloc(sizeof(schema_vtab));

    94   if( pVtab ){

    95     memset(pVtab, 0, sizeof(schema_vtab));

    96     pVtab->db = db;

    97 #ifndef SQLITE_OMIT_VIRTUALTABLE

    98     rc = sqlite3_declare_vtab(db, SCHEMA);

    99 #endif

   100   }

   101   *ppVtab = (sqlite3_vtab *)pVtab;

   102   return rc;

   103 }

   104 

   105 /*

   106 ** Open a new cursor on the schema table.

   107 */

   108 static int schemaOpen(sqlite3_vtab *pVTab, sqlite3_vtab_cursor **ppCursor){

   109   int rc = SQLITE_NOMEM;

   110   schema_cursor *pCur;

   111   pCur = sqlite3_malloc(sizeof(schema_cursor));

   112   if( pCur ){

   113     memset(pCur, 0, sizeof(schema_cursor));

   114     *ppCursor = (sqlite3_vtab_cursor *)pCur;

   115     rc = SQLITE_OK;

   116   }

   117   return rc;

   118 }

   119 

   120 /*

   121 ** Close a schema table cursor.

   122 */

   123 static int schemaClose(sqlite3_vtab_cursor *cur){

   124   schema_cursor *pCur = (schema_cursor *)cur;

   125   sqlite3_finalize(pCur->pDbList);

   126   sqlite3_finalize(pCur->pTableList);

   127   sqlite3_finalize(pCur->pColumnList);

   128   sqlite3_free(pCur);

   129   return SQLITE_OK;

   130 }

   131 

   132 /*

   133 ** Retrieve a column of data.

   134 */

   135 static int schemaColumn(sqlite3_vtab_cursor *cur, sqlite3_context *ctx, int i){

   136   schema_cursor *pCur = (schema_cursor *)cur;

   137   switch( i ){

   138     case 0:

   139       sqlite3_result_value(ctx, sqlite3_column_value(pCur->pDbList, 1));

   140       break;

   141     case 1:

   142       sqlite3_result_value(ctx, sqlite3_column_value(pCur->pTableList, 0));

   143       break;

   144     default:

   145       sqlite3_result_value(ctx, sqlite3_column_value(pCur->pColumnList, i-2));

   146       break;

   147   }

   148   return SQLITE_OK;

   149 }

   150 

   151 /*

   152 ** Retrieve the current rowid.

   153 */

   154 static int schemaRowid(sqlite3_vtab_cursor *cur, sqlite_int64 *pRowid){

   155   schema_cursor *pCur = (schema_cursor *)cur;

   156   *pRowid = pCur->rowid;

   157   return SQLITE_OK;

   158 }

   159 

   160 static int finalize(sqlite3_stmt **ppStmt){

   161   int rc = sqlite3_finalize(*ppStmt);

   162   *ppStmt = 0;

   163   return rc;

   164 }

   165 

   166 static int schemaEof(sqlite3_vtab_cursor *cur){

   167   schema_cursor *pCur = (schema_cursor *)cur;

   168   return (pCur->pDbList ? 0 : 1);

   169 }

   170 

   171 /*

   172 ** Advance the cursor to the next row.

   173 */

   174 static int schemaNext(sqlite3_vtab_cursor *cur){

   175   int rc = SQLITE_OK;

   176   schema_cursor *pCur = (schema_cursor *)cur;

   177   schema_vtab *pVtab = (schema_vtab *)(cur->pVtab);

   178   char *zSql = 0;

   179 

   180   while( !pCur->pColumnList || SQLITE_ROW!=sqlite3_step(pCur->pColumnList) ){

   181     if( SQLITE_OK!=(rc = finalize(&pCur->pColumnList)) ) goto next_exit;

   182 

   183     while( !pCur->pTableList || SQLITE_ROW!=sqlite3_step(pCur->pTableList) ){

   184       if( SQLITE_OK!=(rc = finalize(&pCur->pTableList)) ) goto next_exit;

   185 

   186       assert(pCur->pDbList);

   187       while( SQLITE_ROW!=sqlite3_step(pCur->pDbList) ){

   188         rc = finalize(&pCur->pDbList);

   189         goto next_exit;

   190       }

   191 

   192       /* Set zSql to the SQL to pull the list of tables from the 

   193       ** sqlite_master (or sqlite_temp_master) table of the database

   194       ** identfied by the row pointed to by the SQL statement pCur->pDbList

   195       ** (iterating through a "PRAGMA database_list;" statement).

   196       */

   197       if( sqlite3_column_int(pCur->pDbList, 0)==1 ){

   198         zSql = sqlite3_mprintf(

   199             "SELECT name FROM sqlite_temp_master WHERE type='table'"

   200         );

   201       }else{

   202         sqlite3_stmt *pDbList = pCur->pDbList;

   203         zSql = sqlite3_mprintf(

   204             "SELECT name FROM %Q.sqlite_master WHERE type='table'",

   205              sqlite3_column_text(pDbList, 1)

   206         );

   207       }

   208       if( !zSql ){

   209         rc = SQLITE_NOMEM;

   210         goto next_exit;

   211       }

   212 

   213       rc = sqlite3_prepare(pVtab->db, zSql, -1, &pCur->pTableList, 0);

   214       sqlite3_free(zSql);

   215       if( rc!=SQLITE_OK ) goto next_exit;

   216     }

   217 

   218     /* Set zSql to the SQL to the table_info pragma for the table currently

   219     ** identified by the rows pointed to by statements pCur->pDbList and

   220     ** pCur->pTableList.

   221     */

   222     zSql = sqlite3_mprintf("PRAGMA %Q.table_info(%Q)", 

   223         sqlite3_column_text(pCur->pDbList, 1),

   224         sqlite3_column_text(pCur->pTableList, 0)

   225     );

   226 

   227     if( !zSql ){

   228       rc = SQLITE_NOMEM;

   229       goto next_exit;

   230     }

   231     rc = sqlite3_prepare(pVtab->db, zSql, -1, &pCur->pColumnList, 0);

   232     sqlite3_free(zSql);

   233     if( rc!=SQLITE_OK ) goto next_exit;

   234   }

   235   pCur->rowid++;

   236 

   237 next_exit:

   238   /* TODO: Handle rc */

   239   return rc;

   240 }

   241 

   242 /*

   243 ** Reset a schema table cursor.

   244 */

   245 static int schemaFilter(

   246   sqlite3_vtab_cursor *pVtabCursor, 

   247   int idxNum, const char *idxStr,

   248   int argc, sqlite3_value **argv

   249 ){

   250   int rc;

   251   schema_vtab *pVtab = (schema_vtab *)(pVtabCursor->pVtab);

   252   schema_cursor *pCur = (schema_cursor *)pVtabCursor;

   253   pCur->rowid = 0;

   254   finalize(&pCur->pTableList);

   255   finalize(&pCur->pColumnList);

   256   finalize(&pCur->pDbList);

   257   rc = sqlite3_prepare(pVtab->db,"PRAGMA database_list", -1, &pCur->pDbList, 0);

   258   return (rc==SQLITE_OK ? schemaNext(pVtabCursor) : rc);

   259 }

   260 

   261 /*

   262 ** Analyse the WHERE condition.

   263 */

   264 static int schemaBestIndex(sqlite3_vtab *tab, sqlite3_index_info *pIdxInfo){

   265   return SQLITE_OK;

   266 }

   267 

   268 /*

   269 ** A virtual table module that merely echos method calls into TCL

   270 ** variables.

   271 */

   272 static sqlite3_module schemaModule = {

   273   0,                           /* iVersion */

   274   schemaCreate,

   275   schemaCreate,

   276   schemaBestIndex,

   277   schemaDestroy,

   278   schemaDestroy,

   279   schemaOpen,                  /* xOpen - open a cursor */

   280   schemaClose,                 /* xClose - close a cursor */

   281   schemaFilter,                /* xFilter - configure scan constraints */

   282   schemaNext,                  /* xNext - advance a cursor */

   283   schemaEof,                   /* xEof */

   284   schemaColumn,                /* xColumn - read data */

   285   schemaRowid,                 /* xRowid - read data */

   286   0,                           /* xUpdate */

   287   0,                           /* xBegin */

   288   0,                           /* xSync */

   289   0,                           /* xCommit */

   290   0,                           /* xRollback */

   291   0,                           /* xFindMethod */

   292   0,                           /* xRename */

   293 };

   294 

   295 #endif /* !defined(SQLITE_OMIT_VIRTUALTABLE) */

   296 

   297 #ifdef SQLITE_TEST

   298 

   299 /*

   300 ** Decode a pointer to an sqlite3 object.

   301 */

   302 extern int getDbPointer(Tcl_Interp *interp, const char *zA, sqlite3 **ppDb);

   303 

   304 /*

   305 ** Register the schema virtual table module.

   306 */

   307 static int register_schema_module(

   308   ClientData clientData, /* Not used */

   309   Tcl_Interp *interp,    /* The TCL interpreter that invoked this command */

   310   int objc,              /* Number of arguments */

   311   Tcl_Obj *CONST objv[]  /* Command arguments */

   312 ){

   313   sqlite3 *db;

   314   if( objc!=2 ){

   315     Tcl_WrongNumArgs(interp, 1, objv, "DB");

   316     return TCL_ERROR;

   317   }

   318   if( getDbPointer(interp, Tcl_GetString(objv[1]), &db) ) return TCL_ERROR;

   319 #ifndef SQLITE_OMIT_VIRTUALTABLE

   320   sqlite3_create_module(db, "schema", &schemaModule, 0);

   321 #endif

   322   return TCL_OK;

   323 }

   324 

   325 /*

   326 ** Register commands with the TCL interpreter.

   327 */

   328 int Sqlitetestschema_Init(Tcl_Interp *interp){

   329   static struct {

   330      char *zName;

   331      Tcl_ObjCmdProc *xProc;

   332      void *clientData;

   333   } aObjCmd[] = {

   334      { "register_schema_module", register_schema_module, 0 },

   335   };

   336   int i;

   337   for(i=0; i<sizeof(aObjCmd)/sizeof(aObjCmd[0]); i++){

   338     Tcl_CreateObjCommand(interp, aObjCmd[i].zName, 

   339         aObjCmd[i].xProc, aObjCmd[i].clientData, 0);

   340   }

   341   return TCL_OK;

   342 }

   343 

   344 #else

   345 

   346 /*

   347 ** Extension load function.

   348 */

   349 int sqlite3_extension_init(

   350   sqlite3 *db, 

   351   char **pzErrMsg, 

   352   const sqlite3_api_routines *pApi

   353 ){

   354   SQLITE_EXTENSION_INIT2(pApi);

   355 #ifndef SQLITE_OMIT_VIRTUALTABLE

   356   sqlite3_create_module(db, "schema", &schemaModule, 0);

   357 #endif

   358   return 0;

   359 }

   360 

   361 #endif