Annotation of embedaddon/sqlite3/test/alter2.test, revision 1.1
1.1 ! misho 1: # 2005 February 18
! 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: alter2.test,v 1.14 2009/04/07 14:14:22 danielk1977 Exp $
! 17: #
! 18:
! 19: set testdir [file dirname $argv0]
! 20: source $testdir/tester.tcl
! 21:
! 22: # We have to have pragmas in order to do this test
! 23: ifcapable {!pragma} return
! 24:
! 25: # Do not use a codec for tests in this file, as the database file is
! 26: # manipulated directly using tcl scripts. See proc [set_file_format].
! 27: #
! 28: do_not_use_codec
! 29:
! 30: # The file format change affects the way row-records stored in tables (but
! 31: # not indices) are interpreted. Before version 3.1.3, a row-record for a
! 32: # table with N columns was guaranteed to contain exactly N fields. As
! 33: # of version 3.1.3, the record may contain up to N fields. In this case
! 34: # the M fields that are present are the values for the left-most M
! 35: # columns. The (N-M) rightmost columns contain NULL.
! 36: #
! 37: # If any records in the database contain less fields than their table
! 38: # has columns, then the file-format meta value should be set to (at least) 2.
! 39: #
! 40:
! 41: # This procedure sets the value of the file-format in file 'test.db'
! 42: # to $newval. Also, the schema cookie is incremented.
! 43: #
! 44: proc set_file_format {newval} {
! 45: hexio_write test.db 44 [hexio_render_int32 $newval]
! 46: set schemacookie [hexio_get_int [hexio_read test.db 40 4]]
! 47: incr schemacookie
! 48: hexio_write test.db 40 [hexio_render_int32 $schemacookie]
! 49: return {}
! 50: }
! 51:
! 52: # This procedure returns the value of the file-format in file 'test.db'.
! 53: #
! 54: proc get_file_format {{fname test.db}} {
! 55: return [hexio_get_int [hexio_read $fname 44 4]]
! 56: }
! 57:
! 58: # This procedure sets the SQL statement stored for table $tbl in the
! 59: # sqlite_master table of file 'test.db' to $sql. Also set the file format
! 60: # to the supplied value. This is 2 if the added column has a default that is
! 61: # NULL, or 3 otherwise.
! 62: #
! 63: proc alter_table {tbl sql {file_format 2}} {
! 64: sqlite3 dbat test.db
! 65: set s [string map {' ''} $sql]
! 66: set t [string map {' ''} $tbl]
! 67: dbat eval [subst {
! 68: PRAGMA writable_schema = 1;
! 69: UPDATE sqlite_master SET sql = '$s' WHERE name = '$t' AND type = 'table';
! 70: PRAGMA writable_schema = 0;
! 71: }]
! 72: dbat close
! 73: set_file_format 2
! 74: }
! 75:
! 76: # Create bogus application-defined functions for functions used
! 77: # internally by ALTER TABLE, to ensure that ALTER TABLE falls back
! 78: # to the built-in functions.
! 79: #
! 80: proc failing_app_func {args} {error "bad function"}
! 81: do_test alter2-1.0 {
! 82: db func substr failing_app_func
! 83: db func like failing_app_func
! 84: db func sqlite_rename_table failing_app_func
! 85: db func sqlite_rename_trigger failing_app_func
! 86: db func sqlite_rename_parent failing_app_func
! 87: catchsql {SELECT substr('abcdefg',1,3)}
! 88: } {1 {bad function}}
! 89:
! 90:
! 91: #-----------------------------------------------------------------------
! 92: # Some basic tests to make sure short rows are handled.
! 93: #
! 94: do_test alter2-1.1 {
! 95: execsql {
! 96: CREATE TABLE abc(a, b);
! 97: INSERT INTO abc VALUES(1, 2);
! 98: INSERT INTO abc VALUES(3, 4);
! 99: INSERT INTO abc VALUES(5, 6);
! 100: }
! 101: } {}
! 102: do_test alter2-1.2 {
! 103: # ALTER TABLE abc ADD COLUMN c;
! 104: alter_table abc {CREATE TABLE abc(a, b, c);}
! 105: } {}
! 106: do_test alter2-1.3 {
! 107: execsql {
! 108: SELECT * FROM abc;
! 109: }
! 110: } {1 2 {} 3 4 {} 5 6 {}}
! 111: do_test alter2-1.4 {
! 112: execsql {
! 113: UPDATE abc SET c = 10 WHERE a = 1;
! 114: SELECT * FROM abc;
! 115: }
! 116: } {1 2 10 3 4 {} 5 6 {}}
! 117: do_test alter2-1.5 {
! 118: execsql {
! 119: CREATE INDEX abc_i ON abc(c);
! 120: }
! 121: } {}
! 122: do_test alter2-1.6 {
! 123: execsql {
! 124: SELECT c FROM abc ORDER BY c;
! 125: }
! 126: } {{} {} 10}
! 127: do_test alter2-1.7 {
! 128: execsql {
! 129: SELECT * FROM abc WHERE c = 10;
! 130: }
! 131: } {1 2 10}
! 132: do_test alter2-1.8 {
! 133: execsql {
! 134: SELECT sum(a), c FROM abc GROUP BY c;
! 135: }
! 136: } {8 {} 1 10}
! 137: do_test alter2-1.9 {
! 138: # ALTER TABLE abc ADD COLUMN d;
! 139: alter_table abc {CREATE TABLE abc(a, b, c, d);}
! 140: if {[permutation] == "prepare"} { db cache flush }
! 141: execsql { SELECT * FROM abc; }
! 142: execsql {
! 143: UPDATE abc SET d = 11 WHERE c IS NULL AND a<4;
! 144: SELECT * FROM abc;
! 145: }
! 146: } {1 2 10 {} 3 4 {} 11 5 6 {} {}}
! 147: do_test alter2-1.10 {
! 148: execsql {
! 149: SELECT typeof(d) FROM abc;
! 150: }
! 151: } {null integer null}
! 152: do_test alter2-1.99 {
! 153: execsql {
! 154: DROP TABLE abc;
! 155: }
! 156: } {}
! 157:
! 158: #-----------------------------------------------------------------------
! 159: # Test that views work when the underlying table structure is changed.
! 160: #
! 161: ifcapable view {
! 162: do_test alter2-2.1 {
! 163: execsql {
! 164: CREATE TABLE abc2(a, b, c);
! 165: INSERT INTO abc2 VALUES(1, 2, 10);
! 166: INSERT INTO abc2 VALUES(3, 4, NULL);
! 167: INSERT INTO abc2 VALUES(5, 6, NULL);
! 168: CREATE VIEW abc2_v AS SELECT * FROM abc2;
! 169: SELECT * FROM abc2_v;
! 170: }
! 171: } {1 2 10 3 4 {} 5 6 {}}
! 172: do_test alter2-2.2 {
! 173: # ALTER TABLE abc ADD COLUMN d;
! 174: alter_table abc2 {CREATE TABLE abc2(a, b, c, d);}
! 175: execsql {
! 176: SELECT * FROM abc2_v;
! 177: }
! 178: } {1 2 10 {} 3 4 {} {} 5 6 {} {}}
! 179: do_test alter2-2.3 {
! 180: execsql {
! 181: DROP TABLE abc2;
! 182: DROP VIEW abc2_v;
! 183: }
! 184: } {}
! 185: }
! 186:
! 187: #-----------------------------------------------------------------------
! 188: # Test that triggers work when a short row is copied to the old.*
! 189: # trigger pseudo-table.
! 190: #
! 191: ifcapable trigger {
! 192: do_test alter2-3.1 {
! 193: execsql {
! 194: CREATE TABLE abc3(a, b);
! 195: CREATE TABLE blog(o, n);
! 196: CREATE TRIGGER abc3_t AFTER UPDATE OF b ON abc3 BEGIN
! 197: INSERT INTO blog VALUES(old.b, new.b);
! 198: END;
! 199: }
! 200: } {}
! 201: do_test alter2-3.2 {
! 202: execsql {
! 203: INSERT INTO abc3 VALUES(1, 4);
! 204: UPDATE abc3 SET b = 2 WHERE b = 4;
! 205: SELECT * FROM blog;
! 206: }
! 207: } {4 2}
! 208: do_test alter2-3.3 {
! 209: execsql {
! 210: INSERT INTO abc3 VALUES(3, 4);
! 211: INSERT INTO abc3 VALUES(5, 6);
! 212: }
! 213: alter_table abc3 {CREATE TABLE abc3(a, b, c);}
! 214: execsql {
! 215: SELECT * FROM abc3;
! 216: }
! 217: } {1 2 {} 3 4 {} 5 6 {}}
! 218: do_test alter2-3.4 {
! 219: execsql {
! 220: UPDATE abc3 SET b = b*2 WHERE a<4;
! 221: SELECT * FROM abc3;
! 222: }
! 223: } {1 4 {} 3 8 {} 5 6 {}}
! 224: do_test alter2-3.5 {
! 225: execsql {
! 226: SELECT * FROM blog;
! 227: }
! 228: } {4 2 2 4 4 8}
! 229:
! 230: do_test alter2-3.6 {
! 231: execsql {
! 232: CREATE TABLE clog(o, n);
! 233: CREATE TRIGGER abc3_t2 AFTER UPDATE OF c ON abc3 BEGIN
! 234: INSERT INTO clog VALUES(old.c, new.c);
! 235: END;
! 236: UPDATE abc3 SET c = a*2;
! 237: SELECT * FROM clog;
! 238: }
! 239: } {{} 2 {} 6 {} 10}
! 240: } else {
! 241: execsql { CREATE TABLE abc3(a, b); }
! 242: }
! 243:
! 244: #---------------------------------------------------------------------
! 245: # Check that an error occurs if the database is upgraded to a file
! 246: # format that SQLite does not support (in this case 5). Note: The
! 247: # file format is checked each time the schema is read, so changing the
! 248: # file format requires incrementing the schema cookie.
! 249: #
! 250: do_test alter2-4.1 {
! 251: db close
! 252: set_file_format 5
! 253: catch { sqlite3 db test.db }
! 254: set {} {}
! 255: } {}
! 256: do_test alter2-4.2 {
! 257: # We have to run two queries here because the Tcl interface uses
! 258: # sqlite3_prepare_v2(). In this case, the first query encounters an
! 259: # SQLITE_SCHEMA error. Then, when trying to recompile the statement, the
! 260: # "unsupported file format" error is encountered. So the error code
! 261: # returned is SQLITE_SCHEMA, not SQLITE_ERROR as required by the following
! 262: # test case.
! 263: #
! 264: # When the query is attempted a second time, the same error message is
! 265: # returned but the error code is SQLITE_ERROR, because the unsupported
! 266: # file format was detected during a call to sqlite3_prepare(), not
! 267: # sqlite3_step().
! 268: #
! 269: catchsql { SELECT * FROM sqlite_master; }
! 270: catchsql { SELECT * FROM sqlite_master; }
! 271: } {1 {unsupported file format}}
! 272: do_test alter2-4.3 {
! 273: sqlite3_errcode db
! 274: } {SQLITE_ERROR}
! 275: do_test alter2-4.4 {
! 276: set ::DB [sqlite3_connection_pointer db]
! 277: catchsql {
! 278: SELECT * FROM sqlite_master;
! 279: }
! 280: } {1 {unsupported file format}}
! 281: do_test alter2-4.5 {
! 282: sqlite3_errcode db
! 283: } {SQLITE_ERROR}
! 284:
! 285: #---------------------------------------------------------------------
! 286: # Check that executing VACUUM on a file with file-format version 2
! 287: # resets the file format to 1.
! 288: #
! 289: set default_file_format [expr $SQLITE_DEFAULT_FILE_FORMAT==4 ? 4 : 1]
! 290: ifcapable vacuum {
! 291: do_test alter2-5.1 {
! 292: set_file_format 2
! 293: db close
! 294: sqlite3 db test.db
! 295: execsql {SELECT 1 FROM sqlite_master LIMIT 1;}
! 296: get_file_format
! 297: } {2}
! 298: do_test alter2-5.2 {
! 299: execsql { VACUUM }
! 300: } {}
! 301: do_test alter2-5.3 {
! 302: get_file_format
! 303: } $default_file_format
! 304: }
! 305:
! 306: #---------------------------------------------------------------------
! 307: # Test that when a database with file-format 2 is opened, new
! 308: # databases are still created with file-format 1.
! 309: #
! 310: do_test alter2-6.1 {
! 311: db close
! 312: set_file_format 2
! 313: sqlite3 db test.db
! 314: get_file_format
! 315: } {2}
! 316: ifcapable attach {
! 317: do_test alter2-6.2 {
! 318: forcedelete test2.db-journal
! 319: forcedelete test2.db
! 320: execsql {
! 321: ATTACH 'test2.db' AS aux;
! 322: CREATE TABLE aux.t1(a, b);
! 323: }
! 324: get_file_format test2.db
! 325: } $default_file_format
! 326: }
! 327: do_test alter2-6.3 {
! 328: execsql {
! 329: CREATE TABLE t1(a, b);
! 330: }
! 331: get_file_format
! 332: } {2}
! 333:
! 334: #---------------------------------------------------------------------
! 335: # Test that types and values for columns added with default values
! 336: # other than NULL work with SELECT statements.
! 337: #
! 338: do_test alter2-7.1 {
! 339: execsql {
! 340: DROP TABLE t1;
! 341: CREATE TABLE t1(a);
! 342: INSERT INTO t1 VALUES(1);
! 343: INSERT INTO t1 VALUES(2);
! 344: INSERT INTO t1 VALUES(3);
! 345: INSERT INTO t1 VALUES(4);
! 346: SELECT * FROM t1;
! 347: }
! 348: } {1 2 3 4}
! 349: do_test alter2-7.2 {
! 350: set sql {CREATE TABLE t1(a, b DEFAULT '123', c INTEGER DEFAULT '123')}
! 351: alter_table t1 $sql 3
! 352: execsql {
! 353: SELECT * FROM t1 LIMIT 1;
! 354: }
! 355: } {1 123 123}
! 356: do_test alter2-7.3 {
! 357: execsql {
! 358: SELECT a, typeof(a), b, typeof(b), c, typeof(c) FROM t1 LIMIT 1;
! 359: }
! 360: } {1 integer 123 text 123 integer}
! 361: do_test alter2-7.4 {
! 362: execsql {
! 363: SELECT a, typeof(a), b, typeof(b), c, typeof(c) FROM t1 LIMIT 1;
! 364: }
! 365: } {1 integer 123 text 123 integer}
! 366: do_test alter2-7.5 {
! 367: set sql {CREATE TABLE t1(a, b DEFAULT -123.0, c VARCHAR(10) default 5)}
! 368: alter_table t1 $sql 3
! 369: execsql {
! 370: SELECT a, typeof(a), b, typeof(b), c, typeof(c) FROM t1 LIMIT 1;
! 371: }
! 372: } {1 integer -123 integer 5 text}
! 373:
! 374: #-----------------------------------------------------------------------
! 375: # Test that UPDATE trigger tables work with default values, and that when
! 376: # a row is updated the default values are correctly transfered to the
! 377: # new row.
! 378: #
! 379: ifcapable trigger {
! 380: db function set_val {set ::val}
! 381: do_test alter2-8.1 {
! 382: execsql {
! 383: CREATE TRIGGER trig1 BEFORE UPDATE ON t1 BEGIN
! 384: SELECT set_val(
! 385: old.b||' '||typeof(old.b)||' '||old.c||' '||typeof(old.c)||' '||
! 386: new.b||' '||typeof(new.b)||' '||new.c||' '||typeof(new.c)
! 387: );
! 388: END;
! 389: }
! 390: list
! 391: } {}
! 392: }
! 393: do_test alter2-8.2 {
! 394: execsql {
! 395: UPDATE t1 SET c = 10 WHERE a = 1;
! 396: SELECT a, typeof(a), b, typeof(b), c, typeof(c) FROM t1 LIMIT 1;
! 397: }
! 398: } {1 integer -123 integer 10 text}
! 399: ifcapable trigger {
! 400: do_test alter2-8.3 {
! 401: set ::val
! 402: } {-123 integer 5 text -123 integer 10 text}
! 403: }
! 404:
! 405: #-----------------------------------------------------------------------
! 406: # Test that DELETE trigger tables work with default values, and that when
! 407: # a row is updated the default values are correctly transfered to the
! 408: # new row.
! 409: #
! 410: ifcapable trigger {
! 411: do_test alter2-9.1 {
! 412: execsql {
! 413: CREATE TRIGGER trig2 BEFORE DELETE ON t1 BEGIN
! 414: SELECT set_val(
! 415: old.b||' '||typeof(old.b)||' '||old.c||' '||typeof(old.c)
! 416: );
! 417: END;
! 418: }
! 419: list
! 420: } {}
! 421: do_test alter2-9.2 {
! 422: execsql {
! 423: DELETE FROM t1 WHERE a = 2;
! 424: }
! 425: set ::val
! 426: } {-123 integer 5 text}
! 427: }
! 428:
! 429: #-----------------------------------------------------------------------
! 430: # Test creating an index on a column added with a default value.
! 431: #
! 432: ifcapable bloblit {
! 433: do_test alter2-10.1 {
! 434: execsql {
! 435: CREATE TABLE t2(a);
! 436: INSERT INTO t2 VALUES('a');
! 437: INSERT INTO t2 VALUES('b');
! 438: INSERT INTO t2 VALUES('c');
! 439: INSERT INTO t2 VALUES('d');
! 440: }
! 441: alter_table t2 {CREATE TABLE t2(a, b DEFAULT X'ABCD', c DEFAULT NULL);} 3
! 442: catchsql {
! 443: SELECT * FROM sqlite_master;
! 444: }
! 445: execsql {
! 446: SELECT quote(a), quote(b), quote(c) FROM t2 LIMIT 1;
! 447: }
! 448: } {'a' X'ABCD' NULL}
! 449: do_test alter2-10.2 {
! 450: execsql {
! 451: CREATE INDEX i1 ON t2(b);
! 452: SELECT a FROM t2 WHERE b = X'ABCD';
! 453: }
! 454: } {a b c d}
! 455: do_test alter2-10.3 {
! 456: execsql {
! 457: DELETE FROM t2 WHERE a = 'c';
! 458: SELECT a FROM t2 WHERE b = X'ABCD';
! 459: }
! 460: } {a b d}
! 461: do_test alter2-10.4 {
! 462: execsql {
! 463: SELECT count(b) FROM t2 WHERE b = X'ABCD';
! 464: }
! 465: } {3}
! 466: }
! 467:
! 468: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to alter2.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