Hi Markus,
First I'll try to address the last thing you ask in your email:
> Actually it seems to me that the
> tests try to do more things than are needed for JDBC and EJB 3.0. Might
> this be true?
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.
More comments inline:
Markus KARG wrote:
>Tom,
>
>I have bad news. What you told me is not working with MaxDB. Please find
>more inlined.
>
>(1) "The Wayne Problem"
>
>
>>The MySQL4Platform contains an implementation of this strategy. To
>>see how it works, take a look at the method:
>>
>>supportsGlobalTempTables
>>getCreateTempTableSqlPrefix
>>shouldAlwaysUseTempStorageForModifyAll
>>getCreateTempTableSqlPrefix
>>writeDeleteFromTargetTableUsingTempTableSql
>>writeUpdateOriginalFromTempTableSql
>>
>>
>I implemented a similar solution, but this is not working also. I think
>the reason is:
>
>- MaxDB supports global but not local temporary tables (I customized my
>plattform accordingly), but the behaviour (!) of what GF expects is
>different from MaxDB's actual behaviour. MaxDB decides whether a table
>is temporary or not by the qualifier of the name: TEMP.T1 is a temporary
>table, X.T1 is not. So, each table in the TEMP schema is global
>temporary. MaxDB removes the TEMP table after a connection is closed or
>committed as it seems. This leads to the problem that the GF tests will
>fail with "table not found". You can have a look at the testresults.txt
>file attached. It's the first thing that fails in the test (and it fails
>often).
>
>
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.
If you are still having issues, the output file with SQL logging would
be useful if I am going to try to help you since it will show me where
the temp tables are created. (enable logging at the FINER level)
>- 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".
>
>(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.
>(3) Exception Description: Error preallocating sequence numbers. The
>sequence table information is not complete.
>
>I think this might be the cause for (2) ?
>
>
I think this is related to the above issue.
>
>(4) Parameter spec not allowed in this context
>
>There are several tests creating SQL like the following:
>
>SELECT t0.F_NAME, t0.L_NAME, t0.ID_NUMBER, t0.DTYPE, t0.DEPT_NAME,
>t0.DEPT_ROLE, t0.DEPT_LOCATION, t0.CUBE_ID, t0.CUBE_CODE FROM
>CMP3_SCIENTIST t0 WHERE EXISTS (SELECT ? FROM CMP3_SCIENTIST t1 WHERE
>(((t0.F_NAME = t1.F_NAME) AND (t0.L_NAME = t1.L_NAME)) AND (t0.ID_NUMBER
>= t1.ID_NUMBER)))
>
>In fact, MaxDB is not able to do "SELECT ? FROM", since it only allows
>to have parameters for values but not for column names. Actually, I was
>not aware that this is valid JDBC SQL actually, before I saw that in the
>testresults.txt. Are you sure that it is allowed in JDBC to have
>parameters for column names?
>
>
This statement is not actually trying to parameterize a column name. It
is trying to parameterize a constant. In this case it is is doing "....
WHERE EXISTS ( SELECT 1 FROM...." DB2 has the same issue. You will
have to implement something similar to appendLiteralToCall() in DB2Platform.
>(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.
>
>
>(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)
>
>It seems what is wanted by the test is not able in MaxDB. This is what
>MaxDB says about that error message:
>
>
> -7040: Referential constraint not allowed Locate the document in the
> library structure
> <http://dev.mysql.com/doc/maxdb/en/54/d1851b068d11d2a96c00a0c9449261/frameset.htm>
>
>
> Explanation:
>
>There is already a referential CONSTRAINT definition
><http://dev.mysql.com/doc/maxdb/en/6d/117c5fd14811d2a97400a0c9449261/content.htm>
>with the specified referencing table. Moreover, CASCADE dependency
>arises. Under these conditions, all CONSTRAINT definitions with the
>indicated referencing table must specify either CASCADE or RESTRICT.
>
>
> User Action:
>
>Rethink the desired effect, because it would result in basically
>contesting situations. You can view information about existing
>referential CONSTRAINT definitions using a QUERY statement
><http://dev.mysql.com/doc/maxdb/en/0f/486f992f9611d3a98100a0c9449261/content.htm>
>issued on the DOMAIN.FOREIGNKEYCOLUMNS
><http://dev.mysql.com/doc/maxdb/en/9d/f7211b8d06154e935850f5b3b0143f/content.htm>
>system table.
>
>
>
>
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.
-Tom
>
>
>In fact, I do not know how to solve these six failures, so again, I am
>stuck. :-(
>
>Can you please help me solving this? Actually it seems to me that the
>tests try to do more things than are needed for JDBC and EJB 3.0. Might
>this be true?
>
>
>Thanks
>Markus
>
>