gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/alter4.test (about) 1 # 2009 February 2 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 # $Id: alter4.test,v 1.1 2009/02/02 18:03:22 drh Exp $ 17 # 18 19 set testdir [file dirname $argv0] 20 21 source $testdir/tester.tcl 22 23 # If SQLITE_OMIT_ALTERTABLE is defined, omit this file. 24 ifcapable !altertable { 25 finish_test 26 return 27 } 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 42 do_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)}} 48 do_test alter4-1.1b { 49 execsql { 50 SELECT sql FROM temp.sqlite_master; 51 } 52 } {{CREATE TABLE abc(a, b, c)}} 53 do_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)}} 59 do_test alter4-1.2b { 60 execsql { 61 SELECT sql FROM temp.sqlite_master; 62 } 63 } {{CREATE TABLE abc(a, b, c, d INTEGER)}} 64 do_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)}} 70 do_test alter4-1.3b { 71 execsql { 72 SELECT sql FROM temp.sqlite_master; 73 } 74 } {{CREATE TABLE abc(a, b, c, d INTEGER, e)}} 75 do_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)}} 82 do_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)}} 87 do_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))}} 93 ifcapable 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_master 99 WHERE tbl_name = 't2' AND type = 'table'; 100 } 101 } {{CREATE TABLE t2(a, b, c REFERENCES t1(c), UNIQUE(a, b))}} 102 } 103 do_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_master 108 WHERE tbl_name = 't3' AND type = 'table'; 109 } 110 } {{CREATE TABLE t3(a, b, c VARCHAR(10, 20), UNIQUE(a, b))}} 111 do_test alter4-1.99 { 112 catchsql { 113 # May not exist if foriegn-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 123 do_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}} 132 do_test alter4-2.2 { 133 catchsql { 134 ALTER TABLE t1 ADD c UNIQUE 135 } 136 } {1 {Cannot add a UNIQUE column}} 137 do_test alter4-2.3 { 138 catchsql { 139 ALTER TABLE t1 ADD b VARCHAR(10) 140 } 141 } {1 {duplicate column name: b}} 142 do_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}} 147 do_test alter4-2.4 { 148 catchsql { 149 ALTER TABLE t1 ADD c NOT NULL DEFAULT 10; 150 } 151 } {0 {}} 152 ifcapable 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 } 162 do_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}} 167 do_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}} 172 do_test alter4-2.99 { 173 execsql { 174 DROP TABLE t1; 175 } 176 } {} 177 178 do_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} 186 do_test alter4-3.1 { 187 execsql { 188 PRAGMA schema_version = 10; 189 } 190 } {} 191 do_test alter4-3.2 { 192 execsql { 193 ALTER TABLE t1 ADD c; 194 SELECT * FROM t1; 195 } 196 } {1 100 {} 2 300 {}} 197 ifcapable schema_version { 198 do_test alter4-3.4 { 199 execsql { 200 PRAGMA schema_version; 201 } 202 } {10} 203 } 204 205 do_test alter4-4.1 { 206 db close 207 forcedelete test.db 208 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} 216 do_test alter4-4.1 { 217 execsql { 218 PRAGMA schema_version = 20; 219 } 220 } {} 221 do_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}} 227 ifcapable schema_version { 228 do_test alter4-4.4 { 229 execsql { 230 PRAGMA schema_version; 231 } 232 } {20} 233 } 234 do_test alter4-4.99 { 235 execsql { 236 DROP TABLE t1; 237 } 238 } {} 239 240 ifcapable attach { 241 do_test alter4-5.1 { 242 forcedelete test2.db 243 forcedelete test2.db-journal 244 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 column 305 # is added to a table. 306 # 307 ifcapable 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 BEGIN 314 INSERT INTO log VALUES('a', new.a, new.b); 315 END; 316 CREATE TEMP TRIGGER t1_b AFTER INSERT ON t1 BEGIN 317 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 cause 334 # memory corruption (as was the case before this bug was fixed). 335 do_test alter4-8.1 { 336 execsql { 337 CREATE TEMP TABLE t4(c1); 338 } 339 } {} 340 set ::sql "" 341 do_test alter4-8.2 { 342 set cols c1 343 for {set i 2} {$i < 100} {incr i} { 344 execsql " 345 ALTER TABLE t4 ADD c$i 346 " 347 lappend cols c$i 348 } 349 set ::sql "CREATE TABLE t4([join $cols {, }])" 350 list 351 } {} 352 do_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 possible 360 # 64-bit integer is correctly converted to a real. 361 do_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 369 do_execsql_test alter4-9.2 { SELECT typeof(a), a, typeof(b), b FROM t5; } { 370 integer -9223372036854775808 371 real 9.22337203685478e+18 372 } 373 374 do_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 database 380 # does not corrupt DESC indexes. 381 # 382 # Ticket https://www.sqlite.org/src/tktview/f68bf68513a1c 383 # 384 do_test alter4-10.1 { 385 db close 386 sqlite3 db :memory: 387 sqlite3_db_config db LEGACY_FILE_FORMAT 1 388 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 398 reset_db 399 do_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 406 do_execsql_test alter4-11.1 { 407 ALTER TABLE t1 ADD COLUMN f REFERENCES t1; 408 } 409 410 do_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 414 do_catchsql_test alter4-11.3 { 415 ALTER TABLE t2 ADD COLUMN g; 416 } {1 {no such table: t2}} 417 418 ifcapable 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 427 finish_test