wren
79 lines
2.8 KiB
PL/PgSQL
79 lines
2.8 KiB
PL/PgSQL
-- 入口模块租户关系表
|
|
-- 目的：
|
|
-- 1. 替代 leaudit_entry_modules.areas 的硬编码地区数组
|
|
-- 2. 支持新增自定义租户后为入口模块直接分配 tenant_code
|
|
-- 3. 保留旧 areas 字段作为兼容读写镜像，逐步退出
|
|
|
|
BEGIN;
|
|
|
|
CREATE TABLE IF NOT EXISTS leaudit_entry_module_tenants (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
entry_module_id BIGINT NOT NULL,
|
|
tenant_code VARCHAR(64) NOT NULL,
|
|
tenant_name VARCHAR(128) NULL,
|
|
is_enabled BOOLEAN NOT NULL DEFAULT TRUE,
|
|
sort_order INT NOT NULL DEFAULT 0,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
deleted_at TIMESTAMPTZ NULL,
|
|
UNIQUE (entry_module_id, tenant_code)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_entry_module_tenants_module
|
|
ON leaudit_entry_module_tenants(entry_module_id, sort_order, id)
|
|
WHERE deleted_at IS NULL;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_entry_module_tenants_tenant
|
|
ON leaudit_entry_module_tenants(tenant_code, is_enabled, sort_order)
|
|
WHERE deleted_at IS NULL;
|
|
|
|
COMMENT ON TABLE leaudit_entry_module_tenants IS '入口模块与租户的多对多配置关系表';
|
|
COMMENT ON COLUMN leaudit_entry_module_tenants.entry_module_id IS '入口模块ID';
|
|
COMMENT ON COLUMN leaudit_entry_module_tenants.tenant_code IS '租户编码，引用 sys_tenants.tenant_code';
|
|
COMMENT ON COLUMN leaudit_entry_module_tenants.tenant_name IS '配置快照名称，便于前端直接展示';
|
|
|
|
INSERT INTO leaudit_entry_module_tenants (
|
|
entry_module_id, tenant_code, tenant_name, is_enabled, sort_order, created_at, updated_at, deleted_at
|
|
)
|
|
SELECT
|
|
em.id,
|
|
alias_map.tenant_code,
|
|
COALESCE(t.tenant_name, area_item->>'area') AS tenant_name,
|
|
COALESCE((area_item->>'enabled')::boolean, TRUE) AS is_enabled,
|
|
COALESCE((area_item->>'sort_order')::int, 0) AS sort_order,
|
|
NOW(),
|
|
NOW(),
|
|
NULL
|
|
FROM leaudit_entry_modules em
|
|
CROSS JOIN LATERAL jsonb_array_elements(COALESCE(em.areas, '[]'::jsonb)) AS area_item
|
|
LEFT JOIN LATERAL (
|
|
SELECT a.tenant_code
|
|
FROM sys_tenant_aliases a
|
|
WHERE a.alias_value = area_item->>'area'
|
|
AND a.deleted_at IS NULL
|
|
AND a.is_enabled = TRUE
|
|
ORDER BY
|
|
CASE a.alias_type
|
|
WHEN 'DISPLAY' THEN 1
|
|
WHEN 'LEGACY_AREA' THEN 2
|
|
WHEN 'LEGACY_REGION' THEN 3
|
|
ELSE 9
|
|
END ASC,
|
|
a.id ASC
|
|
LIMIT 1
|
|
) alias_map ON TRUE
|
|
LEFT JOIN sys_tenants t
|
|
ON t.tenant_code = alias_map.tenant_code
|
|
AND t.deleted_at IS NULL
|
|
WHERE em.deleted_at IS NULL
|
|
AND alias_map.tenant_code IS NOT NULL
|
|
ON CONFLICT (entry_module_id, tenant_code) DO UPDATE
|
|
SET
|
|
tenant_name = EXCLUDED.tenant_name,
|
|
is_enabled = EXCLUDED.is_enabled,
|
|
sort_order = EXCLUDED.sort_order,
|
|
updated_at = NOW(),
|
|
deleted_at = NULL;
|
|
|
|
COMMIT;
|