Debian Codegraph @

www/version3.html

raw
1 <!DOCTYPE html>
2 <html><head>
3 <meta name="viewport" content="width=device-width, initial-scale=1.0">
4 <meta http-equiv="content-type" content="text/html; charset=UTF-8">
5 <link href="sqlite.css" rel="stylesheet">
6 <title>SQLite Version 3 Overview</title>
7 <!-- path= -->
8 </head>
9 <body>
10 <div class=nosearch>
11 <a href="index.html">
12 <img class="logo" src="images/sqlite370_banner.svg" alt="SQLite" border="0">
13 </a>
14 <div><!-- IE hack to prevent disappearing logo --></div>
15 <div class="tagline desktoponly">
16 Small. Fast. Reliable.<br>Choose any three.
17 </div>
18 <div class="menu mainmenu">
19 <ul>
20 <li><a href="index.html">Home</a>
21 <li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
22 <li class='wideonly'><a href='about.html'>About</a>
23 <li class='desktoponly'><a href="docs.html">Documentation</a>
24 <li class='desktoponly'><a href="download.html">Download</a>
25 <li class='wideonly'><a href='copyright.html'>License</a>
26 <li class='desktoponly'><a href="support.html">Support</a>
27 <li class='desktoponly'><a href="prosupport.html">Purchase</a>
28 <li class='search' id='search_menubutton'>
29 <a href="javascript:void(0)" onclick='toggle_search()'>Search</a>
30 </ul>
31 </div>
32 <div class="menu submenu" id="submenu">
33 <ul>
34 <li><a href='about.html'>About</a>
35 <li><a href='docs.html'>Documentation</a>
36 <li><a href='download.html'>Download</a>
37 <li><a href='support.html'>Support</a>
38 <li><a href='prosupport.html'>Purchase</a>
39 </ul>
40 </div>
41 <div class="searchmenu" id="searchmenu">
42 <form method="GET" action="search">
43 <select name="s" id="searchtype">
44 <option value="d">Search Documentation</option>
45 <option value="c">Search Changelog</option>
46 </select>
47 <input type="text" name="q" id="searchbox" value="">
48 <input type="submit" value="Go">
49 </form>
50 </div>
51 </div>
52 <script>
53 function toggle_div(nm) {
54 var w = document.getElementById(nm);
55 if( w.style.display=="block" ){
56 w.style.display = "none";
57 }else{
58 w.style.display = "block";
59 }
60 }
61 function toggle_search() {
62 var w = document.getElementById("searchmenu");
63 if( w.style.display=="block" ){
64 w.style.display = "none";
65 } else {
66 w.style.display = "block";
67 setTimeout(function(){
68 document.getElementById("searchbox").focus()
69 }, 30);
70 }
71 }
72 function div_off(nm){document.getElementById(nm).style.display="none";}
73 window.onbeforeunload = function(e){div_off("submenu");}
74 /* Disable the Search feature if we are not operating from CGI, since */
75 /* Search is accomplished using CGI and will not work without it. */
76 if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){
77 document.getElementById("search_menubutton").style.display = "none";
78 }
79 /* Used by the Hide/Show button beside syntax diagrams, to toggle the */
80 function hideorshow(btn,obj){
81 var x = document.getElementById(obj);
82 var b = document.getElementById(btn);
83 if( x.style.display!='none' ){
84 x.style.display = 'none';
85 b.innerHTML='show';
86 }else{
87 x.style.display = '';
88 b.innerHTML='hide';
89 }
90 return false;
91 }
92 var antiRobot = 0;
93 function antiRobotGo(){
94 if( antiRobot!=3 ) return;
95 antiRobot = 7;
96 var j = document.getElementById("mtimelink");
97 if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href");
98 }
99 function antiRobotDefense(){
100 document.body.onmousedown=function(){
101 antiRobot |= 2;
102 antiRobotGo();
103 document.body.onmousedown=null;
104 }
105 document.body.onmousemove=function(){
106 antiRobot |= 2;
107 antiRobotGo();
108 document.body.onmousemove=null;
109 }
110 setTimeout(function(){
111 antiRobot |= 1;
112 antiRobotGo();
113 }, 100)
114 antiRobotGo();
115 }
116 antiRobotDefense();
117 </script>
118
119
120 <p>
121 <center><table border="1" cellpadding="10" width="75%">
122 <tr><td bgcolor="#ffffbb">
123 <b>Editorial Note:</b>
124 This document was written in 2004 as a guide to programmers who were
125 transitioning from SQLite2 to SQLite3.
126 It is retained as part of the historical record of SQLite.
127 Modern programmers should refer to
128 more up-to-date documentation on SQLite available elsewhere
129 on this website.
130 </table></center>
131
132 <h2>SQLite Version 3 Overview</h2>
133
134 <p>
135 SQLite version 3.0 introduces important changes to the library, including:
136 </p>
137
138 <ul>
139 <li>A more compact format for database files.</li>
140 <li>Manifest typing and BLOB support.</li>
141 <li>Support for both UTF-8 and UTF-16 text.</li>
142 <li>User-defined text collating sequences.</li>
143 <li>64-bit ROWIDs.</li>
144 <li>Improved Concurrency.</li>
145 </ul>
146
147 <p>
148 This document is a quick introduction to the changes for SQLite 3.0
149 for users who are already familiar with SQLite version 2.8.
150 </p>
151
152 <h3>Naming Changes</h3>
153
154 <p>
155 SQLite version 2.8 will continue to be supported with bug fixes
156 for the foreseeable future. In order to allow SQLite version 2.8
157 and SQLite version 3.0 to peacefully coexist, the names of key files
158 and APIs in SQLite version 3.0 have been changed to include the
159 character "3". For example, the include file used by C programs
160 has been changed from "sqlite.h" to "sqlite3.h". And the name of
161 the shell program used to interact with databases has been changed
162 from "sqlite.exe" to "sqlite3.exe". With these changes, it is possible
163 to have both SQLite 2.8 and SQLite 3.0 installed on the same system at
164 the same time. And it is possible for the same C program to link
165 against both SQLite 2.8 and SQLite 3.0 at the same time and to use
166 both libraries at the same time.
167 </p>
168
169 <h3>New File Format</h3>
170
171 <p>
172 The format used by SQLite database files has been completely revised.
173 The old version 2.1 format and the new 3.0 format are incompatible with
174 one another. Version 2.8 of SQLite will not read a version 3.0 database
175 files and version 3.0 of SQLite will not read a version 2.8 database file.
176 </p>
177
178 <p>
179 To convert an SQLite 2.8 database into an SQLite 3.0 database, have
180 ready the command-line shells for both version 2.8 and 3.0. Then
181 enter a command like the following:
182 </p>
183
184 <blockquote><pre>
185 sqlite OLD.DB .dump | sqlite3 NEW.DB
186 </pre></blockquote>
187
188 <p>
189 The new database file format uses B+trees for tables. In a B+tree, all
190 data is stored in the leaves of the tree instead of in both the leaves and
191 the intermediate branch nodes. The use of B+trees for tables allows for
192 better scalability and the storage of larger data fields without the use of
193 overflow pages. Traditional B-trees are still used for indices.</p>
194
195 <p>
196 The new file format also supports variable pages sizes between 512 and
197 65536 bytes. The size of a page is stored in the file header so the
198 same library can read databases with different pages sizes, in theory,
199 though this feature has not yet been implemented in practice.
200 </p>
201
202 <p>
203 The new file format omits unused fields from its disk images. For example,
204 indices use only the key part of a B-tree record and not the data. So
205 for indices, the field that records the length of the data is omitted.
206 Integer values such as the length of key and data are stored using
207 a variable-length encoding so that only one or two bytes are required to
208 store the most common cases but up to 64-bits of information can be encoded
209 if needed.
210 Integer and floating point data is stored on the disk in binary rather
211 than being converted into ASCII as in SQLite version 2.8.
212 These changes taken together result in database files that are typically
213 25% to 35% smaller than the equivalent files in SQLite version 2.8.
214 </p>
215
216 <p>
217 Details of the low-level B-tree format used in SQLite version 3.0 can
218 be found in header comments to the
219 <a href="https://sqlite.org/src/finfo?name=src/btreeInt.h">btreeInt.h</a>
220 source file and in the <a href="fileformat2.html">file format</a> documentation.
221 </p>
222
223 <h3>Manifest Typing and BLOB Support</h3>
224
225 <p>
226 SQLite version 2.8 will deal with data in various formats internally,
227 but when writing to the disk or interacting through its API, SQLite 2.8
228 always converts data into ASCII text. SQLite 3.0, in contrast, exposes
229 its internal data representations to the user and stores binary representations
230 to disk when appropriate. The exposing of non-ASCII representations was
231 added in order to support BLOBs.
232 </p>
233
234 <p>
235 SQLite version 2.8 had the feature that any type of data could be stored
236 in any table column regardless of the declared type of that column. This
237 feature is retained in version 3.0, though in a slightly modified form.
238 Each table column will store any type of data, though columns have an
239 affinity for the format of data defined by their declared datatype.
240 When data is inserted into a column, that column will make an attempt
241 to convert the data format into the column's declared type. All SQL
242 database engines do this. The difference is that SQLite 3.0 will
243 still store the data even if a format conversion is not possible.
244 </p>
245
246 <p>
247 For example, if you have a table column declared to be of type "INTEGER"
248 and you try to insert a string, the column will look at the text string
249 and see if it looks like a number. If the string does look like a number
250 it is converted into a number and into an integer if the number does not
251 have a fractional part, and stored that way. But if the string is not
252 a well-formed number it is still stored as a string. A column with a
253 type of "TEXT" tries to convert numbers into an ASCII-Text representation
254 before storing them. But BLOBs are stored in TEXT columns as BLOBs because
255 you cannot in general convert a BLOB into text.
256 </p>
257
258 <p>
259 In most other SQL database engines the datatype is associated with
260 the table column that holds the data - with the data container.
261 In SQLite 3.0, the datatype is associated with the data itself, not
262 with its container.
263 <a href="http://www.paulgraham.com/">Paul Graham</a> in his book
264 <i><a href="http://www.paulgraham.com/acl.html">ANSI Common Lisp</a></i></a>
265 calls this property "Manifest Typing".
266 Other writers have other definitions for the term "manifest typing",
267 so beware of confusion. But by whatever name, that is the datatype
268 model supported by SQLite 3.0.
269 </p>
270
271 <p>
272 Additional information about datatypes in SQLite version 3.0 is
273 available
274 <a href="datatype3.html">separately</a>.
275 </p>
276
277 <h3>Support for UTF-8 and UTF-16</h3>
278
279 <p>
280 The new API for SQLite 3.0 contains routines that accept text as
281 both UTF-8 and UTF-16 in the native byte order of the host machine.
282 Each database file manages text as either UTF-8, UTF-16BE (big-endian),
283 or UTF-16LE (little-endian). Internally and in the disk file, the
284 same text representation is used everywhere. If the text representation
285 specified by the database file (in the file header) does not match
286 the text representation required by the interface routines, then text
287 is converted on-the-fly.
288 Constantly converting text from one representation to another can be
289 computationally expensive, so it is suggested that programmers choose a
290 single representation and stick with it throughout their application.
291 </p>
292
293 <p>
294 In the current implementation of SQLite, the SQL parser only works
295 with UTF-8 text. So if you supply UTF-16 text it will be converted.
296 This is just an implementation issue and there is nothing to prevent
297 future versions of SQLite from parsing UTF-16 encoded SQL natively.
298 </p>
299
300 <p>
301 When creating new user-defined SQL functions and collating sequences,
302 each function or collating sequence can specify if it works with
303 UTF-8, UTF-16be, or UTF-16le. Separate implementations can be registered
304 for each encoding. If an SQL function or collating sequence is required
305 but a version for the current text encoding is not available, then
306 the text is automatically converted. As before, this conversion takes
307 computation time, so programmers are advised to pick a single
308 encoding and stick with it in order to minimize the amount of unnecessary
309 format juggling.
310 </p>
311
312 <p>
313 SQLite is not particular about the text it receives and is more than
314 happy to process text strings that are not normalized or even
315 well-formed UTF-8 or UTF-16. Thus, programmers who want to store
316 ISO8859 data can do so using the UTF-8 interfaces. As long as no
317 attempts are made to use a UTF-16 collating sequence or SQL function,
318 the byte sequence of the text will not be modified in any way.
319 </p>
320
321 <h3>User-defined Collating Sequences</h3>
322
323 <p>
324 A collating sequence is just a defined order for text. When SQLite 3.0
325 sorts (or uses a comparison operator like "&lt;" or "&gt;=") the sort
326 order is first determined by the data type.
327 </p>
328
329 <ul>
330 <li>NULLs sort first</li>
331 <li>Numeric values sort next in numerical order</li>
332 <li>Text values come after numerics</li>
333 <li>BLOBs sort last</li>
334 </ul>
335
336 <p>
337 Collating sequences are used for comparing two text strings.
338 The collating sequence does not change the ordering of NULLs, numbers,
339 or BLOBs, only text.
340 </p>
341
342 <p>
343 A collating sequence is implemented as a function that takes the
344 two strings being compared as inputs and returns negative, zero, or
345 positive if the first string is less than, equal to, or greater than
346 the second.
347 SQLite 3.0 comes with a single built-in collating sequence named "BINARY"
348 which is implemented using the memcmp() routine from the standard C library.
349 The BINARY collating sequence works well for English text. For other
350 languages or locales, alternative collating sequences may be preferred.
351 </p>
352
353 <p>
354 The decision of which collating sequence to use is controlled by the
355 COLLATE clause in SQL. A COLLATE clause can occur on a table definition,
356 to define a default collating sequence to a table column, or on a field
357 of an index, or in the ORDER BY clause of a SELECT statement.
358 Planned enhancements to SQLite are to include standard CAST() syntax
359 to allow the collating sequence of an expression to be defined.
360 </p>
361
362 <h3>64-bit ROWIDs</h3>
363
364 <p>
365 Every row of a table has a unique rowid.
366 If the table defines a column with the type "INTEGER PRIMARY KEY" then that
367 column becomes an alias for the rowid. But with or without an INTEGER PRIMARY
368 KEY column, every row still has a rowid.
369 </p>
370
371 <p>
372 In SQLite version 3.0, the rowid is a 64-bit signed integer.
373 This is an expansion of SQLite version 2.8 which only permitted
374 rowids of 32-bits.
375 </p>
376
377 <p>
378 To minimize storage space, the 64-bit rowid is stored as a variable length
379 integer. Rowids between 0 and 127 use only a single byte.
380 Rowids between 0 and 16383 use just 2 bytes. Up to 2097152 uses three
381 bytes. And so forth. Negative rowids are allowed but they always use
382 nine bytes of storage and so their use is discouraged. When rowids
383 are generated automatically by SQLite, they will always be non-negative.
384 </p>
385
386 <h3>Improved Concurrency</h3>
387
388 <p>
389 SQLite version 2.8 allowed multiple simultaneous readers or a single
390 writer but not both. SQLite version 3.0 allows one process to begin
391 writing the database while other processes continue to read. The
392 writer must still obtain an exclusive lock on the database for a brief
393 interval in order to commit its changes, but the exclusive lock is no
394 longer required for the entire write operation.
395 A <a href="lockingv3.html">more detailed report</a> on the locking
396 behavior of SQLite version 3.0 is available separately.
397 </p>
398
399 <p>
400 A limited form of table-level locking is now also available in SQLite.
401 If each table is stored in a separate database file, those separate
402 files can be attached to the main database (using the ATTACH command)
403 and the combined databases will function as one. But locks will only
404 be acquired on individual files as needed. So if you redefine "database"
405 to mean two or more database files, then it is entirely possible for
406 two processes to be writing to the same database at the same time.
407 To further support this capability, commits of transactions involving
408 two or more ATTACHed database are now atomic.
409 </p>
410
411 <h3>Credits</h3>
412
413 <p>
414 SQLite version 3.0 is made possible in part by AOL developers
415 supporting and embracing great Open-Source Software.
416 </p>
417 <p align="center"><small><i>This page was last updated on 2025-06-12 09:10:23Z </small></i></p>
418