# Upgrading Instructions for Yii Database

This file contains the upgrade notes. These notes highlight changes that could break your
application when you upgrade the package from one version to another.

> **Important!** The following upgrading instructions are cumulative. That is, if you want
> to upgrade from version A to version C and there is version B between A and C, you need
> to following the instructions for both A and B.

## Upgrade from 1.x to 2.x

### Remove `ColumnInterface`

- Remove `ColumnInterface` and use `ColumnSchemaInterface` instead;
- Rename `ColumnSchemaInterface` to `ColumnInterface`.

### `ColumnInterface` as column type

Add `ColumnInterface` support and change type of parameter `$type` to `ColumnInterface|string` 
in the following methods: 
- `addColumn()`
- `alterColumn()`

in classes that implement the following interfaces:
- `Yiisoft\Db\Command\CommandInterface`;
- `Yiisoft\Db\QueryBuilder\DDLQueryBuilderInterface`;

or inherit from the following classes:
- `Yiisoft\Db\Command\AbstractCommand`;
- `Yiisoft\Db\QueryBuilder\AbstractDDLQueryBuilder`;
- `Yiisoft\Db\QueryBuilder\AbstractQueryBuilder`.

### Scalar values for columns in `Query`

Change `$columns` parameter type from `array|string|ExpressionInterface` to `array|bool|float|int|string|ExpressionInterface`
in methods `select()` and `addSelect()` of your classes that implement `Yiisoft\Db\Query\QueryPartsInterface`.

Add support any scalar values for `$columns` parameter of these methods in your classes that implement
`Yiisoft\Db\Query\QueryPartsInterface` or inherit `Yiisoft\Db\Query\Query`.

### Join condition and an associative array

When join condition in the following methods `join()`, `innerJoin()`, `leftJoin()`,
`rightJoin()` of `Yiisoft\Db\Query\Query` class is an associative array, its string values will be quoted
as column names.

### Build `Expression` instances inside `Expression::$params`

`ExpressionBuilder` is replaced by an abstract class `AbstractExpressionBuilder` with an instance of the 
`QueryBuilderInterface` parameter in the constructor. Each DBMS driver should implement its own expression builder.

`Expression::$params` can contain:
- non-unique placeholder names, they will be replaced with unique names;
- `Expression` instances, they will be built when building a query using `QueryBuilder`.

### Rename `batchInsert()` to `insertBatch()`

`batchInsert()` method is renamed to `insertBatch()` in `DMLQueryBuilderInterface` and `CommandInterface`.
The parameters are changed from `$table, $columns, $rows` to `$table, $rows, $columns = []`.
It allows to use the method without columns, for example:

```php
use Yiisoft\Db\Connection\ConnectionInterface;

$values = [
    ['name' => 'Tom', 'age' => 30],
    ['name' => 'Jane', 'age' => 20],
    ['name' => 'Linda', 'age' => 25],
];

/** @var ConnectionInterface $db */
$db->createCommand()->insertBatch('user', $values)->execute();
```

### `ColumnSchemaInterface` changes

Rename `ColumnSchemaInterface` to `ColumnInterface`.

The interface and the abstract implementation `AbstractColumn` were moved to `Yiisoft\Db\Schema\Column` namespace 
and the following changes were made:

- `getName()` method can return `string` or `null`;
- `withName(string|null $name)` method is added;
- `check(string|null $check)` method is added;
- `getCheck()` method is added;
- `reference(ForeignKey|null $reference)` method is added;
- `getReference()` method is added;
- `notNull(bool $notNull = true)` method is added;
- `null()` method is added;
- `isNotNull()` method is added;
- `unique(bool $unique = true)` method is added;
- `isUnique()` method is added;
- `hasDefaultValue()` method is added;
- `getPrecision()` and `precision()` methods are removed;
- `getPhpType()` and `phpType()` methods are removed;
- all `AbstractColumn` class properties except `$type` moved to constructor;
- added `DEFAULT_TYPE` constant to `AbstractColumn` class;
- added method chaining.

### Changes in constraint classes

- Remove `Constraint` class;
- Rename classes `CheckConstraint`, `DefaultValueConstraint`, `ForeignKeyConstraint` and `IndexConstraint`
  to `Check`, `DefaultValue`, `ForeignKey` and `Index`;
- Move properties to constructor and make them `public readonly`;
- Remove all methods - use constructor to initialize values and properties to get values;

### New classes with constants

- `Yiisoft\Db\Constant\GettypeResult` with `gettype()` function results constants;
- `Yiisoft\Db\Constant\ColumnType` with abstract column types constants;
- `Yiisoft\Db\Constant\PseudoType` with column pseudo-types constants;
- `Yiisoft\Db\Constant\IndexType` with table index types;
- `Yiisoft\Db\Constant\ReferentialAction` with possible values of referential actions.

### New classes for table columns

Each table column has its own class in the `Yiisoft\Db\Schema\Column` namespace according to the data type:

- `BooleanColumn` for columns with boolean type;
- `BitColumn` for columns with bit type;
- `IntegerColumn` for columns with integer type (tinyint, smallint, integer, bigint);
- `BigIntColumn` for columns with integer type with range outside `PHP_INT_MIN` and `PHP_INT_MAX`;
- `DoubleColumn` for columns with fractional number type (float, double, decimal, money);
- `StringColumn` for columns with string type (char, string, text);
- `BinaryColumn` for columns with binary type;
- `DateTimeColumn` for columns with date and time type (timestamp, datetime, time, date);
- `ArrayColumn` for columns with array type;
- `StructuredColumn` for columns with structured type (composite type in PostgreSQL);
- `JsonColumn` for columns with json type.

### New methods

- `QuoterInterface::getRawTableName()` - returns the raw table name without quotes;
- `QueryInterface::resultCallback()` - allows to use a callback, to be called on all rows of the query result;
- `QueryInterface::getFor()` - returns the `FOR` part of the query;
- `QueryInterface::getResultCallback()` - returns the callback to be called on all rows of the query result or 
  `null` if the callback is not set;
- `QueryInterface::withTypecasting()` - enables or disables typecasting of values when retrieving records from DB;
- `QueryPartsInterface::for()` - sets the `FOR` part of the query;
- `QueryPartsInterface::addFor()` - adds more `FOR` parts to the existing ones;
- `QueryPartsInterface::setFor()` - overwrites the `FOR` part of the query;
- `QueryPartsInterface::setWhere()` - overwrites the `WHERE` part of the query;
- `QueryPartsInterface::setHaving()` - overwrites the `HAVING` part of the query;
- `QueryPartsInterface::addWithQuery()` - prepends an SQL statement using `WITH` syntax;
- `ConnectionInterface::getColumnBuilderClass()` - returns the column builder class name for concrete DBMS;
- `ConnectionInterface::getColumnFactory()` - returns the column factory object for concrete DBMS;
- `ConnectionInterface::getServerInfo()` - returns `ServerInfoInterface` instance which provides server information;
- `ConnectionInterface::createQuery()` - creates a `Query` object;
- `ConnectionInterface::select()` - creates a `Query` object with the specified columns to be selected;
- `QueryBuilderInterface::buildColumnDefinition()` - builds column definition for `CREATE TABLE` statement;
- `QueryBuilderInterface::buildValue()` - converts a value to its SQL representation with binding parameters if necessary;
- `QueryBuilderInterface::prepareParam()` - converts a `Param` object to its SQL representation;
- `QueryBuilderInterface::prepareValue()` - converts a value to its SQL representation;
- `QueryBuilderInterface::replacePlaceholders()` - replaces placeholders in the SQL string with the corresponding values;
- `QueryBuilderInterface::getColumnFactory()` - returns the column factory object for concrete DBMS;
- `QueryBuilderInterface::getServerInfo()` - returns `ServerInfoInterface` instance which provides server information;
- `DQLQueryBuilderInterface::buildFor()` - builds a SQL for `FOR` clause;
- `DMLQueryBuilderInterface::isTypecastingEnabled()` - returns whether typecasting is enabled for the query builder;
- `DMLQueryBuilderInterface::upsertReturning()` - builds a SQL to insert or update a record with returning values;
- `DMLQueryBuilderInterface::withTypecasting()` - enables or disables typecasting of values when inserting or updating 
  records in DB;
- `LikeCondition::getCaseSensitive()` - returns whether the comparison is case-sensitive;
- `SchemaInterface::hasTable()` - returns whether the specified table exists in database;
- `SchemaInterface::hasSchema()` - returns whether the specified schema exists in database;
- `SchemaInterface::hasView()` - returns whether the specified view exists in database;
- `DbArrayHelper::arrange()` - arranges an array by specified keys;
- `CommandInterface::upsertReturning()` - inserts or updates a record returning its values;
- `CommandInterface::upsertReturningPks()` - inserts or updates a record returning its primary keys;
- `CommandInterface::withDbTypecasting()` - enables or disables typecasting of values when inserting or updating records;
- `CommandInterface::withPhpTypecasting()` - enables or disables typecasting of values when retrieving records from DB;
- `CommandInterface::withTypecasting()` - enables or disables typecasting of values when inserting, updating 
  or retrieving records from DB;
- `SchemaInterface::getResultColumn()` - returns the column instance for the column metadata received from the query;
- `AbstractSchema::getResultColumnCacheKey()` - returns the cache key for the column metadata received from the query;
- `AbstractSchema::loadResultColumn()` - creates a new column instance according to the column metadata from the query;
- `DataReaderInterface::typecastColumns()` - sets columns for type casting the query results;
- `AbstractSchema::resolveFullName()` - resolves the full name of the table, view, index, etc.;
- `AbstractSchema::clarifyFullName()` - clarifies the full name of the table, view, index, etc.;

### Remove methods

- `AbstractQueryBuilder::getColumnType()` - use `AbstractQueryBuilder::buildColumnDefinition()` instead;
- `AbstractDMLQueryBuilder::getTypecastValue()`;
- `TableSchemaInterface::compositeForeignKey()`;
- `SchemaInterface::createColumn()` - use `ColumnBuilder` instead;
- `SchemaInterface::isReadQuery()` - use `DbStringHelper::isReadQuery()` instead;
- `SchemaInterface::findUniqueIndexes()` - use `SchemaInterface::getTableUniques()` instead;
- `SchemaInterface::getRawTableName()` - use `QuoterInterface::getRawTableName()` instead;
- `AbstractSchema::resolveTableName()` - use `QuoterInterface::getTableNameParts()` instead;
- `AbstractSchema::isReadQuery()` - use `DbStringHelper::isReadQuery()` instead;
- `AbstractSchema::getRawTableName()` - use `QuoterInterface::getRawTableName()` instead;
- `AbstractSchema::normalizeRowKeyCase()` - use `array_change_key_case()` instead;
- `Quoter::unquoteParts()`;
- `AbstractPdoCommand::logQuery()`;
- `ConnectionInterface::getServerVersion()` - use `ConnectionInterface::getServerInfo()` instead;
- `DbArrayHelper::getColumn()` - use `array_column()` instead;
- `DbArrayHelper::getValueByPath()`;
- `DbArrayHelper::populate()` - use `DbArrayHelper::index()` instead;
- `DbStringHelper::baseName()`;
- `DbStringHelper::pascalCaseToId()`;
- `AbstractDQLQueryBuilder::hasLimit()` - use `$limit !== null` instead;
- `AbstractDQLQueryBuilder::hasOffset()` - use `!empty($offset)` instead;
- `BatchQueryResultInterface::reset()` - use `BatchQueryResultInterface::rewind()` instead;
- `BatchQueryResult::reset()` - use `BatchQueryResult::rewind()` instead;
- `Like::getEscapingReplacements()`/`LikeCondition::setEscapingReplacements()` - use `escape` constructor parameter
  instead;

### Remove deprecated parameters

- `$table` from `AbstractDMLQueryBuilder::normalizeColumnNames()` method
- `$table` from `AbstractDMLQueryBuilder::getNormalizeColumnNames()` method
- `$withColumn` from `QuoterInterface::getTableNameParts()` method
- `$rawSql` from `AbstractCommand::internalExecute()` method
- `$rawSql` from `AbstractPdoCommand::internalExecute()` method

### Remove constants

- `SchemaInterface::INDEX_*`
- `SchemaInterface::PHP_TYPE_*`
- `SchemaInterface::TYPE_*`

### Other changes

- Allow `ExpressionInterface` for `$alias` parameter of `QueryPartsInterface::withQuery()` method;
- Allow `QueryInterface::one()` to return an object;
- Allow `QueryInterface::all()` to return array of objects;
- Add parameters `$ifExists` and `$cascade` to `CommandInterface::dropTable()` and 
  `DDLQueryBuilderInterface::dropTable()` methods.
- Change `Quoter::quoteValue()` parameter type and return type from `mixed` to `string`;
- Move `DataType` class to `Yiisoft\Db\Constant` namespace;
- Change `DbArrayHelper::index()` parameter names and allow to accept `Closure` for `$indexBy` parameter; 
- Change return type of `CommandInterface::insertWithReturningPks()` method to `array|false`;
- Change return type of `AbstractCommand::insertWithReturningPks()` method to `array|false`;
- Rename `QueryBuilderInterface::quoter()` method to `QueryBuilderInterface::getQuoter()`;
- Change constructor parameters in `AbstractQueryBuilder` class;
- Remove parameters and nullable result from `PdoConnectionInterface::getActivePdo()`;
- Move `Yiisoft\Db\Query\Data\DataReaderInterface` interface to `Yiisoft\Db\Query` namespace;
- Move `Yiisoft\Db\Query\Data\DataReader` class to `Yiisoft\Db\Driver\Pdo` namespace and rename it to `PdoDataReader`;
- Add `indexBy()` and `resultCallback()` methods to `DataReaderInterface` and `PdoDataReader` class;
- Change return type of `DataReaderInterface::key()` method to `int|string|null`;
- Change return type of `DataReaderInterface::current()` method to `array|object|false`;
- Change `PdoDataReader` a constructor parameter;
- Remove the second parameter `$each` from `ConnectionInterface::createBatchQueryResult()`
  and `AbstractConnection::createBatchQueryResult()` methods;
- Rename `setPopulatedMethod()` to `resultCallback()` in `BatchQueryResultInterface` and `BatchQueryResult` class;
- Change return type of `key()` method to `int` in `BatchQueryResultInterface` and `BatchQueryResult` class;
- Change return type of `current()` method to `array` in `BatchQueryResultInterface` and `BatchQueryResult` class;
- Remove `null` from return type of `getQuery()` method in `BatchQueryResultInterface` and `BatchQueryResult` class;
- Add `indexBy()` method to `BatchQueryResultInterface` and `BatchQueryResult` class;
- Remove parameters from `each()` method in `QueryInterface` and `Query` class;
- Change return type of `each()` method to `DataReaderInterface` in `QueryInterface` and `Query` class;
- Add `$columnFactory` parameter to `AbstractPdoConnection::__construct()` constructor;
- Change `Query::$distinct` type to `bool` with `false` as default value;
- Change `QueryInterface::getDistinct()` result type to `bool`;
- Change `QueryPartsInterface::distinct()` parameter type to `bool`;
- Change `$distinct` parameter type in `DQLQueryBuilderInterface::buildSelect()` to `bool`;
- Add `QueryInterface` to type of `$columns` parameter of `insertWithReturningPks()` method in `CommandInterface` and 
  `AbstractCommand` class;
- Rename `insertWithReturningPks()` to `insertReturningPks()` method in `CommandInterface` and `DMLQueryBuilderInterface`;
- Remove `$params` parameter from `upsert()` method in `CommandInterface` and `AbstractCommand` class;
- Add default value to `$updateColumns` and `$params` parameters of `upsert()` method in `DMLQueryBuilderInterface` and 
  `AbstractDMLQueryBuilder` and `AbstractQueryBuilder` classes;
- Remove `ParamInterface`, use `Param` class instead;
- Remove `getType()` and `getValue()` methods from `Param` class, use `$type` and `$value` properties instead;
- Remove specific condition interfaces: `BetweenColumnsConditionInterface`, `BetweenConditionInterface`,
  `ConjunctionConditionInterface`, `ExistConditionInterface`, `HashConditionInterface`, `InConditionInterface`,
  `LikeConditionInterface`, `NotConditionInterface`, `OverlapsConditionInterface`, `SimpleConditionInterface`;
- `ConditionInterface` moved to `Yiisoft\Db\QueryBuilder\Condition` namespace;
- Remove `AbstractConjunctionCondition` and `AbstractOverlapsConditionBuilder`;
- Change namespace of condition and condition builder classes;
- Change namespace of expression and expression builder classes;
- Remove `AbstractDsn` and `AbstractDsnSocket` classes and `DsnInterface` interface;
- Remove `Hash` condition;
- Remove `AbstractTableSchema` and add `TableSchema` instead;
- Remove `BetweenColumns` condition;
- Change `QueryBuilderInterface::getExpressionBuilder()` result type to `ExpressionBuilderInterface`;
- Change `DQLQueryBuilderInterface::getExpressionBuilder()` result type to `ExpressionBuilderInterface`;
- Change type of `$tables` to `array` in `DQLQueryBuilderInterface::buildFrom()`;
- Rename `ArrayExpression` to `ArrayValue` and move it to `Yiisoft\Db\Expression\Value` namespace;
- Rename `ArrayExpressionBuilder` to `ArrayValueBuilder` and move it to `Yiisoft\Db\Expression\Value\Builder` namespace;
- Rename `JsonExpression` to `JsonValue` and move it to `Yiisoft\Db\Expression\Value` namespace;
- Rename `JsonExpressionBuilder` to `JsonValueBuilder` and move it to `Yiisoft\Db\Expression\Value\Builder` namespace;
- Rename `StructuredExpression` to `StructuredValue` and move it to `Yiisoft\Db\Expression\Value` namespace;`
- Rename `StructuredExpressionBuilder` to `StructuredValueBuilder` and move it to `Yiisoft\Db\Expression\Value\Builder`
  namespace;
- Remove `StaleObjectException`, `UnknownMethodException`, `UnknownPropertyException` classes;
- Remove `Expression::getParams()` method, use `$params` property instead;
- Allow `ExpressionInterface` for `$table` and `$on` parameters of `QueryPartsInterface` methods: `join()`,
  `innerJoin()`, `leftJoin()`, `rightJoin()`;
- `QueryInterface::getWithQueries()` method returns array of `WithQuery` instances;
- `QueryPartsInterface::withQuery()` method replace "WITH" clause instead of adding before;
- Change `QueryPartsInterface::withQueries()` parameter to variadic with type `WithQuery`;
- In `DQLQueryBuilderInterface::buildWithQueries()` method change first parameter type form `array[]` to `WithQuery[]`;
- Remove `AbstractCommand::refreshTableSchema()` method;
- Property `$retryHandler` is not reset on `AbstractCommand::reset()` method call;
