sqlite/test/commitstatus.test

# 2015 July 26
#
# 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
set ::testprefix commitstatus

ifcapable !concurrent {
  finish_test
  return
}

proc commit_status_frames {db} {
  list [sqlite3_commit_status $db main FIRSTFRAME] \
       [sqlite3_commit_status $db main NFRAME]
}

proc commit_status_conflict {db} {
  list [sqlite3_commit_status $db main CONFLICT_DB]    \
       [sqlite3_commit_status $db main CONFLICT_FRAME] \
       [sqlite3_commit_status $db main CONFLICT_PGNO]
}


do_execsql_test 1.0 {
  PRAGMA journal_mode = wal;
} {wal}

do_execsql_test 1.1 {
  CREATE TABLE t1(k INTEGER PRIMARY KEY, v);
}

do_test 1.2 { commit_status_frames db } {1 2}

do_execsql_test 1.3 {
  INSERT INTO t1 VALUES(2, 'two');
}
do_test 1.4 { commit_status_frames db } {3 1}

do_execsql_test 1.5 {
  CREATE INDEX i1 ON t1(v);
}
do_test 1.6 { commit_status_frames db } {4 2}

do_execsql_test 1.7 {
  DROP INDEX i1;
}
do_test 1.8 { commit_status_frames db } {6 2}

do_execsql_test 1.9 {
  PRAGMA wal_checkpoint;
  INSERT INTO t1 VALUES(3, 'three');
} {0 7 7}
do_test 1.10 { commit_status_frames db } {1 1}

do_execsql_test 1.11 {
  PRAGMA journal_mode = delete;
  PRAGMA journal_mode = wal2;
  PRAGMA journal_size_limit = 10000;
} {delete wal2 10000}

for {set ii 1} {$ii < 30} {incr ii} {
  do_execsql_test 1.13.$ii.1 {
    INSERT INTO t1(v) VALUES('value');
  }

  if {$ii<=10} {
    set expect [list $ii 1]
  } elseif {$ii <= 20} {
    set expect [list [expr {(1+($ii-1)%10)|0x80000000}] 1]
  } else {
    set expect [list [expr ($ii%10)] 1]
  }

  do_test 1.13.$ii.2 { commit_status_frames db } $expect

  if {$ii==15} {
    execsql { PRAGMA wal_checkpoint }
  }
}

#-------------------------------------------------------------------------
reset_db

do_execsql_test 2.0 {
  CREATE TABLE t1(a, b);
  CREATE TABLE t2(a, b);
  PRAGMA journal_mode = wal;
} {wal}

sqlite3 db2 test.db

do_execsql_test -db db2 2.1 {
  BEGIN CONCURRENT;
    INSERT INTO t1 VALUES(1, 1);
}

do_execsql_test 2.2 {
  INSERT INTO t2 VALUES(1, 1);
  INSERT INTO t2 VALUES(2, 2);
  INSERT INTO t2 VALUES(3, 3);
  INSERT INTO t2 VALUES(4, 4);
}

do_execsql_test -db db2 2.3 {
  COMMIT
}
do_test 2.4 { commit_status_frames db2 } {5 1}

do_execsql_test 2.5 {
  BEGIN CONCURRENT;
    INSERT INTO t1 VALUES('yes', 'no');
    INSERT INTO t2 VALUES('yes', 'no');
  COMMIT;
}
do_test 2.6 { commit_status_frames db } {6 2}

db2 close
do_execsql_test 2.7 {
  PRAGMA journal_mode = delete;
  PRAGMA journal_mode = wal2;
} {delete wal2}
sqlite3 db2 test.db

do_execsql_test 2.8 {
  PRAGMA journal_size_limit = 5000;
  BEGIN CONCURRENT;
    INSERT INTO t1 VALUES('x', 'y');
} {5000}

do_execsql_test -db db2 2.9 {
  PRAGMA journal_size_limit = 5000;
  INSERT INTO t2 VALUES(1, 1);
  INSERT INTO t2 VALUES(2, 2);
  INSERT INTO t2 VALUES(3, 3);
  INSERT INTO t2 VALUES(4, 4);
  INSERT INTO t2 VALUES(5, 5);
  INSERT INTO t2 VALUES(6, 6);
  INSERT INTO t2 VALUES(7, 7);
  INSERT INTO t2 VALUES(8, 8);
} {5000}

do_execsql_test 2.10 {
  COMMIT;
} 
do_test 2.11 { commit_status_frames db } [list [expr {0x80000000 | 4}] 1]

do_execsql_test 2.12 {
  PRAGMA wal_checkpoint;
  BEGIN CONCURRENT;
    INSERT INTO t1 VALUES('a', 'b');
} {0 9 5}

do_execsql_test -db db2 2.13 {
  INSERT INTO t2 VALUES(1, 1);
  INSERT INTO t2 VALUES(2, 2);
  INSERT INTO t2 VALUES(3, 3);
  INSERT INTO t2 VALUES(4, 4);
  INSERT INTO t2 VALUES(5, 5);
  INSERT INTO t2 VALUES(6, 6);
  INSERT INTO t2 VALUES(7, 7);
  INSERT INTO t2 VALUES(8, 8);
} {}

do_execsql_test 2.14 {
  COMMIT;
} 
do_test 2.15 { commit_status_frames db } [list 8 1]

#-------------------------------------------------------------------------
reset_db
do_execsql_test 3.0 {
  CREATE TABLE t1(x);
  CREATE TABLE t2(y);
  CREATE TABLE t3(z);
  PRAGMA journal_mode = wal2;
  PRAGMA journal_size_limit = 8000;
} {wal2 8000}

sqlite3 db2 test.db
do_execsql_test -db db2 3.1 {
  BEGIN CONCURRENT;
    INSERT INTO t1 VALUES('x');
}

do_execsql_test 3.2.0 {
  INSERT INTO t2 VALUES('y');  -- frame 1
  INSERT INTO t3 VALUES('y');  -- frame 2
  INSERT INTO t2 VALUES('z');  -- frame 3
  INSERT INTO t3 VALUES('z');  -- frame 4
  INSERT INTO t1 VALUES('a');  -- frame 5
}
do_test 3.2.1 { commit_status_frames db } {5 1}
do_execsql_test 3.2.2 {
  INSERT INTO t3 VALUES('a');  -- frame 6
}

do_test 3.3 {
  catchsql { COMMIT } db2
} {1 {database is locked}}
execsql ROLLBACK db2
do_test 3.4 {
  commit_status_conflict db2 
} {0 5 2}

do_execsql_test -db db2 3.5 {
  BEGIN CONCURRENT;
    INSERT INTO t3 VALUES('x');
}

do_execsql_test 3.6.0 {
  INSERT INTO t2 VALUES('y');  -- frame 7
  INSERT INTO t1 VALUES('y');  -- frame 8
  INSERT INTO t2 VALUES('z');  -- frame 1b
  INSERT INTO t1 VALUES('z');  -- frame 2b
  INSERT INTO t3 VALUES('a');  -- frame 3b
}
do_test 3.6.1 { commit_status_frames db } [list [expr {0x80000000 | 3}] 1]
do_execsql_test 3.6.2 {
  INSERT INTO t1 VALUES('a');  -- frame 4b
}

do_test 3.7 {
  catchsql { COMMIT } db2
} {1 {database is locked}}
execsql ROLLBACK db2
do_test 3.8 {
  commit_status_conflict db2 
} [list 0 [expr {0x80000000 | 3}] 4]

#-------------------------------------------------------------------------
reset_db
do_execsql_test 4.0 {
  CREATE TABLE t1(x);
  CREATE TABLE t2(y);

  PRAGMA journal_mode = wal2;
  PRAGMA journal_size_limit = 10000000;
} {wal2 10000000}

sqlite3 db2 test.db
db2 eval {SELECT * FROM sqlite_schema}

foreach {tn s1 s2} {
  1 100 100
  2 1000 1000
  3 1000 1000
  4 1000 1000
  5 1000 1000
  6 1000 1000
  7 1000 1000
  8 1000 1000
  9 1000 1000

  10 2000 2000
  11 2000 2000
  12 2000 2000
  13 2000 2000
  14 2000 2000
  15 2000 2000
  16 2000 2000
  17 2000 2000
} {
  execsql { 
    BEGIN CONCURRENT;
      INSERT INTO t1 VALUES(NULL);
  } db2

  for {set ii 0} {$ii<$s1} {incr ii} {
    execsql { INSERT INTO t2 VALUES(randomblob(30)); }
  }

  execsql { INSERT INTO t1 VALUES(NULL) }
  set frame [sqlite3_commit_status db main FIRSTFRAME]

  for {set ii 0} {$ii<$s2} {incr ii} {
    execsql { INSERT INTO t2 VALUES(randomblob(30)); }
  }

  do_test 4.$tn.1 { catchsql "COMMIT" db2 } {1 {database is locked}}
  do_test 4.$tn.2 { 
    commit_status_conflict db2
  } [list 0 $frame 2]
  execsql { ROLLBACK } db2
}

finish_test