/**********************************************************************
Copyright (c) 2003 Erik Bengtson and others. All rights reserved.
Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
Contributors:
2004 Andy Jefferson - updated to specify the transaction isolation level limitation
2004 Andy Jefferson - changed to use CREATE TABLE of DatabaseAdapter
2004 Andy Jefferson - added MappingManager
2007 R Scott - Sequence support
...
**********************************************************************/
package org.jpox.store.rdbms.adapter;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import org.jpox.UserTransaction;
import org.jpox.exceptions.JPOXUserException;
import org.jpox.store.mapped.DatastoreContainerObject;
import org.jpox.store.mapped.DatastoreIdentifier;
import org.jpox.store.mapped.IdentifierFactory;
import org.jpox.store.mapped.expression.LogicSetExpression;
import org.jpox.store.mapped.expression.NumericExpression;
import org.jpox.store.mapped.expression.QueryExpression;
import org.jpox.store.mapped.expression.ScalarExpression;
import org.jpox.store.mapped.expression.StringExpression;
import org.jpox.store.mapped.expression.TableExprAsJoins;
import org.jpox.store.rdbms.Column;
import org.jpox.store.rdbms.key.ForeignKey;
import org.jpox.store.rdbms.key.PrimaryKey;
import org.jpox.store.rdbms.table.Table;
import org.jpox.store.rdbms.typeinfo.HSQLTypeInfo;
import org.jpox.store.rdbms.typeinfo.TypeInfo;
/**
* Provides methods for adapting SQL language elements to the Hypersonic SQL Server database.
*
* @version $Revision: 1.50 $
*/
public class HSQLAdapter extends DatabaseAdapter
{
/**
* Constructs a Hypersonic SQL adapter based on the given JDBC metadata.
* @param metadata the database metadata.
*/
public HSQLAdapter(DatabaseMetaData metadata)
{
super(metadata);
// Add on any missing JDBC types
TypeInfo ti = new HSQLTypeInfo("LONGVARCHAR",
(short)Types.CLOB,
2147483647,
"'",
"'",
null,
1,
true,
(short)3,
false,
false,
false,
"LONGVARCHAR",
(short)0,
(short)0,
0);
addTypeInfo((short)Types.CLOB, ti, true);
ti = new HSQLTypeInfo("LONGVARBINARY",
(short)Types.BLOB,
2147483647,
"'",
"'",
null,
1,
false,
(short)3,
false,
false,
false,
"LONGVARBINARY",
(short)0,
(short)0,
0);
addTypeInfo((short)Types.BLOB, ti, true);
}
/**
* Accessor for the vendor ID for this adapter.
* @return The vendor ID
*/
public String getVendorID()
{
return "hsql";
}
/**
* Accessor for the maximum table name length permitted on this datastore.
* @return Max table name length
*/
public int getMaxTableNameLength()
{
return SQLConstants.MAX_IDENTIFIER_LENGTH;
}
/**
* Accessor for the maximum constraint name length permitted on this
* datastore.
* @return Max constraint name length
*/
public int getMaxConstraintNameLength()
{
return SQLConstants.MAX_IDENTIFIER_LENGTH;
}
/**
* Accessor for the maximum index name length permitted on this datastore.
* @return Max index name length
*/
public int getMaxIndexNameLength()
{
return SQLConstants.MAX_IDENTIFIER_LENGTH;
}
/**
* Accessor for the maximum column name length permitted on this datastore.
* @return Max column name length
*/
public int getMaxColumnNameLength()
{
return SQLConstants.MAX_IDENTIFIER_LENGTH;
}
/**
* Accessor for the SQL statement to add a column to a table.
* @param table The table
* @param col The column
* @return The SQL necessary to add the column
*/
public String getAddColumnStatement(DatastoreContainerObject table, Column col)
{
return "ALTER TABLE " + table.toString() + " ADD COLUMN " + col.getSQLDefinition();
}
/**
* Method to return the SQL to append to the SELECT clause of a SELECT statement to handle
* restriction of ranges using the LIMUT keyword.
* @param offset The offset to return from
* @param count The number of items to return
* @return The SQL to append to allow for ranges using LIMIT.
*/
public String getRangeByLimitSelectClause(long offset, long count)
{
if (offset >= 0 && count > 0)
{
return " LIMIT " + offset + " " + count + " ";
}
else if (offset <= 0 && count > 0)
{
return " LIMIT 0 " + count + " ";
}
else if (offset >= 0 && count < 0)
{
// HSQL doesnt allow just offset so use Long.MAX_VALUE as count
return " LIMIT " + offset + "," + Long.MAX_VALUE;
}
else
{
return "";
}
}
/**
* Factory for TypeInfo objects.
* @param rs The ResultSet from DatabaseMetaData.getTypeInfo().
* @return A TypeInfo object.
*/
public TypeInfo newTypeInfo(ResultSet rs)
{
return new HSQLTypeInfo(rs);
}
/**
* Accessor for whether the adapter supports the transaction isolation level
* HSQL 1.7.0 does not support Connection.setTransactionIsolation(isolationLevel);
* Up to and including 1.7.1, HSQLDB supports only TRANSACTION_READ_UNCOMMITTED.
* @param isolationLevel the isolation level
* @return Whether the transaction isolation level setting is supported.
*/
public boolean supportsTransactionIsolationLevel(int isolationLevel)
{
if (isolationLevel == UserTransaction.TRANSACTION_READ_UNCOMMITTED)
{
return true;
}
return false;
}
/**
* Accessor for the "required" transaction isolation level if it has to be a certain value
* for this adapter.
* @return Transaction isolation level (-1 implies no restriction)
*/
public int getRequiredTransactionIsolationLevel()
{
return UserTransaction.TRANSACTION_READ_UNCOMMITTED;
}
/**
* Whether the datastore supports specification of the primary key in CREATE
* TABLE statements.
* @return Whetehr it allows "PRIMARY KEY ..."
*/
public boolean supportsPrimaryKeyInCreateStatements()
{
return true;
}
/**
* Whether the datastore supports {@link Statement#getGeneratedKeys()}
* @return true if it is supported
*/
public boolean supportsStatementGetGeneratedKeys()
{
return false;
}
/**
* Whether this datastore supports batching of statements.
* @return whether we can use statement batching
*/
public boolean supportsStatementBatching()
{
// HSQL doesnt support this even though it says that it does
// Typically gives a message "failed batch" ... really informative.
return false;
}
/**
* Accessor for whether the SQL extensions CUBE, ROLLUP are supported.
* @return Whether the SQL extensions CUBE, ROLLUP are supported.
*/
public boolean supportsAnalysisMethods()
{
return false;
}
/**
* Accessor for the Schema Name for this datastore.
* HSQL 1.7.0 does not support schemas (catalog)
*
* @param conn Connection to the datastore
* @return The schema name
* @throws SQLException Thrown if error occurs in determining the schema name.
*/
public String getSchemaName(Connection conn)
throws SQLException
{
return "";
}
/**
* HSQL 1.7.0 does not support ALTER TABLE to define a primary key
* @param pk An object describing the primary key.
* @param factory Identifier factory
* @return The PK statement
*/
public String getAddPrimaryKeyStatement(PrimaryKey pk, IdentifierFactory factory)
{
return null;
}
/**
* Returns the appropriate SQL to drop the given table.
* It should return something like:
* <blockquote><pre>
* DROP TABLE FOO
* </pre></blockquote>
*
* @param table The table to drop.
* @return The text of the SQL statement.
*/
public String getDropTableStatement(DatastoreContainerObject table)
{
return "DROP TABLE " + table.toString();
}
/**
* Return a new TableExpression appropriate to MySQL. MySQL does not
* support the TableExprAsSubjoins so instead we use TableExprAsJoins.
* @param qs The QueryStatement to add the expression to
* @param table The table in the expression
* @param rangeVar range variable to assign to the expression.
* @return The expression.
*/
public LogicSetExpression newTableExpression(QueryExpression qs, DatastoreContainerObject table, DatastoreIdentifier rangeVar)
{
return new TableExprAsJoins(qs, table, rangeVar);
}
/**
* Whether we support deferred constraints in keys.
* @return whether we support deferred constraints in keys.
*/
public boolean supportsDeferredConstraints()
{
return false;
}
/**
* Whether this datastore supports the specified foreign key update action
* @param action The update action
* @return Whether it is supported
*/
public boolean supportsForeignKeyUpdateAction(ForeignKey.FKAction action)
{
// HSQL before 1.7.* doesn't support foreign keys
if (datastoreMajorVersion < 1 || (datastoreMajorVersion == 1 && datastoreMinorVersion < 7))
{
return false;
}
if (action.equals(ForeignKey.CASCADE_ACTION) ||
action.equals(ForeignKey.DEFAULT_ACTION) ||
action.equals(ForeignKey.NULL_ACTION))
{
return true;
}
return false;
}
/**
* Whether this datastore supports the specified foreign key delete action
* @param action The delete action
* @return Whether it is supported
*/
public boolean supportsForeignKeyDeleteAction(ForeignKey.FKAction action)
{
// HSQL before 1.7.* doesn't support foreign keys
if (datastoreMajorVersion < 1 || (datastoreMajorVersion == 1 && datastoreMinorVersion < 7))
{
return false;
}
if (action.equals(ForeignKey.CASCADE_ACTION) ||
action.equals(ForeignKey.DEFAULT_ACTION) ||
action.equals(ForeignKey.NULL_ACTION))
{
return true;
}
return false;
}
/**
* Whether we support autoincrementing fields.
* @return whether we support autoincrementing fields.
*/
public boolean supportsIdentityFields()
{
return true;
}
/**
* Accessor for the auto-increment sql statement for this datastore.
* @param table Name of the table that the autoincrement is for
* @param columnName Name of the column that the autoincrement is for
* @return The statement for getting the latest auto-increment key
*/
public String getAutoIncrementStmt(Table table, String columnName)
{
return "CALL IDENTITY()";
}
/**
* Accessor for the auto-increment keyword for generating DDLs (CREATE TABLEs...).
* @return The keyword for a column using auto-increment
*/
public String getAutoIncrementKeyword()
{
return "IDENTITY";
}
/**
* Method to retutn the INSERT statement to use when inserting into a table that has no
* columns specified. This is the case when we have a single column in the table and that column
* is autoincrement/identity (and so is assigned automatically in the datastore).
* @param table The table
* @return The INSERT statement
*/
public String getInsertStatementForNoColumns(Table table)
{
return "INSERT INTO " + table.toString() + " VALUES (null)";
}
/**
* Whether to allow Unique statements in the section of CREATE TABLE after the
* column definitions.
* @see org.jpox.store.rdbms.adapter.DatabaseAdapter#supportsUniqueConstraintsInEndCreateStatements()
*/
public boolean supportsUniqueConstraintsInEndCreateStatements()
{
return true;
}
/**
* Whether this datastore supports the use of CHECK in CREATE TABLE
* statements (DDL). HSQL doesn't seem to support this.
* @return whether we can use CHECK in CREATE TABLE.
*/
public boolean supportsCheckInCreateStatements()
{
return false;
}
/**
* Whether this datastore supports the use of CHECK after the column
* definitions in CREATE TABLE statements (DDL).
* e.g.
* CREATE TABLE XXX
* (
* COL_A int,
* COL_B char(1),
* PRIMARY KEY (COL_A),
* CHECK (COL_B IN ('Y','N'))
* )
* @return whether we can use CHECK after the column definitions in CREATE TABLE.
*/
public boolean supportsCheckConstraintsInEndCreateStatements()
{
// HSQL Introduced in v 1.7.2 the use of CHECK, but ONLY as statements
// at the end of the CREATE TABLE statement. We currently don't support
// anything there other than primary key.
// This was introduced in version 1.7.2
if (datastoreMajorVersion < 1 ||
(datastoreMajorVersion == 1 && datastoreMinorVersion < 7) ||
(datastoreMajorVersion == 1 && datastoreMinorVersion == 7 && datastoreRevisionVersion < 2))
{
return false;
}
else
{
return true;
}
}
/**
* Accessor for whether the specified type is allow to be part of a PK.
* @param datatype The JDBC type
* @return Whether it is permitted in the PK
*/
public boolean isValidPrimaryKeyType(int datatype)
{
if (datatype == Types.BLOB ||
datatype == Types.CLOB ||
datatype == Types.LONGVARBINARY ||
datatype == Types.OTHER ||
datatype == Types.LONGVARCHAR)
{
return false;
}
return true;
}
/**
* Method to generate a modulus expression. The binary % operator is said to
* yield the remainder of its operands from an implied division; the
* left-hand operand is the dividend and the right-hand operand is the
* divisor. This returns MOD(expr1, expr2).
* @param operand1 the left expression
* @param operand2 the right expression
* @return The Expression for modulus
*/
public NumericExpression modOperator(ScalarExpression operand1, ScalarExpression operand2)
{
ArrayList args = new ArrayList();
args.add(operand1);
args.add(operand2);
return new NumericExpression("MOD", args);
}
/**
* Returns the appropriate SQL expression for the java query "trim" method.
* It should return something like:
* <pre>LTRIM(RTRIM(str))</pre>
* @param str The first argument to the trim() method.
* @param leading Whether to trim leading spaces
* @param trailing Whether to trim trailing spaces
* @return The text of the SQL expression.
*/
public StringExpression trimMethod(StringExpression str, boolean leading, boolean trailing)
{
ArrayList args = new ArrayList();
args.add(str);
if (leading && trailing)
{
StringExpression strExpr = new StringExpression("RTRIM", args);
args.clear();
args.add(strExpr);
return new StringExpression("LTRIM", args);
}
else if (leading)
{
return new StringExpression("LTRIM", args);
}
else if (trailing)
{
return new StringExpression("RTRIM", args);
}
return str;
}
/**
* Accessor for a statement that will return the statement to use to get the datastore date.
* @return SQL statement to get the datastore date
*/
public String getDatastoreDateStatement()
{
return "CALL NOW()";
}
// ---------------------------- Sequence Support ---------------------------
/**
* Whether we support sequences.
* @return whether we support sequences.
*/
public boolean supportsSequences()
{
return true;
}
/**
* Accessor for the sequence statement to create the sequence.
* @param sequence_name Name of the sequence
* @param min Minimum value for the sequence
* @param max Maximum value for the sequence
* @param start Start value for the sequence
* @param increment Increment value for the sequence
* @param cache_size Cache size for the sequence
* @return The statement for getting the next id from the sequence
*/
public String getSequenceCreateStmt(String sequence_name,
String min,String max,
String start,String increment,
String cache_size)
{
if (sequence_name == null)
{
throw new JPOXUserException(LOCALISER.msg("Adapter.SequenceNameNullNotSupported"));
}
StringBuffer stmt = new StringBuffer("CREATE SEQUENCE ");
stmt.append(sequence_name);
if (min != null && min.length() > 0)
{
stmt.append(" START WITH " + min);
}
if (max != null && max.length() > 0)
{
throw new JPOXUserException(LOCALISER.msg("051022"));
}
if (start != null && start.length() > 0)
{
stmt.append(" START WITH " + start);
}
if (increment != null && increment.length() > 0)
{
stmt.append(" INCREMENT BY " + increment);
}
if (cache_size != null && cache_size.length() > 0)
{
throw new JPOXUserException(LOCALISER.msg("051023"));
}
return stmt.toString();
}
/**
* Accessor for the statement for getting the next id from the sequence
* for this datastore.
* @param sequence_name Name of the sequence
* @return The statement for getting the next id for the sequence
*/
public String getSequenceNextStmt(String sequence_name)
{
if (sequence_name == null)
{
throw new JPOXUserException(LOCALISER.msg("Adapter.SequenceNameNullNotSupported"));
}
StringBuffer stmt=new StringBuffer("CALL NEXT VALUE FOR ");
stmt.append(sequence_name);
return stmt.toString();
}
}