1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
|
#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
|
