Documentation Source Text

<p>To circumvent this limitation, the amalgamation is also available in
a split form, consisting of files "sqlite3-1.c", "sqlite3-2.c", and
so forth, where each file is less than 32,768 lines in length and
where the concatenation of the files contain all of the code for the
complete amalgamation.  Then there is a separate source file named
"sqlite3-all.c" which basically consists of code like this:

<blockquote><pre>
#include "sqlite3-1.c"
#include "sqlite3-2.c"
#include "sqlite3-3.c"
#include "sqlite3-4.c"
#include "sqlite3-5.c"
#include "sqlite3-6.c"
#include "sqlite3-7.c"
</pre></blockquote>

<p>Applications using the split amalgamation simply compile against
"sqlite3-all.c" instead of "sqlite3.c".  The two files work exactly
the same.  But with "sqlite3-all.c", no single source file contains more
than 32,767 lines of code, and so it is more convenient to use some
debuggers.  The downside of the split amalgamation is that it consists
of 6 C source code files instead of just 1.

split amalgamation), first
[get the canonical source code] from one of the three servers.
Then, on both unix-like systems and on Windows systems that have the
free [http://mingw.org/wiki/msys|MinGW] development environment
installed, the amalgamation can be built using the
following commands:

<blockquote><pre>
sh configure
make sqlite3.c
</pre></blockquote>

<p>To build using Microsoft Visual C++, run this command:

<blockquote><pre>
nmake /f makefile.msc sqlite3.c
</pre></blockquote>

<p>In both cases, the split amalgamation can be obtained by
substituting "sqlite3-all.c" for "sqlite3.c" as the make target.

<h2>Dependencies</h2>

<p>The build process makes extensive use of the 

<p>
^When a new row is inserted into an SQLite table, the ROWID can either
be specified as part of the INSERT statement or it can be assigned
automatically by the database engine.  ^(To specify a ROWID manually,
just include it in the list of values to be inserted.  For example:
</p>

<blockquote><pre>
CREATE TABLE test1(a INT, b TEXT);
INSERT INTO test1(rowid, a, b) VALUES(123, 5, 'hello');
</pre></blockquote>)^

<p>
^If no ROWID is specified on the insert, or if the specified ROWID has a value
of NULL, then an appropriate ROWID is created
automatically.  ^The usual algorithm is to give the newly created row
a ROWID that is one larger than the largest ROWID in the table prior
to the insert.  ^If the table is initially empty, then a ROWID of 1 is

<p>
^The dbstat virtual table is an [eponymous virtual table], meaning
that is not necessary to run [CREATE VIRTUAL TABLE] to create an
instance of the dbstat virtual table before using it.  The "dbstat"
module name can be used as if it were a table name to query the
dbstat virtual table directly.  For example:

<blockquote><pre>
SELECT * FROM dbstat;
</pre></blockquote>

<p>
If a named virtual table that uses the dbstat module is desired,
then the recommended way to create an instance of the dbstat
virtual table is as follows:

<blockquote><pre>
CREATE VIRTUAL TABLE temp.stat USING dbstat(main);
</pre></blockquote>

<p>
Note the "temp." qualifier before the virtual table name ("stat").  This
qualifier causes the virtual table to be temporary - to only exist for
the duration of the current database connection.  This is the
recommended approach.

<p>
The "main" argument to dbstat is default schema
for which information is to be provided.  The default is "main", and
so the use of "main" in the example above is redundant.  For any
particular query, the schema can be changed by specifying the
alternative schema as a function argument to the virtual table
name in the FROM clause of the query.  (See further discussion of
[table-valued functions in the FROM clause] for more details.)

<p>
The schema for the dbstat virtual table is this:
<blockquote><pre>
CREATE TABLE dbstat(
  name       STRING,     -- Name of table or index
  path       INTEGER,    -- Path to page from root
  pageno     INTEGER,    -- Page number
  pagetype   STRING,     -- 'internal', 'leaf' or 'overflow'
  ncell      INTEGER,    -- Cells on page (0 for overflow)
  payload    INTEGER,    -- Bytes of payload on this page
  unused     INTEGER,    -- Bytes of unused space on this page
  mx_payload INTEGER,    -- Largest payload size of all cells on this page
  pgoffset   INTEGER,    -- Offset of page in file
  pgsize     INTEGER,    -- Size of the page
  schema     TEXT HIDDEN -- The database being analyzed
);
</pre></blockquote>

<p>
There is a single row of the dbstat table for each page in the
database file.  Freelist pages, the lock page, and
pointer-map pages of the database file do not appear in the
dbstat virtual table.

Overflow pages are specified by appending a '+' character and a 
six-digit hexadecimal value to the path to the cell they are linked
from. For example, the three overflow pages in a chain linked from 
the left-most cell of the 450th child of the root page are identified
by the paths:

<blockquote><pre>
'/1c2/000+000000'         // First page in overflow chain
'/1c2/000+000001'         // Second page in overflow chain
'/1c2/000+000002'         // Third page in overflow chain
</pre></blockquote>

<p>
If the paths are sorted using the BINARY collation sequence, then
the overflow pages associated with a cell will appear earlier in the
sort-order than its child page:

<blockquote><pre>
'/1c2/000/'               // Left-most child of 451st child of root
</pre></blockquote>

<h1>Example uses of the dbstat virtual table</h1>

<p>
To find the total number of pages used to store table "xyz" in schema "aux1",
use:

<blockquote><pre>
SELECT count(*) FROM dbstat('aux1') WHERE name='xyz';
</pre></blockquote>

<p>
To see how efficiently the content of a table is stored on disk,
compute the amount of space used to hold actual content divided
by the total amount of disk space used.  The closer this number
is to 100%, the more efficient the packing.  (In this example, the
'xyz' table is assumed to be in the 'main' schema.)

<blockquote><pre>
SELECT sum(pgsize-unused)/sum(pgsize) FROM dbstat WHERE name='xyz';
</pre></blockquote>

<p>
To find the average fan-out for a table, run:

<blockquote><pre>
SELECT avg(ncell) FROM dbstat WHERE name='xyz' AND pagetype='internal';
</pre></blockquote>


<p>
Modern filesystems operate faster when disk accesses are sequential.
Hence, SQLite will run faster if the content of the database file
is on sequential pages.  To find out what fraction of the pages in
a database are sequential (and thus obtain a measurement that might
be useful in determining when to [VACUUM]), run a query like the following:

<blockquote><pre>
CREATE TEMP TABLE s(rowid INTEGER PRIMARY KEY, pageno INT);
INSERT INTO s(pageno) SELECT pageno FROM dbstat ORDER BY path;
SELECT sum(s1.pageno+1==s2.pageno)*1.0/count(*)
  FROM s AS s1, s AS s2
 WHERE s1.rowid+1=s2.rowid;
DROP TABLE s;
</pre></blockquote>

<title>Partial Indexes</title>
<tcl>
hd_keywords {partial index} {partial indexes} {partial indices}
</tcl>
<fancy_format>

<h1>Introduction</h1>

<p>
A partial index is an index over a subset of the rows of a table.
</p>

<p>
^The columns referenced in the WHERE clause of a partial index can be
any of the columns in the table, not just columns that happen to be
indexed.  However, it is very common for the WHERE clause
expression of a partial index to be a simple expression on the column
being indexed.  The following is a typical example:</p>

<blockquote>
^(CREATE INDEX po_parent ON purchaseorder(parent_po) WHERE parent_po IS NOT NULL;)^
</blockquote>

<p>In the example above, if most purchase orders do not have a "parent"
purchase order, then most parent_po values will be NULL.  That means only
a small subset of the rows in the purchaseorder table will be indexed.
Hence the index will take up much less space.  And changes to the original
purchaseorder table will run faster since the po_parent index only needs
to be updated for those exceptional rows where parent_po is not NULL.
But the index is still useful for querying.  ^(In particular, if one wants
to know all "children" of a particular purchase order "?1", the query
would be:

<blockquote>
SELECT po_num FROM purchaseorder WHERE parent_po=?1;
</blockquote>)^

<p>^The query above will use the po_parent index to help find the answer,
since the po_parent index contains entries for all rows of interest.
Note that since po_parent is smaller than a full index, the query will
likely run faster too.</p>

<h2>Unique Partial Indexes</h2>

<p>^A partial index definition may include the UNIQUE keyword.  ^If it
does, then SQLite requires every entry <em>in the index</em> to be unique.
This provides a mechanism for enforcing uniqueness across some subset of
the rows in a table.</p>

<p>For example, suppose you have a database of the members of a large
organization where each person is assigned to a particular "team".  
Each team has a "leader" who is also a member of that team.  ^(The
table might look something like this:</p>

<blockquote><pre>
CREATE TABLE person(
  person_id       INTEGER PRIMARY KEY,
  team_id         INTEGER REFERENCES team,
  is_team_leader  BOOLEAN,
  -- other fields elided
);
</pre></blockquote>)^

<p>The team_id field cannot be unique because there usually multiple people
on the same team.  One cannot make the combination of team_id and is_team_leader
unique since there are usually multiple non-leaders on each team.  ^(The
solution to enforcing one leader per team is to create a unique index
on team_id but restricted to those entries for which is_team_leader is
true:</p>

<blockquote>
CREATE UNIQUE INDEX team_leader ON person(team_id) WHERE is_team_leader;
</blockquote>)^

^(<p>Coincidentally, that same index is useful for locating the team leader
of a particular team:</p>

<blockquote>
SELECT person_id FROM person WHERE is_team_leader AND team_id=?1;
</blockquote>)^

<h1>Queries Using Partial Indexes</h1>

<p>Let X be the expression in the WHERE clause of a partial
index, and let W be the WHERE clause of a query that uses the
table that is indexed.  Then, the query is permitted to use 
the partial index if W&#x21d2;X, where the &#x21d2; operator

<ol>
<li><p>If W is AND-connected terms and X is
OR-connected terms and if any term of W
appears as a term of X, then the partial index is usable.</p>

<p>^(For example, let the index be
<blockquote>
CREATE INDEX ex1 ON tab1(a,b) WHERE a=5 OR b=6;
</blockquote>
<p>And let the query be:
<blockquote>
SELECT * FROM tab1 WHERE b=6 AND a=7; <i>-- uses partial index</i>
</blockquote>
<p>Then the index is usable by the query because the "b=6" term appears
in both the index definition and in the query.)^  Remember: terms in the
index should be OR-connected and terms in the query should be AND-connected.</p>

<p>The terms in W and X must match exactly.  SQLite does not
do algebra to try to get them to look the same.
^The term "b=6" does not match "b=3+3" or "b-6=0" or "b BETWEEN 6 AND 6".
^"b=6" will match to "6=b" as long as "b=6" is on the index and "6=b" is
in the query.  ^If a term of the form "6=b" appears in the index, it will
never match anything.</p>

<li><p>^If a term in X is of the form "z IS NOT NULL" and if a term in
       W is a comparison operator on "z" other than "IS", then those
       terms match.</p>

^(<p>Example:  Let the index be
<blockquote>
CREATE INDEX ex2 ON tab2(b,c) WHERE c IS NOT NULL;
</blockquote>
<p>Then any query that uses operators =, &lt;, &gt;, &lt;=, &gt;=, &lt;&gt;,
or IN on column "c" would be usable with the partial index because those
comparison operators are only true if "c" is not NULL.)^  ^(So the following
query could use the partial index:
<blockquote>
SELECT * FROM tab2 WHERE b=456 AND c&lt;&gt;0;  <i>-- uses partial index</i>
</blockquote>)^
^(<p>But the next query can not use the partial index:
<blockquote>
SELECT * FROM tab2 WHERE b=456;  <i>-- cannot use partial index</i>
</blockquote>)^
<p>The latter query can not use the partial index because there might be
rows in the table with b=456 and where c is NULL.  But those rows would
not be in the partial index.
</ol>

<p>These two rules describe how the query planner for SQLite works as of
this writing (2013-08-01).  And the rules above will always be honored.
However, future versions of SQLite might incorporate a better theorem prover
that can find other cases where W&#x21d2;X is true and thus may
find more instances where partial indexes are useful.</p>

<h1>Supported Versions</h1>

<p>
Partial indexes have been supported in SQLite since version 3.8.0.

</p>

<p>Database files that contain partial indices are not readable or writable
by versions of SQLite prior to 3.8.0.  However, a database file created
by SQLite 3.8.0 is still readable and writable by prior versions as long
as its schema contains no partial indexes.  A database that is unreadable
by legacy versions of SQLite can be made readable simply by running
[DROP INDEX] on the partial indexes.</p>

  .fancy-toc1 {margin-left:6ex;}
  .fancy-toc2 {margin-left:12ex;}
  .fancy-toc3 {margin-left:18ex;}
  .fancy-toc4 {margin-left:24ex;}
  .fancy-toc5 {margin-left:30ex;}
  .fancy-toc6 {margin-left:36ex;}
  .fancy .codeblock {margin:0 10ex;}


}
@media screen and (max-width: 600px){
  /* Indentation amounts for narrow mobile screens */
  .toct {margin-left:0;}
  .fancy-toc1 {padding-left:4ex;text-indent:-2ex;}
  .fancy-toc2 {padding-left:8ex;text-indent:-4ex;}
  .fancy-toc3 {padding-left:12ex;text-indent:-6ex;}