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 PostgreSQL dialect
035: */
036:
037: public class PostgreSQLDialect extends
038: org.hibernate.dialect.PostgreSQLDialect implements
039: HibernateDialect {
040:
041: public PostgreSQLDialect() {
042: super ();
043: registerColumnType(Types.BIGINT, "bigint");
044: registerColumnType(Types.BINARY, "bytea");
045: // PostgreSQL follows the standard for SQL BIT. It's a string of BITs.
046: // So bit(10) is a string of 10 bits. JDBC treats SQL BIT as if it
047: // were only a single BIT. It specifies that BIT is equivalent to
048: // BOOLEAN. It claims that the PreparedStatement set method that should
049: // be used with BIT is setBoolean and getBoolean. This is not compliant
050: // with the standard. So SQL BIT type support is broken in Java, there
051: // is nothing we can do about that.
052: // Best thing to do for now, is try to convert the BIT type to a
053: // boolean like the JDBC spec says and hope for the best. Hope that the
054: // source database isn't using the BIT column as a sequence of multiple
055: // BITs.
056: registerColumnType(Types.BIT, "bool");
057: registerColumnType(Types.BLOB, "bytea");
058: registerColumnType(Types.BOOLEAN, "bool");
059: registerColumnType(Types.CHAR, 8000, "char($l)");
060: registerColumnType(Types.CHAR, "text");
061: registerColumnType(Types.CLOB, "text");
062: registerColumnType(Types.DATE, "date");
063: registerColumnType(Types.DECIMAL, "decimal($p,2)");
064: registerColumnType(Types.DOUBLE, "float($p)");
065: registerColumnType(Types.FLOAT, "float($p)");
066: registerColumnType(Types.INTEGER, "int");
067: registerColumnType(Types.LONGVARBINARY, "bytea");
068: registerColumnType(Types.LONGVARCHAR, "text");
069: registerColumnType(Types.NUMERIC, "numeric($p)");
070: registerColumnType(Types.REAL, "real");
071: registerColumnType(Types.SMALLINT, "smallint");
072: registerColumnType(Types.TIME, "time");
073: registerColumnType(Types.TIMESTAMP, "timestamp");
074: registerColumnType(Types.TINYINT, "int");
075: registerColumnType(Types.VARBINARY, "bytea");
076: registerColumnType(Types.VARCHAR, 8000, "varchar($l)");
077: registerColumnType(Types.VARCHAR, "text");
078: }
079:
080: /* (non-Javadoc)
081: * @see net.sourceforge.squirrel_sql.plugins.dbcopy.dialects.HibernateDialect#canPasteTo(net.sourceforge.squirrel_sql.fw.sql.DatabaseObjectType)
082: */
083: public boolean canPasteTo(IDatabaseObjectInfo info) {
084: boolean result = true;
085: DatabaseObjectType type = info.getDatabaseObjectType();
086: if (type.getName().equalsIgnoreCase("database")) {
087: result = false;
088: }
089: return result;
090: }
091:
092: /* (non-Javadoc)
093: * @see net.sourceforge.squirrel_sql.plugins.dbcopy.dialects.HibernateDialect#supportsSchemasInTableDefinition()
094: */
095: public boolean supportsSchemasInTableDefinition() {
096: return true;
097: }
098:
099: /* (non-Javadoc)
100: * @see net.sourceforge.squirrel_sql.plugins.dbcopy.dialects.HibernateDialect#getLengthFunction()
101: */
102: public String getLengthFunction(int dataType) {
103: return "length";
104: }
105:
106: /* (non-Javadoc)
107: * @see net.sourceforge.squirrel_sql.plugins.dbcopy.dialects.HibernateDialect#getMaxFunction()
108: */
109: public String getMaxFunction() {
110: return "max";
111: }
112:
113: /* (non-Javadoc)
114: * @see net.sourceforge.squirrel_sql.plugins.dbcopy.dialects.HibernateDialect#getMaxPrecision(int)
115: */
116: public int getMaxPrecision(int dataType) {
117: if (dataType == Types.DOUBLE || dataType == Types.FLOAT) {
118: return 53;
119: } else {
120: return 38;
121: }
122: }
123:
124: /* (non-Javadoc)
125: * @see net.sourceforge.squirrel_sql.plugins.dbcopy.dialects.HibernateDialect#getMaxScale(int)
126: */
127: public int getMaxScale(int dataType) {
128: return getMaxPrecision(dataType);
129: }
130:
131: /* (non-Javadoc)
132: * @see net.sourceforge.squirrel_sql.plugins.dbcopy.dialects.HibernateDialect#getPrecisionDigits(int, int)
133: */
134: public int getPrecisionDigits(int columnSize, int dataType) {
135: if (columnSize == 2) {
136: return 5;
137: }
138: if (columnSize == 4) {
139: return 10;
140: }
141: return 19;
142: }
143:
144: /* (non-Javadoc)
145: * @see net.sourceforge.squirrel_sql.plugins.dbcopy.dialects.HibernateDialect#getColumnLength(int, int)
146: */
147: public int getColumnLength(int columnSize, int dataType) {
148: if (dataType == Types.VARCHAR && columnSize == -1) {
149: // PostgreSQL 8.0 reports length as -1 sometimes. Why??
150: return 2000;
151: }
152: return columnSize;
153: }
154:
155: /**
156: * The string which identifies this dialect in the dialect chooser.
157: *
158: * @return a descriptive name that tells the user what database this dialect
159: * is design to work with.
160: */
161: public String getDisplayName() {
162: return "PostgreSQL";
163: }
164:
165: /**
166: * Returns boolean value indicating whether or not this dialect supports the
167: * specified database product/version.
168: *
169: * @param databaseProductName the name of the database as reported by
170: * DatabaseMetaData.getDatabaseProductName()
171: * @param databaseProductVersion the version of the database as reported by
172: * DatabaseMetaData.getDatabaseProductVersion()
173: * @return true if this dialect can be used for the specified product name
174: * and version; false otherwise.
175: */
176: public boolean supportsProduct(String databaseProductName,
177: String databaseProductVersion) {
178: if (databaseProductName == null) {
179: return false;
180: }
181: if (databaseProductName.trim().toLowerCase().startsWith(
182: "postgresql")) {
183: // We don't yet have the need to discriminate by version.
184: return true;
185: }
186: return false;
187: }
188:
189: /**
190: * Returns the SQL statement to use to add a column to the specified table
191: * using the information about the new column specified by info.
192: * @param info information about the new column such as type, name, etc.
193: *
194: * @return
195: * @throws UnsupportedOperationException if the database doesn't support
196: * adding columns after a table has already been created.
197: */
198: public String[] getColumnAddSQL(TableColumnInfo info)
199: throws UnsupportedOperationException {
200: ArrayList<String> result = new ArrayList<String>();
201: result.add(DialectUtils.getColumnAddSQL(info, this , true, true,
202: true));
203: if (info.getRemarks() != null && !"".equals(info.getRemarks())) {
204: result.add(getColumnCommentAlterSQL(info));
205: }
206: return result.toArray(new String[result.size()]);
207: }
208:
209: /**
210: * Returns a boolean value indicating whether or not this dialect supports
211: * adding comments to columns.
212: *
213: * @return true if column comments are supported; false otherwise.
214: */
215: public boolean supportsColumnComment() {
216: return true;
217: }
218:
219: /**
220: * Returns a boolean value indicating whether or not this database dialect
221: * supports dropping columns from tables.
222: *
223: * @return true if the database supports dropping columns; false otherwise.
224: */
225: public boolean supportsDropColumn() {
226: return true;
227: }
228:
229: /**
230: * Returns the SQL that forms the command to drop the specified colum in the
231: * specified table.
232: *
233: * @param tableName the name of the table that has the column
234: * @param columnName the name of the column to drop.
235: * @return
236: * @throws UnsupportedOperationException if the database doesn't support
237: * dropping columns.
238: */
239: public String getColumnDropSQL(String tableName, String columnName) {
240: return DialectUtils.getColumnDropSQL(tableName, columnName);
241: }
242:
243: /**
244: * Returns the SQL that forms the command to drop the specified table. If
245: * cascade contraints is supported by the dialect and cascadeConstraints is
246: * true, then a drop statement with cascade constraints clause will be
247: * formed.
248: *
249: * @param iTableInfo the table to drop
250: * @param cascadeConstraints whether or not to drop any FKs that may
251: * reference the specified table.
252: * @return the drop SQL command.
253: */
254: public List<String> getTableDropSQL(ITableInfo iTableInfo,
255: boolean cascadeConstraints, boolean isMaterializedView) {
256: // TODO: Need to verify this
257: return DialectUtils.getTableDropSQL(iTableInfo, true,
258: cascadeConstraints, false, DialectUtils.CASCADE_CLAUSE,
259: false);
260: }
261:
262: /**
263: * Returns the SQL that forms the command to add a primary key to the
264: * specified table composed of the given column names.
265: *
266: * @param pkName the name of the constraint
267: * @param columnNames the columns that form the key
268: * @return
269: */
270: public String[] getAddPrimaryKeySQL(String pkName,
271: TableColumnInfo[] colInfos, ITableInfo ti) {
272: return new String[] { DialectUtils.getAddPrimaryKeySQL(ti,
273: pkName, colInfos, false) };
274: }
275:
276: /**
277: * Returns the SQL statement to use to add a comment to the specified
278: * column of the specified table.
279: * @param info information about the column such as type, name, etc.
280: * @return
281: * @throws UnsupportedOperationException if the database doesn't support
282: * annotating columns with a comment.
283: */
284: public String getColumnCommentAlterSQL(TableColumnInfo info)
285: throws UnsupportedOperationException {
286: return DialectUtils.getColumnCommentAlterSQL(info
287: .getTableName(), info.getColumnName(), info
288: .getRemarks());
289:
290: }
291:
292: /**
293: * Returns the SQL used to alter the specified column to not allow null
294: * values
295: *
296: * ALTER TABLE products ALTER COLUMN product_no SET NOT NULL
297: *
298: * ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL
299: *
300: * @param info the column to modify
301: * @return the SQL to execute
302: */
303: public String getColumnNullableAlterSQL(TableColumnInfo info) {
304: StringBuffer result = new StringBuffer();
305: result.append("ALTER TABLE ");
306: result.append(info.getTableName());
307: result.append(" ALTER COLUMN ");
308: result.append(info.getColumnName());
309: if (info.isNullable().equalsIgnoreCase("YES")) {
310: result.append(" DROP NOT NULL");
311: } else {
312: result.append(" SET NOT NULL");
313: }
314: return result.toString();
315: }
316:
317: /**
318: * Returns a boolean value indicating whether or not this database dialect
319: * supports renaming columns.
320: *
321: * @return true if the database supports changing the name of columns;
322: * false otherwise.
323: */
324: public boolean supportsRenameColumn() {
325: // TODO: need to verify this
326: return true;
327: }
328:
329: /**
330: * Returns the SQL that is used to change the column name.
331: *
332: * ALTER TABLE a RENAME COLUMN x TO y
333: *
334: * @param from the TableColumnInfo as it is
335: * @param to the TableColumnInfo as it wants to be
336: *
337: * @return the SQL to make the change
338: */
339: public String getColumnNameAlterSQL(TableColumnInfo from,
340: TableColumnInfo to) {
341: String alterClause = DialectUtils.RENAME_COLUMN_CLAUSE;
342: String toClause = DialectUtils.TO_CLAUSE;
343: return DialectUtils.getColumnNameAlterSQL(from, to,
344: alterClause, toClause);
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 products ALTER COLUMN price TYPE numeric(10,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(to.getColumnName());
377: result.append(" TYPE ");
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 from null to not-null and vice versa.
386: *
387: * @return true if the database supports dropping columns; false otherwise.
388: */
389: public boolean supportsAlterColumnNull() {
390: return true;
391: }
392:
393: /**
394: * Returns a boolean value indicating whether or not this database dialect
395: * supports changing a column's default value.
396: *
397: * @return true if the database supports modifying column defaults; false
398: * otherwise
399: */
400: public boolean supportsAlterColumnDefault() {
401: return true;
402: }
403:
404: /**
405: * Returns the SQL command to change the specified column's default value
406: *
407: * @param info the column to modify and it's default value.
408: * @return SQL to make the change
409: */
410: public String getColumnDefaultAlterSQL(TableColumnInfo info) {
411: StringBuffer result = new StringBuffer();
412: result.append("ALTER TABLE ");
413: result.append(info.getTableName());
414: result.append(" ALTER COLUMN ");
415: result.append(info.getColumnName());
416: String defVal = info.getDefaultValue();
417: if (defVal == null || "".equals(defVal)) {
418: result.append(" DROP DEFAULT");
419: } else {
420: result.append(" SET DEFAULT ");
421: if (JDBCTypeMapper.isNumberType(info.getDataType())) {
422: result.append(defVal);
423: } else {
424: result.append("'");
425: result.append(defVal);
426: result.append("'");
427: }
428: }
429: return result.toString();
430: }
431:
432: /**
433: * Returns the SQL command to drop the specified table's primary key.
434: *
435: * @param pkName the name of the primary key that should be dropped
436: * @param tableName the name of the table whose primary key should be
437: * dropped
438: * @return
439: */
440: public String getDropPrimaryKeySQL(String pkName, String tableName) {
441: return DialectUtils.getDropPrimaryKeySQL(pkName, tableName,
442: true, false);
443: }
444:
445: /**
446: * Returns the SQL command to drop the specified table's foreign key
447: * constraint.
448: *
449: * @param fkName the name of the foreign key that should be dropped
450: * @param tableName the name of the table whose foreign key should be
451: * dropped
452: * @return
453: */
454: public String getDropForeignKeySQL(String fkName, String tableName) {
455: return DialectUtils.getDropForeignKeySQL(fkName, tableName);
456: }
457:
458: /**
459: * Returns the SQL command to create the specified table.
460: *
461: * @param tables the tables to get create statements for
462: * @param md the metadata from the ISession
463: * @param prefs preferences about how the resultant SQL commands should be
464: * formed.
465: * @param isJdbcOdbc whether or not the connection is via JDBC-ODBC bridge.
466: *
467: * @return the SQL that is used to create the specified table
468: */
469: public List<String> getCreateTableSQL(List<ITableInfo> tables,
470: ISQLDatabaseMetaData md, CreateScriptPreferences prefs,
471: boolean isJdbcOdbc) throws SQLException {
472: return DialectUtils.getCreateTableSQL(tables, md, this, prefs,
473: isJdbcOdbc);
474: }
475:
476: }
|