| 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 "<" or ">=") 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 |
|