1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/alter3.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,395 @@
1.4 +# 2005 February 19
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: alter3.test,v 1.11 2008/03/19 00:21:31 drh Exp $
1.20 +#
1.21 +
1.22 +set testdir [file dirname $argv0]
1.23 +
1.24 +source $testdir/tester.tcl
1.25 +
1.26 +# If SQLITE_OMIT_ALTERTABLE is defined, omit this file.
1.27 +ifcapable !altertable {
1.28 + finish_test
1.29 + return
1.30 +}
1.31 +
1.32 +# Determine if there is a codec available on this test.
1.33 +#
1.34 +if {[catch {sqlite3 -has_codec} r] || $r} {
1.35 + set has_codec 1
1.36 +} else {
1.37 + set has_codec 0
1.38 +}
1.39 +
1.40 +
1.41 +# Test Organisation:
1.42 +# ------------------
1.43 +#
1.44 +# alter3-1.*: Test that ALTER TABLE correctly modifies the CREATE TABLE sql.
1.45 +# alter3-2.*: Test error messages.
1.46 +# alter3-3.*: Test adding columns with default value NULL.
1.47 +# alter3-4.*: Test adding columns with default values other than NULL.
1.48 +# alter3-5.*: Test adding columns to tables in ATTACHed databases.
1.49 +# alter3-6.*: Test that temp triggers are not accidentally dropped.
1.50 +# alter3-7.*: Test that VACUUM resets the file-format.
1.51 +#
1.52 +
1.53 +# This procedure returns the value of the file-format in file 'test.db'.
1.54 +#
1.55 +proc get_file_format {{fname test.db}} {
1.56 + return [hexio_get_int [hexio_read $fname 44 4]]
1.57 +}
1.58 +
1.59 +do_test alter3-1.1 {
1.60 + execsql {
1.61 + CREATE TABLE abc(a, b, c);
1.62 + SELECT sql FROM sqlite_master;
1.63 + }
1.64 +} {{CREATE TABLE abc(a, b, c)}}
1.65 +do_test alter3-1.2 {
1.66 + execsql {ALTER TABLE abc ADD d INTEGER;}
1.67 + execsql {
1.68 + SELECT sql FROM sqlite_master;
1.69 + }
1.70 +} {{CREATE TABLE abc(a, b, c, d INTEGER)}}
1.71 +do_test alter3-1.3 {
1.72 + execsql {ALTER TABLE abc ADD e}
1.73 + execsql {
1.74 + SELECT sql FROM sqlite_master;
1.75 + }
1.76 +} {{CREATE TABLE abc(a, b, c, d INTEGER, e)}}
1.77 +do_test alter3-1.4 {
1.78 + execsql {
1.79 + CREATE TABLE main.t1(a, b);
1.80 + ALTER TABLE t1 ADD c;
1.81 + SELECT sql FROM sqlite_master WHERE tbl_name = 't1';
1.82 + }
1.83 +} {{CREATE TABLE t1(a, b, c)}}
1.84 +do_test alter3-1.5 {
1.85 + execsql {
1.86 + ALTER TABLE t1 ADD d CHECK (a>d);
1.87 + SELECT sql FROM sqlite_master WHERE tbl_name = 't1';
1.88 + }
1.89 +} {{CREATE TABLE t1(a, b, c, d CHECK (a>d))}}
1.90 +ifcapable foreignkey {
1.91 + do_test alter3-1.6 {
1.92 + execsql {
1.93 + CREATE TABLE t2(a, b, UNIQUE(a, b));
1.94 + ALTER TABLE t2 ADD c REFERENCES t1(c) ;
1.95 + SELECT sql FROM sqlite_master WHERE tbl_name = 't2' AND type = 'table';
1.96 + }
1.97 + } {{CREATE TABLE t2(a, b, c REFERENCES t1(c), UNIQUE(a, b))}}
1.98 +}
1.99 +do_test alter3-1.7 {
1.100 + execsql {
1.101 + CREATE TABLE t3(a, b, UNIQUE(a, b));
1.102 + ALTER TABLE t3 ADD COLUMN c VARCHAR(10, 20);
1.103 + SELECT sql FROM sqlite_master WHERE tbl_name = 't3' AND type = 'table';
1.104 + }
1.105 +} {{CREATE TABLE t3(a, b, c VARCHAR(10, 20), UNIQUE(a, b))}}
1.106 +do_test alter3-1.99 {
1.107 + catchsql {
1.108 + # May not exist if foriegn-keys are omitted at compile time.
1.109 + DROP TABLE t2;
1.110 + }
1.111 + execsql {
1.112 + DROP TABLE abc;
1.113 + DROP TABLE t1;
1.114 + DROP TABLE t3;
1.115 + }
1.116 +} {}
1.117 +
1.118 +do_test alter3-2.1 {
1.119 + execsql {
1.120 + CREATE TABLE t1(a, b);
1.121 + }
1.122 + catchsql {
1.123 + ALTER TABLE t1 ADD c PRIMARY KEY;
1.124 + }
1.125 +} {1 {Cannot add a PRIMARY KEY column}}
1.126 +do_test alter3-2.2 {
1.127 + catchsql {
1.128 + ALTER TABLE t1 ADD c UNIQUE
1.129 + }
1.130 +} {1 {Cannot add a UNIQUE column}}
1.131 +do_test alter3-2.3 {
1.132 + catchsql {
1.133 + ALTER TABLE t1 ADD b VARCHAR(10)
1.134 + }
1.135 +} {1 {duplicate column name: b}}
1.136 +do_test alter3-2.3 {
1.137 + catchsql {
1.138 + ALTER TABLE t1 ADD c NOT NULL;
1.139 + }
1.140 +} {1 {Cannot add a NOT NULL column with default value NULL}}
1.141 +do_test alter3-2.4 {
1.142 + catchsql {
1.143 + ALTER TABLE t1 ADD c NOT NULL DEFAULT 10;
1.144 + }
1.145 +} {0 {}}
1.146 +ifcapable view {
1.147 + do_test alter3-2.5 {
1.148 + execsql {
1.149 + CREATE VIEW v1 AS SELECT * FROM t1;
1.150 + }
1.151 + catchsql {
1.152 + alter table v1 add column d;
1.153 + }
1.154 + } {1 {Cannot add a column to a view}}
1.155 +}
1.156 +do_test alter3-2.6 {
1.157 + catchsql {
1.158 + alter table t1 add column d DEFAULT CURRENT_TIME;
1.159 + }
1.160 +} {1 {Cannot add a column with non-constant default}}
1.161 +do_test alter3-2.99 {
1.162 + execsql {
1.163 + DROP TABLE t1;
1.164 + }
1.165 +} {}
1.166 +
1.167 +do_test alter3-3.1 {
1.168 + execsql {
1.169 + CREATE TABLE t1(a, b);
1.170 + INSERT INTO t1 VALUES(1, 100);
1.171 + INSERT INTO t1 VALUES(2, 300);
1.172 + SELECT * FROM t1;
1.173 + }
1.174 +} {1 100 2 300}
1.175 +do_test alter3-3.1 {
1.176 + execsql {
1.177 + PRAGMA schema_version = 10;
1.178 + }
1.179 +} {}
1.180 +do_test alter3-3.2 {
1.181 + execsql {
1.182 + ALTER TABLE t1 ADD c;
1.183 + SELECT * FROM t1;
1.184 + }
1.185 +} {1 100 {} 2 300 {}}
1.186 +if {!$has_codec} {
1.187 + do_test alter3-3.3 {
1.188 + get_file_format
1.189 + } {3}
1.190 +}
1.191 +ifcapable schema_version {
1.192 + do_test alter3-3.4 {
1.193 + execsql {
1.194 + PRAGMA schema_version;
1.195 + }
1.196 + } {11}
1.197 +}
1.198 +
1.199 +do_test alter3-4.1 {
1.200 + db close
1.201 + file delete -force test.db
1.202 + set ::DB [sqlite3 db test.db]
1.203 + execsql {
1.204 + CREATE TABLE t1(a, b);
1.205 + INSERT INTO t1 VALUES(1, 100);
1.206 + INSERT INTO t1 VALUES(2, 300);
1.207 + SELECT * FROM t1;
1.208 + }
1.209 +} {1 100 2 300}
1.210 +do_test alter3-4.1 {
1.211 + execsql {
1.212 + PRAGMA schema_version = 20;
1.213 + }
1.214 +} {}
1.215 +do_test alter3-4.2 {
1.216 + execsql {
1.217 + ALTER TABLE t1 ADD c DEFAULT 'hello world';
1.218 + SELECT * FROM t1;
1.219 + }
1.220 +} {1 100 {hello world} 2 300 {hello world}}
1.221 +if {!$has_codec} {
1.222 + do_test alter3-4.3 {
1.223 + get_file_format
1.224 + } {3}
1.225 +}
1.226 +ifcapable schema_version {
1.227 + do_test alter3-4.4 {
1.228 + execsql {
1.229 + PRAGMA schema_version;
1.230 + }
1.231 + } {21}
1.232 +}
1.233 +do_test alter3-4.99 {
1.234 + execsql {
1.235 + DROP TABLE t1;
1.236 + }
1.237 +} {}
1.238 +
1.239 +ifcapable attach {
1.240 + do_test alter3-5.1 {
1.241 + file delete -force test2.db
1.242 + file delete -force test2.db-journal
1.243 + execsql {
1.244 + CREATE TABLE t1(a, b);
1.245 + INSERT INTO t1 VALUES(1, 'one');
1.246 + INSERT INTO t1 VALUES(2, 'two');
1.247 + ATTACH 'test2.db' AS aux;
1.248 + CREATE TABLE aux.t1 AS SELECT * FROM t1;
1.249 + PRAGMA aux.schema_version = 30;
1.250 + SELECT sql FROM aux.sqlite_master;
1.251 + }
1.252 + } {{CREATE TABLE t1(a,b)}}
1.253 + do_test alter3-5.2 {
1.254 + execsql {
1.255 + ALTER TABLE aux.t1 ADD COLUMN c VARCHAR(128);
1.256 + SELECT sql FROM aux.sqlite_master;
1.257 + }
1.258 + } {{CREATE TABLE t1(a,b, c VARCHAR(128))}}
1.259 + do_test alter3-5.3 {
1.260 + execsql {
1.261 + SELECT * FROM aux.t1;
1.262 + }
1.263 + } {1 one {} 2 two {}}
1.264 + ifcapable schema_version {
1.265 + do_test alter3-5.4 {
1.266 + execsql {
1.267 + PRAGMA aux.schema_version;
1.268 + }
1.269 + } {31}
1.270 + }
1.271 + if {!$has_codec} {
1.272 + do_test alter3-5.5 {
1.273 + list [get_file_format test2.db] [get_file_format]
1.274 + } {2 3}
1.275 + }
1.276 + do_test alter3-5.6 {
1.277 + execsql {
1.278 + ALTER TABLE aux.t1 ADD COLUMN d DEFAULT 1000;
1.279 + SELECT sql FROM aux.sqlite_master;
1.280 + }
1.281 + } {{CREATE TABLE t1(a,b, c VARCHAR(128), d DEFAULT 1000)}}
1.282 + do_test alter3-5.7 {
1.283 + execsql {
1.284 + SELECT * FROM aux.t1;
1.285 + }
1.286 + } {1 one {} 1000 2 two {} 1000}
1.287 + ifcapable schema_version {
1.288 + do_test alter3-5.8 {
1.289 + execsql {
1.290 + PRAGMA aux.schema_version;
1.291 + }
1.292 + } {32}
1.293 + }
1.294 + do_test alter3-5.9 {
1.295 + execsql {
1.296 + SELECT * FROM t1;
1.297 + }
1.298 + } {1 one 2 two}
1.299 + do_test alter3-5.99 {
1.300 + execsql {
1.301 + DROP TABLE aux.t1;
1.302 + DROP TABLE t1;
1.303 + }
1.304 + } {}
1.305 +}
1.306 +
1.307 +#----------------------------------------------------------------
1.308 +# Test that the table schema is correctly reloaded when a column
1.309 +# is added to a table.
1.310 +#
1.311 +ifcapable trigger&&tempdb {
1.312 + do_test alter3-6.1 {
1.313 + execsql {
1.314 + CREATE TABLE t1(a, b);
1.315 + CREATE TABLE log(trig, a, b);
1.316 +
1.317 + CREATE TRIGGER t1_a AFTER INSERT ON t1 BEGIN
1.318 + INSERT INTO log VALUES('a', new.a, new.b);
1.319 + END;
1.320 + CREATE TEMP TRIGGER t1_b AFTER INSERT ON t1 BEGIN
1.321 + INSERT INTO log VALUES('b', new.a, new.b);
1.322 + END;
1.323 +
1.324 + INSERT INTO t1 VALUES(1, 2);
1.325 + SELECT * FROM log;
1.326 + }
1.327 + } {b 1 2 a 1 2}
1.328 + do_test alter3-6.2 {
1.329 + execsql {
1.330 + ALTER TABLE t1 ADD COLUMN c DEFAULT 'c';
1.331 + INSERT INTO t1(a, b) VALUES(3, 4);
1.332 + SELECT * FROM log;
1.333 + }
1.334 + } {b 1 2 a 1 2 b 3 4 a 3 4}
1.335 +}
1.336 +
1.337 +if {!$has_codec} {
1.338 + ifcapable vacuum {
1.339 + do_test alter3-7.1 {
1.340 + execsql {
1.341 + VACUUM;
1.342 + }
1.343 + get_file_format
1.344 + } {1}
1.345 + do_test alter3-7.2 {
1.346 + execsql {
1.347 + CREATE TABLE abc(a, b, c);
1.348 + ALTER TABLE abc ADD d DEFAULT NULL;
1.349 + }
1.350 + get_file_format
1.351 + } {2}
1.352 + do_test alter3-7.3 {
1.353 + execsql {
1.354 + ALTER TABLE abc ADD e DEFAULT 10;
1.355 + }
1.356 + get_file_format
1.357 + } {3}
1.358 + do_test alter3-7.4 {
1.359 + execsql {
1.360 + ALTER TABLE abc ADD f DEFAULT NULL;
1.361 + }
1.362 + get_file_format
1.363 + } {3}
1.364 + do_test alter3-7.5 {
1.365 + execsql {
1.366 + VACUUM;
1.367 + }
1.368 + get_file_format
1.369 + } {1}
1.370 + }
1.371 +}
1.372 +
1.373 +# Ticket #1183 - Make sure adding columns to large tables does not cause
1.374 +# memory corruption (as was the case before this bug was fixed).
1.375 +do_test alter3-8.1 {
1.376 + execsql {
1.377 + CREATE TABLE t4(c1);
1.378 + }
1.379 +} {}
1.380 +set ::sql ""
1.381 +do_test alter3-8.2 {
1.382 + set cols c1
1.383 + for {set i 2} {$i < 100} {incr i} {
1.384 + execsql "
1.385 + ALTER TABLE t4 ADD c$i
1.386 + "
1.387 + lappend cols c$i
1.388 + }
1.389 + set ::sql "CREATE TABLE t4([join $cols {, }])"
1.390 + list
1.391 +} {}
1.392 +do_test alter3-8.2 {
1.393 + execsql {
1.394 + SELECT sql FROM sqlite_master WHERE name = 't4';
1.395 + }
1.396 +} [list $::sql]
1.397 +
1.398 +finish_test