/ Check-in [269bf52e]
Login

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

Overview
Comment:Remove the tool/schemalint.tcl script. And related Makefile entries. It is superseded by sqlite3_expert.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | schemalint
Files: files | file ages | folders
SHA3-256: 269bf52e27611cd00fa7f73ee98b395a30dec215232cf1c34f6b741112ba530b
User & Date: dan 2017-05-04 14:02:05
Context
2017-05-15
17:56
Merge changes from trunk. check-in: 6e0f64ab user: drh tags: schemalint
2017-05-04
14:02
Remove the tool/schemalint.tcl script. And related Makefile entries. It is superseded by sqlite3_expert. check-in: 269bf52e user: dan tags: schemalint
2017-05-03
13:05
Fix a harmless compiler warning on Windows. check-in: 593e5dd0 user: drh tags: schemalint
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to Makefile.in.

  1156   1156   
  1157   1157   sqlite3_analyzer$(TEXE): sqlite3_analyzer.c
  1158   1158   	$(LTLINK) sqlite3_analyzer.c -o $@ $(LIBTCL) $(TLIBS)
  1159   1159   
  1160   1160   sqlite3_expert$(TEXE): $(TOP)/ext/expert/sqlite3expert.h $(TOP)/ext/expert/sqlite3expert.c $(TOP)/ext/expert/expert.c sqlite3.c
  1161   1161   	$(LTLINK)	$(TOP)/ext/expert/sqlite3expert.h $(TOP)/ext/expert/sqlite3expert.c $(TOP)/ext/expert/expert.c sqlite3.c -o sqlite3_expert $(TLIBS)
  1162   1162   
  1163         -sqlite3_schemalint.c: sqlite3.c $(TOP)/src/tclsqlite.c $(TOP)/tool/schemalint.tcl
  1164         -	echo "#define TCLSH 2" > $@
  1165         -	echo "#define SQLITE_ENABLE_DBSTAT_VTAB 1" >> $@
  1166         -	cat sqlite3.c $(TOP)/src/tclsqlite.c >> $@
  1167         -	echo "static const char *tclsh_main_loop(void){" >> $@
  1168         -	echo "static const char *zMainloop = " >> $@
  1169         -	$(TCLSH_CMD) $(TOP)/tool/tostr.tcl $(TOP)/tool/schemalint.tcl >> $@
  1170         -	echo "; return zMainloop; }" >> $@
  1171         -
  1172         -sqlite3_schemalint$(TEXE): $(TESTSRC) sqlite3_schemalint.c
  1173         -	$(LTLINK) $(TCL_FLAGS) $(TESTFIXTURE_FLAGS)                  \
  1174         -		sqlite3_schemalint.c $(TESTSRC)               \
  1175         -		-o sqlite3_schemalint$(EXE) $(LIBTCL) $(TLIBS)
  1176         -
  1177   1163   dbdump$(TEXE): $(TOP)/ext/misc/dbdump.c sqlite3.lo
  1178   1164   	$(LTLINK) -DDBDUMP_STANDALONE -o $@ \
  1179   1165              $(TOP)/ext/misc/dbdump.c sqlite3.lo $(TLIBS)
  1180   1166   
  1181   1167   showdb$(TEXE):	$(TOP)/tool/showdb.c sqlite3.lo
  1182   1168   	$(LTLINK) -o $@ $(TOP)/tool/showdb.c sqlite3.lo $(TLIBS)
  1183   1169   

Changes to Makefile.msc.

  2182   2182   sqlite3_analyzer.exe:	sqlite3_analyzer.c $(LIBRESOBJS)
  2183   2183   	$(LTLINK) $(NO_WARN) -DBUILD_sqlite -I$(TCLINCDIR) sqlite3_analyzer.c \
  2184   2184   		/link $(LDFLAGS) $(LTLINKOPTS) $(LTLIBPATHS) $(LIBRESOBJS) $(LTLIBS) $(TLIBS)
  2185   2185   
  2186   2186   sqlite3_expert.exe: $(SQLITE3C) $(TOP)\ext\expert\sqlite3expert.h $(TOP)\ext\expert\sqlite3expert.c $(TOP)\ext\expert\expert.c
  2187   2187   	$(LTLINK) $(NO_WARN)	$(TOP)\ext\expert\sqlite3expert.c $(TOP)\ext\expert\expert.c $(SQLITE3C) $(TLIBS)
  2188   2188   
  2189         -sqlite3_schemalint.c: $(SQLITE3C) $(SQLITE3H) $(TOP)\src\tclsqlite.c $(TOP)\tool\schemalint.tcl $(SQLITE_TCL_DEP)
  2190         -	echo "#define TCLSH 2" > $@
  2191         -	echo "#define SQLITE_ENABLE_DBSTAT_VTAB 1" >> $@
  2192         -	copy $@ + $(SQLITE3C) + $(TOP)\src\tclsqlite.c >> $@
  2193         -	echo "static const char *tclsh_main_loop(void){" >> $@
  2194         -	echo "static const char *zMainloop = " >> $@
  2195         -	$(TCLSH_CMD) $(TOP)\tool\tostr.tcl $(TOP)\tool\schemalint.tcl >> $@
  2196         -	echo "; return zMainloop; }" >> $@
  2197         -
  2198         -sqlite3_schemalint.exe: $(TESTSRC) sqlite3_schemalint.c
  2199         -	$(LTLINK) $(NO_WARN) -DBUILD_sqlite -I$(TCLINCDIR) sqlite3_schemalint.c \
  2200         -		/link $(LDFLAGS) $(LTLINKOPTS) $(LTLIBPATHS) $(LIBRESOBJS) $(LTLIBS) $(TLIBS)
  2201         -
  2202   2189   dbdump.exe:	$(TOP)\ext\misc\dbdump.c $(SQLITE3C) $(SQLITE3H)
  2203   2190   	$(LTLINK) $(NO_WARN) -DDBDUMP_STANDALONE $(TOP)\ext\misc\dbdump.c $(SQLITE3C) \
  2204   2191   		/link $(LDFLAGS) $(LTLINKOPTS) $(LTLIBPATHS) $(LIBRESOBJS) $(LTLIBS)
  2205   2192   
  2206   2193   testloadext.lo:	$(TOP)\src\test_loadext.c
  2207   2194   	$(LTCOMPILE) $(NO_WARN) -c $(TOP)\src\test_loadext.c
  2208   2195   

Changes to main.mk.

   762    762   
   763    763   sqlite3_analyzer$(EXE): sqlite3_analyzer.c
   764    764   	$(TCCX) $(TCL_FLAGS) sqlite3_analyzer.c -o $@ $(LIBTCL) $(THREADLIB) 
   765    765   
   766    766   sqlite3_expert$(EXE): $(TOP)/ext/expert/sqlite3expert.h $(TOP)/ext/expert/sqlite3expert.c $(TOP)/ext/expert/expert.c sqlite3.c
   767    767   	$(TCCX) $(TOP)/ext/expert/sqlite3expert.h $(TOP)/ext/expert/sqlite3expert.c $(TOP)/ext/expert/expert.c sqlite3.c -o sqlite3_expert $(THREADLIB)
   768    768   
   769         -sqlite3_schemalint.c: sqlite3.c $(TOP)/src/tclsqlite.c $(TOP)/tool/schemalint.tcl
   770         -	echo "#define TCLSH 2" > $@
   771         -	echo "#define SQLITE_ENABLE_DBSTAT_VTAB 1" >> $@
   772         -	cat sqlite3.c $(TOP)/src/tclsqlite.c >> $@
   773         -	echo "static const char *tclsh_main_loop(void){" >> $@
   774         -	echo "static const char *zMainloop = " >> $@
   775         -	tclsh $(TOP)/tool/tostr.tcl $(TOP)/tool/schemalint.tcl >> $@
   776         -	echo "; return zMainloop; }" >> $@
   777         -
   778         -sqlite3_schemalint$(EXE): $(TESTSRC) sqlite3_schemalint.c $(TOP)/ext/session/test_session.c
   779         -	$(TCCX) $(TCL_FLAGS) $(TESTFIXTURE_FLAGS)                  \
   780         -		sqlite3_schemalint.c $(TESTSRC) $(TOP)/ext/session/test_session.c  \
   781         -		-o sqlite3_schemalint$(EXE) $(LIBTCL) $(THREADLIB)
   782         -
   783    769   dbdump$(EXE):	$(TOP)/ext/misc/dbdump.c sqlite3.o
   784    770   	$(TCCX) -DDBDUMP_STANDALONE -o dbdump$(EXE) \
   785    771               $(TOP)/ext/misc/dbdump.c sqlite3.o $(THREADLIB)
   786    772   
   787    773   # Rules to build the 'testfixture' application.
   788    774   #
   789    775   TESTFIXTURE_FLAGS  = -DSQLITE_TEST=1 -DSQLITE_CRASH_TEST=1

Deleted tool/schemalint.tcl.

     1         -if {[catch {
     2         -
     3         -set ::VERBOSE 0
     4         -
     5         -proc usage {} {
     6         -  puts stderr "Usage: $::argv0 ?SWITCHES? DATABASE/SCHEMA"
     7         -  puts stderr "  Switches are:"
     8         -  puts stderr "  -select SQL     (recommend indexes for SQL statement)"
     9         -  puts stderr "  -verbose        (increase verbosity of output)"
    10         -  puts stderr "  -test           (run internal tests and then exit)"
    11         -  puts stderr ""
    12         -  exit
    13         -}
    14         -
    15         -# Return the quoted version of identfier $id. Quotes are only added if 
    16         -# they are required by SQLite.
    17         -#
    18         -# This command currently assumes that quotes are required if the 
    19         -# identifier contains any ASCII-range characters that are not 
    20         -# alpha-numeric or underscores.
    21         -#
    22         -proc quote {id} {
    23         -  if {[requires_quote $id]} {
    24         -    set x [string map {\" \"\"} $id]
    25         -    return "\"$x\""
    26         -  }
    27         -  return $id
    28         -}
    29         -proc requires_quote {id} {
    30         -  foreach c [split $id {}] {
    31         -    if {[string is alnum $c]==0 && $c!="_"} {
    32         -      return 1
    33         -    }
    34         -  }
    35         -  return 0
    36         -}
    37         -
    38         -# The argument passed to this command is a Tcl list of identifiers. The
    39         -# value returned is the same list, except with each item quoted and the
    40         -# elements comma-separated.
    41         -#
    42         -proc list_to_sql {L} {
    43         -  set ret [list]
    44         -  foreach l $L {
    45         -    lappend ret [quote $l]
    46         -  }
    47         -  join $ret ", "
    48         -}
    49         -
    50         -proc readfile {zFile} {
    51         -  set fd [open $zFile]
    52         -  set data [read $fd]
    53         -  close $fd
    54         -  return $data
    55         -}
    56         -
    57         -proc process_cmdline_args {ctxvar argv} {
    58         -  upvar $ctxvar G
    59         -  set nArg [llength $argv]
    60         -  set G(database) [lindex $argv end]
    61         -
    62         -  for {set i 0} {$i < [llength $argv]-1} {incr i} {
    63         -    set k [lindex $argv $i]
    64         -    switch -- $k {
    65         -      -select {
    66         -        incr i
    67         -        if {$i>=[llength $argv]-1} usage
    68         -        set zSelect [lindex $argv $i]
    69         -        if {[file readable $zSelect]} {
    70         -          lappend G(lSelect) [readfile $zSelect]
    71         -        } else {
    72         -          lappend G(lSelect) $zSelect
    73         -        }
    74         -      }
    75         -      -verbose {
    76         -        set ::VERBOSE 1
    77         -      }
    78         -      -test {
    79         -        sqlidx_internal_tests
    80         -      }
    81         -      default {
    82         -        usage
    83         -      }
    84         -    }
    85         -  }
    86         -
    87         -  if {$G(database)=="-test"} {
    88         -    sqlidx_internal_tests
    89         -  }
    90         -}
    91         -
    92         -proc open_database {ctxvar} {
    93         -  upvar $ctxvar G
    94         -  sqlite3 db ""
    95         -
    96         -  # Check if the "database" file is really an SQLite database. If so, copy
    97         -  # it into the temp db just opened. Otherwise, assume that it is an SQL
    98         -  # schema and execute it directly.
    99         -  set fd [open $G(database)]
   100         -  set hdr [read $fd 16]
   101         -  if {$hdr == "SQLite format 3\000"} {
   102         -    close $fd
   103         -    sqlite3 db2 $G(database)
   104         -    sqlite3_backup B db main db2 main
   105         -    B step 2000000000
   106         -    set rc [B finish]
   107         -    db2 close
   108         -    if {$rc != "SQLITE_OK"} { error "Failed to load database $G(database)" }
   109         -  } else {
   110         -    append hdr [read $fd]
   111         -    db eval $hdr
   112         -    close $fd
   113         -  }
   114         -}
   115         -
   116         -proc analyze_selects {ctxvar} {
   117         -  upvar $ctxvar G
   118         -  set G(trace) ""
   119         -
   120         -  # Collect a line of xTrace output for each loop in the set of SELECT
   121         -  # statements.
   122         -  proc xTrace {zMsg} { 
   123         -    upvar G G
   124         -    lappend G(trace) $zMsg 
   125         -  }
   126         -  db trace xTrace
   127         -  foreach s $G(lSelect) {
   128         -    set stmt [sqlite3_prepare_v2 db $s -1 dummy]
   129         -    set rc [sqlite3_finalize $stmt]
   130         -    if {$rc!="SQLITE_OK"} {
   131         -      error "Failed to compile SQL: [sqlite3_errmsg db]"
   132         -    }
   133         -  }
   134         -
   135         -  db trace ""
   136         -  if {$::VERBOSE} {
   137         -    foreach t $G(trace) { puts "trace: $t" }
   138         -  }
   139         -
   140         -  # puts $G(trace)
   141         -}
   142         -
   143         -# The argument is a list of the form:
   144         -#
   145         -#    key1 {value1.1 value1.2} key2 {value2.1 value 2.2...}
   146         -#
   147         -# Values lists may be of any length greater than zero. This function returns
   148         -# a list of lists created by pivoting on each values list. i.e. a list
   149         -# consisting of the elements:
   150         -#
   151         -#   {{key1 value1.1} {key2 value2.1}}
   152         -#   {{key1 value1.2} {key2 value2.1}}
   153         -#   {{key1 value1.1} {key2 value2.2}}
   154         -#   {{key1 value1.2} {key2 value2.2}}
   155         -#
   156         -proc expand_eq_list {L} {
   157         -  set ll [list {}]
   158         -  for {set i 0} {$i < [llength $L]} {incr i 2} {
   159         -    set key [lindex $L $i]
   160         -    set new [list]
   161         -    foreach piv [lindex $L $i+1] {
   162         -      foreach l $ll {
   163         -        lappend new [concat $l [list [list $key $piv]]]
   164         -      }
   165         -    }
   166         -    set ll $new
   167         -  }
   168         -
   169         -  return $ll
   170         -}
   171         -
   172         -#--------------------------------------------------------------------------
   173         -# Formulate a CREATE INDEX statement that creates an index on table $tname.
   174         -#
   175         -proc eqset_to_index {ctxvar aCollVar tname eqset {range {}}} {
   176         -  upvar $ctxvar G
   177         -  upvar $aCollVar aColl
   178         -
   179         -  set rangeset [list]
   180         -  foreach e [lsort $eqset] {
   181         -    lappend rangeset [lindex $e 0] [lindex $e 1] ASC
   182         -  }
   183         -  set rangeset [concat $rangeset $range]
   184         -
   185         -  set lCols [list]
   186         -  set idxname $tname
   187         -
   188         -  foreach {c collate dir} $rangeset {
   189         -    append idxname "_$c"
   190         -    set coldef [quote $c]
   191         -
   192         -    if {[string compare -nocase $collate $aColl($c)]!=0} {
   193         -      append idxname [string tolower $collate]
   194         -      append coldef " COLLATE [quote $collate]"
   195         -    }
   196         -
   197         -    if {$dir=="DESC"} {
   198         -      append coldef " DESC"
   199         -      append idxname "desc"
   200         -    }
   201         -    lappend lCols $coldef
   202         -  }
   203         -
   204         -  set create_index "CREATE INDEX [quote $idxname] ON [quote $tname]("
   205         -  append create_index [join $lCols ", "]
   206         -  append create_index ");"
   207         -
   208         -  set G(trial.$idxname) $create_index
   209         -}
   210         -
   211         -proc expand_or_cons {L} {
   212         -  set lRet [list [list]]
   213         -  foreach elem $L {
   214         -    set type [lindex $elem 0]
   215         -    if {$type=="eq" || $type=="range"} {
   216         -      set lNew [list]
   217         -      for {set i 0} {$i < [llength $lRet]} {incr i} {
   218         -        lappend lNew [concat [lindex $lRet $i] [list $elem]]
   219         -      }
   220         -      set lRet $lNew
   221         -    } elseif {$type=="or"} {
   222         -      set lNew [list]
   223         -      foreach branch [lrange $elem 1 end] {
   224         -        foreach b [expand_or_cons $branch] {
   225         -          for {set i 0} {$i < [llength $lRet]} {incr i} {
   226         -            lappend lNew [concat [lindex $lRet $i] $b]
   227         -          }
   228         -        }
   229         -      }
   230         -      set lRet $lNew
   231         -    } 
   232         -  }
   233         -  return $lRet
   234         -}
   235         -
   236         -#--------------------------------------------------------------------------
   237         -# Argument $tname is the name of a table in the main database opened by
   238         -# database handle [db]. $arrayvar is the name of an array variable in the
   239         -# caller's context. This command populates the array with an entry mapping 
   240         -# from column name to default collation sequence for each column of table
   241         -# $tname. For example, if a table is declared:
   242         -#
   243         -#   CREATE TABLE t1(a COLLATE nocase, b, c COLLATE binary)
   244         -#
   245         -# the mapping is populated with:
   246         -#
   247         -#   map(a) -> "nocase"
   248         -#   map(b) -> "binary"
   249         -#   map(c) -> "binary"
   250         -#
   251         -proc sqlidx_get_coll_map {tname arrayvar} {
   252         -  upvar $arrayvar aColl
   253         -  set colnames [list]
   254         -  set qname [quote $tname]
   255         -  db eval "PRAGMA table_info = $qname" x { lappend colnames $x(name) }
   256         -  db eval "CREATE INDEX schemalint_test ON ${qname}([list_to_sql $colnames])"
   257         -  db eval "PRAGMA index_xinfo = schemalint_test" x { 
   258         -    set aColl($x(name)) $x(coll)
   259         -  }
   260         -  db eval "DROP INDEX schemalint_test"
   261         -}
   262         -
   263         -proc find_trial_indexes {ctxvar} {
   264         -  upvar $ctxvar G
   265         -  foreach t $G(trace) {
   266         -    set tname [lindex $t 0]
   267         -    catch { array unset mask }
   268         -
   269         -    # Invoke "PRAGMA table_info" on the table. Use the results to create
   270         -    # an array mapping from column name to collation sequence. Store the
   271         -    # array in local variable aColl.
   272         -    #
   273         -    sqlidx_get_coll_map $tname aColl
   274         -
   275         -    set orderby [list]
   276         -    if {[lindex $t end 0]=="orderby"} {
   277         -      set orderby [lrange [lindex $t end] 1 end]
   278         -    }
   279         -
   280         -    foreach lCons [expand_or_cons [lrange $t 2 end]] {
   281         -
   282         -      # Populate the array mask() so that it contains an entry for each
   283         -      # combination of prerequisite scans that may lead to distinct sets
   284         -      # of constraints being usable.
   285         -      #
   286         -      catch { array unset mask }
   287         -      set mask(0) 1
   288         -      foreach a $lCons {
   289         -        set type [lindex $a 0]
   290         -        if {$type=="eq" || $type=="range"} {
   291         -          set m [lindex $a 3]
   292         -          foreach k [array names mask] { set mask([expr ($k & $m)]) 1 }
   293         -          set mask($m) 1
   294         -        }
   295         -      }
   296         -
   297         -      # Loop once for each distinct prerequisite scan mask identified in
   298         -      # the previous block.
   299         -      #
   300         -      foreach k [array names mask] {
   301         -
   302         -        # Identify the constraints available for prerequisite mask $k. For
   303         -        # each == constraint, set an entry in the eq() array as follows:
   304         -        # 
   305         -        #   set eq(<col>) <collation>
   306         -        #
   307         -        # If there is more than one == constraint for a column, and they use
   308         -        # different collation sequences, <collation> is replaced with a list
   309         -        # of the possible collation sequences. For example, for:
   310         -        #
   311         -        #   SELECT * FROM t1 WHERE a=? COLLATE BINARY AND a=? COLLATE NOCASE
   312         -        #
   313         -        # Set the following entry in the eq() array:
   314         -        #
   315         -        #   set eq(a) {binary nocase}
   316         -        #
   317         -        # For each range constraint found an entry is appended to the $ranges
   318         -        # list. The entry is itself a list of the form {<col> <collation>}.
   319         -        #
   320         -        catch {array unset eq}
   321         -        set ranges [list]
   322         -        foreach a $lCons {
   323         -          set type [lindex $a 0]
   324         -          if {$type=="eq" || $type=="range"} {
   325         -            foreach {type col collate m} $a {
   326         -              if {($m & $k)==$m} {
   327         -                if {$type=="eq"} {
   328         -                  lappend eq($col) $collate
   329         -                } else {
   330         -                  lappend ranges [list $col $collate ASC]
   331         -                }
   332         -              }
   333         -            }
   334         -          }
   335         -        }
   336         -        set ranges [lsort -unique $ranges]
   337         -        if {$orderby != ""} {
   338         -          lappend ranges $orderby
   339         -        }
   340         -
   341         -        foreach eqset [expand_eq_list [array get eq]] {
   342         -          if {$eqset != ""} {
   343         -            eqset_to_index G aColl $tname $eqset
   344         -          }
   345         -
   346         -          foreach r $ranges {
   347         -            set tail [list]
   348         -            foreach {c collate dir} $r {
   349         -              set bSeen 0
   350         -              foreach e $eqset {
   351         -                if {[lindex $e 0] == $c} {
   352         -                  set bSeen 1
   353         -                  break
   354         -                }
   355         -              }
   356         -              if {$bSeen==0} { lappend tail {*}$r }
   357         -            }
   358         -            if {[llength $tail]} {
   359         -              eqset_to_index G aColl $tname $eqset $r
   360         -            }
   361         -          }
   362         -        }
   363         -      }
   364         -    }
   365         -  }
   366         -
   367         -  if {$::VERBOSE} {
   368         -    foreach k [array names G trial.*] { puts "index: $G($k)" }
   369         -  }
   370         -}
   371         -
   372         -proc run_trials {ctxvar} {
   373         -  upvar $ctxvar G
   374         -  set ret [list]
   375         -
   376         -  foreach k [array names G trial.*] {
   377         -    set idxname [lindex [split $k .] 1]
   378         -    db eval $G($k)
   379         -    set pgno [db one {SELECT rootpage FROM sqlite_master WHERE name = $idxname}]
   380         -    set IDX($pgno) $idxname
   381         -  }
   382         -  db eval ANALYZE
   383         -
   384         -  catch { array unset used }
   385         -  foreach s $G(lSelect) {
   386         -    db eval "EXPLAIN $s" x {
   387         -      if {($x(opcode)=="OpenRead" || $x(opcode)=="ReopenIdx")} {
   388         -        if {[info exists IDX($x(p2))]} { set used($IDX($x(p2))) 1 }
   389         -      }
   390         -    }
   391         -    foreach idx [array names used] {
   392         -      lappend ret $G(trial.$idx)
   393         -    }
   394         -  }
   395         -
   396         -  set ret
   397         -}
   398         -
   399         -proc sqlidx_init_context {varname} {
   400         -  upvar $varname G
   401         -  set G(lSelect)  [list]           ;# List of SELECT statements to analyze
   402         -  set G(database) ""               ;# Name of database or SQL schema file
   403         -  set G(trace)    [list]           ;# List of data from xTrace()
   404         -}
   405         -
   406         -#-------------------------------------------------------------------------
   407         -# The following is test code only.
   408         -#
   409         -proc sqlidx_one_test {tn schema select expected} {
   410         -#  if {$tn!=2} return
   411         -  sqlidx_init_context C
   412         -
   413         -  sqlite3 db ""
   414         -  db collate "a b c" [list string compare]
   415         -  db eval $schema
   416         -  lappend C(lSelect) $select
   417         -  analyze_selects C
   418         -  find_trial_indexes C
   419         -
   420         -  set idxlist [run_trials C]
   421         -  if {$idxlist != [list {*}$expected]} {
   422         -    puts stderr "Test $tn failed"
   423         -    puts stderr "Expected: $expected"
   424         -    puts stderr "Got: $idxlist"
   425         -    exit -1
   426         -  }
   427         -
   428         -  db close
   429         -
   430         -  upvar nTest nTest
   431         -  incr nTest
   432         -}
   433         -
   434         -proc sqlidx_internal_tests {} {
   435         -  set nTest 0
   436         -
   437         -
   438         -  # No indexes for a query with no constraints.
   439         -  sqlidx_one_test 0 {
   440         -    CREATE TABLE t1(a, b, c);
   441         -  } {
   442         -    SELECT * FROM t1;
   443         -  } {
   444         -  }
   445         -
   446         -  sqlidx_one_test 1 {
   447         -    CREATE TABLE t1(a, b, c);
   448         -    CREATE TABLE t2(x, y, z);
   449         -  } {
   450         -    SELECT a FROM t1, t2 WHERE a=? AND x=c
   451         -  } {
   452         -    {CREATE INDEX t2_x ON t2(x);}
   453         -    {CREATE INDEX t1_a_c ON t1(a, c);}
   454         -  }
   455         -
   456         -  sqlidx_one_test 2 {
   457         -    CREATE TABLE t1(a, b, c);
   458         -  } {
   459         -    SELECT * FROM t1 WHERE b>?;
   460         -  } {
   461         -    {CREATE INDEX t1_b ON t1(b);}
   462         -  }
   463         -
   464         -  sqlidx_one_test 3 {
   465         -    CREATE TABLE t1(a, b, c);
   466         -  } {
   467         -    SELECT * FROM t1 WHERE b COLLATE nocase BETWEEN ? AND ?
   468         -  } {
   469         -    {CREATE INDEX t1_bnocase ON t1(b COLLATE NOCASE);}
   470         -  }
   471         -
   472         -  sqlidx_one_test 4 {
   473         -    CREATE TABLE t1(a, b, c);
   474         -  } {
   475         -    SELECT a FROM t1 ORDER BY b;
   476         -  } {
   477         -    {CREATE INDEX t1_b ON t1(b);}
   478         -  }
   479         -
   480         -  sqlidx_one_test 5 {
   481         -    CREATE TABLE t1(a, b, c);
   482         -  } {
   483         -    SELECT a FROM t1 WHERE a=? ORDER BY b;
   484         -  } {
   485         -    {CREATE INDEX t1_a_b ON t1(a, b);}
   486         -  }
   487         -
   488         -  sqlidx_one_test 5 {
   489         -    CREATE TABLE t1(a, b, c);
   490         -  } {
   491         -    SELECT min(a) FROM t1
   492         -  } {
   493         -    {CREATE INDEX t1_a ON t1(a);}
   494         -  }
   495         -
   496         -  sqlidx_one_test 6 {
   497         -    CREATE TABLE t1(a, b, c);
   498         -  } {
   499         -    SELECT * FROM t1 ORDER BY a ASC, b COLLATE nocase DESC, c ASC;
   500         -  } {
   501         -    {CREATE INDEX t1_a_bnocasedesc_c ON t1(a, b COLLATE NOCASE DESC, c);}
   502         -  }
   503         -
   504         -  sqlidx_one_test 7 {
   505         -    CREATE TABLE t1(a COLLATE NOCase, b, c);
   506         -  } {
   507         -    SELECT * FROM t1 WHERE a=?
   508         -  } {
   509         -    {CREATE INDEX t1_a ON t1(a);}
   510         -  }
   511         -
   512         -  # Tables with names that require quotes.
   513         -  #
   514         -  sqlidx_one_test 8.1 {
   515         -    CREATE TABLE "t t"(a, b, c);
   516         -  } {
   517         -    SELECT * FROM "t t" WHERE a=?
   518         -  } {
   519         -    {CREATE INDEX "t t_a" ON "t t"(a);}
   520         -  }
   521         -  sqlidx_one_test 8.2 {
   522         -    CREATE TABLE "t t"(a, b, c);
   523         -  } {
   524         -    SELECT * FROM "t t" WHERE b BETWEEN ? AND ?
   525         -  } {
   526         -    {CREATE INDEX "t t_b" ON "t t"(b);}
   527         -  }
   528         -  
   529         -  # Columns with names that require quotes.
   530         -  #
   531         -  sqlidx_one_test 9.1 {
   532         -    CREATE TABLE t3(a, "b b", c);
   533         -  } {
   534         -    SELECT * FROM t3 WHERE "b b" = ?
   535         -  } {
   536         -    {CREATE INDEX "t3_b b" ON t3("b b");}
   537         -  }
   538         -  sqlidx_one_test 9.2 {
   539         -    CREATE TABLE t3(a, "b b", c);
   540         -  } {
   541         -    SELECT * FROM t3 ORDER BY "b b"
   542         -  } {
   543         -    {CREATE INDEX "t3_b b" ON t3("b b");}
   544         -  }
   545         -
   546         -  # Collations with names that require quotes.
   547         -  #
   548         -  sqlidx_one_test 10.1 {
   549         -    CREATE TABLE t4(a, b, c);
   550         -  } {
   551         -    SELECT * FROM t4 ORDER BY c COLLATE "a b c"
   552         -  } {
   553         -    {CREATE INDEX "t4_ca b c" ON t4(c COLLATE "a b c");}
   554         -  }
   555         -  sqlidx_one_test 10.2 {
   556         -    CREATE TABLE t4(a, b, c);
   557         -  } {
   558         -    SELECT * FROM t4 WHERE c = ? COLLATE "a b c"
   559         -  } {
   560         -    {CREATE INDEX "t4_ca b c" ON t4(c COLLATE "a b c");}
   561         -  }
   562         -
   563         -  # Transitive constraints
   564         -  #
   565         -  sqlidx_one_test 11.1 {
   566         -    CREATE TABLE t5(a, b);
   567         -    CREATE TABLE t6(c, d);
   568         -  } {
   569         -    SELECT * FROM t5, t6 WHERE a=? AND b=c AND c=?
   570         -  } {
   571         -    {CREATE INDEX t6_c ON t6(c);} 
   572         -    {CREATE INDEX t5_a_b ON t5(a, b);}
   573         -  }
   574         -
   575         -  puts "All $nTest tests passed"
   576         -  exit
   577         -}
   578         -# End of internal test code.
   579         -#-------------------------------------------------------------------------
   580         -
   581         -if {[info exists ::argv0]==0} { set ::argv0 [info nameofexec] }
   582         -if {[info exists ::argv]==0} usage
   583         -sqlidx_init_context D
   584         -process_cmdline_args D $::argv
   585         -open_database D
   586         -analyze_selects D
   587         -find_trial_indexes D
   588         -foreach idx [run_trials D] { puts $idx }
   589         -
   590         -} err]} {
   591         -  puts "ERROR: $err"
   592         -  puts $errorInfo
   593         -  exit 1
   594         -}