diff options
| -rw-r--r-- | manifest | 14 | ||||
| -rw-r--r-- | manifest.uuid | 2 | ||||
| -rw-r--r-- | www/capi3.tcl | 364 | ||||
| -rw-r--r-- | www/datatype3.tcl | 308 | ||||
| -rw-r--r-- | www/docs.tcl | 12 |
5 files changed, 692 insertions, 8 deletions
@@ -1,5 +1,5 @@ -C Get\sthe\sattach.test\sworking\son\swindows.\s(CVS\s1508) -D 2004-05-31T18:21:55 +C Begin\sadding\sdocumentation\sfor\sversion\s3.0.\s(CVS\s1509) +D 2004-05-31T18:22:26 F Makefile.in ab7b0d5118e2da97bac66be8684a1034e3500f5a F Makefile.linux-gcc b86a99c493a5bfb402d1d9178dcdc4bd4b32f906 F README f1de682fbbd94899d50aca13d387d1b3fd3be2dd @@ -184,14 +184,16 @@ F www/arch2.fig 613b5ac63511109064c2f93c5754ee662219937d F www/arch2.gif 49c6bb36160f03ca2b89eaa5bfb1f560c7d68ee7 F www/audit.tcl 90e09d580f79c7efec0c7d6f447b7ec5c2dce5c0 F www/c_interface.tcl 2176519fc2bd2d2cf6fe74fd806fc2d8362de2c8 +F www/capi3.tcl 79fec496d95e87420c40e4058f2685a6df2f93c7 F www/changes.tcl 9d562205db584d26b358ebe93fb58039feefd1b8 F www/common.tcl f786e6be86fb2627ceb30e770e9efa83b9c67a3a F www/conflict.tcl fb8a2ba83746c7fdfd9e52fa7f6aaf5c422b8246 F www/copyright-release.html 294e011760c439c44951a6bfecd4c81a1ae359e8 F www/copyright-release.pdf cfca3558fc97095e57c6117d08f1f5b80d95125a F www/copyright.tcl 82c9670c7ddb0311912ab7fe24703f33c531066c +F www/datatype3.tcl 3dcc0baaccc7d3bc28e12a3acc45f5ab7f723ca2 F www/datatypes.tcl 566004b81c36877397ddbe6e1907aae6065f6b41 -F www/docs.tcl 60a7ce60d6f04cd5f56ab9329e0ffc22ebd6f021 +F www/docs.tcl 5ea8a84edd33030879725fca85905899c89c7075 F www/download.tcl 8c84f15695c92cb01486930055fdf5192995f474 F www/dynload.tcl 02eb8273aa78cfa9070dd4501dca937fb22b466c F www/faq.tcl 3a1776818d9bd973ab0c3048ec7ad6b1ad091ae5 @@ -210,7 +212,7 @@ F www/support.tcl 67682848d6ddd283370451dc3da2e56cded9fc9a F www/tclsqlite.tcl 19191cf2a1010eaeff74c51d83fd5f5a4d899075 F www/vdbe.tcl 59288db1ac5c0616296b26dce071c36cb611dfe9 F www/whentouse.tcl a8335bce47cc2fddb07f19052cb0cb4d9129a8e4 -P 460f2361141f14aa709addd41cc011127bac9b6e -R e79cc292e6bccbc3d8d6727cae1981b6 +P 48226a73801bc478d6fd6de5a554aec5119d2194 +R 6c1a6ec8dae26e3de7139bd0ba98440c U drh -Z 90fe3d5148e97c78c1b08256539f671c +Z dd522c4899d9ea1abb58bb871bde4c24 diff --git a/manifest.uuid b/manifest.uuid index 2f37e7de0..d5c081aac 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -48226a73801bc478d6fd6de5a554aec5119d2194
\ No newline at end of file +2005bfdad03ac2aa70a82ba7ff9b2f469d129367
\ No newline at end of file diff --git a/www/capi3.tcl b/www/capi3.tcl new file mode 100644 index 000000000..8407c0dd7 --- /dev/null +++ b/www/capi3.tcl @@ -0,0 +1,364 @@ +set rcsid {$Id: capi3.tcl,v 1.1 2004/05/31 18:22:26 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> + +<p> +SQLite version 3.0 will be a new version of SQLite, derived from +the SQLite 2.8.13 code base, but with an incompatible file format +and API. +SQLite version 3.0 is intended to answer the increasing demand +for the following features: +</p> + +<ul> +<li>Support for UTF-16.</li> +<li>User-definable text collating sequences.</li> +<li>The ability to store BLOBs in indexed columns.</li> +</ul> + +<p> +It became necessary to move to version 3.0 +to implement these features because each +requires incompatible changes to the database file format. Other +incompatible changes, such as a cleanup of the API, were introduced +at the same time under the theory that it is best to get your +incompatible changes out of the way all at once. + +<p> +The API for version 3.0 is similar to the version 2.X API, +but with some important changes. Most noticeably, the "<tt>sqlite_</tt>" +prefix that occurs on the beginning of all API functions and data +structures will be changed to "<tt>sqlite3_</tt>". +This will avoid confusion between the two APIs and allow +linking against both SQLite 2.X and SQLite 3.0 at the same time, +if desired. +</p> + +<p> +There is no agreement on what the C datatype for a UTF-16 +string should be. Therefore, SQLite uses a generic type of void* +to refer to UTF-16 strings. +Client software can cast the void* +to whatever datatype is appropriate for their system. +</p> + +<h3>2.0 C/C++ Interface</h3> + +<h4>2.1 Opening and closing a database</h4> + +<blockquote><pre> + typedef struct sqlite3 sqlite3; + int sqlite3_open(const char*, sqlite3**, const char**); + int sqlite3_open16(const void*, sqlite3**, const char**); + int sqlite3_close(sqlite3*); + const char *sqlite3_errmsg(sqlite3*); + const void *sqlite3_errmsg16(sqlite3*); + int sqlite3_errcode(sqlite3*); +</pre></blockquote> + +<p> +The sqlite3_open() routine returns an integer error code rather than +a pointer to the sqlite3 structure. The difference between sqlite3_open() +and sqlite3_open16() is that sqlite3_open16() takes UTF-16 (in host native +byte order) for the name of the database file. If a new database file +needs to be created, then sqlite3_open16() will set the internal text +representation to UTF-16 whereas sqlite3_open() will set the text +representation to UTF-8. +</p> + +<p> +The third "const char**" argument to sqlite3_open() is a NULL-terminated +list of keyword/value pairs that define options to apply to the open +request. The third argument may be NULL if there are no options. +This extra argument provides an expandable way of supporting new features +in future releases. For example, a future release may contain an +option to define an encryption/decryption key. +</p> + +<p> +The sqlite3_errcode() routine will return the result code for the most +recent major API call. sqlite3_errmsg() will return an English-language +text error message for the most recent error. The error message will +be represented in UTF-8 and will be ephemeral - it could disappear on +the next call to any SQLite API function. sqlite3_errmsg16() works like +sqlite3_errmsg() except that it returns the error message represented +as UTF-16 in host native byte order. +</p> + +<h4>2.2 Executing SQL statements</h4> + +<blockquote><pre> + typedef struct sqlite3_stmt sqlite3_stmt; + int sqlite3_prepare(sqlite3*, const char*, sqlite3_stmt**, const char**); + int sqlite3_prepare16(sqlite3*, const void*, sqlite3_stmt**, const void**); + int sqlite3_finalize(sqlite3_stmt*); + int sqlite3_reset(sqlite3_stmt*); +</pre></blockquote> + +<p> +The non-callback API is now the preferred way of accessing the database. +Wrapper functions that emulate the older callback API may (or may not) +be provided. +</p> + +<p> +The sqlite3_prepare() function compiles an single SQL statement. +The statement may contain tokens of the form "?" or "?nnn" or ":nnn:" +where "nnn" is an integer. Such tokens represent unspecified literal values +(or wildcard) to be filled in later by the sqlite3_bind() API. +Each wildcard as an associated number given +by the "nnn" that follows the "?". If the "?" is not followed by an +integer, then its number one more than the number of prior wildcards +in the same SQL statement. It is allowed for the same wildcard +to occur more than once in the same SQL statement, in which case +all instance of that wildcard will be filled in with the same value. +Unbound wildcards have a value of NULL. +</p> + + +<p>The SQL statement is a UTF-8 string for sqlite3_prepare(). +The sqlite3_prepare16() works the same way except +that it expects a UTF-16 string as SQL input. +Only the first SQL statement in the input string is compiled. +The fourth parameter is filled in with a pointer to the next (uncompiled) +SQLite statement in the input string, if any. +The sqlite3_finalize() routine deallocates a prepared SQL statement. +The sqlite3_reset() routine resets a prepared SQL statement so that it +can be executed again. +</p> + +<blockquote><pre> + int sqlite3_bind_blob(sqlite3_stmt*, int, const void*, int n, int eCopy); + int sqlite3_bind_double(sqlite3_stmt*, int, double); + int sqlite3_bind_int(sqlite3_stmt*, int, int); + int sqlite3_bind_int64(sqlite3_stmt*, int, long long int); + int sqlite3_bind_null(sqlite3_stmt*, int); + int sqlite3_bind_text(sqlite3_stmt*, int, const char*, int n, int eCopy); + int sqlite3_bind_text16(sqlite3_stmt*, int, const void*, int n, int eCopy); + int sqlite3_bind_value(sqlite3_stmt*, int, const sqlite3_value*); +</pre></blockquote> + +<p> +There is an assortment of sqlite3_bind routines used to assign values +to wildcards in a prepared SQL statement. Unbound wildcards +are interpreted as NULLs. Bindings are not reset by sqlite3_reset(). +But wildcards can be rebound to new values after an sqlite3_reset(). +</p> + +<p> +After an SQL statement has been prepared (and optionally bound), it +is executed using: +</p> + +<blockquote><pre> + int sqlite3_step(sqlite3_stmt*); +</pre></blockquote> + +<p> +The sqlite3_step() routine return SQLITE3_ROW if it is returning a single +row of the result set, or SQLITE3_DONE if execution has completed, either +normally or due to an error. It might also return SQLITE3_BUSY if it is +unable to open the database file. If the return value is SQLITE3_ROW, then +the following routines can be used to extract information about that row +of the result set: +</p> + +<blockquote><pre> + int sqlite3_column_count(sqlite3_stmt*); + int sqlite3_column_type(sqlite3_stmt*,int); + const char *sqlite3_column_decltype(sqlite3_stmt *, int i); + const char *sqlite3_column_decltype16(sqlite3_stmt *, int i); + const char *sqlite3_column_name(sqlite3_stmt*,int); + const void *sqlite3_column_name16(sqlite3_stmt*,int); + const void *sqlite3_column_blob(sqlite3_stmt*, int iCol); + int sqlite3_column_bytes(sqlite3_stmt*, int iCol); + int sqlite3_column_bytes16(sqlite3_stmt*, int iCol); + double sqlite3_column_double(sqlite3_stmt*, int iCol); + int sqlite3_column_int(sqlite3_stmt*, int iCol); + long long int sqlite3_column_int64(sqlite3_stmt*, int iCol); + const unsigned char *sqlite3_column_text(sqlite3_stmt*, int iCol); + const void *sqlite3_column_text16(sqlite3_stmt*, int iCol); + int sqlite3_column_type(sqlite3_stmt*, int iCol); +</pre></blockquote> + +<p> +The sqlite3_column_count() function returns the number of columns in +the results set. The sqlite3_column_type() function returns the +datatype for the value in the Nth column. The return value is one +of these: +</p> + +<blockquote><pre> + #define SQLITE3_INTEGER 1 + #define SQLITE3_FLOAT 2 + #define SQLITE3_TEXT 3 + #define SQLITE3_BLOB 4 + #define SQLITE3_NULL 5 +</pre></blockquote> + +<p> +The sqlite3_column_decltype() routine returns text which is the +declared type of the column in the CREATE TABLE statement. For an +expression, the return type is an empty string. sqlite3_column_name() +returns the name of the Nth column. sqlite3_column_bytes() returns +the number of bytes in a column that has type BLOB or the number of bytes +in a TEXT string with UTF-8 encoding. sqlite3_column_bytes16() returns +the same value for BLOBs but for TEXT strings returns the number of bytes +in a UTF-16 encoding. +sqlite3_column_blob() return BLOB data. +sqlite3_column_text() return TEXT data as UTF-8. +sqlite3_column_text16() return TEXT data as UTF-16. +sqlite3_column_int() return INTEGER data in the host machines native +integer format. +sqlite3_column_int64() returns 64-bit INTEGER data. +Finally, sqlite3_column_double() return floating point data. +</p> + +<h4>2.3 User-defined functions</h4> + +<p> +User defined functions can be created using the following routine: +</p> + +<blockquote><pre> + typedef struct sqlite3_value sqlite3_value; + int sqlite3_create_function( + sqlite3 *, + const char *zFunctionName, + int nArg, + int eTextRep, + int iCollateArg, + void*, + void (*xFunc)(sqlite3_context*,int,sqlite3_value**), + void (*xStep)(sqlite3_context*,int,sqlite3_value**), + void (*xFinal)(sqlite3_context*) + ); + int sqlite3_create_function16( + sqlite3*, + const void *zFunctionName, + int nArg, + int eTextRep, + int iCollateArg, + void*, + void (*xFunc)(sqlite3_context*,int,sqlite3_value**), + void (*xStep)(sqlite3_context*,int,sqlite3_value**), + void (*xFinal)(sqlite3_context*) + ); + #define SQLITE3_UTF8 1 + #define SQLITE3_UTF16LE 2 + #define SQLITE3_UTF16BE 3 + #define SQLITE3_ANY 4 +</pre></blockquote> + +<p> +The nArg parameter specifies the number of arguments to the function. +A value of 0 indicates that any number of arguments is allowed. The +eTextRep parameter specifies what representation text values are expected +to be in for arguments to this function. The value of this parameter should +be one of the parameters defined above. SQLite version 3 allows multiple +implementations of the same function using different text representations. +The database engine chooses the function that minimization the number +of text conversions required. +The iCollateArg parameter indicates that the collating sequence for the +result is to be the same as the collating sequence of the iCollateArg-th +parameter. +</p> + +<p> +Normal functions specify only xFunc and leave xStep and xFinal set to NULL. +Aggregate functions specify xStep and xFinal and leave xFunc set to NULL. +There is no separate sqlite3_create_aggregate() API. +</p> + +<p> +The function name is specified in UTF-8. A separate sqlite3_create_function16() +API works the same as sqlite_create_function() +except that the function name is specified in UTF-16 host byte order. +</p> + +<p> +Notice that the parameters to functions are now pointers to sqlite3_value +structures instead of pointers to strings as in SQLite version 2.X. +The following routines are used to extract useful information from these +"values": +</p> + +<blockquote><pre> + const void *sqlite3_value_blob(sqlite3_value*); + int sqlite3_value_bytes(sqlite3_value*); + int sqlite3_value_bytes16(sqlite3_value*); + double sqlite3_value_double(sqlite3_value*); + int sqlite3_value_int(sqlite3_value*); + long long int sqlite3_value_int64(sqlite3_value*); + const unsigned char *sqlite3_value_text(sqlite3_value*); + const void *sqlite3_value_text16(sqlite3_value*); + int sqlite3_value_type(sqlite3_value*); +</pre></blockquote> + +<p> +Function implementations use the following APIs to acquire context and +to report results: +</p> + +<blockquote><pre> + void *sqlite3_aggregate_context(sqlite3_context*, int nbyte); + void *sqlite3_user_data(sqlite3_context*); + void sqlite3_result_blob(sqlite3_context*, const void*, int n, int eCopy); + void sqlite3_result_double(sqlite3_context*, double); + void sqlite3_result_error(sqlite3_context*, const char*, int); + void sqlite3_result_error16(sqlite3_context*, const void*, int); + void sqlite3_result_int(sqlite3_context*, int); + void sqlite3_result_int64(sqlite3_context*, long long int); + void sqlite3_result_null(sqlite3_context*); + void sqlite3_result_text(sqlite3_context*, const char*, int n, int eCopy); + void sqlite3_result_text16(sqlite3_context*, const void*, int n, int eCopy); + void sqlite3_result_value(sqlite3_context*, sqlite3_value*); + void *sqlite3_get_auxdata(sqlite3_context*, int); + void sqlite3_set_auxdata(sqlite3_context*, int, void*, void (*)(void*)); +</pre></blockquote> + +<h4>2.4 User-defined collating sequences</h4> + +<p> +The following routines are used to implement user-defined +collating sequences: +</p> + +<blockquote><pre> + sqlite3_create_collation(sqlite3*, const char *zName, int eTextRep, void*, + int(*xCompare)(void*,int,const void*,int,const void*)); + sqlite3_create_collation16(sqlite3*, const void *zName, int eTextRep, void*, + int(*xCompare)(void*,int,const void*,int,const void*)); + sqlite3_collation_needed(sqlite3*, void*, + void(*)(void*,sqlite3*,int eTextRep,const char*)); + sqlite3_collation_needed16(sqlite3*, void*, + void(*)(void*,sqlite3*,int eTextRep,const void*)); +</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> + +<p> +The sqlite3_collation_needed() routine registers a callback which the +database engine will invoke if it encounters an unknown collating sequence. +The callback can lookup an appropriate comparison function and invoke +sqlite_3_create_collation() as needed. The fourth parameter to the callback +is the name of the collating sequence in UTF-8. For sqlite3_collation_need16() +the callback sends the collating sequence name in UTF-16 host byte order. +</p> +} +footer $rcsid diff --git a/www/datatype3.tcl b/www/datatype3.tcl new file mode 100644 index 000000000..637199c12 --- /dev/null +++ b/www/datatype3.tcl @@ -0,0 +1,308 @@ +set rcsid {$Id: datatype3.tcl,v 1.1 2004/05/31 18:22:26 drh Exp $} +source common.tcl +header {Datatypes In SQLite Version 3} +puts { +<h2>Datatypes In SQLite Version 3</h2> + +<h3>1. Storage Classes</h3> + +<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, 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>As in SQLite version 2, any column in a version 3 database 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 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.</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> + +<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 +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 does not prefer one storage class over +another. It makes no attempt to coerce data before +it is inserted.</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 "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 contains the string "BLOB" + 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 <table> 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 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> +</blockquote> + +<h3>3. Comparison Expressions</h3> + +<P>Like SQLite version 2, version 3 +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> + +<h4>3.1 Comparison Example</h4> + +<blockquote> +<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> +</blockquote> + +<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> + +<h3>4. Operators</h3> + +<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> + +<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> + +<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, to be used +instead of memcmp().</P> +} +footer $rcsid diff --git a/www/docs.tcl b/www/docs.tcl index 8e9dad66d..f54416a2e 100644 --- a/www/docs.tcl +++ b/www/docs.tcl @@ -1,7 +1,7 @@ # This script generates the "docs.html" page that describes various # sources of documentation available for SQLite. # -set rcsid {$Id: docs.tcl,v 1.1 2004/05/31 15:06:30 drh Exp $} +set rcsid {$Id: docs.tcl,v 1.2 2004/05/31 18:22:26 drh Exp $} source common.tcl header {SQLite Documentation} puts { @@ -28,6 +28,10 @@ doc {Version 2 C/C++ API} {c_interface.html} { A description of the C/C++ interface bindings for SQLite through version 2.8 } +doc {Version 3 C/C++ API} {capi3.html} { + A description of the C/C++ interface bindings for SQLite version 3.0.0 + and following. +} doc {Tcl API} {tclsqlite.html} { A description of the TCL interface bindings for SQLite. @@ -36,6 +40,12 @@ doc {Tcl API} {tclsqlite.html} { doc {Version 2 DataTypes } {datatypes.html} { A description of how SQLite version 2 handles SQL datatypes. } +doc {Version 3 DataTypes } {datatype3.html} { + SQLite version 3 introduces the concept of manifest typing, where the + type of a value is associated with the value itself, not the column that + it is stored in. + This page describes data typing for SQLite version 3 in further detail. +} doc {Release History} {changes.html} { A chronology of SQLite releases going back to version 1.0.0 |