Greetings brothers, I seek your knowledge and wise council on a matter where many are at work and must avoid trampling each others feet.

The project I am working on uses DBIx::Class::Schema::Versioned to version the MySQL schema of the database used by the application. The schema has an integer version number that monotomicaly increases whenever a developer makes a change to the schema.

When the developer makes a change he writes an upgrade script containing SQL statements to upgrade the schema. The script is named something like DB-50-51-MySQL.sql, where 50 is the current schema version, and 51 is the next. There is also a downgrade script in case it is necessary to back out the change.

The problem.

The problem is that as the team has grown, and the tempo of development increased there are a lot of changes to the database schema, and we are getting a lot merge conflicts with these upgrade scripts.

The current schema merged into trunk might be at version 50, but there are probably at least three different features at various stages of development and code review that make changes to it and have different upgrade scripts (with the same name) to move it to version 51. The contents of the upgrades are not in conflict because they make unrelated changes to different tables, but as soon as one of those changesets gets merged, the others can't be because they are trying to change the same file.

While the process of resolving the merge conflict is not that difficult, but it is tedious, prone to error, and getting annoying because of how often it is necessary. Sometimes git merge or git rebase claims to have resolved a merge conflict automatically when it has not and this causes even worse breakage if it is undetected.

I have looked into some alternatives to DBIx::Class::Schema::Versioned such as DBIx::Class::DeploymentHandler and DBIx::Class::Migration, and while they have many useful features, they all appear to be stuck to the idea of integer version numbers, so I would experience the same problem with merge conflicts if I switched to one of them.

Possible solutions.

Some on my team have suggested using feature names for the scripts, So that the upgrade script would be named as Add_feature.MySQL.sql, (With a similar name for the corresponding downgrade script). As the names would not be re-used there would be no conflict between the upgrade files. When applying an upgrade the database would maintain a list of features that have been applied, and run any upgrade scripts for any that are missing.

Another proposed solution is to date the upgrade scripts instead of numbering them, where the date is when it is first created (not when it is eventually merged). This would provide a form of soft ordering of the upgrade scripts.

A third solution is to keep all the upgrade & downgrade SQL in a single large perl file, where each set of upgrade commands is a separate entry in an ordered array. When conflicting versions of the file are merged there would still be a conflict, but it would be less tricky to resolve because there would be no need to rename files

The problem with all of these proposed solutions, is that there is no out of the box support for them in DBIx::Class::Schema::Versioned or any of the alternatives that I can find. It would be necessary to spend time on developing a custom solution to manage schema upgrades.

Can the monastery suggest a way forward. Have you solved a similar problem yourself, if so how? Is there another module out there for maintaining database schema versions that solves this more elegantly?