QueryBuilder

public class QueryBuilder

Note: Changing the QueryBuilder should only be needed for adding support for a new database plugin.

QueryBuilder is used in code dealing with variances between the various database engines. As different databases have different query syntax, sometimes changes need to be made when generating the actual SQL statement to run. Additional changes should be made by updating the QueryBuilder substitutions array. Every query component then builds its string representation using that array.

Usage Example:

In this example, a QueryBuilder for PostgreSQL is initialized. Parameters are set for PostgreSQL and string keywords for SQL queries are substituted into the queryBuilder. The queryBuilder is then used to create a string description of the Table class instance called todotable.

let queryBuilder = QueryBuilder(withDeleteRequiresUsing: true, withUpdateRequiresFrom: true, createAutoIncrement: createAutoIncrement)
queryBuilder.updateSubstitutions([QueryBuilder.QuerySubstitutionNames.ucase : "UPPER",
   QueryBuilder.QuerySubstitutionNames.lcase : "LOWER",
   QueryBuilder.QuerySubstitutionNames.len : "LENGTH",
   QueryBuilder.QuerySubstitutionNames.numberedParameter : "$",
   QueryBuilder.QuerySubstitutionNames.namedParameter : "",
   QueryBuilder.QuerySubstitutionNames.double : "double precision"
])
let description = try todotable.build(queryBuilder: queryBuilder)

Substitutions

  • An array of substitutions to be made in query string representation.

    Declaration

    Swift

    public var substitutions: [String]

  • Enum defining the cases and their index for the substitutions array used by QueryBuilder to account for variances between the various database engines.

    Usage Example:

    In this example, a QueryBuilder for PostgreSQL is initialized. Parameters are set for PostgreSQL and strings keywords for SQL queries are substituted into the queryBuilder. The QuerySubstitutionNames enum is used to refer to substitutions by name instead of their position in the “substitutions” array.

    let queryBuilder = QueryBuilder(withDeleteRequiresUsing: true, withUpdateRequiresFrom: true, createAutoIncrement: createAutoIncrement)
queryBuilder.updateSubstitutions([QueryBuilder.QuerySubstitutionNames.ucase : "UPPER",
   QueryBuilder.QuerySubstitutionNames.lcase : "LOWER",
   QueryBuilder.QuerySubstitutionNames.len : "LENGTH",
   QueryBuilder.QuerySubstitutionNames.numberedParameter : "$",
   QueryBuilder.QuerySubstitutionNames.namedParameter : "",
   QueryBuilder.QuerySubstitutionNames.double : "double precision"
])
    See more

    Declaration

    Swift

    public enum QuerySubstitutionNames : Int

Parameters

  • An indication whether the parameters should be numbered (e.g., ‘$1, $2’), or just marked with the numbered parameter marker (e.g., ‘?’).

    Declaration

    Swift

    public let addNumbersToParameters: Bool

  • The starting index for numbered parameters.

    Declaration

    Swift

    public let firstParameterIndex: Int

  • An indication whether ANY on subqueries is supported.

    Declaration

    Swift

    public let anyOnSubquerySupported: Bool

  • An indication whether a DELETE query should use the USING clause for tables in WITH clause.

    Declaration

    Swift

    public let withDeleteRequiresUsing: Bool

  • An indication whether an UPDATE query should use the FROM clause for tables in WITH clause.

    Declaration

    Swift

    public let withUpdateRequiresFrom: Bool

  • An implementer of the ColumnCreator protocol that provides methods to build a string representation of a column.

    Declaration

    Swift

    public let columnBuilder: ColumnCreator

  • An indication whether the drop index syntax requires the ON table.name clause.

    Declaration

    Swift

    public let dropIndexRequiresOnTableName: Bool

  • DateFormatter to convert between date and string instances.

    Declaration

    Swift

    public let dateFormatter: DateFormatter?

Initializer

  • Initialize an instance of QueryBuilder.

    Usage Example:

    In this example, a QueryBuilder for PostgreSQL is initialized. Parameters not defined are set to default values.

    let queryBuilder = QueryBuilder(withDeleteRequiresUsing: true, withUpdateRequiresFrom: true, createAutoIncrement: createAutoIncrement)

    Declaration

    Swift

    public init(addNumbersToParameters: Bool = true, firstParameterIndex: Int = 1, anyOnSubquerySupported: Bool = true,
            withDeleteRequiresUsing: Bool = false, withUpdateRequiresFrom: Bool = false, columnBuilder: ColumnCreator,
            dropIndexRequiresOnTableName: Bool = false, dateFormatter: DateFormatter? = nil)

    Parameters

    addNumbersToParameters

    An indication whether query parameters should be numbered.
    firstParameterIndex

    The starting index for numbered parameters.
    anyOnSubquerySupported

    An indication whether ANY on subqueries is supported.
    withDeleteRequiresUsing

    An indication whether a DELETE query should use USING clause for tables in WITH clause.
    withUpdateRequiresFrom

    An indication whether an UPDATE query should use FROM clause for tables in WITH clause.
    createAutoIncrement

    A function to create an autoincrement expression for the column, based on the column type.
    dropIndexRequiresOnTableName

    An indication whether the drop index syntax requires ON table.name clause.
    dateFormatter

    DateFormatter to convert between date and string instances.

Update substitutions

  • Update substitutions array of a QueryBuilder instance.

    Usage Example:

    In this example, a QueryBuilder for PostgreSQL is initialized. The default substitutions are updated for a PostgreSQL database.

    let queryBuilder = QueryBuilder(withDeleteRequiresUsing: true, withUpdateRequiresFrom: true, createAutoIncrement: createAutoIncrement)
queryBuilder.updateSubstitutions([QueryBuilder.QuerySubstitutionNames.ucase : "UPPER",
   QueryBuilder.QuerySubstitutionNames.lcase : "LOWER",
   QueryBuilder.QuerySubstitutionNames.len : "LENGTH",
   QueryBuilder.QuerySubstitutionNames.numberedParameter : "$",
   QueryBuilder.QuerySubstitutionNames.namedParameter : "",
   QueryBuilder.QuerySubstitutionNames.double : "double precision"
])

    Declaration

    Swift

    public func updateSubstitutions(_ newSubstitutions: [QuerySubstitutionNames : String])

    Parameters

    newSubstitutions

    A Dictionary containing the entries to update.