1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/alter2.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,450 @@
1.4 +# 2005 February 18
1.5 +#
1.6 +# The author disclaims copyright to this source code. In place of
1.7 +# a legal notice, here is a blessing:
1.8 +#
1.9 +# May you do good and not evil.
1.10 +# May you find forgiveness for yourself and forgive others.
1.11 +# May you share freely, never taking more than you give.
1.12 +#
1.13 +#*************************************************************************
1.14 +# This file implements regression tests for SQLite library. The
1.15 +# focus of this script is testing that SQLite can handle a subtle
1.16 +# file format change that may be used in the future to implement
1.17 +# "ALTER TABLE ... ADD COLUMN".
1.18 +#
1.19 +# $Id: alter2.test,v 1.13 2008/03/19 00:21:31 drh Exp $
1.20 +#
1.21 +
1.22 +set testdir [file dirname $argv0]
1.23 +source $testdir/tester.tcl
1.24 +
1.25 +# We have to have pragmas in order to do this test
1.26 +ifcapable {!pragma} return
1.27 +
1.28 +# These tests do not work if there is a codec.
1.29 +#
1.30 +#if {[catch {sqlite3 -has_codec} r] || $r} return
1.31 +
1.32 +# The file format change affects the way row-records stored in tables (but
1.33 +# not indices) are interpreted. Before version 3.1.3, a row-record for a
1.34 +# table with N columns was guaranteed to contain exactly N fields. As
1.35 +# of version 3.1.3, the record may contain up to N fields. In this case
1.36 +# the M fields that are present are the values for the left-most M
1.37 +# columns. The (N-M) rightmost columns contain NULL.
1.38 +#
1.39 +# If any records in the database contain less fields than their table
1.40 +# has columns, then the file-format meta value should be set to (at least) 2.
1.41 +#
1.42 +
1.43 +# This procedure sets the value of the file-format in file 'test.db'
1.44 +# to $newval. Also, the schema cookie is incremented.
1.45 +#
1.46 +proc set_file_format {newval} {
1.47 + hexio_write test.db 44 [hexio_render_int32 $newval]
1.48 + set schemacookie [hexio_get_int [hexio_read test.db 40 4]]
1.49 + incr schemacookie
1.50 + hexio_write test.db 40 [hexio_render_int32 $schemacookie]
1.51 + return {}
1.52 +}
1.53 +
1.54 +# This procedure returns the value of the file-format in file 'test.db'.
1.55 +#
1.56 +proc get_file_format {{fname test.db}} {
1.57 + return [hexio_get_int [hexio_read $fname 44 4]]
1.58 +}
1.59 +
1.60 +# This procedure sets the SQL statement stored for table $tbl in the
1.61 +# sqlite_master table of file 'test.db' to $sql. Also set the file format
1.62 +# to the supplied value. This is 2 if the added column has a default that is
1.63 +# NULL, or 3 otherwise.
1.64 +#
1.65 +proc alter_table {tbl sql {file_format 2}} {
1.66 + sqlite3 dbat test.db
1.67 + set s [string map {' ''} $sql]
1.68 + set t [string map {' ''} $tbl]
1.69 + dbat eval [subst {
1.70 + PRAGMA writable_schema = 1;
1.71 + UPDATE sqlite_master SET sql = '$s' WHERE name = '$t' AND type = 'table';
1.72 + PRAGMA writable_schema = 0;
1.73 + }]
1.74 + dbat close
1.75 + set_file_format 2
1.76 +}
1.77 +
1.78 +#-----------------------------------------------------------------------
1.79 +# Some basic tests to make sure short rows are handled.
1.80 +#
1.81 +do_test alter2-1.1 {
1.82 + execsql {
1.83 + CREATE TABLE abc(a, b);
1.84 + INSERT INTO abc VALUES(1, 2);
1.85 + INSERT INTO abc VALUES(3, 4);
1.86 + INSERT INTO abc VALUES(5, 6);
1.87 + }
1.88 +} {}
1.89 +do_test alter2-1.2 {
1.90 + # ALTER TABLE abc ADD COLUMN c;
1.91 + alter_table abc {CREATE TABLE abc(a, b, c);}
1.92 +} {}
1.93 +do_test alter2-1.3 {
1.94 + execsql {
1.95 + SELECT * FROM abc;
1.96 + }
1.97 +} {1 2 {} 3 4 {} 5 6 {}}
1.98 +do_test alter2-1.4 {
1.99 + execsql {
1.100 + UPDATE abc SET c = 10 WHERE a = 1;
1.101 + SELECT * FROM abc;
1.102 + }
1.103 +} {1 2 10 3 4 {} 5 6 {}}
1.104 +do_test alter2-1.5 {
1.105 + execsql {
1.106 + CREATE INDEX abc_i ON abc(c);
1.107 + }
1.108 +} {}
1.109 +do_test alter2-1.6 {
1.110 + execsql {
1.111 + SELECT c FROM abc ORDER BY c;
1.112 + }
1.113 +} {{} {} 10}
1.114 +do_test alter2-1.7 {
1.115 + execsql {
1.116 + SELECT * FROM abc WHERE c = 10;
1.117 + }
1.118 +} {1 2 10}
1.119 +do_test alter2-1.8 {
1.120 + execsql {
1.121 + SELECT sum(a), c FROM abc GROUP BY c;
1.122 + }
1.123 +} {8 {} 1 10}
1.124 +do_test alter2-1.9 {
1.125 + # ALTER TABLE abc ADD COLUMN d;
1.126 + alter_table abc {CREATE TABLE abc(a, b, c, d);}
1.127 + execsql { SELECT * FROM abc; }
1.128 + execsql {
1.129 + UPDATE abc SET d = 11 WHERE c IS NULL AND a<4;
1.130 + SELECT * FROM abc;
1.131 + }
1.132 +} {1 2 10 {} 3 4 {} 11 5 6 {} {}}
1.133 +do_test alter2-1.10 {
1.134 + execsql {
1.135 + SELECT typeof(d) FROM abc;
1.136 + }
1.137 +} {null integer null}
1.138 +do_test alter2-1.99 {
1.139 + execsql {
1.140 + DROP TABLE abc;
1.141 + }
1.142 +} {}
1.143 +
1.144 +#-----------------------------------------------------------------------
1.145 +# Test that views work when the underlying table structure is changed.
1.146 +#
1.147 +ifcapable view {
1.148 + do_test alter2-2.1 {
1.149 + execsql {
1.150 + CREATE TABLE abc2(a, b, c);
1.151 + INSERT INTO abc2 VALUES(1, 2, 10);
1.152 + INSERT INTO abc2 VALUES(3, 4, NULL);
1.153 + INSERT INTO abc2 VALUES(5, 6, NULL);
1.154 + CREATE VIEW abc2_v AS SELECT * FROM abc2;
1.155 + SELECT * FROM abc2_v;
1.156 + }
1.157 + } {1 2 10 3 4 {} 5 6 {}}
1.158 + do_test alter2-2.2 {
1.159 + # ALTER TABLE abc ADD COLUMN d;
1.160 + alter_table abc2 {CREATE TABLE abc2(a, b, c, d);}
1.161 + execsql {
1.162 + SELECT * FROM abc2_v;
1.163 + }
1.164 + } {1 2 10 {} 3 4 {} {} 5 6 {} {}}
1.165 + do_test alter2-2.3 {
1.166 + execsql {
1.167 + DROP TABLE abc2;
1.168 + DROP VIEW abc2_v;
1.169 + }
1.170 + } {}
1.171 +}
1.172 +
1.173 +#-----------------------------------------------------------------------
1.174 +# Test that triggers work when a short row is copied to the old.*
1.175 +# trigger pseudo-table.
1.176 +#
1.177 +ifcapable trigger {
1.178 + do_test alter2-3.1 {
1.179 + execsql {
1.180 + CREATE TABLE abc3(a, b);
1.181 + CREATE TABLE blog(o, n);
1.182 + CREATE TRIGGER abc3_t AFTER UPDATE OF b ON abc3 BEGIN
1.183 + INSERT INTO blog VALUES(old.b, new.b);
1.184 + END;
1.185 + }
1.186 + } {}
1.187 + do_test alter2-3.2 {
1.188 + execsql {
1.189 + INSERT INTO abc3 VALUES(1, 4);
1.190 + UPDATE abc3 SET b = 2 WHERE b = 4;
1.191 + SELECT * FROM blog;
1.192 + }
1.193 + } {4 2}
1.194 + do_test alter2-3.3 {
1.195 + execsql {
1.196 + INSERT INTO abc3 VALUES(3, 4);
1.197 + INSERT INTO abc3 VALUES(5, 6);
1.198 + }
1.199 + alter_table abc3 {CREATE TABLE abc3(a, b, c);}
1.200 + execsql {
1.201 + SELECT * FROM abc3;
1.202 + }
1.203 + } {1 2 {} 3 4 {} 5 6 {}}
1.204 + do_test alter2-3.4 {
1.205 + execsql {
1.206 + UPDATE abc3 SET b = b*2 WHERE a<4;
1.207 + SELECT * FROM abc3;
1.208 + }
1.209 + } {1 4 {} 3 8 {} 5 6 {}}
1.210 + do_test alter2-3.5 {
1.211 + execsql {
1.212 + SELECT * FROM blog;
1.213 + }
1.214 + } {4 2 2 4 4 8}
1.215 +
1.216 + do_test alter2-3.6 {
1.217 + execsql {
1.218 + CREATE TABLE clog(o, n);
1.219 + CREATE TRIGGER abc3_t2 AFTER UPDATE OF c ON abc3 BEGIN
1.220 + INSERT INTO clog VALUES(old.c, new.c);
1.221 + END;
1.222 + UPDATE abc3 SET c = a*2;
1.223 + SELECT * FROM clog;
1.224 + }
1.225 + } {{} 2 {} 6 {} 10}
1.226 +}
1.227 +
1.228 +#---------------------------------------------------------------------
1.229 +# Check that an error occurs if the database is upgraded to a file
1.230 +# format that SQLite does not support (in this case 5). Note: The
1.231 +# file format is checked each time the schema is read, so changing the
1.232 +# file format requires incrementing the schema cookie.
1.233 +#
1.234 +do_test alter2-4.1 {
1.235 + db close
1.236 + set_file_format 5
1.237 + sqlite3 db test.db
1.238 +} {}
1.239 +do_test alter2-4.2 {
1.240 + # We have to run two queries here because the Tcl interface uses
1.241 + # sqlite3_prepare_v2(). In this case, the first query encounters an
1.242 + # SQLITE_SCHEMA error. Then, when trying to recompile the statement, the
1.243 + # "unsupported file format" error is encountered. So the error code
1.244 + # returned is SQLITE_SCHEMA, not SQLITE_ERROR as required by the following
1.245 + # test case.
1.246 + #
1.247 + # When the query is attempted a second time, the same error message is
1.248 + # returned but the error code is SQLITE_ERROR, because the unsupported
1.249 + # file format was detected during a call to sqlite3_prepare(), not
1.250 + # sqlite3_step().
1.251 + #
1.252 + catchsql { SELECT * FROM sqlite_master; }
1.253 + catchsql { SELECT * FROM sqlite_master; }
1.254 +} {1 {unsupported file format}}
1.255 +do_test alter2-4.3 {
1.256 + sqlite3_errcode db
1.257 +} {SQLITE_ERROR}
1.258 +do_test alter2-4.4 {
1.259 + set ::DB [sqlite3_connection_pointer db]
1.260 + catchsql {
1.261 + SELECT * FROM sqlite_master;
1.262 + }
1.263 +} {1 {unsupported file format}}
1.264 +do_test alter2-4.5 {
1.265 + sqlite3_errcode db
1.266 +} {SQLITE_ERROR}
1.267 +
1.268 +#---------------------------------------------------------------------
1.269 +# Check that executing VACUUM on a file with file-format version 2
1.270 +# resets the file format to 1.
1.271 +#
1.272 +set default_file_format [expr $SQLITE_DEFAULT_FILE_FORMAT==4 ? 4 : 1]
1.273 +ifcapable vacuum {
1.274 + do_test alter2-5.1 {
1.275 + set_file_format 2
1.276 + db close
1.277 + sqlite3 db test.db
1.278 + execsql {SELECT 1 FROM sqlite_master LIMIT 1;}
1.279 + get_file_format
1.280 + } {2}
1.281 + do_test alter2-5.2 {
1.282 + execsql {
1.283 + VACUUM;
1.284 + }
1.285 + } {}
1.286 + do_test alter2-5.3 {
1.287 + get_file_format
1.288 + } $default_file_format
1.289 +}
1.290 +
1.291 +#---------------------------------------------------------------------
1.292 +# Test that when a database with file-format 2 is opened, new
1.293 +# databases are still created with file-format 1.
1.294 +#
1.295 +do_test alter2-6.1 {
1.296 + db close
1.297 + set_file_format 2
1.298 + sqlite3 db test.db
1.299 + get_file_format
1.300 +} {2}
1.301 +ifcapable attach {
1.302 + do_test alter2-6.2 {
1.303 + file delete -force test2.db-journal
1.304 + file delete -force test2.db
1.305 + execsql {
1.306 + ATTACH 'test2.db' AS aux;
1.307 + CREATE TABLE aux.t1(a, b);
1.308 + }
1.309 + get_file_format test2.db
1.310 + } $default_file_format
1.311 +}
1.312 +do_test alter2-6.3 {
1.313 + execsql {
1.314 + CREATE TABLE t1(a, b);
1.315 + }
1.316 + get_file_format
1.317 +} {2}
1.318 +
1.319 +#---------------------------------------------------------------------
1.320 +# Test that types and values for columns added with default values
1.321 +# other than NULL work with SELECT statements.
1.322 +#
1.323 +do_test alter2-7.1 {
1.324 + execsql {
1.325 + DROP TABLE t1;
1.326 + CREATE TABLE t1(a);
1.327 + INSERT INTO t1 VALUES(1);
1.328 + INSERT INTO t1 VALUES(2);
1.329 + INSERT INTO t1 VALUES(3);
1.330 + INSERT INTO t1 VALUES(4);
1.331 + SELECT * FROM t1;
1.332 + }
1.333 +} {1 2 3 4}
1.334 +do_test alter2-7.2 {
1.335 + set sql {CREATE TABLE t1(a, b DEFAULT '123', c INTEGER DEFAULT '123')}
1.336 + alter_table t1 $sql 3
1.337 + execsql {
1.338 + SELECT * FROM t1 LIMIT 1;
1.339 + }
1.340 +} {1 123 123}
1.341 +do_test alter2-7.3 {
1.342 + execsql {
1.343 + SELECT a, typeof(a), b, typeof(b), c, typeof(c) FROM t1 LIMIT 1;
1.344 + }
1.345 +} {1 integer 123 text 123 integer}
1.346 +do_test alter2-7.4 {
1.347 + execsql {
1.348 + SELECT a, typeof(a), b, typeof(b), c, typeof(c) FROM t1 LIMIT 1;
1.349 + }
1.350 +} {1 integer 123 text 123 integer}
1.351 +do_test alter2-7.5 {
1.352 + set sql {CREATE TABLE t1(a, b DEFAULT -123.0, c VARCHAR(10) default 5)}
1.353 + alter_table t1 $sql 3
1.354 + execsql {
1.355 + SELECT a, typeof(a), b, typeof(b), c, typeof(c) FROM t1 LIMIT 1;
1.356 + }
1.357 +} {1 integer -123 integer 5 text}
1.358 +
1.359 +#-----------------------------------------------------------------------
1.360 +# Test that UPDATE trigger tables work with default values, and that when
1.361 +# a row is updated the default values are correctly transfered to the
1.362 +# new row.
1.363 +#
1.364 +ifcapable trigger {
1.365 +db function set_val {set ::val}
1.366 + do_test alter2-8.1 {
1.367 + execsql {
1.368 + CREATE TRIGGER trig1 BEFORE UPDATE ON t1 BEGIN
1.369 + SELECT set_val(
1.370 + old.b||' '||typeof(old.b)||' '||old.c||' '||typeof(old.c)||' '||
1.371 + new.b||' '||typeof(new.b)||' '||new.c||' '||typeof(new.c)
1.372 + );
1.373 + END;
1.374 + }
1.375 + list
1.376 + } {}
1.377 +}
1.378 +do_test alter2-8.2 {
1.379 + execsql {
1.380 + UPDATE t1 SET c = 10 WHERE a = 1;
1.381 + SELECT a, typeof(a), b, typeof(b), c, typeof(c) FROM t1 LIMIT 1;
1.382 + }
1.383 +} {1 integer -123 integer 10 text}
1.384 +ifcapable trigger {
1.385 + do_test alter2-8.3 {
1.386 + set ::val
1.387 + } {-123 integer 5 text -123 integer 10 text}
1.388 +}
1.389 +
1.390 +#-----------------------------------------------------------------------
1.391 +# Test that DELETE trigger tables work with default values, and that when
1.392 +# a row is updated the default values are correctly transfered to the
1.393 +# new row.
1.394 +#
1.395 +ifcapable trigger {
1.396 + do_test alter2-9.1 {
1.397 + execsql {
1.398 + CREATE TRIGGER trig2 BEFORE DELETE ON t1 BEGIN
1.399 + SELECT set_val(
1.400 + old.b||' '||typeof(old.b)||' '||old.c||' '||typeof(old.c)
1.401 + );
1.402 + END;
1.403 + }
1.404 + list
1.405 + } {}
1.406 + do_test alter2-9.2 {
1.407 + execsql {
1.408 + DELETE FROM t1 WHERE a = 2;
1.409 + }
1.410 + set ::val
1.411 + } {-123 integer 5 text}
1.412 +}
1.413 +
1.414 +#-----------------------------------------------------------------------
1.415 +# Test creating an index on a column added with a default value.
1.416 +#
1.417 +ifcapable bloblit {
1.418 + do_test alter2-10.1 {
1.419 + execsql {
1.420 + CREATE TABLE t2(a);
1.421 + INSERT INTO t2 VALUES('a');
1.422 + INSERT INTO t2 VALUES('b');
1.423 + INSERT INTO t2 VALUES('c');
1.424 + INSERT INTO t2 VALUES('d');
1.425 + }
1.426 + alter_table t2 {CREATE TABLE t2(a, b DEFAULT X'ABCD', c DEFAULT NULL);} 3
1.427 + catchsql {
1.428 + SELECT * FROM sqlite_master;
1.429 + }
1.430 + execsql {
1.431 + SELECT quote(a), quote(b), quote(c) FROM t2 LIMIT 1;
1.432 + }
1.433 + } {'a' X'ABCD' NULL}
1.434 + do_test alter2-10.2 {
1.435 + execsql {
1.436 + CREATE INDEX i1 ON t2(b);
1.437 + SELECT a FROM t2 WHERE b = X'ABCD';
1.438 + }
1.439 + } {a b c d}
1.440 + do_test alter2-10.3 {
1.441 + execsql {
1.442 + DELETE FROM t2 WHERE a = 'c';
1.443 + SELECT a FROM t2 WHERE b = X'ABCD';
1.444 + }
1.445 + } {a b d}
1.446 + do_test alter2-10.4 {
1.447 + execsql {
1.448 + SELECT count(b) FROM t2 WHERE b = X'ABCD';
1.449 + }
1.450 + } {3}
1.451 +}
1.452 +
1.453 +finish_test