INFO WANTED : Handling views, stored procedures, triggers in Phinx.

[rquadling]

Hi.

Currently Phinx is pretty good for table orientated migrations. Sure, if your team is working on several different branches with their own migrations, you need to take care if column positioning is important, but there again, a "release manager" role would oversee the integration and hopefully all is good.

But for things like views, stored procedures, triggers, etc., anything that requires SQL code, it is less good.

Does anyone have any process that covers handling migrating of non table related elements?

I have no preference on any particular approach, I just want our developers to have a process that is clearly understood on what to do for migrating these elements.

Regards,

Richard.

[shadowhand]

Richard, I'm not sure that Phinx is the right tool for that... one of the goals of Phinx is to be database agnostic, and things like stored procedures have specific server syntax.

My vote on this would be 👎.

[rquadling]

That's why I asked for info on what devs are actually doing when they do need to migrate non table related schema elements. I know Phinx can't handle this OTB ATM. And I agree that Phinx may not be the right tool for this.

[iamjochem]

hi Richard,

I'm in the same boat - I'd like to be able to migrate 'other stuff', I have in the past implemented a fairly simple strategy (on top of CodeIgniter Migrations of all things!?!) whereby each migration [class] file automatically got a set of accompanying directories (up & down) in which I could place SQL files (I used ZERO-padded number prefixes on the file names to determine run order) that would automatically be read and executed (it assumed 1 query per file) when a given Migration was run.

I wanted to implement the same kind of thing on top of Phinx but ran into the "problem" that it is not even possible to specify a custom Migration class to use as the basis for my own migrations (which would be nice clean way of extending Phinx functionality - given that Phinx uses interfaces there is no reason not to allow this) - see here: #314

It is a pity that DB migration tools in general (based on cursory search in the PHP arena) seem to want to concentrate on:

1. being agnostic
2. doing lots of magic to infer change in either direction

Personally I'd really like to see something that concentrated on just running explicit queries in a given order for a given direction, at the end of the day the magic change() functionality just ends up creating an SQL query (either ALTER TABLE, CREATE TABLE or DROP TABLE) ... aand personally I prefer to define those queries explicitly in my migrations anyway (less magic, less abstraction, less mindf***).

Phinx is quite capable of run explicitly defined SQL queries (that is the basis on which it's more magical functionality is built upon after all) ... all that is really missing is a way to avoid pasting large queries directly in the up() and down() (which make them rather unmanageable from a human perspective) ... and if I ever get a positive reply on my own issue (#314) I'm going to code something for that.

Basically what I am aiming to do is write a custom Migration base class that implements a few sugar/helper methods whose underlying functionality come down to doing something like the following (but then without having to think about dirnames or direction):

    $this->query(file_get_contents(__DIR__ .  '/' . basename(__FILE__) . '/up/001.sql');

PS: I don't think there is any DB migration tool that will ever mitigate the need for a "release manager" to intervene (or at least provide oversight) when merge branch integration)

[shadowhand]

Personally I'd really like to see something that concentrated on just running explicit queries in a given order for a given direction

You don't need a migration tool for this. A simple glob, sort, and foreach script could accomplish it in less than 100 lines. The entire point of a migration tool is to do exactly what you think it shouldn't be doing: be agnostic and be really smart about the direction of change.

[rquadling]

So how do you handle amending views/triggers/UDFs/etc. in a multi-developer environment? What process do you use to ensure you DO have the ability to rollback when you break something? I'm not looking for Phinx to do this, but as Phinx users, I'm hoping some of you are working on projects big enough to warrant at least SOME thought on this issue.

[heskyji]

We have a lot of stored procedures and functions. At the moment, we just use execute function with raw SQL statements for procedure and function definitions. We just have to make sure we captured the old definitions in the down function too. The pain point is that it is almost impossible to tell what's changed in the pull request.

[rquadling]

WOW! Wasn't even aware I'd opened this issue over 2 years ago. A lot has changed. One change is that I am now a contributor to this project.

So, with that, I'd like to close this issue and direct you to #1046.

I'm looking for opinions, ideas, suggestions, etc. relating to the RFC. Please take a read through and let me know what you all think. The more info I have, the better chance that the end result will be useful to those who need it, rather than just what I think it needs.

Thank you.

[viniciushsantana]

Hi, I'm sorry to comment on a old/closed issue. But this issue is exactly around my question:

Is using execute() still the only way to create views?

Thanks!

[rquadling]

@vegbrasil, for the time being it is. I am starting work on repeatables this week (have work level dev time as we need the same functionality). So hopefully in the not too distant future, repeatables will be a 'thing'.