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 not null,
	"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");


-- 初始数据
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");

-- 禁止用户表
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");