New dialect for Solid database for Hibernate 3.1.3 and Solid 4.5 onwards ------------------------------------------------------------------------ Key: HHH-1864 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-1864 Project: Hibernate Core Issue Type: New Feature Components: core Environment: New dialect for Solid database for Hibernate 3.1.3 and Solid 4.5 onwards Reporter: Dave Richardson Attachments: SolidDialect.java I have written a Dialect class for the Solid database (www.solidtech.com) release 4.5 onwards. The SolidDialect is designed to be used with Solid release 4.5 or later. It does however work with Solid release 4.2 if the new features in Solid 4.5 are not used. These are: - referential actions CASCADE, SET NULL, SET DEFAULT, RESTRICT and NO ACTION. - syntax for windowing over the result set: SELECT ... LIMIT ... OFFSET ... - new functions SOUNDEX() and DIFFERENCE() for approximate string comparisons. - automatic removing of constraints, with: DROP TABLE ... CASCADE CONSTRAINTS. I ran the Hibernate test suites with the SolidDialect using Hibernate 3.1.3. As a baseline I also ran the test suite against HSQLDB giving the following results: Tests: 806, unsuccessful: 12, success rate: 98.51% I had initially expected 100%, but the following comment from Gavin King in the Hibernate Forum explains the main reason: "There are some expected failures in the HB3 test suite. Basically, some obscure stuff that we broke going from HB2->HB3 and plan to fix "someday"." The results with the SolidDialect are: Tests: 806, unsuccessful: 36, success rate: 95.53% This compares reasonably well with HSQLDB. Note: some of the Hibernate tests rely on the availability of temporary tables. This is supported by Solid but requires an appropriate license (with support for so-called M-tables). I have sent the dialect and the results of the Hibernate test suite together with a detailed analysis of the results to Solid. They are currently analysing this information. If necessary I can provide a contact person at Solid. I hope the dialect can be included in an official distribution as soon as possible. Test Results Analysis ===================== I analyzed the results and assigned the reason for failure to the following categories: - Feature not currently supported by the Solid database. - Error or non-standard behaviour in the Solid database engine and/or JDBC driver. - Error or missing support in the SolidDialect for Hibernate - Problem with the test case itself - Error in Hibernate itself. This led to the following: #ERRORS CATEGORY 11 Feature not supported by Solid 17 Probable Solid error or non-standard behaviour 0 Problem with SolidDialect 6 Problem with Hibernate test case 2 Hibernate bug 36 Total errors I patched three test cases and corrected one bug in Hibernate to acheive these results: hql.ASTParserLoadingTest: - changed query "from Animal where bodyWeight > 1e-38" to "from Animal where bodyWeight > 1e-12" since Solid supports a maximum precision of 16 and the precision is not the focus of the test. legacy.MasterDetailTest: - There are numerous dialect checks in this test. Many tests are omitted depending on the dialect. For MySQL the transaction isolation level is explicitly set for this test case. The test was patched to do this for Solid too. readonly.ReadOnlyTest: The test case initialises an attribute using: dp.setX( new BigDecimal( 0.1d ).setScale(19, BigDecimal.ROUND_DOWN) ); Solid supports a maximum precision of 16. The test case was patched to use 12 instead of 19. Then only testReadOnlyOnProxiesFailureExpected failed later as it does with HSQLDB with the following diagnostic: org.hibernate.TransientObjectException: Instance was not associated with the session at org.hibernate.engine.StatefulPersistenceContext.setReadOnly( StatefulPersistenceContext.java:1095) at org.hibernate.impl.SessionImpl.setReadOnly(SessionImpl.java:1795) at org.hibernate.test.readonly.ReadOnlyTest. testReadOnlyOnProxiesFailureExpected(ReadOnlyTest.java:50) Research showed that this is a known bug (HHH-1352). I suppose I should submit separate patches for these modified test cases? The Hibernate bug corrected is in the statistics gathering class StatisticsImpl which under certain circumstances causes the test case stats.StatsTestestQueryStatGathering to fail with the following diagnostic: junit.framework.ComparisonFailure: expected:<from Continent> but was:<null> at org.hibernate.test.stats.StatsTest.testQueryStatGathering(StatsTest.java:136) I'll submit a separate patch for this. Here are now some details about the errors: Category: Feature not supported by Solid ---------------------------------------- Of the 11 errors due to unsupported features, 6 are caused by functions in ORDER BY clauses and 3 by missing support for ResultSet.getBlob(). A further error is caused by the test case joinedsubclass.JoinedSubclassTest which leads to the following SQL fragment: where (this_.address, this_.zip, this_.country) in ((?, ?, ?), (?, ?, ?)) This is apparently not currently supported by Solid. Note: this test is skipped for HSQLDB, PostgreSQL, MySQL and DB2. The last error due to an unsupported feature occurs in the test case legacy.FooBarTest.testCollectionsInSelect The test issues the following HQL query: select count(*) from Bar as bar, bar.component.glarch.proxyArray as g where g.id in indices(bar.baz.fooArray) This leads to the following SQL fragment: ...and ((proxyarray2_.tha_key in(select fooarray4_.i from fooArray fooarray4_ where ...)) Solid reports an exception (comparison between incompatible types). proxyarray2_.tha_key is of type VARCHAR(32) and fooarray4_.i is of type INTEGER. Some databases apparently support this comparison. Note: this test is skipped for HSQLDB, MySQL, DB2 and Oracle. Category: Problem with Hibernate test case ------------------------------------------ Of the 6 errors 5 occur in the test hql.HQLTest producing the following diagnostics: Old query translator did not throw an exception, the new one did SQL is not the same as the old SQL New query translator did *NOT* throw an exception, the old one did This was not analyzed any further since the errors also occur with HSQLDB. The other error was in proxy.ProxyTest.testFullyLoadedPCSerialization which produced the following diagnostic: junit.framework.AssertionFailedError: unexpected DP delete count expected:<50> but was:<51> at org.hibernate.test.proxy.ProxyTest.testFullyLoadedPCSerialization(ProxyTest.java:291) This test also fails with HSQLDB with the same diagnostic. Possibly a problem with the test itself. Category: Hibernate bug ----------------------- readonly.ReadOnlyTest.testReadOnlyOnProxiesFailureExpected fails as follows: org.hibernate.TransientObjectException: Instance was not associated with the session at org.hibernate.engine.StatefulPersistenceContext.setReadOnly(StatefulPersistenceContext.java:1095) at org.hibernate.impl.SessionImpl.setReadOnly(SessionImpl.java:1795) at org.hibernate.test.readonly.ReadOnlyTest.testReadOnlyOnProxiesFailureExpected(ReadOnlyTest.java:50) Research showed that this is a known bug (HHH-1352). legacy.SQLLoaderTest.testReturnPropertyComponentRename fails as follows: java.sql.SQLException: [Solid JDBC 04.50.0058] Column not found This test uses a native SQL query defined in the Hibernate mapping file: select id, nickName as n2, name, subName as otherSubName, subName1 from Componentizable The error is in Hibernate trying to access the result set using subName as the column name instead of the defined alias otherSubName. Research showed that this is a known bug (HHH-1515). I hope the dialect meets with your approval and look forward to it being included in the official distribution!