sqlite/test/concurrent3.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.
#
#***********************************************************************
#
# Tests for transactions started with BEGIN CONCURRENT. The tests in this
# file focus on testing that deferred page allocation works properly.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
source $testdir/lock_common.tcl
set ::testprefix concurrent3

if {$AUTOVACUUM} { finish_test ; return }
ifcapable !concurrent {
  finish_test
  return
}

db close
sqlite3_shutdown
test_sqlite3_log xLog
proc xLog {error_code msg} {
  # puts "$error_code: $msg"
  # Enable the previous for debugging
}
reset_db

proc create_schema {} {
  db eval {
    PRAGMA journal_mode = wal;

    CREATE TABLE t1(x, y);
    CREATE TABLE t2(x, y);
    CREATE TABLE t3(x, y);
    CREATE TABLE t4(x, y);

    CREATE INDEX i1 ON t1(y, x);
    CREATE INDEX i2 ON t2(y, x);
    CREATE INDEX i3 ON t3(y, x);
    CREATE INDEX i4 ON t4(y, x);
  }
}

proc do_sql_op {iTbl iOp} {
  set db "db$iTbl"

  switch $iOp {
    "i" {
      set sql "
        WITH cnt(i) AS (SELECT 1 UNION ALL SELECT i+1 FROM cnt WHERE i<10)
        INSERT INTO t$iTbl SELECT randomblob(800), randomblob(800) FROM cnt;
      "
    }

    "d" {
      set sql "
        DELETE FROM t$iTbl WHERE rowid IN (
          SELECT rowid FROM t$iTbl ORDER BY 1 ASC LIMIT 10
        )
      "
    }

    "D" {
      set sql "
        DELETE FROM t$iTbl WHERE rowid IN (
          SELECT rowid FROM t$iTbl o WHERE (
            SELECT count(*) FROM t$iTbl i WHERE i.rowid<o.rowid
          ) % 2
        )
      "
    }

    "I" {
      set sql "
        INSERT INTO t$iTbl SELECT randomblob(800), randomblob(800) FROM t$iTbl;
      "
    }

    default {
      error "bad iOp parameter: $iOp"
    }
  }

  $db eval $sql
}


set DBLIST {db1 db2 db3 db4} 

create_schema
foreach {tn oplist} {
  1  {1i   2i   3i   4i} 
  2  {1iii 2iii 3iii 4iii}
  3  {1d   2d   3d   4d} 
  .  -----------------------
  4  {1i}
  5  {1d 2i}
  .  -----------------------
  6  {1iii 2iii 3iii 4iii}
  7  {1di  2id  3iii 4ddd}
  8  {1iii 2iii 3iii 4iii}
  9  {1D  2II}
  10 {1I  2D  3I  4D}
  11 {1III 3dddddd 4III}
} {
  if {[string range $oplist 0 0]=="-"} {
    reset_db
    create_schema
    continue
  }
  foreach db $DBLIST { sqlite3 $db test.db }

  do_test 1.$tn {
    foreach db $DBLIST { $db eval "BEGIN CONCURRENT" } 

    foreach op $oplist {
      set iTbl [string range $op 0 0]
      foreach char [split [string range $op 1 end] {}] {
        do_sql_op $iTbl $char
      }
    }

    foreach db $DBLIST { $db eval "COMMIT" }
    db eval {PRAGMA integrity_check}
  } {ok}

  foreach db $DBLIST { 
    $db close 
  }
}

#-------------------------------------------------------------------------
#
proc create_schema2 {} {
  db eval {
    PRAGMA journal_mode = wal;
    CREATE TABLE t1(x INTEGER PRIMARY KEY, y);
    CREATE INDEX i1 ON t1(y);
  }
}

proc randint {nMax} {
  db eval {SELECT abs(random() % $nMax)}
}

proc do_sql_op2 {db iOp} {
  switch -- $iOp {
    i {
      # Insert 1 rows.
      set r [randint 1000000000]
      set ::rows($r) 1
      #puts "insert row $r"
      $db eval { INSERT OR IGNORE INTO t1 VALUES($r, randomblob(50)); }
    }

    d {
      # Insert 1 row
      set keys [array names ::rows]
      set r [randint [llength $keys]]
      set rowid [lindex $keys $r]
      $db eval { DELETE FROM t1 WHERE x=$rowid }
      unset ::rows($rowid)
    }
  }
}

foreach {tn nRepeat oplist} {
  - - ----------------------------
  1 100 { 1iiiiiiiiii }
  2 100 { 1i 2d }
  3 100 { 1d 2i }
  4  50 { 1d 2i 3d }
  5 500 { 1i 2i 3i 4i }
  6 500 { 1i 2d 3d 4d }
} {
  if {[string range $oplist 0 0]=="-"} {
    array unset rows
    reset_db
    create_schema2
    continue
  }

  foreach db $DBLIST { 
    sqlite3 $db test.db 
    set stats($db,0) 0
    set stats($db,1) 0
  }
  array unset used

  do_test 2.$tn {

    for {set i 0} {$i < $nRepeat} {incr i} {
      foreach db $DBLIST { $db eval "BEGIN CONCURRENT" } 

      foreach op $oplist {
        set iDb [string range $op 0 0]
        set used(db$iDb) 1
        foreach char [split [string range $op 1 end] {}] {
          do_sql_op2 "db$iDb" $char
        }
      }

      foreach db $DBLIST { 
        set rc [catch { $db eval COMMIT } msg]
        if {$rc} { $db eval ROLLBACK }
        incr stats($db,$rc)
      }
      set res [db eval {PRAGMA integrity_check}]
      if {$res != "ok"} { puts "after $db $rc: $res" ; after 1000000 }
    }
  } {}

  foreach db $DBLIST { 
    $db close 
  }
  # foreach k [lsort [array names used]] {
  #   puts "$k: $stats($k,0) committed, $stats($k,1) rolled back"
  # }
}

catch { db close }
sqlite3_shutdown
test_sqlite3_log


finish_test