gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/fordelete.test (about) 1 # 2001 September 15 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 # This file implements regression tests for SQLite library. The 12 # focus of this file is testing the SELECT statement. 13 # 14 # $Id: select1.test,v 1.70 2009/05/28 01:00:56 drh Exp $ 15 16 set testdir [file dirname $argv0] 17 source $testdir/tester.tcl 18 set ::testprefix fordelete 19 20 # This function returns a list of the tables or indexes opened with 21 # OP_OpenWrite instructions when the SQL statement passed as the only 22 # argument is executed. If the OPFLAG_FORDELETE flag is specified on 23 # the OP_OpenWrite, an asterix is appended to the object name. The list 24 # is sorted in [lsort] order before it is returned. 25 # 26 proc analyze_delete_program {sql} { 27 # Build a map from root page to table/index name. 28 db eval { 29 SELECT name, rootpage FROM sqlite_master 30 } { 31 set T($rootpage) $name 32 } 33 34 # For each OpenWrite instruction generated for the proposed DELETE 35 # statement, add the following array entries: 36 # 37 # $M(<cursor number>) -> <object name> 38 # $O(<object name>) -> "*" | "" 39 # 40 # The O() entry is set to "*" if the BTREE_FORDELETE flag is specified, 41 # or "" otherwise. 42 # 43 db eval "EXPLAIN $sql" R { 44 if {$R(opcode)=="OpenWrite"} { 45 set root $R(p2) 46 set csr $R(p1) 47 if {[info exists T($root)]} { set M($csr) $T($root) } 48 49 set obj $T($root) 50 set O($obj) "" 51 if {$R(p5) & 0x08} { 52 set O($obj) * 53 } else { 54 set O($obj) "" 55 } 56 } 57 } 58 59 db eval "EXPLAIN $sql" R { 60 if {$R(opcode) == "Delete"} { 61 set csr $R(p1) 62 if {[info exists M($csr)]} { 63 set idxdelete [expr {("0x$R(p5)" & 0x04) ? 1 : 0}] 64 if {$idxdelete} { 65 append O($M($csr)) "+" 66 } 67 } 68 } 69 } 70 71 set res [list] 72 foreach {k v} [array get O] { 73 lappend res "${k}${v}" 74 } 75 76 lsort $res 77 } 78 79 proc do_adp_test {tn sql res} { 80 uplevel [list do_test $tn [list analyze_delete_program $sql] [list {*}$res]] 81 } 82 83 do_execsql_test 1.0 { 84 CREATE TABLE t1(a PRIMARY KEY, b); 85 } 86 87 foreach {tn sql res} { 88 1 { DELETE FROM t1 WHERE a=?} { sqlite_autoindex_t1_1 t1*+ } 89 2 { DELETE FROM t1 WHERE a=? AND b=? } { sqlite_autoindex_t1_1 t1+ } 90 3 { DELETE FROM t1 WHERE a>? } { sqlite_autoindex_t1_1 t1*+ } 91 4 { DELETE FROM t1 WHERE rowid=? } { sqlite_autoindex_t1_1* t1 } 92 } { 93 do_adp_test 1.$tn $sql $res 94 } 95 96 do_execsql_test 2.0 { 97 CREATE TABLE t2(a, b, c); 98 CREATE INDEX t2a ON t2(a); 99 CREATE INDEX t2b ON t2(b); 100 CREATE INDEX t2c ON t2(c); 101 } 102 foreach {tn sql res} { 103 1 { DELETE FROM t2 WHERE a=?} { t2*+ t2a t2b* t2c* } 104 2 { DELETE FROM t2 WHERE a=? AND +b=?} { t2+ t2a t2b* t2c* } 105 3 { DELETE FROM t2 WHERE a=? OR b=?} { t2 t2a* t2b* t2c* } 106 4 { DELETE FROM t2 WHERE +a=? } { t2 t2a* t2b* t2c* } 107 5 { DELETE FROM t2 WHERE rowid=? } { t2 t2a* t2b* t2c* } 108 } { 109 do_adp_test 2.$tn $sql $res 110 } 111 112 #------------------------------------------------------------------------- 113 # Test that a record that consists of the bytes: 114 # 115 # 0x01 0x00 116 # 117 # is interpreted by OP_Column as a vector of NULL values (assuming the 118 # default column values are NULL). Also test that: 119 # 120 # 0x00 121 # 122 # is handled in the same way. 123 # 124 do_execsql_test 3.0 { 125 CREATE TABLE x1(a INTEGER PRIMARY KEY, b, c, d); 126 CREATE TABLE x2(a INTEGER PRIMARY KEY, b, c, d); 127 } 128 129 do_test 3.1 { 130 set root [db one { SELECT rootpage FROM sqlite_master WHERE name = 'x1' }] 131 db eval { 132 BEGIN IMMEDIATE; 133 } 134 set bt [btree_from_db db] 135 set csr [btree_cursor $bt $root 1] 136 btree_insert $csr 5 "\000" 137 btree_close_cursor $csr 138 db eval { COMMIT } 139 140 db eval { 141 SELECT * FROM x1; 142 } 143 } {5 {} {} {}} 144 145 do_test 3.2 { 146 set root [db one { SELECT rootpage FROM sqlite_master WHERE name = 'x2' }] 147 db eval { 148 BEGIN IMMEDIATE; 149 } 150 set bt [btree_from_db db] 151 set csr [btree_cursor $bt $root 1] 152 btree_insert $csr 6 "\000" 153 btree_close_cursor $csr 154 db eval { COMMIT } 155 156 db eval { 157 SELECT * FROM x2; 158 } 159 } {6 {} {} {}} 160 161 162 #------------------------------------------------------------------------- 163 # 164 reset_db 165 do_execsql_test 4.0 { 166 CREATE TABLE log(x); 167 CREATE TABLE p1(one PRIMARY KEY, two); 168 169 CREATE TRIGGER tr_bd BEFORE DELETE ON p1 BEGIN 170 INSERT INTO log VALUES('delete'); 171 END; 172 INSERT INTO p1 VALUES('a', 'A'), ('b', 'B'), ('c', 'C'); 173 DELETE FROM p1 WHERE one = 'a'; 174 } 175 176 reset_db 177 do_execsql_test 4.1 { 178 BEGIN TRANSACTION; 179 CREATE TABLE tbl(a PRIMARY KEY, b, c); 180 CREATE TABLE log(a, b, c); 181 INSERT INTO "tbl" VALUES(1,2,3); 182 CREATE TRIGGER the_trigger BEFORE DELETE ON tbl BEGIN 183 INSERT INTO log VALUES(1, 2,3); 184 END; 185 COMMIT; 186 DELETE FROM tbl WHERE a=1; 187 } 188 189 reset_db 190 do_execsql_test 5.1 { 191 PRAGMA foreign_keys = 1; 192 CREATE TABLE t1(a INTEGER PRIMARY KEY, b); 193 CREATE TABLE t2( 194 c INTEGER PRIMARY KEY, 195 d INTEGER DEFAULT 1 REFERENCES t1 ON DELETE SET DEFAULT 196 ); 197 } {} 198 do_execsql_test 5.2 { 199 INSERT INTO t1 VALUES(1, 'one'); 200 INSERT INTO t1 VALUES(2, 'two'); 201 INSERT INTO t2 VALUES(1, 2); 202 SELECT * FROM t2; 203 } {1 2} 204 do_execsql_test 5.3 { 205 DELETE FROM t1 WHERE a = 2; 206 } {} 207 208 209 finish_test