Полное условие и решение

Рабочий материал для самостоятельного использования. Скопируй текст в свой документ и адаптируй под свою задачу. Данные не отправляются.

Практикум: не потерять строки с одинаковым временем

Нужно показать ленту записей одного пользователя. Сортировка: created_at DESC, затем id DESC. Время — целая учебная отметка, не строка даты. Id уникален и положителен, время не NULL. Размер страницы по умолчанию 2; oracle допускает целое число от 1 до 50.

У пользователя A есть четыре записи: (created_at=10, id=100), (10, 99), (9, 98), (9, 97). У пользователя B — отдельная запись с id=200 и временем 11. Первая страница A должна содержать 100, 99; после cursor(10, 99) следующая — 98, 97. Запись B не входит в область доступа A.

Cursor содержит два поля t и id в base64url JSON. Отсутствующий cursor означает начало списка. Допустимы целые t от 0 и положительный id, оба в пределах 64-разрядного знакового целого; bool, null, отрицательное время и неизвестная форма отклоняются. Размер кодированной строки ограничен 256 символами. Допускается только каноническая base64url-строка без padding: алфавит A-Z, a-z, 0-9, дефис и подчёркивание; повторное кодирование исходных байтов должно дать ту же строку. Это маркер позиции, не подпись и не секрет.

Уточнения перед решением

Спроси, одинаковы ли времена у разных записей, возможны ли удаления и изменения порядка, нужна ли стабильная сессия просмотра. Уточни, кто задаёт user_id и обязан ли cursor ссылаться на существующую строку. Без этих ответов можно сделать синтаксически правильный запрос с неподходящим поведением.

В нашем условии cursor задаёт позицию и может пережить удаление строки, от которой был построен. Он не гарантирует неизменность всей ленты при вставках или изменении времени записи. Аутентификация вне модели; фильтр пользователя внутри каждого SQL-запроса обязателен.

Решение и полный SQL

Ответ интервьюеру:

Для убывающего порядка возьму строки с меньшим временем или с тем же временем и меньшим id. Эти два условия образуют одну границу, а пользовательский фильтр действует на обе части. Cursor сначала проверяется и декодируется, его поля передаются как данные. Порядок запроса совпадает с порядком ключей cursor.

SELECT id, created_at, title
FROM posts
WHERE user_id = :user
  AND (:has_cursor = 0 OR created_at < :t_before OR (created_at = :t_equal AND id < :cursor_id))
ORDER BY created_at DESC, id DESC
LIMIT :lim;

Без cursor has_cursor равен 0, поэтому позиционное ограничение не применяется; t_before, t_equal и cursor_id передаются как 0. Это не отменяет user_id. После (10, 99) возвращаются 98 и 97. Для дополнительной границы после (10, 100) должны вернуться 99 и 98. Если оставить только created_at<10, запись 99 исчезнет: сравнение даты пропустит все оставшиеся строки с тем же временем.

В SQL все маркеры имеют разные имена: has_cursor равен 0 для первой страницы и 1 для последующих; t_before и t_equal получают одно и то же время cursor, cursor_id — его id. Для первой страницы эти три числовых значения равны 0. Запрос исполнен только в SQLite. При переносе в PDO проверь целевой драйвер и типы: числовые параметры, включая lim, требуют явной целочисленной привязки через PDO::PARAM_INT. PDO::prepare ограничивает повторное использование одного именованного маркера без эмуляции; bindValue позволяет задать тип. Готовый PHP/PDO-прогон здесь не обещается.

Проверка альтернатив и изменений данных

СценарийОжидаемые id
Начало ленты A100, 99
После (10, 99)98, 97
После (10, 100)99, 98
После (9, 97)Пустая страница
Начало ленты B200
Новый элемент с временем 11 вставлен перед продолжением AПосле прежнего (10, 99) всё ещё 98, 97
Строка 99 удалена после выдачи cursorПосле (10, 99) всё ещё 98, 97

Удаление строки cursor не заставляет нас искать её заново: значения позиции уже сохранены. Однако изменение сортировочного ключа других записей может изменить последующие страницы. Мы не назовём эту модель snapshot isolation и не обещаем отсутствие любых пропусков при произвольном редактировании.

Что фактически проверено

Check.py читает начальный CSV, создаёт SQLite в памяти и исполняет приложенный query.sql с параметрами. Проверяются перечисленные страницы, отдельная область пользователя B, вставка перед позицией, удаление строки cursor, неверная форма/тип/размер cursor, ошибочный limit. Дубликат id и NULL времени дополнительно отвергаются ограничениями самой таблицы.

Строковое значение principal, похожее на SQL-инъекцию, передаётся параметром и не меняет структуру запроса. Это проверка связывания значения, а не демонстрация полноценной аутентификации. В реальном PHP-коде понадобятся свои интеграционные тесты PDO и выбранного способа передачи параметров.

Команда python3 check.py --negative намеренно убирает вторую часть позиционного предиката. Она обнаруживает 98, 97 вместо 99, 98 и завершается с кодом 2. Обычный запуск завершается 0; те же исходы сохранены при PYTHONOPTIMIZE=1, потому что проверки реализованы явными условиями.

Заверши ответ границами: «Для заданного порядка я сохраняю одинаковые времена через второй ключ, не смешиваю cursor и право доступа и проверяю ошибочные входы. Производительность и PHP/PDO-поведение подтвержу отдельным запуском в согласованной среде». Затем спроси о настоящем списке в продукте: насколько часто меняются записи, нужна ли стабильность выдачи и какой объём данных приходится на пользователя.