db-ddlutils-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Frank Hampshire (JIRA)" <j...@apache.org>
Subject [jira] Created: (DDLUTILS-222) ON UPDATE, ON DELETE errors for MS Sql Server - does not implement default "RESTRICT"
Date Mon, 04 Aug 2008 06:07:44 GMT
ON UPDATE, ON DELETE errors for MS Sql Server - does not implement default "RESTRICT"
-------------------------------------------------------------------------------------

                 Key: DDLUTILS-222
                 URL: https://issues.apache.org/jira/browse/DDLUTILS-222
             Project: DdlUtils
          Issue Type: Bug
          Components: Core - SqlServer
    Affects Versions: 1.1
         Environment: MS SQL Server 2005
            Reporter: Frank Hampshire
            Assignee: Thomas Dudziak


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.

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


Mime
View raw message