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