init.sql

-- 数据仓库表,用来配置数据仓库的使用方式,例如网络硬盘,云存储,本地存储等等
create table if not exists d_storage
(
    "id"          serial8 primary key,
    "unid"        character varying(36)  not null DEFAULT uuid_generate_v1(),
    "create_time" TIMESTAMP              NOT NULL DEFAULT now(),
    "create_user" int8,

    "name"        character varying(64)  not null,
    "type"        int4                   not null,
    "path"        character varying(512) not null
);
comment on table d_storage is '数据仓库表';
comment on column "d_storage"."name" is '数据仓库名称';
comment on column "d_storage"."type" is '数据仓库类型';
comment on column "d_storage"."path" is '路径或使用方式,配合类型使用';
create index if not exists "d_storage_unid_idx" on "d_storage" using btree ("unid");

-- 图包表,一个图包包含多张图片
create table if not exists d_pack
(
    "id"          serial8 primary key,
    "unid"        character varying(36) not null DEFAULT uuid_generate_v1(),
    "create_time" TIMESTAMP             NOT NULL DEFAULT now(),
    "create_user" int8,

    "storage_id"  int8                  not null,
    "task_id"     int8,
    "name"        character varying(64) not null,
    "type"        int4                  not null,
    "status"      int4                  not null default 0

);
comment on table "d_pack" is '图包表';
comment on column "d_pack"."storage_id" is '对应数据仓库id';
comment on column "d_pack"."name" is '图包名称';
comment on column "d_pack"."status" is '图包状态';
comment on column "d_pack"."type" is '图包类型';
comment on column "d_pack"."task_id" is '关联的任务';
create index if not exists "d_pack_unid_idx" on "d_pack" using btree ("unid");
create index if not exists "d_pack_storage_id_idx" on "d_pack" using btree ("storage_id");

-- 图片表,图片路径为 /d_pack.unid/d_pic.name
create table if not exists d_pic
(
    "id"          serial8 primary key,
    "unid"        character varying(36)  not null DEFAULT uuid_generate_v1(),
    "create_time" TIMESTAMP              NOT NULL DEFAULT now(),

    "name"        character varying(128) not null,
    "storage_id"  int8                   not null,
    "pack_id"     int8                   not null,
    "status"      int4                   not null default 0
);
comment on table "d_pic" is '图片表';
comment on column "d_pic"."name" is '图片名称';
comment on column "d_pic"."storage_id" is '对应数据仓库id';
comment on column "d_pic"."pack_id" is '对应图片包id';
comment on column "d_pic"."status" is '状态';
create index if not exists "d_pic_unid_idx" on "d_pic" using btree ("unid");
create index if not exists "d_pic_storage_id_idx" on "d_pic" using btree ("storage_id");
create index if not exists "d_pic_pack_id_idx" on "d_pic" using btree ("pack_id");

-- 字典表
create table if not exists s_dict
(
    "id"          serial8 primary key,
    "unid"        character varying(36) not null DEFAULT uuid_generate_v1(),
    "create_time" TIMESTAMP             NOT NULL DEFAULT now(),
    "create_user" int8,

    "key"         character varying(36) not null,
    "value"       int4                  not null,
    "description" character varying(128)
);
comment on table "s_dict" is '字典表';
comment on column "s_dict"."key" is '关键字';
comment on column "s_dict"."value" is '值';
comment on column "s_dict"."description" is '描述';
create index if not exists "s_dict_unid_idx" on "s_dict" using btree ("unid");
create index if not exists "s_dict_key_idx" on "s_dict" using btree ("key");
alter table s_dict
    add constraint s_dict_key_value_unique unique (key, value);

-- 任务表,任务表可以包含多个图片包
create table if not exists d_task
(
    "id"                    serial8 primary key,
    "unid"                  character varying(36) not null DEFAULT uuid_generate_v1(),
    "create_time"           TIMESTAMP             NOT NULL DEFAULT now(),
    "create_user"           int8,

    "name"                  character varying(64) not null,
    "type"                  int4                  not null,
    "label_type"            int4                  not null,
    "account_id"            int8,
    "contractor_manager_id" int8,
    "manager_id"            int8,
    "assign_time"           timestamp,
    "finish_time"           timestamp,
    "in_inspector_id"       int8                  not null,
    "description"           character varying(1024),
    "status"                int4                  not null default 0

);
comment on table "d_task" is '任务/项目表';
comment on column "d_task"."name" is '任务名称';
comment on column "d_task"."type" is '任务类型';
comment on column "d_task"."label_type" is '标注方式';
comment on column "d_task"."account_id" is '所属账户/公司';
comment on column "d_task"."contractor_manager_id" is '外包管理员';
comment on column "d_task"."manager_id" is '任务管理员';
comment on column "d_task"."in_inspector_id" is '内部质检员';
comment on column "d_task"."description" is '任务描述';
comment on column "d_task"."status" is '任务状态';
comment on column "d_task"."assign_time" is '任务分配时间';
comment on column "d_task"."finish_time" is '任务完成时间';
create index if not exists "d_task_unid_idx" on "d_task" using btree ("unid");
create index if not exists "d_task_account_id_idx" on "d_task" using btree ("account_id");
create index if not exists "d_task_contractor_manager_id_idx" on "d_task" using btree ("contractor_manager_id");
create index if not exists "d_task_manager_id_idx" on "d_task" using btree ("manager_id");
create index if not exists "d_task_assign_time_idx" on "d_task" using btree ("assign_time");
create index if not exists "d_task_finish_time_idx" on "d_task" using btree ("finish_time");

-- 子任务表
create table if not exists d_sub_task
(
    "id"               serial8 primary key,
    "unid"             character varying(36) not null DEFAULT uuid_generate_v1(),
    "create_time"      TIMESTAMP             NOT NULL DEFAULT now(),
    "create_user"      int8,

    "pic_id"           int8                  not null,
    "pack_id"          int8                  not null,
    "task_id"          int8                  not null,
    "in_inspector_id"  int8,
    "out_inspector_id" int8,
    "annotator_id"     int8,
    "annotate_time"    timestamp,
    "label_result"     character varying(1024),
    "in_status"        int4                  not null default 1,
    "out_status"       int4                  not null default 1,
    "status"           int4                  not null default 1,
    "direction"        int4,
    "person_unid"      varchar(36)
);
comment on table "d_sub_task" is '子任务表';
comment on column "d_sub_task"."pic_id" is '对应图片id';
comment on column "d_sub_task"."task_id" is '对应任务id';
comment on column "d_sub_task"."in_inspector_id" is '内部质检员';
comment on column "d_sub_task"."out_inspector_id" is '外部质检员';
comment on column "d_sub_task"."annotator_id" is '标注员';
comment on column "d_sub_task"."label_result" is '标注结果';
comment on column "d_sub_task"."in_status" is '内部质检状态';
comment on column "d_sub_task"."out_status" is '外部质检状态';
comment on column "d_sub_task"."status" is '子任务状态';
comment on column "d_sub_task"."direction" is '方向';
comment on column "d_sub_task"."person_unid" is '人员Unid,reid标注中用来表示同一个人';
create index if not exists "d_sub_task_unid_idx" on "d_sub_task" using btree ("unid");
create index if not exists "d_sub_task_pack_id_idx" on "d_sub_task" using btree ("pack_id");
create index if not exists "d_sub_task_pack_id_person_unid_status_idx" on "d_sub_task" using btree ("pic_id", "person_unid", "status");
create index if not exists "d_sub_task_task_id_idx" on "d_sub_task" using btree ("task_id");
create index if not exists "d_sub_task_in_inspector_id_idx" on "d_sub_task" using btree ("in_inspector_id");
create index if not exists "d_sub_task_out_inspector_id_idx" on "d_sub_task" using btree ("out_inspector_id");
create index if not exists "d_sub_task_annotator_id_idx" on "d_sub_task" using btree ("annotator_id");
create index if not exists "d_sub_task_person_unid_status_idx" on "d_sub_task" using btree ("person_unid", "status");

-- 用户表
create table if not exists s_user
(
    "id"              serial8 primary key,
    "unid"            character varying(36) not null DEFAULT uuid_generate_v1(),
    "create_time"     TIMESTAMP             NOT NULL DEFAULT now(),
    "create_user"     int8,

    "username"        character varying(20) not null,
    "password"        character varying(20) not null,
    "type"            int4                  not null default -1,
    "name"            character varying(20),
    "mail"            character varying(64),
    "tel"             character varying(20),
    "account_id"      int8,
    "last_login_time" TIMESTAMP
);
comment on table "s_user" is '用户表';
comment on column "s_user"."username" is '用户名';
comment on column "s_user"."password" is '密码';
comment on column "s_user"."type" is '用户类型';
comment on column "s_user"."name" is '姓名';
comment on column "s_user"."mail" is '邮箱';
comment on column "s_user"."tel" is '电话';
comment on column "s_user"."account_id" is '所属账户/公司';
comment on column "s_user"."last_login_time" is '最后登录时间';
alter table s_user
    add constraint s_user_username_unique unique (username);
create index if not exists "s_user_unid_idx" on "s_user" using btree ("unid");
create index if not exists "s_user_username_idx" on "s_user" using btree ("username");
create index if not exists "s_user_account_id_idx" on "s_user" using btree ("account_id");

-- 账户/公司表
create table if not exists s_account
(
    "id"          serial8 primary key,
    "unid"        character varying(36)  not null DEFAULT uuid_generate_v1(),
    "create_time" TIMESTAMP              NOT NULL DEFAULT now(),
    "create_user" int8,

    "name"        character varying(128) not null,
    "manager_id"  int8,
    "description" character varying(128),
    "status"      int4                   not null default 1
);
comment on table "s_account" is '账户/公司表';
comment on column "s_account"."name" is '账户/公司名称';
comment on column "s_account"."manager_id" is '管理员';
comment on column "s_account"."description" is '描述';


-- 图包-标签表
create table if not exists r_pack_tag
(
    "id"          serial8 primary key,
    "create_time" TIMESTAMP NOT NULL DEFAULT now(),

    "pack_id"     int8      not null,
    "tag"         int4      not null
);
comment on table "r_pack_tag" is '图包标签关联表';
comment on column "r_pack_tag"."pack_id" is '图包id';
comment on column "r_pack_tag"."tag" is '标签值';
create index if not exists "r_pack_tag_pack_id_idx" on "r_pack_tag" using btree ("pack_id");
create index if not exists "r_pack_tag_tag_idx" on "r_pack_tag" using btree ("tag");

-- 日志表
CREATE TABLE IF NOT EXISTS s_log
(
    "id"           serial8 PRIMARY KEY,
    "create_time"  TIMESTAMP NOT NULL DEFAULT now(),

    "operate_user" int8      NOT NULL,
    "operate_date" DATE      NOT NULL DEFAULT now(),
    "operate_type" int4,
    "operate"      VARCHAR(1024)
);
comment on table "s_log" is '日志表';
comment on column "s_log"."operate_user" is '用户';
comment on column "s_log"."operate_date" is '日期';
comment on column "s_log"."operate_type" is '操作代码';
comment on column "s_log"."operate" is '具体操作';
create index if not exists "s_log_date_user_idx" on "s_log" using btree ("operate_date", "operate_user");
create index if not exists "s_log_user_idx" on "s_log" using btree ("operate_user");

-- 禁止用户表
create table if not exists s_forbidden_user
(
    "id"          serial8,
    "user_id"     varchar(64) DEFAULT NULL,
    "status"      int2        DEFAULT NULL,
    "deleted"     int2        DEFAULT NULL,
    "name"        varchar(32) DEFAULT NULL,
    "remark"      varchar(64) DEFAULT NULL,
    "url"         varchar(32) DEFAULT NULL,
    "create_time" timestamp   DEFAULT NULL,
    "update_time" timestamp   DEFAULT NULL,
    CONSTRAINT "s_forbidden_user_pkey" PRIMARY KEY ("id")
);
COMMENT ON COLUMN "public"."s_forbidden_user"."id" IS 'id';
COMMENT ON COLUMN "public"."s_forbidden_user"."user_id" IS '用户id';
COMMENT ON COLUMN "public"."s_forbidden_user"."status" IS '状态，0：禁止登陆，查询，等所有操作';
COMMENT ON COLUMN "public"."s_forbidden_user"."deleted" IS '是否删除：0：未删除，1：已删除';
COMMENT ON COLUMN "public"."s_forbidden_user"."name" IS '用户名字';
COMMENT ON COLUMN "public"."s_forbidden_user"."remark" IS '备注';
COMMENT ON COLUMN "public"."s_forbidden_user"."url" IS '请求接口';
COMMENT ON COLUMN "public"."s_forbidden_user"."create_time" IS '创建时间';
COMMENT ON COLUMN "public"."s_forbidden_user"."update_time" IS '修改时间';
COMMENT ON TABLE "public"."s_forbidden_user" IS '禁止用户表';
create index if not exists "indx_user_id" on "s_forbidden_user" using btree ("user_id");


-- 初始数据
insert into s_user(username, password, type, name, account_id)
values ('vion', 'viontech', 0, '超级管理员', 1);
insert into s_account(name, manager_id, description)
values ('vion', 1, 'vion');
insert into s_dict(key, value, description)
values ('userType', 0, '超级管理员');
insert into s_dict(key, value, description)
values ('userType', 1, '项目经理');
insert into s_dict(key, value, description)
values ('userType', 2, '内部质检员');
insert into s_dict(key, value, description)
values ('userType', 3, '外部管理员');
insert into s_dict(key, value, description)
values ('userType', 4, '外部质检员');
insert into s_dict(key, value, description)
values ('userType', 5, '外部标注员');
insert into s_dict(key, value, description)
values ('userType', 6, '兼职标注员');

insert into s_dict(key, value, description)
values ('packStatus', 1, '待完成');
insert into s_dict(key, value, description)
values ('packStatus', 2, '审核中');
insert into s_dict(key, value, description)
values ('packStatus', 3, '审核驳回');
insert into s_dict(key, value, description)
values ('packStatus', 4, '审核通过');

insert into s_dict(key, value, description)
values ('reidOperateType', 1, 'REID合并图片');
insert into s_dict(key, value, description)
values ('reidOperateType', 2, 'REID合并人');
insert into s_dict(key, value, description)
values ('reidOperateType', 3, 'REID合并图片到人');
insert into s_dict(key, value, description)
values ('reidOperateType', 4, 'REID删除');
insert into s_dict(key, value, description)
values ('reidOperateType', 5, 'REID完成标注');


-- 外键,正式环境不执行
alter table s_user
    add FOREIGN KEY ("account_id") REFERENCES "public"."s_account" ("id");

alter table d_sub_task
    add FOREIGN KEY ("task_id") REFERENCES "public"."d_task" ("id");
alter table d_sub_task
    add FOREIGN KEY ("pic_id") REFERENCES "public"."d_pic" ("id");

alter table d_task
    add FOREIGN KEY ("account_id") REFERENCES "public"."s_account" ("id");

alter table d_pic
    add FOREIGN KEY ("storage_id") REFERENCES "public"."d_storage" ("id");
alter table d_pic
    add FOREIGN KEY ("pack_id") REFERENCES "public"."d_pack" ("id");

alter table d_pack
    add FOREIGN KEY ("storage_id") REFERENCES "public"."d_storage" ("id");
alter table d_pack
    add FOREIGN KEY ("task_id") REFERENCES "public"."d_task" ("id");

alter table r_pack_tag
    add FOREIGN KEY ("pack_id") REFERENCES "public"."d_pack" ("id");