1*1da57d55SToomas Soome# 27c478bd9Sstevel@tonic-gate# 2002 July 17 37c478bd9Sstevel@tonic-gate# 47c478bd9Sstevel@tonic-gate# The author disclaims copyright to this source code. In place of 57c478bd9Sstevel@tonic-gate# a legal notice, here is a blessing: 67c478bd9Sstevel@tonic-gate# 77c478bd9Sstevel@tonic-gate# May you do good and not evil. 87c478bd9Sstevel@tonic-gate# May you find forgiveness for yourself and forgive others. 97c478bd9Sstevel@tonic-gate# May you share freely, never taking more than you give. 107c478bd9Sstevel@tonic-gate# 117c478bd9Sstevel@tonic-gate#*********************************************************************** 127c478bd9Sstevel@tonic-gate# This file implements regression tests for SQLite library. The 137c478bd9Sstevel@tonic-gate# focus of this file is testing the ability of the library to detect 147c478bd9Sstevel@tonic-gate# past or future file format version numbers and respond appropriately. 157c478bd9Sstevel@tonic-gate# 167c478bd9Sstevel@tonic-gate# $Id: version.test,v 1.9 2004/02/12 19:01:05 drh Exp $ 177c478bd9Sstevel@tonic-gate 187c478bd9Sstevel@tonic-gateset testdir [file dirname $argv0] 197c478bd9Sstevel@tonic-gatesource $testdir/tester.tcl 207c478bd9Sstevel@tonic-gate 217c478bd9Sstevel@tonic-gate# Current file format version 227c478bd9Sstevel@tonic-gateset VX 4 237c478bd9Sstevel@tonic-gate 247c478bd9Sstevel@tonic-gate# Create a new database 257c478bd9Sstevel@tonic-gate# 267c478bd9Sstevel@tonic-gatedo_test version-1.1 { 277c478bd9Sstevel@tonic-gate execsql { 287c478bd9Sstevel@tonic-gate CREATE TABLE t1(x); 297c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(1); 307c478bd9Sstevel@tonic-gate INSERT INTO t1 SELECT x+1 FROM t1; 317c478bd9Sstevel@tonic-gate INSERT INTO t1 SELECT x+2 FROM t1; 327c478bd9Sstevel@tonic-gate INSERT INTO t1 SELECT x+4 FROM t1; 337c478bd9Sstevel@tonic-gate SELECT * FROM t1; 347c478bd9Sstevel@tonic-gate } 357c478bd9Sstevel@tonic-gate} {1 2 3 4 5 6 7 8} 367c478bd9Sstevel@tonic-gate 377c478bd9Sstevel@tonic-gate# Make sure the version number is set correctly 387c478bd9Sstevel@tonic-gate# 397c478bd9Sstevel@tonic-gatedo_test version-1.2 { 407c478bd9Sstevel@tonic-gate db close 417c478bd9Sstevel@tonic-gate set ::bt [btree_open test.db] 427c478bd9Sstevel@tonic-gate btree_begin_transaction $::bt 437c478bd9Sstevel@tonic-gate set ::meta [btree_get_meta $::bt] 447c478bd9Sstevel@tonic-gate btree_rollback $::bt 457c478bd9Sstevel@tonic-gate lindex $::meta 2 467c478bd9Sstevel@tonic-gate} $VX 477c478bd9Sstevel@tonic-gate 487c478bd9Sstevel@tonic-gate# Increase the file_format number by one. Verify that the 497c478bd9Sstevel@tonic-gate# file will refuse to open. 507c478bd9Sstevel@tonic-gate# 517c478bd9Sstevel@tonic-gatedo_test version-1.3 { 527c478bd9Sstevel@tonic-gate set m2 [lreplace $::meta 2 2 [expr {$::VX+1}]] 537c478bd9Sstevel@tonic-gate btree_begin_transaction $::bt 547c478bd9Sstevel@tonic-gate eval btree_update_meta $::bt $m2 557c478bd9Sstevel@tonic-gate btree_commit $::bt 567c478bd9Sstevel@tonic-gate set rc [catch {sqlite db test.db} msg] 577c478bd9Sstevel@tonic-gate lappend rc $msg 587c478bd9Sstevel@tonic-gate} {1 {unsupported file format}} 597c478bd9Sstevel@tonic-gate 607c478bd9Sstevel@tonic-gate# Decrease the file_format number by one. Verify that the 617c478bd9Sstevel@tonic-gate# file will open correctly. 627c478bd9Sstevel@tonic-gate# 637c478bd9Sstevel@tonic-gatedo_test version-1.4 { 647c478bd9Sstevel@tonic-gate set m2 [lreplace $::meta 2 2 [expr {$::VX-1}]] 657c478bd9Sstevel@tonic-gate btree_begin_transaction $::bt 667c478bd9Sstevel@tonic-gate eval btree_update_meta $::bt $m2 677c478bd9Sstevel@tonic-gate btree_commit $::bt 687c478bd9Sstevel@tonic-gate sqlite db test.db 697c478bd9Sstevel@tonic-gate execsql { 707c478bd9Sstevel@tonic-gate SELECT * FROM t1; 717c478bd9Sstevel@tonic-gate } 727c478bd9Sstevel@tonic-gate} {1 2 3 4 5 6 7 8} 737c478bd9Sstevel@tonic-gate 747c478bd9Sstevel@tonic-gate# Set the file_format number to 2. This should cause the automatic 757c478bd9Sstevel@tonic-gate# upgrade processing to run. 767c478bd9Sstevel@tonic-gate# 777c478bd9Sstevel@tonic-gatedo_test version-1.5 { 787c478bd9Sstevel@tonic-gate set m2 [lreplace $::meta 2 2 2] 797c478bd9Sstevel@tonic-gate btree_begin_transaction $::bt 807c478bd9Sstevel@tonic-gate eval btree_update_meta $::bt $m2 817c478bd9Sstevel@tonic-gate btree_commit $::bt 827c478bd9Sstevel@tonic-gate sqlite db test.db 837c478bd9Sstevel@tonic-gate execsql { 847c478bd9Sstevel@tonic-gate SELECT * FROM t1; 857c478bd9Sstevel@tonic-gate } 867c478bd9Sstevel@tonic-gate} {1 2 3 4 5 6 7 8} 877c478bd9Sstevel@tonic-gatedo_test version-1.6 { 887c478bd9Sstevel@tonic-gate set ::meta [btree_get_meta $::bt] 897c478bd9Sstevel@tonic-gate lindex $::meta 2 907c478bd9Sstevel@tonic-gate} $VX 917c478bd9Sstevel@tonic-gate 927c478bd9Sstevel@tonic-gate# Add some triggers, views, and indices to the schema and make sure the 937c478bd9Sstevel@tonic-gate# automatic upgrade still works. 947c478bd9Sstevel@tonic-gate# 957c478bd9Sstevel@tonic-gatedo_test version-1.7 { 967c478bd9Sstevel@tonic-gate execsql { 977c478bd9Sstevel@tonic-gate CREATE INDEX i1 ON t1(x); 987c478bd9Sstevel@tonic-gate DELETE FROM t1; 997c478bd9Sstevel@tonic-gate CREATE TABLE t2(a INTEGER PRIMARY KEY, b UNIQUE, c); 1007c478bd9Sstevel@tonic-gate CREATE TABLE cnt(name,ins, del); 1017c478bd9Sstevel@tonic-gate INSERT INTO cnt VALUES('t1',0,0); 1027c478bd9Sstevel@tonic-gate INSERT INTO cnt VALUES('t2',0,0); 1037c478bd9Sstevel@tonic-gate CREATE TRIGGER r1 AFTER INSERT ON t1 FOR EACH ROW BEGIN 1047c478bd9Sstevel@tonic-gate UPDATE cnt SET ins=ins+1 WHERE name='t1'; 1057c478bd9Sstevel@tonic-gate END; 1067c478bd9Sstevel@tonic-gate CREATE TRIGGER r2 AFTER DELETE ON t1 FOR EACH ROW BEGIN 1077c478bd9Sstevel@tonic-gate UPDATE cnt SET del=del+1 WHERE name='t1'; 1087c478bd9Sstevel@tonic-gate END; 1097c478bd9Sstevel@tonic-gate CREATE TRIGGER r3 AFTER INSERT ON t2 FOR EACH ROW BEGIN 1107c478bd9Sstevel@tonic-gate UPDATE cnt SET ins=ins+1 WHERE name='t2'; 1117c478bd9Sstevel@tonic-gate END; 1127c478bd9Sstevel@tonic-gate CREATE TRIGGER r4 AFTER DELETE ON t2 FOR EACH ROW BEGIN 1137c478bd9Sstevel@tonic-gate UPDATE cnt SET del=del+1 WHERE name='t2'; 1147c478bd9Sstevel@tonic-gate END; 1157c478bd9Sstevel@tonic-gate CREATE VIEW v1 AS SELECT x+100 FROM t1; 1167c478bd9Sstevel@tonic-gate CREATE VIEW v2 AS SELECT sum(ins), sum(del) FROM cnt; 1177c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(1); 1187c478bd9Sstevel@tonic-gate INSERT INTO t1 SELECT x+1 FROM t1; 1197c478bd9Sstevel@tonic-gate INSERT INTO t1 SELECT x+2 FROM t1; 1207c478bd9Sstevel@tonic-gate INSERT INTO t1 SELECT x+4 FROM t1; 1217c478bd9Sstevel@tonic-gate SELECT * FROM t1; 1227c478bd9Sstevel@tonic-gate } 1237c478bd9Sstevel@tonic-gate} {1 2 3 4 5 6 7 8} 1247c478bd9Sstevel@tonic-gatedo_test version-1.8 { 1257c478bd9Sstevel@tonic-gate execsql { 1267c478bd9Sstevel@tonic-gate SELECT * FROM v2; 1277c478bd9Sstevel@tonic-gate } 1287c478bd9Sstevel@tonic-gate} {8 0} 1297c478bd9Sstevel@tonic-gatedo_test version-1.9 { 1307c478bd9Sstevel@tonic-gate execsql { 1317c478bd9Sstevel@tonic-gate SELECT * FROM cnt; 1327c478bd9Sstevel@tonic-gate } 1337c478bd9Sstevel@tonic-gate} {t1 8 0 t2 0 0} 1347c478bd9Sstevel@tonic-gatedo_test version-1.10 { 1357c478bd9Sstevel@tonic-gate execsql { 1367c478bd9Sstevel@tonic-gate INSERT INTO t2 SELECT x*3, x*2, x FROM t1; 1377c478bd9Sstevel@tonic-gate SELECT * FROM t2; 1387c478bd9Sstevel@tonic-gate } 1397c478bd9Sstevel@tonic-gate} {3 2 1 6 4 2 9 6 3 12 8 4 15 10 5 18 12 6 21 14 7 24 16 8} 1407c478bd9Sstevel@tonic-gatedo_test version-1.11 { 1417c478bd9Sstevel@tonic-gate execsql { 1427c478bd9Sstevel@tonic-gate SELECT * FROM cnt; 1437c478bd9Sstevel@tonic-gate } 1447c478bd9Sstevel@tonic-gate} {t1 8 0 t2 8 0} 1457c478bd9Sstevel@tonic-gate 1467c478bd9Sstevel@tonic-gate# Here we do the upgrade test. 1477c478bd9Sstevel@tonic-gate# 1487c478bd9Sstevel@tonic-gatedo_test version-1.12 { 1497c478bd9Sstevel@tonic-gate db close 1507c478bd9Sstevel@tonic-gate set m2 [lreplace $::meta 2 2 2] 1517c478bd9Sstevel@tonic-gate btree_begin_transaction $::bt 1527c478bd9Sstevel@tonic-gate eval btree_update_meta $::bt $m2 1537c478bd9Sstevel@tonic-gate btree_commit $::bt 1547c478bd9Sstevel@tonic-gate sqlite db test.db 1557c478bd9Sstevel@tonic-gate execsql { 1567c478bd9Sstevel@tonic-gate SELECT * FROM cnt; 1577c478bd9Sstevel@tonic-gate } 1587c478bd9Sstevel@tonic-gate} {t1 8 0 t2 8 0} 1597c478bd9Sstevel@tonic-gatedo_test version-1.13 { 1607c478bd9Sstevel@tonic-gate execsql { 1617c478bd9Sstevel@tonic-gate SELECT * FROM v1; 1627c478bd9Sstevel@tonic-gate } 1637c478bd9Sstevel@tonic-gate} {101 102 103 104 105 106 107 108} 1647c478bd9Sstevel@tonic-gatedo_test version-1.14 { 1657c478bd9Sstevel@tonic-gate execsql { 1667c478bd9Sstevel@tonic-gate SELECT * FROM v2; 1677c478bd9Sstevel@tonic-gate } 1687c478bd9Sstevel@tonic-gate} {16 0} 1697c478bd9Sstevel@tonic-gate 1707c478bd9Sstevel@tonic-gate# Try to do an upgrade where the database file is read-only 1717c478bd9Sstevel@tonic-gate# 1727c478bd9Sstevel@tonic-gatedo_test version-2.1 { 1737c478bd9Sstevel@tonic-gate db close 1747c478bd9Sstevel@tonic-gate set m2 [lreplace $::meta 2 2 2] 1757c478bd9Sstevel@tonic-gate btree_begin_transaction $::bt 1767c478bd9Sstevel@tonic-gate eval btree_update_meta $::bt $m2 1777c478bd9Sstevel@tonic-gate btree_commit $::bt 1787c478bd9Sstevel@tonic-gate btree_close $::bt 1797c478bd9Sstevel@tonic-gate catch {file attributes test.db -permissions 0444} 1807c478bd9Sstevel@tonic-gate catch {file attributes test.db -readonly 1} 1817c478bd9Sstevel@tonic-gate if {[file writable test.db]} { 1827c478bd9Sstevel@tonic-gate error "Unable to make the database file test.db readonly - rerun this test as an unprivileged user" 1837c478bd9Sstevel@tonic-gate } 1847c478bd9Sstevel@tonic-gate set rc [catch {sqlite db test.db} msg] 1857c478bd9Sstevel@tonic-gate lappend rc $msg 1867c478bd9Sstevel@tonic-gate} {1 {unable to upgrade database to the version 2.6 format: attempt to write a readonly database}} 1877c478bd9Sstevel@tonic-gatedo_test version-2.2 { 1887c478bd9Sstevel@tonic-gate file delete -force test.db 1897c478bd9Sstevel@tonic-gate set fd [open test.db w] 1907c478bd9Sstevel@tonic-gate set txt "This is not a valid database file\n" 1917c478bd9Sstevel@tonic-gate while {[string length $txt]<4092} {append txt $txt} 1927c478bd9Sstevel@tonic-gate puts $fd $txt 1937c478bd9Sstevel@tonic-gate close $fd 1947c478bd9Sstevel@tonic-gate set rc [catch {sqlite db test.db} msg] 1957c478bd9Sstevel@tonic-gate lappend rc $msg 1967c478bd9Sstevel@tonic-gate} {1 {file is encrypted or is not a database}} 1977c478bd9Sstevel@tonic-gate 1987c478bd9Sstevel@tonic-gate 1997c478bd9Sstevel@tonic-gatefinish_test 200