AryaWu/sqlite
0
1# 2009 February 22#3# The author disclaims copyright to this source code. In place of4# 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. The12# focus of this script is testing that SQLite can handle a subtle 13# file format change that may be used in the future to implement14# "ALTER TABLE ... ADD COLUMN".15#16# $Id: alter4.test,v 1.1 2009/02/02 18:03:22 drh Exp $17#18 19set testdir [file dirname $argv0]20 21source $testdir/tester.tcl22 23# If SQLITE_OMIT_ALTERTABLE is defined, omit this file.24ifcapable !altertable {25 finish_test26 return27}28 29 30# Test Organisation:31# ------------------32#33# alter4-1.*: Test that ALTER TABLE correctly modifies the CREATE TABLE sql.34# alter4-2.*: Test error messages.35# alter4-3.*: Test adding columns with default value NULL.36# alter4-4.*: Test adding columns with default values other than NULL.37# alter4-5.*: Test adding columns to tables in ATTACHed databases.38# alter4-6.*: Test that temp triggers are not accidentally dropped.39# alter4-7.*: Test that VACUUM resets the file-format.40#41 42do_test alter4-1.1 {43 execsql {44 CREATE TEMP TABLE abc(a, b, c);45 SELECT sql FROM sqlite_temp_master;46 }47} {{CREATE TABLE abc(a, b, c)}}48do_test alter4-1.1b {49 execsql {50 SELECT sql FROM temp.sqlite_master;51 }52} {{CREATE TABLE abc(a, b, c)}}53do_test alter4-1.2 {54 execsql {ALTER TABLE abc ADD d INTEGER;}55 execsql {56 SELECT sql FROM sqlite_temp_master;57 }58} {{CREATE TABLE abc(a, b, c, d INTEGER)}}59do_test alter4-1.2b {60 execsql {61 SELECT sql FROM temp.sqlite_master;62 }63} {{CREATE TABLE abc(a, b, c, d INTEGER)}}64do_test alter4-1.3 {65 execsql {ALTER TABLE abc ADD e}66 execsql {67 SELECT sql FROM sqlite_temp_master;68 }69} {{CREATE TABLE abc(a, b, c, d INTEGER, e)}}70do_test alter4-1.3b {71 execsql {72 SELECT sql FROM temp.sqlite_master;73 }74} {{CREATE TABLE abc(a, b, c, d INTEGER, e)}}75do_test alter4-1.4 {76 execsql {77 CREATE TABLE temp.t1(a, b);78 ALTER TABLE t1 ADD c;79 SELECT sql FROM sqlite_temp_master WHERE tbl_name = 't1';80 }81} {{CREATE TABLE t1(a, b, c)}}82do_test alter4-1.4b {83 execsql {84 SELECT sql FROM temp.sqlite_master WHERE tbl_name = 't1';85 }86} {{CREATE TABLE t1(a, b, c)}}87do_test alter4-1.5 {88 execsql {89 ALTER TABLE t1 ADD d CHECK (a>d);90 SELECT sql FROM sqlite_temp_master WHERE tbl_name = 't1';91 }92} {{CREATE TABLE t1(a, b, c, d CHECK (a>d))}}93ifcapable foreignkey {94 do_test alter4-1.6 {95 execsql {96 CREATE TEMP TABLE t2(a, b, UNIQUE(a, b));97 ALTER TABLE t2 ADD c REFERENCES t1(c) ;98 SELECT sql FROM sqlite_temp_master99 WHERE tbl_name = 't2' AND type = 'table';100 }101 } {{CREATE TABLE t2(a, b, c REFERENCES t1(c), UNIQUE(a, b))}}102}103do_test alter4-1.7 {104 execsql {105 CREATE TEMPORARY TABLE t3(a, b, UNIQUE(a, b));106 ALTER TABLE t3 ADD COLUMN c VARCHAR(10, 20);107 SELECT sql FROM sqlite_temp_master108 WHERE tbl_name = 't3' AND type = 'table';109 }110} {{CREATE TABLE t3(a, b, c VARCHAR(10, 20), UNIQUE(a, b))}}111do_test alter4-1.99 {112 catchsql {113 # May not exist if foreign-keys are omitted at compile time.114 DROP TABLE t2; 115 }116 execsql {117 DROP TABLE abc; 118 DROP TABLE t1; 119 DROP TABLE t3; 120 }121} {}122 123do_test alter4-2.1 {124 execsql {125 CREATE TABLE temp.t1(a, b);126 INSERT INTO t1 VALUES(1,2);127 }128 catchsql {129 ALTER TABLE t1 ADD c PRIMARY KEY;130 }131} {1 {Cannot add a PRIMARY KEY column}}132do_test alter4-2.2 {133 catchsql {134 ALTER TABLE t1 ADD c UNIQUE135 }136} {1 {Cannot add a UNIQUE column}}137do_test alter4-2.3 {138 catchsql {139 ALTER TABLE t1 ADD b VARCHAR(10)140 }141} {1 {duplicate column name: b}}142do_test alter4-2.3 {143 catchsql {144 ALTER TABLE t1 ADD c NOT NULL;145 }146} {1 {Cannot add a NOT NULL column with default value NULL}}147do_test alter4-2.4 {148 catchsql {149 ALTER TABLE t1 ADD c NOT NULL DEFAULT 10;150 }151} {0 {}}152ifcapable view {153 do_test alter4-2.5 {154 execsql {155 CREATE TEMPORARY VIEW v1 AS SELECT * FROM t1;156 }157 catchsql {158 alter table v1 add column d;159 }160 } {1 {Cannot add a column to a view}}161}162do_test alter4-2.6 {163 catchsql {164 alter table t1 add column d DEFAULT CURRENT_TIME;165 }166} {1 {Cannot add a column with non-constant default}}167do_test alter4-2.7 {168 catchsql {169 alter table t1 add column d default (-5+1);170 }171} {1 {Cannot add a column with non-constant default}}172do_test alter4-2.99 {173 execsql {174 DROP TABLE t1;175 }176} {}177 178do_test alter4-3.1 {179 execsql {180 CREATE TEMP TABLE t1(a, b);181 INSERT INTO t1 VALUES(1, 100);182 INSERT INTO t1 VALUES(2, 300);183 SELECT * FROM t1;184 }185} {1 100 2 300}186do_test alter4-3.1 {187 execsql {188 PRAGMA schema_version = 10;189 }190} {}191do_test alter4-3.2 {192 execsql {193 ALTER TABLE t1 ADD c;194 SELECT * FROM t1;195 }196} {1 100 {} 2 300 {}}197ifcapable schema_version {198 do_test alter4-3.4 {199 execsql {200 PRAGMA schema_version;201 }202 } {10}203}204 205do_test alter4-4.1 {206 db close207 forcedelete test.db208 set ::DB [sqlite3 db test.db]209 execsql {210 CREATE TEMP TABLE t1(a, b);211 INSERT INTO t1 VALUES(1, 100);212 INSERT INTO t1 VALUES(2, 300);213 SELECT * FROM t1;214 }215} {1 100 2 300}216do_test alter4-4.1 {217 execsql {218 PRAGMA schema_version = 20;219 }220} {}221do_test alter4-4.2 {222 execsql {223 ALTER TABLE t1 ADD c DEFAULT 'hello world';224 SELECT * FROM t1;225 }226} {1 100 {hello world} 2 300 {hello world}}227ifcapable schema_version {228 do_test alter4-4.4 {229 execsql {230 PRAGMA schema_version;231 }232 } {20}233}234do_test alter4-4.99 {235 execsql {236 DROP TABLE t1;237 }238} {}239 240ifcapable attach {241 do_test alter4-5.1 {242 forcedelete test2.db243 forcedelete test2.db-journal244 execsql {245 CREATE TEMP TABLE t1(a, b);246 INSERT INTO t1 VALUES(1, 'one');247 INSERT INTO t1 VALUES(2, 'two');248 ATTACH 'test2.db' AS aux;249 CREATE TABLE aux.t1 AS SELECT * FROM t1;250 PRAGMA aux.schema_version = 30;251 SELECT sql FROM aux.sqlite_master;252 } 253 } {{CREATE TABLE t1(a,b)}}254 do_test alter4-5.2 {255 execsql {256 ALTER TABLE aux.t1 ADD COLUMN c VARCHAR(128);257 SELECT sql FROM aux.sqlite_master;258 }259 } {{CREATE TABLE t1(a,b, c VARCHAR(128))}}260 do_test alter4-5.3 {261 execsql {262 SELECT * FROM aux.t1;263 }264 } {1 one {} 2 two {}}265 ifcapable schema_version {266 do_test alter4-5.4 {267 execsql {268 PRAGMA aux.schema_version;269 }270 } {31}271 }272 do_test alter4-5.6 {273 execsql {274 ALTER TABLE aux.t1 ADD COLUMN d DEFAULT 1000;275 SELECT sql FROM aux.sqlite_master;276 }277 } {{CREATE TABLE t1(a,b, c VARCHAR(128), d DEFAULT 1000)}}278 do_test alter4-5.7 {279 execsql {280 SELECT * FROM aux.t1;281 }282 } {1 one {} 1000 2 two {} 1000}283 ifcapable schema_version {284 do_test alter4-5.8 {285 execsql {286 PRAGMA aux.schema_version;287 }288 } {32}289 }290 do_test alter4-5.9 {291 execsql {292 SELECT * FROM t1;293 }294 } {1 one 2 two}295 do_test alter4-5.99 {296 execsql {297 DROP TABLE aux.t1;298 DROP TABLE t1;299 }300 } {}301}302 303#----------------------------------------------------------------304# Test that the table schema is correctly reloaded when a column305# is added to a table.306#307ifcapable trigger&&tempdb {308 do_test alter4-6.1 {309 execsql {310 CREATE TEMP TABLE t1(a, b);311 CREATE TEMP TABLE log(trig, a, b);312 313 CREATE TRIGGER t1_a AFTER INSERT ON t1 BEGIN314 INSERT INTO log VALUES('a', new.a, new.b);315 END;316 CREATE TEMP TRIGGER t1_b AFTER INSERT ON t1 BEGIN317 INSERT INTO log VALUES('b', new.a, new.b);318 END;319 320 INSERT INTO t1 VALUES(1, 2);321 SELECT * FROM log ORDER BY trig, a, b;322 }323 } {a 1 2 b 1 2}324 do_test alter4-6.2 {325 execsql {326 ALTER TABLE t1 ADD COLUMN c DEFAULT 'c';327 INSERT INTO t1(a, b) VALUES(3, 4);328 SELECT * FROM log ORDER BY trig, a, b;329 }330 } {a 1 2 a 3 4 b 1 2 b 3 4}331}332 333# Ticket #1183 - Make sure adding columns to large tables does not cause334# memory corruption (as was the case before this bug was fixed).335do_test alter4-8.1 {336 execsql {337 CREATE TEMP TABLE t4(c1);338 }339} {}340set ::sql ""341do_test alter4-8.2 {342 set cols c1343 for {set i 2} {$i < 100} {incr i} {344 execsql "345 ALTER TABLE t4 ADD c$i346 "347 lappend cols c$i348 }349 set ::sql "CREATE TABLE t4([join $cols {, }])"350 list 351} {}352do_test alter4-8.2 {353 execsql {354 SELECT sql FROM sqlite_temp_master WHERE name = 't4';355 }356} [list $::sql]357 358 359# Test that a default value equal to -1 multipied by the smallest possible360# 64-bit integer is correctly converted to a real.361do_execsql_test alter4-9.1 {362 CREATE TABLE t5(363 a INTEGER DEFAULT -9223372036854775808,364 b INTEGER DEFAULT (-(-9223372036854775808))365 );366 INSERT INTO t5 DEFAULT VALUES;367}368 369do_execsql_test alter4-9.2 { SELECT typeof(a), a, typeof(b), b FROM t5; } {370 integer -9223372036854775808371 real 9.22337203685478e+18372}373 374do_execsql_test alter4-9.3 { 375 ALTER TABLE t5 ADD COLUMN c INTEGER DEFAULT (-(-9223372036854775808));376 SELECT typeof(c), c FROM t5;377} {real 9.22337203685478e+18}378 379# Confirm that doing an ALTER TABLE on a legacy format database380# does not corrupt DESC indexes.381#382# Ticket https://sqlite.org/src/tktview/f68bf68513a1c383#384do_test alter4-10.1 {385 db close386 sqlite3 db :memory:387 sqlite3_db_config db LEGACY_FILE_FORMAT 1388 db eval {389 CREATE TABLE t1(a,b,c);390 CREATE INDEX t1a ON t1(a DESC);391 INSERT INTO t1 VALUES(1,2,3);392 INSERT INTO t1 VALUES(2,3,4);393 ALTER TABLE t1 ADD COLUMN d;394 PRAGMA integrity_check;395 }396} {ok}397 398reset_db399do_execsql_test alter4-11.0 {400 CREATE TABLE t1(c INTEGER PRIMARY KEY, d);401 INSERT INTO t1(c,d) VALUES(1,2);402 PRAGMA foreign_keys = on;403 ALTER TABLE t1 ADD COLUMN e;404}405 406do_execsql_test alter4-11.1 {407 ALTER TABLE t1 ADD COLUMN f REFERENCES t1;408}409 410do_catchsql_test alter4-11.2 {411 ALTER TABLE t1 ADD COLUMN g REFERENCES t1 DEFAULT 4;412} {1 {Cannot add a REFERENCES column with non-NULL default value}}413 414do_catchsql_test alter4-11.3 {415 ALTER TABLE t2 ADD COLUMN g;416} {1 {no such table: t2}}417 418ifcapable fts5 {419 do_execsql_test alter4-11.4 {420 CREATE VIRTUAL TABLE fff USING fts5(f);421 }422 do_catchsql_test alter4-11.2 {423 ALTER TABLE fff ADD COLUMN g;424 } {1 {virtual tables may not be altered}}425}426 427finish_test428