Community
Participate
Working Groups
Pervasive PSQL EclipseLink JPA 2.0 support
Created attachment 222947 [details] PSQL DatabasePlatform Extension Pervasive's DatabasePlatform extension.
Checked into our incubator git git.eclipse.org/gitroot/eclipselink/incubator.git The platform can be found under extensions/org.eclipse.persistence.platform.database.pervasive What testing have you run. What, specifically, are the results?
We have run CORE, SRG and STORED PROCEDURE tests. Results are below: CORE ---- Tests run Failures Errors 690 0 1 1. Issue with Scrollable Cursors (Cause Unknown) The following tests yield the error: "The cursored stream size function is not working properly". • ScrollableCursorTest(... testing.models.employee.domain.Employee) • ScrollableCursorTest(...testing.models.employee.domain.LargeProject) We are thus far unable to determine the reason for this failure, therefore, these tests are disabled. JPA SRG -------- Tests run Failures Errors 234 4 8 The 8 "Errors" are due to the following 4 reasons. Two are known limitations in Pervasive SQL. 1. Pervasive SQL doesn't support dynamic parameters in the SELECT list (2 Errors) Tests: testUpdateUsingTempStorage testUpdateUsingTempStorageWithParameter 2. Pervasive SQL IDENTITY column is limited,(1 Error) Pervasive SQL IDENTITY column is an integer type. So, when we attempt to create a foreign key on Buyer_CREDITCARDS.BUYER_ID, it fails, because this column is defined as BIGINT and the PK column, CMP3_BUYER.BUYER_ID is defined as IDENTITY. Test: testSetup 3. "The number of actual and formal parameters differs" (4 Errors) We were unable to determine the cause of this error. Tests: testUpdateAllLargeProjectsWithNullTeamLeader testUpdateAllProjectsWithNullTeamLeader testUpdateAllSmallProjectsWithNullTeamLeader testDeleteEverything 4. "DriverWrapper: old connections are broken" (1 Error) We were unable to determine the cause of this error. This is the same message that is treated as a "Failure" in two other tests: "testDeadConnectionFailover" and "testDeadPoolFailover". We don't know why it is treated as an "Error" in this case, or what causes this message in the first place. Test: testEMCloseAndOpen STORED PROCEDURE TEST --------------------- Tests run Failures Errors 116 0 0
Details of the Files that have been modified: Set Flags for IsPervasive() In two tests, the boolean attribute CONCUR_UPDATABLE_isSupported is set to false. This allows processing to continue in a read-only fashion. Affected files: .\foundation\eclipselink.core.test\src\org\eclipse\persistence\testing\tests\queries\Jdk12ScrollableCursorTest.java .\foundation\eclipselink.core.test\src\org\eclipse\persistence\testing\tests\queries\ScrollableCursorTest.java Disabled this test. Changing CONCUR_UPDATABLE_isSupported to false helped, but the test still fails with the error: "The cursored stream size function is not working properly". This means one of the two queries ( "normal" or "cursored") is not returning the right number of rows. Shorten LOCATION column (to avoid key length > 255) An explicit change to the test code was necessary to avoid trying to define keys longer than 255. Affected files: .\foundation\eclipselink.core.test\src\org\eclipse\persistence\testing\models\aggregate\House.java .\foundation\eclipselink.core.test\src\org\eclipse\persistence\testing\models\aggregate\SellingPoint.java .\foundation\eclipselink.core.test\src\org\eclipse\persistence\testing\models\mapping\EmergencyExit.java Modified Custom SQL for StoredProcedureTest The Stored Procedure test is driven by a file called EmployeeCustomSQLSystem.java, which contains customizations for stored procedure handling by all of the vendors. A section for Pervasive was added. .\foundation\eclipselink.core.test\src\org\eclipse\persistence\testing\tests\customsqlstoredprocedures\EmployeeCustomSQLSystem.java Added required Pervasive stored procedure customizations. .\foundation\eclipselink.core.test\src\org\eclipse\persistence\testing\tests\customsqlstoredprocedures\CacheHitTest.java Enabled this test for Pervasive. The test passes. .\foundation\eclipselink.core.test\src\org\eclipse\persistence\testing\tests\TestRunModel.java Selected which stored procedure tests from LRG to run.
Created attachment 222981 [details] Pervasive's modified TestRunModel.java
Created attachment 222982 [details] Pervasive's modified SellingPoint.java
Created attachment 222983 [details] This zip contains all files modified by Pervasive.
DatasourcePlatform and PervasivePlatform should both implement an isPervasive() method. Have I missed something? I am comparing the contents of the zip file to what is in our current Git master and I do not see any changes made by you.
Created attachment 223035 [details] Pervasive modified files zip v1
sorry, zipped up the wrong files. Have added a new zip file with our modifications.
I have pushed the additional files into the incubator. The TestRunModel you have provided comments out the vast majority of our tests. Can you comment on the reasons for that.
We were trying to confirm that we could support stored procedures in JPA. Our understanding is that stored procedure tests are run only as part of the LRG, which we did not consider to be a requirement at this point of time. The changes we made to TestRunModel.java were because we wanted to enable only the stored procedure tests, without attempting to run the entire LRG.
Is there anything I can do to help you figure out the source of the 3 issues you were unable to diagnose?
Thanks for the offer Tom. But for now, our priority is to get this Platform promoted as is if possible. Do we have to resolve the errors for it to be promoted?
In general, our policy for promotion is that the JPA and Core SRG tests run without error. That means triaging tests that are failing and determining if they are the result of a database limitation or are an actual bug in the submitted DatabasePlatform.
We have four remaining unexplained errors in the Eclipselink JPA SRG test. They are in these tests: -- testUpdateAllProjectsWithNullTeamLeader -- testUpdateAllSmallProjectsWithNullTeamLeader -- testUpdateAllLargeProjectsWithNullTeamLeader -- testDeleteEverything The errors all involve calling setBudget() on CMP3_LPROJECT.BUDGET, and are of the form: Exception Description: Trying to invoke [setBudget] on the object with the value [100.98]. The number of actual and formal parameters differs, or an unwrapping conversion has failed. Internal Exception: java.lang.IllegalArgumentException: java.lang.ClassCastException@36ab12 Mapping: org.eclipse.persistence.mappings.DirectToFieldMapping[budget-->CMP3_LPROJECT.BUDGET] Descriptor: RelationalDescriptor(org.eclipse.persistence.testing.models.jpa.advanced.LargeProject --> [DatabaseTable(CMP3_PROJECT), DatabaseTable(CMP3_LPROJECT)]) My investigations thus far have not been very fruitful, but it appears that several other tests successfully create Large Projects with null Team Leaders, or with Team Leaders that are persisted immediately before the Large Project itself. The failures seem to occur when trying to use a Team Leader that was persisted at some earlier point (and possibly committed to the database). So, the function EntityManagerJUnitTestSuite.createProjectsWithName() seems to succeed when a null teamLeader is passed in and seems fail otherwise. The former is the case when it is called from internalTestUpdateAllProjects() and internalTestUpdateAllProjectsWithName(), but the latter is the case when called from internalTestUpdateAllProjectsWithNullTeamLeader(). The test testdeleteEverything() gets the same error, but seems to follow a somewhat different pattern. Note also that we have done extensive massaging of AdvancedTableCreator.java for this test, in order to avoid errors during testSetup(). The reason is that Pervasive's IDENTITY column is also a datatype that is equivalent to INTEGER. The declaration of NUMERIC (15) for the IDENTITY has no effect on this, therefore the numerous foreign key columns created as NUMERIC(15) cannot be added as foreign keys, because they get a data type mismatch between INTEGER and NUMERIC(15). So, we have changed a large number of NUMERIC(15) columns to be INTEGER instead. I could not immediately see any reason why these changes should affect the CMP3_LPROJECT.BUDGET column. The other remaining errors in the JPA SRG have much more straightforward reasons, which we feel confident we can explain. I'm also attaching the html summary from the JPA SRG and the modified AdvancedTableCreator source.
Created attachment 223735 [details] Latest JPA SRG results summary.
Created attachment 223736 [details] Modified AdvancedTableCreator source.
It looks to me like the original exception (ClassCastException) is happening in our weaved code. A couple of questions/comments 1. If you run the code in org.eclipse.persistence.testing.tests.jpa.advanced.EntityManagerJUnitTestSuite.createProjectsWithName() on it's own on a clean DB, does it fail? 2. What about code in org.eclipse.persistence.testing.tests.jpa.advanced.EntityManagerJUnitTestSuite.deleteProjectsWithName(). Does that code appear to succeed in pervasive? Does the SQL look correct to you considering the multiple tables involved? 3. What happens if you run testdeleteEverything() on it's own? (e.g. Just run the testSetup test and then testdeleteEverything())
Your suggestions were immediately beneficial. Regarding suggestion (3), I ran testDeleteEverything() by itself on a clean DB and it succeeded. Unfortunately, if I try re-adding all the other tests, except the ones with related errors, testDeleteEverything() fails again. The newly disabled tests are: --testUpdateAllProjectsWithNullTeamLeader --testUpdateAllLargeProjectsWithNullTeamLeader --testUpdateAllSmallProjectsWithNullTeamLeader So, we have now confirmed that it is some data modification in some other test besides those above that is causing the problem with testDeleteEverything(). I suppose I could proceed by trying to determine which test(s) this is. Regarding your other two suggestions, namely to run createProjectsWithName() and deleteProjectsWithName() by themselves, there is already evidence that these functions succeed some of the time, since they are called from other tests that succeed (such as the tests which call internalTestUpdateAllProjects() and internalTestUpdateAllProjectsWithName()). It is only when they are called from internalTestUpdateAllProjectsWithNullTeamLeader that they seem to fail. Also, I did not mention the fact that, after a certain amount of trial and error, we have typically been setting up the database as follows: 1. Create a new, empty database 2. Execute the DDL statements saved in the file org.eclipse.persistence\jpa\eclipselink.jpa.test\run\advanced-model-create.jdbc 3. Run the test However, before successfully running testDeleteEverything() by itself, I added a fourth step: 1. Create a new, empty database 2. Execute the DDL statements saved in the file org.eclipse.persistence\jpa\eclipselink.jpa.test\run\advanced-model-create.jdbc 3. Execute the DDL statements saved in the file org.eclipse.persistence\jpa\eclipselink.jpa.test\run\advanced-model-drop.jdbc 4. Run the test This leaves some of the SEQUENCE infrastructure around, but not the data tables.
In testDeleteEverything, does clearing the cache help. e.g. Try to put the following calls just before the call to persistExample(): em.clear(); clearCache();
Yes, adding those two lines worked. Only the three tests mentioned above are disabled, and testDeleteEverything() succeeds.
Are you in a state where you can get a successful SRG run now? If so, I suggest augmenting your Platform implementation with comments that indicate any limitations you either learned from the testing, or were aware of from the beginning.
Created attachment 223981 [details] Latest test results as of 11/26/2012
Created attachment 223982 [details] Latest modified source and descriptions as of 11/26/2012.
Created attachment 223983 [details] Latest PervasivePlatform.java, with comments added per Tom's suggestion.
The following new attachments have been posted: Pervasive-Test-Results-112612.zip, containing: junit-noframes-Core-112612.html Latest Eclipselink Core SRG results, showing no errors. junit-noframes-JPA-112012.html Latest JPA SRG results, showing no errors. junit-noframes-SPTest-050912.html Latest Store Procedure Test results, showing no errors. Pervasive-Modified-JPA-Source-112612.zip Complete set of latest modified source with originals for diffing. Also includes description of all changes in "ModifiedJPASource.txt". Updated PervasivePlatform.java Contains comments on test results and known limitations.
Created attachment 224014 [details] Two additional modified source files that had been omitted.
You have added several attachments. Can you either post a single attachment that contains exactly the changes you hope to see, or tell me specifically which attachments you are hoping to have checked in.
Created attachment 224020 [details] Complete modified source and description as of 11/27/2012. Per Tom's request, I have combined all modified source and the descriptions into a single zip file.
The attached zip file has files at the base level and a similar set of files under the "org" directory. Which set is correct?
The top-level directory contains the modified files with Pervasive's changes. The "org" directory contains the original files as downloaded from Eclipselink in February, 2012. The idea was to make it fairly easy to do a diff, in order to see what changes that we have made. Neither set of source has been refreshed from Eclipselink since then.
The major concern I have is the changes to AdvancedTableCreator. It is difficult for me to justify changing a huge number of tests to operate on INTEGER instead of the SQL NUMERIC type. I am surprised that NUMERIC is not completely supported for columns that make up a primary key. Please provide exact details of why this change is neccessary. I suspect, at the least, we will have to minimize this change so it only occurs when running on PervasivePlatform.
I agree that the changes to AdvancedTableCreator are rather extensive. Perhaps I did not explain the IDENTITY issue very clearly. Pervasive's IDENTITY column is a data type, not an attribute or constraint of a column. The data type is either a 2- or 4-byte integer (SMALLIDENTITY or IDENTITY). Any foreign keys that reference an IDENTITY column must, therefore, also be 2- or 4-byte integers (SMALLINT or INTEGER). This DDL limitation is well known to Pervasive users. In AdvancedTableCreator, all of the primary and foreign keys are created as type NUMERIC(15), which our platform implementation maps to BIGINT. Pervasive ignores this data type when creating the primary key IDENTITY column (see the overridden method printFieldTypeSize()). Later, when the foreign key constraint is added, there is a type mismatch between the INTEGER primary key and the BIGINT foreign key, generating the Pervasive error: "Data type in foreign key is invalid. Error Code: -3038". We have received enhancement requests to allow 8-byte IDENTITY columns, which might help solve this particular problem. It is also true that the current changes to AdvancedTableCreator affect more than the absolute minimum of necessary columns. There are about 100 columns created as NUMERIC(15) and only about half of these are foreign keys. I can probably reduce this number to close to the minimum with additional effort. Alternatively, as you say, I would need to figure out a way to limit the use of the alternate data type the Pervasive platform. This would probably require a boolean argument to the AdvancedTableCreator constructor indicating that it is creating the NUMERIC(15) foreign key fields as INTEGER, plus the if-then-else code in about 40-50 places to determine which data type to use. Either one of these will still rather be intrusive, though hopefully somewhat less so than the current situation.
I think we will need to limit the use of the alternate data type the Pervasive platform - I realize this is more work, but it is difficult to justify changing the data types so extensively for a single platform.
I've been thinking about the least intrusive way to do this and I have several alternatives to consider. (1) Add a new AdvancedTableCreator class, e.g. PervasiveAdvancedTableCreator, based on the modified version of AdvancedTableCreator we provided. Then, in EntityManagerJUnitTestSuite.testSetup(), simply generate the necessary subtype of AdvancedTableCreator, based on the platform. The advantage is that it leaves the code for other vendors largely untouched. The disadvantage is that there would be some code duplication and it would be easier for someone to break the Pervasive tests by adding or changing table definitions. (2) This would be similar to (1), but inherit PervasiveAdvancedTableCreator from AdvancedTableCreator, overriding only the necessary buildXXXXTable() methods. (3) Change the AdvancedTableCreator to add the check for isPervasive() for each of the 33 fields where we would need to. This would also require changing AdvancedTableCreator to be aware of the current platform while adding the table definitions, which is currently done in the constructor. So, perhaps an alternative constructor with the actual table creation refactored into a separate function called from both the old and new constructors. The advantage to this is that there would be less duplication of code, but it would make the code less readable for all vendors, because of the conditional code in many buildXXXXTable() methods. (4) This would be similar to (3), but the AdvancedTableCreator would use a variable to specify the type name and size for each of the 33 fields that need to be changed. The variables would default to "NUMERIC" and 15, but if the platform was Pervasive, be set to "INTEGER" and 0. This would require fewer changes, but would make it harder to see the table definitions at a glance.
Would it be possible to override replaceTables method in AdvancedTableCreator that iterated through the table definitions and replaced fields based on some crieria (e.g. any field that is an ID and type NUMERIC) The advantage of that kind of change is that tests added in the future would likely need no additional changes to work. Note: The SymfowarePlatform already overrides some table creation functionality, so you may be able to use a similar pattern to determine if the override needs to occur. Take a look at org.eclipse.persistence.testing.framework.TogglingFastTableCreator. It uses a system property to determine whether to do the override.
I had thought of this, but was concerned with the number of iterations required through the list of TableDefinitions. It doesn't look quite as bad as I thought, so I am working on a solution based on your suggestion.
I was able to successfully change the data types on the fly for all but five of the target PK tables created by AdvancedTableCreator. The target tables listed below call setIsIdentity(true) but still get their PK column created as BIGINT, not IDENTITY. So now, when we set the FK field type to INTEGER in the code, we get a mismatch between the PK, which is BIGINT and the FK, which is INTEGER. I don't know why the eventual DDL is ignoring the isIdentity() flag for these. It may have something to do with the fact that the generation strategy is not GenerationType.IDENTITY (based on the contents of the file: eclipselink-annotation-model\META-INF\case-sensitive-advanced-entity-mappings.xml). It looks like our options are to revert to explicitly changing the data types for just these 5 primary keys or somehow code around the reason that they are not using the isIdentity() flag. CREATE TABLE CMP3_ADV_CUSTOMER (CUSTOMER_ID BIGINT... CREATE TABLE CMP3_DEALER (DEALER_ID BIGINT... CREATE TABLE CMP3_EMPLOYEE (EMP_ID BIGINT... CREATE TABLE CMP3_ADV_EQUIP (ID BIGINT... CREATE TABLE ADV_ENTYA (ID BIGINT...
I have added a method AdvancedTableCreator.adjustForeignKeyFieldTypes() which is only called for the Pervasive platform. It changes the data type of a field to INTEGER if that field is also a foreign key pointing to a primary key of type IDENTITY. It also uses a call to session.getPlatform().shouldPrintFieldIdentityClause() to verify that the primary key will actually use the IDENTITY keyword when being created. This method allows AdvancedTableCreator.testSetup() to succeed and the rest of the tests to pass. There is no explicit changing of any data types for any tables being created in the buildXXXXTable methods.
Created attachment 224246 [details] Complete set of changed source as of 12/03/2012 The latest complete zip file has been attached, Complete-Pervasive-Modified-JPA-Source-120312.zip.
I have updated your changed files based on recent changes to our trunk and checked them into the incubator. Please have a look to ensure they reflect what you expect. Note: Several tests have been added to EntityManagerJunitTestSuite Also, who should be listed as the copyright holder for PervasivePlatform in the license comment? Who should be listed as the author?
I don't seem to be looking in the right place for the revised source in the incubator. I'm looking in: http://dev.eclipse.org/svnroot/rt/org.eclipse.persistence/incubator/
http://git.eclipse.org/gitroot/eclipselink/incubator.git
I get a "not found" error accessing: http://git.eclipse.org/gitroot/eclipselink/incubator.git
The above URL will work in Git The following should let you browse on the web: http://git.eclipse.org/c/eclipselink/incubator.git/tree/extensions/org.eclipse.persistence.platform.database.pervasive
(1) I have verified the changes you made. Everything looks correct. I did correct a minor typo in AdvancedTableCreator.java since I last provided the file to you. Line 2613 needs a space between the quote and the text: sourceFieldDefinition.getName() + " to INTEGER. ^ This obviously affects only Pervasive users with FINEST logging turned on. (2) The author info should list: Peter Lohman (3) For the copyright info, I assume all you need is the first line of the file, i.e., above the standard Eclipselink licensing information. If so, please include: Copyright 2012 Pervasive Software Inc. All Rights Reserved Please let me know if there is anything else you need.
Submitted Changes Checked into master stream. Reviewed by: Tom Ware -> Reviewed user submitted files
PervasivePlatform is not checked into our 2.5.0 stream. A couple of suggestions: 1. It is a good idea to run testing on a regular basis (at least on late milestones within a release, if not on every milestone) and publish results 2. There is code to automatically detect the database platform, you may want to consider integrating into it. Configuration is currently in: foundation\org.eclipse.persistence.core\resource\org\eclipse\persistence\internal\helper\VendorNameToPlatformMapping.properties There will be new updates in 2.5.0 for spec-compliant database vendor detection.
Typo above: PervasivePlatform is NOW checked into our 2.5.0 stream.
The Eclipselink project has moved to Github: https://github.com/eclipse-ee4j/eclipselink
