SQLite4
Artifact Content
Not logged in

Artifact 9186eb87b61aa68f7a010db4a8cb0e00ee448e18:


# 2013 September 24
#
# 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.
#
#***********************************************************************
# The tests in this file were used while developing the SQLite 4 code. 
#
set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix simple3

db close
forcedelete test.db

proc bigstr {n} {
  set nRep [expr 1+($n/20)]
  string range [string repeat "abcdefghijklmnopqrstuvwxyz" $nRep] 0 [expr $n-1]
}


do_test 1.0 {
  sqlite4 db file:test.db?kv=bt
  db close
} {}

do_test 1.1 { sqlite4 db file:test.db?kv=bt } {}

do_execsql_test 1.2 { 
  CREATE TABLE t1(a, b) 
} 

do_execsql_test 1.3.1 {
  SELECT * FROM sqlite_master;
} {table t1 t1 2 {CREATE TABLE t1(a, b)}}

do_test 1.3.2 {
  sqlite4 db2 file:test.db?kv=bt
  breakpoint
  execsql { SELECT * FROM sqlite_master } db2
} {table t1 t1 2 {CREATE TABLE t1(a, b)}}
db2 close

do_execsql_test 1.4 {
  INSERT INTO t1 VALUES('abc', 'def');
  INSERT INTO t1 VALUES('ghi', 'jkl');
} {}

do_execsql_test 1.5 {
  SELECT rowid, a, b FROM t1;
} {1 abc def 2 ghi jkl}

do_execsql_test 1.6 {
  UPDATE t1 SET b = 5;
}

do_execsql_test 1.7 {
  SELECT rowid, a, b FROM t1;
} {1 abc 5 2 ghi 5}

#execsql { PRAGMA kv_trace = 1 }
do_execsql_test 1.8 {
  DELETE FROM t1 WHERE 1;
}

do_execsql_test 1.9 {
  SELECT * FROM t1;
  DROP TABLE t1;
}

do_execsql_test 1.10 {
  SELECT * FROM sqlite_kvstore;
}

#--------------------------------------------------------------------------

set val [string repeat x 200]
do_execsql_test 2.0 {
  CREATE TABLE t1(a PRIMARY KEY, b);
  INSERT INTO t1 VALUES(1, $val);
  INSERT INTO t1 VALUES(2, $val);
  INSERT INTO t1 VALUES(3, $val);
  INSERT INTO t1 VALUES(4, $val);
} 

do_execsql_test 2.1 {
  DELETE FROM t1 WHERE a = 2;
}

do_execsql_test 2.2 {
  INSERT INTO t1 VALUES(5, $val);
}

do_execsql_test 2.3 { 
  SELECT a, length(b) FROM t1 
} {1 200  3 200  4 200  5 200}

do_execsql_test 2.4 {
  INSERT INTO t1 VALUES(6, $val);
}

do_execsql_test 2.5 { 
  SELECT a, length(b) FROM t1 
} {1 200  3 200  4 200  5 200  6 200}

#-------------------------------------------------------------------------

proc lshuffle {list} {
  set nVal [llength $list]
  for {set i 0} {$i < $nVal} {incr i} {
    set i2 [expr int(rand()*$nVal)]
    set tmp [lindex $list $i]
    lset list $i [lindex $list $i2]
    lset list $i2 $tmp
  }
  return $list
}

proc K {a} { set a }

proc int_list {nVal} {
  set ret [list]
  for {set i 0} {$i < $nVal} {incr i} {
    lappend ret $i
  }
  return $ret
}

do_test 3.0 {
  catch { db close }
  forcedelete test.db
  forcedelete test.db-wal
  sqlite4 db file:test.db?kv=bt
} {}

do_execsql_test 3.1 {
  CREATE TABLE t1(a PRIMARY KEY, b);
}

set nRow 100000
set nStep [expr $nRow / 50]

foreach {tn shuffle_proc} {
  1 K
  2 lshuffle
} {
  
  set iRow 0
  foreach k [$shuffle_proc [int_list $nRow]] {
    incr iRow
    
    execsql { INSERT INTO t1 VALUES($k, randomblob(100)); }
    if {0==($iRow % $nStep)} {
      do_execsql_test 4.$tn.1.$iRow {
        SELECT count(*) FROM t1;
      } $iRow
    }
  }
  
  do_test 4.$tn.2 {
    set nInitial [db one {SELECT count(*) FROM t1}]
    for {set i 0} {$i < $nRow} {incr i} {
      set res [execsql {SELECT count(*) FROM t1 WHERE a = $i}]
      if {$res!="1"} { error "res = $res for i=$i" }
    }
  } {}
  
  set iRow 0
  foreach k [$shuffle_proc [int_list $nRow]] {
    incr iRow
    
    execsql { DELETE FROM t1 WHERE a = $k }
    if {0==($iRow % $nStep)} {
      do_execsql_test 4.$tn.3.$iRow {
        SELECT count(*) FROM t1;
      } [expr $nRow - $iRow]
    }
  }
}

proc bigstr {n} {
  set nRep [expr 1+($n/20)]
  string range [string repeat "abcdefghijklmnopqrstuvwxyz" $nRep] 0 [expr $n-1]
}

foreach {tn nStr} {
  1 3000
  2 30000
  3 300000
  4 3000000
  5 30000000
} {
  set big [bigstr $nStr]
  do_execsql_test 5.$tn.1 {
    DROP TABLE IF EXISTS t5;
    CREATE TABLE t5(a PRIMARY KEY, b VALUE);
    INSERT INTO t5 VALUES(1, $big);
  }

  do_execsql_test 5.$tn.2 {
    SELECT length(b) FROM t5;
  } $nStr
  if {[set_test_counter errors]} break

  do_execsql_test 5.$tn.3 {
    SELECT b FROM t5;
  } [list $big]
}

foreach {tn nStr} {
  1 3000
  2 30000
  3 300000
  4 3000000
  5 30000000
} {
  set big [bigstr $nStr]
  do_execsql_test 6.$tn.1 {
    DROP TABLE IF EXISTS t6;
    CREATE TABLE t6(a PRIMARY KEY, b VALUE);
    INSERT INTO t6 VALUES($big, '123');
  }

  do_execsql_test 6.$tn.2 {
    SELECT length(a) FROM t6;
  } $nStr
  if {[set_test_counter errors]} break

  do_execsql_test 6.$tn.3 {
    SELECT a FROM t6;
  } [list $big]
}

finish_test