uu-platform/db/message-schema.sql

create table if not exists `message_config`
(
id int unsigned primary key not null auto_increment,
code varchar(30) comment '唯一编码',
order_num int comment '顺序',
name varchar(100) comment '消息名',
icon varchar(100) comment '图标',
view_type int default 0 comment '视图类型 0 - widget, 1 - web',
android_widget varchar(100) comment 'android控件',
ios_widget varchar(100) comment 'ios控件',
web_url varchar(300) comment '网页',
scope_platform int default 0 comment '专属平台的应用 0 - ALL',
scope_company_id bigint comment '专属企业的应用',
company_relate int comment '与公司相关的类型 0 - COMPANY_OR_PERSONAL'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='消息配置';

create table if not exists `message_company`
(
config_id int not null,
company_id bigint not null
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='企业绑定消息配置';

create table if not exists `message_unread`
(
id bigint unsigned primary key not null auto_increment,
code varchar(30) not null comment 'message_config.code',
body varchar(1000) comment '消息内容',
account_id bigint unsigned not null comment 'ac_account.id',
company_id bigint comment 'ac_company.id',
create_time datetime comment '创建时间',
push_time datetime comment '推送到客户端时间',
push_error_code int comment '推送相关错误码',
push_error_msg varchar(500) comment '推送相关错误信息'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='未读消息';

create table if not exists `message_history`
(
id bigint unsigned primary key not null,
code varchar(30) not null comment 'message_config.code',
body varchar(1000) comment '消息内容',
account_id bigint unsigned not null comment 'ac_account.id',
company_id bigint comment 'ac_company.id',
create_time datetime comment '创建时间',
push_time datetime comment '推送到客户端时间',
push_error_code int comment '推送相关错误码',
push_error_msg varchar(500) comment '推送相关错误信息',
read_time datetime comment '阅读时间',
read_platform int comment '在哪个平台/系统阅读的'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='已读消息';

drop procedure if exists `p_message_all_read`;
DELIMITER ;;
create procedure `p_message_all_read`
(
in i_account_id bigint,
in i_platform int
)
begin
  insert into message_history(id,code,body,account_id,company_id,create_time,push_time,push_error_code,
  push_error_msg,read_time,read_platform)
  select id,code,body,account_id,company_id,create_time,push_time,push_error_code,push_error_msg,sysdate(),
  i_platform from message_unread where account_id=i_account_id;
  delete from message_unread where account_id=i_account_id;
  commit;
end
;;
DELIMITER ;