Annotation of embedaddon/sqlite3/test/alter4.test, revision 1.1
1.1 ! misho 1: # 2009 February 2
! 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: alter4.test,v 1.1 2009/02/02 18:03:22 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:
! 30: # Test Organisation:
! 31: # ------------------
! 32: #
! 33: # alter4-1.*: Test that ALTER TABLE correctly modifies the CREATE TABLE sql.
! 34: # alter4-2.*: Test error messages.
! 35: # alter4-3.*: Test adding columns with default value NULL.
! 36: # alter4-4.*: Test adding columns with default values other than NULL.
! 37: # alter4-5.*: Test adding columns to tables in ATTACHed databases.
! 38: # alter4-6.*: Test that temp triggers are not accidentally dropped.
! 39: # alter4-7.*: Test that VACUUM resets the file-format.
! 40: #
! 41:
! 42: do_test alter4-1.1 {
! 43: execsql {
! 44: CREATE TEMP TABLE abc(a, b, c);
! 45: SELECT sql FROM sqlite_temp_master;
! 46: }
! 47: } {{CREATE TABLE abc(a, b, c)}}
! 48: do_test alter4-1.2 {
! 49: execsql {ALTER TABLE abc ADD d INTEGER;}
! 50: execsql {
! 51: SELECT sql FROM sqlite_temp_master;
! 52: }
! 53: } {{CREATE TABLE abc(a, b, c, d INTEGER)}}
! 54: do_test alter4-1.3 {
! 55: execsql {ALTER TABLE abc ADD e}
! 56: execsql {
! 57: SELECT sql FROM sqlite_temp_master;
! 58: }
! 59: } {{CREATE TABLE abc(a, b, c, d INTEGER, e)}}
! 60: do_test alter4-1.4 {
! 61: execsql {
! 62: CREATE TABLE temp.t1(a, b);
! 63: ALTER TABLE t1 ADD c;
! 64: SELECT sql FROM sqlite_temp_master WHERE tbl_name = 't1';
! 65: }
! 66: } {{CREATE TABLE t1(a, b, c)}}
! 67: do_test alter4-1.5 {
! 68: execsql {
! 69: ALTER TABLE t1 ADD d CHECK (a>d);
! 70: SELECT sql FROM sqlite_temp_master WHERE tbl_name = 't1';
! 71: }
! 72: } {{CREATE TABLE t1(a, b, c, d CHECK (a>d))}}
! 73: ifcapable foreignkey {
! 74: do_test alter4-1.6 {
! 75: execsql {
! 76: CREATE TEMP TABLE t2(a, b, UNIQUE(a, b));
! 77: ALTER TABLE t2 ADD c REFERENCES t1(c) ;
! 78: SELECT sql FROM sqlite_temp_master
! 79: WHERE tbl_name = 't2' AND type = 'table';
! 80: }
! 81: } {{CREATE TABLE t2(a, b, c REFERENCES t1(c), UNIQUE(a, b))}}
! 82: }
! 83: do_test alter4-1.7 {
! 84: execsql {
! 85: CREATE TEMPORARY TABLE t3(a, b, UNIQUE(a, b));
! 86: ALTER TABLE t3 ADD COLUMN c VARCHAR(10, 20);
! 87: SELECT sql FROM sqlite_temp_master
! 88: WHERE tbl_name = 't3' AND type = 'table';
! 89: }
! 90: } {{CREATE TABLE t3(a, b, c VARCHAR(10, 20), UNIQUE(a, b))}}
! 91: do_test alter4-1.99 {
! 92: catchsql {
! 93: # May not exist if foriegn-keys are omitted at compile time.
! 94: DROP TABLE t2;
! 95: }
! 96: execsql {
! 97: DROP TABLE abc;
! 98: DROP TABLE t1;
! 99: DROP TABLE t3;
! 100: }
! 101: } {}
! 102:
! 103: do_test alter4-2.1 {
! 104: execsql {
! 105: CREATE TABLE temp.t1(a, b);
! 106: }
! 107: catchsql {
! 108: ALTER TABLE t1 ADD c PRIMARY KEY;
! 109: }
! 110: } {1 {Cannot add a PRIMARY KEY column}}
! 111: do_test alter4-2.2 {
! 112: catchsql {
! 113: ALTER TABLE t1 ADD c UNIQUE
! 114: }
! 115: } {1 {Cannot add a UNIQUE column}}
! 116: do_test alter4-2.3 {
! 117: catchsql {
! 118: ALTER TABLE t1 ADD b VARCHAR(10)
! 119: }
! 120: } {1 {duplicate column name: b}}
! 121: do_test alter4-2.3 {
! 122: catchsql {
! 123: ALTER TABLE t1 ADD c NOT NULL;
! 124: }
! 125: } {1 {Cannot add a NOT NULL column with default value NULL}}
! 126: do_test alter4-2.4 {
! 127: catchsql {
! 128: ALTER TABLE t1 ADD c NOT NULL DEFAULT 10;
! 129: }
! 130: } {0 {}}
! 131: ifcapable view {
! 132: do_test alter4-2.5 {
! 133: execsql {
! 134: CREATE TEMPORARY VIEW v1 AS SELECT * FROM t1;
! 135: }
! 136: catchsql {
! 137: alter table v1 add column d;
! 138: }
! 139: } {1 {Cannot add a column to a view}}
! 140: }
! 141: do_test alter4-2.6 {
! 142: catchsql {
! 143: alter table t1 add column d DEFAULT CURRENT_TIME;
! 144: }
! 145: } {1 {Cannot add a column with non-constant default}}
! 146: do_test alter4-2.99 {
! 147: execsql {
! 148: DROP TABLE t1;
! 149: }
! 150: } {}
! 151:
! 152: do_test alter4-3.1 {
! 153: execsql {
! 154: CREATE TEMP TABLE t1(a, b);
! 155: INSERT INTO t1 VALUES(1, 100);
! 156: INSERT INTO t1 VALUES(2, 300);
! 157: SELECT * FROM t1;
! 158: }
! 159: } {1 100 2 300}
! 160: do_test alter4-3.1 {
! 161: execsql {
! 162: PRAGMA schema_version = 10;
! 163: }
! 164: } {}
! 165: do_test alter4-3.2 {
! 166: execsql {
! 167: ALTER TABLE t1 ADD c;
! 168: SELECT * FROM t1;
! 169: }
! 170: } {1 100 {} 2 300 {}}
! 171: ifcapable schema_version {
! 172: do_test alter4-3.4 {
! 173: execsql {
! 174: PRAGMA schema_version;
! 175: }
! 176: } {10}
! 177: }
! 178:
! 179: do_test alter4-4.1 {
! 180: db close
! 181: forcedelete test.db
! 182: set ::DB [sqlite3 db test.db]
! 183: execsql {
! 184: CREATE TEMP TABLE t1(a, b);
! 185: INSERT INTO t1 VALUES(1, 100);
! 186: INSERT INTO t1 VALUES(2, 300);
! 187: SELECT * FROM t1;
! 188: }
! 189: } {1 100 2 300}
! 190: do_test alter4-4.1 {
! 191: execsql {
! 192: PRAGMA schema_version = 20;
! 193: }
! 194: } {}
! 195: do_test alter4-4.2 {
! 196: execsql {
! 197: ALTER TABLE t1 ADD c DEFAULT 'hello world';
! 198: SELECT * FROM t1;
! 199: }
! 200: } {1 100 {hello world} 2 300 {hello world}}
! 201: ifcapable schema_version {
! 202: do_test alter4-4.4 {
! 203: execsql {
! 204: PRAGMA schema_version;
! 205: }
! 206: } {20}
! 207: }
! 208: do_test alter4-4.99 {
! 209: execsql {
! 210: DROP TABLE t1;
! 211: }
! 212: } {}
! 213:
! 214: ifcapable attach {
! 215: do_test alter4-5.1 {
! 216: forcedelete test2.db
! 217: forcedelete test2.db-journal
! 218: execsql {
! 219: CREATE TEMP TABLE t1(a, b);
! 220: INSERT INTO t1 VALUES(1, 'one');
! 221: INSERT INTO t1 VALUES(2, 'two');
! 222: ATTACH 'test2.db' AS aux;
! 223: CREATE TABLE aux.t1 AS SELECT * FROM t1;
! 224: PRAGMA aux.schema_version = 30;
! 225: SELECT sql FROM aux.sqlite_master;
! 226: }
! 227: } {{CREATE TABLE t1(a,b)}}
! 228: do_test alter4-5.2 {
! 229: execsql {
! 230: ALTER TABLE aux.t1 ADD COLUMN c VARCHAR(128);
! 231: SELECT sql FROM aux.sqlite_master;
! 232: }
! 233: } {{CREATE TABLE t1(a,b, c VARCHAR(128))}}
! 234: do_test alter4-5.3 {
! 235: execsql {
! 236: SELECT * FROM aux.t1;
! 237: }
! 238: } {1 one {} 2 two {}}
! 239: ifcapable schema_version {
! 240: do_test alter4-5.4 {
! 241: execsql {
! 242: PRAGMA aux.schema_version;
! 243: }
! 244: } {31}
! 245: }
! 246: do_test alter4-5.6 {
! 247: execsql {
! 248: ALTER TABLE aux.t1 ADD COLUMN d DEFAULT 1000;
! 249: SELECT sql FROM aux.sqlite_master;
! 250: }
! 251: } {{CREATE TABLE t1(a,b, c VARCHAR(128), d DEFAULT 1000)}}
! 252: do_test alter4-5.7 {
! 253: execsql {
! 254: SELECT * FROM aux.t1;
! 255: }
! 256: } {1 one {} 1000 2 two {} 1000}
! 257: ifcapable schema_version {
! 258: do_test alter4-5.8 {
! 259: execsql {
! 260: PRAGMA aux.schema_version;
! 261: }
! 262: } {32}
! 263: }
! 264: do_test alter4-5.9 {
! 265: execsql {
! 266: SELECT * FROM t1;
! 267: }
! 268: } {1 one 2 two}
! 269: do_test alter4-5.99 {
! 270: execsql {
! 271: DROP TABLE aux.t1;
! 272: DROP TABLE t1;
! 273: }
! 274: } {}
! 275: }
! 276:
! 277: #----------------------------------------------------------------
! 278: # Test that the table schema is correctly reloaded when a column
! 279: # is added to a table.
! 280: #
! 281: ifcapable trigger&&tempdb {
! 282: do_test alter4-6.1 {
! 283: execsql {
! 284: CREATE TEMP TABLE t1(a, b);
! 285: CREATE TEMP TABLE log(trig, a, b);
! 286:
! 287: CREATE TRIGGER t1_a AFTER INSERT ON t1 BEGIN
! 288: INSERT INTO log VALUES('a', new.a, new.b);
! 289: END;
! 290: CREATE TEMP TRIGGER t1_b AFTER INSERT ON t1 BEGIN
! 291: INSERT INTO log VALUES('b', new.a, new.b);
! 292: END;
! 293:
! 294: INSERT INTO t1 VALUES(1, 2);
! 295: SELECT * FROM log;
! 296: }
! 297: } {b 1 2 a 1 2}
! 298: do_test alter4-6.2 {
! 299: execsql {
! 300: ALTER TABLE t1 ADD COLUMN c DEFAULT 'c';
! 301: INSERT INTO t1(a, b) VALUES(3, 4);
! 302: SELECT * FROM log;
! 303: }
! 304: } {b 1 2 a 1 2 b 3 4 a 3 4}
! 305: }
! 306:
! 307: # Ticket #1183 - Make sure adding columns to large tables does not cause
! 308: # memory corruption (as was the case before this bug was fixed).
! 309: do_test alter4-8.1 {
! 310: execsql {
! 311: CREATE TEMP TABLE t4(c1);
! 312: }
! 313: } {}
! 314: set ::sql ""
! 315: do_test alter4-8.2 {
! 316: set cols c1
! 317: for {set i 2} {$i < 100} {incr i} {
! 318: execsql "
! 319: ALTER TABLE t4 ADD c$i
! 320: "
! 321: lappend cols c$i
! 322: }
! 323: set ::sql "CREATE TABLE t4([join $cols {, }])"
! 324: list
! 325: } {}
! 326: do_test alter4-8.2 {
! 327: execsql {
! 328: SELECT sql FROM sqlite_temp_master WHERE name = 't4';
! 329: }
! 330: } [list $::sql]
! 331:
! 332: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to alter4.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