package org.jumpmind.db.platform.mssql;
/*
* Licensed to the Apache Software Foundation (ASF) under one
* or more contributor license agreements. See the NOTICE file
* distributed with this work for additional information
* regarding copyright ownership. The ASF licenses this file
* to you 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.
*/
import static org.apache.commons.lang.StringUtils.isNotBlank;
import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Time;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.jumpmind.db.model.Column;
import org.jumpmind.db.model.IIndex;
import org.jumpmind.db.model.PlatformColumn;
import org.jumpmind.db.model.Table;
import org.jumpmind.db.model.TypeMap;
import org.jumpmind.db.platform.AbstractJdbcDdlReader;
import org.jumpmind.db.platform.DatabaseMetaDataWrapper;
import org.jumpmind.db.platform.DdlException;
import org.jumpmind.db.platform.IDatabasePlatform;
/*
* Reads a database model from a Microsoft Sql Server database.
*/
public class MsSqlDdlReader extends AbstractJdbcDdlReader {
/* Known system tables that Sql Server creates (e.g. automatic maintenance). */
private static final String[] KNOWN_SYSTEM_TABLES = { "dtproperties" };
/* The regular expression pattern for the ISO dates. */
private Pattern isoDatePattern = Pattern.compile("'(\\d{4}\\-\\d{2}\\-\\d{2})'");
/* The regular expression pattern for the ISO times. */
private Pattern isoTimePattern = Pattern.compile("'(\\d{2}:\\d{2}:\\d{2})'");
public MsSqlDdlReader(IDatabasePlatform platform) {
super(platform);
setDefaultCatalogPattern(null);
setDefaultSchemaPattern(null);
setDefaultTablePattern("%");
}
@Override
protected Table readTable(Connection connection, DatabaseMetaDataWrapper metaData,
Map<String, Object> values) throws SQLException {
String tableName = (String) values.get("TABLE_NAME");
for (int idx = 0; idx < KNOWN_SYSTEM_TABLES.length; idx++) {
if (KNOWN_SYSTEM_TABLES[idx].equals(tableName)) {
return null;
}
}
Table table = super.readTable(connection, metaData, values);
if (table != null) {
// Sql Server does not return the auto-increment status via the
// database metadata
determineAutoIncrementFromResultSetMetaData(connection, table, table.getColumns());
// TODO: Replace this manual filtering using named pks once they are
// available
// This is then probably of interest to every platform
for (int idx = 0; idx < table.getIndexCount();) {
IIndex index = table.getIndex(idx);
if (index.isUnique() && existsPKWithName(metaData, table, index.getName())) {
table.removeIndex(idx);
} else {
idx++;
}
}
}
return table;
}
@Override
protected boolean isInternalPrimaryKeyIndex(Connection connection,
DatabaseMetaDataWrapper metaData, Table table, IIndex index) {
// Sql Server generates an index "PK__[table name]__[hex number]"
StringBuffer pkIndexName = new StringBuffer();
pkIndexName.append("PK__");
pkIndexName.append(table.getName());
pkIndexName.append("__");
return index.getName().toUpperCase().startsWith(pkIndexName.toString().toUpperCase());
}
/*
* Determines whether there is a pk for the table with the given name.
*
* @param metaData The database metadata
*
* @param table The table
*
* @param name The pk name
*
* @return <code>true</code> if there is such a pk
*/
private boolean existsPKWithName(DatabaseMetaDataWrapper metaData, Table table, String name) {
try {
ResultSet pks = metaData.getPrimaryKeys(table.getName());
boolean found = false;
while (pks.next() && !found) {
if (name.equals(pks.getString("PK_NAME"))) {
found = true;
}
}
pks.close();
return found;
} catch (SQLException ex) {
throw new DdlException(ex);
}
}
protected Integer mapUnknownJdbcTypeForColumn(Map<String, Object> values) {
String typeName = (String) values.get("TYPE_NAME");
int size = -1;
String columnSize = (String) values.get("COLUMN_SIZE");
if (isNotBlank(columnSize)) {
size = Integer.parseInt(columnSize);
}
if (typeName != null && typeName.toLowerCase().startsWith("text")) {
return Types.LONGVARCHAR;
} else if (typeName != null && typeName.toLowerCase().startsWith("ntext")) {
return Types.CLOB;
} else if (typeName != null && typeName.toUpperCase().contains(TypeMap.GEOMETRY)) {
return Types.VARCHAR;
} else if (typeName != null && typeName.toUpperCase().contains("VARCHAR") && size > 8000) {
return Types.LONGVARCHAR;
} else if (typeName != null && typeName.toUpperCase().contains("NVARCHAR") && size > 8000) {
return Types.LONGNVARCHAR;
} else if (typeName != null && typeName.toUpperCase().equals("SQL_VARIANT")) {
return Types.BINARY;
} else {
return super.mapUnknownJdbcTypeForColumn(values);
}
}
@Override
protected Column readColumn(DatabaseMetaDataWrapper metaData, Map<String, Object> values)
throws SQLException {
Column column = super.readColumn(metaData, values);
String defaultValue = column.getDefaultValue();
// Sql Server tends to surround the returned default value with one or
// two sets of parentheses
if (defaultValue != null) {
while (defaultValue.startsWith("(") && defaultValue.endsWith(")")) {
defaultValue = defaultValue.substring(1, defaultValue.length() - 1);
}
if (column.getMappedTypeCode() == Types.TIMESTAMP) {
// Sql Server maintains the default values for DATE/TIME jdbc
// types, so we have to
// migrate the default value to TIMESTAMP
Matcher matcher = isoDatePattern.matcher(defaultValue);
Timestamp timestamp = null;
if (matcher.matches()) {
timestamp = new Timestamp(Date.valueOf(matcher.group(1)).getTime());
} else {
matcher = isoTimePattern.matcher(defaultValue);
if (matcher.matches()) {
timestamp = new Timestamp(Time.valueOf(matcher.group(1)).getTime());
}
}
if (timestamp != null) {
defaultValue = timestamp.toString();
}
} else if (column.getMappedTypeCode() == Types.DECIMAL ||
column.getMappedTypeCode() == Types.BIGINT) {
// For some reason, Sql Server 2005 always returns DECIMAL
// default values with a dot
// even if the scale is 0, so we remove the dot
if ((column.getScale() == 0) && defaultValue.endsWith(".")) {
defaultValue = defaultValue.substring(0, defaultValue.length() - 1);
}
} else if (TypeMap.isTextType(column.getMappedTypeCode())) {
if (defaultValue.startsWith("N'") && defaultValue.endsWith("'")) {
defaultValue = defaultValue.substring(2, defaultValue.length()-1);
}
defaultValue = unescape(defaultValue, "'", "''");
}
column.setDefaultValue(defaultValue);
}
if ((column.getMappedTypeCode() == Types.DECIMAL) && (column.getSizeAsInt() == 19)
&& (column.getScale() == 0)) {
column.setMappedTypeCode(Types.BIGINT);
}
// These columns return sizes and/or decimal places with the metat data from MSSql Server however
// the values are not adjustable through the create table so they are omitted
if (column.getJdbcTypeName() != null &&
(column.getJdbcTypeName().equals("smallmoney")
|| column.getJdbcTypeName().equals("money")
|| column.getJdbcTypeName().equals("timestamp")
|| column.getJdbcTypeName().equals("uniqueidentifier")
|| column.getJdbcTypeName().equals("time")
|| column.getJdbcTypeName().equals("datetime2")
|| column.getJdbcTypeName().equals("date"))) {
removePlatformSizeAndDecimal(column);
}
return column;
}
protected void removePlatformSizeAndDecimal(Column column) {
for (PlatformColumn platformColumn : column.getPlatformColumns().values()) {
platformColumn.setSize(-1);
platformColumn.setDecimalDigits(-1);
}
}
}