Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Add test script shell6.test, containing tests for schemalint. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | schemalint |
| Files: | files | file ages | folders |
| SHA1: |
0b73406595c9a077399b0f4c17af3a82 |
| User & Date: | dan 2016-02-22 19:51:08 |
Context
|
2016-03-09
| ||
| 08:07 | Fix a problem in the schemalint code that comes up when a sub-query uses one or more of the same tables as its parent. (check-in: fc18cc92 user: dan tags: schemalint) | |
|
2016-02-22
| ||
| 19:51 | Add test script shell6.test, containing tests for schemalint. (check-in: 0b734065 user: dan tags: schemalint) | |
|
2016-02-19
| ||
| 07:53 | Fix a couple of bugs in the schemalint code. (check-in: 02fbf699 user: dan tags: schemalint) | |
Changes
Changes to src/shell_indexes.c.
30 30 */ 31 31 struct IdxConstraint { 32 32 char *zColl; /* Collation sequence */ 33 33 int bRange; /* True for range, false for eq */ 34 34 int iCol; /* Constrained table column */ 35 35 i64 depmask; /* Dependency mask */ 36 36 int bFlag; /* Used by idxFindCompatible() */ 37 + int bDesc; /* True if ORDER BY <expr> DESC */ 37 38 IdxConstraint *pNext; /* Next constraint in pEq or pRange list */ 38 39 IdxConstraint *pLink; /* See above */ 39 40 }; 40 41 41 42 /* 42 43 ** A WHERE clause. Made up of IdxConstraint objects. Example WHERE clause: 43 44 ** ................................................................................ 194 195 p->pScan = pNew; 195 196 p->pCurrent = &pNew->where; 196 197 break; 197 198 } 198 199 199 200 case SQLITE_WHEREINFO_ORDERBY: { 200 201 IdxConstraint *pNew = idxNewConstraint(&p->rc, zVal); 201 - IdxConstraint **pp; 202 202 if( pNew==0 ) return; 203 203 pNew->iCol = iVal; 204 - for(pp=&p->pScan->pOrder; *pp; pp=&(*pp)->pNext); 205 - *pp = pNew; 204 + pNew->bDesc = (int)mask; 205 + if( p->pScan->pOrder==0 ){ 206 + p->pScan->pOrder = pNew; 207 + }else{ 208 + IdxConstraint *pIter; 209 + for(pIter=p->pScan->pOrder; pIter->pNext; pIter=pIter->pNext); 210 + pIter->pNext = pNew; 211 + pIter->pLink = pNew; 212 + } 206 213 break; 207 214 } 208 215 209 216 case SQLITE_WHEREINFO_EQUALS: 210 217 case SQLITE_WHEREINFO_RANGE: { 211 218 IdxConstraint *pNew = idxNewConstraint(&p->rc, zVal); 212 219 if( pNew==0 ) return; ................................................................................ 504 511 if( sqlite3_stricmp(p->zColl, pCons->zColl) ){ 505 512 if( idxIdentifierRequiresQuotes(pCons->zColl) ){ 506 513 zRet = idxAppendText(pRc, zRet, " COLLATE %Q", pCons->zColl); 507 514 }else{ 508 515 zRet = idxAppendText(pRc, zRet, " COLLATE %s", pCons->zColl); 509 516 } 510 517 } 518 + 519 + if( pCons->bDesc ){ 520 + zRet = idxAppendText(pRc, zRet, " DESC"); 521 + } 511 522 return zRet; 512 523 } 513 524 514 525 /* 515 526 ** Search database dbm for an index compatible with the one idxCreateFromCons() 516 527 ** would create from arguments pScan, pEq and pTail. If no error occurs and 517 528 ** such an index is found, return non-zero. Or, if no such index is found, ................................................................................ 808 819 if( memcmp(&zDetail[i], " USING INDEX ", 13)==0 ){ 809 820 zIdx = &zDetail[i+13]; 810 821 }else if( memcmp(&zDetail[i], " USING COVERING INDEX ", 22)==0 ){ 811 822 zIdx = &zDetail[i+22]; 812 823 } 813 824 if( zIdx ){ 814 825 int nIdx = 0; 815 - while( zIdx[nIdx]!='\0' && zIdx[nIdx]!=' ' ) nIdx++; 826 + while( zIdx[nIdx]!='\0' && (zIdx[nIdx]!=' ' || zIdx[nIdx+1]!='(') ){ 827 + nIdx++; 828 + } 816 829 sqlite3_bind_text(pSelect, 1, zIdx, nIdx, SQLITE_STATIC); 817 830 if( SQLITE_ROW==sqlite3_step(pSelect) ){ 818 831 i64 iRowid = sqlite3_column_int64(pSelect, 0); 819 832 const char *zSql = (const char*)sqlite3_column_text(pSelect, 1); 820 833 if( iRowid>=pCtx->iIdxRowid ){ 821 834 xOut(pOutCtx, zSql); 822 835 bFound = 1;
Changes to src/where.c.
3986 3986 /* ORDER BY callbacks */ 3987 3987 if( p->pOrderBy ){ 3988 3988 int i; 3989 3989 int bFirst = 1; 3990 3990 for(i=0; i<p->pOrderBy->nExpr; i++){ 3991 3991 Expr *pExpr = p->pOrderBy->a[i].pExpr; 3992 3992 CollSeq *pColl = sqlite3ExprCollSeq(pParse, pExpr); 3993 + assert( pColl || pParse->rc ); 3993 3994 pExpr = sqlite3ExprSkipCollate(pExpr); 3994 3995 if( pExpr->op==TK_COLUMN && pExpr->iTable==pItem->iCursor ){ 3995 3996 int iCol = pExpr->iColumn; 3996 - if( iCol>=0 ){ 3997 - x(pCtx, SQLITE_WHEREINFO_ORDERBY, pColl->zName, iCol, 0); 3997 + if( pColl && iCol>=0 ){ 3998 + int bDesc = p->pOrderBy->a[i].sortOrder; 3999 + x(pCtx, SQLITE_WHEREINFO_ORDERBY, pColl->zName, iCol, bDesc); 3998 4000 } 3999 4001 } 4000 4002 } 4001 4003 } 4002 4004 4003 4005 /* WHERE callbacks */ 4004 4006 whereTraceWC(pParse, pItem, p->pWC, 0);
Added test/shell6.test.
1 +# 2009 Nov 11 2 +# 3 +# The author disclaims copyright to this source code. In place of 4 +# a legal notice, here is a blessing: 5 +# 6 +# May you do good and not evil. 7 +# May you find forgiveness for yourself and forgive others. 8 +# May you share freely, never taking more than you give. 9 +# 10 +#*********************************************************************** 11 +# 12 +# The focus of this file is testing the CLI shell tool. Specifically, 13 +# the ".recommend" command. 14 +# 15 +# 16 + 17 +# Test plan: 18 +# 19 +# shell1-1.*: Basic command line option handling. 20 +# shell1-2.*: Basic "dot" command token parsing. 21 +# shell1-3.*: Basic test that "dot" command can be called. 22 +# 23 +set testdir [file dirname $argv0] 24 +source $testdir/tester.tcl 25 +set testprefix shell6 26 + 27 +if {$tcl_platform(platform)=="windows"} { 28 + set CLI "sqlite3.exe" 29 +} else { 30 + set CLI "./sqlite3" 31 +} 32 +if {![file executable $CLI]} { 33 + finish_test 34 + return 35 +} 36 + 37 + 38 +proc squish {txt} { 39 + regsub -all {[[:space:]]+} $txt { } 40 +} 41 + 42 +proc do_rec_test {tn sql res} { 43 + set res [squish [string trim $res]] 44 + set tst [subst -nocommands { 45 + squish [lindex [catchcmd [list -rec test.db {$sql;}]] 1] 46 + }] 47 + uplevel [list do_test $tn $tst $res] 48 +} 49 + 50 +proc do_setup_rec_test {tn setup sql res} { 51 + reset_db 52 + db eval $setup 53 + uplevel [list do_rec_test $tn $sql $res] 54 +} 55 + 56 + 57 +do_setup_rec_test 1.1 { CREATE TABLE t1(a, b, c) } { 58 + SELECT * FROM t1 59 +} { 60 + (no new indexes) 61 + 0|0|0|SCAN TABLE t1 62 +} 63 + 64 +do_setup_rec_test 1.2 { 65 + CREATE TABLE t1(a, b, c); 66 +} { 67 + SELECT * FROM t1 WHERE b>?; 68 +} { 69 + CREATE INDEX t1_idx_00000062 ON t1(b) 70 + 0|0|0|SEARCH TABLE t1 USING INDEX t1_idx_00000062 (b>?) 71 +} 72 + 73 +do_setup_rec_test 1.3 { 74 + CREATE TABLE t1(a, b, c); 75 +} { 76 + SELECT * FROM t1 WHERE b COLLATE nocase BETWEEN ? AND ? 77 +} { 78 + CREATE INDEX t1_idx_3e094c27 ON t1(b COLLATE NOCASE) 79 + 0|0|0|SEARCH TABLE t1 USING INDEX t1_idx_3e094c27 (b>? AND b<?) 80 +} 81 + 82 +do_setup_rec_test 1.4 { 83 + CREATE TABLE t1(a, b, c); 84 +} { 85 + SELECT a FROM t1 ORDER BY b; 86 +} { 87 + CREATE INDEX t1_idx_00000062 ON t1(b) 88 + 0|0|0|SCAN TABLE t1 USING INDEX t1_idx_00000062 89 +} 90 + 91 +do_setup_rec_test 1.5 { 92 + CREATE TABLE t1(a, b, c); 93 +} { 94 + SELECT a FROM t1 WHERE a=? ORDER BY b; 95 +} { 96 + CREATE INDEX t1_idx_000123a7 ON t1(a, b) 97 + 0|0|0|SEARCH TABLE t1 USING COVERING INDEX t1_idx_000123a7 (a=?) 98 +} 99 + 100 +do_setup_rec_test 1.6 { 101 + CREATE TABLE t1(a, b, c); 102 +} { 103 + SELECT min(a) FROM t1 104 +} { 105 + CREATE INDEX t1_idx_00000061 ON t1(a) 106 + 0|0|0|SEARCH TABLE t1 USING COVERING INDEX t1_idx_00000061 107 +} 108 + 109 +do_setup_rec_test 1.7 { 110 + CREATE TABLE t1(a, b, c); 111 +} { 112 + SELECT * FROM t1 ORDER BY a, b, c; 113 +} { 114 + CREATE INDEX t1_idx_033e95fe ON t1(a, b, c) 115 + 0|0|0|SCAN TABLE t1 USING COVERING INDEX t1_idx_033e95fe 116 +} 117 + 118 +do_setup_rec_test 1.8 { 119 + CREATE TABLE t1(a, b, c); 120 +} { 121 + SELECT * FROM t1 ORDER BY a ASC, b COLLATE nocase DESC, c ASC; 122 +} { 123 + CREATE INDEX t1_idx_5be6e222 ON t1(a, b COLLATE NOCASE DESC, c) 124 + 0|0|0|SCAN TABLE t1 USING COVERING INDEX t1_idx_5be6e222 125 +} 126 + 127 +do_setup_rec_test 1.9 { 128 + CREATE TABLE t1(a COLLATE NOCase, b, c); 129 +} { 130 + SELECT * FROM t1 WHERE a=? 131 +} { 132 + CREATE INDEX t1_idx_00000061 ON t1(a) 133 + 0|0|0|SEARCH TABLE t1 USING INDEX t1_idx_00000061 (a=?) 134 +} 135 + 136 + 137 +# Tables with names that require quotes. 138 +# 139 +do_setup_rec_test 8.1 { 140 + CREATE TABLE "t t"(a, b, c); 141 +} { 142 + SELECT * FROM "t t" WHERE a=? 143 +} { 144 + CREATE INDEX 't t_idx_00000061' ON 't t'(a) 145 + 0|0|0|SEARCH TABLE t t USING INDEX t t_idx_00000061 (a=?) 146 +} 147 + 148 +do_setup_rec_test 8.2 { 149 + CREATE TABLE "t t"(a, b, c); 150 +} { 151 + SELECT * FROM "t t" WHERE b BETWEEN ? AND ? 152 +} { 153 + CREATE INDEX 't t_idx_00000062' ON 't t'(b) 154 + 0|0|0|SEARCH TABLE t t USING INDEX t t_idx_00000062 (b>? AND b<?) 155 +} 156 + 157 +# Columns with names that require quotes. 158 +# 159 +do_setup_rec_test 9.1 { 160 + CREATE TABLE t3(a, "b b", c); 161 +} { 162 + SELECT * FROM t3 WHERE "b b" = ? 163 +} { 164 + CREATE INDEX t3_idx_00050c52 ON t3('b b') 165 + 0|0|0|SEARCH TABLE t3 USING INDEX t3_idx_00050c52 (b b=?) 166 +} 167 + 168 +do_setup_rec_test 9.2 { 169 + CREATE TABLE t3(a, "b b", c); 170 +} { 171 + SELECT * FROM t3 ORDER BY "b b" 172 +} { 173 + CREATE INDEX t3_idx_00050c52 ON t3('b b') 174 + 0|0|0|SCAN TABLE t3 USING INDEX t3_idx_00050c52 175 +} 176 + 177 +# Transitive constraints 178 +# 179 +do_setup_rec_test 10.1 { 180 + CREATE TABLE t5(a, b); 181 + CREATE TABLE t6(c, d); 182 +} { 183 + SELECT * FROM t5, t6 WHERE a=? AND b=c AND c=? 184 +} { 185 + CREATE INDEX t6_idx_00000063 ON t6(c) 186 + CREATE INDEX t5_idx_000123a7 ON t5(a, b) 187 + 0|0|1|SEARCH TABLE t6 USING INDEX t6_idx_00000063 (c=?) 188 + 0|1|0|SEARCH TABLE t5 USING COVERING INDEX t5_idx_000123a7 (a=? AND b=?) 189 +} 190 + 191 +finish_test