Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | In the CREATE INDEX statements output by schemalint.tcl, avoid declaring an explicit collation sequence that is the same as the column's default. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | schemalint |
| Files: | files | file ages | folders |
| SHA1: |
d3aa067c830e98f2074630c4613c557b |
| User & Date: | dan 2015-11-23 18:28:07 |
Context
|
2015-11-30
| ||
| 18:17 | Fix the schemalint.tcl script to handle identifiers that require quoting. check-in: 451e0faf user: dan tags: schemalint | |
|
2015-11-23
| ||
| 18:28 | In the CREATE INDEX statements output by schemalint.tcl, avoid declaring an explicit collation sequence that is the same as the column's default. check-in: d3aa067c user: dan tags: schemalint | |
| 17:14 | Merge latest trunk changes with this branch. check-in: 8f1ef090 user: dan tags: schemalint | |
Changes
Changes to tool/schemalint.tcl.
120 120 121 121 return $ll 122 122 } 123 123 124 124 #-------------------------------------------------------------------------- 125 125 # Formulate a CREATE INDEX statement that creates an index on table $tname. 126 126 # 127 -proc eqset_to_index {ctxvar tname eqset {range {}}} { 127 +proc eqset_to_index {ctxvar aCollVar tname eqset {range {}}} { 128 128 upvar $ctxvar G 129 + upvar $aCollVar aColl 130 + 131 + set rangeset [list] 132 + foreach e [lsort $eqset] { 133 + lappend rangeset [lindex $e 0] [lindex $e 1] ASC 134 + } 135 + set rangeset [concat $rangeset $range] 129 136 130 137 set lCols [list] 131 138 set idxname $tname 132 - foreach e [lsort $eqset] { 133 - if {[llength $e]==0} continue 134 - foreach {c collate} $e {} 135 - lappend lCols "$c collate $collate" 139 + 140 + foreach {c collate dir} $rangeset { 136 141 append idxname "_$c" 137 - if {[string compare -nocase binary $collate]!=0} { 142 + set coldef $c 143 + 144 + if {[string compare -nocase $collate $aColl($c)]!=0} { 138 145 append idxname [string tolower $collate] 146 + append coldef " COLLATE $collate" 139 147 } 140 - } 141 148 142 - foreach {c collate dir} $range { 143 - append idxname "_$c" 144 - if {[string compare -nocase binary $collate]!=0} { 145 - append idxname [string tolower $collate] 149 + if {$dir=="DESC"} { 150 + append coldef " DESC" 151 + append idxname "desc" 146 152 } 147 - if {$dir=="DESC"} { 148 - lappend lCols "$c collate $collate DESC" 149 - append idxname "desc" 150 - } else { 151 - lappend lCols "$c collate $collate" 152 - } 153 + lappend lCols $coldef 153 154 } 154 155 155 156 set create_index "CREATE INDEX $idxname ON ${tname}(" 156 157 append create_index [join $lCols ", "] 157 158 append create_index ");" 158 159 159 160 set G(trial.$idxname) $create_index ................................................................................ 179 180 } 180 181 } 181 182 set lRet $lNew 182 183 } 183 184 } 184 185 return $lRet 185 186 } 187 + 188 +proc sqlidx_get_coll_map {tname arrayvar} { 189 + upvar $arrayvar aColl 190 + set colnames [list] 191 + db eval "PRAGMA table_info = $tname" x { lappend colnames $x(name) } 192 + db eval "CREATE INDEX schemalint_test ON ${tname}([join $colnames ,])" 193 + 194 + db eval "PRAGMA index_xinfo = schemalint_test" x { 195 + set aColl($x(name)) $x(coll) 196 + } 197 + db eval "DROP INDEX schemalint_test" 198 +} 186 199 187 200 proc find_trial_indexes {ctxvar} { 188 201 upvar $ctxvar G 189 202 foreach t $G(trace) { 190 203 set tname [lindex $t 0] 191 204 catch { array unset mask } 205 + 206 + # Invoke "PRAGMA table_info" on the table. Use the results to create 207 + # an array mapping from column name to collation sequence. Store the 208 + # array in local variable aColl. 209 + # 210 + sqlidx_get_coll_map $tname aColl 192 211 193 212 set orderby [list] 194 213 if {[lindex $t end 0]=="orderby"} { 195 214 set orderby [lrange [lindex $t end] 1 end] 196 215 } 197 216 198 217 foreach lCons [expand_or_cons [lrange $t 2 end]] { ................................................................................ 254 273 set ranges [lsort -unique $ranges] 255 274 if {$orderby != ""} { 256 275 lappend ranges $orderby 257 276 } 258 277 259 278 foreach eqset [expand_eq_list [array get eq]] { 260 279 if {$eqset != ""} { 261 - eqset_to_index G $tname $eqset 280 + eqset_to_index G aColl $tname $eqset 262 281 } 263 282 264 283 foreach r $ranges { 265 284 set tail [list] 266 285 foreach {c collate dir} $r { 267 286 set bSeen 0 268 287 foreach e $eqset { ................................................................................ 270 289 set bSeen 1 271 290 break 272 291 } 273 292 } 274 293 if {$bSeen==0} { lappend tail {*}$r } 275 294 } 276 295 if {[llength $tail]} { 277 - eqset_to_index G $tname $eqset $r 296 + eqset_to_index G aColl $tname $eqset $r 278 297 } 279 298 } 280 299 } 281 300 } 282 301 } 283 302 } 284 303 ................................................................................ 357 376 358 377 sqlidx_one_test 1 { 359 378 CREATE TABLE t1(a, b, c); 360 379 CREATE TABLE t2(x, y, z); 361 380 } { 362 381 SELECT a FROM t1, t2 WHERE a=? AND x=c 363 382 } { 364 - {CREATE INDEX t2_x ON t2(x collate BINARY);} 365 - {CREATE INDEX t1_a_c ON t1(a collate BINARY, c collate BINARY);} 383 + {CREATE INDEX t2_x ON t2(x);} 384 + {CREATE INDEX t1_a_c ON t1(a, c);} 366 385 } 367 386 368 387 sqlidx_one_test 2 { 369 388 CREATE TABLE t1(a, b, c); 370 389 } { 371 390 SELECT * FROM t1 WHERE b>?; 372 391 } { 373 - {CREATE INDEX t1_b ON t1(b collate BINARY);} 392 + {CREATE INDEX t1_b ON t1(b);} 374 393 } 375 394 376 395 sqlidx_one_test 3 { 377 396 CREATE TABLE t1(a, b, c); 378 397 } { 379 398 SELECT * FROM t1 WHERE b COLLATE nocase BETWEEN ? AND ? 380 399 } { 381 - {CREATE INDEX t1_bnocase ON t1(b collate NOCASE);} 400 + {CREATE INDEX t1_bnocase ON t1(b COLLATE NOCASE);} 382 401 } 383 402 384 403 sqlidx_one_test 4 { 385 404 CREATE TABLE t1(a, b, c); 386 405 } { 387 406 SELECT a FROM t1 ORDER BY b; 388 407 } { 389 - {CREATE INDEX t1_b ON t1(b collate BINARY);} 408 + {CREATE INDEX t1_b ON t1(b);} 390 409 } 391 410 392 411 sqlidx_one_test 5 { 393 412 CREATE TABLE t1(a, b, c); 394 413 } { 395 414 SELECT a FROM t1 WHERE a=? ORDER BY b; 396 415 } { 397 - {CREATE INDEX t1_a_b ON t1(a collate BINARY, b collate BINARY);} 416 + {CREATE INDEX t1_a_b ON t1(a, b);} 398 417 } 399 418 400 419 sqlidx_one_test 5 { 401 420 CREATE TABLE t1(a, b, c); 402 421 } { 403 422 SELECT min(a) FROM t1 404 423 } { 405 - {CREATE INDEX t1_a ON t1(a collate BINARY);} 424 + {CREATE INDEX t1_a ON t1(a);} 406 425 } 407 426 408 427 sqlidx_one_test 6 { 409 428 CREATE TABLE t1(a, b, c); 410 429 } { 411 430 SELECT * FROM t1 ORDER BY a ASC, b COLLATE nocase DESC, c ASC; 412 431 } { 413 - {CREATE INDEX t1_a_bnocasedesc_c ON t1(a collate BINARY, b collate NOCASE DESC, c collate BINARY);} 432 + {CREATE INDEX t1_a_bnocasedesc_c ON t1(a, b COLLATE NOCASE DESC, c);} 433 + } 434 + 435 + sqlidx_one_test 7 { 436 + CREATE TABLE t1(a COLLATE NOCase, b, c); 437 + } { 438 + SELECT * FROM t1 WHERE a=? 439 + } { 440 + {CREATE INDEX t1_a ON t1(a);} 414 441 } 415 442 416 443 exit 417 444 } 418 445 # End of internal test code. 419 446 #------------------------------------------------------------------------- 420 447