SQLite

Check-in [27c0678623]
Login

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

Overview
Comment:added default values (CVS 46)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 27c0678623de37f3166cb9952989fd03484cdb8d
User & Date: drh 2000-06-03 18:06:52.000
Context
2000-06-03
19:19
:-) (CVS 47) (check-in: 729d6586b8 user: drh tags: trunk)
18:06
added default values (CVS 46) (check-in: 27c0678623 user: drh tags: trunk)
2000-06-02
23:22
:-) (CVS 45) (check-in: 3e88142e90 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/build.c.
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
**     DROP TABLE
**     CREATE INDEX
**     DROP INDEX
**     creating expressions and ID lists
**     COPY
**     VACUUM
**
** $Id: build.c,v 1.13 2000/06/02 13:27:59 drh Exp $
*/
#include "sqliteInt.h"

/*
** This routine is called after a single SQL statement has been
** parsed and we want to execute the code to implement 
** the statement.  Prior action routines should have already







|







29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
**     DROP TABLE
**     CREATE INDEX
**     DROP INDEX
**     creating expressions and ID lists
**     COPY
**     VACUUM
**
** $Id: build.c,v 1.14 2000/06/03 18:06:52 drh Exp $
*/
#include "sqliteInt.h"

/*
** This routine is called after a single SQL statement has been
** parsed and we want to execute the code to implement 
** the statement.  Prior action routines should have already
177
178
179
180
181
182
183
184

185
186
187
188
189
190
191
192
193
194
195
196
197
** memory structures of the indices associated with the table.
*/
void sqliteDeleteTable(sqlite *db, Table *pTable){
  int i;
  Index *pIndex, *pNext;
  if( pTable==0 ) return;
  for(i=0; i<pTable->nCol; i++){
    if( pTable->azCol[i] ) sqliteFree(pTable->azCol[i]);

  }
  for(pIndex = pTable->pIndex; pIndex; pIndex=pNext){
    pNext = pIndex->pNext;
    sqliteDeleteIndex(db, pIndex);
  }
  sqliteFree(pTable->azCol);
  sqliteFree(pTable);
}

/*
** Construct the name of a user table from a token.
**
** Space to hold the name is obtained from sqliteMalloc() and must







|
>





|







177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
** memory structures of the indices associated with the table.
*/
void sqliteDeleteTable(sqlite *db, Table *pTable){
  int i;
  Index *pIndex, *pNext;
  if( pTable==0 ) return;
  for(i=0; i<pTable->nCol; i++){
    sqliteFree(pTable->aCol[i].zName);
    sqliteFree(pTable->aCol[i].zDflt);
  }
  for(pIndex = pTable->pIndex; pIndex; pIndex=pNext){
    pNext = pIndex->pNext;
    sqliteDeleteIndex(db, pIndex);
  }
  sqliteFree(pTable->aCol);
  sqliteFree(pTable);
}

/*
** Construct the name of a user table from a token.
**
** Space to hold the name is obtained from sqliteMalloc() and must
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
    sqliteSetString(&pParse->zErrMsg, "out of memory", 0);
    pParse->nErr++;
    return;
  }
  pTable->zName = zName;
  pTable->pHash = 0;
  pTable->nCol = 0;
  pTable->azCol = 0;
  pTable->pIndex = 0;
  if( pParse->pNewTable ) sqliteDeleteTable(pParse->db, pParse->pNewTable);
  pParse->pNewTable = pTable;
}

/*
** Add a new column to the table currently being constructed.
*/
void sqliteAddColumn(Parse *pParse, Token *pName){
  Table *p;
  char **pz;
  if( (p = pParse->pNewTable)==0 ) return;
  if( (p->nCol & 0x7)==0 ){
    p->azCol = sqliteRealloc( p->azCol, (p->nCol+8)*sizeof(p->azCol[0]));
  }
  if( p->azCol==0 ){
    p->nCol = 0;
    return;
  }
  pz = &p->azCol[p->nCol++];
  *pz = 0;
  sqliteSetNString(pz, pName->z, pName->n, 0);
  sqliteDequote(*pz);
}





















/*
** This routine is called to report the final ")" that terminates
** a CREATE TABLE statement.
**
** The table structure is added to the internal hash tables.  
**







|













|

|



|
|



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







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
    sqliteSetString(&pParse->zErrMsg, "out of memory", 0);
    pParse->nErr++;
    return;
  }
  pTable->zName = zName;
  pTable->pHash = 0;
  pTable->nCol = 0;
  pTable->aCol = 0;
  pTable->pIndex = 0;
  if( pParse->pNewTable ) sqliteDeleteTable(pParse->db, pParse->pNewTable);
  pParse->pNewTable = pTable;
}

/*
** Add a new column to the table currently being constructed.
*/
void sqliteAddColumn(Parse *pParse, Token *pName){
  Table *p;
  char **pz;
  if( (p = pParse->pNewTable)==0 ) return;
  if( (p->nCol & 0x7)==0 ){
    p->aCol = sqliteRealloc( p->aCol, (p->nCol+8)*sizeof(p->aCol[0]));
  }
  if( p->aCol==0 ){
    p->nCol = 0;
    return;
  }
  memset(&p->aCol[p->nCol], 0, sizeof(p->aCol[0]));
  pz = &p->aCol[p->nCol++].zName;
  sqliteSetNString(pz, pName->z, pName->n, 0);
  sqliteDequote(*pz);
}

/*
** The given token is the default value for the last column added to
** the table currently under construction.  If "minusFlag" is true, it
** means the value token was preceded by a minus sign.
*/
void sqliteAddDefaultValue(Parse *pParse, Token *pVal, int minusFlag){
  Table *p;
  int i;
  char **pz;
  if( (p = pParse->pNewTable)==0 ) return;
  i = p->nCol-1;
  pz = &p->aCol[i].zDflt;
  if( minusFlag ){
    sqliteSetNString(pz, "-", 1, pVal->z, pVal->n, 0);
  }else{
    sqliteSetNString(pz, pVal->z, pVal->n, 0);
  }
  sqliteDequote(*pz);
}

/*
** This routine is called to report the final ")" that terminates
** a CREATE TABLE statement.
**
** The table structure is added to the internal hash tables.  
**
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
  }

  /* If pList==0, it means this routine was called to make a primary
  ** key out of the last field added to the table under construction.
  ** So create a fake list to simulate this.
  */
  if( pList==0 ){
    nullId.z = pTab->azCol[pTab->nCol-1];
    nullId.n = strlen(nullId.z);
    pList = sqliteIdListAppend(0, &nullId);
    if( pList==0 ) goto exit_create_index;
  }

  /* 
  ** Allocate the index structure. 







|







492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
  }

  /* If pList==0, it means this routine was called to make a primary
  ** key out of the last field added to the table under construction.
  ** So create a fake list to simulate this.
  */
  if( pList==0 ){
    nullId.z = pTab->aCol[pTab->nCol-1].zName;
    nullId.n = strlen(nullId.z);
    pList = sqliteIdListAppend(0, &nullId);
    if( pList==0 ) goto exit_create_index;
  }

  /* 
  ** Allocate the index structure. 
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513

  /* Scan the names of the fields of the table to be indexed and
  ** load the field indices into the Index structure.  Report an error
  ** if any field is not found.
  */
  for(i=0; i<pList->nId; i++){
    for(j=0; j<pTab->nCol; j++){
      if( sqliteStrICmp(pList->a[i].zName, pTab->azCol[j])==0 ) break;
    }
    if( j>=pTab->nCol ){
      sqliteSetString(&pParse->zErrMsg, "table ", pTab->zName, 
        " has no field named ", pList->a[i].zName, 0);
      pParse->nErr++;
      sqliteFree(pIndex);
      goto exit_create_index;







|







520
521
522
523
524
525
526
527
528
529
530
531
532
533
534

  /* Scan the names of the fields of the table to be indexed and
  ** load the field indices into the Index structure.  Report an error
  ** if any field is not found.
  */
  for(i=0; i<pList->nId; i++){
    for(j=0; j<pTab->nCol; j++){
      if( sqliteStrICmp(pList->a[i].zName, pTab->aCol[j].zName)==0 ) break;
    }
    if( j>=pTab->nCol ){
      sqliteSetString(&pParse->zErrMsg, "table ", pTab->zName, 
        " has no field named ", pList->a[i].zName, 0);
      pParse->nErr++;
      sqliteFree(pIndex);
      goto exit_create_index;
Changes to src/expr.c.
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
** Author contact information:
**   drh@hwaci.com
**   http://www.hwaci.com/drh/
**
*************************************************************************
** This file contains C code routines used for processing expressions
**
** $Id: expr.c,v 1.2 2000/06/02 13:27:59 drh Exp $
*/
#include "sqliteInt.h"

/*
** This routine walks an expression tree and resolves references to
** table fields.  Nodes of the form ID.ID or ID resolve into an
** index to the table in the table list and a field offset.  The opcode







|







19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
** Author contact information:
**   drh@hwaci.com
**   http://www.hwaci.com/drh/
**
*************************************************************************
** This file contains C code routines used for processing expressions
**
** $Id: expr.c,v 1.3 2000/06/03 18:06:53 drh Exp $
*/
#include "sqliteInt.h"

/*
** This routine walks an expression tree and resolves references to
** table fields.  Nodes of the form ID.ID or ID resolve into an
** index to the table in the table list and a field offset.  The opcode
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
      char *z = 0;
      sqliteSetNString(&z, pExpr->token.z, pExpr->token.n, 0);
      for(i=0; i<pTabList->nId; i++){
        int j;
        Table *pTab = pTabList->a[i].pTab;
        if( pTab==0 ) continue;
        for(j=0; j<pTab->nCol; j++){
          if( sqliteStrICmp(pTab->azCol[j], z)==0 ){
            cnt++;
            pExpr->iTable = i;
            pExpr->iField = j;
          }
        }
      }
      sqliteFree(z);







|







48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
      char *z = 0;
      sqliteSetNString(&z, pExpr->token.z, pExpr->token.n, 0);
      for(i=0; i<pTabList->nId; i++){
        int j;
        Table *pTab = pTabList->a[i].pTab;
        if( pTab==0 ) continue;
        for(j=0; j<pTab->nCol; j++){
          if( sqliteStrICmp(pTab->aCol[j].zName, z)==0 ){
            cnt++;
            pExpr->iTable = i;
            pExpr->iField = j;
          }
        }
      }
      sqliteFree(z);
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
        if( pTabList->a[i].zAlias ){
          zTab = pTabList->a[i].zAlias;
        }else{
          zTab = pTab->zName;
        }
        if( sqliteStrICmp(zTab, zLeft)!=0 ) continue;
        for(j=0; j<pTab->nCol; j++){
          if( sqliteStrICmp(pTab->azCol[j], zRight)==0 ){
            cnt++;
            pExpr->iTable = i;
            pExpr->iField = j;
          }
        }
      }
      sqliteFree(zLeft);







|







98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
        if( pTabList->a[i].zAlias ){
          zTab = pTabList->a[i].zAlias;
        }else{
          zTab = pTab->zName;
        }
        if( sqliteStrICmp(zTab, zLeft)!=0 ) continue;
        for(j=0; j<pTab->nCol; j++){
          if( sqliteStrICmp(pTab->aCol[j].zName, zRight)==0 ){
            cnt++;
            pExpr->iTable = i;
            pExpr->iField = j;
          }
        }
      }
      sqliteFree(zLeft);
Changes to src/insert.c.
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
**   drh@hwaci.com
**   http://www.hwaci.com/drh/
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle INSERT statements.
**
** $Id: insert.c,v 1.3 2000/06/02 13:27:59 drh Exp $
*/
#include "sqliteInt.h"

/*
** This routine is call to handle SQL of the following form:
**
**    insert into TABLE (IDLIST) values(EXPRLIST)







|







20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
**   drh@hwaci.com
**   http://www.hwaci.com/drh/
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle INSERT statements.
**
** $Id: insert.c,v 1.4 2000/06/03 18:06:53 drh Exp $
*/
#include "sqliteInt.h"

/*
** This routine is call to handle SQL of the following form:
**
**    insert into TABLE (IDLIST) values(EXPRLIST)
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
  }
  if( pField ){
    for(i=0; i<pField->nId; i++){
      pField->a[i].idx = -1;
    }
    for(i=0; i<pField->nId; i++){
      for(j=0; j<pTab->nCol; j++){
        if( sqliteStrICmp(pField->a[i].zName, pTab->azCol[j])==0 ){
          pField->a[i].idx = j;
          break;
        }
      }
      if( j>=pTab->nCol ){
        sqliteSetString(&pParse->zErrMsg, "table ", pTab->zName,
           " has no column named ", pField->a[i].zName, 0);







|







84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
  }
  if( pField ){
    for(i=0; i<pField->nId; i++){
      pField->a[i].idx = -1;
    }
    for(i=0; i<pField->nId; i++){
      for(j=0; j<pTab->nCol; j++){
        if( sqliteStrICmp(pField->a[i].zName, pTab->aCol[j].zName)==0 ){
          pField->a[i].idx = j;
          break;
        }
      }
      if( j>=pTab->nCol ){
        sqliteSetString(&pParse->zErrMsg, "table ", pTab->zName,
           " has no column named ", pField->a[i].zName, 0);
117
118
119
120
121
122
123


124
125
126
127
128
129
130
131
        j = i;
      }else{
        for(j=0; j<pField->nId; j++){
          if( pField->a[j].idx==i ) break;
        }
      }
      if( pField && j>=pField->nId ){


        sqliteVdbeAddOp(v, OP_String, 0, 0, "", 0);
      }else{
        sqliteExprCode(pParse, pList->a[j].pExpr);
      }
    }
    sqliteVdbeAddOp(v, OP_MakeRecord, pTab->nCol, 0, 0, 0);
    sqliteVdbeAddOp(v, OP_Put, 0, 0, 0, 0);
    sqliteVdbeAddOp(v, OP_Close, 0, 0, 0, 0);







>
>
|







117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
        j = i;
      }else{
        for(j=0; j<pField->nId; j++){
          if( pField->a[j].idx==i ) break;
        }
      }
      if( pField && j>=pField->nId ){
        char *zDflt = pTab->aCol[i].zDflt;
        if( zDflt==0 ) zDflt = "";
        sqliteVdbeAddOp(v, OP_String, 0, 0, zDflt, 0);
      }else{
        sqliteExprCode(pParse, pList->a[j].pExpr);
      }
    }
    sqliteVdbeAddOp(v, OP_MakeRecord, pTab->nCol, 0, 0, 0);
    sqliteVdbeAddOp(v, OP_Put, 0, 0, 0, 0);
    sqliteVdbeAddOp(v, OP_Close, 0, 0, 0, 0);
139
140
141
142
143
144
145


146
147
148
149
150
151
152
153
          j = idx;
        }else{
          for(j=0; j<pField->nId; j++){
            if( pField->a[j].idx==idx ) break;
          }
        }
        if( pField && j>=pField->nId ){


          sqliteVdbeAddOp(v, OP_String, 0, 0, "", 0);
        }else{
          sqliteExprCode(pParse, pList->a[j].pExpr);
        }
      }
      sqliteVdbeAddOp(v, OP_MakeKey, pIdx->nField, 0, 0, 0);
      sqliteVdbeAddOp(v, OP_PutIdx, idx, 0, 0, 0);
      sqliteVdbeAddOp(v, OP_Close, idx, 0, 0, 0);







>
>
|







141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
          j = idx;
        }else{
          for(j=0; j<pField->nId; j++){
            if( pField->a[j].idx==idx ) break;
          }
        }
        if( pField && j>=pField->nId ){
          char *zDflt = pTab->aCol[idx].zDflt;
          if( zDflt==0 ) zDflt = "";
          sqliteVdbeAddOp(v, OP_String, 0, 0, zDflt, 0);
        }else{
          sqliteExprCode(pParse, pList->a[j].pExpr);
        }
      }
      sqliteVdbeAddOp(v, OP_MakeKey, pIdx->nField, 0, 0, 0);
      sqliteVdbeAddOp(v, OP_PutIdx, idx, 0, 0, 0);
      sqliteVdbeAddOp(v, OP_Close, idx, 0, 0, 0);
Changes to src/parse.y.
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
**
*************************************************************************
** 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.6 2000/05/31 20:00:52 drh Exp $
*/
%token_prefix TK_
%token_type {Token}
%extra_argument {Parse *pParse}
%syntax_error {
  sqliteSetNString(&pParse->zErrMsg,"syntax error near \"",0,TOKEN.z,TOKEN.n,
                   "\"", 1, 0);







|







22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
**
*************************************************************************
** 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.7 2000/06/03 18:06:53 drh Exp $
*/
%token_prefix TK_
%token_type {Token}
%extra_argument {Parse *pParse}
%syntax_error {
  sqliteSetNString(&pParse->zErrMsg,"syntax error near \"",0,TOKEN.z,TOKEN.n,
                   "\"", 1, 0);
88
89
90
91
92
93
94








95
96
97
98
99
100
101
102
signed ::= INTEGER.
signed ::= PLUS INTEGER.
signed ::= MINUS INTEGER.
carglist ::= carglist carg.
carglist ::= .
carg ::= CONSTRAINT ID ccons.
carg ::= ccons.








carg ::= DEFAULT expr.

// In addition to the type name, we also care about the primary key.
//
ccons ::= NOT NULL.
ccons ::= PRIMARY KEY sortorder.  
   {sqliteCreateIndex(pParse,0,0,0,0,0);}
ccons ::= UNIQUE.







>
>
>
>
>
>
>
>
|







88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
signed ::= INTEGER.
signed ::= PLUS INTEGER.
signed ::= MINUS INTEGER.
carglist ::= carglist carg.
carglist ::= .
carg ::= CONSTRAINT ID ccons.
carg ::= ccons.
carg ::= DEFAULT STRING(X).          {sqliteAddDefaultValue(pParse,&X,0);}
carg ::= DEFAULT ID(X).              {sqliteAddDefaultValue(pParse,&X,0);}
carg ::= DEFAULT INTEGER(X).         {sqliteAddDefaultValue(pParse,&X,0);}
carg ::= DEFAULT PLUS INTEGER(X).    {sqliteAddDefaultValue(pParse,&X,0);}
carg ::= DEFAULT MINUS INTEGER(X).   {sqliteAddDefaultValue(pParse,&X,1);}
carg ::= DEFAULT FLOAT(X).           {sqliteAddDefaultValue(pParse,&X,0);}
carg ::= DEFAULT PLUS FLOAT(X).      {sqliteAddDefaultValue(pParse,&X,0);}
carg ::= DEFAULT MINUS FLOAT(X).     {sqliteAddDefaultValue(pParse,&X,1);}
carg ::= DEFAULT NULL. 

// In addition to the type name, we also care about the primary key.
//
ccons ::= NOT NULL.
ccons ::= PRIMARY KEY sortorder.  
   {sqliteCreateIndex(pParse,0,0,0,0,0);}
ccons ::= UNIQUE.
216
217
218
219
220
221
222






223






224
225
226
227
228
229
230
%destructor itemlist {sqliteExprListDelete($$);}
%type item {Expr*}
%destructor item {sqliteExprDelete($$);}

itemlist(A) ::= itemlist(X) COMMA item(Y).  {A = sqliteExprListAppend(X,Y,0);}
itemlist(A) ::= item(X).     {A = sqliteExprListAppend(0,X,0);}
item(A) ::= INTEGER(X).      {A = sqliteExpr(TK_INTEGER, 0, 0, &X);}






item(A) ::= FLOAT(X).        {A = sqliteExpr(TK_FLOAT, 0, 0, &X);}






item(A) ::= STRING(X).       {A = sqliteExpr(TK_STRING, 0, 0, &X);}

%type fieldlist_opt {IdList*}
%destructor fieldlist_opt {sqliteIdListDelete($$);}
%type fieldlist {IdList*}
%destructor fieldlist {sqliteIdListDelete($$);}








>
>
>
>
>
>

>
>
>
>
>
>







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
%destructor itemlist {sqliteExprListDelete($$);}
%type item {Expr*}
%destructor item {sqliteExprDelete($$);}

itemlist(A) ::= itemlist(X) COMMA item(Y).  {A = sqliteExprListAppend(X,Y,0);}
itemlist(A) ::= item(X).     {A = sqliteExprListAppend(0,X,0);}
item(A) ::= INTEGER(X).      {A = sqliteExpr(TK_INTEGER, 0, 0, &X);}
item(A) ::= PLUS INTEGER(X). {A = sqliteExpr(TK_INTEGER, 0, 0, &X);}
item(A) ::= MINUS INTEGER(X). {
  A = sqliteExpr(TK_INTEGER, 0, 0, 0);
  A->token.z = 0;
  sqliteSetNString(&A->token.z, "-", 1, X.z, X.n, 0);
}
item(A) ::= FLOAT(X).        {A = sqliteExpr(TK_FLOAT, 0, 0, &X);}
item(A) ::= PLUS FLOAT(X).   {A = sqliteExpr(TK_FLOAT, 0, 0, &X);}
item(A) ::= MINUS FLOAT(X).  {
  A = sqliteExpr(TK_FLOAT, 0, 0, 0);
  A->token.z = 0;
  sqliteSetNString(&A->token.z, "-", 1, X.z, X.n, 0);
}
item(A) ::= STRING(X).       {A = sqliteExpr(TK_STRING, 0, 0, &X);}

%type fieldlist_opt {IdList*}
%destructor fieldlist_opt {sqliteIdListDelete($$);}
%type fieldlist {IdList*}
%destructor fieldlist {sqliteIdListDelete($$);}

Changes to src/select.c.
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
**   drh@hwaci.com
**   http://www.hwaci.com/drh/
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle SELECT statements.
**
** $Id: select.c,v 1.4 2000/06/02 01:51:20 drh Exp $
*/
#include "sqliteInt.h"


/*
** Process a SELECT statement.
*/







|







20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
**   drh@hwaci.com
**   http://www.hwaci.com/drh/
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle SELECT statements.
**
** $Id: select.c,v 1.5 2000/06/03 18:06:53 drh Exp $
*/
#include "sqliteInt.h"


/*
** Process a SELECT statement.
*/
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
      if( pTabList->nId>1 ){
        char *zName = 0;
        Table *pTab = pTabList->a[p->iTable].pTab;
        char *zTab;

        zTab = pTabList->a[p->iTable].zAlias;
        if( zTab==0 ) zTab = pTab->zName;
        sqliteSetString(&zName, zTab, ".", pTab->azCol[p->iField], 0);
        sqliteVdbeAddOp(v, OP_ColumnName, i, 0, zName, 0);
        sqliteFree(zName);
      }else{
        Table *pTab = pTabList->a[0].pTab;
        sqliteVdbeAddOp(v, OP_ColumnName, i, 0, pTab->azCol[p->iField], 0);
      }
    }
  }

  /* Initialize the stack to contain aggregate seed values
  */
  if( isAgg ){







|




|







161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
      if( pTabList->nId>1 ){
        char *zName = 0;
        Table *pTab = pTabList->a[p->iTable].pTab;
        char *zTab;

        zTab = pTabList->a[p->iTable].zAlias;
        if( zTab==0 ) zTab = pTab->zName;
        sqliteSetString(&zName, zTab, ".", pTab->aCol[p->iField].zName, 0);
        sqliteVdbeAddOp(v, OP_ColumnName, i, 0, zName, 0);
        sqliteFree(zName);
      }else{
        Table *pTab = pTabList->a[0].pTab;
        sqliteVdbeAddOp(v, OP_ColumnName, i, 0, pTab->aCol[p->iField].zName, 0);
      }
    }
  }

  /* Initialize the stack to contain aggregate seed values
  */
  if( isAgg ){
Changes to src/sqliteInt.h.
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
** Author contact information:
**   drh@hwaci.com
**   http://www.hwaci.com/drh/
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.10 2000/06/02 13:28:00 drh Exp $
*/
#include "sqlite.h"
#include "dbbe.h"
#include "vdbe.h"
#include "parse.h"
#include <gdbm.h>
#include <stdio.h>







|







19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
** Author contact information:
**   drh@hwaci.com
**   http://www.hwaci.com/drh/
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.11 2000/06/03 18:06:53 drh Exp $
*/
#include "sqlite.h"
#include "dbbe.h"
#include "vdbe.h"
#include "parse.h"
#include <gdbm.h>
#include <stdio.h>
73
74
75
76
77
78
79

80
81
82
83
84
85
86
#define FN_Max        3
#define FN_Sum        4
#define FN_Avg        5

/*
** Forward references to structures
*/

typedef struct Table Table;
typedef struct Index Index;
typedef struct Instruction Instruction;
typedef struct Expr Expr;
typedef struct ExprList ExprList;
typedef struct Parse Parse;
typedef struct Token Token;







>







73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
#define FN_Max        3
#define FN_Sum        4
#define FN_Avg        5

/*
** Forward references to structures
*/
typedef struct Column Column;
typedef struct Table Table;
typedef struct Index Index;
typedef struct Instruction Instruction;
typedef struct Expr Expr;
typedef struct ExprList ExprList;
typedef struct Parse Parse;
typedef struct Token Token;
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

/*
** Possible values for the flags field of sqlite
*/
#define SQLITE_VdbeTrace    0x00000001
#define SQLITE_Initialized  0x00000002











/*
** Each table is represented in memory by
** an instance of the following structure
*/
struct Table {
  char *zName;        /* Name of the table */
  Table *pHash;       /* Next table with same hash on zName */
  int nCol;           /* Number of columns in this table */

  int readOnly;       /* True if this table should not be written by the user */
  char **azCol;       /* Name of each column */
  Index *pIndex;      /* List of indices on this table. */
};

/*
** Each index is represented in memory by and
** instance of the following structure.
*/
struct Index {
  char *zName;        /* Name of this index */
  Index *pHash;       /* Next index with the same hash on zName */
  int nField;         /* Number of fields in the table indexed by this index */
  int *aiField;       /* Indices of fields used by this index.  1st is 0 */
  Table *pTable;      /* The table being indexed */

  Index *pNext;       /* The next index associated with the same table */
};

/*
** Each token coming out of the lexer is an instance of
** this structure.
*/







>
>
>
>
>
>
>
>
>
>








>

<













>







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

/*
** Possible values for the flags field of sqlite
*/
#define SQLITE_VdbeTrace    0x00000001
#define SQLITE_Initialized  0x00000002

/*
** information about each column of a table is held in an instance
** of this structure.
*/
struct Column {
  char *zName;        /* Name of this column */
  char *zDflt;        /* Default value of this column */
  int notNull;        /* True if there is a NOT NULL constraing */
};

/*
** Each table is represented in memory by
** an instance of the following structure
*/
struct Table {
  char *zName;        /* Name of the table */
  Table *pHash;       /* Next table with same hash on zName */
  int nCol;           /* Number of columns in this table */
  Column *aCol;       /* Information about each column */
  int readOnly;       /* True if this table should not be written by the user */

  Index *pIndex;      /* List of indices on this table. */
};

/*
** Each index is represented in memory by and
** instance of the following structure.
*/
struct Index {
  char *zName;        /* Name of this index */
  Index *pHash;       /* Next index with the same hash on zName */
  int nField;         /* Number of fields in the table indexed by this index */
  int *aiField;       /* Indices of fields used by this index.  1st is 0 */
  Table *pTable;      /* The table being indexed */
  int isUnique;       /* True if keys must all be unique */
  Index *pNext;       /* The next index associated with the same table */
};

/*
** Each token coming out of the lexer is an instance of
** this structure.
*/
240
241
242
243
244
245
246

247
248
249
250
251
252
253
Expr *sqliteExpr(int, Expr*, Expr*, Token*);
Expr *sqliteExprFunction(ExprList*, Token*);
void sqliteExprDelete(Expr*);
ExprList *sqliteExprListAppend(ExprList*,Expr*,Token*);
void sqliteExprListDelete(ExprList*);
void sqliteStartTable(Parse*,Token*,Token*);
void sqliteAddColumn(Parse*,Token*);

void sqliteEndTable(Parse*,Token*);
void sqliteDropTable(Parse*, Token*);
void sqliteDeleteTable(sqlite*, Table*);
void sqliteInsert(Parse*, Token*, ExprList*, IdList*);
IdList *sqliteIdListAppend(IdList*, Token*);
void sqliteIdListAddAlias(IdList*, Token*);
void sqliteIdListDelete(IdList*);







>







252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
Expr *sqliteExpr(int, Expr*, Expr*, Token*);
Expr *sqliteExprFunction(ExprList*, Token*);
void sqliteExprDelete(Expr*);
ExprList *sqliteExprListAppend(ExprList*,Expr*,Token*);
void sqliteExprListDelete(ExprList*);
void sqliteStartTable(Parse*,Token*,Token*);
void sqliteAddColumn(Parse*,Token*);
void sqliteAddDefaultValue(Parse*,Token*,int);
void sqliteEndTable(Parse*,Token*);
void sqliteDropTable(Parse*, Token*);
void sqliteDeleteTable(sqlite*, Table*);
void sqliteInsert(Parse*, Token*, ExprList*, IdList*);
IdList *sqliteIdListAppend(IdList*, Token*);
void sqliteIdListAddAlias(IdList*, Token*);
void sqliteIdListDelete(IdList*);
Changes to src/update.c.
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
**   drh@hwaci.com
**   http://www.hwaci.com/drh/
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle UPDATE statements.
**
** $Id: update.c,v 1.2 2000/06/02 01:17:38 drh Exp $
*/
#include "sqliteInt.h"

/*
** Process an UPDATE statement.
*/
void sqliteUpdate(







|







20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
**   drh@hwaci.com
**   http://www.hwaci.com/drh/
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle UPDATE statements.
**
** $Id: update.c,v 1.3 2000/06/03 18:06:53 drh Exp $
*/
#include "sqliteInt.h"

/*
** Process an UPDATE statement.
*/
void sqliteUpdate(
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
    if( sqliteExprResolveIds(pParse, pTabList, pChanges->a[i].pExpr) ){
      goto update_cleanup;
    }
    if( sqliteExprCheck(pParse, pChanges->a[i].pExpr, 0, 0) ){
      goto update_cleanup;
    }
    for(j=0; j<pTab->nCol; j++){
      if( strcmp(pTab->azCol[j], pChanges->a[i].zName)==0 ){
        pChanges->a[i].idx = j;
        aXRef[j] = i;
        break;
      }
    }
    if( j>=pTab->nCol ){
      sqliteSetString(&pParse->zErrMsg, "no such field: ", 







|







92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
    if( sqliteExprResolveIds(pParse, pTabList, pChanges->a[i].pExpr) ){
      goto update_cleanup;
    }
    if( sqliteExprCheck(pParse, pChanges->a[i].pExpr, 0, 0) ){
      goto update_cleanup;
    }
    for(j=0; j<pTab->nCol; j++){
      if( strcmp(pTab->aCol[j].zName, pChanges->a[i].zName)==0 ){
        pChanges->a[i].idx = j;
        aXRef[j] = i;
        break;
      }
    }
    if( j>=pTab->nCol ){
      sqliteSetString(&pParse->zErrMsg, "no such field: ", 
Changes to test/copy.test.
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
#   drh@hwaci.com
#   http://www.hwaci.com/drh/
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the COPY statement.
#
# $Id: copy.test,v 1.1 2000/05/30 17:30:36 drh 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]







|







19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
#   drh@hwaci.com
#   http://www.hwaci.com/drh/
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the COPY statement.
#
# $Id: copy.test,v 1.2 2000/06/03 18:06:53 drh 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]
90
91
92
93
94
95
96


























97
98
99
100
101
102
103
  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}



























# Try inserting really long data
#
set x {}
for {set i 0} {$i<100} {incr i} {
  append x "($i)-abcdefghijklmnopqrstyvwxyz-ABCDEFGHIJKLMNOPQRSTUVWXYZ-"
}







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







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
  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.
#
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)}
do_test copy-1.8 {
  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-"
}
Changes to test/delete.test.
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
#   drh@hwaci.com
#   http://www.hwaci.com/drh/
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the DELETE FROM statement.
#
# $Id: delete.test,v 1.2 2000/05/30 03:12:22 drh Exp $

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

# Try to delete from a non-existant table.
#
do_test delete-1.1 {
  set v [catch {execsql {DELETE FROM test1}} msg]
  lappend v $msg
} {1 {no such table: test1}}

# Try to delete from sqlite_master
#
do_test delete-2.1 {
  set v [catch {execsql {DELETE FROM sqlite_master}} msg]
  lappend v $msg
} {1 {table sqlite_master may not be modified}}


























finish_test







|


















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



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
#   drh@hwaci.com
#   http://www.hwaci.com/drh/
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the DELETE FROM statement.
#
# $Id: delete.test,v 1.3 2000/06/03 18:06:53 drh Exp $

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

# Try to delete from a non-existant table.
#
do_test delete-1.1 {
  set v [catch {execsql {DELETE FROM test1}} msg]
  lappend v $msg
} {1 {no such table: test1}}

# Try to delete from sqlite_master
#
do_test delete-2.1 {
  set v [catch {execsql {DELETE FROM sqlite_master}} msg]
  lappend v $msg
} {1 {table sqlite_master may not be modified}}

# Delete selected entries from a table with and without an index.
#
do_test delete-3.1a {
  execsql {CREATE TABLE table1(f1 int, f2 int)}
  execsql {INSERT INTO table1 VALUES(1,2)}
  execsql {INSERT INTO table1 VALUES(2,4)}
  execsql {INSERT INTO table1 VALUES(3,8)}
  execsql {INSERT INTO table1 VALUES(4,16)}
  execsql {SELECT * FROM table1 ORDER BY f1}
} {1 2 2 4 3 8 4 16}
do_test delete-3.1b {
  execsql {DELETE FROM table1 WHERE f1=3}
  execsql {SELECT * FROM table1 ORDER BY f1}
} {1 2 2 4 4 16}
do_test delete-3.1c {
  execsql {CREATE INDEX index1 ON table1(f1)}
  execsql {DELETE FROM table1 WHERE f1=3}
  execsql {SELECT * FROM table1 ORDER BY f1}
} {1 2 2 4 4 16}
do_test delete-3.1d {
  execsql {DELETE FROM table1 WHERE f1=2}
  execsql {SELECT * FROM table1 ORDER BY f1}
} {1 2 4 16}


finish_test
Changes to test/index.test.
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
#   drh@hwaci.com
#   http://www.hwaci.com/drh/
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the CREATE INDEX statement.
#
# $Id: index.test,v 1.2 2000/05/30 03:12:22 drh Exp $

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

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







|







19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
#   drh@hwaci.com
#   http://www.hwaci.com/drh/
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the CREATE INDEX statement.
#
# $Id: index.test,v 1.3 2000/06/03 18:06:53 drh Exp $

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

# Create a basic index and verify it is added to sqlite_master
#
do_test index-1.1 {
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
  lsort -dictionary [glob -nocomplain testdb/index*.tbl]
} {}

# Create a table and insert values into that table.  Then create
# an index on that table.  Verify that we can select values
# from the table correctly using the index.
#



do_test index-4.1 {
  execsql {CREATE TABLE test1(cnt int, power int)}
  for {set i 1} {$i<20} {incr i} {
    execsql "INSERT INTO test1 VALUES($i,[expr {int(pow(2,$i))}])"
  }
  execsql {CREATE INDEX index1 ON test1(cnt)}
  execsql {CREATE INDEX index2 ON test1(cnt)}
  execsql {SELECT name FROM sqlite_master ORDER BY name}
} {index1 index2 test1}
do_test index-4.2 {
  execsql {SELECT cnt FROM test1 WHERE power=4}
} {2}
do_test index-4.3 {
  execsql {SELECT cnt FROM test1 WHERE power=1024}
} {10}
do_test index-4.4 {
  execsql {SELECT power FROM test1 WHERE cnt=6}
} {64}
do_test index-4.5 {




























  execsql {DROP TABLE test1}

} {}

# Do not allow indices to be added to sqlite_master
#
do_test index-5.1 {
  set v [catch {execsql {CREATE INDEX index1 ON sqlite_master(name)}} msg]
  lappend v $msg







>
>
>





|
|

|










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

>







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
  lsort -dictionary [glob -nocomplain testdb/index*.tbl]
} {}

# Create a table and insert values into that table.  Then create
# an index on that table.  Verify that we can select values
# from the table correctly using the index.
#
# Note that the index names "index9" and "indext" are chosen because
# they both have the same hash.
#
do_test index-4.1 {
  execsql {CREATE TABLE test1(cnt int, power int)}
  for {set i 1} {$i<20} {incr i} {
    execsql "INSERT INTO test1 VALUES($i,[expr {int(pow(2,$i))}])"
  }
  execsql {CREATE INDEX index9 ON test1(cnt)}
  execsql {CREATE INDEX indext ON test1(power)}
  execsql {SELECT name FROM sqlite_master ORDER BY name}
} {index9 indext test1}
do_test index-4.2 {
  execsql {SELECT cnt FROM test1 WHERE power=4}
} {2}
do_test index-4.3 {
  execsql {SELECT cnt FROM test1 WHERE power=1024}
} {10}
do_test index-4.4 {
  execsql {SELECT power FROM test1 WHERE cnt=6}
} {64}
do_test index-4.5 {
  execsql {DROP INDEX indext}
  execsql {SELECT power FROM test1 WHERE cnt=6}
} {64}
do_test index-4.6 {
  execsql {SELECT cnt FROM test1 WHERE power=1024}
} {10}
do_test index-4.7 {
  execsql {CREATE INDEX indext ON test1(cnt)}
  execsql {SELECT power FROM test1 WHERE cnt=6}
} {64}
do_test index-4.8 {
  execsql {SELECT cnt FROM test1 WHERE power=1024}
} {10}
do_test index-4.9 {
  execsql {DROP INDEX index9}
  execsql {SELECT power FROM test1 WHERE cnt=6}
} {64}
do_test index-4.10 {
  execsql {SELECT cnt FROM test1 WHERE power=1024}
} {10}
do_test index-4.11 {
  execsql {DROP INDEX indext}
  execsql {SELECT power FROM test1 WHERE cnt=6}
} {64}
do_test index-4.12 {
  execsql {SELECT cnt FROM test1 WHERE power=1024}
} {10}
do_test index-4.13 {
  execsql {DROP TABLE test1}
  execsql {SELECT name FROM sqlite_master ORDER BY name}
} {}

# Do not allow indices to be added to sqlite_master
#
do_test index-5.1 {
  set v [catch {execsql {CREATE INDEX index1 ON sqlite_master(name)}} msg]
  lappend v $msg
201
202
203
204
205
206
207
208
209
210
211
212
213
214


215









216
217
  expr {[lsearch $code test1__primary_key]>0}
} {1}
do_test index-7.4 {
  execsql {DROP table test1}
  execsql {SELECT name FROM sqlite_master}
} {}

# Make sure we cannot drop a non-existant table.
#
do_test index-8.1 {
  set v [catch {execsql {DROP INDEX index1}} msg]
  lappend v $msg
} {1 {no such index: index1}}














finish_test







|






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


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
  expr {[lsearch $code test1__primary_key]>0}
} {1}
do_test index-7.4 {
  execsql {DROP table test1}
  execsql {SELECT name FROM sqlite_master}
} {}

# Make sure we cannot drop a non-existant index.
#
do_test index-8.1 {
  set v [catch {execsql {DROP INDEX index1}} msg]
  lappend v $msg
} {1 {no such index: index1}}

# Make sure we don't actually create an index when the EXPLAIN keyword
# is used.
#
do_test index-9.1 {
  execsql {CREATE TABLE tab1(a int)}
  execsql {EXPLAIN CREATE INDEX idx1 ON tab1(a)}
  execsql {SELECT name FROM sqlite_master WHERE tbl_name='tab1'}
} {tab1}
do_test index-9.2 {
  execsql {CREATE INDEX idx1 ON tab1(a)}
  execsql {SELECT name FROM sqlite_master WHERE tbl_name='tab1' ORDER BY name}
} {idx1 tab1}

finish_test
Changes to test/insert.test.
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
#   drh@hwaci.com
#   http://www.hwaci.com/drh/
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the INSERT statement.
#
# $Id: insert.test,v 1.2 2000/05/30 03:12:22 drh Exp $

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

# Try to insert into a non-existant table.
#
do_test insert-1.1 {







|







19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
#   drh@hwaci.com
#   http://www.hwaci.com/drh/
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the INSERT statement.
#
# $Id: insert.test,v 1.3 2000/06/03 18:06:53 drh Exp $

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

# Try to insert into a non-existant table.
#
do_test insert-1.1 {
93
94
95
96
97
98
99
100















































101
  execsql {INSERT INTO test1(two,three) VALUES(5,6)}
  execsql {SELECT * FROM test1 ORDER BY one}
} {{} 5 6 1 2 {}}
do_test insert-1.6c {
  execsql {INSERT INTO test1(three,one) VALUES(7,8)}
  execsql {SELECT * FROM test1 ORDER BY one}
} {{} 5 6 1 2 {} 8 {} 7}
















































finish_test








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

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
  execsql {INSERT INTO test1(two,three) VALUES(5,6)}
  execsql {SELECT * FROM test1 ORDER BY one}
} {{} 5 6 1 2 {}}
do_test insert-1.6c {
  execsql {INSERT INTO test1(three,one) VALUES(7,8)}
  execsql {SELECT * FROM test1 ORDER BY one}
} {{} 5 6 1 2 {} 8 {} 7}

# A table to use for testing default values
#
execsql {
  CREATE TABLE test2(
    f1 int default 111, 
    f2 real default -4.32,
    f3 text default hi,
    f4 text default 'abc-123',
    f5 varchar(10)
  )
}

do_test insert-2.1 {
  execsql {SELECT * from test2}
} {}
do_test insert-2.2 {
  execsql {INSERT INTO test2(f2,f4) VALUES(-2.22,'hi!')}
  execsql {SELECT * FROM test2}
} {111 -2.22 hi hi! {}}
do_test insert-2.3 {
  execsql {INSERT INTO test2(f1,f5) VALUES(1,'xyzzy')}
  execsql {SELECT * FROM test2 ORDER BY f1}
} {1 -4.32 hi abc-123 xyzzy 111 -2.22 hi hi! {}}

# Do additional inserts with default values, but this time
# on a table that has indices.  In particular we want to verify
# that the correct default values are inserted into the indices.
#
execsql {DELETE FROM test2}
execsql {CREATE INDEX index9 ON test2(f1,f2)}
execsql {CREATE INDEX indext ON test2(f4,f5)}

do_test insert-3.1 {
  execsql {SELECT * from test2}
} {}
do_test insert-3.2 {
  execsql {INSERT INTO test2(f2,f4) VALUES(-3.33,'hum')}
  execsql {SELECT * FROM test2 WHERE f1=111 AND f2=-3.33}
} {111 -3.33 hi hum {}}
do_test insert-3.3 {
  execsql {INSERT INTO test2(f1,f2,f5) VALUES(22,-4.44,'wham')}
  execsql {SELECT * FROM test2 WHERE f1=111 AND f2=-3.33}
} {111 -3.33 hi hum {}}
do_test insert-3.4 {
  execsql {SELECT * FROM test2 WHERE f1=22 AND f2=-4.44}
} {22 -4.44 hi abc-123 wham}

finish_test
Added test/vacuum.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
# Copyright (c) 1999, 2000 D. Richard Hipp
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public
# License as published by the Free Software Foundation; either
# version 2 of the License, or (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
# General Public License for more details.
# 
# You should have received a copy of the GNU General Public
# License along with this library; if not, write to the
# Free Software Foundation, Inc., 59 Temple Place - Suite 330,
# Boston, MA  02111-1307, USA.
#
# Author contact information:
#   drh@hwaci.com
#   http://www.hwaci.com/drh/
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the VACUUM statement.
#
# $Id: vacuum.test,v 1.1 2000/06/03 18:06:54 drh Exp $

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

# Try to vacuum a non-existant table.
#
do_test vacuum-1.1 {
  set v [catch {execsql {VACUUM dummy1}} msg]
  lappend v $msg
} {1 {no such table or index: dummy1}}

# It is OK to vacuum sqlite_master...
#
do_test vacuum-1.2 {
  set v [catch {execsql {VACUUM sqlite_master}} msg]
  lappend v $msg
} {0 {}}

# Create some tables and indices to test against.
#
execsql {CREATE TABLE test1(a int)}
execsql {CREATE TABLE test2(b int)}
execsql {CREATE INDEX index1 ON test1(a)}
execsql {INSERT INTO test1 VALUES(1)}
execsql {INSERT INTO test1 VALUES(1)}
execsql {INSERT INTO test1 VALUES(2)}
execsql {INSERT INTO test1 VALUES(3)}
execsql {INSERT INTO test2 VALUES(4)}

do_test vacuum-1.3 {
  set b1 [file mtime testdb/test1.tbl]
  set b2 [file mtime testdb/test2.tbl]
  set b3 [file mtime testdb/index1.tbl]
  after 1000
  execsql {VACUUM test1}
  set a1 [file mtime testdb/test1.tbl]
  set a2 [file mtime testdb/test2.tbl]
  set a3 [file mtime testdb/index1.tbl]
  expr {$a1>$b1 && $a2==$b2 && $a3==$b3}
} {1}
do_test vacuum-1.4 {
  set b1 [file mtime testdb/test1.tbl]
  set b2 [file mtime testdb/test2.tbl]
  set b3 [file mtime testdb/index1.tbl]
  after 1000
  execsql {VACUUM}
  set a1 [file mtime testdb/test1.tbl]
  set a2 [file mtime testdb/test2.tbl]
  set a3 [file mtime testdb/index1.tbl]
  expr {$a1>$b1 && $a2>$b2 && $a3>$b3}
} {1}


finish_test