deploy/db/initdb-scripts/init.sql - develop - nikoir/hotel-advisors-api

deploy/db/initdb-scripts/init.sql

56 строк2 KB

drzoombo

Добавлен метод обновления отеля; добавлен маппер для обработки ошибок.

19 июн 2025, 21:42

19 июн 2025, 21:42bef40b7

CREATE DATABASE IF NOT EXISTS hotel_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE hotel_db;

-- Таблица городов (справочник)
CREATE TABLE IF NOT EXISTS city (
    id BIGINT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    region VARCHAR(100),
    country VARCHAR(100) DEFAULT 'Россия',
    PRIMARY KEY (id),
    CONSTRAINT uk_city_name UNIQUE (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Таблица адресов
CREATE TABLE IF NOT EXISTS address (
    id BIGINT NOT NULL AUTO_INCREMENT,
    postal_code VARCHAR(20),
    city_id BIGINT NOT NULL,
    street VARCHAR(100) NOT NULL,
    building VARCHAR(20) NOT NULL,
    PRIMARY KEY (id),
    CONSTRAINT fk_address_city FOREIGN KEY (city_id) 
        REFERENCES city (id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Таблица отелей
CREATE TABLE IF NOT EXISTS hotel (
    id BIGINT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    address_id BIGINT NOT NULL,
    star_rating TINYINT UNSIGNED CHECK (star_rating BETWEEN 1 AND 5),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    CONSTRAINT fk_hotel_address FOREIGN KEY (address_id) 
        REFERENCES address (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Таблица заметок
CREATE TABLE IF NOT EXISTS hotel_note (
    id BIGINT NOT NULL AUTO_INCREMENT,
    hotel_id BIGINT NOT NULL,
    note TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    CONSTRAINT fk_note_hotel FOREIGN KEY (hotel_id) 
        REFERENCES hotel (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Индексы
CREATE INDEX idx_hotel_name ON hotel(name);
CREATE INDEX idx_hotel_stars ON hotel(star_rating);
CREATE INDEX idx_address_city ON address(city_id);
CREATE INDEX idx_note_hotel ON hotel_note(hotel_id);