SQLite

Check-in [36a8fe0ad0]
Login

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

Overview
Comment:Updates to the documentation. Changed version number to 2.4.0-beta1 (CVS 417)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 36a8fe0ad0ee2a67afafc04125dcc085ec1b5a13
User & Date: drh 2002-03-04 02:26:16.000
Context
2002-03-05
01:11
Add the ability to turn of calls to fsync() using the "synchronous" pragma. Increased the default cache size from 100 to 2000 and made the "cache_size" pragma persistent. (CVS 418) (check-in: 414da4af1f user: drh tags: trunk)
2002-03-04
02:26
Updates to the documentation. Changed version number to 2.4.0-beta1 (CVS 417) (check-in: 36a8fe0ad0 user: drh tags: trunk)
2002-03-03
23:06
More bugs fixed for views. (CVS 416) (check-in: 8130776230 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to VERSION. 
1

2.3.4

|

1

2.4.0-beta1
Changes to src/build.c.
︙︙
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

**     COPY
**     VACUUM
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**     PRAGMA
**
** $Id: build.c,v 1.83 2002/03/03 23:06:01 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** This routine is called after a single SQL statement has been
** parsed and we want to execute the VDBE code to implement 








|








21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

**     COPY
**     VACUUM
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**     PRAGMA
**
** $Id: build.c,v 1.84 2002/03/04 02:26:16 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** This routine is called after a single SQL statement has been
** parsed and we want to execute the VDBE code to implement
︙︙
1504
1505
1506
1507
1508
1509
1510
1511
1512
1513
1514
1515
1516
1517
1518
1519
1520
1521
1522
1523
1524
1525
1526
1527
1528
1529
1530
1531

  Vdbe *v;
  int addr, end;
  Index *pIdx;
  sqlite *db = pParse->db;

  zTab = sqliteTableNameFromToken(pTableName);
  if( sqlite_malloc_failed || zTab==0 ) goto copy_cleanup;
  pTab = sqliteFindTable(db, zTab);
  sqliteFree(zTab);
  if( pTab==0 ){
    sqliteSetNString(&pParse->zErrMsg, "no such table: ", 0, 
        pTableName->z, pTableName->n, 0);
    pParse->nErr++;
    goto copy_cleanup;
  }
  if( pTab->readOnly ){
    sqliteSetString(&pParse->zErrMsg, "table ", pTab->zName,
        " may not be modified", 0);
    pParse->nErr++;
    goto copy_cleanup;
  }
  v = sqliteGetVdbe(pParse);
  if( v ){
    int openOp;
    sqliteBeginMultiWriteOperation(pParse);
    addr = sqliteVdbeAddOp(v, OP_FileOpen, 0, 0);
    sqliteVdbeChangeP3(v, addr, pFilename->z, pFilename->n);
    sqliteVdbeDequoteP3(v, addr);








|

|
<
<
<
<
<
<
<
<
<
<
<








1504
1505
1506
1507
1508
1509
1510
1511
1512
1513











1514
1515
1516
1517
1518
1519
1520

  Vdbe *v;
  int addr, end;
  Index *pIdx;
  sqlite *db = pParse->db;

  zTab = sqliteTableNameFromToken(pTableName);
  if( sqlite_malloc_failed || zTab==0 ) goto copy_cleanup;
  pTab = sqliteTableNameToTable(pParse, zTab);
  sqliteFree(zTab);
  if( pTab==0 ) goto copy_cleanup;











  v = sqliteGetVdbe(pParse);
  if( v ){
    int openOp;
    sqliteBeginMultiWriteOperation(pParse);
    addr = sqliteVdbeAddOp(v, OP_FileOpen, 0, 0);
    sqliteVdbeChangeP3(v, addr, pFilename->z, pFilename->n);
    sqliteVdbeDequoteP3(v, addr);
︙︙
Changes to src/func.c.
︙︙
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

** This file contains the C functions that implement various SQL
** functions of SQLite.  
**
** There is only one exported symbol in this file - the function
** sqliteRegisterBuildinFunctions() found at the bottom of the file.
** All other code has file scope.
**
** $Id: func.c,v 1.13 2002/03/02 19:00:31 drh Exp $
*/
#include <ctype.h>
#include <math.h>
#include <stdlib.h>
#include <assert.h>
#include "sqliteInt.h"









|








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

** This file contains the C functions that implement various SQL
** functions of SQLite.  
**
** There is only one exported symbol in this file - the function
** sqliteRegisterBuildinFunctions() found at the bottom of the file.
** All other code has file scope.
**
** $Id: func.c,v 1.14 2002/03/04 02:26:16 drh Exp $
*/
#include <ctype.h>
#include <math.h>
#include <stdlib.h>
#include <assert.h>
#include "sqliteInt.h"
︙︙
238
239
240
241
242
243
244

245
246
247
248
249
250
251

*/
typedef struct StdDevCtx StdDevCtx;
struct StdDevCtx {
  double sum;     /* Sum of terms */
  double sum2;    /* Sum of the squares of terms */
};


/*
** Routines used to compute the standard deviation as an aggregate.
*/
static void stdDevStep(sqlite_func *context, int argc, const char **argv){
  StdDevCtx *p;
  double x;
  if( argc<1 ) return;








>








238
239
240
241
242
243
244
245
246
247
248
249
250
251
252

*/
typedef struct StdDevCtx StdDevCtx;
struct StdDevCtx {
  double sum;     /* Sum of terms */
  double sum2;    /* Sum of the squares of terms */
};

#if 0   /* Omit because math library is required */
/*
** Routines used to compute the standard deviation as an aggregate.
*/
static void stdDevStep(sqlite_func *context, int argc, const char **argv){
  StdDevCtx *p;
  double x;
  if( argc<1 ) return;
︙︙
259
260
261
262
263
264
265

266
267
268
269
270
271
272

  double rN = sqlite_aggregate_count(context);
  StdDevCtx *p = sqlite_aggregate_context(context, sizeof(*p));
  if( p && rN>1.0 ){
    sqlite_set_result_double(context, 
       sqrt((p->sum2 - p->sum*p->sum/rN)/(rN-1.0)));
  }
}


/*
** The following structure keeps track of state information for the
** count() aggregate function.
*/
typedef struct CountCtx CountCtx;
struct CountCtx {








>








260
261
262
263
264
265
266
267
268
269
270
271
272
273
274

  double rN = sqlite_aggregate_count(context);
  StdDevCtx *p = sqlite_aggregate_context(context, sizeof(*p));
  if( p && rN>1.0 ){
    sqlite_set_result_double(context, 
       sqrt((p->sum2 - p->sum*p->sum/rN)/(rN-1.0)));
  }
}
#endif

/*
** The following structure keeps track of state information for the
** count() aggregate function.
*/
typedef struct CountCtx CountCtx;
struct CountCtx {
︙︙
392
393
394
395
396
397
398

399

400
401
402
403
404
405
406
407
408
409
410
411

  } aAggs[] = {
    { "min",    1, minStep,      minMaxFinalize },
    { "max",    1, maxStep,      minMaxFinalize },
    { "sum",    1, sumStep,      sumFinalize    },
    { "avg",    1, sumStep,      avgFinalize    },
    { "count",  0, countStep,    countFinalize  },
    { "count",  1, countStep,    countFinalize  },

    { "stddev", 1, stdDevStep,   stdDevFinalize },

  };
  int i;

  for(i=0; i<sizeof(aFuncs)/sizeof(aFuncs[0]); i++){
    sqlite_create_function(db, aFuncs[i].zName,
           aFuncs[i].nArg, aFuncs[i].xFunc, 0);
  }
  for(i=0; i<sizeof(aAggs)/sizeof(aAggs[0]); i++){
    sqlite_create_aggregate(db, aAggs[i].zName,
           aAggs[i].nArg, aAggs[i].xStep, aAggs[i].xFinalize, 0);
  }
}








>

>













394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415

  } aAggs[] = {
    { "min",    1, minStep,      minMaxFinalize },
    { "max",    1, maxStep,      minMaxFinalize },
    { "sum",    1, sumStep,      sumFinalize    },
    { "avg",    1, sumStep,      avgFinalize    },
    { "count",  0, countStep,    countFinalize  },
    { "count",  1, countStep,    countFinalize  },
#if 0
    { "stddev", 1, stdDevStep,   stdDevFinalize },
#endif
  };
  int i;

  for(i=0; i<sizeof(aFuncs)/sizeof(aFuncs[0]); i++){
    sqlite_create_function(db, aFuncs[i].zName,
           aFuncs[i].nArg, aFuncs[i].xFunc, 0);
  }
  for(i=0; i<sizeof(aAggs)/sizeof(aAggs[0]); i++){
    sqlite_create_aggregate(db, aAggs[i].zName,
           aAggs[i].nArg, aAggs[i].xStep, aAggs[i].xFinalize, 0);
  }
}
Changes to www/c_interface.tcl. 
1
2
3
4
5
6
7
8
9
10
11

#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: c_interface.tcl,v 1.24 2002/02/19 22:42:06 drh Exp $}

puts {<html>
<head>
  <title>The C language interface to the SQLite library</title>
</head>
<body bgcolor=white>
<h1 align=center>




|








1
2
3
4
5
6
7
8
9
10
11

#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: c_interface.tcl,v 1.25 2002/03/04 02:26:17 drh Exp $}

puts {<html>
<head>
  <title>The C language interface to the SQLite library</title>
</head>
<body bgcolor=white>
<h1 align=center>
︙︙
637
638
639
640
641
642
643












































































644
645
646
647
648
649
650

</pre></blockquote>

<p>Here the apostrophy has been escaped and the SQL statement is well-formed.
When generating SQL on-the-fly from data that might contain a
single-quote character ('), it is always a good idea to use the
SQLite printf routines and the %q formatting option instead of <b>sprintf</b>.
</p>













































































<h2>Usage Examples</h2>

<p>For examples of how the SQLite C/C++ interface can be used,
refer to the source code for the <b>sqlite</b> program in the
file <b>src/shell.c</b> of the source tree.
Additional information about sqlite is available at








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








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

</pre></blockquote>

<p>Here the apostrophy has been escaped and the SQL statement is well-formed.
When generating SQL on-the-fly from data that might contain a
single-quote character ('), it is always a good idea to use the
SQLite printf routines and the %q formatting option instead of <b>sprintf</b>.
</p>

<h2>Adding New SQL Functions</h2>

<p>Beginning with version 2.4.0, SQLite allows the SQL language to be
extended with new functions implemented as C code.  The following interface
is used:
</p>

<blockquote><pre>
typedef struct sqlite_func sqlite_func;

int sqlite_create_function(
  sqlite *db,
  const char *zName,
  int nArg,
  void (*xFunc)(sqlite_func*,int,const char**),
  void *pUserData
);
int sqlite_create_aggregate(
  sqlite *db,
  const char *zName,
  int nArg,
  void (*xStep)(sqlite_func*,int,const char**),
  void (*xFinalize)(sqlite_func*),
  void *pUserData
);

char *sqlite_set_result_string(sqlite_func*,const char*,int);
void sqlite_set_result_int(sqlite_func*,int);
void sqlite_set_result_double(sqlite_func*,double);
void sqlite_set_result_error(sqlite_func*,const char*,int);

void *sqlite_user_data(sqlite_func*);
void *sqlite_aggregate_context(sqlite_func*, int nBytes);
int sqlite_aggregate_count(sqlite_func*);
</pre></blockquote>

<p>
The <b>sqlite_create_function()</b> interface is used to create 
regular functions and <b>sqlite_create_aggregate()</b> is used to
create new aggregate functions.  In both cases, the <b>db</b>
parameter is an open SQLite database on which the functions should
be registered, <b>zName</b> is the name of the new function,
<b>nArg</b> is the number of arguments, and <b>pUserData</b> is
a pointer which is passed through unchanged to the C implementation
of the function.
</p>

<p>
For regular functions, the <b>xFunc</b> callback is invoked once
for each function call.  The implementation of xFunc should call
one of the <b>sqlite_set_result_...</b> interfaces to return its
result.  The <b>sqlite_user_data()</b> routine can be used to
retrieve the <b>pUserData</b> pointer that was passed in when the
function was registered.
</p>

<p>
For aggregate functions, the <b>xStep</b> callback is invoked once
for each row in the result and then <b>xFinalize</b> is invoked at the
end to compute a final answer.  The xStep routine can use the
<b>sqlite_aggregate_context()</b> interface to allocate memory that
will be unique to that particular instance of the SQL function.
This memory will be automatically deleted after xFinalize is called.
The <b>sqlite_aggregate_count()</b> routine can be used to find out
how many rows of data were passed to the aggregate.  The xFinalize
callback should invoke one of the <b>sqlite_set_result_...</b>
interfaces to set the final result of the aggregate.
</p>

<p>
SQLite now implements all of its built-in functions using this
interface.  For additional information and examples on how to create
new SQL functions, review the SQLite source code in the file
<b>func.c</b>.
</p>

<h2>Usage Examples</h2>

<p>For examples of how the SQLite C/C++ interface can be used,
refer to the source code for the <b>sqlite</b> program in the
file <b>src/shell.c</b> of the source tree.
Additional information about sqlite is available at
︙︙
Changes to www/changes.tcl.
︙︙
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



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

chng {2002 Feb * (2.3.4)} {
<li>Change the name of the sanity_check PRAGMA to <b>integrity_check</b>
    and make it available in all compiles.</li>
<li>SELECT min() or max() of an indexed column with no WHERE or GROUP BY
    clause is handled as a special case which avoids a complete table scan.</li>
<li>Automatically generated ROWIDs are now sequential.</li>
<li>Do not allow dot-commands of the command-line shell to occur in the
    middle of a real SQL command.</li>
<li>Modifications to the "lemon" parser generator so that the parser tables
    are 4 times smaller.</li>
<li>Added support for user-defined functions implemented in C.</li>


<li>Added support for VIEWs.</li>
<li>Added the subquery flattening optimizer.</li>






}

chng {2002 Feb 18 (2.3.3)} {
<li>Allow identifiers to be quoted in square brackets, for compatibility
    with MS-Access.</li>
<li>Added support for sub-queries in the FROM clause of a SELECT.</li>
<li>More efficient implementation of sqliteFileExists() under Windows.








|










>
>


>
>
>
>
>
>








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



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

chng {2002 Mar * (2.4.0)} {
<li>Change the name of the sanity_check PRAGMA to <b>integrity_check</b>
    and make it available in all compiles.</li>
<li>SELECT min() or max() of an indexed column with no WHERE or GROUP BY
    clause is handled as a special case which avoids a complete table scan.</li>
<li>Automatically generated ROWIDs are now sequential.</li>
<li>Do not allow dot-commands of the command-line shell to occur in the
    middle of a real SQL command.</li>
<li>Modifications to the "lemon" parser generator so that the parser tables
    are 4 times smaller.</li>
<li>Added support for user-defined functions implemented in C.</li>
<li>Added support for new functions: <b>coalesce()</b>, <b>lower()</b>,
    <b>upper()</b>, and <b>random()</b>
<li>Added support for VIEWs.</li>
<li>Added the subquery flattening optimizer.</li>
<li>Modified the B-Tree and Pager modules so that disk pages that do not
    contain real data (free pages) are not journalled and are not
    written from memory back to the disk when they change.  This does not 
    impact database integrity, since the
    pages contain no real data, but it does make large INSERT operations
    about 2.5 times faster and large DELETEs about 5 times faster.</li>
}

chng {2002 Feb 18 (2.3.3)} {
<li>Allow identifiers to be quoted in square brackets, for compatibility
    with MS-Access.</li>
<li>Added support for sub-queries in the FROM clause of a SELECT.</li>
<li>More efficient implementation of sqliteFileExists() under Windows.
︙︙
Changes to www/formatchng.tcl. 
1
2
3
4
5
6
7
8
9
10
11

#
# Run this Tcl script to generate the formatchng.html file.
#
set rcsid {$Id: formatchng.tcl,v 1.2 2002/01/30 16:17:25 drh Exp $ }

puts {<html>
<head>
  <title>File Format Changes in SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>




|








1
2
3
4
5
6
7
8
9
10
11

#
# Run this Tcl script to generate the formatchng.html file.
#
set rcsid {$Id: formatchng.tcl,v 1.3 2002/03/04 02:26:17 drh Exp $ }

puts {<html>
<head>
  <title>File Format Changes in SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>
︙︙
85
86
87
88
89
90
91
92














93
94
95
96
97
98
99

  <td valign="top">2.2.5 to 2.3.0</td>
  <td valign="top">2002-Jan-30</td>
  <td>Beginning with version 2.3.0, SQLite supports some additional syntax
  (the "ON CONFLICT" clause) in the CREATE TABLE and CREATE INDEX statements
  that are stored in the SQLITE_MASTER table.  If you create a database that
  contains this new syntax, then try to read that database using version 2.2.5
  or earlier, the parser will not understand the new syntax and you will get
  an error.  Otherwise, databases for 2.2.x and 2.3.x are interchangeable.</p>














</tr>
</table>
</blockquote>

<p>
To perform a database reload, have ready versions of the
<b>sqlite</b> command-line utility for both the old and new








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








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

  <td valign="top">2.2.5 to 2.3.0</td>
  <td valign="top">2002-Jan-30</td>
  <td>Beginning with version 2.3.0, SQLite supports some additional syntax
  (the "ON CONFLICT" clause) in the CREATE TABLE and CREATE INDEX statements
  that are stored in the SQLITE_MASTER table.  If you create a database that
  contains this new syntax, then try to read that database using version 2.2.5
  or earlier, the parser will not understand the new syntax and you will get
  an error.  Otherwise, databases for 2.2.x and 2.3.x are interchangeable.</td>
</tr>
<tr>
  <td valign="top">2.3.3 to 2.4.0</td>
  <td valign="top">2002-Mar-?</td>
  <td>Beginning with version 2.4.0, SQLite added support for views. 
  Information about views is stored in the SQLITE_MASTER table.  If an older
  version of SQLite attempts to read a database that contains VIEW information
  in the SQLITE_MASTER table, the parser will not understand the new syntax
  and you will get an error.  Also, the
  way SQLite keeps track of unused disk blocks in the database file
  changed slightly.
  If an older version of SQLite attempts to write a database that
  was previously written by version 2.4.0 or later, then it may leak disk
  blocks.</td>
</tr>
</table>
</blockquote>

<p>
To perform a database reload, have ready versions of the
<b>sqlite</b> command-line utility for both the old and new
︙︙
Changes to www/lang.tcl. 
1
2
3
4
5
6
7
8
9
10
11

#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: lang.tcl,v 1.26 2002/02/19 22:42:06 drh Exp $}

puts {<html>
<head>
  <title>Query Language Understood By SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>




|








1
2
3
4
5
6
7
8
9
10
11

#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: lang.tcl,v 1.27 2002/03/04 02:26:17 drh Exp $}

puts {<html>
<head>
  <title>Query Language Understood By SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>
︙︙
48
49
50
51
52
53
54


55
56
57
58
59
60
61

  {SELECT select}
  {COPY copy}
  {EXPLAIN explain}
  {expression expr}
  {{BEGIN TRANSACTION} transaction}
  {PRAGMA pragma}
  {{ON CONFLICT clause} conflict}


}] {
  puts "<li><a href=\"#[lindex $section 1]\">[lindex $section 0]</a></li>"
}
puts {</ul></p>

<p>Details on the implementation of each command are provided in
the sequel.</p>








>
>








48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63

  {SELECT select}
  {COPY copy}
  {EXPLAIN explain}
  {expression expr}
  {{BEGIN TRANSACTION} transaction}
  {PRAGMA pragma}
  {{ON CONFLICT clause} conflict}
  {{CREATE VIEW} createview}
  {{DROP VIEW} dropview}
}] {
  puts "<li><a href=\"#[lindex $section 1]\">[lindex $section 0]</a></li>"
}
puts {</ul></p>

<p>Details on the implementation of each command are provided in
the sequel.</p>
︙︙
337
338
339
340
341
342
343















344
345
346
347
348
349
350

are read from the <b>sqlite_master</b> table and used to regenerate
SQLite's internal representation of the table layout.
If the original command was a CREATE TABLE AS then then an equivalent
CREATE TABLE statement is synthesized and store in <b>sqlite_master</b>
in place of the original command.
</p>
}
















Section DELETE delete

Syntax {sql-statement} {
DELETE FROM <table-name> [WHERE <expression>]
}









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








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

are read from the <b>sqlite_master</b> table and used to regenerate
SQLite's internal representation of the table layout.
If the original command was a CREATE TABLE AS then then an equivalent
CREATE TABLE statement is synthesized and store in <b>sqlite_master</b>
in place of the original command.
</p>
}

Section {CREATE VIEW} {createview}

Syntax {sql-command} {
CREATE VIEW <view-name> AS <select-statement>
}

puts {
<p>The CREATE VIEW command assigns a name to a pre-packaged SELECT
statement.  Once the view is created, it can be used in the FROM clause
of another SELECT in place of a table name.
</p>

<p>You cannot COPY, INSERT or UPDATE a view.  Views are read-only.</p>
}

Section DELETE delete

Syntax {sql-statement} {
DELETE FROM <table-name> [WHERE <expression>]
}
︙︙
381
382
383
384
385
386
387











388
389
390
391
392
393
394


puts {
<p>The DROP TABLE statement consists of the keywords "DROP TABLE" followed
by the name of the table.  The table named is completely removed from
the disk.  The table can not be recovered.  All indices associated with
the table are also deleted.</p>}












Section EXPLAIN explain

Syntax {sql-statement} {
EXPLAIN <sql-statement>
}

puts {








>
>
>
>
>
>
>
>
>
>
>








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


puts {
<p>The DROP TABLE statement consists of the keywords "DROP TABLE" followed
by the name of the table.  The table named is completely removed from
the disk.  The table can not be recovered.  All indices associated with
the table are also deleted.</p>}

Section {DROP VIEW} dropview

Syntax {sql-command} {
DROP VIEW <view-name>
}

puts {
<p>The DROP VIEW statement consists of the keywords "DROP TABLE" followed
by the name of the view.  The view named is removed from the database.
But no actual data is modified.</p>}

Section EXPLAIN explain

Syntax {sql-statement} {
EXPLAIN <sql-statement>
}

puts {
︙︙
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431

<table-name> . <column-name> |
<literal-value> |
<function-name> ( <expr-list> | STAR ) |
<expression> ISNULL |
<expression> NOTNULL |
<expression> [NOT] BETWEEN <expression> AND <expression> |
<expression> [NOT] IN ( <value-list> ) |
<expression> [NOT] IN ( <select> ) |
( <select> )
} {like-op} {
LIKE | GLOB | NOT LIKE | NOT GLOB
}

puts {
<p>This section is different from the others.  Most other sections of
this document talks about a particular SQL command.  This section does








|
|








444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459

<table-name> . <column-name> |
<literal-value> |
<function-name> ( <expr-list> | STAR ) |
<expression> ISNULL |
<expression> NOTNULL |
<expression> [NOT] BETWEEN <expression> AND <expression> |
<expression> [NOT] IN ( <value-list> ) |
<expression> [NOT] IN ( <select-statement> ) |
( <select-statement> )
} {like-op} {
LIKE | GLOB | NOT LIKE | NOT GLOB
}

puts {
<p>This section is different from the others.  Most other sections of
this document talks about a particular SQL command.  This section does
︙︙
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


<p>When a SELECT appears within an expression but is not the right
operand of an IN operator, then the first row of the result of the
SELECT becomes the value used in the expression.  If the SELECT yields
more than one result row, all rows after the first are ignored.  If
the SELECT yeilds no rows, then the value of the SELECT is NULL.</p>


<p>The expression syntax currently supports the following
functions:</p>




<blockquote><pre>





<font color="#2c2cf0"><big>count    min       max       sum





avg      length    substr    abs       round</big></font>





</pre></blockquote>

<p>






The functions <b>count</b>, <b>sum</b>, and <b>avg</b> and the functions






<b>min</b> and <b>max</b> used with only one argument are all aggregate

functions.  This means that they are computed across all rows of the result.




The functions <b>min</b> and <b>max</b>
with two or more arguments and all other functions


are non-aggregates.  Non-aggregate functions
are computed separately for each row of the result.




</p>



























<p>


The <b>round</b> function can take either 1 or 2 arguments.  The





first argument is the floating point value that is rounded.  The


second argument is the number of digits to the right of the
decimal point to preserve.  If the second argument is omitted,


zero is assumed.  So round(1.23456,2) is 1.23 and
round(12.34,0) and round(12.34) both evaluate to 12.




</p>

<p>




The "<b>count(*)</b>" syntax is supported but
"<b>count(distinct</b> <i>COLUMN-NAME</i><b>)</b>" is not.



</p>

}

Section INSERT insert

Syntax {sql-statement} {
INSERT [OR <conflict-algorithm>] INTO <table-name> [(<column-list>)] VALUES(<value-list>) |
INSERT [OR <conflict-algorithm>] INTO <table-name> [(<column-list>)] <select-statement>








>
|
|
>
>

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

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

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

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

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








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


<p>When a SELECT appears within an expression but is not the right
operand of an IN operator, then the first row of the result of the
SELECT becomes the value used in the expression.  If the SELECT yields
more than one result row, all rows after the first are ignored.  If
the SELECT yeilds no rows, then the value of the SELECT is NULL.</p>

<p>Both simple and aggregate functions are supported.  A simple
function can be used in any expression.  Simple functions return
a result immediately based on their inputs.  Aggregate functions
may only be used in a SELECT statement.  Aggregate functions compute
their result across all rows of the result set.</p>

<p>The following simple functions are currently supported:</p>

<table border=0 cellpadding=10>
<tr>
<td valign="top" align="right" width=120>abs(<i>X</i>)</td>
<td valign="top">Return the absolute value of argument <i>X</i>.</td>
</tr>

<tr>
<td valign="top" align="right">coelasce(<i>X</i>,<i>Y</i>,...)</td>
<td valign="top">Return a copy of the first non-NULL argument.  If
all arguments are NULL then NULL is returned.</td>
</tr>

<tr>
<td valign="top" align="right">length(<i>X</i>)</td>
<td valign="top">Return the string length of <i>X</i> in characters.
If SQLite is configured to support UTF-8, then the number of UTF-8
characters is returned, not the number of bytes.</td>
</tr>

<tr>
<td valign="top" align="right">lower(<i>X</i>)</td>
<td valign="top">Return a copy of string <i>X</i> will all characters
converted to lower case.  The C library <b>tolower()</b> routine is used
for the conversion, which means that this function might not
work correctly on UTF-8 characters.</td>
</tr>

<tr>
<td valign="top" align="right">max(<i>X</i>,<i>Y</i>,...)</td>
<td valign="top">Return the argument with the maximum value.  Arguments
may be strings in addition to numbers.  The maximum value is determined
by the usual sort order.  Note that <b>max()</b> is a simple function when
it has 2 or more arguments but converts to an aggregate function if given
only a single argument.</td>
</tr>

<tr>
<td valign="top" align="right">min(<i>X</i>,<i>Y</i>,...)</td>
<td valign="top">Return the argument with the minimum value.  Arguments
may be strings in addition to numbers.  The mminimum value is determined
by the usual sort order.  Note that <b>min()</b> is a simple function when
it has 2 or more arguments but converts to an aggregate function if given
only a single argument.</td>
</tr>


<tr>
<td valign="top" align="right">random(*)</td>
<td valign="top">Return a random integer between -2147483648 and
+2147483647.</td>
</tr>

<tr>
<td valign="top" align="right">round(<i>X</i>)<br>round(<i>X</i>,<i>Y</i>)</td>
<td valign="top">Round off the number <i>X</i> to <i>Y</i> digits to the
right of the decimal point.  If the <i>Y</i> argument is omitted, 0 is 
assumed.</td>
</tr>

<tr>
<td valign="top" align="right">subst(<i>X</i>,<i>Y</i>,<i>Z</i>)</td>
<td valign="top">Return a substring of input string <i>X</i> that begins
with the <i>Y</i>-th character and which is <i>Z</i> characters long.
The left-most character of <i>X</i> is number 1.  If <i>Y</i> is negative
the the first character of the substring is found by counting from the
right rather than the left.  If SQLite is configured to support UTF-8,
then characters indices refer to actual UTF-8 characters, not bytes.</td>
</tr>

<tr>
<td valign="top" align="right">upper(<i>X</i>)</td>
<td valign="top">Return a copy of input string <i>X</i> converted to all
upper-case letters.  The implementation of this function uses the C library
routine <b>toupper()</b> which means it may not work correctly on 
UTF-8 strings.</td>
</tr>
</table>

<p>
The following aggregate functions are supported:
</p>

<table border=0 cellpadding=10>
<tr>
<td valign="top" align="right" width=120>avg(<i>X</i>)</td>
<td valign="top">Return the average value of all <i>X</i> within a group.</td>
</tr>

<tr>
<td valign="top" align="right">count(<i>X</i>)<br>count(*)</td>
<td valign="top">The first form return a count of the number of times
that <i>X</i> is not NULL in a group.  The second form (with no argument)
returns the total number of rows in the group.</td>
</tr>


<tr>
<td valign="top" align="right">max(<i>X</i>)</td>
<td valign="top">Return the maximum value of all values in the group.
The usual sort order is used to determine the maximum.</td>
</tr>

<tr>
<td valign="top" align="right">min(<i>X</i>)</td>
<td valign="top">Return the minimum value of all values in the group.
The usual sort order is used to determine the minimum.</td>
</tr>


<tr>
<td valign="top" align="right">sum(<i>X</i>)</td>
<td valign="top">Return the numeric sum of all values in the group.</td>
</tr>
</table>
}

Section INSERT insert

Syntax {sql-statement} {
INSERT [OR <conflict-algorithm>] INTO <table-name> [(<column-list>)] VALUES(<value-list>) |
INSERT [OR <conflict-algorithm>] INTO <table-name> [(<column-list>)] <select-statement>
︙︙