This patch contains the beginnings of the data-typing infrastructure.
The new build-in TypeOf() function is added. New opcodes for doing
pure text comparisons are added. Most changes are disabled pending
the 2.6.0 release. (CVS 632)
FossilOrigin-Name: cbbc858d973c2d515c6a2464981316549a241b73
diff --git a/src/build.c b/src/build.c
index 22f87dd..7e79202 100644
--- a/src/build.c
+++ b/src/build.c
@@ -25,7 +25,7 @@
** ROLLBACK
** PRAGMA
**
-** $Id: build.c,v 1.96 2002/06/17 17:07:20 drh Exp $
+** $Id: build.c,v 1.97 2002/06/20 11:36:49 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>
@@ -504,6 +504,7 @@
Table *p;
int i;
char *z = 0;
+ Column *pCol;
if( (p = pParse->pNewTable)==0 ) return;
sqliteSetNString(&z, pName->z, pName->n, 0);
if( z==0 ) return;
@@ -522,8 +523,11 @@
if( aNew==0 ) return;
p->aCol = aNew;
}
- memset(&p->aCol[p->nCol], 0, sizeof(p->aCol[0]));
- p->aCol[p->nCol++].zName = z;
+ pCol = &p->aCol[p->nCol];
+ memset(pCol, 0, sizeof(p->aCol[0]));
+ pCol->zName = z;
+ pCol->sortOrder = SQLITE_SO_NUM;
+ p->nCol++;
}
/*
@@ -554,10 +558,12 @@
int i, j;
int n;
char *z, **pz;
+ Column *pCol;
if( (p = pParse->pNewTable)==0 ) return;
i = p->nCol-1;
if( i<0 ) return;
- pz = &p->aCol[i].zType;
+ pCol = &p->aCol[i];
+ pz = &pCol->zType;
n = pLast->n + Addr(pLast->z) - Addr(pFirst->z);
sqliteSetNString(pz, pFirst->z, n, 0);
z = *pz;
@@ -568,6 +574,31 @@
z[j++] = c;
}
z[j] = 0;
+ pCol->sortOrder = SQLITE_SO_NUM;
+ for(i=0; z[i]; i++){
+ switch( z[i] ){
+ case 'c':
+ case 'C': {
+ if( sqliteStrNICmp(&z[i],"char",4)==0 ||
+ sqliteStrNICmp(&z[i],"clob",4)==0 ){
+ pCol->sortOrder = SQLITE_SO_TEXT;
+ return;
+ }
+ break;
+ }
+ case 'x':
+ case 'X': {
+ if( i>=2 && sqliteStrNICmp(&z[i-2],"text",4)==0 ){
+ pCol->sortOrder = SQLITE_SO_TEXT;
+ return;
+ }
+ break;
+ }
+ default: {
+ break;
+ }
+ }
+ }
}
/*
diff --git a/src/expr.c b/src/expr.c
index e6a3cfa..92ae657 100644
--- a/src/expr.c
+++ b/src/expr.c
@@ -12,7 +12,7 @@
** This file contains routines used for analyzing expressions and
** for generating VDBE code that evaluates expressions in SQLite.
**
-** $Id: expr.c,v 1.72 2002/06/17 17:07:20 drh Exp $
+** $Id: expr.c,v 1.73 2002/06/20 11:36:49 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>
@@ -460,8 +460,10 @@
if( j==pTab->iPKey ){
/* Substitute the record number for the INTEGER PRIMARY KEY */
pExpr->iColumn = -1;
+ pExpr->dataType = SQLITE_SO_NUM;
}else{
pExpr->iColumn = j;
+ pExpr->dataType = pTab->aCol[j].sortOrder & SQLITE_SO_TYPEMASK;
}
pExpr->op = TK_COLUMN;
}
@@ -485,6 +487,7 @@
pExpr->iTable = base;
cnt = 1 + (pTabList->nSrc>1);
pExpr->op = TK_COLUMN;
+ pExpr->dataType = SQLITE_SO_NUM;
}
sqliteFree(z);
if( cnt==0 && pExpr->token.z[0]!='"' ){
@@ -546,6 +549,7 @@
}else{
pExpr->iColumn = j;
}
+ pExpr->dataType = pTab->aCol[j].sortOrder & SQLITE_SO_TYPEMASK;
}
}
}
@@ -568,10 +572,12 @@
if( t ){
int j;
- for(j=0; j < pTriggerStack->pTab->nCol; j++) {
- if( sqliteStrICmp(pTriggerStack->pTab->aCol[j].zName, zRight)==0 ){
+ Table *pTab = pTriggerStack->pTab;
+ for(j=0; j < pTab->nCol; j++) {
+ if( sqliteStrICmp(pTab->aCol[j].zName, zRight)==0 ){
cnt++;
pExpr->iColumn = j;
+ pExpr->dataType = pTab->aCol[j].sortOrder & SQLITE_SO_TYPEMASK;
}
}
}
@@ -580,6 +586,7 @@
if( cnt==0 && cntTab==1 && sqliteIsRowid(zRight) ){
cnt = 1;
pExpr->iColumn = -1;
+ pExpr->dataType = SQLITE_SO_NUM;
}
sqliteFree(zLeft);
sqliteFree(zRight);
@@ -714,10 +721,11 @@
if( pExpr==0 ) return 0;
switch( pExpr->op ){
case TK_FUNCTION: {
- int n = pExpr->pList ? pExpr->pList->nExpr : 0;
- int no_such_func = 0;
- int wrong_num_args = 0;
- int is_agg = 0;
+ int n = pExpr->pList ? pExpr->pList->nExpr : 0; /* Number of arguments */
+ int no_such_func = 0; /* True if no such function exists */
+ int is_type_of = 0; /* True if is the special TypeOf() function */
+ int wrong_num_args = 0; /* True if wrong number of arguments */
+ int is_agg = 0; /* True if is an aggregate function */
int i;
FuncDef *pDef;
@@ -727,7 +735,12 @@
pDef = sqliteFindFunction(pParse->db,
pExpr->token.z, pExpr->token.n, -1, 0);
if( pDef==0 ){
- no_such_func = 1;
+ if( n==1 && pExpr->token.n==6
+ && sqliteStrNICmp(pExpr->token.z, "typeof", 6)==0 ){
+ is_type_of = 1;
+ }else {
+ no_such_func = 1;
+ }
}else{
wrong_num_args = 1;
}
@@ -758,6 +771,37 @@
nErr = sqliteExprCheck(pParse, pExpr->pList->a[i].pExpr,
allowAgg && !is_agg, pIsAgg);
}
+ if( pDef==0 ){
+ if( is_type_of ){
+ pExpr->op = TK_STRING;
+ if( sqliteExprType(pExpr->pList->a[0].pExpr)==SQLITE_SO_NUM ){
+ pExpr->token.z = "numeric";
+ pExpr->token.n = 7;
+ }else{
+ pExpr->token.z = "text";
+ pExpr->token.n = 4;
+ }
+ }
+ }else if( pDef->dataType>=0 ){
+ if( pDef->dataType<n ){
+ pExpr->dataType =
+ sqliteExprType(pExpr->pList->a[pDef->dataType].pExpr);
+ }else{
+ pExpr->dataType = SQLITE_SO_NUM;
+ }
+ }else if( pDef->dataType==SQLITE_ARGS ){
+ pDef->dataType = SQLITE_SO_TEXT;
+ for(i=0; i<n; i++){
+ if( sqliteExprType(pExpr->pList->a[i].pExpr)==SQLITE_SO_NUM ){
+ pExpr->dataType = SQLITE_SO_NUM;
+ break;
+ }
+ }
+ }else if( pDef->dataType==SQLITE_NUMERIC ){
+ pExpr->dataType = SQLITE_SO_NUM;
+ }else{
+ pExpr->dataType = SQLITE_SO_TEXT;
+ }
}
default: {
if( pExpr->pLeft ){
@@ -781,6 +825,78 @@
}
/*
+** Return either SQLITE_SO_NUM or SQLITE_SO_TEXT to indicate whether the
+** given expression should sort as numeric values or as text.
+**
+** The sqliteExprResolveIds() and sqliteExprCheck() routines must have
+** both been called on the expression before it is passed to this routine.
+*/
+int sqliteExprType(Expr *p){
+ if( p==0 ) return SQLITE_SO_NUM;
+ while( p ) switch( p->op ){
+ case TK_PLUS:
+ case TK_MINUS:
+ case TK_STAR:
+ case TK_SLASH:
+ case TK_AND:
+ case TK_OR:
+ case TK_ISNULL:
+ case TK_NOTNULL:
+ case TK_NOT:
+ case TK_UMINUS:
+ case TK_BITAND:
+ case TK_BITOR:
+ case TK_BITNOT:
+ case TK_LSHIFT:
+ case TK_RSHIFT:
+ case TK_REM:
+ case TK_INTEGER:
+ case TK_FLOAT:
+ case TK_IN:
+ case TK_BETWEEN:
+ return SQLITE_SO_NUM;
+
+ case TK_STRING:
+ case TK_NULL:
+ case TK_CONCAT:
+ return SQLITE_SO_TEXT;
+
+ case TK_LT:
+ case TK_LE:
+ case TK_GT:
+ case TK_GE:
+ case TK_NE:
+ case TK_EQ:
+ if( sqliteExprType(p->pLeft)==SQLITE_SO_NUM ){
+ return SQLITE_SO_NUM;
+ }
+ p = p->pRight;
+ break;
+
+ case TK_AS:
+ p = p->pLeft;
+ break;
+
+ case TK_COLUMN:
+ case TK_FUNCTION:
+ case TK_AGG_FUNCTION:
+ return p->dataType;
+
+ case TK_SELECT:
+ assert( p->pSelect );
+ assert( p->pSelect->pEList );
+ assert( p->pSelect->pEList->nExpr>0 );
+ p = p->pSelect->pEList->a[0].pExpr;
+ break;
+
+ default:
+ assert( p->op==TK_ABORT ); /* Can't Happen */
+ break;
+ }
+ return SQLITE_SO_NUM;
+}
+
+/*
** Generate code into the current Vdbe to evaluate the given
** expression and leave the result on the top of stack.
*/
@@ -856,6 +972,17 @@
sqliteVdbeAddOp(v, OP_String, 0, 0);
break;
}
+ case TK_LT:
+ case TK_LE:
+ case TK_GT:
+ case TK_GE:
+ case TK_NE:
+ case TK_EQ: {
+ if( pParse->db->file_format>=3 && sqliteExprType(pExpr)==SQLITE_SO_TEXT ){
+ op += 6; /* Convert numeric opcodes to text opcodes */
+ }
+ /* Fall through into the next case */
+ }
case TK_AND:
case TK_OR:
case TK_PLUS:
@@ -864,13 +991,7 @@
case TK_REM:
case TK_BITAND:
case TK_BITOR:
- case TK_SLASH:
- case TK_LT:
- case TK_LE:
- case TK_GT:
- case TK_GE:
- case TK_NE:
- case TK_EQ: {
+ case TK_SLASH: {
sqliteExprCode(pParse, pExpr->pLeft);
sqliteExprCode(pParse, pExpr->pRight);
sqliteVdbeAddOp(v, op, 0, 0);
@@ -1090,6 +1211,9 @@
case TK_EQ: {
sqliteExprCode(pParse, pExpr->pLeft);
sqliteExprCode(pParse, pExpr->pRight);
+ if( pParse->db->file_format>=3 && sqliteExprType(pExpr)==SQLITE_SO_TEXT ){
+ op += 6; /* Convert numeric opcodes to text opcodes */
+ }
sqliteVdbeAddOp(v, op, jumpIfNull, dest);
break;
}
@@ -1180,6 +1304,9 @@
case TK_GE:
case TK_NE:
case TK_EQ: {
+ if( pParse->db->file_format>=3 && sqliteExprType(pExpr)==SQLITE_SO_TEXT ){
+ op += 6; /* Convert numeric opcodes to text opcodes */
+ }
sqliteExprCode(pParse, pExpr->pLeft);
sqliteExprCode(pParse, pExpr->pRight);
sqliteVdbeAddOp(v, op, jumpIfNull, dest);
@@ -1395,6 +1522,7 @@
if( p==0 && createFlag && (p = sqliteMalloc(sizeof(*p)))!=0 ){
p->nArg = nArg;
p->pNext = pFirst;
+ p->dataType = pFirst ? pFirst->dataType : SQLITE_NUMERIC;
sqliteHashInsert(&db->aFunc, zName, nName, (void*)p);
}
return p;
diff --git a/src/func.c b/src/func.c
index 3a50a6e..518973f 100644
--- a/src/func.c
+++ b/src/func.c
@@ -16,7 +16,7 @@
** sqliteRegisterBuildinFunctions() found at the bottom of the file.
** All other code has file scope.
**
-** $Id: func.c,v 1.20 2002/06/09 10:14:19 drh Exp $
+** $Id: func.c,v 1.21 2002/06/20 11:36:49 drh Exp $
*/
#include <ctype.h>
#include <math.h>
@@ -416,42 +416,44 @@
static struct {
char *zName;
int nArg;
+ int dataType;
void (*xFunc)(sqlite_func*,int,const char**);
} aFuncs[] = {
- { "min", -1, minFunc },
- { "min", 0, 0 },
- { "max", -1, maxFunc },
- { "max", 0, 0 },
- { "length", 1, lengthFunc },
- { "substr", 3, substrFunc },
- { "abs", 1, absFunc },
- { "round", 1, roundFunc },
- { "round", 2, roundFunc },
- { "upper", 1, upperFunc },
- { "lower", 1, lowerFunc },
- { "coalesce", -1, ifnullFunc },
- { "coalesce", 0, 0 },
- { "coalesce", 1, 0 },
- { "ifnull", 2, ifnullFunc },
- { "random", -1, randomFunc },
- { "like", 2, likeFunc },
- { "glob", 2, globFunc },
- { "nullif", 2, nullifFunc },
+ { "min", -1, SQLITE_ARGS, minFunc },
+ { "min", 0, 0, 0 },
+ { "max", -1, SQLITE_ARGS, maxFunc },
+ { "max", 0, 0, 0 },
+ { "length", 1, SQLITE_NUMERIC, lengthFunc },
+ { "substr", 3, SQLITE_TEXT, substrFunc },
+ { "abs", 1, SQLITE_NUMERIC, absFunc },
+ { "round", 1, SQLITE_NUMERIC, roundFunc },
+ { "round", 2, SQLITE_NUMERIC, roundFunc },
+ { "upper", 1, SQLITE_TEXT, upperFunc },
+ { "lower", 1, SQLITE_TEXT, lowerFunc },
+ { "coalesce", -1, SQLITE_ARGS, ifnullFunc },
+ { "coalesce", 0, 0, 0 },
+ { "coalesce", 1, 0, 0 },
+ { "ifnull", 2, SQLITE_ARGS, ifnullFunc },
+ { "random", -1, SQLITE_NUMERIC, randomFunc },
+ { "like", 2, SQLITE_NUMERIC, likeFunc },
+ { "glob", 2, SQLITE_NUMERIC, globFunc },
+ { "nullif", 2, SQLITE_ARGS, nullifFunc },
};
static struct {
char *zName;
int nArg;
+ int dataType;
void (*xStep)(sqlite_func*,int,const char**);
void (*xFinalize)(sqlite_func*);
} aAggs[] = {
- { "min", 1, minStep, minMaxFinalize },
- { "max", 1, maxStep, minMaxFinalize },
- { "sum", 1, sumStep, sumFinalize },
- { "avg", 1, sumStep, avgFinalize },
- { "count", 0, countStep, countFinalize },
- { "count", 1, countStep, countFinalize },
+ { "min", 1, 0, minStep, minMaxFinalize },
+ { "max", 1, 0, maxStep, minMaxFinalize },
+ { "sum", 1, SQLITE_NUMERIC, sumStep, sumFinalize },
+ { "avg", 1, SQLITE_NUMERIC, sumStep, avgFinalize },
+ { "count", 0, SQLITE_NUMERIC, countStep, countFinalize },
+ { "count", 1, SQLITE_NUMERIC, countStep, countFinalize },
#if 0
- { "stddev", 1, stdDevStep, stdDevFinalize },
+ { "stddev", 1, SQLITE_NUMERIC, stdDevStep, stdDevFinalize },
#endif
};
int i;
@@ -459,11 +461,16 @@
for(i=0; i<sizeof(aFuncs)/sizeof(aFuncs[0]); i++){
sqlite_create_function(db, aFuncs[i].zName,
aFuncs[i].nArg, aFuncs[i].xFunc, 0);
+ if( aFuncs[i].xFunc ){
+ sqlite_function_type(db, aFuncs[i].zName, aFuncs[i].dataType);
+ }
}
sqlite_create_function(db, "last_insert_rowid", 0,
last_insert_rowid, db);
+ sqlite_function_type(db, "last_insert_rowid", SQLITE_NUMERIC);
for(i=0; i<sizeof(aAggs)/sizeof(aAggs[0]); i++){
sqlite_create_aggregate(db, aAggs[i].zName,
aAggs[i].nArg, aAggs[i].xStep, aAggs[i].xFinalize, 0);
+ sqlite_function_type(db, aAggs[i].zName, aAggs[i].dataType);
}
}
diff --git a/src/main.c b/src/main.c
index 84614c5..a5e1be6 100644
--- a/src/main.c
+++ b/src/main.c
@@ -14,7 +14,7 @@
** other files are for internal use by SQLite and should not be
** accessed by users of the library.
**
-** $Id: main.c,v 1.80 2002/06/16 18:21:44 drh Exp $
+** $Id: main.c,v 1.81 2002/06/20 11:36:49 drh Exp $
*/
#include "sqliteInt.h"
#include "os.h"
@@ -51,6 +51,11 @@
break;
}
case 'f': { /* File format */
+ /*
+ ** file_format==1 Version 2.1.0.
+ ** file_format==2 Version 2.2.0. Integer primary key.
+ ** file_format==3 Version 2.6.0. Separate text and numeric datatypes.
+ */
db->file_format = atoi(argv[3]);
break;
}
@@ -827,3 +832,14 @@
p->pUserData = pUserData;
return 0;
}
+
+/*
+** Change the datatype for all functions with a given name.
+*/
+int sqlite_function_type(sqlite *db, const char *zName, int dataType){
+ FuncDef *p = (FuncDef*)sqliteHashFind(&db->aFunc, zName, strlen(zName));
+ while( p ){
+ p->dataType = dataType;
+ p = p->pNext;
+ }
+}
diff --git a/src/sqlite.h.in b/src/sqlite.h.in
index 29dbb87..5d7d52c 100644
--- a/src/sqlite.h.in
+++ b/src/sqlite.h.in
@@ -12,7 +12,7 @@
** This header file defines the interface that the SQLite library
** presents to client programs.
**
-** @(#) $Id: sqlite.h.in,v 1.31 2002/05/10 05:44:56 drh Exp $
+** @(#) $Id: sqlite.h.in,v 1.32 2002/06/20 11:36:50 drh Exp $
*/
#ifndef _SQLITE_H_
#define _SQLITE_H_
@@ -427,6 +427,27 @@
);
/*
+** Use the following routine to define the datatype returned by a
+** user-defined function. The second argument can be one of the
+** constants SQLITE_NUMERIC, SQLITE_TEXT, or SQLITE_ARGS or it
+** can be an integer greater than or equal to zero. The datatype
+** will be numeric or text (the only two types supported) if the
+** argument is SQLITE_NUMERIC or SQLITE_TEXT. If the argument is
+** SQLITE_ARGS, then the datatype is numeric if any argument to the
+** function is numeric and is text otherwise. If the second argument
+** is an integer, then the datatype of the result is the same as the
+** parameter to the function that corresponds to that integer.
+*/
+int sqlite_function_type(
+ sqlite *db, /* The database there the function is registered */
+ const char *zName, /* Name of the function */
+ int datatype /* The datatype for this function */
+);
+#define SQLITE_NUMERIC (-1)
+#define SQLITE_TEXT (-2)
+#define SQLITE_ARGS (-3)
+
+/*
** The user function implementations call one of the following four routines
** in order to return their results. The first parameter to each of these
** routines is a copy of the first argument to xFunc() or xFinialize().
diff --git a/src/sqliteInt.h b/src/sqliteInt.h
index f20caa7..79a44f9 100644
--- a/src/sqliteInt.h
+++ b/src/sqliteInt.h
@@ -11,7 +11,7 @@
*************************************************************************
** Internal interface definitions for SQLite.
**
-** @(#) $Id: sqliteInt.h,v 1.126 2002/06/19 14:27:05 drh Exp $
+** @(#) $Id: sqliteInt.h,v 1.127 2002/06/20 11:36:50 drh Exp $
*/
#include "sqlite.h"
#include "hash.h"
@@ -176,7 +176,18 @@
typedef struct TriggerStack TriggerStack;
/*
-** Each database is an instance of the following structure
+** Each database is an instance of the following structure.
+**
+** The sqlite.file_format is initialized by the database file
+** and helps determines how the data in the database file is
+** represented. This field allows newer versions of the library
+** to read and write older databases. The various file formats
+** are as follows:
+**
+** file_format==1 Version 2.1.0.
+** file_format==2 Version 2.2.0. Add support for INTEGER PRIMARY KEY.
+** file_format==3 Version 2.6.0. Add support for separate numeric and
+** text datatypes.
*/
struct sqlite {
Btree *pBe; /* The B*Tree backend */
@@ -244,6 +255,7 @@
void (*xStep)(sqlite_func*,int,const char**); /* Aggregate function step */
void (*xFinalize)(sqlite_func*); /* Aggregate function finializer */
int nArg; /* Number of arguments */
+ int dataType; /* Datatype of the result */
void *pUserData; /* User data parameter */
FuncDef *pNext; /* Next function with same name */
};
@@ -876,6 +888,7 @@
int sqliteLikeCompare(const unsigned char*,const unsigned char*);
char *sqliteTableNameFromToken(Token*);
int sqliteExprCheck(Parse*, Expr*, int, int*);
+int sqliteExprType(Expr*);
int sqliteExprCompare(Expr*, Expr*);
int sqliteFuncId(Token*);
int sqliteExprResolveIds(Parse*, int, SrcList*, ExprList*, Expr*);
diff --git a/src/vdbe.c b/src/vdbe.c
index 8589fd3..d5ad988 100644
--- a/src/vdbe.c
+++ b/src/vdbe.c
@@ -30,7 +30,7 @@
** But other routines are also provided to help in building up
** a program instruction by instruction.
**
-** $Id: vdbe.c,v 1.156 2002/06/14 22:38:43 drh Exp $
+** $Id: vdbe.c,v 1.157 2002/06/20 11:36:50 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>
@@ -1077,10 +1077,11 @@
"Divide", "Remainder", "BitAnd", "BitOr",
"BitNot", "ShiftLeft", "ShiftRight", "AbsValue",
"Eq", "Ne", "Lt", "Le",
- "Gt", "Ge", "IsNull", "NotNull",
- "Negative", "And", "Or", "Not",
- "Concat", "Noop", "Function", "Limit",
- "LimitCk",
+ "Gt", "Ge", "StrEq", "StrNe",
+ "StrLt", "StrLe", "StrGt", "StrGe",
+ "IsNull", "NotNull", "Negative", "And",
+ "Or", "Not", "Concat", "Noop",
+ "Function", "Limit", "LimitCk",
};
/*
@@ -1992,6 +1993,11 @@
** If either operand is NULL (and thus if the result is unknown) then
** take the jump if P1 is true.
**
+** If both values are numeric, they are converted to doubles using atof()
+** and compared for equality that way. Otherwise the strcmp() library
+** routine is used for the comparison. For a pure text comparison
+** use OP_StrEq.
+**
** If P2 is zero, do not jump. Instead, push an integer 1 onto the
** stack if the jump would have been taken, or a 0 if not. Push a
** NULL if either operand was NULL.
@@ -2004,6 +2010,11 @@
** If either operand is NULL (and thus if the result is unknown) then
** take the jump if P1 is true.
**
+** If both values are numeric, they are converted to doubles using atof()
+** and compared in that format. Otherwise the strcmp() library
+** routine is used for the comparison. For a pure text comparison
+** use OP_StrNe.
+**
** If P2 is zero, do not jump. Instead, push an integer 1 onto the
** stack if the jump would have been taken, or a 0 if not. Push a
** NULL if either operand was NULL.
@@ -2018,6 +2029,12 @@
** If either operand is NULL (and thus if the result is unknown) then
** take the jump if P1 is true.
**
+** If both values are numeric, they are converted to doubles using atof()
+** and compared in that format. Numeric values are always less than
+** non-numeric values. If both operands are non-numeric, the strcmp() library
+** routine is used for the comparison. For a pure text comparison
+** use OP_StrLt.
+**
** If P2 is zero, do not jump. Instead, push an integer 1 onto the
** stack if the jump would have been taken, or a 0 if not. Push a
** NULL if either operand was NULL.
@@ -2031,6 +2048,12 @@
** If either operand is NULL (and thus if the result is unknown) then
** take the jump if P1 is true.
**
+** If both values are numeric, they are converted to doubles using atof()
+** and compared in that format. Numeric values are always less than
+** non-numeric values. If both operands are non-numeric, the strcmp() library
+** routine is used for the comparison. For a pure text comparison
+** use OP_StrLe.
+**
** If P2 is zero, do not jump. Instead, push an integer 1 onto the
** stack if the jump would have been taken, or a 0 if not. Push a
** NULL if either operand was NULL.
@@ -2044,6 +2067,12 @@
** If either operand is NULL (and thus if the result is unknown) then
** take the jump if P1 is true.
**
+** If both values are numeric, they are converted to doubles using atof()
+** and compared in that format. Numeric values are always less than
+** non-numeric values. If both operands are non-numeric, the strcmp() library
+** routine is used for the comparison. For a pure text comparison
+** use OP_StrGt.
+**
** If P2 is zero, do not jump. Instead, push an integer 1 onto the
** stack if the jump would have been taken, or a 0 if not. Push a
** NULL if either operand was NULL.
@@ -2057,6 +2086,12 @@
** If either operand is NULL (and thus if the result is unknown) then
** take the jump if P1 is true.
**
+** If both values are numeric, they are converted to doubles using atof()
+** and compared in that format. Numeric values are always less than
+** non-numeric values. If both operands are non-numeric, the strcmp() library
+** routine is used for the comparison. For a pure text comparison
+** use OP_StrGe.
+**
** If P2 is zero, do not jump. Instead, push an integer 1 onto the
** stack if the jump would have been taken, or a 0 if not. Push a
** NULL if either operand was NULL.
@@ -2116,6 +2151,145 @@
break;
}
+/* Opcode: StrEq P1 P2 *
+**
+** Pop the top two elements from the stack. If they are equal, then
+** jump to instruction P2. Otherwise, continue to the next instruction.
+**
+** If either operand is NULL (and thus if the result is unknown) then
+** take the jump if P1 is true.
+**
+** The strcmp() library routine is used for the comparison. For a
+** numeric comparison, use OP_Eq.
+**
+** If P2 is zero, do not jump. Instead, push an integer 1 onto the
+** stack if the jump would have been taken, or a 0 if not. Push a
+** NULL if either operand was NULL.
+*/
+/* Opcode: StrNe P1 P2 *
+**
+** Pop the top two elements from the stack. If they are not equal, then
+** jump to instruction P2. Otherwise, continue to the next instruction.
+**
+** If either operand is NULL (and thus if the result is unknown) then
+** take the jump if P1 is true.
+**
+** The strcmp() library routine is used for the comparison. For a
+** numeric comparison, use OP_Ne.
+**
+** If P2 is zero, do not jump. Instead, push an integer 1 onto the
+** stack if the jump would have been taken, or a 0 if not. Push a
+** NULL if either operand was NULL.
+*/
+/* Opcode: StrLt P1 P2 *
+**
+** Pop the top two elements from the stack. If second element (the
+** next on stack) is less than the first (the top of stack), then
+** jump to instruction P2. Otherwise, continue to the next instruction.
+** In other words, jump if NOS<TOS.
+**
+** If either operand is NULL (and thus if the result is unknown) then
+** take the jump if P1 is true.
+**
+** The strcmp() library routine is used for the comparison. For a
+** numeric comparison, use OP_Lt.
+**
+** If P2 is zero, do not jump. Instead, push an integer 1 onto the
+** stack if the jump would have been taken, or a 0 if not. Push a
+** NULL if either operand was NULL.
+*/
+/* Opcode: StrLe P1 P2 *
+**
+** Pop the top two elements from the stack. If second element (the
+** next on stack) is less than or equal to the first (the top of stack),
+** then jump to instruction P2. In other words, jump if NOS<=TOS.
+**
+** If either operand is NULL (and thus if the result is unknown) then
+** take the jump if P1 is true.
+**
+** The strcmp() library routine is used for the comparison. For a
+** numeric comparison, use OP_Le.
+**
+** If P2 is zero, do not jump. Instead, push an integer 1 onto the
+** stack if the jump would have been taken, or a 0 if not. Push a
+** NULL if either operand was NULL.
+*/
+/* Opcode: StrGt P1 P2 *
+**
+** Pop the top two elements from the stack. If second element (the
+** next on stack) is greater than the first (the top of stack),
+** then jump to instruction P2. In other words, jump if NOS>TOS.
+**
+** If either operand is NULL (and thus if the result is unknown) then
+** take the jump if P1 is true.
+**
+** The strcmp() library routine is used for the comparison. For a
+** numeric comparison, use OP_Gt.
+**
+** If P2 is zero, do not jump. Instead, push an integer 1 onto the
+** stack if the jump would have been taken, or a 0 if not. Push a
+** NULL if either operand was NULL.
+*/
+/* Opcode: StrGe P1 P2 *
+**
+** Pop the top two elements from the stack. If second element (the next
+** on stack) is greater than or equal to the first (the top of stack),
+** then jump to instruction P2. In other words, jump if NOS>=TOS.
+**
+** If either operand is NULL (and thus if the result is unknown) then
+** take the jump if P1 is true.
+**
+** The strcmp() library routine is used for the comparison. For a
+** numeric comparison, use OP_Ge.
+**
+** If P2 is zero, do not jump. Instead, push an integer 1 onto the
+** stack if the jump would have been taken, or a 0 if not. Push a
+** NULL if either operand was NULL.
+*/
+case OP_StrEq:
+case OP_StrNe:
+case OP_StrLt:
+case OP_StrLe:
+case OP_StrGt:
+case OP_StrGe: {
+ int tos = p->tos;
+ int nos = tos - 1;
+ int c;
+ VERIFY( if( nos<0 ) goto not_enough_stack; )
+ if( (aStack[nos].flags | aStack[tos].flags) & STK_Null ){
+ POPSTACK;
+ POPSTACK;
+ if( pOp->p2 ){
+ if( pOp->p1 ) pc = pOp->p2-1;
+ }else{
+ p->tos++;
+ aStack[nos].flags = STK_Null;
+ }
+ break;
+ }else{
+ if( Stringify(p, tos) || Stringify(p, nos) ) goto no_mem;
+ c = strcmp(zStack[nos], zStack[tos]);
+ }
+ switch( pOp->opcode ){
+ case OP_Eq: c = c==0; break;
+ case OP_Ne: c = c!=0; break;
+ case OP_Lt: c = c<0; break;
+ case OP_Le: c = c<=0; break;
+ case OP_Gt: c = c>0; break;
+ default: c = c>=0; break;
+ }
+ POPSTACK;
+ POPSTACK;
+ if( pOp->p2 ){
+ if( c ) pc = pOp->p2-1;
+ }else{
+ p->tos++;
+ aStack[nos].flags = STK_Int;
+ aStack[nos].i = c;
+ }
+ break;
+}
+
/* Opcode: And * * *
**
** Pop two values off the stack. Take the logical AND of the
@@ -2435,7 +2609,7 @@
break;
}
-/* Opcode: MakeKey P1 P2 *
+/* Opcode: MakeKey P1 P2 P3
**
** Convert the top P1 entries of the stack into a single entry suitable
** for use as the key in an index. The top P1 records are
@@ -2449,9 +2623,14 @@
** data is popped off the stack first then the new key is pushed
** back in its place.
**
+** P3 is a string that is P1 characters long. Each character is either
+** an 'n' or a 't' to indicates if the argument should be numeric or
+** text. The first character corresponds to the lowest element on the
+** stack.
+**
** See also: MakeIdxKey, SortMakeKey
*/
-/* Opcode: MakeIdxKey P1 P2 *
+/* Opcode: MakeIdxKey P1 P2 P3
**
** Convert the top P1 entries of the stack into a single entry suitable
** for use as the key in an index. In addition, take one additional integer
@@ -2473,6 +2652,11 @@
** guaranteed to be unique. This jump can be used to skip a subsequent
** uniqueness test.
**
+** P3 is a string that is P1 characters long. Each character is either
+** an 'n' or a 't' to indicates if the argument should be numeric or
+** text. The first character corresponds to the lowest element on the
+** stack.
+**
** See also: MakeKey, SortMakeKey
*/
case OP_MakeIdxKey:
@@ -2488,13 +2672,15 @@
nField = pOp->p1;
VERIFY( if( p->tos+1+addRowid<nField ) goto not_enough_stack; )
nByte = 0;
- for(i=p->tos-nField+1; i<=p->tos; i++){
+ for(j=0, i=p->tos-nField+1; i<=p->tos; i++, j++){
int flags = aStack[i].flags;
int len;
char *z;
if( flags & STK_Null ){
nByte += 2;
containsNull = 1;
+ }else if( pOp->p3 && pOp->p3[j]=='t' ){
+ Stringify(p, i);
}else if( flags & STK_Real ){
z = aStack[i].z;
sqliteRealToSortable(aStack[i].r, &z[1]);
diff --git a/src/vdbe.h b/src/vdbe.h
index 4e2327e..91b34f2 100644
--- a/src/vdbe.h
+++ b/src/vdbe.h
@@ -15,7 +15,7 @@
** or VDBE. The VDBE implements an abstract machine that runs a
** simple program to access and modify the underlying database.
**
-** $Id: vdbe.h,v 1.55 2002/06/14 22:38:43 drh Exp $
+** $Id: vdbe.h,v 1.56 2002/06/20 11:36:50 drh Exp $
*/
#ifndef _SQLITE_VDBE_H_
#define _SQLITE_VDBE_H_
@@ -187,27 +187,37 @@
#define OP_ShiftLeft 102
#define OP_ShiftRight 103
#define OP_AbsValue 104
+
+/* Note: The code generator assumes that OP_XX+6==OP_StrXX */
#define OP_Eq 105
#define OP_Ne 106
#define OP_Lt 107
#define OP_Le 108
#define OP_Gt 109
#define OP_Ge 110
-#define OP_IsNull 111
-#define OP_NotNull 112
-#define OP_Negative 113
-#define OP_And 114
-#define OP_Or 115
-#define OP_Not 116
-#define OP_Concat 117
-#define OP_Noop 118
-#define OP_Function 119
+#define OP_StrEq 111
+#define OP_StrNe 112
+#define OP_StrLt 113
+#define OP_StrLe 114
+#define OP_StrGt 115
+#define OP_StrGe 116
+/* Note: the code generator assumes that OP_XX+6==OP_StrXX */
-#define OP_Limit 120
-#define OP_LimitCk 121
+#define OP_IsNull 117
+#define OP_NotNull 118
+#define OP_Negative 119
+#define OP_And 120
+#define OP_Or 121
+#define OP_Not 122
+#define OP_Concat 123
+#define OP_Noop 124
+#define OP_Function 125
+
+#define OP_Limit 126
+#define OP_LimitCk 127
-#define OP_MAX 121
+#define OP_MAX 127
/*
** Prototypes for the VDBE interface. See comments on the implementation