#!/usr/bin/tclsh
#
# Run this script using TCLSH to do a speed comparison between
# various two different version of SQLite in executables sqlite-base
# and sqlite-test
#

# Run a test
#
set cnt 1
proc runtest {title} {
  global cnt
  set sqlfile test$cnt.sql
  puts "*** Test $cnt: $title ***"
  incr cnt
  set fd [open $sqlfile r]
  set sql [string trim [read $fd [file size $sqlfile]]]
  close $fd
  set sx [split $sql \n]
  set n [llength $sx]
  if {$n>8} {
    set sql {}
    for {set i 0} {$i<3} {incr i} {append sql [lindex $sx $i]\n}
    append sql  "... [expr {$n-6}] lines omitted\n"
    for {set i [expr {$n-3}]} {$i<$n} {incr i} {
      append sql [lindex $sx $i]\n
    }
  }
  puts "[string trim $sql]\n"
  set format {  %-30s  %10.3f}
  set delay 1000
  exec sync; after $delay;
  set t [time "exec ./sqlite-base sb1.db <$sqlfile" 1]
  set t [expr {[lindex $t 0]/1000000.0}]
  puts [format $format {SQLite (base):} $t]
  exec sync; after $delay;
  set t [time "exec ./sqlite-test st1.db <$sqlfile" 1]
  set t [expr {[lindex $t 0]/1000000.0}]
  puts [format $format {SQLite (test):} $t]
  exec sync; after $delay;
  set t [time "exec ./sqlite-base sb2.db <$sqlfile" 1]
  set t [expr {[lindex $t 0]/1000000.0}]
  puts [format $format {SQLite (base):} $t]
  exec sync; after $delay;
  set t [time "exec ./sqlite-test st2.db <$sqlfile" 1]
  set t [expr {[lindex $t 0]/1000000.0}]
  puts [format $format {SQLite (test):} $t]
  exec sync; after $delay;
  set t [time "exec ./sqlite-base sb3.db <$sqlfile" 1]
  set t [expr {[lindex $t 0]/1000000.0}]
  puts [format $format {SQLite (base):} $t]
  exec sync; after $delay;
  set t [time "exec ./sqlite-test st3.db <$sqlfile" 1]
  set t [expr {[lindex $t 0]/1000000.0}]
  puts [format $format {SQLite (test):} $t]
  puts ""
  set sum [exec sqlite sb1.db .dump | md5sum]
  foreach f {st1.db sb2.db st2.db sb3.db st3.db} {
    if {[exec sqlite $f .dump | md5sum]!=$sum} {
      puts "**** MD5 checksum differs for files $f and sb1.db"
    }
  }
}

# Initialize the environment
#
expr srand(1)
catch {exec /bin/sh -c {rm -f s*.db}}
set fd [open clear.sql w]
puts $fd {
  drop table t1;
  drop table t2;
}
close $fd
set fd [open init.sql w]
puts $fd {
  PRAGMA default_cache_size=2000;
  PRAGMA default_synchronous=off;
}
close $fd
exec ./sqlite-base sb1.db <init.sql
exec ./sqlite-test st1.db <init.sql
exec ./sqlite-base sb2.db <init.sql
exec ./sqlite-test st2.db <init.sql
exec ./sqlite-base sb3.db <init.sql
exec ./sqlite-test st3.db <init.sql
set ones {zero one two three four five six seven eight nine
          ten eleven twelve thirteen fourteen fifteen sixteen seventeen
          eighteen nineteen}
set tens {{} ten twenty thirty forty fifty sixty seventy eighty ninety}
proc number_name {n} {
  if {$n>=1000} {
    set txt "[number_name [expr {$n/1000}]] thousand"
    set n [expr {$n%1000}]
  } else {
    set txt {}
  }
  if {$n>=100} {
    append txt " [lindex $::ones [expr {$n/100}]] hundred"
    set n [expr {$n%100}]
  }
  if {$n>=20} {
    append txt " [lindex $::tens [expr {$n/10}]]"
    set n [expr {$n%10}]
  }
  if {$n>0} {
    append txt " [lindex $::ones $n]"
  }
  set txt [string trim $txt]
  if {$txt==""} {set txt zero}
  return $txt
}



set fd [open test$cnt.sql w]
puts $fd "CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100));"
for {set i 1} {$i<=5000} {incr i} {
  set r [expr {int(rand()*100000)}]
  puts $fd "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');"
}
close $fd
runtest {5000 INSERTs}



set fd [open test$cnt.sql w]
puts $fd "BEGIN;"
puts $fd "CREATE TABLE t2(a INTEGER, b INTEGER, c VARCHAR(100));"
for {set i 1} {$i<=50000} {incr i} {
  set r [expr {int(rand()*500000)}]
  puts $fd "INSERT INTO t2 VALUES($i,$r,'[number_name $r]');"
}
puts $fd "COMMIT;"
close $fd
runtest {50000 INSERTs in a transaction}

set fd [open test$cnt.sql w]
for {set i 0} {$i<50} {incr i} {
  set lwr [expr {$i*100}]
  set upr [expr {($i+10)*100}]
  puts $fd "SELECT count(*), avg(b) FROM t2 WHERE b>=$lwr AND b<$upr;"
}
close $fd
runtest {50 SELECTs without an index}



set fd [open test$cnt.sql w]
for {set i 1} {$i<=50} {incr i} {
  puts $fd "SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%[number_name $i]%';"
}
close $fd
runtest {50 SELECTs on a string comparison}



set fd [open test$cnt.sql w]
puts $fd {CREATE INDEX i2a ON t2(a);}
puts $fd {CREATE INDEX i2b ON t2(b);}
puts $fd {CREATE INDEX i2ab ON t2(a,b);}
puts $fd {CREATE INDEX i2ba ON t2(b,a);}
puts $fd {DROP INDEX i2ab;}
puts $fd {DROP INDEX i2ba;}
close $fd
runtest {Creating an index}



set fd [open test$cnt.sql w]
for {set i 0} {$i<5000} {incr i} {
  set lwr [expr {$i*100}]
  set upr [expr {($i+1)*100}]
  puts $fd "SELECT count(*), avg(b) FROM t2 WHERE b>=$lwr AND b<$upr;"
}
close $fd
runtest {5000 SELECTs with an index}



set fd [open test$cnt.sql w]
puts $fd "BEGIN;"
for {set i 0} {$i<1000} {incr i} {
  set lwr [expr {$i*10}]
  set upr [expr {($i+1)*10}]
  puts $fd "UPDATE t1 SET b=b*2 WHERE a>=$lwr AND a<$upr;"
}
puts $fd "COMMIT;"
close $fd
runtest {1000 UPDATEs without an index}



set fd [open test$cnt.sql w]
puts $fd "BEGIN;"
for {set i 1} {$i<=25000} {incr i} {
  set r [expr {int(rand()*500000)}]
  puts $fd "UPDATE t2 SET b=$r WHERE a=$i;"
}
puts $fd "COMMIT;"
close $fd
runtest {25000 UPDATEs with an index}


set fd [open test$cnt.sql w]
puts $fd "BEGIN;"
for {set i 1} {$i<=25000} {incr i} {
  set r [expr {int(rand()*500000)}]
  puts $fd "UPDATE t2 SET c='[number_name $r]' WHERE a=$i;"
}
puts $fd "COMMIT;"
close $fd
runtest {25000 text UPDATEs with an index}



set fd [open test$cnt.sql w]
puts $fd "BEGIN;"
puts $fd "INSERT INTO t1 SELECT * FROM t2;"
puts $fd "INSERT INTO t2 SELECT * FROM t1;"
puts $fd "COMMIT;"
close $fd
runtest {INSERTs from a SELECT}



set fd [open test$cnt.sql w]
puts $fd {DELETE FROM t2 WHERE c LIKE '%fifty%';}
close $fd
runtest {DELETE without an index}



set fd [open test$cnt.sql w]
puts $fd {DELETE FROM t2 WHERE a>10 AND a<20000;}
close $fd
runtest {DELETE with an index}



set fd [open test$cnt.sql w]
puts $fd {INSERT INTO t2 SELECT * FROM t1;}
close $fd
runtest {A big INSERT after a big DELETE}



set fd [open test$cnt.sql w]
puts $fd {BEGIN;}
puts $fd {DELETE FROM t1;}
for {set i 1} {$i<=20000} {incr i} {
  set r [expr {int(rand()*100000)}]
  puts $fd "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');"
}
puts $fd {COMMIT;}
close $fd
runtest {A big DELETE followed by many small INSERTs}



set fd [open test$cnt.sql w]
puts $fd {DROP TABLE t1;}
puts $fd {DROP TABLE t2;}
close $fd
runtest {DROP TABLE}