CREATE DATABASE hw;
CREATE EXTENSION pgcrypto;

Statements

В базе данных Airline информация о рейсах самолётов задана в виде таблиц

Flights(
    FlightId integer, 
    FlightTime timestamp, 
    PlaneId integer,
    -- Дополнительные столбцы, при необходимости
)
Seats(
    PlaneId integer,
    SeatNo varchar(4), -- 123A
    -- Дополнительные столбцы, при необходимости
)

Реализуйте запросы к базе данных Airline с применением представлений, хранимых процедур и функций. При необходимости, вы можете создать дополнительные таблицы, представления и хранимые процедуры.

Возможность бронирования должна автоматически отключаться за трое суток до начала рейса. Продажа мест должна автоматически отключаться за три часа до начала рейса. Также должна быть предусмотрена возможность отключения бронирования и продаж вручную.

FreeSeats(FlightId) — список мест, доступных для продажи и для бронирования.
Reserve(UserId, Pass, FlightId, SeatNo) — пытается забронировать место на трое суток начиная с момента бронирования. Возвращает истину, если удалось и ложь — в противном случае.
ExtendReservation(UserId, Pass, FlightId, SeatNo) — пытается продлить бронь места на трое суток начиная с момента продления. Возвращает истину, если удалось и ложь — в противном случае.
BuyFree(FlightId, SeatNo) — пытается купить свободное место. Возвращает истину, если удалось и ложь — в противном случае.
BuyReserved(UserId, Pass, FlightId, SeatNo) — пытается выкупить забронированное место (пользователи должны совпадать). Возвращает истину, если удалось и ложь — в противном случае.
FlightsStatistics(UserId, Pass) — статистика по рейсам: возможность бронирования и покупки, число свободных, забронированных и проданных мест.
FlightStat(UserId, Pass, FlightId) — статистика по рейсу: возможность бронирования и покупки, число свободных, забронированных и проданных мест.
CompressSeats(FlightId) — оптимизирует занятость мест в самолете. В результате оптимизации, в начале самолета должны быть купленные места, затем — забронированные, а в конце — свободные. Примечание: клиенты, которые уже выкупили билеты, также должны быть пересажены.

0.1

PostgreSQL 14.5

0.2

DROP TABLE IF EXISTS Flights CASCADE;
DROP TABLE IF EXISTS Seats CASCADE;
DROP TABLE IF EXISTS Tickets CASCADE;
DROP TABLE IF EXISTS Users CASCADE;

-- Список рейсов
CREATE TABLE Flights (
       FlightId int GENERATED ALWAYS AS IDENTITY not null,
       FlightTime TIMESTAMP not null,
       PlaneId int not null,

       PRIMARY KEY (FlightId)
);

-- Список мест
CREATE TABLE Seats (
       PlaneId int not null,
       SeatNo VARCHAR(4) not null,

       PRIMARY KEY (PlaneId, SeatNo)
);

-- Описание пользователей: Идентификатор и захещированный пароль
CREATE TABLE Users (
       UserId int GENERATED ALWAYS AS IDENTITY not null,
       Pass TEXT not null,

       PRIMARY KEY (UserId)
);

-- Купленные и забронированные места. Если место забронированно то
-- BookedUntil указано время когда бронь слетает, а также указан
-- пользователь взявший бронь. Если билет на место куплен то Taken =
-- TRUE.
CREATE TABLE Tickets (
       SeatNo VARCHAR(4) not null,
       PlaneId int not null,
       FlightId int not null,
       BookedUntil TIMESTAMP,
       Taken bool not null,
       UserId int,

       PRIMARY KEY (SeatNo, PlaneId, FlightId) DEFERRABLE,

       FOREIGN KEY (FlightId)
       REFERENCES Flights(FlightId),

       FOREIGN KEY (PlaneId, SeatNo)
       REFERENCES Seats(PlaneId, SeatNo),

       FOREIGN KEY (UserId)
       REFERENCES Users(UserId)
);

-- Все места актуальных рейсов
CREATE OR REPLACE VIEW AllSeats AS
SELECT Flights.FlightId, Seats.PlaneId, Seats.SeatNo, Tickets.Taken, Tickets.BookedUntil, Tickets.UserId
FROM Flights
     NATURAL JOIN Seats
     NATURAL LEFT JOIN Tickets
WHERE Flights.FlightTime > NOW();

-- Все не купленные места актуальных рейсов
CREATE OR REPLACE VIEW FreeOrReservedTickets AS
SELECT FlightId, PlaneId, SeatNo, BookedUntil, UserId
FROM AllSeats
WHERE AllSeats.Taken IS NULL
      OR NOT AllSeats.Taken;


-- Все места актуальных рейсов
CREATE OR REPLACE VIEW FreeSeatsView AS
SELECT FlightId, PlaneId, SeatNo, BookedUntil, UserId
FROM FreeOrReservedTickets
WHERE BookedUntil IS NULL
      OR BookedUntil < NOW();

Fill

INSERT INTO Flights(FlightTime, PlaneId) VALUES
       ('2022-11-26 18:00:00', 0),
       ('2022-12-03 18:00:00', 0),
       ('2022-11-24 10:00:00', 1),
       ('2022-11-26 10:00:00', 1),
       ('2022-11-28 10:00:00', 2);

INSERT INTO Seats(PlaneId, SeatNo) VALUES
       (0, '001A'),
       (0, '001B'),
       (0, '002A'),
       (0, '002B'),
       (0, '003A'),
       (0, '003B'),
       (1, '001A'),
       (1, '001B'),
       (1, '001C'),
       (1, '002A'),
       (1, '002B'),
       (1, '002C'),
       (2, '0001'),
       (2, '0002'),
       (2, '0003'),
       (2, '0004');

INSERT INTO Users(Pass) VALUES
       (CRYPT('qwerty', GEN_SALT('bf'))),
       (CRYPT('123456', GEN_SALT('bf'))),
       (CRYPT('amogus', GEN_SALT('bf')));

INSERT INTO Tickets(SeatNo, PlaneId, FlightId, BookedUntil, Taken, UserId) VALUES
       ('001A', 0, 1, NULL, TRUE, 1),
       ('001B', 0, 1, '2022-11-28 17:00:00', FALSE, 2),
       ('001A', 0, 2, '2022-12-03 17:00:00', FALSE, 3),
       ('002A', 0, 2, NULL, TRUE, NULL),
       ('001B', 0, 2, NULL, FALSE, 2);

View

SELECT * FROM Flights;

SELECT * FROM Users;

SELECT * FROM Tickets;

0.3

1

CREATE OR REPLACE FUNCTION FreeSeats(_FlightId int)
RETURNS TABLE (
    SeatNo VARCHAR(4)
)
LANGUAGE plpgsql
AS $$
BEGIN
   RETURN QUERY (
         SELECT FreeSeatsView.SeatNo
      FROM FreeSeatsView
      WHERE FlightId = _FlightId
   );
END;
$$

SELECT * FROM FreeSeats(2);

2

-- Проверка что пользователь сущесвует и пароль корректный
CREATE OR REPLACE FUNCTION Authenticate(UserId INT, Pass TEXT)
RETURNS BOOLEAN
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN EXISTS (
           SELECT *
           FROM Users
           WHERE Users.UserId = Authenticate.UserId
                    AND Users.Pass = CRYPT(Authenticate.Pass, Users.Pass)
    );
END;
$$;

-- Проверка что место не забронированно и не куплено
CREATE OR REPLACE FUNCTION IsFreeSeat(_FlightId INT, _SeatNo VARCHAR(40))
RETURNS BOOLEAN
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN _SeatNo IN (
           SELECT FreeSeatsView.SeatNo
           FROM FreeSeatsView
           WHERE FlightId = _FlightId
    );
END;
$$;

SELECT * FROM Authenticate(1, 'qwerty');

SELECT * FROM IsFreeSeat(2, '002A');

CREATE OR REPLACE FUNCTION Reserve(_UserId INT, _Pass TEXT, _FlightId INT, _SeatNo VARCHAR(4))
RETURNS BOOLEAN
LANGUAGE plpgsql
AS $$
DECLARE _PlaneId INT = (
    SELECT PlaneId
    FROM Flights
    WHERE FlightId = _FlightId
);
DECLARE _FlightTime TIMESTAMP = (
    SELECT FlightTime
    FROM Flights
    WHERE FlightId = _FlightId
);
BEGIN
    IF _PlaneId IS NULL OR _FlightTime IS NULL THEN
       RETURN FALSE;
    END IF;

    IF NOT Authenticate(_UserId, _Pass) THEN
       RETURN FALSE;
    END IF;

    IF _FlightTime < NOW() THEN
       RETURN FALSE;
    END IF;

    IF NOT IsFreeSeat(_FlightId, _SeatNo) THEN
       RETURN FALSE;
    END IF;

    INSERT INTO Tickets(SeatNo, PlaneId, FlightId, BookedUntil, Taken, UserId) VALUES
           (_SeatNo, _PlaneId, _FlightId, NOW() + INTERVAL '3' DAY, FALSE, _UserId)
    ON CONFLICT(SeatNo, PlaneId, FlightId) DO UPDATE
       SET UserId = _UserId,
           BookedUntil = NOW() + INTERVAL '3' DAY,
           Taken = FALSE;

    RETURN TRUE;
END;
$$

SELECT * FROM Reserve(3, 'amogus', 5, '0003');

3

CREATE OR REPLACE FUNCTION ExtendReservation(_UserId INT, _Pass TEXT, _FlightId INT, _SeatNo VARCHAR(4))
RETURNS BOOLEAN
LANGUAGE plpgsql
AS $$
DECLARE _PlaneId INT = (
    SELECT PlaneId
    FROM Flights
    WHERE FlightId = _FlightId
);
DECLARE _FlightTime TIMESTAMP = (
    SELECT FlightTime
    FROM Flights
    WHERE FlightId = _FlightId
);
DECLARE _ReservedByUser INT = (
    SELECT UserId
    FROM Tickets
    WHERE PlaneId = _PlaneId
          AND FlightId = _FlightId
          AND SeatNo = _SeatNo
);
BEGIN
    IF _PlaneId IS NULL OR _FlightTime IS NULL OR _ReservedByUser IS NULL THEN
       RETURN FALSE;
    END IF;

    IF NOT Authenticate(_UserId, _Pass) THEN
       RETURN FALSE;
    END IF;

    IF _UserId != _ReservedByUser THEN
       RETURN FALSE;
    END IF;

    IF _FlightTime < NOW() THEN
       RETURN FALSE;
    END IF;

    UPDATE Tickets
    SET BookedUntil = NOW() + INTERVAL '3' DAY
    WHERE PlaneId = _PlaneId
          AND FlightId = _FlightId
          AND SeatNo = _SeatNo;

    RETURN TRUE;
END;
$$

SELECT * FROM ExtendReservation(1, 'qwerty', 2, '001B');

4

CREATE OR REPLACE FUNCTION BuyFree(_FlightId INT, _SeatNo VARCHAR(4))
RETURNS BOOLEAN
LANGUAGE plpgsql
AS $$
DECLARE _PlaneId INT = (
    SELECT PlaneId
    FROM Flights
    WHERE FlightId = _FlightId
);
DECLARE _FlightTime TIMESTAMP = (
    SELECT FlightTime
    FROM Flights
    WHERE FlightId = _FlightId
);
BEGIN
    IF _PlaneId IS NULL OR _FlightTime IS NULL THEN
       RETURN FALSE;
    END IF;

    IF _FlightTime < NOW() THEN
       RETURN FALSE;
    END IF;

    IF NOT IsFreeSeat(_FlightId, _SeatNo) THEN
       RETURN FALSE;
    END IF;

    INSERT INTO Tickets(SeatNo, PlaneId, FlightId, BookedUntil, Taken, UserId) VALUES
           (_SeatNo, _PlaneId, _FlightId, NULL, TRUE, NULL)
    ON CONFLICT(SeatNo, PlaneId, FlightId) DO UPDATE
       SET UserId = NULL,
           BookedUntil = NULL,
           Taken = TRUE;

    RETURN TRUE;
END;
$$

SELECT * FROM BuyFree(2, '002B');

5

CREATE OR REPLACE FUNCTION BuyReserved(_UserId INT, _Pass TEXT, _FlightId INT, _SeatNo VARCHAR(4))
RETURNS BOOLEAN
LANGUAGE plpgsql
AS $$
DECLARE _PlaneId INT = (
    SELECT PlaneId
    FROM Flights
    WHERE FlightId = _FlightId
);
DECLARE _FlightTime TIMESTAMP = (
    SELECT FlightTime
    FROM Flights
    WHERE FlightId = _FlightId
);
DECLARE _ReservedByUser INT = (
    SELECT UserId
    FROM Tickets
    WHERE PlaneId = _PlaneId
          AND FlightId = _FlightId
          AND SeatNo = _SeatNo
);
BEGIN
    IF _PlaneId IS NULL OR _FlightTime IS NULL OR _ReservedByUser IS NULL THEN
       RETURN FALSE;
    END IF;

    IF NOT Authenticate(_UserId, _Pass) THEN
       RETURN FALSE;
    END IF;

    IF _UserId != _ReservedByUser THEN
       RETURN FALSE;
    END IF;

    IF _FlightTime < NOW() THEN
       RETURN FALSE;
    END IF;

    UPDATE Tickets
    SET BookedUntil = NULL,
        TAKEN = TRUE
    WHERE PlaneId = _PlaneId
          AND FlightId = _FlightId
          AND SeatNo = _SeatNo;

    RETURN TRUE;
END;
$$

SELECT * FROM BuyReserved(2, '123456', 5, '0001');

6

-- Статистика по местам которые можно забронировать
CREATE OR REPLACE VIEW StatCanReserve AS
SELECT FlightId, COUNT(*) AS CanReserve
FROM FreeOrReservedTickets
WHERE BookedUntil IS NULL
      OR BookedUntil < NOW()
GROUP BY FlightId;

-- Статистика по местам которые может купить пользователь
CREATE OR REPLACE FUNCTION StatCanBuy(_UserId INT)
RETURNS TABLE (
    FlightId INT,
    CanBuy INT
)
LANGUAGE plpgsql
AS $$
BEGIN
   RETURN QUERY (
         SELECT FreeOrReservedTickets.FlightId,
           CAST(COUNT(*) AS INT) AS CanBuy
      FROM FreeOrReservedTickets
      WHERE BookedUntil IS NULL
        OR BookedUntil < NOW()
        OR (
           BookedUntil > NOW()
           AND UserId = _UserId
        )
      GROUP BY FreeOrReservedTickets.FlightId
   );
END;
$$;


-- Статистика по свободным местам
CREATE OR REPLACE VIEW StatFree AS
SELECT FlightId, COUNT(*) AS Free
FROM FreeOrReservedTickets
WHERE BookedUntil IS NULL
      OR BookedUntil < NOW()
GROUP BY FlightId;


-- Статистика по забронированным местам
CREATE OR REPLACE VIEW StatReserved AS
SELECT FlightId, COUNT(*) AS Reserved
FROM FreeOrReservedTickets
WHERE BookedUntil > NOW()
GROUP BY FlightId;


-- Статистика по купленным местам
CREATE OR REPLACE VIEW StatSold AS
SELECT FlightId, COUNT(Taken = TRUE) AS Sold
FROM AllSeats
GROUP BY FlightId;

SELECT * FROM AllSeats;

SELECT * FROM StatCanBuy(3);

CREATE OR REPLACE FUNCTION FlightsStatistics(_UserId INT, _Pass TEXT)
RETURNS TABLE (
    CanReserve INT,
    CanBuy INT,
    Free INT,
    Reserved INT,
    Sold INT
)
LANGUAGE plpgsql
AS $$
BEGIN
    IF NOT Authenticate(_UserId, _Pass) THEN
       RETURN;
    END IF;

    RETURN QUERY (
         SELECT CAST(SUM(StatCanReserve.CanReserve) AS INT),
        CAST(SUM(StatCanBuy.CanBuy) AS INT),
        CAST(SUM(StatFree.Free) AS INT),
        CAST(SUM(StatReserved.Reserved) AS INT),
        CAST(SUM(StatSold.Sold) AS INT)
         FROM StatFree
             NATURAL LEFT JOIN StatReserved
             NATURAL LEFT JOIN StatSold
             NATURAL LEFT JOIN StatCanReserve
             NATURAL LEFT JOIN StatCanBuy(_UserId)
    );
END;
$$

SELECT * FROM FlightsStatistics(3, 'amogus');

7

CREATE OR REPLACE FUNCTION FlightStat(_UserId INT, _Pass TEXT, _FlightId INT)
RETURNS TABLE (
    CanReserve INT,
    CanBuy INT,
    Free INT,
    Reserved INT,
    Sold INT
)
LANGUAGE plpgsql
AS $$
BEGIN
    IF NOT Authenticate(_UserId, _Pass) THEN
       RETURN;
    END IF;

    RETURN QUERY (
         SELECT CAST(StatCanReserve.CanReserve AS INT),
        CAST(StatCanBuy.CanBuy AS INT),
        CAST(StatFree.Free AS INT),
        CAST(StatReserved.Reserved AS INT),
        CAST(StatSold.Sold AS INT)
         FROM StatFree
             NATURAL LEFT JOIN StatReserved
             NATURAL LEFT JOIN StatSold
             NATURAL LEFT JOIN StatCanReserve
             NATURAL LEFT JOIN StatCanBuy(_UserId)
     WHERE StatFree.FlightId = _FlightId
    );
END;
$$

SELECT * FROM FlightStat(2, '123456', 5);

8

CREATE OR REPLACE FUNCTION CompressSeats(_FlightId INT)
RETURNS BOOL
LANGUAGE plpgsql
AS $$
DECLARE _CurSeatNo VARCHAR(4);
DECLARE _SeatCursor CURSOR FOR
    SELECT SeatNo
    FROM Seats
         NATURAL JOIN Flights
    WHERE FlightId = _FlightId
    ORDER BY SeatNo
    FOR READ ONLY;
DECLARE _TakenCursor CURSOR FOR
    SELECT SeatNo
    FROM Tickets
    WHERE FlightId = _FlightId
          AND Taken
    FOR UPDATE;
DECLARE _ReservedCursor CURSOR FOR
    SELECT SeatNo
    FROM Tickets
    WHERE FlightId = _FlightId
          AND NOT Taken
          AND BookedUntil >= NOW()
      FOR UPDATE;
BEGIN
    SET CONSTRAINTS ALL DEFERRED;

    DELETE FROM Tickets
    WHERE NOT Taken
          AND (
           BookedUntil < NOW()
           OR BookedUntil IS NULL
          )
          AND FlightId = _FlightId;

    IF _FlightId NOT IN (SELECT FlightId FROM Flights) THEN
       RETURN FALSE;
    END IF;

    OPEN _SeatCursor;

    FOR _SeatNo IN _TakenCursor LOOP
        FETCH NEXT FROM _SeatCursor INTO _CurSeatNo;
        UPDATE Tickets
        SET SeatNo = _CurSeatNo
        WHERE CURRENT OF _TakenCursor; 
    END LOOP;

    FOR _SeatNo IN _ReservedCursor LOOP
        FETCH NEXT FROM _SeatCursor INTO _CurSeatNo;
        UPDATE Tickets
        SET SeatNo = _CurSeatNo
        WHERE CURRENT OF _ReservedCursor; 
    END LOOP;

    CLOSE _SeatCursor;

    RETURN TRUE;
END;
$$

SELECT * FROM CompressSeats(2);

HW-10 tests

START TRANSACTION READ ONLY ISOLATION LEVEL READ COMMITTED;
SELECT SeatNo, BookedUntil FROM FreeOrReservedTickets
  WHERE FlightId = 2
  AND (UserId = 1 OR UserId IS NULL);
COMMIT;

Comments

0.2. Таблицы и представления
0.3. Дополнительные процедуры

FreeSeats
Reserve
ExtendReservation
BuyFree
BuyReserved
FlightsStatistics
FlightsStat
- Дублирования кода подсчёта статистики
CompressSeats
- Дублирования кода подсчёта статистики