150 lines
7.0 KiB
SQL
150 lines
7.0 KiB
SQL
-- Blog 系统数据库迁移
|
||
-- 创建博客分类表
|
||
CREATE TABLE IF NOT EXISTS blog_categories (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
name VARCHAR(255) NOT NULL UNIQUE,
|
||
slug VARCHAR(255) NOT NULL UNIQUE,
|
||
description TEXT,
|
||
color VARCHAR(7), -- 十六进制颜色代码，如 #FF5733
|
||
icon VARCHAR(100), -- 图标名称或路径
|
||
is_active BOOLEAN DEFAULT true,
|
||
sort_order INTEGER DEFAULT 0,
|
||
created_at TIMESTAMPTZ DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ DEFAULT NOW(),
|
||
created_by UUID REFERENCES users(id),
|
||
updated_by UUID REFERENCES users(id)
|
||
);
|
||
|
||
-- 创建博客标签表
|
||
CREATE TABLE IF NOT EXISTS blog_tags (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
name VARCHAR(255) NOT NULL UNIQUE,
|
||
slug VARCHAR(255) NOT NULL UNIQUE,
|
||
description TEXT,
|
||
color VARCHAR(7), -- 十六进制颜色代码
|
||
is_active BOOLEAN DEFAULT true,
|
||
created_at TIMESTAMPTZ DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ DEFAULT NOW(),
|
||
created_by UUID REFERENCES users(id),
|
||
updated_by UUID REFERENCES users(id)
|
||
);
|
||
|
||
-- 创建博客文章表
|
||
CREATE TABLE IF NOT EXISTS blogs (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
title VARCHAR(500) NOT NULL,
|
||
slug VARCHAR(500) NOT NULL UNIQUE,
|
||
excerpt TEXT, -- 摘要
|
||
content JSONB NOT NULL, -- JSON格式的博客内容
|
||
category_id UUID REFERENCES blog_categories(id),
|
||
status VARCHAR(20) DEFAULT 'draft', -- draft, published, archived
|
||
featured_image VARCHAR(500), -- 特色图片URL
|
||
meta_title VARCHAR(255), -- SEO标题
|
||
meta_description TEXT, -- SEO描述
|
||
published_at TIMESTAMPTZ, -- 发布时间
|
||
view_count INTEGER DEFAULT 0, -- 浏览次数
|
||
is_featured BOOLEAN DEFAULT false, -- 是否推荐
|
||
is_active BOOLEAN DEFAULT true,
|
||
created_at TIMESTAMPTZ DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ DEFAULT NOW(),
|
||
created_by UUID REFERENCES users(id),
|
||
updated_by UUID REFERENCES users(id)
|
||
);
|
||
|
||
-- 创建博客标签关联表（多对多关系）
|
||
CREATE TABLE IF NOT EXISTS blog_tag_relations (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
blog_id UUID NOT NULL REFERENCES blogs(id) ON DELETE CASCADE,
|
||
tag_id UUID NOT NULL REFERENCES blog_tags(id) ON DELETE CASCADE,
|
||
created_at TIMESTAMPTZ DEFAULT NOW(),
|
||
UNIQUE(blog_id, tag_id)
|
||
);
|
||
|
||
-- 创建索引以提高查询性能
|
||
CREATE INDEX IF NOT EXISTS idx_blog_categories_slug ON blog_categories(slug);
|
||
CREATE INDEX IF NOT EXISTS idx_blog_categories_active ON blog_categories(is_active);
|
||
CREATE INDEX IF NOT EXISTS idx_blog_categories_sort ON blog_categories(sort_order);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_blog_tags_slug ON blog_tags(slug);
|
||
CREATE INDEX IF NOT EXISTS idx_blog_tags_active ON blog_tags(is_active);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_blogs_slug ON blogs(slug);
|
||
CREATE INDEX IF NOT EXISTS idx_blogs_status ON blogs(status);
|
||
CREATE INDEX IF NOT EXISTS idx_blogs_category ON blogs(category_id);
|
||
CREATE INDEX IF NOT EXISTS idx_blogs_published_at ON blogs(published_at);
|
||
CREATE INDEX IF NOT EXISTS idx_blogs_featured ON blogs(is_featured);
|
||
CREATE INDEX IF NOT EXISTS idx_blogs_active ON blogs(is_active);
|
||
CREATE INDEX IF NOT EXISTS idx_blogs_created_at ON blogs(created_at);
|
||
CREATE INDEX IF NOT EXISTS idx_blogs_view_count ON blogs(view_count);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_blog_tag_relations_blog ON blog_tag_relations(blog_id);
|
||
CREATE INDEX IF NOT EXISTS idx_blog_tag_relations_tag ON blog_tag_relations(tag_id);
|
||
|
||
-- 创建全文搜索索引
|
||
CREATE INDEX IF NOT EXISTS idx_blogs_content_gin ON blogs USING GIN (content);
|
||
-- CREATE INDEX IF NOT EXISTS idx_blogs_title_gin ON blogs USING GIN (to_tsvector('chinese', title));
|
||
-- CREATE INDEX IF NOT EXISTS idx_blogs_excerpt_gin ON blogs USING GIN (to_tsvector('chinese', excerpt));
|
||
|
||
-- 创建触发器更新 updated_at
|
||
CREATE TRIGGER update_blog_categories_updated_at BEFORE UPDATE
|
||
ON blog_categories FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
||
|
||
CREATE TRIGGER update_blog_tags_updated_at BEFORE UPDATE
|
||
ON blog_tags FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
||
|
||
CREATE TRIGGER update_blogs_updated_at BEFORE UPDATE
|
||
ON blogs FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
||
|
||
-- 插入默认分类
|
||
INSERT INTO blog_categories (name, slug, description, color, icon, sort_order, created_at, updated_at) VALUES
|
||
('Announcement', 'announcement', 'Announcement', '#3B82F6', 'code', 1, NOW(), NOW()),
|
||
('Product Update', 'product-update', 'Product Update', '#10B981', 'bell', 2, NOW(), NOW()),
|
||
('Usage Guide', 'usage-guide', 'Usage Guide', '#F59E0B', 'book-open', 3, NOW(), NOW()),
|
||
('Industry Trend', 'industry-trend', 'Industry Trend', '#8B5CF6', 'trending-up', 4, NOW(), NOW()),
|
||
('Team Story', 'team-story', 'Team Story', '#EF4444', 'users', 5, NOW(), NOW());
|
||
|
||
-- 插入默认标签
|
||
INSERT INTO blog_tags (name, slug, description, color, created_at, updated_at) VALUES
|
||
('Rust', 'rust', 'Rust编程语言相关', '#CE422B', NOW(), NOW());
|
||
|
||
-- 为Casbin添加Blog相关权限
|
||
INSERT INTO casbin_rule (ptype, v0, v1, v2, v3, v4, v5) VALUES
|
||
-- 管理员Blog权限
|
||
('p', 'admin', 'blogs', 'read', '', '', ''),
|
||
('p', 'admin', 'blogs', 'write', '', '', ''),
|
||
('p', 'admin', 'blogs', 'delete', '', '', ''),
|
||
('p', 'admin', 'blog_categories', 'read', '', '', ''),
|
||
('p', 'admin', 'blog_categories', 'write', '', '', ''),
|
||
('p', 'admin', 'blog_categories', 'delete', '', '', ''),
|
||
('p', 'admin', 'blog_tags', 'read', '', '', ''),
|
||
('p', 'admin', 'blog_tags', 'write', '', '', ''),
|
||
('p', 'admin', 'blog_tags', 'delete', '', '', ''),
|
||
|
||
-- 用户Blog权限（只读）
|
||
('p', 'user', 'blogs', 'read', '', '', ''),
|
||
('p', 'user', 'blog_categories', 'read', '', '', ''),
|
||
('p', 'user', 'blog_tags', 'read', '', '', '');
|
||
|
||
-- 添加Blog相关权限到permissions表
|
||
INSERT INTO permissions (name, description, resource, action, is_active, created_at, updated_at) VALUES
|
||
-- Blog管理权限
|
||
('blogs_read', '读取博客文章', 'blogs', 'read', true, NOW(), NOW()),
|
||
('blogs_write', '创建/修改博客文章', 'blogs', 'write', true, NOW(), NOW()),
|
||
('blogs_delete', '删除博客文章', 'blogs', 'delete', true, NOW(), NOW()),
|
||
|
||
-- 博客分类管理权限
|
||
('blog_categories_read', '读取博客分类', 'blog_categories', 'read', true, NOW(), NOW()),
|
||
('blog_categories_write', '创建/修改博客分类', 'blog_categories', 'write', true, NOW(), NOW()),
|
||
('blog_categories_delete', '删除博客分类', 'blog_categories', 'delete', true, NOW(), NOW()),
|
||
|
||
-- 博客标签管理权限
|
||
('blog_tags_read', '读取博客标签', 'blog_tags', 'read', true, NOW(), NOW()),
|
||
('blog_tags_write', '创建/修改博客标签', 'blog_tags', 'write', true, NOW(), NOW()),
|
||
('blog_tags_delete', '删除博客标签', 'blog_tags', 'delete', true, NOW(), NOW());
|
||
|
||
-- 为角色分配Blog权限
|
||
INSERT INTO role_permissions (role_name, permission_id, granted_by, granted_at)
|
||
SELECT 'admin', id, NULL, NOW() FROM permissions WHERE resource IN ('blogs', 'blog_categories', 'blog_tags');
|
||
|
||
INSERT INTO role_permissions (role_name, permission_id, granted_by, granted_at)
|
||
SELECT 'user', id, NULL, NOW() FROM permissions WHERE resource IN ('blogs', 'blog_categories', 'blog_tags') AND action = 'read'; |