net.sourceforge.squirrel_sql.fw.dialects.DB2DialectExt.java Source code

Java tutorial

Introduction

Here is the source code for net.sourceforge.squirrel_sql.fw.dialects.DB2DialectExt.java

Source

/*
 * Copyright (C) 2006 Rob Manning
 * manningr@users.sourceforge.net
 *
 * This program is free software; you can redistribute it and/or
 * modify it under the terms of the GNU General Public License
 * as published by the Free Software Foundation; either version 2
 * of the License, or any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
 */
package net.sourceforge.squirrel_sql.fw.dialects;

import static net.sourceforge.squirrel_sql.fw.dialects.DialectUtils.CYCLE_CLAUSE;
import static net.sourceforge.squirrel_sql.fw.dialects.DialectUtils.NO_CYCLE_CLAUSE;

import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;

import net.sourceforge.squirrel_sql.fw.sql.DatabaseObjectType;
import net.sourceforge.squirrel_sql.fw.sql.IDatabaseObjectInfo;
import net.sourceforge.squirrel_sql.fw.sql.ISQLDatabaseMetaData;
import net.sourceforge.squirrel_sql.fw.sql.ITableInfo;
import net.sourceforge.squirrel_sql.fw.sql.JDBCTypeMapper;
import net.sourceforge.squirrel_sql.fw.sql.TableColumnInfo;

import org.antlr.stringtemplate.StringTemplate;
import org.apache.commons.lang.StringUtils;
import org.hibernate.HibernateException;

/**
 * An extension to the standard Hibernate DB2 dialect
 */
public class DB2DialectExt extends CommonHibernateDialect implements HibernateDialect {

    private class DB2DialectHelper extends org.hibernate.dialect.DB2Dialect {
        public DB2DialectHelper() {
            super();
            registerColumnType(Types.BIGINT, "bigint");
            registerColumnType(Types.BINARY, 254, "char($l) for bit data");
            registerColumnType(Types.BINARY, "blob");
            registerColumnType(Types.BIT, "smallint");
            // DB2 spec says max=2147483647, but the driver throws an exception
            registerColumnType(Types.BLOB, 1073741823, "blob($l)");
            registerColumnType(Types.BLOB, "blob(1073741823)");
            registerColumnType(Types.BOOLEAN, "smallint");
            registerColumnType(Types.CHAR, 254, "char($l)");
            registerColumnType(Types.CHAR, 4000, "varchar($l)");
            registerColumnType(Types.CHAR, 32700, "long varchar");
            registerColumnType(Types.CHAR, 1073741823, "clob($l)");
            registerColumnType(Types.CHAR, "clob(1073741823)");
            // DB2 spec says max=2147483647, but the driver throws an exception
            registerColumnType(Types.CLOB, 1073741823, "clob($l)");
            registerColumnType(Types.CLOB, "clob(1073741823)");
            registerColumnType(Types.DATE, "date");
            registerColumnType(Types.DECIMAL, "decimal($p,$s)");
            registerColumnType(Types.DOUBLE, "float($p)");
            registerColumnType(Types.FLOAT, "float($p)");
            registerColumnType(Types.INTEGER, "int");
            registerColumnType(Types.LONGVARBINARY, 32700, "long varchar for bit data");
            registerColumnType(Types.LONGVARBINARY, 1073741823, "blob($l)");
            registerColumnType(Types.LONGVARBINARY, "blob(1073741823)");
            registerColumnType(Types.LONGVARCHAR, 32700, "long varchar");
            // DB2 spec says max=2147483647, but the driver throws an exception
            registerColumnType(Types.LONGVARCHAR, 1073741823, "clob($l)");
            registerColumnType(Types.LONGVARCHAR, "clob(1073741823)");
            registerColumnType(Types.NUMERIC, "bigint");
            registerColumnType(Types.REAL, "real");
            registerColumnType(Types.SMALLINT, "smallint");
            registerColumnType(Types.TIME, "time");
            registerColumnType(Types.TIMESTAMP, "timestamp");
            registerColumnType(Types.TINYINT, "smallint");
            registerColumnType(Types.VARBINARY, 254, "varchar($l) for bit data");
            registerColumnType(Types.VARBINARY, "blob");
            // The driver throws an exception for varchar with length > 3924
            registerColumnType(Types.VARCHAR, 3924, "varchar($l)");
            registerColumnType(Types.VARCHAR, 32700, "long varchar");
            // DB2 spec says max=2147483647, but the driver throws an exception
            registerColumnType(Types.VARCHAR, 1073741823, "clob($l)");
            registerColumnType(Types.VARCHAR, "clob(1073741823)");

            // The registrations below are made in support for new types introduced in Java6

            // Replace "-8" with Types.ROWID when Java6 is the minimum supported version
            registerColumnType(-8, "int");
            // Replace "-9" with Types.NVARCHAR when Java6 is the minimum supported version
            registerColumnType(-9, 1073741823, "clob($l)");
            registerColumnType(-9, "clob(1073741823)");

            // Replace "-15" with Types.NCHAR when Java6 is the minimum supported version
            registerColumnType(-15, "char($l)");
            // Replace "-16" with Types.LONGNVARCHAR when Java6 is the minimum supported version
            registerColumnType(-16, "longvarchar");
            // Replace "2009" with Types.SQLXML when Java6 is the minimum supported version
            registerColumnType(2009, "clob");
            // Replace "2011" with Types.NCLOB when Java6 is the minimum supported version
            registerColumnType(2011, "clob");

        }
    }

    /** extended hibernate dialect used in this wrapper */
    private final DB2DialectHelper _dialect = new DB2DialectHelper();

    /**
     * @see net.sourceforge.squirrel_sql.fw.dialects.CommonHibernateDialect#getTypeName(int, int, int, int)
     */
    @Override
    public String getTypeName(int code, int length, int precision, int scale) throws HibernateException {
        return _dialect.getTypeName(code, length, precision, scale);
    }

    /**
     * @see net.sourceforge.squirrel_sql.fw.dialects.CommonHibernateDialect#canPasteTo(net.sourceforge.squirrel_sql.fw.sql.IDatabaseObjectInfo)
     */
    @Override
    public boolean canPasteTo(IDatabaseObjectInfo info) {
        boolean result = true;
        final DatabaseObjectType type = info.getDatabaseObjectType();
        if (type.getName().equalsIgnoreCase("database")) {
            result = false;
        }
        return result;
    }

    /**
     * @see net.sourceforge.squirrel_sql.fw.dialects.HibernateDialect#getLengthFunction(int)
     */
    public String getLengthFunction(int dataType) {
        return "length";
    }

    /**
     * @see net.sourceforge.squirrel_sql.fw.dialects.HibernateDialect#getMaxFunction()
     */
    public String getMaxFunction() {
        return "max";
    }

    /**
     * @see net.sourceforge.squirrel_sql.fw.dialects.HibernateDialect#getMaxPrecision(int)
     */
    public int getMaxPrecision(int dataType) {
        if (dataType == Types.DOUBLE || dataType == Types.FLOAT) {
            return 53;
        } else {
            return 31;
        }
    }

    /**
     * @see net.sourceforge.squirrel_sql.fw.dialects.HibernateDialect#getMaxScale(int)
     */
    public int getMaxScale(int dataType) {
        if (dataType == Types.DOUBLE || dataType == Types.FLOAT) {
            // double and float have no scale - that is DECIMAL_DIGITS is null.
            // Assume that is because it's variable - "floating" point.
            return 0;
        } else {
            return getMaxPrecision(dataType);
        }
    }

    /**
     * @see net.sourceforge.squirrel_sql.fw.dialects.HibernateDialect#getPrecisionDigits(int, int)
     */
    public int getPrecisionDigits(int columnSize, int dataType) {
        return columnSize;
    }

    /**
     * @see net.sourceforge.squirrel_sql.fw.dialects.HibernateDialect#getColumnLength(int, int)
     */
    public int getColumnLength(int columnSize, int dataType) {
        return columnSize;
    }

    /**
     * The string which identifies this dialect in the dialect chooser.
     * 
     * @return a descriptive name that tells the user what database this dialect is design to work with.
     */
    public String getDisplayName() {
        return "DB2";
    }

    /**
     * Returns boolean value indicating whether or not this dialect supports the specified database
     * product/version.
     * 
     * @param databaseProductName
     *           the name of the database as reported by DatabaseMetaData.getDatabaseProductName()
     * @param databaseProductVersion
     *           the version of the database as reported by DatabaseMetaData.getDatabaseProductVersion()
     * @return true if this dialect can be used for the specified product name and version; false otherwise.
     */
    public boolean supportsProduct(String databaseProductName, String databaseProductVersion) {
        if (databaseProductName == null) {
            return false;
        }
        if (databaseProductName.trim().startsWith("DB2")) {
            // We don't yet have the need to discriminate by version.
            return true;
        }
        return false;
    }

    /**
     * Returns the SQL statement to use to add a column to the specified table using the information about the
     * new column specified by info.
     * 
     * @param info
     *           information about the new column such as type, name, etc.
     * @return
     * @throws UnsupportedOperationException
     *            if the database doesn't support adding columns after a table has already been created.
     */
    public String[] getAddColumnSQL(TableColumnInfo info, DatabaseObjectQualifier qualifier,
            SqlGenerationPreferences prefs) throws UnsupportedOperationException {
        final String qualifedTableName = DialectUtils.shapeQualifiableIdentifier(info.getTableName(), qualifier,
                prefs, this);
        final String shapedColumnName = DialectUtils.shapeIdentifier(info.getColumnName(), prefs, this);

        final ArrayList<String> result = new ArrayList<String>();

        final StringBuffer addColumn = new StringBuffer();
        addColumn.append("ALTER TABLE ");
        addColumn.append(qualifedTableName);
        addColumn.append(" ADD ");
        addColumn.append(shapedColumnName);
        addColumn.append(" ");
        addColumn.append(getTypeName(info.getDataType(), info.getColumnSize(), info.getColumnSize(),
                info.getDecimalDigits()));
        if (info.getDefaultValue() != null) {
            addColumn.append(" WITH DEFAULT ");
            if (JDBCTypeMapper.isNumberType(info.getDataType())) {
                addColumn.append(info.getDefaultValue());
            } else {
                addColumn.append("'");
                addColumn.append(info.getDefaultValue());
                addColumn.append("'");
            }
        }
        result.add(addColumn.toString());

        if (info.isNullable() == "NO") {
            // ALTER TABLE <TABLENAME> ADD CONSTRAINT NULL_FIELD CHECK (<FIELD> IS NOT
            // NULL)
            final StringBuffer notnull = new StringBuffer();
            notnull.append("ALTER TABLE ");
            notnull.append(qualifedTableName);
            notnull.append(" ADD CONSTRAINT ");
            // TODO: should the constraint name simply be the column name or something more like a constraint
            // name?
            notnull.append(shapedColumnName);
            notnull.append(" CHECK (");
            notnull.append(shapedColumnName);
            notnull.append(" IS NOT NULL)");
            result.add(notnull.toString());
        }

        if (info.getRemarks() != null && !"".equals(info.getRemarks())) {
            result.add(getColumnCommentAlterSQL(info, qualifier, prefs));
        }

        return result.toArray(new String[result.size()]);

    }

    /**
     * Returns the SQL statement to use to add a comment to the specified column of the specified table.
     * 
     * @param tableName
     *           the name of the table to create the SQL for.
     * @param columnName
     *           the name of the column to create the SQL for.
     * @param comment
     *           the comment to add.
     * @param qualifier
     *           qualifier of the table
     * @param prefs
     *           preferences for generated sql scripts
     * @param dialect
     *           the HibernateDialect for the target database
     * @return
     * @throws UnsupportedOperationException
     *            if the database doesn't support annotating columns with a comment.
     */
    public String getColumnCommentAlterSQL(String tableName, String columnName, String comment,
            DatabaseObjectQualifier qualifier, SqlGenerationPreferences prefs, HibernateDialect dialect)
            throws UnsupportedOperationException {
        return DialectUtils.getColumnCommentAlterSQL(tableName, columnName, comment, qualifier, prefs, dialect);
    }

    /**
     * Returns a boolean value indicating whether or not this database dialect supports dropping columns from
     * tables.
     * 
     * @return true if the database supports dropping columns; false otherwise.
     */
    public boolean supportsDropColumn() {
        return true;
    }

    /**
     * Returns the SQL that forms the command to drop the specified colum in the specified table.
     * 
     * @param tableName
     *           the name of the table that has the column
     * @param columnName
     *           the name of the column to drop.
     * @return
     * @throws UnsupportedOperationException
     *            if the database doesn't support dropping columns.
     */
    public String getColumnDropSQL(String tableName, String columnName, DatabaseObjectQualifier qualifier,
            SqlGenerationPreferences prefs) {
        // alter table <tablename> drop column <columnName>
        return DialectUtils.getColumnDropSQL(tableName, columnName, qualifier, prefs, this);
    }

    /**
     * Returns the SQL that forms the command to drop the specified table. If cascade contraints is supported
     * by the dialect and cascadeConstraints is true, then a drop statement with cascade constraints clause
     * will be formed.
     * 
     * @param iTableInfo
     *           the table to drop
     * @param cascadeConstraints
     *           whether or not to drop any FKs that may reference the specified table.
     * @return the drop SQL command.
     */
    public List<String> getTableDropSQL(ITableInfo iTableInfo, boolean cascadeConstraints,
            boolean isMaterializedView, DatabaseObjectQualifier qualifier, SqlGenerationPreferences prefs) {
        return DialectUtils.getTableDropSQL(iTableInfo, false, cascadeConstraints, false,
                DialectUtils.CASCADE_CLAUSE, false, qualifier, prefs, this);
    }

    /**
     * Returns the SQL that forms the command to add a primary key to the specified table composed of the given
     * column names. ALTER TABLE table_name ADD CONSTRAINT contraint_name PRIMARY KEY (column_name)
     * 
     * @param pkName
     *           the name of the constraint
     * @param columnNames
     *           the columns that form the key
     * @return
     */
    public String[] getAddPrimaryKeySQL(String pkName, TableColumnInfo[] columns, ITableInfo ti,
            DatabaseObjectQualifier qualifier, SqlGenerationPreferences prefs) {
        return new String[] {
                DialectUtils.getAddPrimaryKeySQL(ti, pkName, columns, false, qualifier, prefs, this) };
    }

    /**
     * Returns a boolean value indicating whether or not this dialect supports adding comments to columns.
     * 
     * @return true if column comments are supported; false otherwise.
     */
    public boolean supportsColumnComment() {
        return true;
    }

    /**
     * Returns the SQL statement to use to add a comment to the specified column of the specified table.
     * 
     * @param info
     *           information about the column such as type, name, etc.
     * @return
     * @throws UnsupportedOperationException
     *            if the database doesn't support annotating columns with a comment.
     */
    public String getColumnCommentAlterSQL(TableColumnInfo info, DatabaseObjectQualifier qualifier,
            SqlGenerationPreferences prefs) throws UnsupportedOperationException {
        return DialectUtils.getColumnCommentAlterSQL(info, qualifier, prefs, this);
    }

    /**
     * Returns a boolean value indicating whether or not this database dialect supports changing a column from
     * null to not-null and vice versa.
     * 
     * @return true if the database supports dropping columns; false otherwise.
     */
    public boolean supportsAlterColumnNull() {
        return true;
    }

    /**
     * Update: DB2 version 9.5 appears to support altering column nullability just fine via: ALTER TABLE
     * table_name ALTER COLUMN column_name SET NOT NULL So, I'll use that Returns the SQL used to alter the
     * specified column to not allow null values This appears to work: ALTER TABLE table_name ADD CONSTRAINT
     * constraint_name CHECK (column_name IS NOT NULL) However, the jdbc driver still reports the column as
     * nullable - which means I can't reliably display the correct value for this attribute in the UI. I tried
     * this alternate syntax and it fails with an exception: ALTER TABLE table_name ALTER COLUMN column_name
     * SET NOT NULL Error: com.ibm.db2.jcc.b.SqlException: DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601,
     * SQLERRMC: NOT;ER COLUMN mychar SET;DEFAULT, SQL State: 42601, Error Code: -104 I don't see how I can
     * practically support changing column nullability in DB2.
     * 
     * @param info
     *           the column to modify
     * @return the SQL to execute
     */
    public String[] getColumnNullableAlterSQL(TableColumnInfo info, DatabaseObjectQualifier qualifier,
            SqlGenerationPreferences prefs) {
        final ArrayList<String> result = new ArrayList<String>();

        final boolean nullable = info.isNullable().equalsIgnoreCase("yes");
        result.addAll(Arrays.asList(getColumnNullableAlterSQL(info, nullable, qualifier, prefs)));

        /* DB2 needs to reorg table after changing nullabolity */
        final StringBuilder reorgSql = new StringBuilder();
        reorgSql.append("CALL SYSPROC.ADMIN_CMD('REORG TABLE ");
        reorgSql.append(DialectUtils.shapeQualifiableIdentifier(info.getTableName(), qualifier, prefs, this));
        reorgSql.append("')");

        result.add(reorgSql.toString());
        return result.toArray(new String[result.size()]);
    }

    /**
     * Returns an SQL statement that alters the specified column nullability.
     * 
     * @param info
     *           the column to modify
     * @param nullable
     *           whether or not the column should allow nulls after being altered
     * @param qualifier
     *           qualifier of the table
     * @param prefs
     *           preferences for generated sql scripts
     * @return
     */
    private String[] getColumnNullableAlterSQL(TableColumnInfo info, boolean nullable,
            DatabaseObjectQualifier qualifier, SqlGenerationPreferences prefs) {
        final ArrayList<String> sql = new ArrayList<String>();

        final StringBuilder result = new StringBuilder();
        result.append("ALTER TABLE ");
        result.append(DialectUtils.shapeQualifiableIdentifier(info.getTableName(), qualifier, prefs, this));
        result.append(" ");
        result.append(DialectUtils.ALTER_COLUMN_CLAUSE);
        result.append(" ");
        result.append(DialectUtils.shapeIdentifier(info.getColumnName(), prefs, this));
        result.append(" SET ");
        if (nullable) {
            result.append("NULL");
        } else {
            result.append("NOT NULL");
        }
        sql.add(result.toString());
        sql.add(getTableReorgSql(info.getTableName(), qualifier, prefs));
        return sql.toArray(new String[sql.size()]);
    }

    /**
     * Returns a boolean value indicating whether or not this database dialect supports renaming columns.
     * 
     * @return true if the database supports changing the name of columns; false otherwise.
     */
    public boolean supportsRenameColumn() {
        return false;
    }

    /**
     * Returns the SQL that is used to change the column name.
     * 
     * @param from
     *           the TableColumnInfo as it is
     * @param to
     *           the TableColumnInfo as it wants to be
     * @return the SQL to make the change
     */
    public String getColumnNameAlterSQL(TableColumnInfo from, TableColumnInfo to, DatabaseObjectQualifier qualifier,
            SqlGenerationPreferences prefs) {
        final int featureId = DialectUtils.COLUMN_NAME_ALTER_TYPE;
        final String msg = DialectUtils.getUnsupportedMessage(this, featureId);
        throw new UnsupportedOperationException(msg);
    }

    /**
     * Returns a boolean value indicating whether or not this dialect supports modifying a columns type.
     * 
     * @return true if supported; false otherwise
     */
    public boolean supportsAlterColumnType() {
        return true;
    }

    /**
     * Returns the SQL that is used to change the column type. ALTER TABLE table_name ALTER COLUMN column_name
     * SET DATA TYPE data_type
     * 
     * @param from
     *           the TableColumnInfo as it is
     * @param to
     *           the TableColumnInfo as it wants to be
     * @return the SQL to make the change
     * @throw UnsupportedOperationException if the database doesn't support modifying column types.
     */
    public List<String> getColumnTypeAlterSQL(TableColumnInfo from, TableColumnInfo to,
            DatabaseObjectQualifier qualifier, SqlGenerationPreferences prefs)
            throws UnsupportedOperationException {

        // "ALTER TABLE $tableName$ " +
        // "ALTER $columnName$ SET DATA TYPE $dataType$";

        final String templateString = ST_ALTER_COLUMN_SET_DATA_TYPE_STYLE_ONE;
        final StringTemplate st = new StringTemplate(templateString);

        final HashMap<String, String> valuesMap = DialectUtils.getValuesMap(ST_TABLE_NAME_KEY, from.getTableName());
        valuesMap.put(ST_COLUMN_NAME_KEY, from.getColumnName());
        valuesMap.put(ST_DATA_TYPE_KEY, DialectUtils.getTypeName(to, this));

        final ArrayList<String> result = new ArrayList<String>();
        result.add(DialectUtils.bindAttributes(this, st, valuesMap, qualifier, prefs));
        return result;
    }

    /**
     * Returns a boolean value indicating whether or not this database dialect supports changing a column's
     * default value.
     * 
     * @return true if the database supports modifying column defaults; false otherwise
     */
    public boolean supportsAlterColumnDefault() {
        return true;
    }

    /**
     * Returns the SQL command to change the specified column's default value ALTER TABLE EMPLOYEE ALTER COLUMN
     * WORKDEPTSET SET DEFAULT '123'
     * 
     * @param info
     *           the column to modify and it's default value.
     * @return SQL to make the change
     */
    public String getColumnDefaultAlterSQL(TableColumnInfo info, DatabaseObjectQualifier qualifier,
            SqlGenerationPreferences prefs) {
        final String alterClause = DialectUtils.ALTER_COLUMN_CLAUSE;
        final String defaultClause = DialectUtils.SET_DEFAULT_CLAUSE;
        return DialectUtils.getColumnDefaultAlterSQL(this, info, alterClause, false, defaultClause, qualifier,
                prefs);
    }

    /**
     * Returns the SQL command to drop the specified table's primary key.
     * 
     * @param pkName
     *           the name of the primary key that should be dropped
     * @param tableName
     *           the name of the table whose primary key should be dropped
     * @return
     */
    public String getDropPrimaryKeySQL(String pkName, String tableName, DatabaseObjectQualifier qualifier,
            SqlGenerationPreferences prefs) {
        return DialectUtils.getDropPrimaryKeySQL(pkName, tableName, false, false, qualifier, prefs, this);
    }

    /**
     * Returns the SQL command to drop the specified table's foreign key constraint.
     * 
     * @param fkName
     *           the name of the foreign key that should be dropped
     * @param tableName
     *           the name of the table whose foreign key should be dropped
     * @return
     */
    public String getDropForeignKeySQL(String fkName, String tableName, DatabaseObjectQualifier qualifier,
            SqlGenerationPreferences prefs) {
        return DialectUtils.getDropForeignKeySQL(fkName, tableName, qualifier, prefs, this);
    }

    /**
     * Returns the SQL command to create the specified table.
     * 
     * @param tables
     *           the tables to get create statements for
     * @param md
     *           the metadata from the ISession
     * @param prefs
     *           preferences about how the resultant SQL commands should be formed.
     * @param isJdbcOdbc
     *           whether or not the connection is via JDBC-ODBC bridge.
     * @return the SQL that is used to create the specified table
     */
    public List<String> getCreateTableSQL(List<ITableInfo> tables, ISQLDatabaseMetaData md,
            CreateScriptPreferences prefs, boolean isJdbcOdbc) throws SQLException {
        return DialectUtils.getCreateTableSQL(tables, md, this, prefs, isJdbcOdbc);
    }

    /**
     * @see net.sourceforge.squirrel_sql.fw.dialects.HibernateDialect#getDialectType()
     */
    public DialectType getDialectType() {
        return DialectType.DB2;
    }

    /**
     * @see net.sourceforge.squirrel_sql.fw.dialects.HibernateDialect#getIndexAccessMethodsTypes()
     */
    public String[] getIndexAccessMethodsTypes() {
        return new String[] {};
    }

    /**
     * @see net.sourceforge.squirrel_sql.fw.dialects.HibernateDialect#getIndexStorageOptions()
     */
    public String[] getIndexStorageOptions() {
        return null;
    }

    /**
     * @see net.sourceforge.squirrel_sql.fw.dialects.CommonHibernateDialect#getAddAutoIncrementSQL(net.sourceforge.squirrel_sql.fw.sql.TableColumnInfo,
     *      java.lang.String, net.sourceforge.squirrel_sql.fw.dialects.DatabaseObjectQualifier,
     *      net.sourceforge.squirrel_sql.fw.dialects.SqlGenerationPreferences)
     */
    @Override
    public String[] getAddAutoIncrementSQL(TableColumnInfo column, String sequenceName,
            DatabaseObjectQualifier qualifier, SqlGenerationPreferences prefs) {
        final ArrayList<String> result = new ArrayList<String>();
        /*
         * DB2 doesn't support adding an auto-increment column once the table has already been created. So this
         * can simulate one using trigger on the table to access a sequence. Found this idea at wikibooks:
         * http://en.wikibooks.org/wiki/SQL_dialects_reference/Data_structure_definition/Auto-increment_column
         * CREATE SEQUENCE sequence_name; CREATE TABLE table_name ( column_name INT ); CREATE TRIGGER
         * insert_trigger NO CASCADE BEFORE INSERT ON table_name REFERENCING NEW AS n FOR EACH ROW SET
         * n.column_name = NEXTVAL FOR sequence_name;
         */
        final String tableName = column.getTableName();
        final String columnName = column.getColumnName();

        result.add(getCreateSequenceSQL(sequenceName, "1", "1", null, "1", null, false, qualifier, prefs));

        final StringBuilder triggerSql = new StringBuilder();
        triggerSql.append("CREATE TRIGGER ");
        triggerSql.append(columnName);
        triggerSql.append("_trigger \n");
        triggerSql.append("NO CASCADE BEFORE INSERT ON ");
        triggerSql.append(tableName);
        triggerSql.append(" REFERENCING NEW AS n \n");
        triggerSql.append("FOR EACH ROW \n");
        triggerSql.append("SET n.");
        triggerSql.append(columnName);
        triggerSql.append(" = NEXTVAL FOR ");
        triggerSql.append(sequenceName);

        result.add(triggerSql.toString());

        return result.toArray(new String[result.size()]);

    }

    /**
     * @see net.sourceforge.squirrel_sql.fw.dialects.HibernateDialect#getAddAutoIncrementSQL(net.sourceforge.squirrel_sql.fw.sql.TableColumnInfo,
     *      DatabaseObjectQualifier, net.sourceforge.squirrel_sql.fw.dialects.SqlGenerationPreferences)
     * @deprecated use the version that accepts the sequence name instead.
     */
    public String[] getAddAutoIncrementSQL(TableColumnInfo column, DatabaseObjectQualifier qualifier,
            SqlGenerationPreferences prefs) {
        final String tableName = column.getTableName();
        final String columnName = column.getColumnName();
        final StringBuilder sequenceName = new StringBuilder();
        sequenceName.append(tableName.toUpperCase()).append("_");
        sequenceName.append(columnName.toUpperCase()).append("_SEQ");

        return getAddAutoIncrementSQL(column, sequenceName.toString(), qualifier, prefs);
    }

    /**
     * @see net.sourceforge.squirrel_sql.fw.dialects.CommonHibernateDialect#getAddForeignKeyConstraintSQL(java.lang.String,
     *      java.lang.String, java.lang.String, java.lang.Boolean, java.lang.Boolean, java.lang.Boolean,
     *      boolean, java.lang.String, java.util.Collection, java.lang.String, java.lang.String,
     *      net.sourceforge.squirrel_sql.fw.dialects.DatabaseObjectQualifier,
     *      net.sourceforge.squirrel_sql.fw.dialects.SqlGenerationPreferences)
     */
    public String[] getAddForeignKeyConstraintSQL(String localTableName, String refTableName, String constraintName,
            Boolean deferrable, Boolean initiallyDeferred, Boolean matchFull, boolean autoFKIndex,
            String fkIndexName, Collection<String[]> localRefColumns, String onUpdateAction, String onDeleteAction,
            DatabaseObjectQualifier qualifier, SqlGenerationPreferences prefs) {
        final Boolean deferrableNotSupported = null;
        final Boolean initiallyDeferredNotSupported = null;
        final Boolean matchFullNotSupported = null;

        /* DB2 doesn't support cascade, set default, or set null for FK constraint update action */
        if (onUpdateAction != null) {
            if (!(onUpdateAction.equalsIgnoreCase("no action") || onUpdateAction.equalsIgnoreCase("restrict"))) {
                onUpdateAction = "";
            }
        }

        /* DB2 doesn't support set default for FK constraint delete action */
        if (onDeleteAction != null && onDeleteAction.equalsIgnoreCase("set default")) {
            onDeleteAction = "";
        }

        return DialectUtils.getAddForeignKeyConstraintSQL(localTableName, refTableName, constraintName,
                deferrableNotSupported, initiallyDeferredNotSupported, matchFullNotSupported, autoFKIndex,
                fkIndexName, localRefColumns, onUpdateAction, onDeleteAction, qualifier, prefs, this);
    }

    private String getTableReorgSql(String tableName, DatabaseObjectQualifier qualifier,
            SqlGenerationPreferences prefs) {
        /* DB2 needs to reorg table after changing nullabolity */
        final StringBuilder reorgSql = new StringBuilder();
        reorgSql.append("CALL SYSPROC.ADMIN_CMD('REORG TABLE ");
        reorgSql.append(DialectUtils.shapeQualifiableIdentifier(tableName, qualifier, prefs, this));
        reorgSql.append("')");
        return reorgSql.toString();
    }

    /**
     * @see net.sourceforge.squirrel_sql.fw.dialects.HibernateDialect#getAddUniqueConstraintSQL(java.lang.String,
     *      java.lang.String, TableColumnInfo[],
     *      net.sourceforge.squirrel_sql.fw.dialects.DatabaseObjectQualifier,
     *      net.sourceforge.squirrel_sql.fw.dialects.SqlGenerationPreferences)
     */
    public String[] getAddUniqueConstraintSQL(String tableName, String constraintName, TableColumnInfo[] columns,
            DatabaseObjectQualifier qualifier, SqlGenerationPreferences prefs) {
        final ArrayList<String> result = new ArrayList<String>();

        // DB2 requires that columns be not-null before applying a unique constraint
        for (final TableColumnInfo column : columns) {
            if (column.isNullable().equalsIgnoreCase("YES")) {
                result.addAll(Arrays.asList(getColumnNullableAlterSQL(column, false, qualifier, prefs)));
            }
        }

        result.add(
                DialectUtils.getAddUniqueConstraintSQL(tableName, constraintName, columns, qualifier, prefs, this));

        return result.toArray(new String[result.size()]);
    }

    /**
     * @see net.sourceforge.squirrel_sql.fw.dialects.HibernateDialect#getAlterSequenceSQL(java.lang.String,
     *      java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String, boolean,
     *      net.sourceforge.squirrel_sql.fw.dialects.DatabaseObjectQualifier,
     *      net.sourceforge.squirrel_sql.fw.dialects.SqlGenerationPreferences)
     */
    public String[] getAlterSequenceSQL(String sequenceName, String increment, String minimum, String maximum,
            String restart, String cache, boolean cycle, DatabaseObjectQualifier qualifier,
            SqlGenerationPreferences prefs) {
        String cycleClause = NO_CYCLE_CLAUSE;
        if (cycle == true) {
            cycleClause = CYCLE_CLAUSE;
        }
        return new String[] {

                DialectUtils.getAlterSequenceSQL(sequenceName, increment, minimum, maximum, restart, cache,
                        cycleClause, qualifier, prefs, this) };
    }

    /**
     * @see net.sourceforge.squirrel_sql.fw.dialects.HibernateDialect#getCreateIndexSQL(java.lang.String,
     *      java.lang.String, java.lang.String, java.lang.String[], boolean, java.lang.String,
     *      java.lang.String, net.sourceforge.squirrel_sql.fw.dialects.DatabaseObjectQualifier,
     *      net.sourceforge.squirrel_sql.fw.dialects.SqlGenerationPreferences)
     */
    public String getCreateIndexSQL(String indexName, String tableName, String accessMethod, String[] columns,
            boolean unique, String tablespace, String constraints, DatabaseObjectQualifier qualifier,
            SqlGenerationPreferences prefs) {
        return DialectUtils.getCreateIndexSQL(indexName, tableName, accessMethod, columns, unique, tablespace,
                constraints, qualifier, prefs, this);
    }

    /**
     * @see net.sourceforge.squirrel_sql.fw.dialects.HibernateDialect#getCreateSequenceSQL(java.lang.String,
     *      java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String, boolean,
     *      net.sourceforge.squirrel_sql.fw.dialects.DatabaseObjectQualifier,
     *      net.sourceforge.squirrel_sql.fw.dialects.SqlGenerationPreferences)
     */
    public String getCreateSequenceSQL(String sequenceName, String increment, String minimum, String maximum,
            String start, String cache, boolean cycle, DatabaseObjectQualifier qualifier,
            SqlGenerationPreferences prefs) {
        return DialectUtils.getCreateSequenceSQL(sequenceName, increment, minimum, maximum, start, cache, null,
                qualifier, prefs, this);
    }

    /**
     * @see net.sourceforge.squirrel_sql.fw.dialects.HibernateDialect#getCreateTableSQL(java.lang.String,
     *      java.util.List, java.util.List, net.sourceforge.squirrel_sql.fw.dialects.SqlGenerationPreferences,
     *      net.sourceforge.squirrel_sql.fw.dialects.DatabaseObjectQualifier)
     */
    public String getCreateTableSQL(String tableName, List<TableColumnInfo> columns,
            List<TableColumnInfo> primaryKeys, SqlGenerationPreferences prefs, DatabaseObjectQualifier qualifier) {
        return DialectUtils.getCreateTableSQL(tableName, columns, primaryKeys, prefs, qualifier, this);
    }

    /**
     * @see net.sourceforge.squirrel_sql.fw.dialects.HibernateDialect#getCreateViewSQL(java.lang.String,
     *      java.lang.String, java.lang.String,
     *      net.sourceforge.squirrel_sql.fw.dialects.DatabaseObjectQualifier,
     *      net.sourceforge.squirrel_sql.fw.dialects.SqlGenerationPreferences)
     */
    public String getCreateViewSQL(String viewName, String definition, String checkOption,
            DatabaseObjectQualifier qualifier, SqlGenerationPreferences prefs) {
        return DialectUtils.getCreateViewSQL(viewName, definition, checkOption, qualifier, prefs, this);
    }

    /**
     * @see net.sourceforge.squirrel_sql.fw.dialects.HibernateDialect#getDropConstraintSQL(java.lang.String,
     *      java.lang.String, net.sourceforge.squirrel_sql.fw.dialects.DatabaseObjectQualifier,
     *      net.sourceforge.squirrel_sql.fw.dialects.SqlGenerationPreferences)
     */
    public String getDropConstraintSQL(String tableName, String constraintName, DatabaseObjectQualifier qualifier,
            SqlGenerationPreferences prefs) {
        return DialectUtils.getDropConstraintSQL(tableName, constraintName, qualifier, prefs, this);
    }

    /**
     * @see net.sourceforge.squirrel_sql.fw.dialects.HibernateDialect#getDropIndexSQL(String, java.lang.String,
     *      boolean, net.sourceforge.squirrel_sql.fw.dialects.DatabaseObjectQualifier,
     *      net.sourceforge.squirrel_sql.fw.dialects.SqlGenerationPreferences)
     */
    public String getDropIndexSQL(String tableName, String indexName, boolean cascade,
            DatabaseObjectQualifier qualifier, SqlGenerationPreferences prefs) {
        final Boolean cascadeNotSupported = null;
        return DialectUtils.getDropIndexSQL(indexName, cascadeNotSupported, qualifier, prefs, this);
    }

    /**
     * @see net.sourceforge.squirrel_sql.fw.dialects.HibernateDialect#getDropSequenceSQL(java.lang.String,
     *      boolean, net.sourceforge.squirrel_sql.fw.dialects.DatabaseObjectQualifier,
     *      net.sourceforge.squirrel_sql.fw.dialects.SqlGenerationPreferences)
     */
    public String getDropSequenceSQL(String sequenceName, boolean cascade, DatabaseObjectQualifier qualifier,
            SqlGenerationPreferences prefs) {
        return DialectUtils.getDropSequenceSQL(sequenceName, false, qualifier, prefs, this);
    }

    /**
     * @see net.sourceforge.squirrel_sql.fw.dialects.HibernateDialect#getDropViewSQL(java.lang.String, boolean,
     *      net.sourceforge.squirrel_sql.fw.dialects.DatabaseObjectQualifier,
     *      net.sourceforge.squirrel_sql.fw.dialects.SqlGenerationPreferences)
     */
    public String getDropViewSQL(String viewName, boolean cascade, DatabaseObjectQualifier qualifier,
            SqlGenerationPreferences prefs) {
        final Boolean cascadeNotSupported = null;

        return DialectUtils.getDropViewSQL(viewName, cascadeNotSupported, qualifier, prefs, this);
    }

    /**
     * @see net.sourceforge.squirrel_sql.fw.dialects.HibernateDialect#getInsertIntoSQL(java.lang.String,
     *      java.util.List, java.lang.String, net.sourceforge.squirrel_sql.fw.dialects.DatabaseObjectQualifier,
     *      net.sourceforge.squirrel_sql.fw.dialects.SqlGenerationPreferences)
     */
    public String getInsertIntoSQL(String tableName, List<String> columns, String valuesPart,
            DatabaseObjectQualifier qualifier, SqlGenerationPreferences prefs) {
        return DialectUtils.getInsertIntoSQL(tableName, columns, valuesPart, qualifier, prefs, this);
    }

    /**
     * @see net.sourceforge.squirrel_sql.fw.dialects.HibernateDialect#getRenameTableSQL(java.lang.String,
     *      java.lang.String, net.sourceforge.squirrel_sql.fw.dialects.DatabaseObjectQualifier,
     *      net.sourceforge.squirrel_sql.fw.dialects.SqlGenerationPreferences)
     */
    public String getRenameTableSQL(String oldTableName, String newTableName, DatabaseObjectQualifier qualifier,
            SqlGenerationPreferences prefs) {
        // RENAME TABLE <tablename> TO <newtablename>;
        final StringBuilder sql = new StringBuilder();

        sql.append("RENAME TABLE ");
        sql.append(DialectUtils.shapeQualifiableIdentifier(oldTableName, qualifier, prefs, this));
        sql.append(" ");
        sql.append(" TO ");
        sql.append(DialectUtils.shapeIdentifier(newTableName, prefs, this));

        return sql.toString();
    }

    /**
     * @see net.sourceforge.squirrel_sql.fw.dialects.HibernateDialect#getRenameViewSQL(java.lang.String,
     *      java.lang.String, net.sourceforge.squirrel_sql.fw.dialects.DatabaseObjectQualifier,
     *      net.sourceforge.squirrel_sql.fw.dialects.SqlGenerationPreferences)
     */
    public String[] getRenameViewSQL(String oldViewName, String newViewName, DatabaseObjectQualifier qualifier,
            SqlGenerationPreferences prefs) {
        final int featureId = DialectUtils.RENAME_VIEW_TYPE;
        final String msg = DialectUtils.getUnsupportedMessage(this, featureId);
        throw new UnsupportedOperationException(msg);
    }

    /**
     * @see net.sourceforge.squirrel_sql.fw.dialects.HibernateDialect#supportsViewDefinition()
     */
    public boolean supportsViewDefinition() {
        return true;
    }

    public String getViewDefinitionSQL(String viewName, DatabaseObjectQualifier qualifier,
            SqlGenerationPreferences prefs) {
        /*
         * SELECT 'CREATE VIEW <newViewName> AS ' || SUBSTR(TEXT , LOCATE('as', TEXT)+2, LENGTH(TEXT)) FROM
         * SYSCAT.VIEWS WHERE VIEWSCHEMA = '<schema>' AND VIEWNAME = '<oldViewName>';
         */

        final StringBuilder createViewSql = new StringBuilder();
        createViewSql.append("SELECT TEXT ");
        createViewSql.append(" FROM SYSCAT.VIEWS ");
        createViewSql.append("WHERE VIEWSCHEMA = '");
        createViewSql.append(qualifier.getSchema());
        createViewSql.append("' AND UPPER(VIEWNAME) = '");
        createViewSql.append(viewName.toUpperCase());
        createViewSql.append("'");
        return createViewSql.toString();
    }

    /**
     * @see net.sourceforge.squirrel_sql.fw.dialects.HibernateDialect#getSequenceInformationSQL(java.lang.String,
     *      net.sourceforge.squirrel_sql.fw.dialects.DatabaseObjectQualifier,
     *      net.sourceforge.squirrel_sql.fw.dialects.SqlGenerationPreferences)
     */
    public String getSequenceInformationSQL(String sequenceName, DatabaseObjectQualifier qualifier,
            SqlGenerationPreferences prefs) {
        // SELECT
        //SEQSCHEMA,SEQNAME,DEFINER,DEFINERTYPE,OWNER,OWNERTYPE,SEQID,SEQTYPE,INCREMENT,START,MAXVALUE,MINVALUE,
        //NEXTCACHEFIRSTVALUE,CYCLE,CACHE,ORDER,DATATYPEID,SOURCETYPEID,CREATE_TIME,ALTER_TIME,PRECISION,ORIGIN,
        // REMARKS
        // FROM SYSCAT.SEQUENCES
        // WHERE SEQNAME = ?
        // and SEQSCHEMA = <schema>

        final StringBuilder result = new StringBuilder();
        result.append("SELECT NEXTCACHEFIRSTVALUE, MAXVALUE, MINVALUE, CACHE, INCREMENT, CYCLE ");
        result.append("FROM SYSCAT.SEQUENCES ");
        result.append("WHERE ");
        if (qualifier.getSchema() != null) {
            result.append("SEQSCHEMA = upper('" + qualifier.getSchema() + "') AND ");
        }
        // TODO: figure out why bind variables aren't working
        result.append("SEQNAME = '");
        result.append(sequenceName);
        result.append("'");
        return result.toString();
    }

    /**
     * @see net.sourceforge.squirrel_sql.fw.dialects.HibernateDialect#supportsAccessMethods()
     */
    public boolean supportsAccessMethods() {
        return false;
    }

    /**
     * @see net.sourceforge.squirrel_sql.fw.dialects.HibernateDialect#supportsAddForeignKeyConstraint()
     */
    public boolean supportsAddForeignKeyConstraint() {
        return true;
    }

    /**
     * @see net.sourceforge.squirrel_sql.fw.dialects.HibernateDialect#supportsAddUniqueConstraint()
     */
    public boolean supportsAddUniqueConstraint() {
        return true;
    }

    /**
     * @see net.sourceforge.squirrel_sql.fw.dialects.HibernateDialect#supportsAlterSequence()
     */
    public boolean supportsAlterSequence() {
        return true;
    }

    /**
     * @see net.sourceforge.squirrel_sql.fw.dialects.HibernateDialect#supportsAutoIncrement()
     */
    public boolean supportsAutoIncrement() {
        return true;
    }

    /**
     * @see net.sourceforge.squirrel_sql.fw.dialects.HibernateDialect#supportsCheckOptionsForViews()
     */
    public boolean supportsCheckOptionsForViews() {
        return false;
    }

    /**
     * @see net.sourceforge.squirrel_sql.fw.dialects.HibernateDialect#supportsCreateIndex()
     */
    public boolean supportsCreateIndex() {
        return true;
    }

    /**
     * @see net.sourceforge.squirrel_sql.fw.dialects.HibernateDialect#supportsCreateSequence()
     */
    public boolean supportsCreateSequence() {
        return true;
    }

    /**
     * @see net.sourceforge.squirrel_sql.fw.dialects.HibernateDialect#supportsCreateTable()
     */
    public boolean supportsCreateTable() {
        return true;
    }

    /**
     * @see net.sourceforge.squirrel_sql.fw.dialects.HibernateDialect#supportsCreateView()
     */
    public boolean supportsCreateView() {
        return true;
    }

    public boolean supportsDropConstraint() {
        return true;
    }

    /**
     * @see net.sourceforge.squirrel_sql.fw.dialects.HibernateDialect#supportsDropIndex()
     */
    public boolean supportsDropIndex() {
        return true;
    }

    /**
     * @see net.sourceforge.squirrel_sql.fw.dialects.HibernateDialect#supportsDropSequence()
     */
    public boolean supportsDropSequence() {
        return true;
    }

    /**
     * @see net.sourceforge.squirrel_sql.fw.dialects.HibernateDialect#supportsDropView()
     */
    public boolean supportsDropView() {
        return true;
    }

    public boolean supportsEmptyTables() {
        return false;
    }

    /**
     * @see net.sourceforge.squirrel_sql.fw.dialects.CommonHibernateDialect#supportsIndexes()
     */
    public boolean supportsIndexes() {
        return true;
    }

    /**
     * @see net.sourceforge.squirrel_sql.fw.dialects.HibernateDialect#supportsInsertInto()
     */
    public boolean supportsInsertInto() {
        return true;
    }

    /**
     * @see net.sourceforge.squirrel_sql.fw.dialects.CommonHibernateDialect#supportsMultipleRowInserts()
     */
    public boolean supportsMultipleRowInserts() {
        return false;
    }

    /**
     * @see net.sourceforge.squirrel_sql.fw.dialects.HibernateDialect#supportsRenameTable()
     */
    public boolean supportsRenameTable() {
        return true;
    }

    /**
     * @see net.sourceforge.squirrel_sql.fw.dialects.HibernateDialect#supportsRenameView()
     */
    public boolean supportsRenameView() {
        return false;
    }

    /**
     * @see net.sourceforge.squirrel_sql.fw.dialects.HibernateDialect#supportsSequence()
     */
    public boolean supportsSequence() {
        return true;
    }

    /**
     * @see net.sourceforge.squirrel_sql.fw.dialects.HibernateDialect#supportsSequenceInformation()
     */
    public boolean supportsSequenceInformation() {
        return true;
    }

    /**
     * @see net.sourceforge.squirrel_sql.fw.dialects.HibernateDialect#supportsTablespace()
     */
    public boolean supportsTablespace() {
        return false;
    }

    /**
     * @see net.sourceforge.squirrel_sql.fw.dialects.HibernateDialect#supportsUpdate()
     */
    public boolean supportsUpdate() {
        return true;
    }

    /**
     * @see net.sourceforge.squirrel_sql.fw.dialects.HibernateDialect#supportsAddColumn()
     */
    public boolean supportsAddColumn() {
        return true;
    }

    /**
     * @see net.sourceforge.squirrel_sql.fw.dialects.HibernateDialect#getQualifiedIdentifier(java.lang.String,
     *      net.sourceforge.squirrel_sql.fw.dialects.DatabaseObjectQualifier,
     *      net.sourceforge.squirrel_sql.fw.dialects.SqlGenerationPreferences)
     */
    public String getQualifiedIdentifier(String identifier, DatabaseObjectQualifier qualifier,
            SqlGenerationPreferences prefs) {
        final String schema = qualifier.getSchema();
        final String catalog = qualifier.getCatalog();
        final StringBuilder result = new StringBuilder();
        if (!StringUtils.isEmpty(catalog)) {
            result.append(DialectUtils.shapeIdentifier(catalog, prefs, this));
            result.append(".");
        }
        if (!StringUtils.isEmpty(schema)) {
            result.append(DialectUtils.shapeIdentifier(schema, prefs, this));
            result.append(".");
        }
        result.append(DialectUtils.shapeIdentifier(identifier, prefs, this));
        return result.toString();
    }

    /**
     * @see net.sourceforge.squirrel_sql.fw.dialects.HibernateDialect#supportsCorrelatedSubQuery()
     */
    public boolean supportsCorrelatedSubQuery() {
        return true;
    }

    /**
     * @see net.sourceforge.squirrel_sql.fw.dialects.CommonHibernateDialect#getTimestampMaximumFractionalDigits()
     */
    @Override
    public int getTimestampMaximumFractionalDigits() {
        return 6;
    }

    /**
     * @see net.sourceforge.squirrel_sql.fw.dialects.CommonHibernateDialect#getBinaryLiteralString(byte[])
     */
    @Override
    public String getBinaryLiteralString(byte[] binaryData) {
        return "BLOB(x'" + DialectUtils.toHexString(binaryData) + "')";
    }

}