seecoder-devcloud/web/src/main/resources/sql/init.sql

create table if not exists devcloud.project
(
    id             int          not null
        primary key,
    name           varchar(128) null,
    description    varchar(512) null,
    git_remote_url varchar(512) not null
)
    charset = utf8;

create table if not exists devcloud.commit
(
    id              varchar(127) not null
        primary key,
    message         text         null,
    title           text         null,
    timestamp       varchar(31)  null,
    gitlab_username varchar(31)  null,
    related_id      int          null,
    project_id      int          null,
    related_type    varchar(11)  null,
    constraint commit_project_id_fk
        foreign key (project_id) references devcloud.project (id)
);

create table if not exists devcloud.pipeline
(
    id            int auto_increment
        primary key,
    project_id    int         null,
    name          varchar(47) null,
    config_json   text        null,
    template_name varchar(31) null,
    constraint pipeline_project_id_fk
        foreign key (project_id) references devcloud.project (id)
            on delete set null
);

create table if not exists devcloud.deployment
(
    id            int auto_increment
        primary key,
    pipeline_id   int         not null,
    namespace     varchar(63) null,
    deploy_name   varchar(63) null,
    deployed_time timestamp   null,
    access_url    text        null,
    constraint deployment_pipeline_id_uindex
        unique (pipeline_id),
    constraint deployment_pipeline_id_fk
        foreign key (pipeline_id) references devcloud.pipeline (id)
);

create table if not exists devcloud.test_case
(
    id         int auto_increment
        primary key,
    project_id int                  not null,
    title      varchar(63)          null,
    pass       tinyint(1) default 0 not null
);

create table if not exists devcloud.test_step
(
    id           int auto_increment
        primary key,
    test_case_id int  null,
    description  text null,
    constraint test_step_test_case_id_fk
        foreign key (test_case_id) references devcloud.test_case (id)
            on delete set null
);

create table if not exists devcloud.tree_log
(
    id        int auto_increment
        primary key,
    time      datetime    null,
    operation varchar(32) null,
    user_id   int         null,
    tree_id   int         null,
    user_name varchar(64) null
)
    charset = utf8;

create table if not exists devcloud.tree_node
(
    id           int auto_increment
        primary key,
    title        varchar(24)                           not null,
    description  varchar(512)                          null,
    type         varchar(32) default '0'               not null,
    priority     int         default 0                 null,
    state        varchar(32) default '0'               not null,
    time_to_take int         default 0                 not null,
    start_time   datetime    default CURRENT_TIMESTAMP not null,
    end_time     datetime                              null,
    leaf         tinyint(1)  default 0                 null,
    project_id   int                                   null,
    deep         int                                   null,
    parent_id    int                                   null,
    processor_id int                                   null
);

create table if not exists devcloud.user
(
    id          int         not null
        primary key,
    username    varchar(31) null,
    phone       varchar(11) null,
    email       varchar(63) null,
    role        varchar(11) null,
    with_gitlab tinyint(1)  null
);

create table if not exists devcloud.bug_list
(
    id           int auto_increment
        primary key,
    title        varchar(31)                         null,
    description  text                                null,
    time_to_take mediumtext                          null,
    start_time   timestamp default CURRENT_TIMESTAMP not null,
    end_time     timestamp                           null,
    priority     varchar(10)                         null,
    state        varchar(10)                         null,
    project_id   int                                 null,
    processor_id int                                 null,
    constraint bug_list_project_id_fk
        foreign key (project_id) references devcloud.project (id),
    constraint bug_list_user_id_fk
        foreign key (processor_id) references devcloud.user (id)
);

create table if not exists devcloud.pipeline_history
(
    id          int auto_increment
        primary key,
    pipeline_id int                                 null,
    start_time  timestamp default CURRENT_TIMESTAMP not null,
    user_id     int                                 null,
    result      varchar(15)                         null,
    details     text                                null,
    constraint pipeline_history_pipeline_id_fk
        foreign key (pipeline_id) references devcloud.pipeline (id),
    constraint pipeline_history_user_id_fk
        foreign key (user_id) references devcloud.user (id)
);