SQLite

Check-in [72ee21c05e]
Login

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

Overview
Comment:The case_sensitive_like pragma added. Test cases added for the LIKE optimization. (CVS 2592)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 72ee21c05e618b6f46f5460f8c85779c72fe32d7
User & Date: drh 2005-08-14 01:20:38.000
Context
2005-08-14
01:34
Declare local-use functions as static. Ticket #1363. (CVS 2593) (check-in: 94efd79088 user: drh tags: trunk)
01:20
The case_sensitive_like pragma added. Test cases added for the LIKE optimization. (CVS 2592) (check-in: 72ee21c05e user: drh tags: trunk)
2005-08-13
18:15
Provide grave accent quoting of identifiers for MySQL compatibility. Ticket #1337. (CVS 2591) (check-in: 6b7a4e9752 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/callback.c.
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
**    May you share freely, never taking more than you give.
**
*************************************************************************
**
** This file contains functions used to access the internal hash tables
** of user defined functions and collation sequences.
**
** $Id: callback.c,v 1.2 2005/05/25 10:45:10 danielk1977 Exp $
*/

#include "sqliteInt.h"

/*
** Invoke the 'collation needed' callback to request a collation sequence
** in the database text encoding of name zName, length nName.







|







9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
**    May you share freely, never taking more than you give.
**
*************************************************************************
**
** This file contains functions used to access the internal hash tables
** of user defined functions and collation sequences.
**
** $Id: callback.c,v 1.3 2005/08/14 01:20:38 drh Exp $
*/

#include "sqliteInt.h"

/*
** Invoke the 'collation needed' callback to request a collation sequence
** in the database text encoding of name zName, length nName.
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
** array of three CollSeq structures. The first is the collation sequence
** prefferred for UTF-8, the second UTF-16le, and the third UTF-16be.
**
** Stored immediately after the three collation sequences is a copy of
** the collation sequence name. A pointer to this string is stored in
** each collation sequence structure.
*/
static CollSeq * findCollSeqEntry(
  sqlite3 *db,
  const char *zName,
  int nName,
  int create
){
  CollSeq *pColl;
  if( nName<0 ) nName = strlen(zName);







|







143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
** array of three CollSeq structures. The first is the collation sequence
** prefferred for UTF-8, the second UTF-16le, and the third UTF-16be.
**
** Stored immediately after the three collation sequences is a copy of
** the collation sequence name. A pointer to this string is stored in
** each collation sequence structure.
*/
static CollSeq *findCollSeqEntry(
  sqlite3 *db,
  const char *zName,
  int nName,
  int create
){
  CollSeq *pColl;
  if( nName<0 ) nName = strlen(zName);
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
  }

  /* If the createFlag parameter is true, and the seach did not reveal an
  ** exact match for the name, number of arguments and encoding, then add a
  ** new entry to the hash table and return it.
  */
  if( createFlag && bestmatch<6 && 
      (pBest = sqliteMalloc(sizeof(*pBest)+nName+1)) ){
    pBest->nArg = nArg;
    pBest->pNext = pFirst;
    pBest->zName = (char*)&pBest[1];
    pBest->iPrefEnc = enc;
    memcpy(pBest->zName, zName, nName);
    pBest->zName[nName] = 0;
    if( pBest==sqlite3HashInsert(&db->aFunc,pBest->zName,nName,(void*)pBest) ){
      sqliteFree(pBest);
      return 0;
    }







|


<







282
283
284
285
286
287
288
289
290
291

292
293
294
295
296
297
298
  }

  /* If the createFlag parameter is true, and the seach did not reveal an
  ** exact match for the name, number of arguments and encoding, then add a
  ** new entry to the hash table and return it.
  */
  if( createFlag && bestmatch<6 && 
      (pBest = sqliteMalloc(sizeof(*pBest)+nName)) ){
    pBest->nArg = nArg;
    pBest->pNext = pFirst;

    pBest->iPrefEnc = enc;
    memcpy(pBest->zName, zName, nName);
    pBest->zName[nName] = 0;
    if( pBest==sqlite3HashInsert(&db->aFunc,pBest->zName,nName,(void*)pBest) ){
      sqliteFree(pBest);
      return 0;
    }
Changes to src/func.c.
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28



29
30
31
32
33
34
35
** This file contains the C functions that implement various SQL
** functions of SQLite.  
**
** There is only one exported symbol in this file - the function
** sqliteRegisterBuildinFunctions() found at the bottom of the file.
** All other code has file scope.
**
** $Id: func.c,v 1.103 2005/08/13 03:07:47 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>
#include <math.h>
#include <stdlib.h>
#include <assert.h>
#include "vdbeInt.h"
#include "os.h"




static CollSeq *sqlite3GetFuncCollSeq(sqlite3_context *context){
  return context->pColl;
}

/*
** Implementation of the non-aggregate min() and max() functions
*/







|









>
>
>







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
** This file contains the C functions that implement various SQL
** functions of SQLite.  
**
** There is only one exported symbol in this file - the function
** sqliteRegisterBuildinFunctions() found at the bottom of the file.
** All other code has file scope.
**
** $Id: func.c,v 1.104 2005/08/14 01:20:38 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>
#include <math.h>
#include <stdlib.h>
#include <assert.h>
#include "vdbeInt.h"
#include "os.h"

/*
** Return the collating function associated with a function.
*/
static CollSeq *sqlite3GetFuncCollSeq(sqlite3_context *context){
  return context->pColl;
}

/*
** Implementation of the non-aggregate min() and max() functions
*/
304
305
306
307
308
309
310

311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
*/
struct compareInfo {
  u8 matchAll;
  u8 matchOne;
  u8 matchSet;
  u8 noCase;
};

static const struct compareInfo globInfo = { '*', '?', '[', 0 };
#ifndef SQLITE_CASE_SENSITIVE_LIKE
  /* The correct SQL-92 behavior is for the LIKE operator to ignore
  ** case.  Thus  'a' LIKE 'A' would be true. */
  static const struct compareInfo likeInfo = { '%', '_',   0, 1 };
#else
  /* If SQLITE_CASE_SENSITIVE_LIKE is defined, then the LIKE operator
  ** is case sensitive causing 'a' LIKE 'A' to be false */
  static const struct compareInfo likeInfo = { '%', '_',   0, 0 };
#endif

/*
** X is a pointer to the first byte of a UTF-8 character.  Increment
** X so that it points to the next character.  This only works right
** if X points to a well-formed UTF-8 string.
*/
#define sqliteNextChar(X)  while( (0xc0&*++(X))==0x80 ){}







>

<
|
|
|
<
|
|
|
<







307
308
309
310
311
312
313
314
315

316
317
318

319
320
321

322
323
324
325
326
327
328
*/
struct compareInfo {
  u8 matchAll;
  u8 matchOne;
  u8 matchSet;
  u8 noCase;
};

static const struct compareInfo globInfo = { '*', '?', '[', 0 };

/* The correct SQL-92 behavior is for the LIKE operator to ignore
** case.  Thus  'a' LIKE 'A' would be true. */
static const struct compareInfo likeInfoNorm = { '%', '_',   0, 1 };

/* If SQLITE_CASE_SENSITIVE_LIKE is defined, then the LIKE operator
** is case sensitive causing 'a' LIKE 'A' to be false */
static const struct compareInfo likeInfoAlt = { '%', '_',   0, 0 };


/*
** X is a pointer to the first byte of a UTF-8 character.  Increment
** X so that it points to the next character.  This only works right
** if X points to a well-formed UTF-8 string.
*/
#define sqliteNextChar(X)  while( (0xc0&*++(X))==0x80 ){}
455
456
457
458
459
460
461









462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
      zString++;
      prevEscape = 0;
    }
  }
  return *zString==0;
}











/*
** Implementation of the like() SQL function.  This function implements
** the build-in LIKE operator.  The first argument to the function is the
** pattern and the second argument is the string.  So, the SQL statements:
**
**       A LIKE B
**
** is implemented as like(B,A).
**
** If the pointer retrieved by via a call to sqlite3_user_data() is
** not NULL, then this function uses UTF-16. Otherwise UTF-8.
*/
static void likeFunc(
  sqlite3_context *context, 
  int argc, 
  sqlite3_value **argv
){
  const unsigned char *zA = sqlite3_value_text(argv[0]);







>
>
>
>
>
>
>
>
>










|
|







456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
      zString++;
      prevEscape = 0;
    }
  }
  return *zString==0;
}

/*
** Count the number of times that the LIKE operator (or GLOB which is
** just a variation of LIKE) gets called.  This is used for testing
** only.
*/
#ifdef SQLITE_TEST
int sqlite3_like_count = 0;
#endif


/*
** Implementation of the like() SQL function.  This function implements
** the build-in LIKE operator.  The first argument to the function is the
** pattern and the second argument is the string.  So, the SQL statements:
**
**       A LIKE B
**
** is implemented as like(B,A).
**
** This same function (with a different compareInfo structure) computes
** the GLOB operator.
*/
static void likeFunc(
  sqlite3_context *context, 
  int argc, 
  sqlite3_value **argv
){
  const unsigned char *zA = sqlite3_value_text(argv[0]);
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
      sqlite3_result_error(context, 
          "ESCAPE expression must be a single character", -1);
      return;
    }
    escape = sqlite3ReadUtf8(zEsc);
  }
  if( zA && zB ){
    sqlite3_result_int(context, patternCompare(zA, zB, &likeInfo, escape));
  }
}

/*
** Implementation of the glob() SQL function.  This function implements
** the build-in GLOB operator.  The first argument to the function is the
** string and the second argument is the pattern.  So, the SQL statements:
**
**       A GLOB B
**
** is implemented as glob(B,A).
*/
static void globFunc(sqlite3_context *context, int arg, sqlite3_value **argv){
  const unsigned char *zA = sqlite3_value_text(argv[0]);
  const unsigned char *zB = sqlite3_value_text(argv[1]);
  if( zA && zB ){
    sqlite3_result_int(context, patternCompare(zA, zB, &globInfo, 0));
  }
}

/*
** Implementation of the NULLIF(x,y) function.  The result is the first
** argument if the arguments are different.  The result is NULL if the
** arguments are equal to each other.







|
<
<
|
<
<
<
<
<
<
<
<
<
<
|
<
|
|







499
500
501
502
503
504
505
506


507










508

509
510
511
512
513
514
515
516
517
      sqlite3_result_error(context, 
          "ESCAPE expression must be a single character", -1);
      return;
    }
    escape = sqlite3ReadUtf8(zEsc);
  }
  if( zA && zB ){
    struct compareInfo *pInfo = sqlite3_user_data(context);


#ifdef SQLITE_TEST










    sqlite3_like_count++;

#endif
    sqlite3_result_int(context, patternCompare(zA, zB, pInfo, escape));
  }
}

/*
** Implementation of the NULLIF(x,y) function.  The result is the first
** argument if the arguments are different.  The result is NULL if the
** arguments are equal to each other.
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
    { "upper",              1, 0, SQLITE_UTF8,    0, upperFunc  },
    { "lower",              1, 0, SQLITE_UTF8,    0, lowerFunc  },
    { "coalesce",          -1, 0, SQLITE_UTF8,    0, ifnullFunc },
    { "coalesce",           0, 0, SQLITE_UTF8,    0, 0          },
    { "coalesce",           1, 0, SQLITE_UTF8,    0, 0          },
    { "ifnull",             2, 0, SQLITE_UTF8,    1, ifnullFunc },
    { "random",            -1, 0, SQLITE_UTF8,    0, randomFunc },
    { "like",               2, 0, SQLITE_UTF8,    0, likeFunc   },
    { "like",               3, 0, SQLITE_UTF8,    0, likeFunc   },
    { "glob",               2, 0, SQLITE_UTF8,    0, globFunc   },
    { "nullif",             2, 0, SQLITE_UTF8,    1, nullifFunc },
    { "sqlite_version",     0, 0, SQLITE_UTF8,    0, versionFunc},
    { "quote",              1, 0, SQLITE_UTF8,    0, quoteFunc  },
    { "last_insert_rowid",  0, 1, SQLITE_UTF8,    0, last_insert_rowid },
    { "changes",            0, 1, SQLITE_UTF8,    0, changes    },
    { "total_changes",      0, 1, SQLITE_UTF8,    0, total_changes },
#ifdef SQLITE_SOUNDEX







<
<
<







964
965
966
967
968
969
970



971
972
973
974
975
976
977
    { "upper",              1, 0, SQLITE_UTF8,    0, upperFunc  },
    { "lower",              1, 0, SQLITE_UTF8,    0, lowerFunc  },
    { "coalesce",          -1, 0, SQLITE_UTF8,    0, ifnullFunc },
    { "coalesce",           0, 0, SQLITE_UTF8,    0, 0          },
    { "coalesce",           1, 0, SQLITE_UTF8,    0, 0          },
    { "ifnull",             2, 0, SQLITE_UTF8,    1, ifnullFunc },
    { "random",            -1, 0, SQLITE_UTF8,    0, randomFunc },



    { "nullif",             2, 0, SQLITE_UTF8,    1, nullifFunc },
    { "sqlite_version",     0, 0, SQLITE_UTF8,    0, versionFunc},
    { "quote",              1, 0, SQLITE_UTF8,    0, quoteFunc  },
    { "last_insert_rowid",  0, 1, SQLITE_UTF8,    0, last_insert_rowid },
    { "changes",            0, 1, SQLITE_UTF8,    0, changes    },
    { "total_changes",      0, 1, SQLITE_UTF8,    0, total_changes },
#ifdef SQLITE_SOUNDEX
1041
1042
1043
1044
1045
1046
1047








1048










1049











1050









1051











1052




















      if( pFunc && aAggs[i].needCollSeq ){
        pFunc->needCollSeq = 1;
      }
    }
  }
  sqlite3RegisterDateTimeFunctions(db);
#ifdef SQLITE_SSE








  {










    sqlite3SseFunctions(db);











  }









#endif











}



























>
>
>
>
>
>
>
>
|
>
>
>
>
>
>
>
>
>
>
|
>
>
>
>
>
>
>
>
>
>
>

>
>
>
>
>
>
>
>
>
|
>
>
>
>
>
>
>
>
>
>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
      if( pFunc && aAggs[i].needCollSeq ){
        pFunc->needCollSeq = 1;
      }
    }
  }
  sqlite3RegisterDateTimeFunctions(db);
#ifdef SQLITE_SSE
  sqlite3SseFunctions(db);
#endif
#ifdef SQLITE_CASE_SENSITIVE_LIKE
  sqlite3RegisterLikeFunctions(db, 1);
#else
  sqlite3RegisterLikeFunctions(db, 0);
#endif
}

/*
** Set the LIKEOPT flag on the 2-argument function with the given name.
*/
static void setLikeOptFlag(sqlite3 *db, const char *zName){
  FuncDef *pDef;
  pDef = sqlite3FindFunction(db, zName, strlen(zName), 2, SQLITE_UTF8, 0);
  if( pDef ){
    pDef->flags = SQLITE_FUNC_LIKEOPT;
  }
}

/*
** Register the built-in LIKE and GLOB functions.  The caseSensitive
** parameter determines whether or not the LIKE operator is case
** sensitive.  GLOB is always case sensitive.
*/
void sqlite3RegisterLikeFunctions(sqlite3 *db, int caseSensitive){
  struct compareInfo *pInfo;
  if( caseSensitive ){
    pInfo = (struct compareInfo*)&likeInfoAlt;
  }else{
    pInfo = (struct compareInfo*)&likeInfoNorm;
  }
  sqlite3_create_function(db, "like", 2, SQLITE_UTF8, pInfo, likeFunc, 0, 0);
  sqlite3_create_function(db, "like", 3, SQLITE_UTF8, pInfo, likeFunc, 0, 0);
  sqlite3_create_function(db, "glob", 2, SQLITE_UTF8, 
      (struct compareInfo*)&globInfo, likeFunc, 0,0);
  setLikeOptFlag(db, "glob");
  if( caseSensitive ){
    setLikeOptFlag(db, "like");
  }
}

/*
** pExpr points to an expression which implements a function.  If
** it is appropriate to apply the LIKE optimization to that function
** then set aWc[0] through aWc[2] to the wildcard characters and
** return TRUE.  If the function is not a LIKE-style function then
** return FALSE.
*/
int sqlite3IsLikeFunction(sqlite3 *db, Expr *pExpr, char *aWc){
  FuncDef *pDef;
  if( pExpr->op!=TK_FUNCTION ){
    return 0;
  }
  if( pExpr->pList->nExpr!=2 ){
    return 0;
  }
  pDef = sqlite3FindFunction(db, pExpr->token.z, pExpr->token.n, 2,
                             SQLITE_UTF8, 0);
  if( pDef==0 || (pDef->flags & SQLITE_FUNC_LIKEOPT)==0 ){
    return 0;
  }

  /* The memcpy() statement assumes that the wildcard characters are
  ** the first three statements in the compareInfo structure.  The
  ** asserts() that follow verify that assumption
  */
  memcpy(aWc, pDef->pUserData, 3);
  assert( (char*)&likeInfoAlt == (char*)&likeInfoAlt.matchAll );
  assert( &((char*)&likeInfoAlt)[1] == (char*)&likeInfoAlt.matchOne );
  assert( &((char*)&likeInfoAlt)[2] == (char*)&likeInfoAlt.matchSet );

  return 1;
}
Changes to src/main.c.
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
**
*************************************************************************
** Main file for the SQLite library.  The routines in this file
** implement the programmer interface to the library.  Routines in
** other files are for internal use by SQLite and should not be
** accessed by users of the library.
**
** $Id: main.c,v 1.297 2005/08/11 02:10:19 drh Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>

/*
** The following constant value is used by the SQLITE_BIGENDIAN and







|







10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
**
*************************************************************************
** Main file for the SQLite library.  The routines in this file
** implement the programmer interface to the library.  Routines in
** other files are for internal use by SQLite and should not be
** accessed by users of the library.
**
** $Id: main.c,v 1.298 2005/08/14 01:20:39 drh Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>

/*
** The following constant value is used by the SQLITE_BIGENDIAN and
467
468
469
470
471
472
473

474
475
476
477
478
479
480
    }else{
      sqlite3ExpirePreparedStatements(db);
    }
  }

  p = sqlite3FindFunction(db, zFunctionName, nName, nArg, enc, 1);
  if( p==0 ) return SQLITE_NOMEM;

  p->xFunc = xFunc;
  p->xStep = xStep;
  p->xFinalize = xFinal;
  p->pUserData = pUserData;
  return SQLITE_OK;
}
#ifndef SQLITE_OMIT_UTF16







>







467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
    }else{
      sqlite3ExpirePreparedStatements(db);
    }
  }

  p = sqlite3FindFunction(db, zFunctionName, nName, nArg, enc, 1);
  if( p==0 ) return SQLITE_NOMEM;
  p->flags = 0;
  p->xFunc = xFunc;
  p->xStep = xStep;
  p->xFinalize = xFinal;
  p->pUserData = pUserData;
  return SQLITE_OK;
}
#ifndef SQLITE_OMIT_UTF16
Changes to src/pragma.c.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
/*
** 2003 April 6
**
** 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.
**
*************************************************************************
** This file contains code used to implement the PRAGMA command.
**
** $Id: pragma.c,v 1.97 2005/08/13 00:56:27 drh Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>

/* Ignore this whole file if pragmas are disabled
*/













|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
/*
** 2003 April 6
**
** 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.
**
*************************************************************************
** This file contains code used to implement the PRAGMA command.
**
** $Id: pragma.c,v 1.98 2005/08/14 01:20:39 drh Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>

/* Ignore this whole file if pragmas are disabled
*/
599
600
601
602
603
604
605

606
607
608
609

610
611
612









613
614
615
616
617
618
619
    }
  }else
#endif /* !defined(SQLITE_OMIT_FOREIGN_KEY) */

#ifndef NDEBUG
  if( sqlite3StrICmp(zLeft, "parser_trace")==0 ){
    extern void sqlite3ParserTrace(FILE*, char *);

    if( getBoolean(zRight) ){
      sqlite3ParserTrace(stderr, "parser: ");
    }else{
      sqlite3ParserTrace(0, 0);

    }
  }else
#endif










#ifndef SQLITE_OMIT_INTEGRITY_CHECK
  if( sqlite3StrICmp(zLeft, "integrity_check")==0 ){
    int i, j, addr;

    /* Code that initializes the integrity check program.  Set the
    ** error count 0







>
|
|
|
|
>



>
>
>
>
>
>
>
>
>







599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
    }
  }else
#endif /* !defined(SQLITE_OMIT_FOREIGN_KEY) */

#ifndef NDEBUG
  if( sqlite3StrICmp(zLeft, "parser_trace")==0 ){
    extern void sqlite3ParserTrace(FILE*, char *);
    if( zRight ){
      if( getBoolean(zRight) ){
        sqlite3ParserTrace(stderr, "parser: ");
      }else{
        sqlite3ParserTrace(0, 0);
      }
    }
  }else
#endif

  /* Reinstall the LIKE and GLOB functions.  The variant of LIKE
  ** used will be case sensitive or not depending on the RHS.
  */
  if( sqlite3StrICmp(zLeft, "case_sensitive_like")==0 ){
    if( zRight ){
      sqlite3RegisterLikeFunctions(db, getBoolean(zRight));
    }
  }else

#ifndef SQLITE_OMIT_INTEGRITY_CHECK
  if( sqlite3StrICmp(zLeft, "integrity_check")==0 ){
    int i, j, addr;

    /* Code that initializes the integrity check program.  Set the
    ** error count 0
Changes to src/sqliteInt.h.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
/*
** 2001 September 15
**
** 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.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.401 2005/08/12 22:56:09 drh Exp $
*/
#ifndef _SQLITEINT_H_
#define _SQLITEINT_H_

/*
** These #defines should enable >2GB file support on Posix if the
** underlying operating system supports it.  If the OS lacks













|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
/*
** 2001 September 15
**
** 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.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.402 2005/08/14 01:20:39 drh Exp $
*/
#ifndef _SQLITEINT_H_
#define _SQLITEINT_H_

/*
** These #defines should enable >2GB file support on Posix if the
** underlying operating system supports it.  If the OS lacks
492
493
494
495
496
497
498
499
500
501


502
503
504
505
506
507
508
509





510
511
512
513
514
515
516
/*
** Each SQL function is defined by an instance of the following
** structure.  A pointer to this structure is stored in the sqlite.aFunc
** hash table.  When multiple functions have the same name, the hash table
** points to a linked list of these structures.
*/
struct FuncDef {
  char *zName;         /* SQL name of the function */
  int nArg;            /* Number of arguments.  -1 means unlimited */
  u8 iPrefEnc;         /* Preferred text encoding (SQLITE_UTF8, 16LE, 16BE) */


  void *pUserData;     /* User data parameter */
  FuncDef *pNext;      /* Next function with same name */
  void (*xFunc)(sqlite3_context*,int,sqlite3_value**); /* Regular function */
  void (*xStep)(sqlite3_context*,int,sqlite3_value**); /* Aggregate step */
  void (*xFinalize)(sqlite3_context*);                /* Aggregate finializer */
  u8 needCollSeq;      /* True if sqlite3GetFuncCollSeq() might be called */
};






/*
** information about each column of an SQL table is held in an instance
** of this structure.
*/
struct Column {
  char *zName;     /* Name of this column */
  Expr *pDflt;     /* Default value of this column */







<
|

>
>





|


>
>
>
>
>







492
493
494
495
496
497
498

499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
/*
** Each SQL function is defined by an instance of the following
** structure.  A pointer to this structure is stored in the sqlite.aFunc
** hash table.  When multiple functions have the same name, the hash table
** points to a linked list of these structures.
*/
struct FuncDef {

  i16 nArg;            /* Number of arguments.  -1 means unlimited */
  u8 iPrefEnc;         /* Preferred text encoding (SQLITE_UTF8, 16LE, 16BE) */
  u8 needCollSeq;      /* True if sqlite3GetFuncCollSeq() might be called */
  u8 flags;            /* Some combination of SQLITE_FUNC_* */
  void *pUserData;     /* User data parameter */
  FuncDef *pNext;      /* Next function with same name */
  void (*xFunc)(sqlite3_context*,int,sqlite3_value**); /* Regular function */
  void (*xStep)(sqlite3_context*,int,sqlite3_value**); /* Aggregate step */
  void (*xFinalize)(sqlite3_context*);                /* Aggregate finializer */
  char zName[1];       /* SQL name of the function.  MUST BE LAST */
};

/*
** Possible values for FuncDef.flags
*/
#define SQLITE_FUNC_LIKEOPT  0x01    /* Candidate for the LIKE optimization */

/*
** information about each column of an SQL table is held in an instance
** of this structure.
*/
struct Column {
  char *zName;     /* Name of this column */
  Expr *pDflt;     /* Default value of this column */
1572
1573
1574
1575
1576
1577
1578


1579
1580
1581
1582
1583
1584
CollSeq *sqlite3GetCollSeq(sqlite3*, CollSeq *, const char *, int);
char sqlite3AffinityType(const Token*);
void sqlite3Analyze(Parse*, Token*, Token*);
int sqlite3InvokeBusyHandler(BusyHandler*);
int sqlite3FindDb(sqlite3*, Token*);
void sqlite3AnalysisLoad(sqlite3*,int iDB);
void sqlite3DefaultRowEst(Index*);



#ifdef SQLITE_SSE
#include "sseInt.h"
#endif

#endif







>
>






1578
1579
1580
1581
1582
1583
1584
1585
1586
1587
1588
1589
1590
1591
1592
CollSeq *sqlite3GetCollSeq(sqlite3*, CollSeq *, const char *, int);
char sqlite3AffinityType(const Token*);
void sqlite3Analyze(Parse*, Token*, Token*);
int sqlite3InvokeBusyHandler(BusyHandler*);
int sqlite3FindDb(sqlite3*, Token*);
void sqlite3AnalysisLoad(sqlite3*,int iDB);
void sqlite3DefaultRowEst(Index*);
void sqlite3RegisterLikeFunctions(sqlite3*, int);
int sqlite3IsLikeFunction(sqlite3*,Expr*,char*);

#ifdef SQLITE_SSE
#include "sseInt.h"
#endif

#endif
Changes to src/test1.c.
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
**    May you share freely, never taking more than you give.
**
*************************************************************************
** Code for testing the printf() interface to SQLite.  This code
** is not included in the SQLite library.  It is used for automated
** testing of the SQLite library.
**
** $Id: test1.c,v 1.153 2005/08/11 02:10:19 drh Exp $
*/
#include "sqliteInt.h"
#include "tcl.h"
#include "os.h"
#include <stdlib.h>
#include <string.h>








|







9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
**    May you share freely, never taking more than you give.
**
*************************************************************************
** Code for testing the printf() interface to SQLite.  This code
** is not included in the SQLite library.  It is used for automated
** testing of the SQLite library.
**
** $Id: test1.c,v 1.154 2005/08/14 01:20:39 drh Exp $
*/
#include "sqliteInt.h"
#include "tcl.h"
#include "os.h"
#include <stdlib.h>
#include <string.h>

2792
2793
2794
2795
2796
2797
2798






2799
2800
2801
2802
2803
2804
2805
  Tcl_SetVar2(interp, "sqlite_options", "autovacuum", "1", TCL_GLOBAL_ONLY);
#endif /* SQLITE_OMIT_AUTOVACUUM */
#if !defined(SQLITE_DEFAULT_AUTOVACUUM) || SQLITE_DEFAULT_AUTOVACUUM==0
  Tcl_SetVar2(interp,"sqlite_options","default_autovacuum","0",TCL_GLOBAL_ONLY);
#else
  Tcl_SetVar2(interp,"sqlite_options","default_autovacuum","1",TCL_GLOBAL_ONLY);
#endif







#ifdef SQLITE_OMIT_BLOB_LITERAL
  Tcl_SetVar2(interp, "sqlite_options", "bloblit", "0", TCL_GLOBAL_ONLY);
#else
  Tcl_SetVar2(interp, "sqlite_options", "bloblit", "1", TCL_GLOBAL_ONLY);
#endif








>
>
>
>
>
>







2792
2793
2794
2795
2796
2797
2798
2799
2800
2801
2802
2803
2804
2805
2806
2807
2808
2809
2810
2811
  Tcl_SetVar2(interp, "sqlite_options", "autovacuum", "1", TCL_GLOBAL_ONLY);
#endif /* SQLITE_OMIT_AUTOVACUUM */
#if !defined(SQLITE_DEFAULT_AUTOVACUUM) || SQLITE_DEFAULT_AUTOVACUUM==0
  Tcl_SetVar2(interp,"sqlite_options","default_autovacuum","0",TCL_GLOBAL_ONLY);
#else
  Tcl_SetVar2(interp,"sqlite_options","default_autovacuum","1",TCL_GLOBAL_ONLY);
#endif

#ifdef SQLITE_OMIT_BETWEEN_OPTIMIZATION
  Tcl_SetVar2(interp, "sqlite_options", "between_opt", "0", TCL_GLOBAL_ONLY);
#else
  Tcl_SetVar2(interp, "sqlite_options", "between_opt", "1", TCL_GLOBAL_ONLY);
#endif

#ifdef SQLITE_OMIT_BLOB_LITERAL
  Tcl_SetVar2(interp, "sqlite_options", "bloblit", "0", TCL_GLOBAL_ONLY);
#else
  Tcl_SetVar2(interp, "sqlite_options", "bloblit", "1", TCL_GLOBAL_ONLY);
#endif

2864
2865
2866
2867
2868
2869
2870






2871
2872
2873
2874
2875
2876






2877
2878
2879
2880
2881
2882
2883
#endif

#ifdef SQLITE_OMIT_INTEGRITY_CHECK
  Tcl_SetVar2(interp, "sqlite_options", "integrityck", "0", TCL_GLOBAL_ONLY);
#else
  Tcl_SetVar2(interp, "sqlite_options", "integrityck", "1", TCL_GLOBAL_ONLY);
#endif







#ifdef SQLITE_OMIT_MEMORYDB
  Tcl_SetVar2(interp, "sqlite_options", "memorydb", "0", TCL_GLOBAL_ONLY);
#else
  Tcl_SetVar2(interp, "sqlite_options", "memorydb", "1", TCL_GLOBAL_ONLY);
#endif







#ifdef SQLITE_OMIT_PAGER_PRAGMAS
  Tcl_SetVar2(interp, "sqlite_options", "pager_pragmas", "0", TCL_GLOBAL_ONLY);
#else
  Tcl_SetVar2(interp, "sqlite_options", "pager_pragmas", "1", TCL_GLOBAL_ONLY);
#endif








>
>
>
>
>
>






>
>
>
>
>
>







2870
2871
2872
2873
2874
2875
2876
2877
2878
2879
2880
2881
2882
2883
2884
2885
2886
2887
2888
2889
2890
2891
2892
2893
2894
2895
2896
2897
2898
2899
2900
2901
#endif

#ifdef SQLITE_OMIT_INTEGRITY_CHECK
  Tcl_SetVar2(interp, "sqlite_options", "integrityck", "0", TCL_GLOBAL_ONLY);
#else
  Tcl_SetVar2(interp, "sqlite_options", "integrityck", "1", TCL_GLOBAL_ONLY);
#endif

#ifdef SQLITE_OMIT_LIKE_OPTIMIZATION
  Tcl_SetVar2(interp, "sqlite_options", "like_opt", "0", TCL_GLOBAL_ONLY);
#else
  Tcl_SetVar2(interp, "sqlite_options", "like_opt", "1", TCL_GLOBAL_ONLY);
#endif

#ifdef SQLITE_OMIT_MEMORYDB
  Tcl_SetVar2(interp, "sqlite_options", "memorydb", "0", TCL_GLOBAL_ONLY);
#else
  Tcl_SetVar2(interp, "sqlite_options", "memorydb", "1", TCL_GLOBAL_ONLY);
#endif

#ifdef SQLITE_OMIT_OR_OPTIMIZATION
  Tcl_SetVar2(interp, "sqlite_options", "or_opt", "0", TCL_GLOBAL_ONLY);
#else
  Tcl_SetVar2(interp, "sqlite_options", "or_opt", "1", TCL_GLOBAL_ONLY);
#endif

#ifdef SQLITE_OMIT_PAGER_PRAGMAS
  Tcl_SetVar2(interp, "sqlite_options", "pager_pragmas", "0", TCL_GLOBAL_ONLY);
#else
  Tcl_SetVar2(interp, "sqlite_options", "pager_pragmas", "1", TCL_GLOBAL_ONLY);
#endif

3090
3091
3092
3093
3094
3095
3096

3097
3098
3099
3100
3101
3102
3103
3104
3105
3106
3107
3108
3109


3110
3111
3112
3113
3114
3115
3116
  extern int sqlite3_os_trace;
  extern int sqlite3_where_trace;
  extern int sqlite3_sync_count, sqlite3_fullsync_count;
  extern int sqlite3_opentemp_count;
  extern int sqlite3_memUsed;
  extern int sqlite3_memMax;
  extern char sqlite3_query_plan[];

  static char *query_plan = sqlite3_query_plan;

  for(i=0; i<sizeof(aCmd)/sizeof(aCmd[0]); i++){
    Tcl_CreateCommand(interp, aCmd[i].zName, aCmd[i].xProc, 0, 0);
  }
  for(i=0; i<sizeof(aObjCmd)/sizeof(aObjCmd[0]); i++){
    Tcl_CreateObjCommand(interp, aObjCmd[i].zName, 
        aObjCmd[i].xProc, aObjCmd[i].clientData, 0);
  }
  Tcl_LinkVar(interp, "sqlite_search_count", 
      (char*)&sqlite3_search_count, TCL_LINK_INT);
  Tcl_LinkVar(interp, "sqlite_sort_count", 
      (char*)&sqlite3_sort_count, TCL_LINK_INT);


  Tcl_LinkVar(interp, "sqlite_interrupt_count", 
      (char*)&sqlite3_interrupt_count, TCL_LINK_INT);
  Tcl_LinkVar(interp, "sqlite_open_file_count", 
      (char*)&sqlite3_open_file_count, TCL_LINK_INT);
  Tcl_LinkVar(interp, "sqlite_current_time", 
      (char*)&sqlite3_current_time, TCL_LINK_INT);
  Tcl_LinkVar(interp, "sqlite_os_trace",







>













>
>







3108
3109
3110
3111
3112
3113
3114
3115
3116
3117
3118
3119
3120
3121
3122
3123
3124
3125
3126
3127
3128
3129
3130
3131
3132
3133
3134
3135
3136
3137
  extern int sqlite3_os_trace;
  extern int sqlite3_where_trace;
  extern int sqlite3_sync_count, sqlite3_fullsync_count;
  extern int sqlite3_opentemp_count;
  extern int sqlite3_memUsed;
  extern int sqlite3_memMax;
  extern char sqlite3_query_plan[];
  extern int sqlite3_like_count;
  static char *query_plan = sqlite3_query_plan;

  for(i=0; i<sizeof(aCmd)/sizeof(aCmd[0]); i++){
    Tcl_CreateCommand(interp, aCmd[i].zName, aCmd[i].xProc, 0, 0);
  }
  for(i=0; i<sizeof(aObjCmd)/sizeof(aObjCmd[0]); i++){
    Tcl_CreateObjCommand(interp, aObjCmd[i].zName, 
        aObjCmd[i].xProc, aObjCmd[i].clientData, 0);
  }
  Tcl_LinkVar(interp, "sqlite_search_count", 
      (char*)&sqlite3_search_count, TCL_LINK_INT);
  Tcl_LinkVar(interp, "sqlite_sort_count", 
      (char*)&sqlite3_sort_count, TCL_LINK_INT);
  Tcl_LinkVar(interp, "sqlite_like_count", 
      (char*)&sqlite3_like_count, TCL_LINK_INT);
  Tcl_LinkVar(interp, "sqlite_interrupt_count", 
      (char*)&sqlite3_interrupt_count, TCL_LINK_INT);
  Tcl_LinkVar(interp, "sqlite_open_file_count", 
      (char*)&sqlite3_open_file_count, TCL_LINK_INT);
  Tcl_LinkVar(interp, "sqlite_current_time", 
      (char*)&sqlite3_current_time, TCL_LINK_INT);
  Tcl_LinkVar(interp, "sqlite_os_trace",
Changes to src/vdbeaux.c.
105
106
107
108
109
110
111

112
113
114
115
116
117
118
  }
  pOp = &p->aOp[i];
  pOp->opcode = op;
  pOp->p1 = p1;
  pOp->p2 = p2;
  pOp->p3 = 0;
  pOp->p3type = P3_NOTUSED;

#ifdef SQLITE_DEBUG
  if( sqlite3_vdbe_addop_trace ) sqlite3VdbePrintOp(0, i, &p->aOp[i]);
#endif
  return i;
}

/*







>







105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
  }
  pOp = &p->aOp[i];
  pOp->opcode = op;
  pOp->p1 = p1;
  pOp->p2 = p2;
  pOp->p3 = 0;
  pOp->p3type = P3_NOTUSED;
  p->expired = 0;
#ifdef SQLITE_DEBUG
  if( sqlite3_vdbe_addop_trace ) sqlite3VdbePrintOp(0, i, &p->aOp[i]);
#endif
  return i;
}

/*
Changes to src/where.c.
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
** This module contains C code that generates VDBE code used to process
** the WHERE clause of SQL statements.  This module is reponsible for
** generating the code that loops through a table looking for applicable
** rows.  Indices are selected and used to speed the search when doing
** so is applicable.  Because this module is responsible for selecting
** indices, you might also think of this module as the "query optimizer".
**
** $Id: where.c,v 1.161 2005/08/13 16:13:05 drh Exp $
*/
#include "sqliteInt.h"

/*
** The number of bits in a Bitmask.  "BMS" means "BitMask Size".
*/
#define BMS  (sizeof(Bitmask)*8)







|







12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
** This module contains C code that generates VDBE code used to process
** the WHERE clause of SQL statements.  This module is reponsible for
** generating the code that loops through a table looking for applicable
** rows.  Indices are selected and used to speed the search when doing
** so is applicable.  Because this module is responsible for selecting
** indices, you might also think of this module as the "query optimizer".
**
** $Id: where.c,v 1.162 2005/08/14 01:20:39 drh Exp $
*/
#include "sqliteInt.h"

/*
** The number of bits in a Bitmask.  "BMS" means "BitMask Size".
*/
#define BMS  (sizeof(Bitmask)*8)
463
464
465
466
467
468
469

470
471
472
473
474
475

476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
** can be optimized using inequality constraints.  Return TRUE if it is
** so and false if not.
**
** In order for the operator to be optimizible, the RHS must be a string
** literal that does not begin with a wildcard.  
*/
static int isLikeOrGlob(

  Expr *pExpr,      /* Test this expression */
  int *pnPattern,   /* Number of non-wildcard prefix characters */
  int *pisComplete  /* True if the only wildcard is % in the last character */
){
  const char *z;
  Expr *pRight, *pLeft;

  int c, cnt;
  char wc1, wc2, wc3;
  if( pExpr->op!=TK_FUNCTION ){
    return 0;
  }
  if( pExpr->pList->nExpr!=2 ){
    return 0;
  }
  if( pExpr->token.n!=4 ){
    return 0;
  }
  z = pExpr->token.z;
  if( sqlite3StrNICmp(z, "glob", 4)==0 ){
    wc1 = '*';
    wc2 = '?';
    wc3 = '[';
  }
#ifdef SQLITE_CASE_SENSITIVE_LIKE
  else if( sqlite3StrNICmp(z, "like", 4)==0 ){
    wc1 = '%';
    wc2 = '_';
    wc3 = '_';
  }
#endif
  else{
    return 0;
  }
  pRight = pExpr->pList->a[0].pExpr;
  if( pRight->op!=TK_STRING ){
    return 0;
  }
  pLeft = pExpr->pList->a[1].pExpr;
  if( pLeft->op!=TK_COLUMN ){
    return 0;
  }
  sqlite3DequoteExpr(pRight);
  z = pRight->token.z;
  for(cnt=0; (c=z[cnt])!=0 && c!=wc1 && c!=wc2 && c!=wc3; cnt++){}
  if( cnt==0 || 255==(u8)z[cnt] ){
    return 0;
  }
  *pisComplete = z[cnt]==wc1 && z[cnt+1]==0;
  *pnPattern = cnt;
  return 1;
}
#endif /* SQLITE_OMIT_LIKE_OPTIMIZATION */

/*
** The input to this routine is an WhereTerm structure with only the







>






>

|
|


|
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
|



|





|



|







463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483





















484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
** can be optimized using inequality constraints.  Return TRUE if it is
** so and false if not.
**
** In order for the operator to be optimizible, the RHS must be a string
** literal that does not begin with a wildcard.  
*/
static int isLikeOrGlob(
  sqlite3 *db,      /* The database */
  Expr *pExpr,      /* Test this expression */
  int *pnPattern,   /* Number of non-wildcard prefix characters */
  int *pisComplete  /* True if the only wildcard is % in the last character */
){
  const char *z;
  Expr *pRight, *pLeft;
  ExprList *pList;
  int c, cnt;
  char wc[3];
  if( !sqlite3IsLikeFunction(db, pExpr, wc) ){
    return 0;
  }
  pList = pExpr->pList;





















  pRight = pList->a[0].pExpr;
  if( pRight->op!=TK_STRING ){
    return 0;
  }
  pLeft = pList->a[1].pExpr;
  if( pLeft->op!=TK_COLUMN ){
    return 0;
  }
  sqlite3DequoteExpr(pRight);
  z = pRight->token.z;
  for(cnt=0; (c=z[cnt])!=0 && c!=wc[0] && c!=wc[1] && c!=wc[2]; cnt++){}
  if( cnt==0 || 255==(u8)z[cnt] ){
    return 0;
  }
  *pisComplete = z[cnt]==wc[0] && z[cnt+1]==0;
  *pnPattern = cnt;
  return 1;
}
#endif /* SQLITE_OMIT_LIKE_OPTIMIZATION */

/*
** The input to this routine is an WhereTerm structure with only the
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
  }
#endif /* SQLITE_OMIT_OR_OPTIMIZATION */

#ifndef SQLITE_OMIT_LIKE_OPTIMIZATION
  /* Add constraints to reduce the search space on a LIKE or GLOB
  ** operator.
  */
  if( isLikeOrGlob(pExpr, &nPattern, &isComplete) ){
    Expr *pLeft, *pRight;
    Expr *pStr1, *pStr2;
    Expr *pNewExpr1, *pNewExpr2;
    WhereTerm *pNewTerm1, *pNewTerm2;
    pLeft = pExpr->pList->a[1].pExpr;
    pRight = pExpr->pList->a[0].pExpr;
    pStr1 = sqlite3Expr(TK_STRING, 0, 0, 0);







|







648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
  }
#endif /* SQLITE_OMIT_OR_OPTIMIZATION */

#ifndef SQLITE_OMIT_LIKE_OPTIMIZATION
  /* Add constraints to reduce the search space on a LIKE or GLOB
  ** operator.
  */
  if( isLikeOrGlob(pTerm->pWC->pParse->db, pExpr, &nPattern, &isComplete) ){
    Expr *pLeft, *pRight;
    Expr *pStr1, *pStr2;
    Expr *pNewExpr1, *pNewExpr2;
    WhereTerm *pNewTerm1, *pNewTerm2;
    pLeft = pExpr->pList->a[1].pExpr;
    pRight = pExpr->pList->a[0].pExpr;
    pStr1 = sqlite3Expr(TK_STRING, 0, 0, 0);
Added test/like.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
# 2005 August 13
#
# 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.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the LIKE and GLOB operators and
# in particular the optimizations that occur to help those operators
# run faster.
#
# $Id: like.test,v 1.1 2005/08/14 01:20:40 drh Exp $

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

# Create some sample data to work with.
#
do_test like-1.0 {
  execsql {
    CREATE TABLE t1(x TEXT);
  }
  foreach str {
    a
    ab
    abc
    abcd

    acd
    abd
    bc
    bcd

    xyz
    ABC
    CDE
    {ABC abc xyz}
  } {
    db eval {INSERT INTO t1 VALUES($str)}
  }
  execsql {
    SELECT count(*) FROM t1;
  }
} {12}

# Test that both case sensitive and insensitive version of LIKE work.
#
do_test like-1.1 {
  execsql {
    SELECT x FROM t1 WHERE x LIKE 'abc' ORDER BY 1;
  }
} {ABC abc}
do_test like-1.2 {
  execsql {
    SELECT x FROM t1 WHERE x GLOB 'abc' ORDER BY 1;
  }
} {abc}
do_test like-1.3 {
  execsql {
    SELECT x FROM t1 WHERE x LIKE 'ABC' ORDER BY 1;
  }
} {ABC abc}
do_test like-1.4 {
  execsql {
    SELECT x FROM t1 WHERE x LIKE 'aBc' ORDER BY 1;
  }
} {ABC abc}
do_test like-1.5 {
  execsql {
    PRAGMA case_sensitive_like=on;
    SELECT x FROM t1 WHERE x LIKE 'abc' ORDER BY 1;
  }
} {abc}
do_test like-1.6 {
  execsql {
    SELECT x FROM t1 WHERE x GLOB 'abc' ORDER BY 1;
  }
} {abc}
do_test like-1.7 {
  execsql {
    SELECT x FROM t1 WHERE x LIKE 'ABC' ORDER BY 1;
  }
} {ABC}
do_test like-1.8 {
  execsql {
    SELECT x FROM t1 WHERE x LIKE 'aBc' ORDER BY 1;
  }
} {}
do_test like-1.9 {
  execsql {
    PRAGMA case_sensitive_like=off;
    SELECT x FROM t1 WHERE x LIKE 'abc' ORDER BY 1;
  }
} {ABC abc}

# Tests of the REGEXP operator
#
do_test like-2.1 {
  proc test_regexp {a b} {
    return [regexp $a $b]
  }
  db function regexp test_regexp
  execsql {
    SELECT x FROM t1 WHERE x REGEXP 'abc' ORDER BY 1;
  }
} {{ABC abc xyz} abc abcd}
do_test like-2.2 {
  execsql {
    SELECT x FROM t1 WHERE x REGEXP '^abc' ORDER BY 1;
  }
} {abc abcd}

# For the remaining tests, we need to have the like optimizations
# enabled.
#
ifcapable !like_opt {
  finish_test
  return
} 

# This procedure executes the SQL.  Then it appends to the result the
# "sort" or "nosort" keyword (as in the cksort procedure above) then
# it appends the ::sqlite_query_plan variable.
#
proc queryplan {sql} {
  set ::sqlite_sort_count 0
  set data [execsql $sql]
  if {$::sqlite_sort_count} {set x sort} {set x nosort}
  lappend data $x
  return [concat $data $::sqlite_query_plan]
}

# Perform tests on the like optimization.
#
# With no index on t1.x and with case sensitivity turned off, no optimization
# is performed.
#
do_test like-3.1 {
  set sqlite_like_count 0
  queryplan {
    SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1;
  }
} {ABC {ABC abc xyz} abc abcd sort t1 {}}
do_test like-3.2 {
  set sqlite_like_count
} {12}

# With an index on t1.x and case sensitivity on, optimize completely.
#
do_test like-3.3 {
  set sqlite_like_count 0
  execsql {
    PRAGMA case_sensitive_like=on;
    CREATE INDEX i1 ON t1(x);
  }
  queryplan {
    SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1;
  }
} {abc abcd nosort {} i1}
do_test like-3.4 {
  set sqlite_like_count
} 0

# Partial optimization when the pattern does not end in '%'
#
do_test like-3.5 {
  set sqlite_like_count 0
  queryplan {
    SELECT x FROM t1 WHERE x LIKE 'a_c' ORDER BY 1;
  }
} {abc nosort {} i1}
do_test like-3.6 {
  set sqlite_like_count
} 6
do_test like-3.7 {
  set sqlite_like_count 0
  queryplan {
    SELECT x FROM t1 WHERE x LIKE 'ab%d' ORDER BY 1;
  }
} {abcd abd nosort {} i1}
do_test like-3.8 {
  set sqlite_like_count
} 4
do_test like-3.9 {
  set sqlite_like_count 0
  queryplan {
    SELECT x FROM t1 WHERE x LIKE 'a_c%' ORDER BY 1;
  }
} {abc abcd nosort {} i1}
do_test like-3.10 {
  set sqlite_like_count
} 6

# No optimization when the pattern begins with a wildcard.
# Note that the index is still used but only for sorting.
#
do_test like-3.11 {
  set sqlite_like_count 0
  queryplan {
    SELECT x FROM t1 WHERE x LIKE '%bcd' ORDER BY 1;
  }
} {abcd bcd nosort {} i1}
do_test like-3.12 {
  set sqlite_like_count
} 12

# No optimization for case insensitive LIKE
#
do_test like-3.13 {
  set sqlite_like_count 0
  queryplan {
    PRAGMA case_sensitive_like=off;
    SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1;
  }
} {ABC {ABC abc xyz} abc abcd nosort {} i1}
do_test like-3.14 {
  set sqlite_like_count
} 12

# No optimization without an index.
#
do_test like-3.15 {
  set sqlite_like_count 0
  queryplan {
    PRAGMA case_sensitive_like=on;
    DROP INDEX i1;
    SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1;
  }
} {abc abcd sort t1 {}}
do_test like-3.16 {
  set sqlite_like_count
} 12

# No GLOB optimization without an index.
#
do_test like-3.17 {
  set sqlite_like_count 0
  queryplan {
    SELECT x FROM t1 WHERE x GLOB 'abc*' ORDER BY 1;
  }
} {abc abcd sort t1 {}}
do_test like-3.18 {
  set sqlite_like_count
} 12

# GLOB is optimized regardless of the case_sensitive_like setting.
#
do_test like-3.19 {
  set sqlite_like_count 0
  queryplan {
    CREATE INDEX i1 ON t1(x);
    SELECT x FROM t1 WHERE x GLOB 'abc*' ORDER BY 1;
  }
} {abc abcd nosort {} i1}
do_test like-3.20 {
  set sqlite_like_count
} 0
do_test like-3.21 {
  set sqlite_like_count 0
  queryplan {
    PRAGMA case_sensitive_like=on;
    SELECT x FROM t1 WHERE x GLOB 'abc*' ORDER BY 1;
  }
} {abc abcd nosort {} i1}
do_test like-3.22 {
  set sqlite_like_count
} 0
do_test like-3.23 {
  set sqlite_like_count 0
  queryplan {
    PRAGMA case_sensitive_like=off;
    SELECT x FROM t1 WHERE x GLOB 'a[bc]d' ORDER BY 1;
  }
} {abd acd nosort {} i1}
do_test like-3.24 {
  set sqlite_like_count
} 6

finish_test