У меня есть таблица в базе данных, которая отвечает за хранение упорядоченных/переупорядочиваемых списков. Он имеет следующую форму:
| id | listId | index | title | ... |
где id — первичный ключ, listId — внешний ключ, определяющий, к какому списку принадлежит элемент, title, а другие столбцы — содержимое элементов. Свойство index отвечает за позицию элемента в списке. Это целочисленный счетчик (начиная с 0), который уникален в рамках списка, но может повторяться в разных списках. Пример данных:
| id | listId | index | title | ...
---------------------------------------------
| "item1" | "list1" | 0 | "title1" | ...
| "item2" | "list1" | 1 | "title2" | ...
| "item3" | "list1" | 2 | "title3" | ...
| "item4" | "list2" | 0 | "title4" | ...
| "item5" | "list2" | 1 | "title5" | ...
Пользователи могут создавать/удалять элементы, перемещать их внутри списка или между списками. Чтобы обеспечить согласованность индексов при выполнении этих операций, я делаю следующее:
Создать элемент:
- Подсчитать элементы в этом списке
SELECT COUNT(DISTINCT "Item"."id") as "cnt"
FROM "item" "Item"
WHERE "Item"."listId" = ${listId}
- Вставьте новый элемент с индексом, установленным для подсчета с шага 1:
INSERT INTO "item"("id", "listId", "index", "title", ...)
VALUES (${id}, ${listId}, ${count}, ${title})
Таким образом, индекс растет с каждым элементом, вставленным в список.
Переместить элемент:
- Получить текущий listId и индекс элемента:
SELECT "Item"."listId" AS "Item_listId", "Item"."index" AS "Item_index"
FROM "item" "Item"
WHERE "Item"."id" = ${id}
- При необходимости измените индекс «смещенных» элементов, чтобы порядок был последовательным, например. если элемент перемещается вперед, все элементы между его текущей позицией (исключительно) и его следующей позицией (включительно) должны уменьшить свой индекс на 1:
UPDATE "item"
SET "index" = "index" - 1
WHERE "listId" = ${listId}
AND "index" BETWEEN ${sourceIndex + 1} AND ${destinationIndex}
Вариант с перемещением по спискам я опущу, потому что он очень похож.
- Обновите сам элемент:
UPDATE "item"
SET "index" = ${destinationIndex}
WHERE "id" = ${id}
Удалить элемент:
Получить индекс элемента и listId
Переместите все элементы в том же списке, которые находятся рядом с этим элементом, на 1 шаг назад, чтобы убрать пробел
UPDATE "item"
SET "index" = "index" - 1
WHERE "listId" = ${listId}
AND "index" > ${itemIndex}
- Удалить пункт:
DELETE FROM "item"
WHERE "id" = ${id}
Вопрос:
Какие уровни изоляции транзакций я должен предоставить для каждой из этих операций? Для меня очень важно, чтобы столбец индекса был последовательным, без пробелов и, самое главное, без дубликатов. Правильно ли я понимаю, что операция создать элемент подлежит фантомному чтению, потому что она подсчитывает элементы по некоторым критериям и должна быть сериализуема? А другие операции?
(ListID, Index). Ограничение уникального индекса несложно реализовать, и оно всегда будет выполняться. - person Vladimir Baranov schedule 29.07.2019