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