最代碼網站中關於動態表event的設計思路

原文：最代碼網站中關於動態表event的設計思路

為了能將最代碼整站使用者的操作都展現出來，需要設計一種動態類型，既可以根據業務無限擴充，也可以指定某些使用者行為是可以產生多少牛幣交換的，這樣就在原先javaniu的零散的表設計基礎上抽象出event表

表結構如下：

CREATE TABLE `javaniu_event` (  `id` bigint(20) unsigned NOT NULL auto_increment,  `create_time` datetime NOT NULL,  `update_time` datetime default NULL,  `event_rule_id` bigint(20) NOT NULL default '0' COMMENT '使用者註冊\r\n下載代碼\r\n瀏覽分享\r\n瀏覽尋求\r\n收藏分享\r\n收藏尋求\r\n瀏覽活動\r\n追加懸賞\r\n加入活動\r\n拜師\r\n關注使用者\r\n發表心情\r\n發表尋求\r\n評論尋求\r\n評論代碼\r\n上傳代碼\r\n下載代碼\r\n分享代碼\r\n關注使用者\r\n瀏覽分享\r\n瀏覽尋求\r\n管理員刪除代碼\r\n收藏分享\r\n收藏尋求\r\n擷取勳章\r\n拜師傅\r\n發起活動\r\n瀏覽活動\r\n加入活動\r\n追加懸賞\r\n連續一周發表心情\r\n使用者周貢獻排行\r\n使用者月貢獻排行\r\n使用者年貢獻排行\r\n代碼下載周排行\r\n代碼下載月排行\r\n代碼下載年排行',  `user_id` bigint(20) NOT NULL default '0',  `source_user_id` bigint(20) NOT NULL default '0',  `source_id` bigint(20) NOT NULL default '0',  `target_id` bigint(20) NOT NULL default '0',  `status` int(2) NOT NULL default '0' COMMENT '-1刪除0待審核2正常',  `type` int(2) NOT NULL default '0',  PRIMARY KEY  (`id`),  KEY `create_time` (`create_time`),  KEY `userid_status` (`user_id`,`status`),  KEY `event_rule_id_source_id` (`event_rule_id`,`source_id`),  KEY `event_rule_id_status` (`event_rule_id`,`status`),  KEY `type_source_id` (`type`,`source_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8

關聯表event_rule可以指定牛幣規則及其動態顯示資訊，結構如下：

CREATE TABLE `javaniu_event_rule` (  `id` bigint(20) unsigned NOT NULL auto_increment,  `create_time` datetime NOT NULL,  `update_time` datetime default NULL,  `type` int(1) NOT NULL COMMENT '註冊+1\r\n發表心情+1\r\n連續一周發表心情+5\r\n分享代碼+1\r\n分享代碼被下載+n(n為分享者者自訂牛幣)\r\n尋求代碼-2\r\n上傳代碼+1\r\n上傳代碼被下載+1\r\n代碼被設為最佳+n(n為尋求者者自訂牛幣)\r\n刪除代碼-1\r\n無效尋求-2\r\n無效代碼-2\r\n管理員獎賞+n\r\n管理員懲罰-n\r\n周top10+5\r\n月top10+10\r\n年top10+100\r\n資訊完善+1\r\n包月vip+100\r\n師傅贈送+n牛幣\r\n授予徽章+5牛幣\r\n',  `name` varchar(100) NOT NULL,  `niubi` int(11) NOT NULL,  `extend_json` varchar(1000) NOT NULL,  PRIMARY KEY  (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8

event的使用者行為資料模型抽象如下：

 模型一：使用者a通過事件x產生動態0=user_id_a 0 0 0
 a=>x=>0
 模型二：使用者a通過事件x產生產生使用者a的資料1=user_id_a 0 0 1
 a=>x=>1
 模型三：使用者a通過事件x對使用者b的資料1產生使用者a的資料2=user_id_a b 1 2
 a=>x+b+1=>2 
 模型四：使用者a通過事件x對使用者b的資料1產生動態0=user_id_a b 1 0
 a=>x+b+1=>0
 模型五：使用者a通過事件x對使用者b產生動態0=user_id_a b 0 0
 a=>x+b=>0
 
 排列組合:
 user_id source_user_id source_id target_id
 user_id
 user_id source_user_id
 user_id source_user_id source_id
 user_id source_user_id source_id target_id

這樣就囊括了所有會出現的使用者event，只要在java層做業務轉換即可：

最核心的event資料轉換java類源碼：

package com.zuidaima.core.service.impl;private void setSourceAndTarget(Event event, EventRule _eventRule) {try {EventRule eventRule = new EventRule();eventRule.setCreateTime(_eventRule.getCreateTime());eventRule.setExtendJson(_eventRule.getExtendJson());eventRule.setId(_eventRule.getId());eventRule.setName(_eventRule.getName());eventRule.setNiubi(_eventRule.getNiubi());eventRule.setType(_eventRule.getType());eventRule.setUpdateTime(_eventRule.getUpdateTime());BaseEntity source = null;BaseEntity target = null;long sourceId = event.getSourceId();long targetId = event.getTargetId();JSONObject extend = eventRule.getExtend();extend = eventRule.getExtend();String description = (String) extend.get("description");String _description = null;Answer answer = null;Project project = null;switch (eventRule.getType()) {case ModuleConstants.EVENT_TYPE_RULE_PROJECT_CREATE:case ModuleConstants.EVENT_TYPE_RULE_PROJECT_DELETE_BY_USER:case ModuleConstants.EVENT_TYPE_RULE_PROJECT_DELETE_BY_ADMIN:case ModuleConstants.EVENT_TYPE_RULE_PROJECT_VIEW:case ModuleConstants.EVENT_TYPE_RULE_PROJECT_COLLECT:case ModuleConstants.EVENT_TYPE_RULE_PROJECT_REWARD:if (sourceId > 0) {source = projectService.findOneById(sourceId);}if (targetId > 0) {target = projectService.findOneById(targetId);}project = (Project) target;if (source != null) {project = (Project) source;}if (project == null) {return;}_description = String.format(description,ModuleConstants.PROJECT_TYPE_DESC_MAP.get(project.getType()).getDesc());break;case ModuleConstants.EVENT_TYPE_RULE_POST_CREATE:case ModuleConstants.EVENT_TYPE_RULE_POST_DELETE_BY_USER:case ModuleConstants.EVENT_TYPE_RULE_POST_DELETE_BY_ADMIN:if (sourceId > 0) {source = postService.findOneById(sourceId);}if (targetId > 0) {target = postService.findOneById(targetId);}Post post = (Post) target;if (source != null) {post = (Post) source;}_description = String.format(description,ModuleConstants.POST_TYPE_DESC_MAP.get(post.getType()));break;// case ModuleConstants.EVENT_TYPE_RULE_GROUP_CREATE://暫時沒有這種動態case ModuleConstants.EVENT_TYPE_RULE_GROUP_JOIN_IN:case ModuleConstants.EVENT_TYPE_RULE_GROUP_DELETE_BY_USER:// case// ModuleConstants.EVENT_TYPE_RULE_GROUP_DELETE_BY_ADMIN://暫時沒有這種動態if (sourceId > 0) {source = groupService.findOneById(sourceId);}if (targetId > 0) {target = groupService.findOneById(targetId);}Group group = (Group) source;_description = String.format(description,ModuleConstants.GROUP_TYPE_DESC_MAP.get(group.getType()));break;case ModuleConstants.EVENT_TYPE_RULE_COMMENT_CREATE:case ModuleConstants.EVENT_TYPE_RULE_COMMENT_DELETE_BY_USER:case ModuleConstants.EVENT_TYPE_RULE_COMMENT_DELETE_BY_ADMIN:target = commentService.findOneById(targetId);Comment comment = (Comment) target;int commentType = comment.getType();if (commentType == ModuleConstants.COMMENT_TYPE_ANSWER) {source = answerService.findOneById(sourceId);answer = (Answer) source;project = (Project) answer.getTarget();_description = String.format(description,ModuleConstants.PROJECT_TYPE_DESC_MAP.get(project.getType()).getDesc());} else if (commentType == ModuleConstants.COMMENT_TYPE_PROJECT) {source = projectService.findOneById(sourceId);project = (Project) source;_description = String.format(description,ModuleConstants.PROJECT_TYPE_DESC_MAP.get(project.getType()).getDesc());} else if (commentType == ModuleConstants.COMMENT_TYPE_POST) {source = postService.findOneById(sourceId);post = (Post) source;_description = String.format(description,ModuleConstants.POST_TYPE_DESC_MAP.get(post.getType()));} else {}break;case ModuleConstants.EVENT_TYPE_RULE_ANSWER_CREATE:case ModuleConstants.EVENT_TYPE_RULE_ANSWER_BEEN_SET_PERFECT:source = projectService.findOneById(sourceId);target = answerService.findOneById(targetId);project = (Project) source;_description = String.format(description,ModuleConstants.PROJECT_TYPE_DESC_MAP.get(project.getType()).getDesc());break;case ModuleConstants.EVENT_TYPE_RULE_ANSWER_GET:case ModuleConstants.EVENT_TYPE_RULE_ANSWER_DELETE_BY_USER:case ModuleConstants.EVENT_TYPE_RULE_ANSWER_DELETE_BY_ADMIN:source = answerService.findOneById(sourceId);answer = (Answer) source;Project _project = (Project) answer.getTarget();_description = String.format(description,ModuleConstants.PROJECT_TYPE_DESC_MAP.get(_project.getType()).getDesc());break;case ModuleConstants.EVENT_TYPE_RULE_REPUTATION_CREATE:if (sourceId > 0) {source = reputationService.findOneById(sourceId);}if (targetId > 0) {target = reputationService.findOneById(targetId);}break;case ModuleConstants.EVENT_TYPE_RULE_USER_FOLLOW:source = userService.findOneById(sourceId);User _user = (User) source;_description = String.format(description,"<a href='/user/n/" + _user.getName()+ ".htm'>" + _user.getName() + "</a>");break;case ModuleConstants.EVENT_TYPE_RULE_MENTION_COMMENT:target = commentService.findOneById(targetId);comment = (Comment) target;commentType = comment.getType();if (commentType == ModuleConstants.COMMENT_TYPE_ANSWER) {source = answerService.findOneById(sourceId);answer = (Answer) source;project = (Project) answer.getTarget();_description = String.format(description,ModuleConstants.PROJECT_TYPE_DESC_MAP.get(project.getType()).getDesc());} else if (commentType == ModuleConstants.COMMENT_TYPE_PROJECT) {source = projectService.findOneById(sourceId);project = (Project) source;_description = String.format(description,ModuleConstants.PROJECT_TYPE_DESC_MAP.get(project.getType()).getDesc());} else if (commentType == ModuleConstants.COMMENT_TYPE_POST) {source = postService.findOneById(sourceId);post = (Post) source;_description = String.format(description,ModuleConstants.POST_TYPE_DESC_MAP.get(post.getType()));} else {}break;case ModuleConstants.EVENT_TYPE_RULE_MENTION_POST:source = postService.findOneById(sourceId);break;default:_description = description;}extend.put("description", _description);eventRule.setExtend(extend);eventRule.setExtendJson(extend.toString());event.setEventRule(eventRule);event.setSource(source);event.setTarget(target);} catch (Exception e) {logger.error("Fail to setSourceAndTarget event:" + event);}}

freemarker顯示層轉換核心代碼：

<#switch event.eventRule.type><#case event_type_rule_post_create><@event_post_macro event.target/><#break><#case event_type_rule_project_create><@event_project_macro event event.target/><#break><#case event_type_rule_project_view><#case event_type_rule_project_collect><#case event_type_rule_project_reward><@event_project_macro event event.source/><#break><#case event_type_rule_comment_create><@event_comment_macro event event.target/><#break><#case event_type_rule_answer_create><@event_answer_macro event event.target/><#break><#case event_type_rule_answer_get><#case event_type_rule_answer_been_set_perfect><@event_answer_macro event event.source/><#break><#case event_type_rule_mention_comment><@event_comment_macro event event.target/><#break><#case event_type_rule_mention_post><@event_post_macro event.source/><#break></#switch>

比如其中一種event type的freemarker macro代碼如下：

<!--event post--><#macro event_post_macro post><div class="content margin_top5">${post.contentExt}<span class="comments_count"><a target="_blank" href="/mood/${post.id}/comment.htm" rel="nofollow"><img alt="${post.thirdSort}個評論" src="/resource/img/comment.gif">&nbsp;&nbsp;${post.thirdSort}</a></span></div></#macro>

這樣的設計符合高內聚低耦合的設計思路，未來可以根據業務實現無限擴張，當然代價就是event表越來越大，但可以通過分庫分表來分擔壓力，大家可以參考下，有好的意見可以留言。