Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Incremental updates to fileformat.html. |
|---|---|
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
8b1e897acb696848f568469f647fc7f0 |
| User & Date: | dan 2009-04-07 15:34:24 |
Context
|
2009-04-07
| ||
| 18:27 | Further changes to fileformat.html. check-in: 6631cb911e user: dan tags: trunk | |
| 15:34 | Incremental updates to fileformat.html. check-in: 8b1e897acb user: dan tags: trunk | |
| 15:08 | Remove spurious "ah" from limits.html. cvstrac ticket #3787. check-in: d8a646e989 user: dan tags: trunk | |
Changes
Changes to pages/fileformat.in.
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 ... 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 ... 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 ... 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 ... 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 ... 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 ... 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 ... 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 ... 794 795 796 797 798 799 800 801 802 803 804 805 806 807 808 809 810 811 812 813 814 815 816 817 818 819 820 821 822 823 824 825 826 827 828 829 830 831 832 833 834 835 836 837 838 839 840 841 842 843 844 845 846 847 848 849 850 851 852 853 854 855 856 857 858 859 860 861 862 863 864 865 866 867 868 869 870 871 872 873 ... 987 988 989 990 991 992 993 994 995 996 997 998 999 1000 1001 .... 1003 1004 1005 1006 1007 1008 1009 1010 1011 1012 1013 1014 1015 1016 1017 1018 1019 1020 1021 1022 1023 1024 1025 1026 1027 1028 1029 1030 1031 1032 1033 1034 .... 1041 1042 1043 1044 1045 1046 1047 1048 1049 1050 1051 1052 1053 1054 1055 1056 1057 1058 1059 1060 1061 1062 1063 1064 1065 1066 1067 .... 1269 1270 1271 1272 1273 1274 1275 1276 1277 1278 1279 1280 1281 1282 1283 1284 1285 1286 1287 1288 1289 1290 1291 .... 1302 1303 1304 1305 1306 1307 1308 1309 1310 1311 1312 1313 1314 1315 1316 1317 .... 1336 1337 1338 1339 1340 1341 1342 1343 1344 1345 1346 1347 1348 1349 1350 1351 1352 1353 1354 1355 1356 1357 1358 .... 1481 1482 1483 1484 1485 1486 1487 1488 1489 1490 1491 1492 1493 1494 1495 .... 1603 1604 1605 1606 1607 1608 1609 1610 1611 1612 1613 1614 1615 1616 1617 1618 1619 1620 1621 .... 1622 1623 1624 1625 1626 1627 1628 1629 1630 1631 1632 1633 1634 1635 1636 1637 1638 1639 1640 1641 1642 1643 1644 1645 1646 1647 1648 1649 1650 1651 1652 1653 1654 1655 1656 1657 1658 1659 1660 1661 1662 1663 1664 1665 1666 1667 1668 1669 1670 .... 2027 2028 2029 2030 2031 2032 2033 2034 2035 2036 2037 2038 2039 2040 2041 .... 2045 2046 2047 2048 2049 2050 2051 2052 2053 2054 2055 2056 2057 2058 2059 .... 2099 2100 2101 2102 2103 2104 2105 2106 2107 2108 2109 2110 2111 2112 2113 .... 2138 2139 2140 2141 2142 2143 2144 2145 2146 2147 2148 2149 2150 2151 2152 .... 2199 2200 2201 2202 2203 2204 2205 2206 2207 2208 2209 2210 2211 2212 2213 2214 2215 2216 2217 2218 2219 2220 2221 2222 2223 2224 2225 2226 2227 2228 2229 2230 2231 2232 2233 2234 2235 2236 2237 2238 2239 2240 2241 2242 2243 2244 2245 2246 2247 2248 2249 2250 2251 2252 2253 2254 2255 2256 2257 2258 2259 2260 2261 2262 2263 2264 2265 2266 2267 2268 2269 2270 2271 2272 2273 2274 2275 2276 2277 2278 2279 2280 2281 2282 2283 2284 2285 2286 2287 2288 2289 2290 2291 2292 2293 2294 2295 2296 2297 2298 2299 2300 2301 2302 2303 2304 2305 2306 2307 2308 2309 2310 2311 2312 2313 2314 2315 2316 2317 2318 2319 2320 2321 2322 2323 2324 2325 2326 2327 2328 2329 2330 2331 2332 2333 2334 2335 2336 2337 2338 2339 2340 2341 2342 2343 2344 2345 2346 2347 2348 2349 2350 2351 2352 2353 2354 2355 2356 2357 2358 2359 2360 2361 2362 2363 2364 2365 2366 2367 2368 2369 2370 2371 2372 2373 2374 2375 2376 2377 2378 2379 2380 2381 2382 2383 2384 2385 2386 2387 2388 2389 2390 2391 2392 2393 2394 2395 2396 2397 2398 2399 2400 2401 2402 2403 2404 2405 2406 2407 2408 2409 2410 2411 2412 2413 2414 2415 2416 2417 2418 2419 2420 2421 2422 2423 2424 2425 2426 2427 2428 2429 2430 2431 2432 2433 2434 2435 2436 2437 2438 2439 2440 2441 2442 2443 2444 2445 2446 2447 2448 2449 2450 2451 2452 2453 2454 2455 2456 2457 2458 2459 2460 2461 2462 2463 2464 2465 2466 2467 2468 2469 2470 2471 2472 2473 2474 2475 2476 2477 2478 2479 2480 2481 2482 2483 2484 2485 2486 2487 2488 2489 2490 2491 2492 2493 2494 2495 2496 2497 2498 2499 2500 2501 2502 2503 2504 2505 2506 2507 2508 2509 2510 2511 2512 2513 2514 2515 2516 2517 2518 2519 2520 2521 2522 2523 2524 2525 2526 2527 2528 2529 2530 2531 2532 2533 2534 2535 2536 2537 2538 2539 2540 2541 2542 2543 2544 2545 2546 2547 2548 2549 2550 2551 2552 2553 2554 2555 2556 2557 2558 2559 2560 2561 2562 2563 2564 2565 2566 2567 2568 2569 2570 2571 2572 2573 2574 2575 2576 2577 2578 2579 2580 2581 2582 2583 2584 2585 2586 2587 2588 2589 2590 2591 2592 2593 2594 2595 2596 2597 2598 2599 2600 2601 2602 2603 2604 2605 2606 2607 2608 2609 |
which these fields and data structures are created, initialized and
updated.
-->
[h2 "Glossary"]
<table id=glossary>
<tr><td>Auto-vacuum last root-page<td>
A page number stored as 32-bit integer at byte offset 52 of the
database file header (see section <cite>file_header</cite>). In
an auto-vacuum database, this is the numerically largest
<i>root-page</i> number in the database. Additionally, all pages that
occur before this page in the database are either B-Tree <i>root
pages</i>, <i>pointer-map pages</i> or the <i>locking page</i>.
<tr><td>Auto-vacuum database <td>
Each database is either an auto-vacuum database or a non auto-vacuum
database. Auto-vacuum databases feature pointer-map pages (section
<cite>pointer_map_pages</cite>) and have a non-zero value stored
as a 4-byte big-endian integer at offset 52 of the file header (section
<cite>file_header</cite>).
<tr><td>B-Tree <td>
A B-Tree is a tree structure optimized for offline storage. The table
and index data in an SQLite database file is stored in B-Tree
structures.
<tr><td>B-Tree cell <td>
Each database page that is part of a B-Tree structure contains zero
or more B-Tree cells. A B-Tree cell contains a single B-Tree key value
(either an integer or database record) and optionally an associated
database record value.
<tr><td>B-Tree page <td>
A database page that is part of a B-Tree tree structure (not an
overflow page).
<tr><td>(B-Tree) page header <td>
The 8 (leaf pages) or 12 (internal node pages) byte header that
occurs at the start of each B-Tree page.
<tr><td>Cell content area <td>
The area within a B-Tree page in which the B-Tree cells are stored.
<tr><td>(Database) text encoding <td>
The text encoding used for all text values in the database file. One
................................................................................
<tr><td>Database record data area <td>
Following the database record header in each database record is
the database record data area. It contains the actual data (string
content, numeric value etc.) of all values in the record
(see section <cite>record_format</cite>).
<tr><td>Default pager cache size <td>
A 32-bit integer field stored at byte offset 48 of the database file
header (see section <cite>file_header</cite>).
<tr><td style="white-space:nowrap">(Database) usable page size <td>
The number of bytes of each database page that is usable. This
is the page-size less the number of bytes left unused at the end
of each page. The number of bytes left unused is governed by the
value stored at offset 20 of the file header (see section
<cite>file_header</cite>).
<tr><td>File format read version <td>
Single byte field stored at byte offset 20 of the database file header
(see section <cite>file_header</cite>).
<tr><td>File format write version <td>
Single byte field stored at byte offset 19 of the database file header
(see section <cite>file_header</cite>).
<tr><td>File change counter <td>
A 32-bit integer field stored at byte offset 24 of the database file
header (see section <cite>file_header</cite>). Normally, SQLite
increments this value each time it commits a transaction.
<tr><td>Fragment <td>
A block of 3 or less bytes of unused space within the cell content
area of a B-Tree page.
<tr><td>Free block <td>
................................................................................
<tr><td>Index B-Tree <td>
One of two variants on the B-Tree data structure used within SQLite
database files. An index B-Tree (section <cite>index_btrees</cite>)
uses database records as keys.
<tr><td>Incremental Vacuum flag <td>
A 32-bit integer field stored at byte offset 64 of the database file
header (see section <cite>file_header</cite>). In auto-vacuum
databases, if this field is non-zero then the database is not
automatically compacted at the end of each transaction.
<tr><td>Locking page <td>
The database page that begins at the 1GB (2<sup>30</sup> byte)
boundary. This page is always left unused.
<tr><td>Logical database <td>
An SQLite database file is a serialized representation of a logical
database. A logical database consists of the SQL database schema,
the content of the various tables in the database, and assorted
database properties that may be set by the user (auto-vacuum,
page-size, user-cookie value etc.),
<tr><td>Non-auto-vacuum database <td>
Any database that is not an auto-vacuum database. A non-auto-vacuum
database contains no pointer-map pages and has a zero value stored
in the 4-byte big-endian integer field at offset 52 of the database
file header (section <cite>file_header</cite>).
<tr><td>Overflow chain <td>
A linked list of overflow pages across which a single (large)
database record is stored (see section
<cite>overflow_page_chains</cite>).
<tr><td>Overflow page <td>
If a B-Tree cell is too large to store within a B-Tree page, a
portion of it is stored using a chain of one or more overflow pages
(see section <cite>overflow_page_chains</cite>).
<tr><td>Pointer-map page <td>
A database page used to store meta data only present in auto-vacuum
databases (see section <cite>pointer_map_pages</cite>).
<tr><td>Right child page <td>
Each internal B-Tree node page has one or more child pages. The
rightmost of these (the one containing the largest key values) is
known as the right child page.
<tr><td>Root page <td>
A root page is a database page used to store the root node of a
B-Tree data structure.
<tr><td>Schema layer file format <td>
An integer between 1 and 4 stored as a 4 byte big-endian integer at
offset 44 of the file header (section <cite>file_header</cite>).
Certain file format constructions may only be present in databases
with a certain minimum schema layer file format value.
<tr><td>Schema table <td>
The table B-Tree with root-page 1 used to store database records
describing the database schema. Accessible as the "sqlite_master"
table from within SQLite.
<tr><td>Schema version <td>
A 32-bit integer field stored at byte offset 40 of the database file
header (see section <cite>file_header</cite>). Normally, SQLite
increments this value each time it modifies the databas schema.
<tr><td>Table B-Tree <td>
One of two variants on the B-Tree data structure used within SQLite
database files. A table B-Tree (section <cite>table_btrees</cite>)
uses 64 bit integers as key values and stores an associated database
record along with each key value.
<tr><td>User cookie <td>
A 32-bit integer field stored at byte offset 60 of the database file
header (see section <cite>file_header</cite>). Normally, SQLite
increments this value each time it modifies the databas schema.
<tr><td>Variable Length Integer <td>
A format used for storing 64-bit signed integer values in SQLite
database files. Consumes between 1 and 9 bytes of space, depending
on the precise value being stored.
................................................................................
fields and data structures described in section
<cite>database_file_format</cite> under various circumstances. These
requirements are to a certain extent derived from the requirements
in this section.
-->
[h1 "Database File Format Details" database_file_format]
<p>
This section describes the various fields and sub-structures that make up
the format used by SQLite to serialize a logical SQL database.
<p>
This section does not contain requirements governing the behaviour of any
software system. Instead, along with the plain language description of the
................................................................................
are true. <span class=todo>mention the requirements numbering scheme
here.</span> A software system that wishes to interoperate with other
systems using the SQLite database file format should only ever
output well-formed SQLite databases. In the case of SQLite itself,
the system should ensure that the database file is well-formed at
the conclusion of each database transaction.
[h2 "File Format Overview" "fileformat_overview"]
<p>
A B-Tree is a data structure designed for offline storage of a set of
unique key values. It is structured so as to support fast querying
for a single key or range of keys. As implemented in SQLite, each
entry may be associated with a blob of data that is not part of the
key. For the canonical introduction to the B-Tree and its variants,
refer to reference <cite>ref_comer_btree</cite>. The B-Tree
................................................................................
<p>
Each SQLite database file begins with a 100-byte header. The header
file consists of a well known 16-byte sequence followed by a series of
1, 2 and 4 byte unsigned integers. All integers in the file header (as
well as the rest of the database file) are stored in big-endian format.
<p>
The well known 16-byte sequence that begins every SQLite database file
is:
<pre>
0x53 0x51 0x4c 0x69 0x74 0x65 0x20 0x66 0x6f 0x72 0x6d 0x61 0x74 0x20 0x33 0x00</pre>
<p>
Interpreted as UTF-8 encoded text, this byte sequence corresponds
to the string "SQLite format 3" followed by a nul-terminator byte.
................................................................................
The schema version. Each time the database schema is modified (by
creating or deleting a database table, index, trigger or view)
the value of the 32-bit unsigned integer stored in this field
is incremented.
[Tr]<td>44..47 <td>4<td>
<p style="margin-top:0">
Schema layer file-format. This value is similar to the
"read-version" and "write-version" fields at offsets 18 and 19
of the database file header. If SQLite encounters a database
with a schema layer file-format value greater than the file-format
that it understands (currently 4), then SQLite will refuse to
access the database.
<p>
Usually, this value is set to 1. However, if any of the following
file-format features are used, then the schema layer file-format
must be set to the corresponding value or greater:
<ol start=2 style="margin-bottom:0">
<li> Implicit NULL values at the end of table records
(see section <cite>table_btree_content</cite>).
<li> Implicit default (non-NULL) values at the end of table
records (see section <cite>table_btree_content</cite>).
<li> Descending indexes (see section
<cite>index_btree_compare_func</cite>) and Boolean values
in database records (see section <cite>record_format</cite>,
serial types 8 and 9).
</ol>
[Tr]<td>48..51 <td>4<td>
Default pager cache size. This field is used by SQLite to store
the recommended pager cache size to use for the database.
[Tr]<td>52..55 <td>4<td>
For auto-vacuum capable databases, the numerically largest
root-page number in the database. Since page 1 is always the
root-page of the schema table (section <cite>schema_table</cite>),
this value is always non-zero for auto-vacuum databases. For
non-auto-vacuum databases, this value is always zero.
[Tr]<td>56..59 <td>4<td>
(constant) Database text encoding. A value of 1 means all
text values are stored using UTF-8 encoding. 2 indicates
little-endian UTF-16 text. A value of 3 means that the database
................................................................................
<p>
The four byte block beginning at offset 28 is unused. As is the
32 byte block beginning at offset 68.
</p>
<p>
Some of the following requirements state that certain database header
fields must contain defined constant values, even though the sqlite
database file format is designed to allow various values. This is
done to artificially constrain the definition of a
<i>well-formed database</i> in order to make implementation and
testing more practical.
<p class=req id=H30030>
[fileformat_import_requirement H30030]
<p>
Following the 16 byte magic string in the file header is the
<i>page size</i>, a 2-byte field. See section
<cite>pages_and_page_types</cite> for details.
<p class=req id=H30040>
[fileformat_import_requirement H30040]
<p class=req id=H30050>
[fileformat_import_requirement H30050]
................................................................................
For now, it is sufficient to know that the B-Tree structure is a
data structure that stores a set of records. Each record is an
ordered set of SQL values (the format of which is described in
section <cite>record_format</cite>). Given the root page number of
the B-Tree structure (which is well known for the schema table), it
is possible to iterate through the set of records.
<p>
The schema table contains a record for each SQL table (including
virtual tables) except for sqlite_master, and for each index, trigger
and view in the logical database. There is also an entry for each
UNIQUE or PRIMARY KEY clause present in the definition of a database
table. Each record in the schema table contains exactly 5 values, in
the following order:
[Table]
[Tr]<th>Field<th>Description
[Tr]<td>Schema item type.
<td>A string value. One of "table", "index", "trigger" or "view",
according to the schema item type. Entries associated with
UNIQUE or PRIMARY KEY clauses have this field set to "index".
[Tr]<td>Schema item name.
<td>A string value. The name of the database schema item (table,
index, trigger or view) associated with this record, if any.
Entries associated with UNIQUE or PRIMARY KEY clauses have
this field set to a string of the form
"sqlite_autoindex_<name>_<idx>" where <name>
is the name of the SQL table and <idx> is an integer
value.
[Tr]<td style="white-space:nowrap">Associated table name.
<td>A string value. For "table"
or "view" records this is a copy of the second (previous) value.
For "index" and "trigger" records, this field is set to the name
of the associated database table.
[Tr]<td style="white-space:nowrap">The "root page" number.
<td>For "trigger" and "view" records, as well as "table" records
associated with virtual tables, this is set to NULL. For other
"table" and "index" records (including those associated with
UNIQUE or PRIMARY KEY clauses), this field contains the root
page number (an integer) of the B-Tree structure that contains
the table or index data.
[Tr]<td>The SQL statement.
<td>A string value. The SQL statement used to create the schema
item (i.e. the complete text of an SQL "CREATE TABLE"
statement). This field contains an empty string for table
entries associated with PRIMARY KEY or UNIQUE clauses.
<span class=todo>Refer to some document that describes these
SQL statements more precisely.</span>
</table>
<p>
Logical database schema items other than non-virtual tables and indexes
(including indexes created by UNIQUE or PRIMARY key constraints) do not
require any other data structures to be created within the database
file.
<p>
Tables and indexes on the other hand, are represented within the
database file by both an entry in the schema table and a B-Tree
structure stored elsewhere in the file. The specific B-Tree associated
with each database table or index is identified by its root page
number, which is stored in the 4th field of the schema table record.
In a non-auto-vacuum database, the B-Tree root pages may be stored
anywhere within the database file. For an auto-vacuum database, all
B-Tree root pages must at all times form a contiguous set starting
at page 3 of the database file, skipping any pages that are required to
be used as pointer-map pages (see section
<cite>pointer_map_pages</cite>).
<p>
As noted in section <cite>file_header</cite>, in an auto-vacuum
database the page number of the page immediately following the
final root page in the contiguous set of root pages is stored
as a 4 byte big-endian integer at byte offset 52 of the database
file header. Unless that page is itself a pointer-map page, in which
case the page number of the page following it is stored instead.
<p>
For example, if the schema of a logical database is created using
the following SQL statements:
<pre>
CREATE TABLE abc(a, b, c);
................................................................................
contiguous block. The page that contains the root node of a B-Tree
structure is known as the "root page".
<p>
SQLite uses two distinct variants of the B-Tree structure:
<ul>
<li>The <b>table B-Tree</b>, which uses 64-bit integer values for keys.
In a table B-Tree, an associated database record (section
<cite>record_format</cite>) is stored along with each entry. Table
B-Tree structures are described in detail in section
<cite>table_btrees</cite>.
<li>The <b>index B-Tree</b>, which uses database records as keys. Index
B-Tree structures are described in detail in section
<cite>index_btrees</cite>.
</ul>
................................................................................
<p class=req id=H30500>
[fileformat_import_requirement H30500]
<p class=req id=H30510>
[fileformat_import_requirement H30510]
[h3 "Variable Length Integer Format" "varint_format"]
<p>
In several parts of the B-Tree structure, 64-bit twos-complement signed
integer values are stored in the "variable length integer format"
described here.
<p>
A variable length integer consumes from one to nine bytes of space,
depending on the value stored. Seven bits are used from each of
the first eight bytes present, and, if present, all eight from
the final ninth byte. Unless the full nine byte format is used, the
serialized form consists of all bytes up to and including the first
byte with the 0x80 bit cleared.
<p>
The number of bytes present depends on the position of the most
significant set bit in the 64-bit word. Negative numbers always have
the most significant bit of the word (the sign bit) set and so are
always encoded using the full nine bytes. Positive integers may be
encoded using less space. The following table shows the 9 different
length formats available for storing a variable length integer
value.
[Table]
[Tr]<th>Bytes<th>Value Range<th>Bit Pattern
[Tr]<td>1<td>7 bit<td>0xxxxxxx
[Tr]<td>2<td>14 bit<td>1xxxxxxx 0xxxxxxx
[Tr]<td>3<td>21 bit<td>1xxxxxxx 1xxxxxxx 0xxxxxxx
[Tr]<td>4<td>28 bit<td>1xxxxxxx 1xxxxxxx 1xxxxxxx 0xxxxxxx
................................................................................
<p>
When using the full 9 byte representation, the first byte contains
the 7 most significant bits of the 64-bit value. The final byte of
the 9 byte representation contains the 8 least significant bits of
the 64-bit value. When using one of the other representations, the
final byte contains the 7 least significant bits of the 64-bit value.
The second last byte, if present, contains the 7 next least signficant
bits of the value, and so on. The significant bits of the 64-bit
value for which no storage is provided are assumed to be zero.
<p>
When encoding a variable length integer, SQLite usually selects the
most compact representation that provides enough storage to accomadate
the most significant set bit of the value. This is not required
however, using more bytes than is strictly necessary when encoding
an integer is valid.
[Table]
[Tr]<th>Decimal<th>Hexadecimal <th>Variable Length Integer
[Tr]<td>43 <td>0x000000000000002B <td>0x2B
[Tr]<td>200815 <td>0x000000000003106F <td>0x8C 0xA0 0x6F
[Tr]<td>-1 <td>0xFFFFFFFFFFFFFFFF
<td>0xFF 0xFF 0xFF 0xFF 0xFF 0xFF 0xFF 0xFF 0xFF
[Tr]<td>-78056 <td>0xFFFFFFFFFFFECD56
<td>0xFF 0xFF 0xFF 0xFF 0xFF 0xFF 0xFD 0xCD 0x56
</table>
<p class=req id=H30520>
................................................................................
<p class=req id=H30750>
[fileformat_import_requirement H30750]
<p class=req id=H30760>
[fileformat_import_requirement H30760]
<p>
The precise way in which index B-Tree pages and cells are formatted is
described in subsequent sections.
[h4 "Index B-Tree Content"]
<p>
The database file contains one index B-Tree for each database index
in the logical database, including those created by UNIQUE or
PRIMARY KEY clauses in table declarations. Each record stored in
an index B-Tree contains the same number of fields, the number of
indexed columns in the database index declaration plus one.
<p>
An index B-Tree contains an entry for each row in its associated
database table. The fields of the record used as the index B-Tree
key are copies of each of the indexed columns of the associated
database row, in order, followed by the rowid value of the same
................................................................................
<p class=req id=H30800>
[fileformat_import_requirement H30800]
[h4 "Record Sort Order" "index_btree_compare_func"]
<p>
This section defines the comparison function used when database
records are used as B-Tree keys for index B-Trees. The comparison
function is only defined when both database records contain the same
number of fields.
<p>
When comparing two database records, the first field of one
record is compared to the first field of the other. If they
are not equal, the next pair of fields are compared, and so
on. If all the fields in the database records are equal, then
the two records are considered equal. Otherwise, the result
................................................................................
<li>If one value is text and the other a blob, the text value
is considered lesser.
<li>If both values are blobs, memcmp() is used to determine the
results of the comparison function. If one blob is a prefix
of the other, the shorter blob is considered lesser.
</ol>
<p>
Each column of a database index may be declared as "descending".
<span class=todo>Link to document with CREATE INDEX syntax.</span>
In SQLite database files with a schema layer file-format equal
to 4, this modifies the order in which the records are stored in
the corresponding index B-Tree structure. For each index column
declared as descending, the results of the above comparison
procedure are inverted.
<p>
The columns of database indexes created by UNIQUE or PRIMARY
KEY clauses are never treated as descending.
<p class=todo>
Need requirements style statements for this information. Easier
to do once collation sequences have been defined somewhere.
................................................................................
<p class=req id=H30900>
[fileformat_import_requirement H30900]
<p class=req id=H30910>
[fileformat_import_requirement H30910]
<p>
The following requirements govern management of free-space within the
page content area (both table and index B-Tree pages).
<p class=req id=H30920>
[fileformat_import_requirement H30920]
<p class=req id=H30930>
[fileformat_import_requirement H30930]
................................................................................
by child page C(n+1) have values larger than that of I(n), for values
of n in the same range.
[Figure tabletree.gif figure_tabletree "Table B-Tree Tree Structure"]
<p>
Figure <cite>figure_tabletree</cite> depicts a table B-Tree containing
a contiguous set of 14 integer keys starting with 1. Each key <i>n</i>
has an associated database record R<i>n</i>. All the keys and their
associated records are stored in the leaf pages. The internal node
pages contain no database data, their only purpose is to provide
a way to navigate the tree structure.
<p class=req id=H31020>
[fileformat_import_requirement H31020]
<p class=req id=H31030>
[fileformat_import_requirement H31030]
................................................................................
<p class=req id=H31040>
[fileformat_import_requirement H31040]
<p class=req id=H31050>
[fileformat_import_requirement H31050]
<p>
The precise way in which table B-Tree pages and cells are formatted is
described in subsequent sections.
[h4 "Table B-Tree Content" table_btree_content]
<p>
The database file contains one table B-Tree for each database table
in the logical database. Although some data may be duplicated in
index B-Tree structures, the table B-Tree is the primary location
of table data.
<p>
The table B-Tree contains exactly one entry for each row in the
database table. The integer key value used for the B-Tree entry is
the value of the "rowid" field of the corresponding logical row
in the database table. The database row fields are stored in the
record associated with the table B-Tree entry, in the same order
as they appear in the logical database table. The first field in
the record (see section <cite>record_format</cite>) contains the
value of the leftmost field in the database row, and so on.
<p>
If a database table column is declared as an INTEGER PRIMARY KEY,
then it is an alias for the rowid field, which is stored as the
table B-Tree key value. Instead of duplicating the integer value
in the associated record, the record field associated with the
INTEGER PRIMARY KEY column is always set to an SQL NULL.
<p>
Finally, if the schema layer file-format is greater than or equal
to 2, some of the records stored in table B-Trees may contain
less fields than the associated logical database table does columns.
If the schema layer file-format is exactly 2, then the logical
database table column values associated with the "missing" fields
are SQL NULL. If the schema layer file-format is greater than
2, then the values associated with the "missing" fields are
determined by the default value of the associated database table
columns.
<span class=todo>Reference to CREATE TABLE syntax. How are default
values determined?</span>
<p class=req id=H31060>
[fileformat_import_requirement H31060]
<p class=req id=H31070>
[fileformat_import_requirement H31070]
................................................................................
<p class=req id=H31400>
[fileformat_import_requirement H31400]
<p class=req id=H31410>
[fileformat_import_requirement H31410]
<p class=req id=H31420>
[fileformat_import_requirement H31420]
[h1 "Journal File Format" journal_file_format]
<p>
This section describes the format used by an SQLite <i>journal file</i>.
<p>
A journal file consists of one or more <i>journal headers</i>, zero
or more <i>journal records</i> and optionally a <i>master journal
................................................................................
second set of zero or more <i>journal records</i> and so on. There
is no limit to the number of <i>journal headers</i> a journal file
may contain. Following the <i>journal headers</i> and their accompanying
sets of <i>journal records</i> may be the optional <i>master journal
pointer</i>. Or, the file may simply end following the final <i>journal
record</i>.
[h2 "Journal Header Format" journal_header_format]
<p>
A <i>journal header</i> is <i>sector-size</i> bytes in size, where <i>
sector-size</i> is the value returned by the xSectorSize method of
the file handle opened on the database file. Only the first 28 bytes
of the <i>journal header</i> are used, the remainder may contain garbage
data. The first 28 bytes of each <i>journal header</i> consists of an
................................................................................
<p>
All <i>journal headers</i> are positioned in the file so that they
start at a <i>sector size</i> aligned offset. To achieve this, unused
space may be left between the start of the second and subsequent
<i>journal headers</i> and the end of the <i>journal records</i>
associated with the previous header.
[h2 "Journal Record Format" journal_record_format]
<p>
Each <i>journal record</i> contains the original data for a database page
modified by the <i>write transaction</i>. If a rollback is required, then
this data may be used to restore the contents of the database page to the
state it was in before the <i>write transaction</i> was started.
................................................................................
<p>
The set of <i>journal records</i> that follow a <i>journal header</i>
in a <i>journal file</i> are packed tightly together. There are no
alignment requirements for <i>journal records</i> as there are for
<i>journal headers</i>.
[h2 "Master Journal Pointer"]
<p>
To support <i>atomic</i> transactions that modify more than one
database file, SQLite sometimes includes a <i>master journal pointer</i>
record in a <i>journal file</i>. A <i>master journal pointer</i>
contains the name of a <i>master journal-file</i> along with a
check-sum and some well-known values that allow the
................................................................................
Finally, the <b>journal magic</b> field always contains a
well-known 8-byte string value; the same value stored in the
first 8 bytes of a <i>journal header</i>. The well-known
sequence of bytes is:
<pre>0xd9 0xd5 0x05 0xf9 0x20 0xa1 0x63 0xd7</pre>
</table>
<!--
\[h1 "Database File Structure Traversal" "database_file_traversal"]
\[h2 "B-Tree Cursors"]
<h2>B-Tree Access Strategies</h2>
<h3>Full Linear Scan</h3>
<h3>Seek to Value</h3>
<h3>Range Scan</h3>
<h2>Retrieving Record Values</h2>
\[h1 "Database File Manipulation" "database_file_manipulation"]
<h2>Creating a Database</h2>
<h2>Table B-Trees</h2>
<h3>Creating a new Table B-Tree</h3>
<h3>Deleting a Table B-Tree</h3>
<h3>Adding an Entry to a Table B-Tree</h3>
<h3>Removing an Entry from a Table B-Tree</h3>
<h2>Index B-Trees</h2>
<h3>Creating a new Index B-Tree</h3>
<h3>Deleting a Index B-Tree</h3>
<h3>Adding an Entry to a Index B-Tree</h3>
<h3>Removing an Entry from a Index B-Tree</h3>
<h2>Overflow Chains</h2>
<h2>Allocating/Deallocating Pages</h2>
<h2>Allocating a Page</h2>
<h2>Deallocating a Page</h2>
<h2>Auto-Vacuum Commit Operations</h2>
<p>
The previous section described the format of a valid SQLite database
file. This section describes the way in which a database file is
transitioned between valid states by SQLite to effect various
operations, for example creating a table or inserting a database
record.
<p>
SQLite manipulates database files using combinations of the following
operations:
<ol>
<li>Database Initialization (see section
<cite>database_initialization</cite>).
<li>Modification of a global parameter stored in the database file
file-header (see section <cite>database_parameters</cite>).
<li>Creation of a new table.
<li>Creation of a new index.
<li>Creation of a new trigger or view.
<li>Destruction of a database table.
<li>Destruction of a database index.
<li>Destruction of a trigger or view.
<li>Insertion of an index entry.
<li>Removal of an index entry.
<li>Insertion of a table entry.
<li>Removal of a table entry.
</ol>
<p>
For example, execution of a single "UPDATE" statement may result in
many entries being removed and inserted into a table B-Tree and several
index B-Trees.
<p>
The list of operations above itemizes inserting and removing entries
from index and table B-Trees separately. Requirements specifying the
set of database file operations required by various SQL statements
may be found in <i>insert-link</i>.
The requirements in <i>insert-link</i> are written so as to
ensure that the constraints relating the content of a table B-Tree and
its associated index B-Trees are met (see section YYY). By contrast the
requirements specifying the behaviour of the system when a
schema item (table, index, view or trigger) is added to or removed
from a database describe both the schema (sqlite_master) table
modifications and any additional modifications made to other parts
of the database file.
<p class=todo>
Fix this XXX reference. And add any other references to SQLiteRT
requirements documents that may specify requirements in terms of these
operations.
<p class=todo>
VACUUM? Auto-vacuum steps?
\[h2 "Database Creation/Initialization" database_initialization]
<p>
As noted in section <cite>database_file_format</cite> a zero-length
file is a valid empty SQLite database. The first time such a
database is written to, SQLite initializes the the first page of
the database file as described by the following requirements, creating
a one-page empty SQLite database file.
<p class=req>
When initializing a new database file, SQLite shall set the
size of the file to <i>page-size</i> bytes, where <i>page-size</i>
is the database page size in bytes.
<p class=req>
When initializing a new database file, SQLite shall set the
first 16 bytes of the database file to the following values, from
first (byte offset 0) to last (byte offset 15): 0x53 0x51 0x4c
0x69 0x74 0x65 0x20 0x66 0x6f 0x72 0x6d 0x61 0x74 0x20 0x33 0x00.
<p>
The 16 byte values specified in the above requirement are the UTF-8
encoding of the string "SQLite file format 3" followed by a single
nul-terminator byte.
<p class=req>
When initializing a new database file, SQLite shall store the page
size in bytes as a 2-byte big-endian unsigned integer at byte
offset 16 of the new database file.
<p class=todo>
Some requirement to say where the initial page-size comes from. Probably
a reference to the SQL level requirements documenting the page-size
pragma.
<p class=todo>
Requirements for the other fields of the database header. Also to
describe how the part of page 1 after the header is initialized.
\[h2 "Setting Database Parameters" database_parameters]
<p>
The database file-header contains three values that the system may
be required to update in response to the execution of SQL pragma
statements. These are:
<ul>
<li>The default pager-cache size,
<li>The user-cookie value,
<li>The incremental-vacuum flag.
</ul>
<p>
Requirements specified in <cite>sql_sqlitert_requirements</cite>
specify the various scenarios in which the system is required to set
one of the above three values. The following requirements explain
more specifically what the system has to do to achieve this.
<p class=req>
When required to set the default pager-cache size of a database, the
system shall store the new value as a 4-byte big-endian unsigned
integer starting at byte offset 48 of the database file.
<p class=req>
When required to set the user-cookie value of a database, the
system shall store the new value as a 4-byte big-endian unsigned
integer starting at byte offset 60 of the database file.
<p class=req>
When required to set the incremental vacuum flag of a database, the
system shall store the new value as a 4-byte big-endian unsigned
integer starting at byte offset 64 of the database file.
<h2>Creating and Deleting B-Tree Structures</h2>
\[h3 "Table/Index Creation" btree_creation]
<p class=req>
When a new table or index is added to a non auto-vacuum database file,
the system shall initialize a newly allocated database page as the root
page of an empty table or index B-Tree, respectively.
<p class=todo>
Requirements describing in detail how an empty root page is initialized.
<p class=req>
When a new table is added to an auto-vacuum database file, the
system shall initialize the database page immediately following
the root page of the B-Tree structure with the numerically largest
root page number, skipping any pointer-map pages, as the root page of
an empty table B-Tree.
<p class=subreq>
When adding a new table or index to an auto-vacuum database file,
if the selected root page exists and is part of the database
free-list, the system shall remove it from the free-list.
<p class=subreq>
When adding a new table or index to an auto-vacuum database file,
if the selected root page exists and is not part of the database
free-list, then the system shall move the current contents of the
page to a newly allocated page. The system shall update all existing
references to the page within the database file to refer to the
new page number.
<p class=todo>
The above requirement needs to be tested for a B-Tree page, an
overflow page that is at the start of an overflow chain and an
overflow page that is not at the start of a chain. Maybe each
page type should have its own requirement.
<p class=subreq>
When adding a new table or index to an auto-vacuum database file,
the system shall update the pointer-map page entries corresponding
to both the new root page and, if the existing content of the page
was moved, the page to which the existing content was moved.
<p class=req>
When a new table is added to a database file, the system shall
add a new entry to the table B-Tree with page 1 as its root page (the
sqlite_master table).
<p class=todo>
Requirements for the contents of the new sqlite_master row.
<h3>Table/Index Destruction</h3>
<h2>Creating and Deleting Other Schema Items</h2>
<h2>Data Manipulation</h2>
<p>
This section describes the ways in which the system is required to
manipulate B-Tree structures within a database file. Various
operations at the SQL level require the system to insert or remove
entries from both table and index B-Trees. <span class=todo>It would be
good to reference some other requirements document here.</span>
<h3>Inserting Records</h3>
\[h4 "Table B-Tree Inserts"]
<p class=req>
When required to insert a new entry into a table B-Tree, the system
shall format a new table B-Tree leaf node cell containing the
integer key value and accompanying database record, and add the
new cell to a leaf node of the table B-Tree structure.
<p>
The format of a "table B-Tree leaf node cell", as specified in the
above requirement, is described in section
<cite>table_btree_cell_format</cite>.The following sub-requirements
state that the system shall "attempt to insert a cell" into a given
page. This operation may fail if there is not enough room on the
selected page.
<p class=subreq>
When inserting a a new table B-Tree leaf node cell, if the table
B-Tree is empty or consists of a single page only, the system shall
attempt to insert the new cell into the root page of the B-Tree.
<p class=subreq>
When inserting a a new table B-Tree leaf node cell, if the table
B-Tree constructor consists of more than one page, then the system
shall attempt to insert the new cell into the leaf node page that
currently contains the largest key value that is smaller than
the key value of the cell being inserted.
<p class=todo>
Finish this.
\[h4 "Index B-Tree Inserts"]
<p class=todo>
Finish this.
<h3>Removing Records</h3>
<p class=todo>
Finish this.
<h2>Page Management</h2>
<p>
When a new table or index is created, or when a new entry is added
to a table or index that does not fit into one of the B-Tree structures
existing pages, SQLite is required to select a new, presently unused,
database page to use for the new data. The new page may be obtained
using one of two methods:
<ul>
<li>The size of the database file may be extended by <i>page-size</i>
bytes, creating a new page at the end of the current database file.
<li>A database page that is currently part of the free page list
(refer to section <cite>free_page_list</cite>) may be removed from
the free-list and reused.
</ul>
<p>
This process of selecting a new page to use is refered to as
<i>allocating a new page</i>. See section <cite>page_allocation</cite>
for further details.
<p>
Similarly, when a table or index is removed from the database, or
when a B-Tree structure is reorganized such that it consumes fewer
pages, database pages may become unused. In this case the unused pages
must be added to the database free-list. This process is known as
<i>freeing a page</i>. See section <cite>page_deallocation</cite> for
details.
<p>
Sometimes, when operating on an auto-vacuum database, the system is
required to remove a specific page from the free page list. This
can occur:
<ul>
<li>When a new database table or index is created (section
<cite>btree_creation</cite>), or
<li>during an incremental-vacuum operation (section
<cite>incremental_vacuum</cite>).
</ul>
<p>
The requirements found in this section specify the manner in which
the system is required to manipulate the contents of database
free-list pages to achieve this are found in section
<cite>page_removal</cite>.
\[h3 "Page Allocation" page_allocation]
<p>
If the database free-list is empty, then the new page is allocated
by extending the database file:
<p class=req>
When SQLite allocates a new database page, if the database free
page list is completely empty, the page shall be allocated by
extending the database file.
<p class=subreq>
If extending the database file by page-size bytes means that the
final page of the database file would be a pointer-map page, SQLite
shall extend the database file by (2 * page-size) bytes. The final
page of the database file after it has been extended shall be used
as the new (allocated) page.
<p class=subreq>
Otherwise, SQLite shall extend the database file by page-size bytes.
The final page of the database file after it has been extended shall
be used as the new (allocated) page.
<p>
In the above requirements, the condition "would be a pointer-map
page" is only true if both of the following are true:
<ul>
<li>The database the page belongs to is an auto-vacuum database, and
<li>The page number is equal to <i>(2 + n * (usable-size / 5))</i>
for some integer <i>n</i>.
</ul>
<p>
Otherwise, if the database free page list is not empty when SQLite
is required to allocate a new database page, then the page is
allocated by removing a page from the free-list for reuse.
<p class=req>
When SQLite allocates a new database page, if the database free page
list is not empty, then SQLite shall allocate the page by reusing
a page that is currently linked into the database free page list.
The page shall be removed from the free-list before it is resused.
<p class=subreq>
When allocating a page from the free-list, if the first page
in the free-list trunk has no leaves, then SQLite shall use this
page as the new (allocated) page. Otherwise, SQLite shall select
one of the leaves belonging to the first page in the free-list
trunk, remove its entry from the trunk page and use it as the
new (allocated) page.
<p class=todo>
How do we pick a single leaf from the set of leaves on the trunk
page?
<p class=subreq>
If the page removed from the free-list is the first page in the
free-list trunk, SQLite shall update the 4-byte integer value stored
at byte offset 32 to contain the page number of the second page
in the free-list trunk if it exists, or zero if the freelist is
now empty.
<p class=subreq>
After removing a page from the free-list, SQLite shall update
the 4-byte integer value stored at byte offset 36 of the database
file header to reflect the new number of pages in the database
free page list (one less than before).
\[h3 "Page Deallocation" page_deallocation]
<p class=req>
If SQLite is required to free a database page when the free-list
is complete empty, or when the first page of the free-list trunk
is completely full, SQLite shall use the freed page as the new
head of the free-list trunk.
<p class=subreq>
When a newly freed page is made the head of the free-list trunk,
the system shall update the 4-byte integer value stored at byte
offset 32 to store the page number of the new free-list trunk head.
<p class=subreq>
When a newly freed page is made the new head of the free-list trunk,
the system shall store the page number of the previous head page
as a big-endian integer in the first 4 bytes of the page data, or
zero if the free-list was previously empty.
<p class=subreq>
When a newly freed page is made the new head of the free-list trunk,
the system shall set the second block of 4 bytes on the page to
zero (indicating that the free-list trunk page currently has no
leaves).
<p class=req>
If SQLite is required to free a database page when the first
page of the free-list trunk exists and has enough free space for
another leaf, the freed page shall become a leaf of the first
page in the free-list trunk.
<p class=req>
After removing a page from the free-list, SQLite shall update
the 4-byte integer value stored at byte offset 36 of the database
file header to reflect the new number of pages in the database
free page list (one less than before).
\[h3 "Removing a Page From The Free List" page_removal]
<p class=req>
When the system is required to remove a specific page from the
database free-list, and that page is a free-list leaf page, the
system shall remove the specified leaf page number from the
relevant trunk page.
<p class=req>
When the system is required to remove a specific page from the
database free-list, and that page is an empty free-list trunk page,
the system shall remove the specified page from the free-list
trunk linked list.
<p class=req>
When the system is required to remove a specific page from the
database free-list, and that page is a non-empty free-list trunk
page, the system shall move the contents of the trunk page
to its first leaf page, remove the first leaf entry from the new
trunk page, then link the new trunk page into the free-list trunk
in place of the page being removed.
\[h3 "Database Reorganization (auto-vacuum)" incremental_vacuum]
<p class=todo>
Requirements describing incremental vacuum steps. And on-commit
handling in auto-vacuum databases.
-->
[h1 References]
<table id="refs" style="width:auto; margin: 1em 5ex">
<tr><td style="width:5ex" id="ref_comer_btree">\[1\]<td>
Douglas Comer, <u>Ubiquitous B-Tree</u>, ACM Computing Surveys (CSUR),
v.11 n.2, pages 121-137, June 1979.
|
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | > > > > > > > > > > > > > > > > > > | > > | | | | < < > < < < < > < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < > > > > | > > > > > > < < < < < < < < | < < < < > > > | > > > > < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < > > > > > > | < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < | < < < < < > > > > > > < < < < < < < < < | < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < > > > > > > > > > | < < < < < < < < < < < < < < | < < < < < < < < < < < > > < < < < < < < < < < < < < < < < < < < < < < < < < < > < < < < < < < < < < < < < < < < < < < < > < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < |
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 ... 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 ... 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 ... 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 ... 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 ... 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 ... 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 ... 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 ... 794 795 796 797 798 799 800 801 802 803 804 805 806 807 808 809 810 811 812 813 814 815 816 817 818 819 820 821 822 823 824 825 826 827 828 829 830 831 832 833 834 835 836 837 838 839 840 841 842 843 844 845 846 847 848 849 850 851 852 853 854 855 856 857 858 859 860 861 862 863 864 865 866 867 868 869 870 871 872 873 ... 987 988 989 990 991 992 993 994 995 996 997 998 999 1000 1001 .... 1003 1004 1005 1006 1007 1008 1009 1010 1011 1012 1013 1014 1015 1016 1017 1018 1019 1020 1021 1022 1023 1024 1025 1026 1027 1028 1029 1030 1031 1032 1033 1034 .... 1041 1042 1043 1044 1045 1046 1047 1048 1049 1050 1051 1052 1053 1054 1055 1056 1057 1058 1059 1060 1061 1062 1063 1064 1065 1066 1067 .... 1269 1270 1271 1272 1273 1274 1275 1276 1277 1278 1279 1280 1281 1282 1283 1284 1285 1286 1287 1288 1289 1290 1291 .... 1302 1303 1304 1305 1306 1307 1308 1309 1310 1311 1312 1313 1314 1315 1316 1317 .... 1336 1337 1338 1339 1340 1341 1342 1343 1344 1345 1346 1347 1348 1349 1350 1351 1352 1353 1354 1355 1356 1357 1358 .... 1481 1482 1483 1484 1485 1486 1487 1488 1489 1490 1491 1492 1493 1494 1495 .... 1603 1604 1605 1606 1607 1608 1609 1610 1611 1612 1613 1614 1615 1616 1617 1618 1619 1620 1621 .... 1622 1623 1624 1625 1626 1627 1628 1629 1630 1631 1632 1633 1634 1635 1636 1637 1638 1639 1640 1641 1642 1643 1644 1645 1646 1647 1648 1649 1650 1651 1652 1653 1654 1655 1656 1657 1658 1659 1660 1661 1662 1663 1664 1665 1666 1667 1668 1669 1670 .... 2027 2028 2029 2030 2031 2032 2033 2034 2035 2036 2037 2038 2039 2040 2041 2042 2043 2044 2045 2046 2047 2048 2049 2050 2051 2052 2053 2054 2055 2056 2057 2058 2059 2060 2061 .... 2065 2066 2067 2068 2069 2070 2071 2072 2073 2074 2075 2076 2077 2078 2079 .... 2119 2120 2121 2122 2123 2124 2125 2126 2127 2128 2129 2130 2131 2132 2133 .... 2158 2159 2160 2161 2162 2163 2164 2165 2166 2167 2168 2169 2170 2171 2172 .... 2219 2220 2221 2222 2223 2224 2225 2226 2227 2228 2229 2230 2231 2232 2233 2234 2235 2236 2237 2238 2239 2240 2241 2242 2243 2244 2245 2246 2247 2248 2249 2250 2251 2252 2253 2254 2255 2256 2257 2258 2259 2260 2261 2262 2263 2264 2265 2266 2267 2268 2269 2270 2271 2272 2273 2274 2275 2276 2277 2278 2279 2280 2281 2282 2283 2284 2285 2286 2287 2288 2289 2290 2291 2292 2293 2294 2295 2296 2297 2298 |
which these fields and data structures are created, initialized and
updated.
-->
[h2 "Glossary"]
<table id=glossary>
<tr><td>Auto-vacuum last root-page<td>
A page number stored as 32-bit integer at byte offset 52 of the
database file header (see section <cite>file_header</cite>). In
an auto-vacuum database, this is the numerically largest
<i>root-page</i> number in the database. Additionally, all pages that
occur before this page in the database are either B-Tree <i>root
pages</i>, <i>pointer-map pages</i> or the <i>locking page</i>.
<tr><td>Auto-vacuum database <td>
Each database is either an auto-vacuum database or a non auto-vacuum
database. Auto-vacuum databases feature pointer-map pages (section
<cite>pointer_map_pages</cite>) and have a non-zero value stored
as a 4-byte big-endian integer at offset 52 of the file header (section
<cite>file_header</cite>).
<tr><td>B-Tree <td>
A B-Tree is a tree structure optimized for offline storage. The table
and index data in an SQLite database file is stored in B-Tree
structures.
<tr><td>B-Tree cell <td>
Each database page that is part of a B-Tree structure contains zero
or more B-Tree cells. A B-Tree cell contains a single B-Tree key value
(either an integer or database record) and optionally an associated
database record value.
<tr><td>B-Tree page <td>
A database page that is part of a B-Tree tree structure (not an
overflow page).
<tr><td>(B-Tree) page header <td>
The 8 (leaf pages) or 12 (internal node pages) byte header that
occurs at the start of each B-Tree page.
<tr><td>Cell content area <td>
The area within a B-Tree page in which the B-Tree cells are stored.
<tr><td>(Database) text encoding <td>
The text encoding used for all text values in the database file. One
................................................................................
<tr><td>Database record data area <td>
Following the database record header in each database record is
the database record data area. It contains the actual data (string
content, numeric value etc.) of all values in the record
(see section <cite>record_format</cite>).
<tr><td>Default pager cache size <td>
A 32-bit integer field stored at byte offset 48 of the database file
header (see section <cite>file_header</cite>).
<tr><td style="white-space:nowrap">(Database) usable page size <td>
The number of bytes of each database page that is usable. This
is the page-size less the number of bytes left unused at the end
of each page. The number of bytes left unused is governed by the
value stored at offset 20 of the file header (see section
<cite>file_header</cite>).
<tr><td>File format read version <td>
Single byte field stored at byte offset 20 of the database file header
(see section <cite>file_header</cite>).
<tr><td>File format write version <td>
Single byte field stored at byte offset 19 of the database file header
(see section <cite>file_header</cite>).
<tr><td>File change counter <td>
A 32-bit integer field stored at byte offset 24 of the database file
header (see section <cite>file_header</cite>). Normally, SQLite
increments this value each time it commits a transaction.
<tr><td>Fragment <td>
A block of 3 or less bytes of unused space within the cell content
area of a B-Tree page.
<tr><td>Free block <td>
................................................................................
<tr><td>Index B-Tree <td>
One of two variants on the B-Tree data structure used within SQLite
database files. An index B-Tree (section <cite>index_btrees</cite>)
uses database records as keys.
<tr><td>Incremental Vacuum flag <td>
A 32-bit integer field stored at byte offset 64 of the database file
header (see section <cite>file_header</cite>). In auto-vacuum
databases, if this field is non-zero then the database is not
automatically compacted at the end of each transaction.
<tr><td>Locking page <td>
The database page that begins at the 1GB (2<sup>30</sup> byte)
boundary. This page is always left unused.
<tr><td>Logical database <td>
An SQLite database file is a serialized representation of a logical
database. A logical database consists of the SQL database schema,
the content of the various tables in the database, and assorted
database properties that may be set by the user (auto-vacuum,
page-size, user-cookie value etc.),
<tr><td>Non-auto-vacuum database <td>
Any database that is not an auto-vacuum database. A non-auto-vacuum
database contains no pointer-map pages and has a zero value stored
in the 4-byte big-endian integer field at offset 52 of the database
file header (section <cite>file_header</cite>).
<tr><td>Overflow chain <td>
A linked list of overflow pages across which a single (large)
database record is stored (see section
<cite>overflow_page_chains</cite>).
<tr><td>Overflow page <td>
If a B-Tree cell is too large to store within a B-Tree page, a
portion of it is stored using a chain of one or more overflow pages
(see section <cite>overflow_page_chains</cite>).
<tr><td>Pointer-map page <td>
A database page used to store meta data only present in auto-vacuum
databases (see section <cite>pointer_map_pages</cite>).
<tr><td>Right child page <td>
Each internal B-Tree node page has one or more child pages. The
rightmost of these (the one containing the largest key values) is
known as the right child page.
<tr><td>Root page <td>
A root page is a database page used to store the root node of a
B-Tree data structure.
<tr><td>Schema layer file format <td>
An integer between 1 and 4 stored as a 4 byte big-endian integer at
offset 44 of the file header (section <cite>file_header</cite>).
Certain file format constructions may only be present in databases
with a certain minimum schema layer file format value.
<tr><td>Schema table <td>
The table B-Tree with root-page 1 used to store database records
describing the database schema. Accessible as the "sqlite_master"
table from within SQLite.
<tr><td>Schema version <td>
A 32-bit integer field stored at byte offset 40 of the database file
header (see section <cite>file_header</cite>). Normally, SQLite
increments this value each time it modifies the databas schema.
<tr><td>Table B-Tree <td>
One of two variants on the B-Tree data structure used within SQLite
database files. A table B-Tree (section <cite>table_btrees</cite>)
uses 64 bit integers as key values and stores an associated database
record along with each key value.
<tr><td>User cookie <td>
A 32-bit integer field stored at byte offset 60 of the database file
header (see section <cite>file_header</cite>). Normally, SQLite
increments this value each time it modifies the databas schema.
<tr><td>Variable Length Integer <td>
A format used for storing 64-bit signed integer values in SQLite
database files. Consumes between 1 and 9 bytes of space, depending
on the precise value being stored.
................................................................................
fields and data structures described in section
<cite>database_file_format</cite> under various circumstances. These
requirements are to a certain extent derived from the requirements
in this section.
-->
[h1 "Database Image Format Details" database_file_format]
<p>
This section describes the various fields and sub-structures that make up
the format used by SQLite to serialize a logical SQL database.
<p>
This section does not contain requirements governing the behaviour of any
software system. Instead, along with the plain language description of the
................................................................................
are true. <span class=todo>mention the requirements numbering scheme
here.</span> A software system that wishes to interoperate with other
systems using the SQLite database file format should only ever
output well-formed SQLite databases. In the case of SQLite itself,
the system should ensure that the database file is well-formed at
the conclusion of each database transaction.
[h2 "Image Format Overview" "fileformat_overview"]
<p>
A B-Tree is a data structure designed for offline storage of a set of
unique key values. It is structured so as to support fast querying
for a single key or range of keys. As implemented in SQLite, each
entry may be associated with a blob of data that is not part of the
key. For the canonical introduction to the B-Tree and its variants,
refer to reference <cite>ref_comer_btree</cite>. The B-Tree
................................................................................
<p>
Each SQLite database file begins with a 100-byte header. The header
file consists of a well known 16-byte sequence followed by a series of
1, 2 and 4 byte unsigned integers. All integers in the file header (as
well as the rest of the database file) are stored in big-endian format.
<p>
The well known 16-byte sequence that begins every SQLite database file
is:
<pre>
0x53 0x51 0x4c 0x69 0x74 0x65 0x20 0x66 0x6f 0x72 0x6d 0x61 0x74 0x20 0x33 0x00</pre>
<p>
Interpreted as UTF-8 encoded text, this byte sequence corresponds
to the string "SQLite format 3" followed by a nul-terminator byte.
................................................................................
The schema version. Each time the database schema is modified (by
creating or deleting a database table, index, trigger or view)
the value of the 32-bit unsigned integer stored in this field
is incremented.
[Tr]<td>44..47 <td>4<td>
<p style="margin-top:0">
Schema layer file-format. This value is similar to the
"read-version" and "write-version" fields at offsets 18 and 19
of the database file header. If SQLite encounters a database
with a schema layer file-format value greater than the file-format
that it understands (currently 4), then SQLite will refuse to
access the database.
<p>
Usually, this value is set to 1. However, if any of the following
file-format features are used, then the schema layer file-format
must be set to the corresponding value or greater:
<ol start=2 style="margin-bottom:0">
<li> Implicit NULL values at the end of table records
(see section <cite>table_btree_content</cite>).
<li> Implicit default (non-NULL) values at the end of table
records (see section <cite>table_btree_content</cite>).
<li> Descending indexes (see section
<cite>index_btree_compare_func</cite>) and Boolean values
in database records (see section <cite>record_format</cite>,
serial types 8 and 9).
</ol>
[Tr]<td>48..51 <td>4<td>
Default pager cache size. This field is used by SQLite to store
the recommended pager cache size to use for the database.
[Tr]<td>52..55 <td>4<td>
For auto-vacuum capable databases, the numerically largest
root-page number in the database. Since page 1 is always the
root-page of the schema table (section <cite>schema_table</cite>),
this value is always non-zero for auto-vacuum databases. For
non-auto-vacuum databases, this value is always zero.
[Tr]<td>56..59 <td>4<td>
(constant) Database text encoding. A value of 1 means all
text values are stored using UTF-8 encoding. 2 indicates
little-endian UTF-16 text. A value of 3 means that the database
................................................................................
<p>
The four byte block beginning at offset 28 is unused. As is the
32 byte block beginning at offset 68.
</p>
<p>
Some of the following requirements state that certain database header
fields must contain defined constant values, even though the sqlite
database file format is designed to allow various values. This is
done to artificially constrain the definition of a
<i>well-formed database</i> in order to make implementation and
testing more practical.
<p class=req id=H30030>
[fileformat_import_requirement H30030]
<p>
Following the 16 byte magic string in the file header is the
<i>page size</i>, a 2-byte field. See section
<cite>pages_and_page_types</cite> for details.
<p class=req id=H30040>
[fileformat_import_requirement H30040]
<p class=req id=H30050>
[fileformat_import_requirement H30050]
................................................................................
For now, it is sufficient to know that the B-Tree structure is a
data structure that stores a set of records. Each record is an
ordered set of SQL values (the format of which is described in
section <cite>record_format</cite>). Given the root page number of
the B-Tree structure (which is well known for the schema table), it
is possible to iterate through the set of records.
<p>
The schema table contains a record for each SQL table (including
virtual tables) except for sqlite_master, and for each index, trigger
and view in the logical database. There is also an entry for each
UNIQUE or PRIMARY KEY clause present in the definition of a database
table. Each record in the schema table contains exactly 5 values, in
the following order:
[Table]
[Tr]<th>Field<th>Description
[Tr]<td>Schema item type.
<td>A string value. One of "table", "index", "trigger" or "view",
according to the schema item type. Entries associated with
UNIQUE or PRIMARY KEY clauses have this field set to "index".
[Tr]<td>Schema item name.
<td>A string value. The name of the database schema item (table,
index, trigger or view) associated with this record, if any.
Entries associated with UNIQUE or PRIMARY KEY clauses have
this field set to a string of the form
"sqlite_autoindex_<name>_<idx>" where <name>
is the name of the SQL table and <idx> is an integer
value.
[Tr]<td style="white-space:nowrap">Associated table name.
<td>A string value. For "table"
or "view" records this is a copy of the second (previous) value.
For "index" and "trigger" records, this field is set to the name
of the associated database table.
[Tr]<td style="white-space:nowrap">The "root page" number.
<td>For "trigger" and "view" records, as well as "table" records
associated with virtual tables, this is set to NULL. For other
"table" and "index" records (including those associated with
UNIQUE or PRIMARY KEY clauses), this field contains the root
page number (an integer) of the B-Tree structure that contains
the table or index data.
[Tr]<td>The SQL statement.
<td>A string value. The SQL statement used to create the schema
item (i.e. the complete text of an SQL "CREATE TABLE"
statement). This field contains an empty string for table
entries associated with PRIMARY KEY or UNIQUE clauses.
<span class=todo>Refer to some document that describes these
SQL statements more precisely.</span>
</table>
<p>
Logical database schema items other than non-virtual tables and indexes
(including indexes created by UNIQUE or PRIMARY key constraints) do not
require any other data structures to be created within the database
file.
<p>
Tables and indexes on the other hand, are represented within the
database file by both an entry in the schema table and a B-Tree
structure stored elsewhere in the file. The specific B-Tree associated
with each database table or index is identified by its root page
number, which is stored in the 4th field of the schema table record.
In a non-auto-vacuum database, the B-Tree root pages may be stored
anywhere within the database file. For an auto-vacuum database, all
B-Tree root pages must at all times form a contiguous set starting
at page 3 of the database file, skipping any pages that are required to
be used as pointer-map pages (see section
<cite>pointer_map_pages</cite>).
<p>
As noted in section <cite>file_header</cite>, in an auto-vacuum
database the page number of the page immediately following the
final root page in the contiguous set of root pages is stored
as a 4 byte big-endian integer at byte offset 52 of the database
file header. Unless that page is itself a pointer-map page, in which
case the page number of the page following it is stored instead.
<p>
For example, if the schema of a logical database is created using
the following SQL statements:
<pre>
CREATE TABLE abc(a, b, c);
................................................................................
contiguous block. The page that contains the root node of a B-Tree
structure is known as the "root page".
<p>
SQLite uses two distinct variants of the B-Tree structure:
<ul>
<li>The <b>table B-Tree</b>, which uses 64-bit integer values for keys.
In a table B-Tree, an associated database record (section
<cite>record_format</cite>) is stored along with each entry. Table
B-Tree structures are described in detail in section
<cite>table_btrees</cite>.
<li>The <b>index B-Tree</b>, which uses database records as keys. Index
B-Tree structures are described in detail in section
<cite>index_btrees</cite>.
</ul>
................................................................................
<p class=req id=H30500>
[fileformat_import_requirement H30500]
<p class=req id=H30510>
[fileformat_import_requirement H30510]
[h3 "Variable Length Integer Format" "varint_format"]
<p>
In several parts of the B-Tree structure, 64-bit twos-complement signed
integer values are stored in the "variable length integer format"
described here.
<p>
A variable length integer consumes from one to nine bytes of space,
depending on the value stored. Seven bits are used from each of
the first eight bytes present, and, if present, all eight from
the final ninth byte. Unless the full nine byte format is used, the
serialized form consists of all bytes up to and including the first
byte with the 0x80 bit cleared.
<p>
The number of bytes present depends on the position of the most
significant set bit in the 64-bit word. Negative numbers always have
the most significant bit of the word (the sign bit) set and so are
always encoded using the full nine bytes. Positive integers may be
encoded using less space. The following table shows the 9 different
length formats available for storing a variable length integer
value.
[Table]
[Tr]<th>Bytes<th>Value Range<th>Bit Pattern
[Tr]<td>1<td>7 bit<td>0xxxxxxx
[Tr]<td>2<td>14 bit<td>1xxxxxxx 0xxxxxxx
[Tr]<td>3<td>21 bit<td>1xxxxxxx 1xxxxxxx 0xxxxxxx
[Tr]<td>4<td>28 bit<td>1xxxxxxx 1xxxxxxx 1xxxxxxx 0xxxxxxx
................................................................................
<p>
When using the full 9 byte representation, the first byte contains
the 7 most significant bits of the 64-bit value. The final byte of
the 9 byte representation contains the 8 least significant bits of
the 64-bit value. When using one of the other representations, the
final byte contains the 7 least significant bits of the 64-bit value.
The second last byte, if present, contains the 7 next least signficant
bits of the value, and so on. The significant bits of the 64-bit
value for which no storage is provided are assumed to be zero.
<p>
When encoding a variable length integer, SQLite usually selects the
most compact representation that provides enough storage to accomadate
the most significant set bit of the value. This is not required
however, using more bytes than is strictly necessary when encoding
an integer is valid.
[Table]
[Tr]<th>Decimal<th>Hexadecimal <th>Variable Length Integer
[Tr]<td>43 <td>0x000000000000002B <td>0x2B
[Tr]<td>200815 <td>0x000000000003106F <td>0x8C 0xA0 0x6F
[Tr]<td>-1 <td>0xFFFFFFFFFFFFFFFF
<td>0xFF 0xFF 0xFF 0xFF 0xFF 0xFF 0xFF 0xFF 0xFF
[Tr]<td>-78056 <td>0xFFFFFFFFFFFECD56
<td>0xFF 0xFF 0xFF 0xFF 0xFF 0xFF 0xFD 0xCD 0x56
</table>
<p class=req id=H30520>
................................................................................
<p class=req id=H30750>
[fileformat_import_requirement H30750]
<p class=req id=H30760>
[fileformat_import_requirement H30760]
<p>
The precise way in which index B-Tree pages and cells are formatted is
described in subsequent sections.
[h4 "Index B-Tree Content"]
<p>
The database file contains one index B-Tree for each database index
in the logical database, including those created by UNIQUE or
PRIMARY KEY clauses in table declarations. Each record stored in
an index B-Tree contains the same number of fields, the number of
indexed columns in the database index declaration plus one.
<p>
An index B-Tree contains an entry for each row in its associated
database table. The fields of the record used as the index B-Tree
key are copies of each of the indexed columns of the associated
database row, in order, followed by the rowid value of the same
................................................................................
<p class=req id=H30800>
[fileformat_import_requirement H30800]
[h4 "Record Sort Order" "index_btree_compare_func"]
<p>
This section defines the comparison function used when database
records are used as B-Tree keys for index B-Trees. The comparison
function is only defined when both database records contain the same
number of fields.
<p>
When comparing two database records, the first field of one
record is compared to the first field of the other. If they
are not equal, the next pair of fields are compared, and so
on. If all the fields in the database records are equal, then
the two records are considered equal. Otherwise, the result
................................................................................
<li>If one value is text and the other a blob, the text value
is considered lesser.
<li>If both values are blobs, memcmp() is used to determine the
results of the comparison function. If one blob is a prefix
of the other, the shorter blob is considered lesser.
</ol>
<p>
Each column of a database index may be declared as "descending".
<span class=todo>Link to document with CREATE INDEX syntax.</span>
In SQLite database files with a schema layer file-format equal
to 4, this modifies the order in which the records are stored in
the corresponding index B-Tree structure. For each index column
declared as descending, the results of the above comparison
procedure are inverted.
<p>
The columns of database indexes created by UNIQUE or PRIMARY
KEY clauses are never treated as descending.
<p class=todo>
Need requirements style statements for this information. Easier
to do once collation sequences have been defined somewhere.
................................................................................
<p class=req id=H30900>
[fileformat_import_requirement H30900]
<p class=req id=H30910>
[fileformat_import_requirement H30910]
<p>
The following requirements govern management of free-space within the
page content area (both table and index B-Tree pages).
<p class=req id=H30920>
[fileformat_import_requirement H30920]
<p class=req id=H30930>
[fileformat_import_requirement H30930]
................................................................................
by child page C(n+1) have values larger than that of I(n), for values
of n in the same range.
[Figure tabletree.gif figure_tabletree "Table B-Tree Tree Structure"]
<p>
Figure <cite>figure_tabletree</cite> depicts a table B-Tree containing
a contiguous set of 14 integer keys starting with 1. Each key <i>n</i>
has an associated database record R<i>n</i>. All the keys and their
associated records are stored in the leaf pages. The internal node
pages contain no database data, their only purpose is to provide
a way to navigate the tree structure.
<p class=req id=H31020>
[fileformat_import_requirement H31020]
<p class=req id=H31030>
[fileformat_import_requirement H31030]
................................................................................
<p class=req id=H31040>
[fileformat_import_requirement H31040]
<p class=req id=H31050>
[fileformat_import_requirement H31050]
<p>
The precise way in which table B-Tree pages and cells are formatted is
described in subsequent sections.
[h4 "Table B-Tree Content" table_btree_content]
<p>
The database file contains one table B-Tree for each database table
in the logical database. Although some data may be duplicated in
index B-Tree structures, the table B-Tree is the primary location
of table data.
<p>
The table B-Tree contains exactly one entry for each row in the
database table. The integer key value used for the B-Tree entry is
the value of the "rowid" field of the corresponding logical row
in the database table. The database row fields are stored in the
record associated with the table B-Tree entry, in the same order
as they appear in the logical database table. The first field in
the record (see section <cite>record_format</cite>) contains the
value of the leftmost field in the database row, and so on.
<p>
If a database table column is declared as an INTEGER PRIMARY KEY,
then it is an alias for the rowid field, which is stored as the
table B-Tree key value. Instead of duplicating the integer value
in the associated record, the record field associated with the
INTEGER PRIMARY KEY column is always set to an SQL NULL.
<p>
Finally, if the schema layer file-format is greater than or equal
to 2, some of the records stored in table B-Trees may contain
less fields than the associated logical database table does columns.
If the schema layer file-format is exactly 2, then the logical
database table column values associated with the "missing" fields
are SQL NULL. If the schema layer file-format is greater than
2, then the values associated with the "missing" fields are
determined by the default value of the associated database table
columns.
<span class=todo>Reference to CREATE TABLE syntax. How are default
values determined?</span>
<p class=req id=H31060>
[fileformat_import_requirement H31060]
<p class=req id=H31070>
[fileformat_import_requirement H31070]
................................................................................
<p class=req id=H31400>
[fileformat_import_requirement H31400]
<p class=req id=H31410>
[fileformat_import_requirement H31410]
<p class=req id=H31420>
[fileformat_import_requirement H31420]
[h1 "Database File-System Representation" file_system_usage]
<p>
SQLite is often described as a "single-file database system", implying
that the contents of a database are always stored in a single file
within the file-system. However, although it is the case that an
SQLite database image can be and usually is stored within a single
file, it is also true that an SQLite database image may be stored
using two or even three files, as follows:
<ul>
<li> A database file.
<li> Optionally, a journal file.
<li> Optionally, master-journal file may also be part of the file-system
representation of a database image. A master-journal file may only
be part of the representation if the journal file is present.
</ul>
[h2 "Journal File Formats"]
[h3 "Journal File Details" journal_file_format]
<p>
This section describes the format used by an SQLite <i>journal file</i>.
<p>
A journal file consists of one or more <i>journal headers</i>, zero
or more <i>journal records</i> and optionally a <i>master journal
................................................................................
second set of zero or more <i>journal records</i> and so on. There
is no limit to the number of <i>journal headers</i> a journal file
may contain. Following the <i>journal headers</i> and their accompanying
sets of <i>journal records</i> may be the optional <i>master journal
pointer</i>. Or, the file may simply end following the final <i>journal
record</i>.
[h4 "Journal Header Format" journal_header_format]
<p>
A <i>journal header</i> is <i>sector-size</i> bytes in size, where <i>
sector-size</i> is the value returned by the xSectorSize method of
the file handle opened on the database file. Only the first 28 bytes
of the <i>journal header</i> are used, the remainder may contain garbage
data. The first 28 bytes of each <i>journal header</i> consists of an
................................................................................
<p>
All <i>journal headers</i> are positioned in the file so that they
start at a <i>sector size</i> aligned offset. To achieve this, unused
space may be left between the start of the second and subsequent
<i>journal headers</i> and the end of the <i>journal records</i>
associated with the previous header.
[h4 "Journal Record Format" journal_record_format]
<p>
Each <i>journal record</i> contains the original data for a database page
modified by the <i>write transaction</i>. If a rollback is required, then
this data may be used to restore the contents of the database page to the
state it was in before the <i>write transaction</i> was started.
................................................................................
<p>
The set of <i>journal records</i> that follow a <i>journal header</i>
in a <i>journal file</i> are packed tightly together. There are no
alignment requirements for <i>journal records</i> as there are for
<i>journal headers</i>.
[h4 "Master Journal Pointer"]
<p>
To support <i>atomic</i> transactions that modify more than one
database file, SQLite sometimes includes a <i>master journal pointer</i>
record in a <i>journal file</i>. A <i>master journal pointer</i>
contains the name of a <i>master journal-file</i> along with a
check-sum and some well-known values that allow the
................................................................................
Finally, the <b>journal magic</b> field always contains a
well-known 8-byte string value; the same value stored in the
first 8 bytes of a <i>journal header</i>. The well-known
sequence of bytes is:
<pre>0xd9 0xd5 0x05 0xf9 0x20 0xa1 0x63 0xd7</pre>
</table>
[h3 "Master-Journal File Details" masterjournal_file_format]
[h2 "Reading an SQLite Database" reading_from_files]
[h2 "Writing to an SQLite Database" writing_to_files]
<p>
When an SQLite user commits a transaction that modifies the contents
of the database, the database representation on disk must be modified
to reflect the new contents of the database. SQLite is required to do
make all modifications associated with the transaction such that the
logical contents of the database file is modified atomically. If an
application, operating system (OS) or power failure occurs while SQLite
is updating the database, upon recovery the contents of the database
must reflect either that all modifications associated with the
database transaction were successfully applied, or that none of the
modifications were applied and the contents of the database are as
they were before the failed attempt to modify the database.
<p>
Some operations on a file-system may be considered atomic. For example
deleting a file, or on some systems writing to a single disk sector.
However, in general there exists no atomic file-system operation
that may be used to update an SQLite database file with the effects
of an arbitrary database transaction, which may remove, modify or
add multiple database rows, tables or indexes. Therefore, a two stage
approach to writing an SQLite database (or indeed, modifying the logical
contents of any on-disk database) is required:
<ol>
<li> The file-system representation of the database is manipulated to
a state where a single atomic operation may be used to transform
the logical contents of the database from its initial state to
the required final state.
<li> The required atomic operation is applied.
</ol>
<p>
Step 1 of the above must be accomplished such that all interim states
of the file-system correspond to the logical contents of the database
as they were before the procedure began. This way, if an application,
OS or power failure occurs during step 1, upon recovery the database
contents remain unchanged. It is not possible for such a failure to
occur "during" step 2, as step 2 consists of a single atomic operation.
<p>
SQLite is also required to support atomic database transactions that
involve updates to multiple database files. If an application, OS or
power failure occurs while committing the transaction, it is required
that following recovery either the logical contents of all affected
databases have been completely updated, or that the contents of each
of them remains unchanged. Whether or not a transaction involves
multiple database files, the principle remains the same: the file-system
must be manipulated into a state whereby a single atomic file-system
operation can be used to effect all required changes to the logical
database contents.
<p>
The following two sub-sections describe the specific ways in which
SQLite achieves this for single and multiple database transactions.
[h3 "Single Database Transactions" single_db_transactions]
[h3 "Multiple Database Transactions" multi_db_transactions]
[h1 References]
<table id="refs" style="width:auto; margin: 1em 5ex">
<tr><td style="width:5ex" id="ref_comer_btree">\[1\]<td>
Douglas Comer, <u>Ubiquitous B-Tree</u>, ACM Computing Surveys (CSUR),
v.11 n.2, pages 121-137, June 1979.
|