/ 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 Side-by-Side Diffs Ignore Whitespace Patch

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