Patch: Extra Column Options for MODIFY statements
|
View:
New views
2 Messages
—
Rating Filter:
Alert me
|
 |
Patch: Extra Column Options for MODIFY statements
by Ben Wolfe-2
::
Rate this Message:
MySQL (among others I'm sure) treat "ALTER TABLE MODIFY columnname" very similar to a create statement's ability to add NOT NULL, DEFAULT, AUTO INCREMENT, etc. I added support for mysql for these other attributes. See attached patch. I'm sure there are other databases that do similar things, but I don't have a good reference for comparing all the supported databases. Ben Index: src/java-test/liquibase/change/ModifyColumnChangeTest.java =================================================================== --- src/java-test/liquibase/change/ModifyColumnChangeTest.java (revision 842) +++ src/java-test/liquibase/change/ModifyColumnChangeTest.java (working copy) @@ -1,15 +1,19 @@ package liquibase.change; +import static org.junit.Assert.assertEquals; + +import javax.xml.parsers.DocumentBuilderFactory; + +import liquibase.database.Database; +import liquibase.database.MySQLDatabase; import liquibase.database.OracleDatabase; -import static org.junit.Assert.*; + import org.junit.Before; import org.junit.Test; import org.w3c.dom.Document; import org.w3c.dom.Element; import org.w3c.dom.NodeList; -import javax.xml.parsers.DocumentBuilderFactory; - /** * Tests for {@link ModifyColumnChange} */ @@ -37,7 +41,7 @@ @Test public void generateStatement() throws Exception { OracleDatabase database = new OracleDatabase(); - assertEquals("ALTER TABLE TABLE_NAME MODIFY (NAME integer(3))", change.generateStatements(database)[0].getSqlStatement(database)); + assertEquals("ALTER TABLE TABLE_NAME MODIFY ( NAME integer(3) )", change.generateStatements(database)[0].getSqlStatement(database)); } @Test @@ -59,4 +63,27 @@ assertEquals("NAME", ((Element) columns.item(0)).getAttribute("name")); assertEquals("integer(3)", ((Element) columns.item(0)).getAttribute("type")); } + + @Test + public void generateFullStatementForMysql() throws Exception { + Database database = new MySQLDatabase(); + ModifyColumnChange mysqlchange = new ModifyColumnChange(); + mysqlchange.setTableName("TABLE_NAME"); + + ColumnConfig col1 = new ColumnConfig(); + col1.setName("NAME"); + col1.setType("integer(3)"); + col1.setAutoIncrement(true); + col1.setDefaultValueNumeric(0); + + ConstraintsConfig constraints = new ConstraintsConfig(); + constraints.setPrimaryKey(true); + constraints.setNullable(false); + col1.setConstraints(constraints); + + mysqlchange.addColumn(col1); + assertEquals("ALTER TABLE `TABLE_NAME` MODIFY `NAME` integer(3) NOT NULL DEFAULT 0 AUTO_INCREMENT PRIMARY KEY", mysqlchange.generateStatements(database)[0].getSqlStatement(database)); + } + + } Index: src/java-test/liquibase/util/MD5UtilTest.java =================================================================== --- src/java-test/liquibase/util/MD5UtilTest.java (revision 754) +++ src/java-test/liquibase/util/MD5UtilTest.java (working copy) @@ -4,15 +4,12 @@ import org.junit.Test; -import hidden.org.codehaus.plexus.util.StringInputStream; - public class MD5UtilTest { @Test public void testComputeMD5InputStream() { String s = "abc"; - StringInputStream is = new StringInputStream(s); - String hexString = MD5Util.computeMD5(is); + String hexString = MD5Util.computeMD5(s); assertEquals("90150983cd24fb0d6963f7d28e17f72",hexString); } } Index: src/java/liquibase/change/ModifyColumnChange.java =================================================================== --- src/java/liquibase/change/ModifyColumnChange.java (revision 842) +++ src/java/liquibase/change/ModifyColumnChange.java (working copy) @@ -1,26 +1,31 @@ package liquibase.change; -import liquibase.database.*; +import java.util.ArrayList; +import java.util.HashSet; +import java.util.List; +import java.util.Set; + +import liquibase.database.Database; +import liquibase.database.SQLiteDatabase; import liquibase.database.SQLiteDatabase.AlterTableVisitor; -import liquibase.database.sql.RawSqlStatement; +import liquibase.database.sql.AutoIncrementConstraint; +import liquibase.database.sql.ColumnConstraint; +import liquibase.database.sql.ModifyColumnStatement; +import liquibase.database.sql.NotNullConstraint; +import liquibase.database.sql.PrimaryKeyConstraint; import liquibase.database.sql.SqlStatement; -import liquibase.database.sql.ReorganizeTableStatement; import liquibase.database.structure.Column; import liquibase.database.structure.DatabaseObject; import liquibase.database.structure.Index; import liquibase.database.structure.Table; +import liquibase.exception.InvalidChangeDefinitionException; import liquibase.exception.JDBCException; import liquibase.exception.UnsupportedChangeException; -import liquibase.exception.InvalidChangeDefinitionException; import liquibase.util.StringUtils; + import org.w3c.dom.Document; import org.w3c.dom.Element; -import java.util.ArrayList; -import java.util.HashSet; -import java.util.List; -import java.util.Set; - /** * Modifies the data type of an existing column. */ @@ -84,29 +89,31 @@ List<SqlStatement> sql = new ArrayList<SqlStatement>(); - for (ColumnConfig aColumn : columns) { + + for (ColumnConfig aColumn : columns) { + + Set<ColumnConstraint> constraints = new HashSet<ColumnConstraint>(); + if (aColumn.getConstraints() != null) { + if (aColumn.getConstraints().isNullable() != null && !aColumn.getConstraints().isNullable()) { + constraints.add(new NotNullConstraint()); + } + if (aColumn.getConstraints().isPrimaryKey() != null && aColumn.getConstraints().isPrimaryKey()) { + constraints.add(new PrimaryKeyConstraint(aColumn.getConstraints().getPrimaryKeyName())); + } + } + if (aColumn.isAutoIncrement() != null && aColumn.isAutoIncrement()) { + constraints.add(new AutoIncrementConstraint(aColumn.getName())); + } + + ModifyColumnStatement modColumnStatement = new ModifyColumnStatement(schemaName, + getTableName(), + aColumn.getName(), + aColumn.getType(), + aColumn.getDefaultValueObject(), + constraints.toArray(new ColumnConstraint[constraints.size()])); - String schemaName = getSchemaName() == null?database.getDefaultSchemaName():getSchemaName(); - if(database instanceof SybaseASADatabase || database instanceof SybaseDatabase) { - sql.add(new RawSqlStatement("ALTER TABLE " + database.escapeTableName(schemaName, getTableName()) + " MODIFY " + aColumn.getName() + " " + database.getColumnType(aColumn.getType(), false))); - } else if (database instanceof MSSQLDatabase) { - sql.add(new RawSqlStatement("ALTER TABLE " + database.escapeTableName(schemaName, getTableName()) + " ALTER COLUMN " + aColumn.getName() + " " + database.getColumnType(aColumn.getType(), false))); - } else if (database instanceof MySQLDatabase) { - sql.add(new RawSqlStatement("ALTER TABLE " + database.escapeTableName(schemaName, getTableName()) + " MODIFY COLUMN " + aColumn.getName() + " " + database.getColumnType(aColumn.getType(), false))); - } else if (database instanceof OracleDatabase || database instanceof MaxDBDatabase) { - sql.add(new RawSqlStatement("ALTER TABLE " + database.escapeTableName(schemaName, getTableName()) + " MODIFY (" + aColumn.getName() + " " + database.getColumnType(aColumn.getType(), false) + ")")); - } else if (database instanceof DerbyDatabase) { - sql.add(new RawSqlStatement("ALTER TABLE " + database.escapeTableName(schemaName, getTableName()) + " ALTER COLUMN "+aColumn.getName()+" SET DATA TYPE " + database.getColumnType(aColumn.getType(), false))); - } else if (database instanceof HsqlDatabase) { - sql.add(new RawSqlStatement("ALTER TABLE " + database.escapeTableName(schemaName, getTableName()) + " ALTER COLUMN "+aColumn.getName()+" "+database.getColumnType(aColumn.getType(), false))); - } else if (database instanceof CacheDatabase) { - sql.add(new RawSqlStatement("ALTER TABLE " + database.escapeTableName(schemaName, getTableName()) + " ALTER COLUMN " + aColumn.getName() + " " + database.getColumnType(aColumn.getType(), false))); - } else if (database instanceof DB2Database) { - sql.add(new RawSqlStatement("ALTER TABLE " + database.escapeTableName(schemaName, getTableName()) + " ALTER COLUMN " + aColumn.getName() + " SET DATA TYPE " + database.getColumnType(aColumn.getType(), false))); - sql.add(new ReorganizeTableStatement(schemaName, getTableName())); - } else { - sql.add(new RawSqlStatement("ALTER TABLE " + database.escapeTableName(schemaName, getTableName()) + " ALTER COLUMN " + aColumn.getName() + " TYPE " + database.getColumnType(aColumn.getType(), false))); - } + sql.add(modColumnStatement); + } return sql.toArray(new SqlStatement[sql.size()]); Index: src/java/liquibase/database/sql/AddColumnStatement.java =================================================================== --- src/java/liquibase/database/sql/AddColumnStatement.java (revision 842) +++ src/java/liquibase/database/sql/AddColumnStatement.java (working copy) @@ -7,6 +7,9 @@ import java.util.HashSet; import java.util.Set; +/** + * Represents statements like "ALTER TABLE tablename MODIFY COLUMN VOIDED tinyint(1) NOT NULL" + */ public class AddColumnStatement implements SqlStatement { private String schemaName; Index: src/java/liquibase/database/sql/ModifyColumnStatement.java =================================================================== --- src/java/liquibase/database/sql/ModifyColumnStatement.java (revision 0) +++ src/java/liquibase/database/sql/ModifyColumnStatement.java (revision 0) @@ -0,0 +1,228 @@ +package liquibase.database.sql; + +import liquibase.database.*; +import liquibase.exception.StatementNotSupportedOnDatabaseException; + +import java.util.Arrays; +import java.util.HashSet; +import java.util.Set; + +/** + * Represents statements like "ALTER TABLE tablename MODIFY COLUMN VOIDED tinyint(1) NOT NULL" + */ +public class ModifyColumnStatement implements SqlStatement { + + private String schemaName; + private String tableName; + private String columnName; + private String columnType; + private Object defaultValue; + private Set<ColumnConstraint> constraints = new HashSet<ColumnConstraint>(); + + public ModifyColumnStatement(String schemaName, String tableName, String columnName, String columnType, Object defaultValue, ColumnConstraint... constraints) { + this.schemaName = schemaName; + this.tableName = tableName; + this.columnName = columnName; + this.columnType = columnType; + this.defaultValue = defaultValue; + if (constraints != null) { + this.constraints.addAll(Arrays.asList(constraints)); + } + } + + public String getSchemaName() { + return schemaName; + } + + public String getTableName() { + return tableName; + } + + public String getColumnName() { + return columnName; + } + + public String getColumnType() { + return columnType; + } + + public Set<ColumnConstraint> getConstraints() { + return constraints; + } + + public String getSqlStatement(Database database) throws StatementNotSupportedOnDatabaseException { + if (isPrimaryKey() && (database instanceof CacheDatabase + || database instanceof H2Database + || database instanceof DB2Database + || database instanceof DerbyDatabase + || database instanceof SQLiteDatabase)) { + throw new StatementNotSupportedOnDatabaseException("Adding primary key columns is not supported", this, database); + } + + String alterTable = "ALTER TABLE " + database.escapeTableName(getSchemaName(), getTableName()); + + // add "MODIFY" + alterTable += " " + getModifyString(database) + " "; + + // add column name + alterTable += database.escapeColumnName(getSchemaName(), getTableName(), getColumnName()); + + alterTable += getPreDataTypeString(database); // adds a space if nothing else + + // add column type + alterTable += database.getColumnType(getColumnType(), false); + + if (supportsExtraMetaData(database)) { + if (!isNullable()) { + alterTable += " NOT NULL"; + } else { + if (database instanceof SybaseDatabase || database instanceof SybaseASADatabase) { + alterTable += " NULL"; + } + } + + alterTable += getDefaultClause(database); + + if (isAutoIncrement()) { + alterTable += " " + database.getAutoIncrementClause(); + } + + if (isPrimaryKey()) { + alterTable += " PRIMARY KEY"; + } + } + + alterTable += getPostDataTypeString(database); + + return alterTable; + } + + /** + * Whether the ALTER command can take things like "DEFAULT VALUE" or "PRIMARY KEY" as well as type changes + * + * @param database + * @return true/false whether extra information can be included + */ + private boolean supportsExtraMetaData(Database database) { + if (database instanceof MSSQLDatabase + || database instanceof MySQLDatabase) { + return true; + } + + return false; + } + + /** + * @return either "MODIFY" or "ALTER COLUMN" depending on the current db + */ + private String getModifyString(Database database) { + if (database instanceof HsqlDatabase + || database instanceof DerbyDatabase + || database instanceof DB2Database + || database instanceof MSSQLDatabase + || database instanceof CacheDatabase) { + return "ALTER COLUMN"; + } + else if (database instanceof SybaseASADatabase + || database instanceof SybaseDatabase + || database instanceof MySQLDatabase) { + return "MODIFY"; + } + else if (database instanceof OracleDatabase + || database instanceof MaxDBDatabase) { + return "MODIFY ("; + } + else { + return "ALTER COLUMN"; + } + } + + /** + * @return the string that comes before the column type + * definition (like 'set data type' for derby or an open parentheses for Oracle) + */ + private String getPreDataTypeString(Database database) { + if (database instanceof DerbyDatabase + || database instanceof DB2Database) { + return " SET DATA TYPE "; + } + else if (database instanceof SybaseASADatabase + || database instanceof SybaseDatabase + || database instanceof MSSQLDatabase + || database instanceof MySQLDatabase + || database instanceof HsqlDatabase + || database instanceof CacheDatabase + || database instanceof OracleDatabase + || database instanceof MaxDBDatabase) { + return " "; + } + else { + return " TYPE "; + } + } + + /** + * @return the string that comes after the column type definition (like a close parentheses for Oracle) + */ + private String getPostDataTypeString(Database database) { + if (database instanceof OracleDatabase + || database instanceof MaxDBDatabase ) { + return " )"; + } + else { + return ""; + } + } + + public boolean isAutoIncrement() { + for (ColumnConstraint constraint : getConstraints()) { + if (constraint instanceof AutoIncrementConstraint) { + return true; + } + } + return false; + } + + public boolean isPrimaryKey() { + for (ColumnConstraint constraint : getConstraints()) { + if (constraint instanceof PrimaryKeyConstraint) { + return true; + } + } + return false; + } + + public String getEndDelimiter(Database database) { + return ";"; + } + + public boolean supportsDatabase(Database database) { + return true; + } + + private String getDefaultClause(Database database) { + String clause = ""; + if (getDefaultValue() != null) { + if (database instanceof MySQLDatabase) { + clause += " DEFAULT " + database.convertJavaObjectToString(getDefaultValue()); + } + } + return clause; + } + + public boolean isNullable() { + if (isPrimaryKey()) { + return false; + } + for (ColumnConstraint constraint : getConstraints()) { + if (constraint instanceof NotNullConstraint) { + return false; + } + } + return true; + } + + public Object getDefaultValue() { + return defaultValue; + } +} ------------------------------------------------------------------------------ Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com _______________________________________________ Liquibase-devel mailing list Liquibase-devel@... https://lists.sourceforge.net/lists/listinfo/liquibase-devel |
|
|
Re: Patch: Extra Column Options for MODIFY statements
by Voxland, Nathan
::
Rate this Message:
Thanks! I'll get it applied and see how applicable it is to other databases. Nathan
------------------------------------------------------------------------------ Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com _______________________________________________ Liquibase-devel mailing list Liquibase-devel@... https://lists.sourceforge.net/lists/listinfo/liquibase-devel |
| Free embeddable forum powered by Nabble | Forum Help |
