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