/ Check-in [451e0faf]
Login

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

Overview
Comment:Fix the schemalint.tcl script to handle identifiers that require quoting.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | schemalint
Files: files | file ages | folders
SHA1: 451e0fafbe5b7e9c67d9b584d5e16796c3196881
User & Date: dan 2015-11-30 18:17:55
Context
2015-11-30
19:16
Add a rule to main.mk to build the schemalint.tcl script into an executable. Similar to the way the sqlite3_analyzer executable is built. check-in: b8251065 user: dan tags: schemalint
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
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

  3938   3938       zOp = "eq";
  3939   3939     }else if( pTerm->eOperator & (WO_LT|WO_LE|WO_GE|WO_GT) ){
  3940   3940       zOp = "range";
  3941   3941     }
  3942   3942     pColl = sqlite3BinaryCompareCollSeq(pParse, pX->pLeft, pX->pRight);
  3943   3943   
  3944   3944     if( zOp ){
  3945         -    const char *zFmt = bOr ? "%z{{%s %s %s %lld}}" : "%z{%s %s %s %lld}";
         3945  +    const char *zFmt = bOr ? "%z{{%s \"%w\" \"%w\" %lld}}" :
         3946  +                             "%z{%s \"%w\" \"%w\" %lld}";
  3946   3947       zBuf = whereAppendPrintf(db, zFmt, zIn, 
  3947   3948           zOp, pTab->aCol[pTerm->u.leftColumn].zName, 
  3948   3949           (pColl ? pColl->zName : "BINARY"),
  3949   3950           pTerm->prereqRight
  3950   3951       );
  3951   3952     }else{
  3952   3953       zBuf = zIn;
................................................................................
  4021   4022   
  4022   4023         struct SrcList_item *pItem = &pWInfo->pTabList->a[ii];
  4023   4024         if( pItem->pSelect ) continue;
  4024   4025         pTab = pItem->pTab;
  4025   4026         nCol = pTab->nCol;
  4026   4027   
  4027   4028         /* Append the table name to the buffer. */
  4028         -      zBuf = whereAppendPrintf(db, "%s", pTab->zName);
         4029  +      zBuf = whereAppendPrintf(db, "\"%w\"", pTab->zName);
  4029   4030   
  4030   4031         /* Append the list of columns required to create a covering index */
  4031   4032         zBuf = whereAppendPrintf(db, "%z {cols", zBuf);
  4032   4033         if( 0==(pItem->colUsed & ((u64)1 << (sizeof(Bitmask)*8-1))) ){
  4033   4034           for(iCol=0; iCol<nCol; iCol++){
  4034   4035             if( iCol==(sizeof(Bitmask)*8-1) ) break;
  4035   4036             if( pItem->colUsed & ((u64)1 << iCol) ){
  4036         -            zBuf = whereAppendPrintf(db, "%z %s", zBuf, pTab->aCol[iCol].zName);
         4037  +            const char *zName = pTab->aCol[iCol].zName;
         4038  +            zBuf = whereAppendPrintf(db, "%z \"%w\"", zBuf, zName);
  4037   4039             }
  4038   4040           }
  4039   4041         }
  4040   4042         zBuf = whereAppendPrintf(db, "%z}",zBuf);
  4041   4043   
  4042   4044         /* Append the contents of WHERE clause */
  4043   4045         zBuf = whereTraceWC(pParse, 1, pItem, zBuf, p->pWC);
................................................................................
  4050   4052             Expr *pExpr = pOrderBy->a[i].pExpr; 
  4051   4053             CollSeq *pColl = sqlite3ExprCollSeq(pParse, pExpr);
  4052   4054   
  4053   4055             pExpr = sqlite3ExprSkipCollate(pExpr);
  4054   4056             if( pExpr->op==TK_COLUMN && pExpr->iTable==pItem->iCursor ){
  4055   4057               if( pExpr->iColumn>=0 ){
  4056   4058                 const char *zName = pTab->aCol[pExpr->iColumn].zName;
  4057         -              zBuf = whereAppendPrintf(db, "%z%s%s %s %s", zBuf,
         4059  +              zBuf = whereAppendPrintf(db, "%z%s\"%w\" \"%w\" %s", zBuf,
  4058   4060                     bFirst ? " {orderby " : " ", zName, pColl->zName,
  4059   4061                     (pOrderBy->a[i].sortOrder ? "DESC" : "ASC")
  4060   4062                 );
  4061   4063                 bFirst = 0;
  4062   4064               }
  4063   4065             }
  4064   4066           }

Changes to test/schemalint.test.

    23     23     CREATE TABLE t1(a, b, c);
    24     24     CREATE TABLE t2(x, y, z);
    25     25   }
    26     26   
    27     27   do_trace_test 1.1 {
    28     28     SELECT b, c, y, z FROM t1, t2 WHERE c=? AND z=?
    29     29   } {
    30         -  {t1 {cols b c} {eq c BINARY 0}}
    31         -  {t2 {cols y z} {eq z BINARY 0}}
           30  +  {"t1" {cols "b" "c"} {eq "c" "BINARY" 0}}
           31  +  {"t2" {cols "y" "z"} {eq "z" "BINARY" 0}}
    32     32   }
    33     33   
    34     34   do_trace_test 1.2 {
    35     35     SELECT a FROM t1 WHERE b>10
    36     36   } {
    37         -  {t1 {cols a b} {range b BINARY 0}}
           37  +  {"t1" {cols "a" "b"} {range "b" "BINARY" 0}}
    38     38   }
    39     39   
    40     40   do_trace_test 1.3 {
    41     41     SELECT b FROM t1 WHERE b IN (10, 20, 30)
    42     42   } {
    43         -  {t1 {cols b} {eq b BINARY 0}}
           43  +  {"t1" {cols "b"} {eq "b" "BINARY" 0}}
    44     44   }
    45     45   
    46     46   do_trace_test 1.4 {
    47     47     SELECT * FROM t1, t2 WHERE x=a
    48     48   } {
    49         -  {t1 {cols a b c} {eq a BINARY 2}} 
    50         -  {t2 {cols x y z} {eq x BINARY 1}}
           49  +  {"t1" {cols "a" "b" "c"} {eq "a" "BINARY" 2}} 
           50  +  {"t2" {cols "x" "y" "z"} {eq "x" "BINARY" 1}}
    51     51   }
    52     52   
    53     53   do_trace_test 1.5 {
    54     54     SELECT * FROM t1 WHERE a IN (1, 2, 3)
    55     55   } {
    56         -  {t1 {cols a b c} {eq a BINARY 0}}
           56  +  {"t1" {cols "a" "b" "c"} {eq "a" "BINARY" 0}}
    57     57   }
    58     58   
    59     59   #-----------------------------------------------------------------------
    60     60   # Cases involving OR clauses in the WHERE clause.
    61     61   #
    62     62   do_trace_test 2.1 {
    63     63     SELECT * FROM t1 WHERE a=? OR b=?
    64     64   } {
    65         -  {t1 {cols a b c} {or {{eq a BINARY 0}} {{eq b BINARY 0}}}}
           65  +  {"t1" {cols "a" "b" "c"} {or {{eq "a" "BINARY" 0}} {{eq "b" "BINARY" 0}}}}
    66     66   }
    67     67   
    68     68   do_trace_test 2.2 {
    69     69     SELECT * FROM t1 WHERE a=? OR (b=? AND c=?)
    70     70   } {
    71         -  {t1 {cols a b c} {or {{eq a BINARY 0}} {{eq b BINARY 0} {eq c BINARY 0}}}}
           71  +  {"t1" {cols "a" "b" "c"} {or {{eq "a" "BINARY" 0}} {{eq "b" "BINARY" 0} {eq "c" "BINARY" 0}}}}
    72     72   }
    73     73   
    74     74   do_trace_test 2.3 {
    75     75     SELECT * FROM t1 WHERE (a=? AND b=?) OR c=?
    76     76   } {
    77         -  {t1 {cols a b c} {or {{eq c BINARY 0}} {{eq a BINARY 0} {eq b BINARY 0}}}}
           77  +  {"t1" {cols "a" "b" "c"} {or {{eq "c" "BINARY" 0}} {{eq "a" "BINARY" 0} {eq "b" "BINARY" 0}}}}
    78     78   }
    79     79   
    80     80   #-----------------------------------------------------------------------
    81     81   # Cases involving ORDER BY.
    82     82   #
    83     83   do_trace_test 3.1 {
    84     84     SELECT * FROM t1 ORDER BY a;
    85         -} {{t1 {cols a b c} {orderby a BINARY ASC}}}
           85  +} {{"t1" {cols "a" "b" "c"} {orderby "a" "BINARY" ASC}}}
    86     86   
    87     87   do_trace_test 3.2 {
    88     88     SELECT * FROM t1 WHERE a=? ORDER BY b;
    89         -} {{t1 {cols a b c} {eq a BINARY 0} {orderby b BINARY ASC}}}
           89  +} {{"t1" {cols "a" "b" "c"} {eq "a" "BINARY" 0} {orderby "b" "BINARY" ASC}}}
    90     90   
    91     91   do_trace_test 3.3 {
    92     92     SELECT min(a) FROM t1;
    93         -} {{t1 {cols a} {orderby a BINARY ASC}}}
           93  +} {{"t1" {cols "a"} {orderby "a" "BINARY" ASC}}}
    94     94   
    95     95   do_trace_test 3.4 {
    96     96     SELECT max(a) FROM t1;
    97         -} {{t1 {cols a} {orderby a BINARY DESC}}}
           97  +} {{"t1" {cols "a"} {orderby "a" "BINARY" DESC}}}
    98     98   
    99     99   finish_test
   100    100   

Changes to tool/schemalint.tcl.

     6      6     puts stderr "  Switches are:"
     7      7     puts stderr "  -select SQL     (recommend indexes for SQL statement)"
     8      8     puts stderr "  -verbose        (increase verbosity of output)"
     9      9     puts stderr "  -test           (run internal tests and then exit)"
    10     10     puts stderr ""
    11     11     exit
    12     12   }
           13  +
           14  +# Return the quoted version of identfier $id. Quotes are only added if 
           15  +# they are required by SQLite.
           16  +#
           17  +# This command currently assumes that quotes are required if the 
           18  +# identifier contains any ASCII-range characters that are not 
           19  +# alpha-numeric or underscores.
           20  +#
           21  +proc quote {id} {
           22  +  if {[requires_quote $id]} {
           23  +    set x [string map {\" \"\"} $id]
           24  +    return "\"$x\""
           25  +  }
           26  +  return $id
           27  +}
           28  +proc requires_quote {id} {
           29  +  foreach c [split $id {}] {
           30  +    if {[string is alnum $c]==0 && $c!="_"} {
           31  +      return 1
           32  +    }
           33  +  }
           34  +  return 0
           35  +}
           36  +
           37  +# The argument passed to this command is a Tcl list of identifiers. The
           38  +# value returned is the same list, except with each item quoted and the
           39  +# elements comma-separated.
           40  +#
           41  +proc list_to_sql {L} {
           42  +  set ret [list]
           43  +  foreach l $L {
           44  +    lappend ret [quote $l]
           45  +  }
           46  +  join $ret ", "
           47  +}
    13     48   
    14     49   proc process_cmdline_args {ctxvar argv} {
    15     50     upvar $ctxvar G
    16     51     set nArg [llength $argv]
    17     52     set G(database) [lindex $argv end]
    18     53   
    19     54     for {set i 0} {$i < [llength $argv]-1} {incr i} {
................................................................................
   135    170     set rangeset [concat $rangeset $range]
   136    171   
   137    172     set lCols [list]
   138    173     set idxname $tname
   139    174   
   140    175     foreach {c collate dir} $rangeset {
   141    176       append idxname "_$c"
   142         -    set coldef $c
          177  +    set coldef [quote $c]
   143    178   
   144    179       if {[string compare -nocase $collate $aColl($c)]!=0} {
   145    180         append idxname [string tolower $collate]
   146         -      append coldef " COLLATE $collate"
          181  +      append coldef " COLLATE [quote $collate]"
   147    182       }
   148    183   
   149    184       if {$dir=="DESC"} {
   150    185         append coldef " DESC"
   151    186         append idxname "desc"
   152    187       }
   153    188       lappend lCols $coldef
   154    189     }
   155    190   
   156         -  set create_index "CREATE INDEX $idxname ON ${tname}("
          191  +  set create_index "CREATE INDEX [quote $idxname] ON [quote $tname]("
   157    192     append create_index [join $lCols ", "]
   158    193     append create_index ");"
   159    194   
   160    195     set G(trial.$idxname) $create_index
   161    196   }
   162    197   
   163    198   proc expand_or_cons {L} {
................................................................................
   181    216         }
   182    217         set lRet $lNew
   183    218       } 
   184    219     }
   185    220     return $lRet
   186    221   }
   187    222   
          223  +#--------------------------------------------------------------------------
          224  +# Argument $tname is the name of a table in the main database opened by
          225  +# database handle [db]. $arrayvar is the name of an array variable in the
          226  +# caller's context. This command populates the array with an entry mapping 
          227  +# from column name to default collation sequence for each column of table
          228  +# $tname. For example, if a table is declared:
          229  +#
          230  +#   CREATE TABLE t1(a COLLATE nocase, b, c COLLATE binary)
          231  +#
          232  +# the mapping is populated with:
          233  +#
          234  +#   map(a) -> "nocase"
          235  +#   map(b) -> "binary"
          236  +#   map(c) -> "binary"
          237  +#
   188    238   proc sqlidx_get_coll_map {tname arrayvar} {
   189    239     upvar $arrayvar aColl
   190    240     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         -
          241  +  set qname [quote $tname]
          242  +  db eval "PRAGMA table_info = $qname" x { lappend colnames $x(name) }
          243  +  db eval "CREATE INDEX schemalint_test ON ${qname}([list_to_sql $colnames])"
   194    244     db eval "PRAGMA index_xinfo = schemalint_test" x { 
   195    245       set aColl($x(name)) $x(coll)
   196    246     }
   197    247     db eval "DROP INDEX schemalint_test"
   198    248   }
   199    249   
   200    250   proc find_trial_indexes {ctxvar} {
................................................................................
   344    394   # The following is test code only.
   345    395   #
   346    396   proc sqlidx_one_test {tn schema select expected} {
   347    397   #  if {$tn!=2} return
   348    398     sqlidx_init_context C
   349    399   
   350    400     sqlite3 db ""
          401  +  db collate "a b c" [list string compare]
   351    402     db eval $schema
   352    403     lappend C(lSelect) $select
   353    404     analyze_selects C
   354    405     find_trial_indexes C
   355    406   
   356    407     set idxlist [run_trials C]
   357    408     if {$idxlist != [list {*}$expected]} {
................................................................................
   358    409       puts stderr "Test $tn failed"
   359    410       puts stderr "Expected: $expected"
   360    411       puts stderr "Got: $idxlist"
   361    412       exit -1
   362    413     }
   363    414   
   364    415     db close
          416  +
          417  +  upvar nTest nTest
          418  +  incr nTest
   365    419   }
   366    420   
   367    421   proc sqlidx_internal_tests {} {
          422  +  set nTest 0
          423  +
   368    424   
   369    425     # No indexes for a query with no constraints.
   370    426     sqlidx_one_test 0 {
   371    427       CREATE TABLE t1(a, b, c);
   372    428     } {
   373    429       SELECT * FROM t1;
   374    430     } {
................................................................................
   436    492       CREATE TABLE t1(a COLLATE NOCase, b, c);
   437    493     } {
   438    494       SELECT * FROM t1 WHERE a=?
   439    495     } {
   440    496       {CREATE INDEX t1_a ON t1(a);}
   441    497     }
   442    498   
          499  +  # Tables with names that require quotes.
          500  +  #
          501  +  sqlidx_one_test 8.1 {
          502  +    CREATE TABLE "t t"(a, b, c);
          503  +  } {
          504  +    SELECT * FROM "t t" WHERE a=?
          505  +  } {
          506  +    {CREATE INDEX "t t_a" ON "t t"(a);}
          507  +  }
          508  +  sqlidx_one_test 8.2 {
          509  +    CREATE TABLE "t t"(a, b, c);
          510  +  } {
          511  +    SELECT * FROM "t t" WHERE b BETWEEN ? AND ?
          512  +  } {
          513  +    {CREATE INDEX "t t_b" ON "t t"(b);}
          514  +  }
          515  +  
          516  +  # Columns with names that require quotes.
          517  +  #
          518  +  sqlidx_one_test 9.1 {
          519  +    CREATE TABLE t3(a, "b b", c);
          520  +  } {
          521  +    SELECT * FROM t3 WHERE "b b" = ?
          522  +  } {
          523  +    {CREATE INDEX "t3_b b" ON t3("b b");}
          524  +  }
          525  +  sqlidx_one_test 9.2 {
          526  +    CREATE TABLE t3(a, "b b", c);
          527  +  } {
          528  +    SELECT * FROM t3 ORDER BY "b b"
          529  +  } {
          530  +    {CREATE INDEX "t3_b b" ON t3("b b");}
          531  +  }
          532  +
          533  +  # Collations with names that require quotes.
          534  +  #
          535  +  sqlidx_one_test 10.1 {
          536  +    CREATE TABLE t4(a, b, c);
          537  +  } {
          538  +    SELECT * FROM t4 ORDER BY c COLLATE "a b c"
          539  +  } {
          540  +    {CREATE INDEX "t4_ca b c" ON t4(c COLLATE "a b c");}
          541  +  }
          542  +  sqlidx_one_test 10.2 {
          543  +    CREATE TABLE t4(a, b, c);
          544  +  } {
          545  +    SELECT * FROM t4 WHERE c = ? COLLATE "a b c"
          546  +  } {
          547  +    {CREATE INDEX "t4_ca b c" ON t4(c COLLATE "a b c");}
          548  +  }
          549  +
          550  +  puts "All $nTest tests passed"
   443    551     exit
   444    552   }
   445    553   # End of internal test code.
   446    554   #-------------------------------------------------------------------------
   447    555   
   448    556   sqlidx_init_context D
   449    557   process_cmdline_args D $argv
   450    558   open_database D
   451    559   analyze_selects D
   452    560   find_trial_indexes D
   453    561   foreach idx [run_trials D] { puts $idx }
   454    562