Documentation Source Text

<h3>6.1 Assigning Collating Sequences from SQL</h3>

<p>
^Every column of every
table has an associated collating function.  ^If no collating function
is explicitly defined, then the collating function defaults to BINARY.
^The COLLATE clause of the [column definition] is used
to define alternative collating functions for a column.
</p>  

<p>
^(The rules for determining which collating function to use for a
binary comparison operator (=, &lt;, &gt;, &lt;=, &gt;=, !=, IS, and
IS NOT) are as follows and in the order shown:)^

preceded by one or more unary "+" operators is still considered a column name.
</p></li>

<li><p>^Otherwise, the BINARY collating function is used for comparison.
</p></li>
</ol>

<p>
^An operand of a comparison is considered to have an explicit
collating function assignment (rule 1 above) 
if any subexpression of the operand uses
the postfix [COLLATE operator].  ^Thus, if a [COLLATE operator] is used
anywhere in a comparision expression, the collating function defined
by that operator is used for string comparison regardless of what 
table columns might be a part of that expression.  ^If two or more
[COLLATE operator] subexpressions appear anywhere in a comparison, the 
left most explicit collating function is used regardless of how deeply the
COLLATE operators are nested in the expression and regardless of
how the expression is parenthesized.
</p>

<p>
^The expression "x BETWEEN y and z" is logically
equivalent to two comparisons "x &gt;= y AND x &lt;= z" and works with
respect to collating functions as if it were two separate comparisons.
^(The expression "x IN (SELECT y ...)" is handled in the same way as the
expression "x = y" for the purposes of determining the collating sequence.)^
^(The collating sequence used for expressions of the form 

of the two records.  The sort order of individual columns is as
follows:</p>

<ol>
<li>^NULL values (serial type 0) sort first
<li>^Numeric values (serial types 1 through 9) sort next and in numeric order
<li>^Text values (even serial types 12 and larger) sort next in the order
    determined by the columns [collating function]
<li>^BLOB values (odd serial types 13 and larger) sort last in order 
    determined by memcmp().
</ol>

<p>A [collating function] for each column is necessary in order to compute
the order of text fields.  ^SQLite defines three built-in collating functions:
</p>

<blockquote><table border=0 cellspacing=10>
<tr>^<td valign=top>BINARY
    <td>Strings are compared byte by byte using the memcmp() function
        from the standard C library.
<tr>^<td valign=top>NOCASE

</table></blockquote>

<p>^Additional application-specific collating functions can be added to
SQLite using the [sqlite3_create_collation()] interface.</p>

<p>^The default collating function for all strings is BINARY.
^Alternative collating functions for table columns can be specified in the
[CREATE TABLE] statement using the COLLATE clause on the [column definition].
^When a column is indexed, the same collating function specified in the
[CREATE TABLE] statement is used for the column in the index, by default,
though this can be overridden using a COLLATE clause in the 
[CREATE INDEX] statement.

<h3>2.3 Representation Of SQL Tables</h3>

<p>^A CREATE TABLE statement is basically the keywords "CREATE TABLE"
followed by the name of a new table and a parenthesized list of column
definitions and constraints.  
^Tables names that begin with "<b>sqlite_</b>" are reserved
for use by SQLite itself and cannot normally
appear in a CREATE TABLE statement.</p>

<tcl>hd_fragment {tablecoldef} {column definition} {column definitions}</tcl>
<p>^Each column definition is the name of the column optionally followed by the
[datatype] for that column, then one or more optional column constraints.
^SQLite uses [dynamic typing]; 
the datatype for the column does not restrict what data may be put
in that column.
^The UNIQUE constraint causes an unique index to be created on the specified
columns.  ^All NULL values are considered different from each other and from

the virtual table.  ^The <module-name> must be registered with
the SQLite database connection using
[sqlite3_create_module()] or [sqlite3_create_module_v2()]
prior to issuing the CREATE VIRTUAL TABLE statement.
^The module takes zero or more comma-separated arguments.
^The arguments can be just about any text as long as it has balanced
parentheses.  The argument syntax is sufficiently general that the
arguments can be made to appear as [column definitions] in a traditional
[CREATE TABLE] statement.  
^SQLite passes the module arguments directly
to the [xCreate] and [xConnect] methods of the module implementation
without any interpretation.  It is the responsibility
of the module implementation to parse and interpret its own arguments.</p>

<p>^A virtual table is destroyed using the ordinary

<p>^(Supported unary prefix operators are these:</p>

<blockquote><pre>
<font color="#2c2cf0"><big>-    +    ~    NOT</big></font>
</pre></blockquote>)^

<tcl>hd_fragment collateop {COLLATE operator}</tcl>
<p>^The COLLATE operator is a unary postfix
operator that assigns a [collating sequence] to an expression.
^The COLLATE operator has a higher precedence (binds more tightly) than any
prefix unary operator or any binary operator.
^The collating sequence set by the COLLATE operator overrides the
collating sequence determined by the COLLATE clause in a table
[column definition].
See the [collating sequence| detailed discussion on collating sequences]
in the [datatype | Datatype In SQLite3] document for additional information.
</p>

<tcl>hd_puts "
<p>^The unary operator [Operator +] is a no-op.  ^It can be applied
to strings, numbers, blobs or NULL and it always returns a result
with the same value as the operand.</p>"</tcl>

<p>Note that there are two variations of the equals and not equals

<tcl>hd_fragment castexpr {CAST expression}</tcl>
<h3>CAST expressions</h3>

<p>^A CAST expression changes the [datatype] of the <expr> into the
type specified by &lt;type&gt;. 
^(&lt;type&gt; can be any non-empty type name that is valid
for the type in a [column definition] of a [CREATE TABLE] statement.)^
^The &lt;type&gt; name is interpreted according to the 
[rules for determining column affinity].

<p>^An explicit cast is stronger than affinity; with the CAST expression
the datatype conversion is forced even if it is lossy and irrreversible.
<ul>
<li><p>

can consist of keywords, string literals, identifiers, numbers, and 
punctuation. The arguments are passed as written (as text) into the
[xCreate | constructor method] of the virtual table implementation 
when the virtual 
table is created and that constructor is responsible for parsing and 
interpreting the arguments. The argument syntax is sufficiently general 
that a virtual table implementation can, if it wants to, interpret its
arguments as [column definitions] in an ordinary [CREATE TABLE] statement. 
The implementation could also impose some other interpretation on the 
arguments.

<p>Once a virtual table has been created, it can be used like any other 
table with the exceptions noted above and imposed by specific virtual
table implementations. A virtual table is destroyed using the ordinary
[DROP TABLE] syntax.