89 lines
4.8 KiB
Markdown
89 lines
4.8 KiB
Markdown
-- =============================================
|
||
-- 定时任务调度服务 - 数据库初始化脚本
|
||
-- =============================================
|
||
|
||
-- 创建数据库
|
||
CREATE DATABASE IF NOT EXISTS erp_task DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
||
|
||
USE erp_task;
|
||
|
||
-- 定时任务表
|
||
CREATE TABLE IF NOT EXISTS scheduled_task (
|
||
id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '任务ID',
|
||
task_name VARCHAR(100) NOT NULL COMMENT '任务名称',
|
||
description VARCHAR(500) COMMENT '任务描述',
|
||
task_group VARCHAR(50) NOT NULL COMMENT '任务分组',
|
||
cron_expression VARCHAR(100) NOT NULL COMMENT 'Cron表达式',
|
||
task_class VARCHAR(500) NOT NULL COMMENT '任务类名或Bean名称',
|
||
method_name VARCHAR(100) COMMENT '任务方法名',
|
||
task_params TEXT COMMENT '任务参数(JSON格式)',
|
||
status VARCHAR(20) NOT NULL DEFAULT 'DRAFT' COMMENT '任务状态: RUNNING/STOPPED/PAUSED/DRAFT/DELETED/ERROR',
|
||
concurrent BOOLEAN NOT NULL DEFAULT TRUE COMMENT '是否允许并发',
|
||
sync BOOLEAN NOT NULL DEFAULT FALSE COMMENT '是否同步执行',
|
||
task_type VARCHAR(20) NOT NULL DEFAULT 'BEAN' COMMENT '任务类型: BEAN/CLASS/XXL_JOB',
|
||
xxl_job_id BIGINT COMMENT 'XXL-Job任务ID(兼容模式)',
|
||
max_retries INT NOT NULL DEFAULT 3 COMMENT '最大重试次数',
|
||
retry_interval INT NOT NULL DEFAULT 60 COMMENT '重试间隔(秒)',
|
||
timeout INT COMMENT '超时时间(秒)',
|
||
alert_emails VARCHAR(500) COMMENT '告警邮箱(逗号分隔)',
|
||
alert_phones VARCHAR(200) COMMENT '告警手机号(逗号分隔)',
|
||
alert_enabled BOOLEAN NOT NULL DEFAULT FALSE COMMENT '是否启用告警',
|
||
misfire_policy VARCHAR(20) DEFAULT 'DO_NOTHING' COMMENT 'Misfire策略: DO_NOTHING/FIRE_NOW/FIRE_ONCE',
|
||
next_fire_time DATETIME COMMENT '下次执行时间',
|
||
prev_fire_time DATETIME COMMENT '上次执行时间',
|
||
execute_count BIGINT NOT NULL DEFAULT 0 COMMENT '执行次数',
|
||
owner VARCHAR(50) COMMENT '负责人',
|
||
remark VARCHAR(500) COMMENT '备注',
|
||
tenant_id VARCHAR(50) COMMENT '租户ID',
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
created_by VARCHAR(50) COMMENT '创建人',
|
||
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
updated_by VARCHAR(50) COMMENT '更新人',
|
||
|
||
INDEX idx_task_name (task_name),
|
||
INDEX idx_task_group (task_group),
|
||
INDEX idx_task_status (status),
|
||
INDEX idx_cron (cron_expression),
|
||
UNIQUE KEY uk_name_group (task_name, task_group)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='定时任务表';
|
||
|
||
-- 任务执行日志表
|
||
CREATE TABLE IF NOT EXISTS task_execution_log (
|
||
id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '记录ID',
|
||
task_id BIGINT NOT NULL COMMENT '任务ID',
|
||
task_name VARCHAR(100) NOT NULL COMMENT '任务名称',
|
||
task_group VARCHAR(50) COMMENT '任务分组',
|
||
batch_no VARCHAR(64) NOT NULL COMMENT '执行批次号',
|
||
trigger_time DATETIME NOT NULL COMMENT '触发时间',
|
||
start_time DATETIME COMMENT '开始时间',
|
||
end_time DATETIME COMMENT '结束时间',
|
||
status VARCHAR(20) NOT NULL COMMENT '执行状态: RUNNING/SUCCESS/FAILED/RETRYING/TIMEOUT/CANCELLED',
|
||
duration BIGINT COMMENT '执行耗时(毫秒)',
|
||
executor_node VARCHAR(50) COMMENT '执行节点IP',
|
||
result TEXT COMMENT '执行结果',
|
||
error_trace TEXT COMMENT '错误堆栈',
|
||
retry_count INT NOT NULL DEFAULT 0 COMMENT '重试次数',
|
||
is_retry BOOLEAN DEFAULT FALSE COMMENT '是否重试执行',
|
||
task_params TEXT COMMENT '任务参数',
|
||
tenant_id VARCHAR(50) COMMENT '租户ID',
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
|
||
INDEX idx_log_task_id (task_id),
|
||
INDEX idx_log_status (status),
|
||
INDEX idx_log_start_time (start_time),
|
||
INDEX idx_log_trigger_time (trigger_time),
|
||
INDEX idx_batch_no (batch_no)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='任务执行日志表';
|
||
|
||
-- Quartz相关表 (如果使用JDBC存储)
|
||
-- 这些表由Quartz自动创建，这里只是预留
|
||
-- CREATE TABLE QRTZ_JOB_DETAILS (...) ENGINE=InnoDB;
|
||
-- CREATE TABLE QRTZ_TRIGGERS (...) ENGINE=InnoDB;
|
||
-- CREATE TABLE QRTZ_CRON_TRIGGERS (...) ENGINE=InnoDB;
|
||
|
||
-- 初始化示例数据
|
||
INSERT INTO scheduled_task (task_name, description, task_group, cron_expression, task_class, method_name, task_type, status, max_retries, alert_enabled, owner) VALUES
|
||
('数据同步任务', '每日凌晨同步业务数据', 'sync', '0 0 2 * * ?', 'dataSyncTask', 'execute', 'BEAN', 'STOPPED', 3, TRUE, 'admin'),
|
||
('报表生成任务', '每周一生成周报', 'report', '0 0 8 ? * MON', 'reportTask', 'generate', 'BEAN', 'STOPPED', 2, TRUE, 'admin'),
|
||
('缓存清理任务', '每小时清理过期缓存', 'system', '0 0 * * * ?', 'cacheCleanTask', 'clean', 'BEAN', 'RUNNING', 1, FALSE, 'system');
|