freedakgmail
32 lines
1.6 KiB
SQL
32 lines
1.6 KiB
SQL
-- 多租户：给更多实体添加 org_id 关联
|
|
|
|
-- 分类表
|
|
ALTER TABLE categories ADD COLUMN IF NOT EXISTS org_id UUID REFERENCES organizations(id);
|
|
CREATE INDEX IF NOT EXISTS idx_categories_org ON categories(org_id);
|
|
|
|
-- 公文模板表
|
|
ALTER TABLE document_templates ADD COLUMN IF NOT EXISTS org_id UUID REFERENCES organizations(id);
|
|
CREATE INDEX IF NOT EXISTS idx_doc_templates_org ON document_templates(org_id);
|
|
|
|
-- 分析报告模板表（可能不存在，安全跳过）
|
|
DO $$ BEGIN
|
|
IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'analysis_report_templates') THEN
|
|
ALTER TABLE analysis_report_templates ADD COLUMN IF NOT EXISTS org_id UUID REFERENCES organizations(id);
|
|
CREATE INDEX IF NOT EXISTS idx_analysis_templates_org ON analysis_report_templates(org_id);
|
|
END IF;
|
|
END $$;
|
|
|
|
-- 部门表
|
|
ALTER TABLE departments ADD COLUMN IF NOT EXISTS org_id UUID REFERENCES organizations(id);
|
|
CREATE INDEX IF NOT EXISTS idx_departments_org ON departments(org_id);
|
|
|
|
-- 将现有数据关联到科技局（默认机构）
|
|
UPDATE categories SET org_id = 'a0000000-0000-0000-0000-000000000001' WHERE org_id IS NULL;
|
|
UPDATE document_templates SET org_id = 'a0000000-0000-0000-0000-000000000001' WHERE org_id IS NULL;
|
|
DO $$ BEGIN
|
|
IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'analysis_report_templates') THEN
|
|
UPDATE analysis_report_templates SET org_id = 'a0000000-0000-0000-0000-000000000001' WHERE org_id IS NULL;
|
|
END IF;
|
|
END $$;
|
|
UPDATE departments SET org_id = 'a0000000-0000-0000-0000-000000000001' WHERE org_id IS NULL;
|