Oops, some errors in the second example: ``` def OpenRowid(db, table, rowid): if db.execute('select _rowid_ from %s where _rowid_ == ?;' % (table,), (rowid,)).fetchone() is not None: db.execute(OpenRowid.sql % (table,table), (rowid,)) OpenRowid.sql = ''' insert into %s (_rowid_) select _rowid_ from %s where _rowid_ >= ? order by _rowid_ desc on conflict (_rowid_) do update set _rowid_ = excluded._rowid_ + 1; ''' def RenumberTable(db, table): db.execute(RenumberTable.sql % (table,table,table,table,table,table,table)) RenumberTable.sql = ''' begin immediate; drop table if exists temp.r; create table temp.r ( old integer not null, new not null, primary key (old, new) ) without rowid; -- close up all rowid > 0 gaps, renumber going up insert into temp.r select _rowid_ as old, row_number() over (order by _rowid_) as new from %s where _rowid_ > 0; insert into %s (_rowid_) select old from temp.r where old != new order by old on conflict (_rowid_) do update set _rowid_ = (select new from temp.r where old == excluded._rowid_); -- make space for the rowid < 0 rows, renumber going down delete from temp.r; insert into temp.r select _rowid_ as old, row_number() over (order by _rowid_) + (select count(*) from %s where _rowid_ < 1) as new from %s where _rowid_ > 0; insert into %s (_rowid_) select old from temp.r where old != new order by old desc on conflict (_rowid_) do update set _rowid_ = (select new from temp.r where old == excluded._rowid_); -- set final row numbers for rowid < 0 rows, renumber going down delete from temp.r; insert into temp.r select _rowid_ as old, row_number() over (order by _rowid_) as new from %s where _rowid_ < 1; insert into %s (_rowid_) select old from temp.r order by old desc on conflict (_rowid_) do update set _rowid_ = (select new from temp.r where old == excluded._rowid_); drop table temp.r; commit; ''' if __name__ == '__main__': import apsw db = apsw.Connection() db.execute('create table x(id integer primary key, v integer);') db.execute('insert into x select value*10, value from generate_series where start=1 and stop=10;') db.execute('insert into x select -(value-1)*10, -value from generate_series where start=1 and stop=10;') db.execute('delete from x where id=50;') db.execute('delete from x where id=-30;') for row in db.execute('select * from x;'): print(row) print() RenumberTable(db, 'x') for row in db.execute('select * from x;'): print(row) print() OpenRowid(db, 'x', 3) OpenRowid(db, 'x', -4) for row in db.execute('select * from x;'): print(row) print() db.execute('insert into x values (3, 103);') db.execute('insert into x values (-4, -104);') for row in db.execute('select * from x;'): print(row) print() RenumberTable(db, 'x') for row in db.execute('select * from x;'): print(row) print() ``` Now also includes sample test same in both.