548 lines
24 KiB
PL/PgSQL
548 lines
24 KiB
PL/PgSQL
-- ===================================================================
|
||
-- 改进的全局配置表设计 - 主表+翻译表模式
|
||
-- 更科学的多语言支持架构
|
||
-- ===================================================================
|
||
|
||
-- 1. 主配置表 (ak_global_config)
|
||
CREATE TABLE IF NOT EXISTS public.ak_global_config (
|
||
id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
|
||
config_key VARCHAR(100) NOT NULL UNIQUE,
|
||
config_type VARCHAR(50) DEFAULT 'string',
|
||
config_category VARCHAR(50) DEFAULT 'general',
|
||
default_value TEXT,
|
||
is_translatable BOOLEAN DEFAULT false,
|
||
is_active BOOLEAN DEFAULT true,
|
||
sort_order INTEGER DEFAULT 0,
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
||
);
|
||
|
||
-- 2. 配置翻译表 (ak_global_config_translations)
|
||
CREATE TABLE IF NOT EXISTS public.ak_global_config_translations (
|
||
id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
|
||
config_id UUID NOT NULL REFERENCES public.ak_global_config(id) ON DELETE CASCADE,
|
||
language_code VARCHAR(10) NOT NULL,
|
||
translated_value TEXT NOT NULL,
|
||
is_active BOOLEAN DEFAULT true,
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
UNIQUE(config_id, language_code)
|
||
);
|
||
|
||
-- 3. 语言表 (检查并适配现有表结构)
|
||
DO $$
|
||
BEGIN
|
||
-- 如果 ak_languages 表不存在，则创建
|
||
IF NOT EXISTS (
|
||
SELECT 1 FROM information_schema.tables
|
||
WHERE table_schema = 'public' AND table_name = 'ak_languages'
|
||
) THEN
|
||
CREATE TABLE public.ak_languages (
|
||
id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
|
||
code VARCHAR(10) NOT NULL UNIQUE,
|
||
name VARCHAR(100) NOT NULL,
|
||
native_name VARCHAR(100) NOT NULL,
|
||
is_active BOOLEAN DEFAULT true,
|
||
is_default BOOLEAN DEFAULT false,
|
||
sort_order INTEGER DEFAULT 0,
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
||
);
|
||
RAISE NOTICE '已创建 ak_languages 表';
|
||
ELSE
|
||
RAISE NOTICE 'ak_languages 表已存在，将适配现有结构';
|
||
END IF;
|
||
END $$;
|
||
|
||
-- 创建索引
|
||
CREATE INDEX IF NOT EXISTS idx_ak_global_config_key ON public.ak_global_config(config_key);
|
||
CREATE INDEX IF NOT EXISTS idx_ak_global_config_active ON public.ak_global_config(is_active);
|
||
CREATE INDEX IF NOT EXISTS idx_ak_global_config_category ON public.ak_global_config(config_category);
|
||
CREATE INDEX IF NOT EXISTS idx_ak_global_config_translatable ON public.ak_global_config(is_translatable);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_ak_global_config_translations_config ON public.ak_global_config_translations(config_id);
|
||
CREATE INDEX IF NOT EXISTS idx_ak_global_config_translations_lang ON public.ak_global_config_translations(language_code);
|
||
CREATE INDEX IF NOT EXISTS idx_ak_global_config_translations_active ON public.ak_global_config_translations(is_active);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_ak_languages_code ON public.ak_languages(code);
|
||
CREATE INDEX IF NOT EXISTS idx_ak_languages_active ON public.ak_languages(is_active);
|
||
|
||
-- ===================================================================
|
||
-- 初始化语言数据
|
||
-- 注意：适配现有的 ak_languages 表结构
|
||
-- ===================================================================
|
||
|
||
-- 首先检查 ak_languages 表是否有 sort_order 字段，如果没有则添加
|
||
DO $$
|
||
BEGIN
|
||
IF NOT EXISTS (
|
||
SELECT 1 FROM information_schema.columns
|
||
WHERE table_schema = 'public'
|
||
AND table_name = 'ak_languages'
|
||
AND column_name = 'sort_order'
|
||
) THEN
|
||
ALTER TABLE public.ak_languages ADD COLUMN sort_order INTEGER DEFAULT 0;
|
||
RAISE NOTICE '已为 ak_languages 表添加 sort_order 字段';
|
||
END IF;
|
||
|
||
IF NOT EXISTS (
|
||
SELECT 1 FROM information_schema.columns
|
||
WHERE table_schema = 'public'
|
||
AND table_name = 'ak_languages'
|
||
AND column_name = 'is_default'
|
||
) THEN
|
||
ALTER TABLE public.ak_languages ADD COLUMN is_default BOOLEAN DEFAULT false;
|
||
RAISE NOTICE '已为 ak_languages 表添加 is_default 字段';
|
||
END IF;
|
||
END $$;
|
||
|
||
-- 插入语言数据（使用安全的插入方式）
|
||
INSERT INTO public.ak_languages (code, name, native_name, is_default, sort_order) VALUES
|
||
('zh', 'Chinese Simplified', '简体中文', true, 1),
|
||
('en', 'English', 'English', false, 2),
|
||
('tw', 'Chinese Traditional', '繁體中文', false, 3),
|
||
('ja', 'Japanese', '日本語', false, 4)
|
||
ON CONFLICT (code) DO UPDATE SET
|
||
name = EXCLUDED.name,
|
||
native_name = EXCLUDED.native_name,
|
||
is_default = EXCLUDED.is_default,
|
||
sort_order = EXCLUDED.sort_order;
|
||
|
||
-- ===================================================================
|
||
-- 插入主配置数据
|
||
-- ===================================================================
|
||
INSERT INTO public.ak_global_config (config_key, config_type, config_category, is_translatable, sort_order) VALUES
|
||
-- 公司基础信息（需要翻译）
|
||
('company_name', 'string', 'company', true, 1),
|
||
('company_slogan', 'string', 'company', true, 2),
|
||
('company_address', 'string', 'company', true, 3),
|
||
|
||
-- 联系方式（不需要翻译）
|
||
('company_phone', 'string', 'contact', false, 10),
|
||
('company_email', 'string', 'contact', false, 11),
|
||
('company_icp', 'string', 'legal', false, 12),
|
||
|
||
-- 媒体资源（不需要翻译）
|
||
('company_logo_url', 'string', 'media', false, 20),
|
||
|
||
-- 社交媒体链接（不需要翻译）
|
||
('social_wechat_url', 'string', 'social', false, 30),
|
||
('social_weibo_url', 'string', 'social', false, 31),
|
||
('social_qq_url', 'string', 'social', false, 32),
|
||
('social_linkedin_url', 'string', 'social', false, 33),
|
||
('social_twitter_url', 'string', 'social', false, 34),
|
||
|
||
-- 热门搜索关键词（需要翻译）
|
||
('hot_search_1', 'string', 'search', true, 50),
|
||
('hot_search_2', 'string', 'search', true, 51),
|
||
('hot_search_3', 'string', 'search', true, 52),
|
||
('hot_search_4', 'string', 'search', true, 53),
|
||
('hot_search_5', 'string', 'search', true, 54)
|
||
|
||
ON CONFLICT (config_key) DO UPDATE SET
|
||
config_type = EXCLUDED.config_type,
|
||
config_category = EXCLUDED.config_category,
|
||
is_translatable = EXCLUDED.is_translatable,
|
||
sort_order = EXCLUDED.sort_order,
|
||
updated_at = NOW();
|
||
|
||
-- ===================================================================
|
||
-- 插入翻译数据
|
||
-- ===================================================================
|
||
|
||
-- 公司名称翻译
|
||
INSERT INTO public.ak_global_config_translations (config_id, language_code, translated_value)
|
||
SELECT c.id, l.code,
|
||
CASE
|
||
WHEN l.code IN ('zh', 'zh-CN') THEN '创新科技有限公司'
|
||
WHEN l.code IN ('en', 'en-US') THEN 'Innovation Technology Co., Ltd.'
|
||
WHEN l.code IN ('tw', 'zh-TW') THEN '創新科技有限公司'
|
||
WHEN l.code IN ('ja', 'ja-JP') THEN 'イノベーション技術株式会社'
|
||
ELSE '创新科技有限公司' -- 默认中文
|
||
END
|
||
FROM public.ak_global_config c
|
||
CROSS JOIN public.ak_languages l
|
||
WHERE c.config_key = 'company_name' AND l.is_active = true
|
||
AND CASE
|
||
WHEN l.code IN ('zh', 'zh-CN') THEN '创新科技有限公司'
|
||
WHEN l.code IN ('en', 'en-US') THEN 'Innovation Technology Co., Ltd.'
|
||
WHEN l.code IN ('tw', 'zh-TW') THEN '創新科技有限公司'
|
||
WHEN l.code IN ('ja', 'ja-JP') THEN 'イノベーション技術株式会社'
|
||
ELSE '创新科技有限公司'
|
||
END IS NOT NULL
|
||
ON CONFLICT (config_id, language_code) DO UPDATE SET
|
||
translated_value = EXCLUDED.translated_value,
|
||
updated_at = NOW();
|
||
|
||
-- 公司标语翻译
|
||
INSERT INTO public.ak_global_config_translations (config_id, language_code, translated_value)
|
||
SELECT c.id, l.code,
|
||
CASE
|
||
WHEN l.code IN ('zh', 'zh-CN') THEN '科技创新，未来可期'
|
||
WHEN l.code IN ('en', 'en-US') THEN 'Innovation for the Future'
|
||
WHEN l.code IN ('tw', 'zh-TW') THEN '科技創新，未來可期'
|
||
WHEN l.code IN ('ja', 'ja-JP') THEN '技術革新で未来を創る'
|
||
ELSE '科技创新，未来可期' -- 默认中文
|
||
END
|
||
FROM public.ak_global_config c
|
||
CROSS JOIN public.ak_languages l
|
||
WHERE c.config_key = 'company_slogan' AND l.is_active = true
|
||
AND CASE
|
||
WHEN l.code IN ('zh', 'zh-CN') THEN '科技创新，未来可期'
|
||
WHEN l.code IN ('en', 'en-US') THEN 'Innovation for the Future'
|
||
WHEN l.code IN ('tw', 'zh-TW') THEN '科技創新，未來可期'
|
||
WHEN l.code IN ('ja', 'ja-JP') THEN '技術革新で未来を創る'
|
||
ELSE '科技创新，未来可期'
|
||
END IS NOT NULL
|
||
ON CONFLICT (config_id, language_code) DO UPDATE SET
|
||
translated_value = EXCLUDED.translated_value,
|
||
updated_at = NOW();
|
||
|
||
-- 公司地址翻译
|
||
INSERT INTO public.ak_global_config_translations (config_id, language_code, translated_value)
|
||
SELECT c.id, l.code,
|
||
CASE
|
||
WHEN l.code IN ('zh', 'zh-CN') THEN '上海市浦东新区张江高科技园区创新路88号'
|
||
WHEN l.code IN ('en', 'en-US') THEN '88 Innovation Road, Zhangjiang Hi-Tech Park, Pudong New Area, Shanghai'
|
||
WHEN l.code IN ('tw', 'zh-TW') THEN '上海市浦東新區張江高科技園區創新路88號'
|
||
WHEN l.code IN ('ja', 'ja-JP') THEN '上海市浦東新区張江ハイテクパーク イノベーション路88号'
|
||
ELSE '上海市浦东新区张江高科技园区创新路88号' -- 默认中文
|
||
END
|
||
FROM public.ak_global_config c
|
||
CROSS JOIN public.ak_languages l
|
||
WHERE c.config_key = 'company_address' AND l.is_active = true
|
||
AND CASE
|
||
WHEN l.code IN ('zh', 'zh-CN') THEN '上海市浦东新区张江高科技园区创新路88号'
|
||
WHEN l.code IN ('en', 'en-US') THEN '88 Innovation Road, Zhangjiang Hi-Tech Park, Pudong New Area, Shanghai'
|
||
WHEN l.code IN ('tw', 'zh-TW') THEN '上海市浦東新區張江高科技園區創新路88號'
|
||
WHEN l.code IN ('ja', 'ja-JP') THEN '上海市浦東新区張江ハイテクパーク イノベーション路88号'
|
||
ELSE '上海市浦东新区张江高科技园区创新路88号'
|
||
END IS NOT NULL
|
||
ON CONFLICT (config_id, language_code) DO UPDATE SET
|
||
translated_value = EXCLUDED.translated_value,
|
||
updated_at = NOW();
|
||
|
||
-- 热门搜索关键词翻译
|
||
-- 搜索关键词1
|
||
INSERT INTO public.ak_global_config_translations (config_id, language_code, translated_value)
|
||
SELECT c.id, l.code,
|
||
CASE
|
||
WHEN l.code IN ('zh', 'zh-CN') THEN '人工智能'
|
||
WHEN l.code IN ('en', 'en-US') THEN 'Artificial Intelligence'
|
||
WHEN l.code IN ('tw', 'zh-TW') THEN '人工智慧'
|
||
WHEN l.code IN ('ja', 'ja-JP') THEN '人工知能'
|
||
ELSE '人工智能' -- 默认中文
|
||
END
|
||
FROM public.ak_global_config c
|
||
CROSS JOIN public.ak_languages l
|
||
WHERE c.config_key = 'hot_search_1' AND l.is_active = true
|
||
AND CASE
|
||
WHEN l.code IN ('zh', 'zh-CN') THEN '人工智能'
|
||
WHEN l.code IN ('en', 'en-US') THEN 'Artificial Intelligence'
|
||
WHEN l.code IN ('tw', 'zh-TW') THEN '人工智慧'
|
||
WHEN l.code IN ('ja', 'ja-JP') THEN '人工知能'
|
||
ELSE '人工智能'
|
||
END IS NOT NULL
|
||
ON CONFLICT (config_id, language_code) DO UPDATE SET
|
||
translated_value = EXCLUDED.translated_value,
|
||
updated_at = NOW();
|
||
|
||
-- 搜索关键词2
|
||
INSERT INTO public.ak_global_config_translations (config_id, language_code, translated_value)
|
||
SELECT c.id, l.code,
|
||
CASE
|
||
WHEN l.code IN ('zh', 'zh-CN') THEN '大数据分析'
|
||
WHEN l.code IN ('en', 'en-US') THEN 'Big Data Analytics'
|
||
WHEN l.code IN ('tw', 'zh-TW') THEN '大數據分析'
|
||
WHEN l.code IN ('ja', 'ja-JP') THEN 'ビッグデータ解析'
|
||
ELSE '大数据分析' -- 默认中文
|
||
END
|
||
FROM public.ak_global_config c
|
||
CROSS JOIN public.ak_languages l
|
||
WHERE c.config_key = 'hot_search_2' AND l.is_active = true
|
||
AND CASE
|
||
WHEN l.code IN ('zh', 'zh-CN') THEN '大数据分析'
|
||
WHEN l.code IN ('en', 'en-US') THEN 'Big Data Analytics'
|
||
WHEN l.code IN ('tw', 'zh-TW') THEN '大數據分析'
|
||
WHEN l.code IN ('ja', 'ja-JP') THEN 'ビッグデータ解析'
|
||
ELSE '大数据分析'
|
||
END IS NOT NULL
|
||
ON CONFLICT (config_id, language_code) DO UPDATE SET
|
||
translated_value = EXCLUDED.translated_value,
|
||
updated_at = NOW();
|
||
|
||
-- 搜索关键词3
|
||
INSERT INTO public.ak_global_config_translations (config_id, language_code, translated_value)
|
||
SELECT c.id, l.code,
|
||
CASE
|
||
WHEN l.code IN ('zh', 'zh-CN') THEN '物联网'
|
||
WHEN l.code IN ('en', 'en-US') THEN 'Internet of Things'
|
||
WHEN l.code IN ('tw', 'zh-TW') THEN '物聯網'
|
||
WHEN l.code IN ('ja', 'ja-JP') THEN 'IoT'
|
||
ELSE '物联网' -- 默认中文
|
||
END
|
||
FROM public.ak_global_config c
|
||
CROSS JOIN public.ak_languages l
|
||
WHERE c.config_key = 'hot_search_3' AND l.is_active = true
|
||
AND CASE
|
||
WHEN l.code IN ('zh', 'zh-CN') THEN '物联网'
|
||
WHEN l.code IN ('en', 'en-US') THEN 'Internet of Things'
|
||
WHEN l.code IN ('tw', 'zh-TW') THEN '物聯網'
|
||
WHEN l.code IN ('ja', 'ja-JP') THEN 'IoT'
|
||
ELSE '物联网'
|
||
END IS NOT NULL
|
||
ON CONFLICT (config_id, language_code) DO UPDATE SET
|
||
translated_value = EXCLUDED.translated_value,
|
||
updated_at = NOW();
|
||
|
||
-- 搜索关键词4
|
||
INSERT INTO public.ak_global_config_translations (config_id, language_code, translated_value)
|
||
SELECT c.id, l.code,
|
||
CASE
|
||
WHEN l.code IN ('zh', 'zh-CN') THEN '云计算'
|
||
WHEN l.code IN ('en', 'en-US') THEN 'Cloud Computing'
|
||
WHEN l.code IN ('tw', 'zh-TW') THEN '雲端運算'
|
||
WHEN l.code IN ('ja', 'ja-JP') THEN 'クラウドコンピューティング'
|
||
ELSE '云计算' -- 默认中文
|
||
END
|
||
FROM public.ak_global_config c
|
||
CROSS JOIN public.ak_languages l
|
||
WHERE c.config_key = 'hot_search_4' AND l.is_active = true
|
||
AND CASE
|
||
WHEN l.code IN ('zh', 'zh-CN') THEN '云计算'
|
||
WHEN l.code IN ('en', 'en-US') THEN 'Cloud Computing'
|
||
WHEN l.code IN ('tw', 'zh-TW') THEN '雲端運算'
|
||
WHEN l.code IN ('ja', 'ja-JP') THEN 'クラウドコンピューティング'
|
||
ELSE '云计算'
|
||
END IS NOT NULL
|
||
ON CONFLICT (config_id, language_code) DO UPDATE SET
|
||
translated_value = EXCLUDED.translated_value,
|
||
updated_at = NOW();
|
||
|
||
-- 搜索关键词5
|
||
INSERT INTO public.ak_global_config_translations (config_id, language_code, translated_value)
|
||
SELECT c.id, l.code,
|
||
CASE
|
||
WHEN l.code IN ('zh', 'zh-CN') THEN '区块链'
|
||
WHEN l.code IN ('en', 'en-US') THEN 'Blockchain'
|
||
WHEN l.code IN ('tw', 'zh-TW') THEN '區塊鏈'
|
||
WHEN l.code IN ('ja', 'ja-JP') THEN 'ブロックチェーン'
|
||
ELSE '区块链' -- 默认中文
|
||
END
|
||
FROM public.ak_global_config c
|
||
CROSS JOIN public.ak_languages l
|
||
WHERE c.config_key = 'hot_search_5' AND l.is_active = true
|
||
AND CASE
|
||
WHEN l.code IN ('zh', 'zh-CN') THEN '区块链'
|
||
WHEN l.code IN ('en', 'en-US') THEN 'Blockchain'
|
||
WHEN l.code IN ('tw', 'zh-TW') THEN '區塊鏈'
|
||
WHEN l.code IN ('ja', 'ja-JP') THEN 'ブロックチェーン'
|
||
ELSE '区块链'
|
||
END IS NOT NULL
|
||
ON CONFLICT (config_id, language_code) DO UPDATE SET
|
||
translated_value = EXCLUDED.translated_value,
|
||
updated_at = NOW();
|
||
|
||
-- ===================================================================
|
||
-- 插入非翻译配置项的值（直接存储在主表的default_value字段）
|
||
-- ===================================================================
|
||
UPDATE public.ak_global_config SET default_value = '400-123-4567' WHERE config_key = 'company_phone';
|
||
UPDATE public.ak_global_config SET default_value = 'info@innovation-tech.com' WHERE config_key = 'company_email';
|
||
UPDATE public.ak_global_config SET default_value = '沪ICP备12345678号-1' WHERE config_key = 'company_icp';
|
||
UPDATE public.ak_global_config SET default_value = '/static/company-logo.png' WHERE config_key = 'company_logo_url';
|
||
UPDATE public.ak_global_config SET default_value = 'https://weixin.qq.com/innovation-tech' WHERE config_key = 'social_wechat_url';
|
||
UPDATE public.ak_global_config SET default_value = 'https://weibo.com/innovation-tech' WHERE config_key = 'social_weibo_url';
|
||
UPDATE public.ak_global_config SET default_value = 'https://qun.qq.com/innovation-tech' WHERE config_key = 'social_qq_url';
|
||
UPDATE public.ak_global_config SET default_value = 'https://linkedin.com/company/innovation-tech' WHERE config_key = 'social_linkedin_url';
|
||
UPDATE public.ak_global_config SET default_value = 'https://twitter.com/innovation_tech' WHERE config_key = 'social_twitter_url';
|
||
|
||
-- ===================================================================
|
||
-- 创建更新时间触发器
|
||
-- ===================================================================
|
||
CREATE OR REPLACE FUNCTION update_updated_at_column()
|
||
RETURNS TRIGGER AS $$
|
||
BEGIN
|
||
NEW.updated_at = NOW();
|
||
RETURN NEW;
|
||
END;
|
||
$$ language 'plpgsql';
|
||
|
||
-- 为主配置表创建更新时间触发器
|
||
DROP TRIGGER IF EXISTS update_ak_global_config_updated_at ON public.ak_global_config;
|
||
CREATE TRIGGER update_ak_global_config_updated_at
|
||
BEFORE UPDATE ON public.ak_global_config
|
||
FOR EACH ROW
|
||
EXECUTE FUNCTION update_updated_at_column();
|
||
|
||
-- 为翻译表创建更新时间触发器
|
||
DROP TRIGGER IF EXISTS update_ak_global_config_translations_updated_at ON public.ak_global_config_translations;
|
||
CREATE TRIGGER update_ak_global_config_translations_updated_at
|
||
BEFORE UPDATE ON public.ak_global_config_translations
|
||
FOR EACH ROW
|
||
EXECUTE FUNCTION update_updated_at_column();
|
||
|
||
-- ===================================================================
|
||
-- 创建便于查询的视图
|
||
-- ===================================================================
|
||
|
||
-- 1. 多语言配置视图
|
||
CREATE OR REPLACE VIEW public.vw_global_config_multilingual AS
|
||
SELECT
|
||
gc.config_key,
|
||
gc.config_type,
|
||
gc.config_category,
|
||
gc.is_translatable,
|
||
gc.default_value,
|
||
gct.language_code,
|
||
COALESCE(gct.translated_value, gc.default_value) AS config_value,
|
||
gc.sort_order,
|
||
gc.is_active
|
||
FROM public.ak_global_config gc
|
||
LEFT JOIN public.ak_global_config_translations gct ON gc.id = gct.config_id AND gct.is_active = true
|
||
WHERE gc.is_active = true
|
||
ORDER BY gc.config_category, gc.sort_order, gc.config_key, gct.language_code;
|
||
|
||
-- 2. 单语言配置视图函数
|
||
CREATE OR REPLACE FUNCTION get_config_by_language(lang_code VARCHAR(10) DEFAULT 'zh')
|
||
RETURNS TABLE (
|
||
config_key VARCHAR(100),
|
||
config_value TEXT,
|
||
config_type VARCHAR(50),
|
||
config_category VARCHAR(50)
|
||
) AS $$
|
||
BEGIN
|
||
RETURN QUERY
|
||
SELECT
|
||
gc.config_key,
|
||
COALESCE(gct.translated_value, gc.default_value) AS config_value,
|
||
gc.config_type,
|
||
gc.config_category
|
||
FROM public.ak_global_config gc
|
||
LEFT JOIN public.ak_global_config_translations gct ON gc.id = gct.config_id
|
||
AND gct.language_code = lang_code
|
||
AND gct.is_active = true
|
||
WHERE gc.is_active = true
|
||
ORDER BY gc.config_category, gc.sort_order;
|
||
END;
|
||
$$ LANGUAGE plpgsql;
|
||
|
||
-- 3. 热门搜索关键词视图函数
|
||
CREATE OR REPLACE FUNCTION get_hot_searches(lang_code VARCHAR(10) DEFAULT 'zh')
|
||
RETURNS TABLE (
|
||
search_term TEXT,
|
||
sort_order INTEGER
|
||
) AS $$
|
||
BEGIN
|
||
RETURN QUERY
|
||
SELECT
|
||
COALESCE(gct.translated_value, gc.default_value) AS search_term,
|
||
gc.sort_order
|
||
FROM public.ak_global_config gc
|
||
LEFT JOIN public.ak_global_config_translations gct ON gc.id = gct.config_id
|
||
AND gct.language_code = lang_code
|
||
AND gct.is_active = true
|
||
WHERE gc.is_active = true
|
||
AND gc.config_category = 'search'
|
||
ORDER BY gc.sort_order;
|
||
END;
|
||
$$ LANGUAGE plpgsql;
|
||
|
||
-- 4. 单个配置值获取函数（简化接口）
|
||
CREATE OR REPLACE FUNCTION get_config_value(
|
||
p_config_key VARCHAR(100),
|
||
p_language_code VARCHAR(10) DEFAULT 'zh'
|
||
) RETURNS TEXT AS $$
|
||
DECLARE
|
||
config_value TEXT;
|
||
default_value TEXT;
|
||
BEGIN
|
||
-- 先尝试获取指定语言的翻译
|
||
SELECT t.translated_value, c.default_value
|
||
INTO config_value, default_value
|
||
FROM public.ak_global_config c
|
||
LEFT JOIN public.ak_global_config_translations t
|
||
ON c.id = t.config_id
|
||
AND t.language_code = p_language_code
|
||
AND t.is_active = true
|
||
WHERE c.config_key = p_config_key
|
||
AND c.is_active = true;
|
||
|
||
-- 返回翻译值，如果没有则返回默认值
|
||
RETURN COALESCE(config_value, default_value);
|
||
END;
|
||
$$ LANGUAGE plpgsql;
|
||
|
||
-- 5. 批量配置获取函数（参数名标准化）
|
||
CREATE OR REPLACE FUNCTION get_configs_by_language(
|
||
p_language_code VARCHAR(10) DEFAULT 'zh'
|
||
) RETURNS TABLE (
|
||
config_key VARCHAR(100),
|
||
config_value TEXT,
|
||
config_type VARCHAR(50),
|
||
config_category VARCHAR(50),
|
||
sort_order INTEGER
|
||
) AS $$
|
||
BEGIN
|
||
RETURN QUERY
|
||
SELECT
|
||
c.config_key,
|
||
COALESCE(t.translated_value, c.default_value) AS config_value,
|
||
c.config_type,
|
||
c.config_category,
|
||
c.sort_order
|
||
FROM public.ak_global_config c
|
||
LEFT JOIN public.ak_global_config_translations t
|
||
ON c.id = t.config_id
|
||
AND t.language_code = p_language_code
|
||
AND t.is_active = true
|
||
WHERE c.is_active = true
|
||
ORDER BY c.config_category, c.sort_order, c.config_key;
|
||
END;
|
||
$$ LANGUAGE plpgsql;
|
||
|
||
COMMENT ON FUNCTION get_configs_by_language IS '批量获取指定语言的配置，支持分类和排序';
|
||
|
||
-- ===================================================================
|
||
-- 创建表注释
|
||
-- ===================================================================
|
||
COMMENT ON TABLE public.ak_global_config IS '全局配置主表';
|
||
COMMENT ON TABLE public.ak_global_config_translations IS '全局配置翻译表';
|
||
COMMENT ON TABLE public.ak_languages IS '支持的语言表';
|
||
|
||
COMMENT ON COLUMN public.ak_global_config.config_key IS '配置键名（唯一）';
|
||
COMMENT ON COLUMN public.ak_global_config.config_type IS '配置值类型：string, number, boolean, json';
|
||
COMMENT ON COLUMN public.ak_global_config.config_category IS '配置分类：company, contact, social, search等';
|
||
COMMENT ON COLUMN public.ak_global_config.default_value IS '默认值（用于不需要翻译的配置项）';
|
||
COMMENT ON COLUMN public.ak_global_config.is_translatable IS '是否需要多语言翻译';
|
||
|
||
COMMENT ON COLUMN public.ak_global_config_translations.config_id IS '关联的配置项ID';
|
||
COMMENT ON COLUMN public.ak_global_config_translations.language_code IS '语言代码';
|
||
COMMENT ON COLUMN public.ak_global_config_translations.translated_value IS '翻译后的值';
|
||
|
||
COMMENT ON VIEW public.vw_global_config_multilingual IS '多语言配置视图';
|
||
COMMENT ON FUNCTION get_config_by_language IS '获取指定语言的配置函数';
|
||
COMMENT ON FUNCTION get_hot_searches IS '获取指定语言的热门搜索关键词函数';
|
||
COMMENT ON FUNCTION get_config_value IS '获取单个配置项的值，支持多语言回退';
|
||
|
||
-- ===================================================================
|
||
-- 完成信息
|
||
-- ===================================================================
|
||
DO $$
|
||
BEGIN
|
||
RAISE NOTICE '========================================';
|
||
RAISE NOTICE '改进的全局配置表结构初始化完成！';
|
||
RAISE NOTICE '========================================';
|
||
RAISE NOTICE '主要改进：';
|
||
RAISE NOTICE '1. 主表+翻译表分离设计，结构更清晰';
|
||
RAISE NOTICE '2. 通过 is_translatable 字段区分是否需要翻译';
|
||
RAISE NOTICE '3. 配置分类管理，便于按类别查询';
|
||
RAISE NOTICE '4. 提供多语言视图和函数，查询更便捷';
|
||
RAISE NOTICE '5. 支持语言管理，可动态添加新语言';
|
||
RAISE NOTICE '========================================';
|
||
RAISE NOTICE '使用示例：';
|
||
RAISE NOTICE '-- 获取中文配置：SELECT * FROM get_config_by_language(''zh'');';
|
||
RAISE NOTICE '-- 获取英文热门搜索：SELECT * FROM get_hot_searches(''en'');';
|
||
RAISE NOTICE '-- 查看所有多语言配置：SELECT * FROM vw_global_config_multilingual;';
|
||
RAISE NOTICE '========================================';
|
||
END
|
||
$$;
|