package org.apache.ddlutils.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 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 java.util.regex.PatternSyntaxException; | |
import org.apache.ddlutils.DdlUtilsException; | |
import org.apache.ddlutils.Platform; | |
import org.apache.ddlutils.model.Column; | |
import org.apache.ddlutils.model.Index; | |
import org.apache.ddlutils.model.Table; | |
import org.apache.ddlutils.model.TypeMap; | |
import org.apache.ddlutils.platform.DatabaseMetaDataWrapper; | |
import org.apache.ddlutils.platform.JdbcModelReader; | |
/** | |
* Reads a database model from a Microsoft Sql Server database. | |
* | |
* @version $Revision: $ | |
*/ | |
public class MSSqlModelReader extends JdbcModelReader | |
{ | |
/** 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; | |
/** The regular expression pattern for the ISO times. */ | |
private Pattern _isoTimePattern; | |
/** | |
* Creates a new model reader for Microsoft Sql Server databases. | |
* | |
* @param platform The platform that this model reader belongs to | |
*/ | |
public MSSqlModelReader(Platform platform) | |
{ | |
super(platform); | |
setDefaultCatalogPattern(null); | |
setDefaultSchemaPattern(null); | |
setDefaultTablePattern("%"); | |
try | |
{ | |
_isoDatePattern = Pattern.compile("'(\\d{4}\\-\\d{2}\\-\\d{2})'"); | |
_isoTimePattern = Pattern.compile("'(\\d{2}:\\d{2}:\\d{2})'"); | |
} | |
catch (PatternSyntaxException ex) | |
{ | |
throw new DdlUtilsException(ex); | |
} | |
} | |
/** | |
* {@inheritDoc} | |
*/ | |
protected Table readTable(DatabaseMetaDataWrapper metaData, Map 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(metaData, values); | |
if (table != null) | |
{ | |
// Sql Server does not return the auto-increment status via the database metadata | |
determineAutoIncrementFromResultSetMetaData(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();) | |
{ | |
Index index = table.getIndex(idx); | |
if (index.isUnique() && existsPKWithName(metaData, table, index.getName())) | |
{ | |
table.removeIndex(idx); | |
} | |
else | |
{ | |
idx++; | |
} | |
} | |
} | |
return table; | |
} | |
/** | |
* {@inheritDoc} | |
*/ | |
protected boolean isInternalPrimaryKeyIndex(DatabaseMetaDataWrapper metaData, Table table, Index 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) throws SQLException | |
{ | |
ResultSet pks = null; | |
try | |
{ | |
pks = metaData.getPrimaryKeys(table.getName()); | |
while (pks.next()) | |
{ | |
if (name.equals(pks.getString("PK_NAME"))) | |
{ | |
return true; | |
} | |
} | |
return false; | |
} | |
finally | |
{ | |
closeResultSet(pks); | |
} | |
} | |
/** | |
* {@inheritDoc} | |
*/ | |
protected Column readColumn(DatabaseMetaDataWrapper metaData, Map 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.getTypeCode() == 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.getTypeCode() == Types.DECIMAL) | |
{ | |
// 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.getTypeCode())) | |
{ | |
defaultValue = unescape(defaultValue, "'", "''"); | |
} | |
column.setDefaultValue(defaultValue); | |
} | |
if ((column.getTypeCode() == Types.DECIMAL) && (column.getSizeAsInt() == 19) && (column.getScale() == 0)) | |
{ | |
column.setTypeCode(Types.BIGINT); | |
} | |
return column; | |
} | |
} |