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"

(added Symfoware equivalent for inner join)
(added suggestion regarding tablespace issue)
Line 103: Line 103:
 
** 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 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)
 
** 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
 
*** 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
 
*** 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
 
*** We are having issues creating the temp table in the correct tablespace.  We will need to address this
*** Symfoware does not support local temporary tables
+
**** 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:
 
The following log is from one of the tests that has been run:

Revision as of 09:09, 18 October 2009

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"

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 MOD function cannot be used.
  • The third argument to the LOCATE function cannot be an input parameter.
  • 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 and LENGTH functions.
  • The first argument to the SUBSTRING function cannot be an input parameter.
  • No more than one input parameter can be used as argument to the CONCAT function.
  • The LockNoWait option of Pessimitic Locking cannot be used.
  • Pessimistic Locking cannot be used with queries that select from multiple tables.
  • Pessimistic Locking cannot be used with queries that use DISTINCT.

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% (of 1304); Foundation 77.97% (of 4802); 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)

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