1# 2005 February 18 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: alter2.test,v 1.1 2005/02/19 08:18:06 danielk1977 Exp $ 17# 18 19set testdir [file dirname $argv0] 20source $testdir/tester.tcl 21 22# The file format change affects the way row-records stored in tables (but 23# not indices) are interpreted. Before version 3.1.3, a row-record for a 24# table with N columns was guaranteed to contain exactly N fields. As 25# of version 3.1.3, the record may contain up to N fields. In this case 26# the M fields that are present are the values for the left-most M 27# columns. The (N-M) rightmost columns contain NULL. 28# 29# If any records in the database contain less fields than their table 30# has columns, then the file-format meta value should be set to (at least) 2. 31# 32 33# This procedure sets the value of the file-format in file 'test.db' 34# to $newval. Also, the schema cookie is incremented. 35# 36proc set_file_format {newval} { 37 set bt [btree_open test.db 10 0] 38 btree_begin_transaction $bt 39 set meta [btree_get_meta $bt] 40 lset meta 2 $newval ;# File format 41 lset meta 1 [expr [lindex $meta 1]+1] ;# Schema cookie 42 eval "btree_update_meta $bt $meta" 43 btree_commit $bt 44 btree_close $bt 45} 46 47# This procedure returns the value of the file-format in file 'test.db'. 48# 49proc get_file_format {{fname test.db}} { 50 set bt [btree_open $fname 10 0] 51 set meta [btree_get_meta $bt] 52 btree_close $bt 53 lindex $meta 2 54} 55 56# This procedure sets the SQL statement stored for table $tbl in the 57# sqlite_master table of file 'test.db' to $sql. 58# 59proc alter_table {tbl sql} { 60 sqlite3 dbat test.db 61 dbat eval { 62 PRAGMA writable_schema = 1; 63 UPDATE sqlite_master SET sql = $sql WHERE name = $tbl AND type = 'table'; 64 PRAGMA writable_schema = 0; 65 } 66 dbat close 67 set_file_format 2 68} 69 70#----------------------------------------------------------------------- 71# Some basic tests to make sure short rows are handled. 72# 73do_test alter2-1.1 { 74 execsql { 75 CREATE TABLE abc(a, b); 76 INSERT INTO abc VALUES(1, 2); 77 INSERT INTO abc VALUES(3, 4); 78 INSERT INTO abc VALUES(5, 6); 79 } 80} {} 81do_test alter2-1.2 { 82 # ALTER TABLE abc ADD COLUMN c; 83 alter_table abc {CREATE TABLE abc(a, b, c);} 84} {} 85do_test alter2-1.3 { 86 execsql { 87 SELECT * FROM abc; 88 } 89} {1 2 {} 3 4 {} 5 6 {}} 90do_test alter2-1.4 { 91 execsql { 92 UPDATE abc SET c = 10 WHERE a = 1; 93 SELECT * FROM abc; 94 } 95} {1 2 10 3 4 {} 5 6 {}} 96do_test alter2-1.5 { 97 execsql { 98 CREATE INDEX abc_i ON abc(c); 99 } 100} {} 101do_test alter2-1.6 { 102 execsql { 103 SELECT c FROM abc ORDER BY c; 104 } 105} {{} {} 10} 106do_test alter2-1.7 { 107 execsql { 108 SELECT * FROM abc WHERE c = 10; 109 } 110} {1 2 10} 111do_test alter2-1.8 { 112 execsql { 113 SELECT sum(a), c FROM abc GROUP BY c; 114 } 115} {8.0 {} 1.0 10} 116do_test alter2-1.9 { 117 # ALTER TABLE abc ADD COLUMN d; 118 alter_table abc {CREATE TABLE abc(a, b, c, d);} 119 execsql { SELECT * FROM abc; } 120 execsql { 121 UPDATE abc SET d = 11 WHERE c IS NULL AND a<4; 122 SELECT * FROM abc; 123 } 124} {1 2 10 {} 3 4 {} 11 5 6 {} {}} 125do_test alter2-1.10 { 126 execsql { 127 SELECT typeof(d) FROM abc; 128 } 129} {null integer null} 130do_test alter2-1.99 { 131 execsql { 132 DROP TABLE abc; 133 } 134} {} 135 136#----------------------------------------------------------------------- 137# Test that views work when the underlying table structure is changed. 138# 139ifcapable view { 140 do_test alter2-2.1 { 141 execsql { 142 CREATE TABLE abc2(a, b, c); 143 INSERT INTO abc2 VALUES(1, 2, 10); 144 INSERT INTO abc2 VALUES(3, 4, NULL); 145 INSERT INTO abc2 VALUES(5, 6, NULL); 146 CREATE VIEW abc2_v AS SELECT * FROM abc2; 147 SELECT * FROM abc2_v; 148 } 149 } {1 2 10 3 4 {} 5 6 {}} 150 do_test alter2-2.2 { 151 # ALTER TABLE abc ADD COLUMN d; 152 alter_table abc2 {CREATE TABLE abc2(a, b, c, d);} 153 execsql { 154 SELECT * FROM abc2_v; 155 } 156 } {1 2 10 {} 3 4 {} {} 5 6 {} {}} 157 do_test alter2-2.3 { 158 execsql { 159 DROP TABLE abc2; 160 DROP VIEW abc2_v; 161 } 162 } {} 163} 164 165#----------------------------------------------------------------------- 166# Test that triggers work when a short row is copied to the old.* 167# trigger pseudo-table. 168# 169ifcapable trigger { 170 do_test alter2-3.1 { 171 execsql { 172 CREATE TABLE abc3(a, b); 173 CREATE TABLE blog(o, n); 174 CREATE TRIGGER abc3_t AFTER UPDATE OF b ON abc3 BEGIN 175 INSERT INTO blog VALUES(old.b, new.b); 176 END; 177 } 178 } {} 179 do_test alter2-3.2 { 180 execsql { 181 INSERT INTO abc3 VALUES(1, 4); 182 UPDATE abc3 SET b = 2 WHERE b = 4; 183 SELECT * FROM blog; 184 } 185 } {4 2} 186 do_test alter2-3.3 { 187 execsql { 188 INSERT INTO abc3 VALUES(3, 4); 189 INSERT INTO abc3 VALUES(5, 6); 190 } 191 alter_table abc3 {CREATE TABLE abc3(a, b, c);} 192 execsql { 193 SELECT * FROM abc3; 194 } 195 } {1 2 {} 3 4 {} 5 6 {}} 196 do_test alter2-3.4 { 197 execsql { 198 UPDATE abc3 SET b = b*2 WHERE a<4; 199 SELECT * FROM abc3; 200 } 201 } {1 4 {} 3 8 {} 5 6 {}} 202 do_test alter2-3.5 { 203 execsql { 204 SELECT * FROM blog; 205 } 206 } {4 2 2 4 4 8} 207 208 do_test alter2-3.6 { 209 execsql { 210 CREATE TABLE clog(o, n); 211 CREATE TRIGGER abc3_t2 AFTER UPDATE OF c ON abc3 BEGIN 212 INSERT INTO clog VALUES(old.c, new.c); 213 END; 214 UPDATE abc3 SET c = a*2; 215 SELECT * FROM clog; 216 } 217 } {{} 2 {} 6 {} 10} 218} 219 220#--------------------------------------------------------------------- 221# Check that an error occurs if the database is upgraded to a file 222# format that SQLite does not support (in this case 3). Note: The 223# file format is checked each time the schema is read, so changing the 224# file format requires incrementing the schema cookie. 225# 226do_test alter2-4.1 { 227 set_file_format 3 228} {} 229do_test alter2-4.2 { 230 catchsql { 231 SELECT * FROM sqlite_master; 232 } 233} {1 {unsupported file format}} 234do_test alter2-4.3 { 235 sqlite3_errcode $::DB 236} {SQLITE_ERROR} 237do_test alter2-4.4 { 238 db close 239 set ::DB [sqlite3 db test.db] 240 catchsql { 241 SELECT * FROM sqlite_master; 242 } 243} {1 {unsupported file format}} 244do_test alter2-4.5 { 245 sqlite3_errcode $::DB 246} {SQLITE_ERROR} 247 248#--------------------------------------------------------------------- 249# Check that executing VACUUM on a file with file-format version 2 250# resets the file format to 1. 251# 252do_test alter2-5.1 { 253 set_file_format 2 254 get_file_format 255} {2} 256do_test alter2-5.2 { 257 execsql { 258 VACUUM; 259 } 260} {} 261do_test alter2-5.3 { 262 get_file_format 263} {1} 264 265#--------------------------------------------------------------------- 266# Test that when a database with file-format 2 is opened, new 267# databases are still created with file-format 1. 268# 269do_test alter2-6.1 { 270 db close 271 set_file_format 2 272 set ::DB [sqlite3 db test.db] 273 get_file_format 274} {2} 275do_test alter2-6.2 { 276 file delete -force test2.db-journal 277 file delete -force test2.db 278 execsql { 279 ATTACH 'test2.db' AS aux; 280 CREATE TABLE aux.t1(a, b); 281 } 282 get_file_format test2.db 283} {1} 284do_test alter2-6.3 { 285 execsql { 286 CREATE TABLE t1(a, b); 287 } 288 get_file_format 289} {2} 290 291finish_test 292 293