/ Check-in [de8d32ac]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Add test_schema.c, containing a module for viewing the database schema via a virtual table. (CVS 3257)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: de8d32ac71a6e113e83b952813424cb3fb5a2e59
User & Date: danielk1977 2006-06-15 15:59:19
Context
2006-06-15
16:26
Fix type in test_schema.c. (CVS 3258) check-in: d65d83d3 user: danielk1977 tags: trunk
15:59
Add test_schema.c, containing a module for viewing the database schema via a virtual table. (CVS 3257) check-in: de8d32ac user: danielk1977 tags: trunk
15:38
Add column_value, declare_vtab and create_module to the function table used by dynamic extensions. (CVS 3256) check-in: 25c47508 user: danielk1977 tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to Makefile.in.

   206    206     $(TOP)/src/test4.c \
   207    207     $(TOP)/src/test5.c \
   208    208     $(TOP)/src/test6.c \
   209    209     $(TOP)/src/test7.c \
   210    210     $(TOP)/src/test8.c \
   211    211     $(TOP)/src/test_async.c \
   212    212     $(TOP)/src/test_md5.c \
          213  +  $(TOP)/src/test_schema.c \
   213    214     $(TOP)/src/test_server.c \
   214    215     $(TOP)/src/test_tclvar.c \
   215    216     $(TOP)/src/utf.c \
   216    217     $(TOP)/src/util.c \
   217    218     $(TOP)/src/vdbe.c \
   218    219     $(TOP)/src/where.c
   219    220   

Changes to main.mk.

   139    139     $(TOP)/src/test4.c \
   140    140     $(TOP)/src/test5.c \
   141    141     $(TOP)/src/test6.c \
   142    142     $(TOP)/src/test7.c \
   143    143     $(TOP)/src/test8.c \
   144    144     $(TOP)/src/test_async.c \
   145    145     $(TOP)/src/test_md5.c \
          146  +  $(TOP)/src/test_schema.c \
   146    147     $(TOP)/src/test_server.c \
   147    148     $(TOP)/src/test_tclvar.c \
   148    149     $(TOP)/src/utf.c \
   149    150     $(TOP)/src/util.c \
   150    151     $(TOP)/src/vdbe.c \
   151    152     $(TOP)/src/where.c
   152    153   

Changes to src/tclsqlite.c.

     7      7   **    May you do good and not evil.
     8      8   **    May you find forgiveness for yourself and forgive others.
     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** A TCL Interface to SQLite
    13     13   **
    14         -** $Id: tclsqlite.c,v 1.158 2006/06/13 23:51:35 drh Exp $
           14  +** $Id: tclsqlite.c,v 1.159 2006/06/15 15:59:20 danielk1977 Exp $
    15     15   */
    16     16   #ifndef NO_TCL     /* Omit this whole file if TCL is unavailable */
    17     17   
    18     18   #include "sqliteInt.h"
    19     19   #include "hash.h"
    20     20   #include "tcl.h"
    21     21   #include <stdlib.h>
................................................................................
  2152   2152       extern int Sqlitetest6_Init(Tcl_Interp*);
  2153   2153       extern int Sqlitetest7_Init(Tcl_Interp*);
  2154   2154       extern int Sqlitetest8_Init(Tcl_Interp*);
  2155   2155       extern int Md5_Init(Tcl_Interp*);
  2156   2156       extern int Sqlitetestsse_Init(Tcl_Interp*);
  2157   2157       extern int Sqlitetestasync_Init(Tcl_Interp*);
  2158   2158       extern int Sqlitetesttclvar_Init(Tcl_Interp*);
         2159  +    extern int Sqlitetestschema_Init(Tcl_Interp*);
  2159   2160   
  2160   2161       Sqlitetest1_Init(interp);
  2161   2162       Sqlitetest2_Init(interp);
  2162   2163       Sqlitetest3_Init(interp);
  2163   2164       Sqlitetest4_Init(interp);
  2164   2165       Sqlitetest5_Init(interp);
  2165   2166       Sqlitetest6_Init(interp);
  2166   2167       Sqlitetest7_Init(interp);
  2167   2168       Sqlitetest8_Init(interp);
  2168   2169       Sqlitetestasync_Init(interp);
  2169   2170       Sqlitetesttclvar_Init(interp);
         2171  +    Sqlitetestschema_Init(interp);
  2170   2172       Md5_Init(interp);
  2171   2173   #ifdef SQLITE_SSE
  2172   2174       Sqlitetestsse_Init(interp);
  2173   2175   #endif
  2174   2176     }
  2175   2177   #endif
  2176   2178     if( argc>=2 || TCLSH==2 ){

Added src/test_schema.c.

            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.1 2006/06/15 15:59:20 danielk1977 Exp $
           17  +*/
           18  +
           19  +/* The code in this file defines a sqlite3 module that provides
           20  +** a read-only view of the current database schema. There is one
           21  +** row in the schema table for each column in the database.
           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  +  #define MALLOC(x) sqliteRawMalloc(x) 
           43  +  #define FREE(x)   sqliteFree(x)
           44  +#else
           45  +  #include "sqlite3ext.h"
           46  +  SQLITE_EXTENSION_INIT1
           47  +  #define MALLOC(x) malloc(x) 
           48  +  #define FREE(x)   free(x)
           49  +#endif
           50  +
           51  +#include <stdlib.h>
           52  +#include <string.h>
           53  +#include <assert.h>
           54  +
           55  +typedef struct schema_vtab schema_vtab;
           56  +typedef struct schema_cursor schema_cursor;
           57  +
           58  +/* A schema table object */
           59  +struct schema_vtab {
           60  +  sqlite3_vtab base;
           61  +  sqlite3 *db;
           62  +};
           63  +
           64  +/* A schema table cursor object */
           65  +struct schema_cursor {
           66  +  sqlite3_vtab_cursor base;
           67  +  sqlite3_stmt *pDbList;
           68  +  sqlite3_stmt *pTableList;
           69  +  sqlite3_stmt *pColumnList;
           70  +  int rowid;
           71  +};
           72  +
           73  +/*
           74  +** Table destructor for the schema module.
           75  +*/
           76  +static int schemaDestroy(sqlite3_vtab *pVtab){
           77  +  FREE(pVtab);
           78  +  return 0;
           79  +}
           80  +
           81  +/*
           82  +** Table constructor for the schema module.
           83  +*/
           84  +static int schemaCreate(
           85  +  sqlite3 *db,
           86  +  void *pAux,
           87  +  int argc, char **argv,
           88  +  sqlite3_vtab **ppVtab
           89  +){
           90  +  int rc = SQLITE_NOMEM;
           91  +  schema_vtab *pVtab = MALLOC(sizeof(schema_vtab));
           92  +  if( pVtab ){
           93  +    memset(pVtab, 0, sizeof(schema_vtab));
           94  +    pVtab->db = db;
           95  +    rc = sqlite3_declare_vtab(db, SCHEMA);
           96  +  }
           97  +  *ppVtab = (sqlite3_vtab *)pVtab;
           98  +  return rc;
           99  +}
          100  +
          101  +/*
          102  +** Open a new cursor on the schema table.
          103  +*/
          104  +static int schemaOpen(sqlite3_vtab *pVTab, sqlite3_vtab_cursor **ppCursor){
          105  +  int rc = SQLITE_NOMEM;
          106  +  schema_cursor *pCur;
          107  +  pCur = MALLOC(sizeof(schema_cursor));
          108  +  if( pCur ){
          109  +    memset(pCur, 0, sizeof(schema_cursor));
          110  +    *ppCursor = (sqlite3_vtab_cursor *)pCur;
          111  +    rc = SQLITE_OK;
          112  +  }
          113  +  return rc;
          114  +}
          115  +
          116  +/*
          117  +** Close a schema table cursor.
          118  +*/
          119  +static int schemaClose(sqlite3_vtab_cursor *cur){
          120  +  schema_cursor *pCur = (schema_cursor *)cur;
          121  +  sqlite3_finalize(pCur->pDbList);
          122  +  sqlite3_finalize(pCur->pTableList);
          123  +  sqlite3_finalize(pCur->pColumnList);
          124  +  FREE(pCur);
          125  +  return SQLITE_OK;
          126  +}
          127  +
          128  +/*
          129  +** Retrieve a column of data.
          130  +*/
          131  +static int schemaColumn(sqlite3_vtab_cursor *cur, sqlite3_context *ctx, int i){
          132  +  schema_cursor *pCur = (schema_cursor *)cur;
          133  +  switch( i ){
          134  +    case 0:
          135  +      sqlite3_result_value(ctx, sqlite3_column_value(pCur->pDbList, 1));
          136  +      break;
          137  +    case 1:
          138  +      sqlite3_result_value(ctx, sqlite3_column_value(pCur->pTableList, 0));
          139  +      break;
          140  +    default:
          141  +      sqlite3_result_value(ctx, sqlite3_column_value(pCur->pColumnList, i-2));
          142  +      break;
          143  +  }
          144  +  return SQLITE_OK;
          145  +}
          146  +
          147  +/*
          148  +** Retrieve the current rowid.
          149  +*/
          150  +static int schemaRowid(sqlite3_vtab_cursor *cur, sqlite_int64 *pRowid){
          151  +  schema_cursor *pCur = (schema_cursor *)cur;
          152  +  *pRowid = pCur->rowid;
          153  +  return SQLITE_OK;
          154  +}
          155  +
          156  +static int finalize(sqlite3_stmt **ppStmt){
          157  +  int rc = sqlite3_finalize(*ppStmt);
          158  +  *ppStmt = 0;
          159  +  return rc;
          160  +}
          161  +
          162  +/*
          163  +** Advance the cursor to the next row.
          164  +*/
          165  +static int schemaNext(sqlite3_vtab_cursor *cur){
          166  +  int rc;
          167  +  schema_cursor *pCur = (schema_cursor *)cur;
          168  +  schema_vtab *pVtab = (schema_vtab *)(cur->pVtab);
          169  +  char *zSql = 0;
          170  +
          171  +  while( !pCur->pColumnList || SQLITE_ROW!=sqlite3_step(pCur->pColumnList) ){
          172  +    if( SQLITE_OK!=(rc = finalize(&pCur->pColumnList)) ) goto fail;
          173  +
          174  +    while( !pCur->pTableList || SQLITE_ROW!=sqlite3_step(pCur->pTableList) ){
          175  +      if( SQLITE_OK!=(rc = finalize(&pCur->pTableList)) ) goto fail;
          176  +
          177  +      assert(pCur->pDbList);
          178  +      while( SQLITE_ROW!=sqlite3_step(pCur->pDbList) ){
          179  +        if( SQLITE_OK!=(rc = finalize(&pCur->pDbList)) ) goto fail;
          180  +        return 0;
          181  +      }
          182  +
          183  +      /* Set zSql to the SQL to pull the list of tables from the 
          184  +      ** sqlite_master (or sqlite_temp_master) table of the database
          185  +      ** identfied by the row pointed to by the SQL statement pCur->pDbList
          186  +      ** (iterating through a "PRAGMA database_list;" statement).
          187  +      */
          188  +      if( sqlite3_column_int(pCur->pDbList, 0)==1 ){
          189  +        zSql = sqlite3_mprintf(
          190  +            "SELECT name FROM sqlite_temp_master WHERE type='table'"
          191  +        );
          192  +      }else{
          193  +        sqlite3_stmt *pDbList = pCur->pDbList;
          194  +        zSql = sqlite3_mprintf(
          195  +            "SELECT name FROM %Q.sqlite_master WHERE type='table'",
          196  +             sqlite3_column_text(pDbList, 1)
          197  +        );
          198  +      }
          199  +      if( !zSql ){
          200  +        rc = SQLITE_NOMEM;
          201  +        goto fail;
          202  +      }
          203  +
          204  +      rc = sqlite3_prepare(pVtab->db, zSql, -1, &pCur->pTableList, 0);
          205  +      sqlite3_free(zSql);
          206  +      if( rc!=SQLITE_OK ) goto fail;
          207  +    }
          208  +
          209  +    /* Set zSql to the SQL to the table_info pragma for the table currently
          210  +    ** identified by the rows pointed to by statements pCur->pDbList and
          211  +    ** pCur->pTableList.
          212  +    */
          213  +    zSql = sqlite3_mprintf("PRAGMA %Q.table_info(%Q)", 
          214  +        sqlite3_column_text(pCur->pDbList, 1),
          215  +        sqlite3_column_text(pCur->pTableList, 0)
          216  +    );
          217  +
          218  +    if( !zSql ){
          219  +      rc = SQLITE_NOMEM;
          220  +      goto fail;
          221  +    }
          222  +    rc = sqlite3_prepare(pVtab->db, zSql, -1, &pCur->pColumnList, 0);
          223  +    sqlite3_free(zSql);
          224  +    if( rc!=SQLITE_OK ) goto fail;
          225  +  }
          226  +  pCur->rowid++;
          227  +
          228  +fail:
          229  +  /* TODO: Handle rc */
          230  +  return 1;
          231  +}
          232  +
          233  +/*
          234  +** Reset a schema table cursor.
          235  +*/
          236  +static int schemaFilter(
          237  +  sqlite3_vtab_cursor *pVtabCursor, 
          238  +  int idxNum, const char *idxStr,
          239  +  int argc, sqlite3_value **argv
          240  +){
          241  +  int rc;
          242  +  schema_vtab *pVtab = (schema_vtab *)(pVtabCursor->pVtab);
          243  +  schema_cursor *pCur = (schema_cursor *)pVtabCursor;
          244  +  pCur->rowid = 0;
          245  +  finalize(&pCur->pTableList);
          246  +  finalize(&pCur->pColumnList);
          247  +  finalize(&pCur->pDbList);
          248  +  rc = sqlite3_prepare(pVtab->db,"PRAGMA database_list", -1, &pCur->pDbList, 0);
          249  +  return (rc==SQLITE_OK ? schemaNext(pVtabCursor) : rc);
          250  +}
          251  +
          252  +/*
          253  +** Analyse the WHERE condition.
          254  +*/
          255  +static int schemaBestIndex(sqlite3_vtab *tab, sqlite3_index_info *pIdxInfo){
          256  +  return SQLITE_OK;
          257  +}
          258  +
          259  +/*
          260  +** A virtual table module that merely echos method calls into TCL
          261  +** variables.
          262  +*/
          263  +static sqlite3_module schemaModule = {
          264  +  0,                           /* iVersion */
          265  +  "schema",                    /* zName */
          266  +  schemaCreate,
          267  +  schemaCreate,
          268  +  schemaBestIndex,
          269  +  schemaDestroy,
          270  +  schemaDestroy,
          271  +  schemaOpen,                  /* xOpen - open a cursor */
          272  +  schemaClose,                 /* xClose - close a cursor */
          273  +  schemaFilter,                /* xFilter - configure scan constraints */
          274  +  schemaNext,                  /* xNext - advance a cursor */
          275  +  schemaColumn,                /* xColumn - read data */
          276  +  schemaRowid,                 /* xRowid - read data */
          277  +};
          278  +
          279  +
          280  +#ifdef SQLITE_TEST
          281  +
          282  +/*
          283  +** Decode a pointer to an sqlite3 object.
          284  +*/
          285  +static int getDbPointer(Tcl_Interp *interp, const char *zA, sqlite3 **ppDb){
          286  +  *ppDb = (sqlite3*)sqlite3TextToPtr(zA);
          287  +  return TCL_OK;
          288  +}
          289  +
          290  +/*
          291  +** Register the schema virtual table module.
          292  +*/
          293  +static int register_schema_module(
          294  +  ClientData clientData, /* Not used */
          295  +  Tcl_Interp *interp,    /* The TCL interpreter that invoked this command */
          296  +  int objc,              /* Number of arguments */
          297  +  Tcl_Obj *CONST objv[]  /* Command arguments */
          298  +){
          299  +  sqlite3 *db;
          300  +  if( objc!=2 ){
          301  +    Tcl_WrongNumArgs(interp, 1, objv, "DB");
          302  +    return TCL_ERROR;
          303  +  }
          304  +#ifndef SQLITE_OMIT_VIRTUALTABLE
          305  +  sqlite3_create_module(db, "schema", &schemaModule, 0);
          306  +#endif
          307  +  return TCL_OK;
          308  +}
          309  +
          310  +/*
          311  +** Register commands with the TCL interpreter.
          312  +*/
          313  +int Sqlitetestschema_Init(Tcl_Interp *interp){
          314  +  static struct {
          315  +     char *zName;
          316  +     Tcl_ObjCmdProc *xProc;
          317  +     void *clientData;
          318  +  } aObjCmd[] = {
          319  +     { "register_schema_module", register_schema_module, 0 },
          320  +  };
          321  +  int i;
          322  +  for(i=0; i<sizeof(aObjCmd)/sizeof(aObjCmd[0]); i++){
          323  +    Tcl_CreateObjCommand(interp, aObjCmd[i].zName, 
          324  +        aObjCmd[i].xProc, aObjCmd[i].clientData, 0);
          325  +  }
          326  +  return TCL_OK;
          327  +}
          328  +
          329  +#else
          330  +
          331  +/*
          332  +** Extension load function.
          333  +*/
          334  +int schema_init(
          335  +  sqlite3 *db, 
          336  +  char **pzErrMsg, 
          337  +  const sqlite3_api_routines *pApi
          338  +){
          339  +  SQLITE_EXTENSION_INIT2(pApi);
          340  +  sqlite3_create_module(db, "schema", &schemaModule, 0);
          341  +  return 0;
          342  +}
          343  +
          344  +#endif