#pragma ident "%Z%%M% %I% %E% SMI"
# 2002 July 17
#
# The author disclaims copyright to this source code. In place of
# a legal notice, here is a blessing:
#
# May you do good and not evil.
# May you find forgiveness for yourself and forgive others.
# May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library. The
# focus of this file is testing the ability of the library to detect
# past or future file format version numbers and respond appropriately.
#
# $Id: version.test,v 1.9 2004/02/12 19:01:05 drh Exp $
set testdir [file dirname $argv0]
source $testdir/tester.tcl
# Current file format version
set VX 4
# Create a new database
#
do_test version-1.1 {
execsql {
CREATE TABLE t1(x);
INSERT INTO t1 VALUES(1);
INSERT INTO t1 SELECT x+1 FROM t1;
INSERT INTO t1 SELECT x+2 FROM t1;
INSERT INTO t1 SELECT x+4 FROM t1;
SELECT * FROM t1;
}
} {1 2 3 4 5 6 7 8}
# Make sure the version number is set correctly
#
do_test version-1.2 {
db close
set ::bt [btree_open test.db]
btree_begin_transaction $::bt
set ::meta [btree_get_meta $::bt]
btree_rollback $::bt
lindex $::meta 2
} $VX
# Increase the file_format number by one. Verify that the
# file will refuse to open.
#
do_test version-1.3 {
set m2 [lreplace $::meta 2 2 [expr {$::VX+1}]]
btree_begin_transaction $::bt
eval btree_update_meta $::bt $m2
btree_commit $::bt
set rc [catch {sqlite db test.db} msg]
lappend rc $msg
} {1 {unsupported file format}}
# Decrease the file_format number by one. Verify that the
# file will open correctly.
#
do_test version-1.4 {
set m2 [lreplace $::meta 2 2 [expr {$::VX-1}]]
btree_begin_transaction $::bt
eval btree_update_meta $::bt $m2
btree_commit $::bt
sqlite db test.db
execsql {
SELECT * FROM t1;
}
} {1 2 3 4 5 6 7 8}
# Set the file_format number to 2. This should cause the automatic
# upgrade processing to run.
#
do_test version-1.5 {
set m2 [lreplace $::meta 2 2 2]
btree_begin_transaction $::bt
eval btree_update_meta $::bt $m2
btree_commit $::bt
sqlite db test.db
execsql {
SELECT * FROM t1;
}
} {1 2 3 4 5 6 7 8}
do_test version-1.6 {
set ::meta [btree_get_meta $::bt]
lindex $::meta 2
} $VX
# Add some triggers, views, and indices to the schema and make sure the
# automatic upgrade still works.
#
do_test version-1.7 {
execsql {
CREATE INDEX i1 ON t1(x);
DELETE FROM t1;
CREATE TABLE t2(a INTEGER PRIMARY KEY, b UNIQUE, c);
CREATE TABLE cnt(name,ins, del);
INSERT INTO cnt VALUES('t1',0,0);
INSERT INTO cnt VALUES('t2',0,0);
CREATE TRIGGER r1 AFTER INSERT ON t1 FOR EACH ROW BEGIN
UPDATE cnt SET ins=ins+1 WHERE name='t1';
END;
CREATE TRIGGER r2 AFTER DELETE ON t1 FOR EACH ROW BEGIN
UPDATE cnt SET del=del+1 WHERE name='t1';
END;
CREATE TRIGGER r3 AFTER INSERT ON t2 FOR EACH ROW BEGIN
UPDATE cnt SET ins=ins+1 WHERE name='t2';
END;
CREATE TRIGGER r4 AFTER DELETE ON t2 FOR EACH ROW BEGIN
UPDATE cnt SET del=del+1 WHERE name='t2';
END;
CREATE VIEW v1 AS SELECT x+100 FROM t1;
CREATE VIEW v2 AS SELECT sum(ins), sum(del) FROM cnt;
INSERT INTO t1 VALUES(1);
INSERT INTO t1 SELECT x+1 FROM t1;
INSERT INTO t1 SELECT x+2 FROM t1;
INSERT INTO t1 SELECT x+4 FROM t1;
SELECT * FROM t1;
}
} {1 2 3 4 5 6 7 8}
do_test version-1.8 {
execsql {
SELECT * FROM v2;
}
} {8 0}
do_test version-1.9 {
execsql {
SELECT * FROM cnt;
}
} {t1 8 0 t2 0 0}
do_test version-1.10 {
execsql {
INSERT INTO t2 SELECT x*3, x*2, x FROM t1;
SELECT * FROM t2;
}
} {3 2 1 6 4 2 9 6 3 12 8 4 15 10 5 18 12 6 21 14 7 24 16 8}
do_test version-1.11 {
execsql {
SELECT * FROM cnt;
}
} {t1 8 0 t2 8 0}
# Here we do the upgrade test.
#
do_test version-1.12 {
db close
set m2 [lreplace $::meta 2 2 2]
btree_begin_transaction $::bt
eval btree_update_meta $::bt $m2
btree_commit $::bt
sqlite db test.db
execsql {
SELECT * FROM cnt;
}
} {t1 8 0 t2 8 0}
do_test version-1.13 {
execsql {
SELECT * FROM v1;
}
} {101 102 103 104 105 106 107 108}
do_test version-1.14 {
execsql {
SELECT * FROM v2;
}
} {16 0}
# Try to do an upgrade where the database file is read-only
#
do_test version-2.1 {
db close
set m2 [lreplace $::meta 2 2 2]
btree_begin_transaction $::bt
eval btree_update_meta $::bt $m2
btree_commit $::bt
btree_close $::bt
catch {file attributes test.db -permissions 0444}
catch {file attributes test.db -readonly 1}
if {[file writable test.db]} {
error "Unable to make the database file test.db readonly - rerun this test as an unprivileged user"
}
set rc [catch {sqlite db test.db} msg]
lappend rc $msg
} {1 {unable to upgrade database to the version 2.6 format: attempt to write a readonly database}}
do_test version-2.2 {
file delete -force test.db
set fd [open test.db w]
set txt "This is not a valid database file\n"
while {[string length $txt]<4092} {append txt $txt}
puts $fd $txt
close $fd
set rc [catch {sqlite db test.db} msg]
lappend rc $msg
} {1 {file is encrypted or is not a database}}
finish_test