sqlite中的sequence - The AUTOINCREMENT Keyword

今天想在sqlite中使用sequence 找到了 AUTOINCREMENT 。通过 AUTOINCREMENT 可以得到一个sequence。

AUTOINCREMENT 使用方法

定义表时, 将一列用"AUTOINCREMENT"修饰。例如：
----------------------------------------------
create table tf_b_spy
(
 tradeid integer primary key autoincrement ,
 ylabel real,
 spytime date
);
----------------------------------------------

注1: AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY
注2: 从此 tf_b_spy.tradeid = tf_b_spy.rowid。 :)
定义后，向表中插记录，字段tradeid的值会自增1。

AUTOINCREMENT的“管理器” sqlite_sequence

当一张表包含了用 "AUTOINCREMENT" 修饰的列时, sqlite 将自动创建表 "SQLITE_SEQUENCE"。
表SQLITE_SEQUENCE中只有两个字段： name, seq 。
建表语句为： CREATE TABLE sqlite_sequence(name,seq);
注3：可以向 sqlite_sequence 中添加重复的记录
seq值是可以手工修改的。
例如：
Example2：
update sqlite_sequence set seq = 2008 where name = 'tf_b_spy';
向表“tf_b_spy”中添加记录后，tf_b_spy.tradeid 将变为 2009。

Example3： 
update sqlite_sequence set seq = 1 where name = 'tf_b_spy';
向表“tf_b_spy”中添加记录后，tf_b_spy.tradeid 将变为 2010。

通过多次 insert、update、delete 表 sqlite_sequence 推出一个猜测：
向 tf_b_spy 插数据时， sqlite 会做一下动作：

# get currval of tf_b_spy.rowid
curr_rowid = select max(rowid) from tf_b_spy
if curr_rowid is None: curr_rowid = 0

#get seq and rowid from table sqlite_sequence
seq, rowid = select seq,rowid from sqlite_sequence where name = 'tf_b_spy' and seq = :rowid order by rowid limit 1;
if seq is None:
    seq = select max(seq) from sqlite_sequence where name = 'tf_b_spy';
    rowid = select rowid from sqlite_sequence where name = 'tf_b_spy' and seq = :seq order by rowid limit 1;
    #seq is None? ^^

#get next_rowid
next_rowid = seq + 1 if seq > curr_rowid else curr_rowid +1

# update table tf_b_spy
insert into tf_b_spy (tradeid, ylabel, spytime) values (:next_rowid, :ylabel, :spytime)

# update table sqlite_sequence
if seq is not None:
    update sqlite_sequence set seq = :next_rowid where name = 'tf_b_spy' and seq = :seq and rowid = :rowid;
else:
    insert into sqlite_sequence (name, seq) values ('tf_b_spy', :next_rowid);