审查视图

sql/1.3.0.sql 3.7 KB
1
-- 增加指标分类字段, 默认0
tangxvhui authored
2 3 4
ALTER TABLE public.evaluation_item_used
    ADD indicator_type int4 NOT NULL DEFAULT 0;
5 6 7
COMMENT ON COLUMN public.evaluation_item_used.indicator_type IS '指标类型';

-- 增加项目表-任务负责人ID
tangxvhui authored
8 9 10
ALTER TABLE public.evaluation_project
    ADD principal_id text NULL;
11 12 13
COMMENT ON COLUMN public.evaluation_project.principal_id IS '任务负责人ID';

-- 增加评估Cache数据缓存表-任务里程碑JSON
tangxvhui authored
14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92
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)
);
tangxvhui authored
93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145
-- 公司添加超级管理员(不存在时插入新数据)
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);