-- 增加指标分类字段, 默认0 ALTER TABLE public.evaluation_item_used ADD indicator_type int4 NOT NULL DEFAULT 0; COMMENT ON COLUMN public.evaluation_item_used.indicator_type IS '指标类型'; -- 增加项目表-任务负责人ID ALTER TABLE public.evaluation_project ADD principal_id text NULL; COMMENT ON COLUMN public.evaluation_project.principal_id IS '任务负责人ID'; -- 增加评估Cache数据缓存表-任务里程碑JSON ALTER TABLE public.staff_assess_cache ADD assess_task_stages jsonb NULL; COMMENT ON COLUMN public.staff_assess_cache.assess_task_stages IS '任务里程碑内容'; -- 添加表 task CREATE TABLE public.task( id bigserial NOT NULL, created_at timestamptz NULL, updated_at timestamptz NULL, deleted_at timestamptz NULL, "name" text NULL, alias text NULL, company_id int8 NULL, leader jsonb NULL, status int8 NULL, "level" int8 NULL, level_name text NULL, related_user jsonb NULL, run_at int8 NULL, stop_at int8 NULL, anomaly int8 NULL DEFAULT 0, warn_flag int4 NULL, current_stage jsonb NULL, last_stage jsonb NULL, CONSTRAINT task_pkey PRIMARY KEY (id) ); -- 添加表 task_ignore CREATE TABLE public.task_ignore( id bigserial NOT NULL, task_id int8 NULL, user_id int8 NULL, created_at timestamptz NULL ); -- 添加表 task_level CREATE TABLE public.task_level( id bigserial NOT NULL, level_name text NULL, company_id int8 NULL, CONSTRAINT task_level_pkey PRIMARY KEY (id) ); -- 添加表 task_record CREATE TABLE public.task_record( id bigserial NOT NULL, company_id int8 NULL, staff_assess_id int8 NULL, task_id int8 NULL, task_category text NULL, task_name text NULL, task_alias text NULL, task_leader jsonb NULL, assist_level int8 NULL, assist_content text NULL, task_stages jsonb NULL, task_stage_check jsonb NULL, created_at timestamptz NULL, updated_at timestamptz NULL, deleted_at timestamptz NULL, anomaly_state int8 NULL DEFAULT 0, CONSTRAINT task_record_pkey PRIMARY KEY (id) ); -- 添加表 task_stage CREATE TABLE public.task_stage( id bigserial NOT NULL, task_id int8 NULL, created_at timestamptz NULL, updated_at timestamptz NULL, deleted_at timestamptz NULL, "name" text NULL, sort_by int8 NULL, plan_completed_at int8 NULL, real_completed_at int8 NULL, CONSTRAINT task_stage_pkey PRIMARY KEY (id) ); -- 公司添加超级管理员(不存在时插入新数据) INSERT INTO public."role"("name", "type", description, company_id, created_at, updated_at, deleted_at) SELECT '超级管理员', 2, '隐藏角色,拥有部分权限', 1, now(), now(), NULL WHERE NOT EXISTS ( SELECT 1 FROM public."role" WHERE "role".company_id = 1 AND "role"."type" = 2 AND "role".deleted_at ISNULL); -- ------------------------------------------------------------------------- -- 超级管理员关联用户(不存在时插入新数据) WITH temp_role AS ( SELECT id FROM public."role" WHERE "role".company_id = 1 AND "role"."type" = 2) INSERT INTO public."role_user"(role_id, user_id, company_id, created_at, updated_at, deleted_at) SELECT temp_role."id", 3242583253399296, 1, now(), now(), NULL FROM temp_role WHERE NOT EXISTS ( SELECT 1 FROM public."role_user" WHERE "role_user".company_id = 1 AND "role_user".user_id = 3242583253399296 AND "role_user".role_id = temp_role."id" AND "role_user".deleted_at ISNULL);