Documentation Source Text

Check-in [2ca5d3c1c4]
Login

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

Overview
Comment:Merge fixes from the 3.23.0 branch.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 2ca5d3c1c4d223f32425219e18b6a3dda1627c168fae27eef7e0e4a1affd5580
User & Date: drh 2018-04-10 01:09:53
Context
2018-04-10
12:36
Changes for the 3.23.1 patch release. check-in: 21ae80926f user: drh tags: trunk
01:09
Merge fixes from the 3.23.0 branch. check-in: 2ca5d3c1c4 user: drh tags: trunk
00:29
Fix a typo in the whynotgit.html document. Leaf check-in: 737022637a user: drh tags: version-3.23
00:21
Add the Why SQLite Does Not Use Git document. check-in: 1b4a90faa0 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Show Whitespace Changes Patch

Changes to pages/cli.in.

912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
For command-line usage, add the short style command-line options immediately
following the "-A", without an intervening space.  All subsequent arguments
are considered to be part of the .archive command.  For example, the following 
commands are equivalent:

<codeblock>
sqlite3 new_archive.db -Acv file1 file2 file3
sqlite3 new_archive.db ".ar -tv file1 file2 file3"
</codeblock>

<p>
Long and short style options may be mixed. For example, the following are
equivalent:

<codeblock>







|







912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
For command-line usage, add the short style command-line options immediately
following the "-A", without an intervening space.  All subsequent arguments
are considered to be part of the .archive command.  For example, the following 
commands are equivalent:

<codeblock>
sqlite3 new_archive.db -Acv file1 file2 file3
sqlite3 new_archive.db ".ar -cv file1 file2 file3"
</codeblock>

<p>
Long and short style options may be mixed. For example, the following are
equivalent:

<codeblock>

Changes to pages/cpu.in.

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

<table_of_contents>

<h1>Overview</h1>

<p>The graph below shows the number of CPU cycles used by SQLite on a
standard workload, for all versions of SQLite going back about 9 years.
Recent version so SQLite use less then a third of the CPU cycles 
compared to older versions.

<p>
This article describes how the SQLite developers measure CPU usage,
what those measurements actually mean, and the techniques used by
SQLite developers on their continuing quest to further reduce the
CPU usage of the SQLite library.







|







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

<table_of_contents>

<h1>Overview</h1>

<p>The graph below shows the number of CPU cycles used by SQLite on a
standard workload, for all versions of SQLite going back about 9 years.
Recent versions of SQLite use less than a third of the CPU cycles 
compared to older versions.

<p>
This article describes how the SQLite developers measure CPU usage,
what those measurements actually mean, and the techniques used by
SQLite developers on their continuing quest to further reduce the
CPU usage of the SQLite library.

Changes to pages/howtocorrupt.in.

227
228
229
230
231
232
233

















234
235
236
237
238
239
240

<p>Beginning with SQLite [version 3.10.0] ([dateof:3.10.0]), 
the unix OS interface will
attempt to resolve symbolic links and open the database file by its
canonical name.  Prior to version 3.10.0, opening a database file 
through a symbolic link was similar to opening a database file
that had multiple hard links and resulted in undefined behavior.</p>


















<h1> Failure to sync</h1>

<p>In order to guarantee that database files are always consistent, SQLite
will occasionally ask the operating system to flush all pending writes to
persistent storage then wait for that flush to complete.  This is 
accomplished using the <tt>fsync()</tt> system call under unix and







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







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

<p>Beginning with SQLite [version 3.10.0] ([dateof:3.10.0]), 
the unix OS interface will
attempt to resolve symbolic links and open the database file by its
canonical name.  Prior to version 3.10.0, opening a database file 
through a symbolic link was similar to opening a database file
that had multiple hard links and resulted in undefined behavior.</p>

<tcl>hd_fragment fork</tcl>
<h2> Carrying an open database connection across a fork() </h2>

<p> Do not open an SQLite database connection, then fork(), then try
to use that database connection in the child process.  All kinds of
locking problems will result and you can easily end up with a corrupt
database.  SQLite is not designed to support that kind of behavior.
Any database connection that is used in a child process must be opened
in the child process, not inherited from the parent.

<p> Do not even call [sqlite3_close()] on a database connection from a
child process if the connection was opened in the parent.  It is safe
to close the underlying file descriptor, but the [sqlite3_close()]
interface might invoke cleanup activities that will delete content out
from under the parent, leading to errors and perhaps even database
corruption.

<h1> Failure to sync</h1>

<p>In order to guarantee that database files are always consistent, SQLite
will occasionally ask the operating system to flush all pending writes to
persistent storage then wait for that flush to complete.  This is 
accomplished using the <tt>fsync()</tt> system call under unix and

Changes to pages/lang.in.

835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
...
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
limited to the value set by
[sqlite3_limit]([SQLITE_LIMIT_COLUMN],...).)^</p>

<tcl>hd_fragment uniqueidx {unique index}</tcl>
<p>^If the UNIQUE keyword appears between CREATE and INDEX then duplicate
index entries are not allowed.  ^Any attempt to insert a duplicate entry
will result in an error.  ^For the purposes of unique indices, all NULL values
are considered to different from all other NULL values and are thus unique.
This is one of the two possible interpretations of the SQL-92 standard
(the language in the standard is ambiguous) and is the interpretation
followed by PostgreSQL, MySQL, Firebird, and Oracle.  Informix and
Microsoft SQL Server follow the other interpretation of the standard.</p>

<p>^If the optional IF NOT EXISTS clause is present and another index
with the same name already exists, then this command becomes a no-op.</p>
................................................................................

<center><table border=1>
  <tr><th>Expression Affinity   <th>Column Declared Type
  <tr><td>TEXT                  <td>"TEXT"
  <tr><td>NUMERIC               <td>"NUM"
  <tr><td>INTEGER               <td>"INT"
  <tr><td>REAL                  <td>"REAL"
  <tr><td>NONE                  <td>"" (empty string)
</table></center>)^

<p>^(A table created using CREATE TABLE AS has no PRIMARY KEY and no
constraints of any kind. The default value of each column is NULL. The default
collation sequence for each column of the new table is BINARY.)^

<p>^Tables created using CREATE TABLE AS are initially populated with the







|







 







|







835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
...
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
limited to the value set by
[sqlite3_limit]([SQLITE_LIMIT_COLUMN],...).)^</p>

<tcl>hd_fragment uniqueidx {unique index}</tcl>
<p>^If the UNIQUE keyword appears between CREATE and INDEX then duplicate
index entries are not allowed.  ^Any attempt to insert a duplicate entry
will result in an error.  ^For the purposes of unique indices, all NULL values
are considered different from all other NULL values and are thus unique.
This is one of the two possible interpretations of the SQL-92 standard
(the language in the standard is ambiguous) and is the interpretation
followed by PostgreSQL, MySQL, Firebird, and Oracle.  Informix and
Microsoft SQL Server follow the other interpretation of the standard.</p>

<p>^If the optional IF NOT EXISTS clause is present and another index
with the same name already exists, then this command becomes a no-op.</p>
................................................................................

<center><table border=1>
  <tr><th>Expression Affinity   <th>Column Declared Type
  <tr><td>TEXT                  <td>"TEXT"
  <tr><td>NUMERIC               <td>"NUM"
  <tr><td>INTEGER               <td>"INT"
  <tr><td>REAL                  <td>"REAL"
  <tr><td>BLOB (a.k.a "NONE")   <td>"" (empty string)
</table></center>)^

<p>^(A table created using CREATE TABLE AS has no PRIMARY KEY and no
constraints of any kind. The default value of each column is NULL. The default
collation sequence for each column of the new table is BINARY.)^

<p>^Tables created using CREATE TABLE AS are initially populated with the

Changes to pages/whentouse.in.

63
64
65
66
67
68
69




70
71
72
73
74
75
76
...
151
152
153
154
155
156
157

158


159


















160
161

162
163
164
165
166
167
168
...
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317

<p>There are many benefits to this approach, including improved
performance, reduced cost and complexity, and
improved reliability.  See technical notes
[file-format benefits|"aff_short.html"] and 
[application file-format|"appfileformat.html"] and
[faster than the filesystem|"fasterthanfs.html"] for more information.





<tcl>hd_fragment website {using SQLite for websites}</tcl>
<li><p><b>Websites</b></p>

<p>SQLite works great as the database engine for most low to
medium traffic websites (which is to say, most websites).
The amount of web traffic that SQLite can handle depends
................................................................................
SQL database engine in this scenario.
Database requests are serialized by the server, so concurrency is not
an issue.  Concurrency is also improved by "database sharding":
using separate database files for different subdomains.  For
example, the server might have a separate SQLite database for each
user, so that the server can handle hundreds or thousands of simultaneous
connections, but each SQLite database is only used by one connection.</p>























</li>


<li><p><b>File archive and/or data container</b></p>

<p>
The [SQLite Archive] idea shows how
SQLite can be used as a substitute for ZIP archives or Tarballs.
An archive of files stored in SQLite is only very slightly larger, and
in some cases actually smaller, than the equivalent ZIP archive.
................................................................................
</li>

<li><p><b>High Concurrency</b></p>

<p>
SQLite supports an unlimited number of simultaneous readers, but it 
will only allow one writer at any instant in time.
For many situations, this is not a problem.  Writer queue up. Each application
does its database work quickly and moves on, and no lock lasts for more
than a few dozen milliseconds. But there are some applications that require
more concurrency, and those applications may need to seek a different
solution.
</p>
</li>








>
>
>
>







 







>

>
>

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


>







 







|







63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
...
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
...
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343

<p>There are many benefits to this approach, including improved
performance, reduced cost and complexity, and
improved reliability.  See technical notes
[file-format benefits|"aff_short.html"] and 
[application file-format|"appfileformat.html"] and
[faster than the filesystem|"fasterthanfs.html"] for more information.
This use case is closely related to the
<a href="#wireproto">data transfer format</a> and
<a href="#container">data container</a> use cases below.
</li>

<tcl>hd_fragment website {using SQLite for websites}</tcl>
<li><p><b>Websites</b></p>

<p>SQLite works great as the database engine for most low to
medium traffic websites (which is to say, most websites).
The amount of web traffic that SQLite can handle depends
................................................................................
SQL database engine in this scenario.
Database requests are serialized by the server, so concurrency is not
an issue.  Concurrency is also improved by "database sharding":
using separate database files for different subdomains.  For
example, the server might have a separate SQLite database for each
user, so that the server can handle hundreds or thousands of simultaneous
connections, but each SQLite database is only used by one connection.</p>
</li>

<tcl>hd_fragment wireproto {data transfer format}</tcl>
<li><p><b>Data transfer format</b><p>

<p>Because an SQLite database is a single compact file in a
[file format|well-defined cross-platform format], it is often used
as a container for transfering content from one system to another.
The sender gathers content into an SQLite database file, transfers
that one file to the receiver, then the receiver uses SQL to extract
the content as needed.

<p>An SQLite database facilitates data transfer between systems even
when the endpoints have different word sizes and/or byte orders.
The data can be a complex mix of large binary blobs, text, and small
numeric or boolean values.  The data format can be easily extended
by adding new tables and/or columns, without breaking legacy receivers.
The SQL query language means that receivers are not required to parse
the entire transfer all at once, but can instead query the
received content as needed.  The data format is "transparent" in the
sense that it is easily decoded for human viewing using 
a variety of universally available, open-source tools, from multiple
vendors.
</li>

<tcl>hd_fragment container {data container}</tcl>
<li><p><b>File archive and/or data container</b></p>

<p>
The [SQLite Archive] idea shows how
SQLite can be used as a substitute for ZIP archives or Tarballs.
An archive of files stored in SQLite is only very slightly larger, and
in some cases actually smaller, than the equivalent ZIP archive.
................................................................................
</li>

<li><p><b>High Concurrency</b></p>

<p>
SQLite supports an unlimited number of simultaneous readers, but it 
will only allow one writer at any instant in time.
For many situations, this is not a problem.  Writers queue up. Each application
does its database work quickly and moves on, and no lock lasts for more
than a few dozen milliseconds. But there are some applications that require
more concurrency, and those applications may need to seek a different
solution.
</p>
</li>

Changes to pages/whynotgit.in.

1
2
3
4
5
6
7
8
<title>Why SQLite Does Not Use Git?</title>

<table_of_contents>

<h1>Introduction</h1>

<p>
SQLite does not use the
|







1
2
3
4
5
6
7
8
<title>Why SQLite Does Not Use Git</title>

<table_of_contents>

<h1>Introduction</h1>

<p>
SQLite does not use the