Add helpers for plugin install migrations with existing database tables

[engram-design]

On the rare occasion, when a plugin isn't uninstalled properly, the database tables created in the install migration stick around. This can happen for a variety of reasons, including:

• Past project config issues
• Craft 2, or old Craft 3 issues
• Plugin migration issues
• composer remove plugin to remove the plugin files, but Craft still thinks it's "installed"

For the last item, that can happen pretty commonly. For example, someone tries out a plugin by installing it, but doesn't like it. They forget to uninstall it properly (running the uninstall migrations) and while the files are removed, Craft still considers it installed. Months/years down the track, they install the plugin again and they'll be smacked with an error about existing tables.

We have a few options as plugin developers in our install migrations:

• Check for any existing tables and remove them
• Check for any existing tables and rename them or archive them to prevent data loss

We can't rely on potentially stale existing database tables, so we need to start fresh. But then we also might lose any existing data, so that's dangerous. Commerce doesn't even handle this.

So in an effort to provide some best-practices for plugin developers, I might propose Craft provide a way to handle this. As I've mentioned, we can of course handle this as plugin developers, and for those that need special handling of existing tables are free to do so. But for 99% of plugins out there, I think some sane defaults will help end users frustrated with install issues from plugin developers who might not be aware of this potential edge-case.

Some proposals:

Adding a createTableOrArchive() method

This would replace the current createTable() method, which would be a safe check against existing tables, and rename any - creating as normal.

$this->createTableOrArchive('{{%mytablename}}', [
    'id' => $this->primaryKey(),
    ...
]);

Another syntax, maybe more helpful?

$this->createTable('{{%mytablename}}', [
    'id' => $this->primaryKey(),
    ...
], true);

Where the third parameter is a check for existing tables, and to rename any. Maybe this should be opt-out in Craft 4 due to a maybe breaking change?

Adding an archiveTable() method

This would rename any existing tables, and skip if there were none. This would separate the calls to createTable() for clarity, but the downside is this could be easily missed.

$this->archiveTable('{{%mytablename}}');

$this->createTable('{{%mytablename}}', [
    'id' => $this->primaryKey(),
    ...
]);

Just add conditionals!

As I've mentioned, I'm well aware we could do:

if ($this->db->tableExists('{{%mytablename}}')) {
    MigrationHelper::renameTable('{{%mytablename}}', '{{%mytablename_old}}');
}

$this->createTable('{{%mytablename}}', [
    'id' => $this->primaryKey(),
    ...
]);

And maybe that should just be the best-practice approach?

[brandonkelly]

Just added a new Migration::archiveTableIfExists() method, for the next release. This will need to be called in addition to createTable() (9a30d8b).

I’ve also drafted a PR for Commerce to start taking advantage of this, once Craft 4 Beta 4 goes out. (craftcms/commerce#2765)

[engram-design]

Nice one @brandonkelly thanks for the consideration!

[brandonkelly]

Craft 4.0.0-beta.4 is out now with this method.