261 lines
9.5 KiB
PL/PgSQL
261 lines
9.5 KiB
PL/PgSQL
-- ===================================================================
|
||
-- 业务项目内容初始化脚本
|
||
-- 基于 ak_contents 表，使用 content_type='business_item' 来区分业务项目
|
||
-- ===================================================================
|
||
|
||
-- 首先确保 ak_contents 表中有 content_type 字段
|
||
DO $$
|
||
BEGIN
|
||
IF NOT EXISTS (
|
||
SELECT 1 FROM information_schema.columns
|
||
WHERE table_name = 'ak_contents'
|
||
AND column_name = 'content_type'
|
||
) THEN
|
||
ALTER TABLE public.ak_contents
|
||
ADD COLUMN content_type VARCHAR(50) DEFAULT 'article';
|
||
|
||
-- 创建索引以提高查询性能
|
||
CREATE INDEX idx_ak_contents_content_type ON public.ak_contents(content_type);
|
||
|
||
RAISE NOTICE '已为 ak_contents 表添加 content_type 字段';
|
||
ELSE
|
||
RAISE NOTICE 'content_type 字段已存在';
|
||
END IF;
|
||
END
|
||
$$;
|
||
|
||
-- 确保有业务相关的分类
|
||
INSERT INTO public.ak_content_categories (name_key, parent_id, level, ai_keywords, sort_order, is_active) VALUES
|
||
('business.ai', NULL, 0, ARRAY['人工智能', 'AI', '机器学习', 'artificial intelligence'], 1, true),
|
||
('business.bigdata', NULL, 0, ARRAY['大数据', 'big data', '数据分析', 'analytics'], 2, true),
|
||
('business.iot', NULL, 0, ARRAY['物联网', 'IoT', '智能设备', 'smart devices'], 3, true),
|
||
('business.cloud', NULL, 0, ARRAY['云计算', 'cloud computing', '云服务', 'cloud services'], 4, true),
|
||
('business.blockchain', NULL, 0, ARRAY['区块链', 'blockchain', '数字货币', 'cryptocurrency'], 5, true),
|
||
('business.mobile', NULL, 0, ARRAY['移动开发', 'mobile development', 'APP开发', 'mobile app'], 6, true),
|
||
('business.web', NULL, 0, ARRAY['前端开发', 'web development', '网站建设', 'website'], 7, true),
|
||
('business.database', NULL, 0, ARRAY['数据库', 'database', 'SQL', '数据管理'], 8, true)
|
||
ON CONFLICT (name_key) DO NOTHING;
|
||
|
||
-- 获取分类ID以便插入内容
|
||
DO $$
|
||
DECLARE
|
||
ai_category_id UUID;
|
||
bigdata_category_id UUID;
|
||
iot_category_id UUID;
|
||
cloud_category_id UUID;
|
||
blockchain_category_id UUID;
|
||
mobile_category_id UUID;
|
||
web_category_id UUID;
|
||
database_category_id UUID;
|
||
BEGIN
|
||
-- 获取分类ID
|
||
SELECT id INTO ai_category_id FROM public.ak_content_categories WHERE name_key = 'business.ai';
|
||
SELECT id INTO bigdata_category_id FROM public.ak_content_categories WHERE name_key = 'business.bigdata';
|
||
SELECT id INTO iot_category_id FROM public.ak_content_categories WHERE name_key = 'business.iot';
|
||
SELECT id INTO cloud_category_id FROM public.ak_content_categories WHERE name_key = 'business.cloud';
|
||
SELECT id INTO blockchain_category_id FROM public.ak_content_categories WHERE name_key = 'business.blockchain';
|
||
SELECT id INTO mobile_category_id FROM public.ak_content_categories WHERE name_key = 'business.mobile';
|
||
SELECT id INTO web_category_id FROM public.ak_content_categories WHERE name_key = 'business.web';
|
||
SELECT id INTO database_category_id FROM public.ak_content_categories WHERE name_key = 'business.database';
|
||
|
||
-- 插入业务项目内容
|
||
INSERT INTO public.ak_contents (
|
||
title,
|
||
content_summary,
|
||
content_type,
|
||
category_id,
|
||
original_language,
|
||
status,
|
||
featured_image_url,
|
||
view_count,
|
||
published_at,
|
||
tags,
|
||
quality_score
|
||
) VALUES
|
||
(
|
||
'人工智能解决方案',
|
||
'为企业提供全方位的AI技术支持和解决方案，助力数字化转型升级。我们的AI服务包括机器学习模型开发、自然语言处理、计算机视觉等核心技术。',
|
||
'business_item',
|
||
ai_category_id,
|
||
'zh-CN',
|
||
'published',
|
||
'https://picsum.photos/400/300?random=1',
|
||
1285,
|
||
NOW() - INTERVAL '5 days',
|
||
ARRAY['人工智能', 'AI', '机器学习', '企业服务'],
|
||
0.95
|
||
),
|
||
(
|
||
'大数据分析平台',
|
||
'专业的数据分析工具，帮助企业洞察商业价值，制定精准决策。支持实时数据处理、可视化报表、智能预测分析等功能。',
|
||
'business_item',
|
||
bigdata_category_id,
|
||
'zh-CN',
|
||
'published',
|
||
'https://picsum.photos/400/300?random=2',
|
||
892,
|
||
NOW() - INTERVAL '8 days',
|
||
ARRAY['大数据', '数据分析', '商业智能', 'BI'],
|
||
0.92
|
||
),
|
||
(
|
||
'物联网系统集成',
|
||
'完整的IoT解决方案，实现设备互联和智能管理。提供硬件接入、数据采集、远程控制、智能分析等一站式服务。',
|
||
'business_item',
|
||
iot_category_id,
|
||
'zh-CN',
|
||
'published',
|
||
'https://picsum.photos/400/300?random=3',
|
||
756,
|
||
NOW() - INTERVAL '10 days',
|
||
ARRAY['物联网', 'IoT', '智能设备', '系统集成'],
|
||
0.89
|
||
),
|
||
(
|
||
'云计算服务',
|
||
'稳定可靠的云端基础设施，为业务提供强大的技术支撑。包括云服务器、云存储、CDN加速、负载均衡等核心服务。',
|
||
'business_item',
|
||
cloud_category_id,
|
||
'zh-CN',
|
||
'published',
|
||
'https://picsum.photos/400/300?random=4',
|
||
643,
|
||
NOW() - INTERVAL '12 days',
|
||
ARRAY['云计算', '云服务', '基础设施', 'IaaS'],
|
||
0.91
|
||
),
|
||
(
|
||
'区块链技术',
|
||
'安全可信的区块链解决方案，保障数据安全与透明。提供智能合约开发、数字资产管理、去中心化应用等服务。',
|
||
'business_item',
|
||
blockchain_category_id,
|
||
'zh-CN',
|
||
'published',
|
||
'https://picsum.photos/400/300?random=5',
|
||
521,
|
||
NOW() - INTERVAL '15 days',
|
||
ARRAY['区块链', '智能合约', '数字资产', 'DApp'],
|
||
0.87
|
||
),
|
||
(
|
||
'移动应用开发',
|
||
'专业的移动端应用开发服务，覆盖iOS和Android平台。提供原生开发、混合开发、跨平台开发等多种解决方案。',
|
||
'business_item',
|
||
mobile_category_id,
|
||
'zh-CN',
|
||
'published',
|
||
'https://picsum.photos/400/300?random=6',
|
||
467,
|
||
NOW() - INTERVAL '18 days',
|
||
ARRAY['移动开发', 'APP', 'iOS', 'Android'],
|
||
0.93
|
||
),
|
||
(
|
||
'Web前端设计',
|
||
'现代化的前端界面设计，提供优秀的用户体验。采用最新的前端技术栈，确保响应式设计和跨浏览器兼容性。',
|
||
'business_item',
|
||
web_category_id,
|
||
'zh-CN',
|
||
'published',
|
||
'https://picsum.photos/400/300?random=7',
|
||
389,
|
||
NOW() - INTERVAL '20 days',
|
||
ARRAY['前端开发', 'UI设计', '响应式', '用户体验'],
|
||
0.90
|
||
),
|
||
(
|
||
'数据库优化',
|
||
'高性能数据库设计与优化，提升系统运行效率。包括数据库架构设计、性能调优、数据迁移、备份恢复等专业服务。',
|
||
'business_item',
|
||
database_category_id,
|
||
'zh-CN',
|
||
'published',
|
||
'https://picsum.photos/400/300?random=8',
|
||
324,
|
||
NOW() - INTERVAL '22 days',
|
||
ARRAY['数据库', '性能优化', 'SQL', '数据架构'],
|
||
0.88
|
||
);
|
||
|
||
RAISE NOTICE '业务项目内容初始化完成！';
|
||
RAISE NOTICE '已插入 8 个业务项目到 ak_contents 表中';
|
||
RAISE NOTICE '内容类型设置为: business_item';
|
||
END
|
||
$$;
|
||
|
||
-- 创建业务项目专用视图，方便查询和管理
|
||
CREATE OR REPLACE VIEW public.vw_business_items AS
|
||
SELECT
|
||
c.id,
|
||
c.title,
|
||
c.content_summary as description,
|
||
c.featured_image_url as image_url,
|
||
cc.name_key as category_key,
|
||
c.published_at,
|
||
c.view_count,
|
||
c.status,
|
||
c.tags,
|
||
c.quality_score,
|
||
c.created_at,
|
||
c.updated_at
|
||
FROM public.ak_contents c
|
||
LEFT JOIN public.ak_content_categories cc ON c.category_id = cc.id
|
||
WHERE c.content_type = 'business_item'
|
||
ORDER BY c.published_at DESC;
|
||
|
||
COMMENT ON VIEW public.vw_business_items IS '业务项目视图，基于ak_contents表的business_item类型内容';
|
||
|
||
-- 创建获取业务项目的函数
|
||
CREATE OR REPLACE FUNCTION public.get_business_items(
|
||
p_limit INTEGER DEFAULT 20,
|
||
p_offset INTEGER DEFAULT 0,
|
||
p_language VARCHAR(10) DEFAULT 'zh-CN'
|
||
)
|
||
RETURNS TABLE (
|
||
id UUID,
|
||
title TEXT,
|
||
description TEXT,
|
||
image_url TEXT,
|
||
category_name TEXT,
|
||
publish_time TEXT,
|
||
view_count INTEGER,
|
||
tags TEXT[]
|
||
) AS $$
|
||
BEGIN
|
||
RETURN QUERY
|
||
SELECT
|
||
c.id,
|
||
c.title,
|
||
c.content_summary as description,
|
||
c.featured_image_url as image_url,
|
||
COALESCE(cc.name_key, '未分类') as category_name,
|
||
TO_CHAR(c.published_at, 'YYYY-MM-DD') as publish_time,
|
||
c.view_count,
|
||
c.tags
|
||
FROM public.ak_contents c
|
||
LEFT JOIN public.ak_content_categories cc ON c.category_id = cc.id
|
||
WHERE c.content_type = 'business_item'
|
||
AND c.status = 'published'
|
||
AND c.original_language = p_language
|
||
ORDER BY c.published_at DESC
|
||
LIMIT p_limit
|
||
OFFSET p_offset;
|
||
END;
|
||
$$ LANGUAGE plpgsql;
|
||
|
||
COMMENT ON FUNCTION public.get_business_items IS '获取业务项目列表的函数';
|
||
|
||
-- 输出完成信息
|
||
DO $$
|
||
BEGIN
|
||
RAISE NOTICE '=================================';
|
||
RAISE NOTICE '业务项目数据初始化完成！';
|
||
RAISE NOTICE '- 已创建业务相关分类';
|
||
RAISE NOTICE '- 已插入示例业务项目数据';
|
||
RAISE NOTICE '- 已创建业务项目视图';
|
||
RAISE NOTICE '- 已创建查询函数';
|
||
RAISE NOTICE '现在可以在 comindex.uvue 页面中使用 ak_contents 表获取业务项目了';
|
||
RAISE NOTICE '=================================';
|
||
END
|
||
$$;
|