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