sybasemodelreader.java

OBPM是一个开源

package org.apache.ddlutils.platform.sybase;

/*
 * 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.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Map;

import org.apache.ddlutils.DdlUtilsException;
import org.apache.ddlutils.Platform;
import org.apache.ddlutils.model.Column;
import org.apache.ddlutils.model.ForeignKey;
import org.apache.ddlutils.model.Index;
import org.apache.ddlutils.model.Reference;
import org.apache.ddlutils.model.Table;
import org.apache.ddlutils.model.TypeMap;
import org.apache.ddlutils.platform.DatabaseMetaDataWrapper;
import org.apache.ddlutils.platform.JdbcModelReader;
import org.apache.oro.text.regex.MalformedPatternException;
import org.apache.oro.text.regex.Pattern;
import org.apache.oro.text.regex.PatternCompiler;
import org.apache.oro.text.regex.PatternMatcher;
import org.apache.oro.text.regex.Perl5Compiler;
import org.apache.oro.text.regex.Perl5Matcher;

/**
 * Reads a database model from a Sybase database.
 *
 * @version $Revision: $
 */
public class SybaseModelReader extends JdbcModelReader
{
	/** 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 Sybase databases.
     * 
     * @param platform The platform that this model reader belongs to
     */
    public SybaseModelReader(Platform platform)
    {
        super(platform);
        setDefaultCatalogPattern(null);
        setDefaultSchemaPattern(null);
        setDefaultTablePattern("%");

        PatternCompiler compiler = new Perl5Compiler();

    	try
    	{
            _isoDatePattern = compiler.compile("'(\\d{4}\\-\\d{2}\\-\\d{2})'");
            _isoTimePattern = compiler.compile("'(\\d{2}:\\d{2}:\\d{2})'");
        }
    	catch (MalformedPatternException ex)
        {
        	throw new DdlUtilsException(ex);
        }
    }

    /**
     * {@inheritDoc}
     */
	protected Table readTable(DatabaseMetaDataWrapper metaData, Map values) throws SQLException
	{
        Table table = super.readTable(metaData, values);

        if (table != null)
        {
            // Sybase does not return the auto-increment status via the database metadata
            determineAutoIncrementFromResultSetMetaData(table, table.getColumns());
        }
        return table;
	}

	/**
     * {@inheritDoc}
     */
    protected Column readColumn(DatabaseMetaDataWrapper metaData, Map values) throws SQLException
    {
		Column column = super.readColumn(metaData, values);

		if ((column.getTypeCode() == Types.DECIMAL) && (column.getSizeAsInt() == 19) && (column.getScale() == 0))
		{
			// Back-mapping to BIGINT
			column.setTypeCode(Types.BIGINT);
		}
        else if (column.getDefaultValue() != null)
        {
    		if (column.getTypeCode() == Types.TIMESTAMP)
    		{
    			// Sybase maintains the default values for DATE/TIME jdbc types, so we have to
    			// migrate the default value to TIMESTAMP
    			PatternMatcher matcher   = new Perl5Matcher();
    			Timestamp      timestamp = null;
    
    			if (matcher.matches(column.getDefaultValue(), _isoDatePattern))
    			{
    				timestamp = new Timestamp(Date.valueOf(matcher.getMatch().group(1)).getTime());
    			}
    			else if (matcher.matches(column.getDefaultValue(), _isoTimePattern))
    			{
    				timestamp = new Timestamp(Time.valueOf(matcher.getMatch().group(1)).getTime());
    			}
    			if (timestamp != null)
    			{
    				column.setDefaultValue(timestamp.toString());
    			}
    		}
            else if (TypeMap.isTextType(column.getTypeCode()))
            {
                column.setDefaultValue(unescape(column.getDefaultValue(), "'", "''"));
            }
        }
		return column;
	}

    /**
	 * {@inheritDoc}
	 */
	protected void readIndex(DatabaseMetaDataWrapper metaData, Map values, Map knownIndices) throws SQLException
	{
		if (getPlatform().isDelimitedIdentifierModeOn())
		{
	        String indexName = (String)values.get("INDEX_NAME");

	        // Sometimes, Sybase keeps the delimiter quotes around the index names
	        // when returning them in the metadata, so we strip them
	        if (indexName != null)
	        {
		        String delimiter = getPlatformInfo().getDelimiterToken();

				if ((indexName != null) && indexName.startsWith(delimiter) && indexName.endsWith(delimiter))
				{
					indexName = indexName.substring(delimiter.length(), indexName.length() - delimiter.length());
					values.put("INDEX_NAME", indexName);
				}
	        }
		}
		super.readIndex(metaData, values, knownIndices);
	}

	/**
     * {@inheritDoc}
     */
    protected Collection readForeignKeys(DatabaseMetaDataWrapper metaData, String tableName) throws SQLException
    {
        // Sybase (or jConnect) does not return the foreign key names, thus we have to
        // read the foreign keys manually from the system tables
        StringBuffer query = new StringBuffer();

        query.append("SELECT refobjs.name, localtables.id, remotetables.name, remotetables.id");
        for (int idx = 1; idx <= 16; idx++)
        {
            query.append(", refs.fokey");
            query.append(idx);
            query.append(", refs.refkey");
            query.append(idx);
        }
        query.append(" FROM sysreferences refs, sysobjects refobjs, sysobjects localtables, sysobjects remotetables");
        query.append(" WHERE refobjs.type = 'RI' AND refs.constrid = refobjs.id AND");
        query.append(" localtables.type = 'U' AND refs.tableid = localtables.id AND localtables.name = '");
        query.append(tableName);
        query.append("' AND remotetables.type = 'U' AND refs.reftabid = remotetables.id");

        Statement         stmt     = getConnection().createStatement();
        PreparedStatement prepStmt = getConnection().prepareStatement("SELECT name FROM syscolumns WHERE id = ? AND colid = ?");
        ArrayList         result   = new ArrayList();

        try
        {
            ResultSet fkRs = stmt.executeQuery(query.toString());

            while (fkRs.next())
            {
                ForeignKey fk            = new ForeignKey(fkRs.getString(1));
                int        localTableId  = fkRs.getInt(2);
                int        remoteTableId = fkRs.getInt(4);

                fk.setForeignTableName(fkRs.getString(3));
                for (int idx = 0; idx < 16; idx++)
                {
                    short     fkColIdx = fkRs.getShort(5 + idx + idx);
                    short     pkColIdx = fkRs.getShort(6 + idx + idx);
                    Reference ref      = new Reference();

                    if (fkColIdx == 0)
                    {
                        break;
                    }

                    prepStmt.setInt(1, localTableId);
                    prepStmt.setShort(2, fkColIdx);

                    ResultSet colRs = prepStmt.executeQuery();

                    if (colRs.next())
                    {
                        ref.setLocalColumnName(colRs.getString(1));
                    }
                    colRs.close();

                    prepStmt.setInt(1, remoteTableId);
                    prepStmt.setShort(2, pkColIdx);

                    colRs = prepStmt.executeQuery();

                    if (colRs.next())
                    {
                        ref.setForeignColumnName(colRs.getString(1));
                    }
                    colRs.close();

                    fk.addReference(ref);
                }
                result.add(fk);
            }

            fkRs.close();
        }
        finally
        {
            stmt.close();
            prepStmt.close();
        }

        return result;
    }

    /**
     * {@inheritDoc}
     */
    protected boolean isInternalPrimaryKeyIndex(DatabaseMetaDataWrapper metaData, Table table, Index index) throws SQLException
    {
        // We can simply check the sysindexes table where a specific flag is set for pk indexes
        StringBuffer query = new StringBuffer();

        query.append("SELECT name = sysindexes.name FROM sysindexes, sysobjects WHERE sysobjects.name = '");
        query.append(table.getName());
        query.append("' AND sysindexes.name = '");
        query.append(index.getName());
        query.append("' AND sysobjects.id = sysindexes.id AND (sysindexes.status & 2048) > 0");
        
        Statement stmt = getConnection().createStatement();

        try
        {
            ResultSet rs     = stmt.executeQuery(query.toString());
            boolean   result = rs.next();

            rs.close();
            return result;
        }
        finally
        {
            stmt.close();
        }
    }
}