| 1 |
# 2018 September 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 |
|
| 13 |
set testdir [file dirname $argv0] |
| 14 |
source $testdir/tester.tcl |
| 15 |
set testprefix altertab2 |
| 16 |
|
| 17 |
# If SQLITE_OMIT_ALTERTABLE is defined, omit this file. |
| 18 |
ifcapable !altertable { |
| 19 |
finish_test |
| 20 |
return |
| 21 |
} |
| 22 |
|
| 23 |
ifcapable fts5 { |
| 24 |
do_execsql_test 1.0 { |
| 25 |
CREATE TABLE rr(a, b); |
| 26 |
CREATE VIRTUAL TABLE ff USING fts5(a, b); |
| 27 |
CREATE TRIGGER tr1 AFTER INSERT ON rr BEGIN |
| 28 |
INSERT INTO ff VALUES(new.a, new.b); |
| 29 |
END; |
| 30 |
INSERT INTO rr VALUES('hello', 'world'); |
| 31 |
SELECT * FROM ff; |
| 32 |
} {hello world} |
| 33 |
|
| 34 |
do_execsql_test 1.1 { |
| 35 |
ALTER TABLE ff RENAME TO ffff; |
| 36 |
} |
| 37 |
|
| 38 |
do_execsql_test 1.2 { |
| 39 |
INSERT INTO rr VALUES('in', 'tcl'); |
| 40 |
SELECT * FROM ffff; |
| 41 |
} {hello world in tcl} |
| 42 |
} |
| 43 |
|
| 44 |
#------------------------------------------------------------------------- |
| 45 |
# Check that table names that appear in REFERENCES clauses are updated |
| 46 |
# when a table is renamed unless: |
| 47 |
# |
| 48 |
# a) "PRAGMA legacy_alter_table" is true, and |
| 49 |
# b) "PRAGMA foreign_keys" is false. |
| 50 |
# |
| 51 |
do_execsql_test 2.0 { |
| 52 |
CREATE TABLE p1(a PRIMARY KEY, b); |
| 53 |
CREATE TABLE c1(x REFERENCES p1); |
| 54 |
CREATE TABLE c2(x, FOREIGN KEY (x) REFERENCES p1); |
| 55 |
CREATE TABLE c3(x, FOREIGN KEY (x) REFERENCES p1(a)); |
| 56 |
} |
| 57 |
|
| 58 |
do_execsql_test 2.1 { |
| 59 |
ALTER TABLE p1 RENAME TO p2; |
| 60 |
SELECT sql FROM sqlite_master WHERE name LIKE 'c%'; |
| 61 |
} { |
| 62 |
{CREATE TABLE c1(x REFERENCES "p2")} |
| 63 |
{CREATE TABLE c2(x, FOREIGN KEY (x) REFERENCES "p2")} |
| 64 |
{CREATE TABLE c3(x, FOREIGN KEY (x) REFERENCES "p2"(a))} |
| 65 |
} |
| 66 |
|
| 67 |
do_execsql_test 2.2 { |
| 68 |
PRAGMA legacy_alter_table = 1; |
| 69 |
ALTER TABLE p2 RENAME TO p3; |
| 70 |
SELECT sql FROM sqlite_master WHERE name LIKE 'c%'; |
| 71 |
} { |
| 72 |
{CREATE TABLE c1(x REFERENCES "p2")} |
| 73 |
{CREATE TABLE c2(x, FOREIGN KEY (x) REFERENCES "p2")} |
| 74 |
{CREATE TABLE c3(x, FOREIGN KEY (x) REFERENCES "p2"(a))} |
| 75 |
} |
| 76 |
|
| 77 |
do_execsql_test 2.3 { |
| 78 |
ALTER TABLE p3 RENAME TO p2; |
| 79 |
PRAGMA foreign_keys = 1; |
| 80 |
ALTER TABLE p2 RENAME TO p3; |
| 81 |
SELECT sql FROM sqlite_master WHERE name LIKE 'c%'; |
| 82 |
} { |
| 83 |
{CREATE TABLE c1(x REFERENCES "p3")} |
| 84 |
{CREATE TABLE c2(x, FOREIGN KEY (x) REFERENCES "p3")} |
| 85 |
{CREATE TABLE c3(x, FOREIGN KEY (x) REFERENCES "p3"(a))} |
| 86 |
} |
| 87 |
|
| 88 |
#------------------------------------------------------------------------- |
| 89 |
# Table name in WITH clauses that are part of views or triggers. |
| 90 |
# |
| 91 |
foreach {tn schema} { |
| 92 |
1 { |
| 93 |
CREATE TABLE log_entry(col1, y); |
| 94 |
CREATE INDEX i1 ON log_entry(col1); |
| 95 |
} |
| 96 |
|
| 97 |
2 { |
| 98 |
CREATE TABLE t1(a, b, c); |
| 99 |
CREATE TABLE t2(x); |
| 100 |
CREATE TABLE log_entry(col1); |
| 101 |
CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN |
| 102 |
INSERT INTO t2 SELECT col1 FROM log_entry; |
| 103 |
END; |
| 104 |
} |
| 105 |
|
| 106 |
3 { |
| 107 |
CREATE TABLE t1(a, b, c); |
| 108 |
CREATE TABLE t2(x); |
| 109 |
CREATE TABLE log_entry(col1); |
| 110 |
CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN |
| 111 |
INSERT INTO t2 |
| 112 |
WITH xyz(x) AS (SELECT col1 FROM log_entry) |
| 113 |
SELECT x FROM xyz; |
| 114 |
END; |
| 115 |
} |
| 116 |
|
| 117 |
4 { |
| 118 |
CREATE TABLE log_entry(col1); |
| 119 |
CREATE VIEW ttt AS |
| 120 |
WITH xyz(x) AS (SELECT col1 FROM log_entry) |
| 121 |
SELECT x FROM xyz; |
| 122 |
} |
| 123 |
} { |
| 124 |
reset_db |
| 125 |
do_execsql_test 3.$tn.1 $schema |
| 126 |
set expect [db eval "SELECT sql FROM sqlite_master"] |
| 127 |
set expect [string map {log_entry {"newname"}} $expect] |
| 128 |
|
| 129 |
do_execsql_test 3.$tn.2 { |
| 130 |
ALTER TABLE log_entry RENAME TO newname; |
| 131 |
SELECT sql FROM sqlite_master; |
| 132 |
} $expect |
| 133 |
|
| 134 |
reset_db |
| 135 |
do_execsql_test 3.$tn.3 $schema |
| 136 |
set expect [db eval "SELECT sql FROM sqlite_master"] |
| 137 |
set expect [string map {col1 newname} $expect] |
| 138 |
|
| 139 |
do_execsql_test 3.$tn.4 { |
| 140 |
ALTER TABLE log_entry RENAME col1 TO newname; |
| 141 |
SELECT sql FROM sqlite_master; |
| 142 |
} $expect |
| 143 |
} |
| 144 |
|
| 145 |
#------------------------------------------------------------------------- |
| 146 |
reset_db |
| 147 |
do_execsql_test 4.0 { |
| 148 |
CREATE TABLE t1(a,b,c,d,e,f); |
| 149 |
CREATE TRIGGER r1 AFTER INSERT ON t1 WHEN new.a NOT NULL BEGIN |
| 150 |
UPDATE t1 SET (c,d)=(a,b); |
| 151 |
END; |
| 152 |
} |
| 153 |
|
| 154 |
do_execsql_test 4.1 { |
| 155 |
ALTER TABLE t1 RENAME TO t1x; |
| 156 |
SELECT sql FROM sqlite_master WHERE type = 'trigger'; |
| 157 |
} { |
| 158 |
{CREATE TRIGGER r1 AFTER INSERT ON "t1x" WHEN new.a NOT NULL BEGIN |
| 159 |
UPDATE "t1x" SET (c,d)=(a,b); |
| 160 |
END} |
| 161 |
} |
| 162 |
|
| 163 |
do_execsql_test 4.2 { |
| 164 |
ALTER TABLE t1x RENAME a TO aaa; |
| 165 |
SELECT sql FROM sqlite_master WHERE type = 'trigger'; |
| 166 |
} { |
| 167 |
{CREATE TRIGGER r1 AFTER INSERT ON "t1x" WHEN new.aaa NOT NULL BEGIN |
| 168 |
UPDATE "t1x" SET (c,d)=(aaa,b); |
| 169 |
END} |
| 170 |
} |
| 171 |
|
| 172 |
do_execsql_test 4.3 { |
| 173 |
ALTER TABLE t1x RENAME d TO ddd; |
| 174 |
SELECT sql FROM sqlite_master WHERE type = 'trigger'; |
| 175 |
} { |
| 176 |
{CREATE TRIGGER r1 AFTER INSERT ON "t1x" WHEN new.aaa NOT NULL BEGIN |
| 177 |
UPDATE "t1x" SET (c,ddd)=(aaa,b); |
| 178 |
END} |
| 179 |
} |
| 180 |
|
| 181 |
#------------------------------------------------------------------------- |
| 182 |
ifcapable windowfunc { |
| 183 |
do_execsql_test 5.0 { |
| 184 |
CREATE TABLE t2(a); |
| 185 |
CREATE TRIGGER r2 AFTER INSERT ON t2 WHEN new.a NOT NULL BEGIN |
| 186 |
SELECT a, sum(a) OVER w1 FROM t2 |
| 187 |
WINDOW w1 AS ( |
| 188 |
PARTITION BY a ORDER BY a |
| 189 |
ROWS BETWEEN 2 PRECEDING AND 3 FOLLOWING |
| 190 |
), |
| 191 |
w2 AS ( |
| 192 |
PARTITION BY a |
| 193 |
ORDER BY rowid ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING |
| 194 |
); |
| 195 |
END; |
| 196 |
} {} |
| 197 |
|
| 198 |
do_execsql_test 5.0.1 { |
| 199 |
INSERT INTO t2 VALUES(1); |
| 200 |
} {} |
| 201 |
|
| 202 |
do_execsql_test 5.1 { |
| 203 |
ALTER TABLE t2 RENAME TO t2x; |
| 204 |
SELECT sql FROM sqlite_master WHERE name = 'r2'; |
| 205 |
} { |
| 206 |
{CREATE TRIGGER r2 AFTER INSERT ON "t2x" WHEN new.a NOT NULL BEGIN |
| 207 |
SELECT a, sum(a) OVER w1 FROM "t2x" |
| 208 |
WINDOW w1 AS ( |
| 209 |
PARTITION BY a ORDER BY a |
| 210 |
ROWS BETWEEN 2 PRECEDING AND 3 FOLLOWING |
| 211 |
), |
| 212 |
w2 AS ( |
| 213 |
PARTITION BY a |
| 214 |
ORDER BY rowid ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING |
| 215 |
); |
| 216 |
END} |
| 217 |
} |
| 218 |
|
| 219 |
do_execsql_test 5.2 { |
| 220 |
ALTER TABLE t2x RENAME a TO aaaa; |
| 221 |
SELECT sql FROM sqlite_master WHERE name = 'r2'; |
| 222 |
} { |
| 223 |
{CREATE TRIGGER r2 AFTER INSERT ON "t2x" WHEN new.aaaa NOT NULL BEGIN |
| 224 |
SELECT aaaa, sum(aaaa) OVER w1 FROM "t2x" |
| 225 |
WINDOW w1 AS ( |
| 226 |
PARTITION BY aaaa ORDER BY aaaa |
| 227 |
ROWS BETWEEN 2 PRECEDING AND 3 FOLLOWING |
| 228 |
), |
| 229 |
w2 AS ( |
| 230 |
PARTITION BY aaaa |
| 231 |
ORDER BY rowid ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING |
| 232 |
); |
| 233 |
END} |
| 234 |
} |
| 235 |
|
| 236 |
do_execsql_test 5.3 { |
| 237 |
INSERT INTO t2x VALUES(1); |
| 238 |
} {} |
| 239 |
} ;# windowfunc |
| 240 |
|
| 241 |
#------------------------------------------------------------------------- |
| 242 |
|
| 243 |
do_execsql_test 6.0 { |
| 244 |
CREATE TABLE t3(a,b,c,d); |
| 245 |
CREATE TRIGGER r3 AFTER INSERT ON t3 WHEN new.a NOT NULL BEGIN |
| 246 |
SELECT a,b,c FROM t3 EXCEPT SELECT a,b,c FROM t3 ORDER BY a; |
| 247 |
SELECT rowid, * FROM t3; |
| 248 |
END; |
| 249 |
} {} |
| 250 |
|
| 251 |
do_execsql_test 6.1 { |
| 252 |
ALTER TABLE t3 RENAME TO t3x; |
| 253 |
SELECT sql FROM sqlite_master WHERE name = 'r3'; |
| 254 |
} { |
| 255 |
{CREATE TRIGGER r3 AFTER INSERT ON "t3x" WHEN new.a NOT NULL BEGIN |
| 256 |
SELECT a,b,c FROM "t3x" EXCEPT SELECT a,b,c FROM "t3x" ORDER BY a; |
| 257 |
SELECT rowid, * FROM "t3x"; |
| 258 |
END} |
| 259 |
} |
| 260 |
|
| 261 |
do_execsql_test 6.2 { |
| 262 |
ALTER TABLE t3x RENAME a TO abcd; |
| 263 |
SELECT sql FROM sqlite_master WHERE name = 'r3'; |
| 264 |
} { |
| 265 |
{CREATE TRIGGER r3 AFTER INSERT ON "t3x" WHEN new.abcd NOT NULL BEGIN |
| 266 |
SELECT abcd,b,c FROM "t3x" EXCEPT SELECT abcd,b,c FROM "t3x" ORDER BY abcd; |
| 267 |
SELECT rowid, * FROM "t3x"; |
| 268 |
END} |
| 269 |
} |
| 270 |
|
| 271 |
#------------------------------------------------------------------------- |
| 272 |
reset_db |
| 273 |
|
| 274 |
do_execsql_test 7.0 { |
| 275 |
CREATE TABLE t1(a,b,c,d,e,f); |
| 276 |
INSERT INTO t1 VALUES(1,2,3,4,5,6); |
| 277 |
CREATE TABLE t2(x,y,z); |
| 278 |
} |
| 279 |
|
| 280 |
do_execsql_test 7.1 { |
| 281 |
SELECT a,b,c FROM t1 UNION SELECT d,e,f FROM t1 ORDER BY b,c; |
| 282 |
} {1 2 3 4 5 6} |
| 283 |
|
| 284 |
do_execsql_test 7.2 { |
| 285 |
CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN |
| 286 |
INSERT INTO t2 |
| 287 |
SELECT a,b,c FROM t1 UNION SELECT d,e,f FROM t1 ORDER BY b,c; |
| 288 |
END; |
| 289 |
INSERT INTO t1 VALUES(2,3,4,5,6,7); |
| 290 |
SELECT * FROM t2; |
| 291 |
} {1 2 3 2 3 4 4 5 6 5 6 7} |
| 292 |
|
| 293 |
do_execsql_test 7.3 { |
| 294 |
ALTER TABLE t1 RENAME TO xyzzy; |
| 295 |
SELECT sql FROM sqlite_master WHERE name='r1' |
| 296 |
} { |
| 297 |
{CREATE TRIGGER r1 AFTER INSERT ON "xyzzy" BEGIN |
| 298 |
INSERT INTO t2 |
| 299 |
SELECT a,b,c FROM "xyzzy" UNION SELECT d,e,f FROM "xyzzy" ORDER BY b,c; |
| 300 |
END} |
| 301 |
} |
| 302 |
|
| 303 |
do_execsql_test 7.3 { |
| 304 |
ALTER TABLE xyzzy RENAME c TO ccc; |
| 305 |
SELECT sql FROM sqlite_master WHERE name='r1' |
| 306 |
} { |
| 307 |
{CREATE TRIGGER r1 AFTER INSERT ON "xyzzy" BEGIN |
| 308 |
INSERT INTO t2 |
| 309 |
SELECT a,b,ccc FROM "xyzzy" UNION SELECT d,e,f FROM "xyzzy" ORDER BY b,ccc; |
| 310 |
END} |
| 311 |
} |
| 312 |
|
| 313 |
#------------------------------------------------------------------------- |
| 314 |
reset_db |
| 315 |
do_execsql_test 8.0 { |
| 316 |
CREATE TABLE t1(a, b, c); |
| 317 |
CREATE TABLE t2(a, b, c); |
| 318 |
CREATE TABLE t3(d, e, f); |
| 319 |
CREATE VIEW v1 AS SELECT * FROM t1; |
| 320 |
CREATE TRIGGER tr AFTER INSERT ON t3 BEGIN |
| 321 |
UPDATE t2 SET a = new.d; |
| 322 |
SELECT a, b, c FROM v1; |
| 323 |
END; |
| 324 |
} |
| 325 |
|
| 326 |
do_execsql_test 8.1 { |
| 327 |
INSERT INTO t3 VALUES(1, 2, 3); |
| 328 |
} |
| 329 |
|
| 330 |
# The following ALTER TABLE fails as if column "t1.a" is renamed the "a" |
| 331 |
# in the "SELECT a, b, c FROM v1" within the trigger can no longer be |
| 332 |
# resolved. But at one point there was a bug allowing the ALTER TABLE |
| 333 |
# succeed. Which meant the subsequent INSERT statement would fail. |
| 334 |
do_catchsql_test 8.2 { |
| 335 |
ALTER TABLE t1 RENAME a TO aaa; |
| 336 |
} {1 {error in trigger tr after rename: no such column: a}} |
| 337 |
do_execsql_test 8.3 { |
| 338 |
INSERT INTO t3 VALUES(4, 5, 6); |
| 339 |
} |
| 340 |
|
| 341 |
do_execsql_test 8.4 { |
| 342 |
CREATE TABLE t4(a, b); |
| 343 |
CREATE VIEW v4 AS SELECT * FROM t4 WHERE (a=1 AND 0) OR b=2; |
| 344 |
} |
| 345 |
|
| 346 |
# Branches of an expression tree that are optimized out by the AND |
| 347 |
# optimization are renamed. |
| 348 |
# |
| 349 |
do_execsql_test 8.5 { |
| 350 |
ALTER TABLE t4 RENAME a TO c; |
| 351 |
SELECT sql FROM sqlite_master WHERE name = 'v4' |
| 352 |
} {{CREATE VIEW v4 AS SELECT * FROM t4 WHERE (c=1 AND 0) OR b=2}} |
| 353 |
|
| 354 |
# 2019-06-10 https://sqlite.org/src/info/533010b8cacebe82 |
| 355 |
reset_db |
| 356 |
do_catchsql_test 8.6 { |
| 357 |
CREATE TABLE t0(c0); |
| 358 |
CREATE INDEX i0 ON t0(likelihood(1,2) AND 0); |
| 359 |
ALTER TABLE t0 RENAME TO t1; |
| 360 |
SELECT sql FROM sqlite_master WHERE name='i0'; |
| 361 |
} {1 {second argument to likelihood() must be a constant between 0.0 and 1.0}} |
| 362 |
|
| 363 |
|
| 364 |
reset_db |
| 365 |
|
| 366 |
do_execsql_test 9.0 { |
| 367 |
CREATE TABLE t1(a,b,c,d); |
| 368 |
CREATE TABLE t2(a,b,c,d,x); |
| 369 |
|
| 370 |
CREATE TRIGGER AFTER INSERT ON t2 BEGIN |
| 371 |
|
| 372 |
SELECT group_conct( |
| 373 |
123 ORDER BY ( |
| 374 |
SELECT 1 FROM ( VALUES(a, 'b'), ('c') ) |
| 375 |
)) |
| 376 |
FROM t1; |
| 377 |
|
| 378 |
END; |
| 379 |
} |
| 380 |
|
| 381 |
do_catchsql_test 9.1 { |
| 382 |
ALTER TABLE t2 RENAME TO newname; |
| 383 |
} {1 {error in trigger AFTER: all VALUES must have the same number of terms}} |
| 384 |
|
| 385 |
finish_test |