Annotation of embedaddon/sqlite3/test/alter.test, revision 1.1
1.1 ! misho 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.32 2009/03/24 15:08:10 drh Exp $
! 15: #
! 16:
! 17: set testdir [file dirname $argv0]
! 18: source $testdir/tester.tcl
! 19:
! 20: # If SQLITE_OMIT_ALTERTABLE is defined, omit this file.
! 21: ifcapable !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: # alter-12.*: Test ALTER TABLE on views.
! 42: #
! 43:
! 44: # Create some tables to rename. Be sure to include some TEMP tables
! 45: # and some tables with odd names.
! 46: #
! 47: do_test alter-1.1 {
! 48: ifcapable tempdb {
! 49: set ::temp TEMP
! 50: } 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}
! 71: do_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 sqlite_temp_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 changes
! 101: #
! 102: integrity_check alter-1.3.0
! 103: do_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: } {}
! 110: integrity_check alter-1.3.1
! 111: do_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}
! 118: do_test alter-1.5 {
! 119: execsql {
! 120: DELETE FROM objlist;
! 121: INSERT INTO objlist SELECT type, name, tbl_name
! 122: 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: #
! 147: ifcapable tempdb {
! 148: do_test alter-1.6 {
! 149: db close
! 150: sqlite3 db test.db
! 151: 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 sqlite_temp_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 back
! 178: # to the built-in functions.
! 179: #
! 180: proc failing_app_func {args} {error "bad function"}
! 181: do_test alter-1.7-prep {
! 182: db func substr failing_app_func
! 183: db func like failing_app_func
! 184: db func sqlite_rename_table failing_app_func
! 185: db func sqlite_rename_trigger failing_app_func
! 186: db func sqlite_rename_parent failing_app_func
! 187: catchsql {SELECT substr(name,1,3) FROM sqlite_master}
! 188: } {1 {bad function}}
! 189:
! 190: # Make sure the ALTER TABLE statements work with the
! 191: # non-callback API
! 192: #
! 193: do_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_name
! 201: 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: #
! 222: ifcapable attach {
! 223: do_test alter-1.8.1 {
! 224: forcedelete test2.db
! 225: forcedelete test2.db-journal
! 226: 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:
! 269: do_test alter-1.9.1 {
! 270: execsql {
! 271: CREATE TABLE tbl1 (a, b, c);
! 272: INSERT INTO tbl1 VALUES(1, 2, 3);
! 273: }
! 274: } {}
! 275: do_test alter-1.9.2 {
! 276: execsql {
! 277: SELECT * FROM tbl1;
! 278: }
! 279: } {1 2 3}
! 280: do_test alter-1.9.3 {
! 281: execsql {
! 282: ALTER TABLE tbl1 RENAME TO tbl2;
! 283: SELECT * FROM tbl2;
! 284: }
! 285: } {1 2 3}
! 286: do_test alter-1.9.4 {
! 287: execsql {
! 288: DROP TABLE tbl2;
! 289: }
! 290: } {}
! 291:
! 292: # Test error messages
! 293: #
! 294: do_test alter-2.1 {
! 295: catchsql {
! 296: ALTER TABLE none RENAME TO hi;
! 297: }
! 298: } {1 {no such table: none}}
! 299: do_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}}
! 307: do_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}}
! 312: do_test alter-2.4 {
! 313: catchsql {
! 314: ALTER TABLE SqLiTe_master RENAME TO master;
! 315: }
! 316: } {1 {table sqlite_master may not be altered}}
! 317: do_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}}
! 322: do_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.
! 329: ifcapable 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 the
! 337: # ALTER TABLE code (specifically the implementation of SQL function
! 338: # "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 they
! 343: # are working.
! 344: proc trigfunc {args} {
! 345: set ::TRIGGER $args
! 346: }
! 347: db func trigfunc trigfunc
! 348:
! 349: do_test alter-3.1.0 {
! 350: execsql {
! 351: CREATE TABLE t6(a, b, c);
! 352: CREATE TRIGGER trig1 AFTER INSERT ON t6 BEGIN
! 353: SELECT trigfunc('trig1', new.a, new.b, new.c);
! 354: END;
! 355: }
! 356: } {}
! 357: do_test alter-3.1.1 {
! 358: execsql {
! 359: INSERT INTO t6 VALUES(1, 2, 3);
! 360: }
! 361: set ::TRIGGER
! 362: } {trig1 1 2 3}
! 363: do_test alter-3.1.2 {
! 364: execsql {
! 365: ALTER TABLE t6 RENAME TO t7;
! 366: INSERT INTO t7 VALUES(4, 5, 6);
! 367: }
! 368: set ::TRIGGER
! 369: } {trig1 4 5 6}
! 370: do_test alter-3.1.3 {
! 371: execsql {
! 372: DROP TRIGGER trig1;
! 373: }
! 374: } {}
! 375: do_test alter-3.1.4 {
! 376: execsql {
! 377: CREATE TRIGGER trig2 AFTER INSERT ON main.t7 BEGIN
! 378: SELECT trigfunc('trig2', new.a, new.b, new.c);
! 379: END;
! 380: INSERT INTO t7 VALUES(1, 2, 3);
! 381: }
! 382: set ::TRIGGER
! 383: } {trig2 1 2 3}
! 384: do_test alter-3.1.5 {
! 385: execsql {
! 386: ALTER TABLE t7 RENAME TO t8;
! 387: INSERT INTO t8 VALUES(4, 5, 6);
! 388: }
! 389: set ::TRIGGER
! 390: } {trig2 4 5 6}
! 391: do_test alter-3.1.6 {
! 392: execsql {
! 393: DROP TRIGGER trig2;
! 394: }
! 395: } {}
! 396: do_test alter-3.1.7 {
! 397: execsql {
! 398: CREATE TRIGGER trig3 AFTER INSERT ON main.'t8'BEGIN
! 399: SELECT trigfunc('trig3', new.a, new.b, new.c);
! 400: END;
! 401: INSERT INTO t8 VALUES(1, 2, 3);
! 402: }
! 403: set ::TRIGGER
! 404: } {trig3 1 2 3}
! 405: do_test alter-3.1.8 {
! 406: execsql {
! 407: ALTER TABLE t8 RENAME TO t9;
! 408: INSERT INTO t9 VALUES(4, 5, 6);
! 409: }
! 410: set ::TRIGGER
! 411: } {trig3 4 5 6}
! 412:
! 413: # Make sure "ON" cannot be used as a database, table or column name without
! 414: # quoting. Otherwise the sqlite_alter_trigger() function might not work.
! 415: forcedelete test3.db
! 416: forcedelete test3.db-journal
! 417: ifcapable attach {
! 418: do_test alter-3.2.1 {
! 419: catchsql {
! 420: ATTACH 'test3.db' AS ON;
! 421: }
! 422: } {1 {near "ON": syntax error}}
! 423: do_test alter-3.2.2 {
! 424: catchsql {
! 425: ATTACH 'test3.db' AS 'ON';
! 426: }
! 427: } {0 {}}
! 428: do_test alter-3.2.3 {
! 429: catchsql {
! 430: CREATE TABLE ON.t1(a, b, c);
! 431: }
! 432: } {1 {near "ON": syntax error}}
! 433: do_test alter-3.2.4 {
! 434: catchsql {
! 435: CREATE TABLE 'ON'.t1(a, b, c);
! 436: }
! 437: } {0 {}}
! 438: do_test alter-3.2.4 {
! 439: catchsql {
! 440: CREATE TABLE 'ON'.ON(a, b, c);
! 441: }
! 442: } {1 {near "ON": syntax error}}
! 443: do_test alter-3.2.5 {
! 444: catchsql {
! 445: CREATE TABLE 'ON'.'ON'(a, b, c);
! 446: }
! 447: } {0 {}}
! 448: }
! 449: do_test alter-3.2.6 {
! 450: catchsql {
! 451: CREATE TABLE t10(a, ON, c);
! 452: }
! 453: } {1 {near "ON": syntax error}}
! 454: do_test alter-3.2.7 {
! 455: catchsql {
! 456: CREATE TABLE t10(a, 'ON', c);
! 457: }
! 458: } {0 {}}
! 459: do_test alter-3.2.8 {
! 460: catchsql {
! 461: CREATE TRIGGER trig4 AFTER INSERT ON ON BEGIN SELECT 1; END;
! 462: }
! 463: } {1 {near "ON": syntax error}}
! 464: ifcapable attach {
! 465: do_test alter-3.2.9 {
! 466: catchsql {
! 467: CREATE TRIGGER 'on'.trig4 AFTER INSERT ON 'ON' BEGIN SELECT 1; END;
! 468: }
! 469: } {0 {}}
! 470: }
! 471: do_test alter-3.2.10 {
! 472: execsql {
! 473: DROP TABLE t10;
! 474: }
! 475: } {}
! 476:
! 477: do_test alter-3.3.1 {
! 478: execsql [subst {
! 479: CREATE TABLE tbl1(a, b, c);
! 480: CREATE $::temp TRIGGER trig1 AFTER INSERT ON tbl1 BEGIN
! 481: SELECT trigfunc('trig1', new.a, new.b, new.c);
! 482: END;
! 483: }]
! 484: } {}
! 485: do_test alter-3.3.2 {
! 486: execsql {
! 487: INSERT INTO tbl1 VALUES('a', 'b', 'c');
! 488: }
! 489: set ::TRIGGER
! 490: } {trig1 a b c}
! 491: do_test alter-3.3.3 {
! 492: execsql {
! 493: ALTER TABLE tbl1 RENAME TO tbl2;
! 494: INSERT INTO tbl2 VALUES('d', 'e', 'f');
! 495: }
! 496: set ::TRIGGER
! 497: } {trig1 d e f}
! 498: do_test alter-3.3.4 {
! 499: execsql [subst {
! 500: CREATE $::temp TRIGGER trig2 AFTER UPDATE ON tbl2 BEGIN
! 501: SELECT trigfunc('trig2', new.a, new.b, new.c);
! 502: END;
! 503: }]
! 504: } {}
! 505: do_test alter-3.3.5 {
! 506: execsql {
! 507: ALTER TABLE tbl2 RENAME TO tbl3;
! 508: INSERT INTO tbl3 VALUES('g', 'h', 'i');
! 509: }
! 510: set ::TRIGGER
! 511: } {trig1 g h i}
! 512: do_test alter-3.3.6 {
! 513: execsql {
! 514: UPDATE tbl3 SET a = 'G' where a = 'g';
! 515: }
! 516: set ::TRIGGER
! 517: } {trig2 G h i}
! 518: do_test alter-3.3.7 {
! 519: execsql {
! 520: DROP TABLE tbl3;
! 521: }
! 522: } {}
! 523: ifcapable tempdb {
! 524: do_test alter-3.3.8 {
! 525: execsql {
! 526: SELECT * FROM sqlite_temp_master WHERE type = 'trigger';
! 527: }
! 528: } {}
! 529: }
! 530:
! 531: } ;# ifcapable trigger
! 532:
! 533: # If the build does not include AUTOINCREMENT fields, omit alter-4.*.
! 534: ifcapable autoinc {
! 535:
! 536: do_test alter-4.1 {
! 537: execsql {
! 538: CREATE TABLE tbl1(a INTEGER PRIMARY KEY AUTOINCREMENT);
! 539: INSERT INTO tbl1 VALUES(10);
! 540: }
! 541: } {}
! 542: do_test alter-4.2 {
! 543: execsql {
! 544: INSERT INTO tbl1 VALUES(NULL);
! 545: SELECT a FROM tbl1;
! 546: }
! 547: } {10 11}
! 548: do_test alter-4.3 {
! 549: execsql {
! 550: ALTER TABLE tbl1 RENAME TO tbl2;
! 551: DELETE FROM tbl2;
! 552: INSERT INTO tbl2 VALUES(NULL);
! 553: SELECT a FROM tbl2;
! 554: }
! 555: } {12}
! 556: do_test alter-4.4 {
! 557: execsql {
! 558: DROP TABLE tbl2;
! 559: }
! 560: } {}
! 561:
! 562: } ;# ifcapable autoinc
! 563:
! 564: # Test that it is Ok to execute an ALTER TABLE immediately after
! 565: # opening a database.
! 566: do_test alter-5.1 {
! 567: execsql {
! 568: CREATE TABLE tbl1(a, b, c);
! 569: INSERT INTO tbl1 VALUES('x', 'y', 'z');
! 570: }
! 571: } {}
! 572: do_test alter-5.2 {
! 573: sqlite3 db2 test.db
! 574: execsql {
! 575: ALTER TABLE tbl1 RENAME TO tbl2;
! 576: SELECT * FROM tbl2;
! 577: } db2
! 578: } {x y z}
! 579: do_test alter-5.3 {
! 580: db2 close
! 581: } {}
! 582:
! 583: foreach tblname [execsql {
! 584: SELECT name FROM sqlite_master
! 585: WHERE type='table' AND name NOT GLOB 'sqlite*'
! 586: }] {
! 587: execsql "DROP TABLE \"$tblname\""
! 588: }
! 589:
! 590: set ::tbl_name "abc\uABCDdef"
! 591: do_test alter-6.1 {
! 592: string length $::tbl_name
! 593: } {7}
! 594: do_test alter-6.2 {
! 595: execsql "
! 596: CREATE TABLE ${tbl_name}(a, b, c);
! 597: "
! 598: set ::oid [execsql {SELECT max(oid) FROM sqlite_master}]
! 599: execsql "
! 600: SELECT sql FROM sqlite_master WHERE oid = $::oid;
! 601: "
! 602: } "{CREATE TABLE ${::tbl_name}(a, b, c)}"
! 603: execsql "
! 604: SELECT * FROM ${::tbl_name}
! 605: "
! 606: set ::tbl_name2 "abcXdef"
! 607: do_test alter-6.3 {
! 608: execsql "
! 609: ALTER TABLE $::tbl_name RENAME TO $::tbl_name2
! 610: "
! 611: execsql "
! 612: SELECT sql FROM sqlite_master WHERE oid = $::oid
! 613: "
! 614: } "{CREATE TABLE \"${::tbl_name2}\"(a, b, c)}"
! 615: do_test alter-6.4 {
! 616: execsql "
! 617: ALTER TABLE $::tbl_name2 RENAME TO $::tbl_name
! 618: "
! 619: execsql "
! 620: SELECT sql FROM sqlite_master WHERE oid = $::oid
! 621: "
! 622: } "{CREATE TABLE \"${::tbl_name}\"(a, b, c)}"
! 623: set ::col_name ghi\1234\jkl
! 624: do_test alter-6.5 {
! 625: execsql "
! 626: ALTER TABLE $::tbl_name ADD COLUMN $::col_name VARCHAR
! 627: "
! 628: execsql "
! 629: SELECT sql FROM sqlite_master WHERE oid = $::oid
! 630: "
! 631: } "{CREATE TABLE \"${::tbl_name}\"(a, b, c, $::col_name VARCHAR)}"
! 632: set ::col_name2 B\3421\A
! 633: do_test alter-6.6 {
! 634: db close
! 635: sqlite3 db test.db
! 636: execsql "
! 637: ALTER TABLE $::tbl_name ADD COLUMN $::col_name2
! 638: "
! 639: execsql "
! 640: SELECT sql FROM sqlite_master WHERE oid = $::oid
! 641: "
! 642: } "{CREATE TABLE \"${::tbl_name}\"(a, b, c, $::col_name VARCHAR, $::col_name2)}"
! 643: do_test alter-6.7 {
! 644: execsql "
! 645: INSERT INTO ${::tbl_name} VALUES(1, 2, 3, 4, 5);
! 646: SELECT $::col_name, $::col_name2 FROM $::tbl_name;
! 647: "
! 648: } {4 5}
! 649:
! 650: # Ticket #1665: Make sure ALTER TABLE ADD COLUMN works on a table
! 651: # that includes a COLLATE clause.
! 652: #
! 653: do_realnum_test alter-7.1 {
! 654: execsql {
! 655: CREATE TABLE t1(a TEXT COLLATE BINARY);
! 656: ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
! 657: INSERT INTO t1 VALUES(1,'-2');
! 658: INSERT INTO t1 VALUES(5.4e-08,'5.4e-08');
! 659: SELECT typeof(a), a, typeof(b), b FROM t1;
! 660: }
! 661: } {text 1 integer -2 text 5.4e-08 real 5.4e-08}
! 662:
! 663: # Make sure that when a column is added by ALTER TABLE ADD COLUMN and has
! 664: # a default value that the default value is used by aggregate functions.
! 665: #
! 666: do_test alter-8.1 {
! 667: execsql {
! 668: CREATE TABLE t2(a INTEGER);
! 669: INSERT INTO t2 VALUES(1);
! 670: INSERT INTO t2 VALUES(1);
! 671: INSERT INTO t2 VALUES(2);
! 672: ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
! 673: SELECT sum(b) FROM t2;
! 674: }
! 675: } {27}
! 676: do_test alter-8.2 {
! 677: execsql {
! 678: SELECT a, sum(b) FROM t2 GROUP BY a;
! 679: }
! 680: } {1 18 2 9}
! 681:
! 682: #--------------------------------------------------------------------------
! 683: # alter-9.X - Special test: Make sure the sqlite_rename_trigger() and
! 684: # rename_table() functions do not crash when handed bad input.
! 685: #
! 686: ifcapable trigger {
! 687: do_test alter-9.1 {
! 688: execsql {SELECT SQLITE_RENAME_TRIGGER(0,0)}
! 689: } {{}}
! 690: }
! 691: do_test alter-9.2 {
! 692: execsql {
! 693: SELECT SQLITE_RENAME_TABLE(0,0);
! 694: SELECT SQLITE_RENAME_TABLE(10,20);
! 695: SELECT SQLITE_RENAME_TABLE('foo', 'foo');
! 696: }
! 697: } {{} {} {}}
! 698:
! 699: #------------------------------------------------------------------------
! 700: # alter-10.X - Make sure ALTER TABLE works with multi-byte UTF-8 characters
! 701: # in the names.
! 702: #
! 703: do_test alter-10.1 {
! 704: execsql "CREATE TABLE xyz(x UNIQUE)"
! 705: execsql "ALTER TABLE xyz RENAME TO xyz\u1234abc"
! 706: execsql {SELECT name FROM sqlite_master WHERE name GLOB 'xyz*'}
! 707: } [list xyz\u1234abc]
! 708: do_test alter-10.2 {
! 709: execsql {SELECT name FROM sqlite_master WHERE name GLOB 'sqlite_autoindex*'}
! 710: } [list sqlite_autoindex_xyz\u1234abc_1]
! 711: do_test alter-10.3 {
! 712: execsql "ALTER TABLE xyz\u1234abc RENAME TO xyzabc"
! 713: execsql {SELECT name FROM sqlite_master WHERE name GLOB 'xyz*'}
! 714: } [list xyzabc]
! 715: do_test alter-10.4 {
! 716: execsql {SELECT name FROM sqlite_master WHERE name GLOB 'sqlite_autoindex*'}
! 717: } [list sqlite_autoindex_xyzabc_1]
! 718:
! 719: do_test alter-11.1 {
! 720: sqlite3_exec db {CREATE TABLE t11(%c6%c6)}
! 721: execsql {
! 722: ALTER TABLE t11 ADD COLUMN abc;
! 723: }
! 724: catchsql {
! 725: ALTER TABLE t11 ADD COLUMN abc;
! 726: }
! 727: } {1 {duplicate column name: abc}}
! 728: set isutf16 [regexp 16 [db one {PRAGMA encoding}]]
! 729: if {!$isutf16} {
! 730: do_test alter-11.2 {
! 731: execsql {INSERT INTO t11 VALUES(1,2)}
! 732: sqlite3_exec db {SELECT %c6%c6 AS xyz, abc FROM t11}
! 733: } {0 {xyz abc 1 2}}
! 734: }
! 735: do_test alter-11.3 {
! 736: sqlite3_exec db {CREATE TABLE t11b("%81%82%83" text)}
! 737: execsql {
! 738: ALTER TABLE t11b ADD COLUMN abc;
! 739: }
! 740: catchsql {
! 741: ALTER TABLE t11b ADD COLUMN abc;
! 742: }
! 743: } {1 {duplicate column name: abc}}
! 744: if {!$isutf16} {
! 745: do_test alter-11.4 {
! 746: execsql {INSERT INTO t11b VALUES(3,4)}
! 747: sqlite3_exec db {SELECT %81%82%83 AS xyz, abc FROM t11b}
! 748: } {0 {xyz abc 3 4}}
! 749: do_test alter-11.5 {
! 750: sqlite3_exec db {SELECT [%81%82%83] AS xyz, abc FROM t11b}
! 751: } {0 {xyz abc 3 4}}
! 752: do_test alter-11.6 {
! 753: sqlite3_exec db {SELECT "%81%82%83" AS xyz, abc FROM t11b}
! 754: } {0 {xyz abc 3 4}}
! 755: }
! 756: do_test alter-11.7 {
! 757: sqlite3_exec db {CREATE TABLE t11c(%81%82%83 text)}
! 758: execsql {
! 759: ALTER TABLE t11c ADD COLUMN abc;
! 760: }
! 761: catchsql {
! 762: ALTER TABLE t11c ADD COLUMN abc;
! 763: }
! 764: } {1 {duplicate column name: abc}}
! 765: if {!$isutf16} {
! 766: do_test alter-11.8 {
! 767: execsql {INSERT INTO t11c VALUES(5,6)}
! 768: sqlite3_exec db {SELECT %81%82%83 AS xyz, abc FROM t11c}
! 769: } {0 {xyz abc 5 6}}
! 770: do_test alter-11.9 {
! 771: sqlite3_exec db {SELECT [%81%82%83] AS xyz, abc FROM t11c}
! 772: } {0 {xyz abc 5 6}}
! 773: do_test alter-11.10 {
! 774: sqlite3_exec db {SELECT "%81%82%83" AS xyz, abc FROM t11c}
! 775: } {0 {xyz abc 5 6}}
! 776: }
! 777:
! 778: do_test alter-12.1 {
! 779: execsql {
! 780: CREATE TABLE t12(a, b, c);
! 781: CREATE VIEW v1 AS SELECT * FROM t12;
! 782: }
! 783: } {}
! 784: do_test alter-12.2 {
! 785: catchsql {
! 786: ALTER TABLE v1 RENAME TO v2;
! 787: }
! 788: } {1 {view v1 may not be altered}}
! 789: do_test alter-12.3 {
! 790: execsql { SELECT * FROM v1; }
! 791: } {}
! 792: do_test alter-12.4 {
! 793: db close
! 794: sqlite3 db test.db
! 795: execsql { SELECT * FROM v1; }
! 796: } {}
! 797: do_test alter-12.5 {
! 798: catchsql {
! 799: ALTER TABLE v1 ADD COLUMN new_column;
! 800: }
! 801: } {1 {Cannot add a column to a view}}
! 802:
! 803: # Ticket #3102:
! 804: # Verify that comments do not interfere with the table rename
! 805: # algorithm.
! 806: #
! 807: do_test alter-13.1 {
! 808: execsql {
! 809: CREATE TABLE /* hi */ t3102a(x);
! 810: CREATE TABLE t3102b -- comment
! 811: (y);
! 812: CREATE INDEX t3102c ON t3102a(x);
! 813: SELECT name FROM sqlite_master WHERE name GLOB 't3102*' ORDER BY 1;
! 814: }
! 815: } {t3102a t3102b t3102c}
! 816: do_test alter-13.2 {
! 817: execsql {
! 818: ALTER TABLE t3102a RENAME TO t3102a_rename;
! 819: SELECT name FROM sqlite_master WHERE name GLOB 't3102*' ORDER BY 1;
! 820: }
! 821: } {t3102a_rename t3102b t3102c}
! 822: do_test alter-13.3 {
! 823: execsql {
! 824: ALTER TABLE t3102b RENAME TO t3102b_rename;
! 825: SELECT name FROM sqlite_master WHERE name GLOB 't3102*' ORDER BY 1;
! 826: }
! 827: } {t3102a_rename t3102b_rename t3102c}
! 828:
! 829: # Ticket #3651
! 830: do_test alter-14.1 {
! 831: catchsql {
! 832: CREATE TABLE t3651(a UNIQUE);
! 833: ALTER TABLE t3651 ADD COLUMN b UNIQUE;
! 834: }
! 835: } {1 {Cannot add a UNIQUE column}}
! 836: do_test alter-14.2 {
! 837: catchsql {
! 838: ALTER TABLE t3651 ADD COLUMN b PRIMARY KEY;
! 839: }
! 840: } {1 {Cannot add a PRIMARY KEY column}}
! 841:
! 842:
! 843: #-------------------------------------------------------------------------
! 844: # Test that it is not possible to use ALTER TABLE on any system table.
! 845: #
! 846: set system_table_list {1 sqlite_master}
! 847: catchsql ANALYZE
! 848: ifcapable analyze { lappend system_table_list 2 sqlite_stat1 }
! 849: ifcapable stat3 { lappend system_table_list 4 sqlite_stat3 }
! 850:
! 851: foreach {tn tbl} $system_table_list {
! 852: do_test alter-15.$tn.1 {
! 853: catchsql "ALTER TABLE $tbl RENAME TO xyz"
! 854: } [list 1 "table $tbl may not be altered"]
! 855:
! 856: do_test alter-15.$tn.2 {
! 857: catchsql "ALTER TABLE $tbl ADD COLUMN xyz"
! 858: } [list 1 "table $tbl may not be altered"]
! 859: }
! 860:
! 861:
! 862: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to alter.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test