/**********************************************************************
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:
...
**********************************************************************/
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 org.jpox.exceptions.JPOXDataStoreException;
import org.jpox.store.mapped.IdentifierFactory;
import org.jpox.store.mapped.expression.BooleanExpression;
import org.jpox.store.mapped.expression.NumericExpression;
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.StringLiteral;
import org.jpox.store.mapped.expression.ScalarExpression.IllegalArgumentTypeException;
import org.jpox.store.mapped.mapping.JavaTypeMapping;
import org.jpox.store.rdbms.key.CandidateKey;
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.InformixTypeInfo;
import org.jpox.store.rdbms.typeinfo.TypeInfo;
import org.jpox.util.JPOXLogger;
/**
* Provides methods for adapting SQL language elements to the Informix
* database. Overrides some methods in DatabaseAdapter where Informix behaviour
* differs.
*
* Informix databases must be created WITH LOG MODE ANSI, otherwise
* errors like "Transaction Not Supported", "Not in transaction" will appear.
* See the informix info
*
* @version $Revision: 1.11 $
*/
public class InformixAdapter extends DatabaseAdapter
{
/**
* Constructor.
* @param metadata MetaData for the DB
**/
public InformixAdapter(DatabaseMetaData metadata)
{
super(metadata);
}
/**
* 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();
try
{
st.execute(getSTRPOSDropFunction());
}
catch (SQLException e)
{
JPOXLogger.DATASTORE.warn(LOCALISER.msg("051027",e));
}
try
{
st.execute(getSTRPOSFunction());
}
catch (SQLException e)
{
JPOXLogger.DATASTORE.warn(LOCALISER.msg("051027",e));
}
st.close();
}
catch (SQLException e)
{
e.printStackTrace();
throw new JPOXDataStoreException(e.getMessage(), e);
}
}
public String getVendorID()
{
return "informix";
}
/**
* Factory for TypeInfo objects.
* @param rs The ResultSet from DatabaseMetaData.getTypeInfo().
* @return A TypeInfo object.
**/
public TypeInfo newTypeInfo(ResultSet rs)
{
return new InformixTypeInfo(rs);
}
/**
* Accessor for an identifier quote string.
* @return Identifier quote string.
**/
public String getIdentifierQuoteString()
{
// the documentation says a double quote, but it seems to not work
return "";
}
// ---------------------------- AutoIncrement Support ---------------------------
/**
* Whether we support autoincrementing fields.
* @return whether we support autoincrementing fields.
**/
public boolean supportsIdentityFields()
{
return true; // SERIAL columns
}
/**
* Accessor for the autoincrement sql access 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)
{
// Which one of these works ? Not got Informix so cant check
return "SELECT first 1 dbinfo('sqlca.sqlerrd1') from systables";
// return "SELECT unique dbinfo('sqlca.sqlerrd1') s from " + JDBCUtils.getIdentifierNameStripped(tableName, this);
}
/**
* Accessor for the auto-increment keyword for generating DDLs (CREATE TABLEs...).
* @return The keyword for a column using auto-increment
**/
public String getAutoIncrementKeyword()
{
return "SERIAL";
}
/**
* Whether we support auto-increment/identity keys with nullability specification.
* @return whether we support auto-increment keys with nullability spec.
**/
public boolean supportsAutoIncrementKeysNullSpecification()
{
return false;
}
/**
* Whether we support auto-increment/identity keys with column type specification.
* @return whether we support auto-increment keys with column type spec.
**/
public boolean supportsAutoIncrementColumnTypeSpecification()
{
return false;
}
/**
* Whether the database support NULLs in the column options for table creation.
* @return whether the database support NULLs in the column options for table creation.
**/
public boolean supportsNullsKeywordInColumnOptions()
{
return false;
}
/**
* Whether this datastore supports "SELECT a.* FROM (SELECT * FROM TBL1 INNER JOIN TBL2 ON tbl1.x = tbl2.y ) a"
* If the database does not support the SQL statement generated is like
* "SELECT a.* FROM (TBL1 INNER JOIN TBL2 ON tbl1.x = tbl2.y ) a"
* @return whether we support project in subqueries joins
**/
public boolean supportsProjectionInTableReferenceJoins()
{
return true;
}
/**
* Informix 11.x mandates this
* Whether the datastore supports specification of the primary key in
* CREATE TABLE statements.
* @return Whetehr it allows "PRIMARY KEY ..."
*/
public boolean supportsPrimaryKeyInCreateStatements()
{
return true;
}
/**
* Informix 11.x does not support
* Whether this datastore supports deferred constraints.
* @return whether we support deferred constraints.
**/
public boolean supportsDeferredConstraints()
{
return false;
}
/**
* Informix 11.x 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)
{
// PK is created by the CREATE TABLE statement so we just return null
return null;
}
/**
* Returns the appropriate SQL to add a foreign key to its table.
* It should return something like:
* <p>
* <pre>
* ALTER TABLE FOO ADD CONSTRAINT FOREIGN KEY (BAR, BAZ) REFERENCES ABC (COL1, COL2) CONSTRAINT FOO_FK1
* ALTER TABLE FOO ADD FOREIGN KEY (BAR, BAZ) REFERENCES ABC (COL1, COL2)
* </pre>
* @param fk An object describing the foreign key.
* @param factory Identifier factory
* @return The text of the SQL statement.
*/
public String getAddForeignKeyStatement(ForeignKey fk, IdentifierFactory factory)
{
if (fk.getName() != null)
{
String identifier = factory.getIdentifierInAdapterCase(fk.getName());
return "ALTER TABLE " + fk.getDatastoreContainerObject().toString() + " ADD CONSTRAINT" + ' ' + fk + ' ' + "CONSTRAINT" + ' ' + identifier;
}
else
{
return "ALTER TABLE " + fk.getDatastoreContainerObject().toString() + " ADD " + fk;
}
}
/**
* Returns the appropriate SQL to add a candidate key to its table.
* It should return something like:
* <p>
* <pre>
* ALTER TABLE FOO ADD CONSTRAINT FOO_CK UNIQUE (BAZ)
* ALTER TABLE FOO ADD UNIQUE (BAZ)
* </pre>
*
* @param ck An object describing the candidate key.
* @param factory Identifier factory
* @return The text of the SQL statement.
*/
public String getAddCandidateKeyStatement(CandidateKey ck, IdentifierFactory factory)
{
if (ck.getName() != null)
{
String identifier = factory.getIdentifierInAdapterCase(ck.getName());
return "ALTER TABLE " + ck.getDatastoreContainerObject().toString() + " ADD CONSTRAINT" + ' ' + ck + ' ' + "CONSTRAINT" + ' ' + identifier;
}
else
{
return "ALTER TABLE " + ck.getDatastoreContainerObject().toString() + " ADD " + ck;
}
}
/**
* 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 FIRST 1 (CURRENT) FROM SYSTABLES";
}
/**
* Informix 11.x: We create indexes before foreign keys to avoid duplicate indexes error
* since Informix creates an index automatically
* Whether to create indexes before foreign keys.
* @return Whether to create indexes before foreign keys
**/
public boolean createIndexesBeforeForeignKeys()
{
return true;
}
/**
* Informix 11.x
* Returns the appropriate SQL expression for the JDOQL Time.getHour()
* method. It should return something like:
* <pre>HOUR(time)</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(new StringLiteral(time.getQueryExpression(),null,"%I"));
ArrayList args0 = new ArrayList();
args0.add(new StringExpression("TO_CHAR",args));
ArrayList types = new ArrayList();
types.add("INTEGER");
return new NumericExpression("CAST",args0,types);
}
/**
* Informix 11.x
* Returns the appropriate SQL expression for the JDOQL Time.getMinute()
* method. It should return something like:
* <pre>MINUTE(time)</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(new StringLiteral(time.getQueryExpression(),null,"%M"));
ArrayList args0 = new ArrayList();
args0.add(new StringExpression("TO_CHAR",args));
ArrayList types = new ArrayList();
types.add("INTEGER");
return new NumericExpression("CAST",args0,types);
}
/**
* Returns the appropriate SQL expression for the JDOQL Time.getSecond()
* method. It should return something like:
* <pre>SECOND(time)</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(new StringLiteral(time.getQueryExpression(),null,"%S"));
ArrayList args0 = new ArrayList();
args0.add(new StringExpression("TO_CHAR",args));
ArrayList types = new ArrayList();
types.add("INTEGER");
return new NumericExpression("CAST",args0,types);
}
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);
}
/**
* 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);
}
/**
* Creates a JPOX_STRPOS function for Informix
* @return the SQL JPOX_STRPOS function
*/
private String getSTRPOSFunction()
{
return "create function JPOX_STRPOS(str char(40),search char(40),from smallint) returning smallint\n"+
"define i,pos,lenstr,lensearch smallint;\n"+
"let lensearch = length(search);\n"+
"let lenstr = length(str);\n"+
"if lenstr=0 or lensearch=0 then return 0; end if;\n"+
"let pos=-1;\n"+
"for i=1+from to lenstr\n"+
"if substr(str,i,lensearch)=search then\n"+
"let pos=i;\n"+
"exit for;\n"+
"end if;\n"+
"end for;\n"+
"return pos;\n"+
"end function;";
}
/**
* DROP a JPOX_STRPOS function for Informix
* @return the SQL JPOX_STRPOS function
*/
private String getSTRPOSDropFunction()
{
return "drop function JPOX_STRPOS;";
}
/**
* Returns the appropriate SQL expression for the JDOQL String.indexOf() method.
* It should return something like:
* <p>
* <blockquote><pre>
* STRPOS(str, substr [, pos])-1
* </pre></blockquote>
* since STRPOS 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 LOCATE
args.add(from.add(integerLiteral));
}
else
{
ScalarExpression literal = getMapping(BigInteger.class, source).newLiteral(source.getQueryExpression(), BigInteger.ZERO);
args.add(literal);
}
NumericExpression locateExpr = new NumericExpression("JPOX_STRPOS", args);
// Subtract 1 from the result of STRPOS to be consistent with Java strings
// TODO Would be nice to put this in parentheses
return new NumericExpression(locateExpr, ScalarExpression.OP_SUB, integerLiteral);
}
/**
* 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(source);
args.add(str);
ScalarExpression literal = getMapping(BigInteger.class, source).newLiteral(source.getQueryExpression(), BigInteger.ZERO);
args.add(literal);
//JPOX_STRPOS( SearchString, stringSearched, [StartPosition] )
return new BooleanExpression(new StringExpression("JPOX_STRPOS", args),ScalarExpression.OP_EQ,integerLiteral);
}
/**
* 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);
}
JavaTypeMapping m = getMapping(BigInteger.class, leftOperand);
ScalarExpression integerLiteral = m.newLiteral(leftOperand.getQueryExpression(), BigInteger.ONE);
ArrayList args = new ArrayList();
args.add(leftOperand);
args.add(rightOperand);
ScalarExpression literal = getMapping(BigInteger.class, leftOperand).newLiteral(leftOperand.getQueryExpression(), BigInteger.ZERO);
args.add(literal);
//JPOX_STRPOS( SearchString, stringSearched, [StartPosition] )
return new BooleanExpression(new StringExpression("JPOX_STRPOS", args),ScalarExpression.OP_EQ,lengthMethod((StringExpression)leftOperand).sub(lengthMethod((StringExpression)rightOperand)).add(integerLiteral).encloseWithInParentheses());
}
}