| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115 |
- -- ============================================================
- -- AI 记录级数据权限:可见范围(visible_scope)+ 归属部门(owner_dept_id)
- --
- -- 适用表:ai_entry_agent / ai_expert_agent / ai_skill / ai_scenario_pack / ai_knowledge_base
- -- 执行方式:手工在目标库执行(项目无 Flyway/Liquibase),脚本幂等,可重复执行。
- --
- -- 存量语义保持:
- -- * 入口智能体为全局单例,空值统一按「全员」处理;
- -- * 其余表原口径为「内置 or 创建人本人」,故内置数据回填 '0'(全员/公开),
- -- 非内置数据回填 '2'(仅创建人/本人),避免存量私有数据被意外公开。
- -- * 已存在旧 visible_scope 值(如从 bizbuddy 迁移的库)时保留原值,不覆盖。
- -- ============================================================
- -- ---------- 1. 加列(先不加默认值,便于区分存量空值) ----------
- alter table ai_entry_agent add column if not exists owner_dept_id bigint;
- alter table ai_entry_agent add column if not exists visible_scope char(1);
- alter table ai_expert_agent add column if not exists owner_dept_id bigint;
- alter table ai_expert_agent add column if not exists visible_scope char(1);
- alter table ai_skill add column if not exists owner_dept_id bigint;
- alter table ai_skill add column if not exists visible_scope char(1);
- alter table ai_scenario_pack add column if not exists owner_dept_id bigint;
- alter table ai_scenario_pack add column if not exists visible_scope char(1);
- alter table ai_knowledge_base add column if not exists owner_dept_id bigint;
- alter table ai_knowledge_base add column if not exists visible_scope char(1);
- -- ---------- 2. 存量数据回填 ----------
- -- 入口智能体:全局单例,空值按全员处理
- update ai_entry_agent
- set visible_scope = '0'
- where visible_scope is null or visible_scope = '';
- -- 其余四类:内置 -> 全员/公开;非内置 -> 仅创建人/本人
- update ai_expert_agent
- set visible_scope = case when is_builtin = '1' then '0' else '2' end
- where visible_scope is null or visible_scope = '';
- update ai_skill
- set visible_scope = case when is_builtin = '1' then '0' else '2' end
- where visible_scope is null or visible_scope = '';
- update ai_scenario_pack
- set visible_scope = case when is_builtin = '1' then '0' else '2' end
- where visible_scope is null or visible_scope = '';
- update ai_knowledge_base
- set visible_scope = case when is_builtin = '1' then '0' else '2' end
- where visible_scope is null or visible_scope = '';
- -- 旧数据已指定「本部门」但归属部门为空:按创建人部门补齐
- update ai_entry_agent a
- set owner_dept_id = u.dept_id
- from perm_user u
- where a.owner_dept_id is null
- and a.visible_scope = '1'
- and a.create_by = u.user_id
- and u.dept_id is not null;
- update ai_expert_agent a
- set owner_dept_id = u.dept_id
- from perm_user u
- where a.owner_dept_id is null
- and a.visible_scope = '1'
- and a.create_by = u.user_id
- and u.dept_id is not null;
- update ai_skill a
- set owner_dept_id = u.dept_id
- from perm_user u
- where a.owner_dept_id is null
- and a.visible_scope = '1'
- and a.create_by = u.user_id
- and u.dept_id is not null;
- update ai_scenario_pack a
- set owner_dept_id = u.dept_id
- from perm_user u
- where a.owner_dept_id is null
- and a.visible_scope = '1'
- and a.create_by = u.user_id
- and u.dept_id is not null;
- update ai_knowledge_base a
- set owner_dept_id = u.dept_id
- from perm_user u
- where a.owner_dept_id is null
- and a.visible_scope = '1'
- and a.create_by = u.user_id
- and u.dept_id is not null;
- -- ---------- 3. 新数据默认值 ----------
- alter table ai_entry_agent alter column visible_scope set default '0';
- alter table ai_expert_agent alter column visible_scope set default '0';
- alter table ai_skill alter column visible_scope set default '0';
- alter table ai_scenario_pack alter column visible_scope set default '0';
- alter table ai_knowledge_base alter column visible_scope set default '0';
- -- ---------- 4. 列注释 ----------
- comment on column ai_entry_agent.owner_dept_id is '归属部门(可见范围=本部门时生效)';
- comment on column ai_entry_agent.visible_scope is '可见范围(0全员 1本部门 2仅创建人)';
- comment on column ai_expert_agent.owner_dept_id is '归属部门(可见范围=本部门时生效)';
- comment on column ai_expert_agent.visible_scope is '可见范围(0全员 1本部门 2仅创建人)';
- comment on column ai_skill.owner_dept_id is '归属部门(生效范围=本部门时生效)';
- comment on column ai_skill.visible_scope is '生效范围(0公开 1本部门 2仅本人)';
- comment on column ai_scenario_pack.owner_dept_id is '归属部门(生效范围=本部门时生效)';
- comment on column ai_scenario_pack.visible_scope is '生效范围(0公开 1本部门 2仅本人)';
- comment on column ai_knowledge_base.owner_dept_id is '归属部门(生效范围=本部门时生效)';
- comment on column ai_knowledge_base.visible_scope is '生效范围(0公开 1本部门 2仅本人)';
|