/ Check-in [1f43219a]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Fix for #764. When reloading the schema, load the temp schema last. (CVS 1628)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 1f43219a7402af7255743466731dba2afb31d12b
User & Date: danielk1977 2004-06-19 02:22:10
Context
2004-06-19
03:26
Update sqlite.def for version 3.0. (CVS 1629) check-in: 327780ad user: drh tags: trunk
02:22
Fix for #764. When reloading the schema, load the temp schema last. (CVS 1628) check-in: 1f43219a user: danielk1977 tags: trunk
02:19
fix dependencies for testfixture in Makefile.in (CVS 1627) check-in: 26676538 user: dougcurrie tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/build.c.

19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
...
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
**     DROP INDEX
**     creating ID lists
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**     PRAGMA
**
** $Id: build.c,v 1.222 2004/06/18 06:02:35 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** This routine is called when a new SQL statement is beginning to
** be parsed.  Check to see if the schema for the database needs
................................................................................
  ** PRIMARY KEY or UNIQUE keywords are parsed.  Those keywords will cause
  ** indices to be created and the table record must come before the 
  ** indices.  Hence, the record number for the table must be allocated
  ** now.
  */
  if( !db->init.busy && (v = sqlite3GetVdbe(pParse))!=0 ){
    sqlite3BeginWriteOperation(pParse, 0, iDb);
    if( !isTemp ){
      /* Every time a new table is created the file-format
      ** and encoding meta-values are set in the database, in
      ** case this is the first table created.
      */
      sqlite3VdbeAddOp(v, OP_Integer, db->file_format, 0);
      sqlite3VdbeAddOp(v, OP_SetCookie, iDb, 1);
      sqlite3VdbeAddOp(v, OP_Integer, db->enc, 0);
      sqlite3VdbeAddOp(v, OP_SetCookie, iDb, 4);
    }
    sqlite3OpenMasterTable(v, iDb);
    sqlite3VdbeAddOp(v, OP_NewRecno, 0, 0);
    sqlite3VdbeAddOp(v, OP_Dup, 0, 0);
    sqlite3VdbeAddOp(v, OP_String8, 0, 0);
    sqlite3VdbeAddOp(v, OP_PutIntKey, 0, 0);
  }
}







|







 







<
|
|
|
|
|
|
|
|
|







19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
...
652
653
654
655
656
657
658

659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
**     DROP INDEX
**     creating ID lists
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**     PRAGMA
**
** $Id: build.c,v 1.223 2004/06/19 02:22:10 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** This routine is called when a new SQL statement is beginning to
** be parsed.  Check to see if the schema for the database needs
................................................................................
  ** PRIMARY KEY or UNIQUE keywords are parsed.  Those keywords will cause
  ** indices to be created and the table record must come before the 
  ** indices.  Hence, the record number for the table must be allocated
  ** now.
  */
  if( !db->init.busy && (v = sqlite3GetVdbe(pParse))!=0 ){
    sqlite3BeginWriteOperation(pParse, 0, iDb);

    /* Every time a new table is created the file-format
    ** and encoding meta-values are set in the database, in
    ** case this is the first table created.
    */
    sqlite3VdbeAddOp(v, OP_Integer, db->file_format, 0);
    sqlite3VdbeAddOp(v, OP_SetCookie, iDb, 1);
    sqlite3VdbeAddOp(v, OP_Integer, db->enc, 0);
    sqlite3VdbeAddOp(v, OP_SetCookie, iDb, 4);

    sqlite3OpenMasterTable(v, iDb);
    sqlite3VdbeAddOp(v, OP_NewRecno, 0, 0);
    sqlite3VdbeAddOp(v, OP_Dup, 0, 0);
    sqlite3VdbeAddOp(v, OP_String8, 0, 0);
    sqlite3VdbeAddOp(v, OP_PutIntKey, 0, 0);
  }
}

Changes to src/main.c.

10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
...
138
139
140
141
142
143
144
145
146
147
148


149
150
151
152
153
154
155
...
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187




188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
...
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320

321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
...
356
357
358
359
360
361
362
363
364
365
366
367
368
369












370
371
372
373
374
375
376
**
*************************************************************************
** Main file for the SQLite library.  The routines in this file
** implement the programmer interface to the library.  Routines in
** other files are for internal use by SQLite and should not be
** accessed by users of the library.
**
** $Id: main.c,v 1.226 2004/06/18 17:10:17 drh Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>

/*
** A pointer to this structure is used to communicate information
................................................................................
** indicate success or failure.
*/
static int sqlite3InitOne(sqlite *db, int iDb, char **pzErrMsg){
  int rc;
  BtCursor *curMain;
  int size;
  Table *pTab;
  char *azArg[6];
  char zDbNum[30];
  int meta[10];
  InitData initData;



  /*
  ** The master database table has a structure like this
  */
  static char master_schema[] = 
     "CREATE TABLE sqlite_master(\n"
     "  type text,\n"
................................................................................
     "  name text,\n"
     "  tbl_name text,\n"
     "  rootpage integer,\n"
     "  sql text\n"
     ")"
  ;

  /* The following SQL will read the schema from the master tables.
  */
  static char init_script1[] = 
     "SELECT type, name, rootpage, sql, 1 FROM sqlite_temp_master";
  static char init_script2[] = 
     "SELECT type, name, rootpage, sql, 0 FROM sqlite_master";

  assert( iDb>=0 && iDb!=1 && iDb<db->nDb );

  /* Construct the schema tables: sqlite_master and sqlite_temp_master
  */
  sqlite3SafetyOff(db);
  azArg[0] = "table";
  azArg[1] = MASTER_NAME;
  azArg[2] = "1";
  azArg[3] = master_schema;




  sprintf(zDbNum, "%d", iDb);
  azArg[4] = zDbNum;
  azArg[5] = 0;
  initData.db = db;
  initData.pzErrMsg = pzErrMsg;
  sqlite3InitCallback(&initData, 5, azArg, 0);
  pTab = sqlite3FindTable(db, MASTER_NAME, "main");
  if( pTab ){
    pTab->readOnly = 1;
  }
  if( iDb==0 ){
    azArg[1] = TEMP_MASTER_NAME;
    azArg[3] = temp_master_schema;
    azArg[4] = "1";
    sqlite3InitCallback(&initData, 5, azArg, 0);
    pTab = sqlite3FindTable(db, TEMP_MASTER_NAME, "temp");
    if( pTab ){
      pTab->readOnly = 1;
    }
  }
  sqlite3SafetyOn(db);

  /* Create a cursor to hold the database open
  */
  if( db->aDb[iDb].pBt==0 ) return SQLITE_OK;
  rc = sqlite3BtreeCursor(db->aDb[iDb].pBt, MASTER_ROOT, 0, 0, 0, &curMain);
  if( rc!=SQLITE_OK && rc!=SQLITE_EMPTY ){
................................................................................
  /* Read the schema information out of the schema tables
  */
  assert( db->init.busy );
  if( rc==SQLITE_EMPTY ){
    /* For an empty database, there is nothing to read */
    rc = SQLITE_OK;
  }else{
    sqlite3SafetyOff(db);
    if( iDb==0 ){
      /* This SQL statement tries to read the temp.* schema from the
      ** sqlite_temp_master table. It might return SQLITE_EMPTY. 
      */
      rc = sqlite3_exec(db, init_script1, sqlite3InitCallback, &initData, 0);
      if( rc==SQLITE_OK || rc==SQLITE_EMPTY ){
        rc = sqlite3_exec(db, init_script2, sqlite3InitCallback, &initData, 0);
      }
    }else{
      char *zSql = 0;

      sqlite3SetString(&zSql, 
         "SELECT type, name, rootpage, sql, ", zDbNum, " FROM \"",
         db->aDb[iDb].zName, "\".sqlite_master", (char*)0);
      rc = sqlite3_exec(db, zSql, sqlite3InitCallback, &initData, 0);
      sqliteFree(zSql);
    }
    sqlite3SafetyOn(db);
    sqlite3BtreeCloseCursor(curMain);
  }
  if( sqlite3_malloc_failed ){
    sqlite3SetString(pzErrMsg, "out of memory", (char*)0);
    rc = SQLITE_NOMEM;
    sqlite3ResetInternalSchema(db, 0);
  }
  if( rc==SQLITE_OK ){
    DbSetProperty(db, iDb, DB_SchemaLoaded);
    if( iDb==0 ){
      DbSetProperty(db, 1, DB_SchemaLoaded);
    }
  }else{
    sqlite3ResetInternalSchema(db, iDb);
  }
  return rc;
}

/*
................................................................................
  int i, rc;
  
  if( db->init.busy ) return SQLITE_OK;
  assert( (db->flags & SQLITE_Initialized)==0 );
  rc = SQLITE_OK;
  db->init.busy = 1;
  for(i=0; rc==SQLITE_OK && i<db->nDb; i++){
    if( DbHasProperty(db, i, DB_SchemaLoaded) ) continue;
    assert( i!=1 );  /* Should have been initialized together with 0 */
    rc = sqlite3InitOne(db, i, pzErrMsg);
    if( rc ){
      sqlite3ResetInternalSchema(db, i);
    }
  }












  db->init.busy = 0;
  if( rc==SQLITE_OK ){
    db->flags |= SQLITE_Initialized;
    sqlite3CommitInternalChanges(db);
  }

  if( rc!=SQLITE_OK ){







|







 







|



>
>







 







|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
>
>
>
>





|
|



<
<
<
<
<
<
<
<
<
<







 







<
<
<
<
<
<
<
<
<
<
|
>
|
|
|
|
|
<










<
<
<







 







|
<





>
>
>
>
>
>
>
>
>
>
>
>







10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
...
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
...
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203










204
205
206
207
208
209
210
...
299
300
301
302
303
304
305










306
307
308
309
310
311
312

313
314
315
316
317
318
319
320
321
322



323
324
325
326
327
328
329
...
339
340
341
342
343
344
345
346

347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
**
*************************************************************************
** Main file for the SQLite library.  The routines in this file
** implement the programmer interface to the library.  Routines in
** other files are for internal use by SQLite and should not be
** accessed by users of the library.
**
** $Id: main.c,v 1.227 2004/06/19 02:22:11 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>

/*
** A pointer to this structure is used to communicate information
................................................................................
** indicate success or failure.
*/
static int sqlite3InitOne(sqlite *db, int iDb, char **pzErrMsg){
  int rc;
  BtCursor *curMain;
  int size;
  Table *pTab;
  char const *azArg[6];
  char zDbNum[30];
  int meta[10];
  InitData initData;
  char const *zMasterSchema;
  char const *zMasterName;

  /*
  ** The master database table has a structure like this
  */
  static char master_schema[] = 
     "CREATE TABLE sqlite_master(\n"
     "  type text,\n"
................................................................................
     "  name text,\n"
     "  tbl_name text,\n"
     "  rootpage integer,\n"
     "  sql text\n"
     ")"
  ;

  assert( iDb>=0 && iDb<db->nDb );

  /* zMasterSchema and zInitScript are set to point at the master schema
  ** and initialisation script appropriate for the database being
  ** initialised. zMasterName is the name of the master table.
  */
  if( iDb==1 ){
    zMasterSchema = temp_master_schema;
    zMasterName = TEMP_MASTER_NAME;
  }else{
    zMasterSchema = master_schema;
    zMasterName = MASTER_NAME;
  }

  /* Construct the schema tables.  */
  sqlite3SafetyOff(db);
  azArg[0] = "table";
  azArg[1] = zMasterName;
  azArg[2] = "1";
  azArg[3] = zMasterSchema;
  sprintf(zDbNum, "%d", iDb);
  azArg[4] = zDbNum;
  azArg[5] = 0;
  initData.db = db;
  initData.pzErrMsg = pzErrMsg;
  sqlite3InitCallback(&initData, 5, (char **)azArg, 0);
  pTab = sqlite3FindTable(db, zMasterName, db->aDb[iDb].zName);
  if( pTab ){
    pTab->readOnly = 1;
  }










  sqlite3SafetyOn(db);

  /* Create a cursor to hold the database open
  */
  if( db->aDb[iDb].pBt==0 ) return SQLITE_OK;
  rc = sqlite3BtreeCursor(db->aDb[iDb].pBt, MASTER_ROOT, 0, 0, 0, &curMain);
  if( rc!=SQLITE_OK && rc!=SQLITE_EMPTY ){
................................................................................
  /* Read the schema information out of the schema tables
  */
  assert( db->init.busy );
  if( rc==SQLITE_EMPTY ){
    /* For an empty database, there is nothing to read */
    rc = SQLITE_OK;
  }else{










    char *zSql = 0;
    sqlite3SafetyOff(db);
    sqlite3SetString(&zSql, 
        "SELECT type, name, rootpage, sql, ", zDbNum, " FROM \"",
        db->aDb[iDb].zName, "\".", zMasterName, (char*)0);
    rc = sqlite3_exec(db, zSql, sqlite3InitCallback, &initData, 0);
    sqliteFree(zSql);

    sqlite3SafetyOn(db);
    sqlite3BtreeCloseCursor(curMain);
  }
  if( sqlite3_malloc_failed ){
    sqlite3SetString(pzErrMsg, "out of memory", (char*)0);
    rc = SQLITE_NOMEM;
    sqlite3ResetInternalSchema(db, 0);
  }
  if( rc==SQLITE_OK ){
    DbSetProperty(db, iDb, DB_SchemaLoaded);



  }else{
    sqlite3ResetInternalSchema(db, iDb);
  }
  return rc;
}

/*
................................................................................
  int i, rc;
  
  if( db->init.busy ) return SQLITE_OK;
  assert( (db->flags & SQLITE_Initialized)==0 );
  rc = SQLITE_OK;
  db->init.busy = 1;
  for(i=0; rc==SQLITE_OK && i<db->nDb; i++){
    if( DbHasProperty(db, i, DB_SchemaLoaded) || i==1 ) continue;

    rc = sqlite3InitOne(db, i, pzErrMsg);
    if( rc ){
      sqlite3ResetInternalSchema(db, i);
    }
  }

  /* Once all the other databases have been initialised, load the schema
  ** for the TEMP database. This is loaded last, as the TEMP database
  ** schema may contain references to objects in other databases.
  */
  if( rc==SQLITE_OK && db->nDb>1 && !DbHasProperty(db, 1, DB_SchemaLoaded) ){
    rc = sqlite3InitOne(db, 1, pzErrMsg);
    if( rc ){
      sqlite3ResetInternalSchema(db, 1);
    }
  }

  db->init.busy = 0;
  if( rc==SQLITE_OK ){
    db->flags |= SQLITE_Initialized;
    sqlite3CommitInternalChanges(db);
  }

  if( rc!=SQLITE_OK ){

Changes to test/attach3.test.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
207
208
209
210
211
212
213








214
215
216
217
218
219
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is testing the ATTACH and DETACH commands
# and schema changes to attached databases.
#
# $Id: attach3.test,v 1.7 2004/06/19 00:16:31 drh Exp $
#


set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Create tables t1 and t2 in the main database
................................................................................
} {1}
do_test attach3-9.2 {
  execsql {
    DROP TABLE aux.t4;
    SELECT count(*) FROM sqlite_temp_master;
  }
} {0}









finish_test











|







 







>
>
>
>
>
>
>
>






8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is testing the ATTACH and DETACH commands
# and schema changes to attached databases.
#
# $Id: attach3.test,v 1.8 2004/06/19 02:22:11 danielk1977 Exp $
#


set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Create tables t1 and t2 in the main database
................................................................................
} {1}
do_test attach3-9.2 {
  execsql {
    DROP TABLE aux.t4;
    SELECT count(*) FROM sqlite_temp_master;
  }
} {0}

# Make sure the aux.sqlite_master table is read-only
do_test attach3-10.0 {
  catchsql {
    INSERT INTO aux.sqlite_master VALUES(1, 2, 3, 4, 5);
  }
} {1 {table sqlite_master may not be modified}}


finish_test




Changes to test/trigger1.test.

408
409
410
411
412
413
414




415

































































































416
do_test trigger-9.2 {
  execsql {
    INSERT INTO t3 VALUES(1,3);
    SELECT * FROM t3 UNION ALL SELECT 99, 99 UNION ALL SELECT * FROM t4;
  }
} {1 2 1 3 99 99 1 3}







































































































finish_test







>
>
>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
do_test trigger-9.2 {
  execsql {
    INSERT INTO t3 VALUES(1,3);
    SELECT * FROM t3 UNION ALL SELECT 99, 99 UNION ALL SELECT * FROM t4;
  }
} {1 2 1 3 99 99 1 3}

execsql {
  DROP TABLE t2;
  DROP TABLE t3;
  DROP TABLE t4;
}

# Ticket #764. At one stage TEMP triggers would fail to re-install when the
# schema was reloaded. The following tests ensure that TEMP triggers are
# correctly re-installed.
#
# Also verify that references within trigger programs are resolved at
# statement compile time, not trigger installation time. This means, for
# example, that you can drop and re-create tables referenced by triggers. 
do_test trigger-10.0 {
  file delete -force test2.db
  file delete -force test2.db-journal
  execsql {
    ATTACH 'test2.db' AS aux;
  }
} {}
do_test trigger-10.1 {
  execsql {
    CREATE TABLE main.t4(a, b, c);
    CREATE TABLE temp.t4(a, b, c);
    CREATE TABLE aux.t4(a, b, c);
    CREATE TABLE insert_log(db, a, b, c);
  }
} {}
do_test trigger-10.2 {
  execsql {
    CREATE TEMP TRIGGER trig1 AFTER INSERT ON main.t4 BEGIN 
      INSERT INTO insert_log VALUES('main', new.a, new.b, new.c);
    END;
    CREATE TEMP TRIGGER trig2 AFTER INSERT ON temp.t4 BEGIN 
      INSERT INTO insert_log VALUES('temp', new.a, new.b, new.c);
    END;
    CREATE TEMP TRIGGER trig3 AFTER INSERT ON aux.t4 BEGIN 
      INSERT INTO insert_log VALUES('aux', new.a, new.b, new.c);
    END;
  }
} {}
do_test trigger-10.3 {
  execsql {
    INSERT INTO main.t4 VALUES(1, 2, 3);
    INSERT INTO temp.t4 VALUES(4, 5, 6);
    INSERT INTO aux.t4  VALUES(7, 8, 9);
  }
} {}
do_test trigger-10.4 {
  execsql {
    SELECT * FROM insert_log;
  }
} {main 1 2 3 temp 4 5 6 aux 7 8 9}
do_test trigger-10.5 {
  execsql {
    BEGIN;
    INSERT INTO main.t4 VALUES(1, 2, 3);
    INSERT INTO temp.t4 VALUES(4, 5, 6);
    INSERT INTO aux.t4  VALUES(7, 8, 9);
    ROLLBACK;
  }
} {}
do_test trigger-10.6 {
  execsql {
    SELECT * FROM insert_log;
  }
} {main 1 2 3 temp 4 5 6 aux 7 8 9}
do_test trigger-10.7 {
  execsql {
    DELETE FROM insert_log;
    INSERT INTO main.t4 VALUES(11, 12, 13);
    INSERT INTO temp.t4 VALUES(14, 15, 16);
    INSERT INTO aux.t4  VALUES(17, 18, 19);
  }
} {}
do_test trigger-10.8 {
  execsql {
    SELECT * FROM insert_log;
  }
} {main 11 12 13 temp 14 15 16 aux 17 18 19}
do_test trigger-10.8 {
# Drop and re-create the insert_log table in a different database. Note
# that we can change the column names because the trigger programs don't
# use them explicitly.
  execsql {
    DROP TABLE insert_log;
    CREATE TABLE aux.insert_log(db, d, e, f);
  }
} {}
do_test trigger-10.10 {
  execsql {
    INSERT INTO main.t4 VALUES(21, 22, 23);
    INSERT INTO temp.t4 VALUES(24, 25, 26);
    INSERT INTO aux.t4  VALUES(27, 28, 29);
  }
} {}
do_test trigger-10.11 {
  execsql {
    SELECT * FROM insert_log;
  }
} {main 21 22 23 temp 24 25 26 aux 27 28 29}

finish_test