public function testNullAndDefaultColumnChanges()
{
$old = <<<XML
<schema name="public" owner="ROLE_OWNER">
<table name="table" primaryKey="id" owner="ROLE_OWNER">
<column name="id" type="int"/>
<column name="col" type="text" default="'xyz'"/>
</table>
</schema>
XML;
$new = <<<XML
<schema name="public" owner="ROLE_OWNER">
<table name="table" primaryKey="id" owner="ROLE_OWNER">
<column name="id" type="int"/>
<column name="col" type="text"/>
</table>
</schema>
XML;
// drop defaults: stage 1 drop default
$this->common_diff($old, $new, "ALTER TABLE `table`\n ALTER COLUMN `col` DROP DEFAULT;", '');
// add defaults: stage 1 set default
$this->common_diff($new, $old, "ALTER TABLE `table`\n ALTER COLUMN `col` SET DEFAULT 'xyz';", '');
$nullable = <<<XML
<schema name="public" owner="ROLE_OWNER">
<table name="table" primaryKey="id" owner="ROLE_OWNER">
<column name="id" type="int"/>
<column name="col" type="text" null="true"/>
</table>
</schema>
XML;
$notnullable = <<<XML
<schema name="public" owner="ROLE_OWNER">
<table name="table" primaryKey="id" owner="ROLE_OWNER">
<column name="id" type="int"/>
<column name="col" type="text" null="false"/>
</table>
</schema>
XML;
// NULL -> NOT NULL: stage 1 alter to NOT NULL (no update or stage 3 alter)
$this->common_diff($nullable, $notnullable, "ALTER TABLE `table`\n MODIFY COLUMN `col` text NOT NULL;", '');
// NOT NULL -> NULL: stage 1 alter to NULL
$this->common_diff($notnullable, $nullable, "ALTER TABLE `table`\n MODIFY COLUMN `col` text;", '');
$nullable_with_default = <<<XML
<schema name="public" owner="ROLE_OWNER">
<table name="table" primaryKey="id" owner="ROLE_OWNER">
<column name="id" type="int"/>
<column name="col" type="text" null="true" default="'xyz'"/>
</table>
</schema>
XML;
$notnullable_with_default = <<<XML
<schema name="public" owner="ROLE_OWNER">
<table name="table" primaryKey="id" owner="ROLE_OWNER">
<column name="id" type="int"/>
<column name="col" type="text" null="false" default="'xyz'"/>
</table>
</schema>
XML;
mysql5_diff::$add_defaults = true;
// NULL -> NOT NULL with defaults: stage 1 alter, updating nulls to default is done implicitly by mysql
$this->common_diff($nullable_with_default, $notnullable_with_default, "ALTER TABLE `table`\n MODIFY COLUMN `col` text NOT NULL DEFAULT 'xyz';", '');
// NOT NULL -> NULL with defaults: stage 1 alter, no need to change anything else
$this->common_diff($notnullable_with_default, $nullable_with_default, "ALTER TABLE `table`\n MODIFY COLUMN `col` text DEFAULT 'xyz';", '');
$notnullable_without_default = <<<XML
<schema name="public" owner="ROLE_OWNER">
<table name="table" primaryKey="id" owner="ROLE_OWNER">
<column name="id" type="int"/>
<column name="col" type="text" null="false"/>
</table>
</schema>
XML;
// NULL DEFAULT 'xyz' -> NOT NULL: stage 1 alter, no need to update nulls
$this->common_diff($nullable_with_default, $notnullable_without_default, "ALTER TABLE `table`\n MODIFY COLUMN `col` text NOT NULL;", '');
// NOT NULL -> NULL DEFAULT 'xyz': stage 1 alter
$this->common_diff($notnullable_without_default, $nullable_with_default, "ALTER TABLE `table`\n MODIFY COLUMN `col` text DEFAULT 'xyz';", "");
}
