seecoder-devcloud/web/src/main/resources/SQLScripts/trees.sql

create database if not exists seecoder;
use seecoder;
create table tree_nodes
(
    id           int auto_increment,
    title        varchar(24)                        not null,
    description  varchar(512)                       null,
    type         int      default 0                 not null,
    priority     int      default 0                 null,
    state        int      default 0                 not null,
    processor    varchar(64)                        not null,
    time_to_take int      default 0                 not null,
    start_time   datetime default CURRENT_TIMESTAMP null,
    end_time     datetime                           null,
    is_leaf      int      default 0                 null,
    group_id     int                                null,
    constraint tree_nodes_id_uindex
        unique (id)
);

alter table tree_nodes
    add primary key (id);

create table messages
(
    message_id   int auto_increment,
    creator      varchar(64)  not null,
    tree_node_id int          null,
    detail       varchar(512) null,
    constraint messages_message_id_uindex
        unique (message_id),
    constraint tree_node_fk
        foreign key (tree_node_id) references tree_nodes (id)
            on update cascade on delete cascade
);

alter table messages
    add primary key (message_id);

create table tree_sides
(
    side_id   int auto_increment,
    parent_id int null,
    child_id  int null,
    constraint tree_sides_side_id_uindex
        unique (side_id),
    constraint child_fk
        foreign key (child_id) references tree_nodes (id)
            on update cascade on delete cascade,
    constraint parent_fk
        foreign key (parent_id) references tree_nodes (id)
            on update cascade on delete cascade
);

alter table tree_sides
    add primary key (side_id);