/ Check-in [0b734065]
Login

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: 0b73406595c9a077399b0f4c17af3a826cf3612f
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
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

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