DCL, DDL

권한 부여(DCL)

GRANT all ON DATABASE Test_Structure TO Tester;

UUID 사용을 위한 추가제공 모듈 설치

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

Table 생성 (DDL)

drop table if exists maker_review cascade;

drop table if exists tester_review cascade;

drop table if exists apply_information cascade;

drop table if exists mission cascade;

drop table if exists maker cascade;

drop table if exists tester cascade;

create table maker
(
    maker_id         uuid   not null
        primary key,
    create_date_time timestamp,
    update_date_time timestamp,
    account_number   varchar(255),
    company          varchar(255),
    email            varchar(255)
        constraint uk_syexsb24b7d8hxa5j748ep97s
            unique,
    nickname         varchar(255)
        constraint uk_r7uxyne7x2wuaqvqwddsr03xp
            unique,
    password         varchar(255),
    phone_number     varchar(255)
        constraint uk_qjvn08t4ff8q5recbyjjxgxo3
            unique,
    point            bigint not null
);

create table mission
(
    mission_id             uuid    not null
        primary key,
    create_date_time       timestamp,
    update_date_time       timestamp,
    content                text,
    imageurl               varchar(255),
    limit_performer        integer not null,
    duration_time_end      date,
    duration_time_start    date,
    recruitment_time_end   date,
    recruitment_time_start date,
    reward                 bigint  not null,
    status                 varchar(255),
    title                  varchar(255),
    maker_id               uuid
        constraint fkkgklv7xdw0lr22u4iex1354t0
            references maker
);

create table tester
(
    tester_id        uuid   not null
        primary key,
    create_date_time timestamp,
    update_date_time timestamp,
    account_number   varchar(255),
    email            varchar(255)
        constraint uk_qkbporqaverlimamful70xnyd
            unique,
    intro_message    varchar(255),
    nickname         varchar(255)
        constraint uk_245tvwkexvigvn0ugkmuaause
            unique,
    password         varchar(255),
    phone_number     varchar(255)
        constraint uk_dre4nxwgk1x3ssxeus28govwc
            unique,
    point            bigint not null
);

create table apply_information
(
    apply_information_id uuid not null
        primary key,
    create_date_time     timestamp,
    update_date_time     timestamp,
    approve_time         timestamp,
    execution_time       timestamp,
    register_time        timestamp,
    status               varchar(255),
    mission_id           uuid
        constraint fk87uwqaxymgmy1q8rtwnwa0iu7
            references mission,
    tester_id            uuid
        constraint fknecgo25p9lrrrqsi40rcngkwi
            references tester
);

create table maker_review
(
    maker_review_id      uuid    not null
        primary key,
    create_date_time     timestamp,
    update_date_time     timestamp,
    comment              varchar(255),
    star_point           integer not null,
    apply_information_id uuid
        constraint fkhink1l9rvexsas3dp9khi4fy8
            references apply_information
);

create table tester_review
(
    tester_review_id     uuid    not null
        primary key,
    create_date_time     timestamp,
    update_date_time     timestamp,
    comment              varchar(255),
    star_point           integer not null,
    apply_information_id uuid
        constraint fkfeueajv8qhr9ta1lbpmd9qs3a
            references apply_information
);

DML

Test Data 생성