1# 2004 November 10 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 the ALTER TABLE statement. 13# 14# $Id: alter.test,v 1.25 2007/05/15 16:51:37 drh Exp $ 15# 16 17set testdir [file dirname $argv0] 18source $testdir/tester.tcl 19 20# If SQLITE_OMIT_ALTERTABLE is defined, omit this file. 21ifcapable !altertable { 22 finish_test 23 return 24} 25 26#---------------------------------------------------------------------- 27# Test organization: 28# 29# alter-1.1.* - alter-1.7.*: Basic tests of ALTER TABLE, including tables 30# with implicit and explicit indices. These tests came from an earlier 31# 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 the 35# 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 42# Create some tables to rename. Be sure to include some TEMP tables 43# and some tables with odd names. 44# 45do_test alter-1.1 { 46 ifcapable tempdb { 47 set ::temp TEMP 48 } else { 49 set ::temp {} 50 } 51 execsql [subst -nocommands { 52 CREATE TABLE t1(a,b); 53 INSERT INTO t1 VALUES(1,2); 54 CREATE TABLE [t1'x1](c UNIQUE, b PRIMARY KEY); 55 INSERT INTO [t1'x1] VALUES(3,4); 56 CREATE INDEX t1i1 ON T1(B); 57 CREATE INDEX t1i2 ON t1(a,b); 58 CREATE INDEX i3 ON [t1'x1](b,c); 59 CREATE $::temp TABLE "temp table"(e,f,g UNIQUE); 60 CREATE INDEX i2 ON [temp table](f); 61 INSERT INTO [temp table] VALUES(5,6,7); 62 }] 63 execsql { 64 SELECT 't1', * FROM t1; 65 SELECT 't1''x1', * FROM "t1'x1"; 66 SELECT * FROM [temp table]; 67 } 68} {t1 1 2 t1'x1 3 4 5 6 7} 69do_test alter-1.2 { 70 execsql [subst { 71 CREATE $::temp TABLE objlist(type, name, tbl_name); 72 INSERT INTO objlist SELECT type, name, tbl_name 73 FROM sqlite_master WHERE NAME!='objlist'; 74 }] 75 ifcapable tempdb { 76 execsql { 77 INSERT INTO objlist SELECT type, name, tbl_name 78 FROM sqlite_temp_master WHERE NAME!='objlist'; 79 } 80 } 81 82 execsql { 83 SELECT type, name, tbl_name FROM objlist ORDER BY tbl_name, type desc, name; 84 } 85} [list \ 86 table t1 t1 \ 87 index t1i1 t1 \ 88 index t1i2 t1 \ 89 table t1'x1 t1'x1 \ 90 index i3 t1'x1 \ 91 index {sqlite_autoindex_t1'x1_1} t1'x1 \ 92 index {sqlite_autoindex_t1'x1_2} t1'x1 \ 93 table {temp table} {temp table} \ 94 index i2 {temp table} \ 95 index {sqlite_autoindex_temp table_1} {temp table} \ 96 ] 97 98# Make some changes 99# 100integrity_check alter-1.3.0 101do_test alter-1.3 { 102 execsql { 103 ALTER TABLE [T1] RENAME to [-t1-]; 104 ALTER TABLE "t1'x1" RENAME TO T2; 105 ALTER TABLE [temp table] RENAME to TempTab; 106 } 107} {} 108integrity_check alter-1.3.1 109do_test alter-1.4 { 110 execsql { 111 SELECT 't1', * FROM [-t1-]; 112 SELECT 't2', * FROM t2; 113 SELECT * FROM temptab; 114 } 115} {t1 1 2 t2 3 4 5 6 7} 116do_test alter-1.5 { 117 execsql { 118 DELETE FROM objlist; 119 INSERT INTO objlist SELECT type, name, tbl_name 120 FROM sqlite_master WHERE NAME!='objlist'; 121 } 122 catchsql { 123 INSERT INTO objlist SELECT type, name, tbl_name 124 FROM sqlite_temp_master WHERE NAME!='objlist'; 125 } 126 execsql { 127 SELECT type, name, tbl_name FROM objlist ORDER BY tbl_name, type desc, name; 128 } 129} [list \ 130 table -t1- -t1- \ 131 index t1i1 -t1- \ 132 index t1i2 -t1- \ 133 table T2 T2 \ 134 index i3 T2 \ 135 index {sqlite_autoindex_T2_1} T2 \ 136 index {sqlite_autoindex_T2_2} T2 \ 137 table {TempTab} {TempTab} \ 138 index i2 {TempTab} \ 139 index {sqlite_autoindex_TempTab_1} {TempTab} \ 140 ] 141 142# Make sure the changes persist after restarting the database. 143# (The TEMP table will not persist, of course.) 144# 145ifcapable tempdb { 146 do_test alter-1.6 { 147 db close 148 sqlite3 db test.db 149 set DB [sqlite3_connection_pointer db] 150 execsql { 151 CREATE TEMP TABLE objlist(type, name, tbl_name); 152 INSERT INTO objlist SELECT type, name, tbl_name FROM sqlite_master; 153 INSERT INTO objlist 154 SELECT type, name, tbl_name FROM sqlite_temp_master 155 WHERE NAME!='objlist'; 156 SELECT type, name, tbl_name FROM objlist 157 ORDER BY tbl_name, type desc, name; 158 } 159 } [list \ 160 table -t1- -t1- \ 161 index t1i1 -t1- \ 162 index t1i2 -t1- \ 163 table T2 T2 \ 164 index i3 T2 \ 165 index {sqlite_autoindex_T2_1} T2 \ 166 index {sqlite_autoindex_T2_2} T2 \ 167 ] 168} else { 169 execsql { 170 DROP TABLE TempTab; 171 } 172} 173 174# Make sure the ALTER TABLE statements work with the 175# non-callback API 176# 177do_test alter-1.7 { 178 stepsql $DB { 179 ALTER TABLE [-t1-] RENAME to [*t1*]; 180 ALTER TABLE T2 RENAME TO [<t2>]; 181 } 182 execsql { 183 DELETE FROM objlist; 184 INSERT INTO objlist SELECT type, name, tbl_name 185 FROM sqlite_master WHERE NAME!='objlist'; 186 } 187 catchsql { 188 INSERT INTO objlist SELECT type, name, tbl_name 189 FROM sqlite_temp_master WHERE NAME!='objlist'; 190 } 191 execsql { 192 SELECT type, name, tbl_name FROM objlist ORDER BY tbl_name, type desc, name; 193 } 194} [list \ 195 table *t1* *t1* \ 196 index t1i1 *t1* \ 197 index t1i2 *t1* \ 198 table <t2> <t2> \ 199 index i3 <t2> \ 200 index {sqlite_autoindex_<t2>_1} <t2> \ 201 index {sqlite_autoindex_<t2>_2} <t2> \ 202 ] 203 204# Check that ALTER TABLE works on attached databases. 205# 206do_test alter-1.8.1 { 207 file delete -force test2.db 208 file delete -force test2.db-journal 209 execsql { 210 ATTACH 'test2.db' AS aux; 211 } 212} {} 213do_test alter-1.8.2 { 214 execsql { 215 CREATE TABLE t4(a PRIMARY KEY, b, c); 216 CREATE TABLE aux.t4(a PRIMARY KEY, b, c); 217 CREATE INDEX i4 ON t4(b); 218 CREATE INDEX aux.i4 ON t4(b); 219 } 220} {} 221do_test alter-1.8.3 { 222 execsql { 223 INSERT INTO t4 VALUES('main', 'main', 'main'); 224 INSERT INTO aux.t4 VALUES('aux', 'aux', 'aux'); 225 SELECT * FROM t4 WHERE a = 'main'; 226 } 227} {main main main} 228do_test alter-1.8.4 { 229 execsql { 230 ALTER TABLE t4 RENAME TO t5; 231 SELECT * FROM t4 WHERE a = 'aux'; 232 } 233} {aux aux aux} 234do_test alter-1.8.5 { 235 execsql { 236 SELECT * FROM t5; 237 } 238} {main main main} 239do_test alter-1.8.6 { 240 execsql { 241 SELECT * FROM t5 WHERE b = 'main'; 242 } 243} {main main main} 244do_test alter-1.8.7 { 245 execsql { 246 ALTER TABLE aux.t4 RENAME TO t5; 247 SELECT * FROM aux.t5 WHERE b = 'aux'; 248 } 249} {aux aux aux} 250 251do_test alter-1.9.1 { 252 execsql { 253 CREATE TABLE tbl1 (a, b, c); 254 INSERT INTO tbl1 VALUES(1, 2, 3); 255 } 256} {} 257do_test alter-1.9.2 { 258 execsql { 259 SELECT * FROM tbl1; 260 } 261} {1 2 3} 262do_test alter-1.9.3 { 263 execsql { 264 ALTER TABLE tbl1 RENAME TO tbl2; 265 SELECT * FROM tbl2; 266 } 267} {1 2 3} 268do_test alter-1.9.4 { 269 execsql { 270 DROP TABLE tbl2; 271 } 272} {} 273 274# Test error messages 275# 276do_test alter-2.1 { 277 catchsql { 278 ALTER TABLE none RENAME TO hi; 279 } 280} {1 {no such table: none}} 281do_test alter-2.2 { 282 execsql { 283 CREATE TABLE t3(p,q,r); 284 } 285 catchsql { 286 ALTER TABLE [<t2>] RENAME TO t3; 287 } 288} {1 {there is already another table or index with this name: t3}} 289do_test alter-2.3 { 290 catchsql { 291 ALTER TABLE [<t2>] RENAME TO i3; 292 } 293} {1 {there is already another table or index with this name: i3}} 294do_test alter-2.4 { 295 catchsql { 296 ALTER TABLE SqLiTe_master RENAME TO master; 297 } 298} {1 {table sqlite_master may not be altered}} 299do_test alter-2.5 { 300 catchsql { 301 ALTER TABLE t3 RENAME TO sqlite_t3; 302 } 303} {1 {object name reserved for internal use: sqlite_t3}} 304do_test alter-2.6 { 305 catchsql { 306 ALTER TABLE t3 ADD COLUMN (ALTER TABLE t3 ADD COLUMN); 307 } 308} {1 {near "(": syntax error}} 309 310# If this compilation does not include triggers, omit the alter-3.* tests. 311ifcapable trigger { 312 313#----------------------------------------------------------------------- 314# Tests alter-3.* test ALTER TABLE on tables that have triggers. 315# 316# alter-3.1.*: ALTER TABLE with triggers. 317# alter-3.2.*: Test that the ON keyword cannot be used as a database, 318# table or column name unquoted. This is done because part of the 319# ALTER TABLE code (specifically the implementation of SQL function 320# "sqlite_alter_trigger") will break in this case. 321# alter-3.3.*: ALTER TABLE with TEMP triggers (todo). 322# 323 324# An SQL user-function for triggers to fire, so that we know they 325# are working. 326proc trigfunc {args} { 327 set ::TRIGGER $args 328} 329db func trigfunc trigfunc 330 331do_test alter-3.1.0 { 332 execsql { 333 CREATE TABLE t6(a, b, c); 334 CREATE TRIGGER trig1 AFTER INSERT ON t6 BEGIN 335 SELECT trigfunc('trig1', new.a, new.b, new.c); 336 END; 337 } 338} {} 339do_test alter-3.1.1 { 340 execsql { 341 INSERT INTO t6 VALUES(1, 2, 3); 342 } 343 set ::TRIGGER 344} {trig1 1 2 3} 345do_test alter-3.1.2 { 346 execsql { 347 ALTER TABLE t6 RENAME TO t7; 348 INSERT INTO t7 VALUES(4, 5, 6); 349 } 350 set ::TRIGGER 351} {trig1 4 5 6} 352do_test alter-3.1.3 { 353 execsql { 354 DROP TRIGGER trig1; 355 } 356} {} 357do_test alter-3.1.4 { 358 execsql { 359 CREATE TRIGGER trig2 AFTER INSERT ON main.t7 BEGIN 360 SELECT trigfunc('trig2', new.a, new.b, new.c); 361 END; 362 INSERT INTO t7 VALUES(1, 2, 3); 363 } 364 set ::TRIGGER 365} {trig2 1 2 3} 366do_test alter-3.1.5 { 367 execsql { 368 ALTER TABLE t7 RENAME TO t8; 369 INSERT INTO t8 VALUES(4, 5, 6); 370 } 371 set ::TRIGGER 372} {trig2 4 5 6} 373do_test alter-3.1.6 { 374 execsql { 375 DROP TRIGGER trig2; 376 } 377} {} 378do_test alter-3.1.7 { 379 execsql { 380 CREATE TRIGGER trig3 AFTER INSERT ON main.'t8'BEGIN 381 SELECT trigfunc('trig3', new.a, new.b, new.c); 382 END; 383 INSERT INTO t8 VALUES(1, 2, 3); 384 } 385 set ::TRIGGER 386} {trig3 1 2 3} 387do_test alter-3.1.8 { 388 execsql { 389 ALTER TABLE t8 RENAME TO t9; 390 INSERT INTO t9 VALUES(4, 5, 6); 391 } 392 set ::TRIGGER 393} {trig3 4 5 6} 394 395# Make sure "ON" cannot be used as a database, table or column name without 396# quoting. Otherwise the sqlite_alter_trigger() function might not work. 397file delete -force test3.db 398file delete -force test3.db-journal 399do_test alter-3.2.1 { 400 catchsql { 401 ATTACH 'test3.db' AS ON; 402 } 403} {1 {near "ON": syntax error}} 404do_test alter-3.2.2 { 405 catchsql { 406 ATTACH 'test3.db' AS 'ON'; 407 } 408} {0 {}} 409do_test alter-3.2.3 { 410 catchsql { 411 CREATE TABLE ON.t1(a, b, c); 412 } 413} {1 {near "ON": syntax error}} 414do_test alter-3.2.4 { 415 catchsql { 416 CREATE TABLE 'ON'.t1(a, b, c); 417 } 418} {0 {}} 419do_test alter-3.2.4 { 420 catchsql { 421 CREATE TABLE 'ON'.ON(a, b, c); 422 } 423} {1 {near "ON": syntax error}} 424do_test alter-3.2.5 { 425 catchsql { 426 CREATE TABLE 'ON'.'ON'(a, b, c); 427 } 428} {0 {}} 429do_test alter-3.2.6 { 430 catchsql { 431 CREATE TABLE t10(a, ON, c); 432 } 433} {1 {near "ON": syntax error}} 434do_test alter-3.2.7 { 435 catchsql { 436 CREATE TABLE t10(a, 'ON', c); 437 } 438} {0 {}} 439do_test alter-3.2.8 { 440 catchsql { 441 CREATE TRIGGER trig4 AFTER INSERT ON ON BEGIN SELECT 1; END; 442 } 443} {1 {near "ON": syntax error}} 444do_test alter-3.2.9 { 445 catchsql { 446 CREATE TRIGGER 'on'.trig4 AFTER INSERT ON 'ON' BEGIN SELECT 1; END; 447 } 448} {0 {}} 449do_test alter-3.2.10 { 450 execsql { 451 DROP TABLE t10; 452 } 453} {} 454 455do_test alter-3.3.1 { 456 execsql [subst { 457 CREATE TABLE tbl1(a, b, c); 458 CREATE $::temp TRIGGER trig1 AFTER INSERT ON tbl1 BEGIN 459 SELECT trigfunc('trig1', new.a, new.b, new.c); 460 END; 461 }] 462} {} 463do_test alter-3.3.2 { 464 execsql { 465 INSERT INTO tbl1 VALUES('a', 'b', 'c'); 466 } 467 set ::TRIGGER 468} {trig1 a b c} 469do_test alter-3.3.3 { 470 execsql { 471 ALTER TABLE tbl1 RENAME TO tbl2; 472 INSERT INTO tbl2 VALUES('d', 'e', 'f'); 473 } 474 set ::TRIGGER 475} {trig1 d e f} 476do_test alter-3.3.4 { 477 execsql [subst { 478 CREATE $::temp TRIGGER trig2 AFTER UPDATE ON tbl2 BEGIN 479 SELECT trigfunc('trig2', new.a, new.b, new.c); 480 END; 481 }] 482} {} 483do_test alter-3.3.5 { 484 execsql { 485 ALTER TABLE tbl2 RENAME TO tbl3; 486 INSERT INTO tbl3 VALUES('g', 'h', 'i'); 487 } 488 set ::TRIGGER 489} {trig1 g h i} 490do_test alter-3.3.6 { 491 execsql { 492 UPDATE tbl3 SET a = 'G' where a = 'g'; 493 } 494 set ::TRIGGER 495} {trig2 G h i} 496do_test alter-3.3.7 { 497 execsql { 498 DROP TABLE tbl3; 499 } 500} {} 501ifcapable tempdb { 502 do_test alter-3.3.8 { 503 execsql { 504 SELECT * FROM sqlite_temp_master WHERE type = 'trigger'; 505 } 506 } {} 507} 508 509} ;# ifcapable trigger 510 511# If the build does not include AUTOINCREMENT fields, omit alter-4.*. 512ifcapable autoinc { 513 514do_test alter-4.1 { 515 execsql { 516 CREATE TABLE tbl1(a INTEGER PRIMARY KEY AUTOINCREMENT); 517 INSERT INTO tbl1 VALUES(10); 518 } 519} {} 520do_test alter-4.2 { 521 execsql { 522 INSERT INTO tbl1 VALUES(NULL); 523 SELECT a FROM tbl1; 524 } 525} {10 11} 526do_test alter-4.3 { 527 execsql { 528 ALTER TABLE tbl1 RENAME TO tbl2; 529 DELETE FROM tbl2; 530 INSERT INTO tbl2 VALUES(NULL); 531 SELECT a FROM tbl2; 532 } 533} {12} 534do_test alter-4.4 { 535 execsql { 536 DROP TABLE tbl2; 537 } 538} {} 539 540} ;# ifcapable autoinc 541 542# Test that it is Ok to execute an ALTER TABLE immediately after 543# opening a database. 544do_test alter-5.1 { 545 execsql { 546 CREATE TABLE tbl1(a, b, c); 547 INSERT INTO tbl1 VALUES('x', 'y', 'z'); 548 } 549} {} 550do_test alter-5.2 { 551 sqlite3 db2 test.db 552 execsql { 553 ALTER TABLE tbl1 RENAME TO tbl2; 554 SELECT * FROM tbl2; 555 } db2 556} {x y z} 557do_test alter-5.3 { 558 db2 close 559} {} 560 561foreach tblname [execsql { 562 SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite%' 563}] { 564 execsql "DROP TABLE \"$tblname\"" 565} 566 567set ::tbl_name "abc\uABCDdef" 568do_test alter-6.1 { 569 string length $::tbl_name 570} {7} 571do_test alter-6.2 { 572 execsql " 573 CREATE TABLE ${tbl_name}(a, b, c); 574 " 575 set ::oid [execsql {SELECT max(oid) FROM sqlite_master}] 576 execsql " 577 SELECT sql FROM sqlite_master WHERE oid = $::oid; 578 " 579} "{CREATE TABLE ${::tbl_name}(a, b, c)}" 580execsql " 581 SELECT * FROM ${::tbl_name} 582" 583set ::tbl_name2 "abcXdef" 584do_test alter-6.3 { 585 execsql " 586 ALTER TABLE $::tbl_name RENAME TO $::tbl_name2 587 " 588 execsql " 589 SELECT sql FROM sqlite_master WHERE oid = $::oid 590 " 591} "{CREATE TABLE '${::tbl_name2}'(a, b, c)}" 592do_test alter-6.4 { 593 execsql " 594 ALTER TABLE $::tbl_name2 RENAME TO $::tbl_name 595 " 596 execsql " 597 SELECT sql FROM sqlite_master WHERE oid = $::oid 598 " 599} "{CREATE TABLE '${::tbl_name}'(a, b, c)}" 600set ::col_name ghi\1234\jkl 601do_test alter-6.5 { 602 execsql " 603 ALTER TABLE $::tbl_name ADD COLUMN $::col_name VARCHAR 604 " 605 execsql " 606 SELECT sql FROM sqlite_master WHERE oid = $::oid 607 " 608} "{CREATE TABLE '${::tbl_name}'(a, b, c, $::col_name VARCHAR)}" 609set ::col_name2 B\3421\A 610do_test alter-6.6 { 611 db close 612 sqlite3 db test.db 613 execsql " 614 ALTER TABLE $::tbl_name ADD COLUMN $::col_name2 615 " 616 execsql " 617 SELECT sql FROM sqlite_master WHERE oid = $::oid 618 " 619} "{CREATE TABLE '${::tbl_name}'(a, b, c, $::col_name VARCHAR, $::col_name2)}" 620do_test alter-6.7 { 621 execsql " 622 INSERT INTO ${::tbl_name} VALUES(1, 2, 3, 4, 5); 623 SELECT $::col_name, $::col_name2 FROM $::tbl_name; 624 " 625} {4 5} 626 627# Ticket #1665: Make sure ALTER TABLE ADD COLUMN works on a table 628# that includes a COLLATE clause. 629# 630do_test alter-7.1 { 631 execsql { 632 CREATE TABLE t1(a TEXT COLLATE BINARY); 633 ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE; 634 INSERT INTO t1 VALUES(1,'-2'); 635 INSERT INTO t1 VALUES(5.4e-8,'5.4e-8'); 636 SELECT typeof(a), a, typeof(b), b FROM t1; 637 } 638} {text 1 integer -2 text 5.4e-8 real 5.4e-08} 639 640# Make sure that when a column is added by ALTER TABLE ADD COLUMN and has 641# a default value that the default value is used by aggregate functions. 642# 643do_test alter-8.1 { 644 execsql { 645 CREATE TABLE t2(a INTEGER); 646 INSERT INTO t2 VALUES(1); 647 INSERT INTO t2 VALUES(1); 648 INSERT INTO t2 VALUES(2); 649 ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9; 650 SELECT sum(b) FROM t2; 651 } 652} {27} 653do_test alter-8.2 { 654 execsql { 655 SELECT a, sum(b) FROM t2 GROUP BY a; 656 } 657} {1 18 2 9} 658 659#-------------------------------------------------------------------------- 660# alter-9.X - Special test: Make sure the sqlite_rename_trigger() and 661# rename_table() functions do not crash when handed bad input. 662# 663ifcapable trigger { 664 do_test alter-9.1 { 665 execsql {SELECT SQLITE_RENAME_TRIGGER(0,0)} 666 } {{}} 667} 668do_test alter-9.2 { 669 execsql { 670 SELECT SQLITE_RENAME_TABLE(0,0); 671 SELECT SQLITE_RENAME_TABLE(10,20); 672 SELECT SQLITE_RENAME_TABLE("foo", "foo"); 673 } 674} {{} {} {}} 675 676#------------------------------------------------------------------------ 677# alter-10.X - Make sure ALTER TABLE works with multi-byte UTF-8 characters 678# in the names. 679# 680do_test alter-10.1 { 681 execsql "CREATE TABLE xyz(x UNIQUE)" 682 execsql "ALTER TABLE xyz RENAME TO xyz\u1234abc" 683 execsql {SELECT name FROM sqlite_master WHERE name LIKE 'xyz%'} 684} [list xyz\u1234abc] 685do_test alter-10.2 { 686 execsql {SELECT name FROM sqlite_master WHERE name LIKE 'sqlite_autoindex%'} 687} [list sqlite_autoindex_xyz\u1234abc_1] 688do_test alter-10.3 { 689 execsql "ALTER TABLE xyz\u1234abc RENAME TO xyzabc" 690 execsql {SELECT name FROM sqlite_master WHERE name LIKE 'xyz%'} 691} [list xyzabc] 692do_test alter-10.4 { 693 execsql {SELECT name FROM sqlite_master WHERE name LIKE 'sqlite_autoindex%'} 694} [list sqlite_autoindex_xyzabc_1] 695 696do_test alter-11.1 { 697 sqlite3_exec db {CREATE TABLE t11(%c6%c6)} 698 execsql { 699 ALTER TABLE t11 ADD COLUMN abc; 700 } 701 catchsql { 702 ALTER TABLE t11 ADD COLUMN abc; 703 } 704} {1 {duplicate column name: abc}} 705set isutf16 [regexp 16 [db one {PRAGMA encoding}]] 706if {!$isutf16} { 707 do_test alter-11.2 { 708 execsql {INSERT INTO t11 VALUES(1,2)} 709 sqlite3_exec db {SELECT %c6%c6 AS xyz, abc FROM t11} 710 } {0 {xyz abc 1 2}} 711} 712do_test alter-11.3 { 713 sqlite3_exec db {CREATE TABLE t11b("%81%82%83" text)} 714 execsql { 715 ALTER TABLE t11b ADD COLUMN abc; 716 } 717 catchsql { 718 ALTER TABLE t11b ADD COLUMN abc; 719 } 720} {1 {duplicate column name: abc}} 721if {!$isutf16} { 722 do_test alter-11.4 { 723 execsql {INSERT INTO t11b VALUES(3,4)} 724 sqlite3_exec db {SELECT %81%82%83 AS xyz, abc FROM t11b} 725 } {0 {xyz abc 3 4}} 726 do_test alter-11.5 { 727 sqlite3_exec db {SELECT [%81%82%83] AS xyz, abc FROM t11b} 728 } {0 {xyz abc 3 4}} 729 do_test alter-11.6 { 730 sqlite3_exec db {SELECT "%81%82%83" AS xyz, abc FROM t11b} 731 } {0 {xyz abc 3 4}} 732} 733do_test alter-11.7 { 734 sqlite3_exec db {CREATE TABLE t11c(%81%82%83 text)} 735 execsql { 736 ALTER TABLE t11c ADD COLUMN abc; 737 } 738 catchsql { 739 ALTER TABLE t11c ADD COLUMN abc; 740 } 741} {1 {duplicate column name: abc}} 742if {!$isutf16} { 743 do_test alter-11.8 { 744 execsql {INSERT INTO t11c VALUES(5,6)} 745 sqlite3_exec db {SELECT %81%82%83 AS xyz, abc FROM t11c} 746 } {0 {xyz abc 5 6}} 747 do_test alter-11.9 { 748 sqlite3_exec db {SELECT [%81%82%83] AS xyz, abc FROM t11c} 749 } {0 {xyz abc 5 6}} 750 do_test alter-11.10 { 751 sqlite3_exec db {SELECT "%81%82%83" AS xyz, abc FROM t11c} 752 } {0 {xyz abc 5 6}} 753} 754 755 756finish_test 757