So, here are some examples in Python of how to "renumber" tables. This uses the rowid as the indicator of order and allows you to "open up" a row at a specific location by moving an existing row at that location and all following rows "up by one", and how to "renumber" a rowid table so that all the current record numbers are set to start at 1 and increment by 1. All this is done without having to rebalance the b-tree. I suppose you could use any UNIQUE index you liked for the rowid (ie, position) by why have extra indirection that you do not need? Unless, of course, you have "multiple ordered lists" in one table, in which case you would be better off with a without rowid table where the primary key is the `(list_id, _rowid_)` after which you just need to modify the below to ad a `where list_id == :list_id` everywhere to constrain the operation to one list_id ...
Getting the output in the "right order" is simply a matter of
`select * from <table> order by _rowid_;`
```
def OpenRowid(db, table, id, rowid='_rowid_'):
# Check if we need to open up the rowid
sql = 'select %s from %s where %s == ?' % (rowid, table, rowid)
if db.execute(sql, (id,)).fetchone() is not None:
db.beginimmediate()
# Get rowid's to renumber
sql = 'select %s, %s + 1 from %s where %s >= ? order by %s desc;' % (rowid, rowid, table, rowid, rowid)
r = [(row[0], row[1]) for row in db.execute(sql, (id,)) if row[0] != row[1]]
# Renumber all the rowid's that need renumbering
sql = 'update %s set %s = ? where %s == ?;' % (table, rowid, rowid)
for old, new in r:
db.execute(sql, (new, old))
db.commit()
def RenumberRowids(db, table, rowid='_rowid_'):
db.beginimmediate()
# Generate the SQL to get all the rowid > 0 rows that need renumberid
sql = 'select %s, row_number() over (order by %s) from %s where %s > 0 order by %s;' % (rowid, rowid, table, rowid, rowid)
r = [(row[0], row[1]) for row in db.execute(sql) if row[0] != row[1]]
# Renumber all the rowid's that need renumbering which will compact the rowids > 0
sql = 'update %s set %s = ? where %s == ?;' % (table, rowid, rowid)
for old, new in r:
db.execute(sql, (new, old))
# Find out if there are any rowid's < 1 that we need to renumber
sql = 'select coalesce(count(*), 0) from %s where %s < 1;' % (table, rowid)
offset = db.execute(sql).fetchone()[0]
if offset:
# Get the rowid's that need to move to make way for the rows with rowid's < 1
sql = 'select %s, %s + ? from %s where %s > 0 order by %s desc;' % (rowid, rowid, table, rowid, rowid)
r = [(row[0], row[1]) for row in db.execute(sql, (offset,)) if row[0] != row[1]]
# Renumber all the rowid's that need renumbering
sql = 'update %s set %s = ? where %s = ?;' % (table, rowid, rowid)
for old, new in r:
db.execute(sql, (new, old))
# Get the new rowid for all the rows with rowids < 1
sql = 'select %s, row_number() over (order by %s) from %s where %s < 1 order by %s desc;' % (rowid, rowid, table, rowid, rowid)
r = [(row[0], row[1]) for row in db.execute(sql) if row[0] != row[1]]
# Renumber all the rowid's that need renumbering
sql = 'update %s set %s = ? where %s = ?;' % (table, rowid, rowid)
for old, new in r:
db.execute(sql, (new, old))
db.commit()
if __name__ == '__main__':
import apsw
db = apsw.Connection()
db.execute('create table x(id integer primary key, v integer not null);')
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()
RenumberRowids(db, 'x', 'id')
for row in db.execute('select * from x;'):
print(row)
print()
OpenRowid(db, 'x', 3, 'id')
OpenRowid(db, 'x', -4, 'id')
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()
RenumberRowids(db, 'x', 'id')
for row in db.execute('select * from x;'):
print(row)
print()
```
and here is another example that does the same thing using more direct SQL (ie, it is not programmatic but does place some more constraints against the validity of the table being renumbered.
```
def OpenRowid(db, table, rowid):
if not db.execute('select _rowid_ from %s where _rowid_ == ?;' % (table,), (rowid,)).fetchone():
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;
'''
```