Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Documentation updates. (CVS 1037) |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
ab21de5fdd8e45be19411cdba647eada |
| User & Date: | drh 2003-06-29 16:11:13 |
Context
|
2003-06-29
| ||
| 16:16 | Bump the version number in preparation for the next release. (CVS 1038) (check-in: 0532e1a8 user: drh tags: trunk) | |
| 16:11 | Documentation updates. (CVS 1037) (check-in: ab21de5f user: drh tags: trunk) | |
|
2003-06-28
| ||
| 16:25 | Minor change to a comment in encode.c. (CVS 1036) (check-in: 3f252b72 user: drh tags: trunk) | |
Changes
Changes to www/fileformat.tcl.
1
2
3
4
5
6
7
8
9
10
11
..
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
|
#
# 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">
................................................................................
<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>
|
|
|
|
1
2
3
4
5
6
7
8
9
10
11
..
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
|
#
# 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">
................................................................................
<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
...
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
|
#
# 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>
................................................................................
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
|
|
|
|
1
2
3
4
5
6
7
8
9
10
11
...
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
|
#
# 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>
................................................................................
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
...
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
|
#
# 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>
................................................................................
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] {\&} body
regsub -all {>} $body {\>} body
regsub -all {<} $body {\<} 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
|
|
<
|
|
|
>
>
|
|
1
2
3
4
5
6
7
8
9
10
11
...
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
|
#
# 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>
................................................................................
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] {\&} body
regsub -all {>} $body {\>} body
regsub -all {<} $body {\<} body
regsub -all {\(\(\(} $body {<b>} body
regsub -all {\)\)\)} $body {</b>} body
regsub -all { } $body {\ } 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 .. 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 ... 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 |
#
# 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>
................................................................................
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>
................................................................................
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>
|
| | | |
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 ... 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 |
#
# 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>
................................................................................
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>
................................................................................
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 .. 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 .. 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 |
#
# 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>
................................................................................
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] {\&} body
regsub -all {>} $body {\>} body
regsub -all {<} $body {\<} 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> (((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
|
| | | | > > | < < < < |
1 2 3 4 5 6 7 8 9 10 11 .. 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 .. 57 58 59 60 61 62 63 64 65 66 67 68 69 70 |
#
# 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>
................................................................................
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] {\&} body
regsub -all {>} $body {\>} body
regsub -all {<} $body {\<} body
regsub -all {\(\(\(} $body {<b>} body
regsub -all {\)\)\)} $body {</b>} body
regsub -all { } $body {\ } 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> (((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
...
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
|
#
# 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>
................................................................................
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] {\&} body
regsub -all {>} $body {\>} body
regsub -all {<} $body {\<} 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);)))
|
|
<
|
|
|
>
>
|
|
1
2
3
4
5
6
7
8
9
10
11
...
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
|
#
# 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>
................................................................................
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] {\&} body
regsub -all {>} $body {\>} body
regsub -all {<} $body {\<} body
regsub -all {\(\(\(} $body {<b>} body
regsub -all {\)\)\)} $body {</b>} body
regsub -all { } $body {\ } 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);)))
|