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