SQLite

Check-in [287f86731c]
Login

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

Overview
Comment:Remove the COPY command. (CVS 1477)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 287f86731c71401dbac098e08357367b4f8e5a43
User & Date: drh 2004-05-27 17:22:55.000
Context
2004-05-27
19:59
Change the record format to include an extra varint at the beginning to record the number of bytes in the header. (CVS 1478) (check-in: 0c4d138807 user: drh tags: trunk)
17:22
Remove the COPY command. (CVS 1477) (check-in: 287f86731c user: drh tags: trunk)
14:23
Enhance the C function used to test sqlite3_create_function() from Tcl. (CVS 1476) (check-in: c85e5f1528 user: danielk1977 tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to main.mk.
︙︙
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80


# This is how we compile
#
TCCX = $(TCC) $(OPTS) $(THREADSAFE) $(USLEEP) -I. -I$(TOP)/src

# Object files for the SQLite library.
#
LIBOBJ = attach.o auth.o btree.o build.o copy.o date.o delete.o \
         expr.o func.o hash.o insert.o \
         main.o opcodes.o os_mac.o os_unix.o os_win.o \
         pager.o parse.o pragma.o printf.o random.o \
         select.o table.o tokenize.o trigger.o update.o util.o vacuum.o \
         vdbe.o vdbeapi.o vdbeaux.o vdbemem.o \
         where.o tclsqlite.o utf.o legacy.o

# All of the source code files.
#
SRC = \
  $(TOP)/src/attach.c \
  $(TOP)/src/auth.c \
  $(TOP)/src/btree.c \
  $(TOP)/src/btree.h \
  $(TOP)/src/build.c \
  $(TOP)/src/copy.c \
  $(TOP)/src/date.c \
  $(TOP)/src/delete.c \
  $(TOP)/src/encode.c \
  $(TOP)/src/expr.c \
  $(TOP)/src/func.c \
  $(TOP)/src/hash.c \
  $(TOP)/src/hash.h \








|















<








50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72

73
74
75
76
77
78
79


# This is how we compile
#
TCCX = $(TCC) $(OPTS) $(THREADSAFE) $(USLEEP) -I. -I$(TOP)/src

# Object files for the SQLite library.
#
LIBOBJ = attach.o auth.o btree.o build.o date.o delete.o \
         expr.o func.o hash.o insert.o \
         main.o opcodes.o os_mac.o os_unix.o os_win.o \
         pager.o parse.o pragma.o printf.o random.o \
         select.o table.o tokenize.o trigger.o update.o util.o vacuum.o \
         vdbe.o vdbeapi.o vdbeaux.o vdbemem.o \
         where.o tclsqlite.o utf.o legacy.o

# All of the source code files.
#
SRC = \
  $(TOP)/src/attach.c \
  $(TOP)/src/auth.c \
  $(TOP)/src/btree.c \
  $(TOP)/src/btree.h \
  $(TOP)/src/build.c \

  $(TOP)/src/date.c \
  $(TOP)/src/delete.c \
  $(TOP)/src/encode.c \
  $(TOP)/src/expr.c \
  $(TOP)/src/func.c \
  $(TOP)/src/hash.c \
  $(TOP)/src/hash.h \
︙︙
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231

	echo '"#define SQLITE_PTR_SZ %d",sizeof(char*));' >>temp.c
	echo 'exit(0);}' >>temp.c
	$(BCC) -o temp temp.c
	./temp >config.h
	echo >>config.h
	rm -f temp.c temp

copy.o:	$(TOP)/src/copy.c $(HDR)
	$(TCCX) -c $(TOP)/src/copy.c

date.o:	$(TOP)/src/date.c $(HDR)
	$(TCCX) -c $(TOP)/src/date.c

delete.o:	$(TOP)/src/delete.c $(HDR)
	$(TCCX) -c $(TOP)/src/delete.c

encode.o:	$(TOP)/src/encode.c








<
<
<








214
215
216
217
218
219
220



221
222
223
224
225
226
227

	echo '"#define SQLITE_PTR_SZ %d",sizeof(char*));' >>temp.c
	echo 'exit(0);}' >>temp.c
	$(BCC) -o temp temp.c
	./temp >config.h
	echo >>config.h
	rm -f temp.c temp




date.o:	$(TOP)/src/date.c $(HDR)
	$(TCCX) -c $(TOP)/src/date.c

delete.o:	$(TOP)/src/delete.c $(HDR)
	$(TCCX) -c $(TOP)/src/delete.c

encode.o:	$(TOP)/src/encode.c
︙︙
Deleted src/copy.c. 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113

/*
** 2003 April 6
**
** The author disclaims copyright to this source code.  In place of
** a legal notice, here is a blessing:
**
**    May you do good and not evil.
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains code used to implement the COPY command.
**
** $Id: copy.c,v 1.13 2004/05/26 10:11:05 danielk1977 Exp $
*/
#include "sqliteInt.h"

/*
** The COPY command is for compatibility with PostgreSQL and specificially
** for the ability to read the output of pg_dump.  The format is as
** follows:
**
**    COPY table FROM file [USING DELIMITERS string]
**
** "table" is an existing table name.  We will read lines of code from
** file to fill this table with data.  File might be "stdin".  The optional
** delimiter string identifies the field separators.  The default is a tab.
*/
void sqlite3Copy(
  Parse *pParse,       /* The parser context */
  SrcList *pTableName, /* The name of the table into which we will insert */
  Token *pFilename,    /* The file from which to obtain information */
  Token *pDelimiter,   /* Use this as the field delimiter */
  int onError          /* What to do if a constraint fails */
){
  Table *pTab;
  int i;
  Vdbe *v;
  int addr, end;
  char *zFile = 0;
  const char *zDb;
  sqlite *db = pParse->db;


  if( sqlite3_malloc_failed  ) goto copy_cleanup;
  assert( pTableName->nSrc==1 );
  pTab = sqlite3SrcListLookup(pParse, pTableName);
  if( pTab==0 || sqlite3IsReadOnly(pParse, pTab, 0) ) goto copy_cleanup;
  zFile = sqliteStrNDup(pFilename->z, pFilename->n);
  sqlite3Dequote(zFile);
  assert( pTab->iDb<db->nDb );
  zDb = db->aDb[pTab->iDb].zName;
  if( sqlite3AuthCheck(pParse, SQLITE_INSERT, pTab->zName, 0, zDb)
      || sqlite3AuthCheck(pParse, SQLITE_COPY, pTab->zName, zFile, zDb) ){
    goto copy_cleanup;
  }
  v = sqlite3GetVdbe(pParse);
  if( v ){
    sqlite3BeginWriteOperation(pParse, 1, pTab->iDb);
    addr = sqlite3VdbeOp3(v, OP_FileOpen, 0, 0, pFilename->z, pFilename->n);
    sqlite3VdbeDequoteP3(v, addr);
    sqlite3OpenTableAndIndices(pParse, pTab, 0);
    if( db->flags & SQLITE_CountRows ){
      sqlite3VdbeAddOp(v, OP_Integer, 0, 0);  /* Initialize the row count */
    }
    end = sqlite3VdbeMakeLabel(v);
    addr = sqlite3VdbeAddOp(v, OP_FileRead, pTab->nCol, end);
    if( pDelimiter ){
      sqlite3VdbeChangeP3(v, addr, pDelimiter->z, pDelimiter->n);
      sqlite3VdbeDequoteP3(v, addr);
    }else{
      sqlite3VdbeChangeP3(v, addr, "\t", 1);
    }
    if( pTab->iPKey>=0 ){
      sqlite3VdbeAddOp(v, OP_FileColumn, pTab->iPKey, 0);
      sqlite3VdbeAddOp(v, OP_MustBeInt, 0, 0);
    }else{
      sqlite3VdbeAddOp(v, OP_NewRecno, 0, 0);
    }
    for(i=0; i<pTab->nCol; i++){
      if( i==pTab->iPKey ){
        /* The integer primary key column is filled with NULL since its
        ** value is always pulled from the record number */
        sqlite3VdbeAddOp(v, OP_String, 0, 0);
      }else{
        sqlite3VdbeAddOp(v, OP_FileColumn, i, 0);
      }
    }
    sqlite3GenerateConstraintChecks(pParse, pTab, 0, 0, pTab->iPKey>=0, 
                                   0, onError, addr);
    sqlite3CompleteInsertion(pParse, pTab, 0, 0, 0, 0, -1);
    if( (db->flags & SQLITE_CountRows)!=0 ){
      sqlite3VdbeAddOp(v, OP_AddImm, 1, 0);  /* Increment row count */
    }
    sqlite3VdbeAddOp(v, OP_Goto, 0, addr);
    sqlite3VdbeResolveLabel(v, end);
    sqlite3VdbeAddOp(v, OP_Noop, 0, 0);
    sqlite3EndWriteOperation(pParse);
    if( db->flags & SQLITE_CountRows ){
      sqlite3VdbeAddOp(v, OP_Callback, 1, 0);
      sqlite3VdbeSetNumCols(v, 1);
      sqlite3VdbeSetColName(v, 0, "rows inserted", P3_STATIC);
    }
  }
  
copy_cleanup:
  sqlite3SrcListDelete(pTableName);
  sqliteFree(zFile);
  return;
}




<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
Changes to src/parse.y.
︙︙
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

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








|








10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

**
*************************************************************************
** This file contains SQLite's grammar for SQL.  Process this file
** using the lemon parser generator to generate C code that runs
** the parser.  Lemon will also generate a header file containing
** numeric codes for all of the tokens.
**
** @(#) $Id: parse.y,v 1.119 2004/05/27 17:22:55 drh Exp $
*/
%token_prefix TK_
%token_type {Token}
%default_type {Token}
%extra_argument {Parse *pParse}
%syntax_error {
  if( pParse->zErrMsg==0 ){
︙︙
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132


// The following directive causes tokens ABORT, AFTER, ASC, etc. to
// fallback to ID if they will not parse as their original value.
// This obviates the need for the "id" nonterminal.
//
%fallback ID
  ABORT AFTER ASC ATTACH BEFORE BEGIN CASCADE CLUSTER CONFLICT
  COPY DATABASE DEFERRED DELIMITERS DESC DETACH EACH END EXPLAIN FAIL FOR
  GLOB IGNORE IMMEDIATE INITIALLY INSTEAD LIKE MATCH KEY
  OF OFFSET PRAGMA RAISE REPLACE RESTRICT ROW STATEMENT
  TEMP TRIGGER VACUUM VIEW.

// Define operator precedence early so that this is the first occurance
// of the operator tokens in the grammer.  Keeping the operators together
// causes them to be assigned integer values that are close together,








|








118
119
120
121
122
123
124
125
126
127
128
129
130
131
132


// The following directive causes tokens ABORT, AFTER, ASC, etc. to
// fallback to ID if they will not parse as their original value.
// This obviates the need for the "id" nonterminal.
//
%fallback ID
  ABORT AFTER ASC ATTACH BEFORE BEGIN CASCADE CLUSTER CONFLICT
  DATABASE DEFERRED DESC DETACH EACH END EXPLAIN FAIL FOR
  GLOB IGNORE IMMEDIATE INITIALLY INSTEAD LIKE MATCH KEY
  OF OFFSET PRAGMA RAISE REPLACE RESTRICT ROW STATEMENT
  TEMP TRIGGER VACUUM VIEW.

// Define operator precedence early so that this is the first occurance
// of the operator tokens in the grammer.  Keeping the operators together
// causes them to be assigned integer values that are close together,
︙︙
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775

///////////////////////////// The DROP INDEX command /////////////////////////
//

cmd ::= DROP INDEX nm(X) dbnm(Y).   {
  sqlite3DropIndex(pParse, sqlite3SrcListAppend(0,&X,&Y));
}


///////////////////////////// The COPY command ///////////////////////////////
//
cmd ::= COPY orconf(R) nm(X) dbnm(D) FROM nm(Y) USING DELIMITERS STRING(Z).
    {sqlite3Copy(pParse,sqlite3SrcListAppend(0,&X,&D),&Y,&Z,R);}
cmd ::= COPY orconf(R) nm(X) dbnm(D) FROM nm(Y).
    {sqlite3Copy(pParse,sqlite3SrcListAppend(0,&X,&D),&Y,0,R);}

///////////////////////////// The VACUUM command /////////////////////////////
//
cmd ::= VACUUM.                {sqlite3Vacuum(pParse,0);}
cmd ::= VACUUM nm(X).         {sqlite3Vacuum(pParse,&X);}

///////////////////////////// The PRAGMA command /////////////////////////////
//








<
<
<
<
<
<
<
<








754
755
756
757
758
759
760








761
762
763
764
765
766
767

///////////////////////////// The DROP INDEX command /////////////////////////
//

cmd ::= DROP INDEX nm(X) dbnm(Y).   {
  sqlite3DropIndex(pParse, sqlite3SrcListAppend(0,&X,&Y));
}









///////////////////////////// The VACUUM command /////////////////////////////
//
cmd ::= VACUUM.                {sqlite3Vacuum(pParse,0);}
cmd ::= VACUUM nm(X).         {sqlite3Vacuum(pParse,&X);}

///////////////////////////// The PRAGMA command /////////////////////////////
//
︙︙
Changes to src/sqliteInt.h. 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

/*
** 2001 September 15
**
** The author disclaims copyright to this source code.  In place of
** a legal notice, here is a blessing:
**
**    May you do good and not evil.
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.255 2004/05/27 13:35:20 danielk1977 Exp $
*/
#include "config.h"
#include "sqlite.h"
#include "hash.h"
#include "parse.h"
#include <stdio.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.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.256 2004/05/27 17:22:56 drh Exp $
*/
#include "config.h"
#include "sqlite.h"
#include "hash.h"
#include "parse.h"
#include <stdio.h>
#include <stdlib.h>
︙︙
1240
1241
1242
1243
1244
1245
1246
1247
1248
1249
1250
1251
1252
1253
1254

int sqlite3ExprCodeExprList(Parse*, ExprList*);
void sqlite3ExprIfTrue(Parse*, Expr*, int, int);
void sqlite3ExprIfFalse(Parse*, Expr*, int, int);
Table *sqlite3FindTable(sqlite*,const char*, const char*);
Table *sqlite3LocateTable(Parse*,const char*, const char*);
Index *sqlite3FindIndex(sqlite*,const char*, const char*);
void sqlite3UnlinkAndDeleteIndex(sqlite*,Index*);
void sqlite3Copy(Parse*, SrcList*, Token*, Token*, int);
void sqlite3Vacuum(Parse*, Token*);
int sqlite3RunVacuum(char**, sqlite*);
int sqlite3GlobCompare(const unsigned char*,const unsigned char*);
int sqlite3LikeCompare(const unsigned char*,const unsigned char*);
char *sqlite3TableNameFromToken(Token*);
int sqlite3ExprCheck(Parse*, Expr*, int, int*);
int sqlite3ExprType(Expr*);








<








1240
1241
1242
1243
1244
1245
1246

1247
1248
1249
1250
1251
1252
1253

int sqlite3ExprCodeExprList(Parse*, ExprList*);
void sqlite3ExprIfTrue(Parse*, Expr*, int, int);
void sqlite3ExprIfFalse(Parse*, Expr*, int, int);
Table *sqlite3FindTable(sqlite*,const char*, const char*);
Table *sqlite3LocateTable(Parse*,const char*, const char*);
Index *sqlite3FindIndex(sqlite*,const char*, const char*);
void sqlite3UnlinkAndDeleteIndex(sqlite*,Index*);

void sqlite3Vacuum(Parse*, Token*);
int sqlite3RunVacuum(char**, sqlite*);
int sqlite3GlobCompare(const unsigned char*,const unsigned char*);
int sqlite3LikeCompare(const unsigned char*,const unsigned char*);
char *sqlite3TableNameFromToken(Token*);
int sqlite3ExprCheck(Parse*, Expr*, int, int*);
int sqlite3ExprType(Expr*);
︙︙
Changes to src/tokenize.c.
︙︙
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

*************************************************************************
** An tokenizer for SQL
**
** This file contains C code that splits an SQL input string up into
** individual tokens and sends those tokens one-by-one over to the
** parser for analysis.
**
** $Id: tokenize.c,v 1.72 2004/05/27 13:35:20 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>
#include <stdlib.h>

/*








|








11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

*************************************************************************
** An tokenizer for SQL
**
** This file contains C code that splits an SQL input string up into
** individual tokens and sends those tokens one-by-one over to the
** parser for analysis.
**
** $Id: tokenize.c,v 1.73 2004/05/27 17:22:56 drh Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>
#include <stdlib.h>

/*
︙︙
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75

  { "CASE",              TK_CASE,         },
  { "CHECK",             TK_CHECK,        },
  { "CLUSTER",           TK_CLUSTER,      },
  { "COLLATE",           TK_COLLATE,      },
  { "COMMIT",            TK_COMMIT,       },
  { "CONFLICT",          TK_CONFLICT,     },
  { "CONSTRAINT",        TK_CONSTRAINT,   },
  { "COPY",              TK_COPY,         },
  { "CREATE",            TK_CREATE,       },
  { "CROSS",             TK_JOIN_KW,      },
  { "DATABASE",          TK_DATABASE,     },
  { "DEFAULT",           TK_DEFAULT,      },
  { "DEFERRED",          TK_DEFERRED,     },
  { "DEFERRABLE",        TK_DEFERRABLE,   },
  { "DELETE",            TK_DELETE,       },
  { "DELIMITERS",        TK_DELIMITERS,   },
  { "DESC",              TK_DESC,         },
  { "DETACH",            TK_DETACH,       },
  { "DISTINCT",          TK_DISTINCT,     },
  { "DROP",              TK_DROP,         },
  { "END",               TK_END,          },
  { "EACH",              TK_EACH,         },
  { "ELSE",              TK_ELSE,         },








<







<








53
54
55
56
57
58
59

60
61
62
63
64
65
66

67
68
69
70
71
72
73

  { "CASE",              TK_CASE,         },
  { "CHECK",             TK_CHECK,        },
  { "CLUSTER",           TK_CLUSTER,      },
  { "COLLATE",           TK_COLLATE,      },
  { "COMMIT",            TK_COMMIT,       },
  { "CONFLICT",          TK_CONFLICT,     },
  { "CONSTRAINT",        TK_CONSTRAINT,   },

  { "CREATE",            TK_CREATE,       },
  { "CROSS",             TK_JOIN_KW,      },
  { "DATABASE",          TK_DATABASE,     },
  { "DEFAULT",           TK_DEFAULT,      },
  { "DEFERRED",          TK_DEFERRED,     },
  { "DEFERRABLE",        TK_DEFERRABLE,   },
  { "DELETE",            TK_DELETE,       },

  { "DESC",              TK_DESC,         },
  { "DETACH",            TK_DETACH,       },
  { "DISTINCT",          TK_DISTINCT,     },
  { "DROP",              TK_DROP,         },
  { "END",               TK_END,          },
  { "EACH",              TK_EACH,         },
  { "ELSE",              TK_ELSE,         },
︙︙
692
693
694
695
696
697
698
699
700
701

      }
    }
    state = trans[state][token];
    zSql++;
  }
  return state==0;
}











<
<
<

690
691
692
693
694
695
696




      }
    }
    state = trans[state][token];
    zSql++;
  }
  return state==0;
}
Changes to src/vdbe.c.
︙︙
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53

**
** Various scripts scan this source file in order to generate HTML
** documentation, headers files, or other derived files.  The formatting
** of the code in this file is, therefore, important.  See other comments
** in this file for details.  If in doubt, do not deviate from existing
** commenting and indentation practices when changing or adding code.
**
** $Id: vdbe.c,v 1.339 2004/05/27 09:28:43 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>
#include "vdbeInt.h"

/*








|








39
40
41
42
43
44
45
46
47
48
49
50
51
52
53

**
** Various scripts scan this source file in order to generate HTML
** documentation, headers files, or other derived files.  The formatting
** of the code in this file is, therefore, important.  See other comments
** in this file for details.  If in doubt, do not deviate from existing
** commenting and indentation practices when changing or adding code.
**
** $Id: vdbe.c,v 1.340 2004/05/27 17:22:56 drh Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>
#include "vdbeInt.h"

/*
︙︙
4244
4245
4246
4247
4248
4249
4250
4251
4252
4253
4254
4255
4256
4257
4258
4259
4260
4261
4262
4263
4264
4265
4266
4267
4268
4269
4270
4271
4272
4273
4274
4275
4276
4277
4278
4279
4280
4281
4282
4283
4284
4285
4286
4287
4288
4289
4290
4291
4292
4293
4294
4295
4296
4297
4298
4299
4300
4301
4302
4303
4304
4305
4306
4307
4308
4309
4310
4311
4312
4313
4314
4315
4316
4317
4318
4319
4320
4321
4322
4323
4324
4325
4326
4327
4328
4329
4330
4331
4332
4333
4334
4335
4336
4337
4338
4339
4340
4341
4342
4343
4344
4345
4346
4347
4348
4349
4350
4351
4352
4353
4354
4355
4356
4357
4358
4359
4360
4361
4362
4363
4364
4365
4366
4367
4368
4369
4370
4371
4372
4373
4374
4375
4376
4377
4378
4379
4380
4381
4382
4383
4384
4385
4386
4387
4388
4389
4390
4391
4392
4393
4394
4395
4396
4397
4398
4399
4400
4401
4402
4403
4404
4405
4406
4407
4408
4409
4410
4411
4412
4413
4414
4415
4416
4417
4418
4419
4420
4421
4422
4423
4424
4425
4426
4427
4428
4429
4430
4431


/* Opcode: SortReset * * *
**
** Remove any elements that remain on the sorter.
*/
case OP_SortReset: {
  sqlite3VdbeSorterReset(p);
  break;
}

/* Opcode: FileOpen * * P3
**
** Open the file named by P3 for reading using the FileRead opcode.
** If P3 is "stdin" then open standard input for reading.
*/
case OP_FileOpen: {
  assert( pOp->p3!=0 );
  if( p->pFile ){
    if( p->pFile!=stdin ) fclose(p->pFile);
    p->pFile = 0;
  }
  if( sqlite3StrICmp(pOp->p3,"stdin")==0 ){
    p->pFile = stdin;
  }else{
    p->pFile = fopen(pOp->p3, "r");
  }
  if( p->pFile==0 ){
    sqlite3SetString(&p->zErrMsg,"unable to open file: ", pOp->p3, (char*)0);
    rc = SQLITE_ERROR;
  }
  break;
}

/* Opcode: FileRead P1 P2 P3
**
** Read a single line of input from the open file (the file opened using
** FileOpen).  If we reach end-of-file, jump immediately to P2.  If
** we are able to get another line, split the line apart using P3 as
** a delimiter.  There should be P1 fields.  If the input line contains
** more than P1 fields, ignore the excess.  If the input line contains
** fewer than P1 fields, assume the remaining fields contain NULLs.
**
** Input ends if a line consists of just "\.".  A field containing only
** "\N" is a null field.  The backslash \ character can be used be used
** to escape newlines or the delimiter.
*/
case OP_FileRead: {
  int n, eol, nField, i, c, nDelim;
  char *zDelim, *z;
  CHECK_FOR_INTERRUPT;
  if( p->pFile==0 ) goto fileread_jump;
  nField = pOp->p1;
  if( nField<=0 ) goto fileread_jump;
  if( nField!=p->nField || p->azField==0 ){
    char **azField = sqliteRealloc(p->azField, sizeof(char*)*nField+1);
    if( azField==0 ){ goto no_mem; }
    p->azField = azField;
    p->nField = nField;
  }
  n = 0;
  eol = 0;
  while( eol==0 ){
    if( p->zLine==0 || n+200>p->nLineAlloc ){
      char *zLine;
      p->nLineAlloc = p->nLineAlloc*2 + 300;
      zLine = sqliteRealloc(p->zLine, p->nLineAlloc);
      if( zLine==0 ){
        p->nLineAlloc = 0;
        sqliteFree(p->zLine);
        p->zLine = 0;
        goto no_mem;
      }
      p->zLine = zLine;
    }
    if( vdbe_fgets(&p->zLine[n], p->nLineAlloc-n, p->pFile)==0 ){
      eol = 1;
      p->zLine[n] = 0;
    }else{
      int c;
      while( (c = p->zLine[n])!=0 ){
        if( c=='\\' ){
          if( p->zLine[n+1]==0 ) break;
          n += 2;
        }else if( c=='\n' ){
          p->zLine[n] = 0;
          eol = 1;
          break;
        }else{
          n++;
        }
      }
    }
  }
  if( n==0 ) goto fileread_jump;
  z = p->zLine;
  if( z[0]=='\\' && z[1]=='.' && z[2]==0 ){
    goto fileread_jump;
  }
  zDelim = pOp->p3;
  if( zDelim==0 ) zDelim = "\t";
  c = zDelim[0];
  nDelim = strlen(zDelim);
  p->azField[0] = z;
  for(i=1; *z!=0 && i<=nField; i++){
    int from, to;
    from = to = 0;
    if( z[0]=='\\' && z[1]=='N' 
       && (z[2]==0 || strncmp(&z[2],zDelim,nDelim)==0) ){
      if( i<=nField ) p->azField[i-1] = 0;
      z += 2 + nDelim;
      if( i<nField ) p->azField[i] = z;
      continue;
    }
    while( z[from] ){
      if( z[from]=='\\' && z[from+1]!=0 ){
        int tx = z[from+1];
        switch( tx ){
          case 'b':  tx = '\b'; break;
          case 'f':  tx = '\f'; break;
          case 'n':  tx = '\n'; break;
          case 'r':  tx = '\r'; break;
          case 't':  tx = '\t'; break;
          case 'v':  tx = '\v'; break;
          default:   break;
        }
        z[to++] = tx;
        from += 2;
        continue;
      }
      if( z[from]==c && strncmp(&z[from],zDelim,nDelim)==0 ) break;
      z[to++] = z[from++];
    }
    if( z[from] ){
      z[to] = 0;
      z += from + nDelim;
      if( i<nField ) p->azField[i] = z;
    }else{
      z[to] = 0;
      z = "";
    }
  }
  while( i<nField ){
    p->azField[i++] = 0;
  }
  break;

  /* If we reach end-of-file, or if anything goes wrong, jump here.
  ** This code will cause a jump to P2 */
fileread_jump:
  pc = pOp->p2 - 1;
  break;
}

/* Opcode: FileColumn P1 * *
**
** Push onto the stack the P1-th column of the most recently read line
** from the input file.
*/
case OP_FileColumn: {
/*
** FIX ME: This will be deleted, but loads of test case files have
** to be updated first...
*/
  int i = pOp->p1;
  char *z;
  assert( i>=0 && i<p->nField );
  if( p->azField ){
    z = p->azField[i];
  }else{
    z = 0;
  }
  pTos++;
  if( z ){
    pTos->n = strlen(z);
    pTos->z = z;
    pTos->flags = MEM_Str | MEM_Ephem | MEM_Term;
    pTos->enc = TEXT_Utf8;
    sqlite3VdbeChangeEncoding(pTos, db->enc);
  }else{
    pTos->flags = MEM_Null;
  }
  break;
}

/* Opcode: MemStore P1 P2 *
**
** Write the top of the stack into memory location P1.
** P1 should be a small integer since space is allocated








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








4244
4245
4246
4247
4248
4249
4250














































































































































































4251
4252
4253
4254
4255
4256
4257


/* Opcode: SortReset * * *
**
** Remove any elements that remain on the sorter.
*/
case OP_SortReset: {
  sqlite3VdbeSorterReset(p);














































































































































































  break;
}

/* Opcode: MemStore P1 P2 *
**
** Write the top of the stack into memory location P1.
** P1 should be a small integer since space is allocated
︙︙
Changes to test/auth.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 script is testing the ATTACH and DETACH commands
# and related functionality.
#
# $Id: auth.test,v 1.12 2003/12/07 00:24:35 drh Exp $
#

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

# disable this test if the SQLITE_OMIT_AUTHORIZATION macro is
# defined during compilation.








|








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 script is testing the ATTACH and DETACH commands
# and related functionality.
#
# $Id: auth.test,v 1.13 2004/05/27 17:22:56 drh Exp $
#

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

# disable this test if the SQLITE_OMIT_AUTHORIZATION macro is
# defined during compilation.
︙︙
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477

      return SQLITE_OK
    }
    return SQLITE_OK
  }
  catchsql {SELECT * FROM t2}
} {0 {11 2 33}}

set f [open data1.txt w]
puts $f "7:8:9"
close $f
do_test auth-1.54 {
  proc auth {code arg1 arg2 arg3 arg4} {
    if {$code=="SQLITE_COPY"} {
      set ::authargs [list $arg1 $arg2 $arg3 $arg4]
      return SQLITE_DENY
    }
    return SQLITE_OK
  }
  catchsql {COPY t2 FROM 'data1.txt' USING DELIMITERS ':'}
} {1 {not authorized}}
do_test auth-1.55 {
  set ::authargs
} {t2 data1.txt main {}}
do_test auth-1.56 {
  execsql {SELECT * FROM t2}
} {11 2 33}
do_test auth-1.57 {
  proc auth {code arg1 arg2 arg3 arg4} {
    if {$code=="SQLITE_COPY"} {
      set ::authargs [list $arg1 $arg2 $arg3 $arg4]
      return SQLITE_IGNORE
    }
    return SQLITE_OK
  }
  catchsql {COPY t2 FROM 'data1.txt' USING DELIMITERS ':'}
} {0 {}}
do_test auth-1.58 {
  set ::authargs
} {t2 data1.txt main {}}
do_test auth-1.59 {
  execsql {SELECT * FROM t2}
} {11 2 33}
do_test auth-1.60 {
  proc auth {code arg1 arg2 arg3 arg4} {
    if {$code=="SQLITE_COPY"} {
      set ::authargs [list $arg1 $arg2 $arg3 $arg4]
      return SQLITE_OK
    }
    return SQLITE_OK
  }
  catchsql {COPY t2 FROM 'data1.txt' USING DELIMITERS ':'}
} {0 {}}
do_test auth-1.61 {
  set ::authargs
} {t2 data1.txt main {}}
do_test auth-1.62 {
  execsql {SELECT * FROM t2}
} {11 2 33 7 8 9}

do_test auth-1.63 {
  proc auth {code arg1 arg2 arg3 arg4} {
    if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} {
       return SQLITE_DENY
    }
    return SQLITE_OK








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








413
414
415
416
417
418
419



















































420
421
422
423
424
425
426

      return SQLITE_OK
    }
    return SQLITE_OK
  }
  catchsql {SELECT * FROM t2}
} {0 {11 2 33}}





















































do_test auth-1.63 {
  proc auth {code arg1 arg2 arg3 arg4} {
    if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} {
       return SQLITE_DENY
    }
    return SQLITE_OK
︙︙
Deleted test/copy.test. 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265

# 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 COPY statement.
#
# $Id: copy.test,v 1.17 2004/02/17 18:26:57 dougcurrie Exp $

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

# Create a file of data from which to copy.
#
set f [open data1.txt w]
puts $f "11\t22\t33"
puts $f "22\t33\t11"
close $f
set f [open data2.txt w]
puts $f "11\t22\t33"
puts $f "\\."
puts $f "22\t33\t11"
close $f
set f [open data3.txt w]
puts $f "11\t22\t33\t44"
puts $f "22\t33\t11"
close $f
set f [open data4.txt w]
puts $f "11 | 22 | 33"
puts $f "22 | 33 | 11"
close $f
set f [open data5.txt w]
puts $f "11|22|33"
puts $f "22|33|11"
close $f
set f [open dataX.txt w]
fconfigure $f -translation binary 
puts -nonewline $f "11|22|33\r"
puts -nonewline $f "22|33|44\r\n"
puts -nonewline $f "33|44|55\n"
puts -nonewline $f "44|55|66\r"
puts -nonewline $f "55|66|77\r\n"
puts -nonewline $f "66|77|88\n"
close $f

# Try to COPY into a non-existant table.
#
do_test copy-1.1 {
  set v [catch {execsql {COPY test1 FROM 'data1.txt'}} msg]
  lappend v $msg
} {1 {no such table: test1}}

# Try to insert into sqlite_master
#
do_test copy-1.2 {
  set v [catch {execsql {COPY sqlite_master FROM 'data2.txt'}} msg]
  lappend v $msg
} {1 {table sqlite_master may not be modified}}

# Do some actual inserts
#
do_test copy-1.3 {
  execsql {CREATE TABLE test1(one int, two int, three int)}
  execsql {COPY test1 FROM 'data1.txt'}
  execsql {SELECT * FROM test1 ORDER BY one}
} {11 22 33 22 33 11}

# Make sure input terminates at \.
#
do_test copy-1.4 {
  execsql {DELETE FROM test1}
  execsql {COPY test1 FROM 'data2.txt'}
  execsql {SELECT * FROM test1 ORDER BY one}
} {11 22 33}

# Test out the USING DELIMITERS clause
#
do_test copy-1.5 {
  execsql {DELETE FROM test1}
  execsql {COPY test1 FROM 'data4.txt' USING DELIMITERS ' | '}
  execsql {SELECT * FROM test1 ORDER BY one}
} {11 22 33 22 33 11}
do_test copy-1.6 {
  execsql {DELETE FROM test1}
  execsql {COPY test1 FROM 'data5.txt' USING DELIMITERS '|'}
  execsql {SELECT * FROM test1 ORDER BY one}
} {11 22 33 22 33 11}
do_test copy-1.7 {
  execsql {DELETE FROM test1}
  execsql {COPY test1 FROM 'data4.txt' USING DELIMITERS '|'}
  execsql {SELECT * FROM test1 ORDER BY one}
} {{11 } { 22 } { 33} {22 } { 33 } { 11}}

# Try copying into a table that has one or more indices.
#
do_test copy-1.8 {
  execsql {DELETE FROM test1}
  execsql {CREATE INDEX index1 ON test1(one)}
  execsql {CREATE INDEX index2 ON test1(two)}
  execsql {CREATE INDEX index3 ON test1(three)}
  execsql {COPY test1 from 'data1.txt'}
  execsql {SELECT * FROM test1 WHERE one=11}
} {11 22 33}
do_test copy-1.8b {
  execsql {SELECT * FROM test1 WHERE one=22}
} {22 33 11}
do_test copy-1.8c {
  execsql {SELECT * FROM test1 WHERE two=22}
} {11 22 33}
do_test copy-1.8d {
  execsql {SELECT * FROM test1 WHERE three=11}
} {22 33 11}


# Try inserting really long data
#
set x {}
for {set i 0} {$i<100} {incr i} {
  append x "($i)-abcdefghijklmnopqrstyvwxyz-ABCDEFGHIJKLMNOPQRSTUVWXYZ-"
}
do_test copy-2.1 {
  execsql {CREATE TABLE test2(a int, x text)}
  set f [open data21.txt w]
  puts $f "123\t$x"
  close $f
  execsql {COPY test2 FROM 'data21.txt'}
  execsql {SELECT x from test2}
} $x
file delete -force data21.txt

# Test the escape character mechanism
#
do_test copy-3.1 {
  set fd [open data6.txt w]
  puts $fd "hello\\\tworld\t1"
  puts $fd "hello\tworld\\\t2"
  close $fd
  execsql {
    CREATE TABLE t1(a text, b text);
    COPY t1 FROM 'data6.txt';
    SELECT * FROM t1 ORDER BY a;
  }
} {hello {world	2} {hello	world} 1}
do_test copy-3.2 {
  set fd [open data6.txt w]
  puts $fd "1\thello\\\nworld"
  puts $fd "2\thello world"
  close $fd
  execsql {
    DELETE FROM t1;
    COPY t1 FROM 'data6.txt';
    SELECT * FROM t1 ORDER BY a;
  }
} {1 {hello
world} 2 {hello world}}
do_test copy-3.3 {
  set fd [open data6.txt w]
  puts $fd "1:hello\\b\\f\\n\\r\\t\\vworld"
  puts $fd "2:hello world"
  close $fd
  execsql {
    DELETE FROM t1;
    COPY t1 FROM 'data6.txt' USING DELIMITERS ':';
    SELECT * FROM t1 ORDER BY a;
  }
} [list 1 "hello\b\f\n\r\t\vworld" 2 "hello world"]

# Test the embedded NULL logic.
#
do_test copy-4.1 {
  set fd [open data6.txt w]
  puts $fd "1\t\\N"
  puts $fd "\\N\thello world"
  close $fd
  execsql {
    DELETE FROM t1;
    COPY t1 FROM 'data6.txt';
    SELECT * FROM t1 WHERE a IS NULL;
  }
} {{} {hello world}}
do_test copy-4.2 {
  execsql {
    SELECT * FROM t1 WHERE b IS NULL;
  }
} {1 {}}

# Test the conflict resolution logic for COPY
#
do_test copy-5.1 {
  execsql {
    DROP TABLE t1;
    CREATE TABLE t1(a INTEGER PRIMARY KEY, b UNIQUE, c);
    COPY t1 FROM 'data5.txt' USING DELIMITERS '|';
    SELECT * FROM t1;
  }
} {11 22 33 22 33 11}
do_test copy-5.2 {
  set fd [open data6.txt w]
  puts $fd "33|22|44"
  close $fd
  catchsql {
    COPY t1 FROM 'data6.txt' USING DELIMITERS '|';
    SELECT * FROM t1;
  }
} {1 {column b is not unique}}
do_test copy-5.3 {
  set fd [open data6.txt w]
  puts $fd "33|22|44"
  close $fd
  catchsql {
    COPY OR IGNORE t1 FROM 'data6.txt' USING DELIMITERS '|';
    SELECT * FROM t1;
  }
} {0 {11 22 33 22 33 11}}
do_test copy-5.4 {
  set fd [open data6.txt w]
  puts $fd "33|22|44"
  close $fd
  catchsql {
    COPY OR REPLACE t1 FROM 'data6.txt' USING DELIMITERS '|';
    SELECT * FROM t1;
  }
} {0 {22 33 11 33 22 44}}

do_test copy-5.5 {
  execsql {
    DELETE FROM t1;
    PRAGMA count_changes=on;
    COPY t1 FROM 'data5.txt' USING DELIMITERS '|';
  }
} {2}
do_test copy-5.6 {
  execsql {
    COPY OR REPLACE t1 FROM 'data5.txt' USING DELIMITERS '|';
  }
} {2}
do_test copy-5.7 {
  execsql {
    COPY OR IGNORE t1 FROM 'data5.txt' USING DELIMITERS '|';
  }
} {0}

do_test copy-6.1 {
  execsql {
    PRAGMA count_changes=off;
    CREATE TABLE t2(a,b,c);
    COPY t2 FROM 'dataX.txt' USING DELIMITERS '|';
    SELECT * FROM t2;
  }
} {11 22 33 22 33 44 33 44 55 44 55 66 55 66 77 66 77 88}

integrity_check copy-7.1

# Cleanup 
#
#file delete -force data1.txt data2.txt data3.txt data4.txt data5.txt \
                   data6.txt dataX.txt

finish_test

<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
Changes to test/delete.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 DELETE FROM statement.
#
# $Id: delete.test,v 1.13 2003/06/15 23:42:25 drh Exp $

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

# Try to delete from a non-existant table.
#
do_test delete-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 DELETE FROM statement.
#
# $Id: delete.test,v 1.14 2004/05/27 17:22:56 drh Exp $

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

# Try to delete from a non-existant table.
#
do_test delete-1.1 {
︙︙
148
149
150
151
152
153
154
155
156
157
158
159
160
161

162

163
164
165
166
167
168
169
170
171

integrity_check delete-5.8


# Delete large quantities of data.  We want to test the List overflow
# mechanism in the vdbe.
#
do_test delete-6.1 {
  set fd [open data1.txt w]
  for {set i 1} {$i<=3000} {incr i} {
    puts $fd "[expr {$i}]\t[expr {$i*$i}]"
  }
  close $fd
  execsql {DELETE FROM table1}
  execsql {COPY table1 FROM 'data1.txt'}

  execsql {DELETE FROM table2}

  execsql {COPY table2 FROM 'data1.txt'}
  file delete data1.txt
  execsql {SELECT count(*) FROM table1}
} {3000}
do_test delete-6.2 {
  execsql {SELECT count(*) FROM table2}
} {3000}
do_test delete-6.3 {
  execsql {SELECT f1 FROM table1 WHERE f1<10 ORDER BY f1}








|

|

<
|
<
>
|
>
|
<








148
149
150
151
152
153
154
155
156
157
158

159

160
161
162
163

164
165
166
167
168
169
170

integrity_check delete-5.8


# Delete large quantities of data.  We want to test the List overflow
# mechanism in the vdbe.
#
do_test delete-6.1 {
  execsql {BEGIN; DELETE FROM table1}
  for {set i 1} {$i<=3000} {incr i} {
    execsql "INSERT INTO table1 VALUES($i,[expr {$i*$i}])"
  }

  execsql {DELETE FROM table2}

  for {set i 1} {$i<=3000} {incr i} {
    execsql "INSERT INTO table2 VALUES($i,[expr {$i*$i}])"
  }
  execsql {COMMIT}

  execsql {SELECT count(*) FROM table1}
} {3000}
do_test delete-6.2 {
  execsql {SELECT count(*) FROM table2}
} {3000}
do_test delete-6.3 {
  execsql {SELECT f1 FROM table1 WHERE f1<10 ORDER BY f1}
︙︙
Changes to test/enc2.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 the SQLite routines used for converting between the
# various suported unicode encodings (UTF-8, UTF-16, UTF-16le and
# UTF-16be).
#
# $Id: enc2.test,v 1.3 2004/05/27 01:53:56 drh Exp $

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

db close

# Return the UTF-8 representation of the supplied UTF-16 string $str. 








|








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 the SQLite routines used for converting between the
# various suported unicode encodings (UTF-8, UTF-16, UTF-16le and
# UTF-16be).
#
# $Id: enc2.test,v 1.4 2004/05/27 17:22:56 drh Exp $

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

db close

# Return the UTF-8 representation of the supplied UTF-16 string $str.
︙︙
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69




70
71
72
73
74
75
76


# Insert some data
do_test $t.2 {
  execsql {INSERT INTO t1 VALUES('two', 'II', 2);}
  execsql {SELECT * FROM t1}
} {one I 1 two II 2}

# Insert some data using the COPY command.
do_test $t.3 {
  set f [open data.txt w]
  puts $f "three\tIII\t3"
  puts $f "four\tIV\t4"
  puts $f "five\tV\t5"
  close $f
  execsql {COPY t1 FROM 'data.txt'}




  execsql {SELECT * FROM t1}
} {one I 1 two II 2 three III 3 four IV 4 five V 5}

# Use the index
do_test $t.4 {
  execsql {
    SELECT * FROM t1 WHERE a = 'one';








|

<
<
<
<
<
|
>
>
>
>








55
56
57
58
59
60
61
62
63





64
65
66
67
68
69
70
71
72
73
74
75


# Insert some data
do_test $t.2 {
  execsql {INSERT INTO t1 VALUES('two', 'II', 2);}
  execsql {SELECT * FROM t1}
} {one I 1 two II 2}

# Insert some data 
do_test $t.3 {





  execsql {
    INSERT INTO t1 VALUES('three','III',3);
    INSERT INTO t1 VALUES('four','IV',4);
    INSERT INTO t1 VALUES('five','V',5);
  }
  execsql {SELECT * FROM t1}
} {one I 1 two II 2 three III 3 four IV 4 five V 5}

# Use the index
do_test $t.4 {
  execsql {
    SELECT * FROM t1 WHERE a = 'one';
︙︙
Deleted test/format3.test. 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
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

# 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 the library is able to correctly
# handle file-format 3 (version 2.6.x) databases.
#
# $Id: format3.test,v 1.4 2003/12/23 02:17:35 drh Exp $

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

# Create a bunch of data to sort against
#
do_test format3-1.0 {
  set fd [open data.txt w]
  puts $fd "1\tone\t0\tI\t3.141592653"
  puts $fd "2\ttwo\t1\tII\t2.15"
  puts $fd "3\tthree\t1\tIII\t4221.0"
  puts $fd "4\tfour\t2\tIV\t-0.0013442"
  puts $fd "5\tfive\t2\tV\t-11"
  puts $fd "6\tsix\t2\tVI\t0.123"
  puts $fd "7\tseven\t2\tVII\t123.0"
  puts $fd "8\teight\t3\tVIII\t-1.6"
  close $fd
  execsql {
    CREATE TABLE t1(
       n int,
       v varchar(10),
       log int,
       roman varchar(10),
       flt real
    );
    COPY t1 FROM 'data.txt'
  }
  file delete data.txt
  db close
  set ::bt [btree_open test.db]
  btree_begin_transaction $::bt
  set m [btree_get_meta $::bt]
  set m [lreplace $m 2 2 3]
  eval btree_update_meta $::bt $m
  btree_commit $::bt
  btree_close $::bt
  sqlite db test.db
  execsql {SELECT count(*) FROM t1}
} {8}

do_test format3-1.1 {
  execsql {SELECT n FROM t1 ORDER BY n}
} {1 2 3 4 5 6 7 8}
do_test format3-1.1.1 {
  execsql {SELECT n FROM t1 ORDER BY n ASC}
} {1 2 3 4 5 6 7 8}
do_test format3-1.1.1 {
  execsql {SELECT ALL n FROM t1 ORDER BY n ASC}
} {1 2 3 4 5 6 7 8}
do_test format3-1.2 {
  execsql {SELECT n FROM t1 ORDER BY n DESC}
} {8 7 6 5 4 3 2 1}
do_test format3-1.3a {
  execsql {SELECT v FROM t1 ORDER BY v}
} {eight five four one seven six three two}
do_test format3-1.3b {
  execsql {SELECT n FROM t1 ORDER BY v}
} {8 5 4 1 7 6 3 2}
do_test format3-1.4 {
  execsql {SELECT n FROM t1 ORDER BY v DESC}
} {2 3 6 7 1 4 5 8}
do_test format3-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 format3-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 format3-1.7 {
  execsql {SELECT roman FROM t1 ORDER BY roman}
} {I II III IV V VI VII VIII}
do_test format3-1.8 {
  execsql {SELECT n FROM t1 ORDER BY log, flt}
} {1 2 3 5 4 6 7 8}
do_test format3-1.8.1 {
  execsql {SELECT n FROM t1 ORDER BY log asc, flt}
} {1 2 3 5 4 6 7 8}
do_test format3-1.8.2 {
  execsql {SELECT n FROM t1 ORDER BY log, flt ASC}
} {1 2 3 5 4 6 7 8}
do_test format3-1.8.3 {
  execsql {SELECT n FROM t1 ORDER BY log ASC, flt asc}
} {1 2 3 5 4 6 7 8}
do_test format3-1.9 {
  execsql {SELECT n FROM t1 ORDER BY log, flt DESC}
} {1 3 2 7 6 4 5 8}
do_test format3-1.9.1 {
  execsql {SELECT n FROM t1 ORDER BY log ASC, flt DESC}
} {1 3 2 7 6 4 5 8}
do_test format3-1.10 {
  execsql {SELECT n FROM t1 ORDER BY log DESC, flt}
} {8 5 4 6 7 2 3 1}
do_test format3-1.11 {
  execsql {SELECT n FROM t1 ORDER BY log DESC, flt DESC}
} {8 7 6 4 5 3 2 1}

# These tests are designed to reach some hard-to-reach places
# inside the string comparison routines.
#
# (Later) The sorting behavior changed in 2.7.0.  But we will
# keep these tests.  You can never have too many test cases!
#
do_test format3-2.1.1 {
  execsql {
    UPDATE t1 SET v='x' || -flt;
    UPDATE t1 SET v='x-2b' where v=='x-0.123';
    SELECT v FROM t1 ORDER BY v;
  }
} {x-123 x-2.15 x-2b x-3.141592653 x-4221 x0.0013442 x1.6 x11}
do_test format3-2.1.2 {
  execsql {
    SELECT v FROM t1 ORDER BY substr(v,2,999);
  }
} {x-4221 x-123 x-3.141592653 x-2.15 x0.0013442 x1.6 x11 x-2b}
do_test format3-2.1.3 {
  execsql {
    SELECT v FROM t1 ORDER BY substr(v,2,999)+0.0;
  }
} {x-4221 x-123 x-3.141592653 x-2.15 x-2b x0.0013442 x1.6 x11}
do_test format3-2.1.4 {
  execsql {
    SELECT v FROM t1 ORDER BY substr(v,2,999) DESC;
  }
} {x-2b x11 x1.6 x0.0013442 x-2.15 x-3.141592653 x-123 x-4221}
do_test format3-2.1.5 {
  execsql {
    SELECT v FROM t1 ORDER BY substr(v,2,999)+0.0 DESC;
  }
} {x11 x1.6 x0.0013442 x-2b x-2.15 x-3.141592653 x-123 x-4221}

# This is a bug fix for 2.2.4.
# Strings are normally mapped to upper-case for a caseless comparison.
# But this can cause problems for characters in between 'Z' and 'a'.
#
do_test format3-3.1 {
  execsql {
    CREATE TABLE t2(a,b);
    INSERT INTO t2 VALUES('AGLIENTU',1);
    INSERT INTO t2 VALUES('AGLIE`',2);
    INSERT INTO t2 VALUES('AGNA',3);
    SELECT a, b FROM t2 ORDER BY a;
  }
} {AGLIENTU 1 AGLIE` 2 AGNA 3}
do_test format3-3.2 {
  execsql {
    SELECT a, b FROM t2 ORDER BY a DESC;
  }
} {AGNA 3 AGLIE` 2 AGLIENTU 1}
do_test format3-3.3 {
  execsql {
    DELETE FROM t2;
    INSERT INTO t2 VALUES('aglientu',1);
    INSERT INTO t2 VALUES('aglie`',2);
    INSERT INTO t2 VALUES('agna',3);
    SELECT a, b FROM t2 ORDER BY a;
  }
} {aglie` 2 aglientu 1 agna 3}
do_test format3-3.4 {
  execsql {
    SELECT a, b FROM t2 ORDER BY a DESC;
  }
} {agna 3 aglientu 1 aglie` 2}

# Version 2.7.0 testing.
#
do_test format3-4.1 {
  execsql {
    INSERT INTO t1 VALUES(9,'x2.7',3,'IX',4.0e5);
    INSERT INTO t1 VALUES(10,'x5.0e10',3,'X',-4.0e5);
    INSERT INTO t1 VALUES(11,'x-4.0e9',3,'XI',4.1e4);
    INSERT INTO t1 VALUES(12,'x01234567890123456789',3,'XII',-4.2e3);
    SELECT n FROM t1 ORDER BY n;
  }
} {1 2 3 4 5 6 7 8 9 10 11 12}
do_test format3-4.2 {
  execsql {
    SELECT n||'' FROM t1 ORDER BY 1;
  }
} {1 2 3 4 5 6 7 8 9 10 11 12}
do_test format3-4.3 {
  execsql {
    SELECT n+0 FROM t1 ORDER BY 1;
  }
} {1 2 3 4 5 6 7 8 9 10 11 12}
do_test format3-4.4 {
  execsql {
    SELECT n||'' FROM t1 ORDER BY 1 DESC;
  }
} {12 11 10 9 8 7 6 5 4 3 2 1}
do_test format3-4.5 {
  execsql {
    SELECT n+0 FROM t1 ORDER BY 1 DESC;
  }
} {12 11 10 9 8 7 6 5 4 3 2 1}
do_test format3-4.6 {
  execsql {
    SELECT v FROM t1 ORDER BY 1;
  }
} {x-123 x-2.15 x-2b x-3.141592653 x-4.0e9 x-4221 x0.0013442 x01234567890123456789 x1.6 x11 x2.7 x5.0e10}
do_test format3-4.7 {
  execsql {
    SELECT v FROM t1 ORDER BY 1 DESC;
  }
} {x5.0e10 x2.7 x11 x1.6 x01234567890123456789 x0.0013442 x-4221 x-4.0e9 x-3.141592653 x-2b x-2.15 x-123}
do_test format3-4.8 {
  execsql {
    SELECT substr(v,2,99) FROM t1 ORDER BY 1;
  }
} {-4.0e9 -4221 -123 -3.141592653 -2.15 0.0013442 1.6 2.7 11 5.0e10 01234567890123456789 -2b}

# Build some new test data, this time with indices.
#
do_test format3-5.0 {
  execsql {
    DROP TABLE t1;
    CREATE TABLE t1(w int, x text, y blob);
    DROP TABLE t2;
    CREATE TABLE t2(p varchar(1), q clob, r real, s numeric(8));
  }
  for {set i 1} {$i<=100} {incr i} {
    set w $i
    set x [expr {int(log($i)/log(2))}]
    set y [expr {$i*$i + 2*$i + 1}]
    execsql "INSERT INTO t1 VALUES($w,$x,$y)"
  }
  execsql {
    INSERT INTO t2 SELECT 101-w, x, (SELECT max(y) FROM t1)+1-y, y FROM t1;
    CREATE INDEX i1w ON t1(w);
    CREATE INDEX i1xy ON t1(x,y);
    CREATE INDEX i2p ON t2(p);
    CREATE INDEX i2r ON t2(r);
    CREATE INDEX i2qs ON t2(q, s);
  }
} {}

# Do an SQL statement.  Append the search count to the end of the result.
#
proc count sql {
  set ::sqlite_search_count 0
  return [concat [execsql $sql] $::sqlite_search_count]
}

# Verify that queries use an index.  We are using the special variable
# "sqlite_search_count" which tallys the number of executions of MoveTo
# and Next operators in the VDBE.  By verifing that the search count is
# small we can be assured that indices are being used properly.
#
do_test format3-5.1 {
  db close
  sqlite db test.db
  count {SELECT x, y FROM t1 WHERE w=10}
} {3 121 3}
do_test format3-5.2 {
  count {SELECT x, y FROM t1 WHERE w=11}
} {3 144 3}
do_test format3-5.3 {
  count {SELECT x, y FROM t1 WHERE 11=w}
} {3 144 3}
do_test format3-5.4 {
  count {SELECT x, y FROM t1 WHERE 11=w AND x>2}
} {3 144 3}
do_test format3-5.5 {
  count {SELECT x, y FROM t1 WHERE y<200 AND w=11 AND x>2}
} {3 144 3}
do_test format3-5.6 {
  count {SELECT x, y FROM t1 WHERE y<200 AND x>2 AND w=11}
} {3 144 3}
do_test format3-5.7 {
  count {SELECT x, y FROM t1 WHERE w=11 AND y<200 AND x>2}
} {3 144 3}
do_test format3-5.8 {
  count {SELECT x, y FROM t1 WHERE w>10 AND y=144 AND x=3}
} {3 144 3}
do_test format3-5.9 {
  count {SELECT x, y FROM t1 WHERE y=144 AND w>10 AND x=3}
} {3 144 3}
do_test format3-5.10 {
  count {SELECT x, y FROM t1 WHERE x=3 AND w>=10 AND y=121}
} {3 121 3}
do_test format3-5.11 {
  count {SELECT x, y FROM t1 WHERE x=3 AND y=100 AND w<10}
} {3 100 3}

# New for SQLite version 2.1: Verify that that inequality constraints
# are used correctly.
#
do_test format3-5.12 {
  count {SELECT w FROM t1 WHERE x=3 AND y<100}
} {8 3}
do_test format3-5.13 {
  count {SELECT w FROM t1 WHERE x=3 AND 100>y}
} {8 3}
do_test format3-5.14 {
  count {SELECT w FROM t1 WHERE 3=x AND y<100}
} {8 3}
do_test format3-5.15 {
  count {SELECT w FROM t1 WHERE 3=x AND 100>y}
} {8 3}
do_test format3-5.16 {
  count {SELECT w FROM t1 WHERE x=3 AND y<=100}
} {8 9 5}
do_test format3-5.17 {
  count {SELECT w FROM t1 WHERE x=3 AND 100>=y}
} {8 9 5}
do_test format3-5.18 {
  count {SELECT w FROM t1 WHERE x=3 AND y>225}
} {15 3}
do_test format3-5.19 {
  count {SELECT w FROM t1 WHERE x=3 AND 225<y}
} {15 3}
do_test format3-5.20 {
  count {SELECT w FROM t1 WHERE x=3 AND y>=225}
} {14 15 5}
do_test format3-5.21 {
  count {SELECT w FROM t1 WHERE x=3 AND 225<=y}
} {14 15 5}
do_test format3-5.22 {
  count {SELECT w FROM t1 WHERE x=3 AND y>121 AND y<196}
} {11 12 5}
do_test format3-5.23 {
  count {SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<=196}
} {10 11 12 13 9}
do_test format3-5.24 {
  count {SELECT w FROM t1 WHERE x=3 AND 121<y AND 196>y}
} {11 12 5}
do_test format3-5.25 {
  count {SELECT w FROM t1 WHERE x=3 AND 121<=y AND 196>=y}
} {10 11 12 13 9}

# Need to work on optimizing the BETWEEN operator.  
#
# do_test format3-5.26 {
#   count {SELECT w FROM t1 WHERE x=3 AND y BETWEEN 121 AND 196}
# } {10 11 12 13 9}

do_test format3-5.27 {
  count {SELECT w FROM t1 WHERE x=3 AND y+1==122}
} {10 17}
do_test format3-5.28 {
  count {SELECT w FROM t1 WHERE x+1=4 AND y+1==122}
} {10 99}
do_test format3-5.29 {
  count {SELECT w FROM t1 WHERE y==121}
} {10 99}


do_test format3-5.30 {
  count {SELECT w FROM t1 WHERE w>97}
} {98 99 100 6}
do_test format3-5.31 {
  count {SELECT w FROM t1 WHERE w>=97}
} {97 98 99 100 8}
do_test format3-5.33 {
  count {SELECT w FROM t1 WHERE w==97}
} {97 3}
do_test format3-5.34 {
  count {SELECT w FROM t1 WHERE w+1==98}
} {97 99}
do_test format3-5.35 {
  count {SELECT w FROM t1 WHERE w<3}
} {1 2 4}
do_test format3-5.36 {
  count {SELECT w FROM t1 WHERE w<=3}
} {1 2 3 6}
do_test format3-5.37 {
  count {SELECT w FROM t1 WHERE w+1<=4 ORDER BY w}
} {1 2 3 199}


# Do the same kind of thing except use a join as the data source.
#
do_test format3-6.1 {
  db close
  sqlite db test.db
  count {
    SELECT w, p FROM t2, t1
    WHERE x=q AND y=s AND r=8977
  }
} {34 67 6}
do_test format3-6.2 {
  count {
    SELECT w, p FROM t2, t1
    WHERE x=q AND s=y AND r=8977
  }
} {34 67 6}
do_test format3-6.3 {
  count {
    SELECT w, p FROM t2, t1
    WHERE x=q AND s=y AND r=8977 AND w>10
  }
} {34 67 6}
do_test format3-6.4 {
  count {
    SELECT w, p FROM t2, t1
    WHERE p<80 AND x=q AND s=y AND r=8977 AND w>10
  }
} {34 67 6}
do_test format3-6.5 {
  count {
    SELECT w, p FROM t2, t1
    WHERE p<80 AND x=q AND 8977=r AND s=y AND w>10
  }
} {34 67 6}
do_test format3-6.6 {
  count {
    SELECT w, p FROM t2, t1
    WHERE x=q AND p=77 AND s=y AND w>5
  }
} {24 77 6}
do_test format3-6.7 {
  count {
    SELECT w, p FROM t1, t2
    WHERE x=q AND p>77 AND s=y AND w=5
  }
} {5 96 6}

# Lets do a 3-way join.
#
do_test format3-7.1 {
  count {
    SELECT A.w, B.p, C.w FROM t1 as A, t2 as B, t1 as C
    WHERE C.w=101-B.p AND B.r=10202-A.y AND A.w=11
  }
} {11 90 11 9}
do_test format3-7.2 {
  count {
    SELECT A.w, B.p, C.w FROM t1 as A, t2 as B, t1 as C
    WHERE C.w=101-B.p AND B.r=10202-A.y AND A.w=12
  }
} {12 89 12 9}
do_test format3-7.3 {
  count {
    SELECT A.w, B.p, C.w FROM t1 as A, t2 as B, t1 as C
    WHERE A.w=15 AND B.p=C.w AND B.r=10202-A.y
  }
} {15 86 86 9}

# Test to see that the special case of a constant WHERE clause is
# handled.
#
do_test format3-8.1 {
  count {
    SELECT * FROM t1 WHERE 0
  }
} {0}
do_test format3-8.2 {
  count {
    SELECT * FROM t1 WHERE 1 LIMIT 1
  }
} {1 0 4 1}
do_test format3-8.3 {
  execsql {
    SELECT 99 WHERE 0
  }
} {}
do_test format3-8.4 {
  execsql {
    SELECT 99 WHERE 1
  }
} {99}

# Verify that IN operators in a WHERE clause are handled correctly.
#
do_test format3-9.1 {
  count {
    SELECT * FROM t1 WHERE rowid IN (1,2,3,1234) order by 1;
  }
} {1 0 4 2 1 9 3 1 16 0}
do_test format3-9.2 {
  count {
    SELECT * FROM t1 WHERE rowid+0 IN (1,2,3,1234) order by 1;
  }
} {1 0 4 2 1 9 3 1 16 199}
do_test format3-9.3 {
  count {
    SELECT * FROM t1 WHERE w IN (-1,1,2,3) order by 1;
  }
} {1 0 4 2 1 9 3 1 16 10}
do_test format3-9.4 {
  count {
    SELECT * FROM t1 WHERE w+0 IN (-1,1,2,3) order by 1;
  }
} {1 0 4 2 1 9 3 1 16 199}
do_test format3-9.5 {
  count {
    SELECT * FROM t1 WHERE rowid IN 
       (select rowid from t1 where rowid IN (-1,2,4))
    ORDER BY 1;
  }
} {2 1 9 4 2 25 1}
do_test format3-9.6 {
  count {
    SELECT * FROM t1 WHERE rowid+0 IN 
       (select rowid from t1 where rowid IN (-1,2,4))
    ORDER BY 1;
  }
} {2 1 9 4 2 25 199}
do_test format3-9.7 {
  count {
    SELECT * FROM t1 WHERE w IN 
       (select rowid from t1 where rowid IN (-1,2,4))
    ORDER BY 1;
  }
} {2 1 9 4 2 25 7}
do_test format3-9.8 {
  count {
    SELECT * FROM t1 WHERE w+0 IN 
       (select rowid from t1 where rowid IN (-1,2,4))
    ORDER BY 1;
  }
} {2 1 9 4 2 25 199}
do_test format3-9.9 {
  count {
    SELECT * FROM t1 WHERE x IN (1,7) ORDER BY 1;
  }
} {2 1 9 3 1 16 6}
do_test format3-9.10 {
  count {
    SELECT * FROM t1 WHERE x+0 IN (1,7) ORDER BY 1;
  }
} {2 1 9 3 1 16 199}
do_test format3-9.11 {
  count {
    SELECT * FROM t1 WHERE y IN (6400,8100) ORDER BY 1;
  }
} {79 6 6400 89 6 8100 199}
do_test format3-9.12 {
  count {
    SELECT * FROM t1 WHERE x=6 AND y IN (6400,8100) ORDER BY 1;
  }
} {79 6 6400 89 6 8100 74}
do_test format3-9.13 {
  count {
    SELECT * FROM t1 WHERE x IN (1,7) AND y NOT IN (6400,8100) ORDER BY 1;
  }
} {2 1 9 3 1 16 6}
do_test format3-9.14 {
  count {
    SELECT * FROM t1 WHERE x IN (1,7) AND y IN (9,10) ORDER BY 1;
  }
} {2 1 9 6}

# This procedure executes the SQL.  Then it checks the generated program
# for the SQL and appends a "nosort" to the result if the program contains the
# SortCallback opcode.  If the program does not contain the SortCallback
# opcode it appends "sort"
#
proc cksort {sql} {
  set data [execsql $sql]
  set prog [execsql "EXPLAIN $sql"]
  if {[regexp SortCallback $prog]} {set x sort} {set x nosort}
  lappend data $x
  return $data
}
# Check out the logic that attempts to implement the ORDER BY clause
# using an index rather than by sorting.
#
do_test format3-10.1 {
  execsql {
    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 format3-10.2 {
  cksort {
    SELECT * FROM t3 ORDER BY a LIMIT 3
  }
} {1 100 4 2 99 9 3 98 16 nosort}
do_test format3-10.3 {
  cksort {
    SELECT * FROM t3 ORDER BY a+1 LIMIT 3
  }
} {1 100 4 2 99 9 3 98 16 sort}
do_test format3-10.4 {
  cksort {
    SELECT * FROM t3 WHERE a<10 ORDER BY a LIMIT 3
  }
} {1 100 4 2 99 9 3 98 16 nosort}
do_test format3-10.5 {
  cksort {
    SELECT * FROM t3 WHERE a>0 AND a<10 ORDER BY a LIMIT 3
  }
} {1 100 4 2 99 9 3 98 16 nosort}
do_test format3-10.6 {
  cksort {
    SELECT * FROM t3 WHERE a>0 ORDER BY a LIMIT 3
  }
} {1 100 4 2 99 9 3 98 16 nosort}
do_test format3-10.7 {
  cksort {
    SELECT * FROM t3 WHERE b>0 ORDER BY a LIMIT 3
  }
} {1 100 4 2 99 9 3 98 16 sort}
do_test format3-10.8 {
  cksort {
    SELECT * FROM t3 WHERE a IN (3,5,7,1,9,4,2) ORDER BY a LIMIT 3
  }
} {1 100 4 2 99 9 3 98 16 sort}
do_test format3-10.9 {
  cksort {
    SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a LIMIT 3
  }
} {1 100 4 nosort}
do_test format3-10.10 {
  cksort {
    SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a LIMIT 3
  }
} {1 100 4 nosort}
do_test format3-10.11 {
  cksort {
    SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a,c LIMIT 3
  }
} {1 100 4 nosort}
do_test format3-10.12 {
  cksort {
    SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a,c,b LIMIT 3
  }
} {1 100 4 nosort}
do_test format3-10.13 {
  cksort {
    SELECT * FROM t3 WHERE a>0 ORDER BY a DESC LIMIT 3
  }
} {100 1 10201 99 2 10000 98 3 9801 nosort}
do_test format3-10.13.1 {
  cksort {
    SELECT * FROM t3 WHERE a>0 ORDER BY a+1 DESC LIMIT 3
  }
} {100 1 10201 99 2 10000 98 3 9801 sort}
do_test format3-10.14 {
  cksort {
    SELECT * FROM t3 ORDER BY b LIMIT 3
  }
} {100 1 10201 99 2 10000 98 3 9801 nosort}
do_test format3-10.15 {
  cksort {
    SELECT t3.a, t1.x FROM t3, t1 WHERE t3.a=t1.w ORDER BY t3.a LIMIT 3
  }
} {1 0 2 1 3 1 nosort}
do_test format3-10.16 {
  cksort {
    SELECT t3.a, t1.x FROM t3, t1 WHERE t3.a=t1.w ORDER BY t1.x, t3.a LIMIT 3
  }
} {1 0 2 1 3 1 sort}
do_test format3-10.17 {
  cksort {
    SELECT y FROM t1 ORDER BY w COLLATE text LIMIT 3;
  }
} {4 121 10201 sort}
do_test format3-10.18 {
  cksort {
    SELECT y FROM t1 ORDER BY w COLLATE numeric LIMIT 3;
  }
} {4 9 16 sort}
do_test format3-10.19 {
  cksort {
    SELECT y FROM t1 ORDER BY w LIMIT 3;
  }
} {4 9 16 nosort}

# Check that all comparisons are numeric.  Similar tests in misc1.test
# check the same comparisons on a format4+ database and find that some
# are numeric and some are text.
#
do_test format3-11.1 {
  execsql {SELECT '0'=='0.0'}
} {1}
do_test format3-11.2 {
  execsql {SELECT '0'==0.0}
} {1}
do_test format3-11.3 {
  execsql {SELECT '123456789012345678901'=='123456789012345678900'}
} {1}
do_test format3-11.4 {
  execsql {
    CREATE TABLE t6(a INT UNIQUE, b TEXT UNIQUE);
    INSERT INTO t6 VALUES('0','0.0');
    SELECT * FROM t6;
  }
} {0 0.0}
do_test format3-11.5 {
  execsql {
    INSERT OR IGNORE INTO t6 VALUES(0.0,'x');
    SELECT * FROM t6;
  }
} {0 0.0}
do_test format3-11.6 {
  execsql {
    INSERT OR IGNORE INTO t6 VALUES('y',0);
    SELECT * FROM t6;
  }
} {0 0.0}
do_test format3-11.7 {
  execsql {
    CREATE TABLE t7(x INTEGER, y TEXT, z);
    INSERT INTO t7 VALUES(0,0,1);
    INSERT INTO t7 VALUES(0.0,0,2);
    INSERT INTO t7 VALUES(0,0.0,3);
    INSERT INTO t7 VALUES(0.0,0.0,4);
    SELECT DISTINCT x, y FROM t7 ORDER BY z;
  }
} {0 0}

# Make sure attempts to attach a format 3 database fail.
#
do_test format3-12.1 {
  file delete -force test2.db
  sqlite db2 test2.db
  catchsql {
    CREATE TABLE t8(x,y);
    ATTACH DATABASE 'test.db' AS format3;
  } db2;
} {1 {incompatible file format in auxiliary database: format3}}
do_test format3-12.2 {
  catchsql {
    ATTACH DATABASE 'test2.db' AS test2;
  }
} {1 {cannot attach auxiliary databases to an older format master database}}
db2 close

finish_test

<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
Changes to test/in.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

# 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 IN and BETWEEN operator.
#
# $Id: in.test,v 1.11 2004/01/15 03:30:25 drh Exp $

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

# Generate the test data we will need for the first squences of tests.
#
do_test in-1.0 {
  set fd [open data1.txt w]



  for {set i 1} {$i<=10} {incr i} {
    puts $fd "$i\t[expr {int(pow(2,$i))}]"
  }
  close $fd
  execsql {
    CREATE TABLE t1(a int, b int);

    COPY t1 FROM 'data1.txt';
  }
  file delete -force data1.txt
  execsql {SELECT count(*) FROM t1}
} {10}

# Do basic testing of BETWEEN.
#
do_test in-1.1 {
  execsql {SELECT a FROM t1 WHERE b BETWEEN 10 AND 50 ORDER BY a}
} {4 5}














|







|
>
>
>

|

<

<
>
|

<
<








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

# 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 IN and BETWEEN operator.
#
# $Id: in.test,v 1.12 2004/05/27 17:22:56 drh Exp $

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

# Generate the test data we will need for the first squences of tests.
#
do_test in-1.0 {
  execsql {
    BEGIN;
    CREATE TABLE t1(a int, b int);
  }
  for {set i 1} {$i<=10} {incr i} {
    execsql "INSERT INTO t1 VALUES($i,[expr {int(pow(2,$i))}])"
  }

  execsql {

    COMMIT;
    SELECT count(*) FROM t1;
  }


} {10}

# Do basic testing of BETWEEN.
#
do_test in-1.1 {
  execsql {SELECT a FROM t1 WHERE b BETWEEN 10 AND 50 ORDER BY a}
} {4 5}
︙︙
Changes to test/intpkey.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 the special processing associated
# with INTEGER PRIMARY KEY columns.
#
# $Id: intpkey.test,v 1.14 2003/06/15 23:42:25 drh Exp $

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

# Create a table with a primary key and a datatype other than
# integer
#








|








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 the special processing associated
# with INTEGER PRIMARY KEY columns.
#
# $Id: intpkey.test,v 1.15 2004/05/27 17:22:56 drh Exp $

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

# Create a table with a primary key and a datatype other than
# integer
#
︙︙
376
377
378
379
380
381
382



383
384
385
386
387
388
389

390



391
392
393
394
395
396
397

    SELECT rowid, a FROM t1
  }
} {-4 -4 0 0 5 5 6 6 11 11}

# Test the ability of the COPY command to put data into a
# table that contains an integer primary key.
#



do_test intpkey-6.1 {
  set f [open ./data1.txt w]
  puts $f "20\tb-20\tc-20"
  puts $f "21\tb-21\tc-21"
  puts $f "22\tb-22\tc-22"
  close $f
  execsql {

    COPY t1 FROM 'data1.txt';



    SELECT * FROM t1 WHERE a>=20;
  }
} {20 b-20 c-20 21 b-21 c-21 22 b-22 c-22}
do_test intpkey-6.2 {
  execsql {
    SELECT * FROM t1 WHERE b=='hello'
  }








>
>
>

<
<
<
<
<

>
|
>
>
>








376
377
378
379
380
381
382
383
384
385
386





387
388
389
390
391
392
393
394
395
396
397
398
399

    SELECT rowid, a FROM t1
  }
} {-4 -4 0 0 5 5 6 6 11 11}

# Test the ability of the COPY command to put data into a
# table that contains an integer primary key.
#
# COPY command has been removed.  But we retain these tests so
# that the tables will contain the right data for tests that follow.
#
do_test intpkey-6.1 {





  execsql {
    BEGIN;
    INSERT INTO t1 VALUES(20,'b-20','c-20');
    INSERT INTO t1 VALUES(21,'b-21','c-21');
    INSERT INTO t1 VALUES(22,'b-22','c-22');
    COMMIT;
    SELECT * FROM t1 WHERE a>=20;
  }
} {20 b-20 c-20 21 b-21 c-21 22 b-22 c-22}
do_test intpkey-6.2 {
  execsql {
    SELECT * FROM t1 WHERE b=='hello'
  }
︙︙
Changes to test/limit.test.
︙︙
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

#    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.12 2004/05/14 21:12:24 drh Exp $

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

# Build some test data
#
set fd [open data1.txt w]



for {set i 1} {$i<=32} {incr i} {
  for {set j 0} {pow(2,$j)<$i} {incr j} {}
  puts $fd "[expr {32-$i}]\t[expr {10-$j}]"
}
close $fd
execsql {
  CREATE TABLE t1(x int, y int);
  COPY t1 FROM 'data1.txt'
}
file delete data1.txt

do_test limit-1.0 {
  execsql {SELECT count(*) FROM t1}
} {32}
do_test limit-1.1 {
  execsql {SELECT count(*) FROM t1 LIMIT  5}
} {32}








|






|
>
>
>


|

<

<
|

<








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

#    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.13 2004/05/27 17:22:56 drh Exp $

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

# Build some test data
#
execsql {
  CREATE TABLE t1(x int, y int);
  BEGIN;
}
for {set i 1} {$i<=32} {incr i} {
  for {set j 0} {pow(2,$j)<$i} {incr j} {}
  execsql "INSERT INTO t1 VALUES([expr {32-$i}],[expr {10-$j}])"
}

execsql {

  COMMIT;
}


do_test limit-1.0 {
  execsql {SELECT count(*) FROM t1}
} {32}
do_test limit-1.1 {
  execsql {SELECT count(*) FROM t1 LIMIT  5}
} {32}
︙︙
Changes to test/malloc.test.
︙︙
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

#***********************************************************************
# This file attempts to check the library in an out-of-memory situation.
# When compiled with -DMEMORY_DEBUG=1, the SQLite library accepts a special
# command (sqlite_malloc_fail N) which causes the N-th malloc to fail.  This
# special feature is used to see what happens in the library if a malloc
# were to really fail due to an out-of-memory situation.
#
# $Id: malloc.test,v 1.6 2004/02/14 01:39:50 drh Exp $

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

# Only run these tests if memory debugging is turned on.
#
if {[info command sqlite_malloc_stat]==""} {








|








10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

#***********************************************************************
# This file attempts to check the library in an out-of-memory situation.
# When compiled with -DMEMORY_DEBUG=1, the SQLite library accepts a special
# command (sqlite_malloc_fail N) which causes the N-th malloc to fail.  This
# special feature is used to see what happens in the library if a malloc
# were to really fail due to an out-of-memory situation.
#
# $Id: malloc.test,v 1.7 2004/05/27 17:22:56 drh Exp $

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

# Only run these tests if memory debugging is turned on.
#
if {[info command sqlite_malloc_stat]==""} {
︙︙
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88





89
90
91
92
93
94
95

       lappend v $v2
     }
  } {1 1}
}

set fd [open ./data.tmp w]
for {set i 1} {$i<=20} {incr i} {
  puts $fd "$i\t[expr {$i*$i}]\t[expr {100-$i}] abcdefghijklmnopqrstuvwxyz"
}
close $fd

for {set go 1; set i 1} {$go} {incr i} {
  do_test malloc-2.$i {
     sqlite_malloc_fail 0
     catch {db close}
     catch {file delete -force test.db}
     catch {file delete -force test.db-journal}
     sqlite_malloc_fail $i
     set v [catch {sqlite db test.db} msg]
     if {$v} {
       set msg ""
     } else {
       set v [catch {execsql {
         CREATE TABLE t1(a int, b int, c int);
         CREATE INDEX i1 ON t1(a,b);
         COPY t1 FROM 'data.tmp';





         SELECT 'stuff', count(*) as 'other stuff', max(a+10) FROM t1;
         UPDATE t1 SET b=b||b||b||b;
         UPDATE t1 SET b=a WHERE a in (10,12,22);
         INSERT INTO t1(c,b,a) VALUES(20,10,5);
         INSERT INTO t1 SELECT * FROM t1
             WHERE a IN (SELECT a FROM t1 WHERE a<10);
         DELETE FROM t1 WHERE a>=10;








|

















|
>
>
>
>
>








63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100

       lappend v $v2
     }
  } {1 1}
}

set fd [open ./data.tmp w]
for {set i 1} {$i<=20} {incr i} {
  puts $fd "$i\t[expr {$i*$i}]\t[expr {100-$i}]  abcdefghijklmnopqrstuvwxyz"
}
close $fd

for {set go 1; set i 1} {$go} {incr i} {
  do_test malloc-2.$i {
     sqlite_malloc_fail 0
     catch {db close}
     catch {file delete -force test.db}
     catch {file delete -force test.db-journal}
     sqlite_malloc_fail $i
     set v [catch {sqlite db test.db} msg]
     if {$v} {
       set msg ""
     } else {
       set v [catch {execsql {
         CREATE TABLE t1(a int, b int, c int);
         CREATE INDEX i1 ON t1(a,b);
         INSERT INTO t1 VALUES(1,1,'99 abcdefghijklmnopqrstuvwxyz');
         INSERT INTO t1 VALUES(2,4,'98 abcdefghijklmnopqrstuvwxyz');
         INSERT INTO t1 VALUES(3,9,'97 abcdefghijklmnopqrstuvwxyz');
         INSERT INTO t1 VALUES(4,16,'96 abcdefghijklmnopqrstuvwxyz');
         INSERT INTO t1 VALUES(5,25,'95 abcdefghijklmnopqrstuvwxyz');
         INSERT INTO t1 VALUES(6,36,'94 abcdefghijklmnopqrstuvwxyz');
         SELECT 'stuff', count(*) as 'other stuff', max(a+10) FROM t1;
         UPDATE t1 SET b=b||b||b||b;
         UPDATE t1 SET b=a WHERE a in (10,12,22);
         INSERT INTO t1(c,b,a) VALUES(20,10,5);
         INSERT INTO t1 SELECT * FROM t1
             WHERE a IN (SELECT a FROM t1 WHERE a<10);
         DELETE FROM t1 WHERE a>=10;
︙︙
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134





135
136
137
138
139
140
141

       set v2 [expr {$msg=="" || $msg=="out of memory"}]
       if {!$v2} {puts "\nError message returned: $msg"}
       lappend v $v2
     }
  } {1 1}
}

set fd [open ./data.tmp w]
for {set i 1} {$i<=10} {incr i} {
  puts $fd "$i\t[expr {$i*$i}]\t[expr {100-$i}]"
}
close $fd

for {set go 1; set i 1} {$go} {incr i} {
  do_test malloc-3.$i {
     sqlite_malloc_fail 0
     catch {db close}
     catch {file delete -force test.db}
     catch {file delete -force test.db-journal}
     sqlite_malloc_fail $i
     set v [catch {sqlite db test.db} msg]
     if {$v} {
       set msg ""
     } else {
       set v [catch {execsql {
         BEGIN TRANSACTION;
         CREATE TABLE t1(a int, b int, c int);
         CREATE INDEX i1 ON t1(a,b);
         COPY t1 FROM 'data.tmp';





         INSERT INTO t1(c,b,a) VALUES(20,10,5);
         DELETE FROM t1 WHERE a>=10;
         DROP INDEX i1;
         DELETE FROM t1;
         ROLLBACK;
       }} msg]
     }








<
<
<
<
<
<















|
>
>
>
>
>








111
112
113
114
115
116
117






118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145

       set v2 [expr {$msg=="" || $msg=="out of memory"}]
       if {!$v2} {puts "\nError message returned: $msg"}
       lappend v $v2
     }
  } {1 1}
}







for {set go 1; set i 1} {$go} {incr i} {
  do_test malloc-3.$i {
     sqlite_malloc_fail 0
     catch {db close}
     catch {file delete -force test.db}
     catch {file delete -force test.db-journal}
     sqlite_malloc_fail $i
     set v [catch {sqlite db test.db} msg]
     if {$v} {
       set msg ""
     } else {
       set v [catch {execsql {
         BEGIN TRANSACTION;
         CREATE TABLE t1(a int, b int, c int);
         CREATE INDEX i1 ON t1(a,b);
         INSERT INTO t1 VALUES(1,1,99);
         INSERT INTO t1 VALUES(2,4,98);
         INSERT INTO t1 VALUES(3,9,97);
         INSERT INTO t1 VALUES(4,16,96);
         INSERT INTO t1 VALUES(5,25,95);
         INSERT INTO t1 VALUES(6,36,94);
         INSERT INTO t1(c,b,a) VALUES(20,10,5);
         DELETE FROM t1 WHERE a>=10;
         DROP INDEX i1;
         DELETE FROM t1;
         ROLLBACK;
       }} msg]
     }
︙︙
162
163
164
165
166
167
168
169





170
171
172
173
174
175
176

     if {$v} {
       set msg ""
     } else {
       set v [catch {execsql {
         BEGIN TRANSACTION;
         CREATE TABLE t1(a int, b int, c int);
         CREATE INDEX i1 ON t1(a,b);
         COPY t1 FROM 'data.tmp';





         UPDATE t1 SET b=a WHERE a in (10,12,22);
         INSERT INTO t1 SELECT * FROM t1
             WHERE a IN (SELECT a FROM t1 WHERE a<10);
         DROP INDEX i1;
         DELETE FROM t1;
         COMMIT;
       }} msg]








|
>
>
>
>
>








166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185

     if {$v} {
       set msg ""
     } else {
       set v [catch {execsql {
         BEGIN TRANSACTION;
         CREATE TABLE t1(a int, b int, c int);
         CREATE INDEX i1 ON t1(a,b);
         INSERT INTO t1 VALUES(1,1,99);
         INSERT INTO t1 VALUES(2,4,98);
         INSERT INTO t1 VALUES(3,9,97);
         INSERT INTO t1 VALUES(4,16,96);
         INSERT INTO t1 VALUES(5,25,95);
         INSERT INTO t1 VALUES(6,36,94);
         UPDATE t1 SET b=a WHERE a in (10,12,22);
         INSERT INTO t1 SELECT * FROM t1
             WHERE a IN (SELECT a FROM t1 WHERE a<10);
         DROP INDEX i1;
         DELETE FROM t1;
         COMMIT;
       }} msg]
︙︙
Changes to test/select2.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

# 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: select2.test,v 1.20 2004/05/14 11:00:53 danielk1977 Exp $

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

# Create a table with some data
#
execsql {CREATE TABLE tbl1(f1 int, f2 int)}
set f [open ./testdata1.txt w]
for {set i 0} {$i<=30} {incr i} {
  puts $f "[expr {$i%9}]\t[expr {$i%10}]"
}
close $f
execsql {COPY tbl1 FROM './testdata1.txt'}
file delete -force ./testdata1.txt
catch {unset data}

# Do a second query inside a first.
#
do_test select2-1.1 {
  set sql {SELECT DISTINCT f1 FROM tbl1 ORDER BY f1}
  set r {}
  db eval $sql data {














|







|

|

<
|
<
<








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

# 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: select2.test,v 1.21 2004/05/27 17:22:56 drh Exp $

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

# Create a table with some data
#
execsql {CREATE TABLE tbl1(f1 int, f2 int)}
execsql {BEGIN}
for {set i 0} {$i<=30} {incr i} {
  execsql "INSERT INTO tbl1 VALUES([expr {$i%9}],[expr {$i%10}])"
}

execsql {COMMIT}



# Do a second query inside a first.
#
do_test select2-1.1 {
  set sql {SELECT DISTINCT f1 FROM tbl1 ORDER BY f1}
  set r {}
  db eval $sql data {
︙︙
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79

  }
  set r
} {4: 2 3 4}

# Create a largish table
#
do_test select2-2.0 {
  execsql {CREATE TABLE tbl2(f1 int, f2 int, f3 int)}
  set f [open ./testdata1.txt w]
  for {set i 1} {$i<=30000} {incr i} {
    puts $f "$i\t[expr {$i*2}]\t[expr {$i*3}]"
  }
  close $f
  # execsql {--vdbe-trace-on--}
  execsql {COPY tbl2 FROM './testdata1.txt'}
  file delete -force ./testdata1.txt
} {}

do_test select2-2.1 {
  execsql {SELECT count(*) FROM tbl2}
} {30000}
do_test select2-2.2 {
  execsql {SELECT count(*) FROM tbl2 WHERE f2>1000}








|
<

|

<
<
|
<








54
55
56
57
58
59
60
61

62
63
64


65

66
67
68
69
70
71
72

  }
  set r
} {4: 2 3 4}

# Create a largish table
#
do_test select2-2.0 {
  execsql {CREATE TABLE tbl2(f1 int, f2 int, f3 int); BEGIN;}

  for {set i 1} {$i<=30000} {incr i} {
    execsql "INSERT INTO tbl2 VALUES($i,[expr {$i*2}],[expr {$i*3}])"
  }


  execsql {COMMIT}

} {}

do_test select2-2.1 {
  execsql {SELECT count(*) FROM tbl2}
} {30000}
do_test select2-2.2 {
  execsql {SELECT count(*) FROM tbl2 WHERE f2>1000}
︙︙
Changes to test/select3.test.
︙︙
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

#    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.9 2004/05/21 02:14:25 drh Exp $

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

# Build some test data
#
do_test select3-1.0 {
  set fd [open data1.txt w]



  for {set i 1} {$i<32} {incr i} {
    for {set j 0} {pow(2,$j)<$i} {incr j} {}
    puts $fd "$i\t$j"

  }
  close $fd
  execsql {
    CREATE TABLE t1(n int, log int);
    COPY t1 FROM 'data1.txt'
  }
  file delete data1.txt
  execsql {SELECT DISTINCT log FROM t1 ORDER BY log}
} {0 1 2 3 4 5}

# Basic aggregate functions.
#
do_test select3-1.1 {
  execsql {SELECT count(*) FROM t1}








|







|
>
>
>


<
>

<

<
|

<








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

#    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.10 2004/05/27 17:22:56 drh Exp $

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

# Build some test data
#
do_test select3-1.0 {
  execsql {
    CREATE TABLE t1(n int, log int);
    BEGIN;
  }
  for {set i 1} {$i<32} {incr i} {
    for {set j 0} {pow(2,$j)<$i} {incr j} {}

    execsql "INSERT INTO t1 VALUES($i,$j)"
  }

  execsql {

    COMMIT
  }

  execsql {SELECT DISTINCT log FROM t1 ORDER BY log}
} {0 1 2 3 4 5}

# Basic aggregate functions.
#
do_test select3-1.1 {
  execsql {SELECT count(*) FROM t1}
︙︙
Changes to test/select4.test.
︙︙
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

#    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 UNION, INTERSECT and EXCEPT operators
# in SELECT statements.
#
# $Id: select4.test,v 1.15 2004/05/14 11:00:53 danielk1977 Exp $

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

# Build some test data
#
set fd [open data1.txt w]



for {set i 1} {$i<32} {incr i} {
  for {set j 0} {pow(2,$j)<$i} {incr j} {}
  puts $fd "$i\t$j"

}
close $fd
execsql {
  CREATE TABLE t1(n int, log int);
  COPY t1 FROM 'data1.txt'
}
file delete data1.txt

do_test select4-1.0 {
  execsql {SELECT DISTINCT log FROM t1 ORDER BY log}
} {0 1 2 3 4 5}

# Union All operator
#








|






|
>
>
>


<
>

<

<
|

<








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

#    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 UNION, INTERSECT and EXCEPT operators
# in SELECT statements.
#
# $Id: select4.test,v 1.16 2004/05/27 17:22:56 drh Exp $

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

# Build some test data
#
execsql {
  CREATE TABLE t1(n int, log int);
  BEGIN;
}
for {set i 1} {$i<32} {incr i} {
  for {set j 0} {pow(2,$j)<$i} {incr j} {}

  execsql "INSERT INTO t1 VALUES($i,$j)"
}

execsql {

  COMMIT;
}


do_test select4-1.0 {
  execsql {SELECT DISTINCT log FROM t1 ORDER BY log}
} {0 1 2 3 4 5}

# Union All operator
#
︙︙
Changes to test/select5.test.
︙︙
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

#    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.6 2001/10/15 00:44:36 drh Exp $

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

# Build some test data
#
set fd [open data1.txt w]



for {set i 1} {$i<32} {incr i} {
  for {set j 0} {pow(2,$j)<$i} {incr j} {}
  puts $fd "[expr {32-$i}]\t[expr {10-$j}]"
}
close $fd
execsql {
  CREATE TABLE t1(x int, y int);
  COPY t1 FROM 'data1.txt'
}
file delete data1.txt

do_test select5-1.0 {
  execsql {SELECT DISTINCT y FROM t1 ORDER BY y}
} {5 6 7 8 9 10}

# Sort by an aggregate function.
#








|






|
>
>
>


|

<

<
|

<








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

#    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 {
  CREATE TABLE t1(x int, y int);
  BEGIN;
}
for {set i 1} {$i<32} {incr i} {
  for {set j 0} {pow(2,$j)<$i} {incr j} {}
  execsql "INSERT INTO t1 VALUES([expr {32-$i}],[expr {10-$j}])"
}

execsql {

  COMMIT
}


do_test select5-1.0 {
  execsql {SELECT DISTINCT y FROM t1 ORDER BY y}
} {5 6 7 8 9 10}

# Sort by an aggregate function.
#
︙︙
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
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

# 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.11 2004/05/21 02:14:25 drh Exp $

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

# Create a bunch of data to sort against
#
do_test sort-1.0 {
  set fd [open data.txt w]
  puts $fd "1\tone\t0\tI\t3.141592653"
  puts $fd "2\ttwo\t1\tII\t2.15"
  puts $fd "3\tthree\t1\tIII\t4221.0"
  puts $fd "4\tfour\t2\tIV\t-0.0013442"
  puts $fd "5\tfive\t2\tV\t-11"
  puts $fd "6\tsix\t2\tVI\t0.123"
  puts $fd "7\tseven\t2\tVII\t123.0"
  puts $fd "8\teight\t3\tVIII\t-1.6"
  close $fd
  execsql {
    CREATE TABLE t1(
       n int,
       v varchar(10),
       log int,
       roman varchar(10),
       flt real
    );

    COPY t1 FROM 'data.txt'






  }
  file delete data.txt
  execsql {SELECT count(*) FROM t1}
} {8}

do_test sort-1.1 {
  execsql {SELECT n FROM t1 ORDER BY n}
} {1 2 3 4 5 6 7 8}
do_test sort-1.1.1 {














|







<
<
<
<
<
<
<
<
<
<








>
|
>
>
>
>
>
>

<








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

# 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.12 2004/05/27 17:22:56 drh Exp $

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

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










  execsql {
    CREATE TABLE t1(
       n int,
       v varchar(10),
       log int,
       roman varchar(10),
       flt real
    );
    INSERT INTO t1 VALUES(1,'one',0,'I',3.141592653);
    INSERT INTO t1 VALUES(2,'two',1,'II',2.15);
    INSERT INTO t1 VALUES(3,'three',1,'III',4221.0);
    INSERT INTO t1 VALUES(4,'four',2,'IV',-0.0013442);
    INSERT INTO t1 VALUES(5,'five',2,'V',-11);
    INSERT INTO t1 VALUES(6,'six',2,'VI',0.123);
    INSERT INTO t1 VALUES(7,'seven',2,'VII',123.0);
    INSERT INTO t1 VALUES(8,'eight',3,'VIII',-1.6);
  }

  execsql {SELECT count(*) FROM t1}
} {8}

do_test sort-1.1 {
  execsql {SELECT n FROM t1 ORDER BY n}
} {1 2 3 4 5 6 7 8}
do_test sort-1.1.1 {
︙︙