gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/alter2.test (about) 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 sqlite3_db_config dbat DEFENSIVE 0 68 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 close 74 set_file_format 2 75 } 76 77 # Create bogus application-defined functions for functions used 78 # internally by ALTER TABLE, to ensure that ALTER TABLE falls back 79 # to the built-in functions. 80 # 81 proc failing_app_func {args} {error "bad function"} 82 do_test alter2-1.0 { 83 db func substr failing_app_func 84 db func like failing_app_func 85 db func sqlite_rename_table failing_app_func 86 db func sqlite_rename_trigger failing_app_func 87 db func sqlite_rename_parent failing_app_func 88 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 # 95 sqlite3_db_config db DEFENSIVE 0 96 do_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 } {} 104 do_test alter2-1.2 { 105 # ALTER TABLE abc ADD COLUMN c; 106 alter_table abc {CREATE TABLE abc(a, b, c);} 107 } {} 108 do_test alter2-1.3 { 109 execsql { 110 SELECT * FROM abc; 111 } 112 } {1 2 {} 3 4 {} 5 6 {}} 113 do_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 {}} 119 do_test alter2-1.5 { 120 execsql { 121 CREATE INDEX abc_i ON abc(c); 122 } 123 } {} 124 do_test alter2-1.6 { 125 execsql { 126 SELECT c FROM abc ORDER BY c; 127 } 128 } {{} {} 10} 129 do_test alter2-1.7 { 130 execsql { 131 SELECT * FROM abc WHERE c = 10; 132 } 133 } {1 2 10} 134 do_test alter2-1.8 { 135 execsql { 136 SELECT sum(a), c FROM abc GROUP BY c; 137 } 138 } {8 {} 1 10} 139 do_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 {} {}} 149 do_test alter2-1.10 { 150 execsql { 151 SELECT typeof(d) FROM abc; 152 } 153 } {null integer null} 154 do_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 # 163 ifcapable 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 # 193 ifcapable 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 BEGIN 199 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 BEGIN 236 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 file 248 # 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 the 250 # file format requires incrementing the schema cookie. 251 # 252 do_test alter2-4.1 { 253 db close 254 set_file_format 5 255 catch { sqlite3 db test.db } 256 set {} {} 257 } {} 258 do_test alter2-4.2 { 259 # We have to run two queries here because the Tcl interface uses 260 # sqlite3_prepare_v2(). In this case, the first query encounters an 261 # SQLITE_SCHEMA error. Then, when trying to recompile the statement, the 262 # "unsupported file format" error is encountered. So the error code 263 # returned is SQLITE_SCHEMA, not SQLITE_ERROR as required by the following 264 # test case. 265 # 266 # When the query is attempted a second time, the same error message is 267 # returned but the error code is SQLITE_ERROR, because the unsupported 268 # file format was detected during a call to sqlite3_prepare(), not 269 # sqlite3_step(). 270 # 271 catchsql { SELECT * FROM sqlite_master; } 272 catchsql { SELECT * FROM sqlite_master; } 273 } {1 {unsupported file format}} 274 do_test alter2-4.3 { 275 sqlite3_errcode db 276 } {SQLITE_ERROR} 277 do_test alter2-4.4 { 278 set ::DB [sqlite3_connection_pointer db] 279 catchsql { 280 SELECT * FROM sqlite_master; 281 } 282 } {1 {unsupported file format}} 283 do_test alter2-4.5 { 284 sqlite3_errcode db 285 } {SQLITE_ERROR} 286 287 #--------------------------------------------------------------------- 288 # Check that executing VACUUM on a file with file-format version 2 289 # resets the file format to 1. 290 # 291 set default_file_format [expr $SQLITE_DEFAULT_FILE_FORMAT==4 ? 4 : 1] 292 ifcapable vacuum { 293 do_test alter2-5.1 { 294 set_file_format 2 295 db close 296 sqlite3 db test.db 297 execsql {SELECT 1 FROM sqlite_master LIMIT 1;} 298 get_file_format 299 } {2} 300 do_test alter2-5.2 { 301 execsql { VACUUM } 302 } {} 303 do_test alter2-5.3 { 304 get_file_format 305 } $default_file_format 306 } 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 # 312 do_test alter2-6.1 { 313 db close 314 set_file_format 2 315 sqlite3 db test.db 316 get_file_format 317 } {2} 318 ifcapable attach { 319 do_test alter2-6.2 { 320 forcedelete test2.db-journal 321 forcedelete test2.db 322 execsql { 323 ATTACH 'test2.db' AS aux; 324 CREATE TABLE aux.t1(a, b); 325 } 326 get_file_format test2.db 327 } $default_file_format 328 } 329 do_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 # 340 do_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} 351 do_test alter2-7.2 { 352 set sql {CREATE TABLE t1(a, b DEFAULT '123', c INTEGER DEFAULT '123')} 353 alter_table t1 $sql 3 354 execsql { 355 SELECT * FROM t1 LIMIT 1; 356 } 357 } {1 123 123} 358 do_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} 363 do_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} 368 do_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 3 371 execsql { 372 SELECT a, typeof(a), b, typeof(b), c, typeof(c) FROM t1 LIMIT 1; 373 } 374 } {1 integer -123 integer 5 text} 375 376 #----------------------------------------------------------------------- 377 # Test that UPDATE trigger tables work with default values, and that when 378 # a row is updated the default values are correctly transfered to the 379 # new row. 380 # 381 ifcapable trigger { 382 db function set_val {set ::val} 383 do_test alter2-8.1 { 384 execsql { 385 CREATE TRIGGER trig1 BEFORE UPDATE ON t1 BEGIN 386 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 list 393 } {} 394 } 395 do_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 integer 10 text} 401 ifcapable trigger { 402 do_test alter2-8.3 { 403 set ::val 404 } {-123 integer 5 text -123 integer 10 text} 405 } 406 407 #----------------------------------------------------------------------- 408 # Test that DELETE trigger tables work with default values, and that when 409 # a row is updated the default values are correctly transfered to the 410 # new row. 411 # 412 ifcapable trigger { 413 do_test alter2-9.1 { 414 execsql { 415 CREATE TRIGGER trig2 BEFORE DELETE ON t1 BEGIN 416 SELECT set_val( 417 old.b||' '||typeof(old.b)||' '||old.c||' '||typeof(old.c) 418 ); 419 END; 420 } 421 list 422 } {} 423 do_test alter2-9.2 { 424 execsql { 425 DELETE FROM t1 WHERE a = 2; 426 } 427 set ::val 428 } {-123 integer 5 text} 429 } 430 431 #----------------------------------------------------------------------- 432 # Test creating an index on a column added with a default value. 433 # 434 ifcapable 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);} 3 444 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 470 finish_test