SQLite

Check-in [0b73406595]
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
Timelines: family | ancestors | descendants | both | schemalint
Files: files | file ages | folders
SHA1: 0b73406595c9a077399b0f4c17af3a826cf3612f
User & Date: dan 2016-02-22 19:51:08.971
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: fc18cc9293 user: dan tags: schemalint)
2016-02-22
19:51
Add test script shell6.test, containing tests for schemalint. (check-in: 0b73406595 user: dan tags: schemalint)
2016-02-19
07:53
Fix a couple of bugs in the schemalint code. (check-in: 02fbf699c0 user: dan tags: schemalint)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/shell_indexes.c.
30
31
32
33
34
35
36

37
38
39
40
41
42
43
*/
struct IdxConstraint {
  char *zColl;                    /* Collation sequence */
  int bRange;                     /* True for range, false for eq */
  int iCol;                       /* Constrained table column */
  i64 depmask;                    /* Dependency mask */
  int bFlag;                      /* Used by idxFindCompatible() */

  IdxConstraint *pNext;           /* Next constraint in pEq or pRange list */
  IdxConstraint *pLink;           /* See above */
};

/*
** A WHERE clause. Made up of IdxConstraint objects. Example WHERE clause:
**







>







30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
*/
struct IdxConstraint {
  char *zColl;                    /* Collation sequence */
  int bRange;                     /* True for range, false for eq */
  int iCol;                       /* Constrained table column */
  i64 depmask;                    /* Dependency mask */
  int bFlag;                      /* Used by idxFindCompatible() */
  int bDesc;                      /* True if ORDER BY <expr> DESC */
  IdxConstraint *pNext;           /* Next constraint in pEq or pRange list */
  IdxConstraint *pLink;           /* See above */
};

/*
** A WHERE clause. Made up of IdxConstraint objects. Example WHERE clause:
**
194
195
196
197
198
199
200
201
202
203


204



205


206
207
208
209
210
211
212
        p->pScan = pNew;
        p->pCurrent = &pNew->where;
        break;
      }

      case SQLITE_WHEREINFO_ORDERBY: {
        IdxConstraint *pNew = idxNewConstraint(&p->rc, zVal);
        IdxConstraint **pp;
        if( pNew==0 ) return;
        pNew->iCol = iVal;


        for(pp=&p->pScan->pOrder; *pp; pp=&(*pp)->pNext);



        *pp = pNew;


        break;
      }

      case SQLITE_WHEREINFO_EQUALS:
      case SQLITE_WHEREINFO_RANGE: {
        IdxConstraint *pNew = idxNewConstraint(&p->rc, zVal);
        if( pNew==0 ) return;







<


>
>
|
>
>
>
|
>
>







195
196
197
198
199
200
201

202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
        p->pScan = pNew;
        p->pCurrent = &pNew->where;
        break;
      }

      case SQLITE_WHEREINFO_ORDERBY: {
        IdxConstraint *pNew = idxNewConstraint(&p->rc, zVal);

        if( pNew==0 ) return;
        pNew->iCol = iVal;
        pNew->bDesc = (int)mask;
        if( p->pScan->pOrder==0 ){
          p->pScan->pOrder = pNew;
        }else{
          IdxConstraint *pIter;
          for(pIter=p->pScan->pOrder; pIter->pNext; pIter=pIter->pNext);
          pIter->pNext = pNew;
          pIter->pLink = pNew;
        }
        break;
      }

      case SQLITE_WHEREINFO_EQUALS:
      case SQLITE_WHEREINFO_RANGE: {
        IdxConstraint *pNew = idxNewConstraint(&p->rc, zVal);
        if( pNew==0 ) return;
504
505
506
507
508
509
510




511
512
513
514
515
516
517
  if( sqlite3_stricmp(p->zColl, pCons->zColl) ){
    if( idxIdentifierRequiresQuotes(pCons->zColl) ){
      zRet = idxAppendText(pRc, zRet, " COLLATE %Q", pCons->zColl);
    }else{
      zRet = idxAppendText(pRc, zRet, " COLLATE %s", pCons->zColl);
    }
  }




  return zRet;
}

/*
** Search database dbm for an index compatible with the one idxCreateFromCons()
** would create from arguments pScan, pEq and pTail. If no error occurs and 
** such an index is found, return non-zero. Or, if no such index is found,







>
>
>
>







511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
  if( sqlite3_stricmp(p->zColl, pCons->zColl) ){
    if( idxIdentifierRequiresQuotes(pCons->zColl) ){
      zRet = idxAppendText(pRc, zRet, " COLLATE %Q", pCons->zColl);
    }else{
      zRet = idxAppendText(pRc, zRet, " COLLATE %s", pCons->zColl);
    }
  }

  if( pCons->bDesc ){
    zRet = idxAppendText(pRc, zRet, " DESC");
  }
  return zRet;
}

/*
** Search database dbm for an index compatible with the one idxCreateFromCons()
** would create from arguments pScan, pEq and pTail. If no error occurs and 
** such an index is found, return non-zero. Or, if no such index is found,
808
809
810
811
812
813
814
815


816
817
818
819
820
821
822
      if( memcmp(&zDetail[i], " USING INDEX ", 13)==0 ){
        zIdx = &zDetail[i+13];
      }else if( memcmp(&zDetail[i], " USING COVERING INDEX ", 22)==0 ){
        zIdx = &zDetail[i+22];
      }
      if( zIdx ){
        int nIdx = 0;
        while( zIdx[nIdx]!='\0' && zIdx[nIdx]!=' ' ) nIdx++;


        sqlite3_bind_text(pSelect, 1, zIdx, nIdx, SQLITE_STATIC);
        if( SQLITE_ROW==sqlite3_step(pSelect) ){
          i64 iRowid = sqlite3_column_int64(pSelect, 0);
          const char *zSql = (const char*)sqlite3_column_text(pSelect, 1);
          if( iRowid>=pCtx->iIdxRowid ){
            xOut(pOutCtx, zSql);
            bFound = 1;







|
>
>







819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
      if( memcmp(&zDetail[i], " USING INDEX ", 13)==0 ){
        zIdx = &zDetail[i+13];
      }else if( memcmp(&zDetail[i], " USING COVERING INDEX ", 22)==0 ){
        zIdx = &zDetail[i+22];
      }
      if( zIdx ){
        int nIdx = 0;
        while( zIdx[nIdx]!='\0' && (zIdx[nIdx]!=' ' || zIdx[nIdx+1]!='(') ){
          nIdx++;
        }
        sqlite3_bind_text(pSelect, 1, zIdx, nIdx, SQLITE_STATIC);
        if( SQLITE_ROW==sqlite3_step(pSelect) ){
          i64 iRowid = sqlite3_column_int64(pSelect, 0);
          const char *zSql = (const char*)sqlite3_column_text(pSelect, 1);
          if( iRowid>=pCtx->iIdxRowid ){
            xOut(pOutCtx, zSql);
            bFound = 1;
Changes to src/where.c.
3986
3987
3988
3989
3990
3991
3992

3993
3994
3995
3996

3997
3998
3999
4000
4001
4002
4003
4004
        /* ORDER BY callbacks */
        if( p->pOrderBy ){
          int i;
          int bFirst = 1;
          for(i=0; i<p->pOrderBy->nExpr; i++){
            Expr *pExpr = p->pOrderBy->a[i].pExpr; 
            CollSeq *pColl = sqlite3ExprCollSeq(pParse, pExpr);

            pExpr = sqlite3ExprSkipCollate(pExpr);
            if( pExpr->op==TK_COLUMN && pExpr->iTable==pItem->iCursor ){
              int iCol = pExpr->iColumn;
              if( iCol>=0 ){

                x(pCtx, SQLITE_WHEREINFO_ORDERBY, pColl->zName, iCol, 0); 
              }
            }
          }
        }

        /* WHERE callbacks */
        whereTraceWC(pParse, pItem, p->pWC, 0);







>



|
>
|







3986
3987
3988
3989
3990
3991
3992
3993
3994
3995
3996
3997
3998
3999
4000
4001
4002
4003
4004
4005
4006
        /* ORDER BY callbacks */
        if( p->pOrderBy ){
          int i;
          int bFirst = 1;
          for(i=0; i<p->pOrderBy->nExpr; i++){
            Expr *pExpr = p->pOrderBy->a[i].pExpr; 
            CollSeq *pColl = sqlite3ExprCollSeq(pParse, pExpr);
            assert( pColl || pParse->rc );
            pExpr = sqlite3ExprSkipCollate(pExpr);
            if( pExpr->op==TK_COLUMN && pExpr->iTable==pItem->iCursor ){
              int iCol = pExpr->iColumn;
              if( pColl && iCol>=0 ){
                int bDesc = p->pOrderBy->a[i].sortOrder;
                x(pCtx, SQLITE_WHEREINFO_ORDERBY, pColl->zName, iCol, bDesc); 
              }
            }
          }
        }

        /* WHERE callbacks */
        whereTraceWC(pParse, pItem, p->pWC, 0);
Added test/shell6.test.






























































































































































































































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
# 2009 Nov 11
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
#
# The focus of this file is testing the CLI shell tool. Specifically,
# the ".recommend" command.
#
#

# Test plan:
#
#   shell1-1.*: Basic command line option handling.
#   shell1-2.*: Basic "dot" command token parsing.
#   shell1-3.*: Basic test that "dot" command can be called.
#
set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix shell6

if {$tcl_platform(platform)=="windows"} {
  set CLI "sqlite3.exe"
} else {
  set CLI "./sqlite3"
}
if {![file executable $CLI]} {
  finish_test
  return
}


proc squish {txt} {
  regsub -all {[[:space:]]+} $txt { }
}

proc do_rec_test {tn sql res} {
  set res [squish [string trim $res]]
  set tst [subst -nocommands { 
    squish [lindex [catchcmd [list -rec test.db {$sql;}]] 1]
  }]
  uplevel [list do_test $tn $tst $res]
}

proc do_setup_rec_test {tn setup sql res} {
  reset_db
  db eval $setup
  uplevel [list do_rec_test $tn $sql $res]
}


do_setup_rec_test 1.1 { CREATE TABLE t1(a, b, c) } {
  SELECT * FROM t1
} {
  (no new indexes)
  0|0|0|SCAN TABLE t1
}

do_setup_rec_test 1.2 {
  CREATE TABLE t1(a, b, c);
} {
  SELECT * FROM t1 WHERE b>?;
} {
  CREATE INDEX t1_idx_00000062 ON t1(b) 
  0|0|0|SEARCH TABLE t1 USING INDEX t1_idx_00000062 (b>?)
}

do_setup_rec_test 1.3 {
  CREATE TABLE t1(a, b, c);
} {
  SELECT * FROM t1 WHERE b COLLATE nocase BETWEEN ? AND ?
} {
  CREATE INDEX t1_idx_3e094c27 ON t1(b COLLATE NOCASE) 
  0|0|0|SEARCH TABLE t1 USING INDEX t1_idx_3e094c27 (b>? AND b<?)
}

do_setup_rec_test 1.4 {
  CREATE TABLE t1(a, b, c);
} {
  SELECT a FROM t1 ORDER BY b;
} {
  CREATE INDEX t1_idx_00000062 ON t1(b) 
  0|0|0|SCAN TABLE t1 USING INDEX t1_idx_00000062
}

do_setup_rec_test 1.5 {
  CREATE TABLE t1(a, b, c);
} {
  SELECT a FROM t1 WHERE a=? ORDER BY b;
} {
  CREATE INDEX t1_idx_000123a7 ON t1(a, b) 
  0|0|0|SEARCH TABLE t1 USING COVERING INDEX t1_idx_000123a7 (a=?)
}

do_setup_rec_test 1.6 {
  CREATE TABLE t1(a, b, c);
} {
  SELECT min(a) FROM t1
} {
  CREATE INDEX t1_idx_00000061 ON t1(a) 
  0|0|0|SEARCH TABLE t1 USING COVERING INDEX t1_idx_00000061
}

do_setup_rec_test 1.7 {
  CREATE TABLE t1(a, b, c);
} {
  SELECT * FROM t1 ORDER BY a, b, c;
} {
  CREATE INDEX t1_idx_033e95fe ON t1(a, b, c) 
  0|0|0|SCAN TABLE t1 USING COVERING INDEX t1_idx_033e95fe
}

do_setup_rec_test 1.8 {
  CREATE TABLE t1(a, b, c);
} {
  SELECT * FROM t1 ORDER BY a ASC, b COLLATE nocase DESC, c ASC;
} {
  CREATE INDEX t1_idx_5be6e222 ON t1(a, b COLLATE NOCASE DESC, c)
  0|0|0|SCAN TABLE t1 USING COVERING INDEX t1_idx_5be6e222
}

do_setup_rec_test 1.9 {
  CREATE TABLE t1(a COLLATE NOCase, b, c);
} {
  SELECT * FROM t1 WHERE a=?
} {
  CREATE INDEX t1_idx_00000061 ON t1(a) 
  0|0|0|SEARCH TABLE t1 USING INDEX t1_idx_00000061 (a=?)
}


# Tables with names that require quotes.
#
do_setup_rec_test 8.1 {
  CREATE TABLE "t t"(a, b, c);
} {
  SELECT * FROM "t t" WHERE a=?
} {
  CREATE INDEX 't t_idx_00000061' ON 't t'(a)
  0|0|0|SEARCH TABLE t t USING INDEX t t_idx_00000061 (a=?) 
}

do_setup_rec_test 8.2 {
  CREATE TABLE "t t"(a, b, c);
} {
  SELECT * FROM "t t" WHERE b BETWEEN ? AND ?
} {
  CREATE INDEX 't t_idx_00000062' ON 't t'(b) 
  0|0|0|SEARCH TABLE t t USING INDEX t t_idx_00000062 (b>? AND b<?)
}

# Columns with names that require quotes.
#
do_setup_rec_test 9.1 {
  CREATE TABLE t3(a, "b b", c);
} {
  SELECT * FROM t3 WHERE "b b" = ?
} {
  CREATE INDEX t3_idx_00050c52 ON t3('b b') 
  0|0|0|SEARCH TABLE t3 USING INDEX t3_idx_00050c52 (b b=?)
}

do_setup_rec_test 9.2 {
  CREATE TABLE t3(a, "b b", c);
} {
  SELECT * FROM t3 ORDER BY "b b"
} {
  CREATE INDEX t3_idx_00050c52 ON t3('b b') 
  0|0|0|SCAN TABLE t3 USING INDEX t3_idx_00050c52
}

# Transitive constraints
#
do_setup_rec_test 10.1 {
  CREATE TABLE t5(a, b);
  CREATE TABLE t6(c, d);
} {
  SELECT * FROM t5, t6 WHERE a=? AND b=c AND c=?
} {
  CREATE INDEX t6_idx_00000063 ON t6(c) 
  CREATE INDEX t5_idx_000123a7 ON t5(a, b) 
  0|0|1|SEARCH TABLE t6 USING INDEX t6_idx_00000063 (c=?) 
  0|1|0|SEARCH TABLE t5 USING COVERING INDEX t5_idx_000123a7 (a=? AND b=?)
}

finish_test