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 556sqlite3_db_config db DEFENSIVE 0 557do_execsql_test 13.1.4 { 558 PRAGMA writable_schema = 1; 559 UPDATE sqlite_master SET sql = 'CREATE INDEX x1i ON x1(j)' WHERE name='x1i'; 560} {} 561 562do_catchsql_test 13.1.5 { 563 ALTER TABLE x1 RENAME COLUMN t TO ttt; 564} {1 {error in index x1i: no such column: j}} 565 566do_execsql_test 13.1.6 { 567 UPDATE sqlite_master SET sql = '' WHERE name='x1i'; 568} {} 569 570do_catchsql_test 13.1.7 { 571 ALTER TABLE x1 RENAME COLUMN t TO ttt; 572} {1 {database disk image is malformed}} 573 574do_execsql_test 13.1.8 { 575 DELETE FROM sqlite_master WHERE name = 'x1i'; 576} 577 578do_execsql_test 13.2.0 { 579 CREATE TABLE data(x UNIQUE, y, z); 580} 581foreach {tn trigger error} { 582 1 { 583 CREATE TRIGGER tr1 AFTER INSERT ON x1 BEGIN 584 UPDATE data SET x=x+1 WHERE zzz=new.i; 585 END; 586 } {no such column: zzz} 587 588 2 { 589 CREATE TRIGGER tr1 AFTER INSERT ON x1 BEGIN 590 INSERT INTO data(x, y) VALUES(new.i, new.t, 1) 591 ON CONFLICT (x) DO UPDATE SET z=zz+1; 592 END; 593 } {no such column: zz} 594 595 3 { 596 CREATE TRIGGER tr1 AFTER INSERT ON x1 BEGIN 597 INSERT INTO x1(i, t) VALUES(new.i+1, new.t||'1') 598 ON CONFLICT (tttttt) DO UPDATE SET t=i+1; 599 END; 600 } {no such column: tttttt} 601 602 4 { 603 CREATE TRIGGER tr1 AFTER INSERT ON x1 BEGIN 604 INSERT INTO nosuchtable VALUES(new.i, new.t); 605 END; 606 } {no such table: main.nosuchtable} 607} { 608 do_execsql_test 13.2.$tn.1 " 609 DROP TRIGGER IF EXISTS tr1; 610 $trigger 611 " 612 613 do_catchsql_test 13.2.$tn.2 { 614 ALTER TABLE x1 RENAME COLUMN t TO ttt; 615 } "1 {error in trigger tr1: $error}" 616} 617 618#------------------------------------------------------------------------- 619# Passing invalid parameters directly to sqlite_rename_column(). 620# 621sqlite3_test_control SQLITE_TESTCTRL_INTERNAL_FUNCTIONS 1 622do_execsql_test 14.1 { 623 CREATE TABLE ddd(sql, type, object, db, tbl, icol, znew, bquote); 624 INSERT INTO ddd VALUES( 625 'CREATE TABLE x1(i INTEGER, t TEXT)', 626 'table', 'x1', 'main', 'x1', -1, 'zzz', 0 627 ), ( 628 'CREATE TABLE x1(i INTEGER, t TEXT)', 629 'table', 'x1', 'main', 'x1', 2, 'zzz', 0 630 ), ( 631 'CREATE TABLE x1(i INTEGER, t TEXT)', 632 'table', 'x1', 'main', 'notable', 0, 'zzz', 0 633 ), ( 634 'CREATE TABLE x1(i INTEGER, t TEXT)', 635 'table', 'x1', 'main', 'ddd', -1, 'zzz', 0 636 ); 637} {} 638 639do_execsql_test 14.2 { 640 SELECT 641 sqlite_rename_column(sql, type, object, db, tbl, icol, znew, bquote, 0) 642 FROM ddd; 643} {{} {} {} {}} 644sqlite3_test_control SQLITE_TESTCTRL_INTERNAL_FUNCTIONS 0 645 646# If the INTERNAL_FUNCTIONS test-control is disabled (which is the default) 647# then the sqlite_rename_table() SQL function is not accessible to 648# ordinary SQL. 649# 650do_catchsql_test 14.3 { 651 SELECT sqlite_rename_column(0,0,0,0,0,0,0,0,0); 652} {1 {no such function: sqlite_rename_column}} 653 654#------------------------------------------------------------------------- 655# 656reset_db 657do_execsql_test 15.0 { 658 CREATE TABLE xxx(a, b, c); 659 SELECT a AS d FROM xxx WHERE d=0; 660} 661 662do_execsql_test 15.1 { 663 CREATE VIEW vvv AS SELECT a AS d FROM xxx WHERE d=0; 664 ALTER TABLE xxx RENAME a TO xyz; 665} 666 667do_execsql_test 15.2 { 668 SELECT sql FROM sqlite_master WHERE type='view'; 669} {{CREATE VIEW vvv AS SELECT xyz AS d FROM xxx WHERE d=0}} 670 671#------------------------------------------------------------------------- 672# 673do_execsql_test 16.1.0 { 674 CREATE TABLE t1(a,b,c); 675 CREATE TABLE t2(d,e,f); 676 INSERT INTO t1 VALUES(1,2,3); 677 INSERT INTO t2 VALUES(4,5,6); 678 CREATE VIEW v4 AS SELECT a, d FROM t1, t2; 679 SELECT * FROM v4; 680} {1 4} 681 682do_catchsql_test 16.1.1 { 683 ALTER TABLE t2 RENAME d TO a; 684} {1 {error in view v4 after rename: ambiguous column name: a}} 685 686do_execsql_test 16.1.2 { 687 SELECT * FROM v4; 688} {1 4} 689 690do_execsql_test 16.1.3 { 691 CREATE UNIQUE INDEX t2d ON t2(d); 692 CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN 693 INSERT INTO t2 VALUES(new.a, new.b, new.c) 694 ON CONFLICT(d) DO UPDATE SET f = excluded.f; 695 END; 696} 697 698do_execsql_test 16.1.4 { 699 INSERT INTO t1 VALUES(4, 8, 456); 700 SELECT * FROM t2; 701} {4 5 456} 702 703do_execsql_test 16.1.5 { 704 ALTER TABLE t2 RENAME COLUMN f TO "big f"; 705 INSERT INTO t1 VALUES(4, 0, 20456); 706 SELECT * FROM t2; 707} {4 5 20456} 708 709do_execsql_test 16.1.6 { 710 ALTER TABLE t1 RENAME COLUMN c TO "big c"; 711 INSERT INTO t1 VALUES(4, 0, 0); 712 SELECT * FROM t2; 713} {4 5 0} 714 715do_execsql_test 16.2.1 { 716 CREATE VIEW temp.v5 AS SELECT "big c" FROM t1; 717 SELECT * FROM v5; 718} {3 456 20456 0} 719 720do_execsql_test 16.2.2 { 721 ALTER TABLE t1 RENAME COLUMN "big c" TO reallybigc; 722} {} 723 724do_execsql_test 16.2.3 { 725 SELECT * FROM v5; 726} {3 456 20456 0} 727 728#------------------------------------------------------------------------- 729# 730do_execsql_test 17.0 { 731 CREATE TABLE u7(x, y, z); 732 CREATE TRIGGER u7t AFTER INSERT ON u7 BEGIN 733 INSERT INTO u8 VALUES(new.x, new.y, new.z); 734 END; 735} {} 736do_catchsql_test 17.1 { 737 ALTER TABLE u7 RENAME x TO xxx; 738} {1 {error in trigger u7t: no such table: main.u8}} 739 740do_execsql_test 17.2 { 741 CREATE TEMP TABLE uu7(x, y, z); 742 CREATE TRIGGER uu7t AFTER INSERT ON uu7 BEGIN 743 INSERT INTO u8 VALUES(new.x, new.y, new.z); 744 END; 745} {} 746do_catchsql_test 17.3 { 747 ALTER TABLE uu7 RENAME x TO xxx; 748} {1 {error in trigger uu7t: no such table: u8}} 749 750reset_db 751forcedelete test.db2 752do_execsql_test 18.0 { 753 ATTACH 'test.db2' AS aux; 754 CREATE TABLE t1(a); 755 CREATE TABLE aux.log(v); 756 CREATE TEMP TRIGGER tr1 AFTER INSERT ON t1 BEGIN 757 INSERT INTO log VALUES(new.a); 758 END; 759 INSERT INTO t1 VALUES(111); 760 SELECT v FROM log; 761} {111} 762 763do_execsql_test 18.1 { 764 ALTER TABLE t1 RENAME a TO b; 765} 766 767reset_db 768do_execsql_test 19.0 { 769 CREATE TABLE t1(a, b); 770 CREATE TABLE t2(c, d); 771 CREATE VIEW v2(e) AS SELECT coalesce(t2.c,t1.a) FROM t1, t2 WHERE t1.b=t2.d; 772} 773 774do_execsql_test 19.1 { 775 ALTER TABLE t1 RENAME a TO f; 776 SELECT sql FROM sqlite_master WHERE name = 'v2'; 777} { 778 {CREATE VIEW v2(e) AS SELECT coalesce(t2.c,t1.f) FROM t1, t2 WHERE t1.b=t2.d} 779} 780 781 782 783finish_test 784