Solved

Capturing an error to handle partial failures in a CodeIgniter migration

Posted on 2013-11-17
1
153 Views
Last Modified: 2015-05-06
Below is an example of the kind of migrations I'm using in CodeIgniter. Performing one migration can include several queries.

The trouble I'm having during development of such migration code is where at least one, but not all, of the queries are successful. Currently, this leaves the migration partially done, so that it's not re-runnable. It slows down development; I have to manually change the schema back to it's original state before I can retest the code.

Schema changes can't use database transactions, so any code achieving a roll back would need to be coded manually. If I was to restructure the code so that each SQL was grouped as a pair (an "up" query, and a "down" query), then an error could be caught, and the schema changes could be rolled back.

Can someone explain if it's possible for error handling to be set up to trigger a roll back, and how it would be done? Thanks

<?
defined('BASEPATH') OR exit('No direct script access allowed');

class Migration_Alter_table_files extends CI_Migration {

	public function __construct($config = array()) {
		parent::__construct($config);
	}

	public function up()
	{
		$sqls = array();
		$sqls[] = <<<END
ALTER TABLE files RENAME parent_file_id  TO parent_folder_id;
END;

		$sqls[] = <<<END
ALTER TABLE files 
ADD CONSTRAINT component_files_fk FOREIGN KEY (component_files_id)
      REFERENCES component_files (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION;
END;

		$sqls[] = <<<END
ALTER TABLE files 
ADD CONSTRAINT parent_folder_fk FOREIGN KEY (parent_folder_id)
      REFERENCES file_folders (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
END;

		foreach ($sqls as $sql) {
			$this->db->query($sql);
		}
	}

	public function down()
	{
		$table = "files";

		$sqls = array();
		$sqls[] = <<<END
ALTER TABLE files DROP CONSTRAINT parent_folder_fk;
END;
		
		$sqls[] = <<<END
ALTER TABLE files RENAME parent_folder_id  TO parent_file_id;
END;
		
		$sqls[] = <<<END
ALTER TABLE files DROP CONSTRAINT component_files_fk;
END;
		
		foreach ($sqls as $sql) {
			$this->db->query($sql);
		}
		#exit;

	}

}

Open in new window

0
Comment
Question by:Terry Woods
1 Comment
 
LVL 25

Accepted Solution

by:
Squinky earned 500 total points
Comment Utility
That approach is reasonable in general, but it's not always possible to go back - for example if you drop a table or field you can't undelete it.

You could copy the tables to be altered to different names, apply your migrations to the copies, then swap them into place by renaming them after they have all been created successfully. That can also have the benefit that you retain copies of the old tables.

The only issue with it is it's hard to retain foreign key constraints like that (if you're using them), so you might have to drop constraints and recreate them afterwards.

It's very difficult to retain any kind of transactional integrity through all of this, and it can take a long time if your tables are large.

Automated schema change scripts usually make the mistake of doing each change on a table in a separate ALTER TABLE statement. This multiples the time it needs to take by the number of alterations; you should combine multiple ALTER statements into a single one, and the alterations can all happen in one go. That said, I don't think it's possible to do multiple foreign key constraint changes at once, but most operations are ok.

You could look at the online schema change script that's part of Percona's MySQL toolkit: http://www.percona.com/software/percona-toolkit
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

This article will explain how to display the first page of your Microsoft Word documents (e.g. .doc, .docx, etc...) as images in a web page programatically. I have scoured the web on a way to do this unsuccessfully. The goal is to produce something …
There is a huge demand for CodeIgniter among the PHP web developers due to its dynamic features and benefits these days. It is one of most popular and agile open source PHP framework for creating robust web applications in PHP web development field.…
The purpose of this video is to demonstrate how to set up the WordPress backend so that each page automatically generates a Mailchimp signup form in the sidebar. This will be demonstrated using a Windows 8 PC. Tools Used are Photoshop, Awesome…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now