Skip to main content

Notice: this Wiki will be going read only early in 2024 and edits will no longer be possible. Please see: https://gitlab.eclipse.org/eclipsefdn/helpdesk/-/wikis/Wiki-shutdown-plan for the plan.

Jump to: navigation, search

Difference between revisions of "EclipseLink/Development/Incubator/Extensions/SymfowarePlatform"

(Level of Testing: updated results of JPA test set, after replacing recreation of tables by deletes)
(Limitations: Added Query Timeout, Bulk update/delete (UpdateAll/DeleteAll), identity columns, 3-arg LOCATE, table drop, table generator's default table name SEQUENCE .)
Line 115: Line 115:
 
* Reserved SQL keywords cannot be used as table, column or sequence names. Use a different name, or enclose the name in double quotes. For example:
 
* Reserved SQL keywords cannot be used as table, column or sequence names. Use a different name, or enclose the name in double quotes. For example:
 
<source lang="java">@Column(name="\"LANGUAGE\"")</source>
 
<source lang="java">@Column(name="\"LANGUAGE\"")</source>
 +
** The default table name of the TABLE generator is 'SEQUENCE', which is a reserved SQL keyword. Implicitly specify a valid table name.
 
* Spaces cannot be used in table, column or sequence names.
 
* Spaces cannot be used in table, column or sequence names.
* The MOD(x, y) function is executed as 'CASE WHEN y = 0 THEN x ELSE (x - y * TRUNC( x / y )) END' on Symfoware database, which gives the same result as the MOD function on Oracle database. Input parameters cannot be used for both its arguments at the same time. In that case, calculate the modulus in Java code first and pass the result to the query instead.
+
* The MOD(x, y) function is executed as 'CASE WHEN y = 0 THEN x ELSE (x - y * TRUNC( x / y )) END' on Symfoware database, which gives the same result as the MOD function on Oracle database. Input parameters cannot be used for both its arguments at the same time. In such case, calculate the modulus in Java code first and pass the result to the query instead.
* The third argument to the LOCATE function cannot be an input parameter.
+
* The third argument of the LOCATE function cannot be used.
 
* No more than one input parameter can be used as argument to the LOCATE function.
 
* No more than one input parameter can be used as argument to the LOCATE function.
* Input parameters cannot be used as arguments to the TRIM function.
 
 
* The first argument to the SUBSTRING function cannot be an input parameter.
 
* The first argument to the SUBSTRING function cannot be an input parameter.
 +
* Input parameters cannot be used as arguments to the TRIM function.
 
* Input parameters cannot be used as argument to the LENGTH function. Calculate the length in Java code first and pass the result to the query instead.
 
* Input parameters cannot be used as argument to the LENGTH function. Calculate the length in Java code first and pass the result to the query instead.
 
* Input parameters cannot be used as adjacent arguments to the CONCAT function. Concatenate the values in Java code first and pass the result to the query instead.
 
* Input parameters cannot be used as adjacent arguments to the CONCAT function. Concatenate the values in Java code first and pass the result to the query instead.
* Input parameters cannot be used at both sides of an operand at the same time in a SQL statement (e.g. '? * ?').
+
* Input parameters cannot be used at both sides of an operand at the same time in an SQL statement (e.g. '? * ?'). Perform the operation in Java code first and pass the result to the query instead.
* The LockNoWait option of Pessimistic Locking cannot be used.
+
* Input parameters cannot be used as arguments to the UPPER and LOWER functions. Perform the operation in Java code first and pass the result to the query instead.
* Pessimistic Locking adds 'FOR UPDATE' to SELECT statements, and cannot be used with queries that use DISTINCT.
+
* Identity columns cannot be used. When primary key generation type IDENTITY is specified, a database sequence will be used instead.
 +
* Pessimistic Locking adds 'FOR UPDATE' to the SELECT statement, and cannot be used with queries that use DISTINCT.
 
* Pessimistic Locking cannot be used with queries that select from multiple tables.
 
* Pessimistic Locking cannot be used with queries that select from multiple tables.
 +
* The LockNoWait option of Pessimistic Locking cannot be used; it is ignored when specified (i.e. only 'FOR UPDATE' is added to the SELECT statement).
 +
* Query timeout cannot be used; the timeout value is silently ignored.
 +
* Bulk update and delete operations that require multiple tables to be accessed cannot be used (e.g. bulk operation on an entity that is part of an inheritance hierarchy, etc.). (See {{bug|298193}}).
 +
* Dropping of tables, sequences and procedures while the database connection is still open can fail due to unreleased locks. Shut down the Java process that executed the create operation before performing the drop operation, or have the create operation use an unpooled connection that is closed after use (GlassFish's deploy-time table generation function uses an unpooled connection).
  
  
Line 132: Line 138:
 
* The LOG, SIN, COS and TAN functions cannot be used.
 
* The LOG, SIN, COS and TAN functions cannot be used.
 
* The standard deviation (STDDEV) and variance (VARIANCE) functions cannot be used.
 
* The standard deviation (STDDEV) and variance (VARIANCE) functions cannot be used.
* Input parameters cannot be used as arguments to the UPPER and LOWER functions.
 
 
* '= NULL' and '<> NULL' cannot be used for null comparisons in the WHERE clause. Use 'IS (NOT) NULL' instead.
 
* '= NULL' and '<> NULL' cannot be used for null comparisons in the WHERE clause. Use 'IS (NOT) NULL' instead.
 
* A scrollable cursor policy of CONCUR_UPDATABLE mode cannot be used with queries that select from multiple tables.
 
* A scrollable cursor policy of CONCUR_UPDATABLE mode cannot be used with queries that select from multiple tables.
* Columns and literals of different type may need casting to allow them to be compared or assigned. For example:
+
* UpdateAll and DeleteAll queries on multi-table objects (see limitation of JPA's bulk update and delete operations).
 +
* Columns and literals of different type may need casting to allow them to be compared or assigned. For example:
 
<source lang="sql">'SELECT ... WHERE CAST(PHONE_ORDER_VARCHAR AS INTEGER) BETWEEN 0 AND 1'
 
<source lang="sql">'SELECT ... WHERE CAST(PHONE_ORDER_VARCHAR AS INTEGER) BETWEEN 0 AND 1'
 
</source>
 
</source>

Revision as of 21:50, 3 January 2010

Note: This page describes an extension of EclipseLink that is part of the EclipseLink incubator. Incubator projects are published so the community can use them either to progress towards having them included in the main product, or to use in their own implementations. They have been tested to varying levels and as such, we recommend doing your own testing before including any of this code in a production-level product. Please report any issues via the bug listed below:

Bug

bug 288715

Description

This is a subclass of DatabasePlatform that can be used by customers using the Symfoware database.

Documentation

This section should be written as the platform is exercised and should include things like "configuration", "how to use", "limitations"

Automatic Table Generation

The following table shows the default SQL data types and sizes used for table generation.


Java data type Symfoware SQL data type
boolean
java.lang.Boolean
SMALLINT default 0
byte
java.lang.Byte
SMALLINT
char
java.lang.Character
CHARACTER(1)
short
java.lang.Short
SMALLINT
int
java.lang.Integer
INTEGER
long
java.lang.Long
NUMERIC(18)
float
java.lang.Float
NUMERIC(18,4)
double
java.lang.Double
NUMERIC(18,4)
java.lang.String VARCHAR(255)
java.math.BigDecimal DECIMAL(18)
java.math.BigInteger NUMERIC(18)
java.sql.Date DATE
java.sql.Time TIME
java.sql.Timestamp TIMESTAMP
java.util.Date Refer to the specified TemporalType mapping
java.util.Calendar
byte[] BLOB(1024)
java.lang.Byte[] BLOB(1024)
char[] VARCHAR(255)
java.lang.Character[] VARCHAR(255)
enum(ordinal) Refer to the java.lang.Integer mapping
enum(String) Refer to the java.lang.String mapping
Serializable object BLOB(1024)
(LOB)byte[] BLOB(1024)
(LOB)java.lang.Byte[] BLOB(1024)
(LOB)Serializable object BLOB(1024)
(LOB)char[] VARCHAR(255)
(LOB)java.lang.Character[] VARCHAR(255)
(LOB)java.lang.String VARCHAR(255)


Limitations

SymfowarePlatform inherits the limitations of Symfoware Server and its JDBC driver. For example, note the following restrictions. Refer to the database manual for details.

  • Reserved SQL keywords cannot be used as table, column or sequence names. Use a different name, or enclose the name in double quotes. For example:
@Column(name="\"LANGUAGE\"")
    • The default table name of the TABLE generator is 'SEQUENCE', which is a reserved SQL keyword. Implicitly specify a valid table name.
  • Spaces cannot be used in table, column or sequence names.
  • The MOD(x, y) function is executed as 'CASE WHEN y = 0 THEN x ELSE (x - y * TRUNC( x / y )) END' on Symfoware database, which gives the same result as the MOD function on Oracle database. Input parameters cannot be used for both its arguments at the same time. In such case, calculate the modulus in Java code first and pass the result to the query instead.
  • The third argument of the LOCATE function cannot be used.
  • No more than one input parameter can be used as argument to the LOCATE function.
  • The first argument to the SUBSTRING function cannot be an input parameter.
  • Input parameters cannot be used as arguments to the TRIM function.
  • Input parameters cannot be used as argument to the LENGTH function. Calculate the length in Java code first and pass the result to the query instead.
  • Input parameters cannot be used as adjacent arguments to the CONCAT function. Concatenate the values in Java code first and pass the result to the query instead.
  • Input parameters cannot be used at both sides of an operand at the same time in an SQL statement (e.g. '? * ?'). Perform the operation in Java code first and pass the result to the query instead.
  • Input parameters cannot be used as arguments to the UPPER and LOWER functions. Perform the operation in Java code first and pass the result to the query instead.
  • Identity columns cannot be used. When primary key generation type IDENTITY is specified, a database sequence will be used instead.
  • Pessimistic Locking adds 'FOR UPDATE' to the SELECT statement, and cannot be used with queries that use DISTINCT.
  • Pessimistic Locking cannot be used with queries that select from multiple tables.
  • The LockNoWait option of Pessimistic Locking cannot be used; it is ignored when specified (i.e. only 'FOR UPDATE' is added to the SELECT statement).
  • Query timeout cannot be used; the timeout value is silently ignored.
  • Bulk update and delete operations that require multiple tables to be accessed cannot be used (e.g. bulk operation on an entity that is part of an inheritance hierarchy, etc.). (See bug 298193).
  • Dropping of tables, sequences and procedures while the database connection is still open can fail due to unreleased locks. Shut down the Java process that executed the create operation before performing the drop operation, or have the create operation use an unpooled connection that is closed after use (GlassFish's deploy-time table generation function uses an unpooled connection).


The following restrictions are related to EclipseLink specific functionality (outside of JPA scope):

  • The LOG, SIN, COS and TAN functions cannot be used.
  • The standard deviation (STDDEV) and variance (VARIANCE) functions cannot be used.
  • '= NULL' and '<> NULL' cannot be used for null comparisons in the WHERE clause. Use 'IS (NOT) NULL' instead.
  • A scrollable cursor policy of CONCUR_UPDATABLE mode cannot be used with queries that select from multiple tables.
  • UpdateAll and DeleteAll queries on multi-table objects (see limitation of JPA's bulk update and delete operations).
  • Columns and literals of different type may need casting to allow them to be compared or assigned. For example:
'SELECT ... WHERE CAST(PHONE_ORDER_VARCHAR AS INTEGER) BETWEEN 0 AND 1'

Location

svn+ssh://dev.eclipse.org/svnroot/rt/org.eclipse.persistence/incubator/extensions/trunk/org.eclipse.persistence.platform.database.symfoware

Level of Testing

  • Sept 3, 2009 - Initial check-in - Template file. Awaiting initial implementation from contributor.
  • Sept 15, 2009 - Initial implementation provided. Awaiting review and commit to incubator. Meanwhile testing is continuing.
  • Oct 17, 2009 - Test success rates so far: JPA 57.90% (1304); Foundation 77.97% (4802); CTS not run yet.
  • Oct 23, 2009 - Test success rates so far: JPA 57.90% (1304); Foundation 99.36% (8555); CTS not run yet.
  • Dec 11, 2009 - Test success rates so far: JPA 57.10% (1583); Foundation 99.69% (11319); CTS not run yet.
  • Dec 18, 2009 - Test success rates so far: JPA 79.53% (1583); Foundation 99.69% (11319); CTS not run yet.


Testing is currently performed on the following environment:

  • EclipseLink jar and tests built from TRUNK (regularly updated to keep local environment up to date)
  • Symfoware Server V10 (pre-release) installed on RH Linux
  • Test sets are run on Windows 2003SE and XP with Symfoware JDBC 4 driver
  • Fujitsu JDK6

As the test failures and errors are being analysed the Limitations above and Open Issues below are updated.

Location of tests and examples

No specific tests or examples have been written. Until a database conformance suite (see EclipseLink/Development/Testing/DatabaseCertification) is available, the following tests are run on this platform:


Test suite Location Setup
JPA Test Suite EclipseLink/Development/Testing/JPA See below
Foundation Test Suite LRG (long regression) EclipseLink/Development/Testing/foundation See below
Java EE 5 CTS (ejb30 category) Not publicly available Refer to the CTS guide


  • Install Symfoware Server Client on the machine that EclipseLink is installed on.
  • For the JPA and Foundation Test Suites, update the respective test.properties files with your JDBC connection settings.

For example:

jdbc.driver.jar=c:/SFWCLNT/JDBC/fjjdbc/lib/fjsymjdbc4.jar;c:/eclipselink_incubator_extensions/org.eclipse.persistence.platform.database.symfoware/classes
db.driver=com.fujitsu.symfoware.jdbc.SYMDriver
db.url=jdbc:symford://symfodb:56005/TESTDB
db.user=symfouser
db.pwd=symfopwd
db.platform=org.eclipse.persistence.extensions.platform.database.SymfowarePlatform

Note the following:

jdbc.driver.jar
Should include the path to the JDBC driver libraries. Refer to the Symfoware Server Client manual for details, and use the correct driver for your JDK version.
Should include the path to the Symfoware Platform class.
db.platform
The platform name is org.eclipse.persistence.extensions.platform.database.SymfowarePlatform. The "extensions" part will be dropped when the platform is moved to the main project.
  • Also, include the JDBC driver native libraries in your PATH (Windows) or LD_LIBRARY_PATH (Unix).

Open Issues

Bulk Update/Delete

  • EclipseLink allows two ways of doing Bulk Update/Delete.
    • The default version involves a subquery, that may include a reference to a table in the outer query. This is not supported by Symfoware
    • The alternate version involves temporary table (either Global or Local)
      • Symfoware does not support local temporary tables
      • Symfoware supports global temporary tables, but they may not be dropped until the connection that was using them is closed
      • EclipseLink tries to drop these tables when done with them causing locking issues
      • We are having issues creating the temp table in the correct tablespace. We will need to address this
        • The 'CREATE TABLE' syntax has an optional 'ON <database space>' clause, and Symfoware V10 added an option 'ON DEFAULT DBSPACE'
        • When the database space specification is omitted, it will look for a database space with the same name as the data resource name. The data resource name is a mandatory part of the database url used to obtain a connection ('TESTDB' in the example above)
        • For global temporary tables the database space specification is mandatory
        • Maybe the data resource can be obained from the connection (con.getMetaData().getUrl()) and used as database space in the global temporary table creation statement.

The following log is from one of the tests that has been run:

   [junit] [EL Finer]: ServerSession(14864562)--Thread(Thread[main,5,main])--client acquired
   [junit] [EL Finest]: UnitOfWork(31975400)--Thread(Thread[main,5,main])--Execute query DeleteAllQuery(referenceClass=Project sql="DELETE FROM TL_CMP3_PROJECT")
   [junit] [EL Finer]: ClientSession(2776693)--Connection(14563222)--Thread(Thread[main,5,main])--begin transaction
   [junit] [EL Fine]: ClientSession(2776693)--Connection(14563222)--Thread(Thread[main,5,main])--CREATE GLOBAL TEMPORARY TABLE TL_CMP3_PROJECT (PROJ_ID INTEGER NOT NULL, PROJ_TYPE VARCHAR(255), DESCRIP VARCHAR(255), PROJ_NAME VARCHAR(255), VERSION INTEGER, LEADER_ID INTEGER, PRIMARY KEY (PROJ_ID)) ON TESTDB01 10
   [junit] [EL Fine]: ClientSession(2776693)--Connection(14563222)--Thread(Thread[main,5,main])--INSERT INTO TL_CMP3_PROJECT (PROJ_ID) SELECT PROJ_ID FROM CMP3_PROJECT WHERE (PROJ_NAME = testUpdateAllProjects)
   [junit] [EL Fine]: ClientSession(2776693)--Connection(14563222)--Thread(Thread[main,5,main])--DELETE FROM TL_CMP3_PROJECT
   [junit] [EL Warning]: UnitOfWork(31975400)--Thread(Thread[main,5,main])--Local Exception Stack:
   [junit] Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.0.0.qualifier): org.eclipse.persistence.exceptions.DatabaseException
   [junit] Internal Exception: java.sql.SQLException: [SymfoWARE ODBC Driver][SymfoWARE Server] JYP2091E Table "TL_CMP3_PROJECT" of schema "DEVELOP" being used exclusively by another user.
   [junit] Error Code: -2091
   [junit] Call: INSERT INTO TL_CMP3_PROJECT (PROJ_ID) SELECT PROJ_ID FROM CMP3_PROJECT WHERE (PROJ_NAME = 'testUpdateAllProjects')
   [junit] Query: DeleteAllQuery(referenceClass=Project sql="DELETE FROM TL_CMP3_PROJECT")
   [junit]     at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:333) 

This lock does not appear to be related to a drop table, instead, the issue appears to be with an insert. What sequence of events causes this lock?

Drop Table Restrictions

  • The EclipseLink testing framework makes extensive use of DDL to clean up the database after tests run
  • Symfoware does not support dropping tables until the connections using those tables have been closed
  • Need clarification about whether using a transaction to wrap table creation and use makes any difference
  • Initial investigation will focus on getting our Core tests running based on these restrictions since JPA testing will need to address the bulk delete and update issue before they can run successfully.
    • Our core test framework may provide an easier place to deal with the drop table issues since most of the testing runs on a single EclipseLink session
    • We should start by trying to get our Core SRG running
    • The org.eclipse.persistence.testing.framework.TestSystem class and its subclasses deal with DDL. It should be possible to build some logic into TestSystem that ensures connections are closed prior to dropping tables
    • Info on the core test framework is here: http://wiki.eclipse.org/EclipseLink/Development/Testing/foundation
    • We should start with the Core SRG

Inner Join Keyword

  • When using ANSI outer join, EclipseLink generates SQL that includes "JOIN" syntax as well
    • e.g. SELECT t1.ID, t1.NAME, t1.SALARY FROM EMPLOYEE t1 LEFT OUTER JOIN (EMPLOYEE_PROJECT t2 JOIN PROJECT t0 ON (t0.ID = t2.projects_ID))ON (t2.employees_ID = t1.ID)
    • the JOIN keyword above (without LEFT/RIGHT OUTER) is not supported by Symfoware
    • The above SQL is used to do a JOINING Query across a Many-to-many relationship. This is a fairly commonly used optimization. How can SQL be written on Symfoware to do the following:
      • Assume Employee has a Many-To-Many relationship to Project with a join table representing the relationship
      • What SQL can be written that gets all the Employees and their Projects (and selects employees that have no project)
      • Ideally, this should be a single line of SQL
      • Ideally, the SQL should be as efficient as possible. (i.e. no extra outer joins)
      • The equivalent SQL for the above on Symfoware, according to the manual and tried out in my environment, is as follows.
        • "cor0" is a (mandatory) 'correlation name'.
 SELECT t1.ID, t1.NAME, t1.SALARY, cor0.name
 FROM EMPLOYEE t1 LEFT OUTER JOIN (
   SELECT t2.employees_ID, t0.name
   FROM EMPLOYEE_PROJECT t2, PROJECT t0
   WHERE t0.id = t2.projects_ID
 ) AS cor0 ON (cor0.employees_ID = t1.ID)

Generating the above SQL in place of the SQL that uses the correct ANSI join and outer join syntax would be a fairly challenging feature to implement.

  • The code attached to the bug has a portion of the functionality implemented in the code that is actually writing the SQL for the FROM clause. Unfortunately, that is too late to correctly rewrite this query. It is too late to add aliases for the subquery (the cor0 above)
  • To implement this feature, the code that would have to be changed is in the methods that normalize our Expressions. This code would have to be altered to detect the cases where the query would have to be rewritten and rebuild the selection criteria and the selected fields for the query to include the subquery and its aliases.
  • The normalization code mentioned above is some of the most complex code in the whole code base.
  • While it is certainly possible to implement this feature, I suspect that it would take an experienced EclipseLink developer a fairly large amount of time to correctly implement it.
  • An enhancement request could be entered, but I would be surprised if it found its way to the top of the queue in the near future unless there was another driving factor.
  • The options I see, are as follows:
    • Document a limitation that indicates the following cases are not supported
      • Outer Join to Many-Many relationship
      • Self-referencing outer join
    • Find out if Symfoware plans to fully implement ANSI joining
  • There was the suggestion that we could "fake" this feature by using an OUTER JOIN instead. I recommend against that strategy because of the two reasons someone would choose to use joining
    • A fetch join is used to improve the efficiency of a query. As more outer joins are added to a query, the result set gets big at an exponential rate. This removes any benefit one would get from the FETCH join
    • A non-fetch join is used to limit results. Adding an outer join means that the results are no longer limited in the same way.

Table, Column Name Restrictions

  • The EclipseLink testing framework has tests that use reserved SQL keywords as table, column or sequence names. Symfoware does not allow the use of SQL keywords from SQL92, SQL95, SQL96, SQL2000 and SQL2007.
    • Symfoware does allow them if the keywords are enclosed in double-quotes.
    • Some of these tests have been changed to use another name, but not all. In particular, the use of keyword SEQUENCE as table sequence name is causing failures.
    • Tests will be updated to use either non-reserved words or quoted identifiers
    • Defaults for SEQUENCE table in our core code cannot be updated as easily since there is a backwards compatibility risk if we update the defaults to use "/"SEQUENCE/"" since we cannot test all the database people use and it is possible that some databases either will not support quoted identifiers or will use a character other than the double quote as the delimiter.
      • Symfoware users will have to be aware of this limitation and explicitly specify SEQUENCE table name as quoted
      • We will have to address this issue for testing as we go

Column Precision Restrictions

  • The EclipseLink testing framework has tests that use hard-coded database column types with precision values that exceed Symfoware's supported range of its corresponding types. There is no suitable column type with a bigger range that can be mapped to instead.
    • In some of these tests the precision has been decreased to fall in Symfoware's supported range, but not all. For example, a column type of NUMBER or NUMERIC can have a maximum precision of 18 on Symfoware but some tests specify a value of 20 and 19/31 resp.
    • Tests will be updated to use smaller precision as necessary. A couple of initial passes will catch most of the issues and any dangling issues will be resolved as they are required.

Back to the top