Author: steve.ebersole(a)jboss.com Date: 2010-07-21 15:03:33 -0400 (Wed, 21 Jul 2010) New Revision: 19995 Modified: core/branches/Branch_3_5/core/src/main/java/org/hibernate/dialect/HSQLDialect.java core/branches/Branch_3_5/parent/pom.xml Log: HHH-5381 - HSQLDB new dialect (Fred Toussi) Modified: core/branches/Branch_3_5/core/src/main/java/org/hibernate/dialect/HSQLDialect.java =================================================================== --- core/branches/Branch_3_5/core/src/main/java/org/hibernate/dialect/HSQLDialect.java 2010-07-21 17:31:09 UTC (rev 19994) +++ core/branches/Branch_3_5/core/src/main/java/org/hibernate/dialect/HSQLDialect.java 2010-07-21 19:03:33 UTC (rev 19995) @@ -1,10 +1,10 @@ /* * Hibernate, Relational Persistence for Idiomatic Java * - * Copyright (c) 2008, Red Hat Middleware LLC or third-party contributors as + * Copyright (c) 2010, Red Hat Inc. or third-party contributors as * indicated by the @author tags or express copyright attribution * statements applied by the authors. All third-party contributions are - * distributed under license by Red Hat Middleware LLC. + * distributed under license by Red Hat Inc. * * This copyrighted material is made available to anyone wishing to use, modify, * copy, or redistribute it subject to the terms and conditions of the GNU @@ -20,7 +20,6 @@ * Free Software Foundation, Inc. * 51 Franklin Street, Fifth Floor * Boston, MA 02110-1301 USA - * */ package org.hibernate.dialect; @@ -32,10 +31,11 @@ import org.hibernate.LockMode; import org.hibernate.StaleObjectStateException; import org.hibernate.JDBCException; -import org.hibernate.dialect.function.AvgWithArgumentCastFunction; import org.hibernate.engine.SessionImplementor; import org.hibernate.persister.entity.Lockable; import org.hibernate.cfg.Environment; +import org.hibernate.dialect.function.AvgWithArgumentCastFunction; +import org.hibernate.dialect.function.SQLFunctionTemplate; import org.hibernate.dialect.function.NoArgSQLFunction; import org.hibernate.dialect.function.StandardSQLFunction; import org.hibernate.dialect.function.VarArgsSQLFunction; @@ -43,29 +43,55 @@ import org.hibernate.exception.JDBCExceptionHelper; import org.hibernate.exception.TemplatedViolatedConstraintNameExtracter; import org.hibernate.exception.ViolatedConstraintNameExtracter; +import org.hibernate.util.ReflectHelper; + import org.slf4j.Logger; import org.slf4j.LoggerFactory; /** - * An SQL dialect compatible with HSQLDB (Hypersonic SQL). + * An SQL dialect compatible with HSQLDB (HyperSQL). * <p/> * Note this version supports HSQLDB version 1.8 and higher, only. + * <p/> + * Enhancements to version 3.5.0 GA to provide basic support for both HSQLDB 1.8.x and 2.0 + * Should work with Hibernate 3.2 and later * * @author Christoph Sturm * @author Phillip Baird + * @author Fred Toussi */ public class HSQLDialect extends Dialect { + private static final Logger log = LoggerFactory.getLogger( HSQLDialect.class ); + /** + * version is 18 for 1.8 or 20 for 2.0 + */ + private int hsqldbVersion = 18; + + public HSQLDialect() { super(); + + try { + Class props = ReflectHelper.classForName( "org.hsqldb.persist.HsqlDatabaseProperties" ); + String versionString = (String) props.getDeclaredField( "THIS_VERSION" ).get( null ); + + hsqldbVersion = Integer.parseInt( versionString.substring( 0, 1 ) ) * 10; + hsqldbVersion += Integer.parseInt( versionString.substring( 2, 3 ) ); + } + catch ( Throwable e ) { + // must be a very old version + } + registerColumnType( Types.BIGINT, "bigint" ); registerColumnType( Types.BINARY, "binary" ); registerColumnType( Types.BIT, "bit" ); - registerColumnType( Types.CHAR, "char(1)" ); + registerColumnType( Types.CHAR, "char($l)" ); registerColumnType( Types.DATE, "date" ); - registerColumnType( Types.DECIMAL, "decimal" ); + + registerColumnType( Types.DECIMAL, "decimal($p,$s)" ); registerColumnType( Types.DOUBLE, "double" ); registerColumnType( Types.FLOAT, "float" ); registerColumnType( Types.INTEGER, "integer" ); @@ -77,12 +103,23 @@ registerColumnType( Types.TIMESTAMP, "timestamp" ); registerColumnType( Types.VARCHAR, "varchar($l)" ); registerColumnType( Types.VARBINARY, "varbinary($l)" ); - registerColumnType( Types.NUMERIC, "numeric" ); + + if ( hsqldbVersion < 20 ) { + registerColumnType( Types.NUMERIC, "numeric" ); + } + else { + registerColumnType( Types.NUMERIC, "numeric($p,$s)" ); + } + //HSQL has no Blob/Clob support .... but just put these here for now! - registerColumnType( Types.BLOB, "longvarbinary" ); - registerColumnType( Types.CLOB, "longvarchar" ); - registerColumnType( Types.LONGVARBINARY, "longvarbinary" ); - registerColumnType( Types.LONGVARCHAR, "longvarchar" ); + if ( hsqldbVersion < 20 ) { + registerColumnType( Types.BLOB, "longvarbinary" ); + registerColumnType( Types.CLOB, "longvarchar" ); + } + else { + registerColumnType( Types.BLOB, "blob" ); + registerColumnType( Types.CLOB, "clob" ); + } registerFunction( "avg", new AvgWithArgumentCastFunction( "double" ) ); @@ -99,13 +136,16 @@ registerFunction( "space", new StandardSQLFunction( "space", Hibernate.STRING ) ); registerFunction( "rawtohex", new StandardSQLFunction( "rawtohex" ) ); registerFunction( "hextoraw", new StandardSQLFunction( "hextoraw" ) ); - + registerFunction( "str", new SQLFunctionTemplate( Hibernate.STRING, "cast(?1 as varchar(24))" ) ); registerFunction( "user", new NoArgSQLFunction( "user", Hibernate.STRING ) ); registerFunction( "database", new NoArgSQLFunction( "database", Hibernate.STRING ) ); + registerFunction( "sysdate", new NoArgSQLFunction( "sysdate", Hibernate.DATE, false ) ); registerFunction( "current_date", new NoArgSQLFunction( "current_date", Hibernate.DATE, false ) ); registerFunction( "curdate", new NoArgSQLFunction( "curdate", Hibernate.DATE ) ); - registerFunction( "current_timestamp", new NoArgSQLFunction( "current_timestamp", Hibernate.TIMESTAMP, false ) ); + registerFunction( + "current_timestamp", new NoArgSQLFunction( "current_timestamp", Hibernate.TIMESTAMP, false ) + ); registerFunction( "now", new NoArgSQLFunction( "now", Hibernate.TIMESTAMP ) ); registerFunction( "current_time", new NoArgSQLFunction( "current_time", Hibernate.TIME, false ) ); registerFunction( "curtime", new NoArgSQLFunction( "curtime", Hibernate.TIME ) ); @@ -116,7 +156,7 @@ registerFunction( "month", new StandardSQLFunction( "month", Hibernate.INTEGER ) ); registerFunction( "year", new StandardSQLFunction( "year", Hibernate.INTEGER ) ); registerFunction( "week", new StandardSQLFunction( "week", Hibernate.INTEGER ) ); - registerFunction( "quater", new StandardSQLFunction( "quater", Hibernate.INTEGER ) ); + registerFunction( "quarter", new StandardSQLFunction( "quarter", Hibernate.INTEGER ) ); registerFunction( "hour", new StandardSQLFunction( "hour", Hibernate.INTEGER ) ); registerFunction( "minute", new StandardSQLFunction( "minute", Hibernate.INTEGER ) ); registerFunction( "second", new StandardSQLFunction( "second", Hibernate.INTEGER ) ); @@ -173,7 +213,7 @@ } public String getIdentityInsertString() { - return "null"; + return hsqldbVersion < 20 ? "null" : "default"; } public boolean supportsLockTimeouts() { @@ -193,14 +233,25 @@ } public String getLimitString(String sql, boolean hasOffset) { - return new StringBuffer( sql.length() + 10 ) - .append( sql ) - .insert( sql.toLowerCase().indexOf( "select" ) + 6, hasOffset ? " limit ? ?" : " top ?" ) - .toString(); + if ( hsqldbVersion < 20 ) { + return new StringBuffer( sql.length() + 10 ) + .append( sql ) + .insert( + sql.toLowerCase().indexOf( "select" ) + 6, + hasOffset ? " limit ? ?" : " top ?" + ) + .toString(); + } + else { + return new StringBuffer( sql.length() + 20 ) + .append( sql ) + .append( hasOffset ? " offset ? limit ?" : " limit ?" ) + .toString(); + } } public boolean bindLimitParametersFirst() { - return true; + return hsqldbVersion < 20; } public boolean supportsIfExistsAfterTableName() { @@ -208,7 +259,7 @@ } public boolean supportsColumnCheck() { - return false; + return hsqldbVersion >= 20; } public boolean supportsSequences() { @@ -241,10 +292,10 @@ } public ViolatedConstraintNameExtracter getViolatedConstraintNameExtracter() { - return EXTRACTER; + return hsqldbVersion < 20 ? EXTRACTER_18 : EXTRACTER_20; } - private static ViolatedConstraintNameExtracter EXTRACTER = new TemplatedViolatedConstraintNameExtracter() { + private static ViolatedConstraintNameExtracter EXTRACTER_18 = new TemplatedViolatedConstraintNameExtracter() { /** * Extract the name of the violated constraint from the given SQLException. @@ -274,40 +325,260 @@ } else if ( errorCode == -177 ) { constraintName = extractUsingTemplate( - "Integrity constraint violation - no parent ", " table:", sqle.getMessage() + "Integrity constraint violation - no parent ", " table:", + sqle.getMessage() ); } - return constraintName; } }; /** - * HSQL does not really support temp tables; just take advantage of the - * fact that it is a single user db... + * HSQLDB 2.0 messages have changed + * messages may be localized - therefore use the common, non-locale element " table: " */ + private static ViolatedConstraintNameExtracter EXTRACTER_20 = new TemplatedViolatedConstraintNameExtracter() { + + public String extractConstraintName(SQLException sqle) { + String constraintName = null; + + int errorCode = JDBCExceptionHelper.extractErrorCode( sqle ); + + if ( errorCode == -8 ) { + constraintName = extractUsingTemplate( + "; ", " table: ", sqle.getMessage() + ); + } + else if ( errorCode == -9 ) { + constraintName = extractUsingTemplate( + "; ", " table: ", sqle.getMessage() + ); + } + else if ( errorCode == -104 ) { + constraintName = extractUsingTemplate( + "; ", " table: ", sqle.getMessage() + ); + } + else if ( errorCode == -177 ) { + constraintName = extractUsingTemplate( + "; ", " table: ", sqle.getMessage() + ); + } + return constraintName; + } + }; + + public String getSelectClauseNullString(int sqlType) { + String literal; + switch ( sqlType ) { + case Types.VARCHAR: + case Types.CHAR: + literal = "cast(null as varchar(100))"; + break; + case Types.DATE: + literal = "cast(null as date)"; + break; + case Types.TIMESTAMP: + literal = "cast(null as timestamp)"; + break; + case Types.TIME: + literal = "cast(null as time)"; + break; + default: + literal = "cast(null as int)"; + } + return literal; + } + + + // temporary table support ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + // Hibernate uses this information for temporary tables that it uses for its own operations + // therefore the appropriate strategy is taken with different versions of HSQLDB + + // All versions of HSQLDB support GLOBAL TEMPORARY tables where the table + // definition is shared by all users but data is private to the session + // HSQLDB 2.0 also supports session-based LOCAL TEMPORARY tables where + // the definition and data is private to the session and table declaration + // can happen in the middle of a transaction + + /** + * Does this dialect support temporary tables? + * + * @return True if temp tables are supported; false otherwise. + */ public boolean supportsTemporaryTables() { return true; } + /** + * With HSQLDB 2.0, the table name is qualified with MODULE to assist the drop + * statement (in-case there is a global name beginning with HT_) + * + * @param baseTableName The table name from which to base the temp table name. + * + * @return The generated temp table name. + */ + public String generateTemporaryTableName(String baseTableName) { + if ( hsqldbVersion < 20 ) { + return "HT_" + baseTableName; + } + else { + return "MODULE.HT_" + baseTableName; + } + } + + /** + * Command used to create a temporary table. + * + * @return The command used to create a temporary table. + */ + public String getCreateTemporaryTableString() { + if ( hsqldbVersion < 20 ) { + return "create global temporary table"; + } + else { + return "declare local temporary table"; + } + } + + /** + * No fragment is needed if data is not needed beyond commit, otherwise + * should add "on commit preserve rows" + * + * @return Any required postfix. + */ + public String getCreateTemporaryTablePostfix() { + return ""; + } + + /** + * Command used to drop a temporary table. + * + * @return The command used to drop a temporary table. + */ + public String getDropTemporaryTableString() { + return "drop table"; + } + + /** + * Different behaviour for GLOBAL TEMPORARY (1.8) and LOCAL TEMPORARY (2.0) + * <p/> + * Possible return values and their meanings:<ul> + * <li>{@link Boolean#TRUE} - Unequivocally, perform the temporary table DDL + * in isolation.</li> + * <li>{@link Boolean#FALSE} - Unequivocally, do <b>not</b> perform the + * temporary table DDL in isolation.</li> + * <li><i>null</i> - defer to the JDBC driver response in regards to + * {@link java.sql.DatabaseMetaData#dataDefinitionCausesTransactionCommit()}</li> + * </ul> + * + * @return see the result matrix above. + */ + public Boolean performTemporaryTableDDLInIsolation() { + if ( hsqldbVersion < 20 ) { + return Boolean.TRUE; + } + else { + return Boolean.FALSE; + } + } + + /** + * Do we need to drop the temporary table after use? + * + * todo - clarify usage by Hibernate + * Version 1.8 GLOBAL TEMPORARY table definitions persist beyond the end + * of the session (data is cleared). If there are not too many such tables, + * perhaps we can avoid dropping them and reuse the table next time? + * + * @return True if the table should be dropped. + */ + public boolean dropTemporaryTableAfterUse() { + return true; + } + + // current timestamp support ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + + /** + * HSQLDB 1.8.x requires CALL CURRENT_TIMESTAMP but this should not + * be treated as a callable statement. It is equivalent to + * "select current_timestamp from dual" in some databases. + * HSQLDB 2.0 also supports VALUES CURRENT_TIMESTAMP + * + * @return True if the current timestamp can be retrieved; false otherwise. + */ public boolean supportsCurrentTimestampSelection() { + return true; + } + + /** + * Should the value returned by {@link #getCurrentTimestampSelectString} + * be treated as callable. Typically this indicates that JDBC escape + * syntax is being used... + * + * @return True if the {@link #getCurrentTimestampSelectString} return + * is callable; false otherwise. + */ + public boolean isCurrentTimestampSelectStringCallable() { return false; } + /** + * Retrieve the command used to retrieve the current timestamp from the + * database. + * + * @return The command. + */ + public String getCurrentTimestampSelectString() { + return "call current_timestamp"; + } + + /** + * The name of the database-specific SQL function for retrieving the + * current timestamp. + * + * @return The function name. + */ + public String getCurrentTimestampSQLFunctionName() { + // the standard SQL function name is current_timestamp... + return "current_timestamp"; + } + + /** + * For HSQLDB 2.0, this is a copy of the base class implementation. + * For HSQLDB 1.8, only READ_UNCOMMITTED is supported. + * + * @param lockable The persister for the entity to be locked. + * @param lockMode The type of lock to be acquired. + * + * @return The appropriate locking strategy. + * + * @since 3.2 + */ public LockingStrategy getLockingStrategy(Lockable lockable, LockMode lockMode) { - // HSQLDB only supports READ_UNCOMMITTED transaction isolation - if ( lockMode==LockMode.PESSIMISTIC_FORCE_INCREMENT) { - return new PessimisticForceIncrementLockingStrategy( lockable, lockMode); + if ( lockMode == LockMode.PESSIMISTIC_FORCE_INCREMENT ) { + return new PessimisticForceIncrementLockingStrategy( lockable, lockMode ); } - else if ( lockMode==LockMode.OPTIMISTIC) { - return new OptimisticLockingStrategy( lockable, lockMode); + else if ( lockMode == LockMode.PESSIMISTIC_WRITE ) { + return new PessimisticWriteSelectLockingStrategy( lockable, lockMode ); } - else if ( lockMode==LockMode.OPTIMISTIC_FORCE_INCREMENT) { - return new OptimisticForceIncrementLockingStrategy( lockable, lockMode); + else if ( lockMode == LockMode.PESSIMISTIC_READ ) { + return new PessimisticReadSelectLockingStrategy( lockable, lockMode ); } - return new ReadUncommittedLockingStrategy( lockable, lockMode ); - + else if ( lockMode == LockMode.OPTIMISTIC ) { + return new OptimisticLockingStrategy( lockable, lockMode ); + } + else if ( lockMode == LockMode.OPTIMISTIC_FORCE_INCREMENT ) { + return new OptimisticForceIncrementLockingStrategy( lockable, lockMode ); + } + + if ( hsqldbVersion < 20 ) { + return new ReadUncommittedLockingStrategy( lockable, lockMode ); + } + else { + return new SelectLockingStrategy( lockable, lockMode ); + } } public static class ReadUncommittedLockingStrategy extends SelectLockingStrategy { @@ -324,6 +595,9 @@ } } + public boolean supportsCommentOn() { + return true; + } // Overridden informational metadata ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ @@ -331,6 +605,46 @@ return false; } + /** + * todo - needs usage clarification + * + * If the SELECT statement is always part of a UNION, then the type of + * parameter is resolved by v. 2.0, but not v. 1.8 (assuming the other + * SELECT in the UNION has a column reference in the same position and + * can be type-resolved). + * + * On the other hand if the SELECT statement is isolated, all versions of + * HSQLDB require casting for "select ? from .." to work. + * + * @return True if select clause parameter must be cast()ed + * + * @since 3.2 + */ + public boolean requiresCastingOfParametersInSelectClause() { + return true; + } + + /** + * For the underlying database, is READ_COMMITTED isolation implemented by + * forcing readers to wait for write locks to be released? + * + * @return True if writers block readers to achieve READ_COMMITTED; false otherwise. + */ + public boolean doesReadCommittedCauseWritersToBlockReaders() { + return hsqldbVersion >= 20; + } + + /** + * For the underlying database, is REPEATABLE_READ isolation implemented by + * forcing writers to wait for read locks to be released? + * + * @return True if readers block writers to achieve REPEATABLE_READ; false otherwise. + */ + public boolean doesRepeatableReadCauseReadersToBlockWriters() { + return hsqldbVersion >= 20; + } + + public boolean supportsLobValueChangePropogation() { return false; } Modified: core/branches/Branch_3_5/parent/pom.xml =================================================================== --- core/branches/Branch_3_5/parent/pom.xml 2010-07-21 17:31:09 UTC (rev 19994) +++ core/branches/Branch_3_5/parent/pom.xml 2010-07-21 19:03:33 UTC (rev 19995) @@ -539,6 +539,11 @@ <artifactId>hibernate-validator</artifactId> <version>4.0.2.GA</version> </dependency> + <dependency> + <groupId>org.hsqldb</groupId> + <artifactId>hsqldb</artifactId> + <version>1.8.0.10</version> + </dependency> </dependencies> </dependencyManagement> @@ -567,9 +572,8 @@ </activation> <dependencies> <dependency> - <groupId>hsqldb</groupId> + <groupId>org.hsqldb</groupId> <artifactId>hsqldb</artifactId> - <version>1.8.0.2</version> <scope>test</scope> </dependency> </dependencies>