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: |
269bf52e27611cd00fa7f73ee98b395a |
| 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
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 -}