SQLite

** 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.87 2004/11/14 21:56:30 drh Exp $
** $Id: func.c,v 1.88 2004/11/17 16:41:30 danielk1977 Exp $
*/
#include <ctype.h>
#include <math.h>
#include <stdlib.h>
#include <assert.h>
#include "sqliteInt.h"
#include "vdbeInt.h"

**
** This routine is usually quick, but can be N**2 in the worst case.
**
** Hints: to match '*' or '?', put them in "[]".  Like this:
**
**         abc[*]xyz        Matches "abc*xyz" only
*/
int patternCompare(
static int patternCompare(
  const u8 *zPattern,              /* The glob pattern */
  const u8 *zString,               /* The string to compare against the glob */
  const struct compareInfo *pInfo  /* Information about how to do the compare */
  const struct compareInfo *pInfo, /* Information about how to do the compare */
  const int esc                    /* The escape character */
){
  register int c;
  int invert;
  int seen;
  int c2;
  u8 matchOne = pInfo->matchOne;
  u8 matchAll = pInfo->matchAll;
  u8 matchSet = pInfo->matchSet;
  u8 noCase = pInfo->noCase; 
  int prevEscape = 0;     /* True if the previous character was 'escape' */

  while( (c = *zPattern)!=0 ){
    if( c==matchAll ){
    if( !prevEscape && c==matchAll ){
      while( (c=zPattern[1]) == matchAll || c == matchOne ){
        if( c==matchOne ){
          if( *zString==0 ) return 0;
          sqliteNextChar(zString);
        }
        zPattern++;
      }
      if( c && sqlite3ReadUtf8(&zPattern[1])==esc ){
        u8 const *zTemp = &zPattern[1];
        sqliteNextChar(zTemp);
        c = *zTemp;
      }
      if( c==0 ) return 1;
      if( c==matchSet ){
        assert( esc==0 );   /* This is GLOB, not LIKE */
        while( *zString && patternCompare(&zPattern[1],zString,pInfo)==0 ){
        while( *zString && patternCompare(&zPattern[1],zString,pInfo,esc)==0 ){
          sqliteNextChar(zString);
        }
        return *zString!=0;
      }else{
        while( (c2 = *zString)!=0 ){
          if( noCase ){
            c2 = sqlite3UpperToLower[c2];
            c = sqlite3UpperToLower[c];
            while( c2 != 0 && c2 != c ){ c2 = sqlite3UpperToLower[*++zString]; }
          }else{
            while( c2 != 0 && c2 != c ){ c2 = *++zString; }
          }
          if( c2==0 ) return 0;
          if( patternCompare(&zPattern[1],zString,pInfo) ) return 1;
          if( patternCompare(&zPattern[1],zString,pInfo,esc) ) return 1;
          sqliteNextChar(zString);
        }
        return 0;
      }
    }else if( c==matchOne ){
    }else if( !prevEscape && c==matchOne ){
      if( *zString==0 ) return 0;
      sqliteNextChar(zString);
      zPattern++;
    }else if( c==matchSet ){
      int prior_c = 0;
      assert( esc==0 );    /* This only occurs for GLOB, not LIKE */
      seen = 0;
      invert = 0;
      c = sqliteCharVal(zString);
      if( c==0 ) return 0;
      c2 = *++zPattern;
      if( c2=='^' ){ invert = 1; c2 = *++zPattern; }
      if( c2==']' ){

          prior_c = c2;
        }
        sqliteNextChar(zPattern);
      }
      if( c2==0 || (seen ^ invert)==0 ) return 0;
      sqliteNextChar(zString);
      zPattern++;
    }else if( !prevEscape && sqlite3ReadUtf8(zPattern)==esc){
      prevEscape = 1;
      sqliteNextChar(zPattern);
    }else{
      if( noCase ){
        if( sqlite3UpperToLower[c] != sqlite3UpperToLower[*zString] ) return 0;
      }else{
        if( c != *zString ) return 0;
      }
      zPattern++;
      zString++;
      prevEscape = 0;
    }
  }
  return *zString==0;
}


/*

static void likeFunc(
  sqlite3_context *context, 
  int argc, 
  sqlite3_value **argv
){
  const unsigned char *zA = sqlite3_value_text(argv[0]);
  const unsigned char *zB = sqlite3_value_text(argv[1]);
  int escape = 0;
  if( argc==3 ){
    /* The escape character string must consist of a single UTF-8 character.
    ** Otherwise, return an error.
    */
    const unsigned char *zEsc = sqlite3_value_text(argv[2]);
    if( sqlite3utf8CharLen(zEsc, -1)!=1 ){
      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));
    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(A,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));
    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.

    { "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 },

**
*************************************************************************
** This file contains SQLite's grammar for SQL.  Process this file
** using the lemon parser generator to generate C code that runs
** the parser.  Lemon will also generate a header file containing
** numeric codes for all of the tokens.
**
** @(#) $Id: parse.y,v 1.156 2004/11/13 15:59:15 drh Exp $
** @(#) $Id: parse.y,v 1.157 2004/11/17 16:41:30 danielk1977 Exp $
*/
%token_prefix TK_
%token_type {Token}
%default_type {Token}
%extra_argument {Parse *pParse}
%syntax_error {
  if( pParse->zErrMsg==0 ){

// constraint.
//
%left OR.
%left AND.
%right NOT.
%left IS LIKE GLOB BETWEEN IN ISNULL NOTNULL NE EQ.
%left GT LE LT GE.
%right ESCAPE.
%left BITAND BITOR LSHIFT RSHIFT.
%left PLUS MINUS.
%left STAR SLASH REM.
%left CONCAT.
%right UMINUS UPLUS BITNOT.

// And "ids" is an identifer-or-string.

expr(A) ::= expr(X) REM(OP) expr(Y).    {A = sqlite3Expr(@OP, X, Y, 0);}
expr(A) ::= expr(X) CONCAT(OP) expr(Y). {A = sqlite3Expr(@OP, X, Y, 0);}
%type likeop {struct LikeOp}
likeop(A) ::= LIKE.     {A.opcode = TK_LIKE; A.not = 0;}
likeop(A) ::= GLOB.     {A.opcode = TK_GLOB; A.not = 0;}
likeop(A) ::= NOT LIKE. {A.opcode = TK_LIKE; A.not = 1;}
likeop(A) ::= NOT GLOB. {A.opcode = TK_GLOB; A.not = 1;}
%type escape {Expr*}
escape(X) ::= ESCAPE expr(A). [ESCAPE] {X = A;}
escape(X) ::= .               [ESCAPE] {X = 0;}
expr(A) ::= expr(X) likeop(OP) expr(Y).  [LIKE]  {
expr(A) ::= expr(X) likeop(OP) expr(Y) escape(E).  [LIKE]  {
  ExprList *pList = sqlite3ExprListAppend(0, Y, 0);
  pList = sqlite3ExprListAppend(pList, X, 0);
  if( E ){
    pList = sqlite3ExprListAppend(pList, E, 0);
  }
  A = sqlite3ExprFunction(pList, 0);
  if( A ) A->op = OP.opcode;
  if( OP.not ) A = sqlite3Expr(TK_NOT, A, 0, 0);
  sqlite3ExprSpan(A, &X->span, &Y->span);
}

expr(A) ::= expr(X) ISNULL(E). {
  A = sqlite3Expr(TK_ISNULL, X, 0, 0);
  sqlite3ExprSpan(A,&X->span,&E);
}
expr(A) ::= expr(X) IS NULL(E). {
  A = sqlite3Expr(TK_ISNULL, X, 0, 0);
  sqlite3ExprSpan(A,&X->span,&E);

# 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.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing expressions.
#
# $Id: expr.test,v 1.39 2004/11/15 01:40:48 drh Exp $
# $Id: expr.test,v 1.40 2004/11/17 16:41:29 danielk1977 Exp $

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

# Create a table to work with.
#
execsql {CREATE TABLE test1(i1 int, i2 int, r1 real, r2 real, t1 text, t2 text)}

}

test_expr expr-5.54 {t1='abc', t2=NULL} {t1 LIKE t2} {{}}
test_expr expr-5.55 {t1='abc', t2=NULL} {t1 NOT LIKE t2} {{}}
test_expr expr-5.56 {t1='abc', t2=NULL} {t2 LIKE t1} {{}}
test_expr expr-5.57 {t1='abc', t2=NULL} {t2 NOT LIKE t1} {{}}

# LIKE expressions that use ESCAPE characters.
test_expr expr-5.58 {t1='abc', t2='A_C'}   {t1 LIKE t2 ESCAPE '7'} 1
test_expr expr-5.59 {t1='a_c', t2='A7_C'}  {t1 LIKE t2 ESCAPE '7'} 1
test_expr expr-5.60 {t1='abc', t2='A7_C'}  {t1 LIKE t2 ESCAPE '7'} 0
test_expr expr-5.61 {t1='a7Xc', t2='A7_C'} {t1 LIKE t2 ESCAPE '7'} 0
test_expr expr-5.62 {t1='abcde', t2='A%E'} {t1 LIKE t2 ESCAPE '7'} 1
test_expr expr-5.63 {t1='abcde', t2='A7%E'} {t1 LIKE t2 ESCAPE '7'} 0
test_expr expr-5.64 {t1='a7cde', t2='A7%E'} {t1 LIKE t2 ESCAPE '7'} 0
test_expr expr-5.65 {t1='a7cde', t2='A77%E'} {t1 LIKE t2 ESCAPE '7'} 1
test_expr expr-5.66 {t1='abc7', t2='A%77'} {t1 LIKE t2 ESCAPE '7'} 1
test_expr expr-5.67 {t1='abc_', t2='A%7_'} {t1 LIKE t2 ESCAPE '7'} 1
test_expr expr-5.68 {t1='abc7', t2='A%7_'} {t1 LIKE t2 ESCAPE '7'} 0

# These are the same test as the block above, but using a multi-byte 
# character as the escape character.
if {"\u1234"!="u1234"} {
  test_expr expr-5.69 "t1='abc', t2='A_C'" \
      "t1 LIKE t2 ESCAPE '\u1234'" 1
  test_expr expr-5.70 "t1='a_c', t2='A\u1234_C'" \
      "t1 LIKE t2 ESCAPE '\u1234'" 1
  test_expr expr-5.71 "t1='abc', t2='A\u1234_C'" \
       "t1 LIKE t2 ESCAPE '\u1234'" 0
  test_expr expr-5.72 "t1='a\u1234Xc', t2='A\u1234_C'" \
      "t1 LIKE t2 ESCAPE '\u1234'" 0
  test_expr expr-5.73 "t1='abcde', t2='A%E'" \
      "t1 LIKE t2 ESCAPE '\u1234'" 1
  test_expr expr-5.74 "t1='abcde', t2='A\u1234%E'" \
      "t1 LIKE t2 ESCAPE '\u1234'" 0
  test_expr expr-5.75 "t1='a\u1234cde', t2='A\u1234%E'" \
      "t1 LIKE t2 ESCAPE '\u1234'" 0
  test_expr expr-5.76 "t1='a\u1234cde', t2='A\u1234\u1234%E'" \
      "t1 LIKE t2 ESCAPE '\u1234'" 1
  test_expr expr-5.77 "t1='abc\u1234', t2='A%\u1234\u1234'" \
      "t1 LIKE t2 ESCAPE '\u1234'" 1
  test_expr expr-5.78 "t1='abc_', t2='A%\u1234_'" \
      "t1 LIKE t2 ESCAPE '\u1234'" 1
  test_expr expr-5.79 "t1='abc\u1234', t2='A%\u1234_'" \
      "t1 LIKE t2 ESCAPE '\u1234'" 0
}

test_expr expr-6.1 {t1='abc', t2='xyz'} {t1 GLOB t2} 0
test_expr expr-6.2 {t1='abc', t2='ABC'} {t1 GLOB t2} 0
test_expr expr-6.3 {t1='abc', t2='A?C'} {t1 GLOB t2} 0
test_expr expr-6.4 {t1='abc', t2='a?c'} {t1 GLOB t2} 1
test_expr expr-6.5 {t1='abc', t2='abc?'} {t1 GLOB t2} 0
test_expr expr-6.6 {t1='abc', t2='A*C'} {t1 GLOB t2} 0

# 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.
#
#***********************************************************************
# This file implements regression tests for SQLite library. The focus
# of this file is modifications made to tables while SELECT queries are
# active on the tables. Using this capability in a program is tricky
# because results can be difficult to predict, but can be useful.
#
# $Id: lock4.test,v 1.1 2004/11/17 16:41:29 danielk1977 Exp $
#

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

do_test lock4-1.0 {
  execsql {
    CREATE TABLE t1(a, b);
    INSERT INTO t1 VALUES(1, 2);
  }
} {}

# Check that we can INSERT into a table while doing a SELECT on it.
do_test lock4-1.1 {
  db eval {SELECT * FROM t1} {
    if {$a<5} {
      execsql "INSERT INTO t1 VALUES($a+1, ($a+1)*2)"
    }
  }
} {}
do_test lock4-1.2 {
  execsql {
    SELECT * FROM t1
  }
} {1 2 2 4 3 6 4 8 5 10}

# Check that we can UPDATE a table while doing a SELECT on it.
do_test lock4-1.3 {
  db eval {SELECT * FROM t1 WHERE (a%2)=0} {
    execsql "UPDATE t1 SET b = b/2 WHERE a = $a"
  }
} {}
do_test lock4-1.4 {
  execsql {
    SELECT * FROM t1
  }
} {1 2 2 2 3 6 4 4 5 10}

# Check that we can DELETE from a table while doing a SELECT on it.
do_test lock4-1.5 {
  db eval {SELECT * FROM t1 WHERE (a%2)=0} {
    execsql "DELETE FROM t1 WHERE a = $a"
  }
} {}
do_test lock4-1.6 {
  execsql {
    SELECT * FROM t1
  }
} {1 2 3 6 5 10}

# Check what happens when a row is deleted while a cursor is still using
# the row (because of a SELECT that does a join).
do_test lock4-2.0 {
  execsql {
    CREATE TABLE t2(c);
    INSERT INTO t2 VALUES('one');
    INSERT INTO t2 VALUES('two');
  }
} {}
do_test lock4-2.1 {
  set res [list]
  db eval {SELECT a, b, c FROM t1, t2} {
    lappend res $a $b $c
    if {0==[string compare $c one]} {
      execsql "DELETE FROM t1 WHERE a = $a"
    }
  }
  set res
} {1 2 one 1 2 two 3 6 one 3 6 two 5 10 one 5 10 two}
do_test lock4-2.2 {
  execsql {
    SELECT * FROM t1;
  }
} {}

# do_test lock4-2.3 {
#   execsql "
#     INSERT INTO t1 VALUES('[string repeat 1 750]', '[string repeat 2 750]')
#   "
# } {}
# do_test lock4-2.4 {
#   set res [list]
#   db eval {SELECT a, b, c FROM t1, t2} {
#     lappend res $a $b $c
#     if {0==[string compare $c one]} {
#       execsql "DELETE FROM t1 WHERE a = '$a'"
#     }
#   }
#   set res
# } [list \
#     [string repeat 1 750] [string repeat 2 750] one \
#     [string repeat 1 750] [string repeat 2 750] two
#   ]
# do_test lock4-2.5 {
#   execsql {
#     SELECT * FROM t1;
#   }
# } {}

finish_test

  { "DESC",             "TK_DESC",         ALWAYS                 },
  { "DETACH",           "TK_DETACH",       ATTACH                 },
  { "DISTINCT",         "TK_DISTINCT",     ALWAYS                 },
  { "DROP",             "TK_DROP",         ALWAYS                 },
  { "END",              "TK_END",          ALWAYS                 },
  { "EACH",             "TK_EACH",         TRIGGER                },
  { "ELSE",             "TK_ELSE",         ALWAYS                 },
  { "ESCAPE",           "TK_ESCAPE",       ALWAYS                 },
  { "EXCEPT",           "TK_EXCEPT",       COMPOUND               },
  { "EXCLUSIVE",        "TK_EXCLUSIVE",    ALWAYS                 },
  { "EXPLAIN",          "TK_EXPLAIN",      EXPLAIN                },
  { "FAIL",             "TK_FAIL",         CONFLICT|TRIGGER       },
  { "FOR",              "TK_FOR",          TRIGGER                },
  { "FOREIGN",          "TK_FOREIGN",      FKEY                   },
  { "FROM",             "TK_FROM",         ALWAYS                 },