SQLite

Check-in [9fb29f7331]
Login

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

Overview
Comment:Documentation updates in preparation for the release of version 3.0.0. (CVS 1613)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 9fb29f7331254b601b71f0fe7d77f91915272dc7
User & Date: drh 2004-06-17 19:04:17.000
Context
2004-06-18
04:24
Optimisation for unicode encoding conversion routines. (CVS 1614) (check-in: 39a415eaa6 user: danielk1977 tags: trunk)
2004-06-17
19:04
Documentation updates in preparation for the release of version 3.0.0. (CVS 1613) (check-in: 9fb29f7331 user: drh tags: trunk)
07:53
Remove redundant opcodes OP_MakeKey and OP_MakeIdxKey. (CVS 1612) (check-in: a71a9ff114 user: danielk1977 tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to main.mk.
454
455
456
457
458
459
460



461
462
463
464
465
466
467

tclsqlite.html:	$(TOP)/www/tclsqlite.tcl
	tclsh $(TOP)/www/tclsqlite.tcl >tclsqlite.html

vdbe.html:	$(TOP)/www/vdbe.tcl
	tclsh $(TOP)/www/vdbe.tcl >vdbe.html





# Files to be published on the website.
#
DOC = \
  arch.html \
  arch.png \
  c_interface.html \







>
>
>







454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470

tclsqlite.html:	$(TOP)/www/tclsqlite.tcl
	tclsh $(TOP)/www/tclsqlite.tcl >tclsqlite.html

vdbe.html:	$(TOP)/www/vdbe.tcl
	tclsh $(TOP)/www/vdbe.tcl >vdbe.html

version3.html:	$(TOP)/www/version3.tcl
	tclsh $(TOP)/www/version3.tcl >version3.html


# Files to be published on the website.
#
DOC = \
  arch.html \
  arch.png \
  c_interface.html \
489
490
491
492
493
494
495
496

497
498
499
500
501
502
503
  opcode.html \
  quickstart.html \
  speed.html \
  sqlite.gif \
  sqlite.html \
  support.html \
  tclsqlite.html \
  vdbe.html 


doc:	common.tcl $(DOC)
	mkdir -p doc
	mv $(DOC) doc

# Standard install and cleanup targets
#







|
>







492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
  opcode.html \
  quickstart.html \
  speed.html \
  sqlite.gif \
  sqlite.html \
  support.html \
  tclsqlite.html \
  vdbe.html \
  version3.html

doc:	common.tcl $(DOC)
	mkdir -p doc
	mv $(DOC) doc

# Standard install and cleanup targets
#
Changes to src/os_unix.c.
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
** SQLite to access the file will not know that the journal exists (because
** the directory entry for the journal was never created) and the transaction
** will not roll back - possibly leading to database corruption.
*/
int sqlite3OsSync(OsFile *id){
  SimulateIOError(SQLITE_IOERR);
  TRACE2("SYNC    %-3d\n", id->h);





  if( fsync(id->h) ){
    return SQLITE_IOERR;
  }else{

    if( id->dirfd>=0 ){
      TRACE2("DIRSYNC %-3d\n", id->dirfd);
      fsync(id->dirfd);
      close(id->dirfd);  /* Only need to sync once, so close the directory */
      id->dirfd = -1;    /* when we are done. */
    }

    return SQLITE_OK;
  }
}

/*
** Sync the directory zDirname. This is a no-op on operating systems other
** than UNIX.
*/
int sqlite3OsSyncDirectory(const char *zDirname){







>
>
>
>
>


<
>
|
|
|
|
|
|
>
|
<







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
** SQLite to access the file will not know that the journal exists (because
** the directory entry for the journal was never created) and the transaction
** will not roll back - possibly leading to database corruption.
*/
int sqlite3OsSync(OsFile *id){
  SimulateIOError(SQLITE_IOERR);
  TRACE2("SYNC    %-3d\n", id->h);
{
off_t sz;
sqlite3OsFileSize(id, &sz);
fprintf(stderr,"SYNC %d size=%lld...  ", id->h, sz);
}
  if( fsync(id->h) ){
    return SQLITE_IOERR;

  }
  if( id->dirfd>=0 ){
    TRACE2("DIRSYNC %-3d\n", id->dirfd);
    fsync(id->dirfd);
    close(id->dirfd);  /* Only need to sync once, so close the directory */
    id->dirfd = -1;    /* when we are done. */
  }
fprintf(stderr,"DONE\n");
  return SQLITE_OK;

}

/*
** Sync the directory zDirname. This is a no-op on operating systems other
** than UNIX.
*/
int sqlite3OsSyncDirectory(const char *zDirname){
Changes to src/pragma.c.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
/*
** 2003 April 6
**
** The author disclaims copyright to this source code.  In place of
** a legal notice, here is a blessing:
**
**    May you do good and not evil.
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains code used to implement the PRAGMA command.
**
** $Id: pragma.c,v 1.45 2004/06/16 12:02:43 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

#ifdef SQLITE_DEBUG
# include "pager.h"
# include "btree.h"













|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
/*
** 2003 April 6
**
** The author disclaims copyright to this source code.  In place of
** a legal notice, here is a blessing:
**
**    May you do good and not evil.
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains code used to implement the PRAGMA command.
**
** $Id: pragma.c,v 1.46 2004/06/17 19:04:17 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

#ifdef SQLITE_DEBUG
# include "pager.h"
# include "btree.h"
120
121
122
123
124
125
126

127
128
129
130

131
132
133
134
135
136
137
  static const struct {
    const char *zName;  /* Name of the pragma */
    int mask;           /* Mask for the db->flags value */
  } aPragma[] = {
    { "vdbe_trace",               SQLITE_VdbeTrace     },
    { "sql_trace",                SQLITE_SqlTrace      },
    { "vdbe_listing",             SQLITE_VdbeListing   },

    { "full_column_names",        SQLITE_FullColNames  },
    { "short_column_names",       SQLITE_ShortColNames },
    { "count_changes",            SQLITE_CountRows     },
    { "empty_result_callbacks",   SQLITE_NullCallback  },

  };
  int i;
  for(i=0; i<sizeof(aPragma)/sizeof(aPragma[0]); i++){
    if( sqlite3StrICmp(zLeft, aPragma[i].zName)==0 ){
      sqlite *db = pParse->db;
      Vdbe *v;
      if( strcmp(zLeft,zRight)==0 && (v = sqlite3GetVdbe(pParse))!=0 ){







>




>







120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
  static const struct {
    const char *zName;  /* Name of the pragma */
    int mask;           /* Mask for the db->flags value */
  } aPragma[] = {
    { "vdbe_trace",               SQLITE_VdbeTrace     },
    { "sql_trace",                SQLITE_SqlTrace      },
    { "vdbe_listing",             SQLITE_VdbeListing   },
#if 1  /* FIX ME:  Remove the following pragmas */
    { "full_column_names",        SQLITE_FullColNames  },
    { "short_column_names",       SQLITE_ShortColNames },
    { "count_changes",            SQLITE_CountRows     },
    { "empty_result_callbacks",   SQLITE_NullCallback  },
#endif
  };
  int i;
  for(i=0; i<sizeof(aPragma)/sizeof(aPragma[0]); i++){
    if( sqlite3StrICmp(zLeft, aPragma[i].zName)==0 ){
      sqlite *db = pParse->db;
      Vdbe *v;
      if( strcmp(zLeft,zRight)==0 && (v = sqlite3GetVdbe(pParse))!=0 ){
Changes to src/sqlite.h.in.
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This header file defines the interface that the SQLite library
** presents to client programs.
**
** @(#) $Id: sqlite.h.in,v 1.101 2004/06/12 09:25:20 danielk1977 Exp $
*/
#ifndef _SQLITE_H_
#define _SQLITE_H_
#include <stdarg.h>     /* Needed for the definition of va_list */

/*
** Make sure we can call this stuff from C++.







|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This header file defines the interface that the SQLite library
** presents to client programs.
**
** @(#) $Id: sqlite.h.in,v 1.102 2004/06/17 19:04:17 drh Exp $
*/
#ifndef _SQLITE_H_
#define _SQLITE_H_
#include <stdarg.h>     /* Needed for the definition of va_list */

/*
** Make sure we can call this stuff from C++.
946
947
948
949
950
951
952










953
954
955
956
957
958
959
** In practice, meta-data is preserved between function calls for
** expressions that are constant at compile time. This includes literal
** values and SQL variables.
*/
void *sqlite3_get_auxdata(sqlite3_context*, int);
void sqlite3_set_auxdata(sqlite3_context*, int, void*, void (*)(void*));











#define SQLITE_STATIC      ((void(*)(void *))0)
#define SQLITE_TRANSIENT   ((void(*)(void *))-1)

/*
** User-defined functions invoke the following routines in order to
** set their return value.
*/







>
>
>
>
>
>
>
>
>
>







946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
** In practice, meta-data is preserved between function calls for
** expressions that are constant at compile time. This includes literal
** values and SQL variables.
*/
void *sqlite3_get_auxdata(sqlite3_context*, int);
void sqlite3_set_auxdata(sqlite3_context*, int, void*, void (*)(void*));


/*
** These are special value for the destructor that is passed in as the
** final argument to routines like sqlite3_result_blob().  If the destructor
** argument is SQLITE_STATIC, it means that the content pointer is constant
** and will never change.  It does not need to be destroyed.  The 
** SQLITE_TRANSIENT value means that the content will likely change in
** the near future and that SQLite should make its own private copy of
** the content before returning.
*/
#define SQLITE_STATIC      ((void(*)(void *))0)
#define SQLITE_TRANSIENT   ((void(*)(void *))-1)

/*
** User-defined functions invoke the following routines in order to
** set their return value.
*/
Changes to www/capi3.tcl.
1
2
3
4
5
6
7
8
set rcsid {$Id: capi3.tcl,v 1.3 2004/06/01 10:01:25 drh Exp $}
source common.tcl
header {C/C++ Interface For SQLite Version 3}
puts {
<h2>C/C++ Interface For SQLite Version 3</h2>

<h3>1.0 Overview</h3>

|







1
2
3
4
5
6
7
8
set rcsid {$Id: capi3.tcl,v 1.4 2004/06/17 19:04:17 drh Exp $}
source common.tcl
header {C/C++ Interface For SQLite Version 3}
puts {
<h2>C/C++ Interface For SQLite Version 3</h2>

<h3>1.0 Overview</h3>

23
24
25
26
27
28
29

30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49

























50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65

66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
<p>
It became necessary to move to version 3.0
to implement these features because each
requires incompatible changes to the database file format.  Other
incompatible changes, such as a cleanup of the API, were introduced
at the same time under the theory that it is best to get your
incompatible changes out of the way all at once.


<p>
The API for version 3.0 is similar to the version 2.X API,
but with some important changes.  Most noticeably, the "<tt>sqlite_</tt>"
prefix that occurs on the beginning of all API functions and data
structures will be changed to "<tt>sqlite3_</tt>".  
This will avoid confusion between the two APIs and allow
linking against both SQLite 2.X and SQLite 3.0 at the same time,
if desired.
</p>

<p>
There is no agreement on what the C datatype for a UTF-16
string should be.  Therefore, SQLite uses a generic type of void*
to refer to UTF-16 strings.
Client software can cast the void*
to whatever datatype is appropriate for their system.
</p>

<h3>2.0 C/C++ Interface</h3>


























<h4>2.1 Opening and closing a database</h4>

<blockquote><pre>
   typedef struct sqlite3 sqlite3;
   int sqlite3_open(const char*, sqlite3**, const char**);
   int sqlite3_open16(const void*, sqlite3**, const char**);
   int sqlite3_close(sqlite3*);
   const char *sqlite3_errmsg(sqlite3*);
   const void *sqlite3_errmsg16(sqlite3*);
   int sqlite3_errcode(sqlite3*);
</pre></blockquote>

<p>
The sqlite3_open() routine returns an integer error code rather than
a pointer to the sqlite3 structure.  The difference between sqlite3_open()

and sqlite3_open16() is that sqlite3_open16() takes UTF-16 (in host native
byte order) for the name of the database file.  If a new database file
needs to be created, then sqlite3_open16() will set the internal text
representation to UTF-16 whereas sqlite3_open() will set the text
representation to UTF-8.
</p>

<p>
The third "const char**" argument to sqlite3_open() is a NULL-terminated
list of keyword/value pairs that define options to apply to the open
request.  The third argument may be NULL if there are no options.
This extra argument provides an expandable way of supporting new features
in future releases.  For example, a future release may contain an
option to define an encryption/decryption key.
</p>

<p>
The sqlite3_errcode() routine will return the result code for the most
recent major API call.  sqlite3_errmsg() will return an English-language
text error message for the most recent error.  The error message will
be represented in UTF-8 and will be ephemeral - it could disappear on







>




















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





|
|








|
>








|
|
|
|
<
<







23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104


105
106
107
108
109
110
111
<p>
It became necessary to move to version 3.0
to implement these features because each
requires incompatible changes to the database file format.  Other
incompatible changes, such as a cleanup of the API, were introduced
at the same time under the theory that it is best to get your
incompatible changes out of the way all at once.
</p>

<p>
The API for version 3.0 is similar to the version 2.X API,
but with some important changes.  Most noticeably, the "<tt>sqlite_</tt>"
prefix that occurs on the beginning of all API functions and data
structures will be changed to "<tt>sqlite3_</tt>".  
This will avoid confusion between the two APIs and allow
linking against both SQLite 2.X and SQLite 3.0 at the same time,
if desired.
</p>

<p>
There is no agreement on what the C datatype for a UTF-16
string should be.  Therefore, SQLite uses a generic type of void*
to refer to UTF-16 strings.
Client software can cast the void*
to whatever datatype is appropriate for their system.
</p>

<h3>2.0 C/C++ Interface</h3>

<p>
The API for SQLite 3.0 three includes 83 separate functions in addition
to several data structures and #defines.  (A complete
<a href="capi3ref.html">API reference</a> is provided as a separate document.)
Fortunately, the interface is not nearly as complex as its size implies.
Simple programs can still make due with only 3 functions:
<a href="capi3ref.html#sqlite3_open">sqlite3_open()</a>,
<a href="capi3ref.html#sqlite3_exec">sqlite3_exec()</a>, and
<a href="capi3ref.html#sqlite3_close">sqlite3_close()</a>.
More control over the execution of the database engine is provided
using
<a href="capi3ref.html#sqlite3_prepare">sqlite3_prepare()</a>
to compile an SQLite statement into byte code and
<a href="capi3ref.html#sqlite3_prepare">sqlite3_step()</a>
to execute that bytecode.
A family of routines with names beginning with 
<a href="capi3ref.html#sqlite3_column_blob">sqlite3_column_</a>
is used to extract informatiom about the result set of a query.
Many interface functions come in pairs, with both a UTF-8 and
and UTF-16 version.  And there is a collection of routines
used to implement user-defined SQL functions and user-defined
text collating sequences.
</p>


<h4>2.1 Opening and closing a database</h4>

<blockquote><pre>
   typedef struct sqlite3 sqlite3;
   int sqlite3_open(const char*, sqlite3**);
   int sqlite3_open16(const void*, sqlite3**);
   int sqlite3_close(sqlite3*);
   const char *sqlite3_errmsg(sqlite3*);
   const void *sqlite3_errmsg16(sqlite3*);
   int sqlite3_errcode(sqlite3*);
</pre></blockquote>

<p>
The sqlite3_open() routine returns an integer error code rather than
a pointer to the sqlite3 structure as the version 2 interface did.
The difference between sqlite3_open()
and sqlite3_open16() is that sqlite3_open16() takes UTF-16 (in host native
byte order) for the name of the database file.  If a new database file
needs to be created, then sqlite3_open16() will set the internal text
representation to UTF-16 whereas sqlite3_open() will set the text
representation to UTF-8.
</p>

<p>
The opening and/or creating of the database file is deferred until the
file is actually needed.  This allows options and parameters, such
as the native text representation and default page size, to be
set using PRAGMA statements.


</p>

<p>
The sqlite3_errcode() routine will return the result code for the most
recent major API call.  sqlite3_errmsg() will return an English-language
text error message for the most recent error.  The error message will
be represented in UTF-8 and will be ephemeral - it could disappear on
120
121
122
123
124
125
126


















127
128
129
130
131
132
133
134
135
136


137

138









139


140
141
142
143
144
145
146

147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
#define SQLITE_NOLFS       22   /* Uses OS features not supported on host */
#define SQLITE_AUTH        23   /* Authorization denied */
#define SQLITE_ROW         100  /* sqlite_step() has another row ready */
#define SQLITE_DONE        101  /* sqlite_step() has finished executing */
</pre></blockquote>

<h4>2.2 Executing SQL statements</h4>



















<blockquote><pre>
   typedef struct sqlite3_stmt sqlite3_stmt;
   int sqlite3_prepare(sqlite3*, const char*, sqlite3_stmt**, const char**);
   int sqlite3_prepare16(sqlite3*, const void*, sqlite3_stmt**, const void**);
   int sqlite3_finalize(sqlite3_stmt*);
   int sqlite3_reset(sqlite3_stmt*);
</pre></blockquote>

<p>


The non-callback API is now the preferred way of accessing the database.

Wrapper functions that emulate the older callback API may (or may not)









be provided.


</p>

<p>
The sqlite3_prepare() function compiles an single SQL statement.
The statement may contain tokens of the form "?" or "?nnn" or ":nnn:"
where "nnn" is an integer.  Such tokens represent unspecified literal values
(or wildcard) to be filled in later by the sqlite3_bind() API.

Each wildcard as an associated number given
by the "nnn" that follows the "?".  If the "?" is not followed by an
integer, then its number one more than the number of prior wildcards
in the same SQL statement.  It is allowed for the same wildcard
to occur more than once in the same SQL statement, in which case
all instance of that wildcard will be filled in with the same value.
Unbound wildcards have a value of NULL.
</p>


<p>The SQL statement is a UTF-8 string for sqlite3_prepare().
The sqlite3_prepare16() works the same way except
that it expects a UTF-16 string as SQL input.
Only the first SQL statement in the input string is compiled.
The fourth parameter is filled in with a pointer to the next (uncompiled)
SQLite statement in the input string, if any.
The sqlite3_finalize() routine deallocates a prepared SQL statement.
The sqlite3_reset() routine resets a prepared SQL statement so that it
can be executed again.
</p>

<blockquote><pre>
   int sqlite3_bind_blob(sqlite3_stmt*, int, const void*, int n, int eCopy);
   int sqlite3_bind_double(sqlite3_stmt*, int, double);
   int sqlite3_bind_int(sqlite3_stmt*, int, int);
   int sqlite3_bind_int64(sqlite3_stmt*, int, long long int);
   int sqlite3_bind_null(sqlite3_stmt*, int);
   int sqlite3_bind_text(sqlite3_stmt*, int, const char*, int n, int eCopy);
   int sqlite3_bind_text16(sqlite3_stmt*, int, const void*, int n, int eCopy);
   int sqlite3_bind_value(sqlite3_stmt*, int, const sqlite3_value*);
</pre></blockquote>

<p>
There is an assortment of sqlite3_bind routines used to assign values
to wildcards in a prepared SQL statement.  Unbound wildcards
are interpreted as NULLs.  Bindings are not reset by sqlite3_reset().







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



|
|





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



<
|

|
>









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

|




|
|







145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199

200
201
202
203
204
205
206
207
208
209
210
211
212












213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
#define SQLITE_NOLFS       22   /* Uses OS features not supported on host */
#define SQLITE_AUTH        23   /* Authorization denied */
#define SQLITE_ROW         100  /* sqlite_step() has another row ready */
#define SQLITE_DONE        101  /* sqlite_step() has finished executing */
</pre></blockquote>

<h4>2.2 Executing SQL statements</h4>

<blockquote><pre>
   typedef int (*sqlite_callback)(void*,int,char**, char**);
   int sqlite3_exec(sqlite3*, const char *sql, sqlite_callback, void*, char**);
</pre></blockquote>

<p>
The sqlite3_exec function works much as it did in SQLite version 2.
Zero or more SQL statements specified in the second parameter are compiled
and executed.  Query results are returned to a callback routine.
See the <a href="capi3ref.html#sqlite3_exec">API reference</a> for additional
information.
</p>

<p>
In SQLite version 3, the sqlite3_exec routine is just a wrapper around
calls to the prepared statement interface.
</p>

<blockquote><pre>
   typedef struct sqlite3_stmt sqlite3_stmt;
   int sqlite3_prepare(sqlite3*, const char*, int, sqlite3_stmt**, const char**);
   int sqlite3_prepare16(sqlite3*, const void*, int, sqlite3_stmt**, const void**);
   int sqlite3_finalize(sqlite3_stmt*);
   int sqlite3_reset(sqlite3_stmt*);
</pre></blockquote>

<p>
The sqlite3_prepare interface compiles a single SQL statement into byte code
for later execution.
This interface is now the preferred way of accessing the database.
</p>

<p>
The SQL statement is a UTF-8 string for sqlite3_prepare().
The sqlite3_prepare16() works the same way except
that it expects a UTF-16 string as SQL input.
Only the first SQL statement in the input string is compiled.
The fourth parameter is filled in with a pointer to the next (uncompiled)
SQLite statement in the input string, if any.
The sqlite3_finalize() routine deallocates a prepared SQL statement.
All prepared statements must be finalized before the database can be
closed.
The sqlite3_reset() routine resets a prepared SQL statement so that it
can be executed again.
</p>

<p>

The SQL statement may contain tokens of the form "?" or "?nnn" or ":nnn:"
where "nnn" is an integer.  Such tokens represent unspecified literal values
(or wildcards) to be filled in later by the 
<a href="capi3ref.html#sqlite3_bind_blob">sqlite3_bind</a> interface.
Each wildcard as an associated number given
by the "nnn" that follows the "?".  If the "?" is not followed by an
integer, then its number one more than the number of prior wildcards
in the same SQL statement.  It is allowed for the same wildcard
to occur more than once in the same SQL statement, in which case
all instance of that wildcard will be filled in with the same value.
Unbound wildcards have a value of NULL.
</p>













<blockquote><pre>
   int sqlite3_bind_blob(sqlite3_stmt*, int, const void*, int n, void(*)(void*));
   int sqlite3_bind_double(sqlite3_stmt*, int, double);
   int sqlite3_bind_int(sqlite3_stmt*, int, int);
   int sqlite3_bind_int64(sqlite3_stmt*, int, long long int);
   int sqlite3_bind_null(sqlite3_stmt*, int);
   int sqlite3_bind_text(sqlite3_stmt*, int, const char*, int n, void(*)(void*));
   int sqlite3_bind_text16(sqlite3_stmt*, int, const void*, int n, void(*)(void*));
   int sqlite3_bind_value(sqlite3_stmt*, int, const sqlite3_value*);
</pre></blockquote>

<p>
There is an assortment of sqlite3_bind routines used to assign values
to wildcards in a prepared SQL statement.  Unbound wildcards
are interpreted as NULLs.  Bindings are not reset by sqlite3_reset().
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213



214
215
216


217
218
219
220
221
222


223











224
225
226
227
228
229
230
231
normally or due to an error.  It might also return SQLITE3_BUSY if it is
unable to open the database file.  If the return value is SQLITE3_ROW, then
the following routines can be used to extract information about that row
of the result set:
</p>

<blockquote><pre>
   int sqlite3_column_count(sqlite3_stmt*);
   int sqlite3_column_type(sqlite3_stmt*,int);
   const char *sqlite3_column_decltype(sqlite3_stmt *, int i);
   const char *sqlite3_column_decltype16(sqlite3_stmt *, int i);
   const char *sqlite3_column_name(sqlite3_stmt*,int);
   const void *sqlite3_column_name16(sqlite3_stmt*,int);
   const void *sqlite3_column_blob(sqlite3_stmt*, int iCol);
   int sqlite3_column_bytes(sqlite3_stmt*, int iCol);
   int sqlite3_column_bytes16(sqlite3_stmt*, int iCol);



   double sqlite3_column_double(sqlite3_stmt*, int iCol);
   int sqlite3_column_int(sqlite3_stmt*, int iCol);
   long long int sqlite3_column_int64(sqlite3_stmt*, int iCol);


   const unsigned char *sqlite3_column_text(sqlite3_stmt*, int iCol);
   const void *sqlite3_column_text16(sqlite3_stmt*, int iCol);
   int sqlite3_column_type(sqlite3_stmt*, int iCol);
</pre></blockquote>

<p>


The sqlite3_column_count() function returns the number of columns in











the results set.   The sqlite3_column_type() function returns the
datatype for the value in the Nth column.  The return value is one
of these:
</p>

<blockquote><pre>
   #define SQLITE_INTEGER  1
   #define SQLITE_FLOAT    2







<
<
<
<
<
<



>
>
>



>
>






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







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
normally or due to an error.  It might also return SQLITE3_BUSY if it is
unable to open the database file.  If the return value is SQLITE3_ROW, then
the following routines can be used to extract information about that row
of the result set:
</p>

<blockquote><pre>






   const void *sqlite3_column_blob(sqlite3_stmt*, int iCol);
   int sqlite3_column_bytes(sqlite3_stmt*, int iCol);
   int sqlite3_column_bytes16(sqlite3_stmt*, int iCol);
   int sqlite3_column_count(sqlite3_stmt*);
   const char *sqlite3_column_decltype(sqlite3_stmt *, int iCol);
   const void *sqlite3_column_decltype16(sqlite3_stmt *, int iCol);
   double sqlite3_column_double(sqlite3_stmt*, int iCol);
   int sqlite3_column_int(sqlite3_stmt*, int iCol);
   long long int sqlite3_column_int64(sqlite3_stmt*, int iCol);
   const char *sqlite3_column_name(sqlite3_stmt*, int iCol);
   const void *sqlite3_column_name16(sqlite3_stmt*, int iCol);
   const unsigned char *sqlite3_column_text(sqlite3_stmt*, int iCol);
   const void *sqlite3_column_text16(sqlite3_stmt*, int iCol);
   int sqlite3_column_type(sqlite3_stmt*, int iCol);
</pre></blockquote>

<p>
The 
<a href="capi3ref.html#sqlite3_column_count">sqlite3_column_count()</a>
function returns the number of columns in
the results set.  sqlite3_column_count() can be called at any time after
sqlite3_prepare().  
<a href="capi3ref.html#sqlite3_data_count">sqlite3_data_count()</a>
works similarly to
sqlite3_column_count() except that it only works following sqlite3_step().
If the previous call to sqlite3_step() returned SQLITE_DONE or an error code,
then sqlite3_data_count() will return 0 whereas sqlite3_column_count() will
continue to return the number of columns in the result set.
</p>

<p>
The sqlite3_column_type() function returns the
datatype for the value in the Nth column.  The return value is one
of these:
</p>

<blockquote><pre>
   #define SQLITE_INTEGER  1
   #define SQLITE_FLOAT    2
248
249
250
251
252
253
254






255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287

288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
sqlite3_column_text16() return TEXT data as UTF-16.
sqlite3_column_int() return INTEGER data in the host machines native
integer format.
sqlite3_column_int64() returns 64-bit INTEGER data.
Finally, sqlite3_column_double() return floating point data.
</p>







<h4>2.3 User-defined functions</h4>

<p>
User defined functions can be created using the following routine:
</p>

<blockquote><pre>
   typedef struct sqlite3_value sqlite3_value;
   int sqlite3_create_function(
     sqlite3 *,
     const char *zFunctionName,
     int nArg,
     int eTextRep,
     int iCollateArg,
     void*,
     void (*xFunc)(sqlite3_context*,int,sqlite3_value**),
     void (*xStep)(sqlite3_context*,int,sqlite3_value**),
     void (*xFinal)(sqlite3_context*)
   );
   int sqlite3_create_function16(
     sqlite3*,
     const void *zFunctionName,
     int nArg,
     int eTextRep,
     int iCollateArg,
     void*,
     void (*xFunc)(sqlite3_context*,int,sqlite3_value**),
     void (*xStep)(sqlite3_context*,int,sqlite3_value**),
     void (*xFinal)(sqlite3_context*)
   );
   #define SQLITE3_UTF8     1
   #define SQLITE3_UTF16LE  2
   #define SQLITE3_UTF16BE  3

   #define SQLITE3_ANY      4
</pre></blockquote>

<p>
The nArg parameter specifies the number of arguments to the function.
A value of 0 indicates that any number of arguments is allowed.  The
eTextRep parameter specifies what representation text values are expected
to be in for arguments to this function.  The value of this parameter should
be one of the parameters defined above.  SQLite version 3 allows multiple
implementations of the same function using different text representations.
The database engine chooses the function that minimization the number
of text conversions required.
The iCollateArg parameter indicates that the collating sequence for the
result is to be the same as the collating sequence of the iCollateArg-th
parameter.
</p>

<p>
Normal functions specify only xFunc and leave xStep and xFinal set to NULL.
Aggregate functions specify xStep and xFinal and leave xFunc set to NULL.
There is no separate sqlite3_create_aggregate() API.
</p>







>
>
>
>
>
>













<










<






|

>
|











<
<
<







305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330

331
332
333
334
335
336
337
338
339
340

341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361



362
363
364
365
366
367
368
sqlite3_column_text16() return TEXT data as UTF-16.
sqlite3_column_int() return INTEGER data in the host machines native
integer format.
sqlite3_column_int64() returns 64-bit INTEGER data.
Finally, sqlite3_column_double() return floating point data.
</p>

<p>
It is not necessary to retrieve data in the format specify by
sqlite3_column_type().  If a different format is requested, the data
is converted automatically.
</p>

<h4>2.3 User-defined functions</h4>

<p>
User defined functions can be created using the following routine:
</p>

<blockquote><pre>
   typedef struct sqlite3_value sqlite3_value;
   int sqlite3_create_function(
     sqlite3 *,
     const char *zFunctionName,
     int nArg,
     int eTextRep,

     void*,
     void (*xFunc)(sqlite3_context*,int,sqlite3_value**),
     void (*xStep)(sqlite3_context*,int,sqlite3_value**),
     void (*xFinal)(sqlite3_context*)
   );
   int sqlite3_create_function16(
     sqlite3*,
     const void *zFunctionName,
     int nArg,
     int eTextRep,

     void*,
     void (*xFunc)(sqlite3_context*,int,sqlite3_value**),
     void (*xStep)(sqlite3_context*,int,sqlite3_value**),
     void (*xFinal)(sqlite3_context*)
   );
   #define SQLITE3_UTF8     1
   #define SQLITE3_UTF16    2
   #define SQLITE3_UTF16BE  3
   #define SQLITE3_UTF16LE  4
   #define SQLITE3_ANY      5
</pre></blockquote>

<p>
The nArg parameter specifies the number of arguments to the function.
A value of 0 indicates that any number of arguments is allowed.  The
eTextRep parameter specifies what representation text values are expected
to be in for arguments to this function.  The value of this parameter should
be one of the parameters defined above.  SQLite version 3 allows multiple
implementations of the same function using different text representations.
The database engine chooses the function that minimization the number
of text conversions required.



</p>

<p>
Normal functions specify only xFunc and leave xStep and xFinal set to NULL.
Aggregate functions specify xStep and xFinal and leave xFunc set to NULL.
There is no separate sqlite3_create_aggregate() API.
</p>
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
Function implementations use the following APIs to acquire context and
to report results:
</p>

<blockquote><pre>
   void *sqlite3_aggregate_context(sqlite3_context*, int nbyte);
   void *sqlite3_user_data(sqlite3_context*);
   void sqlite3_result_blob(sqlite3_context*, const void*, int n, int eCopy);
   void sqlite3_result_double(sqlite3_context*, double);
   void sqlite3_result_error(sqlite3_context*, const char*, int);
   void sqlite3_result_error16(sqlite3_context*, const void*, int);
   void sqlite3_result_int(sqlite3_context*, int);
   void sqlite3_result_int64(sqlite3_context*, long long int);
   void sqlite3_result_null(sqlite3_context*);
   void sqlite3_result_text(sqlite3_context*, const char*, int n, int eCopy);
   void sqlite3_result_text16(sqlite3_context*, const void*, int n, int eCopy);
   void sqlite3_result_value(sqlite3_context*, sqlite3_value*);
   void *sqlite3_get_auxdata(sqlite3_context*, int);
   void sqlite3_set_auxdata(sqlite3_context*, int, void*, void (*)(void*));
</pre></blockquote>

<h4>2.4 User-defined collating sequences</h4>








|






|
|







396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
Function implementations use the following APIs to acquire context and
to report results:
</p>

<blockquote><pre>
   void *sqlite3_aggregate_context(sqlite3_context*, int nbyte);
   void *sqlite3_user_data(sqlite3_context*);
   void sqlite3_result_blob(sqlite3_context*, const void*, int n, void(*)(void*));
   void sqlite3_result_double(sqlite3_context*, double);
   void sqlite3_result_error(sqlite3_context*, const char*, int);
   void sqlite3_result_error16(sqlite3_context*, const void*, int);
   void sqlite3_result_int(sqlite3_context*, int);
   void sqlite3_result_int64(sqlite3_context*, long long int);
   void sqlite3_result_null(sqlite3_context*);
   void sqlite3_result_text(sqlite3_context*, const char*, int n, void(*)(void*));
   void sqlite3_result_text16(sqlite3_context*, const void*, int n, void(*)(void*));
   void sqlite3_result_value(sqlite3_context*, sqlite3_value*);
   void *sqlite3_get_auxdata(sqlite3_context*, int);
   void sqlite3_set_auxdata(sqlite3_context*, int, void*, void (*)(void*));
</pre></blockquote>

<h4>2.4 User-defined collating sequences</h4>

Changes to www/capi3ref.tcl.
1
2
3
4
5
6
7
8
set rcsid {$Id: capi3ref.tcl,v 1.3 2004/06/12 00:42:36 danielk1977 Exp $}
source common.tcl
header {C/C++ Interface For SQLite Version 3}
puts {
<h2>C/C++ Interface For SQLite Version 3</h2>
}

proc api {name prototype desc {notused x}} {
|







1
2
3
4
5
6
7
8
set rcsid {$Id: capi3ref.tcl,v 1.4 2004/06/17 19:04:17 drh Exp $}
source common.tcl
header {C/C++ Interface For SQLite Version 3}
puts {
<h2>C/C++ Interface For SQLite Version 3</h2>
}

proc api {name prototype desc {notused x}} {
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
#define SQLITE_MISMATCH    20   /* Data type mismatch */
#define SQLITE_MISUSE      21   /* Library used incorrectly */
#define SQLITE_NOLFS       22   /* Uses OS features not supported on host */
#define SQLITE_AUTH        23   /* Authorization denied */
#define SQLITE_ROW         100  /* sqlite_step() has another row ready */
#define SQLITE_DONE        101  /* sqlite_step() has finished executing */
} {
The sqlite3.h header file defines macros for the integer result codes
returned by many API functions.
}

api {} {
  void *sqlite3_aggregate_context(sqlite3_context*, int nBytes);
} {
  Aggregate functions use the following routine to allocate
  a structure for storing their state.  The first time this routine







|
|







39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
#define SQLITE_MISMATCH    20   /* Data type mismatch */
#define SQLITE_MISUSE      21   /* Library used incorrectly */
#define SQLITE_NOLFS       22   /* Uses OS features not supported on host */
#define SQLITE_AUTH        23   /* Authorization denied */
#define SQLITE_ROW         100  /* sqlite_step() has another row ready */
#define SQLITE_DONE        101  /* sqlite_step() has finished executing */
} {
Many SQLite functions return an integer result code from the set shown
above in order to indicates success or failure.
}

api {} {
  void *sqlite3_aggregate_context(sqlite3_context*, int nBytes);
} {
  Aggregate functions use the following routine to allocate
  a structure for storing their state.  The first time this routine
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
  int sqlite3_bind_blob(sqlite3_stmt*, int, const void*, int n, void(*)(void*));
  int sqlite3_bind_double(sqlite3_stmt*, int, double);
  int sqlite3_bind_int(sqlite3_stmt*, int, int);
  int sqlite3_bind_int64(sqlite3_stmt*, int, long long int);
  int sqlite3_bind_null(sqlite3_stmt*, int);
  int sqlite3_bind_text(sqlite3_stmt*, int, const char*, int n, void(*)(void*));
  int sqlite3_bind_text16(sqlite3_stmt*, int, const void*, int n, void(*)(void*));
  #define SQLITE_STATIC      ((void*)0)
  #define SQLITE_EPHEMERAL   ((void*)8)
} {
 In the SQL strings input to sqlite3_prepare() and sqlite3_prepare16(),
 one or more literals can be replace by a wildcard "?" or ":N:" where
 N is an integer.  The value of these wildcard literals can be set
 using these routines.

 The first parameter is a pointer to the sqlite3_stmt
 structure returned from sqlite3_prepare().  The second parameter is the
 index of the wildcard.  The first "?" has an index of 1.  ":N:" wildcards
 use the index N.

 The fifth parameter to sqlite3_bind_blob(), sqlite3_bind_text(), and
 sqlite3_bind_text16() is a destructor used to dispose of the BLOB or
 text after SQLite has finished with it.  If the fifth argument is the
 special value SQLITE_STATIC, then the library assumes that the information
 is in static, unmanaged space and does not need to be freed.  If the
 fifth argument has the value SQLITE_EPHEMERAL, then SQLite makes its
 on private copy of the data.

 The sqlite3_bind_*() routine must be called after
 sqlite3_prepare() or sqlite3_reset() and before sqlite3_step().
 Bindings are not reset by the sqlite3_reset() routine.
 Unbound wildcards are interpreted as NULL.
}

api {} {
  void sqlite3_busy_handler(sqlite*, int(*)(void*,int), void*);
} {
 This routine identifies a callback function that is invoked
 whenever an attempt is made to open a database table that is
 currently locked by another process or thread.  If the busy callback
 is NULL, then sqlite3_exec() returns SQLITE_BUSY immediately if
 it finds a locked table.  If the busy callback is not NULL, then
 sqlite3_exec() invokes the callback with two arguments.  The







|
|
















|









|







72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
  int sqlite3_bind_blob(sqlite3_stmt*, int, const void*, int n, void(*)(void*));
  int sqlite3_bind_double(sqlite3_stmt*, int, double);
  int sqlite3_bind_int(sqlite3_stmt*, int, int);
  int sqlite3_bind_int64(sqlite3_stmt*, int, long long int);
  int sqlite3_bind_null(sqlite3_stmt*, int);
  int sqlite3_bind_text(sqlite3_stmt*, int, const char*, int n, void(*)(void*));
  int sqlite3_bind_text16(sqlite3_stmt*, int, const void*, int n, void(*)(void*));
  #define SQLITE_STATIC      ((void(*)(void *))0)
  #define SQLITE_TRANSIENT   ((void(*)(void *))-1)
} {
 In the SQL strings input to sqlite3_prepare() and sqlite3_prepare16(),
 one or more literals can be replace by a wildcard "?" or ":N:" where
 N is an integer.  The value of these wildcard literals can be set
 using these routines.

 The first parameter is a pointer to the sqlite3_stmt
 structure returned from sqlite3_prepare().  The second parameter is the
 index of the wildcard.  The first "?" has an index of 1.  ":N:" wildcards
 use the index N.

 The fifth parameter to sqlite3_bind_blob(), sqlite3_bind_text(), and
 sqlite3_bind_text16() is a destructor used to dispose of the BLOB or
 text after SQLite has finished with it.  If the fifth argument is the
 special value SQLITE_STATIC, then the library assumes that the information
 is in static, unmanaged space and does not need to be freed.  If the
 fifth argument has the value SQLITE_TRANSIENT, then SQLite makes its
 on private copy of the data.

 The sqlite3_bind_*() routine must be called after
 sqlite3_prepare() or sqlite3_reset() and before sqlite3_step().
 Bindings are not reset by the sqlite3_reset() routine.
 Unbound wildcards are interpreted as NULL.
}

api {} {
  int sqlite3_busy_handler(sqlite*, int(*)(void*,int), void*);
} {
 This routine identifies a callback function that is invoked
 whenever an attempt is made to open a database table that is
 currently locked by another process or thread.  If the busy callback
 is NULL, then sqlite3_exec() returns SQLITE_BUSY immediately if
 it finds a locked table.  If the busy callback is not NULL, then
 sqlite3_exec() invokes the callback with two arguments.  The
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
 is allowed, in theory.)  But the busy handler may not close the
 database.  Closing the database from a busy handler will delete 
 data structures out from under the executing query and will 
 probably result in a coredump.
}

api {} {
  void sqlite3_busy_timeout(sqlite*, int ms);
} {
 This routine sets a busy handler that sleeps for a while when a
 table is locked.  The handler will sleep multiple times until 
 at least "ms" milleseconds of sleeping have been done.  After
 "ms" milleseconds of sleeping, the handler returns 0 which
 causes sqlite3_exec() to return SQLITE_BUSY.

 Calling this routine with an argument less than or equal to zero
 turns off all busy handlers.
}

api {} {
int sqlite3_changes(sqlite*);
} {
 This function returns the number of database rows that were changed
 (or inserted or deleted) by the most recent called sqlite3_exec().

 All changes are counted, even if they were later undone by a
 ROLLBACK or ABORT.  Except, changes associated with creating and
 dropping tables are not counted.


 If a callback invokes sqlite3_exec() recursively, then the changes
 in the inner, recursive call are counted together with the changes

 in the outer call.

 SQLite implements the command "DELETE FROM table" without a WHERE clause
 by dropping and recreating the table.  (This is much faster than going
 through and deleting individual elements form the table.)  Because of
 this optimization, the change count for "DELETE FROM table" will be
 zero regardless of the number of elements that were originally in the
 table. To get an accurate count of the number of rows deleted, use
 "DELETE FROM table WHERE 1" instead.
}

api {} {






















  void sqlite3_close(sqlite *);
} {
 Call this function with a pointer to a structure that was previously
 returned from sqlite3_open() or sqlite3_open16()
 and the corresponding database will by closed.






}

api {} {
const void *sqlite3_column_blob(sqlite3_stmt*, int iCol);
int sqlite3_column_bytes(sqlite3_stmt*, int iCol);
int sqlite3_column_bytes16(sqlite3_stmt*, int iCol);
double sqlite3_column_double(sqlite3_stmt*, int iCol);







|












|


|
|
|
|
|
>

|
<
>
|











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

|
|
|
>
>
>
>
>
>







125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155

156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
 is allowed, in theory.)  But the busy handler may not close the
 database.  Closing the database from a busy handler will delete 
 data structures out from under the executing query and will 
 probably result in a coredump.
}

api {} {
  int sqlite3_busy_timeout(sqlite*, int ms);
} {
 This routine sets a busy handler that sleeps for a while when a
 table is locked.  The handler will sleep multiple times until 
 at least "ms" milleseconds of sleeping have been done.  After
 "ms" milleseconds of sleeping, the handler returns 0 which
 causes sqlite3_exec() to return SQLITE_BUSY.

 Calling this routine with an argument less than or equal to zero
 turns off all busy handlers.
}

api {} {
  int sqlite3_changes(sqlite*);
} {
 This function returns the number of database rows that were changed
 (or inserted or deleted) by the most recently completed
 INSERT, UPDATE, or DELETE
 statement.  Only changes that are directly specified by the INSERT,
 UPDATE, or DELETE statement are counted.  Auxiliary changes caused by
 triggers are not counted.  Use the sqlite3_total_changes() function
 to find the total number of changes including changes caused by triggers.

 Within the body of a trigger, the sqlite3_changes() function does work

 to report the number of rows that were changed for the most recently
 completed INSERT, UPDATE, or DELETE statement within the trigger body.

 SQLite implements the command "DELETE FROM table" without a WHERE clause
 by dropping and recreating the table.  (This is much faster than going
 through and deleting individual elements form the table.)  Because of
 this optimization, the change count for "DELETE FROM table" will be
 zero regardless of the number of elements that were originally in the
 table. To get an accurate count of the number of rows deleted, use
 "DELETE FROM table WHERE 1" instead.
}

api {} {
  int sqlite3_total_changes(sqlite*);
} {
  This function returns the total number of database rows that have
  be modified, inserted, or deleted since the database connection was
  created using sqlite3_open().  All changes are counted, including
  changes by triggers and changes to TEMP and auxiliary databases.
  Except, changes to the SQLITE_MASTER table (caused by statements 
  such as CREATE TABLE) are not counted.  Nor are changes counted when
  an entire table is deleted using DROP TABLE.

  See also the sqlite3_changes() API.

  SQLite implements the command "DELETE FROM table" without a WHERE clause
  by dropping and recreating the table.  (This is much faster than going
  through and deleting individual elements form the table.)  Because of
  this optimization, the change count for "DELETE FROM table" will be
  zero regardless of the number of elements that were originally in the
  table. To get an accurate count of the number of rows deleted, use
  "DELETE FROM table WHERE 1" instead.
}

api {} {
  int sqlite3_close(sqlite *);
} {
  Call this function with a pointer to a structure that was previously
  returned from sqlite3_open() or sqlite3_open16()
  and the corresponding database will by closed.

  SQLITE_OK is returned if the close is successful.  If there are
  prepared statements that have not been finalized, then SQLITE_BUSY
  is returned.  SQLITE_ERROR might be returned if the argument is not
  a valid connection pointer returned by sqlite3_open() or if the connection
  pointer has been closed previously.
}

api {} {
const void *sqlite3_column_blob(sqlite3_stmt*, int iCol);
int sqlite3_column_bytes(sqlite3_stmt*, int iCol);
int sqlite3_column_bytes16(sqlite3_stmt*, int iCol);
double sqlite3_column_double(sqlite3_stmt*, int iCol);
225
226
227
228
229
230
231
232
233
234


235
236
237
238
239
240
241
242
243
244
245
246
247
248
<tr><td> BLOB </td><td>    TEXT </td><td>  Add a \000 terminator if needed</td></tr>
</table>
}

api {} {
int sqlite3_column_count(sqlite3_stmt *pStmt);
} {
 Return the number of columns in the result set returned by the compiled
 SQL statement. This routine returns 0 if pStmt is an SQL statement
 that does not return data (for example an UPDATE).


}

api {} {
const char *sqlite3_column_decltype(sqlite3_stmt *, int i);
const void *sqlite3_column_decltype16(sqlite3_stmt*,int);
} {
 The first parameter is a compiled SQL statement. If this statement
 is a SELECT statement, the Nth column of the returned result set 
 of the SELECT is a table column then the declared type of the table
 column is returned. If the Nth column of the result set is not at table
 column, then a NULL pointer is returned. The returned string is 
 UTF-8 encoded for sqlite3_column_decltype() and UTF-16 encoded
 for sqlite3_column_decltype16().
 For example, in the database schema:







|


>
>






|







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
<tr><td> BLOB </td><td>    TEXT </td><td>  Add a \000 terminator if needed</td></tr>
</table>
}

api {} {
int sqlite3_column_count(sqlite3_stmt *pStmt);
} {
 Return the number of columns in the result set returned by the prepared
 SQL statement. This routine returns 0 if pStmt is an SQL statement
 that does not return data (for example an UPDATE).

 See also sqlite3_data_count().
}

api {} {
const char *sqlite3_column_decltype(sqlite3_stmt *, int i);
const void *sqlite3_column_decltype16(sqlite3_stmt*,int);
} {
 The first parameter is a prepared SQL statement. If this statement
 is a SELECT statement, the Nth column of the returned result set 
 of the SELECT is a table column then the declared type of the table
 column is returned. If the Nth column of the result set is not at table
 column, then a NULL pointer is returned. The returned string is 
 UTF-8 encoded for sqlite3_column_decltype() and UTF-16 encoded
 for sqlite3_column_decltype16().
 For example, in the database schema:
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291

292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
 (i==0).
}

api {} {
const char *sqlite3_column_name(sqlite3_stmt*,int);
const void *sqlite3_column_name16(sqlite3_stmt*,int);
} {
 The first parameter is a compiled SQL statement. This function returns
 the column heading for the Nth column of that statement, where N is the
 second function parameter.  The string returned is UTF-8 for
 sqlite3_column_name() and UTF-16 for sqlite3_column_name16().
}

api {} {
void *sqlite3_commit_hook(sqlite*, int(*)(void*), void*);
} {
 This routine configures a callback function - the progress callback - that
 is invoked periodically during long running calls to sqlite3_exec(),
 sqlite3_step() and sqlite3_get_table().
 An example use for this API is to keep
 a GUI updated during a large query.

 The progress callback is invoked once for every N virtual machine opcodes,
 where N is the second argument to this function. The progress callback
 itself is identified by the third argument to this function. The fourth
 argument to this function is a void pointer passed to the progress callback
 function each time it is invoked.

 If a call to sqlite3_exec(), sqlite3_step() or sqlite3_get_table() results 
 in less than N opcodes being executed, then the progress callback is not

 invoked.
 
 To remove the progress callback altogether, pass NULL as the third
 argument to this function.

 If the progress callback returns a result other than 0, then the current 
 query is immediately terminated and any database changes rolled back. If the
 query was part of a larger transaction, then the transaction is not rolled
 back and remains active. The sqlite3_exec() call returns SQLITE_ABORT. 
}

api {} {
int sqlite3_complete(const char *sql);
int sqlite3_complete16(const void *sql);
} {
 These functions return true if the given input string comprises







|






|

|
<
<
<
<

|
|
|
|
<

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







293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309




310
311
312
313
314

315


316
317
318


319
320



321
322
323
324
325
326
327
 (i==0).
}

api {} {
const char *sqlite3_column_name(sqlite3_stmt*,int);
const void *sqlite3_column_name16(sqlite3_stmt*,int);
} {
 The first parameter is a prepared SQL statement. This function returns
 the column heading for the Nth column of that statement, where N is the
 second function parameter.  The string returned is UTF-8 for
 sqlite3_column_name() and UTF-16 for sqlite3_column_name16().
}

api {} {
void *sqlite3_commit_hook(sqlite*, int(*xCallback)(void*), void *pArg);
} {
 <i>Experimental</i>





 Register a callback function to be invoked whenever a new transaction
 is committed.  The pArg argument is passed through to the callback.
 callback.  If the callback function returns non-zero, then the commit
 is converted into a rollback.




 If another function was previously registered, its pArg value is returned.
 Otherwise NULL is returned.



 Registering a NULL function disables the callback.  Only a single commit
 hook callback can be registered at a time.



}

api {} {
int sqlite3_complete(const char *sql);
int sqlite3_complete16(const void *sql);
} {
 These functions return true if the given input string comprises
341
342
343
344
345
346
347
348


349
350
351
352
353
354
355
 for sqlite3_create_collation() and a UTF-16 string for
 sqlite3_create_collation16(). In both cases the name is passed as the
 second function argument.

 The third argument must be one of the constants SQLITE_UTF8,
 SQLITE_UTF16LE or SQLITE_UTF16BE, indicating that the user-supplied
 routine expects to be passed pointers to strings encoded using UTF-8,
 UTF-16 little-endian or UTF-16 big-endian respectively.



 A pointer to the user supplied routine must be passed as the fifth
 argument. If it is NULL, this is the same as deleting the collation
 sequence (so that SQLite cannot call it anymore). Each time the user
 supplied function is invoked, it is passed a copy of the void* passed as
 the fourth argument to sqlite3_create_collation() or
 sqlite3_create_collation16() as its first parameter.







|
>
>







361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
 for sqlite3_create_collation() and a UTF-16 string for
 sqlite3_create_collation16(). In both cases the name is passed as the
 second function argument.

 The third argument must be one of the constants SQLITE_UTF8,
 SQLITE_UTF16LE or SQLITE_UTF16BE, indicating that the user-supplied
 routine expects to be passed pointers to strings encoded using UTF-8,
 UTF-16 little-endian or UTF-16 big-endian respectively.  The
 SQLITE_UTF16 constant indicates that text strings are expected in
 UTF-16 in the native byte order of the host machine.

 A pointer to the user supplied routine must be passed as the fifth
 argument. If it is NULL, this is the same as deleting the collation
 sequence (so that SQLite cannot call it anymore). Each time the user
 supplied function is invoked, it is passed a copy of the void* passed as
 the fourth argument to sqlite3_create_collation() or
 sqlite3_create_collation16() as its first parameter.
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425

api {} {
int sqlite3_create_function(
  sqlite3 *,
  const char *zFunctionName,
  int nArg,
  int eTextRep,
  int iCollateArg,
  void*,
  void (*xFunc)(sqlite3_context*,int,sqlite3_value**),
  void (*xStep)(sqlite3_context*,int,sqlite3_value**),
  void (*xFinal)(sqlite3_context*)
);
int sqlite3_create_function16(
  sqlite3*,
  const void *zFunctionName,
  int nArg,
  int eTextRep,
  int iCollateArg,
  void*,
  void (*xFunc)(sqlite3_context*,int,sqlite3_value**),
  void (*xStep)(sqlite3_context*,int,sqlite3_value**),
  void (*xFinal)(sqlite3_context*)
);
#define SQLITE_UTF8     1
#define SQLITE_UTF16    2







<










<







422
423
424
425
426
427
428

429
430
431
432
433
434
435
436
437
438

439
440
441
442
443
444
445

api {} {
int sqlite3_create_function(
  sqlite3 *,
  const char *zFunctionName,
  int nArg,
  int eTextRep,

  void*,
  void (*xFunc)(sqlite3_context*,int,sqlite3_value**),
  void (*xStep)(sqlite3_context*,int,sqlite3_value**),
  void (*xFinal)(sqlite3_context*)
);
int sqlite3_create_function16(
  sqlite3*,
  const void *zFunctionName,
  int nArg,
  int eTextRep,

  void*,
  void (*xFunc)(sqlite3_context*,int,sqlite3_value**),
  void (*xStep)(sqlite3_context*,int,sqlite3_value**),
  void (*xFinal)(sqlite3_context*)
);
#define SQLITE_UTF8     1
#define SQLITE_UTF16    2
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
 be added individually to each database handle with which they will be
 used.

 The third parameter is the number of arguments that the function or
 aggregate takes. If this parameter is negative, then the function or
 aggregate may take any number of arguments.

 The seventh, eighth and ninth parameters, xFunc, xStep and xFinal, are
 pointers to user implemented C functions that implement the user
 function or aggregate. A scalar function requires an implementation of
 the xFunc callback only, NULL pointers should be passed as the xStep
 and xFinal parameters. An aggregate function requires an implementation
 of xStep and xFinal, but NULL should be passed for xFunc. To delete an
 existing user function or aggregate, pass NULL for all three function
 callback. Specifying an inconstent set of callback values, such as an
 xFunc and an xFinal, or an xStep but no xFinal, SQLITE_ERROR is
 returned.
}

api {} {
int sqlite3_data_count(sqlite3_stmt *pStmt);
} {
 Return the number of values in the current row of the result set.

 After a call to sqlite3_step() that returns SQLITE_ROW, this routine
 will return the same value as the sqlite3_column_count() function.
 After sqlite3_step() has returned an SQLITE_DONE, SQLITE_BUSY or
 error code, or before sqlite3_step() has been called on a 
 compiled SQL statement, this routine returns zero.
}

api {} {
int sqlite3_errcode(sqlite3 *db);
} {
 Return the error code for the most recent sqlite3_* API call associated
 with sqlite3 handle 'db'. SQLITE_OK is returned if the most recent 







|




















|







459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
 be added individually to each database handle with which they will be
 used.

 The third parameter is the number of arguments that the function or
 aggregate takes. If this parameter is negative, then the function or
 aggregate may take any number of arguments.

 The sixth, seventh and  eighth, xFunc, xStep and xFinal, are
 pointers to user implemented C functions that implement the user
 function or aggregate. A scalar function requires an implementation of
 the xFunc callback only, NULL pointers should be passed as the xStep
 and xFinal parameters. An aggregate function requires an implementation
 of xStep and xFinal, but NULL should be passed for xFunc. To delete an
 existing user function or aggregate, pass NULL for all three function
 callback. Specifying an inconstent set of callback values, such as an
 xFunc and an xFinal, or an xStep but no xFinal, SQLITE_ERROR is
 returned.
}

api {} {
int sqlite3_data_count(sqlite3_stmt *pStmt);
} {
 Return the number of values in the current row of the result set.

 After a call to sqlite3_step() that returns SQLITE_ROW, this routine
 will return the same value as the sqlite3_column_count() function.
 After sqlite3_step() has returned an SQLITE_DONE, SQLITE_BUSY or
 error code, or before sqlite3_step() has been called on a 
 prepared SQL statement, this routine returns zero.
}

api {} {
int sqlite3_errcode(sqlite3 *db);
} {
 Return the error code for the most recent sqlite3_* API call associated
 with sqlite3 handle 'db'. SQLITE_OK is returned if the most recent 
546
547
548
549
550
551
552
553
554
555
556
557
558



559
560
561
562
563
564
565
 behavior can be modified somewhat using the sqlite3_busy_handler()
 and sqlite3_busy_timeout() functions.)
} {}

api {} {
int sqlite3_finalize(sqlite3_stmt *pStmt);
} {
 The sqlite3_finalize() function is called to delete a compiled
 SQL statement obtained by a previous call to sqlite3_prepare()
 or sqlite3_prepare16(). If the statement was executed successfully, or
 not executed at all, then SQLITE_OK is returned. If execution of the
 statement failed then an error code is returned. 




 This routine can be called at any point during the execution of the
 virtual machine.  If the virtual machine has not completed execution
 when this routine is called, that is like encountering an error or
 an interrupt.  (See sqlite3_interrupt().)  Incomplete updates may be
 rolled back and transactions cancelled,  depending on the circumstances,
 and the result code returned will be SQLITE_ABORT.
}







|





>
>
>







566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
 behavior can be modified somewhat using the sqlite3_busy_handler()
 and sqlite3_busy_timeout() functions.)
} {}

api {} {
int sqlite3_finalize(sqlite3_stmt *pStmt);
} {
 The sqlite3_finalize() function is called to delete a prepared
 SQL statement obtained by a previous call to sqlite3_prepare()
 or sqlite3_prepare16(). If the statement was executed successfully, or
 not executed at all, then SQLITE_OK is returned. If execution of the
 statement failed then an error code is returned. 

 All prepared statements must finalized before sqlite3_close() is
 called or else the close will fail with a return code of SQLITE_BUSY.

 This routine can be called at any point during the execution of the
 virtual machine.  If the virtual machine has not completed execution
 when this routine is called, that is like encountering an error or
 an interrupt.  (See sqlite3_interrupt().)  Incomplete updates may be
 rolled back and transactions cancelled,  depending on the circumstances,
 and the result code returned will be SQLITE_ABORT.
}
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
 This function causes any pending database operation to abort and
 return at its earliest opportunity.  This routine is typically
 called in response to a user action such as pressing "Cancel"
 or Ctrl-C where the user wants a long query operation to halt
 immediately.
} {}

api {} {
int sqlite3_last_statement_changes(sqlite*);
} {
Experimental
}

api {} {
long long int sqlite3_last_insert_rowid(sqlite*);
} {
 Each entry in an SQLite table has a unique integer key.  (The key is
 the value of the INTEGER PRIMARY KEY column if there is such a column,
 otherwise the key is generated at random.  The unique key is always
 available as the ROWID, OID, or _ROWID_ column.)  The following routine







<
<
<
<
<
<







656
657
658
659
660
661
662






663
664
665
666
667
668
669
 This function causes any pending database operation to abort and
 return at its earliest opportunity.  This routine is typically
 called in response to a user action such as pressing "Cancel"
 or Ctrl-C where the user wants a long query operation to halt
 immediately.
} {}







api {} {
long long int sqlite3_last_insert_rowid(sqlite*);
} {
 Each entry in an SQLite table has a unique integer key.  (The key is
 the value of the INTEGER PRIMARY KEY column if there is such a column,
 otherwise the key is generated at random.  The unique key is always
 available as the ROWID, OID, or _ROWID_ column.)  The following routine
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
 back and remains active. The sqlite3_exec() call returns SQLITE_ABORT. 

}

api {} {
int sqlite3_reset(sqlite3_stmt *pStmt);
} {
 The sqlite3_reset() function is called to reset a compiled SQL
 statement obtained by a previous call to sqlite3_prepare() or
 sqlite3_prepare16() back to it's initial state, ready to be re-executed.
 Any SQL statement variables that had values bound to them using
 the sqlite3_bind_*() API retain their values.
}

api {} {







|







823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
 back and remains active. The sqlite3_exec() call returns SQLITE_ABORT. 

}

api {} {
int sqlite3_reset(sqlite3_stmt *pStmt);
} {
 The sqlite3_reset() function is called to reset a prepared SQL
 statement obtained by a previous call to sqlite3_prepare() or
 sqlite3_prepare16() back to it's initial state, ready to be re-executed.
 Any SQL statement variables that had values bound to them using
 the sqlite3_bind_*() API retain their values.
}

api {} {
830
831
832
833
834
835
836




837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
void sqlite3_result_text16be(sqlite3_context*, const void*, int n, void(*)(void*));
void sqlite3_result_text16le(sqlite3_context*, const void*, int n, void(*)(void*));
void sqlite3_result_value(sqlite3_context*, sqlite3_value*);
} {
 User-defined functions invoke the following routines in order to
 set their return value.  The sqlite3_result_value() routine is used
 to return an exact copy of one of the parameters to the function.




}

api {} {
int sqlite3_set_authorizer(
  sqlite*,
  int (*xAuth)(void*,int,const char*,const char*,const char*,const char*),
  void *pUserData
);
#define SQLITE_COPY                  0   /* Table Name      File Name       */
#define SQLITE_CREATE_INDEX          1   /* Index Name      Table Name      */
#define SQLITE_CREATE_TABLE          2   /* Table Name      NULL            */
#define SQLITE_CREATE_TEMP_INDEX     3   /* Index Name      Table Name      */
#define SQLITE_CREATE_TEMP_TABLE     4   /* Table Name      NULL            */
#define SQLITE_CREATE_TEMP_TRIGGER   5   /* Trigger Name    Table Name      */
#define SQLITE_CREATE_TEMP_VIEW      6   /* View Name       NULL            */
#define SQLITE_CREATE_TRIGGER        7   /* Trigger Name    Table Name      */







>
>
>
>








<







847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865

866
867
868
869
870
871
872
void sqlite3_result_text16be(sqlite3_context*, const void*, int n, void(*)(void*));
void sqlite3_result_text16le(sqlite3_context*, const void*, int n, void(*)(void*));
void sqlite3_result_value(sqlite3_context*, sqlite3_value*);
} {
 User-defined functions invoke the following routines in order to
 set their return value.  The sqlite3_result_value() routine is used
 to return an exact copy of one of the parameters to the function.

 The operation of these routines is very similar to the operation of
 sqlite3_bind_blob() and its cousins.  Refer to the documentation there
 for additional information.
}

api {} {
int sqlite3_set_authorizer(
  sqlite*,
  int (*xAuth)(void*,int,const char*,const char*,const char*,const char*),
  void *pUserData
);

#define SQLITE_CREATE_INDEX          1   /* Index Name      Table Name      */
#define SQLITE_CREATE_TABLE          2   /* Table Name      NULL            */
#define SQLITE_CREATE_TEMP_INDEX     3   /* Index Name      Table Name      */
#define SQLITE_CREATE_TEMP_TABLE     4   /* Table Name      NULL            */
#define SQLITE_CREATE_TEMP_TRIGGER   5   /* Trigger Name    Table Name      */
#define SQLITE_CREATE_TEMP_VIEW      6   /* View Name       NULL            */
#define SQLITE_CREATE_TRIGGER        7   /* Trigger Name    Table Name      */
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
 The return value of the authorization function should be one of the
 constants SQLITE_DENY or SQLITE_IGNORE.
}

api {} {
int sqlite3_step(sqlite3_stmt*);
} {
 After an SQL query has been compiled with a call to either
 sqlite3_prepare() or sqlite3_prepare16(), then this function must be
 called one or more times to execute the statement.

 The return value will be either SQLITE_BUSY, SQLITE_DONE, 
 SQLITE_ROW, SQLITE_ERROR, or SQLITE_MISUSE.

 SQLITE_BUSY means that the database engine attempted to open







|







912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
 The return value of the authorization function should be one of the
 constants SQLITE_DENY or SQLITE_IGNORE.
}

api {} {
int sqlite3_step(sqlite3_stmt*);
} {
 After an SQL query has been prepared with a call to either
 sqlite3_prepare() or sqlite3_prepare16(), then this function must be
 called one or more times to execute the statement.

 The return value will be either SQLITE_BUSY, SQLITE_DONE, 
 SQLITE_ROW, SQLITE_ERROR, or SQLITE_MISUSE.

 SQLITE_BUSY means that the database engine attempted to open
990
991
992
993
994
995
996

997
998
999
1000
1001
1002

1003
1004
1005
1006
1007
1008
1009
for {set j 0} {$j<3} {incr j} {
  if {$j>0} {puts {<td width="10"></td>}}
  puts {<td valign="top">}
  set limit [expr {$i+$nrow}]
  puts {<ul>}
  while {$i<$limit && $i<$n} {
    set name $sname($i)

    puts "<li><a href=\"#$name\">$name</a></li>"
    incr i
  }
  puts {</ul></td>}
}
puts "</table>"


proc resolve_name {ignore_list name} {
  global name_to_idx
  if {![info exists name_to_idx($name)] || [lsearch $ignore_list $name]>=0} {
    return $name
  } else {
    return "<a href=\"#$name\">$name</a>"







>
|





>







1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
for {set j 0} {$j<3} {incr j} {
  if {$j>0} {puts {<td width="10"></td>}}
  puts {<td valign="top">}
  set limit [expr {$i+$nrow}]
  puts {<ul>}
  while {$i<$limit && $i<$n} {
    set name $sname($i)
    if {[regexp {^sqlite} $name]} {set display $name} {set display <i>$name</i>}
    puts "<li><a href=\"#$name\">$display</a></li>"
    incr i
  }
  puts {</ul></td>}
}
puts "</table>"
puts "<!-- $n entries.  $nrow rows in 3 columns -->"

proc resolve_name {ignore_list name} {
  global name_to_idx
  if {![info exists name_to_idx($name)] || [lsearch $ignore_list $name]>=0} {
    return $name
  } else {
    return "<a href=\"#$name\">$name</a>"
Changes to www/datatype3.tcl.
1
2
3
4
5
6
7
8
set rcsid {$Id: datatype3.tcl,v 1.5 2004/06/10 10:51:53 danielk1977 Exp $}
source common.tcl
header {Datatypes In SQLite Version 3}
puts {
<h2>Datatypes In SQLite Version 3</h2>

<h3>1. Storage Classes</h3>

|







1
2
3
4
5
6
7
8
set rcsid {$Id: datatype3.tcl,v 1.6 2004/06/17 19:04:17 drh Exp $}
source common.tcl
header {Datatypes In SQLite Version 3}
puts {
<h2>Datatypes In SQLite Version 3</h2>

<h3>1. Storage Classes</h3>

305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
comparison is determined using memcmp(), regardless of the encoding of the
string. SQLite v3 provides the ability for users to supply arbitrary
comparison functions, known as user-defined collation sequences, to be used
instead of memcmp().
</p>  
<p>
Aside from the default collation sequence BINARY, implemented using
memcmp(), SQLite features two extra built-in collation sequences,
NOCASE and REVERSE:
</p>  
<UL>
	<LI><b>BINARY</b> - Compares string data using memcmp(), regardless
                            of text encoding.</LI>
	<LI><b>REVERSE</b> - Collate in the reverse order to BINARY. </LI>
	<LI><b>NOCASE</b> - The same as binary, except the 26 upper case
			    characters used by the English language are







|
|







305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
comparison is determined using memcmp(), regardless of the encoding of the
string. SQLite v3 provides the ability for users to supply arbitrary
comparison functions, known as user-defined collation sequences, to be used
instead of memcmp().
</p>  
<p>
Aside from the default collation sequence BINARY, implemented using
memcmp(), SQLite features two extra built-in collation sequences 
intended for testing purposes, NOCASE and REVERSE:
</p>  
<UL>
	<LI><b>BINARY</b> - Compares string data using memcmp(), regardless
                            of text encoding.</LI>
	<LI><b>REVERSE</b> - Collate in the reverse order to BINARY. </LI>
	<LI><b>NOCASE</b> - The same as binary, except the 26 upper case
			    characters used by the English language are
Changes to www/docs.tcl.
1
2
3
4
5
6
7
8
9
10
11
# This script generates the "docs.html" page that describes various
# sources of documentation available for SQLite.
#
set rcsid {$Id: docs.tcl,v 1.4 2004/06/11 17:48:04 drh Exp $}
source common.tcl
header {SQLite Documentation}
puts {
<h2>Available Documentation</h2>
<table width="100%" cellpadding="5">
}




|







1
2
3
4
5
6
7
8
9
10
11
# This script generates the "docs.html" page that describes various
# sources of documentation available for SQLite.
#
set rcsid {$Id: docs.tcl,v 1.5 2004/06/17 19:04:17 drh Exp $}
source common.tcl
header {SQLite Documentation}
puts {
<h2>Available Documentation</h2>
<table width="100%" cellpadding="5">
}

24
25
26
27
28
29
30



31
32
33
34
35
36
37
  SQLite.  
}

doc {Version 2 C/C++ API} {c_interface.html} {
  A description of the C/C++ interface bindings for SQLite through version 
  2.8
}



doc {Version 3 C/C++ API} {capi3.html} {
  A description of the C/C++ interface bindings for SQLite version 3.0.0
  and following.
}
doc {Version 3 C/C++ API<br>Reference} {capi3ref.html} {
  This document describes each API function separately.
}







>
>
>







24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
  SQLite.  
}

doc {Version 2 C/C++ API} {c_interface.html} {
  A description of the C/C++ interface bindings for SQLite through version 
  2.8
}
doc {SQLite Version 3} {version3.html} {
  A summary of of the changes between SQLite version 2.8 and SQLite version 3.0.
}
doc {Version 3 C/C++ API} {capi3.html} {
  A description of the C/C++ interface bindings for SQLite version 3.0.0
  and following.
}
doc {Version 3 C/C++ API<br>Reference} {capi3ref.html} {
  This document describes each API function separately.
}
Changes to www/index.tcl.
56
57
58
59
60
61
62
63
64
65
66
67
68
69

70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
  regsub -all "\n( *\n)+" $text "</p>\n\n<p>" txt
  puts "<p>$txt</p>"
  puts "<hr width=\"50%\">"
}

newsitem {2004-Jun-18} {Version 3.0.0 (alpha) Released} {
  The first alpha release of SQLite version 3.0 is available for
  public review and comment.  Version 3.0 enhances i18n support
  through the use of UTF-16 and user-defined text collating sequences.
  BLOBs can now be stored directly, without encoding.
  A new file format results in databases that are 25% smaller (depending
  on content).  The code is also a little faster.  In spite of the many
  new features, the library footprint is still less than 235KB
  (x86, gcc -O1).


  Our intent is to freeze the file format and API on 2004-Jul-01.
  Users are encouraged to review and evaluate this alpha release carefully 
  and submit any feedback prior to that date.

  The 2.8 series of SQLite will continue to be supported with bug
  fixes for the foreseeable future.
}

newsitem {2004-Jun-09} {Version 2.8.14 Released} {
  SQLite version 2.8.14 is a patch release to the stable 2.8 series.
  There is no reason to upgrade if 2.8.13 is working ok for you.
  This is only a bug-fix release.  Most developement effort is
  going into version 3.0.0 which is due out soon.
}

puts {
<p align="right"><a href="oldnews.html">Old news...</a></p>
</td></tr></table>
}
footer {$Id: index.tcl,v 1.87 2004/06/16 03:02:04 drh Exp $}







|




|

>




















|
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
  regsub -all "\n( *\n)+" $text "</p>\n\n<p>" txt
  puts "<p>$txt</p>"
  puts "<hr width=\"50%\">"
}

newsitem {2004-Jun-18} {Version 3.0.0 (alpha) Released} {
  The first alpha release of SQLite version 3.0 is available for
  public review and comment.  Version 3.0 enhances internationalization support
  through the use of UTF-16 and user-defined text collating sequences.
  BLOBs can now be stored directly, without encoding.
  A new file format results in databases that are 25% smaller (depending
  on content).  The code is also a little faster.  In spite of the many
  new features, the library footprint is still less than 240KB
  (x86, gcc -O1).
  <a href="version3.html">Additional information</a>.

  Our intent is to freeze the file format and API on 2004-Jul-01.
  Users are encouraged to review and evaluate this alpha release carefully 
  and submit any feedback prior to that date.

  The 2.8 series of SQLite will continue to be supported with bug
  fixes for the foreseeable future.
}

newsitem {2004-Jun-09} {Version 2.8.14 Released} {
  SQLite version 2.8.14 is a patch release to the stable 2.8 series.
  There is no reason to upgrade if 2.8.13 is working ok for you.
  This is only a bug-fix release.  Most developement effort is
  going into version 3.0.0 which is due out soon.
}

puts {
<p align="right"><a href="oldnews.html">Old news...</a></p>
</td></tr></table>
}
footer {$Id: index.tcl,v 1.88 2004/06/17 19:04:17 drh Exp $}
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.68 2004/05/31 15:06:30 drh Exp $}
source common.tcl
header {Query Language Understood by SQLite}
puts {
<h2>SQL As Understood By SQLite</h2>

<p>The SQLite library understands most of the standard SQL
language.  But it does <a href="omitted.html">omit some features</a>



|







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.69 2004/06/17 19:04:17 drh Exp $}
source common.tcl
header {Query Language Understood by SQLite}
puts {
<h2>SQL As Understood By SQLite</h2>

<p>The SQLite library understands most of the standard SQL
language.  But it does <a href="omitted.html">omit some features</a>
125
126
127
128
129
130
131
132
133

134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149

150


151
152
153
154
155




156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
file to the current database connection.  If the filename contains 
punctuation characters it must be quoted.  The names 'main' and 
'temp' refer to the main database and the database used for 
temporary tables.  These cannot be detached.  Attached databases 
are removed using the <a href="#detach">DETACH DATABASE</a> 
statement.</p>

<p>You can read from and write to an attached database, but you cannot 
alter the schema of an attached database.  You can only CREATE and 

DROP in the original database.</p>

<p>You cannot create a new table with the same name as a table in 
an attached database, but you can attach a database which contains
tables whose names are duplicates of tables in the main database.  It is 
also permissible to attach the same database file multiple times.</p>

<p>Tables in an attached database can be referred to using the syntax 
<i>database-name.table-name</i>.  If an attached table doesn't have 
a duplicate table name in the main database, it doesn't require a 
database name prefix.  When a database is attached, all of its 
tables which don't have duplicate names become the 'default' table
of that name.  Any tables of that name attached afterwards require the table 
prefix. If the 'default' table of a given name is detached, then 
the last table of that name attached becomes the new default.</p>


<p>When there are attached databases, transactions are not atomic. 


Transactions continue to be atomic within each individual
database file. But if your machine crashes in the middle
of a COMMIT where you have updated two or more database
files, some of those files might get the changes where others
might not.</p>





<p>There is a compile-time limit of 10 attached database files.</p>

<p>Executing a BEGIN TRANSACTION statement locks all database
files, so this feature cannot (currently) be used to increase 
concurrancy.</p>
}


Section {BEGIN TRANSACTION} transaction

Syntax {sql-statement} {
BEGIN [TRANSACTION [<name>]] [ON CONFLICT <conflict-algorithm>]
}
Syntax {sql-statement} {
END [TRANSACTION [<name>]]
}
Syntax {sql-statement} {
COMMIT [TRANSACTION [<name>]]
}
Syntax {sql-statement} {
ROLLBACK [TRANSACTION [<name>]]
}

puts {
<p>Beginning in version 2.0, SQLite supports transactions with
rollback and atomic commit.  See <a href="#attach">ATTACH</a> for
an exception when there are attached databases.</p>

<p>The optional transaction name is ignored. SQLite currently 
doesn't allow nested transactions.  Attempting to start a new 
transaction inside another is an error.</p>

<p>
No changes can be made to the database except within a transaction.
Any command that changes the database (basically, any SQL command
other than SELECT) will automatically start a transaction if
one is not already in effect.  Automatically started transactions
are committed at the conclusion of the command.







|
|
>
|















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


<
<
<
<






|

















|
<







125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165




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

191
192
193
194
195
196
197
file to the current database connection.  If the filename contains 
punctuation characters it must be quoted.  The names 'main' and 
'temp' refer to the main database and the database used for 
temporary tables.  These cannot be detached.  Attached databases 
are removed using the <a href="#detach">DETACH DATABASE</a> 
statement.</p>

<p>You can read from and write to an attached database and you
can modify the schema of the attached database.  This is a new
feature of SQLite version 3.0.  In SQLite 2.8, schema changes
to attached databases were not allows.</p>

<p>You cannot create a new table with the same name as a table in 
an attached database, but you can attach a database which contains
tables whose names are duplicates of tables in the main database.  It is 
also permissible to attach the same database file multiple times.</p>

<p>Tables in an attached database can be referred to using the syntax 
<i>database-name.table-name</i>.  If an attached table doesn't have 
a duplicate table name in the main database, it doesn't require a 
database name prefix.  When a database is attached, all of its 
tables which don't have duplicate names become the 'default' table
of that name.  Any tables of that name attached afterwards require the table 
prefix. If the 'default' table of a given name is detached, then 
the last table of that name attached becomes the new default.</p>

<p>
Transactions involving multiple attached databases are atomic,
assuming that the main database is not ":memory:".  If the main
database is ":memory:" then 
transactions continue to be atomic within each individual
database file. But if the host computer crashes in the middle
of a COMMIT where two or more database files are updated,
some of those files might get the changes where others
might not.
Atomic commit of attached databases is a new feature of SQLite version 3.0.
In SQLite version 2.8, all commits to attached databases behaved as if
the main database were ":memory:".
</p>

<p>There is a compile-time limit of 10 attached database files.</p>




}


Section {BEGIN TRANSACTION} transaction

Syntax {sql-statement} {
BEGIN [TRANSACTION [<name>]]
}
Syntax {sql-statement} {
END [TRANSACTION [<name>]]
}
Syntax {sql-statement} {
COMMIT [TRANSACTION [<name>]]
}
Syntax {sql-statement} {
ROLLBACK [TRANSACTION [<name>]]
}

puts {
<p>Beginning in version 2.0, SQLite supports transactions with
rollback and atomic commit.  See <a href="#attach">ATTACH</a> for
an exception when there are attached databases.</p>

<p>The optional transaction name is ignored. SQLite currently 
does not allow nested transactions.</p>


<p>
No changes can be made to the database except within a transaction.
Any command that changes the database (basically, any SQL command
other than SELECT) will automatically start a transaction if
one is not already in effect.  Automatically started transactions
are committed at the conclusion of the command.
202
203
204
205
206
207
208



209

210
211
212




213
214
215
216
217
218
219
220
221
222
223
and the ROLLBACK conflict resolution algorithm is specified.
See the documention on the <a href="#conflict">ON CONFLICT</a>
clause for additional information about the ROLLBACK
conflict resolution algorithm.
</p>

<p>



The optional ON CONFLICT clause at the end of a BEGIN statement

can be used to changed the default conflict resolution algorithm.
The normal default is ABORT.  If an alternative is specified by
the ON CONFLICT clause of a BEGIN, then that alternative is used




as the default for all commands within the transaction.  The default
algorithm is overridden by ON CONFLICT clauses on individual
constraints within the CREATE TABLE or CREATE INDEX statements
and by the OR clauses on COPY, INSERT, and UPDATE commands.
</p>
}


Section comment comment

Syntax {comment} {<SQL-comment> | <C-comment>







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







205
206
207
208
209
210
211
212
213
214
215
216
217


218
219
220
221
222

223

224
225
226
227
228
229
230
and the ROLLBACK conflict resolution algorithm is specified.
See the documention on the <a href="#conflict">ON CONFLICT</a>
clause for additional information about the ROLLBACK
conflict resolution algorithm.
</p>

<p>
The COMMIT command does not actually perform a commit until all
pending SQL commands finish.  Thus if two or more SELECT statements
are in the middle of processing and a COMMIT is executed, the commit
will not actually occur until all SELECT statements finish.
</p>



<p>
An attempt to execute COMMIT might result in an SQLITE_BUSY return code.
This indicates that another thread or process had a read lock on the database
that prevented the database from being updated.  When COMMIT fails in this
way, the transaction remains active and the COMMIT can be retried later

after the reader has had a chance to clear.

</p>
}


Section comment comment

Syntax {comment} {<SQL-comment> | <C-comment>
247
248
249
250
251
252
253





254
255
256
257
258
259
260

Syntax {sql-statement} {
COPY [ OR <conflict-algorithm> ] [<database-name> .] <table-name> FROM <filename>
[ USING DELIMITERS <delim> ]
}

puts {





<p>The COPY command is an extension used to load large amounts of
data into a table.  It is modeled after a similar command found
in PostgreSQL.  In fact, the SQLite COPY command is specifically
designed to be able to read the output of the PostgreSQL dump
utility <b>pg_dump</b> so that data can be easily transferred from
PostgreSQL into SQLite.</p>








>
>
>
>
>







254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272

Syntax {sql-statement} {
COPY [ OR <conflict-algorithm> ] [<database-name> .] <table-name> FROM <filename>
[ USING DELIMITERS <delim> ]
}

puts {
<p>The COPY command is available in SQLite version 2.8 and earlier.
The COPY command has been removed from SQLite version 3.0 due to
complications in trying to support it in a mixed UTF-8/16 environment.
</p>

<p>The COPY command is an extension used to load large amounts of
data into a table.  It is modeled after a similar command found
in PostgreSQL.  In fact, the SQLite COPY command is specifically
designed to be able to read the output of the PostgreSQL dump
utility <b>pg_dump</b> so that data can be easily transferred from
PostgreSQL into SQLite.</p>

286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303






304
305
306
307
308
309
310
Section {CREATE INDEX} createindex

Syntax {sql-statement} {
CREATE [UNIQUE] INDEX <index-name> 
ON [<database-name> .] <table-name> ( <column-name> [, <column-name>]* )
[ ON CONFLICT <conflict-algorithm> ]
} {column-name} {
<name> [ ASC | DESC ]
}

puts {
<p>The CREATE INDEX command consists of the keywords "CREATE INDEX" followed
by the name of the new index, the keyword "ON", the name of a previously
created table that is to be indexed, and a parenthesized list of names of
columns in the table that are used for the index key.
Each column name can be followed by one of the "ASC" or "DESC" keywords
to indicate sort order, but the sort order is ignored in the current
implementation.  Sorting is always done in ascending order.</p>







<p>There are no arbitrary limits on the number of indices that can be
attached to a single table, nor on the number of columns in an index.</p>

<p>If the UNIQUE keyword appears between CREATE and INDEX then duplicate
index entries are not allowed.  Any attempt to insert a duplicate entry
will result in an error.</p>







|










>
>
>
>
>
>







298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
Section {CREATE INDEX} createindex

Syntax {sql-statement} {
CREATE [UNIQUE] INDEX <index-name> 
ON [<database-name> .] <table-name> ( <column-name> [, <column-name>]* )
[ ON CONFLICT <conflict-algorithm> ]
} {column-name} {
<name> [ COLLATE <collation-name>] [ ASC | DESC ]
}

puts {
<p>The CREATE INDEX command consists of the keywords "CREATE INDEX" followed
by the name of the new index, the keyword "ON", the name of a previously
created table that is to be indexed, and a parenthesized list of names of
columns in the table that are used for the index key.
Each column name can be followed by one of the "ASC" or "DESC" keywords
to indicate sort order, but the sort order is ignored in the current
implementation.  Sorting is always done in ascending order.</p>

<p>The COLLATE clause following each column name defines a collating
sequence used for text entires in that column.  The default collating
sequence is the collating sequence defined for that column in the
CREATE TABLE statement.  Or if no collating sequence is otherwise defined,
the built-in BINARY collating sequence is used.</p>

<p>There are no arbitrary limits on the number of indices that can be
attached to a single table, nor on the number of columns in an index.</p>

<p>If the UNIQUE keyword appears between CREATE and INDEX then duplicate
index entries are not allowed.  Any attempt to insert a duplicate entry
will result in an error.</p>
349
350
351
352
353
354
355
356

357
358
359
360
361
362
363
<typename> ( <number> ) |
<typename> ( <number> , <number> )
} {column-constraint} {
NOT NULL [ <conflict-clause> ] |
PRIMARY KEY [<sort-order>] [ <conflict-clause> ] |
UNIQUE [ <conflict-clause> ] |
CHECK ( <expr> ) [ <conflict-clause> ] |
DEFAULT <value>

} {constraint} {
PRIMARY KEY ( <column-list> ) [ <conflict-clause> ] |
UNIQUE ( <column-list> ) [ <conflict-clause> ] |
CHECK ( <expr> ) [ <conflict-clause> ]
} {conflict-clause} {
ON CONFLICT <conflict-algorithm>
}







|
>







367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
<typename> ( <number> ) |
<typename> ( <number> , <number> )
} {column-constraint} {
NOT NULL [ <conflict-clause> ] |
PRIMARY KEY [<sort-order>] [ <conflict-clause> ] |
UNIQUE [ <conflict-clause> ] |
CHECK ( <expr> ) [ <conflict-clause> ] |
DEFAULT <value> |
COLLATE <collation-name>
} {constraint} {
PRIMARY KEY ( <column-list> ) [ <conflict-clause> ] |
UNIQUE ( <column-list> ) [ <conflict-clause> ] |
CHECK ( <expr> ) [ <conflict-clause> ]
} {conflict-clause} {
ON CONFLICT <conflict-algorithm>
}
375
376
377
378
379
380
381



382
383
384
385
386
387
388
The datatype for the column does not restrict what data may be put
in that column.
All information is stored as null-terminated strings.
The UNIQUE constraint causes an index to be created on the specified
columns.  This index must contain unique keys.
The DEFAULT constraint
specifies a default value to use when doing an INSERT.



</p>

<p>Specifying a PRIMARY KEY normally just creates a UNIQUE index
on the primary key.  However, if primary key is on a single column
that has datatype INTEGER, then that column is used internally
as the actual key of the B-Tree for the table.  This means that the column
may only hold unique integer values.  (Except for this one case,







>
>
>







394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
The datatype for the column does not restrict what data may be put
in that column.
All information is stored as null-terminated strings.
The UNIQUE constraint causes an index to be created on the specified
columns.  This index must contain unique keys.
The DEFAULT constraint
specifies a default value to use when doing an INSERT.
The COLLATE clause specifies what text collating function to use
when comparing text entries for the column.  The built-in BINARY
collating function is used by default.
</p>

<p>Specifying a PRIMARY KEY normally just creates a UNIQUE index
on the primary key.  However, if primary key is on a single column
that has datatype INTEGER, then that column is used internally
as the actual key of the B-Tree for the table.  This means that the column
may only hold unique integer values.  (Except for this one case,
1242
1243
1244
1245
1246
1247
1248
1249
1250
1251
1252
1253
1254
1255
1256
1257
1258
1259
1260
1261
1262
1263
    improvement.</p>
    <p>When you change the cache size using the cache_size pragma, the
    change only endures for the current session.  The cache size reverts
    to the default value when the database is closed and reopened.  Use
    the <a href="#pragma_default_cache_size"><b>default_cache_size</b></a> 
    pragma to check the cache size permanently.</p></li>

<li><p><b>PRAGMA count_changes = ON; </b>(1)<b>
       <br>PRAGMA count_changes = OFF;</b> (0)</p>
    <p>When on, the COUNT_CHANGES pragma causes the callback function to
    be invoked once for each DELETE, INSERT, or UPDATE operation.  The
    argument is the number of rows that were changed.</p>
    <p>This pragma may be removed from future versions of SQLite.
    Consider using the <b>sqlite_changes()</b> API function instead.</p></li>

<li><p><b>PRAGMA database_list;</b></p>
    <p>For each open database, invoke the callback function once with
    information about that database.  Arguments include the index and 
    the name the datbase was attached with.  The first row will be for 
    the main database.  The second row will be for the database used to 
    store temporary tables.</p></li>








<
<
<
<
<
<
<
<







1264
1265
1266
1267
1268
1269
1270








1271
1272
1273
1274
1275
1276
1277
    improvement.</p>
    <p>When you change the cache size using the cache_size pragma, the
    change only endures for the current session.  The cache size reverts
    to the default value when the database is closed and reopened.  Use
    the <a href="#pragma_default_cache_size"><b>default_cache_size</b></a> 
    pragma to check the cache size permanently.</p></li>









<li><p><b>PRAGMA database_list;</b></p>
    <p>For each open database, invoke the callback function once with
    information about that database.  Arguments include the index and 
    the name the datbase was attached with.  The first row will be for 
    the main database.  The second row will be for the database used to 
    store temporary tables.</p></li>

1332
1333
1334
1335
1336
1337
1338
1339
1340
1341
1342
1343
1344
1345
1346
1347
1348
1349
1350
1351
1352
1353
1354
1355
1356
1357
1358
1359
1360
1361
1362
1363
1364
1365
1366
1367
1368
1369

    <p>This pragma changes the temp_store mode for whenever the database
    is opened in the future.  The temp_store mode for the current session
    is unchanged.  Use the 
    <a href="#pragma_temp_store"><b>temp_store</b></a> pragma to change the
    temp_store mode for the current session.</p></li>

<a name="pragma_empty_result_callbacks"></a>
<li><p><b>PRAGMA empty_result_callbacks = ON; </b>(1)<b>
       <br>PRAGMA empty_result_callbacks = OFF;</b> (0)</p>
    <p>When on, the EMPTY_RESULT_CALLBACKS pragma causes the callback
    function to be invoked once for each query that has an empty result
    set.  The third "<b>argv</b>" parameter to the callback is set to NULL
    because there is no data to report.  But the second "<b>argc</b>" and
    fourth "<b>columnNames</b>" parameters are valid and can be used to
    determine the number and names of the columns that would have been in
    the result set had the set not been empty.</p></li>

<li><p><b>PRAGMA foreign_key_list(</b><i>table-name</i><b>);</b></p>
    <p>For each foreign key that references a column in the argument
    table, invoke the callback function with information about that
    foreign key. The callback function will be invoked once for each
    column in each foreign key.</p></li>

<li><p><b>PRAGMA full_column_names = ON; </b>(1)<b>
       <br>PRAGMA full_column_names = OFF;</b> (0)</p>
    <p>The column names reported in an SQLite callback are normally just
    the name of the column itself, except for joins when "TABLE.COLUMN"
    is used.  But when full_column_names is turned on, column names are
    always reported as "TABLE.COLUMN" even for simple queries.</p></li>

<li><p><b>PRAGMA index_info(</b><i>index-name</i><b>);</b></p>
    <p>For each column that the named index references, invoke the 
    callback function
    once with information about that column, including the column name,
    and the column number.</p></li>

<li><p><b>PRAGMA index_list(</b><i>table-name</i><b>);</b></p>







<
<
<
<
<
<
<
<
<
<
<






<
<
<
<
<
<
<







1346
1347
1348
1349
1350
1351
1352











1353
1354
1355
1356
1357
1358







1359
1360
1361
1362
1363
1364
1365

    <p>This pragma changes the temp_store mode for whenever the database
    is opened in the future.  The temp_store mode for the current session
    is unchanged.  Use the 
    <a href="#pragma_temp_store"><b>temp_store</b></a> pragma to change the
    temp_store mode for the current session.</p></li>












<li><p><b>PRAGMA foreign_key_list(</b><i>table-name</i><b>);</b></p>
    <p>For each foreign key that references a column in the argument
    table, invoke the callback function with information about that
    foreign key. The callback function will be invoked once for each
    column in each foreign key.</p></li>








<li><p><b>PRAGMA index_info(</b><i>index-name</i><b>);</b></p>
    <p>For each column that the named index references, invoke the 
    callback function
    once with information about that column, including the column name,
    and the column number.</p></li>

<li><p><b>PRAGMA index_list(</b><i>table-name</i><b>);</b></p>
1383
1384
1385
1386
1387
1388
1389
1390
1391
1392
1393
1394
1395
1396
1397
1398
1399
1400
1401
1402
1403
1404
1405
1406
1407
1408
1409
1410
1411
1412
1413
1414
1415
1416
1417
1418
1419
1420
1421
1422
1423
1424
1425
1426
1427
1428
1429
<li><p><b>PRAGMA parser_trace = ON; </b>(1)<b>
    <br>PRAGMA parser_trace = OFF;</b> (0)</p>
    <p>Turn tracing of the SQL parser inside of the
    SQLite library on and off.  This is used for debugging.
    This only works if the library is compiled without the NDEBUG macro.
    </p></li>

<a name="pragma_show_datatypes"></a>
<li><p><b>PRAGMA show_datatypes = ON; </b>(1)<b>
    <br>PRAGMA show_datatypes = OFF;</b> (0)</p>
    <p>When turned on, the SHOW_DATATYPES pragma causes extra entries containing
    the names of <a href="datatypes.html">datatypes</a> of columns to be
    appended to the 4th ("columnNames") argument to <b>sqlite_exec()</b>
    callbacks.  When
    turned off, the 4th argument to callbacks contains only the column names.
    The datatype for table columns is taken from the CREATE TABLE statement
    that defines the table.  Columns with an unspecified datatype have a
    datatype of "NUMERIC" and the results of expression have a datatype of
    either "TEXT" or "NUMERIC" depending on the expression.
    The following chart illustrates the difference for the query
    "SELECT 'xyzzy', 5, NULL AS empty ":</p>

    <blockquote><table border=0>
    <tr><th>show_datatypes=OFF</th><th width=30></th>
        <th>show_datatypes=ON</th></tr>
    <tr><td valign="top">
       azCol[0] = "xyzzy";<br>
       azCol[1] = "5";<br>
       azCol[2] = "empty";<br>
       azCol[3] = 0;
    </td><td></td><td valign="top">
       azCol[0] = "xyzzy";<br>
       azCol[1] = "5";<br>
       azCol[2] = "empty";<br>
       azCol[3] = "TEXT";<br>
       azCol[4] = "NUMERIC";<br>
       azCol[5] = "TEXT";<br>
       azCol[6] = 0;
    </td></table></blockquote></li>

<a name="pragma_synchronous"></a>
<li><p><b>PRAGMA synchronous;
       <br>PRAGMA synchronous = FULL; </b>(2)<b>
       <br>PRAGMA synchronous = NORMAL; </b>(1)<b>
       <br>PRAGMA synchronous = OFF;</b> (0)</p>
    <p>Query or change the setting of the "synchronous" flag affecting
    the database for the duration of the current database connection.







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







1379
1380
1381
1382
1383
1384
1385

































1386
1387
1388
1389
1390
1391
1392
<li><p><b>PRAGMA parser_trace = ON; </b>(1)<b>
    <br>PRAGMA parser_trace = OFF;</b> (0)</p>
    <p>Turn tracing of the SQL parser inside of the
    SQLite library on and off.  This is used for debugging.
    This only works if the library is compiled without the NDEBUG macro.
    </p></li>


































<a name="pragma_synchronous"></a>
<li><p><b>PRAGMA synchronous;
       <br>PRAGMA synchronous = FULL; </b>(2)<b>
       <br>PRAGMA synchronous = NORMAL; </b>(1)<b>
       <br>PRAGMA synchronous = OFF;</b> (0)</p>
    <p>Query or change the setting of the "synchronous" flag affecting
    the database for the duration of the current database connection.
1509
1510
1511
1512
1513
1514
1515
1516
1517
1518
1519
1520
1521
1522
1523
} {join-op} {
, | [NATURAL] [LEFT | RIGHT | FULL] [OUTER | INNER | CROSS] JOIN
} {join-args} {
[ON <expr>] [USING ( <id-list> )]
} {sort-expr-list} {
<expr> [<sort-order>] [, <expr> [<sort-order>]]*
} {sort-order} {
ASC | DESC
} {compound_op} {
UNION | UNION ALL | INTERSECT | EXCEPT
}

puts {
<p>The SELECT statement is used to query the database.  The
result of a SELECT is zero or more rows of data where each row







|







1472
1473
1474
1475
1476
1477
1478
1479
1480
1481
1482
1483
1484
1485
1486
} {join-op} {
, | [NATURAL] [LEFT | RIGHT | FULL] [OUTER | INNER | CROSS] JOIN
} {join-args} {
[ON <expr>] [USING ( <id-list> )]
} {sort-expr-list} {
<expr> [<sort-order>] [, <expr> [<sort-order>]]*
} {sort-order} {
[ COLLATE <collation-name> ] [ ASC | DESC ]
} {compound_op} {
UNION | UNION ALL | INTERSECT | EXCEPT
}

puts {
<p>The SELECT statement is used to query the database.  The
result of a SELECT is zero or more rows of data where each row
1557
1558
1559
1560
1561
1562
1563
1564

1565
1566
1567
1568
1569
1570
1571
1572
may refer to values, even aggregate functions, that are not in the result.</p>

<p>The ORDER BY clause causes the output rows to be sorted.  
The argument to ORDER BY is a list of expressions that are used as the
key for the sort.  The expressions do not have to be part of the
result for a simple SELECT, but in a compound SELECT each sort
expression must exactly match one of the result columns.  Each
sort expression may be optionally followed by ASC or DESC to specify

the sort order.</p>

<p>The LIMIT clause places an upper bound on the number of rows
returned in the result.  A negative LIMIT indicates no upper bound.
The optional OFFSET following LIMIT specifies how many
rows to skip at the beginning of the result set.
In a compound query, the LIMIT clause may only appear on the
final SELECT statement.







|
>
|







1520
1521
1522
1523
1524
1525
1526
1527
1528
1529
1530
1531
1532
1533
1534
1535
1536
may refer to values, even aggregate functions, that are not in the result.</p>

<p>The ORDER BY clause causes the output rows to be sorted.  
The argument to ORDER BY is a list of expressions that are used as the
key for the sort.  The expressions do not have to be part of the
result for a simple SELECT, but in a compound SELECT each sort
expression must exactly match one of the result columns.  Each
sort expression may be optionally followed by a COLLATE keyword and
the name of a collating function used for ordering text and/or
keywords ASC or DESC to specify the sort order.</p>

<p>The LIMIT clause places an upper bound on the number of rows
returned in the result.  A negative LIMIT indicates no upper bound.
The optional OFFSET following LIMIT specifies how many
rows to skip at the beginning of the result set.
In a compound query, the LIMIT clause may only appear on the
final SELECT statement.
Added www/version3.tcl.


























































































































































































































































































































































































































































































































































































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

<p>
SQLite version 3.0 introduces important changes to the library, including:
</p>

<ul>
<li>A more compact format for database files.</li>
<li>Manifest typing and BLOB support.</li>
<li>Support for both UTF-8 and UTF-16 text.</li>
<li>User-defined text collating sequences.</li>
<li>64-bit ROWIDs.</li>
<li>Improved Concurrency.</li>
</ul>

<p>
This document is a quick introduction to the changes for SQLite 3.0
for users who are already familiar with SQLite version 2.8.
</p>

<h3>Naming Changes</h3>

<p>
SQLite version 2.8 will continue to be supported with bug fixes
for the foreseeable future.  In order to allow SQLite version 2.8
and SQLite version 3.0 to peacefully coexist, the names of key files
and APIs in SQLite version 3.0 have been changed to include the
character "3".  For example, the include file used by C programs
has been changed from "sqlite.h" to "sqlite3.h".  And the name of
the shell program used to interact with databases has been changed
from "sqlite.exe" to "sqlite3.exe".  With these changes, it is possible
to have both SQLite 2.8 and SQLite 3.0 installed on the same system at
the same time.  And it is possible for the same C program to link
against both SQLite 2.8 and SQLite 3.0 at the same time and to use
both libraries at the same time.
</p>

<h3>New File Format</h3>

<p>
The format used by SQLite database files has been completely revised.
The old version 2.1 format and the new 3.0 format are incompatible with
one another.  Version 2.8 of SQLite will not read and version 3.0 database
files and version 3.0 of SQLite will not read a version 2.8 database file.
</p>

<p>
To convert an SQLite 2.8 database into an SQLite 3.0 database, have
ready the command-line shells for both version 2.8 and 3.0.  Then
enter a command like the following:
</p>

<blockquote><pre>
sqlite OLD.DB .dump | sqlite3 NEW.DB
</pre></blockquote>

<p>
The new database file format uses B+Trees for tables.  In a B+Tree, all
data is stored in the leaves of the tree instead of in both the leaves and
the intermediate branch nodes.  The use of B+Trees for tables allows for
better scalability and the storage larger data fields without the use of
overflow pages.  Traditional B-Trees are still used for indices.</p>

<p>
The new file format also supports variable pages sizes between 512 and
65536 bytes.  The size of a page is stored in the file header so the
same library can read databases with different pages sizes, in theory,
though this feature has not yet been implemented in practice.
</p>

<p>
The new file format omits unused fields from its disk images.  For example,
indices use only the key part of a B-Tree record and not the data.  So
for indices, the field that records the length of the data is omitted.
Integer values such as the length of key and data are stored using
a variable-length encoding so that only one or two bytes are required to
store the most common cases but up to 64-bits of information can be encoded
if needed. 
Integer and floating point data is stored on the disk in binary rather
than being converted into ASCII as in SQLite version 2.8.
These changes taken together result in database files that are typically
25% to 35% smaller than the equivalent files in SQLite version 2.8.
</p>

<p>
Details of the low-level B-Tree format used in SQLite version 3.0 can
be found in header comments to the 
<a href="http://www.sqlite.org/cvstrac/getfile/sqlite/src/btree.c">btree.c</a>
source file.
</p>

<h3>Manifest Typing and BLOB Support</h3>

<p>
SQLite version 2.8 will deal with data in various formats internally,
but when writing to the disk or interacting through its API, SQLite 2.8
always converts data into ASCII text.  SQLite 3.0, in contrast, exposes 
its internal data representations to the user and stores binary representations
to disk when appropriate.  The exposing of non-ASCII representations was
added in order to support BLOBs.
</p>

<p>
SQLite version 2.8 had the feature that any type of data could be stored
in any table column regardless of the declared type of that column.  This
feature is retained in version 3.0, though in a slightly modified form.
Each table column will store any type of data, though columns have an
affinity for the format of data defined by their declared datatype.
When data is inserted into a column, that column will make at attempt
to convert the data format into the columns declared type.   All SQL
database engines do this.  The difference is that SQLite 3.0 will 
still store the data even if a format conversion is not possible.
</p>

<p>
For example, if you have a table column declared to be of type "INTEGER"
and you try to insert a string, the column will look at the text string
and see if it looks like a number.  If the string does look like a number
it is converted into a number and into an integer if the number does not
have a fractional part, and stored that way.  But if the string is not
a well-formed number it is still stored as a string.  A column with a
type of "TEXT" tries to convert numbers into an ASCII-Text representation
before storing them.  But BLOBs are stored in TEXT columns as BLOBs because
you cannot in general convert a BLOB into text.
</p>

<p>
In most other SQL database engines the datatype is associated with
the table column the holds the data - with the data container.
In SQLite 3.0, the datatype is associated with the data itself, not
with its container.
<a href="http://www.paulgraham.com/">Paul Graham</a> in his book 
<a href="http://www.paulgraham.com/acl.html"><i>ANSI Common Lisp</i></a>
calls this property "Manifest Typing".
Other writers have other definitions for the term "manifest typing",
so beware of confusion.  But by whatever name, that is the datatype
model supported by SQLite 3.0.
</p>

<p>
Additional information about datatypes in SQLite version 3.0 is
available
<a href="datatype3.html">separately</a>.
</p>

<h3>Support for UTF-8 and UTF-16</h3>

<p>
The new API for SQLite 3.0 contains routines that accept text as
both UTF-8 and UTF-16 in the native byte order of the host machine.
Each database file manages text as either UTF-8, UTF-16BE (big-endian),
or UTF-16LE (little-endian).  Internally and in the disk file, the
same text representation is used everywhere.  If the text representation
specified by the database file (in the file header) does not match
the text representation required by the interface routines, then text
is converted on-the-fly.
Constantly converting text from one representation to another can be
computationally expensive, so it is suggested that programmers choose a
single representation and stick with it throughout their application.
</p>

<p>
In the current implementation of SQLite, the SQL parser only works
with UTF-8 text.  So if you supply UTF-16 text it will be converted.
This is just an implementation issue and there is nothing to prevent
future versions of SQLite from parsing UTF-16 encoded SQL natively.
</p>

<p>
When creating new user-defined SQL functions and collating sequences,
each function or collating sequence can specify it if works with
UTF-8, UTF-16be, or UTF-16le.  Separate implementations can be registered
for each encoding.   If an SQL function or collating sequences is required
but a version for the current text encoding is not available, then 
the text is automatically converted.  As before, this conversion takes
computation time, so programmers are advised to pick a single
encoding and stick with it in order to minimize the amount of unnecessary
format juggling.
</p>

<p>
SQLite is not particular about the text it receives and is more than
happen to process text strings that are not normalized or even
well-formed UTF-8 or UTF-16.  Thus, programmers who want to store
IS08859 data can do so using the UTF-8 interfaces.  As long as no
attempts are made to use a UTF-16 collating sequence or SQL function,
the byte sequence of the text will not be modified in any way.
</p>

<h3>User-defined Collating Sequences</h3>

<p>
A collating sequence is just a defined order for text.  When SQLite 3.0
sorts (or uses a comparison operator like "<" or ">=") the sort order
is first determined by the data type.
</p>

<ul>
<li>NULLs sort first</li>
<li>Numeric values sort next in numerical order</li>
<li>Text values come after numerics</li>
<li>BLOBs sort last</li>
</ul>

<p>
Collating sequences are used for comparing two text strings.
The collating sequence does not change the ordering of NULLs, numbers,
or BLOBs, only text.
</p>

<p>
A collating sequence is implemented as a function that takes the
two strings being compared as inputs and returns negative, zero, or
positive if the first string is less than, equal to, or greater than
the first.
SQLite 3.0 comes with a single built-in collating sequence named "BINARY"
which is implemented using the memcmp() routine from the standard C library.
The BINARY collating sequence works well for English text.  For other
languages or locales, alternative collating sequences may be preferred.
</p>

<p>
The decision of which collating sequence to use is controlled by the
COLLATE clause in SQL.  A COLLATE clause can occur on a table definition,
to define a default collating sequence to a table column, or on field
of an index, or in the ORDER BY clause of a SELECT statement.
Planned enhancements to SQLite are to include standard CAST() syntax
to allow the collating sequence of an expression to be defined.
</p>

<h3>64-bit ROWIDs</h3>

<p>
Every row of a table has a unique rowid.
If the table defines a column with the type "INTEGER PRIMARY KEY" then that
column becomes an alias for the rowid.  But with or without an INTEGER PRIMARY
KEY column, every row still has a rowid.
</p>

<p>
In SQLite version 3.0, the rowid is a 64-bit signed integer.
This is an expansion of SQLite version 2.8 which only permitted
rowids of 32-bits.
</p>

<p>
To minimize storage space, the 64-bit rowid is stored as a variable length
integer.  Rowids between 0 and 127 use only a single byte.  
Rowids between 0 and 16383 use just 2 bytes.  Up to 2097152 uses three
bytes.  And so forth.  Negative rowids are allowed but they always use
nine bytes of storage and so their use is discouraged.  When rowids
are generated automatically by SQLite, they will always be non-negative.
</p>

<h3>Improved Concurrency</h3>

<p>
SQLite version 2.8 allowed multiple simultaneous readers or a single
writer but not both.  SQLite version 3.0 allows one process to begin
writing the database while other processes continue to read.  The
writer must still obtain an exclusive lock on the database for a brief
interval in order to commit its changes, but the exclusive lock is no
longer required for the entire write operation.
A <a href="lockingv3.html">more detailed report</a> on the locking
behavior of SQLite version 3.0 is available separately.
</p>

<p>
A limited form of table-level locking is now also available in SQLite.
If each table is stored in a separate database file, those separate
files can be attached to the main database (using the ATTACH command)
and the combined databases will function as one.  But locks will only
be acquired on individual files as needed.  So if you redefine "database"
to mean two or more database files, then it is entirely possible for
two processes to be writing to the same database at the same time.
To further support this capability, commits of transactions involving
two or more ATTACHed database are now atomic.
</p>

}
footer {$Id: version3.tcl,v 1.1 2004/06/17 19:04:17 drh Exp $}