Home Explore Help
Register Sign In
wuzilong
/
buff
1
0
Fork 0
Files Issues 0 Pull Requests 0 Wiki
Branch: master
Branches Tags
buff
/
backend
/
create_system_config.sql

create_system_config.sql 8.9 KB
Permalink History Raw

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061 
  1. -- 创建system_config表（纵表设计）
    2. 

  2. CREATE TABLE IF NOT EXISTS `system_config` (
    3. 

  3.   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    4. 

  4.   `config_key` varchar(100) NOT NULL COMMENT '配置键名',
    5. 

  5.   `config_value` varchar(500) NOT NULL COMMENT '配置值',
    6. 

  6.   `config_type` enum('int','string','bool','json') DEFAULT 'string' COMMENT '配置值类型',
    7. 

  7.   `description` varchar(255) DEFAULT NULL COMMENT '配置描述',
    8. 

  8.   `is_system` tinyint(1) DEFAULT '1' COMMENT '是否为系统配置：1-是，0-否',
    9. 

  9.   `is_editable` tinyint(1) DEFAULT '1' COMMENT '是否可编辑：1-是，0-否',
    10. 

  10.   `is_deleted` tinyint(1) DEFAULT '0' COMMENT '是否删除：0-否，1-是',
    11. 

  11.   `created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    12. 

  12.   `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    13. 

  13.   PRIMARY KEY (`id`),
    14. 

  14.   UNIQUE KEY `uk_config_key` (`config_key`, `is_deleted`) COMMENT '配置键唯一约束'
    15. 

  15. ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='系统配置表（纵表设计）';
    16. 

  16. 

  17. -- 为 api_keys 表添加 updated_at 字段
    18. 

  18. ALTER TABLE `api_keys` ADD COLUMN `updated_at` datetime DEFAULT NULL COMMENT '更新时间';
    19. 

  19. 

  20. -- 创建 sessions 表
    21. 

  21. CREATE TABLE IF NOT EXISTS `sessions` (
    22. 

  22.   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    23. 

  23.   `session_id` varchar(64) NOT NULL COMMENT '会话ID',
    24. 

  24.   `user_id` int(11) NOT NULL COMMENT '用户ID',
    25. 

  25.   `machine_code` varchar(100) DEFAULT NULL COMMENT '机器码',
    26. 

  26.   `ip_address` varchar(45) DEFAULT NULL COMMENT 'IP地址',
    27. 

  27.   `user_agent` text COMMENT '用户代理',
    28. 

  28.   `is_active` tinyint(1) DEFAULT '1' COMMENT '是否激活：0-否，1-是',
    29. 

  29.   `expires_at` datetime NOT NULL COMMENT '过期时间',
    30. 

  30.   `created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    31. 

  31.   `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    32. 

  32.   `last_activity` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后活动时间',
    33. 

  33.   PRIMARY KEY (`id`),
    34. 

  34.   UNIQUE KEY `ix_sessions_session_id` (`session_id`),
    35. 

  35.   KEY `ix_sessions_user_id` (`user_id`),
    36. 

  36.   KEY `ix_sessions_is_active` (`is_active`),
    37. 

  37.   KEY `ix_sessions_expires_at` (`expires_at`)
    38. 

  38. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户会话表';
    39. 

  39. 

  40. -- 插入默认配置数据（纵表结构）
    41. 

  41. INSERT INTO `buff`.`system_config` (`id`, `config_key`, `config_value`, `config_type`, `description`, `is_system`, `is_editable`, `is_deleted`, `created_at`, `updated_at`) VALUES (1, 'heartbeat_interval_seconds', '30', 'int', '心跳间隔秒数（默认30秒）', 1, 1, 0, '2026-01-06 17:21:40', '2026-01-06 17:21:40');
    42. 

  42. INSERT INTO `buff`.`system_config` (`id`, `config_key`, `config_value`, `config_type`, `description`, `is_system`, `is_editable`, `is_deleted`, `created_at`, `updated_at`) VALUES (2, 'key_overlap_minutes', '30', 'int', '新旧密钥共存分钟数', 1, 1, 0, '2026-01-06 17:21:40', '2026-01-06 17:21:40');
    43. 

  43. INSERT INTO `buff`.`system_config` (`id`, `config_key`, `config_value`, `config_type`, `description`, `is_system`, `is_editable`, `is_deleted`, `created_at`, `updated_at`) VALUES (3, 'key_expire_minutes', '60', 'int', '密钥过期分钟数（从创建时间算起）', 1, 1, 0, '2026-01-06 17:21:40', '2026-01-06 17:21:40');
    44. 

  44. INSERT INTO `buff`.`system_config` (`id`, `config_key`, `config_value`, `config_type`, `description`, `is_system`, `is_editable`, `is_deleted`, `created_at`, `updated_at`) VALUES (4, 'max_heartbeat_failures', '10', 'int', '最大心跳失败次数，超过后暂停检测', 1, 1, 0, '2026-01-06 17:21:40', '2026-01-06 17:21:40');
    45. 

  45. INSERT INTO `buff`.`system_config` (`id`, `config_key`, `config_value`, `config_type`, `description`, `is_system`, `is_editable`, `is_deleted`, `created_at`, `updated_at`) VALUES (5, 'max_network_failures', '15', 'int', '网络异常最大失败次数，超过后登出', 1, 1, 0, '2026-01-06 17:21:40', '2026-01-06 17:21:40');
    46. 

  46. INSERT INTO `buff`.`system_config` (`id`, `config_key`, `config_value`, `config_type`, `description`, `is_system`, `is_editable`, `is_deleted`, `created_at`, `updated_at`) VALUES (6, 'prevent_duplicate_login', '1', 'bool', '是否防止重复登录：0-否，1-是', 1, 1, 0, '2026-01-06 17:21:40', '2026-01-06 17:21:40');
    47. 

  47. INSERT INTO `buff`.`system_config` (`id`, `config_key`, `config_value`, `config_type`, `description`, `is_system`, `is_editable`, `is_deleted`, `created_at`, `updated_at`) VALUES (7, 'timestamp_tolerance_seconds', '30', 'int', '时间戳容忍秒数（前后5分钟）', 1, 1, 0, '2026-01-06 17:21:40', '2026-01-07 23:47:41');
    48. 

  48. INSERT INTO `buff`.`system_config` (`id`, `config_key`, `config_value`, `config_type`, `description`, `is_system`, `is_editable`, `is_deleted`, `created_at`, `updated_at`) VALUES (8, 'session_extend_enabled', '1', 'bool', '是否启用心跳延长session：0-否，1-是', 1, 1, 0, '2026-01-06 17:21:40', '2026-01-06 17:21:40');
    49. 

  49. INSERT INTO `buff`.`system_config` (`id`, `config_key`, `config_value`, `config_type`, `description`, `is_system`, `is_editable`, `is_deleted`, `created_at`, `updated_at`) VALUES (9, 'session_extend_threshold_hours', '4', 'int', 'session延长阈值（小时），距离过期小于此值时延长', 1, 1, 0, '2026-01-06 17:21:40', '2026-01-06 17:21:40');
    50. 

  50. INSERT INTO `buff`.`system_config` (`id`, `config_key`, `config_value`, `config_type`, `description`, `is_system`, `is_editable`, `is_deleted`, `created_at`, `updated_at`) VALUES (10, 'session_extend_hours', '2', 'int', '每次心跳延长的小时数', 1, 1, 0, '2026-01-06 17:21:40', '2026-01-06 17:21:40');
    51. 

  51. INSERT INTO `buff`.`system_config` (`id`, `config_key`, `config_value`, `config_type`, `description`, `is_system`, `is_editable`, `is_deleted`, `created_at`, `updated_at`) VALUES (11, 'api_timeout_seconds', '30', 'int', 'API请求超时秒数', 1, 1, 0, '2026-01-06 17:21:40', '2026-01-06 17:21:40');
    52. 

  52. INSERT INTO `buff`.`system_config` (`id`, `config_key`, `config_value`, `config_type`, `description`, `is_system`, `is_editable`, `is_deleted`, `created_at`, `updated_at`) VALUES (12, 'client_heartbeat_interval_seconds', '30', 'int', '客户端心跳间隔秒数', 1, 1, 0, '2026-01-06 17:21:40', '2026-01-06 17:21:40');
    53. 

  53. INSERT INTO `buff`.`system_config` (`id`, `config_key`, `config_value`, `config_type`, `description`, `is_system`, `is_editable`, `is_deleted`, `created_at`, `updated_at`) VALUES (13, 'sweep_batch_size', '10', 'int', '扫货批量大小', 1, 1, 0, '2026-01-06 20:09:54', '2026-01-06 20:09:54');
    54. 

  54. INSERT INTO `buff`.`system_config` (`id`, `config_key`, `config_value`, `config_type`, `description`, `is_system`, `is_editable`, `is_deleted`, `created_at`, `updated_at`) VALUES (14, 'sweep_purchase_interval', '30', 'int', '扫货购买间隔秒数', 1, 1, 0, '2026-01-06 20:09:54', '2026-01-06 20:09:54');
    55. 

  55. INSERT INTO `buff`.`system_config` (`id`, `config_key`, `config_value`, `config_type`, `description`, `is_system`, `is_editable`, `is_deleted`, `created_at`, `updated_at`) VALUES (15, 'sweep_max_purchases', '100', 'int', '扫货最大购买数量', 1, 1, 0, '2026-01-06 20:09:54', '2026-01-06 20:09:54');
    56. 

  56. INSERT INTO `buff`.`system_config` (`id`, `config_key`, `config_value`, `config_type`, `description`, `is_system`, `is_editable`, `is_deleted`, `created_at`, `updated_at`) VALUES (16, 'page_size', '10', 'int', '分页每页显示数量', 1, 1, 0, '2026-01-06 20:09:54', '2026-01-06 20:09:54');
    57. 

  57. INSERT INTO `buff`.`system_config` (`id`, `config_key`, `config_value`, `config_type`, `description`, `is_system`, `is_editable`, `is_deleted`, `created_at`, `updated_at`) VALUES (17, 'steam_auth_countdown_seconds', '30', 'int', 'Steam验证倒计时秒数', 1, 1, 0, '2026-01-06 20:11:16', '2026-01-06 20:11:16');
    58. 

  58. INSERT INTO `buff`.`system_config` (`id`, `config_key`, `config_value`, `config_type`, `description`, `is_system`, `is_editable`, `is_deleted`, `created_at`, `updated_at`) VALUES (19, 'init_key_expire_days', '1', 'int', '初始化密钥过期天数（应用启动时创建的密钥）', 1, 1, 0, '2026-01-08 09:52:03', '2026-01-08 09:52:31');
    59. 

  59. INSERT INTO `buff`.`system_config` (`id`, `config_key`, `config_value`, `config_type`, `description`, `is_system`, `is_editable`, `is_deleted`, `created_at`, `updated_at`) VALUES (20, 'key_rotation_check_interval_minutes', '5', 'int', '密钥轮换检查间隔分钟数', 1, 1, 0, '2026-01-08 09:57:32', '2026-01-08 09:57:48');
    60. 

  60. INSERT INTO `buff`.`system_config` (`id`, `config_key`, `config_value`, `config_type`, `description`, `is_system`, `is_editable`, `is_deleted`, `created_at`, `updated_at`) VALUES (21, 'session_cleanup_interval_hours', '2', 'int', 'Session清理间隔小时数', 1, 1, 0, '2026-01-08 09:58:57', '2026-01-08 09:59:22');
    61. 

  61. INSERT INTO `buff`.`system_config` (`id`, `config_key`, `config_value`, `config_type`, `description`, `is_system`, `is_editable`, `is_deleted`, `created_at`, `updated_at`) VALUES (22, 'heartbeat_cleanup_interval_hours', '1', 'int', '心跳缓存清理间隔小时数', 1, 1, 0, '2026-01-08 09:58:57', '2026-01-08 09:59:22');
    62.