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

Class Dialect

    • Field Detail

      • NULLS_LAST_ON_DESC_PROP

        public static final String NULLS_LAST_ON_DESC_PROP
        Property used to disable NULLS LAST usage when sorting DESC. This increase performance for some dialects because they can use an index for sorting when there are no NULL value.
        Since:
        5.9
        See Also:
        Constant Field Values

      • descending

        protected String descending
        Store the SQL for descending order
        Since:
        5.9

      • DIALECT_CLASS

        public static final String DIALECT_CLASS
        System property to override the dialect to use globally instead of the one auto-detected. It can be suffixed by "." and the database name (without spaces and as returned by the database itself) to override only for a specific database.
        Since:
        5.6
        See Also:
        Constant Field Values

      • storesUpperCaseIdentifiers

        protected final boolean storesUpperCaseIdentifiers

      • fulltextDisabled

        protected boolean fulltextDisabled

      • fulltextSearchDisabled

        protected boolean fulltextSearchDisabled

      • aclOptimizationsEnabled

        protected final boolean aclOptimizationsEnabled

      • clusteringEnabled

        protected boolean clusteringEnabled
        Since:
        5.7

      • softDeleteEnabled

        protected boolean softDeleteEnabled
        Since:
        5.7

      • proxiesEnabled

        protected boolean proxiesEnabled

      • readAclMaxSize

        protected final int readAclMaxSize

      • HEX_DIGITS

        protected static final char[] HEX_DIGITS

    • Method Detail

      • supportsScroll

        public boolean supportsScroll()
        Does the dialect support an scroll API
        Since:
        8.4

      • jdbcInfo

        public static Dialect.JDBCInfo jdbcInfo​(String string,
                                        int length,
                                        int jdbcType,
                                        String jdbcBaseTypeString,
                                        int jdbcBaseType)

      • createDialect

        public static Dialect createDialect​(Connection connection,
                                    RepositoryDescriptor repositoryDescriptor)
        Creates a Dialect by connecting to the datasource to check what database is used.

      • getJDBCTypeAndString

        public abstract Dialect.JDBCInfo getJDBCTypeAndString​(ColumnType type)
        Gets the JDBC type and string from Nuxeo's type abstraction.

      • isAllowedConversion

        public boolean isAllowedConversion​(int expected,
                                   int actual,
                                   String actualName,
                                   int actualSize)
        Check mismatches between expected and actual JDBC types read from database introspection.

      • setId

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

      • getTimestampFromCalendar

        public Timestamp getTimestampFromCalendar​(Calendar value)

      • getCalendarFromTimestamp

        public Calendar getCalendarFromTimestamp​(Timestamp value)

      • getCalendarFromTimestamp

        public Calendar[] getCalendarFromTimestamp​(Timestamp[] value)

      • storesUpperCaseIdentifiers

        public boolean storesUpperCaseIdentifiers()

      • openQuote

        public char openQuote()

      • closeQuote

        public char closeQuote()

      • toBooleanValueString

        public String toBooleanValueString​(boolean bool)

      • getMaxNameSize

        protected int getMaxNameSize()

      • getMaxIndexNameSize

        protected int getMaxIndexNameSize()

      • makeName

        protected String makeName​(String name,
                          int maxNameSize)

      • toHexString

        public static String toHexString​(byte[] bytes)

      • getTableName

        public String getTableName​(String name)

      • getColumnName

        public String getColumnName​(String name)

      • getPrimaryKeyConstraintName

        public String getPrimaryKeyConstraintName​(String tableName)

      • getForeignKeyConstraintName

        public String getForeignKeyConstraintName​(String tableName,
                                          String foreignColumnName,
                                          String foreignTableName)

      • getCreateIndexSql

        public String getCreateIndexSql​(String indexName,
                                Table.IndexType indexType,
                                Table table,
                                List<Column> columns,
                                Model model)
        Gets a CREATE INDEX statement for an index.
        Parameters:
        indexName - the index name (for fulltext)
        indexType - the index type
        table - the table
        columns - the columns to index
        model - the model

      • getFulltextIndexedColumns

        public abstract int getFulltextIndexedColumns()
        Specifies what columns of the fulltext table have to be indexed.
        Returns:
        0 for none, 1 for the synthetic one, 2 for the individual ones

      • supportsMultipleFulltextIndexes

        public boolean supportsMultipleFulltextIndexes()
        SQL Server supports only one fulltext index.

      • getMaterializeFulltextSyntheticColumn

        public abstract boolean getMaterializeFulltextSyntheticColumn()
        Does the fulltext synthetic column have to be materialized.

      • getCreateFulltextIndexSql

        public abstract String getCreateFulltextIndexSql​(String indexName,
                                                 String quotedIndexName,
                                                 Table table,
                                                 List<Column> columns,
                                                 Model model)
        Gets a CREATE INDEX statement for a fulltext index.

      • getDialectFulltextQuery

        public abstract String getDialectFulltextQuery​(String query)
        Get the dialect-specific version of a fulltext query.
        Parameters:
        query - the CMIS-syntax-based fulltext query string
        Returns:
        the dialect native fulltext query string

      • getFulltextScoredMatchInfo

        public abstract Dialect.FulltextMatchInfo getFulltextScoredMatchInfo​(String fulltextQuery,
                                                                     String indexName,
                                                                     int nthMatch,
                                                                     Column mainColumn,
                                                                     Model model,
                                                                     Database database)
        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.

      • getLikeEscaping

        public String getLikeEscaping()
        Gets the SQL fragment to add after a LIKE match to specify the escaping character.
        Since:
        7.4

      • getMatchMixinType

        public String getMatchMixinType​(Column mixinsColumn,
                                String mixin,
                                boolean positive,
                                String[] returnParam)
        Gets the SQL fragment to match a mixin type.

      • supportsPaging

        public boolean supportsPaging()
        Indicates if dialect supports paging
        Returns:
        true if the dialect supports paging

      • addPagingClause

        public String addPagingClause​(String sql,
                              long limit,
                              long offset)
        Returns the SQL query with a paging clause
        Since:
        5.7 (replacing getPagingClause)

      • getFulltextType

        public int getFulltextType()
        Gets the type of a fulltext column has known by JDBC.

        This is used for setNull.

      • getFreeVariableSetterForType

        public String getFreeVariableSetterForType​(ColumnType type)
        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).

        Parameters:
        type - the column type
        Returns:
        the expression containing a free variable

      • getNoColumnsInsertString

        public String getNoColumnsInsertString​(Column idColumn)

      • getNullColumnString

        public String getNullColumnString()

      • getTableTypeString

        public String getTableTypeString​(Table table)

      • getAddPrimaryKeyConstraintString

        public String getAddPrimaryKeyConstraintString​(String constraintName)

      • getAddForeignKeyConstraintString

        public String getAddForeignKeyConstraintString​(String constraintName,
                                               String[] foreignKeys,
                                               String referencedTable,
                                               String[] primaryKeys,
                                               boolean referencesPrimaryKey)

      • qualifyIndexName

        public boolean qualifyIndexName()

      • supportsIfExistsBeforeTableName

        public boolean supportsIfExistsBeforeTableName()

      • supportsIfExistsAfterTableName

        public boolean supportsIfExistsAfterTableName()

      • getCascadeDropConstraintsString

        public String getCascadeDropConstraintsString()

      • supportsCircularCascadeDeleteConstraints

        public boolean supportsCircularCascadeDeleteConstraints()

      • getAddColumnString

        public String getAddColumnString()

      • supportsUpdateFrom

        public abstract boolean supportsUpdateFrom()
        Does the dialect support UPDATE t SET ... FROM t, u WHERE ... ?

      • doesUpdateFromRepeatSelf

        public abstract boolean doesUpdateFromRepeatSelf()
        When doing an UPDATE t SET ... FROM t, u WHERE ..., does the FROM clause need to repeate the updated table (t).

      • needsOrderByKeysAfterDistinct

        public boolean needsOrderByKeysAfterDistinct()
        When doing a SELECT DISTINCT that uses a ORDER BY, do the keys along which we order have to be mentioned in the DISTINCT clause?

      • needsAliasForDerivedTable

        public boolean needsAliasForDerivedTable()
        Whether a derived table (subselect in a FROM statement) needs an alias.

      • needsOriginalColumnInGroupBy

        public boolean needsOriginalColumnInGroupBy()
        Whether a GROUP BY can only be used with the original column name and not an alias.

      • needsOracleJoins

        public boolean needsOracleJoins()
        Whether implicit Oracle joins (instead of explicit ANSI joins) are needed.

      • needsPrepareUserReadAcls

        public boolean needsPrepareUserReadAcls()
        The dialect need an extra SQL statement to populate a user read acl cache before running the query.
        Since:
        5.5

      • needsNullsLastOnDescSort

        public boolean needsNullsLastOnDescSort()
        True if the dialect need an extra NULLS LAST on DESC sort.
        Since:
        5.9

      • getClobCast

        public String getClobCast​(boolean inOrderBy)
        When using a CLOB field in an expression, is some casting required and with what pattern?

        Needed for H2.

        Parameters:
        inOrderBy - true if the expression is for an ORDER BY column
        Returns:
        a pattern for String.format with one parameter for the column name and one for the width, or null if no cast is required

      • getQuotedNameForExpression

        public String getQuotedNameForExpression​(Column column)
        Gets the SQL to use to refer to a column in an expression.

        Needed for Oracle CLOB.

        Parameters:
        column - the column used in an expression
        Returns:
        the SQL to use to refer to it
        Since:
        10.10

      • getDateCast

        public String getDateCast()
        Get the expression to use to cast a column to a DATE type.
        Returns:
        a pattern for String.format with one parameter for the column name
        Since:
        5.6

      • castIdToVarchar

        public String castIdToVarchar​(String expr)
        Casts an id column to a VARCHAR type.

        Used for uuid/varchar joins.

        Returns:
        the casted expression
        Since:
        5.7

      • getIdType

        public Dialect.DialectIdType getIdType()
        Gets the type of id when stored in the database.
        Since:
        5.7

      • getSecurityCheckSql

        public abstract String getSecurityCheckSql​(String idColumnName)
        Gets the expression to use to check security.
        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()
        Checks if the dialect supports an ancestors table.

      • supportsFastDescendants

        public boolean supportsFastDescendants()
        Checks whether getInTreeSql(String, String) is optimized for fast results (using an ancestors or descendants table).
        Since:
        7.10, 6.0-HF21

      • getInTreeSql

        public abstract String getInTreeSql​(String idColumnName,
                                    String id)
        Gets the expression to use to check tree membership.
        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

      • supportsArrays

        public boolean supportsArrays()
        Does the dialect support passing ARRAY values (to stored procedures mostly).

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

        Returns:
        true if ARRAY values are supported

      • supportsArraysReturnInsteadOfRows

        public boolean supportsArraysReturnInsteadOfRows()
        Does a stored function returning an result set need to access it as a single array instead of iterating over a normal result set's rows.

        Oracle needs this.

      • supportsSysNameArray

        public boolean supportsSysNameArray()
        Checks if the dialect supports storing arrays of system names (for mixins for instance).

      • supportsArrayColumns

        public boolean supportsArrayColumns()
        Does the dialect support storing arrays in table columns.

        Returns:
        true if ARRAY columns are supported

      • getArraySubQuery

        public Dialect.ArraySubQuery getArraySubQuery​(Column arrayColumn,
                                              String subQueryAlias)
        Gets the dialect-specific subquery for an array column.

      • getArrayElementString

        public String getArrayElementString​(String arrayColumnName,
                                    int arrayElementIndex)
        Get SQL Array Element Subscripted string.

      • getArrayInSql

        public String getArrayInSql​(Column arrayColumn,
                            String cast,
                            boolean positive,
                            List<Serializable> params)
        Gets the SQL string for an array column IN expression.

      • getArrayLikeSql

        public String getArrayLikeSql​(Column arrayColumn,
                              String refName,
                              boolean positive,
                              Table dataHierTable)
        Gets the SQL string for an array column LIKE expression.

      • getArrayIlikeSql

        public String getArrayIlikeSql​(Column arrayColumn,
                               String refName,
                               boolean positive,
                               Table dataHierTable)
        Gets the SQL string for an array column ILIKE expression.

      • getUpsertSql

        public abstract String getUpsertSql​(List<Column> columns,
                                    List<Serializable> values,
                                    List<Column> outColumns,
                                    List<Serializable> outValues)
        Gets the SQL string for an UPSERT expression.
        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
        Since:
        10.10

      • getInsertOnConflictDoNothingSql

        public String getInsertOnConflictDoNothingSql​(List<Column> columns,
                                              List<Serializable> values,
                                              List<Column> outColumns,
                                              List<Serializable> outValues)
        Gets the SQL string for an INSERT ... ON CONFLICT DO NOTHING expression.
        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
        Since:
        11.1

      • getSQLStatementsFilename

        public abstract String getSQLStatementsFilename()
        Gets the name of the file containing the SQL statements.

      • getTestSQLStatementsFilename

        public abstract String getTestSQLStatementsFilename()

      • getSQLStatementsProperties

        public abstract Map<String,​Serializable> getSQLStatementsProperties​(Model model,
                                                                          Database database)
        Gets the properties to use with the SQL statements.

      • isClusteringSupported

        public boolean isClusteringSupported()
        Checks that clustering is supported.

      • isClusteringDeleteNeeded

        public boolean isClusteringDeleteNeeded()
        Does clustering fetch of invalidations ( getClusterGetInvalidations()) need a separate delete for them.

      • getClusterInsertInvalidations

        public String getClusterInsertInvalidations()
        Gets the SQL to send an invalidation to the cluster.
        Returns:
        an SQL statement with parameters for: nodeId, id, fragments, kind

      • getClusterGetInvalidations

        public String getClusterGetInvalidations()
        Gets the SQL to query invalidations for this cluster node.
        Returns:
        an SQL statement returning a result set

      • supportsIlike

        public boolean supportsIlike()
        Does the dialect support ILIKE operator

      • supportsReadAcl

        public boolean supportsReadAcl()
        Does the dialect support an optimized read security checks

      • supportsWith

        public boolean supportsWith()
        Does the dialect support SQL-99 WITH common table expressions.

      • hasNullEmptyString

        public boolean hasNullEmptyString()
        Does the dialect have an empty string identical to NULL (Oracle).

      • getMaximumArgsForIn

        public int getMaximumArgsForIn()
        Maximum number of values in a IN (?, ?, ...) statement.

        Beyond this size we'll do the query in several chunks.

        PostgreSQL is limited to 65535 values in a prepared statement.

        Oracle is limited to 1000 expressions in a list (ORA-01795).

      • getUpdateReadAclsSql

        public String getUpdateReadAclsSql()
        Gets the statement to update the read acls

      • getRebuildReadAclsSql

        public String getRebuildReadAclsSql()
        Gets the statement to rebuild the wall read acls

      • getReadAclsCheckSql

        public String getReadAclsCheckSql​(String userIdCol)
        Gets the expression to check if access is allowed using read acls. The dialect must suppportsReadAcl.
        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

      • getPrepareUserReadAclsSql

        public String getPrepareUserReadAclsSql()
        Gets the SQL expression to prepare the user read acls cache. This can be used to populate a table cache.
        Returns:
        and SQL expression with one parameter (principals)
        Since:
        5.5

      • getStartupSqls

        public List<String> getStartupSqls​(Model model,
                                   Database database)
        Gets the sql statements to execute after the repository init (at startup).

        Used for vacuum-like operations.

        Since:
        6.0-HF24, 7.10-HF01, 8.1

      • isConcurrentUpdateException

        public boolean isConcurrentUpdateException​(Throwable t)
        Checks if an exception received means that a concurrent update was detected.
        Since:
        5.8

      • performAdditionalStatements

        public void performAdditionalStatements​(Connection connection)
                                 throws SQLException
        Let the dialect processes additional statements after tables creation and conditional statements. Can be used for specific upgrade procedure.
        Throws:
        SQLException

      • getValidationQuery

        public String getValidationQuery()
        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.

      • getBlobLengthFunction

        public String getBlobLengthFunction()
        Gets the SQL function that returns the length of a blob, in bytes.

      • performPostOpenStatements

        public void performPostOpenStatements​(Connection connection)
                               throws SQLException
        Let the dialect perform additional statements just after the connection is opened.
        Throws:
        SQLException

      • getPostCreateIdentityColumnSql

        public List<String> getPostCreateIdentityColumnSql​(Column column)
        Gets additional SQL statements to execute after the CREATE TABLE when creating an identity column.

        Oracle needs both a sequence and a trigger.

      • isIdentityAlreadyPrimary

        public boolean isIdentityAlreadyPrimary()
        Checks if an identity column is already defined as a primary key and does not need a separate index added.

        MySQL defines the identity column directly as primary key.

      • hasIdentityGeneratedKey

        public boolean hasIdentityGeneratedKey()
        True if the dialect returns the generated key for the identity from the insert statement.

        Oracle needs a separate call to CURRVAL.

      • getIdentityGeneratedKeySql

        public String getIdentityGeneratedKeySql​(Column column)
        Gets the SQL query to execute to retrieve the last generated identity key.

        Oracle needs a separate call to CURRVAL.

      • getAncestorsIdsSql

        public String getAncestorsIdsSql()
        Gets the SQL query to get the ancestors of a set of ids.
        Returns:
        null if not available

      • getDescending

        public String getDescending()
        Gets the SQL descending sort direction with option to sort nulls last. Use to unify database behavior.
        Returns:
        DESC or DESC NULLS LAST depending on dialects.

      • getIgnoredColumns

        public List<String> getIgnoredColumns​(Table table)
        Columns ignored if we see them in existing tables.

      • getCustomColumnDefinition

        public String getCustomColumnDefinition​(Table table)
        Additional column definitions for CREATE TABLE.

      • getCustomPostCreateSqls

        public List<String> getCustomPostCreateSqls​(Table table,
                                            Model model)
        Additional things to execute after CREATE TABLE.

      • getSoftDeleteSql

        public String getSoftDeleteSql()
        SQL to soft delete documents. SQL returned has free parameters for the array of ids and time.

      • getSoftDeleteCleanupSql

        public String getSoftDeleteCleanupSql()
        SQL to clean soft-delete documents. SQL returned has free parameters max and beforeTime.

      • getBinaryFulltextSql

        public String getBinaryFulltextSql​(List<String> columns)
        Return the SQL to get the columns fulltext fields
        Since:
        5.9.3

      • checkStoredProcedure

        public abstract List<String> checkStoredProcedure​(String procName,
                                                  String procCreate,
                                                  String ddlMode,
                                                  Connection connection,
                                                  JDBCLogger logger,
                                                  Map<String,​Serializable> properties)
                                           throws SQLException
        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.

        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
        Since:
        6.0-HF24, 7.10-HF01, 8.1

      • getDumpStart

        public Collection<? extends String> getDumpStart()
        Returns the initial DDL statements to add to a DDL dump.
        Returns:
        a list of SQL statements, usually empty
        Since:
        6.0-HF24, 7.10-HF01, 8.1

      • getDumpStop

        public Collection<? extends String> getDumpStop()
        Returns the final DDL statements to add to a DDL dump.
        Returns:
        a list of SQL statements, usually empty
        Since:
        6.0-HF24, 7.10-HF01, 8.1

      • getSQLForDump

        public String getSQLForDump​(String sql)
        Returns the SQL statement with proper terminator to use in a dump.
        Returns:
        the SQL statement
        Since:
        6.0-HF24, 7.10-HF01, 8.1

      • supportsBatchUpdateCount

        public boolean supportsBatchUpdateCount()
        Does the dialect report accurate update counts for batch updates.
        Since:
        9.2

      • registerReturnParameter

        public void registerReturnParameter​(PreparedStatement ps,
                                    int parameterIndex,
                                    int sqlType)
                             throws SQLException
        Registers return parameters.

        Used for Oracle "DML Returning".

        Throws:
        SQLException
        Since:
        10.10