sqllogictest: Artifact Content

Artifact 83186f4f0f7277f7a623730a877b3d5c8120e91d:

File proto/select4.tcl — part of check-in [e9c99b6893] at 2008-12-02 23:08:46 on branch trunk — Extend the select4 test script to do lots of multi-way joins. (user: drh size: 8354)
#!/usr/bin/tclsh
#
# Run this script to generate a larger prototype test script for
# sqllogictest.
#
# Nine separate tables, T1 through T9, each with integer data.
# Data for table T1 is values in the range 1000..1999.  Data for
# table T2 is values in the range 2000.2999.  And so forth.  The
# data is random.
#
expr {srand(0)}
package require sqlite3
sqlite3 db :memory:

# Scramble the $inlist list into a random order.
#
proc scramble {inlist} {
  set y {}
  foreach x $inlist {
    lappend y [list [expr {rand()}] $x]
  }
  set y [lsort $y]
  set outlist {}
  foreach x $y {
    lappend outlist [lindex $x 1]
  }
  return $outlist
}

# Construct the schema.  9 tables, each with 5 integer columns and one
# text column.
#
for {set tn 1} {$tn<=9} {incr tn} {
  puts {statement ok}
  set sql [subst {CREATE TABLE t${tn}(
  a$tn INTEGER,
  b$tn INTEGER,
  c$tn INTEGER,
  d$tn INTEGER,
  e$tn INTEGER,
  x$tn VARCHAR(30)
)}]
  puts $sql
  db eval $sql
  puts {}
}

# Populate the tables with data
#
for {set tn 1} {$tn<=9} {incr tn} {set nrow($tn) 0}
for {set i 0} {$i<1000} {incr i} {
  set tn [expr {int(rand()*9)+1}]
  set base 0 ;# [expr {$tn*1000}]
  incr nrow($tn)
  set x "table tn$tn row $nrow($tn)"
  foreach column {a b c d e} {
    set v [expr {$base+int(rand()*999)}]
    lappend cdata($column$tn) $v
    set $column $v
  }
  lappend tdata($tn) [list $a $b $c $d $e $x]
  set sql "INSERT INTO t$tn VALUES($a,$b,$c,$d,$e,'$x')"
  db eval $sql
  puts "statement ok\n$sql\n"
}

# Queries to make sure all the data got in correctly.
#
for {set tn 1} {$tn<=9} {incr tn} {
  puts "query IIIIIT rowsort all$tn\nSELECT * FROM t$tn\n"
}

# Create indices
#
# t1 gets prefix indices...
#
for {set i 0} {$i<5} {incr i} {
  puts "statement ok"
  set sql \
    "CREATE INDEX t1i$i ON t1([join [lrange {a1 b1 c1 d1 e1 x1} $i end] ,])"
  puts "$sql\n"
  db eval $sql
}
set fast(t1) {a1} ;# {a1 b1} {a1 b1 c1} {a1 b1 c1 d1} {a1 b1 c1 d3 e1}}
# t2 gets a separate index on each column
#
foreach c {a2 b2 c2 d2 e2} {
  puts "statement ok"
  set sql "CREATE INDEX t2$c ON t2($c)"
  puts "$sql\n"
  db eval $sql
}
set fast(t2) {a2 b2 c2 d2 e2}
# t3 through t7 get a single index on one column
#
for {set i 0} {$i<5} {incr i} {
  set tn [expr {$i+3}]
  set cn [string index {abcde} $i]$tn
  puts "statement ok"
  set sql "CREATE INDEX t$tn$cn ON t${tn}($cn)"
  puts "$sql\n"
  db eval $sql
  set fast(t$tn) $cn
}
# t8 gets a single reverse-order index.
#
puts "statement ok"
set sql "CREATE INDEX t8all ON t8(e8 DESC, d8 ASC, c8 DESC, b8 ASC, a8 DESC)"
db eval $sql
puts "$sql\n"
set fast(t8) {e8} ;# {e8 d8} {e8 d8 c8} {e8 d8 c8 b8} {e8 d8 c8 b8 a8}}
# t9 does not get an index.
#
set fast(t9) {}

# Repeat the data dumps.  The table contents should not have changed.
#
for {set tn 1} {$tn<=9} {incr tn} {
  puts "query IIIIIT rowsort all$tn\nSELECT * FROM t$tn\n"
}

# Return a list of one or more WHERE clause terms that will restrict
# the number of rows obtained from table $tn to a handful.
#
proc few_row_where {tn} {
  global cdata tdata
  set w {}
  set n [expr {int(rand()*3)+1}]
  for {set i 0} {$i<$n} {incr i} {
    set p [expr {rand()}]
    if {$p<0.3333} {
      set cn [string index abcde [expr {int(rand()*5)}]]$tn
      set m [expr {int(rand()*12)+2}]
      set term "$cn in ([join [lrange [scramble $::cdata($cn)] 0 $m] ,])"
    } elseif {$p<0.6666} {
      set nrow [llength $tdata($tn)]
      set idx [expr {int(rand()*$nrow)}]
      set row [lindex $tdata($tn) $idx]
      set m [expr {int(rand()*5)}]
      set r [lrange [scramble {0 1 2 3 4}] 0 $m]
      set term {}
      set conn (
      foreach k $r {
        set cn [string index abcde $k]$tn
        set vx [lindex $row $k]
        if {rand()<0.5} {
          append term $conn$vx=$cn
        } else {
          append term $conn$cn=$vx
        }
        set conn " AND "
      }
      append term )
    } else {
      set m [expr {int(rand()*3)+1}]
      set term {}
      set conn (
      for {set k 0} {$k<$m} {incr k} {
        set cn [string index abcde [expr {int(rand()*5)}]]$tn
        set nv [llength $cdata($cn)]
        set idx [expr {int(rand()*$nv)}]
        set vx [lindex $cdata($cn) $idx]
        if {rand()<0.5} {
          append term $conn$vx=$cn
        } else {
          append term $conn$cn=$vx
        }
        set conn " OR "
      }
      append term )
    }
    lappend w $term
  }
  return $w
}

# Do lots of compound queries across multiple tables.
#
for {set i 0} {$i<1000} {incr i} {
  set n [expr {int(rand()*8)+1}]
  set tnset [lrange [scramble {1 2 3 4 5 6 7 8 9}] 0 $n]
  set sql {}
  set j 0
  foreach tn $tnset {
    incr j
    set want_many 0
    if {[string length $sql]>0} {
      set p [expr {rand()}]
      if {$p<0.25} {
        append sql "UNION\n"
      } elseif {$p<0.5} {
        append sql "UNION ALL\n"
      } elseif {$p<0.75 || $j>2} {
        append sql "EXCEPT\n"
        set want_many 1
      } else {
        append sql "INTERSECT\n"
        incr j -1
        set want_many 1
      }
    }
    set cn [string index abcde [expr {int(rand()*5)}]]$tn
    append sql "  SELECT $cn FROM t$tn\n"
    if {$want_many} {
      set w [few_row_where $tn]
      set op     "\n           OR "
      append sql "   WHERE NOT ([join $w $op])\n"
    } else {
      set w [few_row_where $tn]
      set op "\n      OR "
      append sql "   WHERE [join $w $op]\n"
    }
  }
  puts "query T valuesort\n$sql" 
}

# Return a single WHERE clause term that restricts table $tn to just
# a handful or rows.  Try to use an index.
#
# Feel free to use columns from any tables in $tnset as right-hand side.
#
proc oneortwo_where_term {tn tnset} {
  global cdata fast
  set n [llength $fast(t$tn)]
  if {$n==0} {
    set cn [string index abcde [expr {int(rand()*5)}]]$tn
  } else {
    set cn [lindex [scramble $fast(t$tn)] 0]
  }
  set ntnset [llength $tnset]
  set rhs [lindex $tnset [expr {int(rand()*$ntnset)}]]
  if {$rhs==$tn} {set rhs 0}
  set p [expr {rand()}]
  if {$p<0.3333} {
    set m [expr {int(rand()*6)+2}]
    set term "$cn in ([join [lrange [scramble $::cdata($cn)] 0 $m] ,])"
  } elseif {$p<0.6666 && $rhs>0} {
    set rcn [string index abcde [expr {int(rand()*5)}]]$rhs
    set term "$cn=$rcn"
  } else {
    if {$p<0.9} {
      set m 1
      set conn ""
    } else {
      set m [expr {int(rand()*3)+2}]
      set conn (
    }
    set term {}
    for {set k 0} {$k<$m} {incr k} {
      set nv [llength $cdata($cn)]
      set idx [expr {int(rand()*$nv)}]
      set vx [lindex $cdata($cn) $idx]
      if {rand()<0.5} {
        append term $conn$vx=$cn
      } else {
        append term $conn$cn=$vx
      }
      set conn " OR "
    }
    if {$m>1} {
      append term )
    }
  }
  return $term
}

# Do some joins
#
for {set i 0} {$i<500} {incr i} {
  while {1} {
    set n [expr {int(rand()*7)+1}]
    set tnset [lrange [scramble {1 2 3 4 5 6 7 8 9}] 0 $n]
    set rset {}
    set typestr {}
    set w {}
    set tnnames {}
    foreach tn $tnset {
      lappend tnnames t$tn
      set cn [string index abcdex [expr {int(rand()*6)}]]
      if {$cn=="x"} {
        append typestr T
        append cn $tn
      } else {
        append typestr I
        append cn $tn
        set p [expr {rand()}]
        if {$p<0.2} {
          append cn "*[expr {int(rand()*1000)+1}]"
        } elseif {$p<0.4} {
          append cn "+[expr {int(rand()*1000)+1}]"
        }
        if {rand()<0.3} {
          set tn2 [lindex $tnset [expr {int(rand()*$n)}]]
          set cn2 [string index abcde [expr {int(rand()*5)}]]
          append cn +$cn2$tn2
        }
      }
      lappend rset $cn
      lappend w [oneortwo_where_term $tn $tnset]
    }
    set sql "SELECT count(*) FROM [join $tnnames ,] WHERE [join $w { AND }]"
    append sql " LIMIT [expr {(100000+$n*2)/$n}]"
    set njoin [db eval $sql]
    if {$njoin<1 || $njoin*$n>100000} {
      puts -nonewline stderr .
      continue
    }
    puts stderr "join-$i is [expr {$n+1}]-way has $njoin rows"
  
    unset -nocomplain seen
    for {set j 0} {$j<4} {incr j} {
      set sql "SELECT [join $rset {, }]\n"
      append sql "  FROM [join [scramble $tnnames] {, }]\n"
      append sql " WHERE [join [scramble $w] "\n   AND "]"
      if {[info exists seen($sql)]} continue
      set seen($sql) 1
      puts "query $typestr rowsort join$i"
      puts $sql
      puts ""
    }
    break
  }
}