/ Check-in [7257fcc8]
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:Change the name of the SQLITE_OPEN_REUSE_SCHEMA flag to SQLITE_OPEN_SHARED_SCHEMA.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | reuse-schema
Files: files | file ages | folders
SHA3-256: 7257fcc8c990b46a4f6a9e506f4a4a40195e6b2c626efd380cfa01f0ce8eb0fb
User & Date: dan 2019-02-14 18:38:44
Wiki:reuse-schema
Context
2019-02-14
21:04
Fix SQLITE_DBSTATUS_SCHEMA_USED so that it works with SQLITE_OPEN_SHARED_SCHEMA connections. check-in: d43b3c05 user: dan tags: reuse-schema
18:38
Change the name of the SQLITE_OPEN_REUSE_SCHEMA flag to SQLITE_OPEN_SHARED_SCHEMA. check-in: 7257fcc8 user: dan tags: reuse-schema
17:59
Fix a typo in shared_schema.md. check-in: e47a5aea user: dan tags: reuse-schema
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/build.c.

278
279
280
281
282
283
284












285
286
287
288
289
290
291
292
...
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
...
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
** list of users and their access credentials.
*/
int sqlite3UserAuthTable(const char *zTable){
  return sqlite3_stricmp(zTable, "sqlite_user")==0;
}
#endif













static int loadReusableSchema(sqlite3 *db, int iDb){
  if( IsReuseSchema(db) 
   && DbHasProperty(db, iDb, DB_SchemaLoaded)==0 
   && (db->init.busy==0 || (iDb!=1 && db->init.iDb==1))
  ){
    char *zDummy = 0;
    struct sqlite3InitInfo sv = db->init;
    memset(&db->init, 0, sizeof(struct sqlite3InitInfo));
................................................................................
#endif
  while(1){
    for(i=OMIT_TEMPDB; i<db->nDb; i++){
      int j = (i<2) ? i^1 : i;   /* Search TEMP before MAIN */
      if( zDatabase==0 || sqlite3StrICmp(zDatabase, db->aDb[j].zDbSName)==0 ){
        int bUnload;
        assert( sqlite3SchemaMutexHeld(db, j, 0) );
        bUnload = loadReusableSchema(db, j);
        p = sqlite3HashFind(&db->aDb[j].pSchema->tblHash, zName);
        if( p ) return p;
        if( bUnload ){
          sqlite3SchemaRelease(db, j);
        }
      }
    }
................................................................................
    ** can be an eponymous virtual table. */
    if( pParse->disableVtab==0 ){
      Module *pMod = (Module*)sqlite3HashFind(&db->aModule, zName);
      if( pMod==0 && sqlite3_strnicmp(zName, "pragma_", 7)==0 ){
        pMod = sqlite3PragmaVtabRegister(db, zName);
      }
      if( pMod ){
        loadReusableSchema(db, 0);
        if( sqlite3VtabEponymousTableInit(pParse, pMod) ){
          return pMod->pEpoTab;
        }
      }
    }
#endif
    if( flags & LOCATE_NOERR ) return 0;







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







 







|







 







|







278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
...
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
...
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
** list of users and their access credentials.
*/
int sqlite3UserAuthTable(const char *zTable){
  return sqlite3_stricmp(zTable, "sqlite_user")==0;
}
#endif

/*
** If this database connection was opened with the SQLITE_OPEN_SHARED_SCHEMA
** flag specified, then ensure that the database schema for database iDb
** is loaded. Either by obtaining a Schema object from the schema-pool, or
** by reading the contents of the sqlite_master table.
**
** If the database handle was not opened with SQLITE_OPEN_SHARED_SCHEMA, or
** if the schema for database iDb is already loaded, this function is a no-op.
**
** Non-zero is returned if a schema is loaded, or zero if it was already 
** loaded when this function was called..
*/
static int loadSharableSchema(sqlite3 *db, int iDb){
  if( IsReuseSchema(db) 
   && DbHasProperty(db, iDb, DB_SchemaLoaded)==0 
   && (db->init.busy==0 || (iDb!=1 && db->init.iDb==1))
  ){
    char *zDummy = 0;
    struct sqlite3InitInfo sv = db->init;
    memset(&db->init, 0, sizeof(struct sqlite3InitInfo));
................................................................................
#endif
  while(1){
    for(i=OMIT_TEMPDB; i<db->nDb; i++){
      int j = (i<2) ? i^1 : i;   /* Search TEMP before MAIN */
      if( zDatabase==0 || sqlite3StrICmp(zDatabase, db->aDb[j].zDbSName)==0 ){
        int bUnload;
        assert( sqlite3SchemaMutexHeld(db, j, 0) );
        bUnload = loadSharableSchema(db, j);
        p = sqlite3HashFind(&db->aDb[j].pSchema->tblHash, zName);
        if( p ) return p;
        if( bUnload ){
          sqlite3SchemaRelease(db, j);
        }
      }
    }
................................................................................
    ** can be an eponymous virtual table. */
    if( pParse->disableVtab==0 ){
      Module *pMod = (Module*)sqlite3HashFind(&db->aModule, zName);
      if( pMod==0 && sqlite3_strnicmp(zName, "pragma_", 7)==0 ){
        pMod = sqlite3PragmaVtabRegister(db, zName);
      }
      if( pMod ){
        loadSharableSchema(db, 0);
        if( sqlite3VtabEponymousTableInit(pParse, pMod) ){
          return pMod->pEpoTab;
        }
      }
    }
#endif
    if( flags & LOCATE_NOERR ) return 0;

Changes to src/callback.c.

13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
...
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
...
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
...
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
...
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
...
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
** This file contains functions used to access the internal hash tables
** of user defined functions and collation sequences.
*/

#include "sqliteInt.h"

/*
** Connections opened with the SQLITE_OPEN_REUSE_SCHEMA flag specified
** may use SchemaPool objects for any database that is not the temp db
** (iDb==1). For such databases (type "struct Db") there are three states
** the Schema/SchemaPool object may be in.
**
**   1) pSPool==0, pSchema points to an empty object allocated by
**      sqlite3_malloc(). DB_SchemaLoaded flag is clear.
**
................................................................................
  if( pSchema->schemaFlags & DB_SchemaLoaded ){
    pSchema->iGeneration++;
  }
  pSchema->schemaFlags &= ~(DB_SchemaLoaded|DB_ResetWanted);
}

/*
** If this database was opened with the SQLITE_OPEN_REUSE_SCHEMA flag
** and iDb!=1, then disconnect from the schema-pool associated with
** database iDb. Otherwise, clear the Schema object belonging to
** database iDb. 
**
** If an OOM error occurs while disconnecting from a schema-pool, 
** the db->mallocFailed flag is set.
*/
................................................................................
*/
SchemaPool *sqlite3SchemaPoolList(void){ return schemaPoolList; }
#endif

/*
** Check that the schema of db iDb is writable (either because it is the 
** temp db schema or because the db handle was opened without
** SQLITE_OPEN_REUSE_SCHEMA). If so, do nothing. Otherwise, leave an 
** error in the Parse object.
*/
void sqlite3SchemaWritable(Parse *pParse, int iDb){
  if( iDb!=1 && (pParse->db->openFlags & SQLITE_OPEN_REUSE_SCHEMA) 
   && IN_DECLARE_VTAB==0
  ){
    sqlite3ErrorMsg(pParse, "attempt to modify read-only schema");
  }
}

/*
................................................................................
  pDb->pSchema = &pDb->pSPool->sSchema;

  assert( (pDb->pSchema->schemaFlags & DB_SchemaLoaded)==0 );
}

/*
** The schema for database iDb of database handle db, which was opened
** with SQLITE_OPEN_REUSE_SCHEMA, has just been parsed. This function either
** finds a matching SchemaPool object on the global list (schemaPoolList) or
** else allocates a new one and sets the Db.pSPool variable accordingly.
**
** SQLITE_OK is returned if no error occurs, or an SQLite error code 
** (SQLITE_NOMEM) otherwise.
*/
int sqlite3SchemaConnect(sqlite3 *db, int iDb, u64 cksum){
................................................................................

  db->aDb[iDb].pSPool = p;
  return (p ? SQLITE_OK : SQLITE_NOMEM);
}

/*
** If parameter iDb is 1 (the temp db), or if connection handle db was not
** opened with the SQLITE_OPEN_REUSE_SCHEMA flag, this function is a no-op.
** Otherwise, it disconnects from the schema-pool associated with database
** iDb, assuming it is connected.
**
** If parameter bNew is true, then Db.pSchema is set to point to a new, empty,
** Schema object obtained from sqlite3_malloc(). Or, if bNew is false, then
** Db.pSchema is set to NULL before returning.
**
................................................................................
  }
  sqlite3_mutex_leave( sqlite3_mutex_alloc(SQLITE_MUTEX_STATIC_MASTER) );
}

/*
** In most cases, this function finds and returns the schema associated 
** with BTree handle pBt, creating a new one if necessary. However, if
** the database handle was opened with the SQLITE_OPEN_REUSE_SCHEMA flag
** specified, a new, empty, Schema object in memory obtained by 
** sqlite3_malloc() is always returned.
*/
Schema *sqlite3SchemaGet(sqlite3 *db, Btree *pBt){
  Schema *p;
  if( pBt && (db->openFlags & SQLITE_OPEN_REUSE_SCHEMA)==0 ){
    p = (Schema *)sqlite3BtreeSchema(pBt, sizeof(Schema), sqlite3SchemaClear);
  }else{
    p = (Schema *)sqlite3DbMallocZero(0, sizeof(Schema));
  }
  if( !p ){
    sqlite3OomFault(db);
  }else if ( 0==p->file_format ){







|







 







|







 







|



|







 







|







 







|







 







|





|







13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
...
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
...
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
...
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
...
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
...
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
** This file contains functions used to access the internal hash tables
** of user defined functions and collation sequences.
*/

#include "sqliteInt.h"

/*
** Connections opened with the SQLITE_OPEN_SHARED_SCHEMA flag specified
** may use SchemaPool objects for any database that is not the temp db
** (iDb==1). For such databases (type "struct Db") there are three states
** the Schema/SchemaPool object may be in.
**
**   1) pSPool==0, pSchema points to an empty object allocated by
**      sqlite3_malloc(). DB_SchemaLoaded flag is clear.
**
................................................................................
  if( pSchema->schemaFlags & DB_SchemaLoaded ){
    pSchema->iGeneration++;
  }
  pSchema->schemaFlags &= ~(DB_SchemaLoaded|DB_ResetWanted);
}

/*
** If this database was opened with the SQLITE_OPEN_SHARED_SCHEMA flag
** and iDb!=1, then disconnect from the schema-pool associated with
** database iDb. Otherwise, clear the Schema object belonging to
** database iDb. 
**
** If an OOM error occurs while disconnecting from a schema-pool, 
** the db->mallocFailed flag is set.
*/
................................................................................
*/
SchemaPool *sqlite3SchemaPoolList(void){ return schemaPoolList; }
#endif

/*
** Check that the schema of db iDb is writable (either because it is the 
** temp db schema or because the db handle was opened without
** SQLITE_OPEN_SHARED_SCHEMA). If so, do nothing. Otherwise, leave an 
** error in the Parse object.
*/
void sqlite3SchemaWritable(Parse *pParse, int iDb){
  if( iDb!=1 && (pParse->db->openFlags & SQLITE_OPEN_SHARED_SCHEMA) 
   && IN_DECLARE_VTAB==0
  ){
    sqlite3ErrorMsg(pParse, "attempt to modify read-only schema");
  }
}

/*
................................................................................
  pDb->pSchema = &pDb->pSPool->sSchema;

  assert( (pDb->pSchema->schemaFlags & DB_SchemaLoaded)==0 );
}

/*
** The schema for database iDb of database handle db, which was opened
** with SQLITE_OPEN_SHARED_SCHEMA, has just been parsed. This function either
** finds a matching SchemaPool object on the global list (schemaPoolList) or
** else allocates a new one and sets the Db.pSPool variable accordingly.
**
** SQLITE_OK is returned if no error occurs, or an SQLite error code 
** (SQLITE_NOMEM) otherwise.
*/
int sqlite3SchemaConnect(sqlite3 *db, int iDb, u64 cksum){
................................................................................

  db->aDb[iDb].pSPool = p;
  return (p ? SQLITE_OK : SQLITE_NOMEM);
}

/*
** If parameter iDb is 1 (the temp db), or if connection handle db was not
** opened with the SQLITE_OPEN_SHARED_SCHEMA flag, this function is a no-op.
** Otherwise, it disconnects from the schema-pool associated with database
** iDb, assuming it is connected.
**
** If parameter bNew is true, then Db.pSchema is set to point to a new, empty,
** Schema object obtained from sqlite3_malloc(). Or, if bNew is false, then
** Db.pSchema is set to NULL before returning.
**
................................................................................
  }
  sqlite3_mutex_leave( sqlite3_mutex_alloc(SQLITE_MUTEX_STATIC_MASTER) );
}

/*
** In most cases, this function finds and returns the schema associated 
** with BTree handle pBt, creating a new one if necessary. However, if
** the database handle was opened with the SQLITE_OPEN_SHARED_SCHEMA flag
** specified, a new, empty, Schema object in memory obtained by 
** sqlite3_malloc() is always returned.
*/
Schema *sqlite3SchemaGet(sqlite3 *db, Btree *pBt){
  Schema *p;
  if( pBt && (db->openFlags & SQLITE_OPEN_SHARED_SCHEMA)==0 ){
    p = (Schema *)sqlite3BtreeSchema(pBt, sizeof(Schema), sqlite3SchemaClear);
  }else{
    p = (Schema *)sqlite3DbMallocZero(0, sizeof(Schema));
  }
  if( !p ){
    sqlite3OomFault(db);
  }else if ( 0==p->file_format ){

Changes to src/prepare.c.

157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
...
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
     || pIndex->tnum<2
     || sqlite3IndexHasDuplicateRootPage(pIndex)
    ){
      corruptSchema(pData, argv[0], pIndex?"invalid rootpage":"orphan index");
    }
  }

  if( iDb!=1 && (db->openFlags & SQLITE_OPEN_REUSE_SCHEMA) ){
    schemaUpdateChecksum(pData, argv[0], argv[1], argv[2]);
  }
  return 0;
}

/*
** Attempt to read the database schema and initialize internal
................................................................................
    ** purpose of this is to allow access to the sqlite_master table
    ** even when its contents have been corrupted.
    */
    DbSetProperty(db, iDb, DB_SchemaLoaded);
    rc = SQLITE_OK;
  }

  if( rc==SQLITE_OK && iDb!=1 && (db->openFlags & SQLITE_OPEN_REUSE_SCHEMA) ){
    rc = sqlite3SchemaConnect(db, iDb, initData.cksum);
  }

  /* Jump here for an error that occurs after successfully allocating
  ** curMain and calling sqlite3BtreeEnter(). For an error that occurs
  ** before that point, jump to error_out.
  */







|







 







|







157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
...
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
     || pIndex->tnum<2
     || sqlite3IndexHasDuplicateRootPage(pIndex)
    ){
      corruptSchema(pData, argv[0], pIndex?"invalid rootpage":"orphan index");
    }
  }

  if( iDb!=1 && (db->openFlags & SQLITE_OPEN_SHARED_SCHEMA) ){
    schemaUpdateChecksum(pData, argv[0], argv[1], argv[2]);
  }
  return 0;
}

/*
** Attempt to read the database schema and initialize internal
................................................................................
    ** purpose of this is to allow access to the sqlite_master table
    ** even when its contents have been corrupted.
    */
    DbSetProperty(db, iDb, DB_SchemaLoaded);
    rc = SQLITE_OK;
  }

  if( rc==SQLITE_OK && iDb!=1 && (db->openFlags & SQLITE_OPEN_SHARED_SCHEMA) ){
    rc = sqlite3SchemaConnect(db, iDb, initData.cksum);
  }

  /* Jump here for an error that occurs after successfully allocating
  ** curMain and calling sqlite3BtreeEnter(). For an error that occurs
  ** before that point, jump to error_out.
  */

Changes to src/shell.c.in.

3910
3911
3912
3913
3914
3915
3916
3917
3918
3919
3920
3921
3922
3923
3924
      }
      case SHELL_OPEN_READONLY: {
        sqlite3_open_v2(p->zDbFilename, &p->db, SQLITE_OPEN_READONLY, 0);
        break;
      }
      case SHELL_OPEN_REUSESCHEMA: {
        sqlite3_open_v2(p->zDbFilename, &p->db,
           SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE|SQLITE_OPEN_REUSE_SCHEMA, 0);
        break;
      }
      case SHELL_OPEN_UNSPEC:
      case SHELL_OPEN_NORMAL: {
        sqlite3_open(p->zDbFilename, &p->db);
        break;
      }







|







3910
3911
3912
3913
3914
3915
3916
3917
3918
3919
3920
3921
3922
3923
3924
      }
      case SHELL_OPEN_READONLY: {
        sqlite3_open_v2(p->zDbFilename, &p->db, SQLITE_OPEN_READONLY, 0);
        break;
      }
      case SHELL_OPEN_REUSESCHEMA: {
        sqlite3_open_v2(p->zDbFilename, &p->db,
          SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE|SQLITE_OPEN_SHARED_SCHEMA,0);
        break;
      }
      case SHELL_OPEN_UNSPEC:
      case SHELL_OPEN_NORMAL: {
        sqlite3_open(p->zDbFilename, &p->db);
        break;
      }

Changes to src/sqlite.h.in.

563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
#define SQLITE_OPEN_NOMUTEX          0x00008000  /* Ok for sqlite3_open_v2() */
#define SQLITE_OPEN_FULLMUTEX        0x00010000  /* Ok for sqlite3_open_v2() */
#define SQLITE_OPEN_SHAREDCACHE      0x00020000  /* Ok for sqlite3_open_v2() */
#define SQLITE_OPEN_PRIVATECACHE     0x00040000  /* Ok for sqlite3_open_v2() */
#define SQLITE_OPEN_WAL              0x00080000  /* VFS only */

/* Reserved:                         0x00F00000 */
#define SQLITE_OPEN_REUSE_SCHEMA     0x01000000  /* Ok for sqlite3_open_v2() */


/*
** CAPI3REF: Device Characteristics
**
** The xDeviceCharacteristics method of the [sqlite3_io_methods]
** object returns an integer which is a vector of these







|







563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
#define SQLITE_OPEN_NOMUTEX          0x00008000  /* Ok for sqlite3_open_v2() */
#define SQLITE_OPEN_FULLMUTEX        0x00010000  /* Ok for sqlite3_open_v2() */
#define SQLITE_OPEN_SHAREDCACHE      0x00020000  /* Ok for sqlite3_open_v2() */
#define SQLITE_OPEN_PRIVATECACHE     0x00040000  /* Ok for sqlite3_open_v2() */
#define SQLITE_OPEN_WAL              0x00080000  /* VFS only */

/* Reserved:                         0x00F00000 */
#define SQLITE_OPEN_SHARED_SCHEMA    0x01000000  /* Ok for sqlite3_open_v2() */


/*
** CAPI3REF: Device Characteristics
**
** The xDeviceCharacteristics method of the [sqlite3_io_methods]
** object returns an integer which is a vector of these

Changes to src/sqliteInt.h.

1501
1502
1503
1504
1505
1506
1507
1508
1509
1510
1511
1512
1513
1514
1515
  sqlite3 *pNextBlocked;        /* Next in list of all blocked connections */
#endif
#ifdef SQLITE_USER_AUTHENTICATION
  sqlite3_userauth auth;        /* User authentication information */
#endif
};

#define IsReuseSchema(db) (((db)->openFlags & SQLITE_OPEN_REUSE_SCHEMA)!=0)

/*
** A macro to discover the encoding of a database.
*/
#define SCHEMA_ENC(db) ((db)->aDb[0].pSchema->enc)
#define ENC(db)        ((db)->enc)








|







1501
1502
1503
1504
1505
1506
1507
1508
1509
1510
1511
1512
1513
1514
1515
  sqlite3 *pNextBlocked;        /* Next in list of all blocked connections */
#endif
#ifdef SQLITE_USER_AUTHENTICATION
  sqlite3_userauth auth;        /* User authentication information */
#endif
};

#define IsReuseSchema(db) (((db)->openFlags & SQLITE_OPEN_SHARED_SCHEMA)!=0)

/*
** A macro to discover the encoding of a database.
*/
#define SCHEMA_ENC(db) ((db)->aDb[0].pSchema->enc)
#define ENC(db)        ((db)->enc)

Changes to src/tclsqlite.c.

3466
3467
3468
3469
3470
3471
3472
3473
3474
3475
3476
3477
3478
3479
3480
....
3601
3602
3603
3604
3605
3606
3607
3608
3609
3610
3611
3612
3613
3614
3615
3616
3617
3618
3619
3620
3621
static int sqliteCmdUsage(
  Tcl_Interp *interp,
  Tcl_Obj *const*objv
){
  Tcl_WrongNumArgs(interp, 1, objv,
    "HANDLE ?FILENAME? ?-vfs VFSNAME? ?-readonly BOOLEAN? ?-create BOOLEAN?"
    " ?-nomutex BOOLEAN? ?-fullmutex BOOLEAN? ?-uri BOOLEAN?"
    " ?-reuse-schema BOOLEAN?"
#if defined(SQLITE_HAS_CODEC) && !defined(SQLITE_OMIT_CODEC_FROM_TCL)
    " ?-key CODECKEY?"
#endif
  );
  return TCL_ERROR;
}

................................................................................
      int b;
      if( Tcl_GetBooleanFromObj(interp, objv[i], &b) ) return TCL_ERROR;
      if( b ){
        flags |= SQLITE_OPEN_URI;
      }else{
        flags &= ~SQLITE_OPEN_URI;
      }
    }else if( strcmp(zArg, "-reuse-schema")==0 ){
      int b;
      if( Tcl_GetBooleanFromObj(interp, objv[i], &b) ) return TCL_ERROR;
      if( b ){
        flags |= SQLITE_OPEN_REUSE_SCHEMA;
      }else{
        flags &= ~SQLITE_OPEN_REUSE_SCHEMA;
      }
    }else{
      Tcl_AppendResult(interp, "unknown option: ", zArg, (char*)0);
      return TCL_ERROR;
    }
  }
  zErrMsg = 0;







|







 







|



|

|







3466
3467
3468
3469
3470
3471
3472
3473
3474
3475
3476
3477
3478
3479
3480
....
3601
3602
3603
3604
3605
3606
3607
3608
3609
3610
3611
3612
3613
3614
3615
3616
3617
3618
3619
3620
3621
static int sqliteCmdUsage(
  Tcl_Interp *interp,
  Tcl_Obj *const*objv
){
  Tcl_WrongNumArgs(interp, 1, objv,
    "HANDLE ?FILENAME? ?-vfs VFSNAME? ?-readonly BOOLEAN? ?-create BOOLEAN?"
    " ?-nomutex BOOLEAN? ?-fullmutex BOOLEAN? ?-uri BOOLEAN?"
    " ?-shared-schema BOOLEAN?"
#if defined(SQLITE_HAS_CODEC) && !defined(SQLITE_OMIT_CODEC_FROM_TCL)
    " ?-key CODECKEY?"
#endif
  );
  return TCL_ERROR;
}

................................................................................
      int b;
      if( Tcl_GetBooleanFromObj(interp, objv[i], &b) ) return TCL_ERROR;
      if( b ){
        flags |= SQLITE_OPEN_URI;
      }else{
        flags &= ~SQLITE_OPEN_URI;
      }
    }else if( strcmp(zArg, "-shared-schema")==0 ){
      int b;
      if( Tcl_GetBooleanFromObj(interp, objv[i], &b) ) return TCL_ERROR;
      if( b ){
        flags |= SQLITE_OPEN_SHARED_SCHEMA;
      }else{
        flags &= ~SQLITE_OPEN_SHARED_SCHEMA;
      }
    }else{
      Tcl_AppendResult(interp, "unknown option: ", zArg, (char*)0);
      return TCL_ERROR;
    }
  }
  zErrMsg = 0;

Changes to test/reuse1.test.

32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
...
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
...
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
...
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
...
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
...
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
...
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
...
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
  CREATE INDEX i1 ON t1(z);
  PRAGMA schema_version;
} {2}

do_test 1.2 {
  db close
  db2 close
  sqlite3 db2 test.db2 -reuse-schema 1
  sqlite3 db  test.db -reuse-schema 1
} {}

do_execsql_test -db db2 1.3.1 {
  INSERT INTO t1 VALUES(1, 2, 3);
  INSERT INTO t1 VALUES(4, 5, 6);
}

................................................................................
    INSERT INTO ft VALUES('one'), ('two'), ('three');
    ATTACH 'test.db2' AS aux;
    CREATE VIRTUAL TABLE aux.ft USING fts5(a);
    INSERT INTO aux.ft VALUES('aux1'), ('aux2'), ('aux3');
  }

  db close
  sqlite3 db  test.db -reuse-schema 1

  do_execsql_test 2.1 {
    ATTACH 'test.db2' AS aux;
    SELECT * FROM main.ft;
  } {one two three}

breakpoint
................................................................................
} {1 2 3}

do_execsql_test 3.3 {
  SELECT * FROM aux.v1;
} {4 5 6}

db close
sqlite3 db test.db -reuse-schema 1

do_execsql_test 3.4 { ATTACH 'test.db2' AS aux } {}
do_execsql_test 3.5 { SELECT * FROM main.v1 } {1 2 3}
do_execsql_test 3.6 { SELECT * FROM aux.v1  } {4 5 6}

do_execsql_test 3.7.1 { INSERT INTO aux.t1 VALUES(8, 9, 10); }
do_execsql_test 3.7.2 { SELECT * FROM main.v1 } {1 2 3}
................................................................................
  CREATE TRIGGER tr1 AFTER DELETE ON t1 BEGIN
    INSERT INTO del VALUES(old.a, old.b, old.c);
  END;
}
forcecopy test.db test.db2

db close
sqlite3 db test.db -reuse-schema 1
execsql { 
  ATTACH 'test.db2' AS aux;
  PRAGMA recursive_triggers = 1;
}

do_execsql_test 4.1 {
  INSERT INTO main.t1 VALUES(1, 2, 3);
................................................................................
  INSERT INTO t1 VALUES(1, 2, 3), (4, 5, 6);
  ANALYZE;
  PRAGMA writable_schema = 1;
  DELETE FROM sqlite_stat1;
}
db close
forcecopy test.db test.db2
sqlite3 db test.db -reuse-schema 1
execsql { ATTACH 'test.db2' AS aux }

foreach {tn sql} {
  1 { CREATE TABLE t3(x) }
  2 { DROP TABLE t2 }
  3 { CREATE INDEX i2 ON t2(b) }
  4 { DROP INDEX i1 }
................................................................................
do_execsql_test 5.4 {
  CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
  CREATE TABLE t2(a INTEGER PRIMARY KEY, b, c);
  CREATE INDEX i1 ON t1(b);
  INSERT INTO t1 VALUES(1, 2, 3), (4, 5, 6);
}
db close
sqlite3 db test.db -reuse-schema 1
foreach {tn sql} {
  1 { ANALYZE }
  2 { ANALYZE t1 }
  3 { ANALYZE i1 }
  4 { ANALYZE main }
  5 { ANALYZE main.t1 }
  6 { ANALYZE main.i1 }
................................................................................
reset_db
do_execsql_test 6.0 {
  CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
  CREATE VIEW v1 AS SELECT * FROM t1;
}
db close
forcecopy test.db test.db2
sqlite3 db test.db -reuse-schema 1
execsql { ATTACH 'test.db2' AS aux }

do_execsql_test 6.1 {
  INSERT INTO main.t1(a) VALUES(1), (2), (3);
  INSERT INTO aux.t1(a) VALUES(4), (5), (6);
  CREATE TEMP TABLE t2(i,t);
  INSERT INTO t2 VALUES(2, 'two'), (5, 'five');
................................................................................
  CREATE TABLE p1(a PRIMARY KEY, b);
  CREATE TABLE p2(a PRIMARY KEY, b);
  CREATE TABLE c1(x REFERENCES p1 ON UPDATE CASCADE ON DELETE CASCADE);
}

db close
forcecopy test.db test.db2
sqlite3 db test.db -reuse-schema 1
execsql { ATTACH 'test.db2' AS aux }

do_execsql_test 7.1 {
  INSERT INTO aux.p1 VALUES(1, 'one');
  INSERT INTO aux.p1 VALUES(2, 'two');
  PRAGMA foreign_keys = on;
}







|
|







 







|







 







|







 







|







 







|







 







|







 







|







 







|







32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
...
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
...
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
...
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
...
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
...
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
...
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
...
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
  CREATE INDEX i1 ON t1(z);
  PRAGMA schema_version;
} {2}

do_test 1.2 {
  db close
  db2 close
  sqlite3 db2 test.db2 -shared-schema 1
  sqlite3 db  test.db -shared-schema 1
} {}

do_execsql_test -db db2 1.3.1 {
  INSERT INTO t1 VALUES(1, 2, 3);
  INSERT INTO t1 VALUES(4, 5, 6);
}

................................................................................
    INSERT INTO ft VALUES('one'), ('two'), ('three');
    ATTACH 'test.db2' AS aux;
    CREATE VIRTUAL TABLE aux.ft USING fts5(a);
    INSERT INTO aux.ft VALUES('aux1'), ('aux2'), ('aux3');
  }

  db close
  sqlite3 db  test.db -shared-schema 1

  do_execsql_test 2.1 {
    ATTACH 'test.db2' AS aux;
    SELECT * FROM main.ft;
  } {one two three}

breakpoint
................................................................................
} {1 2 3}

do_execsql_test 3.3 {
  SELECT * FROM aux.v1;
} {4 5 6}

db close
sqlite3 db test.db -shared-schema 1

do_execsql_test 3.4 { ATTACH 'test.db2' AS aux } {}
do_execsql_test 3.5 { SELECT * FROM main.v1 } {1 2 3}
do_execsql_test 3.6 { SELECT * FROM aux.v1  } {4 5 6}

do_execsql_test 3.7.1 { INSERT INTO aux.t1 VALUES(8, 9, 10); }
do_execsql_test 3.7.2 { SELECT * FROM main.v1 } {1 2 3}
................................................................................
  CREATE TRIGGER tr1 AFTER DELETE ON t1 BEGIN
    INSERT INTO del VALUES(old.a, old.b, old.c);
  END;
}
forcecopy test.db test.db2

db close
sqlite3 db test.db -shared-schema 1
execsql { 
  ATTACH 'test.db2' AS aux;
  PRAGMA recursive_triggers = 1;
}

do_execsql_test 4.1 {
  INSERT INTO main.t1 VALUES(1, 2, 3);
................................................................................
  INSERT INTO t1 VALUES(1, 2, 3), (4, 5, 6);
  ANALYZE;
  PRAGMA writable_schema = 1;
  DELETE FROM sqlite_stat1;
}
db close
forcecopy test.db test.db2
sqlite3 db test.db -shared-schema 1
execsql { ATTACH 'test.db2' AS aux }

foreach {tn sql} {
  1 { CREATE TABLE t3(x) }
  2 { DROP TABLE t2 }
  3 { CREATE INDEX i2 ON t2(b) }
  4 { DROP INDEX i1 }
................................................................................
do_execsql_test 5.4 {
  CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
  CREATE TABLE t2(a INTEGER PRIMARY KEY, b, c);
  CREATE INDEX i1 ON t1(b);
  INSERT INTO t1 VALUES(1, 2, 3), (4, 5, 6);
}
db close
sqlite3 db test.db -shared-schema 1
foreach {tn sql} {
  1 { ANALYZE }
  2 { ANALYZE t1 }
  3 { ANALYZE i1 }
  4 { ANALYZE main }
  5 { ANALYZE main.t1 }
  6 { ANALYZE main.i1 }
................................................................................
reset_db
do_execsql_test 6.0 {
  CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
  CREATE VIEW v1 AS SELECT * FROM t1;
}
db close
forcecopy test.db test.db2
sqlite3 db test.db -shared-schema 1
execsql { ATTACH 'test.db2' AS aux }

do_execsql_test 6.1 {
  INSERT INTO main.t1(a) VALUES(1), (2), (3);
  INSERT INTO aux.t1(a) VALUES(4), (5), (6);
  CREATE TEMP TABLE t2(i,t);
  INSERT INTO t2 VALUES(2, 'two'), (5, 'five');
................................................................................
  CREATE TABLE p1(a PRIMARY KEY, b);
  CREATE TABLE p2(a PRIMARY KEY, b);
  CREATE TABLE c1(x REFERENCES p1 ON UPDATE CASCADE ON DELETE CASCADE);
}

db close
forcecopy test.db test.db2
sqlite3 db test.db -shared-schema 1
execsql { ATTACH 'test.db2' AS aux }

do_execsql_test 7.1 {
  INSERT INTO aux.p1 VALUES(1, 'one');
  INSERT INTO aux.p1 VALUES(2, 'two');
  PRAGMA foreign_keys = on;
}

Changes to test/reuse2.test.

21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
..
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
..
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
...
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
...
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
  CREATE INDEX i1 ON t1(z);
  PRAGMA schema_version;
} {2}

do_test 1.2 {
  catch { db close }
  catch { db2 close }
  sqlite3 db2 test.db -reuse-schema 1
  sqlite3 db  test.db -reuse-schema 1
} {}

do_execsql_test -db db2 1.3.1 {
  INSERT INTO t1 VALUES(1, 2, 3);
}

do_execsql_test -db db2 1.3.2 {
................................................................................
reset_db
ifcapable fts5 {
  do_execsql_test 2.0 {
    CREATE VIRTUAL TABLE ft USING fts5(c);
    INSERT INTO ft VALUES('one two three');
  }
  db close
  sqlite3 db test.db -reuse-schema 1

  do_execsql_test 2.1 {
    SELECT * FROM ft
  } {{one two three}}
}

#--------------------------------------------------------------------------
................................................................................
do_execsql_test 3.0 {
  CREATE TABLE t1(x INTEGER PRIMARY KEY, y UNIQUE, z);
  CREATE INDEX i1 ON t1(z);
  PRAGMA schema_version;
} {2}

do_test 3.1 {
  sqlite3 db1 test.db -reuse-schema 1
  sqlite3 db2 test.db -reuse-schema 1
} {}

do_execsql_test -db db1 3.2.1 { SELECT * FROM t1 }
do_execsql_test -db db2 3.2.2 { SELECT * FROM t1 }

register_schemapool_module db
do_execsql_test 3.3 { 
  SELECT 'nref=' || nRef, 'nschema=' || nSchema FROM schemapool;
} {nref=2 nschema=1}

sqlite3 db3 test.db -reuse-schema 1
register_schemapool_module db3

do_execsql_test 3.5 { 
  SELECT 'nref=' || nRef, 'nschema=' || nSchema FROM schemapool;
} {nref=2 nschema=1}

do_execsql_test -db db3 3.6 { 
................................................................................
do_test 4.0.2 {
  db close
  for {set i 1} {$i < 6} {incr i} {
    forcedelete test.db${i}-journal test.db${i}-wal test.db${i}-wal2 
    forcecopy test.db test.db${i}
  }
  sqlite3 db  test.db
  sqlite3 db2 test.db -reuse-schema 1
} {}

register_schemapool_module db
do_execsql_test 4.0.3 {
  SELECT 'nref=' || nRef, 'nschema=' || nSchema FROM schemapool ORDER BY 1;
} {}

................................................................................
catch {db3 close}
reset_db
do_execsql_test 5.0.1 {
  CREATE TABLE bbb(a INTEGER PRIMARY KEY, b);
}
db close
do_test 5.0.2 {
  sqlite3 db2 test.db -reuse-schema 1
  register_schemapool_module db2
  for {set i 1} {$i<6} {incr i} {
    forcedelete test.db${i}-journal test.db${i}-wal test.db${i}-wal2 
    forcecopy test.db test.db${i}
    sqlite3 db test.db${i}
    db eval { INSERT INTO bbb VALUES(123, 'database_' || $i) }
    db close







|
|







 







|







 







|
|










|







 







|







 







|







21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
..
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
..
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
...
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
...
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
  CREATE INDEX i1 ON t1(z);
  PRAGMA schema_version;
} {2}

do_test 1.2 {
  catch { db close }
  catch { db2 close }
  sqlite3 db2 test.db -shared-schema 1
  sqlite3 db  test.db -shared-schema 1
} {}

do_execsql_test -db db2 1.3.1 {
  INSERT INTO t1 VALUES(1, 2, 3);
}

do_execsql_test -db db2 1.3.2 {
................................................................................
reset_db
ifcapable fts5 {
  do_execsql_test 2.0 {
    CREATE VIRTUAL TABLE ft USING fts5(c);
    INSERT INTO ft VALUES('one two three');
  }
  db close
  sqlite3 db test.db -shared-schema 1

  do_execsql_test 2.1 {
    SELECT * FROM ft
  } {{one two three}}
}

#--------------------------------------------------------------------------
................................................................................
do_execsql_test 3.0 {
  CREATE TABLE t1(x INTEGER PRIMARY KEY, y UNIQUE, z);
  CREATE INDEX i1 ON t1(z);
  PRAGMA schema_version;
} {2}

do_test 3.1 {
  sqlite3 db1 test.db -shared-schema 1
  sqlite3 db2 test.db -shared-schema 1
} {}

do_execsql_test -db db1 3.2.1 { SELECT * FROM t1 }
do_execsql_test -db db2 3.2.2 { SELECT * FROM t1 }

register_schemapool_module db
do_execsql_test 3.3 { 
  SELECT 'nref=' || nRef, 'nschema=' || nSchema FROM schemapool;
} {nref=2 nschema=1}

sqlite3 db3 test.db -shared-schema 1
register_schemapool_module db3

do_execsql_test 3.5 { 
  SELECT 'nref=' || nRef, 'nschema=' || nSchema FROM schemapool;
} {nref=2 nschema=1}

do_execsql_test -db db3 3.6 { 
................................................................................
do_test 4.0.2 {
  db close
  for {set i 1} {$i < 6} {incr i} {
    forcedelete test.db${i}-journal test.db${i}-wal test.db${i}-wal2 
    forcecopy test.db test.db${i}
  }
  sqlite3 db  test.db
  sqlite3 db2 test.db -shared-schema 1
} {}

register_schemapool_module db
do_execsql_test 4.0.3 {
  SELECT 'nref=' || nRef, 'nschema=' || nSchema FROM schemapool ORDER BY 1;
} {}

................................................................................
catch {db3 close}
reset_db
do_execsql_test 5.0.1 {
  CREATE TABLE bbb(a INTEGER PRIMARY KEY, b);
}
db close
do_test 5.0.2 {
  sqlite3 db2 test.db -shared-schema 1
  register_schemapool_module db2
  for {set i 1} {$i<6} {incr i} {
    forcedelete test.db${i}-journal test.db${i}-wal test.db${i}-wal2 
    forcecopy test.db test.db${i}
    sqlite3 db test.db${i}
    db eval { INSERT INTO bbb VALUES(123, 'database_' || $i) }
    db close

Changes to test/reuse3.test.

19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
..
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
do_execsql_test 1.0 {
  CREATE TABLE t1(x INTEGER PRIMARY KEY, y UNIQUE, z);
  CREATE INDEX i1 ON t1(z);
  CREATE TABLE t2(a);
} {}

db close
sqlite3 db test.db -reuse-schema 1

do_execsql_test 1.1 {
  CREATE TEMP VIEW v1 AS SELECT * FROM t1;
  SELECT * FROM v1;
}

do_execsql_test 1.2 {
................................................................................
  CREATE TABLE x1(a, b, c);
  CREATE TABLE y1(d, e, f);
  PRAGMA writable_schema = 1;
  UPDATE sqlite_master SET sql = 'CREATE TBL y1(d, e, f)' WHERE name = 'y1';
}
db close

sqlite3 db test.db -reuse-schema 1
do_catchsql_test 2.1 {
  SELECT * FROM x1;
} {1 {no such table: x1}}

do_catchsql_test 2.2 {
  SELECT * FROM x1;
} {1 {no such table: x1}}

finish_test








|







 







|










19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
..
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
do_execsql_test 1.0 {
  CREATE TABLE t1(x INTEGER PRIMARY KEY, y UNIQUE, z);
  CREATE INDEX i1 ON t1(z);
  CREATE TABLE t2(a);
} {}

db close
sqlite3 db test.db -shared-schema 1

do_execsql_test 1.1 {
  CREATE TEMP VIEW v1 AS SELECT * FROM t1;
  SELECT * FROM v1;
}

do_execsql_test 1.2 {
................................................................................
  CREATE TABLE x1(a, b, c);
  CREATE TABLE y1(d, e, f);
  PRAGMA writable_schema = 1;
  UPDATE sqlite_master SET sql = 'CREATE TBL y1(d, e, f)' WHERE name = 'y1';
}
db close

sqlite3 db test.db -shared-schema 1
do_catchsql_test 2.1 {
  SELECT * FROM x1;
} {1 {no such table: x1}}

do_catchsql_test 2.2 {
  SELECT * FROM x1;
} {1 {no such table: x1}}

finish_test