AryaWu/sqlite

1# 2015-01-302#3# The author disclaims copyright to this source code.  In place of4# 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# This file implements tests for SQLite library.13#14# The focus of this file is adding extra entries in the symbol table15# using sqlite3_test_control(SQLITE_TESTCTRL_IMPOSTER) and verifying that16# SQLite handles those as expected.17#18 19set testdir [file dirname $argv0]20source $testdir/tester.tcl21set testprefix imposter22 23# Create a bunch of data to sort against24#25do_test imposter-1.0 {26  execsql {27    CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, d NOT NULL);28    CREATE INDEX t1b ON t1(b);29    CREATE UNIQUE INDEX t1c ON t1(c);30    WITH RECURSIVE c(i) AS (VALUES(1) UNION ALL SELECT i+1 FROM c WHERE i<30)31      INSERT INTO t1(a,b,c,d) SELECT i,1000+i,2000+i,3000+i FROM c;32  }33  set t1_root [db one {SELECT rootpage FROM sqlite_master WHERE name='t1'}]34  set t1b_root [db one {SELECT rootpage FROM sqlite_master WHERE name='t1b'}]35  set t1c_root [db one {SELECT rootpage FROM sqlite_master WHERE name='t1c'}]36 37  # Create an imposter table that uses the same b-tree as t1 but which does38  # not have the indexes39  #40  sqlite3_test_control SQLITE_TESTCTRL_IMPOSTER db main 1 $t1_root41  db eval {CREATE TABLE xt1(a,b,c,d)}42 43  # And create an imposter table for the t1c index.44  sqlite3_test_control SQLITE_TESTCTRL_IMPOSTER db main 1 $t1c_root45  db eval {CREATE TABLE xt1c(c,rowid,PRIMARY KEY(c,rowid))WITHOUT ROWID;}46 47  # Go out of imposter mode for now.48  sqlite3_test_control SQLITE_TESTCTRL_IMPOSTER db main 0 049 50  # Create triggers to record changes to xt1.51  #52  db eval {53    CREATE TEMP TABLE chnglog(desc TEXT);54    CREATE TEMP TRIGGER xt1_del AFTER DELETE ON xt1 BEGIN55      INSERT INTO chnglog VALUES(56           printf('DELETE t1: rowid=%d, a=%s, b=%s, c=%s, d=%s',57                  old.rowid, quote(old.a), quote(old.b), quote(old.c),58                  quote(old.d)));59    END;60    CREATE TEMP TRIGGER xt1_ins AFTER INSERT ON xt1 BEGIN61      INSERT INTO chnglog VALUES(62           printf('INSERT t1:  rowid=%d, a=%s, b=%s, c=%s, d=%s',63                  new.rowid, quote(new.a), quote(new.b), quote(new.c),64                  quote(new.d)));65    END;66  }67} {}68 69# The xt1 table has separate xt1.rowid and xt1.a columns.  The xt1.rowid70# column corresponds to t1.rowid and t1.a, but the xt1.a column is always71# NULL72#73do_execsql_test imposter-1.1 {74  SELECT rowid FROM xt1 WHERE a IS NOT NULL;75} {}76do_execsql_test imposter-1.2 {77  SELECT a,b,c,d FROM t1 EXCEPT SELECT rowid,b,c,d FROM xt1;78  SELECT rowid,b,c,d FROM xt1 EXCEPT SELECT a,b,c,d FROM t1;79} {}80 81 82# Make changes via the xt1 shadow table.  This will not update the83# indexes on t1 nor check the uniqueness constraint on t1.c nor check84# the NOT NULL constraint on t1.d, resulting in a logically inconsistent85# database.86#87do_execsql_test imposter-1.3 {88  PRAGMA writable_schema=on;89  DELETE FROM xt1 WHERE rowid=5;90  INSERT INTO xt1(rowid,a,b,c,d) VALUES(99,'hello',1099,2022,NULL);91  SELECT * FROM chnglog ORDER BY rowid;92} [list \93  {DELETE t1: rowid=5, a=NULL, b=1005, c=2005, d=3005} \94  {INSERT t1:  rowid=99, a='hello', b=1099, c=2022, d=NULL} \95]96 97do_execsql_test imposter-1.4a {98  PRAGMA integrity_check;99} {/NULL value in t1.d/}100do_execsql_test imposter-1.4b {101  PRAGMA integrity_check;102} {/row # missing from index t1b/}103do_execsql_test imposter-1.4c {104  PRAGMA integrity_check;105} {/row # missing from index t1c/}106 107# Cleanup the corruption.108# Then demonstrate that the xt1c imposter table can insert non-unique109# and NULL values into the UNIQUE index.110#111do_execsql_test imposter-2.0 {112  DELETE FROM t1;113  WITH RECURSIVE c(i) AS (VALUES(1) UNION ALL SELECT i+1 FROM c WHERE i<10)114   INSERT INTO t1(a,b,c,d) SELECT i,i,i,i FROM c;115  UPDATE xt1c SET c=NULL WHERE rowid=5;116  PRAGMA integrity_check;117} {/row # missing from index t1c/}118 119do_execsql_test imposter-2.1 {120  DELETE FROM t1;121  WITH RECURSIVE c(i) AS (VALUES(1) UNION ALL SELECT i+1 FROM c WHERE i<10)122   INSERT INTO t1(a,b,c,d) SELECT i,i,i,i FROM c;123  UPDATE xt1c SET c=99 WHERE rowid IN (5,7,9);124  SELECT c FROM t1 ORDER BY c;125} {1 2 3 4 6 8 10 99 99 99}126do_execsql_test imposter-2.2 {127  UPDATE xt1 SET c=99 WHERE rowid IN (5,7,9);128  PRAGMA integrity_check;129} {/non-unique entry in index t1c/}130 131# Erase the imposter tables132#133do_test imposter-3.1 {134  sqlite3_test_control SQLITE_TESTCTRL_IMPOSTER db main 0 1135  db eval {136    DELETE FROM t1 WHERE rowid IN (5,7,9);137    PRAGMA integrity_check;138  }139} {ok}140 141 142finish_test143