/*
* Copyright (C) 2010-2101 Alibaba Group Holding Limited.
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package com.alibaba.otter.shared.common.utils.meta;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.builder.ToStringBuilder;
import org.apache.commons.lang.builder.ToStringStyle;
import org.apache.commons.lang.math.NumberUtils;
import org.apache.ddlutils.model.Column;
import org.apache.ddlutils.model.Table;
import org.apache.ddlutils.platform.DatabaseMetaDataWrapper;
import org.apache.ddlutils.platform.MetaDataColumnDescriptor;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ConnectionCallback;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.SingleColumnRowMapper;
import org.springframework.jdbc.support.JdbcUtils;
import org.springframework.util.Assert;
/**
* copy from otter3.0
*
* @author xiaoqing.zhouxq 2012-3-30 上午10:43:04
* @author zebin.xuzb add filter for data
*/
public class DdlUtils {
private static final Logger logger = LoggerFactory.getLogger(DdlUtils.class);
private static TableType[] SUPPORTED_TABLE_TYPES = new TableType[] { TableType.view, TableType.table };
private final static Map<Integer, String> _defaultSizes = new HashMap<Integer, String>();
static {
_defaultSizes.put(new Integer(1), "254");
_defaultSizes.put(new Integer(12), "254");
_defaultSizes.put(new Integer(-1), "254");
_defaultSizes.put(new Integer(-2), "254");
_defaultSizes.put(new Integer(-3), "254");
_defaultSizes.put(new Integer(-4), "254");
_defaultSizes.put(new Integer(4), "32");
_defaultSizes.put(new Integer(-5), "64");
_defaultSizes.put(new Integer(7), "7,0");
_defaultSizes.put(new Integer(6), "15,0");
_defaultSizes.put(new Integer(8), "15,0");
_defaultSizes.put(new Integer(3), "15,15");
_defaultSizes.put(new Integer(2), "15,15");
}
/**
* !!! Only supports MySQL
*/
@SuppressWarnings("unchecked")
public static List<String> findSchemas(JdbcTemplate jdbcTemplate, final String schemaPattern) {
try {
if (StringUtils.isEmpty(schemaPattern)) {
return jdbcTemplate.query("show databases", new SingleColumnRowMapper(String.class));
}
return jdbcTemplate.query("show databases like ?", new Object[] { schemaPattern },
new SingleColumnRowMapper(String.class));
} catch (Exception e) {
logger.error(e.getMessage(), e);
return new ArrayList<String>();
}
}
/**
* !!! Only supports MySQL
*/
public static List<String> findSchemas(JdbcTemplate jdbcTemplate, final String schemaPattern,
final DdlSchemaFilter ddlSchemaFilter) {
List<String> schemas = findSchemas(jdbcTemplate, schemaPattern);
if (ddlSchemaFilter == null) {
return schemas;
}
List<String> filterSchemas = new ArrayList<String>();
for (String schema : schemas) {
if (ddlSchemaFilter.accept(schema)) {
filterSchemas.add(schema);
}
}
return filterSchemas;
}
public static Table findTable(JdbcTemplate jdbcTemplate, final String catalogName, final String schemaName,
final String tableName) throws Exception {
return findTable(jdbcTemplate, catalogName, schemaName, tableName, null);
}
public static Table findTable(final JdbcTemplate jdbcTemplate, final String catalogName, final String schemaName,
final String tableName, final DdlUtilsFilter filter) throws Exception {
return (Table) jdbcTemplate.execute(new ConnectionCallback() {
public Object doInConnection(Connection con) throws SQLException, DataAccessException {
Table table = null;
DatabaseMetaDataWrapper metaData = new DatabaseMetaDataWrapper();
try {
if (filter != null) {
con = filter.filterConnection(con);
Assert.notNull(con);
}
DatabaseMetaData databaseMetaData = con.getMetaData();
if (filter != null) {
databaseMetaData = filter.filterDataBaseMetaData(jdbcTemplate, con, databaseMetaData);
Assert.notNull(databaseMetaData);
}
metaData.setMetaData(databaseMetaData);
metaData.setTableTypes(TableType.toStrings(SUPPORTED_TABLE_TYPES));
metaData.setCatalog(catalogName);
metaData.setSchemaPattern(schemaName);
String convertTableName = tableName;
if (databaseMetaData.storesUpperCaseIdentifiers()) {
metaData.setCatalog(catalogName.toUpperCase());
metaData.setSchemaPattern(schemaName.toUpperCase());
convertTableName = tableName.toUpperCase();
}
if (databaseMetaData.storesLowerCaseIdentifiers()) {
metaData.setCatalog(catalogName.toLowerCase());
metaData.setSchemaPattern(schemaName.toLowerCase());
convertTableName = tableName.toLowerCase();
}
ResultSet tableData = null;
try {
tableData = metaData.getTables(convertTableName);
while ((tableData != null) && tableData.next()) {
Map<String, Object> values = readColumns(tableData, initColumnsForTable());
table = readTable(metaData, values);
if (table.getName().equalsIgnoreCase(tableName)) {
break;
}
}
} finally {
JdbcUtils.closeResultSet(tableData);
}
} catch (Exception e) {
logger.error(e.getMessage(), e);
}
makeAllColumnsPrimaryKeysIfNoPrimaryKeysFound(table);
return table;
}
});
}
@SuppressWarnings("unchecked")
public static List<Table> findTables(final JdbcTemplate jdbcTemplate, final String catalogName,
final String schemaName, final String tableNamePattern,
final DdlUtilsFilter filter, final DdlTableNameFilter tableNameFilter)
throws Exception {
return (List<Table>) jdbcTemplate.execute(new ConnectionCallback() {
public Object doInConnection(Connection con) throws SQLException, DataAccessException {
List<Table> tables = new ArrayList<Table>();
DatabaseMetaDataWrapper metaData = new DatabaseMetaDataWrapper();
try {
if (filter != null) {
con = filter.filterConnection(con);
Assert.notNull(con);
}
DatabaseMetaData databaseMetaData = con.getMetaData();
if (filter != null) {
databaseMetaData = filter.filterDataBaseMetaData(jdbcTemplate, con, databaseMetaData);
Assert.notNull(databaseMetaData);
}
metaData.setMetaData(databaseMetaData);
metaData.setTableTypes(TableType.toStrings(SUPPORTED_TABLE_TYPES));
metaData.setCatalog(catalogName);
metaData.setSchemaPattern(schemaName);
String convertTableName = tableNamePattern;
if (databaseMetaData.storesUpperCaseIdentifiers()) {
metaData.setCatalog(catalogName.toUpperCase());
metaData.setSchemaPattern(schemaName.toUpperCase());
convertTableName = tableNamePattern.toUpperCase();
}
if (databaseMetaData.storesLowerCaseIdentifiers()) {
metaData.setCatalog(catalogName.toLowerCase());
metaData.setSchemaPattern(schemaName.toLowerCase());
convertTableName = tableNamePattern.toLowerCase();
}
ResultSet tableData = null;
try {
tableData = metaData.getTables(convertTableName);
while ((tableData != null) && tableData.next()) {
Map<String, Object> values = readColumns(tableData, initColumnsForTable());
Table table = readTable(metaData, values);
if ((tableNameFilter == null)
|| tableNameFilter.accept(catalogName, schemaName, table.getName())) {
tables.add(table);
}
}
} finally {
JdbcUtils.closeResultSet(tableData);
}
} catch (Exception e) {
logger.error(e.getMessage(), e);
}
for (Table table : tables) {
makeAllColumnsPrimaryKeysIfNoPrimaryKeysFound(table);
}
return tables;
}
});
}
/**
* Treat tables with no primary keys as a table with all primary keys.
*/
private static void makeAllColumnsPrimaryKeysIfNoPrimaryKeysFound(Table table) {
if ((table != null) && (table.getPrimaryKeyColumns() != null) && (table.getPrimaryKeyColumns().length == 0)) {
Column[] allCoumns = table.getColumns();
for (Column column : allCoumns) {
column.setPrimaryKey(true);
}
}
}
private static Table readTable(DatabaseMetaDataWrapper metaData, Map<String, Object> values) throws SQLException {
String tableName = (String) values.get("TABLE_NAME");
Table table = null;
if ((tableName != null) && (tableName.length() > 0)) {
table = new Table();
table.setName(tableName);
table.setType((String) values.get("TABLE_TYPE"));
table.setCatalog((String) values.get("TABLE_CAT"));
table.setSchema((String) values.get("TABLE_SCHEM"));
table.setDescription((String) values.get("REMARKS"));
table.addColumns(readColumns(metaData, tableName));
Collection<String> primaryKeys = readPrimaryKeyNames(metaData, tableName);
for (Object key : primaryKeys) {
Column col = table.findColumn((String) key, true);
if (col != null) {
col.setPrimaryKey(true);
} else {
throw new NullPointerException(
String.format("%s pk %s is null - %s %s",
tableName,
key,
ToStringBuilder.reflectionToString(metaData,
ToStringStyle.SIMPLE_STYLE),
ToStringBuilder.reflectionToString(values,
ToStringStyle.SIMPLE_STYLE)));
}
}
}
return table;
}
private static List<MetaDataColumnDescriptor> initColumnsForTable() {
List<MetaDataColumnDescriptor> result = new ArrayList<MetaDataColumnDescriptor>();
result.add(new MetaDataColumnDescriptor("TABLE_NAME", Types.VARCHAR));
result.add(new MetaDataColumnDescriptor("TABLE_TYPE", Types.VARCHAR, "UNKNOWN"));
result.add(new MetaDataColumnDescriptor("TABLE_CAT", Types.VARCHAR));
result.add(new MetaDataColumnDescriptor("TABLE_SCHEM", Types.VARCHAR));
result.add(new MetaDataColumnDescriptor("REMARKS", Types.VARCHAR));
return result;
}
private static List<MetaDataColumnDescriptor> initColumnsForColumn() {
List<MetaDataColumnDescriptor> result = new ArrayList<MetaDataColumnDescriptor>();
// As suggested by Alexandre Borgoltz, we're reading the COLUMN_DEF first because Oracle
// has problems otherwise (it seemingly requires a LONG column to be the first to be read)
// See also DDLUTILS-29
result.add(new MetaDataColumnDescriptor("COLUMN_DEF", Types.VARCHAR));
// we're also reading the table name so that a model reader impl can filter manually
result.add(new MetaDataColumnDescriptor("TABLE_NAME", Types.VARCHAR));
result.add(new MetaDataColumnDescriptor("COLUMN_NAME", Types.VARCHAR));
result.add(new MetaDataColumnDescriptor("TYPE_NAME", Types.VARCHAR));
result.add(new MetaDataColumnDescriptor("DATA_TYPE", Types.INTEGER, new Integer(java.sql.Types.OTHER)));
result.add(new MetaDataColumnDescriptor("NUM_PREC_RADIX", Types.INTEGER, new Integer(10)));
result.add(new MetaDataColumnDescriptor("DECIMAL_DIGITS", Types.INTEGER, new Integer(0)));
result.add(new MetaDataColumnDescriptor("COLUMN_SIZE", Types.VARCHAR));
result.add(new MetaDataColumnDescriptor("IS_NULLABLE", Types.VARCHAR, "YES"));
result.add(new MetaDataColumnDescriptor("REMARKS", Types.VARCHAR));
return result;
}
private static List<MetaDataColumnDescriptor> initColumnsForPK() {
List<MetaDataColumnDescriptor> result = new ArrayList<MetaDataColumnDescriptor>();
result.add(new MetaDataColumnDescriptor("COLUMN_NAME", Types.VARCHAR));
// we're also reading the table name so that a model reader impl can filter manually
result.add(new MetaDataColumnDescriptor("TABLE_NAME", Types.VARCHAR));
// the name of the primary key is currently only interesting to the pk index name resolution
result.add(new MetaDataColumnDescriptor("PK_NAME", Types.VARCHAR));
return result;
}
private static List<Column> readColumns(DatabaseMetaDataWrapper metaData, String tableName) throws SQLException {
ResultSet columnData = null;
try {
columnData = metaData.getColumns(tableName, null);
List<Column> columns = new ArrayList<Column>();
Map<String, Object> values = null;
for (; columnData.next(); columns.add(readColumn(metaData, values))) {
Map<String, Object> tmp = readColumns(columnData, initColumnsForColumn());
if (tableName.equalsIgnoreCase((String) tmp.get("TABLE_NAME"))) {
values = tmp;
} else {
break;
}
}
// if (true == columns.isEmpty()) {
// Connection con = metaData.getMetaData().getConnection();
// String propIncludeSynonyms = "includeSynonyms";
//
// if (PropertyUtils.isWriteable(con, propIncludeSynonyms)) {
// try {
// String includeSynonymsMethodName = "setIncludeSynonyms";
// boolean previousSynonyms = (Boolean) PropertyUtils.getProperty(con, propIncludeSynonyms);
//
// if (logger.isInfoEnabled()) {
// logger.info("ORACLE: switch includeSynonyms to " + previousSynonyms);
// }
//
// try {
// MethodUtils.invokeMethod(con, includeSynonymsMethodName, !previousSynonyms);
// columns = readColumns(metaData, tableName);
//
// if (false == columns.isEmpty()) {
// return columns;
// }
// } finally {
// MethodUtils.invokeMethod(con, includeSynonymsMethodName, previousSynonyms);
// }
//
// throw new SQLException("ORACLE: no column found for " + tableName);
// } catch (IllegalAccessException e) {
// logger.error(e.getMessage(), e);
// } catch (InvocationTargetException e) {
// logger.error(e.getMessage(), e);
// } catch (NoSuchMethodException e) {
// logger.error(e.getMessage(), e);
// }
// }
// }
return columns;
} finally {
JdbcUtils.closeResultSet(columnData);
}
}
private static Column readColumn(DatabaseMetaDataWrapper metaData, Map<String, Object> values) throws SQLException {
Column column = new Column();
column.setName((String) values.get("COLUMN_NAME"));
column.setDefaultValue((String) values.get("COLUMN_DEF"));
column.setTypeCode(((Integer) values.get("DATA_TYPE")).intValue());
String typeName = (String) values.get("TYPE_NAME");
if ((typeName != null) && typeName.startsWith("TIMESTAMP")) {
column.setTypeCode(Types.TIMESTAMP);
}
Integer precision = (Integer) values.get("NUM_PREC_RADIX");
if (precision != null) {
column.setPrecisionRadix(precision.intValue());
}
String size = (String) values.get("COLUMN_SIZE");
if (size == null) {
size = (String) _defaultSizes.get(new Integer(column.getTypeCode()));
}
// we're setting the size after the precision and radix in case
// the database prefers to return them in the size value
column.setSize(size);
int scale = 0;
Object dec_digits = values.get("DECIMAL_DIGITS");
if (dec_digits instanceof String) {
scale = (dec_digits == null) ? 0 : NumberUtils.toInt(dec_digits.toString());
} else if (dec_digits instanceof Integer) {
scale = (dec_digits == null) ? 0 : (Integer) dec_digits;
}
if (scale != 0) {
column.setScale(scale);
}
column.setRequired("NO".equalsIgnoreCase(((String) values.get("IS_NULLABLE")).trim()));
column.setDescription((String) values.get("REMARKS"));
return column;
}
private static Map<String, Object> readColumns(ResultSet resultSet, List<MetaDataColumnDescriptor> columnDescriptors)
throws SQLException {
Map<String, Object> values = new HashMap<String, Object>();
MetaDataColumnDescriptor descriptor;
for (Iterator<MetaDataColumnDescriptor> it = columnDescriptors.iterator(); it.hasNext(); values.put(descriptor.getName(),
descriptor.readColumn(resultSet))) {
descriptor = (MetaDataColumnDescriptor) it.next();
}
return values;
}
private static Collection<String> readPrimaryKeyNames(DatabaseMetaDataWrapper metaData, String tableName)
throws SQLException {
ResultSet pkData = null;
try {
List<String> pks = new ArrayList<String>();
Map<String, Object> values;
for (pkData = metaData.getPrimaryKeys(tableName); pkData.next(); pks.add(readPrimaryKeyName(metaData,
values))) {
values = readColumns(pkData, initColumnsForPK());
}
return pks;
} finally {
JdbcUtils.closeResultSet(pkData);
}
}
private static String readPrimaryKeyName(DatabaseMetaDataWrapper metaData, Map<String, Object> values)
throws SQLException {
return (String) values.get("COLUMN_NAME");
}
}