Package org.nuxeo.ecm.core.storage.sql.jdbc.dialect

Class DialectPostgreSQL

java.lang.Object
org.nuxeo.ecm.core.storage.sql.jdbc.dialect.Dialect
org.nuxeo.ecm.core.storage.sql.jdbc.dialect.DialectPostgreSQL
public class DialectPostgreSQL extends Dialect
PostgreSQL-specific dialect.
Author:
Florent Guillaume

  • Field Details

    • fulltextAnalyzer

      protected final String fulltextAnalyzer

    • supportsWith

      protected final boolean supportsWith

    • hierarchyCreated

      protected boolean hierarchyCreated

    • pathOptimizationsEnabled

      protected boolean pathOptimizationsEnabled

    • arrayColumnsEnabled

      protected final boolean arrayColumnsEnabled

    • childNameUniqueConstraintEnabled

      protected final boolean childNameUniqueConstraintEnabled

    • collectionUniqueConstraintEnabled

      protected final boolean collectionUniqueConstraintEnabled

    • disableVersionACL

      protected final boolean disableVersionACL

    • disableReadVersionPermission

      protected final boolean disableReadVersionPermission

    • usersSeparator

      protected String usersSeparator

    • idType

      protected final Dialect.DialectIdType idType

    • compatibilityFulltextTable

      protected boolean compatibilityFulltextTable

    • unloggedKeyword

      protected final String unloggedKeyword

    • idSequenceName

      protected String idSequenceName

    • FT_LIKE_SEP

      protected static final String FT_LIKE_SEP
      See Also:

    • FT_LIKE_COL

      protected static final String FT_LIKE_COL
      See Also:

  • Constructor Details

  • Method Details

    • getCompatibilityFulltextTable

      protected boolean getCompatibilityFulltextTable(DatabaseMetaData metadata) throws SQLException
      Throws:
      SQLException

    • toBooleanValueString

      public String toBooleanValueString(boolean bool)
      Overrides:
      toBooleanValueString in class Dialect

    • getNoColumnsInsertString

      public String getNoColumnsInsertString(Column idColumn)
      Overrides:
      getNoColumnsInsertString in class Dialect

    • getCascadeDropConstraintsString

      public String getCascadeDropConstraintsString()
      Overrides:
      getCascadeDropConstraintsString in class Dialect

    • getJDBCTypeAndString

      public Dialect.JDBCInfo getJDBCTypeAndString(ColumnType type)
      Description copied from class: Dialect
      Gets the JDBC type and string from Nuxeo's type abstraction.
      Specified by:
      getJDBCTypeAndString in class Dialect

    • isAllowedConversion

      public boolean isAllowedConversion(int expected, int actual, String actualName, int actualSize)
      Description copied from class: Dialect
      Check mismatches between expected and actual JDBC types read from database introspection.
      Overrides:
      isAllowedConversion in class Dialect

    • getGeneratedId

      public Serializable getGeneratedId(Connection connection) throws SQLException
      Description copied from class: Dialect
      Gets a generated id if so configured, otherwise returns null.
      Overrides:
      getGeneratedId in class Dialect
      Throws:
      SQLException

    • setId

      public void setId(PreparedStatement ps, int index, Serializable value) throws SQLException
      Description copied from class: Dialect
      Sets a prepared statement value that is a Nuxeo main id (usually UUID).
      Overrides:
      setId in class Dialect
      Parameters:
      ps - the prepared statement
      index - the parameter index in the prepared statement
      value - the value to set
      Throws:
      SQLException

    • getId

      public Serializable getId(ResultSet rs, int index) throws SQLException
      Throws:
      SQLException

    • setToPreparedStatement

      public void setToPreparedStatement(PreparedStatement ps, int index, Serializable value, Column column) throws SQLException
      Specified by:
      setToPreparedStatement in class Dialect
      Throws:
      SQLException

    • getFromResultSet

      public Serializable getFromResultSet(ResultSet rs, int index, Column column) throws SQLException
      Specified by:
      getFromResultSet in class Dialect
      Throws:
      SQLException

    • getMaxNameSize

      protected int getMaxNameSize()
      Overrides:
      getMaxNameSize in class Dialect

    • getColumnName

      public String getColumnName(String name)
      Overrides:
      getColumnName in class Dialect

    • getCustomPostCreateSqls

      public List<String> getCustomPostCreateSqls(Table table, Model model)
      Description copied from class: Dialect
      Additional things to execute after CREATE TABLE.
      Overrides:
      getCustomPostCreateSqls in class Dialect

    • getCreateFulltextIndexSql

      public String getCreateFulltextIndexSql(String indexName, String quotedIndexName, Table table, List<Column> columns, Model model)
      Description copied from class: Dialect
      Gets a CREATE INDEX statement for a fulltext index.
      Specified by:
      getCreateFulltextIndexSql in class Dialect

    • getDialectFulltextQuery

      public String getDialectFulltextQuery(String query)
      Get the dialect-specific version of a fulltext query.

      The result of this is passed to getFulltextScoredMatchInfo(java.lang.String, java.lang.String, int, org.nuxeo.ecm.core.storage.sql.jdbc.db.Column, org.nuxeo.ecm.core.storage.sql.Model, org.nuxeo.ecm.core.storage.sql.jdbc.db.Database).

      Specified by:
      getDialectFulltextQuery in class Dialect
      Parameters:
      query - the CMIS-syntax-based fulltext query string
      Returns:
      the dialect native fulltext query string

    • breakPhrases

      protected static FulltextQueryAnalyzer.FulltextQuery breakPhrases(FulltextQueryAnalyzer.FulltextQuery ft)
      Returns a fulltext query that is a superset of the original one and does not have phrase searches.

      Negative phrases (which are at AND level) are removed, positive phrases are split into ANDed words.

    • removeToplevelAndedWords

      protected static FulltextQueryAnalyzer.FulltextQuery removeToplevelAndedWords(FulltextQueryAnalyzer.FulltextQuery ft)
      Removes toplevel ANDed simple words from the query.

    • generateLikeSql

      protected static void generateLikeSql(FulltextQueryAnalyzer.FulltextQuery ft, StringBuilder sb)

    • getFulltextScoredMatchInfo

      public Dialect.FulltextMatchInfo getFulltextScoredMatchInfo(String fulltextQuery, String indexName, int nthMatch, Column mainColumn, Model model, Database database)
      Description copied from class: Dialect
      Gets the SQL information needed to do a a fulltext match, either with a direct expression in the WHERE clause, or using a join with an additional table.
      Specified by:
      getFulltextScoredMatchInfo in class Dialect

    • getMaterializeFulltextSyntheticColumn

      public boolean getMaterializeFulltextSyntheticColumn()
      Description copied from class: Dialect
      Does the fulltext synthetic column have to be materialized.
      Specified by:
      getMaterializeFulltextSyntheticColumn in class Dialect

    • getFulltextIndexedColumns

      public int getFulltextIndexedColumns()
      Description copied from class: Dialect
      Specifies what columns of the fulltext table have to be indexed.
      Specified by:
      getFulltextIndexedColumns in class Dialect
      Returns:
      0 for none, 1 for the synthetic one, 2 for the individual ones

    • getFreeVariableSetterForType

      public String getFreeVariableSetterForType(ColumnType type)
      Description copied from class: Dialect
      Gets the JDBC expression setting a free value for this column type.

      Needed for columns that need an expression around the value being set, usually for conversion (this is the case for PostgreSQL fulltext TSVECTOR columns for instance).

      Overrides:
      getFreeVariableSetterForType in class Dialect
      Parameters:
      type - the column type
      Returns:
      the expression containing a free variable

    • supportsUpdateFrom

      public boolean supportsUpdateFrom()
      Description copied from class: Dialect
      Does the dialect support UPDATE t SET ... FROM t, u WHERE ... ?
      Specified by:
      supportsUpdateFrom in class Dialect

    • doesUpdateFromRepeatSelf

      public boolean doesUpdateFromRepeatSelf()
      Description copied from class: Dialect
      When doing an UPDATE t SET ... FROM t, u WHERE ..., does the FROM clause need to repeate the updated table (t).
      Specified by:
      doesUpdateFromRepeatSelf in class Dialect

    • needsAliasForDerivedTable

      public boolean needsAliasForDerivedTable()
      Description copied from class: Dialect
      Whether a derived table (subselect in a FROM statement) needs an alias.
      Overrides:
      needsAliasForDerivedTable in class Dialect

    • supportsIlike

      public boolean supportsIlike()
      Description copied from class: Dialect
      Does the dialect support ILIKE operator
      Overrides:
      supportsIlike in class Dialect

    • supportsReadAcl

      public boolean supportsReadAcl()
      Description copied from class: Dialect
      Does the dialect support an optimized read security checks
      Overrides:
      supportsReadAcl in class Dialect

    • getPrepareUserReadAclsSql

      public String getPrepareUserReadAclsSql()
      Description copied from class: Dialect
      Gets the SQL expression to prepare the user read acls cache. This can be used to populate a table cache.
      Overrides:
      getPrepareUserReadAclsSql in class Dialect
      Returns:
      and SQL expression with one parameter (principals)

    • getReadAclsCheckSql

      public String getReadAclsCheckSql(String userIdCol)
      Description copied from class: Dialect
      Gets the expression to check if access is allowed using read acls. The dialect must suppportsReadAcl.
      Overrides:
      getReadAclsCheckSql in class Dialect
      Parameters:
      userIdCol - the quoted name of the aclr_user_map user_id column to use
      Returns:
      an SQL expression with one parameter (principals) that is true if access is allowed

    • getUpdateReadAclsSql

      public String getUpdateReadAclsSql()
      Description copied from class: Dialect
      Gets the statement to update the read acls
      Overrides:
      getUpdateReadAclsSql in class Dialect

    • getRebuildReadAclsSql

      public String getRebuildReadAclsSql()
      Description copied from class: Dialect
      Gets the statement to rebuild the wall read acls
      Overrides:
      getRebuildReadAclsSql in class Dialect

    • getSecurityCheckSql

      public String getSecurityCheckSql(String idColumnName)
      Description copied from class: Dialect
      Gets the expression to use to check security.
      Specified by:
      getSecurityCheckSql in class Dialect
      Parameters:
      idColumnName - the quoted name of the id column to use
      Returns:
      an SQL expression with two parameters (principals and permissions) that is true if access is allowed

    • supportsAncestorsTable

      public boolean supportsAncestorsTable()
      Description copied from class: Dialect
      Checks if the dialect supports an ancestors table.
      Overrides:
      supportsAncestorsTable in class Dialect

    • supportsFastDescendants

      public boolean supportsFastDescendants()
      Description copied from class: Dialect
      Checks whether Dialect.getInTreeSql(String, String) is optimized for fast results (using an ancestors or descendants table).
      Overrides:
      supportsFastDescendants in class Dialect

    • getInTreeSql

      public String getInTreeSql(String idColumnName, String id)
      Description copied from class: Dialect
      Gets the expression to use to check tree membership.
      Specified by:
      getInTreeSql in class Dialect
      Parameters:
      idColumnName - the quoted name of the id column to use
      id - the id, to check syntax with respect to specialized id column types
      Returns:
      an SQL expression with one parameters for the based id that is true if the document is under base id, or null if the query cannot match

    • getCastForArray

      protected String getCastForArray(String cast)

    • getCastForId

      protected String getCastForId(String id)

    • getUpsertSql

      public String getUpsertSql(List<Column> columns, List<Serializable> values, List<Column> outColumns, List<Serializable> outValues)
      Description copied from class: Dialect
      Gets the SQL string for an UPSERT expression.
      Specified by:
      getUpsertSql in class Dialect
      Parameters:
      columns - the columns to upsert (first one being the key column)
      values - the values to upsert (first one being the key value)
      outColumns - the columns to use in the prepared statement
      outValues - the values to use in the prepared statement

    • getInsertOnConflictDoNothingSql

      public String getInsertOnConflictDoNothingSql(List<Column> columns, List<Serializable> values, List<Column> outColumns, List<Serializable> outValues)
      Description copied from class: Dialect
      Gets the SQL string for an INSERT ... ON CONFLICT DO NOTHING expression.
      Overrides:
      getInsertOnConflictDoNothingSql in class Dialect
      Parameters:
      columns - the columns to insert (first one being the key column)
      values - the values to insert (first one being the key value)
      outColumns - the columns to use in the prepared statement
      outValues - the values to use in the prepared statement

    • getMatchMixinType

      public String getMatchMixinType(Column mixinsColumn, String mixin, boolean positive, String[] returnParam)
      Description copied from class: Dialect
      Gets the SQL fragment to match a mixin type.
      Overrides:
      getMatchMixinType in class Dialect

    • supportsSysNameArray

      public boolean supportsSysNameArray()
      Description copied from class: Dialect
      Checks if the dialect supports storing arrays of system names (for mixins for instance).
      Overrides:
      supportsSysNameArray in class Dialect

    • supportsArrays

      public boolean supportsArrays()
      Description copied from class: Dialect
      Does the dialect support passing ARRAY values (to stored procedures mostly).

      If not, we'll simulate them using a string and a separator.

      Overrides:
      supportsArrays in class Dialect
      Returns:
      true if ARRAY values are supported

    • supportsArrayColumns

      public boolean supportsArrayColumns()
      Description copied from class: Dialect
      Does the dialect support storing arrays in table columns.

      Overrides:
      supportsArrayColumns in class Dialect
      Returns:
      true if ARRAY columns are supported

    • getArraySubQuery

      public Dialect.ArraySubQuery getArraySubQuery(Column arrayColumn, String subQueryAlias)
      Description copied from class: Dialect
      Gets the dialect-specific subquery for an array column.
      Overrides:
      getArraySubQuery in class Dialect

    • getArrayElementString

      public String getArrayElementString(String arrayColumnName, int arrayElementIndex)
      Description copied from class: Dialect
      Get SQL Array Element Subscripted string.
      Overrides:
      getArrayElementString in class Dialect

    • getArrayInSql

      public String getArrayInSql(Column arrayColumn, String cast, boolean positive, List<Serializable> params)
      Description copied from class: Dialect
      Gets the SQL string for an array column IN expression.
      Overrides:
      getArrayInSql in class Dialect

    • getArrayLikeSql

      public String getArrayLikeSql(Column arrayColumn, String refName, boolean positive, Table dataHierTable)
      Description copied from class: Dialect
      Gets the SQL string for an array column LIKE expression.
      Overrides:
      getArrayLikeSql in class Dialect

    • getArrayIlikeSql

      public String getArrayIlikeSql(Column arrayColumn, String refName, boolean positive, Table dataHierTable)
      Description copied from class: Dialect
      Gets the SQL string for an array column ILIKE expression.
      Overrides:
      getArrayIlikeSql in class Dialect

    • getArrayOpSql

      protected String getArrayOpSql(Column arrayColumn, String refName, boolean positive, Table dataHierTable, String op)

    • createArrayOf

      public Array createArrayOf(int type, Object[] elements, Connection connection) throws SQLException
      Description copied from class: Dialect
      Factory method for creating Array objects, suitable for passing to PreparedStatement.setArray(int, java.sql.Array).

      (An equivalent method is defined by JDBC4 on the Connection class.)

      Overrides:
      createArrayOf in class Dialect
      Parameters:
      type - the SQL type of the elements
      elements - the elements of the array
      connection - the connection
      Returns:
      an Array holding the elements
      Throws:
      SQLException

    • getSQLStatementsFilename

      public String getSQLStatementsFilename()
      Description copied from class: Dialect
      Gets the name of the file containing the SQL statements.
      Specified by:
      getSQLStatementsFilename in class Dialect

    • getTestSQLStatementsFilename

      public String getTestSQLStatementsFilename()
      Specified by:
      getTestSQLStatementsFilename in class Dialect

    • getSQLStatementsProperties

      public Map<String,Serializable> getSQLStatementsProperties(Model model, Database database)
      Description copied from class: Dialect
      Gets the properties to use with the SQL statements.
      Specified by:
      getSQLStatementsProperties in class Dialect

    • getStartupSqls

      public List<String> getStartupSqls(Model model, Database database)
      Description copied from class: Dialect
      Gets the sql statements to execute after the repository init (at startup).

      Used for vacuum-like operations.

      Overrides:
      getStartupSqls in class Dialect

    • isClusteringSupported

      public boolean isClusteringSupported()
      Description copied from class: Dialect
      Checks that clustering is supported.
      Overrides:
      isClusteringSupported in class Dialect

    • getClusterInsertInvalidations

      public String getClusterInsertInvalidations()
      Description copied from class: Dialect
      Gets the SQL to send an invalidation to the cluster.
      Overrides:
      getClusterInsertInvalidations in class Dialect
      Returns:
      an SQL statement with parameters for: nodeId, id, fragments, kind

    • getClusterGetInvalidations

      public String getClusterGetInvalidations()
      Description copied from class: Dialect
      Gets the SQL to query invalidations for this cluster node.
      Overrides:
      getClusterGetInvalidations in class Dialect
      Returns:
      an SQL statement returning a result set

    • isConcurrentUpdateException

      public boolean isConcurrentUpdateException(Throwable t)
      Description copied from class: Dialect
      Checks if an exception received means that a concurrent update was detected.
      Overrides:
      isConcurrentUpdateException in class Dialect

    • supportsPaging

      public boolean supportsPaging()
      Description copied from class: Dialect
      Indicates if dialect supports paging
      Overrides:
      supportsPaging in class Dialect
      Returns:
      true if the dialect supports paging

    • addPagingClause

      public String addPagingClause(String sql, long limit, long offset)
      Description copied from class: Dialect
      Returns the SQL query with a paging clause
      Overrides:
      addPagingClause in class Dialect

    • supportsWith

      public boolean supportsWith()
      Description copied from class: Dialect
      Does the dialect support SQL-99 WITH common table expressions.
      Overrides:
      supportsWith in class Dialect

    • performAdditionalStatements

      public void performAdditionalStatements(Connection connection) throws SQLException
      Description copied from class: Dialect
      Let the dialect processes additional statements after tables creation and conditional statements. Can be used for specific upgrade procedure.
      Overrides:
      performAdditionalStatements in class Dialect
      Throws:
      SQLException

    • getUsersSeparator

      public String getUsersSeparator()

    • getValidationQuery

      public String getValidationQuery()
      Description copied from class: Dialect
      A query that, when executed, will make at least a round-trip to the server to check that the connection is alive.

      The query should throw an error if the connection is dead.

      Overrides:
      getValidationQuery in class Dialect

    • getAncestorsIdsSql

      public String getAncestorsIdsSql()
      Description copied from class: Dialect
      Gets the SQL query to get the ancestors of a set of ids.
      Overrides:
      getAncestorsIdsSql in class Dialect
      Returns:
      null if not available

    • needsNullsLastOnDescSort

      public boolean needsNullsLastOnDescSort()
      Description copied from class: Dialect
      True if the dialect need an extra NULLS LAST on DESC sort.
      Overrides:
      needsNullsLastOnDescSort in class Dialect

    • getDateCast

      public String getDateCast()
      Description copied from class: Dialect
      Get the expression to use to cast a column to a DATE type.
      Overrides:
      getDateCast in class Dialect
      Returns:
      a pattern for String.format with one parameter for the column name

    • castIdToVarchar

      public String castIdToVarchar(String expr)
      Description copied from class: Dialect
      Casts an id column to a VARCHAR type.

      Used for uuid/varchar joins.

      Overrides:
      castIdToVarchar in class Dialect
      Returns:
      the casted expression

    • getIdType

      public Dialect.DialectIdType getIdType()
      Description copied from class: Dialect
      Gets the type of id when stored in the database.
      Overrides:
      getIdType in class Dialect

    • getSoftDeleteSql

      public String getSoftDeleteSql()
      Description copied from class: Dialect
      SQL to soft delete documents. SQL returned has free parameters for the array of ids and time.
      Overrides:
      getSoftDeleteSql in class Dialect

    • getSoftDeleteCleanupSql

      public String getSoftDeleteCleanupSql()
      Description copied from class: Dialect
      SQL to clean soft-delete documents. SQL returned has free parameters max and beforeTime.
      Overrides:
      getSoftDeleteCleanupSql in class Dialect

    • getBinaryFulltextSql

      public String getBinaryFulltextSql(List<String> columns)
      Description copied from class: Dialect
      Return the SQL to get the columns fulltext fields
      Overrides:
      getBinaryFulltextSql in class Dialect

    • checkStoredProcedure

      public List<String> checkStoredProcedure(String procName, String procCreate, String ddlMode, Connection connection, JDBCLogger logger, Map<String,Serializable> properties) throws SQLException
      Description copied from class: Dialect
      Checks if a given stored procedure exists and is identical to the passed creation SQL.

      There are 3 cases to deal with, and actions to perform:

      • the stored procedure doesn't exist, and must be created (create the stored procedure);
      • the stored procedure exists but is not up to date (drop the old stored procedure and re-create it);
      • the stored procedure exists and is up to date (nothing to do).

      When there is nothing to do, null is returned. Otherwise the returned value is a list of SQL statements to execute. Note that the SQL statements will include also INSERT statements to be executed to remember the creation SQL itself.

      Specified by:
      checkStoredProcedure in class Dialect
      Parameters:
      procName - the stored procedure name
      procCreate - the creation SQL for the stored procedure
      ddlMode - the DDL mode
      connection - the connection
      logger - the logger
      properties - the statement execution properties
      Returns:
      a list of SQL statements
      Throws:
      SQLException

    • normalizeString

      protected static String normalizeString(String string)

    • normalizeArgs

      protected static String normalizeArgs(String args)
      Normalize PostgreSQL type aliases.