/ Check-in [b7b90237]
Login

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

Overview
Comment:file format change (CVS 122)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:b7b90237945d3577caba3a2f5595e52b25027297
User & Date: drh 2000-08-02 13:47:42
Context
2000-08-03
15:09
bug fix (CVS 123) check-in: 4dabf5e4 user: drh tags: trunk
2000-08-02
13:47
file format change (CVS 122) check-in: b7b90237 user: drh tags: trunk
12:37
file format change (CVS 121) check-in: 4110936f user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/build.c.

    29     29   **     DROP TABLE
    30     30   **     CREATE INDEX
    31     31   **     DROP INDEX
    32     32   **     creating expressions and ID lists
    33     33   **     COPY
    34     34   **     VACUUM
    35     35   **
    36         -** $Id: build.c,v 1.21 2000/07/29 13:06:59 drh Exp $
           36  +** $Id: build.c,v 1.22 2000/08/02 13:47:42 drh Exp $
    37     37   */
    38     38   #include "sqliteInt.h"
    39     39   
    40     40   /*
    41     41   ** This routine is called after a single SQL statement has been
    42     42   ** parsed and we want to execute the VDBE code to implement 
    43     43   ** that statement.  Prior action routines should have already
................................................................................
   315    315   ** the master table because we just connected to the database, so 
   316    316   ** the entry for this table already exists in the master table.
   317    317   ** We do not want to create it again.
   318    318   */
   319    319   void sqliteEndTable(Parse *pParse, Token *pEnd){
   320    320     Table *p;
   321    321     int h;
          322  +  int addVersion;      /* True to insert a "file format" meta record */
   322    323   
   323    324     if( pParse->nErr ) return;
          325  +  p = pParse->pNewTable;
          326  +  addVersion =  p!=0 && pParse->db->nTable==1;
   324    327   
   325    328     /* Add the table to the in-memory representation of the database
   326    329     */
   327         -  if( (p = pParse->pNewTable)!=0 && pParse->explain==0 ){
          330  +  if( p!=0 && pParse->explain==0 ){
   328    331       h = sqliteHashNoCase(p->zName, 0) % N_HASH;
   329    332       p->pHash = pParse->db->apTblHash[h];
   330    333       pParse->db->apTblHash[h] = p;
   331    334       pParse->pNewTable = 0;
          335  +    pParse->db->nTable++;
   332    336     }
   333    337   
   334    338     /* If not initializing, then create the table on disk.
   335    339     */
   336    340     if( !pParse->initFlag ){
   337    341       static VdbeOp addTable[] = {
   338    342         { OP_Open,        0, 1, MASTER_NAME },
................................................................................
   339    343         { OP_New,         0, 0, 0},
   340    344         { OP_String,      0, 0, "table"     },
   341    345         { OP_String,      0, 0, 0},            /* 3 */
   342    346         { OP_String,      0, 0, 0},            /* 4 */
   343    347         { OP_String,      0, 0, 0},            /* 5 */
   344    348         { OP_MakeRecord,  4, 0, 0},
   345    349         { OP_Put,         0, 0, 0},
   346         -      { OP_Close,       0, 0, 0},
          350  +    };
          351  +    static VdbeOp addVersion[] = {
          352  +      { OP_New,         0, 0, 0},
          353  +      { OP_String,      0, 0, "meta"            },
          354  +      { OP_String,      0, 0, ""                },
          355  +      { OP_String,      0, 0, ""                },
          356  +      { OP_String,      0, 0, "file format 2"   },
          357  +      { OP_MakeRecord,  4, 0, 0},
          358  +      { OP_Put,         0, 0, 0},
   347    359       };
   348    360       int n, base;
   349    361       Vdbe *v;
   350    362   
   351    363       v = sqliteGetVdbe(pParse);
   352    364       if( v==0 ) return;
   353    365       n = (int)pEnd->z - (int)pParse->sFirstToken.z + 1;
   354    366       base = sqliteVdbeAddOpList(v, ArraySize(addTable), addTable);
   355    367       sqliteVdbeChangeP3(v, base+3, p->zName, 0);
   356    368       sqliteVdbeChangeP3(v, base+4, p->zName, 0);
   357    369       sqliteVdbeChangeP3(v, base+5, pParse->sFirstToken.z, n);
          370  +    if( addVersion ){
          371  +      sqliteVdbeAddOpList(v, ArraySize(addVersion), addVersion);
          372  +    }
          373  +    sqliteVdbeAddOp(v, OP_Close, 0, 0, 0, 0);
   358    374     }
   359    375   }
   360    376   
   361    377   /*
   362    378   ** Given a token, look up a table with that name.  If not found, leave
   363    379   ** an error for the parser to find and return NULL.
   364    380   */
................................................................................
   436    452       }else{
   437    453         Table *p;
   438    454         for(p=pParse->db->apTblHash[h]; p && p->pHash!=pTable; p=p->pHash){}
   439    455         if( p && p->pHash==pTable ){
   440    456           p->pHash = pTable->pHash;
   441    457         }
   442    458       }
          459  +    pParse->db->nTable--;
   443    460       sqliteDeleteTable(pParse->db, pTable);
   444    461     }
   445    462   }
   446    463   
   447    464   /*
   448    465   ** Create a new index for an SQL table.  pIndex is the name of the index 
   449    466   ** and pTable is the name of the table that is to be indexed.  Both will 

Changes to src/main.c.

    22     22   **
    23     23   *************************************************************************
    24     24   ** Main file for the SQLite library.  The routines in this file
    25     25   ** implement the programmer interface to the library.  Routines in
    26     26   ** other files are for internal use by SQLite and should not be
    27     27   ** accessed by users of the library.
    28     28   **
    29         -** $Id: main.c,v 1.15 2000/07/31 13:38:26 drh Exp $
           29  +** $Id: main.c,v 1.16 2000/08/02 13:47:42 drh Exp $
    30     30   */
    31     31   #include "sqliteInt.h"
    32     32   
    33     33   /*
    34     34   ** This is the callback routine for the code that initializes the
    35     35   ** database.  Each callback contains text of a CREATE TABLE or
    36     36   ** CREATE INDEX statement that must be parsed to yield the internal
    37     37   ** structures that describe the tables.
           38  +**
           39  +** This callback is also called with argc==2 when there is meta
           40  +** information in the sqlite_master file.  The meta information is
           41  +** contained in argv[1].  Typical meta information is the file format
           42  +** version.
    38     43   */
    39     44   static int sqliteOpenCb(void *pDb, int argc, char **argv, char **azColName){
    40     45     sqlite *db = (sqlite*)pDb;
    41     46     Parse sParse;
    42     47     int nErr;
    43     48   
           49  +  if( argc==2 ){
           50  +    if( sscanf(argv[1],"file format %d",&db->file_format)==1 ){
           51  +      return 0;
           52  +    }
           53  +    /* Unknown meta information.  Ignore it. */
           54  +    return 0;
           55  +  }
    44     56     if( argc!=1 ) return 0;
    45     57     memset(&sParse, 0, sizeof(sParse));
    46     58     sParse.db = db;
    47     59     sParse.initFlag = 1;
    48     60     nErr = sqliteRunParser(&sParse, argv[0], 0);
    49     61     return nErr;
    50     62   }
................................................................................
    79     91   
    80     92     /* The following program is used to initialize the internal
    81     93     ** structure holding the tables and indexes of the database.
    82     94     ** The database contains a special table named "sqlite_master"
    83     95     ** defined as follows:
    84     96     **
    85     97     **    CREATE TABLE sqlite_master (
    86         -  **        type       text,    --  Either "table" or "index"
           98  +  **        type       text,    --  Either "table" or "index" or "meta"
    87     99     **        name       text,    --  Name of table or index
    88    100     **        tbl_name   text,    --  Associated table 
    89    101     **        sql        text     --  The CREATE statement for this object
    90    102     **    );
    91    103     **
    92    104     ** The sqlite_master table contains a single entry for each table
    93    105     ** and each index.  The "type" column tells whether the entry is
................................................................................
    94    106     ** a table or index.  The "name" column is the name of the object.
    95    107     ** The "tbl_name" is the name of the associated table.  For tables,
    96    108     ** the tbl_name column is always the same as name.  For indices, the
    97    109     ** tbl_name column contains the name of the table that the index
    98    110     ** indexes.  Finally, the "sql" column contains the complete text of
    99    111     ** the CREATE TABLE or CREATE INDEX statement that originally created
   100    112     ** the table or index.
          113  +  **
          114  +  ** If the "type" column has the value "meta", then the "sql" column
          115  +  ** contains extra information about the database, such as the
          116  +  ** file format version number.  All meta information must be processed
          117  +  ** before any tables or indices are constructed.
   101    118     **
   102    119     ** The following program invokes its callback on the SQL for each
   103    120     ** table then goes back and invokes the callback on the
   104    121     ** SQL for each index.  The callback will invoke the
   105    122     ** parser to build the internal representation of the
   106    123     ** database scheme.
   107    124     */
   108    125     static VdbeOp initProg[] = {
   109    126       { OP_Open,     0, 0,  MASTER_NAME},
   110         -    { OP_Next,     0, 8,  0},           /* 1 */
          127  +    { OP_Next,     0, 9,  0},           /* 1 */
          128  +    { OP_Field,    0, 0,  0},
          129  +    { OP_String,   0, 0,  "meta"},
          130  +    { OP_Ne,       0, 1,  0},
          131  +    { OP_Field,    0, 0,  0},
          132  +    { OP_Field,    0, 3,  0},
          133  +    { OP_Callback, 2, 0,  0},
          134  +    { OP_Goto,     0, 1,  0},
          135  +    { OP_Rewind,   0, 0,  0},           /* 9 */
          136  +    { OP_Next,     0, 17, 0},           /* 10 */
   111    137       { OP_Field,    0, 0,  0},
   112    138       { OP_String,   0, 0,  "table"},
   113         -    { OP_Ne,       0, 1,  0},
          139  +    { OP_Ne,       0, 10, 0},
   114    140       { OP_Field,    0, 3,  0},
   115    141       { OP_Callback, 1, 0,  0},
   116         -    { OP_Goto,     0, 1,  0},
   117         -    { OP_Rewind,   0, 0,  0},           /* 8 */
   118         -    { OP_Next,     0, 16, 0},           /* 9 */
          142  +    { OP_Goto,     0, 10, 0},
          143  +    { OP_Rewind,   0, 0,  0},           /* 17 */
          144  +    { OP_Next,     0, 25, 0},           /* 18 */
   119    145       { OP_Field,    0, 0,  0},
   120    146       { OP_String,   0, 0,  "index"},
   121         -    { OP_Ne,       0, 9,  0},
          147  +    { OP_Ne,       0, 18, 0},
   122    148       { OP_Field,    0, 3,  0},
   123    149       { OP_Callback, 1, 0,  0},
   124         -    { OP_Goto,     0, 9,  0},
   125         -    { OP_Halt,     0, 0,  0},           /* 16 */
          150  +    { OP_Goto,     0, 18, 0},
          151  +    { OP_Halt,     0, 0,  0},           /* 25 */
   126    152     };
   127    153   
   128    154     /* Create a virtual machine to run the initialization program.  Run
   129    155     ** the program.  The delete the virtual machine.
   130    156     */
   131    157     vdbe = sqliteVdbeCreate(db->pBe);
   132    158     if( vdbe==0 ){
................................................................................
   133    159       sqliteSetString(pzErrMsg, "out of memory",0); 
   134    160       return 1;
   135    161     }
   136    162     sqliteVdbeAddOpList(vdbe, sizeof(initProg)/sizeof(initProg[0]), initProg);
   137    163     rc = sqliteVdbeExec(vdbe, sqliteOpenCb, db, pzErrMsg, 
   138    164                         db->pBusyArg, db->xBusyCallback);
   139    165     sqliteVdbeDelete(vdbe);
          166  +  if( rc==SQLITE_OK && db->file_format<2 && db->nTable>0 ){
          167  +    sqliteSetString(pzErrMsg, "obsolete file format", 0);
          168  +    rc = SQLITE_ERROR;
          169  +  }
   140    170     if( rc==SQLITE_OK ){
   141    171       Table *pTab;
   142    172       char *azArg[2];
   143    173       azArg[0] = master_schema;
   144    174       azArg[1] = 0;
   145    175       sqliteOpenCb(db, 1, azArg, 0);
   146    176       pTab = sqliteFindTable(db, MASTER_NAME);
................................................................................
   179    209     /* Open the backend database driver */
   180    210     db->pBe = sqliteDbbeOpen(zFilename, (mode&0222)!=0, mode!=0, pzErrMsg);
   181    211     if( db->pBe==0 ){
   182    212       sqliteStrRealloc(pzErrMsg);
   183    213       sqliteFree(db);
   184    214       return 0;
   185    215     }
          216  +
          217  +  /* Assume file format 1 unless the database says otherwise */
          218  +  db->file_format = 1;
   186    219   
   187    220     /* Attempt to read the schema */
   188    221     rc = sqliteInit(db, pzErrMsg);
   189    222     if( rc!=SQLITE_OK && rc!=SQLITE_BUSY ){
   190    223       sqlite_close(db);
   191    224       return 0;
   192    225     }else{

Changes to src/shell.c.

    20     20   **   drh@hwaci.com
    21     21   **   http://www.hwaci.com/drh/
    22     22   **
    23     23   *************************************************************************
    24     24   ** This file contains code to implement the "sqlite" command line
    25     25   ** utility for accessing SQLite databases.
    26     26   **
    27         -** $Id: shell.c,v 1.18 2000/07/31 11:57:37 drh Exp $
           27  +** $Id: shell.c,v 1.19 2000/08/02 13:47:42 drh Exp $
    28     28   */
    29     29   #include <stdlib.h>
    30     30   #include <string.h>
    31     31   #include <stdio.h>
    32     32   #include "sqlite.h"
    33     33   #include <unistd.h>
    34     34   #include <ctype.h>
................................................................................
   435    435     c = azArg[0][0];
   436    436    
   437    437     if( c=='d' && strncmp(azArg[0], "dump", n)==0 ){
   438    438       char *zErrMsg = 0;
   439    439       char zSql[1000];
   440    440       if( nArg==1 ){
   441    441         sprintf(zSql, "SELECT name, type, sql FROM sqlite_master "
          442  +                    "WHERE type!='meta' "
   442    443                       "ORDER BY tbl_name, type DESC, name");
   443    444         sqlite_exec(db, zSql, dump_callback, p, &zErrMsg);
   444    445       }else{
   445    446         int i;
   446    447         for(i=1; i<nArg && zErrMsg==0; i++){
   447    448           sprintf(zSql, "SELECT name, type, sql FROM sqlite_master "
   448         -                      "WHERE tbl_name LIKE '%.800s'"
          449  +                      "WHERE tbl_name LIKE '%.800s' AND type!='meta' "
   449    450                         "ORDER BY type DESC, name", azArg[i]);
   450    451           sqlite_exec(db, zSql, dump_callback, p, &zErrMsg);
   451    452           
   452    453         }
   453    454       }
   454    455       if( zErrMsg ){
   455    456         fprintf(stderr,"Error: %s\n", zErrMsg);
................................................................................
   547    548       char *zErrMsg = 0;
   548    549       char zSql[1000];
   549    550       memcpy(&data, p, sizeof(data));
   550    551       data.showHeader = 0;
   551    552       data.mode = MODE_List;
   552    553       if( nArg>1 ){
   553    554         sprintf(zSql, "SELECT sql FROM sqlite_master "
   554         -                    "WHERE tbl_name LIKE '%.800s'"
          555  +                    "WHERE tbl_name LIKE '%.800s' AND type!='meta'"
   555    556                       "ORDER BY type DESC, name",
   556    557            azArg[1]);
   557    558       }else{
   558    559         sprintf(zSql, "SELECT sql FROM sqlite_master "
          560  +         "WHERE type!='meta' "
   559    561            "ORDER BY tbl_name, type DESC, name");
   560    562       }
   561    563       sqlite_exec(db, zSql, callback, &data, &zErrMsg);
   562    564       if( zErrMsg ){
   563    565         fprintf(stderr,"Error: %s\n", zErrMsg);
   564    566         free(zErrMsg);
   565    567       }

Changes to src/sqliteInt.h.

    19     19   ** Author contact information:
    20     20   **   drh@hwaci.com
    21     21   **   http://www.hwaci.com/drh/
    22     22   **
    23     23   *************************************************************************
    24     24   ** Internal interface definitions for SQLite.
    25     25   **
    26         -** @(#) $Id: sqliteInt.h,v 1.28 2000/08/02 12:26:29 drh Exp $
           26  +** @(#) $Id: sqliteInt.h,v 1.29 2000/08/02 13:47:42 drh Exp $
    27     27   */
    28     28   #include "sqlite.h"
    29     29   #include "dbbe.h"
    30     30   #include "vdbe.h"
    31     31   #include "parse.h"
    32     32   #include <gdbm.h>
    33     33   #include <stdio.h>
................................................................................
   118    118   
   119    119   /*
   120    120   ** Each database is an instance of the following structure
   121    121   */
   122    122   struct sqlite {
   123    123     Dbbe *pBe;                 /* The backend driver */
   124    124     int flags;                 /* Miscellanous flags */
          125  +  int file_format;           /* What file format version is this database? */
          126  +  int nTable;                /* Number of tables in the database */
   125    127     void *pBusyArg;            /* 1st Argument to the busy callback */
   126    128     int (*xBusyCallback)(void *,const char*,int);  /* The busy callback */
   127    129     Table *apTblHash[N_HASH];  /* All tables of the database */
   128    130     Index *apIdxHash[N_HASH];  /* All indices of the database */
   129    131   };
   130    132   
   131    133   /*
   132    134   ** Possible values for the sqlite.flags.
   133    135   */
   134    136   #define SQLITE_VdbeTrace    0x00000001
   135    137   #define SQLITE_Initialized  0x00000002
   136    138   
          139  +/*
          140  +** Current file format version
          141  +*/
          142  +#define SQLITE_FileFormat 2
          143  +
   137    144   /*
   138    145   ** information about each column of an SQL table is held in an instance
   139    146   ** of this structure.
   140    147   */
   141    148   struct Column {
   142    149     char *zName;     /* Name of this column */
   143    150     char *zDflt;     /* Default value of this column */

Changes to test/index.test.

    19     19   #   drh@hwaci.com
    20     20   #   http://www.hwaci.com/drh/
    21     21   #
    22     22   #***********************************************************************
    23     23   # This file implements regression tests for SQLite library.  The
    24     24   # focus of this file is testing the CREATE INDEX statement.
    25     25   #
    26         -# $Id: index.test,v 1.6 2000/08/02 12:26:30 drh Exp $
           26  +# $Id: index.test,v 1.7 2000/08/02 13:47:42 drh Exp $
    27     27   
    28     28   set testdir [file dirname $argv0]
    29     29   source $testdir/tester.tcl
    30     30   
    31     31   # Create a basic index and verify it is added to sqlite_master
    32     32   #
    33     33   do_test index-1.1 {
    34     34     execsql {CREATE TABLE test1(f1 int, f2 int, f3 int)}
    35     35     execsql {CREATE INDEX index1 ON test1(f1)}
    36         -  execsql {SELECT name FROM sqlite_master ORDER BY name}
           36  +  execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
    37     37   } {index1 test1}
    38     38   do_test index-1.1b {
    39     39     execsql {SELECT name, sql, tbl_name, type FROM sqlite_master 
    40     40              WHERE name='index1'}
    41     41   } {index1 {CREATE INDEX index1 ON test1(f1)} test1 index}
    42     42   do_test index-1.1c {
    43     43     db close
................................................................................
    44     44     sqlite db testdb
    45     45     execsql {SELECT name, sql, tbl_name, type FROM sqlite_master 
    46     46              WHERE name='index1'}
    47     47   } {index1 {CREATE INDEX index1 ON test1(f1)} test1 index}
    48     48   do_test index-1.1d {
    49     49     db close
    50     50     sqlite db testdb
    51         -  execsql {SELECT name FROM sqlite_master ORDER BY name}
           51  +  execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
    52     52   } {index1 test1}
    53     53   
    54     54   # Verify that the index dies with the table
    55     55   #
    56     56   do_test index-1.2 {
    57     57     execsql {DROP TABLE test1}
    58         -  execsql {SELECT name FROM sqlite_master ORDER BY name}
           58  +  execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
    59     59   } {}
    60     60   
    61     61   # Try adding an index to a table that does not exist
    62     62   #
    63     63   do_test index-2.1 {
    64     64     set v [catch {execsql {CREATE INDEX index1 ON test1(f1)}} msg]
    65     65     lappend v $msg
................................................................................
   133    133   do_test index-4.1 {
   134    134     execsql {CREATE TABLE test1(cnt int, power int)}
   135    135     for {set i 1} {$i<20} {incr i} {
   136    136       execsql "INSERT INTO test1 VALUES($i,[expr {int(pow(2,$i))}])"
   137    137     }
   138    138     execsql {CREATE INDEX index9 ON test1(cnt)}
   139    139     execsql {CREATE INDEX indext ON test1(power)}
   140         -  execsql {SELECT name FROM sqlite_master ORDER BY name}
          140  +  execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
   141    141   } {index9 indext test1}
   142    142   do_test index-4.2 {
   143    143     execsql {SELECT cnt FROM test1 WHERE power=4}
   144    144   } {2}
   145    145   do_test index-4.3 {
   146    146     execsql {SELECT cnt FROM test1 WHERE power=1024}
   147    147   } {10}
................................................................................
   174    174     execsql {SELECT power FROM test1 WHERE cnt=6}
   175    175   } {64}
   176    176   do_test index-4.12 {
   177    177     execsql {SELECT cnt FROM test1 WHERE power=1024}
   178    178   } {10}
   179    179   do_test index-4.13 {
   180    180     execsql {DROP TABLE test1}
   181         -  execsql {SELECT name FROM sqlite_master ORDER BY name}
          181  +  execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
   182    182   } {}
   183    183   
   184    184   # Do not allow indices to be added to sqlite_master
   185    185   #
   186    186   do_test index-5.1 {
   187    187     set v [catch {execsql {CREATE INDEX index1 ON sqlite_master(name)}} msg]
   188    188     lappend v $msg
   189    189   } {1 {table sqlite_master may not have new indices added}}
   190    190   do_test index-5.2 {
   191         -  execsql {SELECT name FROM sqlite_master}
          191  +  execsql {SELECT name FROM sqlite_master WHERE type!='meta'}
   192    192   } {}
   193    193   
   194    194   # Do not allow indices with duplicate names to be added
   195    195   #
   196    196   do_test index-6.1 {
   197    197     execsql {CREATE TABLE test1(f1 int, f2 int)}
   198    198     execsql {CREATE TABLE test2(g1 real, g2 real)}
   199    199     execsql {CREATE INDEX index1 ON test1(f1)}
   200    200     set v [catch {execsql {CREATE INDEX index1 ON test2(g1)}} msg]
   201    201     lappend v $msg
   202    202   } {1 {index index1 already exists}}
   203    203   do_test index-6.1b {
   204         -  execsql {SELECT name FROM sqlite_master ORDER BY name}
          204  +  execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
   205    205   } {index1 test1 test2}
   206    206   do_test index-6.2 {
   207    207     set v [catch {execsql {CREATE INDEX test1 ON test2(g1)}} msg]
   208    208     lappend v $msg
   209    209   } {1 {there is already a table named test1}}
   210    210   do_test index-6.2b {
   211         -  execsql {SELECT name FROM sqlite_master ORDER BY name}
          211  +  execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
   212    212   } {index1 test1 test2}
   213    213   do_test index-6.3 {
   214    214     execsql {DROP TABLE test1}
   215    215     execsql {DROP TABLE test2}
   216         -  execsql {SELECT name FROM sqlite_master ORDER BY name}
          216  +  execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
   217    217   } {}
   218    218   
   219    219   # Create a primary key
   220    220   #
   221    221   do_test index-7.1 {
   222    222     execsql {CREATE TABLE test1(f1 int, f2 int primary key)}
   223    223     for {set i 1} {$i<20} {incr i} {
................................................................................
   230    230   } {16}
   231    231   do_test index-7.3 {
   232    232     set code [execsql {EXPLAIN SELECT f1 FROM test1 WHERE f2=65536}]
   233    233     expr {[lsearch $code test1__primary_key]>0}
   234    234   } {1}
   235    235   do_test index-7.4 {
   236    236     execsql {DROP table test1}
   237         -  execsql {SELECT name FROM sqlite_master}
          237  +  execsql {SELECT name FROM sqlite_master WHERE type!='meta'}
   238    238   } {}
   239    239   
   240    240   # Make sure we cannot drop a non-existant index.
   241    241   #
   242    242   do_test index-8.1 {
   243    243     set v [catch {execsql {DROP INDEX index1}} msg]
   244    244     lappend v $msg

Changes to test/table.test.

    19     19   #   drh@hwaci.com
    20     20   #   http://www.hwaci.com/drh/
    21     21   #
    22     22   #***********************************************************************
    23     23   # This file implements regression tests for SQLite library.  The
    24     24   # focus of this file is testing the CREATE TABLE statement.
    25     25   #
    26         -# $Id: table.test,v 1.5 2000/06/08 15:10:48 drh Exp $
           26  +# $Id: table.test,v 1.6 2000/08/02 13:47:43 drh Exp $
    27     27   
    28     28   set testdir [file dirname $argv0]
    29     29   source $testdir/tester.tcl
    30     30   
    31     31   # Create a basic table and verify it is added to sqlite_master
    32     32   #
    33     33   do_test table-1.1 {
................................................................................
    34     34     execsql {
    35     35       CREATE TABLE test1 (
    36     36         one varchar(10),
    37     37         two text
    38     38       )
    39     39     }
    40     40     execsql {
    41         -    SELECT sql FROM sqlite_master
           41  +    SELECT sql FROM sqlite_master WHERE type!='meta'
    42     42     }
    43     43   } {{CREATE TABLE test1 (
    44     44         one varchar(10),
    45     45         two text
    46     46       )}}
    47     47   
    48     48   # Verify that both table files exists in the database directory
................................................................................
    51     51     execsql {INSERT INTO test1 VALUES('hi', 'y''all')}
    52     52     lsort [glob -nocomplain testdb/*.tbl]
    53     53   } {testdb/sqlite_master.tbl testdb/test1.tbl}
    54     54   
    55     55   # Verify the other fields of the sqlite_master file.
    56     56   #
    57     57   do_test table-1.3 {
    58         -  execsql {SELECT name, tbl_name, type FROM sqlite_master}
           58  +  execsql {SELECT name, tbl_name, type FROM sqlite_master WHERE type!='meta'}
    59     59   } {test1 test1 table}
    60     60   
    61     61   # Close and reopen the database.  Verify that everything is
    62     62   # still the same.
    63     63   #
    64     64   do_test table-1.4 {
    65     65     db close
    66     66     sqlite db testdb
    67         -  execsql {SELECT name, tbl_name, type from sqlite_master}
           67  +  execsql {SELECT name, tbl_name, type from sqlite_master WHERE type!='meta'}
    68     68   } {test1 test1 table}
    69     69   
    70     70   # Drop the database and make sure it disappears.
    71     71   #
    72     72   do_test table-1.5 {
    73     73     execsql {DROP TABLE test1}
    74         -  execsql {SELECT * FROM sqlite_master}
           74  +  execsql {SELECT * FROM sqlite_master WHERE type!='meta'}
    75     75   } {}
    76     76   
    77     77   # Verify that the file associated with the database is gone.
    78     78   #
    79     79   do_test table-1.5 {
    80     80     lsort [glob -nocomplain testdb/*.tbl]
    81     81   } {testdb/sqlite_master.tbl}
................................................................................
    82     82   
    83     83   # Close and reopen the database.  Verify that the table is
    84     84   # still gone.
    85     85   #
    86     86   do_test table-1.6 {
    87     87     db close
    88     88     sqlite db testdb
    89         -  execsql {SELECT name FROM sqlite_master}
           89  +  execsql {SELECT name FROM sqlite_master WHERE type!='meta'}
    90     90   } {}
    91     91   
    92     92   # Repeat the above steps, but this time quote the table name.
    93     93   #
    94     94   do_test table-1.10 {
    95     95     execsql {CREATE TABLE "create" (f1 int)}
    96         -  execsql {SELECT name FROM sqlite_master}
           96  +  execsql {SELECT name FROM sqlite_master WHERE type!='meta'}
    97     97   } {create}
    98     98   do_test table-1.11 {
    99     99     execsql {DROP TABLE "create"}
   100         -  execsql {SELECT name FROM "sqlite_master"}
          100  +  execsql {SELECT name FROM "sqlite_master" WHERE type!='meta'}
   101    101   } {}
   102    102   do_test table-1.12 {
   103    103     execsql {CREATE TABLE test1("f1 ho" int)}
   104         -  execsql {SELECT name as "X" FROM sqlite_master}
          104  +  execsql {SELECT name as "X" FROM sqlite_master WHERE type!='meta'}
   105    105   } {test1}
   106    106   do_test table-1.13 {
   107    107     execsql {DROP TABLE "TEST1"}
   108         -  execsql {SELECT name FROM "sqlite_master"}
          108  +  execsql {SELECT name FROM "sqlite_master" WHERE type!='meta'}
   109    109   } {}
   110    110   
   111    111   
   112    112   
   113    113   # Verify that we cannot make two tables with the same name
   114    114   #
   115    115   do_test table-2.1 {
................................................................................
   124    124   do_test table-2.1c {
   125    125     db close
   126    126     sqlite db testdb
   127    127     set v [catch {execsql {CREATE TABLE sqlite_master(two text)}} msg]
   128    128     lappend v $msg
   129    129   } {1 {table sqlite_master already exists}}
   130    130   do_test table-2.1d {
   131         -  execsql {DROP TABLE test2; SELECT name FROM sqlite_master}
          131  +  execsql {DROP TABLE test2; SELECT name FROM sqlite_master WHERE type!='meta'}
   132    132   } {}
   133    133   
   134    134   # Verify that we cannot make a table with the same name as an index
   135    135   #
   136    136   do_test table-2.2a {
   137    137     execsql {CREATE TABLE test2(one text); CREATE INDEX test3 ON test2(one)}
   138    138     set v [catch {execsql {CREATE TABLE test3(two text)}} msg]
................................................................................
   146    146   } {1 {there is already an index named test3}}
   147    147   do_test table-2.2c {
   148    148     execsql {DROP INDEX test3}
   149    149     set v [catch {execsql {CREATE TABLE test3(two text)}} msg]
   150    150     lappend v $msg
   151    151   } {0 {}}
   152    152   do_test table-2.2d {
   153         -  execsql {SELECT name FROM sqlite_master ORDER BY name}
          153  +  execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
   154    154   } {test2 test3}
   155    155   do_test table-2.2e {
   156    156     execsql {DROP TABLE test2; DROP TABLE test3}
   157         -  execsql {SELECT name FROM sqlite_master ORDER BY name}
          157  +  execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
   158    158   } {}
   159    159   
   160    160   # Create a table with many field names
   161    161   #
   162    162   set big_table \
   163    163   {CREATE TABLE big(
   164    164     f1 varchar(20),
................................................................................
   180    180     f17 text,
   181    181     f18 text,
   182    182     f19 text,
   183    183     f20 text
   184    184   )}
   185    185   do_test table-3.1 {
   186    186     execsql $big_table
   187         -  execsql {SELECT sql FROM sqlite_master}
          187  +  execsql {SELECT sql FROM sqlite_master WHERE type!='meta'}
   188    188   } \{$big_table\}
   189    189   do_test table-3.2 {
   190    190     set v [catch {execsql {CREATE TABLE BIG(xyz foo)}} msg]
   191    191     lappend v $msg
   192    192   } {1 {table BIG already exists}}
   193    193   do_test table-3.3 {
   194    194     set v [catch {execsql {CREATE TABLE biG(xyz foo)}} msg]
................................................................................
   202    202     db close
   203    203     sqlite db testdb
   204    204     set v [catch {execsql {CREATE TABLE Big(xyz foo)}} msg]
   205    205     lappend v $msg
   206    206   } {1 {table Big already exists}}
   207    207   do_test table-3.6 {
   208    208     execsql {DROP TABLE big}
   209         -  execsql {SELECT name FROM sqlite_master}
          209  +  execsql {SELECT name FROM sqlite_master WHERE type!='meta'}
   210    210   } {}
   211    211   
   212    212   # Try creating large numbers of tables
   213    213   #
   214    214   set r {}
   215    215   for {set i 1} {$i<=100} {incr i} {
   216    216     lappend r test$i
................................................................................
   220    220       set sql "CREATE TABLE test$i ("
   221    221       for {set k 1} {$k<$i} {incr k} {
   222    222         append sql "field$k text,"
   223    223       }
   224    224       append sql "last_field text)"
   225    225       execsql $sql
   226    226     }
   227         -  execsql {SELECT name FROM sqlite_master ORDER BY name}
          227  +  execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
   228    228   } $r
   229    229   do_test table-4.1b {
   230    230     db close
   231    231     sqlite db testdb
   232         -  execsql {SELECT name FROM sqlite_master ORDER BY name}
          232  +  execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
   233    233   } $r
   234    234   
   235    235   # Drop the even number tables
   236    236   #
   237    237   set r {}
   238    238   for {set i 1} {$i<=100} {incr i 2} {
   239    239     lappend r test$i
   240    240   }
   241    241   do_test table-4.2 {
   242    242     for {set i 2} {$i<=100} {incr i 2} {
   243    243       set sql "DROP TABLE TEST$i"
   244    244       execsql $sql
   245    245     }
   246         -  execsql {SELECT name FROM sqlite_master ORDER BY name}
          246  +  execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
   247    247   } $r
   248    248   
   249    249   # Drop the odd number tables
   250    250   #
   251    251   do_test table-4.3 {
   252    252     for {set i 1} {$i<=100} {incr i 2} {
   253    253       set sql "DROP TABLE test$i"
   254    254       execsql $sql
   255    255     }
   256         -  execsql {SELECT name FROM sqlite_master ORDER BY name}
          256  +  execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
   257    257   } {}
   258    258   
   259    259   # Try to drop a table that does not exist
   260    260   #
   261    261   do_test table-5.1 {
   262    262     set v [catch {execsql {DROP TABLE test9}} msg]
   263    263     lappend v $msg
................................................................................
   270    270     lappend v $msg
   271    271   } {1 {table sqlite_master may not be dropped}}
   272    272   
   273    273   # Make sure an EXPLAIN does not really create a new table
   274    274   #
   275    275   do_test table-5.3 {
   276    276     execsql {EXPLAIN CREATE TABLE test1(f1 int)}
   277         -  execsql {SELECT name FROM sqlite_master}
          277  +  execsql {SELECT name FROM sqlite_master WHERE type!='meta'}
   278    278   } {}
   279    279   
   280    280   # Make sure an EXPLAIN does not really drop an existing table
   281    281   #
   282    282   do_test table-5.4 {
   283    283     execsql {CREATE TABLE test1(f1 int)}
   284    284     execsql {EXPLAIN DROP TABLE test1}
   285         -  execsql {SELECT name FROM sqlite_master}
          285  +  execsql {SELECT name FROM sqlite_master WHERE type!='meta'}
   286    286   } {test1}
   287    287   
   288    288   # Create a table with a goofy name
   289    289   #
   290    290   do_test table-6.1 {
   291    291     execsql {CREATE TABLE 'Spaces In This Name!'(x int)}
   292    292     execsql {INSERT INTO 'spaces in this name!' VALUES(1)}
   293    293     set list [glob -nocomplain testdb/spaces*.tbl]
   294    294   } {testdb/spaces+in+this+name+.tbl}
   295    295   
   296    296   finish_test