Documentation Source Text

The aggregate functions shown below are available by default.  Additional
aggregate functions written in C may be added using the 
[sqlite3_create_function()]</a>
API.</p>

<p>
In any aggregate function that takes a single argument, that argument
can be preceeded by the keyword DISTINCT.  In such cases, duplicate
elements are filtered before being passed into the aggregate function.
For example, the function "count(distinct X)" will return the number
of distinct values of column X instead of the total number of non-null
values in column X.
</p>

<table border=0 cellpadding=10>
................................................................................

<tcl>
BubbleDiagram qualified-table-name
BubbleDiagram single-source
</tcl>

<p>The "INDEXED BY index-name" clause specifies that the named index
must be used in order to look up values on the preceeding table.
If index-name does not exist or cannot be used for the query, then
the preparation of the SQL statement fails.
The "NOT INDEXED" clause specifies that no index shall be used when
accessing the preceeding table, including implied indices create by
UNIQUE and PRIMARY KEY constraints.  However, the INTEGER PRIMARY KEY
can still be used to look up entries even when "NOT INDEXED" is specified.</p>

<p>Some SQL database engines provide non-standard "hint" mechanisms which
can be used to give the query optimizer clues about what indices it should
use for a particular statement.  The INDEX BY clause of SQLite is 
<em>not</em> a hinting mechanism and it should not be used as such.

The aggregate functions shown below are available by default.  Additional
aggregate functions written in C may be added using the 
[sqlite3_create_function()]</a>
API.</p>

<p>
In any aggregate function that takes a single argument, that argument
can be preceded by the keyword DISTINCT.  In such cases, duplicate
elements are filtered before being passed into the aggregate function.
For example, the function "count(distinct X)" will return the number
of distinct values of column X instead of the total number of non-null
values in column X.
</p>

<table border=0 cellpadding=10>
................................................................................

<tcl>
BubbleDiagram qualified-table-name
BubbleDiagram single-source
</tcl>

<p>The "INDEXED BY index-name" clause specifies that the named index
must be used in order to look up values on the preceding table.
If index-name does not exist or cannot be used for the query, then
the preparation of the SQL statement fails.
The "NOT INDEXED" clause specifies that no index shall be used when
accessing the preceding table, including implied indices create by
UNIQUE and PRIMARY KEY constraints.  However, the INTEGER PRIMARY KEY
can still be used to look up entries even when "NOT INDEXED" is specified.</p>

<p>Some SQL database engines provide non-standard "hint" mechanisms which
can be used to give the query optimizer clues about what indices it should
use for a particular statement.  The INDEX BY clause of SQLite is 
<em>not</em> a hinting mechanism and it should not be used as such.

    for example:</p>

    <blockquote>
PRAGMA <b>main.</b>locking_mode=EXCLUSIVE;
    </blockquote>

    <p>Then the locking mode applies only to the named database.  If no
    database name qualifier preceeds the "locking_mode" keyword then
    the locking mode is applied to all databases, including any new
    databases added by subsequent [ATTACH] commands.</p>

   <p>The "temp" database (in which TEMP tables and indices are stored)
   always uses exclusive locking mode.  The locking mode of temp cannot
   be changed.  All other databases use the normal locking mode by default
   and are affected by this pragma.</p>

    for example:</p>

    <blockquote>
PRAGMA <b>main.</b>locking_mode=EXCLUSIVE;
    </blockquote>

    <p>Then the locking mode applies only to the named database.  If no
    database name qualifier precedes the "locking_mode" keyword then
    the locking mode is applied to all databases, including any new
    databases added by subsequent [ATTACH] commands.</p>

   <p>The "temp" database (in which TEMP tables and indices are stored)
   always uses exclusive locking mode.  The locking mode of temp cannot
   be changed.  All other databases use the normal locking mode by default
   and are affected by this pragma.</p>

}
</pre></blockquote>

<p>For bitmask tests, testcase() macros are used to verify that every
bit of the bitmask effects the test.  For example, in the following block
of code, the condition is true if the mask contains either of two bits
indicating either a MAIN_DB or a TEMP_DB is being opened.  The testcase()
macros that preceed the if statement verify that both cases are tested:</p>

<blockquote><pre>
testcase( mask & SQLITE_OPEN_MAIN_DB );
testcase( mask & SQLITE_OPEN_TEMP_DB );
if( (mask & (SQLITE_OPEN_MAIN_DB|SQLITE_OPEN_TEMP_DB))!=0 ){ ... }
</pre></blockquote>

}
</pre></blockquote>

<p>For bitmask tests, testcase() macros are used to verify that every
bit of the bitmask effects the test.  For example, in the following block
of code, the condition is true if the mask contains either of two bits
indicating either a MAIN_DB or a TEMP_DB is being opened.  The testcase()
macros that precede the if statement verify that both cases are tested:</p>

<blockquote><pre>
testcase( mask & SQLITE_OPEN_MAIN_DB );
testcase( mask & SQLITE_OPEN_TEMP_DB );
if( (mask & (SQLITE_OPEN_MAIN_DB|SQLITE_OPEN_TEMP_DB))!=0 ){ ... }
</pre></blockquote>