# Migration Guide

## v13.0.0

### Columns are now stored in a different format internally

Before v13.0.0, internally `columns` were stored as string values or raw `Expression` instances.  Now, they are stored as `struct`s representing the column.  This was to allow aliasing and renaming of subselect columns more easily.

This change shouldn't break anything for end users.  The only people who need to check their code are Grammar authors, as the Grammars will now be passed an array of column structs instead of simple values.

Column types follow the format: `{ "type": "string", "value": "any" }`.  For simple columns this shows as `{ "type": "simple", "value": "name" }`.  For expressions this shows as `{ "type": "raw", "value": Expression Instance }` , etc.

### Query Param structs are now validated when adding to a query

Previously, when passing a query param struct as a binding, qb would use the keys it cared about and would ignore the rest.  Now, qb will validate the incoming param to make sure it is a valid query param struct.  A valid query param struct contains **NO** keys that are not found on `cfqueryparam`.  The main reason for this is to catch bugs where the value of a column should be JSON and instead of passing the result of `serializeJSON` the struct itself is passed.

This change may cause some of your existing queries to begin throwing `QBInvalidQueryParam` exceptions. Remove the non-standard keys to fix the error.

## v12.0.0

### Remove `autoAddScale` setting

It is no longer possible to disable auto scale being added.  You can still override any scale by providing it in your query param struct.

### Remove `strictDateDetection` setting

It is no longer possible to disable strict date detection being performed.  You can still override the `cfsqltype` by specifying it in your query param struct.

### Remove `autoDeriveNumericType` setting

It is no longer possible to disable the numeric type detection.  You can specify the numeric types you want used in your settings.  You can also override the `cfsqltype` by specifying it in your query param struct.

### Argument order changed for some aggregate functions

The [`max`](https://qb.ortusbooks.com/query-builder/executing-queries/aggregates#max), [`min`](https://qb.ortusbooks.com/query-builder/executing-queries/aggregates#min), [`count`](https://qb.ortusbooks.com/query-builder/executing-queries/aggregates#count), and [`sum`](https://qb.ortusbooks.com/query-builder/executing-queries/aggregates#sum) methods now accept a `defaultValue` argument.  This comes **before** the `defaultOptions` argument.  If you are using positional parameters with any of these functions, update your code to the new method signature.

### Argument order changed for QueryUtils initializer

This only affects people instantiating `QueryUtils` manually (such as non-ColdBox users) and instantiating with positional arguments.

Please review the QueryUtils `init` function and update your code to the new method signature, if needed.

### Generated `cfsqltype` attributes no longer include the `CF_SQL_` prefix

Although it shouldn't impact any running application, out of an abundance of caution, we are labeling the drop of the`CF_SQL_` prefix as a breaking change.  This prefix has been optional since Adobe ColdFusion 11.

## v11.0.0

### Auto Boolean Casting

Grammars will be able to influence the `cfsqltype` and value when passing in a literal boolean value as a binding. Postgres and SQLite have boolean support, so they will keep the literal boolean value and use a`cfsqltype` of `CF_SQL_OTHER`. SQL Server uses `CF_SQL_BIT`, Oracle users`CF_SQL_NUMERIC`, and MySQL uses `CF_SQL_TINYINT` — all of these will convert literal boolean values to either 1 or 0. This behavior is skipped when providing a custom `cfsqltype`. Custom grammars can implement the`getBooleanSqlType` and `convertBooleanValue` methods to customize this behavior.

Most people will not need to change anything in their code for this breaking change.

## v10.0.0

### Dropped Support for Adobe Coldfusion 2018

### Internal variables renamed for compatibility with BoxLang and cleaner code in general

In certifying qb for BoxLang, we discovered that some of the way qb had worked for years was due to a lucky interaction between properties and functions sharing a name.  Both of these values are put into the `variables` scope, and the way qb shared some of these names like the `from` method as well as the `from` property only worked because of the way Lucee and ACF ordered defining the function and properties.  BoxLang is more strict in this regard and probably for the best.  You can probably imagine how setting `variables.from` inside a function called `from` would maybe work once and then cause a very strange bug when trying to call the `from` function internally again.  Because of these reasons, the following properties have had their names changed:

#### QueryBuilder

* `from` -> `tableName`

#### Column

* `nullable` -> `isNullable`
* `unique` -> `isUnique`
* `unsigned` -> `isUnsigned`
* `default` -> `defaultValue`
* `comment` -> `commentValue`
* `onUpdate` -> `onUpdateAction`
* `onDelete` -> `onDeleteAction`

The following functions have had their signatures updated:

#### BaseGrammar

* `compileFrom` -> `compileTableName( required QueryBuilder query, required any tableName )`

For the majority of users, this will not take any updates to their code to work with qb 10.  For users who have created a custom grammar, column type, or a custom QueryBuilder class, you will need to make sure you code uses the updated property names and functions.

## v9.0.0

### Dropped support for Adobe ColdFusion 2016

Adobe has ended support for ACF 2016, and so must we.

### SchemaBuilder's `uuid` split into [guid()](https://qb.ortusbooks.com/schema-builder/columns#guid) and [uuid()](https://qb.ortusbooks.com/schema-builder/columns#uuid)

CFML's `uuid` does not match other languages; it's one character shorter. Because of this, the value from `createUUID()` cannot be used in some database column types like SQL Server's `uniqueidentifier`. This made for some confusion in SchemaBuilder since it wasn't clear if `uuid` meant CFML's definition or the wider world's definition.

So, the types have been split, following Lucee's pattern, into [`uuid`](https://qb.ortusbooks.com/schema-builder/columns#uuid) (matching CFML's [`createUUID()`](https://cfdocs.org/createuuid)) and [`guid`](https://qb.ortusbooks.com/schema-builder/columns#guid) (matching Java's UUID or [`createGUID()`](https://cfdocs.org/createguid) on Lucee).

{% hint style="warning" %}
If you are using `uuid` with 36 character UUIDs or SQL Server's `uniqueidentifier` columns, please migrate your `uuid` calls to `guid`.
{% endhint %}

### Returning all rows from [paginate](https://qb.ortusbooks.com/query-builder/executing-queries/retrieving-results#paginate) when maxRows is  0 or lower

Popular grid frameworks like Quasar and Datatables use values of 0 or -1 to return all rows from a query. This is now supported in qb. Previously, it generated an invalid query (`SELECT * FROM users LIMIT 0 OFFSET 0`).

{% hint style="success" %}
If this behavior is fine for your application, you don't need to change anything.
{% endhint %}

This behavior can be customized by providing a callback to the `shouldMaxRowsOverrideToAll` setting or `init` argument. 

{% hint style="danger" %}
If you need to revert to the previous behavior, provide the following as the `shouldMaxRowsOverrideToAll` setting:

```cfscript
moduleSettings = {
    "qb": {
        "shouldMaxRowsOverrideToAll": function( maxRows ) {
            return false;
        }
    }
};
```

{% endhint %}

### [`autoDeriveNumericType`](https://qb.ortusbooks.com/query-builder/building-queries/parameters-and-bindings#numeric-sql-type) is now the default

Introduced in [8.10.0](#8.10.0), this feature uses separate SQL types for integers and decimals to increase performance in certain database grammars.  This feature is now the default, but the previous behavior can be enabled by setting `autoDeriveNumericType` to `false`.

{% hint style="success" %}
This behavior *should* be an improvement in most every case without any changes needed.
{% endhint %}

{% hint style="danger" %}
If you need to revert to the previous behavior, provide the following as the `autoDeriveNumericType` setting:

```cfscript
moduleSettings = {
    "qb": {
        "autoDeriveNumericType": false
    }
};
```

{% endhint %}

{% hint style="warning" %}
**Note:** The option to revert to the old behavior will be removed in the next major version.
{% endhint %}

#### [`strictDateDetection`](https://qb.ortusbooks.com/query-builder/building-queries/parameters-and-bindings#strict-date-detection) is now the default

Introduced in [8.1.0](#8.1.0), this feature only returns a SQL type of `CF_SQL_TIMESTAMP` if the param is a date object, not just a string that looks like a date.  This helps avoid situations where some strings were incorrectly interpreted as dates.  For many, the migration path is straightforward — calls to [`now()`](https://cfdocs.org/now) are already date objects as well as any function that operates on a date.  If you need to parse a string as a date, the [`parseDateTime`](https://cfdocs.org/parsedatetime) built-in function can accomplish that.

{% hint style="warning" %}
If you are relying on qb treating any strings as dates you will need to parse them as actual date objects first. (You can do so using functions like [`parseDateTime`](https://cfdocs.org/parsedatetime).
{% endhint %}

{% hint style="danger" %}
If you need to revert to the previous behavior, provide the following as the `strictDateDetection` setting:

```cfscript
moduleSettings = {
    "qb": {
        "strictDateDetection": false
    }
};
```

{% endhint %}

{% hint style="warning" %}
**Note:** The option to revert to the old behavior **may** be removed in the next major version.
{% endhint %}

## v8.0.0

### Where clauses with an OR combinator are now automatically wrapped inside [`when`](https://qb.ortusbooks.com/query-builder/building-queries/when#when) callbacks

This isn't a breaking change that will affect most people.  In fact, it will most likely improve your code.

Previously, when using the [`when`](https://qb.ortusbooks.com/query-builder/building-queries/when#when) control flow function, you were fully responsible for the wrapping of your where statements.  For example, the following query:

```javascript
qb.from( "users" )
    .where( "active", 1 )
    .when( len( url.q ), function( q ) {
        q.where( "username", "LIKE", q & "%" )
            .orWhere( "email", "LIKE", q & "%" );   
    } );
```

Would generate the following SQL:

```sql
SELECT *
FROM "users"
WHERE "active" = ?
    AND "username" = ?
    OR "email" = ?
```

The problem with this statement is that the `OR` can short circuit the `active` check.

The fix is to wrap the `LIKE` statements in parenthesis.  This is done in qb using a function callback to `where`.

```javascript
qb.from( "users" )
    .where( "active", 1 )
    .where( function( q ) {
        q.where( "username", "LIKE", q & "%" )
            .orWhere( "email", "LIKE", q & "%" );
    } );
```

```sql
SELECT *
FROM "users"
WHERE "active" = ?
    AND (
        "username" = ?
        OR "email" = ?
    )
```

When using the `when` control flow function, it was easy to miss this.  This is because you are already in a closure - it looks the same as when using `where` to group the clauses.

In qb 8.0.0, `when` will automatically group added where clauses when needed.  That means our original example now produces the SQL we probably expected.

```javascript
// qb 8.0.0
qb.from( "users" )
    .where( "active", 1 )
    .when( len( url.q ), function( q ) {
        q.where( "username", "LIKE", q & "%" )
            .orWhere( "email", "LIKE", q & "%" );   
    } );
```

```sql
SELECT *
FROM "users"
WHERE "active" = ?
    AND (
        "username" = ?
        OR "email" = ?
    )
```

Grouping is not needed if there is no `OR` combinator.  In these cases no grouping is added.

```javascript
// qb 8.0.0
qb.from( "users" )
    .where( "active", 1 )
    .when( url.keyExists( "admin" ), function( q ) {
        q.where( "admin", 1 )
            .whereNotNull( "hireDate" );
    } );
```

```sql
SELECT *
FROM "users"
WHERE "active" = ?
    AND "admin" = ?
    AND "hireDate IS NOT NULL
```

If you had already wrapped your expression in a group inside the `when` callback, nothing changes.  Your code works as before.  The `OR` combinator check only works on the top most level of added where clauses.

```javascript
qb.from( "users" )
    .where( "active", 1 )
    .when( len( url.q ), function( q ) {
        q.where( function( q2 ) {
            q2.where( "username", "LIKE", q & "%" )
                .orWhere( "email", "LIKE", q & "%" );
        } );
    } );
```

```sql
SELECT *
FROM "users"
WHERE "active" = ?
    AND (
        "username" = ?
        OR "email" = ?
    )
```

Additionally, if you do not add any where clauses inside a `when` callback, nothing changes from qb 7.

The breaking change part is if you were relying on these statements residing at the same level without grouping.  In those cases, you may pass the `withoutScoping` flag to the `when` callback.

```javascript
// qb 8.0.0
qb.from( "users" )
    .where( "active", 1 )
    .when(
        condition = len( url.q ),
        onTrue = function( q ) {
            q.where( "username", "LIKE", q & "%" )
                .orWhere( "email", "LIKE", q & "%" );   
        },
        withoutScoping = true
    );
```

```sql
SELECT *
FROM "users"
WHERE "active" = ?
    AND "username" = ?
    OR "email" = ?
```

## v7.0.0

### Lucee 4.5 and Adobe ColdFusion 11 EOL

Support for Lucee 4.5 and Adobe ColdFusion 11 has been dropped. If you need support for these engines, please remain on an earlier version of qb.

### MSSQLGrammar renamed to SqlServerGrammar

`MSSQLGrammar` was visually too close to `MySQLGrammar` and was hard to differentiate quickly. `SqlServerGrammar` is much more unique and easily identifiable. Additionally, more people that use this library refer to their database engine as "SQL Server" than "MSSQL".

To migrate, replace any instances of `MSSQLGrammar` with `SqlServerGrammar`. Make sure to also append the `@qb` namespace, if needed, [as explained below.](#defaultgrammar-updated-to-be-the-full-wirebox-mapping)

### Variadic Parameters Support Removed

Variadic parameter support was the ability to pass any number of arguments to certain methods like `select`.

```javascript
qb.select( "name", "email", "createdDate" );
```

This code came with a slight performance cost and readability cost. That, combined with the fact that the above syntax is very close to an array, we are dropping support for variadic parameters. To migrate, wrap instances of variadic parameters in an array:

```javascript
qb.select( [ "name", "email", "createdDate" ] );
```

### defaultGrammar updated to be the full WireBox mapping

In previous versions, the value passed to `defaultGrammar` was used to look up a mapping in the `@qb` namespace. This made it difficult to add or use grammars that weren't part of qb. (You could get around this be registering your custom grammar in the `@qb` namespace, but doing so seemed strange.)

To migrate this code, change your `defaultGrammar` to be the full WireBox mapping in your `moduleSettings`:

```javascript
moduleSettings = {
    "qb": {
        "defaultGrammar": "MSSQLGrammar@qb"
    }
};
```

### value method argument order changed

A `defaultValue` parameter and optional exception throwing was added to `value`. This pushed the `options` struct to the end of the method. If you are using positional parameters with `value`, you will need to update your method calls to either use named parameters or the new positions.

```javascript
public any function value(
    required string column,
    string defaultValue = "",
    boolean throwWhenNotFound = false,
    struct options = {}
);
```

### Some methods renamed `callback` to `query`

All methods that could conceivably take a subquery as well as a value now accept a closure or another builder instance to use as a subquery. This led to changing the `callback` argument to `query` in the following cases:

* `whereSub`
* `whereInSub`
* `whereExists`
* `orWhereExists`
* `whereNotExists`
* `andWhereNotExists`
* `orWhereNotExists`
* `whereNullSub`
* `orderBySub`
* `subSelect`

If you are using named parameters with any of the above methods you will need to migrate your method calls.

## v5.0.0

Version `v5.0.0` brings support for `SchemaBuilder` inside `qb`. To avoid naming confusion, `Builder` was renamed to `QueryBuilder`. Any references in your code to `Builder@qb` need to be updated to `QueryBuilder@qb`.