/ Check-in [282dae7e]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Add tests to e_select.test.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 282dae7edf7209197fffb6c58b038b3aae1a0367
User & Date: dan 2010-09-07 19:05:28
Context
2010-09-07
19:10
Update the sqlite3_limit() documentation to explain that SQLITE_LIMIT_VDBE_OP is not enforced. check-in: 17be9bee user: drh tags: trunk
19:05
Add tests to e_select.test. check-in: 282dae7e user: dan tags: trunk
14:59
Revised documentation for sqlite3_limit(). Added some evidence marks and assert() statements to verify sqlite3_limit() behavior. check-in: 883b9b74 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to test/e_select.test.

   351    351     3 {SELECT * FROM t1 NATURAL LEFT JOIN t2 ON (45)}
   352    352   } {
   353    353     do_catchsql_test e_select-1.12.$tn "
   354    354       $sql
   355    355     " {1 {a NATURAL join may not have an ON or USING clause}}
   356    356   }
   357    357   
          358  +
          359  +#-------
          360  +# Usage:   tcl_join <table-data1> <table-data2> <join spec>...
          361  +#
          362  +# Where a join-spec is an optional list of arguments as follows:
          363  +#
          364  +#   ?-left? 
          365  +#   ?-using colname-list using-expr-proc?
          366  +#   ?-on on-expr-proc?
          367  +#
          368  +proc tcl_join {data1 data2 args} {
          369  +
          370  +  set testproc ""
          371  +  set usinglist [list]
          372  +  set isleft 0
          373  +  for {set i 0} {$i < [llength $args]} {incr i} {
          374  +    set a [lindex $args $i]
          375  +    switch -- $a {
          376  +      -on { set testproc [lindex $args [incr i]] }
          377  +      -using { 
          378  +        set usinglist [lindex $args [incr i]]
          379  +      }
          380  +      -left {
          381  +        set isleft 1
          382  +      }
          383  +
          384  +      default {
          385  +        error "Unknown argument: $a"
          386  +      }
          387  +    }
          388  +  }
          389  +
          390  +  set c1 [lindex $data1 0]
          391  +  set c2 [lindex $data2 0]
          392  +  set omitlist [list]
          393  +  set nullrowlist [list]
          394  +  set cret $c1
          395  +
          396  +  set cidx 0
          397  +  foreach col $c2 {
          398  +    set idx [lsearch $usinglist $col]
          399  +    if {$idx>=0} {lappend omitlist $cidx}
          400  +    if {$idx<0} {
          401  +      lappend nullrowlist {NULL {}}
          402  +      lappend cret $col
          403  +    }
          404  +    incr cidx
          405  +  }
          406  +  set omitlist [lsort -integer -decreasing $omitlist]
          407  +
          408  +
          409  +  set rret [list]
          410  +  foreach r1 [lindex $data1 1] {
          411  +    set one 0
          412  +    foreach r2 [lindex $data2 1] {
          413  +      set ok 1
          414  +      if {$testproc != ""} {
          415  +        set ok [eval $testproc [list $c1 $r1 $c2 $r2]]
          416  +      }
          417  +      if {$ok} {
          418  +        set one 1
          419  +        foreach idx $omitlist {set r2 [lreplace $r2 $idx $idx]}
          420  +        lappend rret [concat $r1 $r2]
          421  +      }
          422  +    }
          423  +
          424  +    if {$isleft && $one==0} {
          425  +      lappend rret [concat $r1 $nullrowlist]
          426  +    }
          427  +  }
          428  +  
          429  +  list $cret $rret
          430  +}
          431  +
          432  +proc tcl_tbljoin {db t1 t2 args} {
          433  +  tcl_join [tcl_read_tbl $db $t1] [tcl_read_tbl $db $t2] {*}$args
          434  +}
          435  +
          436  +#----------
          437  +# te_equals ?SWITCHES? c1 c2 cols1 row1 cols2 row2
          438  +#
          439  +proc te_equals {args} {
          440  +
          441  +  if {[llength $args]<6} {error "invalid arguments to te_equals"}
          442  +  foreach {c1 c2 cols1 row1 cols2 row2} [lrange $args end-5 end] break
          443  +
          444  +  set nocase 0
          445  +
          446  +  foreach a [lrange $args 0 end-6] {
          447  +    switch -- $a {
          448  +      -nocase {
          449  +        set nocase 1
          450  +      }
          451  +      default {
          452  +        error "invalid arguments to te_equals"
          453  +      }
          454  +    }
          455  +  }
          456  +
          457  +  set idx1 [lsearch $cols1 $c1]
          458  +  set idx2 [lsearch $cols2 $c2]
          459  +
          460  +  set t1 [lindex $row1 $idx1 0]
          461  +  set t2 [lindex $row2 $idx2 0]
          462  +  set v1 [lindex $row1 $idx1 1]
          463  +  set v2 [lindex $row2 $idx2 1]
          464  +
          465  +  if {$t1 == "NULL" || $t2 == "NULL"} { return 0 }
          466  +  if {$nocase && $t1 == "TEXT"} { set v1 [string tolower $v1] }
          467  +  if {$nocase && $t2 == "TEXT"} { set v2 [string tolower $v2] }
          468  +  return [expr {$t1 == $t2 && $v1 == $v2}]
          469  +}
          470  +
          471  +proc te_and {args} {
          472  +  foreach a [lrange $args 0 end-4] {
          473  +    set res [eval $a [lrange $args end-3 end]]
          474  +    if {$res == 0} {return 0}
          475  +  }
          476  +  return 1
          477  +}
          478  +
          479  +# Read the
          480  +#
          481  +# Table data format:
          482  +#
          483  +#   * List of column names.
          484  +#
          485  +#   * List of rows. Each row is a list of values. Each value is a list of
          486  +#     2 elements - the value type and string representation.
          487  +#
          488  +proc tcl_read_tbl {db tbl} { tcl_read_sql $db "SELECT * FROM $tbl" }
          489  +
          490  +proc tcl_read_sql {db sql} {
          491  +  set S [sqlite3_prepare_v2 $db $sql -1 DUMMY]
          492  +
          493  +  set cols [list]
          494  +  for {set i 0} {$i < [sqlite3_column_count $S]} {incr i} {
          495  +    lappend cols [sqlite3_column_name $S $i]
          496  +  }
          497  +
          498  +  set rows [list]
          499  +  while {[sqlite3_step $S] == "SQLITE_ROW"} {
          500  +    set r [list]
          501  +    for {set i 0} {$i < [sqlite3_column_count $S]} {incr i} {
          502  +      lappend r [list [sqlite3_column_type $S $i] [sqlite3_column_text $S $i]]
          503  +    }
          504  +    lappend rows $r
          505  +  }
          506  +  sqlite3_finalize $S
          507  +
          508  +  return [list $cols $rows]
          509  +}
          510  +
          511  +drop_all_tables
          512  +do_execsql_test e_select-2.0 {
          513  +  CREATE TABLE t1(a, b);
          514  +  CREATE TABLE t2(a, b);
          515  +  CREATE TABLE t3(b COLLATE nocase);
          516  +
          517  +  INSERT INTO t1 VALUES(1, 'A');
          518  +  INSERT INTO t1 VALUES(2, 'B');
          519  +  INSERT INTO t1 VALUES(3, NULL);
          520  +  INSERT INTO t1 VALUES(4, 'D');
          521  +  INSERT INTO t1 VALUES(NULL, NULL);
          522  +
          523  +  INSERT INTO t2 VALUES(1, 'A');
          524  +  INSERT INTO t2 VALUES(2, NULL);
          525  +  INSERT INTO t2 VALUES(3, 'C');
          526  +  INSERT INTO t2 VALUES(5, 'E');
          527  +  INSERT INTO t2 VALUES(NULL, NULL);
          528  +
          529  +  INSERT INTO t3 VALUES('a');
          530  +  INSERT INTO t3 VALUES('b');
          531  +  INSERT INTO t3 VALUES('c');
          532  +} {}
          533  +
          534  +foreach {tn sqljoin tbljoinargs} {
          535  +  1     "t1, t2"                         {t1 t2}
          536  +  2     "t1, t2 ON (t1.a=t2.a)"          {t1 t2 -on {te_equals a a}}
          537  +  3     "t1 LEFT JOIN t2 ON (t1.a=t2.a)" {t1 t2 -left -on {te_equals a a}}
          538  +  4     "t1 LEFT JOIN t2 USING (a)"      
          539  +          {t1 t2 -left -using a -on {te_equals a a}}
          540  +
          541  +  5     "t1 CROSS JOIN t2 USING(b, a)" 
          542  +        {t1 t2 -using {a b} -on {te_and {te_equals a a} {te_equals b b}}}
          543  +
          544  +  6     "t1 NATURAL JOIN t2" 
          545  +        {t1 t2 -using {a b} -on {te_and {te_equals a a} {te_equals b b}}}
          546  +  7     "t1 NATURAL INNER JOIN t2" 
          547  +        {t1 t2 -using {a b} -on {te_and {te_equals a a} {te_equals b b}}}
          548  +  8     "t1 NATURAL CROSS JOIN t2" 
          549  +        {t1 t2 -using {a b} -on {te_and {te_equals a a} {te_equals b b}}}
          550  +  9     "t1 NATURAL INNER JOIN t2" 
          551  +        {t1 t2 -using {a b} -on {te_and {te_equals a a} {te_equals b b}}}
          552  +  10    "t1 NATURAL LEFT JOIN t2" 
          553  +        {t1 t2 -left -using {a b} -on {te_and {te_equals a a} {te_equals b b}}}
          554  +  11    "t1 NATURAL LEFT OUTER JOIN t2" 
          555  +        {t1 t2 -left -using {a b} -on {te_and {te_equals a a} {te_equals b b}}}
          556  +
          557  +  12    "t2 NATURAL JOIN t1" 
          558  +        {t2 t1 -using {a b} -on {te_and {te_equals a a} {te_equals b b}}}
          559  +  13    "t2 NATURAL INNER JOIN t1" 
          560  +        {t2 t1 -using {a b} -on {te_and {te_equals a a} {te_equals b b}}}
          561  +  14    "t2 NATURAL CROSS JOIN t1" 
          562  +        {t2 t1 -using {a b} -on {te_and {te_equals a a} {te_equals b b}}}
          563  +  15    "t2 NATURAL INNER JOIN t1" 
          564  +        {t2 t1 -using {a b} -on {te_and {te_equals a a} {te_equals b b}}}
          565  +  16    "t2 NATURAL LEFT JOIN t1" 
          566  +        {t2 t1 -left -using {a b} -on {te_and {te_equals a a} {te_equals b b}}}
          567  +  17    "t2 NATURAL LEFT OUTER JOIN t1" 
          568  +        {t2 t1 -left -using {a b} -on {te_and {te_equals a a} {te_equals b b}}}
          569  +
          570  +  18    "t1 LEFT JOIN t2 USING (b)"      
          571  +        {t1 t2 -left -using b -on {te_equals b b}}
          572  +
          573  +  19    "t1 JOIN t3 USING(b)" {t1 t3 -using b -on {te_equals b b}}
          574  +  20    "t3 JOIN t1 USING(b)" {t3 t1 -using b -on {te_equals -nocase b b}}
          575  +  21    "t1 NATURAL JOIN t3"  {t1 t3 -using b -on {te_equals b b}}
          576  +  22    "t3 NATURAL JOIN t1"  {t3 t1 -using b -on {te_equals -nocase b b}}
          577  +  23    "t1 NATURAL LEFT JOIN t3"  {t1 t3 -left -using b -on {te_equals b b}}
          578  +  24    "t3 NATURAL LEFT JOIN t1"  
          579  +        {t3 t1 -left -using b -on {te_equals -nocase b b}}
          580  +
          581  +  25    "t1 LEFT JOIN t3 ON (t3.b=t1.b)" 
          582  +        {t1 t3 -left -on {te_equals -nocase b b}}
          583  +  26    "t1 LEFT JOIN t3 ON (t1.b=t3.b)"  
          584  +        {t1 t3 -left -on {te_equals b b}}
          585  +
          586  +} {
          587  +
          588  +  do_test e_select-2.1.$tn [list tcl_read_sql db "SELECT * FROM $sqljoin"
          589  +  ] [tcl_tbljoin db {*}$tbljoinargs]
          590  +
          591  +}
          592  +
   358    593   finish_test
          594  +