1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
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
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
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
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
|
<!DOCTYPE html>
<html><head>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<link href="sqlite.css" rel="stylesheet">
<title>Benefits of SQLite As A File Format</title>
<!-- path= -->
</head>
<body>
<div class=nosearch>
<a href="index.html">
<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite" border="0">
</a>
<div><!-- IE hack to prevent disappearing logo --></div>
<div class="tagline desktoponly">
Small. Fast. Reliable.<br>Choose any three.
</div>
<div class="menu mainmenu">
<ul>
<li><a href="index.html">Home</a>
<li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
<li class='wideonly'><a href='about.html'>About</a>
<li class='desktoponly'><a href="docs.html">Documentation</a>
<li class='desktoponly'><a href="download.html">Download</a>
<li class='wideonly'><a href='copyright.html'>License</a>
<li class='desktoponly'><a href="support.html">Support</a>
<li class='desktoponly'><a href="prosupport.html">Purchase</a>
<li class='search' id='search_menubutton'>
<a href="javascript:void(0)" onclick='toggle_search()'>Search</a>
</ul>
</div>
<div class="menu submenu" id="submenu">
<ul>
<li><a href='about.html'>About</a>
<li><a href='docs.html'>Documentation</a>
<li><a href='download.html'>Download</a>
<li><a href='support.html'>Support</a>
<li><a href='prosupport.html'>Purchase</a>
</ul>
</div>
<div class="searchmenu" id="searchmenu">
<form method="GET" action="search">
<select name="s" id="searchtype">
<option value="d">Search Documentation</option>
<option value="c">Search Changelog</option>
</select>
<input type="text" name="q" id="searchbox" value="">
<input type="submit" value="Go">
</form>
</div>
</div>
<script>
function toggle_div(nm) {
var w = document.getElementById(nm);
if( w.style.display=="block" ){
w.style.display = "none";
}else{
w.style.display = "block";
}
}
function toggle_search() {
var w = document.getElementById("searchmenu");
if( w.style.display=="block" ){
w.style.display = "none";
} else {
w.style.display = "block";
setTimeout(function(){
document.getElementById("searchbox").focus()
}, 30);
}
}
function div_off(nm){document.getElementById(nm).style.display="none";}
window.onbeforeunload = function(e){div_off("submenu");}
/* Disable the Search feature if we are not operating from CGI, since */
/* Search is accomplished using CGI and will not work without it. */
if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){
document.getElementById("search_menubutton").style.display = "none";
}
/* Used by the Hide/Show button beside syntax diagrams, to toggle the */
function hideorshow(btn,obj){
var x = document.getElementById(obj);
var b = document.getElementById(btn);
if( x.style.display!='none' ){
x.style.display = 'none';
b.innerHTML='show';
}else{
x.style.display = '';
b.innerHTML='hide';
}
return false;
}
var antiRobot = 0;
function antiRobotGo(){
if( antiRobot!=3 ) return;
antiRobot = 7;
var j = document.getElementById("mtimelink");
if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href");
}
function antiRobotDefense(){
document.body.onmousedown=function(){
antiRobot |= 2;
antiRobotGo();
document.body.onmousedown=null;
}
document.body.onmousemove=function(){
antiRobot |= 2;
antiRobotGo();
document.body.onmousemove=null;
}
setTimeout(function(){
antiRobot |= 1;
antiRobotGo();
}, 100)
antiRobotGo();
}
antiRobotDefense();
</script>
<h1 align="center">
SQLite As An Application File Format
</h1>
<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 <a href="appfileformat.html">separate technical note</a>.)</i></p>
<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 <a href="backup.html">backup API</a>.
</p>
<p>
There are many advantages to using SQLite as an application file format,
including:
</p>
<ol type="1">
<li><b>Better performance</b>
<ul>
<li> Reading and writing from an SQLite database
is often faster than reading and writing individual files from disk.
See <a href="fasterthanfs.html">35% Faster Than The Filesystem</a>
and <a href="intern-v-extern-blob.html">Internal Versus External BLOBs</a>.
<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.
</ul>
<li><b>Reduced application cost and complexity</b>
<ul>
<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
<a href="fts3.html">full-text search</a> and <a href="rtree.html">RTREE</a> indexes and use triggers to implement
an <a href="undoredo.html">automated undo/redo stack</a>.
<li> Performance problems can often be resolved, even late in the
development cycle, using <a href="lang_createindex.html">CREATE INDEX</a>, avoiding costly
redesign, rewrite, and retest efforts.
</ul>
<li><b>Portability</b>
<ul>
<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.
</ul>
<li><b>Reliability</b>
<ul>
<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.
</ul>
<li><b>Accessibility</b>
<ul>
<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.
<li> SQLite database files are <a href="locrsf.html">recommended by the US Library of Congress</a>
as a storage format for long-term preservation of digital content.
</ul>
</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 <a href="pragma.html#pragma_application_id">application_id pragma</a> can be used to set an "Application ID"
integer in the database file so that tools like
<a href="http://www.darwinsys.com/file/">file(1)</a> can determine that the file
is associated with your application and is not just a generic
SQL database.</p>
<p align="center"><small><i>This page last modified on <a href="https://sqlite.org/docsrc/honeypot" id="mtimelink" data-href="https://sqlite.org/docsrc/finfo/pages/aff_short.in?m=e10af018ad">2022-01-08 05:02:57</a> UTC </small></i></p>
|
