/ Check-in [d3aa067c]
Login

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: d3aa067c830e98f2074630c4613c557b0ce90a57
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
Hide Diffs Unified Diffs Show Whitespace Changes Patch

Changes to tool/schemalint.tcl.

120
121
122
123
124
125
126
127
128







129
130
131
132
133
134
135
136
137
138
139
140
141
142
143


144
145

146

147
148

149
150
151
152

153
154
155
156
157
158
159
...
179
180
181
182
183
184
185












186
187
188
189
190
191






192
193
194
195
196
197
198
...
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
...
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
...
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413








414
415
416
417
418
419
420

  return $ll
}

#--------------------------------------------------------------------------
# Formulate a CREATE INDEX statement that creates an index on table $tname.
#
proc eqset_to_index {ctxvar tname eqset {range {}}} {
  upvar $ctxvar G








  set lCols [list]
  set idxname $tname
  foreach e [lsort $eqset] { 
    if {[llength $e]==0} continue
    foreach {c collate} $e {}
    lappend lCols "$c collate $collate"
    append idxname "_$c"
    if {[string compare -nocase binary $collate]!=0} {
      append idxname [string tolower $collate]
    }
  }

  foreach {c collate dir} $range {
    append idxname "_$c"


    if {[string compare -nocase binary $collate]!=0} {
      append idxname [string tolower $collate]

    }

    if {$dir=="DESC"} {
      lappend lCols "$c collate $collate DESC"

      append idxname "desc"
    } else {
      lappend lCols "$c collate $collate"
    }

  }

  set create_index "CREATE INDEX $idxname ON ${tname}("
  append create_index [join $lCols ", "]
  append create_index ");"

  set G(trial.$idxname) $create_index
................................................................................
        }
      }
      set lRet $lNew
    } 
  }
  return $lRet
}













proc find_trial_indexes {ctxvar} {
  upvar $ctxvar G
  foreach t $G(trace) {
    set tname [lindex $t 0]
    catch { array unset mask }







    set orderby [list]
    if {[lindex $t end 0]=="orderby"} {
      set orderby [lrange [lindex $t end] 1 end]
    }

    foreach lCons [expand_or_cons [lrange $t 2 end]] {
................................................................................
        set ranges [lsort -unique $ranges]
        if {$orderby != ""} {
          lappend ranges $orderby
        }

        foreach eqset [expand_eq_list [array get eq]] {
          if {$eqset != ""} {
            eqset_to_index G $tname $eqset
          }

          foreach r $ranges {
            set tail [list]
            foreach {c collate dir} $r {
              set bSeen 0
              foreach e $eqset {
................................................................................
                  set bSeen 1
                  break
                }
              }
              if {$bSeen==0} { lappend tail {*}$r }
            }
            if {[llength $tail]} {
              eqset_to_index G $tname $eqset $r
            }
          }
        }
      }
    }
  }

................................................................................

  sqlidx_one_test 1 {
    CREATE TABLE t1(a, b, c);
    CREATE TABLE t2(x, y, z);
  } {
    SELECT a FROM t1, t2 WHERE a=? AND x=c
  } {
    {CREATE INDEX t2_x ON t2(x collate BINARY);}
    {CREATE INDEX t1_a_c ON t1(a collate BINARY, c collate BINARY);}
  }

  sqlidx_one_test 2 {
    CREATE TABLE t1(a, b, c);
  } {
    SELECT * FROM t1 WHERE b>?;
  } {
    {CREATE INDEX t1_b ON t1(b collate BINARY);}
  }

  sqlidx_one_test 3 {
    CREATE TABLE t1(a, b, c);
  } {
    SELECT * FROM t1 WHERE b COLLATE nocase BETWEEN ? AND ?
  } {
    {CREATE INDEX t1_bnocase ON t1(b collate NOCASE);}
  }

  sqlidx_one_test 4 {
    CREATE TABLE t1(a, b, c);
  } {
    SELECT a FROM t1 ORDER BY b;
  } {
    {CREATE INDEX t1_b ON t1(b collate BINARY);}
  }

  sqlidx_one_test 5 {
    CREATE TABLE t1(a, b, c);
  } {
    SELECT a FROM t1 WHERE a=? ORDER BY b;
  } {
    {CREATE INDEX t1_a_b ON t1(a collate BINARY, b collate BINARY);}
  }

  sqlidx_one_test 5 {
    CREATE TABLE t1(a, b, c);
  } {
    SELECT min(a) FROM t1
  } {
    {CREATE INDEX t1_a ON t1(a collate BINARY);}
  }

  sqlidx_one_test 6 {
    CREATE TABLE t1(a, b, c);
  } {
    SELECT * FROM t1 ORDER BY a ASC, b COLLATE nocase DESC, c ASC;
  } {
    {CREATE INDEX t1_a_bnocasedesc_c ON t1(a collate BINARY, b collate NOCASE DESC, c collate BINARY);}








  }

  exit
}
# End of internal test code.
#-------------------------------------------------------------------------








|

>
>
>
>
>
>
>



<
<
<
<
<
<
<
|
<
<
|

>
>
|

>

>

<
>

<
<

>







 







>
>
>
>
>
>
>
>
>
>
>
>






>
>
>
>
>
>







 







|







 







|







 







|
|







|







|







|







|







|







|
>
>
>
>
>
>
>
>







120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138







139


140
141
142
143
144
145
146
147
148
149

150
151


152
153
154
155
156
157
158
159
160
...
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
...
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
...
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
...
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447

  return $ll
}

#--------------------------------------------------------------------------
# Formulate a CREATE INDEX statement that creates an index on table $tname.
#
proc eqset_to_index {ctxvar aCollVar tname eqset {range {}}} {
  upvar $ctxvar G
  upvar $aCollVar aColl

  set rangeset [list]
  foreach e [lsort $eqset] {
    lappend rangeset [lindex $e 0] [lindex $e 1] ASC
  }
  set rangeset [concat $rangeset $range]

  set lCols [list]
  set idxname $tname










  foreach {c collate dir} $rangeset {
    append idxname "_$c"
    set coldef $c

    if {[string compare -nocase $collate $aColl($c)]!=0} {
      append idxname [string tolower $collate]
      append coldef " COLLATE $collate"
    }

    if {$dir=="DESC"} {

      append coldef " DESC"
      append idxname "desc"


    }
    lappend lCols $coldef
  }

  set create_index "CREATE INDEX $idxname ON ${tname}("
  append create_index [join $lCols ", "]
  append create_index ");"

  set G(trial.$idxname) $create_index
................................................................................
        }
      }
      set lRet $lNew
    } 
  }
  return $lRet
}

proc sqlidx_get_coll_map {tname arrayvar} {
  upvar $arrayvar aColl
  set colnames [list]
  db eval "PRAGMA table_info = $tname" x { lappend colnames $x(name) }
  db eval "CREATE INDEX schemalint_test ON ${tname}([join $colnames ,])"

  db eval "PRAGMA index_xinfo = schemalint_test" x { 
    set aColl($x(name)) $x(coll)
  }
  db eval "DROP INDEX schemalint_test"
}

proc find_trial_indexes {ctxvar} {
  upvar $ctxvar G
  foreach t $G(trace) {
    set tname [lindex $t 0]
    catch { array unset mask }

    # Invoke "PRAGMA table_info" on the table. Use the results to create
    # an array mapping from column name to collation sequence. Store the
    # array in local variable aColl.
    #
    sqlidx_get_coll_map $tname aColl

    set orderby [list]
    if {[lindex $t end 0]=="orderby"} {
      set orderby [lrange [lindex $t end] 1 end]
    }

    foreach lCons [expand_or_cons [lrange $t 2 end]] {
................................................................................
        set ranges [lsort -unique $ranges]
        if {$orderby != ""} {
          lappend ranges $orderby
        }

        foreach eqset [expand_eq_list [array get eq]] {
          if {$eqset != ""} {
            eqset_to_index G aColl $tname $eqset
          }

          foreach r $ranges {
            set tail [list]
            foreach {c collate dir} $r {
              set bSeen 0
              foreach e $eqset {
................................................................................
                  set bSeen 1
                  break
                }
              }
              if {$bSeen==0} { lappend tail {*}$r }
            }
            if {[llength $tail]} {
              eqset_to_index G aColl $tname $eqset $r
            }
          }
        }
      }
    }
  }

................................................................................

  sqlidx_one_test 1 {
    CREATE TABLE t1(a, b, c);
    CREATE TABLE t2(x, y, z);
  } {
    SELECT a FROM t1, t2 WHERE a=? AND x=c
  } {
    {CREATE INDEX t2_x ON t2(x);}
    {CREATE INDEX t1_a_c ON t1(a, c);}
  }

  sqlidx_one_test 2 {
    CREATE TABLE t1(a, b, c);
  } {
    SELECT * FROM t1 WHERE b>?;
  } {
    {CREATE INDEX t1_b ON t1(b);}
  }

  sqlidx_one_test 3 {
    CREATE TABLE t1(a, b, c);
  } {
    SELECT * FROM t1 WHERE b COLLATE nocase BETWEEN ? AND ?
  } {
    {CREATE INDEX t1_bnocase ON t1(b COLLATE NOCASE);}
  }

  sqlidx_one_test 4 {
    CREATE TABLE t1(a, b, c);
  } {
    SELECT a FROM t1 ORDER BY b;
  } {
    {CREATE INDEX t1_b ON t1(b);}
  }

  sqlidx_one_test 5 {
    CREATE TABLE t1(a, b, c);
  } {
    SELECT a FROM t1 WHERE a=? ORDER BY b;
  } {
    {CREATE INDEX t1_a_b ON t1(a, b);}
  }

  sqlidx_one_test 5 {
    CREATE TABLE t1(a, b, c);
  } {
    SELECT min(a) FROM t1
  } {
    {CREATE INDEX t1_a ON t1(a);}
  }

  sqlidx_one_test 6 {
    CREATE TABLE t1(a, b, c);
  } {
    SELECT * FROM t1 ORDER BY a ASC, b COLLATE nocase DESC, c ASC;
  } {
    {CREATE INDEX t1_a_bnocasedesc_c ON t1(a, b COLLATE NOCASE DESC, c);}
  }

  sqlidx_one_test 7 {
    CREATE TABLE t1(a COLLATE NOCase, b, c);
  } {
    SELECT * FROM t1 WHERE a=?
  } {
    {CREATE INDEX t1_a ON t1(a);}
  }

  exit
}
# End of internal test code.
#-------------------------------------------------------------------------