AryaWu/sqlite
0
1# 2005 February 182#3# The author disclaims copyright to this source code. In place of4# 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. The12# focus of this script is testing that SQLite can handle a subtle 13# file format change that may be used in the future to implement14# "ALTER TABLE ... ADD COLUMN".15#16# $Id: alter2.test,v 1.14 2009/04/07 14:14:22 danielk1977 Exp $17#18 19set testdir [file dirname $argv0]20source $testdir/tester.tcl21 22# We have to have pragmas in order to do this test23ifcapable {!pragma} return24 25# Do not use a codec for tests in this file, as the database file is26# manipulated directly using tcl scripts. See proc [set_file_format].27#28do_not_use_codec29 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. As33# of version 3.1.3, the record may contain up to N fields. In this case34# 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 table38# 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# 44proc 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 schemacookie48 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# 54proc 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 the59# sqlite_master table of file 'test.db' to $sql. Also set the file format60# to the supplied value. This is 2 if the added column has a default that is61# NULL, or 3 otherwise. 62#63proc alter_table {tbl sql {file_format 2}} {64 sqlite3 dbat test.db65 set s [string map {' ''} $sql]66 set t [string map {' ''} $tbl]67 sqlite3_db_config dbat DEFENSIVE 068 dbat eval [subst {69 PRAGMA writable_schema = 1;70 UPDATE sqlite_master SET sql = '$s' WHERE name = '$t' AND type = 'table';71 PRAGMA writable_schema = 0;72 }]73 dbat close74 set_file_format 275}76 77# Create bogus application-defined functions for functions used 78# internally by ALTER TABLE, to ensure that ALTER TABLE falls back79# to the built-in functions.80#81proc failing_app_func {args} {error "bad function"}82do_test alter2-1.0 {83 db func substr failing_app_func84 db func like failing_app_func85 db func sqlite_rename_table failing_app_func86 db func sqlite_rename_trigger failing_app_func87 db func sqlite_rename_parent failing_app_func88 catchsql {SELECT substr('abcdefg',1,3)}89} {1 {bad function}}90 91 92#-----------------------------------------------------------------------93# Some basic tests to make sure short rows are handled.94#95sqlite3_db_config db DEFENSIVE 096do_test alter2-1.1 {97 execsql {98 CREATE TABLE abc(a, b);99 INSERT INTO abc VALUES(1, 2);100 INSERT INTO abc VALUES(3, 4);101 INSERT INTO abc VALUES(5, 6);102 }103} {}104do_test alter2-1.2 {105 # ALTER TABLE abc ADD COLUMN c;106 alter_table abc {CREATE TABLE abc(a, b, c);}107} {}108do_test alter2-1.3 {109 execsql {110 SELECT * FROM abc;111 }112} {1 2 {} 3 4 {} 5 6 {}}113do_test alter2-1.4 {114 execsql {115 UPDATE abc SET c = 10 WHERE a = 1;116 SELECT * FROM abc;117 }118} {1 2 10 3 4 {} 5 6 {}}119do_test alter2-1.5 {120 execsql {121 CREATE INDEX abc_i ON abc(c);122 }123} {}124do_test alter2-1.6 {125 execsql {126 SELECT c FROM abc ORDER BY c;127 }128} {{} {} 10}129do_test alter2-1.7 {130 execsql {131 SELECT * FROM abc WHERE c = 10;132 }133} {1 2 10}134do_test alter2-1.8 {135 execsql {136 SELECT sum(a), c FROM abc GROUP BY c;137 }138} {8 {} 1 10}139do_test alter2-1.9 {140 # ALTER TABLE abc ADD COLUMN d;141 alter_table abc {CREATE TABLE abc(a, b, c, d);}142 if {[permutation] == "prepare"} { db cache flush }143 execsql { SELECT * FROM abc; }144 execsql {145 UPDATE abc SET d = 11 WHERE c IS NULL AND a<4;146 SELECT * FROM abc;147 }148} {1 2 10 {} 3 4 {} 11 5 6 {} {}}149do_test alter2-1.10 {150 execsql {151 SELECT typeof(d) FROM abc;152 }153} {null integer null}154do_test alter2-1.99 {155 execsql {156 DROP TABLE abc;157 }158} {}159 160#-----------------------------------------------------------------------161# Test that views work when the underlying table structure is changed.162#163ifcapable view {164 do_test alter2-2.1 {165 execsql {166 CREATE TABLE abc2(a, b, c);167 INSERT INTO abc2 VALUES(1, 2, 10);168 INSERT INTO abc2 VALUES(3, 4, NULL);169 INSERT INTO abc2 VALUES(5, 6, NULL);170 CREATE VIEW abc2_v AS SELECT * FROM abc2;171 SELECT * FROM abc2_v;172 }173 } {1 2 10 3 4 {} 5 6 {}}174 do_test alter2-2.2 {175 # ALTER TABLE abc ADD COLUMN d;176 alter_table abc2 {CREATE TABLE abc2(a, b, c, d);}177 execsql {178 SELECT * FROM abc2_v;179 }180 } {1 2 10 {} 3 4 {} {} 5 6 {} {}}181 do_test alter2-2.3 {182 execsql {183 DROP TABLE abc2;184 DROP VIEW abc2_v;185 }186 } {}187}188 189#-----------------------------------------------------------------------190# Test that triggers work when a short row is copied to the old.*191# trigger pseudo-table.192#193ifcapable trigger {194 do_test alter2-3.1 {195 execsql {196 CREATE TABLE abc3(a, b);197 CREATE TABLE blog(o, n);198 CREATE TRIGGER abc3_t AFTER UPDATE OF b ON abc3 BEGIN199 INSERT INTO blog VALUES(old.b, new.b);200 END;201 }202 } {}203 do_test alter2-3.2 {204 execsql {205 INSERT INTO abc3 VALUES(1, 4);206 UPDATE abc3 SET b = 2 WHERE b = 4;207 SELECT * FROM blog;208 }209 } {4 2}210 do_test alter2-3.3 {211 execsql {212 INSERT INTO abc3 VALUES(3, 4);213 INSERT INTO abc3 VALUES(5, 6);214 }215 alter_table abc3 {CREATE TABLE abc3(a, b, c);}216 execsql {217 SELECT * FROM abc3;218 }219 } {1 2 {} 3 4 {} 5 6 {}}220 do_test alter2-3.4 {221 execsql {222 UPDATE abc3 SET b = b*2 WHERE a<4;223 SELECT * FROM abc3;224 }225 } {1 4 {} 3 8 {} 5 6 {}}226 do_test alter2-3.5 {227 execsql {228 SELECT * FROM blog;229 }230 } {4 2 2 4 4 8}231 232 do_test alter2-3.6 {233 execsql {234 CREATE TABLE clog(o, n);235 CREATE TRIGGER abc3_t2 AFTER UPDATE OF c ON abc3 BEGIN236 INSERT INTO clog VALUES(old.c, new.c);237 END;238 UPDATE abc3 SET c = a*2;239 SELECT * FROM clog;240 }241 } {{} 2 {} 6 {} 10}242} else {243 execsql { CREATE TABLE abc3(a, b); }244}245 246#---------------------------------------------------------------------247# Check that an error occurs if the database is upgraded to a file248# format that SQLite does not support (in this case 5). Note: The 249# file format is checked each time the schema is read, so changing the250# file format requires incrementing the schema cookie.251#252do_test alter2-4.1 {253 db close254 set_file_format 5255 catch { sqlite3 db test.db }256 set {} {}257} {}258do_test alter2-4.2 {259 # We have to run two queries here because the Tcl interface uses260 # sqlite3_prepare_v2(). In this case, the first query encounters an 261 # SQLITE_SCHEMA error. Then, when trying to recompile the statement, the262 # "unsupported file format" error is encountered. So the error code263 # returned is SQLITE_SCHEMA, not SQLITE_ERROR as required by the following264 # test case.265 #266 # When the query is attempted a second time, the same error message is267 # returned but the error code is SQLITE_ERROR, because the unsupported268 # file format was detected during a call to sqlite3_prepare(), not269 # sqlite3_step().270 #271 catchsql { SELECT * FROM sqlite_master; }272 catchsql { SELECT * FROM sqlite_master; }273} {1 {unsupported file format}}274do_test alter2-4.3 {275 sqlite3_errcode db276} {SQLITE_ERROR}277do_test alter2-4.4 {278 set ::DB [sqlite3_connection_pointer db]279 catchsql {280 SELECT * FROM sqlite_master;281 }282} {1 {unsupported file format}}283do_test alter2-4.5 {284 sqlite3_errcode db285} {SQLITE_ERROR}286 287#---------------------------------------------------------------------288# Check that executing VACUUM on a file with file-format version 2289# resets the file format to 1.290#291set default_file_format [expr $SQLITE_DEFAULT_FILE_FORMAT==4 ? 4 : 1]292ifcapable vacuum {293 do_test alter2-5.1 {294 set_file_format 2295 db close296 sqlite3 db test.db297 execsql {SELECT 1 FROM sqlite_master LIMIT 1;}298 get_file_format299 } {2}300 do_test alter2-5.2 {301 execsql { VACUUM }302 } {}303 do_test alter2-5.3 {304 get_file_format305 } $default_file_format306}307 308#---------------------------------------------------------------------309# Test that when a database with file-format 2 is opened, new 310# databases are still created with file-format 1.311#312do_test alter2-6.1 {313 db close314 set_file_format 2315 sqlite3 db test.db316 get_file_format317} {2}318ifcapable attach {319 do_test alter2-6.2 {320 forcedelete test2.db-journal321 forcedelete test2.db322 execsql {323 ATTACH 'test2.db' AS aux;324 CREATE TABLE aux.t1(a, b);325 }326 get_file_format test2.db327 } $default_file_format328}329do_test alter2-6.3 {330 execsql {331 CREATE TABLE t1(a, b);332 }333 get_file_format 334} {2}335 336#---------------------------------------------------------------------337# Test that types and values for columns added with default values 338# other than NULL work with SELECT statements.339#340do_test alter2-7.1 {341 execsql {342 DROP TABLE t1;343 CREATE TABLE t1(a);344 INSERT INTO t1 VALUES(1);345 INSERT INTO t1 VALUES(2);346 INSERT INTO t1 VALUES(3);347 INSERT INTO t1 VALUES(4);348 SELECT * FROM t1;349 }350} {1 2 3 4}351do_test alter2-7.2 {352 set sql {CREATE TABLE t1(a, b DEFAULT '123', c INTEGER DEFAULT '123')}353 alter_table t1 $sql 3354 execsql {355 SELECT * FROM t1 LIMIT 1;356 }357} {1 123 123}358do_test alter2-7.3 {359 execsql {360 SELECT a, typeof(a), b, typeof(b), c, typeof(c) FROM t1 LIMIT 1;361 }362} {1 integer 123 text 123 integer}363do_test alter2-7.4 {364 execsql {365 SELECT a, typeof(a), b, typeof(b), c, typeof(c) FROM t1 LIMIT 1;366 }367} {1 integer 123 text 123 integer}368do_test alter2-7.5 {369 set sql {CREATE TABLE t1(a, b DEFAULT -123.0, c VARCHAR(10) default 5)}370 alter_table t1 $sql 3371 execsql {372 SELECT a, typeof(a), b, typeof(b), c, typeof(c) FROM t1 LIMIT 1;373 }374} {1 integer -123.0 real 5 text}375 376#-----------------------------------------------------------------------377# Test that UPDATE trigger tables work with default values, and that when378# a row is updated the default values are correctly transfered to the 379# new row.380# 381ifcapable trigger {382db function set_val {set ::val}383 do_test alter2-8.1 {384 execsql {385 CREATE TRIGGER trig1 BEFORE UPDATE ON t1 BEGIN386 SELECT set_val(387 old.b||' '||typeof(old.b)||' '||old.c||' '||typeof(old.c)||' '||388 new.b||' '||typeof(new.b)||' '||new.c||' '||typeof(new.c) 389 );390 END;391 }392 list393 } {}394}395do_test alter2-8.2 {396 execsql {397 UPDATE t1 SET c = 10 WHERE a = 1;398 SELECT a, typeof(a), b, typeof(b), c, typeof(c) FROM t1 LIMIT 1;399 }400} {1 integer -123.0 real 10 text}401ifcapable trigger {402 do_test alter2-8.3 {403 set ::val404 } {-123.0 real 5 text -123.0 real 10 text}405}406 407#-----------------------------------------------------------------------408# Test that DELETE trigger tables work with default values, and that when409# a row is updated the default values are correctly transfered to the 410# new row.411# 412ifcapable trigger {413 do_test alter2-9.1 {414 execsql {415 CREATE TRIGGER trig2 BEFORE DELETE ON t1 BEGIN416 SELECT set_val(417 old.b||' '||typeof(old.b)||' '||old.c||' '||typeof(old.c)418 );419 END;420 }421 list422 } {}423 do_test alter2-9.2 {424 execsql {425 DELETE FROM t1 WHERE a = 2;426 }427 set ::val428 } {-123.0 real 5 text}429}430 431#-----------------------------------------------------------------------432# Test creating an index on a column added with a default value. 433#434ifcapable bloblit {435 do_test alter2-10.1 {436 execsql {437 CREATE TABLE t2(a);438 INSERT INTO t2 VALUES('a');439 INSERT INTO t2 VALUES('b');440 INSERT INTO t2 VALUES('c');441 INSERT INTO t2 VALUES('d');442 }443 alter_table t2 {CREATE TABLE t2(a, b DEFAULT X'ABCD', c DEFAULT NULL);} 3444 catchsql {445 SELECT * FROM sqlite_master;446 }447 execsql {448 SELECT quote(a), quote(b), quote(c) FROM t2 LIMIT 1;449 }450 } {'a' X'ABCD' NULL}451 do_test alter2-10.2 {452 execsql {453 CREATE INDEX i1 ON t2(b);454 SELECT a FROM t2 WHERE b = X'ABCD';455 }456 } {a b c d}457 do_test alter2-10.3 {458 execsql {459 DELETE FROM t2 WHERE a = 'c';460 SELECT a FROM t2 WHERE b = X'ABCD';461 }462 } {a b d}463 do_test alter2-10.4 {464 execsql {465 SELECT count(b) FROM t2 WHERE b = X'ABCD';466 }467 } {3}468}469 470finish_test471