Hello,
I would like to contribute the following change to hibernate and would
appreciate any feedback very much.
The need for this change arose in a project where we had the necessity to alter
the length of varchar columns. It adds the ability to alter a table
columns type
with PostgreSQL >= 8.0.
Additionally it extracts the methods: getUniqueConstraintString(),
getCheckConstraintString() and the getColumnCommentString(). This prepares for
some more alter table statements as adding/removing a constraint.
Not solved yet:
* PostgreSQLDialect should be compatible with PostgreSQL < 8.0. One possible
solution for this issue could be two versions: PostgreSQLDialect and
PostgreSQL8Dialect or similar. Any other ideas?
* Other Dialects should get the alter type statement if they allow it. This
would be a little time consuming for me to implement. Is it a must have?
Greets Frank Sattelberger
--- ./Hibernate3/src/org/hibernate/dialect/Dialect.java 2005-11-27
23:42:03.000000000 +0100
+++ ../Hibernate3/src/org/hibernate/dialect/Dialect.java 2005-12-11
20:41:11.000000000 +0100
@@ -941,11 +941,28 @@
/**
* The name of the database-specific SQL function for retrieving the
* current timestamp.
*
* @return The function name.
*/
public String getCurrentTimestampSQLFunctionName() {
// the standard SQL function name is current_timestamp...
return "current_timestamp";
}
+
+ /**
+ * Does this dialect support the alteration of column types?
+ *
+ * @return <code>true</code> if this is supported,
<code>false</code> otherwise.
+ */
+ public boolean supportsAlterColumnType() {
+ return false;
+ }
+
+ public String getAlterColumnString() {
+ throw new UnsupportedOperationException( "No alter column
syntax supported by Dialect" );
+ }
+
+ public String getAlterColumnTypeString() {
+ throw new UnsupportedOperationException( "No alter column
type syntax supported by Dialect" );
+ }
}
--- ./Hibernate3/src/org/hibernate/dialect/PostgreSQLDialect.java
2005-12-06
04:45:06.000000000 +0100
+++ ../Hibernate3/src/org/hibernate/dialect/PostgreSQLDialect.java
2005-12-11
20:41:30.000000000 +0100
@@ -110,20 +110,28 @@
registerFunction( "str", new
SQLFunctionTemplate(Hibernate.STRING,
"cast(?1 as varchar)") );
getDefaultProperties().setProperty(Environment.STATEMENT_BATCH_SIZE,
DEFAULT_BATCH_SIZE);
}
public String getAddColumnString() {
return "add column";
}
+ public String getAlterColumnString() {
+ return "alter column";
+ }
+
+ public String getAlterColumnTypeString() {
+ return "type";
+ }
+
public String getSequenceNextValString(String sequenceName) {
return "select " + getSelectSequenceNextValString(
sequenceName );
}
public String getSelectSequenceNextValString(String sequenceName) {
return "nextval ('" + sequenceName + "')";
}
public String getCreateSequenceString(String sequenceName) {
return "create sequence " + sequenceName; //starts with
1, implicitly
@@ -258,20 +266,24 @@
}
public String toBooleanValueString(boolean bool) {
return bool ? "true" : "false";
}
public ViolatedConstraintNameExtracter
getViolatedConstraintNameExtracter() {
return EXTRACTER;
}
+ public boolean supportsAlterColumnType() {
+ return true;
+ }
+
/**
* Constraint-name extractor for Postgres contraint violation exceptions.
* Orginally contributed by Denny Bartelt.
*/
private static ViolatedConstraintNameExtracter EXTRACTER = new
TemplatedViolatedConstraintNameExtracter() {
public String extractConstraintName(SQLException sqle) {
try {
int sqlState = Integer.valueOf(
JDBCExceptionHelper.extractSqlState(sqle)).intValue();
switch (sqlState) {
// CHECK VIOLATION
--- ./Hibernate3/src/org/hibernate/mapping/Column.java 2005-11-11
00:58:54.000000000 +0100
+++ ../Hibernate3/src/org/hibernate/mapping/Column.java 2005-12-11
19:32:10.000000000 +0100
@@ -277,20 +277,49 @@
}
public void setDefaultValue(String defaultValue) {
this.defaultValue = defaultValue;
}
public String getCanonicalName() {
return quoted ? name : name.toLowerCase();
}
+ public String getUniqueConstraintString(Dialect dialect) {
+ boolean useUniqueConstraint = isUnique() &&
+ dialect.supportsUnique() &&
+ ( !isNullable() || dialect.supportsNotNullUnique() );
+ if ( useUniqueConstraint ) {
+ return " unique";
+ }
+ return "";
+ }
+
+ public String getCheckConstraintString(Dialect dialect) {
+ StringBuffer alter = new StringBuffer();
+ if ( hasCheckConstraint() && dialect.supportsColumnCheck() ) {
+ alter.append( " check(" )
+ .append( getCheckConstraint() )
+ .append( ")" );
+ return alter.toString();
+ }
+ return "";
+ }
+
+ public String getColumnCommentString(Dialect dialect) {
+ String columnComment = getComment();
+ if (columnComment!=null) {
+ return " " + dialect.getColumnComment(columnComment);
+ }
+ return "";
+ }
+
/**
* Shallow copy, the value is not copied
*/
protected Object clone() {
Column copy = new Column();
copy.setLength( length );
copy.setScale( scale );
copy.setValue( value );
copy.setTypeIndex( typeIndex );
copy.setName( getQuotedName() );
--- ./Hibernate3/src/org/hibernate/mapping/Table.java 2005-11-27
23:42:03.000000000 +0100
+++ ../Hibernate3/src/org/hibernate/mapping/Table.java 2005-12-11
21:04:53.000000000 +0100
@@ -2,20 +2,22 @@
package org.hibernate.mapping;
import java.io.Serializable;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import org.apache.commons.collections.SequencedHashMap;
import org.hibernate.HibernateException;
import org.hibernate.MappingException;
import org.hibernate.dialect.Dialect;
import org.hibernate.engine.Mapping;
import org.hibernate.tool.hbm2ddl.ColumnMetadata;
import org.hibernate.tool.hbm2ddl.TableMetadata;
import org.hibernate.util.CollectionHelper;
/**
* A relational table
@@ -255,37 +257,39 @@
if ( column.isNullable() ) {
alter.append(
dialect.getNullColumnString() );
}
else {
alter.append( " not null" );
}
}
- boolean useUniqueConstraint =
column.isUnique() &&
- dialect.supportsUnique() &&
- ( !column.isNullable()
|| dialect.supportsNotNullUnique() );
- if ( useUniqueConstraint ) {
- alter.append( " unique" );
- }
+ alter.append(
column.getUniqueConstraintString(dialect) );
- if ( column.hasCheckConstraint() &&
dialect.supportsColumnCheck() ) {
- alter.append( " check(" )
- .append(
column.getCheckConstraint() )
- .append( ")" );
- }
+ alter.append(
column.getCheckConstraintString(dialect) );
- String columnComment = column.getComment();
- if (columnComment!=null) alter.append(
dialect.getColumnComment(columnComment) );
+ alter.append(
column.getColumnCommentString(dialect) );
results.add( alter.toString() );
+
+
+ } else if (dialect.supportsAlterColumnType()) {
+
+ AlterColumnType alterColumnTypeLength = new
AlterColumnType(column, columnInfo);
+ alterColumnTypeLength.setDefaultCatalog(defaultCatalog);
+ alterColumnTypeLength.setDefaultSchema(defaultSchema);
+ alterColumnTypeLength.setDialect(dialect);
+ alterColumnTypeLength.setMapping(p);
+ alterColumnTypeLength.setTable(this);
+
+
results.add(alterColumnTypeLength.createAlterString().toString());
}
}
return results.iterator();
}
public boolean hasPrimaryKey() {
return getPrimaryKey()!=null;
}
--- ./Hibernate3/src/org/hibernate/mapping/AlterColumnType.java 1970-01-01
01:00:00.000000000 +0100
+++ ../Hibernate3/src/org/hibernate/mapping/AlterColumnType.java
2005-12-11
21:02:43.000000000 +0100
@@ -0,0 +1,100 @@
+package org.hibernate.mapping;
+
+import java.sql.Types;
+
+import org.hibernate.dialect.Dialect;
+import org.hibernate.engine.Mapping;
+import org.hibernate.tool.hbm2ddl.ColumnMetadata;
+
+/**
+ * Generates the sql code to alter a column type.
+ *
+ * @author Frank Sattelberger
+ */
+public class AlterColumnType {
+
+ private Column column;
+ private ColumnMetadata columnInfo;
+ private Dialect dialect;
+ private Mapping mapping;
+ private String defaultCatalog;
+ private String defaultSchema;
+ private Table table;
+
+ public AlterColumnType(Column column, ColumnMetadata columnInfo) {
+ this.column = column;
+ this.columnInfo = columnInfo;
+ }
+
+
+
+ public StringBuffer createAlterString() {
+
+ StringBuffer alter = new StringBuffer();
+
+ if (checkType()) {
+
+ // Example code: ALTER TABLE products ALTER COLUMN price
TYPE numeric(10,2);
+
+ StringBuffer root = new StringBuffer( "alter table " )
+ .append( table.getQualifiedName( dialect, defaultCatalog,
defaultSchema ) )
+ .append( ' ' )
+ .append( dialect.getAlterColumnString() );
+
+ alter.append( root.toString() )
+ .append( ' ' )
+ .append( column.getQuotedName( dialect ) )
+ .append( ' ' )
+ .append( dialect.getAlterColumnTypeString() )
+ .append( ' ' )
+ .append( column.getSqlType( dialect, mapping ) );
+ }
+
+ return alter;
+ }
+
+
+
+ private boolean checkType() {
+ if (column != null) {
+ switch (column.getSqlTypeCode(mapping)) {
+ case Types.VARCHAR:
+ if (column.getLength() != columnInfo.getColumnSize()) {
+ return true;
+ }
+ break;
+
+ case Types.NUMERIC:
+ if (column.getPrecision() != columnInfo.getColumnSize() ||
+ column.getScale() != columnInfo.getDecimalDigits()) {
+ return true;
+ }
+ break;
+
+
+ default:
+ return false;
+ }
+ }
+ return false;
+ }
+
+ public void setDefaultCatalog(String defaultCatalog) {
+ this.defaultCatalog = defaultCatalog;
+ }
+
+ public void setDefaultSchema(String defaultSchema) {
+ this.defaultSchema = defaultSchema;
+ }
+
+ public void setDialect(Dialect dialect) {
+ this.dialect = dialect;
+ }
+
+ public void setMapping(Mapping p) {
+ this.mapping = p;
+ }
+
+ public void setTable(Table table) {
+ this.table = table;
+ }
+
+}
--- ./Hibernate3/src/org/hibernate/dialect/Dialect.java 2005-11-27
23:42:03.000000000 +0100
+++ ../Hibernate3/src/org/hibernate/dialect/Dialect.java 2005-12-11
20:41:11.000000000 +0100
@@ -941,11 +941,28 @@
/**
* The name of the database-specific SQL function for retrieving the
* current timestamp.
*
* @return The function name.
*/
public String getCurrentTimestampSQLFunctionName() {
// the standard SQL function name is current_timestamp...
return "current_timestamp";
}
+
+ /**
+ * Does this dialect support the alteration of column types?
+ *
+ * @return <code>true</code> if this is supported, <code>false</code>
otherwise.
+ */
+ public boolean supportsAlterColumnType() {
+ return false;
+ }
+
+ public String getAlterColumnString() {
+ throw new UnsupportedOperationException( "No alter column syntax
supported by Dialect" );
+ }
+
+ public String getAlterColumnTypeString() {
+ throw new UnsupportedOperationException( "No alter column type syntax
supported by Dialect" );
+ }
}
--- ./Hibernate3/src/org/hibernate/dialect/PostgreSQLDialect.java
2005-12-06 04:45:06.000000000 +0100
+++ ../Hibernate3/src/org/hibernate/dialect/PostgreSQLDialect.java
2005-12-11 20:41:30.000000000 +0100
@@ -110,20 +110,28 @@
registerFunction( "str", new
SQLFunctionTemplate(Hibernate.STRING, "cast(?1 as varchar)") );
getDefaultProperties().setProperty(Environment.STATEMENT_BATCH_SIZE,
DEFAULT_BATCH_SIZE);
}
public String getAddColumnString() {
return "add column";
}
+ public String getAlterColumnString() {
+ return "alter column";
+ }
+
+ public String getAlterColumnTypeString() {
+ return "type";
+ }
+
public String getSequenceNextValString(String sequenceName) {
return "select " + getSelectSequenceNextValString( sequenceName
);
}
public String getSelectSequenceNextValString(String sequenceName) {
return "nextval ('" + sequenceName + "')";
}
public String getCreateSequenceString(String sequenceName) {
return "create sequence " + sequenceName; //starts with 1,
implicitly
@@ -258,20 +266,24 @@
}
public String toBooleanValueString(boolean bool) {
return bool ? "true" : "false";
}
public ViolatedConstraintNameExtracter
getViolatedConstraintNameExtracter() {
return EXTRACTER;
}
+ public boolean supportsAlterColumnType() {
+ return true;
+ }
+
/**
* Constraint-name extractor for Postgres contraint violation
exceptions.
* Orginally contributed by Denny Bartelt.
*/
private static ViolatedConstraintNameExtracter EXTRACTER = new
TemplatedViolatedConstraintNameExtracter() {
public String extractConstraintName(SQLException sqle) {
try {
int sqlState = Integer.valueOf(
JDBCExceptionHelper.extractSqlState(sqle)).intValue();
switch (sqlState) {
// CHECK VIOLATION
--- ./Hibernate3/src/org/hibernate/mapping/Column.java 2005-11-11
00:58:54.000000000 +0100
+++ ../Hibernate3/src/org/hibernate/mapping/Column.java 2005-12-11
19:32:10.000000000 +0100
@@ -277,20 +277,49 @@
}
public void setDefaultValue(String defaultValue) {
this.defaultValue = defaultValue;
}
public String getCanonicalName() {
return quoted ? name : name.toLowerCase();
}
+ public String getUniqueConstraintString(Dialect dialect) {
+ boolean useUniqueConstraint = isUnique() &&
+ dialect.supportsUnique() &&
+ ( !isNullable() || dialect.supportsNotNullUnique() );
+ if ( useUniqueConstraint ) {
+ return " unique";
+ }
+ return "";
+ }
+
+ public String getCheckConstraintString(Dialect dialect) {
+ StringBuffer alter = new StringBuffer();
+ if ( hasCheckConstraint() && dialect.supportsColumnCheck() ) {
+ alter.append( " check(" )
+ .append( getCheckConstraint() )
+ .append( ")" );
+ return alter.toString();
+ }
+ return "";
+ }
+
+ public String getColumnCommentString(Dialect dialect) {
+ String columnComment = getComment();
+ if (columnComment!=null) {
+ return " " + dialect.getColumnComment(columnComment);
+ }
+ return "";
+ }
+
/**
* Shallow copy, the value is not copied
*/
protected Object clone() {
Column copy = new Column();
copy.setLength( length );
copy.setScale( scale );
copy.setValue( value );
copy.setTypeIndex( typeIndex );
copy.setName( getQuotedName() );
--- ./Hibernate3/src/org/hibernate/mapping/Table.java 2005-11-27
23:42:03.000000000 +0100
+++ ../Hibernate3/src/org/hibernate/mapping/Table.java 2005-12-11
21:04:53.000000000 +0100
@@ -2,20 +2,22 @@
package org.hibernate.mapping;
import java.io.Serializable;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import org.apache.commons.collections.SequencedHashMap;
import org.hibernate.HibernateException;
import org.hibernate.MappingException;
import org.hibernate.dialect.Dialect;
import org.hibernate.engine.Mapping;
import org.hibernate.tool.hbm2ddl.ColumnMetadata;
import org.hibernate.tool.hbm2ddl.TableMetadata;
import org.hibernate.util.CollectionHelper;
/**
* A relational table
@@ -255,37 +257,39 @@
if ( column.isNullable() ) {
alter.append(
dialect.getNullColumnString() );
}
else {
alter.append( " not null" );
}
}
- boolean useUniqueConstraint = column.isUnique()
&&
- dialect.supportsUnique() &&
- ( !column.isNullable() ||
dialect.supportsNotNullUnique() );
- if ( useUniqueConstraint ) {
- alter.append( " unique" );
- }
+ alter.append(
column.getUniqueConstraintString(dialect) );
- if ( column.hasCheckConstraint() &&
dialect.supportsColumnCheck() ) {
- alter.append( " check(" )
- .append(
column.getCheckConstraint() )
- .append( ")" );
- }
+ alter.append(
column.getCheckConstraintString(dialect) );
- String columnComment = column.getComment();
- if (columnComment!=null) alter.append(
dialect.getColumnComment(columnComment) );
+ alter.append(
column.getColumnCommentString(dialect) );
results.add( alter.toString() );
+
+
+ } else if (dialect.supportsAlterColumnType()) {
+
+ AlterColumnType alterColumnTypeLength = new
AlterColumnType(column, columnInfo);
+ alterColumnTypeLength.setDefaultCatalog(defaultCatalog);
+ alterColumnTypeLength.setDefaultSchema(defaultSchema);
+ alterColumnTypeLength.setDialect(dialect);
+ alterColumnTypeLength.setMapping(p);
+ alterColumnTypeLength.setTable(this);
+
+
results.add(alterColumnTypeLength.createAlterString().toString());
}
}
return results.iterator();
}
public boolean hasPrimaryKey() {
return getPrimaryKey()!=null;
}
--- ./Hibernate3/src/org/hibernate/mapping/AlterColumnType.java 1970-01-01
01:00:00.000000000 +0100
+++ ../Hibernate3/src/org/hibernate/mapping/AlterColumnType.java
2005-12-11 21:02:43.000000000 +0100
@@ -0,0 +1,100 @@
+package org.hibernate.mapping;
+
+import java.sql.Types;
+
+import org.hibernate.dialect.Dialect;
+import org.hibernate.engine.Mapping;
+import org.hibernate.tool.hbm2ddl.ColumnMetadata;
+
+/**
+ * Generates the sql code to alter a column type.
+ *
+ * @author Frank Sattelberger
+ */
+public class AlterColumnType {
+
+ private Column column;
+ private ColumnMetadata columnInfo;
+ private Dialect dialect;
+ private Mapping mapping;
+ private String defaultCatalog;
+ private String defaultSchema;
+ private Table table;
+
+ public AlterColumnType(Column column, ColumnMetadata columnInfo) {
+ this.column = column;
+ this.columnInfo = columnInfo;
+ }
+
+
+
+ public StringBuffer createAlterString() {
+
+ StringBuffer alter = new StringBuffer();
+
+ if (checkType()) {
+
+ // Example code: ALTER TABLE products ALTER COLUMN price TYPE
numeric(10,2);
+
+ StringBuffer root = new StringBuffer( "alter table " )
+ .append( table.getQualifiedName( dialect, defaultCatalog,
defaultSchema ) )
+ .append( ' ' )
+ .append( dialect.getAlterColumnString() );
+
+ alter.append( root.toString() )
+ .append( ' ' )
+ .append( column.getQuotedName( dialect ) )
+ .append( ' ' )
+ .append( dialect.getAlterColumnTypeString() )
+ .append( ' ' )
+ .append( column.getSqlType( dialect, mapping ) );
+ }
+
+ return alter;
+ }
+
+
+
+ private boolean checkType() {
+ if (column != null) {
+ switch (column.getSqlTypeCode(mapping)) {
+ case Types.VARCHAR:
+ if (column.getLength() != columnInfo.getColumnSize()) {
+ return true;
+ }
+ break;
+
+ case Types.NUMERIC:
+ if (column.getPrecision() != columnInfo.getColumnSize() ||
+ column.getScale() != columnInfo.getDecimalDigits()) {
+ return true;
+ }
+ break;
+
+
+ default:
+ return false;
+ }
+ }
+ return false;
+ }
+
+ public void setDefaultCatalog(String defaultCatalog) {
+ this.defaultCatalog = defaultCatalog;
+ }
+
+ public void setDefaultSchema(String defaultSchema) {
+ this.defaultSchema = defaultSchema;
+ }
+
+ public void setDialect(Dialect dialect) {
+ this.dialect = dialect;
+ }
+
+ public void setMapping(Mapping p) {
+ this.mapping = p;
+ }
+
+ public void setTable(Table table) {
+ this.table = table;
+ }
+
+}
