1
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
3
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
4
<title>Distinctive Features Of SQLite</title>
5
<style type="text/css">
8
font-family: Verdana, sans-serif;
13
a:visited { color: #734559 }
15
.logo { position:absolute; margin:3px; }
31
.toolbar a { color: white; text-decoration: none; padding: 6px 12px; }
32
.toolbar a:visited { color: white; }
33
.toolbar a:hover { color: #044a64; background: white; }
35
.content { margin: 5%; }
36
.content dt { font-weight:bold; }
37
.content dd { margin-bottom: 25px; margin-left:20%; }
38
.content ul { padding:0px; padding-left: 15px; margin:0px; }
41
.se { background: url(images/se.gif) 100% 100% no-repeat #044a64}
42
.sw { background: url(images/sw.gif) 0% 100% no-repeat }
43
.ne { background: url(images/ne.gif) 100% 0% no-repeat }
44
.nw { background: url(images/nw.gif) 0% 0% no-repeat }
46
/* Things for "fancyformat" documents start here. */
47
.fancy img+p {font-style:italic}
48
.fancy .codeblock i { color: darkblue; }
49
.fancy h1,.fancy h2,.fancy h3,.fancy h4 {font-weight:normal;color:#044a64}
50
.fancy h2 { margin-left: 10px }
51
.fancy h3 { margin-left: 20px }
52
.fancy h4 { margin-left: 30px }
53
.fancy th {white-space:nowrap;text-align:left;border-bottom:solid 1px #444}
54
.fancy th, .fancy td {padding: 0.2em 1ex; vertical-align:top}
55
.fancy #toc a { color: darkblue ; text-decoration: none }
56
.fancy .todo { color: #AA3333 ; font-style : italic }
57
.fancy .todo:before { content: 'TODO:' }
58
.fancy p.todo { border: solid #AA3333 1px; padding: 1ex }
59
.fancy img { display:block; }
60
.fancy :link:hover, .fancy :visited:hover { background: wheat }
61
.fancy p,.fancy ul,.fancy ol { margin: 1em 5ex }
62
.fancy li p { margin: 1em 0 }
63
/* End of "fancyformat" specific rules. */
69
<div><!-- container div to satisfy validator -->
72
<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite Logo"
74
<div><!-- IE hack to prevent disappearing logo--></div>
75
<div class="tagline">Small. Fast. Reliable.<br>Choose any three.</div>
77
<table width=100% style="clear:both"><tr><td>
78
<div class="se"><div class="sw"><div class="ne"><div class="nw">
79
<table width=100% style="padding:0;margin:0;cell-spacing:0"><tr>
82
<a href="about.html">About</a>
83
<a href="sitemap.html">Sitemap</a>
84
<a href="docs.html">Documentation</a>
85
<a href="download.html">Download</a>
86
<a href="copyright.html">License</a>
87
<a href="news.html">News</a>
88
<a href="support.html">Support</a>
91
gMsg = "Search SQLite Docs..."
92
function entersearch() {
93
var q = document.getElementById("q");
94
if( q.value == gMsg ) { q.value = "" }
95
q.style.color = "black"
96
q.style.fontStyle = "normal"
98
function leavesearch() {
99
var q = document.getElementById("q");
100
if( q.value == "" ) {
102
q.style.color = "#044a64"
103
q.style.fontStyle = "italic"
108
<div style="padding:0 1em 0px 0;white-space:nowrap">
109
<form name=f method="GET" action="http://www.sqlite.org/search">
110
<input id=q name=q type=text
111
onfocus="entersearch()" onblur="leavesearch()" style="width:24ex;padding:1px 1ex; border:solid white 1px; font-size:0.9em ; font-style:italic;color:#044a64;" value="Search SQLite Docs...">
112
<input type=submit value="Go" style="border:solid white 1px;background-color:#044a64;color:white;font-size:0.9em;padding:0 1ex">
116
</div></div></div></div>
118
<div class=startsearch></div>
121
<h1 align=center>Distinctive Features Of SQLite</h1>
124
This page highlights some of the characteristics of SQLite that are
125
unusual and which make SQLite different from many other SQL
129
<a name="zeroconfig"></a>
130
<p><b>Zero-Configuration</b></p>
132
SQLite does not need to be "installed" before it is used.
133
There is no "setup" procedure. There is no
134
server process that needs to be started, stopped, or configured.
136
no need for an administrator to create a new database instance or assign
137
access permissions to users.
138
SQLite uses no configuration files.
139
Nothing needs to be done to tell the system that SQLite is running.
140
No actions are required to recover after a system crash or power failure.
141
There is nothing to troubleshoot.
145
Other more familiar database engines run great once you get them going.
146
But doing the initial installation and configuration can be
147
intimidatingly complex.
149
<a name="serverless"></a>
150
<p><b>Serverless</b></p>
152
Most SQL database engines are implemented as a separate server
153
process. Programs that want to access the database communicate
154
with the server using some kind of interprocess communication
155
(typically TCP/IP) to send requests to the server and to receive
156
back results. SQLite does not work this way. With SQLite, the
157
process that wants to access the database reads and writes
158
directly from the database files on disk. There is no intermediary
161
There are advantages and disadvantages to being serverless. The
162
main advantage is that there is no separate server process
163
to install, setup, configure, initialize, manage, and troubleshoot.
164
This is one reason why SQLite is a "zero-configuration" database
165
engine. Programs that use SQLite require no administrative support
166
for setting up the database engine before they are run. Any program
167
that is able to access the disk is able to use an SQLite database.
169
On the other hand, a database engine that uses a server can provide
170
better protection from bugs in the client application - stray pointers
171
in a client cannot corrupt memory on the server. And because a server
172
is a single persistent process, it is able control database access with
173
more precision, allowing for finer grain locking and better concurrency.
175
Most SQL database engines are client/server based. Of those that are
176
serverless, SQLite is the only one that this author knows of that
177
allows multiple applications to access the same database at the same time.
179
<a name="onefile"></a>
180
<p><b>Single Database File</b></p>
182
An SQLite database is a single ordinary disk file that can be located
183
anywhere in the directory hierarchy. If SQLite can read
184
the disk file then it can read anything in the database. If the disk
185
file and its directory are writable, then SQLite can change anything
186
in the database. Database files can easily be copied onto a USB
187
memory stick or emailed for sharing.
189
Other SQL database engines tend to store data as a large collection of
190
files. Often these files are in a standard location that only the
191
database engine itself can access. This makes the data more secure,
192
but also makes it harder to access. Some SQL database engines provide
193
the option of writing directly to disk and bypassing the filesystem
194
all together. This provides added performance, but at the cost of
195
considerable setup and maintenance complexity.
197
<a name="onefile"></a>
198
<p><b>Stable Cross-Platform Database File</b></p>
200
The SQLite file format is cross-platform. A database file written
201
on one machine can be copied to and used on a different machine with
202
a different architecture. Big-endian or little-endian, 32-bit or
203
64-bit does not matter. All machines use the same file format.
204
Furthermore, the developers have pledged to keep the file format
205
stable and backwards compatible, so newer versions of SQLite can
206
read and write older database files.
208
Most other SQL database engines require you to dump and restore
209
the database when moving from one platform to another and often
210
when upgrading to a newer version of the software.
213
<p><b>Compact</b></p>
215
When optimized for size, the whole SQLite library with everything enabled
216
is [footprint | less than 350KiB in size]
217
(as measured on an ix86 using the "size"
218
utility from the GNU compiler suite.) Unneeded features can be disabled
219
at compile-time to further reduce the size of the library to under
222
Most other SQL database engines are much larger than this. IBM boasts
223
that its recently released CloudScape database engine is "only" a 2MiB
224
jar file - an order of magnitude larger than SQLite even after it is
226
Firebird boasts that its client-side library is only 350KiB. That's
227
as big as SQLite and does not even contain the database engine.
228
The Berkeley DB library from Oracle is 450KiB and it omits SQL
229
support, providing the programmer with only simple key/value pairs.
231
<a name="typing"></a>
232
<p><b>Manifest typing</b></p>
234
Most SQL database engines use static typing. A datatype is associated
235
with each column in a table and only values of that particular datatype
236
are allowed to be stored in that column. SQLite relaxes this restriction
237
by using manifest typing.
238
In manifest typing, the datatype is a property of the value itself, not
239
of the column in which the value is stored.
240
SQLite thus allows the user to store
241
any value of any datatype into any column regardless of the declared type
242
of that column. (There are some exceptions to this rule: An INTEGER
243
PRIMARY KEY column may only store integers. And SQLite attempts to coerce
244
values into the declared datatype of the column when it can.)
246
As far as we can tell, the SQL language specification allows the use
247
of manifest typing. Nevertheless, most other SQL database engines are
248
statically typed and so some people
249
feel that the use of manifest typing is a bug in SQLite. But the authors
250
of SQLite feel very strongly that this is a feature. The use of manifest
251
typing in SQLite is a deliberate design decision which has proven in practice
252
to make SQLite more reliable and easier to use, especially when used in
253
combination with dynamically typed programming languages such as Tcl and
257
<p><b>Variable-length records</b></p>
259
Most other SQL database engines allocated a fixed amount of disk space
260
for each row in most tables. They play special tricks for handling
261
BLOBs and CLOBs which can be of wildly varying length. But for most
262
tables, if you declare a column to be a VARCHAR(100) then the database
264
100 bytes of disk space regardless of how much information you actually
265
store in that column.
267
SQLite, in contrast, use only the amount of disk space actually
268
needed to store the information in a row. If you store a single
269
character in a VARCHAR(100) column, then only a single byte of disk
270
space is consumed. (Actually two bytes - there is some overhead at
271
the beginning of each column to record its datatype and length.)
273
The use of variable-length records by SQLite has a number of advantages.
274
It results in smaller database files, obviously. It also makes the
275
database run faster, since there is less information to move to and from
276
disk. And, the use of variable-length records makes it possible for
277
SQLite to employ manifest typing instead of static typing.
279
<a name="readable"></a>
280
<p><b>Readable source code</b></p>
282
The source code to SQLite is designed to be readable and accessible to
283
the average programmer. All procedures and data structures and many
284
automatic variables are carefully commented with useful information about
285
what they do. Boilerplate commenting is omitted.
288
<p><b>SQL statements compile into virtual machine code</b></p>
290
Every SQL database engine compiles each SQL statement into some kind of
291
internal data structure which is then used to carry out the work of the
292
statement. But in most SQL engines that internal data structure is a
293
complex web of interlinked structures and objects. In SQLite, the compiled
294
form of statements is a short program in a machine-language like
295
representation. Users of the database can view this
296
<a href="opcode.html">virtual machine language</a>
297
by prepending the <a href="lang_explain.html">EXPLAIN</a> keyword
300
The use of a virtual machine in SQLite has been a great benefit to the
301
library's development. The virtual machine provides a crisp, well-defined
302
junction between the front-end of SQLite (the part that parses SQL
303
statements and generates virtual machine code) and the back-end (the
304
part that executes the virtual machine code and computes a result.)
305
The virtual machine allows the developers to see clearly and in an
306
easily readable form what SQLite is trying to do with each statement
307
it compiles, which is a tremendous help in debugging.
308
Depending on how it is compiled, SQLite also has the capability of
309
tracing the execution of the virtual machine - printing each
310
virtual machine instruction and its result as it executes.
312
<a name="license"></a>
313
<p><b>Public domain</b></p>
315
The source code for SQLite is in the public domain. No claim of copyright
316
is made on any part of the core source code. (The documentation and test
317
code is a different matter - some sections of documentation and test logic
318
are governed by open-source licenses.) All contributors to the
319
SQLite core software have signed affidavits specifically disavowing any
320
copyright interest in the code. This means that anybody is able to legally
321
do anything they want with the SQLite source code.
323
There are other SQL database engines with liberal licenses that allow
324
the code to be broadly and freely used. But those other engines are
325
still governed by copyright law. SQLite is different in that copyright
326
law simply does not apply.
328
The source code files for other SQL database engines typically begin
329
with a comment describing your license rights to view and copy that file.
330
The SQLite source code contains no license since it is not governed by
331
copyright. Instead of a license, the SQLite source code offers a blessing:
333
<i>May you do good and not evil<br>
334
May you find forgiveness for yourself and forgive others<br>
335
May you share freely, never taking more than you give.</i>
338
<a name="extensions"></a>
339
<p><b>SQL language extensions</b></p>
341
SQLite provides a number of enhancements to the SQL language
342
not normally found in other database engines.
343
The EXPLAIN keyword and manifest typing have already been mentioned
344
above. SQLite also provides statements such as
345
<a href="lang_replace.html">REPLACE</a> and the
346
<a href="lang_conflict.html">ON CONFLICT</a> clause that allow for
347
added control over the resolution of constraint conflicts.
348
SQLite supports <a href="lang_attach.html">ATTACH</a> and
349
<a href="lang_detach.html">DETACH</a> commands that allow multiple
350
independent databases to be used together in the same query.
351
And SQLite defines APIs that allows the user to add new
352
<a href="c3ref/create_function.html">SQL functions</a>
353
and <a href="c3ref/create_collation.html">collating sequences</a>.
added
removed
different.html
