diff options
Diffstat (limited to 'test/alter2.test')
| -rw-r--r-- | test/alter2.test | 468 |
1 files changed, 468 insertions, 0 deletions
diff --git a/test/alter2.test b/test/alter2.test new file mode 100644 index 0000000..14be637 --- /dev/null +++ b/test/alter2.test @@ -0,0 +1,468 @@ +# 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.14 2009/04/07 14:14:22 danielk1977 Exp $ +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl + +# We have to have pragmas in order to do this test +ifcapable {!pragma} return + +# Do not use a codec for tests in this file, as the database file is +# manipulated directly using tcl scripts. See proc [set_file_format]. +# +do_not_use_codec + +# 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 +} + +# Create bogus application-defined functions for functions used +# internally by ALTER TABLE, to ensure that ALTER TABLE falls back +# to the built-in functions. +# +proc failing_app_func {args} {error "bad function"} +do_test alter2-1.0 { + db func substr failing_app_func + db func like failing_app_func + db func sqlite_rename_table failing_app_func + db func sqlite_rename_trigger failing_app_func + db func sqlite_rename_parent failing_app_func + catchsql {SELECT substr('abcdefg',1,3)} +} {1 {bad function}} + + +#----------------------------------------------------------------------- +# 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);} + if {[permutation] == "prepare"} { db cache flush } + 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} +} else { + execsql { CREATE TABLE abc3(a, b); } +} + +#--------------------------------------------------------------------- +# 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 + catch { sqlite3 db test.db } + set {} {} +} {} +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 { + forcedelete test2.db-journal + forcedelete 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 |