/**********************************************************************
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
...
**********************************************************************/
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.sql.Types;
import java.util.ArrayList;
import java.util.List;
import org.jpox.exceptions.JPOXDataStoreException;
import org.jpox.exceptions.JPOXUserException;
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.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.expression.ScalarExpression.IllegalArgumentTypeException;
import org.jpox.store.rdbms.columninfo.ColumnInfo;
import org.jpox.store.rdbms.columninfo.DB2ColumnInfo;
import org.jpox.store.rdbms.key.ForeignKey;
import org.jpox.store.rdbms.table.Table;
import org.jpox.store.rdbms.typeinfo.DB2TypeInfo;
import org.jpox.store.rdbms.typeinfo.TypeInfo;
/**
* Provides methods for adapting SQL language elements to the DB2 database.
*
* @see DatabaseAdapter
* @version $Revision: 1.35 $
*/
public class DB2Adapter extends DatabaseAdapter
{
/**
* A string containing the list of DB2 keywords
* This list is normally obtained dynamically from the driver using
* DatabaseMetaData.getSQLKeywords()
*
* Based on database DB2 version 7
*/
public static final String DB2_RESERVED_WORDS =
"ACCESS,ALIAS,ALLOW,ASUTIME,AUDIT,AUX,AUXILIARY,BUFFERPOOL," +
"CAPTURE,CCSID,CLUSTER,COLLECTION,COLLID,COMMENT,CONCAT," +
"CONTAINS,COUNT_BIG,CURRENT_LC_PATH,CURRENT_SERVER," +
"CURRENT_TIMEZONE,DATABASE,DAYS,DB2GENERAL,DB2SQL,DBA," +
"DBINFO,DBSPACE,DISALLOW,DSSIZE,EDITPROC,ERASE,EXCLUSIVE," +
"EXPLAIN,FENCED,FIELDPROC,FILE,FINAL,GENERATED,GRAPHIC,HOURS," +
"IDENTIFIED,INDEX,INTEGRITY,ISOBID,JAVA,LABEL,LC_CTYPE,LINKTYPE," +
"LOCALE,LOCATORS,LOCK,LOCKSIZE,LONG,MICROSECOND,MICROSECONDS," +
"MINUTES,MODE,MONTHS,NAME,NAMED,NHEADER,NODENAME,NODENUMBER," +
"NULLS,NUMPARTS,OBID,OPTIMIZATION,OPTIMIZE,PACKAGE,PAGE," +
"PAGES,PART,PCTFREE,PCTINDEX,PIECESIZE,PLAN,PRIQTY,PRIVATE," +
"PROGRAM,PSID,QYERYNO,RECOVERY,RENAME,RESET,RESOURCE,RRN,RUN," +
"SCHEDULE,SCRATCHPAD,SECONDS,SECQTY,SECURITY,SHARE,SIMPLE," +
"SOURCE,STANDARD,STATISTICS,STAY,STOGROUP,STORES,STORPOOL," +
"STYLE,SUBPAGES,SYNONYM,TABLESPACE,TYPE,VALIDPROC,VARIABLE," +
"VARIANT,VCAT,VOLUMES,WLM,YEARS";
/**
* Constructs a DB2 adapter based on the given JDBC metadata.
* @param metadata the database metadata.
*/
public DB2Adapter(DatabaseMetaData metadata)
{
super(metadata);
reservedKeywords.addAll(parseKeywordList(DB2_RESERVED_WORDS));
// Add on any missing JDBC types
TypeInfo ti = new DB2TypeInfo("FLOAT",
(short)Types.FLOAT,
53,
null,
null,
null,
1,
false,
(short)2,
false,
false,
false,
null,
(short)0,
(short)0,
0);
addTypeInfo((short)Types.FLOAT, ti, true);
ti = new DB2TypeInfo("NUMERIC",
(short)Types.NUMERIC,
31,
null,
null,
"PRECISION,SCALE",
1,
false,
(short)2,
false,
false,
false,
null,
(short)0,
(short)31,
0);
addTypeInfo((short)Types.NUMERIC, ti, true);
ti = new DB2TypeInfo("BIGINT",
(short)Types.BIGINT,
20,
null,
null,
null,
1,
false,
(short)2,
false,
true,
false,
null,
(short)0,
(short)0,
10);
addTypeInfo((short)Types.BIGINT, ti, true);
}
public String getVendorID()
{
return "db2";
}
public String getSchemaName(Connection conn) throws SQLException
{
Statement stmt = conn.createStatement();
try
{
String stmtText = "VALUES (CURRENT SCHEMA)";
ResultSet rs = stmt.executeQuery(stmtText);
try
{
if (!rs.next())
{
throw new JPOXDataStoreException("No result returned from " + stmtText).setFatal();
}
return rs.getString(1).trim();
}
finally
{
rs.close();
}
}
finally
{
stmt.close();
}
}
/**
* Method to close a Connection to the datastore.
* @param conn The connection
* @throws SQLException Thrown if error occurs on the close.
**/
public void closeConnection(Connection conn)
throws SQLException
{
try
{
conn.rollback();
}
catch( Exception ex)
{
//nothing to do here
}
finally
{
conn.close();
}
}
/**
* Accessor for the maximum constraint name length permitted on this
* datastore.
* @return Max constraint name length
**/
public int getMaxConstraintNameLength()
{
// Impose 18 char restriction
return 18;
}
/**
* Accessor for the maximum index name length permitted on this datastore.
* @return Max index name length
**/
public int getMaxIndexNameLength()
{
// Impose 18 char restriction
return 18;
}
/**
* Whether this datastore supports locking using SELECT ... FOR UPDATE.
* @return whether we support locking using SELECT ... FOR UPDATE.
**/
public boolean supportsLockWithSelectForUpdate()
{
return true;
}
public boolean supportsBooleanComparison()
{
return false;
}
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)
{
if (action.equals(ForeignKey.RESTRICT_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)
{
if (action.equals(ForeignKey.CASCADE_ACTION) ||
action.equals(ForeignKey.RESTRICT_ACTION) ||
action.equals(ForeignKey.NULL_ACTION))
{
return true;
}
return false;
}
public boolean supportsNullsInCandidateKeys()
{
return false;
}
public TypeInfo newTypeInfo(ResultSet rs)
{
TypeInfo ti = new DB2TypeInfo(rs);
return ti;
}
public ColumnInfo newColumnInfo(ResultSet rs)
{
return new DB2ColumnInfo(rs);
}
public LogicSetExpression newTableExpression(QueryExpression qs, DatastoreContainerObject table, DatastoreIdentifier rangeVar)
{
// there is no real reason, why only on or after version 8, but the version I'm testing against
if (datastoreMajorVersion >= 8)
{
return new TableExprAsJoins(qs, table, rangeVar);
}
else
{
return new TableExprAsSubquery(qs, table, rangeVar);
}
}
/**
* 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)
{
List args = new ArrayList();
List types = new ArrayList();
types.add("BIGINT");
List argsOp1 = new ArrayList();
argsOp1.add(operand1);
args.add(new NumericExpression("CAST", argsOp1, types));
List argsOp2 = new ArrayList();
argsOp2.add(operand2);
args.add(new NumericExpression("CAST", argsOp2, types));
return new NumericExpression("MOD", args);
}
public int getUnlimitedLengthPrecisionValue(TypeInfo typeInfo)
{
if (typeInfo.dataType == java.sql.Types.BLOB || typeInfo.dataType == java.sql.Types.CLOB)
{
return 1 << 30;
}
else
{
return super.getUnlimitedLengthPrecisionValue(typeInfo);
}
}
public String getDropTableStatement(DatastoreContainerObject table)
{
return "DROP TABLE " + table.toString();
}
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();
if( str instanceof StringLiteral )
{
args.add(toStringExpression((StringLiteral) str));
}
else
{
args.add(str);
}
args.add(begin.add(getMapping(BigInteger.class, str).newLiteral(str.getQueryExpression(), BigInteger.ONE)));
//DB2 8.0 manual
//SUBSTR( string, start )
return new StringExpression("SUBSTR", args);
}
public StringExpression substringMethod(StringExpression str,
NumericExpression begin,
NumericExpression end)
{
ArrayList args = new ArrayList();
if( str instanceof StringLiteral )
{
args.add(toStringExpression((StringLiteral) str));
}
else
{
args.add(str);
}
args.add(begin.add(getMapping(BigInteger.class, str).newLiteral(str.getQueryExpression(), BigInteger.ONE)));
args.add(end.sub(begin));
//DB2 8.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;
}
/**
* Returns the appropriate SQL expression for the JDOQL String.indexOf() method.
* It should return something like:
* <p>
* <blockquote><pre>
* LOCATE(str, substr [,pos])-1
* </pre></blockquote>
* since LOCATE returns the first character as position 1. Similarly the "pos" is based on the first
* position being 1.
* </p>
* @param source The expression we want to search.
* @param str The argument to the indexOf() method.
* @param from The from position
* @return The text of the SQL expression.
*/
public NumericExpression indexOfMethod(ScalarExpression source, ScalarExpression str, NumericExpression from)
{
ScalarExpression integerLiteral = getMapping(BigInteger.class, source).newLiteral(source.getQueryExpression(), BigInteger.ONE);
ArrayList args = new ArrayList();
args.add(source);
List argsOp0 = new ArrayList();
argsOp0.add(str);
List types = new ArrayList();
types.add("VARCHAR(4000)"); // max 4000 according DB2 docs
args.add(new StringExpression("CAST", argsOp0, types));
if (from != null)
{
types = new ArrayList();
types.add("BIGINT");
List argsOp1 = new ArrayList();
argsOp1.add(new NumericExpression(from, ScalarExpression.OP_ADD, integerLiteral));
// Add 1 to the passed in value so that it is of origin 1 to be compatible with LOCATE
// Make sure argument is typed as BIGINT
args.add(new NumericExpression("CAST", argsOp1, types));
}
NumericExpression locateExpr = new NumericExpression("LOCATE", args);
// Subtract 1 from the result of LOCATE to be consistent with Java strings
// TODO Would be nice to put this in parentheses
return new NumericExpression(locateExpr, ScalarExpression.OP_SUB, integerLiteral);
}
/**
* 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()
{
return false;
}
/**
* Whether we support auto-increment fields.
* @return whether we support auto-increment 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 "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)";
}
/**
* Continuation string to use where the SQL statement goes over more than 1
* line. DB2 doesn't convert newlines into continuation characters and so
* we just provide a space so that it accepts the statement.
* @return Continuation string.
**/
public String getContinuationString()
{
return " ";
}
// ---------------------------- 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);
stmt.append(" AS INTEGER ");
if (start != null && start.length() > 0)
{
stmt.append(" START WITH " + start);
}
if (increment != null && increment.length() > 0)
{
stmt.append(" INCREMENT BY " + increment);
}
if (min != null && min.length() > 0)
{
stmt.append(" MINVALUE " + min);
}
if (max != null && max.length() > 0)
{
stmt.append(" MAXVALUE " + max);
}
if (cache_size != null && cache_size.length() > 0)
{
stmt.append(" CACHE " + cache_size);
}
else
{
stmt.append(" NOCACHE");
}
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("VALUES NEXTVAL FOR ");
stmt.append(sequence_name);
return stmt.toString();
}
/**
* A String conversion that converts a String literal to String expression. It will allow
* the String to only be evaluated at runtime.
* In SQL, it should compile something like:
* <p>
* <blockquote>
* <pre>
* CAST(999999 AS VARCHAR(32672))
* </pre>
* </blockquote>
* </p>
* @param expr The NumericExpression
* @return the StringExpression
*/
public StringExpression toStringExpression(StringLiteral expr)
{
List args = new ArrayList();
args.add(expr);
List types = new ArrayList();
types.add("VARCHAR(32672)");
return new StringExpression("CAST", args, types);
}
/**
* Method to translate all chars in this expression to the <code>fromExpr</code> which
* corresponds to <code>toExpr</code>.
* @return The expression.
**/
public StringExpression translateMethod(ScalarExpression expr, ScalarExpression toExpr, ScalarExpression fromExpr)
{
ArrayList args = new ArrayList();
args.add(expr);
args.add(toExpr);
args.add(fromExpr);
return new StringExpression("TRANSLATE", args);
}
/**
* 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)
{
ScalarExpression integerLiteral = getMapping(BigInteger.class, source).newLiteral(source.getQueryExpression(), BigInteger.ONE);
ArrayList args = new ArrayList();
if (str instanceof StringLiteral)
{
args.add(toStringExpression((StringLiteral) str));
}
else
{
args.add(str);
}
if (source instanceof StringLiteral)
{
args.add(toStringExpression((StringLiteral) source));
}
else
{
args.add(source);
}
//DB2 8 manual
//a VARCHAR the maximum length is 4 000 bytes
//LOCATE( CAST(stringSearched AS VARCHAR(4000)), SearchString, [StartPosition] )
return new BooleanExpression(new StringExpression("LOCATE", args),ScalarExpression.OP_EQ,integerLiteral);
}
/**
* <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);
}
/**
* Returns whether this string ends with the specified string.
* @param leftOperand the source string
* @param rightOperand The string to compare against.
* @return Whether it ends with the string.
**/
public BooleanExpression endsWithMethod(ScalarExpression leftOperand, ScalarExpression rightOperand)
{
if (!(rightOperand instanceof StringExpression))
{
throw new IllegalArgumentTypeException(rightOperand);
}
List types = new ArrayList();
types.add("VARCHAR(4000)");
List argsOp1 = new ArrayList();
argsOp1.add(leftOperand);
StringLiteral pct = new StringLiteral(leftOperand.getQueryExpression(), leftOperand.getMapping(), '%');
return new BooleanExpression(new StringExpression("CAST", argsOp1, types), ScalarExpression.OP_LIKE, pct.add(leftOperand.getQueryExpression().getStoreManager().getDatastoreAdapter().getEscapedPatternExpression(rightOperand)));
}
}