RENAME TABLE instead of re-creation

[ghost]

This issue is related to the schema comparator. We use it to run migrations.

If I change the table name it will result into DROP TABLE and CREATE TABLE sql queries. This will result into a data-loss. It would be better to use the RENAME TABLE feature.

See https://github.com/doctrine/migrations/issues/461

[deeky666]

Which DBAL version are you using and which platform does this affect? Also some sort of information/code how to replicate this is necessary otherwise we cannot do much about it.

[Grundik]

Doctrine\DBAL\Schema\Schema code for that are not platform-specific and about 12 years old now:

    public function renameTable($oldName, $newName)
    {
        $table = $this->getTable($oldName);
        $table->_setName($newName);

        $this->dropTable($oldName);
        $this->_addTable($table);

        return $this;
    }

See also: https://github.com/doctrine/migrations/issues/17

This method should not exist with such name, its a trap.

[Killerkiwi2005]

At the very least this method should be removed...

[NiklasBr]

Maybe add this to Milestone 5.0.0, I would love that!

[derrabus]

Do you want to work on this topic?

[NiklasBr]

Which solution would you most like? Change the method name to something that suggests that it will delete content, or change the internal actions to use the RENAME TABLE grammar?

[derrabus]

I think, we should use RENAME TABLE on platforms that support it.

[Grundik]

...and leave trap armed for other ones.;)

[derrabus]

First of all, it would be interesting to know, how many "other ones" there are. Haven't researched it, tbh.

How would you deal with platforms that don't support renaming tables?

[Grundik]

I think, this trap must be disarmed. Best time for that was several years ago, second best time is now. So, if database dont support RENAME, then this migration should throw an exception, but not discard user data.

[Grundik]

And another option could be to remove this method entirely (or at least rename it), to not cause the situation, when developer intended RENAME, but production dependencies was not updated yet, so data will be lost.

[NiklasBr]

Looking at the platforms at https://www.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/platforms.html, I think this could work. although I have not looked at which versions need to be supported for each platform…

• MySQL ✅ RENAME TABLE
• MariaDB ✅ RENAME TABLE
• Oracle ✅ ALTER TABLE ... RENAME TO
• Microsoft SQL Server ✅ sp_rename procedure
• PostgreSQL ✅ ALTER TABLE ... RENAME TO
• IBM DB2 ✅ RENAME TABLE
• SQLite ✅ ALTER TABLE ... RENAME TO

How would you deal with platforms that don't support renaming tables?

I would probably throw an Unsupported-style exception.

[Grundik]

Its a very dangerous method now, and making it usable is also dangerous. This method should not be called ever, if user cares about his data. So real rename should be new method, to not allow any confusion or mistake, since cost of calling that method could be devastating.

And current one should just fail loudly (or dont even exist), instead of discarding data.

[derrabus]

Best time for that was several years ago

Let's switch to solution mode, shall we.

[derrabus]

Looking at the platforms at https://www.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/platforms.html, I think this could work. although I have not looked at which versions need to be supported for each platform…

Thank you, that looks very promising indeed. The versions that support renaming would be interesting indeed. Right now, it looks like we don't even need to discuss the case that a platform does not support renaming.