AryaWu/sqlite
0
1# 2005 February 192#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 17set testdir [file dirname $argv0]18 19source $testdir/tester.tcl20 21# If SQLITE_OMIT_ALTERTABLE is defined, omit this file.22ifcapable !altertable {23 finish_test24 return25}26 27# Determine if there is a codec available on this test.28#29if {[catch {sqlite3 -has-codec} r] || $r} {30 set has_codec 131} else {32 set has_codec 033}34 35 36# Test Organisation:37# ------------------38#39# alter3-1.*: Test that ALTER TABLE correctly modifies the CREATE TABLE sql.40# alter3-2.*: Test error messages.41# alter3-3.*: Test adding columns with default value NULL.42# alter3-4.*: Test adding columns with default values other than NULL.43# alter3-5.*: Test adding columns to tables in ATTACHed databases.44# alter3-6.*: Test that temp triggers are not accidentally dropped.45# alter3-7.*: Test that VACUUM resets the file-format.46#47 48# This procedure returns the value of the file-format in file 'test.db'.49# 50proc get_file_format {{fname test.db}} {51 return [hexio_get_int [hexio_read $fname 44 4]]52}53 54do_test alter3-1.1 {55 sqlite3_db_config db LEGACY_FILE_FORMAT 156 execsql {57 CREATE TABLE abc(a, b, c);58 SELECT sql FROM sqlite_master;59 }60} {{CREATE TABLE abc(a, b, c)}}61do_test alter3-1.2 {62 execsql {ALTER TABLE abc ADD d INTEGER;}63 execsql {64 SELECT sql FROM sqlite_master;65 }66} {{CREATE TABLE abc(a, b, c, d INTEGER)}}67do_test alter3-1.3 {68 execsql {ALTER TABLE abc ADD e}69 execsql {70 SELECT sql FROM sqlite_master;71 }72} {{CREATE TABLE abc(a, b, c, d INTEGER, e)}}73do_test alter3-1.4 {74 execsql {75 CREATE TABLE main.t1(a, b);76 ALTER TABLE t1 ADD c;77 SELECT sql FROM sqlite_master WHERE tbl_name = 't1';78 }79} {{CREATE TABLE t1(a, b, c)}}80do_test alter3-1.5 {81 execsql {82 ALTER TABLE t1 ADD d CHECK (a>d);83 SELECT sql FROM sqlite_master WHERE tbl_name = 't1';84 }85} {{CREATE TABLE t1(a, b, c, d CHECK (a>d))}}86ifcapable foreignkey {87 do_test alter3-1.6 {88 execsql {89 CREATE TABLE t2(a, b, UNIQUE(a, b));90 ALTER TABLE t2 ADD c REFERENCES t1(c) ;91 SELECT sql FROM sqlite_master WHERE tbl_name = 't2' AND type = 'table';92 }93 } {{CREATE TABLE t2(a, b, c REFERENCES t1(c), UNIQUE(a, b))}}94}95do_test alter3-1.7 {96 execsql {97 CREATE TABLE t3(a, b, UNIQUE(a, b));98 ALTER TABLE t3 ADD COLUMN c VARCHAR(10, 20);99 SELECT sql FROM sqlite_master WHERE tbl_name = 't3' AND type = 'table';100 }101} {{CREATE TABLE t3(a, b, c VARCHAR(10, 20), UNIQUE(a, b))}}102do_test alter3-1.99 {103 catchsql {104 # May not exist if foreign-keys are omitted at compile time.105 DROP TABLE t2; 106 }107 execsql {108 DROP TABLE abc; 109 DROP TABLE t1; 110 DROP TABLE t3; 111 }112} {}113 114do_test alter3-2.1 {115 execsql {116 CREATE TABLE t1(a, b);117 INSERT INTO t1 VALUES(1,2);118 }119 catchsql {120 ALTER TABLE t1 ADD c PRIMARY KEY;121 }122} {1 {Cannot add a PRIMARY KEY column}}123do_test alter3-2.2 {124 catchsql {125 ALTER TABLE t1 ADD c UNIQUE126 }127} {1 {Cannot add a UNIQUE column}}128do_test alter3-2.3 {129 catchsql {130 ALTER TABLE t1 ADD b VARCHAR(10)131 }132} {1 {duplicate column name: b}}133do_test alter3-2.3 {134 catchsql {135 ALTER TABLE t1 ADD c NOT NULL;136 }137} {1 {Cannot add a NOT NULL column with default value NULL}}138do_test alter3-2.4 {139 catchsql {140 ALTER TABLE t1 ADD c NOT NULL DEFAULT 10;141 }142} {0 {}}143ifcapable view {144 do_test alter3-2.5 {145 execsql {146 CREATE VIEW v1 AS SELECT * FROM t1;147 }148 catchsql {149 alter table v1 add column d;150 }151 } {1 {Cannot add a column to a view}}152}153do_test alter3-2.6 {154 catchsql {155 alter table t1 add column d DEFAULT CURRENT_TIME;156 }157} {1 {Cannot add a column with non-constant default}}158do_test alter3-2.99 {159 execsql {160 DROP TABLE t1;161 }162} {}163 164do_test alter3-3.1 {165 execsql {166 CREATE TABLE t1(a, b);167 INSERT INTO t1 VALUES(1, 100);168 INSERT INTO t1 VALUES(2, 300);169 SELECT * FROM t1;170 }171} {1 100 2 300}172do_test alter3-3.1 {173 execsql {174 PRAGMA schema_version = 10;175 }176} {}177do_test alter3-3.2 {178 execsql {179 ALTER TABLE t1 ADD c;180 SELECT * FROM t1;181 }182} {1 100 {} 2 300 {}}183if {!$has_codec} {184 do_test alter3-3.3 {185 get_file_format186 } {3}187}188ifcapable schema_version {189 do_test alter3-3.4 {190 execsql {191 PRAGMA schema_version;192 }193 } {11}194}195 196do_test alter3-4.1 {197 db close198 forcedelete test.db199 set ::DB [sqlite3 db test.db]200 sqlite3_db_config db LEGACY_FILE_FORMAT 1201 execsql {202 CREATE TABLE t1(a, b);203 INSERT INTO t1 VALUES(1, 100);204 INSERT INTO t1 VALUES(2, 300);205 SELECT * FROM t1;206 }207} {1 100 2 300}208do_test alter3-4.1 {209 execsql {210 PRAGMA schema_version = 20;211 }212} {}213do_test alter3-4.2 {214 execsql {215 ALTER TABLE t1 ADD c DEFAULT 'hello world';216 SELECT * FROM t1;217 }218} {1 100 {hello world} 2 300 {hello world}}219if {!$has_codec} {220 do_test alter3-4.3 {221 get_file_format222 } {3}223}224ifcapable schema_version {225 do_test alter3-4.4 {226 execsql {227 PRAGMA schema_version;228 }229 } {21}230}231do_test alter3-4.99 {232 execsql {233 DROP TABLE t1;234 }235} {}236 237ifcapable attach {238 do_test alter3-5.1 {239 forcedelete test2.db240 forcedelete test2.db-journal241 execsql {242 CREATE TABLE t1(a, b);243 INSERT INTO t1 VALUES(1, 'one');244 INSERT INTO t1 VALUES(2, 'two');245 ATTACH 'test2.db' AS aux;246 CREATE TABLE aux.t1 AS SELECT * FROM t1;247 PRAGMA aux.schema_version = 30;248 SELECT sql FROM aux.sqlite_master;249 } 250 } {{CREATE TABLE t1(a,b)}}251 do_test alter3-5.2 {252 execsql {253 ALTER TABLE aux.t1 ADD COLUMN c VARCHAR(128);254 SELECT sql FROM aux.sqlite_master;255 }256 } {{CREATE TABLE t1(a,b, c VARCHAR(128))}}257 do_test alter3-5.3 {258 execsql {259 SELECT * FROM aux.t1;260 }261 } {1 one {} 2 two {}}262 ifcapable schema_version {263 do_test alter3-5.4 {264 execsql {265 PRAGMA aux.schema_version;266 }267 } {31}268 }269 if {!$has_codec} {270 do_test alter3-5.5 {271 list [get_file_format test2.db] [get_file_format]272 } {3 3}273 }274 do_test alter3-5.6 {275 execsql {276 ALTER TABLE aux.t1 ADD COLUMN d DEFAULT 1000;277 SELECT sql FROM aux.sqlite_master;278 }279 } {{CREATE TABLE t1(a,b, c VARCHAR(128), d DEFAULT 1000)}}280 do_test alter3-5.7 {281 execsql {282 SELECT * FROM aux.t1;283 }284 } {1 one {} 1000 2 two {} 1000}285 ifcapable schema_version {286 do_test alter3-5.8 {287 execsql {288 PRAGMA aux.schema_version;289 }290 } {32}291 }292 do_test alter3-5.9 {293 execsql {294 SELECT * FROM t1;295 }296 } {1 one 2 two}297 do_test alter3-5.99 {298 execsql {299 DROP TABLE aux.t1;300 DROP TABLE t1;301 }302 } {}303}304 305#----------------------------------------------------------------306# Test that the table schema is correctly reloaded when a column307# is added to a table.308#309ifcapable trigger&&tempdb {310 do_test alter3-6.1 {311 execsql {312 CREATE TABLE t1(a, b);313 CREATE TABLE log(trig, a, b);314 315 CREATE TRIGGER t1_a AFTER INSERT ON t1 BEGIN316 INSERT INTO log VALUES('a', new.a, new.b);317 END;318 CREATE TEMP TRIGGER t1_b AFTER INSERT ON t1 BEGIN319 INSERT INTO log VALUES('b', new.a, new.b);320 END;321 322 INSERT INTO t1 VALUES(1, 2);323 SELECT * FROM log;324 }325 } {b 1 2 a 1 2}326 do_test alter3-6.2 {327 execsql {328 ALTER TABLE t1 ADD COLUMN c DEFAULT 'c';329 INSERT INTO t1(a, b) VALUES(3, 4);330 SELECT * FROM log;331 }332 } {b 1 2 a 1 2 b 3 4 a 3 4}333}334 335if {!$has_codec} {336 ifcapable vacuum {337 do_test alter3-7.1 {338 execsql {339 VACUUM;340 }341 get_file_format342 } {1}343 do_test alter3-7.2 {344 execsql {345 CREATE TABLE abc(a, b, c);346 ALTER TABLE abc ADD d DEFAULT NULL;347 }348 get_file_format349 } {3}350 do_test alter3-7.3 {351 execsql {352 ALTER TABLE abc ADD e DEFAULT 10;353 }354 get_file_format355 } {3}356 do_test alter3-7.4 {357 execsql {358 ALTER TABLE abc ADD f DEFAULT NULL;359 }360 get_file_format361 } {3}362 do_test alter3-7.5 {363 execsql {364 VACUUM;365 }366 get_file_format367 } {1}368 }369}370 371# Ticket #1183 - Make sure adding columns to large tables does not cause372# memory corruption (as was the case before this bug was fixed).373do_test alter3-8.1 {374 execsql {375 CREATE TABLE t4(c1);376 }377} {}378set ::sql ""379do_test alter3-8.2 {380 set cols c1381 for {set i 2} {$i < 100} {incr i} {382 execsql "383 ALTER TABLE t4 ADD c$i384 "385 lappend cols c$i386 }387 set ::sql "CREATE TABLE t4([join $cols {, }])"388 list 389} {}390do_test alter3-8.2 {391 execsql {392 SELECT sql FROM sqlite_master WHERE name = 't4';393 }394} [list $::sql]395 396# 2021-07-20: Add support for detecting CHECK and NOT NULL constraint397# violations in ALTER TABLE ADD COLUMN398#399reset_db400do_execsql_test alter3-9.1 {401 CREATE TABLE t1(a,b);402 INSERT INTO t1 VALUES(1, 2), ('null!',NULL), (3,4);403} {}404do_catchsql_test alter3-9.2 {405 ALTER TABLE t1 ADD COLUMN c CHECK(a!=1);406} {1 {CHECK constraint failed}}407do_catchsql_test alter3-9.3 {408 ALTER TABLE t1 ADD COLUMN c CHECK(a!=3);409} {1 {CHECK constraint failed}}410do_catchsql_test alter3-9.4 {411 ALTER TABLE t1 ADD COLUMN c CHECK(a!=2);412} {0 {}}413do_catchsql_test alter3-9.5 {414 ALTER TABLE t1 ADD COLUMN d AS (b+1) NOT NULL;415} {1 {NOT NULL constraint failed}}416do_catchsql_test alter3-9.6 {417 ALTER TABLE t1 ADD COLUMN d AS (b+1) NOT NULL CHECK(a!=1);418} {1 {CHECK constraint failed}}419do_catchsql_test alter3-9.7 {420 ALTER TABLE t1 ADD COLUMN d AS (b+1) NOT NULL CHECK(a!=3);421} {1 {NOT NULL constraint failed}}422 423do_execsql_test alter3-9.10 {424 CREATE TEMP TABLE t0(m,n);425 INSERT INTO t0 VALUES(1, 2), ('null!',NULL), (3,4);426 ATTACH ':memory:' AS aux1;427 CREATE TABLE aux1.t2(x,y);428 INSERT INTO t2 VALUES(1, 2), ('null!',NULL), (3,4);429} {}430do_catchsql_test alter3-9.11 {431 ALTER TABLE t0 ADD COLUMN xtra1 AS (n+1) NOT NULL CHECK(m!=1);432} {1 {CHECK constraint failed}}433do_catchsql_test alter3-9.12 {434 ALTER TABLE t0 ADD COLUMN xtra1 AS (n+1) NOT NULL CHECK(m!=3);435} {1 {NOT NULL constraint failed}}436do_catchsql_test alter3-9.13 {437 ALTER TABLE t2 ADD COLUMN xtra1 AS (y+1) NOT NULL CHECK(x!=1);438} {1 {CHECK constraint failed}}439do_catchsql_test alter3-9.14 {440 ALTER TABLE t2 ADD COLUMN xtra1 AS (y+1) NOT NULL CHECK(x!=3);441} {1 {NOT NULL constraint failed}}442 443finish_test444