SQLite3 的一鳞片爪

SQLite3引擎的结构

 

有一个这样的图片：

 

 

 

Tokenizer的任务是把原有字符串分成一个个标示符，并把这些标示符传递给剖析器。

Tokenizer的源代码tokenize.c

 

parse.y

 

文件说明：

This file contains SQLite's grammar for SQL. Process this file using the lemon parser generator to generate C code that runs the parser.  Lemon will also generate a header file containing numeric codes for all of the tokens.

 

All token codes are small integers with #defines that begin with "TK_"

...

parser之后是code generator

 

___________________________________________________

 

sqlite_master

 

数据库的框架保存在一个名叫"sqlite_master"的特殊的表中，可以像查询其它表一样执行“SELECT”查询这个特殊的表。

sqlite> .sch SQLITE_MASTER
CREATE TABLE sqlite_master (
  type text,
  name text,
  tbl_name text,
  rootpage integer,
  sql text
);
sqlite>
sqlite> select * from sqlite_master;
table|blobtest|blobtest|2|CREATE TABLE blobtest (des varchar(80), b blob)

在创建、删除和索引数据库时sqlite_master表被自动更新。不能手工更改sqlite_master表的内容。

 

___________________________________________________

 

索引

 

按主键检索特定行是一种快速的操作，因为主键数据总是排序的。

 

搜索其他列时，通常效率不高，因为其他列未排序，DBMS必须读出表中所有行，看其是否匹配。解决的办法是使用索引，为要查找的列定义一个索引，这样DBMS会保存一个其内容的排过序的列表。搜索时，DBMS首先搜索排过序的索引，找出匹配的位置，然后检索相应的行。

但是，索引也有缺点：

1、索引可能占有大量的存储空间；

2、索引虽然可以改善检索操作的性能，但是会降低数据插入、修改和删除的性能，因为插入数据的时候是需要根据索引的顺序插入，而不是在第一个可用的位置直接插入数据，这样一来，存在的索引越多将导致插入或者更新声明所需要的时间就越多。

 

// 理论上是这样，实际如何实现的？

 

___________________________________________________

 

基本的C语言接口

 

来自：...\sqlite-3_6_6-docs\cintro.html

 

 

1.0 Core Objects And Interfaces

The principal function of an SQL database engine is to evaluate statements of SQL. In order to accomplish this purpose, the developer needs to know about two objects:
The database connection object: sqlite3
The prepared statement object: sqlite3_stmt

 

Strictly speaking, the prepared statement object is not required since the convenience wrapper interfaces, sqlite3_exec or sqlite3_get_table, that encapsulate and hide the prepared statement can be used instead. Nevertheless, and understanding of prepared statements is needed to make full use of SQLite.

 

The database connection and prepared statement objects are controlled by a small set of C/C++ interface routine listed below.

sqlite3_open()
sqlite3_prepare()
sqlite3_step()
sqlite3_column_*()
sqlite3_finalize()
sqlite3_close()

 

The six C/C++ interface routines and two objects listed above form the core functionality of SQLite. The developer who understands them will have a good foundation for using SQLite.

 

SQLite3的C语言接口基本概念：

1、两个数据类型 sqlite3、sqlite3_stmt；

2、六个基本接口函数。

 

 

2009.11.25 补充______________________________________________________________

 

sqlite3_get_table(*) 失败的处理

 

在sqlite3的文档 C/C++接口部分(G:\sqlite\sqlite-3_6_6-docs\c3ref\free_table.html)，有这样一段话:

 

int sqlite3_get_table(
  sqlite3 *db,          /* An open database */
  const char *zSql,     /* SQL to be evaluated */
  char ***pazResult,    /* Results of the query */
  int *pnRow,           /* Number of result rows written here */
  int *pnColumn,        /* Number of result columns written here */
  char **pzErrmsg       /* Error msg written here */
);
...

When an error occurs during evaluation of sqlite3_get_table() the function shall set *pazResult to NULL, write an error message into memory obtained from sqlite3_malloc(), make **pzErrmsg point to that error message, and return a appropriate error code.

意思是说 调用 sqlite3_get_table(*) 失败的话，会写错误信息到 sqlite3_malloc()分配的内存空间里，也即是说，虽然失败，也会有分配空间，因此就算失败，还是需要调用 sqlite3_free(*)(还是用sqlite3_free_table(*)?)来释放空间。

2010.4.17 补充______________________________________________________________

一个关于sqlite3数据库“malformed”的相关信息，在：

PRAGMA integrity_check; 

PRAGMA integrity_check(integer)

This pragma does an integrity check of the entire database. It looks for out-of-order records, missing pages, malformed records, and corrupt indices. If any problems are found, then strings are returned (as multiple rows with a single column per row) which describe the problems. At most integer errors will be reported before the analysis quits. The default value for integer is 100. If no errors are found, a single row with the value "ok" is returned.

简单翻译如下：

这个pragma做整个数据库的完整性检查，寻找异常的记录，丢失的页数据、malformed records、错误的索引数据。发现错误后，会返回一些描述错误的信息 (多行，每行一列)。At most integer errors will be reported before the analysis quits. 缺省的integer值是100。如果没有发现错误，结束后会返回ok。

2010.4.22 补充______________________________________________________________

在这里有一个关于错误代码是21 (SQLITE_MISUSE ) 的说明：

内容如下：

Error Code SQLITE_MISUSE (21) "Library routine called out of sequence"

The SQLITE_MISUSE error code is returned when you misuse the SQLite library in some way. SQLite does not guarantee that it will detect misuse, so you should not depend on this behavior in any way. The SQLITE_MISUSE error code is intended to help you find the bugs in your code.

Here are some possible causes of SQLITE_MISUSE:

1. Calling any API routine with an sqlite3* pointer that was not obtained from sqlite3_open() or sqlite3_open16() or which has already been closed by sqlite3_close().

2. Trying to use the same database connection at the same instant in time from two or more threads.

3. Calling sqlite3_step() with a sqlite3_stmt* statement pointer that was not obtained from sqlite3_prepare() or sqlite3_prepare16() or that has already been destroyed by sqlite3_finalize().

4. Trying to bind values to a statement (using sqlite3_bind_...()) while that statement is running.

简单翻译如下：

当你以某种错误的方式使用SQLite  库时，SQLite  接口会返回 SQLITE_MISUSE 错误代码，其值为21。SQLite 不保证一定能检测到这种错误，所以无论如何你不应该依赖这个行为(?)。SQLITE_MISUSE 会帮助你找到你程序中的bug。

以下是几种出现SQLITE_MISUSE 错误的可能：

1. 没有用 sqlite3_open() or sqlite3_open16() 打开数据库，或者数据库已经被 sqlite3_close()关闭的情况下，调用任何API (用到sqlite3* 指针)；

2. 在超过2个线程里同一时刻使用相同的数据库连接；

3. 调用sqlite3_step()时，使用的sqlite3_stmt* 指针没有事先用sqlite3_prepare() or sqlite3_prepare16() 来分配空间，或者使用已经被 sqlite3_finalize() 结束的sqlite3_stmt* 指针。

4. 在sqlite3_stmt* 指向的语句 (statement) 还在运行时，尝试使用sqlite3_bind_...() 绑定值。