47 lines
1.5 KiB
SQL
47 lines
1.5 KiB
SQL
-- ===========================================
|
|
-- 迁移: file-service files 表添加 tenant_id
|
|
-- 日期: 2026-02-07
|
|
-- ===========================================
|
|
|
|
-- 默认租户 ID
|
|
DO $$
|
|
DECLARE
|
|
default_tenant UUID := '00000000-0000-0000-0000-000000000001';
|
|
BEGIN
|
|
-- 1. 添加 tenant_id 列（如果不存在）
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM information_schema.columns
|
|
WHERE table_name = 'files' AND column_name = 'tenant_id'
|
|
) THEN
|
|
ALTER TABLE files ADD COLUMN tenant_id UUID NOT NULL DEFAULT default_tenant;
|
|
RAISE NOTICE 'Added tenant_id column to files table';
|
|
ELSE
|
|
RAISE NOTICE 'tenant_id column already exists in files table';
|
|
END IF;
|
|
|
|
-- 2. 回填现有记录
|
|
UPDATE files SET tenant_id = default_tenant WHERE tenant_id IS NULL;
|
|
|
|
-- 3. 创建索引（如果不存在）
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM pg_indexes WHERE indexname = 'idx_files_tenant_id'
|
|
) THEN
|
|
CREATE INDEX idx_files_tenant_id ON files(tenant_id);
|
|
RAISE NOTICE 'Created index idx_files_tenant_id';
|
|
END IF;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM pg_indexes WHERE indexname = 'idx_files_tenant'
|
|
) THEN
|
|
CREATE INDEX idx_files_tenant ON files(tenant_id);
|
|
RAISE NOTICE 'Created index idx_files_tenant';
|
|
END IF;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM pg_indexes WHERE indexname = 'idx_files_tenant_user'
|
|
) THEN
|
|
CREATE INDEX idx_files_tenant_user ON files(tenant_id, user_id);
|
|
RAISE NOTICE 'Created index idx_files_tenant_user';
|
|
END IF;
|
|
END $$;
|