25 lines
1.1 KiB
SQL
25 lines
1.1 KiB
SQL
-- migrate:up
|
|
|
|
-- A bike is normally held for its reservation's own period. These two columns
|
|
-- override it for one bike, which is how a conflict is resolved without moving
|
|
-- the whole booking: the reservation keeps its hours, and only the bike both
|
|
-- bookings want changes hands earlier.
|
|
--
|
|
-- NULL means "the reservation's period", so every existing row keeps behaving
|
|
-- exactly as before.
|
|
ALTER TABLE reservations_bikes
|
|
ADD COLUMN start_time timestamptz,
|
|
ADD COLUMN end_time timestamptz;
|
|
|
|
-- Both or neither, and the right way round
|
|
ALTER TABLE reservations_bikes ADD CONSTRAINT reservations_bikes_period CHECK (
|
|
(start_time IS NULL AND end_time IS NULL)
|
|
OR (start_time IS NOT NULL AND end_time IS NOT NULL AND start_time < end_time));
|
|
|
|
-- Conflicts are looked up by bike — "who else holds this one, and when" — which
|
|
-- `reservations_bikes_bike_id_idx` already serves: it was created with the
|
|
-- table, and the primary key, starting with reservation_id, could not.
|
|
|
|
-- migrate:down
|
|
ALTER TABLE reservations_bikes DROP CONSTRAINT reservations_bikes_period;
|
|
ALTER TABLE reservations_bikes DROP COLUMN start_time, DROP COLUMN end_time;
|