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# $Id: alter3.test,v 1.4 2005/03/27 01:56:31 danielk1977 Exp $ 17# 18 19set testdir [file dirname $argv0] 20 21source $testdir/tester.tcl 22 23# If SQLITE_OMIT_ALTERTABLE is defined, omit this file. 24ifcapable !altertable { 25 finish_test 26 return 27} 28 29# Test Organisation: 30# ------------------ 31# 32# alter3-1.*: Test that ALTER TABLE correctly modifies the CREATE TABLE sql. 33# alter3-2.*: Test error messages. 34# alter3-3.*: Test adding columns with default value NULL. 35# alter3-4.*: Test adding columns with default values other than NULL. 36# alter3-5.*: Test adding columns to tables in ATTACHed databases. 37# alter3-6.*: Test that temp triggers are not accidentally dropped. 38# alter3-7.*: Test that VACUUM resets the file-format. 39# 40 41# This procedure returns the value of the file-format in file 'test.db'. 42# 43proc get_file_format {{fname test.db}} { 44 set bt [btree_open $fname 10 0] 45 set meta [btree_get_meta $bt] 46 btree_close $bt 47 lindex $meta 2 48} 49 50do_test alter3-1.1 { 51 execsql { 52 CREATE TABLE abc(a, b, c); 53 SELECT sql FROM sqlite_master; 54 } 55} {{CREATE TABLE abc(a, b, c)}} 56do_test alter3-1.2 { 57 execsql {ALTER TABLE abc ADD d INTEGER;} 58 execsql { 59 SELECT sql FROM sqlite_master; 60 } 61} {{CREATE TABLE abc(a, b, c, d INTEGER)}} 62do_test alter3-1.3 { 63 execsql {ALTER TABLE abc ADD e} 64 execsql { 65 SELECT sql FROM sqlite_master; 66 } 67} {{CREATE TABLE abc(a, b, c, d INTEGER, e)}} 68do_test alter3-1.4 { 69 execsql { 70 CREATE TABLE main.t1(a, b); 71 ALTER TABLE t1 ADD c; 72 SELECT sql FROM sqlite_master WHERE tbl_name = 't1'; 73 } 74} {{CREATE TABLE t1(a, b, c)}} 75do_test alter3-1.5 { 76 execsql { 77 ALTER TABLE t1 ADD d CHECK (a>d); 78 SELECT sql FROM sqlite_master WHERE tbl_name = 't1'; 79 } 80} {{CREATE TABLE t1(a, b, c, d CHECK (a>d))}} 81do_test alter3-1.6 { 82 execsql { 83 CREATE TABLE t2(a, b, UNIQUE(a, b)); 84 ALTER TABLE t2 ADD c REFERENCES t1(c) ; 85 SELECT sql FROM sqlite_master WHERE tbl_name = 't2' AND type = 'table'; 86 } 87} {{CREATE TABLE t2(a, b, c REFERENCES t1(c), UNIQUE(a, b))}} 88do_test alter3-1.7 { 89 execsql { 90 CREATE TABLE t3(a, b, UNIQUE(a, b)); 91 ALTER TABLE t3 ADD COLUMN c VARCHAR(10, 20); 92 SELECT sql FROM sqlite_master WHERE tbl_name = 't3' AND type = 'table'; 93 } 94} {{CREATE TABLE t3(a, b, c VARCHAR(10, 20), UNIQUE(a, b))}} 95do_test alter3-1.99 { 96 execsql { 97 DROP TABLE abc; 98 DROP TABLE t1; 99 DROP TABLE t2; 100 DROP TABLE t3; 101 } 102} {} 103 104do_test alter3-2.1 { 105 execsql { 106 CREATE TABLE t1(a, b); 107 } 108 catchsql { 109 ALTER TABLE t1 ADD c PRIMARY KEY; 110 } 111} {1 {Cannot add a PRIMARY KEY column}} 112do_test alter3-2.2 { 113 catchsql { 114 ALTER TABLE t1 ADD c UNIQUE 115 } 116} {1 {Cannot add a UNIQUE column}} 117do_test alter3-2.3 { 118 catchsql { 119 ALTER TABLE t1 ADD b VARCHAR(10) 120 } 121} {1 {duplicate column name: b}} 122do_test alter3-2.3 { 123 catchsql { 124 ALTER TABLE t1 ADD c NOT NULL; 125 } 126} {1 {Cannot add a NOT NULL column with default value NULL}} 127do_test alter3-2.4 { 128 catchsql { 129 ALTER TABLE t1 ADD c NOT NULL DEFAULT 10; 130 } 131} {0 {}} 132do_test alter3-2.5 { 133 execsql { 134 CREATE VIEW v1 AS SELECT * FROM t1; 135 } 136 catchsql { 137 alter table v1 add column d; 138 } 139} {1 {Cannot add a column to a view}} 140do_test alter3-2.6 { 141 catchsql { 142 alter table t1 add column d DEFAULT CURRENT_TIME; 143 } 144} {1 {Cannot add a column with non-constant default}} 145do_test alter3-2.99 { 146 execsql { 147 DROP TABLE t1; 148 } 149} {} 150 151do_test alter3-3.1 { 152 execsql { 153 CREATE TABLE t1(a, b); 154 INSERT INTO t1 VALUES(1, 100); 155 INSERT INTO t1 VALUES(2, 300); 156 SELECT * FROM t1; 157 } 158} {1 100 2 300} 159do_test alter3-3.1 { 160 execsql { 161 PRAGMA schema_version = 10; 162 } 163} {} 164do_test alter3-3.2 { 165 execsql { 166 ALTER TABLE t1 ADD c; 167 SELECT * FROM t1; 168 } 169} {1 100 {} 2 300 {}} 170do_test alter3-3.3 { 171 get_file_format 172} {3} 173do_test alter3-3.4 { 174 execsql { 175 PRAGMA schema_version; 176 } 177} {11} 178 179do_test alter3-4.1 { 180 db close 181 file delete -force test.db 182 set ::DB [sqlite3 db test.db] 183 execsql { 184 CREATE TABLE t1(a, b); 185 INSERT INTO t1 VALUES(1, 100); 186 INSERT INTO t1 VALUES(2, 300); 187 SELECT * FROM t1; 188 } 189} {1 100 2 300} 190do_test alter3-4.1 { 191 execsql { 192 PRAGMA schema_version = 20; 193 } 194} {} 195do_test alter3-4.2 { 196 execsql { 197 ALTER TABLE t1 ADD c DEFAULT 'hello world'; 198 SELECT * FROM t1; 199 } 200} {1 100 {hello world} 2 300 {hello world}} 201do_test alter3-4.3 { 202 get_file_format 203} {3} 204do_test alter3-4.4 { 205 execsql { 206 PRAGMA schema_version; 207 } 208} {21} 209do_test alter3-4.99 { 210 execsql { 211 DROP TABLE t1; 212 } 213} {} 214 215do_test alter3-5.1 { 216 file delete -force test2.db 217 file delete -force test2.db-journal 218 execsql { 219 CREATE TABLE t1(a, b); 220 INSERT INTO t1 VALUES(1, 'one'); 221 INSERT INTO t1 VALUES(2, 'two'); 222 ATTACH 'test2.db' AS aux; 223 CREATE TABLE aux.t1 AS SELECT * FROM t1; 224 PRAGMA aux.schema_version = 30; 225 SELECT sql FROM aux.sqlite_master; 226 } 227} {{CREATE TABLE t1(a,b)}} 228do_test alter3-5.2 { 229 execsql { 230 ALTER TABLE aux.t1 ADD COLUMN c VARCHAR(128); 231 SELECT sql FROM aux.sqlite_master; 232 } 233} {{CREATE TABLE t1(a,b, c VARCHAR(128))}} 234do_test alter3-5.3 { 235 execsql { 236 SELECT * FROM aux.t1; 237 } 238} {1 one {} 2 two {}} 239do_test alter3-5.4 { 240 execsql { 241 PRAGMA aux.schema_version; 242 } 243} {31} 244do_test alter3-5.5 { 245 list [get_file_format test2.db] [get_file_format] 246} {2 3} 247do_test alter3-5.6 { 248 execsql { 249 ALTER TABLE aux.t1 ADD COLUMN d DEFAULT 1000; 250 SELECT sql FROM aux.sqlite_master; 251 } 252} {{CREATE TABLE t1(a,b, c VARCHAR(128), d DEFAULT 1000)}} 253do_test alter3-5.7 { 254 execsql { 255 SELECT * FROM aux.t1; 256 } 257} {1 one {} 1000 2 two {} 1000} 258do_test alter3-5.8 { 259 execsql { 260 PRAGMA aux.schema_version; 261 } 262} {32} 263do_test alter3-5.9 { 264 execsql { 265 SELECT * FROM t1; 266 } 267} {1 one 2 two} 268do_test alter3-5.99 { 269 execsql { 270 DROP TABLE aux.t1; 271 DROP TABLE t1; 272 } 273} {} 274 275#---------------------------------------------------------------- 276# Test that the table schema is correctly reloaded when a column 277# is added to a table. 278# 279ifcapable trigger { 280 do_test alter3-6.1 { 281 execsql { 282 CREATE TABLE t1(a, b); 283 CREATE TABLE log(trig, a, b); 284 285 CREATE TRIGGER t1_a AFTER INSERT ON t1 BEGIN 286 INSERT INTO log VALUES('a', new.a, new.b); 287 END; 288 CREATE TEMP TRIGGER t1_b AFTER INSERT ON t1 BEGIN 289 INSERT INTO log VALUES('b', new.a, new.b); 290 END; 291 292 INSERT INTO t1 VALUES(1, 2); 293 SELECT * FROM log; 294 } 295 } {b 1 2 a 1 2} 296 do_test alter3-6.2 { 297 execsql { 298 ALTER TABLE t1 ADD COLUMN c DEFAULT 'c'; 299 INSERT INTO t1(a, b) VALUES(3, 4); 300 SELECT * FROM log; 301 } 302 } {b 1 2 a 1 2 b 3 4 a 3 4} 303} 304 305ifcapable vacuum { 306 do_test alter3-7.1 { 307 execsql { 308 VACUUM; 309 } 310 get_file_format 311 } {1} 312 do_test alter3-7.2 { 313 execsql { 314 CREATE TABLE abc(a, b, c); 315 ALTER TABLE abc ADD d DEFAULT NULL; 316 } 317 get_file_format 318 } {2} 319 do_test alter3-7.3 { 320 execsql { 321 ALTER TABLE abc ADD e DEFAULT 10; 322 } 323 get_file_format 324 } {3} 325 do_test alter3-7.4 { 326 execsql { 327 ALTER TABLE abc ADD f DEFAULT NULL; 328 } 329 get_file_format 330 } {3} 331 do_test alter3-7.5 { 332 execsql { 333 VACUUM; 334 } 335 get_file_format 336 } {1} 337} 338 339# Ticket #1183 - Make sure adding columns to large tables does not cause 340# memory corruption (as was the case before this bug was fixed). 341do_test alter3-8.1 { 342 execsql { 343 CREATE TABLE t4(c1); 344 } 345} {} 346do_test alter3-8.2 { 347 set cols c1 348 for {set i 2} {$i < 100} {incr i} { 349 execsql " 350 ALTER TABLE t4 ADD c$i 351 " 352 lappend cols c$i 353 } 354 set ::sql "CREATE TABLE t4([join $cols {, }])" 355 list 356} {} 357do_test alter3-8.2 { 358 execsql { 359 SELECT sql FROM sqlite_master WHERE name = 't4'; 360 } 361} [list $::sql] 362 363finish_test 364 365