/**********************************************************************
Copyright (c) 2002 Kelly Grizzle (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:
2002 Mike Martin (TJDO)
2003 Erik Bengtson - Oracle 9 support the ISO 99 outer join syntax
so I have changed newQueryStatement to use
QueryStatement instead of OracleQueryStatement
when Oracle version is 9
2003 Andy Jefferson - coding standards
2004 Andy Jefferson - added sequence methods
2004 Andy Jefferson - updated Oracle version checks to use majorVersion
2005 Andrew Hoffman - getColumnInfo method to cut down on Oracle startup
...
**********************************************************************/
package org.jpox.store.rdbms.adapter;
import java.math.BigInteger;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import org.jpox.ClassLoaderResolver;
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.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.SqlTemporalExpression;
import org.jpox.store.mapped.expression.StringExpression;
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.mapped.mapping.MappingManager;
import org.jpox.store.rdbms.columninfo.ColumnInfo;
import org.jpox.store.rdbms.columninfo.OracleColumnInfo;
import org.jpox.store.rdbms.key.ForeignKey;
import org.jpox.store.rdbms.mapping.oracle.OracleRDBMSMappingManager;
import org.jpox.store.rdbms.query.Oracle99QueryStatement;
import org.jpox.store.rdbms.query.OracleQueryStatement;
import org.jpox.store.rdbms.typeinfo.OracleTypeInfo;
import org.jpox.store.rdbms.typeinfo.TypeInfo;
import org.jpox.util.JPOXLogger;
/**
* Provides methods for adapting SQL language elements to the Oracle database.
*
* @see DatabaseAdapter
* @version $Revision: 1.51 $
*/
public class OracleAdapter extends DatabaseAdapter
{
/**
* A string containing the list of Oracle keywords
* This list is normally obtained dynamically from the driver using
* DatabaseMetaData.getSQLKeywords()
*
* Based on database Oracle8
*/
public static final String ORACLE_8_RESERVED_WORDS =
"ACCESS,AUDIT,CLUSTER,COMMENT,COMPRESS,EXCLUSIVE,FILE,IDENTIFIED," +
"INCREMENT,INDEX,INITIAL,LOCK,LONG,MAXEXTENTS,MINUS,MLSLABEL,MODE," +
"MODIFY,NOAUDIT,NOCOMPRESS,NOWAIT,NUMBER,OFFLINE,ONLINE,PCTFREE,RAW," +
"RENAME,RESOURCE,ROWID,ROWNUM,SHARE,SUCCESSFUL,SYNONYM,SYSDATE,UID," +
"VALIDATE,VARCHAR2,VALIDATE,VARCHAR2";
/**
* A string containing the list of Oracle keywords
* This list is normally obtained dynamically from the driver using
* DatabaseMetaData.getSQLKeywords()
*
* Based on database Oracle9i
*/
public static final String ORACLE_9_RESERVED_WORDS =
"ACCESS,CHAR,DEFAULT,ADD,CHECK,DELETE,ALL,CLUSTER,DESC,ALTER,COLUMN," +
"DISTINCT,AND,COMMENT,DROP,ANY,COMPRESS,ELSE,AS,CONNECT,EXCLUSIVE,ASC," +
"CREATE,EXISTS,AUDIT,CURRENT,FILE,BETWEEN,DATE,FLOAT,BY,DECIMAL,FOR,FROM," +
"NOT,SHARE,GRANT,NOWAIT,SIZE,GROUP,NULL,SMALLINT,HAVING,NUMBER,START," +
"IDENTIFIED,OF,SUCCESSFUL,IMMEDIATE,OFFLINE,SYNONYM,IN,ON,SYSDATE," +
"INCREMENT,ONLINE,TABLE,INDEX,OPTION,THEN,INITIAL,OR,TO,INSERT,ORDER," +
"TRIGGER,INTEGER,PCTFREE,UID,INTERSECT,PRIOR,UNION,INTO,PRIVILEGES,UNIQUE," +
"IS,PUBLIC,UPDATE,LEVEL,RAW,USER,LIKE,RENAME,VALIDATE,LOCK,RESOURCE,VALUES," +
"LONG,REVOKE,VARCHAR,MAXEXTENTS,ROW,VARCHAR2,MINUS,ROWID,VIEW,MLSLABEL," +
"ROWNUM,WHENEVER,MODE,ROWS,WHERE,MODIFY,SELECT,WITH,NOAUDIT,SESSION," +
"NOCOMPRESS,SET";
/**
* A string containing the list of Oracle keywords
* This list is normally obtained dynamically from the driver using
* DatabaseMetaData.getSQLKeywords()
*
* Based on database Oracle10g
*/
public static final String ORACLE_10_RESERVED_WORDS =
"ACCESS,ADD,ALL,ALTER,AND,ANY,AS,ASC,AUDIT,BETWEEN,BY,CHAR,CHECK,CLUSTER," +
"COLUMN,COMMENT,COMPRESS,CONNECT,CREATE,CURRENT,DATE,DECIMAL,DEFAULT,DELETE," +
"DESC,DISTINCT,DROP,ELSE,EXCLUSIVE,EXISTS,FILE,FLOAT,FOR,FROM,GRANT,GROUP," +
"HAVING,IDENTIFIED,IMMEDIATE,IN,INCREMENT,INDEX,INITIAL,INSERT,INTEGER," +
"INTERSECT,INTO,IS,LEVEL,LIKE,LOCK,LONG,MAXEXTENTS,MINUS,MLSLABEL,MODE," +
"MODIFY,NOAUDIT,NOCOMPRESS,NOT,NOWAIT,NULL,NUMBER,OF,OFFLINE,ON,ONLINE," +
"OPTION,OR,ORDER,PCTFREE,PRIOR,PRIVILEGES,PUBLIC,RAW,RENAME,RESOURCE," +
"REVOKE,ROW,ROWID,ROWNUM,ROWS,SELECT,SESSION,SET,SHARE,SIZE,SMALLINT," +
"START,SUCCESSFUL,SYNONYM,SYSDATE,TABLE,THEN,TO,TRIGGER,UID,UNION," +
"UNIQUE,UPDATE,USER,VALIDATE,VALUES,VARCHAR,VARCHAR2,VIEW,WHENEVER," +
"WHERE,WITH";
/**
* Constructs an Oracle adapter based on the given JDBC metadata.
* @param metadata the database metadata.
*/
public OracleAdapter(DatabaseMetaData metadata)
{
super(metadata);
if (datastoreMajorVersion <= 8)
{
reservedKeywords.addAll(parseKeywordList(ORACLE_8_RESERVED_WORDS));
}
else if (datastoreMajorVersion == 9)
{
reservedKeywords.addAll(parseKeywordList(ORACLE_9_RESERVED_WORDS));
}
else if (datastoreMajorVersion >= 10)
{
reservedKeywords.addAll(parseKeywordList(ORACLE_10_RESERVED_WORDS));
}
// Add on any missing JDBC types
TypeInfo ti = new OracleTypeInfo("CLOB",
(short)Types.CLOB,
1073741823,
"'",
"'",
null,
1,
true,
(short)0,
false,
false,
false,
"CLOB",
(short)0,
(short)0,
10);
addTypeInfo((short)Types.CLOB, ti, true);
ti = new OracleTypeInfo("DATE",
(short)Types.DATE,
7,
null,
null,
null,
1,
false,
(short)3,
false,
false,
false,
"DATE",
(short)0,
(short)0,
10);
addTypeInfo((short)Types.DATE, ti, true);
ti = new OracleTypeInfo("DECIMAL",
(short)Types.DECIMAL,
38,
null,
null,
null,
1,
false,
(short)3,
false,
true,
false,
"NUMBER",
(short)-84,
(short)127,
10);
addTypeInfo((short)Types.DECIMAL, ti, true);
ti = new OracleTypeInfo("DOUBLE",
(short)Types.DOUBLE,
38,
null,
null,
null,
1,
false,
(short)3,
false,
true,
false,
"NUMBER",
(short)-84,
(short)127,
10);
addTypeInfo((short)Types.DOUBLE, ti, true);
ti = new OracleTypeInfo("SDO_GEOMETRY",
(short)Types.STRUCT,
0,
null,
null,
null,
1,
false,
(short)0,
false,
false,
false,
"SDO_GEOMETRY",
(short)0,
(short)0,
10);
addTypeInfo((short)OracleTypeInfo.TYPES_SDO_GEOMETRY, ti, true);
ti = new OracleTypeInfo(OracleTypeInfo.TYPES_NAME_SYS_XMLTYPE,
(short)OracleTypeInfo.TYPES_SYS_XMLTYPE,
1073741823,
"'",
"'",
null,
1,
true,
(short)0,
false,
false,
false,
OracleTypeInfo.TYPES_NAME_SYS_XMLTYPE,
(short)0,
(short)0,
10);
addTypeInfo((short)OracleTypeInfo.TYPES_SYS_XMLTYPE, ti, true);
}
/**
* Accessor for the vendor id
* @return The Oracle vendor id
*/
public String getVendorID()
{
return "oracle";
}
/**
* Some databases, Oracle, treats an empty string (0 length) equals null
* @return whether the database treats an empty string as null
*/
public boolean isNullEqualsEmptyStrings()
{
return true;
}
/**
* Some databases, Oracle, treats an empty string (0 length) equals null
* @return returns a surrogate to replace the empty string in the database
* otherwise it would be treated as null
*/
public String getSurrogateForEmptyStrings()
{
return "\u0001";
}
/**
* @return null, because oracle does not have catalogs
*/
public String getCatalogName(Connection conn) throws SQLException
{
return null;
}
public String getSchemaName(Connection conn) throws SQLException
{
Statement stmt = conn.createStatement();
try
{
String stmtText = "SELECT SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') FROM DUAL";
ResultSet rs = stmt.executeQuery(stmtText);
try
{
if (!rs.next())
{
throw new JPOXDataStoreException("No result returned from " + stmtText).setFatal();
}
return rs.getString(1);
}
finally
{
rs.close();
}
}
finally
{
stmt.close();
}
}
/**
* Whether this datastore supports locking using SELECT ... FOR UPDATE.
* @return whether we support locking using SELECT ... FOR UPDATE.
**/
public boolean supportsLockWithSelectForUpdate()
{
return true;
}
/**
* 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 == ForeignKey.CASCADE_ACTION )
{
return true;
}
if( action == ForeignKey.NULL_ACTION )
{
return true;
}
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)
{
return false;
}
public boolean supportsBooleanComparison()
{
return false;
}
/**
* Accessor for whether the RDBMS supports ANSI join syntax.
* @return Whether the RDBMS supports ANSI join syntax
*/
public boolean supportsAnsiJoinSyntax()
{
if (datastoreMajorVersion < 9)
{
// Oracle 8 doesnt allow "INNER JOIN"
return false;
}
else
{
return true;
}
}
/**
* Accessor for the WHERE clause to add to provide an INNER JOIN
* using non-ANSI syntax.
* @param col1 The main datastore column
* @param col2 The secondary column to join to
* @return The Inner Join WHERE clause.
*/
public String getNonAnsiInnerJoinWhereClause(String col1, String col2)
{
if (datastoreMajorVersion < 9)
{
// p.col_id = s.othercol_id
StringBuffer str = new StringBuffer();
str.append(col1);
str.append(" = ");
str.append(col2);
return str.toString();
}
else
{
return null;
}
}
/**
* Accessor for the WHERE clause to add to provide a LEFT OUTER JOIN
* using non-ANSI syntax.
* @param col1 The main datastore column
* @param col2 The secondary column to join to
* @return The Left Outer Join WHERE clause.
*/
public String getNonAnsiLeftOuterJoinWhereClause(String col1, String col2)
{
if (datastoreMajorVersion < 9)
{
// p.col_id = s.othercol_id (+)
StringBuffer str = new StringBuffer();
str.append(col1);
str.append(" = ");
str.append(col2);
str.append(" (+) ");
return str.toString();
}
else
{
return null;
}
}
/**
* Accessor for the WHERE clause to add to provide a LEFT OUTER JOIN
* using non-ANSI syntax.
* @param col1 The main datastore column
* @param col2 The secondary column to join to
* @return The Left Outer Join WHERE clause.
*/
public String getNonAnsiRightOuterJoinWhereClause(String col1, String col2)
{
if (datastoreMajorVersion < 9)
{
// p.col_id (+) = s.othercol_id
StringBuffer str = new StringBuffer();
str.append(col1);
str.append(" (+) = ");
str.append(col2);
str.append(" ");
return str.toString();
}
else
{
return null;
}
}
/**
* Provide the existing indexes in the database for the table
* @param conn the JDBC connection
* @param dmd the DatabaseMetaData
* @param catalog the catalog name
* @param schema the schema name
* @param table the table name
* @return a ResultSet with the format @see DatabaseMetaData#getIndexInfo(java.lang.String, java.lang.String, java.lang.String, boolean, boolean)
* @throws SQLException
*/
public ResultSet getExistingIndexes(Connection conn, DatabaseMetaData dmd, String catalog, String schema, String table) throws SQLException
{
String GET_INDEXES_STMT =
"select null as table_cat, "+
"owner as table_schem, "+
"table_name, "+
"0 as NON_UNIQUE, "+
"null as index_qualifier, "+
"null as index_name, 0 as type, "+
"0 as ordinal_position, null as column_name, "+
"null as asc_or_desc, "+
"num_rows as cardinality, "+
"blocks as pages, "+
"null as filter_condition "+
"from all_tables "+
"where table_name = ? "+
"and owner = ? "+
"union "+
"select null as table_cat, "+
"i.owner as table_schem, "+
"i.table_name, "+
"decode (i.uniqueness, 'UNIQUE', 0, 1), "+
"null as index_qualifier, "+
"i.index_name, "+
"1 as type, "+
"c.column_position as ordinal_position, "+
"c.column_name, "+
"null as asc_or_desc, "+
"i.distinct_keys as cardinality, "+
"i.leaf_blocks as pages, "+
"null as filter_condition "+
"from all_indexes i, all_ind_columns c "+
"where i.table_name = ? "+
"and i.owner = ? "+
"and i.index_name = c.index_name "+
"and i.table_owner = c.table_owner "+
"and i.table_name = c.table_name "+
"and i.owner = c.index_owner "+
"order by non_unique, type, index_name, ordinal_position";
if( datastoreMajorVersion==10)
{
//THIS IS A FIX to the JDBC driver. Already noticed in previous versions, but this fix was only tested with Oracle 10g, and all drivers versions
PreparedStatement stmt = conn.prepareStatement(GET_INDEXES_STMT);
stmt.setString(1,table);
stmt.setString(2,schema);
stmt.setString(3,table);
stmt.setString(4,schema);
return stmt.executeQuery();
}
//the above was used and tested with Oracle 10 to fix a driver bug when the table identifier is a reserved word
return super.getExistingIndexes(conn,dmd,catalog,schema,table);
}
/**
* Accessor for a new query statement.
* @param table The table to query
* @param clr The ClassLoaderResolver
* @return The Query Statement
**/
public QueryExpression newQueryStatement(DatastoreContainerObject table, ClassLoaderResolver clr)
{
// Oracle 9 or upper supports the ISO 99 outer join syntax
if (datastoreMajorVersion >= 9)
{
return new Oracle99QueryStatement(table, clr);
}
else
{
return new OracleQueryStatement(table, clr);
}
}
/**
* Accessor for a new query statement.
* @param table The table to query
* @param rangeVar A range variable for the query
* @param clr The ClassLoaderResolver
* @return The Query Statement
**/
public QueryExpression newQueryStatement(DatastoreContainerObject table, DatastoreIdentifier rangeVar, ClassLoaderResolver clr)
{
// Oracle 9 or upper supports the ISO 99 outer join syntax
if (datastoreMajorVersion >= 9)
{
return new Oracle99QueryStatement(table, rangeVar, clr);
}
else
{
return new OracleQueryStatement(table, rangeVar, clr);
}
}
public LogicSetExpression newTableExpression(QueryExpression qs, DatastoreContainerObject table, DatastoreIdentifier rangeVar)
{
// Oracle 9 or upper supports the ISO 99 outer join syntax
if (datastoreMajorVersion >= 9)
{
return new TableExprAsJoins(qs, table, rangeVar);
}
else
{
return new TableExprAsSubquery(qs, table, rangeVar);
}
}
/**
* Method to return the drop table statement for Oracle.
* @param table The table
* @return The statement text
*/
public String getDropTableStatement(DatastoreContainerObject table)
{
if (datastoreMajorVersion >= 10)
{
// Add "PURGE" to avoid putting the table into the Oracle "recycle bin"
return "DROP TABLE " + table.toString() + " CASCADE CONSTRAINTS PURGE";
}
else
{
return "DROP TABLE " + table.toString() + " CASCADE CONSTRAINTS";
}
}
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)));
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));
return new StringExpression("SUBSTR", args);
}
/**
* 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 JDOQL Date.getDay()
* method. It should return something like:
* <pre>TO_NUMBER(TO_CHAR(date,'DD'))</pre>
* @param date The date for the getDay() method.
* @return The text of the SQL expression.
*/
public NumericExpression getDayMethod(SqlTemporalExpression date)
{
ArrayList args = new ArrayList();
args.add(date);
args.add(getMapping(String.class, date).newLiteral(date.getQueryExpression(), "DD"));
ArrayList args1 = new ArrayList();
args1.add(new StringExpression("TO_CHAR", args));
return new NumericExpression("TO_NUMBER", args1);
}
/**
* Returns the appropriate SQL expression for the JDOQL Date.getMonth()
* method. It should return something like:
* <pre>TO_NUMBER(TO_CHAR(date,'MM'))</pre>
* @param date The date for the getMonth() method.
* @return The text of the SQL expression.
*/
public NumericExpression getMonthMethod(SqlTemporalExpression date)
{
ArrayList args = new ArrayList();
args.add(date);
args.add(getMapping(String.class, date).newLiteral(date.getQueryExpression(), "MM"));
ArrayList args1 = new ArrayList();
args1.add(new StringExpression("TO_CHAR", args));
// Delete one from the SQL "month" (origin=1) to be compatible with Java month (origin=0)
JavaTypeMapping m = getMapping(BigInteger.class, date);
ScalarExpression integerLiteral = m.newLiteral(date.getQueryExpression(), BigInteger.ONE);
NumericExpression expr = new NumericExpression(new NumericExpression("TO_NUMBER", args1), ScalarExpression.OP_SUB, integerLiteral);
expr.encloseWithInParentheses();
return expr;
}
/**
* Returns the appropriate SQL expression for the JDOQL Date.getYear()
* method. It should return something like:
* <pre>TO_NUMBER(TO_CHAR(date,'YYYY'))</pre>
* @param date The date for the getYear() method.
* @return The text of the SQL expression.
*/
public NumericExpression getYearMethod(SqlTemporalExpression date)
{
ArrayList args = new ArrayList();
args.add(date);
args.add(getMapping(String.class, date).newLiteral(date.getQueryExpression(), "YYYY"));
ArrayList args1 = new ArrayList();
args1.add(new StringExpression("TO_CHAR", args));
return new NumericExpression("TO_NUMBER", args1);
}
/**
* Returns the appropriate SQL expression for the JDOQL Time.getHour()
* method. It should return something like:
* <pre>TO_NUMBER(TO_CHAR(time,'HH24'))</pre>
* @param time The time for the getHour() method.
* @return The text of the SQL expression.
*/
public NumericExpression getHourMethod(SqlTemporalExpression time)
{
ArrayList args = new ArrayList();
args.add(time);
args.add(getMapping(String.class, time).newLiteral(time.getQueryExpression(), "HH24"));
ArrayList args1 = new ArrayList();
args1.add(new StringExpression("TO_CHAR", args));
return new NumericExpression("TO_NUMBER", args1);
}
/**
* Returns the appropriate SQL expression for the JDOQL Time.getMinute()
* method. It should return something like:
* <pre>TO_NUMBER(TO_CHAR(time,'MI'))</pre>
* @param time The time for the getMinute() method.
* @return The text of the SQL expression.
*/
public NumericExpression getMinuteMethod(SqlTemporalExpression time)
{
ArrayList args = new ArrayList();
args.add(time);
args.add(getMapping(String.class, time).newLiteral(time.getQueryExpression(), "MI"));
ArrayList args1 = new ArrayList();
args1.add(new StringExpression("TO_CHAR", args));
return new NumericExpression("TO_NUMBER", args1);
}
/**
* Returns the appropriate SQL expression for the JDOQL Time.getSecond()
* method. It should return something like:
* <pre>TO_NUMBER(TO_CHAR(time,'SS'))</pre>
* @param time The time for the getSecond() method.
* @return The text of the SQL expression.
*/
public NumericExpression getSecondMethod(SqlTemporalExpression time)
{
ArrayList args = new ArrayList();
args.add(time);
args.add(getMapping(String.class, time).newLiteral(time.getQueryExpression(), "SS"));
ArrayList args1 = new ArrayList();
args1.add(new StringExpression("TO_CHAR", args));
return new NumericExpression("TO_NUMBER", args1);
}
/**
* Returns the appropriate SQL expression for the JDOQL String.indexOf() method.
* It should return something like:
* <p>
* <blockquote><pre>
* INSTR(str, substr [,pos])-1
* </pre></blockquote>
* since INSTR 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);
args.add(str);
if (from != null)
{
// Add 1 to the passed in value so that it is of origin 1 to be compatible with INSTR
args.add(new NumericExpression(from, ScalarExpression.OP_ADD, integerLiteral));
}
NumericExpression locateExpr = new NumericExpression("INSTR", args);
// Subtract 1 from the result of INSTR to be consistent with Java strings
// TODO Would be nice to put this in parentheses
return new NumericExpression(locateExpr, ScalarExpression.OP_SUB, integerLiteral);
}
// ---------------------------- 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 UnsupportedOperationException("Adapter.SequenceNameNullNotSupported");
}
StringBuffer stmt = new StringBuffer("CREATE SEQUENCE ");
stmt.append(sequence_name);
if (min != null && min.length() > 0)
{
stmt.append(" MINVALUE " + min);
}
if (max != null && max.length() > 0)
{
stmt.append(" MAXVALUE " + max);
}
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)
{
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("SELECT ");
stmt.append(sequence_name);
stmt.append(".NEXTVAL from dual ");
return stmt.toString();
}
/**
* Accessor for ColumnInfo
*
* @see org.jpox.store.rdbms.adapter.DatabaseAdapter#newColumnInfo(java.sql.ResultSet)
*/
public ColumnInfo newColumnInfo(ResultSet rs)
{
return new OracleColumnInfo(rs);
}
/**
* Accessor for the transaction isolation level to use during schema creation.
* @return The transaction isolation level for schema generation process
*/
public int getTransactionIsolationForSchemaCreation()
{
return Connection.TRANSACTION_READ_COMMITTED;
}
/**
* Whether to initialise the column default values for this datastore.
* With Oracle we cant get the default values without impacting performance
* so we just ignore the default settings.
* @return Whether we should initialise the column defaults
*/
public boolean requiresColumnDefaultsInitialising()
{
// Ignore the defaults since the getColumns() method doesnt return them anyway!
return false;
}
/**
* Accessor for table and column information for a catalog/schema in this datastore.
* An override for the DatabaseMetaData.getColumns() method call as referenced in superclass.
* The default Oracle-provided getColumns() method is VERY slow for large schemas,
* particularly due to REMARKS and COLUMN_DEF (column defaults) columns
* (outer-joins in data dictionary views)
* <b>Note:</b> This method DOES NOT return default column value (meta-data ResultSet column COLUMN_DEF)
* as this column causes MAJOR slowdown in meta-data retrieval performance.
* @param conn Connection to use
* @param catalog The catalog (null if none)
* @param schema The schema (null if none)
* @param table The table (null if all)
* @return ResultSet containing the table/column information
* @throws SQLException Thrown if an error occurs
*/
public ResultSet getColumns(Connection conn, String catalog, String schema, String table)
throws SQLException
{
// setup SQL for query from Oracle data dictionary view ALL_TAB_COLUMNS
StringBuffer columnsQuery = new StringBuffer();
// select columns clause
columnsQuery.append("SELECT NULL TABLE_CAT, OWNER TABLE_SCHEM, TABLE_NAME, COLUMN_NAME, NULL DATA_TYPE, ");
columnsQuery.append("DATA_TYPE TYPE_NAME, DECODE(DATA_TYPE,'NUMBER',DATA_PRECISION,DATA_LENGTH) COLUMN_SIZE, ");
columnsQuery.append("0 BUFFER_LENGTH, DATA_SCALE DECIMAL_DIGITS, 10 NUM_PREC_RADIX, ");
columnsQuery.append("DECODE(NULLABLE,'Y',1,0) NULLABLE, NULL REMARKS, NULL COLUMN_DEF, 0 SQL_DATA_TYPE, 0 SQL_DATETIME_SUB, ");
columnsQuery.append("DATA_LENGTH CHAR_OCTET_LENGTH, COLUMN_ID ORDINAL_POSITION, DECODE(NULLABLE,'Y','YES','NO') IS_NULLABLE ");
// from clause
columnsQuery.append("FROM ALL_TAB_COLUMNS ");
boolean outputWhere = false;
// where clause - schemaString
if (schema != null && schema.length() > 0)
{
columnsQuery.append("WHERE OWNER LIKE '").append(schema.toUpperCase()).append("' ");
outputWhere = true;
}
// where clause - tableString
if (table != null)
{
if (!outputWhere)
{
columnsQuery.append("WHERE ");
outputWhere = true;
}
else
{
columnsQuery.append("AND ");
}
if (table.length() > 0)
{
columnsQuery.append("TABLE_NAME LIKE '").append(table.toUpperCase()).append("' ");
}
else
{
columnsQuery.append("TABLE_NAME IS NULL ");
}
}
// order clause
columnsQuery.append("ORDER BY TABLE_SCHEM, TABLE_NAME, ORDINAL_POSITION ");
JPOXLogger.DATASTORE_SCHEMA.debug("Retrieving Oracle column info using the following SQL : " + columnsQuery);
Statement columnsStmt = conn.createStatement();
ResultSet columnsResult = columnsStmt.executeQuery(columnsQuery.toString());
columnsQuery = null;
return columnsResult;
}
/**
* Acessor for a new MappingManager
* @return the new MappingManager
*/
public MappingManager getNewMappingManager()
{
return new OracleRDBMSMappingManager();
}
/**
* 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 "SELECT CURRENT_TIMESTAMP FROM DUAL";
}
}