1# 2002 July 17 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 file is testing the ability of the library to detect 13# past or future file format version numbers and respond appropriately. 14# 15# $Id: version.test,v 1.9 2004/02/12 19:01:05 drh Exp $ 16 17set testdir [file dirname $argv0] 18source $testdir/tester.tcl 19 20# Current file format version 21set VX 4 22 23# Create a new database 24# 25do_test version-1.1 { 26 execsql { 27 CREATE TABLE t1(x); 28 INSERT INTO t1 VALUES(1); 29 INSERT INTO t1 SELECT x+1 FROM t1; 30 INSERT INTO t1 SELECT x+2 FROM t1; 31 INSERT INTO t1 SELECT x+4 FROM t1; 32 SELECT * FROM t1; 33 } 34} {1 2 3 4 5 6 7 8} 35 36# Make sure the version number is set correctly 37# 38do_test version-1.2 { 39 db close 40 set ::bt [btree_open test.db] 41 btree_begin_transaction $::bt 42 set ::meta [btree_get_meta $::bt] 43 btree_rollback $::bt 44 lindex $::meta 2 45} $VX 46 47# Increase the file_format number by one. Verify that the 48# file will refuse to open. 49# 50do_test version-1.3 { 51 set m2 [lreplace $::meta 2 2 [expr {$::VX+1}]] 52 btree_begin_transaction $::bt 53 eval btree_update_meta $::bt $m2 54 btree_commit $::bt 55 set rc [catch {sqlite db test.db} msg] 56 lappend rc $msg 57} {1 {unsupported file format}} 58 59# Decrease the file_format number by one. Verify that the 60# file will open correctly. 61# 62do_test version-1.4 { 63 set m2 [lreplace $::meta 2 2 [expr {$::VX-1}]] 64 btree_begin_transaction $::bt 65 eval btree_update_meta $::bt $m2 66 btree_commit $::bt 67 sqlite db test.db 68 execsql { 69 SELECT * FROM t1; 70 } 71} {1 2 3 4 5 6 7 8} 72 73# Set the file_format number to 2. This should cause the automatic 74# upgrade processing to run. 75# 76do_test version-1.5 { 77 set m2 [lreplace $::meta 2 2 2] 78 btree_begin_transaction $::bt 79 eval btree_update_meta $::bt $m2 80 btree_commit $::bt 81 sqlite db test.db 82 execsql { 83 SELECT * FROM t1; 84 } 85} {1 2 3 4 5 6 7 8} 86do_test version-1.6 { 87 set ::meta [btree_get_meta $::bt] 88 lindex $::meta 2 89} $VX 90 91# Add some triggers, views, and indices to the schema and make sure the 92# automatic upgrade still works. 93# 94do_test version-1.7 { 95 execsql { 96 CREATE INDEX i1 ON t1(x); 97 DELETE FROM t1; 98 CREATE TABLE t2(a INTEGER PRIMARY KEY, b UNIQUE, c); 99 CREATE TABLE cnt(name,ins, del); 100 INSERT INTO cnt VALUES('t1',0,0); 101 INSERT INTO cnt VALUES('t2',0,0); 102 CREATE TRIGGER r1 AFTER INSERT ON t1 FOR EACH ROW BEGIN 103 UPDATE cnt SET ins=ins+1 WHERE name='t1'; 104 END; 105 CREATE TRIGGER r2 AFTER DELETE ON t1 FOR EACH ROW BEGIN 106 UPDATE cnt SET del=del+1 WHERE name='t1'; 107 END; 108 CREATE TRIGGER r3 AFTER INSERT ON t2 FOR EACH ROW BEGIN 109 UPDATE cnt SET ins=ins+1 WHERE name='t2'; 110 END; 111 CREATE TRIGGER r4 AFTER DELETE ON t2 FOR EACH ROW BEGIN 112 UPDATE cnt SET del=del+1 WHERE name='t2'; 113 END; 114 CREATE VIEW v1 AS SELECT x+100 FROM t1; 115 CREATE VIEW v2 AS SELECT sum(ins), sum(del) FROM cnt; 116 INSERT INTO t1 VALUES(1); 117 INSERT INTO t1 SELECT x+1 FROM t1; 118 INSERT INTO t1 SELECT x+2 FROM t1; 119 INSERT INTO t1 SELECT x+4 FROM t1; 120 SELECT * FROM t1; 121 } 122} {1 2 3 4 5 6 7 8} 123do_test version-1.8 { 124 execsql { 125 SELECT * FROM v2; 126 } 127} {8 0} 128do_test version-1.9 { 129 execsql { 130 SELECT * FROM cnt; 131 } 132} {t1 8 0 t2 0 0} 133do_test version-1.10 { 134 execsql { 135 INSERT INTO t2 SELECT x*3, x*2, x FROM t1; 136 SELECT * FROM t2; 137 } 138} {3 2 1 6 4 2 9 6 3 12 8 4 15 10 5 18 12 6 21 14 7 24 16 8} 139do_test version-1.11 { 140 execsql { 141 SELECT * FROM cnt; 142 } 143} {t1 8 0 t2 8 0} 144 145# Here we do the upgrade test. 146# 147do_test version-1.12 { 148 db close 149 set m2 [lreplace $::meta 2 2 2] 150 btree_begin_transaction $::bt 151 eval btree_update_meta $::bt $m2 152 btree_commit $::bt 153 sqlite db test.db 154 execsql { 155 SELECT * FROM cnt; 156 } 157} {t1 8 0 t2 8 0} 158do_test version-1.13 { 159 execsql { 160 SELECT * FROM v1; 161 } 162} {101 102 103 104 105 106 107 108} 163do_test version-1.14 { 164 execsql { 165 SELECT * FROM v2; 166 } 167} {16 0} 168 169# Try to do an upgrade where the database file is read-only 170# 171do_test version-2.1 { 172 db close 173 set m2 [lreplace $::meta 2 2 2] 174 btree_begin_transaction $::bt 175 eval btree_update_meta $::bt $m2 176 btree_commit $::bt 177 btree_close $::bt 178 catch {file attributes test.db -permissions 0444} 179 catch {file attributes test.db -readonly 1} 180 if {[file writable test.db]} { 181 error "Unable to make the database file test.db readonly - rerun this test as an unprivileged user" 182 } 183 set rc [catch {sqlite db test.db} msg] 184 lappend rc $msg 185} {1 {unable to upgrade database to the version 2.6 format: attempt to write a readonly database}} 186do_test version-2.2 { 187 file delete -force test.db 188 set fd [open test.db w] 189 set txt "This is not a valid database file\n" 190 while {[string length $txt]<4092} {append txt $txt} 191 puts $fd $txt 192 close $fd 193 set rc [catch {sqlite db test.db} msg] 194 lappend rc $msg 195} {1 {file is encrypted or is not a database}} 196 197 198finish_test 199