SQLite

Check-in [9a78d89c84]
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
Timelines: family | ancestors | descendants | both | reuse-schema
Files: files | file ages | folders
SHA3-256: 9a78d89c8427ae737d4798cc146e41ada7cb83f4e39dcf3ac9469c510ed37673
User & Date: dan 2019-02-20 17:36:10.699
Context
2019-02-20
18:44
Further test cases and fixes for SQLITE_OPEN_SHARED_SCHEMA. (check-in: ba0ab042f4 user: dan tags: reuse-schema)
17:36
Add test and fixes for SQLITE_OPEN_SHARED_SCHEMA mode. (check-in: 9a78d89c84 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: 8ac75b8a88 user: dan tags: reuse-schema)
Changes
Unified Diff Ignore Whitespace Patch
Changes to doc/shared_schema.md.
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

Connections opened with the SQLITE_OPEN_SHARED_SCHEMA flag
specified may not modify any database schema except that belonging to the
temp database in anyway. This includes creating or dropping database 
objects, vacuuming the database, or running ANALYZE when the
sqlite_stat\[14\] tables do not exist.

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








<
<
<
<
<
<







22
23
24
25
26
27
28






29
30
31
32
33
34
35

Connections opened with the SQLITE&#95;OPEN&#95;SHARED&#95;SCHEMA flag
specified may not modify any database schema except that belonging to the
temp database in anyway. This includes creating or dropping database 
objects, vacuuming the database, or running ANALYZE when the
sqlite&#95;stat\[14\] tables do not exist.







For SQLITE&#95;OPEN&#95;SHARED&#95;SCHEMA connections, the
SQLITE&#95;DBSTATUS&#95;SCHEMA&#95;USED sqlite3&#95;db&#95;status() verb
distributes the memory used for a shared schema object evenly between all
database connections that share it.

## Implementation Notes

Changes to src/attach.c.
319
320
321
322
323
324
325

326
327
328
329
330
331
332
    goto detach_error;
  }
  if( sqlite3BtreeIsInReadTrans(pDb->pBt) || sqlite3BtreeIsInBackup(pDb->pBt) ){
    sqlite3_snprintf(sizeof(zErr),zErr, "database %s is locked", zName);
    goto detach_error;
  }


  sqlite3BtreeClose(pDb->pBt);
  pDb->pBt = 0;
  pDb->pSchema = 0;
  sqlite3CollapseDatabaseArray(db);
  return;

detach_error:







>







319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
    goto detach_error;
  }
  if( sqlite3BtreeIsInReadTrans(pDb->pBt) || sqlite3BtreeIsInBackup(pDb->pBt) ){
    sqlite3_snprintf(sizeof(zErr),zErr, "database %s is locked", zName);
    goto detach_error;
  }

  sqlite3SchemaDisconnect(db, i, 0);
  sqlite3BtreeClose(pDb->pBt);
  pDb->pBt = 0;
  pDb->pSchema = 0;
  sqlite3CollapseDatabaseArray(db);
  return;

detach_error:
Changes to src/test_schemapool.c.
70
71
72
73
74
75
76




77
78
79
80
81
82
83
  char **pzErr
){
  int rc = SQLITE_NOMEM;
  schemapool_vtab *pVtab = sqlite3_malloc(sizeof(schemapool_vtab));
  if( pVtab ){
    memset(pVtab, 0, sizeof(schemapool_vtab));
    rc = sqlite3_declare_vtab(db, SCHEMAPOOL_SCHEMA);




  }
  *ppVtab = (sqlite3_vtab *)pVtab;
  return rc;
}

/*
** Open a new cursor on the schema table.







>
>
>
>







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

/*
** Open a new cursor on the schema table.
Changes to test/reuse3.test.
255
256
257
258
259
260
261













262
263
264
  PRAGMA writable_schema = 1;
  UPDATE sqlite_master SET sql='CREATE TABLE t3 a,b' WHERE name = 't3';
}

do_test 5.2 { 
  catchsql { SELECT * FROM t1 } db2
} {1 {malformed database schema (t3) - near "a": syntax error}}














finish_test








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



255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
  PRAGMA writable_schema = 1;
  UPDATE sqlite_master SET sql='CREATE TABLE t3 a,b' WHERE name = 't3';
}

do_test 5.2 { 
  catchsql { SELECT * FROM t1 } db2
} {1 {malformed database schema (t3) - near "a": syntax error}}

do_test 5.3 { 
  catchsql { SELECT nref,nschema FROM schemapool } db2
} {1 {vtable constructor failed: schemapool}}

do_execsql_test 5.4 {
  PRAGMA writable_schema = 1;
  UPDATE sqlite_master SET sql='CREATE TABLE t3(a,b)' WHERE name = 't3';
}

do_test 5.5 { 
  catchsql { SELECT nref,nschema FROM schemapool } db2
} {0 {1 1}}

finish_test

Added test/reuse4.test.














































































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
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
# 2019 February 12
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
#
#

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

foreach {tn sharedschema} {
  1 0  
  2 1  
} {
  reset_db

  do_execsql_test 1.$tn.0 {
    CREATE TABLE x1(a, b);
    CREATE INDEX x1a ON x1(a);
    CREATE INDEX x1b ON x1(b);
    CREATE TABLE x2(a, b);
  }
  db close
  
  do_test 1.$tn.1 {
    for {set i 1} {$i<4} {incr i} {
      forcedelete test.db$i test.db$i-journal test.db$i-wal
      forcecopy test.db test.db$i
    }
  
    sqlite3 db test.db -shared-schema $sharedschema
    for {set i 1} {$i<4} {incr i} {
      execsql " ATTACH 'test.db$i' AS db$i "
    }
  } {}
  
  do_execsql_test 1.$tn.2 {
    WITH s(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<10 )
    INSERT INTO x1 SELECT i, i FROM s;
  
    INSERT INTO db3.x2 SELECT * FROM x1;
    INSERT INTO db2.x1 SELECT * FROM db3.x2;
    CREATE TEMP TRIGGER tr1 AFTER INSERT ON db2.x2 BEGIN
      INSERT INTO x1 VALUES(new.a, new.b);
    END;
    INSERT INTO db2.x2 SELECT * FROM x1 WHERE a%2;
    DELETE FROM x1 WHERE a<3;
    INSERT INTO db3.x1 SELECT * FROM db2.x2;
  
    DETACH db3;
    ATTACH 'test.db3' AS db3;
  
    UPDATE db3.x1 SET a=a-10 WHERE b NOT IN (SELECT b FROM db2.x2);
  
    CREATE TEMP TABLE x1(a, b);
    INSERT INTO db2.x2 VALUES(50, 60), (60, 70), (80, 90);
    ALTER TABLE x1 RENAME TO x2;
    ALTER TABLE x2 ADD COLUMN c;
    ALTER TABLE x2 RENAME a TO aaa;
    DELETE FROM x1 WHERE b>8;
    UPDATE db3.x2 SET b=b*10;
  
    BEGIN;
      CREATE TEMP TABLE x5(x);
      INSERT INTO x5 VALUES(1);
    ROLLBACK;
  
    INSERT INTO main.x2 VALUES(123, 456);
  }
  
  integrity_check 1.$tn.3
  
  do_execsql_test 1.$tn.4 {
    SELECT * FROM main.x1; SELECT 'xxx';
    SELECT * FROM main.x2; SELECT 'xxx';
    SELECT * FROM temp.x2; SELECT 'xxx';
  
    SELECT * FROM db1.x1; SELECT 'xxx';
    SELECT * FROM db1.x2; SELECT 'xxx';
    SELECT * FROM db2.x1; SELECT 'xxx';
    SELECT * FROM db2.x2; SELECT 'xxx';
    SELECT * FROM db3.x1; SELECT 'xxx';
    SELECT * FROM db3.x2; SELECT 'xxx';
  } {
    3 3 4 4 5 5 6 6 7 7 8 8 3 3 5 5 7 7 xxx 
    123 456 xxx 
    50 60 {} 60 70 {} 80 90 {} xxx
    xxx
    xxx
    1 1 2 2 3 3 4 4 5 5 6 6 7 7 8 8 9 9 10 10 xxx
    1 1 3 3 5 5 7 7 9 9 50 60 60 70 80 90 xxx
    1 1 3 3  5 5 7 7 9 9 xxx 
    1 10 2 20 3 30 4 40 5 50 6 60 7 70 8 80 9 90 10 100 xxx
  }
  
  do_test 1.$tn.5.1 {
    sqlite3 db2 test.db
    db2 eval { CREATE TABLE x3(x) }
  } {}
  do_execsql_test 1.$tn.5.2 {
    SELECT * FROM main.x1; SELECT 'xxx';
    SELECT * FROM main.x2; SELECT 'xxx';
    SELECT * FROM main.x3; SELECT 'xxx';
  } {
    3 3 4 4 5 5 6 6 7 7 8 8 3 3 5 5 7 7 xxx 
    123 456 xxx 
    xxx
  }
}

finish_test