src/main/resources/schema.sql - master - ilyaBu/WebSpringJava

src/main/resources/schema.sql
95 строк2 KB
IliaBu
Update code
14 июл 2024, 15:26
14 июл 2024, 15:263414678
INSERT INTO roles(id, name)
SELECT 1, 'ROLE_ADMIN'
WHERE NOT EXISTS(
        SELECT id FROM roles WHERE id = 1
    );

INSERT INTO roles(id, name)
SELECT 2, 'ROLE_USER'
WHERE NOT EXISTS(
        SELECT id FROM roles WHERE id = 2
    );

INSERT INTO users(id, email, first_name, last_name, password)
SELECT 1, 'admin@mail.ru', 'Admin', 'Admin', '$2a$10$TSjcCZ50T/5W55N4HpGVfuN1lwow9zLEZxqqfgRzqRNdn6piq90D6'
WHERE NOT EXISTS(
        SELECT id FROM users WHERE id = 1
    );

INSERT INTO users_roles(user_id, role_id)
SELECT 1, 1
WHERE NOT EXISTS(
        SELECT role_id, user_id FROM users_roles WHERE user_id = 1 AND role_id = 1
    );

INSERT INTO users_roles(user_id, role_id)
SELECT 1, 2
WHERE NOT EXISTS(
        SELECT user_id, role_id FROM users_roles WHERE user_id = 1 AND role_id = 2
    );

SELECT setval('users_id_seq', (SELECT max(id) FROM users));
SELECT setval('roles_id_seq', (SELECT max(id) FROM roles));

CREATE TABLE IF NOT EXISTS PRICE_LIST
(
    ARTICUL      varchar(255),
    NAIMENOVANIE varchar(255),
    VALUTA       varchar(10),
    KODESKIDOK   varchar(20),
    MINPARTY     varchar(20),
    ZENA         varchar(20),
    SURCHAGE     varchar(20),
    NONAME       varchar(20)
);

CREATE TABLE IF NOT EXISTS CodesDiscounts
(
    disccd  varchar(255),
    discstk varchar(255),
    discvor varchar(255)
);

CREATE TABLE IF NOT EXISTS OstatokSkladMoskov
(
    articul      varchar(255),
    naimenovanie varchar(255),
    ostatok      varchar(255)
);

CREATE TABLE IF NOT EXISTS OstatokSkladCompany
(
    naimenovanie varchar(255),
    articul      varchar(255),
    ostatok      varchar(255)
);

CREATE TABLE IF NOT EXISTS DimensionsWeight
(
    articul varchar(255),
    weight  varchar(255),
    length  varchar(255),
    width   varchar(255),
    height  varchar(255)
);

CREATE TABLE IF NOT EXISTS Discount
(
    articul varchar(255),
    ostatok varchar(255)
);

CREATE TABLE IF NOT EXISTS Discount2
(
    articul varchar(255),
    ostatok varchar(255)
);

-- создаём триггер для удаления пробелов и лишних букв (колонка articul) из строк OstatokSkladCompany
CREATE OR REPLACE FUNCTION uppercase_trim_articulOstatokSkladCompany_on_insert() RETURNS trigger AS $$
BEGIN
    NEW.articul = UPPER(TRIM(NEW.articul));
    NEW.naimenovanie = UPPER(NEW.naimenovanie);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;;