/**********************************************************************
Copyright (c) 2002 Mike Martin (TJDO) 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:
2003 Andy Jefferson - coding standards
2004 Erik Bengtson - added Cloudscape 10 and Apache Derby specifics
...
**********************************************************************/
package org.jpox.store.rdbms.adapter;
import java.math.BigInteger;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.StringTokenizer;
import org.jpox.exceptions.JPOXDataStoreException;
import org.jpox.store.mapped.DatastoreContainerObject;
import org.jpox.store.mapped.DatastoreIdentifier;
import org.jpox.store.mapped.expression.BooleanExpression;
import org.jpox.store.mapped.expression.CharacterExpression;
import org.jpox.store.mapped.expression.CharacterLiteral;
import org.jpox.store.mapped.expression.Literal;
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.StringLiteral;
import org.jpox.store.mapped.expression.TableExprAsJoins;
import org.jpox.store.mapped.expression.TableExprAsSubquery;
import org.jpox.store.mapped.mapping.JavaTypeMapping;
import org.jpox.store.rdbms.table.Table;
import org.jpox.store.rdbms.typeinfo.DerbyTypeInfo;
import org.jpox.store.rdbms.typeinfo.TypeInfo;
import org.jpox.util.JPOXLogger;
/**
* Provides methods for adapting SQL language elements to the Cloudscape/Derby database.
*/
public class DerbyAdapter extends DatabaseAdapter
{
/**
* Cloudscape 10.0 beta reserved words, includes SQL92 reserved words
*/
private static final String CLOUDSCAPE_RESERVED_WORDS =
"ADD,ALL,ALLOCATE,ALTER,AND,ANY,ARE,AS," +
"ASC,ASSERTION,AT,AUTHORIZATION,AVG,BEGIN,BETWEEN,BIT," +
"BIT_LENGTH,BOOLEAN,BOTH,BY,CALL,CASCADE,CASCADED,CASE," +
"CAST,CHAR,CHARACTER,CHARACTER_LENGTH,CHAR_LENGTH,CHECK,CLOSE,COLLATE," +
"COLLATION,COLUMN,COMMIT,CONNECT,CONNECTION,CONSTRAINT,CONSTRAINTS,CONTINUE," +
"CONVERT,CORRESPONDING,COUNT,CREATE,CROSS,CURRENT,CURRENT_DATE,CURRENT_TIME," +
"CURRENT_TIMESTAMP,CURRENT_USER,CURSOR,DEALLOCATE,DEC,DECIMAL,DECLARE,DEFERRABLE," +
"DEFERRED,DELETE,DESC,DESCRIBE,DIAGNOSTICS,DISCONNECT,DISTINCT,DOUBLE," +
"DROP,ELSE,END,ENDEXEC,ESCAPE,EXCEPT,EXCEPTION,EXEC," +
"EXECUTE,EXISTS,EXPLAIN,EXTERNAL,EXTRACT,FALSE,FETCH,FIRST," +
"FLOAT,FOR,FOREIGN,FOUND,FROM,FULL,FUNCTION,GET," +
"GET_CURRENT_CONNECTION,GLOBAL,GO,GOTO,GRANT,GROUP,HAVING,HOUR," +
"IDENTITY,IMMEDIATE,IN,INDICATOR,INITIALLY,INNER,INOUT,INPUT," +
"INSENSITIVE,INSERT,INT,INTEGER,INTERSECT,INTO,IS,ISOLATION," +
"JOIN,KEY,LAST,LEADING,LEFT,LIKE,LOCAL,LONGINT," +
"LOWER,LTRIM,MATCH,MAX,MIN,MINUTE,NATIONAL,NATURAL," +
"NCHAR,NVARCHAR,NEXT,NO,NOT,NULL,NULLIF,NUMERIC," +
"OCTET_LENGTH,OF,ON,ONLY,OPEN,OPTION,OR,ORDER," +
"OUT,OUTER,OUTPUT,OVERLAPS,PAD,PARTIAL,PREPARE,PRESERVE," +
"PRIMARY,PRIOR,PRIVILEGES,PROCEDURE,PUBLIC,READ,REAL,REFERENCES," +
"RELATIVE,RESTRICT,REVOKE,RIGHT,ROLLBACK,ROWS,RTRIM,RUNTIMESTATISTICS," +
"SCHEMA,SCROLL,SECOND,SELECT,SESSION_USER,SET,SMALLINT,SOME," +
"SPACE,SQL,SQLCODE,SQLERROR,SQLSTATE,SUBSTR,SUBSTRING,SUM," +
"SYSTEM_USER,TABLE,TEMPORARY,TIMEZONE_HOUR,TIMEZONE_MINUTE,TINYINT,TO,TRAILING," +
"TRANSACTION,TRANSLATE,TRANSLATION,TRIM,TRUE,UNION,UNIQUE,UNKNOWN," +
"UPDATE,UPPER,USER,USING,VALUES,VARCHAR,VARYING,VIEW," +
"WHENEVER,WHERE,WITH,WORK,WRITE,YEAR";
/**
* Constructs an Apache Derby adapter based on the given JDBC metadata.
* @param metadata the database metadata.
*/
public DerbyAdapter(DatabaseMetaData metadata)
{
super(metadata);
reservedKeywords.addAll(parseKeywordList(CLOUDSCAPE_RESERVED_WORDS));
}
/**
* Creates the auxiliary functions/procedures in the schema
* @param conn the connection to the datastore
*/
public void initialiseDatastore(Object conn)
{
try
{
Statement st = ((Connection) conn).createStatement();
StringTokenizer tokens = new StringTokenizer(getASCIIFunction()+getMatchesFunction(),";");
while (tokens.hasMoreTokens())
{
try
{
st.execute(tokens.nextToken());
}
catch (SQLException e)
{
JPOXLogger.DATASTORE.warn(LOCALISER.msg("051027",e));
}
}
st.close();
}
catch (SQLException e)
{
e.printStackTrace();
throw new JPOXDataStoreException(e.getMessage(), e);
}
}
/**
* Accessor for the schema name.
* @param conn The Connection to use
* @return The schema name used by this connection
* @throws SQLException
*/
public String getSchemaName(Connection conn)
throws SQLException
{
// see http://incubator.apache.org/derby/faq.html#schema_exist
// a connection's current schema name defaults to the connection's user name
return conn.getMetaData().getUserName().toUpperCase();
}
/**
* Accessor for the catalog name.
* @param conn The Connection to use
* @return The catalog name used by this connection
* @throws SQLException
*/
public String getCatalogName(Connection conn)
throws SQLException
{
String catalog = conn.getCatalog();
// the ProbeTable approach returns empty string instead of null here, so do the same
return catalog != null ? catalog : "";
}
/**
* Accessor for the vendor id.
* @return The vendor id.
*/
public String getVendorID()
{
return "derby";
}
/**
* Method to add a new type.
* @param rs ResultSet containing the type info.
* @return The TypeInfo
*/
public TypeInfo newTypeInfo(ResultSet rs)
{
return new DerbyTypeInfo(rs);
}
/**
* Accessor for whether Cloudscape supports deferred constraints.
* @return Whether it supports deferred constraints
*/
public boolean supportsDeferredConstraints()
{
return false;
}
public LogicSetExpression newTableExpression(QueryExpression qs, DatastoreContainerObject table, DatastoreIdentifier rangeVar)
{
/*
* Syntactically Cloudscape can also handle TableExprAsJoins,
* but when I tried that style with Cloudscape 4.0 certain queries
* (like those involving EXISTS) failed with a message stating that
* no execution plan could be found, or some such.
*/
if (datastoreMajorVersion >= 10)
{
return new TableExprAsJoins(qs, table, rangeVar);
}
else
{
return new TableExprAsSubquery(qs, table, rangeVar);
}
}
public String getDropTableStatement(DatastoreContainerObject table)
{
return "DROP TABLE " + table.toString();
}
/**
* Whether the database support NULLs in the column options for table creation.
* By default all columns are nullable in DB2, and the keyword NULL in the column options are not supported
* @return whether the database support NULLs in the column options for table creation.
**/
public boolean supportsNullsKeywordInColumnOptions()
{
if (datastoreMajorVersion >= 10)
{
return false;
}
return true;
}
/**
* Accessor for the maximum constraint name length permitted on this
* datastore.
* @return Max constraint name length
**/
public int getMaxConstraintNameLength()
{
// TODO Do we need this limit ?
if (datastoreMajorVersion >= 10)
{
return 18;
}
return maxConstraintNameLength;
}
/**
* Accessor for the maximum index name length permitted on this datastore.
* @return Max index name length
**/
public int getMaxIndexNameLength()
{
// TODO Do we need this limit ?
if (datastoreMajorVersion >= 10)
{
return 18;
}
return maxIndexNameLength;
}
public NumericExpression lengthMethod(StringExpression str)
{
ArrayList args = new ArrayList();
args.add(str);
return new NumericExpression("LENGTH", args);
}
public StringExpression substringMethod(StringExpression str,
NumericExpression begin)
{
ArrayList args = new ArrayList();
args.add(str);
args.add(begin.add(getMapping(BigInteger.class, str).newLiteral(str.getQueryExpression(), BigInteger.ONE)));
//Cloudscape 10.0
//SUBSTR( string, start )
return new StringExpression("SUBSTR", args);
}
public StringExpression substringMethod(StringExpression str,
NumericExpression begin,
NumericExpression end)
{
ArrayList args = new ArrayList();
args.add(str);
args.add(begin.add(getMapping(BigInteger.class, str).newLiteral(str.getQueryExpression(), BigInteger.ONE)));
args.add(end.sub(begin));
//Cloudscape 10.0
//SUBSTR( string, start, length )
return new StringExpression("SUBSTR", 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;
}
/**
* Method to handle the starts with operation.
* @param source The expression with the searched string
* @param str The expression for the search string
* @return The expression.
**/
public BooleanExpression startsWithMethod(ScalarExpression source, ScalarExpression str)
{
JavaTypeMapping m = getMapping(BigInteger.class, source);
ScalarExpression integerLiteral = m.newLiteral(source.getQueryExpression(), BigInteger.ONE);
ArrayList args = new ArrayList();
args.add(str);
args.add(source);
//Cloudscape 10.0
//LOCATE( stringSearched, SearchString, [StartPosition] )
return new BooleanExpression(new StringExpression("LOCATE", args),ScalarExpression.OP_EQ,integerLiteral);
}
/**
* 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 "VALUES IDENTITY_VAL_LOCAL()";
}
/**
* Accessor for the auto-increment keyword for generating DDLs (CREATE TABLEs...).
* @return The keyword for a column using auto-increment
**/
public String getAutoIncrementKeyword()
{
return "generated always as identity (start with 1)";
}
/**
* Verifies if the given <code>columnDef</code> is auto incremented by the datastore.
* @param columnDef the datastore type name
* @return true when the <code>columnDef</code> has values auto incremented by the datastore
**/
public boolean isIdentityFieldDataType(String columnDef)
{
if( columnDef != null && columnDef.toUpperCase().indexOf("AUTOINCREMENT")>=0 )
{
return true;
}
return false;
}
/**
* Whether we support auto-increment fields.
* @return whether we support auto-increment fields.
**/
public boolean supportsIdentityFields()
{
return true;
}
/**
* 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)
{
// Note that calling "VALUES IDENTITY_VAL_LOCAL()" immediately after this with Derby 10.0.2.1 returns null!
// This is a bug in Derby - Apache JIRA "DERBY-439"
return "INSERT INTO " + table.toString() + " VALUES (DEFAULT)";
}
/**
* Whether we support DEFAULT tag together with NOT NULL in CREATE TABLE statements.
* <pre>
* CREATE TABLE X ( MEMORY_SIZE BIGINT DEFAULT 0 NOT NULL )
* </pre>
* Some databases only support <i>DEFAULT {ConstantExpression | NULL}</i>
*
* @return whether we support the DEFAULT tag together with NOT NULL in CREATE TABLE
**/
public boolean supportsDefaultKeywordWithNotNullInColumnOptions()
{
return false;
}
/**
* Whether this datastore supports SELECT ... FOR UPDATE.
* @return whether we support SELECT ... FOR UPDATE.
**/
public boolean supportsLockWithSelectForUpdate()
{
return true;
}
/**
* Whether we support NULLs in candidate keys.
* @return whether we support NULLs in candidate keys.
**/
public boolean supportsNullsInCandidateKeys()
{
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;
}
/**
* 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 expression for the <code>(int)'A'</code> expression.
* In SQL, it should compile something like:
* <p>
* <blockquote><pre>
* ASCII('A')
* </pre></blockquote>
* </p>
* @param expr The CharacterExpression
* @return The NumericExpression
*/
public NumericExpression toNumericExpression(CharacterExpression expr)
{
if (expr instanceof CharacterLiteral)
{
char c = ((Character) ((CharacterLiteral) expr).getValue()).charValue();
BigInteger value = new BigInteger("" + (int) c);
JavaTypeMapping m = getMapping(value.getClass(), expr);
return (NumericExpression) m.newLiteral(expr.getQueryExpression(), value);
}
else if (expr instanceof Literal)
{
BigInteger value = new BigInteger((String) ((Literal) expr).getValue());
JavaTypeMapping m = getMapping(value.getClass(), expr);
return (NumericExpression) m.newLiteral(expr.getQueryExpression(), value);
}
ArrayList args = new ArrayList();
args.add(expr);
return new NumericExpression("JPOX_ASCII", args);
}
/**
* A String conversion that converts a numeric expression to string.
* If the expr argument represents a Literal value, converts to a Literal string.
* In SQL, it should compile something like:
* <p>
* <blockquote>
* <pre>
* CAST(999999 AS VARCHAR(4000))
* </pre>
* </blockquote>
* </p>
* @param expr The NumericExpression
* @return the StringExpression
*/
public StringExpression toStringExpression(NumericExpression expr)
{
if (expr instanceof Literal)
{
JavaTypeMapping m = getMapping(String.class, expr);
return new StringLiteral(expr.getQueryExpression(),m,((Literal)expr).getValue().toString());
}
List args = new ArrayList();
args.add(expr);
List argsRTRIM = new ArrayList();
argsRTRIM.add(new StringExpression("CHAR", args));
return new StringExpression("RTRIM",argsRTRIM);
}
/**
* Creates a ASCII function for Derby
* @return the SQL ASCII function
*/
private String getASCIIFunction()
{
return "DROP FUNCTION JPOX_ASCII; "+
"CREATE FUNCTION JPOX_ASCII(C CHAR(1)) RETURNS INTEGER "+
"EXTERNAL NAME 'org.jpox.store.rdbms.adapter.DerbySQLFunction.ascii' "+
"CALLED ON NULL INPUT "+
"LANGUAGE JAVA PARAMETER STYLE JAVA;";
}
/**
* Creates a String.matches function for Derby
* @return the SQL String.matches function
*/
private String getMatchesFunction()
{
return "DROP FUNCTION JPOX_MATCHES; "+
"CREATE FUNCTION JPOX_MATCHES(TEXT VARCHAR(8000), PATTERN VARCHAR(8000)) RETURNS INTEGER "+
"EXTERNAL NAME 'org.jpox.store.rdbms.adapter.DerbySQLFunction.matches' "+
"CALLED ON NULL INPUT "+
"LANGUAGE JAVA PARAMETER STYLE JAVA;";
}
/**
* <p>
* If only one operand expression is of type String, then string conversion
* is performed on the other operand to produce a string at run time. The
* result is a reference to a String object (newly created, unless the
* expression is a compile-time constant expression (�15.28))that is the
* concatenation of the two operand strings. The characters of the left-hand
* operand precede the characters of the right-hand operand in the newly
* created string. If an operand of type String is null, then the string
* "null" is used instead of that operand. "null" is used instead of that
* operand.
* </p>
* <p>
* Concatenates two or more character or binary strings, columns, or a
* combination of strings and column names into one expression (a string
* operator).
* </p>
* @param operand1 the left expression
* @param operand2 the right expression
* @return The Expression for concatenation
*/
public ScalarExpression concatOperator(ScalarExpression operand1, ScalarExpression operand2)
{
/*
* We cast it to VARCHAR otherwise the concatenation in derby it is promoted to
* LONG VARCHAR.
*
* In Derby, ? string parameters are promoted to LONG VARCHAR, and Derby
* does not allow comparisons between LONG VARCHAR types. so the below
* example would be invalid in Derby
* (THIS.FIRSTNAME||?) = ?
*
* Due to that we convert it to
*
* (CAST(THIS.FIRSTNAME||? AS VARCHAR(4000))) = ?
*
* The only issue with this solution is for columns bigger than 4000 chars.
*
* Secondly, if both operands are parameters, derby does not allow concatenation
* e.g.
*
* ? || ? is not allowed by derby
*
* so we do
*
* CAST( ? AS VARCHAR(4000) ) || CAST( ? AS VARCHAR(4000) )
*
* If both situations happen,
*
* (CAST(CAST( ? AS VARCHAR(4000) ) || CAST( ? AS VARCHAR(4000) ) AS VARCHAR(4000))) = ?
*/
List types = new ArrayList();
types.add("VARCHAR(4000)");
List argsOp1 = new ArrayList();
argsOp1.add(operand1);
List argsOp2 = new ArrayList();
argsOp2.add(operand2);
List args = new ArrayList();
args.add(super.concatOperator(new StringExpression("CAST", argsOp1, types), new StringExpression("CAST", argsOp2, types)));
return new StringExpression("CAST", args, types);
}
/**
* Matches this to the argument expression pattern. Use "." to find any
* character and ".*" for wildcard matches. A global case-insensitive flag
* "(?i)" can be set for the pattern. If used, the global case-insensitive
* flag must prefix the pattern. The pattern passed to matches must be a
* literal or parameter.
* @param text The argument to the length() method.
* @param pattern The literal expression with the pattern.
* @return the match expression.
*/
public BooleanExpression matchesMethod(StringExpression text, StringExpression pattern)
{
JavaTypeMapping m = getMapping(BigInteger.class, text);
ScalarExpression integerLiteral = m.newLiteral(text.getQueryExpression(), BigInteger.ONE);
List args = new ArrayList();
args.add(text);
args.add(pattern);
return new NumericExpression("JPOX_MATCHES",args).eq(integerLiteral);
}
/**
* 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 "VALUES CURRENT_TIMESTAMP";
}
}