From Ingres Community Wiki

Contents 1 Ingres Hibernate Dialects 1.1 IngresDialect 1.2 Ingres9Dialect 1.2.1 Date/Time functions 1.2.2 "SELECT...FOR UPDATE" 1.2.3 Locking issues 1.2.4 Verify methods overridden by the Ingres Dialect 1.2.5 SELECT LIMIT 1.3 Ingres10Dialect 1.4 Hibernate Test Suite Summaries

Ingres Hibernate Dialects

IngresDialect

• Recommended for versions of Ingres up to and including 9.2 and Hibernate versions prior to 3.5.0.

Ingres9Dialect

• Recommended for Ingres 9.3.x and Hibernate 3.5.x

Some of the limitations that are listed as part of the certification are no longer relevant as Ingres has improved in many areas. Other changes in Ingres make changes in the Ingres dialect possible that improves the usability and performance with Hibernate. Reflecting these developments in the Ingres dialect will possibly prevent the dialect to operate with older versions of Ingres. This can be avoided by providing different dialects for the different Ingres versions that are currently available. The idea is to have a base Ingres dialect that is sufficient for all supported Ingres versions and several specialized dialects that cover the features that were introduced in new Ingres releases.

Date/Time functions

The functions current_date, current_time, current_timestamp are in the current dialect defined as follows:

registerFunction( "current_time", new NoArgSQLFunction( "date('now')", Hibernate.TIMESTAMP, false ) );
registerFunction( "current_timestamp", new NoArgSQLFunction( "date('now')", Hibernate.TIMESTAMP, false ) );
registerFunction( "current_date", new NoArgSQLFunction( "date('now')", Hibernate.TIMESTAMP, false ) );

Because Ingres now supports current_date, current_time, current_timestamp, the function definitions can be changed to use the available functionality. With this change the result type of the functions can also be modified to the appropriate type.

registerFunction( "current_time", new NoArgSQLFunction( "current_time", Hibernate.TIME, false ) );
registerFunction( "current_timestamp", new NoArgSQLFunction( "current_timestamp", Hibernate.TIMESTAMP, false ) );
registerFunction( "current_date", new NoArgSQLFunction( "current_date", Hibernate.DATE, false ) );

This change applies to dialects for Ingres version *TBD* and above.

"SELECT...FOR UPDATE"

Hibernate uses "SELECT...FOR UPDATE" statements to lock data for updates. Ingres supports this statement type with the following limitations:

  * Must refer to a single table
  * No aggregate functions, union clause, group by clause, having clause, distinct
  * The maximum of one variable at the outermost query level

Hibernate does not take care of the above limitations, this leads to the problem that persisting objects might fail if an invalid statement was generated.

A solution in that scenario is to disable the use of "FOR UPDATE" which can be done by returning an empty string in the method getForUpdateString().This will work as long as Ingres is *not* configured with readlock=nolock otherwise concurrent database reads may return invalid results.

Locking issues

When running the Hibernate test suites several test cases will fail due to locking issues. The tests will finish without error when overriding the methods doesReadCommittedCauseWritersToBlockReaders and doesRepeatableReadCauseReadersToBlockWriters to return true and when the transaction isolation is set to TRANSACTION_READ_COMMITTED.

Verify methods overridden by the Ingres Dialect

A comparison of the Ingres Dialect with the latest base class (Dialect) showed that there are several methods that have been renamed in the base class. It should be investigated in how far these changes influence the functionality of the dialect.

SELECT LIMIT

During the [[LiferayCetification][Liferay Certification]] occurred an issue with queries that limits the number of result columns came up. The generated statements looked similar to this one "SELE first 20CT...". The method getLimitString(String, int, int) was identified to cause this problem by ignoring leading whitespaces. The fix that is available on the wiki page works, in addition the following code will work (it has not yet been tested) with an improved performance.

public String getLimitString(String querySelect, int offset, int limit) {
    if (offset > 0) {
        throw new UnsupportedOperationException("offset not supported");
    }
    return new StringBuffer(querySelect.length() + 16).append(
            querySelect.trim()).insert(6, " first " + limit).toString();
}

Ingres10Dialect

• Recommended for Ingres 10.0.x and Hibernate versions 3.5.5 and above

HHH-5435	Patch adds support for identity columns
HHH-5213	Patch adds native boolean type support
HHH-4931	Added dialects for Ingres 9.3 and Ingres 10 to the existing Ingres 9.2 dialect

Hibernate Test Suite Summaries

• Hibernate Test suite testing
  • Hibernate error details
  • Hibernate error details (wide view)