# 2005 February 18 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #************************************************************************* # This file implements regression tests for SQLite library. The # focus of this script is testing that SQLite can handle a subtle # file format change that may be used in the future to implement # "ALTER TABLE ... ADD COLUMN". # # $Id: alter2.test,v 1.13 2008/03/19 00:21:31 drh Exp $ # set testdir [file dirname $argv0] source $testdir/tester.tcl # We have to have pragmas in order to do this test ifcapable {!pragma} return # These tests do not work if there is a codec. # #if {[catch {sqlite3 -has_codec} r] || $r} return # The file format change affects the way row-records stored in tables (but # not indices) are interpreted. Before version 3.1.3, a row-record for a # table with N columns was guaranteed to contain exactly N fields. As # of version 3.1.3, the record may contain up to N fields. In this case # the M fields that are present are the values for the left-most M # columns. The (N-M) rightmost columns contain NULL. # # If any records in the database contain less fields than their table # has columns, then the file-format meta value should be set to (at least) 2. # # This procedure sets the value of the file-format in file 'test.db' # to $newval. Also, the schema cookie is incremented. # proc set_file_format {newval} { hexio_write test.db 44 [hexio_render_int32 $newval] set schemacookie [hexio_get_int [hexio_read test.db 40 4]] incr schemacookie hexio_write test.db 40 [hexio_render_int32 $schemacookie] return {} } # This procedure returns the value of the file-format in file 'test.db'. # proc get_file_format {{fname test.db}} { return [hexio_get_int [hexio_read $fname 44 4]] } # This procedure sets the SQL statement stored for table $tbl in the # sqlite_master table of file 'test.db' to $sql. Also set the file format # to the supplied value. This is 2 if the added column has a default that is # NULL, or 3 otherwise. # proc alter_table {tbl sql {file_format 2}} { sqlite3 dbat test.db set s [string map {' ''} $sql] set t [string map {' ''} $tbl] dbat eval [subst { PRAGMA writable_schema = 1; UPDATE sqlite_master SET sql = '$s' WHERE name = '$t' AND type = 'table'; PRAGMA writable_schema = 0; }] dbat close set_file_format 2 } #----------------------------------------------------------------------- # Some basic tests to make sure short rows are handled. # do_test alter2-1.1 { execsql { CREATE TABLE abc(a, b); INSERT INTO abc VALUES(1, 2); INSERT INTO abc VALUES(3, 4); INSERT INTO abc VALUES(5, 6); } } {} do_test alter2-1.2 { # ALTER TABLE abc ADD COLUMN c; alter_table abc {CREATE TABLE abc(a, b, c);} } {} do_test alter2-1.3 { execsql { SELECT * FROM abc; } } {1 2 {} 3 4 {} 5 6 {}} do_test alter2-1.4 { execsql { UPDATE abc SET c = 10 WHERE a = 1; SELECT * FROM abc; } } {1 2 10 3 4 {} 5 6 {}} do_test alter2-1.5 { execsql { CREATE INDEX abc_i ON abc(c); } } {} do_test alter2-1.6 { execsql { SELECT c FROM abc ORDER BY c; } } {{} {} 10} do_test alter2-1.7 { execsql { SELECT * FROM abc WHERE c = 10; } } {1 2 10} do_test alter2-1.8 { execsql { SELECT sum(a), c FROM abc GROUP BY c; } } {8 {} 1 10} do_test alter2-1.9 { # ALTER TABLE abc ADD COLUMN d; alter_table abc {CREATE TABLE abc(a, b, c, d);} execsql { SELECT * FROM abc; } execsql { UPDATE abc SET d = 11 WHERE c IS NULL AND a<4; SELECT * FROM abc; } } {1 2 10 {} 3 4 {} 11 5 6 {} {}} do_test alter2-1.10 { execsql { SELECT typeof(d) FROM abc; } } {null integer null} do_test alter2-1.99 { execsql { DROP TABLE abc; } } {} #----------------------------------------------------------------------- # Test that views work when the underlying table structure is changed. # ifcapable view { do_test alter2-2.1 { execsql { CREATE TABLE abc2(a, b, c); INSERT INTO abc2 VALUES(1, 2, 10); INSERT INTO abc2 VALUES(3, 4, NULL); INSERT INTO abc2 VALUES(5, 6, NULL); CREATE VIEW abc2_v AS SELECT * FROM abc2; SELECT * FROM abc2_v; } } {1 2 10 3 4 {} 5 6 {}} do_test alter2-2.2 { # ALTER TABLE abc ADD COLUMN d; alter_table abc2 {CREATE TABLE abc2(a, b, c, d);} execsql { SELECT * FROM abc2_v; } } {1 2 10 {} 3 4 {} {} 5 6 {} {}} do_test alter2-2.3 { execsql { DROP TABLE abc2; DROP VIEW abc2_v; } } {} } #----------------------------------------------------------------------- # Test that triggers work when a short row is copied to the old.* # trigger pseudo-table. # ifcapable trigger { do_test alter2-3.1 { execsql { CREATE TABLE abc3(a, b); CREATE TABLE blog(o, n); CREATE TRIGGER abc3_t AFTER UPDATE OF b ON abc3 BEGIN INSERT INTO blog VALUES(old.b, new.b); END; } } {} do_test alter2-3.2 { execsql { INSERT INTO abc3 VALUES(1, 4); UPDATE abc3 SET b = 2 WHERE b = 4; SELECT * FROM blog; } } {4 2} do_test alter2-3.3 { execsql { INSERT INTO abc3 VALUES(3, 4); INSERT INTO abc3 VALUES(5, 6); } alter_table abc3 {CREATE TABLE abc3(a, b, c);} execsql { SELECT * FROM abc3; } } {1 2 {} 3 4 {} 5 6 {}} do_test alter2-3.4 { execsql { UPDATE abc3 SET b = b*2 WHERE a<4; SELECT * FROM abc3; } } {1 4 {} 3 8 {} 5 6 {}} do_test alter2-3.5 { execsql { SELECT * FROM blog; } } {4 2 2 4 4 8} do_test alter2-3.6 { execsql { CREATE TABLE clog(o, n); CREATE TRIGGER abc3_t2 AFTER UPDATE OF c ON abc3 BEGIN INSERT INTO clog VALUES(old.c, new.c); END; UPDATE abc3 SET c = a*2; SELECT * FROM clog; } } {{} 2 {} 6 {} 10} } #--------------------------------------------------------------------- # Check that an error occurs if the database is upgraded to a file # format that SQLite does not support (in this case 5). Note: The # file format is checked each time the schema is read, so changing the # file format requires incrementing the schema cookie. # do_test alter2-4.1 { db close set_file_format 5 sqlite3 db test.db } {} do_test alter2-4.2 { # We have to run two queries here because the Tcl interface uses # sqlite3_prepare_v2(). In this case, the first query encounters an # SQLITE_SCHEMA error. Then, when trying to recompile the statement, the # "unsupported file format" error is encountered. So the error code # returned is SQLITE_SCHEMA, not SQLITE_ERROR as required by the following # test case. # # When the query is attempted a second time, the same error message is # returned but the error code is SQLITE_ERROR, because the unsupported # file format was detected during a call to sqlite3_prepare(), not # sqlite3_step(). # catchsql { SELECT * FROM sqlite_master; } catchsql { SELECT * FROM sqlite_master; } } {1 {unsupported file format}} do_test alter2-4.3 { sqlite3_errcode db } {SQLITE_ERROR} do_test alter2-4.4 { set ::DB [sqlite3_connection_pointer db] catchsql { SELECT * FROM sqlite_master; } } {1 {unsupported file format}} do_test alter2-4.5 { sqlite3_errcode db } {SQLITE_ERROR} #--------------------------------------------------------------------- # Check that executing VACUUM on a file with file-format version 2 # resets the file format to 1. # set default_file_format [expr $SQLITE_DEFAULT_FILE_FORMAT==4 ? 4 : 1] ifcapable vacuum { do_test alter2-5.1 { set_file_format 2 db close sqlite3 db test.db execsql {SELECT 1 FROM sqlite_master LIMIT 1;} get_file_format } {2} do_test alter2-5.2 { execsql { VACUUM; } } {} do_test alter2-5.3 { get_file_format } $default_file_format } #--------------------------------------------------------------------- # Test that when a database with file-format 2 is opened, new # databases are still created with file-format 1. # do_test alter2-6.1 { db close set_file_format 2 sqlite3 db test.db get_file_format } {2} ifcapable attach { do_test alter2-6.2 { file delete -force test2.db-journal file delete -force test2.db execsql { ATTACH 'test2.db' AS aux; CREATE TABLE aux.t1(a, b); } get_file_format test2.db } $default_file_format } do_test alter2-6.3 { execsql { CREATE TABLE t1(a, b); } get_file_format } {2} #--------------------------------------------------------------------- # Test that types and values for columns added with default values # other than NULL work with SELECT statements. # do_test alter2-7.1 { execsql { DROP TABLE t1; CREATE TABLE t1(a); INSERT INTO t1 VALUES(1); INSERT INTO t1 VALUES(2); INSERT INTO t1 VALUES(3); INSERT INTO t1 VALUES(4); SELECT * FROM t1; } } {1 2 3 4} do_test alter2-7.2 { set sql {CREATE TABLE t1(a, b DEFAULT '123', c INTEGER DEFAULT '123')} alter_table t1 $sql 3 execsql { SELECT * FROM t1 LIMIT 1; } } {1 123 123} do_test alter2-7.3 { execsql { SELECT a, typeof(a), b, typeof(b), c, typeof(c) FROM t1 LIMIT 1; } } {1 integer 123 text 123 integer} do_test alter2-7.4 { execsql { SELECT a, typeof(a), b, typeof(b), c, typeof(c) FROM t1 LIMIT 1; } } {1 integer 123 text 123 integer} do_test alter2-7.5 { set sql {CREATE TABLE t1(a, b DEFAULT -123.0, c VARCHAR(10) default 5)} alter_table t1 $sql 3 execsql { SELECT a, typeof(a), b, typeof(b), c, typeof(c) FROM t1 LIMIT 1; } } {1 integer -123 integer 5 text} #----------------------------------------------------------------------- # Test that UPDATE trigger tables work with default values, and that when # a row is updated the default values are correctly transfered to the # new row. # ifcapable trigger { db function set_val {set ::val} do_test alter2-8.1 { execsql { CREATE TRIGGER trig1 BEFORE UPDATE ON t1 BEGIN SELECT set_val( old.b||' '||typeof(old.b)||' '||old.c||' '||typeof(old.c)||' '|| new.b||' '||typeof(new.b)||' '||new.c||' '||typeof(new.c) ); END; } list } {} } do_test alter2-8.2 { execsql { UPDATE t1 SET c = 10 WHERE a = 1; SELECT a, typeof(a), b, typeof(b), c, typeof(c) FROM t1 LIMIT 1; } } {1 integer -123 integer 10 text} ifcapable trigger { do_test alter2-8.3 { set ::val } {-123 integer 5 text -123 integer 10 text} } #----------------------------------------------------------------------- # Test that DELETE trigger tables work with default values, and that when # a row is updated the default values are correctly transfered to the # new row. # ifcapable trigger { do_test alter2-9.1 { execsql { CREATE TRIGGER trig2 BEFORE DELETE ON t1 BEGIN SELECT set_val( old.b||' '||typeof(old.b)||' '||old.c||' '||typeof(old.c) ); END; } list } {} do_test alter2-9.2 { execsql { DELETE FROM t1 WHERE a = 2; } set ::val } {-123 integer 5 text} } #----------------------------------------------------------------------- # Test creating an index on a column added with a default value. # ifcapable bloblit { do_test alter2-10.1 { execsql { CREATE TABLE t2(a); INSERT INTO t2 VALUES('a'); INSERT INTO t2 VALUES('b'); INSERT INTO t2 VALUES('c'); INSERT INTO t2 VALUES('d'); } alter_table t2 {CREATE TABLE t2(a, b DEFAULT X'ABCD', c DEFAULT NULL);} 3 catchsql { SELECT * FROM sqlite_master; } execsql { SELECT quote(a), quote(b), quote(c) FROM t2 LIMIT 1; } } {'a' X'ABCD' NULL} do_test alter2-10.2 { execsql { CREATE INDEX i1 ON t2(b); SELECT a FROM t2 WHERE b = X'ABCD'; } } {a b c d} do_test alter2-10.3 { execsql { DELETE FROM t2 WHERE a = 'c'; SELECT a FROM t2 WHERE b = X'ABCD'; } } {a b d} do_test alter2-10.4 { execsql { SELECT count(b) FROM t2 WHERE b = X'ABCD'; } } {3} } finish_test