Documentation Source Text

Check-in [e1a02f28ad]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:File format and file I/O documents converted to read their requirements text out of the requirements TXT files. Requirements numbers are shifted up into the 30000 range.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: e1a02f28add1e8953d3abc1656d219c9ec625271
User & Date: drh 2008-12-10 00:47:40.000
Context
2008-12-10
00:49
Check in the Sony logo GIF. (check-in: 3472b0873a user: drh tags: trunk)
00:47
File format and file I/O documents converted to read their requirements text out of the requirements TXT files. Requirements numbers are shifted up into the 30000 range. (check-in: e1a02f28ad user: drh tags: trunk)
2008-12-09
22:49
Break out the requirements text into separate ASCII text files in the req/ folder. Requirements are now in a format suitable for use with qabot. Requirements information is no longer automatically extracted from sqlite3.h or fileformat.in. Changes to requirements must be manually entered. (check-in: e76f948793 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/fileformat.in.
13
14
15
16
17
18
19
















20
21
22
23
24
25
26
  <b>Javascript is required for some features of this document, including 
     table of contents, figure numbering and internal references (section
     numbers and hyper-links.
  </b>
</div>
<!-- End of standard rt docs header -->

















<h1>Document Overview</h1>

  <h2>Scope and Purpose</h2>

  <p>
    This document is designed to serve two purposes:
  <ul>







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
  <b>Javascript is required for some features of this document, including 
     table of contents, figure numbering and internal references (section
     numbers and hyper-links.
  </b>
</div>
<!-- End of standard rt docs header -->

<tcl>
###############################################################################
# The actual text of requirments is stored in ../req/hlr30000.txt.  During
# the process in which this document is converted into HTML, TCL script runs
# and imports requirements from that file over into this file whenever you
# see:
#            <t*l>fileformat_import_requirement H00000</t*l>
#
unset -nocomplain ffreq
hd_read_requirement_file $::DOC/req/hlr30000.txt ffreq
proc fileformat_import_requirement {reqid} {
  hd_resolve [lindex $::ffreq($reqid) 1]
}
###############################################################################
</tcl>

<h1>Document Overview</h1>

  <h2>Scope and Purpose</h2>

  <p>
    This document is designed to serve two purposes:
  <ul>
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
<h1 id=sqlite_database_files>SQLite Database Files</h1>
 
  <p>
    The bulk of this document, section <cite>database_file_format</cite>,
    contains the definition of a <i>well-formed SQLite database file</i>.
    SQLite is required to create database files that meet this definition.

  <p class=req id=H20001>
    The system shall ensure that at the successful conclusion of a
    database transaction the contents of the database file constitute
    a <i>well-formed SQLite database file</i>.

  <p>
    Additionally, the database file should contain a serialized version
    of the logical database produced by the transaction. For all but the
    most trivial logical databases, there are many possible serial 
    representations.

  <p class=req id=H20002>
    The system shall ensure that at the successful conclusion of a
    database transaction the contents of the database file are a valid
    serialization of the contents of the logical SQL database produced
    by the transaction.

  <p>
    Section <cite>database_file_manipulation</cite> contains requirements
    describing in more detail the way in which SQLite manipulates the
    fields and data structures described in section
    <cite>database_file_format</cite> under various circumstances. These
    requirements are to a certain extent derived from the requirements 







|
|
<
<







|
|
<
<
<







283
284
285
286
287
288
289
290
291


292
293
294
295
296
297
298
299
300



301
302
303
304
305
306
307
<h1 id=sqlite_database_files>SQLite Database Files</h1>
 
  <p>
    The bulk of this document, section <cite>database_file_format</cite>,
    contains the definition of a <i>well-formed SQLite database file</i>.
    SQLite is required to create database files that meet this definition.

  <p class=req id=H30010>
          <tcl>fileformat_import_requirement H30010</tcl>



  <p>
    Additionally, the database file should contain a serialized version
    of the logical database produced by the transaction. For all but the
    most trivial logical databases, there are many possible serial 
    representations.

  <p class=req id=H30020>
          <tcl>fileformat_import_requirement H30020</tcl>




  <p>
    Section <cite>database_file_manipulation</cite> contains requirements
    describing in more detail the way in which SQLite manipulates the
    fields and data structures described in section
    <cite>database_file_format</cite> under various circumstances. These
    requirements are to a certain extent derived from the requirements 
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615

616
617
618

619
620
621
622

623
624
625
626
627
628
629
630
631

632
633
634
635

636
637
638
639

640
641
642
643
644
645
646
647
648
649
650
651
652
653
654

655
656
657
658
659

660
661
662
663
664
665
666
667
668
669
670

671
672
673
674
675
676
677
678
679
680

681
682
683
684
685
686

687
688
689
690
691
692
693
	Some of the following requirements state that certain database header
        fields must contain defined constant values, even though the sqlite 
        database file format is designed to allow various values. This is
        done to artificially constrain the definition of a 
        <i>well-formed database</i> in order to make implementation and 
        testing more practical.

      <p class=req id=H20003>
	The first 16 bytes of a well-formed database file contain the UTF-8
        encoding of the string "SQLite format 3" followed by a single 
        nul-terminator byte.

      <p>
        Following the 16 byte magic string in the file header is the
	<i>page size</i>, a 2-byte field. See section
        <cite>pages_and_page_types</cite> for details.

      <p class=req id=H20004>
	The 19th byte (byte offset 18), the <i>file-format write version</i>, 
        of a well-formed database file contains the value 0x01.

      <p class=req id=H20005>
	The 20th byte (byte offset 19), the <i>file-format read version</i>, 
        of a well-formed database file contains the value 0x01.


      <p class=req id=H20006>
	The 21st byte (byte offset 20), the number of unused bytes on each
        page, of a well-formed database file shall contain the value 0x00.


      <p class=req id=H20007>
	The 22nd byte (byte offset 21), the maximum fraction of an index 
        B-Tree page to use for embedded content, of a well-formed database 
        file shall contain the value 0x40.
      <p class=req id=H20008>
	The 23rd byte (byte offset 22), the minimum fraction of an index 
        B-Tree page to use for embedded content when using overflow pages,
        of a well-formed database file contains the value 0x20.

      <p class=req id=H20009>
	The 24th byte (byte offset 23), the minimum fraction of a table
        B-Tree page to use for embedded content when using overflow pages,
        of a well-formed database file contains the value 0x20.

      <p class=req id=H20010>
        The 4 byte block starting at byte offset 24 of a well-formed 
        database file contains the <i>file change counter</i> formatted
        as a 4-byte big-endian integer.


      <p>
        Following the <i>file change counter</i> in the database header are
        two 4-byte fields related to the database file <i>free page list</i>.
        See section <cite>free_page_list</cite> for details.

      <p class=req id=H20011>
        The 4 byte block starting at byte offset 40 of a well-formed 
        database file contains the <i>schema version</i> formatted
        as a 4-byte big-endian integer.

      <p class=req id=H20012>
	The 4 byte block starting at byte offset 44 of a well-formed
        database file, the <i>schema layer file format</i>, contains a 
        big-endian integer value between 1 and 4, inclusive.


      <p class=req id=H20013>
        The 4 byte block starting at byte offset 48 of a well-formed 
        database file contains the <i>default pager cache size</i> formatted
        as a 4-byte big-endian integer.


      <p class=req id=H20014>
        The 4 byte block starting at byte offset 52 of a well-formed 
        database file contains the <i>auto-vacuum last root-page</i> 
        formatted as a 4-byte big-endian integer. If this value is non-zero,
        the database is said to be an <i>auto-vacuum database</i>.

      <p class=req id=H20015>
	The 4 byte block starting at byte offset 56 of a well-formed
        database file, the <i>text encoding</i> contains a big-endian integer 
        value between 1 and 3, inclusive.


      <p class=req id=H20016>
        The 4 byte block starting at byte offset 60 of a well-formed 
        database file contains the <i>user cookie</i> formatted
        as a 4-byte big-endian integer.

      <p class=req id=H20017>
	The 4 byte block starting at byte offset 64 of a well-formed
        database file, the <i>incremental vaccum flag</i> contains a big-endian 
        integer value between 0 and 1, inclusive.


      <p class=req id=H20018>
	In a well-formed non-autovacuum database (one with a zero stored
        in the 4-byte big-endian integer value beginning at byte offset
        52 of the database file header, the incremental vacuum flag is
        set to 0.


    <h3 id="pages_and_page_types">Pages and Page Types</h3>
      <p>
        The entire database file is divided into pages, each page consisting
        of <i>page-size</i> bytes, where <i>page-size</i> is the 2-byte 
        integer value stored at offset 16 of the file header (see above).
        The <i>page-size</i> is always a power of two between 512 







|
|
<
<






|
<
<
>
|
<
<
>

|
<
<
>

|
<
<
|
|
<
<
<
>
|
<
<
<
>
|
<
<
<
>






|
<
|
<

|
<
<
<
>

|
<
<
<
>

|
<
<
|
<

|
<
<
<
>

|
<
|
<

|
<
<
<
>

|
<
<
<
<
>







607
608
609
610
611
612
613
614
615


616
617
618
619
620
621
622


623
624


625
626
627


628
629
630


631
632



633
634



635
636



637
638
639
640
641
642
643
644

645

646
647



648
649
650



651
652
653


654

655
656



657
658
659

660

661
662



663
664
665




666
667
668
669
670
671
672
673
	Some of the following requirements state that certain database header
        fields must contain defined constant values, even though the sqlite 
        database file format is designed to allow various values. This is
        done to artificially constrain the definition of a 
        <i>well-formed database</i> in order to make implementation and 
        testing more practical.

      <p class=req id=H30030>
          <tcl>fileformat_import_requirement H30030</tcl>



      <p>
        Following the 16 byte magic string in the file header is the
	<i>page size</i>, a 2-byte field. See section
        <cite>pages_and_page_types</cite> for details.

      <p class=req id=H30040>


          <tcl>fileformat_import_requirement H30040</tcl>
      <p class=req id=H30050>


          <tcl>fileformat_import_requirement H30050</tcl>

      <p class=req id=H30060>


          <tcl>fileformat_import_requirement H30060</tcl>

      <p class=req id=H30070>


          <tcl>fileformat_import_requirement H30070</tcl>
      <p class=req id=H30080>



          <tcl>fileformat_import_requirement H30080</tcl>
      <p class=req id=H30090>



          <tcl>fileformat_import_requirement H30090</tcl>
      <p class=req id=H30100>



          <tcl>fileformat_import_requirement H30100</tcl>

      <p>
        Following the <i>file change counter</i> in the database header are
        two 4-byte fields related to the database file <i>free page list</i>.
        See section <cite>free_page_list</cite> for details.

      <p class=req id=H30110>

          <tcl>fileformat_import_requirement H30110</tcl>


      <p class=req id=H30120>



          <tcl>fileformat_import_requirement H30120</tcl>

      <p class=req id=H30130>



          <tcl>fileformat_import_requirement H30130</tcl>

      <p class=req id=H30140>


          <tcl>fileformat_import_requirement H30140</tcl>


      <p class=req id=H30150>



          <tcl>fileformat_import_requirement H30150</tcl>

      <p class=req id=H30160>

          <tcl>fileformat_import_requirement H30160</tcl>


      <p class=req id=H30170>



          <tcl>fileformat_import_requirement H30170</tcl>

      <p class=req id=H30180>




          <tcl>fileformat_import_requirement H30180</tcl>

    <h3 id="pages_and_page_types">Pages and Page Types</h3>
      <p>
        The entire database file is divided into pages, each page consisting
        of <i>page-size</i> bytes, where <i>page-size</i> is the 2-byte 
        integer value stored at offset 16 of the file header (see above).
        The <i>page-size</i> is always a power of two between 512 
713
714
715
716
717
718
719
720
721
722
723
724
725
726

727
728
729
730

731
732
733

734
735
736
737
738
739
740
741
            permanently designated "pointer-map" pages. See section 
            <cite>pointer_map_pages</cite> for details.
        <li><b>The locking page</b>. The database page that starts at
            byte offset 2<sup>30</sup>, if it is large enough to contain
            such a page, is always left unused.
      </ul>

      <p class=req id=H20019>
        The <i>database page size</i> of a well-formed database, stored as a
        2-byte big-endian unsigned integer at byte offset 16 of the file, 
        shall be an integer power of 2 between 512 and 32768, inclusive.
      <p class=req id=H20020>
        The size of a <i>well formed database file</i> shall be an integer
        multiple of the <i>database page size</i>.

      <p class=req id=H20021>
	Each page of a <i>well formed database file</i> is exactly one of a 
        <i>B-Tree page</i>, an <i>overflow page</i>, a <i>free page</i>, a 
        <i>pointer-map page</i> or the <i>locking page</i>.

      <p class=req id=H20022>
        The database page that starts at byte offset 2<sup>30</sup>, the
        <i>locking page</i>, is never used for any purpose.

        

    <h3 id=schema_table>The Schema Table</h3>
      <p>
        Apart from being the page that contains the file-header, page 1 of
        the database file is special because it is the root page of the
        B-Tree structure that contains the schema table data. From the SQL
        level, the schema table is accessible via the name "sqlite_master".







|
|
<
<
|
<
<
>
|
<
<
<
>
|
<
<
>
|







693
694
695
696
697
698
699
700
701


702


703
704



705
706


707
708
709
710
711
712
713
714
715
            permanently designated "pointer-map" pages. See section 
            <cite>pointer_map_pages</cite> for details.
        <li><b>The locking page</b>. The database page that starts at
            byte offset 2<sup>30</sup>, if it is large enough to contain
            such a page, is always left unused.
      </ul>

      <p class=req id=H30190>
          <tcl>fileformat_import_requirement H30190</tcl>


      <p class=req id=H30200>


          <tcl>fileformat_import_requirement H30200</tcl>
      <p class=req id=H30210>



          <tcl>fileformat_import_requirement H30210</tcl>
      <p class=req id=H30220>


          <tcl>fileformat_import_requirement H30220</tcl>


    <h3 id=schema_table>The Schema Table</h3>
      <p>
        Apart from being the page that contains the file-header, page 1 of
        the database file is special because it is the root page of the
        B-Tree structure that contains the schema table data. From the SQL
        level, the schema table is accessible via the name "sqlite_master".
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849

850
851
852
853
854
855
856
857
858
859
860

861
862
863
864

865
866
867
868
869

870
871
872
873
874
875
876
877
878
879
880
881

882
883
884
885
886
887
888
889

890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906

907
908
909
910
911

912
913
914
915

916
917
918
919
920
921
922
923

924
925
926
927

928
929
930
931
932

933
934
935
936
937
938

939
940
941
942
943
944
945
946
947
948
949
950
951

952
953
954
955

956
957
958
959
960
961
962

963
964
965
966
967
968
969
970

971
972
973
974
975

976
977
978
979

980
981
982
983
984
985
986

987
988
989
990
991
992
993
994
995
996
997
998
999
1000
1001

1002
1003
1004
1005
1006
1007
1008
        <tr><td>index <td>i1 <td>abc <td>3 <td>CREATE INDEX i1 ON abc(b, c)
        <tr><td>table <td>def <td>def <td>4 <td>CREATE TABLE def(a PRIMARY KEY, b, c, UNIQUE(b, c))
        <tr><td>index <td>sqlite_autoindex_def_1 <td>def <td>5 <td>
        <tr><td>index <td>sqlite_autoindex_def_2 <td>def <td>6 <td>
        <tr><td>view <td>v1 <td>v1 <td>0 <td>CREATE VIEW v1 AS SELECT * FROM abc
      </table>

      <p class=req id=H20023>
	In a <i>well-formed database file</i>, the portion of the first 
        database page not consumed by the database file-header (all but the 
        first 100 bytes) contains the root node of a table B-Tree, 
        the <i>schema table</i>.
      <p class=req id=H20024>
	All records stored in the <i>schema table</i> contain exactly five
        fields.


      <p>The following requirements describe "table" records.

      <p class=req id=H20025>
        For each SQL table in the database apart from itself 
        ("sqlite_master"), the <i>schema table</i> of a <i>well-formed 
        database file</i> contains an associated record.

      <p class=req id=H20026>
        The first field of each <i>schema table</i> record associated with an
        SQL table shall be the text value "table".


      <p class=req id=H20027>
        The second field of each <i>schema table</i> record associated with an
	SQL table shall be a text value set to the name of the SQL table.


      <p class=req id=H20028>
        In a <i>well-formed database file</i>, the third field of all 
        <i>schema table</i> records associated with SQL tables shall contain 
        the same value as the second field.


      <p class=req id=H20029>
        In a <i>well-formed database file</i>, the fourth field of all 
	<i>schema table</i> records associated with SQL tables that are not
	virtual tables contains the page number (an integer value) of the root
        page of the associated <i>table B-Tree</i> structure within the 
        database file.

      <p class=req id=H20030>
        If the associated database table is a virtual table, the fourth
        field of the <i>schema table</i> record shall contain an SQL NULL 
        value.


      <p class=req id=H20031>
        In a well-formed database, the fifth field of all <i>schema table</i>
	records associated with SQL tables shall contain a "CREATE TABLE"
        or "CREATE VIRTUAL TABLE" statment (a text value).  The details
        of the statement shall be such that executing the statement
	would create a table of precisely the same name and schema as the
        existing database table.


      <p>The following requirements describe "implicit index" records.

      <p class=req id=H20032>
        For each PRIMARY KEY or UNIQUE constraint present in the definition
        of each SQL table in the database, the schema table of a well-formed 
        database shall contain a record with the first field set to the text 
        value "index", and the second field set to a text value containing a
        string of the form "sqlite_autoindex_&lt;name&gt;_&lt;idx&gt;", where
        &lt;name&gt; is the name of the SQL table and &lt;idx&gt; is an 
        integer value.

      <p class=req id=H20033>
        In a well-formed database, the third field of all schema table
        records associated with SQL PRIMARY KEY or UNIQUE constraints shall
	contain the name of the table to which the constraint applies (a 
        text value).

      <p class=req id=H20034>
        In a well-formed database, the fourth field of all schema table
	records associated with SQL PRIMARY KEY or UNIQUE constraints shall
	contain the page number (an integer value) of the root page of the
        associated index B-Tree structure.

      <p class=req id=H20035>
        In a well-formed database, the fifth field of all schema table
        records associated with SQL PRIMARY KEY or UNIQUE constraints shall
        contain an SQL NULL value.


      <p>The following requirements describe "explicit index" records.

      <p class=req id=H20036>
	For each SQL index in the database, the schema table of a well-formed
	database shall contain a record with the first field set to the text
	value "index" and the second field set to a text value containing the
        name of the SQL index.

      <p class=req id=H20037>
        In a well-formed database, the third field of all schema table
        records associated with SQL indexes shall contain the name of the
        SQL table that the index applies to.

      <p class=req id=H20038>
        In a well-formed database, the fourth field of all schema table
        records associated with SQL indexes shall contain the page number 
        (an integer value) of the root page of the associated index B-Tree
        structure.

      <p class=req id=H20039>
        In a well-formed database, the fifth field of all schema table
	records associated with SQL indexes shall contain an SQL "CREATE
	INDEX" statement (a text value). The details of the statement shall 
        be such that executing the statement would create an index of 
        precisely the same name and content as the existing database index.


      <p>The following requirements describe "view" records.

      <p class=req id=H20040>
	For each SQL view in the database, the schema table of a well-formed
	database shall contain a record with the first field set to the text
	value "view" and the second field set to a text value containing the
        name of the SQL view.

      <p class=req id=H20041>
        In a well-formed database, the third field of all schema table
        records associated with SQL views shall contain the same value as
        the second field.


      <p class=req id=H20042>
        In a well-formed database, the third field of all schema table
        records associated with SQL views shall contain the integer value 0.


      <p class=req id=H20043>
        In a well-formed database, the fifth field of all schema table
	records associated with SQL indexes shall contain an SQL "CREATE
	VIEW" statement (a text value). The details of the statement shall 
        be such that executing the statement would create a view of 
        precisely the same name and definition as the existing database view.


      <p>The following requirements describe "trigger" records.

      <p class=req id=H20044>
	For each SQL trigger in the database, the schema table of a well-formed
	database shall contain a record with the first field set to the text
	value "trigger" and the second field set to a text value containing the
        name of the SQL trigger.


      <p class=req id=H20045>
        In a well-formed database, the third field of all schema table
        records associated with SQL triggers shall contain the name of the
        database table or view to which the trigger applies.


      <p class=req id=H20046>
        In a well-formed database, the third field of all schema table
        records associated with SQL triggers shall contain the integer value 0.


      <p class=req id=H20047>
        In a well-formed database, the fifth field of all schema table
	records associated with SQL indexes shall contain an SQL "CREATE
	TRIGGER" statement (a text value). The details of the statement shall 
        be such that executing the statement would create a trigger of 
        precisely the same name and definition as the existing database trigger.


      <p>The following requirements describe the placement of B-Tree root 
         pages in auto-vacuum databases.

      <p class=req id=H20048>
        In an auto-vacuum database, all pages that occur before the page
        number stored in the <i>auto-vacuum last root-page</i> field
        of the database file header (see H20014) must be either B-Tree <i>root
        pages</i>, <i>pointer-map pages</i> or the <i>locking page</i>.

      <p class=req id=H20049>
        In an auto-vacuum database, no B-Tree <i>root pages</i> may occur
        on or after the page number stored in the <i>auto-vacuum last root-page</i> field
        of the database file header (see H20014) must be either B-Tree <i>root
        pages</i>, <i>pointer-map pages</i> or the <i>locking page</i>.



 
  <h2 id="btree_structures">B-Tree Structures</h2>
    <p>
      A large part of any SQLite database file is given over to one or more
      B-Tree structures. A single B-Tree structure is stored using one or more







|
|
<
<
<
|
<
<
>



|
|
<
<

|
<
<
>

|
<
<
>

|
<
<
<
>

|
<
<
<
<
|

|
<
<
<
>

|
<
<
<
<
<
<
>



|
|
<
<
<
<
<
<

|
<
<
<
<
>
|
<
<
<
<
>
|
<
<
<
>



|
<
<
<
<
>
|
<
<
<
>
|
<
<
<
<
>
|
<
<
<
<
<
>



|
|
<
<
<

|
<
<
<
>

|
<
<
>

|
<
<
<
<
<
>



|
<
<
<
<
>

|
<
<
<
>

|
<
<
>

|
<
<
<
<
<
>




|
|
<
<
<

|
<
<
<
<
>







809
810
811
812
813
814
815
816
817



818


819
820
821
822
823
824


825
826


827
828
829


830
831
832



833
834
835




836
837
838



839
840
841






842
843
844
845
846
847






848
849




850
851




852
853



854
855
856
857
858




859
860



861
862




863
864





865
866
867
868
869
870



871
872



873
874
875


876
877
878





879
880
881
882
883




884
885
886



887
888
889


890
891
892





893
894
895
896
897
898
899



900
901




902
903
904
905
906
907
908
909
        <tr><td>index <td>i1 <td>abc <td>3 <td>CREATE INDEX i1 ON abc(b, c)
        <tr><td>table <td>def <td>def <td>4 <td>CREATE TABLE def(a PRIMARY KEY, b, c, UNIQUE(b, c))
        <tr><td>index <td>sqlite_autoindex_def_1 <td>def <td>5 <td>
        <tr><td>index <td>sqlite_autoindex_def_2 <td>def <td>6 <td>
        <tr><td>view <td>v1 <td>v1 <td>0 <td>CREATE VIEW v1 AS SELECT * FROM abc
      </table>

      <p class=req id=H30230>
          <tcl>fileformat_import_requirement H30230</tcl>



      <p class=req id=H30240>


          <tcl>fileformat_import_requirement H30240</tcl>

      <p>The following requirements describe "table" records.

      <p class=req id=H30250>
          <tcl>fileformat_import_requirement H30250</tcl>



      <p class=req id=H30260>


          <tcl>fileformat_import_requirement H30260</tcl>

      <p class=req id=H30270>


          <tcl>fileformat_import_requirement H30270</tcl>

      <p class=req id=H30280>



          <tcl>fileformat_import_requirement H30280</tcl>

      <p class=req id=H30290>




          <tcl>fileformat_import_requirement H30290</tcl>

      <p class=req id=H30300>



          <tcl>fileformat_import_requirement H30300</tcl>

      <p class=req id=H30310>






          <tcl>fileformat_import_requirement H30310</tcl>

      <p>The following requirements describe "implicit index" records.

      <p class=req id=H30320>
          <tcl>fileformat_import_requirement H30320</tcl>







      <p class=req id=H30330>




          <tcl>fileformat_import_requirement H30330</tcl>
      <p class=req id=H30340>




          <tcl>fileformat_import_requirement H30340</tcl>
      <p class=req id=H30350>



          <tcl>fileformat_import_requirement H30350</tcl>

      <p>The following requirements describe "explicit index" records.

      <p class=req id=H30360>




          <tcl>fileformat_import_requirement H30360</tcl>
      <p class=req id=H30370>



          <tcl>fileformat_import_requirement H30370</tcl>
      <p class=req id=H30380>




          <tcl>fileformat_import_requirement H30380</tcl>
      <p class=req id=H30390>





          <tcl>fileformat_import_requirement H30390</tcl>

      <p>The following requirements describe "view" records.

      <p class=req id=H30400>
          <tcl>fileformat_import_requirement H30400</tcl>




      <p class=req id=H30410>



          <tcl>fileformat_import_requirement H30410</tcl>

      <p class=req id=H30420>


          <tcl>fileformat_import_requirement H30420</tcl>

      <p class=req id=H30430>





          <tcl>fileformat_import_requirement H30430</tcl>

      <p>The following requirements describe "trigger" records.

      <p class=req id=H30440>




          <tcl>fileformat_import_requirement H30440</tcl>

      <p class=req id=H30450>



          <tcl>fileformat_import_requirement H30450</tcl>

      <p class=req id=H30460>


          <tcl>fileformat_import_requirement H30460</tcl>

      <p class=req id=H30470>





          <tcl>fileformat_import_requirement H30470</tcl>

      <p>The following requirements describe the placement of B-Tree root 
         pages in auto-vacuum databases.

      <p class=req id=H30480>
          <tcl>fileformat_import_requirement H30480</tcl>




      <p class=req id=H30490>




          <tcl>fileformat_import_requirement H30490</tcl>


 
  <h2 id="btree_structures">B-Tree Structures</h2>
    <p>
      A large part of any SQLite database file is given over to one or more
      B-Tree structures. A single B-Tree structure is stored using one or more
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037

1038
1039
1040
1041
1042
1043
1044
          B-Tree structures are described in detail in section 
          <cite>table_btrees</cite>.
      <li>The <b>index B-Tree</b>, which uses database records as keys. Index
          B-Tree structures are described in detail in section 
          <cite>index_btrees</cite>.
    </ul>

    <p class=req id=H20050>
      As well as the <i>schema table</i>, a <i>well-formed database file</i> 
      contains <i>N</i> table B-Tree structures, where <i>N</i> is the 
      number of non-virtual tables in the logical database, excluding the 
      sqlite_master table but including sqlite_sequence and other system 
      tables.
    <p class=req id=H20051>
      A well-formed database file contains <i>N</i> table B-Tree structures,
      where <i>N</i> is the number of indexes in the logical database,
      including indexes created by UNIQUE or PRIMARY KEY clauses in the
      declaration of SQL tables.


    <h3 id="varint_format">Variable Length Integer Format</h3>
      <p>
	In several parts of the B-Tree structure, 64-bit twos-complement signed
	integer values are stored in the "variable length integer format"
	described here.
      <p>







|
|
<
<
<
<
|
<
<
<
<
>







921
922
923
924
925
926
927
928
929




930




931
932
933
934
935
936
937
938
          B-Tree structures are described in detail in section 
          <cite>table_btrees</cite>.
      <li>The <b>index B-Tree</b>, which uses database records as keys. Index
          B-Tree structures are described in detail in section 
          <cite>index_btrees</cite>.
    </ul>

    <p class=req id=H30500>
          <tcl>fileformat_import_requirement H30500</tcl>




    <p class=req id=H30510>




          <tcl>fileformat_import_requirement H30510</tcl>

    <h3 id="varint_format">Variable Length Integer Format</h3>
      <p>
	In several parts of the B-Tree structure, 64-bit twos-complement signed
	integer values are stored in the "variable length integer format"
	described here.
      <p>
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106

1107
1108
1109
1110
1111
1112
1113
1114
1115

1116
1117
1118
1119
1120
1121
1122
1123

1124
1125
1126
1127
1128
1129
1130
1131
	<tr><td>200815 <td>0x000000000003106F <td>0x8C 0xA0 0x6F
        <tr><td>-1     <td>0xFFFFFFFFFFFFFFFF 
            <td>0xFF 0xFF 0xFF 0xFF 0xFF 0xFF 0xFF 0xFF 0xFF
        <tr><td>-78056 <td>0xFFFFFFFFFFFECD56
            <td>0xFF 0xFF 0xFF 0xFF 0xFF 0xFF 0xFD 0xCD 0x56
      </table>

    <p class=req id=H20052>
      A 64-bit signed integer value stored in <i>variable length integer</i>
      format consumes from 1 to 9 bytes of space.

    <p class=req id=H20053>
      The most significant bit of all bytes except the last in a serialized
      <i>variable length integer</i> is always set. Unless the serialized 
      form consumes the maximum 9 bytes available, then the most significant
      bit of the final byte of the representation is always cleared.


    <p class=req id=H20054>
      The eight least significant bytes of the 64-bit twos-compliment
      representation of a value stored in a 9 byte <i>variable length
      integer</i> are stored in the final byte (byte offset 8) of the
      serialized <i>variable length integer</i>. The other 56 bits are
      stored in the 7 least significant bits of each of the first 8 bytes
      of the serialized <i>variable length integer</i>, in order from
      most significant to least significant.


    <p class=req id=H20055>
      A <i>variable length integer</i> that consumes less than 9 bytes of
      space contains a value represented as an <i>N</i>-bit unsigned 
      integer, where <i>N</i> is equal to the number of bytes consumed by 
      the serial representation (between 1 and 8) multiplied by 7. The
      <i>N</i> bits are stored in the 7 least significant bits of each
      byte of the serial representation, from most to least significant.

      

    <h3 id="record_format">Database Record Format</h3>
      <p>
        A database record is a blob of data that represents an ordered
        list of one or more SQL values. Database records are used in two
        places in SQLite database files - as the associated data for entries
        in table B-Tree structures, and as the key values in index B-Tree







|
<
|

|
<
<
<
<
>

|
<
<
<
<
<
<
<
>

|
<
<
<
<
<
<
>
|







985
986
987
988
989
990
991
992

993
994
995




996
997
998







999
1000
1001






1002
1003
1004
1005
1006
1007
1008
1009
1010
	<tr><td>200815 <td>0x000000000003106F <td>0x8C 0xA0 0x6F
        <tr><td>-1     <td>0xFFFFFFFFFFFFFFFF 
            <td>0xFF 0xFF 0xFF 0xFF 0xFF 0xFF 0xFF 0xFF 0xFF
        <tr><td>-78056 <td>0xFFFFFFFFFFFECD56
            <td>0xFF 0xFF 0xFF 0xFF 0xFF 0xFF 0xFD 0xCD 0x56
      </table>

    <p class=req id=H30520>

          <tcl>fileformat_import_requirement H30520</tcl>

    <p class=req id=H30530>




          <tcl>fileformat_import_requirement H30530</tcl>

    <p class=req id=H30540>







          <tcl>fileformat_import_requirement H30540</tcl>

    <p class=req id=H30550>






          <tcl>fileformat_import_requirement H30550</tcl>


    <h3 id="record_format">Database Record Format</h3>
      <p>
        A database record is a blob of data that represents an ordered
        list of one or more SQL values. Database records are used in two
        places in SQLite database files - as the associated data for entries
        in table B-Tree structures, and as the key values in index B-Tree
1209
1210
1211
1212
1213
1214
1215
1216
1217
1218
1219
1220
1221
1222
1223
1224
1225
1226

1227
1228
1229
1230
1231
1232
1233
1234
1235

1236
1237
1238
1239
1240

1241
1242
1243
1244
1245
1246

1247
1248
1249
1250
1251

1252
1253
1254
1255
1256

1257
1258
1259





1260
1261
1262
1263
1264
1265
1266
1267
1268
1269
1270
1271
1272
1273
1274
1275
1276
1277

1278
1279
1280
1281
1282

1283
1284
1285
1286
1287

1288
1289
1290
1291
1292
1293
1294
1295

1296
1297
1298
1299
1300
1301
1302
1303
1304

1305
1306
1307
1308
1309
1310
1311
1312
1313
1314
1315
1316
1317
1318
1319

1320
1321
1322
1323
1324
1325
1326
        the length of the data field is as described in the table above.
      <p>
        The data field associated with a string value contains the string
        encoded using the database encoding, as defined in the database
        file header (see section <cite>file_header</cite>). No 
        nul-terminator character is stored in the database.

      <p class=req id=H20056>
	A <i>database record</i> consists of a <i>database record header</i>,
        followed by <i>database record data</i>. The first part of the
        <i>database record header</i> is a <i>variable length integer</i>
        containing the total size (including itself) of the header in bytes.

      <p class=req id=H20057>
	Following the length field, the remainder of the <i>database record
	header</i> is populated with <i>N</i> <i>variable length integer</i>
        fields, where <i>N</i> is the number of database values stored in
        the record. 


      <p class=req id=H20058>
	Following the <i>database record header</i>, the <i>database record
        data</i> is made up of <i>N</i> variable length blobs of data, where
        <i>N</i> is again the number of database values stored in the record.
        The <i>n</i> blob contains the data for the <i>n</i>th value in
        the database record. The size and format of each blob of data is
        encoded in the corresponding <i>variable length integer</i> field
        in the <i>database record header</i>.


      <p class=req id=H20059>
        A value of 0 stored within the <i>database record header</i> indicates
        that the corresponding database value is an SQL NULL. In this case
        the blob of data in the data area is 0 bytes in size.


      <p class=req id=H20060>
        A value of 1 stored within the <i>database record header</i> indicates
        that the corresponding database value is an SQL integer. In this case
	the blob of data contains the integer value, formatted as a 1-byte
        big-endian signed integer.

      <p class=req id=H20061>
        A value of 2 stored within the <i>database record header</i> indicates
        that the corresponding database value is an SQL integer. In this case
	the blob of data contains the integer value, formatted as a 2-byte
        big-endian signed integer.

      <p class=req id=H20062>
        A value of 3 stored within the <i>database record header</i> indicates
        that the corresponding database value is an SQL integer. In this case
	the blob of data contains the integer value, formatted as a 3-byte
        big-endian signed integer.

      <p class=req id=H20063>
        A value of 4 stored within the <i>database record header</i> indicates
        that the corresponding database value is an SQL integer. In this case





	the blob of data contains the integer value, formatted as a 4-byte
        big-endian signed integer.
      <p class=req id=H20064>
        A value of 5 stored within the <i>database record header</i> indicates
        that the corresponding database value is an SQL integer. In this case
	the blob of data contains the integer value, formatted as a 6-byte
        big-endian signed integer.
      <p class=req id=H20065>
        A value of 6 stored within the <i>database record header</i> indicates
        that the corresponding database value is an SQL integer. In this case
	the blob of data contains the integer value, formatted as a 8-byte
        big-endian signed integer.

      <p class=req id=H20066>
        A value of 7 stored within the <i>database record header</i> indicates
	that the corresponding database value is an SQL real (floating
	point number). In this case the blob of data contains an 8-byte
        IEEE floating point number, stored in big-endian byte order.


      <p class=req id=H20067>
        A value of 8 stored within the <i>database record header</i> indicates
	that the corresponding database value is an SQL integer, value 0.
        In this case the blob of data in the data area is 0 bytes in size.


      <p class=req id=H20068>
        A value of 9 stored within the <i>database record header</i> indicates
	that the corresponding database value is an SQL integer, value 1.
        In this case the blob of data in the data area is 0 bytes in size.


      <p class=req id=H20069>
	An even value greater than or equal to 12 stored within the 
        <i>database record header</i> indicates that the corresponding 
        database value is an SQL blob field. The blob of data contains the
        value data.  The blob of data is exactly (<i>n</i>-12)/2 bytes 
        in size, where <i>n</i> is the integer value stored in the 
        <i>database record header</i>.


      <p class=req id=H20070>
	An odd value greater than or equal to 13 stored within the 
        <i>database record header</i> indicates that the corresponding 
        database value is an SQL text field. The blob of data contains the
        value text stored using the <i>database encoding</i>, with no 
        nul-terminator. The blob of data is exactly (<i>n</i>-12)/2 bytes 
        in size, where <i>n</i> is the integer value stored in the 
        <i>database record header</i>.


      <p>
        The following database file properties define restrictions on the 
        integer values that may be stored within a 
        <i>database record header</i>.

      <p class=req id=H20071>
        In a well-formed database file, if the values 8 or 9 appear within
        any <i>database record header</i> within the database, then the
        <i>schema-layer file format</i> (stored at byte offset 44 of the
        database file header) must be set to 4.
      <p class=req id=H20072>
        In a well-formed database file, the values 10 and 11, and all
        negative values may not appear within any <i>database record header</i> 
        in the database.


    <h3 id=index_btrees>Index B-Trees</h3>
      <p>
        As specified in section <cite>fileformat_overview</cite>, index 
        B-Tree structures store a unique set of the database records described
        in the previous section. While in some cases, when there are very
        few entries in the B-Tree, the entire structure may fit on a single







|
|
<
<
<

|
<
<
<
<
>

|
<
<
<
<
<
<
<
>

|
<
<
<
>

|
<
<
<
<
>
|
<
<
<
<
>
|
<
<
<
<
>
|
<
<
>
>
>
>
>
<
<
<
<
<
<
<
<
<
<
<
<

|
<
<
<
<
>

|
<
<
<
>

|
<
<
<
>

|
<
<
<
<
<
<
>

|
<
<
<
<
<
<
<
>






|
|
<
<
<
|
<
<
<
>







1088
1089
1090
1091
1092
1093
1094
1095
1096



1097
1098




1099
1100
1101







1102
1103
1104



1105
1106
1107




1108
1109




1110
1111




1112
1113


1114
1115
1116
1117
1118












1119
1120




1121
1122
1123



1124
1125
1126



1127
1128
1129






1130
1131
1132







1133
1134
1135
1136
1137
1138
1139
1140
1141



1142



1143
1144
1145
1146
1147
1148
1149
1150
        the length of the data field is as described in the table above.
      <p>
        The data field associated with a string value contains the string
        encoded using the database encoding, as defined in the database
        file header (see section <cite>file_header</cite>). No 
        nul-terminator character is stored in the database.

      <p class=req id=H30560>
          <tcl>fileformat_import_requirement H30560</tcl>




      <p class=req id=H30570>




          <tcl>fileformat_import_requirement H30570</tcl>

      <p class=req id=H30580>







          <tcl>fileformat_import_requirement H30580</tcl>

      <p class=req id=H30590>



          <tcl>fileformat_import_requirement H30590</tcl>

      <p class=req id=H30600>




          <tcl>fileformat_import_requirement H30600</tcl>
      <p class=req id=H30610>




          <tcl>fileformat_import_requirement H30610</tcl>
      <p class=req id=H30620>




          <tcl>fileformat_import_requirement H30620</tcl>
      <p class=req id=H30630>


          <tcl>fileformat_import_requirement H30630</tcl>
      <p class=req id=H30640>
          <tcl>fileformat_import_requirement H30640</tcl>
      <p class=req id=H30650>
          <tcl>fileformat_import_requirement H30650</tcl>













      <p class=req id=H30660>




          <tcl>fileformat_import_requirement H30660</tcl>

      <p class=req id=H30670>



          <tcl>fileformat_import_requirement H30670</tcl>

      <p class=req id=H30680>



          <tcl>fileformat_import_requirement H30680</tcl>

      <p class=req id=H30690>






          <tcl>fileformat_import_requirement H30690</tcl>

      <p class=req id=H30700>







          <tcl>fileformat_import_requirement H30700</tcl>

      <p>
        The following database file properties define restrictions on the 
        integer values that may be stored within a 
        <i>database record header</i>.

      <p class=req id=H30710>
          <tcl>fileformat_import_requirement H30710</tcl>



      <p class=req id=H30720>



          <tcl>fileformat_import_requirement H30720</tcl>

    <h3 id=index_btrees>Index B-Trees</h3>
      <p>
        As specified in section <cite>fileformat_overview</cite>, index 
        B-Tree structures store a unique set of the database records described
        in the previous section. While in some cases, when there are very
        few entries in the B-Tree, the entire structure may fit on a single
1358
1359
1360
1361
1362
1363
1364
1365
1366
1367
1368
1369
1370
1371

1372
1373
1374
1375
1376
1377
1378
1379
1380
1381
1382
1383

1384
1385
1386
1387
1388
1389
1390
1391

1392
1393
1394
1395
1396
1397
1398
1399
1400
1401
1402
1403
1404
1405
1406
1407
1408
1409
1410
1411
1412
1413
1414
1415
1416
1417
1418
1419

1420
1421
1422
1423
1424

1425
1426
1427
1428
1429
1430
1431

1432
1433
1434
1435
1436
1437
1438
1439
        Figure <cite>figure_indextree</cite> depicts one possible record
        distribution for an index B-Tree containing records R1 to R26, assuming
        that for all values of N, <i>R(N+1)&gt;R(N)</i>. In total the B-Tree
        structure uses 11 database file pages. Internal tree nodes contain
        database records and references to child node pages. Leaf nodes contain
        database records only.

      <p class=req id=H20073>
        The pages in an index B-Tree structures are arranged into a tree
        structure such that all leaf pages are at the same depth.

      <p class=req id=H20074>
        Each leaf node page in an index B-Tree contains one or more
        B-Tree cells, where each cell contains a database record.


      <p class=req id=H20075>
        Each internal node page in an index B-Tree contains one or more
        B-Tree cells, where each cell contains a child page number, <i>C</i>,
        and a database record <i>R</i>. All database records stored within
        the sub-tree headed by page <i>C</i> are smaller than record <i>R</i>,
        according to the index sort order (see below). Additionally, unless 
	<i>R</i> is the smallest database record stored on the internal node
	page, all integer keys within the sub-tree headed by <i>C</i> are
	greater than <i>R<sub>-1</sub></i>, where <i>R<sub>-1</sub></i> is the
        largest database record on the internal node page that is smaller 
        than <i>R</i>.


      <p class=req id=H20076>
        As well as child page numbers associated with B-Tree cells, each
        internal node page in an index B-Tree contains the page number
        of an extra child page, the <i>right-child page</i>. All database
        records stored in all B-Tree cells within the sub-tree headed by the 
        <i>right-child page</i> are greater than all database records 
        stored within B-Tree cells on the internal node page.


      <p>
	The precise way in which index B-Tree pages and cells are formatted is
        described in subsequent sections.


        <h4>Index B-Tree Content</h4>
          <p>
	    The database file contains one index B-Tree for each database index
	    in the logical database, including those created by UNIQUE or
	    PRIMARY KEY clauses in table declarations. Each record stored in
            an index B-Tree contains the same number of fields, the number of
            indexed columns in the database index declaration plus one. 
          <p>
            An index B-Tree contains an entry for each row in its associated
            database table. The fields of the record used as the index B-Tree
            key are copies of each of the indexed columns of the associated 
            database row, in order, followed by the rowid value of the same 
            row. See figure <cite>figure_examplepop</cite> for an example.

        <p class=req id=H20077>
          In a well-formed database, each index B-Tree contains a single entry
          for each row in the indexed logical database table.

        <p class=req id=H20078>
	  Each <i>database record</i> (key) stored by an index B-Tree in a
	  well-formed database contains the same number of values, the number
          of indexed columns plus one.


        <p class=req id=H20079>
	  The final value in each <i>database record</i> (key) stored by an
	  index B-Tree in a well-formed database contains the rowid (an integer
          value) of the corresponding logical database row.


        <p class=req id=H20080>
          The first <i>N</i> values in each <i>database record</i> (key) 
	  stored in an index B-Tree where <i>N</i> is the number of indexed
	  columns, contain the values of the indexed columns from the
	  corresponding logical database row, in the order specified for the
          index.

 
      <h4 id="index_btree_compare_func">Record Sort Order</h4>
        <p>
          This section defines the comparison function used when database
	  records are used as B-Tree keys for index B-Trees. The comparison
	  function is only defined when both database records contain the same
          number of fields.
        <p>







|
|
<

|
<
<
>

|
<
<
<
<
<
<
<
<
<
<
>

|
<
<
<
<
<
<
>




















|
|
<

|
<
<
<
>

|
<
<
<
>

|
<
<
<
<
<
>
|







1182
1183
1184
1185
1186
1187
1188
1189
1190

1191
1192


1193
1194
1195










1196
1197
1198






1199
1200
1201
1202
1203
1204
1205
1206
1207
1208
1209
1210
1211
1212
1213
1214
1215
1216
1217
1218
1219
1220
1221

1222
1223



1224
1225
1226



1227
1228
1229





1230
1231
1232
1233
1234
1235
1236
1237
1238
        Figure <cite>figure_indextree</cite> depicts one possible record
        distribution for an index B-Tree containing records R1 to R26, assuming
        that for all values of N, <i>R(N+1)&gt;R(N)</i>. In total the B-Tree
        structure uses 11 database file pages. Internal tree nodes contain
        database records and references to child node pages. Leaf nodes contain
        database records only.

      <p class=req id=H30730>
          <tcl>fileformat_import_requirement H30730</tcl>


      <p class=req id=H30740>


          <tcl>fileformat_import_requirement H30740</tcl>

      <p class=req id=H30750>










          <tcl>fileformat_import_requirement H30750</tcl>

      <p class=req id=H30760>






          <tcl>fileformat_import_requirement H30760</tcl>

      <p>
	The precise way in which index B-Tree pages and cells are formatted is
        described in subsequent sections.


        <h4>Index B-Tree Content</h4>
          <p>
	    The database file contains one index B-Tree for each database index
	    in the logical database, including those created by UNIQUE or
	    PRIMARY KEY clauses in table declarations. Each record stored in
            an index B-Tree contains the same number of fields, the number of
            indexed columns in the database index declaration plus one. 
          <p>
            An index B-Tree contains an entry for each row in its associated
            database table. The fields of the record used as the index B-Tree
            key are copies of each of the indexed columns of the associated 
            database row, in order, followed by the rowid value of the same 
            row. See figure <cite>figure_examplepop</cite> for an example.

        <p class=req id=H30770>
          <tcl>fileformat_import_requirement H30770</tcl>


        <p class=req id=H30780>



          <tcl>fileformat_import_requirement H30780</tcl>

        <p class=req id=H30790>



          <tcl>fileformat_import_requirement H30790</tcl>

        <p class=req id=H30800>





          <tcl>fileformat_import_requirement H30800</tcl>

      <h4 id="index_btree_compare_func">Record Sort Order</h4>
        <p>
          This section defines the comparison function used when database
	  records are used as B-Tree keys for index B-Trees. The comparison
	  function is only defined when both database records contain the same
          number of fields.
        <p>
1569
1570
1571
1572
1573
1574
1575
1576
1577
1578
1579
1580
1581
1582

1583
1584
1585
1586
1587
1588
1589
1590
1591
1592

1593
1594
1595
1596
1597

1598
1599
1600
1601
1602
1603
1604
1605

1606
1607
1608
1609
1610
1611

1612
1613
1614
1615
1616
1617
1618
1619
1620
1621
1622
1623
1624
1625
1626
1627
1628

1629
1630
1631
1632
1633

1634
1635
1636
1637
1638

1639
1640
1641

1642
1643
1644
1645
1646
1647
1648
1649
1650
1651
1652
1653
1654
1655
1656
1657
1658

1659
1660
1661
1662
1663

1664
1665
1666
1667
1668
1669
1670
1671
1672

1673
1674
1675
1676
1677
1678
1679
1680
1681

1682
1683
1684
1685
1686
1687
1688
            the page) of the next block in the list stored as a big-endian
            unsigned integer. The first two bytes of the final block in the 
            list are set to zero. The third and fourth bytes of each free
            block contain the total size of the free block in bytes, stored
            as a 2 byte big-endian unsigned integer.
      </ul>

      <p class=req id=H20081>
	The <i>b-tree page flags</i> field (the first byte) of each database
	page used as an internal node of an index B-Tree structure is set to
        0x02.  
      <p class=req id=H20082>
	The <i>b-tree page flags</i> field (the first byte) of each database
        page used as a leaf node of an index B-Tree structure is set to 0x0A.


      <p>
        The following requirements describe the <i>B-Tree page header</i>
        present at the start of both index and table B-Tree pages.

      <p class=req id=H20083>
        The first byte of each database page used as a B-Tree page contains
	the <i>b-tree page flags</i> field. On page 1, the <i>b-tree page
        flags</i> field is stored directly after the 100 byte file header
        at byte offset 100.


      <p class=req id=H20084>
        The number of B-Tree cells stored on a B-Tree page is stored as a
        2-byte big-endian integer starting at byte offset 3 of the B-Tree 
        page. On page 1, this field is stored at byte offset 103.


      <p class=req id=H20085>
        The 2-byte big-endian integer starting at byte offset 5 of each
        B-Tree page contains the byte-offset from the start of the page
        to the start of the <i>cell content area</i>, which consumes all space
        from this offset to the end of the usable region of the page.
	On page 1, this field is stored at byte offset 105.  All B-Tree 
        cells on the page are stored within the cell-content area.


      <p class=req id=H20086>
        On each page used as an internal node a of B-Tree structures, the
        page number of the rightmost child node in the B-Tree structure is
        stored as a 4-byte big-endian unsigned integer beginning at byte
        offset 8 of the database page, or byte offset 108 on page 1.


      <p>
        This requirement describes the cell content offset array. It applies
        to both B-Tree variants.

      <p class=req id=H20087>
        Immediately following the <i>page header</i> on each B-Tree page is the
        <i>cell offset array</i>, consisting of <i>N</i> 2-byte big-endian
        unsigned integers, where <i>N</i> is the number of cells stored
        on the B-Tree page (H20084). On an internal node B-Tree page,
        the cell offset array begins at byte offset 12, or on a leaf
        page, byte offset 8. For the B-Tree node on page 1, these
        offsets are 112 and 108, respectively.

      <p class=req id=H20088>
        The <i>cell offset array</i> and the <i>cell content area</i> (H20085)
        may not overlap.


      <p class=req id=H20089>
        Each value stored in the <i>cell offset array</i> must be greater
        than or equal to the offset to the <i>cell content area</i> (H20085),
        and less than the database <i>page size</i>.


      <p class=req id=H20090>
	The <i>N</i> values stored within the <i>cell offset array</i> are the
        byte offsets from the start of the B-Tree page to the beginning of 
        each of the <i>N</i> cells stored on the page.


      <p class=req id=H20091>
	No two B-Tree cells may overlap.


      <p>
	The following requirements govern management of free-space within the
        page content area (both table and index B-Tree pages).

      <p class=req id=H20092>
	Within the <i>cell content area</i>, all blocks of contiguous
	free-space (space not used by B-Tree cells) greater than 3 bytes in
        size are linked together into a linked list, the <i>free block list</i>.
        Such blocks of free space are known as <i>free blocks</i>.

      <p class=req id=H20093>
        The first two bytes of each <i>free block</i> contain the offset
        of the next <i>free block</i> in the <i>free block list</i> formatted
	as a 2-byte big-endian integer, relative to the start of the database
        page. If there is no next <i>free block</i>, then the first two 
        bytes are set to 0x00.


      <p class=req id=H20094>
        The second two bytes (byte offsets 2 and 3) of each <i>free block</i> 
        contain the total size of the <i>free block</i>, formatted as a 2-byte
        big-endian integer.


      <p class=req id=H20095>
        On all B-Tree pages, the offset of the first <i>free block</i> in the 
        <i>free block list</i>, relative to the start of the database page,
        is stored as a 2-byte big-endian integer starting at byte offset 
        1 of the database page. If there is no first <i>free block</i> 
        (because the <i>free block list</i> is empty), then the two bytes
        at offsets 1 and 2 of the database page are set to 0x00. On page 1,
        this field is stored at byte offset 101 of the page.



      <p class=req id=H20096>
        Within the cell-content area, all blocks of contiguous free-space 
        (space not used by B-Tree cells) less than or equal to 3 bytes in 
        size are known as <i>fragments</i>. The total size of all 
        <i>fragments</i> on a B-Tree page is stored as a 1-byte unsigned 
        integer at byte offset 7 of the database page. On page 1, this
        field is stored at byte offset 107.


      <h4 id=index_btree_cell_format>Index B-Tree Cell Format</h4>
        <p> 
          For index B-Tree internal tree node pages, each B-Tree cell begins
          with a child page-number, stored as a 4-byte big-endian unsigned
          integer. This field is omitted for leaf pages, which have no 
          children.







|
|
<
<
|
<
<
>





|
<
<
<
<
>

|
<
<
<
>

|
<
<
<
<
<
<
>

|
<
<
<
<
>





|
|
<
<
<
<
<
<

|
<
<
>

|
<
<
<
>

|
<
<
<
>

|
<
>





|
|
<
<
<

|
<
<
<
<
<
>

|
<
<
<
>

|
<
<
<
<
<
<
<
>


|
<
<
<
<
<
<
>







1368
1369
1370
1371
1372
1373
1374
1375
1376


1377


1378
1379
1380
1381
1382
1383
1384




1385
1386
1387



1388
1389
1390






1391
1392
1393




1394
1395
1396
1397
1398
1399
1400
1401






1402
1403


1404
1405
1406



1407
1408
1409



1410
1411
1412

1413
1414
1415
1416
1417
1418
1419
1420



1421
1422





1423
1424
1425



1426
1427
1428







1429
1430
1431
1432






1433
1434
1435
1436
1437
1438
1439
1440
            the page) of the next block in the list stored as a big-endian
            unsigned integer. The first two bytes of the final block in the 
            list are set to zero. The third and fourth bytes of each free
            block contain the total size of the free block in bytes, stored
            as a 2 byte big-endian unsigned integer.
      </ul>

      <p class=req id=H30810>
          <tcl>fileformat_import_requirement H30810</tcl>


      <p class=req id=H30820>


          <tcl>fileformat_import_requirement H30820</tcl>

      <p>
        The following requirements describe the <i>B-Tree page header</i>
        present at the start of both index and table B-Tree pages.

      <p class=req id=H30830>




          <tcl>fileformat_import_requirement H30830</tcl>

      <p class=req id=H30840>



          <tcl>fileformat_import_requirement H30840</tcl>

      <p class=req id=H30850>






          <tcl>fileformat_import_requirement H30850</tcl>

      <p class=req id=H30860>




          <tcl>fileformat_import_requirement H30860</tcl>

      <p>
        This requirement describes the cell content offset array. It applies
        to both B-Tree variants.

      <p class=req id=H30870>
          <tcl>fileformat_import_requirement H30870</tcl>







      <p class=req id=H30880>


          <tcl>fileformat_import_requirement H30880</tcl>

      <p class=req id=H30890>



          <tcl>fileformat_import_requirement H30890</tcl>

      <p class=req id=H30900>



          <tcl>fileformat_import_requirement H30900</tcl>

      <p class=req id=H30910>

          <tcl>fileformat_import_requirement H30910</tcl>

      <p>
	The following requirements govern management of free-space within the
        page content area (both table and index B-Tree pages).

      <p class=req id=H30920>
          <tcl>fileformat_import_requirement H30920</tcl>




      <p class=req id=H30930>





          <tcl>fileformat_import_requirement H30930</tcl>

      <p class=req id=H30940>



          <tcl>fileformat_import_requirement H30940</tcl>

      <p class=req id=H30950>







          <tcl>fileformat_import_requirement H30950</tcl>


      <p class=req id=H30960>






          <tcl>fileformat_import_requirement H30960</tcl>

      <h4 id=index_btree_cell_format>Index B-Tree Cell Format</h4>
        <p> 
          For index B-Tree internal tree node pages, each B-Tree cell begins
          with a child page-number, stored as a 4-byte big-endian unsigned
          integer. This field is omitted for leaf pages, which have no 
          children.
1732
1733
1734
1735
1736
1737
1738
1739
1740
1741
1742
1743
1744
1745
1746
1747
1748
1749
1750
1751

1752
1753
1754
1755
1756
1757
1758
1759

1760
1761
1762
1763
1764
1765
1766
1767
1768
1769

1770
1771
1772
1773
1774
1775
1776
1777
1778
1779
1780

1781
1782
1783
1784
1785
1786
1787
1788
1789
1790
1791
1792
1793
1794
          the values read from byte offsets 21 and 22 of the file header,
          respectively.
        <center><img src="images/fileformat/indexlongrecord.gif">
        <p><i>Figure <span class=fig id=figure_indexlongrecord></span> - 
          Large Record Index B-Tree Cell.</i>
        </center>

      <p class=req id=H20097>
        Each B-Tree cell belonging to an internal node page of an index 
        B-Tree consists of a 4-byte big-endian unsigned integer, the 
        <i>child page number</i>, followed by a <i>variable length integer</i>
        field, followed by a <i>database record</i>. The 
        <i>variable length integer</i> field contains the length of the
        database record in bytes.

      <p class=req id=H20098>
	Each B-Tree cell belonging to an leaf page of an index B-Tree 
        consists of a <i>variable length integer</i> field, followed by 
        a <i>database record</i>. The <i>variable length integer</i> field 
        contains the length of the database record in bytes.


      <p class=req id=H20099>
	If the database record stored in an index B-Tree page is 
        sufficiently small, then the entire cell is stored within the 
        index B-Tree page.  Sufficiently small is defined as equal to or 
        less than <i>max-local</i>, where:
        <code>
            <i>max-local</i> := (<i>usable-size</i> - 12) * 64 / 255 - 23</code>

        
      <p class=req id=H20100>
        If the database record stored as part of an index B-Tree cell is too
        large to be stored entirely within the B-Tree page (as defined by
        H20052), then only a prefix of the <i>database record</i> is stored
        within the B-Tree page and the remainder stored in an <i>overflow
        chain</i>. In this case, the database record prefix is immediately
	followed by the page number of the first page of the 
        <i>overflow chain</i>, formatted as a 4-byte big-endian unsigned 
        integer.


      <p class=req id=H20101>
        When a <i>database record</i> belonging to a table B-Tree cell is
        stored partially within an <i>overflow page chain</i>, the size
        of the prefix stored within the index B-Tree page is <i>N</i> bytes,
        where <i>N</i> is calculated using the following algorithm:
        <code>
            <i>min-local</i> := (<i>usable-size</i> - 12) * 32 / 255 - 23
            <i>max-local</i> := (<i>usable-size</i> - 12) * 64 / 255 - 23
            <i>N</i> := <i>min-local</i> + ((<i>record-size</i> - <i>min-local</i>) % (<i>usable-size</i> - 4))
            if( <i>N</i> &gt; <i>max-local</i> ) <i>N</i> := <i>min-local</i></code>


      <p>
        Requirements H20101 and H20099 are similar to the algorithms 
        presented in the text above. However instead of 
        <i>min-embedded-fraction</i> and <i>max-embedded-fraction</i> the
        requirements use the constant values 32 and 64, as well-formed 
        database files are required by H20008 and H20007 to store these 
        values in the relevant database file header fields.

    <h3 id=table_btrees>Table B-Trees</h3>
      <p>
        As noted in section <cite>fileformat_overview</cite>, table B-Trees
        store a set of unique 64-bit signed integer keys. Associated with
        each key is a database record. As with index B-Trees, the database







|
|
<
<
<
<
<

|
<
<
<
<
>

|
<
<
<
<
<
<
>
|
|
<
<
<
<
<
<
<
<
>

|
<
<
<
<
<
<
<
<
<
>


|



|







1484
1485
1486
1487
1488
1489
1490
1491
1492





1493
1494




1495
1496
1497






1498
1499
1500








1501
1502
1503









1504
1505
1506
1507
1508
1509
1510
1511
1512
1513
1514
1515
1516
1517
1518
          the values read from byte offsets 21 and 22 of the file header,
          respectively.
        <center><img src="images/fileformat/indexlongrecord.gif">
        <p><i>Figure <span class=fig id=figure_indexlongrecord></span> - 
          Large Record Index B-Tree Cell.</i>
        </center>

      <p class=req id=H30970>
          <tcl>fileformat_import_requirement H30970</tcl>






      <p class=req id=H30980>




          <tcl>fileformat_import_requirement H30980</tcl>

      <p class=req id=H30990>






          <tcl>fileformat_import_requirement H30990</tcl>

      <p class=req id=H31000>








          <tcl>fileformat_import_requirement H31000</tcl>

      <p class=req id=H31010>









          <tcl>fileformat_import_requirement H31010</tcl>

      <p>
        Requirements H31010 and H30990 are similar to the algorithms 
        presented in the text above. However instead of 
        <i>min-embedded-fraction</i> and <i>max-embedded-fraction</i> the
        requirements use the constant values 32 and 64, as well-formed 
        database files are required by H30080 and H30070 to store these 
        values in the relevant database file header fields.

    <h3 id=table_btrees>Table B-Trees</h3>
      <p>
        As noted in section <cite>fileformat_overview</cite>, table B-Trees
        store a set of unique 64-bit signed integer keys. Associated with
        each key is a database record. As with index B-Trees, the database
1818
1819
1820
1821
1822
1823
1824
1825
1826
1827
1828
1829
1830
1831
1832

1833
1834
1835
1836
1837
1838
1839
1840
1841
1842
1843

1844
1845
1846
1847
1848
1849
1850
1851

1852
1853
1854
1855
1856
1857
1858
        Figure <cite>figure_tabletree</cite> depicts a table B-Tree containing
	a contiguous set of 14 integer keys starting with 1. Each key <i>n</i>
	has an associated database record R<i>n</i>. All the keys and their
	associated records are stored in the leaf pages. The internal node
	pages contain no database data, their only purpose is to provide
	a way to navigate the tree structure.

      <p class=req id=H20102>
        The pages in a table B-Tree structures are arranged into a tree
        structure such that all leaf pages are at the same depth.

      <p class=req id=H20103>
        Each leaf page in a table B-Tree structure contains one or more
        B-Tree cells, where each cell contains a 64-bit signed integer key
        value and a database record.


      <p class=req id=H20104>
        Each internal node page in a table B-Tree structure contains one or 
        more B-Tree cells, where each cell contains a 64-bit signed integer 
	key value, <i>K</i>, and a child page number, <i>C</i>. All integer key
        values in all B-Tree cells within the sub-tree headed by page <i>C</i>
        are less than or equal to <i>K</i>. Additionally, unless <i>K</i>
        is the smallest integer key value stored on the internal node page,
        all integer keys within the sub-tree headed by <i>C</i> are greater
        than <i>K<sub>-1</sub></i>, where <i>K<sub>-1</sub></i> is the largest
        integer key on the internal node page that is smaller than <i>K</i>.


      <p class=req id=H20105>
        As well as child page numbers associated with B-Tree cells, each
        internal node page in a table B-Tree contains the page number
        of an extra child page, the <i>right-child page</i>. All key values
        in all B-Tree cells within the sub-tree headed by the <i>right-child
        page</i> are greater than all key values stored within B-Tree cells
        on the internal node page.


      <p>
	The precise way in which table B-Tree pages and cells are formatted is
        described in subsequent sections.

      <h4 id=table_btree_content>Table B-Tree Content</h4>
        <p>







|
|
<

|
<
<
<
>

|
<
<
<
<
<
<
<
<
<
>

|
<
<
<
<
<
<
>







1542
1543
1544
1545
1546
1547
1548
1549
1550

1551
1552



1553
1554
1555









1556
1557
1558






1559
1560
1561
1562
1563
1564
1565
1566
        Figure <cite>figure_tabletree</cite> depicts a table B-Tree containing
	a contiguous set of 14 integer keys starting with 1. Each key <i>n</i>
	has an associated database record R<i>n</i>. All the keys and their
	associated records are stored in the leaf pages. The internal node
	pages contain no database data, their only purpose is to provide
	a way to navigate the tree structure.

      <p class=req id=H31020>
          <tcl>fileformat_import_requirement H31020</tcl>


      <p class=req id=H31030>



          <tcl>fileformat_import_requirement H31030</tcl>

      <p class=req id=H31040>









          <tcl>fileformat_import_requirement H31040</tcl>

      <p class=req id=H31050>






          <tcl>fileformat_import_requirement H31050</tcl>

      <p>
	The precise way in which table B-Tree pages and cells are formatted is
        described in subsequent sections.

      <h4 id=table_btree_content>Table B-Tree Content</h4>
        <p>
1884
1885
1886
1887
1888
1889
1890
1891
1892
1893
1894
1895
1896
1897
1898

1899
1900
1901
1902
1903
1904
1905

1906
1907
1908
1909
1910
1911

1912
1913
1914
1915
1916
1917
1918
1919
1920
1921

1922
1923
1924
1925
1926
1927
1928
1929

1930
1931
1932
1933
1934
1935
1936
1937

1938
1939
1940
1941
1942
1943
1944
1945
1946
1947
1948
1949
1950
1951
1952
1953
1954
1955
1956
1957
1958
1959
1960

1961
1962
1963
1964
1965
1966
1967
1968
          are SQL NULL. If the schema layer file-format is greater than
          2, then the values associated with the "missing" fields are 
	  determined by the default value of the associated database table 
          columns.
	  <span class=todo>Reference to CREATE TABLE syntax. How are default
          values determined?</span>

        <p class=req id=H20106>
          In a well-formed database, each table B-Tree contains a single entry
          for each row in the corresponding logical database table.

        <p class=req id=H20107>
	  The key value (a 64-bit signed integer) for each B-Tree entry is
	  the same as the value of the rowid field of the corresponding
          logical database row.


        <p class=req id=H20108>
	  The SQL values serialized to make up each <i>database record</i>
	  stored as ancillary data in a table B-Tree shall be the equal to the
	  values taken by the <i>N</i> leftmost columns of the corresponding
	  logical database row, where <i>N</i> is the number of values in the
          database record.


        <p class=req id=H20109>
	    If a logical database table column is declared as an "INTEGER
            PRIMARY KEY", then instead of its integer value, an SQL NULL
	    shall be stored in its place in any database records used as
            ancillary data in a table B-Tree.


        <p>The following database properties discuss table B-Tree records 
           with implicit (default) values.

          <p class=req id=H20110>
	    If the database <i>schema layer file-format</i> (the value stored 
            as a 4-byte integer at byte offset 44 of the file header) is 1, 
            then all database records stored as ancillary data in a table 
            B-Tree structure have the same number of fields as there are 
            columns in the corresponding logical database table.


          <p class=req id=H20111>
	    If the database <i>schema layer file-format</i> value is two or
            greater and the rightmost <i>M</i> columns of a row contain SQL NULL
            values, then the corresponding record stored as ancillary data in
	    the table B-Tree has between <i>N</i>-<i>M</i> and <i>N</i> fields,
	    where <i>N</i> is the number of columns in the logical database
            table.


          <p class=req id=H20112>
	    If the database <i>schema layer file-format</i> value is three or
	    greater and the rightmost <i>M</i> columns of a row contain their
            default values according to the logical table declaration, then the
	    corresponding record stored as ancillary data in the table B-Tree
	    may have as few as <i>N</i>-<i>M</i> fields, where <i>N</i> is the
            number of columns in the logical database table.


      <h4>Table B-Tree Page Format</h4>
        <p>
          Table B-Tree structures use the same page format as index B-Tree 
          structures, described in section <cite>index_btree_page_format</cite>,
          with the following differences:
        <ul>
          <li>The first byte of the page-header, the "flags" field, is set to 
              0x05 for internal tree node pages, and 0x0D for leaf pages.
          <li>The content and format of the B-Tree cells is different. See
              section <cite>table_btree_cell_format</cite> for details.
          <li>The format of page 1 is the same as any other table B-Tree,
              except that 100 bytes less than usual is available for content.
              The first 100 bytes of page 1 is consumed by the database
              file header.
        </ul>

      <p class=req id=H20113>
	In a <i>well-formed database file</i>, the first byte of each page used
        as an internal node of a table B-Tree structure is set to 0x05.
      <p class=req id=H20114>
	In a <i>well-formed database file</i>, the first byte of each page used
        as a leaf node of a table B-Tree structure is set to 0x0D.

        
      <p>
        Most of the requirements specified in section 
        <cite>index_btree_page_format</cite> also apply to table B-Tree 
        pages. The wording of the requirements make it clear when this is
        the case, either by refering to generic "B-Tree pages" or by
        explicitly stating that the statement applies to both "table and
        index B-Tree pages".







|
|
<

|
<
<
<
>

|
<
<
<
<
<
>

|
<
<
<
<
>




|
<
<
<
<
<
>

|
<
<
<
<
<
<
>

|
<
<
<
<
<
<
>

















|
|
<
|
<
<
>
|







1592
1593
1594
1595
1596
1597
1598
1599
1600

1601
1602



1603
1604
1605





1606
1607
1608




1609
1610
1611
1612
1613
1614





1615
1616
1617






1618
1619
1620






1621
1622
1623
1624
1625
1626
1627
1628
1629
1630
1631
1632
1633
1634
1635
1636
1637
1638
1639
1640

1641


1642
1643
1644
1645
1646
1647
1648
1649
1650
          are SQL NULL. If the schema layer file-format is greater than
          2, then the values associated with the "missing" fields are 
	  determined by the default value of the associated database table 
          columns.
	  <span class=todo>Reference to CREATE TABLE syntax. How are default
          values determined?</span>

        <p class=req id=H31060>
          <tcl>fileformat_import_requirement H31060</tcl>


        <p class=req id=H31070>



          <tcl>fileformat_import_requirement H31070</tcl>

        <p class=req id=H31080>





          <tcl>fileformat_import_requirement H31080</tcl>

        <p class=req id=H31090>




          <tcl>fileformat_import_requirement H31090</tcl>

        <p>The following database properties discuss table B-Tree records 
           with implicit (default) values.

          <p class=req id=H31100>





          <tcl>fileformat_import_requirement H31100</tcl>

          <p class=req id=H31110>






          <tcl>fileformat_import_requirement H31110</tcl>

          <p class=req id=H31120>






          <tcl>fileformat_import_requirement H31120</tcl>

      <h4>Table B-Tree Page Format</h4>
        <p>
          Table B-Tree structures use the same page format as index B-Tree 
          structures, described in section <cite>index_btree_page_format</cite>,
          with the following differences:
        <ul>
          <li>The first byte of the page-header, the "flags" field, is set to 
              0x05 for internal tree node pages, and 0x0D for leaf pages.
          <li>The content and format of the B-Tree cells is different. See
              section <cite>table_btree_cell_format</cite> for details.
          <li>The format of page 1 is the same as any other table B-Tree,
              except that 100 bytes less than usual is available for content.
              The first 100 bytes of page 1 is consumed by the database
              file header.
        </ul>

      <p class=req id=H31130>
          <tcl>fileformat_import_requirement H31130</tcl>

      <p class=req id=H31140>


          <tcl>fileformat_import_requirement H31140</tcl>

      <p>
        Most of the requirements specified in section 
        <cite>index_btree_page_format</cite> also apply to table B-Tree 
        pages. The wording of the requirements make it clear when this is
        the case, either by refering to generic "B-Tree pages" or by
        explicitly stating that the statement applies to both "table and
        index B-Tree pages".
2038
2039
2040
2041
2042
2043
2044
2045
2046
2047
2048
2049
2050
2051
2052
2053
2054
2055
2056
2057
2058
2059

2060
2061
2062
2063
2064
2065
2066

2067
2068
2069
2070
2071
2072
2073
2074
2075

2076
2077
2078
2079
2080
2081
2082
2083
2084
2085
2086
2087
2088

2089
2090
2091
2092
2093
2094
2095
2096
2097
2098
2099
2100
2101
          header).

        <p>
          The following requirements describe the format of table B-Tree 
          cells, and the distribution thereof between B-Tree and overflow
          pages.

        <p class=req id=H20115>
          B-Tree cells belonging to table B-Tree internal node pages consist
	  of exactly two fields, a 4-byte big-endian unsigned integer
          immediately followed by a <i>variable length integer</i>. These
          fields contain the child page number and key value respectively
          (see H20103).

        <p class=req id=H20116>
          B-Tree cells belonging to table B-Tree leaf node pages consist
	  of three fields, two <i>variable length integer</i> values
          followed by a database record. The size of the database record
          in bytes is stored in the first of the two 
          <i>variable length integer</i> fields. The second of the two
          <i>variable length integer</i> fields contains the 64-bit signed 
          integer key (see H20103).


        <p class=req id=H20117>
          If the size of the record stored in a table B-Tree leaf page cell 
          is less than or equal to (<i>usable page size</i>-35) bytes, then 
          the entire cell is stored on the B-Tree leaf page. In a well-formed
	  database, <i>usable page size</i> is the same as the database 
          <i>page size</i>.


        <p class=req id=H20118>
          If a table B-Tree cell is too large to be stored entirely on
          a leaf page (as defined by H20117), then a prefix of the cell
          is stored on the leaf page, and the remainder stored in an
          <i>overflow page chain</i>. In this case the cell prefix
          stored on the B-Tree leaf page is immediately followed by a 
          4-byte big-endian unsigned integer containing the page number
          of the first overflow page in the chain.


        <p class=req id=H20119>
          When a table B-Tree cell is stored partially in an 
          <i>overflow page chain</i>, the prefix stored on the B-Tree
          leaf page consists of the two <i>variable length integer</i> fields,
          followed by the first <i>N</i> bytes of the database record, where
          <i>N</i> is determined by the following algorithm:
      <code>
        <i>min-local</i> := (<i>usable-size</i> - 12) * 255 / 32 - 23
        <i>max-local</i> := (<i>usable-size</i> - 35)
        <i>N</i> := <i>min-local</i> + (<i>record-size</i> - <i>min-local</i>) % (<i>usable-size</i> - 4)
        if( <i>N</i> &gt; <i>max-local</i> ) N := <i>min-local</i>
      </code>

        
        <p>
          Requirement H20119 is very similar to the algorithm presented in
          the text above. Instead of <i>min-embedded-fraction</i>, it uses
          the constant value 32, as well-formed database files are required
          by H20009 to store this value in the relevant database file 
          header field.

    <h3 id="overflow_page_chains">Overflow Page Chains</h3>
      <p>
        Sometimes, a database record stored in either an index or table 
        B-Trees is too large to fit entirely within a B-Tree cell. In this
        case part of the record is stored within the B-Tree cell and the







|
|
<
<
<
<

|
<
<
<
<
<
<
<
>

|
<
<
<
<
<
>

|
<
<
<
<
<
<
<
>

|
<
<
<
<
<
<
<
<
<
<
<
>
|

|


|







1720
1721
1722
1723
1724
1725
1726
1727
1728




1729
1730







1731
1732
1733





1734
1735
1736







1737
1738
1739











1740
1741
1742
1743
1744
1745
1746
1747
1748
1749
1750
1751
1752
1753
          header).

        <p>
          The following requirements describe the format of table B-Tree 
          cells, and the distribution thereof between B-Tree and overflow
          pages.

        <p class=req id=H31150>
          <tcl>fileformat_import_requirement H31150</tcl>





        <p class=req id=H31160>







          <tcl>fileformat_import_requirement H31160</tcl>

        <p class=req id=H31170>





          <tcl>fileformat_import_requirement H31170</tcl>

        <p class=req id=H31180>







          <tcl>fileformat_import_requirement H31180</tcl>

        <p class=req id=H31190>











          <tcl>fileformat_import_requirement H31190</tcl>

        <p>
          Requirement H31190 is very similar to the algorithm presented in
          the text above. Instead of <i>min-embedded-fraction</i>, it uses
          the constant value 32, as well-formed database files are required
          by H30090 to store this value in the relevant database file 
          header field.

    <h3 id="overflow_page_chains">Overflow Page Chains</h3>
      <p>
        Sometimes, a database record stored in either an index or table 
        B-Trees is too large to fit entirely within a B-Tree cell. In this
        case part of the record is stored within the B-Tree cell and the
2128
2129
2130
2131
2132
2133
2134
2135
2136
2137
2138
2139
2140
2141
2142
2143
2144
2145
2146

2147
2148
2149
2150
2151
2152
2153
2154
2155

2156
2157
2158
2159
2160
2161
2162

2163
2164
2165
2166
2167
2168
2169
      <p>
        Each overflow page except for the last one in the linked list 
        contains <i>available-space</i> bytes of record data. The last
        page in the list contains the remaining data, starting at byte
        offset 4. The value of the "next page" field on the last page
        in an overflow chain is undefined.

      <p class=req id=H20120>
	A single <i>overflow page</i> may store up to <i>available-space</i>
        bytes of database record data, where <i>available-space</i> is equal
        to (<i>usable-size</i> - 4).

      <p class=req id=H20121>
        When a database record is too large to store within a B-Tree page
        (see H20117 and H20100), a prefix of the record is stored within
        the B-Tree page and the remainder stored across <i>N</i> overflow
        pages. In this case <i>N</i> is the minimum number of pages required 
        to store the portion of the record not stored on the B-Tree page,
        given the maximum payload per overflow page defined by H20120.


      <p class=req id=H20122>
        The list of overflow pages used to store a single database record
        are linked together in a singly linked list known as an 
	<i>overflow chain</i>. The first four bytes of each page except the
        last in an <i>overflow chain</i> are used to store the page number
	of the next page in the linked list, formatted as an unsigned
        big-endian integer. The first four bytes of the last page in an
        <i>overflow chain</i> are set to 0x00.


      <p class=req id=H20123>
        Each overflow page except the last in an <i>overflow chain</i> 
	contains <i>N</i> bytes of record data starting at byte offset 4 of
	the page, where <i>N</i> is the maximum payload per overflow page, 
        as defined by H20120. The final page in an <i>overflow chain</i> 
        contains the remaining data, also starting at byte offset 4.


  <h2 id=free_page_list>The Free Page List</h2>
    <p>
      Sometimes, after deleting data from the database, SQLite removes pages
      from B-Tree structures. If these pages are not immediately required
      for some other purpose, they are placed on the free page list. The
      free page list contains those pages that are not currently being







|
|
<
<

|
<
<
<
<
<
<
>

|
<
<
<
<
<
<
<
>

|
<
<
<
<
<
>







1780
1781
1782
1783
1784
1785
1786
1787
1788


1789
1790






1791
1792
1793







1794
1795
1796





1797
1798
1799
1800
1801
1802
1803
1804
      <p>
        Each overflow page except for the last one in the linked list 
        contains <i>available-space</i> bytes of record data. The last
        page in the list contains the remaining data, starting at byte
        offset 4. The value of the "next page" field on the last page
        in an overflow chain is undefined.

      <p class=req id=H31200>
          <tcl>fileformat_import_requirement H31200</tcl>



      <p class=req id=H31210>






          <tcl>fileformat_import_requirement H31210</tcl>

      <p class=req id=H31220>







          <tcl>fileformat_import_requirement H31220</tcl>

      <p class=req id=H31230>





          <tcl>fileformat_import_requirement H31230</tcl>

  <h2 id=free_page_list>The Free Page List</h2>
    <p>
      Sometimes, after deleting data from the database, SQLite removes pages
      from B-Tree structures. If these pages are not immediately required
      for some other purpose, they are placed on the free page list. The
      free page list contains those pages that are not currently being
2199
2200
2201
2202
2203
2204
2205
2206
2207
2208
2209
2210
2211
2212

2213
2214
2215
2216
2217
2218
2219

2220
2221
2222
2223
2224
2225
2226
2227
2228
2229
2230
2231
2232
2233

2234
2235
2236
2237
2238

2239
2240
2241
2242
2243
2244
2245
2246
2247
2248
2249
2250
2251
2252
2253
2254

2255
2256
2257
2258
2259
2260
2261
2262
    <p>
      All trunk pages in the free-list except for the first contain the 
      maximum possible number of references to leaf pages. <span class=todo>Is this actually true in an auto-vacuum capable database?</span> The page number
      of the first page in the linked list of free-list trunk pages is 
      stored as a 4-byte big-endian unsigned integer at offset 32 of the
      file header (section <cite>file_header</cite>).

    <p class=req id=H20124>
      All <i>free pages</i> in a <i>well-formed database file</i> are part of
      the database <i>free page list</i>.

    <p class=req id=H20125>
      Each free page is either a <i>free list trunk</i> page or a
      <i>free list leaf</i> page.


    <p class=req id=H20126>
      All <i>free list trunk</i> pages are linked together into a singly
      linked list. The first 4 bytes of each page in the linked list 
      contains the page number of the next page in the list, formatted
      as an unsigned big-endian integer. The first 4 bytes of the last
      page in the linked list are set to 0x00.


    <p class=req id=H20127>
      The second 4 bytes of each <i>free list trunk</i> page contains
      the number of </i>free list leaf</i> page numbers stored on the free list
      trunk page, formatted as an unsigned big-endian integer.

    <p class=req id=H20128>
      Beginning at byte offset 8 of each <i>free list trunk</i> page are
      <i>N</i> page numbers, each formatted as a 4-byte unsigned big-endian 
      integers, where <i>N</i> is the value described in requirement H20127.

    <p class=req id=H20129>
      All page numbers stored on all <i>free list trunk</i> pages refer to
      database pages that are <i>free list leaves</i>.


    <p class=req id=H20130>
      The page number of each <i>free list leaf</i> page in a well-formed
      database file appears exactly once within the set of pages numbers
      stored on <i>free list trunk</i> pages.


    <p>The following statements govern the two 4-byte big-endian integers
       associated with the <i>free page list</i> structure in the database
       file header.

    <p class=req id=H20131>
      The total number of pages in the free list, including all <i>free list
      trunk</i> and <i>free list leaf</i> pages, is stored as a 4-byte unsigned
      big-endian integer at offset 36 of the database file header.

    <p class=req id=H20132>
      The page number of the first page in the linked list of <i>free list
      trunk</i> pages is stored as a 4-byte big-endian unsigned integer at
      offset 32 of the database file header. If there are no <i>free list
      trunk</i> pages in the database file, then the value stored at
      offset 32 of the database file header is 0.

  

    
     

  <h2 id=pointer_map_pages>Pointer Map Pages</h2>
    <p>
      Pointer map pages are only present in auto-vacuum capable databases.







|
|
<

|
<
<
>

|
<
<
<
<
<
>

|
<
<
|

|
<
<
|

|
<
<
>

|
<
<
<
>





|
|
<
<

|
<
<
<
<
<
>
|







1834
1835
1836
1837
1838
1839
1840
1841
1842

1843
1844


1845
1846
1847





1848
1849
1850


1851
1852
1853


1854
1855
1856


1857
1858
1859



1860
1861
1862
1863
1864
1865
1866
1867


1868
1869





1870
1871
1872
1873
1874
1875
1876
1877
1878
    <p>
      All trunk pages in the free-list except for the first contain the 
      maximum possible number of references to leaf pages. <span class=todo>Is this actually true in an auto-vacuum capable database?</span> The page number
      of the first page in the linked list of free-list trunk pages is 
      stored as a 4-byte big-endian unsigned integer at offset 32 of the
      file header (section <cite>file_header</cite>).

    <p class=req id=H31240>
          <tcl>fileformat_import_requirement H31240</tcl>


    <p class=req id=H31250>


          <tcl>fileformat_import_requirement H31250</tcl>

    <p class=req id=H31260>





          <tcl>fileformat_import_requirement H31260</tcl>

    <p class=req id=H31270>


          <tcl>fileformat_import_requirement H31270</tcl>

    <p class=req id=H31280>


          <tcl>fileformat_import_requirement H31280</tcl>

    <p class=req id=H31290>


          <tcl>fileformat_import_requirement H31290</tcl>

    <p class=req id=H31300>



          <tcl>fileformat_import_requirement H31300</tcl>

    <p>The following statements govern the two 4-byte big-endian integers
       associated with the <i>free page list</i> structure in the database
       file header.

    <p class=req id=H31310>
          <tcl>fileformat_import_requirement H31310</tcl>



    <p class=req id=H31320>





          <tcl>fileformat_import_requirement H31320</tcl>


    
     

  <h2 id=pointer_map_pages>Pointer Map Pages</h2>
    <p>
      Pointer map pages are only present in auto-vacuum capable databases.
2328
2329
2330
2331
2332
2333
2334
2335
2336
2337
2338
2339
2340
2341
2342
2343
2344
2345
2346
2347
2348
2349

2350
2351
2352
2353

2354
2355
2356
2357
2358
2359

2360
2361
2362
2363
2364
2365
2366
2367
2368
2369
2370
2371
2372

2373
2374
2375
2376
2377
2378

2379
2380
2381
2382
2383

2384
2385
2386
2387
2388
2389

2390
2391
2392
2393
2394
2395
2396
      table entries for the <i>num-entries</i> pages that follow it in the
      database file:
    <pre>
        <i>pointer-map-page-number</i> := 2 + <i>n</i> * <i>num-entries</i>
</pre>


    <p class=req id=H20133>
      Non auto-vacuum databases do not contain pointer map pages.

    <p class=req id=H20134>
      In an auto-vacuum database file, every <i>(num-entries + 1)</i>th 
      page beginning with page 2 is designated a pointer-map page, where 
      <i>num-entries</i> is calculated as:
      <code>
        <i>num-entries</i> := <i>database-usable-page-size</i> / 5
      </code>

    <p class=req id=H20135>
      In an auto-vacuum database file, each pointer-map page contains
      a pointer map entry for each of the <i>num-entries</i> (defined by 
      H20134) pages that follow it, if they exist.


    <p class=req id=H20136>
      Each pointer-map page entry consists of a 1-byte page type and a 
      4-byte page parent number, 5 bytes in total.


    <p class=req id=H20137>
	Pointer-map entries are packed into the pointer-map page in order,
        starting at offset 0. The entry associated with the database 
        page that immediately follows the pointer-map page is located at
        offset 0. The entry for the following page at offset 5 etc.


    <p>
      The following requirements govern the content of pointer-map entries.

    <p class=req id=H20138>
	For each page except page 1 in an auto-vacuum database file that is 
	the root page of a B-Tree structure, the page type of the 
      corresponding pointer-map entry is set to the value 0x01 and the
      parent page number is zero.
    <p class=req id=H20139>
	For each page that is a part of an auto-vacuum database file free-list, 
	the page type of the corresponding pointer-map entry is set to the
      value 0x02 and the parent page number is zero.

    <p class=req id=H20140>
	For each page in a well-formed auto-vacuum database that is the first
	page in an overflow chain, the page type of the corresponding
      pointer-map entry is set to 0x03 and the parent page number field
      is set to the page number of the B-Tree page that contains the start
      of the B-Tree cell stored in the overflow-chain.

    <p class=req id=H20141>
	For each page that is the second or a subsequent page in an overflow
	chain, the page type of the corresponding pointer-map entry is set to
	0x04 and the parent page number field is set to the page number of the
      preceding page in the overflow chain.

    <p class=req id=H20142>
	For each page that is not a root page but is a part of a B-Tree tree
	structure (not part of an overflow chain), the page type of the
	corresponding pointer-map entry is set to the value 0x05 and the parent
	page number field is set to the page number of the parent node in the
        B-Tree structure.



<h1 id="database_file_manipulation">Database File Manipulation</h1>

  <p class=todo>
    Better to do other higher level requirements before this.
<!--







|
|

|
|
<
<
<
<
<

|
<
<
<
>

|
<
<
>

|
<
<
<
<
>




|
|
<
<
<
|
<
<
<
>
|
<
<
<
<
<
>
|
<
<
<
<
>
|
<
<
<
<
<
>







1944
1945
1946
1947
1948
1949
1950
1951
1952
1953
1954
1955





1956
1957



1958
1959
1960


1961
1962
1963




1964
1965
1966
1967
1968
1969
1970



1971



1972
1973





1974
1975




1976
1977





1978
1979
1980
1981
1982
1983
1984
1985
      table entries for the <i>num-entries</i> pages that follow it in the
      database file:
    <pre>
        <i>pointer-map-page-number</i> := 2 + <i>n</i> * <i>num-entries</i>
</pre>


    <p class=req id=H31330>
          <tcl>fileformat_import_requirement H31330</tcl>

    <p class=req id=H31340>
          <tcl>fileformat_import_requirement H31340</tcl>






    <p class=req id=H31350>



          <tcl>fileformat_import_requirement H31350</tcl>

    <p class=req id=H31360>


          <tcl>fileformat_import_requirement H31360</tcl>

    <p class=req id=H31370>




          <tcl>fileformat_import_requirement H31370</tcl>

    <p>
      The following requirements govern the content of pointer-map entries.

    <p class=req id=H31380>
          <tcl>fileformat_import_requirement H31380</tcl>



    <p class=req id=H31390>



          <tcl>fileformat_import_requirement H31390</tcl>
    <p class=req id=H31400>





          <tcl>fileformat_import_requirement H31400</tcl>
    <p class=req id=H31410>




          <tcl>fileformat_import_requirement H31410</tcl>
    <p class=req id=H31420>





          <tcl>fileformat_import_requirement H31420</tcl>


<h1 id="database_file_manipulation">Database File Manipulation</h1>

  <p class=todo>
    Better to do other higher level requirements before this.
<!--
Changes to pages/fileio.in.
1
2
3
4
5



6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

<tcl>
proc hd_assumption {id text} {
  # hd_requirement $id $text
}




proc process {text} {
  set zOut ""
  set zSpecial ""

  foreach zLine [split $text "\n"] {
    switch -regexp $zLine {
      {^ *REQ *[^ ][^ ]* *$} {
        regexp { *REQ *([^ ]+) *} $zLine -> zRecid
        append zOut "<p class=req id=$zRecid>"
        set zSpecial hd_requirement
        set zRecText ""
      }
      {^ *ASSUMPTION *[^ ][^ ]* *$} {
        regexp { *ASSUMPTION *([^ ]+) *} $zLine -> zRecid
        append zOut "<p class=req id=$zRecid>"
        set zSpecial hd_assumption
        set zRecText ""
      }
      {^ *$} {
        if {$zSpecial ne ""} {
          # $zSpecial $zRecid $zRecText
          set zSpecial ""
          append zOut </p>
        }
      }
      default {
        if {$zSpecial ne ""} {
          if {[regexp {^ *\. *$} $zLine]} {set zLine ""}





>
>
>









|
|




|





|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

<tcl>
proc hd_assumption {id text} {
  # hd_requirement $id $text
}
unset -nocomplain fioreq
hd_read_requirement_file $::DOC/req/hlr35000.txt fioreq
proc fileio_noop {args} {}

proc process {text} {
  set zOut ""
  set zSpecial ""

  foreach zLine [split $text "\n"] {
    switch -regexp $zLine {
      {^ *REQ *[^ ][^ ]* *$} {
        regexp { *REQ *([^ ]+) *} $zLine -> zRecid
        append zOut "<p class=req id=$zRecid>\n"
        append zOut "[lindex $::fioreq($zRecid) 1]\n"
        set zRecText ""
      }
      {^ *ASSUMPTION *[^ ][^ ]* *$} {
        regexp { *ASSUMPTION *([^ ]+) *} $zLine -> zRecid
        append zOut "<p class=req id=$zRecid>\n"
        set zSpecial hd_assumption
        set zRecText ""
      }
      {^ *$} {
        if {$zSpecial ne ""} {
          $zSpecial $zRecid $zRecText
          set zSpecial ""
          append zOut </p>
        }
      }
      default {
        if {$zSpecial ne ""} {
          if {[regexp {^ *\. *$} $zLine]} {set zLine ""}
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
      is to determine the <i>page-size</i> used by the database file. 
      Because it is not possible to be certain as to the <i>page-size</i> 
      without holding at least a <i>shared lock</i> on the database file
      (because some other <i>database connection</i> might have changed it
      since the <i>database file header</i> was read), the value read from the
      <i>database file header</i> is known as the <i>expected page size</i>. 

    REQ H21006
      When a new <i>database connection</i> is required, SQLite shall attempt
      to open a file-handle on the database file. If the attempt fails, then
      no new <i>database connection</i> is created and an error returned.

    REQ H21007
      When a new <i>database connection</i> is required, after opening the
      new file-handle, SQLite shall attempt to read the first 100 bytes
      of the database file. If the attempt fails for any other reason than
      that the opened file is less than 100 bytes in size, then 
      the file-handle is closed, no new <i>database connection</i> is created
      and an error returned instead.

    REQ H21008
      If the <i>database file header</i> is successfully read from a newly
      opened database file, the connections <i>expected page-size</i> shall 
      be set to the value stored in the <i>page-size field</i> of the 
      database header.

    REQ H21009
      If the <i>database file header</i> cannot be read from a newly opened 
      database file (because the file is less than 100 bytes in size), the 
      connections <i>expected page-size</i> shall be set to the compile time
      value of the SQLITE_DEFAULT_PAGESIZE option.

  <h2 id=closing_database_connection>Closing a Connection</h2>

    <p>
      This section describes the VFS operations that take place when an
      existing database connection is closed (destroyed). 

    <p>
      Closing a database connection is a simple matter. The open VFS 
      file-handle is closed and in-memory <i>page cache</i> related resources
      are released. 

    REQ H21040
      When a <i>database connection</i> is closed, SQLite shall close the 
      associated file handle at the VFS level.

    REQ H21043
      When a <i>database connection</i> is closed, all associated <i>page
      cache</i> entries shall be discarded.

<h1 id=page_cache>The Page Cache</h1>
  <p>
    The contents of an SQLite database file are formatted as a set of 
    fixed size pages. See <cite>ff_sqlitert_requirements</cite> for a
    complete description of the format used. The <i>page size</i> used
    for a particular database is stored as part of the database file







|
<
<
<

|
<
<
<
<
<
<

|
<
<
<
<

|
<
<
<
<












|
<
<

|
<
<







707
708
709
710
711
712
713
714



715
716






717
718




719
720




721
722
723
724
725
726
727
728
729
730
731
732
733


734
735


736
737
738
739
740
741
742
      is to determine the <i>page-size</i> used by the database file. 
      Because it is not possible to be certain as to the <i>page-size</i> 
      without holding at least a <i>shared lock</i> on the database file
      (because some other <i>database connection</i> might have changed it
      since the <i>database file header</i> was read), the value read from the
      <i>database file header</i> is known as the <i>expected page size</i>. 

    REQ H35060




    REQ H35070







    REQ H35080





    REQ H35090





  <h2 id=closing_database_connection>Closing a Connection</h2>

    <p>
      This section describes the VFS operations that take place when an
      existing database connection is closed (destroyed). 

    <p>
      Closing a database connection is a simple matter. The open VFS 
      file-handle is closed and in-memory <i>page cache</i> related resources
      are released. 

    REQ H35400



    REQ H35430



<h1 id=page_cache>The Page Cache</h1>
  <p>
    The contents of an SQLite database file are formatted as a set of 
    fixed size pages. See <cite>ff_sqlitert_requirements</cite> for a
    complete description of the format used. The <i>page size</i> used
    for a particular database is stored as part of the database file
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
    different to reading from the <i>database file</i>.

   <p>
    Refer to section <cite>page_cache_algorithms</cite> for a description 
    of exactly how and for how long page data is stored in the 
    <i>page cache</i>.

  REQ H21001
    Except for the read operation required by H21007 and those reads made
    as part of opening a read-only transaction, SQLite shall ensure that
    a <i>database connection</i> has an open read-only or read/write 
    transaction when any data is read from the <i>database file</i>.

  REQ H21002
    Aside from those read operations described by H21007 and H21XXX, SQLite
    shall read data from the database file in aligned blocks of 
    <i>page-size</i> bytes, where <i>page-size</i> is the database page size 
    used by the database file.

  REQ H21042
    SQLite shall ensure that a <i>database connection</i> has an open
    read-only or read/write transaction before using data stored in the <i>page
    cache</i> to satisfy user queries.

  <h2 id=open_read_only_trans>Opening a Read-Only Transaction</h2>
    <p>
      Before data may be read from a <i>database file</i> or queried from
      the <i>page cache</i>, a <i>read-only transaction</i> must be
      successfully opened by the associated database connection (this is true
      even if the connection will eventually write to the database, as a







|
<
<
<
<

|
<
<
<
<

|
<
<
<







921
922
923
924
925
926
927
928




929
930




931
932



933
934
935
936
937
938
939
    different to reading from the <i>database file</i>.

   <p>
    Refer to section <cite>page_cache_algorithms</cite> for a description 
    of exactly how and for how long page data is stored in the 
    <i>page cache</i>.

  REQ H35010





  REQ H35020





  REQ H35420




  <h2 id=open_read_only_trans>Opening a Read-Only Transaction</h2>
    <p>
      Before data may be read from a <i>database file</i> or queried from
      the <i>page cache</i>, a <i>read-only transaction</i> must be
      successfully opened by the associated database connection (this is true
      even if the connection will eventually write to the database, as a
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
      enumerated above. If this happens, then the <i>shared-lock</i> is 
      released (if it was obtained) and an error returned to the user. 
      Step 2 of the procedure above is described in more detail in section
      <cite>hot_journal_detection</cite>. Section <cite>cache_validation</cite>
      describes the process identified by step 3 above. Further detail
      on step 4 may be found in section <cite>read_page_one</cite>.

    REQ H21010
      When required to open a <i>read-only transaction</i> using a 
      <i>database connection</i>, SQLite shall first attempt to obtain 
      a <i>shared-lock</i> on the file-handle open on the database file.

    REQ H21011
      If, while opening a <i>read-only transaction</i>, SQLite fails to obtain
      the <i>shared-lock</i> on the database file, then the process is
      abandoned, no transaction is opened and an error returned to the user.

    <p>
      The most common reason an attempt to obtain a <i>shared-lock</i> may
      fail is that some other connection is holding an <i>exclusive</i> or
      <i>pending lock</i>. However it may also fail because some other
      error (e.g. an IO or comms related error) occurs within the call to the
      xLock() method.

    REQ H21003
      While opening a <i>read-only transaction</i>, after successfully
      obtaining a <i>shared lock</i> on the database file, SQLite shall 
      attempt to detect and roll back a <i>hot journal file</i> associated 
      with the same database file.

    REQ H21012
      If, while opening a <i>read-only transaction</i>, SQLite encounters
      an error while attempting to detect or roll back a <i>hot journal
      file</i>, then the <i>shared-lock</i> on the database file is released,
      no transaction is opened and an error returned to the user.

    <p>
      Section <cite>hot_journal_detection</cite> contains a description of
      and requirements governing the detection of a hot-journal file refered
      to in the above requirements.

    REQ H21004
      Assuming no errors have occured, then after attempting to detect and
      roll back a <i>hot journal file</i>, if the <i>page cache</i> contains
      any entries associated with the current <i>database connection</i>,
      then SQLite shall validate the contents of the <i>page cache</i> by
      testing the <i>file change counter</i>.  This procedure is known as
      <i>cache validiation</i>.

    <p>
      The <i>cache validiation</i> process is described in detail in section
      <cite>cache_validation</cite>

    REQ H21005
      If the cache validiate procedure prescribed by H21004 is required and
      does not prove that the <i>page cache</i> entries associated with the
      current <i>database connection</i> are valid, then SQLite shall discard
      all entries associated with the current <i>database connection</i> from
      the <i>page cache</i>.

    <p>
      The numbered list above notes that the data for the first page of the
      database file, if it exists and is not already loaded into the <i>page
      cache</i>, must be read from the database file before the <i>read-only
      transaction</i> may be considered opened. This is handled by 
      requirement H21024.

  <h3 id=hot_journal_detection>Hot Journal Detection</h3>
    <p>
      This section describes the procedure that SQLite uses to detect a
      <i>hot journal file</i>. If a <i>hot journal file</i> is detected,
      this indicates that at some point the process of writing a 
      transaction to the database was interrupted and a recovery operation







|
<
<
<

|
<
<
<








|
<
<
<
<

|
<
<
<
<






|
<
<
<
<
<
<





|
<
<
<
<
<






|







977
978
979
980
981
982
983
984



985
986



987
988
989
990
991
992
993
994
995




996
997




998
999
1000
1001
1002
1003
1004






1005
1006
1007
1008
1009
1010





1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
      enumerated above. If this happens, then the <i>shared-lock</i> is 
      released (if it was obtained) and an error returned to the user. 
      Step 2 of the procedure above is described in more detail in section
      <cite>hot_journal_detection</cite>. Section <cite>cache_validation</cite>
      describes the process identified by step 3 above. Further detail
      on step 4 may be found in section <cite>read_page_one</cite>.

    REQ H35100




    REQ H35110




    <p>
      The most common reason an attempt to obtain a <i>shared-lock</i> may
      fail is that some other connection is holding an <i>exclusive</i> or
      <i>pending lock</i>. However it may also fail because some other
      error (e.g. an IO or comms related error) occurs within the call to the
      xLock() method.

    REQ H35030





    REQ H35120





    <p>
      Section <cite>hot_journal_detection</cite> contains a description of
      and requirements governing the detection of a hot-journal file refered
      to in the above requirements.

    REQ H35040







    <p>
      The <i>cache validiation</i> process is described in detail in section
      <cite>cache_validation</cite>

    REQ H35050






    <p>
      The numbered list above notes that the data for the first page of the
      database file, if it exists and is not already loaded into the <i>page
      cache</i>, must be read from the database file before the <i>read-only
      transaction</i> may be considered opened. This is handled by 
      requirement H35240.

  <h3 id=hot_journal_detection>Hot Journal Detection</h3>
    <p>
      This section describes the procedure that SQLite uses to detect a
      <i>hot journal file</i>. If a <i>hot journal file</i> is detected,
      this indicates that at some point the process of writing a 
      transaction to the database was interrupted and a recovery operation
1139
1140
1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
1159
1160
1161
1162
1163
1164
1165
1166
1167
1168
1169
1170
1171
1172
1173
1174
1175
1176
1177
1178
1179
1180
1181
1182
1183
1184
1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
1200
1201
1202
1203
1204
1205
1206
1207
1208
1209
1210
1211
1212
1213
1214
1215
1216
1217
1218
1219
1220
1221
1222
1223
1224
1225
1226
1227
1228
1229
1230
1231
1232
1233
1234
1235
1236
1237
1238
1239
1240
1241
1242
1243
1244
1245
1246
1247
1248
1249
1250
1251
1252
1253
1254
1255
1256
1257
1258
1259
1260
1261
1262
1263
1264
1265
1266
1267
1268
1269
1270
1271
1272
1273
1274
1275
1276
1277
1278
1279
1280
1281
1282
1283
1284
1285

    <p class=todo> Master journal file pointers?

    <p>
      The following requirements describe step 1 of the above procedure in
      more detail.

    REQ H21014
      When required to attempt to detect a <i>hot-journal file</i>, SQLite
      shall first use the xAccess() method of the VFS layer to check if a
      journal file exists in the file-system.

    REQ H21051
      If the call to xAccess() required by H21014 fails (due to an IO error or
      similar), then SQLite shall abandon the attempt to open a <i>read-only
      transaction</i>, relinquish the <i>shared lock</i> held on the database
      file and return an error to the user.

    REQ H21015
      When required to attempt to detect a <i>hot-journal file</i>, if the
      call to xAccess() required by H21014 indicates that a journal file does
      not exist, then SQLite shall conclude that there is no <i>hot-journal
      file</i> in the file system and therefore that no <i>hot journal
      rollback</i> is required.

    <p>
      The following requirements describe step 2 of the above procedure in
      more detail.

    REQ H21016
      When required to attempt to detect a <i>hot-journal file</i>, if the
      call to xAccess() required by H21014 indicates that a journal file
      is present, then the xCheckReservedLock() method of the database file
      file-handle is invoked to determine whether or not some other 
      process is holding a <i>reserved</i> or greater lock on the database 
      file.

    REQ H21052
      If the call to xCheckReservedLock() required by H21016 fails (due to an
      IO or other internal VFS error), then SQLite shall abandon the attempt
      to open a <i>read-only transaction</i>, relinquish the <i>shared lock</i>
      held on the database file and return an error to the user.

    REQ H21017
      If the call to xCheckReservedLock() required by H21016 indicates that
      some other <i>database connection</i> is holding a <i>reserved</i>
      or greater lock on the database file, then SQLite shall conclude that
      there is no <i>hot journal file</i>. In this case the attempt to detect 
      a <i>hot journal file</i> is concluded.

    <p>
      The following requirements describe step 3 of the above procedure in
      more detail.

    REQ H21044
      If while attempting to detect a <i>hot-journal file</i> the call to
      xCheckReservedLock() indicates that no process holds a <i>reserved</i>
      or greater lock on the <i>database file</i>, then SQLite shall open
      a file handle on the potentially hot journal file using the VFS xOpen()
      method.

    REQ H21053
      If the call to xOpen() required by H21044 fails (due to an IO or other
      internal VFS error), then SQLite shall abandon the attempt to open a
      <i>read-only transaction</i>, relinquish the <i>shared lock</i> held on
      the database file and return an error to the user.

    REQ H21045
      After successfully opening a file-handle on a potentially hot journal
      file, SQLite shall query the file for its size in bytes using the
      xFileSize() method of the open file handle. 

    REQ H21054
      If the call to xFileSize() required by H21045 fails (due to an IO or
      other internal VFS error), then SQLite shall abandon the attempt to open
      a <i>read-only transaction</i>, relinquish the <i>shared lock</i> held on
      the database file, close the file handle opened on the journal file and
      return an error to the user.

    REQ H21046
      If the size of a potentially hot journal file is revealed to be zero
      bytes by a query required by H21045, then SQLite shall close the
      file handle opened on the journal file and delete the journal file using
      a call to the VFS xDelete() method. In this case SQLite shall conclude
      that there is no <i>hot journal file</i>.

    REQ H21055
      If the call to xDelete() required by H21045 fails (due to an IO or
      other internal VFS error), then SQLite shall abandon the attempt to open
      a <i>read-only transaction</i>, relinquish the <i>shared lock</i> held on
      the database file and return an error to the user.

    <p>
      The following requirements describe step 4 of the above procedure in
      more detail.

    REQ H21047
      If the size of a potentially hot journal file is revealed to be greater
      than zero bytes by a query required by H21045, then SQLite shall attempt
      to upgrade the <i>shared lock</i> held by the <i>database connection</i>
      on the <i>database file</i> directly to an <i>exclusive lock</i>.

    REQ H21048
      If an attempt to upgrade to an <i>exclusive lock</i> prescribed by 
      H21047 fails for any reason, then SQLite shall release all locks held by
      the <i>database connection</i> and close the file handle opened on the
      <i>journal file</i>. The attempt to open a <i>read-only transaction</i>
      shall be deemed to have failed and an error returned to the user.

    <p>
      Finally, the following requirements describe step 5 of the above
      procedure in more detail.

    REQ H21049
      If, as part of the <i>hot journal file</i> detection process, the
      attempt to upgrade to an <i>exclusive lock</i> mandated by H21047 is
      successful, then SQLite shall query the file-system using the xAccess()
      method of the VFS implementation to test whether or not the journal
      file is still present in the file-system.

    REQ H21056
      If the call to xAccess() required by H21049 fails (due to an IO or
      other internal VFS error), then SQLite shall abandon the attempt to open
      a <i>read-only transaction</i>, relinquish the lock held on the 
      database file, close the file handle opened on the journal file and
      return an error to the user.
    
    REQ H21057
      If the call to xAccess() required by H21049 reveals that the journal
      file is no longer present in the file system, then SQLite shall abandon 
      the attempt to open a <i>read-only transaction</i>, relinquish the 
      lock held on the database file, close the file handle opened on the 
      journal file and return an SQLITE_BUSY error to the user.

    REQ H21050
      If the xAccess() query required by H21049 reveals that the journal
      file is still present in the file system, then SQLite shall conclude
      that the journal file is a <i>hot journal file</i> that needs to
      be rolled back. SQLite shall immediately begin <i>hot journal
      rollback</i>.


  <h3 id=cache_validation>Cache Validation</h3>
    <p>
      When a <i>database connection</i> opens a <i>read transaction</i>, the
      <i>page cache</i> may already contain data associated with the
      <i>database connection</i>. However, if another process has modified 







|
<
<
<

|
<
<
<
<

|
<
<
<
<
<





|
<
<
<
<
<
<

|
<
<
<
<

|
<
<
<
<
<





|
<
<
<
<
<

|
<
<
<
<

|
<
<
<

|
<
<
<
<
<

|
<
<
<
<
<

|
<
<
<
<





|
<
<
<
<

|
<
<
<
<
<





|
<
<
<
<
<

|
<
<
<
<
<
|
|
<
<
<
<
<

|
<
<
<
<
<







1085
1086
1087
1088
1089
1090
1091
1092



1093
1094




1095
1096





1097
1098
1099
1100
1101
1102






1103
1104




1105
1106





1107
1108
1109
1110
1111
1112





1113
1114




1115
1116



1117
1118





1119
1120





1121
1122




1123
1124
1125
1126
1127
1128




1129
1130





1131
1132
1133
1134
1135
1136





1137
1138





1139
1140





1141
1142





1143
1144
1145
1146
1147
1148
1149

    <p class=todo> Master journal file pointers?

    <p>
      The following requirements describe step 1 of the above procedure in
      more detail.

    REQ H35140




    REQ H35510





    REQ H35150






    <p>
      The following requirements describe step 2 of the above procedure in
      more detail.

    REQ H35160







    REQ H35520





    REQ H35170






    <p>
      The following requirements describe step 3 of the above procedure in
      more detail.

    REQ H35440






    REQ H35530





    REQ H35450




    REQ H35540






    REQ H35460






    REQ H35550





    <p>
      The following requirements describe step 4 of the above procedure in
      more detail.

    REQ H35470





    REQ H35480






    <p>
      Finally, the following requirements describe step 5 of the above
      procedure in more detail.

    REQ H35490






    REQ H35560






    REQ H35570






    REQ H35500







  <h3 id=cache_validation>Cache Validation</h3>
    <p>
      When a <i>database connection</i> opens a <i>read transaction</i>, the
      <i>page cache</i> may already contain data associated with the
      <i>database connection</i>. However, if another process has modified 
1300
1301
1302
1303
1304
1305
1306
1307
1308
1309
1310
1311
1312
1313
1314
1315
1316
1317
1318
1319
1320
1321
1322
1323
1324
1325
1326
1327
1328
1329
1330
1331
1332
1333
1334
1335
1336
1337
1338
1339
1340
1341
1342
1343
1344
1345
1346
1347
1348
1349
1350
1351
1352
1353
1354
1355
1356
1357
1358
1359
1360
1361
1362
1363
1364
1365
1366
1367
1368
1369
1370
1371
1372
1373
1374
1375
1376
1377
1378
1379
1380
1381
1382
1383
1384
1385
1386
1387
1388
1389
1390
1391
1392
1393
1394
1395
1396
1397
1398
1399
1400
1401
1402
1403
1404
1405
1406
      new <i>read-only transaction</i>, SQLite checks the value of the <i>file
      change counter</i> stored in the database file. If the value has not
      changed since the database file was unlocked, then the <i>page cache</i>
      entries can be trusted. If the value has changed, then the <i>page
      cache</i> entries cannot be trusted and all entries associated with
      the current <i>database connection</i> are discarded.
   
    REQ H21018
      When a file-handle open on a database file is unlocked, if the
      <i>page cache</i> contains one or more entries belonging to the
      associated <i>database connection</i>, SQLite shall store the value 
      of the <i>file change counter</i> internally.

    REQ H21019
      When required to perform <i>cache validation</i> as part of opening
      a <i>read transaction</i>, SQLite shall read a 16 byte block 
      starting at byte offset 24 of the <i>database file</i> using the xRead()
      method of the <i>database connections</i> file handle.

    <p class=todo>
      Why a 16 byte block? Why not 4? (something to do with encrypted
      databases).

    REQ H21020
      While performing <i>cache validation</i>, after loading the 16 byte
      block as required by H21019, SQLite shall compare the 32-bit big-endian
      integer stored in the first 4 bytes of the block to the most
      recently stored value of the <i>file change counter</i> (see H21018).
      If the values are not the same, then SQLite shall conclude that
      the contents of the cache are invalid.

    <p>
      Requirement H21005 (section <cite>open_read_only_trans</cite>) 
      specifies the action SQLite is required to take upon determining that 
      the cache contents are invalid.

  <h3 id=read_page_one>Page 1 and the Expected Page Size</h3>
    <p>
      As the last step in opening a <i>read transaction</i> on a database
      file that is more than 0 bytes in size, SQLite is required to load 
      data for page 1 of the database into the <i>page cache</i>, if it is 
      not already there. This is slightly more complicated than it seems, 
      as the database <i>page-size</i> is no known at this point.

    <p>
      Even though the database <i>page-size</i> cannot be known for sure,
      SQLite is usually able to guess correctly by assuming it to be equal to
      the connections <i>expected page size</i>. The <i>expected page size</i>
      is the value of the <i>page-size</i> field read from the 
      <i>database file header</i> while opening the database connection 
      (see section <cite>open_new_connection</cite>), or the <i>page-size</i>
      of the database file when the most <i>read transaction</i> was concluded.

    REQ H21021
      During the conclusion of a <i>read transaction</i>, before unlocking
      the database file, SQLite shall set the connections 
      <i>expected page size</i> to the current database <i>page-size</i>.

    REQ H21022
      As part of opening a new <i>read transaction</i>, immediately after 
      performing <i>cache validation</i>, if there is no data for database
      page 1 in the <i>page cache</i>, SQLite shall read <i>N</i> bytes from
      the start of the database file using the xRead() method of the 
      connections file handle, where <i>N</i> is the connections current 
      <i>expected page size</i> value.

    REQ H21023
      If page 1 data is read as required by H21023, then the value of the
      <i>page-size</i> field that appears in the database file header that
      consumes the first 100 bytes of the read block is not the same as the
      connections current <i>expected page size</i>, then the 
      <i>expected page size</i> is set to this value, the database file is
      unlocked and the entire procedure to open a <i>read transaction</i>
      is repeated.

    REQ H21024
      If page 1 data is read as required by H21023, then the value of the
      <i>page-size</i> field that appears in the database file header that
      consumes the first 100 bytes of the read block is the same as the
      connections current <i>expected page size</i>, then the block of data
      read is stored in the <i>page cache</i> as page 1.

  <h2>Reading Database Data</h2>

  <p class=todo>
    Add something about checking the page-cache first etc.

  <h2>Ending a Read-only Transaction</h2>
    <p>
      To end a <i>read-only transaction</i>, SQLite simply relinquishes the
      <i>shared lock</i> on the file-handle open on the database file. No
      other action is required.

    REQ H21013
      When required to end a <i>read-only transaction</i>, SQLite shall
      relinquish the <i>shared lock</i> held on the database file by
      calling the xUnlock() method of the file-handle.

    <p>
      See also requirements H21018 and H21021 above.

<h1 id=writing_data>Writing Data</h1>
  <p>
    Using DDL or DML SQL statements, SQLite users may modify the contents and
    size of a database file. Exactly how changes to the logical database are
    translated to modifications to the database file is described in 
    <cite>ff_sqlitert_requirements</cite>. From the point of view of the







|
<
<
<
<

|
<
<
<
<





|
<
<
<
<
<
<


|




















|
<
<
<

|
<
<
<
<
<
<

|
<
<
<
<
<
<
<

|
<
<
<
<
<












|
<
<
<


|







1164
1165
1166
1167
1168
1169
1170
1171




1172
1173




1174
1175
1176
1177
1178
1179






1180
1181
1182
1183
1184
1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
1200
1201
1202
1203



1204
1205






1206
1207







1208
1209





1210
1211
1212
1213
1214
1215
1216
1217
1218
1219
1220
1221
1222



1223
1224
1225
1226
1227
1228
1229
1230
1231
1232
      new <i>read-only transaction</i>, SQLite checks the value of the <i>file
      change counter</i> stored in the database file. If the value has not
      changed since the database file was unlocked, then the <i>page cache</i>
      entries can be trusted. If the value has changed, then the <i>page
      cache</i> entries cannot be trusted and all entries associated with
      the current <i>database connection</i> are discarded.
   
    REQ H35180





    REQ H35190





    <p class=todo>
      Why a 16 byte block? Why not 4? (something to do with encrypted
      databases).

    REQ H35200







    <p>
      Requirement H35050 (section <cite>open_read_only_trans</cite>) 
      specifies the action SQLite is required to take upon determining that 
      the cache contents are invalid.

  <h3 id=read_page_one>Page 1 and the Expected Page Size</h3>
    <p>
      As the last step in opening a <i>read transaction</i> on a database
      file that is more than 0 bytes in size, SQLite is required to load 
      data for page 1 of the database into the <i>page cache</i>, if it is 
      not already there. This is slightly more complicated than it seems, 
      as the database <i>page-size</i> is no known at this point.

    <p>
      Even though the database <i>page-size</i> cannot be known for sure,
      SQLite is usually able to guess correctly by assuming it to be equal to
      the connections <i>expected page size</i>. The <i>expected page size</i>
      is the value of the <i>page-size</i> field read from the 
      <i>database file header</i> while opening the database connection 
      (see section <cite>open_new_connection</cite>), or the <i>page-size</i>
      of the database file when the most <i>read transaction</i> was concluded.

    REQ H35210




    REQ H35220







    REQ H35230








    REQ H35240






  <h2>Reading Database Data</h2>

  <p class=todo>
    Add something about checking the page-cache first etc.

  <h2>Ending a Read-only Transaction</h2>
    <p>
      To end a <i>read-only transaction</i>, SQLite simply relinquishes the
      <i>shared lock</i> on the file-handle open on the database file. No
      other action is required.

    REQ H35130




    <p>
      See also requirements H35180 and H35210 above.

<h1 id=writing_data>Writing Data</h1>
  <p>
    Using DDL or DML SQL statements, SQLite users may modify the contents and
    size of a database file. Exactly how changes to the logical database are
    translated to modifications to the database file is described in 
    <cite>ff_sqlitert_requirements</cite>. From the point of view of the
1788
1789
1790
1791
1792
1793
1794
1795
1796
1797
1798
1799
1800
1801
1802
1803
1804
1805
1806
1807
1808
1809
1810
1811
1812
1813
1814
1815
1816
1817
1818
1819
1820
1821
1822
1823
1824
1825
1826
1827
1828
1829
1830
1831
1832
1833
1834
1835
1836
1837
1838
1839
1840
1841
1842
1843
1844
1845
1846
1847
1848
1849
1850
1851
1852
1853
1854
1855
1856
1857
1858
1859
1860
1861
1862
1863
1864
1865
1866
1867
1868
1869
1870
1871
1872
1873
1874
          to the start of it using a single call to the file handles xWrite 
          method.
    </ol>

    <p>
      Requirements describing step 1 of the above procedure in detail:

    REQ H21035
      When required to open a <i>write transaction</i> on the database, 
      SQLite shall first open a <i>read transaction</i>, if the <i>database
      connection</i> in question has not already opened one.

    REQ H21036
      When required to open a <i>write transaction</i> on the database, after
      ensuring a <i>read transaction</i> has already been opened, SQLite 
      shall obtain a <i>reserved lock</i> on the database file by calling
      the xLock method of the file-handle open on the database file.

    REQ H21058
      If an attempt to acquire a <i>reserved lock</i> prescribed by 
      requirement H21036 fails, then SQLite shall deem the attempt to
      open a <i>write transaction</i> to have failed and return an error 
      to the user.

    <p>
      Requirements describing step 2 of the above procedure in detail:

    REQ H21037
      When required to open a <i>write transaction</i> on the database, after
      obtaining a <i>reserved lock</i> on the database file, SQLite shall
      open a read/write file-handle on the corresponding <i>journal file</i>.

    REQ H21038
      When required to open a <i>write transaction</i> on the database, after
      opening a file-handle on the <i>journal file</i>, SQLite shall append
      a <i>journal header</i> to the (currently empty) <i>journal file</i>.

    <h4 id=writing_journal_header>Writing a Journal Header</h4>

    <p>
      Requirements describing how a <i>journal header</i> is appended to
      a journal file:

    REQ H21068
      When required to append a <i>journal header</i> to the <i>journal
      file</i>, SQLite shall do so by writing a block of <i>sector-size</i> 
      bytes using a single call to the xWrite method of the file-handle
      open on the <i>journal file</i>. The block of data written shall begin
      at the smallest sector-size aligned offset at or following the current
      end of the <i>journal file</i>.

    REQ H21069
      The first 8 bytes of the <i>journal header</i> required to be written
      by H21068 shall contain the following values, in order from byte offset 0
      to 7: 0xd9, 0xd5, 0x05, 0xf9, 0x20, 0xa1, 0x63 and 0xd7.

    REQ H21070
      Bytes 8-11 of the <i>journal header</i> required to be written by 
      H21068 shall contain 0x00.

    REQ H21071
      Bytes 12-15 of the <i>journal header</i> required to be written by 
      H21068 shall contain the number of pages that the database file
      contained when the current <i>write-transaction</i> was started,
      formatted as a 4-byte big-endian unsigned integer.

    REQ H21072
      Bytes 16-19 of the <i>journal header</i> required to be written by 
      H21068 shall contain pseudo-randomly generated values.

    REQ H21073
      Bytes 20-23 of the <i>journal header</i> required to be written by 
      H21068 shall contain the <i>sector size</i> used by the VFS layer,
      formatted as a 4-byte big-endian unsigned integer.

    REQ H21074
      Bytes 24-27 of the <i>journal header</i> required to be written by 
      H21068 shall contain the <i>page size</i> used by the database at
      the start of the <i>write transaction</i>, formatted as a 4-byte
      big-endian unsigned integer.

  <h3 id=modifying_appending_truncating>
    Modifying, Adding or Truncating a Database Page
  </h3>

    <p>
      When the end-user executes a DML or DDL SQL statement to modify the







|
<
<
<

|
<
<
<
<

|
<
<
<
<




|
<
<
<

|
<
<
<







|
<
<
<
<
<
<

|
<
<
<

|
<
<

|
<
<
<
<

|
<
<

|
<
<
<

|
<
<
<
<







1614
1615
1616
1617
1618
1619
1620
1621



1622
1623




1624
1625




1626
1627
1628
1629
1630



1631
1632



1633
1634
1635
1636
1637
1638
1639
1640






1641
1642



1643
1644


1645
1646




1647
1648


1649
1650



1651
1652




1653
1654
1655
1656
1657
1658
1659
          to the start of it using a single call to the file handles xWrite 
          method.
    </ol>

    <p>
      Requirements describing step 1 of the above procedure in detail:

    REQ H35350




    REQ H35360





    REQ H35580





    <p>
      Requirements describing step 2 of the above procedure in detail:

    REQ H35370




    REQ H35380




    <h4 id=writing_journal_header>Writing a Journal Header</h4>

    <p>
      Requirements describing how a <i>journal header</i> is appended to
      a journal file:

    REQ H35680







    REQ H35690




    REQ H35700



    REQ H35710





    REQ H35720



    REQ H35730




    REQ H35740





  <h3 id=modifying_appending_truncating>
    Modifying, Adding or Truncating a Database Page
  </h3>

    <p>
      When the end-user executes a DML or DDL SQL statement to modify the
1883
1884
1885
1886
1887
1888
1889
1890
1891
1892
1893
1894
1895
1896
1897
1898
1899
1900
1901
1902
1903
1904
1905
1906
1907
1908
1909
1910
1911
1912
1913
1914
1915
1916
1917
1918
1919
1920
1921
1922
1923
1924
1925
1926
1927
1928
1929
1930
1931
1932
1933
1934
1935
1936
1937
1938
1939
1940
1941
1942
1943
1944
1945
1946
1947
1948
1949
1950
1951
1952
1953
1954
1955
1956
1957
1958
1959
1960
1961
1962
1963
1964
1965
1966
1967
1968
1969
1970
1971
1972
1973
1974
1975
1976
1977
1978
1979
1980
1981
1982
1983
1984
1985
1986
1987
1988
1989
      may need to be restored by a rollback operation, the page must be
      <i>journalled</i>. <i>Journalling a page</i> is the process of copying
      that pages original data into the journal file so that it can be
      recovered if the <i>write transaction</i> is rolled back. The process
      of journalling a page is described in section 
      <cite>journalling_a_page</cite>.

    REQ H21059
      When required to modify the contents of an existing database page that
      existed and was not a <i>free-list leaf page</i> when the <i>write
      transaction</i> was opened, SQLite shall journal the page if it has not
      already been journalled within the current <i>write transaction</i>.

    REQ H21060
      When required to modify the contents of an existing database page,
      SQLite shall update the cached version of the database page content
      stored as part of the <i>page cache entry</i> associated with the page.

    <p>
      When a new database page is appended to a database file, there is
      no requirement to add a record to the <i>journal file</i>. If a 
      rollback is required the database file will simply be truncated back 
      to its original size based on the value stored at byte offset 12
      of the <i>journal file</i>.

    REQ H21061
      When required to append a new database page to the database file,
      SQLite shall create a new <i>page cache entry</i> corresponding to
      the new page and insert it into the <i>page cache</i>. The <i>dirty
      flag</i> of the new <i>page cache entry</i> shall be set.

    <p>
      If required to truncate a database page from the end of the database
      file, the associated <i>page cache entry</i> is discarded. The adjusted
      size of the database file is stored internally. The database file
      is not actually truncated until the current <i>write transaction</i>
      is committed (see section <cite>committing_a_transaction</cite>).

    REQ H21062
      When required to truncate (remove) a database page that existed and was
      not a <i>free-list leaf page</i> when the <i>write transaction</i> was
      opened from the end of a database file, SQLite shall journal the page if
      it has not already been journalled within the current <i>write
      transaction</i>.

    REQ H21063
      When required to truncate a database page from the end of the database
      file, SQLite shall discard the associated <i>page cache entry</i>
      from the page cache.

  <h4 id=journalling_a_page>Journalling a Database Page</h4>

    <p>
      A page is journalled by adding a <i>journal record</i> to the <i>
      journal file</i>. The format of a <i>journal record</i> is described
      in section <cite>journal_record_format</cite>.

    REQ H21027
      When required to <i>journal a database page</i>, SQLite shall first
      append the <i>page number</i> of the page being journalled to the
      <i>journal file</i>, formatted as a 4-byte big-endian unsigned integer,
      using a single call to the xWrite method of the file-handle opened
      on the journal file.

    REQ H21028
      When required to <i>journal a database page</i>, if the attempt to 
      append the <i>page number</i> to the journal file is successful, 
      then the current page data (<i>page-size</i> bytes) shall be appended 
      to the journal file, using a single call to the xWrite method of the 
      file-handle opened on the journal file.

    REQ H21029
      When required to <i>journal a database page</i>, if the attempt to 
      append the current page data to the journal file is successful, 
      then SQLite shall append a 4-byte big-endian integer checksum value 
      to the to the journal file, using a single call to the xWrite method 
      of the file-handle opened on the journal file.

    <p>
      The checksum value written to the <i>journal file</i> immediately after
      the page data (requirement H21029), is a function of both the page
      data and the <i>checksum initializer</i> field stored in the 
      <i>journal header</i> (see section <cite>journal_header_format</cite>).
      Specifically, it is the sum of the <i>checksum initializer</i> and
      the value of every 200th byte of page data interpreted as an 8-bit
      unsigned integer, starting with the (<i>page-size</i> % 200)'th 
      byte of page data. For example, if the <i>page-size</i> is 1024 bytes,
      then a checksum is calculated by adding the values of the bytes at
      offsets 23, 223, 423, 623, 823 and 1023 (the last byte of the page)
      together with the value of the <i>checksum initializer</i>.

    REQ H21030
      The checksum value written to the <i>journal file</i> by the write
      required by H21029 shall be equal to the sum of the <i>checksum
      initializer</i> field stored in the <i>journal header</i> (H21070) and
      every 200th byte of the page data, beginning with the 
      (<i>page-size</i> % 200)th byte.

    <p>
      The '%' character is used in requirement H21030 to represent the
      modulo operator, just as it is in programming languages such as C, Java
      and Javascript.

  <h3 id=syncing_journal_file>Syncing the Journal File</h3>

    <p>
      Even after the original data of a database page has been written into







|
<
<
<
<

|
<
<
<








|
<
<
<
<








|
<
<
<
<
<

|
<
<
<








|
<
<
<
<
<

|
<
<
<
<
<

|
<
<
<
<
<



|










|
<
<
<
<
<


|







1668
1669
1670
1671
1672
1673
1674
1675




1676
1677



1678
1679
1680
1681
1682
1683
1684
1685
1686




1687
1688
1689
1690
1691
1692
1693
1694
1695





1696
1697



1698
1699
1700
1701
1702
1703
1704
1705
1706





1707
1708





1709
1710





1711
1712
1713
1714
1715
1716
1717
1718
1719
1720
1721
1722
1723
1724
1725





1726
1727
1728
1729
1730
1731
1732
1733
1734
1735
      may need to be restored by a rollback operation, the page must be
      <i>journalled</i>. <i>Journalling a page</i> is the process of copying
      that pages original data into the journal file so that it can be
      recovered if the <i>write transaction</i> is rolled back. The process
      of journalling a page is described in section 
      <cite>journalling_a_page</cite>.

    REQ H35590





    REQ H35600




    <p>
      When a new database page is appended to a database file, there is
      no requirement to add a record to the <i>journal file</i>. If a 
      rollback is required the database file will simply be truncated back 
      to its original size based on the value stored at byte offset 12
      of the <i>journal file</i>.

    REQ H35610





    <p>
      If required to truncate a database page from the end of the database
      file, the associated <i>page cache entry</i> is discarded. The adjusted
      size of the database file is stored internally. The database file
      is not actually truncated until the current <i>write transaction</i>
      is committed (see section <cite>committing_a_transaction</cite>).

    REQ H35620






    REQ H35630




  <h4 id=journalling_a_page>Journalling a Database Page</h4>

    <p>
      A page is journalled by adding a <i>journal record</i> to the <i>
      journal file</i>. The format of a <i>journal record</i> is described
      in section <cite>journal_record_format</cite>.

    REQ H35270






    REQ H35280






    REQ H35290






    <p>
      The checksum value written to the <i>journal file</i> immediately after
      the page data (requirement H35290), is a function of both the page
      data and the <i>checksum initializer</i> field stored in the 
      <i>journal header</i> (see section <cite>journal_header_format</cite>).
      Specifically, it is the sum of the <i>checksum initializer</i> and
      the value of every 200th byte of page data interpreted as an 8-bit
      unsigned integer, starting with the (<i>page-size</i> % 200)'th 
      byte of page data. For example, if the <i>page-size</i> is 1024 bytes,
      then a checksum is calculated by adding the values of the bytes at
      offsets 23, 223, 423, 623, 823 and 1023 (the last byte of the page)
      together with the value of the <i>checksum initializer</i>.

    REQ H35300






    <p>
      The '%' character is used in requirement H35300 to represent the
      modulo operator, just as it is in programming languages such as C, Java
      and Javascript.

  <h3 id=syncing_journal_file>Syncing the Journal File</h3>

    <p>
      Even after the original data of a database page has been written into
2013
2014
2015
2016
2017
2018
2019
2020
2021
2022
2023
2024
2025
2026
2027
2028
2029
2030
2031
2032
2033
2034
2035
2036
2037
2038
2039
2040
2041
2042
2043
2044
2045
2046
2047
2048
2049
2050
2051
2052
2053
2054
2055
2056
2057
2058
2059
2060
2061
2062
2063
2064
2065
2066
2067
2068

    <p>
      If all three of the steps enumerated above are executed successfully,
      then it is safe to modify the content of the <i>journalled</i> 
      database pages within the database file itself. The combination of
      the three steps above is refered to as <i>syncing the journal file</i>.
 
    REQ H21075
      When required to <i>sync the journal file</i>, SQLite shall invoke the
      xSync method of the file handle open on the <i>journal file</i>.

    REQ H21076
      When required to <i>sync the journal file</i>, after invoking the
      xSync method as required by H21075, SQLite shall update the <i>record
      count</i> of the <i>journal header</i> most recently written to the
      <i>journal file</i>. The 4-byte field shall be updated to contain
      the number of <i>journal records</i> that have been written to the
      <i>journal file</i> since the <i>journal header</i> was written,
      formatted as a 4-byte big-endian unsigned integer.

    REQ H21077
      When required to <i>sync the journal file</i>, after updating the
      <i>record count</i> field of a <i>journal header</i> as required by
      H21076, SQLite shall invoke the xSync method of the file handle open 
      on the <i>journal file</i>.

  <h3 id=upgrading_to_exclusive_lock>Upgrading to an Exclusive Lock</h3>
    <p>
      Before the content of a page modified within the <i>page cache</i> may
      be written to the database file, an <i>exclusive lock</i> must be held
      on the database file. The purpose of this lock is to prevent another
      connection from reading from the database file while the first 
      connection is midway through writing to it. Whether the reason for
      writing to the database file is because a transaction is being committed,
      or to free up space within the <i>page cache</i>, upgrading to an 
      <i>exclusive lock</i> always occurs immediately after 
      <i>syncing the journal file</i>.

    REQ H21078
      When required to upgrade to an <i>exclusive lock</i> as part of a write
      transaction, SQLite shall first attempt to obtain a <i>pending lock</i>
      on the database file if one is not already held by invoking the xLock
      method of the file handle opened on the <i>database file</i>.

    REQ H21079
      When required to upgrade to an <i>exclusive lock</i> as part of a write
      transaction, after successfully obtaining a <i>pending lock</i> SQLite
      shall attempt to obtain an <i>exclusive lock</i> by invoking the
      xLock method of the file handle opened on the <i>database file</i>.

    <p class=todo>
      What happens if the exclusive lock cannot be obtained? It is not
      possible for the attempt to upgrade from a reserved to a pending 
      lock to fail.

  <h3 id=committing_a_transaction>Committing a Transaction</h3>







|
<
<

|
<
<
<
<
<
<
<

|
<
<
<
<













|
<
<
<
<

|
<
<
<
<







1759
1760
1761
1762
1763
1764
1765
1766


1767
1768







1769
1770




1771
1772
1773
1774
1775
1776
1777
1778
1779
1780
1781
1782
1783
1784




1785
1786




1787
1788
1789
1790
1791
1792
1793

    <p>
      If all three of the steps enumerated above are executed successfully,
      then it is safe to modify the content of the <i>journalled</i> 
      database pages within the database file itself. The combination of
      the three steps above is refered to as <i>syncing the journal file</i>.
 
    REQ H35750



    REQ H35760








    REQ H35770





  <h3 id=upgrading_to_exclusive_lock>Upgrading to an Exclusive Lock</h3>
    <p>
      Before the content of a page modified within the <i>page cache</i> may
      be written to the database file, an <i>exclusive lock</i> must be held
      on the database file. The purpose of this lock is to prevent another
      connection from reading from the database file while the first 
      connection is midway through writing to it. Whether the reason for
      writing to the database file is because a transaction is being committed,
      or to free up space within the <i>page cache</i>, upgrading to an 
      <i>exclusive lock</i> always occurs immediately after 
      <i>syncing the journal file</i>.

    REQ H35780





    REQ H35790





    <p class=todo>
      What happens if the exclusive lock cannot be obtained? It is not
      possible for the attempt to upgrade from a reserved to a pending 
      lock to fail.

  <h3 id=committing_a_transaction>Committing a Transaction</h3>
2114
2115
2116
2117
2118
2119
2120
2121
2122
2123
2124
2125
2126
2127
2128
2129
2130
2131
2132
2133
2134
2135
2136
2137
2138
2139
2140
2141
2142
2143
2144
2145
2146
2147
2148
2149
2150
2151
2152
2153
2154
2155
2156
2157
2158
2159
2160
2161
2162
2163
2164
2165
2166
2167
2168
2169
2170
2171
2172
2173
2174
2175
2176
2177
2178
2179
2180
2181
2182
2183
2184
    <p class=todo>
      Expand on and explain the above a bit.
    
    <p>
      The following requirements describe the steps enumerated above in more
      detail.

    REQ H21080
      When required to <i>commit a write-transaction</i>, SQLite shall
      modify page 1 to increment the value stored in the <i>change counter</i>
      field of the <i>database file header</i>.

    <p>
      The <i>change counter</i> is a 4-byte big-endian integer field stored
      at byte offset 24 of the <i>database file</i>. The modification to page 1
      required by H21080 is made using the process described in section
      <cite>modifying_appending_truncating</cite>. If page 1 has not already
      been journalled as a part of the current write-transaction, then
      incrementing the <i>change counter</i> may require that page 1 be
      journalled. In all cases the <i>page cache entry</i> corresponding to
      page 1 becomes a <i>dirty page</i> as part of incrementing the <i>change
      counter</i> value.

    REQ H21081
      When required to <i>commit a write-transaction</i>, after incrementing
      the <i>change counter</i> field, SQLite shall <i>sync the journal
      file</i>.

    REQ H21082
      When required to <i>commit a write-transaction</i>, after <i>syncing
      the journal file</i> as required by H21081, if an <i>exclusive lock</i>
      on the database file is not already held, SQLite shall attempt to
      <i>upgrade to an exclusive lock</i>.

    REQ H21083
      When required to <i>commit a write-transaction</i>, after <i>syncing
      the journal file</i> as required by H21081 and ensuring that an
      <i>exclusive lock</i> is held on the database file as required by
      H21083, SQLite shall copy the contents of all <i>dirty page</i>
      stored in the <i>page cache</i> into the <i>database file</i> using
      calls to the xWrite method of the <i>database connection</i> file
      handle. Each call to xWrite shall write the contents of a single
      <i>dirty page</i> (<i>page-size</i> bytes of data) to the database
      file. Dirty pages shall be written in order of <i>page number</i>, 
      from lowest to highest.

    REQ H21084
      When required to <i>commit a write-transaction</i>, after copying the
      contents of any <i>dirty pages</i> to the database file as required
      by H21083, SQLite shall sync the database file by invoking the xSync
      method of the <i>database connection</i> file handle.

    REQ H21085
      When required to <i>commit a write-transaction</i>, after syncing
      the database file as required by H21084, SQLite shall close the
      file-handle opened on the <i>journal file</i> and delete the
      <i>journal file</i> from the file system via a call to the VFS
      xDelete method.

    REQ H21086
      When required to <i>commit a write-transaction</i>, after deleting
      the <i>journal file</i> as required by H21085, SQLite shall relinquish
      all locks held on the <i>database file</i> by invoking the xUnlock
      method of the <i>database connection</i> file handle.

    <p class=todo>
      Is the shared lock held after committing a <i>write transaction</i>?

  <h3>Purging a Dirty Page</h3>

    <p>







|
<
<
<




|







|
<
<
<

|
<
<
<
<

|
<
<
<
<
<
<
<
<
<
<

|
<
<
<
<

|
<
<
<
<
<

|
<
<
<
<







1839
1840
1841
1842
1843
1844
1845
1846



1847
1848
1849
1850
1851
1852
1853
1854
1855
1856
1857
1858
1859



1860
1861




1862
1863










1864
1865




1866
1867





1868
1869




1870
1871
1872
1873
1874
1875
1876
    <p class=todo>
      Expand on and explain the above a bit.
    
    <p>
      The following requirements describe the steps enumerated above in more
      detail.

    REQ H35800




    <p>
      The <i>change counter</i> is a 4-byte big-endian integer field stored
      at byte offset 24 of the <i>database file</i>. The modification to page 1
      required by H35800 is made using the process described in section
      <cite>modifying_appending_truncating</cite>. If page 1 has not already
      been journalled as a part of the current write-transaction, then
      incrementing the <i>change counter</i> may require that page 1 be
      journalled. In all cases the <i>page cache entry</i> corresponding to
      page 1 becomes a <i>dirty page</i> as part of incrementing the <i>change
      counter</i> value.

    REQ H35810




    REQ H35820





    REQ H35830











    REQ H35840





    REQ H35850






    REQ H35860





    <p class=todo>
      Is the shared lock held after committing a <i>write transaction</i>?

  <h3>Purging a Dirty Page</h3>

    <p>
2199
2200
2201
2202
2203
2204
2205
2206
2207
2208
2209
2210
2211
2212
2213
2214
2215
2216
2217
2218
2219
2220
2221
2222
2223
2224
2225
2226
2227
2228
2229
2230
2231
2232
2233
2234
2235
      for a <i>writable dirty page</i>, as defined in section
      <cite>page_cache_algorithms</cite>. If the dirty page selected by the
      algorithms in section <cite>page_cache_algorithms</cite> for purging,
      SQLite is required to <i>sync the journal file</i>. Immediately after
      the journal file is synced, all dirty pages associated with the
      <i>database connection</i> are classified as <i>writable dirty pages</i>.

    REQ H21064
      When required to purge a <i>non-writable dirty page</i> from the 
      <i>page cache</i>, SQLite shall <i>sync the journal file</i> before
      proceding with the write operation required by H21067.

    REQ H21066
      After <i>syncing the journal file</i> as required by H21064, SQLite
      shall append a new <i>journal header</i> to the <i>journal file</i>
      before proceding with the write operation required by H21067. 

    <p>
      Appending a new <i>journal header</i> to the journal file is described
      in section <cite>writing_journal_header</cite>.

    <p>
      Once the dirty page being purged is writable, it is simply written
      into the database file.

    REQ H21067
      When required to purge a <i>page cache entry</i> that is a 
      <i>dirty page</i> SQLite shall write the page data into the database
      file, using a single call to the xWrite method of the <i>database
      connection</i> file handle.

  <h2 id="multifile_transactions">Multi-File Transactions</h2>

  <h2 id="statement_transactions">Statement Transactions</h2>










|
<
<
<

|
<
<
<









|
<
<
<
<







1891
1892
1893
1894
1895
1896
1897
1898



1899
1900



1901
1902
1903
1904
1905
1906
1907
1908
1909
1910




1911
1912
1913
1914
1915
1916
1917
      for a <i>writable dirty page</i>, as defined in section
      <cite>page_cache_algorithms</cite>. If the dirty page selected by the
      algorithms in section <cite>page_cache_algorithms</cite> for purging,
      SQLite is required to <i>sync the journal file</i>. Immediately after
      the journal file is synced, all dirty pages associated with the
      <i>database connection</i> are classified as <i>writable dirty pages</i>.

    REQ H35640




    REQ H35660




    <p>
      Appending a new <i>journal header</i> to the journal file is described
      in section <cite>writing_journal_header</cite>.

    <p>
      Once the dirty page being purged is writable, it is simply written
      into the database file.

    REQ H35670





  <h2 id="multifile_transactions">Multi-File Transactions</h2>

  <h2 id="statement_transactions">Statement Transactions</h2>



2244
2245
2246
2247
2248
2249
2250

      C API Requirements Document.
    <tr><td style="width:5ex" id="sql_sqlitert_requirements">[2]<td>
      SQL Requirements Document.
    <tr><td style="width:5ex" id="ff_sqlitert_requirements">[3]<td>
      File Format Requirements Document.
  </table>
}]</tcl>








>
1926
1927
1928
1929
1930
1931
1932
1933
      C API Requirements Document.
    <tr><td style="width:5ex" id="sql_sqlitert_requirements">[2]<td>
      SQL Requirements Document.
    <tr><td style="width:5ex" id="ff_sqlitert_requirements">[3]<td>
      File Format Requirements Document.
  </table>
}]</tcl>

Name change from req/hlr20000.txt to req/hlr30000.txt.
1



2

3
4

5
6
7



8


9
10



11



12
13
14


15
16



17
18
19
20


21
22
23


24

25




26
27
28


29


30

31


32


33
34

35

36


37

38
39

40

41



42

43

44
45


46
47
48
49
50
51
52
53
54
55


56
57



58
59
60
61

62
63
64


65

66


67



68
69


70
71
72
73




74
75
76



77
78
79
80



81




82

83
84
85
86
87
88
89
90






91
92
93
94
95
96
97



98
99
100




101




102

103
104
105



106
107
108


109


110
111

112
113





114


115

116
117




118
119


120
121



122


123


124

125
126
127
128
129

130
131
132
133
134
135

136
137

138


139
140
141


142

143
144



145

146
147






148
149
150
151



152
153

154

155
156
157



158
159

160

161




162
163
164
165
166
167

168

169
170


171

172
173




174
175








176
177
178
179
180
181

182
183
184




185

186



187





188
189
190
191
192
193
194
195
196
197
198


199
200




201

202



203




204
205
206
207

208
209

210


211
212

213
214






215
216





217
218
219
220
221
222

223
224
225
226
227
228
229
230
231
232
233
234
235









236
237






238



239

240
241

242
243
244
245
246


247
248






249
250



251



252





253


254
255

256
257
258
259
260
261
262






263
264
265
266



267
268
269
270
271



272



273



274




275
276
277
278


279
280


281
282




283
284

285
286

287


288
289
290
291
292
293


294
295
296
297

298

299
300


301
302
303
304
305




306

307







308
309
310
311
312

313
314
315
316
317
318
319




320
321
322

323





324










325
326
327
328
329
330


331
332
333
334


335




336
337




338
339
340
341




342
343
344
345
346
347


348
349



350
351
352






353
354
355
356

357
358
359
360
361
362
363


364







365

366





367
368
369
370
371

372
373


374




375

376
377
378


379
380


381
382
383
384
385



386
387

388
389


390

391
392
393



394
395
396






397
398



399


400




401
402
403
404
405


406
407
408

409


410
411
412
413
414
415





416
417
418
419
420
421



422
423





424
425

426


427




428
429


430
431


432

433
434
435
436


437
438
439
440
441




442
443


444
445
446
447
448
449
450
451
452




453



454





455





456
457
458
459
460
461

462
463
464
465
466
467
468

469
470
471
472
473


474






475
HLR H21001 



Except for the read operation required by H21007 and those reads made

as part of opening a read-only transaction, SQLite shall ensure that
a <i>database connection</i> has an open read-only or read/write

transaction when any data is read from the <i>database file</i>.

HLR H21002 



Aside from those read operations described by H21007 and H21XXX, SQLite


shall read data from the database file in aligned blocks of
<i>page-size</i> bytes, where <i>page-size</i> is the database page size



used by the database file.




HLR H21003 
While opening a <i>read-only transaction</i>, after successfully


obtaining a <i>shared lock</i> on the database file, SQLite shall
attempt to detect and roll back a <i>hot journal file</i> associated



with the same database file.

HLR H21004 
Assuming no errors have occured, then after attempting to detect and


roll back a <i>hot journal file</i>, if the <i>page cache</i> contains
any entries associated with the current <i>database connection</i>,
then SQLite shall validate the contents of the <i>page cache</i> by


testing the <i>file change counter</i>.  This procedure is known as

<i>cache validiation</i>.





HLR H21005 
If the cache validiate procedure prescribed by H21004 is required and


does not prove that the <i>page cache</i> entries associated with the


current <i>database connection</i> are valid, then SQLite shall discard

all entries associated with the current <i>database connection</i> from


the <i>page cache</i>.



HLR H21006 

When a new <i>database connection</i> is required, SQLite shall attempt

to open a file-handle on the database file. If the attempt fails, then


no new <i>database connection</i> is created and an error returned.


HLR H21007 

When a new <i>database connection</i> is required, after opening the

new file-handle, SQLite shall attempt to read the first 100 bytes



of the database file. If the attempt fails for any other reason than

that the opened file is less than 100 bytes in size, then

the file-handle is closed, no new <i>database connection</i> is created
and an error returned instead.



HLR H21008 
If the <i>database file header</i> is successfully read from a newly
opened database file, the connections <i>expected page-size</i> shall
be set to the value stored in the <i>page-size field</i> of the
database header.

HLR H21009 
If the <i>database file header</i> cannot be read from a newly opened
database file (because the file is less than 100 bytes in size), the


connections <i>expected page-size</i> shall be set to the compile time
value of the SQLITE_DEFAULT_PAGESIZE option.




HLR H21010 
When required to open a <i>read-only transaction</i> using a
<i>database connection</i>, SQLite shall first attempt to obtain

a <i>shared-lock</i> on the file-handle open on the database file.

HLR H21011 


If, while opening a <i>read-only transaction</i>, SQLite fails to obtain

the <i>shared-lock</i> on the database file, then the process is


abandoned, no transaction is opened and an error returned to the user.




HLR H21012 


If, while opening a <i>read-only transaction</i>, SQLite encounters
an error while attempting to detect or roll back a <i>hot journal
file</i>, then the <i>shared-lock</i> on the database file is released,
no transaction is opened and an error returned to the user.





HLR H21013 
When required to end a <i>read-only transaction</i>, SQLite shall



relinquish the <i>shared lock</i> held on the database file by
calling the xUnlock() method of the file-handle.

HLR H21014 



When required to attempt to detect a <i>hot-journal file</i>, SQLite




shall first use the xAccess() method of the VFS layer to check if a

journal file exists in the file-system.

HLR H21015 
When required to attempt to detect a <i>hot-journal file</i>, if the
call to xAccess() required by H21014 indicates that a journal file does
not exist, then SQLite shall conclude that there is no <i>hot-journal
file</i> in the file system and therefore that no <i>hot journal
rollback</i> is required.







HLR H21016 
When required to attempt to detect a <i>hot-journal file</i>, if the
call to xAccess() required by H21014 indicates that a journal file
is present, then the xCheckReservedLock() method of the database file
file-handle is invoked to determine whether or not some other
process is holding a <i>reserved</i> or greater lock on the database



file.

HLR H21017 




If the call to xCheckReservedLock() required by H21016 indicates that




some other <i>database connection</i> is holding a <i>reserved</i>

or greater lock on the database file, then SQLite shall conclude that
there is no <i>hot journal file</i>. In this case the attempt to detect
a <i>hot journal file</i> is concluded.




HLR H21018 
When a file-handle open on a database file is unlocked, if the


<i>page cache</i> contains one or more entries belonging to the


associated <i>database connection</i>, SQLite shall store the value
of the <i>file change counter</i> internally.


HLR H21019 





When required to perform <i>cache validation</i> as part of opening


a <i>read transaction</i>, SQLite shall read a 16 byte block

starting at byte offset 24 of the <i>database file</i> using the xRead()
method of the <i>database connections</i> file handle.





HLR H21020 


While performing <i>cache validation</i>, after loading the 16 byte
block as required by H21019, SQLite shall compare the 32-bit big-endian



integer stored in the first 4 bytes of the block to the most


recently stored value of the <i>file change counter</i> (see H21018).


If the values are not the same, then SQLite shall conclude that

the contents of the cache are invalid.

HLR H21021 
During the conclusion of a <i>read transaction</i>, before unlocking
the database file, SQLite shall set the connections

<i>expected page size</i> to the current database <i>page-size</i>.

HLR H21022 
As part of opening a new <i>read transaction</i>, immediately after
performing <i>cache validation</i>, if there is no data for database
page 1 in the <i>page cache</i>, SQLite shall read <i>N</i> bytes from

the start of the database file using the xRead() method of the
connections file handle, where <i>N</i> is the connections current

<i>expected page size</i> value.



HLR H21023 
If page 1 data is read as required by H21023, then the value of the


<i>page-size</i> field that appears in the database file header that

consumes the first 100 bytes of the read block is not the same as the
connections current <i>expected page size</i>, then the



<i>expected page size</i> is set to this value, the database file is

unlocked and the entire procedure to open a <i>read transaction</i>
is repeated.







HLR H21024 
If page 1 data is read as required by H21023, then the value of the
<i>page-size</i> field that appears in the database file header that



consumes the first 100 bytes of the read block is the same as the
connections current <i>expected page size</i>, then the block of data

read is stored in the <i>page cache</i> as page 1.


HLR H21027 
When required to <i>journal a database page</i>, SQLite shall first



append the <i>page number</i> of the page being journalled to the
<i>journal file</i>, formatted as a 4-byte big-endian unsigned integer,

using a single call to the xWrite method of the file-handle opened

on the journal file.





HLR H21028 
When required to <i>journal a database page</i>, if the attempt to
append the <i>page number</i> to the journal file is successful,
then the current page data (<i>page-size</i> bytes) shall be appended
to the journal file, using a single call to the xWrite method of the

file-handle opened on the journal file.


HLR H21029 


When required to <i>journal a database page</i>, if the attempt to

append the current page data to the journal file is successful,
then SQLite shall append a 4-byte big-endian integer checksum value




to the to the journal file, using a single call to the xWrite method
of the file-handle opened on the journal file.









HLR H21030 
The checksum value written to the <i>journal file</i> by the write
required by H21029 shall be equal to the sum of the <i>checksum
initializer</i> field stored in the <i>journal header</i> (H21070) and
every 200th byte of the page data, beginning with the

(<i>page-size</i> % 200)th byte.

HLR H21035 




When required to open a <i>write transaction</i> on the database,

SQLite shall first open a <i>read transaction</i>, if the <i>database



connection</i> in question has not already opened one.






HLR H21036 
When required to open a <i>write transaction</i> on the database, after
ensuring a <i>read transaction</i> has already been opened, SQLite
shall obtain a <i>reserved lock</i> on the database file by calling
the xLock method of the file-handle open on the database file.

HLR H21037 
When required to open a <i>write transaction</i> on the database, after
obtaining a <i>reserved lock</i> on the database file, SQLite shall
open a read/write file-handle on the corresponding <i>journal file</i>.



HLR H21038 




When required to open a <i>write transaction</i> on the database, after

opening a file-handle on the <i>journal file</i>, SQLite shall append



a <i>journal header</i> to the (currently empty) <i>journal file</i>.





HLR H21040 
When a <i>database connection</i> is closed, SQLite shall close the
associated file handle at the VFS level.


HLR H21042 

SQLite shall ensure that a <i>database connection</i> has an open


read-only or read/write transaction before using data stored in the <i>page
cache</i> to satisfy user queries.


HLR H21043 






When a <i>database connection</i> is closed, all associated <i>page
cache</i> entries shall be discarded.






HLR H21044 
If while attempting to detect a <i>hot-journal file</i> the call to
xCheckReservedLock() indicates that no process holds a <i>reserved</i>
or greater lock on the <i>database file</i>, then SQLite shall open
a file handle on the potentially hot journal file using the VFS xOpen()

method.

HLR H21045 
After successfully opening a file-handle on a potentially hot journal
file, SQLite shall query the file for its size in bytes using the
xFileSize() method of the open file handle.

HLR H21046 
If the size of a potentially hot journal file is revealed to be zero
bytes by a query required by H21045, then SQLite shall close the
file handle opened on the journal file and delete the journal file using
a call to the VFS xDelete() method. In this case SQLite shall conclude
that there is no <i>hot journal file</i>.










HLR H21047 






If the size of a potentially hot journal file is revealed to be greater



than zero bytes by a query required by H21045, then SQLite shall attempt

to upgrade the <i>shared lock</i> held by the <i>database connection</i>
on the <i>database file</i> directly to an <i>exclusive lock</i>.


HLR H21048 
If an attempt to upgrade to an <i>exclusive lock</i> prescribed by
H21047 fails for any reason, then SQLite shall release all locks held by
the <i>database connection</i> and close the file handle opened on the


<i>journal file</i>. The attempt to open a <i>read-only transaction</i>
shall be deemed to have failed and an error returned to the user.







HLR H21049 



If, as part of the <i>hot journal file</i> detection process, the



attempt to upgrade to an <i>exclusive lock</i> mandated by H21047 is





successful, then SQLite shall query the file-system using the xAccess()


method of the VFS implementation to test whether or not the journal
file is still present in the file-system.


HLR H21050 
If the xAccess() query required by H21049 reveals that the journal
file is still present in the file system, then SQLite shall conclude
that the journal file is a <i>hot journal file</i> that needs to
be rolled back. SQLite shall immediately begin <i>hot journal
rollback</i>.







HLR H21051 
If the call to xAccess() required by H21014 fails (due to an IO error or
similar), then SQLite shall abandon the attempt to open a <i>read-only



transaction</i>, relinquish the <i>shared lock</i> held on the database
file and return an error to the user.

HLR H21052 
If the call to xCheckReservedLock() required by H21016 fails (due to an



IO or other internal VFS error), then SQLite shall abandon the attempt



to open a <i>read-only transaction</i>, relinquish the <i>shared lock</i>



held on the database file and return an error to the user.





HLR H21053 
If the call to xOpen() required by H21044 fails (due to an IO or other
internal VFS error), then SQLite shall abandon the attempt to open a


<i>read-only transaction</i>, relinquish the <i>shared lock</i> held on
the database file and return an error to the user.



HLR H21054 




If the call to xFileSize() required by H21045 fails (due to an IO or
other internal VFS error), then SQLite shall abandon the attempt to open

a <i>read-only transaction</i>, relinquish the <i>shared lock</i> held on
the database file, close the file handle opened on the journal file and

return an error to the user.



HLR H21055 
If the call to xDelete() required by H21045 fails (due to an IO or
other internal VFS error), then SQLite shall abandon the attempt to open
a <i>read-only transaction</i>, relinquish the <i>shared lock</i> held on
the database file and return an error to the user.



HLR H21056 
If the call to xAccess() required by H21049 fails (due to an IO or
other internal VFS error), then SQLite shall abandon the attempt to open

a <i>read-only transaction</i>, relinquish the lock held on the

database file, close the file handle opened on the journal file and
return an error to the user.



HLR H21057 
If the call to xAccess() required by H21049 reveals that the journal
file is no longer present in the file system, then SQLite shall abandon
the attempt to open a <i>read-only transaction</i>, relinquish the




lock held on the database file, close the file handle opened on the

journal file and return an SQLITE_BUSY error to the user.








HLR H21058 
If an attempt to acquire a <i>reserved lock</i> prescribed by
requirement H21036 fails, then SQLite shall deem the attempt to
open a <i>write transaction</i> to have failed and return an error

to the user.

HLR H21059 
When required to modify the contents of an existing database page that
existed and was not a <i>free-list leaf page</i> when the <i>write
transaction</i> was opened, SQLite shall journal the page if it has not
already been journalled within the current <i>write transaction</i>.





HLR H21060 
When required to modify the contents of an existing database page,

SQLite shall update the cached version of the database page content





stored as part of the <i>page cache entry</i> associated with the page.











HLR H21061 
When required to append a new database page to the database file,
SQLite shall create a new <i>page cache entry</i> corresponding to
the new page and insert it into the <i>page cache</i>. The <i>dirty
flag</i> of the new <i>page cache entry</i> shall be set.



HLR H21062 
When required to truncate (remove) a database page that existed and was
not a <i>free-list leaf page</i> when the <i>write transaction</i> was


opened from the end of a database file, SQLite shall journal the page if




it has not already been journalled within the current <i>write
transaction</i>.





HLR H21063 
When required to truncate a database page from the end of the database
file, SQLite shall discard the associated <i>page cache entry</i>




from the page cache.

HLR H21064 
When required to purge a <i>non-writable dirty page</i> from the
<i>page cache</i>, SQLite shall <i>sync the journal file</i> before
proceding with the write operation required by H21067.



HLR H21066 



After <i>syncing the journal file</i> as required by H21064, SQLite
shall append a new <i>journal header</i> to the <i>journal file</i>
before proceding with the write operation required by H21067.







HLR H21067 
When required to purge a <i>page cache entry</i> that is a
<i>dirty page</i> SQLite shall write the page data into the database

file, using a single call to the xWrite method of the <i>database
connection</i> file handle.

HLR H21068 
When required to append a <i>journal header</i> to the <i>journal
file</i>, SQLite shall do so by writing a block of <i>sector-size</i>
bytes using a single call to the xWrite method of the file-handle


open on the <i>journal file</i>. The block of data written shall begin







at the smallest sector-size aligned offset at or following the current

end of the <i>journal file</i>.






HLR H21069 
The first 8 bytes of the <i>journal header</i> required to be written
by H21068 shall contain the following values, in order from byte offset 0
to 7: 0xd9, 0xd5, 0x05, 0xf9, 0x20, 0xa1, 0x63 and 0xd7.


HLR H21070 


Bytes 8-11 of the <i>journal header</i> required to be written by




H21068 shall contain 0x00.


HLR H21071 
Bytes 12-15 of the <i>journal header</i> required to be written by


H21068 shall contain the number of pages that the database file
contained when the current <i>write-transaction</i> was started,


formatted as a 4-byte big-endian unsigned integer.

HLR H21072 
Bytes 16-19 of the <i>journal header</i> required to be written by
H21068 shall contain pseudo-randomly generated values.




HLR H21073 

Bytes 20-23 of the <i>journal header</i> required to be written by
H21068 shall contain the <i>sector size</i> used by the VFS layer,


formatted as a 4-byte big-endian unsigned integer.


HLR H21074 
Bytes 24-27 of the <i>journal header</i> required to be written by



H21068 shall contain the <i>page size</i> used by the database at
the start of the <i>write transaction</i>, formatted as a 4-byte
big-endian unsigned integer.







HLR H21075 



When required to <i>sync the journal file</i>, SQLite shall invoke the


xSync method of the file handle open on the <i>journal file</i>.





HLR H21076 
When required to <i>sync the journal file</i>, after invoking the
xSync method as required by H21075, SQLite shall update the <i>record
count</i> of the <i>journal header</i> most recently written to the


<i>journal file</i>. The 4-byte field shall be updated to contain
the number of <i>journal records</i> that have been written to the
<i>journal file</i> since the <i>journal header</i> was written,

formatted as a 4-byte big-endian unsigned integer.



HLR H21077 
When required to <i>sync the journal file</i>, after updating the
<i>record count</i> field of a <i>journal header</i> as required by
H21076, SQLite shall invoke the xSync method of the file handle open
on the <i>journal file</i>.






HLR H21078 
When required to upgrade to an <i>exclusive lock</i> as part of a write
transaction, SQLite shall first attempt to obtain a <i>pending lock</i>
on the database file if one is not already held by invoking the xLock
method of the file handle opened on the <i>database file</i>.




HLR H21079 





When required to upgrade to an <i>exclusive lock</i> as part of a write
transaction, after successfully obtaining a <i>pending lock</i> SQLite

shall attempt to obtain an <i>exclusive lock</i> by invoking the


xLock method of the file handle opened on the <i>database file</i>.





HLR H21080 


When required to <i>commit a write-transaction</i>, SQLite shall
modify page 1 to increment the value stored in the <i>change counter</i>


field of the <i>database file header</i>.


HLR H21081 
When required to <i>commit a write-transaction</i>, after incrementing
the <i>change counter</i> field, SQLite shall <i>sync the journal


file</i>.

HLR H21082 
When required to <i>commit a write-transaction</i>, after <i>syncing
the journal file</i> as required by H21081, if an <i>exclusive lock</i>




on the database file is not already held, SQLite shall attempt to
<i>upgrade to an exclusive lock</i>.



HLR H21083 
When required to <i>commit a write-transaction</i>, after <i>syncing
the journal file</i> as required by H21081 and ensuring that an
<i>exclusive lock</i> is held on the database file as required by
H21083, SQLite shall copy the contents of all <i>dirty page</i>
stored in the <i>page cache</i> into the <i>database file</i> using
calls to the xWrite method of the <i>database connection</i> file
handle. Each call to xWrite shall write the contents of a single




<i>dirty page</i> (<i>page-size</i> bytes of data) to the database



file. Dirty pages shall be written in order of <i>page number</i>,





from lowest to highest.






HLR H21084 
When required to <i>commit a write-transaction</i>, after copying the
contents of any <i>dirty pages</i> to the database file as required
by H21083, SQLite shall sync the database file by invoking the xSync
method of the <i>database connection</i> file handle.


HLR H21085 
When required to <i>commit a write-transaction</i>, after syncing
the database file as required by H21084, SQLite shall close the
file-handle opened on the <i>journal file</i> and delete the
<i>journal file</i> from the file system via a call to the VFS
xDelete method.


HLR H21086 
When required to <i>commit a write-transaction</i>, after deleting
the <i>journal file</i> as required by H21085, SQLite shall relinquish
all locks held on the <i>database file</i> by invoking the xUnlock


method of the <i>database connection</i> file handle.







|
>
>
>
|
>
|
|
>
|

|
>
>
>
|
>
>
|
|
>
>
>
|
>
>
>

|
<
>
>
|
|
>
>
>
|

|
<
>
>
|
|
<
>
>
|
>
|
>
>
>
>

|
|
>
>
|
>
>
|
>
|
>
>
|
>
>

|
>
|
>
|
>
>
|
>

|
>
|
>
|
>
>
>
|
>
|
>
|
<
>
>

|
<
|
<
|

|
<
|
>
>
|
<
>
>
>

|
|
|
>
|

|
>
>
|
>
|
>
>
|
>
>
>

|
>
>
|
<
<
<
>
>
>
>

|
|
>
>
>
|
<

|
>
>
>
|
>
>
>
>
|
>
|

|
<
<
<
|
<
>
>
>
>
>
>

|
<
<
<
<
<
>
>
>
|

|
>
>
>
>
|
>
>
>
>
|
>
|
<
<
>
>
>

|
|
>
>
|
>
>
|
|
>

|
>
>
>
>
>
|
>
>
|
>
|
|
>
>
>
>

|
>
>
|
<
>
>
>
|
>
>
|
>
>
|
>
|

|
<
|
>
|

|
|
|
|
>
|
<
>
|
>
>

|
<
>
>
|
>
|
<
>
>
>
|
>
|
<
>
>
>
>
>
>

|
<
|
>
>
>
|
<
>
|
>

|
<
>
>
>
|
|
>
|
>
|
>
>
>
>

|
<
|
|
|
>
|
>

|
>
>
|
>
|
<
>
>
>
>
|
|
>
>
>
>
>
>
>
>

|
<
<
|
<
>
|

|
>
>
>
>
|
>
|
>
>
>
|
>
>
>
>
>

|
|
|
|
|

|
|
<
|
>
>

|
>
>
>
>
|
>
|
>
>
>
|
>
>
>
>

|
|
|
>

|
>
|
>
>
|
<
>

|
>
>
>
>
>
>
|
|
>
>
>
>
>

|
<
<
|
<
>
|

|
<
|
|

|
|
|
|
|
|
>
>
>
>
>
>
>
>
>

|
>
>
>
>
>
>
|
>
>
>
|
>
|
|
>

|
<
<
|
>
>
|
<
>
>
>
>
>
>

|
>
>
>
|
>
>
>
|
>
>
>
>
>
|
>
>
|
<
>

|
<
<
<
<
<
>
>
>
>
>
>

|
<
<
>
>
>
|
<

|
<
>
>
>
|
>
>
>
|
>
>
>
|
>
>
>
>

|
<
<
>
>
|
|
>
>

|
>
>
>
>
|
<
>
|
<
>
|
>
>

|
<
<
|
<
>
>

|
<
<
>
|
>
|
|
>
>

|
<
<
<
>
>
>
>
|
>
|
>
>
>
>
>
>
>

|
<
|
|
>
|

|
|
<
<
|
>
>
>
>

|
|
>
|
>
>
>
>
>
|
>
>
>
>
>
>
>
>
>
>

|
<
<
<
<
>
>

|
<
|
>
>
|
>
>
>
>
|
|
>
>
>
>

|
<
|
>
>
>
>
|

|
<
<
<
>
>

|
>
>
>
|
<
<
>
>
>
>
>
>

|
<
|
>
|
|

|
|
|
|
>
>
|
>
>
>
>
>
>
>
|
>
|
>
>
>
>
>

|
|
<
<
>

|
>
>
|
>
>
>
>
|
>

|
<
>
>
|
|
>
>
|

|
|
|
>
>
>

|
>
|
|
>
>
|
>

|
<
>
>
>
|
<
|
>
>
>
>
>
>

|
>
>
>
|
>
>
|
>
>
>
>

|
<
<
<
>
>
|
<
<
>
|
>
>

|
<
<
<
<
>
>
>
>
>

|
<
|
|
|
>
>
>

|
>
>
>
>
>
|
<
>
|
>
>
|
>
>
>
>

|
>
>
|
<
>
>
|
>

|
<
<
>
>
|

|
<
<
>
>
>
>
|
|
>
>

|
|
|
|
|
|
|
|
>
>
>
>
|
>
>
>
|
>
>
>
>
>
|
>
>
>
>
>

|
<
|
<
|
>

|
<
|
|
|
|
>

|
<
|
|
>
>
|
>
>
>
>
>
>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

30
31
32
33
34
35
36
37
38
39

40
41
42
43

44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92

93
94
95
96

97

98
99
100

101
102
103
104

105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131



132
133
134
135
136
137
138
139
140
141
142

143
144
145
146
147
148
149
150
151
152
153
154
155
156
157



158

159
160
161
162
163
164
165
166





167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184


185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221

222
223
224
225
226
227
228
229
230
231
232
233
234
235

236
237
238
239
240
241
242
243
244
245

246
247
248
249
250
251

252
253
254
255
256

257
258
259
260
261
262

263
264
265
266
267
268
269
270

271
272
273
274
275

276
277
278
279
280

281
282
283
284
285
286
287
288
289
290
291
292
293
294
295

296
297
298
299
300
301
302
303
304
305
306
307
308

309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324


325

326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354

355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386

387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404


405

406
407
408
409

410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446


447
448
449
450

451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475

476
477
478





479
480
481
482
483
484
485
486


487
488
489
490

491
492

493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510


511
512
513
514
515
516
517
518
519
520
521
522
523

524
525

526
527
528
529
530
531


532

533
534
535
536


537
538
539
540
541
542
543
544
545



546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561

562
563
564
565
566
567
568


569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596




597
598
599
600

601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616

617
618
619
620
621
622
623
624



625
626
627
628
629
630
631
632


633
634
635
636
637
638
639
640

641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670


671
672
673
674
675
676
677
678
679
680
681
682
683
684

685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709

710
711
712
713

714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735



736
737
738


739
740
741
742
743
744




745
746
747
748
749
750
751

752
753
754
755
756
757
758
759
760
761
762
763
764
765

766
767
768
769
770
771
772
773
774
775
776
777
778
779

780
781
782
783
784
785


786
787
788
789
790


791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829

830

831
832
833
834

835
836
837
838
839
840
841

842
843
844
845
846
847
848
849
850
851
852
853
HLR H30010
The system shall ensure that at the successful conclusion of a
database transaction the contents of the database file constitute
a <i>well-formed SQLite database file</i>.

HLR H30020
The system shall ensure that at the successful conclusion of a
database transaction the contents of the database file are a valid
serialization of the contents of the logical SQL database produced
by the transaction.

HLR H30030
The first 16 bytes of a well-formed database file contain the UTF-8
encoding of the string "SQLite format 3" followed by a single
nul-terminator byte.

HLR H30040
The 19th byte (byte offset 18), the <i>file-format write version</i>,
of a well-formed database file contains the value 0x01.

HLR H30050
The 20th byte (byte offset 19), the <i>file-format read version</i>,
of a well-formed database file contains the value 0x01.

HLR H30060
The 21st byte (byte offset 20), the number of unused bytes on each
page, of a well-formed database file shall contain the value 0x00.

HLR H30070

The 22nd byte (byte offset 21), the maximum fraction of an index
B-Tree page to use for embedded content, of a well-formed database
file shall contain the value 0x40.

HLR H30080
The 23rd byte (byte offset 22), the minimum fraction of an index
B-Tree page to use for embedded content when using overflow pages,
of a well-formed database file contains the value 0x20.

HLR H30090

The 24th byte (byte offset 23), the minimum fraction of a table
B-Tree page to use for embedded content when using overflow pages,
of a well-formed database file contains the value 0x20.


HLR H30100
The 4 byte block starting at byte offset 24 of a well-formed
database file contains the <i>file change counter</i> formatted
as a 4-byte big-endian integer.

HLR H30110
The 4 byte block starting at byte offset 40 of a well-formed
database file contains the <i>schema version</i> formatted
as a 4-byte big-endian integer.

HLR H30120
The 4 byte block starting at byte offset 44 of a well-formed
database file, the <i>schema layer file format</i>, contains a
big-endian integer value between 1 and 4, inclusive.

HLR H30130
The 4 byte block starting at byte offset 48 of a well-formed
database file contains the <i>default pager cache size</i> formatted
as a 4-byte big-endian integer.

HLR H30140
The 4 byte block starting at byte offset 52 of a well-formed
database file contains the <i>auto-vacuum last root-page</i>
formatted as a 4-byte big-endian integer. If this value is non-zero,
the database is said to be an <i>auto-vacuum database</i>.

HLR H30150
The 4 byte block starting at byte offset 56 of a well-formed
database file, the <i>text encoding</i> contains a big-endian integer
value between 1 and 3, inclusive.

HLR H30160
The 4 byte block starting at byte offset 60 of a well-formed
database file contains the <i>user cookie</i> formatted
as a 4-byte big-endian integer.

HLR H30170
The 4 byte block starting at byte offset 64 of a well-formed
database file, the <i>incremental vaccum flag</i> contains a big-endian
integer value between 0 and 1, inclusive.

HLR H30180
In a well-formed non-autovacuum database (one with a zero stored
in the 4-byte big-endian integer value beginning at byte offset
52 of the database file header, the incremental vacuum flag is
set to 0.

HLR H30190
The <i>database page size</i> of a well-formed database, stored as a

2-byte big-endian unsigned integer at byte offset 16 of the file,
shall be an integer power of 2 between 512 and 32768, inclusive.

HLR H30200

The size of a <i>well formed database file</i> shall be an integer

multiple of the <i>database page size</i>.

HLR H30210

Each page of a <i>well formed database file</i> is exactly one of a
<i>B-Tree page</i>, an <i>overflow page</i>, a <i>free page</i>, a
<i>pointer-map page</i> or the <i>locking page</i>.


HLR H30220
The database page that starts at byte offset 2<sup>30</sup>, the
<i>locking page</i>, is never used for any purpose.

HLR H30230
In a <i>well-formed database file</i>, the portion of the first
database page not consumed by the database file-header (all but the
first 100 bytes) contains the root node of a table B-Tree,
the <i>schema table</i>.

HLR H30240
All records stored in the <i>schema table</i> contain exactly five
fields.

HLR H30250
For each SQL table in the database apart from itself
("sqlite_master"), the <i>schema table</i> of a <i>well-formed
database file</i> contains an associated record.

HLR H30260
The first field of each <i>schema table</i> record associated with an
SQL table shall be the text value "table".

HLR H30270
The second field of each <i>schema table</i> record associated with an
SQL table shall be a text value set to the name of the SQL table.




HLR H30280
In a <i>well-formed database file</i>, the third field of all
<i>schema table</i> records associated with SQL tables shall contain
the same value as the second field.

HLR H30290
In a <i>well-formed database file</i>, the fourth field of all
<i>schema table</i> records associated with SQL tables that are not
virtual tables contains the page number (an integer value) of the root
page of the associated <i>table B-Tree</i> structure within the
database file.


HLR H30300
If the associated database table is a virtual table, the fourth
field of the <i>schema table</i> record shall contain an SQL NULL
value.

HLR H30310
In a well-formed database, the fifth field of all <i>schema table</i>
records associated with SQL tables shall contain a "CREATE TABLE"
or "CREATE VIRTUAL TABLE" statment (a text value).  The details
of the statement shall be such that executing the statement
would create a table of precisely the same name and schema as the
existing database table.

HLR H30320



For each PRIMARY KEY or UNIQUE constraint present in the definition

of each SQL table in the database, the schema table of a well-formed
database shall contain a record with the first field set to the text
value "index", and the second field set to a text value containing a
string of the form "sqlite_autoindex_&lt;name&gt;_&lt;idx&gt;", where
&lt;name&gt; is the name of the SQL table and &lt;idx&gt; is an
integer value.

HLR H30330





In a well-formed database, the third field of all schema table
records associated with SQL PRIMARY KEY or UNIQUE constraints shall
contain the name of the table to which the constraint applies (a
text value).

HLR H30340
In a well-formed database, the fourth field of all schema table
records associated with SQL PRIMARY KEY or UNIQUE constraints shall
contain the page number (an integer value) of the root page of the
associated index B-Tree structure.

HLR H30350
In a well-formed database, the fifth field of all schema table
records associated with SQL PRIMARY KEY or UNIQUE constraints shall
contain an SQL NULL value.

HLR H30360
For each SQL index in the database, the schema table of a well-formed


database shall contain a record with the first field set to the text
value "index" and the second field set to a text value containing the
name of the SQL index.

HLR H30370
In a well-formed database, the third field of all schema table
records associated with SQL indexes shall contain the name of the
SQL table that the index applies to.

HLR H30380
In a well-formed database, the fourth field of all schema table
records associated with SQL indexes shall contain the page number
(an integer value) of the root page of the associated index B-Tree
structure.

HLR H30390
In a well-formed database, the fifth field of all schema table
records associated with SQL indexes shall contain an SQL "CREATE
INDEX" statement (a text value). The details of the statement shall
be such that executing the statement would create an index of
precisely the same name and content as the existing database index.

HLR H30400
For each SQL view in the database, the schema table of a well-formed
database shall contain a record with the first field set to the text
value "view" and the second field set to a text value containing the
name of the SQL view.

HLR H30410
In a well-formed database, the third field of all schema table
records associated with SQL views shall contain the same value as
the second field.

HLR H30420
In a well-formed database, the third field of all schema table
records associated with SQL views shall contain the integer value 0.


HLR H30430
In a well-formed database, the fifth field of all schema table
records associated with SQL indexes shall contain an SQL "CREATE
VIEW" statement (a text value). The details of the statement shall
be such that executing the statement would create a view of
precisely the same name and definition as the existing database view.

HLR H30440
For each SQL trigger in the database, the schema table of a well-formed
database shall contain a record with the first field set to the text
value "trigger" and the second field set to a text value containing the
name of the SQL trigger.

HLR H30450

In a well-formed database, the third field of all schema table
records associated with SQL triggers shall contain the name of the
database table or view to which the trigger applies.

HLR H30460
In a well-formed database, the third field of all schema table
records associated with SQL triggers shall contain the integer value 0.

HLR H30470
In a well-formed database, the fifth field of all schema table

records associated with SQL indexes shall contain an SQL "CREATE
TRIGGER" statement (a text value). The details of the statement shall
be such that executing the statement would create a trigger of
precisely the same name and definition as the existing database trigger.

HLR H30480

In an auto-vacuum database, all pages that occur before the page
number stored in the <i>auto-vacuum last root-page</i> field
of the database file header (see H30140) must be either B-Tree <i>root
pages</i>, <i>pointer-map pages</i> or the <i>locking page</i>.


HLR H30490
In an auto-vacuum database, no B-Tree <i>root pages</i> may occur
on or after the page number stored in the <i>auto-vacuum last root-page</i> field
of the database file header (see H30140) must be either B-Tree <i>root
pages</i>, <i>pointer-map pages</i> or the <i>locking page</i>.


HLR H30500
As well as the <i>schema table</i>, a <i>well-formed database file</i>
contains <i>N</i> table B-Tree structures, where <i>N</i> is the
number of non-virtual tables in the logical database, excluding the
sqlite_master table but including sqlite_sequence and other system
tables.

HLR H30510

A well-formed database file contains <i>N</i> table B-Tree structures,
where <i>N</i> is the number of indexes in the logical database,
including indexes created by UNIQUE or PRIMARY KEY clauses in the
declaration of SQL tables.


HLR H30520
A 64-bit signed integer value stored in <i>variable length integer</i>
format consumes from 1 to 9 bytes of space.

HLR H30530

The most significant bit of all bytes except the last in a serialized
<i>variable length integer</i> is always set. Unless the serialized
form consumes the maximum 9 bytes available, then the most significant
bit of the final byte of the representation is always cleared.

HLR H30540
The eight least significant bytes of the 64-bit twos-compliment
representation of a value stored in a 9 byte <i>variable length
integer</i> are stored in the final byte (byte offset 8) of the
serialized <i>variable length integer</i>. The other 56 bits are
stored in the 7 least significant bits of each of the first 8 bytes
of the serialized <i>variable length integer</i>, in order from
most significant to least significant.

HLR H30550

A <i>variable length integer</i> that consumes less than 9 bytes of
space contains a value represented as an <i>N</i>-bit unsigned
integer, where <i>N</i> is equal to the number of bytes consumed by
the serial representation (between 1 and 8) multiplied by 7. The
<i>N</i> bits are stored in the 7 least significant bits of each
byte of the serial representation, from most to least significant.

HLR H30560
A <i>database record</i> consists of a <i>database record header</i>,
followed by <i>database record data</i>. The first part of the
<i>database record header</i> is a <i>variable length integer</i>
containing the total size (including itself) of the header in bytes.


HLR H30570
Following the length field, the remainder of the <i>database record
header</i> is populated with <i>N</i> <i>variable length integer</i>
fields, where <i>N</i> is the number of database values stored in
the record.

HLR H30580
Following the <i>database record header</i>, the <i>database record
data</i> is made up of <i>N</i> variable length blobs of data, where
<i>N</i> is again the number of database values stored in the record.
The <i>n</i> blob contains the data for the <i>n</i>th value in
the database record. The size and format of each blob of data is
encoded in the corresponding <i>variable length integer</i> field
in the <i>database record header</i>.

HLR H30590


A value of 0 stored within the <i>database record header</i> indicates

that the corresponding database value is an SQL NULL. In this case
the blob of data in the data area is 0 bytes in size.

HLR H30600
A value of 1 stored within the <i>database record header</i> indicates
that the corresponding database value is an SQL integer. In this case
the blob of data contains the integer value, formatted as a 1-byte
big-endian signed integer.

HLR H30610
A value of 2 stored within the <i>database record header</i> indicates
that the corresponding database value is an SQL integer. In this case
the blob of data contains the integer value, formatted as a 2-byte
big-endian signed integer.

HLR H30620
A value of 3 stored within the <i>database record header</i> indicates
that the corresponding database value is an SQL integer. In this case
the blob of data contains the integer value, formatted as a 3-byte
big-endian signed integer.

HLR H30630
A value of 4 stored within the <i>database record header</i> indicates
that the corresponding database value is an SQL integer. In this case
the blob of data contains the integer value, formatted as a 4-byte
big-endian signed integer.

HLR H30640
A value of 5 stored within the <i>database record header</i> indicates

that the corresponding database value is an SQL integer. In this case
the blob of data contains the integer value, formatted as a 6-byte
big-endian signed integer.

HLR H30650
A value of 6 stored within the <i>database record header</i> indicates
that the corresponding database value is an SQL integer. In this case
the blob of data contains the integer value, formatted as a 8-byte
big-endian signed integer.

HLR H30660
A value of 7 stored within the <i>database record header</i> indicates
that the corresponding database value is an SQL real (floating
point number). In this case the blob of data contains an 8-byte
IEEE floating point number, stored in big-endian byte order.

HLR H30670
A value of 8 stored within the <i>database record header</i> indicates
that the corresponding database value is an SQL integer, value 0.
In this case the blob of data in the data area is 0 bytes in size.

HLR H30680
A value of 9 stored within the <i>database record header</i> indicates
that the corresponding database value is an SQL integer, value 1.
In this case the blob of data in the data area is 0 bytes in size.

HLR H30690
An even value greater than or equal to 12 stored within the
<i>database record header</i> indicates that the corresponding
database value is an SQL blob field. The blob of data contains the
value data.  The blob of data is exactly (<i>n</i>-12)/2 bytes
in size, where <i>n</i> is the integer value stored in the

<i>database record header</i>.

HLR H30700
An odd value greater than or equal to 13 stored within the
<i>database record header</i> indicates that the corresponding
database value is an SQL text field. The blob of data contains the
value text stored using the <i>database encoding</i>, with no
nul-terminator. The blob of data is exactly (<i>n</i>-12)/2 bytes
in size, where <i>n</i> is the integer value stored in the
<i>database record header</i>.

HLR H30710
In a well-formed database file, if the values 8 or 9 appear within
any <i>database record header</i> within the database, then the
<i>schema-layer file format</i> (stored at byte offset 44 of the
database file header) must be set to 4.

HLR H30720


In a well-formed database file, the values 10 and 11, and all

negative values may not appear within any <i>database record header</i>
in the database.

HLR H30730

The pages in an index B-Tree structures are arranged into a tree
structure such that all leaf pages are at the same depth.

HLR H30740
Each leaf node page in an index B-Tree contains one or more
B-Tree cells, where each cell contains a database record.

HLR H30750
Each internal node page in an index B-Tree contains one or more
B-Tree cells, where each cell contains a child page number, <i>C</i>,
and a database record <i>R</i>. All database records stored within
the sub-tree headed by page <i>C</i> are smaller than record <i>R</i>,
according to the index sort order (see below). Additionally, unless
<i>R</i> is the smallest database record stored on the internal node
page, all integer keys within the sub-tree headed by <i>C</i> are
greater than <i>R<sub>-1</sub></i>, where <i>R<sub>-1</sub></i> is the
largest database record on the internal node page that is smaller
than <i>R</i>.

HLR H30760
As well as child page numbers associated with B-Tree cells, each
internal node page in an index B-Tree contains the page number
of an extra child page, the <i>right-child page</i>. All database
records stored in all B-Tree cells within the sub-tree headed by the
<i>right-child page</i> are greater than all database records
stored within B-Tree cells on the internal node page.

HLR H30770
In a well-formed database, each index B-Tree contains a single entry
for each row in the indexed logical database table.

HLR H30780
Each <i>database record</i> (key) stored by an index B-Tree in a
well-formed database contains the same number of values, the number
of indexed columns plus one.

HLR H30790


The final value in each <i>database record</i> (key) stored by an
index B-Tree in a well-formed database contains the rowid (an integer
value) of the corresponding logical database row.


HLR H30800
The first <i>N</i> values in each <i>database record</i> (key)
stored in an index B-Tree where <i>N</i> is the number of indexed
columns, contain the values of the indexed columns from the
corresponding logical database row, in the order specified for the
index.

HLR H30810
The <i>b-tree page flags</i> field (the first byte) of each database
page used as an internal node of an index B-Tree structure is set to
0x02.

HLR H30820
The <i>b-tree page flags</i> field (the first byte) of each database
page used as a leaf node of an index B-Tree structure is set to 0x0A.

HLR H30830
The first byte of each database page used as a B-Tree page contains
the <i>b-tree page flags</i> field. On page 1, the <i>b-tree page
flags</i> field is stored directly after the 100 byte file header
at byte offset 100.

HLR H30840
The number of B-Tree cells stored on a B-Tree page is stored as a
2-byte big-endian integer starting at byte offset 3 of the B-Tree

page. On page 1, this field is stored at byte offset 103.

HLR H30850





The 2-byte big-endian integer starting at byte offset 5 of each
B-Tree page contains the byte-offset from the start of the page
to the start of the <i>cell content area</i>, which consumes all space
from this offset to the end of the usable region of the page.
On page 1, this field is stored at byte offset 105.  All B-Tree
cells on the page are stored within the cell-content area.

HLR H30860


On each page used as an internal node a of B-Tree structures, the
page number of the rightmost child node in the B-Tree structure is
stored as a 4-byte big-endian unsigned integer beginning at byte
offset 8 of the database page, or byte offset 108 on page 1.


HLR H30870

Immediately following the <i>page header</i> on each B-Tree page is the
<i>cell offset array</i>, consisting of <i>N</i> 2-byte big-endian
unsigned integers, where <i>N</i> is the number of cells stored
on the B-Tree page (H30840). On an internal node B-Tree page,
the cell offset array begins at byte offset 12, or on a leaf
page, byte offset 8. For the B-Tree node on page 1, these
offsets are 112 and 108, respectively.

HLR H30880
The <i>cell offset array</i> and the <i>cell content area</i> (H30850)
may not overlap.

HLR H30890
Each value stored in the <i>cell offset array</i> must be greater
than or equal to the offset to the <i>cell content area</i> (H30850),
and less than the database <i>page size</i>.

HLR H30900


The <i>N</i> values stored within the <i>cell offset array</i> are the
byte offsets from the start of the B-Tree page to the beginning of
each of the <i>N</i> cells stored on the page.

HLR H30910
No two B-Tree cells may overlap.

HLR H30920
Within the <i>cell content area</i>, all blocks of contiguous
free-space (space not used by B-Tree cells) greater than 3 bytes in
size are linked together into a linked list, the <i>free block list</i>.
Such blocks of free space are known as <i>free blocks</i>.


HLR H30930
The first two bytes of each <i>free block</i> contain the offset

of the next <i>free block</i> in the <i>free block list</i> formatted
as a 2-byte big-endian integer, relative to the start of the database
page. If there is no next <i>free block</i>, then the first two
bytes are set to 0x00.

HLR H30940


The second two bytes (byte offsets 2 and 3) of each <i>free block</i>

contain the total size of the <i>free block</i>, formatted as a 2-byte
big-endian integer.

HLR H30950


On all B-Tree pages, the offset of the first <i>free block</i> in the
<i>free block list</i>, relative to the start of the database page,
is stored as a 2-byte big-endian integer starting at byte offset
1 of the database page. If there is no first <i>free block</i>
(because the <i>free block list</i> is empty), then the two bytes
at offsets 1 and 2 of the database page are set to 0x00. On page 1,
this field is stored at byte offset 101 of the page.

HLR H30960



Within the cell-content area, all blocks of contiguous free-space
(space not used by B-Tree cells) less than or equal to 3 bytes in
size are known as <i>fragments</i>. The total size of all
<i>fragments</i> on a B-Tree page is stored as a 1-byte unsigned
integer at byte offset 7 of the database page. On page 1, this
field is stored at byte offset 107.

HLR H30970
Each B-Tree cell belonging to an internal node page of an index
B-Tree consists of a 4-byte big-endian unsigned integer, the
<i>child page number</i>, followed by a <i>variable length integer</i>
field, followed by a <i>database record</i>. The
<i>variable length integer</i> field contains the length of the
database record in bytes.

HLR H30980

Each B-Tree cell belonging to an leaf page of an index B-Tree
consists of a <i>variable length integer</i> field, followed by
a <i>database record</i>. The <i>variable length integer</i> field
contains the length of the database record in bytes.

HLR H30990
If the database record stored in an index B-Tree page is


sufficiently small, then the entire cell is stored within the
index B-Tree page.  Sufficiently small is defined as equal to or
less than <i>max-local</i>, where:
<code>
<i>max-local</i> := (<i>usable-size</i> - 12) * 64 / 255 - 23</code>

HLR H31000
If the database record stored as part of an index B-Tree cell is too
large to be stored entirely within the B-Tree page (as defined by
H30520), then only a prefix of the <i>database record</i> is stored
within the B-Tree page and the remainder stored in an <i>overflow
chain</i>. In this case, the database record prefix is immediately
followed by the page number of the first page of the
<i>overflow chain</i>, formatted as a 4-byte big-endian unsigned
integer.

HLR H31010
When a <i>database record</i> belonging to a table B-Tree cell is
stored partially within an <i>overflow page chain</i>, the size
of the prefix stored within the index B-Tree page is <i>N</i> bytes,
where <i>N</i> is calculated using the following algorithm:
<code>
<i>min-local</i> := (<i>usable-size</i> - 12) * 32 / 255 - 23
<i>max-local</i> := (<i>usable-size</i> - 12) * 64 / 255 - 23
<i>N</i> := <i>min-local</i> + ((<i>record-size</i> - <i>min-local</i>) % (<i>usable-size</i> - 4))
if( <i>N</i> &gt; <i>max-local</i> ) <i>N</i> := <i>min-local</i></code>

HLR H31020




The pages in a table B-Tree structures are arranged into a tree
structure such that all leaf pages are at the same depth.

HLR H31030

Each leaf page in a table B-Tree structure contains one or more
B-Tree cells, where each cell contains a 64-bit signed integer key
value and a database record.

HLR H31040
Each internal node page in a table B-Tree structure contains one or
more B-Tree cells, where each cell contains a 64-bit signed integer
key value, <i>K</i>, and a child page number, <i>C</i>. All integer key
values in all B-Tree cells within the sub-tree headed by page <i>C</i>
are less than or equal to <i>K</i>. Additionally, unless <i>K</i>
is the smallest integer key value stored on the internal node page,
all integer keys within the sub-tree headed by <i>C</i> are greater
than <i>K<sub>-1</sub></i>, where <i>K<sub>-1</sub></i> is the largest
integer key on the internal node page that is smaller than <i>K</i>.

HLR H31050

As well as child page numbers associated with B-Tree cells, each
internal node page in a table B-Tree contains the page number
of an extra child page, the <i>right-child page</i>. All key values
in all B-Tree cells within the sub-tree headed by the <i>right-child
page</i> are greater than all key values stored within B-Tree cells
on the internal node page.

HLR H31060



In a well-formed database, each table B-Tree contains a single entry
for each row in the corresponding logical database table.

HLR H31070
The key value (a 64-bit signed integer) for each B-Tree entry is
the same as the value of the rowid field of the corresponding
logical database row.



HLR H31080
The SQL values serialized to make up each <i>database record</i>
stored as ancillary data in a table B-Tree shall be the equal to the
values taken by the <i>N</i> leftmost columns of the corresponding
logical database row, where <i>N</i> is the number of values in the
database record.

HLR H31090

If a logical database table column is declared as an "INTEGER
PRIMARY KEY", then instead of its integer value, an SQL NULL
shall be stored in its place in any database records used as
ancillary data in a table B-Tree.

HLR H31100
If the database <i>schema layer file-format</i> (the value stored
as a 4-byte integer at byte offset 44 of the file header) is 1,
then all database records stored as ancillary data in a table
B-Tree structure have the same number of fields as there are
columns in the corresponding logical database table.

HLR H31110
If the database <i>schema layer file-format</i> value is two or
greater and the rightmost <i>M</i> columns of a row contain SQL NULL
values, then the corresponding record stored as ancillary data in
the table B-Tree has between <i>N</i>-<i>M</i> and <i>N</i> fields,
where <i>N</i> is the number of columns in the logical database
table.

HLR H31120
If the database <i>schema layer file-format</i> value is three or
greater and the rightmost <i>M</i> columns of a row contain their
default values according to the logical table declaration, then the
corresponding record stored as ancillary data in the table B-Tree
may have as few as <i>N</i>-<i>M</i> fields, where <i>N</i> is the
number of columns in the logical database table.

HLR H31130
In a <i>well-formed database file</i>, the first byte of each page used


as an internal node of a table B-Tree structure is set to 0x05.

HLR H31140
In a <i>well-formed database file</i>, the first byte of each page used
as a leaf node of a table B-Tree structure is set to 0x0D.

HLR H31150
B-Tree cells belonging to table B-Tree internal node pages consist
of exactly two fields, a 4-byte big-endian unsigned integer
immediately followed by a <i>variable length integer</i>. These
fields contain the child page number and key value respectively
(see H31030).

HLR H31160

B-Tree cells belonging to table B-Tree leaf node pages consist
of three fields, two <i>variable length integer</i> values
followed by a database record. The size of the database record
in bytes is stored in the first of the two
<i>variable length integer</i> fields. The second of the two
<i>variable length integer</i> fields contains the 64-bit signed
integer key (see H31030).

HLR H31170
If the size of the record stored in a table B-Tree leaf page cell
is less than or equal to (<i>usable page size</i>-35) bytes, then
the entire cell is stored on the B-Tree leaf page. In a well-formed
database, <i>usable page size</i> is the same as the database
<i>page size</i>.

HLR H31180
If a table B-Tree cell is too large to be stored entirely on
a leaf page (as defined by H31170), then a prefix of the cell
is stored on the leaf page, and the remainder stored in an
<i>overflow page chain</i>. In this case the cell prefix
stored on the B-Tree leaf page is immediately followed by a
4-byte big-endian unsigned integer containing the page number
of the first overflow page in the chain.

HLR H31190

When a table B-Tree cell is stored partially in an
<i>overflow page chain</i>, the prefix stored on the B-Tree
leaf page consists of the two <i>variable length integer</i> fields,
followed by the first <i>N</i> bytes of the database record, where

<i>N</i> is determined by the following algorithm:
<code>
<i>min-local</i> := (<i>usable-size</i> - 12) * 255 / 32 - 23
<i>max-local</i> := (<i>usable-size</i> - 35)
<i>N</i> := <i>min-local</i> + (<i>record-size</i> - <i>min-local</i>) % (<i>usable-size</i> - 4)
if( <i>N</i> &gt; <i>max-local</i> ) N := <i>min-local</i>
</code>

HLR H31200
A single <i>overflow page</i> may store up to <i>available-space</i>
bytes of database record data, where <i>available-space</i> is equal
to (<i>usable-size</i> - 4).

HLR H31210
When a database record is too large to store within a B-Tree page
(see H31170 and H31000), a prefix of the record is stored within
the B-Tree page and the remainder stored across <i>N</i> overflow
pages. In this case <i>N</i> is the minimum number of pages required
to store the portion of the record not stored on the B-Tree page,
given the maximum payload per overflow page defined by H31200.

HLR H31220



The list of overflow pages used to store a single database record
are linked together in a singly linked list known as an
<i>overflow chain</i>. The first four bytes of each page except the


last in an <i>overflow chain</i> are used to store the page number
of the next page in the linked list, formatted as an unsigned
big-endian integer. The first four bytes of the last page in an
<i>overflow chain</i> are set to 0x00.

HLR H31230




Each overflow page except the last in an <i>overflow chain</i>
contains <i>N</i> bytes of record data starting at byte offset 4 of
the page, where <i>N</i> is the maximum payload per overflow page,
as defined by H31200. The final page in an <i>overflow chain</i>
contains the remaining data, also starting at byte offset 4.

HLR H31240

All <i>free pages</i> in a <i>well-formed database file</i> are part of
the database <i>free page list</i>.

HLR H31250
Each free page is either a <i>free list trunk</i> page or a
<i>free list leaf</i> page.

HLR H31260
All <i>free list trunk</i> pages are linked together into a singly
linked list. The first 4 bytes of each page in the linked list
contains the page number of the next page in the list, formatted
as an unsigned big-endian integer. The first 4 bytes of the last
page in the linked list are set to 0x00.


HLR H31270
The second 4 bytes of each <i>free list trunk</i> page contains
the number of </i>free list leaf</i> page numbers stored on the free list
trunk page, formatted as an unsigned big-endian integer.

HLR H31280
Beginning at byte offset 8 of each <i>free list trunk</i> page are
<i>N</i> page numbers, each formatted as a 4-byte unsigned big-endian
integers, where <i>N</i> is the value described in requirement H31270.

HLR H31290
All page numbers stored on all <i>free list trunk</i> pages refer to
database pages that are <i>free list leaves</i>.


HLR H31300
The page number of each <i>free list leaf</i> page in a well-formed
database file appears exactly once within the set of pages numbers
stored on <i>free list trunk</i> pages.

HLR H31310


The total number of pages in the free list, including all <i>free list
trunk</i> and <i>free list leaf</i> pages, is stored as a 4-byte unsigned
big-endian integer at offset 36 of the database file header.

HLR H31320


The page number of the first page in the linked list of <i>free list
trunk</i> pages is stored as a 4-byte big-endian unsigned integer at
offset 32 of the database file header. If there are no <i>free list
trunk</i> pages in the database file, then the value stored at
offset 32 of the database file header is 0.

HLR H31330
Non auto-vacuum databases do not contain pointer map pages.

HLR H31340
In an auto-vacuum database file, every <i>(num-entries + 1)</i>th
page beginning with page 2 is designated a pointer-map page, where
<i>num-entries</i> is calculated as:
<code>
<i>num-entries</i> := <i>database-usable-page-size</i> / 5
</code>

HLR H31350
In an auto-vacuum database file, each pointer-map page contains
a pointer map entry for each of the <i>num-entries</i> (defined by
H31340) pages that follow it, if they exist.

HLR H31360
Each pointer-map page entry consists of a 1-byte page type and a
4-byte page parent number, 5 bytes in total.

HLR H31370
Pointer-map entries are packed into the pointer-map page in order,
starting at offset 0. The entry associated with the database
page that immediately follows the pointer-map page is located at
offset 0. The entry for the following page at offset 5 etc.

HLR H31380
For each page except page 1 in an auto-vacuum database file that is
the root page of a B-Tree structure, the page type of the
corresponding pointer-map entry is set to the value 0x01 and the
parent page number is zero.

HLR H31390

For each page that is a part of an auto-vacuum database file free-list,

the page type of the corresponding pointer-map entry is set to the
value 0x02 and the parent page number is zero.

HLR H31400

For each page in a well-formed auto-vacuum database that is the first
page in an overflow chain, the page type of the corresponding
pointer-map entry is set to 0x03 and the parent page number field
is set to the page number of the B-Tree page that contains the start
of the B-Tree cell stored in the overflow-chain.

HLR H31410

For each page that is the second or a subsequent page in an overflow
chain, the page type of the corresponding pointer-map entry is set to
0x04 and the parent page number field is set to the page number of the
preceding page in the overflow chain.

HLR H31420
For each page that is not a root page but is a part of a B-Tree tree
structure (not part of an overflow chain), the page type of the
corresponding pointer-map entry is set to the value 0x05 and the parent
page number field is set to the page number of the parent node in the
B-Tree structure.

Added req/hlr35000.txt.






















































































































































































































































































































































































































































































































































































































































































































































































































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
HLR H35010 
Except for the read operation required by H35070 and those reads made
as part of opening a read-only transaction, SQLite shall ensure that
a <i>database connection</i> has an open read-only or read/write
transaction when any data is read from the <i>database file</i>.

HLR H35020 
Aside from those read operations described by H35070 and H21XXX, SQLite
shall read data from the database file in aligned blocks of
<i>page-size</i> bytes, where <i>page-size</i> is the database page size
used by the database file.

HLR H35030 
While opening a <i>read-only transaction</i>, after successfully
obtaining a <i>shared lock</i> on the database file, SQLite shall
attempt to detect and roll back a <i>hot journal file</i> associated
with the same database file.

HLR H35040 
Assuming no errors have occured, then after attempting to detect and
roll back a <i>hot journal file</i>, if the <i>page cache</i> contains
any entries associated with the current <i>database connection</i>,
then SQLite shall validate the contents of the <i>page cache</i> by
testing the <i>file change counter</i>.  This procedure is known as
<i>cache validiation</i>.

HLR H35050 
If the cache validiate procedure prescribed by H35040 is required and
does not prove that the <i>page cache</i> entries associated with the
current <i>database connection</i> are valid, then SQLite shall discard
all entries associated with the current <i>database connection</i> from
the <i>page cache</i>.

HLR H35060 
When a new <i>database connection</i> is required, SQLite shall attempt
to open a file-handle on the database file. If the attempt fails, then
no new <i>database connection</i> is created and an error returned.

HLR H35070 
When a new <i>database connection</i> is required, after opening the
new file-handle, SQLite shall attempt to read the first 100 bytes
of the database file. If the attempt fails for any other reason than
that the opened file is less than 100 bytes in size, then
the file-handle is closed, no new <i>database connection</i> is created
and an error returned instead.

HLR H35080 
If the <i>database file header</i> is successfully read from a newly
opened database file, the connections <i>expected page-size</i> shall
be set to the value stored in the <i>page-size field</i> of the
database header.

HLR H35090 
If the <i>database file header</i> cannot be read from a newly opened
database file (because the file is less than 100 bytes in size), the
connections <i>expected page-size</i> shall be set to the compile time
value of the SQLITE_DEFAULT_PAGESIZE option.

HLR H35100 
When required to open a <i>read-only transaction</i> using a
<i>database connection</i>, SQLite shall first attempt to obtain
a <i>shared-lock</i> on the file-handle open on the database file.

HLR H35110 
If, while opening a <i>read-only transaction</i>, SQLite fails to obtain
the <i>shared-lock</i> on the database file, then the process is
abandoned, no transaction is opened and an error returned to the user.

HLR H35120 
If, while opening a <i>read-only transaction</i>, SQLite encounters
an error while attempting to detect or roll back a <i>hot journal
file</i>, then the <i>shared-lock</i> on the database file is released,
no transaction is opened and an error returned to the user.

HLR H35130 
When required to end a <i>read-only transaction</i>, SQLite shall
relinquish the <i>shared lock</i> held on the database file by
calling the xUnlock() method of the file-handle.

HLR H35140 
When required to attempt to detect a <i>hot-journal file</i>, SQLite
shall first use the xAccess() method of the VFS layer to check if a
journal file exists in the file-system.

HLR H35150 
When required to attempt to detect a <i>hot-journal file</i>, if the
call to xAccess() required by H35140 indicates that a journal file does
not exist, then SQLite shall conclude that there is no <i>hot-journal
file</i> in the file system and therefore that no <i>hot journal
rollback</i> is required.

HLR H35160 
When required to attempt to detect a <i>hot-journal file</i>, if the
call to xAccess() required by H35140 indicates that a journal file
is present, then the xCheckReservedLock() method of the database file
file-handle is invoked to determine whether or not some other
process is holding a <i>reserved</i> or greater lock on the database
file.

HLR H35170 
If the call to xCheckReservedLock() required by H35160 indicates that
some other <i>database connection</i> is holding a <i>reserved</i>
or greater lock on the database file, then SQLite shall conclude that
there is no <i>hot journal file</i>. In this case the attempt to detect
a <i>hot journal file</i> is concluded.

HLR H35180 
When a file-handle open on a database file is unlocked, if the
<i>page cache</i> contains one or more entries belonging to the
associated <i>database connection</i>, SQLite shall store the value
of the <i>file change counter</i> internally.

HLR H35190 
When required to perform <i>cache validation</i> as part of opening
a <i>read transaction</i>, SQLite shall read a 16 byte block
starting at byte offset 24 of the <i>database file</i> using the xRead()
method of the <i>database connections</i> file handle.

HLR H35200 
While performing <i>cache validation</i>, after loading the 16 byte
block as required by H35190, SQLite shall compare the 32-bit big-endian
integer stored in the first 4 bytes of the block to the most
recently stored value of the <i>file change counter</i> (see H35180).
If the values are not the same, then SQLite shall conclude that
the contents of the cache are invalid.

HLR H35210 
During the conclusion of a <i>read transaction</i>, before unlocking
the database file, SQLite shall set the connections
<i>expected page size</i> to the current database <i>page-size</i>.

HLR H35220 
As part of opening a new <i>read transaction</i>, immediately after
performing <i>cache validation</i>, if there is no data for database
page 1 in the <i>page cache</i>, SQLite shall read <i>N</i> bytes from
the start of the database file using the xRead() method of the
connections file handle, where <i>N</i> is the connections current
<i>expected page size</i> value.

HLR H35230 
If page 1 data is read as required by H35230, then the value of the
<i>page-size</i> field that appears in the database file header that
consumes the first 100 bytes of the read block is not the same as the
connections current <i>expected page size</i>, then the
<i>expected page size</i> is set to this value, the database file is
unlocked and the entire procedure to open a <i>read transaction</i>
is repeated.

HLR H35240 
If page 1 data is read as required by H35230, then the value of the
<i>page-size</i> field that appears in the database file header that
consumes the first 100 bytes of the read block is the same as the
connections current <i>expected page size</i>, then the block of data
read is stored in the <i>page cache</i> as page 1.

HLR H35270 
When required to <i>journal a database page</i>, SQLite shall first
append the <i>page number</i> of the page being journalled to the
<i>journal file</i>, formatted as a 4-byte big-endian unsigned integer,
using a single call to the xWrite method of the file-handle opened
on the journal file.

HLR H35280 
When required to <i>journal a database page</i>, if the attempt to
append the <i>page number</i> to the journal file is successful,
then the current page data (<i>page-size</i> bytes) shall be appended
to the journal file, using a single call to the xWrite method of the
file-handle opened on the journal file.

HLR H35290 
When required to <i>journal a database page</i>, if the attempt to
append the current page data to the journal file is successful,
then SQLite shall append a 4-byte big-endian integer checksum value
to the to the journal file, using a single call to the xWrite method
of the file-handle opened on the journal file.

HLR H35300 
The checksum value written to the <i>journal file</i> by the write
required by H35290 shall be equal to the sum of the <i>checksum
initializer</i> field stored in the <i>journal header</i> (H35700) and
every 200th byte of the page data, beginning with the
(<i>page-size</i> % 200)th byte.

HLR H35350 
When required to open a <i>write transaction</i> on the database,
SQLite shall first open a <i>read transaction</i>, if the <i>database
connection</i> in question has not already opened one.

HLR H35360 
When required to open a <i>write transaction</i> on the database, after
ensuring a <i>read transaction</i> has already been opened, SQLite
shall obtain a <i>reserved lock</i> on the database file by calling
the xLock method of the file-handle open on the database file.

HLR H35370 
When required to open a <i>write transaction</i> on the database, after
obtaining a <i>reserved lock</i> on the database file, SQLite shall
open a read/write file-handle on the corresponding <i>journal file</i>.

HLR H35380 
When required to open a <i>write transaction</i> on the database, after
opening a file-handle on the <i>journal file</i>, SQLite shall append
a <i>journal header</i> to the (currently empty) <i>journal file</i>.

HLR H35400 
When a <i>database connection</i> is closed, SQLite shall close the
associated file handle at the VFS level.

HLR H35420 
SQLite shall ensure that a <i>database connection</i> has an open
read-only or read/write transaction before using data stored in the <i>page
cache</i> to satisfy user queries.

HLR H35430 
When a <i>database connection</i> is closed, all associated <i>page
cache</i> entries shall be discarded.

HLR H35440 
If while attempting to detect a <i>hot-journal file</i> the call to
xCheckReservedLock() indicates that no process holds a <i>reserved</i>
or greater lock on the <i>database file</i>, then SQLite shall open
a file handle on the potentially hot journal file using the VFS xOpen()
method.

HLR H35450 
After successfully opening a file-handle on a potentially hot journal
file, SQLite shall query the file for its size in bytes using the
xFileSize() method of the open file handle.

HLR H35460 
If the size of a potentially hot journal file is revealed to be zero
bytes by a query required by H35450, then SQLite shall close the
file handle opened on the journal file and delete the journal file using
a call to the VFS xDelete() method. In this case SQLite shall conclude
that there is no <i>hot journal file</i>.

HLR H35470 
If the size of a potentially hot journal file is revealed to be greater
than zero bytes by a query required by H35450, then SQLite shall attempt
to upgrade the <i>shared lock</i> held by the <i>database connection</i>
on the <i>database file</i> directly to an <i>exclusive lock</i>.

HLR H35480 
If an attempt to upgrade to an <i>exclusive lock</i> prescribed by
H35470 fails for any reason, then SQLite shall release all locks held by
the <i>database connection</i> and close the file handle opened on the
<i>journal file</i>. The attempt to open a <i>read-only transaction</i>
shall be deemed to have failed and an error returned to the user.

HLR H35490 
If, as part of the <i>hot journal file</i> detection process, the
attempt to upgrade to an <i>exclusive lock</i> mandated by H35470 is
successful, then SQLite shall query the file-system using the xAccess()
method of the VFS implementation to test whether or not the journal
file is still present in the file-system.

HLR H35500 
If the xAccess() query required by H35490 reveals that the journal
file is still present in the file system, then SQLite shall conclude
that the journal file is a <i>hot journal file</i> that needs to
be rolled back. SQLite shall immediately begin <i>hot journal
rollback</i>.

HLR H35510 
If the call to xAccess() required by H35140 fails (due to an IO error or
similar), then SQLite shall abandon the attempt to open a <i>read-only
transaction</i>, relinquish the <i>shared lock</i> held on the database
file and return an error to the user.

HLR H35520 
If the call to xCheckReservedLock() required by H35160 fails (due to an
IO or other internal VFS error), then SQLite shall abandon the attempt
to open a <i>read-only transaction</i>, relinquish the <i>shared lock</i>
held on the database file and return an error to the user.

HLR H35530 
If the call to xOpen() required by H35440 fails (due to an IO or other
internal VFS error), then SQLite shall abandon the attempt to open a
<i>read-only transaction</i>, relinquish the <i>shared lock</i> held on
the database file and return an error to the user.

HLR H35540 
If the call to xFileSize() required by H35450 fails (due to an IO or
other internal VFS error), then SQLite shall abandon the attempt to open
a <i>read-only transaction</i>, relinquish the <i>shared lock</i> held on
the database file, close the file handle opened on the journal file and
return an error to the user.

HLR H35550 
If the call to xDelete() required by H35450 fails (due to an IO or
other internal VFS error), then SQLite shall abandon the attempt to open
a <i>read-only transaction</i>, relinquish the <i>shared lock</i> held on
the database file and return an error to the user.

HLR H35560 
If the call to xAccess() required by H35490 fails (due to an IO or
other internal VFS error), then SQLite shall abandon the attempt to open
a <i>read-only transaction</i>, relinquish the lock held on the
database file, close the file handle opened on the journal file and
return an error to the user.

HLR H35570 
If the call to xAccess() required by H35490 reveals that the journal
file is no longer present in the file system, then SQLite shall abandon
the attempt to open a <i>read-only transaction</i>, relinquish the
lock held on the database file, close the file handle opened on the
journal file and return an SQLITE_BUSY error to the user.

HLR H35580 
If an attempt to acquire a <i>reserved lock</i> prescribed by
requirement H35360 fails, then SQLite shall deem the attempt to
open a <i>write transaction</i> to have failed and return an error
to the user.

HLR H35590 
When required to modify the contents of an existing database page that
existed and was not a <i>free-list leaf page</i> when the <i>write
transaction</i> was opened, SQLite shall journal the page if it has not
already been journalled within the current <i>write transaction</i>.

HLR H35600 
When required to modify the contents of an existing database page,
SQLite shall update the cached version of the database page content
stored as part of the <i>page cache entry</i> associated with the page.

HLR H35610 
When required to append a new database page to the database file,
SQLite shall create a new <i>page cache entry</i> corresponding to
the new page and insert it into the <i>page cache</i>. The <i>dirty
flag</i> of the new <i>page cache entry</i> shall be set.

HLR H35620 
When required to truncate (remove) a database page that existed and was
not a <i>free-list leaf page</i> when the <i>write transaction</i> was
opened from the end of a database file, SQLite shall journal the page if
it has not already been journalled within the current <i>write
transaction</i>.

HLR H35630 
When required to truncate a database page from the end of the database
file, SQLite shall discard the associated <i>page cache entry</i>
from the page cache.

HLR H35640 
When required to purge a <i>non-writable dirty page</i> from the
<i>page cache</i>, SQLite shall <i>sync the journal file</i> before
proceding with the write operation required by H35670.

HLR H35660 
After <i>syncing the journal file</i> as required by H35640, SQLite
shall append a new <i>journal header</i> to the <i>journal file</i>
before proceding with the write operation required by H35670.

HLR H35670 
When required to purge a <i>page cache entry</i> that is a
<i>dirty page</i> SQLite shall write the page data into the database
file, using a single call to the xWrite method of the <i>database
connection</i> file handle.

HLR H35680 
When required to append a <i>journal header</i> to the <i>journal
file</i>, SQLite shall do so by writing a block of <i>sector-size</i>
bytes using a single call to the xWrite method of the file-handle
open on the <i>journal file</i>. The block of data written shall begin
at the smallest sector-size aligned offset at or following the current
end of the <i>journal file</i>.

HLR H35690 
The first 8 bytes of the <i>journal header</i> required to be written
by H35680 shall contain the following values, in order from byte offset 0
to 7: 0xd9, 0xd5, 0x05, 0xf9, 0x20, 0xa1, 0x63 and 0xd7.

HLR H35700 
Bytes 8-11 of the <i>journal header</i> required to be written by
H35680 shall contain 0x00.

HLR H35710 
Bytes 12-15 of the <i>journal header</i> required to be written by
H35680 shall contain the number of pages that the database file
contained when the current <i>write-transaction</i> was started,
formatted as a 4-byte big-endian unsigned integer.

HLR H35720 
Bytes 16-19 of the <i>journal header</i> required to be written by
H35680 shall contain pseudo-randomly generated values.

HLR H35730 
Bytes 20-23 of the <i>journal header</i> required to be written by
H35680 shall contain the <i>sector size</i> used by the VFS layer,
formatted as a 4-byte big-endian unsigned integer.

HLR H35740 
Bytes 24-27 of the <i>journal header</i> required to be written by
H35680 shall contain the <i>page size</i> used by the database at
the start of the <i>write transaction</i>, formatted as a 4-byte
big-endian unsigned integer.

HLR H35750 
When required to <i>sync the journal file</i>, SQLite shall invoke the
xSync method of the file handle open on the <i>journal file</i>.

HLR H35760 
When required to <i>sync the journal file</i>, after invoking the
xSync method as required by H35750, SQLite shall update the <i>record
count</i> of the <i>journal header</i> most recently written to the
<i>journal file</i>. The 4-byte field shall be updated to contain
the number of <i>journal records</i> that have been written to the
<i>journal file</i> since the <i>journal header</i> was written,
formatted as a 4-byte big-endian unsigned integer.

HLR H35770 
When required to <i>sync the journal file</i>, after updating the
<i>record count</i> field of a <i>journal header</i> as required by
H35760, SQLite shall invoke the xSync method of the file handle open
on the <i>journal file</i>.

HLR H35780 
When required to upgrade to an <i>exclusive lock</i> as part of a write
transaction, SQLite shall first attempt to obtain a <i>pending lock</i>
on the database file if one is not already held by invoking the xLock
method of the file handle opened on the <i>database file</i>.

HLR H35790 
When required to upgrade to an <i>exclusive lock</i> as part of a write
transaction, after successfully obtaining a <i>pending lock</i> SQLite
shall attempt to obtain an <i>exclusive lock</i> by invoking the
xLock method of the file handle opened on the <i>database file</i>.

HLR H35800 
When required to <i>commit a write-transaction</i>, SQLite shall
modify page 1 to increment the value stored in the <i>change counter</i>
field of the <i>database file header</i>.

HLR H35810 
When required to <i>commit a write-transaction</i>, after incrementing
the <i>change counter</i> field, SQLite shall <i>sync the journal
file</i>.

HLR H35820 
When required to <i>commit a write-transaction</i>, after <i>syncing
the journal file</i> as required by H35810, if an <i>exclusive lock</i>
on the database file is not already held, SQLite shall attempt to
<i>upgrade to an exclusive lock</i>.

HLR H35830 
When required to <i>commit a write-transaction</i>, after <i>syncing
the journal file</i> as required by H35810 and ensuring that an
<i>exclusive lock</i> is held on the database file as required by
H35830, SQLite shall copy the contents of all <i>dirty page</i>
stored in the <i>page cache</i> into the <i>database file</i> using
calls to the xWrite method of the <i>database connection</i> file
handle. Each call to xWrite shall write the contents of a single
<i>dirty page</i> (<i>page-size</i> bytes of data) to the database
file. Dirty pages shall be written in order of <i>page number</i>,
from lowest to highest.

HLR H35840 
When required to <i>commit a write-transaction</i>, after copying the
contents of any <i>dirty pages</i> to the database file as required
by H35830, SQLite shall sync the database file by invoking the xSync
method of the <i>database connection</i> file handle.

HLR H35850 
When required to <i>commit a write-transaction</i>, after syncing
the database file as required by H35840, SQLite shall close the
file-handle opened on the <i>journal file</i> and delete the
<i>journal file</i> from the file system via a call to the VFS
xDelete method.

HLR H35860 
When required to <i>commit a write-transaction</i>, after deleting
the <i>journal file</i> as required by H35850, SQLite shall relinquish
all locks held on the <i>database file</i> by invoking the xUnlock
method of the <i>database connection</i> file handle.