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# 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# 28do_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# 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 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# 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 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# 63proc 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# 80proc failing_app_func {args} {error "bad function"} 81do_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# 94do_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} {} 102do_test alter2-1.2 { 103 # ALTER TABLE abc ADD COLUMN c; 104 alter_table abc {CREATE TABLE abc(a, b, c);} 105} {} 106do_test alter2-1.3 { 107 execsql { 108 SELECT * FROM abc; 109 } 110} {1 2 {} 3 4 {} 5 6 {}} 111do_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 {}} 117do_test alter2-1.5 { 118 execsql { 119 CREATE INDEX abc_i ON abc(c); 120 } 121} {} 122do_test alter2-1.6 { 123 execsql { 124 SELECT c FROM abc ORDER BY c; 125 } 126} {{} {} 10} 127do_test alter2-1.7 { 128 execsql { 129 SELECT * FROM abc WHERE c = 10; 130 } 131} {1 2 10} 132do_test alter2-1.8 { 133 execsql { 134 SELECT sum(a), c FROM abc GROUP BY c; 135 } 136} {8 {} 1 10} 137do_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 {} {}} 147do_test alter2-1.10 { 148 execsql { 149 SELECT typeof(d) FROM abc; 150 } 151} {null integer null} 152do_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# 161ifcapable 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# 191ifcapable 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# 250do_test alter2-4.1 { 251 db close 252 set_file_format 5 253 catch { sqlite3 db test.db } 254 set {} {} 255} {} 256do_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}} 272do_test alter2-4.3 { 273 sqlite3_errcode db 274} {SQLITE_ERROR} 275do_test alter2-4.4 { 276 set ::DB [sqlite3_connection_pointer db] 277 catchsql { 278 SELECT * FROM sqlite_master; 279 } 280} {1 {unsupported file format}} 281do_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# 289set default_file_format [expr $SQLITE_DEFAULT_FILE_FORMAT==4 ? 4 : 1] 290ifcapable 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# 310do_test alter2-6.1 { 311 db close 312 set_file_format 2 313 sqlite3 db test.db 314 get_file_format 315} {2} 316ifcapable 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} 327do_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# 338do_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} 349do_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} 356do_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} 361do_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} 366do_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# 379ifcapable trigger { 380db 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} 393do_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} 399ifcapable 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# 410ifcapable 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# 432ifcapable 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 468finish_test 469