Documentation Source Text

<li>The --deserialize option associated with opening a new database cause the
    database file to be read into memory and accessed using the
    [sqlite3_deserialize()] API. This simplifies running tests on a database
    without modifying the file on disk.
</ol>
<li>Enhancements to the [geopoly] extension:
<ol type="a">
<li>Aways stores polygons
    using the binary format, which is faster and uses less space.
<li>Added the [geopoly_regular()] function.
<li>Added the [geopoly_ccw()] function.
</ol>
<li>Enhancements to the [session] extension:
<ol type="a">
<li>Added the [SQLITE_CHANGESETAPPLY_INVERT] flag

<li>The --deserialize option associated with opening a new database cause the
    database file to be read into memory and accessed using the
    [sqlite3_deserialize()] API. This simplifies running tests on a database
    without modifying the file on disk.
</ol>
<li>Enhancements to the [geopoly] extension:
<ol type="a">
<li>Always stores polygons
    using the binary format, which is faster and uses less space.
<li>Added the [geopoly_regular()] function.
<li>Added the [geopoly_ccw()] function.
</ol>
<li>Enhancements to the [session] extension:
<ol type="a">
<li>Added the [SQLITE_CHANGESETAPPLY_INVERT] flag

of the [bytecode engine].  By omitting this interface, a single conditional
is removed from the inner loop of the [bytecode engine], helping SQL statements
to run slightly faster.

<li><p><b>[SQLITE_OMIT_SHARED_CACHE]</b>.
Omitting the possibility of using [shared cache] allows many conditionals
in performance-critical sections of the code to be eliminated.  This can
give a noticable improvement in performance.

<li><p><b>[SQLITE_USE_ALLOCA]</b>.
Make use of alloca() for dynamically allocating temporary stack space for 
use within a single function, on systems that support alloca().  Without
this option, temporary space is allocated from the heap.
</ol>

of the [bytecode engine].  By omitting this interface, a single conditional
is removed from the inner loop of the [bytecode engine], helping SQL statements
to run slightly faster.

<li><p><b>[SQLITE_OMIT_SHARED_CACHE]</b>.
Omitting the possibility of using [shared cache] allows many conditionals
in performance-critical sections of the code to be eliminated.  This can
give a noticeable improvement in performance.

<li><p><b>[SQLITE_USE_ALLOCA]</b>.
Make use of alloca() for dynamically allocating temporary stack space for 
use within a single function, on systems that support alloca().  Without
this option, temporary space is allocated from the heap.
</ol>

</codeblock>

<tcl>hd_fragment {dbstatagg} {DBSTAT aggregated mode}</tcl>
<h1>Aggregated Data</h1>

<p>
Beginning with SQLite version 3.31.0 ([dateof:3.31.0]), the DBSTAT table
has a new [hidden column] named "aggregate", which if constrainted to be
TRUE will cause DBSTAT to generate one row per btree in the database,
rather than one row per page.  When running in aggregated mode, the 
"path", "pagetype", and "pgoffset" columns are always NULL and the
"pageno" column holds the number of pages in the entire btree, rather
than the number of the page that corresponds to the row.

<p>

</codeblock>

<tcl>hd_fragment {dbstatagg} {DBSTAT aggregated mode}</tcl>
<h1>Aggregated Data</h1>

<p>
Beginning with SQLite version 3.31.0 ([dateof:3.31.0]), the DBSTAT table
has a new [hidden column] named "aggregate", which if constrained to be
TRUE will cause DBSTAT to generate one row per btree in the database,
rather than one row per page.  When running in aggregated mode, the 
"path", "pagetype", and "pgoffset" columns are always NULL and the
"pageno" column holds the number of pages in the entire btree, rather
than the number of the page that corresponds to the row.

<p>

}
doc {Aggregate SQL Functions} {lang_aggfunc.html} {
  General-purpose built-in aggregate SQL functions.
}
doc {Date and Time SQL Functions} {lang_datefunc.html} {
  SQL functions for manipulating dates and times.
}






doc {System.Data.SQLite} {http://system.data.sqlite.org/} {
  C#/.NET bindings for SQLite
}
doc {Tcl API} {tclsqlite.html} {
  A description of the TCL interface bindings for SQLite.
}
doc {DataTypes} {datatype3.html} {

}
doc {Aggregate SQL Functions} {lang_aggfunc.html} {
  General-purpose built-in aggregate SQL functions.
}
doc {Date and Time SQL Functions} {lang_datefunc.html} {
  SQL functions for manipulating dates and times.
}
doc {Window Functions} {windowfunctions.html} {
  SQL Window functions.
}
doc {Generated Columns} {gencol.html} {
  Stored and virtual columns in table definitions.
}
doc {System.Data.SQLite} {http://system.data.sqlite.org/} {
  C#/.NET bindings for SQLite
}
doc {Tcl API} {tclsqlite.html} {
  A description of the TCL interface bindings for SQLite.
}
doc {DataTypes} {datatype3.html} {

and 3.7.9 use the legacy schema format by default.  The newer schema format is
used by default in version 3.7.10 ([dateof: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 NULLS FIRST and NULLS LAST predicates are not supported
for indexes.  For [sort order|sorting purposes], SQLite considers NULL values 
to be smaller than all other values.  Hences NULL values always appear at
the beginning of an ASC index and at the end of a DESC index.</p>

<tcl>hd_fragment collidx {COLLATE}</tcl>
<p>^The COLLATE clause optionally following each column name
or expression defines a
collating sequence used for text entries in that column.
^The default collating

and 3.7.9 use the legacy schema format by default.  The newer schema format is
used by default in version 3.7.10 ([dateof: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 NULLS FIRST and NULLS LAST predicates are not supported
for indexes.  For [sort order|sorting purposes], SQLite considers NULL values 
to be smaller than all other values.  Hence NULL values always appear at
the beginning of an ASC index and at the end of a DESC index.</p>

<tcl>hd_fragment collidx {COLLATE}</tcl>
<p>^The COLLATE clause optionally following each column name
or expression defines a
collating sequence used for text entries in that column.
^The default collating

then it will fail when moved to another database that uses a more rigid
and unforgiving type enforcement policy.

<p>
Flexible typing is considered a feature of SQLite, not a bug.
Nevertheless, we recognize that this feature does sometimes cause
confusion and pain for developers who are acustomed to working with
other databases that are more judgemental with regard to data types.
In retrospect, perhaps it would have been better if SQLite had merely
implemented an ANY datatype so that developers could explicitly state
when they wanted to use flexible typing, rather than making flexible
typing the default.
But that is not something that can be changed now without breaking
the millions of applications and trillions of database files that 
already use SQLite's flexible typing feature.

then it will fail when moved to another database that uses a more rigid
and unforgiving type enforcement policy.

<p>
Flexible typing is considered a feature of SQLite, not a bug.
Nevertheless, we recognize that this feature does sometimes cause
confusion and pain for developers who are acustomed to working with
other databases that are more judgmental with regard to data types.
In retrospect, perhaps it would have been better if SQLite had merely
implemented an ANY datatype so that developers could explicitly state
when they wanted to use flexible typing, rather than making flexible
typing the default.
But that is not something that can be changed now without breaking
the millions of applications and trillions of database files that 
already use SQLite's flexible typing feature.

<ol>
<li value="7"><p>
If the application includes any [custom SQL functions] or 
[custom virtual tables] that have side effects or that might leak
privileged information, then the application should use one or more
of the techniques below to prevent a maliciously crafted database
schema from surreptiously running those SQL functions and/or
virtual tables for neferious purposes:
<ol type="a">
<li> Invoke [sqlite3_db_config](db,[SQLITE_DBCONFIG_TRUSTED_SCHEMA],0,0)
     on each [database connection] as soon as it is opened.
<li> Run the [PRAGMA trusted_schema=OFF] statement on each database connection
     as soon as it is opened.
<li> Compile SQLite using the [-DSQLITE_TRUSTED_SCHEMA=0] compile-time option.
<li> Disable the surreptious use of custom SQL functions and virtual tables
     by setting the [SQLITE_DIRECTONLY] flag on all custom SQL functions and
     the [SQLITE_VTAB_DIRECTONLY] flag on all custom virtual tables.
</ol>
</li>

<li><p>
If the application does not use triggers or views, consider disabling the
................................................................................
</p>
</ol>

<p>
For reading database files that are unusually high-risk, such as database
files that are received from remote machines, and possibly from anonymous
contributors, the following extra precautions
might be justifed.  These added defenses come with performance costs,
however, and so are probably not appropriate in every situation:

<ol>
<li value="9"><p>
Run [PRAGMA integrity_check] or [PRAGMA quick_check] on the database
as the first SQL statement after opening the database files and
prior to running any other SQL statements.  Reject and refuse to

<ol>
<li value="7"><p>
If the application includes any [custom SQL functions] or 
[custom virtual tables] that have side effects or that might leak
privileged information, then the application should use one or more
of the techniques below to prevent a maliciously crafted database
schema from surreptitiously running those SQL functions and/or
virtual tables for nefarious purposes:
<ol type="a">
<li> Invoke [sqlite3_db_config](db,[SQLITE_DBCONFIG_TRUSTED_SCHEMA],0,0)
     on each [database connection] as soon as it is opened.
<li> Run the [PRAGMA trusted_schema=OFF] statement on each database connection
     as soon as it is opened.
<li> Compile SQLite using the [-DSQLITE_TRUSTED_SCHEMA=0] compile-time option.
<li> Disable the surreptitious use of custom SQL functions and virtual tables
     by setting the [SQLITE_DIRECTONLY] flag on all custom SQL functions and
     the [SQLITE_VTAB_DIRECTONLY] flag on all custom virtual tables.
</ol>
</li>

<li><p>
If the application does not use triggers or views, consider disabling the
................................................................................
</p>
</ol>

<p>
For reading database files that are unusually high-risk, such as database
files that are received from remote machines, and possibly from anonymous
contributors, the following extra precautions
might be justified.  These added defenses come with performance costs,
however, and so are probably not appropriate in every situation:

<ol>
<li value="9"><p>
Run [PRAGMA integrity_check] or [PRAGMA quick_check] on the database
as the first SQL statement after opening the database files and
prior to running any other SQL statements.  Reject and refuse to