xref: /sqlite-3.40.0/test/pager1.test (revision 153eda0a)

# 2010 June 15
#
# 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.
#
#***********************************************************************
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
source $testdir/lock_common.tcl
source $testdir/malloc_common.tcl
source $testdir/wal_common.tcl

#
# pager1-1.*: Test inter-process locking (clients in multiple processes).
#
# pager1-2.*: Test intra-process locking (multiple clients in this process).
#
# pager1-3.*: Savepoint related tests.
#
# pager1-4.*: Hot-journal related tests.
#
# pager1-5.*: Cases related to multi-file commits.
#
# pager1-6.*: Cases related to "PRAGMA max_page_count"
#
# pager1-7.*: Cases specific to "PRAGMA journal_mode=TRUNCATE"
#

set a_string_counter 1
proc a_string {n} {
  global a_string_counter
  incr a_string_counter
  string range [string repeat "${a_string_counter}." $n] 1 $n
}
db func a_string a_string

do_multiclient_test tn {

  # Create and populate a database table using connection [db]. Check
  # that connections [db2] and [db3] can see the schema and content.
  #
  do_test pager1-$tn.1 {
    sql1 {
      CREATE TABLE t1(a PRIMARY KEY, b);
      CREATE INDEX i1 ON t1(b);
      INSERT INTO t1 VALUES(1, 'one');
      INSERT INTO t1 VALUES(2, 'two');
    }
  } {}
  do_test pager1-$tn.2 { sql2 { SELECT * FROM t1 } } {1 one 2 two}
  do_test pager1-$tn.3 { sql3 { SELECT * FROM t1 } } {1 one 2 two}

  # Open a transaction and add a row using [db]. This puts [db] in
  # RESERVED state. Check that connections [db2] and [db3] can still
  # read the database content as it was before the transaction was
  # opened. [db] should see the inserted row.
  #
  do_test pager1-$tn.4 {
    sql1 {
      BEGIN;
        INSERT INTO t1 VALUES(3, 'three');
    }
  } {}
  do_test pager1-$tn.5 { sql2 { SELECT * FROM t1 } } {1 one 2 two}
  do_test pager1-$tn.7 { sql1 { SELECT * FROM t1 } } {1 one 2 two 3 three}

  # [db] still has an open write transaction. Check that this prevents
  # other connections (specifically [db2]) from writing to the database.
  #
  # Even if [db2] opens a transaction first, it may not write to the
  # database. After the attempt to write the db within a transaction,
  # [db2] is left with an open transaction, but not a read-lock on
  # the main database. So it does not prevent [db] from committing.
  #
  do_test pager1-$tn.8 {
    csql2 { UPDATE t1 SET a = a + 10 }
  } {1 {database is locked}}
  do_test pager1-$tn.9 {
    csql2 {
      BEGIN;
      UPDATE t1 SET a = a + 10;
    }
  } {1 {database is locked}}

  # Have [db] commit its transactions. Check the other connections can
  # now see the new database content.
  #
  do_test pager1-$tn.10 { sql1 { COMMIT } } {}
  do_test pager1-$tn.11 { sql1 { SELECT * FROM t1 } } {1 one 2 two 3 three}
  do_test pager1-$tn.12 { sql2 { SELECT * FROM t1 } } {1 one 2 two 3 three}
  do_test pager1-$tn.13 { sql3 { SELECT * FROM t1 } } {1 one 2 two 3 three}

  # Check that, as noted above, [db2] really did keep an open transaction
  # after the attempt to write the database failed.
  #
  do_test pager1-$tn.14 {
    csql2 { BEGIN }
  } {1 {cannot start a transaction within a transaction}}
  do_test pager1-$tn.15 { sql2 { ROLLBACK } } {}

  # Have [db2] open a transaction and take a read-lock on the database.
  # Check that this prevents [db] from writing to the database (outside
  # of any transaction). After this fails, check that [db3] can read
  # the db (showing that [db] did not take a PENDING lock etc.)
  #
  do_test pager1-$tn.15 {
    sql2 { BEGIN; SELECT * FROM t1; }
  } {1 one 2 two 3 three}
  do_test pager1-$tn.16 {
    csql1 { UPDATE t1 SET a = a + 10 }
  } {1 {database is locked}}
  do_test pager1-$tn.17 { sql3 { SELECT * FROM t1 } } {1 one 2 two 3 three}

  # This time, have [db] open a transaction before writing the database.
  # This works - [db] gets a RESERVED lock which does not conflict with
  # the SHARED lock [db2] is holding.
  #
  do_test pager1-$tn.18 {
    sql1 {
      BEGIN;
      UPDATE t1 SET a = a + 10;
    }
  } {}
  do_test pager1-$tn-19 {
    sql1 { PRAGMA lock_status }
  } {main reserved temp closed}
  do_test pager1-$tn-20 {
    sql2 { PRAGMA lock_status }
  } {main shared temp closed}

  # Check that all connections can still read the database. Only [db] sees
  # the updated content (as the transaction has not been committed yet).
  #
  do_test pager1-$tn.21 { sql1 { SELECT * FROM t1 } } {11 one 12 two 13 three}
  do_test pager1-$tn.22 { sql2 { SELECT * FROM t1 } } {1 one 2 two 3 three}
  do_test pager1-$tn.23 { sql3 { SELECT * FROM t1 } } {1 one 2 two 3 three}

  # Because [db2] still has the SHARED lock, [db] is unable to commit the
  # transaction. If it tries, an error is returned and the connection
  # upgrades to a PENDING lock.
  #
  # Once this happens, [db] can read the database and see the new content,
  # [db2] (still holding SHARED) can still read the old content, but [db3]
  # (not holding any lock) is prevented by [db]'s PENDING from reading
  # the database.
  #
  do_test pager1-$tn.24 { csql1 { COMMIT } } {1 {database is locked}}
  do_test pager1-$tn-25 {
    sql1 { PRAGMA lock_status }
  } {main pending temp closed}
  do_test pager1-$tn.26 { sql1 { SELECT * FROM t1  } } {11 one 12 two 13 three}
  do_test pager1-$tn.27 { sql2 { SELECT * FROM t1  } } {1 one 2 two 3 three}
  do_test pager1-$tn.28 { csql3 { SELECT * FROM t1 } } {1 {database is locked}}

  # Have [db2] commit its read transaction, releasing the SHARED lock it
  # is holding. Now, neither [db2] nor [db3] may read the database (as [db]
  # is still holding a PENDING).
  #
  do_test pager1-$tn.29 { sql2 { COMMIT } } {}
  do_test pager1-$tn.30 { csql2 { SELECT * FROM t1 } } {1 {database is locked}}
  do_test pager1-$tn.31 { csql3 { SELECT * FROM t1 } } {1 {database is locked}}

  # [db] is now able to commit the transaction. Once the transaction is
  # committed, all three connections can read the new content.
  #
  do_test pager1-$tn.25 { sql1 { UPDATE t1 SET a = a+10 } } {}
  do_test pager1-$tn.26 { sql1 { COMMIT } } {}
  do_test pager1-$tn.27 { sql1 { SELECT * FROM t1 } } {21 one 22 two 23 three}
  do_test pager1-$tn.27 { sql2 { SELECT * FROM t1 } } {21 one 22 two 23 three}
  do_test pager1-$tn.28 { sql3 { SELECT * FROM t1 } } {21 one 22 two 23 three}
}

#-------------------------------------------------------------------------
# Savepoint related test cases.
#
do_test pager1-3.1 {
  faultsim_delete_and_reopen
  execsql {
    CREATE TABLE t1(a PRIMARY KEY, b);
    CREATE TABLE counter(
      i CHECK (i<5),
      u CHECK (u<10)
    );
    INSERT INTO counter VALUES(0, 0);
    CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN
      UPDATE counter SET i = i+1;
    END;
    CREATE TRIGGER tr2 AFTER UPDATE ON t1 BEGIN
      UPDATE counter SET u = u+1;
    END;
  }
  execsql { SELECT * FROM counter }
} {0 0}

do_execsql_test pager1-3.2 {
  BEGIN;
    INSERT INTO t1 VALUES(1, randomblob(1500));
    INSERT INTO t1 VALUES(2, randomblob(1500));
    INSERT INTO t1 VALUES(3, randomblob(1500));
    SELECT * FROM counter;
} {3 0}
do_catchsql_test pager1-3.3 {
    INSERT INTO t1 SELECT a+3, randomblob(1500) FROM t1
} {1 {constraint failed}}
do_execsql_test pager1-3.4 { SELECT * FROM counter } {3 0}
do_execsql_test pager1-3.5 { SELECT a FROM t1 } {1 2 3}
do_execsql_test pager1-3.6 { COMMIT } {}

#-------------------------------------------------------------------------
# Hot journal rollback related test cases.
#
# pager1.4.1.*: Test that the pager module deletes very small invalid
#               journal files.
#
# pager1.4.2.*: Test that if the master journal pointer at the end of a
#               hot-journal file appears to be corrupt (checksum does not
#               compute) the associated journal is rolled back (and no
#               xAccess() call to check for the presence of any master
#               journal file is made).
#
# pager1.4.3.*: Test that the contents of a hot-journal are ignored if the
#               page-size or sector-size in the journal header appear to
#               be invalid (too large, too small or not a power of 2).
#
do_test pager1.4.1.1 {
  faultsim_delete_and_reopen
  execsql {
    CREATE TABLE x(y, z);
    INSERT INTO x VALUES(1, 2);
  }
  set fd [open test.db-journal w]
  puts -nonewline $fd "helloworld"
  close $fd
  file exists test.db-journal
} {1}
do_test pager1.4.1.2 { execsql { SELECT * FROM x } } {1 2}
do_test pager1.4.1.3 { file exists test.db-journal } {0}

# Set up a [testvfs] to snapshot the file-system just before SQLite
# deletes the master-journal to commit a multi-file transaction.
#
# In subsequent test cases, invoking [faultsim_restore_and_reopen] sets
# up the file system to contain two databases, two hot-journal files and
# a master-journal.
#
do_test pager1.4.2.1 {
  testvfs tstvfs -default 1
  tstvfs filter xDelete
  tstvfs script xDeleteCallback
  proc xDeleteCallback {method file args} {
    set file [file tail $file]
    if { [string match *mj* $file] } { faultsim_save }
  }
  faultsim_delete_and_reopen
  db func a_string a_string
  execsql {
    ATTACH 'test.db2' AS aux;
    PRAGMA journal_mode = DELETE;
    PRAGMA main.cache_size = 10;
    PRAGMA aux.cache_size = 10;
    CREATE TABLE t1(a UNIQUE, b UNIQUE);
    CREATE TABLE aux.t2(a UNIQUE, b UNIQUE);
    INSERT INTO t1 VALUES(a_string(200), a_string(300));
    INSERT INTO t1 SELECT a_string(200), a_string(300) FROM t1;
    INSERT INTO t1 SELECT a_string(200), a_string(300) FROM t1;
    INSERT INTO t2 SELECT * FROM t1;
    BEGIN;
      INSERT INTO t1 SELECT a_string(201), a_string(301) FROM t1;
      INSERT INTO t1 SELECT a_string(202), a_string(302) FROM t1;
      INSERT INTO t1 SELECT a_string(203), a_string(303) FROM t1;
      INSERT INTO t1 SELECT a_string(204), a_string(304) FROM t1;
      REPLACE INTO t2 SELECT * FROM t1;
    COMMIT;
  }
  db close
  tstvfs delete
} {}
do_test pager1.4.2.2 {
  faultsim_restore_and_reopen
  execsql {
    SELECT count(*) FROM t1;
    PRAGMA integrity_check;
  }
} {4 ok}
do_test pager1.4.2.3 {
  faultsim_restore_and_reopen
  foreach f [glob test.db-mj*] { file delete -force $f }
  execsql {
    SELECT count(*) FROM t1;
    PRAGMA integrity_check;
  }
} {64 ok}
do_test pager1.4.2.4 {
  faultsim_restore_and_reopen
  hexio_write test.db-journal [expr [file size test.db-journal]-20] 123456
  execsql {
    SELECT count(*) FROM t1;
    PRAGMA integrity_check;
  }
} {4 ok}
do_test pager1.4.2.5 {
  faultsim_restore_and_reopen
  hexio_write test.db-journal [expr [file size test.db-journal]-20] 123456
  foreach f [glob test.db-mj*] { file delete -force $f }
  execsql {
    SELECT count(*) FROM t1;
    PRAGMA integrity_check;
  }
} {4 ok}

do_test pager1.4.3.1 {
  testvfs tstvfs -default 1
  tstvfs filter xSync
  tstvfs script xSyncCallback
  proc xSyncCallback {method file args} {
    set file [file tail $file]
    if { 0==[string match *journal $file] } { faultsim_save }
  }
  faultsim_delete_and_reopen
  execsql {
    PRAGMA journal_mode = DELETE;
    CREATE TABLE t1(a, b);
    INSERT INTO t1 VALUES(1, 2);
    INSERT INTO t1 VALUES(3, 4);
  }
  db close
  tstvfs delete
} {}

foreach {tn ofst value result} {
          2   20    31       {1 2 3 4}
          3   20    32       {1 2 3 4}
          4   20    33       {1 2 3 4}
          5   20    65536    {1 2 3 4}
          6   20    131072   {1 2 3 4}

          7   24    511      {1 2 3 4}
          8   24    513      {1 2 3 4}
          9   24    65536    {1 2 3 4}

         10   32    65536    {1 2}
} {
  do_test pager1.4.3.$tn {
    faultsim_restore_and_reopen
    hexio_write test.db-journal $ofst [format %.8x $value]
    execsql { SELECT * FROM t1 }
  } $result
}
db close

#-------------------------------------------------------------------------
# The following tests deal with multi-file commits.
#
# pager1-5.1.*: The case where a multi-file cannot be committed because
#               another connection is holding a SHARED lock on one of the
#               files. After the SHARED lock is removed, the COMMIT succeeds.
#
# pager1-5.2.*: Multi-file commits with journal_mode=memory.
#
# pager1-5.3.*: Multi-file commits with journal_mode=memory.
#
# pager1-5.4.*: Check that with synchronous=normal, the master-journal file
#               name is added to a journal file immediately after the last
#               journal record. But with synchronous=full, extra unused space
#               is allocated between the last journal record and the
#               master-journal file name so that the master-journal file
#               name does not lie on the same sector as the last journal file
#               record.
#
# pager1-5.5.*:
#
do_test pager1-5.1.1 {
  faultsim_delete_and_reopen
  execsql {
    ATTACH 'test.db2' AS aux;
    CREATE TABLE t1(a, b);
    CREATE TABLE aux.t2(a, b);
    INSERT INTO t1 VALUES(17, 'Lenin');
    INSERT INTO t1 VALUES(22, 'Stalin');
    INSERT INTO t1 VALUES(53, 'Khrushchev');
  }
} {}
do_test pager1-5.1.2 {
  execsql {
    BEGIN;
      INSERT INTO t1 VALUES(64, 'Brezhnev');
      INSERT INTO t2 SELECT * FROM t1;
  }
  sqlite3 db2 test.db2
  execsql {
    BEGIN;
      SELECT * FROM t2;
  } db2
} {}
do_test pager1-5.1.3 {
  catchsql COMMIT
} {1 {database is locked}}
do_test pager1-5.1.4 {
  execsql COMMIT db2
  execsql COMMIT
  execsql { SELECT * FROM t2 } db2
} {17 Lenin 22 Stalin 53 Khrushchev 64 Brezhnev}
do_test pager1-5.1.5 {
  db2 close
} {}

do_test pager1-5.2.1 {
  execsql {
    PRAGMA journal_mode = memory;
    BEGIN;
      INSERT INTO t1 VALUES(84, 'Andropov');
      INSERT INTO t2 VALUES(84, 'Andropov');
    COMMIT;
  }
} {memory}
do_test pager1-5.3.1 {
  execsql {
    PRAGMA journal_mode = off;
    BEGIN;
      INSERT INTO t1 VALUES(85, 'Gorbachev');
      INSERT INTO t2 VALUES(85, 'Gorbachev');
    COMMIT;
  }
} {off}

do_test pager1-5.4.1 {
  db close
  testvfs tv
  sqlite3 db test.db -vfs tv
  execsql { ATTACH 'test.db2' AS aux }

  tv filter xDelete
  tv script max_journal_size
  tv sectorsize 512
  set ::max_journal 0
  proc max_journal_size {method args} {
    set sz 0
    catch { set sz [file size test.db-journal] }
    if {$sz > $::max_journal} {
      set ::max_journal $sz
    }
    return SQLITE_OK
  }
  execsql {
    PRAGMA journal_mode = DELETE;
    PRAGMA synchronous = NORMAL;
    BEGIN;
      INSERT INTO t1 VALUES(85, 'Gorbachev');
      INSERT INTO t2 VALUES(85, 'Gorbachev');
    COMMIT;
  }
  set ::max_journal
} [expr 2615+[string length [pwd]]]
do_test pager1-5.4.2 {
  set ::max_journal 0
  execsql {
    PRAGMA synchronous = full;
    BEGIN;
      DELETE FROM t1 WHERE b = 'Lenin';
      DELETE FROM t2 WHERE b = 'Lenin';
    COMMIT;
  }
  set ::max_journal
} [expr 3111+[string length [pwd]]]
db close
tv delete

do_test pager1-5.5.1 {
  sqlite3 db test.db
  execsql {
    ATTACH 'test.db2' AS aux;
    PRAGMA journal_mode = PERSIST;
    CREATE TABLE t3(a, b);
    INSERT INTO t3 SELECT randomblob(1500), randomblob(1500) FROM t1;
    UPDATE t3 SET b = randomblob(1500);
  }
  expr [file size test.db-journal] > 15000
} {1}
do_test pager1-5.5.2 {
  execsql {
    PRAGMA synchronous = full;
    BEGIN;
      DELETE FROM t1 WHERE b = 'Stalin';
      DELETE FROM t2 WHERE b = 'Stalin';
    COMMIT;
  }
  file size test.db-journal
} {0}


#-------------------------------------------------------------------------
# The following tests work with "PRAGMA max_page_count"
#
do_test pager1-6.1 {
  faultsim_delete_and_reopen
  execsql {
    PRAGMA max_page_count = 10;
    CREATE TABLE t2(a, b);
    CREATE TABLE t3(a, b);
    CREATE TABLE t4(a, b);
    CREATE TABLE t5(a, b);
    CREATE TABLE t6(a, b);
    CREATE TABLE t7(a, b);
    CREATE TABLE t8(a, b);
    CREATE TABLE t9(a, b);
    CREATE TABLE t10(a, b);
  }
} {10}
do_test pager1-6.2 {
  catchsql {
    CREATE TABLE t11(a, b);
  }
} {1 {database or disk is full}}


#-------------------------------------------------------------------------
# The following tests work with "PRAGMA journal_mode=TRUNCATE" and
# "PRAGMA locking_mode=EXCLUSIVE".
#
# Each test is specified with 5 variables. As follows:
#
#   $tn:  Test Number. Used as part of the [do_test] test names.
#   $sql: SQL to execute.
#   $res: Expected result of executing $sql.
#   $js:  The expected size of the journal file, in bytes, after executing
#         the SQL script. Or -1 if the journal is not expected to exist.
#   $ws:  The expected size of the WAL file, in bytes, after executing
#         the SQL script. Or -1 if the WAL is not expected to exist.
#
faultsim_delete_and_reopen
foreach {tn sql res js ws} [subst {

  1  {
    CREATE TABLE t1(a, b);
    PRAGMA auto_vacuum=OFF;
    PRAGMA synchronous=NORMAL;
    PRAGMA page_size=1024;
    PRAGMA locking_mode=EXCLUSIVE;
    PRAGMA journal_mode=TRUNCATE;
    INSERT INTO t1 VALUES(1, 2);
  } {exclusive truncate} 0 -1

  2  {
    BEGIN IMMEDIATE;
      SELECT * FROM t1;
    COMMIT;
  } {1 2} 0 -1

  3  {
    BEGIN;
      SELECT * FROM t1;
    COMMIT;
  } {1 2} 0 -1

  4  { PRAGMA journal_mode = WAL }    wal    -1 -1
  5  { INSERT INTO t1 VALUES(3, 4) }  {}     -1 [wal_file_size 1 1024]
  6  { PRAGMA locking_mode = NORMAL } normal -1 [wal_file_size 1 1024]
  7  { INSERT INTO t1 VALUES(5, 6); } {}     -1 [wal_file_size 2 1024]

  8  { PRAGMA journal_mode = TRUNCATE } truncate          0 -1
  9  { INSERT INTO t1 VALUES(7, 8) }    {}                0 -1
  10 { SELECT * FROM t1 }               {1 2 3 4 5 6 7 8} 0 -1

}] {
  do_execsql_test pager1-7.1.$tn.1 $sql $res
  catch { set J -1 ; set J [file size test.db-journal] }
  catch { set W -1 ; set W [file size test.db-wal] }
  do_test pager1-7.1.$tn.2 { list $J $W } [list $js $ws]
}

do_test pager1-8.1 {
  faultsim_delete_and_reopen
  db close
  sqlite3 db :memory:
  execsql {
    CREATE TABLE x1(x);
    INSERT INTO x1 VALUES('Charles');
    INSERT INTO x1 VALUES('James');
    INSERT INTO x1 VALUES('Mary');
    SELECT * FROM x1;
  }
} {Charles James Mary}
do_test pager1-8.2 {
  db close
  sqlite3 db :memory:
  catchsql { SELECT * FROM x1 }
} {1 {no such table: x1}}

finish_test