# Бронирование

Начнем с бронирования и выберем какое-нибудь одно:

    => SELECT * FROM bookings b WHERE b.book_ref = '0824C5';

     book_ref |       book_date        | total_amount
    ----------+------------------------+--------------
     0824C5   | 2017-07-25 23:36:00+03 |    112400.00
    (1 row)

Мы видим дату бронирования и общую сумму.

Если сравнивать дату с текущей, то бронирование сделано довольно давно:

    => SELECT now();

                  now
    -------------------------------
     2019-02-13 13:02:19.861574+03
    (1 row)

Но для демобазы "текущим" моментом является другая дата:

    => SELECT bookings.now();

              now
    ------------------------
     2017-08-15 18:00:00+03
    (1 row)

Так что "на самом деле" билеты забронированы 20 дней назад:

    => SELECT bookings.now() - b.book_date
    FROM bookings b WHERE b.book_ref = '0824C5';

         ?column?
    ------------------
     20 days 18:24:00
    (1 row)

# Билеты

Посмотрим, какие билеты включены в это бронирование.

    => SELECT t.*
    FROM bookings b, tickets t
    WHERE b.book_ref = '0824C5' AND t.book_ref = b.book_ref;

       ticket_no   | book_ref | passenger_id |  passenger_name   |       contact_data
    ---------------+----------+--------------+-------------------+---------------------------
     0005435126781 | 0824C5   | 7247 393204  | ALEKSANDR MATVEEV | {"phone": "+70095062310"}
     0005435126782 | 0824C5   | 1745 826066  | NINA KRASNOVA     | {"phone": "+70876976071"}
    (2 rows)

Летят два человека; на каждого оформляется собственный билет с информацией о пассажире.

# Перелеты

По какому маршруту летят пассажиры? Добавим в запрос перелеты.

    => SELECT tf.*
    FROM tickets t, ticket_flights tf
    WHERE t.ticket_no = '0005435126781' AND tf.ticket_no = t.ticket_no;

       ticket_no   | flight_id | fare_conditions |  amount
    ---------------+-----------+-----------------+----------
     0005435126781 |     22566 | Economy         | 11700.00
     0005435126781 |     71439 | Economy         |  3200.00
     0005435126781 |     74643 | Economy         |  8800.00
     0005435126781 |     94335 | Economy         | 11700.00
     0005435126781 |     95726 | Economy         |  3200.00
     0005435126781 |    206625 | Business        | 26400.00
    (6 rows)

Здесь мы смотрим только на один билет - все маршруты в одном бронировании всегда совпадают.

Видим, что в билете 6 перелетов; из них один бизнес-классом, другие - экономом.

# Рейсы

Теперь разберемся, какие рейсы скрываются за этими перелетами.

    => SELECT f.flight_id, f.scheduled_departure, f.departure_airport dep,
    f.arrival_airport arr, f.status, f.aircraft_code aircraft
    FROM tickets t, ticket_flights tf, flights f
    WHERE t.ticket_no = '0005435126781'
    AND tf.ticket_no = t.ticket_no AND f.flight_id = tf.flight_id
    ORDER BY f.scheduled_departure;

     flight_id |  scheduled_departure   | dep | arr |  status   | aircraft
    -----------+------------------------+-----+-----+-----------+----------
         22566 | 2017-08-12 11:00:00+03 | VKO | PEE | Arrived   | 773
         95726 | 2017-08-12 15:30:00+03 | PEE | SVX | Arrived   | SU9
         74643 | 2017-08-13 11:30:00+03 | SVX | SGC | Arrived   | SU9
        206625 | 2017-08-15 14:45:00+03 | SGC | SVX | Departed  | SU9
         71439 | 2017-08-16 08:50:00+03 | SVX | PEE | On Time   | SU9
         94335 | 2017-08-16 18:55:00+03 | PEE | VKO | Scheduled | 773
    (6 rows)

Видим три рейса "туда" и три "обратно". "Туда" все рейсы уже совершены (Arrived), а в настоящее время пассажир летит "обратно" (Departed). Следующий рейс будет по расписанию (On Time), а на последний еще не открыта регистрация (Scheduled).

Посмотрим внимательнее на все столбцы одного из рейсов.

    => SELECT * FROM flights f WHERE f.flight_id = 22566 \gx

    -[ RECORD 1 ]-------+-----------------------
    flight_id           | 22566
    flight_no           | PG0412
    scheduled_departure | 2017-08-12 11:00:00+03
    scheduled_arrival   | 2017-08-12 12:25:00+03
    departure_airport   | VKO
    arrival_airport     | PEE
    status              | Arrived
    aircraft_code       | 773
    actual_departure    | 2017-08-12 11:01:00+03
    actual_arrival      | 2017-08-12 12:25:00+03

Реальное время может отличаться от времени по расписанию (обычно не сильно).

Номер flight_no одинаков для всех рейсов, следующих по одному маршруту по расписанию:

    => SELECT f.flight_id, f.flight_no, f.scheduled_departure
    FROM flights f WHERE f.flight_no = 'PG0412'
    ORDER BY f.scheduled_departure
    LIMIT 10;

     flight_id | flight_no |  scheduled_departure
    -----------+-----------+------------------------
         22784 | PG0412    | 2016-08-15 11:00:00+03
         22746 | PG0412    | 2016-08-16 11:00:00+03
         22721 | PG0412    | 2016-08-17 11:00:00+03
         22691 | PG0412    | 2016-08-18 11:00:00+03
         22749 | PG0412    | 2016-08-19 11:00:00+03
         22508 | PG0412    | 2016-08-20 11:00:00+03
         22493 | PG0412    | 2016-08-21 11:00:00+03
         22496 | PG0412    | 2016-08-22 11:00:00+03
         22483 | PG0412    | 2016-08-23 11:00:00+03
         22501 | PG0412    | 2016-08-24 11:00:00+03
    (10 rows)

# Аэропорты

В качестве ключа для аэропортов используется общепринятый трехбуквенный код. Расшифруем:

    => SELECT f.scheduled_departure,
    dep.airport_code || ' ' || dep.city || ' (' || dep.airport_name || ')' departure,
    arr.airport_code || ' ' || arr.city || ' (' || arr.airport_name || ')' arrival
    FROM tickets t, ticket_flights tf, flights f, airports dep, airports arr
    WHERE t.ticket_no = '0005435126781'
    AND tf.ticket_no = t.ticket_no AND f.flight_id = tf.flight_id
    AND dep.airport_code = f.departure_airport AND arr.airport_code = f.arrival_airport
    ORDER BY f.scheduled_departure;

      scheduled_departure   |          departure          |           arrival
    ------------------------+-----------------------------+-----------------------------
     2017-08-12 11:00:00+03 | VKO Москва (Внуково)        | PEE Пермь (Пермь)
     2017-08-12 15:30:00+03 | PEE Пермь (Пермь)           | SVX Екатеринбург (Кольцово)
     2017-08-13 11:30:00+03 | SVX Екатеринбург (Кольцово) | SGC Сургут (Сургут)
     2017-08-15 14:45:00+03 | SGC Сургут (Сургут)         | SVX Екатеринбург (Кольцово)
     2017-08-16 08:50:00+03 | SVX Екатеринбург (Кольцово) | PEE Пермь (Пермь)
     2017-08-16 18:55:00+03 | PEE Пермь (Пермь)           | VKO Москва (Внуково)
    (6 rows)

Посмотрим полную информацию об одном аэропорте:

    => SELECT * FROM airports WHERE airport_code = 'VKO' \gx

    -[ RECORD 1 ]+------------------------------
    airport_code | VKO
    airport_name | Внуково
    city         | Москва
    coordinates  | (37.2615013123,55.5914993286)
    timezone     | Europe/Moscow

Помимо названия и города, хранятся координаты аэропорта и часовой пояс.

# Снова рейсы (представление)

Для упрощения запросов можно использовать представление, которое расшифровывает информацию о рейсах:

    => SELECT * FROM flights_v f WHERE f.flight_id = 22566 \gx

    -[ RECORD 1 ]-------------+-----------------------
    flight_id                 | 22566
    flight_no                 | PG0412
    scheduled_departure       | 2017-08-12 11:00:00+03
    scheduled_departure_local | 2017-08-12 11:00:00
    scheduled_arrival         | 2017-08-12 12:25:00+03
    scheduled_arrival_local   | 2017-08-12 14:25:00
    scheduled_duration        | 01:25:00
    departure_airport         | VKO
    departure_airport_name    | Внуково
    departure_city            | Москва
    arrival_airport           | PEE
    arrival_airport_name      | Пермь
    arrival_city              | Пермь
    status                    | Arrived
    aircraft_code             | 773
    actual_departure          | 2017-08-12 11:01:00+03
    actual_departure_local    | 2017-08-12 11:01:00
    actual_arrival            | 2017-08-12 12:25:00+03
    actual_arrival_local      | 2017-08-12 14:25:00
    actual_duration           | 01:24:00

Здесь видим и локальное время в часовых поясах городов отправления и прибытия, и длительность полета.

# Маршруты (представление)

Поскольку в демобазе маршруты не меняются со временем, из таблицы рейсов можно выделить информацию, которая не зависит от конкретной даты вылета. Такая информация собрана в представлении:

    => SELECT * FROM routes r WHERE r.flight_no = 'PG0412' \gx

    -[ RECORD 1 ]----------+----------------
    flight_no              | PG0412
    departure_airport      | VKO
    departure_airport_name | Внуково
    departure_city         | Москва
    arrival_airport        | PEE
    arrival_airport_name   | Пермь
    arrival_city           | Пермь
    aircraft_code          | 773
    duration               | 01:25:00
    days_of_week           | {1,2,3,4,5,6,7}

Видно, что рейсы выполняются ежедневно (массив days_of_week).

# Самолеты

Модели самолетов, обслуживающих рейсы, также используют стандартные трехсимвольные коды в качестве первичных ключей.

    => SELECT a.*
    FROM flights f, aircrafts a
    WHERE f.flight_id = 22566 AND a.aircraft_code = f.aircraft_code;

     aircraft_code |     model     | range
    ---------------+---------------+-------
     773           | Боинг 777-300 | 11100
    (1 row)

# Места

В демобазе все самолеты одной модели имеют одинаковую конфигурацию салона. Посмотрим на первый ряд:

    => SELECT s.*
    FROM flights f, aircrafts a, seats s
    WHERE f.flight_id = 22566
    AND a.aircraft_code = f.aircraft_code AND s.aircraft_code = a.aircraft_code
    AND s.seat_no ~ '^1.$';

     aircraft_code | seat_no | fare_conditions
    ---------------+---------+-----------------
     773           | 1A      | Business
     773           | 1C      | Business
     773           | 1D      | Business
     773           | 1G      | Business
     773           | 1H      | Business
     773           | 1K      | Business
    (6 rows)

Это бизнес-класс.

А вот общее число мест различных классов обслуживания:

    => SELECT s.fare_conditions, count(*)
    FROM seats s WHERE s.aircraft_code = '733'
    GROUP BY s.fare_conditions;

     fare_conditions | count
    -----------------+-------
     Business        |    12
     Economy         |   118
    (2 rows)

# Посадочные талоны

На каких местах сидел наш пассажир? Для этого надо заглянуть в посадочный талон, который выдается при регистрации на рейс:

    => SELECT f.status, bp.*
    FROM tickets t
      JOIN ticket_flights tf ON tf.ticket_no = t.ticket_no
      JOIN flights f ON f.flight_id = tf.flight_id
      LEFT JOIN boarding_passes bp
        ON bp.ticket_no = tf.ticket_no AND bp.flight_id = tf.flight_id
    WHERE t.ticket_no = '0005435126781'
    ORDER BY f.scheduled_departure;

      status   |   ticket_no   | flight_id | boarding_no | seat_no
    -----------+---------------+-----------+-------------+---------
     Arrived   | 0005435126781 |     22566 |           4 | 22A
     Arrived   | 0005435126781 |     95726 |          64 | 19D
     Arrived   | 0005435126781 |     74643 |          42 | 8D
     Departed  | 0005435126781 |    206625 |          11 | 3F
     On Time   |               |           |             |
     Scheduled |               |           |             |
    (6 rows)

На два оставшихся рейса пассажир еще не зарегистрировался.

# Многоязычность

В демобазе заложена возможность перевода названий аэропортов, городов и самолетов на другие языки. Как мы видели, по умолчанию все названия выводятся по-русски:

    => SELECT * FROM airports a WHERE a.airport_code = 'VKO' \gx

    -[ RECORD 1 ]+------------------------------
    airport_code | VKO
    airport_name | Внуково
    city         | Москва
    coordinates  | (37.2615013123,55.5914993286)
    timezone     | Europe/Moscow

Чтобы сменить язык, достаточно установить конфигурационный параметр:

    => SET bookings.lang = 'en';

    SET

    => SELECT * FROM airports a WHERE a.airport_code = 'VKO' \gx

    -[ RECORD 1 ]+------------------------------
    airport_code | VKO
    airport_name | Vnukovo International Airport
    city         | Moscow
    coordinates  | (37.2615013123,55.5914993286)
    timezone     | Europe/Moscow

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

    => SELECT * FROM airports_data ml WHERE ml.airport_code = 'VKO' \gx

    -[ RECORD 1 ]+---------------------------------------------------------
    airport_code | VKO
    airport_name | {"en": "Vnukovo International Airport", "ru": "Внуково"}
    city         | {"en": "Moscow", "ru": "Москва"}
    coordinates  | (37.2615013123,55.5914993286)
    timezone     | Europe/Moscow

Конец демонстрации.