Annotation of embedaddon/sqlite3/test/alter3.test, revision 1.1
1.1 ! misho 1: # 2005 February 19
! 2: #
! 3: # The author disclaims copyright to this source code. In place of
! 4: # a legal notice, here is a blessing:
! 5: #
! 6: # May you do good and not evil.
! 7: # May you find forgiveness for yourself and forgive others.
! 8: # May you share freely, never taking more than you give.
! 9: #
! 10: #*************************************************************************
! 11: # This file implements regression tests for SQLite library. The
! 12: # focus of this script is testing that SQLite can handle a subtle
! 13: # file format change that may be used in the future to implement
! 14: # "ALTER TABLE ... ADD COLUMN".
! 15: #
! 16: # $Id: alter3.test,v 1.11 2008/03/19 00:21:31 drh Exp $
! 17: #
! 18:
! 19: set testdir [file dirname $argv0]
! 20:
! 21: source $testdir/tester.tcl
! 22:
! 23: # If SQLITE_OMIT_ALTERTABLE is defined, omit this file.
! 24: ifcapable !altertable {
! 25: finish_test
! 26: return
! 27: }
! 28:
! 29: # Determine if there is a codec available on this test.
! 30: #
! 31: if {[catch {sqlite3 -has-codec} r] || $r} {
! 32: set has_codec 1
! 33: } else {
! 34: set has_codec 0
! 35: }
! 36:
! 37:
! 38: # Test Organisation:
! 39: # ------------------
! 40: #
! 41: # alter3-1.*: Test that ALTER TABLE correctly modifies the CREATE TABLE sql.
! 42: # alter3-2.*: Test error messages.
! 43: # alter3-3.*: Test adding columns with default value NULL.
! 44: # alter3-4.*: Test adding columns with default values other than NULL.
! 45: # alter3-5.*: Test adding columns to tables in ATTACHed databases.
! 46: # alter3-6.*: Test that temp triggers are not accidentally dropped.
! 47: # alter3-7.*: Test that VACUUM resets the file-format.
! 48: #
! 49:
! 50: # This procedure returns the value of the file-format in file 'test.db'.
! 51: #
! 52: proc get_file_format {{fname test.db}} {
! 53: return [hexio_get_int [hexio_read $fname 44 4]]
! 54: }
! 55:
! 56: do_test alter3-1.1 {
! 57: execsql {
! 58: PRAGMA legacy_file_format=ON;
! 59: CREATE TABLE abc(a, b, c);
! 60: SELECT sql FROM sqlite_master;
! 61: }
! 62: } {{CREATE TABLE abc(a, b, c)}}
! 63: do_test alter3-1.2 {
! 64: execsql {ALTER TABLE abc ADD d INTEGER;}
! 65: execsql {
! 66: SELECT sql FROM sqlite_master;
! 67: }
! 68: } {{CREATE TABLE abc(a, b, c, d INTEGER)}}
! 69: do_test alter3-1.3 {
! 70: execsql {ALTER TABLE abc ADD e}
! 71: execsql {
! 72: SELECT sql FROM sqlite_master;
! 73: }
! 74: } {{CREATE TABLE abc(a, b, c, d INTEGER, e)}}
! 75: do_test alter3-1.4 {
! 76: execsql {
! 77: CREATE TABLE main.t1(a, b);
! 78: ALTER TABLE t1 ADD c;
! 79: SELECT sql FROM sqlite_master WHERE tbl_name = 't1';
! 80: }
! 81: } {{CREATE TABLE t1(a, b, c)}}
! 82: do_test alter3-1.5 {
! 83: execsql {
! 84: ALTER TABLE t1 ADD d CHECK (a>d);
! 85: SELECT sql FROM sqlite_master WHERE tbl_name = 't1';
! 86: }
! 87: } {{CREATE TABLE t1(a, b, c, d CHECK (a>d))}}
! 88: ifcapable foreignkey {
! 89: do_test alter3-1.6 {
! 90: execsql {
! 91: CREATE TABLE t2(a, b, UNIQUE(a, b));
! 92: ALTER TABLE t2 ADD c REFERENCES t1(c) ;
! 93: SELECT sql FROM sqlite_master WHERE tbl_name = 't2' AND type = 'table';
! 94: }
! 95: } {{CREATE TABLE t2(a, b, c REFERENCES t1(c), UNIQUE(a, b))}}
! 96: }
! 97: do_test alter3-1.7 {
! 98: execsql {
! 99: CREATE TABLE t3(a, b, UNIQUE(a, b));
! 100: ALTER TABLE t3 ADD COLUMN c VARCHAR(10, 20);
! 101: SELECT sql FROM sqlite_master WHERE tbl_name = 't3' AND type = 'table';
! 102: }
! 103: } {{CREATE TABLE t3(a, b, c VARCHAR(10, 20), UNIQUE(a, b))}}
! 104: do_test alter3-1.99 {
! 105: catchsql {
! 106: # May not exist if foriegn-keys are omitted at compile time.
! 107: DROP TABLE t2;
! 108: }
! 109: execsql {
! 110: DROP TABLE abc;
! 111: DROP TABLE t1;
! 112: DROP TABLE t3;
! 113: }
! 114: } {}
! 115:
! 116: do_test alter3-2.1 {
! 117: execsql {
! 118: CREATE TABLE t1(a, b);
! 119: }
! 120: catchsql {
! 121: ALTER TABLE t1 ADD c PRIMARY KEY;
! 122: }
! 123: } {1 {Cannot add a PRIMARY KEY column}}
! 124: do_test alter3-2.2 {
! 125: catchsql {
! 126: ALTER TABLE t1 ADD c UNIQUE
! 127: }
! 128: } {1 {Cannot add a UNIQUE column}}
! 129: do_test alter3-2.3 {
! 130: catchsql {
! 131: ALTER TABLE t1 ADD b VARCHAR(10)
! 132: }
! 133: } {1 {duplicate column name: b}}
! 134: do_test alter3-2.3 {
! 135: catchsql {
! 136: ALTER TABLE t1 ADD c NOT NULL;
! 137: }
! 138: } {1 {Cannot add a NOT NULL column with default value NULL}}
! 139: do_test alter3-2.4 {
! 140: catchsql {
! 141: ALTER TABLE t1 ADD c NOT NULL DEFAULT 10;
! 142: }
! 143: } {0 {}}
! 144: ifcapable view {
! 145: do_test alter3-2.5 {
! 146: execsql {
! 147: CREATE VIEW v1 AS SELECT * FROM t1;
! 148: }
! 149: catchsql {
! 150: alter table v1 add column d;
! 151: }
! 152: } {1 {Cannot add a column to a view}}
! 153: }
! 154: do_test alter3-2.6 {
! 155: catchsql {
! 156: alter table t1 add column d DEFAULT CURRENT_TIME;
! 157: }
! 158: } {1 {Cannot add a column with non-constant default}}
! 159: do_test alter3-2.99 {
! 160: execsql {
! 161: DROP TABLE t1;
! 162: }
! 163: } {}
! 164:
! 165: do_test alter3-3.1 {
! 166: execsql {
! 167: CREATE TABLE t1(a, b);
! 168: INSERT INTO t1 VALUES(1, 100);
! 169: INSERT INTO t1 VALUES(2, 300);
! 170: SELECT * FROM t1;
! 171: }
! 172: } {1 100 2 300}
! 173: do_test alter3-3.1 {
! 174: execsql {
! 175: PRAGMA schema_version = 10;
! 176: }
! 177: } {}
! 178: do_test alter3-3.2 {
! 179: execsql {
! 180: ALTER TABLE t1 ADD c;
! 181: SELECT * FROM t1;
! 182: }
! 183: } {1 100 {} 2 300 {}}
! 184: if {!$has_codec} {
! 185: do_test alter3-3.3 {
! 186: get_file_format
! 187: } {3}
! 188: }
! 189: ifcapable schema_version {
! 190: do_test alter3-3.4 {
! 191: execsql {
! 192: PRAGMA schema_version;
! 193: }
! 194: } {11}
! 195: }
! 196:
! 197: do_test alter3-4.1 {
! 198: db close
! 199: forcedelete test.db
! 200: set ::DB [sqlite3 db test.db]
! 201: execsql {
! 202: PRAGMA legacy_file_format=ON;
! 203: CREATE TABLE t1(a, b);
! 204: INSERT INTO t1 VALUES(1, 100);
! 205: INSERT INTO t1 VALUES(2, 300);
! 206: SELECT * FROM t1;
! 207: }
! 208: } {1 100 2 300}
! 209: do_test alter3-4.1 {
! 210: execsql {
! 211: PRAGMA schema_version = 20;
! 212: }
! 213: } {}
! 214: do_test alter3-4.2 {
! 215: execsql {
! 216: ALTER TABLE t1 ADD c DEFAULT 'hello world';
! 217: SELECT * FROM t1;
! 218: }
! 219: } {1 100 {hello world} 2 300 {hello world}}
! 220: if {!$has_codec} {
! 221: do_test alter3-4.3 {
! 222: get_file_format
! 223: } {3}
! 224: }
! 225: ifcapable schema_version {
! 226: do_test alter3-4.4 {
! 227: execsql {
! 228: PRAGMA schema_version;
! 229: }
! 230: } {21}
! 231: }
! 232: do_test alter3-4.99 {
! 233: execsql {
! 234: DROP TABLE t1;
! 235: }
! 236: } {}
! 237:
! 238: ifcapable attach {
! 239: do_test alter3-5.1 {
! 240: forcedelete test2.db
! 241: forcedelete test2.db-journal
! 242: execsql {
! 243: CREATE TABLE t1(a, b);
! 244: INSERT INTO t1 VALUES(1, 'one');
! 245: INSERT INTO t1 VALUES(2, 'two');
! 246: ATTACH 'test2.db' AS aux;
! 247: CREATE TABLE aux.t1 AS SELECT * FROM t1;
! 248: PRAGMA aux.schema_version = 30;
! 249: SELECT sql FROM aux.sqlite_master;
! 250: }
! 251: } {{CREATE TABLE t1(a,b)}}
! 252: do_test alter3-5.2 {
! 253: execsql {
! 254: ALTER TABLE aux.t1 ADD COLUMN c VARCHAR(128);
! 255: SELECT sql FROM aux.sqlite_master;
! 256: }
! 257: } {{CREATE TABLE t1(a,b, c VARCHAR(128))}}
! 258: do_test alter3-5.3 {
! 259: execsql {
! 260: SELECT * FROM aux.t1;
! 261: }
! 262: } {1 one {} 2 two {}}
! 263: ifcapable schema_version {
! 264: do_test alter3-5.4 {
! 265: execsql {
! 266: PRAGMA aux.schema_version;
! 267: }
! 268: } {31}
! 269: }
! 270: if {!$has_codec} {
! 271: do_test alter3-5.5 {
! 272: list [get_file_format test2.db] [get_file_format]
! 273: } {2 3}
! 274: }
! 275: do_test alter3-5.6 {
! 276: execsql {
! 277: ALTER TABLE aux.t1 ADD COLUMN d DEFAULT 1000;
! 278: SELECT sql FROM aux.sqlite_master;
! 279: }
! 280: } {{CREATE TABLE t1(a,b, c VARCHAR(128), d DEFAULT 1000)}}
! 281: do_test alter3-5.7 {
! 282: execsql {
! 283: SELECT * FROM aux.t1;
! 284: }
! 285: } {1 one {} 1000 2 two {} 1000}
! 286: ifcapable schema_version {
! 287: do_test alter3-5.8 {
! 288: execsql {
! 289: PRAGMA aux.schema_version;
! 290: }
! 291: } {32}
! 292: }
! 293: do_test alter3-5.9 {
! 294: execsql {
! 295: SELECT * FROM t1;
! 296: }
! 297: } {1 one 2 two}
! 298: do_test alter3-5.99 {
! 299: execsql {
! 300: DROP TABLE aux.t1;
! 301: DROP TABLE t1;
! 302: }
! 303: } {}
! 304: }
! 305:
! 306: #----------------------------------------------------------------
! 307: # Test that the table schema is correctly reloaded when a column
! 308: # is added to a table.
! 309: #
! 310: ifcapable trigger&&tempdb {
! 311: do_test alter3-6.1 {
! 312: execsql {
! 313: CREATE TABLE t1(a, b);
! 314: CREATE TABLE log(trig, a, b);
! 315:
! 316: CREATE TRIGGER t1_a AFTER INSERT ON t1 BEGIN
! 317: INSERT INTO log VALUES('a', new.a, new.b);
! 318: END;
! 319: CREATE TEMP TRIGGER t1_b AFTER INSERT ON t1 BEGIN
! 320: INSERT INTO log VALUES('b', new.a, new.b);
! 321: END;
! 322:
! 323: INSERT INTO t1 VALUES(1, 2);
! 324: SELECT * FROM log;
! 325: }
! 326: } {b 1 2 a 1 2}
! 327: do_test alter3-6.2 {
! 328: execsql {
! 329: ALTER TABLE t1 ADD COLUMN c DEFAULT 'c';
! 330: INSERT INTO t1(a, b) VALUES(3, 4);
! 331: SELECT * FROM log;
! 332: }
! 333: } {b 1 2 a 1 2 b 3 4 a 3 4}
! 334: }
! 335:
! 336: if {!$has_codec} {
! 337: ifcapable vacuum {
! 338: do_test alter3-7.1 {
! 339: execsql {
! 340: VACUUM;
! 341: }
! 342: get_file_format
! 343: } {1}
! 344: do_test alter3-7.2 {
! 345: execsql {
! 346: CREATE TABLE abc(a, b, c);
! 347: ALTER TABLE abc ADD d DEFAULT NULL;
! 348: }
! 349: get_file_format
! 350: } {2}
! 351: do_test alter3-7.3 {
! 352: execsql {
! 353: ALTER TABLE abc ADD e DEFAULT 10;
! 354: }
! 355: get_file_format
! 356: } {3}
! 357: do_test alter3-7.4 {
! 358: execsql {
! 359: ALTER TABLE abc ADD f DEFAULT NULL;
! 360: }
! 361: get_file_format
! 362: } {3}
! 363: do_test alter3-7.5 {
! 364: execsql {
! 365: VACUUM;
! 366: }
! 367: get_file_format
! 368: } {1}
! 369: }
! 370: }
! 371:
! 372: # Ticket #1183 - Make sure adding columns to large tables does not cause
! 373: # memory corruption (as was the case before this bug was fixed).
! 374: do_test alter3-8.1 {
! 375: execsql {
! 376: CREATE TABLE t4(c1);
! 377: }
! 378: } {}
! 379: set ::sql ""
! 380: do_test alter3-8.2 {
! 381: set cols c1
! 382: for {set i 2} {$i < 100} {incr i} {
! 383: execsql "
! 384: ALTER TABLE t4 ADD c$i
! 385: "
! 386: lappend cols c$i
! 387: }
! 388: set ::sql "CREATE TABLE t4([join $cols {, }])"
! 389: list
! 390: } {}
! 391: do_test alter3-8.2 {
! 392: execsql {
! 393: SELECT sql FROM sqlite_master WHERE name = 't4';
! 394: }
! 395: } [list $::sql]
! 396:
! 397: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to alter3.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