fileformat.html

sqlite的帮助文档

        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".
      <p>
        The exact format of the B-Tree structure and the meaning of the term
        "root page" is discussed in section <cite>btree_structures</cite>.
        For now, it is sufficient to know that the B-Tree structure is a
        data structure that stores a set of records. Each record is an
        ordered set of SQL values (the format of which is described in
        section <cite>record_format</cite>). Given the root page number of
        the B-Tree structure (which is well known for the schema table), it
        is possible to iterate through the set of records.
      <p>
	The schema table contains a record for each SQL table (including
	virtual tables) except for sqlite_master, and for each index, trigger
	and view in the logical database.  There is also an entry for each
	UNIQUE or PRIMARY KEY clause present in the definition of a database
	table. Each record in the schema table contains exactly 5 values, in
        the following order:

      <table class=striped>
        <tr><th>Field<th>Description
        <tr><td>Schema item type.
	    <td>A string value. One of "table", "index", "trigger" or "view",
		according to the schema item type. Entries associated with
		UNIQUE or PRIMARY KEY clauses have this field set to "index".
        <tr><td>Schema item name.
	    <td>A string value. The name of the database schema item (table,
		index, trigger or view) associated with this record, if any.
		Entries associated with UNIQUE or PRIMARY KEY clauses have
		this field set to a string of the form
		"sqlite_autoindex_&lt;name&gt;_&lt;idx&gt;" where &lt;name&gt;
		is the name of the SQL table and &lt;idx&gt; is an integer
		value.

        <tr><td style="white-space:nowrap">Associated table name.
            <td>A string value. For "table" 
            or "view" records this is a copy of the second (previous) value. 
            For "index" and "trigger" records, this field is set to the name 
            of the associated database table.
        <tr><td style="white-space:nowrap">The "root page" number. 
	    <td>For "trigger" and "view" records, as well as "table" records
		associated with virtual tables, this is set to NULL. For other
		"table" and "index" records (including those associated with
		UNIQUE or PRIMARY KEY clauses), this field contains the root
		page number (an integer) of the B-Tree structure that contains
		the table or index data.
        <tr><td>The SQL statement.
            <td>A string value. The SQL statement used to create the schema
                item (i.e.  the complete text of an SQL "CREATE TABLE"
		statement). This field contains an empty string for table
		entries associated with PRIMARY KEY or UNIQUE clauses.
		<span class=todo>Refer to some document that describes these
	        SQL statements more precisely.</span>
      </table>
      <p>
	Logical database schema items other than non-virtual tables and indexes
	(including indexes created by UNIQUE or PRIMARY key constraints) do not
	require any other data structures to be created within the database
	file.

      <p>
        Tables and indexes on the other hand, are represented within the
	database file by both an entry in the schema table and a B-Tree
	structure stored elsewhere in the file. The specific B-Tree associated
        with each database table or index is identified by its root page
        number, which is stored in the 4th field of the schema table record.
	In a non-auto-vacuum database, the B-Tree root pages may be stored
	anywhere within the database file. For an auto-vacuum database, all
        B-Tree root pages must at all times form a contiguous set starting
	at page 3 of the database file, skipping any pages that are required to
	be used as pointer-map pages (see section
        <cite>pointer_map_pages</cite>).
      <p>
	As noted in section <cite>file_header</cite>, in an auto-vacuum
        database the page number of the page immediately following the
        final root page in the contiguous set of root pages is stored
        as a 4 byte big-endian integer at byte offset 52 of the database
	file header. Unless that page is itself a pointer-map page, in which
        case the page number of the page following it is stored instead.

      <p>
        For example, if the schema of a logical database is created using
        the following SQL statements:
      <pre>
          CREATE TABLE abc(a, b, c);
          CREATE INDEX i1 ON abc(b, c);
          CREATE TABLE main.def(a PRIMARY KEY, b, c, UNIQUE(b, c));
          CREATE VIEW v1 AS SELECT * FROM abc;
      </pre>
      <p>
        Then the schema table would contain a total of 7 records, as follows:

      <table class=striped>
        <tr><th>Field 1<th>Field 2<th>Field 3<th>Field 4<th>Field 5
        <tr><td>table <td>abc <td>abc <td>2 <td>CREATE TABLE abc(a, b, c)
        <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