82 lines
3.2 KiB
SQL
82 lines
3.2 KiB
SQL
CREATE TABLE posts
|
|
(
|
|
id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
|
|
title TEXT NOT NULL,
|
|
cover_id INTEGER,
|
|
slug TEXT UNIQUE NOT NULL,
|
|
content TEXT NOT NULL,
|
|
summary TEXT NOT NULL,
|
|
status SMALLINT NOT NULL DEFAULT 1,
|
|
view INTEGER DEFAULT 0,
|
|
sort INTEGER DEFAULT 0,
|
|
published_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ,
|
|
|
|
CHECK (status IN (0, 1, 2))
|
|
);
|
|
|
|
CREATE TRIGGER update_posts_updated_at
|
|
BEFORE UPDATE
|
|
ON posts
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
|
|
COMMENT ON COLUMN posts.id IS '主键ID';
|
|
COMMENT ON COLUMN posts.title IS '文章标题';
|
|
COMMENT ON COLUMN posts.cover_id IS '文章封面图片id';
|
|
COMMENT ON COLUMN posts.slug IS 'URL友好地址';
|
|
COMMENT ON COLUMN posts.content IS '文章正文';
|
|
COMMENT ON COLUMN posts.summary IS '摘要';
|
|
COMMENT ON COLUMN posts.status IS '状态 0:草稿 1:已发布 2:已下线';
|
|
COMMENT ON COLUMN posts.view IS '阅读量';
|
|
COMMENT ON COLUMN posts.sort IS '文章排序';
|
|
COMMENT ON COLUMN posts.published_at IS '发布时间';
|
|
COMMENT ON COLUMN posts.created_at IS '创建时间';
|
|
COMMENT ON COLUMN posts.updated_at IS '修改时间';
|
|
|
|
-- 批量插入菜单及关联权限
|
|
WITH input(name, path, component, type, sort, status, code)
|
|
AS (VALUES ('文章管理', '/post', '/blog/post/index', 1, 6, 1, 'post'),
|
|
('文章编辑', '/post/edit', '/blog/post-edit/index', 1, 7, 1, 'post:edit')
|
|
),
|
|
-- 插入菜单
|
|
inserted_menus AS (
|
|
INSERT INTO sys_menus (name, path, component, type, sort, status)
|
|
SELECT name, path, component, type, sort, status FROM input RETURNING id),
|
|
menu_rows AS (SELECT id, ROW_NUMBER() OVER () AS rn FROM inserted_menus),
|
|
-- 插入权限
|
|
inserted_permissions AS (
|
|
INSERT INTO sys_permissions (type, code)
|
|
SELECT 0, code FROM input RETURNING id),
|
|
permission_rows AS (SELECT id, ROW_NUMBER() OVER () AS rn FROM inserted_permissions)
|
|
-- 关联权限
|
|
INSERT
|
|
INTO sys_menu_permission(menu_id, permission_id)
|
|
SELECT m.id, p.id
|
|
FROM menu_rows m
|
|
JOIN permission_rows p ON m.rn = p.rn;
|
|
|
|
-- 批量插入接口及关联权限
|
|
WITH inserted_apis AS (
|
|
-- 插入菜单
|
|
INSERT INTO sys_apis (group_name, name, method, path)
|
|
VALUES ('文章管理', '文章列表', 'GET', '/admin/post'),
|
|
('文章管理', '创建文章', 'POST', '/admin/post'),
|
|
('文章管理', '修改文章', 'PATCH', '/admin/post/{id}'),
|
|
('文章管理', '删除文章', 'DELETE', '/admin/post/{id}')
|
|
RETURNING id),
|
|
api_rows AS (SELECT id, ROW_NUMBER() OVER () AS rn
|
|
FROM inserted_apis),
|
|
-- 创建权限
|
|
inserted_permissions AS (
|
|
INSERT INTO sys_permissions (type)
|
|
SELECT 1 FROM api_rows RETURNING id),
|
|
permission_rows AS (SELECT id, ROW_NUMBER() OVER () AS rn
|
|
FROM inserted_permissions)
|
|
INSERT
|
|
INTO sys_api_permission(api_id, permission_id)
|
|
SELECT a.id, b.id
|
|
FROM api_rows a
|
|
INNER JOIN permission_rows b ON a.rn = b.rn; |