Documentation Source Text

Check-in [9811172daa]
Login

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

Overview
Comment:Updates to the application-file-format text.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 9811172daa665f1b6ebf20b4faf40005d55f8827
User & Date: drh 2013-05-03 17:42:59.942
Context
2013-05-03
17:51
Still more edits to the application file format text. (check-in: 6ef8771745 user: drh tags: trunk)
17:42
Updates to the application-file-format text. (check-in: 9811172daa user: drh tags: trunk)
15:43
Add documentation for the Application ID. Add a proposed release date for 3.7.17. (check-in: 0d800b73f2 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/intern-v-extern-blob.in. 

1
2
3
4
5
6
7


<title>Internal Versus External BLOBs</title>
<body>
<h1 align="center">
Internal Versus External BLOBs in SQLite
</h1>

<p>

>








1
2
3
4
5
6
7
8

<tcl>hd_keywords {BLOB I/O performance} {Internal Versus External BLOBs}</tcl>
<title>Internal Versus External BLOBs</title>
<body>
<h1 align="center">
Internal Versus External BLOBs in SQLite
</h1>

<p>
︙︙
Changes to pages/whentouse.in.
︙︙
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

<tcl>hd_fragment appfileformat {application file-format}</tcl>
<li><p><b>Application File Format</b></p>

<p>
SQLite has been used with great success as the on-disk file format
for desktop applications such as financial analysis tools, CAD
packages, record keeping programs, and so forth.  The traditional
File/Open operation does an sqlite3_open() and executes a
BEGIN TRANSACTION to get exclusive access to the content.  File/Save
does a COMMIT followed by another BEGIN TRANSACTION.  The use
of transactions guarantees that updates to the application file are atomic,
durable, isolated, and consistent.
</p>

<p>
Temporary triggers can be added to the database to record all
changes into a (temporary) undo/redo log table.  These changes can then
be played back when the user presses the Undo and Redo buttons.  Using
this technique, an unlimited depth undo/redo implementation can be written

in surprisingly little code.
</p>


































</li>










<li><p><b>Embedded devices and applications</b></p>

<p>Because an SQLite database requires little or no administration,
SQLite is a good choice for devices or services that must work
unattended and without human support.  SQLite is a good fit for
use in cellphones, PDAs, set-top boxes, and/or appliances.  It also








|
|
|
|
<



<
<
<
<
>
|

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








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
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126

<tcl>hd_fragment appfileformat {application file-format}</tcl>
<li><p><b>Application File Format</b></p>

<p>
SQLite has been used with great success as the on-disk file format
for desktop applications such as financial analysis tools, CAD
packages, record keeping programs, and so forth.  The traditional
File/Open operation calls sqlite3_open() to attach to the database
file.  Updates to the file happen atomically as the file is edited,
so the File/Save menu option become superfluous.  The File/Save_As
menu option can be implemented using the [backup API].

</p>

<p>




There are many advantages to using SQLite as an application file format,
including:
</p>

<ol type="1">
<li> There is no file parsing and generating code to write and debug.
<li> Content can be accessed and updated using powerful SQL queries,
     greatly reducing the complexity of the application code.
<li> Extending the file format for new capabilities in later releases
     is a simple as adding new tables or new columns to existing tables.
<li> The content can be viewed using third-party tools.
<li> The application file is portable across all operating systems,
     32-bit and 64-bit and big- and little-endian architectures.
<li> The application only has to load as much data as it needs, rather
     than reading the entire application file and holding a complete parse
     in memory.  Startup time and memory consumption are reduced.
<li> Small content edits only overwrite the parts of the file that change,
     not the entire file, thus improving performance
     and reducing wear on SSD drives.
<li> Content is updated continously and atomically so 
     that there is no work lost in the event of a power failure or crash.
<li> Applications can leverage the
     [full-text search] and [RTREE] capablities that are build into SQLite.
<li> Performance problems can often be resolved using [CREATE INDEX]
     rather than redesigning, rewriting, and retesting application code.
<li> A federation of programs, perhaps written in different programming
     languages, can all access the same application file with no 
     compatibility concerns.
<li> Multiple processes can attach to the same application
     file and can read and write without interfering with each another.
<li> Cross-session undo/redo can be implemented using triggers.
<li> In many common cases, loading content from an SQLite database
     can be much faster than loading content out of individual files.
     See [Internal Versus External BLOBs] for additional information.
<li> Content stored in an SQLite database is more likely to be 
     recoverable decades in the future, long after all traces of
     the original application have been lost. Data lives longer than code.
</ol>

<p>
SQLite allows database files to have any desired filename extension, so
an application can choose a custom filename extension for its own use, if
desired.  The [application_id pragma] can be used to set an "Application ID"
integer in the database file so that tools like
[http://www.darwinsys.com/file/ | file(1)] can determine that the file
is associated with your application and is not just a generic
SQL database.</p>

<li><p><b>Embedded devices and applications</b></p>

<p>Because an SQLite database requires little or no administration,
SQLite is a good choice for devices or services that must work
unattended and without human support.  SQLite is a good fit for
use in cellphones, PDAs, set-top boxes, and/or appliances.  It also
︙︙
220
221
222
223
224
225
226
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

database component off onto a separate machine, then you should 
definitely consider using an enterprise-class client/server database
engine instead of SQLite.</p>
</li>

<li><p><b>Very large datasets</b></p>

<p>With the default page size of 1024 bytes, an SQLite database is
limited in size to 2 terabytes (2<sup><small>41</small></sup> bytes).
And even if it could handle larger databases, SQLite stores the entire
database in a single disk file and many filesystems limit the maximum
size of files to something less than this.  So if you are contemplating
databases of this magnitude, you would do well to consider using a
client/server database engine that spreads its content across multiple
disk files, and perhaps across multiple volumes.
</p>
</li>

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

<p>
SQLite uses reader/writer locks on the entire database file.  That means
if any process is reading from any part of the database, all other
processes are prevented from writing any other part of the database.
Similarly, if any one process is writing to the database,
all other processes are prevented from reading any other part of the
database.
For many situations, this is not a problem.  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>

</ul>








|
|












|
|
<
<
<
<










259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281




282
283
284
285
286
287
288
289
290

database component off onto a separate machine, then you should 
definitely consider using an enterprise-class client/server database
engine instead of SQLite.</p>
</li>

<li><p><b>Very large datasets</b></p>

<p>An SQLite database is limited in size to 140 terabytes 
(2<sup><small>47</small></sup> bytes, 128 tibibytes).
And even if it could handle larger databases, SQLite stores the entire
database in a single disk file and many filesystems limit the maximum
size of files to something less than this.  So if you are contemplating
databases of this magnitude, you would do well to consider using a
client/server database engine that spreads its content across multiple
disk files, and perhaps across multiple volumes.
</p>
</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.  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>

</ul>