Package jetbrains.buildServer.serverSide.db

Interface SQLDialect

  • public interface SQLDialect
    Represents a SQL dialect. Contains a set of functions that tell what is supported in this dialect, or construct SQL expressions and statements.

    Each function constructs an SQL statement or a part of it. These functions make just SQL text, not jdbc objects. They don't execute constructed statements so they don't require connections to database.

    In order to execute constructed statements, use DBFunctions.

    Author:
    Leonid Bushuev

    • Method Detail

      • getDbType

        @NotNull
DatabaseType getDbType()
        Returns the type of the database.
        Returns:

      • getDbVersion

        @NotNull
Version getDbVersion()
        Returns version of the RDBMS. If not connected yet or version is not detected, 0 is returned.
        Returns:
        the version of RDBMS.

      • concat

        @NotNull
String concat​(@NotNull
              String... exprs)
        Constructs a SQL expression that concatenates several string values.
        Parameters:
        exprs - expressions to concatenate.
        Returns:
        expression that concatenates the given ones.

      • nvl

        @NotNull
String nvl​(@NotNull
           String expr1,
           @NotNull
           String expr2)
        Construct a SQL expression that select the first value if it is not null or the next one.
        Parameters:
        expr1 - the first expression (which will be checked for null)
        expr2 - the second expression
        Returns:
        a SQL expression that will match the following rule: 
                    If expr1 is not null - returns expr1; otherwise returns expr2

      • convertNumberToVarchar

        @NotNull
String convertNumberToVarchar​(@NotNull
                              String expr,
                              @NotNull
                              DBFunctions dbf)
        Constructs an expression that converts the given number to a varchar.
        Parameters:
        expr - a number expression to convert.
        dbf - the database connection.
        Returns:
        converted expression.

      • convertVarcharToNumber

        @NotNull
String convertVarcharToNumber​(@NotNull
                              String expr)
        Constructs a SQL expression that converts varchar value to an integer number.
        Parameters:
        expr - a varchar expression to convert.
        Returns:
        created SQL expression that includes the given expression with converting value to a number.

      • convertVarcharToNVarchar

        String convertVarcharToNVarchar​(@NotNull
                                String expr)
        Constructs a SQL expression that converts a varchar value to an nvarchar.
        Parameters:
        expr - a varchar expression to convert.
        Returns:
        created SQL expression
        Since:
        2021.1

      • lower

        @NotNull
String lower​(@NotNull
             String str)
        Generates the SQL expression that converts the inner string expression to lower case.
        Parameters:
        str - string SQL expression
        Returns:
        lower string SQL expression

      • isTableIndexOrganizedByDefault

        boolean isTableIndexOrganizedByDefault()
        Determines whether the RDBMS make tables with defined primary keys to be index organized by default.
        Returns:
        see above.

      • addColumnQuery

        @NotNull
String addColumnQuery​(@NotNull
                      String tableName,
                      @NotNull
                      String newColumnName,
                      @NotNull
                      String newColumnDefinition)

      • modifyColumnQuery

        @NotNull
String modifyColumnQuery​(@NotNull
                         String tableName,
                         @NotNull
                         String columnName,
                         @NotNull
                         String columnDefinition)
        Constructs a DDL statement for modifying column.
        Parameters:
        tableName -
        columnName -
        columnDefinition -
        Returns:

      • isSupportInlineKey

        boolean isSupportInlineKey​(@NotNull
                           TableKeyKind keyKind)
        Determines does the RDBMS allow to inline the specified key definition into a table create statement.
        Parameters:
        keyKind - kind of the key to inline.
        Returns:
        allows or not.

      • inlineKey

        @NotNull
String inlineKey​(boolean tableIsTemp,
                 @NotNull
                 TableKeyKind keyKind,
                 @NotNull
                 String keyName,
                 @NotNull
                 String... columns)
        Phrase to inline the key into a table create statement.
        Parameters:
        tableIsTemp -
        keyKind - kind of the key to inline.
        keyName - constraint and/or index name.
        columns - columns of constraint/index.
        Returns:
        inline phrase.

      • addPrimaryKey

        @NotNull
String addPrimaryKey​(@NotNull
                     String tableName,
                     @NotNull
                     String... columns)
        Constructs a DDL statement that adds a primary key to the specified table.
        Parameters:
        tableName - name of table
        columns - list of key columns comma delimited
        Returns:

      • addPrimaryKeyNamed

        @NotNull
String addPrimaryKeyNamed​(@NotNull
                          String tableName,
                          @Nullable
                          String keyName,
                          @NotNull
                          String... columns)
        Constructs a DDL statement that adds a primary key with the sepecified name to the specified table.
        Parameters:
        tableName - name of table
        keyName - name of key or null to infer key name
        columns - list of key columns comma delimited
        Returns:

      • addIndexNamed

        @NotNull
String addIndexNamed​(@NotNull
                     String tableName,
                     @NotNull
                     String indexName,
                     boolean unique,
                     @NotNull
                     String... columns)
        Constructs a DDL statement that adds an index to the specified table.
        Parameters:
        tableName - name of the table to add the index to.
        indexName - full name of index.
        unique - whether to add unique index or not.
        columns - columns to be indeexed.
        Returns:
        a SQL DDL command.
        See Also:
        addIndex(java.lang.String, java.lang.String, boolean, java.lang.String...)

      • dropIndexNamed

        @NotNull
String dropIndexNamed​(@NotNull
                      String tableName,
                      @NotNull
                      String indexFullName)
        Construct DDL statement that drops an index from the specified table. This function is applicable only to non-unique indices; unique ones should be deleted by their associated constraint and keys, or renaming the table and copying data to a new one.
        Parameters:
        tableName - table name
        indexFullName - full index name
        Returns:
        constructed SQL DDL query

      • dropUniqueIndexNamed

        @NotNull
String dropUniqueIndexNamed​(@NotNull
                            String tableName,
                            @NotNull
                            String indexName)
        Construct DDL statement that drops an unique index with given name from the given table.
        Parameters:
        tableName - table name
        indexName - name of the index
        Returns:
        constructed SQL DDL query
        Since:
        10.0

      • renameColumn

        @NotNull
String renameColumn​(@NotNull
                    String tableName,
                    @NotNull
                    String oldName,
                    @NotNull
                    String newName,
                    @NotNull
                    String columnDefinition)
        Constructs DDL statement that renames the specified column.
        Parameters:
        tableName - table name where rename column.
        oldName - column that should be renamed.
        newName - new name for column.
        columnDefinition - column definition (used only in MySQL because of a problem with renaming a column in MySQL).
        Returns:
        constructed DDL statement.
        See Also:
        for changing column type

      • changeColumnType

        @NotNull
String changeColumnType​(@NotNull
                        String tableName,
                        @NotNull
                        String columnName,
                        @NotNull
                        String columnTypeDefinition,
                        boolean nullable)
        Constructs DDL statement that alters the specified column.
        Parameters:
        tableName - table name where rename column.
        columnName - column that should be changed.
        columnTypeDefinition - column definition
        nullable - true if column can be null, false otherwise - should have the same value as in original schema
        Returns:
        constructed DDL statement.

      • dropColumn

        @NotNull
String dropColumn​(@NotNull
                  String tableName,
                  @NotNull
                  String columnName)
        Constructs DDL statement that drops the specified column in the table.
        Parameters:
        tableName - table name.
        columnName - column name.
        Returns:
        constructed DDL statement.

      • renameTable

        @NotNull
String renameTable​(@NotNull
                   String oldName,
                   @NotNull
                   String newName)
        Makes a SQL statement that renames a table.
        Parameters:
        oldName - name of the existent table.
        newName - new name for this table.
        Returns:
        DDL statement that renames the table.

      • checkIndexByName

        @NotNull
String checkIndexByName()
        Makes a SQL statement that check whether the specified table has the specified index. The table name and index name should be provided as named parameters with names:
        • tablename
        • indexname
        Returns:
        SQL query that returns a row if the index exists or no rows if no such indices.

      • today

        @NotNull
String today()
        Make an expression that returns the current date.
        Returns:
        current date expression.

      • now

        @NotNull
String now()
        Make an expression that returns the current timestamp.
        Returns:
        current timestamp expression.

      • replace

        @NotNull
String replace​(@NotNull
               String original,
               @NotNull
               String pattern,
               @NotNull
               String replacement)
        Make an expression that receives string, pattern and substitution and performs a substitution
        Parameters:
        original - Original string.
        pattern - Pattern to match.
        replacement - replacement to use.
        Returns:
        original string with pattern changed to replacement

      • substr

        @NotNull
String substr​(@NotNull
              String str,
              @NotNull
              String fromIndex,
              @NotNull
              String length)
        Returns an expression that gets a substring from the given string.
        Parameters:
        str - string of interest.
        fromIndex - start position (starts from 1).
        length - length of the result string.
        Returns:
        see above.

      • trunc

        @NotNull
String trunc​(@NotNull
             String str,
             @NotNull
             String declaredLength)
        Returns an expression that truncates given string to fit into varchar(N) declaration. In case of variable length character set (i.e. UTF-8), the truncation is performed used byte-lengths or char-length depend on the current RDBMS.
        Parameters:
        str - the string to truncate.
        declaredLength - the N from declaration char(N) or varchar(N).
        Returns:
        truncated string.

      • chr

        @NotNull
String chr​(int charCode)
        Makes an expression that produces ascii char with given code
        Parameters:
        charCode -
        Returns:
        character function expression

      • isTemporaryDeclarationPersistent

        boolean isTemporaryDeclarationPersistent()
        Whether RDBMS allows to persist declarations of temporary tables.
        Returns:
        true - temporary tables declarations are persisted in the schema.
        See Also:
        isRequireToRecreateTemporaryTablesEachSession()

      • isRequireToRecreateTemporaryTablesEachSession

        boolean isRequireToRecreateTemporaryTablesEachSession()
        Whether the specified DBMS requires to recreate all temporary tables each session.
        Returns:
        true - temporary tables should be recreated each
        See Also:
        isTemporaryDeclarationPersistent()

      • isSupportTableTruncating

        boolean isSupportTableTruncating()
        Whether the specified DBMS supports the 'truncate table' command.
        Returns:
        true - supports

      • adjustTemporaryTableName

        String adjustTemporaryTableName​(@NotNull
                                String name)
        Adjusts a name to meet specific requirements for temporary table name that are in specific DBMS.
        Parameters:
        name - logical name of temporary table.
        Returns:
        physical name of temporary table.
        See Also:
        adjustTableName(String)

      • toMetadataName

        @NotNull
String toMetadataName​(@NotNull
                      String name)
        Object names can be one of two types: SQL-script like and metadata like. This method converts object name to the metadata style (if necessary).
        Parameters:
        name - the SQL-like name
        Returns:
        metadata name

      • defInt

        @NotNull
String defInt()
        Definition of data type where java int can be fit.
        Returns:
        data type definition.

      • defLongInt

        @NotNull
String defLongInt()
        Definition of data type where java long int can be fit.
        Returns:
        data type definition.

      • defDecimal

        @NotNull
String defDecimal​(int length,
                  int scale)

      • defString

        @NotNull
String defString​(@Nonnegative
                 int length)
        Definition of data type for a string with the specified number of characters.
        Parameters:
        length - number of characters.
        Returns:
        data type definition.

      • defLongString

        @NotNull
String defLongString​(int length)
        Definition of data type for a string with the specified number of characters.
        Parameters:
        length - number of characters.
        Returns:
        data type definition.

      • defUniString

        @NotNull
String defUniString​(@Nonnegative
                    int length,
                    @NotNull
                    DBFunctions dbf)
        Definition of data type for a unicode string with the specified number of characters.
        Parameters:
        length - number of characters.
        dbf - the database connection.
        Returns:
        data type definition.

      • defLongUniString

        @NotNull
String defLongUniString​(@Nonnegative
                        int length,
                        @NotNull
                        DBFunctions dbf)
        Definition of data type for a unicode string with the specified number of characters.
        Parameters:
        length - number of characters.
        dbf - the database connection.
        Returns:
        data type definition.

      • defBoolean

        @NotNull
String defBoolean()
        Definition of data type for a boolean value. Values: 1 means true and 0 means false.
        Returns:
        data type definition.

      • defTimestamp

        @NotNull
String defTimestamp()
        Definition of data type where java date and time can be fit.
        Returns:
        data type definition.

      • varcharTypeLimit

        int varcharTypeLimit()
        Limit on the size of varchar columns.
        Returns:
        the maximum length.
        Since:
        8.1

      • supportsNChar

        boolean supportsNChar()
        Supports type such as NChar, NVarchar, NCLOB.
        Returns:
        supports or doesn't.
        Since:
        9.0
        See Also:
        supportsUnicode()

      • supportsMultiRowInsert

        boolean supportsMultiRowInsert()
        Whether RDBMS supports inserting several rows in one SQL statement.

        P.S. don't mix with batch operations.

        Returns:
        supports inserting several rows in one SQL statement.

      • splitScript

        @NotNull
String[] splitScript​(@NotNull
                     String script)

      • determineQueryKind

        @NotNull
QueryKind determineQueryKind​(@NotNull
                             String query)
        Determines the kind of the given query.
        Parameters:
        query - query to determine kind, should be alleady preprocessed.
        Returns:
        kind.

      • deleteByRowsFromAuxTable

        @NotNull
String deleteByRowsFromAuxTable​(@NotNull
                                String minuendTableName,
                                @NotNull
                                String minuendJoinFieldName,
                                @Nullable
                                String minuendAdditionalCondition,
                                @NotNull
                                String subtrahendTableName,
                                @NotNull
                                String subtrahendJoinFieldName,
                                @Nullable
                                String subtrahendAdditionalCondition)
        Makes a SQL statement that deletes from one table (minuend) rows that join/exist in the other table (subtrahend).

        For normal RDBMS, such statement looks like delete from minuend where id in (select id from subtrahend) and (additional condition)

        But MySQL hangs on such questions because of an extremally stupid query planner, so for MySQL such statement looks like delete minuend from subtrahend inner join minuend on subtrahend.id = minuend.id where additional condition

        Parameters:
        additionalCondition - optional additional condition (only rows that met this condition are to delete).
        minuendTableName - table to delete from (aka minuend table).
        minuendJoinFieldName - linked field name in the minuend table.
        minuendAdditionalCondition -
        subtrahendTableName - table with rows that should be deleted from minuen (aka subtrahend table).
        subtrahendJoinFieldName - linked field name in the subtrahend table.
        subtrahendAdditionalCondition -
        Returns:
        the constructed SQL statement.
        Since:
        9.0
        See Also:
        deleteByRowsNotFromAuxTable(java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String)

      • deleteByRowsNotFromAuxTable

        @NotNull
String deleteByRowsNotFromAuxTable​(@NotNull
                                   String minuendTableName,
                                   @NotNull
                                   String minuendJoinFieldName,
                                   @NotNull
                                   String reminderTableName,
                                   @NotNull
                                   String reminderJoinFieldName,
                                   @Nullable
                                   String additionalCondition)
        Makes a SQL statement that deletes form one table (minuend) rows that don't exist in the other table (reminder).

        For normal RDBMS, such statement looks like delete from minuend where id not in (select id from subtrahend) and (additional condition)

        But MySQL hangs on such questions because of an extremally stupid query planner, so for MySQL such statement looks like delete minuend from minuend left outer join reminder on minuend.id = reminder.id where (additional condition) and reminder.id is null

        See MySQL bug 35794 for details.

        Parameters:
        minuendTableName - table to delete from (aka minuend table).
        minuendJoinFieldName - linked field name in the minuend table.
        reminderTableName - table with rows that should be reminded from minuen (aka reminder table).
        reminderJoinFieldName - linked field name in the reminder table.
        additionalCondition - optional additional condition (only rows that met this condition are to delete).
        Returns:
        the constructed SQL statement.
        Since:
        9.0
        See Also:
        deleteByRowsFromAuxTable(java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String)

      • copyTable

        @NotNull
List<String> copyTable​(@NotNull
                       String sourceTableName,
                       @NotNull
                       String newTableName)
        Makes a list of DDL statements that create a new table with the existent one's content.
        Parameters:
        sourceTableName - the name of table to copy.
        newTableName - the name for a new table.
        Returns:
        SQL statements.

      • getDefaultConnectionProperties

        @NotNull
Map<String,​String> getDefaultConnectionProperties()

        Returns the default connection properties for the given dialect, or an empty map if no defaults have been defined.

        Returns:
        the default connection properties for the given dialect.
        Since:
        2018.1.2