吴程程 / gxb-doc · Files

# 什么时间加的,谁加的,干什么的
#SQL语句

-- ----------------------------
-- 2016-03-22 pffan 直播相关表 START
-- ----------------------------
-- ----------------------------
-- Table structure for `webcast`
-- ----------------------------
DROP TABLE IF EXISTS `webcast`;
CREATE TABLE `webcast` (
  `webcast_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '直播id',
  `title` varchar(255) NOT NULL COMMENT '标题',
  `teacher_name` varchar(255) NOT NULL COMMENT '讲师姓名',
  `start_at` datetime NOT NULL COMMENT '直播开始时间',
  `end_at` datetime NOT NULL COMMENT '直播结束时间',
  `place` varchar(255) DEFAULT NULL COMMENT '直播地点',
  `live_id` varchar(255) DEFAULT NULL COMMENT '房间id',
  `is_support_signup_site` tinyint(4) DEFAULT NULL COMMENT '是否支持报名现场（1 是； 0 不是）',
  `is_prevent_cheating` tinyint(4) DEFAULT NULL COMMENT '是否防作弊（1 是； 0 不是）',
  `is_lock` tinyint(4) NOT NULL COMMENT '开启/关闭直播（1 开启； 0 关闭）',
  `introduce` mediumtext NOT NULL COMMENT '直播介绍',
  `teaching_required` varchar(2000) DEFAULT NULL COMMENT '教学要求',
  `signup_start_at` datetime DEFAULT NULL COMMENT '报名开始时间',
  `signup_end_at` datetime DEFAULT NULL COMMENT '报名结束时间',
  `signup_number` int(8) DEFAULT NULL COMMENT '报名人数',
  `cover` varchar(255) DEFAULT NULL COMMENT '封面（uuid）',
  `webcast_url` varchar(255) DEFAULT NULL COMMENT '直播url',
  `play_back_url` varchar(255) DEFAULT NULL COMMENT '回放url',
  `user_id` bigint(20) DEFAULT NULL COMMENT '创建人id',
  `editor_id` bigint(20) DEFAULT NULL COMMENT '编辑人id',
  `created_at` datetime DEFAULT NULL COMMENT '创建时间',
  `updated_at` datetime DEFAULT NULL COMMENT '更新时间',
  `delete_flag` tinyint(4) DEFAULT NULL COMMENT '1可用 0 不可用',
  PRIMARY KEY (`webcast_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='直播表';

-- ----------------------------
-- Table structure for `webcast_checkin`
-- ----------------------------
DROP TABLE IF EXISTS `webcast_checkin`;
CREATE TABLE `webcast_checkin` (
  `webcast_checkin_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '签到id',
  `webcast_id` bigint(20) NOT NULL COMMENT '直播id',
  `class_id` bigint(20) NOT NULL COMMENT '班次id',
  `user_id` bigint(20) NOT NULL COMMENT '用户id',
  `checkin_type` tinyint(4) NOT NULL COMMENT '签到类型（1：pc端签到；2：手机端签到；3：现场签到）',
  `checkin_at` datetime DEFAULT NULL COMMENT '签到时间',
  PRIMARY KEY (`webcast_checkin_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='直播签到表';

-- ----------------------------
-- Table structure for `webcast_material`
-- ----------------------------
DROP TABLE IF EXISTS `webcast_material`;
CREATE TABLE `webcast_material` (
  `webcast_material_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '直播资料id',
  `webcast_id` bigint(20) NOT NULL COMMENT '直播id',
  `title` varchar(255) NOT NULL COMMENT '标题',
  `material_path` varchar(255) NOT NULL COMMENT '资料路径',
  `description` mediumtext NOT NULL COMMENT '资料描述',
  `user_id` bigint(20) DEFAULT NULL COMMENT '创建人id',
  `editor_id` bigint(20) DEFAULT NULL COMMENT '编辑人id',
  `created_at` datetime DEFAULT NULL COMMENT '创建时间',
  `updated_at` datetime DEFAULT NULL COMMENT '更新时间',
  `delete_flag` tinyint(4) DEFAULT NULL COMMENT '1可用 0 不可用',
  PRIMARY KEY (`webcast_material_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='直播资料表';

-- ----------------------------
-- Table structure for `webcast_record`
-- ----------------------------
DROP TABLE IF EXISTS `webcast_record`;
CREATE TABLE `webcast_record` (
  `webcast_record_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `webcast_id` bigint(20) NOT NULL COMMENT '直播id',
  `class_id` bigint(20) NOT NULL COMMENT '班级Id',
  `user_id` bigint(20) NOT NULL COMMENT '用户id',
  `webcast_length` int(11) NOT NULL COMMENT '直播长度',
  `webcast_view_time` int(11) DEFAULT NULL COMMENT '观看直播长度',
  `play_back_view_time` int(11) DEFAULT NULL COMMENT '观看回放长度',
  `created_at` datetime DEFAULT NULL COMMENT '创建时间',
  `updated_at` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`webcast_record_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='直播观看记录表';

-- ----------------------------
-- Table structure for `webcast_sale`
-- ----------------------------
DROP TABLE IF EXISTS `webcast_sale`;
CREATE TABLE `webcast_sale` (
  `webcast_sale_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '售卖id',
  `webcast_id` bigint(20) NOT NULL COMMENT '直播id',
  `class_id` bigint(20) NOT NULL COMMENT '班级id',
  `user_id` bigint(20) DEFAULT NULL COMMENT '创建人id',
  `editor_id` bigint(20) DEFAULT NULL COMMENT '编辑人id',
  `created_at` datetime DEFAULT NULL COMMENT '创建时间',
  `updated_at` datetime DEFAULT NULL COMMENT '更新时间',
  `delete_flag` tinyint(4) DEFAULT NULL COMMENT '0不可用 1可用',
  PRIMARY KEY (`webcast_sale_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='直播售卖表';

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `webcast_signup`
-- ----------------------------
DROP TABLE IF EXISTS `webcast_signup`;
CREATE TABLE `webcast_signup` (
  `webcast_signup_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '报名id',
  `webcast_id` bigint(20) NOT NULL COMMENT '直播id',
  `user_id` bigint(20) NOT NULL COMMENT '报名用户id',
  `mobile` varchar(11) NOT NULL COMMENT '手机号',
  `created_at` datetime DEFAULT NULL COMMENT '创建时间',
  `updated_at` datetime DEFAULT NULL COMMENT '更新时间',
  `delete_flag` tinyint(4) DEFAULT NULL COMMENT '0不可用 1可用',
  PRIMARY KEY (`webcast_signup_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='直播报名表';

SET GLOBAL group_concat_max_len=200000;

-- ----------------------------
-- 2016-03-08 pffan 直播相关表 END
-- ----------------------------
alter TABLE submission add  is_distribution TINYINT;
alter TABLE submission add  is_calculateScore TINYINT ;
UPDATE submission SET is_distribution = 1 WHERE submitted_id IN (
		SELECT assignment_id FROM class_assignment WHERE is_peer_review = 1 AND is_distribution = 1)
AND submitted_type = 'Assignment';

UPDATE submission SET is_calculateScore = 1 WHERE submitted_id IN (
		SELECT assignment_id FROM class_assignment WHERE is_peer_review = 1 AND is_calculate_score = 1)
AND submitted_type = 'Assignment';
CREATE table transcript_log(
	class_id BIGINT COMMENT '班次id',
	user_id BIGINT COMMENT '用户id',
	error_message VARCHAR(2000) COMMENT '失败原因',
	created_at datetime COMMENT '创建时间',
	updated_at datetime COMMENT '更新时间'
);
create INDEX transcript_log_class_id_user_id on transcript_log(class_id,user_id);

alter table class_group_user add `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名' ;

update class_group_user c join user i on c.student_user_id=i.user_id  set c.name=i.name where c.student_user_id is not null;

update class_group_user c join student_temp i on c.no=i.no and c.tenant_id=i.tenant_id  set c.name=i.name where c.student_user_id is null;

alter table transcript add video_view_count int4;

-- 什么时间加的,谁加的,干什么的
-- SQL语句

-- 2016-03-11，张金霞，选课 start
CREATE TABLE class_select (
  class_select_id bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  class_id bigint(20) NOT NULL COMMENT '班次id',
  tenant_id bigint(20) DEFAULT NULL COMMENT '租户id',
  delete_flag int4 comment '1可用 0 不可用',
  PRIMARY KEY (class_select_id),
  KEY index_class_id (class_id),
  KEY index_tenant_id (tenant_id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE class_select_rule (
  class_select_rule_id bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  rule_id bigint(20) NOT NULL COMMENT '规则id',
  tenant_id bigint(20) DEFAULT NULL COMMENT '租户id',
  number double(12,2) COMMENT '数量',
  delete_flag int4 comment '1可用 0 不可用',
  PRIMARY KEY (class_select_rule_id),
  KEY index_tenant_id (tenant_id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

-- 线上插入选课数据
insert into class_select_rule(class_select_rule_id,rule_id,tenant_id,number,delete_flag) values (1,1,380,1,1);
insert into class_select(class_select_id,class_id,tenant_id,delete_flag) values
(1,4851,380,1),(1,4858,380,1),
(2,4852,380,1),(1,4862,380,1),
(3,4854,380,1),(1,4861,380,1),
(4,4866,380,1),(1,4859,380,1),
(5,4856,380,1);
alter TABLE transcript add updated_at datetime COMMENT '更新时间';
create INDEX transcript_studentno_course_id on transcript(student_no,course_id);
delete from transcript where user_id is null and student_no is null;
create INDEX index_transcript_course_id on transcript(course_id);
alter TABLE transcript add  class_group_user_id BIGINT COMMENT '选课记录id';


alter table video add column transcoding_progress tinyint(4) DEFAULT 2 COMMENT '1 正在转码；2 成功；3 失败';
create INDEX transcoding_status_index on video(transcoding_status);
create INDEX transcoding_progress_index on video(transcoding_progress);

CREATE TABLE courses_profiles (
  course_id bigint  NOT NULL auto_increment PRIMARY KEY,
  eval_count int4,
  eval_score_total  NUMERIC(10,2),
  eval_score_avg NUMERIC(10,2),
  created_at datetime,
  updated_at datetime
);

insert into gxb_core.courses_profiles select * from cms_production.courses_profiles;

ALTER TABLE class MODIFY COLUMN teach_mode varchar(255) DEFAULT NULL COMMENT '教学模式: 10 开放学习模式 20 传统学习模式 30 顺序学习模式';

CREATE TABLE exam_class_relate (
  exam_class_id bigint  NOT NULL auto_increment PRIMARY KEY,
  class_id BIGINT COMMENT '班次id',
  exam_id BIGINT COMMENT '考试id',
  user_id BIGINT COMMENT '创建者id',
  created_at datetime,
  updated_at datetime
);
create INDEX exam_class_relate_classid_index on exam_class_relate(class_id);


CREATE TABLE exam_score (
  exam_score_id bigint  NOT NULL auto_increment PRIMARY KEY,
  exam_id BIGINT COMMENT '考试id',
  class_id BIGINT COMMENT '班次id',
  user_id BIGINT COMMENT '学生id',
  score NUMERIC(5,2) COMMENT '考试成绩',
  created_at datetime,
  delete_flag tinyint(4) DEFAULT NULL COMMENT '是否可用 1 未删除 0 删除',
  updated_at datetime
);
alter table exam_score add COLUMN graded_id BIGINT COMMENT '批改老师id';



CREATE TABLE exam (
  exam_id bigint  NOT NULL auto_increment PRIMARY KEY,
  exam_name VARCHAR(255) COMMENT '考试名称',
  exam_category VARCHAR(255) COMMENT '考试分类',
  pass_score NUMERIC(5,2) COMMENT '及格分数',
  duration INT4 COMMENT '考试时长默认分钟',
  start_at datetime COMMENT '开始时间',
  end_at datetime COMMENT '结束时间',
  allowed_attempts INT4 COMMENT '允许考试次数 0 无限制',
  status TINYINT COMMENT '考试状态 1 正常 0 禁用',
  publish_score_type TINYINT COMMENT '成绩公布方式1显示成绩，0不显示',
  total_score NUMERIC(5,2) COMMENT '考试总分',
  user_id BIGINT COMMENT '创建人',
  tenant_id BIGINT COMMENT '租户id',
  delete_flag TINYINT COMMENT '是否删除 1 正常 0 删除',
  created_at datetime,
  updated_at datetime
);
alter table exam add COLUMN third_party_exam_id BIGINT COMMENT '第三方考试id,考试星考试id';
alter TABLE exam add COLUMN  least_schedule INT4 COMMENT '参加考试必须完成的学习进度';
alter TABLE exam add COLUMN  publish_score_delay INT4 COMMENT '考试结束后几天公布成绩';


alter table class_set add COLUMN exam_percent INT4 COMMENT '考试占比';

alter table transcript add COLUMN exam_percentile_score decimal(5,2) COMMENT '考试百分占比成绩';
alter table transcript add COLUMN exam_count INT4 COMMENT '考试数量';
alter table transcript add COLUMN sub_exam_count INT4 COMMENT '参加考试数量';

alter table class_question_relate add COLUMN push_status TINYINT DEFAULT 0 COMMENT '推送第三方状态 0：未推送；1：已推送';


-- ----------------------------
-- 2016-05-07 pffan 推送 START
-- ----------------------------

alter table class_question_relate drop COLUMN push_status;

CREATE TABLE `question_push_record` (
  `question_push_record_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '推送id',
  `class_id` bigint(20) NOT NULL COMMENT '班次id',
  `course_id` bigint(20) NOT NULL COMMENT '课程id',
  `tenant_id` bigint(20) NOT NULL COMMENT '租户id',
  `question_id` bigint(20) NOT NULL COMMENT '试题id',
  `push_at` datetime DEFAULT NULL COMMENT '推送时间',
  PRIMARY KEY (`question_push_record_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='试题推送记录表';

-- ----------------------------
-- 2016-05-07 pffan 推送 END
-- ----------------------------

-- ----------------------------
-- 2016-05-10 pffan START
-- ----------------------------
alter table webcast_material modify title varchar(255) null COMMENT '资料标题';
alter table webcast_material modify material_path varchar(255) null COMMENT '资料路径';
alter table webcast_material modify description mediumtext null COMMENT '资料描述';

CREATE TABLE `exam_record` (
  `exam_record_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `exam_id` bigint(20) DEFAULT NULL COMMENT '考试id',
  `class_id` bigint(20) DEFAULT NULL COMMENT '班次id',
  `user_id` bigint(20) DEFAULT NULL COMMENT '学生id',
  `exam_sub_id` bigint(20) DEFAULT NULL COMMENT '考试提交id',
  `submit_score` NUMERIC(5,2) DEFAULT NULL COMMENT '提交考试成绩',
  `grade_score` NUMERIC(5,2) DEFAULT NULL COMMENT '批改考试成绩',
  `submit_at` datetime DEFAULT NULL COMMENT '提交考试时间',
  `grade_at` datetime DEFAULT NULL COMMENT '批改考试时间',
  `delete_flag` tinyint(4) DEFAULT NULL COMMENT '是否可用 1 未删除 0 删除',
  PRIMARY KEY (`exam_record_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='考试学生记录表';

alter table class add column is_documented TINYINT(1) default 0 COMMENT '是否归档';
-- ----------------------------
-- 2016-05-10 pffan END
-- ----------------------------

-- ----------------------------
-- 2016-05-21 孙宁海  条件解锁
-- ----------------------------

alter table  gxb_core.class modify column teach_mode varchar(255) DEFAULT NULL COMMENT '教学模式: 10 开放学习模式 20 传统学习模式 30 顺序学习模式 40 条件解锁模式';
alter table  gxb_core.class add  column unlock_quiz_score  int4  DEFAULT 0 COMMENT '条件模式解锁条件,测验需要达到的分数';

-- 2016-05-18 孙宁海 成绩计算学习进度 END
-- ----------------------------

alter table transcript add COLUMN learn_rating int4  COMMENT '学习进度';

-- ----------------------------
-- 2016-05-31 张金霞 互动实践
-- ----------------------------
DROP TABLE IF EXISTS `interactive_practice`;
CREATE TABLE `interactive_practice` (
  `practice_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '互动实践id',
  `class_id` bigint(20) NOT NULL COMMENT '班次id',
  `name` varchar(255) NOT NULL COMMENT '名称',
  `place` varchar(255) NOT NULL COMMENT '地点',
  `start_at` datetime NOT NULL COMMENT '活动开始时间',
  `end_at` datetime NOT NULL COMMENT '活动结束时间',
  `introduce` mediumtext COMMENT '活动介绍',
  `signup_end_at` datetime DEFAULT NULL COMMENT '报名结束时间',
  `signup_number` int(8) DEFAULT NULL COMMENT '报名人数',
  `cover` varchar(255) DEFAULT NULL COMMENT '封面（uuid）',
  `cooperative_unit` varchar(255) DEFAULT NULL COMMENT '合作单位',
  `user_id` bigint(20) DEFAULT NULL COMMENT '创建人id',
  `editor_id` bigint(20) DEFAULT NULL COMMENT '编辑人id',
  `created_at` datetime DEFAULT NULL COMMENT '创建时间',
  `updated_at` datetime DEFAULT NULL COMMENT '更新时间',
  `delete_flag` tinyint(4) DEFAULT NULL COMMENT '1可用 0 不可用',
  PRIMARY KEY (`practice_id`),
  KEY `interactive_practice_classid_index` (`class_id`),
  KEY `interactive_practice_delete_flag_index` (`delete_flag`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8 COMMENT='互动实践表';

DROP TABLE IF EXISTS `interactive_practice_signup`;
CREATE TABLE `interactive_practice_signup` (
  `signup_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '报名id',
  `practice_id` bigint(20) NOT NULL COMMENT '互动实践id',
  `user_id` bigint(20) NOT NULL COMMENT '报名人id',
  `mobile` varchar(255) NOT NULL COMMENT '报名人手机号',
  `created_at` datetime DEFAULT NULL COMMENT '创建时间',
  `updated_at` datetime DEFAULT NULL COMMENT '更新时间',
  `delete_flag` tinyint(4) DEFAULT NULL COMMENT '1可用 0 不可用',
  PRIMARY KEY (`signup_id`),
  KEY `interactive_practice_signup_delete_flag_index` (`delete_flag`),
  KEY `interactive_practice_signup_practiceid_index` (`practice_id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8 COMMENT='互动实践报名表';

-- ----------------------------
-- 2016-06-12 孙宁海 错题本
-- ----------------------------

CREATE TABLE wrong_question (
  `wrong_question_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '错题本id',
  `class_id` bigint(20) NOT NULL COMMENT '班次id',
  `user_id` bigint(20) NOT NULL COMMENT '用户id',
  `question_id` bigint(20) NOT NULL COMMENT '题目id',
  `error_count` INT4 NOT NULL COMMENT '错误次数',
  `belong_type` VARCHAR(20) NOT NULL COMMENT '题目属于 Quiz PopQuiz',
  `belong_type_id` bigint(20) NOT NULL COMMENT '对应的id',
  `answer_data` mediumtext NOT NULL COMMENT '提交的错题答案',
  `question_data` mediumtext DEFAULT NULL COMMENT '题目数据',
  `created_at` datetime DEFAULT NULL COMMENT '创建时间',
  `updated_at` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`wrong_question_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='错题本表';

CREATE TABLE `question_correct_rate` (
  `question_correct_rate_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `class_id` bigint(20) NOT NULL COMMENT '班次id',
  `question_id` bigint(20) NOT NULL COMMENT '问题id',
  `sub_count` int(11) DEFAULT NULL COMMENT 'question提交次数',
  `correct_count` int(11) DEFAULT NULL COMMENT 'question 正确次数',
  `created_at` datetime,
  `updated_at` datetime,
  PRIMARY KEY (`question_correct_rate_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='题目正确率表';

create index class_id_user_id on wrong_question(class_id,user_id);

create index class_id_question_id on question_correct_rate(class_id,question_id);

-- ----------------------------
-- 2016-06-24 范朋飞 租户设置表
-- ----------------------------
CREATE TABLE gxb_core.tenants_set (
  `tenants_set_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `context_id` bigint(20) NOT NULL COMMENT 'id',
  `context_type` varchar(20) NOT NULL COMMENT '类型',
  `permission_type` varchar(20) DEFAULT NULL COMMENT '权限类型',
  `is_permission` tinyint(4) NOT NULL COMMENT '0:没有权限；1：有权限',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`tenants_set_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='租户设置表';

insert into gxb_core.tenants_set(context_id,context_type,permission_type,is_permission,created_at,updated_at)
values (301,"Tenant","Exam",1,now(),now()),(676,"Tenant","Exam",1,now(),now()),(552,"Tenant","Exam",1,now(),now());


-- ----------------------------
-- 2016-06-21 范朋飞 直播
-- ----------------------------

alter table gxb_core.webcast add column assistant_url varchar(255) DEFAULT NULL COMMENT '助教url' after webcast_url;
alter table gxb_core.webcast add column teacher_url varchar(255) DEFAULT NULL COMMENT '讲师url' after webcast_url;

create table gxb_core.webcast_playback (
  `webcast_playback_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `webcast_id` bigint(20) NOT NULL COMMENT '直播id',
  `third_party_id` varchar(255) NOT NULL COMMENT '第三方回放id',
	`start_at` datetime NOT NULL COMMENT '直播回放开始时间',
	`end_at` datetime NOT NULL COMMENT '直播回放结束时间',
	`play_back_url` varchar(255) DEFAULT NULL COMMENT '回放url',
	`is_marker` tinyint(4) NOT NULL COMMENT '是否使用当前回放 0：不使用；1：使用',
  `editor_id` bigint(20) DEFAULT NULL COMMENT '编辑人ID',
  `created_at` datetime DEFAULT NULL COMMENT '创建时间',
  `updated_at` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`webcast_playback_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='直播回放信息表';
-- ----------------------------
-- 2016-05-30 范朋飞 随堂测验、讨论回复语音 start
-- ----------------------------

alter table gxb_core.post add column attachment_is_exist tinyint(4) DEFAULT 0 COMMENT '是否有附件；0：没有；1：有';

CREATE TABLE gxb_core.post_attachment (
  `attachment_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `post_id` bigint(20) NOT NULL COMMENT '回复id',
  `third_party_id` varchar(255) DEFAULT NULL COMMENT '第三方资源id',
  `url` varchar(255) DEFAULT NULL COMMENT '资源url',
  `type` varchar(255) DEFAULT NULL COMMENT '所属类型',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `seconds` int(11) DEFAULT NULL COMMENT '语音长度',
  `transcoding_status` int(4) DEFAULT NULL COMMENT '1已转码 0 未转码',
  `transcoding_progress` int(4) DEFAULT NULL COMMENT '1 正在转码；2 成功；3失败',
  PRIMARY KEY (`attachment_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE gxb_core.class_pop_quiz (
  `class_pop_quiz_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `class_id` bigint(20) NOT NULL COMMENT '班次id',
  `unit_id` bigint(20) DEFAULT NULL COMMENT '章节id',
  `user_id` bigint(20) NOT NULL COMMENT '创建者id',
  `editor_id` bigint(20) NOT NULL COMMENT '修改者id',
  `tenant_id` bigint(20) DEFAULT NULL COMMENT '租户id',
  `title` varchar(255) DEFAULT NULL COMMENT '标题',
  `quiz_questions_count` int(11) DEFAULT NULL COMMENT '随堂测验试题数量',
  `time` int(11) DEFAULT NULL COMMENT '限时',
  `hide_results` tinyint(4) DEFAULT NULL COMMENT '是否显示结果 1 是 0 不是',
  `position` int(11) DEFAULT NULL COMMENT '测验排序',
  `status` varchar(255) DEFAULT NULL COMMENT '状态 10:待发布；20:进行中；30:已发布',
  `publish_at` datetime DEFAULT NULL COMMENT '发布时间',
  `delete_flag` tinyint(4) DEFAULT NULL COMMENT '1可用 0 不可用',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`class_pop_quiz_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='随堂测验表';

create index index_class_id on class_pop_quiz(class_id);
create index index_unit_id on class_pop_quiz(unit_id);
create index index_status on class_pop_quiz(status);


CREATE TABLE gxb_core.pop_quiz_submission (
  `pop_quiz_submission_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `class_id` bigint(20) NOT NULL COMMENT '班次id',
  `class_pop_quiz_id` bigint(20) NOT NULL COMMENT '随堂测验id',
  `user_id` bigint(20) DEFAULT NULL COMMENT '用户id',
  `submission_data` mediumtext COMMENT '提交详情',
  `score` double(12,2) DEFAULT NULL COMMENT '成绩',
  `started_at` datetime DEFAULT NULL COMMENT '开始做题时间',
  `created_at` datetime DEFAULT NULL COMMENT '创建时间',
  `updated_at` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`pop_quiz_submission_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE gxb_core.pop_quiz_gather (
  `pop_quiz_gather_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `class_id` bigint(20) NOT NULL COMMENT '班次id',
  `class_pop_quiz_id` bigint(20) NOT NULL COMMENT '随堂测验id',
  `question_id` bigint(20) NOT NULL COMMENT '问题id',
  `sub_count` int(11) DEFAULT NULL COMMENT '问题回答次数',
  `correct_count` int(11) DEFAULT NULL COMMENT '回答正确次数',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`pop_quiz_gather_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE gxb_core.pop_quiz_answers (
  `pop_quiz_answers_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `class_id` bigint(20) NOT NULL COMMENT '班次id',
  `class_pop_quiz_id` bigint(20) NOT NULL COMMENT '随堂测验id',
  `question_id` bigint(20) DEFAULT NULL COMMENT '问题id',
  `answer_id` bigint(20) DEFAULT NULL COMMENT '答案id',
  `sub_count` int(11) DEFAULT NULL COMMENT '选择此答案个数',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`pop_quiz_answers_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

alter table pop_quiz_submission add constraint uk_class_pop_user_id unique(class_id,class_pop_quiz_id,user_id);
alter table pop_quiz_gather add constraint uk_class_pop_question_id unique(class_id,class_pop_quiz_id,question_id);
alter table pop_quiz_answers add constraint uk_question_answer_id unique(class_id,class_pop_quiz_id,question_id,answer_id);
-- ----------------------------
-- 2016-05-30 范朋飞 随堂测验、讨论回复语音 end
-- ----------------------------

-- ----------------------------
-- 2016-06-14 孙宁海 班次添加类型 普通班次 微课
-- ----------------------------

alter table class add COLUMN type VARCHAR(50) DEFAULT 'Class' COMMENT '班次类型,MiniClass Class 微课 普通班次';

-- ----------------------------
-- 2016-06-28 张金霞 选课 添加截止时间字段
-- ----------------------------
alter table class_select_rule add column end_time datetime null comment '截止时间';

CREATE TABLE `roll_call` (
  `roll_call_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '点名id',
  `teacher_id` bigint(20) NOT NULL COMMENT '老师id',
  `distance` double NOT NULL,
  `class_id` bigint(20) NOT NULL COMMENT '班次id',
  `created_at` datetime DEFAULT NULL COMMENT '创建时间',
  `updated_at` datetime DEFAULT NULL,
  `status` tinyint(4) NOT NULL,
  `delete_flag` tinyint(4) DEFAULT NULL COMMENT '1可用 0 不可用',
  `longitude` double NOT NULL,
  `latitude` double NOT NULL,
  PRIMARY KEY (`roll_call_id`),
  KEY `roll_call_delete_flag_index` (`delete_flag`),
  KEY `roll_call_teacher_id_index` (`teacher_id`),
  KEY `roll_call_class_id_index` (`class_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='点名表';

CREATE TABLE `roll_call_sign` (
  `roll_call_sign_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '签到id',
  `roll_call_id` bigint(20) NOT NULL COMMENT '点名id',
  `student_user_id` bigint(20) NOT NULL COMMENT '用户id',
  `longitude` double DEFAULT NULL,
  `latitude` double DEFAULT NULL,
  `created_at` datetime DEFAULT NULL COMMENT '签到时间',
  `type` tinyint(4) DEFAULT NULL COMMENT '0-正常；1-补签；2-请假',
  `delete_flag` tinyint(4) DEFAULT NULL COMMENT '1可用 0 不可用',
  PRIMARY KEY (`roll_call_sign_id`),
  KEY `roll_call_delete_flag_index` (`delete_flag`),
  KEY `roll_call_user_id_index` (`student_user_id`),
  KEY `roll_call_roll_call_id_index` (`roll_call_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='点名签到表';

CREATE TABLE `shake` (
  `shake_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '摇一摇id',
  `teacher_id` bigint(20) NOT NULL COMMENT '老师id',
  `class_id` bigint(20) NOT NULL COMMENT '班次id',
  `student_user_id` bigint(20) NOT NULL COMMENT '学生id',
  `created_at` datetime DEFAULT NULL COMMENT '创建时间',
  `delete_flag` tinyint(4) DEFAULT NULL COMMENT '1可用 0 不可用',
  PRIMARY KEY (`shake_id`),
  KEY `shake_delete_flag_index` (`delete_flag`),
  KEY `shake_teacher_id_index` (`teacher_id`),
  KEY `shake_class_id_index` (`class_id`),
  KEY `shake_user_id_index` (`student_user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='摇一摇表';

-- ----------------------------
-- 2016-07-04 范朋飞 打卡 start
-- ----------------------------

CREATE TABLE gxb_core.clock (
  `clock_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '打卡id',
  `user_id` bigint(20) NOT NULL COMMENT '学生ID',
  `message` varchar(255) DEFAULT '' COMMENT '打卡信息',
  `clock_at` datetime NOT NULL COMMENT '打卡时间',
  PRIMARY KEY (`clock_id`),
  KEY `user_id_index` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='打卡信息表';


CREATE TABLE gxb_core.clock_profile (
  `clock_profile_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '打卡id',
  `user_id` bigint(20) NOT NULL COMMENT '学生id',
  `clock_count` bigint(20) NOT NULL COMMENT '打卡次数',
  `clock_at` date NOT NULL COMMENT '打卡日期',
  PRIMARY KEY (`clock_profile_id`),
  UNIQUE KEY `user_id_clock_at_unique` (`user_id`,`clock_at`),
  KEY `user_id_index` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='打卡记录信息表';

CREATE TABLE gxb_core.clock_info (
  `clock_info_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '打卡次数id',
  `user_id` bigint(20) NOT NULL COMMENT '学生id',
  `clock_count` bigint(20) NOT NULL COMMENT '打卡总次数',
  `vote_count` bigint(20) NOT NULL COMMENT '点赞次数',
  `view_count` bigint(20) NOT NULL COMMENT '查看次数',
  `last_clock_at` datetime NOT NULL COMMENT '最后一次打卡时间',
  PRIMARY KEY (`clock_info_id`),
  UNIQUE KEY `user_id_unique` (`user_id`),
  KEY `last_clock_at_index` (`last_clock_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='打卡次数信息表';

CREATE TABLE gxb_core.clock_vote_info (
  `clock_vote_info_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '打卡点赞id',
  `user_id` bigint(20) NOT NULL COMMENT '学生id',
  `vote_user_id` bigint(20) NOT NULL COMMENT '点赞学生id',
  `vote_at` datetime NOT NULL COMMENT '点赞时间',
  `status` tinyint(4) NOT NULL COMMENT '点赞状态 1：可用 0：不可用',
  PRIMARY KEY (`clock_vote_info_id`),
  UNIQUE KEY `user_id_vote_user_id_unique` (`user_id`,`vote_user_id`),
  KEY `user_id_index` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='打卡点赞信息表';

CREATE TABLE gxb_core.clock_view_info (
  `clock_view_info_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '打卡观看id',
  `user_id` bigint(20) NOT NULL COMMENT '学生id',
  `view_user_id` bigint(20) NOT NULL COMMENT '观看学生id',
  `view_at` datetime NOT NULL COMMENT '观看时间',
  PRIMARY KEY (`clock_view_info_id`),
  UNIQUE KEY `user_id_view_user_id_unique` (`user_id`,`view_user_id`),
  KEY `user_id_index` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='打卡观看信息表';

-- ----------------------------
-- 2016-07-04 范朋飞 打卡 end
-- ----------------------------

-- ----------------------------
-- 2016-06-27 孙宁海 微信建班
-- ----------------------------

alter table gxb_core.class modify COLUMN type VARCHAR(50) DEFAULT 'Class' COMMENT '班次类型,MiniClass Class Self 微课 普通班次 邀请课';


CREATE TABLE gxb_core.class_invitation_code (
  `invitation_code_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `class_id` bigint(20) NOT NULL COMMENT '班次id',
  `invitation_code` varchar(20) NOT NULL COMMENT '口令码',
  `status` TINYINT DEFAULT NULL COMMENT '是否可用 1 可用 0 不可用',
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`invitation_code_id`),
  KEY invitation_code_index(`invitation_code`),
  KEY class_index(`class_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='班次邀请码表';

-- ----------------------------
-- 2016-07-12 张金霞 云信token
-- ----------------------------
CREATE TABLE user_im_token (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) DEFAULT NULL,
  `im_token` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_id_index` (`user_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


-- ----------------------------
-- 2016-07-12 张金霞 资源发布状态表
-- ----------------------------
CREATE TABLE `resource_node_publish` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `resource_node_id` bigint(20) NOT NULL,
  `publish_status` tinyint(4) DEFAULT 0 COMMENT '1已发布 0未发布',
  PRIMARY KEY (`id`),
  UNIQUE KEY `resource_node_id_index` (`resource_node_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
-- -----------------------------
-- 2016-07-12 范朋飞 云批改 start
-- -----------------------------

CREATE TABLE gxb_core.cloud_correct (
  `cloud_correct_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `context_id` bigint(20) NOT NULL COMMENT 'id',
  `context_type` varchar(20) NOT NULL COMMENT '类型',
  `user_id` bigint(20) NOT NULL COMMENT '学生id',
  `url` varchar(255) NOT NULL COMMENT '附件url',
  `body` varchar(255) DEFAULT '' COMMENT '备注',
  `status` tinyint(4) NOT NULL COMMENT '批改状态 10：未批改；20：已批改',
  `created_at` datetime NOT NULL COMMENT '创建时间',
  `updated_at` datetime NOT NULL COMMENT '更新时间',
  `delete_flag` tinyint(4) NOT NULL COMMENT '1可用 0 不可用',
  PRIMARY KEY (`cloud_correct_id`),
  KEY `context_index` (`context_id`, `context_type`),
  KEY `index_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生云批改信息表';

CREATE TABLE gxb_core.cloud_teach_correct (
  `teach_correct_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `cloud_correct_id` bigint(20) NOT NULL COMMENT '批改id',
  `grade_id` bigint(20) NOT NULL COMMENT '批改人id',
  `edit_id` bigint(20) NOT NULL COMMENT '更新人id',
  `abscissa` decimal(12,10) NOT NULL COMMENT 'x轴',
  `ordinate` decimal(12,10) NOT NULL COMMENT 'y轴',
  `type` tinyint(4) NOT NULL COMMENT '批改类型 10：对号；20：X号；30：语音',
  `position` int(11) NOT NULL COMMENT '排序',
  `media_id` varchar(255) DEFAULT '' NULL COMMENT '语音id',
  `url` varchar(255) DEFAULT '' COMMENT '语音url',
  `seconds` int(11) DEFAULT 0 COMMENT '语音长度',
  `created_at` datetime NOT NULL COMMENT '创建时间',
  `updated_at` datetime NOT NULL COMMENT '更新时间',
  `delete_flag` tinyint(4) NOT NULL COMMENT '1可用 0 不可用',
  PRIMARY KEY (`teach_correct_id`),
  KEY `cloud_correct_id_index` (`cloud_correct_id`),
  KEY `position_index` (`position`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='教师批改信息表';

-- ----------------------------
-- 2016-07-12 范朋飞 云批改 end
-- ----------------------------





-- ----------------------------
-- 2016-06-16 孙宁海 调查问卷&投票
-- ----------------------------

CREATE TABLE `questionnaire` (
  `questionnaire_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '调查问卷id',
  `context_type` varchar(50) NOT NULL COMMENT '所属类型 Class Tenant',
  `context_id` bigint(20) NOT NULL COMMENT 'type id',
  `title` varchar(255) NOT NULL COMMENT '标题',
  `description` varchar(2000)  COMMENT '描述',
  `start_at` datetime NOT NULL COMMENT '开始时间',
  `end_at` datetime NOT NULL COMMENT '结束时间',
  `user_Id` bigint(20) COMMENT '创建人',
  `delete_flag` tinyint COMMENT '是否可用 1 未删除 0 删除',
  `editor_Id` bigint(20) COMMENT '修改人',
  `created_at` datetime DEFAULT NULL COMMENT '创建时间',
  `updated_at` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`questionnaire_id`),
  KEY `context_index` (`context_type`,`context_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='调查问卷表';


CREATE TABLE `vote` (
  `vote_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '投票id',
  `context_type` varchar(50) NOT NULL COMMENT '所属类型 Class Tenant',
  `context_id` bigint(20) NOT NULL COMMENT 'type id',
  `title` varchar(255) NOT NULL COMMENT '标题',
  `description` varchar(2000)  COMMENT '描述',
  `start_at` datetime NOT NULL COMMENT '开始时间',
  `end_at` datetime NOT NULL COMMENT '结束时间',
  `user_Id` bigint(20) COMMENT '创建人',
  `editor_Id` bigint(20) COMMENT '修改人',
  `delete_flag` tinyint COMMENT '是否可用 1 未删除 0 删除',
  `created_at` datetime DEFAULT NULL COMMENT '创建时间',
  `updated_at` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`vote_id`),
  KEY `context_index` (`context_type`,`context_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='投票表';


CREATE TABLE `open_question` (
  `open_question_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL,
  `editor_id` bigint(20) NOT NULL,
  `question_name` varchar(2000) DEFAULT NULL COMMENT '题干',
  `question_type` varchar(255) DEFAULT NULL COMMENT '题目类型 单选 多选 填空 问答，multiple_choice、multiple_answers、fill_in_blank、short_answer',
  `position` int(11) DEFAULT NULL COMMENT '排序坐标',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`open_question_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='问卷投票问题表';

CREATE TABLE `open_question_option` (
  `open_question_option_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `open_question_id` bigint(20) NOT NULL,
  `content` mediumtext COMMENT '选项内容',
  `position` int(11) DEFAULT NULL COMMENT '排序坐标',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`open_question_option_id`),
  KEY `index_question_id` (`open_question_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 comment '问卷投票问题选项表';


CREATE TABLE `open_question_relate` (
  `questions_relate_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL COMMENT '创建者id',
  `editor_id` bigint(20) NOT NULL COMMENT '修改者id',
  `context_id` bigint(20) NOT NULL COMMENT '',
  `context_type` varchar(255) DEFAULT NULL COMMENT '所属类型,vote questionnaire',
  `open_question_id` bigint(20) NOT NULL COMMENT 'open_question表的id',
  `position` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`questions_relate_id`),
  KEY `index_context_type_context_id` (`context_type`,`context_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 comment '调查问卷 投票 与题目的对应关系表';

CREATE TABLE `questionnaire_submission` (
  `questionnaire_submission_id` BIGINT NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL COMMENT '提交人id',
  `questionnaire_id` bigint(20) NOT NULL COMMENT '问卷id' ,
  `submission_data` mediumtext COMMENT '提交数据',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`questionnaire_submission_id`),
  KEY `index_questionnaire_id_user_id` (`questionnaire_id`,`user_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 comment '调查问卷提交表';

CREATE TABLE `vote_submission` (
  `vote_submission_id` BIGINT NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL COMMENT '提交人id',
  `vote_id` bigint(20) NOT NULL COMMENT '投票id' ,
  `submission_data` mediumtext COMMENT '提交数据',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`vote_submission_id`),
  KEY `index_vote_id_user_id` (`vote_id`,`user_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 comment '投票提交表';

CREATE TABLE `open_question_option_statistics` (
  `open_question_option_id` bigint(20) NOT NULL,
  `count` INT4 COMMENT '被选择数量',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`open_question_option_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 comment '问卷投票问题选项统计表';

CREATE TABLE `questionnaire_short_answer_statistics` (
  `statistics_id` BIGINT NOT NULL AUTO_INCREMENT,
  `questionnaire_id` INT4 NOT NULL COMMENT '调查问卷id',
  `open_question_id` INT4 NOT NULL COMMENT '题目id',
  `submission_data` mediumtext COMMENT '提交数据',
  `user_id` bigint(20) NOT NULL COMMENT '提交人id',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`statistics_id`),
  KEY `index_questionnaire_id_question_id` (`questionnaire_id`,`open_question_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 comment '调查问卷问答统计表';



alter table gxb_core.exam add column is_correct tinyint(5) default 1 comment '是否需要批改 0 不需要;1 需要';




-- -------------------------------------
-- 2016-08-14 范朋飞 文件路径数据更新 start
-- -------------------------------------

--课程封面、课件、作业附件
update gxb_core.course set course_cover=concat('/uploads/course_image/link/', course_cover) where length(course_cover) > 0;
update gxb_core.course set intro_video=concat('/00000/', intro_video) where length(intro_video) > 0;
update gxb_core.course set intro_video_cover=concat('/00000/', intro_video_cover) where length(intro_video_cover) > 0;
update gxb_core.course set intro_video_srt=concat('/00000/', intro_video_srt) where length(intro_video_srt) > 0;
update gxb_core.course_asset set link=concat('/lcms/attachment/link/', link) where length(link) > 0;;

--班次封面、课件、作业附件
update gxb_core.class_info set class_cover=concat('/uploads/course_image/link/', class_cover) where length(class_cover) > 0;
update gxb_core.class_info set promotional_video=concat('/00000/', promotional_video) where length(promotional_video) > 0;
update gxb_core.class_info set promotional_video_srt=concat('/00000/', promotional_video_srt) where length(promotional_video_srt) > 0;
update gxb_core.class_info set promotional_video_cover=concat('/00000/', promotional_video_cover) where length(promotional_video_cover) > 0 and promotional_video_cover not like 'http:%';
update gxb_core.class_info set promotional_video_cover=replace(promotional_video_cover, 'http://gxb-file.cdn.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where length(promotional_video_cover) > 0;
update gxb_core.class_asset set link=concat('/lcms/attachment/link/', link) where viewable_type='Assignment' and type='AssignmentAttachment' and length(link) > 0;
update gxb_core.document set file_path=concat('/lcms/attachment/link/', file_path) where length(file_path) > 0;

--视频封面、字幕
update gxb_core.video set cover_path=concat('/lcms/video/cover/', cover_path) where length(cover_path) > 0;
update gxb_core.video set srt_path=concat('/lcms/video/srt/', srt_path) where length(srt_path) > 0;
update gxb_core.video set file_path=concat('/lcms/video/file/', file_path) where length(file_path) > 0;

--学生作业提交附件
update gxb_core.asset set link=replace(link, 'http://gxb-file.cdn.bj.xs3cnc.com/lcms/attachment/link/', '') where viewable_type='Submission' and type='AssignmentAttachment' and  length(link) > 0 and link not like '%qini%' and link like 'http:%';
update gxb_core.asset set link=concat('/lcms/attachment/link/', link) where viewable_type='Submission' and type='AssignmentAttachment' and length(link) > 0 and link not like '%http:%';

--授课老师头像
update gxb_core.instructor set avatar=concat('/uploads/instructor_image/link/', avatar) where length(avatar) > 0 and  avatar not like 'http:%';
update gxb_core.instructor set avatar=replace(avatar, 'http://gxb-image.cdn.bj.xs3cnc.com', 'http://gxb-image.oss-cn-beijing.aliyuncs.com') where length(avatar) > 0 and avatar like '%http:%';

--替换富文本中的图片链接
update gxb_core.announcement set body=replace(body, 'http://s3.bj.xs3cnc.com/gxb-file', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where body like '%http:%';
update gxb_core.announcement set body=replace(body, 'http://gxb-file.cdn.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where body like '%http:%';
update gxb_core.announcement set body=replace(body, 'http://gxb-file.s3.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where body like '%http:%';
update gxb_core.class_assignment set body=replace(body, 'http://s3.bj.xs3cnc.com/gxb-file', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where body like '%http:%';
update gxb_core.class_assignment set body=replace(body, 'http://gxb-file.cdn.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where body like '%http:%';
update gxb_core.class_assignment set body=replace(body, 'http://gxb-file.s3.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where body like '%http:%';
update gxb_core.class_assignment set grading_standard=replace(grading_standard, 'http://s3.bj.xs3cnc.com/gxb-file', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where grading_standard like '%http:%';
update gxb_core.class_assignment set grading_standard=replace(grading_standard, 'http://gxb-file.cdn.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where grading_standard like '%http:%';
update gxb_core.class_assignment set grading_standard=replace(grading_standard, 'http://gxb-file.s3.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where grading_standard like '%http:%';
update gxb_core.class_courseware set description=replace(description, 'http://s3.bj.xs3cnc.com/gxb-file', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where description like '%http:%';
update gxb_core.class_courseware set description=replace(description, 'http://gxb-file.cdn.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where description like '%http:%';
update gxb_core.class_courseware set description=replace(description, 'http://gxb-file.s3.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where description like '%http:%';
update gxb_core.class_info set description=replace(description, 'http://s3.bj.xs3cnc.com/gxb-file', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where description like '%http:%';
update gxb_core.class_info set description=replace(description, 'http://gxb-file.cdn.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where description like '%http:%';
update gxb_core.class_info set description=replace(description, 'http://gxb-file.s3.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where description like '%http:%';
update gxb_core.class_info set teach_plan=replace(teach_plan, 'http://s3.bj.xs3cnc.com/gxb-file', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where teach_plan like '%http:%';
update gxb_core.class_info set teach_plan=replace(teach_plan, 'http://gxb-file.cdn.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where teach_plan like '%http:%';
update gxb_core.class_info set teach_plan=replace(teach_plan, 'http://gxb-file.s3.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where teach_plan like '%http:%';
update gxb_core.class_note set body=replace(body, 'http://s3.bj.xs3cnc.com/gxb-file', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where body like '%http:%';
update gxb_core.class_note set body=replace(body, 'http://gxb-file.cdn.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where body like '%http:%';
update gxb_core.class_note set body=replace(body, 'http://gxb-file.s3.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where body like '%http:%';
update gxb_core.class_page set body=replace(body, 'http://s3.bj.xs3cnc.com/gxb-file', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where body like '%http:%';
update gxb_core.class_page set body=replace(body, 'http://gxb-file.cdn.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where body like '%http:%';
update gxb_core.class_page set body=replace(body, 'http://gxb-file.s3.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where body like '%http:%';

update gxb_core.class_quiz set description=replace(description, 'http://s3.bj.xs3cnc.com/gxb-file', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where description like '%http:%';
update gxb_core.class_quiz set description=replace(description, 'http://gxb-file.cdn.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where description like '%http:%';
update gxb_core.class_quiz set description=replace(description, 'http://gxb-file.s3.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where description like '%http:%';

update gxb_core.class_set set set_info=replace(set_info, 'http://s3.bj.xs3cnc.com/gxb-file', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where set_info like '%http:%';
update gxb_core.class_set set set_info=replace(set_info, 'http://gxb-file.cdn.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where set_info like '%http:%';
update gxb_core.class_set set set_info=replace(set_info, 'http://gxb-file.s3.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where set_info like '%http:%';

update gxb_core.class_topic set body=replace(body, 'http://s3.bj.xs3cnc.com/gxb-file', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where body like '%http:%';
update gxb_core.class_topic set body=replace(body, 'http://gxb-file.cdn.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where body like '%http:%';
update gxb_core.class_topic set body=replace(body, 'http://gxb-file.s3.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where body like '%http:%';


update gxb_core.course_assignment set body=replace(body, 'http://s3.bj.xs3cnc.com/gxb-file', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where body like '%http:%';
update gxb_core.course_assignment set body=replace(body, 'http://gxb-file.cdn.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where body like '%http:%';
update gxb_core.course_assignment set body=replace(body, 'http://gxb-file.s3.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where body like '%http:%';

update gxb_core.course_assignment set grading_standard=replace(grading_standard, 'http://s3.bj.xs3cnc.com/gxb-file', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where grading_standard like '%http:%';
update gxb_core.course_assignment set grading_standard=replace(grading_standard, 'http://gxb-file.cdn.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where grading_standard like '%http:%';
update gxb_core.course_assignment set grading_standard=replace(grading_standard, 'http://gxb-file.s3.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where grading_standard like '%http:%';

update gxb_core.course_courseware set description=replace(description, 'http://s3.bj.xs3cnc.com/gxb-file', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where description like '%http:%';
update gxb_core.course_courseware set description=replace(description, 'http://gxb-file.cdn.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where description like '%http:%';
update gxb_core.course_courseware set description=replace(description, 'http://gxb-file.s3.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where description like '%http:%';
update gxb_core.course_info set description=replace(description, 'http://s3.bj.xs3cnc.com/gxb-file', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where description like '%http:%';
update gxb_core.course_info set description=replace(description, 'http://gxb-file.cdn.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where description like '%http:%';
update gxb_core.course_info set description=replace(description, 'http://gxb-file.s3.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where description like '%http:%';

update gxb_core.course_page set body=replace(body, 'http://s3.bj.xs3cnc.com/gxb-file', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where body like '%http:%';
update gxb_core.course_page set body=replace(body, 'http://gxb-file.cdn.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where body like '%http:%';
update gxb_core.course_page set body=replace(body, 'http://gxb-file.s3.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where body like '%http:%';

update gxb_core.course_quiz set description=replace(description, 'http://s3.bj.xs3cnc.com/gxb-file', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where description like '%http:%';
update gxb_core.course_quiz set description=replace(description, 'http://gxb-file.cdn.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where description like '%http:%';
update gxb_core.course_quiz set description=replace(description, 'http://gxb-file.s3.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where description like '%http:%';

update gxb_core.course_topic set body=replace(body, 'http://s3.bj.xs3cnc.com/gxb-file', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where body like '%http:%';
update gxb_core.course_topic set body=replace(body, 'http://gxb-file.cdn.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where body like '%http:%';
update gxb_core.course_topic set body=replace(body, 'http://gxb-file.s3.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where body like '%http:%';

update gxb_core.forum set description=replace(description, 'http://s3.bj.xs3cnc.com/gxb-file', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where description like '%http:%';
update gxb_core.forum set description=replace(description, 'http://gxb-file.cdn.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where description like '%http:%';
update gxb_core.forum set description=replace(description, 'http://gxb-file.s3.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where description like '%http:%';

update gxb_core.help_center_body set body=replace(body, 'http://s3.bj.xs3cnc.com/gxb-file', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where body like '%http:%';
update gxb_core.help_center_body set body=replace(body, 'http://gxb-file.cdn.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where body like '%http:%';
update gxb_core.help_center_body set body=replace(body, 'http://gxb-file.s3.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where body like '%http:%';

update gxb_core.instructor set avatar=replace(avatar, 'http://gxb-image.cdn.bj.xs3cnc.com', 'http://gxb-image.oss-cn-beijing.aliyuncs.com') where avatar like '%http:%';
update gxb_core.instructor set avatar=replace(avatar, 'http://s3.bj.xs3cnc.com/gxb-file', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where avatar like '%http:%';
update gxb_core.instructor set avatar=replace(avatar, 'http://gxb-file.s3.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where avatar like '%http:%';

update gxb_core.news set body=replace(body, 'http://gxb-file.cdn.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where body like '%http:%';
update gxb_core.news set body=replace(body, 'http://s3.bj.xs3cnc.com/gxb-file', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where body like '%http:%';
update gxb_core.news set body=replace(body, 'http://gxb-file.s3.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where body like '%http:%';

update gxb_core.post set message=replace(message, 'http://gxb-file.cdn.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where message like '%http:%';
update gxb_core.post set message=replace(message, 'http://s3.bj.xs3cnc.com/gxb-file', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where message like '%http:%';
update gxb_core.post set message=replace(message, 'http://gxb-file.s3.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where message like '%http:%';

update gxb_core.question set question_name=replace(question_name, 'http://gxb-file.cdn.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where question_name like '%http:%';
update gxb_core.question set question_name=replace(question_name, 'http://s3.bj.xs3cnc.com/gxb-file', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where question_name like '%http:%';
update gxb_core.question set question_name=replace(question_name, 'http://gxb-file.s3.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where question_name like '%http:%';
update gxb_core.question set comment=replace(comment, 'http://gxb-file.cdn.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where comment like '%http:%';
update gxb_core.question set comment=replace(comment, 'http://s3.bj.xs3cnc.com/gxb-file', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where comment like '%http:%';
update gxb_core.question set comment=replace(comment, 'http://gxb-file.s3.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where comment like '%http:%';

update gxb_core.question_node set name=replace(name, 'http://gxb-file.cdn.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where name like '%http:%';
update gxb_core.question_node set name=replace(name, 'http://s3.bj.xs3cnc.com/gxb-file', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where name like '%http:%';
update gxb_core.question_node set name=replace(name, 'http://gxb-file.s3.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where name like '%http:%';

update gxb_core.question_node_0720 set name=replace(name, 'http://gxb-file.cdn.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where name like '%http:%';
update gxb_core.question_node_0720 set name=replace(name, 'http://s3.bj.xs3cnc.com/gxb-file', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where name like '%http:%';
update gxb_core.question_node_0720 set name=replace(name, 'http://gxb-file.s3.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where name like '%http:%';

update gxb_core.question_node_new set name=replace(name, 'http://gxb-file.cdn.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where name like '%http:%';
update gxb_core.question_node_new set name=replace(name, 'http://s3.bj.xs3cnc.com/gxb-file', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where name like '%http:%';
update gxb_core.question_node_new set name=replace(name, 'http://gxb-file.s3.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where name like '%http:%';

update gxb_core.question_option set content=replace(content, 'http://gxb-file.cdn.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where content like '%http:%';
update gxb_core.question_option set content=replace(content, 'http://s3.bj.xs3cnc.com/gxb-file', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where content like '%http:%';
update gxb_core.question_option set content=replace(content, 'http://gxb-file.s3.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where content like '%http:%';

update gxb_core.question_option set correct=replace(correct, 'http://gxb-file.cdn.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where correct like '%http:%';
update gxb_core.question_option set correct=replace(correct, 'http://s3.bj.xs3cnc.com/gxb-file', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where correct like '%http:%';
update gxb_core.question_option set correct=replace(correct, 'http://gxb-file.s3.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where correct like '%http:%';

update gxb_core.quiz_submission set submission_data=replace(submission_data, 'http://gxb-file.cdn.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where submission_data like '%http:%';
update gxb_core.quiz_submission set submission_data=replace(submission_data, 'http://s3.bj.xs3cnc.com/gxb-file', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where submission_data like '%http:%';
update gxb_core.quiz_submission set submission_data=replace(submission_data, 'http://gxb-file.s3.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where submission_data like '%http:%';
update gxb_core.quiz_submission set quiz_data=replace(quiz_data, 'http://gxb-file.cdn.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where quiz_data like '%http:%';
update gxb_core.quiz_submission set quiz_data=replace(quiz_data, 'http://s3.bj.xs3cnc.com/gxb-file', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where quiz_data like '%http:%';
update gxb_core.quiz_submission set quiz_data=replace(quiz_data, 'http://gxb-file.s3.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where quiz_data like '%http:%';

update gxb_core.submission set body=replace(body, 'http://gxb-file.cdn.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where body like '%http:%';
update gxb_core.submission set body=replace(body, 'http://s3.bj.xs3cnc.com/gxb-file', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where body like '%http:%';
update gxb_core.submission set body=replace(body, 'http://gxb-file.s3.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where body like '%http:%';

update gxb_core.tenants set related_intro=replace(related_intro, 'http://gxb-file.s3.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where related_intro like '%http:%';
update gxb_core.tenants set related_intro=replace(related_intro, 'http://s3.bj.xs3cnc.com/gxb-file', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where related_intro like '%http:%';
update gxb_core.tenants set related_intro=replace(related_intro, 'http://gxb-file.cdn.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where related_intro like '%http:%';

update gxb_core.webcast set introduce=replace(introduce, 'http://gxb-file.s3.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where introduce like '%http:%';
update gxb_core.webcast set introduce=replace(introduce, 'http://s3.bj.xs3cnc.com/gxb-file', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where introduce like '%http:%';
update gxb_core.webcast set introduce=replace(introduce, 'http://gxb-file.cdn.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where introduce like '%http:%';

update gxb_core.webcast_material set description=replace(description, 'http://gxb-file.s3.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where description like '%http:%';
update gxb_core.webcast_material set description=replace(description, 'http://s3.bj.xs3cnc.com/gxb-file', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where description like '%http:%';
update gxb_core.webcast_material set description=replace(description, 'http://gxb-file.cdn.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where description like '%http:%';

update gxb_core.wrong_question set answer_data=replace(answer_data, 'http://gxb-file.s3.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where answer_data like '%http:%';
update gxb_core.wrong_question set answer_data=replace(answer_data, 'http://s3.bj.xs3cnc.com/gxb-file', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where answer_data like '%http:%';
update gxb_core.wrong_question set answer_data=replace(answer_data, 'http://gxb-file.cdn.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where answer_data like '%http:%';

update gxb_core.wrong_question set question_data=replace(question_data, 'http://gxb-file.s3.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where question_data like '%http:%';
update gxb_core.wrong_question set question_data=replace(question_data, 'http://s3.bj.xs3cnc.com/gxb-file', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where question_data like '%http:%';
update gxb_core.wrong_question set question_data=replace(question_data, 'http://gxb-file.cdn.bj.xs3cnc.com', 'http://gxb-file.oss-cn-beijing.aliyuncs.com') where question_data like '%http:%';

CREATE TABLE gxb_core.system_config (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `config_type` varchar(100) NOT NULL DEFAULT '' COMMENT '类型',
  `config_value` varchar(100) NOT NULL DEFAULT '' COMMENT '值',
  `status` tinyint(4) DEFAULT '1' COMMENT '1可用 0 不可用',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='系统配置表';

INSERT INTO gxb_core.system_config (`config_type`, `config_value`, `status`)
VALUES
	('video', 'http://gxbvideo-gs.gaoxiaobang.com', 1),
	('image', 'http://gxbimage-gs.gaoxiaobang.com', 1),
	('xcsp', 'http://gxbxcsp-gs.gaoxiaobang.com', 1),
	('file', 'http://gxbfile-gs.gaoxiaobang.com', 1),
	('course', 'http://gxbcourse-gs.gaoxiaobang.com', 1);

-- -------------------------------------
-- 2016-08-14 范朋飞 文件路径数据更新 end
-- -------------------------------------




-- -------------------------------------
-- 新版需执行sql
-- -------------------------------------


-- ----------------------------------
-- 2016-07-14 孙宁海 教学足迹
-- ----------------------------

CREATE TABLE gxb_core.teacher_calendar (
  `teacher_calendar_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `teacher_id` bigint(20) NOT NULL COMMENT '老师id',
  `class_id` bigint(20) NOT NULL COMMENT '班次id',
  `content_type` varchar(30) NOT NULL COMMENT '类型 Quiz,Topic,Exam,Announcement,ROllCall,Activity,Vote,Questionnaire,WebCast,Video,Document,',
  `content_id` bigint(20) NOT NULL COMMENT '学生id',
  `created_at` DATE NOT NULL COMMENT '创建日期',
  `delete_flag` tinyint(4) NOT NULL COMMENT '1可用 0 不可用',
  PRIMARY KEY (`teacher_calendar_id`),
  KEY `class_id_teacher_id_index` (`class_id`,`teacher_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='教学日历表';
ALTER TABLE class DEFAULT CHARACTER SET utf8mb4;


-- ----------------------------------
-- 2016-08-10 孙宁海 建课
-- ----------------------------

CREATE TABLE gxb_core.picture_dictionary (
  `dictionary_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '菜单id',
  `type` VARCHAR(20) NOT NULL COMMENT '类型,ClassCover',
  `url` VARCHAR(200) NOT NULL COMMENT '值',
  `description` VARCHAR(200)  COMMENT '描述',
  `status` tinyint(20) NOT NULL COMMENT '状态 1 可用 0 不可用',
  `created_at` datetime  COMMENT '创建时间',
  `updated_at` datetime  COMMENT '更新时间',
  PRIMARY KEY (`dictionary_id`),
  KEY `type_index` (`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='图片字典表';

-- -------------------------------------
-- 2016-08-23 孙宁海  学习记录归档 start
-- -------------------------------------

alter table gxb_core.schedule_history add COLUMN document_at datetime  COMMENT '归档时间';

alter table gxb_core.class add COLUMN is_move tinyint default 0  COMMENT '0 未移动数据 1 已移动数据';

alter table gxb_core.class add COLUMN least_move_at datetime  COMMENT '最早移动时间';


update gxb_core.class set is_move=1 where class_id in(select DISTINCT(class_id) from schedule_history);

-- -------------------------------------
-- 2016-08-23 孙宁海  学习记录归档 end
-- -------------------------------------




-- -------------------------------------
-- 2016-08-31 孙宁海  成绩添加线下成绩 start
-- -------------------------------------

alter table gxb_core.transcript add COLUMN off_line_score decimal(5,2)  COMMENT '线下成绩';

-- -------------------------------------
-- 2016-08-31 孙宁海  成绩添加线下成绩 end
-- -------------------------------------


-- -------------------------------------
-- 2016-09-5 楼文政  一键督学 start
-- -------------------------------------

CREATE TABLE gxb_core.class_push_message (
  `message_id` int(20) NOT NULL AUTO_INCREMENT,
  `class_id` int(20) NOT NULL,
  `user_id` int(20) NOT NULL COMMENT '发布消息的教师id',
  `body` mediumtext COMMENT '督学内容',
  `type` varchar(255) DEFAULT NULL COMMENT '发送消息的类型 督学:Urge',
  `editor_id` int(20) DEFAULT NULL,
  `delete_flag` tinyint(4) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`message_id`),
  KEY `classIdAndUserId` (`class_id`,`user_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '教师发送消息表';


CREATE TABLE gxb_core.user_message_center (
  `user_message_id` int(20) NOT NULL AUTO_INCREMENT,
  `user_id` int(20) NOT NULL,
  `class_id` int(20) NOT NULL,
  `message_id` int(20) NOT NULL,
  `is_read` tinyint(4) DEFAULT NULL COMMENT '是否已读 1 是 0 否',
  `delete_flag` tinyint(4) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`user_message_id`),
  UNIQUE KEY `class_message_user_unque` (`user_id`,`class_id`,`message_id`) USING BTREE,
  KEY `userId` (`user_id`) USING BTREE,
  KEY `messageId` (`message_id`) USING BTREE,
  KEY `class_id` (`class_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '学生接收消息表';

-- -------------------------------------
-- 2016-09-5 楼文政  一键督学 end
-- -------------------------------------


-- -------------------------------------
-- 2016-09-09 孙宁海  考试系统 end
-- -------------------------------------
CREATE TABLE gxb_core.paper (
  `paper_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '试卷id',
  `paper_name` varchar(100) NOT NULL COMMENT '试卷名称',
  `class_id` bigint(20) NOT NULL COMMENT '班次id',
  `description` varchar(500) DEFAULT NULL COMMENT '描述',
  `user_Id` bigint(20) DEFAULT NULL COMMENT '创建人',
  `editor_Id` bigint(20) DEFAULT NULL COMMENT '修改人',
  `delete_flag` tinyint(4) DEFAULT NULL COMMENT '是否可用 1 未删除 0 删除',
  `created_at` datetime DEFAULT NULL COMMENT '创建时间',
  `updated_at` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`paper_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='试卷表';

CREATE TABLE gxb_core.exam_paper_relate (
  `exam_paper_relate_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '关联id',
  `exam_id` bigint(20) NOT NULL COMMENT '考试id',
  `paper_id` bigint(20) NOT NULL COMMENT '试卷id',
  `description` varchar(500) DEFAULT NULL COMMENT '描述',
  `user_Id` bigint(20) DEFAULT NULL COMMENT '创建人',
  `editor_Id` bigint(20) DEFAULT NULL COMMENT '修改人',
  `delete_flag` tinyint(4) DEFAULT NULL COMMENT '是否可用 1 未删除 0 删除',
  `created_at` datetime DEFAULT NULL COMMENT '创建时间',
  `updated_at` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`exam_paper_relate_id`),
  KEY `exam_index` (`exam_id`),
  KEY `paper_index` (`paper_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='考试试卷关联表';

CREATE TABLE gxb_core.paper_question_type_relate (
  `paper_question_type_relate_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '关联id',
  `paper_id` bigint(20) NOT NULL COMMENT '试卷id',
  `question_type` varchar(20) NOT NULL COMMENT '试题类型',
  `position` int(11) DEFAULT NULL COMMENT '顺序',
  `description` varchar(200) DEFAULT NULL COMMENT '描述',
  `user_Id` bigint(20) DEFAULT NULL COMMENT '创建人',
  `editor_Id` bigint(20) DEFAULT NULL COMMENT '修改人',
  `delete_flag` tinyint(4) DEFAULT NULL COMMENT '是否可用 1 未删除 0 删除',
  `created_at` datetime DEFAULT NULL COMMENT '创建时间',
  `updated_at` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`paper_question_type_relate_id`),
  KEY `paper_index` (`paper_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='试卷题型关联表';


CREATE TABLE gxb_core.question_type_question_relate (
  `question_type_question_relate_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '关联id',
  `question_type_id` bigint(20) NOT NULL COMMENT '试卷试题类型id,表paper_question_type_relate主键',
  `question_id` bigint(20) DEFAULT NULL COMMENT '试题id',
  `position` int(11) DEFAULT NULL COMMENT '顺序',
  `score` decimal(5,2)  COMMENT '分数',
  `user_Id` bigint(20) DEFAULT NULL COMMENT '创建人',
  `editor_Id` bigint(20) DEFAULT NULL COMMENT '修改人',
  `delete_flag` tinyint(4) DEFAULT NULL COMMENT '是否可用 1 未删除 0 删除',
  `created_at` datetime DEFAULT NULL COMMENT '创建时间',
  `updated_at` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`question_type_question_relate_id`),
  KEY `question_type_id_index` (`question_type_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='题型试题对应表';


CREATE TABLE gxb_core.exam_submission (
  `exam_submission_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '关联id',
  `class_id` bigint(20) NOT NULL COMMENT '班次ID',
  `exam_id` bigint(20) NOT NULL COMMENT '考试ID',
  `sub_data` mediumtext COMMENT '提交数据',
  `user_Id` bigint(20) NOT NULL COMMENT '提交人',
  `version` int(11) DEFAULT NULL COMMENT '版本',
  `delete_flag` tinyint(4) DEFAULT NULL COMMENT '是否可用 1 未删除 0 删除',
  `created_at` datetime DEFAULT NULL COMMENT '创建时间',
  `updated_at` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`exam_submission_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='考试信息提交表';

CREATE TABLE gxb_core.exam_log (
  `class_id` bigint(20) DEFAULT NULL COMMENT '班次id',
  `exam_id` bigint(20) DEFAULT NULL COMMENT '考试id',
  `user_id` bigint(20) DEFAULT NULL COMMENT '用户id',
  `error_message` varchar(2000) DEFAULT NULL COMMENT '失败原因',
  `created_at` datetime DEFAULT NULL COMMENT '创建时间',
  `updated_at` datetime DEFAULT NULL COMMENT '更新时间',
  KEY `exam_log_class_id_exam_id_user_id` (`class_id`,`exam_id`,`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


alter table gxb_core.exam add COLUMN is_publish tinyint(4) default 1  COMMENT '是否发布 1是 0否';

alter table gxb_core.exam_record add COLUMN delete_flag tinyint(4) default 1  COMMENT '1可用 0 不可用';
alter table gxb_core.exam_score add COLUMN delete_flag tinyint(4) default 1  COMMENT '1可用 0 不可用';

alter table gxb_core.class_courseware add column courseware_count int(5) default 0 comment '课件个数' after description;
update gxb_core.class_courseware cc, (select cd.class_id,cd.context_id,count(1) cou from gxb_core.class_document_relate cd
where cd.context_type='Courseware' and cd.delete_flag=1 group by cd.context_id) t
	set cc.courseware_count=t.cou where cc.class_id=t.class_id and cc.courseware_id=t.context_id;


-- -------------------------------------
-- 2016-09-09 孙宁海  考试系统 end
-- -------------------------------------

-- ----------------------------------
-- 2016-07-07 范朋飞 班次菜单设置 start
-- ----------------------------------

CREATE TABLE gxb_core.class_menu_set (
  `menu_set_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '菜单id',
  `class_id` bigint(20) NOT NULL COMMENT '班次id',
  `type` varchar(20) NOT NULL DEFAULT '' COMMENT '菜单类型',
  `status` tinyint(20) NOT NULL COMMENT '状态 1：存在；0：不存在',
  `created_at` datetime NOT NULL COMMENT '创建时间',
  `updated_at` datetime NOT NULL COMMENT '更新时间',
  PRIMARY KEY (`menu_set_id`),
  KEY `context_id_index` (`class_id`),
  KEY `type_index` (`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='菜单配置表';

-- ----------------------------------
-- 2016-07-07 范朋飞 班次菜单设置 end
-- ----------------------------------

-- ----------------------------------
-- 2016-10-10 楼文政 考试记录表添加唯一索引 start
-- ----------------------------------
ALTER TABLE gxb_core.exam_record
ADD CONSTRAINT uk_exam_id_class_id_user_id_delete_flag  UNIQUE (exam_id,class_id,user_id,delete_flag);
-- ----------------------------------
-- 2016-10-10 楼文政 考试记录表添加唯一索引 end
-- ----------------------------------

-- -------------------------------------------
-- 2016-10-10 范朋飞 默认班次封面数据初始化 start
-- -------------------------------------------

INSERT INTO gxb_core.picture_dictionary (`type`, `url`, `description`, `status`, `created_at`, `updated_at`)
VALUES
	('ClassCover', '/uploads/course_image/link/605a4d0be5014aa9a299f4d2b7a2bc2c.jpg', NULL, 1, now(), now()),
	('ClassCover', '/uploads/course_image/link/e97db867a4db406eb8c759f9a2e8b781.jpg', NULL, 1, now(), now()),
	('ClassCover', '/uploads/course_image/link/1e0a8a9ce0854e038844ec144f1be445.jpg', NULL, 1, now(), now()),
	('ClassCover', '/uploads/course_image/link/6f94cc5d44cc489bb1a3ee413dd433d7.jpg', NULL, 1, now(), now()),
	('ClassCover', '/uploads/course_image/link/30cb4925f5e240cb8c36d2a6aa345697.jpg', NULL, 1, now(), now()),
	('ClassCover', '/uploads/course_image/link/50d63776d49b4631ab10a597aa961375.jpg', NULL, 1, now(), now()),
	('ClassCover', '/uploads/course_image/link/1cd57d39d7a940a2b6d48ee23a37e0af.jpg', NULL, 1, now(), now()),
	('ClassCover', '/uploads/course_image/link/1d69593e419949b798915a5fc09cf349.jpg', NULL, 1, now(), now());

-- -------------------------------------------
-- 2016-10-10 范朋飞 默认班次封面数据初始化 end
-- -------------------------------------------
-- -------------------------------------
-- 2016-10-17 孙宁海  权限相关 start
-- -------------------------------------
CREATE TABLE `permission` (
  `permission_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '权限id',
  `url` varchar(200) DEFAULT NULL COMMENT '权限url',
  `method` varchar(20) DEFAULT NULL COMMENT '请求类型GET POST PUT DELETE',
  `system` varchar(20) DEFAULT NULL COMMENT '系统类型 lcms,cms等',
  `is_anonymous` tinyint(4) DEFAULT NULL COMMENT '是否允许匿名访问 1 允许 0 不允许',
  `created_at` datetime DEFAULT NULL COMMENT '创建日期',
  `description` varchar(200) DEFAULT NULL COMMENT '信息描述',
  PRIMARY KEY (`permission_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='权限信息表';
CREATE TABLE `role_permission` (
  `role_permission_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `role_id` varchar(200) DEFAULT NULL COMMENT '角色id',
  `permission_id` varchar(20) DEFAULT NULL COMMENT '权限id',
  `created_at` datetime DEFAULT NULL COMMENT '创建日期',
  `description` varchar(200) DEFAULT NULL COMMENT '信息描述',
  PRIMARY KEY (`role_permission_id`),
  KEY `role_index` (`role_id`)
 )ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='角色权限关联表';

 insert into gxb_core.permission(url,method,system,created_at,description) VALUES
('/class/new/createClass/api','POST','lcms',now(),'旧版创建班次'),
('/class/new/api','POST','lcms',now(),'新版创建班次'),
('/class/{classId}/del/api','DELETE','lcms',now(),'删除班次'),
('/class/{classId}/close/api','DELETE','lcms',now(),'关闭班次')

INSERT INTO gxb_core.role_permission(role_id,permission_id,created_at,description)
select 2,a.permission_id,now(),null
from permission a where a.url='/class/new/createClass/api' and a.method='POST' and a.system='lcms';

INSERT INTO gxb_core.role_permission(role_id,permission_id,created_at,description)
select 5,a.permission_id,now(),null
from permission a where a.url='/class/new/createClass/api' and a.method='POST' and a.system='lcms';

INSERT INTO gxb_core.role_permission(role_id,permission_id,created_at,description)
select 6,a.permission_id,now(),null
from permission a where a.url='/class/new/createClass/api' and a.method='POST' and a.system='lcms';

INSERT INTO gxb_core.role_permission(role_id,permission_id,created_at,description)
select 7,a.permission_id,now(),null
from permission a where a.url='/class/new/createClass/api' and a.method='POST' and a.system='lcms';

INSERT INTO gxb_core.role_permission(role_id,permission_id,created_at,description)
select 14,a.permission_id,now(),null
from permission a where a.url='/class/new/createClass/api' and a.method='POST' and a.system='lcms';

INSERT INTO gxb_core.role_permission(role_id,permission_id,created_at,description)
select 2,a.permission_id,now(),null
from permission a where a.url='/class/new/api' and a.method='POST' and a.system='lcms';

INSERT INTO gxb_core.role_permission(role_id,permission_id,created_at,description)
select 5,a.permission_id,now(),null
from permission a where a.url='/class/new/api' and a.method='POST' and a.system='lcms';

INSERT INTO gxb_core.role_permission(role_id,permission_id,created_at,description)
select 6,a.permission_id,now(),null
from permission a where a.url='/class/new/api' and a.method='POST' and a.system='lcms';

INSERT INTO gxb_core.role_permission(role_id,permission_id,created_at,description)
select 7,a.permission_id,now(),null
from permission a where a.url='/class/new/api' and a.method='POST' and a.system='lcms';

INSERT INTO gxb_core.role_permission(role_id,permission_id,created_at,description)
select 14,a.permission_id,now(),null
from permission a where a.url='/class/new/api' and a.method='POST' and a.system='lcms';

INSERT INTO gxb_core.role_permission(role_id,permission_id,created_at,description)
select 2,a.permission_id,now(),null
from permission a where a.url='/class/{classId}/del/api' and a.method='DELETE' and a.system='lcms';

INSERT INTO gxb_core.role_permission(role_id,permission_id,created_at,description)
select 5,a.permission_id,now(),null
from permission a where a.url='/class/{classId}/del/api' and a.method='DELETE' and a.system='lcms';

INSERT INTO gxb_core.role_permission(role_id,permission_id,created_at,description)
select 6,a.permission_id,now(),null
from permission a where a.url='/class/{classId}/del/api' and a.method='DELETE' and a.system='lcms';


INSERT INTO gxb_core.role_permission(role_id,permission_id,created_at,description)
select 7,a.permission_id,now(),null
from permission a where a.url='/class/{classId}/del/api' and a.method='DELETE' and a.system='lcms';

INSERT INTO gxb_core.role_permission(role_id,permission_id,created_at,description)
select 14,a.permission_id,now(),null
from permission a where a.url='/class/{classId}/del/api' and a.method='DELETE' and a.system='lcms';


INSERT INTO gxb_core.role_permission(role_id,permission_id,created_at,description)
select 2,a.permission_id,now(),null
from permission a where a.url='/class/{classId}/close/api' and a.method='DELETE' and a.system='lcms';

INSERT INTO gxb_core.role_permission(role_id,permission_id,created_at,description)
select 5,a.permission_id,now(),null
from permission a where a.url='/class/{classId}/close/api' and a.method='DELETE' and a.system='lcms';

INSERT INTO gxb_core.role_permission(role_id,permission_id,created_at,description)
select 6,a.permission_id,now(),null
from permission a where a.url='/class/{classId}/close/api' and a.method='DELETE' and a.system='lcms';

INSERT INTO gxb_core.role_permission(role_id,permission_id,created_at,description)
select 7,a.permission_id,now(),null
from permission a where a.url='/class/{classId}/close/api' and a.method='DELETE' and a.system='lcms';

INSERT INTO gxb_core.role_permission(role_id,permission_id,created_at,description)
select 14,a.permission_id,now(),null
from permission a where a.url='/class/{classId}/close/api' and a.method='DELETE' and a.system='lcms';

-- -------------------------------------
-- 2016-10-17 孙宁海  权限相关 end
-- -------------------------------------

-- -------------------------------------
-- 2016-11-10 孙宁海  互动编程 start
-- -------------------------------------
create table programming_config (
  `config_id` INT4 NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint(20) NOT NULL,
  `user_id` bigint(20)  COMMENT '创建者id',
  `editor_id` bigint(20)  COMMENT '修改者id',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `delete_flag` tinyint(4) DEFAULT NULL COMMENT '是否删除  0 删除 1 未删除',
  PRIMARY KEY (`config_id`),
  KEY `index_tenant_id` (`tenant_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT '互动编程配置表';
create table submission_tutorial_replay_relate (
  `relate_id` BIGINT NOT NULL AUTO_INCREMENT,
  `submission_id` bigint(20) NOT NULL COMMENT '作业提交id',
  `replay_id` bigint(20)  COMMENT '学生录制视频id',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `delete_flag` tinyint(4) DEFAULT NULL COMMENT '是否删除  0 删除 1 未删除',
  PRIMARY KEY (`relate_id`),
  KEY `index_submission_id` (`submission_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT '编程作业提交关联表';
alter TABLE gxb_core.class_assignment add  sub_type varchar(20) default 'asset' COMMENT '提交类型，文本加附件 录制编程 asset programming';
alter TABLE gxb_core.class_set add  programming_percent INT4 COMMENT '编程权重';
alter table gxb_core.transcript add programming_num INT4 COMMENT '编程数量';
alter table gxb_core.transcript add view_programming_num INT4 COMMENT '观看的编程数量';
alter table gxb_core.transcript add programming_percentile_score DECIMAL(5,2)  COMMENT '编程分数';

-- gxb_study数据库
CREATE TABLE `programming_record` (
  `programming_record_id` BIGINT NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `class_id` int(11) DEFAULT NULL,
  `chapter_id` int(11) DEFAULT NULL,
  `programming_id` int(11) DEFAULT NULL,
  `programming_length` int(11) DEFAULT NULL,
  `cur_view_time` int(11) DEFAULT NULL,
  `max_view_time` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `learn_status` int(11) DEFAULT NULL,
  PRIMARY KEY (`programming_record_id`,`class_id`),
  UNIQUE KEY `class_chapter_user_unque_index` (`class_id`,`chapter_id`,`user_id`) USING BTREE,
  KEY `class_index` (`class_id`),
  KEY `chapter_index` (`chapter_id`),
  KEY `user_index` (`user_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT '编程观看记录表'
partition by hash(class_id) partitions 100;

-- -------------------------------------
-- 2016-11-10 孙宁海  互动编程 end
-- -------------------------------------

-- 2016-11-16 张金霞  考试乱序 end
-- -------------------------------------
alter table exam add COLUMN question_order TINYINT default 0;
alter table exam add COLUMN question_option_order TINYINT default 0;
-- -------------------------------------
-- 2016-11-16 张金霞  考试乱序 end
-- -------------------------------------

-- -------------------------------------

-- 2016-11-29 孙宁海  讨论评分 start
-- -------------------------------------
alter table class_topic add COLUMN score decimal(5,2) DEFAULT '100.00' COMMENT '满分';
-- -------------------------------------
-- 2016-11-29 孙宁海  讨论评分 end

-- 2016-11-24 孙宁海  成绩任务状态 end
-- -------------------------------------
CREATE TABLE `transcript_task_status` (
  `id` INT4 NOT NULL AUTO_INCREMENT COMMENT 'id',
  `cur_date` date NOT NULL COMMENT '日期',
  `status` TINYINT NOT NULL COMMENT '状态 0 计算中 1 计算完成',
  `created_at` datetime DEFAULT NULL COMMENT '创建时间',
  `updated_at` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `cur_date` (`cur_date`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='成绩计算状态';
-- -------------------------------------
-- 2016-11-24 孙宁海  成绩任务状态 end

-- -------------------------------------
-- 2016-11-21 楼文政  补考 start
-- -------------------------------------
alter TABLE gxb_core.exam add COLUMN is_makeup TINYINT DEFAULT 0 COMMENT '是否补考';
alter TABLE gxb_core.exam add COLUMN makeup_scores DECIMAL(5) COMMENT '参加补考分数';
alter TABLE gxb_core.exam add COLUMN parent_id BIGINT(20) COMMENT '补考对应的正考id';
-- -------------------------------------
-- 2016-11-21 楼文政  补考 end
-- -------------------------------------

-- -------------------------------------
-- 2016-12-06 楼文政  保存学生开考时间 start
-- -------------------------------------
alter TABLE gxb_core.exam_record add COLUMN start_at datetime COMMENT '学生开始答题时间';

UPDATE gxb_core.exam_record er,gxb_core.exam e set er.start_at = e.start_at where er.exam_id = e.exam_id and er.start_at is null
-- -------------------------------------
-- 2016-12-06 楼文政  保存学生开考时间 end
-- -------------------------------------

-- -------------------------------------
-- 2016-12-12 张金霞  保存学生考试IP start
-- -------------------------------------
alter table exam_score add COLUMN user_ip VARCHAR(20) DEFAULT null;
-- -------------------------------------
-- 2016-12-12 张金霞  保存学生考试IP end
-- -------------------------------------

-- -------------------------------------
-- 2016-12-19 孙宁海  直播主讲人头像 start
-- -------------------------------------
alter table  webcast add COLUMN teacher_avatar VARCHAR(200) DEFAULT null COMMENT '主讲人头像';
alter table  webcast add COLUMN app_cover VARCHAR(200) DEFAULT null COMMENT 'app入口图';
-- -------------------------------------
-- 2016-12-19 孙宁海  直播主讲人头像 end
-- -------------------------------------

-- -------------------------------------
-- 2016-12-26 张金霞 班次增加是否允许自主退选 start
-- -------------------------------------
alter table gxb_core.class add COLUMN dropping_self tinyint default 0 comment '是否允许自主退选，1是允许，0是禁止';
-- -------------------------------------
-- 2016-12-26 张金霞 班次增加是否允许自主退选 end
-- -------------------------------------

-- -------------------------------------
-- 2016-12-28 张金霞 题目类型 0-常规 1-期末 2-内嵌 start
-- -------------------------------------
alter table gxb_core.question add COLUMN business_type int DEFAULT 0 comment '题目类型：0-常规 1-期末 2-内嵌';
-- -------------------------------------
-- 2016-12-28 张金霞 题目类型 0-常规 1-期末 2-内嵌 start
-- -------------------------------------

-- -------------------------------------
-- 2016-12-29 楼文政  app弹层添加按钮 start
-- -------------------------------------
ALTER TABLE gxb_core.app_popup_img add COLUMN btn_url VARCHAR(255) COMMENT '按钮url';
-- -------------------------------------
-- 2016-12-29 楼文政  app弹层添加按钮 end
-- -------------------------------------


-- -------------------------------------
-- 2017-01-06 孙宁海  作业重新提交记录 start
-- -------------------------------------
CREATE TABLE `assignment_resubmit` (
  `submission_id` int(11) NOT NULL,
  `context_id` int(11) DEFAULT NULL,
  `context_type` varchar(255) DEFAULT NULL,
  `submitted_id` int(11) DEFAULT NULL,
  `submitted_type` varchar(255) DEFAULT NULL,
  `assignment_id` int(11) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `body` mediumtext,
  `url` varchar(255) DEFAULT NULL,
  `grade` varchar(255) DEFAULT NULL,
  `score` double(12,2) DEFAULT NULL,
  `submitted_at` datetime DEFAULT NULL,
  `submission_type` varchar(255) DEFAULT NULL,
  `processed` tinyint(1) DEFAULT NULL,
  `process_attempts` int(11) DEFAULT NULL,
  `published_score` double(12,2) DEFAULT NULL,
  `graded_at` datetime DEFAULT NULL,
  `graded_comment` mediumtext,
  `grader_id` int(11) DEFAULT NULL,
  `late` tinyint(1) DEFAULT NULL,
  `is_master` tinyint(1) DEFAULT NULL,
  `position` int(11) DEFAULT NULL,
  `status` varchar(255) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `peer_review_comment` mediumtext,
  `is_distribution` tinyint(4) DEFAULT '0',
  `is_calculateScore` tinyint(4) DEFAULT '0',
  `resubmit_user_id` int(11) COMMENT '作业打回人',
  `resubmit_at` datetime COMMENT '作业打回时间',
  PRIMARY KEY (`submission_id`),
  KEY `submitted_id_index` (`submitted_id`),
  KEY `user_id_index` (`user_id`),
  KEY `type_index` (`submitted_type`),
  KEY `context_id_index` (`context_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT '作业重新提交记录表'
-- -------------------------------------
-- 2017-01-06 孙宁海  作业重新提交记录 end
-- -------------------------------------

-- -------------------------------------
-- 李淦唐增改消息中心相关表 start
-- -------------------------------------
ALTER TABLE gxb_core.user_message_center ADD COLUMN title varchar(255) comment '标题';
ALTER TABLE gxb_core.user_message_center ADD COLUMN content varchar(255) comment '内容';
alter table gxb_core.user_message_center modify column message_type VARCHAR(255) DEFAULT 'Supervision' COMMENT '消息类型，Supervision：教学监督信息（class_push_message），TenantAnnouncement：租户公告（announcements），ClassAnnouncement：班次公告（announcement）,ClassEndNotice：结课预告，ScorePublish：成绩公布，ExamStartNotice：考试预告，ExamScorePublish：考试成绩公布，ExamMakeUp：补考安排，AssignmentBack：作业打回';

create table message (
  message_id BIGINT not null auto_increment PRIMARY KEY,
  user_id BIGINT NOT NULL COMMENT '创建者id',
  editor_id BIGINT NOT NULL COMMENT '修改者id',
  class_id BIGINT COMMENT '班次id',
  title varchar(255) NOT NULL COMMENT '标题',
  content varchar(255) NOT NULL COMMENT '内容',
  content_id bigint(20) NOT NULL COMMENT '内容相应id，例：作业id，考试id',
  content_type varchar(255) NOT NULL COMMENT 'ClassEndNotice：结课预告，ScorePublish：成绩公布，ExamStartNotice：考试预告，ExamScorePublish：考试成绩公布，ExamMakeUp：补考安排，AssignmentBack：作业打回',
  created_at datetime default now(),
  updated_at datetime default now(),
  KEY index_user_id (user_id),
  KEY index_class_id (class_id)
);
-- -------------------------------------
-- 李淦唐增改消息中心相关表 end
-- -------------------------------------



-- 2017-02-08 孙宁海  互动编程时速云修改 start
-- -------------------------------------
alter TABLE gxb_core.class_assignment add  programming_source tinyint COMMENT '互动编程来源默认0 时速云1 ';

-- 时速云在线编译需要执行的sql

-- 老师端多文件
CREATE TABLE `gxb_core`.`programming_file` (
	`file_id` bigint(10) NOT NULL AUTO_INCREMENT COMMENT '主键',
	`path` varchar(100) NOT NULL COMMENT '文件的路径',
	`content` mediumtext COMMENT '文件内容',
	`code_replay_id` bigint(10) NOT NULL COMMENT '对应编程id',
	`created_at` datetime NOT NULL,
	`updated_at` datetime NOT NULL,
	`delete_flag` tinyint(2) NOT NULL,
	PRIMARY KEY (`file_id`)
) COMMENT='';

-- 老师端replay增加字段
ALTER TABLE `gxb_core`.`system_code_replay` CHANGE COLUMN `html` `html` MEDIUMTEXT COMMENT '最终html代码' ,
 ADD COLUMN `main_path` VARCHAR(200) COMMENT 'mian函数所在类的路径' AFTER `totaltime` ,
 ADD COLUMN `main_argus` VARCHAR(200) COMMENT 'main函数参数' AFTER `main_path` ,
 ADD COLUMN `runtime_argus` VARCHAR(200) COMMENT 'Scanner参数' AFTER `main_argus` ,
 ADD COLUMN `programming_source` TINYINT(4) NOT NULL DEFAULT 0 COMMENT '是否是时速云编译，0否  1是' AFTER `runtime_argus`;

-- 学生端replay增加字段
ALTER TABLE `gxb_core`.`system_student_replay` CHANGE COLUMN `html` `html` MEDIUMTEXT COMMENT '最终html代码' ,
ADD COLUMN `class_id` bigint(10) AFTER `audio_url`,
 ADD COLUMN `totaltime` int(10) AFTER `class_id`,
ADD COLUMN `main_path` VARCHAR(200) COMMENT 'mian函数所在类的路径' AFTER `totaltime` ,
 ADD COLUMN `main_argus` VARCHAR(200) COMMENT 'main函数参数' AFTER `main_path` ,
 ADD COLUMN `runtime_argus` VARCHAR(200) COMMENT 'Scanner参数' AFTER `main_argus` ,
 ADD COLUMN `programming_source` TINYINT(4) NOT NULL DEFAULT 0 COMMENT '是否是时速云编译，0否  1是' AFTER `runtime_argus`;


-- 老师端record增加字段
ALTER TABLE `gxb_core`.`system_code_record` ADD COLUMN `status` VARCHAR(10) AFTER `stderr` ,
 ADD COLUMN `compiler_info` MEDIUMTEXT AFTER `status` ,
 ADD COLUMN `content` MEDIUMTEXT AFTER `compiler_info` ,
 ADD COLUMN `error` MEDIUMTEXT AFTER `content` ,
 ADD COLUMN `code` VARCHAR(10) AFTER `error`,
ADD COLUMN `main_argus` varchar(200) AFTER `code`,
ADD COLUMN `runtime_argus` varchar(200) AFTER `main_argus`,
 ADD COLUMN `treefile` varchar(200) AFTER `runtime_argus`;

-- 学生端多文件
CREATE TABLE `gxb_core`.`programming_file_student` (
	`file_id` bigint(10) NOT NULL AUTO_INCREMENT COMMENT '主键',
	`path` varchar(100) NOT NULL COMMENT '文件的路径',
	`content` mediumtext COMMENT '文件内容',
	`code_replay_id` bigint(10) NOT NULL COMMENT '对应编程id',
	`created_at` datetime NOT NULL,
	`updated_at` datetime NOT NULL,
	`delete_flag` tinyint(2) NOT NULL,
	PRIMARY KEY (`file_id`)
) COMMENT='';

-- 学生端record增加字段
ALTER TABLE `gxb_core`.`system_student_record` ADD COLUMN `status` VARCHAR(10) AFTER `stderr` ,
 ADD COLUMN `compiler_info` MEDIUMTEXT AFTER `status` ,
 ADD COLUMN `content` MEDIUMTEXT AFTER `compiler_info` ,
 ADD COLUMN `error` MEDIUMTEXT AFTER `content` ,
 ADD COLUMN `code` VARCHAR(10) AFTER `error`,
ADD COLUMN `main_argus` varchar(200) AFTER `code`,
ADD COLUMN `runtime_argus` varchar(200) AFTER `main_argus`,
 ADD COLUMN `treefile` varchar(200) AFTER `runtime_argus`;
-- -------------------------------------
-- 2017-02-08 孙宁海  互动编程时速云修改 end

-- 编程考试相关脚本 Start
-- 编程题库表
DROP TABLE IF EXISTS `programming_node`;
CREATE TABLE `gxb_core`.`programming_node` (
	`programming_node_id` bigint(20) NOT NULL AUTO_INCREMENT,
	`parent_id` bigint(20) NOT NULL COMMENT '父节点id',
	`course_id` bigint(20) NOT NULL COMMENT '课程id',
	`context_uuid` varchar(40) NOT NULL COMMENT '课程的uuid或者班次的uuid',
	`context_type` varchar(255) NOT NULL COMMENT '所属类型，课程或者班级',
	`programming_id` bigint(20) NOT NULL COMMENT 'programming id',
	`type` varchar(255) DEFAULT NULL COMMENT '文件夹 或者文件类型',
	`name` varchar(400) NOT NULL COMMENT '名称',
	`tenant_id` bigint(20) DEFAULT NULL COMMENT '租户id',
	`is_private` tinyint(4) DEFAULT NULL COMMENT '是否私有,1是私有 0不是私有',
	`position` int(11) DEFAULT NULL COMMENT '排序坐标',
	`status` varchar(255) DEFAULT NULL COMMENT '状态',
	`delete_flag` tinyint(4) DEFAULT NULL COMMENT '1可用 0 不可用',
	`created_at` datetime DEFAULT NULL,
	`updated_at` datetime DEFAULT NULL,
	`user_id` bigint(20) NOT NULL,
	`editor_id` bigint(20) NOT NULL,
	`language_type` VARCHAR(10),
	PRIMARY KEY (`programming_node_id`),
	INDEX `index_parent_id` USING BTREE (`parent_id`) comment '',
	INDEX `index_context_uuid` USING BTREE (`context_uuid`) comment '',
	INDEX `index_programming_id` USING BTREE (`programming_id`) comment '',
	INDEX `index_context_type` USING BTREE (`context_type`(10)) comment '',
	INDEX `index_course_id` USING BTREE (`course_id`) comment ''
) ENGINE=`InnoDB` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ROW_FORMAT=COMPACT COMMENT='' CHECKSUM=0 DELAY_KEY_WRITE=0;

-- 编程题表
DROP TABLE IF EXISTS `programming`;
CREATE TABLE `gxb_core`.`programming` (
	`programming_id` bigint(20) NOT NULL AUTO_INCREMENT,
	`course_id` bigint(20) NOT NULL COMMENT '课程id',
	`user_id` bigint(20) NOT NULL,
	`editor_id` bigint(20) NOT NULL,
 	`title` VARCHAR(1000)  NOT NULL COMMENT '标题',
	`title_desc` mediumtext NOT NULL COMMENT '题目描述',
	`input_desc` VARCHAR(10000) NOT NULL COMMENT '输入描述',
 	`output_desc` mediumtext NOT NULL COMMENT '输出描述',
	`code_model` mediumtext DEFAULT NULL COMMENT '代码模块',
	`comment` mediumtext DEFAULT NULL COMMENT '题目解析',
	`level` varchar(255) DEFAULT NULL COMMENT '题目难度级别',
	`position` int(11) DEFAULT NULL COMMENT '排序坐标',
	`status` varchar(255) DEFAULT NULL COMMENT '状态',
	`delete_flag` tinyint(4) DEFAULT NULL COMMENT '1可用 0不可用',
	`created_at` datetime DEFAULT NULL,
	`updated_at` datetime DEFAULT NULL,
	`business_type` int(11) DEFAULT 0 COMMENT '题目类型：0-常规 1-期末 2-内嵌',
	`language_type` varchar(255) NOT NULL COMMENT '编程语言类型',
	PRIMARY KEY (`programming_id`),
	INDEX `index_course_id` USING BTREE (`course_id`) comment ''
) ENGINE=`InnoDB` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ROW_FORMAT=COMPACT COMMENT='' CHECKSUM=0 DELAY_KEY_WRITE=0;

-- 测试用例表
DROP TABLE IF EXISTS `testcase`;
CREATE TABLE `gxb_core`.`testcase` (
	`testcase_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
	`programming_id` bigint(20) NOT NULL COMMENT '编程题id',
	`content` mediumtext NOT NULL COMMENT 'testcase内容',
	`user_id` bigint(10) NOT NULL,
	`created_at` datetime NOT NULL,
	`updated_at` datetime NOT NULL,
	`delete_flag` tinyint NOT NULL,
	PRIMARY KEY (`testcase_id`)
) COMMENT='';

-- 学生提交编程内容
DROP TABLE IF EXISTS `programming_submission`;
CREATE TABLE `gxb_core`.`programming_submission` (
	`programming_submission_id` bigint(20) NOT NULL AUTO_INCREMENT,
	`user_id` bigint(10) NOT NULL,
	`programming_id` bigint(20) NOT NULL COMMENT '编程id',
	`exam_id` bigint(10) NOT NULL COMMENT '考试id',
	`correct_rate` double(12,2) COMMENT '正确率',
	`content` mediumtext NOT NULL COMMENT '编程内容',
	`created_at` datetime NOT NULL,
	`updated_at` datetime NOT NULL,
	PRIMARY KEY (`programming_submission_id`)
) COMMENT='';

-- 添加考试表增加字段
ALTER TABLE `gxb_core`.`exam`
  ADD COLUMN `has_programming` tinyint(5) NOT NULL DEFAULT 0 COMMENT '是否设置编程题   0不设置   1设置' AFTER `parent_id`,
  ADD COLUMN `objective_percent` int(11) COMMENT '客观题占比' AFTER `has_programming`,
  ADD COLUMN `programming_percent` int(11) COMMENT '编程题占比' AFTER `objective_percent`;

-- 试题与题干关联表增加字段
ALTER TABLE `gxb_core`.`question_type_question_relate` ADD COLUMN `type` varchar(255) DEFAULT 'objective' COMMENT '试题类型  objective客观题  programming编程题  ' AFTER `updated_at`;

-- 编程考试相关脚本 End

-- -------------------------------------
-- -------------------------------------
-- 2017-03-01 楼文政  建课开班白名单 start
-- -------------------------------------
--白名单表
CREATE TABLE `teacher_white_list` (
  `teacher_white_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) DEFAULT NULL,
  `content_id` bigint(20) DEFAULT NULL,
  `content_type` varchar(255) DEFAULT NULL,
  `editor_id` bigint(20) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`teacher_white_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--刷特定租户的
INSERT INTO gxb_core.teacher_white_list (
	user_id,
	content_id,
	content_type,
	editor_id,
	created_at,
	updated_at
)
	SELECT
	ru.user_id,
	c.class_id,
	'class',
	0,
	NOW(),
	NOW()
FROM
	gxb_core.role_user ru
JOIN gxb_core.class c ON ru.tenant_id = c.tenant_id
WHERE
	ru.role_id = 2
AND ru.tenant_id = 301
and c.delete_flag = 1
;

INSERT INTO gxb_core.teacher_white_list (
	user_id,
	content_id,
	content_type,
	editor_id,
	created_at,
	updated_at
)
SELECT
	ru.user_id,
	c.course_id,
	'course',
	0,
	NOW(),
	NOW()
FROM
	gxb_core.role_user ru
JOIN gxb_core.course c ON ru.tenant_id = c.tenant_id
WHERE
	ru.role_id = 1
AND ru.tenant_id = 301
and c.delete_flag = 1
;

-- -------------------------------------
-- 2017-03-01 楼文政  建课开班白名单 end
-- -------------------------------------

-- -------------------------------------
-- 2017-03-08 孙宁海  直播添加课件介绍 start
-- -------------------------------------
alter TABLE gxb_core.webcast add  material_description mediumtext COMMENT '课件介绍';
-- -------------------------------------
-- 2017-03-08 孙宁海  直播添加课件介绍  end
-- -------------------------------------

-- -------------------------------------
-- 2017-03-13 楼文政  y轴项目所需 start
-- -------------------------------------
ALTER TABLE gxb_core.class ADD COLUMN practice_hour int(11) comment '实践课时';
ALTER TABLE gxb_core.class ADD COLUMN theory_hour int(11) comment '理论课时';
ALTER TABLE gxb_core.class ADD COLUMN version varchar(255) comment '版本';

ALTER TABLE gxb_core.class ADD COLUMN research_personnel varchar(255) comment '研发人员';
ALTER TABLE gxb_core.class ADD COLUMN evaluation_mode tinyint(4) comment '考核方式 1:线上考试 2:线下考试 3:答辩 4:大作业';

ALTER TABLE gxb_core.class_info ADD COLUMN cultivate mediumtext comment '培养目标';

ALTER TABLE gxb_core.class_info ADD COLUMN first_study varchar(255) comment '先修课名称';

ALTER TABLE gxb_core.class_info ADD COLUMN outline_link varchar(255) comment '标准大纲';

ALTER TABLE gxb_core.class_info ADD COLUMN outline_title varchar(255) comment '标准大纲文件名';

ALTER TABLE gxb_core.class_info ADD COLUMN courseware_link varchar(255) comment '标准课件';

ALTER TABLE gxb_core.class_info ADD COLUMN courseware_title varchar(255) comment '标准课件文件名';

ALTER TABLE gxb_core.class_unit ADD COLUMN teaching_design_id BIGINT(20) comment '教学设计id';

ALTER TABLE gxb_core.class_item ADD COLUMN teaching_design_id BIGINT(20) comment '教学设计id';

CREATE TABLE `class_teaching_design` (
  `class_teaching_design_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `class_id` bigint(20) DEFAULT NULL,
  `content_id` bigint(20) DEFAULT NULL,
  `content_type` varchar(255) DEFAULT NULL,
  `user_id` bigint(20) DEFAULT NULL,
  `target` mediumtext,
  `extract` mediumtext,
  `essentials` mediumtext,
  `resource` mediumtext,
  `flow` mediumtext,
  `checkpoint` mediumtext,
  `performance` mediumtext,
  `delete_flag` tinyint(4) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`class_teaching_design_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

CREATE TABLE `class_level` (
  `class_level_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `level_name` varchar(255) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`class_level_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;



CREATE TABLE `class_level_relate` (
  `class_level_relate_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `class_level_id` bigint(20) DEFAULT NULL,
  `class_id` bigint(20) DEFAULT NULL,
  `user_id` bigint(20) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`class_level_relate_id`,`created_at`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
INSERT INTO gxb_core.`class_level` VALUES ('1', '中职中专', '2017-03-01 16:44:24', '2017-03-01 16:44:27'), ('2', '高职高专', '2017-03-01 16:44:42', '2017-03-01 16:44:45'), ('3', '本科', '2017-03-01 16:45:00', '2017-03-01 16:45:03'), ('4', '研究生(普硕)', '2017-03-01 16:45:27', '2017-03-01 16:45:29'), ('5', '研究生(在职)', '2017-03-01 16:45:43', '2017-03-01 16:45:46');


CREATE TABLE `class_school_room` (
  `school_room_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `class_id` bigint(20) DEFAULT NULL,
  `user_id` bigint(20) DEFAULT NULL,
  `editor_id` bigint(20) DEFAULT NULL,
  `tenant_id` bigint(20) DEFAULT NULL,
  `title` varchar(255) DEFAULT NULL,
  `body` mediumtext,
  `start_at` datetime DEFAULT NULL,
  `lesson` varchar(255) DEFAULT NULL,
  `delete_flag` tinyint(4) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`school_room_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

insert into `gxb_core`.`role` ( `desc`, `type`, `updated_at`, `name`, `created_at`) values ( null, 'tenant', null, '班主任', null);
-- -------------------------------------
-- 2017-03-13 楼文政  y轴项目所需 end
-- -------------------------------------

-- 2017-03-13 孙宁海  直播租户权限 start
-- -------------------------------------
create table webcast_permission_config (
  `config_id` INT4 NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint(20) NOT NULL,
  `user_id` bigint(20)  COMMENT '创建者id',
  `editor_id` bigint(20)  COMMENT '修改者id',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `delete_flag` tinyint(4) DEFAULT NULL COMMENT '是否删除  0 删除 1 未删除',
  PRIMARY KEY (`config_id`),
  KEY `index_tenant_id` (`tenant_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT '直播配置表';
-- -------------------------------------
-- 2017-03-13 孙宁海  直播租户权限  end
-- -------------------------------------




-- 2017-03-17 孙宁海  建课开班权限合并 start
-- -------------------------------------
update role set name ='建课老师' where role_id =2;

update role_user set role_id = 2 where role_id =1;

select GROUP_CONCAT(role_user_id) from (select * from role_user where role_id = 2 group by tenant_id,user_id HAVING count(1)>1)t1;
-- -------------------------------------
-- 2017-03-17 孙宁海  建课开班权限合并  end
-- -------------------------------------


-- 2017-03-28 孙宁海  复制班次标识复制售卖来源 start
-- -------------------------------------
ALTER TABLE gxb_core.class ADD COLUMN from_class BIGINT(20) comment '班次复制售卖源';
ALTER TABLE gxb_core.class ADD COLUMN root_class BIGINT(20) comment '班次复制根源';
-- -------------------------------------
-- 2017-03-28 孙宁海  复制班次标识复制售卖来源  end
-- -------------------------------------

-- 2017-04-11 孙宁海  考试表客户端类型 IP限制 start
-- -------------------------------------
ALTER TABLE gxb_core.exam ADD COLUMN client_type tinyint comment '客户端类型0 不限制 1 WEB端 2 PC客户端';

create table exam_white_list (
  relate_id BIGINT not null auto_increment PRIMARY KEY,
  exam_id BIGINT NOT NULL COMMENT '考试ID',
  start_ip BIGINT NOT NULL COMMENT '起始ip,int存储ip inet_aton(ip) inet_ntoa(int值)',
  end_ip BIGINT COMMENT '结束ip',
  created_at datetime,
  updated_at datetime,
  KEY index_user_id (exam_id)
)COMMENT='考试IP白名单';
-- -------------------------------------
-- 2017-04-11 孙宁海  考试表客户端类型 IP限制 end
--  -------------------------------------

alter table gxb_core.student MODIFY status int(11)  COMMENT '学生状态 10在校 20校外实习 30毕业 40社会用户';

alter table gxb_core.webcast_material modify description mediumtext  COMMENT '资料描述';

-- -------------------------------------
-- 2017-05-15 孙宁海 考试主观题  start
--  -------------------------------------
ALTER TABLE gxb_core.exam ADD COLUMN subjective_percent int(11) DEFAULT 0 comment '主观题占比';
CREATE TABLE `subjective_question` (
  `subjective_question_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL,
  `editor_id` bigint(20) NOT NULL,
  `title` varchar(4000) DEFAULT NULL COMMENT '标题',
  `comment` mediumtext COMMENT '题目解析',
  `level` varchar(255) DEFAULT NULL COMMENT '题目难度级别',
  `delete_flag` tinyint(4) DEFAULT NULL COMMENT '1可用 0不可用',
  `least_count_answer` int(11)  DEFAULT NULL COMMENT '答案字数限制',
  `score` DECIMAL(3,0)  DEFAULT 0 COMMENT '分值',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`subjective_question_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='主观题表';
alter table gxb_core.question_type_question_relate MODIFY COLUMN `type` varchar(255) DEFAULT 'objective' COMMENT '试题类型  objective客观题  programming编程题 subjective 主观题 ';

CREATE TABLE `subjective_question_submission` (
  `subjective_question_submission_id` int(11) NOT NULL AUTO_INCREMENT,
  `exam_id` int(11) NOT NULL comment '考试id',
  `user_id` int(11) NOT NULL comment '提交人',
  `subjective_question_id` int(11) NOT NULL comment '主观题id',
  `submission_data` mediumtext comment '提交内容',
  `submitted_at` datetime DEFAULT NULL comment '提交时间',
  `graded_at` datetime DEFAULT NULL comment '批改时间',
  `graded_id` int(11)  comment '批阅人',
  `graded_comment` mediumtext comment '批阅注解',
  `score` double(3,0) DEFAULT NULL comment '评分',
  `delete_flag` tinyint(4) DEFAULT NULL COMMENT '1可用 0不可用',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`subjective_question_submission_id`),
  KEY `index_exam_user_subjective_id` (`exam_id`,`user_id`,`subjective_question_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 comment '主观题提交记录';

alter table gxb_core.exam_score add COLUMN `subjective_score` DECIMAL(5,2)  COMMENT '主观题得分';
alter table gxb_core.exam_score add COLUMN `objective_score` DECIMAL(5,2)  COMMENT '客观题得分';
alter table gxb_core.exam_score add COLUMN `programming_score` DECIMAL(5,2)  COMMENT '编程题得分';
alter table gxb_core.exam_score add COLUMN `subjective_grade_complete` CHAR(1) DEFAULT '0' COMMENT '主观题是否批阅完成 1 完成 0 未完成';
-- -------------------------------------
-- 2017-05-15 孙宁海 考试主观题 end
--  -------------------------------------

-- -------------------------------------
-- 2017-07-03 楼文政 考试主观题添加参考答案字段  start
--  -------------------------------------
ALTER TABLE `gxb_core`.`subjective_question` ADD COLUMN `reference_answer` mediumtext COMMENT '参考答案';
-- -------------------------------------
-- 2017-07-03 楼文政 考试主观题添加参考答案字段  end
--  -------------------------------------



-- 粉笔匠app start
ALTER TABLE `gxb_core`.`study_profile` CHANGE COLUMN `school_room_id` `school_room_id` bigint(20) DEFAULT 0 COMMENT '课堂id';

ALTER TABLE `gxb_core`.`class_pop_quiz` CHANGE COLUMN `school_room_id` `school_room_id` bigint(20) DEFAULT 0 COMMENT '课堂id';

ALTER TABLE `gxb_core`.`roll_call` CHANGE COLUMN `school_room_id` `school_room_id` bigint(20) DEFAULT 0 COMMENT '课堂id';

-- ppt转码状态
DROP TABLE IF EXISTS `ppt_convert`;
CREATE TABLE `gxb_core`.`ppt_convert` (
	`ppt_convert_id` bigint(10) NOT NULL AUTO_INCREMENT,
	`resource_node_id` bigint(10) COMMENT 'ppt文件的resource_node_id',
	`ppt_url` varchar(255) COMMENT 'ppt的地址',
	`html_url` varchar(255) COMMENT 'ppt的html地址',
	`status` tinyint(4) COMMENT '状态   0 未转码  1 已转码',
	`thumb` varchar(255) COMMENT 'ppt封面',
	`page_amount` int(10) COMMENT '页数',
	`ppt_id` int(10) COMMENT '自由坊pptId',
	`filename` varchar(255) COMMENT '文件名',
	`created_at` datetime,
	`updated_at` datetime,
	PRIMARY KEY (`ppt_convert_id`)
) COMMENT='';

-- 粉笔匠app end

=======
-- -------------------------------------
-- 2017-07-06 孙宁海 关于我们 友链  start
--  -------------------------------------
CREATE TABLE `friend_chain` (
  `friend_chain_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL,
  `editor_id` bigint(20) NOT NULL,
  `title` varchar(255) DEFAULT NULL COMMENT '友链名称',
  `url` varchar(255) COMMENT '跳转链接',
  `tenant_id` bigint(20) NOT NULL COMMENT '租户id',
  `delete_flag` tinyint(4) DEFAULT NULL COMMENT '1可用 0不可用',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`friend_chain_id`),
  KEY `index_tenant_id` (`tenant_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='友链表';

CREATE TABLE `about_us` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) DEFAULT '',
  `body` mediumtext,
  `user_id` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_tenant_id` (`tenant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='关于我们';
-- -------------------------------------
-- 2017-07-06 孙宁海 关于我们 友链  end
--  -------------------------------------

-- -------------------------------------
-- 2017-07-14 孙宁海 视频完成度  start
--  -------------------------------------
alter table gxb_core.class_set add COLUMN `video_completion` tinyint   COMMENT '视频完成度';
-- -------------------------------------
-- 2017-07-14 孙宁海 视频完成度  end
-- -------------------------------------

-- 2017-07-14 楼文政 测验添加是否显示答案与解析 讨论分区添加是否自定义字段  start
--  -------------------------------------
ALTER TABLE `gxb_core`.`class_quiz` ADD COLUMN `show_answer_comment` tinyint(4) DEFAULT 1 COMMENT '是否显示答案与解析';
ALTER TABLE `gxb_core`.`forum` ADD COLUMN `is_custom` tinyint(4) DEFAULT 0 COMMENT '是否为自定义分区';
-- -------------------------------------
-- 2017-07-14 楼文政 测验添加是否显示答案与解析 讨论分区添加是否自定义字段  end
-- -------------------------------------

-- 2017-07-18 孙宁海 短信发送统计  start
--  -------------------------------------
CREATE TABLE `mobile_message_record` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `class_id` bigint,
  `context_id` bigint comment 'context_type对应id',
  `context_type` VARCHAR(30) comment '类型 announcement teachingSupervision',
  `mobile` varchar(20) comment '手机号',
  `user_id` bigint comment '发送人',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='短信发送记录表';
-- -------------------------------------
-- 2017-07-18 孙宁海 短信发送统计  end
-- -------------------------------------

-- -------------------------------------
-- 2017-08-07 楼文政 直播添加密码与类型字段  start
-- -------------------------------------
ALTER TABLE `gxb_core`.`webcast` ADD COLUMN `type` tinyint(4);
ALTER TABLE `gxb_core`.`webcast` ADD COLUMN `password` varchar(255);
-- -------------------------------------
-- 2017-08-07 楼文政 直播添加密码与类型字段  end
-- -------------------------------------

-- 2017-08-02 孙宁海 权限配置相关  start
--  -------------------------------------
ALTER TABLE `gxb_core`.`role` ADD COLUMN `tenant_id` int(11)  COMMENT '租户id';
CREATE TABLE `permission_dictionary` (
  `dictionary_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '字典id',
  `parent_id` int(11) NOT NULL  COMMENT '父Id -1 无父级',
  `type` tinyint NOT NULL COMMENT '类型 0 非叶子 1叶子',
  `title` varchar(200) DEFAULT NULL COMMENT '标题',
  `code` varchar(200) DEFAULT NULL COMMENT '权限码',
  `delete_ftlag` tinyint(20) NOT NULL COMMENT '状态 1 可用 0 不可用',
  `created_at` datetime NOT NULL COMMENT '创建时间',
  `updated_at` datetime NOT NULL COMMENT '更新时间',
  PRIMARY KEY (`dictionary_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='权限字典表';

insert into gxb_core.permission_dictionary(dictionary_id,parent_id,type,title,code,delete_flag,created_at,updated_at) values
(1,-1,0,'内容管理','',1,now(),now()),
(2,-1,0,'教学管理','',1,now(),now()),
(3,-1,0,'成员管理','',0,now(),now()),
(4,-1,0,'数据中心','',1,now(),now()),
(5,-1,0,'校管中心','',1,now(),now()),
(6,1,1,'章节内容修改','',1,now(),now()),
(7,1,1,'班次资源修改','',1,now(),now()),
(8,2,1,'教学监督','',1,now(),now()),
(9,2,1,'辅助教学','',1,now(),now()),
(10,2,1,'视频学习情况管理','',1,now(),now()),
(11,2,1,'测验学习情况管理','',1,now(),now()),
(12,2,1,'讨论学习情况管理','',1,now(),now()),
(13,2,1,'课堂学习情况管理','',1,now(),now()),
(14,2,1,'作业学习情况管理','',1,now(),now()),
(15,2,1,'编程学习情况管理','',1,now(),now()),
(16,2,1,'考试管理','',1,now(),now()),
(17,3,1,'成员管理','',1,now(),now()),
(18,4,1,'学校总览','',1,now(),now()),
(19,4,1,'课程分析','',1,now(),now()),
(20,4,1,'学生分析','',1,now(),now()),
(21,4,1,'老师分析','',1,now(),now()),
(22,4,1,'导学-学生分析','',1,now(),now()),
(23,5,1,'用户管理','',1,now(),now()),
(24,5,1,'权限管理','',1,now(),now()),
(25,5,1,'门户首页管理','',1,now(),now()),
(26,5,1,'课程广场管理','',1,now(),now()),
(27,5,1,'帮助中心问题管理','',1,now(),now()),
(28,2,1,'成绩管理','',1,now(),now());


insert into gxb_core.permission(permission_id,url,method,system,created_at,description) values
(30,'/class/{classId}/mode/revision','GET','lcms',now(),'章节内容页面'),
(31,'/class/{classId}/resource/revision','GET','lcms',now(),'课程资源页面'),
(32,'/teaching/class/{classId}/supervise/revision','GET','lcms',now(),'教学监督页面'),
(33,'/class/{classId}/video/list','GET','lcms',now(),'视频学习管理页面'),
(34,'/teaching/class/{classId}/quiz/revision','GET','lcms',now(),'测验学习管理页面'),
(35,'/teaching/class/{classId}/topic/revision','GET','lcms',now(),'讨论学习管理页面'),
(36,'/teaching/class/{classId}/classroom','GET','lcms',now(),'课堂学习管理页面'),
(37,'/teaching/class/{classId}/assignment/revision','GET','lcms',now(),'作业学习管理页面'),
(38,'/teaching/class/{classId}/program/revision','GET','lcms',now(),'编程学习管理页面'),
(39,'/class/{classId}/exam/list','GET','lcms',now(),'考试学习管理页面'),
(40,'/class/{classId}/calendar','GET','lcms',now(),'教学足迹管理页面'),
(41,'/class/{classId}/announcements','GET','lcms',now(),'公告管理页面'),
(42,'/teaching/class/{classId}/revision/topic','GET','lcms',now(),'话题管理页面'),
(43,'/class/{classId}/revision/activities','GET','lcms',now(),'活动管理页面'),
(44,'/class/{classId}/votes','GET','lcms',now(),'投票管理页面'),
(45,'/class/{classId}/surveys','GET','lcms',now(),'问卷管理页面'),
(46,'/class/{classId}/revision/studyauthorization','GET','lcms',now(),'班级成员管理页面'),
(47,'/teaching/class/{classId}/achievement/revision','GET','lcms',now(),'成绩管理页面'),
(48,'/class/{classId}/createExam/type/api','GET','lcms',now(),'创建考试点击'),
(49,'/exam/{examId}/class/{classId}/api','DELETE','lcms',now(),'删除考试点击'),
(50,'/class/{classId}/exam/{examId}/publish/api','PUT','lcms',now(),'发布考试'),
(51,'/class/{classId}/exam/{examId}','GET','lcms',now(),'编辑考试'),
(52,'/class/{classId}/exam/{examId}/paper/{paperId}/examinationPaper','GET','lcms',now(),'试卷内容'),
(53,'/class','GET','lcms',now(),'班次管理列表'),
(54,'/course','GET','lcms',now(),'课程管理列表');




CREATE TABLE `permission_dictionary_permission_relate` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `dictionary_id` int(11) NOT NULL  COMMENT '字典id',
  `permission_id` int(11) NOT NULL  COMMENT '权限Id',
  `delete_flag` tinyint(20) NOT NULL COMMENT '状态 1 可用 0 不可用',
  `created_at` datetime NOT NULL COMMENT '创建时间',
  `updated_at` datetime NOT NULL COMMENT '更新时间',
   PRIMARY KEY (`id`),
   KEY `index_dictionary_id` (`dictionary_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='权限字典与具体权限关联表';

insert into gxb_core.permission_dictionary_permission_relate(dictionary_id,permission_id,delete_flag,created_at,updated_at) VALUES
(6,30,1,now(),now()),
(6,48,1,now(),now()),
(6,49,1,now(),now()),
(6,50,1,now(),now()),
(6,51,1,now(),now()),
(6,52,1,now(),now()),
(6,53,1,now(),now()),
(6,54,1,now(),now()),
(7,31,1,now(),now()),
(7,53,1,now(),now()),
(7,54,1,now(),now()),
(8,32,1,now(),now()),
(8,53,1,now(),now()),
(9,40,1,now(),now()),
(9,41,1,now(),now()),
(9,42,1,now(),now()),
(9,43,1,now(),now()),
(9,44,1,now(),now()),
(9,45,1,now(),now()),
(9,53,1,now(),now()),
(10,33,1,now(),now()),
(10,53,1,now(),now()),
(11,34,1,now(),now()),
(11,53,1,now(),now()),
(12,35,1,now(),now()),
(12,53,1,now(),now()),
(13,36,1,now(),now()),
(13,53,1,now(),now()),
(14,37,1,now(),now()),
(14,53,1,now(),now()),
(15,38,1,now(),now()),
(15,53,1,now(),now()),
(16,39,1,now(),now()),
(16,53,1,now(),now()),
(17,46,1,now(),now()),
(17,53,1,now(),now()),
(28,47,1,now(),now()),
(28,53,1,now(),now());


CREATE TABLE `role_permission_dictionary_relate` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `dictionary_id` int(11) NOT NULL  COMMENT '权限字典id',
  `role_id` int(11) NOT NULL  COMMENT '权限Id',
  `delete_flag` tinyint(20) NOT NULL COMMENT '状态 1 可用 0 不可用',
  `created_at` datetime NOT NULL COMMENT '创建时间',
  `updated_at` datetime NOT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `index_dictionary_id` (`dictionary_id`),
  KEY `index_role_id` (`role_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='角色权限字典关联表';

insert into gxb_core.role_permission_dictionary_relate(role_id,dictionary_id,delete_flag,created_at,updated_at) VALUES
(2,6,1,now(),now()),
(2,7,1,now(),now()),
(2,16,1,now(),now()),
(2,17,1,now(),now()),
(3,8,1,now(),now()),
(3,9,1,now(),now()),
(3,10,1,now(),now()),
(3,11,1,now(),now()),
(3,12,1,now(),now()),
(3,13,1,now(),now()),
(3,14,1,now(),now()),
(3,15,1,now(),now()),
(3,16,1,now(),now()),
(3,28,1,now(),now()),
(13,18,1,now(),now()),
(13,19,1,now(),now()),
(13,20,1,now(),now()),
(13,21,1,now(),now()),
(13,22,1,now(),now()),
(7,6,1,now(),now()),
(7,7,1,now(),now()),
(7,8,1,now(),now()),
(7,9,1,now(),now()),
(7,10,1,now(),now()),
(7,11,1,now(),now()),
(7,12,1,now(),now()),
(7,13,1,now(),now()),
(7,14,1,now(),now()),
(7,15,1,now(),now()),
(7,16,1,now(),now()),
(7,17,1,now(),now()),
(7,18,1,now(),now()),
(7,19,1,now(),now()),
(7,20,1,now(),now()),
(7,21,1,now(),now()),
(7,22,1,now(),now()),
(7,23,1,now(),now()),
(7,24,1,now(),now()),
(7,25,1,now(),now()),
(7,26,1,now(),now()),
(7,27,1,now(),now()),
(7,28,1,now(),now()),
(5,6,1,now(),now()),
(5,7,1,now(),now()),
(5,8,1,now(),now()),
(5,9,1,now(),now()),
(5,10,1,now(),now()),
(5,11,1,now(),now()),
(5,12,1,now(),now()),
(5,13,1,now(),now()),
(5,14,1,now(),now()),
(5,15,1,now(),now()),
(5,16,1,now(),now()),
(5,17,1,now(),now()),
(5,18,1,now(),now()),
(5,19,1,now(),now()),
(5,20,1,now(),now()),
(5,21,1,now(),now()),
(5,22,1,now(),now()),
(5,23,1,now(),now()),
(5,24,1,now(),now()),
(5,25,1,now(),now()),
(5,26,1,now(),now()),
(5,27,1,now(),now()),
(5,28,1,now(),now()),
(14,6,1,now(),now()),
(14,7,1,now(),now()),
(14,8,1,now(),now()),
(14,9,1,now(),now()),
(14,10,1,now(),now()),
(14,11,1,now(),now()),
(14,12,1,now(),now()),
(14,13,1,now(),now()),
(14,14,1,now(),now()),
(14,15,1,now(),now()),
(14,16,1,now(),now()),
(14,17,1,now(),now()),
(14,18,1,now(),now()),
(14,19,1,now(),now()),
(14,20,1,now(),now()),
(14,21,1,now(),now()),
(14,22,1,now(),now()),
(14,23,1,now(),now()),
(14,24,1,now(),now()),
(14,25,1,now(),now()),
(14,26,1,now(),now()),
(14,27,1,now(),now()),
(14,28,1,now(),now()),
(10,6,1,now(),now()),
(10,7,1,now(),now()),
(10,17,1,now(),now()),
(15,6,1,now(),now()),
(15,7,1,now(),now()),
(15,17,1,now(),now()),
(9,6,1,now(),now()),
(9,7,1,now(),now()),
(9,8,1,now(),now()),
(9,9,1,now(),now()),
(9,10,1,now(),now()),
(9,11,1,now(),now()),
(9,12,1,now(),now()),
(9,13,1,now(),now()),
(9,14,1,now(),now()),
(9,15,1,now(),now()),
(9,16,1,now(),now()),
(9,17,1,now(),now()),
(9,18,1,now(),now()),
(9,19,1,now(),now()),
(9,20,1,now(),now()),
(9,21,1,now(),now()),
(9,22,1,now(),now()),
(9,23,1,now(),now()),
(9,24,1,now(),now()),
(9,25,1,now(),now()),
(9,26,1,now(),now()),
(9,27,1,now(),now()),
(9,28,1,now(),now()),
(8,8,1,now(),now()),
(8,9,1,now(),now()),
(8,10,1,now(),now()),
(8,11,1,now(),now()),
(8,12,1,now(),now()),
(8,13,1,now(),now()),
(8,14,1,now(),now()),
(8,15,1,now(),now()),
(8,16,1,now(),now()),
(8,28,1,now(),now()),;
-- -------------------------------------
-- 2017-08-03 孙宁海 权限配置相关  end
-- -------------------------------------

-- ----------------------------
-- 2017-08-28 李星 跟随式编程房间表 START
-- ----------------------------
-- ----------------------------
-- Table programming_live_room
-- ----------------------------

CREATE TABLE `gxb_core`.`programming_live_room` (
  `room_id` bigint(10) NOT NULL AUTO_INCREMENT COMMENT '房间id',
  `user_id` bigint(10) NOT NULL COMMENT '用户id',
  `language_id` int(10) COMMENT '编程语言id',
  `created_at` datetime,
  `updated_at` datetime,
  PRIMARY KEY (`room_id`)
) COMMENT= '跟随式编程用户房间id';

-- -------------------------------------
-- 2017-08-28 李星 跟随式编程房间表  end
-- -------------------------------------

-- ----------------------------
-- 2017-08-30 
-- 罗銅 刘岱鹏
-- 代码版本 feature/fix170831
-- 脚本版本 201708301449
-- 涉及功能 首页增加模版 增加师资管理 添加学校课程
-- START
-- ----------------------------
alter table zygj_classroom add enter_year varchar(10) not null default '' comment '入学年份';
alter table zygj_teacher modify teacher_type tinyint(2) NOT NULL DEFAULT '0' COMMENT '师资类型,1:讲师-企业老师，2:讲师-学校老师,3:全职讲师';
update zygj_teacher set teacher_type = 3 where job_type = 1;
alter table zygj_teacher add teacher_level tinyint(2) NOT NULL DEFAULT '0' COMMENT '师资级别,1:实习，2:初级，3:高级，4:金牌，5:特级，6:特约';
alter table zygj_teacher add address varchar(255) NOT NULL DEFAULT '' COMMENT '详细地址';
alter table zygj_teacher add `credentials_photo_url` varchar(255) NOT NULL DEFAULT '' COMMENT '证件照片url,格式 jpg、png';
alter table zygj_teacher add `credentials_photo_name` varchar(255) not null default '' comment '证件照片名称';

update zygj_teacher set company = concat(company,' ',position) where position != '';

CREATE TABLE `zygj_teacher_education` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增id',
  teacher_id bigint(20) not null default '0' comment '专业共建老师id',
  type tinyint(2) not null default '1' comment '学历类型，最近学历：1，第一学历：2',
  school varchar(100) not null default '' comment '毕业院校',
  major varchar(100) not null default '' comment '专业',
    education tinyint(2) NOT NULL DEFAULT '0' COMMENT '最近学历 (1 博士、2 硕士、3 本科、4 大专、5 高中)',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `delete_flag` tinyint(2) NOT NULL DEFAULT '1' COMMENT '1 正常，0 删除',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='老师学历表';

alter table zygj_teacher add linkman_mobile varchar(50) NOT NULL DEFAULT '' COMMENT '推荐人联系方式';
alter table zygj_teacher add contract_start_time datetime DEFAULT NULL COMMENT '合同开始时间';
alter table zygj_teacher add contract_end_time datetime DEFAULT NULL COMMENT '合同结束时间';
alter table zygj_teacher add contract_photo_url varchar(255) NOT NULL DEFAULT '' COMMENT '合同照片url';
alter table zygj_teacher add contract_photo_name varchar(255) NOT NULL DEFAULT '' COMMENT '合同照片名称';
alter table zygj_teacher add card_photo_url varchar(255) NOT NULL DEFAULT '' COMMENT '身份证照片url';
alter table zygj_teacher add card_photo_name varchar(255) NOT NULL DEFAULT '' COMMENT '身份证照片名称';
alter table zygj_teacher add benchmarkc_condition varchar(255) NOT NULL DEFAULT '' COMMENT '课酬约束条件';
alter table zygj_teacher add bank_card varchar(255) NOT NULL DEFAULT '' COMMENT '银行卡号';
alter table zygj_teacher add open_bank varchar(255) NOT NULL DEFAULT '' COMMENT '开户行';
alter table zygj_teacher add open_bank_address varchar(255) NOT NULL DEFAULT '' COMMENT '开户网点';
alter table zygj_teacher add replace_name varchar(255) NOT NULL DEFAULT '' COMMENT '代收款人姓名';
alter table zygj_teacher add replace_bank_card varchar(255) NOT NULL DEFAULT '' COMMENT '代收款人银行卡号';
alter table zygj_teacher add replace_open_bank varchar(255) NOT NULL DEFAULT '' COMMENT '代收款人开户行';
alter table zygj_teacher add replace_open_bank_address varchar(255) NOT NULL DEFAULT '' COMMENT '代收款人开户网点';
alter table zygj_teacher modify  `introduction` mediumtext default null COMMENT '个人简介';
ALTER TABLE `zygj_teacher` CHANGE `created_at` `created_at` TIMESTAMP  NOT NULL  DEFAULT CURRENT_TIMESTAMP;

alter table zygj_teacher_class add judges varchar(100) NOT NULL DEFAULT '' COMMENT '认证评委名称';
alter table zygj_teacher_class add score float NOT NULL DEFAULT '0' COMMENT '认证评分';
alter table zygj_teacher_class add authenticate_time datetime DEFAULT null COMMENT '认证时间';

ALTER TABLE `zygj_class_material` CHANGE `created_at` `created_at` TIMESTAMP  NOT NULL  DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE `zygj_class_material` CHANGE `updated_at` `updated_at` TIMESTAMP  NOT NULL  DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;



# ------------
CREATE TABLE
    zygj_school_class
    (
        class_id bigint NOT NULL AUTO_INCREMENT,
        uuid VARCHAR(255) COLLATE utf8_general_ci NOT NULL COMMENT '视频上传小工具要用uuid',
        course_id bigint NOT NULL COMMENT '课程id',
        user_id bigint NOT NULL COMMENT '创建者id',
        editor_id bigint NOT NULL COMMENT '修改者id',
        tenant_id bigint COMMENT '租户id',
        from_tenant_id bigint COMMENT '购买租户id',
        class_name VARCHAR(255) COLLATE utf8_general_ci NOT NULL COMMENT '班次名称',
        intro VARCHAR(255) COLLATE utf8_general_ci COMMENT '班次简介',
        class_type VARCHAR(50) COLLATE utf8_general_ci DEFAULT 'Class' COMMENT
        '班次类型,MicroClass Class 微课 普通班次',
        level VARCHAR(255) COLLATE utf8_general_ci COMMENT '班次难度：10 易 20 中 30 难',
        use_type VARCHAR(255) COLLATE utf8_general_ci COMMENT '班次使用性质：10 自用 20 售卖',
        credit_score DECIMAL(5,2) COMMENT '学分',
        validate_type VARCHAR(255) COLLATE utf8_general_ci DEFAULT '30' COMMENT
        '校验类型: 10 其他用户 20 全校可选 30 授权 40 付费',
        teach_mode VARCHAR(255) COLLATE utf8_general_ci COMMENT
        '教学模式: 10 开放学习模式 20 传统学习模式 30 顺序学习模式 40 条件解锁模式',
        start_at DATETIME COMMENT '开课时间',
        conclude_at DATETIME COMMENT '结课时间',
        score_publish_day INT COMMENT '成绩公布时间（N天之后）',
        study_hour INT COMMENT '课程学时',
        experience_num INT COMMENT '试学的前N个视频',
        status VARCHAR(255) COLLATE utf8_general_ci COMMENT '状态: 10 待发布 20 预发布 30 已发布 40 关闭',
        delete_flag TINYINT COMMENT '1可用 0 不可用',
        created_at DATETIME,
        updated_at DATETIME,
        is_documented TINYINT(1) DEFAULT 0 COMMENT '是否归档',
        unlock_quiz_score INT DEFAULT 0 COMMENT '条件模式解锁条件,测验需要达到的分数',
        type VARCHAR(50) COLLATE utf8_general_ci DEFAULT 'Class' COMMENT 'SchoolClass',
        position bigint COMMENT '排序',
        is_top TINYINT COMMENT '是否置顶 -1:置顶,null:不置顶',
        is_move TINYINT DEFAULT 0 COMMENT '0 未移动数据 1 已移动数据',
        least_move_at DATETIME COMMENT '最早移动时间',
        dropping_self TINYINT DEFAULT 0,
        practice_hour INT COMMENT '实践课时',
        theory_hour INT COMMENT '理论课时',
        evaluation_mode TINYINT COMMENT '考核方式 1:线上考试 2:线下考试 3:答辩 4:大作业',
        version VARCHAR(255) COMMENT '版本',
        research_personnel VARCHAR(255) COMMENT '研发人员',
        from_class bigint COMMENT '班次复制售卖源',
        root_class bigint COMMENT '班次复制根源',
        PRIMARY KEY (class_id),
        INDEX index_course_id (course_id)
    )
    ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Class表映射表，表示学校自由课程，表结构跟class一致';

CREATE TABLE
    zygj_class_category
    (
        id bigint NOT NULL AUTO_INCREMENT COMMENT 'id',
        name VARCHAR(50) NOT NULL COMMENT '分类名称',
        level INT DEFAULT 1 NOT NULL COMMENT '层级',
        parent_id bigint NOT NULL COMMENT '父节点ID',
        created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
        updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
        delete_flag TINYINT DEFAULT 0 NOT NULL COMMENT '是否删除',
        tenant_id INT COMMENT '租户ID',
        user_id INT COMMENT '学生ID',
        PRIMARY KEY (id)
    )
    ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='班级分类';

ALTER TABLE `zygj_material` CHANGE `user_id` `user_id` BIGINT(20)  NOT NULL  COMMENT '添加人';
ALTER TABLE `zygj_material` CHANGE `delete_flag` `delete_flag` TINYINT(4)  NOT NULL  COMMENT '是否删除';
ALTER TABLE `zygj_material` CHANGE `updated_at` `updated_at` TIMESTAMP  NOT NULL  DEFAULT CURRENT_TIMESTAMP  ON UPDATE CURRENT_TIMESTAMP  COMMENT '更新时间';
ALTER TABLE `zygj_material` CHANGE `created_at` `created_at` TIMESTAMP  NOT NULL  DEFAULT CURRENT_TIMESTAMP  COMMENT '创建时间';
ALTER TABLE `zygj_material` CHANGE `remark` `remark` MEDIUMTEXT  CHARACTER SET utf8  COLLATE utf8_general_ci  NULL  COMMENT '备注';
ALTER TABLE `zygj_material` CHANGE `publish_at` `publish_at` DATE  NULL  DEFAULT NULL  COMMENT '出版时间';
ALTER TABLE `zygj_material` CHANGE `press` `press` VARCHAR(120)  CHARACTER SET utf8  COLLATE utf8_general_ci  NOT NULL  DEFAULT ''  COMMENT '出版社';
ALTER TABLE `zygj_material` CHANGE `isbn` `isbn` VARCHAR(120)  CHARACTER SET utf8  COLLATE utf8_general_ci  NOT NULL  DEFAULT ''  COMMENT 'ISBN号';
ALTER TABLE `zygj_material` CHANGE `author` `author` VARCHAR(120)  CHARACTER SET utf8  COLLATE utf8_general_ci  NOT NULL  DEFAULT ''  COMMENT '作者';
ALTER TABLE `zygj_material` CHANGE `name` `name` VARCHAR(120)  CHARACTER SET utf8  COLLATE utf8_general_ci  NOT NULL  DEFAULT ''  COMMENT '姓名';
ALTER TABLE `zygj_material` CHANGE `id` `id` BIGINT(20)  NOT NULL  AUTO_INCREMENT  COMMENT '主键ID';


INSERT INTO zygj_class_category (id, name, level, parent_id, created_at, updated_at, delete_flag, tenant_id, user_id) VALUES (1, '通识与公共基础课程', 1, 0, '2017-08-24 10:42:59', '2017-08-28 17:12:41', 1, null, null);
INSERT INTO zygj_class_category (id, name, level, parent_id, created_at, updated_at, delete_flag, tenant_id, user_id) VALUES (2, '思想政治类', 2, 1, '2017-08-24 10:42:59', '2017-08-28 17:15:25', 1, null, null);
INSERT INTO zygj_class_category (id, name, level, parent_id, created_at, updated_at, delete_flag, tenant_id, user_id) VALUES (3, '军事体育类', 2, 1, '2017-08-24 10:42:59', '2017-08-28 17:15:25', 1, null, null);
INSERT INTO zygj_class_category (id, name, level, parent_id, created_at, updated_at, delete_flag, tenant_id, user_id) VALUES (4, '通识类', 2, 1, '2017-08-24 10:42:59', '2017-08-28 17:15:25', 1, null, null);
INSERT INTO zygj_class_category (id, name, level, parent_id, created_at, updated_at, delete_flag, tenant_id, user_id) VALUES (5, '外语类', 2, 1, '2017-08-21 16:54:07', '2017-08-28 17:16:48', 1, null, null);
INSERT INTO zygj_class_category (id, name, level, parent_id, created_at, updated_at, delete_flag, tenant_id, user_id) VALUES (6, '数学类', 2, 1, '2017-08-21 16:54:07', '2017-08-28 17:16:48', 1, null, null);
INSERT INTO zygj_class_category (id, name, level, parent_id, created_at, updated_at, delete_flag, tenant_id, user_id) VALUES (7, '学科与专业基本课程', 1, 0, '2017-08-21 16:54:07', '2017-08-28 17:16:48', 1, null, null);
INSERT INTO zygj_class_category (id, name, level, parent_id, created_at, updated_at, delete_flag, tenant_id, user_id) VALUES (8, '学科基础（大类）平台课程', 2, 7, '2017-08-21 16:54:07', '2017-08-28 17:16:48', 1, null, null);
INSERT INTO zygj_class_category (id, name, level, parent_id, created_at, updated_at, delete_flag, tenant_id, user_id) VALUES (11, '专业基础课程', 2, 7, '2017-08-28 17:15:55', '2017-08-28 17:27:50', 1, null, null);
INSERT INTO zygj_class_category (id, name, level, parent_id, created_at, updated_at, delete_flag, tenant_id, user_id) VALUES (12, '专业与专业方向（模块）课程', 1, 0, '2017-08-28 17:15:55', '2017-08-28 17:27:50', 1, null, null);
INSERT INTO zygj_class_category (id, name, level, parent_id, created_at, updated_at, delete_flag, tenant_id, user_id) VALUES (13, '专业课程', 2, 12, '2017-08-28 17:15:55', '2017-08-28 17:27:50', 1, null, null);
INSERT INTO zygj_class_category (id, name, level, parent_id, created_at, updated_at, delete_flag, tenant_id, user_id) VALUES (14, '专业方向模块课程', 2, 12, '2017-08-28 17:15:55', '2017-08-28 17:27:50', 1, null, null);
INSERT INTO zygj_class_category (id, name, level, parent_id, created_at, updated_at, delete_flag, tenant_id, user_id) VALUES (15, '专业实验、实习、实践', 2, 12, '2017-08-28 17:15:55', '2017-08-28 17:27:50', 1, null, null);
INSERT INTO zygj_class_category (id, name, level, parent_id, created_at, updated_at, delete_flag, tenant_id, user_id) VALUES (16, '专业实训', 2, 12, '2017-08-28 17:15:55', '2017-08-28 17:27:50', 1, null, null);
INSERT INTO zygj_class_category (id, name, level, parent_id, created_at, updated_at, delete_flag, tenant_id, user_id) VALUES (17, '毕业设计（论文）', 2, 12, '2017-08-28 17:15:55', '2017-08-28 17:27:50', 1, null, null);
INSERT INTO zygj_class_category (id, name, level, parent_id, created_at, updated_at, delete_flag, tenant_id, user_id) VALUES (18, '交叉与个性发展课程', 1, 0, '2017-08-28 17:15:55', '2017-08-28 17:27:50', 1, null, null);
INSERT INTO zygj_class_category (id, name, level, parent_id, created_at, updated_at, delete_flag, tenant_id, user_id) VALUES (19, '跨学科课程', 2, 18, '2017-08-28 17:15:55', '2017-08-28 17:27:50', 1, null, null);
INSERT INTO zygj_class_category (id, name, level, parent_id, created_at, updated_at, delete_flag, tenant_id, user_id) VALUES (20, '创新创业训练计划', 2, 18, '2017-08-28 17:15:55', '2017-08-28 17:27:50', 1, null, null);
INSERT INTO zygj_class_category (id, name, level, parent_id, created_at, updated_at, delete_flag, tenant_id, user_id) VALUES (21, '个性选修课', 2, 18, '2017-08-28 17:15:55', '2017-08-28 17:27:50', 1, null, null);
INSERT INTO zygj_class_category (id, name, level, parent_id, created_at, updated_at, delete_flag, tenant_id, user_id) VALUES (22, '第二课堂', 1, 0, '2017-08-28 17:15:55', '2017-08-28 17:27:50', 1, null, null);
INSERT INTO zygj_class_category (id, name, level, parent_id, created_at, updated_at, delete_flag, tenant_id, user_id) VALUES (23, '社会实践', 2, 22, '2017-08-28 17:15:55', '2017-08-28 17:27:50', 1, null, null);
INSERT INTO zygj_class_category (id, name, level, parent_id, created_at, updated_at, delete_flag, tenant_id, user_id) VALUES (24, '健康教育', 2, 22, '2017-08-28 17:15:55', '2017-08-28 17:27:50', 1, null, null);
INSERT INTO zygj_class_category (id, name, level, parent_id, created_at, updated_at, delete_flag, tenant_id, user_id) VALUES (25, '讲座、两组学习、劳动、社团活动等', 2, 22, '2017-08-28 17:15:55', '2017-08-28 17:27:50', 1, null, null);


ALTER TABLE `zygj_solution_class` ADD  `practice_hour` INT(11)  NOT NULL  DEFAULT '0'  COMMENT '实践学时，小时为单位';
ALTER TABLE `zygj_solution_class` ADD  `theory_hour` INT(11)  NOT NULL  DEFAULT '0'  COMMENT '理论学时，小时为单位';
ALTER TABLE `zygj_solution_class` ADD  `class_target` VARCHAR(20)  CHARACTER SET utf8  COLLATE utf8_general_ci  NOT NULL  DEFAULT '0'  COMMENT 'prefession,class 慧科课程 SchoolClass 学校自建课程';
ALTER TABLE `zygj_solution_class` ADD `category_id` BIGINT(20)  NOT NULL  DEFAULT '0'  COMMENT '课程分类，二级分类ID';

ALTER TABLE `zygj_class_schedule` ADD `class_target` VARCHAR(20)  CHARACTER SET utf8  COLLATE utf8_general_ci  NOT NULL  DEFAULT '0'  COMMENT '课程类型';

ALTER TABLE `zygj_class_schedule` ADD (teacher_name VARCHAR(50) COLLATE utf8_general_ci COMMENT '学校课程老师名称');

# ------------
CREATE TABLE `zygj_major` (
  `major_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `product_direction` int(11) NOT NULL DEFAULT '0' COMMENT '产品方向，1:云计算、2:大数据技术与应用、3:智能硬件、4:虚拟现实（VR）、5:互联网产品设计、6:互联网营销、7:互联网金融、8:网络空间安全、9:人工智能、10:互联网医疗、11:互联网教育、12:智能制造',
  `name` varchar(60) COLLATE utf8mb4_bin NOT NULL COMMENT '专业名称',
  `synopsis` text COLLATE utf8mb4_bin NOT NULL COMMENT '专业简介',
  `cover` text CHARACTER SET utf8mb4 COMMENT '封面',
  `tenant_id` bigint(20) DEFAULT '0' COMMENT '所属学校id',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `delete_flag` tinyint(2) NOT NULL DEFAULT '1',
  PRIMARY KEY (`major_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='合作专业表';
-- ----------------------------
-- 2017-08-30 罗銅 高校邦Y-迭代1.6相关 END
-- ----------------------------




-- ----------------------------
-- 2017-08-30
-- 罗銅 刘岱鹏
-- 代码版本 feature/fix170831
-- 脚本版本 201708301623
-- 涉及功能 feature/fix170831版本，sql脚本修复
-- START
-- ----------------------------

update zygj_solution_class,class set zygj_solution_class.class_target=class.type where zygj_solution_class.class_target!='SchoolClass' and zygj_solution_class.class_id=class.class_id;
alter table zygj_teacher modify city varchar(50) NOT NULL DEFAULT '' COMMENT '城市';

-- ----------------------------
-- 2017-08-30 罗銅 脚本版本 201708301623 END
-- ----------------------------

-- ----------------------------
-- 2017-08-30
-- 罗銅 刘岱鹏
-- 代码版本 feature/fix170831
-- 脚本版本 201708301735
-- 涉及功能 feature/fix170831版本，sql脚本修复
-- START
-- ----------------------------

ALTER TABLE `zygj_teacher` CHANGE `benchmarkc_condition` `benchmark_condition` VARCHAR(255)  CHARACTER SET utf8  COLLATE utf8_general_ci  NOT NULL  DEFAULT ''  COMMENT '课酬约束条件';

-- ----------------------------
-- 2017-08-30 罗銅 脚本版本 201708301735 END
-- ----------------------------


-- ----------------------------
-- 2017-08-31
-- 罗銅 刘岱鹏
-- 代码版本 feature/fix170831
-- 脚本版本 201708311952
-- 涉及功能 feature/fix170831版本，sql脚本修复
-- 执行环境 予发布和灰度
-- START
-- ----------------------------

alter table zygj_solution add enter_year varchar(10) not null default '' comment '入学年份';

-- ----------------------------
-- 2017-08-31 罗銅 脚本版本 201708311952 END
-- ----------------------------