list

#!/usr/bin/env bash

set -Eeuo pipefail
IFS=$'\n\t'

db=$(mktemp -t "list.sqlite3")

print_list() {
  sqlite3 -column -header "$db" '
    select *
    from list
    order by position asc
           , generation desc;
  '
}

move_item() {
  sqlite3 "$db" "
    update list
    set position = $2
      , generation = (
          -- TODO: Do better than a naive full table scan.
          select max(generation) + 1
          from list
        )
    where id = $1;
  "
  echo "moved item with id=$1 to position=$2"
}

main() {
  sqlite3 "$db" '
    create table list (
      id integer primary key,
      value text not null,
      position integer not null,
      generation text not null default 0
    ) strict;

    insert into list (id, position, value) values
      (1, 1, "one"),
      (2, 2, "two"),
      (3, 3, "three"),
      (4, 4, "four"),
      (5, 5, "five");
  '

  print_list

  move_item 5 0
  move_item 4 1
  move_item 3 1 # 3 takes 4's spot, pushing it down

  print_list
}

main

output.txt

$ ./list
id  value  position  generation
--  -----  --------  ----------
1   one    1         0
2   two    2         0
3   three  3         0
4   four   4         0
5   five   5         0
moved item with id=5 to position=0
moved item with id=4 to position=1
moved item with id=3 to position=1
id  value  position  generation
--  -----  --------  ----------
5   five   0         1
3   three  1         3
4   four   1         2
1   one    1         0
2   two    2         0