ChangeLog Chain Example required

Liquibase Development
1

Hi Nathan,
I’m exploring you changes, you made for adding fine tuning sql generation through SqlGeneratorChain facility.
And already run into situation, where it might to be applied.

Running java-dbtest against MSSQL 2000 getting the error (see at the end of post)
The server supports only to the following
[ ON DELETE { CASCADE | NO ACTION } ]
[ ON UPDATE { CASCADE | NO ACTION } ]

By RESTRICT I think the clauses ON DELETE/ON UPDATE must be omitted.

Give some ideas, how sql generation may be customized to this case

Thanks.

liquibase.exception.MigrationFailedException: Migration failed for change set liquibase-test3201895695241807216.xml::1244876788296-49::otaranenko (generated):
    Reason: liquibase.exception.JDBCException: Error executing SQL ALTER TABLE [liquibase].[address] ADD CONSTRAINT [FK_ADDRESS_STATE] FOREIGN KEY ([state]) REFERENCES [liquibase].state ON UPDATE RESTRICT ON DELETE RESTRICT:
          Caused By: Error executing SQL ALTER TABLE [liquibase].[address] ADD CONSTRAINT [FK_ADDRESS_STATE] FOREIGN KEY ([state]) REFERENCES [liquibase].state ON UPDATE RESTRICT ON DELETE RESTRICT:
          Caused By: Incorrect syntax near the keyword ‘RESTRICT’.
at liquibase.changelog.ChangeSet.execute(ChangeSet.java:234)
at liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:26)
at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:39)
at liquibase.Liquibase.update(Liquibase.java:113)
at liquibase.dbtest.AbstractSimpleChangeLogRunnerTest.testRerunDiffChangeLog(AbstractSimpleChangeLogRunnerTest.java:298)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at junit.framework.TestCase.runTest(TestCase.java:164)
at junit.framework.TestCase.runBare(TestCase.java:130)
at junit.framework.TestResult$1.protect(TestResult.java:110)
at junit.framework.TestResult.runProtected(TestResult.java:128)
at junit.framework.TestResult.run(TestResult.java:113)
at junit.framework.TestCase.run(TestCase.java:120)
at junit.framework.TestSuite.runTest(TestSuite.java:228)
at junit.framework.TestSuite.run(TestSuite.java:223)
at org.junit.internal.runners.OldTestClassRunner.run(OldTestClassRunner.java:35)
at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:46)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)
Caused by: liquibase.exception.JDBCException: Error executing SQL ALTER TABLE [liquibase].[address] ADD CONSTRAINT [FK_ADDRESS_STATE] FOREIGN KEY ([state]) REFERENCES [liquibase].state ON UPDATE RESTRICT ON DELETE RESTRICT
at liquibase.executor.DefaultExecutor.execute(DefaultExecutor.java:59)
at liquibase.executor.DefaultExecutor.execute(DefaultExecutor.java:89)
at liquibase.database.AbstractDatabase.execute(AbstractDatabase.java:1443)
at liquibase.database.AbstractDatabase.executeStatements(AbstractDatabase.java:1430)
at liquibase.changelog.ChangeSet.execute(ChangeSet.java:210)
… 23 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword ‘RESTRICT’.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source)
at com.microsoft.sqlserver.jdbc.IOBuffer.processPackets(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.sendExecute(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecute(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.execute(Unknown Source)
at liquibase.executor.DefaultExecutor$1ExecuteStatementCallback.doInStatement(DefaultExecutor.java:80)
at liquibase.executor.DefaultExecutor.execute(DefaultExecutor.java:52)
… 27 more

2

What you would do is something like this:

        public Sql[] generateSql(AddForeignKeyConstraintStatement statement, Database database, SqlGeneratorChain sqlGeneratorChain) {         Sql[] sql = sqlGeneratorChain.generateSql(statement, database);         sql[0] = new UnparsedSql(sql[0].toSql().replaceAll("ON UPDATE RESTRICT ","")); }

Although since this is with a build-in SqlStatement, I think it would be best to modify the AddForeignKeyConstriantSqlGenerator to be smarter about what databases it adds the “on update restrict” clause for in the code directly with a “if (database instanceof MSSQLDatabase && sqlStatement.getOnDelete().equals(“RESTRICT”))” statement. 

I think that is a much more accurate implementation than using a replaceAll on a string.  It would work and if you were creating a custom generator that did something different to the normal sql that would be good.  But since you are modifying the liquibase source, I think there is a better way. 

If you are creating a database-specific SqlGenerator for the standard SqlStatements and you are adding to the returned Sql, feel free to use the sqlGeneratorChain to get the “standard” Sql and then add additional Sql instances before or after in the array you return.

Does that make sense?

Nathan

Nathan

3

I am also working on examples, hopefully done by the end of next week.

Nathan 

4

Of course, first my idea was to split implementation as it for i.e. AddAutoIncrementGenerator done.
Next I thought, may be explore new more flexible stuff… Do you mean the SqlGeneratorChain facility intended to be used by extension only?

5

I don’t think it should be for extension only, it should be used for built-in SqlStatement’s as well when it works.  For example, statements for which DB2 requires a “reorg table” command can add that to the Sql generated by the chain.

The trouble is that the objects returned from the chain are UnparsedSql objects which give little hooks for modification.  Eventually I would like to see something smarter being returned, but I am currently not smart enough to know a good way that isn’t a very complex abstract syntax tree.  I think they will work fine for end-user extensions where they are controlling the usage, but we want to be sure that the generated SQL is correct for whatever weird case is passed us, and I am afraid that modifying the Sql as string is not as safe as putting the logic into the sql generation itself.

Nathan