Migrations and Transactions

Hi,

I'm setting up a Migrations controller for installing/upgrading my application and am trying to get it working with transactions.

The issue I'm having is that it seems, regardless of whether the migration is successful or not, CI updates the version number in DB.

This means that the next time I run the migration again (after having sorted the previous problem with the query or whatever it is), it thinks my DB is up to date, forcing me to manually change the DB version number before running the migration again.

It would be great to have an option to change this functionality? Either stop the version being updated in DB automatically (so I can manually add this in my migration library), or have some other smart functionality which only updates the version if the migration is successful.

This is what I'm trying to build now using transactions and some custom migration code, but this issue is preventing it from working smoothly.

Please let me know if there's a way around this or if it's possible to get this looked at for the next update.

Thanks,
Jay.

Can anyone weigh in on this? @Narf?

Transactions are irrelevant ... what's important is how you "handle" migration failures, which you haven't explained.
If the migration itself doesn't break, of course CI is going to update the version number.

Thanks for the reply. The core migration code doesn't "break", but it still can "fail" in the sense of a SQL error or some custom validation code.

The code the core uses is (in the version() function):

PHP Code:

$migration[0] = new $migration[0];
call_user_func($migration);
$current_version = $number;
$this->_update_version($current_version); 


So the "_update_version" function runs without any checks on call_user_func to see the result returned. If it checked the response from the user migration function, I could return false and it would leave the DB version untouched.

Alternatively, if there was a way to disable the "update version" portion of the code and allow me to handle it manually, that'd work too.

The reason I need this is I'm creating a custom CLI utility for my app which will migrate all accounts (I have a multi-DB setup, with a master DB and multiple client DBs).

And one other thing which I can't figure out - how to get the "target version" from CI_Migration. It'd be handy to be able to pull the current migration it's up to in the loop, as my utility reports "Upgrading to version 201605......" as it goes through and updates master and client DBs.

I don't think the target version is stored in the object anywhere, but it'd be useful if it was.

Thanks for your time.

You just need to end script execution - throw an exception, die(), exit(), etc.

Hmm, the thing is there is potential for one DB to be upgraded correctly, but another to fail with the same migration.

Also, the current migration library currently will only update the version for one DB (whichever is selected at that time), so I've had to implement a custom "update_version" function and run it manually for all other DBs as it loops through, so there's this discrepancy between the "normal migration" and the custom ones...

I might have to just have to override the "version" function and implement the custom functionality I need in there and keep an eye out for any future changes to it in CI updates.

Thanks for the help.

The current version stores its own data in a single database - the default one - because that's what the library itself needs.

It doesn't in any other way concern itself with database interactions, nor does it put any limits on that.

UPDATE: I've since implemented my own modified Migration class (not using default CI one), but am still having an issue with transactions not working properly.

I just want to confirm, do transactions/rollbacks work with dbforge functions? Add/drop columns etc? Also would there be an issue if I'm using trans_begin(), then call_user_func to load some classes which run queries, then trans_rollback() (so they're separated across different classes/files)?

Appreciate any input.

You're thinking in PHP terms and on that level there's no obstacle for transactions to work with DBForge.

But it's not that simple, you're asking the wrong question ... SQL itself doesn't allow CREATE/ALTER/DROP statements to be isolated in transactions.

You're thinking in PHP terms and on that level there's no obstacle for transactions to work with DBForge.

But it's not that simple, you're asking the wrong question ... SQL itself doesn't allow CREATE/ALTER/DROP statements to be isolated in transactions.

Ahh okay, yeah I was afraid of that. That's a shame - I guess I'll have to find work-around or just put up with it.

Thanks for the help.