SQLite

** 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 loadReusableSchema(sqlite3 *db, int iDb){
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 = loadReusableSchema(db, j);
        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 ){
        loadReusableSchema(db, 0);
        loadSharableSchema(db, 0);
        if( sqlite3VtabEponymousTableInit(pParse, pMod) ){
          return pMod->pEpoTab;
        }
      }
    }
#endif
    if( flags & LOCATE_NOERR ) return 0;

** 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
** 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_REUSE_SCHEMA flag
** 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_REUSE_SCHEMA). If so, do nothing. Otherwise, leave an 
** 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_REUSE_SCHEMA) 
  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_REUSE_SCHEMA, has just been parsed. This function either
** 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_REUSE_SCHEMA flag, this function is a no-op.
** 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_REUSE_SCHEMA flag
** 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_REUSE_SCHEMA)==0 ){
  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 ){

     || pIndex->tnum<2
     || sqlite3IndexHasDuplicateRootPage(pIndex)
    ){
      corruptSchema(pData, argv[0], pIndex?"invalid rootpage":"orphan index");
    }
  }

  if( iDb!=1 && (db->openFlags & SQLITE_OPEN_REUSE_SCHEMA) ){
  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_REUSE_SCHEMA) ){
  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.
  */

      }
      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);
          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;
      }

#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() */
#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

  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)
#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)

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?"
    " ?-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, "-reuse-schema")==0 ){
    }else if( strcmp(zArg, "-shared-schema")==0 ){
      int b;
      if( Tcl_GetBooleanFromObj(interp, objv[i], &b) ) return TCL_ERROR;
      if( b ){
        flags |= SQLITE_OPEN_REUSE_SCHEMA;
        flags |= SQLITE_OPEN_SHARED_SCHEMA;
      }else{
        flags &= ~SQLITE_OPEN_REUSE_SCHEMA;
        flags &= ~SQLITE_OPEN_SHARED_SCHEMA;
      }
    }else{
      Tcl_AppendResult(interp, "unknown option: ", zArg, (char*)0);
      return TCL_ERROR;
    }
  }
  zErrMsg = 0;

  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
  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 -reuse-schema 1
  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 -reuse-schema 1
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 -reuse-schema 1
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 -reuse-schema 1
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 -reuse-schema 1
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 }
} {
  do_catchsql_test 5.4.$tn $sql {1 {attempt to modify read-only schema}}
}

#-------------------------------------------------------------------------
#
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
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 -reuse-schema 1
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;
}

  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
  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 -reuse-schema 1
  sqlite3 db test.db -shared-schema 1

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

#--------------------------------------------------------------------------
reset_db
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
  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 -reuse-schema 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 -reuse-schema 1
  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 -reuse-schema 1
  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

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
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 -reuse-schema 1
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