/ Check-in [518d82d3]
Login

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

Overview
Comment:Add the sqlite3_collation_needed() API and fix some error handling cases involving unknown collation sequences. (CVS 1563)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:518d82d3b1ab996d675f45c94d740c98578a04a6
User & Date: danielk1977 2004-06-10 10:51:48
Context
2004-06-10
10:51
Add the sqlite3_collation_needed() API and fix some error handling cases involving unknown collation sequences. (CVS 1564) check-in: 67500546 user: danielk1977 tags: trunk
10:51
Add the sqlite3_collation_needed() API and fix some error handling cases involving unknown collation sequences. (CVS 1563) check-in: 518d82d3 user: danielk1977 tags: trunk
10:50
Add the sqlite3_collation_needed() API and fix some error handling cases involving unknown collation sequences. (CVS 1562) check-in: edf069b9 user: danielk1977 tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/where.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222

223
224
225
226
227
228
229
...
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
...
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
...
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This module contains C code that generates VDBE code used to process
** the WHERE clause of SQL statements.
**
** $Id: where.c,v 1.103 2004/06/09 09:55:20 danielk1977 Exp $
*/
#include "sqliteInt.h"

/*
** The query generator uses an array of instances of this structure to
** help it analyze the subexpressions of the WHERE clause.  Each WHERE
** clause subexpression is separated from the others by an AND operator.
................................................................................
** the first nEqCol columns.
**
** All terms of the ORDER BY clause must be either ASC or DESC.  The
** *pbRev value is set to 1 if the ORDER BY clause is all DESC and it is
** set to 0 if the ORDER BY clause is all ASC.
*/
static Index *findSortingIndex(
  sqlite *db,
  Table *pTab,            /* The table to be sorted */
  int base,               /* Cursor number for pTab */
  ExprList *pOrderBy,     /* The ORDER BY clause */
  Index *pPreferredIdx,   /* Use this index, if possible and not NULL */
  int nEqCol,             /* Number of index columns used with == constraints */
  int *pbRev              /* Set to 1 if ORDER BY is DESC */
){
  int i, j;
  Index *pMatch;
  Index *pIdx;
  int sortOrder;


  assert( pOrderBy!=0 );
  assert( pOrderBy->nExpr>0 );
  sortOrder = pOrderBy->a[0].sortOrder;
  for(i=0; i<pOrderBy->nExpr; i++){
    Expr *p;
    if( pOrderBy->a[i].sortOrder!=sortOrder ){
................................................................................
  ** check for a matching index.
  */
  pMatch = 0;
  for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
    int nExpr = pOrderBy->nExpr;
    if( pIdx->nColumn < nEqCol || pIdx->nColumn < nExpr ) continue;
    for(i=j=0; i<nEqCol; i++){
      CollSeq *pColl = sqlite3ExprCollSeq(pOrderBy->a[j].pExpr);
      if( !pColl ) pColl = db->pDfltColl;
      if( pPreferredIdx->aiColumn[i]!=pIdx->aiColumn[i] ) break;
      if( pPreferredIdx->keyInfo.aColl[i]!=pIdx->keyInfo.aColl[i] ) break;
      if( j<nExpr && 
          pOrderBy->a[j].pExpr->iColumn==pIdx->aiColumn[i] &&
          pColl==pIdx->keyInfo.aColl[i]
      ){ 
        j++; 
      }
    }
    if( i<nEqCol ) continue;
    for(i=0; i+j<nExpr; i++){
      CollSeq *pColl = sqlite3ExprCollSeq(pOrderBy->a[i+j].pExpr);
      if( !pColl ) pColl = db->pDfltColl;
      if( pOrderBy->a[i+j].pExpr->iColumn!=pIdx->aiColumn[i+nEqCol] ||
          pColl!=pIdx->keyInfo.aColl[i+nEqCol] ) break;
    }
    if( i+j>=nExpr ){
      pMatch = pIdx;
      if( pIdx==pPreferredIdx ) break;
................................................................................
      int ltMask = 0;  /* Index columns covered by an x<... term */
      int gtMask = 0;  /* Index columns covered by an x>... term */
      int inMask = 0;  /* Index columns covered by an x IN .. term */
      int nEq, m, score;

      if( pIdx->nColumn>32 ) continue;  /* Ignore indices too many columns */
      for(j=0; j<nExpr; j++){
        CollSeq *pColl = sqlite3ExprCollSeq(aExpr[j].p->pLeft);
        if( !pColl && aExpr[j].p->pRight ){
          pColl = sqlite3ExprCollSeq(aExpr[j].p->pRight);
        }
        if( !pColl ){
          pColl = pParse->db->pDfltColl;
        }
        if( aExpr[j].idxLeft==iCur 
             && (aExpr[j].prereqRight & loopMask)==aExpr[j].prereqRight ){
          int iColumn = aExpr[j].p->pLeft->iColumn;
................................................................................
     }else if( iDirectEq[0]>=0 || iDirectLt[0]>=0 || iDirectGt[0]>=0 ){
       /* If the left-most column is accessed using its ROWID, then do
       ** not try to sort by index.
       */
       pSortIdx = 0;
     }else{
       int nEqCol = (pWInfo->a[0].score+4)/8;
       pSortIdx = findSortingIndex(pParse->db, pTab, pTabList->a[0].iCursor, 
                                   *ppOrderBy, pIdx, nEqCol, &bRev);
     }
     if( pSortIdx && (pIdx==0 || pIdx==pSortIdx) ){
       if( pIdx==0 ){
         pWInfo->a[0].pIdx = pSortIdx;
         pWInfo->a[0].iCur = pParse->nTab++;
         pWInfo->peakNTab = pParse->nTab;







|







 







|











>







 







|












|







 







|

|







 







|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
...
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
...
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
...
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This module contains C code that generates VDBE code used to process
** the WHERE clause of SQL statements.
**
** $Id: where.c,v 1.104 2004/06/10 10:51:48 danielk1977 Exp $
*/
#include "sqliteInt.h"

/*
** The query generator uses an array of instances of this structure to
** help it analyze the subexpressions of the WHERE clause.  Each WHERE
** clause subexpression is separated from the others by an AND operator.
................................................................................
** the first nEqCol columns.
**
** All terms of the ORDER BY clause must be either ASC or DESC.  The
** *pbRev value is set to 1 if the ORDER BY clause is all DESC and it is
** set to 0 if the ORDER BY clause is all ASC.
*/
static Index *findSortingIndex(
  Parse *pParse,
  Table *pTab,            /* The table to be sorted */
  int base,               /* Cursor number for pTab */
  ExprList *pOrderBy,     /* The ORDER BY clause */
  Index *pPreferredIdx,   /* Use this index, if possible and not NULL */
  int nEqCol,             /* Number of index columns used with == constraints */
  int *pbRev              /* Set to 1 if ORDER BY is DESC */
){
  int i, j;
  Index *pMatch;
  Index *pIdx;
  int sortOrder;
  sqlite *db = pParse->db;

  assert( pOrderBy!=0 );
  assert( pOrderBy->nExpr>0 );
  sortOrder = pOrderBy->a[0].sortOrder;
  for(i=0; i<pOrderBy->nExpr; i++){
    Expr *p;
    if( pOrderBy->a[i].sortOrder!=sortOrder ){
................................................................................
  ** check for a matching index.
  */
  pMatch = 0;
  for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
    int nExpr = pOrderBy->nExpr;
    if( pIdx->nColumn < nEqCol || pIdx->nColumn < nExpr ) continue;
    for(i=j=0; i<nEqCol; i++){
      CollSeq *pColl = sqlite3ExprCollSeq(pParse, pOrderBy->a[j].pExpr);
      if( !pColl ) pColl = db->pDfltColl;
      if( pPreferredIdx->aiColumn[i]!=pIdx->aiColumn[i] ) break;
      if( pPreferredIdx->keyInfo.aColl[i]!=pIdx->keyInfo.aColl[i] ) break;
      if( j<nExpr && 
          pOrderBy->a[j].pExpr->iColumn==pIdx->aiColumn[i] &&
          pColl==pIdx->keyInfo.aColl[i]
      ){ 
        j++; 
      }
    }
    if( i<nEqCol ) continue;
    for(i=0; i+j<nExpr; i++){
      CollSeq *pColl = sqlite3ExprCollSeq(pParse, pOrderBy->a[i+j].pExpr);
      if( !pColl ) pColl = db->pDfltColl;
      if( pOrderBy->a[i+j].pExpr->iColumn!=pIdx->aiColumn[i+nEqCol] ||
          pColl!=pIdx->keyInfo.aColl[i+nEqCol] ) break;
    }
    if( i+j>=nExpr ){
      pMatch = pIdx;
      if( pIdx==pPreferredIdx ) break;
................................................................................
      int ltMask = 0;  /* Index columns covered by an x<... term */
      int gtMask = 0;  /* Index columns covered by an x>... term */
      int inMask = 0;  /* Index columns covered by an x IN .. term */
      int nEq, m, score;

      if( pIdx->nColumn>32 ) continue;  /* Ignore indices too many columns */
      for(j=0; j<nExpr; j++){
        CollSeq *pColl = sqlite3ExprCollSeq(pParse, aExpr[j].p->pLeft);
        if( !pColl && aExpr[j].p->pRight ){
          pColl = sqlite3ExprCollSeq(pParse, aExpr[j].p->pRight);
        }
        if( !pColl ){
          pColl = pParse->db->pDfltColl;
        }
        if( aExpr[j].idxLeft==iCur 
             && (aExpr[j].prereqRight & loopMask)==aExpr[j].prereqRight ){
          int iColumn = aExpr[j].p->pLeft->iColumn;
................................................................................
     }else if( iDirectEq[0]>=0 || iDirectLt[0]>=0 || iDirectGt[0]>=0 ){
       /* If the left-most column is accessed using its ROWID, then do
       ** not try to sort by index.
       */
       pSortIdx = 0;
     }else{
       int nEqCol = (pWInfo->a[0].score+4)/8;
       pSortIdx = findSortingIndex(pParse, pTab, pTabList->a[0].iCursor, 
                                   *ppOrderBy, pIdx, nEqCol, &bRev);
     }
     if( pSortIdx && (pIdx==0 || pIdx==pSortIdx) ){
       if( pIdx==0 ){
         pWInfo->a[0].pIdx = pSortIdx;
         pWInfo->a[0].iCur = pParse->nTab++;
         pWInfo->peakNTab = pParse->nTab;

Added test/collate3.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
192
193
194
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
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
#
# The author or author's hereby grant to the public domain a non-exclusive,
# fully paid-up, perpetual, license in the software and all related
# intellectual property to make, have made, use, have used, reproduce,
# prepare derivative works, distribute, perform and display the work.  
#
#*************************************************************************
# This file implements regression tests for SQLite library. The
# focus of this file is testing that when the user tries to use an 
# unknown or undefined collation type SQLite handles this correctly.
# Also some other error cases are tested.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl

#
# Tests are organised as follows:
#
# collate3.1.* - Errors related to unknown collation sequences.
# collate3.2.* - Errors related to undefined collation sequences.
# collate3.3.* - Writing to a table that has an index with an undefined c.s.
# collate3.4.* - Misc errors.
# collate3.5.* - Collation factory.
#

#
# These tests ensure that when a user executes a statement with an 
# unknown collation sequence an error is returned.
#
do_test collate3-1.0 {
  execsql {
    CREATE TABLE collate3t1(c1);
  }
} {}
do_test collate3-1.1 {
  catchsql {
    SELECT * FROM collate3t1 ORDER BY 1 collate garbage;
  }
} {1 {no such collation sequence: garbage}}
do_test collate3-1.2 {
  catchsql {
    CREATE TABLE collate3t2(c1 collate garbage);
  }
} {1 {no such collation sequence: garbage}}
do_test collate3-1.3 {
  catchsql {
    CREATE INDEX collate3i1 ON collate3t1(c1 COLLATE garbage);
  }
} {1 {no such collation sequence: garbage}}

execsql {
  DROP TABLE collate3t1;
}

#
# Create a table with a default collation sequence, then close
# and re-open the database without re-registering the collation
# sequence. Then make sure the library stops us from using
# the collation sequence in:
# * an explicitly collated ORDER BY
# * an ORDER BY that uses the default collation sequence
# * an expression (=)
# * a CREATE TABLE statement
# * a CREATE INDEX statement that uses a default collation sequence
# * a GROUP BY that uses the default collation sequence
# * a SELECT DISTINCT that uses the default collation sequence
# * Compound SELECTs that uses the default collation sequence
# * An ORDER BY on a compound SELECT with an explicit ORDER BY.
#
do_test collate3-2.0 {
  db collate string_compare {string compare}
  execsql {
    CREATE TABLE collate3t1(c1 COLLATE string_compare, c2);
  }
  db close
  sqlite db test.db
  expr 0
} 0
do_test collate3-2.1 {
  catchsql {
    SELECT * FROM collate3t1 ORDER BY 1 COLLATE string_compare;
  }
} {1 {no such collation sequence: string_compare}} 
do_test collate3-2.2 {
  catchsql {
    SELECT * FROM collate3t1 ORDER BY c1;
  }
} {1 {no such collation sequence: string_compare}} 
do_test collate3-2.3 {
  catchsql {
    SELECT * FROM collate3t1 WHERE c1 = 'xxx';
  }
} {1 {no such collation sequence: string_compare}} 
do_test collate3-2.4 {
  catchsql {
    CREATE TABLE collate3t2(c1 COLLATE string_compare);
  }
} {1 {no such collation sequence: string_compare}} 
do_test collate3-2.5 {
  catchsql {
    CREATE INDEX collate3t1_i1 ON collate3t1(c1);
  }
} {1 {no such collation sequence: string_compare}}
do_test collate3-2.6 {
  catchsql {
    SELECT * FROM collate3t1;
  }
} {0 {}}

# FIX ME
if 0 {

do_test collate3-2.7 {
  catchsql {
    SELECT * FROM collate3t1 GROUP BY c1;
  }
} {1 {no such collation sequence: string_compare}} 
do_test collate3-2.8 {
  catchsql {
    SELECT DISTINCT c1 FROM collate3t1;
  }
} {1 {no such collation sequence: string_compare}} 
do_test collate3-2.9 {
  catchsql {
    SELECT c1 FROM collate3t1 UNION SELECT c1 FROM collate3t1;
  }
} {1 {no such collation sequence: string_compare}} 
do_test collate3-2.10 {
  catchsql {
    SELECT c1 FROM collate3t1 EXCEPT SELECT c1 FROM collate3t1;
  }
} {1 {no such collation sequence: string_compare}} 
do_test collate3-2.11 {
  catchsql {
    SELECT c1 FROM collate3t1 INTERSECT SELECT c1 FROM collate3t1;
  }
} {1 {no such collation sequence: string_compare}} 
do_test collate3-2.12 {
  catchsql {
    SELECT c1 FROM collate3t1 UNION ALL SELECT c1 FROM collate3t1;
  }
} {0 {}}
do_test collate3-2.13 {
  catchsql {
    SELECT 10 UNION ALL SELECT 20 ORDER BY 1 COLLATE string_compare;
  }
} {1 {no such collation sequence: string_compare}} 
do_test collate3-2.14 {
  catchsql {
    SELECT 10 INTERSECT SELECT 20 ORDER BY 1 COLLATE string_compare;
  }
} {1 {no such collation sequence: string_compare}} 
do_test collate3-2.15 {
  catchsql {
    SELECT 10 EXCEPT SELECT 20 ORDER BY 1 COLLATE string_compare;
  }
} {1 {no such collation sequence: string_compare}} 
do_test collate3-2.16 {
  catchsql {
    SELECT 10 UNION SELECT 20 ORDER BY 1 COLLATE string_compare;
  }
} {1 {no such collation sequence: string_compare}} 
do_test collate3-2.17 {
  catchsql {
    SELECT c1 FROM collate3t1 UNION ALL SELECT c1 FROM collate3t1 ORDER BY 1;
  }
} {1 {no such collation sequence: string_compare}} 

}


#
# Create an index that uses a collation sequence then close and
# re-open the database without re-registering the collation
# sequence. Then check that for the table with the index 
# * An INSERT fails,
# * An UPDATE on the column with the index fails,
# * An UPDATE on a different column succeeds.
# * A DELETE with a WHERE clause fails
# * A DELETE without a WHERE clause succeeds
#
# Also, ensure that the restrictions tested by collate3-2.* still
# apply after the index has been created.
#
do_test collate3-3.0 {
  db collate string_compare {string compare}
  execsql {
    CREATE INDEX collate3t1_i1 ON collate3t1(c1);
    INSERT INTO collate3t1 VALUES('xxx', 'yyy');
  }
  db close
  sqlite db test.db
  expr 0
} 0
db eval {select * from collate3t1}
breakpoint
do_test collate3-3.1 {
  catchsql {
    INSERT INTO collate3t1 VALUES('xxx', 0);
  }
} {1 {no such collation sequence: string_compare}} 
do_test collate3-3.2 {
  catchsql {
    UPDATE collate3t1 SET c1 = 'xxx';
  }
} {1 {no such collation sequence: string_compare}} 
do_test collate3-3.3 {
  catchsql {
    UPDATE collate3t1 SET c2 = 'xxx';
  }
} {0 {}}
do_test collate3-3.4 {
  catchsql {
    DELETE FROM collate3t1 WHERE 1;
  }
} {1 {no such collation sequence: string_compare}} 
do_test collate3-3.5 {
  catchsql {
    SELECT * FROM collate3t1;
  }
} {0 {xxx xxx}}
do_test collate3-3.6 {
  catchsql {
    DELETE FROM collate3t1;
  }
} {0 {}}
do_test collate3-3.8 {
  catchsql {
    PRAGMA integrity_check
  }
} {1 {no such collation sequence: string_compare}}
do_test collate3-3.9 {
  catchsql {
    SELECT * FROM collate3t1;
  }
} {0 {}}
do_test collate3-3.10 {
  catchsql {
    SELECT * FROM collate3t1 ORDER BY 1 COLLATE string_compare;
  }
} {1 {no such collation sequence: string_compare}} 
do_test collate3-3.11 {
  catchsql {
    SELECT * FROM collate3t1 ORDER BY c1;
  }
} {1 {no such collation sequence: string_compare}} 
do_test collate3-3.12 {
  catchsql {
    SELECT * FROM collate3t1 WHERE c1 = 'xxx';
  }
} {1 {no such collation sequence: string_compare}} 
do_test collate3-3.13 {
  catchsql {
    CREATE TABLE collate3t2(c1 COLLATE string_compare);
  }
} {1 {no such collation sequence: string_compare}} 
do_test collate3-3.14 {
  catchsql {
    CREATE INDEX collate3t1_i2 ON collate3t1(c1);
  }
} {1 {no such collation sequence: string_compare}} 
do_test collate3-3.15 {
  execsql {
    DROP TABLE collate3t1;
  }
} {}

# Check we can create an index that uses an explicit collation 
# sequence and then close and re-open the database.
do_test collate3-4.6 {
  db collate user_defined "string compare"
  execsql {
    CREATE TABLE collate3t1(a, b);
    INSERT INTO collate3t1 VALUES('hello', NULL);
    CREATE INDEX collate3i1 ON collate3t1(a COLLATE user_defined);
  }
} {}
do_test collate3-4.7 {
  db close
  sqlite db test.db
  catchsql {
    SELECT * FROM collate3t1 ORDER BY a COLLATE user_defined;
  }
} {1 {no such collation sequence: user_defined}}
do_test collate3-4.8 {
  db collate user_defined "string compare"
  catchsql {
    SELECT * FROM collate3t1 ORDER BY a COLLATE user_defined;
  }
} {0 {hello {}}}
do_test collate3-4.8 {
  db close
  lindex [catch {
    sqlite db test.db
  }] 0
} {0}
do_test collate3-4.8 {
  execsql {
    DROP TABLE collate3t1;
  }
} {}

# Compare strings as numbers.
proc numeric_compare {lhs rhs} {
  if {$rhs > $lhs} {
    set res -1
  } else {
    set res [expr ($lhs > $rhs)?1:0]
  }
  return $res
}

# Check we can create a view that uses an explicit collation 
# sequence and then close and re-open the database.
do_test collate3-4.9 {
  db collate user_defined numeric_compare
  execsql {
    CREATE TABLE collate3t1(a, b);
    INSERT INTO collate3t1 VALUES('2', NULL);
    INSERT INTO collate3t1 VALUES('101', NULL);
    INSERT INTO collate3t1 VALUES('12', NULL);
    CREATE VIEW collate3v1 AS SELECT * FROM collate3t1 
        ORDER BY 1 COLLATE user_defined;
    SELECT * FROM collate3v1;
  }
} {2 {} 12 {} 101 {}}
do_test collate3-4.10 {
  db close
  sqlite db test.db
  catchsql {
    SELECT * FROM collate3v1;
  }
} {1 {no such collation sequence: user_defined}}
do_test collate3-4.11 {
  db collate user_defined numeric_compare
  catchsql {
    SELECT * FROM collate3v1;
  }
} {0 {2 {} 12 {} 101 {}}}
do_test collate3-4.12 {
  execsql {
    DROP TABLE collate3t1;
  }
} {}

#
# Test the collation factory. In the code, the "no such collation sequence"
# message is only generated in two places. So these tests just test that
# the collation factory can be called once from each of those points.
#
do_test collate3-5.0 {
  catchsql {
    CREATE TABLE collate3t1(a);
    INSERT INTO collate3t1 VALUES(10);
    SELECT a FROM collate3t1 ORDER BY 1 COLLATE unk;
  }
} {1 {no such collation sequence: unk}}
do_test collate3-5.1 {
  set ::cfact_cnt 0
  proc cfact {nm} {
    db collate $nm {string compare}
    incr ::cfact_cnt
  }
  db collation_needed cfact
} {}
do_test collate3-5.2 {
  catchsql {
    SELECT a FROM collate3t1 ORDER BY 1 COLLATE unk;
  }
} {0 10}
do_test collate3-5.3 {
  set ::cfact_cnt
} {1}
do_test collate3-5.4 {
  catchsql {
    SELECT a FROM collate3t1 ORDER BY 1 COLLATE unk;
  }
} {0 10}
do_test collate3-5.5 {
  set ::cfact_cnt
} {1}
do_test collate3-5.6 {
  catchsql {
    SELECT a FROM collate3t1 ORDER BY 1 COLLATE unk;
  }
} {0 10}
do_test collate3-5.7 {
  execsql {
    DROP TABLE collate3t1;
    CREATE TABLE collate3t1(a COLLATE unk);
  }
  db close
  sqlite db test.db
  catchsql {
    SELECT a FROM collate3t1 ORDER BY 1;
  }
} {1 {no such collation sequence: unk}}
do_test collate3-5.8 {
  set ::cfact_cnt 0
  proc cfact {nm} {
    db collate $nm {string compare}
    incr ::cfact_cnt
  }
  db collation_needed cfact
  catchsql {
    SELECT a FROM collate3t1 ORDER BY 1;
  }
} {0 {}}

do_test collate3-5.9 {
  execsql {
    DROP TABLE collate3t1;
  }
} {}

finish_test

Changes to test/tclsqlite.test.

11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
..
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
# This file implements regression tests for TCL interface to the
# SQLite library. 
#
# Actually, all tests are based on the TCL interface, so the main
# interface is pretty well tested.  This file contains some addition
# tests for fringe issues that the main test suite does not cover.
#
# $Id: tclsqlite.test,v 1.22 2004/06/09 09:55:20 danielk1977 Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Check the error messages generated by tclsqlite
#
if {[sqlite -has-codec]} {
................................................................................
do_test tcl-1.1 {
  set v [catch {sqlite bogus} msg]
  lappend v $msg
} [list 1 "wrong # args: should be \"$r\""]
do_test tcl-1.2 {
  set v [catch {db bogus} msg]
  lappend v $msg
} {1 {bad option "bogus": must be authorizer, busy, changes, close, commit_hook, complete, errorcode, eval, function, last_insert_rowid, last_statement_changes, onecolumn, progress, rekey, timeout, trace, or collate}}
do_test tcl-1.3 {
  execsql {CREATE TABLE t1(a int, b int)}
  execsql {INSERT INTO t1 VALUES(10,20)}
  set v [catch {
    db eval {SELECT * FROM t1} data {
      error "The error message"
    }







|







 







|







11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
..
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
# This file implements regression tests for TCL interface to the
# SQLite library. 
#
# Actually, all tests are based on the TCL interface, so the main
# interface is pretty well tested.  This file contains some addition
# tests for fringe issues that the main test suite does not cover.
#
# $Id: tclsqlite.test,v 1.23 2004/06/10 10:51:53 danielk1977 Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Check the error messages generated by tclsqlite
#
if {[sqlite -has-codec]} {
................................................................................
do_test tcl-1.1 {
  set v [catch {sqlite bogus} msg]
  lappend v $msg
} [list 1 "wrong # args: should be \"$r\""]
do_test tcl-1.2 {
  set v [catch {db bogus} msg]
  lappend v $msg
} {1 {bad option "bogus": must be authorizer, busy, changes, close, commit_hook, complete, errorcode, eval, function, last_insert_rowid, last_statement_changes, onecolumn, progress, rekey, timeout, trace, collate, or collation_needed}}
do_test tcl-1.3 {
  execsql {CREATE TABLE t1(a int, b int)}
  execsql {INSERT INTO t1 VALUES(10,20)}
  set v [catch {
    db eval {SELECT * FROM t1} data {
      error "The error message"
    }

Changes to test/temptable.test.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for temporary tables and indices.
#
# $Id: temptable.test,v 1.11 2004/02/14 16:31:04 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Create an alternative connection to the database
#
do_test temptable-1.0 {
................................................................................
  }
} {3 4}
do_test temptable-4.10.1 {
  catchsql {
    SELECT * FROM t2;
  } db2
} {0 {1 2}}
#do_test temptable-4.10.2 {
#  catchsql {
#    SELECT name FROM sqlite_master WHERE type='table'
#  } db2
#} {1 {database schema has changed}}
do_test temptable-4.10.3 {
  catchsql {
    SELECT name FROM sqlite_master WHERE type='table'
  } db2
} {0 {t1 t2}}
do_test temptable-4.11 {
  execsql {







|







 







|
|
|
|
|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for temporary tables and indices.
#
# $Id: temptable.test,v 1.12 2004/06/10 10:51:53 danielk1977 Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Create an alternative connection to the database
#
do_test temptable-1.0 {
................................................................................
  }
} {3 4}
do_test temptable-4.10.1 {
  catchsql {
    SELECT * FROM t2;
  } db2
} {0 {1 2}}
do_test temptable-4.10.2 {
  catchsql {
    SELECT name FROM sqlite_master WHERE type='table'
  } db2
} {1 {database schema has changed}}
do_test temptable-4.10.3 {
  catchsql {
    SELECT name FROM sqlite_master WHERE type='table'
  } db2
} {0 {t1 t2}}
do_test temptable-4.11 {
  execsql {

Changes to www/datatype3.tcl.

1
2
3
4
5
6
7
8
...
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
set rcsid {$Id: datatype3.tcl,v 1.4 2004/06/09 09:55:20 danielk1977 Exp $}
source common.tcl
header {Datatypes In SQLite Version 3}
puts {
<h2>Datatypes In SQLite Version 3</h2>

<h3>1. Storage Classes</h3>

................................................................................


<h4>7.1 Assigning Collation Sequences from SQL</h4>

<p>
Each column of each table has a default collation type. If a collation type
other than BINARY is required, a COLLATE clause is specified as part of the
<a href="lang.html#createtable">column definition</a> to define it.used as
illustrated in the example below to 
</p>  

<p>
Whenever two text values are compared by SQLite, a collation sequence is
used to determine the results of the comparison according to the following
rules. Sections 3 and 5 of this document describe the circumstances under
which such a comparison takes place.
|







 







|
<







1
2
3
4
5
6
7
8
...
323
324
325
326
327
328
329
330

331
332
333
334
335
336
337
set rcsid {$Id: datatype3.tcl,v 1.5 2004/06/10 10:51:53 danielk1977 Exp $}
source common.tcl
header {Datatypes In SQLite Version 3}
puts {
<h2>Datatypes In SQLite Version 3</h2>

<h3>1. Storage Classes</h3>

................................................................................


<h4>7.1 Assigning Collation Sequences from SQL</h4>

<p>
Each column of each table has a default collation type. If a collation type
other than BINARY is required, a COLLATE clause is specified as part of the
<a href="lang.html#createtable">column definition</a> to define it. 

</p>  

<p>
Whenever two text values are compared by SQLite, a collation sequence is
used to determine the results of the comparison according to the following
rules. Sections 3 and 5 of this document describe the circumstances under
which such a comparison takes place.