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