Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
| 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: |
451e0fafbe5b7e9c67d9b584d5e16796 |
| User & Date: | dan 2015-11-30 18:17:55 |
|
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 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