58 lines
1.9 KiB
PL/PgSQL
58 lines
1.9 KiB
PL/PgSQL
-- 许可管理单位权限优化 - 数据库迁移脚本
|
||
-- 版本: 1.0
|
||
-- 日期: 2025-11-19
|
||
|
||
BEGIN;
|
||
|
||
-- 1. 为service_departments表添加unit_level字段
|
||
ALTER TABLE service_departments
|
||
ADD COLUMN IF NOT EXISTS unit_level VARCHAR(20) DEFAULT 'unit'
|
||
CHECK (unit_level IN ('admin', 'municipal', 'district', 'unit'));
|
||
|
||
-- 2. 为service_departments表添加allowed_regions字段（市级单位可访问的行政区）
|
||
ALTER TABLE service_departments
|
||
ADD COLUMN IF NOT EXISTS allowed_regions TEXT;
|
||
|
||
-- 3. 为permit_sources表添加uploader_department_id字段
|
||
ALTER TABLE permit_sources
|
||
ADD COLUMN IF NOT EXISTS uploader_department_id uuid REFERENCES service_departments(id);
|
||
|
||
-- 4. 为permit_sources表添加bound_department_id字段
|
||
ALTER TABLE permit_sources
|
||
ADD COLUMN IF NOT EXISTS bound_department_id uuid REFERENCES service_departments(id);
|
||
|
||
-- 5. 创建索引以提高查询性能
|
||
CREATE INDEX IF NOT EXISTS idx_service_dept_unit_level
|
||
ON service_departments(unit_level);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_service_dept_parent_level
|
||
ON service_departments(parent_id, unit_level);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_permit_sources_bound_dept
|
||
ON permit_sources(bound_department_id);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_permit_sources_uploader
|
||
ON permit_sources(uploader_department_id);
|
||
|
||
-- 6. 数据迁移：根据现有grade设置unit_level
|
||
-- 市局管理员（grade >= 90）设置为admin
|
||
UPDATE service_departments
|
||
SET unit_level = 'admin'
|
||
WHERE grade >= 90;
|
||
|
||
-- 根节点且grade < 90的设置为district（区局子管理员）
|
||
UPDATE service_departments
|
||
SET unit_level = 'district'
|
||
WHERE parent_id IS NULL AND grade < 90;
|
||
|
||
-- 有父节点且grade < 90的设置为unit（区级单位）
|
||
UPDATE service_departments
|
||
SET unit_level = 'unit'
|
||
WHERE parent_id IS NOT NULL AND grade < 90;
|
||
|
||
-- 7. 为unit_level字段添加NOT NULL约束（在更新数据后）
|
||
ALTER TABLE service_departments
|
||
ALTER COLUMN unit_level SET NOT NULL;
|
||
|
||
COMMIT;
|