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

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