Hi Markus,
I have an idea that might allow us to avoid dealing with the issue of
constraint creation for the time being and focus on other potential
issues. We can deal with constraint creation when every else works.
Implement the following method on your platform:
public boolean supportsForeignKeyConstraints() {
return false;
}
That should prevent TopLink from creating the constraints and allow
you to focus on the functionality that is closer to the core.
-Tom
Markus KARG wrote:
>Tom,
>
>
>>We are definitely requiring more than strict JDBC support. The reason
>>is that EJB 3.0 has some features that benefit quite a bit from some
>>JDBC extensions that are quite common (but perhaps not strictly required)
>>
>>1. DDL Generation is an optional feature in EJB 3.0. Quite a few of
>>the issues you are seeing are related to this issue. In our
>>experience, this is one of the areas that JDBC implementations vary
>>the most. The issues you are seeing with native sequencing and with
>>constraint creation are related to this issue. Despite the lower
>>compatibility between JDBC providers for DDL generation related
>>issues, it is very desirable feature for developers who want to get
>>started quickly. (Unfortunately, it is a bit less desirable for
>>people that want to add DatabasePlatforms)
>>
>>2. The JPA Query Language allows some syntax in Delete queries that
>>does not translate directly to SQL. An example of this is any delete
>>query that uses multiple tables. We have chosen two ways of
>>implementing this support. By default we do the subselect you
>>originally saw issues with in your "Wayne Problem". We have also
>>enabled the use of temporary tables to avoid the issues you were
>>seeing. Implementation of these two features is not consistent
>>accross JDBC implementations. Although this is not necessarily
>>standard from the JDBC point of view, we believe the way we have
>>implemented this feature is fairly standard throughout JPA
>>implementations. With enough demand, we could investigate other ways
>>of doing this.
>>
>>I have talked to my management about the difficulty in extending our
>>DatabasePlatform repertoire for members of the community and we think
>>we need to change our test framework to have 3 parts. 1. A Basic test
>>suite that uses only standard JDBC. 2. An extended test suite that
>>runs all of JPA except for DDL generation. 3. A test suite that makes
>>use of JDBC. With these 3 granularities, it would be possible to get
>>basic support running quickly, then identify what parts of the
>>extended functionality are difficult for your database. Finally,
>>since DDL generation is an extended feature it would be possible to
>>decide on a case-by-case basis whether you wanted support for DDL
>>generation in your database and perhaps check-in code without that
>>support. Unfortuantely, this work is probably somewhatlonger term
>>than will immediately help you.
>>
>>
>Thank you for this insight into the deeper sense of all that. In fact it
>helped me understand why the platform is such complex.
>
>More inlined.
>
>
>>>(1) "The Wayne Problem"
>>>
>>>
>>The removal of the table after the transaction ends sounds more like
>>what we have termed: Local Temporary Tables. Perhaps there's a
>>terminology issue there. Nonehtheless, we do support a second
>>temporary table generation strategy. That strategy is implemented by
>>SybasePlatform. Instead of overriding supportsGlobalTemporaryTables()
>>it overrides supportsLocalTemporaryTables() . It also implements some
>>slightly different methods for tempory table creation.
>>(getCreateTempTableSqlPrefix, getTempTableForTable,
>>writeUpdateOriginalFromTempTableSql) Perhaps that will help.
>>
>>
>Yes, it was a terminology problem: Originally I am coming from the
>Sybase ASA world, where global and local temporary tables are working
>quite different: They are created once statically, but at COMMIT they
>are losing their content. MaxDB is working more the way that you
>described, which I did not know when starting my platform. I now
>switched to support local instead of global.
>
>On the other hand, I now have the problem that the tests tell me that
>the temp table is missing:
>
>Testcase: testRefreshRemoved took 0,05 sec
> FAILED
>entityManager.refresh(removedObject) threw a wrong exception:
>Internal Exception: com.sap.dbtech.jdbc.exceptions.DatabaseException:
>[-4004] (at 13): Unknown table name:$CMP3_EMPLOYEEError Code: -4004
>Call:INSERT INTO TEMP.$CMP3_EMPLOYEE (EMP_ID) SELECT t0.EMP_ID FROM
>CMP3_EMPLOYEE t0, CMP3_SALARY t1 WHERE ((t0.F_NAME = 'testRefreshRemoved'
>) AND (t1.EMP_ID = t0.EMP_ID))
>Query:DeleteAllQuery()
>
>I did not find the reason actually. The name seems correct: I am putting
>a $ prefix in front of the table name when I want to address a temporary
>table. Also I am using the TEMP namespace. It seems that the tests
>either "forgot" to create the temporary table, or GF is unaware of the
>fact that MaxDB works this way:
>- Created table can only be seen inside of the same connection.
>- Created table automatically gets DROPped (not DELETEd) at end of
>connection.
>I have correctly set support for local=true, global=false.
>
>
>>>- GF tests seem not to use the "Temporary Table Solution" correctly
>>>every time, since I still find failing SQL UPDATE commands showing "The
>>>Wayne Problem", while not seeing any temporary table names in that SQL
>>>in testresults.txt (you can identify them easily since I have told my
>>>platform to add a leading $ prefix to the TEMP tables -- no $, no TEMP).
>>>
>>>
>>Can you point me more specifically at the issues you are seeing. My
>>search of your document does not find "UPDATE".
>>
>>
>It did not happen anymore, but instead I found that one:
>
>Testcase: test_null took 1,24 sec
> Caused an ERROR
>Internal Exception: com.sap.dbtech.jdbc.exceptions.DatabaseException:
>[-7008]: Updates of this table not allowedError Code: -7008
>Call:DELETE FROM CMP3_FUEL_VEH WHERE EXISTS(SELECT t0.ID FROM
>CMP3_VEHICLE t0, CMP3_FUEL_VEH t1 WHERE ((t1.ID = t0.ID) AND t0.VEH_TYPE
>IN ('C', 'IC', 'SC')) AND t1.ID = CMP3_FUEL_VEH.ID)
>Query:DeleteAllQuery()
>
>Actually "the wayne problem" happens less now, but still it happens. As
>you can see above, the tests still create SOME but not all SQL that is
>using DELETE together with subselect. So it seems, either the tests of
>GF is not taking care of whether it might do that in ALL places. Also
>"test_null" seems to be an odd name...?
>
>
>
>>>(2) Unknown Sequence Name: PROJECT_SEQ, ADDRESS_SEQ, BUYER_SEQ,
>>>SCIENTIST_SEQ, XML_MERGE_ADDRESS_SEQ
>>>
>>>Still it seems GF test are not issuing the needed CREATE SEQUENCE
>>>commands.
>>>
>>>
>>It looks like there must still be an issue with the ddl generation
>>phase of things. This may require looking at the logging with the
>>logging level set to FINER. I expect we will see an error logged. If
>>you want to send a version of your output file with logging turned on,
>>I can take a look.
>>
>>
>I have set from OFF to FINER in the "persistence.xml" file in -tests.
>But I did neither find the "CREATE SEQUENCE" SQL commands -- neither in
>the .jdbc files, nor in testresults.txt (see attached files).
>
>Maybe you expect me to send another file, so please tell me which one.
>
>
>
>>>(5) Data types must be compatible:CUSTOMER_CUST_ID
>>>
>>>It seems that some tests are assuming data types to be compatible that
>>>in fact are not on MaxDB:
>>>
>>>Testcase: testgetReference took 0,067 sec
>>>[-8006] (at 97): Data types must be compatible:CUSTOMER_CUST_ID
>>>ALTER TABLE CMP3_CUSTOMER_CUSTOMER ADD CONSTRAINT
>>>CMP3CUSTOMERCUSTOMERCSTMERCUSTID FOREIGN KEY (CUSTOMER_CUST_ID)
>>>REFERENCES CMP3_CUSTOMER (CUST_ID)
>>>
>>>Testcase: unknown took 0 sec
>>>[-8006] (at 104): Data types must be compatible:PARTSLIST_ID
>>>ALTER TABLE CMP3_XML_MERGE_PARTSLIST_ITEM ADD CONSTRAINT
>>>CMP3XMLMRGEPARTSLISTITEMPRTSLSTD FOREIGN KEY (PARTSLIST_ID) REFERENCES
>>>CMP3_XML_MERGE_ITEM (ITEM_ID)
>>>
>>>
>>What are the types in the generated tables? I am surprised they are
>>not the same. Note: These tables are generated by the DDL generation
>>feature. I can take a look at this issue too if you sent your output
>>file with FINER logging.
>>
>>
>See attached files.
>
>
>>>(6) Referential constraint not allowed
>>>
>>>Testcase: unknown took 0 sec
>>>[-7040]: Referential constraint not allowed
>>>ALTER TABLE CMP3_XML_UNI_PHONENUMBER ADD CONSTRAINT
>>>CMP3_XML_UNI_PHONENUMBERPHONE_ID FOREIGN KEY (PHONE_ID) REFERENCES
>>>CMP3_XML_UNI_PHONENUMBER (PHONE_ID)
>>>
>>>
>>>
>>I am a bit surprised we are creating this constraint. I will
>>investigate a bit further and let you know what I come up with.
>>
>>
>Once I typed in the fix for the bug you found in the tests, it worked
>well. Please check in your fix, it solves the problem. :-)
>
>Thanks for your kind help.
>I would be lost without.
>Markus
>
>