数据库

1:sqlite常用接口

2个重要结构体和5个主要函数：

sqlite3               *pdb, 数据库句柄，跟文件句柄FILE很类似

sqlite3_stmt      *stmt, 这个相当于ODBC的Command对象，用于保存编译好的SQL语句

 

sqlite3_open(),   打开数据库

sqlite3_exec(),   执行非查询的sql语句

sqlite3_prepare(), 准备sql语句，执行select语句或者要使用parameter bind时，用这个函数（封装了sqlite3_exec）.

Sqlite3_step(), 在调用sqlite3_prepare后，使用这个函数在记录集中移动。

Sqlite3_close(), 关闭数据库文件

 

还有一系列的函数，用于从记录集字段中获取数据，如

sqlite3_column_text(), 取text类型的数据。

sqlite3_column_blob（），取blob类型的数据

sqlite3_column_int(), 取int类型的数据

…

 2:sqlite数据类型介绍

     在进行数据库Sql操作之前，首先有个问题需要说明，就是Sqlite的数据类型，和其他的数据库不同，Sqlite支持的数据类型有他自己的特色，这个特色有时会被认为是一个潜在的缺点，但是这个问题并不在我们的讨论范围之内。

大 多数的数据库在数据类型上都有严格的限制，在建立表的时候，每一列都必须制定一个数据类型，只有符合该数据类型的数据可以被保存在这一列当中。而在 Sqlite 2.X中，数据类型这个属性只属于数据本生，而不和数据被存在哪一列有关，也就是说数据的类型并不受数据列限制（有一个例外：INTEGER PRIMARY KEY，该列只能存整型数据）。

但是当Sqlite进入到3.0版本的时候，这个问题似乎又有了新的答 案，Sqlite的开发者开始限制这种无类型的使用，在3.0版本当中，每一列开始拥有自己的类型，并且在数据存入该列的时候，数据库会试图把数据的类型 向该类型转换，然后以转换之后的类型存储。当然，如果转换被认为是不可行的，Sqlite仍然会存储这个数据，就像他的前任版本一样。

举个例子，如果你企图向一个INTEGER类型的列中插入一个字符串，Sqlite会检查这个字符串是否有整型数据的特征, 如果有而且可以被数据库所识别，那么该字符串会被转换成整型再保存，如果不行，则还是作为整型存储。

总的来说，所有存在Sqlite 3.0版本当中的数据都拥有以下之一的数据类型：

空（NULL）：该值为空

整型（INTEGEER）：有符号整数，按大小被存储成1,2,3,4,6或8字节。

实数（REAL）：浮点数，以8字节指数形式存储。

文本（TEXT）：字符串，以数据库编码方式存储（UTF-8, UTF-16BE 或者 UTF-16-LE）。

BLOB：BLOB数据不做任何转换，以输入形式存储。

ps: 在关系数据库中，CLOB和BLOB类型被用来存放大对象。BOLB表示二进制大对象，这种数据类型通过用来保存图片，图象，视频等。CLOB表示字符大对象，能够存放大量基于字符的数据。

对应的，对于数据列，同样有以下的数据类型：

TEXT

NUMERIC

INTEGER

REAL

NONE

数据列的属性的作用是确定对插入的数据的转换方向：

TEXT 将数据向文本进行转换，对应的数据类型为NULL，TEXT 或 BLOB

NUMERIC 将数据向数字进行转换，对应的数据类型可能为所有的五类数据，当试图存入文本 时将执行向整型或浮点类型的转换（视具体的数值而定），转换若不可行，则保留文本类型存储，NULL或BLOB不做变化

INTEGER 将数据向整型转换，类似于NUMERIC，不同的是没有浮点标志的浮点数将转换为整型保存

REAL 将数据向浮点数类型转换，类似于NUMERIC，不同的是整数将转换为浮点数保存

NULL 不做任何转换的数据列类型

 

实例代码如下,

附件工程可直接编译，例子使用了blob数据类型。

#include "sqlite3.h"                                //包含一个头文件就可以使用所以sqlite的接口了

#include "stdlib.h"

#include "stdio.h"

#include "string.h"

 

#pragma comment(lib, "sqlite.lib")           //我把sqlite编译成了一个静态的lib文件。

 

void       createdb();

void       querydb();

 

int         main()

{

            createdb();

            querydb();

 

            return 0;

}

 

void       createdb()

{

            int                     ret;

            sqlite3               *pdb = 0;

            sqlite3_stmt      *stmt = 0;

            char                  *error = 0;

            char                  *sql = "insert into table1 values('value11',:aaa)";

            int                     index;

            static void          *value = "asdfadsfasdfjasdfjaksdfaskjdfakdsfaksfja";

 

            ret = sqlite3_open("db1.sdb", &pdb);                    //打开数据库，跟打开文本文件一样

            if( ret != SQLITE_OK )

                        return;

            ret = sqlite3_exec(pdb, "create table table1(col1 char(20), col2 BLOB)", 0,0, &error );

            if( ret != SQLITE_OK )

                        return;

 

            ret = sqlite3_prepare(pdb, sql,strlen(sql), &stmt, &error);

            if( ret != SQLITE_OK )

                        return;

 

            index = sqlite3_bind_parameter_index(stmt, ":aaa");

 

            ret = sqlite3_bind_blob(stmt, index, value, strlen(value), SQLITE_STATIC);

            if( ret != SQLITE_OK )

                        return;

 

            ret = sqlite3_step(stmt);

           

            if( ret != SQLITE_DONE )

                        return;

 

            sqlite3_close(pdb);        

}

 

void       querydb()

{

            int                     ret;

            sqlite3   *pdb = 0;

            sqlite3_stmt      *pstmt = 0;

            char      *error = 0;

            char      *sql = "select * from table1";

            int                     len;

            int                     i;

            char      *name;

            void       *value;

 

            ret = sqlite3_open("db1.sdb", &pdb);

            if( ret != SQLITE_OK )

                        return;

 

            ret = sqlite3_prepare(pdb, sql, strlen(sql), &pstmt, &error);

            if( ret != SQLITE_OK )

                        return;

 

            while( 1 )

            {

                        ret = sqlite3_step(pstmt);

                        if( ret != SQLITE_ROW )

                                    break;

 

                        name = sqlite3_column_text(pstmt, 0);

                        value = sqlite3_column_blob(pstmt, 1);

                        len = sqlite3_column_bytes(pstmt,1 );

            }

}

 

 

实例二:SQLite中如何用api操作blob类型的字段

 

   在实际的编程开发当中我们经常要处理一些大容量二进制数据的存储，如图片或者音乐等等。对于这些二进制数据（blob字段）我们不能像处理普通的文本那样 简单的插入或者查询，为此SQLite提供了一组函数来处理这种BLOB字段类型。下面的代码演示了如何使用这些API函数。

 

首先我们要建立一个数据库：

sqlite3_exec(db, "CREATE TABLE list (fliename varchar(128) UNIQUE, fzip blob);", 0, 0, &zErrMsg);

 

//由于mmmm.rar是一个二进制文件，所以要在使用insert语句时先用？号代替

     sqlite3_prepare(db, "insert into list values ('mmmm.rar',?);", -1, &stat, 0);

    

     FILE *fp;

     long filesize = 0;

     char * ffile;

 

     fp = fopen("mmmm.rar", "rb");

 

     if(fp != NULL)

     {

         //计算文件的大小

         fseek(fp, 0, SEEK_END);

         filesize = ftell(fp);

         fseek(fp, 0, SEEK_SET);

 

         //读取文件

         ffile = new char[filesize+1];

         size_t sz = fread(ffile, sizeof(char), filesize+1, fp);

             

         fclose(fp);

     }

 

     //将文件数据绑定到insert语句中，替换“？”部分

     sqlite3_bind_blob(stat, 1, ffile, filesize, NULL);

    

//执行绑定之后的SQL语句

     sqlite3_step(stat);

这时数据库当中已经有了一条包含BLOB字段的数据。接下来我们要读取这条数据：

    

     //选取该条数据

     sqlite3_prepare(db, "select * from list;", -1, &stat, 0);

     sqlite3_step(stat);

//得到纪录中的BLOB字段

const void * test = sqlite3_column_blob(stat, 1);

//得到字段中数据的长度

     int size = sqlite3_column_bytes(stat, 1);

    

     //拷贝该字段

     sprintf(buffer2, "%s", test);

此时可以将buffer2写入到文件当中，至此BLOB数据处理完毕。

 

实例三:sqlite 中用blob存储图片和取出图片

 

#include

#include

#include

using namespace std;

int main()

{

        sqlite3 *db;

 sqlite3_stmt *stat;

 char *zErrMsg = 0;

 char buffer2[1024]="0";

 sqlite3_open("./MetaInfo.db", &db);

    int result;

     if(result)

      {

         cout

       }

   

     else

          cout

 sqlite3_exec(db, "CREATE TABLE list (fliename varchar(128) UNIQUE, fzip blob);", 0, 0, &zErrMsg);

 sqlite3_prepare(db, "insert into list values ('./data/2.bmp',?);", -1, &stat, 0);

 

 FILE *fp;

 long filesize = 0;

 char * ffile;

 fp = fopen("./data/2.bmp", "rb");

 if(fp != NULL)

 {

  fseek(fp, 0, SEEK_END);

  filesize = ftell(fp);

  fseek(fp, 0, SEEK_SET);

  ffile = new char[filesize+1];

  size_t sz = fread(ffile, sizeof(char), filesize+1, fp);

   

  fclose(fp);

 }

 sqlite3_bind_blob(stat, 1, ffile, filesize, NULL);

 sqlite3_step(stat);

 sqlite3_prepare(db, "select * from list;", -1, &stat, 0);

 sqlite3_step(stat);

 const void * test = sqlite3_column_blob(stat, 1);

 int size = sqlite3_column_bytes(stat, 1);

 sprintf(buffer2, "%s", test);

 FILE *fp2;

 fp2 = fopen("outfile.png", "wb");

 if(fp2 != NULL)

 {

  size_t ret = fwrite(test, sizeof(char), size, fp2);

  fclose(fp2);

 }

 delete(ffile);

 sqlite3_finalize(stat);

 sqlite3_close(db);

   return 0;

}

     SQLite3是SQLite一个全新的版本,它虽然是在SQLite 2.8.13的代码基础之上开发的,但是使用了和之前的版本不兼容的数据库格式和API. SQLite3是为了满足以下的需求而开发的:

支持UTF-16编码.

用户自定义的文本排序方法.

可以对BLOBs字段建立索引.

因此为了支持这些特性我改变了数据库的格式,建立了一个与之前版本不兼容的3.0版. 至于其他的兼容性的改变,例如全新的API等等,都将在理论介绍之后向你说明,这样可以使你最快的一次性摆脱兼容性问题.

3.0版的和2.X版的API非常相似,但是有一些重要的改变需要注意. 所有API接口函数和数据结构的前缀都由"sqlite_"改为了"sqlite3_". 这是为了避免同时使用SQLite 2.X和SQLite 3.0这两个版本的时候发生链接冲突.

由于对于C语言应该用什么数据类型来存放UTF-16编码的字符串并没有一致的规范. 因此SQLite使用了普通的void* 类型来指向UTF-16编码的字符串. 客户端使用过程中可以把void*映射成适合他们的系统的任何数据类型.

2.0 C/C++ 接口

SQLite 3.0一共有83个API函数,此外还有一些数据结构和预定义(#defines). (完整的API介绍请参看另一份文档.) 不过你们可以放心,这些接口使用起来不会像它的数量所暗示的那么复杂. 最简单的程序仍然使用三个函数就可以完成: sqlite3_open(), sqlite3_exec(), 和 sqlite3_close(). 要是想更好的控制数据库引擎的执行,可以使用提供的sqlite3_prepare()函数把SQL语句编译成字节码,然后在使用 sqlite3_step()函数来执行编译后的字节码. 以sqlite3_column_开头的一组API函数用来获取查询结果集中的信息. 许多接口函数都是成对出现的,同时有UTF-8和UTF-16两个版本. 并且提供了一组函数用来执行用户自定义的SQL函数和文本排序函数.

2.1 如何打开关闭数据库

   typedef struct sqlite3 sqlite3;

   int sqlite3_open(const char*, sqlite3**);

   int sqlite3_open16(const void*, sqlite3**);

   int sqlite3_close(sqlite3*);

   const char *sqlite3_errmsg(sqlite3*);

   const void *sqlite3_errmsg16(sqlite3*);

   int sqlite3_errcode(sqlite3*);

sqlite3_open() 函数返回一个整数错误代码,而不是像第二版中一样返回一个指向sqlite3结构体的指针. sqlite3_open() 和 sqlite3_open16() 的不同之处在于sqlite3_open16() 使用UTF-16编码(使用本地主机字节顺序)传递数据库文件名. 如果要创建新数据库, sqlite3_open16() 将内部文本转换为UTF-16编码, 反之sqlite3_open() 将文本转换为UTF-8编码.

打开或者创建数据库的命令会被缓存,直到这个数据库真正被调用的时候才会被执行. 而且允许使用PRAGMA声明来设置如本地文本编码或默认内存页面大小等选项和参数.

sqlite3_errcode() 通常用来获取最近调用的API接口返回的错误代码. sqlite3_errmsg() 则用来得到这些错误代码所对应的文字说明. 这些错误信息将以 UTF-8 的编码返回,并且在下一次调用任何SQLite API函数的时候被清除. sqlite3_errmsg16() 和 sqlite3_errmsg() 大体上相同,除了返回的错误信息将以 UTF-16 本机字节顺序编码.

SQLite3的错误代码相比SQLite2没有任何的改变,它们分别是:

#define SQLITE_OK           0  

#define SQLITE_ERROR        1  

#define SQLITE_INTERNAL     2  

#define SQLITE_PERM         3  

#define SQLITE_ABORT        4  

#define SQLITE_BUSY         5  

#define SQLITE_LOCKED       6  

#define SQLITE_NOMEM        7  

#define SQLITE_READONLY     8  

#define SQLITE_INTERRUPT    9  

#define SQLITE_IOERR       10  

#define SQLITE_CORRUPT     11  

#define SQLITE_NOTFOUND    12  

#define SQLITE_FULL        13  

#define SQLITE_CANTOPEN    14  

#define SQLITE_PROTOCOL    15  

#define SQLITE_EMPTY       16  

#define SQLITE_SCHEMA      17  

#define SQLITE_TOOBIG      18  

#define SQLITE_CONSTRAINT  19  

#define SQLITE_MISMATCH    20  

#define SQLITE_MISUSE      21  

#define SQLITE_NOLFS       22  

#define SQLITE_AUTH        23  

#define SQLITE_ROW         100  

#define SQLITE_DONE        101  

2.2 执行 SQL 语句

       typedef int (*sqlite_callback)(void*,int,char**, char**);

       int sqlite3_exec(sqlite3*, const char *sql, sqlite_callback, void*, char**);

sqlite3_exec 函数依然像它在SQLite2中一样承担着很多的工作. 该函数的第二个参数中可以编译和执行零个或多个SQL语句. 查询的结果返回给回调函数. 更多地信息可以查看API 参考.

在SQLite3里,sqlite3_exec一般是被准备SQL语句接口封装起来使用的.

       typedef struct sqlite3_stmt sqlite3_stmt;

       int sqlite3_prepare(sqlite3*, const char*, int, sqlite3_stmt**, const char**);

       int sqlite3_prepare16(sqlite3*, const void*, int, sqlite3_stmt**, const void**);

       int sqlite3_finalize(sqlite3_stmt*);

       int sqlite3_reset(sqlite3_stmt*);

sqlite3_prepare 接口把一条SQL语句编译成字节码留给后面的执行函数. 使用该接口访问数据库是当前比较好的的一种方法.

sqlite3_prepare() 处理的SQL语句应该是UTF-8编码的. 而sqlite3_prepare16() 则要求是UTF-16编码的. 输入的参数中只有第一个SQL语句会被编译. 第四个参数则用来指向输入参数中下一个需要编译的SQL语句存放的SQLite statement对象的指针, 任何时候如果调用 sqlite3_finalize() 将销毁一个准备好的SQL声明. 在数据库关闭之前，所有准备好的声明都必须被释放销毁. sqlite3_reset() 函数用来重置一个SQL声明的状态，使得它可以被再次执行.

SQL 声明可以包含一些型如"?" 或 "?nnn" 或 ":aaa"的标记， 其中"nnn" 是一个整数，"aaa" 是一个字符串. 这些标记代表一些不确定的字符值（或者说是通配符），可以在后面用sqlite3_bind 接口来填充这些值. 每一个通配符都被分配了一个编号（由它在SQL声明中的位置决定，从1开始），此外也可以用 "nnn" 来表示 "?nnn" 这种情况. 允许相同的通配符在同一个SQL声明中出现多次, 在这种情况下所有相同的通配符都会被替换成相同的值. 没有被绑定的通配符将自动取NULL值.

       int sqlite3_bind_blob(sqlite3_stmt*, int, const void*, int n, void(*)(void*));

       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, void(*)(void*));

       int sqlite3_bind_text16(sqlite3_stmt*, int, const void*, int n, void(*)(void*));

       int sqlite3_bind_value(sqlite3_stmt*, int, const sqlite3_value*);

以 上是 sqlite3_bind 所包含的全部接口，它们是用来给SQL声明中的通配符赋值的. 没有绑定的通配符则被认为是空值. 绑定上的值不会被sqlite3_reset()函数重置. 但是在调用了sqlite3_reset()之后所有的通配符都可以被重新赋值.

在SQL声明准备好之后(其中绑定的步骤是可选的), 需要调用以下的方法来执行:

       int sqlite3_step(sqlite3_stmt*);

如 果SQL返回了一个单行结果集，sqlite3_step() 函数将返回 SQLITE_ROW , 如果SQL语句执行成功或者正常将返回 SQLITE_DONE , 否则将返回错误代码. 如果不能打开数据库文件则会返回 SQLITE_BUSY . 如果函数的返回值是 SQLITE_ROW, 那么下边的这些方法可以用来获得记录集行中的数据:

       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);

       int sqlite3_column_count(sqlite3_stmt*);

       const char *sqlite3_column_decltype(sqlite3_stmt *, int iCol);

       const void *sqlite3_column_decltype16(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 char *sqlite3_column_name(sqlite3_stmt*, int iCol);

       const void *sqlite3_column_name16(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);

sqlite3_column_count() 函数返回结果集中包含的列数. sqlite3_column_count() 可以在执行了 sqlite3_prepare()之后的任何时刻调用. sqlite3_data_count()除了必需要在sqlite3_step()之后调用之外，其他跟sqlite3_column_count() 大同小异. 如果调用sqlite3_step() 返回值是 SQLITE_DONE 或者一个错误代码, 则此时调用sqlite3_data_count() 将返回 0 ，然而 sqlite3_column_count() 仍然会返回结果集中包含的列数.

返回的记录集通过使用其它的几个 sqlite3_column_***() 函数来提取, 所有的这些函数都把列的编号作为第二个参数. 列编号从左到右以零起始. 请注意它和之前那些从1起始的参数的不同.

sqlite3_column_type()函数返回第N列的值的数据类型. 具体的返回值如下:

       #define SQLITE_INTEGER  1

       #define SQLITE_FLOAT    2

       #define SQLITE_TEXT     3

       #define SQLITE_BLOB     4

       #define SQLITE_NULL     5

sqlite3_column_decltype() 则用来返回该列在 CREATE TABLE 语句中声明的类型. 它可以用在当返回类型是空字符串的时候. sqlite3_column_name() 返回第N列的字段名. sqlite3_column_bytes() 用来返回 UTF-8 编码的BLOBs列的字节数或者TEXT字符串的字节数. sqlite3_column_bytes16() 对于BLOBs列返回同样的结果，但是对于TEXT字符串则按 UTF-16 的编码来计算字节数. sqlite3_column_blob() 返回 BLOB 数据. sqlite3_column_text() 返回 UTF-8 编码的 TEXT 数据. sqlite3_column_text16() 返回 UTF-16 编码的 TEXT 数据. sqlite3_column_int() 以本地主机的整数格式返回一个整数值. sqlite3_column_int64() 返回一个64位的整数. 最后, sqlite3_column_double() 返回浮点数.

不一定非要按照sqlite3_column_type()接口返回的数据类型来获取数据. 数据类型不同时软件将自动转换.

Datatypes In SQLite Version 3

1. Storage Classes

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.

Each value stored in an SQLite database (or manipulated by the database engine) has one of the following storage classes:

NULL. The value is a NULL value.

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

REAL. The value is a floating point value, stored as an 8-byte IEEE floating point number.

TEXT. The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16-LE).

BLOB. The value is a blob of data, stored exactly as it was input.

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

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.

Storage classes are initially assigned as follows:

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.

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

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.

2. Column Affinity

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 manifest typing.) 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.

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

Each column in an SQLite 3 database is assigned one of the following type affinities:

TEXT

NUMERIC

INTEGER

REAL

NONE

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.

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.

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.

A column with REAL affinity behaves like a column with NUMERIC affinity except that it forces integer values into floating point representation. (As an optimization, integer values are stored on disk as integers in order to take up less space and are only converted to floating point as the value is read out of the table.)

A column with affinity NONE does not prefer one storage class over another. It makes no attempt to coerce data before it is inserted.

2.1 Determination Of Column Affinity

The type affinity of a column is determined by the declared type of the column, according to the following rules:

If the datatype contains the string "INT" then it is assigned INTEGER affinity.

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.

If the datatype for a column contains the string "BLOB" or if no datatype is specified then the column has affinity NONE.

If the datatype for a column contains any of the strings "REAL", "FLOA", or "DOUB" then the column has REAL affinity

Otherwise, the affinity is NUMERIC.

If a table is created using a "CREATE TABLE

AS SELECT..." statement, then all columns have no datatype specified and they are given no affinity.

2.2 Column Affinity Example

CREATE TABLE t1(

    t  TEXT,

    nu NUMERIC, 

    i  INTEGER,

    no BLOB

);

-- 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);

3. Comparison Expressions

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

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

A value with storage class NULL is considered less than any other value (including another value with storage class NULL).

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.

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.

When two BLOB values are compared, the result is always determined using memcmp().

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. Note that if X and Y.Z are a column names, then +X and +Y.Z are considered expressions.

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.

When two column values are compared, if one column has INTEGER or REAL or NUMERIC affinity and the other does not, then NUMERIC affinity is applied to any values with storage class TEXT extracted from the non-NUMERIC column.

When the results of two expressions are compared, 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.

In SQLite, the expression "a BETWEEN b AND c" is equivalent to "a >= b AND a

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.

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.

3.1 Comparison Example

CREATE TABLE t1(

    a TEXT,

    b NUMERIC,

    c BLOB

);

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

1|0

-- Comparisons are numeric. No conversions are required.

SELECT b

0|1

-- Both 60 and 600 (storage class NUMERIC) are less than '500'

-- (storage class TEXT).

SELECT c

0|0

4. Operators

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.

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.

5. Sorting, Grouping and Compound SELECTs

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.

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.

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.

6. Other Affinity Modes

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:

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

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

7. User-defined Collation Sequences

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

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:

BINARY - Compares string data using memcmp(), regardless of text encoding.

NOCASE - 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 due full UTF case folding due to the size of the tables required.

RTRIM - The same as binary, except that trailing space characters are ignored.

7.1 Assigning Collation Sequences from SQL

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 column definition to define it.

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.

For binary comparison operators (=, , =) 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.

The expression "x BETWEEN y and z" is equivalent to "x >= y AND x

An ORDER BY 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.

7.2 Collation Sequences Example

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.

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;