xref: /sqlite-3.40.0/test/imposter1.test (revision 1ffede8c) 
1# 2015-01-30
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# This file implements tests for SQLite library.
13#
14# The focus of this file is adding extra entries in the symbol table
15# using sqlite3_test_control(SQLITE_TESTCTRL_IMPOSTER) and verifying that
16# SQLite handles those as expected.
17#
18
19set testdir [file dirname $argv0]
20source $testdir/tester.tcl
21set testprefix imposter
22
23# Create a bunch of data to sort against
24#
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 does
38  # not have the indexes
39  #
40  sqlite3_test_control SQLITE_TESTCTRL_IMPOSTER db main 1 $t1_root
41  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_root
45  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 0
49
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 BEGIN
55      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 BEGIN
61      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.rowid
70# column corresponds to t1.rowid and t1.a, but the xt1.a column is always
71# NULL
72#
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 the
83# indexes on t1 nor check the uniqueness constraint on t1.c nor check
84# the NOT NULL constraint on t1.d, resulting in a logically inconsistent
85# database.
86#
87do_execsql_test imposter-1.3 {
88  DELETE FROM xt1 WHERE rowid=5;
89  INSERT INTO xt1(rowid,a,b,c,d) VALUES(99,'hello',1099,2022,NULL);
90  SELECT * FROM chnglog ORDER BY rowid;
91} [list \
92  {DELETE t1: rowid=5, a=NULL, b=1005, c=2005, d=3005} \
93  {INSERT t1:  rowid=99, a='hello', b=1099, c=2022, d=NULL} \
94]
95
96do_execsql_test imposter-1.4a {
97  PRAGMA integrity_check;
98} {/NULL value in t1.d/}
99do_execsql_test imposter-1.4b {
100  PRAGMA integrity_check;
101} {/row # missing from index t1b/}
102do_execsql_test imposter-1.4c {
103  PRAGMA integrity_check;
104} {/row # missing from index t1c/}
105
106# Cleanup the corruption.
107# Then demonstrate that the xt1c imposter table can insert non-unique
108# and NULL values into the UNIQUE index.
109#
110do_execsql_test imposter-2.0 {
111  DELETE FROM t1;
112  WITH RECURSIVE c(i) AS (VALUES(1) UNION ALL SELECT i+1 FROM c WHERE i<10)
113   INSERT INTO t1(a,b,c,d) SELECT i,i,i,i FROM c;
114  UPDATE xt1c SET c=NULL WHERE rowid=5;
115  PRAGMA integrity_check;
116} {/row # missing from index t1c/}
117
118do_execsql_test imposter-2.1 {
119  DELETE FROM t1;
120  WITH RECURSIVE c(i) AS (VALUES(1) UNION ALL SELECT i+1 FROM c WHERE i<10)
121   INSERT INTO t1(a,b,c,d) SELECT i,i,i,i FROM c;
122  UPDATE xt1c SET c=99 WHERE rowid IN (5,7,9);
123  SELECT c FROM t1 ORDER BY c;
124} {1 2 3 4 6 8 10 99 99 99}
125do_execsql_test imposter-2.2 {
126  UPDATE xt1 SET c=99 WHERE rowid IN (5,7,9);
127  PRAGMA integrity_check;
128} {/non-unique entry in index t1c/}
129
130# Erase the imposter tables
131#
132do_test imposter-3.1 {
133  sqlite3_test_control SQLITE_TESTCTRL_IMPOSTER db main 0 1
134  db eval {
135    DELETE FROM t1 WHERE rowid IN (5,7,9);
136    PRAGMA integrity_check;
137  }
138} {ok}
139
140
141finish_test
142