缘起

做了一个分享邀请码的工具: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这列