MSSQL 2k5 - convert old style on Delete triggers to onDelete at constraint level

General Discussion
1

As mentioned in my previous post the legacy way of managing onDelete in SQL Server was at the table level of the foreign table.

Fortunately, 2k5 supports the ON DELETE syntax that the rest of the SQL world has also adopted.

Unfortunately, we have a lot of FK constraints whose onDelete triggers are still managed at the table level of the foreign table. Was wondering if anyone in his community has written a custom liquibase change set that goes through and converts these old style triggers into FK constraints with the onDelete attached to the actual constraint.

Liquibase seems to only use this system for handling onDelete, so by introducing Liquibase we’re going to have a mix of both systems until we figure out how to convert them.

The biggest problem with this is that if we use Liquibase change or manipulate an existing foreign key column or table containing a fk column, we have to consult the referenced table and update any of these old style ON DELETE triggers by hand making the use of Liquibase very cumbersome and not very vendor agnostic.

2

I haven’t done it, but if you can find anyone that even has SQL running against the information_schema you may be able to build up one or more blocks that would convert them.

Nathan

3

Thanks Nathan. We’re actually working on rolling a custom SQL change that will query the database schema for existing triggers and convert them into ON DELETE events at the FK constraint level. I’ll post it when we get it working.

4

Learned something interesting today which will probably prevent us from using cascading actions at the FK constraint level with SQL Server.

SQL Server 2005 will not allow cascading actions with self-referencing FKs:

    ALTER TABLE folder ADD CONSTRAINT "FK_PARENTFOLDER" FOREIGN KEY (parentFolder) REFERENCES folder(id) ON DELETE SET NULL
The above will fail with an error about how introducing the FK "may cause cycles or multiple cascade paths"

SQL Server 2005 will not allow multiple cascade paths to the same table:

    ALTER TABLE document ADD CONSTRAINT "FK_AUTHOR" FOREIGN KEY (created) REFERENCES author(id) ON DELETE SET NULL ALTER TABLE document ADD CONSTRAINT "FK_AUTHOR" FOREIGN KEY (lastModified) REFERENCES author(id) ON DELETE SET NULL
The second query will fail above because there is already on cascading action from author -> document when a record is deleted so you can't add a second FK that also has a cascading action from author -> document.
5

Decided to try to handle cascading referential integrity using Triggers as we did before as they allow for “ON DELETE SET NULL” behavior self-referencing FK columns and when multiple columns in a single table reference another table. Now, to figure out how to override these Change objects in Liquibase…

6

Liquibase 2.0 has much better support for overriding change objects.  See http://liquibase.jira.com/wiki/display/CONTRIB/Change for what there is of docs so far

Nathan

7

One thing that’s not entirely clear to me is how you might go about overriding the default AddForeignKeyConstraintChange class as ideally we’d like to modify it and never have to know that we’re using a custom implementation of this class when writing the change logs.

8

The idea behind the extensibility in Liquibase 2.0 is to allow just what you are looking for: to provide custom implementations of change classes without the changelog knowing about it at all. 

A couple other doc links: http://liquibase.jira.com/wiki/display/CONTRIB/Overview and http://liquibase.jira.com/wiki/display/CONTRIB/Contributing 

The general idea is that you can create custom classes that either extend from the standard class, or a completely different class that implements the Change interface.  Liquibase looks for classes in liquibase.ext subpackages for classes that implement the given interfaces.  If you want to use a different package, you can add a “LiquiBase-Package:” line in your META-INF folder

When liquibase uses your extension classes depends on what they are.  For the Change classes, if the parser looks for the Change implementation(s) that return the given tag name as the getChangeMetaData().getName() method (normally set by passing the tag name into the AbstractChange superclass constructor).  If there is more than one Change classes that return the given tag name, it will pick the one that has the highest getChangeMetaData().getPriority() value (normally set by passing priority to the AbstractChange superclass constructor).

A simple example would be:

    public class SampleChange extends AbstractChange {     public SampleChange() {         super("addForeignKeyConstraint", "Custom FK Constraint Change", 15);     }

        public SqlStatement[] generateStatements(Database database) {
            return new SqlStatement[]{
                new CreateTableStatement(null, “samplechange”).addColumn(“id”, “int”).addColumn(“name”, “varchar(5)”)
            };
        }
    }

which will make it so all tags will generate a CreateTableStatement instead. 

I really need to get to the docs on this… :slight_smile:

What can make it more complicated, is that there are a couple integration points that may make more sense to you.  If you look at the core AddForeignKeyConstraintChange class, it return a AddForeignKeyConstraintStatement class.  The *Statement classes are a package containing all the information needed to generate database-specific SQL, and different Change classes can return the same Statement classes.  There is a SqlGenerator extension point where you can override the default SqlGenerator for the AddForeignKeyConstraintStatement instead of overriding the Change class.  See http://liquibase.jira.com/wiki/display/CONTRIB/SqlGenerator.  The pattern is the same as overriding Change classes, but is for the SqlGenerator interface.

Creating custom Change classes is good if you want to change the type of statements returned, but not the SQL generated by those statements.  In your case, you want to modify the generated SQL, so you will want to extend the SqlGenerator classes.

Does that help?

Nathan