SQLite

Check-in [e97c331362]
Login

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

Overview
Comment:Tcl interface transfers values directly between SQLite and Tcl_Objs, without at translation to strings. (CVS 1898)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: e97c331362545ce21117776c7b61d3488668f2bf
User & Date: drh 2004-08-20 18:34:20.000
Context
2004-08-21
17:54
Optimizations to the code generator. (CVS 1899) (check-in: bd6649c5aa user: drh tags: trunk)
2004-08-20
18:34
Tcl interface transfers values directly between SQLite and Tcl_Objs, without at translation to strings. (CVS 1898) (check-in: e97c331362 user: drh tags: trunk)
16:02
Add support for named wildcards in SQL statements. (CVS 1897) (check-in: d3be0b7c5a user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/tclsqlite.c.
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.
**
*************************************************************************
** A TCL Interface to SQLite
**
** $Id: tclsqlite.c,v 1.99 2004/08/20 16:02:39 drh Exp $
*/
#ifndef NO_TCL     /* Omit this whole file if TCL is unavailable */

#include "sqliteInt.h"
#include "hash.h"
#include "tcl.h"
#include <stdlib.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.
**
*************************************************************************
** A TCL Interface to SQLite
**
** $Id: tclsqlite.c,v 1.100 2004/08/20 18:34:20 drh Exp $
*/
#ifndef NO_TCL     /* Omit this whole file if TCL is unavailable */

#include "sqliteInt.h"
#include "hash.h"
#include "tcl.h"
#include <stdlib.h>
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
};

/*
** There is one instance of this structure for each SQLite database
** that has been opened by the SQLite TCL interface.
*/
typedef struct SqliteDb SqliteDb;
typedef struct SqlStmt SqlStmt;
struct SqliteDb {
  sqlite3 *db;          /* The "real" database structure */
  Tcl_Interp *interp;   /* The interpreter used for this database */
  char *zBusy;          /* The busy callback routine */
  char *zCommit;        /* The commit hook callback routine */
  char *zTrace;         /* The trace callback routine */
  char *zProgress;      /* The progress callback routine */
  char *zAuth;          /* The authorization callback routine */
  SqlFunc *pFunc;       /* List of SQL functions */
  SqlCollate *pCollate; /* List of SQL collation functions */
  int rc;               /* Return code of most recent sqlite3_exec() */
  Tcl_Obj *pCollateNeeded;  /* Collation needed script */
  SqlStmt *pStmtList;   /* List of all prepared statements */
};

/*
** Each prepared statement is an instance of the following structure.
*/
struct SqlStmt {
  SqliteDb *pDb;        /* The database that this statement is part of */
  SqlStmt *pAll;        /* Next statement in list of all for pDb */
  SqlStmt **ppPrev;     /* Previous pAll pointer */
  sqlite3_stmt *pVm;    /* Compiled statement. */
  int nBind;            /* Number of bindings in this statement */
  char *azBindVar[1];   /* Name of variables for each binding */
};

/*
** This is a second alternative callback for database queries.  A the
** first column of the first row of the result is made the TCL result.
*/
static int DbEvalCallback3(







<












<
<
<
<
<
<
<
<
<
<
<
<
<







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
};

/*
** There is one instance of this structure for each SQLite database
** that has been opened by the SQLite TCL interface.
*/
typedef struct SqliteDb SqliteDb;

struct SqliteDb {
  sqlite3 *db;          /* The "real" database structure */
  Tcl_Interp *interp;   /* The interpreter used for this database */
  char *zBusy;          /* The busy callback routine */
  char *zCommit;        /* The commit hook callback routine */
  char *zTrace;         /* The trace callback routine */
  char *zProgress;      /* The progress callback routine */
  char *zAuth;          /* The authorization callback routine */
  SqlFunc *pFunc;       /* List of SQL functions */
  SqlCollate *pCollate; /* List of SQL collation functions */
  int rc;               /* Return code of most recent sqlite3_exec() */
  Tcl_Obj *pCollateNeeded;  /* Collation needed script */













};

/*
** This is a second alternative callback for database queries.  A the
** first column of the first row of the result is made the TCL result.
*/
static int DbEvalCallback3(
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137

/*
** TCL calls this procedure when an sqlite3 database command is
** deleted.
*/
static void DbDeleteCmd(void *db){
  SqliteDb *pDb = (SqliteDb*)db;
  SqlStmt *pStmt, *pNextStmt;
  for(pStmt=pDb->pStmtList; pStmt; pStmt=pNextStmt){
    pNextStmt = pStmt->pAll;
    sqlite3_finalize(pStmt->pVm);
    Tcl_Free(pStmt);
  }
  sqlite3_close(pDb->db);
  while( pDb->pFunc ){
    SqlFunc *pFunc = pDb->pFunc;
    pDb->pFunc = pFunc->pNext;
    Tcl_Free((char*)pFunc);
  }
  while( pDb->pCollate ){







<
<
<
<
<
<







104
105
106
107
108
109
110






111
112
113
114
115
116
117

/*
** TCL calls this procedure when an sqlite3 database command is
** deleted.
*/
static void DbDeleteCmd(void *db){
  SqliteDb *pDb = (SqliteDb*)db;






  sqlite3_close(pDb->db);
  while( pDb->pFunc ){
    SqlFunc *pFunc = pDb->pFunc;
    pDb->pFunc = pFunc->pNext;
    Tcl_Free((char*)pFunc);
  }
  while( pDb->pCollate ){
712
713
714
715
716
717
718


719
720
721
722
723
724
725
726










727
728
729
730



731

732
733
734
735
736








737

738














































739
740
741
742
743
744
745
746
747
748
749


750
751
752
753
754
755
756
757
758
759

760






761
762







763



764
765

766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781











782
783
784
785
786
787
788
789
790
791
792
793
  ** If "array" is an empty string, then the values are placed in variables
  ** that have the same name as the fields extracted by the query.
  */
  case DB_EVAL: {
    char const *zSql;
    char const *zLeft;
    sqlite3_stmt *pStmt;



    Tcl_Obj *pRet = Tcl_NewObj();
    Tcl_IncrRefCount(pRet);

    if( objc<3 || objc>5 || objc==4 ){
      Tcl_WrongNumArgs(interp, 2, objv, "SQL ?ARRAY-NAME? ?SCRIPT?");
      return TCL_ERROR;
    }











    zSql = Tcl_GetStringFromObj(objv[2], 0);
    while( zSql[0] ){
      int i;



  

      if( SQLITE_OK!=sqlite3_prepare(pDb->db, zSql, -1, &pStmt, &zLeft) ){
        Tcl_SetObjResult(interp, dbTextToObj(sqlite3_errmsg(pDb->db)));
        rc = TCL_ERROR;
        break;
      }








  

      if( pStmt && objc==5 ){














































        Tcl_Obj *pColList = Tcl_NewObj();
        Tcl_IncrRefCount(pColList);

        for(i=0; i<sqlite3_column_count(pStmt); i++){
          Tcl_ListObjAppendElement(interp, pColList,
              dbTextToObj(sqlite3_column_name(pStmt, i))
          );
        }
        Tcl_ObjSetVar2(interp,objv[3],Tcl_NewStringObj("*",-1),pColList,0);
      }



      while( pStmt && SQLITE_ROW==sqlite3_step(pStmt) ){
        for(i=0; i<sqlite3_column_count(pStmt); i++){
          Tcl_Obj *pVal;
          
          /* Set pVal to contain the i'th column of this row. */
          if( SQLITE_BLOB!=sqlite3_column_type(pStmt, i) ){
            pVal = dbTextToObj(sqlite3_column_text(pStmt, i));
          }else{
            int bytes = sqlite3_column_bytes(pStmt, i);
            pVal = Tcl_NewByteArrayObj(sqlite3_column_blob(pStmt, i), bytes);

          }






  
          if( objc==5 ){







            Tcl_Obj *pName = dbTextToObj(sqlite3_column_name(pStmt, i));



            Tcl_IncrRefCount(pName);
            if( !strcmp("", Tcl_GetString(objv[3])) ){

              Tcl_ObjSetVar2(interp, pName, 0, pVal, 0);
            }else{
              Tcl_ObjSetVar2(interp, objv[3], pName, pVal, 0);
            }
            Tcl_DecrRefCount(pName);
          }else{
            Tcl_ListObjAppendElement(interp, pRet, pVal);
          }
        }
  
        if( objc==5 ){
          rc = Tcl_EvalObjEx(interp, objv[4], 0);
          if( rc!=TCL_ERROR ) rc = TCL_OK;
        }
      }
       











      if( pStmt && SQLITE_SCHEMA==sqlite3_finalize(pStmt) ){
        continue;
      }
  
      if( pStmt && SQLITE_OK!=sqlite3_errcode(pDb->db) ){
        Tcl_SetObjResult(interp, dbTextToObj(sqlite3_errmsg(pDb->db)));
        rc = TCL_ERROR;
        break;
      }

      zSql = zLeft;
    }







>
>




|



>
>
>
>
>
>
>
>
>
>



|
>
>
>

>





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


|
<
|
<
<

|


>
>

|



|
<
|
|
|
>
|
>
>
>
>
>
>
|
|
>
>
>
>
>
>
>
|
>
>
>
|
|
>
|

|

<





|
|



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



|







692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792

793


794
795
796
797
798
799
800
801
802
803
804
805

806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836

837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
  ** If "array" is an empty string, then the values are placed in variables
  ** that have the same name as the fields extracted by the query.
  */
  case DB_EVAL: {
    char const *zSql;
    char const *zLeft;
    sqlite3_stmt *pStmt;
    Tcl_Obj *pArray;       /* Name of array into which results are written */
    Tcl_Obj *pScript;      /* Script to run for each result set */

    Tcl_Obj *pRet = Tcl_NewObj();
    Tcl_IncrRefCount(pRet);

    if( objc<3 || objc>5 ){
      Tcl_WrongNumArgs(interp, 2, objv, "SQL ?ARRAY-NAME? ?SCRIPT?");
      return TCL_ERROR;
    }
    if( objc==3 ){
      pArray = pScript = 0;
    }else if( objc==4 ){
      pArray = 0;
      pScript = objv[3];
    }else{
      pArray = objv[3];
      if( Tcl_GetString(pArray)[0]==0 ) pArray = 0;
      pScript = objv[4];
    }

    zSql = Tcl_GetStringFromObj(objv[2], 0);
    while( zSql[0] ){
      int i;      /* Loop counter */
      int nVar;   /* Number of wildcards in the SQL */
      int nCol;   /* Number of columns in the result set */
      Tcl_Obj **apColName = 0;   /* Array of column names */
  
      /* Compile a single SQL statement */
      if( SQLITE_OK!=sqlite3_prepare(pDb->db, zSql, -1, &pStmt, &zLeft) ){
        Tcl_SetObjResult(interp, dbTextToObj(sqlite3_errmsg(pDb->db)));
        rc = TCL_ERROR;
        break;
      }
      if( pStmt==0 ){
        if( SQLITE_OK!=sqlite3_errcode(pDb->db) ){
          Tcl_SetObjResult(interp, dbTextToObj(sqlite3_errmsg(pDb->db)));
          rc = TCL_ERROR;
          break;
        }else{
          zSql = zLeft;
          continue;
        }
      }

      /* Bind values to wildcards that begin with $ */  
      nVar = sqlite3_bind_parameter_count(pStmt);
      for(i=1; i<=nVar; i++){
        const char *zVar = sqlite3_bind_parameter_name(pStmt, i);
        if( zVar[0]=='$' ){
          Tcl_Obj *pVar = Tcl_GetVar2Ex(interp, &zVar[1], 0, 0);
          if( pVar ){
            int n;
            u8 *data;
            char *zType = pVar->typePtr ? pVar->typePtr->name : "";
            char c = zType[0];
            if( c=='b' && strcmp(zType,"bytearray")==0 ){
              data = Tcl_GetByteArrayFromObj(pVar, &n);
              sqlite3_bind_blob(pStmt, i, data, n, SQLITE_STATIC);
            }else if( (c=='b' && strcmp(zType,"boolean")==0) ||
                  (c=='i' && strcmp(zType,"int")==0) ){
              Tcl_GetIntFromObj(interp, pVar, &n);
              sqlite3_bind_int(pStmt, i, n);
            }else if( c=='d' && strcmp(zType,"double")==0 ){
              double r;
              Tcl_GetDoubleFromObj(interp, pVar, &r);
              sqlite3_bind_double(pStmt, i, r);
            }else{
              data = Tcl_GetStringFromObj(pVar, &n);
              sqlite3_bind_text(pStmt, i, data, n, SQLITE_STATIC);
            }
          }
        }
      }
   

      /* Compute column names */
      nCol = sqlite3_column_count(pStmt);
      if( pScript ){
        apColName = (Tcl_Obj**)Tcl_Alloc( sizeof(Tcl_Obj*)*nCol );
        if( apColName==0 ) break;
        for(i=0; i<nCol; i++){
          apColName[i] = dbTextToObj(sqlite3_column_name(pStmt,i));
          Tcl_IncrRefCount(apColName[i]);
        }
      }

      /* If results are being stored in an array variable, then create
      ** the array(*) entry for that array
      */
      if( pArray ){
        Tcl_Obj *pColList = Tcl_NewObj();
        Tcl_IncrRefCount(pColList);
        for(i=0; i<nCol; i++){

          Tcl_ListObjAppendElement(interp, pColList, apColName[i]);


        }
        Tcl_ObjSetVar2(interp, pArray, Tcl_NewStringObj("*",-1), pColList,0);
      }

      /* Execute the SQL
      */
      while( pStmt && SQLITE_ROW==sqlite3_step(pStmt) ){
        for(i=0; i<nCol; i++){
          Tcl_Obj *pVal;
          
          /* Set pVal to contain the i'th column of this row. */
          switch( sqlite3_column_type(pStmt, i) ){

            case SQLITE_BLOB: {
              int bytes = sqlite3_column_bytes(pStmt, i);
              pVal = Tcl_NewByteArrayObj(sqlite3_column_blob(pStmt, i), bytes);
              break;
            }
            case SQLITE_INTEGER: {
              sqlite_int64 v = sqlite3_column_int64(pStmt, i);
              if( v>=-2147483647 && v<=2147483647 ){
                pVal = Tcl_NewIntObj(v);
              }else{
                pVal = Tcl_NewWideIntObj(v);
              }
              break;
            }
            case SQLITE_FLOAT: {
              double r = sqlite3_column_double(pStmt, i);
              pVal = Tcl_NewDoubleObj(r);
              break;
            }
            default: {
              pVal = dbTextToObj(sqlite3_column_text(pStmt, i));
              break;
            }
          }
  
          if( pScript ){
            if( pArray==0 ){
              Tcl_ObjSetVar2(interp, apColName[i], 0, pVal, 0);
            }else{
              Tcl_ObjSetVar2(interp, pArray, apColName[i], pVal, 0);
            }

          }else{
            Tcl_ListObjAppendElement(interp, pRet, pVal);
          }
        }
  
        if( pScript ){
          rc = Tcl_EvalObjEx(interp, pScript, 0);
          if( rc!=TCL_ERROR ) rc = TCL_OK;
        }
      }

      /* Free the column name objects */
      if( pScript ){
        for(i=0; i<nCol; i++){
          Tcl_DecrRefCount(apColName[i]);
        }
        Tcl_Free((char*)apColName);
      }

      /* Finalize the statement.  If the result code is SQLITE_SCHEMA, then
      ** try again to execute the same statement
      */
      if( SQLITE_SCHEMA==sqlite3_finalize(pStmt) ){
        continue;
      }
  
      if( SQLITE_OK!=sqlite3_errcode(pDb->db) ){
        Tcl_SetObjResult(interp, dbTextToObj(sqlite3_errmsg(pDb->db)));
        rc = TCL_ERROR;
        break;
      }

      zSql = zLeft;
    }
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
** This Tcl module contains only a single new Tcl command named "sqlite".
** (Hence there is no namespace.  There is no point in using a namespace
** if the extension only supplies one new name!)  The "sqlite" command is
** used to open a new SQLite database.  See the DbMain() routine above
** for additional information.
*/
int Sqlite3_Init(Tcl_Interp *interp){
  Tcl_InitStubs(interp, "8.0", 0);
  Tcl_CreateObjCommand(interp, "sqlite3", (Tcl_ObjCmdProc*)DbMain, 0, 0);
  Tcl_PkgProvide(interp, "sqlite3", "3.0");
  return TCL_OK;
}
int Tclsqlite3_Init(Tcl_Interp *interp){
  Tcl_InitStubs(interp, "8.0", 0);
  Tcl_CreateObjCommand(interp, "sqlite3", (Tcl_ObjCmdProc*)DbMain, 0, 0);
  Tcl_PkgProvide(interp, "sqlite3", "3.0");
  return TCL_OK;
}
int Sqlite3_SafeInit(Tcl_Interp *interp){
  return TCL_OK;
}







|





|







1197
1198
1199
1200
1201
1202
1203
1204
1205
1206
1207
1208
1209
1210
1211
1212
1213
1214
1215
1216
1217
** This Tcl module contains only a single new Tcl command named "sqlite".
** (Hence there is no namespace.  There is no point in using a namespace
** if the extension only supplies one new name!)  The "sqlite" command is
** used to open a new SQLite database.  See the DbMain() routine above
** for additional information.
*/
int Sqlite3_Init(Tcl_Interp *interp){
  Tcl_InitStubs(interp, "8.4", 0);
  Tcl_CreateObjCommand(interp, "sqlite3", (Tcl_ObjCmdProc*)DbMain, 0, 0);
  Tcl_PkgProvide(interp, "sqlite3", "3.0");
  return TCL_OK;
}
int Tclsqlite3_Init(Tcl_Interp *interp){
  Tcl_InitStubs(interp, "8.4", 0);
  Tcl_CreateObjCommand(interp, "sqlite3", (Tcl_ObjCmdProc*)DbMain, 0, 0);
  Tcl_PkgProvide(interp, "sqlite3", "3.0");
  return TCL_OK;
}
int Sqlite3_SafeInit(Tcl_Interp *interp){
  return TCL_OK;
}
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.98 2004/08/20 16:02:39 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.99 2004/08/20 18:34:20 drh Exp $
*/
#include "sqliteInt.h"
#include "tcl.h"
#include "os.h"
#include <stdlib.h>
#include <string.h>

2383
2384
2385
2386
2387
2388
2389

























2390
2391
2392
2393
2394
2395
2396
  if( rc!=SQLITE_OK ){
    Tcl_SetResult(interp, (char *)errorName(rc), TCL_STATIC);
    return TCL_ERROR;
  }
  Tcl_AppendResult(interp, zFile, 0);
  return TCL_OK;
}


























/*
** Register commands with the TCL interpreter.
*/
int Sqlitetest1_Init(Tcl_Interp *interp){
  extern int sqlite3_search_count;
  extern int sqlite3_interrupt_count;







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







2383
2384
2385
2386
2387
2388
2389
2390
2391
2392
2393
2394
2395
2396
2397
2398
2399
2400
2401
2402
2403
2404
2405
2406
2407
2408
2409
2410
2411
2412
2413
2414
2415
2416
2417
2418
2419
2420
2421
  if( rc!=SQLITE_OK ){
    Tcl_SetResult(interp, (char *)errorName(rc), TCL_STATIC);
    return TCL_ERROR;
  }
  Tcl_AppendResult(interp, zFile, 0);
  return TCL_OK;
}

/*
** Usage:  tcl_variable_type VARIABLENAME
**
** Return the name of the internal representation for the
** value of the given variable.
*/
static int tcl_variable_type(
  void * clientData,
  Tcl_Interp *interp,
  int objc,
  Tcl_Obj *CONST objv[]
){
  Tcl_Obj *pVar;
  if( objc!=2 ){
    Tcl_WrongNumArgs(interp, 1, objv, "VARIABLE");
    return TCL_ERROR;
  }
  pVar = Tcl_GetVar2Ex(interp, Tcl_GetString(objv[1]), 0, TCL_LEAVE_ERR_MSG);
  if( pVar==0 ) return TCL_ERROR;
  if( pVar->typePtr ){
    Tcl_SetObjResult(interp, Tcl_NewStringObj(pVar->typePtr->name, -1));
  }
  return TCL_OK;
}

/*
** Register commands with the TCL interpreter.
*/
int Sqlitetest1_Init(Tcl_Interp *interp){
  extern int sqlite3_search_count;
  extern int sqlite3_interrupt_count;
2478
2479
2480
2481
2482
2483
2484

2485
2486
2487
2488
2489
2490
2491
     /* Custom test interfaces */
     { "sqlite3OsUnlock",         test_sqlite3OsUnlock, 0    },
     { "add_test_collate",        test_collate, 0            },
     { "add_test_collate_needed", test_collate_needed, 0     },
     { "add_test_function",       test_function, 0           },
     { "sqlite3_crashparams",     sqlite3_crashparams, 0     },
     { "sqlite3_test_errstr",     test_errstr, 0             },


  };
  int i;
  extern int sqlite3_os_trace;

  for(i=0; i<sizeof(aCmd)/sizeof(aCmd[0]); i++){
    Tcl_CreateCommand(interp, aCmd[i].zName, aCmd[i].xProc, 0, 0);







>







2503
2504
2505
2506
2507
2508
2509
2510
2511
2512
2513
2514
2515
2516
2517
     /* Custom test interfaces */
     { "sqlite3OsUnlock",         test_sqlite3OsUnlock, 0    },
     { "add_test_collate",        test_collate, 0            },
     { "add_test_collate_needed", test_collate_needed, 0     },
     { "add_test_function",       test_function, 0           },
     { "sqlite3_crashparams",     sqlite3_crashparams, 0     },
     { "sqlite3_test_errstr",     test_errstr, 0             },
     { "tcl_variable_type",       tcl_variable_type, 0       },

  };
  int i;
  extern int sqlite3_os_trace;

  for(i=0; i<sizeof(aCmd)/sizeof(aCmd[0]); i++){
    Tcl_CreateCommand(interp, aCmd[i].zName, aCmd[i].xProc, 0, 0);
Changes to src/vdbeapi.c.
516
517
518
519
520
521
522
523

524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
}

/*
** Return the number of wildcards that can be potentially bound to.
** This routine is added to support DBD::SQLite.  
*/
int sqlite3_bind_parameter_count(sqlite3_stmt *pStmt){
  return ((Vdbe*)pStmt)->nVar;

}

/*
** Return the name of a wildcard parameter.  Return NULL if the index
** is out of range or if the wildcard is unnamed.
**
** The result is always UTF-8.
*/
const char *sqlite3_bind_parameter_name(sqlite3_stmt *pStmt, int i){
  Vdbe *p = (Vdbe*)pStmt;
  if( i<1 || i>p->nVar ){
    return 0;
  }
  if( !p->okVar ){
    int j;
    Op *pOp;
    for(j=0, pOp=p->aOp; j<p->nOp; j++, pOp++){
      if( pOp->opcode==OP_Variable ){







|
>










|







516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
}

/*
** Return the number of wildcards that can be potentially bound to.
** This routine is added to support DBD::SQLite.  
*/
int sqlite3_bind_parameter_count(sqlite3_stmt *pStmt){
  Vdbe *p = (Vdbe*)pStmt;
  return p ? p->nVar : 0;
}

/*
** Return the name of a wildcard parameter.  Return NULL if the index
** is out of range or if the wildcard is unnamed.
**
** The result is always UTF-8.
*/
const char *sqlite3_bind_parameter_name(sqlite3_stmt *pStmt, int i){
  Vdbe *p = (Vdbe*)pStmt;
  if( p==0 || i<1 || i>p->nVar ){
    return 0;
  }
  if( !p->okVar ){
    int j;
    Op *pOp;
    for(j=0, pOp=p->aOp; j<p->nOp; j++, pOp++){
      if( pOp->opcode==OP_Variable ){
Changes to test/bind.test.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 2003 September 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 implements regression tests for SQLite library.  The
# focus of this script testing the sqlite_bind API.
#
# $Id: bind.test,v 1.16 2004/08/20 16:02:39 drh Exp $
#

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

proc sqlite_step {stmt N VALS COLS} {
  upvar VALS vals













|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 2003 September 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 implements regression tests for SQLite library.  The
# focus of this script testing the sqlite_bind API.
#
# $Id: bind.test,v 1.17 2004/08/20 18:34:20 drh Exp $
#

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

proc sqlite_step {stmt N VALS COLS} {
  upvar VALS vals
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
do_test bind-4.1 {
  sqlite3_bind_double $VM 1 1234.1234
  sqlite3_bind_double $VM 2 0.00001
  sqlite3_bind_double $VM 3 123456789
  sqlite_step $VM N VALUES COLNAMES
  sqlite3_reset $VM
  execsql {SELECT rowid, * FROM t1}
} {1 1234.1234 1e-05 123456789}
do_test bind-4.2 {
  execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1}
} {real real real}
do_test bind-4.3 {
  execsql {
    DELETE FROM t1;
  }







|







163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
do_test bind-4.1 {
  sqlite3_bind_double $VM 1 1234.1234
  sqlite3_bind_double $VM 2 0.00001
  sqlite3_bind_double $VM 3 123456789
  sqlite_step $VM N VALUES COLNAMES
  sqlite3_reset $VM
  execsql {SELECT rowid, * FROM t1}
} {1 1234.1234 1e-05 123456789.0}
do_test bind-4.2 {
  execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1}
} {real real real}
do_test bind-4.3 {
  execsql {
    DELETE FROM t1;
  }
Changes to test/collate1.test.
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
#    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 script is page cache subsystem.
#
# $Id: collate1.test,v 1.2 2004/06/11 10:51:41 danielk1977 Exp $

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

#
# Tests are roughly organised as follows:
#







|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
#    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 script is page cache subsystem.
#
# $Id: collate1.test,v 1.3 2004/08/20 18:34:20 drh Exp $

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

#
# Tests are roughly organised as follows:
#
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
    SELECT c2 FROM collate1t1 ORDER BY 1;
  }
} {{} 1 101 12}
do_test collate1-4.3 {
  execsql {
    SELECT c2+0 FROM collate1t1 ORDER BY 1;
  }
} {{} 1 12 101}
do_test collate1-4.4 {
  execsql {
    SELECT c1||'' FROM collate1t1 ORDER BY 1;
  }
} {{} 1 101 12}
do_test collate1-4.5 {
  execsql {
    DROP TABLE collate1t1;
  }
} {}

finish_test







|












210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
    SELECT c2 FROM collate1t1 ORDER BY 1;
  }
} {{} 1 101 12}
do_test collate1-4.3 {
  execsql {
    SELECT c2+0 FROM collate1t1 ORDER BY 1;
  }
} {{} 1.0 12.0 101.0}
do_test collate1-4.4 {
  execsql {
    SELECT c1||'' FROM collate1t1 ORDER BY 1;
  }
} {{} 1 101 12}
do_test collate1-4.5 {
  execsql {
    DROP TABLE collate1t1;
  }
} {}

finish_test
Changes to test/date.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
# 2003 October 31
#
# 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 date and time functions.
#
# $Id: date.test,v 1.8 2004/07/18 22:22:44 drh Exp $

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

proc datetest {tnum expr result} {
  do_test date-$tnum [subst {
    execsql "SELECT coalesce($expr,'NULL')"
  }] [list $result]
}

datetest 1.1 julianday('2000-01-01') 2451544.5
datetest 1.2 julianday('1970-01-01') 2440587.5
datetest 1.3 julianday('1910-04-20') 2418781.5
datetest 1.4 julianday('1986-02-09') 2446470.5
datetest 1.5 julianday('12:00:00') 2451545
datetest 1.6 {julianday('2000-01-01 12:00:00')} 2451545
datetest 1.7 {julianday('2000-01-01 12:00')} 2451545
datetest 1.8 julianday('bogus') NULL
datetest 1.9 julianday('1999-12-31') 2451543.5
datetest 1.10 julianday('1999-12-32') NULL
datetest 1.11 julianday('1999-13-01') NULL
datetest 1.12 julianday('2003-02-31') 2452701.5
datetest 1.13 julianday('2003-03-03') 2452701.5
datetest 1.14 julianday('+2000-01-01') NULL
datetest 1.15 julianday('200-01-01') NULL
datetest 1.16 julianday('2000-1-01') NULL
datetest 1.17 julianday('2000-01-1') NULL
datetest 1.18 {julianday('2000-01-01     12:00:00')} 2451545
datetest 1.19 {julianday('2000-01-01 12:00:00.1')}   2451545.00000116
datetest 1.20 {julianday('2000-01-01 12:00:00.01')}  2451545.00000012
datetest 1.21 {julianday('2000-01-01 12:00:00.001')} 2451545.00000001
datetest 1.22 {julianday('2000-01-01 12:00:00.')} NULL
datetest 1.23 julianday(12345.6) 12345.6
datetest 1.24 {julianday('2001-01-01 12:00:00 bogus')} NULL
datetest 1.25 {julianday('2001-01-01 bogus')} NULL













|









|




|
|
|










|







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
# 2003 October 31
#
# 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 date and time functions.
#
# $Id: date.test,v 1.9 2004/08/20 18:34:20 drh Exp $

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

proc datetest {tnum expr result} {
  do_test date-$tnum [subst {
    execsql "SELECT coalesce($expr,'NULL')"
  }] [list $result]
}
set tcl_precision 15
datetest 1.1 julianday('2000-01-01') 2451544.5
datetest 1.2 julianday('1970-01-01') 2440587.5
datetest 1.3 julianday('1910-04-20') 2418781.5
datetest 1.4 julianday('1986-02-09') 2446470.5
datetest 1.5 julianday('12:00:00') 2451545.0
datetest 1.6 {julianday('2000-01-01 12:00:00')} 2451545.0
datetest 1.7 {julianday('2000-01-01 12:00')} 2451545.0
datetest 1.8 julianday('bogus') NULL
datetest 1.9 julianday('1999-12-31') 2451543.5
datetest 1.10 julianday('1999-12-32') NULL
datetest 1.11 julianday('1999-13-01') NULL
datetest 1.12 julianday('2003-02-31') 2452701.5
datetest 1.13 julianday('2003-03-03') 2452701.5
datetest 1.14 julianday('+2000-01-01') NULL
datetest 1.15 julianday('200-01-01') NULL
datetest 1.16 julianday('2000-1-01') NULL
datetest 1.17 julianday('2000-01-1') NULL
datetest 1.18 {julianday('2000-01-01     12:00:00')} 2451545.0
datetest 1.19 {julianday('2000-01-01 12:00:00.1')}   2451545.00000116
datetest 1.20 {julianday('2000-01-01 12:00:00.01')}  2451545.00000012
datetest 1.21 {julianday('2000-01-01 12:00:00.001')} 2451545.00000001
datetest 1.22 {julianday('2000-01-01 12:00:00.')} NULL
datetest 1.23 julianday(12345.6) 12345.6
datetest 1.24 {julianday('2001-01-01 12:00:00 bogus')} NULL
datetest 1.25 {julianday('2001-01-01 bogus')} NULL
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
datetest 8.17 {datetime('now','+10.5 minutes')} {2003-10-22 12:44:30}
datetest 8.18 {datetime('now','-1.25 hours')} {2003-10-22 11:19:00}
datetest 8.19 {datetime('now','11.25 seconds')} {2003-10-22 12:34:11}
set sqlite_current_time 0

# Negative years work.  Example:  '-4713-11-26' is JD 1.5.
#
datetest 9.1 {julianday('-4713-11-24 12:00:00')} {0}
datetest 9.2 {julianday(datetime(5))} {5}
datetest 9.3 {julianday(datetime(10))} {10}
datetest 9.4 {julianday(datetime(100))} {100}
datetest 9.5 {julianday(datetime(1000))} {1000}
datetest 9.6 {julianday(datetime(10000))} {10000}
datetest 9.7 {julianday(datetime(100000))} {100000}

# datetime() with just an HH:MM:SS correctly inserts the date 2000-01-01.
#
datetest 10.1 {datetime('01:02:03')}  {2000-01-01 01:02:03}
datetest 10.2 {date('01:02:03')}  {2000-01-01}
datetest 10.3 {strftime('%Y-%m-%d %H:%M','01:02:03')} {2000-01-01 01:02}








|
|
|
|
|
|
|







212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
datetest 8.17 {datetime('now','+10.5 minutes')} {2003-10-22 12:44:30}
datetest 8.18 {datetime('now','-1.25 hours')} {2003-10-22 11:19:00}
datetest 8.19 {datetime('now','11.25 seconds')} {2003-10-22 12:34:11}
set sqlite_current_time 0

# Negative years work.  Example:  '-4713-11-26' is JD 1.5.
#
datetest 9.1 {julianday('-4713-11-24 12:00:00')} {0.0}
datetest 9.2 {julianday(datetime(5))} {5.0}
datetest 9.3 {julianday(datetime(10))} {10.0}
datetest 9.4 {julianday(datetime(100))} {100.0}
datetest 9.5 {julianday(datetime(1000))} {1000.0}
datetest 9.6 {julianday(datetime(10000))} {10000.0}
datetest 9.7 {julianday(datetime(100000))} {100000.0}

# datetime() with just an HH:MM:SS correctly inserts the date 2000-01-01.
#
datetest 10.1 {datetime('01:02:03')}  {2000-01-01 01:02:03}
datetest 10.2 {date('01:02:03')}  {2000-01-01}
datetest 10.3 {strftime('%Y-%m-%d %H:%M','01:02:03')} {2000-01-01 01:02}

Changes to test/expr.test.
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.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing expressions.
#
# $Id: expr.test,v 1.35 2004/06/19 00:16:31 drh 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)}













|







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.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing expressions.
#
# $Id: expr.test,v 1.36 2004/08/20 18:34:20 drh 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)}
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
test_expr expr-1.15 {i1=20, i2=20} {i2<=i1} 1
test_expr expr-1.16 {i1=20, i2=20} {i2>i1} 0
test_expr expr-1.17 {i1=20, i2=20} {i2>=i1} 1
test_expr expr-1.18 {i1=20, i2=20} {i2!=i1} 0
test_expr expr-1.19 {i1=20, i2=20} {i2=i1} 1
test_expr expr-1.20 {i1=20, i2=20} {i2<>i1} 0
test_expr expr-1.21 {i1=20, i2=20} {i2==i1} 1
test_expr expr-1.22 {i1=1, i2=2, r1=3.0} {i1+i2*r1} {7}
test_expr expr-1.23 {i1=1, i2=2, r1=3.0} {(i1+i2)*r1} {9}
test_expr expr-1.24 {i1=1, i2=2} {min(i1,i2,i1+i2,i1-i2)} {-1}
test_expr expr-1.25 {i1=1, i2=2} {max(i1,i2,i1+i2,i1-i2)} {3}
test_expr expr-1.26 {i1=1, i2=2} {max(i1,i2,i1+i2,i1-i2)} {3}
test_expr expr-1.27 {i1=1, i2=2} {i1==1 AND i2=2} {1}
test_expr expr-1.28 {i1=1, i2=2} {i1=2 AND i2=1} {0}
test_expr expr-1.29 {i1=1, i2=2} {i1=1 AND i2=1} {0}
test_expr expr-1.30 {i1=1, i2=2} {i1=2 AND i2=2} {0}







|
|







44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
test_expr expr-1.15 {i1=20, i2=20} {i2<=i1} 1
test_expr expr-1.16 {i1=20, i2=20} {i2>i1} 0
test_expr expr-1.17 {i1=20, i2=20} {i2>=i1} 1
test_expr expr-1.18 {i1=20, i2=20} {i2!=i1} 0
test_expr expr-1.19 {i1=20, i2=20} {i2=i1} 1
test_expr expr-1.20 {i1=20, i2=20} {i2<>i1} 0
test_expr expr-1.21 {i1=20, i2=20} {i2==i1} 1
test_expr expr-1.22 {i1=1, i2=2, r1=3.0} {i1+i2*r1} {7.0}
test_expr expr-1.23 {i1=1, i2=2, r1=3.0} {(i1+i2)*r1} {9.0}
test_expr expr-1.24 {i1=1, i2=2} {min(i1,i2,i1+i2,i1-i2)} {-1}
test_expr expr-1.25 {i1=1, i2=2} {max(i1,i2,i1+i2,i1-i2)} {3}
test_expr expr-1.26 {i1=1, i2=2} {max(i1,i2,i1+i2,i1-i2)} {3}
test_expr expr-1.27 {i1=1, i2=2} {i1==1 AND i2=2} {1}
test_expr expr-1.28 {i1=1, i2=2} {i1=2 AND i2=1} {0}
test_expr expr-1.29 {i1=1, i2=2} {i1=1 AND i2=1} {0}
test_expr expr-1.30 {i1=1, i2=2} {i1=2 AND i2=2} {0}
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
test_expr expr-1.99 {i1=32, i2=NULL} {coalesce(i1&i2,99)} 99
test_expr expr-1.100 {i1=1, i2=''} {i1=i2} 0
test_expr expr-1.101 {i1=0, i2=''} {i1=i2} 0

test_expr expr-2.1 {r1=1.23, r2=2.34} {r1+r2} 3.57
test_expr expr-2.2 {r1=1.23, r2=2.34} {r1-r2} -1.11
test_expr expr-2.3 {r1=1.23, r2=2.34} {r1*r2} 2.8782

test_expr expr-2.4 {r1=1.23, r2=2.34} {r1/r2} 0.525641025641026
test_expr expr-2.5 {r1=1.23, r2=2.34} {r2/r1} 1.90243902439024
test_expr expr-2.6 {r1=1.23, r2=2.34} {r2<r1} 0
test_expr expr-2.7 {r1=1.23, r2=2.34} {r2<=r1} 0
test_expr expr-2.8 {r1=1.23, r2=2.34} {r2>r1} 1
test_expr expr-2.9 {r1=1.23, r2=2.34} {r2>=r1} 1
test_expr expr-2.10 {r1=1.23, r2=2.34} {r2!=r1} 1
test_expr expr-2.11 {r1=1.23, r2=2.34} {r2=r1} 0
test_expr expr-2.12 {r1=1.23, r2=2.34} {r2<>r1} 1
test_expr expr-2.13 {r1=1.23, r2=2.34} {r2==r1} 0
test_expr expr-2.14 {r1=2.34, r2=2.34} {r2<r1} 0
test_expr expr-2.15 {r1=2.34, r2=2.34} {r2<=r1} 1
test_expr expr-2.16 {r1=2.34, r2=2.34} {r2>r1} 0
test_expr expr-2.17 {r1=2.34, r2=2.34} {r2>=r1} 1
test_expr expr-2.18 {r1=2.34, r2=2.34} {r2!=r1} 0
test_expr expr-2.19 {r1=2.34, r2=2.34} {r2=r1} 1
test_expr expr-2.20 {r1=2.34, r2=2.34} {r2<>r1} 0
test_expr expr-2.21 {r1=2.34, r2=2.34} {r2==r1} 1
test_expr expr-2.22 {r1=1.23, r2=2.34} {min(r1,r2,r1+r2,r1-r2)} {-1.11}
test_expr expr-2.23 {r1=1.23, r2=2.34} {max(r1,r2,r1+r2,r1-r2)} {3.57}
test_expr expr-2.24 {r1=25.0, r2=11.0} {r1%r2} 3
test_expr expr-2.25 {r1=1.23, r2=NULL} {coalesce(r1+r2,99.0)} 99.0

test_expr expr-3.1 {t1='abc', t2='xyz'} {t1<t2} 1
test_expr expr-3.2 {t1='xyz', t2='abc'} {t1<t2} 0
test_expr expr-3.3 {t1='abc', t2='abc'} {t1<t2} 0
test_expr expr-3.4 {t1='abc', t2='xyz'} {t1<=t2} 1
test_expr expr-3.5 {t1='xyz', t2='abc'} {t1<=t2} 0







>




















|







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
test_expr expr-1.99 {i1=32, i2=NULL} {coalesce(i1&i2,99)} 99
test_expr expr-1.100 {i1=1, i2=''} {i1=i2} 0
test_expr expr-1.101 {i1=0, i2=''} {i1=i2} 0

test_expr expr-2.1 {r1=1.23, r2=2.34} {r1+r2} 3.57
test_expr expr-2.2 {r1=1.23, r2=2.34} {r1-r2} -1.11
test_expr expr-2.3 {r1=1.23, r2=2.34} {r1*r2} 2.8782
set tcl_precision 15
test_expr expr-2.4 {r1=1.23, r2=2.34} {r1/r2} 0.525641025641026
test_expr expr-2.5 {r1=1.23, r2=2.34} {r2/r1} 1.90243902439024
test_expr expr-2.6 {r1=1.23, r2=2.34} {r2<r1} 0
test_expr expr-2.7 {r1=1.23, r2=2.34} {r2<=r1} 0
test_expr expr-2.8 {r1=1.23, r2=2.34} {r2>r1} 1
test_expr expr-2.9 {r1=1.23, r2=2.34} {r2>=r1} 1
test_expr expr-2.10 {r1=1.23, r2=2.34} {r2!=r1} 1
test_expr expr-2.11 {r1=1.23, r2=2.34} {r2=r1} 0
test_expr expr-2.12 {r1=1.23, r2=2.34} {r2<>r1} 1
test_expr expr-2.13 {r1=1.23, r2=2.34} {r2==r1} 0
test_expr expr-2.14 {r1=2.34, r2=2.34} {r2<r1} 0
test_expr expr-2.15 {r1=2.34, r2=2.34} {r2<=r1} 1
test_expr expr-2.16 {r1=2.34, r2=2.34} {r2>r1} 0
test_expr expr-2.17 {r1=2.34, r2=2.34} {r2>=r1} 1
test_expr expr-2.18 {r1=2.34, r2=2.34} {r2!=r1} 0
test_expr expr-2.19 {r1=2.34, r2=2.34} {r2=r1} 1
test_expr expr-2.20 {r1=2.34, r2=2.34} {r2<>r1} 0
test_expr expr-2.21 {r1=2.34, r2=2.34} {r2==r1} 1
test_expr expr-2.22 {r1=1.23, r2=2.34} {min(r1,r2,r1+r2,r1-r2)} {-1.11}
test_expr expr-2.23 {r1=1.23, r2=2.34} {max(r1,r2,r1+r2,r1-r2)} {3.57}
test_expr expr-2.24 {r1=25.0, r2=11.0} {r1%r2} 3.0
test_expr expr-2.25 {r1=1.23, r2=NULL} {coalesce(r1+r2,99.0)} 99.0

test_expr expr-3.1 {t1='abc', t2='xyz'} {t1<t2} 1
test_expr expr-3.2 {t1='xyz', t2='abc'} {t1<t2} 0
test_expr expr-3.3 {t1='abc', t2='abc'} {t1<t2} 0
test_expr expr-3.4 {t1='abc', t2='xyz'} {t1<=t2} 1
test_expr expr-3.5 {t1='xyz', t2='abc'} {t1<=t2} 0
Changes to test/func.test.
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.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing built-in functions.
#
# $Id: func.test,v 1.28 2004/07/18 20:52:32 drh Exp $

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

# Create a table to work with.
#
do_test func-0.0 {













|







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.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing built-in functions.
#
# $Id: func.test,v 1.29 2004/08/20 18:34:20 drh Exp $

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

# Create a table to work with.
#
do_test func-0.0 {
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
  catchsql {SELECT abs(c) FROM t1 ORDER BY a}
} {0 {3 12345.6789 5}}
do_test func-4.4.1 {
  execsql {SELECT abs(a) FROM t2}
} {1 {} 345 {} 67890}
do_test func-4.4.2 {
  execsql {SELECT abs(t1) FROM tbl1}
} {0 0 0 0 0}

do_test func-4.5 {
  catchsql {SELECT round(a,b,c) FROM t1}
} {1 {wrong number of arguments to function round()}}
do_test func-4.6 {
  catchsql {SELECT round(b,2) FROM t1 ORDER BY b}
} {0 {-2.00 1.23 2.00}}







|







168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
  catchsql {SELECT abs(c) FROM t1 ORDER BY a}
} {0 {3 12345.6789 5}}
do_test func-4.4.1 {
  execsql {SELECT abs(a) FROM t2}
} {1 {} 345 {} 67890}
do_test func-4.4.2 {
  execsql {SELECT abs(t1) FROM tbl1}
} {0.0 0.0 0.0 0.0 0.0}

do_test func-4.5 {
  catchsql {SELECT round(a,b,c) FROM t1}
} {1 {wrong number of arguments to function round()}}
do_test func-4.6 {
  catchsql {SELECT round(b,2) FROM t1 ORDER BY b}
} {0 {-2.00 1.23 2.00}}
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261

# Tests for aggregate functions and how they handle NULLs.
#
do_test func-8.1 {
  execsql {
    SELECT sum(a), count(a), round(avg(a),2), min(a), max(a), count(*) FROM t2;
  }
} {68236 3 22745.33 1 67890 5}
do_test func-8.2 {
  execsql {
    SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t2;
  }
} {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
do_test func-8.3 {
  execsql {







|







247
248
249
250
251
252
253
254
255
256
257
258
259
260
261

# Tests for aggregate functions and how they handle NULLs.
#
do_test func-8.1 {
  execsql {
    SELECT sum(a), count(a), round(avg(a),2), min(a), max(a), count(*) FROM t2;
  }
} {68236.0 3 22745.33 1 67890 5}
do_test func-8.2 {
  execsql {
    SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t2;
  }
} {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
do_test func-8.3 {
  execsql {
Changes to test/index.test.
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.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the CREATE INDEX statement.
#
# $Id: index.test,v 1.31 2004/07/20 00:52:44 drh Exp $

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

# Create a basic index and verify it is added to sqlite_master
#
do_test index-1.1 {













|







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.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the CREATE INDEX statement.
#
# $Id: index.test,v 1.32 2004/08/20 18:34:20 drh Exp $

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

# Create a basic index and verify it is added to sqlite_master
#
do_test index-1.1 {
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
    INSERT INTO t4 VALUES('abc',3);
    INSERT INTO t4 VALUES('-1.0',4);
    INSERT INTO t4 VALUES('+1.0',5);
    INSERT INTO t4 VALUES('0',6);
    INSERT INTO t4 VALUES('00000',7);
    SELECT a FROM t4 ORDER BY b;
  }
} {0 0 abc -1 1 0 0}
do_test index-12.2 {
  execsql {
    SELECT a FROM t4 WHERE a==0 ORDER BY b
  }
} {0 0 0 0}
do_test index-12.3 {
  execsql {
    SELECT a FROM t4 WHERE a<0.5 ORDER BY b
  }
} {0 0 -1 0 0}
do_test index-12.4 {
  execsql {
    SELECT a FROM t4 WHERE a>-0.5 ORDER BY b
  }
} {0 0 abc 1 0 0}
do_test index-12.5 {
  execsql {
    CREATE INDEX t4i1 ON t4(a);
    SELECT a FROM t4 WHERE a==0 ORDER BY b
  }
} {0 0 0 0}
do_test index-12.6 {
  execsql {
    SELECT a FROM t4 WHERE a<0.5 ORDER BY b
  }
} {0 0 -1 0 0}
do_test index-12.7 {
  execsql {
    SELECT a FROM t4 WHERE a>-0.5 ORDER BY b
  }
} {0 0 abc 1 0 0}
integrity_check index-12.8

# Make sure we cannot drop an automatically created index.
#
do_test index-13.1 {
  execsql {
   CREATE TABLE t5(







|




|




|




|





|




|




|







355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
    INSERT INTO t4 VALUES('abc',3);
    INSERT INTO t4 VALUES('-1.0',4);
    INSERT INTO t4 VALUES('+1.0',5);
    INSERT INTO t4 VALUES('0',6);
    INSERT INTO t4 VALUES('00000',7);
    SELECT a FROM t4 ORDER BY b;
  }
} {0.0 0.0 abc -1.0 1.0 0 0}
do_test index-12.2 {
  execsql {
    SELECT a FROM t4 WHERE a==0 ORDER BY b
  }
} {0.0 0.0 0 0}
do_test index-12.3 {
  execsql {
    SELECT a FROM t4 WHERE a<0.5 ORDER BY b
  }
} {0.0 0.0 -1.0 0 0}
do_test index-12.4 {
  execsql {
    SELECT a FROM t4 WHERE a>-0.5 ORDER BY b
  }
} {0.0 0.0 abc 1.0 0 0}
do_test index-12.5 {
  execsql {
    CREATE INDEX t4i1 ON t4(a);
    SELECT a FROM t4 WHERE a==0 ORDER BY b
  }
} {0.0 0.0 0 0}
do_test index-12.6 {
  execsql {
    SELECT a FROM t4 WHERE a<0.5 ORDER BY b
  }
} {0.0 0.0 -1.0 0 0}
do_test index-12.7 {
  execsql {
    SELECT a FROM t4 WHERE a>-0.5 ORDER BY b
  }
} {0.0 0.0 abc 1.0 0 0}
integrity_check index-12.8

# Make sure we cannot drop an automatically created index.
#
do_test index-13.1 {
  execsql {
   CREATE TABLE t5(
Changes to test/limit.test.
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
#    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 LIMIT ... OFFSET ... clause
#  of SELECT statements.
#
# $Id: limit.test,v 1.15 2004/07/19 23:38:11 drh Exp $

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

# Build some test data
#
execsql {







|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
#    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 LIMIT ... OFFSET ... clause
#  of SELECT statements.
#
# $Id: limit.test,v 1.16 2004/08/20 18:34:20 drh Exp $

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

# Build some test data
#
execsql {
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
do_test limit-5.5 {
  execsql {
    DELETE FROM t5;
    INSERT INTO t5 SELECT a.x*100+b.x, a.y*100+b.y FROM t1 AS a, t1 AS b
                   ORDER BY 1, 2 LIMIT 1000;
    SELECT count(*), sum(x), sum(y), min(x), max(x), min(y), max(y) FROM t5;
  }
} {1000 1528204 593161 0 3107 505 1005}

# There is some contraversy about whether LIMIT 0 should be the same as
# no limit at all or if LIMIT 0 should result in zero output rows.
#
do_test limit-6.1 {
  execsql {
    BEGIN;







|







158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
do_test limit-5.5 {
  execsql {
    DELETE FROM t5;
    INSERT INTO t5 SELECT a.x*100+b.x, a.y*100+b.y FROM t1 AS a, t1 AS b
                   ORDER BY 1, 2 LIMIT 1000;
    SELECT count(*), sum(x), sum(y), min(x), max(x), min(y), max(y) FROM t5;
  }
} {1000 1528204.0 593161.0 0 3107 505 1005}

# There is some contraversy about whether LIMIT 0 should be the same as
# no limit at all or if LIMIT 0 should result in zero output rows.
#
do_test limit-6.1 {
  execsql {
    BEGIN;
Changes to test/main.test.
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.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is exercising the code in main.c.
#
# $Id: main.test,v 1.15 2004/06/19 00:16:31 drh Exp $

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

# Tests of the sqlite_complete() function.
#
do_test main-1.1 {













|







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.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is exercising the code in main.c.
#
# $Id: main.test,v 1.16 2004/08/20 18:34:20 drh Exp $

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

# Tests of the sqlite_complete() function.
#
do_test main-1.1 {
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
    insert into T1 values(-5.1e-2);
    insert into T1 values(0.5e2);
    insert into T1 values(0.5E+02);
    insert into T1 values(5E+02);
    insert into T1 values(5.0E+03);
    select x*10 from T1 order by x*5;
  }
} {-0.51 -0.5 0.05 0.5 5 500 500 500 5000 50000}
do_test main-3.4 {
  set v [catch {execsql {create bogus}} msg]
  lappend v $msg
} {1 {near "bogus": syntax error}}
do_test main-3.5 {
  set v [catch {execsql {create}} msg]
  lappend v $msg
} {1 {near "create": syntax error}}

finish_test







|










280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
    insert into T1 values(-5.1e-2);
    insert into T1 values(0.5e2);
    insert into T1 values(0.5E+02);
    insert into T1 values(5E+02);
    insert into T1 values(5.0E+03);
    select x*10 from T1 order by x*5;
  }
} {-0.51 -0.5 0.05 0.5 5.0 500.0 500.0 500.0 5000.0 50000.0}
do_test main-3.4 {
  set v [catch {execsql {create bogus}} msg]
  lappend v $msg
} {1 {near "bogus": syntax error}}
do_test main-3.5 {
  set v [catch {execsql {create}} msg]
  lappend v $msg
} {1 {near "create": syntax error}}

finish_test
Changes to test/minmax.test.
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing SELECT statements that contain
# aggregate min() and max() functions and which are handled as
# as a special case.
#
# $Id: minmax.test,v 1.12 2004/07/18 21:33:02 drh Exp $

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

do_test minmax-1.0 {
  execsql {
    BEGIN;







|







9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing SELECT statements that contain
# aggregate min() and max() functions and which are handled as
# as a special case.
#
# $Id: minmax.test,v 1.13 2004/08/20 18:34:20 drh Exp $

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

do_test minmax-1.0 {
  execsql {
    BEGIN;
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
} {1 20}
do_test minmax-4.2 {
  execsql {
    SELECT y, sum(x) FROM
      (SELECT null, y+1 FROM t1 UNION SELECT * FROM t1)
    GROUP BY y ORDER BY y;
  }
} {1 1 2 5 3 22 4 92 5 90 6 0}
do_test minmax-4.3 {
  execsql {
    SELECT y, count(x), count(*) FROM
      (SELECT null, y+1 FROM t1 UNION SELECT * FROM t1)
    GROUP BY y ORDER BY y;
  }
} {1 1 1 2 2 3 3 4 5 4 8 9 5 5 6 6 0 1}







|







130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
} {1 20}
do_test minmax-4.2 {
  execsql {
    SELECT y, sum(x) FROM
      (SELECT null, y+1 FROM t1 UNION SELECT * FROM t1)
    GROUP BY y ORDER BY y;
  }
} {1 1.0 2 5.0 3 22.0 4 92.0 5 90.0 6 0.0}
do_test minmax-4.3 {
  execsql {
    SELECT y, count(x), count(*) FROM
      (SELECT null, y+1 FROM t1 UNION SELECT * FROM t1)
    GROUP BY y ORDER BY y;
  }
} {1 1 1 2 2 3 3 4 5 4 8 9 5 5 6 6 0 1}
Changes to test/misc1.test.
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for miscellanous features that were
# left out of other test files.
#
# $Id: misc1.test,v 1.29 2004/07/19 00:39:46 drh Exp $

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

# Mimic the SQLite 2 collation type NUMERIC.
db collate numeric numeric_collate
proc numeric_collate {lhs rhs} {







|







9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for miscellanous features that were
# left out of other test files.
#
# $Id: misc1.test,v 1.30 2004/08/20 18:34:20 drh Exp $

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

# Mimic the SQLite 2 collation type NUMERIC.
db collate numeric numeric_collate
proc numeric_collate {lhs rhs} {
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
    COMMIT
  }
  execsql {SELECT count(*) FROM agger}
} 6
do_test misc1-2.2 {
  execsql {SELECT sum(one), two, four FROM agger
           GROUP BY two, four ORDER BY sum(one) desc}
} {8 two no 6 one yes 4 two yes 3 thr yes}
do_test misc1-2.3 {
  execsql {SELECT sum((one)), (two), (four) FROM agger
           GROUP BY (two), (four) ORDER BY sum(one) desc}
} {8 two no 6 one yes 4 two yes 3 thr yes}

# Here's a test for a bug found by Joel Lucsy.  The code below
# was causing an assertion failure.
#
do_test misc1-3.1 {
  set r [execsql {
    CREATE TABLE t1(a);







|



|







111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
    COMMIT
  }
  execsql {SELECT count(*) FROM agger}
} 6
do_test misc1-2.2 {
  execsql {SELECT sum(one), two, four FROM agger
           GROUP BY two, four ORDER BY sum(one) desc}
} {8.0 two no 6.0 one yes 4.0 two yes 3.0 thr yes}
do_test misc1-2.3 {
  execsql {SELECT sum((one)), (two), (four) FROM agger
           GROUP BY (two), (four) ORDER BY sum(one) desc}
} {8.0 two no 6.0 one yes 4.0 two yes 3.0 thr yes}

# Here's a test for a bug found by Joel Lucsy.  The code below
# was causing an assertion failure.
#
do_test misc1-3.1 {
  set r [execsql {
    CREATE TABLE t1(a);
Changes to test/misc3.test.
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for miscellanous features that were
# left out of other test files.
#
# $Id: misc3.test,v 1.11 2004/05/20 03:30:11 drh Exp $

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

# Ticket #529.  Make sure an ABORT does not damage the in-memory cache
# that will be used by subsequent statements in the same transaction.
#







|







9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for miscellanous features that were
# left out of other test files.
#
# $Id: misc3.test,v 1.12 2004/08/20 18:34:20 drh Exp $

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

# Ticket #529.  Make sure an ABORT does not damage the in-memory cache
# that will be used by subsequent statements in the same transaction.
#
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
  }
} ok

# Tests of the sqliteAtoF() function in util.c
#
do_test misc3-2.1 {
  execsql {SELECT 2e-25*0.5e25}
} 1
do_test misc3-2.2 {
  execsql {SELECT 2.0e-25*000000.500000000000000000000000000000e+00025}
} 1
do_test misc3-2.3 {
  execsql {SELECT 000000000002e-0000000025*0.5e25}
} 1
do_test misc3-2.4 {
  execsql {SELECT 2e-25*0.5e250}
} 1e+225
do_test misc3-2.5 {
  execsql {SELECT 2.0e-250*0.5e25}
} 1e-225
do_test misc3-2.6 {







|


|


|







68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
  }
} ok

# Tests of the sqliteAtoF() function in util.c
#
do_test misc3-2.1 {
  execsql {SELECT 2e-25*0.5e25}
} 1.0
do_test misc3-2.2 {
  execsql {SELECT 2.0e-25*000000.500000000000000000000000000000e+00025}
} 1.0
do_test misc3-2.3 {
  execsql {SELECT 000000000002e-0000000025*0.5e25}
} 1.0
do_test misc3-2.4 {
  execsql {SELECT 2e-25*0.5e250}
} 1e+225
do_test misc3-2.5 {
  execsql {SELECT 2.0e-250*0.5e25}
} 1e-225
do_test misc3-2.6 {
Changes to test/null.test.
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
# Check to see that NULL values are ignored in aggregate functions.
#
do_test null-3.1 {
  execsql {
    select count(*), count(b), count(c), sum(b), sum(c), 
           avg(b), avg(c), min(b), max(b) from t1;
  }
} {7 4 6 2 3 0.5 0.5 0 1}

# Check to see how WHERE clauses handle NULL values.  A NULL value
# is the same as UNKNOWN.  The WHERE clause should only select those
# rows that are TRUE.  FALSE and UNKNOWN rows are rejected.
#
do_test null-4.1 {
  execsql {







|







96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
# Check to see that NULL values are ignored in aggregate functions.
#
do_test null-3.1 {
  execsql {
    select count(*), count(b), count(c), sum(b), sum(c), 
           avg(b), avg(c), min(b), max(b) from t1;
  }
} {7 4 6 2.0 3.0 0.5 0.5 0 1}

# Check to see how WHERE clauses handle NULL values.  A NULL value
# is the same as UNKNOWN.  The WHERE clause should only select those
# rows that are TRUE.  FALSE and UNKNOWN rows are rejected.
#
do_test null-4.1 {
  execsql {
Changes to test/quote.test.
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is the ability to specify table and column names
# as quoted strings.
#
# $Id: quote.test,v 1.3 2002/05/21 13:43:04 drh Exp $

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

# Create a table with a strange name and with strange column names.
#
do_test quote-1.0 {







|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is the ability to specify table and column names
# as quoted strings.
#
# $Id: quote.test,v 1.4 2004/08/20 18:34:20 drh Exp $

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

# Create a table with a strange name and with strange column names.
#
do_test quote-1.0 {
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
  } msg ]
  lappend r $msg
} {0 {hello 10}}
do_test quote-1.3.1 {
  catchsql {
    SELECT '!pqr', '#xyz'+5 FROM '@abc'
  }
} {0 {!pqr 5}}
do_test quote-1.3.2 {
  catchsql {
    SELECT "!pqr", "#xyz"+5 FROM '@abc'
  }
} {0 {hello 10}}
do_test quote-1.3 {
  set r [catch {







|







46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
  } msg ]
  lappend r $msg
} {0 {hello 10}}
do_test quote-1.3.1 {
  catchsql {
    SELECT '!pqr', '#xyz'+5 FROM '@abc'
  }
} {0 {!pqr 5.0}}
do_test quote-1.3.2 {
  catchsql {
    SELECT "!pqr", "#xyz"+5 FROM '@abc'
  }
} {0 {hello 10}}
do_test quote-1.3 {
  set r [catch {
Changes to test/select1.test.
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.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the SELECT statement.
#
# $Id: select1.test,v 1.35 2004/07/20 01:45:21 drh Exp $

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

# Try to select on a non-existant table.
#
do_test select1-1.1 {













|







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.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the SELECT statement.
#
# $Id: select1.test,v 1.36 2004/08/20 18:34:20 drh Exp $

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

# Try to select on a non-existant table.
#
do_test select1-1.1 {
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
do_test select1-2.14 {
  set v [catch {execsql {SELECT SUM(*) FROM test1}} msg]
  lappend v $msg
} {1 {wrong number of arguments to function SUM()}}
do_test select1-2.15 {
  set v [catch {execsql {SELECT Sum(f1) FROM test1}} msg]
  lappend v $msg
} {0 44}
do_test select1-2.16 {
  set v [catch {execsql {SELECT sum(f1,f2) FROM test1}} msg]
  lappend v $msg
} {1 {wrong number of arguments to function sum()}}
do_test select1-2.17 {
  set v [catch {execsql {SELECT SUM(f1)+1 FROM test1}} msg]
  lappend v $msg
} {0 45}
do_test select1-2.17.1 {
  execsql {SELECT sum(a) FROM t3}
} {44}
do_test select1-2.18 {
  set v [catch {execsql {SELECT XYZZY(f1) FROM test1}} msg]
  lappend v $msg
} {1 {no such function: XYZZY}}
do_test select1-2.19 {
  set v [catch {execsql {SELECT SUM(min(f1,f2)) FROM test1}} msg]
  lappend v $msg
} {0 44}
do_test select1-2.20 {
  set v [catch {execsql {SELECT SUM(min(f1)) FROM test1}} msg]
  lappend v $msg
} {1 {misuse of aggregate function min()}}

# WHERE clause expressions
#







|







|


|







|







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
do_test select1-2.14 {
  set v [catch {execsql {SELECT SUM(*) FROM test1}} msg]
  lappend v $msg
} {1 {wrong number of arguments to function SUM()}}
do_test select1-2.15 {
  set v [catch {execsql {SELECT Sum(f1) FROM test1}} msg]
  lappend v $msg
} {0 44.0}
do_test select1-2.16 {
  set v [catch {execsql {SELECT sum(f1,f2) FROM test1}} msg]
  lappend v $msg
} {1 {wrong number of arguments to function sum()}}
do_test select1-2.17 {
  set v [catch {execsql {SELECT SUM(f1)+1 FROM test1}} msg]
  lappend v $msg
} {0 45.0}
do_test select1-2.17.1 {
  execsql {SELECT sum(a) FROM t3}
} {44.0}
do_test select1-2.18 {
  set v [catch {execsql {SELECT XYZZY(f1) FROM test1}} msg]
  lappend v $msg
} {1 {no such function: XYZZY}}
do_test select1-2.19 {
  set v [catch {execsql {SELECT SUM(min(f1,f2)) FROM test1}} msg]
  lappend v $msg
} {0 44.0}
do_test select1-2.20 {
  set v [catch {execsql {SELECT SUM(min(f1)) FROM test1}} msg]
  lappend v $msg
} {1 {misuse of aggregate function min()}}

# WHERE clause expressions
#
Changes to test/select3.test.
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
#    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 aggregate functions and the
# GROUP BY and HAVING clauses of SELECT statements.
#
# $Id: select3.test,v 1.11 2004/06/11 13:19:22 danielk1977 Exp $

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

# Build some test data
#
do_test select3-1.0 {







|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
#    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 aggregate functions and the
# GROUP BY and HAVING clauses of SELECT statements.
#
# $Id: select3.test,v 1.12 2004/08/20 18:34:20 drh Exp $

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

# Build some test data
#
do_test select3-1.0 {
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
  execsql {SELECT count(*) FROM t1}
} {31}
do_test select3-1.2 {
  execsql {
    SELECT min(n),min(log),max(n),max(log),sum(n),sum(log),avg(n),avg(log)
    FROM t1
  }
} {1 0 31 5 496 124 16 4}
do_test select3-1.3 {
  execsql {SELECT max(n)/avg(n), max(log)/avg(log) FROM t1}
} {1.9375 1.25}

# Try some basic GROUP BY clauses
#
do_test select3-2.1 {
  execsql {SELECT log, count(*) FROM t1 GROUP BY log ORDER BY log}
} {0 1 1 1 2 2 3 4 4 8 5 15}
do_test select3-2.2 {
  execsql {SELECT log, min(n) FROM t1 GROUP BY log ORDER BY log}
} {0 1 1 2 2 3 3 5 4 9 5 17}
do_test select3-2.3 {
  execsql {SELECT log, avg(n) FROM t1 GROUP BY log ORDER BY log}
} {0 1 1 2 2 3.5 3 6.5 4 12.5 5 24}
do_test select3-2.3 {
  execsql {SELECT log, avg(n)+1 FROM t1 GROUP BY log ORDER BY log}
} {0 2 1 3 2 4.5 3 7.5 4 13.5 5 25}
do_test select3-2.4 {
  execsql {SELECT log, avg(n)-min(n) FROM t1 GROUP BY log ORDER BY log}
} {0 0 1 0 2 0.5 3 1.5 4 3.5 5 7}
do_test select3-2.5 {
  execsql {SELECT log*2+1, avg(n)-min(n) FROM t1 GROUP BY log ORDER BY log}
} {1 0 3 0 5 0.5 7 1.5 9 3.5 11 7}
do_test select3-2.6 {
  execsql {
    SELECT log*2+1 as x, count(*) FROM t1 GROUP BY x ORDER BY x
  }
} {1 1 3 1 5 2 7 4 9 8 11 15}
do_test select3-2.7 {
  execsql {







|














|


|


|


|







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
  execsql {SELECT count(*) FROM t1}
} {31}
do_test select3-1.2 {
  execsql {
    SELECT min(n),min(log),max(n),max(log),sum(n),sum(log),avg(n),avg(log)
    FROM t1
  }
} {1 0 31 5 496.0 124.0 16.0 4.0}
do_test select3-1.3 {
  execsql {SELECT max(n)/avg(n), max(log)/avg(log) FROM t1}
} {1.9375 1.25}

# Try some basic GROUP BY clauses
#
do_test select3-2.1 {
  execsql {SELECT log, count(*) FROM t1 GROUP BY log ORDER BY log}
} {0 1 1 1 2 2 3 4 4 8 5 15}
do_test select3-2.2 {
  execsql {SELECT log, min(n) FROM t1 GROUP BY log ORDER BY log}
} {0 1 1 2 2 3 3 5 4 9 5 17}
do_test select3-2.3 {
  execsql {SELECT log, avg(n) FROM t1 GROUP BY log ORDER BY log}
} {0 1.0 1 2.0 2 3.5 3 6.5 4 12.5 5 24.0}
do_test select3-2.3 {
  execsql {SELECT log, avg(n)+1 FROM t1 GROUP BY log ORDER BY log}
} {0 2.0 1 3.0 2 4.5 3 7.5 4 13.5 5 25.0}
do_test select3-2.4 {
  execsql {SELECT log, avg(n)-min(n) FROM t1 GROUP BY log ORDER BY log}
} {0 0.0 1 0.0 2 0.5 3 1.5 4 3.5 5 7.0}
do_test select3-2.5 {
  execsql {SELECT log*2+1, avg(n)-min(n) FROM t1 GROUP BY log ORDER BY log}
} {1 0.0 3 0.0 5 0.5 7 1.5 9 3.5 11 7.0}
do_test select3-2.6 {
  execsql {
    SELECT log*2+1 as x, count(*) FROM t1 GROUP BY x ORDER BY x
  }
} {1 1 3 1 5 2 7 4 9 8 11 15}
do_test select3-2.7 {
  execsql {
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183

do_test select3-5.1 {
  execsql {
    SELECT log, count(*), avg(n), max(n+log*2) FROM t1 
    GROUP BY log 
    ORDER BY max(n+log*2)+0, avg(n)+0
  }
} {0 1 1 1 1 1 2 4 2 2 3.5 8 3 4 6.5 14 4 8 12.5 24 5 15 24 41}
do_test select3-5.2 {
  execsql {
    SELECT log, count(*), avg(n), max(n+log*2) FROM t1 
    GROUP BY log 
    ORDER BY max(n+log*2)+0, min(log,avg(n))+0
  }
} {0 1 1 1 1 1 2 4 2 2 3.5 8 3 4 6.5 14 4 8 12.5 24 5 15 24 41}

# Test sorting of GROUP BY results in the presence of an index
# on the GROUP BY column.
#
do_test select3-6.1 {
  execsql {
    SELECT log, min(n) FROM t1 GROUP BY log ORDER BY log;







|






|







162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183

do_test select3-5.1 {
  execsql {
    SELECT log, count(*), avg(n), max(n+log*2) FROM t1 
    GROUP BY log 
    ORDER BY max(n+log*2)+0, avg(n)+0
  }
} {0 1 1.0 1 1 1 2.0 4 2 2 3.5 8 3 4 6.5 14 4 8 12.5 24 5 15 24.0 41}
do_test select3-5.2 {
  execsql {
    SELECT log, count(*), avg(n), max(n+log*2) FROM t1 
    GROUP BY log 
    ORDER BY max(n+log*2)+0, min(log,avg(n))+0
  }
} {0 1 1.0 1 1 1 2.0 4 2 2 3.5 8 3 4 6.5 14 4 8 12.5 24 5 15 24.0 41}

# Test sorting of GROUP BY results in the presence of an index
# on the GROUP BY column.
#
do_test select3-6.1 {
  execsql {
    SELECT log, min(n) FROM t1 GROUP BY log ORDER BY log;
Changes to test/select5.test.
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
#    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 aggregate functions and the
# GROUP BY and HAVING clauses of SELECT statements.
#
# $Id: select5.test,v 1.7 2004/05/27 17:22:56 drh Exp $

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

# Build some test data
#
execsql {







|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
#    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 aggregate functions and the
# GROUP BY and HAVING clauses of SELECT statements.
#
# $Id: select5.test,v 1.8 2004/08/20 18:34:20 drh Exp $

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

# Build some test data
#
execsql {
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96

# Get the Agg function to rehash in vdbe.c
#
do_test select5-3.1 {
  execsql {
    SELECT x, count(*), avg(y) FROM t1 GROUP BY x HAVING x<4 ORDER BY x
  }
} {1 1 5 2 1 5 3 1 5}

# Run various aggregate functions when the count is zero.
#
do_test select5-4.1 {
  execsql {
    SELECT avg(x) FROM t1 WHERE x>100
  }







|







82
83
84
85
86
87
88
89
90
91
92
93
94
95
96

# Get the Agg function to rehash in vdbe.c
#
do_test select5-3.1 {
  execsql {
    SELECT x, count(*), avg(y) FROM t1 GROUP BY x HAVING x<4 ORDER BY x
  }
} {1 1 5.0 2 1 5.0 3 1 5.0}

# Run various aggregate functions when the count is zero.
#
do_test select5-4.1 {
  execsql {
    SELECT avg(x) FROM t1 WHERE x>100
  }
110
111
112
113
114
115
116
117
118
119
    SELECT max(x) FROM t1 WHERE x>100
  }
} {{}}
do_test select5-4.5 {
  execsql {
    SELECT sum(x) FROM t1 WHERE x>100
  }
} {0}

finish_test







|


110
111
112
113
114
115
116
117
118
119
    SELECT max(x) FROM t1 WHERE x>100
  }
} {{}}
do_test select5-4.5 {
  execsql {
    SELECT sum(x) FROM t1 WHERE x>100
  }
} {0.0}

finish_test
Changes to test/select6.test.
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
#    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 SELECT statements that contain
# subqueries in their FROM clause.
#
# $Id: select6.test,v 1.11 2004/01/24 20:18:13 drh Exp $

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

do_test select6-1.0 {
  execsql {
    BEGIN;







|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
#    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 SELECT statements that contain
# subqueries in their FROM clause.
#
# $Id: select6.test,v 1.12 2004/08/20 18:34:20 drh Exp $

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

do_test select6-1.0 {
  execsql {
    BEGIN;
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
    SELECT a,b,a+b FROM (SELECT avg(x) as 'a', avg(y) as 'b' FROM t1)
  }
} {10.5 3.7 14.2}
do_test select6-3.4 {
  execsql {
    SELECT a,b,a+b FROM (SELECT avg(x) as 'a', avg(y) as 'b' FROM t1 WHERE y=4)
  }
} {11.5 4 15.5}
do_test select6-3.5 {
  execsql {
    SELECT x,y,x+y FROM (SELECT avg(a) as 'x', avg(b) as 'y' FROM t2 WHERE a=4)
  }
} {4 3 7}
do_test select6-3.6 {
  execsql {
    SELECT a,b,a+b FROM (SELECT avg(x) as 'a', avg(y) as 'b' FROM t1)
    WHERE a>10
  }
} {10.5 3.7 14.2}
do_test select6-3.7 {
  execsql {
    SELECT a,b,a+b FROM (SELECT avg(x) as 'a', avg(y) as 'b' FROM t1)
    WHERE a<10
  }
} {}
do_test select6-3.8 {
  execsql {
    SELECT a,b,a+b FROM (SELECT avg(x) as 'a', avg(y) as 'b' FROM t1 WHERE y=4)
    WHERE a>10
  }
} {11.5 4 15.5}
do_test select6-3.9 {
  execsql {
    SELECT a,b,a+b FROM (SELECT avg(x) as 'a', avg(y) as 'b' FROM t1 WHERE y=4)
    WHERE a<10
  }
} {}
do_test select6-3.10 {
  execsql {
    SELECT a,b,a+b FROM (SELECT avg(x) as 'a', y as 'b' FROM t1 GROUP BY b)
    ORDER BY a
  }
} {1 1 2 2.5 2 4.5 5.5 3 8.5 11.5 4 15.5 18 5 23}
do_test select6-3.11 {
  execsql {
    SELECT a,b,a+b FROM 
       (SELECT avg(x) as 'a', y as 'b' FROM t1 GROUP BY b)
    WHERE b<4 ORDER BY a
  }
} {1 1 2 2.5 2 4.5 5.5 3 8.5}
do_test select6-3.12 {
  execsql {
    SELECT a,b,a+b FROM 
       (SELECT avg(x) as 'a', y as 'b' FROM t1 GROUP BY b HAVING a>1)
    WHERE b<4 ORDER BY a
  }
} {2.5 2 4.5 5.5 3 8.5}
do_test select6-3.13 {
  execsql {
    SELECT a,b,a+b FROM 
       (SELECT avg(x) as 'a', y as 'b' FROM t1 GROUP BY b HAVING a>1)
    ORDER BY a
  }
} {2.5 2 4.5 5.5 3 8.5 11.5 4 15.5 18 5 23}
do_test select6-3.14 {
  execsql {
    SELECT [count(*)],y FROM (SELECT count(*), y FROM t1 GROUP BY y)
    ORDER BY [count(*)]
  }
} {1 1 2 2 4 3 5 5 8 4}
do_test select6-3.15 {







|




|

















|











|






|













|







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
    SELECT a,b,a+b FROM (SELECT avg(x) as 'a', avg(y) as 'b' FROM t1)
  }
} {10.5 3.7 14.2}
do_test select6-3.4 {
  execsql {
    SELECT a,b,a+b FROM (SELECT avg(x) as 'a', avg(y) as 'b' FROM t1 WHERE y=4)
  }
} {11.5 4.0 15.5}
do_test select6-3.5 {
  execsql {
    SELECT x,y,x+y FROM (SELECT avg(a) as 'x', avg(b) as 'y' FROM t2 WHERE a=4)
  }
} {4.0 3.0 7.0}
do_test select6-3.6 {
  execsql {
    SELECT a,b,a+b FROM (SELECT avg(x) as 'a', avg(y) as 'b' FROM t1)
    WHERE a>10
  }
} {10.5 3.7 14.2}
do_test select6-3.7 {
  execsql {
    SELECT a,b,a+b FROM (SELECT avg(x) as 'a', avg(y) as 'b' FROM t1)
    WHERE a<10
  }
} {}
do_test select6-3.8 {
  execsql {
    SELECT a,b,a+b FROM (SELECT avg(x) as 'a', avg(y) as 'b' FROM t1 WHERE y=4)
    WHERE a>10
  }
} {11.5 4.0 15.5}
do_test select6-3.9 {
  execsql {
    SELECT a,b,a+b FROM (SELECT avg(x) as 'a', avg(y) as 'b' FROM t1 WHERE y=4)
    WHERE a<10
  }
} {}
do_test select6-3.10 {
  execsql {
    SELECT a,b,a+b FROM (SELECT avg(x) as 'a', y as 'b' FROM t1 GROUP BY b)
    ORDER BY a
  }
} {1.0 1 2.0 2.5 2 4.5 5.5 3 8.5 11.5 4 15.5 18.0 5 23.0}
do_test select6-3.11 {
  execsql {
    SELECT a,b,a+b FROM 
       (SELECT avg(x) as 'a', y as 'b' FROM t1 GROUP BY b)
    WHERE b<4 ORDER BY a
  }
} {1.0 1 2.0 2.5 2 4.5 5.5 3 8.5}
do_test select6-3.12 {
  execsql {
    SELECT a,b,a+b FROM 
       (SELECT avg(x) as 'a', y as 'b' FROM t1 GROUP BY b HAVING a>1)
    WHERE b<4 ORDER BY a
  }
} {2.5 2 4.5 5.5 3 8.5}
do_test select6-3.13 {
  execsql {
    SELECT a,b,a+b FROM 
       (SELECT avg(x) as 'a', y as 'b' FROM t1 GROUP BY b HAVING a>1)
    ORDER BY a
  }
} {2.5 2 4.5 5.5 3 8.5 11.5 4 15.5 18.0 5 23.0}
do_test select6-3.14 {
  execsql {
    SELECT [count(*)],y FROM (SELECT count(*), y FROM t1 GROUP BY y)
    ORDER BY [count(*)]
  }
} {1 1 2 2 4 3 5 5 8 4}
do_test select6-3.15 {
Changes to test/sort.test.
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.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the CREATE TABLE statement.
#
# $Id: sort.test,v 1.14 2004/06/27 21:31:40 drh Exp $

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

# Create a bunch of data to sort against
#
do_test sort-1.0 {













|







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.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the CREATE TABLE statement.
#
# $Id: sort.test,v 1.15 2004/08/20 18:34:20 drh Exp $

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

# Create a bunch of data to sort against
#
do_test sort-1.0 {
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
  execsql {SELECT n FROM t1 ORDER BY v}
} {8 5 4 1 7 6 3 2}
do_test sort-1.4 {
  execsql {SELECT n FROM t1 ORDER BY v DESC}
} {2 3 6 7 1 4 5 8}
do_test sort-1.5 {
  execsql {SELECT flt FROM t1 ORDER BY flt}
} {-11 -1.6 -0.0013442 0.123 2.15 3.141592653 123 4221}
do_test sort-1.6 {
  execsql {SELECT flt FROM t1 ORDER BY flt DESC}
} {4221 123 3.141592653 2.15 0.123 -0.0013442 -1.6 -11}
do_test sort-1.7 {
  execsql {SELECT roman FROM t1 ORDER BY roman}
} {I II III IV V VI VII VIII}
do_test sort-1.8 {
  execsql {SELECT n FROM t1 ORDER BY log, flt}
} {1 2 3 5 4 6 7 8}
do_test sort-1.8.1 {







|


|







58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
  execsql {SELECT n FROM t1 ORDER BY v}
} {8 5 4 1 7 6 3 2}
do_test sort-1.4 {
  execsql {SELECT n FROM t1 ORDER BY v DESC}
} {2 3 6 7 1 4 5 8}
do_test sort-1.5 {
  execsql {SELECT flt FROM t1 ORDER BY flt}
} {-11 -1.6 -0.0013442 0.123 2.15 3.141592653 123.0 4221.0}
do_test sort-1.6 {
  execsql {SELECT flt FROM t1 ORDER BY flt DESC}
} {4221.0 123.0 3.141592653 2.15 0.123 -0.0013442 -1.6 -11}
do_test sort-1.7 {
  execsql {SELECT roman FROM t1 ORDER BY roman}
} {I II III IV V VI VII VIII}
do_test sort-1.8 {
  execsql {SELECT n FROM t1 ORDER BY log, flt}
} {1 2 3 5 4 6 7 8}
do_test sort-1.8.1 {
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
do_test sort-8.1 {
  execsql {
    CREATE TABLE t5(a real, b text);
    INSERT INTO t5 VALUES(100,'A1');
    INSERT INTO t5 VALUES(100.0,'A2');
    SELECT * FROM t5 ORDER BY a, b;
  }
} {100 A1 100 A2}

# BLOBs should sort after TEXT
#
do_test sort-9.1 {
  execsql {
    CREATE TABLE t6(x, y);
    INSERT INTO t6 VALUES(1,1);







|







349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
do_test sort-8.1 {
  execsql {
    CREATE TABLE t5(a real, b text);
    INSERT INTO t5 VALUES(100,'A1');
    INSERT INTO t5 VALUES(100.0,'A2');
    SELECT * FROM t5 ORDER BY a, b;
  }
} {100 A1 100.0 A2}

# BLOBs should sort after TEXT
#
do_test sort-9.1 {
  execsql {
    CREATE TABLE t6(x, y);
    INSERT INTO t6 VALUES(1,1);
Changes to test/subselect.test.
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
#    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 SELECT statements that are part of
# expressions.
#
# $Id: subselect.test,v 1.9 2004/07/26 23:32:27 drh Exp $

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

# Basic sanity checking.  Try a simple subselect.
#
do_test subselect-1.1 {







|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
#    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 SELECT statements that are part of
# expressions.
#
# $Id: subselect.test,v 1.10 2004/08/20 18:34:20 drh Exp $

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

# Basic sanity checking.  Try a simple subselect.
#
do_test subselect-1.1 {
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
    SELECT * FROM t3 ORDER BY x;
  }
} {1 2 3 4 5 6}
do_test subselect-3.2 {
  execsql {
    SELECT sum(x) FROM (SELECT x FROM t3 ORDER BY x LIMIT 2);
  }
} {3}
do_test subselect-3.3 {
  execsql {
    SELECT sum(x) FROM (SELECT x FROM t3 ORDER BY x DESC LIMIT 2);
  }
} {11}
do_test subselect-3.4 {
  execsql {
    SELECT (SELECT x FROM t3 ORDER BY x);
  }
} {1}
do_test subselect-3.5 {
  execsql {







|




|







109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
    SELECT * FROM t3 ORDER BY x;
  }
} {1 2 3 4 5 6}
do_test subselect-3.2 {
  execsql {
    SELECT sum(x) FROM (SELECT x FROM t3 ORDER BY x LIMIT 2);
  }
} {3.0}
do_test subselect-3.3 {
  execsql {
    SELECT sum(x) FROM (SELECT x FROM t3 ORDER BY x DESC LIMIT 2);
  }
} {11.0}
do_test subselect-3.4 {
  execsql {
    SELECT (SELECT x FROM t3 ORDER BY x);
  }
} {1}
do_test subselect-3.5 {
  execsql {
Changes to test/tclsqlite.test.
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
# This file implements regression tests for TCL interface to the
# SQLite library. 
#
# Actually, all tests are based on the TCL interface, so the main
# interface is pretty well tested.  This file contains some addition
# tests for fringe issues that the main test suite does not cover.
#
# $Id: tclsqlite.test,v 1.29 2004/08/20 16:02:39 drh Exp $

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

# Check the error messages generated by tclsqlite
#
if {[sqlite3 -has-codec]} {







|







11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
# This file implements regression tests for TCL interface to the
# SQLite library. 
#
# Actually, all tests are based on the TCL interface, so the main
# interface is pretty well tested.  This file contains some addition
# tests for fringe issues that the main test suite does not cover.
#
# $Id: tclsqlite.test,v 1.30 2004/08/20 18:34:20 drh Exp $

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

# Check the error messages generated by tclsqlite
#
if {[sqlite3 -has-codec]} {
180
181
182
183
184
185
186
187



































188
  db busy busy_callback
  db busy
} {busy_callback}
do_test tcl-4.2 {
  db busy {}
  db busy
} {}




































finish_test








>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

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
  db busy busy_callback
  db busy
} {busy_callback}
do_test tcl-4.2 {
  db busy {}
  db busy
} {}

# Parsing of TCL variable names within SQL into bound parameters.
#
do_test tcl-5.1 {
  execsql {CREATE TABLE t3(a,b,c)}
  catch {unset x}
  set x(1) 5
  set x(2) 7
  execsql {
    INSERT INTO t3 VALUES($::x(1),$::x(2),$::x(3));
    SELECT * FROM t3
  }
} {5 7 {}}
do_test tcl-5.2 {
  execsql {
    SELECT typeof(a), typeof(b), typeof(c) FROM t3
  }
} {text text null}
do_test tcl-5.3 {
  catch {unset x}
  set x [binary format h12 686900686f00]
  execsql {
    UPDATE t3 SET a=$::x;
  }
  db eval {
    SELECT a FROM t3
  } break
  binary scan $a h12 adata
  set adata
} {686900686f00}
do_test tcl-5.4 {
    execsql {
    SELECT typeof(a), typeof(b), typeof(c) FROM t3
  }
} {blob text null}

finish_test
Changes to test/tester.tcl.
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.
#
#***********************************************************************
# This file implements some common TCL routines used for regression
# testing the SQLite library
#
# $Id: tester.tcl,v 1.37 2004/06/19 00:16:31 drh Exp $

# Make sure tclsqlite3 was compiled correctly.  Abort now with an
# error message if not.
#
if {[sqlite3 -tcl-uses-utf]} {
  if {"\u1234"=="u1234"} {
    puts stderr "***** BUILD PROBLEM *****"













|







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.
#
#***********************************************************************
# This file implements some common TCL routines used for regression
# testing the SQLite library
#
# $Id: tester.tcl,v 1.38 2004/08/20 18:34:20 drh Exp $

# Make sure tclsqlite3 was compiled correctly.  Abort now with an
# error message if not.
#
if {[sqlite3 -tcl-uses-utf]} {
  if {"\u1234"=="u1234"} {
    puts stderr "***** BUILD PROBLEM *****"
35
36
37
38
39
40
41


42
43
44
45
46
47
48
    puts stderr "(\"tcl.h\") from a old TCL version that does not support"
    puts stderr "Unicode.  This combination causes internal errors."
    puts stderr "Recompile using a TCL library and header file that match"
    puts stderr "and try again.\n**************************"
    exit 1
  }
}



# Use the pager codec if it is available
#
if {[sqlite3 -has-codec] && [info command sqlite_orig]==""} {
  rename sqlite3 sqlite_orig
  proc sqlite3 {args} {
    if {[llength $args]==2 && [string index [lindex $args 0] 0]!="-"} {







>
>







35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
    puts stderr "(\"tcl.h\") from a old TCL version that does not support"
    puts stderr "Unicode.  This combination causes internal errors."
    puts stderr "Recompile using a TCL library and header file that match"
    puts stderr "and try again.\n**************************"
    exit 1
  }
}

set tcl_precision 15

# Use the pager codec if it is available
#
if {[sqlite3 -has-codec] && [info command sqlite_orig]==""} {
  rename sqlite3 sqlite_orig
  proc sqlite3 {args} {
    if {[llength $args]==2 && [string index [lindex $args 0] 0]!="-"} {
Changes to test/trigger2.test.
102
103
104
105
106
107
108

109
110
111
112


113

114
115
116
117
118
119
120
	  old.a, old.b, 
	  (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 
	  new.a, new.b);
    END;
  }

  do_test trigger2-1.$ii.1 {

    execsql { 
      UPDATE tbl SET a = a * 10, b = b * 10;
      SELECT * FROM rlog ORDER BY idx;
      SELECT * FROM clog ORDER BY idx;


    }

  } [list 1 1 2  4  6 10 20 \
          2 1 2 13 24 10 20 \
	  3 3 4 13 24 30 40 \
	  4 3 4 40 60 30 40 \
          1 1 2 13 24 10 20 ]

  execsql {







>
|



>
>

>







102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
	  old.a, old.b, 
	  (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 
	  new.a, new.b);
    END;
  }

  do_test trigger2-1.$ii.1 {
    set r {}
    foreach v [execsql { 
      UPDATE tbl SET a = a * 10, b = b * 10;
      SELECT * FROM rlog ORDER BY idx;
      SELECT * FROM clog ORDER BY idx;
    }] {
      lappend r [expr {int($v)}]
    }
    set r
  } [list 1 1 2  4  6 10 20 \
          2 1 2 13 24 10 20 \
	  3 3 4 13 24 30 40 \
	  4 3 4 40 60 30 40 \
          1 1 2 13 24 10 20 ]

  execsql {
135
136
137
138
139
140
141

142
143
144


145

146
147
148
149
150
151
152
      INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog), 
	  old.a, old.b, 
	  (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 
	  0, 0);
    END;
  }
  do_test trigger2-1.$ii.2 {

    execsql {
      DELETE FROM tbl;
      SELECT * FROM rlog;


    }

  } [list 1 100 100 400 300 0 0 \
          2 100 100 300 200 0 0 \
          3 300 200 300 200 0 0 \
          4 300 200 0 0 0 0 ]

  execsql {
    DELETE FROM rlog;







>
|


>
>

>







139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
      INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog), 
	  old.a, old.b, 
	  (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 
	  0, 0);
    END;
  }
  do_test trigger2-1.$ii.2 {
    set r {}
    foreach v [execsql {
      DELETE FROM tbl;
      SELECT * FROM rlog;
    }] {
      lappend r [expr {int($v)}]
    }
    set r
  } [list 1 100 100 400 300 0 0 \
          2 100 100 300 200 0 0 \
          3 300 200 300 200 0 0 \
          4 300 200 0 0 0 0 ]

  execsql {
    DELETE FROM rlog;
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
      INSERT INTO other_tbl VALUES(3, 4);
      -- INSERT INTO tbl SELECT * FROM other_tbl;
      INSERT INTO tbl VALUES(5, 6);
      DROP TABLE other_tbl;

      SELECT * FROM rlog;
    }
  } [list 1 0 0 0 0 5 6 \
          2 0 0 5 6 5 6 ]

  do_test trigger2-1.$ii.4 {
    execsql {
      PRAGMA integrity_check;
    }
  } {ok}
}







|
|







182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
      INSERT INTO other_tbl VALUES(3, 4);
      -- INSERT INTO tbl SELECT * FROM other_tbl;
      INSERT INTO tbl VALUES(5, 6);
      DROP TABLE other_tbl;

      SELECT * FROM rlog;
    }
  } [list 1 0 0 0.0 0.0 5 6 \
          2 0 0 5.0 6.0 5 6 ]

  do_test trigger2-1.$ii.4 {
    execsql {
      PRAGMA integrity_check;
    }
  } {ok}
}
Changes to test/types.test.
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library. Specfically
# it tests that the different storage classes (integer, real, text etc.)
# all work correctly.
#
# $Id: types.test,v 1.10 2004/07/22 15:02:26 drh Exp $

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

# Tests in this file are organized roughly as follows:
#
# types-1.*.*: Test that values are stored using the expected storage







|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library. Specfically
# it tests that the different storage classes (integer, real, text etc.)
# all work correctly.
#
# $Id: types.test,v 1.11 2004/08/20 18:34:20 drh Exp $

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

# Tests in this file are organized roughly as follows:
#
# types-1.*.*: Test that values are stored using the expected storage
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
    INSERT INTO t2 VALUES(-12345.678);
  }
} {}
do_test types-2.2.2 {
  execsql {
    SELECT a FROM t2;
  }
} {0 12345.678 -12345.678}

# Check that all the record sizes are as we expected.
do_test types-2.2.3 {
  set root [db eval {select rootpage from sqlite_master where name = 't2'}]
  record_sizes $root
} {10 10 10}








|







215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
    INSERT INTO t2 VALUES(-12345.678);
  }
} {}
do_test types-2.2.2 {
  execsql {
    SELECT a FROM t2;
  }
} {0.0 12345.678 -12345.678}

# Check that all the record sizes are as we expected.
do_test types-2.2.3 {
  set root [db eval {select rootpage from sqlite_master where name = 't2'}]
  record_sizes $root
} {10 10 10}

Changes to test/where.test.
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.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the use of indices in WHERE clases.
#
# $Id: where.test,v 1.21 2004/05/21 03:01:59 drh Exp $

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

# Build some test data
#
do_test where-1.0 {













|







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.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the use of indices in WHERE clases.
#
# $Id: where.test,v 1.22 2004/08/20 18:34:20 drh Exp $

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

# Build some test data
#
do_test where-1.0 {
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
    CREATE TABLE t3(a,b,c);
    CREATE INDEX t3a ON t3(a);
    CREATE INDEX t3bc ON t3(b,c);
    CREATE INDEX t3acb ON t3(a,c,b);
    INSERT INTO t3 SELECT w, 101-w, y FROM t1;
    SELECT count(*), sum(a), sum(b), sum(c) FROM t3;
  }
} {100 5050 5050 348550}
do_test where-6.2 {
  cksort {
    SELECT * FROM t3 ORDER BY a LIMIT 3
  }
} {1 100 4 2 99 9 3 98 16 nosort}
do_test where-6.3 {
  cksort {







|







376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
    CREATE TABLE t3(a,b,c);
    CREATE INDEX t3a ON t3(a);
    CREATE INDEX t3bc ON t3(b,c);
    CREATE INDEX t3acb ON t3(a,c,b);
    INSERT INTO t3 SELECT w, 101-w, y FROM t1;
    SELECT count(*), sum(a), sum(b), sum(c) FROM t3;
  }
} {100 5050.0 5050.0 348550.0}
do_test where-6.2 {
  cksort {
    SELECT * FROM t3 ORDER BY a LIMIT 3
  }
} {1 100 4 2 99 9 3 98 16 nosort}
do_test where-6.3 {
  cksort {