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: |
b7b90237945d3577caba3a2f5595e52b |
| 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
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