You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
144 lines
6.9 KiB
144 lines
6.9 KiB
-- =====================================================
|
|
-- AmazingData 数据服务平台 - MySQL 数据库初始化脚本
|
|
-- 版本: 1.0
|
|
-- 日期: 2026-04-09
|
|
-- =====================================================
|
|
|
|
CREATE DATABASE IF NOT EXISTS amazingdata_platform DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
USE amazingdata_platform;
|
|
|
|
-- =====================================================
|
|
-- 1. 系统配置表
|
|
-- =====================================================
|
|
CREATE TABLE IF NOT EXISTS `system_config` (
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`config_key` VARCHAR(100) NOT NULL UNIQUE COMMENT '配置键',
|
|
`config_value` TEXT COMMENT '配置值',
|
|
`config_type` VARCHAR(20) DEFAULT 'string' COMMENT '类型: string/number/boolean/json',
|
|
`description` VARCHAR(255) COMMENT '描述',
|
|
`created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
`updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
INDEX `idx_config_key` (`config_key`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='系统配置表';
|
|
|
|
-- =====================================================
|
|
-- 2. 用户表
|
|
-- =====================================================
|
|
CREATE TABLE IF NOT EXISTS `users` (
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`username` VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
|
|
`password_hash` VARCHAR(255) NOT NULL COMMENT '密码哈希',
|
|
`role` VARCHAR(20) DEFAULT 'user' COMMENT '角色: admin/user',
|
|
`is_active` TINYINT(1) DEFAULT 1 COMMENT '是否激活',
|
|
`last_login` DATETIME COMMENT '最后登录时间',
|
|
`created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
`updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
INDEX `idx_username` (`username`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';
|
|
|
|
-- =====================================================
|
|
-- 3. 订阅任务表
|
|
-- =====================================================
|
|
CREATE TABLE IF NOT EXISTS `subscription_tasks` (
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`task_name` VARCHAR(100) NOT NULL COMMENT '任务名称',
|
|
`codes` JSON NOT NULL COMMENT '品种代码列表',
|
|
`periods` JSON NOT NULL COMMENT '订阅周期列表',
|
|
`save_path` VARCHAR(255) COMMENT '保存路径',
|
|
`duration` INT DEFAULT 0 COMMENT '运行时长(秒), 0=无限',
|
|
`save_interval` INT DEFAULT 60 COMMENT '保存间隔(秒)',
|
|
`status` VARCHAR(20) DEFAULT 'pending' COMMENT '状态: pending/running/stopped/error',
|
|
`subscription_id` VARCHAR(100) COMMENT '订阅实例ID',
|
|
`started_at` DATETIME COMMENT '开始时间',
|
|
`stopped_at` DATETIME COMMENT '停止时间',
|
|
`created_by` VARCHAR(50) COMMENT '创建人',
|
|
`created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
`updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
INDEX `idx_status` (`status`),
|
|
INDEX `idx_created_at` (`created_at`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订阅任务表';
|
|
|
|
-- =====================================================
|
|
-- 4. 批量任务表
|
|
-- =====================================================
|
|
CREATE TABLE IF NOT EXISTS `batch_tasks` (
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`task_type` VARCHAR(20) NOT NULL COMMENT '类型: stock/future',
|
|
`task_params` JSON COMMENT '任务参数',
|
|
`total_count` INT DEFAULT 0 COMMENT '总数量',
|
|
`processed_count` INT DEFAULT 0 COMMENT '已处理数量',
|
|
`success_count` INT DEFAULT 0 COMMENT '成功数量',
|
|
`failed_count` INT DEFAULT 0 COMMENT '失败数量',
|
|
`status` VARCHAR(20) DEFAULT 'pending' COMMENT '状态',
|
|
`output_path` VARCHAR(255) COMMENT '输出路径',
|
|
`error_message` TEXT COMMENT '错误信息',
|
|
`started_at` DATETIME,
|
|
`completed_at` DATETIME,
|
|
`created_by` VARCHAR(50),
|
|
`created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
`updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
INDEX `idx_status` (`status`),
|
|
INDEX `idx_task_type` (`task_type`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='批量任务表';
|
|
|
|
-- =====================================================
|
|
-- 5. 数据缓存记录表
|
|
-- =====================================================
|
|
CREATE TABLE IF NOT EXISTS `cache_records` (
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`filename` VARCHAR(255) NOT NULL COMMENT '文件名',
|
|
`file_type` VARCHAR(20) NOT NULL COMMENT '类型: stock/future/realtime',
|
|
`trading_day` VARCHAR(8) COMMENT '交易日',
|
|
`code` VARCHAR(20) COMMENT '代码',
|
|
`period` VARCHAR(10) COMMENT '周期',
|
|
`record_count` INT DEFAULT 0 COMMENT '记录数',
|
|
`file_size` BIGINT DEFAULT 0 COMMENT '文件大小(字节)',
|
|
`file_path` VARCHAR(255) COMMENT '完整路径',
|
|
`created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
INDEX `idx_file_type` (`file_type`),
|
|
INDEX `idx_trading_day` (`trading_day`),
|
|
INDEX `idx_filename` (`filename`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='数据缓存记录表';
|
|
|
|
-- =====================================================
|
|
-- 6. 操作日志表
|
|
-- =====================================================
|
|
CREATE TABLE IF NOT EXISTS `operation_logs` (
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`user_id` INT COMMENT '用户ID',
|
|
`operation` VARCHAR(50) NOT NULL COMMENT '操作类型',
|
|
`resource` VARCHAR(100) COMMENT '操作资源',
|
|
`detail` TEXT COMMENT '详细信息',
|
|
`ip_address` VARCHAR(45) COMMENT 'IP地址',
|
|
`status` VARCHAR(20) DEFAULT 'success' COMMENT '状态',
|
|
`error_message` TEXT COMMENT '错误信息',
|
|
`created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
INDEX `idx_user_id` (`user_id`),
|
|
INDEX `idx_operation` (`operation`),
|
|
INDEX `idx_created_at` (`created_at`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='操作日志表';
|
|
|
|
-- =====================================================
|
|
-- 初始数据
|
|
-- =====================================================
|
|
|
|
-- 系统配置初始数据
|
|
INSERT INTO `system_config` (`config_key`, `config_value`, `config_type`, `description`) VALUES
|
|
('amazing_data_username', '11200008169', 'string', 'AmazingData 用户名'),
|
|
('amazing_data_password', '11200008169@2026', 'string', 'AmazingData 密码'),
|
|
('amazing_data_host', '140.206.44.234', 'string', 'AmazingData 服务器地址'),
|
|
('amazing_data_port', '8600', 'number', 'AmazingData 端口'),
|
|
('realtime_save_days', '7', 'number', '实时数据保存天数'),
|
|
('cache_auto_save_interval', '60', 'number', '缓存自动保存间隔(秒)'),
|
|
('max_concurrent_tasks', '5', 'number', '最大并发任务数'),
|
|
('data_save_path', './data', 'string', '数据保存路径');
|
|
|
|
-- 默认管理员账号 (密码: admin123)
|
|
-- 密码哈希使用 bcrypt 生成
|
|
INSERT INTO `users` (`username`, `password_hash`, `role`) VALUES
|
|
('admin', '$2b$12$LQv3c1yqBWVHxkd0LHAkCOYz6TtxMQJqhN8/LewY5GyYILp92S.0i', 'admin');
|
|
|
|
-- =====================================================
|
|
-- 完成
|
|
-- =====================================================
|
|
SELECT 'Database initialization completed successfully!' AS message; |