/ Check-in [8cce4d27]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment::-) (CVS 102)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 8cce4d279de00da45c5970c8f0946f49e03e6846
User & Date: drh 2000-06-19 19:09:09
Context
2000-06-19
19:10
:-) (CVS 103) check-in: af14a5b3 user: drh tags: trunk
19:09
:-) (CVS 102) check-in: 8cce4d27 user: drh tags: trunk
2000-06-17
13:12
:-) (CVS 101) check-in: 6ed35a1d user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/parse.y.

22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
..
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
...
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
...
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
...
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
**
*************************************************************************
** 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.21 2000/06/16 20:51:26 drh Exp $
*/
%token_prefix TK_
%token_type {Token}
%extra_argument {Parse *pParse}
%syntax_error {
  sqliteSetString(&pParse->zErrMsg,"syntax error",0);
  pParse->sErrToken = TOKEN;
................................................................................


// Input is zero or more commands.
input ::= cmdlist.

// These are extra tokens used by the lexer but never seen by the
// parser.  We put them in a rule so that the parser generator will
// add them to the sqliteTokens.h output file.
//
input ::= END_OF_FILE ILLEGAL SPACE UNCLOSED_STRING COMMENT FUNCTION
          UMINUS FIELD AGG_FUNCTION.

// A list of commands is zero or more commands
//
cmdlist ::= ecmd.
................................................................................
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.
ccons ::= CHECK LP expr RP.

// For the time being, the only constraint we care about is the primary
// key.
//
conslist_opt ::= .
conslist_opt ::= COMMA conslist.
conslist ::= conslist COMMA tcons.
conslist ::= tcons.
tcons ::= CONSTRAINT id tcons2.
tcons ::= tcons2.
tcons2 ::= PRIMARY KEY LP idxlist(X) RP.  
      {sqliteCreateIndex(pParse,0,0,X,0,0);}
tcons2 ::= UNIQUE LP idlist RP.
tcons2 ::= CHECK expr.
idlist ::= idlist COMMA id.
idlist ::= id.

// The next command format is dropping tables.
//
................................................................................
%type from {IdList*}
%destructor from {sqliteIdListDelete($$);}

from(A) ::= FROM seltablist(X).               {A = X;}
stl_prefix(A) ::= seltablist(X) COMMA.        {A = X;}
stl_prefix(A) ::= .                           {A = 0;}
seltablist(A) ::= stl_prefix(X) id(Y).        {A = sqliteIdListAppend(X,&Y);}
seltablist(A) ::= stl_prefix(X) id(Y) AS id(Z).
   {A = sqliteIdListAppend(X,&Y);
    sqliteIdListAddAlias(A,&Z);}

%type orderby_opt {ExprList*}
%destructor orderby_opt {sqliteExprListDelete($$);}
%type sortlist {ExprList*}
%destructor sortlist {sqliteExprListDelete($$);}
................................................................................

sortorder(A) ::= ASC.      {A = 0;}
sortorder(A) ::= DESC.     {A = 1;}
sortorder(A) ::= .         {A = 0;}

%type groupby_opt {ExprList*}
%destructor groupby_opt {sqliteExprListDelete($$);}
groupby_opt(A) ::= .     {A = 0;}
groupby_opt(A) ::= GROUP BY exprlist(X).  {A = X;}

%type having_opt {Expr*}
%destructor having_opt {sqliteExprDelete($$);}
having_opt(A) ::= .      {A = 0;}
having_opt(A) ::= HAVING expr(X).  {A = X;}


cmd ::= DELETE FROM id(X) where_opt(Y).
    {sqliteDeleteFrom(pParse, &X, Y);}

%type where_opt {Expr*}







|







 







|







 







<
|












|
<







 







|







 







|




|







22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
..
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
...
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
...
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
...
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
**
*************************************************************************
** 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.22 2000/06/19 19:09:09 drh Exp $
*/
%token_prefix TK_
%token_type {Token}
%extra_argument {Parse *pParse}
%syntax_error {
  sqliteSetString(&pParse->zErrMsg,"syntax error",0);
  pParse->sErrToken = TOKEN;
................................................................................


// Input is zero or more commands.
input ::= cmdlist.

// These are extra tokens used by the lexer but never seen by the
// parser.  We put them in a rule so that the parser generator will
// add them to the parse.h output file.
//
input ::= END_OF_FILE ILLEGAL SPACE UNCLOSED_STRING COMMENT FUNCTION
          UMINUS FIELD AGG_FUNCTION.

// A list of commands is zero or more commands
//
cmdlist ::= ecmd.
................................................................................
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.
ccons ::= CHECK LP expr RP.

// For the time being, the only constraint we care about is the primary
// key.
//
conslist_opt ::= .
conslist_opt ::= COMMA conslist.
conslist ::= conslist COMMA tcons.
conslist ::= tcons.
tcons ::= CONSTRAINT id tcons2.
tcons ::= tcons2.
tcons2 ::= PRIMARY KEY LP idxlist(X) RP. {sqliteCreateIndex(pParse,0,0,X,0,0);}

tcons2 ::= UNIQUE LP idlist RP.
tcons2 ::= CHECK expr.
idlist ::= idlist COMMA id.
idlist ::= id.

// The next command format is dropping tables.
//
................................................................................
%type from {IdList*}
%destructor from {sqliteIdListDelete($$);}

from(A) ::= FROM seltablist(X).               {A = X;}
stl_prefix(A) ::= seltablist(X) COMMA.        {A = X;}
stl_prefix(A) ::= .                           {A = 0;}
seltablist(A) ::= stl_prefix(X) id(Y).        {A = sqliteIdListAppend(X,&Y);}
seltablist(A) ::= stl_prefix(X) id(Y) as id(Z).
   {A = sqliteIdListAppend(X,&Y);
    sqliteIdListAddAlias(A,&Z);}

%type orderby_opt {ExprList*}
%destructor orderby_opt {sqliteExprListDelete($$);}
%type sortlist {ExprList*}
%destructor sortlist {sqliteExprListDelete($$);}
................................................................................

sortorder(A) ::= ASC.      {A = 0;}
sortorder(A) ::= DESC.     {A = 1;}
sortorder(A) ::= .         {A = 0;}

%type groupby_opt {ExprList*}
%destructor groupby_opt {sqliteExprListDelete($$);}
groupby_opt(A) ::= .                      {A = 0;}
groupby_opt(A) ::= GROUP BY exprlist(X).  {A = X;}

%type having_opt {Expr*}
%destructor having_opt {sqliteExprDelete($$);}
having_opt(A) ::= .                {A = 0;}
having_opt(A) ::= HAVING expr(X).  {A = X;}


cmd ::= DELETE FROM id(X) where_opt(Y).
    {sqliteDeleteFrom(pParse, &X, Y);}

%type where_opt {Expr*}

Changes to src/select.c.

20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
...
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
**   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.23 2000/06/08 15:10:48 drh Exp $
*/
#include "sqliteInt.h"

/*
** Allocate a new Select structure and return a pointer to that
** structure.
*/
................................................................................
      break;
    }
    case TK_INTERSECT: {
      int tab1, tab2;
      int iCont, iBreak;

      /* INTERSECT is different from the others since it requires
      ** two temporary tables.  Hence it has its own case.  Begine
      ** by allocating the tables we will need.
      */
      tab1 = pParse->nTab++;
      tab2 = pParse->nTab++;
      if( p->pOrderBy && matchOrderbyToColumn(pParse,p,p->pOrderBy,tab1,1) ){
        return 1;
      }







|







 







|







20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
...
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
**   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.24 2000/06/19 19:09:09 drh Exp $
*/
#include "sqliteInt.h"

/*
** Allocate a new Select structure and return a pointer to that
** structure.
*/
................................................................................
      break;
    }
    case TK_INTERSECT: {
      int tab1, tab2;
      int iCont, iBreak;

      /* INTERSECT is different from the others since it requires
      ** two temporary tables.  Hence it has its own case.  Begin
      ** by allocating the tables we will need.
      */
      tab1 = pParse->nTab++;
      tab2 = pParse->nTab++;
      if( p->pOrderBy && matchOrderbyToColumn(pParse,p,p->pOrderBy,tab1,1) ){
        return 1;
      }

Changes to src/update.c.

20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
..
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
**   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.6 2000/06/17 13:12:40 drh Exp $
*/
#include "sqliteInt.h"

/*
** Process an UPDATE statement.
*/
void sqliteUpdate(
................................................................................
    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 ){
        aXRef[j] = i;
        break;
      }
    }
    if( j>=pTab->nCol ){
      sqliteSetString(&pParse->zErrMsg, "no such field: ", 
         pChanges->a[i].zName, 0);







|







 







|







20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
..
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
**   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.7 2000/06/19 19:09:09 drh Exp $
*/
#include "sqliteInt.h"

/*
** Process an UPDATE statement.
*/
void sqliteUpdate(
................................................................................
    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( sqliteStrICmp(pTab->aCol[j].zName, pChanges->a[i].zName)==0 ){
        aXRef[j] = i;
        break;
      }
    }
    if( j>=pTab->nCol ){
      sqliteSetString(&pParse->zErrMsg, "no such field: ", 
         pChanges->a[i].zName, 0);

Changes to test/update.test.

19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
..
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
#   drh@hwaci.com
#   http://www.hwaci.com/drh/
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the UPDATE statement.
#
# $Id: update.test,v 1.2 2000/06/08 16:26:25 drh Exp $

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

# Try to update an non-existent table
#
do_test update-1.1 {
................................................................................
  execsql {UPDATE test1 SET f2=f2/3 WHERE f1>5}
  execsql {SELECT * FROM test1 ORDER BY f1}
} {1 2 2 4 3 8 4 16 5 32 6 64 7 128 8 256 9 512 10 1024}

# Swap the values of f1 and f2 for all elements
#
do_test update-3.8 {
  execsql {UPDATE test1 SET f2=f1, f1=f2}
  execsql {SELECT * FROM test1 ORDER BY f1}
} {2 1 4 2 8 3 16 4 32 5 64 6 128 7 256 8 512 9 1024 10}

# Create an index and make sure updating works with an index.
#
do_test update-3.9 {
  execsql {CREATE INDEX index1 ON test1(f1)}
  execsql {CREATE INDEX index2 ON test1(f1)}







|







 







|
|







19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
..
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
#   drh@hwaci.com
#   http://www.hwaci.com/drh/
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the UPDATE statement.
#
# $Id: update.test,v 1.3 2000/06/19 19:09:09 drh Exp $

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

# Try to update an non-existent table
#
do_test update-1.1 {
................................................................................
  execsql {UPDATE test1 SET f2=f2/3 WHERE f1>5}
  execsql {SELECT * FROM test1 ORDER BY f1}
} {1 2 2 4 3 8 4 16 5 32 6 64 7 128 8 256 9 512 10 1024}

# Swap the values of f1 and f2 for all elements
#
do_test update-3.8 {
  execsql {UPDATE test1 SET F2=f1, F1=f2}
  execsql {SELECT * FROM test1 ORDER BY F1}
} {2 1 4 2 8 3 16 4 32 5 64 6 128 7 256 8 512 9 1024 10}

# Create an index and make sure updating works with an index.
#
do_test update-3.9 {
  execsql {CREATE INDEX index1 ON test1(f1)}
  execsql {CREATE INDEX index2 ON test1(f1)}

Changes to www/changes.tcl.

12
13
14
15
16
17
18





19
20
21
22
23
24
25
}


proc chng {date desc} {
  puts "<DT><B>$date</B></DT>"
  puts "<DD><P><UL>$desc</UL></P></DD>"
}






chng {2000 June 16} {
<li>Added the concatenate string operator (||)</li>
}

chng {2000 June 12} {
<li>Added the fcnt() function to the SQL interpreter.  The fcnt() function







>
>
>
>
>







12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
}


proc chng {date desc} {
  puts "<DT><B>$date</B></DT>"
  puts "<DD><P><UL>$desc</UL></P></DD>"
}

chng {2000 June 19} {
<li>Bug fix: Column names in UPDATE statements used to be case sensitive.
    Now they are not.</li>
}

chng {2000 June 16} {
<li>Added the concatenate string operator (||)</li>
}

chng {2000 June 12} {
<li>Added the fcnt() function to the SQL interpreter.  The fcnt() function