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 17set testdir [file dirname $argv0] 18source $testdir/tester.tcl 19set testprefix altercol 20 21# If SQLITE_OMIT_ALTERTABLE is defined, omit this file. 22ifcapable !altertable { 23 finish_test 24 return 25} 26 27# Drop all the tables and views in the 'main' database of database connect 28# [db]. Sort the objects by name before dropping them. 29# 30proc drop_all_tables_and_views {db} { 31 set SQL { 32 SELECT name, type FROM sqlite_master 33 WHERE type IN ('table', 'view') AND name NOT LIKE 'sqlite_%' 34 ORDER BY 1 35 } 36 foreach {z t} [db eval $SQL] { 37 db eval "DROP $t $z" 38 } 39} 40 41foreach {tn before after} { 42 1 {CREATE TABLE t1(a INTEGER, b TEXT, c BLOB)} 43 {CREATE TABLE t1(a INTEGER, d TEXT, c BLOB)} 44 45 2 {CREATE TABLE t1(a INTEGER, x TEXT, "b" BLOB)} 46 {CREATE TABLE t1(a INTEGER, x TEXT, "d" BLOB)} 47 48 3 {CREATE TABLE t1(a INTEGER, b TEXT, c BLOB, CHECK(b!=''))} 49 {CREATE TABLE t1(a INTEGER, d TEXT, c BLOB, CHECK(d!=''))} 50 51 4 {CREATE TABLE t1(a INTEGER, b TEXT, c BLOB, CHECK(t1.b!=''))} 52 {CREATE TABLE t1(a INTEGER, d TEXT, c BLOB, CHECK(t1.d!=''))} 53 54 5 {CREATE TABLE t1(a INTEGER, b TEXT, c BLOB, CHECK( coalesce(b,c) ))} 55 {CREATE TABLE t1(a INTEGER, d TEXT, c BLOB, CHECK( coalesce(d,c) ))} 56 57 6 {CREATE TABLE t1(a INTEGER, "b"TEXT, c BLOB, CHECK( coalesce(b,c) ))} 58 {CREATE TABLE t1(a INTEGER, "d"TEXT, c BLOB, CHECK( coalesce(d,c) ))} 59 60 7 {CREATE TABLE t1(a INTEGER, b TEXT, c BLOB, PRIMARY KEY(b, c))} 61 {CREATE TABLE t1(a INTEGER, d TEXT, c BLOB, PRIMARY KEY(d, c))} 62 63 8 {CREATE TABLE t1(a INTEGER, b TEXT PRIMARY KEY, c BLOB)} 64 {CREATE TABLE t1(a INTEGER, d TEXT PRIMARY KEY, c BLOB)} 65 66 9 {CREATE TABLE t1(a, b TEXT, c, PRIMARY KEY(a, b), UNIQUE("B"))} 67 {CREATE TABLE t1(a, d TEXT, c, PRIMARY KEY(a, d), UNIQUE("d"))} 68 69 10 {CREATE TABLE t1(a, b, c); CREATE INDEX t1i ON t1(a, c)} 70 {{CREATE TABLE t1(a, d, c)} {CREATE INDEX t1i ON t1(a, c)}} 71 72 11 {CREATE TABLE t1(a, b, c); CREATE INDEX t1i ON t1(b, c)} 73 {{CREATE TABLE t1(a, d, c)} {CREATE INDEX t1i ON t1(d, c)}} 74 75 12 {CREATE TABLE t1(a, b, c); CREATE INDEX t1i ON t1(b+b+b+b, c) WHERE b>0} 76 {{CREATE TABLE t1(a, d, c)} {CREATE INDEX t1i ON t1(d+d+d+d, c) WHERE d>0}} 77 78 13 {CREATE TABLE t1(a, b, c, FOREIGN KEY (b) REFERENCES t2)} 79 {CREATE TABLE t1(a, d, c, FOREIGN KEY (d) REFERENCES t2)} 80 81 14 {CREATE TABLE t1(a INTEGER, b TEXT, c BLOB, PRIMARY KEY(b))} 82 {CREATE TABLE t1(a INTEGER, d TEXT, c BLOB, PRIMARY KEY(d))} 83 84 15 {CREATE TABLE t1(a INTEGER, b INTEGER, c BLOB, PRIMARY KEY(b))} 85 {CREATE TABLE t1(a INTEGER, d INTEGER, c BLOB, PRIMARY KEY(d))} 86 87 16 {CREATE TABLE t1(a INTEGER, b INTEGER PRIMARY KEY, c BLOB)} 88 {CREATE TABLE t1(a INTEGER, d INTEGER PRIMARY KEY, c BLOB)} 89 90 17 {CREATE TABLE t1(a INTEGER, b INTEGER PRIMARY KEY, c BLOB, FOREIGN KEY (b) REFERENCES t2)} 91 {CREATE TABLE t1(a INTEGER, d INTEGER PRIMARY KEY, c BLOB, FOREIGN KEY (d) REFERENCES t2)} 92 93} { 94 reset_db 95 do_execsql_test 1.$tn.0 $before 96 97 do_execsql_test 1.$tn.1 { 98 INSERT INTO t1 VALUES(1, 2, 3); 99 } 100 101 do_execsql_test 1.$tn.2 { 102 ALTER TABLE t1 RENAME COLUMN b TO d; 103 } 104 105 do_execsql_test 1.$tn.3 { 106 SELECT * FROM t1; 107 } {1 2 3} 108 109 if {[string first INDEX $before]>0} { 110 set res $after 111 } else { 112 set res [list $after] 113 } 114 do_execsql_test 1.$tn.4 { 115 SELECT sql FROM sqlite_master WHERE tbl_name='t1' AND sql!='' 116 } $res 117} 118 119#------------------------------------------------------------------------- 120# 121do_execsql_test 2.0 { 122 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); 123} 124 125sqlite3 db2 test.db 126do_execsql_test -db db2 2.1 { SELECT b FROM t3 } 127 128do_execsql_test 2.2 { 129 ALTER TABLE t3 RENAME b TO biglongname; 130 SELECT sql FROM sqlite_master WHERE name='t3'; 131} {{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)}} 132 133do_execsql_test -db db2 2.3 { SELECT biglongname FROM t3 } 134 135#------------------------------------------------------------------------- 136# 137do_execsql_test 3.0 { 138 CREATE TABLE t4(x, y, z); 139 CREATE TRIGGER ttt AFTER INSERT ON t4 WHEN new.y<0 BEGIN 140 SELECT x, y, z FROM t4; 141 DELETE FROM t4 WHERE y=32; 142 UPDATE t4 SET x=y+1, y=0 WHERE y=32; 143 INSERT INTO t4(x, y, z) SELECT 4, 5, 6 WHERE 0; 144 END; 145 INSERT INTO t4 VALUES(3, 2, 1); 146} 147 148do_execsql_test 3.1 { 149 ALTER TABLE t4 RENAME y TO abc; 150 SELECT sql FROM sqlite_master WHERE name='t4'; 151} {{CREATE TABLE t4(x, abc, z)}} 152 153do_execsql_test 3.2 { 154 SELECT * FROM t4; 155} {3 2 1} 156 157do_execsql_test 3.3 { INSERT INTO t4 VALUES(6, 5, 4); } {} 158 159do_execsql_test 3.4 { SELECT sql FROM sqlite_master WHERE type='trigger' } { 160{CREATE TRIGGER ttt AFTER INSERT ON t4 WHEN new.abc<0 BEGIN 161 SELECT x, abc, z FROM t4; 162 DELETE FROM t4 WHERE abc=32; 163 UPDATE t4 SET x=abc+1, abc=0 WHERE abc=32; 164 INSERT INTO t4(x, abc, z) SELECT 4, 5, 6 WHERE 0; 165 END} 166} 167 168#------------------------------------------------------------------------- 169# 170do_execsql_test 4.0 { 171 CREATE TABLE c1(a, b, FOREIGN KEY (a, b) REFERENCES p1(c, d)); 172 CREATE TABLE p1(c, d, PRIMARY KEY(c, d)); 173 PRAGMA foreign_keys = 1; 174 INSERT INTO p1 VALUES(1, 2); 175 INSERT INTO p1 VALUES(3, 4); 176} 177 178do_execsql_test 4.1 { 179 ALTER TABLE p1 RENAME d TO "silly name"; 180 SELECT sql FROM sqlite_master WHERE name IN ('c1', 'p1'); 181} { 182 {CREATE TABLE c1(a, b, FOREIGN KEY (a, b) REFERENCES p1(c, "silly name"))} 183 {CREATE TABLE p1(c, "silly name", PRIMARY KEY(c, "silly name"))} 184} 185 186do_execsql_test 4.2 { INSERT INTO c1 VALUES(1, 2); } 187 188do_execsql_test 4.3 { 189 CREATE TABLE c2(a, b, FOREIGN KEY (a, b) REFERENCES p1); 190} 191 192do_execsql_test 4.4 { 193 ALTER TABLE p1 RENAME "silly name" TO reasonable; 194 SELECT sql FROM sqlite_master WHERE name IN ('c1', 'c2', 'p1'); 195} { 196 {CREATE TABLE c1(a, b, FOREIGN KEY (a, b) REFERENCES p1(c, "reasonable"))} 197 {CREATE TABLE p1(c, "reasonable", PRIMARY KEY(c, "reasonable"))} 198 {CREATE TABLE c2(a, b, FOREIGN KEY (a, b) REFERENCES p1)} 199} 200 201#------------------------------------------------------------------------- 202 203do_execsql_test 5.0 { 204 CREATE TABLE t5(a, b, c); 205 CREATE INDEX t5a ON t5(a); 206 INSERT INTO t5 VALUES(1, 2, 3), (4, 5, 6); 207 ANALYZE; 208} 209 210do_execsql_test 5.1 { 211 ALTER TABLE t5 RENAME b TO big; 212 SELECT big FROM t5; 213} {2 5} 214 215do_catchsql_test 6.1 { 216 ALTER TABLE sqlite_stat1 RENAME tbl TO thetable; 217} {1 {table sqlite_stat1 may not be altered}} 218 219#------------------------------------------------------------------------- 220# 221do_execsql_test 6.0 { 222 CREATE TABLE blob( 223 rid INTEGER PRIMARY KEY, 224 rcvid INTEGER, 225 size INTEGER, 226 uuid TEXT UNIQUE NOT NULL, 227 content BLOB, 228 CHECK( length(uuid)>=40 AND rid>0 ) 229 ); 230} 231 232do_execsql_test 6.1 { 233 ALTER TABLE "blob" RENAME COLUMN "rid" TO "a1"; 234} 235 236do_catchsql_test 6.2 { 237 ALTER TABLE "blob" RENAME COLUMN "a1" TO [where]; 238} {0 {}} 239 240do_execsql_test 6.3 { 241 SELECT "where" FROM blob; 242} {} 243 244#------------------------------------------------------------------------- 245# Triggers. 246# 247db close 248db2 close 249reset_db 250do_execsql_test 7.0 { 251 CREATE TABLE c(x); 252 INSERT INTO c VALUES(0); 253 CREATE TABLE t6("col a", "col b", "col c"); 254 CREATE TRIGGER zzz AFTER UPDATE OF "col a", "col c" ON t6 BEGIN 255 UPDATE c SET x=x+1; 256 END; 257} 258 259do_execsql_test 7.1.1 { 260 INSERT INTO t6 VALUES(0, 0, 0); 261 UPDATE t6 SET "col c" = 1; 262 SELECT * FROM c; 263} {1} 264 265do_execsql_test 7.1.2 { 266 ALTER TABLE t6 RENAME "col c" TO "col 3"; 267} 268 269do_execsql_test 7.1.3 { 270 UPDATE t6 SET "col 3" = 0; 271 SELECT * FROM c; 272} {2} 273 274#------------------------------------------------------------------------- 275# Views. 276# 277reset_db 278do_execsql_test 8.0 { 279 CREATE TABLE a1(x INTEGER, y TEXT, z BLOB, PRIMARY KEY(x)); 280 CREATE TABLE a2(a, b, c); 281 CREATE VIEW v1 AS SELECT x, y, z FROM a1; 282} 283 284do_execsql_test 8.1 { 285 ALTER TABLE a1 RENAME y TO yyy; 286 SELECT sql FROM sqlite_master WHERE type='view'; 287} {{CREATE VIEW v1 AS SELECT x, yyy, z FROM a1}} 288 289do_execsql_test 8.2.1 { 290 DROP VIEW v1; 291 CREATE VIEW v2 AS SELECT x, x+x, a, a+a FROM a1, a2; 292} {} 293do_execsql_test 8.2.2 { 294 ALTER TABLE a1 RENAME x TO xxx; 295} 296do_execsql_test 8.2.3 { 297 SELECT sql FROM sqlite_master WHERE type='view'; 298} {{CREATE VIEW v2 AS SELECT xxx, xxx+xxx, a, a+a FROM a1, a2}} 299 300do_execsql_test 8.3.1 { 301 DROP TABLE a2; 302 DROP VIEW v2; 303 CREATE TABLE a2(a INTEGER PRIMARY KEY, b, c); 304 CREATE VIEW v2 AS SELECT xxx, xxx+xxx, a, a+a FROM a1, a2; 305} {} 306do_execsql_test 8.3.2 { 307 ALTER TABLE a1 RENAME xxx TO x; 308} 309do_execsql_test 8.3.3 { 310 SELECT sql FROM sqlite_master WHERE type='view'; 311} {{CREATE VIEW v2 AS SELECT x, x+x, a, a+a FROM a1, a2}} 312 313do_execsql_test 8.4.0 { 314 CREATE TABLE b1(a, b, c); 315 CREATE TABLE b2(x, y, z); 316} 317 318do_execsql_test 8.4.1 { 319 CREATE VIEW vvv AS SELECT c+c || coalesce(c, c) FROM b1, b2 WHERE x=c GROUP BY c HAVING c>0; 320 ALTER TABLE b1 RENAME c TO "a;b"; 321 SELECT sql FROM sqlite_master WHERE name='vvv'; 322} {{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}} 323 324do_execsql_test 8.4.2 { 325 CREATE VIEW www AS SELECT b FROM b1 UNION ALL SELECT y FROM b2; 326 ALTER TABLE b1 RENAME b TO bbb; 327 SELECT sql FROM sqlite_master WHERE name='www'; 328} {{CREATE VIEW www AS SELECT bbb FROM b1 UNION ALL SELECT y FROM b2}} 329 330db collate nocase {string compare} 331 332do_execsql_test 8.4.3 { 333 CREATE VIEW xxx AS SELECT a FROM b1 UNION SELECT x FROM b2 ORDER BY 1 COLLATE nocase; 334} 335 336do_execsql_test 8.4.4 { 337 ALTER TABLE b2 RENAME x TO hello; 338 SELECT sql FROM sqlite_master WHERE name='xxx'; 339} {{CREATE VIEW xxx AS SELECT a FROM b1 UNION SELECT hello FROM b2 ORDER BY 1 COLLATE nocase}} 340 341do_catchsql_test 8.4.5 { 342 CREATE VIEW zzz AS SELECT george, ringo FROM b1; 343 ALTER TABLE b1 RENAME a TO aaa; 344} {1 {error in view zzz: no such column: george}} 345 346#------------------------------------------------------------------------- 347# More triggers. 348# 349proc do_rename_column_test {tn old new lSchema} { 350 for {set i 0} {$i < 2} {incr i} { 351 drop_all_tables_and_views db 352 353 set lSorted [list] 354 foreach sql $lSchema { 355 execsql $sql 356 lappend lSorted [string trim $sql] 357 } 358 set lSorted [lsort $lSorted] 359 360 do_execsql_test $tn.$i.1 { 361 SELECT sql FROM sqlite_master WHERE sql!='' ORDER BY 1 362 } $lSorted 363 364 if {$i==1} { 365 db close 366 sqlite3 db test.db 367 } 368 369 do_execsql_test $tn.$i.2 "ALTER TABLE t1 RENAME $old TO $new" 370 371 do_execsql_test $tn.$i.3 { 372 SELECT sql FROM sqlite_master ORDER BY 1 373 } [string map [list $old $new] $lSorted] 374 } 375} 376 377foreach {tn old new lSchema} { 378 1 _x_ _xxx_ { 379 { CREATE TABLE t1(a, b, _x_) } 380 { CREATE TRIGGER AFTER INSERT ON t1 BEGIN 381 SELECT _x_ FROM t1; 382 END } 383 } 384 385 2 _x_ _xxx_ { 386 { CREATE TABLE t1(a, b, _x_) } 387 { CREATE TABLE t2(c, d, e) } 388 { CREATE TRIGGER ttt AFTER INSERT ON t2 BEGIN 389 SELECT _x_ FROM t1; 390 END } 391 } 392 393 3 _x_ _xxx_ { 394 { CREATE TABLE t1(a, b, _x_ INTEGER, PRIMARY KEY(_x_), CHECK(_x_>0)) } 395 { CREATE TABLE t2(c, d, e) } 396 { CREATE TRIGGER ttt AFTER UPDATE ON t1 BEGIN 397 INSERT INTO t2 VALUES(new.a, new.b, new._x_); 398 END } 399 } 400 401 4 _x_ _xxx_ { 402 { CREATE TABLE t1(a, b, _x_ INTEGER, PRIMARY KEY(_x_), CHECK(_x_>0)) } 403 { CREATE TRIGGER ttt AFTER UPDATE ON t1 BEGIN 404 INSERT INTO t1 VALUES(new.a, new.b, new._x_) 405 ON CONFLICT (_x_) WHERE _x_>10 DO UPDATE SET _x_ = _x_+1; 406 END } 407 } 408 409 4 _x_ _xxx_ { 410 { CREATE TABLE t1(a, b, _x_ INTEGER, PRIMARY KEY(_x_), CHECK(_x_>0)) } 411 { CREATE TRIGGER ttt AFTER UPDATE ON t1 BEGIN 412 INSERT INTO t1 VALUES(new.a, new.b, new._x_) 413 ON CONFLICT (_x_) WHERE _x_>10 DO NOTHING; 414 END } 415 } 416} { 417 do_rename_column_test 9.$tn $old $new $lSchema 418} 419 420#------------------------------------------------------------------------- 421# Test that views can be edited even if there are missing collation 422# sequences or user defined functions. 423# 424reset_db 425 426ifcapable vtab { 427 foreach {tn old new lSchema} { 428 1 _x_ _xxx_ { 429 { CREATE TABLE t1(a, b, _x_) } 430 { CREATE VIEW s1 AS SELECT a, b, _x_ FROM t1 WHERE _x_='abc' COLLATE xyz } 431 } 432 433 2 _x_ _xxx_ { 434 { CREATE TABLE t1(a, b, _x_) } 435 { CREATE VIEW v1 AS SELECT a, b, _x_ FROM t1 WHERE scalar(_x_) } 436 } 437 438 3 _x_ _xxx_ { 439 { CREATE TABLE t1(a, b, _x_) } 440 { CREATE VIEW v1 AS SELECT a, b, _x_ FROM t1 WHERE _x_ = unicode(1, 2, 3) } 441 } 442 443 4 _x_ _xxx_ { 444 { CREATE TABLE t1(a, b, _x_) } 445 { CREATE VIRTUAL TABLE e1 USING echo(t1) } 446 } 447 } { 448 register_echo_module db 449 do_rename_column_test 10.$tn $old $new $lSchema 450 } 451 452 #-------------------------------------------------------------------------- 453 # Test that if a view or trigger refers to a virtual table for which the 454 # module is not available, RENAME COLUMN cannot proceed. 455 # 456 reset_db 457 register_echo_module db 458 do_execsql_test 11.0 { 459 CREATE TABLE x1(a, b, c); 460 CREATE VIRTUAL TABLE e1 USING echo(x1); 461 } 462 db close 463 sqlite3 db test.db 464 465 do_execsql_test 11.1 { 466 ALTER TABLE x1 RENAME b TO bbb; 467 SELECT sql FROM sqlite_master; 468 } { {CREATE TABLE x1(a, bbb, c)} {CREATE VIRTUAL TABLE e1 USING echo(x1)} } 469 470 do_execsql_test 11.2 { 471 CREATE VIEW v1 AS SELECT e1.*, x1.c FROM e1, x1; 472 } 473 474 do_catchsql_test 11.3 { 475 ALTER TABLE x1 RENAME c TO ccc; 476 } {1 {error in view v1: no such module: echo}} 477} 478 479#------------------------------------------------------------------------- 480# Test some error conditions: 481# 482# 1. Renaming a column of a system table, 483# 2. Renaming a column of a VIEW, 484# 3. Renaming a column of a virtual table. 485# 4. Renaming a column that does not exist. 486# 5. Renaming a column of a table that does not exist. 487# 488reset_db 489do_execsql_test 12.1.1 { 490 CREATE TABLE t1(a, b); 491 CREATE INDEX t1a ON t1(a); 492 INSERT INTO t1 VALUES(1, 1), (2, 2), (3, 4); 493 ANALYZE; 494} 495do_catchsql_test 12.1.2 { 496 ALTER TABLE sqlite_stat1 RENAME idx TO theindex; 497} {1 {table sqlite_stat1 may not be altered}} 498do_execsql_test 12.1.3 { 499 SELECT sql FROM sqlite_master WHERE tbl_name = 'sqlite_stat1' 500} {{CREATE TABLE sqlite_stat1(tbl,idx,stat)}} 501 502do_execsql_test 12.2.1 { 503 CREATE VIEW v1 AS SELECT * FROM t1; 504 CREATE VIEW v2(c, d) AS SELECT * FROM t1; 505} 506do_catchsql_test 12.2.2 { 507 ALTER TABLE v1 RENAME a TO z; 508} {1 {cannot rename columns of view "v1"}} 509do_catchsql_test 12.2.3 { 510 ALTER TABLE v2 RENAME c TO y; 511} {1 {cannot rename columns of view "v2"}} 512 513ifcapable fts5 { 514 do_execsql_test 12.3.1 { 515 CREATE VIRTUAL TABLE ft USING fts5(a, b, c); 516 } 517 do_catchsql_test 12.3.2 { 518 ALTER TABLE ft RENAME a TO z; 519 } {1 {cannot rename columns of virtual table "ft"}} 520} 521 522do_execsql_test 12.4.1 { 523 CREATE TABLE t2(x, y, z); 524} 525do_catchsql_test 12.4.2 { 526 ALTER TABLE t2 RENAME COLUMN a TO b; 527} {1 {no such column: "a"}} 528 529do_catchsql_test 12.5.1 { 530 ALTER TABLE t3 RENAME COLUMN a TO b; 531} {1 {no such table: t3}} 532 533#------------------------------------------------------------------------- 534# Test the effect of some parse/resolve errors. 535# 536reset_db 537do_execsql_test 13.1.1 { 538 CREATE TABLE x1(i INTEGER, t TEXT UNIQUE); 539 CREATE TRIGGER tr1 AFTER INSERT ON x1 BEGIN 540 SELECT * FROM nosuchtable; 541 END; 542} 543 544do_catchsql_test 13.1.2 { 545 ALTER TABLE x1 RENAME COLUMN t TO ttt; 546} {1 {error in trigger tr1: no such table: main.nosuchtable}} 547 548do_execsql_test 13.1.3 { 549 DROP TRIGGER tr1; 550 CREATE INDEX x1i ON x1(i); 551 SELECT sql FROM sqlite_master WHERE name='x1i'; 552} {{CREATE INDEX x1i ON x1(i)}} 553 554sqlite3_db_config db DEFENSIVE 0 555do_execsql_test 13.1.4 { 556 PRAGMA writable_schema = ON; 557 UPDATE sqlite_master SET sql = 'CREATE INDEX x1i ON x1(j)' WHERE name='x1i'; 558 PRAGMA writable_schema = OFF; 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 PRAGMA writable_schema = ON; 567 UPDATE sqlite_master SET sql = '' WHERE name='x1i'; 568 PRAGMA writable_schema = OFF; 569} {} 570 571do_catchsql_test 13.1.7 { 572 ALTER TABLE x1 RENAME COLUMN t TO ttt; 573} {1 {error in index x1i: }} 574 575do_execsql_test 13.1.8 { 576 PRAGMA writable_schema = ON; 577 DELETE FROM sqlite_master WHERE name = 'x1i'; 578 PRAGMA writable_schema = OFF; 579} 580 581do_execsql_test 13.2.0 { 582 CREATE TABLE data(x UNIQUE, y, z); 583} 584foreach {tn trigger error} { 585 1 { 586 CREATE TRIGGER tr1 AFTER INSERT ON x1 BEGIN 587 UPDATE data SET x=x+1 WHERE zzz=new.i; 588 END; 589 } {no such column: zzz} 590 591 2 { 592 CREATE TRIGGER tr1 AFTER INSERT ON x1 BEGIN 593 INSERT INTO data(x, y) VALUES(new.i, new.t, 1) 594 ON CONFLICT (x) DO UPDATE SET z=zz+1; 595 END; 596 } {no such column: zz} 597 598 3 { 599 CREATE TRIGGER tr1 AFTER INSERT ON x1 BEGIN 600 INSERT INTO x1(i, t) VALUES(new.i+1, new.t||'1') 601 ON CONFLICT (tttttt) DO UPDATE SET t=i+1; 602 END; 603 } {no such column: tttttt} 604 605 4 { 606 CREATE TRIGGER tr1 AFTER INSERT ON x1 BEGIN 607 INSERT INTO nosuchtable VALUES(new.i, new.t); 608 END; 609 } {no such table: main.nosuchtable} 610} { 611 do_execsql_test 13.2.$tn.1 " 612 DROP TRIGGER IF EXISTS tr1; 613 $trigger 614 " 615 616 do_catchsql_test 13.2.$tn.2 { 617 ALTER TABLE x1 RENAME COLUMN t TO ttt; 618 } "1 {error in trigger tr1: $error}" 619} 620 621#------------------------------------------------------------------------- 622# Passing invalid parameters directly to sqlite_rename_column(). 623# 624sqlite3_test_control SQLITE_TESTCTRL_INTERNAL_FUNCTIONS db 625do_execsql_test 14.1 { 626 CREATE TABLE ddd(sql, type, object, db, tbl, icol, znew, bquote); 627 INSERT INTO ddd VALUES( 628 'CREATE TABLE x1(i INTEGER, t TEXT)', 629 'table', 'x1', 'main', 'x1', -1, 'zzz', 0 630 ), ( 631 'CREATE TABLE x1(i INTEGER, t TEXT)', 632 'table', 'x1', 'main', 'x1', 2, 'zzz', 0 633 ), ( 634 'CREATE TABLE x1(i INTEGER, t TEXT)', 635 'table', 'x1', 'main', 'notable', 0, 'zzz', 0 636 ), ( 637 'CREATE TABLE x1(i INTEGER, t TEXT)', 638 'table', 'x1', 'main', 'ddd', -1, 'zzz', 0 639 ); 640} {} 641 642do_execsql_test 14.2 { 643 SELECT 644 sqlite_rename_column(sql, type, object, db, tbl, icol, znew, bquote, 0) 645 FROM ddd; 646} {{} {} {} {}} 647sqlite3_test_control SQLITE_TESTCTRL_INTERNAL_FUNCTIONS db 648 649# If the INTERNAL_FUNCTIONS test-control is disabled (which is the default) 650# then the sqlite_rename_table() SQL function is not accessible to 651# ordinary SQL. 652# 653do_catchsql_test 14.3 { 654 SELECT sqlite_rename_column(0,0,0,0,0,0,0,0,0); 655} {1 {no such function: sqlite_rename_column}} 656 657#------------------------------------------------------------------------- 658# 659reset_db 660do_execsql_test 15.0 { 661 CREATE TABLE xxx(a, b, c); 662 SELECT a AS d FROM xxx WHERE d=0; 663} 664 665do_execsql_test 15.1 { 666 CREATE VIEW vvv AS SELECT a AS d FROM xxx WHERE d=0; 667 ALTER TABLE xxx RENAME a TO xyz; 668} 669 670do_execsql_test 15.2 { 671 SELECT sql FROM sqlite_master WHERE type='view'; 672} {{CREATE VIEW vvv AS SELECT xyz AS d FROM xxx WHERE d=0}} 673 674#------------------------------------------------------------------------- 675# 676do_execsql_test 16.1.0 { 677 CREATE TABLE t1(a,b,c); 678 CREATE TABLE t2(d,e,f); 679 INSERT INTO t1 VALUES(1,2,3); 680 INSERT INTO t2 VALUES(4,5,6); 681 CREATE VIEW v4 AS SELECT a, d FROM t1, t2; 682 SELECT * FROM v4; 683} {1 4} 684 685do_catchsql_test 16.1.1 { 686 ALTER TABLE t2 RENAME d TO a; 687} {1 {error in view v4 after rename: ambiguous column name: a}} 688 689do_execsql_test 16.1.2 { 690 SELECT * FROM v4; 691} {1 4} 692 693do_execsql_test 16.1.3 { 694 CREATE UNIQUE INDEX t2d ON t2(d); 695 CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN 696 INSERT INTO t2 VALUES(new.a, new.b, new.c) 697 ON CONFLICT(d) DO UPDATE SET f = excluded.f; 698 END; 699} 700 701do_execsql_test 16.1.4 { 702 INSERT INTO t1 VALUES(4, 8, 456); 703 SELECT * FROM t2; 704} {4 5 456} 705 706do_execsql_test 16.1.5 { 707 ALTER TABLE t2 RENAME COLUMN f TO "big f"; 708 INSERT INTO t1 VALUES(4, 0, 20456); 709 SELECT * FROM t2; 710} {4 5 20456} 711 712do_execsql_test 16.1.6 { 713 ALTER TABLE t1 RENAME COLUMN c TO "big c"; 714 INSERT INTO t1 VALUES(4, 0, 0); 715 SELECT * FROM t2; 716} {4 5 0} 717 718do_execsql_test 16.2.1 { 719 CREATE VIEW temp.v5 AS SELECT "big c" FROM t1; 720 SELECT * FROM v5; 721} {3 456 20456 0} 722 723do_execsql_test 16.2.2 { 724 ALTER TABLE t1 RENAME COLUMN "big c" TO reallybigc; 725} {} 726 727do_execsql_test 16.2.3 { 728 SELECT * FROM v5; 729} {3 456 20456 0} 730 731#------------------------------------------------------------------------- 732# 733do_execsql_test 17.0 { 734 CREATE TABLE u7(x, y, z); 735 CREATE TRIGGER u7t AFTER INSERT ON u7 BEGIN 736 INSERT INTO u8 VALUES(new.x, new.y, new.z); 737 END; 738} {} 739do_catchsql_test 17.1 { 740 ALTER TABLE u7 RENAME x TO xxx; 741} {1 {error in trigger u7t: no such table: main.u8}} 742 743do_execsql_test 17.2 { 744 CREATE TEMP TABLE uu7(x, y, z); 745 CREATE TRIGGER uu7t AFTER INSERT ON uu7 BEGIN 746 INSERT INTO u8 VALUES(new.x, new.y, new.z); 747 END; 748} {} 749do_catchsql_test 17.3 { 750 ALTER TABLE uu7 RENAME x TO xxx; 751} {1 {error in trigger uu7t: no such table: u8}} 752 753reset_db 754forcedelete test.db2 755do_execsql_test 18.0 { 756 ATTACH 'test.db2' AS aux; 757 CREATE TABLE t1(a); 758 CREATE TABLE aux.log(v); 759 CREATE TEMP TRIGGER tr1 AFTER INSERT ON t1 BEGIN 760 INSERT INTO log VALUES(new.a); 761 END; 762 INSERT INTO t1 VALUES(111); 763 SELECT v FROM log; 764} {111} 765 766do_execsql_test 18.1 { 767 ALTER TABLE t1 RENAME a TO b; 768} 769 770reset_db 771do_execsql_test 19.0 { 772 CREATE TABLE t1(a, b); 773 CREATE TABLE t2(c, d); 774 CREATE VIEW v2(e) AS SELECT coalesce(t2.c,t1.a) FROM t1, t2 WHERE t1.b=t2.d; 775} 776 777do_execsql_test 19.1 { 778 ALTER TABLE t1 RENAME a TO f; 779 SELECT sql FROM sqlite_master WHERE name = 'v2'; 780} { 781 {CREATE VIEW v2(e) AS SELECT coalesce(t2.c,t1.f) FROM t1, t2 WHERE t1.b=t2.d} 782} 783 784# 2019-01-08: https://www.sqlite.org/src/tktview/bc8d94f0fbd633fd9a051e3 785# 786# ALTER TABLE RENAME COLUMN does not work for tables that have redundant 787# UNIQUE constraints. 788# 789sqlite3 db :memory: 790do_execsql_test 20.100 { 791 CREATE TABLE t1(aaa,b,c,UNIQUE(aaA),PRIMARY KEY(aAa),UNIQUE(aAA)); 792 ALTER TABLE t1 RENAME aaa TO bbb; 793 SELECT sql FROM sqlite_master WHERE name='t1'; 794} {{CREATE TABLE t1(bbb,b,c,UNIQUE(bbb),PRIMARY KEY(bbb),UNIQUE(bbb))}} 795do_execsql_test 20.105 { 796 DROP TABLE t1; 797 CREATE TABLE t1(aaa,b,c,UNIQUE(aaA),PRIMARY KEY(aAa),UNIQUE(aAA))WITHOUT ROWID; 798 ALTER TABLE t1 RENAME aaa TO bbb; 799 SELECT sql FROM sqlite_master WHERE name='t1'; 800} {{CREATE TABLE t1(bbb,b,c,UNIQUE(bbb),PRIMARY KEY(bbb),UNIQUE(bbb))WITHOUT ROWID}} 801do_execsql_test 20.110 { 802 DROP TABLE t1; 803 CREATE TABLE t1(aa UNIQUE,bb UNIQUE,cc UNIQUE,UNIQUE(aA),PRIMARY KEY(bB),UNIQUE(cC)); 804 ALTER TABLE t1 RENAME aa TO xx; 805 ALTER TABLE t1 RENAME bb TO yy; 806 ALTER TABLE t1 RENAME cc TO zz; 807 SELECT sql FROM sqlite_master WHERE name='t1'; 808} {{CREATE TABLE t1(xx UNIQUE,yy UNIQUE,zz UNIQUE,UNIQUE(xx),PRIMARY KEY(yy),UNIQUE(zz))}} 809 810#------------------------------------------------------------------------- 811reset_db 812do_execsql_test 21.0 { 813 CREATE TABLE t1(a, b, c NOT NULL); 814 CREATE TRIGGER tr1 AFTER INSERT ON t1 WHEN new.c IS NOT NULL BEGIN 815 SELECT c NOT NULL FROM t1; 816 END; 817} 818 819do_execsql_test 21.1 { 820 ALTER TABLE t1 RENAME c TO d; 821} 822 823do_execsql_test 21.2 { 824 SELECT sql FROM sqlite_schema WHERE name IS 'tr1' 825} {{CREATE TRIGGER tr1 AFTER INSERT ON t1 WHEN new.d IS NOT NULL BEGIN 826 SELECT d NOT NULL FROM t1; 827 END} 828} 829 830#------------------------------------------------------------------------- 831# 832reset_db 833do_execsql_test 22.0 { 834 CREATE TABLE t1(a, b); 835 CREATE TABLE t2(c, othername, extra AS (c + 1)); 836 ALTER TABLE t1 RENAME a to othername; 837 SELECT sql FROM sqlite_schema; 838} { 839 {CREATE TABLE t1(othername, b)} 840 {CREATE TABLE t2(c, othername, extra AS (c + 1))} 841} 842 843#------------------------------------------------------------------------- 844# 845reset_db 846sqlite3_db_config db SQLITE_DBCONFIG_DQS_DDL 1 847sqlite3_db_config db SQLITE_DBCONFIG_DQS_DML 1 848do_execsql_test 22.0 { 849 CREATE TABLE t1(a, b); 850 CREATE INDEX x1 on t1("c"=b); 851 INSERT INTO t1 VALUES('a', 'a'); 852 INSERT INTO t1 VALUES('b', 'b'); 853 INSERT INTO t1 VALUES('c', 'c'); 854 ALTER TABLE t1 RENAME COLUMN a TO "c"; 855 PRAGMA integrity_check; 856} {ok} 857 858reset_db 859do_execsql_test 23.0 { 860 CREATE TABLE t1('a'"b",c); 861 CREATE INDEX i1 ON t1('a'); 862 INSERT INTO t1 VALUES(1,2), (3,4); 863 ALTER TABLE t1 RENAME COLUMN a TO x; 864 PRAGMA integrity_check; 865 SELECT sql FROM sqlite_schema WHERE name='t1'; 866 867} {ok {CREATE TABLE t1("x" "b",c)}} 868 869# 2022-02-04 870# Do not complain about syntax errors in the schema if 871# in PRAGMA writable_schema=ON mode. 872# 873reset_db 874do_execsql_test 23.0 { 875 CREATE TABLE t1(a INT, b REAL, c TEXT, d BLOB, e ANY); 876 CREATE INDEX t1abx ON t1(a, b, a+b) WHERE c IS NOT NULL; 877 CREATE VIEW t2 AS SELECT a+10, b*5.0, xyz FROM t1; -- unknown column "xyz" 878 CREATE TABLE schema_copy(name TEXT, sql TEXT); 879 INSERT INTO schema_copy(name,sql) SELECT name, sql FROM sqlite_schema WHERE sql IS NOT NULL; 880} {} 881do_catchsql_test 23.1 { 882 ALTER TABLE t1 RENAME COLUMN e TO eeee; 883} {1 {error in view t2: no such column: xyz}} 884do_execsql_test 23.2 { 885 SELECT name, sql FROM sqlite_master 886 EXCEPT SELECT name, sql FROM schema_copy; 887} {} 888do_execsql_test 23.3 { 889 BEGIN; 890 PRAGMA writable_schema=ON; 891 ALTER TABLE t1 RENAME COLUMN e TO eeee; 892 PRAGMA writable_schema=OFF; 893 SELECT name FROM sqlite_master 894 WHERE (name, sql) NOT IN (SELECT name, sql FROM schema_copy); 895 ROLLBACK; 896} {t1} 897do_execsql_test 23.10 { 898 DROP VIEW t2; 899 CREATE TRIGGER r3 AFTER INSERT ON t1 BEGIN 900 INSERT INTO t3(x,y) VALUES(new.a, new.b); 901 INSERT INTO t4(p) VALUES(new.c); -- no such table "t4" 902 END; 903 DELETE FROM schema_copy; 904 INSERT INTO schema_copy(name,sql) SELECT name, sql FROM sqlite_schema WHERE sql IS NOT NULL; 905} {} 906do_catchsql_test 23.11 { 907 ALTER TABLE t1 RENAME COLUMN e TO eeee; 908} {1 {error in trigger r3: no such table: main.t3}} 909do_execsql_test 23.12 { 910 SELECT name, sql FROM sqlite_master 911 EXCEPT SELECT name, sql FROM schema_copy; 912} {} 913do_execsql_test 23.13 { 914 BEGIN; 915 PRAGMA writable_schema=ON; 916 ALTER TABLE t1 RENAME COLUMN e TO eeee; 917 PRAGMA writable_schema=OFF; 918 SELECT name FROM sqlite_master 919 WHERE (name, sql) NOT IN (SELECT name, sql FROM schema_copy); 920 ROLLBACK; 921} {t1} 922do_execsql_test 23.20 { 923 CREATE TABLE t4(id INTEGER PRIMARY KEY, c1 INT, c2 INT); 924 CREATE VIEW t4v1 AS SELECT id, c1, c99 FROM t4; 925 DELETE FROM schema_copy; 926 INSERT INTO schema_copy SELECT name, sql FROM sqlite_schema; 927 BEGIN; 928 PRAGMA writable_schema=ON; 929 ALTER TABLE t4 RENAME to t4new; 930 SELECT name FROM sqlite_schema WHERE (name,sql) NOT IN (SELECT * FROM schema_copy); 931 ROLLBACK; 932} {t4new} 933 934finish_test 935