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>SQLite Version 3 Overview</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>
122
<h2>SQLite Version 3 Overview</h2>
125
SQLite version 3.0 introduces important changes to the library, including:
129
<li>A more compact format for database files.</li>
130
<li>Manifest typing and BLOB support.</li>
131
<li>Support for both UTF-8 and UTF-16 text.</li>
132
<li>User-defined text collating sequences.</li>
133
<li>64-bit ROWIDs.</li>
134
<li>Improved Concurrency.</li>
138
This document is a quick introduction to the changes for SQLite 3.0
139
for users who are already familiar with SQLite version 2.8.
142
<h3>Naming Changes</h3>
145
SQLite version 2.8 will continue to be supported with bug fixes
146
for the foreseeable future. In order to allow SQLite version 2.8
147
and SQLite version 3.0 to peacefully coexist, the names of key files
148
and APIs in SQLite version 3.0 have been changed to include the
149
character "3". For example, the include file used by C programs
150
has been changed from "sqlite.h" to "sqlite3.h". And the name of
151
the shell program used to interact with databases has been changed
152
from "sqlite.exe" to "sqlite3.exe". With these changes, it is possible
153
to have both SQLite 2.8 and SQLite 3.0 installed on the same system at
154
the same time. And it is possible for the same C program to link
155
against both SQLite 2.8 and SQLite 3.0 at the same time and to use
156
both libraries at the same time.
159
<h3>New File Format</h3>
162
The format used by SQLite database files has been completely revised.
163
The old version 2.1 format and the new 3.0 format are incompatible with
164
one another. Version 2.8 of SQLite will not read a version 3.0 database
165
files and version 3.0 of SQLite will not read a version 2.8 database file.
169
To convert an SQLite 2.8 database into an SQLite 3.0 database, have
170
ready the command-line shells for both version 2.8 and 3.0. Then
171
enter a command like the following:
175
sqlite OLD.DB .dump | sqlite3 NEW.DB
179
The new database file format uses B+trees for tables. In a B+tree, all
180
data is stored in the leaves of the tree instead of in both the leaves and
181
the intermediate branch nodes. The use of B+trees for tables allows for
182
better scalability and the storage of larger data fields without the use of
183
overflow pages. Traditional B-trees are still used for indices.</p>
186
The new file format also supports variable pages sizes between 512 and
187
65536 bytes. The size of a page is stored in the file header so the
188
same library can read databases with different pages sizes, in theory,
189
though this feature has not yet been implemented in practice.
193
The new file format omits unused fields from its disk images. For example,
194
indices use only the key part of a B-tree record and not the data. So
195
for indices, the field that records the length of the data is omitted.
196
Integer values such as the length of key and data are stored using
197
a variable-length encoding so that only one or two bytes are required to
198
store the most common cases but up to 64-bits of information can be encoded
200
Integer and floating point data is stored on the disk in binary rather
201
than being converted into ASCII as in SQLite version 2.8.
202
These changes taken together result in database files that are typically
203
25% to 35% smaller than the equivalent files in SQLite version 2.8.
207
Details of the low-level B-tree format used in SQLite version 3.0 can
208
be found in header comments to the
209
<a href="http://www.sqlite.org/src/finfo?name=src/btreeInt.h">btreeInt.h</a>
210
source file and in the <a href="fileformat2.html">file format</a> documentation.
213
<h3>Manifest Typing and BLOB Support</h3>
216
SQLite version 2.8 will deal with data in various formats internally,
217
but when writing to the disk or interacting through its API, SQLite 2.8
218
always converts data into ASCII text. SQLite 3.0, in contrast, exposes
219
its internal data representations to the user and stores binary representations
220
to disk when appropriate. The exposing of non-ASCII representations was
221
added in order to support BLOBs.
225
SQLite version 2.8 had the feature that any type of data could be stored
226
in any table column regardless of the declared type of that column. This
227
feature is retained in version 3.0, though in a slightly modified form.
228
Each table column will store any type of data, though columns have an
229
affinity for the format of data defined by their declared datatype.
230
When data is inserted into a column, that column will make an attempt
231
to convert the data format into the column's declared type. All SQL
232
database engines do this. The difference is that SQLite 3.0 will
233
still store the data even if a format conversion is not possible.
237
For example, if you have a table column declared to be of type "INTEGER"
238
and you try to insert a string, the column will look at the text string
239
and see if it looks like a number. If the string does look like a number
240
it is converted into a number and into an integer if the number does not
241
have a fractional part, and stored that way. But if the string is not
242
a well-formed number it is still stored as a string. A column with a
243
type of "TEXT" tries to convert numbers into an ASCII-Text representation
244
before storing them. But BLOBs are stored in TEXT columns as BLOBs because
245
you cannot in general convert a BLOB into text.
249
In most other SQL database engines the datatype is associated with
250
the table column that holds the data - with the data container.
251
In SQLite 3.0, the datatype is associated with the data itself, not
253
<a href="http://www.paulgraham.com/">Paul Graham</a> in his book
254
<i><a href="http://www.paulgraham.com/acl.html">ANSI Common Lisp</a></i></a>
255
calls this property "Manifest Typing".
256
Other writers have other definitions for the term "manifest typing",
257
so beware of confusion. But by whatever name, that is the datatype
258
model supported by SQLite 3.0.
262
Additional information about datatypes in SQLite version 3.0 is
264
<a href="datatype3.html">separately</a>.
267
<h3>Support for UTF-8 and UTF-16</h3>
270
The new API for SQLite 3.0 contains routines that accept text as
271
both UTF-8 and UTF-16 in the native byte order of the host machine.
272
Each database file manages text as either UTF-8, UTF-16BE (big-endian),
273
or UTF-16LE (little-endian). Internally and in the disk file, the
274
same text representation is used everywhere. If the text representation
275
specified by the database file (in the file header) does not match
276
the text representation required by the interface routines, then text
277
is converted on-the-fly.
278
Constantly converting text from one representation to another can be
279
computationally expensive, so it is suggested that programmers choose a
280
single representation and stick with it throughout their application.
284
In the current implementation of SQLite, the SQL parser only works
285
with UTF-8 text. So if you supply UTF-16 text it will be converted.
286
This is just an implementation issue and there is nothing to prevent
287
future versions of SQLite from parsing UTF-16 encoded SQL natively.
291
When creating new user-defined SQL functions and collating sequences,
292
each function or collating sequence can specify if it works with
293
UTF-8, UTF-16be, or UTF-16le. Separate implementations can be registered
294
for each encoding. If an SQL function or collating sequence is required
295
but a version for the current text encoding is not available, then
296
the text is automatically converted. As before, this conversion takes
297
computation time, so programmers are advised to pick a single
298
encoding and stick with it in order to minimize the amount of unnecessary
303
SQLite is not particular about the text it receives and is more than
304
happy to process text strings that are not normalized or even
305
well-formed UTF-8 or UTF-16. Thus, programmers who want to store
306
IS08859 data can do so using the UTF-8 interfaces. As long as no
307
attempts are made to use a UTF-16 collating sequence or SQL function,
308
the byte sequence of the text will not be modified in any way.
311
<h3>User-defined Collating Sequences</h3>
314
A collating sequence is just a defined order for text. When SQLite 3.0
315
sorts (or uses a comparison operator like "<" or ">=") the sort
316
order is first determined by the data type.
320
<li>NULLs sort first</li>
321
<li>Numeric values sort next in numerical order</li>
322
<li>Text values come after numerics</li>
323
<li>BLOBs sort last</li>
327
Collating sequences are used for comparing two text strings.
328
The collating sequence does not change the ordering of NULLs, numbers,
333
A collating sequence is implemented as a function that takes the
334
two strings being compared as inputs and returns negative, zero, or
335
positive if the first string is less than, equal to, or greater than
337
SQLite 3.0 comes with a single built-in collating sequence named "BINARY"
338
which is implemented using the memcmp() routine from the standard C library.
339
The BINARY collating sequence works well for English text. For other
340
languages or locales, alternative collating sequences may be preferred.
344
The decision of which collating sequence to use is controlled by the
345
COLLATE clause in SQL. A COLLATE clause can occur on a table definition,
346
to define a default collating sequence to a table column, or on field
347
of an index, or in the ORDER BY clause of a SELECT statement.
348
Planned enhancements to SQLite are to include standard CAST() syntax
349
to allow the collating sequence of an expression to be defined.
352
<h3>64-bit ROWIDs</h3>
355
Every row of a table has a unique rowid.
356
If the table defines a column with the type "INTEGER PRIMARY KEY" then that
357
column becomes an alias for the rowid. But with or without an INTEGER PRIMARY
358
KEY column, every row still has a rowid.
362
In SQLite version 3.0, the rowid is a 64-bit signed integer.
363
This is an expansion of SQLite version 2.8 which only permitted
368
To minimize storage space, the 64-bit rowid is stored as a variable length
369
integer. Rowids between 0 and 127 use only a single byte.
370
Rowids between 0 and 16383 use just 2 bytes. Up to 2097152 uses three
371
bytes. And so forth. Negative rowids are allowed but they always use
372
nine bytes of storage and so their use is discouraged. When rowids
373
are generated automatically by SQLite, they will always be non-negative.
376
<h3>Improved Concurrency</h3>
379
SQLite version 2.8 allowed multiple simultaneous readers or a single
380
writer but not both. SQLite version 3.0 allows one process to begin
381
writing the database while other processes continue to read. The
382
writer must still obtain an exclusive lock on the database for a brief
383
interval in order to commit its changes, but the exclusive lock is no
384
longer required for the entire write operation.
385
A <a href="lockingv3.html">more detailed report</a> on the locking
386
behavior of SQLite version 3.0 is available separately.
390
A limited form of table-level locking is now also available in SQLite.
391
If each table is stored in a separate database file, those separate
392
files can be attached to the main database (using the ATTACH command)
393
and the combined databases will function as one. But locks will only
394
be acquired on individual files as needed. So if you redefine "database"
395
to mean two or more database files, then it is entirely possible for
396
two processes to be writing to the same database at the same time.
397
To further support this capability, commits of transactions involving
398
two or more ATTACHed database are now atomic.
404
SQLite version 3.0 is made possible in part by AOL developers
405
supporting and embracing great Open-Source Software.
added
removed
version3.html
