gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/alter3.test (about) 1 # 2005 February 19 2 # 3 # The author disclaims copyright to this source code. In place of 4 # 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. The 12 # focus of this script is testing that SQLite can handle a subtle 13 # file format change that may be used in the future to implement 14 # "ALTER TABLE ... ADD COLUMN". 15 # 16 17 set testdir [file dirname $argv0] 18 19 source $testdir/tester.tcl 20 21 # If SQLITE_OMIT_ALTERTABLE is defined, omit this file. 22 ifcapable !altertable { 23 finish_test 24 return 25 } 26 27 # Determine if there is a codec available on this test. 28 # 29 if {[catch {sqlite3 -has-codec} r] || $r} { 30 set has_codec 1 31 } else { 32 set has_codec 0 33 } 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 # 50 proc get_file_format {{fname test.db}} { 51 return [hexio_get_int [hexio_read $fname 44 4]] 52 } 53 54 do_test alter3-1.1 { 55 sqlite3_db_config db LEGACY_FILE_FORMAT 1 56 execsql { 57 CREATE TABLE abc(a, b, c); 58 SELECT sql FROM sqlite_master; 59 } 60 } {{CREATE TABLE abc(a, b, c)}} 61 do_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)}} 67 do_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)}} 73 do_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)}} 80 do_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))}} 86 ifcapable 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 } 95 do_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))}} 102 do_test alter3-1.99 { 103 catchsql { 104 # May not exist if foriegn-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 114 do_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}} 123 do_test alter3-2.2 { 124 catchsql { 125 ALTER TABLE t1 ADD c UNIQUE 126 } 127 } {1 {Cannot add a UNIQUE column}} 128 do_test alter3-2.3 { 129 catchsql { 130 ALTER TABLE t1 ADD b VARCHAR(10) 131 } 132 } {1 {duplicate column name: b}} 133 do_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}} 138 do_test alter3-2.4 { 139 catchsql { 140 ALTER TABLE t1 ADD c NOT NULL DEFAULT 10; 141 } 142 } {0 {}} 143 ifcapable 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 } 153 do_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}} 158 do_test alter3-2.99 { 159 execsql { 160 DROP TABLE t1; 161 } 162 } {} 163 164 do_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} 172 do_test alter3-3.1 { 173 execsql { 174 PRAGMA schema_version = 10; 175 } 176 } {} 177 do_test alter3-3.2 { 178 execsql { 179 ALTER TABLE t1 ADD c; 180 SELECT * FROM t1; 181 } 182 } {1 100 {} 2 300 {}} 183 if {!$has_codec} { 184 do_test alter3-3.3 { 185 get_file_format 186 } {3} 187 } 188 ifcapable schema_version { 189 do_test alter3-3.4 { 190 execsql { 191 PRAGMA schema_version; 192 } 193 } {11} 194 } 195 196 do_test alter3-4.1 { 197 db close 198 forcedelete test.db 199 set ::DB [sqlite3 db test.db] 200 sqlite3_db_config db LEGACY_FILE_FORMAT 1 201 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} 208 do_test alter3-4.1 { 209 execsql { 210 PRAGMA schema_version = 20; 211 } 212 } {} 213 do_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}} 219 if {!$has_codec} { 220 do_test alter3-4.3 { 221 get_file_format 222 } {3} 223 } 224 ifcapable schema_version { 225 do_test alter3-4.4 { 226 execsql { 227 PRAGMA schema_version; 228 } 229 } {21} 230 } 231 do_test alter3-4.99 { 232 execsql { 233 DROP TABLE t1; 234 } 235 } {} 236 237 ifcapable attach { 238 do_test alter3-5.1 { 239 forcedelete test2.db 240 forcedelete test2.db-journal 241 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 column 307 # is added to a table. 308 # 309 ifcapable 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 BEGIN 316 INSERT INTO log VALUES('a', new.a, new.b); 317 END; 318 CREATE TEMP TRIGGER t1_b AFTER INSERT ON t1 BEGIN 319 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 335 if {!$has_codec} { 336 ifcapable vacuum { 337 do_test alter3-7.1 { 338 execsql { 339 VACUUM; 340 } 341 get_file_format 342 } {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_format 349 } {3} 350 do_test alter3-7.3 { 351 execsql { 352 ALTER TABLE abc ADD e DEFAULT 10; 353 } 354 get_file_format 355 } {3} 356 do_test alter3-7.4 { 357 execsql { 358 ALTER TABLE abc ADD f DEFAULT NULL; 359 } 360 get_file_format 361 } {3} 362 do_test alter3-7.5 { 363 execsql { 364 VACUUM; 365 } 366 get_file_format 367 } {1} 368 } 369 } 370 371 # Ticket #1183 - Make sure adding columns to large tables does not cause 372 # memory corruption (as was the case before this bug was fixed). 373 do_test alter3-8.1 { 374 execsql { 375 CREATE TABLE t4(c1); 376 } 377 } {} 378 set ::sql "" 379 do_test alter3-8.2 { 380 set cols c1 381 for {set i 2} {$i < 100} {incr i} { 382 execsql " 383 ALTER TABLE t4 ADD c$i 384 " 385 lappend cols c$i 386 } 387 set ::sql "CREATE TABLE t4([join $cols {, }])" 388 list 389 } {} 390 do_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 constraint 397 # violations in ALTER TABLE ADD COLUMN 398 # 399 reset_db 400 do_execsql_test alter3-9.1 { 401 CREATE TABLE t1(a,b); 402 INSERT INTO t1 VALUES(1, 2), ('null!',NULL), (3,4); 403 } {} 404 do_catchsql_test alter3-9.2 { 405 ALTER TABLE t1 ADD COLUMN c CHECK(a!=1); 406 } {1 {CHECK constraint failed}} 407 do_catchsql_test alter3-9.3 { 408 ALTER TABLE t1 ADD COLUMN c CHECK(a!=3); 409 } {1 {CHECK constraint failed}} 410 do_catchsql_test alter3-9.4 { 411 ALTER TABLE t1 ADD COLUMN c CHECK(a!=2); 412 } {0 {}} 413 do_catchsql_test alter3-9.5 { 414 ALTER TABLE t1 ADD COLUMN d AS (b+1) NOT NULL; 415 } {1 {NOT NULL constraint failed}} 416 do_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}} 419 do_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 423 do_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 } {} 430 do_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}} 433 do_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}} 436 do_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}} 439 do_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 443 finish_test