/ Check-in [2b2e9f81]
Login

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

Overview
Comment:Add test cases and fix problems on this branch.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | reuse-schema
Files: files | file ages | folders
SHA3-256: 2b2e9f81cdc7a3ac1eacc087fbd0414ead732878aae296bab6b54b4a7cd0a06e
User & Date: dan 2019-02-08 19:30:09
Wiki:reuse-schema
Context
2019-02-09
17:47
Fix virtual table support for SQLITE_OPEN_REUSABLE_SCHEMA connections. check-in: 3ca8856a user: dan tags: reuse-schema
2019-02-08
19:30
Add test cases and fix problems on this branch. check-in: 2b2e9f81 user: dan tags: reuse-schema
2019-02-05
19:51
Merge latest trunk into this branch. check-in: c089cc4f user: dan tags: reuse-schema
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/analyze.c.

   212    212       Table *pStat;
   213    213       if( (pStat = sqlite3FindTable(db, zTab, pDb->zDbSName))==0 ){
   214    214         if( aTable[i].zCols ){
   215    215           /* The sqlite_statN table does not exist. Create it. Note that a 
   216    216           ** side-effect of the CREATE TABLE statement is to leave the rootpage 
   217    217           ** of the new table in register pParse->regRoot. This is important 
   218    218           ** because the OpenWrite opcode below will be needing it. */
          219  +        sqlite3SchemaWritable(pParse, iDb);
   219    220           sqlite3NestedParse(pParse,
   220    221               "CREATE TABLE %Q.%s(%s)", pDb->zDbSName, zTab, aTable[i].zCols
   221    222           );
   222    223           aRoot[i] = pParse->regRoot;
   223    224           aCreateTbl[i] = OPFLAG_P2ISREG;
   224    225         }
   225    226       }else{
................................................................................
  1335   1336     sqlite3 *db = pParse->db;
  1336   1337     Schema *pSchema = db->aDb[iDb].pSchema;    /* Schema of database iDb */
  1337   1338     HashElem *k;
  1338   1339     int iStatCur;
  1339   1340     int iMem;
  1340   1341     int iTab;
  1341   1342   
  1342         -  sqlite3SchemaWritable(pParse, iDb);
  1343   1343     sqlite3BeginWriteOperation(pParse, 0, iDb);
  1344   1344     iStatCur = pParse->nTab;
  1345   1345     pParse->nTab += 3;
  1346   1346     openStatTable(pParse, iDb, iStatCur, 0, 0);
  1347   1347     iMem = pParse->nMem+1;
  1348   1348     iTab = pParse->nTab;
  1349   1349     assert( sqlite3SchemaMutexHeld(db, iDb, 0) );

Changes to src/callback.c.

    37     37     Schema *pSchema;                /* Linked list of Schema objects */
    38     38     Schema sSchema;                 /* The single dummy schema object */
    39     39     SchemaPool *pNext;              /* Next element in schemaPoolList */
    40     40   };
    41     41   
    42     42   #ifdef SQLITE_DEBUG
    43     43   static void assert_schema_state_ok(sqlite3 *db){
    44         -  if( IsReuseSchema(db) ){
           44  +  if( IsReuseSchema(db) && db->magic!=SQLITE_MAGIC_ZOMBIE ){
    45     45       int i;
    46     46       for(i=0; i<db->nDb; i++){
    47     47         if( i!=1 ){
    48     48           Db *pDb = &db->aDb[i];
    49     49           Btree *pBt = pDb->pBt;
    50     50           if( pDb->pSPool ){
    51     51             if( DbHasProperty(db, i, DB_SchemaLoaded)==0 ){

Changes to src/prepare.c.

   409    409         sqlite3OomFault(db);
   410    410       }
   411    411       sqlite3ResetOneSchema(db, iDb);
   412    412     }
   413    413     db->init.busy = 0;
   414    414     return rc;
   415    415   }
          416  +
          417  +int sqlite3LockReusableSchema(sqlite3 *db){
          418  +  if( IsReuseSchema(db) && (db->mDbFlags & DBFLAG_SchemaInuse)==0 ){
          419  +    db->mDbFlags |= DBFLAG_SchemaInuse;
          420  +    return 1;
          421  +  }
          422  +  return 0;
          423  +}
          424  +void sqlite3UnlockReusableSchema(sqlite3 *db, int bRelease){
          425  +  if( bRelease ){
          426  +    db->mDbFlags &= ~DBFLAG_SchemaInuse;
          427  +    sqlite3SchemaReleaseAll(db);
          428  +  }
          429  +}
   416    430   
   417    431   /*
   418    432   ** Initialize all database files - the main database file, the file
   419    433   ** used to store temporary tables, and any additional database files
   420    434   ** created using ATTACH statements.  Return a success code.  If an
   421    435   ** error occurs, write an error message into *pzErrMsg.
   422    436   **
   423    437   ** After a database is initialized, the DB_SchemaLoaded bit is set
   424    438   ** bit is set in the flags field of the Db structure. If the database
   425    439   ** file was of zero-length, then the DB_Empty flag is also set.
   426    440   */
   427    441   int sqlite3Init(sqlite3 *db, char **pzErrMsg){
   428         -  int i, rc;
          442  +  int rc = SQLITE_OK;
          443  +  int bReleaseSchema;
          444  +  int i;
   429    445     int commit_internal = !(db->mDbFlags&DBFLAG_SchemaChange);
          446  +
          447  +  bReleaseSchema = sqlite3LockReusableSchema(db);
   430    448     
   431    449     assert( sqlite3_mutex_held(db->mutex) );
   432    450     assert( sqlite3BtreeHoldsMutex(db->aDb[0].pBt) );
   433    451     assert( db->init.busy==0 );
   434    452     ENC(db) = SCHEMA_ENC(db);
   435    453     assert( db->nDb>0 );
   436    454     /* Do the main schema first */
   437    455     if( !DbHasProperty(db, 0, DB_SchemaLoaded) ){
   438    456       rc = sqlite3InitOne(db, 0, pzErrMsg, 0);
   439         -    if( rc ) return rc;
   440    457     }
   441    458     /* All other schemas after the main schema. The "temp" schema must be last */
   442         -  for(i=db->nDb-1; i>0; i--){
          459  +  for(i=db->nDb-1; rc==SQLITE_OK && i>0; i--){
   443    460       assert( i==1 || sqlite3BtreeHoldsMutex(db->aDb[i].pBt) );
   444    461       if( !DbHasProperty(db, i, DB_SchemaLoaded) ){
   445    462         rc = sqlite3InitOne(db, i, pzErrMsg, 0);
   446         -      if( rc ) return rc;
   447    463       }
   448    464     }
   449         -  if( commit_internal ){
          465  +  if( rc==SQLITE_OK && commit_internal ){
   450    466       sqlite3CommitInternalChanges(db);
   451    467     }
   452         -  return SQLITE_OK;
          468  +  sqlite3UnlockReusableSchema(db, bReleaseSchema);
          469  +  return rc;
   453    470   }
   454    471   
   455    472   /*
   456    473   ** This routine is a no-op if the database schema is already initialized.
   457    474   ** Otherwise, the schema is loaded. An error code is returned.
   458    475   */
   459    476   int sqlite3ReadSchema(Parse *pParse){
................................................................................
   744    761     if( ppStmt==0 ) return SQLITE_MISUSE_BKPT;
   745    762   #endif
   746    763     *ppStmt = 0;
   747    764     if( !sqlite3SafetyCheckOk(db)||zSql==0 ){
   748    765       return SQLITE_MISUSE_BKPT;
   749    766     }
   750    767     sqlite3_mutex_enter(db->mutex);
   751         -  if( IsReuseSchema(db) ){
   752         -    if( (db->mDbFlags & DBFLAG_SchemaInuse)==0 ){
   753         -      db->mDbFlags |= DBFLAG_SchemaInuse;
   754         -      bReleaseSchema = 1;
   755         -    }
   756         -  }
          768  +  bReleaseSchema = sqlite3LockReusableSchema(db);
   757    769     sqlite3BtreeEnterAll(db);
   758    770     do{
   759    771       /* Make multiple attempts to compile the SQL, until it either succeeds
   760    772       ** or encounters a permanent error.  A schema problem after one schema
   761    773       ** reset is considered a permanent error. */
   762    774       rc = sqlite3Prepare(db, zSql, nBytes, prepFlags, pOld, ppStmt, pzTail);
   763    775       assert( rc==SQLITE_OK || *ppStmt==0 );
   764    776     }while( rc==SQLITE_ERROR_RETRY
   765    777          || (rc==SQLITE_SCHEMA && (sqlite3ResetOneSchema(db,-1), cnt++)==0) );
   766    778   
   767    779     sqlite3BtreeLeaveAll(db);
   768    780   
   769         -  if( bReleaseSchema ){
   770         -    db->mDbFlags &= ~DBFLAG_SchemaInuse;
   771         -    sqlite3SchemaReleaseAll(db);
   772         -  }
          781  +  sqlite3UnlockReusableSchema(db, bReleaseSchema);
   773    782   
   774    783     rc = sqlite3ApiExit(db, rc);
   775    784     assert( (rc&db->errMask)==rc );
   776    785     sqlite3_mutex_leave(db->mutex);
   777    786     return rc;
   778    787   }
   779    788   

Changes to src/sqliteInt.h.

  3258   3258     u8 op;                  /* One of TK_DELETE, TK_UPDATE, TK_INSERT         */
  3259   3259     u8 tr_tm;               /* One of TRIGGER_BEFORE, TRIGGER_AFTER */
  3260   3260     Expr *pWhen;            /* The WHEN clause of the expression (may be NULL) */
  3261   3261     IdList *pColumns;       /* If this is an UPDATE OF <column-list> trigger,
  3262   3262                                the <column-list> is stored here */
  3263   3263     Schema *pSchema;        /* Schema containing the trigger */
  3264   3264     Schema *pTabSchema;     /* Schema containing the table */
         3265  +  char *zTabSchema;       /* Temp triggers in IsReuseSchema() dbs only */
  3265   3266     TriggerStep *step_list; /* Link list of trigger program steps             */
  3266   3267     Trigger *pNext;         /* Next trigger associated with the table */
  3267   3268   };
  3268   3269   
  3269   3270   /*
  3270   3271   ** A trigger is either a BEFORE or an AFTER trigger.  The following constants
  3271   3272   ** determine which.
................................................................................
  4325   4326   Schema *sqlite3SchemaGet(sqlite3 *, Btree *);
  4326   4327   int sqlite3SchemaToIndex(sqlite3 *db, Schema *);
  4327   4328   KeyInfo *sqlite3KeyInfoAlloc(sqlite3*,int,int);
  4328   4329   void sqlite3KeyInfoUnref(KeyInfo*);
  4329   4330   KeyInfo *sqlite3KeyInfoRef(KeyInfo*);
  4330   4331   KeyInfo *sqlite3KeyInfoOfIndex(Parse*, Index*);
  4331   4332   KeyInfo *sqlite3KeyInfoFromExprList(Parse*, ExprList*, int, int);
         4333  +
         4334  +void sqlite3UnlockReusableSchema(sqlite3 *db, int bRelease);
         4335  +int sqlite3LockReusableSchema(sqlite3 *db);
  4332   4336   
  4333   4337   #ifdef SQLITE_DEBUG
  4334   4338   int sqlite3KeyInfoIsWriteable(KeyInfo*);
  4335   4339   #endif
  4336   4340   int sqlite3CreateFunc(sqlite3 *, const char *, int, int, void *,
  4337   4341     void (*)(sqlite3_context*,int,sqlite3_value **),
  4338   4342     void (*)(sqlite3_context*,int,sqlite3_value **), 

Changes to src/trigger.c.

    51     51     Trigger *pList = 0;                  /* List of triggers to return */
    52     52   
    53     53     if( pParse->disableTriggers ){
    54     54       return 0;
    55     55     }
    56     56   
    57     57     if( pTmpSchema!=pTab->pSchema ){
           58  +    sqlite3 *db = pParse->db;
    58     59       HashElem *p;
    59         -    assert( sqlite3SchemaMutexHeld(pParse->db, 0, pTmpSchema) );
           60  +    char *zSchema = 0;
           61  +    if( IsReuseSchema(db) ){
           62  +      zSchema = db->aDb[sqlite3SchemaToIndex(db, pTab->pSchema)].zDbSName;
           63  +    }
           64  +    assert( sqlite3SchemaMutexHeld(db, 0, pTmpSchema) );
    60     65       for(p=sqliteHashFirst(&pTmpSchema->trigHash); p; p=sqliteHashNext(p)){
    61     66         Trigger *pTrig = (Trigger *)sqliteHashData(p);
    62         -      if( pTrig->pTabSchema==pTab->pSchema
    63         -       && 0==sqlite3StrICmp(pTrig->table, pTab->zName) 
           67  +      if( (zSchema==0 && pTrig->pTabSchema==pTab->pSchema)
           68  +       || (zSchema!=0 && 0==sqlite3StrICmp(pTrig->zTabSchema, zSchema))
    64     69         ){
    65         -        pTrig->pNext = (pList ? pList : pTab->pTrigger);
    66         -        pList = pTrig;
           70  +        if( 0==sqlite3StrICmp(pTrig->table, pTab->zName) 
           71  +        ){
           72  +          pTrig->pTabSchema = pTab->pSchema;
           73  +          pTrig->pNext = (pList ? pList : pTab->pTrigger);
           74  +          pList = pTrig;
           75  +        }
    67     76         }
    68     77       }
    69     78     }
    70     79   
    71     80     return (pList ? pList : pTab->pTrigger);
    72     81   }
    73     82   
................................................................................
   240    249   
   241    250     /* Build the Trigger object */
   242    251     pTrigger = (Trigger*)sqlite3DbMallocZero(db, sizeof(Trigger));
   243    252     if( pTrigger==0 ) goto trigger_cleanup;
   244    253     pTrigger->zName = zName;
   245    254     zName = 0;
   246    255     pTrigger->table = sqlite3DbStrDup(db, pTableName->a[0].zName);
          256  +  if( IsReuseSchema(db) && iDb==1 ){
          257  +    int iTabDb = sqlite3SchemaToIndex(db, pTab->pSchema);
          258  +    pTrigger->zTabSchema = sqlite3DbStrDup(db, db->aDb[iTabDb].zDbSName);
          259  +  }
   247    260     pTrigger->pSchema = db->aDb[iDb].pSchema;
   248    261     pTrigger->pTabSchema = pTab->pSchema;
   249    262     pTrigger->op = (u8)op;
   250    263     pTrigger->tr_tm = tr_tm==TK_BEFORE ? TRIGGER_BEFORE : TRIGGER_AFTER;
   251    264     if( IN_RENAME_OBJECT ){
   252    265       sqlite3RenameTokenRemap(pParse, pTrigger->table, pTableName->a[0].zName);
   253    266       pTrigger->pWhen = pWhen;
................................................................................
   537    550   ** Recursively delete a Trigger structure
   538    551   */
   539    552   void sqlite3DeleteTrigger(sqlite3 *db, Trigger *pTrigger){
   540    553     if( pTrigger==0 ) return;
   541    554     sqlite3DeleteTriggerStep(db, pTrigger->step_list);
   542    555     sqlite3DbFree(db, pTrigger->zName);
   543    556     sqlite3DbFree(db, pTrigger->table);
          557  +  sqlite3DbFree(db, pTrigger->zTabSchema);
   544    558     sqlite3ExprDelete(db, pTrigger->pWhen);
   545    559     sqlite3IdListDelete(db, pTrigger->pColumns);
   546    560     sqlite3DbFree(db, pTrigger);
   547    561   }
   548    562   
   549    563   /*
   550    564   ** This function is called to drop a trigger from the database schema. 

Changes to src/vdbe.c.

  5757   5757   ** then runs the new virtual machine.  It is thus a re-entrant opcode.
  5758   5758   */
  5759   5759   case OP_ParseSchema: {
  5760   5760     int iDb;
  5761   5761     const char *zMaster;
  5762   5762     char *zSql;
  5763   5763     InitData initData;
         5764  +  int bRelease;
  5764   5765   
  5765   5766     /* Any prepared statement that invokes this opcode will hold mutexes
  5766   5767     ** on every btree.  This is a prerequisite for invoking 
  5767   5768     ** sqlite3InitCallback().
  5768   5769     */
  5769   5770   #ifdef SQLITE_DEBUG
  5770   5771     for(iDb=0; iDb<db->nDb; iDb++){
................................................................................
  5793   5794       initData.mInitFlags = 0;
  5794   5795       zSql = sqlite3MPrintf(db,
  5795   5796          "SELECT name, rootpage, sql FROM '%q'.%s WHERE %s ORDER BY rowid",
  5796   5797          db->aDb[iDb].zDbSName, zMaster, pOp->p4.z);
  5797   5798       if( zSql==0 ){
  5798   5799         rc = SQLITE_NOMEM_BKPT;
  5799   5800       }else{
         5801  +      bRelease = sqlite3LockReusableSchema(db);
         5802  +      if( IsReuseSchema(db) ){
         5803  +        rc = sqlite3Init(db, &p->zErrMsg);
         5804  +        if( rc ){
         5805  +          sqlite3UnlockReusableSchema(db, bRelease);
         5806  +          goto abort_due_to_error;
         5807  +        }
         5808  +      }
  5800   5809         assert( db->init.busy==0 );
  5801   5810         db->init.busy = 1;
  5802   5811         initData.rc = SQLITE_OK;
  5803   5812         initData.nInitRow = 0;
  5804   5813         assert( !db->mallocFailed );
  5805   5814         rc = sqlite3_exec(db, zSql, sqlite3InitCallback, &initData, 0);
         5815  +      sqlite3UnlockReusableSchema(db, bRelease);
  5806   5816         if( rc==SQLITE_OK ) rc = initData.rc;
  5807   5817         if( rc==SQLITE_OK && initData.nInitRow==0 ){
  5808   5818           /* The OP_ParseSchema opcode with a non-NULL P4 argument should parse
  5809   5819           ** at least one SQL statement. Any less than that indicates that
  5810   5820           ** the sqlite_master table is corrupt. */
  5811   5821           rc = SQLITE_CORRUPT_BKPT;
  5812   5822         }

Changes to test/reuse1.test.

    12     12   #
    13     13   
    14     14   
    15     15   set testdir [file dirname $argv0]
    16     16   source $testdir/tester.tcl
    17     17   set testprefix reuse1
    18     18   
           19  +if 1 {
    19     20   
    20     21   forcedelete test.db2
    21     22   sqlite3 db2 test.db2
    22     23   
    23     24   do_execsql_test 1.0 {
    24     25     CREATE TABLE t1(x INTEGER PRIMARY KEY, y UNIQUE, z);
    25     26     CREATE INDEX i1 ON t1(z);
................................................................................
    32     33     PRAGMA schema_version;
    33     34   } {2}
    34     35   
    35     36   do_test 1.2 {
    36     37     db close
    37     38     db2 close
    38     39     sqlite3 db2 test.db2 -reuse-schema 1
    39         -  sqlite3 db test.db -reuse-schema 1
           40  +  sqlite3 db  test.db -reuse-schema 1
    40     41   } {}
    41     42   
    42     43   do_execsql_test -db db2 1.3.1 {
    43     44     INSERT INTO t1 VALUES(1, 2, 3);
    44     45     INSERT INTO t1 VALUES(4, 5, 6);
    45     46   }
    46     47   
................................................................................
    86     87     7  { DROP INDEX i1 }
    87     88     8  { DROP TABLE t1 }
    88     89     9  { DROP TRIGGER tr1 }
    89     90     10 { ANALYZE }
    90     91   } {
    91     92     do_catchsql_test 1.5.$tn $sql {1 {attempt to modify read-only schema}}
    92     93   }
           94  +
           95  +#-------------------------------------------------------------------------
           96  +#
           97  +reset_db
           98  +forcedelete test.db2
           99  +ifcapable fts5 {
          100  +  do_execsql_test 2.0 {
          101  +    CREATE VIRTUAL TABLE ft USING fts5(a);
          102  +    INSERT INTO ft VALUES('one'), ('two'), ('three');
          103  +    ATTACH 'test.db2' AS aux;
          104  +    CREATE VIRTUAL TABLE aux.ft USING fts5(a);
          105  +    INSERT INTO aux.ft VALUES('aux1'), ('aux2'), ('aux3');
          106  +  }
          107  +
          108  +  db close
          109  +  sqlite3 db  test.db -reuse-schema 1
          110  +
          111  +  do_execsql_test 2.1 {
          112  +    ATTACH 'test.db2' AS aux;
          113  +    SELECT * FROM main.ft;
          114  +  } {one two three}
          115  +
          116  +  do_execsql_test 2.2 {
          117  +    SELECT * FROM aux.ft;
          118  +  } {aux1 aux2 aux3}
          119  +
          120  +  do_execsql_test 2.2 {
          121  +    SELECT * FROM aux.ft_content;
          122  +  } {1 aux1 2 aux2 3 aux3}
          123  +}
          124  +
          125  +}
          126  +
          127  +#-------------------------------------------------------------------------
          128  +#
          129  +reset_db
          130  +forcedelete test.db2
          131  +do_execsql_test 3.0 {
          132  +  CREATE TABLE t1(a PRIMARY KEY, b, c);
          133  +  CREATE VIEW v1 AS SELECT * FROM t1;
          134  +  CREATE TRIGGER v1_ins INSTEAD OF INSERT ON v1 BEGIN
          135  +    INSERT INTO t1 VALUES(new.a, new.b, new.c);
          136  +  END;
          137  +  CREATE TRIGGER v1_del INSTEAD OF DELETE ON v1 BEGIN
          138  +    DELETE FROM t1 WHERE a=old.a;
          139  +  END;
          140  +  CREATE TRIGGER v1_up INSTEAD OF UPDATE ON v1 BEGIN
          141  +    UPDATE t1 SET a=new.a, b=new.b, c=new.c WHERE a=old.a;
          142  +  END;
          143  +}
          144  +forcecopy test.db test.db2
          145  +
          146  +do_test 3.1 {
          147  +  sqlite3 db2 test.db2
          148  +  execsql { INSERT INTO t1 VALUES(1, 2, 3) } db
          149  +  execsql { INSERT INTO t1 VALUES(4, 5, 6) } db2
          150  +  db2 close
          151  +  execsql { ATTACH 'test.db2' AS aux; }
          152  +} {}
          153  +
          154  +do_execsql_test 3.2 {
          155  +  SELECT * FROM main.v1;
          156  +} {1 2 3}
          157  +
          158  +do_execsql_test 3.3 {
          159  +  SELECT * FROM aux.v1;
          160  +} {4 5 6}
          161  +
          162  +db close
          163  +sqlite3 db test.db -reuse-schema 1
          164  +
          165  +do_execsql_test 3.4 { ATTACH 'test.db2' AS aux } {}
          166  +do_execsql_test 3.5 { SELECT * FROM main.v1 } {1 2 3}
          167  +do_execsql_test 3.6 { SELECT * FROM aux.v1  } {4 5 6}
          168  +
          169  +do_execsql_test 3.7.1 { INSERT INTO aux.t1 VALUES(8, 9, 10); }
          170  +do_execsql_test 3.7.2 { SELECT * FROM main.v1 } {1 2 3}
          171  +do_execsql_test 3.7.3 { SELECT * FROM aux.v1  } {4 5 6 8 9 10}
          172  +
          173  +do_execsql_test 3.8.1 { DELETE FROM aux.t1 WHERE b=5 }
          174  +do_execsql_test 3.8.2 { SELECT * FROM main.v1 } {1 2 3}
          175  +do_execsql_test 3.8.3 { SELECT * FROM aux.v1  } {8 9 10}
          176  +
          177  +do_execsql_test 3.9.1 { UPDATE aux.t1 SET b='abc' }
          178  +do_execsql_test 3.9.2 { SELECT * FROM main.v1 } {1 2 3}
          179  +do_execsql_test 3.9.3 { SELECT * FROM aux.v1  } {8 abc 10}
          180  +
          181  +do_execsql_test 3.10.1 { INSERT INTO aux.v1 VALUES(11, 12, 13) }
          182  +do_execsql_test 3.10.2 { SELECT * FROM main.v1 } {1 2 3}
          183  +do_execsql_test 3.10.3 { SELECT * FROM aux.v1  } {8 abc 10 11 12 13}
          184  +
          185  +do_execsql_test 3.11.1 { DELETE FROM aux.v1 WHERE b='abc' }
          186  +do_execsql_test 3.11.2 { SELECT * FROM main.v1 } {1 2 3}
          187  +do_execsql_test 3.11.3 { SELECT * FROM aux.v1  } {11 12 13}
          188  +
          189  +do_execsql_test 3.12.1 { UPDATE aux.v1 SET b='def' }
          190  +do_execsql_test 3.12.2 { SELECT * FROM main.v1 } {1 2 3}
          191  +do_execsql_test 3.12.3 { SELECT * FROM aux.v1  } {11 def 13}
          192  +
          193  +do_execsql_test 3.13.1 {
          194  +  CREATE TEMP TRIGGER xyz AFTER INSERT ON aux.t1 BEGIN
          195  +    INSERT INTO v1 VALUES(new.a, new.b, new.c);
          196  +  END
          197  +}
          198  +do_execsql_test 3.13.2 {
          199  +  INSERT INTO aux.v1 VALUES('x', 'y', 'z');
          200  +}
          201  +do_execsql_test 3.13.3 {
          202  +  SELECT * FROM v1;
          203  +} {1 2 3 x y z}
          204  +
          205  +#-------------------------------------------------------------------------
          206  +#
          207  +reset_db
          208  +forcedelete test.db2
          209  +do_execsql_test 4.0 {
          210  +  CREATE TABLE t1(a PRIMARY KEY, b, c UNIQUE);
          211  +  CREATE TABLE del(a, b, c);
          212  +  CREATE TRIGGER tr1 AFTER DELETE ON t1 BEGIN
          213  +    INSERT INTO del VALUES(old.a, old.b, old.c);
          214  +  END;
          215  +}
          216  +forcecopy test.db test.db2
          217  +
          218  +db close
          219  +sqlite3 db test.db -reuse-schema 1
          220  +execsql { 
          221  +  ATTACH 'test.db2' AS aux;
          222  +  PRAGMA recursive_triggers = 1;
          223  +}
          224  +
          225  +do_execsql_test 4.1 {
          226  +  INSERT INTO main.t1 VALUES(1, 2, 3);
          227  +  INSERT INTO aux.t1 VALUES(4, 5, 6);
          228  +}
          229  +
          230  +do_execsql_test 4.2.1 {
          231  +  INSERT OR REPLACE INTO aux.t1 VALUES('a', 'b', 6);
          232  +  SELECT * FROM aux.t1;
          233  +} {a b 6}
          234  +do_execsql_test 4.2.2 { SELECT * FROM aux.del  } {4 5 6}
          235  +do_execsql_test 4.2.3 { SELECT * FROM main.del } {}
          236  +
          237  +do_execsql_test 4.3.1 {
          238  +  INSERT INTO aux.t1 VALUES('x', 'y', 'z');
          239  +  UPDATE OR REPLACE aux.t1 SET c='z' WHERE a='a';
          240  +} {}
          241  +do_execsql_test 4.3.2 { SELECT * FROM aux.del  } {4 5 6 x y z}
          242  +do_execsql_test 4.3.3 { SELECT * FROM main.del } {}
          243  +
          244  +#-------------------------------------------------------------------------
          245  +#
          246  +reset_db
          247  +do_execsql_test 5.0 {
          248  +  CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
          249  +  CREATE TABLE t2(a INTEGER PRIMARY KEY, b, c);
          250  +  CREATE INDEX i1 ON t1(b);
          251  +  INSERT INTO t1 VALUES(1, 2, 3), (4, 5, 6);
          252  +  ANALYZE;
          253  +  PRAGMA writable_schema = 1;
          254  +  DELETE FROM sqlite_stat1;
          255  +}
          256  +db close
          257  +forcecopy test.db test.db2
          258  +sqlite3 db test.db -reuse-schema 1
          259  +execsql { ATTACH 'test.db2' AS aux }
          260  +
          261  +foreach {tn sql} {
          262  +  1 { CREATE TABLE t3(x) }
          263  +  2 { DROP TABLE t2 }
          264  +  3 { CREATE INDEX i2 ON t2(b) }
          265  +  4 { DROP INDEX i1 }
          266  +  5 { ALTER TABLE t1 ADD COLUMN d }
          267  +  6 { ALTER TABLE t1 RENAME TO t3 }
          268  +  7 { ALTER TABLE t1 RENAME c TO d }
          269  +} {
          270  +  do_catchsql_test 5.1.$tn $sql {1 {attempt to modify read-only schema}}
          271  +}
          272  +
          273  +do_execsql_test 5.2.1 { ANALYZE aux.t1 } {}
          274  +do_execsql_test 5.2.2 { SELECT * FROM aux.sqlite_stat1  } {t1 i1 {2 1}}
          275  +do_execsql_test 5.2.3 { SELECT * FROM main.sqlite_stat1 } {}
          276  +
          277  +do_test 5.3.0 {
          278  +  sqlite3 db2 test.db2
          279  +  db2 eval { 
          280  +    PRAGMA writable_schema = 1;
          281  +    DELETE FROM sqlite_stat1;
          282  +  }
          283  +} {}
          284  +
          285  +do_execsql_test 5.3.1 { SELECT * FROM aux.sqlite_stat1  } {}
          286  +do_execsql_test 5.3.2 { ANALYZE aux } {}
          287  +do_execsql_test 5.3.3 { SELECT * FROM aux.sqlite_stat1  } {t1 i1 {2 1}}
          288  +do_execsql_test 5.3.4 { SELECT * FROM main.sqlite_stat1 } {}
          289  +
          290  +#-------------------------------------------------------------------------
          291  +# Attempting to run ANALYZE when the required sqlite_statXX functions
          292  +# are missing is an error (because it would modify the database schema).
          293  +#
          294  +reset_db
          295  +do_execsql_test 5.4 {
          296  +  CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
          297  +  CREATE TABLE t2(a INTEGER PRIMARY KEY, b, c);
          298  +  CREATE INDEX i1 ON t1(b);
          299  +  INSERT INTO t1 VALUES(1, 2, 3), (4, 5, 6);
          300  +}
          301  +db close
          302  +sqlite3 db test.db -reuse-schema 1
          303  +breakpoint
          304  +foreach {tn sql} {
          305  +  1 { ANALYZE }
          306  +  2 { ANALYZE t1 }
          307  +  3 { ANALYZE i1 }
          308  +  4 { ANALYZE main }
          309  +  5 { ANALYZE main.t1 }
          310  +  6 { ANALYZE main.i1 }
          311  +} {
          312  +  do_catchsql_test 5.4.$tn $sql {1 {attempt to modify read-only schema}}
          313  +}
          314  +
          315  +#-------------------------------------------------------------------------
          316  +#
          317  +reset_db
          318  +do_execsql_test 6.0 {
          319  +  CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
          320  +  CREATE VIEW v1 AS SELECT * FROM t1;
          321  +}
          322  +db close
          323  +forcecopy test.db test.db2
          324  +sqlite3 db test.db -reuse-schema 1
          325  +execsql { ATTACH 'test.db2' AS aux }
          326  +
          327  +do_execsql_test 6.1 {
          328  +  INSERT INTO main.t1(a) VALUES(1), (2), (3);
          329  +  INSERT INTO aux.t1(a) VALUES(4), (5), (6);
          330  +  CREATE TEMP TABLE t2(i,t);
          331  +  INSERT INTO t2 VALUES(2, 'two'), (5, 'five');
          332  +}
          333  +
          334  +do_execsql_test 6.2 {
          335  +  SELECT t FROM t2 WHERE i IN (SELECT a FROM aux.t1)
          336  +} {five}
          337  +do_execsql_test 6.3 {
          338  +  SELECT t FROM t2 WHERE i IN (SELECT a FROM aux.v1)
          339  +} {five}
          340  +
          341  +#-------------------------------------------------------------------------
          342  +#
          343  +reset_db
          344  +do_execsql_test 7.0 {
          345  +  CREATE TABLE p1(a PRIMARY KEY, b);
          346  +  CREATE TABLE p2(a PRIMARY KEY, b);
          347  +  CREATE TABLE c1(x REFERENCES p1 ON UPDATE CASCADE ON DELETE CASCADE);
          348  +}
          349  +
          350  +db close
          351  +forcecopy test.db test.db2
          352  +sqlite3 db test.db -reuse-schema 1
          353  +execsql { ATTACH 'test.db2' AS aux }
          354  +
          355  +do_execsql_test 7.1 {
          356  +  INSERT INTO aux.p1 VALUES(1, 'one');
          357  +  INSERT INTO aux.p1 VALUES(2, 'two');
          358  +  PRAGMA foreign_keys = on;
          359  +}
          360  +
          361  +do_execsql_test 7.2 {
          362  +  INSERT INTO aux.c1 VALUES(2);
          363  +}
          364  +
          365  +do_execsql_test 7.3.1 {
          366  +  PRAGMA foreign_keys = off;
          367  +  INSERT INTO main.p2 SELECT * FROM aux.p1;
          368  +}
          369  +do_execsql_test 7.3.2 {
          370  +  SELECT * FROM main.p2;
          371  +} {1 one 2 two}
          372  +
          373  +do_execsql_test 7.3.3 {
          374  +  INSERT INTO aux.p2 VALUES(1, 2);
          375  +}
          376  +
          377  +do_execsql_test 7.3.4 {
          378  +  SELECT main.p2.a FROM main.p2, aux.p2;
          379  +} {1 2}
          380  +
          381  +do_execsql_test 7.3.5 {
          382  +  SELECT * FROM main.p2, aux.p2;
          383  +} {1 one 1 2   2 two 1 2}
          384  +
          385  +do_execsql_test 7.4 {
          386  +  SELECT count(*) FROM aux.p2;
          387  +} {1}
          388  +
    93    389   
    94    390   finish_test
          391  +
          392  +