SQLite

Check-in [ab21de5fdd]
Login

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

Overview
Comment:Documentation updates. (CVS 1037)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: ab21de5fdd8e45be19411cdba647eada0bc9a9f1
User & Date: drh 2003-06-29 16:11:13.000
Context
2003-06-29
16:16
Bump the version number in preparation for the next release. (CVS 1038) (check-in: 0532e1a8f5 user: drh tags: trunk)
16:11
Documentation updates. (CVS 1037) (check-in: ab21de5fdd user: drh tags: trunk)
2003-06-28
16:25
Minor change to a comment in encode.c. (CVS 1036) (check-in: 3f252b72c9 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to www/fileformat.tcl. 
1
2
3
4
5
6
7
8
9
10
11

#
# Run this script to generated a fileformat.html output file
#
set rcsid {$Id: fileformat.tcl,v 1.8 2003/02/13 02:54:04 drh Exp $}

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




|








1
2
3
4
5
6
7
8
9
10
11

#
# Run this script to generated a fileformat.html output file
#
set rcsid {$Id: fileformat.tcl,v 1.9 2003/06/29 16:11:13 drh Exp $}

puts {<html>
<head>
  <title>SQLite Database File Format</title>
</head>
<body bgcolor="white">
<h1 align="center">
︙︙
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86


<p>
A journal file begins with 8 bytes as follows:
0xd9, 0xd5, 0x05, 0xf9, 0x20, 0xa1, 0x63, and 0xd6.
Processes that are attempting to rollback a journal use these 8 bytes
as a sanity check to make sure the file they think is a journal really
is a valid journal.  Prior version of SQLite used different journal
file formats.  The magic numbers for these prior formats is differ
so that if a new version of the library attempts to rollback a journal
created by an earlier version, it can detect that the journal uses
an obsolete format and make the necessary adjustments.  This article
describes only the newest journal format - supported as of version
2.8.0.
</p>









|








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


<p>
A journal file begins with 8 bytes as follows:
0xd9, 0xd5, 0x05, 0xf9, 0x20, 0xa1, 0x63, and 0xd6.
Processes that are attempting to rollback a journal use these 8 bytes
as a sanity check to make sure the file they think is a journal really
is a valid journal.  Prior version of SQLite used different journal
file formats.  The magic numbers for these prior formats are different
so that if a new version of the library attempts to rollback a journal
created by an earlier version, it can detect that the journal uses
an obsolete format and make the necessary adjustments.  This article
describes only the newest journal format - supported as of version
2.8.0.
</p>
︙︙
Changes to www/formatchng.tcl. 
1
2
3
4
5
6
7
8
9
10
11

#
# Run this Tcl script to generate the formatchng.html file.
#
set rcsid {$Id: formatchng.tcl,v 1.8 2003/02/13 02:54:04 drh Exp $ }

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




|








1
2
3
4
5
6
7
8
9
10
11

#
# Run this Tcl script to generate the formatchng.html file.
#
set rcsid {$Id: formatchng.tcl,v 1.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>
︙︙
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164

  string contains the substrings "char" or "clob" or "blob" or "text".
  Otherwise they are of type "numeric".</p>

  <p>Because "text" columns have a different sort order from numeric,
  indices on "text" columns occur in a different order for version
  2.7.0 and later database.  Hence version 2.6.3 and earlier of SQLite 
  will be unable to read a 2.7.0 or later database.  But version 2.7.0
  and later of SQLite will read earlier database version.</p>
  </td>
</tr>
<tr>
  <td valign="top">2.7.6 to 2.8.0</td>
  <td valign="top">2003-Feb-14</td>
  <td><p>Version 2.8.0 introduces a change to the format of the rollback
  journal file.  The main database file format is unchanged.  Versions








|








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

  string contains the substrings "char" or "clob" or "blob" or "text".
  Otherwise they are of type "numeric".</p>

  <p>Because "text" columns have a different sort order from numeric,
  indices on "text" columns occur in a different order for version
  2.7.0 and later database.  Hence version 2.6.3 and earlier of SQLite 
  will be unable to read a 2.7.0 or later database.  But version 2.7.0
  and later of SQLite will read earlier databases.</p>
  </td>
</tr>
<tr>
  <td valign="top">2.7.6 to 2.8.0</td>
  <td valign="top">2003-Feb-14</td>
  <td><p>Version 2.8.0 introduces a change to the format of the rollback
  journal file.  The main database file format is unchanged.  Versions
︙︙
Changes to www/opcode.tcl. 
1
2
3
4
5
6
7
8
9
10
11

#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: opcode.tcl,v 1.11 2003/06/02 06:29:31 jplyon Exp $}

puts {<html>
<head>
  <title>SQLite Virtual Machine Opcodes</title>
</head>
<body bgcolor=white>
<h1 align=center>




|








1
2
3
4
5
6
7
8
9
10
11

#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: 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>
︙︙
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170


171
172
173
174
175
176
177
178
179

like a query result.  This feature is useful for debugging and
for learning how the virtual machine operates.</p>

<p>You can use the <b>sqlite</b> command-line tool to see the
instructions generated by an SQL statement.  The following is
an example:</p>}


proc Code {body} {
  puts {<blockquote><pre>}
  regsub -all {&} [string trim $body] {\&amp;} body
  regsub -all {>} $body {\&gt;} body
  regsub -all {<} $body {\&lt;} body
  regsub -all {\(\(\(} $body {<font color="#00671f"><u>} body
  regsub -all {\)\)\)} $body {</u></font>} body


  puts $body
  puts {</pre></blockquote>}
}

Code {
$ (((sqlite ex1)))
sqlite> (((.explain)))
sqlite> (((explain delete from tbl1 where two<20;)))
addr  opcode        p1     p2     p3                                      








<

|



|
|
>
>

|








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

like a query result.  This feature is useful for debugging and
for learning how the virtual machine operates.</p>

<p>You can use the <b>sqlite</b> command-line tool to see the
instructions generated by an SQL statement.  The following is
an example:</p>}


proc Code {body} {
  puts {<blockquote><tt>}
  regsub -all {&} [string trim $body] {\&amp;} body
  regsub -all {>} $body {\&gt;} body
  regsub -all {<} $body {\&lt;} body
  regsub -all {\(\(\(} $body {<b>} body
  regsub -all {\)\)\)} $body {</b>} body
  regsub -all { } $body {\&nbsp;} body
  regsub -all \n $body <br>\n body
  puts $body
  puts {</tt></blockquote>}
}

Code {
$ (((sqlite ex1)))
sqlite> (((.explain)))
sqlite> (((explain delete from tbl1 where two<20;)))
addr  opcode        p1     p2     p3
︙︙
Changes to www/speed.tcl. 
1
2
3
4
5
6
7
8
9
10
11

#
# Run this Tcl script to generate the speed.html file.
#
set rcsid {$Id: speed.tcl,v 1.12 2003/06/05 15:05:35 drh Exp $ }

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




|








1
2
3
4
5
6
7
8
9
10
11

#
# Run this Tcl script to generate the 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>
︙︙
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

conclusions drawn from these experiments:
</p>

<ul>
<li><p>
  SQLite 2.7.6 is significantly faster (sometimes as much as 10 or
  20 times faster) than the default PostgreSQL 7.1.3 installation
  on RedHat 7.3 for most common operations.  
</p></li>
<li><p>
  SQLite 2.7.6 is often faster (sometimes
  more than twice as fast) than MySQL 3.23.41
  for most common operations.
</p></li>
<li><p>








|








24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

conclusions drawn from these experiments:
</p>

<ul>
<li><p>
  SQLite 2.7.6 is significantly faster (sometimes as much as 10 or
  20 times faster) than the default PostgreSQL 7.1.3 installation
  on RedHat 7.2 for most common operations.  
</p></li>
<li><p>
  SQLite 2.7.6 is often faster (sometimes
  more than twice as fast) than MySQL 3.23.41
  for most common operations.
</p></li>
<li><p>
︙︙
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 tablel</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>








|








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>
︙︙
Changes to www/sqlite.tcl. 
1
2
3
4
5
6
7
8
9
10
11

#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: sqlite.tcl,v 1.20 2003/05/10 02:54:57 jplyon Exp $}

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




|








1
2
3
4
5
6
7
8
9
10
11

#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: 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>
︙︙
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

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 {&} [string trim $body] {\&amp;} body
  regsub -all {>} $body {\&gt;} body
  regsub -all {<} $body {\&lt;} body
  regsub -all {\(\(\(} $body {<font color="#00671f"><u>} body
  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!
The sqlite looks for a semicolon to know when your SQL command is
complete.  If you omit the semicolon, sqlite will give you a








|



|
|
>
>

|
















<
<
<
<








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

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><tt>}
  regsub -all {&} [string trim $body] {\&amp;} body
  regsub -all {>} $body {\&gt;} body
  regsub -all {<} $body {\&lt;} body
  regsub -all {\(\(\(} $body {<b>} body
  regsub -all {\)\)\)} $body {</b>} body
  regsub -all { } $body {\&nbsp;} body
  regsub -all \n $body <br>\n body
  puts $body
  puts {</tt></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>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!
The sqlite looks for a semicolon to know when your SQL command is
complete.  If you omit the semicolon, sqlite will give you a
︙︙
Changes to www/vdbe.tcl. 
1
2
3
4
5
6
7
8
9
10
11

#
# Run this Tcl script to generate the vdbe.html file.
#
set rcsid {$Id: vdbe.tcl,v 1.10 2003/06/07 08:57:58 jplyon Exp $}

puts {<html>
<head>
  <title>The Virtual Database Engine of SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>




|








1
2
3
4
5
6
7
8
9
10
11

#
# Run this Tcl script to generate the 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>
︙︙
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115


116
117
118
119
120
121
122
123
124

is designed to work with VDBE program dumps by entering the
".explain" command.
Finally, enter the INSERT statement shown above, but precede the
INSERT with the special keyword "EXPLAIN".  The EXPLAIN keyword
will cause <b>sqlite</b> to print the VDBE program rather than 
execute it.  We have:</p>
}

proc Code {body} {
  puts {<blockquote><pre>}
  regsub -all {&} [string trim $body] {\&amp;} body
  regsub -all {>} $body {\&gt;} body
  regsub -all {<} $body {\&lt;} body
  regsub -all {\(\(\(} $body {<font color="#00671f"><u>} body
  regsub -all {\)\)\)} $body {</u></font>} body


  puts $body
  puts {</pre></blockquote>}
}

Code {
$ (((sqlite test_database_1)))
sqlite> (((CREATE TABLE examp(one text, two int);)))
sqlite> (((.explain)))
sqlite> (((EXPLAIN INSERT INTO examp VALUES('Hello, World!',99);)))








<

|



|
|
>
>

|








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

is designed to work with VDBE program dumps by entering the
".explain" command.
Finally, enter the INSERT statement shown above, but precede the
INSERT with the special keyword "EXPLAIN".  The EXPLAIN keyword
will cause <b>sqlite</b> to print the VDBE program rather than 
execute it.  We have:</p>
}

proc Code {body} {
  puts {<blockquote><tt>}
  regsub -all {&} [string trim $body] {\&amp;} body
  regsub -all {>} $body {\&gt;} body
  regsub -all {<} $body {\&lt;} body
  regsub -all {\(\(\(} $body {<b>} body
  regsub -all {\)\)\)} $body {</b>} body
  regsub -all { } $body {\&nbsp;} body
  regsub -all \n $body <br>\n body
  puts $body
  puts {</tt></blockquote>}
}

Code {
$ (((sqlite test_database_1)))
sqlite> (((CREATE TABLE examp(one text, two int);)))
sqlite> (((.explain)))
sqlite> (((EXPLAIN INSERT INTO examp VALUES('Hello, World!',99);)))
︙︙