/ Check-in [46f4eb54]
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:When a connection disconnects from a shared-cache database, only delete the in-memory schema if there are no other connections.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | shared-schema
Files: files | file ages | folders
SHA1: 46f4eb5430d7bc9a339cdf7124ff4bd518eaa39b
User & Date: dan 2012-05-15 17:15:34
Context
2012-05-15
18:28
The former sqlite3ResetInternalSchema() routine was really two different routines, selected by parameter, each with a confused mission. So split this routine up into three separate smaller routines, calling each separately as needed. Hopefully this will make further refactoring and schema reset collateral damage containment easier. check-in: aa0c3493 user: drh tags: shared-schema
17:15
When a connection disconnects from a shared-cache database, only delete the in-memory schema if there are no other connections. check-in: 46f4eb54 user: dan tags: shared-schema
12:49
Add assert()s to verify that Table objects in the schema never use lookaside memory. check-in: 736d6ea6 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/main.c.

   715    715       pDestructor->nRef--;
   716    716       if( pDestructor->nRef==0 ){
   717    717         pDestructor->xDestroy(pDestructor->pUserData);
   718    718         sqlite3DbFree(db, pDestructor);
   719    719       }
   720    720     }
   721    721   }
          722  +
          723  +/*
          724  +** Disconnect all sqlite3_vtab objects that belong to database connection
          725  +** db. This is called when db is being closed.
          726  +*/
          727  +static void disconnectAllVtab(sqlite3 *db){
          728  +#ifndef SQLITE_OMIT_VIRTUALTABLE
          729  +  int i;
          730  +  sqlite3BtreeEnterAll(db);
          731  +  for(i=0; i<db->nDb; i++){
          732  +    Schema *pSchema = db->aDb[i].pSchema;
          733  +    if( db->aDb[i].pSchema ){
          734  +      HashElem *p;
          735  +      for(p=sqliteHashFirst(&pSchema->tblHash); p; p=sqliteHashNext(p)){
          736  +        Table *pTab = (Table *)sqliteHashData(p);
          737  +        if( IsVirtual(pTab) ) sqlite3VtabDisconnect(db, pTab);
          738  +      }
          739  +    }
          740  +  }
          741  +  sqlite3BtreeLeaveAll(db);
          742  +#else
          743  +  UNUSED_PARAMETER(db);
          744  +#endif
          745  +}
   722    746   
   723    747   /*
   724    748   ** Close an existing SQLite database
   725    749   */
   726    750   int sqlite3_close(sqlite3 *db){
   727    751     HashElem *i;                    /* Hash table iterator */
   728    752     int j;
................................................................................
   731    755       return SQLITE_OK;
   732    756     }
   733    757     if( !sqlite3SafetyCheckSickOrOk(db) ){
   734    758       return SQLITE_MISUSE_BKPT;
   735    759     }
   736    760     sqlite3_mutex_enter(db->mutex);
   737    761   
   738         -  /* Force xDestroy calls on all virtual tables */
   739         -  sqlite3ResetInternalSchema(db, -1);
          762  +  /* Force xDisconnect calls on all virtual tables */
          763  +  disconnectAllVtab(db);
   740    764   
   741         -  /* If a transaction is open, the ResetInternalSchema() call above
          765  +  /* If a transaction is open, the disconnectAllVtab() call above
   742    766     ** will not have called the xDisconnect() method on any virtual
   743    767     ** tables in the db->aVTrans[] array. The following sqlite3VtabRollback()
   744    768     ** call will do so. We need to do this before the check for active
   745    769     ** SQL statements below, as the v-table implementation may be storing
   746    770     ** some prepared statements internally.
   747    771     */
   748    772     sqlite3VtabRollback(db);
................................................................................
   775    799         sqlite3BtreeClose(pDb->pBt);
   776    800         pDb->pBt = 0;
   777    801         if( j!=1 ){
   778    802           pDb->pSchema = 0;
   779    803         }
   780    804       }
   781    805     }
          806  +
          807  +  /* This call frees the schema associated with the temp database only (if
          808  +  ** any). It also frees the db->aDb array, if required.  */
   782    809     sqlite3ResetInternalSchema(db, -1);
          810  +  assert( db->nDb<=2 );
          811  +  assert( db->aDb==db->aDbStatic );
   783    812   
   784    813     /* Tell the code in notify.c that the connection no longer holds any
   785    814     ** locks and does not require any further unlock-notify callbacks.
   786    815     */
   787    816     sqlite3ConnectionClosed(db);
   788    817   
   789         -  assert( db->nDb<=2 );
   790         -  assert( db->aDb==db->aDbStatic );
   791    818     for(j=0; j<ArraySize(db->aFunc.a); j++){
   792    819       FuncDef *pNext, *pHash, *p;
   793    820       for(p=db->aFunc.a[j]; p; p=pHash){
   794    821         pHash = p->pHash;
   795    822         while( p ){
   796    823           functionDestroy(db, p);
   797    824           pNext = p->pNext;

Changes to src/sqliteInt.h.

  3097   3097   #  define sqlite3VtabLock(X) 
  3098   3098   #  define sqlite3VtabUnlock(X)
  3099   3099   #  define sqlite3VtabUnlockList(X)
  3100   3100   #  define sqlite3VtabSavepoint(X, Y, Z) SQLITE_OK
  3101   3101   #  define sqlite3GetVTable(X,Y)  ((VTable*)0)
  3102   3102   #else
  3103   3103      void sqlite3VtabClear(sqlite3 *db, Table*);
         3104  +   void sqlite3VtabDisconnect(sqlite3 *db, Table *p);
  3104   3105      int sqlite3VtabSync(sqlite3 *db, char **);
  3105   3106      int sqlite3VtabRollback(sqlite3 *db);
  3106   3107      int sqlite3VtabCommit(sqlite3 *db);
  3107   3108      void sqlite3VtabLock(VTable *);
  3108   3109      void sqlite3VtabUnlock(VTable *);
  3109   3110      void sqlite3VtabUnlockList(sqlite3*);
  3110   3111      int sqlite3VtabSavepoint(sqlite3 *, int, int);

Changes to src/vtab.c.

   175    175       }
   176    176       pVTable = pNext;
   177    177     }
   178    178   
   179    179     assert( !db || pRet );
   180    180     return pRet;
   181    181   }
          182  +
          183  +/*
          184  +** Table *p is a virtual table. This function removes the VTable object
          185  +** for table *p associated with database connection db from the linked
          186  +** list in p->pVTab. It also decrements the VTable ref count. This is
          187  +** used when closing database connection db to free all of its VTable
          188  +** objects without disturbing the rest of the Schema object (which may
          189  +** be being used by other shared-cache connections).
          190  +*/
          191  +void sqlite3VtabDisconnect(sqlite3 *db, Table *p){
          192  +  VTable **ppVTab;
          193  +
          194  +  assert( IsVirtual(p) );
          195  +  assert( sqlite3BtreeHoldsAllMutexes(db) );
          196  +  assert( sqlite3_mutex_held(db->mutex) );
          197  +
          198  +  for(ppVTab=&p->pVTable; *ppVTab; ppVTab=&(*ppVTab)->pNext){
          199  +    if( (*ppVTab)->db==db  ){
          200  +      VTable *pVTab = *ppVTab;
          201  +      *ppVTab = pVTab->pNext;
          202  +      sqlite3VtabUnlock(pVTab);
          203  +      break;
          204  +    }
          205  +  }
          206  +}
   182    207   
   183    208   
   184    209   /*
   185    210   ** Disconnect all the virtual table objects in the sqlite3.pDisconnect list.
   186    211   **
   187    212   ** This function may only be called when the mutexes associated with all
   188    213   ** shared b-tree databases opened using connection db are held by the 

Changes to test/capi3.test.

   645    645     set STMT [sqlite3_prepare $DB $sql -1 TAIL]
   646    646     expr 0
   647    647   } {0}
   648    648   do_test capi3-6.1 {
   649    649     db cache flush
   650    650     sqlite3_close $DB
   651    651   } {SQLITE_BUSY}
          652  +
          653  +# 6.2 and 6.3 used to return SQLITE_ERROR and SQLITE_SCHEMA, respectively.
          654  +# But since attempting to close a connection no longer resets the internal
          655  +# schema and expires all statements, this is no longer the case.
   652    656   do_test capi3-6.2 {
   653    657     sqlite3_step $STMT
   654         -} {SQLITE_ERROR}
          658  +} {SQLITE_ROW}
   655    659   #check_data $STMT capi3-6.3 {INTEGER} {1} {1.0} {1}
   656    660   do_test capi3-6.3 {
   657    661     sqlite3_finalize $STMT
   658         -} {SQLITE_SCHEMA}
          662  +} {SQLITE_OK}
          663  +
   659    664   do_test capi3-6.4-misuse {
   660    665     db cache flush
   661    666     sqlite3_close $DB
   662    667   } {SQLITE_OK}
   663    668   db close
   664    669   
   665    670   # This procedure sets the value of the file-format in file 'test.db'

Added test/shared8.test.

            1  +# 2012 May 15
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +#
           12  +# The tests in this file are intended to show that closing one database
           13  +# connection to a shared-cache while there exist other connections (a)
           14  +# does not cause the schema to be reloaded and (b) does not cause any
           15  +# other problems.
           16  +#
           17  +
           18  +set testdir [file dirname $argv0]
           19  +source $testdir/tester.tcl
           20  +ifcapable !shared_cache { finish_test ; return }
           21  +set testprefix shared8
           22  +
           23  +db close
           24  +set ::enable_shared_cache [sqlite3_enable_shared_cache 1]
           25  +do_test 0.0 { sqlite3_enable_shared_cache } {1}
           26  +
           27  +proc roman {n} {
           28  +  array set R {1 i 2 ii 3 iii 4 iv 5 v 6 vi 7 vii 8 viii 9 ix 10 x}
           29  +  set R($n)
           30  +}
           31  +
           32  +#-------------------------------------------------------------------------
           33  +# The following tests work as follows:
           34  +#
           35  +#    1.0: Open connection [db1] and populate the database.
           36  +#
           37  +#    1.1: Using "PRAGMA writable_schema", destroy the database schema on
           38  +#         disk. The schema is still in memory, so it is possible to keep
           39  +#         using it, but any attempt to reload it from disk will fail.
           40  +#
           41  +#    1.3-4: Open connection db2. Check that it can see the db schema. Then
           42  +#           close db1 and check that db2 still works. This shows that closing
           43  +#           db1 did not reset the in-memory schema.
           44  +#
           45  +#    1.5-7: Similar to 1.3-4.
           46  +#
           47  +#    1.8: Close all database connections (deleting the in-memory schema).
           48  +#         Then open a new connection and check that it cannot read the db.
           49  +#         
           50  +do_test 1.0 {
           51  +  sqlite3 db1 test.db
           52  +  db1 func roman roman
           53  +  execsql {
           54  +    CREATE TABLE t1(a, b);
           55  +    INSERT INTO t1 VALUES(1, 1);
           56  +    INSERT INTO t1 VALUES(2, 2);
           57  +    INSERT INTO t1 VALUES(3, 3);
           58  +    INSERT INTO t1 VALUES(4, 4);
           59  +    CREATE VIEW v1 AS SELECT a, roman(b) FROM t1;
           60  +    SELECT * FROM v1;
           61  +  } db1
           62  +} {1 i 2 ii 3 iii 4 iv}
           63  +
           64  +do_test 1.1 {
           65  +  execsql { 
           66  +    PRAGMA writable_schema = 1;
           67  +    DELETE FROM sqlite_master WHERE 1;
           68  +    PRAGMA writable_schema = 0;
           69  +    SELECT * FROM sqlite_master;
           70  +  } db1
           71  +} {}
           72  +
           73  +do_test 1.2 {
           74  +  execsql { SELECT * FROM v1 } db1
           75  +} {1 i 2 ii 3 iii 4 iv}
           76  +
           77  +do_test 1.3 {
           78  +  sqlite3 db2 test.db
           79  +  db2 func roman roman
           80  +  execsql { SELECT * FROM v1 } db2
           81  +} {1 i 2 ii 3 iii 4 iv}
           82  +
           83  +do_test 1.4 {
           84  +  db1 close
           85  +  execsql { SELECT * FROM v1 } db2
           86  +} {1 i 2 ii 3 iii 4 iv}
           87  +
           88  +do_test 1.5 {
           89  +  sqlite3 db3 test.db
           90  +  db3 func roman roman
           91  +  execsql { SELECT * FROM v1 } db3
           92  +} {1 i 2 ii 3 iii 4 iv}
           93  +
           94  +do_test 1.6 {
           95  +  execsql { SELECT * FROM v1 } db2
           96  +} {1 i 2 ii 3 iii 4 iv}
           97  +
           98  +do_test 1.7 {
           99  +  db2 close
          100  +  execsql { SELECT * FROM v1 } db3
          101  +} {1 i 2 ii 3 iii 4 iv}
          102  +
          103  +do_test 1.8 {
          104  +  db3 close
          105  +  sqlite3 db4 test.db
          106  +  catchsql { SELECT * FROM v1 } db4
          107  +} {1 {no such table: v1}}
          108  +
          109  +
          110  +foreach db {db1 db2 db3 db4} { catch { $db close } }
          111  +sqlite3_enable_shared_cache $::enable_shared_cache
          112  +finish_test
          113  +