Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Default type affinity is now NUMERIC. The affinity.html file checked into the doc directory. (CVS 1417) |
|---|---|
| Downloads: | Tarball | ZIP archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
948307f07d6f8cc1cc186167ff7aaa5d |
| User & Date: | drh 2004-05-20 12:10:20.000 |
Context
|
2004-05-20
| ||
| 12:41 | Fix problems with types and the recognition of BLOB as having no affinity. (CVS 1418) (check-in: 8411718f0a user: drh tags: trunk) | |
| 12:10 | Default type affinity is now NUMERIC. The affinity.html file checked into the doc directory. (CVS 1417) (check-in: 948307f07d user: drh tags: trunk) | |
| 11:00 | Add some more elements of the new API. (CVS 1416) (check-in: 2821767b94 user: danielk1977 tags: trunk) | |
Changes
Added doc/affinity.html.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 |
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="CONTENT-TYPE" CONTENT="text/html; charset=utf-8">
<TITLE></TITLE>
<META NAME="GENERATOR" CONTENT="OpenOffice.org 1.0.2 (Linux)">
<META NAME="CREATED" CONTENT="20040515;10253700">
<META NAME="CHANGED" CONTENT="20040517;11521700">
<STYLE>
<!--
@page { size: 21.59cm 27.94cm; margin-left: 3.18cm; margin-right: 3.18cm; margin-top: 2.54cm; margin-bottom: 2.54cm }
H1 { margin-bottom: 0.21cm }
H1.western { font-family: "Luxi Sans", sans-serif; font-size: 16pt }
H1.cjk { font-size: 16pt }
H1.ctl { font-size: 16pt }
P { margin-bottom: 0.21cm }
H2 { margin-bottom: 0.21cm }
H2.western { font-family: "Luxi Sans", sans-serif; font-size: 14pt; font-style: normal }
H2.cjk { font-size: 14pt; font-style: italic }
H2.ctl { font-size: 14pt; font-style: italic }
-->
</STYLE>
</HEAD>
<BODY LANG="en-US">
<H1 CLASS="western" ALIGN=CENTER>SQLite v3 Value Storage and
Collation</H1>
<P>This document is a collection of notes describing the proposed
SQLite v3 type affinity and collation sequence features.</P>
<H2 CLASS="western">1. Storage Classes</H2>
<P>Version 2 of SQLite stores all column values as ASCII text.
Version 3 enhances this by providing the ability to store integer and
real numbers in a more compact format and the capability to store
BLOB data.</P>
<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, 4 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>As in SQLite v2, normally any SQLite v3 column except an INTEGER
PRIMARY KEY may be used to store any type of value. The exception to
this rule is described below under 'Strict Affinity Mode'.</P>
<P>All values supplied to SQLite, whether as literals embedded in SQL
statements, values bound to pre-compiled SQL statements or data read
using the COPY command 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 read using the COPY command are assigned the storage
class TEXT or NULL.</P>
<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.</P>
<LI><P>Values supplied using the 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>
<H2 CLASS="western">2. Column Affinity</H2>
<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>The affinity of a column determines the storage class used by
values inserted into the column.</P>
<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, then the value is stored
using the INTEGER or REAL storage class. If the conversion cannot be
performed 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
floating point component (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 affinity NONE makes no attempt to coerce data before
it is inserted.</P>
<H3>2.1 Determination Of Column Affinity</H3>
<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 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 contains the string "INT" then it
is assigned INTEGER affinity.</P>
<LI><P>If the datatype contains the string "BLOB" is is
given an affinity of NONE.</P>
<LI><P>Any column that does not matches the rules above, including
columns that have no datatype specified, are given NUMERIC affinity.</P>
</OL>
<P>If a table is created using a “CREATE TABLE <table> AS
SELECT...” statement, then all columns have no datatype specified
and they are given no affinity.</P>
<H3>2.2 Column Affinity Example</H3>
<PRE>CREATE TABLE t1(
t AFFINITY TEXT,
nu AFFINITY NUMERIC,
i AFFINITY INTEGER,
no AFFINITY NONE
);
-- Storage classes for the following row:
-- 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><H2 CLASS="western">
3. Comparison Expressions</H2>
<P>Like SQLite v2, v3 features the binary comparison operators '=',
'<', '<=', '>=' 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 overriden, 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.
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
INTEGER or NUMERIC affinity and the other does not, the NUMERIC
affinity is applied to any values with storage class TEXT extracted
from the non-NUMERIC column.</P>
<LI><P>When the results of two expressions are compared, the NUMERIC
affinity is applied to both values before the comparison takes
place.</P>
</UL>
<H3>3.1 Comparison Example</H3>
<PRE>CREATE TABLE t1(
a AFFINITY TEXT,
b AFFINITY NUMERIC,
c AFFINITY NONE
);
-- Storage classes for the following row:
-- TEXT, REAL, TEXT
INSERT INTO t1 VALUES('500', '500', '500');
-- 60 and 40 are converted to “60” and “40” and values are compared as TEXT.
SELECT a < 60, a < 40 FROM t1;
1|0
-- Comparisons are numeric. No conversions are required.
SELECT b < 60, b < 600 FROM t1;
0|1
-- Both 60 and 600 (storage class NUMERIC) are less than '500' (storage class TEXT).
SELECT c < 60, c < 600 FROM t1;
0|0</PRE><P>
In SQLite, the expression “a BETWEEN b AND c” is currently
equivalent to “a >= b AND a <= c”. SQLite will continue to
treat the two as exactly equivalent, 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 currently treats the expression “a IN (x, y, z)” as
equivalent to “a = z OR a = y OR a = z”. SQLite will continue to
treat the two as exactly equivalent, even if this means that
different affinities are applied to 'a' in each of the comparisons
required to evaluate the expression.</P>
<H2 CLASS="western">4. Operators</H2>
<P>All mathematical operators (which is to say, all operators other
than the concatenation operator "||") 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>
<H2 CLASS="western">5. Sorting, Grouping and Compound SELECTs</H2>
<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 STYLE="font-style: normal">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>
<H2 CLASS="western">6. Other Affinity Modes</H2>
<P>The above sections describe the operation of the database engine
in 'normal' affinity mode. SQLite v3 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>
<H2 CLASS="western">7. User-defined Collation Sequences</H2>
<P STYLE="font-style: normal">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, to be used
instead of memcmp().</P>
<P STYLE="font-style: normal"><BR><BR>
</P>
</BODY>
</HTML>
|
Changes to src/build.c.
| ︙ | ︙ | |||
19 20 21 22 23 24 25 | ** DROP INDEX ** creating ID lists ** BEGIN TRANSACTION ** COMMIT ** ROLLBACK ** PRAGMA ** | | | 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | ** DROP INDEX ** creating ID lists ** BEGIN TRANSACTION ** COMMIT ** ROLLBACK ** PRAGMA ** ** $Id: build.c,v 1.188 2004/05/20 12:10:20 drh Exp $ */ #include "sqliteInt.h" #include <ctype.h> /* ** This routine is called when a new SQL statement is beginning to ** be parsed. Check to see if the schema for the database needs |
| ︙ | ︙ | |||
763 764 765 766 767 768 769 | i = p->nCol-1; /* FIX ME */ /* if( i>=0 ) p->aCol[i].sortOrder = collType; */ } /* | | < | | | | < < < < | > < | > > > > > > > | < | | 763 764 765 766 767 768 769 770 771 772 773 774 775 776 777 778 779 780 781 782 783 784 785 786 787 788 789 790 791 792 793 794 795 796 797 798 799 800 801 802 803 804 805 806 807 |
i = p->nCol-1;
/* FIX ME */
/* if( i>=0 ) p->aCol[i].sortOrder = collType; */
}
/*
** Scan the column type name zType (length nType) and return the
** associated affinity type.
*/
char sqlite3AffinityType(const char *zType, int nType){
int n, i;
struct {
const char *zSub; /* Keywords substring to search for */
int nSub; /* length of zSub */
char affinity; /* Affinity to return if it matches */
} substrings[] = {
{"INT", 3, SQLITE_AFF_INTEGER},
{"CHAR", 4, SQLITE_AFF_TEXT},
{"CLOB", 4, SQLITE_AFF_TEXT},
{"TEXT", 4, SQLITE_AFF_TEXT},
{"BLOB", 4, SQLITE_AFF_NONE},
};
for(i=0; i<sizeof(substrings)/sizeof(substrings[0]); i++){
int c1 = substrings[i].zSub[0];
int c2 = tolower(c1);
int limit = nType - substrings[i].nSub;
const char *z = substrings[i].zSub;
for(n=0; n<=limit; n++){
int c = zType[n];
if( (c==c1 || c==c2)
&& 0==sqlite3StrNICmp(&zType[n], z, substrings[i].nSub) ){
return substrings[i].affinity;
}
}
}
return SQLITE_AFF_NUMERIC;
}
/*
** Come up with a new random value for the schema cookie. Make sure
** the new value is different from the old.
**
** The schema cookie is used to determine when the schema for the
|
| ︙ | ︙ | |||
2163 2164 2165 2166 2167 2168 2169 |
if( db->flags & SQLITE_InTrans ){
/* A BEGIN has executed. Do not commit until we see an explicit
** COMMIT statement. */
}else{
sqlite3VdbeAddOp(v, OP_Commit, 0, 0);
}
}
| < < < | 2164 2165 2166 2167 2168 2169 2170 |
if( db->flags & SQLITE_InTrans ){
/* A BEGIN has executed. Do not commit until we see an explicit
** COMMIT statement. */
}else{
sqlite3VdbeAddOp(v, OP_Commit, 0, 0);
}
}
|