Function compactPositions

apps/sim/lib/table/rows/ordering.ts:147–175

(
  trx: DbTransaction,
  tableId: string,
  minDeletedPos?: number
)

 * the cheaper {@link shiftRowsDownAfter}.
 */
export async function compactPositions(
  trx: DbTransaction,
  tableId: string,
  minDeletedPos?: number
) {
  if (minDeletedPos === undefined) {
    await trx.execute(sql`
      UPDATE user_table_rows t
      SET position = r.new_pos
      FROM (
        SELECT id, ROW_NUMBER() OVER (ORDER BY position) - 1 AS new_pos
        FROM user_table_rows
        WHERE table_id = ${tableId}
      ) r
      WHERE t.id = r.id AND t.table_id = ${tableId} AND t.position != r.new_pos
    `)
    return
  }
  await trx.execute(sql`
    UPDATE user_table_rows t
    SET position = r.new_pos
    FROM (
      SELECT id, ${minDeletedPos}::int + ROW_NUMBER() OVER (ORDER BY position) - 1 AS new_pos
      FROM user_table_rows
      WHERE table_id = ${tableId} AND position >= ${minDeletedPos}
    ) r
    WHERE t.id = r.id AND t.table_id = ${tableId} AND t.position != r.new_pos
  `)
}

/**
 * Computes the fractional `order_key` for a row inserted at the integer