SQLite

Check-in [799f5383c0]
Login

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

Overview
Comment:Documentation updates. (CVS 1946)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 799f5383c0c72a74c7b29f9f40fc949f3248a499
User & Date: drh 2004-09-08 13:06:21.000
Context
2004-09-08
13:07
Add new APIs to sqlite3.def: sqlite3_bind_parameter_name and sqlite3_bind_parameter_index. (CVS 1947) (check-in: ff256fb528 user: drh tags: trunk)
13:06
Documentation updates. (CVS 1946) (check-in: 799f5383c0 user: drh tags: trunk)
2004-09-07
16:19
Wildcards with the same name map into the same variable number. New api sqlite3_bind_parameter_index() added to map wildcard names into wildcard index numbers. Support for "?nnn" wildcards. (CVS 1945) (check-in: 435b3f301f user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to www/arch.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: arch.tcl,v 1.14 2004/07/17 21:56:10 drh Exp $}
source common.tcl
header {Architecture of SQLite}
puts {
<h2>The Architecture Of SQLite</h2>

<h3>Introduction</h3>




|







1
2
3
4
5
6
7
8
9
10
11
#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: arch.tcl,v 1.15 2004/09/08 13:06:21 drh Exp $}
source common.tcl
header {Architecture of SQLite}
puts {
<h2>The Architecture Of SQLite</h2>

<h3>Introduction</h3>

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

<p>
A block diagram showing the main components of SQLite
and how they interrelate is shown at the right.  The text that
follows will provide a quick overview of each of these components.
</p>

<h3>History</h3>

<p>
There are two main C interfaces to the SQLite library:
<b>sqlite_exec()</b> and <b>sqlite_compile()</b>.  Prior to
version 2.8.0 (2003-Feb-16) only sqlite_exec() was supported.
For version 2.8.0, the sqlite_exec and sqlite_compile methods
existed as peers.  Beginning with version 2.8.13, the sqlite_compile
method is the primary interface, and sqlite_exec is implemented
using sqlite_compile.  Externally, this change is an enhancement
that maintains backwards compatibility.  But internally,
the plumbing is very different.  The diagram at the right shows
the structure of SQLite for version 2.8.13 and following.
</p>

<p>
This document describes the structure for SQLite version 2.X.
SQLite version 3.0.0 introduces many new features and capabilities.
The basic architecture of the library remains the same.  However,
some of the details described here are different.  For example,
the code was in the file <b>os.c</b> has now been split out into
several file, on for each operating system.  And
the prefix on the names of API routines changed from <b>sqlite_</b>
to <b>sqlite3_</b>. 
</p>

<h3>Interface</h3>

<p>Much of the public interface to the SQLite library is implemented by
functions found in the <b>main.c</b> source file though some routines are


scattered about in other files where they can have access to data 
structures with file scope.  The
<b>sqlite_get_table()</b> routine is implemented in <b>table.c</b>.
<b>sqlite_step()</b> is found in <b>vdbe.c</b>.  
<b>sqlite_mprintf()</b> is found in <b>printf.c</b>.

The Tcl interface is implemented by <b>tclsqlite.c</b>.  More
information on the C interface to SQLite is
<a href="c_interface.html">available separately</a>.<p>

<p>To avoid name collisions with other software, all external
symbols in the SQLite library begin with the prefix <b>sqlite</b>.
Those symbols that are intended for external use (in other words,
those symbols which form the API for SQLite) begin
with <b>sqlite_</b>.</p>

<h3>SQL Command Processor</h3>

<p>

<h3>Tokenizer</h3>

<p>When a string containing SQL statements is to be executed, the
interface passes that string to the tokenizer.  The job of the tokenizer
is to break the original string up into tokens and pass those tokens
one by one to the parser.  The tokenizer is hand-coded in C.
All of the code for the tokenizer
is contained in the <b>tokenize.c</b> source file.</p>

<p>Note that in this design, the tokenizer calls the parser.  People
who are familiar with YACC and BISON may be used to doing things the
other way around -- having the parser call the tokenizer.  The author
of SQLite 
has done it both ways and finds things generally work out nicer for
the tokenizer to call the parser.  YACC has it backwards.</p>







<


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





|
>
>


|
<
|
>


|


|


|
<
<
<
<






|
<
|







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

<p>
A block diagram showing the main components of SQLite
and how they interrelate is shown at the right.  The text that
follows will provide a quick overview of each of these components.
</p>



<p>













This document describes SQLite version 3.0.  Version 2.8 and


earlier are similar but the details differ.




</p>

<h3>Interface</h3>

<p>Much of the public interface to the SQLite library is implemented by
functions found in the <b>main.c</b>, <b>legacy.c</b>, and
<b>vdbeapi.c</b> source files
though some routines are
scattered about in other files where they can have access to data 
structures with file scope.  The
<b>sqlite3_get_table()</b> routine is implemented in <b>table.c</b>.

<b>sqlite3_mprintf()</b> is found in <b>printf.c</b>.
<b>sqlite3_complete()</b> is in <b>tokenize.c</b>.
The Tcl interface is implemented by <b>tclsqlite.c</b>.  More
information on the C interface to SQLite is
<a href="capi3ref.html">available separately</a>.<p>

<p>To avoid name collisions with other software, all external
symbols in the SQLite library begin with the prefix <b>sqlite3</b>.
Those symbols that are intended for external use (in other words,
those symbols which form the API for SQLite) begin
with <b>sqlite3_</b>.</p>





<h3>Tokenizer</h3>

<p>When a string containing SQL statements is to be executed, the
interface passes that string to the tokenizer.  The job of the tokenizer
is to break the original string up into tokens and pass those tokens
one by one to the parser.  The tokenizer is hand-coded in C in 

the file <b>tokenize.c</b>.

<p>Note that in this design, the tokenizer calls the parser.  People
who are familiar with YACC and BISON may be used to doing things the
other way around -- having the parser call the tokenizer.  The author
of SQLite 
has done it both ways and finds things generally work out nicer for
the tokenizer to call the parser.  YACC has it backwards.</p>
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
</p>

<h3>Code Generator</h3>

<p>After the parser assembles tokens into complete SQL statements,
it calls the code generator to produce virtual machine code that
will do the work that the SQL statements request.  There are many
files in the code generator:  <b>build.c</b>, <b>copy.c</b>,



<b>delete.c</b>,
<b>expr.c</b>, <b>insert.c</b>, <b>pragma.c</b>,


<b>select.c</b>, <b>trigger.c</b>, <b>update.c</b>, <b>vacuum.c</b>



and <b>where.c</b>.
In these files is where most of the serious magic happens.
<b>expr.c</b> handles code generation for expressions.
<b>where.c</b> handles code generation for WHERE clauses on
SELECT, UPDATE and DELETE statements.  The files <b>copy.c</b>,
<b>delete.c</b>, <b>insert.c</b>, <b>select.c</b>, <b>trigger.c</b>
<b>update.c</b>, and <b>vacuum.c</b> handle the code generation
for SQL statements with the same names.  (Each of these files calls routines
in <b>expr.c</b> and <b>where.c</b> as necessary.)  All other
SQL statements are coded out of <b>build.c</b>.</p>



<h3>Virtual Machine</h3>

<p>The program generated by the code generator is executed by
the virtual machine.  Additional information about the virtual
machine is <a href="opcode.html">available separately</a>.
To summarize, the virtual machine implements an abstract computing
engine specifically designed to manipulate database files.  The
machine has a stack which is used for intermediate storage.
Each instruction contains an opcode and
up to three additional operands.</p>

<p>The virtual machine itself is entirely contained in a single
source file <b>vdbe.c</b>.  The virtual machine also has
its own header files: <b>vdbe.h</b> that defines an interface
between the virtual machine and the rest of the SQLite library and
<b>vdbeInt.h</b> which defines structure private the virtual machine.
The <b>vdbeaux.c</b> file contains utilities used by the virtual
machine and interface modules used by the rest of the library to
construct VM programs.</p>














<h3>B-Tree</h3>

<p>An SQLite database is maintained on disk using a B-tree implementation
found in the <b>btree.c</b> source file.  A separate B-tree is used for
each table and index in the database.  All B-trees are stored in the
same disk file.</p>


<p>The interface to the B-tree subsystem is defined by the header file
<b>btree.h</b>.
</p>

<h3>Page Cache</h3>

<p>The B-tree module requests information from the disk in 1024 byte


chunks.  The page cache is reponsible for reading, writing, and
caching these chunks.
The page cache also provides the rollback and atomic commit abstraction
and takes care of reader/writer locking of the database file.  The
B-tree driver requests particular pages from the page cache and notifies
the page cache when it wants to modify pages or commit or rollback
changes and the page cache handles all the messy details of making sure
the requests are handled quickly, safely, and efficiently.</p>

<p>The code to implement the page cache is contained in the single C
source file <b>pager.c</b>.  The interface to the page cache subsystem
is defined by the header file <b>pager.h</b>.
</p>

<h3>OS Interface</h3>

<p>
In order to provide portability between POSIX and Win32 operating systems,
SQLite uses an abstraction layer to interface with the operating system.






The <b>os.c</b> file contains about 20 routines used for opening and

closing files, deleting files, creating and deleting locks on files,










flushing the disk cache, and so forth.  Each of these functions contains

two implementations separated by #ifdefs: one for POSIX and the other






for Win32.  The interface to the OS abstraction layer is defined by

the <b>os.h</b> header file.
</p>
}

footer $rcsid







|
>
>
>

|
>
>
|
>
>
>




|




|
>
>



















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






|
>







|
>
>
|


|















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

|
>

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
</p>

<h3>Code Generator</h3>

<p>After the parser assembles tokens into complete SQL statements,
it calls the code generator to produce virtual machine code that
will do the work that the SQL statements request.  There are many
files in the code generator:
<b>attach.c</b>,
<b>auth.c</b>,
<b>build.c</b>,
<b>delete.c</b>,
<b>expr.c</b>,
<b>insert.c</b>,
<b>pragma.c</b>,
<b>select.c</b>,
<b>trigger.c</b>,
<b>update.c</b>,
<b>vacuum.c</b>
and <b>where.c</b>.
In these files is where most of the serious magic happens.
<b>expr.c</b> handles code generation for expressions.
<b>where.c</b> handles code generation for WHERE clauses on
SELECT, UPDATE and DELETE statements.  The files <b>attach.c</b>,
<b>delete.c</b>, <b>insert.c</b>, <b>select.c</b>, <b>trigger.c</b>
<b>update.c</b>, and <b>vacuum.c</b> handle the code generation
for SQL statements with the same names.  (Each of these files calls routines
in <b>expr.c</b> and <b>where.c</b> as necessary.)  All other
SQL statements are coded out of <b>build.c</b>.
The <b>auth.c</b> file implements the functionality of
<b>sqlite3_set_authorizer()</b>.</p>

<h3>Virtual Machine</h3>

<p>The program generated by the code generator is executed by
the virtual machine.  Additional information about the virtual
machine is <a href="opcode.html">available separately</a>.
To summarize, the virtual machine implements an abstract computing
engine specifically designed to manipulate database files.  The
machine has a stack which is used for intermediate storage.
Each instruction contains an opcode and
up to three additional operands.</p>

<p>The virtual machine itself is entirely contained in a single
source file <b>vdbe.c</b>.  The virtual machine also has
its own header files: <b>vdbe.h</b> that defines an interface
between the virtual machine and the rest of the SQLite library and
<b>vdbeInt.h</b> which defines structure private the virtual machine.
The <b>vdbeaux.c</b> file contains utilities used by the virtual
machine and interface modules used by the rest of the library to
construct VM programs.  The <b>vdbeapi.c</b> file contains external
interfaces to the virtual machine such as the 
<b>sqlite3_bind_...</b> family of functions.  Individual values
(strings, integer, floating point numbers, and BLOBs) are stored
in an internal object named "Mem" which is implemented by
<b>vdbemem.c</b>.</p>

<p>
SQLite implements SQL functions using callbacks to C-language routines.
Even the built-in SQL functions are implemented this way.  Most of
the built-in SQL functions (ex: <b>coalesce()</b>, <b>count()</b>,
<b>substr()</b>, and so forth) can be found in <b>func.c</b>.
Date and time conversion functions are found in <b>date.c</b>.
</p>

<h3>B-Tree</h3>

<p>An SQLite database is maintained on disk using a B-tree implementation
found in the <b>btree.c</b> source file.  A separate B-tree is used for
each table and index in the database.  All B-trees are stored in the
same disk file.  Details of the file format are recorded in a large
comment at the beginning of <b>btree.c</b>.</p>

<p>The interface to the B-tree subsystem is defined by the header file
<b>btree.h</b>.
</p>

<h3>Page Cache</h3>

<p>The B-tree module requests information from the disk in fixed-size
chunks.  The default chunk size is 1024 bytes but can vary between 512
and 65536 bytes.
The page cache is reponsible for reading, writing, and
caching these chunks.
The page cache also provides the rollback and atomic commit abstraction
and takes care of locking of the database file.  The
B-tree driver requests particular pages from the page cache and notifies
the page cache when it wants to modify pages or commit or rollback
changes and the page cache handles all the messy details of making sure
the requests are handled quickly, safely, and efficiently.</p>

<p>The code to implement the page cache is contained in the single C
source file <b>pager.c</b>.  The interface to the page cache subsystem
is defined by the header file <b>pager.h</b>.
</p>

<h3>OS Interface</h3>

<p>
In order to provide portability between POSIX and Win32 operating systems,
SQLite uses an abstraction layer to interface with the operating system.
The interface to the OS abstraction layer is defined in
<b>os.h</b>.  Each supported operating system has its own implementation:
<b>os_unix.c</b> for Unix, <b>os_win.c</b> for windows, and so forth.
Each of these operating-specific implements typically has its own
header file: <b>os_unix.h</b>, <b>os_win.h</b>, etc.
</p>

<h3>Utilities</h3>

<p>
Memory allocation and caseless string comparison routines are located
in <b>util.c</b>.
Symbol tables used by the parser are maintained by hash tables found
in <b>hash.c</b>.  The <b>utf.c</b> source file contains Unicode
conversion subroutines.
SQLite has its own private implementation of <b>printf()</b> (with
some extensions) in <b>printf.c</b> and its own random number generator
in <b>random.c</b>.
</p>

<h3>Test Code</h3>

<p>
If you count regression test scripts,
more than half the total code base of SQLite is devoted to testing.
There are many <b>assert()</b> statements in the main code files.
In additional, the source files <b>test1.c</b> through <b>test5.c</b>
together with <b>md5.c</b> implement extensions used for testing
purposes only.  The <b>os_test.c</b> backend interface is used to
simulate power failures to verify the crash-recovery mechanism in
the pager.
</p>

}
footer $rcsid
Changes to www/arch2.gif.

cannot compute difference between binary files

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.71 2004/07/18 20:52:32 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.72 2004/09/08 13:06:21 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>
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
}
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







|
<







179
180
181
182
183
184
185
186

187
188
189
190
191
192
193
}
Syntax {sql-statement} {
ROLLBACK [TRANSACTION [<name>]]
}

puts {
<p>Beginning in version 2.0, SQLite supports transactions with
rollback and atomic commit.</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
257
258
259
260
261
262
263



264
265
266
267
268
269
270
[ 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







>
>
>







256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
[ 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.
In version 3.0, the <a href="sqlite.html">command-line shell</a>
contains a new command <b>.import</b> that can be used as a substitute
for COPY.
</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
385
386
387
388
389
390
391
392
393
394

395
396
397
398
399
400
401
402
followed by the name of a new table and a parenthesized list of column
definitions and constraints.  The table name can be either an identifier
or a string.  Tables names that begin with "<b>sqlite_</b>" are reserved
for use by the engine.</p>

<p>Each column definition is the name of the column followed by the
datatype for that column, then one or more optional column constraints.
SQLite is <a href="datatypes.html">typeless</a>.
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.







<


>
|







387
388
389
390
391
392
393

394
395
396
397
398
399
400
401
402
403
404
followed by the name of a new table and a parenthesized list of column
definitions and constraints.  The table name can be either an identifier
or a string.  Tables names that begin with "<b>sqlite_</b>" are reserved
for use by the engine.</p>

<p>Each column definition is the name of the column followed by the
datatype for that column, then one or more optional column constraints.

The datatype for the column does not restrict what data may be put
in that column.
See <a href="datatype3.html">Datatypes In SQLite Version 3</a> for
additional information.
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.
444
445
446
447
448
449
450
451

452
453
454
455
456
457
458
459
Support for CHECK constraints may be added in the future.  As of
version 2.3.0, NOT NULL, PRIMARY KEY, and UNIQUE constraints all
work.</p>

<p>There are no arbitrary limits on the number
of columns or on the number of constraints in a table.
The total amount of data in a single row is limited to about
1 megabytes.  (This limit can be increased to 16MB by changing

a single #define in the source code and recompiling.)</p>

<p>The CREATE TABLE AS form defines the table to be
the result set of a query.  The names of the table columns are
the names of the columns in the result.</p>

<p>The exact text
of each CREATE TABLE statement is stored in the <b>sqlite_master</b>







|
>
|







446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
Support for CHECK constraints may be added in the future.  As of
version 2.3.0, NOT NULL, PRIMARY KEY, and UNIQUE constraints all
work.</p>

<p>There are no arbitrary limits on the number
of columns or on the number of constraints in a table.
The total amount of data in a single row is limited to about
1 megabytes in version 2.8.  In version 3.0 there is no arbitrary
limit on the amount of data in a row.</p>


<p>The CREATE TABLE AS form defines the table to be
the result set of a query.  The names of the table columns are
the names of the columns in the result.</p>

<p>The exact text
of each CREATE TABLE statement is stored in the <b>sqlite_master</b>
700
701
702
703
704
705
706
707

708
709
710
711
712
713
714
Section {DROP INDEX} dropindex

Syntax {sql-command} {
DROP INDEX [<database-name> .] <index-name>
}

puts {
<p>The DROP INDEX statement removes an index added with the <a href="#createindex">

CREATE INDEX</a> statement.  The index named is completely removed from
the disk.  The only way to recover the index is to reenter the
appropriate CREATE INDEX command.  Non-temporary indexes on tables in 
an attached database cannot be dropped.</p>

<p>The DROP INDEX statement does not reduce the size of the database 
file.  Empty space in the database is retained for later INSERTs.  To 







|
>







703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
Section {DROP INDEX} dropindex

Syntax {sql-command} {
DROP INDEX [<database-name> .] <index-name>
}

puts {
<p>The DROP INDEX statement removes an index added
with the <a href="#createindex">
CREATE INDEX</a> statement.  The index named is completely removed from
the disk.  The only way to recover the index is to reenter the
appropriate CREATE INDEX command.  Non-temporary indexes on tables in 
an attached database cannot be dropped.</p>

<p>The DROP INDEX statement does not reduce the size of the database 
file.  Empty space in the database is retained for later INSERTs.  To 
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
function can be used in any expression.  Simple functions return
a result immediately based on their inputs.  Aggregate functions
may only be used in a SELECT statement.  Aggregate functions compute
their result across all rows of the result set.</p>

<p>The functions shown below are available by default.  Additional
functions may be written in C and added to the database engine using
the <a href="c_interface.html#cfunc">sqlite_create_function()</a>
API.</p>

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

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

<tr>
<a name="globFunc"></a>
<td valign="top" align="right">glob(<i>X</i>,<i>Y</i>)</td>
<td valign="top">This function is used to implement the
"<b>Y GLOB X</b>" syntax of SQLite.  The
<a href="c_interface.html#cfunc">sqlite_create_function()</a> 
interface can
be used to override this function and thereby change the operation
of the <a href="#glob">GLOB</a> operator.</td>
</tr>

<tr>
<td valign="top" align="right">ifnull(<i>X</i>,<i>Y</i>)</td>







|



















|
|







921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
function can be used in any expression.  Simple functions return
a result immediately based on their inputs.  Aggregate functions
may only be used in a SELECT statement.  Aggregate functions compute
their result across all rows of the result set.</p>

<p>The functions shown below are available by default.  Additional
functions may be written in C and added to the database engine using
the <a href="capi3ref.html#cfunc">sqlite3_create_function()</a>
API.</p>

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

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

<tr>
<a name="globFunc"></a>
<td valign="top" align="right">glob(<i>X</i>,<i>Y</i>)</td>
<td valign="top">This function is used to implement the
"<b>X GLOB Y</b>" syntax of SQLite.  The
<a href="capi3ref.html#sqlite3_create_function">sqlite3_create_function()</a> 
interface can
be used to override this function and thereby change the operation
of the <a href="#glob">GLOB</a> operator.</td>
</tr>

<tr>
<td valign="top" align="right">ifnull(<i>X</i>,<i>Y</i>)</td>
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
characters is returned, not the number of bytes.</td>
</tr>

<tr>
<a name="likeFunc"></a>
<td valign="top" align="right">like(<i>X</i>,<i>Y</i>)</td>
<td valign="top">This function is used to implement the
"<b>Y LIKE X</b>" syntax of SQL.  The
<a href="c_interface.html#cfunc">sqlite_create_function()</a> 
interface can
be used to override this function and thereby change the operation
of the <a href="#like">LIKE</a> operator.</td>
</tr>

<tr>
<td valign="top" align="right">lower(<i>X</i>)</td>







|
|







973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
characters is returned, not the number of bytes.</td>
</tr>

<tr>
<a name="likeFunc"></a>
<td valign="top" align="right">like(<i>X</i>,<i>Y</i>)</td>
<td valign="top">This function is used to implement the
"<b>X LIKE Y</b>" syntax of SQL.  The
<a href="capi3ref.html#sqlite3_create_function">sqlite_create_function()</a> 
interface can
be used to override this function and thereby change the operation
of the <a href="#like">LIKE</a> operator.</td>
</tr>

<tr>
<td valign="top" align="right">lower(<i>X</i>)</td>
1008
1009
1010
1011
1012
1013
1014











1015
1016
1017
1018
1019
1020
1021

<tr>
<td valign="top" align="right">nullif(<i>X</i>,<i>Y</i>)</td>
<td valign="top">Return the first argument if the arguments are different, 
otherwise return NULL.</td>
</tr>












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

<tr>







>
>
>
>
>
>
>
>
>
>
>







1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036

<tr>
<td valign="top" align="right">nullif(<i>X</i>,<i>Y</i>)</td>
<td valign="top">Return the first argument if the arguments are different, 
otherwise return NULL.</td>
</tr>

<tr>
<td valign="top" align="right">quote(<i>X</i>)</td>
<td valign="top">This routine returns a string which is the value of
its argument suitable for inclusion into another SQL statement.
Strings are surrounded by single-quotes with escapes on interior quotes
as needed.  BLOBs are encoded as hexadecimal literals.
The current implementation of VACUUM uses this function.  The function
is also useful when writing triggers to implement undo/redo functionality.
</td>
</tr>

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

<tr>
1049
1050
1051
1052
1053
1054
1055

1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071

1072
1073
1074
1075
1076
1077
1078
1079
right rather than the left.  If SQLite is configured to support UTF-8,
then characters indices refer to actual UTF-8 characters, not bytes.</td>
</tr>

<tr>
<td valign="top" align="right">typeof(<i>X</i>)</td>
<td valign="top">Return the type of the expression <i>X</i>.  The only 

return values are "numeric" and "text".  SQLite's type handling is 
explained in <a href="datatypes.html">Datatypes in SQLite</a>.</td>
</tr>

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

<p>
The following aggregate functions are available by default.  Additional
aggregate functions written in C may be added using the 

<a href="c_interface.html#cfunc">sqlite_create_aggregate()</a> API.</p>

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








>
|
|














>
|







1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
right rather than the left.  If SQLite is configured to support UTF-8,
then characters indices refer to actual UTF-8 characters, not bytes.</td>
</tr>

<tr>
<td valign="top" align="right">typeof(<i>X</i>)</td>
<td valign="top">Return the type of the expression <i>X</i>.  The only 
return values are "null", "integer", "real", "text", and "blob".
SQLite's type handling is 
explained in <a href="datatype3.html">Datatypes in SQLite Version 3</a>.</td>
</tr>

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

<p>
The following aggregate functions are available by default.  Additional
aggregate functions written in C may be added using the 
<a href="capi3ref.html#sqlite3_create_function">sqlite3_create_function()</a>
API.</p>

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

1266
1267
1268
1269
1270
1271
1272
1273


1274
1275
1276
1277
1278
1279
1280
1281
    the main database.  The second row will be for the database used to 
    store temporary tables.</p></li>

<a name="pragma_default_cache_size"></a>
<li><p><b>PRAGMA default_cache_size;
       <br>PRAGMA default_cache_size = </b><i>Number-of-pages</i><b>;</b></p>
    <p>Query or change the maximum number of database disk pages that SQLite
    will hold in memory at once.  Each page uses 1K on disk and about 1.5K in memory.


    This pragma works like the <a href="#pragma_cache_size"><b>cache_size</b></a> 
    pragma with the additional
    feature that it changes the cache size persistently.  With this pragma,
    you can set the cache size once and that setting is retained and reused
    everytime you reopen the database.</p></li>

<a name="pragma_default_synchronous"></a>
<li><p><b>PRAGMA default_synchronous;







|
>
>
|







1283
1284
1285
1286
1287
1288
1289
1290
1291
1292
1293
1294
1295
1296
1297
1298
1299
1300
    the main database.  The second row will be for the database used to 
    store temporary tables.</p></li>

<a name="pragma_default_cache_size"></a>
<li><p><b>PRAGMA default_cache_size;
       <br>PRAGMA default_cache_size = </b><i>Number-of-pages</i><b>;</b></p>
    <p>Query or change the maximum number of database disk pages that SQLite
    will hold in memory at once.  Each page uses 1K on disk and about
    1.5K in memory.
    This pragma works like the
    <a href="#pragma_cache_size"><b>cache_size</b></a> 
    pragma with the additional
    feature that it changes the cache size persistently.  With this pragma,
    you can set the cache size once and that setting is retained and reused
    everytime you reopen the database.</p></li>

<a name="pragma_default_synchronous"></a>
<li><p><b>PRAGMA default_synchronous;
1304
1305
1306
1307
1308
1309
1310
1311


1312
1313
1314
1315
1316
1317
1318
    crashes or the computer loses power before that data has been written
    to the disk surface.  On the other hand, some
    operations are as much as 50 or more times faster with synchronous OFF.
    </p>
    <p>This pragma changes the synchronous mode persistently.  Once changed,
    the mode stays as set even if the database is closed and reopened.  The
    <a href="#pragma_synchronous"><b>synchronous</b></a> pragma does the same 
    thing but only applies the setting to the current session.</p></li>



<a name="pragma_default_temp_store"></a>
<li><p><b>PRAGMA default_temp_store;
       <br>PRAGMA default_temp_store = DEFAULT; </b>(0)<b>
       <br>PRAGMA default_temp_store = MEMORY; </b>(2)<b>
       <br>PRAGMA default_temp_store = FILE;</b> (1)</p>
    <p>Query or change the setting of the "<b>temp_store</b>" flag stored in







|
>
>







1323
1324
1325
1326
1327
1328
1329
1330
1331
1332
1333
1334
1335
1336
1337
1338
1339
    crashes or the computer loses power before that data has been written
    to the disk surface.  On the other hand, some
    operations are as much as 50 or more times faster with synchronous OFF.
    </p>
    <p>This pragma changes the synchronous mode persistently.  Once changed,
    the mode stays as set even if the database is closed and reopened.  The
    <a href="#pragma_synchronous"><b>synchronous</b></a> pragma does the same 
    thing but only applies the setting to the current session.
    
    </p></li>

<a name="pragma_default_temp_store"></a>
<li><p><b>PRAGMA default_temp_store;
       <br>PRAGMA default_temp_store = DEFAULT; </b>(0)<b>
       <br>PRAGMA default_temp_store = MEMORY; </b>(2)<b>
       <br>PRAGMA default_temp_store = FILE;</b> (1)</p>
    <p>Query or change the setting of the "<b>temp_store</b>" flag stored in