gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/altertrig.test (about) 1 # 2022 May 27 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 13 set testdir [file dirname $argv0] 14 source $testdir/tester.tcl 15 set testprefix altertrig 16 17 # If SQLITE_OMIT_ALTERTABLE is defined, omit this file. 18 ifcapable !altertable { 19 finish_test 20 return 21 } 22 23 proc collapse_whitespace {in} { 24 regsub -all {[ \t\n]+} [string trim $in] { } 25 } 26 27 proc do_whitespace_sql_test {tn sql res} { 28 set got [execsql $sql] 29 set wgot [list] 30 set wres [list] 31 foreach g $got { lappend wgot [collapse_whitespace $g] } 32 foreach r $res { lappend wres [collapse_whitespace $r] } 33 34 uplevel [list do_test $tn [list set {} $wgot] $wres] 35 } 36 37 do_execsql_test 1.0 { 38 CREATE TABLE t1(x); 39 CREATE TABLE t2(y); 40 CREATE TABLE t3(z); 41 CREATE TABLE t4(a); 42 43 CREATE TRIGGER r1 INSERT ON t1 BEGIN 44 UPDATE t1 SET d='xyz' FROM t2, t3; 45 END; 46 } 47 48 do_whitespace_sql_test 1.1 { 49 ALTER TABLE t3 RENAME TO t5; 50 SELECT sql FROM sqlite_schema WHERE type='trigger'; 51 } {{ 52 CREATE TRIGGER r1 INSERT ON t1 BEGIN 53 UPDATE t1 SET d='xyz' FROM t2, "t5"; 54 END 55 }} 56 57 do_execsql_test 1.2 { 58 DROP TRIGGER r1; 59 CREATE TRIGGER r1 INSERT ON t1 BEGIN 60 UPDATE t1 SET d='xyz' FROM t2, (SELECT * FROM t5); 61 END; 62 } 63 64 do_whitespace_sql_test 1.3 { 65 ALTER TABLE t5 RENAME TO t3; 66 SELECT sql FROM sqlite_schema WHERE type='trigger'; 67 } {{ 68 CREATE TRIGGER r1 INSERT ON t1 BEGIN 69 UPDATE t1 SET d='xyz' FROM t2, (SELECT * FROM "t3"); 70 END 71 }} 72 73 foreach {tn alter update final} { 74 1 { 75 ALTER TABLE t3 RENAME TO t10 76 } { 77 UPDATE t1 SET d='xyz' FROM t2, (SELECT * FROM t3) 78 } { 79 UPDATE t1 SET d='xyz' FROM t2, (SELECT * FROM "t10") 80 } 81 82 2 { 83 ALTER TABLE t3 RENAME TO t10 84 } { 85 UPDATE t1 SET a='xyz' FROM t3, (SELECT * FROM (SELECT e FROM t3)) 86 } { 87 UPDATE t1 SET a='xyz' FROM "t10", (SELECT * FROM (SELECT e FROM "t10")) 88 } 89 90 3 { 91 ALTER TABLE t3 RENAME e TO abc 92 } { 93 UPDATE t1 SET a='xyz' FROM t3, (SELECT * FROM (SELECT e FROM t3)) 94 } { 95 UPDATE t1 SET a='xyz' FROM t3, (SELECT * FROM (SELECT abc FROM t3)) 96 } 97 98 4 { 99 ALTER TABLE t2 RENAME c TO abc 100 } { 101 UPDATE t1 SET a='xyz' FROM t3, (SELECT 1 FROM t2 WHERE c) 102 } { 103 UPDATE t1 SET a='xyz' FROM t3, (SELECT 1 FROM t2 WHERE abc) 104 } 105 106 5 { 107 ALTER TABLE t2 RENAME c TO abc 108 } { 109 UPDATE t1 SET a=t2.c FROM t2 110 } { 111 UPDATE t1 SET a=t2.abc FROM t2 112 } 113 114 6 { 115 ALTER TABLE t2 RENAME c TO abc 116 } { 117 UPDATE t1 SET a=t2.c FROM t2, t3 118 } { 119 UPDATE t1 SET a=t2.abc FROM t2, t3 120 } 121 122 7 { 123 ALTER TABLE t4 RENAME e TO abc 124 } { 125 UPDATE t1 SET a=1 FROM t3 NATURAL JOIN t4 WHERE t4.e=a 126 } { 127 UPDATE t1 SET a=1 FROM t3 NATURAL JOIN t4 WHERE t4.abc=a 128 } 129 130 8 { 131 ALTER TABLE t4 RENAME TO abc 132 } { 133 UPDATE t1 SET a=1 FROM t3 NATURAL JOIN t4 WHERE t4.e=a 134 } { 135 UPDATE t1 SET a=1 FROM t3 NATURAL JOIN "abc" WHERE "abc".e=a 136 } 137 138 } { 139 reset_db 140 do_execsql_test 2.$tn.1 { 141 CREATE TABLE t1(a,b); 142 CREATE TABLE t2(c,d); 143 CREATE TABLE t3(e,f); 144 CREATE TABLE t4(e,f); 145 } 146 do_execsql_test 2.$tn.2 " 147 CREATE TRIGGER r1 INSERT ON t1 BEGIN 148 $update; 149 END 150 " 151 do_execsql_test 2.$tn.3 $alter 152 153 do_whitespace_sql_test 2.$tn.4 { 154 SELECT sqL FROM sqlite_schema WHERE type='trigger' 155 } "{ 156 CREATE TRIGGER r1 INSERT ON t1 BEGIN 157 $final; 158 END 159 }" 160 } 161 162 finish_test 163