*** DRAFT ***

SQLite Requirement Matrix Details
fileformat2.html

Index Summary Markup Original


R-25008-21688-65381-07984-00031-51738-33385-29636 tcl slt th3 src

The size of a page is a power of two between 512 and 65536 inclusive.

src/btree.c:3043

/* IMP: R-25008-21688 */
# EVIDENCE-OF: R-25008-21688 The size of a page is a power of two
# between 512 and 65536 inclusive.

R-51873-39618-36887-07740-29910-44227-64994-45614 tcl slt th3 src

The page size for a database file is determined by the 2-byte integer located at an offset of 16 bytes from the beginning of the database file.

src/btree.c:2395   src/btree.c:3039

/* IMP: R-51873-39618 */
# EVIDENCE-OF: R-51873-39618 The page size for a database file is
# determined by the 2-byte integer located at an offset of 16 bytes from
# the beginning of the database file.

R-25654-38196-54382-29664-57733-52949-37784-40820 tcl slt th3 src

All reads from and writes to the main database file begin at a page boundary and all writes are an integer number of pages in size.

/* IMP: R-25654-38196 */
# EVIDENCE-OF: R-25654-38196 All reads from and writes to the main
# database file begin at a page boundary and all writes are an integer
# number of pages in size.

R-27484-38990-39416-37657-02655-32272-61477-41469 tcl slt th3 src

Reads are also usually an integer number of pages in size, with the one exception that when the database is first opened, the first 100 bytes of the database file (the database file header) are read as a sub-page size unit.

/* IMP: R-27484-38990 */
# EVIDENCE-OF: R-27484-38990 Reads are also usually an integer number of
# pages in size, with the one exception that when the database is first
# opened, the first 100 bytes of the database file (the database file
# header) are read as a sub-page size unit.

R-01379-47306-55874-35809-24668-39985-05961-47226 tcl slt th3 src

Before any information-bearing page of the database is modified, the original unmodified content of that page is written into the rollback journal.

/* IMP: R-01379-47306 */
# EVIDENCE-OF: R-01379-47306 Before any information-bearing page of the
# database is modified, the original unmodified content of that page is
# written into the rollback journal.

R-12168-36613-28272-64664-28588-05948-62910-42845 tcl slt th3 src

Freelist leaf pages bear no information that would need to be restored on a rollback and so they are not written to the journal prior to modification, in order to reduce disk I/O.

/* IMP: R-12168-36613 */
# EVIDENCE-OF: R-12168-36613 Freelist leaf pages bear no information
# that would need to be restored on a rollback and so they are not
# written to the journal prior to modification, in order to reduce disk
# I/O.

R-43737-39999-60785-41458-46023-21259-00950-21702 tcl slt th3 src

Every valid SQLite database file begins with the following 16 bytes (in hex): 53 51 4c 69 74 65 20 66 6f 72 6d 61 74 20 33 00.

src/btree.c:2982

/* IMP: R-43737-39999 */
# EVIDENCE-OF: R-43737-39999 Every valid SQLite database file begins
# with the following 16 bytes (in hex): 53 51 4c 69 74 65 20 66 6f 72 6d
# 61 74 20 33 00.

R-59310-51205-15407-41555-00888-38389-12336-63259 tcl slt th3 src

The "reserved space" size in the 1-byte integer at offset 20 is the number of bytes of space at the end of each page to reserve for extensions.

src/btree.c:3052

/* IMP: R-59310-51205 */
# EVIDENCE-OF: R-59310-51205 The "reserved space" size in the 1-byte
# integer at offset 20 is the number of bytes of space at the end of
# each page to reserve for extensions.

R-28312-64704-30092-58113-01356-46939-35653-28661 tcl slt th3 src

However, the usable size is not allowed to be less than 480. In other words, if the page size is 512, then the reserved space size cannot exceed 32.

src/btree.c:3079

/* IMP: R-28312-64704 */
# EVIDENCE-OF: R-28312-64704 However, the usable size is not allowed to
# be less than 480. In other words, if the page size is 512, then the
# reserved space size cannot exceed 32.

R-15465-20813-03488-35934-00721-06683-61343-58043 tcl slt th3 src

The maximum and minimum embedded payload fractions and the leaf payload fraction values must be 64, 32, and 32.

src/btree.c:3030

/* IMP: R-15465-20813 */
# EVIDENCE-OF: R-15465-20813 The maximum and minimum embedded payload
# fractions and the leaf payload fraction values must be 64, 32, and 32.

R-13680-39071-55665-32597-06847-40698-32535-24028 tcl slt th3 src

The file change counter is a 4-byte big-endian integer at offset 24 that is incremented whenever the database file is unlocked after having been modified.

/* IMP: R-13680-39071 */
# EVIDENCE-OF: R-13680-39071 The file change counter is a 4-byte
# big-endian integer at offset 24 that is incremented whenever the
# database file is unlocked after having been modified.

R-11345-24891-48483-09798-42153-35485-11277-64815 tcl slt th3 src

The 4-byte big-endian integer at offset 28 into the header stores the size of the database file in pages.

/* IMP: R-11345-24891 */
# EVIDENCE-OF: R-11345-24891 The 4-byte big-endian integer at offset 28
# into the header stores the size of the database file in pages.

R-19194-50107-23350-24777-39179-56094-65469-19806 tcl slt th3 src

If this in-header datasize size is not valid (see the next paragraph), then the database size is computed by looking at the actual size of the database file.

/* IMP: R-19194-50107 */
# EVIDENCE-OF: R-19194-50107 If this in-header datasize size is not
# valid (see the next paragraph), then the database size is computed by
# looking at the actual size of the database file.

R-13591-51789-59523-18205-42201-39107-10748-36836 tcl slt th3 src

Newer versions of SQLite use the in-header database size if it is available but fall back to the actual file size if the in-header database size is not valid.

/* IMP: R-13591-51789 */
# EVIDENCE-OF: R-13591-51789 Newer versions of SQLite use the in-header
# database size if it is available but fall back to the actual file size
# if the in-header database size is not valid.

R-18665-26680-08407-38690-15882-41528-44716-07525 tcl slt th3 src

The in-header database size is only considered to be valid if it is non-zero and if the 4-byte change counter at offset 24 exactly matches the 4-byte version-valid-for number at offset 92.

/* IMP: R-18665-26680 */
# EVIDENCE-OF: R-18665-26680 The in-header database size is only
# considered to be valid if it is non-zero and if the 4-byte change
# counter at offset 24 exactly matches the 4-byte version-valid-for
# number at offset 92.

R-58769-41706-35801-04154-17384-12039-24513-64456 tcl slt th3 src

The in-header database size is always valid when the database is only modified using recent versions of SQLite, versions 3.7.0 (2010-07-21) and later.

/* IMP: R-58769-41706 */
# EVIDENCE-OF: R-58769-41706 The in-header database size is always valid
# when the database is only modified using recent versions of SQLite,
# versions 3.7.0 (2010-07-21) and later.

R-59841-13798-58267-43160-49390-53360-57337-63082 tcl slt th3 src

The 4-byte big-endian integer at offset 32 stores the page number of the first page of the freelist, or zero if the freelist is empty.

src/btree.c:5766

/* IMP: R-59841-13798 */
# EVIDENCE-OF: R-59841-13798 The 4-byte big-endian integer at offset 32
# stores the page number of the first page of the freelist, or zero if
# the freelist is empty.

R-05119-02637-14414-33683-07351-22342-03629-61768 tcl slt th3 src

The 4-byte big-endian integer at offset 36 stores stores the total number of pages on the freelist.

src/btree.c:5712   th3/req1/pragma21.test:27

/* IMP: R-05119-02637 */
# EVIDENCE-OF: R-05119-02637 The 4-byte big-endian integer at offset 36
# stores stores the total number of pages on the freelist.

R-43823-35025-00176-30164-46726-64409-48434-13396 tcl slt th3 src

The schema cookie is a 4-byte big-endian integer at offset 40 that is incremented whenever the database schema changes.

/* IMP: R-43823-35025 */
# EVIDENCE-OF: R-43823-35025 The schema cookie is a 4-byte big-endian
# integer at offset 40 that is incremented whenever the database schema
# changes.

R-34696-31731-45418-63028-04604-07803-00716-15058 tcl slt th3 src

When a prepared statement runs, it first checks the schema cookie to ensure the value is the same as when the statement was prepared and if the schema cookie has changed, the statement either automatically reprepares and reruns or it aborts with an SQLITE_SCHEMA error.

/* IMP: R-34696-31731 */
# EVIDENCE-OF: R-34696-31731 When a prepared statement runs, it first
# checks the schema cookie to ensure the value is the same as when the
# statement was prepared and if the schema cookie has changed, the
# statement either automatically reprepares and reruns or it aborts with
# an SQLITE_SCHEMA error.

R-20339-64003-24275-14776-38996-63966-02497-20063 tcl slt th3 src

Format 4 causes SQLite to respect the DESC keyword on index declarations.

th3/req1/fileformat01.test:39

/* IMP: R-20339-64003 */
# EVIDENCE-OF: R-20339-64003 Format 4 causes SQLite to respect the DESC
# keyword on index declarations.

R-34789-59934-28389-39071-40559-33528-38029-45906 tcl slt th3 src

The DESC keyword is ignored in indexes for formats 1, 2, and 3.

th3/req1/fileformat01.test:36

/* IMP: R-34789-59934 */
# EVIDENCE-OF: R-34789-59934 The DESC keyword is ignored in indexes for
# formats 1, 2, and 3.

R-35214-06564-22940-62614-38557-57045-54055-01727 tcl slt th3 src

Format 4 also adds two new boolean record type values (serial types 8 and 9).

th3/req1/fileformat01.test:95

/* IMP: R-35214-06564 */
# EVIDENCE-OF: R-35214-06564 Format 4 also adds two new boolean record
# type values (serial types 8 and 9).

R-63119-53774-25938-08953-42086-26261-27933-12483 tcl slt th3 src

New database files created by SQLite use format 4 by default.

th3/req1/fileformat01.test:11

/* IMP: R-63119-53774 */
# EVIDENCE-OF: R-63119-53774 New database files created by SQLite use
# format 4 by default.

R-34264-15842-51908-27355-58812-13180-15279-38051 tcl slt th3 src

The legacy_file_format pragma can be used to cause SQLite to create new database files using format 1.

th3/req1/fileformat01.test:23

/* IMP: R-34264-15842 */
# EVIDENCE-OF: R-34264-15842 The legacy_file_format pragma can be used
# to cause SQLite to create new database files using format 1.

R-51370-13632-41258-48157-25813-10718-36175-44493 tcl slt th3 src

If the integer at offset 52 is zero then pointer-map (ptrmap) pages are omitted from the database file and neither auto_vacuum nor incremental_vacuum are supported.

/* IMP: R-51370-13632 */
# EVIDENCE-OF: R-51370-13632 If the integer at offset 52 is zero then
# pointer-map (ptrmap) pages are omitted from the database file and
# neither auto_vacuum nor incremental_vacuum are supported.

R-42657-00400-11552-47738-28049-57819-30867-53665 tcl slt th3 src

If the integer at offset 52 is non-zero then it is the page number of the largest root page in the database file, the database file will contain ptrmap pages, and the mode must be either auto_vacuum or incremental_vacuum.

/* IMP: R-42657-00400 */
# EVIDENCE-OF: R-42657-00400 If the integer at offset 52 is non-zero
# then it is the page number of the largest root page in the database
# file, the database file will contain ptrmap pages, and the mode must
# be either auto_vacuum or incremental_vacuum.

R-26117-01852-15491-47779-25813-50210-15791-48499 tcl slt th3 src

In this latter case, the integer at offset 64 is true for incremental_vacuum and false for auto_vacuum.

/* IMP: R-26117-01852 */
# EVIDENCE-OF: R-26117-01852 In this latter case, the integer at offset
# 64 is true for incremental_vacuum and false for auto_vacuum.

R-44424-47897-20192-49943-44571-25334-57312-32558 tcl slt th3 src

If the integer at offset 52 is zero then the integer at offset 64 must also be zero.

/* IMP: R-44424-47897 */
# EVIDENCE-OF: R-44424-47897 If the integer at offset 52 is zero then
# the integer at offset 64 must also be zero.

R-11566-63657-14865-04704-35153-27057-50201-14742 tcl slt th3 src

The 4-byte big-endian integer at offset 56 determines the encoding used for all text strings stored in the database.

th3/req1/fileformat03.test:45

/* IMP: R-11566-63657 */
# EVIDENCE-OF: R-11566-63657 The 4-byte big-endian integer at offset 56
# determines the encoding used for all text strings stored in the
# database.

R-37514-35566-00640-25084-57688-04377-16048-30214 tcl slt th3 src

A value of 1 means UTF-8.

th3/req1/fileformat03.test:49

/* IMP: R-37514-35566 */
# EVIDENCE-OF: R-37514-35566 A value of 1 means UTF-8.

R-03371-37637-45197-41040-02325-30195-01599-52807 tcl slt th3 src

A value of 2 means UTF-16le.

th3/req1/fileformat03.test:65

/* IMP: R-03371-37637 */
# EVIDENCE-OF: R-03371-37637 A value of 2 means UTF-16le.

R-51971-34154-03806-52008-63597-27653-22801-35864 tcl slt th3 src

A value of 3 means UTF-16be.

th3/req1/fileformat03.test:79

/* IMP: R-51971-34154 */
# EVIDENCE-OF: R-51971-34154 A value of 3 means UTF-16be.

R-03587-06098-24294-21557-27164-26781-18390-63915 tcl slt th3 src

The sqlite3.h header file defines C-preprocessor macros SQLITE_UTF8 as 1, SQLITE_UTF16LE as 2, and SQLITE_UTF16BE as 3, to use in place of the numeric codes for the text encoding.

th3/cov1/main01.test:41

/* IMP: R-03587-06098 */
# EVIDENCE-OF: R-03587-06098 The sqlite3.h header file defines
# C-preprocessor macros SQLITE_UTF8 as 1, SQLITE_UTF16LE as 2, and
# SQLITE_UTF16BE as 3, to use in place of the numeric codes for the text
# encoding.

R-53994-11873-49794-19921-48320-57211-64823-29959 tcl slt th3 src

The 4-byte big-endian integer at offset 60 is the user version which is set and queried by the user_version pragma.

th3/req1/fileformat03.test:11

/* IMP: R-53994-11873 */
# EVIDENCE-OF: R-53994-11873 The 4-byte big-endian integer at offset 60
# is the user version which is set and queried by the user_version
# pragma.

R-43172-64399-63789-05300-15599-35833-47014-02400 tcl slt th3 src

The 4-byte big-endian integer at offset 68 is an "Application ID" that can be set by the PRAGMA application_id command in order to identify the database as belonging to or associated with a particular application.

th3/req1/fileformat03.test:28

/* IMP: R-43172-64399 */
# EVIDENCE-OF: R-43172-64399 The 4-byte big-endian integer at offset 68
# is an "Application ID" that can be set by the PRAGMA application_id
# command in order to identify the database as belonging to or
# associated with a particular application.

R-48666-10705-60913-59085-01718-56024-53514-10016 tcl slt th3 src

The 4-byte big-endian integer at offset 96 stores the SQLITE_VERSION_NUMBER value for the SQLite library that most recently modified the database file.

/* IMP: R-48666-10705 */
# EVIDENCE-OF: R-48666-10705 The 4-byte big-endian integer at offset 96
# stores the SQLITE_VERSION_NUMBER value for the SQLite library that
# most recently modified the database file.

R-07865-58192-09859-42993-29277-46278-59857-06081 tcl slt th3 src

The 4-byte big-endian integer at offset 92 is the value of the change counter when the version number was stored.

/* IMP: R-07865-58192 */
# EVIDENCE-OF: R-07865-58192 The 4-byte big-endian integer at offset 92
# is the value of the change counter when the version number was stored.

R-15356-65223-28934-39981-61264-10821-14188-49484 tcl slt th3 src

SQLite does not use the lock-byte page.

/* IMP: R-15356-65223 */
# EVIDENCE-OF: R-15356-65223 SQLite does not use the lock-byte page.

R-24334-46836-37699-41209-48952-15160-39885-36508 tcl slt th3 src

The SQLite core will never read or write the lock-byte page, though operating-system specific VFS implementations may choose to read or write bytes on the lock-byte page according to the needs and proclivities of the underlying system.

/* IMP: R-24334-46836 */
# EVIDENCE-OF: R-24334-46836 The SQLite core will never read or write
# the lock-byte page, though operating-system specific VFS
# implementations may choose to read or write bytes on the lock-byte
# page according to the needs and proclivities of the underlying system.

R-01506-11053-32502-45139-47336-58914-04836-35356 tcl slt th3 src

The first integer on a freelist trunk page is the page number of the next freelist trunk page in the list or zero if this is the last freelist trunk page.

src/btree.c:5761

/* IMP: R-01506-11053 */
# EVIDENCE-OF: R-01506-11053 The first integer on a freelist trunk page
# is the page number of the next freelist trunk page in the list or zero
# if this is the last freelist trunk page.

R-13523-04394-10297-34543-39920-38715-25686-62539 tcl slt th3 src

The second integer on a freelist trunk page is the number of leaf page pointers to follow.

src/btree.c:5783

/* IMP: R-13523-04394 */
# EVIDENCE-OF: R-13523-04394 The second integer on a freelist trunk page
# is the number of leaf page pointers to follow.

R-23313-12651-48093-62761-39865-28601-55555-42424 tcl slt th3 src

Call the second integer on a freelist trunk page L. If L is greater than zero then integers with array indexes between 2 and L+1 inclusive contain page numbers for freelist leaf pages.

/* IMP: R-23313-12651 */
# EVIDENCE-OF: R-23313-12651 Call the second integer on a freelist trunk
# page L. If L is greater than zero then integers with array indexes
# between 2 and L+1 inclusive contain page numbers for freelist leaf
# pages.

R-43475-58499-15568-06638-27533-08364-56193-27126 tcl slt th3 src

SQLite avoids reading or writing freelist leaf pages in order to reduce disk I/O.

/* IMP: R-43475-58499 */
# EVIDENCE-OF: R-43475-58499 SQLite avoids reading or writing freelist
# leaf pages in order to reduce disk I/O.

R-19920-11576-34548-21403-56992-25102-09000-41133 tcl slt th3 src

However, newer versions of SQLite still avoid using the last six entries in the freelist trunk page array in order that database files created by newer versions of SQLite can be read by older versions of SQLite.

src/btree.c:6098

/* IMP: R-19920-11576 */
# EVIDENCE-OF: R-19920-11576 However, newer versions of SQLite still
# avoid using the last six entries in the freelist trunk page array in
# order that database files created by newer versions of SQLite can be
# read by older versions of SQLite.

R-63048-43246-17135-38740-38774-19406-60211-40776 tcl slt th3 src

The number of freelist pages is stored as a 4-byte big-endian integer in the database header at an offset of 36 from the beginning of the file.

/* IMP: R-63048-43246 */
# EVIDENCE-OF: R-63048-43246 The number of freelist pages is stored as a
# 4-byte big-endian integer in the database header at an offset of 36
# from the beginning of the file.

R-43340-55464-08586-38170-34053-21521-23708-51540 tcl slt th3 src

The database header also stores the page number of the first freelist trunk page as a 4-byte big-endian integer at an offset of 32 from the beginning of the file.

/* IMP: R-43340-55464 */
# EVIDENCE-OF: R-43340-55464 The database header also stores the page
# number of the first freelist trunk page as a 4-byte big-endian integer
# at an offset of 32 from the beginning of the file.

R-51188-10511-53486-13467-33183-09537-36166-58492 tcl slt th3 src

In a well-formed database, all children of an interior b-tree have the same depth.

/* IMP: R-51188-10511 */
# EVIDENCE-OF: R-51188-10511 In a well-formed database, all children of
# an interior b-tree have the same depth.

R-29740-29379-09735-48719-34134-53325-08131-24362 tcl slt th3 src

For any key X, pointers to the left of a X refer to b-tree pages on which all keys are less than or equal to X.

/* IMP: R-29740-29379 */
# EVIDENCE-OF: R-29740-29379 For any key X, pointers to the left of a X
# refer to b-tree pages on which all keys are less than or equal to X.

R-17746-39108-25168-47884-07203-13722-37711-19749 tcl slt th3 src

Pointers to the right of X refer to pages where all keys are greater than X.

/* IMP: R-17746-39108 */
# EVIDENCE-OF: R-17746-39108 Pointers to the right of X refer to pages
# where all keys are greater than X.

R-37497-42412-17450-21022-09662-57031-07761-02196 tcl slt th3 src

The size of the reserved region is determined by the one-byte unsigned integer found at an offset of 20 into the database file header.

src/btree.c:2416   src/btree.c:3056

/* IMP: R-37497-42412 */
# EVIDENCE-OF: R-37497-42412 The size of the reserved region is
# determined by the one-byte unsigned integer found at an offset of 20
# into the database file header.

R-28594-02890-08451-24102-07094-48300-54751-43989 tcl slt th3 src

The one-byte flag at offset 0 indicating the b-tree page type.

src/btree.c:1826

/* IMP: R-28594-02890 */
# EVIDENCE-OF: R-28594-02890 The one-byte flag at offset 0 indicating
# the b-tree page type.

R-43316-37308-43115-39313-41984-23156-09755-42929 tcl slt th3 src

A value of 2 (0x02) means the page is an interior index b-tree page.

src/btree.c:1774

/* IMP: R-43316-37308 */
# EVIDENCE-OF: R-43316-37308 A value of 2 (0x02) means the page is an
# interior index b-tree page.

R-07291-35328-43800-36980-63575-06657-13005-40237 tcl slt th3 src

A value of 5 (0x05) means the page is an interior table b-tree page.

src/btree.c:1756

/* IMP: R-07291-35328 */
# EVIDENCE-OF: R-07291-35328 A value of 5 (0x05) means the page is an
# interior table b-tree page.

R-59615-42828-32025-62941-63094-03389-26549-04394 tcl slt th3 src

A value of 10 (0x0a) means the page is a leaf index b-tree page.

src/btree.c:1777

/* IMP: R-59615-42828 */
# EVIDENCE-OF: R-59615-42828 A value of 10 (0x0a) means the page is a
# leaf index b-tree page.

R-26900-09176-20658-03305-09602-45574-02965-13099 tcl slt th3 src

A value of 13 (0x0d) means the page is a leaf table b-tree page.

src/btree.c:1759

/* IMP: R-26900-09176 */
# EVIDENCE-OF: R-26900-09176 A value of 13 (0x0d) means the page is a
# leaf table b-tree page.

R-47608-56469-23173-27343-55331-44027-52555-65470 tcl slt th3 src

Any other value for the b-tree page type is an error.

src/btree.c:1786

/* IMP: R-47608-56469 */
# EVIDENCE-OF: R-47608-56469 Any other value for the b-tree page type is
# an error.

R-23588-34450-24473-59625-33708-24256-58306-00821 tcl slt th3 src

The two-byte integer at offset 1 gives the start of the first freeblock on the page, or is zero if there are no freeblocks.

src/btree.c:1888

/* IMP: R-23588-34450 */
# EVIDENCE-OF: R-23588-34450 The two-byte integer at offset 1 gives the
# start of the first freeblock on the page, or is zero if there are no
# freeblocks.

R-37002-32774-01005-00409-47681-36421-50128-30923 tcl slt th3 src

The two-byte integer at offset 3 gives the number of cells on the page.

src/btree.c:1843   src/btree.c:9343

/* IMP: R-37002-32774 */
# EVIDENCE-OF: R-37002-32774 The two-byte integer at offset 3 gives the
# number of cells on the page.

R-58015-48175-59968-51702-38590-12098-63778-58558 tcl slt th3 src

The two-byte integer at offset 5 designates the start of the cell content area. A zero value for this integer is interpreted as 65536.

src/btree.c:1839

/* IMP: R-58015-48175 */
# EVIDENCE-OF: R-58015-48175 The two-byte integer at offset 5 designates
# the start of the cell content area. A zero value for this integer is
# interpreted as 65536.

R-07161-27322-25839-32043-58017-64022-31100-22152 tcl slt th3 src

The one-byte integer at offset 7 gives the number of fragmented free bytes within the cell content area.

src/btree.c:9510

/* IMP: R-07161-27322 */
# EVIDENCE-OF: R-07161-27322 The one-byte integer at offset 7 gives the
# number of fragmented free bytes within the cell content area.

R-48313-35799-57609-24734-64937-33072-43085-04424 tcl slt th3 src

The four-byte page number at offset 8 is the right-most pointer. This value appears in the header of interior b-tree pages only and is omitted from all other pages.

/* IMP: R-48313-35799 */
# EVIDENCE-OF: R-48313-35799 The four-byte page number at offset 8 is
# the right-most pointer. This value appears in the header of interior
# b-tree pages only and is omitted from all other pages.

R-23882-45353-30604-47614-45882-29623-34325-59956 tcl slt th3 src

The cell pointer array of a b-tree page immediately follows the b-tree page header.

src/btree.c:9348

/* IMP: R-23882-45353 */
# EVIDENCE-OF: R-23882-45353 The cell pointer array of a b-tree page
# immediately follows the b-tree page header.

R-02776-14802-36986-43055-33540-06058-61102-35501 tcl slt th3 src

The cell pointer array consists of K 2-byte integer offsets to the cell contents.

src/btree.c:9372

/* IMP: R-02776-14802 */
# EVIDENCE-OF: R-02776-14802 The cell pointer array consists of K 2-byte
# integer offsets to the cell contents.

R-54737-49034-36463-44594-64965-25751-29163-41666 tcl slt th3 src

The cell pointers are arranged in key order with left-most cell (the cell with the smallest key) first and the right-most cell (the cell with the largest key) last.

/* IMP: R-54737-49034 */
# EVIDENCE-OF: R-54737-49034 The cell pointers are arranged in key order
# with left-most cell (the cell with the smallest key) first and the
# right-most cell (the cell with the largest key) last.

R-24089-57979-14928-03351-49983-36558-42407-47450 tcl slt th3 src

If a page contains no cells (which is only possible for a root page of a table that contains no rows) then the offset to the cell content area will equal the page size minus the bytes of reserved space.

src/btree.c:1851

/* IMP: R-24089-57979 */
# EVIDENCE-OF: R-24089-57979 If a page contains no cells (which is only
# possible for a root page of a table that contains no rows) then the
# offset to the cell content area will equal the page size minus the
# bytes of reserved space.

R-29356-02391-20787-31753-20232-32348-21090-16703 tcl slt th3 src

If the database uses a 65536-byte page size and the reserved space is zero (the usual value for reserved space) then the cell content offset of an empty page wants to be 65536. However, that integer is too large to be stored in a 2-byte unsigned integer, so a value of 0 is used in its place.

src/btree.c:1567

/* IMP: R-29356-02391 */
# EVIDENCE-OF: R-29356-02391 If the database uses a 65536-byte page size
# and the reserved space is zero (the usual value for reserved space)
# then the cell content offset of an empty page wants to be 65536.
# However, that integer is too large to be stored in a 2-byte unsigned
# integer, so a value of 0 is used in its place.

R-58208-19414-39381-39843-31272-22359-33296-08793 tcl slt th3 src

The first 2 bytes of a freeblock are a big-endian integer which is the offset in the b-tree page of the next freeblock in the chain, or zero if the freeblock is the last on the chain.

src/btree.c:9471

/* IMP: R-58208-19414 */
# EVIDENCE-OF: R-58208-19414 The first 2 bytes of a freeblock are a
# big-endian integer which is the offset in the b-tree page of the next
# freeblock in the chain, or zero if the freeblock is the last on the
# chain.

R-22710-53328-59796-08773-22880-08291-09347-39531 tcl slt th3 src

The third and fourth bytes of each freeblock form a big-endian integer which is the size of the freeblock in bytes, including the 4-byte header.

src/btree.c:1499

/* IMP: R-22710-53328 */
# EVIDENCE-OF: R-22710-53328 The third and fourth bytes of each
# freeblock form a big-endian integer which is the size of the freeblock
# in bytes, including the 4-byte header.

R-06866-39125-55055-41490-42674-60526-11809-05930 tcl slt th3 src

Freeblocks are always connected in order of increasing offset.

src/btree.c:9476

/* IMP: R-06866-39125 */
# EVIDENCE-OF: R-06866-39125 Freeblocks are always connected in order of
# increasing offset.

R-20690-50594-54745-63542-12245-05150-52761-00851 tcl slt th3 src

The second field of the b-tree page header is the offset of the first freeblock, or zero if there are no freeblocks on the page.

src/btree.c:9460

/* IMP: R-20690-50594 */
# EVIDENCE-OF: R-20690-50594 The second field of the b-tree page header
# is the offset of the first freeblock, or zero if there are no
# freeblocks on the page.

R-55530-52930-45065-40235-10917-28940-04210-09538 tcl slt th3 src

In a well-formed b-tree page, there will always be at least one cell before the first freeblock.

src/btree.c:1896

/* IMP: R-55530-52930 */
# EVIDENCE-OF: R-55530-52930 In a well-formed b-tree page, there will
# always be at least one cell before the first freeblock.

R-43263-13491-37815-28347-01820-41692-00359-07852 tcl slt th3 src

The total number of bytes in all fragments is stored in the fifth field of the b-tree page header.

src/btree.c:9508

/* IMP: R-43263-13491 */
# EVIDENCE-OF: R-43263-13491 The total number of bytes in all fragments
# is stored in the fifth field of the b-tree page header.

R-11498-58022-52113-15294-58649-22171-38649-39142 tcl slt th3 src

In a well-formed b-tree page, the total number of bytes in fragments may not exceed 60.

src/btree.c:1510

/* IMP: R-11498-58022 */
# EVIDENCE-OF: R-11498-58022 In a well-formed b-tree page, the total
# number of bytes in fragments may not exceed 60.

R-44582-60138-19271-50465-03257-36624-40089-31260 tcl slt th3 src

SQLite may from time to time reorganize a b-tree page so that there are no freeblocks or fragment bytes, all unused bytes are contained in the unallocated space region, and all cells are packed tightly at the end of the page.

src/btree.c:1342

/* IMP: R-44582-60138 */
# EVIDENCE-OF: R-44582-60138 SQLite may from time to time reorganize a
# b-tree page so that there are no freeblocks or fragment bytes, all
# unused bytes are contained in the unallocated space region, and all
# cells are packed tightly at the end of the page.

R-34809-04839-09181-10394-33851-21757-50200-61577 tcl slt th3 src

Let X be U-35. If the payload size P is less than or equal to X then the entire payload is stored on the b-tree leaf page.

/* IMP: R-34809-04839 */
# EVIDENCE-OF: R-34809-04839 Let X be U-35. If the payload size P is
# less than or equal to X then the entire payload is stored on the
# b-tree leaf page.

R-19878-23484-53576-16359-03133-43197-12770-47752 tcl slt th3 src

Let M be ((U-12)*32/255)-23 and let K be M+((P-M)%(U-4)). If P is greater than X then the number of bytes stored on the table b-tree leaf page is K if K is less or equal to X or M otherwise.

/* IMP: R-19878-23484 */
# EVIDENCE-OF: R-19878-23484 Let M be ((U-12)*32/255)-23 and let K be
# M+((P-M)%(U-4)). If P is greater than X then the number of bytes
# stored on the table b-tree leaf page is K if K is less or equal to X
# or M otherwise.

R-22771-48210-15439-13040-38130-20286-36509-06208 tcl slt th3 src

The number of bytes stored on the leaf page is never less than M.

/* IMP: R-22771-48210 */
# EVIDENCE-OF: R-22771-48210 The number of bytes stored on the leaf page
# is never less than M.

R-54470-63023-21139-04882-06661-43927-15074-46369 tcl slt th3 src

Let X be ((U-12)*64/255)-23). If the payload size P is less than or equal to X then the entire payload is stored on the b-tree page.

/* IMP: R-54470-63023 */
# EVIDENCE-OF: R-54470-63023 Let X be ((U-12)*64/255)-23). If the
# payload size P is less than or equal to X then the entire payload is
# stored on the b-tree page.

R-46709-29000-19476-32067-18840-61551-53618-60985 tcl slt th3 src

Let M be ((U-12)*32/255)-23 and let K be M+((P-M)%(U-4)). If P is greater than X then the number of bytes stored on the index b-tree page is K if K is less than or equal to X or M otherwise.

/* IMP: R-46709-29000 */
# EVIDENCE-OF: R-46709-29000 Let M be ((U-12)*32/255)-23 and let K be
# M+((P-M)%(U-4)). If P is greater than X then the number of bytes
# stored on the index b-tree page is K if K is less than or equal to X
# or M otherwise.

R-62128-24425-59419-24092-37826-34939-18827-33006 tcl slt th3 src

The number of bytes stored on the index page is never less than M.

/* IMP: R-62128-24425 */
# EVIDENCE-OF: R-62128-24425 The number of bytes stored on the index
# page is never less than M.

R-03407-34626-50961-58560-51866-04262-39023-12943 tcl slt th3 src

If P<=X then all P bytes of payload are stored directly on the btree page without overflow.

/* IMP: R-03407-34626 */
# EVIDENCE-OF: R-03407-34626 If P<=X then all P bytes of payload are
# stored directly on the btree page without overflow.

R-26492-45204-29363-38379-65381-14832-32787-08398 tcl slt th3 src

If P>X and K<=X then the first K bytes of P are stored on the btree page and the remaining P-K bytes are stored on overflow pages.

/* IMP: R-26492-45204 */
# EVIDENCE-OF: R-26492-45204 If P>X and K<=X then the first K
# bytes of P are stored on the btree page and the remaining P-K bytes
# are stored on overflow pages.

R-35454-31058-05961-25112-64343-26549-00069-20498 tcl slt th3 src

If P>X and K>X then the first M bytes of P are stored on the btree page and the remaining P-M bytes are stored on overflow pages.

/* IMP: R-35454-31058 */
# EVIDENCE-OF: R-35454-31058 If P>X and K>X then the first M bytes
# of P are stored on the btree page and the remaining P-M bytes are
# stored on overflow pages.

R-63235-50818-32083-65430-57291-26103-11022-63065 tcl slt th3 src

When the payload of a b-tree cell is too large for the b-tree page, the surplus is spilled onto overflow pages.

/* IMP: R-63235-50818 */
# EVIDENCE-OF: R-63235-50818 When the payload of a b-tree cell is too
# large for the b-tree page, the surplus is spilled onto overflow pages.

R-48332-15034-37665-36826-65494-46636-19772-53636 tcl slt th3 src

Overflow pages form a linked list.

/* IMP: R-48332-15034 */
# EVIDENCE-OF: R-48332-15034 Overflow pages form a linked list.

R-20413-39093-25878-54576-60732-55561-33363-27302 tcl slt th3 src

The first four bytes of each overflow page are a big-endian integer which is the page number of the next page in the chain, or zero for the final page in the chain.

/* IMP: R-20413-39093 */
# EVIDENCE-OF: R-20413-39093 The first four bytes of each overflow page
# are a big-endian integer which is the page number of the next page in
# the chain, or zero for the final page in the chain.

R-40949-19585-05483-44375-20599-28045-28956-60873 tcl slt th3 src

The fifth byte through the last usable byte are used to hold overflow content.

/* IMP: R-40949-19585 */
# EVIDENCE-OF: R-40949-19585 The fifth byte through the last usable byte
# are used to hold overflow content.

R-00717-60341-45862-31619-05346-22343-41612-42650 tcl slt th3 src

Ptrmap pages must exist in any database file which has a non-zero largest root b-tree page value at offset 52 in the database header.

/* IMP: R-00717-60341 */
# EVIDENCE-OF: R-00717-60341 Ptrmap pages must exist in any database
# file which has a non-zero largest root b-tree page value at offset 52
# in the database header.

R-15548-18334-64551-32138-25458-60589-38835-29955 tcl slt th3 src

If the largest root b-tree page value is zero, then the database must not contain ptrmap pages.

/* IMP: R-15548-18334 */
# EVIDENCE-OF: R-15548-18334 If the largest root b-tree page value is
# zero, then the database must not contain ptrmap pages.

R-38176-56274-44779-06953-35503-33902-10868-27976 tcl slt th3 src

In a database with ptrmap pages, the first ptrmap page is page 2.

/* IMP: R-38176-56274 */
# EVIDENCE-OF: R-38176-56274 In a database with ptrmap pages, the first
# ptrmap page is page 2.

R-64600-17320-28497-51655-11610-39748-50206-04089 tcl slt th3 src

The first ptrmap page will contain back pointer information for pages 3 through J+2, inclusive.

/* IMP: R-64600-17320 */
# EVIDENCE-OF: R-64600-17320 The first ptrmap page will contain back
# pointer information for pages 3 through J+2, inclusive.

R-06184-64035-19129-41964-19001-40522-59120-29266 tcl slt th3 src

The second pointer map page will be on page J+3 and that ptrmap page will provide back pointer information for pages J+4 through 2*J+3 inclusive.

/* IMP: R-06184-64035 */
# EVIDENCE-OF: R-06184-64035 The second pointer map page will be on page
# J+3 and that ptrmap page will provide back pointer information for
# pages J+4 through 2*J+3 inclusive.

R-11041-58701-63446-54854-64444-07239-52476-54830 tcl slt th3 src

In a database that uses ptrmap pages, all pages at locations identified by the computation in the previous paragraph must be ptrmap page and no other page may be a ptrmap page. Except, if the byte-lock page happens to fall on the same page number as a ptrmap page, then the ptrmap is moved to the following page for that one case.

/* IMP: R-11041-58701 */
# EVIDENCE-OF: R-11041-58701 In a database that uses ptrmap pages, all
# pages at locations identified by the computation in the previous
# paragraph must be ptrmap page and no other page may be a ptrmap page.
# Except, if the byte-lock page happens to fall on the same page number
# as a ptrmap page, then the ptrmap is moved to the following page for
# that one case.

R-11976-23534-61150-43568-11270-52721-42271-48852 tcl slt th3 src

If page B is a ptrmap page then back-link information about page B+1 is provided by the first entry on the pointer map. Information about page B+2 is provided by the second entry. And so forth.

/* IMP: R-11976-23534 */
# EVIDENCE-OF: R-11976-23534 If page B is a ptrmap page then back-link
# information about page B+1 is provided by the first entry on the
# pointer map. Information about page B+2 is provided by the second
# entry. And so forth.

R-50551-20166-10060-53242-13172-15723-06354-52489 tcl slt th3 src

In any database file that contains ptrmap pages, all b-tree root pages must come before any non-root b-tree page, cell payload overflow page, or freelist page.

/* IMP: R-50551-20166 */
# EVIDENCE-OF: R-50551-20166 In any database file that contains ptrmap
# pages, all b-tree root pages must come before any non-root b-tree
# page, cell payload overflow page, or freelist page.

R-31029-02789-51389-40070-25151-19392-18984-32450 tcl slt th3 src

Root pages are moved to the beginning of the database file by the CREATE TABLE, CREATE INDEX, DROP TABLE, and DROP INDEX operations.

/* IMP: R-31029-02789 */
# EVIDENCE-OF: R-31029-02789 Root pages are moved to the beginning of
# the database file by the CREATE TABLE, CREATE INDEX, DROP TABLE, and
# DROP INDEX operations.

R-22564-11647-52151-33857-28940-09855-65445-38014 tcl slt th3 src

The header begins with a single varint which determines the total number of bytes in the header. The varint value is the size of the header in bytes including the size varint itself.

src/vdbe.c:2782   th3/req1/fileformat02.test:20

/* IMP: R-22564-11647 */
# EVIDENCE-OF: R-22564-11647 The header begins with a single varint
# which determines the total number of bytes in the header. The varint
# value is the size of the header in bytes including the size varint
# itself.

R-06529-47362-28298-49876-57281-01728-38007-39876 tcl slt th3 src

Following the size varint are one or more additional varints, one per column.

src/vdbe.c:2819   th3/req1/fileformat02.test:25

/* IMP: R-06529-47362 */
# EVIDENCE-OF: R-06529-47362 Following the size varint are one or more
# additional varints, one per column.

R-24078-09375-10188-46060-61284-00703-09304-21721 tcl slt th3 src

Value is a NULL.

src/vdbeaux.c:3445   th3/req1/fileformat02.test:56

/* IMP: R-24078-09375 */
# EVIDENCE-OF: R-24078-09375 Value is a NULL.

R-44885-25196-65175-63597-64540-27010-19745-60174 tcl slt th3 src

Value is an 8-bit twos-complement integer.

src/vdbeaux.c:3450   th3/req1/fileformat02.test:72

/* IMP: R-44885-25196 */
# EVIDENCE-OF: R-44885-25196 Value is an 8-bit twos-complement integer.

R-49794-35026-38918-00907-30383-35141-38655-30488 tcl slt th3 src

Value is a big-endian 16-bit twos-complement integer.

src/vdbeaux.c:3458   th3/req1/fileformat02.test:94

/* IMP: R-49794-35026 */
# EVIDENCE-OF: R-49794-35026 Value is a big-endian 16-bit
# twos-complement integer.

R-37839-54301-54800-59276-61282-21878-13355-00369 tcl slt th3 src

Value is a big-endian 24-bit twos-complement integer.

src/vdbeaux.c:3466   th3/req1/fileformat02.test:114

/* IMP: R-37839-54301 */
# EVIDENCE-OF: R-37839-54301 Value is a big-endian 24-bit
# twos-complement integer.

R-01849-26079-03122-24665-29509-65417-11059-24465 tcl slt th3 src

Value is a big-endian 32-bit twos-complement integer.

src/vdbeaux.c:3474   th3/req1/fileformat02.test:134

/* IMP: R-01849-26079 */
# EVIDENCE-OF: R-01849-26079 Value is a big-endian 32-bit
# twos-complement integer.

R-50385-09674-49008-33764-60842-63141-30629-37607 tcl slt th3 src

Value is a big-endian 48-bit twos-complement integer.

src/vdbeaux.c:3486   th3/req1/fileformat02.test:154

/* IMP: R-50385-09674 */
# EVIDENCE-OF: R-50385-09674 Value is a big-endian 48-bit
# twos-complement integer.

R-29851-52272-33233-14067-54274-53843-25272-26823 tcl slt th3 src

Value is a big-endian 64-bit twos-complement integer.

src/vdbeaux.c:3409   th3/req1/fileformat02.test:175

/* IMP: R-29851-52272 */
# EVIDENCE-OF: R-29851-52272 Value is a big-endian 64-bit
# twos-complement integer.

R-57343-49114-15055-13437-46054-52631-22669-49552 tcl slt th3 src

Value is a big-endian IEEE 754-2008 64-bit floating point number.

src/vdbeaux.c:3415   th3/req1/fileformat02.test:212

/* IMP: R-57343-49114 */
# EVIDENCE-OF: R-57343-49114 Value is a big-endian IEEE 754-2008 64-bit
# floating point number.

R-12976-22893-11163-42545-13145-11346-20238-07010 tcl slt th3 src

Value is the integer 0.

src/vdbeaux.c:3501   th3/req1/fileformat02.test:234

/* IMP: R-12976-22893 */
# EVIDENCE-OF: R-12976-22893 Value is the integer 0.

R-18143-12121-25249-36193-24562-40889-39176-48672 tcl slt th3 src

Value is the integer 1.

src/vdbeaux.c:3502   th3/req1/fileformat02.test:253

/* IMP: R-18143-12121 */
# EVIDENCE-OF: R-18143-12121 Value is the integer 1.

R-14606-31564-30448-03385-20591-46329-55460-01385 tcl slt th3 src

Value is a BLOB that is (N-12)/2 bytes in length.

src/vdbeaux.c:3508   th3/req1/fileformat02.test:274

/* IMP: R-14606-31564 */
# EVIDENCE-OF: R-14606-31564 Value is a BLOB that is (N-12)/2 bytes in
# length.

R-28401-00140-19628-52826-12287-35959-24998-24420 tcl slt th3 src

Value is a string in the text encoding and (N-13)/2 bytes in length.

src/vdbeaux.c:3510   th3/req1/fileformat02.test:277

/* IMP: R-28401-00140 */
# EVIDENCE-OF: R-28401-00140 Value is a string in the text encoding and
# (N-13)/2 bytes in length.

R-18547-52845-30240-29713-43116-28155-35660-14807 tcl slt th3 src

The nul terminator is not stored.

th3/req1/fileformat02.test:280

/* IMP: R-18547-52845 */
# EVIDENCE-OF: R-18547-52845 The nul terminator is not stored.

R-64536-51728-48624-04422-11602-40097-21490-13878 tcl slt th3 src

The values for each column in the record immediately follow the header.

src/vdbe.c:2822

/* IMP: R-64536-51728 */
# EVIDENCE-OF: R-64536-51728 The values for each column in the record
# immediately follow the header.

R-60007-46407-64450-37762-55294-24345-19762-56932 tcl slt th3 src

For serial types 0, 8, 9, 12, and 13, the value is zero bytes in length. If all columns are of these types then the body section of the record is empty.

th3/req1/fileformat02.test:299

/* IMP: R-60007-46407 */
# EVIDENCE-OF: R-60007-46407 For serial types 0, 8, 9, 12, and 13, the
# value is zero bytes in length. If all columns are of these types then
# the body section of the record is empty.

R-64415-18962-49048-16298-22605-23771-20510-46620 tcl slt th3 src

A record might have fewer values than the number of columns in the corresponding table.

/* IMP: R-64415-18962 */
# EVIDENCE-OF: R-64415-18962 A record might have fewer values than the
# number of columns in the corresponding table.

R-13635-51078-58575-05686-24588-25758-48639-24635 tcl slt th3 src

Missing values at the end of the record are filled in using the default value for the corresponding columns defined in the table schema.

/* IMP: R-13635-51078 */
# EVIDENCE-OF: R-13635-51078 Missing values at the end of the record are
# filled in using the default value for the corresponding columns
# defined in the table schema.

R-18347-31070-03027-11066-56518-02178-55511-27886 tcl slt th3 src

NULL values (serial type 0) sort first.

th3/req1/fileformat05.test:43

/* IMP: R-18347-31070 */
# EVIDENCE-OF: R-18347-31070 NULL values (serial type 0) sort first.

R-15111-17896-62926-21896-36187-36992-24278-56286 tcl slt th3 src

Numeric values (serial types 1 through 9) sort after NULLs and in numeric order.

th3/req1/fileformat05.test:50

/* IMP: R-15111-17896 */
# EVIDENCE-OF: R-15111-17896 Numeric values (serial types 1 through 9)
# sort after NULLs and in numeric order.

R-09669-59284-18904-57253-21475-23488-47064-03765 tcl slt th3 src

Text values (odd serial types 13 and larger) sort after numeric values in the order determined by the columns collating function.

th3/req1/fileformat05.test:57

/* IMP: R-09669-59284 */
# EVIDENCE-OF: R-09669-59284 Text values (odd serial types 13 and
# larger) sort after numeric values in the order determined by the
# columns collating function.

R-40318-26723-38949-62709-27455-61275-20452-62962 tcl slt th3 src

BLOB values (even serial types 12 and larger) sort last and in the order determined by memcmp().

th3/req1/fileformat05.test:69

/* IMP: R-40318-26723 */
# EVIDENCE-OF: R-40318-26723 BLOB values (even serial types 12 and
# larger) sort last and in the order determined by memcmp().

R-52786-44878-02459-51231-59841-41905-39578-10951 tcl slt th3 src

SQLite defines three built-in collating functions:

src/main.c:2920

/* IMP: R-52786-44878 */
# EVIDENCE-OF: R-52786-44878 SQLite defines three built-in collating
# functions:

R-65033-28449-21866-26015-44447-32158-58356-59086 tcl slt th3 src

The built-in BINARY collation compares strings byte by byte using the memcmp() function from the standard C library.

src/main.c:873

/* IMP: R-65033-28449 */
# EVIDENCE-OF: R-65033-28449 The built-in BINARY collation compares
# strings byte by byte using the memcmp() function from the standard C
# library.

R-34481-43669-25977-57618-65082-36703-04463-10475 tcl slt th3 src

The NOCASE collation is like BINARY except that uppercase ASCII characters ('A' through 'Z') are folded into their lowercase equivalents prior to running the comparison. Only ASCII characters are case-folded.

/* IMP: R-34481-43669 */
# EVIDENCE-OF: R-34481-43669 The NOCASE collation is like BINARY except
# that uppercase ASCII characters ('A' through 'Z') are folded into
# their lowercase equivalents prior to running the comparison. Only
# ASCII characters are case-folded.

R-38980-08445-54197-34551-31600-64664-60502-04654 tcl slt th3 src

NOCASE does not implement a general purpose unicode caseless comparison.

/* IMP: R-38980-08445 */
# EVIDENCE-OF: R-38980-08445 NOCASE does not implement a general purpose
# unicode caseless comparison.

R-31624-24737-51221-05558-39670-00793-48763-40650 tcl slt th3 src

RTRIM is like BINARY except that extra spaces at the end of either string do not change the result. In other words, strings will compare equal to one another as long as they differ only in the number of spaces at the end.

src/main.c:883

/* IMP: R-31624-24737 */
# EVIDENCE-OF: R-31624-24737 RTRIM is like BINARY except that extra
# spaces at the end of either string do not change the result. In other
# words, strings will compare equal to one another as long as they
# differ only in the number of spaces at the end.

R-30104-14201-52849-13140-57489-26582-19257-15267 tcl slt th3 src

Additional application-specific collating functions can be added to SQLite using the sqlite3_create_collation() interface.

th3/th3util.c:1993

/* IMP: R-30104-14201 */
# EVIDENCE-OF: R-30104-14201 Additional application-specific collating
# functions can be added to SQLite using the sqlite3_create_collation()
# interface.

R-08308-17224-38959-45804-35149-08611-40097-56535 tcl slt th3 src

The default collating function for all strings is BINARY.

src/main.c:2931   th3/req1/fileformat05.test:80

/* IMP: R-08308-17224 */
# EVIDENCE-OF: R-08308-17224 The default collating function for all
# strings is BINARY.

R-23479-00031-14881-27531-19387-40447-21321-20674 tcl slt th3 src

Alternative collating functions for table columns can be specified in the CREATE TABLE statement using the COLLATE clause on the column definition.

th3/req1/fileformat05.test:89

/* IMP: R-23479-00031 */
# EVIDENCE-OF: R-23479-00031 Alternative collating functions for table
# columns can be specified in the CREATE TABLE statement using the
# COLLATE clause on the column definition.

R-57195-25932-26146-45143-15236-49265-31478-06435 tcl slt th3 src

When a column is indexed, the same collating function specified in the CREATE TABLE statement is used for the column in the index, by default, though this can be overridden using a COLLATE clause in the CREATE INDEX statement.

th3/req1/fileformat05.test:104

/* IMP: R-57195-25932 */
# EVIDENCE-OF: R-57195-25932 When a column is indexed, the same
# collating function specified in the CREATE TABLE statement is used for
# the column in the index, by default, though this can be overridden
# using a COLLATE clause in the CREATE INDEX statement.

R-27945-10199-33337-38453-50117-26077-19774-25797 tcl slt th3 src

Each ordinary SQL table in the database schema is represented on-disk by a table b-tree.

/* IMP: R-27945-10199 */
# EVIDENCE-OF: R-27945-10199 Each ordinary SQL table in the database
# schema is represented on-disk by a table b-tree.

R-13210-09522-30167-47682-54328-15503-65010-64185 tcl slt th3 src

Each entry in the table b-tree corresponds to a row of the SQL table.

/* IMP: R-13210-09522 */
# EVIDENCE-OF: R-13210-09522 Each entry in the table b-tree corresponds
# to a row of the SQL table.

R-21674-26855-05967-15589-29302-41964-38112-11067 tcl slt th3 src

The rowid of the SQL table is the 64-bit signed integer key for each entry in the table b-tree.

/* IMP: R-21674-26855 */
# EVIDENCE-OF: R-21674-26855 The rowid of the SQL table is the 64-bit
# signed integer key for each entry in the table b-tree.

R-53201-01290-50295-51716-63557-40807-32035-47874 tcl slt th3 src

The content of each SQL table row is stored in the database file by first combining the values in the various columns into a byte array in the record format, then storing that byte array as the payload in an entry in the table b-tree.

/* IMP: R-53201-01290 */
# EVIDENCE-OF: R-53201-01290 The content of each SQL table row is stored
# in the database file by first combining the values in the various
# columns into a byte array in the record format, then storing that byte
# array as the payload in an entry in the table b-tree.

R-39279-48828-00404-43812-37996-18739-13104-15317 tcl slt th3 src

The order of values in the record is the same as the order of columns in the SQL table definition.

/* IMP: R-39279-48828 */
# EVIDENCE-OF: R-39279-48828 The order of values in the record is the
# same as the order of columns in the SQL table definition.

R-50324-49641-07188-15996-37030-30300-11878-30571 tcl slt th3 src

When an SQL table includes an INTEGER PRIMARY KEY column (which aliases the rowid) then that column appears in the record as a NULL value.

/* IMP: R-50324-49641 */
# EVIDENCE-OF: R-50324-49641 When an SQL table includes an INTEGER
# PRIMARY KEY column (which aliases the rowid) then that column appears
# in the record as a NULL value.

R-36394-12461-27768-64371-31765-36302-25885-56308 tcl slt th3 src

SQLite will always use the table b-tree key rather than the NULL value when referencing the INTEGER PRIMARY KEY column.

/* IMP: R-36394-12461 */
# EVIDENCE-OF: R-36394-12461 SQLite will always use the table b-tree key
# rather than the NULL value when referencing the INTEGER PRIMARY KEY
# column.

R-59544-00330-10007-23710-00944-16891-38828-33861 tcl slt th3 src

If the affinity of a column is REAL and that column contains a value that can be converted to an integer without loss of information (if the value contains no fractional part and is not too large to be represented as an integer) then the column may be stored in the record as an integer.

/* IMP: R-59544-00330 */
# EVIDENCE-OF: R-59544-00330 If the affinity of a column is REAL and
# that column contains a value that can be converted to an integer
# without loss of information (if the value contains no fractional part
# and is not too large to be represented as an integer) then the column
# may be stored in the record as an integer.

R-60985-57662-04483-59248-10439-16937-18279-52954 tcl slt th3 src

SQLite will convert the value back to floating point when extracting it from the record.

src/expr.c:3987

/* IMP: R-60985-57662 */
# EVIDENCE-OF: R-60985-57662 SQLite will convert the value back to
# floating point when extracting it from the record.

R-37306-64424-24476-44387-21141-11283-27580-01252 tcl slt th3 src

If an SQL table is created using the "WITHOUT ROWID" clause at the end of its CREATE TABLE statement, then that table is a WITHOUT ROWID table and uses a different on-disk representation.

/* IMP: R-37306-64424 */
# EVIDENCE-OF: R-37306-64424 If an SQL table is created using the
# "WITHOUT ROWID" clause at the end of its CREATE TABLE statement, then
# that table is a WITHOUT ROWID table and uses a different on-disk
# representation.

R-36947-39582-19332-41522-37066-30341-40007-20770 tcl slt th3 src

A WITHOUT ROWID table uses an index b-tree rather than a table b-tree for storage.

/* IMP: R-36947-39582 */
# EVIDENCE-OF: R-36947-39582 A WITHOUT ROWID table uses an index b-tree
# rather than a table b-tree for storage.

R-26480-31567-01189-11331-40338-43119-64271-54165 tcl slt th3 src

The key for each entry in the WITHOUT ROWID b-tree is a record composed of the columns of the PRIMARY KEY followed by all remaining columns of the table.

/* IMP: R-26480-31567 */
# EVIDENCE-OF: R-26480-31567 The key for each entry in the WITHOUT ROWID
# b-tree is a record composed of the columns of the PRIMARY KEY followed
# by all remaining columns of the table.

R-21024-07855-27912-21826-13192-43444-47677-34434 tcl slt th3 src

The primary key columns appear in the order they they were declared in the PRIMARY KEY clause and the remaining columns appear in the order they occur in the CREATE TABLE statement.

/* IMP: R-21024-07855 */
# EVIDENCE-OF: R-21024-07855 The primary key columns appear in the order
# they they were declared in the PRIMARY KEY clause and the remaining
# columns appear in the order they occur in the CREATE TABLE statement.

R-07686-30751-51285-58561-12727-05752-40900-44621 tcl slt th3 src

Hence, the content encoding for a WITHOUT ROWID table is the same as the content encoding for an ordinary rowid table, except that the order of the columns is rearranged so that PRIMARY KEY columns come first, and the content is used as the key in an index b-tree rather than as the data in a table b-tree.

/* IMP: R-07686-30751 */
# EVIDENCE-OF: R-07686-30751 Hence, the content encoding for a WITHOUT
# ROWID table is the same as the content encoding for an ordinary rowid
# table, except that the order of the columns is rearranged so that
# PRIMARY KEY columns come first, and the content is used as the key in
# an index b-tree rather than as the data in a table b-tree.

R-14054-28168-18904-50700-49610-53857-56121-06395 tcl slt th3 src

The special encoding rules for columns with REAL affinity apply to WITHOUT ROWID tables the same as they do with rowid tables.

/* IMP: R-14054-28168 */
# EVIDENCE-OF: R-14054-28168 The special encoding rules for columns with
# REAL affinity apply to WITHOUT ROWID tables the same as they do with
# rowid tables.

R-43382-54865-31574-52044-42032-61533-34154-44077 tcl slt th3 src

Each SQL index, whether explicitly declared via a CREATE INDEX statement or implied by a UNIQUE or PRIMARY KEY constraint, corresponds to an index b-tree in the database file.

/* IMP: R-43382-54865 */
# EVIDENCE-OF: R-43382-54865 Each SQL index, whether explicitly declared
# via a CREATE INDEX statement or implied by a UNIQUE or PRIMARY KEY
# constraint, corresponds to an index b-tree in the database file.

R-39254-55835-64728-03186-59811-05640-07786-15038 tcl slt th3 src

Each entry in the index b-tree corresponds to a single row in the associated SQL table.

/* IMP: R-39254-55835 */
# EVIDENCE-OF: R-39254-55835 Each entry in the index b-tree corresponds
# to a single row in the associated SQL table.

R-05702-07867-01732-07701-01060-12844-31655-42982 tcl slt th3 src

The key to an index b-tree is a record composed of the columns that are being indexed followed by the key of the corresponding table row.

/* IMP: R-05702-07867 */
# EVIDENCE-OF: R-05702-07867 The key to an index b-tree is a record
# composed of the columns that are being indexed followed by the key of
# the corresponding table row.

R-54925-42075-28504-27777-13871-37665-20055-23123 tcl slt th3 src

In a normal index, there is a one-to-one mapping between rows in a table and entries in each index associated with that table.

/* IMP: R-54925-42075 */
# EVIDENCE-OF: R-54925-42075 In a normal index, there is a one-to-one
# mapping between rows in a table and entries in each index associated
# with that table.

R-56419-60514-06155-63301-38811-42368-46598-13480 tcl slt th3 src

However, in a partial index, the index b-tree only contains entries corresponding to table rows for which the WHERE clause expression on the CREATE INDEX statement is true.

/* IMP: R-56419-60514 */
# EVIDENCE-OF: R-56419-60514 However, in a partial index, the index
# b-tree only contains entries corresponding to table rows for which the
# WHERE clause expression on the CREATE INDEX statement is true.

R-08804-63175-03781-61718-64946-14312-06461-12465 tcl slt th3 src

Corresponding rows in the index and table b-trees share the same rowid or primary key values and contain the same value for all indexed columns.

/* IMP: R-08804-63175 */
# EVIDENCE-OF: R-08804-63175 Corresponding rows in the index and table
# b-trees share the same rowid or primary key values and contain the
# same value for all indexed columns.

R-06109-26546-51214-53713-59648-49657-53579-51997 tcl slt th3 src

In an index on a WITHOUT ROWID table, if one or more of the columns of the table PRIMARY KEY are also columns of the index, then the indexed column is not repeated in the table-key suffix on the end of the index record.

th3/req1/fileformat04.test:12

/* IMP: R-06109-26546 */
# EVIDENCE-OF: R-06109-26546 In an index on a WITHOUT ROWID table, if
# one or more of the columns of the table PRIMARY KEY are also columns
# of the index, then the indexed column is not repeated in the table-key
# suffix on the end of the index record.

R-51390-33278-03320-06304-01123-63988-56273-08601 tcl slt th3 src

As an example, consider the following SQL:

CREATE TABLE ex25(a,b,c,d,e,PRIMARY KEY(d,c,a)) WITHOUT rowid;
CREATE INDEX ex25ce ON ex25(c,e);
CREATE INDEX ex25acde ON ex25(a,c,d,e);

Each row in the ex25ce index is a record with these columns: c, e, d, a. The first two columns are the columns being indexed, c and e. The remaining columns are the primary key of the corresponding table row. Normally, the primary key would be columns d, c, and a, but because column c already appears earlier in the index, it is omitted from the key suffix.

th3/req1/fileformat04.test:17

/* IMP: R-51390-33278 */
# EVIDENCE-OF: R-51390-33278 As an example, consider the following SQL:
# CREATE TABLE ex25(a,b,c,d,e,PRIMARY KEY(d,c,a)) WITHOUT rowid; CREATE
# INDEX ex25ce ON ex25(c,e); CREATE INDEX ex25acde ON ex25(a,c,d,e);
# Each row in the ex25ce index is a record with these columns: c, e, d,
# a. The first two columns are the columns being indexed, c and e. The
# remaining columns are the primary key of the corresponding table row.
# Normally, the primary key would be columns d, c, and a, but because
# column c already appears earlier in the index, it is omitted from the
# key suffix.

R-62291-48401-09187-39181-50628-35603-55893-32208 tcl slt th3 src

In the extreme case where the columns being indexed cover all columns of the PRIMARY KEY, the index will consist of only the columns being indexed. The ex25acde example above demonstrates this.

th3/req1/fileformat04.test:45

/* IMP: R-62291-48401 */
# EVIDENCE-OF: R-62291-48401 In the extreme case where the columns being
# indexed cover all columns of the PRIMARY KEY, the index will consist
# of only the columns being indexed. The ex25acde example above
# demonstrates this.

R-06521-54703-61418-20766-12795-30140-09114-51879 tcl slt th3 src

Each entry in the ex25acde index consists of only the columns a, c, d, and e, in that order.

th3/req1/fileformat04.test:50

/* IMP: R-06521-54703 */
# EVIDENCE-OF: R-06521-54703 Each entry in the ex25acde index consists
# of only the columns a, c, d, and e, in that order.

R-45744-19146-21008-38057-12826-38180-64084-01618 tcl slt th3 src

The suppression of redundant columns in the key suffix of an index entry only occurs in WITHOUT ROWID tables.

th3/req1/fileformat04.test:61

/* IMP: R-45744-19146 */
# EVIDENCE-OF: R-45744-19146 The suppression of redundant columns in the
# key suffix of an index entry only occurs in WITHOUT ROWID tables.

R-03473-60719-23522-47619-30011-51175-32203-58759 tcl slt th3 src

In an ordinary rowid table, the index entry always ends with the rowid even if the INTEGER PRIMARY KEY column is one of the columns being indexed.

th3/req1/fileformat04.test:64

/* IMP: R-03473-60719 */
# EVIDENCE-OF: R-03473-60719 In an ordinary rowid table, the index entry
# always ends with the rowid even if the INTEGER PRIMARY KEY column is
# one of the columns being indexed.

R-48544-16048-58560-43794-36911-40715-54849-57363 tcl slt th3 src

Page 1 of a database file is the root page of a table b-tree that holds a special table named "sqlite_master" (or "sqlite_temp_master" in the case of a TEMP database) which stores the complete database schema.

/* IMP: R-48544-16048 */
# EVIDENCE-OF: R-48544-16048 Page 1 of a database file is the root page
# of a table b-tree that holds a special table named "sqlite_master" (or
# "sqlite_temp_master" in the case of a TEMP database) which stores the
# complete database schema.

R-58993-22660-13227-57467-37218-61239-51147-21890 tcl slt th3 src

The structure of the sqlite_master table is as if it had been created using the following SQL:

CREATE TABLE sqlite_master(
  type text,
  name text,
  tbl_name text,
  rootpage integer,
  sql text
);

/* IMP: R-58993-22660 */
# EVIDENCE-OF: R-58993-22660 The structure of the sqlite_master table is
# as if it had been created using the following SQL: CREATE TABLE
# sqlite_master( type text, name text, tbl_name text, rootpage integer,
# sql text );

R-17422-06514-52731-10932-24026-18729-00047-10893 tcl slt th3 src

The sqlite_master table contains one row for each table, index, view, and trigger (collectively "objects") in the database schema, except there is no entry for the sqlite_master table itself.

/* IMP: R-17422-06514 */
# EVIDENCE-OF: R-17422-06514 The sqlite_master table contains one row
# for each table, index, view, and trigger (collectively "objects") in
# the database schema, except there is no entry for the sqlite_master
# table itself.

R-11799-54013-19740-27693-35471-55958-11272-42681 tcl slt th3 src

The sqlite_master table contains entries for internal schema objects in addition to application- and programmer-defined objects.

/* IMP: R-11799-54013 */
# EVIDENCE-OF: R-11799-54013 The sqlite_master table contains entries
# for internal schema objects in addition to application- and
# programmer-defined objects.

R-61406-01422-12165-06477-03063-10518-53004-15686 tcl slt th3 src

The sqlite_master.type column will be one of the following text strings: 'table', 'index', 'view', or 'trigger' according to the type of object defined. The 'table' string is used for both ordinary and virtual tables.

/* IMP: R-61406-01422 */
# EVIDENCE-OF: R-61406-01422 The sqlite_master.type column will be one
# of the following text strings: 'table', 'index', 'view', or 'trigger'
# according to the type of object defined. The 'table' string is used
# for both ordinary and virtual tables.

R-23658-63678-27642-11273-33121-18173-42579-42965 tcl slt th3 src

The sqlite_master.name column will hold the name of the object.

/* IMP: R-23658-63678 */
# EVIDENCE-OF: R-23658-63678 The sqlite_master.name column will hold the
# name of the object.

R-23569-48727-57101-02184-22731-43304-31733-55704 tcl slt th3 src

UNIQUE and PRIMARY KEY constraints on tables cause SQLite to create internal indexes with names of the form "sqlite_autoindex_TABLE_N" where TABLE is replaced by the name of the table that contains the constraint and N is an integer beginning with 1 and increasing by one with each constraint seen in the table definition.

/* IMP: R-23569-48727 */
# EVIDENCE-OF: R-23569-48727 UNIQUE and PRIMARY KEY constraints on
# tables cause SQLite to create internal indexes with names of the form
# "sqlite_autoindex_TABLE_N" where TABLE is replaced by the name of the
# table that contains the constraint and N is an integer beginning with
# 1 and increasing by one with each constraint seen in the table
# definition.

R-56903-10946-10464-47244-20107-17033-43956-53082 tcl slt th3 src

In a WITHOUT ROWID table, there is no sqlite_master entry for the PRIMARY KEY, but the "sqlite_autoindex_TABLE_N" name is set aside for the PRIMARY KEY as if the sqlite_master entry did exist. This will affect the numbering of subsequent UNIQUE constraints.

/* IMP: R-56903-10946 */
# EVIDENCE-OF: R-56903-10946 In a WITHOUT ROWID table, there is no
# sqlite_master entry for the PRIMARY KEY, but the
# "sqlite_autoindex_TABLE_N" name is set aside for the PRIMARY KEY as if
# the sqlite_master entry did exist. This will affect the numbering of
# subsequent UNIQUE constraints.

R-07218-15811-34586-61584-11375-61122-40476-23928 tcl slt th3 src

The "sqlite_autoindex_TABLE_N" name is never allocated for an INTEGER PRIMARY KEY, either in rowid tables or WITHOUT ROWID tables.

/* IMP: R-07218-15811 */
# EVIDENCE-OF: R-07218-15811 The "sqlite_autoindex_TABLE_N" name is
# never allocated for an INTEGER PRIMARY KEY, either in rowid tables or
# WITHOUT ROWID tables.

R-37773-10272-55463-21527-23891-50096-65319-03368 tcl slt th3 src

For a table or view, the tbl_name column is a copy of the name column.

/* IMP: R-37773-10272 */
# EVIDENCE-OF: R-37773-10272 For a table or view, the tbl_name column is
# a copy of the name column.

R-43753-41505-35386-09439-04210-20192-61863-65048 tcl slt th3 src

For an index, the tbl_name is the name of the table that is indexed.

/* IMP: R-43753-41505 */
# EVIDENCE-OF: R-43753-41505 For an index, the tbl_name is the name of
# the table that is indexed.

R-03387-12896-30110-08033-64260-38467-31820-29841 tcl slt th3 src

For a trigger, the tbl_name column stores the name of the table or view that causes the trigger to fire.

/* IMP: R-03387-12896 */
# EVIDENCE-OF: R-03387-12896 For a trigger, the tbl_name column stores
# the name of the table or view that causes the trigger to fire.

R-09565-18143-64866-36036-15374-23384-04094-39911 tcl slt th3 src

The sqlite_master.rootpage column stores the page number of the root b-tree page for tables and indexes.

/* IMP: R-09565-18143 */
# EVIDENCE-OF: R-09565-18143 The sqlite_master.rootpage column stores
# the page number of the root b-tree page for tables and indexes.

R-52864-57317-33695-37364-46935-23221-38785-50224 tcl slt th3 src

For rows that define views, triggers, and virtual tables, the rootpage column is 0 or NULL.

/* IMP: R-52864-57317 */
# EVIDENCE-OF: R-52864-57317 For rows that define views, triggers, and
# virtual tables, the rootpage column is 0 or NULL.

R-08292-01711-30929-18073-05835-23551-48977-32015 tcl slt th3 src

The sqlite_master.sql column stores SQL text that describes the object. This SQL text is a CREATE TABLE, CREATE VIRTUAL TABLE, CREATE INDEX, CREATE VIEW, or CREATE TRIGGER statement that if evaluated against the database file when it is the main database of a database connection would recreate the object.

/* IMP: R-08292-01711 */
# EVIDENCE-OF: R-08292-01711 The sqlite_master.sql column stores SQL
# text that describes the object. This SQL text is a CREATE TABLE,
# CREATE VIRTUAL TABLE, CREATE INDEX, CREATE VIEW, or CREATE TRIGGER
# statement that if evaluated against the database file when it is the
# main database of a database connection would recreate the object.

R-45509-34933-24347-37380-13828-03412-57306-45781 tcl slt th3 src

The CREATE, TABLE, VIEW, TRIGGER, and INDEX keywords at the beginning of the statement are converted to all upper case letters.

/* IMP: R-45509-34933 */
# EVIDENCE-OF: R-45509-34933 The CREATE, TABLE, VIEW, TRIGGER, and INDEX
# keywords at the beginning of the statement are converted to all upper
# case letters.

R-18232-39104-31806-20551-06401-15021-42756-14177 tcl slt th3 src

The TEMP or TEMPORARY keyword is removed if it occurs after the initial CREATE keyword.

/* IMP: R-18232-39104 */
# EVIDENCE-OF: R-18232-39104 The TEMP or TEMPORARY keyword is removed if
# it occurs after the initial CREATE keyword.

R-65463-62993-22311-50014-58341-59872-02270-20667 tcl slt th3 src

Any database name qualifier that occurs prior to the name of the object being created is removed.

/* IMP: R-65463-62993 */
# EVIDENCE-OF: R-65463-62993 Any database name qualifier that occurs
# prior to the name of the object being created is removed.

R-22484-49535-05658-48568-12441-17149-15045-25439 tcl slt th3 src

Leading spaces are removed.

/* IMP: R-22484-49535 */
# EVIDENCE-OF: R-22484-49535 Leading spaces are removed.

R-16677-54172-01660-13905-64102-51935-20932-64314 tcl slt th3 src

All spaces following the first two keywords are converted into a single space.

/* IMP: R-16677-54172 */
# EVIDENCE-OF: R-16677-54172 All spaces following the first two keywords
# are converted into a single space.

R-07393-01823-08752-25333-30222-31178-15035-23553 tcl slt th3 src

The text in the sqlite_master.sql column is a copy of the original CREATE statement text that created the object, except normalized as described above and as modified by subsequent ALTER TABLE statements.

/* IMP: R-07393-01823 */
# EVIDENCE-OF: R-07393-01823 The text in the sqlite_master.sql column is
# a copy of the original CREATE statement text that created the object,
# except normalized as described above and as modified by subsequent
# ALTER TABLE statements.

R-36340-52470-54578-03049-38337-36481-45812-37806 tcl slt th3 src

The sqlite_master.sql is NULL for the internal indexes that are automatically created by UNIQUE or PRIMARY KEY constraints.

/* IMP: R-36340-52470 */
# EVIDENCE-OF: R-36340-52470 The sqlite_master.sql is NULL for the
# internal indexes that are automatically created by UNIQUE or PRIMARY
# KEY constraints.

R-19191-63561-27756-43312-31709-28608-52878-51689 tcl slt th3 src

In addition to the tables, indexes, views, and triggers created by the application and/or the developer using CREATE statements SQL, the sqlite_master table may contain zero or more entries for internal schema objects that are created by SQLite for its own internal use.

/* IMP: R-19191-63561 */
# EVIDENCE-OF: R-19191-63561 In addition to the tables, indexes, views,
# and triggers created by the application and/or the developer using
# CREATE statements SQL, the sqlite_master table may contain zero or
# more entries for internal schema objects that are created by SQLite
# for its own internal use.

R-19885-09448-58561-44146-11128-00310-35686-58085 tcl slt th3 src

The names of internal schema objects always begin with "sqlite_" and any table, index, view, or trigger whose name begins with "sqlite_" is an internal schema object.

/* IMP: R-19885-09448 */
# EVIDENCE-OF: R-19885-09448 The names of internal schema objects always
# begin with "sqlite_" and any table, index, view, or trigger whose name
# begins with "sqlite_" is an internal schema object.

R-44992-52812-59111-17856-46428-55199-28475-08004 tcl slt th3 src

SQLite prohibits applications from creating objects whose names begin with "sqlite_".

/* IMP: R-44992-52812 */
# EVIDENCE-OF: R-44992-52812 SQLite prohibits applications from creating
# objects whose names begin with "sqlite_".

R-26999-04791-16827-31249-13986-61939-06074-63605 tcl slt th3 src

The sqlite_sequence table is an internal table used to help implement AUTOINCREMENT.

/* IMP: R-26999-04791 */
# EVIDENCE-OF: R-26999-04791 The sqlite_sequence table is an internal
# table used to help implement AUTOINCREMENT.

R-59218-44473-40361-49593-50972-37596-39041-04578 tcl slt th3 src

The sqlite_sequence table is created automatically whenever any ordinary table with an AUTOINCREMENT integer primary key is created.

/* IMP: R-59218-44473 */
# EVIDENCE-OF: R-59218-44473 The sqlite_sequence table is created
# automatically whenever any ordinary table with an AUTOINCREMENT
# integer primary key is created.

R-25940-54422-48655-57585-43486-07020-37736-04270 tcl slt th3 src

Once created, the sqlite_sequence table exists in the sqlite_master table forever; it cannot be dropped.

/* IMP: R-25940-54422 */
# EVIDENCE-OF: R-25940-54422 Once created, the sqlite_sequence table
# exists in the sqlite_master table forever; it cannot be dropped.

R-49341-60912-32956-53217-17617-26256-31299-20924 tcl slt th3 src

The schema for the sqlite_sequence table is:

CREATE TABLE sqlite_sequence(name,seq);

/* IMP: R-49341-60912 */
# EVIDENCE-OF: R-49341-60912 The schema for the sqlite_sequence table
# is: CREATE TABLE sqlite_sequence(name,seq);

R-35196-57110-30977-27033-46331-62759-56891-62679 tcl slt th3 src

There is a single row in the sqlite_sequence table for each ordinary table that uses AUTOINCREMENT.

/* IMP: R-35196-57110 */
# EVIDENCE-OF: R-35196-57110 There is a single row in the
# sqlite_sequence table for each ordinary table that uses AUTOINCREMENT.

R-27222-14417-61196-13230-50504-44880-21748-58277 tcl slt th3 src

The name of the table (as it appears in sqlite_master.name) is in the sqlite_sequence.main field and the largest INTEGER PRIMARY KEY ever inserted into that table is in the sqlite_sequence.seq field.

/* IMP: R-27222-14417 */
# EVIDENCE-OF: R-27222-14417 The name of the table (as it appears in
# sqlite_master.name) is in the sqlite_sequence.main field and the
# largest INTEGER PRIMARY KEY ever inserted into that table is in the
# sqlite_sequence.seq field.

R-62267-62884-09796-14012-16099-39965-57946-58351 tcl slt th3 src

New automatically generated integer primary keys for AUTOINCREMENT tables are guaranteed to be larger than the sqlite_sequence.

/* IMP: R-62267-62884 */
# EVIDENCE-OF: R-62267-62884 New automatically generated integer primary
# keys for AUTOINCREMENT tables are guaranteed to be larger than the
# sqlite_sequence.

R-34963-21048-59232-16607-21091-43824-49294-11066 tcl slt th3 src

If the sqlite_sequence.seq field of an AUTOINCREMENT table is already at the largest integer value (9223372036854775807) then attempts to add new rows to that table with an automatically generated integer primary will fail with an SQLITE_FULL error.

/* IMP: R-34963-21048 */
# EVIDENCE-OF: R-34963-21048 If the sqlite_sequence.seq field of an
# AUTOINCREMENT table is already at the largest integer value
# (9223372036854775807) then attempts to add new rows to that table with
# an automatically generated integer primary will fail with an
# SQLITE_FULL error.

R-05088-64670-01812-40293-14899-41323-61587-34551 tcl slt th3 src

The sqlite_sequence.

/* IMP: R-05088-64670 */
# EVIDENCE-OF: R-05088-64670 The sqlite_sequence.

R-56960-03061-20836-21931-13129-13661-26943-05315 tcl slt th3 src

The sqlite_sequence row for an AUTOINCREMENT table is automatically deleted when the table is dropped.

/* IMP: R-56960-03061 */
# EVIDENCE-OF: R-56960-03061 The sqlite_sequence row for an
# AUTOINCREMENT table is automatically deleted when the table is
# dropped.

R-52479-14335-30324-56170-28904-55659-58035-31825 tcl slt th3 src

If the sqlite_sequence row for an AUTOINCREMENT table does not exist when the AUTOINCREMENT table is updated, then a new sqlite_sequence row is created.

/* IMP: R-52479-14335 */
# EVIDENCE-OF: R-52479-14335 If the sqlite_sequence row for an
# AUTOINCREMENT table does not exist when the AUTOINCREMENT table is
# updated, then a new sqlite_sequence row is created.

R-45363-38948-33312-55000-36938-65231-54003-14501 tcl slt th3 src

If the sqlite_sequence.seq value for an AUTOINCREMENT table is manually set to something other than an integer and there is a subsequent attempt to insert the or update the AUTOINCREMENT table, then the behavior is undefined.

/* IMP: R-45363-38948 */
# EVIDENCE-OF: R-45363-38948 If the sqlite_sequence.seq value for an
# AUTOINCREMENT table is manually set to something other than an integer
# and there is a subsequent attempt to insert the or update the
# AUTOINCREMENT table, then the behavior is undefined.

R-38841-33525-61370-32823-20999-44685-25709-53990 tcl slt th3 src

Application code is allowed to modify the sqlite_sequence table, to add new rows, to delete rows, or to modify existing rows.

/* IMP: R-38841-33525 */
# EVIDENCE-OF: R-38841-33525 Application code is allowed to modify the
# sqlite_sequence table, to add new rows, to delete rows, or to modify
# existing rows.

R-59741-51383-54306-63308-23594-36785-40026-55929 tcl slt th3 src

However, application code cannot create the sqlite_sequence table if it does not already exist.

/* IMP: R-59741-51383 */
# EVIDENCE-OF: R-59741-51383 However, application code cannot create the
# sqlite_sequence table if it does not already exist.

R-03478-24050-02080-63717-24763-14107-62865-40248 tcl slt th3 src

Application code can delete all entries from the sqlite_sequence table, but application code cannot drop the sqlite_sequence table.

/* IMP: R-03478-24050 */
# EVIDENCE-OF: R-03478-24050 Application code can delete all entries
# from the sqlite_sequence table, but application code cannot drop the
# sqlite_sequence table.

R-65507-55649-05448-07822-60967-65270-42964-52514 tcl slt th3 src

The sqlite_stat1 is an internal table created by the ANALYZE command and used to hold supplemental information about tables and indexes that the query planner can use to help it find better ways of performing queries.

/* IMP: R-65507-55649 */
# EVIDENCE-OF: R-65507-55649 The sqlite_stat1 is an internal table
# created by the ANALYZE command and used to hold supplemental
# information about tables and indexes that the query planner can use to
# help it find better ways of performing queries.

R-23385-34636-24682-17782-28236-64093-44026-01327 tcl slt th3 src

Applications can update, delete from, insert into or drop the sqlite_stat1 table, but may not create or alter the sqlite_stat1 table.

/* IMP: R-23385-34636 */
# EVIDENCE-OF: R-23385-34636 Applications can update, delete from,
# insert into or drop the sqlite_stat1 table, but may not create or
# alter the sqlite_stat1 table.

R-14413-31567-12478-15952-22976-18615-46799-38246 tcl slt th3 src

The schema of the sqlite_stat1 table is as follows:

CREATE TABLE sqlite_stat1(tbl,idx,stat);

/* IMP: R-14413-31567 */
# EVIDENCE-OF: R-14413-31567 The schema of the sqlite_stat1 table is as
# follows: CREATE TABLE sqlite_stat1(tbl,idx,stat);

R-49983-40731-31772-48546-63558-64382-07908-41217 tcl slt th3 src

There is normally one row per index, with the index identified by the name in the sqlite_stat1.idx column.

/* IMP: R-49983-40731 */
# EVIDENCE-OF: R-49983-40731 There is normally one row per index, with
# the index identified by the name in the sqlite_stat1.idx column.

R-21869-50933-18898-03814-15794-26050-32013-17586 tcl slt th3 src

The sqlite_stat1.tbl column is the name of the table to which the index belongs.

/* IMP: R-21869-50933 */
# EVIDENCE-OF: R-21869-50933 The sqlite_stat1.tbl column is the name of
# the table to which the index belongs.

R-18369-35089-02774-53882-33695-33553-03042-04730 tcl slt th3 src

In each such row, the sqlite_stat.stat column will be a string consisting of a list of integers followed by zero or more arguments.

/* IMP: R-18369-35089 */
# EVIDENCE-OF: R-18369-35089 In each such row, the sqlite_stat.stat
# column will be a string consisting of a list of integers followed by
# zero or more arguments.

R-55292-54599-58036-06496-23806-16073-05204-14716 tcl slt th3 src

The first integer in this list is the approximate number of rows in the index.

/* IMP: R-55292-54599 */
# EVIDENCE-OF: R-55292-54599 The first integer in this list is the
# approximate number of rows in the index.

R-37526-53407-04018-28041-58853-34090-01926-44362 tcl slt th3 src

The second integer is the approximate number of rows in the index that have the same value in the first column of the index.

/* IMP: R-37526-53407 */
# EVIDENCE-OF: R-37526-53407 The second integer is the approximate
# number of rows in the index that have the same value in the first
# column of the index.

R-40949-22892-24068-33862-21333-32882-13770-09171 tcl slt th3 src

The third integer is the number number of rows in the index that have the same value for the first two columns.

/* IMP: R-40949-22892 */
# EVIDENCE-OF: R-40949-22892 The third integer is the number number of
# rows in the index that have the same value for the first two columns.

R-15179-00775-17006-23705-43111-64529-05716-35122 tcl slt th3 src

The N-th integer (for N>1) is the estimated average number of rows in the index which have the same value for the first N-1 columns.

/* IMP: R-15179-00775 */
# EVIDENCE-OF: R-15179-00775 The N-th integer (for N>1) is the
# estimated average number of rows in the index which have the same
# value for the first N-1 columns.

R-57132-64688-04338-28404-47453-24973-03948-35534 tcl slt th3 src

For a K-column index, there will be K+1 integers in the stat column.

/* IMP: R-57132-64688 */
# EVIDENCE-OF: R-57132-64688 For a K-column index, there will be K+1
# integers in the stat column.

R-57360-51191-61413-24528-10483-08494-62999-37912 tcl slt th3 src

If the index is unique, then the last integer will be 1.

/* IMP: R-57360-51191 */
# EVIDENCE-OF: R-57360-51191 If the index is unique, then the last
# integer will be 1.

R-20244-07722-17778-50950-32360-26781-61977-13264 tcl slt th3 src

The list of integers in the stat column can optionally be followed by arguments, each of which is a sequence of non-space characters.

/* IMP: R-20244-07722 */
# EVIDENCE-OF: R-20244-07722 The list of integers in the stat column can
# optionally be followed by arguments, each of which is a sequence of
# non-space characters.

R-30264-64343-18676-16200-46556-14505-34639-51509 tcl slt th3 src

All arguments are preceded by a single space.

/* IMP: R-30264-64343 */
# EVIDENCE-OF: R-30264-64343 All arguments are preceded by a single
# space.

R-13024-05443-37707-54511-29409-48512-10836-53316 tcl slt th3 src

Unrecognized arguments are silently ignored.

/* IMP: R-13024-05443 */
# EVIDENCE-OF: R-13024-05443 Unrecognized arguments are silently
# ignored.

R-16046-57096-05363-52910-04894-60844-28556-45944 tcl slt th3 src

If the "unordered" argument is present, then the query planner assumes that the index is unordered and will not use the index for a range query or for sorting.

th3/cov1/unordered01.test:92

/* IMP: R-16046-57096 */
# EVIDENCE-OF: R-16046-57096 If the "unordered" argument is present,
# then the query planner assumes that the index is unordered and will
# not use the index for a range query or for sorting.

R-01391-64022-44610-46114-55308-14246-64441-26910 tcl slt th3 src

The "sz=NNN" argument (where NNN represents a sequence of 1 or more digits) means that the average row size over all records of the table or index is NNN bytes per row.

/* IMP: R-01391-64022 */
# EVIDENCE-OF: R-01391-64022 The "sz=NNN" argument (where NNN represents
# a sequence of 1 or more digits) means that the average row size over
# all records of the table or index is NNN bytes per row.

R-60344-21573-43141-53224-31246-51025-51087-34354 tcl slt th3 src

The SQLite query planner might use the estimated row size information provided by the "sz=NNN" token to help it choose smaller tables and indexes that require less disk I/O.

th3/cov1/index02.test:42

/* IMP: R-60344-21573 */
# EVIDENCE-OF: R-60344-21573 The SQLite query planner might use the
# estimated row size information provided by the "sz=NNN" token to help
# it choose smaller tables and indexes that require less disk I/O.

R-25832-43294-06509-06689-56001-36083-00062-63837 tcl slt th3 src

The presence of the "noskipscan" token on the sqlite_stat1.stat field of an index prevents that index from being used with the skip-scan optimization.

th3/cov1/where43.test:68

/* IMP: R-25832-43294 */
# EVIDENCE-OF: R-25832-43294 The presence of the "noskipscan" token on
# the sqlite_stat1.stat field of an index prevents that index from being
# used with the skip-scan optimization.

R-64032-17389-14849-50560-10397-51267-05389-65161 tcl slt th3 src

If the sqlite_stat1.idx column is NULL, then the sqlite_stat1.stat column contains a single integer which is the approximate number of rows in the table identified by sqlite_stat1.tbl.

/* IMP: R-64032-17389 */
# EVIDENCE-OF: R-64032-17389 If the sqlite_stat1.idx column is NULL,
# then the sqlite_stat1.stat column contains a single integer which is
# the approximate number of rows in the table identified by
# sqlite_stat1.tbl.

R-36765-11966-40319-64990-44757-09945-39108-37525 tcl slt th3 src

If the sqlite_stat1.idx column is the same as the sqlite_stat1.tbl column, then the table is a WITHOUT ROWID table and the sqlite_stat1.stat field contains information about the index btree that implements the WITHOUT ROWID table.

/* IMP: R-36765-11966 */
# EVIDENCE-OF: R-36765-11966 If the sqlite_stat1.idx column is the same
# as the sqlite_stat1.tbl column, then the table is a WITHOUT ROWID
# table and the sqlite_stat1.stat field contains information about the
# index btree that implements the WITHOUT ROWID table.

R-01646-09830-46955-17168-41197-07539-61822-63814 tcl slt th3 src

The schema of the sqlite_stat3 table is as follows:

CREATE TABLE sqlite_stat3(tbl,idx,nEq,nLt,nDLt,sample);

/* IMP: R-01646-09830 */
# EVIDENCE-OF: R-01646-09830 The schema of the sqlite_stat3 table is as
# follows: CREATE TABLE sqlite_stat3(tbl,idx,nEq,nLt,nDLt,sample);

R-05723-45863-34375-13823-48994-32413-55048-46379 tcl slt th3 src

The sqlite_stat3.sample column holds the value of the left-most field of an index identified by sqlite_stat3.idx and sqlite_stat3.tbl.

/* IMP: R-05723-45863 */
# EVIDENCE-OF: R-05723-45863 The sqlite_stat3.sample column holds the
# value of the left-most field of an index identified by
# sqlite_stat3.idx and sqlite_stat3.tbl.

R-46076-42323-06794-05495-00937-01913-04786-13791 tcl slt th3 src

The sqlite_stat3.nEq column holds the approximate number of entries in the index whose left-most column exactly matches the sample.

/* IMP: R-46076-42323 */
# EVIDENCE-OF: R-46076-42323 The sqlite_stat3.nEq column holds the
# approximate number of entries in the index whose left-most column
# exactly matches the sample.

R-36103-43483-56354-57798-31615-25926-09784-32512 tcl slt th3 src

The sqlite_stat3.nLt holds the approximate number of entries in the index whose left-most column is less than the sample.

/* IMP: R-36103-43483 */
# EVIDENCE-OF: R-36103-43483 The sqlite_stat3.nLt holds the approximate
# number of entries in the index whose left-most column is less than the
# sample.

R-10190-16965-26709-52634-48294-10827-43315-62793 tcl slt th3 src

The sqlite_stat3.nDLt column holds the approximate number of distinct left-most entries in the index that are less than the sample.

/* IMP: R-10190-16965 */
# EVIDENCE-OF: R-10190-16965 The sqlite_stat3.nDLt column holds the
# approximate number of distinct left-most entries in the index that are
# less than the sample.

R-32575-52606-04029-38072-09330-12543-11814-27885 tcl slt th3 src

There can be an arbitrary number of sqlite_stat3 entries per index.

/* IMP: R-32575-52606 */
# EVIDENCE-OF: R-32575-52606 There can be an arbitrary number of
# sqlite_stat3 entries per index.

R-37367-57340-65398-65389-19439-49960-45341-59090 tcl slt th3 src

In a well-formed sqlite_stat3 table, the samples for any single index must appear in the same order that they occur in the index. In other words, if the entry with left-most column S1 is earlier in the index b-tree than the entry with left-most column S2, then in the sqlite_stat3 table, sample S1 must have a smaller rowid than sample S2.

/* IMP: R-37367-57340 */
# EVIDENCE-OF: R-37367-57340 In a well-formed sqlite_stat3 table, the
# samples for any single index must appear in the same order that they
# occur in the index. In other words, if the entry with left-most column
# S1 is earlier in the index b-tree than the entry with left-most column
# S2, then in the sqlite_stat3 table, sample S1 must have a smaller
# rowid than sample S2.

R-04670-35394-33055-58940-18775-32578-18606-16346 tcl slt th3 src

The schema of the sqlite_stat4 table is as follows:

CREATE TABLE sqlite_stat4(tbl,idx,nEq,nLt,nDLt,sample);

/* IMP: R-04670-35394 */
# EVIDENCE-OF: R-04670-35394 The schema of the sqlite_stat4 table is as
# follows: CREATE TABLE sqlite_stat4(tbl,idx,nEq,nLt,nDLt,sample);

R-24896-34661-16790-51948-60772-09392-38798-62711 tcl slt th3 src

The sqlite_stat4.tbl column holds name of the table that owns the index that the row describes

/* IMP: R-24896-34661 */
# EVIDENCE-OF: R-24896-34661 The sqlite_stat4.tbl column holds name of
# the table that owns the index that the row describes

R-32329-22225-62429-19871-19916-05643-31400-56048 tcl slt th3 src

The sqlite_stat4.idx column holds name of the index that the row describes, or in the case of an sqlite_stat4 entry for a WITHOUT ROWID table, the name of the table itself.

/* IMP: R-32329-22225 */
# EVIDENCE-OF: R-32329-22225 The sqlite_stat4.idx column holds name of
# the index that the row describes, or in the case of an sqlite_stat4
# entry for a WITHOUT ROWID table, the name of the table itself.

R-65114-09470-57981-35509-40409-30231-64456-31746 tcl slt th3 src

The sqlite_stat4.sample column holds a BLOB in the record format that encodes the indexed columns followed by the rowid for a rowid table or by the columns of the primary key for a WITHOUT ROWID table.

/* IMP: R-65114-09470 */
# EVIDENCE-OF: R-65114-09470 The sqlite_stat4.sample column holds a BLOB
# in the record format that encodes the indexed columns followed by the
# rowid for a rowid table or by the columns of the primary key for a
# WITHOUT ROWID table.

R-01461-33534-36548-25495-56547-17079-32518-24565 tcl slt th3 src

The sqlite_stat4.sample BLOB for the WITHOUT ROWID table itself contains just the columns of the primary key.

/* IMP: R-01461-33534 */
# EVIDENCE-OF: R-01461-33534 The sqlite_stat4.sample BLOB for the
# WITHOUT ROWID table itself contains just the columns of the primary
# key.

R-29187-47463-37855-25397-01259-03141-56758-63159 tcl slt th3 src

For indexes on an ordinary rowid table, N will be one more than the number of columns indexed.

/* IMP: R-29187-47463 */
# EVIDENCE-OF: R-29187-47463 For indexes on an ordinary rowid table, N
# will be one more than the number of columns indexed.

R-14939-58295-46667-49876-44919-10267-12133-09440 tcl slt th3 src

For indexes on WITHOUT ROWID tables, N will be the number of columns indexed plus the number of columns in the primary key.

/* IMP: R-14939-58295 */
# EVIDENCE-OF: R-14939-58295 For indexes on WITHOUT ROWID tables, N will
# be the number of columns indexed plus the number of columns in the
# primary key.

R-35036-36587-25670-53930-46391-17517-28623-46452 tcl slt th3 src

For a WITHOUT ROWID table, N will be the number of columns in the primary key.

/* IMP: R-35036-36587 */
# EVIDENCE-OF: R-35036-36587 For a WITHOUT ROWID table, N will be the
# number of columns in the primary key.

R-33599-53132-08850-48831-58699-38998-10150-06398 tcl slt th3 src

The sqlite_stat4.nEq column holds a list of N integers where the K-th integer is the approximate number of entries in the index whose left-most K columns exactly match the K left-most columns of the sample.

/* IMP: R-33599-53132 */
# EVIDENCE-OF: R-33599-53132 The sqlite_stat4.nEq column holds a list of
# N integers where the K-th integer is the approximate number of entries
# in the index whose left-most K columns exactly match the K left-most
# columns of the sample.

R-41719-33203-52232-61281-45744-11728-08852-57887 tcl slt th3 src

The sqlite_stat4.nLt column holds a list of N integers where the K-th integer is the approximate number of entries in the index whose K left-most columns are collectively less than the K left-most columns of the sample.

/* IMP: R-41719-33203 */
# EVIDENCE-OF: R-41719-33203 The sqlite_stat4.nLt column holds a list of
# N integers where the K-th integer is the approximate number of entries
# in the index whose K left-most columns are collectively less than the
# K left-most columns of the sample.

R-01665-16529-46232-20487-31256-22068-50948-28475 tcl slt th3 src

The sqlite_stat4.nDLt column holds a list of N integers where the K-th integer is the approximate number of entries in the index that are distinct in the first K columns and where the left-most K columns are collectively less than the left-most K columns of the sample.

/* IMP: R-01665-16529 */
# EVIDENCE-OF: R-01665-16529 The sqlite_stat4.nDLt column holds a list
# of N integers where the K-th integer is the approximate number of
# entries in the index that are distinct in the first K columns and
# where the left-most K columns are collectively less than the left-most
# K columns of the sample.

R-11802-27774-09561-41384-21841-59026-14543-36401 tcl slt th3 src

There can be an arbitrary number of sqlite_stat4 entries per index.

/* IMP: R-11802-27774 */
# EVIDENCE-OF: R-11802-27774 There can be an arbitrary number of
# sqlite_stat4 entries per index.

R-18509-21923-06285-21395-08287-44781-54571-62637 tcl slt th3 src

In a well-formed sqlite_stat4 table, the samples for any single index must appear in the same order that they occur in the index. In other words, if entry S1 is earlier in the index b-tree than entry S2, then in the sqlite_stat4 table, sample S1 must have a smaller rowid than sample S2.

/* IMP: R-18509-21923 */
# EVIDENCE-OF: R-18509-21923 In a well-formed sqlite_stat4 table, the
# samples for any single index must appear in the same order that they
# occur in the index. In other words, if entry S1 is earlier in the
# index b-tree than entry S2, then in the sqlite_stat4 table, sample S1
# must have a smaller rowid than sample S2.

R-20609-23860-31620-63775-42539-11534-17004-18607 tcl slt th3 src

The rollback journal file is always located in the same directory as the database file and has the same name as the database file but with the string "-journal" appended.

/* IMP: R-20609-23860 */
# EVIDENCE-OF: R-20609-23860 The rollback journal file is always located
# in the same directory as the database file and has the same name as
# the database file but with the string "-journal" appended.

R-17628-54414-54605-31939-57279-20393-13350-52356 tcl slt th3 src

The next time SQLite attempts to open the database file, the presence of the rollback journal file will be detected and the journal will be automatically played back to restore the database to its state at the start of the incomplete transaction.

/* IMP: R-17628-54414 */
# EVIDENCE-OF: R-17628-54414 The next time SQLite attempts to open the
# database file, the presence of the rollback journal file will be
# detected and the journal will be automatically played back to restore
# the database to its state at the start of the incomplete transaction.

R-48191-50425-24568-05066-49812-07872-47674-23180 tcl slt th3 src

A rollback journal is only considered to be valid if it exists and contains a valid header.

/* IMP: R-48191-50425 */
# EVIDENCE-OF: R-48191-50425 A rollback journal is only considered to be
# valid if it exists and contains a valid header.

R-10900-57544-37938-07934-32518-44927-61812-27434 tcl slt th3 src

The rollback journal file can be deleted

/* IMP: R-10900-57544 */
# EVIDENCE-OF: R-10900-57544 The rollback journal file can be deleted

R-23640-47263-12341-60168-43783-53275-58204-18225 tcl slt th3 src

The rollback journal file can be truncated to zero length

/* IMP: R-23640-47263 */
# EVIDENCE-OF: R-23640-47263 The rollback journal file can be truncated
# to zero length

R-47436-38935-17734-42324-42347-43578-49292-42564 tcl slt th3 src

The header of the rollback journal can be overwritten with invalid header text (for example, all zeros).

/* IMP: R-47436-38935 */
# EVIDENCE-OF: R-47436-38935 The header of the rollback journal can be
# overwritten with invalid header text (for example, all zeros).

R-41795-40039-43827-16838-58364-41518-15809-62342 tcl slt th3 src

These three ways of committing a transaction correspond to the DELETE, TRUNCATE, and PERSIST settings, respectively, of the journal_mode pragma.

/* IMP: R-41795-40039 */
# EVIDENCE-OF: R-41795-40039 These three ways of committing a
# transaction correspond to the DELETE, TRUNCATE, and PERSIST settings,
# respectively, of the journal_mode pragma.

R-48780-51154-29045-33846-29685-59485-21134-40329 tcl slt th3 src

A rollback journal header is padded with zeros out to the size of a single sector (as defined by the sector size integer at offset 20).

/* IMP: R-48780-51154 */
# EVIDENCE-OF: R-48780-51154 A rollback journal header is padded with
# zeros out to the size of a single sector (as defined by the sector
# size integer at offset 20).

R-31305-35170-11464-64098-34506-16219-33057-18056 tcl slt th3 src

After the header and zero padding are zero or more page records.

/* IMP: R-31305-35170 */
# EVIDENCE-OF: R-31305-35170 After the header and zero padding are zero
# or more page records.

R-06044-07843-41488-27430-15484-51013-09491-52143 tcl slt th3 src

Each page record stores a copy of the content of a page from the database file before it was changed.

/* IMP: R-06044-07843 */
# EVIDENCE-OF: R-06044-07843 Each page record stores a copy of the
# content of a page from the database file before it was changed.

R-57428-63758-17863-58074-13884-12302-03924-53063 tcl slt th3 src

The same page may not appear more than once within a single rollback journal.

/* IMP: R-57428-63758 */
# EVIDENCE-OF: R-57428-63758 The same page may not appear more than once
# within a single rollback journal.

R-43646-40507-46737-61854-04250-53447-25357-32059 tcl slt th3 src

The checksum is an unsigned 32-bit integer computed as follows:

  1. Initialize the checksum to the checksum nonce value found in the journal header at offset 12.
  2. Initialize index X to be N-200 (where N is the size of a database page in bytes.
  3. Interpret the byte at offset X into the page as an 8-bit unsigned integer and add the value of that integer to the checksum.
  4. Subtract 200 from X.
  5. If X is greater than or equal to zero, go back to step 3.

/* IMP: R-43646-40507 */
# EVIDENCE-OF: R-43646-40507 The checksum is an unsigned 32-bit integer
# computed as follows: Initialize the checksum to the checksum nonce
# value found in the journal header at offset 12. Initialize index X to
# be N-200 (where N is the size of a database page in bytes. Interpret
# the byte at offset X into the page as an 8-bit unsigned integer and
# add the value of that integer to the checksum. Subtract 200 from X. If
# X is greater than or equal to zero, go back to step 3.

R-21228-58399-13607-54712-08787-36573-11741-23051 tcl slt th3 src

If M is greater than zero then after M page records the journal file may be zero padded out to the next multiple of the sector size and another journal header may be inserted.

/* IMP: R-21228-58399 */
# EVIDENCE-OF: R-21228-58399 If M is greater than zero then after M page
# records the journal file may be zero padded out to the next multiple
# of the sector size and another journal header may be inserted.

R-02823-24537-56381-41482-05439-43543-17760-52541 tcl slt th3 src

All journal headers within the same journal must contain the same database page size and sector size.

/* IMP: R-02823-24537 */
# EVIDENCE-OF: R-02823-24537 All journal headers within the same journal
# must contain the same database page size and sector size.

R-39073-45389-04336-35554-15796-29115-57398-44032 tcl slt th3 src

If M is -1 in the initial journal header, then the number of page records that follow is computed by computing how many page records will fit in the available space of the remainder of the journal file.

/* IMP: R-39073-45389 */
# EVIDENCE-OF: R-39073-45389 If M is -1 in the initial journal header,
# then the number of page records that follow is computed by computing
# how many page records will fit in the available space of the remainder
# of the journal file.

R-47674-04435-43323-49671-63294-37647-53866-33040 tcl slt th3 src

When a database is in WAL mode, all connections to that database must use the WAL.

/* IMP: R-47674-04435 */
# EVIDENCE-OF: R-47674-04435 When a database is in WAL mode, all
# connections to that database must use the WAL.

R-17596-52174-25214-41225-30917-27450-40075-28590 tcl slt th3 src

A particular database will use either a rollback journal or a WAL, but not both at the same time.

/* IMP: R-17596-52174 */
# EVIDENCE-OF: R-17596-52174 A particular database will use either a
# rollback journal or a WAL, but not both at the same time.

R-64813-58505-03479-11903-29047-19194-00816-28075 tcl slt th3 src

The WAL is always located in the same directory as the database file and has the same name as the database file but with the string "-wal" appended.

/* IMP: R-64813-58505 */
# EVIDENCE-OF: R-64813-58505 The WAL is always located in the same
# directory as the database file and has the same name as the database
# file but with the string "-wal" appended.

R-45441-43537-47777-63556-12740-30003-58169-32879 tcl slt th3 src

A single WAL can and usually does record multiple transactions.

/* IMP: R-45441-43537 */
# EVIDENCE-OF: R-45441-43537 A single WAL can and usually does record
# multiple transactions.

R-06211-52866-60944-51344-33653-00695-35099-44728 tcl slt th3 src

A single WAL file can be reused multiple times.

/* IMP: R-06211-52866 */
# EVIDENCE-OF: R-06211-52866 A single WAL file can be reused multiple
# times.

R-65347-63411-45850-56512-24851-44706-34523-61535 tcl slt th3 src

In other words, the WAL can fill up with frames and then be checkpointed and then new frames can overwrite the old ones.

/* IMP: R-65347-63411 */
# EVIDENCE-OF: R-65347-63411 In other words, the WAL can fill up with
# frames and then be checkpointed and then new frames can overwrite the
# old ones.

R-05644-53956-28885-25038-62165-04272-38392-10037 tcl slt th3 src

A WAL always grows from beginning toward the end.

/* IMP: R-05644-53956 */
# EVIDENCE-OF: R-05644-53956 A WAL always grows from beginning toward
# the end.

R-30687-15230-29813-38800-06728-37939-57957-31201 tcl slt th3 src

The WAL header is 32 bytes in size and consists of the following eight big-endian 32-bit unsigned integer values:

WAL Header Format
OffsetSizeDescription
04 Magic number. 0x377f0682 or 0x377f0683
44 File format version. Currently 3007000.
84 Database page size. Example: 1024
124 Checkpoint sequence number
164 Salt-1: random integer incremented with each checkpoint
204 Salt-2: a different random number for each checkpoint
244 Checksum-1: First part of a checksum on the first 24 bytes of header
284 Checksum-2: Second part of the checksum on the first 24 bytes of header

/* IMP: R-30687-15230 */
# EVIDENCE-OF: R-30687-15230 The WAL header is 32 bytes in size and
# consists of the following eight big-endian 32-bit unsigned integer
# values: WAL Header Format OffsetSizeDescription 04 Magic number.
# 0x377f0682 or 0x377f0683 44 File format version. Currently 3007000. 84
# Database page size. Example: 1024 124 Checkpoint sequence number 164
# Salt-1: random integer incremented with each checkpoint 204 Salt-2: a
# different random number for each checkpoint 244 Checksum-1: First part
# of a checksum on the first 24 bytes of header 284 Checksum-2: Second
# part of the checksum on the first 24 bytes of header

R-17287-60737-33985-12645-22938-14534-06595-16406 tcl slt th3 src

Immediately following the wal-header are zero or more frames.

/* IMP: R-17287-60737 */
# EVIDENCE-OF: R-17287-60737 Immediately following the wal-header are
# zero or more frames.

R-17719-64233-56665-24727-28694-41697-48581-47600 tcl slt th3 src

Each frame consists of a 24-byte frame-header followed by a page-size bytes of page data.

/* IMP: R-17719-64233 */
# EVIDENCE-OF: R-17719-64233 Each frame consists of a 24-byte
# frame-header followed by a page-size bytes of page data.

R-24411-60193-43052-59187-49935-26782-54329-59494 tcl slt th3 src

The frame-header is six big-endian 32-bit unsigned integer values, as follows:

WAL Frame Header Format
OffsetSizeDescription
04 Page number
44 For commit records, the size of the database file in pages after the commit. For all other records, zero.
84 Salt-1 copied from the WAL header
124 Salt-2 copied from the WAL header
164 Checksum-1: Cumulative checksum up through and including this page
204 Checksum-2: Second half of the cumulative checksum.

/* IMP: R-24411-60193 */
# EVIDENCE-OF: R-24411-60193 The frame-header is six big-endian 32-bit
# unsigned integer values, as follows: WAL Frame Header Format
# OffsetSizeDescription 04 Page number 44 For commit records, the size
# of the database file in pages after the commit. For all other records,
# zero. 84 Salt-1 copied from the WAL header 124 Salt-2 copied from the
# WAL header 164 Checksum-1: Cumulative checksum up through and
# including this page 204 Checksum-2: Second half of the cumulative
# checksum.

R-38815-48046-28937-36081-10893-02265-15426-25349 tcl slt th3 src

A frame is considered valid if and only if the following conditions are true:

  1. The salt-1 and salt-2 values in the frame-header match salt values in the wal-header

  2. The checksum values in the final 8 bytes of the frame-header exactly match the checksum computed consecutively on the first 24 bytes of the WAL header and the first 8 bytes and the content of all frames up to and including the current frame.

/* IMP: R-38815-48046 */
# EVIDENCE-OF: R-38815-48046 A frame is considered valid if and only if
# the following conditions are true: The salt-1 and salt-2 values in the
# frame-header match salt values in the wal-header The checksum values
# in the final 8 bytes of the frame-header exactly match the checksum
# computed consecutively on the first 24 bytes of the WAL header and the
# first 8 bytes and the content of all frames up to and including the
# current frame.

R-31248-24630-37475-28758-64215-56604-24000-32221 tcl slt th3 src

The 32-bit integers are big-endian if the magic number in the first 4 bytes of the WAL header is 0x377f0683 and the integers are little-endian if the magic number is 0x377f0682.

/* IMP: R-31248-24630 */
# EVIDENCE-OF: R-31248-24630 The 32-bit integers are big-endian if the
# magic number in the first 4 bytes of the WAL header is 0x377f0683 and
# the integers are little-endian if the magic number is 0x377f0682.

R-46413-56591-19010-63234-29028-02870-41994-21141 tcl slt th3 src

The checksum values are always stored in the frame header in a big-endian format regardless of which byte order is used to compute the checksum.

/* IMP: R-46413-56591 */
# EVIDENCE-OF: R-46413-56591 The checksum values are always stored in
# the frame header in a big-endian format regardless of which byte order
# is used to compute the checksum.

R-54914-35280-25250-15027-11750-01727-31556-09932 tcl slt th3 src

The checksum algorithm is as follows:

 
s0 = s1 = 0
for i from 0 to n-1 step 2:
   s0 += x(i) + s1;
   s1 += x(i+1) + s0;
endfor
# result in s0 and s1

/* IMP: R-54914-35280 */
# EVIDENCE-OF: R-54914-35280 The checksum algorithm is as follows: s0 =
# s1 = 0 for i from 0 to n-1 step 2: s0 += x(i) + s1; s1 += x(i+1) + s0;
# endfor # result in s0 and s1

R-63672-25227-17465-07864-20680-40543-20473-50716 tcl slt th3 src

The outputs s0 and s1 are both weighted checksums using Fibonacci weights in reverse order.

/* IMP: R-63672-25227 */
# EVIDENCE-OF: R-63672-25227 The outputs s0 and s1 are both weighted
# checksums using Fibonacci weights in reverse order.

R-06681-36915-07445-41328-35159-18817-58464-46967 tcl slt th3 src

The largest Fibonacci weight occurs on the first element of the sequence being summed.

/* IMP: R-06681-36915 */
# EVIDENCE-OF: R-06681-36915 The largest Fibonacci weight occurs on the
# first element of the sequence being summed.

R-39089-10219-02970-26628-53332-63736-48500-08755 tcl slt th3 src

The s1 value spans all 32-bit integer terms of the sequence whereas s0 omits the final term.

/* IMP: R-39089-10219 */
# EVIDENCE-OF: R-39089-10219 The s1 value spans all 32-bit integer terms
# of the sequence whereas s0 omits the final term.

R-64586-34038-50551-35162-29949-64214-50082-35974 tcl slt th3 src

On a checkpoint, the WAL is first flushed to persistent storage using the xSync method of the VFS.

/* IMP: R-64586-34038 */
# EVIDENCE-OF: R-64586-34038 On a checkpoint, the WAL is first flushed
# to persistent storage using the xSync method of the VFS.

R-52064-29026-64235-14547-56898-38639-53411-40731 tcl slt th3 src

Then valid content of the WAL is transferred into the database file.

/* IMP: R-52064-29026 */
# EVIDENCE-OF: R-52064-29026 Then valid content of the WAL is
# transferred into the database file.

R-09385-24237-02595-13081-61892-09820-41014-64589 tcl slt th3 src

Finally, the database is flushed to persistent storage using another xSync method call.

/* IMP: R-09385-24237 */
# EVIDENCE-OF: R-09385-24237 Finally, the database is flushed to
# persistent storage using another xSync method call.

R-29813-27283-26120-20088-53689-36454-45951-57520 tcl slt th3 src

After a checkpoint, new write transactions overwrite the WAL file from the beginning.

/* IMP: R-29813-27283 */
# EVIDENCE-OF: R-29813-27283 After a checkpoint, new write transactions
# overwrite the WAL file from the beginning.

R-49321-63177-63913-58265-32915-52148-26230-28252 tcl slt th3 src

At the start of the first new write transaction, the WAL header salt-1 value is incremented and the salt-2 value is randomized.

/* IMP: R-49321-63177 */
# EVIDENCE-OF: R-49321-63177 At the start of the first new write
# transaction, the WAL header salt-1 value is incremented and the salt-2
# value is randomized.

R-63827-07147-50106-23079-54369-23240-21585-39525 tcl slt th3 src

To read a page from the database (call it page number P), a reader first checks the WAL to see if it contains page P. If so, then the last valid instance of page P that is followed by a commit frame or is a commit frame itself becomes the value read.

/* IMP: R-63827-07147 */
# EVIDENCE-OF: R-63827-07147 To read a page from the database (call it
# page number P), a reader first checks the WAL to see if it contains
# page P. If so, then the last valid instance of page P that is followed
# by a commit frame or is a commit frame itself becomes the value read.

R-17503-25110-05842-17616-27745-25701-57748-55330 tcl slt th3 src

If the WAL contains no copies of page P that are valid and which are a commit frame or are followed by a commit frame, then page P is read from the database file.

/* IMP: R-17503-25110 */
# EVIDENCE-OF: R-17503-25110 If the WAL contains no copies of page P
# that are valid and which are a commit frame or are followed by a
# commit frame, then page P is read from the database file.

R-51343-23764-31964-03774-37293-59817-59078-38155 tcl slt th3 src

This technique allows multiple concurrent readers to view different versions of the database content simultaneously.

/* IMP: R-51343-23764 */
# EVIDENCE-OF: R-51343-23764 This technique allows multiple concurrent
# readers to view different versions of the database content
# simultaneously.

R-56020-12355-36965-55890-52401-50899-40834-02969 tcl slt th3 src

To overcome this problem, a separate data structure called the wal-index is maintained to expedite the search for frames of a particular page.

/* IMP: R-56020-12355 */
# EVIDENCE-OF: R-56020-12355 To overcome this problem, a separate data
# structure called the wal-index is maintained to expedite the search
# for frames of a particular page.

R-52133-27554-45735-12827-00228-57600-39512-14706 tcl slt th3 src

The mmapped file is in the same directory as the database and has the same name as the database with a "-shm" suffix appended.

/* IMP: R-52133-27554 */
# EVIDENCE-OF: R-52133-27554 The mmapped file is in the same directory
# as the database and has the same name as the database with a "-shm"
# suffix appended.

R-38260-37719-07465-52549-18983-56456-60761-04524 tcl slt th3 src

The VFS is required to either truncate or zero the header of the wal-index when the last connection to it closes.

/* IMP: R-38260-37719 */
# EVIDENCE-OF: R-38260-37719 The VFS is required to either truncate or
# zero the header of the wal-index when the last connection to it
# closes.