AryaWu/sqlite
0
1# 2004 November 102#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# This file implements regression tests for SQLite library. The12# focus of this script is testing the ALTER TABLE statement.13#14# $Id: alter.test,v 1.32 2009/03/24 15:08:10 drh Exp $15#16 17set testdir [file dirname $argv0]18source $testdir/tester.tcl19 20# If SQLITE_OMIT_ALTERTABLE is defined, omit this file.21ifcapable !altertable {22 finish_test23 return24}25 26#----------------------------------------------------------------------27# Test organization:28#29# alter-1.1.* - alter-1.7.*: Basic tests of ALTER TABLE, including tables30# with implicit and explicit indices. These tests came from an earlier31# fork of SQLite that also supported ALTER TABLE.32# alter-1.8.*: Tests for ALTER TABLE when the table resides in an 33# attached database.34# alter-1.9.*: Tests for ALTER TABLE when their is whitespace between the35# table name and left parenthesis token. i.e: 36# "CREATE TABLE abc (a, b, c);"37# alter-2.*: Test error conditions and messages.38# alter-3.*: Test ALTER TABLE on tables that have TRIGGERs attached to them.39# alter-4.*: Test ALTER TABLE on tables that have AUTOINCREMENT fields.40# ...41# alter-12.*: Test ALTER TABLE on views.42#43 44# Create some tables to rename. Be sure to include some TEMP tables45# and some tables with odd names.46#47do_test alter-1.1 {48 ifcapable tempdb {49 set ::temp TEMP50 } else {51 set ::temp {}52 }53 execsql [subst -nocommands {54 CREATE TABLE t1(a,b);55 INSERT INTO t1 VALUES(1,2);56 CREATE TABLE [t1'x1](c UNIQUE, b PRIMARY KEY);57 INSERT INTO [t1'x1] VALUES(3,4);58 CREATE INDEX t1i1 ON T1(B);59 CREATE INDEX t1i2 ON t1(a,b);60 CREATE INDEX i3 ON [t1'x1](b,c);61 CREATE $::temp TABLE "temp table"(e,f,g UNIQUE);62 CREATE INDEX i2 ON [temp table](f);63 INSERT INTO [temp table] VALUES(5,6,7);64 }]65 execsql {66 SELECT 't1', * FROM t1;67 SELECT 't1''x1', * FROM "t1'x1";68 SELECT * FROM [temp table];69 }70} {t1 1 2 t1'x1 3 4 5 6 7}71do_test alter-1.2 {72 execsql [subst {73 CREATE $::temp TABLE objlist(type, name, tbl_name);74 INSERT INTO objlist SELECT type, name, tbl_name 75 FROM sqlite_master WHERE NAME!='objlist';76 }]77 ifcapable tempdb {78 execsql {79 INSERT INTO objlist SELECT type, name, tbl_name 80 FROM temp.sqlite_master WHERE NAME!='objlist';81 }82 }83 84 execsql {85 SELECT type, name, tbl_name FROM objlist ORDER BY tbl_name, type desc, name;86 }87} [list \88 table t1 t1 \89 index t1i1 t1 \90 index t1i2 t1 \91 table t1'x1 t1'x1 \92 index i3 t1'x1 \93 index {sqlite_autoindex_t1'x1_1} t1'x1 \94 index {sqlite_autoindex_t1'x1_2} t1'x1 \95 table {temp table} {temp table} \96 index i2 {temp table} \97 index {sqlite_autoindex_temp table_1} {temp table} \98 ]99 100# Make some changes101#102integrity_check alter-1.3.0103do_test alter-1.3 {104 execsql {105 ALTER TABLE [T1] RENAME to [-t1-];106 ALTER TABLE "t1'x1" RENAME TO T2;107 ALTER TABLE [temp table] RENAME to TempTab;108 }109} {}110integrity_check alter-1.3.1111do_test alter-1.4 {112 execsql {113 SELECT 't1', * FROM [-t1-];114 SELECT 't2', * FROM t2;115 SELECT * FROM temptab;116 }117} {t1 1 2 t2 3 4 5 6 7}118do_test alter-1.5 {119 execsql {120 DELETE FROM objlist;121 INSERT INTO objlist SELECT type, name, tbl_name122 FROM sqlite_master WHERE NAME!='objlist';123 }124 catchsql {125 INSERT INTO objlist SELECT type, name, tbl_name 126 FROM sqlite_temp_master WHERE NAME!='objlist';127 }128 execsql {129 SELECT type, name, tbl_name FROM objlist ORDER BY tbl_name, type desc, name;130 }131} [list \132 table -t1- -t1- \133 index t1i1 -t1- \134 index t1i2 -t1- \135 table T2 T2 \136 index i3 T2 \137 index {sqlite_autoindex_T2_1} T2 \138 index {sqlite_autoindex_T2_2} T2 \139 table {TempTab} {TempTab} \140 index i2 {TempTab} \141 index {sqlite_autoindex_TempTab_1} {TempTab} \142 ]143 144# Make sure the changes persist after restarting the database.145# (The TEMP table will not persist, of course.)146#147ifcapable tempdb {148 do_test alter-1.6 {149 db close150 sqlite3 db test.db151 set DB [sqlite3_connection_pointer db]152 execsql {153 CREATE TEMP TABLE objlist(type, name, tbl_name);154 INSERT INTO objlist SELECT type, name, tbl_name FROM sqlite_master;155 INSERT INTO objlist 156 SELECT type, name, tbl_name FROM temp.sqlite_master 157 WHERE NAME!='objlist';158 SELECT type, name, tbl_name FROM objlist 159 ORDER BY tbl_name, type desc, name;160 }161 } [list \162 table -t1- -t1- \163 index t1i1 -t1- \164 index t1i2 -t1- \165 table T2 T2 \166 index i3 T2 \167 index {sqlite_autoindex_T2_1} T2 \168 index {sqlite_autoindex_T2_2} T2 \169 ]170} else {171 execsql {172 DROP TABLE TempTab;173 }174}175 176# Create bogus application-defined functions for functions used 177# internally by ALTER TABLE, to ensure that ALTER TABLE falls back178# to the built-in functions.179#180proc failing_app_func {args} {error "bad function"}181do_test alter-1.7-prep {182 db func substr failing_app_func183 db func like failing_app_func184 db func sqlite_rename_table failing_app_func185 db func sqlite_rename_trigger failing_app_func186 db func sqlite_rename_parent failing_app_func187 catchsql {SELECT substr(name,1,3) FROM sqlite_master}188} {1 {bad function}}189 190# Make sure the ALTER TABLE statements work with the191# non-callback API192#193do_test alter-1.7 {194 stepsql $DB {195 ALTER TABLE [-t1-] RENAME to [*t1*];196 ALTER TABLE T2 RENAME TO [<t2>];197 }198 execsql {199 DELETE FROM objlist;200 INSERT INTO objlist SELECT type, name, tbl_name201 FROM sqlite_master WHERE NAME!='objlist';202 }203 catchsql {204 INSERT INTO objlist SELECT type, name, tbl_name 205 FROM sqlite_temp_master WHERE NAME!='objlist';206 }207 execsql {208 SELECT type, name, tbl_name FROM objlist ORDER BY tbl_name, type desc, name;209 }210} [list \211 table *t1* *t1* \212 index t1i1 *t1* \213 index t1i2 *t1* \214 table <t2> <t2> \215 index i3 <t2> \216 index {sqlite_autoindex_<t2>_1} <t2> \217 index {sqlite_autoindex_<t2>_2} <t2> \218 ]219 220# Check that ALTER TABLE works on attached databases.221#222ifcapable attach {223 do_test alter-1.8.1 {224 forcedelete test2.db225 forcedelete test2.db-journal226 execsql {227 ATTACH 'test2.db' AS aux;228 }229 } {}230 do_test alter-1.8.2 {231 execsql {232 CREATE TABLE t4(a PRIMARY KEY, b, c);233 CREATE TABLE aux.t4(a PRIMARY KEY, b, c);234 CREATE INDEX i4 ON t4(b);235 CREATE INDEX aux.i4 ON t4(b);236 }237 } {}238 do_test alter-1.8.3 {239 execsql {240 INSERT INTO t4 VALUES('main', 'main', 'main');241 INSERT INTO aux.t4 VALUES('aux', 'aux', 'aux');242 SELECT * FROM t4 WHERE a = 'main';243 }244 } {main main main}245 do_test alter-1.8.4 {246 execsql {247 ALTER TABLE t4 RENAME TO t5;248 SELECT * FROM t4 WHERE a = 'aux';249 }250 } {aux aux aux}251 do_test alter-1.8.5 {252 execsql {253 SELECT * FROM t5;254 }255 } {main main main}256 do_test alter-1.8.6 {257 execsql {258 SELECT * FROM t5 WHERE b = 'main';259 }260 } {main main main}261 do_test alter-1.8.7 {262 execsql {263 ALTER TABLE aux.t4 RENAME TO t5;264 SELECT * FROM aux.t5 WHERE b = 'aux';265 }266 } {aux aux aux}267}268 269do_test alter-1.9.1 {270 execsql {271 CREATE TABLE tbl1 (a, b, c);272 INSERT INTO tbl1 VALUES(1, 2, 3);273 }274} {}275do_test alter-1.9.2 {276 execsql {277 SELECT * FROM tbl1;278 }279} {1 2 3}280do_test alter-1.9.3 {281 execsql {282 ALTER TABLE tbl1 RENAME TO tbl2;283 SELECT * FROM tbl2;284 }285} {1 2 3}286do_test alter-1.9.4 {287 execsql {288 DROP TABLE tbl2;289 }290} {}291 292# Test error messages293#294do_test alter-2.1 {295 catchsql {296 ALTER TABLE none RENAME TO hi;297 }298} {1 {no such table: none}}299do_test alter-2.2 {300 execsql {301 CREATE TABLE t3(p,q,r);302 }303 catchsql {304 ALTER TABLE [<t2>] RENAME TO t3;305 }306} {1 {there is already another table or index with this name: t3}}307do_test alter-2.3 {308 catchsql {309 ALTER TABLE [<t2>] RENAME TO i3;310 }311} {1 {there is already another table or index with this name: i3}}312do_test alter-2.4 {313 catchsql {314 ALTER TABLE SqLiTe_master RENAME TO master;315 }316} {1 {table sqlite_master may not be altered}}317do_test alter-2.5 {318 catchsql {319 ALTER TABLE t3 RENAME TO sqlite_t3;320 }321} {1 {object name reserved for internal use: sqlite_t3}}322do_test alter-2.6 {323 catchsql {324 ALTER TABLE t3 ADD COLUMN (ALTER TABLE t3 ADD COLUMN);325 }326} {1 {near "(": syntax error}}327 328# If this compilation does not include triggers, omit the alter-3.* tests.329ifcapable trigger {330 331#-----------------------------------------------------------------------332# Tests alter-3.* test ALTER TABLE on tables that have triggers.333#334# alter-3.1.*: ALTER TABLE with triggers.335# alter-3.2.*: Test that the ON keyword cannot be used as a database,336# table or column name unquoted. This is done because part of the337# ALTER TABLE code (specifically the implementation of SQL function338# "sqlite_alter_trigger") will break in this case.339# alter-3.3.*: ALTER TABLE with TEMP triggers (todo).340#341 342# An SQL user-function for triggers to fire, so that we know they343# are working.344proc trigfunc {args} {345 set ::TRIGGER $args346}347db func trigfunc trigfunc348 349do_test alter-3.1.0 {350 execsql {351 CREATE TABLE t6(a, b, c);352 -- Different case for the table name in the trigger.353 CREATE TRIGGER trig1 AFTER INSERT ON T6 BEGIN354 SELECT trigfunc('trig1', new.a, new.b, new.c);355 END;356 }357} {}358do_test alter-3.1.1 {359 execsql {360 INSERT INTO t6 VALUES(1, 2, 3);361 }362 set ::TRIGGER363} {trig1 1 2 3}364do_test alter-3.1.2 {365 execsql {366 ALTER TABLE t6 RENAME TO t7;367 INSERT INTO t7 VALUES(4, 5, 6);368 }369 set ::TRIGGER370} {trig1 4 5 6}371do_test alter-3.1.3 {372 execsql {373 DROP TRIGGER trig1;374 }375} {}376do_test alter-3.1.4 {377 execsql {378 CREATE TRIGGER trig2 AFTER INSERT ON main.t7 BEGIN379 SELECT trigfunc('trig2', new.a, new.b, new.c);380 END;381 INSERT INTO t7 VALUES(1, 2, 3);382 }383 set ::TRIGGER384} {trig2 1 2 3}385do_test alter-3.1.5 {386 execsql {387 ALTER TABLE t7 RENAME TO t8;388 INSERT INTO t8 VALUES(4, 5, 6);389 }390 set ::TRIGGER391} {trig2 4 5 6}392do_test alter-3.1.6 {393 execsql {394 DROP TRIGGER trig2;395 }396} {}397do_test alter-3.1.7 {398 execsql {399 CREATE TRIGGER trig3 AFTER INSERT ON main.'t8'BEGIN400 SELECT trigfunc('trig3', new.a, new.b, new.c);401 END;402 INSERT INTO t8 VALUES(1, 2, 3);403 }404 set ::TRIGGER405} {trig3 1 2 3}406do_test alter-3.1.8 {407 execsql {408 ALTER TABLE t8 RENAME TO t9;409 INSERT INTO t9 VALUES(4, 5, 6);410 }411 set ::TRIGGER412} {trig3 4 5 6}413 414# Make sure "ON" cannot be used as a database, table or column name without415# quoting. Otherwise the sqlite_alter_trigger() function might not work.416forcedelete test3.db417forcedelete test3.db-journal418ifcapable attach {419 do_test alter-3.2.1 {420 catchsql {421 ATTACH 'test3.db' AS ON;422 }423 } {1 {near "ON": syntax error}}424 do_test alter-3.2.2 {425 catchsql {426 ATTACH 'test3.db' AS 'ON';427 }428 } {0 {}}429 do_test alter-3.2.3 {430 catchsql {431 CREATE TABLE ON.t1(a, b, c); 432 }433 } {1 {near "ON": syntax error}}434 do_test alter-3.2.4 {435 catchsql {436 CREATE TABLE 'ON'.t1(a, b, c); 437 }438 } {0 {}}439 do_test alter-3.2.4 {440 catchsql {441 CREATE TABLE 'ON'.ON(a, b, c); 442 }443 } {1 {near "ON": syntax error}}444 do_test alter-3.2.5 {445 catchsql {446 CREATE TABLE 'ON'.'ON'(a, b, c); 447 }448 } {0 {}}449}450do_test alter-3.2.6 {451 catchsql {452 CREATE TABLE t10(a, ON, c);453 }454} {1 {near "ON": syntax error}}455do_test alter-3.2.7 {456 catchsql {457 CREATE TABLE t10(a, 'ON', c);458 }459} {0 {}}460do_test alter-3.2.8 {461 catchsql {462 CREATE TRIGGER trig4 AFTER INSERT ON ON BEGIN SELECT 1; END;463 }464} {1 {near "ON": syntax error}}465ifcapable attach {466 do_test alter-3.2.9 {467 catchsql {468 CREATE TRIGGER 'on'.trig4 AFTER INSERT ON 'ON' BEGIN SELECT 1; END;469 }470 } {0 {}}471}472do_test alter-3.2.10 {473 execsql {474 DROP TABLE t10;475 }476} {}477 478do_test alter-3.3.1 {479 execsql [subst {480 CREATE TABLE tbl1(a, b, c);481 CREATE $::temp TRIGGER trig1 AFTER INSERT ON tbl1 BEGIN482 SELECT trigfunc('trig1', new.a, new.b, new.c);483 END;484 }]485} {}486do_test alter-3.3.2 {487 execsql {488 INSERT INTO tbl1 VALUES('a', 'b', 'c');489 }490 set ::TRIGGER491} {trig1 a b c}492do_test alter-3.3.3 {493 execsql {494 ALTER TABLE tbl1 RENAME TO tbl2;495 INSERT INTO tbl2 VALUES('d', 'e', 'f');496 } 497 set ::TRIGGER498} {trig1 d e f}499do_test alter-3.3.4 {500 execsql [subst {501 CREATE $::temp TRIGGER trig2 AFTER UPDATE ON tbl2 BEGIN502 SELECT trigfunc('trig2', new.a, new.b, new.c);503 END;504 }] 505} {}506do_test alter-3.3.5 {507 execsql {508 ALTER TABLE tbl2 RENAME TO tbl3;509 INSERT INTO tbl3 VALUES('g', 'h', 'i');510 } 511 set ::TRIGGER512} {trig1 g h i}513do_test alter-3.3.6 {514 execsql {515 UPDATE tbl3 SET a = 'G' where a = 'g';516 } 517 set ::TRIGGER518} {trig2 G h i}519do_test alter-3.3.7 {520 execsql {521 DROP TABLE tbl3;522 }523} {}524ifcapable tempdb {525 do_test alter-3.3.8 {526 execsql {527 SELECT * FROM temp.sqlite_master WHERE type = 'trigger';528 }529 } {}530}531 532} ;# ifcapable trigger533 534# If the build does not include AUTOINCREMENT fields, omit alter-4.*.535ifcapable autoinc {536 537do_test alter-4.1 {538 execsql {539 CREATE TABLE tbl1(a INTEGER PRIMARY KEY AUTOINCREMENT);540 INSERT INTO tbl1 VALUES(10);541 }542} {}543do_test alter-4.2 {544 execsql {545 INSERT INTO tbl1 VALUES(NULL);546 SELECT a FROM tbl1;547 }548} {10 11}549do_test alter-4.3 {550 execsql {551 ALTER TABLE tbl1 RENAME TO tbl2;552 DELETE FROM tbl2;553 INSERT INTO tbl2 VALUES(NULL);554 SELECT a FROM tbl2;555 }556} {12}557do_test alter-4.4 {558 execsql {559 DROP TABLE tbl2;560 }561} {}562 563} ;# ifcapable autoinc564 565# Test that it is Ok to execute an ALTER TABLE immediately after566# opening a database.567do_test alter-5.1 {568 execsql {569 CREATE TABLE tbl1(a, b, c);570 INSERT INTO tbl1 VALUES('x', 'y', 'z');571 }572} {}573do_test alter-5.2 {574 sqlite3 db2 test.db575 execsql {576 ALTER TABLE tbl1 RENAME TO tbl2;577 SELECT * FROM tbl2;578 } db2579} {x y z}580do_test alter-5.3 {581 db2 close582} {}583 584foreach tblname [execsql {585 SELECT name FROM sqlite_master586 WHERE type='table' AND name NOT GLOB 'sqlite*'587}] {588 execsql "DROP TABLE \"$tblname\""589}590 591set ::tbl_name "abc\uABCDdef"592do_test alter-6.1 {593 string length $::tbl_name594} {7}595do_test alter-6.2 {596 execsql "597 CREATE TABLE ${tbl_name}(a, b, c);598 "599 set ::oid [execsql {SELECT max(oid) FROM sqlite_master}]600 execsql "601 SELECT sql FROM sqlite_master WHERE oid = $::oid;602 "603} "{CREATE TABLE ${::tbl_name}(a, b, c)}"604execsql "605 SELECT * FROM ${::tbl_name}606"607set ::tbl_name2 "abcXdef"608do_test alter-6.3 {609 execsql "610 ALTER TABLE $::tbl_name RENAME TO $::tbl_name2 611 "612 execsql "613 SELECT sql FROM sqlite_master WHERE oid = $::oid614 "615} "{CREATE TABLE \"${::tbl_name2}\"(a, b, c)}"616do_test alter-6.4 {617 execsql "618 ALTER TABLE $::tbl_name2 RENAME TO $::tbl_name619 "620 execsql "621 SELECT sql FROM sqlite_master WHERE oid = $::oid622 "623} "{CREATE TABLE \"${::tbl_name}\"(a, b, c)}"624set ::col_name ghi\1234\jkl625do_test alter-6.5 {626 execsql "627 ALTER TABLE $::tbl_name ADD COLUMN $::col_name VARCHAR628 "629 execsql "630 SELECT sql FROM sqlite_master WHERE oid = $::oid631 "632} "{CREATE TABLE \"${::tbl_name}\"(a, b, c, $::col_name VARCHAR)}"633set ::col_name2 B\3421\A634do_test alter-6.6 {635 db close636 sqlite3 db test.db637 execsql "638 ALTER TABLE $::tbl_name ADD COLUMN $::col_name2639 "640 execsql "641 SELECT sql FROM sqlite_master WHERE oid = $::oid642 "643} "{CREATE TABLE \"${::tbl_name}\"(a, b, c, $::col_name VARCHAR, $::col_name2)}"644do_test alter-6.7 {645 execsql "646 INSERT INTO ${::tbl_name} VALUES(1, 2, 3, 4, 5);647 SELECT $::col_name, $::col_name2 FROM $::tbl_name;648 "649} {4 5}650 651# Ticket #1665: Make sure ALTER TABLE ADD COLUMN works on a table652# that includes a COLLATE clause.653#654do_realnum_test alter-7.1 {655 execsql {656 CREATE TABLE t1(a TEXT COLLATE BINARY);657 ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;658 INSERT INTO t1 VALUES(1,'-2');659 INSERT INTO t1 VALUES(5.4e-08,'5.4e-08');660 SELECT typeof(a), a, typeof(b), b FROM t1;661 }662} {text 1 integer -2 text 5.4e-08 real 5.4e-08}663 664# Make sure that when a column is added by ALTER TABLE ADD COLUMN and has665# a default value that the default value is used by aggregate functions.666#667do_test alter-8.1 {668 execsql {669 CREATE TABLE t2(a INTEGER);670 INSERT INTO t2 VALUES(1);671 INSERT INTO t2 VALUES(1);672 INSERT INTO t2 VALUES(2);673 ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;674 SELECT sum(b) FROM t2;675 }676} {27}677do_test alter-8.2 {678 execsql {679 SELECT a, sum(b) FROM t2 GROUP BY a;680 }681} {1 18 2 9}682 683#--------------------------------------------------------------------------684# alter-9.X - Special test: Make sure the sqlite_rename_column() and685# rename_table() functions do not crash when handed bad input.686#687sqlite3_test_control SQLITE_TESTCTRL_INTERNAL_FUNCTIONS db688do_test alter-9.1 {689 execsql {SELECT SQLITE_RENAME_COLUMN(0,0,0,0,0,0,0,0,0)}690} {{}}691foreach {tn sql} {692 1 { SELECT SQLITE_RENAME_TABLE(0,0,0,0,0,0,0) }693 2 { SELECT SQLITE_RENAME_TABLE(10,20,30,40,50,60,70) }694 3 { SELECT SQLITE_RENAME_TABLE('foo','foo','foo','foo','foo','foo','foo') }695} {696 do_test alter-9.2.$tn {697 catch { execsql $sql }698 } 1699}700sqlite3_test_control SQLITE_TESTCTRL_INTERNAL_FUNCTIONS db701 702# If the INTERNAL_FUNCTIONS test-control is disabled (which is the default),703# then the sqlite_rename_table() SQL function is not accessible to ordinary SQL.704#705do_catchsql_test alter-9.3 {706 SELECT sqlite_rename_table(0,0,0,0,0,0,0);707} {1 {no such function: sqlite_rename_table}}708 709#------------------------------------------------------------------------710# alter-10.X - Make sure ALTER TABLE works with multi-byte UTF-8 characters 711# in the names.712#713do_test alter-10.1 {714 execsql "CREATE TABLE xyz(x UNIQUE)"715 execsql "ALTER TABLE xyz RENAME TO xyz\u1234abc"716 execsql {SELECT name FROM sqlite_master WHERE name GLOB 'xyz*'}717} [list xyz\u1234abc]718do_test alter-10.2 {719 execsql {SELECT name FROM sqlite_master WHERE name GLOB 'sqlite_autoindex*'}720} [list sqlite_autoindex_xyz\u1234abc_1]721do_test alter-10.3 {722 execsql "ALTER TABLE xyz\u1234abc RENAME TO xyzabc"723 execsql {SELECT name FROM sqlite_master WHERE name GLOB 'xyz*'}724} [list xyzabc]725do_test alter-10.4 {726 execsql {SELECT name FROM sqlite_master WHERE name GLOB 'sqlite_autoindex*'}727} [list sqlite_autoindex_xyzabc_1]728 729do_test alter-11.1 {730 sqlite3_exec db {CREATE TABLE t11(%c6%c6)}731 execsql {732 ALTER TABLE t11 ADD COLUMN abc;733 }734 catchsql {735 ALTER TABLE t11 ADD COLUMN abc;736 }737} {1 {duplicate column name: abc}}738set isutf16 [regexp 16 [db one {PRAGMA encoding}]]739if {!$isutf16} {740 do_test alter-11.2 {741 execsql {INSERT INTO t11 VALUES(1,2)}742 sqlite3_exec db {SELECT %c6%c6 AS xyz, abc FROM t11}743 } {0 {xyz abc 1 2}}744}745do_test alter-11.3 {746 sqlite3_exec db {CREATE TABLE t11b("%81%82%83" text)}747 execsql {748 ALTER TABLE t11b ADD COLUMN abc;749 }750 catchsql {751 ALTER TABLE t11b ADD COLUMN abc;752 }753} {1 {duplicate column name: abc}}754if {!$isutf16} {755 do_test alter-11.4 {756 execsql {INSERT INTO t11b VALUES(3,4)}757 sqlite3_exec db {SELECT %81%82%83 AS xyz, abc FROM t11b}758 } {0 {xyz abc 3 4}}759 do_test alter-11.5 {760 sqlite3_exec db {SELECT [%81%82%83] AS xyz, abc FROM t11b}761 } {0 {xyz abc 3 4}}762 do_test alter-11.6 {763 sqlite3_exec db {SELECT "%81%82%83" AS xyz, abc FROM t11b}764 } {0 {xyz abc 3 4}}765}766do_test alter-11.7 {767 sqlite3_exec db {CREATE TABLE t11c(%81%82%83 text)}768 execsql {769 ALTER TABLE t11c ADD COLUMN abc;770 }771 catchsql {772 ALTER TABLE t11c ADD COLUMN abc;773 }774} {1 {duplicate column name: abc}}775if {!$isutf16} {776 do_test alter-11.8 {777 execsql {INSERT INTO t11c VALUES(5,6)}778 sqlite3_exec db {SELECT %81%82%83 AS xyz, abc FROM t11c}779 } {0 {xyz abc 5 6}}780 do_test alter-11.9 {781 sqlite3_exec db {SELECT [%81%82%83] AS xyz, abc FROM t11c}782 } {0 {xyz abc 5 6}}783 do_test alter-11.10 {784 sqlite3_exec db {SELECT "%81%82%83" AS xyz, abc FROM t11c}785 } {0 {xyz abc 5 6}}786}787 788do_test alter-12.1 {789 execsql {790 CREATE TABLE t12(a, b, c);791 CREATE VIEW v1 AS SELECT * FROM t12;792 }793} {}794do_test alter-12.2 {795 catchsql {796 ALTER TABLE v1 RENAME TO v2;797 }798} {1 {view v1 may not be altered}}799do_test alter-12.3 {800 execsql { SELECT * FROM v1; }801} {}802do_test alter-12.4 {803 db close804 sqlite3 db test.db805 execsql { SELECT * FROM v1; }806} {}807do_test alter-12.5 {808 catchsql { 809 ALTER TABLE v1 ADD COLUMN new_column;810 }811} {1 {Cannot add a column to a view}}812 813# Ticket #3102:814# Verify that comments do not interfere with the table rename815# algorithm.816#817do_test alter-13.1 {818 execsql {819 CREATE TABLE /* hi */ t3102a(x);820 CREATE TABLE t3102b -- comment821 (y);822 CREATE INDEX t3102c ON t3102a(x);823 SELECT name FROM sqlite_master WHERE name GLOB 't3102*' ORDER BY 1;824 }825} {t3102a t3102b t3102c}826do_test alter-13.2 {827 execsql {828 ALTER TABLE t3102a RENAME TO t3102a_rename;829 SELECT name FROM sqlite_master WHERE name GLOB 't3102*' ORDER BY 1;830 }831} {t3102a_rename t3102b t3102c}832do_test alter-13.3 {833 execsql {834 ALTER TABLE t3102b RENAME TO t3102b_rename;835 SELECT name FROM sqlite_master WHERE name GLOB 't3102*' ORDER BY 1;836 }837} {t3102a_rename t3102b_rename t3102c}838 839# Ticket #3651840do_test alter-14.1 {841 catchsql {842 CREATE TABLE t3651(a UNIQUE);843 INSERT INTO t3651 VALUES(5);844 ALTER TABLE t3651 ADD COLUMN b UNIQUE;845 }846} {1 {Cannot add a UNIQUE column}}847do_test alter-14.2 {848 catchsql {849 ALTER TABLE t3651 ADD COLUMN b PRIMARY KEY;850 }851} {1 {Cannot add a PRIMARY KEY column}}852 853 854#-------------------------------------------------------------------------855# Test that it is not possible to use ALTER TABLE on any system table.856#857set system_table_list {1 sqlite_master}858catchsql ANALYZE859ifcapable analyze { lappend system_table_list 2 sqlite_stat1 }860ifcapable stat4 { lappend system_table_list 4 sqlite_stat4 }861 862foreach {tn tbl} $system_table_list {863 do_test alter-15.$tn.1 {864 catchsql "ALTER TABLE $tbl RENAME TO xyz"865 } [list 1 "table $tbl may not be altered"]866 867 do_test alter-15.$tn.2 {868 catchsql "ALTER TABLE $tbl ADD COLUMN xyz"869 } [list 1 "table $tbl may not be altered"]870}871 872#------------------------------------------------------------------------873# Verify that ALTER TABLE works on tables with the WITHOUT rowid option.874#875do_execsql_test alter-16.1 {876 CREATE TABLE t16a(a TEXT, b REAL, c INT, PRIMARY KEY(a,b)) WITHOUT rowid;877 INSERT INTO t16a VALUES('abc',1.25,99);878 ALTER TABLE t16a ADD COLUMN d TEXT DEFAULT 'xyzzy';879 INSERT INTO t16a VALUES('cba',5.5,98,'fizzle');880 SELECT * FROM t16a ORDER BY a;881} {abc 1.25 99 xyzzy cba 5.5 98 fizzle}882do_execsql_test alter-16.2 {883 ALTER TABLE t16a RENAME TO t16a_rn;884 SELECT * FROM t16a_rn ORDER BY a;885} {abc 1.25 99 xyzzy cba 5.5 98 fizzle}886 887# 2018-09-16 ticket b41031ea2b5372378cb3d2d43cf9fe2a4a5c2510888#889ifcapable rtree {890 db close891 sqlite3 db :memory:892 do_execsql_test alter-17.100 {893 CREATE TABLE t1(a INTEGER PRIMARY KEY, b);894 CREATE VIRTUAL TABLE t2 USING rtree(id,x0,x1);895 INSERT INTO t1 VALUES(1,'apple'),(2,'fig'),(3,'pear');896 INSERT INTO t2 VALUES(1,1.0,2.0),(2,2.0,3.0),(3,1.5,3.5);897 CREATE TRIGGER r1 AFTER UPDATE ON t1 BEGIN898 DELETE FROM t2 WHERE id = OLD.a;899 END;900 ALTER TABLE t1 RENAME TO t3;901 UPDATE t3 SET b='peach' WHERE a=2;902 SELECT * FROM t2 ORDER BY 1;903 } {1 1.0 2.0 3 1.5 3.5}904}905 906# 2021-03-08 dbsqlfuzz 3f0a7245b69cd08617d7d7781ebaedb0fe765a93907reset_db908do_catchsql_test alter-18.1 {909 CREATE TABLE t1(a,b,c);910 CREATE TABLE log(a INTEGER PRIMARY KEY,b,c);911 CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN912 INSERT INTO logx(a,b,c) VALUES(new.a,new.b,new.c)913 ON CONFLICT(a) DO UPDATE SET c=excluded.c, b=new.b;914 END;915 ALTER TABLE log RENAME COLUMN a TO x;916} {1 {error in trigger tr1: no such table: main.logx}}917 918# 2021-10-13 dbsqlfuzz e89174cbfad2d904f06b5e24df0a22510b6a1c1e919reset_db920do_execsql_test alter-19.1 {921 CREATE TABLE t1(x);922 CREATE TABLE t2(c);923 CREATE TRIGGER r1 AFTER INSERT ON t2 BEGIN924 UPDATE t2 SET (c)=(925 EXISTS(SELECT 1 WHERE (WITH cte1(a) AS (SELECT 1 FROM t1 WHERE (SELECT 1 WHERE (WITH cte2(b) AS (VALUES(1))SELECT b FROM cte2)))SELECT a FROM cte1))926 );927 END;928 ALTER TABLE t2 RENAME TO t3;929} {}930do_execsql_test alter-19.2 {931 SELECT name FROM sqlite_schema WHERE sql LIKE '%t2%';932} {}933do_execsql_test alter-19.3 {934 SELECT name FROM sqlite_schema WHERE sql LIKE '%t3%' ORDER BY name;935} {r1 t3}936 937# 2023-10-14938# On an ALTER TABLE ADD COLUMN with a DEFAULT clause on a STRICT table939# make sure that the DEFAULT has a compatible type.940#941reset_db942do_execsql_test alter-20.1 {943 CREATE TABLE t1(a INT) STRICT;944 INSERT INTO t1(a) VALUES(45);945} {}946do_catchsql_test alter-20.2 {947 ALTER TABLE t1 ADD COLUMN b TEXT DEFAULT x'313233';948} {1 {type mismatch on DEFAULT}}949do_execsql_test alter-20.2 {950 DELETE FROM t1;951 ALTER TABLE t1 ADD COLUMN b TEXT DEFAULT x'313233';952} {}953do_catchsql_test alter-20.3 {954 INSERT INTO t1(a) VALUES(45);955} {1 {cannot store BLOB value in TEXT column t1.b}}956 957# 2023-11-17 dbsqlfuzz e0900262dadd5c78c2226ad6a435c7f0255be2cd958# Assertion fault associated with ALTER TABLE and an959# aggregate ORDER BY within an unknown aggregate function.960#961reset_db962do_execsql_test alter-21.1 {963 CREATE TABLE t1(a,b,c,d);964 CREATE TABLE t2(a,b,c,d,x);965 CREATE TRIGGER r1 AFTER INSERT ON t2 BEGIN966 SELECT unknown_function(a ORDER BY (SELECT group_concat(DISTINCT a ORDER BY a) FROM t1)) FROM t1;967 END;968 ALTER TABLE t2 RENAME TO e;969} {}970do_execsql_test alter-21.2 {971 SELECT name, type FROM sqlite_schema ORDER BY name;972} {e table r1 trigger t1 table}973do_execsql_test alter-21.3 {974 DROP TRIGGER r1;975 CREATE TRIGGER r2 AFTER INSERT ON e BEGIN976 SELECT unknown_function(a ORDER BY (SELECT group_concat(a ORDER BY a) FROM (SELECT b FROM t1))) FROM t1;977 END;978 ALTER TABLE e RENAME TO t99;979}980do_execsql_test alter-21.4 {981 SELECT name, type FROM sqlite_schema ORDER BY name;982} {r2 trigger t1 table t99 table}983 984 985 986finish_test987