libsqlite/test/version.test

#
# 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