Rename and change column definition at once

[hafezdivandari]

On MySQL, It's possible to rename and change column definition in one clause. It is also supported on doctrine/dbal package. But how to do it in Laravel?

Lets assume we have an integer column $table->integer('foo'); and we want to rename it to bar and change its type to char:

Schema::table('users', function (Blueprint $table) {
  $table->char('foo')->nullable()->change();
  $table->renameColumn('foo', 'bar');
});

This compiles to:

[
  'ALTER TABLE users CHANGE foo foo CHAR(255) DEFAULT NULL'
  'ALTER TABLE users CHANGE foo bar INT NOT NULL'
]

As you see, the first clause changes the type as expected but not name, then the second clause changes the name but reverts the type definition. The compiled query won't change if you reorder the blueprint commands.

any solutions?

PS: I know I can use 2 separate Schema/Blueprint closure but I want this to be done on a single DB transaction, to be rollbacked easily in case of error.

[Sophist-UK]

You need to install and use DBAL I think.

[hafezdivandari]

Thank you for your answer, as I mentioned on the first line, I have already installed DBAL package, but that is not the issue.

[Sophist-UK]

I have had another look at the API definitions with the hope that you might be able to chain them, but renameColumn returns a Fluent object, so that is not possible.

I assume that you have tried doing it the other way around i.e.

Schema::table('users', function (Blueprint $table) {
  $table->renameColumn('foo', 'bar');
  $table->char('bar')->nullable()->change();
});

What SQL DDL does this produce? Something like:

ALTER TABLE users CHANGE foo bar INT NOT NULL
ALTER TABLE users CHANGE bar bar CHAR(255) DEFAULT NULL

If the above doesn't work, then I suspect (without having looked at the code) that each $table->... produces one line of code, so if you wanted to create:

ALTER TABLE users CHANGE foo bar CHAR(255) DEFAULT NULL

then this would have to be done in one-line of PHP i.e. something like:

Schema::table('users', function (Blueprint $table) {
  $table->char('bar')->nullable()->change('foo');
});

The API for change() currently has no parameters, so it should be possible to extend the change method in the ColumnDefinition object to include a parameter for the old column name. So you specify the new column name in the Blueprint char() method (which is consistent with creating a new column) and use the old column name in the change() method.

I will take a look this evening at how difficult this would be, and if not too difficult I will create a PR for it. :-)

[Sophist-UK]

I worked out how the attributes work (and why there appeared to be no code), and looked into how changes are actually applied.

But in the end, it looks like DBAL does not have the ability to change a name except by its version of RenameColumn. So to fix this correctly would require adding a change to DBAL, waiting for it to be included in a release. I think if I achieved that, ->change('new_name') might then work with a one-line change in Laravel framework.

Please let us know how you get on with reversing the two lines. :-)

[hafezdivandari]

I assume that you have tried doing it the other way around i.e.

Schema::table('users', function (Blueprint $table) {
  $table->renameColumn('foo', 'bar');
  $table->char('bar')->nullable()->change();
});

What SQL DDL does this produce?

This causes an exception:

Doctrine\DBAL\Schema\Exception\ColumnDoesNotExist : There is no column with name "bar" on table "bars".
 \vendor\doctrine\dbal\src\Schema\Exception\ColumnDoesNotExist.php:16
 \vendor\doctrine\dbal\src\Schema\SchemaException.php:111
 \vendor\doctrine\dbal\src\Schema\Table.php:785
 \vendor\doctrine\dbal\src\Schema\Table.php:383
 \vendor\doctrine\dbal\src\Schema\Table.php:370
 \vendor\laravel\framework\src\Illuminate\Database\Schema\Grammars\ChangeColumn.php:110
 \vendor\laravel\framework\src\Illuminate\Database\Schema\Grammars\ChangeColumn.php:80
 \vendor\laravel\framework\src\Illuminate\Database\Schema\Grammars\ChangeColumn.php:64
 \vendor\laravel\framework\src\Illuminate\Database\Schema\Grammars\ChangeColumn.php:41
 \vendor\laravel\framework\src\Illuminate\Database\Schema\Grammars\Grammar.php:86
 \vendor\laravel\framework\src\Illuminate\Database\Schema\Blueprint.php:135

I also tried this:

Schema::table('users', function (Blueprint $table) {
 $table->renameColumn('foo', 'bar');
 $table->char('foo')->nullable()->change();
});

that compiles to:

[
 'ALTER TABLE users CHANGE foo foo CHAR(255) DEFAULT NULL'
 'ALTER TABLE users CHANGE foo bar INT NOT NULL'
]

So as I said on the question:

The compiled query won't change if you reorder the blueprint commands.

[Sophist-UK]

This could be classed as a bug somewhere - either in Laravel or in DBAL, though I am really not sure which.

Firstly, processing a renameColumn is done through entirely different code from the columnChange, so I think that fixing this way of coding such a migration cannot easily be done inside Laravel.

It would be relatively easy for Laravel to process it if it is coded as ->change('old_name'), but (as far as I can tell) at present DBAL does not have the code needed to produce a single ALTER TABLE statement in the correct format. So either:

1. We first need additional DBAL functionality to allow a name change to be specified as part of a column change and then produce a single ALTER TABLE in the correct format, and once that is merged to specify that version as minimum DBAL and make fairly minor code changes in Laravel (if any - but should be relatively trivial) to make it work.

2. We have special code in Laravel to recognise the name change and tweak the resulting ALTER TABLE to implement the change of name.

But to get it fixed, someone needs to either report it as a bug or code a PR. My best bet would be to report it as a bug on DBAL, and code a special case improvement PR on Laravel. But given that a Taylor refuses many PRs, it is probably worth checking with him first whether he would be minded to approve a well written PR to address this. (I suspect not because the code would be a fudge and not a long-term well-architected solution, and the use case is rare.)

[hafezdivandari]

I believe doctrine/dbal package already supports change and rename column in one clause, check this:

https://github.com/doctrine/dbal/blob/82483595da680180c1d360ea7fbff1ea8fb08142/src/Platforms/AbstractMySQLPlatform.php#L661-L662

If you are looking for the Laravel code, here is where change is get compiled:

framework/src/Illuminate/Database/Schema/Grammars/Grammar.php

Lines 74 to 87 in 9611fda

	/**
	* Compile a change column command into a series of SQL statements.
	*
	* @param \Illuminate\Database\Schema\Blueprint $blueprint
	* @param \Illuminate\Support\Fluent $command
	* @param \Illuminate\Database\Connection $connection
	* @return array
	*
	* @throws \RuntimeException
	*/
	public function compileChange(Blueprint $blueprint, Fluent $command, Connection $connection)
	{
	return ChangeColumn::compile($this, $blueprint, $command, $connection);
	}

You may also take a look at ChangeColumn class: https://github.com/laravel/framework/blob/9611fdaf2db5759b8299802d7185bcdbee0340bb/src/Illuminate/Database/Schema/Grammars/ChangeColumn.php

[Sophist-UK]

The key code is actually at

framework/src/Illuminate/Database/Schema/Grammars/ChangeColumn.php

Lines 85 to 94 in 9611fda

	foreach ($fluent->getAttributes() as $key => $value) {
	if (! is_null($option = static::mapFluentOptionToDoctrine($key))) {
	if (method_exists($column, $method = 'set'.ucfirst($option))) {
	$column->{$method}(static::mapFluentValueToDoctrine($option, $value));
	continue;
	}
	$column->setCustomSchemaOption($option, static::mapFluentValueToDoctrine($option, $value));
	}
	}

.

This code is looking for a DBAL setXXXXX method and AFAIK there is no such method to change name, So at present there is no well architected way to tell DBAL that you want to do the rename with the above code.

So to do this in an architected way, you would have to:

1. Make ->change('old_name') method record the old name in the change attribute rather than just true (can't remember whether it does this or not, but a trivial change to make it so).
2. Have DBAL support setOldname or similar.
3. Change mapFluentOptionToDoctrine and / or mapFluentValueToDoctrine to handle the rename.

The code changes in Laravel would be trivial and it would be well architected.

If we wanted to use the current DBAL code without changes, I have no idea what calls you would need to make to DBAL to stack up the rename so it was processed in the code you pointed to, but AFAIK it can not be done in the existing foreach loop - it would need to be done in special-case code in the Laravel ChangeColumn class.

[Sophist-UK]

I would be interested to know what now happens after #45258 which removes the need for DBAL for column renames.

[hafezdivandari]

It is still impossible to modify a column (using change) without DBAL, I'm working on it: #45281

[Sophist-UK]

Great!!!!

[hafezdivandari]

It is now fixed on 10.x using native schema operations: #45487

$table->enum('difficulty', ['easy', 'hard'])->change();
$table->renameColumn('difficulty', 'hardness');

or simply on MySQL:

$table->enum('difficulty', ['easy', 'hard'])->renameTo('hardness')->change();