MicroCommunity/java110-db/db/WebService/create_table.db

-- m_menu

create table m_menu(
    m_id INT NOT NULL AUTO_INCREMENT KEY COMMENT '菜单ID',
    name varchar(10) not null comment '菜单名称',
    level varchar(2) not null comment '菜单级别 一级菜单 为 1 二级菜单为2',
    parent_id int not null comment '父类菜单id 如果是一类菜单则写为-1 如果是二类菜单则写父类的菜单id',
    menu_group varchar(10) not null comment '菜单组 left 显示在页面左边的菜单',
    user_id varchar(12) not null comment '创建菜单的用户id',
    remark VARCHAR(200) COMMENT '描述',
    seq INT NOT NULL  COMMENT '列顺序',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    status_cd VARCHAR(2) NOT NULL DEFAULT '0' COMMENT '数据状态，详细参考c_status表，0在用，1失效'
);

-- m_menu_ctg

create table m_menu_ctg(
    m_ctg_id INT NOT NULL AUTO_INCREMENT KEY COMMENT '菜单配置ID',
    m_id INT NOT NULL COMMENT '菜单ID',
    url varchar(100) not null comment '菜单打开地址',
    template varchar(50) comment '页面模板 模板名称',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    status_cd VARCHAR(2) NOT NULL DEFAULT '0' COMMENT '数据状态，详细参考c_status表，0在用，1失效'
);

-- m_menu_2_user

create table m_menu_2_user(
    m_user_id INT NOT NULL AUTO_INCREMENT KEY COMMENT '菜单用户ID',
    m_id int not null comment '菜单id',
    user_id varchar(30) not null comment '用户id',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    status_cd VARCHAR(2) NOT NULL DEFAULT '0' COMMENT '数据状态，详细参考c_status表，0在用，1失效'
);

insert into m_menu(name,level,parent_id,menu_group,user_id,remark,seq) values('系统配置','1','-1','LEFT','10001','',1);
insert into m_menu(name,level,parent_id,menu_group,user_id,remark,seq) values('映射管理','2','1','LEFT','10001','',2);
insert into m_menu(name,level,parent_id,menu_group,user_id,remark,seq) values('外部应用','2','1','LEFT','10001','',3);
insert into m_menu(name,level,parent_id,menu_group,user_id,remark,seq) values('路由管理','2','1','LEFT','10001','',4);
insert into m_menu(name,level,parent_id,menu_group,user_id,remark,seq) values('服务管理','2','1','LEFT','10001','',5);
insert into m_menu(name,level,parent_id,menu_group,user_id,remark,seq) values('缓存管理','1','-1','LEFT','10001','',1);
insert into m_menu(name,level,parent_id,menu_group,user_id,remark,seq) values('刷新缓存','2','1','LEFT','10001','',2);

insert into m_menu_ctg(m_id,url,template) values(1,'#','');
insert into m_menu_ctg(m_id,url,template) values(2,'/console/list?templateCode=mapping','');
insert into m_menu_ctg(m_id,url,template) values(3,'/console/list?templateCode=app','');
insert into m_menu_ctg(m_id,url,template) values(4,'/console/list?templateCode=service','');
insert into m_menu_ctg(m_id,url,template) values(5,'/console/list?templateCode=route','');
insert into m_menu_ctg(m_id,url,template) values(6,'#','');
insert into m_menu_ctg(m_id,url,template) values(7,'/','');


insert into m_menu_2_user(m_id,user_id) values(1,'10001');
insert into m_menu_2_user(m_id,user_id) values(2,'10001');
insert into m_menu_2_user(m_id,user_id) values(3,'10001');
insert into m_menu_2_user(m_id,user_id) values(4,'10001');
insert into m_menu_2_user(m_id,user_id) values(5,'10001');
insert into m_menu_2_user(m_id,user_id) values(6,'10001');
insert into m_menu_2_user(m_id,user_id) values(7,'10001');

-- c_template
create table c_template(
    id INT NOT NULL AUTO_INCREMENT KEY COMMENT '模板ID',
    template_code varchar(20) not null UNIQUE comment '模板编码 模板英文名',
    name varchar(50) not null comment '模板名称',
    html_name varchar(20) not null comment '对应HTML文件名称',
    url varchar(200) not null comment '查询数据，修改数据url 其真实地址对应于mapping表中 LIST->key 对应 查询多条数据 QUERY->key 对应单条数据 UPDATE-> 对应修改数据 DELETE->key 对应删除数据 多条之间用 ; 分隔',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    status_cd VARCHAR(2) NOT NULL DEFAULT '0' COMMENT '数据状态，详细参考c_status表，0在用，1失效'
);

-- c_template_col

create table c_template_col(
    id INT NOT NULL AUTO_INCREMENT KEY COMMENT '模板ID',
    template_code varchar(20) not null comment '模板编码 模板英文名',
    col_name varchar(50) not null comment '前台显示名称',
    col_code varchar(20) not null comment '字段的编码',
    col_model longtext not null comment 'jqgrid的colmodel',
    seq INT NOT NULL  COMMENT '列顺序',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    status_cd VARCHAR(2) NOT NULL DEFAULT '0' COMMENT '数据状态，详细参考c_status表，0在用，1失效'
);


insert into c_template(template_code,name,html_name,url) values('mapping','映射管理','list_template','LIST->query.center.mapping;QUERY->mapping_query_url;INSERT->save.center.mapping;UPDATE->update.center.mapping;DELETE->delete.center.mapping');

insert into c_template_col(template_code,col_name,col_code,col_model,seq) values('mapping','列ID','id','{ "name": "id","index": "id","width": "90",
                                                                                                             "editable": true,
                                                                                                             "sorttype": "int" }',1);
insert into c_template_col(template_code,col_name,col_code,col_model,seq) values('mapping','域','domain','{ "name": "domain","index": "domain","width": "90",
                                                                                                             "editable": true,
                                                                                                             "formatoptions": { "defaultValue": "DOMAIN.COMMON" }
                                                                                                           }',2);
insert into c_template_col(template_code,col_name,col_code,col_model,seq) values('mapping','名称','name','{ "name": "name","index": "name","width": "90",
                                                                                                             "editable": true }',3);

insert into c_template_col(template_code,col_name,col_code,col_model,seq) values('mapping','键','key','{ "name": "key","index": "key","width": "90",
                                                                                                             "editable": true }',4);

insert into c_template_col(template_code,col_name,col_code,col_model,seq) values('mapping','值','value','{ "name": "value","index": "value","width": "90",
                                                                                                             "editable": true }',5);
insert into c_template_col(template_code,col_name,col_code,col_model,seq) values('mapping','备注','value','{ "name": "remark","index": "remark","width": "90",
                                                                                                             "editable": true }',6);

INSERT INTO c_template_col(template_code,col_name,col_code,col_model,seq) VALUES('mapping','BUTTON','BUTTON','{
                                                                                                            "name": "detail",
                                                                                                            "index": "",
                                                                                                            "width": "40",
                                                                                                            "fixed": "true",
                                                                                                            "sortable": "false",
                                                                                                            "resize": "false",
                                                                                                            "formatter": "function(cellvalue, options, rowObject){\n var temp =\"<div style=''margin-left:8px;''><div title=''详情记录'' style=''float:left;cursor:pointer;'' class=''ui-pg-div'' id=''jEditButton_3'' onclick=''detail(\"+rowObject+\")'' onmouseover=''jQuery(this).addClass(''ui-state-hover'');'' onmouseout=''jQuery(this).removeClass(''ui-state-hover'');''><span class=''ui-icon fa-search-plus''/></div></div>\";\n return temp; \n}"
                                                                                                          }',7);
/** 如果BUTTON 报错不显示，则直接粘贴到col_model中
 {
 	"name": "detail",
 	"index": "",
 	"width": "40",
 	"fixed": true,
 	"sortable": "false",
 	"resize": "false",
 	"formatter": "function(cellvalue, options, rowObject){ var temp =\"<div style='margin-left:8px;'><div title='详情记录' style='float:left;cursor:pointer;' class='ui-pg-div' id='jEditButton_3' onclick='detail(\" + rowObject + \")' onmouseover='jQuery(this).addClass('ui-state-hover');' onmouseout='jQuery(this).removeClass('ui-state-hover');'><span class='ui-icon fa-search-plus'/></div></div>\";return temp; }"
 }
**/



insert into c_template(template_code,name,html_name,url) values('app','外部应用','list_template','LIST->query.center.apps;QUERY->query.center.app');

insert into c_template_col(template_code,col_name,col_code,col_model,seq) values('app','列ID','id','{ "name": "id","index": "id","width": "20",
                                                                                                             "editable": true,
                                                                                                             "sorttype": "int" }',1);
insert into c_template_col(template_code,col_name,col_code,col_model,seq) values('app','AppId','domain','{ "name": "appId","index": "appId","width": "40",
                                                                                                             "editable": true
                                                                                                           }',2);
insert into c_template_col(template_code,col_name,col_code,col_model,seq) values('app','名称','name','{ "name": "name","index": "name","width": "50",
                                                                                                             "editable": true }',3);

insert into c_template_col(template_code,col_name,col_code,col_model,seq) values('app','秘钥','securityCode','{ "name": "securityCode","index": "securityCode","width": "50",
                                                                                                             "editable": true }',4);

insert into c_template_col(template_code,col_name,col_code,col_model,seq) values('app','白名单','whileListIp','{ "name": "whileListIp","index": "whileListIp","width": "90",
                                                                                                             "editable": true }',5);

insert into c_template_col(template_code,col_name,col_code,col_model,seq) values('app','黑名单','blackListIp','{ "name": "blackListIp","index": "blackListIp","width": "40",
                                                                                                             "editable": true }',6);

insert into c_template_col(template_code,col_name,col_code,col_model,seq) values('app','备注','value','{ "name": "remark","index": "remark","width": "90",
                                                                                                             "editable": true }',7);

INSERT INTO c_template_col(template_code,col_name,col_code,col_model,seq) VALUES('app','BUTTON','BUTTON','{
                                                                                                            "name": "detail",
                                                                                                            "index": "",
                                                                                                            "width": "40",
                                                                                                            "fixed": "true",
                                                                                                            "sortable": "false",
                                                                                                            "resize": "false",
                                                                                                            "formatter": "function(cellvalue, options, rowObject){\n var temp =\"<div style=''margin-left:8px;''><div title=''详情记录'' style=''float:left;cursor:pointer;'' class=''ui-pg-div'' id=''jEditButton_3'' onclick=''detail(\"+rowObject+\")'' onmouseover=''jQuery(this).addClass(''ui-state-hover'');'' onmouseout=''jQuery(this).removeClass(''ui-state-hover'');''><span class=''ui-icon fa-search-plus''/></div></div>\";\n return temp; \n}"
                                                                                                          }',8);


insert into c_template(template_code,name,html_name,url) values('service','服务管理','list_template','LIST->query.center.services;QUERY->query.center.service');

insert into c_template_col(template_code,col_name,col_code,col_model,seq) values('service','服务ID','serviceId','{ "name": "serviceId","index": "serviceId","width": "20",
                                                                                                             "editable": true,
                                                                                                             "sorttype": "int" }',1);
insert into c_template_col(template_code,col_name,col_code,col_model,seq) values('service','服务编码','serviceCode','{ "name": "serviceCode","index": "serviceCode","width": "40",
                                                                                                             "editable": true
                                                                                                           }',2);

insert into c_template_col(template_code,col_name,col_code,col_model,seq) values('service','业务类型','businessTypeCd','{ "name": "businessTypeCd","index": "businessTypeCd","width": "50",
                                                                                                             "editable": true }',3);

insert into c_template_col(template_code,col_name,col_code,col_model,seq) values('service','名称','name','{ "name": "name","index": "name","width": "40",
                                                                                                             "editable": true }',4);

insert into c_template_col(template_code,col_name,col_code,col_model,seq) values('service','消息队列','messageQueueName','{ "name": "messageQueueName","index": "messageQueueName","width": "10",
                                                                                                             "editable": true }',5);
insert into c_template_col(template_code,col_name,col_code,col_model,seq) values('service','需要Instance','isInstance','{ "name": "isInstance","index": "isInstance","width": "10",
                                                                                                             "editable": true }',6);

insert into c_template_col(template_code,col_name,col_code,col_model,seq) values('service','URL','url','{ "name": "url","index": "url","width": "60",
                                                                                                             "editable": true }',7);

insert into c_template_col(template_code,col_name,col_code,col_model,seq) values('service','提供者AppId','provideAppId','{ "name": "provideAppId","index": "provideAppId","width": "10",
                                                                                                             "editable": true }',8);

INSERT INTO c_template_col(template_code,col_name,col_code,col_model,seq) VALUES('service','BUTTON','BUTTON','{
                                                                                                            "name": "detail",
                                                                                                            "index": "",
                                                                                                            "width": "40",
                                                                                                            "fixed": "true",
                                                                                                            "sortable": "false",
                                                                                                            "resize": "false",
                                                                                                            "formatter": "function(cellvalue, options, rowObject){\n var temp =\"<div style=''margin-left:8px;''><div title=''详情记录'' style=''float:left;cursor:pointer;'' class=''ui-pg-div'' id=''jEditButton_3'' onclick=''detail(\"+rowObject+\")'' onmouseover=''jQuery(this).addClass(''ui-state-hover'');'' onmouseout=''jQuery(this).removeClass(''ui-state-hover'');''><span class=''ui-icon fa-search-plus''/></div></div>\";\n return temp; \n}"
                                                                                                          }',9);


insert into c_template(template_code,name,html_name,url) values('route','路由管理','list_template','LIST->query.center.routes;QUERY->query.center.route');

insert into c_template_col(template_code,col_name,col_code,col_model,seq) values('route','路由ID','id','{ "name": "id","index": "id","width": "10",
                                                                                                             "editable": true,
                                                                                                             "sorttype": "int" }',1);
insert into c_template_col(template_code,col_name,col_code,col_model,seq) values('route','AppId','appId','{ "name": "appId","index": "appId","width": "30",
                                                                                                             "editable": true
                                                                                                           }',2);
insert into c_template_col(template_code,col_name,col_code,col_model,seq) values('route','服务ID','serviceId','{ "name": "serviceId","index": "serviceId","width": "30",
                                                                                                             "editable": true }',3);
insert into c_template_col(template_code,col_name,col_code,col_model,seq) values('route','调用方式','invokeModel','{ "name": "invokeModel","index": "invokeModel","width": "50",
                                                                                                              "editable": true }',4);
insert into c_template_col(template_code,col_name,col_code,col_model,seq) values('route','服务名称','serviceName','{ "name": "serviceName","index": "serviceName","width": "30",
                                                                                                             "editable": true }',5);
insert into c_template_col(template_code,col_name,col_code,col_model,seq) values('route','服务编码','serviceCode','{ "name": "serviceCode","index": "serviceCode","width": "30",
                                                                                                             "editable": true }',6);

insert into c_template_col(template_code,col_name,col_code,col_model,seq) values('route','订单类型','orderTypeCd','{ "name": "orderTypeCd","index": "orderTypeCd","width": "30",
                                                                                                             "editable": true }',7);

insert into c_template_col(template_code,col_name,col_code,col_model,seq) values('route','调用次数限制','invokelimitTimes','{ "name": "invokelimitTimes","index": "invokelimitTimes","width": "40",
                                                                                                             "editable": true }',8);

INSERT INTO c_template_col(template_code,col_name,col_code,col_model,seq) VALUES('route','BUTTON','BUTTON','{
                                                                                                            "name": "detail",
                                                                                                            "index": "",
                                                                                                            "width": "40",
                                                                                                            "fixed": "true",
                                                                                                            "sortable": "false",
                                                                                                            "resize": "false",
                                                                                                            "formatter": "function(cellvalue, options, rowObject){\n var temp =\"<div style=''margin-left:8px;''><div title=''详情记录'' style=''float:left;cursor:pointer;'' class=''ui-pg-div'' id=''jEditButton_3'' onclick=''detail(\"+rowObject+\")'' onmouseover=''jQuery(this).addClass(''ui-state-hover'');'' onmouseout=''jQuery(this).removeClass(''ui-state-hover'');''><span class=''ui-icon fa-search-plus''/></div></div>\";\n return temp; \n}"


insert into c_template(template_code,name,html_name,url) values('cache','刷新缓存','list_template_cache','LIST->query.center.caches;QUERY->query.center.cacheOne');

insert into c_template_col(template_code,col_name,col_code,col_model,seq) values('cache','缓存ID','id','{ "name": "id","index": "id","width": "10",
                                                                                                             "editable": true,
                                                                                                             "sorttype": "int" }',1);

insert into c_template_col(template_code,col_name,col_code,col_model,seq) values('cache','缓存编码','cacheCode','{ "name": "cacheCode","index": "cacheCode","width": "30",
                                                                                                             "editable": true }',2);
insert into c_template_col(template_code,col_name,col_code,col_model,seq) values('cache','缓存名称','cacheName','{ "name": "cacheName","index": "cacheName","width": "30",
                                                                                                             "editable": true }',3);
INSERT INTO c_template_col(template_code,col_name,col_code,col_model,seq) VALUES('cache','BUTTON','BUTTON','{
                                                                                                            "name": "detail",
                                                                                                            "index": "",
                                                                                                            "width": "40",
                                                                                                            "fixed": "true",
                                                                                                            "sortable": "false",
                                                                                                            "resize": "false",
                                                                                                            "formatter": ""function(cellvalue, options, rowObject){ var temp =\"<div style='margin-left:8px;'><button type='button' class='btn btn-warning' style='border-radius: .25rem;' onclick='flush(this,\"+rowObject.cacheCode+\")'>刷新缓存</button></div>\";return temp; }"
                                                                                                          }',4);
/** 如果BUTTON 报错不显示，则直接粘贴到col_model中
 {
 	"name": "detail",
 	"index": "",
 	"width": "40",
 	"fixed": "true",
 	"sortable": "false",
 	"resize": "false",
 	"formatter": "function(cellvalue, options, rowObject){ var temp =\"<div style='margin-left:8px;'><button type='button' class='btn btn-warning' style='border-radius: .25rem;' onclick='flush(this,\"+rowObject.cacheCode+\")'>刷新缓存</button></div>\";return temp; }"
 }
**/

-- 缓存配置表
CREATE TABLE c_cache(
    id INT NOT NULL AUTO_INCREMENT KEY COMMENT '缓存ID',
    cache_code  VARCHAR(10) NOT NULL UNIQUE COMMENT '缓存编码 开始于1001',
    service_code  VARCHAR(50) NOT NULL COMMENT '调用服务编码 对应 c_service',
    `name` VARCHAR(50) NOT NULL COMMENT '前台显示名称',
    param LONGTEXT NOT NULL COMMENT '请求缓存系统时的参数',
    seq INT NOT NULL  COMMENT '列顺序',
    `group` VARCHAR(10) NOT NULL DEFAULT 'COMMON' COMMENT '组，缓存属于哪个组',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    status_cd VARCHAR(2) NOT NULL DEFAULT '0' COMMENT '数据状态，详细参考c_status表，0在用，1失效'
);
-- 缓存用户表
create table c_cache_2_user(
     id INT NOT NULL AUTO_INCREMENT KEY COMMENT '缓存用户ID',
     cache_code int not null comment '缓存编码',
     user_id varchar(30) not null comment '用户id',
     create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
     status_cd VARCHAR(2) NOT NULL DEFAULT '0' COMMENT '数据状态，详细参考c_status表，0在用，1失效'
);


insert into  c_cache (cache_code,service_code,`name`,param,seq) values('1001','flush.center.cache','映射缓存（c_mapping表）','{"cacheName":"MAPPING"}',1);
insert into  c_cache (cache_code,service_code,`name`,param,seq) values('1002','flush.center.cache','业务配置缓存（c_app,c_service,c_route表）','{"cacheName":"APP_ROUTE_SERVICE"}',2);
insert into  c_cache (cache_code,service_code,`name`,param,seq) values('1003','flush.center.cache','公用服务缓存（c_service_sql表）','{"cacheName":"SERVICE_SQL"}',3);

insert into c_cache_2_user(cache_code,user_id) values('1001','10001');
insert into c_cache_2_user(cache_code,user_id) values('1002','10001');
insert into c_cache_2_user(cache_code,user_id) values('1003','10001');