EclipseLink/Development/Incubator/Extensions/SymfowarePlatform

SymfowarePlatform was added to EclipseLink as part of a community contribution.

See information about the contributed platform here.

This page is an archive of the discussion that occurred during the development of SymfowarePlatform

Bug

bug 288715

Description

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

Documentation

Configuration

SymfowarePlatform is included in our automatic database platform detection feature and, as such, should automatically be detected when you login if you are using JPA.

If either you are not using JPA, or automatic detection is not working, it can be configured as follows:

1. In JPA set the persistence unit property "eclipselink.target-database" to "Symfoware"
2. In other configuration set the DatabasePlatform to org.eclipse.persistence.platform.database.SymfowarePlatform
   1. This setting is available in sessions.xml and deployment.xml
   2. This setting is also available through direct API on the session

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", with double quotes, which makes it a legal 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.
• 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 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 COALESCE function when it is used in the select list.
• When an input parameter is used as argument to the UPPER, LOWER or LENGTH functions, it is substituted with its value before the SQL statement is sent to the JDBC driver.
• When input parameters are used as arguments to the TRIM function, they are substituted with their values before the SQL statement is sent to the JDBC driver.
• Identity fields 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). When the tables have only a single primary key column, it might be possible to override the query with a native query without EXISTS:

E.g. JPQL statement: DELETE FROM Employee e WHERE (e.salary > 1000) (where 'salary' is stored in secondary table SALARY)
     Generated SQL: uses EXISTS with a subquery whose FROM clause includes the same table as the DELETE's (fails on Symfoware)
     Override with native SQL:
       DELETE FROM EMPLOYEE
         WHERE ID = (
           SELECT s.EMP_ID
           FROM SALARY s
           WHERE s.salary > 1000
         )

• 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 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

SymfowarePlatform is in the main EclipseLink jar file and in the org.eclipse.persistence.core bundle.

Level of Testing

• Sept 03, 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 FR 57.90% (1304); Core LRG 77.97% (4802)
• Oct 23, 2009 - Test success rates so far: JPA FR 57.90% (1304); Core LRG 99.36% (8555)
• Dec 11, 2009 - Test success rates so far: JPA FR 57.10% (1583); Core LRG 99.69% (11319)
• Dec 18, 2009 - Test success rates so far: JPA FR 79.53% (1583); Core LRG 99.69% (11319)
• Jan 05, 2010 - Test success rates so far: JPA FR 79.53% (1583); JPA SRG 100.00% (193); JPA LRG 94.63% (1787); Core SRG 100.00% (683); Core LRG 99.87% (11322)
• Feb 20, 2010 - Test success rates so far: JPA FR 99.63% (1621); JPA SRG 100.00% (195); JPA LRG 99.84% (1831); Core SRG 100.00% (691); Core LRG 100.00% (11496)
• Feb 26, 2010 - Test success rates so far: JPA FR 99.88% (1627); JPA SRG 100.00% (195); JPA LRG 99.89% (1837); Core SRG 100.00% (691); Core LRG 100.00% (11496)

(FR=FullRegressionTestSuite)

February 26/2010

• EclipseLink 2.1.0 Nightly 20100225
• DB: Symfoware Server V10
• JDBC driver: Symfoware JDBC 4 driver (RDB2_TCP)

Test Name	Tests	Failures	Errors
Core SRG	691	0	0
JPA SRG	195	0	0
Core LRG	11496	0	0
JPA LRG	1837	2	0
Server LRG (GlassFish v3)	-	-	-

Test results equivalent to previous run

• Two failures in JPA LRG/FR where Query#setFirstResult and setMaxResults don't seem to work well. (complexResultPropertiesTest and complexNamedQueryResultPropertiesTest). Needs further investigation.
• Occasionally a few tests in JPA LRG/FR have errors due to the DDL for a unique key's column not having 'NOT NULL' even though the attribute is set. Needs further investigation.

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
• Core LRG tests are run on 64bit OS with ANT_OPTS=-Xmx3000m and max.heap.memory=3500m to prevent OOM errors while generating JUnit report.

Testing durations:

• Core SRG: 2 min.; Core LRG: 55 min.
• JPA SRG: 1 min.; JPA LRG: 8 min.; JPA FR: 7 min.

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.
Awaiting finalization and acceptance of the Database Platform Promotion proposal (see EclipseLink/Development/Incubator/Extensions/DatabasePlatformPromotion), the following tests are run on this platform:

Test suite	Location	Setup
JPA Test Suite (SRG, LRG)	EclipseLink/Development/Testing/JPA	See below
Foundation Test Suite (SRG, LRG)	EclipseLink/Development/Testing/foundation	See below

• 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;codeselect=OS
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.

db.url

'codeselect=OS' is added to prevent Japanese messages from the database from being completely garbled when received on the client (required because I use a Japanese DB on Linux and the client is on English Windows).

• Also, include the JDBC driver native libraries in your PATH (Windows) or LD_LIBRARY_PATH (Unix).

Open Issues

Bulk Update/Delete

• Rfe bug 298193 tracks this issue. Until this is resolved, bulk update/delete of multi-table objects will be added to the limitations for this platform and relevant tests will be excluded.
• 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

• This issue has been resolved by changing the test code to set the 'SchemaManager.FAST_TABLE_CREATOR' flag to true when table generators are used multiple times. The first time tables are dropped and created, but in subsequent calls the rows in the tables are deleted, so no DDL statements are used.
• 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
  • It seemed to at first, but it did not solve all issues.
• 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
    • It turned out that the Core SRG did not have as many occurrences of this issue as JPA LRG, but the 'SchemaManager.FAST_TABLE_CREATOR' work-around worked for both.

Inner Join Keyword

• This issue has been resolved by employing the method in the last point, which is to replace the INNER JOIN with a LEFT or RIGHT OUTER JOIN for Symfoware.
• 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
    • Symfoware development team responded there are no such plans for Symfoware V10.
• 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.
• Closer investigation of tests failing because of this issue (about 50 in JPA LRG, 1050 in Core LRG), reveals that in some cases an OUTER JOIN would return the same result set:
  • 'EMPLOYEE_PROJECT JOIN PROJECT' - the table on the left is a join table, which never has NULLs for the columns referencing the table on the right.
    • -> LEFT OUTER JOIN gives the same result set.
  • 'EMPLOYEE JOIN SALARY' - the table on the right is a secondary table, on the left is the main table. For each row in the main table there is a corresponding row in the secondary table.
    • -> LEFT OUTER JOIN gives the same result set.
  • 'PROJECT JOIN LPROJECT' - the table on the left maps to a root class in an inheritance mapping with a joined subclass strategy. The table on the right maps to a subclass. For each row in the subclass table there is a corresponding row in the root class table.
    • -> RIGHT OUTER JOIN gives the same result set.

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.
    • This has been resolved by bug 300111, which allows the default sequence table name to be specified in the platform class. Symfoware platform's default sequence table name will be "SEQUENCE" (with double quotes), which makes it a legal name on this platform.

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.
  • 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 with fields mapping to BLOB and CLOB column types often have no size specified (presumably because these column types in the currently supported databases either take no size, or default to a size large enough for the tests). The Symfoware platform defaults to sizes that are not always sufficiently large for the test to run successfully.
  • Tests will be updated to use smaller precision or larges size as necessary. A couple of initial passes will catch most of the issues and any dangling issues will be resolved as they are required.