# 栏目表
CREATE TABLE CATEGORY (
CATEGORY_ID VARCHAR(32) NOT NULL,
PARENT_CATEGORY_ID VARCHAR(32),
CATEGORY_NAME VARCHAR(100) NOT NULL,
CATEGORY_CODE VARCHAR(50),
CATEGORY_LEVEL INTEGER,
SORT_ORDER INTEGER,
CATEGORY_TYPE VARCHAR(20),
CATEGORY_STATUS VARCHAR(10) NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_CATEGORY PRIMARY KEY (CATEGORY_ID)
) ORGANIZE BY ROW;

# 文章表
CREATE TABLE ARTICLE (
ARTICLE_ID VARCHAR(32) NOT NULL,
TITLE VARCHAR(500) NOT NULL,
SUBTITLE VARCHAR(500),
AUTHOR VARCHAR(100),
SOURCE VARCHAR(200),
KEYWORDS VARCHAR(500),
SUMMARY VARCHAR(2000),
CONTENT CLOB,
COVER_IMAGE VARCHAR(500),
CATEGORY_ID VARCHAR(32),
IS_TOP CHAR(1) DEFAULT ‘N’,
IS_HOT CHAR(1) DEFAULT ‘N’,
IS_RECOMMEND CHAR(1) DEFAULT ‘N’,
VIEW_COUNT INTEGER DEFAULT 0,
LIKE_COUNT INTEGER DEFAULT 0,
COMMENT_COUNT INTEGER DEFAULT 0,
ARTICLE_STATUS VARCHAR(20) NOT NULL,
PUBLISH_TIME TIMESTAMP,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CREATE_USER VARCHAR(32),
UPDATE_USER VARCHAR(32),
CONSTRAINT PK_ARTICLE PRIMARY KEY (ARTICLE_ID)
) ORGANIZE BY ROW;

# 文章版本表
CREATE TABLE ARTICLE_VERSION (
VERSION_ID VARCHAR(32) NOT NULL,
ARTICLE_ID VARCHAR(32) NOT NULL,
VERSION_NO INTEGER NOT NULL,
TITLE VARCHAR(500),
CONTENT CLOB,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CREATE_USER VARCHAR(32),
CONSTRAINT PK_ARTICLE_VERSION PRIMARY KEY (VERSION_ID)
) ORGANIZE BY ROW;

# 创建索引
CREATE INDEX IDX_CATEGORY_PARENT ON CATEGORY(PARENT_CATEGORY_ID);
CREATE INDEX IDX_ARTICLE_CATEGORY ON ARTICLE(CATEGORY_ID, ARTICLE_STATUS, PUBLISH_TIME DESC);
CREATE INDEX IDX_ARTICLE_STATUS ON ARTICLE(ARTICLE_STATUS, PUBLISH_TIME DESC);
CREATE INDEX IDX_ARTICLE_TOP ON ARTICLE(IS_TOP, PUBLISH_TIME DESC);
CREATE INDEX IDX_ARTICLE_VERSION_ARTICLE ON ARTICLE_VERSION(ARTICLE_ID, VERSION_NO DESC);

# 媒体表
CREATE TABLE MEDIA (
MEDIA_ID VARCHAR(32) NOT NULL,
MEDIA_NAME VARCHAR(200) NOT NULL,
MEDIA_TYPE VARCHAR(20) NOT NULL,
MEDIA_FORMAT VARCHAR(20),
FILE_SIZE BIGINT,
FILE_PATH VARCHAR(500),
FILE_URL VARCHAR(500),
THUMBNAIL_URL VARCHAR(500),
WIDTH INTEGER,
HEIGHT INTEGER,
DURATION INTEGER,
MEDIA_DESC VARCHAR(500),
UPLOAD_USER VARCHAR(32),
MEDIA_STATUS VARCHAR(10) NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_MEDIA PRIMARY KEY (MEDIA_ID)
) ORGANIZE BY ROW;

# 标签表
CREATE TABLE TAG (
TAG_ID VARCHAR(32) NOT NULL,
TAG_NAME VARCHAR(100) NOT NULL,
TAG_TYPE VARCHAR(20),
USE_COUNT INTEGER DEFAULT 0,
TAG_STATUS VARCHAR(10) NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_TAG PRIMARY KEY (TAG_ID)
) ORGANIZE BY ROW;

# 文章标签关联表
CREATE TABLE ARTICLE_TAG (
ARTICLE_ID VARCHAR(32) NOT NULL,
TAG_ID VARCHAR(32) NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_ARTICLE_TAG PRIMARY KEY (ARTICLE_ID, TAG_ID)
) ORGANIZE BY ROW;

# 评论表
CREATE TABLE COMMENT (
COMMENT_ID VARCHAR(32) NOT NULL,
ARTICLE_ID VARCHAR(32) NOT NULL,
PARENT_COMMENT_ID VARCHAR(32),
USER_ID VARCHAR(32),
USER_NAME VARCHAR(100),
CONTENT VARCHAR(2000) NOT NULL,
LIKE_COUNT INTEGER DEFAULT 0,
COMMENT_STATUS VARCHAR(20) NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_COMMENT PRIMARY KEY (COMMENT_ID)
) ORGANIZE BY ROW;

# 创建索引
CREATE INDEX IDX_MEDIA_TYPE ON MEDIA(MEDIA_TYPE, MEDIA_STATUS);
CREATE UNIQUE INDEX IDX_TAG_NAME ON TAG(TAG_NAME);
CREATE INDEX IDX_ARTICLE_TAG_TAG ON ARTICLE_TAG(TAG_ID);
CREATE INDEX IDX_COMMENT_ARTICLE ON COMMENT(ARTICLE_ID, CREATE_TIME DESC);
CREATE INDEX IDX_COMMENT_PARENT ON COMMENT(PARENT_COMMENT_ID);

# 发布文章存储过程
CREATE OR REPLACE PROCEDURE SP_PUBLISH_ARTICLE(
IN p_article_id VARCHAR(32),
IN p_title VARCHAR(500),
IN p_content CLOB,
IN p_category_id VARCHAR(32),
IN p_author VARCHAR(100),
IN p_user_id VARCHAR(32),
OUT p_result_code VARCHAR(10),
OUT p_result_msg VARCHAR(200)
)
LANGUAGE SQL
BEGIN
DECLARE v_version_no INTEGER;

SET p_result_code = ‘SUCCESS’;
SET p_result_msg = ‘文章发布成功’;

SELECT COALESCE(MAX(VERSION_NO), 0) + 1 INTO v_version_no
FROM ARTICLE_VERSION
WHERE ARTICLE_ID = p_article_id;

INSERT INTO ARTICLE_VERSION (
VERSION_ID, ARTICLE_ID, VERSION_NO, TITLE, CONTENT, CREATE_USER
) VALUES (
‘VER’ || TO_CHAR(CURRENT TIMESTAMP, ‘YYYYMMDDHH24MISSFF6’),
p_article_id,
v_version_no,
p_title,
p_content,
p_user_id
);

UPDATE ARTICLE
SET TITLE = p_title,
CONTENT = p_content,
CATEGORY_ID = p_category_id,
AUTHOR = p_author,
ARTICLE_STATUS = ‘PUBLISHED’,
PUBLISH_TIME = CURRENT TIMESTAMP,
UPDATE_TIME = CURRENT TIMESTAMP,
UPDATE_USER = p_user_id
WHERE ARTICLE_ID = p_article_id;

END;

# 增加文章浏览量
CREATE OR REPLACE PROCEDURE SP_INCREMENT_VIEW_COUNT(
IN p_article_id VARCHAR(32),
OUT p_result_code VARCHAR(10),
OUT p_result_msg VARCHAR(200)
)
LANGUAGE SQL
BEGIN
SET p_result_code = ‘SUCCESS’;
SET p_result_msg = ‘操作成功’;

UPDATE ARTICLE
SET VIEW_COUNT = VIEW_COUNT + 1
WHERE ARTICLE_ID = p_article_id;

END;

# 创建全文索引
CREATE INDEX IDX_ARTICLE_TEXT ON ARTICLE(TITLE, SUMMARY, CONTENT)
EXTEND USING DB2TEXT;

# 全文搜索
SELECT
ARTICLE_ID,
TITLE,
SUMMARY,
PUBLISH_TIME,
VIEW_COUNT
FROM ARTICLE
WHERE CONTAINS(CONTENT, ‘数据库’) = 1
AND ARTICLE_STATUS = ‘PUBLISHED’
ORDER BY PUBLISH_TIME DESC
FETCH FIRST 20 ROWS ONLY;

# 组合搜索
SELECT
ARTICLE_ID,
TITLE,
SUMMARY,
PUBLISH_TIME,
VIEW_COUNT
FROM ARTICLE
WHERE CONTAINS(TITLE, ‘DB2’) = 1
OR CONTAINS(SUMMARY, ‘DB2’) = 1
OR CONTAINS(CONTENT, ‘DB2’) = 1
AND ARTICLE_STATUS = ‘PUBLISHED’
ORDER BY PUBLISH_TIME DESC;

# 按栏目和标签查询
SELECT
a.ARTICLE_ID,
a.TITLE,
a.SUMMARY,
a.PUBLISH_TIME,
a.VIEW_COUNT,
c.CATEGORY_NAME
FROM ARTICLE a
INNER JOIN CATEGORY c ON a.CATEGORY_ID = c.CATEGORY_ID
INNER JOIN ARTICLE_TAG at ON a.ARTICLE_ID = at.ARTICLE_ID
INNER JOIN TAG t ON at.TAG_ID = t.TAG_ID
WHERE a.ARTICLE_STATUS = ‘PUBLISHED’
AND c.CATEGORY_ID = ‘CAT001’
AND t.TAG_NAME = ‘数据库’
ORDER BY a.PUBLISH_TIME DESC
FETCH FIRST 20 ROWS ONLY;

# 热门文章查询
SELECT
ARTICLE_ID,
TITLE,
SUMMARY,
VIEW_COUNT,
LIKE_COUNT,
PUBLISH_TIME
FROM ARTICLE
WHERE ARTICLE_STATUS = ‘PUBLISHED’
AND PUBLISH_TIME > CURRENT TIMESTAMP – 7 DAYS
ORDER BY VIEW_COUNT DESC
FETCH FIRST 10 ROWS ONLY;