Documentation Source Text

<title>SQLite Autoincrement</title>
<h1>SQLite Autoincrement</h1>
<tcl>hd_keywords AUTOINCREMENT</tcl>

<p>
In SQLite, every row of every table has an 64-bit signed integer [ROWID].
The ROWID for each row is unique among all rows in the same table.
</p>

<p>
You can access the ROWID of an SQLite table using one the special column
names ROWID, _ROWID_, or OID.
Except if you declare an ordinary table column to use one of those special
names, then the use of that name will refer to the declared column not
to the internal ROWID.
</p>

<p>
If a table contains a column of type [INTEGER PRIMARY KEY], then that
column becomes an alias for the ROWID.  You can then access the ROWID
using any of four different names, the original three names described above
or the name given to the [INTEGER PRIMARY KEY] column.  All these names are
aliases for one another and work equally well in any context.
</p>

<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, 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
used.  If the largest ROWID is equal to the largest possible integer
(9223372036854775807) then the database
engine starts picking candidate ROWIDs at random until it finds one
that is not previously used.
</p>

<p>
The normal ROWID selection algorithm described above
will generate monotonically increasing
unique ROWIDs as long as you never use the maximum ROWID value and you never
delete the entry in the table with the largest ROWID. 
If you ever delete rows or if you ever create a row with the maximum possible
ROWID, then ROWIDs from previously deleted rows might be reused when creating
new rows and newly created ROWIDs might not be in strictly accending order.
</p>


<h2>The AUTOINCREMENT Keyword</h2>

<p>
If a column has the type INTEGER PRIMARY KEY AUTOINCREMENT then a slightly
different ROWID selection algorithm is used.  
The ROWID chosen for the new row is at least one larger than the largest ROWID
that has ever before existed in that same table.  If the table has never
before contained any data, then a ROWID of 1 is used.  If the table
has previously held a row with the largest possible ROWID, then new INSERTs
are not allowed and any attempt to insert a new row will fail with an
SQLITE_FULL error.
</p>

<p>
SQLite keeps track of the largest ROWID that a table has ever held using
the special SQLITE_SEQUENCE table.  The SQLITE_SEQUENCE table is created
and initialized automatically whenever a normal table that contains an
AUTOINCREMENT column is created.  The content of the SQLITE_SEQUENCE table
can be modified using ordinary UPDATE, INSERT, and DELETE statements.
But making modifications to this table will likely perturb the AUTOINCREMENT
key generation algorithm.  Make sure you know what you are doing before
you undertake such changes.
</p>

<p>
The behavior implemented by the AUTOINCREMENT keyword is subtly different
from the default behavior.  With AUTOINCREMENT, rows with automatically
selected ROWIDs are guaranteed to have ROWIDs that have never been used
before by the same table in the same database.  And the automatically generated
ROWIDs are guaranteed to be monotonically increasing.  These are important
properties in certain applications.  But if your application does not
need these properties, you should probably stay with the default behavior
since the use of AUTOINCREMENT requires additional work to be done
as each row is inserted and thus causes INSERTs to run a little slower.
</p>

<p>Note that "monotonically increasing" does not imply that the ROWID always
increases by exactly one.  One is the usual increment.  However, if an
insert fails due to (for example) a uniqueness constraint, the ROWID of
the failed insertion attempt might not be reused on subsequent inserts,
resulting in gaps in the ROWID sequence.  AUTOINCREMENT guarantees that
automatically chosen ROWIDs will be increasing but not that they will be
sequential.</p>

<title>SQLite Autoincrement</title>
<h1>SQLite Autoincrement</h1>
<tcl>hd_keywords AUTOINCREMENT</tcl>

<p>
^In SQLite, every row of every table has an 64-bit signed integer [ROWID].
^The ROWID for each row is unique among all rows in the same table.
</p>

<p>
^(You can access the ROWID of an SQLite table using one the special column
names ROWID, _ROWID_, or OID.
Except if you declare an ordinary table column to use one of those special
names, then the use of that name will refer to the declared column not
to the internal ROWID.)^
</p>

<p>
^If a table contains a column of type [INTEGER PRIMARY KEY], then that
column becomes an alias for the ROWID.  ^You can then access the ROWID
using any of four different names, the original three names described above
or the name given to the [INTEGER PRIMARY KEY] column.  ^All these names are
aliases for one another and work equally well in any context.
</p>

<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, 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
used.  ^If the largest ROWID is equal to the largest possible integer
(9223372036854775807) then the database
engine starts picking candidate ROWIDs at random until it finds one
that is not previously used.
</p>

<p>
^The normal ROWID selection algorithm described above
will generate monotonically increasing
unique ROWIDs as long as you never use the maximum ROWID value and you never
delete the entry in the table with the largest ROWID. 
^If you ever delete rows or if you ever create a row with the maximum possible
ROWID, then ROWIDs from previously deleted rows might be reused when creating
new rows and newly created ROWIDs might not be in strictly accending order.
</p>


<h2>The AUTOINCREMENT Keyword</h2>

<p>
^If a column has the type INTEGER PRIMARY KEY AUTOINCREMENT then a slightly
different ROWID selection algorithm is used.  
^The ROWID chosen for the new row is at least one larger than the largest ROWID
that has ever before existed in that same table.  ^If the table has never
before contained any data, then a ROWID of 1 is used.  ^If the table
has previously held a row with the largest possible ROWID, then new INSERTs
are not allowed and any attempt to insert a new row will fail with an
SQLITE_FULL error.
</p>

<p>
^SQLite keeps track of the largest ROWID that a table has ever held using
the special SQLITE_SEQUENCE table.  ^The SQLITE_SEQUENCE table is created
and initialized automatically whenever a normal table that contains an
AUTOINCREMENT column is created.  ^The content of the SQLITE_SEQUENCE table
can be modified using ordinary UPDATE, INSERT, and DELETE statements.
^But making modifications to this table will likely perturb the AUTOINCREMENT
key generation algorithm.  Make sure you know what you are doing before
you undertake such changes.
</p>

<p>
The behavior implemented by the AUTOINCREMENT keyword is subtly different
from the default behavior.  ^With AUTOINCREMENT, rows with automatically
selected ROWIDs are guaranteed to have ROWIDs that have never been used
before by the same table in the same database.  ^And the automatically generated
ROWIDs are guaranteed to be monotonically increasing.  These are important
properties in certain applications.  But if your application does not
need these properties, you should probably stay with the default behavior
since the use of AUTOINCREMENT requires additional work to be done
as each row is inserted and thus causes INSERTs to run a little slower.
</p>

<p>^Note that "monotonically increasing" does not imply that the ROWID always
increases by exactly one.  ^One is the usual increment.  ^However, if an
insert fails due to (for example) a uniqueness constraint, the ROWID of
the failed insertion attempt might not be reused on subsequent inserts,
resulting in gaps in the ROWID sequence.  ^AUTOINCREMENT guarantees that
automatically chosen ROWIDs will be increasing but not that they will be
sequential.</p>

<title>Datatypes In SQLite Version 3</title>
<tcl>hd_keywords {datatype} {dynamic typing}</tcl>

<h2>Datatypes In SQLite Version 3</h2>

<p>Most SQL database engines (every SQL database engine other than SQLite,
as far as we know) uses static typing.  With static typing, the datatype
of a value is determined by its container - the particular column the value
is stored in.</p>

<p>SQLite uses a more general dynamic type system.  In SQLite, the datatype
of a value is associated with the value itself, not with the container in
which it is stored.  The dynamic type system of SQLite is backwards
compatible with the more common static type systems of other database engines
in the sense that SQL statement that work on statically typed databases should
would the same way in SQLite.  However, the dynamic typing in SQLite allowed
it to do things which are not possible in traditional statically typed
databases.</p>

<h3>1. Storage Classes</h3>

<P>Each value stored in an SQLite database (or manipulated by the
database engine) has one of the following storage classes:</P>
<UL>
	<LI><P><B>NULL</B>. The value is a NULL value.</P>
	<LI><P><B>INTEGER</B>. The value is a signed integer, stored in 1,
	2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.</P>
	<LI><P><B>REAL</B>. The value is a floating point value, stored as
	an 8-byte IEEE floating point number.</P>
	<LI><P><B>TEXT</B>. The value is a text string, stored using the
	database encoding (UTF-8, UTF-16BE or UTF-16-LE).</P>
	<LI><P><B>BLOB</B>. The value is a blob of data, stored exactly as
	it was input.</P>
</UL>

<P>Any column in a version 3 database, except an [INTEGER PRIMARY KEY]
column, may be used to store any type of value.</p>

<P>All values supplied to SQLite, whether as literals embedded in SQL
statements or values bound to pre-compiled SQL statements
are assigned a storage class before the SQL statement is executed.
Under circumstances described below, the
database engine may convert values between numeric storage classes
(INTEGER and REAL) and TEXT during query execution. 
</P>

<P>Storage classes are initially assigned as follows:</P>
<UL>
	<LI><P>Values specified as literals as part of SQL statements are
	assigned storage class TEXT if they are enclosed by single or double
	quotes, INTEGER if the literal is specified as an unquoted number
	with no decimal point or exponent, REAL if the literal is an
	unquoted number with a decimal point or exponent and NULL if the
	value is a NULL. Literals with storage class BLOB are specified
        using the X'ABCD' notation.</P>

	<LI><P>Values supplied using the [sqlite3_bind_int | sqlite3_bind_*]
        APIs are assigned
	the storage class that most closely matches the native type bound
	(i.e. sqlite3_bind_blob() binds a value with storage class BLOB).</P>
</UL>
<P>The storage class of a value that is the result of an SQL scalar
operator depends on the outermost operator of the expression.
User-defined functions may return values with any storage class. It
is not generally possible to determine the storage class of the
result of an expression at compile time.</P>

<tcl>hd_fragment affinity affinity</tcl>
<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%20system">
manifest typing</a> or <a href="http://en.wikipedia.org/wiki/Duck_typing">
duck typing</a>.)
All other SQL databases engines that we are aware of use the more
restrictive system of static typing where the type is associated with
the container, not the value.
To look at it another way, SQLite provides dynamic datatypes such as
one finds in "script" programming languages such as Awk, Perl, Tcl,
Python, and Ruby, whereas other SQL database engines provide only
compile-time fixed, static typing such as found in Pascal, C++, and Java.
</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>TEXT</LI>
	<LI>NUMERIC</LI>
	<LI>INTEGER</LI>
        <LI>REAL</li>
	<LI>NONE</LI>
</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
storage classes. When text data is inserted into a NUMERIC column, an
attempt is made to convert it to an integer or real number before it
is stored. If the conversion is successful (meaning that the conversion
occurs without loss of information), then the value is stored
using the INTEGER or REAL storage class. If the conversion cannot be
performed without loss of information then 


the value is stored using the TEXT storage class. No
attempt is made to convert NULL or blob values.</P>

<P>A column that uses INTEGER affinity behaves in the same way as a
column with NUMERIC affinity, except that if a real value with no
fractional component and a magnitude that is less than or equal to the
largest possible integer (or text value that converts to such) is
inserted it is converted to an integer and stored using the INTEGER
storage class.</P>




<P>A column with REAL affinity behaves like a column with NUMERIC
affinity except that it forces integer values into floating point
representation.  (As an internal optimization, small floating point
values with no fractional component are stored on
disk as integers in order to take up less space and are converted
back into floating point as the value is read out.)</P>



<P>A column with affinity NONE does not prefer one storage class over
another.  No attempt is made to coerce data from one storage class into
another.  The data is stored on disk exactly as specified.</p>


<h4>2.1 Determination Of Column Affinity</h4>

<P>The type affinity of a column is determined by the declared type
of the column, according to the following rules:</P>
<OL>


	<LI><P>If the datatype contains the string &quot;INT&quot; then it
	is assigned INTEGER affinity.</P>

	<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
        no datatype is specified then the column has affinity NONE.</P>

        <LI><P>If the datatype for a column
        contains any of the strings &quot;REAL&quot;, &quot;FLOA&quot;,
        or &quot;DOUB&quot; then the column has REAL affinity</P>

	<LI><P>Otherwise, the affinity is 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>

<blockquote>
<PRE>CREATE TABLE t1(
    t  TEXT,
    nu NUMERIC, 
    i  INTEGER,
    no BLOB
);

................................................................................
-- TEXT, REAL, INTEGER, TEXT
INSERT INTO t1 VALUES('500.0', '500.0', '500.0', '500.0');

-- Storage classes for the following row:
-- TEXT, REAL, INTEGER, REAL
INSERT INTO t1 VALUES(500.0, 500.0, 500.0, 500.0);
</PRE>
</blockquote>

<a name="comparisons"></a>
<h3>3. Comparison Expressions</h3>

<P>Like SQLite version 2, version 3
features the binary comparison operators '=',
'&lt;', '&lt;=', '&gt;=' and '!=', an operation to test for set
membership, 'IN', and the ternary comparison operator 'BETWEEN'.</P>

<P>The results of a comparison depend on the storage classes of the
two values being compared, according to the following rules:</P>
<UL>

	<LI><P>A value with storage class NULL is considered less than any
	other value (including another value with storage class NULL).</P>

	<LI><P>An INTEGER or REAL value is less than any TEXT or BLOB value.
	When an INTEGER or REAL is compared to another INTEGER or REAL, a
	numerical comparison is performed.</P>

	<LI><P>A TEXT value is less than a BLOB value. When two TEXT values
	are compared, the C library function memcmp() is usually used to
	determine the result. However this can be overridden, as described
	under 'User-defined collation Sequences' below.</P>



	<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.

Whether or not any conversions are attempted before the comparison takes
place depends on the nominal affinity assigned to the expressions on
either side of the binary operator. Affinities are assigned to expressions
in the following cases:
<UL>



	<LI><P>An expression that is a simple reference to a column value
	has the same affinity as the column it refers to. Note that if X and Y.Z 

	are column names, then +X and +Y.Z are considered expressions.</P>

	<LI><P>An expression of the form "CAST(&lt;expr&gt; TO &lt;type&gt;)"
	is assigned an affinity as if it were a reference to a column declared
	with type &lt;type&gt;
</UL>

<P>Conversions are applied before the comparison as described below.
In the following bullet points, the two operands are
refered to as expression A and expression B. Expressions A and B may
appear as either the left or right operands - the following statements
are true when considering both "A &lt;op&gt;B" and "B &lt;op&gt;A".
<UL>
	<LI><P>When two expressions are compared, if expression A has

	INTEGER or REAL or NUMERIC affinity and expression B does not, 
        then NUMERIC affinity is applied to the value of expression B
	before the comparison takes place.
	<LI><P>When two expressions are compared, if expression A has
	been assigned an affinity and expression B has not, then the
	affinity of expression A is applied to the value of expression B
	before the comparison takes place.
	<LI><P>Otherwise, if neither of the above applies, no conversions
	occur. The results are compared as is. If a string is compared to a
	number, the number will always be less than the string.
</UL>

<P>

In SQLite, the expression "a BETWEEN b AND c" is equivalent to "a &gt;= b
AND a &lt;= c", even if this means that different affinities are applied to
'a' in each of the comparisons required to evaluate the expression.
</P>

<P>Expressions of the type "a IN (SELECT b ....)" are handled by the three
rules enumerated above for binary comparisons (e.g. in a
similar manner to "a = b"). For example if 'b' is a column value


and 'a' is an expression, then the affinity of 'b' is applied to 'a'
before any comparisons take place.</P>

<P>SQLite treats the expression "a IN (x, y, z)" as equivalent to "a = +x OR
a = +y OR a = +z".  The values to the right of the IN operator (the "x", "y",
and "z" values in this example) are considered to be expressions, even if they
happen to be column values.  If the value of the left of the IN operator is
a column, then the affinity of that column is used.  If the value is an
expression then no conversions occur.
</P>

<h4>3.1 Comparison Example</h4>

<blockquote>
<PRE>
CREATE TABLE t1(
    a TEXT,
    b NUMERIC,
    c BLOB
);

................................................................................
0|1

-- Both 60 and 600 (storage class NUMERIC) are less than '500'
-- (storage class TEXT).
SELECT c &lt; 60, c &lt; 600 FROM t1;
0|0
</PRE>
</blockquote>
<h3>4. Operators</h3>

<P>All mathematical operators (which is to say, all operators other
than the concatenation operator &quot;||&quot;) apply NUMERIC
affinity to all operands prior to being carried out. If one or both
operands cannot be converted to NUMERIC then the result of the
operation is NULL.</P>

<P>For the concatenation operator, TEXT affinity is applied to both
operands. If either operand cannot be converted to TEXT (because it
is NULL or a BLOB) then the result of the concatenation is NULL.</P>

<h3>5. Sorting, Grouping and Compound SELECTs</h3>

<P>When values are sorted by an ORDER by clause, values with storage
class NULL come first, followed by INTEGER and REAL values
interspersed in numeric order, followed by TEXT values usually in
memcmp() order, and finally BLOB values in memcmp() order. No storage
class conversions occur before the sort.</P>

<P>When grouping values with the GROUP BY clause values with
different storage classes are considered distinct, except for INTEGER
and REAL values which are considered equal if they are numerically
equal. No affinities are applied to any values as the result of a
GROUP by clause.</P>

<P>The compound SELECT operators UNION,
INTERSECT and EXCEPT perform implicit comparisons between values.
Before these comparisons are performed an affinity may be applied to
each value. The same affinity, if any, is applied to all values that
may be returned in a single column of the compound SELECT result set.
The affinity applied is the affinity of the column returned by the
left most component SELECTs that has a column value (and not some
other kind of expression) in that position. If for a given compound
SELECT column none of the component SELECTs return a column value, no
affinity is applied to the values from that column before they are
compared.</P>

<h3>6. Other Affinity Modes</h3>

<P>The above sections describe the operation of the database engine
in 'normal' affinity mode. SQLite version 3 will feature two other affinity
modes, as follows:</P>
<UL>
	<LI><P><B>Strict affinity</B> mode. In this mode if a conversion
	between storage classes is ever required, the database engine
	returns an error and the current statement is rolled back.</P>

	<LI><P><B>No affinity</B> mode. In this mode no conversions between
	storage classes are ever performed. Comparisons between values of
	different storage classes (except for INTEGER and REAL) are always
	false.</P>
</UL>

<tcl>hd_fragment collation {*collating sequence} \
                 {collating function} *collation *BINARY *NOCASE *RTRIM \
        {BINARY collating function} \
        {NOCASE collating function} \
        {RTRIM collating function}</tcl>
<h3>7. User-defined Collation Sequences</h3>

<p>
By default, when SQLite compares two text values, the result of the
comparison is determined using memcmp(), regardless of the encoding of the
string. SQLite v3 provides the ability for users to supply arbitrary
comparison functions, known as user-defined "collation sequences" or
"collating functions", to be used instead of memcmp().
</p>  
<p>
Aside from the default collation sequence BINARY, implemented using
memcmp(), SQLite features two extra built-in collation sequences 
intended for testing purposes, the NOCASE and RTRIM collations:
</p>  
<UL>
<LI><b>BINARY</b> - Compares string data using memcmp(), regardless
                    of text encoding.</LI>
<LI><b>NOCASE</b> - The same as binary, except the 26 upper case
     characters of ASCII are
     folded to their lower case equivalents before
     the comparison is performed.  Note that only ASCII characters
     are case folded.  SQLite does not attempt to do full
     UTF case folding due to the size of the tables required.</li>

<LI><b>RTRIM</b> - The same as binary, except that trailing space
     characters are ignored.</li>
</ul>



<h4>7.1 Assigning Collation Sequences from SQL</h4>

<p>
Each column of each table has a default collation type. If a collation type
other than BINARY is required, a COLLATE clause is specified as part of the
<a href="lang_createtable.html">column definition</a> to define it. 
</p>  

<p>
Whenever two text values are compared by SQLite, a collation sequence is
used to determine the results of the comparison according to the following
rules. Sections 3 and 5 of this document describe the circumstances under
which such a comparison takes place.
</p>  

<p>
For binary comparison operators (=, &lt;, &gt;, &lt;= and &gt;=) if either
operand is a column, then the default collation type of the column determines
the collation sequence to use for the comparison. If both operands are
columns, then the collation type for the left operand determines the collation
sequence used. If neither operand is a column, then the BINARY collation
sequence is used.  For the purposes of this paragraph, a column name
preceded by one or more unary "+" operators is considered a column name.
</p>  

<p>
The expression "x BETWEEN y and z" is equivalent to "x &gt;= y AND x &lt;=
z". The expression "x IN (SELECT y ...)" is handled in the same way as the
expression "x = y" for the purposes of determining the collation sequence
to use. The collation sequence used for expressions of the form "x IN (y, z
...)" is the default collation type of x if x is a column, or BINARY
otherwise.
</p>  

<p>
An <a href="lang_select.html">ORDER BY</a> clause that is part of a SELECT
statement may be assigned a collation sequence to be used for the sort
operation explicitly. In this case the explicit collation sequence is
always used.  Otherwise, if the expression sorted by an ORDER BY clause is
a column, then the default collation type of the column is used to
determine sort order. If the expression is not a column, then the BINARY
collation sequence is used.
</p>  

<h4>7.2 Collation Sequences Example</h4>
<p>
The examples below identify the collation sequences that would be used to
determine the results of text comparisons that may be performed by various
SQL statements. Note that a text comparison may not be required, and no
collation sequence used, in the case of numeric, blob or NULL values.
</p>
<blockquote>
<PRE>
CREATE TABLE t1(
    a,                 -- default collation type BINARY
    b COLLATE BINARY,  -- default collation type BINARY
    c COLLATE REVERSE, -- default collation type REVERSE
    d COLLATE NOCASE   -- default collation type NOCASE
);

-- Text comparison is performed using the BINARY collation sequence.
SELECT (a = b) FROM t1;

-- Text comparison is performed using the NOCASE collation sequence.
SELECT (d = a) FROM t1;

-- Text comparison is performed using the BINARY collation sequence.
SELECT (a = d) FROM t1;

-- Text comparison is performed using the REVERSE collation sequence.
SELECT ('abc' = c) FROM t1;

-- Text comparison is performed using the REVERSE collation sequence.
SELECT (c = 'abc') FROM t1;

-- Grouping is performed using the NOCASE collation sequence (i.e. values




-- 'abc' and 'ABC' are placed in the same group).
SELECT count(*) GROUP BY d FROM t1;

-- Grouping is performed using the BINARY collation sequence.
SELECT count(*) GROUP BY (d || '') FROM t1;

-- Sorting is performed using the REVERSE collation sequence.
SELECT * FROM t1 ORDER BY c;

-- Sorting is performed using the BINARY collation sequence.
SELECT * FROM t1 ORDER BY (c || '');

-- Sorting is performed using the NOCASE collation sequence.
SELECT * FROM t1 ORDER BY c COLLATE NOCASE;

</PRE>
</blockquote>

<title>Datatypes In SQLite Version 3</title>
<tcl>hd_keywords {datatype} {dynamic typing}</tcl>

<h1 align=center>Datatypes In SQLite Version 3</h1>

<p>Most SQL database engines (every SQL database engine other than SQLite,
as far as we know) uses static, rigid typing.  With static typing, the datatype
of a value is determined by its container - the particular column in
which the value is stored.</p>

<p>SQLite uses a more general dynamic type system.  In SQLite, the datatype
of a value is associated with the value itself, not with its container.
The dynamic type system of SQLite is backwards
compatible with the more common static type systems of other database engines
in the sense that SQL statement that work on statically typed databases should
would the same way in SQLite.  However, the dynamic typing in SQLite allowed
it to do things which are not possible in traditional rigidly typed
databases.</p>

<h2>1.0 Storage Classes</h2>

<p>Each value stored in an SQLite database (or manipulated by the
database engine) has one of the following storage classes:</p>
<ul>
  <li><p><B>NULL</B>.
  The value is a NULL value.</p>

  <li><p><B>INTEGER</B>. The value is a signed integer, stored in 1,
  2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.</p>

  <li><p><B>REAL</B>. The value is a floating point value, stored as
  an 8-byte IEEE floating point number.</p>

  <li><p><B>TEXT</B>. The value is a text string, stored using the
  database encoding (UTF-8, UTF-16BE or UTF-16-LE).</p>

  <li><p><B>BLOB</B>. The value is a blob of data, stored exactly as
  it was input.</p>
</ul>

<p>Note that a storage class is slightly more general than a datatype.
The INTEGER storage class, for example, includes 6 different integer
datatypes of different lengths.  This makes a different on-disk.  But
as soon as INTEGER values are read off of disk and into memory for processing,
they are converted to the most general datatype (8-byte signed integer).
And so for the most part, "storage class" is indistinguishable from 
"datatype" and the two terms can be used interchangably.</p>

<p>^Any column in an SQLite version 3 database,
except an [INTEGER PRIMARY KEY] column, may be used to store a value 
of any storage class.</p>

<p>All values in SQL statements, whether as literals embedded in SQL
statement text or [parameters] bound to 
[prepared statements | pre-compiled SQL statements]
have an implicit storage class.
Under circumstances described below, the
database engine may convert values between numeric storage classes
(INTEGER and REAL) and TEXT during query execution. 
</p>

<h3>1.1 Boolean Datatype</h3>

<p>SQLite does not have a separate Boolean storage class.
^Instead, Boolean values are stored as integers 0 (false) and 1 (true).</p>

<h3>1.2 Date and Time Datatype</h3>

<p>SQLite does not have any storage class set aside for storing
dates and/or times.
^(Instead, the built-in [Date And Time Functions] of SQLite are capable of 
storing dates and times as TEXT, REAL, or INTEGER values:)^</p>

<ul>
<li><b>TEXT</b> as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
<li><b>REAL</b> as Julian day numbers, the number of days since
noon in Greenwich on November 24, 4714 B.C. according to the
proleptic Gregorian calendar.
<li><b>INTEGER</b> as Unix Time, the number of seconds since
1970-01-01 00:00:00 UTC.
</ul>

<p>Applications can chose to store dates and times in any of these
formats and freely convert between formats using the built-in date
and time functions.</p>


<tcl>hd_fragment affinity affinity</tcl>
<h2>2.0 Column Affinity</h2>

<p>
In order to maximize compatibility between SQLite and other database
engines, SQLite supports 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>TEXT</LI>
	<li>NUMERIC</LI>
	<li>INTEGER</LI>
        <li>REAL</li>
	<li>NONE</LI>
</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 into text form before being stored.</p>

<p>^A column with NUMERIC affinity may contain values using all five
storage classes.  ^When text data is inserted into a NUMERIC column, the
storage class of the text is converted to INTEGER or REAL if that conversion
is reversible - if the converversion does not lose information by truncating
the value.
^For conversions between TEXT and REAL storage classes, SQLite considers
the conversion to be lossless and reversible if the first 15 significant
digits of the number are preserved.
^If the lossless conversion of TEXT to INTEGER or REAL is not possible then
the value is stored using the TEXT storage class. ^No
attempt is made to convert NULL or BLOB values.</p>

<p>^A column that uses INTEGER affinity behaves in the same way as a
column with NUMERIC affinity, except that if a real value with no
fractional component and a magnitude that is less than or equal to the
largest possible integer (or text value that converts to such) is
inserted it is converted to an integer and stored using the INTEGER

storage class.  ^Note that a TEXT value inserted into a column with
INTEGER affinity will be stored as REAL if there is a lossless conversion
to REAL but not a lossless conversion to INTEGER.</p>

<p>^A column with REAL affinity behaves like a column with NUMERIC
affinity except that it forces integer values into floating point
representation.  (^As an internal optimization, small floating point
values with no fractional component are stored on
disk as integers in order to take up less space and are 
automatically converted back into floating point as the value is read out.
^This optimization is completely invisible at the SQL level and can only
be detected by examining the raw bits of the database file.)</p>

<p>^A column with affinity NONE does not prefer one storage class over
another and no attempt is made to coerce data from one storage class into

another.</p>

<h3>2.1 Determination Of Column Affinity</h3>

<p>^(The affinity of a column is determined by the declared type
of the column, according to the following rules in the order shown:)^</p>


<ol>
  <li><p>^If the datatype contains the string "INT" then it
  is assigned INTEGER affinity.</p>

  <li><p>^If the datatype of the column contains any of the strings

  "CHAR", "CLOB", or "TEXT" then that
  column has TEXT affinity.  ^Notice that the type VARCHAR contains the
  string "CHAR" and is thus assigned TEXT affinity.</p>

  <li><p>^If the datatype for a column
  contains the string "BLOB" or if
  no datatype is specified then the column has affinity NONE.</p>

  <li><p>^If the datatype for a column
  contains any of the strings "REAL", "FLOA",
  or "DOUB" then the column has REAL affinity.</p>

  <li><p>^Otherwise, the affinity is NUMERIC.</p>
</ol>




<p>^Note that the order of the rules for determining column affinity
is important.  ^A column whose declared datatype is "CHARINT" will match
both rules 1 and 2 but the first rule takes precedence and so the 
column affinity will be INTEGER.</p>

<h3>2.2 Column Affinity Example</h3>

^(<blockquote>
<PRE>CREATE TABLE t1(
    t  TEXT,
    nu NUMERIC, 
    i  INTEGER,
    no BLOB
);

................................................................................
-- TEXT, REAL, INTEGER, TEXT
INSERT INTO t1 VALUES('500.0', '500.0', '500.0', '500.0');

-- Storage classes for the following row:
-- TEXT, REAL, INTEGER, REAL
INSERT INTO t1 VALUES(500.0, 500.0, 500.0, 500.0);
</PRE>
</blockquote>)^

<a name="comparisons"></a>
<h2>3.0 Comparison Expressions</h2>


<p>SQLite version 3 has the usual set of SQL comparison operators


including "=", "&lt;", "&lt;=", "&gt;=", "!=", "IN", "BETWEEN", and "IS".
<p>^(The results of a comparison depend on the storage classes of the
operands, according to the following rules:)^</p>

<ul>
  <li><p>^A value with storage class NULL is considered less than any
  other value (including another value with storage class NULL).</p>

  <li><p>^An INTEGER or REAL value is less than any TEXT or BLOB value.
  ^When an INTEGER or REAL is compared to another INTEGER or REAL, a
  numerical comparison is performed.</p>

  <li><p>^A TEXT value is less than a BLOB value.  ^When two TEXT values



  are compared an appropriate collating sequence is used to determine 
  the result.  </p>

  <li><p>^When two BLOB values are compared, the result is
  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.

^Whether or not any conversions are attempted before the comparison takes
place depends on the affinity of the operands.



^(Affinity is assigned to expression operands by the the following rules:)^

<ul>
  <li><p>^An expression that is a simple reference to a column value
  has the same affinity as the column.
  ^(Note that if X and Y.Z 
  are column names, then +X and +Y.Z are considered expressions.)^</p>

  <li><p>^(An expression of the form "CAST(&lt;expr&gt; TO &lt;type&gt;)"
  is assigned an affinity as if it were a reference to a column declared
  with type &lt;type&gt;)^
</ul>








<p>^In a binary comparison, if either operand has INTEGER or REAL
or NUMERIC affinity and the other operand does not, then NUMERIC

affinity is applied to the other operand before the comparison takes










place.
^(The expression "a BETWEEN b AND c" is treated as two separate
binary comparisons "a &gt;= b AND a &lt;= c", even if that means
different affinities are applied to 'a' in each of the comparisons.)^


^(Expressions of the form "a IN (SELECT b ....)" are handled is if


the expression where really "a=b".)^
^For example if "b" is a column value
and "a" is an expression, then the affinity of "b" is applied to "a"
before any comparisons take place.

^(The expression "a IN (x, y, z)" as equivalent to "a = +x OR
a = +y OR a = +z".)^  ^The values to the right of the IN operator (the "x", "y",
and "z" values in this example) are considered to be expressions, even if they
happen to be column values.  ^If the value to the left of the IN operator is
a column, then the affinity of that column is used.  ^If the left operand
of IN is an expression then no storage class conversions occur.
</p>

<h3>3.1 Comparison Example</h3>

^(<blockquote>
<PRE>
CREATE TABLE t1(
    a TEXT,
    b NUMERIC,
    c BLOB
);

................................................................................
0|1

-- Both 60 and 600 (storage class NUMERIC) are less than '500'
-- (storage class TEXT).
SELECT c &lt; 60, c &lt; 600 FROM t1;
0|0
</PRE>
</blockquote>)^
<h2>4.0 Operators</h2>

<p>^(All mathematical operators (which is to say, all operators other
than the concatenation operator "||", LIKE, GLOB, MATCH, and REGEXP) 
apply NUMERIC affinity to both operands prior to being carried out.)^ 
^If one or both operands cannot be converted to NUMERIC then the result of the
mathematical operation is NULL.</p>





<h2>5.0 Sorting, Grouping and Compound SELECTs</h2>

<p>^When query results are sorted by an ORDER BY clause, values with storage
class NULL come first, followed by INTEGER and REAL values
interspersed in numeric order, followed by TEXT values in collating
sequence order, and finally BLOB values in memcmp() order.  ^No storage
class conversions occur before the sort.</p>

<p>^When grouping values with the GROUP BY clause values with
different storage classes are considered distinct, except for INTEGER
and REAL values which are considered equal if they are numerically
equal. ^No affinities are applied to any values as the result of a
GROUP by clause.</p>

<p>^The compound SELECT operators UNION,
INTERSECT and EXCEPT perform implicit comparisons between values.
^Before these comparisons are performed an affinity may be applied to
each value. ^The same affinity, if any, is applied to all values that
may be returned in a single column of the compound SELECT result set.
^The affinity applied is the affinity of the column returned by the
left most component SELECTs that has a column value (and not some
other kind of expression) in that position. ^If for a given compound
SELECT column none of the component SELECTs return a column value, no
affinity is applied to the values from that column before they are
compared.</p>

















<tcl>hd_fragment collation {*collating sequence} \
                 {collating function} *collation *BINARY *NOCASE *RTRIM \
        {BINARY collating function} \
        {NOCASE collating function} \
        {RTRIM collating function}</tcl>
<h2>6.0 Collating Sequences</h2>

<p>^When SQLite compares two strings, it uses a collating sequence or
collating function (two words for the same thing) to determine which
string is greater or if the two strings are equal.
^SQLite has three built-in collating functions:  BINARY, NOCASE, and 
RTRIM.</p>

<ul>
<li>^(<b>BINARY</b> - Compares string data using memcmp(), regardless
                   of text encoding.</li>)^
<li>^(<b>NOCASE</b> - The same as binary, except the 26 upper case
     characters of ASCII are folded to their lower case equivalents before
     the comparison is performed.)^  Note that only ASCII characters
     are case folded.  SQLite does not attempt to do full
     UTF case folding due to the size of the tables required.</li>

<li>^(<b>RTRIM</b> - The same as binary, except that trailing space
     characters are ignored.)^</li>
</ul>

<p>An application can register additional collating functions using
the [sqlite3_create_collation()] interface.</p>

<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-def | 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:)^

<ol>
<li><p>^If either operand has an explicit collating function assignment
using the postfix [COLLATE operator], then the explicit collating function
is used for comparison, with precedence to the collating function of the
left operand.</p></li>

<li><p>^If either operand is a column, then the collating function of
that column is used with precedence to the left operand.
^For the purposes of the previous sentence, a column name
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>
^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 
"x IN (y, z, ...)" is the collating sequence of x.)^
</p>  

<p>
^An ORDER BY clause that is part of a [SELECT]
statement may be assigned a collating sequence to be used for the sort
operation explicitly. ^In this case the explicit collating sequence is
always used.  ^Otherwise, if the expression sorted by an ORDER BY clause is
a column, then the collating sequence of the column is used to
determine sort order. ^If the expression is not a column, then the BINARY
collating sequence is used.
</p>  

<h3>6.2 Collation Sequence Examples</h3>
<p>
The examples below identify the collating sequences that would be used to
determine the results of text comparisons that may be performed by various
SQL statements. Note that a text comparison may not be required, and no
collating sequence used, in the case of numeric, blob or NULL values.
</p>
^(<blockquote>
<PRE>
CREATE TABLE t1(
    a,                 -- collating sequence BINARY
    b COLLATE BINARY,  -- collating sequence BINARY
    c COLLATE RTRIM,   -- collating sequence RTRIM
    d COLLATE NOCASE   -- collating sequence NOCASE
);

-- Text comparison is performed using the BINARY collating sequence.
SELECT a = b FROM t1;

-- Text comparison is performed using the RTRIM collating sequence.
SELECT a = b COLLATE RTRIM FROM t1;

-- Text comparison is performed using the NOCASE collating sequence.
SELECT d = a FROM t1;

-- Text comparison is performed using the BINARY collating sequence.
SELECT a = d FROM t1;

-- Text comparison is performed using the RTRIM collating sequence.
SELECT 'abc' = c FROM t1;

-- Text comparison is performed using the RTRIM collating sequence.
SELECT c = 'abc' FROM t1;

-- Grouping is performed using the NOCASE collating sequence (i.e. values
-- 'abc' and 'ABC' are placed in the same group).
SELECT count(*) GROUP BY d FROM t1;

-- Grouping is performed using the BINARY collating sequence.
SELECT count(*) GROUP BY (d || '') FROM t1;

-- Sorting is performed using the RTRIM collating sequence.
SELECT * FROM t1 ORDER BY c;

-- Sorting is performed using the BINARY collating sequence.
SELECT * FROM t1 ORDER BY (c || '');

-- Sorting is performed using the NOCASE collating sequence.
SELECT * FROM t1 ORDER BY c COLLATE NOCASE;

</PRE>
</blockquote>)^

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

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


<p>^The COLLATE operator can be thought of as a unary postfix
operator.  ^The COLLATE operator has the highest precedence.
It always binds more tightly than any prefix unary operator or
any binary operator.</p>

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

<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 can be thought of as a unary postfix
operator.  ^The COLLATE operator has the highest precedence.
^It always binds more tightly than any prefix unary operator or
any binary operator.</p>

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