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