143 lines
7.7 KiB
SQL
143 lines
7.7 KiB
SQL
-- +----------------------------------------------------------------------
|
||
-- | YwxApp 轻社区论坛插件（forum）数据表
|
||
-- | 基于 Fly-3.0 社区模板结构分析设计
|
||
-- | 表前缀：__PREFIX__forum_*
|
||
-- +----------------------------------------------------------------------
|
||
|
||
SET NAMES utf8mb4;
|
||
|
||
-- ----------------------------
|
||
-- 版块 / 专栏（对应 Fly 的专栏分类）
|
||
-- ----------------------------
|
||
CREATE TABLE IF NOT EXISTS `__PREFIX__forum_board` (
|
||
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
`parent_id` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '父版块ID，0为顶级',
|
||
`name` VARCHAR(60) NOT NULL DEFAULT '' COMMENT '版块名称',
|
||
`slug` VARCHAR(60) NOT NULL DEFAULT '' COMMENT '英文标识，用于URL',
|
||
`icon` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '图标',
|
||
`description` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '版块简介',
|
||
`sort` INT NOT NULL DEFAULT 0 COMMENT '排序',
|
||
`topic_count` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '帖子数',
|
||
`reply_count` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '回复数',
|
||
`status` TINYINT NOT NULL DEFAULT 1 COMMENT '1启用0禁用',
|
||
`create_at` INT NOT NULL DEFAULT 0,
|
||
`update_at` INT NOT NULL DEFAULT 0,
|
||
PRIMARY KEY (`id`),
|
||
KEY `parent_id` (`parent_id`),
|
||
KEY `slug` (`slug`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='论坛版块';
|
||
|
||
-- ----------------------------
|
||
-- 帖子（对应 Fly 的 jie）
|
||
-- ----------------------------
|
||
CREATE TABLE IF NOT EXISTS `__PREFIX__forum_topic` (
|
||
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
`board_id` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '版块ID',
|
||
`user_id` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '作者会员ID',
|
||
`title` VARCHAR(200) NOT NULL DEFAULT '' COMMENT '标题',
|
||
`content` MEDIUMTEXT NOT NULL COMMENT '正文（HTML）',
|
||
`type` SMALLINT NOT NULL DEFAULT 0 COMMENT '类型：0提问 99分享 100讨论 101建议 168公告 169动态',
|
||
`status` TINYINT NOT NULL DEFAULT 1 COMMENT '1正常 0待审 -1删除',
|
||
`is_top` TINYINT NOT NULL DEFAULT 0 COMMENT '置顶',
|
||
`is_essence` TINYINT NOT NULL DEFAULT 0 COMMENT '加精',
|
||
`audit_reason` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '审核结论/驳回理由',
|
||
`lat` DECIMAL(10,7) NOT NULL DEFAULT '0.0000000' COMMENT '纬度(可选,用于附近)',
|
||
`lng` DECIMAL(10,7) NOT NULL DEFAULT '0.0000000' COMMENT '经度(可选,用于附近)',
|
||
`views` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '浏览数',
|
||
`reply_count` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '回复数',
|
||
`last_reply_id` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '最后回复ID',
|
||
`last_reply_uid` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '最后回复用户ID',
|
||
`last_reply_time` INT NOT NULL DEFAULT 0 COMMENT '最后回复时间',
|
||
`create_at` INT NOT NULL DEFAULT 0,
|
||
`update_at` INT NOT NULL DEFAULT 0,
|
||
PRIMARY KEY (`id`),
|
||
KEY `board_id` (`board_id`),
|
||
KEY `user_id` (`user_id`),
|
||
KEY `type` (`type`),
|
||
KEY `status` (`status`),
|
||
KEY `is_top` (`is_top`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='论坛帖子';
|
||
|
||
-- ----------------------------
|
||
-- 回复（对应 Fly 的帖子详情里的回复列表）
|
||
-- ----------------------------
|
||
CREATE TABLE IF NOT EXISTS `__PREFIX__forum_reply` (
|
||
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
`topic_id` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '所属帖子',
|
||
`user_id` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '回复用户ID',
|
||
`content` MEDIUMTEXT NOT NULL COMMENT '回复内容（HTML）',
|
||
`floor` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '楼层',
|
||
`status` TINYINT NOT NULL DEFAULT 1 COMMENT '1正常 -1删除',
|
||
`create_at` INT NOT NULL DEFAULT 0,
|
||
`update_at` INT NOT NULL DEFAULT 0,
|
||
PRIMARY KEY (`id`),
|
||
KEY `topic_id` (`topic_id`),
|
||
KEY `user_id` (`user_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='论坛回复';
|
||
|
||
-- ----------------------------
|
||
-- 标签
|
||
-- ----------------------------
|
||
CREATE TABLE IF NOT EXISTS `__PREFIX__forum_tag` (
|
||
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
`name` VARCHAR(40) NOT NULL DEFAULT '',
|
||
`topic_count` INT UNSIGNED NOT NULL DEFAULT 0,
|
||
`create_at` INT NOT NULL DEFAULT 0,
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `name` (`name`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='论坛标签';
|
||
|
||
-- ----------------------------
|
||
-- 帖子-标签关联
|
||
-- ----------------------------
|
||
CREATE TABLE IF NOT EXISTS `__PREFIX__forum_topic_tag` (
|
||
`topic_id` INT UNSIGNED NOT NULL,
|
||
`tag_id` INT UNSIGNED NOT NULL,
|
||
PRIMARY KEY (`topic_id`, `tag_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='帖子标签关联';
|
||
|
||
-- ----------------------------
|
||
-- 消息（@提醒 / 回复通知 / 系统）
|
||
-- ----------------------------
|
||
CREATE TABLE IF NOT EXISTS `__PREFIX__forum_message` (
|
||
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
`user_id` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '接收人',
|
||
`from_uid` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '发送人，0为系统',
|
||
`type` VARCHAR(20) NOT NULL DEFAULT 'reply' COMMENT 'reply/at/system',
|
||
`topic_id` INT UNSIGNED NOT NULL DEFAULT 0,
|
||
`reply_id` INT UNSIGNED NOT NULL DEFAULT 0,
|
||
`content` VARCHAR(255) NOT NULL DEFAULT '',
|
||
`is_read` TINYINT NOT NULL DEFAULT 0 COMMENT '0未读1已读',
|
||
`create_at` INT NOT NULL DEFAULT 0,
|
||
PRIMARY KEY (`id`),
|
||
KEY `user_id` (`user_id`),
|
||
KEY `is_read` (`is_read`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='论坛消息';
|
||
|
||
-- ----------------------------
|
||
-- 种子数据：默认版块（与 Fly 专栏一致）
|
||
-- ----------------------------
|
||
INSERT INTO `__PREFIX__forum_board` (`id`, `parent_id`, `name`, `slug`, `icon`, `description`, `sort`, `status`, `create_at`, `update_at`)
|
||
SELECT 1, 0, '提问', 'ask', '', '遇到难题？在这里提问', 1, 1, UNIX_TIMESTAMP(), UNIX_TIMESTAMP()
|
||
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `__PREFIX__forum_board` WHERE `id` = 1);
|
||
|
||
INSERT INTO `__PREFIX__forum_board` (`id`, `parent_id`, `name`, `slug`, `icon`, `description`, `sort`, `status`, `create_at`, `update_at`)
|
||
SELECT 2, 0, '分享', 'share', '', '好东西大家一起分享', 2, 1, UNIX_TIMESTAMP(), UNIX_TIMESTAMP()
|
||
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `__PREFIX__forum_board` WHERE `id` = 2);
|
||
|
||
INSERT INTO `__PREFIX__forum_board` (`id`, `parent_id`, `name`, `slug`, `icon`, `description`, `sort`, `status`, `create_at`, `update_at`)
|
||
SELECT 3, 0, '讨论', 'discuss', '', '随意讨论技术话题', 3, 1, UNIX_TIMESTAMP(), UNIX_TIMESTAMP()
|
||
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `__PREFIX__forum_board` WHERE `id` = 3);
|
||
|
||
INSERT INTO `__PREFIX__forum_board` (`id`, `parent_id`, `name`, `slug`, `icon`, `description`, `sort`, `status`, `create_at`, `update_at`)
|
||
SELECT 4, 0, '建议', 'suggest', '', '给社区提建议', 4, 1, UNIX_TIMESTAMP(), UNIX_TIMESTAMP()
|
||
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `__PREFIX__forum_board` WHERE `id` = 4);
|
||
|
||
INSERT INTO `__PREFIX__forum_board` (`id`, `parent_id`, `name`, `slug`, `icon`, `description`, `sort`, `status`, `create_at`, `update_at`)
|
||
SELECT 5, 0, '公告', 'notice', '', '社区公告', 5, 1, UNIX_TIMESTAMP(), UNIX_TIMESTAMP()
|
||
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `__PREFIX__forum_board` WHERE `id` = 5);
|
||
|
||
INSERT INTO `__PREFIX__forum_board` (`id`, `parent_id`, `name`, `slug`, `icon`, `description`, `sort`, `status`, `create_at`, `update_at`)
|
||
SELECT 6, 0, '动态', 'dynamic', '', '社区新鲜事', 6, 1, UNIX_TIMESTAMP(), UNIX_TIMESTAMP()
|
||
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `__PREFIX__forum_board` WHERE `id` = 6);
|