src/main/java/org/apache/ddlutils/platform/mssql/MSSqlModelReader.java

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.DatabaseMetaData;
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.CascadeActionEnum;
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;
	}

    /**
     * {@inheritDoc}
     */
    protected CascadeActionEnum convertAction(Short jdbcActionValue, CascadeActionEnum defaultAction)
    {
        CascadeActionEnum action = defaultAction;

        // for whatever reason, the sql server jdbc driver returns restrict even though the DB does not support RESTRICT
        if ((jdbcActionValue != null) && (jdbcActionValue.shortValue() == DatabaseMetaData.importedKeyCascade))
        {
            action = CascadeActionEnum.CASCADE;
        }
        return action;
    }
}