/**********************************************************************
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 Andy Jefferson - added sequence methods
2004 Andy Jefferson - patch from Rey Amarego for "escape" method
2004 Andy Jefferson - update to cater for bitReallyBoolean (Tibor Kiss)
...
**********************************************************************/
package org.jpox.store.rdbms.adapter;
import java.math.BigInteger;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Hashtable;
import org.jpox.UserTransaction;
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.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.SqlTemporalExpression;
import org.jpox.store.mapped.expression.TableExprAsJoins;
import org.jpox.store.mapped.mapping.JavaTypeMapping;
import org.jpox.store.rdbms.Column;
import org.jpox.store.rdbms.JDBCUtils;
import org.jpox.store.rdbms.columninfo.ColumnInfo;
import org.jpox.store.rdbms.columninfo.PostgreSQLColumnInfo;
import org.jpox.store.rdbms.key.PrimaryKey;
import org.jpox.store.rdbms.table.Table;
import org.jpox.store.rdbms.typeinfo.ForeignKeyInfo;
import org.jpox.store.rdbms.typeinfo.PostgreSQLForeignKeyInfo;
import org.jpox.store.rdbms.typeinfo.PostgreSQLTypeInfo;
import org.jpox.store.rdbms.typeinfo.TypeInfo;
import org.jpox.util.JPOXLogger;
/**
* Provides methods for adapting SQL language elements to the PostgreSQL
* database.
*
* @version $Revision: 1.52 $
*/
public class PostgreSQLAdapter extends DatabaseAdapter
{
/** List of Postgresql keywords that aren't in SQL92, SQL99 */
public static final String POSTGRESQL_RESERVED_WORDS =
"ALL,ANALYSE,ANALYZE,DO,FREEZE,ILIKE,ISNULL,OFFSET,PLACING,VERBOSE";
protected Hashtable psqlTypes;
/**
* Constructor.
* @param metadata MetaData for the DB
*/
public PostgreSQLAdapter(DatabaseMetaData metadata)
{
super(metadata);
if (datastoreMajorVersion < 7)
{
// TODO Localise this message
throw new JPOXDataStoreException("PostgreSQL version is " + datastoreMajorVersion + '.' + datastoreMinorVersion + ", 7.0 or later required");
}
else if (datastoreMajorVersion == 7)
{
if (datastoreMinorVersion <= 2)
{
// The driver correctly reports the max table name length as 32.
// However, constraint names are apparently limited to 31. In
// this case we get better looking names by simply treating them
// all as limited to 31.
--maxTableNameLength;
--maxConstraintNameLength;
--maxIndexNameLength;
}
}
reservedKeywords.addAll(parseKeywordList(POSTGRESQL_RESERVED_WORDS));
// Add on any missing JDBC types
// If the PostgreSQL JDBC driver does not provide info for the CHAR type we fake it as "char" (e.g PSQL 8.1.405)
TypeInfo ti = new PostgreSQLTypeInfo("char",
(short)Types.CHAR,
65000,
null,
null,
null,
0,
false,
(short)3,
false,
false,
false,
"char",
(short)0,
(short)0,
10);
addTypeInfo((short)Types.CHAR, ti, true);
ti = new PostgreSQLTypeInfo("text",
(short)Types.CLOB,
9,
null,
null,
null,
0,
false,
(short)3,
false,
false,
false,
null,
(short)0,
(short)0,
10);
addTypeInfo((short)Types.CLOB, ti, true);
ti = new PostgreSQLTypeInfo("BYTEA",
(short)Types.BLOB,
9,
null,
null,
null,
0,
false,
(short)3,
false,
false,
false,
null,
(short)0,
(short)0,
10);
addTypeInfo((short)Types.BLOB, ti, true);
}
/**
* Accessor for the vendor id.
* @return The vendor id.
**/
public String getVendorID()
{
return "postgresql";
}
public TypeInfo newTypeInfo(ResultSet rs)
{
TypeInfo ti = new PostgreSQLTypeInfo(rs);
/*
* Since PostgreSQL supports many user defined data types and uses
* many type aliases the default methods have trouble finding the
* right associations between JDBC and PostgreSQL data types. We
* filter the returned type info to be sure we use the appropriate
* base PostgreSQL types for the important JDBC types.
*/
if (psqlTypes == null)
{
psqlTypes = new Hashtable();
psqlTypes.put("" + Types.BIT, "bool");
psqlTypes.put("" + Types.TIMESTAMP, "timestamptz");
psqlTypes.put("" + Types.BIGINT, "int8");
psqlTypes.put("" + Types.CHAR, "char");
psqlTypes.put("" + Types.DATE, "date");
psqlTypes.put("" + Types.DOUBLE, "float8");
psqlTypes.put("" + Types.INTEGER, "int4");
psqlTypes.put("" + Types.LONGVARCHAR, "text");
psqlTypes.put("" + Types.CLOB, "text");
psqlTypes.put("" + Types.BLOB, "bytea");
psqlTypes.put("" + Types.NUMERIC, "numeric");
psqlTypes.put("" + Types.REAL, "float4");
psqlTypes.put("" + Types.SMALLINT, "int2");
psqlTypes.put("" + Types.TIME, "time");
psqlTypes.put("" + Types.VARCHAR, "varchar");
psqlTypes.put("" + Types.OTHER, "***TOTALRUBBISH***");
// PostgreSQL provides 2 types for "char" mappings - "char" and "bpchar". PostgreSQL recommend
// bpchar for default usage, but sadly you cannot say "bpchar(200)" in an SQL statement. Due to
// this we use "char" since you can say "char(100)" (and internally in PostgreSQL it becomes bpchar)
// PostgreSQL 8.1 JDBC driver somehow puts "char" as Types.OTHER rather than Types.CHAR ! so this is
// faked in createTypeInfo() above.
// PostgreSQL (7.3, 7.4) doesn't provide a SQL type to map to JDBC types FLOAT, DECIMAL, BLOB, BOOLEAN
}
Object obj = psqlTypes.get("" + ti.dataType);
if (obj != null)
{
String psql_type_name=(String)obj;
// We don't support this JDBC type using *this* PostgreSQL SQL type
if (!ti.typeName.equalsIgnoreCase(psql_type_name))
{
JPOXLogger.DATASTORE_SCHEMA.debug(LOCALISER.msg("051007", ti.typeName,
JDBCUtils.getNameForJDBCType(ti.dataType)));
return null;
}
}
return ti;
}
public ColumnInfo newColumnInfo(ResultSet rs)
{
return new PostgreSQLColumnInfo(rs);
}
public ForeignKeyInfo newForeignKeyInfo(ResultSet rs)
{
return new PostgreSQLForeignKeyInfo(rs);
}
// ------------------------------------ Schema Methods -------------------------------------
/**
* Accessor for whether the adapter supports the transaction isolation level
* Postgresql doesnt support TRANSACTION_NONE.
* @param isolationLevel the isolation level
* @return Whether the transaction isolation level setting is supported.
*/
public boolean supportsTransactionIsolationLevel(int isolationLevel)
{
if (isolationLevel != UserTransaction.TRANSACTION_NONE)
{
return true;
}
return false;
}
/**
* Accessor for whether a JDBC Types.BIT is really mapped in the datastore
* to a boolean field. Postgresql treats Types.BIT as boolean
* @return Whether it is really a boolean
*/
public boolean isBitReallyBoolean()
{
return true;
}
/**
* Some databases store character strings in CHAR(XX) columns and when read back in have been padded
* with spaces.
* @return whether this database pads char(XX) columns with spaces
*/
public boolean getCharColumnsPaddedWithSpaces()
{
return true;
}
/**
* Whether the database server supports persist of an unassigned character ("0x0").
* If not, any unassigned character will be replaced by " " (space) on persist.
* @return Whether we support persisting an unassigned char.
*/
public boolean getSupportsPersistOfUnassignedChar()
{
return false;
}
/**
* 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 INSERT statement to use when inserting into a table and we dont want to
* specify columns. 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).
* Postgresql expects something like
* <pre>
* INSERT INTO tbl VALUES(DEFAULT)
* </pre>
* @param table The table
* @return The statement for the INSERT
*/
public String getInsertStatementForNoColumns(Table table)
{
return "INSERT INTO " + table.toString() + " VALUES (DEFAULT)";
}
/**
* 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 using DISTINCT when using SELECT ... FOR UPDATE.
* @return whether the datastore supports DISTINCT in same statement as FOR UPDATE
**/
public boolean supportsDistinctWithSelectForUpdate()
{
return false;
}
/**
* PostgreSQL has supported the ALTER TABLE tbl-name DROP CONSTRAINT ...
* since 7.2
* @return Whether it supports the ALTER TABLE DROP CONSTRAINT syntax
**/
public boolean supportsAlterTableDropConstraint()
{
if (datastoreMajorVersion < 7 ||
(datastoreMajorVersion == 7 && datastoreMinorVersion < 2))
{
return false;
}
else
{
return true;
}
}
/**
* PostgreSQL allows specification of PRIMARY KEY in the CREATE TABLE, so
* we need nothing here.
* @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;
}
/**
* Whether the datastore supports specification of the primary key in
* CREATE TABLE statements.
* @return Whether it allows "PRIMARY KEY ..."
*/
public boolean supportsPrimaryKeyInCreateStatements()
{
return true;
}
/**
* Accessor for the statement for dropping a table.
* PostgreSQL has supported the DROP TABLE tbl-name CASCADE since 7.3.
* @param table The table to drop.
* @return The statement for dropping a table.
**/
public String getDropTableStatement(DatastoreContainerObject table)
{
/* DROP TABLE t CASCADE is supported beginning in 7.3 */
if (datastoreMajorVersion < 7 ||
(datastoreMajorVersion == 7 && datastoreMinorVersion < 3))
{
return "DROP TABLE " + table.toString();
}
else
{
return "DROP TABLE " + table.toString() + " CASCADE";
}
}
// ---------------------------- Identity 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 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)
{
StringBuffer stmt=new StringBuffer("SELECT currval('");
// PostgreSQL creates a sequence for each SERIAL column with name of the form "{table}_seq"
// in the current catalog/schema. PostgreSQL doesn't use catalog so ignore that
if (table.getSchemaName() != null)
{
stmt.append(JDBCUtils.getIdentifierNameStripped(table.getSchemaName(), this));
stmt.append(getCatalogSeparator());
}
String tableName = table.getIdentifier().toString();
boolean quoted = tableName.startsWith(getIdentifierQuoteString());
if (quoted)
{
stmt.append(getIdentifierQuoteString());
}
stmt.append(JDBCUtils.getIdentifierNameStripped(tableName, this));
stmt.append("_");
stmt.append(JDBCUtils.getIdentifierNameStripped(columnName, this));
stmt.append("_seq");
if (quoted)
{
stmt.append(getIdentifierQuoteString());
}
stmt.append("')");
return stmt.toString();
}
/**
* 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;
}
// ---------------------------- 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(" 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(" CACHE 1");
}
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 nextval('");
stmt.append(sequence_name);
stmt.append("')");
return stmt.toString();
}
// ------------------------------- Query Support ---------------------------
public LogicSetExpression newTableExpression(QueryExpression qs, DatastoreContainerObject table, DatastoreIdentifier rangeVar)
{
return new TableExprAsJoins(qs, table, rangeVar);
}
/**
* Whether the datastore will support setting the query fetch size to the supplied value.
* @param size The value to set to
* @return Whether it is supported.
*/
public boolean supportsQueryFetchSize(int size)
{
if (driverMajorVersion > 7)
{
// Supported for Postgresql 8
return true;
}
else
{
return false;
}
}
/**
* Method to return the SQL to append to the WHERE 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 getRangeByLimitWhereClause(long offset, long count)
{
String str = "";
if (count > 0)
{
str += " LIMIT " + count;
}
if (offset >= 0)
{
str += " OFFSET " + offset;
}
return str;
}
/**
* Accessor for whether the SQL extensions CUBE, ROLLUP are supported.
* @return Whether the SQL extensions CUBE, ROLLUP are supported.
*/
public boolean supportsAnalysisMethods()
{
return false;
}
/**
* The character for escaping patterns.
* @return Escape character(s)
**/
public String getEscapePatternExpression()
{
return "ESCAPE '\\\\'";
}
/**
* 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)
{
// TODO Find a function in Postgresql that supports the "from" position for searching
throw new JPOXUserException("PostgreSQL doesnt currently provide a function for providing indexOf(str, from). Your workaround is to miss off the 'from' position");
}
NumericExpression locateExpr = new NumericExpression("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);
}
/**
* Returns the appropriate SQL expression for the JDOQL Date.getDay()
* method. It should return something like:
* <pre>date_part("day",date)</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();
JavaTypeMapping m = getMapping(String.class, date);
ScalarExpression submethodLiteral = m.newLiteral(date.getQueryExpression(), "day");
args.add(submethodLiteral);
args.add(date);
return new NumericExpression("date_part", args);
}
/**
* Returns the appropriate SQL expression for the JDOQL Date.getMonth()
* method. It should return something like:
* <pre>date_part("month",date)</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();
JavaTypeMapping m = getMapping(String.class, date);
ScalarExpression submethodLiteral = m.newLiteral(date.getQueryExpression(), "month");
args.add(submethodLiteral);
args.add(date);
// Delete one from the SQL "month" (origin=1) to be compatible with Java month (origin=0)
JavaTypeMapping m2 = getMapping(BigInteger.class, date);
ScalarExpression integerLiteral = m2.newLiteral(date.getQueryExpression(), BigInteger.ONE);
NumericExpression expr = new NumericExpression(new NumericExpression("date_part", args), 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>date_part("year",date)</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();
JavaTypeMapping m = getMapping(String.class, date);
ScalarExpression submethodLiteral = m.newLiteral(date.getQueryExpression(), "year");
args.add(submethodLiteral);
args.add(date);
return new NumericExpression("date_part", args);
}
/**
* Returns the appropriate SQL expression for the JDOQL Time.getHour()
* method. It should return something like:
* <pre>date_part("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();
JavaTypeMapping m = getMapping(String.class, time);
ScalarExpression submethodLiteral = m.newLiteral(time.getQueryExpression(), "hour");
args.add(submethodLiteral);
args.add(time);
return new NumericExpression("date_part", args);
}
/**
* Returns the appropriate SQL expression for the JDOQL Time.getMinute()
* method. It should return something like:
* <pre>date_part("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();
JavaTypeMapping m = getMapping(String.class, time);
ScalarExpression submethodLiteral = m.newLiteral(time.getQueryExpression(), "minute");
args.add(submethodLiteral);
args.add(time);
return new NumericExpression("date_part", args);
}
/**
* Returns the appropriate SQL expression for the JDOQL Time.getSecond()
* method. It should return something like:
* <pre>date_part("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();
JavaTypeMapping m = getMapping(String.class, time);
ScalarExpression submethodLiteral = m.newLiteral(time.getQueryExpression(), "second");
args.add(submethodLiteral);
args.add(time);
return new NumericExpression("date_part", args);
}
}