SQLite

set rcsid {$Id: capi3.tcl,v 1.2 2004/05/31 18:51:59 drh Exp $}
source common.tcl
header {C/C++ Interface For SQLite Version 3}
puts {
<h2>C/C++ Interface For SQLite Version 3</h2>

<h3>1.0 Overview</h3>

</pre></blockquote>

<p>
The sqlite3_create_collation() function specifies a collating sequence name
and a comparison function to implement that collating sequence.  The
comparison function is only used for comparing text values.  The eTextRep
parameter is one of SQLITE3_UTF8, SQLITE3_UTF16LE, SQLITE3_UTF16BE, or
SQLITE3_ANY to specify which text representation to comparison function works
with.  Separate comparison functions can exist for the same collating
sequence for each of the UTF-8, UTF-16LE and UTF-16BE text representations.
The sqlite3_create_collation16() works like sqlite3_create_collation() except
that the collation name is specified in UTF-16 host byte order instead of
in UTF-8.
</p>

set rcsid {$Id: datatype3.tcl,v 1.2 2004/05/31 18:51:59 drh Exp $}
source common.tcl
header {Datatypes In SQLite Version 3}
puts {
<h2>Datatypes In SQLite Version 3</h2>

<h3>1. Storage Classes</h3>

<h3>2. Column Affinity</h3>

<p>
In SQLite version 3, the type of a value is associated with the value
itself, not with the column or variable in which the value is stored.
(This is sometimes called
<a href="http://www.cliki.net/manifest%20type%50system">
manifest typing</a>.)
All other SQL databases engines that we are aware of use the more
restrict system of static typing where the type is associated with
the container, not the value.
</p>

<p>
In order to maximize compatibility between SQLite and other database
engines, SQLite support the concept of "type affinity" on columns.
The type affinity of a column is the recommended type for data stored
in that column.  The key here is that the type is recommended, not
required.  Any column can still store any type of data, in theory.
It is just that some columns, given the choice, will prefer to use
one storage class over another.  The preferred storage class for
a column is called its "affinity".
</p>

<P>Each column in an SQLite 3 database is assigned one of the
following type affinities:</P>
<UL>
	<LI><P>TEXT.</P>
	<LI><P>NUMERIC.</P>
	<LI><P>INTEGER.</P>
	<LI><P>NONE.</P>
</UL>

<P>A column with TEXT affinity stores all data using storage classes
NULL, TEXT or BLOB. If numerical data is inserted into a column with
TEXT affinity it is converted to text form before being stored.</P>

<P>A column with NUMERIC affinity may contain values using all five

	<LI><P>If the datatype of the column contains any of the strings
	&quot;CHAR&quot;, &quot;CLOB&quot;, or &quot;TEXT&quot; then that
	column has TEXT affinity. Notice that the type VARCHAR contains the
	string &quot;CHAR&quot; and is thus assigned TEXT affinity.</P>

	<LI><P>If the datatype for a column
         contains the string &quot;BLOB&quot; or if
        not datatype is specified then the column has affinity NONE.</P>

	<LI><P>Otherwise, the affinity is NUMERIC.  Notice that a column
        where no datatype is specified is given affinity NUMERIC.</P>
</OL>

<P>If a table is created using a "CREATE TABLE &lt;table&gt; AS
SELECT..." statement, then all columns have no datatype specified
and they are given no affinity.</P>

<h4>2.2 Column Affinity Example</h4>

	<LI><P>When two BLOB values are compared, the result is always
	determined using memcmp().</P>
</UL>

<P>SQLite may attempt to convert values between the numeric storage
classes (INTEGER and REAL) and TEXT before performing a comparison.
For binary comparisons, this is done in the cases enumerated below.
The term “expression” used in the bullet points below means any
SQL scalar expression or literal other than a column value.</P>
<UL>
	<LI><P>When a column value is compared to the result of an
	expression, the affinity of the column is applied to the result of
	the expression before the comparison takes place.</P>

	<LI><P>When two column values are compared, if one column has