package com.walker.dbmeta.util;
|
|
import com.walker.connector.Address;
|
import com.walker.connector.support.DatabaseConnector;
|
import com.walker.db.DatabaseException;
|
import com.walker.infrastructure.utils.SegmentReader;
|
import com.walker.infrastructure.utils.StringUtils;
|
|
public abstract class DatabaseUtils {
|
|
public static final String DB_SEPARATOR = "_";
|
public static final String MYSQL_TBL_REF = "`";
|
|
/**
|
* 克隆一个<code>Address</code>资源地址对象
|
* @param addr
|
* @param isUsing 是否正在使用
|
* @param suffixIndex 命名的后缀，多库使用
|
* @return
|
*/
|
public static Address copyAddress(Address addr, boolean isUsing, int suffixIndex, String defineName){
|
Address a = new Address();
|
a.setUrl(addr.getUrl());
|
a.setPort(addr.getPort());
|
a.setService(defineName + DB_SEPARATOR + suffixIndex);
|
a.setSuffixIndex(suffixIndex);
|
a.setUsing(isUsing);
|
a.setAuthentication(addr.getAuthentication());
|
a.setCertification(addr.getCertification());
|
return a;
|
}
|
|
/**
|
* 把MySQL的服务名字中提取数据库，因为里面会有参数，如：walkersoft-share-marks?characterEncoding=UTF-8</p>
|
* 提取之后结果为：walkersoft-share-marks
|
* @param serviceName
|
* @return
|
*/
|
public static final String getMySQLSchemaName(String serviceName){
|
if(StringUtils.isEmpty(serviceName)){
|
return null;
|
}
|
int index = serviceName.indexOf("?");
|
if(index >= 0){
|
return serviceName.substring(0, index);
|
} else {
|
return serviceName;
|
}
|
}
|
|
/**
|
* 返回数据库名字后面的后缀，如果不存在返回-1</p>
|
* 如：demo_1, test_u_2
|
* @param dbName
|
* @return
|
*/
|
public static final int getDatabaseNameIndex(String dbName){
|
if(StringUtils.isEmpty(dbName)){
|
return -1;
|
}
|
String[] test = dbName.split(DB_SEPARATOR);
|
if(test == null || test.length <= 1){
|
return -1;
|
}
|
String index = test[test.length-1];
|
try{
|
return Integer.parseInt(index);
|
} catch(NumberFormatException ex){
|
return -1;
|
}
|
}
|
|
private static final String KEY_START = "create table ";
|
private static final String KEY_START_BACK = "create table if not exists ";
|
private static final String KEY_END = "(";
|
|
/**
|
* 从创建表的SQL中找出表名
|
* @param createTableSQL
|
* @return
|
* @throws DatabaseException
|
*/
|
public static final String findTableNameInSQL(String createTableSQL) throws DatabaseException {
|
// String _s = createTableSQL.trim().toLowerCase();
|
String _s = createTableSQL;
|
if(_s.startsWith(KEY_START_BACK)){
|
String tname = getSingleSqlTable(_s, KEY_START_BACK);
|
if(StringUtils.isNotEmpty(tname)){
|
// MySQL会有这个字符，要去掉
|
return tname.trim().replaceAll(MYSQL_TBL_REF, "");
|
}
|
} else if(_s.startsWith(KEY_START)){
|
String tname = getSingleSqlTable(_s, KEY_START);
|
if(StringUtils.isNotEmpty(tname)){
|
// MySQL会有这个字符，要去掉
|
return tname.trim().replaceAll(MYSQL_TBL_REF, "");
|
}
|
}
|
throw new DatabaseException("SQL中未找到表名" + _s);
|
}
|
|
/**
|
* 得到SQL中表的名字，如： create table table1(id varchar2 not null ...)
|
* @param sql
|
* @return
|
*/
|
private static String getSingleSqlTable(String sql, String keyStart){
|
SegmentReader reader = new SegmentReader();
|
reader.setKeyCaseSensitive(true);
|
reader.addKey(keyStart, KEY_END);
|
reader.addRemoveKey("--", "\n");
|
reader.read(sql);
|
return reader.getSolvedContent(keyStart, KEY_END);
|
}
|
|
private static final String KEY_START_INX = "create index ";
|
private static final String KEY_END_INX = " on";
|
|
/**
|
* 找到SQL语句中的索引名字，如果是创建索引语句的话
|
* @param createTableSQL
|
* @return
|
* @throws DatabaseException
|
*/
|
public static String findIndexNameInSQL(String createTableSQL) throws DatabaseException{
|
if(createTableSQL.startsWith(KEY_START_INX)){
|
String inxName = getSingleIndexName(createTableSQL);
|
if(StringUtils.isNotEmpty(inxName)){
|
// MySQL会有这个字符，要去掉
|
return inxName.trim();
|
}
|
}
|
throw new DatabaseException("SQL中未找到索引名称，sql = " + createTableSQL);
|
}
|
|
private static String getSingleIndexName(String sql){
|
SegmentReader reader = new SegmentReader();
|
reader.setKeyCaseSensitive(true);
|
reader.addKey(KEY_START_INX, KEY_END_INX);
|
reader.addRemoveKey("--", "\n");
|
reader.read(sql);
|
return reader.getSolvedContent(KEY_START_INX, KEY_END_INX);
|
}
|
|
private static final String KEY_TBL_START_INX = " on ";
|
|
/**
|
* 返回创建索引SQL中的表名称
|
* @param sql
|
* @return
|
*/
|
public static final String findTableNameInIndex(String sql){
|
SegmentReader reader = new SegmentReader();
|
reader.setKeyCaseSensitive(true);
|
reader.addKey(KEY_TBL_START_INX, KEY_END);
|
reader.addRemoveKey("--", "\n");
|
reader.read(sql);
|
String result = reader.getSolvedContent(KEY_TBL_START_INX, KEY_END);
|
if(StringUtils.isNotEmpty(result)){
|
return result.trim();
|
} else
|
return null;
|
}
|
|
/**
|
* 是个创建表结构的SQL语句
|
* @param sql
|
* @return
|
*/
|
public static boolean isCreateTableSQL(String sql){
|
if(sql.startsWith(KEY_START_BACK) || sql.startsWith(KEY_START)){
|
return true;
|
}
|
return false;
|
}
|
|
/**
|
* 是个创建索引的SQL语句
|
* @param sql
|
* @return
|
*/
|
public static boolean isCreateIndexSQL(String sql){
|
if(sql.startsWith(KEY_START_INX)){
|
return true;
|
}
|
return false;
|
}
|
|
/**
|
* 手动创建物理表数据，在存在元数据的情况下，物理表不存在，需要通过该方法创建。
|
* @param connector
|
* @param createSQL
|
* @param tableName 缺失的表名，如：rec_main_consume_709
|
* @param business 业务参数，如：customerCode
|
* @return
|
* @throws DatabaseException
|
*/
|
public static boolean manualCreatePhysicalTable(DatabaseConnector connector
|
, String createSQL, String tableName, String business) throws DatabaseException{
|
// 找出sql中表名，如果与给定的目的表名不一致，要用目的表名替换
|
if(StringUtils.isEmpty(createSQL)){
|
throw new IllegalArgumentException("SQL语句中未找到表名，destTableName = " + tableName);
|
}
|
|
// 处理给定的SQL语句，因为里面可能会存在多个，包括：创建表结构和索引的!
|
String[] sqlList = createSQL.split(StringUtils.SEPARATOR_SEMI_COLON);
|
if(sqlList == null || sqlList.length == 0){
|
throw new DatabaseException("存储引擎未发现任何创建表或者索引的SQL语句，无法继续执行写入数据任务。createSQL = " + createSQL + ", tableName = " + tableName);
|
}
|
for(String sql : sqlList){
|
sql = sql.trim(); // 去掉回车、换行、空格等
|
if(DatabaseUtils.isCreateTableSQL(sql)){
|
String tname = DatabaseUtils.findTableNameInSQL(sql);
|
if(!tname.equals(tableName)){
|
sql = sql.replace(tname, tableName);
|
// logger.debug("替换后的SQL = " + sql);
|
}
|
connector.exeCreateTable(sql);
|
return true;
|
|
} else if(DatabaseUtils.isCreateIndexSQL(sql)){
|
String iname = DatabaseUtils.findIndexNameInSQL(sql);
|
// 重用生成表名的策略来生成索引名字，它两个其实一样，不想再加入新接口了。
|
// String indexName = ((DatabaseStoreStrategy)this.storeStrategy).getTableName(addr, iname, parameter);
|
String indexName = iname + DatabaseUtils.DB_SEPARATOR + business;
|
if(!iname.equals(indexName)){
|
// 替换索引名字
|
sql = sql.replace(iname, indexName);
|
// 替换索引中表名
|
sql = sql.replace(DatabaseUtils.findTableNameInIndex(sql), tableName);
|
// logger.debug("替换后的索引SQL = " + sql);
|
}
|
connector.exeCreateTable(sql);
|
return true;
|
}
|
}
|
return false;
|
}
|
|
public static void main(String[] args) throws Exception{
|
// System.out.println("_test_-9: " + DatabaseUtils.getDatabaseNameIndex("_test_-9"));
|
// System.out.println("_test: " + DatabaseUtils.getDatabaseNameIndex("_test"));
|
// System.out.println("test-2_1: " + DatabaseUtils.getDatabaseNameIndex("test-2_1"));
|
// System.out.println("test-2_: " + DatabaseUtils.getDatabaseNameIndex("test-2_"));
|
// System.out.println("ABC2 _DSF_0: " + DatabaseUtils.getDatabaseNameIndex("ABC2 _DSF_0"));
|
|
// String dest = "my_demo";
|
// String test = "CREATE TABLE IF NOT EXISTS `t_menu` (`id` int(11) NOT NULL AUTO_INCREMENT,`pid` int(11) NOT NULL,`url` varchar(100) CHARACTER SET utf8 NOT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=95 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;";
|
// String tname = findTableNameInSQL(test);
|
// System.out.println("tname ==== " + tname);
|
// System.out.println(test.replaceFirst(tname, dest));
|
|
// System.out.println("获得表名：" + findTableNameInSQL("create table if not exists REC_MAIN_CONSUME(cstaccfc BIGINT"));
|
// System.out.println("获得表名：" + findTableNameInSQL("create table REC_MAIN_CONSUME(cstaccfc BIGINT"));
|
|
//// String sql = "CREATE INDEX BASE_AREA_VER_INX ON BASE_AREA (ver)";
|
// String sql = "CREATE INDEX BASE_AREA_VER_INX ON BASE_AREA (ver)";
|
// sql = sql.trim().toLowerCase();
|
// System.out.println("获得索引名：" + findIndexNameInSQL(sql));
|
|
// System.out.println("数据库名字1 = " + getMySQLSchemaName("walkersoft-share-marks?characterEncoding=UTF-8"));
|
// System.out.println("数据库名字2 = " + getMySQLSchemaName("test"));
|
// System.out.println("数据库名字3 = " + getMySQLSchemaName("walkersoft-share-marks?characterEncoding=UTF-8&code=u"));
|
|
System.out.println("" + (1025 % 1024));
|
System.out.println(findTableNameInIndex("create index base_area_ver_inx_44 on base_area (ver) "));
|
}
|
}
|
