/ Check-in [d43b3c05]
Login

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

Overview
Comment:Fix SQLITE_DBSTATUS_SCHEMA_USED so that it works with SQLITE_OPEN_SHARED_SCHEMA connections.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | reuse-schema
Files: files | file ages | folders
SHA3-256:d43b3c056cb13930865c504c9498b2c83e4bebce9bff01ee21293e7dc7a6711e
User & Date: dan 2019-02-14 21:04:27
Wiki:reuse-schema
Context
2019-02-15
11:54
Revert the rearrangement of VDBE code in [219b39e14] so that vdbe.c matches trunk. Since the new call to sqlite3Init() in OP_ParseSchema was removed, the rearrangement no longer provides any performance advantage. check-in: 03c4f003 user: dan tags: reuse-schema
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
Changes
Hide Diffs Unified Diffs Show Whitespace Changes Patch

Changes to doc/shared_schema.md.

29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
If the schema of a database attached to an
SQLITE_OPEN_SHARED_SCHEMA database handle is corrupt, or if
corruption is encountered while parsing the database schema, then the
database is treated as empty. This usually means that corruption results in
a "no such table: xxx" error instead of a more specific error message.

For SQLITE_OPEN_SHARED_SCHEMA connections, the
SQLITE_DBSTATUS_SCHEMA_USED sqlite3_db_used() distributes
the memory used for a shared schema object evenly between all database
connections that share it.

## Implementation Notes

A single Schema object is never used by more than one database simultaneously,
regardless of whether or not those databases are attached to the same or
different database handles. Instead, a pool of schema objects is maintained 
for each unique sqlite_master-contents/schema-cookie combination







|
|
|







29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
If the schema of a database attached to an
SQLITE_OPEN_SHARED_SCHEMA database handle is corrupt, or if
corruption is encountered while parsing the database schema, then the
database is treated as empty. This usually means that corruption results in
a "no such table: xxx" error instead of a more specific error message.

For SQLITE_OPEN_SHARED_SCHEMA connections, the
SQLITE_DBSTATUS_SCHEMA_USED sqlite3_db_status() verb
distributes the memory used for a shared schema object evenly between all
database connections that share it.

## Implementation Notes

A single Schema object is never used by more than one database simultaneously,
regardless of whether or not those databases are attached to the same or
different database handles. Instead, a pool of schema objects is maintained 
for each unique sqlite_master-contents/schema-cookie combination

Changes to src/build.c.

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
**
** 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;







|







 







|







 







|







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
**
** 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..
*/
int sqlite3SchemaLoad(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 = sqlite3SchemaLoad(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 ){
        sqlite3SchemaLoad(db, 0);
        if( sqlite3VtabEponymousTableInit(pParse, pMod) ){
          return pMod->pEpoTab;
        }
      }
    }
#endif
    if( flags & LOCATE_NOERR ) return 0;

Changes to src/callback.c.

544
545
546
547
548
549
550






















551
552
553
554
555
556
557
#ifdef SQLITE_TEST
/*
** Return a pointer to the head of the linked list of SchemaPool objects.
** This is used by the virtual table in file test_schemapool.c.
*/
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.
*/







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







544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
#ifdef SQLITE_TEST
/*
** Return a pointer to the head of the linked list of SchemaPool objects.
** This is used by the virtual table in file test_schemapool.c.
*/
SchemaPool *sqlite3SchemaPoolList(void){ return schemaPoolList; }
#endif

/*
** Database handle db was opened with the SHARED_SCHEMA flag, and database
** iDb is currently connected to a schema-pool. When this function is called,
** (*pnByte) is set to nInit plus the amount of memory used to store a 
** single instance of the Schema objects managed by the schema-pool.
** This function adjusts (*pnByte) sot hat it is set to nInit plus
** (nSchema/nRef) of the amount of memory used by a single Schema object,
** where nSchema is the number of Schema objects allocated by this pool,
** and nRef is the number of connections to the schema-pool.
*/
void sqlite3SchemaAdjustUsed(sqlite3 *db, int iDb, int nInit, int *pnByte){
  SchemaPool *pSPool = db->aDb[iDb].pSPool;
  int nSchema = 0;
  Schema *p;
  sqlite3_mutex_enter( sqlite3_mutex_alloc(SQLITE_MUTEX_STATIC_MASTER) );
  for(p=pSPool->pSchema; p; p=p->pNext){
    nSchema++;
  }
  *pnByte = nInit + ((*pnByte - nInit) * nSchema) / pSPool->nRef;
  sqlite3_mutex_leave( sqlite3_mutex_alloc(SQLITE_MUTEX_STATIC_MASTER) );
}

/*
** 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.
*/

Changes to src/sqliteInt.h.

4321
4322
4323
4324
4325
4326
4327

4328
4329

4330
4331
4332
4333
4334
4335
4336
void sqlite3RegisterLikeFunctions(sqlite3*, int);
int sqlite3IsLikeFunction(sqlite3*,Expr*,int*,char*);
void sqlite3SchemaClear(void *);
int sqlite3SchemaConnect(sqlite3*, int, u64);
int sqlite3SchemaDisconnect(sqlite3 *, int, int);
void sqlite3SchemaClearOrDisconnect(sqlite3*, int);
Schema *sqlite3SchemaExtract(SchemaPool*);

void sqlite3SchemaReleaseAll(sqlite3*);
void sqlite3SchemaRelease(sqlite3*, int);

void sqlite3SchemaWritable(Parse*, int);
Schema *sqlite3SchemaGet(sqlite3 *, Btree *);
int sqlite3SchemaToIndex(sqlite3 *db, Schema *);
KeyInfo *sqlite3KeyInfoAlloc(sqlite3*,int,int);
void sqlite3KeyInfoUnref(KeyInfo*);
KeyInfo *sqlite3KeyInfoRef(KeyInfo*);
KeyInfo *sqlite3KeyInfoOfIndex(Parse*, Index*);







>


>







4321
4322
4323
4324
4325
4326
4327
4328
4329
4330
4331
4332
4333
4334
4335
4336
4337
4338
void sqlite3RegisterLikeFunctions(sqlite3*, int);
int sqlite3IsLikeFunction(sqlite3*,Expr*,int*,char*);
void sqlite3SchemaClear(void *);
int sqlite3SchemaConnect(sqlite3*, int, u64);
int sqlite3SchemaDisconnect(sqlite3 *, int, int);
void sqlite3SchemaClearOrDisconnect(sqlite3*, int);
Schema *sqlite3SchemaExtract(SchemaPool*);
int sqlite3SchemaLoad(sqlite3*, int);
void sqlite3SchemaReleaseAll(sqlite3*);
void sqlite3SchemaRelease(sqlite3*, int);
void sqlite3SchemaAdjustUsed(sqlite3*, int, int, int*);
void sqlite3SchemaWritable(Parse*, int);
Schema *sqlite3SchemaGet(sqlite3 *, Btree *);
int sqlite3SchemaToIndex(sqlite3 *db, Schema *);
KeyInfo *sqlite3KeyInfoAlloc(sqlite3*,int,int);
void sqlite3KeyInfoUnref(KeyInfo*);
KeyInfo *sqlite3KeyInfoRef(KeyInfo*);
KeyInfo *sqlite3KeyInfoOfIndex(Parse*, Index*);

Changes to src/status.c.

271
272
273
274
275
276
277

278
279

280
281






282
283
284
285
286
287
288
289
...
297
298
299
300
301
302
303



304


305
306
307
308
309
310
311
    ** *pCurrent gets an accurate estimate of the amount of memory used
    ** to store the schema for all databases (main, temp, and any ATTACHed
    ** databases.  *pHighwater is set to zero.
    */
    case SQLITE_DBSTATUS_SCHEMA_USED: {
      int i;                      /* Used to iterate through schemas */
      int nByte = 0;              /* Used to accumulate return value */


      sqlite3BtreeEnterAll(db);

      db->pnBytesFreed = &nByte;
      for(i=0; i<db->nDb; i++){






        Schema *pSchema = db->aDb[i].pSchema;
        if( ALWAYS(pSchema!=0) ){
          HashElem *p;

          nByte += sqlite3GlobalConfig.m.xRoundup(sizeof(HashElem)) * (
              pSchema->tblHash.count 
            + pSchema->trigHash.count
            + pSchema->idxHash.count
................................................................................
          for(p=sqliteHashFirst(&pSchema->trigHash); p; p=sqliteHashNext(p)){
            sqlite3DeleteTrigger(db, (Trigger*)sqliteHashData(p));
          }
          for(p=sqliteHashFirst(&pSchema->tblHash); p; p=sqliteHashNext(p)){
            sqlite3DeleteTable(db, (Table *)sqliteHashData(p));
          }
        }



      }


      db->pnBytesFreed = 0;
      sqlite3BtreeLeaveAll(db);

      *pHighwater = 0;
      *pCurrent = nByte;
      break;
    }







>


>


>
>
>
>
>
>
|







 







>
>
>
|
>
>







271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
...
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
    ** *pCurrent gets an accurate estimate of the amount of memory used
    ** to store the schema for all databases (main, temp, and any ATTACHed
    ** databases.  *pHighwater is set to zero.
    */
    case SQLITE_DBSTATUS_SCHEMA_USED: {
      int i;                      /* Used to iterate through schemas */
      int nByte = 0;              /* Used to accumulate return value */
      int bReleaseSchema;

      sqlite3BtreeEnterAll(db);
      bReleaseSchema = sqlite3LockReusableSchema(db);
      db->pnBytesFreed = &nByte;
      for(i=0; i<db->nDb; i++){
        int bUnload = 0;
        int nUsed = nByte;
        Schema *pSchema;
        if( db->aDb[i].pSPool ){
          bUnload = sqlite3SchemaLoad(db, i);
        }
        pSchema = db->aDb[i].pSchema;
        if( ALWAYS(pSchema!=0) ){
          HashElem *p;

          nByte += sqlite3GlobalConfig.m.xRoundup(sizeof(HashElem)) * (
              pSchema->tblHash.count 
            + pSchema->trigHash.count
            + pSchema->idxHash.count
................................................................................
          for(p=sqliteHashFirst(&pSchema->trigHash); p; p=sqliteHashNext(p)){
            sqlite3DeleteTrigger(db, (Trigger*)sqliteHashData(p));
          }
          for(p=sqliteHashFirst(&pSchema->tblHash); p; p=sqliteHashNext(p)){
            sqlite3DeleteTable(db, (Table *)sqliteHashData(p));
          }
        }
        if( db->aDb[i].pSPool ){
          if( bUnload ) sqlite3SchemaRelease(db, i);
          sqlite3SchemaAdjustUsed(db, i, nUsed, &nByte);
        }
      }
      sqlite3UnlockReusableSchema(db, bReleaseSchema);
      db->pnBytesFreed = 0;
      sqlite3BtreeLeaveAll(db);

      *pHighwater = 0;
      *pCurrent = nByte;
      break;
    }

Changes to test/reuse3.test.

83
84
85
86
87
88
89
90




































91
92
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









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


83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
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}}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 3.0 {
  CREATE TABLE x1(a, b, c);
  CREATE INDEX i1 ON x1(a, b, c);
  CREATE TRIGGER tr1 AFTER INSERT ON x1 BEGIN
    SELECT 1, 2, 3, 4, 5;
  END;
  INSERT INTO x1 VALUES(1, 2, 3);
}
sqlite3 db1 test.db -shared-schema 1

do_test 3.1 {
  execsql { SELECT * FROM x1 } db1
  set N [lindex [sqlite3_db_status db1 SCHEMA_USED 0] 1]
  expr $N==$N
} 1

sqlite3 db2 test.db -shared-schema 1
do_test 3.2 {
  execsql { SELECT * FROM x1 } db2
  breakpoint
  set N2 [lindex [sqlite3_db_status db2 SCHEMA_USED 0] 1]
  expr $N2>($N/2) && $N2<($N/2)+400
} 1

sqlite3 db3 test.db -shared-schema 1
sqlite3 db4 test.db -shared-schema 1
do_test 3.3 {
  execsql { SELECT * FROM x1 } db3
  execsql { SELECT * FROM x1 } db4
  set N4 [lindex [sqlite3_db_status db2 SCHEMA_USED 0] 1]
  set M [expr 2*($N-$N2)]
  expr {$N4 == (($M / 4) + $N-$M)}
} 1

finish_test

Changes to test/tclsqlite.test.

20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
catch {sqlite3}

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

# Check the error messages generated by tclsqlite
#
set r "sqlite_orig HANDLE ?FILENAME? ?-vfs VFSNAME? ?-readonly BOOLEAN? ?-create BOOLEAN? ?-nomutex BOOLEAN? ?-fullmutex BOOLEAN? ?-uri BOOLEAN? ?-reuse-schema BOOLEAN?"
if {[sqlite3 -has-codec]} {
  append r " ?-key CODECKEY?"
}
do_test tcl-1.1 {
  set v [catch {sqlite3 -bogus} msg]
  regsub {really_sqlite3} $msg {sqlite3} msg
  lappend v $msg







|







20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
catch {sqlite3}

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

# Check the error messages generated by tclsqlite
#
set r "sqlite_orig HANDLE ?FILENAME? ?-vfs VFSNAME? ?-readonly BOOLEAN? ?-create BOOLEAN? ?-nomutex BOOLEAN? ?-fullmutex BOOLEAN? ?-uri BOOLEAN? ?-shared-schema BOOLEAN?"
if {[sqlite3 -has-codec]} {
  append r " ?-key CODECKEY?"
}
do_test tcl-1.1 {
  set v [catch {sqlite3 -bogus} msg]
  regsub {really_sqlite3} $msg {sqlite3} msg
  lappend v $msg