# 2017 August 9 # # 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 reuse2 do_execsql_test 1.0 { CREATE TABLE t1(x INTEGER PRIMARY KEY, y UNIQUE, z); 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 } {} do_execsql_test -db db2 1.3.1 { INSERT INTO t1 VALUES(1, 2, 3); } do_execsql_test -db db2 1.3.2 { INSERT INTO t1 VALUES(4, 5, 6); } do_execsql_test 1.3.3 { SELECT * FROM t1; } {1 2 3 4 5 6} #-------------------------------------------------------------------------- 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 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 } {} 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 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 { SELECT * FROM t1; SELECT 'nref=' || nRef, 'nschema=' || nSchema FROM schemapool; } {nref=3 nschema=1} do_execsql_test 3.7 { CREATE TABLE t2(x); } do_execsql_test 3.8 { SELECT 'nref=' || nRef, 'nschema=' || nSchema FROM schemapool; } {nref=3 nschema=1} do_execsql_test -db db1 3.9.1 { SELECT * FROM t1 } do_execsql_test 3.9.2 { SELECT 'nref=' || nRef, 'nschema=' || nSchema FROM schemapool ORDER BY 1; } {nref=1 nschema=1 nref=2 nschema=1} do_execsql_test -db db2 3.10.1 { SELECT * FROM t1 } do_execsql_test 3.10.2 { SELECT 'nref=' || nRef, 'nschema=' || nSchema FROM schemapool ORDER BY 1; } {nref=1 nschema=1 nref=2 nschema=1} do_execsql_test -db db3 3.11.1 { SELECT * FROM t1 } do_execsql_test 3.11.2 { SELECT 'nref=' || nRef, 'nschema=' || nSchema FROM schemapool ORDER BY 1; } {nref=3 nschema=1} #-------------------------------------------------------------------------- catch {db1 close} catch {db2 close} catch {db3 close} reset_db do_execsql_test 4.0.1 { CREATE TABLE x1(a, b, c); CREATE INDEX x1a ON x1(a); CREATE INDEX x1b ON x1(b); } 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 } {} register_schemapool_module db do_execsql_test 4.0.3 { SELECT 'nref=' || nRef, 'nschema=' || nSchema FROM schemapool ORDER BY 1; } {} do_test 4.1.1 { execsql { ATTACH 'test.db1' AS db1; ATTACH 'test.db2' AS db2; ATTACH 'test.db3' AS db3; ATTACH 'test.db4' AS db4; ATTACH 'test.db5' AS db5; } db2 } {} do_execsql_test 4.1.2 { SELECT 'nref=' || nRef, 'nschema=' || nSchema FROM schemapool ORDER BY 1; } {} do_execsql_test -db db2 4.1.3 { SELECT * FROM db3.x1 } do_execsql_test 4.1.4 { SELECT 'nref=' || nRef, 'nschema=' || nSchema FROM schemapool ORDER BY 1; } {nref=1 nschema=1} do_execsql_test -db db2 4.1.5 { SELECT * FROM db2.x1 } do_execsql_test 4.1.6 { SELECT 'nref=' || nRef, 'nschema=' || nSchema FROM schemapool ORDER BY 1; } {nref=2 nschema=1} do_execsql_test -db db2 4.1.7 { SELECT * FROM x1 } do_execsql_test 4.1.8 { SELECT 'nref=' || nRef, 'nschema=' || nSchema FROM schemapool ORDER BY 1; } {nref=6 nschema=1} do_test 4.2.1 { catchsql { SELECT * FROM abc } db2 } {1 {no such table: abc}} do_execsql_test 4.2.2 { SELECT 'nref=' || nRef, 'nschema=' || nSchema FROM schemapool ORDER BY 1; } {nref=6 nschema=1} register_schemapool_module db2 do_execsql_test -db db2 4.3.1 { INSERT INTO x1 VALUES(1, 2, 3); INSERT INTO db1.x1 VALUES(4, 5, 6); INSERT INTO db2.x1 VALUES(7, 8, 9); INSERT INTO db3.x1 VALUES(10, 11, 12); INSERT INTO db4.x1 VALUES(13, 14, 15); INSERT INTO db5.x1 VALUES(16, 17, 18); SELECT 'nref=' || nRef, 'nschema=' || nSchema FROM schemapool ORDER BY 1; } {nref=6 nschema=1} do_execsql_test -db db2 4.3.2 { SELECT * FROM db5.x1; SELECT * FROM db4.x1; SELECT * FROM db3.x1; SELECT * FROM db2.x1; SELECT * FROM db1.x1; SELECT * FROM x1; SELECT 'nref=' || nRef, 'nschema=' || nSchema FROM schemapool ORDER BY 1; } { 16 17 18 13 14 15 10 11 12 7 8 9 4 5 6 1 2 3 nref=6 nschema=1 } do_execsql_test -db db2 4.3.3 { UPDATE x1 SET a=a+10; UPDATE db5.x1 SET a=a+10; SELECT 'nref=' || nRef, 'nschema=' || nSchema FROM schemapool ORDER BY 1; } { nref=6 nschema=1 } do_execsql_test -db db2 4.3.4 { SELECT * FROM db5.x1; SELECT * FROM db4.x1; SELECT * FROM db3.x1; SELECT * FROM db2.x1; SELECT * FROM db1.x1; SELECT * FROM x1; SELECT 'nref=' || nRef, 'nschema=' || nSchema FROM schemapool ORDER BY 1; } { 26 17 18 13 14 15 10 11 12 7 8 9 4 5 6 11 2 3 nref=6 nschema=1 } do_execsql_test -db db2 4.3.5 { DELETE FROM db3.x1; DELETE FROM x1; SELECT 'nref=' || nRef, 'nschema=' || nSchema FROM schemapool ORDER BY 1; } { nref=6 nschema=1 } do_execsql_test -db db2 4.3.6 { SELECT * FROM db5.x1; SELECT * FROM db4.x1; SELECT * FROM db3.x1; SELECT * FROM db2.x1; SELECT * FROM db1.x1; SELECT * FROM x1; SELECT 'nref=' || nRef, 'nschema=' || nSchema FROM schemapool ORDER BY 1; } { 26 17 18 13 14 15 7 8 9 4 5 6 nref=6 nschema=1 } do_execsql_test -db db2 4.3.6 { SELECT * FROM db5.x1, db4.x1, db1.x1; SELECT 'nref=' || nRef, 'nschema=' || nSchema FROM schemapool ORDER BY 1; } {26 17 18 13 14 15 4 5 6 nref=6 nschema=3} #-------------------------------------------------------------------------- # Test the incremental-blob API with REUSE_SCHEMA connections. # catch {db1 close} catch {db2 close} 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 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 db2 eval "ATTACH 'test.db${i}' AS db${i}" } execsql { SELECT 'nref=' || nRef, 'nschema=' || nSchema FROM schemapool ORDER BY 1; } db2 } {nref=6 nschema=1} do_test 5.1.1 { set res [list] for {set i 1} {$i<6} {incr i} { set chan [db2 incrblob db${i} bbb b 123] lappend res [gets $chan] close $chan } set res } {database_1 database_2 database_3 database_4 database_5} do_execsql_test -db db2 5.1.2 { SELECT 'nref=' || nRef, 'nschema=' || nSchema FROM schemapool ORDER BY 1; } {nref=6 nschema=1} do_test 5.2.1 { sqlite3_table_column_metadata db2 main bbb a } {INTEGER BINARY 0 1 0} do_test 5.2.2 { sqlite3_table_column_metadata db2 main bbb b } {{} BINARY 0 0 0} do_execsql_test -db db2 5.2.3 { SELECT 'nref=' || nRef, 'nschema=' || nSchema FROM schemapool ORDER BY 1; } {nref=6 nschema=1} finish_test