SQLize | PHPize | SQLtest

-- DROP SCHEMA bookings; CREATE SCHEMA bookings AUTHORIZATION postgres; COMMENT ON SCHEMA bookings IS 'Авиаперевозки'; -- DROP SEQUENCE bookings.flights_flight_id_seq; CREATE SEQUENCE bookings.flights_flight_id_seq INCREMENT BY 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1 NO CYCLE; -- Permissions ALTER SEQUENCE bookings.flights_flight_id_seq OWNER TO postgres; GRANT ALL ON SEQUENCE bookings.flights_flight_id_seq TO postgres; -- bookings.aircrafts определение -- Drop table -- DROP TABLE bookings.aircrafts; CREATE TABLE bookings.aircrafts ( aircraft_code bpchar(3) NOT NULL, -- Код самолета, IATA model text NOT NULL, -- Модель самолета "range" int4 NOT NULL, -- Максимальная дальность полета, км CONSTRAINT aircrafts_pkey PRIMARY KEY (aircraft_code), CONSTRAINT aircrafts_range_check CHECK ((range > 0)) ); COMMENT ON TABLE bookings.aircrafts IS 'Самолеты'; -- Column comments COMMENT ON COLUMN bookings.aircrafts.aircraft_code IS 'Код самолета, IATA'; COMMENT ON COLUMN bookings.aircrafts.model IS 'Модель самолета'; COMMENT ON COLUMN bookings.aircrafts."range" IS 'Максимальная дальность полета, км'; -- Permissions ALTER TABLE bookings.aircrafts OWNER TO postgres; GRANT ALL ON TABLE bookings.aircrafts TO postgres; -- bookings.airports определение -- Drop table -- DROP TABLE bookings.airports; CREATE TABLE bookings.airports ( airport_code bpchar(3) NOT NULL, -- Код аэропорта airport_name text NOT NULL, -- Название аэропорта city text NOT NULL, -- Город longitude float8 NOT NULL, -- Координаты аэропорта: долгота latitude float8 NOT NULL, -- Координаты аэропорта: широта timezone text NOT NULL, -- Временная зона аэропорта CONSTRAINT airports_pkey PRIMARY KEY (airport_code) ); COMMENT ON TABLE bookings.airports IS 'Аэропорты'; -- Column comments COMMENT ON COLUMN bookings.airports.airport_code IS 'Код аэропорта'; COMMENT ON COLUMN bookings.airports.airport_name IS 'Название аэропорта'; COMMENT ON COLUMN bookings.airports.city IS 'Город'; COMMENT ON COLUMN bookings.airports.longitude IS 'Координаты аэропорта: долгота'; COMMENT ON COLUMN bookings.airports.latitude IS 'Координаты аэропорта: широта'; COMMENT ON COLUMN bookings.airports.timezone IS 'Временная зона аэропорта'; -- Permissions ALTER TABLE bookings.airports OWNER TO postgres; GRANT ALL ON TABLE bookings.airports TO postgres; -- bookings.bookings определение -- Drop table -- DROP TABLE bookings.bookings; CREATE TABLE bookings.bookings ( book_ref bpchar(6) NOT NULL, -- Номер бронирования book_date timestamptz NOT NULL, -- Дата бронирования total_amount numeric(10, 2) NOT NULL, -- Полная сумма бронирования CONSTRAINT bookings_pkey PRIMARY KEY (book_ref) ); COMMENT ON TABLE bookings.bookings IS 'Бронирования'; -- Column comments COMMENT ON COLUMN bookings.bookings.book_ref IS 'Номер бронирования'; COMMENT ON COLUMN bookings.bookings.book_date IS 'Дата бронирования'; COMMENT ON COLUMN bookings.bookings.total_amount IS 'Полная сумма бронирования'; -- Permissions ALTER TABLE bookings.bookings OWNER TO postgres; GRANT ALL ON TABLE bookings.bookings TO postgres; -- bookings.flights определение -- Drop table -- DROP TABLE bookings.flights; CREATE TABLE bookings.flights ( flight_id serial4 NOT NULL, -- Идентификатор рейса flight_no bpchar(6) NOT NULL, -- Номер рейса scheduled_departure timestamptz NOT NULL, -- Время вылета по расписанию scheduled_arrival timestamptz NOT NULL, -- Время прилёта по расписанию departure_airport bpchar(3) NOT NULL, -- Аэропорт отправления arrival_airport bpchar(3) NOT NULL, -- Аэропорт прибытия status varchar(20) NOT NULL, -- Статус рейса aircraft_code bpchar(3) NOT NULL, -- Код самолета, IATA actual_departure timestamptz NULL, -- Фактическое время вылета actual_arrival timestamptz NULL, -- Фактическое время прилёта CONSTRAINT flights_check CHECK ((scheduled_arrival > scheduled_departure)), CONSTRAINT flights_check1 CHECK (((actual_arrival IS NULL) OR ((actual_departure IS NOT NULL) AND (actual_arrival IS NOT NULL) AND (actual_arrival > actual_departure)))), CONSTRAINT flights_flight_no_scheduled_departure_key UNIQUE (flight_no, scheduled_departure), CONSTRAINT flights_pkey PRIMARY KEY (flight_id), CONSTRAINT flights_status_check CHECK (((status)::text = ANY (ARRAY[('On Time'::character varying)::text, ('Delayed'::character varying)::text, ('Departed'::character varying)::text, ('Arrived'::character varying)::text, ('Scheduled'::character varying)::text, ('Cancelled'::character varying)::text]))), CONSTRAINT flights_aircraft_code_fkey FOREIGN KEY (aircraft_code) REFERENCES bookings.aircrafts(aircraft_code), CONSTRAINT flights_arrival_airport_fkey FOREIGN KEY (arrival_airport) REFERENCES bookings.airports(airport_code), CONSTRAINT flights_departure_airport_fkey FOREIGN KEY (departure_airport) REFERENCES bookings.airports(airport_code) ); COMMENT ON TABLE bookings.flights IS 'Рейсы'; -- Column comments COMMENT ON COLUMN bookings.flights.flight_id IS 'Идентификатор рейса'; COMMENT ON COLUMN bookings.flights.flight_no IS 'Номер рейса'; COMMENT ON COLUMN bookings.flights.scheduled_departure IS 'Время вылета по расписанию'; COMMENT ON COLUMN bookings.flights.scheduled_arrival IS 'Время прилёта по расписанию'; COMMENT ON COLUMN bookings.flights.departure_airport IS 'Аэропорт отправления'; COMMENT ON COLUMN bookings.flights.arrival_airport IS 'Аэропорт прибытия'; COMMENT ON COLUMN bookings.flights.status IS 'Статус рейса'; COMMENT ON COLUMN bookings.flights.aircraft_code IS 'Код самолета, IATA'; COMMENT ON COLUMN bookings.flights.actual_departure IS 'Фактическое время вылета'; COMMENT ON COLUMN bookings.flights.actual_arrival IS 'Фактическое время прилёта'; -- Permissions ALTER TABLE bookings.flights OWNER TO postgres; GRANT ALL ON TABLE bookings.flights TO postgres; -- bookings.seats определение -- Drop table -- DROP TABLE bookings.seats; CREATE TABLE bookings.seats ( aircraft_code bpchar(3) NOT NULL, -- Код самолета, IATA seat_no varchar(4) NOT NULL, -- Номер места fare_conditions varchar(10) NOT NULL, -- Класс обслуживания CONSTRAINT seats_fare_conditions_check CHECK (((fare_conditions)::text = ANY (ARRAY[('Economy'::character varying)::text, ('Comfort'::character varying)::text, ('Business'::character varying)::text]))), CONSTRAINT seats_pkey PRIMARY KEY (aircraft_code, seat_no), CONSTRAINT seats_aircraft_code_fkey FOREIGN KEY (aircraft_code) REFERENCES bookings.aircrafts(aircraft_code) ON DELETE CASCADE ); COMMENT ON TABLE bookings.seats IS 'Места'; -- Column comments COMMENT ON COLUMN bookings.seats.aircraft_code IS 'Код самолета, IATA'; COMMENT ON COLUMN bookings.seats.seat_no IS 'Номер места'; COMMENT ON COLUMN bookings.seats.fare_conditions IS 'Класс обслуживания'; -- Permissions ALTER TABLE bookings.seats OWNER TO postgres; GRANT ALL ON TABLE bookings.seats TO postgres; -- bookings.tickets определение -- Drop table -- DROP TABLE bookings.tickets; CREATE TABLE bookings.tickets ( ticket_no bpchar(13) NOT NULL, -- Номер билета book_ref bpchar(6) NOT NULL, -- Номер бронирования passenger_id varchar(20) NOT NULL, -- Идентификатор пассажира passenger_name text NOT NULL, -- Имя пассажира contact_data jsonb NULL, -- Контактные данные пассажира CONSTRAINT tickets_pkey PRIMARY KEY (ticket_no), CONSTRAINT tickets_book_ref_fkey FOREIGN KEY (book_ref) REFERENCES bookings.bookings(book_ref) ); COMMENT ON TABLE bookings.tickets IS 'Билеты'; -- Column comments COMMENT ON COLUMN bookings.tickets.ticket_no IS 'Номер билета'; COMMENT ON COLUMN bookings.tickets.book_ref IS 'Номер бронирования'; COMMENT ON COLUMN bookings.tickets.passenger_id IS 'Идентификатор пассажира'; COMMENT ON COLUMN bookings.tickets.passenger_name IS 'Имя пассажира'; COMMENT ON COLUMN bookings.tickets.contact_data IS 'Контактные данные пассажира'; -- Permissions ALTER TABLE bookings.tickets OWNER TO postgres; GRANT ALL ON TABLE bookings.tickets TO postgres; -- bookings.ticket_flights определение -- Drop table -- DROP TABLE bookings.ticket_flights; CREATE TABLE bookings.ticket_flights ( ticket_no bpchar(13) NOT NULL, -- Номер билета flight_id int4 NOT NULL, -- Идентификатор рейса fare_conditions varchar(10) NOT NULL, -- Класс обслуживания amount numeric(10, 2) NOT NULL, -- Стоимость перелета CONSTRAINT ticket_flights_amount_check CHECK ((amount >= (0)::numeric)), CONSTRAINT ticket_flights_fare_conditions_check CHECK (((fare_conditions)::text = ANY (ARRAY[('Economy'::character varying)::text, ('Comfort'::character varying)::text, ('Business'::character varying)::text]))), CONSTRAINT ticket_flights_pkey PRIMARY KEY (ticket_no, flight_id), CONSTRAINT ticket_flights_flight_id_fkey FOREIGN KEY (flight_id) REFERENCES bookings.flights(flight_id), CONSTRAINT ticket_flights_ticket_no_fkey FOREIGN KEY (ticket_no) REFERENCES bookings.tickets(ticket_no) ); COMMENT ON TABLE bookings.ticket_flights IS 'Перелеты'; -- Column comments COMMENT ON COLUMN bookings.ticket_flights.ticket_no IS 'Номер билета'; COMMENT ON COLUMN bookings.ticket_flights.flight_id IS 'Идентификатор рейса'; COMMENT ON COLUMN bookings.ticket_flights.fare_conditions IS 'Класс обслуживания'; COMMENT ON COLUMN bookings.ticket_flights.amount IS 'Стоимость перелета'; -- Permissions ALTER TABLE bookings.ticket_flights OWNER TO postgres; GRANT ALL ON TABLE bookings.ticket_flights TO postgres; -- bookings.boarding_passes определение -- Drop table -- DROP TABLE bookings.boarding_passes; CREATE TABLE bookings.boarding_passes ( ticket_no bpchar(13) NOT NULL, -- Номер билета flight_id int4 NOT NULL, -- Идентификатор рейса boarding_no int4 NOT NULL, -- Номер посадочного талона seat_no varchar(4) NOT NULL, -- Номер места CONSTRAINT boarding_passes_flight_id_boarding_no_key UNIQUE (flight_id, boarding_no), CONSTRAINT boarding_passes_flight_id_seat_no_key UNIQUE (flight_id, seat_no), CONSTRAINT boarding_passes_pkey PRIMARY KEY (ticket_no, flight_id), CONSTRAINT boarding_passes_ticket_no_fkey FOREIGN KEY (ticket_no,flight_id) REFERENCES bookings.ticket_flights(ticket_no,flight_id) ); COMMENT ON TABLE bookings.boarding_passes IS 'Посадочные талоны'; -- Column comments COMMENT ON COLUMN bookings.boarding_passes.ticket_no IS 'Номер билета'; COMMENT ON COLUMN bookings.boarding_passes.flight_id IS 'Идентификатор рейса'; COMMENT ON COLUMN bookings.boarding_passes.boarding_no IS 'Номер посадочного талона'; COMMENT ON COLUMN bookings.boarding_passes.seat_no IS 'Номер места'; -- Permissions ALTER TABLE bookings.boarding_passes OWNER TO postgres; GRANT ALL ON TABLE bookings.boarding_passes TO postgres; -- bookings.flights_v исходный текст CREATE OR REPLACE VIEW bookings.flights_v AS SELECT f.flight_id, f.flight_no, f.scheduled_departure, timezone(dep.timezone, f.scheduled_departure) AS scheduled_departure_local, f.scheduled_arrival, timezone(arr.timezone, f.scheduled_arrival) AS scheduled_arrival_local, f.scheduled_arrival - f.scheduled_departure AS scheduled_duration, f.departure_airport, dep.airport_name AS departure_airport_name, dep.city AS departure_city, f.arrival_airport, arr.airport_name AS arrival_airport_name, arr.city AS arrival_city, f.status, f.aircraft_code, f.actual_departure, timezone(dep.timezone, f.actual_departure) AS actual_departure_local, f.actual_arrival, timezone(arr.timezone, f.actual_arrival) AS actual_arrival_local, f.actual_arrival - f.actual_departure AS actual_duration FROM bookings.flights f, bookings.airports dep, bookings.airports arr WHERE f.departure_airport = dep.airport_code AND f.arrival_airport = arr.airport_code; COMMENT ON VIEW bookings.flights_v IS 'Рейсы'; COMMENT ON COLUMN bookings.flights_v.flight_id IS 'Идентификатор рейса'; COMMENT ON COLUMN bookings.flights_v.flight_no IS 'Номер рейса'; COMMENT ON COLUMN bookings.flights_v.scheduled_departure IS 'Время вылета по расписанию'; COMMENT ON COLUMN bookings.flights_v.scheduled_departure_local IS 'Время вылета по расписанию, местное время в пункте отправления'; COMMENT ON COLUMN bookings.flights_v.scheduled_arrival IS 'Время прилёта по расписанию'; COMMENT ON COLUMN bookings.flights_v.scheduled_arrival_local IS 'Время прилёта по расписанию, местное время в пункте прибытия'; COMMENT ON COLUMN bookings.flights_v.scheduled_duration IS 'Планируемая продолжительность полета'; COMMENT ON COLUMN bookings.flights_v.departure_airport IS 'Код аэропорта отправления'; COMMENT ON COLUMN bookings.flights_v.departure_airport_name IS 'Название аэропорта отправления'; COMMENT ON COLUMN bookings.flights_v.departure_city IS 'Город отправления'; COMMENT ON COLUMN bookings.flights_v.arrival_airport IS 'Код аэропорта прибытия'; COMMENT ON COLUMN bookings.flights_v.arrival_airport_name IS 'Название аэропорта прибытия'; COMMENT ON COLUMN bookings.flights_v.arrival_city IS 'Город прибытия'; COMMENT ON COLUMN bookings.flights_v.status IS 'Статус рейса'; COMMENT ON COLUMN bookings.flights_v.aircraft_code IS 'Код самолета, IATA'; COMMENT ON COLUMN bookings.flights_v.actual_departure IS 'Фактическое время вылета'; COMMENT ON COLUMN bookings.flights_v.actual_departure_local IS 'Фактическое время вылета, местное время в пункте отправления'; COMMENT ON COLUMN bookings.flights_v.actual_arrival IS 'Фактическое время прилёта'; COMMENT ON COLUMN bookings.flights_v.actual_arrival_local IS 'Фактическое время прилёта, местное время в пункте прибытия'; COMMENT ON COLUMN bookings.flights_v.actual_duration IS 'Фактическая продолжительность полета'; -- Permissions ALTER TABLE bookings.flights_v OWNER TO postgres; GRANT ALL ON TABLE bookings.flights_v TO postgres; -- bookings.routes исходный текст CREATE MATERIALIZED VIEW bookings.routes TABLESPACE pg_default AS WITH f3 AS ( SELECT f2.flight_no, f2.departure_airport, f2.arrival_airport, f2.aircraft_code, f2.duration, array_agg(f2.days_of_week) AS days_of_week FROM ( SELECT f1.flight_no, f1.departure_airport, f1.arrival_airport, f1.aircraft_code, f1.duration, f1.days_of_week FROM ( SELECT flights.flight_no, flights.departure_airport, flights.arrival_airport, flights.aircraft_code, flights.scheduled_arrival - flights.scheduled_departure AS duration, to_char(flights.scheduled_departure, 'ID'::text)::integer AS days_of_week FROM bookings.flights) f1 GROUP BY f1.flight_no, f1.departure_airport, f1.arrival_airport, f1.aircraft_code, f1.duration, f1.days_of_week ORDER BY f1.flight_no, f1.departure_airport, f1.arrival_airport, f1.aircraft_code, f1.duration, f1.days_of_week) f2 GROUP BY f2.flight_no, f2.departure_airport, f2.arrival_airport, f2.aircraft_code, f2.duration ) SELECT f3.flight_no, f3.departure_airport, dep.airport_name AS departure_airport_name, dep.city AS departure_city, f3.arrival_airport, arr.airport_name AS arrival_airport_name, arr.city AS arrival_city, f3.aircraft_code, f3.duration, f3.days_of_week FROM f3, bookings.airports dep, bookings.airports arr WHERE f3.departure_airport = dep.airport_code AND f3.arrival_airport = arr.airport_code WITH DATA; COMMENT ON MATERIALIZED VIEW bookings.routes IS 'Маршруты'; COMMENT ON COLUMN bookings.routes.flight_no IS 'Номер рейса'; COMMENT ON COLUMN bookings.routes.departure_airport IS 'Код аэропорта отправления'; COMMENT ON COLUMN bookings.routes.departure_airport_name IS 'Название аэропорта отправления'; COMMENT ON COLUMN bookings.routes.departure_city IS 'Город отправления'; COMMENT ON COLUMN bookings.routes.arrival_airport IS 'Код аэропорта прибытия'; COMMENT ON COLUMN bookings.routes.arrival_airport_name IS 'Название аэропорта прибытия'; COMMENT ON COLUMN bookings.routes.arrival_city IS 'Город прибытия'; COMMENT ON COLUMN bookings.routes.aircraft_code IS 'Код самолета, IATA'; COMMENT ON COLUMN bookings.routes.duration IS 'Продолжительность полета'; COMMENT ON COLUMN bookings.routes.days_of_week IS 'Дни недели, когда выполняются рейсы'; -- Permissions ALTER TABLE bookings.routes OWNER TO postgres; GRANT ALL ON TABLE bookings.routes TO postgres; -- bookings.routes_financial_summary исходный текст CREATE MATERIALIZED VIEW bookings.routes_financial_summary TABLESPACE pg_default AS WITH flight_pairs AS ( SELECT DISTINCT flights.departure_airport, flights.arrival_airport, sum(ticket_flights.amount) AS total_amount FROM bookings.flights flights JOIN bookings.ticket_flights ON flights.flight_id = ticket_flights.flight_id GROUP BY flights.departure_airport, flights.arrival_airport ), max_routes AS ( SELECT flight_pairs.departure_airport, flight_pairs.arrival_airport, flight_pairs.total_amount FROM flight_pairs ORDER BY flight_pairs.total_amount DESC LIMIT 1 ) SELECT departure_airport, arrival_airport, total_amount FROM max_routes WITH DATA; -- Permissions ALTER TABLE bookings.routes_financial_summary OWNER TO postgres; GRANT ALL ON TABLE bookings.routes_financial_summary TO postgres; -- DROP FUNCTION bookings.now(); CREATE OR REPLACE FUNCTION bookings.now() RETURNS timestamp with time zone LANGUAGE sql IMMUTABLE COST 0.01 AS $function$SELECT '2016-10-13 17:00:00'::TIMESTAMP AT TIME ZONE 'Europe/Moscow';$function$ ; COMMENT ON FUNCTION bookings.now() IS 'Момент времени, относительно которого сформированы данные'; -- Permissions ALTER FUNCTION bookings.now() OWNER TO postgres; GRANT ALL ON FUNCTION bookings.now() TO postgres; -- Permissions GRANT ALL ON SCHEMA bookings TO postgres;