Adding stored procedures to migration script

[JimSterling]

Hello, I have looked through the docs and there is no mention of adding a stored procedure to a db migration (so that way the procedure is available when the migration script is run). Is there any way to add that stored procedure to the migration script?

[lorenzo]

There is currently no way of doing that, nor actual plans for doing it.

But it is actually kind of easy to implement yourself. You send raw queries for creating the stored procedures in the up method and delete them in the down method`

[rquadling]

@lorenzo, I'm in the progress of creating repeatable migrations - has some similarity to seeds, but they aren't seeds. It's a Work In Progress. No timescales as yet though.

[lorenzo]

@rquadling that sounds like a mysql_dump. Is that similar?

[rquadling]

@lorenzo Imagine you start a small project by yourself. You do everything by hand because you can. You now start employing others and you all start the features in your own branches. A view is created, straight simple SQL in an migration. All fairly simple.

Now move forward a couple of years. You have 100's views, stored procedures, triggers, UDFs, etc.

Now 2 developers need to make a change to the same view. How do you deal with the up/down aspect? You still need to migrate the view to include the new content. In branch 1, a migration is created yesterday with the down being the current version. In branch 2, a migration is created today, with the down being the current version. Neither are in production, so the down version, and the start position of the up version are the same.

Branch 1 is merged into master, tested and deployed. The migration is run. The view is updated.

Branch 2 is merged into master and deployed. The migration is run. The view is now missing the new content from Branch 1.

One solution is to use something called Repeatables.

Very similar to seeds, but repeatables will always be run after migrations. The idea is that you always run all of them. You can incorporate a hash check to stop the view actually being replaced when it isn't necessary.

With the example I gave earlier, each branch would modify the same file (well a branched version). So Branch 1 adds its lines. These are merged into master, deployed, repeatables run. View is updated.

Branch 2 is merged. Any merge conflicts need to be resolved now. Once all done, can be deployed and repeatables run and the view is now updated with the additional content of branch 2.

Of course unit testing prior to deployment takes place. In the old pattern, you should have a lot of broken tests as the view from branch 1 was lost when branch 2 was merged and the migrations run.

And at that stage, there is no easy way to fix the issue. The versioned migration from branch 2 has no knowledge of the changes in a different migration from branch 1. Add to that, prior to the rollback taking place in reverse execution order (if you've set the option that way), the migrations COULD be rolled-back in a really bad order. Now, at least, you can "undo" the migrations that have been run in the reverse order they were executed in.

With repeatables, version control is the tool you use to deal with rolling back. If you need to roll back the view, you would also need to rollback the code, rollback the migrations and then redeploy and rerun the repeatables.

The RFC I created is #1046. I have a branch : https://github.com/cakephp/phinx/tree/repeatable_migrations, this is a bit out of date as I'm doing the work in between multiple tasks.

[lorenzo]

@rquadling sounds really cool!