001: /*
002: * Copyright (C) 2006 Rob Manning
003: * manningr@users.sourceforge.net
004: *
005: * This program is free software; you can redistribute it and/or
006: * modify it under the terms of the GNU General Public License
007: * as published by the Free Software Foundation; either version 2
008: * of the License, or any later version.
009: *
010: * This program is distributed in the hope that it will be useful,
011: * but WITHOUT ANY WARRANTY; without even the implied warranty of
012: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
013: * GNU General Public License for more details.
014: *
015: * You should have received a copy of the GNU General Public License
016: * along with this program; if not, write to the Free Software
017: * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
018: */
019: package net.sourceforge.squirrel_sql.fw.dialects;
020:
021: import java.sql.SQLException;
022: import java.sql.Types;
023: import java.util.ArrayList;
024: import java.util.List;
025:
026: import net.sourceforge.squirrel_sql.fw.sql.DatabaseObjectType;
027: import net.sourceforge.squirrel_sql.fw.sql.IDatabaseObjectInfo;
028: import net.sourceforge.squirrel_sql.fw.sql.ISQLDatabaseMetaData;
029: import net.sourceforge.squirrel_sql.fw.sql.ITableInfo;
030: import net.sourceforge.squirrel_sql.fw.sql.JDBCTypeMapper;
031: import net.sourceforge.squirrel_sql.fw.sql.TableColumnInfo;
032:
033: /**
034: * An extension to the standard Hibernate SQL Server dialect
035: */
036:
037: public class SQLServerDialect extends
038: org.hibernate.dialect.SQLServerDialect implements
039: HibernateDialect {
040:
041: public SQLServerDialect() {
042: super ();
043: registerColumnType(Types.BIGINT, "bigint");
044: registerColumnType(Types.BINARY, "image");
045: registerColumnType(Types.BIT, "tinyint");
046: registerColumnType(Types.BLOB, "image");
047: registerColumnType(Types.BOOLEAN, "tinyint");
048: registerColumnType(Types.CHAR, 8000, "char($l)");
049: registerColumnType(Types.CHAR, "text");
050: registerColumnType(Types.CLOB, "text");
051: registerColumnType(Types.DATE, "datetime");
052: registerColumnType(Types.DECIMAL, "decimal($p)");
053: registerColumnType(Types.DOUBLE, "float($p)");
054: registerColumnType(Types.FLOAT, "float($p)");
055: registerColumnType(Types.INTEGER, "int");
056: registerColumnType(Types.LONGVARBINARY, "image");
057: registerColumnType(Types.LONGVARCHAR, "text");
058: registerColumnType(Types.NUMERIC, "numeric($p)");
059: registerColumnType(Types.REAL, "real");
060: registerColumnType(Types.SMALLINT, "smallint");
061: registerColumnType(Types.TIME, "datetime");
062: registerColumnType(Types.TIMESTAMP, "datetime");
063: registerColumnType(Types.TINYINT, "tinyint");
064: registerColumnType(Types.VARBINARY, 8000, "varbinary($l)");
065: registerColumnType(Types.VARBINARY, "image");
066: registerColumnType(Types.VARCHAR, 8000, "varchar($l)");
067: registerColumnType(Types.VARCHAR, "text");
068: }
069:
070: /* (non-Javadoc)
071: * @see net.sourceforge.squirrel_sql.plugins.dbcopy.dialects.HibernateDialect#canPasteTo(net.sourceforge.squirrel_sql.fw.sql.DatabaseObjectType)
072: */
073: public boolean canPasteTo(IDatabaseObjectInfo info) {
074: boolean result = true;
075: DatabaseObjectType type = info.getDatabaseObjectType();
076: if (type.getName().equalsIgnoreCase("database")
077: || type.getName().equalsIgnoreCase("catalog")) {
078: result = false;
079: }
080: return result;
081: }
082:
083: /* (non-Javadoc)
084: * @see net.sourceforge.squirrel_sql.plugins.dbcopy.dialects.HibernateDialect#supportsSchemasInTableDefinition()
085: */
086: public boolean supportsSchemasInTableDefinition() {
087: return true;
088: }
089:
090: /* (non-Javadoc)
091: * @see net.sourceforge.squirrel_sql.plugins.dbcopy.dialects.HibernateDialect#getLengthFunction()
092: */
093: public String getLengthFunction(int dataType) {
094: return "len";
095: }
096:
097: /* (non-Javadoc)
098: * @see net.sourceforge.squirrel_sql.plugins.dbcopy.dialects.HibernateDialect#getMaxFunction()
099: */
100: public String getMaxFunction() {
101: return "max";
102: }
103:
104: /* (non-Javadoc)
105: * @see net.sourceforge.squirrel_sql.plugins.dbcopy.dialects.HibernateDialect#getMaxPrecision(int)
106: */
107: public int getMaxPrecision(int dataType) {
108: if (dataType == Types.DOUBLE || dataType == Types.FLOAT) {
109: return 53;
110: } else {
111: return 38;
112: }
113: }
114:
115: /* (non-Javadoc)
116: * @see net.sourceforge.squirrel_sql.plugins.dbcopy.dialects.HibernateDialect#getMaxScale(int)
117: */
118: public int getMaxScale(int dataType) {
119: return getMaxPrecision(dataType);
120: }
121:
122: /* (non-Javadoc)
123: * @see net.sourceforge.squirrel_sql.plugins.dbcopy.dialects.HibernateDialect#getPrecisionDigits(int, int)
124: */
125: public int getPrecisionDigits(int columnSize, int dataType) {
126: return columnSize;
127: }
128:
129: /* (non-Javadoc)
130: * @see net.sourceforge.squirrel_sql.plugins.dbcopy.dialects.HibernateDialect#getColumnLength(int, int)
131: */
132: public int getColumnLength(int columnSize, int dataType) {
133: return columnSize;
134: }
135:
136: /**
137: * The string which identifies this dialect in the dialect chooser.
138: *
139: * @return a descriptive name that tells the user what database this dialect
140: * is design to work with.
141: */
142: public String getDisplayName() {
143: return "MS SQLServer";
144: }
145:
146: /**
147: * Returns boolean value indicating whether or not this dialect supports the
148: * specified database product/version.
149: *
150: * @param databaseProductName the name of the database as reported by
151: * DatabaseMetaData.getDatabaseProductName()
152: * @param databaseProductVersion the version of the database as reported by
153: * DatabaseMetaData.getDatabaseProductVersion()
154: * @return true if this dialect can be used for the specified product name
155: * and version; false otherwise.
156: */
157: public boolean supportsProduct(String databaseProductName,
158: String databaseProductVersion) {
159: if (databaseProductName == null) {
160: return false;
161: }
162: if (databaseProductName.trim().toLowerCase().startsWith(
163: "microsoft")) {
164: // We don't yet have the need to discriminate by version.
165: return true;
166: }
167: return false;
168: }
169:
170: /**
171: * Returns the SQL statement to use to add a column to the specified table
172: * using the information about the new column specified by info.
173: * @param info information about the new column such as type, name, etc.
174: *
175: * @return
176: * @throws UnsupportedOperationException if the database doesn't support
177: * adding columns after a table has already been created.
178: */
179: public String[] getColumnAddSQL(TableColumnInfo info)
180: throws UnsupportedOperationException {
181: if (info.getDefaultValue() != null) {
182: return new String[] {
183: DialectUtils.getColumnAddSQL(info, this , false,
184: true, true), getColumnDefaultAlterSQL(info) };
185: } else {
186: return new String[] { DialectUtils.getColumnAddSQL(info,
187: this , false, true, true), };
188: }
189: }
190:
191: /**
192: * Returns a boolean value indicating whether or not this dialect supports
193: * adding comments to columns.
194: *
195: * @return true if column comments are supported; false otherwise.
196: */
197: public boolean supportsColumnComment() {
198: return false;
199: }
200:
201: /**
202: * Returns the SQL statement to use to add a comment to the specified
203: * column of the specified table.
204: * @param info information about the column such as type, name, etc.
205: * @return
206: * @throws UnsupportedOperationException if the database doesn't support
207: * annotating columns with a comment.
208: */
209: public String getColumnCommentAlterSQL(TableColumnInfo info)
210: throws UnsupportedOperationException {
211: throw new UnsupportedOperationException(
212: "SQL-Server doesn't support column comments");
213: }
214:
215: /**
216: * Returns a boolean value indicating whether or not this database dialect
217: * supports dropping columns from tables.
218: *
219: * @return true if the database supports dropping columns; false otherwise.
220: */
221: public boolean supportsDropColumn() {
222: return true;
223: }
224:
225: /**
226: * Returns the SQL that forms the command to drop the specified colum in the
227: * specified table.
228: *
229: * @param tableName the name of the table that has the column
230: * @param columnName the name of the column to drop.
231: * @return
232: * @throws UnsupportedOperationException if the database doesn't support
233: * dropping columns.
234: */
235: public String getColumnDropSQL(String tableName, String columnName) {
236: // TODO: Need to verify this
237: return DialectUtils.getColumnDropSQL(tableName, columnName,
238: "DROP COLUMN", false, null);
239: }
240:
241: /**
242: * Returns the SQL that forms the command to drop the specified table. If
243: * cascade contraints is supported by the dialect and cascadeConstraints is
244: * true, then a drop statement with cascade constraints clause will be
245: * formed.
246: *
247: * @param iTableInfo the table to drop
248: * @param cascadeConstraints whether or not to drop any FKs that may
249: * reference the specified table.
250: * @return the drop SQL command.
251: */
252: public List<String> getTableDropSQL(ITableInfo iTableInfo,
253: boolean cascadeConstraints, boolean isMaterializedView) {
254: return DialectUtils.getTableDropSQL(iTableInfo, false,
255: cascadeConstraints, false, DialectUtils.CASCADE_CLAUSE,
256: false);
257: }
258:
259: /**
260: * Returns the SQL that forms the command to add a primary key to the
261: * specified table composed of the given column names.
262: *
263: * alter table test alter column mycol integer not null
264: * alter table test add primary key (mycol)
265: *
266: * @param pkName the name of the constraint
267: * @param colInfos the columns that form the key
268: * @return
269: */
270: public String[] getAddPrimaryKeySQL(String pkName,
271: TableColumnInfo[] colInfos, ITableInfo ti) {
272: ArrayList<String> result = new ArrayList<String>();
273: String alterClause = DialectUtils.ALTER_COLUMN_CLAUSE;
274: // convert all columns in key to not null - this doesn't hurt if they
275: // are already null.
276: DialectUtils.getMultiColNotNullSQL(colInfos, this , alterClause,
277: true, result);
278:
279: String pkSQL = DialectUtils.getAddPrimaryKeySQL(ti, pkName,
280: colInfos, false);
281: result.add(pkSQL);
282:
283: return result.toArray(new String[result.size()]);
284: }
285:
286: /**
287: * Returns a boolean value indicating whether or not this database dialect
288: * supports changing a column from null to not-null and vice versa.
289: *
290: * @return true if the database supports dropping columns; false otherwise.
291: */
292: public boolean supportsAlterColumnNull() {
293: return true;
294: }
295:
296: /**
297: * Returns the SQL used to alter the specified column to not allow null
298: * values
299: *
300: * alter table mytest alter column mycol integer not null
301: *
302: * @param info the column to modify
303: * @return the SQL to execute
304: */
305: public String getColumnNullableAlterSQL(TableColumnInfo info) {
306: String alterClause = DialectUtils.ALTER_COLUMN_CLAUSE;
307: return DialectUtils.getColumnNullableAlterSQL(info, this ,
308: alterClause, true);
309: }
310:
311: /**
312: * Returns a boolean value indicating whether or not this database dialect
313: * supports renaming columns.
314: *
315: * @return true if the database supports changing the name of columns;
316: * false otherwise.
317: */
318: public boolean supportsRenameColumn() {
319: return true;
320: }
321:
322: /**
323: * Returns the SQL that is used to change the column name.
324: *
325: * exec sp_rename 'test.renameCol', newNameCol, 'COLUMN'
326: *
327: * @param from the TableColumnInfo as it is
328: * @param to the TableColumnInfo as it wants to be
329: *
330: * @return the SQL to make the change
331: */
332: public String getColumnNameAlterSQL(TableColumnInfo from,
333: TableColumnInfo to) {
334: StringBuffer result = new StringBuffer();
335: result.append("exec sp_rename ");
336: result.append("'");
337: result.append(from.getTableName());
338: result.append(".");
339: result.append(from.getColumnName());
340: result.append("'");
341: result.append(", ");
342: result.append(to.getColumnName());
343: result.append(", 'COLUMN'");
344: return result.toString();
345: }
346:
347: /**
348: * Returns a boolean value indicating whether or not this dialect supports
349: * modifying a columns type.
350: *
351: * @return true if supported; false otherwise
352: */
353: public boolean supportsAlterColumnType() {
354: return true;
355: }
356:
357: /**
358: * Returns the SQL that is used to change the column type.
359: *
360: * ALTER TABLE doc_exy ALTER COLUMN column_a DECIMAL (5, 2)
361: *
362: * @param from the TableColumnInfo as it is
363: * @param to the TableColumnInfo as it wants to be
364: *
365: * @return the SQL to make the change
366: * @throw UnsupportedOperationException if the database doesn't support
367: * modifying column types.
368: */
369: public List<String> getColumnTypeAlterSQL(TableColumnInfo from,
370: TableColumnInfo to) throws UnsupportedOperationException {
371: ArrayList<String> list = new ArrayList<String>();
372: StringBuffer result = new StringBuffer();
373: result.append("ALTER TABLE ");
374: result.append(from.getTableName());
375: result.append(" ALTER COLUMN ");
376: result.append(from.getColumnName());
377: result.append(" ");
378: result.append(DialectUtils.getTypeName(to, this ));
379: list.add(result.toString());
380: return list;
381: }
382:
383: /**
384: * Returns a boolean value indicating whether or not this database dialect
385: * supports changing a column's default value.
386: *
387: * @return true if the database supports modifying column defaults; false
388: * otherwise
389: */
390: public boolean supportsAlterColumnDefault() {
391: return true;
392: }
393:
394: /**
395: * Returns the SQL command to change the specified column's default value
396: *
397: * ALTER TABLE table ADD CONSTRAINT table_c_def DEFAULT 50 FOR column_b ;
398: *
399: * @param info the column to modify and it's default value.
400: * @return SQL to make the change
401: */
402: public String getColumnDefaultAlterSQL(TableColumnInfo info) {
403: StringBuffer result = new StringBuffer();
404: result.append("ALTER TABLE ");
405: result.append(info.getTableName());
406: result.append(" ADD CONSTRAINT ");
407: result.append(info.getTableName()).append("_").append(
408: info.getColumnName()).append("_default");
409: result.append(" DEFAULT ");
410: if (JDBCTypeMapper.isNumberType(info.getDataType())) {
411: result.append(info.getDefaultValue());
412: } else {
413: result.append("'");
414: result.append(info.getDefaultValue());
415: result.append("'");
416: }
417: result.append(" FOR ");
418: result.append(info.getColumnName());
419: return result.toString();
420: }
421:
422: /**
423: * Returns the SQL command to drop the specified table's primary key.
424: *
425: * @param pkName the name of the primary key that should be dropped
426: * @param tableName the name of the table whose primary key should be
427: * dropped
428: * @return
429: */
430: public String getDropPrimaryKeySQL(String pkName, String tableName) {
431: return DialectUtils.getDropPrimaryKeySQL(pkName, tableName,
432: true, false);
433: }
434:
435: /**
436: * Returns the SQL command to drop the specified table's foreign key
437: * constraint.
438: *
439: * @param fkName the name of the foreign key that should be dropped
440: * @param tableName the name of the table whose foreign key should be
441: * dropped
442: * @return
443: */
444: public String getDropForeignKeySQL(String fkName, String tableName) {
445: return DialectUtils.getDropForeignKeySQL(fkName, tableName);
446: }
447:
448: /**
449: * Returns the SQL command to create the specified table.
450: *
451: * @param tables the tables to get create statements for
452: * @param md the metadata from the ISession
453: * @param prefs preferences about how the resultant SQL commands should be
454: * formed.
455: * @param isJdbcOdbc whether or not the connection is via JDBC-ODBC bridge.
456: *
457: * @return the SQL that is used to create the specified table
458: */
459: public List<String> getCreateTableSQL(List<ITableInfo> tables,
460: ISQLDatabaseMetaData md, CreateScriptPreferences prefs,
461: boolean isJdbcOdbc) throws SQLException {
462: return DialectUtils.getCreateTableSQL(tables, md, this, prefs,
463: isJdbcOdbc);
464: }
465:
466: }
|