每日总结13

-- =============================================
-- 日程管理系统数据库建表语句
-- 版本: 1.0
-- =============================================

-- ----------------------------
-- Table structure for schedule (核心日程表)
-- ----------------------------
CREATE TABLE IF NOT EXISTS schedule (
scheduleID INTEGER PRIMARY KEY AUTOINCREMENT, -- 日程唯一标识
scheduleTypeID INTEGER NOT NULL DEFAULT 0, -- 日程类型ID (0=未分类)
remindID INTEGER DEFAULT NULL, -- 提醒规则ID (关联提醒系统)
scheduleContent TEXT NOT NULL, -- 日程内容 (加密存储)
scheduleDate TEXT NOT NULL, -- 基准日期 (ISO8601格式: YYYY-MM-DD)
createTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,-- 创建时间
updateTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,-- 最后更新时间
status INTEGER DEFAULT 1, -- 状态 (1=有效, 0=删除)
CHECK (length(scheduleDate) == 10) -- 日期格式校验
);

-- ----------------------------
-- Table structure for scheduletagdate (日期标签表)
-- ----------------------------
CREATE TABLE IF NOT EXISTS scheduletagdate (
tagID INTEGER PRIMARY KEY AUTOINCREMENT, -- 标签ID
year INTEGER NOT NULL, -- 年份 (YYYY)
month INTEGER NOT NULL CHECK (month BETWEEN 1 AND 12), -- 月份 (1-12)
day INTEGER NOT NULL CHECK (day BETWEEN 1 AND 31), -- 日 (1-31)
scheduleID INTEGER NOT NULL, -- 关联的日程ID
isMainDate BOOLEAN DEFAULT FALSE, -- 是否为主日期标记
FOREIGN KEY (scheduleID)
REFERENCES schedule(scheduleID)
ON DELETE CASCADE, -- 级联删除
CHECK (year BETWEEN 2000 AND 2100) -- 合理年份范围
);

-- ----------------------------
-- 索引配置
-- ----------------------------
CREATE INDEX IF NOT EXISTS idx_schedule_date ON schedule(scheduleDate);
CREATE INDEX IF NOT EXISTS idx_schedule_type ON schedule(scheduleTypeID);
CREATE UNIQUE INDEX IF NOT EXISTS idx_tag_date ON scheduletagdate(year, month, day, scheduleID);

-- ----------------------------
-- 触发器（更新时间自动维护）
-- ----------------------------
CREATE TRIGGER IF NOT EXISTS update_schedule_time
AFTER UPDATE ON schedule
BEGIN
UPDATE schedule SET updateTime = CURRENT_TIMESTAMP WHERE scheduleID = NEW.scheduleID;
END;

-- ----------------------------
-- 视图（常用查询封装）
-- ----------------------------
CREATE VIEW IF NOT EXISTS v_schedule_with_tags AS
SELECT
s.*,
GROUP_CONCAT(t.year || '-' || t.month || '-' || t.day, '; ') AS tagDates
FROM schedule s
LEFT JOIN scheduletagdate t ON s.scheduleID = t.scheduleID
GROUP BY s.scheduleID;