KingBoxingUpdateController.class.php
7.2 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
<?php
/**
* Created by PhpStorm.
* User: xtong
* Date: 17/9/1
* Time: 17:39
*/
namespace Frontend\Controller\UpdateDB;
class KingBoxingUpdateController extends AbstractController
{
/*
* 迭代上线时间，2017年09月04日 16:00:00
*/
/**
* 劲霸（临时）2.5过渡版本迭代，数据库升级脚本
* @author xtong
* @date 2017-09-01
*/
public function Index()
{
$sql = $this->_updateSql();
if (empty($sql)) {
exit('没有可升级的sql');
}
$db = \Think\Db::getInstance();
try {
$db->execute($sql);
} catch (\Exception $e) {
exit("数据库升级失败：" . $e->getMessage());
}
exit("数据库升级完成，执行sql如下：{$sql}");
}
/**
* 待升级的sql
* 1. oa_exam_answer（考试答卷表），增加索引answer_status，uid，ep_id_answer_status，ep_id联合索引
* 2. oa_exam_answer_attach（答卷附件表），增加索引ea_id
* 3. 新增oa_exam_answer_detail_temp（考试-随机答卷详情临时表）
* 4. 新增oa_exam_answer_temp（考试-随机答卷临时表）
* 5. oa_exam_break（闯关主表），增加索引ec_id
* 6. oa_exam_break_detail（闯关答题详情表），增加索引ebreak_id
* 7. oa_exam_category（考试分类表），增加索引ec_status
* 8. oa_exam_like（考试点赞表），增加索引uid
* 9. oa_exam_medal_record（考试勋章记录表），增加索引uid
* 10. oa_exam_medal_relation（考试勋章关系表），增加索引em_id
* 11. oa_exam_paper（考试主表），新增字段
* cron_rank_id排名列表定时任务ID，
* corn_create_exam自动创建考卷的cornid，
* cron_statistics考试统计的cornid
* 12. oa_exam_paper_temp（考试临时试卷储存），增加索引ep_id
* 13. oa_exam_right（考试权限表），增加联合索引epc_id_er_type
* 14. oa_exam_snapshot（试卷快照表），增加索引ep_id，et_id
* 15. oa_exam_statistics（考试统计表），修改
* created,updated,deleted 为 bigint13
* 16. oa_exam_topic（考试题目表），增加索引eb_id，et_type
* @author xtong
* @return string
*/
private function _updateSql()
{
$sql = "
ALTER TABLE `oa_exam_answer` ADD INDEX (`answer_status`);
ALTER TABLE `oa_exam_answer` ADD INDEX uid (`uid`) USING BTREE;
ALTER TABLE `oa_exam_answer` ADD INDEX ep_id_answer_status (`ep_id`,`answer_status`) USING BTREE;
ALTER TABLE `oa_exam_answer` ADD INDEX ep_id (`ep_id`,`domain`,`status`,`answer_status`) USING BTREE;
ALTER TABLE `oa_exam_answer_attach` ADD INDEX (`ea_id`);
CREATE TABLE IF NOT EXISTS `oa_exam_answer_detail_temp` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
`eat_id` INT(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT '随机答卷id',
`er_id` INT(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT '随机试卷库快照表id',
`ep_id` INT(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT '试卷id',
`et_id` INT(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT '题目id',
`et_type` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0' COMMENT '题目类型(1:单选题 2:判断题 3:问答题 4:多选题,5:语音题)',
`title` TEXT NOT NULL COMMENT '题目名称',
`title_pic` TEXT NOT NULL COMMENT '题目图片（逗号分割）',
`score` DECIMAL(18,2) UNSIGNED NOT NULL DEFAULT '0.00' COMMENT '分数',
`options` TEXT COMMENT '选项(序列化:选项名称,选项值，图片ID)',
`answer` TEXT NOT NULL COMMENT '正确答案（多选用逗号分隔）',
`answer_resolve` TEXT NOT NULL COMMENT '答案解析',
`answer_coverage` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '答案覆盖率（问答题）',
`match_type` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0' COMMENT '是否匹配关键字(0:否 1:是)',
`answer_keyword` TEXT NOT NULL COMMENT '答案关键字(序列化:关键字，百分比)',
`order_num` INT(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT '序号',
`domain` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '企业域名',
`status` TINYINT(1) UNSIGNED NOT NULL DEFAULT '1' COMMENT '状态, 1=初始化，2=已更新，3=已删除',
`created` BIGINT(13) UNSIGNED NOT NULL DEFAULT '0' COMMENT '创建时间',
`updated` BIGINT(13) UNSIGNED NOT NULL DEFAULT '0' COMMENT '更新时间',
`deleted` BIGINT(13) UNSIGNED NOT NULL DEFAULT '0' COMMENT '删除时间',
PRIMARY KEY (`id`),
KEY `domain_status` (`domain`,`status`),
KEY `eat_id` (`eat_id`),
KEY `ep_id` (`ep_id`),
KEY `et_id` (`et_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='考试-随机答卷详情临时表';
CREATE TABLE IF NOT EXISTS `oa_exam_answer_temp` (
`eat_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
`ep_id` INT(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT '试卷id',
`domain` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '企业域名',
`status` TINYINT(1) UNSIGNED NOT NULL DEFAULT '1' COMMENT '状态, 1=初始化，2=已更新，3=已删除',
`created` BIGINT(13) UNSIGNED NOT NULL DEFAULT '0' COMMENT '创建时间',
`updated` BIGINT(13) UNSIGNED NOT NULL DEFAULT '0' COMMENT '更新时间',
`deleted` BIGINT(13) UNSIGNED NOT NULL DEFAULT '0' COMMENT '删除时间',
PRIMARY KEY (`eat_id`),
KEY `domain_status` (`domain`,`status`),
KEY `ep_id` (`ep_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='考试-随机答卷临时表';
ALTER TABLE `oa_exam_break` ADD INDEX (`ec_id`);
ALTER TABLE `oa_exam_break_detail` ADD INDEX (`ebreak_id`);
ALTER TABLE `oa_exam_category` ADD INDEX (`ec_status`);
ALTER TABLE `oa_exam_like` ADD INDEX (`uid`);
ALTER TABLE `oa_exam_medal_record` ADD INDEX (`uid`);
ALTER TABLE `oa_exam_medal_relation` ADD INDEX (`em_id`);
ALTER TABLE `oa_exam_paper` ADD `corn_create_exam` CHAR(32) NOT NULL DEFAULT '' COMMENT '自动创建考卷的cornid' AFTER `is_old`;
ALTER TABLE `oa_exam_paper` ADD `cron_rank_id` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '排名列表定时任务ID' AFTER `corn_create_exam`;
ALTER TABLE `oa_exam_paper` ADD `cron_statistics` CHAR(32) NOT NULL DEFAULT '' COMMENT '考试统计的cornid' AFTER `corn_create_exam`;
ALTER TABLE `oa_exam_paper_temp` ADD INDEX (`ep_id`);
ALTER TABLE `oa_exam_right` ADD INDEX epc_id_er_type (`epc_id`,`er_type`);
ALTER TABLE `oa_exam_snapshot` ADD INDEX (`ep_id`);
ALTER TABLE `oa_exam_snapshot` ADD INDEX (`et_id`);
ALTER TABLE `oa_exam_topic` ADD INDEX (`eb_id`);
ALTER TABLE `oa_exam_topic` ADD INDEX (`et_type`);
";
return $sql;
}
}