org.eclipse.persistence.platform.database.PostgreSQLPlatform Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of org.eclipse.persistence.core Show documentation
Show all versions of org.eclipse.persistence.core Show documentation
EclipseLink build based upon Git transaction ecdf3c32c4
/*
* Copyright (c) 1998, 2020 Oracle and/or its affiliates. All rights reserved.
* Copyright (c) 2019 IBM Corporation. All rights reserved.
*
* This program and the accompanying materials are made available under the
* terms of the Eclipse Public License v. 2.0 which is available at
* http://www.eclipse.org/legal/epl-2.0,
* or the Eclipse Distribution License v. 1.0 which is available at
* http://www.eclipse.org/org/documents/edl-v10.php.
*
* SPDX-License-Identifier: EPL-2.0 OR BSD-3-Clause
*/
// Contributors:
// Oracle - initial API and implementation from Oracle TopLink
// Phillip Ross - LIMIT/OFFSET syntax support
// 09/14/2011-2.3.1 Guy Pelletier
// - 357533: Allow DDL queries to execute even when Multitenant entities are part of the PU
package org.eclipse.persistence.platform.database;
import java.io.*;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Types;
import java.util.*;
import org.eclipse.persistence.internal.sessions.AbstractRecord;
import org.eclipse.persistence.internal.sessions.AbstractSession;
import org.eclipse.persistence.mappings.structures.ObjectRelationalDatabaseField;
import org.eclipse.persistence.queries.StoredProcedureCall;
import org.eclipse.persistence.exceptions.ValidationException;
import org.eclipse.persistence.expressions.ExpressionOperator;
import org.eclipse.persistence.internal.databaseaccess.DatabaseCall;
import org.eclipse.persistence.internal.databaseaccess.DatasourceCall;
import org.eclipse.persistence.internal.databaseaccess.FieldTypeDefinition;
import org.eclipse.persistence.internal.expressions.ExpressionSQLPrinter;
import org.eclipse.persistence.internal.expressions.RelationExpression;
import org.eclipse.persistence.internal.expressions.SQLSelectStatement;
import org.eclipse.persistence.internal.helper.*;
import org.eclipse.persistence.queries.SQLCall;
import org.eclipse.persistence.queries.ValueReadQuery;
import org.eclipse.persistence.tools.schemaframework.FieldDefinition;
/**
*
* Purpose: Provides Postgres specific behavior.
*
* Responsibilities:
*
* - Native SQL for Date, Time, {@literal &} Timestamp.
*
- Native sequencing.
*
- Mapping of class types to database types for the schema framework.
*
- Pessimistic locking.
*
- Platform specific operators.
*
- LIMIT/OFFSET query syntax for select statements.
*
*
* @since OracleAS TopLink 10g (10.1.3)
*/
public class PostgreSQLPlatform extends DatabasePlatform {
private static final String LIMIT = " LIMIT ";
private static final String OFFSET = " OFFSET ";
public PostgreSQLPlatform() {
super();
this.cursorCode = 1111; //jdbc.Types.OTHER - PostGreSQL expects this for refCursor types
this.pingSQL = "SELECT 1";
}
/**
* Return the JDBC type for the Java type. For some reason PostgreSQL does
* not seem to like the JDBC Blob type (PostgreSQL 8.2).
*/
@Override
public int getJDBCType(Class javaType) {
if (javaType == ClassConstants.BLOB) {
return Types.LONGVARBINARY;
}
return super.getJDBCType(javaType);
}
/**
* Appends a Boolean value. Refer to :
* http://www.postgresql.org/docs/8.0/static/datatype-boolean.html In
* PostgreSQL the following are the values that are value for a boolean
* field Valid literal values for the "true" state are: TRUE, 't', 'true',
* 'y', 'yes', '1' Valid literal values for the false" state are : FALSE,
* 'f', 'false', 'n', 'no', '0'
*
* To be consistent with the other data platforms we are using the values
* '1' and '0' for true and false states of a boolean field.
*/
@Override
protected void appendBoolean(Boolean bool, Writer writer) throws IOException {
if (bool.booleanValue()) {
writer.write("\'1\'");
} else {
writer.write("\'0\'");
}
}
/**
* INTERNAL: Use the JDBC maxResults and firstResultIndex setting to compute
* a value to use when limiting the results of a query in SQL. These limits
* tend to be used in two ways.
*
* 1. MaxRows is the index of the last row to be returned (like JDBC
* maxResults) 2. MaxRows is the number of rows to be returned
*
* PostGreSQL uses case #2 and therefore the maxResults has to be altered
* based on the firstResultIndex
*
* @param firstResultIndex
* @param maxResults
*
* @see org.eclipse.persistence.platform.database.MySQLPlatform
*/
@Override
public int computeMaxRowsForSQL(int firstResultIndex, int maxResults) {
return maxResults - ((firstResultIndex >= 0) ? firstResultIndex : 0);
}
/**
* INTERNAL: Initialize any platform-specific operators
*/
@Override
protected void initializePlatformOperators() {
super.initializePlatformOperators();
addOperator(ExpressionOperator.simpleLogicalNoParens(ExpressionOperator.Concat, "||"));
addOperator(ExpressionOperator.simpleTwoArgumentFunction(ExpressionOperator.Nvl, "COALESCE"));
addOperator(operatorLocate());
addOperator(operatorLocate2());
addOperator(toNumberOperator());
addOperator(regexpOperator());
}
/**
* INTERNAL:
* Create the ~ operator.
* REGEXP allows for comparison through regular expression.
*/
public static ExpressionOperator regexpOperator() {
ExpressionOperator result = new ExpressionOperator();
result.setSelector(ExpressionOperator.Regexp);
result.setType(ExpressionOperator.FunctionOperator);
Vector v = NonSynchronizedVector.newInstance(3);
v.add("");
v.add(" ~ ");
v.add("");
result.printsAs(v);
result.bePrefix();
result.setNodeClass(ClassConstants.FunctionExpression_Class);
v = NonSynchronizedVector.newInstance(2);
v.add(".regexp(");
v.add(")");
result.printsJavaAs(v);
return result;
}
/**
* INTERNAL: Postgres to_number has two arguments, as fix format argument.
*/
protected ExpressionOperator toNumberOperator() {
ExpressionOperator exOperator = new ExpressionOperator();
exOperator.setType(ExpressionOperator.FunctionOperator);
exOperator.setSelector(ExpressionOperator.ToNumber);
Vector v = org.eclipse.persistence.internal.helper.NonSynchronizedVector.newInstance(2);
v.addElement("TO_NUMBER(");
v.addElement(", '999999999.9999')");
exOperator.printsAs(v);
exOperator.bePrefix();
exOperator.setNodeClass(ClassConstants.FunctionExpression_Class);
return exOperator;
}
/**
* INTERNAL: This method returns the query to select the timestamp from the
* server for Postgres.
*/
@Override
public ValueReadQuery getTimestampQuery() {
if (this.timestampQuery == null) {
this.timestampQuery = new ValueReadQuery();
this.timestampQuery.setSQLString("SELECT NOW()");
this.timestampQuery.setAllowNativeSQLQuery(true);
}
return this.timestampQuery;
}
/**
* This method is used to print the output parameter token when stored
* procedures are called
*/
@Override
public String getInOutputProcedureToken() {
return "OUT";
}
/**
* This is required in the construction of the stored procedures with output
* parameters
*/
@Override
public boolean shouldPrintOutputTokenAtStart() {
// TODO: Check with the reviewer where this is used
return true;
}
/**
* Calling a stored procedure query on PostgreSQL with no output parameters
* always returns true from an execute call regardless if a result set is
* returned or not. This flag will help avoid throwing a JPA mandated
* exception on an executeUpdate call (which calls jdbc execute and checks
* the return value to ensure no results sets are returned (true))
*
* @see PostgreSQLPlatform
*/
@Override
public boolean isJDBCExecuteCompliant() {
return false;
}
/**
* INTERNAL: Answers whether platform is Postgres.
*/
@Override
public boolean isPostgreSQL() {
return true;
}
/**
* INTERNAL:
*/
@Override
protected String getCreateTempTableSqlSuffix() {
return " ON COMMIT DROP";
}
/**
* INTERNAL: Indicates whether locking OF clause should print alias for
* field. Example: on Oracle platform (method returns false): SELECT
* ADDRESS_ID, ... FROM ADDRESS T1 WHERE (T1.ADDRESS_ID = ?) FOR UPDATE OF
* T1.ADDRESS_ID on Postgres platform (method returns true): SELECT
* ADDRESS_ID, ... FROM ADDRESS T1 WHERE (T1.ADDRESS_ID = ?) FOR UPDATE OF
* T1
*/
@Override
public boolean shouldPrintAliasForUpdate() {
return true;
}
/**
* INTERNAL: Indicates whether the platform supports identity.
*/
@Override
public boolean supportsIdentity() {
return true;
}
/**
* INTERNAL: Returns query used to read back the value generated by
* Identity. This method is called when identity NativeSequence is
* connected, the returned query used until the sequence is disconnected. If
* the platform supportsIdentity then (at least) one of
* buildSelectQueryForIdentity methods should return non-null query.
*/
@Override
public ValueReadQuery buildSelectQueryForIdentity() {
ValueReadQuery selectQuery = new ValueReadQuery();
selectQuery.setSQLString("select lastval()");
return selectQuery;
}
/**
* INTERNAL: Indicates whether the platform supports sequence objects.
*/
@Override
public boolean supportsSequenceObjects() {
return true;
}
/**
* INTERNAL: Returns query used to read value generated by sequence object
* (like Oracle sequence). This method is called when sequence object
* NativeSequence is connected, the returned query used until the sequence
* is disconnected. If the platform supportsSequenceObjects then (at least)
* one of buildSelectQueryForSequenceObject methods should return non-null
* query.
*/
@Override
public ValueReadQuery buildSelectQueryForSequenceObject(String qualifiedSeqName, Integer size) {
return new ValueReadQuery("select nextval(\'" + qualifiedSeqName + "\')");
}
/**
* INTERNAL: Append the receiver's field 'identity' constraint clause to a
* writer.
*/
@Override
public void printFieldIdentityClause(Writer writer) throws ValidationException {
try {
writer.write(" SERIAL");
} catch (IOException ioException) {
throw ValidationException.fileError(ioException);
}
}
@Override
protected Hashtable buildFieldTypes() {
Hashtable fieldTypeMapping = new Hashtable();
fieldTypeMapping.put(Boolean.class, new FieldTypeDefinition("BOOLEAN", false));
fieldTypeMapping.put(Integer.class, new FieldTypeDefinition("INTEGER", false));
fieldTypeMapping.put(Long.class, new FieldTypeDefinition("BIGINT", false));
fieldTypeMapping.put(Float.class, new FieldTypeDefinition("FLOAT", false));
fieldTypeMapping.put(Double.class, new FieldTypeDefinition("FLOAT", false));
fieldTypeMapping.put(Short.class, new FieldTypeDefinition("SMALLINT", false));
fieldTypeMapping.put(Byte.class, new FieldTypeDefinition("SMALLINT", false));
fieldTypeMapping.put(java.math.BigInteger.class, new FieldTypeDefinition("BIGINT", false));
fieldTypeMapping.put(java.math.BigDecimal.class, new FieldTypeDefinition("DECIMAL", 38));
fieldTypeMapping.put(Number.class, new FieldTypeDefinition("DECIMAL", 38));
fieldTypeMapping.put(String.class, new FieldTypeDefinition("VARCHAR", DEFAULT_VARCHAR_SIZE));
fieldTypeMapping.put(Character.class, new FieldTypeDefinition("CHAR", 1));
fieldTypeMapping.put(Byte[].class, new FieldTypeDefinition("BYTEA", false));
fieldTypeMapping.put(Character[].class, new FieldTypeDefinition("TEXT", false));
fieldTypeMapping.put(byte[].class, new FieldTypeDefinition("BYTEA", false));
fieldTypeMapping.put(char[].class, new FieldTypeDefinition("TEXT", false));
fieldTypeMapping.put(java.sql.Blob.class, new FieldTypeDefinition("BYTEA"));
fieldTypeMapping.put(java.sql.Clob.class, new FieldTypeDefinition("TEXT", false));
fieldTypeMapping.put(java.sql.Date.class, new FieldTypeDefinition("DATE", false));
fieldTypeMapping.put(java.sql.Time.class, new FieldTypeDefinition("TIME", false));
fieldTypeMapping.put(java.sql.Timestamp.class, new FieldTypeDefinition("TIMESTAMP", false));
fieldTypeMapping.put(java.time.LocalDate.class, new FieldTypeDefinition("DATE", false));
fieldTypeMapping.put(java.time.LocalDateTime.class, new FieldTypeDefinition("TIMESTAMP", false));
fieldTypeMapping.put(java.time.LocalTime.class, new FieldTypeDefinition("TIME", false));
fieldTypeMapping.put(java.time.OffsetDateTime.class, new FieldTypeDefinition("TIMESTAMP", false));
fieldTypeMapping.put(java.time.OffsetTime.class, new FieldTypeDefinition("TIME", false));
return fieldTypeMapping;
}
/**
* INTERNAL: Override the default locate operator.
*/
protected ExpressionOperator operatorLocate() {
ExpressionOperator result = new ExpressionOperator();
result.setSelector(ExpressionOperator.Locate);
Vector v = new Vector(3);
v.addElement("STRPOS(");
v.addElement(", ");
v.addElement(")");
result.printsAs(v);
result.bePrefix();
result.setNodeClass(RelationExpression.class);
return result;
}
/**
* INTERNAL: Override the default locate operator.
*/
protected ExpressionOperator operatorLocate2() {
ExpressionOperator operator = new ExpressionOperator();
operator.setSelector(ExpressionOperator.Locate2);
Vector v = NonSynchronizedVector.newInstance(2);
v.add("COALESCE(NULLIF(STRPOS(SUBSTRING(");
v.add(" FROM ");
v.add("), ");
v.add("), 0) - 1 + ");
v.add(", 0)");
operator.printsAs(v);
operator.bePrefix();
int[] argumentIndices = new int[4];
argumentIndices[0] = 0;
argumentIndices[1] = 2;
argumentIndices[2] = 1;
argumentIndices[3] = 2;
operator.setArgumentIndices(argumentIndices);
operator.setNodeClass(RelationExpression.class);
return operator;
}
/**
* INTERNAL:
*/
@Override
public boolean supportsLocalTempTables() {
return true;
}
/**
* INTERNAL:
*/
@Override
protected String getCreateTempTableSqlPrefix() {
return "CREATE LOCAL TEMPORARY TABLE ";
}
/**
* INTERNAL: returns the maximum number of characters that can be used in a
* field name on this platform.
*/
@Override
public int getMaxFieldNameSize() {
// The system uses no more than NAMEDATALEN-1 characters of an
// identifier; longer names can be written in commands,
// but they will be truncated. By default, NAMEDATALEN is 64 so the
// maximum identifier length is 63 (but at the time PostgreSQL
// is built, NAMEDATALEN can be changed in src/include/postgres_ext.h).
// http://www.postgresql.org/docs/7.3/interactive/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS
return 63;
}
// http://www.postgresql.org/docs/8.1/interactive/plpgsql-declarations.html
/**
* INTERNAL: Used for sp calls.
*/
@Override
public String getProcedureBeginString() {
return "AS $$ BEGIN ";
}
/**
* INTERNAL: Used for sp calls.
*/
@Override
public String getProcedureEndString() {
return "; END ; $$ LANGUAGE plpgsql;";
}
/**
* INTERNAL: Used for sp calls. PostGreSQL uses a different method for executing StoredProcedures than other platforms.
*/
@Override
public String buildProcedureCallString(StoredProcedureCall call, AbstractSession session, AbstractRecord row) {
StringWriter tailWriter = new StringWriter();
StringWriter writer = new StringWriter();
boolean outParameterFound = false;;
tailWriter.write(call.getProcedureName());
tailWriter.write("(");
int indexFirst = call.getFirstParameterIndexForCallString();
int size = call.getParameters().size();
String nextBindString = "?";
for (int index = indexFirst; index < size; index++) {
String name = call.getProcedureArgumentNames().get(index);
Object parameter = call.getParameters().get(index);
Integer parameterType = call.getParameterTypes().get(index);
// If the argument is optional and null, ignore it.
if (!call.hasOptionalArguments() || !call.getOptionalArguments().contains(parameter) || (row.get(parameter) != null)) {
if (!DatasourceCall.isOutputParameterType(parameterType)) {
tailWriter.write(nextBindString);
nextBindString = ", ?";
} else {
if (outParameterFound) {
//multiple outs found
throw ValidationException.multipleOutParamsNotSupported(Helper.getShortClassName(this), call.getProcedureName());
}
outParameterFound = true; //PostGreSQL uses a very different header to execute when there are out params
}
}
}
tailWriter.write(")");
if (outParameterFound) {
writer.write("{?= CALL ");
tailWriter.write("}");
} else {
writer.write("SELECT * FROM ");
}
writer.write(tailWriter.toString());
return writer.toString();
}
/**
* INTERNAL Used for stored function calls.
*/
@Override
public String getAssignmentString() {
return ":= ";
}
/**
* Allows DROP TABLE to cascade dropping of any dependent constraints if the database supports this option.
*/
@Override
public String getDropCascadeString() {
return " CASCADE";
}
@Override
public void printFieldTypeSize(Writer writer, FieldDefinition field, FieldTypeDefinition fieldType, boolean shouldPrintFieldIdentityClause) throws IOException {
if (!shouldPrintFieldIdentityClause) {
super.printFieldTypeSize(writer, field, fieldType, shouldPrintFieldIdentityClause);
}
}
@Override
public void printFieldUnique(Writer writer, boolean shouldPrintFieldIdentityClause) throws IOException {
if (!shouldPrintFieldIdentityClause) {
super.printFieldUnique(writer, shouldPrintFieldIdentityClause);
}
}
/**
* JDBC defines and outer join syntax, many drivers do not support this. So
* we normally avoid it.
*/
@Override
public boolean shouldUseJDBCOuterJoinSyntax() {
return false;
}
/**
* INTERNAL: Override this method if the platform supports sequence objects
* and it's possible to alter sequence object's increment in the database.
*/
@Override
public boolean isAlterSequenceObjectSupported() {
return true;
}
/**
* Print the pagination SQL using Postgres syntax
* " LIMIT {@literal OFFSET }".
*/
@Override
public void printSQLSelectStatement(DatabaseCall call, ExpressionSQLPrinter printer, SQLSelectStatement statement) {
int max = 0;
if (statement.getQuery() != null) {
max = statement.getQuery().getMaxRows();
}
if (max <= 0 || !(this.shouldUseRownumFiltering())) {
super.printSQLSelectStatement(call, printer, statement);
return;
}
statement.setUseUniqueFieldAliases(true);
call.setFields(statement.printSQL(printer));
printer.printString(LIMIT);
printer.printParameter(DatabaseCall.MAXROW_FIELD);
printer.printString(OFFSET);
printer.printParameter(DatabaseCall.FIRSTRESULT_FIELD);
call.setIgnoreFirstRowSetting(true);
call.setIgnoreMaxResultsSetting(true);
}
/**
* INTERNAL: May need to override this method if the platform supports
* temporary tables and the generated sql doesn't work. Write an sql string
* for updating the original table from the temporary table. Precondition:
* supportsTempTables() == true. Precondition: pkFields and assignFields
* don't intersect.
*
* @param writer for writing the sql
* @param table is original table for which temp table is
* created.
* @param pkFields - primary key fields for the original
* table.
* @param assignedFields - fields to be assigned a new value.
*/
@Override
public void writeUpdateOriginalFromTempTableSql(Writer writer, DatabaseTable table, Collection pkFields, Collection assignedFields) throws IOException {
writer.write("UPDATE ");
String tableName = table.getQualifiedNameDelimited(this);
writer.write(tableName);
writer.write(" SET ");
String tempTableName = getTempTableForTable(table).getQualifiedNameDelimited(this);
boolean isFirst = true;
Iterator itFields = assignedFields.iterator();
while (itFields.hasNext()) {
if (isFirst) {
isFirst = false;
} else {
writer.write(", ");
}
DatabaseField field = (DatabaseField) itFields.next();
String fieldName = field.getNameDelimited(this);
writer.write(fieldName);
writer.write(" = (SELECT ");
writer.write(fieldName);
writer.write(" FROM ");
writer.write(tempTableName);
writeAutoJoinWhereClause(writer, null, tableName, pkFields, this);
writer.write(")");
}
writer.write(" WHERE EXISTS(SELECT ");
writer.write(((DatabaseField) pkFields.iterator().next()).getNameDelimited(this));
writer.write(" FROM ");
writer.write(tempTableName);
writeAutoJoinWhereClause(writer, null, tableName, pkFields, this);
writer.write(")");
}
/**
* INTERNAL:
* Postgres has a returning clause.
*/
public boolean canBuildCallWithReturning() {
return true;
}
/**
* INTERNAL:
* Uses the returning clause on Postgres.
*/
public DatabaseCall buildCallWithReturning(SQLCall sqlCall, Vector returnFields) {
SQLCall call = new SQLCall();
call.setParameters(sqlCall.getParameters());
call.setParameterTypes(sqlCall.getParameterTypes());
call.returnOneRow();
Writer writer = new CharArrayWriter(sqlCall.getSQLString().length() + 32);
try {
writer.write(sqlCall.getSQLString());
writer.write(" RETURNING ");
for (int i = 0; i < returnFields.size(); i++) {
DatabaseField field = (DatabaseField)returnFields.elementAt(i);
writer.write(field.getNameDelimited(this));
if ((i + 1) < returnFields.size()) {
writer.write(", ");
}
}
call.setQueryString(writer.toString());
} catch (IOException exception) {
throw ValidationException.fileError(exception);
}
return call;
}
@Override
protected void setNullFromDatabaseField(DatabaseField databaseField, PreparedStatement statement, int index) throws SQLException {
// Substituted null value for the corresponding DatabaseField.
// Cannot bind null through set object, so we must compute the type, this is not good.
// Fix for bug 2730536: for ARRAY/REF/STRUCT types must pass in the
// user defined type to setNull as well.
if (databaseField instanceof ObjectRelationalDatabaseField) {
ObjectRelationalDatabaseField field = (ObjectRelationalDatabaseField)databaseField;
//Fix for bug 537657: Inserting empty or null varchar arrays doesn't work with PostgreSQL since driver version 42.2.4
if (field.getSqlType() == Types.ARRAY) {
statement.setNull(index, field.getSqlType());
} else {
statement.setNull(index, field.getSqlType(), field.getSqlTypeName());
}
} else {
int jdbcType = getJDBCTypeForSetNull(databaseField);
statement.setNull(index, jdbcType);
}
}
}