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 DB2 dialect
035: */
036: public class DB2Dialect extends org.hibernate.dialect.DB2Dialect
037: implements HibernateDialect {
038:
039: public DB2Dialect() {
040: super ();
041: registerColumnType(Types.BIGINT, "bigint");
042: registerColumnType(Types.BINARY, 254, "char($l) for bit data");
043: registerColumnType(Types.BINARY, "blob");
044: registerColumnType(Types.BIT, "smallint");
045: // DB2 spec says max=2147483647, but the driver throws an exception
046: registerColumnType(Types.BLOB, 1073741823, "blob($l)");
047: registerColumnType(Types.BLOB, "blob(1073741823)");
048: registerColumnType(Types.BOOLEAN, "smallint");
049: registerColumnType(Types.CHAR, 254, "char($l)");
050: registerColumnType(Types.CHAR, 4000, "varchar($l)");
051: registerColumnType(Types.CHAR, 32700, "long varchar");
052: registerColumnType(Types.CHAR, 1073741823, "clob($l)");
053: registerColumnType(Types.CHAR, "clob(1073741823)");
054: // DB2 spec says max=2147483647, but the driver throws an exception
055: registerColumnType(Types.CLOB, 1073741823, "clob($l)");
056: registerColumnType(Types.CLOB, "clob(1073741823)");
057: registerColumnType(Types.DATE, "date");
058: registerColumnType(Types.DECIMAL, "decimal($p,$s)");
059: registerColumnType(Types.DOUBLE, "float($p)");
060: registerColumnType(Types.FLOAT, "float($p)");
061: registerColumnType(Types.INTEGER, "int");
062: registerColumnType(Types.LONGVARBINARY, 32700,
063: "long varchar for bit data");
064: registerColumnType(Types.LONGVARBINARY, 1073741823, "blob($l)");
065: registerColumnType(Types.LONGVARBINARY, "blob(1073741823)");
066: registerColumnType(Types.LONGVARCHAR, 32700, "long varchar");
067: // DB2 spec says max=2147483647, but the driver throws an exception
068: registerColumnType(Types.LONGVARCHAR, 1073741823, "clob($l)");
069: registerColumnType(Types.LONGVARCHAR, "clob(1073741823)");
070: registerColumnType(Types.NUMERIC, "bigint");
071: registerColumnType(Types.REAL, "real");
072: registerColumnType(Types.SMALLINT, "smallint");
073: registerColumnType(Types.TIME, "time");
074: registerColumnType(Types.TIMESTAMP, "timestamp");
075: registerColumnType(Types.TINYINT, "smallint");
076: registerColumnType(Types.VARBINARY, 254,
077: "varchar($l) for bit data");
078: registerColumnType(Types.VARBINARY, "blob");
079: // The driver throws an exception for varchar with length > 3924
080: registerColumnType(Types.VARCHAR, 3924, "varchar($l)");
081: registerColumnType(Types.VARCHAR, 32700, "long varchar");
082: // DB2 spec says max=2147483647, but the driver throws an exception
083: registerColumnType(Types.VARCHAR, 1073741823, "clob($l)");
084: registerColumnType(Types.VARCHAR, "clob(1073741823)");
085:
086: }
087:
088: /* (non-Javadoc)
089: * @see net.sourceforge.squirrel_sql.plugins.dbcopy.dialects.HibernateDialect#canPasteTo(net.sourceforge.squirrel_sql.fw.sql.DatabaseObjectType)
090: */
091: public boolean canPasteTo(IDatabaseObjectInfo info) {
092: boolean result = true;
093: DatabaseObjectType type = info.getDatabaseObjectType();
094: if (type.getName().equalsIgnoreCase("database")) {
095: result = false;
096: }
097: return result;
098: }
099:
100: /* (non-Javadoc)
101: * @see net.sourceforge.squirrel_sql.plugins.dbcopy.dialects.HibernateDialect#supportsSchemasInTableDefinition()
102: */
103: public boolean supportsSchemasInTableDefinition() {
104: return true;
105: }
106:
107: /* (non-Javadoc)
108: * @see net.sourceforge.squirrel_sql.plugins.dbcopy.dialects.HibernateDialect#getLengthFunction()
109: */
110: public String getLengthFunction(int dataType) {
111: return "length";
112: }
113:
114: /* (non-Javadoc)
115: * @see net.sourceforge.squirrel_sql.plugins.dbcopy.dialects.HibernateDialect#getMaxFunction()
116: */
117: public String getMaxFunction() {
118: return "max";
119: }
120:
121: /* (non-Javadoc)
122: * @see net.sourceforge.squirrel_sql.plugins.dbcopy.dialects.HibernateDialect#getMaxPrecision(int)
123: */
124: public int getMaxPrecision(int dataType) {
125: if (dataType == Types.DOUBLE || dataType == Types.FLOAT) {
126: return 53;
127: } else {
128: return 31;
129: }
130: }
131:
132: /* (non-Javadoc)
133: * @see net.sourceforge.squirrel_sql.plugins.dbcopy.dialects.HibernateDialect#getMaxScale(int)
134: */
135: public int getMaxScale(int dataType) {
136: if (dataType == Types.DOUBLE || dataType == Types.FLOAT) {
137: // double and float have no scale - that is DECIMAL_DIGITS is null.
138: // Assume that is because it's variable - "floating" point.
139: return 0;
140: } else {
141: return getMaxPrecision(dataType);
142: }
143: }
144:
145: /* (non-Javadoc)
146: * @see net.sourceforge.squirrel_sql.plugins.dbcopy.dialects.HibernateDialect#getPrecisionDigits(int, int)
147: */
148: public int getPrecisionDigits(int columnSize, int dataType) {
149: return columnSize;
150: }
151:
152: /* (non-Javadoc)
153: * @see net.sourceforge.squirrel_sql.plugins.dbcopy.dialects.HibernateDialect#getColumnLength(int, int)
154: */
155: public int getColumnLength(int columnSize, int dataType) {
156: return columnSize;
157: }
158:
159: /**
160: * The string which identifies this dialect in the dialect chooser.
161: *
162: * @return a descriptive name that tells the user what database this dialect
163: * is design to work with.
164: */
165: public String getDisplayName() {
166: return "DB2";
167: }
168:
169: /**
170: * Returns boolean value indicating whether or not this dialect supports the
171: * specified database product/version.
172: *
173: * @param databaseProductName the name of the database as reported by
174: * DatabaseMetaData.getDatabaseProductName()
175: * @param databaseProductVersion the version of the database as reported by
176: * DatabaseMetaData.getDatabaseProductVersion()
177: * @return true if this dialect can be used for the specified product name
178: * and version; false otherwise.
179: */
180: public boolean supportsProduct(String databaseProductName,
181: String databaseProductVersion) {
182: if (databaseProductName == null) {
183: return false;
184: }
185: if (databaseProductName.trim().startsWith("DB2")) {
186: // We don't yet have the need to discriminate by version.
187: return true;
188: }
189: return false;
190: }
191:
192: /**
193: * Returns the SQL statement to use to add a column to the specified table
194: * using the information about the new column specified by info.
195: * @param info information about the new column such as type, name, etc.
196: *
197: * @return
198: * @throws UnsupportedOperationException if the database doesn't support
199: * adding columns after a table has already been created.
200: */
201: public String[] getColumnAddSQL(TableColumnInfo info)
202: throws UnsupportedOperationException {
203: ArrayList<String> result = new ArrayList<String>();
204:
205: StringBuffer addColumn = new StringBuffer();
206: addColumn.append("ALTER TABLE ");
207: addColumn.append(info.getTableName());
208: addColumn.append(" ADD ");
209: addColumn.append(info.getColumnName());
210: addColumn.append(" ");
211: addColumn.append(getTypeName(info.getDataType(), info
212: .getColumnSize(), info.getColumnSize(), info
213: .getDecimalDigits()));
214: if (info.getDefaultValue() != null) {
215: addColumn.append(" WITH DEFAULT ");
216: if (JDBCTypeMapper.isNumberType(info.getDataType())) {
217: addColumn.append(info.getDefaultValue());
218: } else {
219: addColumn.append("'");
220: addColumn.append(info.getDefaultValue());
221: addColumn.append("'");
222: }
223: }
224: result.add(addColumn.toString());
225:
226: if (info.isNullable() == "NO") {
227: // ALTER TABLE <TABLENAME> ADD CONSTRAINT NULL_FIELD CHECK (<FIELD> IS NOT
228: //NULL)
229: StringBuffer notnull = new StringBuffer();
230: notnull.append("ALTER TABLE ");
231: notnull.append(info.getTableName());
232: notnull.append(" ADD CONSTRAINT ");
233: notnull.append(info.getColumnName());
234: notnull.append(" CHECK (");
235: notnull.append(info.getColumnName());
236: notnull.append(" IS NOT NULL)");
237: result.add(notnull.toString());
238: }
239:
240: if (info.getRemarks() != null && !"".equals(info.getRemarks())) {
241: result.add(getColumnCommentAlterSQL(info));
242: }
243:
244: return result.toArray(new String[result.size()]);
245:
246: }
247:
248: /**
249: * Returns the SQL statement to use to add a comment to the specified
250: * column of the specified table.
251: *
252: * @param tableName the name of the table to create the SQL for.
253: * @param columnName the name of the column to create the SQL for.
254: * @param comment the comment to add.
255: * @return
256: * @throws UnsupportedOperationException if the database doesn't support
257: * annotating columns with a comment.
258: */
259: public String getColumnCommentAlterSQL(String tableName,
260: String columnName, String comment)
261: throws UnsupportedOperationException {
262: return DialectUtils.getColumnCommentAlterSQL(tableName,
263: columnName, comment);
264: }
265:
266: /**
267: * Returns a boolean value indicating whether or not this database dialect
268: * supports dropping columns from tables.
269: *
270: * @return true if the database supports dropping columns; false otherwise.
271: */
272: public boolean supportsDropColumn() {
273: return false;
274: }
275:
276: /**
277: * Returns the SQL that forms the command to drop the specified colum in the
278: * specified table.
279: *
280: * @param tableName the name of the table that has the column
281: * @param columnName the name of the column to drop.
282: * @return
283: * @throws UnsupportedOperationException if the database doesn't support
284: * dropping columns.
285: */
286: public String getColumnDropSQL(String tableName, String columnName) {
287: int featureId = DialectUtils.COLUMN_DROP_TYPE;
288: String msg = DialectUtils
289: .getUnsupportedMessage(this , featureId);
290: throw new UnsupportedOperationException(msg);
291: }
292:
293: /**
294: * Returns the SQL that forms the command to drop the specified table. If
295: * cascade contraints is supported by the dialect and cascadeConstraints is
296: * true, then a drop statement with cascade constraints clause will be
297: * formed.
298: *
299: * @param iTableInfo the table to drop
300: * @param cascadeConstraints whether or not to drop any FKs that may
301: * reference the specified table.
302: * @return the drop SQL command.
303: */
304: public List<String> getTableDropSQL(ITableInfo iTableInfo,
305: boolean cascadeConstraints, boolean isMaterializedView) {
306: return DialectUtils.getTableDropSQL(iTableInfo, false,
307: cascadeConstraints, false, DialectUtils.CASCADE_CLAUSE,
308: false);
309: }
310:
311: /**
312: * Returns the SQL that forms the command to add a primary key to the
313: * specified table composed of the given column names.
314: *
315: * ALTER TABLE table_name ADD CONSTRAINT contraint_name PRIMARY KEY (column_name)
316: *
317: * @param pkName the name of the constraint
318: * @param columnNames the columns that form the key
319: * @return
320: */
321: public String[] getAddPrimaryKeySQL(String pkName,
322: TableColumnInfo[] columns, ITableInfo ti) {
323: return new String[] { DialectUtils.getAddPrimaryKeySQL(ti,
324: pkName, columns, false) };
325: }
326:
327: /**
328: * Returns a boolean value indicating whether or not this dialect supports
329: * adding comments to columns.
330: *
331: * @return true if column comments are supported; false otherwise.
332: */
333: public boolean supportsColumnComment() {
334: return true;
335: }
336:
337: /**
338: * Returns the SQL statement to use to add a comment to the specified
339: * column of the specified table.
340: * @param info information about the column such as type, name, etc.
341: * @return
342: * @throws UnsupportedOperationException if the database doesn't support
343: * annotating columns with a comment.
344: */
345: public String getColumnCommentAlterSQL(TableColumnInfo info)
346: throws UnsupportedOperationException {
347: return DialectUtils.getColumnCommentAlterSQL(info);
348: }
349:
350: /**
351: * Returns a boolean value indicating whether or not this database dialect
352: * supports changing a column from null to not-null and vice versa.
353: *
354: * @return true if the database supports dropping columns; false otherwise.
355: */
356: public boolean supportsAlterColumnNull() {
357: return false;
358: }
359:
360: /**
361: * Returns the SQL used to alter the specified column to not allow null
362: * values
363: *
364: * This appears to work:
365: *
366: * ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK (column_name IS NOT NULL)
367: *
368: * However, the jdbc driver still reports the column as nullable - which means
369: * I can't reliably display the correct value for this attribute in the UI.
370: *
371: * I tried this alternate syntax and it fails with an exception:
372: *
373: * ALTER TABLE table_name ALTER COLUMN column_name SET NOT NULL
374: *
375: * Error: com.ibm.db2.jcc.b.SqlException: DB2 SQL error:
376: * SQLCODE: -104,
377: * SQLSTATE: 42601,
378: * SQLERRMC: NOT;ER COLUMN mychar SET;DEFAULT,
379: * SQL State: 42601, Error Code: -104
380: *
381: * I don't see how I can practically support changing column nullability
382: * in DB2.
383: *
384: * @param info the column to modify
385: * @return the SQL to execute
386: */
387: public String getColumnNullableAlterSQL(TableColumnInfo info) {
388: int featureId = DialectUtils.COLUMN_NULL_ALTER_TYPE;
389: String msg = DialectUtils
390: .getUnsupportedMessage(this , featureId);
391: throw new UnsupportedOperationException(msg);
392: }
393:
394: /**
395: * Returns a boolean value indicating whether or not this database dialect
396: * supports renaming columns.
397: *
398: * @return true if the database supports changing the name of columns;
399: * false otherwise.
400: */
401: public boolean supportsRenameColumn() {
402: return false;
403: }
404:
405: /**
406: * Returns the SQL that is used to change the column name.
407: *
408: *
409: * @param from the TableColumnInfo as it is
410: * @param to the TableColumnInfo as it wants to be
411: *
412: * @return the SQL to make the change
413: */
414: public String getColumnNameAlterSQL(TableColumnInfo from,
415: TableColumnInfo to) {
416: int featureId = DialectUtils.COLUMN_NAME_ALTER_TYPE;
417: String msg = DialectUtils
418: .getUnsupportedMessage(this , featureId);
419: throw new UnsupportedOperationException(msg);
420: }
421:
422: /**
423: * Returns a boolean value indicating whether or not this dialect supports
424: * modifying a columns type.
425: *
426: * @return true if supported; false otherwise
427: */
428: public boolean supportsAlterColumnType() {
429: return true;
430: }
431:
432: /**
433: * Returns the SQL that is used to change the column type.
434: *
435: * ALTER TABLE table_name ALTER COLUMN column_name SET DATA TYPE data_type
436: *
437: * @param from the TableColumnInfo as it is
438: * @param to the TableColumnInfo as it wants to be
439: *
440: * @return the SQL to make the change
441: * @throw UnsupportedOperationException if the database doesn't support
442: * modifying column types.
443: */
444: public List<String> getColumnTypeAlterSQL(TableColumnInfo from,
445: TableColumnInfo to) throws UnsupportedOperationException {
446: ArrayList<String> result = new ArrayList<String>();
447: StringBuffer tmp = new StringBuffer();
448: tmp.append("ALTER TABLE ");
449: tmp.append(from.getTableName());
450: tmp.append(" ALTER COLUMN ");
451: tmp.append(from.getColumnName());
452: tmp.append(" SET DATA TYPE ");
453: tmp.append(DialectUtils.getTypeName(to, this ));
454: result.add(tmp.toString());
455: return result;
456: }
457:
458: /**
459: * Returns a boolean value indicating whether or not this database dialect
460: * supports changing a column's default value.
461: *
462: * @return true if the database supports modifying column defaults; false
463: * otherwise
464: */
465: public boolean supportsAlterColumnDefault() {
466: return true;
467: }
468:
469: /**
470: * Returns the SQL command to change the specified column's default value
471: *
472: * ALTER TABLE EMPLOYEE ALTER COLUMN WORKDEPTSET SET DEFAULT '123'
473: *
474: * @param info the column to modify and it's default value.
475: * @return SQL to make the change
476: */
477: public String getColumnDefaultAlterSQL(TableColumnInfo info) {
478: String alterClause = DialectUtils.ALTER_COLUMN_CLAUSE;
479: String defaultClause = DialectUtils.SET_DEFAULT_CLAUSE;
480: return DialectUtils.getColumnDefaultAlterSQL(this , info,
481: alterClause, false, defaultClause);
482: }
483:
484: /**
485: * Returns the SQL command to drop the specified table's primary key.
486: *
487: * @param pkName the name of the primary key that should be dropped
488: * @param tableName the name of the table whose primary key should be
489: * dropped
490: * @return
491: */
492: public String getDropPrimaryKeySQL(String pkName, String tableName) {
493: return DialectUtils.getDropPrimaryKeySQL(pkName, tableName,
494: false, false);
495: }
496:
497: /**
498: * Returns the SQL command to drop the specified table's foreign key
499: * constraint.
500: *
501: * @param fkName the name of the foreign key that should be dropped
502: * @param tableName the name of the table whose foreign key should be
503: * dropped
504: * @return
505: */
506: public String getDropForeignKeySQL(String fkName, String tableName) {
507: return DialectUtils.getDropForeignKeySQL(fkName, tableName);
508: }
509:
510: /**
511: * Returns the SQL command to create the specified table.
512: *
513: * @param tables the tables to get create statements for
514: * @param md the metadata from the ISession
515: * @param prefs preferences about how the resultant SQL commands should be
516: * formed.
517: * @param isJdbcOdbc whether or not the connection is via JDBC-ODBC bridge.
518: *
519: * @return the SQL that is used to create the specified table
520: */
521: public List<String> getCreateTableSQL(List<ITableInfo> tables,
522: ISQLDatabaseMetaData md, CreateScriptPreferences prefs,
523: boolean isJdbcOdbc) throws SQLException {
524: return DialectUtils.getCreateTableSQL(tables, md, this, prefs,
525: isJdbcOdbc);
526: }
527:
528: }
|