1# 2009 February 2 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 script is testing that SQLite can handle a subtle 13# file format change that may be used in the future to implement 14# "ALTER TABLE ... RENAME COLUMN ... TO". 15# 16# $Id: alter4.test,v 1.1 2009/02/02 18:03:22 drh Exp $ 17# 18 19set testdir [file dirname $argv0] 20source $testdir/tester.tcl 21set testprefix altercol 22 23# If SQLITE_OMIT_ALTERTABLE is defined, omit this file. 24ifcapable !altertable { 25 finish_test 26 return 27} 28 29# Drop all the tables and views in the 'main' database of database connect 30# [db]. Sort the objects by name before dropping them. 31# 32proc drop_all_tables_and_views {db} { 33 set SQL { 34 SELECT name, type FROM sqlite_master 35 WHERE type IN ('table', 'view') AND name NOT LIKE 'sqlite_%' 36 ORDER BY 1 37 } 38 foreach {z t} [db eval $SQL] { 39 db eval "DROP $t $z" 40 } 41} 42 43foreach {tn before after} { 44 1 {CREATE TABLE t1(a INTEGER, b TEXT, c BLOB)} 45 {CREATE TABLE t1(a INTEGER, d TEXT, c BLOB)} 46 47 2 {CREATE TABLE t1(a INTEGER, x TEXT, "b" BLOB)} 48 {CREATE TABLE t1(a INTEGER, x TEXT, "d" BLOB)} 49 50 3 {CREATE TABLE t1(a INTEGER, b TEXT, c BLOB, CHECK(b!=''))} 51 {CREATE TABLE t1(a INTEGER, d TEXT, c BLOB, CHECK(d!=''))} 52 53 4 {CREATE TABLE t1(a INTEGER, b TEXT, c BLOB, CHECK(t1.b!=''))} 54 {CREATE TABLE t1(a INTEGER, d TEXT, c BLOB, CHECK(t1.d!=''))} 55 56 5 {CREATE TABLE t1(a INTEGER, b TEXT, c BLOB, CHECK( coalesce(b,c) ))} 57 {CREATE TABLE t1(a INTEGER, d TEXT, c BLOB, CHECK( coalesce(d,c) ))} 58 59 6 {CREATE TABLE t1(a INTEGER, "b"TEXT, c BLOB, CHECK( coalesce(b,c) ))} 60 {CREATE TABLE t1(a INTEGER, "d"TEXT, c BLOB, CHECK( coalesce(d,c) ))} 61 62 7 {CREATE TABLE t1(a INTEGER, b TEXT, c BLOB, PRIMARY KEY(b, c))} 63 {CREATE TABLE t1(a INTEGER, d TEXT, c BLOB, PRIMARY KEY(d, c))} 64 65 8 {CREATE TABLE t1(a INTEGER, b TEXT PRIMARY KEY, c BLOB)} 66 {CREATE TABLE t1(a INTEGER, d TEXT PRIMARY KEY, c BLOB)} 67 68 9 {CREATE TABLE t1(a, b TEXT, c, PRIMARY KEY(a, b), UNIQUE("B"))} 69 {CREATE TABLE t1(a, d TEXT, c, PRIMARY KEY(a, d), UNIQUE("d"))} 70 71 10 {CREATE TABLE t1(a, b, c); CREATE INDEX t1i ON t1(a, c)} 72 {{CREATE TABLE t1(a, d, c)} {CREATE INDEX t1i ON t1(a, c)}} 73 74 11 {CREATE TABLE t1(a, b, c); CREATE INDEX t1i ON t1(b, c)} 75 {{CREATE TABLE t1(a, d, c)} {CREATE INDEX t1i ON t1(d, c)}} 76 77 12 {CREATE TABLE t1(a, b, c); CREATE INDEX t1i ON t1(b+b+b+b, c) WHERE b>0} 78 {{CREATE TABLE t1(a, d, c)} {CREATE INDEX t1i ON t1(d+d+d+d, c) WHERE d>0}} 79 80 13 {CREATE TABLE t1(a, b, c, FOREIGN KEY (b) REFERENCES t2)} 81 {CREATE TABLE t1(a, d, c, FOREIGN KEY (d) REFERENCES t2)} 82 83 14 {CREATE TABLE t1(a INTEGER, b TEXT, c BLOB, PRIMARY KEY(b))} 84 {CREATE TABLE t1(a INTEGER, d TEXT, c BLOB, PRIMARY KEY(d))} 85 86 15 {CREATE TABLE t1(a INTEGER, b INTEGER, c BLOB, PRIMARY KEY(b))} 87 {CREATE TABLE t1(a INTEGER, d INTEGER, c BLOB, PRIMARY KEY(d))} 88 89 16 {CREATE TABLE t1(a INTEGER, b INTEGER PRIMARY KEY, c BLOB)} 90 {CREATE TABLE t1(a INTEGER, d INTEGER PRIMARY KEY, c BLOB)} 91 92 17 {CREATE TABLE t1(a INTEGER, b INTEGER PRIMARY KEY, c BLOB, FOREIGN KEY (b) REFERENCES t2)} 93 {CREATE TABLE t1(a INTEGER, d INTEGER PRIMARY KEY, c BLOB, FOREIGN KEY (d) REFERENCES t2)} 94 95} { 96 reset_db 97 do_execsql_test 1.$tn.0 $before 98 99 do_execsql_test 1.$tn.1 { 100 INSERT INTO t1 VALUES(1, 2, 3); 101 } 102 103 do_execsql_test 1.$tn.2 { 104 ALTER TABLE t1 RENAME COLUMN b TO d; 105 } 106 107 do_execsql_test 1.$tn.3 { 108 SELECT * FROM t1; 109 } {1 2 3} 110 111 if {[string first INDEX $before]>0} { 112 set res $after 113 } else { 114 set res [list $after] 115 } 116 do_execsql_test 1.$tn.4 { 117 SELECT sql FROM sqlite_master WHERE tbl_name='t1' AND sql!='' 118 } $res 119} 120 121#------------------------------------------------------------------------- 122# 123do_execsql_test 2.0 { 124 CREATE TABLE t3(a, b, c, d, e, f, g, h, i, j, k, l, m, FOREIGN KEY (b, c, d, e, f, g, h, i, j, k, l, m) REFERENCES t4); 125} 126 127sqlite3 db2 test.db 128do_execsql_test -db db2 2.1 { SELECT b FROM t3 } 129 130do_execsql_test 2.2 { 131 ALTER TABLE t3 RENAME b TO biglongname; 132 SELECT sql FROM sqlite_master WHERE name='t3'; 133} {{CREATE TABLE t3(a, biglongname, c, d, e, f, g, h, i, j, k, l, m, FOREIGN KEY (biglongname, c, d, e, f, g, h, i, j, k, l, m) REFERENCES t4)}} 134 135do_execsql_test -db db2 2.3 { SELECT biglongname FROM t3 } 136 137#------------------------------------------------------------------------- 138# 139do_execsql_test 3.0 { 140 CREATE TABLE t4(x, y, z); 141 CREATE TRIGGER ttt AFTER INSERT ON t4 WHEN new.y<0 BEGIN 142 SELECT x, y, z FROM t4; 143 DELETE FROM t4 WHERE y=32; 144 UPDATE t4 SET x=y+1, y=0 WHERE y=32; 145 INSERT INTO t4(x, y, z) SELECT 4, 5, 6 WHERE 0; 146 END; 147 INSERT INTO t4 VALUES(3, 2, 1); 148} 149 150do_execsql_test 3.1 { 151 ALTER TABLE t4 RENAME y TO abc; 152 SELECT sql FROM sqlite_master WHERE name='t4'; 153} {{CREATE TABLE t4(x, abc, z)}} 154 155do_execsql_test 3.2 { 156 SELECT * FROM t4; 157} {3 2 1} 158 159do_execsql_test 3.3 { INSERT INTO t4 VALUES(6, 5, 4); } {} 160 161do_execsql_test 3.4 { SELECT sql FROM sqlite_master WHERE type='trigger' } { 162{CREATE TRIGGER ttt AFTER INSERT ON t4 WHEN new.abc<0 BEGIN 163 SELECT x, abc, z FROM t4; 164 DELETE FROM t4 WHERE abc=32; 165 UPDATE t4 SET x=abc+1, abc=0 WHERE abc=32; 166 INSERT INTO t4(x, abc, z) SELECT 4, 5, 6 WHERE 0; 167 END} 168} 169 170#------------------------------------------------------------------------- 171# 172do_execsql_test 4.0 { 173 CREATE TABLE c1(a, b, FOREIGN KEY (a, b) REFERENCES p1(c, d)); 174 CREATE TABLE p1(c, d, PRIMARY KEY(c, d)); 175 PRAGMA foreign_keys = 1; 176 INSERT INTO p1 VALUES(1, 2); 177 INSERT INTO p1 VALUES(3, 4); 178} 179 180do_execsql_test 4.1 { 181 ALTER TABLE p1 RENAME d TO "silly name"; 182 SELECT sql FROM sqlite_master WHERE name IN ('c1', 'p1'); 183} { 184 {CREATE TABLE c1(a, b, FOREIGN KEY (a, b) REFERENCES p1(c, "silly name"))} 185 {CREATE TABLE p1(c, "silly name", PRIMARY KEY(c, "silly name"))} 186} 187 188do_execsql_test 4.2 { INSERT INTO c1 VALUES(1, 2); } 189 190do_execsql_test 4.3 { 191 CREATE TABLE c2(a, b, FOREIGN KEY (a, b) REFERENCES p1); 192} 193 194do_execsql_test 4.4 { 195 ALTER TABLE p1 RENAME "silly name" TO reasonable; 196 SELECT sql FROM sqlite_master WHERE name IN ('c1', 'c2', 'p1'); 197} { 198 {CREATE TABLE c1(a, b, FOREIGN KEY (a, b) REFERENCES p1(c, "reasonable"))} 199 {CREATE TABLE p1(c, "reasonable", PRIMARY KEY(c, "reasonable"))} 200 {CREATE TABLE c2(a, b, FOREIGN KEY (a, b) REFERENCES p1)} 201} 202 203#------------------------------------------------------------------------- 204 205do_execsql_test 5.0 { 206 CREATE TABLE t5(a, b, c); 207 CREATE INDEX t5a ON t5(a); 208 INSERT INTO t5 VALUES(1, 2, 3), (4, 5, 6); 209 ANALYZE; 210} 211 212do_execsql_test 5.1 { 213 ALTER TABLE t5 RENAME b TO big; 214 SELECT big FROM t5; 215} {2 5} 216 217do_catchsql_test 6.1 { 218 ALTER TABLE sqlite_stat1 RENAME tbl TO thetable; 219} {1 {table sqlite_stat1 may not be altered}} 220 221#------------------------------------------------------------------------- 222# 223do_execsql_test 6.0 { 224 CREATE TABLE blob( 225 rid INTEGER PRIMARY KEY, 226 rcvid INTEGER, 227 size INTEGER, 228 uuid TEXT UNIQUE NOT NULL, 229 content BLOB, 230 CHECK( length(uuid)>=40 AND rid>0 ) 231 ); 232} 233 234do_execsql_test 6.1 { 235 ALTER TABLE "blob" RENAME COLUMN "rid" TO "a1"; 236} 237 238do_catchsql_test 6.2 { 239 ALTER TABLE "blob" RENAME COLUMN "a1" TO [where]; 240} {0 {}} 241 242do_execsql_test 6.3 { 243 SELECT "where" FROM blob; 244} {} 245 246#------------------------------------------------------------------------- 247# Triggers. 248# 249db close 250db2 close 251reset_db 252do_execsql_test 7.0 { 253 CREATE TABLE c(x); 254 INSERT INTO c VALUES(0); 255 CREATE TABLE t6("col a", "col b", "col c"); 256 CREATE TRIGGER zzz AFTER UPDATE OF "col a", "col c" ON t6 BEGIN 257 UPDATE c SET x=x+1; 258 END; 259} 260 261do_execsql_test 7.1.1 { 262 INSERT INTO t6 VALUES(0, 0, 0); 263 UPDATE t6 SET "col c" = 1; 264 SELECT * FROM c; 265} {1} 266 267do_execsql_test 7.1.2 { 268 ALTER TABLE t6 RENAME "col c" TO "col 3"; 269} 270 271do_execsql_test 7.1.3 { 272 UPDATE t6 SET "col 3" = 0; 273 SELECT * FROM c; 274} {2} 275 276#------------------------------------------------------------------------- 277# Views. 278# 279reset_db 280do_execsql_test 8.0 { 281 CREATE TABLE a1(x INTEGER, y TEXT, z BLOB, PRIMARY KEY(x)); 282 CREATE TABLE a2(a, b, c); 283 CREATE VIEW v1 AS SELECT x, y, z FROM a1; 284} 285 286do_execsql_test 8.1 { 287 ALTER TABLE a1 RENAME y TO yyy; 288 SELECT sql FROM sqlite_master WHERE type='view'; 289} {{CREATE VIEW v1 AS SELECT x, yyy, z FROM a1}} 290 291do_execsql_test 8.2.1 { 292 DROP VIEW v1; 293 CREATE VIEW v2 AS SELECT x, x+x, a, a+a FROM a1, a2; 294} {} 295do_execsql_test 8.2.2 { 296 ALTER TABLE a1 RENAME x TO xxx; 297} 298do_execsql_test 8.2.3 { 299 SELECT sql FROM sqlite_master WHERE type='view'; 300} {{CREATE VIEW v2 AS SELECT xxx, xxx+xxx, a, a+a FROM a1, a2}} 301 302do_execsql_test 8.3.1 { 303 DROP TABLE a2; 304 DROP VIEW v2; 305 CREATE TABLE a2(a INTEGER PRIMARY KEY, b, c); 306 CREATE VIEW v2 AS SELECT xxx, xxx+xxx, a, a+a FROM a1, a2; 307} {} 308do_execsql_test 8.3.2 { 309 ALTER TABLE a1 RENAME xxx TO x; 310} 311do_execsql_test 8.3.3 { 312 SELECT sql FROM sqlite_master WHERE type='view'; 313} {{CREATE VIEW v2 AS SELECT x, x+x, a, a+a FROM a1, a2}} 314 315do_execsql_test 8.4.0 { 316 CREATE TABLE b1(a, b, c); 317 CREATE TABLE b2(x, y, z); 318} 319 320do_execsql_test 8.4.1 { 321 CREATE VIEW vvv AS SELECT c+c || coalesce(c, c) FROM b1, b2 WHERE x=c GROUP BY c HAVING c>0; 322 ALTER TABLE b1 RENAME c TO "a;b"; 323 SELECT sql FROM sqlite_master WHERE name='vvv'; 324} {{CREATE VIEW vvv AS SELECT "a;b"+"a;b" || coalesce("a;b", "a;b") FROM b1, b2 WHERE x="a;b" GROUP BY "a;b" HAVING "a;b">0}} 325 326do_execsql_test 8.4.2 { 327 CREATE VIEW www AS SELECT b FROM b1 UNION ALL SELECT y FROM b2; 328 ALTER TABLE b1 RENAME b TO bbb; 329 SELECT sql FROM sqlite_master WHERE name='www'; 330} {{CREATE VIEW www AS SELECT bbb FROM b1 UNION ALL SELECT y FROM b2}} 331 332db collate nocase {string compare} 333 334do_execsql_test 8.4.3 { 335 CREATE VIEW xxx AS SELECT a FROM b1 UNION SELECT x FROM b2 ORDER BY 1 COLLATE nocase; 336} 337 338do_execsql_test 8.4.4 { 339 ALTER TABLE b2 RENAME x TO hello; 340 SELECT sql FROM sqlite_master WHERE name='xxx'; 341} {{CREATE VIEW xxx AS SELECT a FROM b1 UNION SELECT hello FROM b2 ORDER BY 1 COLLATE nocase}} 342 343do_catchsql_test 8.4.5 { 344 CREATE VIEW zzz AS SELECT george, ringo FROM b1; 345 ALTER TABLE b1 RENAME a TO aaa; 346} {1 {error in view zzz: no such column: george}} 347 348#------------------------------------------------------------------------- 349# More triggers. 350# 351proc do_rename_column_test {tn old new lSchema} { 352 for {set i 0} {$i < 2} {incr i} { 353 drop_all_tables_and_views db 354 355 set lSorted [list] 356 foreach sql $lSchema { 357 execsql $sql 358 lappend lSorted [string trim $sql] 359 } 360 set lSorted [lsort $lSorted] 361 362 do_execsql_test $tn.$i.1 { 363 SELECT sql FROM sqlite_master WHERE sql!='' ORDER BY 1 364 } $lSorted 365 366 if {$i==1} { 367 db close 368 sqlite3 db test.db 369 } 370 371 do_execsql_test $tn.$i.2 "ALTER TABLE t1 RENAME $old TO $new" 372 373 do_execsql_test $tn.$i.3 { 374 SELECT sql FROM sqlite_master ORDER BY 1 375 } [string map [list $old $new] $lSorted] 376 } 377} 378 379foreach {tn old new lSchema} { 380 1 _x_ _xxx_ { 381 { CREATE TABLE t1(a, b, _x_) } 382 { CREATE TRIGGER AFTER INSERT ON t1 BEGIN 383 SELECT _x_ FROM t1; 384 END } 385 } 386 387 2 _x_ _xxx_ { 388 { CREATE TABLE t1(a, b, _x_) } 389 { CREATE TABLE t2(c, d, e) } 390 { CREATE TRIGGER ttt AFTER INSERT ON t2 BEGIN 391 SELECT _x_ FROM t1; 392 END } 393 } 394 395 3 _x_ _xxx_ { 396 { CREATE TABLE t1(a, b, _x_ INTEGER, PRIMARY KEY(_x_), CHECK(_x_>0)) } 397 { CREATE TABLE t2(c, d, e) } 398 { CREATE TRIGGER ttt AFTER UPDATE ON t1 BEGIN 399 INSERT INTO t2 VALUES(new.a, new.b, new._x_); 400 END } 401 } 402 403 4 _x_ _xxx_ { 404 { CREATE TABLE t1(a, b, _x_ INTEGER, PRIMARY KEY(_x_), CHECK(_x_>0)) } 405 { CREATE TRIGGER ttt AFTER UPDATE ON t1 BEGIN 406 INSERT INTO t1 VALUES(new.a, new.b, new._x_) 407 ON CONFLICT (_x_) WHERE _x_>10 DO UPDATE SET _x_ = _x_+1; 408 END } 409 } 410 411 4 _x_ _xxx_ { 412 { CREATE TABLE t1(a, b, _x_ INTEGER, PRIMARY KEY(_x_), CHECK(_x_>0)) } 413 { CREATE TRIGGER ttt AFTER UPDATE ON t1 BEGIN 414 INSERT INTO t1 VALUES(new.a, new.b, new._x_) 415 ON CONFLICT (_x_) WHERE _x_>10 DO NOTHING; 416 END } 417 } 418} { 419 do_rename_column_test 9.$tn $old $new $lSchema 420} 421 422#------------------------------------------------------------------------- 423# Test that views can be edited even if there are missing collation 424# sequences or user defined functions. 425# 426reset_db 427 428ifcapable vtab { 429 foreach {tn old new lSchema} { 430 1 _x_ _xxx_ { 431 { CREATE TABLE t1(a, b, _x_) } 432 { CREATE VIEW s1 AS SELECT a, b, _x_ FROM t1 WHERE _x_='abc' COLLATE xyz } 433 } 434 435 2 _x_ _xxx_ { 436 { CREATE TABLE t1(a, b, _x_) } 437 { CREATE VIEW v1 AS SELECT a, b, _x_ FROM t1 WHERE scalar(_x_) } 438 } 439 440 3 _x_ _xxx_ { 441 { CREATE TABLE t1(a, b, _x_) } 442 { CREATE VIEW v1 AS SELECT a, b, _x_ FROM t1 WHERE _x_ = unicode(1, 2, 3) } 443 } 444 445 4 _x_ _xxx_ { 446 { CREATE TABLE t1(a, b, _x_) } 447 { CREATE VIRTUAL TABLE e1 USING echo(t1) } 448 } 449 } { 450 register_echo_module db 451 do_rename_column_test 10.$tn $old $new $lSchema 452 } 453 454 #-------------------------------------------------------------------------- 455 # Test that if a view or trigger refers to a virtual table for which the 456 # module is not available, RENAME COLUMN cannot proceed. 457 # 458 reset_db 459 register_echo_module db 460 do_execsql_test 11.0 { 461 CREATE TABLE x1(a, b, c); 462 CREATE VIRTUAL TABLE e1 USING echo(x1); 463 } 464 db close 465 sqlite3 db test.db 466 467 do_execsql_test 11.1 { 468 ALTER TABLE x1 RENAME b TO bbb; 469 SELECT sql FROM sqlite_master; 470 } { {CREATE TABLE x1(a, bbb, c)} {CREATE VIRTUAL TABLE e1 USING echo(x1)} } 471 472 do_execsql_test 11.2 { 473 CREATE VIEW v1 AS SELECT e1.*, x1.c FROM e1, x1; 474 } 475 476 do_catchsql_test 11.3 { 477 ALTER TABLE x1 RENAME c TO ccc; 478 } {1 {error in view v1: no such module: echo}} 479} 480 481#------------------------------------------------------------------------- 482# Test some error conditions: 483# 484# 1. Renaming a column of a system table, 485# 2. Renaming a column of a VIEW, 486# 3. Renaming a column of a virtual table. 487# 4. Renaming a column that does not exist. 488# 5. Renaming a column of a table that does not exist. 489# 490reset_db 491do_execsql_test 12.1.1 { 492 CREATE TABLE t1(a, b); 493 CREATE INDEX t1a ON t1(a); 494 INSERT INTO t1 VALUES(1, 1), (2, 2), (3, 4); 495 ANALYZE; 496} 497do_catchsql_test 12.1.2 { 498 ALTER TABLE sqlite_stat1 RENAME idx TO theindex; 499} {1 {table sqlite_stat1 may not be altered}} 500do_execsql_test 12.1.3 { 501 SELECT sql FROM sqlite_master WHERE tbl_name = 'sqlite_stat1' 502} {{CREATE TABLE sqlite_stat1(tbl,idx,stat)}} 503 504do_execsql_test 12.2.1 { 505 CREATE VIEW v1 AS SELECT * FROM t1; 506 CREATE VIEW v2(c, d) AS SELECT * FROM t1; 507} 508do_catchsql_test 12.2.2 { 509 ALTER TABLE v1 RENAME a TO z; 510} {1 {cannot rename columns of view "v1"}} 511do_catchsql_test 12.2.3 { 512 ALTER TABLE v2 RENAME c TO y; 513} {1 {cannot rename columns of view "v2"}} 514 515ifcapable fts5 { 516 do_execsql_test 12.3.1 { 517 CREATE VIRTUAL TABLE ft USING fts5(a, b, c); 518 } 519 do_catchsql_test 12.3.2 { 520 ALTER TABLE ft RENAME a TO z; 521 } {1 {cannot rename columns of virtual table "ft"}} 522} 523 524do_execsql_test 12.4.1 { 525 CREATE TABLE t2(x, y, z); 526} 527do_catchsql_test 12.4.2 { 528 ALTER TABLE t2 RENAME COLUMN a TO b; 529} {1 {no such column: "a"}} 530 531do_catchsql_test 12.5.1 { 532 ALTER TABLE t3 RENAME COLUMN a TO b; 533} {1 {no such table: t3}} 534 535#------------------------------------------------------------------------- 536# Test the effect of some parse/resolve errors. 537# 538reset_db 539do_execsql_test 13.1.1 { 540 CREATE TABLE x1(i INTEGER, t TEXT UNIQUE); 541 CREATE TRIGGER tr1 AFTER INSERT ON x1 BEGIN 542 SELECT * FROM nosuchtable; 543 END; 544} 545 546do_catchsql_test 13.1.2 { 547 ALTER TABLE x1 RENAME COLUMN t TO ttt; 548} {1 {error in trigger tr1: no such table: main.nosuchtable}} 549 550do_execsql_test 13.1.3 { 551 DROP TRIGGER tr1; 552 CREATE INDEX x1i ON x1(i); 553 SELECT sql FROM sqlite_master WHERE name='x1i'; 554} {{CREATE INDEX x1i ON x1(i)}} 555 556do_execsql_test 13.1.4 { 557 PRAGMA writable_schema = 1; 558 UPDATE sqlite_master SET sql = 'CREATE INDEX x1i ON x1(j)' WHERE name='x1i'; 559} {} 560 561do_catchsql_test 13.1.5 { 562 ALTER TABLE x1 RENAME COLUMN t TO ttt; 563} {1 {error in index x1i: no such column: j}} 564 565do_execsql_test 13.1.6 { 566 UPDATE sqlite_master SET sql = '' WHERE name='x1i'; 567} {} 568 569do_catchsql_test 13.1.7 { 570 ALTER TABLE x1 RENAME COLUMN t TO ttt; 571} {1 {database disk image is malformed}} 572 573do_execsql_test 13.1.8 { 574 DELETE FROM sqlite_master WHERE name = 'x1i'; 575} 576 577do_execsql_test 13.2.0 { 578 CREATE TABLE data(x UNIQUE, y, z); 579} 580foreach {tn trigger error} { 581 1 { 582 CREATE TRIGGER tr1 AFTER INSERT ON x1 BEGIN 583 UPDATE data SET x=x+1 WHERE zzz=new.i; 584 END; 585 } {no such column: zzz} 586 587 2 { 588 CREATE TRIGGER tr1 AFTER INSERT ON x1 BEGIN 589 INSERT INTO data(x, y) VALUES(new.i, new.t, 1) 590 ON CONFLICT (x) DO UPDATE SET z=zz+1; 591 END; 592 } {no such column: zz} 593 594 3 { 595 CREATE TRIGGER tr1 AFTER INSERT ON x1 BEGIN 596 INSERT INTO x1(i, t) VALUES(new.i+1, new.t||'1') 597 ON CONFLICT (tttttt) DO UPDATE SET t=i+1; 598 END; 599 } {no such column: tttttt} 600 601 4 { 602 CREATE TRIGGER tr1 AFTER INSERT ON x1 BEGIN 603 INSERT INTO nosuchtable VALUES(new.i, new.t); 604 END; 605 } {no such table: main.nosuchtable} 606} { 607 do_execsql_test 13.2.$tn.1 " 608 DROP TRIGGER IF EXISTS tr1; 609 $trigger 610 " 611 612 do_catchsql_test 13.2.$tn.2 { 613 ALTER TABLE x1 RENAME COLUMN t TO ttt; 614 } "1 {error in trigger tr1: $error}" 615} 616 617#------------------------------------------------------------------------- 618# Passing invalid parameters directly to sqlite_rename_column(). 619# 620do_execsql_test 14.1 { 621 CREATE TABLE ddd(sql, type, object, db, tbl, icol, znew, bquote); 622 INSERT INTO ddd VALUES( 623 'CREATE TABLE x1(i INTEGER, t TEXT)', 624 'table', 'x1', 'main', 'x1', -1, 'zzz', 0 625 ), ( 626 'CREATE TABLE x1(i INTEGER, t TEXT)', 627 'table', 'x1', 'main', 'x1', 2, 'zzz', 0 628 ), ( 629 'CREATE TABLE x1(i INTEGER, t TEXT)', 630 'table', 'x1', 'main', 'notable', 0, 'zzz', 0 631 ), ( 632 'CREATE TABLE x1(i INTEGER, t TEXT)', 633 'table', 'x1', 'main', 'ddd', -1, 'zzz', 0 634 ); 635} {} 636 637do_execsql_test 14.2 { 638 SELECT 639 sqlite_rename_column(sql, type, object, db, tbl, icol, znew, bquote, 0) 640 FROM ddd; 641} {{} {} {} {}} 642 643#------------------------------------------------------------------------- 644# 645reset_db 646do_execsql_test 15.0 { 647 CREATE TABLE xxx(a, b, c); 648 SELECT a AS d FROM xxx WHERE d=0; 649} 650 651do_execsql_test 15.1 { 652 CREATE VIEW vvv AS SELECT a AS d FROM xxx WHERE d=0; 653 ALTER TABLE xxx RENAME a TO xyz; 654} 655 656do_execsql_test 15.2 { 657 SELECT sql FROM sqlite_master WHERE type='view'; 658} {{CREATE VIEW vvv AS SELECT xyz AS d FROM xxx WHERE d=0}} 659 660#------------------------------------------------------------------------- 661# 662do_execsql_test 16.1.0 { 663 CREATE TABLE t1(a,b,c); 664 CREATE TABLE t2(d,e,f); 665 INSERT INTO t1 VALUES(1,2,3); 666 INSERT INTO t2 VALUES(4,5,6); 667 CREATE VIEW v4 AS SELECT a, d FROM t1, t2; 668 SELECT * FROM v4; 669} {1 4} 670 671do_catchsql_test 16.1.1 { 672 ALTER TABLE t2 RENAME d TO a; 673} {1 {error in view v4 after rename: ambiguous column name: a}} 674 675do_execsql_test 16.1.2 { 676 SELECT * FROM v4; 677} {1 4} 678 679do_execsql_test 16.1.3 { 680 CREATE UNIQUE INDEX t2d ON t2(d); 681 CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN 682 INSERT INTO t2 VALUES(new.a, new.b, new.c) 683 ON CONFLICT(d) DO UPDATE SET f = excluded.f; 684 END; 685} 686 687do_execsql_test 16.1.4 { 688 INSERT INTO t1 VALUES(4, 8, 456); 689 SELECT * FROM t2; 690} {4 5 456} 691 692do_execsql_test 16.1.5 { 693 ALTER TABLE t2 RENAME COLUMN f TO "big f"; 694 INSERT INTO t1 VALUES(4, 0, 20456); 695 SELECT * FROM t2; 696} {4 5 20456} 697 698do_execsql_test 16.1.6 { 699 ALTER TABLE t1 RENAME COLUMN c TO "big c"; 700 INSERT INTO t1 VALUES(4, 0, 0); 701 SELECT * FROM t2; 702} {4 5 0} 703 704do_execsql_test 16.2.1 { 705 CREATE VIEW temp.v5 AS SELECT "big c" FROM t1; 706 SELECT * FROM v5; 707} {3 456 20456 0} 708 709do_execsql_test 16.2.2 { 710 ALTER TABLE t1 RENAME COLUMN "big c" TO reallybigc; 711} {} 712 713do_execsql_test 16.2.3 { 714 SELECT * FROM v5; 715} {3 456 20456 0} 716 717#------------------------------------------------------------------------- 718# 719do_execsql_test 17.0 { 720 CREATE TABLE u7(x, y, z); 721 CREATE TRIGGER u7t AFTER INSERT ON u7 BEGIN 722 INSERT INTO u8 VALUES(new.x, new.y, new.z); 723 END; 724} {} 725do_catchsql_test 17.1 { 726 ALTER TABLE u7 RENAME x TO xxx; 727} {1 {error in trigger u7t: no such table: main.u8}} 728 729do_execsql_test 17.2 { 730 CREATE TEMP TABLE uu7(x, y, z); 731 CREATE TRIGGER uu7t AFTER INSERT ON uu7 BEGIN 732 INSERT INTO u8 VALUES(new.x, new.y, new.z); 733 END; 734} {} 735do_catchsql_test 17.3 { 736 ALTER TABLE uu7 RENAME x TO xxx; 737} {1 {error in trigger uu7t: no such table: u8}} 738 739reset_db 740forcedelete test.db2 741do_execsql_test 18.0 { 742 ATTACH 'test.db2' AS aux; 743 CREATE TABLE t1(a); 744 CREATE TABLE aux.log(v); 745 CREATE TEMP TRIGGER tr1 AFTER INSERT ON t1 BEGIN 746 INSERT INTO log VALUES(new.a); 747 END; 748 INSERT INTO t1 VALUES(111); 749 SELECT v FROM log; 750} {111} 751 752do_execsql_test 18.1 { 753 ALTER TABLE t1 RENAME a TO b; 754} 755 756 757finish_test 758