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
216
217
218
219
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
256
257
258
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
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
|
<!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>SQLite Version 3 Overview</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>
<p>
<center><table border="1" cellpadding="10" width="75%">
<tr><td bgcolor="#ffffbb">
<b>Editorial Note:</b>
This document was written in 2004 as a guide to programmers who were
transitioning from SQLite2 to SQLite3.
It is retained as part of the historical record of SQLite.
Modern programmers should refer to
more up-to-date documentation on SQLite available elsewhere
on this website.
</table></center>
<h2>SQLite Version 3 Overview</h2>
<p>
SQLite version 3.0 introduces important changes to the library, including:
</p>
<ul>
<li>A more compact format for database files.</li>
<li>Manifest typing and BLOB support.</li>
<li>Support for both UTF-8 and UTF-16 text.</li>
<li>User-defined text collating sequences.</li>
<li>64-bit ROWIDs.</li>
<li>Improved Concurrency.</li>
</ul>
<p>
This document is a quick introduction to the changes for SQLite 3.0
for users who are already familiar with SQLite version 2.8.
</p>
<h3>Naming Changes</h3>
<p>
SQLite version 2.8 will continue to be supported with bug fixes
for the foreseeable future. In order to allow SQLite version 2.8
and SQLite version 3.0 to peacefully coexist, the names of key files
and APIs in SQLite version 3.0 have been changed to include the
character "3". For example, the include file used by C programs
has been changed from "sqlite.h" to "sqlite3.h". And the name of
the shell program used to interact with databases has been changed
from "sqlite.exe" to "sqlite3.exe". With these changes, it is possible
to have both SQLite 2.8 and SQLite 3.0 installed on the same system at
the same time. And it is possible for the same C program to link
against both SQLite 2.8 and SQLite 3.0 at the same time and to use
both libraries at the same time.
</p>
<h3>New File Format</h3>
<p>
The format used by SQLite database files has been completely revised.
The old version 2.1 format and the new 3.0 format are incompatible with
one another. Version 2.8 of SQLite will not read a version 3.0 database
files and version 3.0 of SQLite will not read a version 2.8 database file.
</p>
<p>
To convert an SQLite 2.8 database into an SQLite 3.0 database, have
ready the command-line shells for both version 2.8 and 3.0. Then
enter a command like the following:
</p>
<blockquote><pre>
sqlite OLD.DB .dump | sqlite3 NEW.DB
</pre></blockquote>
<p>
The new database file format uses B+trees for tables. In a B+tree, all
data is stored in the leaves of the tree instead of in both the leaves and
the intermediate branch nodes. The use of B+trees for tables allows for
better scalability and the storage of larger data fields without the use of
overflow pages. Traditional B-trees are still used for indices.</p>
<p>
The new file format also supports variable pages sizes between 512 and
65536 bytes. The size of a page is stored in the file header so the
same library can read databases with different pages sizes, in theory,
though this feature has not yet been implemented in practice.
</p>
<p>
The new file format omits unused fields from its disk images. For example,
indices use only the key part of a B-tree record and not the data. So
for indices, the field that records the length of the data is omitted.
Integer values such as the length of key and data are stored using
a variable-length encoding so that only one or two bytes are required to
store the most common cases but up to 64-bits of information can be encoded
if needed.
Integer and floating point data is stored on the disk in binary rather
than being converted into ASCII as in SQLite version 2.8.
These changes taken together result in database files that are typically
25% to 35% smaller than the equivalent files in SQLite version 2.8.
</p>
<p>
Details of the low-level B-tree format used in SQLite version 3.0 can
be found in header comments to the
<a href="https://www.sqlite.org/src/finfo?name=src/btreeInt.h">btreeInt.h</a>
source file and in the <a href="fileformat2.html">file format</a> documentation.
</p>
<h3>Manifest Typing and BLOB Support</h3>
<p>
SQLite version 2.8 will deal with data in various formats internally,
but when writing to the disk or interacting through its API, SQLite 2.8
always converts data into ASCII text. SQLite 3.0, in contrast, exposes
its internal data representations to the user and stores binary representations
to disk when appropriate. The exposing of non-ASCII representations was
added in order to support BLOBs.
</p>
<p>
SQLite version 2.8 had the feature that any type of data could be stored
in any table column regardless of the declared type of that column. This
feature is retained in version 3.0, though in a slightly modified form.
Each table column will store any type of data, though columns have an
affinity for the format of data defined by their declared datatype.
When data is inserted into a column, that column will make an attempt
to convert the data format into the column's declared type. All SQL
database engines do this. The difference is that SQLite 3.0 will
still store the data even if a format conversion is not possible.
</p>
<p>
For example, if you have a table column declared to be of type "INTEGER"
and you try to insert a string, the column will look at the text string
and see if it looks like a number. If the string does look like a number
it is converted into a number and into an integer if the number does not
have a fractional part, and stored that way. But if the string is not
a well-formed number it is still stored as a string. A column with a
type of "TEXT" tries to convert numbers into an ASCII-Text representation
before storing them. But BLOBs are stored in TEXT columns as BLOBs because
you cannot in general convert a BLOB into text.
</p>
<p>
In most other SQL database engines the datatype is associated with
the table column that holds the data - with the data container.
In SQLite 3.0, the datatype is associated with the data itself, not
with its container.
<a href="http://www.paulgraham.com/">Paul Graham</a> in his book
<i><a href="http://www.paulgraham.com/acl.html">ANSI Common Lisp</a></i></a>
calls this property "Manifest Typing".
Other writers have other definitions for the term "manifest typing",
so beware of confusion. But by whatever name, that is the datatype
model supported by SQLite 3.0.
</p>
<p>
Additional information about datatypes in SQLite version 3.0 is
available
<a href="datatype3.html">separately</a>.
</p>
<h3>Support for UTF-8 and UTF-16</h3>
<p>
The new API for SQLite 3.0 contains routines that accept text as
both UTF-8 and UTF-16 in the native byte order of the host machine.
Each database file manages text as either UTF-8, UTF-16BE (big-endian),
or UTF-16LE (little-endian). Internally and in the disk file, the
same text representation is used everywhere. If the text representation
specified by the database file (in the file header) does not match
the text representation required by the interface routines, then text
is converted on-the-fly.
Constantly converting text from one representation to another can be
computationally expensive, so it is suggested that programmers choose a
single representation and stick with it throughout their application.
</p>
<p>
In the current implementation of SQLite, the SQL parser only works
with UTF-8 text. So if you supply UTF-16 text it will be converted.
This is just an implementation issue and there is nothing to prevent
future versions of SQLite from parsing UTF-16 encoded SQL natively.
</p>
<p>
When creating new user-defined SQL functions and collating sequences,
each function or collating sequence can specify if it works with
UTF-8, UTF-16be, or UTF-16le. Separate implementations can be registered
for each encoding. If an SQL function or collating sequence is required
but a version for the current text encoding is not available, then
the text is automatically converted. As before, this conversion takes
computation time, so programmers are advised to pick a single
encoding and stick with it in order to minimize the amount of unnecessary
format juggling.
</p>
<p>
SQLite is not particular about the text it receives and is more than
happy to process text strings that are not normalized or even
well-formed UTF-8 or UTF-16. Thus, programmers who want to store
ISO8859 data can do so using the UTF-8 interfaces. As long as no
attempts are made to use a UTF-16 collating sequence or SQL function,
the byte sequence of the text will not be modified in any way.
</p>
<h3>User-defined Collating Sequences</h3>
<p>
A collating sequence is just a defined order for text. When SQLite 3.0
sorts (or uses a comparison operator like "<" or ">=") the sort
order is first determined by the data type.
</p>
<ul>
<li>NULLs sort first</li>
<li>Numeric values sort next in numerical order</li>
<li>Text values come after numerics</li>
<li>BLOBs sort last</li>
</ul>
<p>
Collating sequences are used for comparing two text strings.
The collating sequence does not change the ordering of NULLs, numbers,
or BLOBs, only text.
</p>
<p>
A collating sequence is implemented as a function that takes the
two strings being compared as inputs and returns negative, zero, or
positive if the first string is less than, equal to, or greater than
the second.
SQLite 3.0 comes with a single built-in collating sequence named "BINARY"
which is implemented using the memcmp() routine from the standard C library.
The BINARY collating sequence works well for English text. For other
languages or locales, alternative collating sequences may be preferred.
</p>
<p>
The decision of which collating sequence to use is controlled by the
COLLATE clause in SQL. A COLLATE clause can occur on a table definition,
to define a default collating sequence to a table column, or on field
of an index, or in the ORDER BY clause of a SELECT statement.
Planned enhancements to SQLite are to include standard CAST() syntax
to allow the collating sequence of an expression to be defined.
</p>
<h3>64-bit ROWIDs</h3>
<p>
Every row of a table has a unique rowid.
If the table defines a column with the type "INTEGER PRIMARY KEY" then that
column becomes an alias for the rowid. But with or without an INTEGER PRIMARY
KEY column, every row still has a rowid.
</p>
<p>
In SQLite version 3.0, the rowid is a 64-bit signed integer.
This is an expansion of SQLite version 2.8 which only permitted
rowids of 32-bits.
</p>
<p>
To minimize storage space, the 64-bit rowid is stored as a variable length
integer. Rowids between 0 and 127 use only a single byte.
Rowids between 0 and 16383 use just 2 bytes. Up to 2097152 uses three
bytes. And so forth. Negative rowids are allowed but they always use
nine bytes of storage and so their use is discouraged. When rowids
are generated automatically by SQLite, they will always be non-negative.
</p>
<h3>Improved Concurrency</h3>
<p>
SQLite version 2.8 allowed multiple simultaneous readers or a single
writer but not both. SQLite version 3.0 allows one process to begin
writing the database while other processes continue to read. The
writer must still obtain an exclusive lock on the database for a brief
interval in order to commit its changes, but the exclusive lock is no
longer required for the entire write operation.
A <a href="lockingv3.html">more detailed report</a> on the locking
behavior of SQLite version 3.0 is available separately.
</p>
<p>
A limited form of table-level locking is now also available in SQLite.
If each table is stored in a separate database file, those separate
files can be attached to the main database (using the ATTACH command)
and the combined databases will function as one. But locks will only
be acquired on individual files as needed. So if you redefine "database"
to mean two or more database files, then it is entirely possible for
two processes to be writing to the same database at the same time.
To further support this capability, commits of transactions involving
two or more ATTACHed database are now atomic.
</p>
<h3>Credits</h3>
<p>
SQLite version 3.0 is made possible in part by AOL developers
supporting and embracing great Open-Source Software.
</p>
|
