/ Check-in [9a78d89c]
Login

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

Overview
Comment:Add test and fixes for SQLITE_OPEN_SHARED_SCHEMA mode.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | reuse-schema
Files: files | file ages | folders
SHA3-256: 9a78d89c8427ae737d4798cc146e41ada7cb83f4e39dcf3ac9469c510ed37673
User & Date: dan 2019-02-20 17:36:10
Wiki:reuse-schema
Context
2019-02-20
18:44
Further test cases and fixes for SQLITE_OPEN_SHARED_SCHEMA. check-in: ba0ab042 user: dan tags: reuse-schema
17:36
Add test and fixes for SQLITE_OPEN_SHARED_SCHEMA mode. check-in: 9a78d89c user: dan tags: reuse-schema
2019-02-19
18:00
Improve error messages caused by corrupt database schemas in OPEN_SHARED_SCHEMA mode. check-in: 8ac75b8a user: dan tags: reuse-schema
Changes
Hide Diffs Side-by-Side Diffs Show Whitespace Changes Patch

Changes to doc/shared_schema.md.

    22     22   
    23     23   Connections opened with the SQLITE_OPEN_SHARED_SCHEMA flag
    24     24   specified may not modify any database schema except that belonging to the
    25     25   temp database in anyway. This includes creating or dropping database 
    26     26   objects, vacuuming the database, or running ANALYZE when the
    27     27   sqlite_stat\[14\] tables do not exist.
    28     28   
    29         -If the schema of a database attached to an
    30         -SQLITE_OPEN_SHARED_SCHEMA database handle is corrupt, or if
    31         -corruption is encountered while parsing the database schema, then the
    32         -database is treated as empty. This usually means that corruption results in
    33         -a "no such table: xxx" error instead of a more specific error message.
    34         -
    35     29   For SQLITE_OPEN_SHARED_SCHEMA connections, the
    36     30   SQLITE_DBSTATUS_SCHEMA_USED sqlite3_db_status() verb
    37     31   distributes the memory used for a shared schema object evenly between all
    38     32   database connections that share it.
    39     33   
    40     34   ## Implementation Notes
    41     35   

Changes to src/attach.c.

   319    319       goto detach_error;
   320    320     }
   321    321     if( sqlite3BtreeIsInReadTrans(pDb->pBt) || sqlite3BtreeIsInBackup(pDb->pBt) ){
   322    322       sqlite3_snprintf(sizeof(zErr),zErr, "database %s is locked", zName);
   323    323       goto detach_error;
   324    324     }
   325    325   
          326  +  sqlite3SchemaDisconnect(db, i, 0);
   326    327     sqlite3BtreeClose(pDb->pBt);
   327    328     pDb->pBt = 0;
   328    329     pDb->pSchema = 0;
   329    330     sqlite3CollapseDatabaseArray(db);
   330    331     return;
   331    332   
   332    333   detach_error:

Changes to src/test_schemapool.c.

    70     70     char **pzErr
    71     71   ){
    72     72     int rc = SQLITE_NOMEM;
    73     73     schemapool_vtab *pVtab = sqlite3_malloc(sizeof(schemapool_vtab));
    74     74     if( pVtab ){
    75     75       memset(pVtab, 0, sizeof(schemapool_vtab));
    76     76       rc = sqlite3_declare_vtab(db, SCHEMAPOOL_SCHEMA);
           77  +    if( rc!=SQLITE_OK ){
           78  +      sqlite3_free(pVtab);
           79  +      pVtab = 0;
           80  +    }
    77     81     }
    78     82     *ppVtab = (sqlite3_vtab *)pVtab;
    79     83     return rc;
    80     84   }
    81     85   
    82     86   /*
    83     87   ** Open a new cursor on the schema table.

Changes to test/reuse3.test.

   255    255     PRAGMA writable_schema = 1;
   256    256     UPDATE sqlite_master SET sql='CREATE TABLE t3 a,b' WHERE name = 't3';
   257    257   }
   258    258   
   259    259   do_test 5.2 { 
   260    260     catchsql { SELECT * FROM t1 } db2
   261    261   } {1 {malformed database schema (t3) - near "a": syntax error}}
          262  +
          263  +do_test 5.3 { 
          264  +  catchsql { SELECT nref,nschema FROM schemapool } db2
          265  +} {1 {vtable constructor failed: schemapool}}
          266  +
          267  +do_execsql_test 5.4 {
          268  +  PRAGMA writable_schema = 1;
          269  +  UPDATE sqlite_master SET sql='CREATE TABLE t3(a,b)' WHERE name = 't3';
          270  +}
          271  +
          272  +do_test 5.5 { 
          273  +  catchsql { SELECT nref,nschema FROM schemapool } db2
          274  +} {0 {1 1}}
   262    275   
   263    276   finish_test
   264    277   

Added test/reuse4.test.

            1  +# 2019 February 12
            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  +#
           13  +
           14  +set testdir [file dirname $argv0]
           15  +source $testdir/tester.tcl
           16  +set testprefix reuse4
           17  +
           18  +foreach {tn sharedschema} {
           19  +  1 0  
           20  +  2 1  
           21  +} {
           22  +  reset_db
           23  +
           24  +  do_execsql_test 1.$tn.0 {
           25  +    CREATE TABLE x1(a, b);
           26  +    CREATE INDEX x1a ON x1(a);
           27  +    CREATE INDEX x1b ON x1(b);
           28  +    CREATE TABLE x2(a, b);
           29  +  }
           30  +  db close
           31  +  
           32  +  do_test 1.$tn.1 {
           33  +    for {set i 1} {$i<4} {incr i} {
           34  +      forcedelete test.db$i test.db$i-journal test.db$i-wal
           35  +      forcecopy test.db test.db$i
           36  +    }
           37  +  
           38  +    sqlite3 db test.db -shared-schema $sharedschema
           39  +    for {set i 1} {$i<4} {incr i} {
           40  +      execsql " ATTACH 'test.db$i' AS db$i "
           41  +    }
           42  +  } {}
           43  +  
           44  +  do_execsql_test 1.$tn.2 {
           45  +    WITH s(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<10 )
           46  +    INSERT INTO x1 SELECT i, i FROM s;
           47  +  
           48  +    INSERT INTO db3.x2 SELECT * FROM x1;
           49  +    INSERT INTO db2.x1 SELECT * FROM db3.x2;
           50  +    CREATE TEMP TRIGGER tr1 AFTER INSERT ON db2.x2 BEGIN
           51  +      INSERT INTO x1 VALUES(new.a, new.b);
           52  +    END;
           53  +    INSERT INTO db2.x2 SELECT * FROM x1 WHERE a%2;
           54  +    DELETE FROM x1 WHERE a<3;
           55  +    INSERT INTO db3.x1 SELECT * FROM db2.x2;
           56  +  
           57  +    DETACH db3;
           58  +    ATTACH 'test.db3' AS db3;
           59  +  
           60  +    UPDATE db3.x1 SET a=a-10 WHERE b NOT IN (SELECT b FROM db2.x2);
           61  +  
           62  +    CREATE TEMP TABLE x1(a, b);
           63  +    INSERT INTO db2.x2 VALUES(50, 60), (60, 70), (80, 90);
           64  +    ALTER TABLE x1 RENAME TO x2;
           65  +    ALTER TABLE x2 ADD COLUMN c;
           66  +    ALTER TABLE x2 RENAME a TO aaa;
           67  +    DELETE FROM x1 WHERE b>8;
           68  +    UPDATE db3.x2 SET b=b*10;
           69  +  
           70  +    BEGIN;
           71  +      CREATE TEMP TABLE x5(x);
           72  +      INSERT INTO x5 VALUES(1);
           73  +    ROLLBACK;
           74  +  
           75  +    INSERT INTO main.x2 VALUES(123, 456);
           76  +  }
           77  +  
           78  +  integrity_check 1.$tn.3
           79  +  
           80  +  do_execsql_test 1.$tn.4 {
           81  +    SELECT * FROM main.x1; SELECT 'xxx';
           82  +    SELECT * FROM main.x2; SELECT 'xxx';
           83  +    SELECT * FROM temp.x2; SELECT 'xxx';
           84  +  
           85  +    SELECT * FROM db1.x1; SELECT 'xxx';
           86  +    SELECT * FROM db1.x2; SELECT 'xxx';
           87  +    SELECT * FROM db2.x1; SELECT 'xxx';
           88  +    SELECT * FROM db2.x2; SELECT 'xxx';
           89  +    SELECT * FROM db3.x1; SELECT 'xxx';
           90  +    SELECT * FROM db3.x2; SELECT 'xxx';
           91  +  } {
           92  +    3 3 4 4 5 5 6 6 7 7 8 8 3 3 5 5 7 7 xxx 
           93  +    123 456 xxx 
           94  +    50 60 {} 60 70 {} 80 90 {} xxx
           95  +    xxx
           96  +    xxx
           97  +    1 1 2 2 3 3 4 4 5 5 6 6 7 7 8 8 9 9 10 10 xxx
           98  +    1 1 3 3 5 5 7 7 9 9 50 60 60 70 80 90 xxx
           99  +    1 1 3 3  5 5 7 7 9 9 xxx 
          100  +    1 10 2 20 3 30 4 40 5 50 6 60 7 70 8 80 9 90 10 100 xxx
          101  +  }
          102  +  
          103  +  do_test 1.$tn.5.1 {
          104  +    sqlite3 db2 test.db
          105  +    db2 eval { CREATE TABLE x3(x) }
          106  +  } {}
          107  +  do_execsql_test 1.$tn.5.2 {
          108  +    SELECT * FROM main.x1; SELECT 'xxx';
          109  +    SELECT * FROM main.x2; SELECT 'xxx';
          110  +    SELECT * FROM main.x3; SELECT 'xxx';
          111  +  } {
          112  +    3 3 4 4 5 5 6 6 7 7 8 8 3 3 5 5 7 7 xxx 
          113  +    123 456 xxx 
          114  +    xxx
          115  +  }
          116  +}
          117  +
          118  +finish_test
          119  +