DrMGC

Untitled

Jan 15th, 2021
864
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. BEGIN;
  2.  
  3. CREATE SCHEMA auth;
  4. CREATE SCHEMA route;
  5. CREATE SCHEMA ticket;
  6. CREATE SCHEMA train;
  7. CREATE SCHEMA trip;
  8.  
  9. CREATE TABLE auth."user" (
  10.     id SERIAL NOT NULL,
  11.     name VARCHAR NOT NULL,
  12.     birthday DATE NOT NULL,
  13.     email VARCHAR(40),
  14.     password_hash VARCHAR(200) NOT NULL,
  15.     created_on TIMESTAMP WITHOUT TIME ZONE DEFAULT now() NOT NULL,
  16.     last_login TIMESTAMP WITHOUT TIME ZONE,
  17.     active BOOLEAN DEFAULT 'true' NOT NULL,
  18.     is_admin BOOLEAN DEFAULT 'false' NOT NULL,
  19.     CONSTRAINT pk_user PRIMARY KEY (id)
  20. );
  21.  
  22. COMMENT ON TABLE auth."user" IS 'Пользователь';
  23. COMMENT ON COLUMN auth."user".name IS 'Полное имя пользователя';
  24. COMMENT ON COLUMN auth."user".created_on IS 'Дата и время регистрации';
  25. COMMENT ON COLUMN auth."user".last_login IS 'Дата и время последней авторизации';
  26.  
  27. CREATE UNIQUE INDEX ix_auth_user_email ON auth."user" (email);
  28.  
  29. CREATE TABLE route.route (
  30.     id SERIAL NOT NULL,
  31.     name VARCHAR(12) NOT NULL,
  32.     CONSTRAINT pk_route PRIMARY KEY (id),
  33.     CONSTRAINT uq_route_name UNIQUE (name)
  34. );
  35.  
  36. COMMENT ON TABLE route.route IS 'Маршрут';
  37.  
  38. CREATE TABLE route.station (
  39.     id SERIAL NOT NULL,
  40.     name VARCHAR(64) NOT NULL,
  41.     is_passenger BOOLEAN DEFAULT true NOT NULL,
  42.     CONSTRAINT pk_station PRIMARY KEY (id)
  43. );
  44.  
  45. COMMENT ON TABLE route.station IS 'Станция';
  46.  
  47. CREATE UNIQUE INDEX ix_route_station_name ON route.station (name);
  48.  
  49. CREATE TABLE train.train (
  50.     id SERIAL NOT NULL,
  51.     name VARCHAR(32) NOT NULL,
  52.     price_multiplier FLOAT DEFAULT '1' NOT NULL,
  53.     CONSTRAINT pk_train PRIMARY KEY (id)
  54. );
  55.  
  56. COMMENT ON TABLE train.train IS 'Поезд (состав)';
  57.  
  58. COMMENT ON COLUMN train.train.price_multiplier IS 'Множитель стоимости на все цены в поезде';
  59.  
  60. CREATE TABLE train.wagon_class (
  61.     id SERIAL NOT NULL,
  62.     name VARCHAR(8) NOT NULL,
  63.     seats JSONB NOT NULL,
  64.     seats_schema BYTEA,
  65.     seats_count INTEGER DEFAULT '0' NOT NULL,
  66.     CONSTRAINT pk_wagon_class PRIMARY KEY (id),
  67.     CONSTRAINT uq_wagon_class_name UNIQUE (name)
  68. );
  69.  
  70. COMMENT ON TABLE train.wagon_class IS 'Класс/тип вагона';
  71. COMMENT ON COLUMN train.wagon_class.seats IS 'Описание мест';
  72. COMMENT ON COLUMN train.wagon_class.seats_schema IS 'Схема расположения мест в вагоне';
  73. COMMENT ON COLUMN train.wagon_class.seats_count IS 'Количество мест';
  74.  
  75. CREATE INDEX ix_train_wagon_class_seats ON train.wagon_class (seats);
  76.  
  77. CREATE TABLE route.stage (
  78.     route_id INTEGER NOT NULL,
  79.     station_id INTEGER NOT NULL,
  80.     arrival INTERVAL,
  81.     departure INTERVAL,
  82.     CONSTRAINT pk_stage PRIMARY KEY (route_id, station_id),
  83.     CONSTRAINT ck_stage_arrival_lt_departure CHECK (departure > arrival),
  84.     CONSTRAINT ck_stage_not_null_either_arrival_or_departure CHECK (true),
  85.     CONSTRAINT fk_stage_route_id_route FOREIGN KEY(route_id) REFERENCES route.route (id) ON DELETE cascade,
  86.     CONSTRAINT fk_stage_station_id_station FOREIGN KEY(station_id) REFERENCES route.station (id) ON DELETE restrict
  87. );
  88.  
  89. COMMENT ON TABLE route.stage IS 'Этап маршрута';
  90. COMMENT ON COLUMN route.stage.station_id IS 'Станция, на которой происходит стоянка';
  91. COMMENT ON COLUMN route.stage.arrival IS 'Время прибытия. Если NULL, значит это - начальная станция';
  92. COMMENT ON COLUMN route.stage.departure IS 'Время отправления. Если NULL, значит это - конечная станция';
  93.  
  94. CREATE TABLE train.wagon (
  95.     id SERIAL NOT NULL,
  96.     train_id INTEGER NOT NULL,
  97.     number INTEGER NOT NULL,
  98.     wagon_class_id INTEGER NOT NULL,
  99.     CONSTRAINT pk_wagon PRIMARY KEY (id),
  100.     CONSTRAINT fk_wagon_train_id_train FOREIGN KEY(train_id) REFERENCES train.train (id) ON DELETE cascade,
  101.     CONSTRAINT fk_wagon_wagon_class_id_wagon_class FOREIGN KEY(wagon_class_id) REFERENCES train.wagon_class (id) ON DELETE restrict,
  102.     CONSTRAINT uq_wagon_train_id_number UNIQUE (train_id, number)
  103. );
  104.  
  105. COMMENT ON TABLE train.wagon IS 'Вагон состава';
  106. COMMENT ON COLUMN train.wagon.train_id IS 'Состав, в который входит вагон';
  107. COMMENT ON COLUMN train.wagon.number IS 'Номер вагона в составе';
  108.  
  109. CREATE TABLE trip.trip (
  110.     id SERIAL NOT NULL,
  111.     name VARCHAR(32) NOT NULL,
  112.     price_multiplier FLOAT DEFAULT '1' NOT NULL,
  113.     departure_date DATE NOT NULL,
  114.     route_id INTEGER NOT NULL,
  115.     train_id INTEGER NOT NULL,
  116.     CONSTRAINT pk_trip PRIMARY KEY (id),
  117.     CONSTRAINT fk_trip_route_id_route FOREIGN KEY(route_id) REFERENCES route.route (id) ON DELETE restrict,
  118.     CONSTRAINT fk_trip_train_id_train FOREIGN KEY(train_id) REFERENCES train.train (id) ON DELETE restrict
  119. );
  120.  
  121. COMMENT ON TABLE trip.trip IS 'рейс';
  122. COMMENT ON COLUMN trip.trip.price_multiplier IS 'Множитель стоимости на все цены в рейсе';
  123. COMMENT ON COLUMN trip.trip.departure_date IS 'День начала рейса';
  124. COMMENT ON COLUMN trip.trip.route_id IS 'Маршрут рейса';
  125. COMMENT ON COLUMN trip.trip.train_id IS 'Состав, идущий в рейс';
  126.  
  127. CREATE TABLE ticket.ticket (
  128.     id SERIAL NOT NULL,
  129.     status INTEGER DEFAULT '0' NOT NULL,
  130.     user_id INTEGER NOT NULL,
  131.     trip_id INTEGER NOT NULL,
  132.     wagon_id INTEGER NOT NULL,
  133.     seat_number INTEGER NOT NULL,
  134.     departure_id INTEGER NOT NULL,
  135.     arrival_id INTEGER NOT NULL,
  136.     CONSTRAINT pk_ticket PRIMARY KEY (id),
  137.     CONSTRAINT fk_ticket_arrival_id_station FOREIGN KEY(arrival_id) REFERENCES route.station (id) ON DELETE restrict,
  138.     CONSTRAINT fk_ticket_departure_id_station FOREIGN KEY(departure_id) REFERENCES route.station (id) ON DELETE restrict,
  139.     CONSTRAINT fk_ticket_trip_id_trip FOREIGN KEY(trip_id) REFERENCES trip.trip (id) ON DELETE restrict,
  140.     CONSTRAINT fk_ticket_user_id_user FOREIGN KEY(user_id) REFERENCES auth."user" (id),
  141.     CONSTRAINT fk_ticket_wagon_id_wagon FOREIGN KEY(wagon_id) REFERENCES train.wagon (id) ON DELETE restrict
  142. );
  143.  
  144. COMMENT ON TABLE ticket.ticket IS 'Купленый билет';
  145. COMMENT ON COLUMN ticket.ticket.status IS 'Статус билета: 0 - BOOKED, 1 - PAID, 2 - CANCELED';
  146. COMMENT ON COLUMN ticket.ticket.user_id IS 'Покупатель';
  147. COMMENT ON COLUMN ticket.ticket.trip_id IS 'Рейс, на который куплен билет';
  148. COMMENT ON COLUMN ticket.ticket.wagon_id IS 'Вагон, в который куплен билет';
  149. COMMENT ON COLUMN ticket.ticket.seat_number IS 'Место в вагоне';
  150. COMMENT ON COLUMN ticket.ticket.departure_id IS 'Станция с которой куплен билет';
  151. COMMENT ON COLUMN ticket.ticket.arrival_id IS 'Станция до которой куплен билет';
  152.  
  153. CREATE INDEX ix_ticket_trip_wagon_seat ON ticket.ticket (trip_id, wagon_id, seat_number);
  154.  
  155. CREATE TABLE ticket.log (
  156.     id SERIAL NOT NULL,
  157.     ticket_id INTEGER NOT NULL,
  158.     old_status INTEGER,
  159.     new_status INTEGER NOT NULL,
  160.     datetime TIMESTAMP WITHOUT TIME ZONE DEFAULT now() NOT NULL,
  161.     CONSTRAINT pk_log PRIMARY KEY (id),
  162.     CONSTRAINT fk_log_ticket_id_ticket FOREIGN KEY(ticket_id) REFERENCES ticket.ticket (id) ON DELETE cascade
  163. );
  164.  
  165. COMMENT ON TABLE ticket.log IS 'Журнал статусов билетов';
  166. COMMENT ON COLUMN ticket.log.old_status IS 'Предыдущий статус билета';
  167. COMMENT ON COLUMN ticket.log.new_status IS 'Новый статус билета';
  168.  
  169. CREATE INDEX ix_ticket_log_ticket_id ON ticket.log (ticket_id);
  170.  
  171. COMMIT;
  172.  
RAW Paste Data