Як реалізувати точне бронювання в системах бронювання?
Готельне бронювання
У PostgreSQL обмеження виключення (exclusion constraints) дозволяють забезпечити унікальність певних комбінацій значень стовпців для кожного рядка. Це корисно, коли потрібно запобігти конфліктуючим або перекриваючим даним, навіть якщо окремі стовпці можуть містити дублікати.
Обмеження виключення працює шляхом визначення, що два рядки не можуть існувати одночасно, якщо вони відповідають певному набору умов. Це зазвичай досягається за допомогою індексу GiST (Generalized Search Tree) або GIN (Generalized Inverted Index).
Приклади використання Exclusion Constraints:
- Запобігання перекриттю діапазону дат: Запобігання перекриттю двох бронювань в часі.
- Забезпечення геометричних обмежень: Переконатися, що геометричні фігури не перетинаються.
- Запобігання конфлікту значень у складних типах даних: Наприклад, забезпечити, щоб два студенти не отримали одне й те ж завдання.
Давайте створимо таблицю бронювань і подивимося, як можна запобігти перекриттю бронювань.
Приклад: Бронювання кімнат
CREATE TABLE reservations
(
id serial PRIMARY KEY,
room_id INTEGER,
booking_status TEXT,
start_date TIMESTAMP,
end_date TIMESTAMP,
EXCLUDE USING GIST (TSRANGE(start_date, end_date) WITH &&)
);
Пояснення:
Клаузула EXCLUDE USING GIST створює обмеження виключення (exclusion constraint), яке гарантує, що не буде конфліктуючих бронювань для одного й того ж діапазону дат. Умова TSRANGE(start_date, end_date) WITH && перевіряє на перекриття діапазонів дат.
Вставка бронювання
Спробуємо забронювати кімнату на наш наступний відпочинок:
INSERT INTO reservations (room_id, booking_status, start_date, end_date)
VALUES (1, 'CONFIRMED', '2025–01–08 14:00:00', '2025–01–15 12:00:00');
Це працює нормально, і ми маємо перше бронювання:
+--+-------+--------------+-------------------+--------------------------+
|id|room_id|booking_status|start_date |end_date |
+--+-------+--------------+-------------------+--------------------------+
|1 |1 |CONFIRMED |2025-01-08 14:00:00|2025-01-15 12:00:00 |
+--+-------+--------------+-------------------+--------------------------+
Спроба вставити перекриваюче бронювання
Тепер спробуємо забронювати ту саму кімнату на перекриваючі дати:
INSERT INTO reservations (room_id, booking_status, start_date, end_date)
VALUES (1, 'CONFIRMED', '2025–01–12 14:00:00', '2025–01–18 12:00:00');
Ми отримуємо помилку:
ERROR: conflicting key value violates exclusion constraint "reservations_tsrange_excl"
DETAIL: Key (tsrange(start_date, end_date)) = (["2025–01–12 14:00:00", "2025–01–18 12:00:00")) conflicts with existing key (tsrange(start_date, end_date)) = (["2025–01–08 14:00:00", "2025–01–15 12:00:00")).
Ця поведінка гарантує, що перекриваючі бронювання не можуть бути вставлені, так само, як ви не можете забронювати кімнату, яка вже зайнята.
Бронювання іншої кімнати на той самий період
Тепер спробуємо забронювати іншу кімнату на той самий період:
INSERT INTO reservations (room_id, booking_status, start_date, end_date)
VALUES (2, 'CONFIRMED', '2025–01–12 14:00:00', '2025–01–18 12:00:00');
Ой! Ми отримуємо ту ж саму помилку:
ERROR: conflicting key value violates exclusion constraint "reservations_tsrange_excl"
DETAIL: Key (tsrange(start_date, end_date)) = (["2025–01–12 14:00:00", "2025–01–18 12:00:00")) conflicts with existing key (tsrange(start_date, end_date)) = (["2025–01–08 14:00:00", "2025–01–15 12:00:00")).
Проблема
Exclusion constraint наразі застосовується до всієї таблиці, тобто перевірка на перекриття діапазонів дат виконується серед усіх кімнат. Насправді ж ми хочемо дозволити бронювання в різних кімнатах на той самий період.
Щоб виправити це, нам потрібно змінити наше обмеження виключення.
Рішення: Додати room_id до обмеження виключення
Додавши room_id до умови виключення, ми забезпечуємо, що обмеження буде застосовуватися лише до однієї і тієї ж кімнати.
-- Увімкнення розширення btree_gist (якщо воно ще не увімкнене)
CREATE EXTENSION IF NOT EXISTS btree_gist;
-- Оновлене визначення таблиці з room_id в обмеженні виключення
CREATE TABLE reservations
(
id serial PRIMARY KEY,
room_id INTEGER,
booking_status TEXT,
start_date TIMESTAMP,
end_date TIMESTAMP,
EXCLUDE USING GIST (room_id WITH =, TSRANGE(start_date, end_date) WITH &&)
);
Тепер ми можемо забронювати дві кімнати на один і той самий період:
- Бронювання двох кімнат на той самий період
INSERT INTO reservations (room_id, booking_status, start_date, end_date)
VALUES (1, 'CONFIRMED', '2025–01–12 14:00:00', '2025–01–18 12:00:00');
INSERT INTO reservations (room_id, booking_status, start_date, end_date)
VALUES (2, 'CONFIRMED', '2025–01–12 14:00:00', '2025–01–18 12:00:00');
Результат
Таблиця тепер дозволяє перекриваючі бронювання в різних кімнатах:
+--+-------+--------------+-------------------+--------------------------+
|id|room_id|booking_status|start_date |end_date |
+--+-------+--------------+-------------------+--------------------------+
|1 |1 |CONFIRMED |2025-01-08 14:00:00|2025-01-15 12:00:00 |
+--+-------+--------------+-------------------+--------------------------+
|2 |2 |CONFIRMED |2025-01-08 14:00:00|2025-01-15 12:00:00 |
+--+-------+--------------+-------------------+--------------------------+
Часткові обмеження виключення
Тепер давайте розглянемо, що відбувається, коли бронювання скасовується. Статус CANCELED означає, що кімната більше не зайнята і може бути заброньована знову.
- Бронювання кімнати
INSERT INTO reservations (room_id, booking_status, start_date, end_date)
VALUES (1, 'CONFIRMED', '2025–01–12 14:00:00', '2025–01–18 12:00:00');
- Скасування бронювання
UPDATE reservations SET booking_status = 'CANCELED' WHERE room_id = 1;
На цей момент бронювання для кімнати 1 було скасоване:
+--+-------+--------------+-------------------+--------------------------+
|id|room_id|booking_status|start_date |end_date |
+--+-------+--------------+-------------------+--------------------------+
|1 |1 |CANCELED |2025-01-08 14:00:00|2025-01-15 12:00:00 |
+--+-------+--------------+-------------------+--------------------------+
Спроба забронювати скасовану кімнату знову
Тепер ми можемо вставити нове бронювання для кімнати 1 на той самий період:
- Спроба забронювати ту саму кімнату та період знову
INSERT INTO reservations (room_id, booking_status, start_date, end_date)
VALUES (1, 'CONFIRMED', '2025–01–12 14:00:00', '2025–01–18 12:00:00');
Ой! Ми знову отримуємо помилку.
ERROR: conflicting key value violates exclusion constraint "reservations_room_id_tsrange_excl" Detail: Key (room_id, tsrange(start_date, end_date))=(1, ["2025-01-12 14:00:00","2025-01-18 12:00:00")) conflicts with existing key (room_id, tsrange(start_date, end_date))=(1, ["2025-01-12 14:00:00","2025-01-18 12:00:00")).
Щоб вирішити цю помилку, вступають у гру часткові обмеження виключення.
Щоб виправити це, ми можемо визначити часткове обмеження виключення (partial exclusion constraint), яке буде застосовуватися тільки до рядків, що не мають статусу "CANCELED".
Визначення часткового обмеження виключення
Часткове обмеження виключення (partial exclusion constraint) — це обмеження (constraint), яке застосовується лише до підмножини рядків таблиці на основі певної умови.
Це корисно, коли потрібно застосовувати умови виключення тільки за певних обставин.
У нашому випадку ми застосовуємо умову тільки тоді, коли бронювання не скасовано.
Ось як можна визначити Часткове обмеження виключення.
EXCLUDE USING GIST (room_id WITH =,TSRANGE(start_date, end_date) WITH &&)
WHERE (booking_status != 'CANCELED')
-- Додано додаткову перевірку в WHERE().
Повне визначення таблиці:
CREATE TABLE reservations
(
id serial primary key,
room_id INTEGER,
booking_status TEXT,
start_date TIMESTAMP,
end_date TIMESTAMP,
EXCLUDE USING GIST (room_id WITH =,TSRANGE(start_date, end_date) WITH &&) WHERE (booking_status != 'CANCELED')
);
Тестування часткового обмеження виключення
INSERT INTO reservations (room_id, booking_status, start_date, end_date)
VALUES (1, 'CONFIRMED', '2025-01-12 14:00:00', '2025-01-18 12:00:00');
INSERT INTO reservations (room_id, booking_status, start_date, end_date)
VALUES (2, 'CONFIRMED', '2025-01-12 14:00:00', '2025-01-18 12:00:00');
-- Скасувати бронювання для room_id = 2
UPDATE reservations SET booking_status = 'CANCELED' WHERE room_id = 2;
-- Вставка нового бронювання для того ж room_id і періоду
INSERT INTO reservations (room_id, booking_status, start_date, end_date)
VALUES (2, 'CONFIRMED', '2025-01-12 14:00:00', '2025-01-18 12:00:00');
SELECT *
FROM reservations;
Тепер усе працює, як і очікувалося!
+--+-------+--------------+-------------------+--------------------------+
|id|room_id|booking_status|start_date |end_date |
+--+-------+--------------+-------------------+--------------------------+
|1 |1 |CONFIRMED |2025-01-08 14:00:00|2025-01-15 12:00:00 |
+--+-------+--------------+-------------------+--------------------------+
|2 |2 |CANCELED |2025-01-08 14:00:00|2025-01-15 12:00:00 |
+--+-------+--------------+-------------------+--------------------------+
|3 |2 |CONFIRMED |2025-01-08 14:00:00|2025-01-15 12:00:00 |
+--+-------+--------------+-------------------+--------------------------+
Висновок
Використовуючи обмеження виключення в PostgreSQL, ми можемо забезпечити виконання правильних бізнес-правил безпосередньо в базі даних, гарантуючи, що:
- Не відбудеться перекриття бронювань для однієї кімнати.
- Перекриття бронювань можна дозволити для різних кімнат.
- Скасовані бронювання можна використовувати знову без порушення обмежень.
Швидкий підсумок:
- Обмеження виключення: Запобігають конфліктам, таким як перекриття даних у таблицях.
- Часткові обмеження виключення: Застосовують логіку виключення лише до підмножини рядків, на основі умови (такої як booking_status != 'CANCELED').
На завершення, обмеження виключення є потужним інструментом для забезпечення цілісності даних і виконання складної бізнес-логіки. Вони дозволяють підтримувати узгодженість, обробляючи реальні сценарії, такі як бронювання кімнат в готелях або системах планування.
Якщо вам сподобалася ця стаття, ось ще кілька моїх публікацій:
- Забезпечення цілісності даних за допомогою обмежень PostgreSQL
- Типи доменів та ENUM в PostgreSQL: Забезпечення цілісності даних
- Еволюція первинних ключів в PostgreSQL: від Serial до Identity
Поставте лайк і підпишіться для отримання нових оновлень. Успіхів у програмуванні! 🧑💻👩💻👨💻
Перекладено з: Exclusion Constraints: