Order in your database: a move applied in one transaction, positions for new rows, a list's keys spread out again, and the order to read it in. The same on Postgres, MySQL and SQLite.

Each row keeps a position key (Narabe.Position, a column made with Narabe.Migration.position/2) among the rows of its scope: its list, its board's column, or its parent in a tree. A move writes the moved rows only: their key, and their column or parent when that changes. No other row is renumbered, so a move costs the same in a list of ten or of a million, and two people moving at once do not touch each other's rows.

Declare the order once, on the schema (Narabe.Ordered):

schema "cards" do
  field :title, :string
  field :position, :string
  belongs_to :column, Column
  belongs_to :board, Board
end

use Narabe.Ordered, group: :column_id

then apply a move from the browser:

def handle_event("moved", params, socket) do
  board = socket.assigns.board

  result =
    Narabe.Ecto.move(Repo, where(Card, board_id: ^board.id), Narabe.Move.parse!(params, Card),
      serialize: where(Board, id: ^board.id)
    )

  {:reply, Narabe.Live.reply(result), reload_cards(socket)}
end

The queryable is the whole of what may be moved (one board's cards): a move of anything outside it, or into a parent outside it, is refused as :not_found.

Options

Each option may be given to the call, or once in use Narabe.Ordered, but :serialize, which names the row of one call.

• :group - the field of the scope that a move may change: a board's :column_id, a tree's :parent_id. Without it, the queryable is one list.
• :tree - true when :group refers to the same table (a parent): a move into the item itself or its descendants is refused, and the rules' :max_depth counts the levels.
• :position - the position field (:position).
• :rules - Narabe.Rules, or its options; a tree's default nests.
• :kind - the field of an item's kind, for the rules' :accepts and :leaves; :group_kind, the kind of a board's columns ("column").
• :serialize - a queryable of the row that owns the list (the board, the playlist's user), locked (FOR UPDATE) before the move reads anything: moves in one list then run one after the other, and two drops into the same gap at the same moment get different keys. Without it, they may get the same key, which ordered/2 orders by id.
• :lock - lock the moved rows as they are read (FOR UPDATE); the default on Postgres and MySQL. SQLite locks the database as it writes, and has no row locks.
• :rebalance_at - when a key in the scope grows past this length, the scope's keys are written anew, evenly (32).

Where it lands

Right after move.after, among the rows of the new scope as they are now; else right before move.before; else at move.index; else at the end. Rows without a key yet (a table that had no positions) are given keys in their current order (by key, then id) as a move first touches their scope.

Trees

A tree is rows with a parent (group: :parent_id, tree: true). Moving an item moves its subtree with it (its children keep their parent). Narabe reads a tree's ancestors and descendants with one recursive query each (WITH RECURSIVE, on all three databases), however deep: a move checks the new parent's ancestors (never into itself), and, only when the rules have a :max_depth, the levels under the moved items. MySQL stops a recursive query at 1,000 levels (cte_max_recursion_depth).

Errors

• :not_found - an item, or the new parent, is not in the queryable.
• :into_itself, :not_accepted, :too_deep - the rules (Narabe.Rules).