Some minor patches for Liquibase
|
View:
New views
2 Messages
—
Rating Filter:
Alert me
|
 |
Some minor patches for Liquibase
by Angus Gratton-2
::
Rate this Message:
Hi guys,
Still loving Liquibase, has made our database Schema management much, much easier. I have some patches that I've been meaning to send in for a while now. These are all for issues encountered using Liquibase with MSSQL 2005. I'll describe them in order, from some simple bugfixes that you probably want to include down to some more esoteric and SQL Server-specific changes that you may or may not want to put in (the last one in particular has a hefty bug, so you probably don't want to include it as-is, but I thought people might be interested to see it.) dropColumnWrongSchemaName.diff The diff tool generates a dropColumn where the schemaName attribute is equal to the table name, not the schema name. uniqueIndexHashCode.diff If two Liquibase index objects A,B have A.unique == false, B.unique == null then ( A.equals(B) == false ) but ( A.hashcode() == B.hashcode() ). mssqlEscapeColumnNames.diff MS SQL should escape column names as [colname], in case someone used a SQL reserved word for their column name. Weirder changes: mssql2005defaults.diff Dropping default values on SQL 2005 didn't work for me, so I added an explicit "version 2005 only" drop that selects the correct name from the sys.objects view. The only real problem with this approach, apart from looking clumsy, is that if the default value doesn't actually exist you get an obscure SQL error that doesn't quite explain the problem. There's probably a way to rewrite the query to give a better error. differentDefaultValues.diff This is a more substantial change, because I noticed Liquibase didn't drop default values when changing or dropping columns. At least on SQL Server 2005, you can't change a column type or drop the column if it still has a default value. This may or may not apply for other DBMSes. The fix here is to generate a DropDefaultValue statement in the diff, and a corresponding AddDefaultValue if you are only changing the column. There is a bug in this code, though, because it's checking for a default value by saying "getDefaultValue() != null". A "not null" column will actually return 0, so it creates a DropDefaultValue statement even though it doesn't need one. I'm not really sure if that's easy to fix or not, with the amount of metadata currently stored by Liquibase. Hope this is of some use. Please let me know if you'd like any more explanation on anything, or if I stuffed up any of the diff generation. - Angus Index: core/src/java/liquibase/database/structure/Column.java =================================================================== --- core/src/java/liquibase/database/structure/Column.java (revision 854) +++ core/src/java/liquibase/database/structure/Column.java (working copy) @@ -295,8 +295,19 @@ } public boolean isDifferent(Column otherColumn) { - return isDataTypeDifferent(otherColumn) || isNullabilityDifferent(otherColumn); + return isDataTypeDifferent(otherColumn) || isNullabilityDifferent(otherColumn) || isDefaultValueDifferent(otherColumn); } + + public boolean isDefaultValueDifferent(Column otherColumn) + { + Object odef = otherColumn.getDefaultValue(); + Object tdef = this.getDefaultValue(); + if(odef == null && tdef == null) + return false; + if(odef == null || tdef == null) + return true; + return !odef.equals(tdef) || isDataTypeDifferent(otherColumn); + } public boolean isPrimaryKey() { Index: core/src/java/liquibase/diff/DiffResult.java =================================================================== --- core/src/java/liquibase/diff/DiffResult.java (revision 854) +++ core/src/java/liquibase/diff/DiffResult.java (working copy) @@ -24,6 +24,7 @@ import liquibase.csv.CSVWriter; import liquibase.database.Database; +import liquibase.database.MSSQLDatabase; import liquibase.database.structure.*; import liquibase.exception.JDBCException; import liquibase.log.LogFactory; @@ -34,6 +35,7 @@ import liquibase.xml.DefaultXmlWriter; import liquibase.xml.XmlWriter; +import org.hibernate.mapping.Constraint; import org.w3c.dom.Document; import org.w3c.dom.Element; @@ -326,6 +328,9 @@ out.println(" now not null"); } } + if(baseColumn.isDefaultValueDifferent(column)) { + out.println(" changed default"); + } } } } @@ -628,9 +633,18 @@ continue; } + if(column.getDefaultValue() != null) + { + DropDefaultValueChange dropDef = new DropDefaultValueChange(); + dropDef.setTableName(column.getTable().getName()); + dropDef.setSchemaName(column.getTable().getSchema()); + dropDef.setColumnName(column.getName()); + changes.add(dropDef); + } + DropColumnChange change = new DropColumnChange(); change.setTableName(column.getTable().getName()); change.setSchemaName(column.getTable().getName()); change.setColumnName(column.getName()); changes.add(change); @@ -661,6 +675,28 @@ boolean foundDifference = false; Column baseColumn = baseSnapshot.getColumn(column); + + AddDefaultValueChange newDefaultValue = null; + + if(column.isDefaultValueDifferent(baseColumn)) { + if(baseColumn.getDefaultValue() != null) + { + DropDefaultValueChange dropDef = new DropDefaultValueChange(); + dropDef.setTableName(column.getTable().getName()); + dropDef.setSchemaName(column.getTable().getSchema()); + dropDef.setColumnName(column.getName()); + changes.add(dropDef); + } + if(column.getDefaultValue() != null) + { + newDefaultValue = new AddDefaultValueChange(); + newDefaultValue.setTableName(column.getTable().getName()); + newDefaultValue.setSchemaName(column.getTable().getSchema()); + newDefaultValue.setColumnName(column.getName()); + newDefaultValue.setDefaultValue(column.getDefaultValue().toString()); // Needs work! + } + foundDifference = true; + } if (column.isDataTypeDifferent(baseColumn)) { ColumnConfig columnConfig = new ColumnConfig(); columnConfig.setName(column.getName()); @@ -694,7 +730,9 @@ changes.add(change); foundDifference = true; } - + + if(newDefaultValue != null) + changes.add(newDefaultValue); // Re-add default after column has been modified } if (!foundDifference) { throw new RuntimeException("Unknown difference"); Index: core/src/java/liquibase/diff/DiffResult.java =================================================================== --- core/src/java/liquibase/diff/DiffResult.java (revision 854) +++ core/src/java/liquibase/diff/DiffResult.java (working copy) @@ -628,9 +633,9 @@ continue; } DropColumnChange change = new DropColumnChange(); change.setTableName(column.getTable().getName()); - change.setSchemaName(column.getTable().getName()); + change.setSchemaName(column.getTable().getSchema()); change.setColumnName(column.getName()); changes.add(change); Index: core/src/java/liquibase/database/MSSQLDatabase.java =================================================================== --- core/src/java/liquibase/database/MSSQLDatabase.java (revision 854) +++ core/src/java/liquibase/database/MSSQLDatabase.java (working copy) @@ -121,7 +121,6 @@ public String getAutoIncrementClause() { return "IDENTITY"; } - protected String getDefaultDatabaseSchemaName() throws JDBCException { return null; } @@ -243,6 +242,9 @@ return "[" + constraintName + "]"; } + public String escapeColumnName(String schemaName, String tableName, String columnName) { + return "[" +columnName+ "]"; + } public String convertRequestedSchemaToCatalog(String requestedSchema) throws JDBCException { return getDefaultCatalogName(); Index: core/src/java/liquibase/database/structure/Index.java =================================================================== --- core/src/java/liquibase/database/structure/Index.java (revision 854) +++ core/src/java/liquibase/database/structure/Index.java (working copy) @@ -83,7 +83,7 @@ int result; result = table.getName().toUpperCase().hashCode(); result = 31 * result + columns.hashCode(); - result = 31 * result + (unique == null || unique ? 1 : 0); + result = 31 * result + (unique == null ? 2 : unique ? 1 : 0); return result; } Index: core/src/java/liquibase/database/sql/DropDefaultValueStatement.java =================================================================== --- core/src/java/liquibase/database/sql/DropDefaultValueStatement.java (revision 854) +++ core/src/java/liquibase/database/sql/DropDefaultValueStatement.java (working copy) @@ -1,6 +1,9 @@ package liquibase.database.sql; +import java.sql.ResultSet; +import java.sql.Statement; import liquibase.database.*; +import liquibase.exception.JDBCException; import liquibase.exception.StatementNotSupportedOnDatabaseException; public class DropDefaultValueStatement implements SqlStatement { @@ -32,9 +35,26 @@ throw new StatementNotSupportedOnDatabaseException(this, database); } - if (database instanceof MSSQLDatabase) { -//smarter drop return new SqlStatement[]{new RawSqlStatement("ALTER TABLE " + SqlUtil.escapeTableName(getTableName(), database) + " DROP CONSTRAINT select d.name from syscolumns c,sysobjects d, sysobjects t where c.id=t.id AND d.parent_obj=t.id AND d.type='D' AND t.type='U' AND c.name='"+getColumnName()+"' AND t.name='"+getTableName()+"'"),}; - return "ALTER TABLE " + database.escapeTableName(getSchemaName(), getTableName()) + " DROP CONSTRAINT " + ((MSSQLDatabase) database).generateDefaultConstraintName(getTableName(), getColumnName()); + if (database instanceof MSSQLDatabase) { + try { + if(database.getDatabaseProductVersion().startsWith("9")) // SQL Server 2005 + { + // SQL Server 2005 does not often work with the simpler query shown below + String query = "DECLARE @default sysname\n"; + query += "SELECT @default = object_name(default_object_id) FROM sys.columns WHERE object_id=object_id('" + getSchemaName() + "." + getTableName() + "') AND name='" + columnName + "'\n"; + query += "EXEC ('ALTER TABLE " + database.escapeTableName(getSchemaName(), getTableName()) + " DROP CONSTRAINT ' + @default)"; + //System.out.println("DROP QUERY : " + query); + return query; + } + else + { + return "ALTER TABLE " + database.escapeTableName(getSchemaName(), getTableName()) + " DROP CONSTRAINT select d.name from syscolumns c,sysobjects d, sysobjects t where c.id=t.id AND d.parent_obj=t.id AND d.type='D' AND t.type='U' AND c.name='"+getColumnName()+"' AND t.name='"+getTableName()+"'"; + } + } + catch(JDBCException e) + { + return e.getMessage(); + } } else if (database instanceof MySQLDatabase) { return "ALTER TABLE " + database.escapeTableName(getSchemaName(), getTableName()) + " ALTER " + database.escapeColumnName(getSchemaName(), getTableName(), getColumnName()) + " DROP DEFAULT"; } else if (database instanceof OracleDatabase || database instanceof SybaseASADatabase) { ------------------------------------------------------------------------------ This SF.net email is sponsored by: High Quality Requirements in a Collaborative Environment. Download a free trial of Rational Requirements Composer Now! http://p.sf.net/sfu/www-ibm-com _______________________________________________ Liquibase-devel mailing list Liquibase-devel@... https://lists.sourceforge.net/lists/listinfo/liquibase-devel |
|
|
Re: Some minor patches for Liquibase
by Voxland, Nathan
::
Rate this Message:
I'm glad you're liking liquibase, and thanks for the patches.
I applied diffColumnWrongSchemaName, uniqueIndexHashCode, mssqlEscapeColumnNames because they were definitely bugs and straight forward. I also applied mssql2005defaults believing you about the problems and that it works. I'll hold off on the differentDefaultValues patch since it doesn't work quite right now. I am starting on a refactoring of the codebase for 1.10 and will see if the problem can be fixed better after the refactor. Nathan -----Original Message----- From: Angus Gratton [mailto:agratton@...] Sent: Wednesday, April 15, 2009 2:34 PM To: liquibase-devel@... Subject: [Liquibase-devel] Some minor patches for Liquibase Hi guys, Still loving Liquibase, has made our database Schema management much, much easier. I have some patches that I've been meaning to send in for a while now. These are all for issues encountered using Liquibase with MSSQL 2005. I'll describe them in order, from some simple bugfixes that you probably want to include down to some more esoteric and SQL Server-specific changes that you may or may not want to put in (the last one in particular has a hefty bug, so you probably don't want to include it as-is, but I thought people might be interested to see it.) dropColumnWrongSchemaName.diff The diff tool generates a dropColumn where the schemaName attribute is equal to the table name, not the schema name. uniqueIndexHashCode.diff If two Liquibase index objects A,B have A.unique == false, B.unique == null then ( A.equals(B) == false ) but ( A.hashcode() == B.hashcode() ). mssqlEscapeColumnNames.diff MS SQL should escape column names as [colname], in case someone used a SQL reserved word for their column name. Weirder changes: mssql2005defaults.diff Dropping default values on SQL 2005 didn't work for me, so I added an explicit "version 2005 only" drop that selects the correct name from the sys.objects view. The only real problem with this approach, apart from looking clumsy, is that if the default value doesn't actually exist you get an obscure SQL error that doesn't quite explain the problem. There's probably a way to rewrite the query to give a better error. differentDefaultValues.diff This is a more substantial change, because I noticed Liquibase didn't drop default values when changing or dropping columns. At least on SQL Server 2005, you can't change a column type or drop the column if it still has a default value. This may or may not apply for other DBMSes. The fix here is to generate a DropDefaultValue statement in the diff, and a corresponding AddDefaultValue if you are only changing the column. There is a bug in this code, though, because it's checking for a default value by saying "getDefaultValue() != null". A "not null" column will actually return 0, so it creates a DropDefaultValue statement even though it doesn't need one. I'm not really sure if that's easy to fix or not, with the amount of metadata currently stored by Liquibase. Hope this is of some use. Please let me know if you'd like any more explanation on anything, or if I stuffed up any of the diff generation. - Angus ------------------------------------------------------------------------------ Stay on top of everything new and different, both inside and around Java (TM) technology - register by April 22, and save $200 on the JavaOne (SM) conference, June 2-5, 2009, San Francisco. 300 plus technical and hands-on sessions. Register today. Use priority code J9JMT32. http://p.sf.net/sfu/p _______________________________________________ Liquibase-devel mailing list Liquibase-devel@... https://lists.sourceforge.net/lists/listinfo/liquibase-devel |
| Free embeddable forum powered by Nabble | Forum Help |
