创建数据库的标准SQL文本

创建人、修改人、创建时间、修改时间
便于后期的维护

rule3.0-处置.sql

CREATE TABLE `rule_dispose` (

`id` BIGINT(20)NOT NULL AUTO_INCREMENT COMMENT'ID',

  `dispose_class` VARCHAR(128)NOT NULL DEFAULT '' COMMENT'处理类',

  `description` VARCHAR(128)NOT NULL DEFAULT '' COMMENT'数据处置方案',

  `createrERP` VARCHAR(32)NOT NULL DEFAULT '' COMMENT'创建人ERP',

  `updaterERP` VARCHAR(32)NOT NULL DEFAULT '' COMMENT'最后修改人ERP',

  `createDate` DATETIMENOT NULL DEFAULT '2010-01-01 00:00:00' COMMENT'创建时间',

  `updateDate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT'最后的修改时间',

  PRIMARY KEY (`id`),

  UNIQUE KEY `UK_dispose_class` (`dispose_class`)USING BTREE

) ENGINE=INNODBDEFAULT CHARSET=utf8 COMMENT='规则3.0数据处置方案';

CREATE TABLE `rule_decision_dispose` (

`id` BIGINT(20)NOT NULL AUTO_INCREMENT COMMENT'ID',

  `decisionId` BIGINT(20)NOT NULL DEFAULT '0' COMMENT'rule_decision2表主键',

  `disposeId` BIGINT(20)NOT NULL DEFAULT '0' COMMENT'rule_dispose表主键',

  `createrERP` VARCHAR(32)NOT NULL DEFAULT '' COMMENT'创建人ERP',

  `updaterERP` VARCHAR(32)NOT NULL DEFAULT '' COMMENT'最后修改人ERP',

  `createDate` DATETIMENOT NULL DEFAULT '2010-01-01 00:00:00' COMMENT'创建时间',

  `updateDate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT'最后的修改时间',

  PRIMARY KEY (`id`),

  KEY `idx_decisionId` (`decisionId`),

  KEY `idx_disposeId` (`disposeId`)

) ENGINE=INNODBDEFAULT CHARSET=utf8 COMMENT='规则3.0rule_decision2和rule_dispose关系表1-n';

创建数据暂存表.sql

create table storage_gap_data(

id int(11) not null auto_increment COMMENT 'id主键',

decision_id varchar(10) not null COMMENT '决策ID（其余业务不需要此字段时设定一个区别值即可）',

resource_type varchar(10) COMMENT '资源类型（可使用此字段识别不同的任务暂存的内容）',

operate_object varchar(100) COMMENT '操作类对象',

function_name varchar(50) COMMENT '操作方法名',

update_json varchar(1024) not null COMMENT '暂存的数据（JSON）',

receive_object varchar(50) COMMENT '接收对象类型',

operate_type int(5) COMMENT '操作类型（增删改查）',

created_date datetime NOT NULL COMMENT '创建时间',

modified_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',

PRIMARY KEY (id),

KEY `idx_decisionId` (decision_id)

)engine = innoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='暂存数据表';

创建决策表.sql

CREATE TABLE `rule_decision_2` ( 
    `id` bigint(20) NOT NULL AUTO_INCREMENT, 
    `sourceType` varchar(64) NOT NULL COMMENT '业务来源', 
    `rule_expression` varchar(128) NOT NULL COMMENT '规则决策表达式(各个脚本唯一ID组合)', 
    `inOrder` int(11) NOT NULL COMMENT '规则的次序。调整类型tinyint->int @date 2017-06-28 @date lijiang64', 
    `action` bigint(20) DEFAULT NULL COMMENT '触发行为,对应decision_action的id', 
    `score` int(11) NOT NULL DEFAULT '0' COMMENT '最终得分', 
    `decision_code` varchar(10) NOT NULL COMMENT '决策码（eg:80000,90000）', 
    `description` varchar(1024) DEFAULT NULL COMMENT '维度规则描述', 
    `disable` smallint(6) DEFAULT NULL, 
    `createDate` datetime NOT NULL, 
    `updateDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
    `createrERP` varchar(32) NOT NULL DEFAULT '""' COMMENT '创建人ERP', 
    `updaterERP` varchar(32) NOT NULL DEFAULT '""' COMMENT '最后修改人ERP', 
    `real_time` smallint(6) DEFAULT '1' COMMENT '否是实时', 
    PRIMARY KEY (`id`), 
    UNIQUE KEY `soureType_inOrder` (`sourceType`,`inOrder`), 
    KEY `disable` (`disable`), 
    KEY `sourceType_disable_order` (`sourceType`,`disable`,`inOrder`) 
) ENGINE=InnoDB AUTO_INCREMENT=7873 DEFAULT CHARSET=utf8 COMMENT='规则决策表'

创建审批表.sql

CREATE TABLE `rule_decision_approve` ( 
    `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键', 
    `decision_id` bigint(20) NOT NULL COMMENT '规则决策id', 
    `creater_erp` varchar(50) COLLATE utf8_bin NOT NULL COMMENT '创建人ERP', 
    `approve_erp` varchar(50) COLLATE utf8_bin NOT NULL COMMENT '审批人erp', 
    `status` tinyint(4) NOT NULL COMMENT '1 已提交 2审批通过 3审批拒绝', 
    `create_time` datetime NOT NULL COMMENT '新建时间', 
    `approve_time` datetime DEFAULT NULL COMMENT '审批时间', 
    PRIMARY KEY (`id`),
    KEY `decision_id` (`decision_id`) 
) ENGINE=InnoDB AUTO_INCREMENT=11981 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='规则决策审批表'