Sqlite数据库自动建表

缘起

做了一个分享邀请码的工具：http://code.lmbj.net/。有个需求是显示有多少人已经索码成功。问题在于程序的设计是只保存正在索码的数据，索码成功的数据直接删除，所以需求转变为如何得到已删除数据的条数。

考虑到id是自增长的，数据库中应该有系统表来保存相关信息，用sqliteman打开数据文件发现确实存在这样一张系统表sqlite_sequence。表中保存了表名和自增长的字段（一般就是主键id）的当前值，测试了下，如果包含自增长字段的表没有插入过数据，不会保存在此表里，只要曾经插入过数据，无论当前是否有数据，该值都会存在。

至此，问题得以解决：执行sql语句通过表名从sqlite_sequence表查询id的当前值，然后减去正在索码的条数就是已删除数据的条数，也就是已经有多少人索码成功。唯一需要注意的是初始化时没有人索码，查询不到记录，需要做异常处理。

看个究竟

做Android开发其实经常打开sqlite数据文件，以前也看到过系统表，只是从来没有研究过，趁此机会看个明白。

在sqlite数据库文件中，一旦你建表，就会在系统目录(System Catalogue)下自动生成2个系统表sqlite_master和sqlite_sequence。

sqlite_master保存了所有表的信息，包括type，是table或者index等，name和tbl_name，二者看起来内容一样。还有一个rootpage，可能是建表顺序。sql在类型是table的时候就是建表的sql语句。

当你的表中有自增长的字段时，sqlite_sequence就会保存下表名和该字段的序列值，当然前提是你已经插入过数据，否则不会保存。

自己看的差不多，然后分别搜索了下这2张表。找到2篇相关的文章解释的比较详细：

sqlite的系统表sqlite_master

SQLite数据库中一个特殊的名叫SQLITE_MASTER上执行一个SELECT查询以获得所有表的索引。每一个SQLite数据库都有一个叫SQLITE_MASTER的表，它定义数据库的模式。SQLITE_MASTER表看起来如下：

CREATE TABLE sqlite_master (
type TEXT,
name TEXT,
tbl_name TEXT,
rootpage INTEGER,
sql TEXT
);

对于表来说，type 字段永远是 ‘table’，name 字段永远是表的名字。所以，要获得数据库中所有表的列表，使用下列SELECT语句：

SELECT name FROM sqlite_master
WHERE type=’table’
ORDER BY name;

对于索引，type 等于 ‘index’, name 则是索引的名字，tbl_name 是该索引所属的表的名字。不管是表还是索引，sql 字段是原先用 CREATE TABLE 或 CREATE INDEX 语句创建它们时的命令文本。对于自动创建的索引（用来实现 PRIMARY KEY 或 UNIQUE 约束），sql字段为NULL。

SQLITE_MASTER 表是只读的。不能对它使用 UPDATE、INSERT 或 DELETE。它会被 CREATE TABLE、CREATE INDEX、DROP TABLE 和 DROP INDEX 命令自动更新。

临时表不会出现在SQLITE_MASTER表中。临时表及其索引和触发器存放在另外一个叫SQLITE_TEMP_MASTER的表中。SQLITE_TEMP_MASTER跟SQLITE_MASTER差不多，但它只是对于创建那些临时表的应用可见。如果要获得所有表的列表，不管是永久的还是临时的，可以使用类似下面的命令：

SELECT name FROM
(SELECT * FROM sqlite_master UNION ALL
SELECT * FROM sqlite_temp_master)
WHERE type=’table’
ORDER BY name )

重置SQLite中的自动编号列

目前流行的数据库都提供了自动编号类型，SQLite也不例外。当数据库中包含自动编号的字段时，SQLite会自动建立一个名为sqlite_sequence的表。这个表包含两个字段：name 和 seq 。name字段记录了自动编号字段所在的表，seq字段记录了当前用到的序号（下一条记录的编号就是当前序号加1）。

在开发过程中，我们经常要把表重置。也就是说把表中的记录全部清空，并把自动编号归0。在SQLite中，只需要修改 sqlite_sequence 表就可以了：

UPDATE sqlite_sequence SET seq = 0 WHERE name = \'TableName\'

也可以直接把该记录删掉：

DELETE FROM sqlite_sequence WHERE name = \'TableName\'

要是想重置所有表，那直接把 sqlite_sequence 清空就可以了：

DELETE FROM sqlite_sequence

验证

看完以上2篇文章做了下验证，sqlite_master表对应用来说是只读，不可更改，尝试执行删除操作得到以下错误：

Query Error: table sqlite_master may not be modified Unable to execute statement

sqlite_sequence表可以任意操作，比如可以直接删除一个表的记录。但是不推荐单独对此表执行查询之外的操作。如果确实需要操作此表的话，可以把操作放在和相关表的处理的同一个事务里，防止引入一些奇怪的bug。

额外赠送

android_metadata表

以前做过一个Android应用使用已有的数据库文件，而不是应用内自建的数据库。发现Android中使用sqlite数据库，必须要有一张android_metadata表，表中只有一个字段locale,一般是TEXT的，内容为zh_CN或者en-us。对于大多数情况都是Android应用新建数据库时自动创建这个表。如果要使用已有的数据库文件，就需要自己创建android_metadata这张表。

_id字段

Android中要使用CursorAdapter及其子类的时候，数据库中必须有_id字段，详情请看我之前写的博客：Android对数据库表的一个约定：每张表都应该至少有_id这列

缘起#

看个究竟#

sqlite的系统表sqlite_master#

重置SQLite中的自动编号列#

验证#

额外赠送#

android_metadata表#

_id字段#

缘起