6.0 升级至 6.5
/* 2016-02-23 模型字段自定义表 编辑器工具条 Cms 改为 Standard */
update cms_model_field_custom set f_value='Standard' where f_key='toolbar' and f_value='Cms';
update cms_model_field_custom set f_key='editorToolbar' where f_key='toolbar';
update cms_model_field_custom set f_key='imageExact' where f_key='exact';
/* 2016-03-31 用户表。微信open id */
alter table cms_user add f_weixin_openid varchar(64) comment 'weixin openid';
/* 2016-06-05 采集表。增加是否允许重复标题 */
alter table cms_collect add f_is_allow_duplicate char(1) not null default '0' comment '是否允许重复标题';
/* 2016-07-05 版本升级为6.5.0 */
update cms_global set f_version = '6.5.0';
6.5 升级至 7.0
/* 2016-08-08 用户表。rank默认值设置为99999 */
alter table cms_user modify f_rank int not null default 99999 comment '等级';
/* 2016-10-08 用户扩展表。删除 f_validation_value字段 */
alter table cms_user_detail drop f_validation_value;
/* 2016-10-14 角色表。增加等级字段 */
alter table cms_role add f_rank int not null default 999 comment '等级';
/* 2016-10-17 采集表。增加是否采集正文中的图片字段 */
alter table cms_collect add f_is_download_image char(1) not null default '1' comment '是否采集正文中的图片';
/* 2016-10-21 访问日志表。增加用户代理、国家、地区字段 */
alter table cms_visit_log add f_user_agent varchar(450) comment '用户代理';
alter table cms_visit_log add f_country varchar(100) comment '国家';
alter table cms_visit_log add f_area varchar(100) comment '地区';
alter table cms_visit_log add f_browser varchar(100) comment '浏览器';
alter table cms_visit_log add f_os varchar(100) comment '操作系统';
alter table cms_visit_log add f_device varchar(100) comment '设备(COMPUTER,MOBILE,TABLET,等)';
/* 2016-10-21 访问日志表。f_date改为f_time_string */
alter table cms_visit_log drop column f_date;
alter table cms_visit_log add f_time_string char(14) not null comment '访问时间（字符串格式yyyyMMddHHmmss）';
update cms_visit_log set f_time_string=DATE_FORMAT(f_time,'%Y%m%d%H%i%S');
/* 2016-10-21 访问日志表。删除f_ip_date、f_cookie_date字段 */
alter table cms_visit_log drop column f_ip_date;
alter table cms_visit_log drop column f_cookie_date;
/* 2016-10-21 访问日志表。f_ip_date的日期格式去掉'-' */
/*
update cms_visit_log set f_ip_date=CONCAT(f_ip,DATE_FORMAT(f_time,'%Y%m%d'));
update cms_visit_log set f_cookie_date=CONCAT(f_cookie,DATE_FORMAT(f_time,'%Y%m%d'));
*/
/* 2016-10-25 访问日志表。增加来源域名 */
alter table cms_visit_log add f_source varchar(100) comment '来源域名';
update cms_visit_log set f_source='DIRECT';
update cms_visit_log set f_country='UNKNOWN';
update cms_visit_log set f_area='UNKNOWN';
update cms_visit_log set f_browser='UNKNOWN';
update cms_visit_log set f_os='UNKNOWN';
update cms_visit_log set f_device='UNKNOWN';
/* 2016-10-31 访问日志表。增加访问用户字段 */
alter table cms_visit_log add f_user_id int comment '访问用户';
alter table cms_visit_log add constraint fk_cms_visitlog_user foreign key (f_user_id)
references cms_user (f_user_id) on delete restrict on update restrict;
/* 2016-10-31 操作日志表。增加国家、地区字段 */
alter table cms_operation_log add f_country varchar(100) comment '国家';
alter table cms_operation_log add f_area varchar(100) comment '地区';
/* 2016-10-31 留言板表。增加国家、地区字段 */
alter table cms_guestbook add f_creation_country varchar(100) comment '留言国家';
alter table cms_guestbook add f_creation_area varchar(100) comment '留言地区';
alter table cms_guestbook add f_reply_country varchar(100) comment '回复国家';
alter table cms_guestbook add f_reply_area varchar(100) comment '回复地区';
/* 2016-10-31 评论表。增加国家、地区字段 */
alter table cms_comment add f_country varchar(100) comment '国家';
alter table cms_comment add f_area varchar(100) comment '地区';
/* 2016-12-06 文档与组织权限表。去除主键 */
alter table cms_info_org drop f_infoorg_id;
/* 2016-12-06 文档与会员组权限表。去除主键 */
alter table cms_info_membergroup drop f_infomgroup_id;
/* 2016-12-06 文档与属性关联表。去除主键 */
alter table cms_info_attribute drop f_infoattr_id;
/* 2016-12-06 文档与专题关联表。去除主键 */
alter table cms_info_special drop f_infospecial_id;
/* 2016-12-06 文档与栏目关联表。去除主键 */
alter table cms_info_node drop f_infonode_id;
/* 2016-12-06 文档与tag关联表。去除主键 */
alter table cms_info_tag drop f_infotag_id;
/* 2016-12-06 栏目与组织权限表。去除主键 */
alter table cms_node_org drop f_nodeorg_id;
/* 2016-12-06 栏目与角色权限表。去除主键 */
alter table cms_node_role drop f_noderole_id;
/* 2016-12-06 栏目与用户组权限表。去除主键 */
alter table cms_node_membergroup drop f_nodemgroup_id;
/* 2016-12-06 用户与角色关联表。去除主键 */
alter table cms_user_role drop f_userrole_id;
/* 2016-12-06 用户与会员组关联表。去除主键 */
alter table cms_user_membergroup drop f_usermgroup_id;
/* 2016-12-06 用户与组织关联表。去除主键 */
alter table cms_user_org drop f_userorg_id;
/* 2016-12-06 审核步骤与角色关联表。去除主键 */
alter table cms_workflowstep_role drop f_wfsteprole_id;
/* 2016-12-06 调查问卷选项与调查问卷记录关联表。去除主键 */
alter table cms_question_opt_rec drop f_questionoptrec_id;
/* 2016-12-06 调查问卷项与调查问卷记录关联表。去除主键 */
alter table cms_question_item_rec drop f_questionitemrec_id;
delete from t_id_table where f_table='cms_info_org' or f_table='cms_info_membergroup' or f_table='cms_info_attribute' or f_table='cms_info_special';
delete from t_id_table where f_table='cms_info_node' or f_table='cms_info_tag' or f_table='cms_node_org' or f_table='cms_node_role';
delete from t_id_table where f_table='cms_node_membergroup' or f_table='cms_user_role' or f_table='cms_user_membergroup' or f_table='cms_user_org';
delete from t_id_table where f_table='cms_workflowstep_role' or f_table='cms_question_opt_rec' or f_table='cms_question_item_rec';
alter table cms_info_node modify f_node_index int default 0 comment '栏目顺序';
alter table cms_info_tag modify f_tag_index int default 0 comment 'tag序号';
alter table cms_info_special modify f_special_index int default 0 comment '专题序号';
alter table cms_user_role modify f_role_index int default 0 comment '角色顺序';
alter table cms_user_org modify f_org_index int default 0 comment '组织顺序';
alter table cms_user_membergroup modify f_group_index int default 0 comment '会员组排列顺序';
alter table cms_workflowstep_role modify f_role_index int default 0 comment '角色排列顺序';
/* 用户对应跨站角色，可能因为f_role_index重复导致失去权限，通过创建临时表重新设置f_role_index的值 */
create table cms_user_role_temp(
f_user_id int not null,
f_role_id int not null
);
insert into cms_user_role_temp (f_user_id,f_role_id) select f_user_id,f_role_id from cms_user_role;
update cms_user_role t set f_role_index = (select count(*) from cms_user_role_temp sub where sub.f_user_id=t.f_user_id and sub.f_role_id<t.f_role_id);
drop table cms_user_role_temp;
/* 用户对应栏目，可能因为f_node_index为-1导致报错，通过创建临时表重新设置f_node_index的值 */
create table cms_info_node_temp(
f_info_id int not null,
f_node_id int not null
);
insert into cms_info_node_temp (f_info_id,f_node_id) select f_info_id,f_node_id from cms_info_node;
update cms_info_node t set f_node_index = (select count(*) from cms_info_node_temp sub where sub.f_info_id=t.f_info_id and sub.f_node_id<t.f_node_id);
drop table cms_info_node_temp;
/* 2016-11-09 新增私信表 */
create table cms_message
(
message_id_ int not null comment '私信',
sender_id_ int not null comment '发送人',
receiver_id_ int not null comment '收件人',
send_time_ datetime not null comment '发送时间',
deletion_flag_ int not null default 0 comment '删除标志(0:正常;1:发件人删除;2:收件人删除;)',
is_unread_ int not null default 1 comment '是否未读',
primary key (message_id_)
)
engine = innodb;
alter table cms_message comment '私信表';
alter table cms_message add constraint fk_cms_message_receiver foreign key (receiver_id_)
references cms_user (f_user_id) on delete restrict on update restrict;
alter table cms_message add constraint fk_cms_message_sender foreign key (sender_id_)
references cms_user (f_user_id) on delete restrict on update restrict;
/* 2016-11-09 新增私信正文表 */
create table cms_message_text
(
message_id_ int not null comment '私信',
subject_ varchar(150) comment '标题',
text_ mediumtext not null comment '内容',
primary key (message_id_)
)
engine = innodb;
alter table cms_message_text comment '私信正文表';
alter table cms_message_text add constraint fk_cms_messagetext_message foreign key (message_id_)
references cms_message (message_id_) on delete restrict on update restrict;
/* 2016-12-26 版本升级为7.0.0 */
update cms_global set f_version = '7.0.0';
7.0 升级至 8.0
/* 2017-01-18 站点表。删除是否默认站点字段。ID为1的站点为默认站点 */
alter table cms_site drop column f_is_def;
/* 2017-01-08 删除之前旧表*/
drop table if exists cms_mail_inbox;
drop table if exists cms_mail_outbox;
drop table if exists cms_mail_text;
/* 2017-01-08 系统消息正文表*/
create table cms_mail_text
(
mailtext_id_ int not null,
subject_ varchar(150) comment '标题',
text_ mediumtext not null comment '内容',
primary key (mailtext_id_)
)
engine = innodb;
alter table cms_mail_text comment '系统消息正文表';
/* 2017-01-08 系统消息发件表*/
create table cms_mail_outbox
(
mailoutbox_id_ int not null,
mailtext_id_ int not null,
sender_id_ int comment '发送人',
send_time_ datetime not null comment '发送时间',
receiver_number_ int not null comment '接收人数',
read_number_ int not null comment '已读人数',
primary key (mailoutbox_id_)
)
engine = innodb;
alter table cms_mail_outbox comment '系统消息发件表';
alter table cms_mail_outbox add constraint fk_cms_mailoutbox_sender foreign key (sender_id_)
references cms_user (f_user_id) on delete restrict on update restrict;
alter table cms_mail_outbox add constraint fk_cms_mailoutbox_text foreign key (mailtext_id_)
references cms_mail_text (mailtext_id_) on delete restrict on update restrict;
/* 2017-01-08 系统消息收件表*/
create table cms_mail_inbox
(
mailinbox_id_ int not null,
mailoutbox_id_ int not null,
mailtext_id_ int not null,
sender_id_ int comment '发件人',
receiver_id_ int not null comment '接收人',
receive_time_ datetime not null comment '接收时间',
is_unread_ int not null default 1 comment '是否未读',
primary key (mailinbox_id_)
)
engine = innodb;
alter table cms_mail_inbox comment '系统消息收件表';
alter table cms_mail_inbox add constraint fk_cms_mailinbox_outbox foreign key (mailoutbox_id_)
references cms_mail_outbox (mailoutbox_id_) on delete restrict on update restrict;
alter table cms_mail_inbox add constraint fk_cms_mailinbox_receiver foreign key (receiver_id_)
references cms_user (f_user_id) on delete restrict on update restrict;
alter table cms_mail_inbox add constraint fk_cms_mailinbox_sender foreign key (sender_id_)
references cms_user (f_user_id) on delete restrict on update restrict;
alter table cms_mail_inbox add constraint fk_cms_mailinbox_text foreign key (mailtext_id_)
references cms_mail_text (mailtext_id_) on delete restrict on update restrict;
/* 2017-01-15 新增通知表（先删除之前旧表）*/
drop table cms_notification;
create table cms_notification
(
notification_id_ int not null,
receiver_id_ int not null comment '接收者',
type_ varchar(50) not null comment '类别',
key_ int not null comment 'KEY',
qty_ int not null default 0 comment '数量',
url_ varchar(255) comment 'URL',
backend_url_ varchar(255) comment '后端URL',
send_time_ datetime not null comment '通知时间',
text_ varchar(2000) not null comment '通知正文',
primary key (notification_id_)
)
engine = innodb;
alter table cms_notification comment '通知表';
alter table cms_notification add constraint fk_cms_notification_receiver foreign key (receiver_id_)
references cms_user (f_user_id) on delete restrict on update restrict;
create table cms_notification_source
(
notification_id_ int not null,
source_ varchar(255) not null,
source_order_ int not null
)
engine = innodb;
alter table cms_notification_source comment '通知来源表';
alter table cms_notification_source add constraint fk_cms_notificationkey_notifi foreign key (notification_id_)
references cms_notification (notification_id_) on delete restrict on update restrict;
/* 2017-01-15 文档表。增加搜藏次数字段 */
alter table cms_info add f_favorites int not null default 0 comment '收藏次数';
/* 2017-01-15 新增收藏表。 */
create table cms_favorite
(
favorite_id_ int not null,
user_id_ int not null comment '收藏用户',
dtype_ varchar(50) not null comment '收藏数据类型',
did_ int not null comment '收藏数据ID',
created_ datetime not null comment '收藏时间',
primary key (favorite_id_)
)
engine = innodb;
alter table cms_favorite comment '收藏表';
alter table cms_favorite add constraint fk_reference_148 foreign key (user_id_)
references cms_user (f_user_id) on delete restrict on update restrict;
/* 2017-02-23 主键表t_id_table改为默认的hibernate_sequences。使用默认的方式，方便不熟悉的用户使用 */
create table hibernate_sequences
(
sequence_name varchar(100) not null comment '表名',
next_val bigint not null comment 'ID值',
primary key (sequence_name)
)
engine = innodb;
alter table hibernate_sequences comment '主键表';
insert into hibernate_sequences (sequence_name,next_val) select f_table,f_id_value from t_id_table;
drop table t_id_table;
/* 2017-03-13 新的主键生成方式会使用next_val-allocationSize的值，需要全部加上allocationSize*2，allocationSize已经改为10，需要加20。cms_visit_log的allocationSize设置为50，需要加100 */
update hibernate_sequences set next_val=next_val+20;
update hibernate_sequences set next_val=next_val+100 where sequence_name='cms_visit_log';
/* 2017-03-27 新增用户自定义表 */
create table cms_user_custom
(
f_user_id int,
f_key varchar(50) not null comment '键',
f_value varchar(2000) comment '值'
)
engine = innodb;
alter table cms_user_custom comment '用户自定义表';
alter table cms_user_custom add constraint fk_cms_usercustom_user foreign key (f_user_id)
references cms_user (f_user_id) on delete restrict on update restrict;
/* 2017-03-27 新增用户大字段表 */
create table cms_user_clob
(
f_user_id int,
f_key varchar(50) not null comment '键',
f_value mediumtext comment '值'
)
engine = innodb;
alter table cms_user_clob comment '用户大字段表';
alter table cms_user_clob add constraint fk_cms_userclob_user foreign key (f_user_id)
references cms_user (f_user_id) on delete restrict on update restrict;
/* 2017-03-31 增加手机端功能 */
alter table cms_site add f_mobile_theme varchar(100) default 'default' comment '手机端模板主题';
alter table cms_site add f_mobile_domain varchar(100) comment '手机端域名';
alter table cms_site add f_mobile_publishpoint_id int comment '手机端发布点';
alter table cms_site add constraint fk_cms_site_publishpoint_m foreign key (f_mobile_publishpoint_id)
references cms_publish_point (f_publishpoint_id) on delete restrict on update restrict;
update cms_site set f_mobile_publishpoint_id=f_html_publishpoint_id;
alter table cms_info_detail add f_mobile_html varchar(255) comment '手机端HTML页面';
alter table cms_node_detail add f_mobile_html varchar(255) comment '手机端HTML页面';
/* 2017-04-06 全局表。取消url带域名 */
alter table cms_global drop f_is_with_domain;
/* 2017-04-06 版本升级为8.0.0 */
update cms_global set f_version = '8.0.0';
8.0 升级至 9.0
/* 8.0 升级至 9.0 */
/* 2017-05-29 投票表。删除f_seq排序，增加创建日期 */
alter table cms_vote add f_creation_date datetime not null comment '创建时间';
update cms_vote set f_creation_date=now();
alter table cms_vote drop column f_seq;
/* 2017-08-05 文档推送表 */
create table cms_info_push
(
infopush_id_ int not null,
info_id_ int not null comment '推送文档',
from_site_id_ int not null comment '推送源站点',
to_site_id_ int not null comment '推送目标站点',
user_id_ int not null comment '推送人',
created_ datetime not null comment '推送时间',
primary key (infopush_id_)
) engine = innodb;
alter table cms_info_push comment '文档推送表';
alter table cms_info_push add constraint fk_cms_infopush_info foreign key (info_id_) references cms_info (f_info_id) on delete restrict on update restrict;
alter table cms_info_push add constraint fk_cms_infopush_site_from foreign key (from_site_id_) references cms_site (f_site_id) on delete restrict on update restrict;
alter table cms_info_push add constraint fk_cms_infopush_site_to foreign key (to_site_id_) references cms_site (f_site_id) on delete restrict on update restrict;
alter table cms_info_push add constraint fk_cms_infopush_user foreign key (user_id_) references cms_user (f_user_id) on delete restrict on update restrict;
/* 2017-08-05 文档表。新增是否微信群发字段 */
alter table cms_info_detail add f_is_weixin_mass char(1) not null default '0' comment '是否微信群发';
update cms_info_detail set f_is_weixin_mass = '0';
/* 2017-08-05 文档表。新增推送来源字段 */
alter table cms_info add f_from_site_id int comment '推送来源站点';
alter table cms_info add constraint fk_cms_info_site_from foreign key (f_from_site_id) references cms_site (f_site_id) on delete restrict on update restrict;
/* 2017-05-14 版本升级为9.0.0 */
update cms_global set f_version = '9.0.0';
/* 2019-08-03 版本升级为9.5.0 */
update cms_global set f_version = '9.5.1';
/* 2020-10-14 版本升级为10.0.0 */
update cms_global set f_version = '10.0.0';
9.0 至 10.0 无变化
10.0 升级至 10.1
/* 10.0 升级至 10.1 */
/* 2020-10-19 新增访问统计表 */
create table cms_visit_stat
(
visitstat_id_ int not null comment '访问统计ID',
site_id_ int not null comment '站点ID',
type_ int not null comment '类型(1:访问量,2:来源,3:受访,4:地域,5:浏览器,6:操作系统,7:设备)',
period_ int not null comment '时期(1:按日统计,2:按月统计)',
name_ varchar(255) not null comment '名称',
date_ datetime not null comment '访问日期',
views_ int not null default 0 comment '访问次数',
unique_views_ int not null default 0 comment '独立访客',
ip_views_ int not null default 0 comment 'ip访客',
primary key (visitstat_id_)
) engine = innodb;
alter table cms_visit_stat comment '访问统计表';
create index idx_cms_visitstat_type on cms_visit_stat(type_);
alter table cms_visit_stat add constraint fk_cms_visitstat_site foreign key (site_id_) references cms_site (f_site_id) on delete restrict on update restrict;
/* 2020-12-19 版本升级为10.1 */
update cms_global set f_version = '10.1.0';