Documentation Source Text

Check-in [8b1e897acb]
Login

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: 8b1e897acb696848f568469f647fc7f0a051a265
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
Hide Diffs Unified Diffs Ignore Whitespace Patch

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_&lt;name&gt;_&lt;idx&gt;" where &lt;name&gt;
		is the name of the SQL table and &lt;idx&gt; 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_&lt;name&gt;_&lt;idx&gt;" where &lt;name&gt;
                is the name of the SQL table and &lt;idx&gt; 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.