SQLite Forum

How can I maintain a list in an arbitrary order?
Login
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.