SQLite

#
# pager1-8.*: Cases using temporary and in-memory databases.
#
# pager1-9.*: Tests related to the backup API.
#
# pager1-10.*: Test that the assumed file-system sector-size is limited to
#              64KB.
#
# pager1-12.*: Tests involving "PRAGMA page_size"
#
# pager1-13.*: Cases specific to "PRAGMA journal_mode=PERSIST"
#
# pager1-14.*: Cases specific to "PRAGMA journal_mode=OFF"
#

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

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

  # Install a busy-handler for connection [db].
  #
  set ::nbusy [list]
  proc busy {n} {
    lappend ::nbusy $n
    if {$n>5} { sql2 COMMIT }
    return 0
  }
  db busy busy

  do_test pager1-$tn.29 { 
    sql1 { BEGIN ; INSERT INTO t1 VALUES('x', 'y') } 
  } {}
  do_test pager1-$tn.30 { 
    sql2 { BEGIN ; SELECT * FROM t1 } 
  } {21 one 22 two 23 three}
  do_test pager1-$tn.31 { sql1 COMMIT } {}
  do_test pager1-$tn.32 { set ::nbusy } {0 1 2 3 4 5 6}
}

#-------------------------------------------------------------------------
# Savepoint related test cases.
#
# pager1-3.1.2.*: Force a savepoint rollback to cause the database file
#                 to grow.

      UPDATE counter SET u = u+1;
    END;
  }
  execsql { SELECT * FROM counter }
} {0 0}

do_execsql_test pager1-3.1.2 {
  PRAGMA cache_size = 10;
  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.1.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 } {}

foreach {tn sql tcl} {


  7  { PRAGMA synchronous = NORMAL ; PRAGMA temp_store = 0 } {
    testvfs tv -default 1
    tv devchar safe_append
  }
  8  { PRAGMA synchronous = NORMAL ; PRAGMA temp_store = 2 } {
    testvfs tv -default 1
    tv devchar sequential
  }
  9  { PRAGMA synchronous = FULL } { }
  10 { PRAGMA synchronous = NORMAL } { }
  11 { PRAGMA synchronous = OFF } { }
  12 { PRAGMA synchronous = FULL ; PRAGMA fullfsync = 1 } { }
  13 { PRAGMA synchronous = FULL } {
    testvfs tv -default 1
    tv devchar sequential
  }
} {
  do_test pager1-3.$tn.1 {
    eval $tcl
    faultsim_delete_and_reopen
    db func a_string a_string
    execsql $sql
    execsql {

    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_catchsql_test pager1-6.2 {

  CREATE TABLE t11(a, b)

} {1 {database or disk is full}}
do_execsql_test pager1-6.4 { PRAGMA max_page_count      } {10}
do_execsql_test pager1-6.5 { PRAGMA max_page_count = 15 } {15}
do_execsql_test pager1-6.6 { CREATE TABLE t11(a, b)     } {}
do_execsql_test pager1-6.7 {
  BEGIN;
    INSERT INTO t11 VALUES(1, 2);
    PRAGMA max_page_count = 13;
} {13}
do_execsql_test pager1-6.8 {
    INSERT INTO t11 VALUES(3, 4);
    PRAGMA max_page_count = 10;
} {11}
do_execsql_test pager1-6.9 { COMMIT } {}


#-------------------------------------------------------------------------
# The following tests work with "PRAGMA journal_mode=TRUNCATE" and
# "PRAGMA locking_mode=EXCLUSIVE".
#
# Each test is specified with 5 variables. As follows:

    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]
  }
}

#-------------------------------------------------------------------------
# The following tests, pager1-8.*, test that the special filenames 
# ":memory:" and "" open temporary databases.
#
foreach {tn filename} {
  1 :memory:
  2 ""
} {
  do_test pager1-8.$tn.1 {
    faultsim_delete_and_reopen
    db close

    PRAGMA integrity_check;
  } db2
} {truncate ok}
do_test pager1-11.4 {
  db2 close
  file size test.db-journal
} {0}

do_execsql_test pager1-11.5 { SELECT count(*) FROM zz } {32}
db close
tv delete
  
#-------------------------------------------------------------------------
# Test "PRAGMA page_size"
#

      SELECT count(*) FROM v;
      PRAGMA main.page_size;
    }
  } [list 1 $pagesize]
  db2 close
}

#-------------------------------------------------------------------------
# Test specal "PRAGMA journal_mode=PERSIST" test cases.
#
# pager1-13.1.*: This tests a special case encountered in persistent 
#                journal mode: If the journal associated with a transaction
#                is smaller than the journal file (because a previous 
#                transaction left a very large non-hot journal file in the
#                file-system), then SQLite has to be careful that there is
#                not a journal-header left over from a previous transaction
#                immediately following the journal content just written.
#                If there is, and the process crashes so that the journal
#                becomes a hot-journal and must be rolled back by another
#                process, there is a danger that the other process may roll
#                back the aborted transaction, then continue copying data
#                from an older transaction from the remainder of the journal.
#                See the syncJournal() function for details.
#
# pager1-13.2.*: Same test as the previous. This time, throw an index into
#                the mix to make the integrity-check more likely to catch
#                errors.
#
testvfs tv -default 1
tv script xSyncCb
tv filter xSync
proc xSyncCb {method filename args} {
  set t [file tail $filename]
  if {$t == "test.db"} faultsim_save
  return SQLITE_OK
}
faultsim_delete_and_reopen
db func a_string a_string

# The UPDATE statement at the end of this test case creates a really big
# journal. Since the cache-size is only 10 pages, the journal contains 
# frequent journal headers.
#
do_execsql_test pager1-13.1.1 {
  PRAGMA page_size = 1024;
  PRAGMA journal_mode = PERSIST;
  PRAGMA cache_size = 10;
  BEGIN;
    CREATE TABLE t1(a INTEGER PRIMARY KEY, b BLOB);
    INSERT INTO t1 VALUES(NULL, a_string(400));
    INSERT INTO t1 SELECT NULL, a_string(400) FROM t1;          /*   2 */
    INSERT INTO t1 SELECT NULL, a_string(400) FROM t1;          /*   4 */
    INSERT INTO t1 SELECT NULL, a_string(400) FROM t1;          /*   8 */
    INSERT INTO t1 SELECT NULL, a_string(400) FROM t1;          /*  16 */
    INSERT INTO t1 SELECT NULL, a_string(400) FROM t1;          /*  32 */
    INSERT INTO t1 SELECT NULL, a_string(400) FROM t1;          /*  64 */
    INSERT INTO t1 SELECT NULL, a_string(400) FROM t1;          /* 128 */
  COMMIT;
  UPDATE t1 SET b = a_string(400);
} {persist}

# Run transactions of increasing sizes. Eventually, one (or more than one)
# of these will write just enough content that one of the old headers created 
# by the transaction in the block above lies immediately after the content
# journalled by the current transaction.
#
for {set nUp 1} {$nUp<64} {incr nUp} {
  do_execsql_test pager1-13.1.2.$nUp.1 { 
    UPDATE t1 SET b = a_string(399) WHERE a <= $nUp
  } {}
  do_execsql_test pager1-13.1.2.$nUp.2 { PRAGMA integrity_check } {ok} 

  # Try to access the snapshot of the file-system.
  #
  sqlite3 db2 sv_test.db
  do_test pager1-13.1.2.$nUp.3 {
    execsql { SELECT sum(length(b)) FROM t1 } db2
  } [expr {128*400 - ($nUp-1)}]
  do_test pager1-13.1.2.$nUp.4 {
    execsql { PRAGMA integrity_check } db2
  } {ok}
  db2 close
}

# Same test as above. But this time with an index on the table.
#
do_execsql_test pager1-13.2.1 {
  CREATE INDEX i1 ON t1(b);
  UPDATE t1 SET b = a_string(400);
} {}
for {set nUp 1} {$nUp<64} {incr nUp} {
  do_execsql_test pager1-13.2.2.$nUp.1 { 
    UPDATE t1 SET b = a_string(399) WHERE a <= $nUp
  } {}
  do_execsql_test pager1-13.2.2.$nUp.2 { PRAGMA integrity_check } {ok} 
  sqlite3 db2 sv_test.db
  do_test pager1-13.2.2.$nUp.3 {
    execsql { SELECT sum(length(b)) FROM t1 } db2
  } [expr {128*400 - ($nUp-1)}]
  do_test pager1-13.2.2.$nUp.4 {
    execsql { PRAGMA integrity_check } db2
  } {ok}
  db2 close
}

db close
tv delete

#-------------------------------------------------------------------------
# Test specal "PRAGMA journal_mode=OFF" test cases.
#
faultsim_delete_and_reopen
do_execsql_test pager1-14.1.1 {
  PRAGMA journal_mode = OFF;
  CREATE TABLE t1(a, b);
  BEGIN;
    INSERT INTO t1 VALUES(1, 2);
  COMMIT;
  SELECT * FROM t1;
} {off 1 2}
do_catchsql_test pager1-14.1.2 {
  BEGIN;
    INSERT INTO t1 VALUES(3, 4);
  ROLLBACK;
} {0 {}}
do_execsql_test pager1-14.1.3 {
  SELECT * FROM t1;
} {1 2 3 4}
do_catchsql_test pager1-14.1.4 {
  BEGIN;
    INSERT INTO t1(rowid, a, b) SELECT a+3, b, b FROM t1;
    INSERT INTO t1(rowid, a, b) SELECT a+3, b, b FROM t1;
} {1 {PRIMARY KEY must be unique}}
do_execsql_test pager1-14.1.5 {
  COMMIT;
  SELECT * FROM t1;
} {1 2 3 4 2 2 4 4}

finish_test

  execsql { UPDATE xx SET a = a_string(300) }
} -test {
  faultsim_test_result {0 {}}
  faultsim_integrity_check
  faultsim_integrity_check
}



#-------------------------------------------------------------------------
# Test fault injection with transaction savepoints (savepoints created
# when a SAVEPOINT command is executed outside of any other savepoint
# or transaction context).
#
do_test pagerfault-9-pre1 {
  faultsim_delete_and_reopen

    DELETE FROM t3;
    RELEASE trans;
  }
} -test {
  faultsim_test_result {0 {}}
  faultsim_integrity_check
}

}

do_test pagerfault-12-pre1 {
  testvfs ss_layer -default 1
  ss_layer sectorsize 4096
  faultsim_delete_and_reopen
  db func a_string a_string;

  execsql {
    PRAGMA page_size = 1024;
    PRAGMA journal_mode = PERSIST;
    PRAGMA cache_size = 10;
    BEGIN;
      CREATE TABLE t1(x, y UNIQUE);
      INSERT INTO t1 VALUES(a_string(333), a_string(444));
      INSERT INTO t1 SELECT a_string(333+rowid), a_string(444+rowid) FROM t1;
      INSERT INTO t1 SELECT a_string(333+rowid), a_string(444+rowid) FROM t1;
      INSERT INTO t1 SELECT a_string(333+rowid), a_string(444+rowid) FROM t1;
      INSERT INTO t1 SELECT a_string(333+rowid), a_string(444+rowid) FROM t1;
      INSERT INTO t1 SELECT a_string(44), a_string(55) FROM t1 LIMIT 13;
    COMMIT;
  }
  faultsim_save_and_close
} {}
do_faultsim_test pagerfault-12 -prep {
  faultsim_restore_and_reopen
  execsql { PRAGMA cache_size = 10 }
  db func a_string a_string;
} -body {
  execsql {
    UPDATE t1 SET x = a_string(length(x)), y = a_string(length(y));
  }
} -test {
  faultsim_test_result {0 {}}
  faultsim_integrity_check
}



finish_test