Documentation Source Text

Check-in [a54eb3903e]
Login

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

Overview
Comment:Enhancements to the "When To Use SQLite" document.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: a54eb3903e8e0e0e497d4469ea821765e15a87f1f1b10c2eab51734b70cf07d2
User & Date: drh 2018-02-12 19:31:26.760
Context
2018-02-13
22:16
Fix an issue with not-found processing in althttpd.c. (check-in: 72c8b8c6ff user: drh tags: trunk)
2018-02-12
19:31
Enhancements to the "When To Use SQLite" document. (check-in: a54eb3903e user: drh tags: trunk)
2018-02-07
16:50
To althttpd.c: Add "signal.h" and a mimetype table entry for "svg" files. (check-in: 2ad7f8fef4 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/whentouse.in.
57
58
59
60
61
62
63
64
65
66
67

68
69
70
71
72
73
74
File/Open operation calls sqlite3_open() to attach to the database
file.  Updates happen automatically as application content is revised
so the File/Save menu option becomes superfluous.  The File/Save_As
menu option can be implemented using the [backup API].
</p>

<p>There are many benefits to this approach, including improved
application performance, reduced cost and complexity, and
improved reliability.  See technical notes
[file-format benefits|here] and 
[application file-format|here] for details.


<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







|

|
|
>







57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
File/Open operation calls sqlite3_open() to attach to the database
file.  Updates happen automatically as application content is revised
so the File/Save menu option becomes superfluous.  The File/Save_As
menu option can be implemented using the [backup API].
</p>

<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
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
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 archives</b></p>

<p>
The [https://www.sqlite.org/sqlar|SQLite Archiver] project 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.
And an SQLite archive features incremental and atomic updating
and the ability to store much richer metadata.
</p>


















<p>SQLite archives are useful as the distribution format for software
or content updates that are broadcast to many clients.  Variations
on this idea are used, for example, to transmit TV programming guides
to set-top boxes and to send over-the-air updates to vehicle navigation
systems.</p>
</li>

<li><p><b>Replacement for <i>ad hoc</i> disk files</b></p>

<p>Many programs use 
[http://man.he.net/man3/fopen | fopen()],
[http://man.he.net/man3/fread | fread()], and 
[http://man.he.net/man3/fwrite | fwrite()] to create and
manage files of data in home-grown formats.  SQLite works 
particularly well as a
replacement for these <i>ad hoc</i> data files.</p>


</li>

<li><p><b>Internal or temporary databases</b></p>

<p>
For programs that have a lot of data that must be sifted and sorted
in diverse ways, it is often easier and quicker to load the data into







|


|







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














|
>
>







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
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 [https://www.sqlite.org/sqlar|SQL Archive] project 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.
And an SQLite archive features incremental and atomic updating
and the ability to store much richer metadata.
</p>

<p>[https://www.fossil-scm.org/|Fossil] version 2.5 and later offers
[https://www.sqlite.org/sqlar|SQL Archive] as a download format, in addition
to traditional tarball and ZIP archive.
The sqlite3.exe [command-line shell] version 3.22.0 and later will create,
list, or unpack an SQL archiving using the 
[.archive command].</p>

<p>
SQLite is a good solution for any situation that requires bundling
diverse content into a self-contained and self-describing package 
for shipment across a network.
Content is encoding in a 
[file format|well-defined, cross-platform, and stable file format].
The encoding is efficient, and receivers can extract small subsets
of the content without having to read and parse the entire file.
</p>

<p>SQL archives are useful as the distribution format for software
or content updates that are broadcast to many clients.  Variations
on this idea are used, for example, to transmit TV programming guides
to set-top boxes and to send over-the-air updates to vehicle navigation
systems.</p>
</li>

<li><p><b>Replacement for <i>ad hoc</i> disk files</b></p>

<p>Many programs use 
[http://man.he.net/man3/fopen | fopen()],
[http://man.he.net/man3/fread | fread()], and 
[http://man.he.net/man3/fwrite | fwrite()] to create and
manage files of data in home-grown formats.  SQLite works 
particularly well as a
replacement for these <i>ad hoc</i> data files.
Contrary to intuition, SQLite can be [faster than the filesystem]
for reading and writing content to disk.
</li>

<li><p><b>Internal or temporary databases</b></p>

<p>
For programs that have a lot of data that must be sifted and sorted
in diverse ways, it is often easier and quicker to load the data into