SQLite

Check-in [2052911b1f]
Login

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

Overview
Comment:Website redesign in preparation for adding version 3 documentation. (CVS 1506)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 2052911b1fd119af9dbd81fdfe80244685f58212
User & Date: drh 2004-05-31 15:06:29.000
Context
2004-05-31
16:04
More website updates. (CVS 1507) (check-in: 460f236114 user: drh tags: trunk)
15:06
Website redesign in preparation for adding version 3 documentation. (CVS 1506) (check-in: 2052911b1f user: drh tags: trunk)
12:34
Some test cases for read locks (CVS 1505) (check-in: 0e07006704 user: danielk1977 tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to www/arch.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

#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: arch.tcl,v 1.11 2004/03/14 11:57:58 drh Exp $}


puts {<html>
<head>
  <title>Architecture of SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>
The Architecture Of SQLite
</h1>}
puts "<p align=center>
(This page was last modified on [lrange $rcsid 3 4] UTC)
</p>"

puts {
<h2>Introduction</h2>

<table align="right" border="1" cellpadding="15" cellspacing="1">
<tr><th>Block Diagram Of SQLite</th></tr>
<tr><td><img src="arch2.gif"></td></tr>
</table>
<p>This document describes the architecture of the SQLite library.
The information here is useful to those who want to understand or
modify the inner workings of SQLite.
</p>

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

<h2>History</h2>

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

<h2>Interface</h2>

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

<h2>SQL Command Process</h2>

<p>

<h2>Tokenizer</h2>

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

<h2>Parser</h2>

<p>The parser is the piece that assigns meaning to tokens based on
their context.  The parser for SQLite is generated using the
<a href="http://www.hwaci.com/sw/lemon/">Lemon</a> LALR(1) parser
generator.  Lemon does the same job as YACC/BISON, but it uses
a different input syntax which is less error-prone.
Lemon also generates a parser which is reentrant and thread-safe.
And lemon defines the concept of a non-terminal destructor so
that it does not leak memory when syntax errors are encountered.
The source file that drives Lemon is found in <b>parse.y</b>.</p>

<p>Because
lemon is a program not normally found on development machines, the
complete source code to lemon (just one C file) is included in the
SQLite distribution in the "tool" subdirectory.  Documentation on
lemon is found in the "doc" subdirectory of the distribution.
</p>

<h2>Code Generator</h2>

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

<h2>Virtual Machine</h2>

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

<h2>Backend</h2>

<p>The backend is an abstraction layer that presents a uniform interface
to the virtual machine for either the B-Tree drivers for disk-based
databases or the Red/Black Tree driver for in-memory databases.
The <b>btree.h</b> source file contains the details.</p>

<h2>Red/Black Tree</h2>

<p>In-memory databases are stored in a red/black tree implementation
contain in the <b>btree_rb.c</b> source file.
</p>

<h2>B-Tree</h2>

<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.  Each page of a B-tree is 1024 bytes in size.  The key
and data for an entry are stored together in an area called "payload".
Up to 236 bytes of payload can be stored on the same page as the B-tree
entry.  Any additional payload is stored in a chain of overflow pages.</p>

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

<h2>Page Cache</h2>

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

<h2>OS Interface</h2>

<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>
}

puts {
<br clear="both" />
<p><hr /></p>
<p><a href="index.html"><img src="/goback.jpg" border=0 />
Back to the SQLite Home Page</a>
</p>

</body></html>}




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

<
|
















|














|


















|



|















|


















|



















|



















|






|





|













|
















|












|
<
<
<
<
<
<
<
<

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









#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: arch.tcl,v 1.12 2004/05/31 15:06:29 drh Exp $}
source common.tcl
header {Architecture of SQLite}
puts {





<h2>The Architecture Of SQLite</h2>






<h3>Introduction</h3>

<table align="right" border="1" cellpadding="15" cellspacing="1">
<tr><th>Block Diagram Of SQLite</th></tr>
<tr><td><img src="arch2.gif"></td></tr>
</table>
<p>This document describes the architecture of the SQLite library.
The information here is useful to those who want to understand or
modify the inner workings of SQLite.
</p>

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

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

<h3>Parser</h3>

<p>The parser is the piece that assigns meaning to tokens based on
their context.  The parser for SQLite is generated using the
<a href="http://www.hwaci.com/sw/lemon/">Lemon</a> LALR(1) parser
generator.  Lemon does the same job as YACC/BISON, but it uses
a different input syntax which is less error-prone.
Lemon also generates a parser which is reentrant and thread-safe.
And lemon defines the concept of a non-terminal destructor so
that it does not leak memory when syntax errors are encountered.
The source file that drives Lemon is found in <b>parse.y</b>.</p>

<p>Because
lemon is a program not normally found on development machines, the
complete source code to lemon (just one C file) is included in the
SQLite distribution in the "tool" subdirectory.  Documentation on
lemon is found in the "doc" subdirectory of the distribution.
</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>Backend</h3>

<p>The backend is an abstraction layer that presents a uniform interface
to the virtual machine for either the B-Tree drivers for disk-based
databases or the Red/Black Tree driver for in-memory databases.
The <b>btree.h</b> source file contains the details.</p>

<h3>Red/Black Tree</h3>

<p>In-memory databases are stored in a red/black tree implementation
contain in the <b>btree_rb.c</b> source file.
</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.  Each page of a B-tree is 1024 bytes in size.  The key
and data for an entry are stored together in an area called "payload".
Up to 236 bytes of payload can be stored on the same page as the B-tree
entry.  Any additional payload is stored in a chain of overflow pages.</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
Changes to www/c_interface.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

#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: c_interface.tcl,v 1.39 2003/10/18 09:37:27 danielk1977 Exp $}

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

The C language interface to the SQLite library
</h1>}
puts "<p align=center>
(This page was last modified on [lrange $rcsid 3 4] UTC)
</p>"

puts {
<p>The SQLite library is designed to be very easy to use from
a C or C++ program.  This document gives an overview of the C/C++
programming interface.</p>

<h2>1.0 The Core API</h2>

<p>The interface to the SQLite library consists of three core functions,
one opaque data structure, and some constants used as return values.
The core interface is as follows:</p>

<blockquote><pre>
typedef struct sqlite sqlite;




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

<




|








1
2
3
4
5


6



7
8




9

10
11
12
13
14
15
16
17
18
19
20
21

#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: c_interface.tcl,v 1.40 2004/05/31 15:06:29 drh Exp $}
source common.tcl


header {The C language interface to the SQLite library}



puts {
<h2>The C language interface to the SQLite library</h2>






<p>The SQLite library is designed to be very easy to use from
a C or C++ program.  This document gives an overview of the C/C++
programming interface.</p>

<h3>1.0 The Core API</h3>

<p>The interface to the SQLite library consists of three core functions,
one opaque data structure, and some constants used as return values.
The core interface is as follows:</p>

<blockquote><pre>
typedef struct sqlite sqlite;
︙︙
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61

The above is all you really need to know in order to use SQLite
in your C or C++ programs.  There are other interface functions
available (and described below) but we will begin by describing
the core functions shown above.
</p>

<a name="sqlite_open">
<h3>1.1 Opening a database</h3>

<p>Use the <b>sqlite_open</b> function to open an existing SQLite
database or to create a new SQLite database.  The first argument
is the database name.  The second argument is intended to signal
whether the database is going to be used for reading and writing
or just for reading.  But in the current implementation, the
second argument to <b>sqlite_open</b> is ignored.








|








38
39
40
41
42
43
44
45
46
47
48
49
50
51
52

The above is all you really need to know in order to use SQLite
in your C or C++ programs.  There are other interface functions
available (and described below) but we will begin by describing
the core functions shown above.
</p>

<a name="sqlite_open">
<h4>1.1 Opening a database</h4>

<p>Use the <b>sqlite_open</b> function to open an existing SQLite
database or to create a new SQLite database.  The first argument
is the database name.  The second argument is intended to signal
whether the database is going to be used for reading and writing
or just for reading.  But in the current implementation, the
second argument to <b>sqlite_open</b> is ignored.
︙︙
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102

<p>The return value of the <b>sqlite_open</b> function is a
pointer to an opaque <b>sqlite</b> structure.  This pointer will
be the first argument to all subsequent SQLite function calls that
deal with the same database.  NULL is returned if the open fails
for any reason.</p>

<a name="sqlite_close">
<h3>1.2 Closing the database</h3>

<p>To close an SQLite database, call the <b>sqlite_close</b>
function passing it the sqlite structure pointer that was obtained
from a prior call to <b>sqlite_open</b>.
If a transaction is active when the database is closed, the transaction
is rolled back.</p>

<a name="sqlite_exec">
<h3>1.3 Executing SQL statements</h3>

<p>The <b>sqlite_exec</b> function is used to process SQL statements
and queries.  This function requires 5 parameters as follows:</p>

<ol>
<li><p>A pointer to the sqlite structure obtained from a prior call
       to <b>sqlite_open</b>.</p></li>








|








|








70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93

<p>The return value of the <b>sqlite_open</b> function is a
pointer to an opaque <b>sqlite</b> structure.  This pointer will
be the first argument to all subsequent SQLite function calls that
deal with the same database.  NULL is returned if the open fails
for any reason.</p>

<a name="sqlite_close">
<h4>1.2 Closing the database</h4>

<p>To close an SQLite database, call the <b>sqlite_close</b>
function passing it the sqlite structure pointer that was obtained
from a prior call to <b>sqlite_open</b>.
If a transaction is active when the database is closed, the transaction
is rolled back.</p>

<a name="sqlite_exec">
<h4>1.3 Executing SQL statements</h4>

<p>The <b>sqlite_exec</b> function is used to process SQL statements
and queries.  This function requires 5 parameters as follows:</p>

<ol>
<li><p>A pointer to the sqlite structure obtained from a prior call
       to <b>sqlite_open</b>.</p></li>
︙︙
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180

result set is empty.</p>

<a name="callback_returns_nonzero">
<p>The callback function should normally return 0.  If the callback
function returns non-zero, the query is immediately aborted and 
<b>sqlite_exec</b> will return SQLITE_ABORT.</p>

<h3>1.4 Error Codes</h3>

<p>
The <b>sqlite_exec</b> function normally returns SQLITE_OK.  But
if something goes wrong it can return a different value to indicate
the type of error.  Here is a complete list of the return codes:
</p>









|








157
158
159
160
161
162
163
164
165
166
167
168
169
170
171

result set is empty.</p>

<a name="callback_returns_nonzero">
<p>The callback function should normally return 0.  If the callback
function returns non-zero, the query is immediately aborted and 
<b>sqlite_exec</b> will return SQLITE_ABORT.</p>

<h4>1.4 Error Codes</h4>

<p>
The <b>sqlite_exec</b> function normally returns SQLITE_OK.  But
if something goes wrong it can return a different value to indicate
the type of error.  Here is a complete list of the return codes:
</p>
︙︙
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354

<b>sqlite_step</b> routine which is part of the non-callback API.
It indicates that the SQL statement has been completely executed and
the <b>sqlite_finalize</b> routine is ready to be called.
</p></dd>
</dl>
</blockquote>

<h2>2.0 Accessing Data Without Using A Callback Function</h2>

<p>
The <b>sqlite_exec</b> routine described above used to be the only
way to retrieve data from an SQLite database.  But many programmers found
it inconvenient to use a callback function to obtain results.  So beginning
with SQLite version 2.7.7, a second access interface is available that
does not use callbacks.








|








331
332
333
334
335
336
337
338
339
340
341
342
343
344
345

<b>sqlite_step</b> routine which is part of the non-callback API.
It indicates that the SQL statement has been completely executed and
the <b>sqlite_finalize</b> routine is ready to be called.
</p></dd>
</dl>
</blockquote>

<h3>2.0 Accessing Data Without Using A Callback Function</h3>

<p>
The <b>sqlite_exec</b> routine described above used to be the only
way to retrieve data from an SQLite database.  But many programmers found
it inconvenient to use a callback function to obtain results.  So beginning
with SQLite version 2.7.7, a second access interface is available that
does not use callbacks.
︙︙
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400

<p>
The strategy is to compile a single SQL statement using
<b>sqlite_compile</b> then invoke <b>sqlite_step</b> multiple times,
once for each row of output, and finally call <b>sqlite_finalize</b>
to clean up after the SQL has finished execution.
</p>

<h3>2.1 Compiling An SQL Statement Into A Virtual Machine</h3>

<p>
The <b>sqlite_compile</b> "compiles" a single SQL statement (specified
by the second parameter) and generates a virtual machine that is able
to execute that statement.  
As with must interface routines, the first parameter must be a pointer
to an sqlite structure that was obtained from a prior call to








|








377
378
379
380
381
382
383
384
385
386
387
388
389
390
391

<p>
The strategy is to compile a single SQL statement using
<b>sqlite_compile</b> then invoke <b>sqlite_step</b> multiple times,
once for each row of output, and finally call <b>sqlite_finalize</b>
to clean up after the SQL has finished execution.
</p>

<h4>2.1 Compiling An SQL Statement Into A Virtual Machine</h4>

<p>
The <b>sqlite_compile</b> "compiles" a single SQL statement (specified
by the second parameter) and generates a virtual machine that is able
to execute that statement.  
As with must interface routines, the first parameter must be a pointer
to an sqlite structure that was obtained from a prior call to
︙︙
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446

</p>

<p>
On success, <b>sqlite_compile</b> returns SQLITE_OK.
Otherwise and error code is returned.
</p>

<h3>2.2 Step-By-Step Execution Of An SQL Statement</h3>

<p>
After a virtual machine has been generated using <b>sqlite_compile</b>
it is executed by one or more calls to <b>sqlite_step</b>.  Each
invocation of <b>sqlite_step</b>, except the last one,
returns a single row of the result.
The number of columns in  the result is stored in the integer that








|








423
424
425
426
427
428
429
430
431
432
433
434
435
436
437

</p>

<p>
On success, <b>sqlite_compile</b> returns SQLITE_OK.
Otherwise and error code is returned.
</p>

<h4>2.2 Step-By-Step Execution Of An SQL Statement</h4>

<p>
After a virtual machine has been generated using <b>sqlite_compile</b>
it is executed by one or more calls to <b>sqlite_step</b>.  Each
invocation of <b>sqlite_step</b>, except the last one,
returns a single row of the result.
The number of columns in  the result is stored in the integer that
︙︙
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531

an error.  It is possible that a misuse of the interface will go
undetected and result in a program crash.  The SQLITE_MISUSE is
intended as a debugging aid only - to help you detect incorrect
usage prior to a mishap.  The misuse detection logic is not guaranteed
to work in every case.
</p>

<h3>2.3 Deleting A Virtual Machine</h3>

<p>
Every virtual machine that <b>sqlite_compile</b> creates should
eventually be handed to <b>sqlite_finalize</b>.  The sqlite_finalize()
procedure deallocates the memory and other resources that the virtual
machine uses.  Failure to call sqlite_finalize() will result in 
resource leaks in your program.








|








508
509
510
511
512
513
514
515
516
517
518
519
520
521
522

an error.  It is possible that a misuse of the interface will go
undetected and result in a program crash.  The SQLITE_MISUSE is
intended as a debugging aid only - to help you detect incorrect
usage prior to a mishap.  The misuse detection logic is not guaranteed
to work in every case.
</p>

<h4>2.3 Deleting A Virtual Machine</h4>

<p>
Every virtual machine that <b>sqlite_compile</b> creates should
eventually be handed to <b>sqlite_finalize</b>.  The sqlite_finalize()
procedure deallocates the memory and other resources that the virtual
machine uses.  Failure to call sqlite_finalize() will result in 
resource leaks in your program.
︙︙
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566

</p>

<p>
It is also acceptable to call <b>sqlite_finalize</b> on a virtual machine
that has never been passed to <b>sqlite_step</b> even once.
</p>

<h2>3.0 The Extended API</h2>

<p>Only the three core routines described in section 1.0 are required to use
SQLite.  But there are many other functions that provide 
useful interfaces.  These extended routines are as follows:
</p>

<blockquote><pre>








|








543
544
545
546
547
548
549
550
551
552
553
554
555
556
557

</p>

<p>
It is also acceptable to call <b>sqlite_finalize</b> on a virtual machine
that has never been passed to <b>sqlite_step</b> even once.
</p>

<h3>3.0 The Extended API</h3>

<p>Only the three core routines described in section 1.0 are required to use
SQLite.  But there are many other functions that provide 
useful interfaces.  These extended routines are as follows:
</p>

<blockquote><pre>
︙︙
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668

void sqlite_progress_handler(sqlite*, int, int (*)(void*), void*);

</pre></blockquote>

<p>All of the above definitions are included in the "sqlite.h"
header file that comes in the source tree.</p>

<h3>3.1 The ROWID of the most recent insert</h3>

<p>Every row of an SQLite table has a unique integer key.  If the
table has a column labeled INTEGER PRIMARY KEY, then that column
serves as the key.  If there is no INTEGER PRIMARY KEY column then
the key is a unique integer.  The key for a row can be accessed in
a SELECT statement or used in a WHERE or ORDER BY clause using any
of the names "ROWID", "OID", or "_ROWID_".</p>

<p>When you do an insert into a table that does not have an INTEGER PRIMARY
KEY column, or if the table does have an INTEGER PRIMARY KEY but the value
for that column is not specified in the VALUES clause of the insert, then
the key is automatically generated.  You can find the value of the key
for the most recent INSERT statement using the
<b>sqlite_last_insert_rowid</b> API function.</p>

<h3>3.2 The number of rows that changed</h3>

<p>The <b>sqlite_changes</b> API function returns the number of rows
that have been inserted, deleted, or modified since the database was
last quiescent.  A "quiescent" database is one in which there are
no outstanding calls to <b>sqlite_exec</b> and no VMs created by
<b>sqlite_compile</b> that have not been finalized by <b>sqlite_finalize</b>.
In common usage, <b>sqlite_changes</b> returns the number








|















|








629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659

void sqlite_progress_handler(sqlite*, int, int (*)(void*), void*);

</pre></blockquote>

<p>All of the above definitions are included in the "sqlite.h"
header file that comes in the source tree.</p>

<h4>3.1 The ROWID of the most recent insert</h4>

<p>Every row of an SQLite table has a unique integer key.  If the
table has a column labeled INTEGER PRIMARY KEY, then that column
serves as the key.  If there is no INTEGER PRIMARY KEY column then
the key is a unique integer.  The key for a row can be accessed in
a SELECT statement or used in a WHERE or ORDER BY clause using any
of the names "ROWID", "OID", or "_ROWID_".</p>

<p>When you do an insert into a table that does not have an INTEGER PRIMARY
KEY column, or if the table does have an INTEGER PRIMARY KEY but the value
for that column is not specified in the VALUES clause of the insert, then
the key is automatically generated.  You can find the value of the key
for the most recent INSERT statement using the
<b>sqlite_last_insert_rowid</b> API function.</p>

<h4>3.2 The number of rows that changed</h4>

<p>The <b>sqlite_changes</b> API function returns the number of rows
that have been inserted, deleted, or modified since the database was
last quiescent.  A "quiescent" database is one in which there are
no outstanding calls to <b>sqlite_exec</b> and no VMs created by
<b>sqlite_compile</b> that have not been finalized by <b>sqlite_finalize</b>.
In common usage, <b>sqlite_changes</b> returns the number
︙︙
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696

a WHERE clause) by dropping the table then recreating it.  
This is much faster than deleting the elements of the table individually.
But it also means that the value returned from <b>sqlite_changes</b>
will be zero regardless of the number of elements that were originally
in the table.  If an accurate count of the number of elements deleted
is necessary, use "<b>DELETE FROM table WHERE 1</b>" instead.</p>

<h3>3.3 Querying into memory obtained from malloc()</h3>

<p>The <b>sqlite_get_table</b> function is a wrapper around
<b>sqlite_exec</b> that collects all the information from successive
callbacks and writes it into memory obtained from malloc().  This
is a convenience function that allows the application to get the
entire result of a database query with a single function call.</p>









|








673
674
675
676
677
678
679
680
681
682
683
684
685
686
687

a WHERE clause) by dropping the table then recreating it.  
This is much faster than deleting the elements of the table individually.
But it also means that the value returned from <b>sqlite_changes</b>
will be zero regardless of the number of elements that were originally
in the table.  If an accurate count of the number of elements deleted
is necessary, use "<b>DELETE FROM table WHERE 1</b>" instead.</p>

<h4>3.3 Querying into memory obtained from malloc()</h4>

<p>The <b>sqlite_get_table</b> function is a wrapper around
<b>sqlite_exec</b> that collects all the information from successive
callbacks and writes it into memory obtained from malloc().  This
is a convenience function that allows the application to get the
entire result of a database query with a single function call.</p>
︙︙
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791

to <b>sqlite_free_table</b> when the table is no longer needed.
It is safe to call <b>sqlite_free_table</b> with a NULL pointer such
as would be returned if the result set is empty.</p>

<p>The <b>sqlite_get_table</b> routine returns the same integer
result code as <b>sqlite_exec</b>.</p>

<h3>3.4 Interrupting an SQLite operation</h3>

<p>The <b>sqlite_interrupt</b> function can be called from a
different thread or from a signal handler to cause the current database
operation to exit at its first opportunity.  When this happens,
the <b>sqlite_exec</b> routine (or the equivalent) that started
the database operation will return SQLITE_INTERRUPT.</p>

<h3>3.5 Testing for a complete SQL statement</h3>

<p>The next interface routine to SQLite is a convenience function used
to test whether or not a string forms a complete SQL statement.
If the <b>sqlite_complete</b> function returns true when its input
is a string, then the argument forms a complete SQL statement.
There are no guarantees that the syntax of that statement is correct,
but we at least know the statement is complete.  If <b>sqlite_complete</b>








|







|








760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782

to <b>sqlite_free_table</b> when the table is no longer needed.
It is safe to call <b>sqlite_free_table</b> with a NULL pointer such
as would be returned if the result set is empty.</p>

<p>The <b>sqlite_get_table</b> routine returns the same integer
result code as <b>sqlite_exec</b>.</p>

<h4>3.4 Interrupting an SQLite operation</h4>

<p>The <b>sqlite_interrupt</b> function can be called from a
different thread or from a signal handler to cause the current database
operation to exit at its first opportunity.  When this happens,
the <b>sqlite_exec</b> routine (or the equivalent) that started
the database operation will return SQLITE_INTERRUPT.</p>

<h4>3.5 Testing for a complete SQL statement</h4>

<p>The next interface routine to SQLite is a convenience function used
to test whether or not a string forms a complete SQL statement.
If the <b>sqlite_complete</b> function returns true when its input
is a string, then the argument forms a complete SQL statement.
There are no guarantees that the syntax of that statement is correct,
but we at least know the statement is complete.  If <b>sqlite_complete</b>
︙︙
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840

line of input is received, <b>sqlite</b> calls <b>sqlite_complete</b>
on all input in its buffer.  If <b>sqlite_complete</b> returns true, 
then <b>sqlite_exec</b> is called and the input buffer is reset.  If
<b>sqlite_complete</b> returns false, then the prompt is changed to
the continuation prompt and another line of text is read and added to
the input buffer.</p>

<h3>3.6 Library version string</h3>

<p>The SQLite library exports the string constant named
<b>sqlite_version</b> which contains the version number of the
library.  The header file contains a macro SQLITE_VERSION
with the same information.  If desired, a program can compare
the SQLITE_VERSION macro against the <b>sqlite_version</b>
string constant to verify that the version number of the
header file and the library match.</p> 

<h3>3.7 Library character encoding</h3>

<p>By default, SQLite assumes that all data uses a fixed-size
8-bit character (iso8859).  But if you give the --enable-utf8 option
to the configure script, then the library assumes UTF-8 variable
sized characters.  This makes a difference for the LIKE and GLOB
operators and the LENGTH() and SUBSTR() functions.  The static
string <b>sqlite_encoding</b> will be set to either "UTF-8" or
"iso8859" to indicate how the library was compiled.  In addition,
the <b>sqlite.h</b> header file will define one of the
macros <b>SQLITE_UTF8</b> or <b>SQLITE_ISO8859</b>, as appropriate.</p>

<p>Note that the character encoding mechanism used by SQLite cannot
be changed at run-time.  This is a compile-time option only.  The
<b>sqlite_encoding</b> character string just tells you how the library
was compiled.</p>

<h3>3.8 Changing the library's response to locked files</h3>

<p>The <b>sqlite_busy_handler</b> procedure can be used to register
a busy callback with an open SQLite database.  The busy callback will
be invoked whenever SQLite tries to access a database that is locked.
The callback will typically do some other useful work, or perhaps sleep,
in order to give the lock a chance to clear.  If the callback returns
non-zero, then SQLite tries again to access the database and the cycle








|









|
















|








790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831

line of input is received, <b>sqlite</b> calls <b>sqlite_complete</b>
on all input in its buffer.  If <b>sqlite_complete</b> returns true, 
then <b>sqlite_exec</b> is called and the input buffer is reset.  If
<b>sqlite_complete</b> returns false, then the prompt is changed to
the continuation prompt and another line of text is read and added to
the input buffer.</p>

<h4>3.6 Library version string</h4>

<p>The SQLite library exports the string constant named
<b>sqlite_version</b> which contains the version number of the
library.  The header file contains a macro SQLITE_VERSION
with the same information.  If desired, a program can compare
the SQLITE_VERSION macro against the <b>sqlite_version</b>
string constant to verify that the version number of the
header file and the library match.</p> 

<h4>3.7 Library character encoding</h4>

<p>By default, SQLite assumes that all data uses a fixed-size
8-bit character (iso8859).  But if you give the --enable-utf8 option
to the configure script, then the library assumes UTF-8 variable
sized characters.  This makes a difference for the LIKE and GLOB
operators and the LENGTH() and SUBSTR() functions.  The static
string <b>sqlite_encoding</b> will be set to either "UTF-8" or
"iso8859" to indicate how the library was compiled.  In addition,
the <b>sqlite.h</b> header file will define one of the
macros <b>SQLITE_UTF8</b> or <b>SQLITE_ISO8859</b>, as appropriate.</p>

<p>Note that the character encoding mechanism used by SQLite cannot
be changed at run-time.  This is a compile-time option only.  The
<b>sqlite_encoding</b> character string just tells you how the library
was compiled.</p>

<h4>3.8 Changing the library's response to locked files</h4>

<p>The <b>sqlite_busy_handler</b> procedure can be used to register
a busy callback with an open SQLite database.  The busy callback will
be invoked whenever SQLite tries to access a database that is locked.
The callback will typically do some other useful work, or perhaps sleep,
in order to give the lock a chance to clear.  If the callback returns
non-zero, then SQLite tries again to access the database and the cycle
︙︙
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870

The first argument to <b>sqlite_busy_timeout</b> is a pointer to
an open SQLite database and the second argument is a number of milliseconds.
After <b>sqlite_busy_timeout</b> has been executed, the SQLite library
will wait for the lock to clear for at least the number of milliseconds 
specified before it returns SQLITE_BUSY.  Specifying zero milliseconds for
the timeout restores the default behavior.</p>

<h3>3.9 Using the <tt>_printf()</tt> wrapper functions</h3>

<p>The four utility functions</p>

<p>
<ul>
<li><b>sqlite_exec_printf()</b></li>
<li><b>sqlite_exec_vprintf()</b></li>








|








847
848
849
850
851
852
853
854
855
856
857
858
859
860
861

The first argument to <b>sqlite_busy_timeout</b> is a pointer to
an open SQLite database and the second argument is a number of milliseconds.
After <b>sqlite_busy_timeout</b> has been executed, the SQLite library
will wait for the lock to clear for at least the number of milliseconds 
specified before it returns SQLITE_BUSY.  Specifying zero milliseconds for
the timeout restores the default behavior.</p>

<h4>3.9 Using the <tt>_printf()</tt> wrapper functions</h4>

<p>The four utility functions</p>

<p>
<ul>
<li><b>sqlite_exec_printf()</b></li>
<li><b>sqlite_exec_vprintf()</b></li>
︙︙
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991

from malloc() and returns a pointer to the malloced buffer.  
<b>sqlite_mprintf()</b> also understands the %q and %Q extensions described
above.  The <b>sqlite_vmprintf()</b> is a varargs version of the same
routine.  The string pointer that these routines return should be freed
by passing it to <b>sqlite_freemem()</b>.
</p>

<h3>3.10 Performing background jobs during large queries </h2>

<p>The <b>sqlite_progress_handler()</b> routine can be used to register a
callback routine with an SQLite database to be invoked periodically during long
running calls to <b>sqlite_exec()</b>, <b>sqlite_step()</b> and the various
wrapper functions.
</p>









|








968
969
970
971
972
973
974
975
976
977
978
979
980
981
982

from malloc() and returns a pointer to the malloced buffer.  
<b>sqlite_mprintf()</b> also understands the %q and %Q extensions described
above.  The <b>sqlite_vmprintf()</b> is a varargs version of the same
routine.  The string pointer that these routines return should be freed
by passing it to <b>sqlite_freemem()</b>.
</p>

<h4>3.10 Performing background jobs during large queries</h3>

<p>The <b>sqlite_progress_handler()</b> routine can be used to register a
callback routine with an SQLite database to be invoked periodically during long
running calls to <b>sqlite_exec()</b>, <b>sqlite_step()</b> and the various
wrapper functions.
</p>
︙︙
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016

is difficult to schedule background operations based on virtual machine
operations. Instead, it is recommended that a callback be scheduled relatively
frequently (say every 1000 instructions) and external timer routines used to
determine whether or not background jobs need to be run.  
</p>

<a name="cfunc">
<h2>4.0 Adding New SQL Functions</h2>

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

<blockquote><pre>








|








993
994
995
996
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007

is difficult to schedule background operations based on virtual machine
operations. Instead, it is recommended that a callback be scheduled relatively
frequently (say every 1000 instructions) and external timer routines used to
determine whether or not background jobs need to be run.  
</p>

<a name="cfunc">
<h3>4.0 Adding New SQL Functions</h3>

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

<blockquote><pre>
︙︙
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099

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

<h2>5.0 Multi-Threading And SQLite</h2>

<p>
If SQLite is compiled with the THREADSAFE preprocessor macro set to 1,
then it is safe to use SQLite from two or more threads of the same process
at the same time.  But each thread should have its own <b>sqlite*</b>
pointer returned from <b>sqlite_open</b>.  It is never safe for two
or more threads to access the same <b>sqlite*</b> pointer at the same time.








|








1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090

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

<h3>5.0 Multi-Threading And SQLite</h3>

<p>
If SQLite is compiled with the THREADSAFE preprocessor macro set to 1,
then it is safe to use SQLite from two or more threads of the same process
at the same time.  But each thread should have its own <b>sqlite*</b>
pointer returned from <b>sqlite_open</b>.  It is never safe for two
or more threads to access the same <b>sqlite*</b> pointer at the same time.
︙︙
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132


<p>
Under Unix, an <b>sqlite*</b> pointer should not be carried across a
<b>fork()</b> system call into the child process.  The child process
should open its own copy of the database after the <b>fork()</b>.
</p>

<h2>6.0 Usage Examples</h2>

<p>For examples of how the SQLite C/C++ interface can be used,
refer to the source code for the <b>sqlite</b> program in the
file <b>src/shell.c</b> of the source tree.
Additional information about sqlite is available at
<a href="sqlite.html">sqlite.html</a>.
See also the sources to the Tcl interface for SQLite in
the source file <b>src/tclsqlite.c</b>.</p>
}

puts {
<p><hr /></p>
<p><a href="index.html"><img src="/goback.jpg" border=0 />
Back to the SQLite Home Page</a>
</p>

</body></html>}








|









|
<
<
<
<
<
<
<

1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116









<p>
Under Unix, an <b>sqlite*</b> pointer should not be carried across a
<b>fork()</b> system call into the child process.  The child process
should open its own copy of the database after the <b>fork()</b>.
</p>

<h3>6.0 Usage Examples</h3>

<p>For examples of how the SQLite C/C++ interface can be used,
refer to the source code for the <b>sqlite</b> program in the
file <b>src/shell.c</b> of the source tree.
Additional information about sqlite is available at
<a href="sqlite.html">sqlite.html</a>.
See also the sources to the Tcl interface for SQLite in
the source file <b>src/tclsqlite.c</b>.</p>
}
footer $rcsid
Changes to www/changes.tcl. 
1
2
3


4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

#
# Run this script to generated a changes.html output file
#


puts {<html>
<head>
  <title>SQLite Change Log</title>
</head>
<body bgcolor="white">
<h1 align="center">Recent Changes To SQLite</h1>

<p>
This file provides a high-level summary of changes to SQLite.
For more detail, refer the the checkin logs generated by
CVS at
<a href="http://www.sqlite.org/cvstrac/timeline">
http:/www.sqlite.org/cvstrac/timeline</a>.
</p>

<DL>




>
>
|
<
<
<
<
<
<

|








1
2
3
4
5
6






7
8
9
10
11
12
13
14
15

#
# Run this script to generated a changes.html output file
#
source common.tcl
header {SQLite changes}
puts {






<p>
This page provides a high-level summary of changes to SQLite.
For more detail, refer the the checkin logs generated by
CVS at
<a href="http://www.sqlite.org/cvstrac/timeline">
http:/www.sqlite.org/cvstrac/timeline</a>.
</p>

<DL>
︙︙
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105



chng {2000 May 29} {
<li>Initial Public Release of Alpha code</li>
}

puts {
</DL>
<p><hr /></p>
<p><a href="index.html"><img src="/goback.jpg" border=0 />
Back to the SQLite Home Page</a>
</p>

</body></html>}









<
<
<
<
|
<
>

1089
1090
1091
1092
1093
1094
1095




1096

1097


chng {2000 May 29} {
<li>Initial Public Release of Alpha code</li>
}

puts {
</DL>




}

footer {$Id:}
Added www/common.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

# This file contains TCL procedures used to generate standard parts of
# web pages.
#

proc header {txt} {
  puts "<html><head><title>$txt</title></head>"
  puts \
{<body bgcolor="white" link="#50695f" vlink="#508896">
<table width="100%" border="0">
<tr><td valign="top"><img src="sqlite.gif"></td>
<td width="100%"></td>
<td valign="bottom">
<ul>
<li><a href="http://www.sqlite.org/cvstrac/tktnew">bugs</a></li>
<li><a href="changes.html">changes</a></li>
<li><a href="download.html">download</a></li>
<li><a href="docs.html">documentation</a></li>
</ul>
</td>
<td width="10"></td>
<td valign="bottom">
<ul>
<li><a href="index.html">home</a></li>
<li><a href="faq.html">faq</a></li>
<li><a href="http://www.sqlite.org/cvstrac/timeline">timeline</a></li>
<li><a href="http://www.sqlite.org/cvstrac/wiki">wiki</a></li>
</ul>
</td>
</tr></table>
<table width="100%">
<tr><td bgcolor="#80a796"></td></tr>
</table>}
}

proc footer {{rcsid {}}} {
  puts {
<table width="100%">
<tr><td bgcolor="#80a796"></td></tr>
</table>}
  set date [lrange $rcsid 3 4]
  if {$date!=""} {
    puts "<small><i>This page last modified on $date</i></small>"
  }
  puts {</body></html>}
}
Changes to www/conflict.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

#
# Run this Tcl script to generate the constraint.html file.
#
set rcsid {$Id: conflict.tcl,v 1.2 2002/02/03 00:56:11 drh Exp $ }

puts {<html>
<head>
  <title>Constraint Conflict Resolution in SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>
Constraint Conflict Resolution in SQLite
</h1>}
puts "<p align=center>
(This page was last modified on [lrange $rcsid 3 4] UTC)
</p>"

puts {
<h2>Introduction</h2>


<p>
In most SQL databases, if you have a UNIQUE constraint on
a table and you try to do an UPDATE or INSERT that violates
that constraint, the database will aborts the operation in
progress, back out any prior changes associated with that 
one UPDATE or INSERT command, and return an error.
This is the default behavior of SQLite.
Beginning with version 2.3.0, though, SQLite allows you to
define alternative ways for dealing with constraint violations.
This article describes those alternatives and how to use them.
</p>

<h2>Conflict Resolution Algorithms</h2>




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

<
>




|
|
|








1
2
3
4
5






6





7

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

#
# Run this Tcl script to generate the constraint.html file.
#
set rcsid {$Id: conflict.tcl,v 1.3 2004/05/31 15:06:30 drh Exp $ }
source common.tcl






header {Constraint Conflict Resolution in SQLite}





puts {

<h1>Constraint Conflict Resolution in SQLite</h1>

<p>
In most SQL databases, if you have a UNIQUE constraint on
a table and you try to do an UPDATE or INSERT that violates
the constraint, the database will abort the operation in
progress, back out any prior changes associated with
UPDATE or INSERT command, and return an error.
This is the default behavior of SQLite.
Beginning with version 2.3.0, though, SQLite allows you to
define alternative ways for dealing with constraint violations.
This article describes those alternatives and how to use them.
</p>

<h2>Conflict Resolution Algorithms</h2>
︙︙
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109

1000 records would get inserted.  In MySQL, some subset of the
records that appeared before the malformed record would be inserted
but the rest would not.  Neither behavior is espeically helpful.
What you really want is to use the IGNORE algorithm to insert
all but the malformed record.</p>

}

puts {
<p><hr /></p>
<p><a href="index.html"><img src="/goback.jpg" border=0 />
Back to the SQLite Home Page</a>
</p>

</body></html>}








|
<
<
<
<
<
<
<

84
85
86
87
88
89
90
91








1000 records would get inserted.  In MySQL, some subset of the
records that appeared before the malformed record would be inserted
but the rest would not.  Neither behavior is espeically helpful.
What you really want is to use the IGNORE algorithm to insert
all but the malformed record.</p>

}
footer $rcsid
Added www/copyright-release.html. 




























































































































































































































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

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

<html>
<body bgcolor="white">
<h1 align="center">
Copyright Release for<br>
Contributions To SQLite
</h1>

<p>
SQLite is software that implements an embeddable SQL database engine.
SQLite is available for free download from http://www.sqlite.org/.
The principal author and maintainer of SQLite has disclaimed all
copyright interest in his contributions to SQLite
and thus released his contributions into the public domain.
In order to keep the SQLite software unencumbered by copyright
claims, the principal author asks others who may from time to
time contribute changes and enhancements to likewise disclaim
their own individual copyright interest.
</p>

<p>
Because the SQLite software found at http://www.sqlite.org/ is in the
public domain, anyone is free to download the SQLite software
from that website, make changes to the software, use, distribute,
or sell the modified software, under either the original name or
under some new name, without any need to obtain permission, pay
royalties, acknowledge the original source of the software, or
in any other way compensate, identify, or notify the original authors.  
Nobody is in any way compelled to contribute their SQLite changes and 
enhancements back to the SQLite website.  This document concerns
only changes and enhancements to SQLite that are intentionally and
deliberately contributed back to the SQLite website.  
</p>

<p>
For the purposes of this document, "SQLite software" shall mean any
computer source code, documentation, makefiles, test scripts, or
other information that is published on the SQLite website, 
http://www.sqlite.org/.  Precompiled binaries are excluded from
the definition of "SQLite software" in this document because the
process of compiling the software may introduce information from
outside sources which is not properly a part of SQLite.
</p>

<p>
The header comments on the SQLite source files exhort the reader to
share freely and to never take more than one gives.
In the spirit of that exhortation I make the following declarations:
</p>

<ol>
<li><p>
I dedicate to the public domain 
any and all copyright interest in the SQLite software that
was publicly available on the SQLite website (http://www.sqlite.org/) prior
to the date of the signature below and any changes or enhancements to
the SQLite software 
that I may cause to be published on that website in the future.
I make this dedication for the benefit of the public at large and
to the detriment of my heirs and successors.  I intend this
dedication to be an overt act of relinquishment in perpetuity of
all present and future rights to the SQLite software under copyright
law.
</p></li>

<li><p>
To the best of my knowledge and belief, the changes and enhancements that
I have contributed to SQLite are either originally written by me
or are derived from prior works which I have verified are also
in the public domain and are not subject to claims of copyright
by other parties.
</p></li>

<li><p>
To the best of my knowledge and belief, no individual, business, organization,
government, or other entity has any copyright interest
in the SQLite software as it existed on the
SQLite website as of the date on the signature line below.
</p></li>

<li><p>
I agree never to publish any additional information
to the SQLite website (by CVS, email, scp, FTP, or any other means) unless
that information is an original work of authorship by me or is derived from 
prior published versions of SQLite.
I agree never to copy and paste code into the SQLite code base from
other sources.
I agree never to publish on the SQLite website any information that
would violate a law or breach a contract.
</p></li>
</ol>

<p>
<table width="100%" cellpadding="0" cellspacing="0">
<tr>
<td width="60%" valign="top">
Signature:
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
</td><td valign="top" align="left">
Date:
</td></tr>
<td colspan=2>
Name (printed):
</td>
</tr>
</table>
</body>
</html>
Added www/copyright-release.pdf.

cannot compute difference between binary files

Changes to www/datatypes.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

#
# Run this script to generated a datatypes.html output file
#
set rcsid {$Id: datatypes.tcl,v 1.6 2003/12/18 14:19:41 drh Exp $}


puts {<html>
<head>
  <title>Datatypes In SQLite</title>
</head>
<body bgcolor="white">
<h1 align="center">
Datatypes In SQLite
</h1>
}
puts "<p align=center>
(This page was last modified on [lrange $rcsid 3 4] UTC)
</p>"

puts {<h2>1.0 &nbsp; Typelessness</h2>
<p>
SQLite is "typeless".  This means that you can store any
kind of data you want in any column of any table, regardless of the
declared datatype of that column.  
(See the one exception to this rule in section 2.0 below.)
This behavior is a feature, not
a bug.  A database is suppose to store and retrieve data and it 




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








1
2
3
4
5
6
7





8

9




10
11
12
13
14
15
16
17

#
# Run this script to generated a datatypes.html output file
#
set rcsid {$Id: datatypes.tcl,v 1.7 2004/05/31 15:06:30 drh Exp $}
source common.tcl
header {Datatypes In SQLite version 2}
puts {





<h2>Datatypes In SQLite Version 2</h2>






<h3>1.0 &nbsp; Typelessness</h3>
<p>
SQLite is "typeless".  This means that you can store any
kind of data you want in any column of any table, regardless of the
declared datatype of that column.  
(See the one exception to this rule in section 2.0 below.)
This behavior is a feature, not
a bug.  A database is suppose to store and retrieve data and it
︙︙
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89

</pre></blockquote>

<p>
And so forth.  Basically any sequence of names optionally followed by 
one or two signed integers in parentheses will do.
</p>

<h2>2.0 &nbsp; The INTEGER PRIMARY KEY</h2>

<p>
One exception to the typelessness of SQLite is a column whose type
is INTEGER PRIMARY KEY.  (And you must use "INTEGER" not "INT".
A column of type INT PRIMARY KEY is typeless just like any other.)
INTEGER PRIMARY KEY columns must contain a 32-bit signed integer.  Any
attempt to insert non-integer data will result in an error.








|








66
67
68
69
70
71
72
73
74
75
76
77
78
79
80

</pre></blockquote>

<p>
And so forth.  Basically any sequence of names optionally followed by 
one or two signed integers in parentheses will do.
</p>

<h3>2.0 &nbsp; The INTEGER PRIMARY KEY</h3>

<p>
One exception to the typelessness of SQLite is a column whose type
is INTEGER PRIMARY KEY.  (And you must use "INTEGER" not "INT".
A column of type INT PRIMARY KEY is typeless just like any other.)
INTEGER PRIMARY KEY columns must contain a 32-bit signed integer.  Any
attempt to insert non-integer data will result in an error.
︙︙
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111

largest key is 2147483647, then the column will be filled with a
random integer.  Either way, the INTEGER PRIMARY KEY column will be
assigned a unique integer.  You can retrieve this integer using
the <b>sqlite_last_insert_rowid()</b> API function or using the
<b>last_insert_rowid()</b> SQL function in a subsequent SELECT statement.
</p>

<h2>3.0 &nbsp; Comparison and Sort Order</h2>

<p>
SQLite is typeless for the purpose of deciding what data is allowed
to be stored in a column.  But some notion of type comes into play
when sorting and comparing data.  For these purposes, a column or
an expression can be one of two types: <b>numeric</b> and <b>text</b>.
The sort or comparison may give different results depending on which








|








88
89
90
91
92
93
94
95
96
97
98
99
100
101
102

largest key is 2147483647, then the column will be filled with a
random integer.  Either way, the INTEGER PRIMARY KEY column will be
assigned a unique integer.  You can retrieve this integer using
the <b>sqlite_last_insert_rowid()</b> API function or using the
<b>last_insert_rowid()</b> SQL function in a subsequent SELECT statement.
</p>

<h3>3.0 &nbsp; Comparison and Sort Order</h3>

<p>
SQLite is typeless for the purpose of deciding what data is allowed
to be stored in a column.  But some notion of type comes into play
when sorting and comparing data.  For these purposes, a column or
an expression can be one of two types: <b>numeric</b> and <b>text</b>.
The sort or comparison may give different results depending on which
︙︙
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154


<p>
For both text and numeric values, NULL sorts before any other value.
A comparison of any value against NULL using operators like "&lt;" or
"&gt;=" is always false.
</p>

<h2>4.0 &nbsp; How SQLite Determines Datatypes</h2>

<p>
For SQLite version 2.6.3 and earlier, all values used the numeric datatype.
The text datatype appears in version 2.7.0 and later.  In the sequel it
is assumed that you are using version 2.7.0 or later of SQLite.
</p>









|








131
132
133
134
135
136
137
138
139
140
141
142
143
144
145


<p>
For both text and numeric values, NULL sorts before any other value.
A comparison of any value against NULL using operators like "&lt;" or
"&gt;=" is always false.
</p>

<h3>4.0 &nbsp; How SQLite Determines Datatypes</h3>

<p>
For SQLite version 2.6.3 and earlier, all values used the numeric datatype.
The text datatype appears in version 2.7.0 and later.  In the sequel it
is assumed that you are using version 2.7.0 or later of SQLite.
</p>
︙︙
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203

text.</p>

<p>If none of the strings above occur anywhere in the type declaration,
then the datatype is numeric.  Note in particular that the datatype for columns
with an empty type declaration is numeric.
</p>

<h2>5.0 &nbsp; Examples</h2>

<p>
Consider the following two command sequences:
</p>

<blockquote><pre>
CREATE TABLE t1(a INTEGER UNIQUE);        CREATE TABLE t2(b TEXT UNIQUE);








|








180
181
182
183
184
185
186
187
188
189
190
191
192
193
194

text.</p>

<p>If none of the strings above occur anywhere in the type declaration,
then the datatype is numeric.  Note in particular that the datatype for columns
with an empty type declaration is numeric.
</p>

<h3>5.0 &nbsp; Examples</h3>

<p>
Consider the following two command sequences:
</p>

<blockquote><pre>
CREATE TABLE t1(a INTEGER UNIQUE);        CREATE TABLE t2(b TEXT UNIQUE);
︙︙
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259


<p>
The SELECT statement on the left returns a single row since '0' and '0.0'
are treated as numbers and are therefore indistinct.  But the SELECT 
statement on the right returns two rows since 0 and 0.0 are treated
a strings which are different.</p>
}

puts {
<p><hr /></p>
<p><a href="index.html"><img src="/goback.jpg" border=0 />
Back to the SQLite Home Page</a>
</p>

</body></html>}








|
<
<
<
<
<
<
<

236
237
238
239
240
241
242
243









<p>
The SELECT statement on the left returns a single row since '0' and '0.0'
are treated as numbers and are therefore indistinct.  But the SELECT 
statement on the right returns two rows since 0 and 0.0 are treated
a strings which are different.</p>
}
footer $rcsid
Added www/docs.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

# This script generates the "docs.html" page that describes various
# sources of documentation available for SQLite.
#
set rcsid {$Id: docs.tcl,v 1.1 2004/05/31 15:06:30 drh Exp $}
source common.tcl
header {SQLite Documentation}
puts {
<h2>Available Documentation</h2>
<table width="100%" cellpadding="5">
}

proc doc {name url desc} {
  puts {<tr><td valign="top" align="right">}
  regsub -all { +} $name {\&nbsp;} name
  puts "<a href=\"$url\">$name</a></td>"
  puts {<td width="10"></td>}
  puts {<td align="top" align="left">}
  puts $desc
  puts {</td></tr>}
}

doc {SQL Syntax} {lang.html} {
  This document describes the SQL language that is understood by
  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 {Tcl API} {tclsqlite.html} {
  A description of the TCL interface bindings for SQLite.
}

doc {Version 2 DataTypes } {datatypes.html} {
  A description of how SQLite version 2 handles SQL datatypes.
}

doc {Release History} {changes.html} {
  A chronology of SQLite releases going back to version 1.0.0
}

doc {Null Handling} {nulls.html} {
  Different SQL database engines handle NULLs in different ways.  The
  SQL standards are ambiguous.  This document describes how SQLite handles
  NULLs in comparison with other SQL database engines.
}

doc {Copyright} {copyright.html} {
  SQLite is in the public domain.  This document describes what that means
  and the implications for contributors.
}

doc {Unsupported SQL} {omitted.html} {
  This page describes features of SQL that SQLite does not support.
}

doc {Speed Comparison} {speed.html} {
  The speed of version 2.7.6 of SQLite is compared against PostgreSQL and
  MySQL.
}

doc {Architecture} {arch.html} {
  An architectural overview of the SQLite library, useful for those who want
  to hack the code.
}

doc {VDBE Tutorial} {vdbe.html} {
  The VDBE is the subsystem within SQLite that does the actual work of
  executing SQL statements.  This page describes the principles of operation
  for the VDBE in SQLite version 2.7.  This is essential reading for anyone
  who want to modify the SQLite sources.
}

doc {VDBE Opcodes} {opcode.html} {
  This document is an automatically generated description of the various
  opcodes that the VDBE understands.  Programmers can use this document as
  a reference to better understand the output of EXPLAIN listings from
  SQLite.
}

puts {</table>}
footer $rcsid
Changes to www/download.tcl. 
1
2
3
4


5
6
7
8
9
10
11
12
13
14
15
16

#
# Run this TCL script to generate HTML for the download.html file.
#
set rcsid {$Id: download.tcl,v 1.6 2002/08/15 13:45:17 drh Exp $}



puts {<html>
<head><title>SQLite Download Page</title></head>
<body bgcolor=white>
<h1 align=center>SQLite Download Page</h1>
<table width="100%" cellpadding="5">
}


proc Product {file desc} {
  if {![file exists $file]} return
  set size [file size $file]




|
>
>

|
<
<
|








1
2
3
4
5
6
7
8


9
10
11
12
13
14
15
16

#
# Run this TCL script to generate HTML for the download.html file.
#
set rcsid {$Id: download.tcl,v 1.7 2004/05/31 15:06:30 drh Exp $}
source common.tcl
header {SQLite Download Page}

puts {


<h2>SQLite Download Page</h1>
<table width="100%" cellpadding="5">
}


proc Product {file desc} {
  if {![file exists $file]} return
  set size [file size $file]
︙︙
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107


foreach name [lsort -dict -decreasing [glob -nocomplain sqlite-*.tar.gz]] {
  regexp {sqlite-(.*)\.tar\.gz} $name match vers
  Product $name "
      Version $vers of the source tree including all documentation.
  "
}

puts {
</table>
<p><hr /></p>
<p>
<a href="index.html"><img src="/goback.jpg" border=0 />
Back to the SQLite home page</a>
</p>

</body></html>}








|
<
<
<
<
<
<
<
<
<

91
92
93
94
95
96
97
98











foreach name [lsort -dict -decreasing [glob -nocomplain sqlite-*.tar.gz]] {
  regexp {sqlite-(.*)\.tar\.gz} $name match vers
  Product $name "
      Version $vers of the source tree including all documentation.
  "
}
footer $rcsid
Changes to www/faq.tcl. 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

#
# Run this script to generated a faq.html output file
#
set rcsid {$Id: faq.tcl,v 1.23 2003/05/29 17:43:08 drh Exp $}

puts {<html>
<head>
  <title>SQLite Frequently Asked Questions</title>
</head>
<body bgcolor="white">
<h1 align="center">Frequently Asked Questions</h1>
}
puts "<p align=center>
(This page was last modified on [lrange $rcsid 3 4] UTC)
</p>"


set cnt 1
proc faq {question answer} {
  set ::faq($::cnt) [list [string trim $question] [string trim $answer]]
  incr ::cnt
}





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








1
2
3
4
5


6








7
8
9
10
11
12
13

#
# Run this script to generated a faq.html output file
#
set rcsid {$Id: faq.tcl,v 1.24 2004/05/31 15:06:30 drh Exp $}
source common.tcl


header {SQLite Frequently Asked Questions</title>}









set cnt 1
proc faq {question answer} {
  set ::faq($::cnt) [list [string trim $question] [string trim $answer]]
  incr ::cnt
}
︙︙
428
429
430
431
432
433
434


435
436
437
438





439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455

  <p>Yes.  SQLite is in the public domain.  No claim of ownership is made
  to any part of the code.  You can do anything you want with it.</p>
}

# End of questions and answers.
#############



puts {<DL COMPACT>}
for {set i 1} {$i<$cnt} {incr i} {
  puts "  <DT><A HREF=\"#q$i\">($i)</A></DT>"
  puts "  <DD>[lindex $faq($i) 0]</DD>"





}
puts {</DL>}

for {set i 1} {$i<$cnt} {incr i} {
  puts "<A NAME=\"q$i\"><HR />"
  puts "<P><B>($i) [lindex $faq($i) 0]</B></P>\n"
  puts "<BLOCKQUOTE>[lindex $faq($i) 1]</BLOCKQUOTE></LI>\n"
}

puts {
</OL>
<p><hr /></p>
<p><a href="index.html"><img src="/goback.jpg" border=0 />
Back to the SQLite Home Page</a>
</p>

</body></html>}








>
>
|
|
|
|
>
>
>
>
>

|







|
<
<
<
<
<
|
<

418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445





446


  <p>Yes.  SQLite is in the public domain.  No claim of ownership is made
  to any part of the code.  You can do anything you want with it.</p>
}

# End of questions and answers.
#############

puts {<h2>Frequently Asked Questions</h2>}

# puts {<DL COMPACT>}
# for {set i 1} {$i<$cnt} {incr i} {
#   puts "  <DT><A HREF=\"#q$i\">($i)</A></DT>"
#   puts "  <DD>[lindex $faq($i) 0]</DD>"
# }
# puts {</DL>}
puts {<OL>}
for {set i 1} {$i<$cnt} {incr i} {
  puts "<li><a href=\"#q$i\">[lindex $faq($i) 0]</a></li>"
}
puts {</OL>}

for {set i 1} {$i<$cnt} {incr i} {
  puts "<A NAME=\"q$i\"><HR />"
  puts "<P><B>($i) [lindex $faq($i) 0]</B></P>\n"
  puts "<BLOCKQUOTE>[lindex $faq($i) 1]</BLOCKQUOTE></LI>\n"
}

puts {</OL>}





footer $rcsid
Changes to www/fileformat.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

#
# Run this script to generated a fileformat.html output file
#
set rcsid {$Id: fileformat.tcl,v 1.11 2003/11/25 23:48:57 drh Exp $}


puts {<html>
<head>
  <title>SQLite Database File Format</title>
</head>
<body bgcolor="white">
<h1 align="center">
SQLite Database File Format
</h1>
}
puts "<p align=center>
(This page was last modified on [lrange $rcsid 3 4] UTC)



</p>"

puts {
<h2>1.0 &nbsp; Layers</h2>

<p>
SQLite is implemented in layers.
(See the <a href="arch.html">architecture description</a>.)
The format of database files is determined by three different
layers in the architecture.
</p>

<ul>
<li>The <b>schema</b> layer implemented by the VDBE.</li>
<li>The <b>b-tree</b> layer implemented by btree.c</li>
<li>The <b>pager</b> layer implemented by pager.c</li>
</ul>

<p>
We wil describe each layer beginning with the bottom (pager)
layer and working upwards.
</p>

<h2>2.0 &nbsp; The Pager Layer</h2>

<p>
An SQLite database consists of
"pages" of data.  Each page is 1024 bytes in size.
Pages are numbered beginning with 1.
A page number of 0 is used to indicate "no such page" in the
B-Tree and Schema layers.




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

<
|



















|








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

#
# Run this script to generated a fileformat.html output file
#
set rcsid {$Id: fileformat.tcl,v 1.12 2004/05/31 15:06:30 drh Exp $}
source common.tcl
header {SQLite Database File Format (Version 2)}
puts {





<h2>SQLite 2.X Database File Format</h2>


<p>

This document describes the disk file format for SQLite versions 2.1
through 2.8.  SQLite version 3.0 and following uses a very different
format which is described separately.
</p>


<h3>1.0 &nbsp; Layers</h3>

<p>
SQLite is implemented in layers.
(See the <a href="arch.html">architecture description</a>.)
The format of database files is determined by three different
layers in the architecture.
</p>

<ul>
<li>The <b>schema</b> layer implemented by the VDBE.</li>
<li>The <b>b-tree</b> layer implemented by btree.c</li>
<li>The <b>pager</b> layer implemented by pager.c</li>
</ul>

<p>
We wil describe each layer beginning with the bottom (pager)
layer and working upwards.
</p>

<h3>2.0 &nbsp; The Pager Layer</h3>

<p>
An SQLite database consists of
"pages" of data.  Each page is 1024 bytes in size.
Pages are numbered beginning with 1.
A page number of 0 is used to indicate "no such page" in the
B-Tree and Schema layers.
︙︙
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168

   <li>4 byte page number</li>
   <li>1024 bytes of original data for the page</li>
   <li>4 byte checksum</li>
   </ul>
</li>
</ul>

<h2>3.0 &nbsp; The B-Tree Layer</h2>

<p>
The B-Tree layer builds on top of the pager layer to implement
one or more separate b-trees all in the same disk file.  The
algorithms used are taken from Knuth's <i>The Art Of Computer
Programming.</i></p>









|








150
151
152
153
154
155
156
157
158
159
160
161
162
163
164

   <li>4 byte page number</li>
   <li>1024 bytes of original data for the page</li>
   <li>4 byte checksum</li>
   </ul>
</li>
</ul>

<h3>3.0 &nbsp; The B-Tree Layer</h3>

<p>
The B-Tree layer builds on top of the pager layer to implement
one or more separate b-trees all in the same disk file.  The
algorithms used are taken from Knuth's <i>The Art Of Computer
Programming.</i></p>
︙︙
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216

Each b-tree is identified by its root page, which never changes.
Child pages of the b-tree may change as entries are added and removed
and pages split and combine.  But the root page always stays the same.
The b-tree itself does not record which pages are root pages and which
are not.  That information is handled entirely at the schema layer.
</p>

<h3>3.1 &nbsp; B-Tree Page 1 Details</h3>

<p>
Page 1 begins with the following 48-byte string:
</p>

<blockquote><pre>
** This file contains an SQLite 2.1 database **








|








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

Each b-tree is identified by its root page, which never changes.
Child pages of the b-tree may change as entries are added and removed
and pages split and combine.  But the root page always stays the same.
The b-tree itself does not record which pages are root pages and which
are not.  That information is handled entirely at the schema layer.
</p>

<h4>3.1 &nbsp; B-Tree Page 1 Details</h4>

<p>
Page 1 begins with the following 48-byte string:
</p>

<blockquote><pre>
** This file contains an SQLite 2.1 database **
︙︙
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285

<li>4 byte integer used to determine the byte-order</li>
<li>4 byte integer which is the first page of the freelist</li>
<li>4 byte integer which is the number of pages on the freelist</li>
<li>36 bytes of meta-data arranged as nine 4-byte integers</li>
<li>928 bytes of unused space</li>
</ul>

<h3>3.2 &nbsp; Structure Of A Single B-Tree Page</h3>

<p>
Conceptually, a b-tree page contains N database entries and N+1 pointers
to other b-tree pages.
</p>

<blockquote>








|








267
268
269
270
271
272
273
274
275
276
277
278
279
280
281

<li>4 byte integer used to determine the byte-order</li>
<li>4 byte integer which is the first page of the freelist</li>
<li>4 byte integer which is the number of pages on the freelist</li>
<li>36 bytes of meta-data arranged as nine 4-byte integers</li>
<li>928 bytes of unused space</li>
</ul>

<h4>3.2 &nbsp; Structure Of A Single B-Tree Page</h4>

<p>
Conceptually, a b-tree page contains N database entries and N+1 pointers
to other b-tree pages.
</p>

<blockquote>
︙︙
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512

When b-tree entries are deleted, the space used by their cells is converted
into freeblocks.  Adjacent freeblocks are merged, but the page can still
become fragmented.  The b-tree code will occasionally try to defragment
the page by moving all cells to the beginning and constructing a single
freeblock at the end to take up all remaining space.
</p>

<h3>3.3 &nbsp; The B-Tree Free Page List</h3>

<p>
When information is removed from an SQLite database such that one or
more pages are no longer needed, those pages are added to a list of
free pages so that they can be reused later when new information is
added.  This subsection describes the structure of this freelist.
</p>








|








494
495
496
497
498
499
500
501
502
503
504
505
506
507
508

When b-tree entries are deleted, the space used by their cells is converted
into freeblocks.  Adjacent freeblocks are merged, but the page can still
become fragmented.  The b-tree code will occasionally try to defragment
the page by moving all cells to the beginning and constructing a single
freeblock at the end to take up all remaining space.
</p>

<h4>3.3 &nbsp; The B-Tree Free Page List</h4>

<p>
When information is removed from an SQLite database such that one or
more pages are no longer needed, those pages are added to a list of
free pages so that they can be reused later when new information is
added.  This subsection describes the structure of this freelist.
</p>
︙︙
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601

into the database file.
Again, the page contains no real data so it is not necessary to record the
content of that page.  By reducing the amount of disk I/O required,
these two optimizations allow some database operations
to go four to six times faster than they would otherwise.
</p>

<h2>4.0 &nbsp; The Schema Layer</h2>

<p>
The schema layer implements an SQL database on top of one or more
b-trees and keeps track of the root page numbers for all b-trees.
Where the b-tree layer provides only unformatted data storage with
a unique key, the schema layer allows each entry to contain multiple
columns.  The schema layer also allows indices and non-unique key values.
</p>

<p>
The schema layer implements two separate data storage abstractions:
tables and indices.  Each table and each index uses its own b-tree
but they use the b-tree capabilities in different ways.  For a table,
the b-tree key is a unique 4-byte integer and the b-tree data is the
content of the table row, encoded so that columns can be separately
extracted.  For indices, the b-tree key varies in size depending on the
size of the fields being indexed and the b-tree data is empty.
</p>

<h3>4.1 &nbsp; SQL Table Implementation Details</h3>

<p>Each row of an SQL table is stored in a single b-tree entry.
The b-tree key is a 4-byte big-endian integer that is the ROWID
or INTEGER PRIMARY KEY for that table row.
The key is stored in a big-endian format so
that keys will sort in numerical order using memcmp() function.</p>









|



















|








563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597

into the database file.
Again, the page contains no real data so it is not necessary to record the
content of that page.  By reducing the amount of disk I/O required,
these two optimizations allow some database operations
to go four to six times faster than they would otherwise.
</p>

<h3>4.0 &nbsp; The Schema Layer</h3>

<p>
The schema layer implements an SQL database on top of one or more
b-trees and keeps track of the root page numbers for all b-trees.
Where the b-tree layer provides only unformatted data storage with
a unique key, the schema layer allows each entry to contain multiple
columns.  The schema layer also allows indices and non-unique key values.
</p>

<p>
The schema layer implements two separate data storage abstractions:
tables and indices.  Each table and each index uses its own b-tree
but they use the b-tree capabilities in different ways.  For a table,
the b-tree key is a unique 4-byte integer and the b-tree data is the
content of the table row, encoded so that columns can be separately
extracted.  For indices, the b-tree key varies in size depending on the
size of the fields being indexed and the b-tree data is empty.
</p>

<h4>4.1 &nbsp; SQL Table Implementation Details</h4>

<p>Each row of an SQL table is stored in a single b-tree entry.
The b-tree key is a 4-byte big-endian integer that is the ROWID
or INTEGER PRIMARY KEY for that table row.
The key is stored in a big-endian format so
that keys will sort in numerical order using memcmp() function.</p>
︙︙
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669

When an SQL statement attempts to read the INTEGER PRIMARY KEY,
the 4-byte b-tree key is read rather than information out of the
b-tree data.  But there is still an Offset associated with the
INTEGER PRIMARY KEY, just like any other column.  But the Value
associated with that offset is always NULL.
</p>

<h3>4.2 &nbsp; SQL Index Implementation Details</h3>

<p>
SQL indices are implement using a b-tree in which the key is used
but the data is always empty.  The purpose of an index is to map
one or more column values into the ROWID for the table entry that
contains those column values.
</p>








|








651
652
653
654
655
656
657
658
659
660
661
662
663
664
665

When an SQL statement attempts to read the INTEGER PRIMARY KEY,
the 4-byte b-tree key is read rather than information out of the
b-tree data.  But there is still an Offset associated with the
INTEGER PRIMARY KEY, just like any other column.  But the Value
associated with that offset is always NULL.
</p>

<h4>4.2 &nbsp; SQL Index Implementation Details</h4>

<p>
SQL indices are implement using a b-tree in which the key is used
but the data is always empty.  The purpose of an index is to map
one or more column values into the ROWID for the table entry that
contains those column values.
</p>
︙︙
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695

additional information on this encoding.)  Numbers are also nul-terminated.
Text values consists of the character 'c' followed by a copy of the
text string and a nul-terminator.  These encoding rules result in
NULLs being sorted first, followed by numerical values in numerical
order, followed by text values in lexigraphical order.
</p>

<h3>4.4 &nbsp; SQL Schema Storage And Root B-Tree Page Numbers</h3>

<p>
The database schema is stored in the database in a special tabled named
"sqlite_master" and which always has a root b-tree page number of 2.
This table contains the original CREATE TABLE,
CREATE INDEX, CREATE VIEW, and CREATE TRIGGER statements used to define
the database to begin with.  Whenever an SQLite database is opened,








|








677
678
679
680
681
682
683
684
685
686
687
688
689
690
691

additional information on this encoding.)  Numbers are also nul-terminated.
Text values consists of the character 'c' followed by a copy of the
text string and a nul-terminator.  These encoding rules result in
NULLs being sorted first, followed by numerical values in numerical
order, followed by text values in lexigraphical order.
</p>

<h4>4.4 &nbsp; SQL Schema Storage And Root B-Tree Page Numbers</h4>

<p>
The database schema is stored in the database in a special tabled named
"sqlite_master" and which always has a root b-tree page number of 2.
This table contains the original CREATE TABLE,
CREATE INDEX, CREATE VIEW, and CREATE TRIGGER statements used to define
the database to begin with.  Whenever an SQLite database is opened,
︙︙
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730

is the same structure as the main database file.  The schema table
for the temporary tables is stored on page 2 just as in the main
database.  But the schema table for the temporary database named
"sqlite_temp_master" instead of "sqlite_master".  Other than the
name change, it works exactly the same.
</p>

<h3>4.4 &nbsp; Schema Version Numbering And Other Meta-Information</h3>

<p>
The nine 32-bit integers that are stored beginning at byte offset
60 of Page 1 in the b-tree layer are passed up into the schema layer
and used for versioning and configuration information.  The meaning
of the first four integers is shown below.  The other five are currently
unused.








|








712
713
714
715
716
717
718
719
720
721
722
723
724
725
726

is the same structure as the main database file.  The schema table
for the temporary tables is stored on page 2 just as in the main
database.  But the schema table for the temporary database named
"sqlite_temp_master" instead of "sqlite_master".  Other than the
name change, it works exactly the same.
</p>

<h4>4.4 &nbsp; Schema Version Numbering And Other Meta-Information</h4>

<p>
The nine 32-bit integers that are stored beginning at byte offset
60 of Page 1 in the b-tree layer are passed up into the schema layer
and used for versioning and configuration information.  The meaning
of the first four integers is shown below.  The other five are currently
unused.
︙︙
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797

A value of 1 corresponds to a SYNCHRONOUS setting of OFF.  In other
words, SQLite does not pause to wait for journal data to reach the disk
surface before overwriting pages of the database.  A value of 2 corresponds
to a SYNCHRONOUS setting of NORMAL.  A value of 3 corresponds to a
SYNCHRONOUS setting of FULL. If the value is 0, that means it has not
been initialized so the default synchronous setting of NORMAL is used.
</p>

}

puts {
<p><hr /></p>
<p><a href="index.html"><img src="/goback.jpg" border=0 />
Back to the SQLite Home Page</a>
</p>

</body></html>}








|
<
|
<
<
<
<
<
<
<

777
778
779
780
781
782
783
784

785








A value of 1 corresponds to a SYNCHRONOUS setting of OFF.  In other
words, SQLite does not pause to wait for journal data to reach the disk
surface before overwriting pages of the database.  A value of 2 corresponds
to a SYNCHRONOUS setting of NORMAL.  A value of 3 corresponds to a
SYNCHRONOUS setting of FULL. If the value is 0, that means it has not
been initialized so the default synchronous setting of NORMAL is used.
</p>
}

footer $rcsid
Changes to www/formatchng.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

#
# Run this Tcl script to generate the formatchng.html file.
#
set rcsid {$Id: formatchng.tcl,v 1.9 2003/06/29 16:11:13 drh Exp $ }


puts {<html>
<head>
  <title>File Format Changes in SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>
File Format Changes in SQLite
</h1>}
puts "<p align=center>
(This page was last modified on [lrange $rcsid 3 4] UTC)
</p>"

puts {
<p>
From time to time, enhancements or bug fixes require a change to
the underlying file format for SQLite.  When this happens and you
want to upgrade your library, you must convert the contents of your
databases into a portable ASCII representation using the old version
of the library then reload the data using the new version of the
library.




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

<








1
2
3
4
5
6
7





8




9

10
11
12
13
14
15
16

#
# Run this Tcl script to generate the formatchng.html file.
#
set rcsid {$Id: formatchng.tcl,v 1.10 2004/05/31 15:06:30 drh Exp $ }
source common.tcl
header {File Format Changes in SQLite}
puts {





<h2>File Format Changes in SQLite</h2>






<p>
From time to time, enhancements or bug fixes require a change to
the underlying file format for SQLite.  When this happens and you
want to upgrade your library, you must convert the contents of your
databases into a portable ASCII representation using the old version
of the library then reload the data using the new version of the
library.
︙︙
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

  using version 2.8.0 or later that crashes with an incomplete
  transaction, then you try to examine the database using version 2.7.6 or
  earlier.  The 2.7.6 code will not be able to read the journal file
  and thus will not be able to rollback the incomplete transaction
  to restore the database.</p>
  </td>
</tr>










</table>
</blockquote>

<p>
To perform a database reload, have ready versions of the
<b>sqlite</b> command-line utility for both the old and new
version of SQLite.  Call these two executables "<b>sqlite-old</b>"
and "<b>sqlite-new</b>".  Suppose the name of your old database
is "<b>old.db</b>" and you want to create a new database with
the same information named "<b>new.db</b>".  The command to do
this is as follows:
</p>

<blockquote>
  echo .dump | sqlite-old old.db | sqlite-new new.db
</blockquote>
}

puts {
<p><hr /></p>
<p><a href="index.html"><img src="/goback.jpg" border=0 />
Back to the SQLite Home Page</a>
</p>

</body></html>}








>
>
>
>
>
>
>
>
>
>

















|
<
<
<
<
<
<
<

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








  using version 2.8.0 or later that crashes with an incomplete
  transaction, then you try to examine the database using version 2.7.6 or
  earlier.  The 2.7.6 code will not be able to read the journal file
  and thus will not be able to rollback the incomplete transaction
  to restore the database.</p>
  </td>
</tr>
<tr>
  <td valign="top">2.8.13 to 3.0.0</td>
  <td valign="top">(pending)</td>
  <td><p>Version 3.0.0 is a major upgrade for SQLite that incorporates
  support for UTF-16, BLOBs, and a more compact encoding that results
  in database files that are typically 25% to 35% smaller.  The new file
  format is radically different and completely incompatible with the
  version 2 file format.</p>
  </td>
</tr>
</table>
</blockquote>

<p>
To perform a database reload, have ready versions of the
<b>sqlite</b> command-line utility for both the old and new
version of SQLite.  Call these two executables "<b>sqlite-old</b>"
and "<b>sqlite-new</b>".  Suppose the name of your old database
is "<b>old.db</b>" and you want to create a new database with
the same information named "<b>new.db</b>".  The command to do
this is as follows:
</p>

<blockquote>
  echo .dump | sqlite-old old.db | sqlite-new new.db
</blockquote>
}
footer $rcsid
Changes to www/index.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

#
# Run this TCL script to generate HTML for the index.html file.
#
set rcsid {$Id: index.tcl,v 1.82 2004/04/23 17:04:45 drh Exp $}

puts {<html>
<head><title>SQLite: An Embeddable SQL Database Engine</title></head>
<body bgcolor=white>
<h1 align=center>SQLite<br>An Embeddable SQL Database Engine</h1>
<p align=center>}
puts "This page was last modified on [lrange $rcsid 3 4] UTC<br>"
set vers [lindex $argv 0]
puts "The latest SQLite version is <b>$vers</b>"
puts " created on [exec cat last_change] UTC"

puts {</p>}

puts {<h2>Introduction</h2>

<p>SQLite is a C library that implements an embeddable SQL database engine.

Programs that link with the SQLite library can have SQL database
access without running a separate RDBMS process.
The distribution comes with a standalone command-line
access program (<a href="sqlite.html">sqlite</a>) that can
be used to administer an SQLite database and which serves as
an example of how to use the SQLite library.</p>

<p>SQLite is <b>not</b> a client library used to connect to a
big database server.  SQLite <b>is</b> the server.  The SQLite
library reads and writes directly to and from the database files
on disk.</p>}

puts {
<table align="right" hspace="10" cellpadding=0 cellspacing=0 broder=0>
<tr><td align="right" bgcolor="#cacae4">
<table border="2" width="100%" cellspacing=0 cellpadding=5><tr><td align="left">
Quick Links:
<ul>
<li><a href="download.html">Download</a></li>
<li><a href="http://www.sqlite.org/cvstrac/timeline">Change Log</a></li>
<li><a href="http://www.sqlite.org/cvstrac/tktnew">Report a bug</a></li>
<li><a href="quickstart.html">Quick start</a></li>
</ul>
</td></tr></table>
</td></tr>
</table>
}

puts {<h2>Features</h2>

<p><ul>
<li>Implements most of SQL92.
    (<a href="omitted.html">Features not supported</a>)</li>
<li>A complete database (with multiple tables and indices) is
    stored in a single disk file.</li>
<li>ACID (Atomic, Consistent, Isolated, Durable) transactions.</li>
<li>Database files can be freely shared between machines with
    different byte orders.</li>
<li>Supports databases up to 2 terabytes (2^41 bytes) in size.</li>
<li>A zero-configuration database engine - no DBA needed.</li>
<li>Small memory footprint: less than 30K lines of C code.</li>

<li><a href="speed.html">Two times faster</a> than PostgreSQL and
    MySQL for many common operations.</li>
<li>Very simple 
<a href="c_interface.html">C/C++ interface</a> requires the use of only
three functions and one opaque structure.</li>
<li><a href="tclsqlite.html">TCL bindings</a> included.
    Bindings for many other languages 
    <a href="http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers">
    available separately.</a></li>
<li>Simple, well-commented source code.</li>
<li>Automated test suite provides over 90% code coverage.</li>
<li>Self-contained: no external dependencies.</li>
<li>Built and tested under Linux and Windows.</li>
<li>Sources are in the <a href="copyright.html">public domain</a>.
    Use for any purpose.</li>
</ul>
</p>
}

puts {<h2>Current Status</h2>

<p>A <a href="changes.html">Change Summary</a> is available on this
website.  You can also access a detailed
<a href="http://www.sqlite.org/cvstrac/timeline">change history</a>,
<a href="http://www.sqlite.org/cvstrac/rptview?rn=2">view open bugs</a>,
or
<a href="http://www.sqlite.org/cvstrac/tktnew">report new bugs</a>
at the
<a href="http://www.sqlite.org/cvstrac/">CVS server</a>.</p>

<p>Complete source code and precompiled binaries for the latest release are
<a href="download.html">available for download</a> on this site.  You
can also obtain the latest changes by anonymous CVS access:
<blockquote><pre>
cvs -d :pserver:anonymous@www.sqlite.org:/sqlite login
cvs -d :pserver:anonymous@www.sqlite.org:/sqlite checkout sqlite
</pre></blockquote>
When prompted for a password, enter "anonymous".
</p>

<p>
Whenever either of the first two digits in the version number
for SQLite change, it means that the underlying file format
has changed.  Usually these changes are backwards compatible.
See <a href="formatchng.html">formatchng.html</a>
for additional information.
</p>
}

puts {<h2>Documentation</h2>

<p>The following documentation is currently available:</p>

<p><ul>
<li>A <a href="quickstart.html">Quick Start</a> guide to using SQLite in
    5 minutes or less.</li>
<li><a href="faq.html">Frequently Asked Questions</a> are available online.</li>
<li>Information on the <a href="sqlite.html">sqlite</a>
    command-line utility.</li>
<li>SQLite is <a href="datatypes.html">typeless</a>.
<li>The <a href="lang.html">SQL Language</a> subset understood by SQLite.</li>
<li>The <a href="c_interface.html">C/C++ Interface</a>.</li>
<li>The <a href="nulls.html">NULL handling</a> in SQLite versus
    other SQL database engines.</li>
<li>The <a href="tclsqlite.html">Tcl Binding</a> to SQLite.</li>
<li>The <a href="arch.html">Architecture of the SQLite Library</a> describes
    how the library is put together.</li>
<li>A description of the <a href="opcode.html">virtual machine</a> that
    SQLite uses to access the database.</li>
<li>A description of the 
    <a href="fileformat.html">database file format</a> used by SQLite.
<li>A <a href="speed.html">speed comparison</a> between SQLite, PostgreSQL,
    and MySQL.</li>
<li>User-written documentation is available on the
    <a href="http://www.sqlite.org/cvstrac/wiki">SQLite Wiki</a>.  Please
    contribute if you can.</li>
</ul>
</p>

<p>The SQLite source code is 30% comment.  These comments are
another important source of information.  </p>





}






puts {
<a name="mailinglist" />
<h2>Mailing List</h2>
<p>A mailing list has been set up for discussion of
SQLite design issues or for asking questions about SQLite.
To subscribe send an email to
<a href="mailto:sqlite-users-subscribe@sqlite.org">
sqlite-users-subscribe@sqlite.org</a>.
If you would prefer to get digests rather than individual
emails, send a message to to
<a href="mailto:sqlite-users-digest-subscribe@sqlite.org">
sqlite-users-digest-subscribe@sqlite.org</a>.
For additional information about operating and using this
mailing list, send a message to
<a href="mailto:sqlite-users-help@sqlite.org">
sqlite-users-help@sqlite.org</a> and instructions will be
sent by to you by return email.
</p>
}

puts {<h2>Professional Support and Custom Modifications</h2>}

puts {
<p>
If you would like professional support for SQLite
or if you want custom modifications to SQLite performed by the
original author, these services are available for a modest fee.
For additional information visit
<a href="http://www.hwaci.com/sw/sqlite/support.html">
http://www.hwaci.com/sw/sqlite/support.html</a> or contact:</p>

<blockquote>
D. Richard Hipp <br />
Hwaci - Applied Software Research <br />
704.948.4565 <br />
<a href="mailto:drh@hwaci.com">drh@hwaci.com</a>
</blockquote>
}

puts {<h2>Building From Source</h2>}



puts {
<p>To build sqlite under Unix, just unwrap the tarball, create a separate
build directory, run configure from the build directory and then
type "make".  For example:</p>




<blockquote><pre>
$ tar xzf sqlite.tar.gz      <i> Unpacks into directory named "sqlite" </i>
$ mkdir bld                  <i> Create a separate build directory </i>
$ cd bld
$ ../sqlite/configure
$ make                       <i> Builds "sqlite" and "libsqlite.a" </i>
$ make test                  <i> Optional: run regression tests </i>
</pre></blockquote>

<p>If you prefer, you can also build by making whatever modifications
you desire to the file "Makefile.linux-gcc" and then executing that
makefile.  The latter method is used for all official development
and testing of SQLite and for building the precompiled
binaries found on this website.  Windows binaries are generated by
cross-compiling from Linux using <a href="http://www.mingw.org/">MinGW</a></p>
}

puts {<h2>Related Sites</h2>

<p>
For information bindings of SQLite to other programming languages
(Perl, Python, Ruby, PHP, etc.) and for a list of programs currently
using SQLite, visit the Wiki documentation at:
</p>

<blockquote>
<a href="http://www.sqlite.org/cvstrac/wiki">
http://www.sqlite.org/cvstrac/wiki</a>
</blockquote>
}

puts {
</body></html>}

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

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


<
|
<



<
|
>
|
|
<
|
<




|
<

<




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


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

>
>
>
|
|
<
<
<
<
<
<

<
|
|
<
<
<


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

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



#!/usr/bin/tclsh


source common.tcl
header {SQLite home page}
puts {








<table width="100%" border="0" cellspacing="5">
<tr>
<td width="50%" valign="top">
<h2>About SQLite</h2>
<p>
SQLite is a small C library that implements a 
self-contained, embeddable,
zero-configuration SQL database engine.





Features include:



</p>






<p><ul>









<li>ACID (Atomic, Consistent, Isolated, Durable) transactions.</li>

<li>A zero-configuration - no DBA needed.</li>

<li>Implements most of SQL92.
    (<a href="omitted.html">Features not supported</a>)</li>

<li>A complete database is stored in a single disk file.</li>

<li>Database files can be freely shared between machines with
    different byte orders.</li>
<li>Supports databases up to 2 terabytes (2^41 bytes) in size.</li>

<li>Small memory footprint: less than 30K lines of C code,
    less than 250KB code space (gcc on i486)</li>
<li><a href="speed.html">Faster</a> than other popular database
    engines for most common operations.</li>

<li>Simple, easy to use <a href="c_interface.html">API</a>.</li>

<li><a href="tclsqlite.html">TCL bindings</a> included.
    Bindings for many other languages 
    <a href="http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers">
    available separately.</a></li>
<li>Well-commented source code with over 90% test coverage.</li>

<li>Self-contained: no external dependencies.</li>

<li>Sources are in the <a href="copyright.html">public domain</a>.
    Use for any purpose.</li>
</ul>
</p>




































<p>
The SQLite distribution comes with a standalone command-line


access program (<a href="sqlite.html">sqlite</a>) that can

be used to administer an SQLite database and which serves as








an example of how to use the SQLite library.








</p>



</td>
<td width="1" bgcolor="#80a796"></td>
<td valign="top" width="50%">
<h2>News</h2>
}

proc newsitem {date title text} {
  puts "<h3>$date - $title</h3>"
  regsub -all "\n( *\n)+" $text "</p>\n\n<p>" txt
  puts "<p>$txt</p>"
  puts "<hr width=\"50%\">"
}



















newsitem {2004-May-31} {CVS Access Temporarily Suspended} {

  Anonymous access to the CVS repository will be suspended








  temporarily beginning on 2004-June-04







  so that the developers working on version 3.0 can have a free hand to

  implement features that a covered by a non-compete agreement.
  Access will be reenabled after the non-compete expires on 2004-June-17.
}





newsitem {2004-Apr-23} {Work Begins On SQLite Version 3} {
  Work has begun on version 3 of SQLite.  Version 3 is a major
  changes to both the C-language API and the underlying file format
  that will enable SQLite to better support internationalization.
  The first beta is schedule for release on 2004-July-01.








  Plans are to continue to support SQLite version 2.8 with
  bug fixes.  But all new development will occur in version 3.0.



}

puts {
</td></tr></table>





}





footer {$Id: index.tcl,v 1.83 2004/05/31 15:06:30 drh Exp $}
Changes to www/lang.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

#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: lang.tcl,v 1.67 2004/04/23 17:04:45 drh Exp $}


puts {<html>
<head>
  <title>Query Language Understood By SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>
SQL As Understood By SQLite
</h1>}
puts "<p align=center>
(This page was last modified on [lrange $rcsid 3 4] UTC)
</p>"

puts {
<p>The SQLite library understands most of the standard SQL
language.  But it does <a href="omitted.html">omit some features</a>
while at the same time
adding a few features of its own.  This document attempts to
describe percisely what parts of the SQL language SQLite does
and does not support.  A list of <a href="#keywords">keywords</a> is 
given at the end.</p>




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

<








1
2
3
4
5
6
7





8




9

10
11
12
13
14
15
16

#
# 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>
while at the same time
adding a few features of its own.  This document attempts to
describe percisely what parts of the SQL language SQLite does
and does not support.  A list of <a href="#keywords">keywords</a> is 
given at the end.</p>
︙︙
1837
1838
1839
1840
1841
1842
1843
1844
1845
1846
1847
1848
1849
1850

  *MAIN
  OID
  *ROWID
  *SQLITE_MASTER
  *SQLITE_TEMP_MASTER
}

puts {
<p><hr /></p>
<p><a href="index.html"><img src="/goback.jpg" border=0 />
Back to the SQLite Home Page</a>
</p>

</body></html>}








<
<
<
<
<
|
<

1828
1829
1830
1831
1832
1833
1834





1835


  *MAIN
  OID
  *ROWID
  *SQLITE_MASTER
  *SQLITE_TEMP_MASTER
}






footer $rcsid
Changes to www/nulls.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

#
# Run this script to generated a nulls.html output file
#
set rcsid {$Id: nulls.tcl,v 1.5 2003/11/08 12:07:01 drh Exp $}


puts {<html>
<head>
<title>NULL Handling In SQLite Versus Other Database Engines</title>
</head>
<body bgcolor="white">
<h1 align="center">
NULL Handling in SQLite Versus Other Database Engines
</h1>
}
puts "<p align=\"center\">
(This page was last modified on [lrange $rcsid 3 4] UTC)
</p>"

puts {
<p>
The goal is
to make SQLite handle NULLs in a standards-compliant way.
But the descriptions in the SQL standards on how to handle
NULLs seem ambiguous. 
It is not clear from the standards documents exactly how NULLs should
be handled in all circumstances.




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








1
2
3
4
5
6
7





8

9





10
11
12
13
14
15
16

#
# Run this script to generated a nulls.html output file
#
set rcsid {$Id: nulls.tcl,v 1.6 2004/05/31 15:06:30 drh Exp $}
source common.tcl
header {NULL Handling in SQLite}
puts {





<h2>NULL Handling in SQLite Versus Other Database Engines</h2>







<p>
The goal is
to make SQLite handle NULLs in a standards-compliant way.
But the descriptions in the SQL standards on how to handle
NULLs seem ambiguous. 
It is not clear from the standards documents exactly how NULLs should
be handled in all circumstances.
︙︙
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344


insert into t2 values(2,null);
insert into t2 values(3,null);
select * from t2;

drop table t1;
drop table t2;
</pre>

<p><hr /></p>
<p><a href="index.html"><img src="/goback.jpg" border=0 />
Back to the SQLite Home Page</a>
</p>
</body>
</html>
}









|
<
<
<
<
<
<
|
>

320
321
322
323
324
325
326
327






328
329

insert into t2 values(2,null);
insert into t2 values(3,null);
select * from t2;

drop table t1;
drop table t2;
</pre>
}







footer $rcsid
Changes to www/omitted.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

#
# Run this script to generated a omitted.html output file
#
set rcsid {$Id: omitted.tcl,v 1.5 2003/08/22 02:34:30 drh Exp $}

puts {<html>
<head>
  <title>SQL Features That SQLite Does Not Implement</title>
</head>
<body bgcolor="white">
<h1 align="center">

SQL Features That SQLite Does Not Implement
</h1>
}
puts "<p align=center>
(This page was last modified on [lrange $rcsid 3 4] UTC)
</p>"

puts {
<p>
Rather than try to list all the features of SQL92 that SQLite does
support, it is much easier to list those that it does not.
Unsupported features of SQL92 are shown below.</p>

<p>
The order of this list gives some hint as to when a feature might




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








1
2
3
4
5


6



7
8

9





10
11
12
13
14
15
16

#
# Run this script to generated a omitted.html output file
#
set rcsid {$Id: omitted.tcl,v 1.6 2004/05/31 15:06:30 drh Exp $}
source common.tcl


header {SQL Features That SQLite Does Not Implement}



puts {
<h2>SQL Features That SQLite Does Not Implement</h2>







<p>
Rather than try to list all the features of SQL92 that SQLite does
support, it is much easier to list those that it does not.
Unsupported features of SQL92 are shown below.</p>

<p>
The order of this list gives some hint as to when a feature might
︙︙
100
101
102
103
104
105
106
107
108
109
110
111
112



<p>
If you find other SQL92 features that SQLite does not support, please
add them to the Wiki page at 
<a href="http://www.sqlite.org/cvstrac/wiki?p=UnsupportedSql">
http://www.sqlite.org/cvstrac/wiki?p=Unsupported</a>
</p>
<p><hr /></p>
<p><a href="index.html"><img src="/goback.jpg" border=0 />
Back to the SQLite Home Page</a>
</p>

</body></html>}









<
<
<
<
|
<
>

90
91
92
93
94
95
96




97

98


<p>
If you find other SQL92 features that SQLite does not support, please
add them to the Wiki page at 
<a href="http://www.sqlite.org/cvstrac/wiki?p=UnsupportedSql">
http://www.sqlite.org/cvstrac/wiki?p=Unsupported</a>
</p>




}

footer $rcsid
Changes to www/opcode.tcl. 
1
2
3
4
5

6
7
8
9
10
11
12
13
14
15
16

17
18
19
20
21
22
23

#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: opcode.tcl,v 1.12 2003/06/29 16:11:13 drh Exp $}


puts {<html>
<head>
  <title>SQLite Virtual Machine Opcodes</title>
</head>
<body bgcolor=white>
<h1 align=center>
SQLite Virtual Machine Opcodes
</h1>}
puts "<p align=center>
(This page was last modified on [lrange $rcsid 3 4] UTC)
</p>"


set fd [open [lindex $argv 0] r]
set file [read $fd [file size [lindex $argv 0]]]
close $fd
set current_op {}
foreach line [split $file \n] {
  set line [string trim $line]




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








1
2
3
4
5
6
7





8




9
10
11
12
13
14
15
16

#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: opcode.tcl,v 1.13 2004/05/31 15:06:30 drh Exp $}
source common.tcl
header {SQLite Virtual Machine Opcodes}
puts {





<h2>SQLite Virtual Machine Opcodes</h2>




}

set fd [open [lindex $argv 0] r]
set file [read $fd [file size [lindex $argv 0]]]
close $fd
set current_op {}
foreach line [split $file \n] {
  set line [string trim $line]
︙︙
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56

  } else {
    append Opcode($current_op:text) \n$line
  }
}
unset file

puts {
<h2>Introduction</h2>

<p>In order to execute an SQL statement, the SQLite library first parses
the SQL, analyzes the statement, then generates a short program to execute
the statement.  The program is generated for a "virtual machine" implemented
by the SQLite library.  This document describes the operation of that
virtual machine.</p>









|








35
36
37
38
39
40
41
42
43
44
45
46
47
48
49

  } else {
    append Opcode($current_op:text) \n$line
  }
}
unset file

puts {
<h3>Introduction</h3>

<p>In order to execute an SQL statement, the SQLite library first parses
the SQL, analyzes the statement, then generates a short program to execute
the statement.  The program is generated for a "virtual machine" implemented
by the SQLite library.  This document describes the operation of that
virtual machine.</p>
︙︙
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157

values being extracted by the select.  The number of slots is the
same for all aggregators and is defined by the AggReset operation.
At any point in time a single aggregator is current or "has focus".
There are operations to read or write to memory slots of the aggregator
in focus.  There are also operations to change the focus aggregator
and to scan through all aggregators.</p>

<h2>Viewing Programs Generated By SQLite</h2>

<p>Every SQL statement that SQLite interprets results in a program
for the virtual machine.  But if you precede the SQL statement with
the keyword "EXPLAIN" the virtual machine will not execute the
program.  Instead, the instructions of the program will be returned
like a query result.  This feature is useful for debugging and
for learning how the virtual machine operates.</p>








|








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

values being extracted by the select.  The number of slots is the
same for all aggregators and is defined by the AggReset operation.
At any point in time a single aggregator is current or "has focus".
There are operations to read or write to memory slots of the aggregator
in focus.  There are also operations to change the focus aggregator
and to scan through all aggregators.</p>

<h3>Viewing Programs Generated By SQLite</h3>

<p>Every SQL statement that SQLite interprets results in a program
for the virtual machine.  But if you precede the SQL statement with
the keyword "EXPLAIN" the virtual machine will not execute the
program.  Instead, the instructions of the program will be returned
like a query result.  This feature is useful for debugging and
for learning how the virtual machine operates.</p>
︙︙
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

PRAGMA vdbe_trace=on;
</pre></blockquote>

<p>
You can turn tracing back off by entering a similar statement but
changing the value "on" to "off".</p>

<h2>The Opcodes</h2>
}

puts "<p>There are currently [llength $OpcodeList] opcodes defined by
the virtual machine."
puts {All currently defined opcodes are described in the table below.
This table was generated automatically by scanning the source code
from the file <b>vdbe.c</b>.</p>}

puts {
<p><table cellspacing="1" border="1" cellpadding="10">
<tr><th>Opcode&nbsp;Name</th><th>Description</th></tr>}
foreach op [lsort -dictionary $OpcodeList] {
  puts {<tr><td valign="top" align="center">}
  puts "<a name=\"$op\">$op</a>"
  puts "<td>[string trim $Opcode($op:text)]</td></tr>"
}
puts {</table></p>}

puts {
<p><hr /></p>
<p><a href="index.html"><img src="/goback.jpg" border=0 />
Back to the SQLite Home Page</a>
</p>

</body></html>}








|

















|
<
<
<
<
<
<
<

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








PRAGMA vdbe_trace=on;
</pre></blockquote>

<p>
You can turn tracing back off by entering a similar statement but
changing the value "on" to "off".</p>

<h3>The Opcodes</h3>
}

puts "<p>There are currently [llength $OpcodeList] opcodes defined by
the virtual machine."
puts {All currently defined opcodes are described in the table below.
This table was generated automatically by scanning the source code
from the file <b>vdbe.c</b>.</p>}

puts {
<p><table cellspacing="1" border="1" cellpadding="10">
<tr><th>Opcode&nbsp;Name</th><th>Description</th></tr>}
foreach op [lsort -dictionary $OpcodeList] {
  puts {<tr><td valign="top" align="center">}
  puts "<a name=\"$op\">$op</a>"
  puts "<td>[string trim $Opcode($op:text)]</td></tr>"
}
puts {</table></p>}
footer $rcsid
Changes to www/speed.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

#
# Run this Tcl script to generate the speed.html file.
#
set rcsid {$Id: speed.tcl,v 1.13 2003/06/29 16:11:13 drh Exp $ }



puts {<html>
<head>
  <title>Database Speed Comparison: SQLite versus PostgreSQL</title>
</head>
<body bgcolor=white>
<h1 align=center>
Database Speed Comparison
</h1>}
puts "<p align=center>
(This page was last modified on [lrange $rcsid 3 4] UTC)
</p>"

puts {
<h2>Executive Summary</h2>

<p>A series of tests were run to measure the relative performance of
SQLite 2.7.6, PostgreSQL 7.1.3, and MySQL 3.23.41.
The following are general
conclusions drawn from these experiments:
</p>





|
>
>

|
<
<
<
<
<
|
<
<
<
<

<
|








1
2
3
4
5
6
7
8





9




10

11
12
13
14
15
16
17
18

#
# Run this Tcl script to generate the speed.html file.
#
set rcsid {$Id: speed.tcl,v 1.14 2004/05/31 15:06:30 drh Exp $ }
source common.tcl
header {SQLite Database Speed Comparison}

puts {





<h2>Database Speed Comparison</h2>






<h3>Executive Summary</h3>

<p>A series of tests were run to measure the relative performance of
SQLite 2.7.6, PostgreSQL 7.1.3, and MySQL 3.23.41.
The following are general
conclusions drawn from these experiments:
</p>
︙︙
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71

</p></li>
<li><p>
  These tests are on a relatively small (approximately 14 megabyte) database.
  They do not measure how well the database engines scale to larger problems.
</p></li>
</ul>

<h2>Test Environment</h2>

<p>
The platform used for these tests is a 1.6GHz Athlon with 1GB or memory
and an IDE disk drive.  The operating system is RedHat Linux 7.2 with
a stock kernel.
</p>









|








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

</p></li>
<li><p>
  These tests are on a relatively small (approximately 14 megabyte) database.
  They do not measure how well the database engines scale to larger problems.
</p></li>
</ul>

<h3>Test Environment</h3>

<p>
The platform used for these tests is a 1.6GHz Athlon with 1GB or memory
and an IDE disk drive.  The operating system is RedHat Linux 7.2 with
a stock kernel.
</p>
︙︙
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142

operating system crash or an unexpected power failure could
damage the database.  Generally speaking, the synchronous SQLite
times are for comparison against PostgreSQL (which is also
synchronous) and the asynchronous SQLite times are for 
comparison against the asynchronous MySQL engine.
</p>

<h2>Test 1: 1000 INSERTs</h2>
<blockquote>
CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100));<br>
INSERT INTO t1 VALUES(1,13153,'thirteen thousand one hundred fifty three');<br>
INSERT INTO t1 VALUES(2,75560,'seventy five thousand five hundred sixty');<br>
<i>... 995 lines omitted</i><br>
INSERT INTO t1 VALUES(998,66289,'sixty six thousand two hundred eighty nine');<br>
INSERT INTO t1 VALUES(999,24322,'twenty four thousand three hundred twenty two');<br>








|








120
121
122
123
124
125
126
127
128
129
130
131
132
133
134

operating system crash or an unexpected power failure could
damage the database.  Generally speaking, the synchronous SQLite
times are for comparison against PostgreSQL (which is also
synchronous) and the asynchronous SQLite times are for 
comparison against the asynchronous MySQL engine.
</p>

<h3>Test 1: 1000 INSERTs</h3>
<blockquote>
CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100));<br>
INSERT INTO t1 VALUES(1,13153,'thirteen thousand one hundred fifty three');<br>
INSERT INTO t1 VALUES(2,75560,'seventy five thousand five hundred sixty');<br>
<i>... 995 lines omitted</i><br>
INSERT INTO t1 VALUES(998,66289,'sixty six thousand two hundred eighty nine');<br>
INSERT INTO t1 VALUES(999,24322,'twenty four thousand three hundred twenty two');<br>
︙︙
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172

version of SQLite is still nearly as fast as MySQL.  Notice how much slower
the synchronous version is, however.  SQLite calls <b>fsync()</b> after 
each synchronous transaction to make sure that all data is safely on
the disk surface before continuing.  For most of the 13 seconds in the
synchronous test, SQLite was sitting idle waiting on disk I/O to complete.</p>


<h2>Test 2: 25000 INSERTs in a transaction</h2>
<blockquote>
BEGIN;<br>
CREATE TABLE t2(a INTEGER, b INTEGER, c VARCHAR(100));<br>
INSERT INTO t2 VALUES(1,59672,'fifty nine thousand six hundred seventy two');<br>
<i>... 24997 lines omitted</i><br>
INSERT INTO t2 VALUES(24999,89569,'eighty nine thousand five hundred sixty nine');<br>
INSERT INTO t2 VALUES(25000,94666,'ninety four thousand six hundred sixty six');<br>








|








150
151
152
153
154
155
156
157
158
159
160
161
162
163
164

version of SQLite is still nearly as fast as MySQL.  Notice how much slower
the synchronous version is, however.  SQLite calls <b>fsync()</b> after 
each synchronous transaction to make sure that all data is safely on
the disk surface before continuing.  For most of the 13 seconds in the
synchronous test, SQLite was sitting idle waiting on disk I/O to complete.</p>


<h3>Test 2: 25000 INSERTs in a transaction</h3>
<blockquote>
BEGIN;<br>
CREATE TABLE t2(a INTEGER, b INTEGER, c VARCHAR(100));<br>
INSERT INTO t2 VALUES(1,59672,'fifty nine thousand six hundred seventy two');<br>
<i>... 24997 lines omitted</i><br>
INSERT INTO t2 VALUES(24999,89569,'eighty nine thousand five hundred sixty nine');<br>
INSERT INTO t2 VALUES(25000,94666,'ninety four thousand six hundred sixty six');<br>
︙︙
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197

When all the INSERTs are put in a transaction, SQLite no longer has to
close and reopen the database or invalidate its cache between each statement.
It also does not
have to do any fsync()s until the very end.  When unshackled in
this way, SQLite is much faster than either PostgreSQL and MySQL.
</p>

<h2>Test 3: 25000 INSERTs into an indexed table</h2>
<blockquote>
BEGIN;<br>
CREATE TABLE t3(a INTEGER, b INTEGER, c VARCHAR(100));<br>
CREATE INDEX i3 ON t3(c);<br>
<i>... 24998 lines omitted</i><br>
INSERT INTO t3 VALUES(24999,88509,'eighty eight thousand five hundred nine');<br>
INSERT INTO t3 VALUES(25000,84791,'eighty four thousand seven hundred ninety one');<br>








|








175
176
177
178
179
180
181
182
183
184
185
186
187
188
189

When all the INSERTs are put in a transaction, SQLite no longer has to
close and reopen the database or invalidate its cache between each statement.
It also does not
have to do any fsync()s until the very end.  When unshackled in
this way, SQLite is much faster than either PostgreSQL and MySQL.
</p>

<h3>Test 3: 25000 INSERTs into an indexed table</h3>
<blockquote>
BEGIN;<br>
CREATE TABLE t3(a INTEGER, b INTEGER, c VARCHAR(100));<br>
CREATE INDEX i3 ON t3(c);<br>
<i>... 24998 lines omitted</i><br>
INSERT INTO t3 VALUES(24999,88509,'eighty eight thousand five hundred nine');<br>
INSERT INTO t3 VALUES(25000,84791,'eighty four thousand seven hundred ninety one');<br>
︙︙
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221

<p>
There were reports that SQLite did not perform as well on an indexed table.
This test was recently added to disprove those rumors.  It is true that
SQLite is not as fast at creating new index entries as the other engines
(see Test 6 below) but its overall speed is still better.
</p>

<h2>Test 4: 100 SELECTs without an index</h2>
<blockquote>
BEGIN;<br>
SELECT count(*), avg(b) FROM t2 WHERE b>=0 AND b<1000;<br>
SELECT count(*), avg(b) FROM t2 WHERE b>=100 AND b<1100;<br>
<i>... 96 lines omitted</i><br>
SELECT count(*), avg(b) FROM t2 WHERE b>=9800 AND b<10800;<br>
SELECT count(*), avg(b) FROM t2 WHERE b>=9900 AND b<10900;<br>








|








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

<p>
There were reports that SQLite did not perform as well on an indexed table.
This test was recently added to disprove those rumors.  It is true that
SQLite is not as fast at creating new index entries as the other engines
(see Test 6 below) but its overall speed is still better.
</p>

<h3>Test 4: 100 SELECTs without an index</h3>
<blockquote>
BEGIN;<br>
SELECT count(*), avg(b) FROM t2 WHERE b>=0 AND b<1000;<br>
SELECT count(*), avg(b) FROM t2 WHERE b>=100 AND b<1100;<br>
<i>... 96 lines omitted</i><br>
SELECT count(*), avg(b) FROM t2 WHERE b>=9800 AND b<10800;<br>
SELECT count(*), avg(b) FROM t2 WHERE b>=9900 AND b<10900;<br>
︙︙
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247

This test does 100 queries on a 25000 entry table without an index,
thus requiring a full table scan.   Prior versions of SQLite used to
be slower than PostgreSQL and MySQL on this test, but recent performance
enhancements have increased its speed so that it is now the fastest
of the group.
</p>

<h2>Test 5: 100 SELECTs on a string comparison</h2>
<blockquote>
BEGIN;<br>
SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%one%';<br>
SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%two%';<br>
<i>... 96 lines omitted</i><br>
SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%ninety nine%';<br>
SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%one hundred%';<br>








|








225
226
227
228
229
230
231
232
233
234
235
236
237
238
239

This test does 100 queries on a 25000 entry table without an index,
thus requiring a full table scan.   Prior versions of SQLite used to
be slower than PostgreSQL and MySQL on this test, but recent performance
enhancements have increased its speed so that it is now the fastest
of the group.
</p>

<h3>Test 5: 100 SELECTs on a string comparison</h3>
<blockquote>
BEGIN;<br>
SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%one%';<br>
SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%two%';<br>
<i>... 96 lines omitted</i><br>
SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%ninety nine%';<br>
SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%one hundred%';<br>
︙︙
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

<p>
This test still does 100 full table scans but it uses
uses string comparisons instead of numerical comparisions.
SQLite is over three times faster than PostgreSQL here and about 30%
faster than MySQL.
</p>

<h2>Test 6: Creating an index</h2>
<blockquote>
CREATE INDEX i2a ON t2(a);<br>CREATE INDEX i2b ON t2(b);
</blockquote><table border=0 cellpadding=0 cellspacing=0>
<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;0.381</td></tr>
<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;0.318</td></tr>
<tr><td>SQLite 2.7.6:</td><td align="right">&nbsp;&nbsp;&nbsp;0.777</td></tr>
<tr><td>SQLite 2.7.6 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;0.659</td></tr>
</table>

<p>
SQLite is slower at creating new indices.  This is not a huge problem
(since new indices are not created very often) but it is something that
is being worked on.  Hopefully, future versions of SQLite will do better
here.
</p>

<h2>Test 7: 5000 SELECTs with an index</h2>
<blockquote>
SELECT count(*), avg(b) FROM t2 WHERE b>=0 AND b<100;<br>
SELECT count(*), avg(b) FROM t2 WHERE b>=100 AND b<200;<br>
SELECT count(*), avg(b) FROM t2 WHERE b>=200 AND b<300;<br>
<i>... 4994 lines omitted</i><br>
SELECT count(*), avg(b) FROM t2 WHERE b>=499700 AND b<499800;<br>
SELECT count(*), avg(b) FROM t2 WHERE b>=499800 AND b<499900;<br>








|
















|








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

<p>
This test still does 100 full table scans but it uses
uses string comparisons instead of numerical comparisions.
SQLite is over three times faster than PostgreSQL here and about 30%
faster than MySQL.
</p>

<h3>Test 6: Creating an index</h3>
<blockquote>
CREATE INDEX i2a ON t2(a);<br>CREATE INDEX i2b ON t2(b);
</blockquote><table border=0 cellpadding=0 cellspacing=0>
<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;0.381</td></tr>
<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;0.318</td></tr>
<tr><td>SQLite 2.7.6:</td><td align="right">&nbsp;&nbsp;&nbsp;0.777</td></tr>
<tr><td>SQLite 2.7.6 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;0.659</td></tr>
</table>

<p>
SQLite is slower at creating new indices.  This is not a huge problem
(since new indices are not created very often) but it is something that
is being worked on.  Hopefully, future versions of SQLite will do better
here.
</p>

<h3>Test 7: 5000 SELECTs with an index</h3>
<blockquote>
SELECT count(*), avg(b) FROM t2 WHERE b>=0 AND b<100;<br>
SELECT count(*), avg(b) FROM t2 WHERE b>=100 AND b<200;<br>
SELECT count(*), avg(b) FROM t2 WHERE b>=200 AND b<300;<br>
<i>... 4994 lines omitted</i><br>
SELECT count(*), avg(b) FROM t2 WHERE b>=499700 AND b<499800;<br>
SELECT count(*), avg(b) FROM t2 WHERE b>=499800 AND b<499900;<br>
︙︙
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310

</table>

<p>
All three database engines run faster when they have indices to work with.
But SQLite is still the fastest.
</p>

<h2>Test 8: 1000 UPDATEs without an index</h2>
<blockquote>
BEGIN;<br>
UPDATE t1 SET b=b*2 WHERE a>=0 AND a<10;<br>
UPDATE t1 SET b=b*2 WHERE a>=10 AND a<20;<br>
<i>... 996 lines omitted</i><br>
UPDATE t1 SET b=b*2 WHERE a>=9980 AND a<9990;<br>
UPDATE t1 SET b=b*2 WHERE a>=9990 AND a<10000;<br>








|








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

</table>

<p>
All three database engines run faster when they have indices to work with.
But SQLite is still the fastest.
</p>

<h3>Test 8: 1000 UPDATEs without an index</h3>
<blockquote>
BEGIN;<br>
UPDATE t1 SET b=b*2 WHERE a>=0 AND a<10;<br>
UPDATE t1 SET b=b*2 WHERE a>=10 AND a<20;<br>
<i>... 996 lines omitted</i><br>
UPDATE t1 SET b=b*2 WHERE a>=9980 AND a<9990;<br>
UPDATE t1 SET b=b*2 WHERE a>=9990 AND a<10000;<br>
︙︙
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335

For this particular UPDATE test, MySQL is consistently
five or ten times
slower than PostgreSQL and SQLite.  I do not know why.  MySQL is
normally a very fast engine.  Perhaps this problem has been addressed
in later versions of MySQL.
</p>

<h2>Test 9: 25000 UPDATEs with an index</h2>
<blockquote>
BEGIN;<br>
UPDATE t2 SET b=468026 WHERE a=1;<br>
UPDATE t2 SET b=121928 WHERE a=2;<br>
<i>... 24996 lines omitted</i><br>
UPDATE t2 SET b=35065 WHERE a=24999;<br>
UPDATE t2 SET b=347393 WHERE a=25000;<br>








|








313
314
315
316
317
318
319
320
321
322
323
324
325
326
327

For this particular UPDATE test, MySQL is consistently
five or ten times
slower than PostgreSQL and SQLite.  I do not know why.  MySQL is
normally a very fast engine.  Perhaps this problem has been addressed
in later versions of MySQL.
</p>

<h3>Test 9: 25000 UPDATEs with an index</h3>
<blockquote>
BEGIN;<br>
UPDATE t2 SET b=468026 WHERE a=1;<br>
UPDATE t2 SET b=121928 WHERE a=2;<br>
<i>... 24996 lines omitted</i><br>
UPDATE t2 SET b=35065 WHERE a=24999;<br>
UPDATE t2 SET b=347393 WHERE a=25000;<br>
︙︙
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358


<p>
As recently as version 2.7.0, SQLite ran at about the same speed as
MySQL on this test.  But recent optimizations to SQLite have more
than doubled speed of UPDATEs.
</p>

<h2>Test 10: 25000 text UPDATEs with an index</h2>
<blockquote>
BEGIN;<br>
UPDATE t2 SET c='one hundred forty eight thousand three hundred eighty two' WHERE a=1;<br>
UPDATE t2 SET c='three hundred sixty six thousand five hundred two' WHERE a=2;<br>
<i>... 24996 lines omitted</i><br>
UPDATE t2 SET c='three hundred eighty three thousand ninety nine' WHERE a=24999;<br>
UPDATE t2 SET c='two hundred fifty six thousand eight hundred thirty' WHERE a=25000;<br>








|








336
337
338
339
340
341
342
343
344
345
346
347
348
349
350


<p>
As recently as version 2.7.0, SQLite ran at about the same speed as
MySQL on this test.  But recent optimizations to SQLite have more
than doubled speed of UPDATEs.
</p>

<h3>Test 10: 25000 text UPDATEs with an index</h3>
<blockquote>
BEGIN;<br>
UPDATE t2 SET c='one hundred forty eight thousand three hundred eighty two' WHERE a=1;<br>
UPDATE t2 SET c='three hundred sixty six thousand five hundred two' WHERE a=2;<br>
<i>... 24996 lines omitted</i><br>
UPDATE t2 SET c='three hundred eighty three thousand ninety nine' WHERE a=24999;<br>
UPDATE t2 SET c='two hundred fifty six thousand eight hundred thirty' WHERE a=25000;<br>
︙︙
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453


<p>
In fairness to PostgreSQL, it started thrashing on this test.  A
knowledgeable administrator might be able to get PostgreSQL to run a lot
faster here by tweaking and tuning the server a little.
</p>

<h2>Test 11: INSERTs from a SELECT</h2>
<blockquote>
BEGIN;<br>INSERT INTO t1 SELECT b,a,c FROM t2;<br>INSERT INTO t2 SELECT b,a,c FROM t1;<br>COMMIT;
</blockquote><table border=0 cellpadding=0 cellspacing=0>
<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;61.364</td></tr>
<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;1.537</td></tr>
<tr><td>SQLite 2.7.6:</td><td align="right">&nbsp;&nbsp;&nbsp;2.787</td></tr>
<tr><td>SQLite 2.7.6 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;1.599</td></tr>
</table>

<p>
The asynchronous SQLite is just a shade slower than MySQL on this test.
(MySQL seems to be especially adept at INSERT...SELECT statements.)
The PostgreSQL engine is still thrashing - most of the 61 seconds it used
were spent waiting on disk I/O.
</p>

<h2>Test 12: DELETE without an index</h2>
<blockquote>
DELETE FROM t2 WHERE c LIKE '%fifty%';
</blockquote><table border=0 cellpadding=0 cellspacing=0>
<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;1.509</td></tr>
<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;0.975</td></tr>
<tr><td>SQLite 2.7.6:</td><td align="right">&nbsp;&nbsp;&nbsp;4.004</td></tr>
<tr><td>SQLite 2.7.6 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;0.560</td></tr>
</table>

<p>
The synchronous version of SQLite is the slowest of the group in this test,
but the asynchronous version is the fastest.  
The difference is the extra time needed to execute fsync().
</p>

<h2>Test 13: DELETE with an index</h2>
<blockquote>
DELETE FROM t2 WHERE a>10 AND a<20000;
</blockquote><table border=0 cellpadding=0 cellspacing=0>
<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;1.316</td></tr>
<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;2.262</td></tr>
<tr><td>SQLite 2.7.6:</td><td align="right">&nbsp;&nbsp;&nbsp;2.068</td></tr>
<tr><td>SQLite 2.7.6 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;0.752</td></tr>
</table>

<p>
This test is significant because it is one of the few where
PostgreSQL is faster than MySQL.  The asynchronous SQLite is,
however, faster then both the other two.
</p>

<h2>Test 14: A big INSERT after a big DELETE</h2>
<blockquote>
INSERT INTO t2 SELECT * FROM t1;
</blockquote><table border=0 cellpadding=0 cellspacing=0>
<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;13.168</td></tr>
<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;1.815</td></tr>
<tr><td>SQLite 2.7.6:</td><td align="right">&nbsp;&nbsp;&nbsp;3.210</td></tr>
<tr><td>SQLite 2.7.6 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;1.485</td></tr>
</table>

<p>
Some older versions of SQLite (prior to version 2.4.0)
would show decreasing performance after a
sequence of DELETEs followed by new INSERTs.  As this test shows, the
problem has now been resolved.
</p>

<h2>Test 15: A big DELETE followed by many small INSERTs</h2>
<blockquote>
BEGIN;<br>
DELETE FROM t1;<br>
INSERT INTO t1 VALUES(1,10719,'ten thousand seven hundred nineteen');<br>
<i>... 11997 lines omitted</i><br>
INSERT INTO t1 VALUES(11999,72836,'seventy two thousand eight hundred thirty six');<br>
INSERT INTO t1 VALUES(12000,64231,'sixty four thousand two hundred thirty one');<br>








|
















|















|















|
















|








365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445


<p>
In fairness to PostgreSQL, it started thrashing on this test.  A
knowledgeable administrator might be able to get PostgreSQL to run a lot
faster here by tweaking and tuning the server a little.
</p>

<h3>Test 11: INSERTs from a SELECT</h3>
<blockquote>
BEGIN;<br>INSERT INTO t1 SELECT b,a,c FROM t2;<br>INSERT INTO t2 SELECT b,a,c FROM t1;<br>COMMIT;
</blockquote><table border=0 cellpadding=0 cellspacing=0>
<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;61.364</td></tr>
<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;1.537</td></tr>
<tr><td>SQLite 2.7.6:</td><td align="right">&nbsp;&nbsp;&nbsp;2.787</td></tr>
<tr><td>SQLite 2.7.6 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;1.599</td></tr>
</table>

<p>
The asynchronous SQLite is just a shade slower than MySQL on this test.
(MySQL seems to be especially adept at INSERT...SELECT statements.)
The PostgreSQL engine is still thrashing - most of the 61 seconds it used
were spent waiting on disk I/O.
</p>

<h3>Test 12: DELETE without an index</h3>
<blockquote>
DELETE FROM t2 WHERE c LIKE '%fifty%';
</blockquote><table border=0 cellpadding=0 cellspacing=0>
<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;1.509</td></tr>
<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;0.975</td></tr>
<tr><td>SQLite 2.7.6:</td><td align="right">&nbsp;&nbsp;&nbsp;4.004</td></tr>
<tr><td>SQLite 2.7.6 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;0.560</td></tr>
</table>

<p>
The synchronous version of SQLite is the slowest of the group in this test,
but the asynchronous version is the fastest.  
The difference is the extra time needed to execute fsync().
</p>

<h3>Test 13: DELETE with an index</h3>
<blockquote>
DELETE FROM t2 WHERE a>10 AND a<20000;
</blockquote><table border=0 cellpadding=0 cellspacing=0>
<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;1.316</td></tr>
<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;2.262</td></tr>
<tr><td>SQLite 2.7.6:</td><td align="right">&nbsp;&nbsp;&nbsp;2.068</td></tr>
<tr><td>SQLite 2.7.6 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;0.752</td></tr>
</table>

<p>
This test is significant because it is one of the few where
PostgreSQL is faster than MySQL.  The asynchronous SQLite is,
however, faster then both the other two.
</p>

<h3>Test 14: A big INSERT after a big DELETE</h3>
<blockquote>
INSERT INTO t2 SELECT * FROM t1;
</blockquote><table border=0 cellpadding=0 cellspacing=0>
<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;13.168</td></tr>
<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;1.815</td></tr>
<tr><td>SQLite 2.7.6:</td><td align="right">&nbsp;&nbsp;&nbsp;3.210</td></tr>
<tr><td>SQLite 2.7.6 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;1.485</td></tr>
</table>

<p>
Some older versions of SQLite (prior to version 2.4.0)
would show decreasing performance after a
sequence of DELETEs followed by new INSERTs.  As this test shows, the
problem has now been resolved.
</p>

<h3>Test 15: A big DELETE followed by many small INSERTs</h3>
<blockquote>
BEGIN;<br>
DELETE FROM t1;<br>
INSERT INTO t1 VALUES(1,10719,'ten thousand seven hundred nineteen');<br>
<i>... 11997 lines omitted</i><br>
INSERT INTO t1 VALUES(11999,72836,'seventy two thousand eight hundred thirty six');<br>
INSERT INTO t1 VALUES(12000,64231,'sixty four thousand two hundred thirty one');<br>
︙︙
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475

</table>

<p>
SQLite is very good at doing INSERTs within a transaction, which probably
explains why it is so much faster than the other databases at this test.
</p>

<h2>Test 16: DROP TABLE</h2>
<blockquote>
DROP TABLE t1;<br>DROP TABLE t2;<br>DROP TABLE t3;
</blockquote><table border=0 cellpadding=0 cellspacing=0>
<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;0.135</td></tr>
<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;0.015</td></tr>
<tr><td>SQLite 2.7.6:</td><td align="right">&nbsp;&nbsp;&nbsp;0.939</td></tr>
<tr><td>SQLite 2.7.6 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;0.254</td></tr>








|








453
454
455
456
457
458
459
460
461
462
463
464
465
466
467

</table>

<p>
SQLite is very good at doing INSERTs within a transaction, which probably
explains why it is so much faster than the other databases at this test.
</p>

<h3>Test 16: DROP TABLE</h3>
<blockquote>
DROP TABLE t1;<br>DROP TABLE t2;<br>DROP TABLE t3;
</blockquote><table border=0 cellpadding=0 cellspacing=0>
<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;0.135</td></tr>
<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;0.015</td></tr>
<tr><td>SQLite 2.7.6:</td><td align="right">&nbsp;&nbsp;&nbsp;0.939</td></tr>
<tr><td>SQLite 2.7.6 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;0.254</td></tr>
︙︙
485
486
487
488
489
490
491
492
493
494
495
496
497
498


<p>
On the other hand, dropping tables is not a very common operation 
so if SQLite takes a little longer, that is not seen as a big problem.
</p>

}
puts {
<p><hr /></p>
<p><a href="index.html"><img src="/goback.jpg" border=0 />
Back to the SQLite Home Page</a>
</p>

</body></html>}








<
<
<
<
<
|
<

477
478
479
480
481
482
483





484



<p>
On the other hand, dropping tables is not a very common operation 
so if SQLite takes a little longer, that is not seen as a big problem.
</p>

}





footer $rcsid
Changes to www/sqlite.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

#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: sqlite.tcl,v 1.21 2003/06/29 16:11:13 drh Exp $}

puts {<html>
<head>
  <title>sqlite: A program of interacting with SQLite databases</title>
</head>
<body bgcolor=white>
<h1 align=center>

sqlite: A program to administer SQLite databases
</h1>}
puts "<p align=center>
(This page was last modified on [lrange $rcsid 3 4] UTC)
</p>"

puts {
<p>The SQLite library includes a simple command-line utility named
<b>sqlite</b> that allows the user to manually enter and execute SQL
commands against an SQLite database.  This document provides a brief
introduction on how to use <b>sqlite</b>.

<h2>Getting Started</h2>

<p>To start the <b>sqlite</b> program, just type "sqlite" followed by
the name the file that holds the SQLite database.  If the file does
not exist, a new one is created automatically.
The <b>sqlite</b> program will
then prompt you to enter SQL.  Type in SQL statements (terminated by a
semicolon), press "Enter" and the SQL will be executed.</p>




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

<





|








1
2
3
4
5


6



7
8




9

10
11
12
13
14
15
16
17
18
19
20
21
22

#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: sqlite.tcl,v 1.22 2004/05/31 15:06:30 drh Exp $}
source common.tcl


header {sqlite: A program of interacting with SQLite databases}



puts {
<h2>sqlite: A command-line program to administer SQLite databases</h2>






<p>The SQLite library includes a simple command-line utility named
<b>sqlite</b> that allows the user to manually enter and execute SQL
commands against an SQLite database.  This document provides a brief
introduction on how to use <b>sqlite</b>.

<h3>Getting Started</h3>

<p>To start the <b>sqlite</b> program, just type "sqlite" followed by
the name the file that holds the SQLite database.  If the file does
not exist, a new one is created automatically.
The <b>sqlite</b> program will
then prompt you to enter SQL.  Type in SQL statements (terminated by a
semicolon), press "Enter" and the SQL will be executed.</p>
︙︙
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94

   ...> (((  f3 real)))
   ...> ((();)))
sqlite> 
}

puts {

<h2>Aside: Querying the SQLITE_MASTER table</h2>

<p>The database schema in an SQLite database is stored in
a special table named "sqlite_master".
You can execute "SELECT" statements against the
special sqlite_master table just like any other table
in an SQLite database.  For example:</p>
}








|








71
72
73
74
75
76
77
78
79
80
81
82
83
84
85

   ...> (((  f3 real)))
   ...> ((();)))
sqlite> 
}

puts {

<h3>Aside: Querying the SQLITE_MASTER table</h3>

<p>The database schema in an SQLite database is stored in
a special table named "sqlite_master".
You can execute "SELECT" statements against the
special sqlite_master table just like any other table
in an SQLite database.  For example:</p>
}
︙︙
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133

The schema for TEMPORARY tables is not stored in the "sqlite_master" table
since TEMPORARY tables are not visible to applications other than the
application that created the table.  The schema for TEMPORARY tables
is stored in another special table named "sqlite_temp_master".  The
"sqlite_temp_master" table is temporary itself.
</p>

<h2>Special commands to sqlite</h2>

<p>
Most of the time, sqlite just reads lines of input and passes them
on to the SQLite library for execution.
But if an input line begins with a dot ("."), then
that line is intercepted and interpreted by the sqlite program itself.
These "dot commands" are typically used to change the output format








|








110
111
112
113
114
115
116
117
118
119
120
121
122
123
124

The schema for TEMPORARY tables is not stored in the "sqlite_master" table
since TEMPORARY tables are not visible to applications other than the
application that created the table.  The schema for TEMPORARY tables
is stored in another special table named "sqlite_temp_master".  The
"sqlite_temp_master" table is temporary itself.
</p>

<h3>Special commands to sqlite</h3>

<p>
Most of the time, sqlite just reads lines of input and passes them
on to the SQLite library for execution.
But if an input line begins with a dot ("."), then
that line is intercepted and interpreted by the sqlite program itself.
These "dot commands" are typically used to change the output format
︙︙
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178

.tables ?PATTERN?      List names of tables matching a pattern
.timeout MS            Try opening locked tables for MS milliseconds
.width NUM NUM ...     Set column widths for "column" mode
sqlite> 
}

puts {
<h2>Changing Output Formats</h2>

<p>The sqlite program is able to show the results of a query
in five different formats: "line", "column", "list", "html", and "insert".
You can use the ".mode" dot command to switch between these output
formats.</p>

<p>The default output mode is "list".  In








|








155
156
157
158
159
160
161
162
163
164
165
166
167
168
169

.tables ?PATTERN?      List names of tables matching a pattern
.timeout MS            Try opening locked tables for MS milliseconds
.width NUM NUM ...     Set column widths for "column" mode
sqlite> 
}

puts {
<h3>Changing Output Formats</h3>

<p>The sqlite program is able to show the results of a query
in five different formats: "line", "column", "list", "html", and "insert".
You can use the ".mode" dot command to switch between these output
formats.</p>

<p>The default output mode is "list".  In
︙︙
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
329
330
331
332
333
334
335
336

&lt;TABLE&gt; and the ending &lt;/TABLE&gt; are not written, but
all of the intervening &lt;TR&gt;s, &lt;TH&gt;s, and &lt;TD&gt;s
are.  The html output mode is envisioned as being useful for
CGI.</p>
}

puts {
<h2>Writing results to a file</h2>

<p>By default, sqlite sends query results to standard output.  You
can change this using the ".output" command.  Just put the name of
an output file as an argument to the .output command and all subsequent
query results will be written to that file.  Use ".output stdout" to
begin writing to standard output again.  For example:</p>}

Code {
sqlite> (((.mode list)))
sqlite> (((.separator |)))
sqlite> (((.output test_file_1.txt)))
sqlite> (((select * from tbl1;)))
sqlite> (((.exit)))
$ (((cat test_file_1.txt)))
hello|10
goodbye|20
$
}

puts {
<h2>Querying the database schema</h2>

<p>The sqlite program provides several convenience commands that
are useful for looking at the schema of the database.  There is
nothing that these commands do that cannot be done by some other
means.  These commands are provided purely as a shortcut.</p>

<p>For example, to see a list of the tables in the database, you








|




















|








292
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

&lt;TABLE&gt; and the ending &lt;/TABLE&gt; are not written, but
all of the intervening &lt;TR&gt;s, &lt;TH&gt;s, and &lt;TD&gt;s
are.  The html output mode is envisioned as being useful for
CGI.</p>
}

puts {
<h3>Writing results to a file</h3>

<p>By default, sqlite sends query results to standard output.  You
can change this using the ".output" command.  Just put the name of
an output file as an argument to the .output command and all subsequent
query results will be written to that file.  Use ".output stdout" to
begin writing to standard output again.  For example:</p>}

Code {
sqlite> (((.mode list)))
sqlite> (((.separator |)))
sqlite> (((.output test_file_1.txt)))
sqlite> (((select * from tbl1;)))
sqlite> (((.exit)))
$ (((cat test_file_1.txt)))
hello|10
goodbye|20
$
}

puts {
<h3>Querying the database schema</h3>

<p>The sqlite program provides several convenience commands that
are useful for looking at the schema of the database.  There is
nothing that these commands do that cannot be done by some other
means.  These commands are provided purely as a shortcut.</p>

<p>For example, to see a list of the tables in the database, you
︙︙
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451

and the second column is the filename of the external file.</p>}

Code {
sqlite> (((.databases)))
}

puts {
<h2>Converting An Entire Database To An ASCII Text File</h2>

<p>Use the ".dump" command to convert the entire contents of a
database into a single ASCII text file.  This file can be converted
back into a database by piping it back into <b>sqlite</b>.</p>

<p>A good way to make an archival copy of a database is this:</p>
}








|








428
429
430
431
432
433
434
435
436
437
438
439
440
441
442

and the second column is the filename of the external file.</p>}

Code {
sqlite> (((.databases)))
}

puts {
<h3>Converting An Entire Database To An ASCII Text File</h3>

<p>Use the ".dump" command to convert the entire contents of a
database into a single ASCII text file.  This file can be converted
back into a database by piping it back into <b>sqlite</b>.</p>

<p>A good way to make an archival copy of a database is this:</p>
}
︙︙
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505


Code {
$ (((sqlite ex3 <schema.sql)))
$ (((pg_dump -a ex2 | sqlite ex3)))
}

puts {
<h2>Other Dot Commands</h2>

<p>The ".explain" dot command can be used to set the output mode
to "column" and to set the column widths to values that are reasonable
for looking at the output of an EXPLAIN command.  The EXPLAIN command
is an SQLite-specific SQL extension that is useful for debugging.  If any
regular SQL is prefaced by EXPLAIN, then the SQL command is parsed and
analyzed but is not executed.  Instead, the sequence of virtual machine








|








482
483
484
485
486
487
488
489
490
491
492
493
494
495
496


Code {
$ (((sqlite ex3 <schema.sql)))
$ (((pg_dump -a ex2 | sqlite ex3)))
}

puts {
<h3>Other Dot Commands</h3>

<p>The ".explain" dot command can be used to set the output mode
to "column" and to set the column widths to values that are reasonable
for looking at the output of an EXPLAIN command.  The EXPLAIN command
is an SQLite-specific SQL extension that is useful for debugging.  If any
regular SQL is prefaced by EXPLAIN, then the SQL command is parsed and
analyzed but is not executed.  Instead, the sequence of virtual machine
︙︙
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549

before returning an error.  The default value of the timeout is zero so
that an error is returned immediately if any needed database table or
index is locked.</p>

<p>And finally, we mention the ".exit" command which causes the
sqlite program to exit.</p>

<h2>Using sqlite in a shell script</h2>

<p>
One way to use sqlite in a shell script is to use "echo" or
"cat" to generate a sequence of commands in a file, then invoke sqlite 
while redirecting input from the generated command file.  This
works fine and is appropriate in many circumstances.  But as
an added convenience, sqlite allows a single SQL command to be








|








526
527
528
529
530
531
532
533
534
535
536
537
538
539
540

before returning an error.  The default value of the timeout is zero so
that an error is returned immediately if any needed database table or
index is locked.</p>

<p>And finally, we mention the ".exit" command which causes the
sqlite program to exit.</p>

<h3>Using sqlite in a shell script</h3>

<p>
One way to use sqlite in a shell script is to use "echo" or
"cat" to generate a sequence of commands in a file, then invoke sqlite 
while redirecting input from the generated command file.  This
works fine and is appropriate in many circumstances.  But as
an added convenience, sqlite allows a single SQL command to be
︙︙
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593

> ((( awk '{printf "<tr><td>%s<td>%s\n",$1,$2 }')))
<tr><td>hello<td>10
<tr><td>goodbye<td>20
$
}

puts {
<h2>Ending shell commands</h2>

<p>
SQLite commands are normally terminated by a semicolon.  In the shell 
you can also use the word "GO" (case-insensitive) or a backslash character 
"\" on a line by itself to end a command.  These are used by SQL Server 
and Oracle, respectively.  These won't work in <b>sqlite_exec()</b>, 
because the shell translates these into a semicolon before passing them 
to that function.</p>
}

puts {
<h2>Compiling the sqlite program from sources</h2>

<p>
The sqlite program is built automatically when you compile the
sqlite library.  Just get a copy of the source tree, run
"configure" and then "make".</p>
}

puts {
<p><hr /></p>
<p><a href="index.html"><img src="/goback.jpg" border=0 />
Back to the SQLite Home Page</a>
</p>

</body></html>}








|











|






|
<
<
<
<
<
<
<

551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577








> ((( awk '{printf "<tr><td>%s<td>%s\n",$1,$2 }')))
<tr><td>hello<td>10
<tr><td>goodbye<td>20
$
}

puts {
<h3>Ending shell commands</h3>

<p>
SQLite commands are normally terminated by a semicolon.  In the shell 
you can also use the word "GO" (case-insensitive) or a backslash character 
"\" on a line by itself to end a command.  These are used by SQL Server 
and Oracle, respectively.  These won't work in <b>sqlite_exec()</b>, 
because the shell translates these into a semicolon before passing them 
to that function.</p>
}

puts {
<h3>Compiling the sqlite program from sources</h3>

<p>
The sqlite program is built automatically when you compile the
sqlite library.  Just get a copy of the source tree, run
"configure" and then "make".</p>
}
footer $rcsid
Changes to www/tclsqlite.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

#
# Run this Tcl script to generate the tclsqlite.html file.
#
set rcsid {$Id: tclsqlite.tcl,v 1.8 2003/08/19 14:31:02 drh Exp $}


puts {<html>
<head>
  <title>The Tcl interface to the SQLite library</title>
</head>
<body bgcolor=white>
<h1 align=center>
The Tcl interface to the SQLite library
</h1>}
puts "<p align=center>
(This page was last modified on [lrange $rcsid 3 4] UTC)
</p>"

puts {
<p>The SQLite library is designed to be very easy to use from
a Tcl or Tcl/Tk script.  This document gives an overview of the Tcl
programming interface.</p>

<h2>The API</h2>

<p>The interface to the SQLite library consists of single
tcl command named <b>sqlite</b>.  Because there is only this
one interface command, the interface is not placed in a separate
namespace.</p>

<p>The <b>sqlite</b> command is used as follows:</p>




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

<




|








1
2
3
4
5
6
7





8




9

10
11
12
13
14
15
16
17
18
19
20
21

#
# Run this Tcl script to generate the tclsqlite.html file.
#
set rcsid {$Id: tclsqlite.tcl,v 1.9 2004/05/31 15:06:30 drh Exp $}
source common.tcl
header {The Tcl interface to the SQLite library}
puts {





<h2>The Tcl interface to the SQLite library</h2>






<p>The SQLite library is designed to be very easy to use from
a Tcl or Tcl/Tk script.  This document gives an overview of the Tcl
programming interface.</p>

<h3>The API</h3>

<p>The interface to the SQLite library consists of single
tcl command named <b>sqlite</b>.  Because there is only this
one interface command, the interface is not placed in a separate
namespace.</p>

<p>The <b>sqlite</b> command is used as follows:</p>
︙︙
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79

<li> timeout
</ul>
</p>

<p>We will explain all of these methods, though not in that order.
We will be begin with the "close" method.</p>

<h2>The "close" method</h2>

<p>
As its name suggests, the "close" method to an SQLite database just
closes the database.  This has the side-effect of deleting the
<i>dbcmd</i> Tcl command.  Here is an example of opening and then
immediately closing a database:
</p>








|








56
57
58
59
60
61
62
63
64
65
66
67
68
69
70

<li> timeout
</ul>
</p>

<p>We will explain all of these methods, though not in that order.
We will be begin with the "close" method.</p>

<h3>The "close" method</h3>

<p>
As its name suggests, the "close" method to an SQLite database just
closes the database.  This has the side-effect of deleting the
<i>dbcmd</i> Tcl command.  Here is an example of opening and then
immediately closing a database:
</p>
︙︙
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103

to the previous:</p>

<blockquote>
<b>sqlite db1 ./testdb<br>
rename db1 {}</b>
</blockquote>

<h2>The "eval" method</h2>

<p>
The most useful <i>dbcmd</i> method is "eval".  The eval method is used
to execute SQL on the database.  The syntax of the eval method looks
like this:</p>

<blockquote>








|








80
81
82
83
84
85
86
87
88
89
90
91
92
93
94

to the previous:</p>

<blockquote>
<b>sqlite db1 ./testdb<br>
rename db1 {}</b>
</blockquote>

<h3>The "eval" method</h3>

<p>
The most useful <i>dbcmd</i> method is "eval".  The eval method is used
to execute SQL on the database.  The syntax of the eval method looks
like this:</p>

<blockquote>
︙︙
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


<blockquote><b>
a=1 b=hello<br>
a=2 b=goodbye<br>
a=3 b=howdy!</b>
</blockquote>

<h2>The "complete" method</h2>

<p>
The "complete" method takes a string of supposed SQL as its only argument.
It returns TRUE if the string is a complete statement of SQL and FALSE if
there is more to be entered.</p>

<p>The "complete" method is useful when building interactive applications
in order to know when the user has finished entering a line of SQL code.
This is really just an interface to the <b>sqlite_complete()</b> C
function.  Refer to the <a href="c_interface.html">C/C++ interface</a>
specification for additional information.</p>

<h2>The "timeout" method</h2>

<p>The "timeout" method is used to control how long the SQLite library
will wait for locks to clear before giving up on a database transaction.
The default timeout is 0 millisecond.  (In other words, the default behavior
is not to wait at all.)</p>

<p>The SQlite database allows multiple simultaneous








|












|








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


<blockquote><b>
a=1 b=hello<br>
a=2 b=goodbye<br>
a=3 b=howdy!</b>
</blockquote>

<h3>The "complete" method</h3>

<p>
The "complete" method takes a string of supposed SQL as its only argument.
It returns TRUE if the string is a complete statement of SQL and FALSE if
there is more to be entered.</p>

<p>The "complete" method is useful when building interactive applications
in order to know when the user has finished entering a line of SQL code.
This is really just an interface to the <b>sqlite_complete()</b> C
function.  Refer to the <a href="c_interface.html">C/C++ interface</a>
specification for additional information.</p>

<h3>The "timeout" method</h3>

<p>The "timeout" method is used to control how long the SQLite library
will wait for locks to clear before giving up on a database transaction.
The default timeout is 0 millisecond.  (In other words, the default behavior
is not to wait at all.)</p>

<p>The SQlite database allows multiple simultaneous
︙︙
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


<blockquote><b>db1 timeout 2000</b></blockquote>

<p>The argument to the timeout method is the maximum number of milliseconds
to wait for the lock to clear.  So in the example above, the maximum delay
would be 2 seconds.</p>

<h2>The "busy" method</h2>

<p>The "busy" method, like "timeout", only comes into play when the
database is locked.  But the "busy" method gives the programmer much more
control over what action to take.  The "busy" method specifies a callback
Tcl procedure that is invoked whenever SQLite tries to open a locked
database.  This callback can do whatever is desired.  Presumably, the
callback will do some other useful work for a short while then return
so that the lock can be tried again.  The callback procedure should
return "0" if it wants SQLite to try again to open the database and
should return "1" if it wants SQLite to abandon the current operation.

<h2>The "last_insert_rowid" method</h2>

<p>The "last_insert_rowid" method returns an integer which is the ROWID
of the most recently inserted database row.</p>

<h2>The "onecolumn" method</h2>

<p>The "onecolumn" method works like "eval" in that it evaluates the
SQL query statement given as its argument.  The difference is that
"onecolumn" returns a single element which is the first column of the
first row of the query result.</p>

<p>This is a convenience method.  It saves the user from having to
do a "<tt>[lindex&nbsp;...&nbsp;0]</tt>" on the results of an "eval"
in order to extract a single column result.</p>

<h2>The "changes" method</h2>

<p>The "changes" method returns an integer which is the number of rows
in the database that were inserted, deleted, and/or modified by the most
recent "eval" method.</p>

}

puts {
<p><hr /></p>
<p><a href="index.html"><img src="/goback.jpg" border=0 />
Back to the SQLite Home Page</a>
</p>

</body></html>}








|











|




|










|






|
<
<
<
<
<
<
<

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









<blockquote><b>db1 timeout 2000</b></blockquote>

<p>The argument to the timeout method is the maximum number of milliseconds
to wait for the lock to clear.  So in the example above, the maximum delay
would be 2 seconds.</p>

<h3>The "busy" method</h3>

<p>The "busy" method, like "timeout", only comes into play when the
database is locked.  But the "busy" method gives the programmer much more
control over what action to take.  The "busy" method specifies a callback
Tcl procedure that is invoked whenever SQLite tries to open a locked
database.  This callback can do whatever is desired.  Presumably, the
callback will do some other useful work for a short while then return
so that the lock can be tried again.  The callback procedure should
return "0" if it wants SQLite to try again to open the database and
should return "1" if it wants SQLite to abandon the current operation.

<h3>The "last_insert_rowid" method</h3>

<p>The "last_insert_rowid" method returns an integer which is the ROWID
of the most recently inserted database row.</p>

<h3>The "onecolumn" method</h3>

<p>The "onecolumn" method works like "eval" in that it evaluates the
SQL query statement given as its argument.  The difference is that
"onecolumn" returns a single element which is the first column of the
first row of the query result.</p>

<p>This is a convenience method.  It saves the user from having to
do a "<tt>[lindex&nbsp;...&nbsp;0]</tt>" on the results of an "eval"
in order to extract a single column result.</p>

<h3>The "changes" method</h3>

<p>The "changes" method returns an integer which is the number of rows
in the database that were inserted, deleted, and/or modified by the most
recent "eval" method.</p>

}
footer $rcsid
Changes to www/vdbe.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

#
# Run this Tcl script to generate the vdbe.html file.
#
set rcsid {$Id: vdbe.tcl,v 1.11 2003/06/29 16:11:13 drh Exp $}


puts {<html>
<head>
  <title>The Virtual Database Engine of SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>
The Virtual Database Engine of SQLite
</h1>}
puts "<p align=center>
(This page was last modified on [lrange $rcsid 3 4] UTC)
</p>"

puts {
<blockquote><b>
This document describes the virtual machine used in SQLite version 2.8.0.  
</b></blockquote>
}

puts {
<p>If you want to know how the SQLite library works internally,




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

<








1
2
3
4
5
6
7





8




9

10
11
12
13
14
15
16

#
# Run this Tcl script to generate the vdbe.html file.
#
set rcsid {$Id: vdbe.tcl,v 1.12 2004/05/31 15:06:30 drh Exp $}
source common.tcl
header {The Virtual Database Engine of SQLite}
puts {





<h2>The Virtual Database Engine of SQLite</h2>






<blockquote><b>
This document describes the virtual machine used in SQLite version 2.8.0.  
</b></blockquote>
}

puts {
<p>If you want to know how the SQLite library works internally,
︙︙
1985
1986
1987
1988
1989
1990
1991
1992
1993
1994
1995
1996
1997
1998
1999

difficulty understanding the rest.</p>

<p>If you find errors in either the documentation or the code,
feel free to fix them and/or contact the author at
<a href="mailto:drh@hwaci.com">drh@hwaci.com</a>.  Your bug fixes or
suggestions are always welcomed.</p>
}

puts {
<p><hr /></p>
<p><a href="index.html"><img src="/goback.jpg" border=0 />
Back to the SQLite Home Page</a>
</p>

</body></html>}








|
<
<
<
<
<
<
<

1976
1977
1978
1979
1980
1981
1982
1983








difficulty understanding the rest.</p>

<p>If you find errors in either the documentation or the code,
feel free to fix them and/or contact the author at
<a href="mailto:drh@hwaci.com">drh@hwaci.com</a>.  Your bug fixes or
suggestions are always welcomed.</p>
}
footer $rcsid