net.sourceforge.squirrel_sql.fw.dialects.SQLServerDialectExt Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of fw Show documentation
Show all versions of fw Show documentation
The framework library contains utility classes that are generic and useful for building applications
that introspect a database via JDBC. These are not intended to be SQuirreLSQL-specific and could be
used by other projects JDBC front-end applications. This project is guaranteed to have no code
dependencies on other SQuirreLSQL projects and could therefore be used when building a different
JDBC front-end application.
/*
* Copyright (C) 2006 Rob Manning
* [email protected]
*
* 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 java.sql.Types;
import java.util.ArrayList;
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.ITableInfo;
import net.sourceforge.squirrel_sql.fw.sql.JDBCTypeMapper;
import net.sourceforge.squirrel_sql.fw.sql.TableColumnInfo;
import org.antlr.stringtemplate.StringTemplate;
import org.hibernate.HibernateException;
/**
* A dialect delegate for the MS SQLServer database.
*/
public class SQLServerDialectExt extends SybaseDialectExt implements HibernateDialect
{
private class SQLServerDialectHelper extends org.hibernate.dialect.SybaseDialect
{
public SQLServerDialectHelper()
{
super();
registerColumnType(Types.BIGINT, "bigint");
registerColumnType(Types.BINARY, "image");
registerColumnType(Types.BIT, "tinyint");
registerColumnType(Types.BLOB, "image");
registerColumnType(Types.BOOLEAN, "tinyint");
registerColumnType(Types.CHAR, 8000, "char($l)");
registerColumnType(Types.CHAR, "text");
registerColumnType(Types.CLOB, "text");
registerColumnType(Types.DATE, "datetime");
registerColumnType(Types.DECIMAL, "decimal($p,$s)");
registerColumnType(Types.DOUBLE, "float($p)");
registerColumnType(Types.FLOAT, "float($p)");
registerColumnType(Types.INTEGER, "int");
registerColumnType(Types.LONGVARBINARY, "image");
registerColumnType(Types.LONGVARCHAR, "text");
registerColumnType(Types.NUMERIC, "numeric($p,$s)");
registerColumnType(Types.REAL, "real");
registerColumnType(Types.SMALLINT, "smallint");
registerColumnType(Types.TIME, "datetime");
registerColumnType(Types.TIMESTAMP, "datetime");
registerColumnType(Types.TINYINT, "tinyint");
registerColumnType(Types.VARBINARY, 8000, "varbinary($l)");
registerColumnType(Types.VARBINARY, "image");
registerColumnType(Types.VARCHAR, 8000, "varchar($l)");
registerColumnType(Types.VARCHAR, "text");
}
}
/** extended hibernate dialect used in this wrapper */
private SQLServerDialectHelper _dialect = new SQLServerDialectHelper();
/**
* @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.SybaseDialectExt#canPasteTo(net.sourceforge.squirrel_sql.fw.sql.IDatabaseObjectInfo)
*/
@Override
public boolean canPasteTo(IDatabaseObjectInfo info)
{
boolean result = true;
DatabaseObjectType type = info.getDatabaseObjectType();
if (type.getName().equalsIgnoreCase("database") || type.getName().equalsIgnoreCase("catalog"))
{
result = false;
}
return result;
}
/**
* @see net.sourceforge.squirrel_sql.fw.dialects.CommonHibernateDialect#getLengthFunction(int)
*/
@Override
public String getLengthFunction(int dataType)
{
return "len";
}
/**
* @see net.sourceforge.squirrel_sql.fw.dialects.SybaseDialectExt#getMaxPrecision(int)
*/
@Override
public int getMaxPrecision(int dataType)
{
if (dataType == Types.DOUBLE || dataType == Types.FLOAT)
{
return 53;
} else
{
return 38;
}
}
/**
* @see net.sourceforge.squirrel_sql.fw.dialects.SybaseDialectExt#getMaxScale(int)
*/
@Override
public int getMaxScale(int dataType)
{
return getMaxPrecision(dataType);
}
/**
* @see net.sourceforge.squirrel_sql.fw.dialects.SybaseDialectExt#getPrecisionDigits(int, int)
*/
@Override
public int getPrecisionDigits(int columnSize, int dataType)
{
return columnSize;
}
/**
* @see net.sourceforge.squirrel_sql.fw.dialects.SybaseDialectExt#getColumnLength(int, int)
*/
@Override
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.
*/
@Override
public String getDisplayName()
{
return "MS SQLServer";
}
/**
* 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.
*/
@Override
public boolean supportsProduct(String databaseProductName, String databaseProductVersion)
{
if (databaseProductName == null)
{
return false;
}
if (databaseProductName.trim().toLowerCase().startsWith("microsoft"))
{
// We don't yet have the need to discriminate by version.
return true;
}
return false;
}
/**
* Returns a boolean value indicating whether or not this dialect supports adding comments to columns.
*
* @return true if column comments are supported; false otherwise.
*/
@Override
public boolean supportsColumnComment()
{
return false;
}
/**
* 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.
*/
@Override
public String getColumnCommentAlterSQL(TableColumnInfo info, DatabaseObjectQualifier qualifier,
SqlGenerationPreferences prefs) throws UnsupportedOperationException
{
final int featureId = DialectUtils.COLUMN_COMMENT_ALTER_TYPE;
final String msg = DialectUtils.getUnsupportedMessage(this, featureId);
throw new UnsupportedOperationException(msg);
}
/**
* @see net.sourceforge.squirrel_sql.fw.dialects.SybaseDialectExt#supportsDropColumn()
*/
@Override
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.
*/
@Override
public String getColumnDropSQL(String tableName, String columnName, DatabaseObjectQualifier qualifier, SqlGenerationPreferences prefs)
{
return DialectUtils.getColumnDropSQL(tableName, columnName, "DROP COLUMN", false, null, 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.
*/
@Override
public List 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 test alter column mycol integer not null alter table test add primary key
* (mycol)
*
* @param pkName
* the name of the constraint
* @param colInfos
* the columns that form the key
* @return
*/
@Override
public String[] getAddPrimaryKeySQL(String pkName, TableColumnInfo[] colInfos, ITableInfo ti, DatabaseObjectQualifier qualifier, SqlGenerationPreferences prefs)
{
ArrayList result = new ArrayList();
String alterClause = DialectUtils.ALTER_COLUMN_CLAUSE;
// convert all columns in key to not null - this doesn't hurt if they
// are already null.
DialectUtils.getMultiColNotNullSQL(colInfos, this, alterClause, true, result, qualifier, prefs);
String pkSQL = DialectUtils.getAddPrimaryKeySQL(ti, pkName, colInfos, false, qualifier, prefs, this);
result.add(pkSQL);
return result.toArray(new String[result.size()]);
}
/**
* 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.
*/
@Override
public boolean supportsAlterColumnNull()
{
return true;
}
/**
* Returns the SQL used to alter the specified column to not allow null values alter table mytest alter
* column mycol integer not null
*
* @param info
* the column to modify
* @return the SQL to execute
*/
@Override
public String[] getColumnNullableAlterSQL(TableColumnInfo info, DatabaseObjectQualifier qualifier,
SqlGenerationPreferences prefs)
{
String alterClause = DialectUtils.ALTER_COLUMN_CLAUSE;
return new String[] { DialectUtils.getColumnNullableAlterSQL(info, this, alterClause, true, qualifier, prefs) };
}
/**
* 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.
*/
@Override
public boolean supportsRenameColumn()
{
return true;
}
/**
* Returns the SQL that is used to change the column name. exec sp_rename 'test.renameCol', newNameCol,
* 'COLUMN'
*
* @param from
* the TableColumnInfo as it is
* @param to
* the TableColumnInfo as it wants to be
* @return the SQL to make the change
*/
@Override
public String getColumnNameAlterSQL(TableColumnInfo from, TableColumnInfo to, DatabaseObjectQualifier qualifier, SqlGenerationPreferences prefs)
{
StringBuffer result = new StringBuffer();
result.append("exec sp_rename ");
result.append("'");
result.append(from.getTableName());
result.append(".");
result.append(from.getColumnName());
result.append("'");
result.append(", ");
result.append(to.getColumnName());
result.append(", 'COLUMN'");
return result.toString();
}
/**
* Returns a boolean value indicating whether or not this dialect supports modifying a columns type.
*
* @return true if supported; false otherwise
*/
@Override
public boolean supportsAlterColumnType()
{
return true;
}
/**
* Returns the SQL that is used to change the column type. ALTER TABLE doc_exy ALTER COLUMN column_a
* DECIMAL (5, 2)
*
* @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.
*/
@Override
public List getColumnTypeAlterSQL(TableColumnInfo from, TableColumnInfo to,
DatabaseObjectQualifier qualifier, SqlGenerationPreferences prefs) throws UnsupportedOperationException
{
ArrayList list = new ArrayList();
StringBuffer result = new StringBuffer();
result.append("ALTER TABLE ");
result.append(from.getTableName());
result.append(" ALTER COLUMN ");
result.append(from.getColumnName());
result.append(" ");
result.append(DialectUtils.getTypeName(to, this));
list.add(result.toString());
return list;
}
/**
* 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
*/
@Override
public boolean supportsAlterColumnDefault()
{
return true;
}
/**
* Returns the SQL command to change the specified column's default value ALTER TABLE table ADD CONSTRAINT
* table_c_def DEFAULT 50 FOR column_b ;
*
* @param info
* the column to modify and it's default value.
* @return SQL to make the change
*/
@Override
public String getColumnDefaultAlterSQL(TableColumnInfo info, DatabaseObjectQualifier qualifier, SqlGenerationPreferences prefs)
{
StringBuffer result = new StringBuffer();
result.append("ALTER TABLE ");
result.append(info.getTableName());
result.append(" ADD CONSTRAINT ");
result.append(info.getTableName()).append("_").append(info.getColumnName()).append("_default");
result.append(" DEFAULT ");
if (JDBCTypeMapper.isNumberType(info.getDataType()))
{
result.append(info.getDefaultValue());
} else
{
result.append("'");
result.append(info.getDefaultValue());
result.append("'");
}
result.append(" FOR ");
result.append(info.getColumnName());
return result.toString();
}
/**
* @see net.sourceforge.squirrel_sql.fw.dialects.CommonHibernateDialect#getDialectType()
*/
@Override
public DialectType getDialectType()
{
return DialectType.MSSQL;
}
/**
* @see net.sourceforge.squirrel_sql.fw.dialects.CommonHibernateDialect#getAddColumnSQL(net.sourceforge.squirrel_sql.fw.sql.TableColumnInfo,
* net.sourceforge.squirrel_sql.fw.dialects.DatabaseObjectQualifier,
* net.sourceforge.squirrel_sql.fw.dialects.SqlGenerationPreferences)
*/
@Override
public String[] getAddColumnSQL(TableColumnInfo column, DatabaseObjectQualifier qualifier,
SqlGenerationPreferences prefs)
{
ArrayList result = new ArrayList();
/**
* The default must be in the add column sql if the new column is marked not-null in SQLServer 2000,
* but it's optional in SQLServer 2005.
*/
boolean addDefaultClause = true;
boolean supportsNullQualifier = true;
boolean addNullClause = true;
String sql =
DialectUtils.getAddColumSQL(column,
this,
addDefaultClause,
supportsNullQualifier,
addNullClause,
qualifier,
prefs);
result.add(sql);
return result.toArray(new String[result.size()]);
}
/**
* @see net.sourceforge.squirrel_sql.fw.dialects.CommonHibernateDialect#getDropIndexSQL(java.lang.String,
* java.lang.String, boolean, net.sourceforge.squirrel_sql.fw.dialects.DatabaseObjectQualifier,
* net.sourceforge.squirrel_sql.fw.dialects.SqlGenerationPreferences)
*/
@Override
public String getDropIndexSQL(String tableName, String indexName, boolean cascade,
DatabaseObjectQualifier qualifier, SqlGenerationPreferences prefs)
{
StringTemplate st = new StringTemplate(ST_DROP_INDEX_STYLE_TWO);
HashMap valuesMap = new HashMap();
valuesMap.put(ST_INDEX_NAME_KEY, indexName);
valuesMap.put(ST_TABLE_NAME_KEY, tableName);
return DialectUtils.bindAttributes(this, st, valuesMap, qualifier, prefs);
}
/**
* @see net.sourceforge.squirrel_sql.fw.dialects.CommonHibernateDialect#getRenameTableSQL(java.lang.String,
* java.lang.String, net.sourceforge.squirrel_sql.fw.dialects.DatabaseObjectQualifier,
* net.sourceforge.squirrel_sql.fw.dialects.SqlGenerationPreferences)
*/
@Override
public String getRenameTableSQL(String oldTableName, String newTableName,
DatabaseObjectQualifier qualifier, SqlGenerationPreferences prefs)
{
final int featureId = DialectUtils.RENAME_TABLE_TYPE;
final String msg = DialectUtils.getUnsupportedMessage(this, featureId);
throw new UnsupportedOperationException(msg);
}
/**
* @see net.sourceforge.squirrel_sql.fw.dialects.SybaseDialectExt#getViewDefinitionSQL(java.lang.String,
* net.sourceforge.squirrel_sql.fw.dialects.DatabaseObjectQualifier,
* net.sourceforge.squirrel_sql.fw.dialects.SqlGenerationPreferences)
*/
@Override
public String getViewDefinitionSQL(String viewName, DatabaseObjectQualifier qualifier,
SqlGenerationPreferences prefs)
{
return "sp_helptext " + viewName;
}
/**
* @see net.sourceforge.squirrel_sql.fw.dialects.CommonHibernateDialect#supportsRenameTable()
*/
@Override
public boolean supportsRenameTable()
{
return false;
}
/**
* @see net.sourceforge.squirrel_sql.fw.dialects.SybaseDialectExt#supportsRenameView()
*/
@Override
public boolean supportsRenameView()
{
return false;
}
/**
* @see net.sourceforge.squirrel_sql.fw.dialects.SybaseDialectExt#getRenameViewSQL(java.lang.String,
* java.lang.String, net.sourceforge.squirrel_sql.fw.dialects.DatabaseObjectQualifier,
* net.sourceforge.squirrel_sql.fw.dialects.SqlGenerationPreferences)
*/
@Override
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.CommonHibernateDialect#supportsSubSecondTimestamps()
*/
@Override
public boolean supportsSubSecondTimestamps() {
return false;
}
}