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 16set testdir [file dirname $argv0] 17source $testdir/tester.tcl 18set ::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# 26proc 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 79proc do_adp_test {tn sql res} { 80 uplevel [list do_test $tn [list analyze_delete_program $sql] [list {*}$res]] 81} 82 83do_execsql_test 1.0 { 84 CREATE TABLE t1(a PRIMARY KEY, b); 85} 86 87foreach {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 96do_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} 102foreach {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# 124do_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 129do_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 145do_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# 164reset_db 165do_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 176reset_db 177do_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 189reset_db 190do_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} {} 198do_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} 204do_execsql_test 5.3 { 205 DELETE FROM t1 WHERE a = 2; 206} {} 207 208 209finish_test 210