Documentation Source Text

Artifact [f8357e153c]

Artifact f8357e153cce34f0b3ab24de79eda63d18e97fa4527150bed025a2466b1ad79b:

<tcl>hd_keywords *affshort {file-format benefits}</tcl>
<title>Benefits of SQLite As A File Format</title>

<h1 align="center">
SQLite As An Application File Format

<p><i>(Note:  The current page is a brief summary of why SQLite makes
a good application file format.  The topic is considered at greater
detail in a [application file-format | separate technical note].)</i></p>

SQLite has been used with great success as the on-disk file format
for desktop applications such as version control systems,
financial analysis tools, media cataloging and editing suites, CAD
packages, record keeping programs, and so forth.  The traditional
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].

There are many advantages to using SQLite as an application file format,

<ol type="1">
<li><b>Better performance</b>
<li> Reading and writing from an SQLite database
     is often faster than reading and writing individual files from disk.
     See [faster than the filesystem|35% Faster Than The Filesystem]
     and [Internal Versus External BLOBs].
<li> The application only has to load the data it needs, rather
     than reading the entire file and holding a complete parse
     in memory.
<li> Small edits only overwrite the parts of the file that change,
     reducing write time and wear on SSD drives.
<li><b>Reduced application cost and complexity</b>
<li> No application file I/O code to write and debug.
<li> Content can be accessed and updated using concise SQL queries instead
     of lengthy and error-prone procedural routines.
<li> The file format can be extended in future releases simply
     by adding new tables and/or column, preserving backwards compatibility.
<li> Applications can leverage the
     [full-text search] and [RTREE] indexes and use triggers to implement
     an [automated undo/redo stack].
<li> Performance problems can often be resolved, even late in the
     development cycle, using [CREATE INDEX], avoiding costly
     redesign, rewrite, and retest efforts.
<li> The application file is portable across all operating systems,
     32-bit and 64-bit and big- and little-endian architectures.
<li> A federation of programs, perhaps all written in different programming
     languages, can 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> Diverse content which might otherwise be stored as a "pile-of-files"
     is encapsulated into a single disk file for simpler transport
     via scp/ftp, USB stick, and/or email attachment.
<li> Content can be updated continuously and atomically so 
     that little or no work is lost in a power failure or crash.
<li> Bugs are far less likely in SQLite than in custom-written file I/O code.
<li> SQL queries are many times smaller than the equivalent procedural
     code, and since the number of bugs per line of code is roughly
     constant, this means fewer bugs overall.
<li> SQLite database content can be viewed using a wide variety
     third-party tools.
<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.

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
[ | file(1)] can determine that the file
is associated with your application and is not just a generic
SQL database.</p>