🚀 go-pugleaf

As always I recommend to use SQL-implementd linked list.

You can add additional cloumn but not with integer to sort by but a pointer
to the next record.

Say you have
CREATE TABLE LINKED_LIST(
  ID INTEGER PRIMARY KEY,
  NAME VARCHAR(10),
  NEXT_ID INTEGER REFERENCES LINKED_LIST(ID)
)

Now to reorder your dataset you can change just one record.

To get data from the list you can use client-side proceudre or a stored
procedure.

Something like:

CREATE PRPCEDURE GET_LINKED_LIST_IN_ORDER
RETURNS (
  ID INTEGER
  NAME VARCHAR(10),
  NEXT_ID INTEGER
)
as
begin
  select id, name, next_id
  from LINKED_LIST
  where id is null /* start of the list */
  into :id, :name, :next_id;
  suspend;

  while (next_id is not null) do begin
    select id, name, next_id
    from LINKED_LIST
    where id = :next_id
    into :id, :name, :next_id;

    suspend;
  end
end

And you need to lock entier table to avoid missing chains or loops in your
list in a multi-user invironment.

Another optimization would be to separate ordering from the main data.
LINKED_LIST could containg only ID and NEXT_ID for the main table and the
procedure could combine them together. If you add to the LINKED_LIST a field
like USER_ID you could get multiple versions of ordering for different users
or situations without changing the main table.

But it works only if reordering is often. In other case it's better to use
sorting column.

Maxim.