[DDLUTILS-222] ON UPDATE, ON DELETE errors for MS Sql Server - does not implement default "RESTRICT"

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.1
• Fix Version/s: 1.1
• Component/s: Core - SqlServer
• Environment: MS SQL Server 2005
• Assignee: Thomas Dudziak
• Reporter: Frank Hampshire
• Created: 04/Aug/08 06:07
• Updated: 06/Nov/08 06:38
• Resolved: 06/Nov/08 06:38

Description

When exporting a database out of SQL Server 2005, a foreign key constrain onUpdate and onDelete values, when not set, are set as "restrict"

Eg:

In the table below for the table fktest ON DELETE has been set to 'cascade', while ON UPDATE has not been defined (defaults to NO ACTION), but the outputted onUpdate is 'restrict'

<table name="fktest">
<column name="ID" primaryKey="true" required="true" type="INTEGER" size="10" autoIncrement="false" />
<column name="fktarget_id" primaryKey="false" required="false" type="INTEGER" size="10" autoIncrement="false" />
<foreign-key foreignTable="fktarget" name="FK_fktest_fktarget" onUpdate="restrict" onDelete="cascade">
<reference local="fktarget_id" foreign="fktarget_id" />
</foreign-key>
</table>

Now, when it comes time to create this table from the XML, back into a MSSQL Server 2005 database, DDLUtils throws the following Exception.

org.apache.ddlutils.DatabaseOperationException: Error while executing SQL ALTER TABLE fktest
ADD CONSTRAINT FK_fktest_fktarget FOREIGN KEY (fktarget_id) REFERENCES fktarget (fktarget_id) ON DELETE CASCADE ON UPDATE RESTRICT
at org.apache.ddlutils.platform.PlatformImplBase.evaluateBatch(PlatformImplBase.java:358)
at org.apache.ddlutils.platform.PlatformImplBase.createModel(PlatformImplBase.java:499)
at com.haley.foundation.db.migrate.TestDdlUtils.testFK(TestDdlUtils.java:37)
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:585)
at junit.framework.TestCase.runTest(TestCase.java:168)
at junit.framework.TestCase.runBare(TestCase.java:134)
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:124)
at junit.framework.TestSuite.runTest(TestSuite.java:232)
at junit.framework.TestSuite.run(TestSuite.java:227)
at org.junit.internal.runners.OldTestClassRunner.run(OldTestClassRunner.java:76)
at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:38)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:460)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:673)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:386)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
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.SQLServerStatement.getNextResult(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(Unknown Source)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeUpdate(Unknown Source)
at org.apache.ddlutils.platform.PlatformImplBase.evaluateBatch(PlatformImplBase.java:336)
... 21 more

This is clearly because SQL Server 2005 does not recognise "RESTRICT" as a valid ON UPDATE value. Essentially the problem is that the methods writeForeignKeyOnDeleteAction and writeForeignKeyOnUpdateAction for the class automatically use the RESTRICT value while writing the SQL for the ALTER TABLE.

So, there seem to be 2 parts to the solution here:

1. Writing the schema XML for ON UPDATE and ON DELETE for MSSQL Servers should probably default to "NO ACTION" rather than "RESTRICT"

2. When read in a schema if the onDelete and onUpdate values are "restrict" the MSSQLBuilder should Interpret these as "NO ACTION"

I am new to the code base, but I would like to propose the following fixes for 1 and 2:

• Override method in in JdbcModelReader: protected CascadeActionEnum convertAction(Short jdbcActionValue) in the subclass MSSqlModelReader so that it returns the value CascadeActionEnum.NONE by default for MSSsql

• Change the methods writeForeignKeyOnDeleteAction and writeForeignKeyOnUpdateAction from private to protected so that MSSqlBuilder can override them to provide a correct implementation of "RESTRICT"(throw error or interpret to "NO ACTION"

I notice that the methods writeForeignKeyOnDeleteAction and writeForeignKeyOnUpdateAction are private methods and so cannot be overriden.