| 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 |
|
| 19 |
set testdir [file dirname $argv0] |
| 20 |
source $testdir/tester.tcl |
| 21 |
set testprefix imposter |
| 22 |
|
| 23 |
# Create a bunch of data to sort against |
| 24 |
# |
| 25 |
do_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 |
# |
| 73 |
do_execsql_test imposter-1.1 { |
| 74 |
SELECT rowid FROM xt1 WHERE a IS NOT NULL; |
| 75 |
} {} |
| 76 |
do_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 |
# |
| 87 |
do_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 |
|
| 97 |
do_execsql_test imposter-1.4a { |
| 98 |
PRAGMA integrity_check; |
| 99 |
} {/NULL value in t1.d/} |
| 100 |
do_execsql_test imposter-1.4b { |
| 101 |
PRAGMA integrity_check; |
| 102 |
} {/row # missing from index t1b/} |
| 103 |
do_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-unique |
| 109 |
# and NULL values into the UNIQUE index. |
| 110 |
# |
| 111 |
do_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 |
|
| 119 |
do_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} |
| 126 |
do_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 tables |
| 132 |
# |
| 133 |
do_test imposter-3.1 { |
| 134 |
sqlite3_test_control SQLITE_TESTCTRL_IMPOSTER db main 0 1 |
| 135 |
db eval { |
| 136 |
DELETE FROM t1 WHERE rowid IN (5,7,9); |
| 137 |
PRAGMA integrity_check; |
| 138 |
} |
| 139 |
} {ok} |
| 140 |
|
| 141 |
|
| 142 |
finish_test |