/ Check-in [4b4bfc62]
Login
Overview
Comment:Documentation updates. (CVS 275)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:4b4bfc6290f05c6672338690911f68fd8bb418c9
User & Date: drh 2001-09-28 23:11:24
Context
2001-09-28
23:15
Version 2.0.0 (CVS 470) check-in: c0a8a1fb user: drh tags: trunk
23:11
Documentation updates. (CVS 275) check-in: 4b4bfc62 user: drh tags: trunk
18:14
Remove reference to GDBM in the documentation of the "sqlite" command-line utility. (CVS 274) check-in: 0ffab36d user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to www/arch.tcl.

1
2
3
4
5
6
7
8
9
10
11
...
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
#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: arch.tcl,v 1.5 2001/09/20 01:44:43 drh Exp $}

puts {<html>
<head>
  <title>Architecture of SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>
................................................................................
its own header file <b>vdbe.h</b> that defines an interface
between the virtual machine and the rest of the SQLite library.</p>

<h2>B-tree Driver</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 but all B-trees are stored in the
same disk file.  Each page of a B-tree is 1024 bytes in size.  The data
is stored with the key in an area called "payload".  Up to 236 bytes of
payload can be stored with each 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 for the B-tree module.
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 and commit or rollback its
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>



|







 







|
|
|
|
|









|



|







1
2
3
4
5
6
7
8
9
10
11
...
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
#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: arch.tcl,v 1.6 2001/09/28 23:11:24 drh Exp $}

puts {<html>
<head>
  <title>Architecture of SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>
................................................................................
its own header file <b>vdbe.h</b> that defines an interface
between the virtual machine and the rest of the SQLite library.</p>

<h2>B-tree Driver</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 at the behest of the B-tree module.
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>

Changes to www/c_interface.tcl.

1
2
3
4
5
6
7
8
9
10
11
..
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
...
255
256
257
258
259
260
261









262
263
264
265
266
267
268
...
304
305
306
307
308
309
310




311
312
313
314
315
316
317
#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: c_interface.tcl,v 1.15 2001/09/20 01:44:43 drh Exp $}

puts {<html>
<head>
  <title>The C language interface to the SQLite library</title>
</head>
<body bgcolor=white>
<h1 align=center>
................................................................................
  sqlite*,
  char *sql,
  int (*)(void*,int,char**,char**),
  void*,
  char **errmsg
);

#define SQLITE_OK        0    /* Successful result */
#define SQLITE_ERROR     1    /* SQL error or missing database */
#define SQLITE_INTERNAL  2    /* An internal logic error in SQLite */
#define SQLITE_PERM      3    /* Access permission denied */
#define SQLITE_ABORT     4    /* Callback routine requested an abort */
#define SQLITE_BUSY      5    /* One or more database files are locked */

#define SQLITE_NOMEM     6    /* A malloc() failed */
#define SQLITE_READONLY  7    /* Attempt to write a readonly database */
#define SQLITE_INTERRUPT 8    /* Operation terminated by sqlite_interrupt() */
#define SQLITE_IOERR     9    /* Some kind of disk I/O error occurred */
#define SQLITE_CORRUPT   10   /* The database disk image is malformed */

#define SQLITE_FULL      12   /* Insertion failed because database is full */
#define SQLITE_CANTOPEN  13   /* Unable to open the database file */
#define SQLITE_PROTOCOL  14   /* Database lock protocol error */

#define SQLITE_SCHEMA    16   /* The database schema changed */
#define SQLITE_TOOBIG    17   /* Too much data for one row of a table */

</pre></blockquote>

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

................................................................................
<dt>SQLITE_BUSY</dt>
<dd><p>This return code indicates that another program or thread has
the database locked.  SQLite allows two or more threads to read the
database at the same time, but only one thread can have the database
open for writing at the same time.  Locking in SQLite is on the
entire database.</p>
</p></dd>









<dt>SQLITE_NOMEM</dt>
<dd><p>This value is returned if a call to <b>malloc()</b> fails.
</p></dd>
<dt>SQLITE_READONLY</dt>
<dd><p>This return code indicates that an attempt was made to write to
a database file that is opened for reading only.
</p></dd>
................................................................................
schema.  This is the return code for such cases.  Retrying the
command usually will clear the problem.
</p></dd>
<dt>SQLITE_TOOBIG</dt>
<dd><p>SQLite cannot store more than about 64K of data in a single row
of a single table.  If you attempt to store more than 64K in a single
row, this is the return code you get.




</p></dd>
</dl>
</blockquote>

<h2>Querying without using a callback function</h2>

<p>The <b>sqlite_get_table()</b> function is a wrapper around



|







 







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







 







>
>
>
>
>
>
>
>
>







 







>
>
>
>







1
2
3
4
5
6
7
8
9
10
11
..
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
...
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
...
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: c_interface.tcl,v 1.16 2001/09/28 23:11:24 drh Exp $}

puts {<html>
<head>
  <title>The C language interface to the SQLite library</title>
</head>
<body bgcolor=white>
<h1 align=center>
................................................................................
  sqlite*,
  char *sql,
  int (*)(void*,int,char**,char**),
  void*,
  char **errmsg
);

#define SQLITE_OK           0   /* Successful result */
#define SQLITE_ERROR        1   /* SQL error or missing database */
#define SQLITE_INTERNAL     2   /* An internal logic error in SQLite */
#define SQLITE_PERM         3   /* Access permission denied */
#define SQLITE_ABORT        4   /* Callback routine requested an abort */
#define SQLITE_BUSY         5   /* The database file is locked */
#define SQLITE_LOCKED       6   /* A table in the database is locked */
#define SQLITE_NOMEM        7   /* A malloc() failed */
#define SQLITE_READONLY     8   /* Attempt to write a readonly database */
#define SQLITE_INTERRUPT    9   /* Operation terminated by sqlite_interrupt() */
#define SQLITE_IOERR       10   /* Some kind of disk I/O error occurred */
#define SQLITE_CORRUPT     11   /* The database disk image is malformed */
#define SQLITE_NOTFOUND    12   /* (Internal Only) Table or record not found */
#define SQLITE_FULL        13   /* Insertion failed because database is full */
#define SQLITE_CANTOPEN    14   /* Unable to open the database file */
#define SQLITE_PROTOCOL    15   /* Database lock protocol error */
#define SQLITE_EMPTY       16   /* (Internal Only) Database table is empty */
#define SQLITE_SCHEMA      17   /* The database schema changed */
#define SQLITE_TOOBIG      18   /* Too much data for one row of a table */
#define SQLITE_CONSTRAINT  19   /* Abort due to contraint violation */
</pre></blockquote>

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

................................................................................
<dt>SQLITE_BUSY</dt>
<dd><p>This return code indicates that another program or thread has
the database locked.  SQLite allows two or more threads to read the
database at the same time, but only one thread can have the database
open for writing at the same time.  Locking in SQLite is on the
entire database.</p>
</p></dd>
<dt>SQLITE_LOCKED</dt>
<dd><p>This return code is similar to SQLITE_BUSY in that it indicates
that the database is locked.  But the source of the lock is a recursive
call to <b>sqlite_exec()</b>.  This return can only occur if you attempt
to invoke sqlite_exec() from within a callback routine of a query
from a prior invocation of sqlite_exec().  Recursive calls to
sqlite_exec() are allowed as long as no more they all read-only or do
not attempt to write the same table.
</p></dd>
<dt>SQLITE_NOMEM</dt>
<dd><p>This value is returned if a call to <b>malloc()</b> fails.
</p></dd>
<dt>SQLITE_READONLY</dt>
<dd><p>This return code indicates that an attempt was made to write to
a database file that is opened for reading only.
</p></dd>
................................................................................
schema.  This is the return code for such cases.  Retrying the
command usually will clear the problem.
</p></dd>
<dt>SQLITE_TOOBIG</dt>
<dd><p>SQLite cannot store more than about 64K of data in a single row
of a single table.  If you attempt to store more than 64K in a single
row, this is the return code you get.
</p></dd>
<dt>SQLITE_CONSTRAINT</dt>
<dd><p>This constant is returned if the SQL statement would have violated
a database constraint.
</p></dd>
</dl>
</blockquote>

<h2>Querying without using a callback function</h2>

<p>The <b>sqlite_get_table()</b> function is a wrapper around

Changes to www/index.tcl.

1
2
3
4
5
6
7
8
9
10
11
..
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
#
# Run this TCL script to generate HTML for the index.html file.
#
set rcsid {$Id: index.tcl,v 1.42 2001/09/28 17:47:14 drh Exp $}

puts {<html>
<head><title>SQLite: An SQL Database Engine In A C Library</title></head>
<body bgcolor=white>
<h1 align=center>SQLite: An SQL Database Engine In A C Library</h1>
<p align=center>}
puts "This page was last modified on [lrange $rcsid 3 4] GMT<br>"
................................................................................
</td></tr>
</table>}

puts {<h2>Features</h2>

<p><ul>
<li>Implements a large subset of SQL92.</li>
<li>A complete SQL database (with multiple tables and indices) is
    stored in a single disk file.</li>
<li>Atomic commit and rollback protect data integrity.</li>
<li>Small memory footprint: about 12000 lines of C code.</li>
<li><a href="speed.html">Four times faster</a> than PostgreSQL.
    Twice as fast as SQLite 1.0.</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 <a href="tclsqlite.html">TCL interface</a>
    to the library is included.</li>
<li>A TCL-based test suite provides near 100% code coverage.</li>
<li>Self-contained: no external dependencies.</li>
<li>Built and tested under Linux and Win2K.</li>
<li>Sources are uncopyrighted.  Use for any purpose.</li>
</ul>
</p>
}

puts {<h2>Download</h2>

<p>
Precompiled binaries for Linux and Windows and the complete
source tree are available for <a href="download.html">download</a>.
</p>
}

puts {<h2>Current Status</h2>

<p>A <a href="changes.html">change history</a> is available online.


There are currently no <em>known</em> memory leaks or debilitating bugs
in the library.  <a href="http://gcc.gnu.org/onlinedocs/gcov_1.html">Gcov</a>
is used to verify test coverage.</p>























<h2>Documentation</h2>

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

<p><ul>
<li>Information on the <a href="sqlite.html">sqlite</a>
    command-line utility.</li>
<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="tclsqlite.html">Tcl Interface</a>.</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>
</ul>
</p>




|







 







|








|
<








<
<
<
<
<
<
<
<



>
>

<
<
>
>

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










|







1
2
3
4
5
6
7
8
9
10
11
..
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
#
# Run this TCL script to generate HTML for the index.html file.
#
set rcsid {$Id: index.tcl,v 1.43 2001/09/28 23:11:24 drh Exp $}

puts {<html>
<head><title>SQLite: An SQL Database Engine In A C Library</title></head>
<body bgcolor=white>
<h1 align=center>SQLite: An SQL Database Engine In A C Library</h1>
<p align=center>}
puts "This page was last modified on [lrange $rcsid 3 4] GMT<br>"
................................................................................
</td></tr>
</table>}

puts {<h2>Features</h2>

<p><ul>
<li>Implements a large subset of SQL92.</li>
<li>A complete database (with multiple tables and indices) is
    stored in a single disk file.</li>
<li>Atomic commit and rollback protect data integrity.</li>
<li>Small memory footprint: about 12000 lines of C code.</li>
<li><a href="speed.html">Four times faster</a> than PostgreSQL.
    Twice as fast as SQLite 1.0.</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.</li>

<li>A TCL-based test suite provides near 100% code coverage.</li>
<li>Self-contained: no external dependencies.</li>
<li>Built and tested under Linux and Win2K.</li>
<li>Sources are uncopyrighted.  Use for any purpose.</li>
</ul>
</p>
}









puts {<h2>Current Status</h2>

<p>A <a href="changes.html">change history</a> is available online.
The latest source code is
<a href="download.html">available for download</a>.
There are currently no <em>known</em> memory leaks or debilitating bugs


in the library.
</p>

<p>
The file format used changed beginning with version 2.0.0.  Version 1.0.X
of SQLite used GDBM as its database backend.  Version 2.0.0 and later
use a built-in implementation of B-trees.  If you have older 1.0 databases
you will need to convert them before they can be read using a 2.0
release of SQLite.  The following command will convert a legacy
database into the new 2.0 format:
</p>

<blockquote><pre>
echo .dump | sqlite1.0 old.db | sqlite2.0 new.db
</pre></blockquote>

<p>
The above command assumes that <b>sqlite1.0</b> is sqlite version 1.0
and <b>sqlite2.0</b> is sqlite version 2.0.  The old database is stored
in a directory named <b>old.db</b> and the new database is created in
the file <b>new.db</b>.
</p>

<h2>Documentation</h2>

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

<p><ul>
<li>Information on the <a href="sqlite.html">sqlite</a>
    command-line utility.</li>
<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="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>
</ul>
</p>

Changes to www/opcode.tcl.

1
2
3
4
5
6
7
8
9
10
11
...
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
#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: opcode.tcl,v 1.5 2001/09/20 01:44:44 drh Exp $}

puts {<html>
<head>
  <title>SQLite Virtual Machine Opcodes</title>
</head>
<body bgcolor=white>
<h1 align=center>
................................................................................
Code {
$ (((sqlite ex1)))
sqlite> (((.explain)))
sqlite> (((explain delete from tbl1 where two<20;)))
addr  opcode        p1     p2     p3                                      
----  ------------  -----  -----  ----------------------------------------
0     Transaction   0      0                                              
1     VerifyCookie  990    0                                              
2     ListOpen      0      0                                              
3     Open          0      31     tbl1                                    
4     VerifyCookie  990    0                                              
5     Rewind        0      0                                              
6     Next          0      13                                             
7     Column        0      1                                              
8     Integer       20     0                                              
9     Ge            0      6                                              
10    Recno         0      0                                              
11    ListWrite     0      0                                              
12    Goto          0      6                                              
13    Close         0      0                                              
14    ListRewind    0      0                                              
15    Open          0      31                                             

16    ListRead      0      20                                             
17    MoveTo        0      0                                              
18    Delete        0      0                                              
19    Goto          0      16                                             
20    ListClose     0      0                                              
21    Commit        0      0                                              
}

puts {
<p>All you have to do is add the "EXPLAIN" keyword to the front of the
SQL statement.  But if you use the ".explain" command to <b>sqlite</b>
first, it will set up the output mode to make the program more easily
viewable.</p>



|







 







|

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







1
2
3
4
5
6
7
8
9
10
11
...
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
#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: opcode.tcl,v 1.6 2001/09/28 23:11:24 drh Exp $}

puts {<html>
<head>
  <title>SQLite Virtual Machine Opcodes</title>
</head>
<body bgcolor=white>
<h1 align=center>
................................................................................
Code {
$ (((sqlite ex1)))
sqlite> (((.explain)))
sqlite> (((explain delete from tbl1 where two<20;)))
addr  opcode        p1     p2     p3                                      
----  ------------  -----  -----  ----------------------------------------
0     Transaction   0      0                                              
1     VerifyCookie  219    0                                              
2     ListOpen      0      0                                              
3     Open          0      3      tbl1                                    

4     Rewind        0      0                                              
5     Next          0      12                                             
6     Column        0      1                                              
7     Integer       20     0                                              
8     Ge            0      5                                              
9     Recno         0      0                                              
10    ListWrite     0      0                                              
11    Goto          0      5                                              
12    Close         0      0                                              
13    ListRewind    0      0                                              

14    OpenWrite     0      3                                              
15    ListRead      0      19                                             
16    MoveTo        0      0                                              
17    Delete        0      0                                              
18    Goto          0      15                                             
19    ListClose     0      0                                              
20    Commit        0      0                                              
}

puts {
<p>All you have to do is add the "EXPLAIN" keyword to the front of the
SQL statement.  But if you use the ".explain" command to <b>sqlite</b>
first, it will set up the output mode to make the program more easily
viewable.</p>

Changes to www/speed.tcl.

1
2
3
4
5
6
7
8
9
10
11
..
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
..
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
#
# Run this Tcl script to generate the speed.html file.
#
set rcsid {$Id: speed.tcl,v 1.2 2001/09/25 02:04:29 drh Exp $ }

puts {<html>
<head>
  <title>Database Speed Comparison: SQLite versus PostgreSQL</title>
</head>
<body bgcolor=white>
<h1 align=center>
................................................................................
</p>

<ul>
<li><p>
  SQLite 2.0 is significantly faster than both SQLite 1.0 and PostgreSQL
  for most common operations.
  SQLite 2.0 is over 4 times faster than PostgreSQL for simple
  query operations about 7 times faster for <b>INSERT</b> statements 
  within a transaction.
</p></li>
<li><p>
  PostgreSQL performs better on complex queries, possibly due to having
  a more sophisticated query optimizer.
</p></li>
<li><p>
................................................................................
important in SQLite version 2.0.  SQLite 2.0 contains some expensive
"assert()" statements in the inner loop of its processing.  Setting
the NDEBUG macro makes SQLite 2.0 run nearly twice as fast.
</p>

<p>
All tests are conducted on an otherwise quiescent machine.
A simple shell script generates and runs all the tests.
The shell script is named <a href="speedtest3.sh">speedtest3.sh</a>.
Each test reports three different times:
</p>

<p>
<ol>
<li> "<b>Real</b>" or wall-clock time. </li>
<li> "<b>User</b>" time, the time spent executing user-level code. </li>



|







 







|







 







|
<







1
2
3
4
5
6
7
8
9
10
11
..
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
..
71
72
73
74
75
76
77
78

79
80
81
82
83
84
85
#
# Run this Tcl script to generate the speed.html file.
#
set rcsid {$Id: speed.tcl,v 1.3 2001/09/28 23:11:24 drh Exp $ }

puts {<html>
<head>
  <title>Database Speed Comparison: SQLite versus PostgreSQL</title>
</head>
<body bgcolor=white>
<h1 align=center>
................................................................................
</p>

<ul>
<li><p>
  SQLite 2.0 is significantly faster than both SQLite 1.0 and PostgreSQL
  for most common operations.
  SQLite 2.0 is over 4 times faster than PostgreSQL for simple
  query operations and about 7 times faster for <b>INSERT</b> statements 
  within a transaction.
</p></li>
<li><p>
  PostgreSQL performs better on complex queries, possibly due to having
  a more sophisticated query optimizer.
</p></li>
<li><p>
................................................................................
important in SQLite version 2.0.  SQLite 2.0 contains some expensive
"assert()" statements in the inner loop of its processing.  Setting
the NDEBUG macro makes SQLite 2.0 run nearly twice as fast.
</p>

<p>
All tests are conducted on an otherwise quiescent machine.
A simple shell script was used to generate and run all the tests.

Each test reports three different times:
</p>

<p>
<ol>
<li> "<b>Real</b>" or wall-clock time. </li>
<li> "<b>User</b>" time, the time spent executing user-level code. </li>

Changes to www/sqlite.tcl.

1
2
3
4
5
6
7
8
9
10
11
..
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
..
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
..
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
#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: sqlite.tcl,v 1.14 2001/09/28 18:14:17 drh Exp $}

puts {<html>
<head>
  <title>sqlite: A program of interacting with SQLite databases</title>
</head>
<body bgcolor=white>
<h1 align=center>
................................................................................
<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.  It's as
simple as that!</p>

<p>For example, to create a new SQLite database named "ex1" 
with a single table named "tbl1", you might do this:</p>
}

proc Code {body} {
  puts {<blockquote><pre>}
................................................................................
  regsub -all {\)\)\)} $body {</u></font>} body
  puts $body
  puts {</pre></blockquote>}
}

Code {
$ (((sqlite ex1)))

Enter ".help" for instructions
sqlite> (((create table tbl1(one varchar(10), two smallint);)))
sqlite> (((insert into tbl1 values('hello!',10);)))
sqlite> (((insert into tbl1 values('goodbye', 20);)))
sqlite> (((select * from tbl1;)))
hello!|10
goodbye|20
sqlite>
}

puts {
<p>(In the example above, and in all subsequent examples, the commands
you type are underlined shown with a green tint and the responses
from the computer are shown in black without underlining.)</p>

<p>You can terminate the sqlite program by typing your systems
End-Of-File character (usually a Control-D) or the interrupt
character (usually a Control-C).</p>

<p>Make sure you type a semicolon at the end of each SQL command!
................................................................................
   ...> (((  f2 text,)))
   ...> (((  f3 real)))
   ...> ((();)))
sqlite> 
}

puts {
<p>If you exit sqlite and look at the contents of the directory "ex1"
you'll see that it now contains two files: <b>sqlite_master.tcl</b>
and <b>tbl1.tbl</b>.  The <b>tbl1.tbl</b> file contains all the
data for table "tbl1" in your database.  The file
<b>sqlite_master.tbl</b> is a special table found on all SQLite
databases that records information about all other tables and
indices.  In general, an SQLite database will contain one "*.tbl"
file for each table and index in your database, plus the extra
"sqlite_master.tbl" file used to store the database schema.</p>

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



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

Code {
$ (((sqlite ex1)))

Enter ".help" for instructions
sqlite> (((select * from sqlite_master;)))
type = table
name = tbl1
tbl_name = tbl1

sql = create table tbl1(one varchar(10), two smallint)
sqlite>
}

puts {
<p>
But you cannot execute DROP TABLE, UPDATE, INSERT or DELETE against
the sqlite_master table.  The sqlite_master



|







 







|
<







 







>












|







 







<
<
<
<
<
<
<
<
<



>
>
|






>


|
|

>
|







1
2
3
4
5
6
7
8
9
10
11
..
24
25
26
27
28
29
30
31

32
33
34
35
36
37
38
..
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
..
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
#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: sqlite.tcl,v 1.15 2001/09/28 23:11:24 drh Exp $}

puts {<html>
<head>
  <title>sqlite: A program of interacting with SQLite databases</title>
</head>
<body bgcolor=white>
<h1 align=center>
................................................................................
<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>


<p>For example, to create a new SQLite database named "ex1" 
with a single table named "tbl1", you might do this:</p>
}

proc Code {body} {
  puts {<blockquote><pre>}
................................................................................
  regsub -all {\)\)\)} $body {</u></font>} body
  puts $body
  puts {</pre></blockquote>}
}

Code {
$ (((sqlite ex1)))
SQLite version 2.0.0
Enter ".help" for instructions
sqlite> (((create table tbl1(one varchar(10), two smallint);)))
sqlite> (((insert into tbl1 values('hello!',10);)))
sqlite> (((insert into tbl1 values('goodbye', 20);)))
sqlite> (((select * from tbl1;)))
hello!|10
goodbye|20
sqlite>
}

puts {
<p>(In the example above, and in all subsequent examples, the commands
you type are underlined and shown with a green tint and the responses
from the computer are shown in black without underlining.)</p>

<p>You can terminate the sqlite program by typing your systems
End-Of-File character (usually a Control-D) or the interrupt
character (usually a Control-C).</p>

<p>Make sure you type a semicolon at the end of each SQL command!
................................................................................
   ...> (((  f2 text,)))
   ...> (((  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>
}

Code {
$ (((sqlite ex1)))
SQlite vresion 2.0.0
Enter ".help" for instructions
sqlite> (((select * from sqlite_master;)))
    type = table
    name = tbl1
tbl_name = tbl1
rootpage = 3
     sql = create table tbl1(one varchar(10), two smallint)
sqlite>
}

puts {
<p>
But you cannot execute DROP TABLE, UPDATE, INSERT or DELETE against
the sqlite_master table.  The sqlite_master

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
26
27
28
29
#
# Run this Tcl script to generate the vdbe.html file.
#
set rcsid {$Id: vdbe.tcl,v 1.6 2000/11/28 20:46:41 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] GMT)
</p>"

# puts {
# <blockquote><font color="red"><b>This document is
# currently under development.  It is incomplete and contains




# errors.  Use it accordingly.</b></font></blockquote>
# }


puts {
<p>If you want to know how the SQLite library works internally,
you need to begin with a solid understanding of the Virtual Database
Engine or VDBE.  The VDBE occurs right in the middle of the
processing stream (see the <a href="arch.html">architecture diagram</a>)
and so it seems to touch most parts of the library.  Even



|













|
|
|
>
>
>
>
|
<
>







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 vdbe.html file.
#
set rcsid {$Id: vdbe.tcl,v 1.7 2001/09/28 23:11:24 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] GMT)
</p>"

puts {
<blockquote><font color="red"><b>
This document describes the
virtual machine used in SQLite version 1.0.  It has not been
updated to reflect important changes that have occurred for
version 2.0.  Some of the information presented below is
obsolete and/or incorrect.  Use it accordingly.
</b></font></blockquote>

}

puts {
<p>If you want to know how the SQLite library works internally,
you need to begin with a solid understanding of the Virtual Database
Engine or VDBE.  The VDBE occurs right in the middle of the
processing stream (see the <a href="arch.html">architecture diagram</a>)
and so it seems to touch most parts of the library.  Even