AryaWu/sqlite
0
1# 2019 January 232#3# The author disclaims copyright to this source code. In place of4# 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 13set testdir [file dirname $argv0]14source $testdir/tester.tcl15set testprefix altertab316 17# If SQLITE_OMIT_ALTERTABLE is defined, omit this file.18ifcapable !altertable {19 finish_test20 return21}22 23ifcapable windowfunc {24do_execsql_test 1.0 {25 CREATE TABLE t1(a, b);26 CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN27 SELECT sum(b) OVER w FROM t1 WINDOW w AS (ORDER BY a);28 END;29}30 31do_execsql_test 1.1 {32 ALTER TABLE t1 RENAME a TO aaa;33}34 35do_execsql_test 1.2 {36 SELECT sql FROM sqlite_master WHERE name='tr1'37} {{CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN38 SELECT sum(b) OVER w FROM t1 WINDOW w AS (ORDER BY aaa);39 END}}40 41do_execsql_test 1.3 {42 INSERT INTO t1 VALUES(1, 2);43}44} ;# windowfunc45 46#-------------------------------------------------------------------------47reset_db48do_execsql_test 2.0 {49 CREATE TABLE t1(a,b,c);50 CREATE TABLE t2(a,b,c);51 CREATE TRIGGER r1 AFTER INSERT ON t1 WHEN new.a NOT NULL BEGIN52 SELECT a,b, a name FROM t1 53 INTERSECT 54 SELECT a,b,c FROM t1 WHERE b>='d' ORDER BY name;55 SELECT new.c;56 END;57}58 59do_execsql_test 2.1 {60 ALTER TABLE t1 RENAME TO t1x;61 SELECT sql FROM sqlite_master WHERE name = 'r1';62} {{CREATE TRIGGER r1 AFTER INSERT ON "t1x" WHEN new.a NOT NULL BEGIN63 SELECT a,b, a name FROM "t1x" 64 INTERSECT 65 SELECT a,b,c FROM "t1x" WHERE b>='d' ORDER BY name;66 SELECT new.c;67 END}}68 69#-------------------------------------------------------------------------70reset_db71do_execsql_test 3.0 {72 CREATE TABLE t1(a, b, c, d);73 CREATE VIEW v1 AS SELECT * FROM t1 WHERE a=1 OR (b IN ());74}75 76do_execsql_test 3.1 {77 ALTER TABLE t1 RENAME b TO bbb;78}79 80do_execsql_test 3.2 {81 SELECT sql FROM sqlite_master WHERE name = 'v1'82} {{CREATE VIEW v1 AS SELECT * FROM t1 WHERE a=1 OR (b IN ())}}83 84#-------------------------------------------------------------------------85reset_db86do_execsql_test 4.0 {87 CREATE TABLE t1(a, b);88 CREATE TABLE t3(e, f);89 CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN90 INSERT INTO t2 VALUES(new.a, new.b);91 END;92}93 94do_catchsql_test 4.1.2 {95 BEGIN;96 ALTER TABLE t3 RENAME TO t4;97} {1 {error in trigger tr1: no such table: main.t2}}98do_execsql_test 4.1.2 {99 COMMIT;100}101do_execsql_test 4.1.3 {102 SELECT type, name, tbl_name, sql 103 FROM sqlite_master WHERE type='table' AND name!='t1';104} {table t3 t3 {CREATE TABLE t3(e, f)}}105 106 107do_catchsql_test 4.2.1 {108 BEGIN;109 ALTER TABLE t3 RENAME e TO eee;110} {1 {error in trigger tr1: no such table: main.t2}}111do_execsql_test 4.2.2 {112 COMMIT;113}114do_execsql_test 4.2.3 {115 SELECT type, name, tbl_name, sql 116 FROM sqlite_master WHERE type='table' AND name!='t1';117} {table t3 t3 {CREATE TABLE t3(e, f)}}118 119#-------------------------------------------------------------------------120reset_db121do_execsql_test 5.0 {122 CREATE TABLE t1 (123 c1 integer, c2, PRIMARY KEY(c1 collate rtrim),124 UNIQUE(c2)125 )126}127do_execsql_test 5.1 {128 ALTER TABLE t1 RENAME c1 TO c3;129}130 131#-------------------------------------------------------------------------132reset_db133do_execsql_test 6.0 {134 CREATE TEMPORARY TABLE Table0 (135 Col0 INTEGER, 136 PRIMARY KEY(Col0 COLLATE RTRIM), 137 FOREIGN KEY (Col0) REFERENCES Table0138 );139}140 141do_execsql_test 6.1 {142 ALTER TABLE Table0 RENAME Col0 TO Col0;143}144 145#-------------------------------------------------------------------------146reset_db147do_execsql_test 7.1.0 {148 CREATE TABLE t1(a,b,c);149 CREATE TRIGGER AFTER INSERT ON t1 BEGIN150 SELECT a, rank() OVER w1 FROM t1151 WINDOW w1 AS (PARTITION BY b, percent_rank() OVER w1);152 END;153}154 155do_execsql_test 7.1.2 {156 ALTER TABLE t1 RENAME TO t1x;157 SELECT sql FROM sqlite_master;158} {159 {CREATE TABLE "t1x"(a,b,c)}160 {CREATE TRIGGER AFTER INSERT ON "t1x" BEGIN161 SELECT a, rank() OVER w1 FROM "t1x"162 WINDOW w1 AS (PARTITION BY b, percent_rank() OVER w1);163 END}164}165 166do_execsql_test 7.2.1 {167 DROP TRIGGER after;168 CREATE TRIGGER AFTER INSERT ON t1x BEGIN169 SELECT a, rank() OVER w1 FROM t1x170 WINDOW w1 AS (PARTITION BY b, percent_rank() OVER w1 ORDER BY d);171 END;172}173 174do_catchsql_test 7.2.2 {175 ALTER TABLE t1x RENAME TO t1;176} {1 {error in trigger AFTER: no such column: d}}177 178#-------------------------------------------------------------------------179reset_db180do_execsql_test 8.0 {181 CREATE TABLE t0(c0);182 CREATE INDEX i0 ON t0('1' IN ());183}184do_execsql_test 8.1 {185 ALTER TABLE t0 RENAME TO t1;186 SELECT sql FROM sqlite_master;187} {188 {CREATE TABLE "t1"(c0)}189 {CREATE INDEX i0 ON "t1"('1' IN ())}190}191do_execsql_test 8.2.1 {192 CREATE TABLE t2 (c0);193 CREATE INDEX i2 ON t2((LIKELIHOOD(c0, 1.0) IN ()));194 ALTER TABLE t2 RENAME COLUMN c0 TO c1;195}196do_execsql_test 8.2.2 {197 SELECT sql FROM sqlite_master WHERE tbl_name = 't2';198} {199 {CREATE TABLE t2 (c1)} 200 {CREATE INDEX i2 ON t2((LIKELIHOOD(c1, 1.0) IN ()))}201}202do_test 8.2.3 {203 sqlite3 db2 test.db204 db2 eval { INSERT INTO t2 VALUES (1), (2), (3) }205 db close206} {}207db2 close208 209#-------------------------------------------------------------------------210reset_db211do_execsql_test 9.1 {212 CREATE TABLE t1(a,b,c);213 CREATE TRIGGER AFTER INSERT ON t1 WHEN new.a NOT NULL BEGIN214 SELECT true WHERE (SELECT a, b FROM (t1)) IN ();215 END;216}217do_execsql_test 9.2 {218 ALTER TABLE t1 RENAME TO t1x;219}220 221#-------------------------------------------------------------------------222reset_db223do_execsql_test 10.1 {224 CREATE TABLE t1(a, b, c);225 CREATE TABLE t2(a, b, c);226 CREATE VIEW v1 AS SELECT * FROM t1 WHERE (227 SELECT t1.a FROM t1, t2228 ) IN () OR t1.a=5;229}230 231do_execsql_test 10.2 {232 ALTER TABLE t2 RENAME TO t3;233 SELECT sql FROM sqlite_master WHERE name='v1';234} {235 {CREATE VIEW v1 AS SELECT * FROM t1 WHERE (236 SELECT t1.a FROM t1, t2237 ) IN () OR t1.a=5}238}239 240#-------------------------------------------------------------------------241reset_db242do_execsql_test 11.1 {243 CREATE TABLE t1(244 a,b,c,d,e,f,g,h,j,jj,jjb,k,aa,bb,cc,dd,ee DEFAULT 3.14,245 ff DEFAULT('hiccup'),Wg NOD NULL DEFAULT(false)246 );247 248 CREATE TRIGGER b AFTER INSERT ON t1 WHEN new.a BEGIN249 SELECT a, sum() w3 FROM t1 250 WINDOW b AS (ORDER BY NOT EXISTS(SELECT 1 FROM abc));251 END;252}253 254do_catchsql_test 11.2 {255 ALTER TABLE t1 RENAME TO t1x;256} {1 {error in trigger b: no such table: main.abc}}257 258do_execsql_test 11.3 {259 DROP TRIGGER b;260 CREATE TRIGGER b AFTER INSERT ON t1 WHEN new.a BEGIN261 SELECT a, sum() w3 FROM t1 262 WINDOW b AS (ORDER BY NOT EXISTS(SELECT 1 FROM t1));263 END;264} {}265 266do_execsql_test 11.4 {267 ALTER TABLE t1 RENAME TO t1x;268 SELECT sql FROM sqlite_master WHERE name = 'b';269} {270{CREATE TRIGGER b AFTER INSERT ON "t1x" WHEN new.a BEGIN271 SELECT a, sum() w3 FROM "t1x" 272 WINDOW b AS (ORDER BY NOT EXISTS(SELECT 1 FROM "t1x"));273 END}274}275 276#-------------------------------------------------------------------------277reset_db278do_execsql_test 12.1 {279CREATE TABLE t1(a,b,c,d,e,f,g,h,j,jj,Zjj,k,aQ,bb,cc,dd,ee DEFAULT 3.14,280ff DEFAULT('hiccup'),gg NOD NULL DEFAULT(false));281CREATE TRIGGER AFTER INSERT ON t1 WHEN new.a NOT NULL BEGIN282 283SELECT b () OVER , dense_rank() OVER d, d () OVER w1284FROM t1285WINDOW286w1 AS287( w1 ORDER BY d288ROWS BETWEEN 2 NOT IN(SELECT a, sum(d) w2,max(d)OVER FROM t1289WINDOW290w1 AS291(PARTITION BY d292ROWS BETWEEN '' PRECEDING AND false FOLLOWING),293d AS294(PARTITION BY b ORDER BY d295ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)296) PRECEDING AND 1 FOLLOWING),297w2 AS298(PARTITION BY b ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),299w3 AS300(PARTITION BY b ORDER BY d301ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)302;303SELECT a, sum(d) w2,max(d)OVER FROM t1304WINDOW305w1 AS306(PARTITION BY d307ROWS BETWEEN '' PRECEDING AND false FOLLOWING),308d AS309(PARTITION BY b ORDER BY d310ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)311;312 313END;314}315 316do_execsql_test 12.2 {317 ALTER TABLE t1 RENAME TO t1x;318}319 320#-------------------------------------------------------------------------321reset_db322do_execsql_test 13.1 {323 CREATE TABLE t1(a);324 CREATE TRIGGER r1 INSERT ON t1 BEGIN325 SELECT a(*) OVER (ORDER BY (SELECT 1)) FROM t1;326 END;327}328 329do_execsql_test 13.2 {330 ALTER TABLE t1 RENAME TO t1x;331}332 333#-------------------------------------------------------------------------334reset_db335do_execsql_test 14.1 {336 CREATE TABLE t1(a);337 CREATE TABLE t2(b);338 CREATE TRIGGER AFTER INSERT ON t1 BEGIN339 SELECT sum() FILTER (WHERE (SELECT sum() FILTER (WHERE 0)) AND a);340 END;341}342 343do_catchsql_test 14.2 {344 ALTER TABLE t1 RENAME TO t1x;345} {1 {error in trigger AFTER: no such column: a}}346 347#-------------------------------------------------------------------------348reset_db349 350do_execsql_test 16.1 {351 CREATE TABLE t1(x);352 CREATE TRIGGER AFTER INSERT ON t1 BEGIN353 SELECT (WITH t2 AS (WITH t3 AS (SELECT true)354 SELECT * FROM t3 ORDER BY true COLLATE nocase)355 SELECT 11);356 357 WITH t4 AS (SELECT * FROM t1) SELECT 33;358 END;359}360do_execsql_test 16.2 {361 ALTER TABLE t1 RENAME TO t1x;362}363 364#-------------------------------------------------------------------------365reset_db366do_execsql_test 17.1 {367 CREATE TABLE t1(a,b,c);368 CREATE TRIGGER AFTER INSERT ON t1 WHEN new.a NOT NULL BEGIN369 SELECT a () FILTER (WHERE a>0) FROM t1;370 END;371}372 373do_execsql_test 17.2 {374 ALTER TABLE t1 RENAME TO t1x;375 ALTER TABLE t1x RENAME a TO aaa;376 SELECT sql FROM sqlite_master WHERE type='trigger';377} {378{CREATE TRIGGER AFTER INSERT ON "t1x" WHEN new.aaa NOT NULL BEGIN379 SELECT a () FILTER (WHERE aaa>0) FROM "t1x";380 END}381}382 383#-------------------------------------------------------------------------384reset_db385do_execsql_test 18.1 {386 CREATE TABLE t1(a,b);387 CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN388 SELECT a, b FROM t1389 INTERSECT SELECT b,a FROM t1390 ORDER BY b IN (391 SELECT a UNION SELECT b392 FROM t1393 ORDER BY b COLLATE nocase394 )395 ;396 END;397}398 399do_catchsql_test 18.2 {400 SELECT a, b FROM t1401 INTERSECT 402 SELECT b,a FROM t1403 ORDER BY b IN (404 SELECT a UNION SELECT b405 FROM t1406 ORDER BY b COLLATE nocase407 );408} {1 {1st ORDER BY term does not match any column in the result set}}409 410do_catchsql_test 18.3 {411 ALTER TABLE t1 RENAME TO t1x;412} {1 {error in trigger r1: 1st ORDER BY term does not match any column in the result set}}413 414#-------------------------------------------------------------------------415reset_db 416do_execsql_test 19.0 {417 CREATE TABLE a(a,h CONSTRAINT a UNIQUE ON CONFLICT FAIL,CONSTRAINT a);418}419 420foreach {tn v res} {421 1 {422 CREATE VIEW q AS SELECT 123423 424 WINDOW x AS (425 RANGE BETWEEN UNBOUNDED PRECEDING AND INDEXED() OVER(426 PARTITION BY ( WITH x AS(VALUES(col1)) VALUES(453) )427 )428 FOLLOWING429 ) 430 } {1 {error in view q: no such column: col1}}431 432 2 {433 CREATE VIEW q AS SELECT434 CAST(CAST(CAST(CAST(CAST(CAST(CAST(CAST(CAST(CAST(CAST(RIGHT435 AS)AS)AS)AS)AS)AS)AS)AS)AS)AS)AS)WINDOW x AS(RANGE BETWEEN UNBOUNDED436 PRECEDING AND INDEXED(*)OVER(PARTITION BY437 CROSS,CROSS,NATURAL,sqlite_master(*)OVER a,(WITH a AS(VALUES(LEFT)UNION438 VALUES(LEFT)UNION VALUES(LEFT)UNION VALUES(LEFT)UNION VALUES(LEFT)UNION439 VALUES(LEFT)UNION VALUES(LEFT))VALUES(LEFT))IN440 STORED,LEFT,LEFT,LEFT,LEFT,LEFT,LEFT)*LEFT FOLLOWING)ORDER BY441 LEFT,LEFT,LEFT,LEFT,LEFT,LEFT,LEFT,LEFT,LEFT,LEFT,LEFT LIMIT442 LEFT,INDEXED(*)OVER(PARTITION BY443 CROSS,CROSS,CROSS,LEFT,INDEXED(*)OVER(PARTITION BY444 CROSS,CROSS,CROSS),INDEXED(*)OVER(PARTITION BY445 LEFT,LEFT,LEFT,LEFT,LEFT,LEFT,LEFT,LEFT,LEFT,LEFT,LEFT),446 LEFT,LEFT,INNER,CROSS,CROSS,CROSS,INNER,NATURAL ORDER BY447 OUTER,NATURAL,NATURAL,NATURAL,NATURAL,NATURAL,NATURAL,NATURAL,INNER,448 INNER,INNER NULLS LAST GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED449 FOLLOWING); 450 } {1 {error in view q: no such column: LEFT}}451 452 3 {453 CREATE VIEW q AS SELECT 99 WINDOW x AS (RANGE BETWEEN UNBOUNDED PRECEDING454 AND count(*)OVER(PARTITION BY (WITH a AS(VALUES(2),(x3))VALUES(0)))455 FOLLOWING)ORDER BY x2,sum(1)OVER(PARTITION BY avg(5)OVER(PARTITION BY x1));456 } {1 {error in view q: no such column: x3}}457} {458 do_execsql_test 19.$tn.1 "459 DROP VIEW IF EXISTS q;460 $v461 " {}462 463 do_catchsql_test 19.$tn.2 {464 ALTER TABLE a RENAME TO g;465 } $res466}467 468# Verify that the "if( pParse->nErr ) return WRC_Abort" at the top of the469# renameUnmapSelectCb() routine in alter.c (2019-12-04) is really required.470#471sqlite3 db :memory:472do_catchsql_test 20.10 {473 CREATE TABLE s(a, b, c);474 CREATE INDEX k ON s( (WITH s AS( SELECT * ) VALUES(2) ) IN () );475 ALTER TABLE s RENAME a TO a2;476} {1 {error in index k: no tables specified}}477 478#------------------------------------------------------------------------479#480reset_db481do_execsql_test 21.1 {482 CREATE TABLE s(col);483 CREATE VIEW v AS SELECT ( 484 WITH x(a) AS(SELECT * FROM s) VALUES(RIGHT) 485 ) IN() ; 486 CREATE TABLE a(a);487 ALTER TABLE a RENAME a TO b;488}489 490#------------------------------------------------------------------------491#492reset_db493do_execsql_test 22.1 {494 CREATE TABLE t1(a);495 CREATE VIEW v2(b) AS SELECT * FROM v2;496}497 498do_catchsql_test 22.2 {499 ALTER TABLE t1 RENAME TO t4;500} {1 {error in view v2: view v2 is circularly defined}}501 502do_execsql_test 22.3 {503 DROP VIEW v2;504 CREATE VIEW v2(b) AS WITH t3 AS (SELECT b FROM v2) SELECT * FROM t3;505}506 507do_catchsql_test 22.4 {508 ALTER TABLE t1 RENAME TO t4;509} {1 {error in view v2: view v2 is circularly defined}}510 511do_execsql_test 22.5 {512 DROP VIEW v2;513 CREATE VIEW v2(b) AS WITH t3 AS (SELECT b FROM v2) VALUES(1);514}515 516do_catchsql_test 22.6 {517 ALTER TABLE t1 RENAME TO t4;518} {0 {}}519 520#------------------------------------------------------------------------521#522reset_db523do_execsql_test 23.1 {524 CREATE TABLE t1(x);525 CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN526 UPDATE t1 SET (c,d)=((SELECT 1 FROM t1 JOIN t2 ON b=x),1);527 END;528}529 530do_catchsql_test 23.2 {531 ALTER TABLE t1 RENAME TO t1x;532} {1 {error in trigger r1: no such table: main.t2}}533 534#------------------------------------------------------------------------535#536reset_db537do_execsql_test 23.1 {538 CREATE TABLE v0 (a);539 CREATE VIEW v2 (v3) AS 540 WITH x1 AS (SELECT * FROM v2) 541 SELECT v3 AS x, v3 AS y FROM v2; 542}543 544do_catchsql_test 23.2 {545 SELECT * FROM v2546} {1 {view v2 is circularly defined}}547 548db close549sqlite3 db test.db550 551do_catchsql_test 23.3 {552 ALTER TABLE v0 RENAME TO t3 ;553} {1 {error in view v2: view v2 is circularly defined}}554 555#------------------------------------------------------------------------556#557reset_db558do_execsql_test 24.1 {559 CREATE TABLE v0 (v1); 560 CREATE TABLE v2 (v3 INTEGER UNIQUE ON CONFLICT ABORT); 561 CREATE TRIGGER x AFTER INSERT ON v2 WHEN ( 562 ( SELECT v1 AS PROMO_REVENUE FROM v2 JOIN v0 USING ( VALUE ) ) AND 0 ) 563 BEGIN 564 DELETE FROM v2; 565 END; 566}567do_catchsql_test 24.2 {568 ALTER TABLE v0 RENAME TO x ;569} {1 {error in trigger x: cannot join using column VALUE - column not present in both tables}}570 571do_execsql_test 24.3 {572 DROP TRIGGER x;573 CREATE TRIGGER x AFTER INSERT ON v2 WHEN (574 0 AND (SELECT rowid FROM v0)575 ) BEGIN576 DELETE FROM v2;577 END;578}579 580do_execsql_test 24.4 {581 ALTER TABLE v0 RENAME TO xyz;582 SELECT sql FROM sqlite_master WHERE type='trigger'583} {{CREATE TRIGGER x AFTER INSERT ON v2 WHEN (584 0 AND (SELECT rowid FROM "xyz")585 ) BEGIN586 DELETE FROM v2;587 END}}588 589#------------------------------------------------------------------------590#591reset_db592do_execsql_test 25.1 {593 CREATE TABLE t1(a, b, c);594 CREATE TABLE t2(a, b, c);595 CREATE TRIGGER ttt AFTER INSERT ON t1 BEGIN596 UPDATE t1 SET a=t2.a FROM t2 WHERE t1.a=t2.a; 597 END;598}599#do_execsql_test 25.2 {600# ALTER TABLE t2 RENAME COLUMN a TO aaa;601#}602 603#------------------------------------------------------------------------604#605reset_db606do_execsql_test 26.1 {607 CREATE TABLE t1(x);608 609 CREATE TABLE t3(y);610 CREATE TABLE t4(z);611 612 CREATE TRIGGER tr1 INSERT ON t3 BEGIN613 UPDATE t3 SET y=z FROM (SELECT z FROM t4);614 END;615 616 CREATE TRIGGER tr2 INSERT ON t3 BEGIN617 UPDATE t3 SET y=abc FROM (SELECT x AS abc FROM t1);618 END;619}620 621do_execsql_test 26.2 {622 ALTER TABLE t1 RENAME TO t2;623}624 625do_execsql_test 26.3 {626 ALTER TABLE t2 RENAME x TO xx;627}628 629do_execsql_test 26.4 {630 SELECT sql FROM sqlite_schema WHERE name='tr2'631} {632{CREATE TRIGGER tr2 INSERT ON t3 BEGIN633 UPDATE t3 SET y=abc FROM (SELECT xx AS abc FROM "t2");634 END}635}636 637# 2020-11-02 OSSFuzz 638#639reset_db640do_execsql_test 26.5 {641 CREATE TABLE t1(xx);642 CREATE TRIGGER xx INSERT ON t1 BEGIN643 UPDATE t1 SET xx=xx FROM(SELECT xx);644 END;645} {}646do_catchsql_test 26.6 {647 ALTER TABLE t1 RENAME TO t2;648} {1 {error in trigger xx: no such column: xx}}649 650 651#-------------------------------------------------------------------------652reset_db653 654do_execsql_test 27.1 {655 CREATE TABLE t1(a, b AS ((WITH w1 (xyz) AS ( SELECT t1.b FROM t1 ) SELECT 123) IN ()), c);656}657 658do_execsql_test 27.2 {659 ALTER TABLE t1 DROP COLUMN c;660 SELECT sql FROM sqlite_schema WHERE name = 't1';661} {662 {CREATE TABLE t1(a, b AS ((WITH w1 (xyz) AS ( SELECT t1.b FROM t1 ) SELECT 123) IN ()))}663}664 665#-------------------------------------------------------------------------666reset_db667do_execsql_test 28.1 {668 CREATE TABLE t1(a,b,c,d);669 CREATE TRIGGER AFTER INSERT ON t1 BEGIN670 UPDATE t1 SET (c,d)=(a,b);671 END;672 ALTER TABLE t1 RENAME TO t2;673}674 675do_execsql_test 28.2 {676 SELECT sql FROM sqlite_schema WHERE type='trigger'677} {{CREATE TRIGGER AFTER INSERT ON "t2" BEGIN678 UPDATE "t2" SET (c,d)=(a,b);679 END}}680 681 682#-------------------------------------------------------------------------683reset_db684do_execsql_test 29.1 {685 CREATE TABLE t1(x, y);686 CREATE TRIGGER Trigger1 DELETE ON t1 687 BEGIN 688 SELECT t1.*, t1.x FROM t1 ORDER BY t1.x;689 END;690}691 692 693do_execsql_test 29.2 {694 ALTER TABLE t1 RENAME x TO z;695}696 697do_execsql_test 29.3 {698 ALTER TABLE t1 RENAME TO t2;699}700 701do_execsql_test 29.4 {702 CREATE TRIGGER tr2 AFTER DELETE ON t2 BEGIN703 SELECT z, y FROM (704 SELECT t2.* FROM t2705 );706 END;707}708 709do_execsql_test 29.5 {710 DELETE FROM t2711}712 713do_execsql_test 29.6 {714 ALTER TABLE t2 RENAME TO t3;715}716 717do_execsql_test 29.7 {718 SELECT sql FROM sqlite_schema WHERE type='trigger'719} {720 {CREATE TRIGGER Trigger1 DELETE ON "t3" 721 BEGIN 722 SELECT "t3".*, "t3".z FROM "t3" ORDER BY "t3".z;723 END}724 {CREATE TRIGGER tr2 AFTER DELETE ON "t3" BEGIN725 SELECT z, y FROM (726 SELECT "t3".* FROM "t3"727 );728 END}729}730 731#-------------------------------------------------------------------------732reset_db733do_execsql_test 30.0 {734 CREATE TABLE t1(a, b);735 CREATE VIEW v1 AS 736 SELECT ( VALUES(a), (b) ) FROM (737 SELECT a, b FROM t1738 )739 ;740}741 742do_execsql_test 30.1 {743 SELECT * FROM v1744}745 746do_execsql_test 30.1 {747 ALTER TABLE t1 RENAME TO t2;748}749do_execsql_test 30.2 {750 SELECT sql FROM sqlite_schema WHERE type='view'751} {752 {CREATE VIEW v1 AS 753 SELECT ( VALUES(a), (b) ) FROM (754 SELECT a, b FROM "t2"755 )}756}757 758#-------------------------------------------------------------------------759reset_db760do_execsql_test 31.0 {761 CREATE TABLE t1(ii INTEGER PRIMARY KEY, tt INTEGER, rr REAL);762 WITH s(i) AS (763 SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<50000764 )765 INSERT INTO t1 SELECT NULL, i, 5.0 FROM s;766}767 768do_test 31.1 {769 set pg [db one {PRAGMA page_count}]770 execsql {771 ALTER TABLE t1 DROP COLUMN tt;772 }773 set pg2 [db one {PRAGMA page_count}]774 expr $pg==$pg2775} {1}776 777do_execsql_test 31.2 {778 SELECT rr FROM t1 LIMIT 1779} {5.0}780 781#-------------------------------------------------------------------------782reset_db783do_execsql_test 32.1.0 {784 CREATE TABLE t1(785 a INT,786 b INT,787 -- comment with comma788 c INT789 );790}791do_execsql_test 32.1.1 {792 ALTER TABLE t1 DROP COLUMN c;793}794do_execsql_test 32.1.2 {795 SELECT sql FROM sqlite_schema796} {{CREATE TABLE t1(797 a INT,798 b INT)}}799 800reset_db801do_execsql_test 32.2.0 {802 CREATE TABLE t1(803 a INT,804 b INT,805 -- comment with, comma806 c INT807 );808}809do_execsql_test 32.2.1 {810 ALTER TABLE t1 DROP COLUMN c;811}812do_execsql_test 32.2.2 {813 SELECT sql FROM sqlite_schema814} {{CREATE TABLE t1(815 a INT,816 b INT)}}817 818#-------------------------------------------------------------------------819reset_db820do_execsql_test 33.1 {821 CREATE TABLE x1(a TEXT, b INTEGER, c CHECK(c!=0));822}823 824do_execsql_test 33.2 {825 ALTER TABLE x1 DROP COLUMN b;826 SELECT sql FROM sqlite_schema;827} {{CREATE TABLE x1(a TEXT, c CHECK(c!=0))}}828 829finish_test830