Documentation Source Text

      <a href="http://www.sqlite.org/src/timeline">
      http://www.sqlite.org/src/timeline</a>.</p>
    }
    hd_close_aux
    hd_enable_main 1
  }
}










chng {2011 November 1 (3.7.9)} {
<li>If a search token (on the right-hand side of the MATCH operator) in
    [FTS4] begins with "&#94;" then that token must be the first in its field
    of the document.  <b>** Potentially Incompatible Change **</b>
<li>Added options [SQLITE_DBSTATUS_CACHE_HIT] and [SQLITE_DBSTATUS_CACHE_MISS]
    to the [sqlite3_db_status()] interface.

      <a href="http://www.sqlite.org/src/timeline">
      http://www.sqlite.org/src/timeline</a>.</p>
    }
    hd_close_aux
    hd_enable_main 1
  }
}

chng {2012 February 1 (3.7.10)} {
<li>The default [schema format number] is changed from 1 to 4.
    This means that, unless
    the [PRAGMA legacy_file_format | PRAGMA legacy_file_format=ON] statement is
    run, newly created database files will be unreadable by version of SQLite
    prior to 3.3.0 (2006-01-10).  It also means that the [descending indices]
    are enabled by default.
}

chng {2011 November 1 (3.7.9)} {
<li>If a search token (on the right-hand side of the MATCH operator) in
    [FTS4] begins with "&#94;" then that token must be the first in its field
    of the document.  <b>** Potentially Incompatible Change **</b>
<li>Added options [SQLITE_DBSTATUS_CACHE_HIT] and [SQLITE_DBSTATUS_CACHE_MISS]
    to the [sqlite3_db_status()] interface.

COMPILE_OPTION {SQLITE_DEFAULT_CACHE_SIZE=<i>&lt;pages&gt;</i>} {
  This macro sets the default size of the page-cache for each attached
  database, in pages. This can be overridden by the 
  [PRAGMA cache_size] command. The default value is 2000.
}

COMPILE_OPTION {SQLITE_DEFAULT_FILE_FORMAT=<i>&lt;1 or 4&gt;</i>} {
  The default schema-level file format used by SQLite when creating
  new database files is set by this macro.  The file formats are all
  very similar.  The difference between formats 1 and 4 is that format
  4 understands descending indices and has a tighter encoding for
  boolean values.


  SQLite (as of [version 3.6.0]) can read and write any file format
  between 1 and 4.  But older versions of SQLite might not be able to
  read formats greater than 1.  So that older versions of SQLite will
  be able to read and write database files created by newer versions
  of SQLite, the default file format is set to 1 for maximum
  compatibility.


  The file format for a new database can be set at runtime using
  the [PRAGMA legacy_file_format] command.
}

COMPILE_OPTION {SQLITE_DEFAULT_FILE_PERMISSIONS=<i>N</i>} {
  The default numeric file permissions for newly created database files
  under unix.  If not specified, the default is 0644 which means that
  the files is globally readable but only writable by the creator.

COMPILE_OPTION {SQLITE_DEFAULT_CACHE_SIZE=<i>&lt;pages&gt;</i>} {
  This macro sets the default size of the page-cache for each attached
  database, in pages. This can be overridden by the 
  [PRAGMA cache_size] command. The default value is 2000.
}

COMPILE_OPTION {SQLITE_DEFAULT_FILE_FORMAT=<i>&lt;1 or 4&gt;</i>} {
  The default [schema format number] used by SQLite when creating
  new database files is set by this macro.  The schema formats are all
  very similar.  The difference between formats 1 and 4 is that format
  4 understands [descending indices] and has a tighter encoding for
  boolean values.

  All versions of SQLite since 3.3.0 (2006-01-10)
  can read and write any schema format
  between 1 and 4.  But older versions of SQLite might not be able to
  read formats greater than 1.  So that older versions of SQLite will
  be able to read and write database files created by newer versions
  of SQLite, the default schema format was set to 1 for SQLite versions
  through 3.7.9 (2011-11-01).  Beginning with version 3.7.10, the default
  schema format is 4.

  The schema format number for a new database can be set at runtime using
  the [PRAGMA legacy_file_format] command.
}

COMPILE_OPTION {SQLITE_DEFAULT_FILE_PERMISSIONS=<i>N</i>} {
  The default numeric file permissions for newly created database files
  under unix.  If not specified, the default is 0644 which means that
  the files is globally readable but only writable by the creator.

journal or write-ahead log contains critical state information needed 
to restore the main database file to a consistent state.  When a rollback 
journal or write-ahead log contain information necessary for recovering 
the state of the database, they are called a "hot journal" or "hot WAL file".
Hot journals and WAL files are only a factor during error recovery
scenarios and so are uncommon, but they are part of the state of an SQLite
database and so cannot be ignored.  This document defines the format
of a rollback journal and the write-ahead log file, but main focus is
on the main database file.</p>

<h3>1.1 Pages</h3>

<p>The main database file consists of one or more pages.  ^The size of a
page is a power of two between 512 and 65536 inclusive.  All pages within
the same database are the same size.  ^The page size for a database file
................................................................................
prepared statement is compiled against a specific version of the
database schema.  ^Whenever the database schema changes, the statement
must be reprepared.  ^Whenever a prepared statement runs, it first checks
the schema cookie to make sure the value is the same as when the statement
was prepared and if the schema cookie has changed, the statement aborts
in order to force the statement to be reprepared.</p>


<h4>1.2.10 Schema format number</h4>

<p>The schema format number is a 4-byte big-endian integer at offset 44.
The schema format number is similar to the file format read and write
version numbers at offsets 18 and 19 except that the schema format number
refers to the high-level SQL formatting rather than the low-level b-tree
formatting.  Four schema format numbers are currently defined:</p>
................................................................................
[ALTER TABLE | ALTER TABLE ... ADD COLUMN] functionality.  Support for
reading and writing format 2 was added in SQLite version 3.1.3 
on 2005-02-19.</li>
<li value=3>Format 3 adds the ability of extra columns added by
[ALTER TABLE | ALTER TABLE ... ADD COLUMN] to have non-NULL default
values.  This capability was added in SQLite version 3.1.4 
on 2005-03-11.</li>
<li value=4>^Format 4 causes SQLite to respect the DESC keyword on

index declarations.  (^The DESC keyword is ignored in indices for 
formats 1, 2, and 3.)
^Format 4 also adds two new boolean record type values ([serial types]
8 and 9.)  Support for format 4 was added in SQLite 3.3.0 on
2006-01-10.</li>
</ol>

<p>^New database files created by SQLite use format 1 by default, so
that database files created by newer versions of SQLite can still
be read by older versions of SQLite.
^The [legacy_file_format pragma] can be used to cause SQLite
to create new database files using format 4.  Future versions of 
SQLite may begin to create files using format 4 by default.
The format version number can be made to default to 4 instead of 1 by
setting [SQLITE_DEFAULT_FILE_FORMAT]=4 at compile-time.
</p>

<h4>1.2.11 Suggested cache size</h4>

<p>The 4-byte big-endian signed integer at offset 48 is the suggested
cache size in pages for the database file.  The value is a suggestion
only and SQLite is under no obligation to honor it.  The absolute value
................................................................................

<p>The two 4-byte big-endian integers at offsets 52 and 64 are used
to manage the [auto_vacuum] and [incremental_vacuum] modes.  ^If
the integer at offset 52 is zero then pointer-map (ptrmap) pages are
omitted from the database file and neither auto_vacuum nor
incremental_vacuum are supported.  ^If the integer at offset 52 is
non-zero then it is the page number of the largest root page in the
database file, the database file contain ptrmap pages, and the
mode must be either auto_vacuum or incremental_vacuum.  ^In this latter
case, the integer at offset 64 is true for incremental_vacuum and
false for auto_vacuum.  ^If the integer at offset 52 is zero then
the integer at offset 64 must also be zero.</p>

<h4>1.2.13 Text encoding</h4>

................................................................................
in size contains no lock-byte page.  A database file larger than
1073741824 contains exactly one lock-byte page.
</p>

<p>The lock-byte page is set aside for use by the operating-system specific
[VFS] implementation in implementing the database file locking primitives.
^SQLite does not use the lock-byte page.  ^The SQLite core 

will never read or write, though operating-system specific [VFS] 
implementations may choose to read or write bytes on the lock-byte 
page according to the 
needs and proclivities of the underlying system.  The unix and win32
[VFS] implementations that come built into SQLite do not write to the
lock-byte page, but third-party VFS implementations for
other operating systems might.</p>

journal or write-ahead log contains critical state information needed 
to restore the main database file to a consistent state.  When a rollback 
journal or write-ahead log contain information necessary for recovering 
the state of the database, they are called a "hot journal" or "hot WAL file".
Hot journals and WAL files are only a factor during error recovery
scenarios and so are uncommon, but they are part of the state of an SQLite
database and so cannot be ignored.  This document defines the format
of a rollback journal and the write-ahead log file, but the focus is
on the main database file.</p>

<h3>1.1 Pages</h3>

<p>The main database file consists of one or more pages.  ^The size of a
page is a power of two between 512 and 65536 inclusive.  All pages within
the same database are the same size.  ^The page size for a database file
................................................................................
prepared statement is compiled against a specific version of the
database schema.  ^Whenever the database schema changes, the statement
must be reprepared.  ^Whenever a prepared statement runs, it first checks
the schema cookie to make sure the value is the same as when the statement
was prepared and if the schema cookie has changed, the statement aborts
in order to force the statement to be reprepared.</p>

<tcl>hd_fragment {schemaformat} {schema format number}</tcl>
<h4>1.2.10 Schema format number</h4>

<p>The schema format number is a 4-byte big-endian integer at offset 44.
The schema format number is similar to the file format read and write
version numbers at offsets 18 and 19 except that the schema format number
refers to the high-level SQL formatting rather than the low-level b-tree
formatting.  Four schema format numbers are currently defined:</p>
................................................................................
[ALTER TABLE | ALTER TABLE ... ADD COLUMN] functionality.  Support for
reading and writing format 2 was added in SQLite version 3.1.3 
on 2005-02-19.</li>
<li value=3>Format 3 adds the ability of extra columns added by
[ALTER TABLE | ALTER TABLE ... ADD COLUMN] to have non-NULL default
values.  This capability was added in SQLite version 3.1.4 
on 2005-03-11.</li>
<li value=4>^Format 4 causes SQLite to respect the
[descending indices | DESC keyword] on
index declarations.  (^The DESC keyword is ignored in indices for 
formats 1, 2, and 3.)
^Format 4 also adds two new boolean record type values ([serial types]
8 and 9.)  Support for format 4 was added in SQLite 3.3.0 on
2006-01-10.</li>
</ol>

<p>^New database files created by SQLite use format 4 by default.


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

The format version number can be made to default to 1 instead of 4 by
setting [SQLITE_DEFAULT_FILE_FORMAT]=1 at compile-time.
</p>

<h4>1.2.11 Suggested cache size</h4>

<p>The 4-byte big-endian signed integer at offset 48 is the suggested
cache size in pages for the database file.  The value is a suggestion
only and SQLite is under no obligation to honor it.  The absolute value
................................................................................

<p>The two 4-byte big-endian integers at offsets 52 and 64 are used
to manage the [auto_vacuum] and [incremental_vacuum] modes.  ^If
the integer at offset 52 is zero then pointer-map (ptrmap) pages are
omitted from the database file and neither auto_vacuum nor
incremental_vacuum are supported.  ^If the integer at offset 52 is
non-zero then it is the page number of the largest root page in the
database file, the database file will contain ptrmap pages, and the
mode must be either auto_vacuum or incremental_vacuum.  ^In this latter
case, the integer at offset 64 is true for incremental_vacuum and
false for auto_vacuum.  ^If the integer at offset 52 is zero then
the integer at offset 64 must also be zero.</p>

<h4>1.2.13 Text encoding</h4>

................................................................................
in size contains no lock-byte page.  A database file larger than
1073741824 contains exactly one lock-byte page.
</p>

<p>The lock-byte page is set aside for use by the operating-system specific
[VFS] implementation in implementing the database file locking primitives.
^SQLite does not use the lock-byte page.  ^The SQLite core 
will never read or write the lock-byte page,
though operating-system specific [VFS] 
implementations may choose to read or write bytes on the lock-byte 
page according to the 
needs and proclivities of the underlying system.  The unix and win32
[VFS] implementations that come built into SQLite do not write to the
lock-byte page, but third-party VFS implementations for
other operating systems might.</p>

</td>
<td width="20"></td><td bgcolor="#044a64" width="1"></td><td width="20"></td>
<td valign="top">
<h3>Current Status</h3>

<p><ul>
<li><a href="releaselog/3_7_9.html">Version 3.7.9</a>
of SQLite is recommended for all new development.
Upgrading from version 3.7.6.3, 3.7.7, 3.7.7.1, or 3.7.8 is optional.
Upgrading from all other SQLite versions is recommended.</li>
</ul></p>

<h3>Common Links</h3>

<p><ul>
<li> <a href="features.html">Features</a> </li>

</td>
<td width="20"></td><td bgcolor="#044a64" width="1"></td><td width="20"></td>
<td valign="top">
<h3>Current Status</h3>

<p><ul>
<li><a href="releaselog/3_7_10.html">Version 3.7.10</a>
of SQLite is recommended for all new development.
Upgrading from version 3.7.6.3, 3.7.7, 3.7.7.1, 3.7.8, or 3.7.9 is optional.
Upgrading from all other SQLite versions is recommended.</li>
</ul></p>

<h3>Common Links</h3>

<p><ul>
<li> <a href="features.html">Features</a> </li>

</tcl>

<p>^The CREATE INDEX command consists of the keywords "CREATE INDEX" followed
by the name of the new index, the keyword "ON", the name of a previously
created table that is to be indexed, and a parenthesized list of names of
columns in the table that are used for the index key.</p>


<p>^Each column name can be followed by one of the "ASC" or "DESC" keywords
to indicate sort order.  ^The sort order may or may not be ignored depending
on the database file format.  ^The "legacy" file format ignores index

sort order.  ^The descending index file format takes index sort order
into account.  ^(Only copies of SQLite newer than [version 3.3.0] 
(released on 2006-01-10) are able to understand the newer descending
index file format and so for compatibility with older versions of
SQLite, the legacy file format is generated by default.)^  ^Use the
[legacy_file_format] pragma to modify this behavior and generate
databases that use the newer file format.  Future versions of SQLite
may begin to generate the newer file format by default.</p>



<p>^The COLLATE clause optionally following each column name defines a
collating sequence used for text entries in that column.
^The default collating
sequence is the collating sequence defined for that column in the
[CREATE TABLE] statement.  ^Or if no collating sequence is otherwise defined,
the built-in BINARY collating sequence is used.</p>

</tcl>

<p>^The CREATE INDEX command consists of the keywords "CREATE INDEX" followed
by the name of the new index, the keyword "ON", the name of a previously
created table that is to be indexed, and a parenthesized list of names of
columns in the table that are used for the index key.</p>

<tcl>hd_fragment {descidx} {descending indices} {descending index}</tcl>
<p>^Each column name can be followed by one of the "ASC" or "DESC" keywords
to indicate sort order.  ^The sort order may or may not be ignored depending
on the database file format, and in particular the [schema format number].
^The "legacy" schema format (1) ignores index
sort order.  ^The descending index schema format (4) takes index sort order
into account.  ^(Only copies of SQLite newer than [version 3.3.0] 
(released on 2006-01-10) are able to understand the newer descending


index file format.)^  For compatibility, version of SQLite between 3.3.0
and 3.7.9 use the legacy schema format by default.  The newer schema format is
used by default in version 3.7.10 and later.
^The [legacy_file_format pragma] can be used to change set the specific
behavior for any version of SQLite.</p>

<p>^The COLLATE clause optionally following each column name defines a
collating sequence used for text entries in that column.
^The default collating
sequence is the collating sequence defined for that column in the
[CREATE TABLE] statement.  ^Or if no collating sequence is otherwise defined,
the built-in BINARY collating sequence is used.</p>