in Web Development

SQL Schema to CodeIgniter DBForge

11/2/2013 – Awhile back I got a comment from someone asking me about my MySQL to CodeIgniter DBForge stuff. I’m not really sure what happened, but I felt bad about not being able to help this person. So I finally got off of my duff and I added it to github.  If you want the code you can find it here: MySQL to CodeIgniter DBForge  If you do end up using the code for something, anything, let me know. I’d love to see what people can or will do with it.  

It’s fairly easy, in my humble opinion, to move an existing website that is not in CodeIgniter over to CodeIgniter.  However one of the topics that people, such as myself, may have never known about is the migrations option.  If you don’t know about migrations it’s worth your time to read up on them (you should also read up on Database Forge as that’s where the bulk of the work takes place!). I’ll try my best to sum it up for you.  Basically it allows you to make incremental changes to your database.  So if you need to add an active/inactive field to one of your tables you can make a migration. This may not sound like a big deal but here’s what it becomes a big deal.  If you bring another member on your staff and they’ve got a development environment and you’ve been making changes directly to the database how are they going to know when the database has been changed and they need to update their database?  That’s where migrations are the most powerful.  I’d add to this that it allows you the ability to quickly move your site from one host to another as the migrations are automatically ran.  The one caveat to this is that it does not pre-populate the data, so be aware of that.  So go do yourself a favor and read through migrations and dbforge.  I’ll wait here for you.

If your first thought was “OMG…that’s a lot of code writing to be done when I’ve already got the SQL Schema”, I’m there with you. But I’ve got a solution cause I have a TON of SQL code already written that was never going to end up in migrations with dbforge.  Just wasn’t going to happen.  However my handy little script has made this a non-issue.

So click on over to my SQL Schema to CodeIgniter DBForge page and get to converting your databases.  It’s currently in alpha and has only really been tested using phpMyAdmin and MySQL, so I don’t know how it works with other databases (like anyone uses anything else..J/K!).

Basically what you need to do is this. Go into your phpMyAdmin interface and click on the table you want to convert.  Once your in that table click on export (see image below)

PhpMyAdmin-Table-Export

 

Once you are in the export menu, you need to change a couple of options to just get the SQL Schema and not the whole database.  You want to click on “Custom – display all possible options” This will give you a lot of other options.  Under Output check “View output as text“.  Lastly under Format-specific options click on structure underneath Dump Table.  After that just click on the Go button and it will produce something like below.

PhpMyAdmin->Table->Export->Result

 

The highlighted area is what you need to copy and then paste into my migration interface! It will return you the CodeIgniter code that you can insert into your migration script (it doesn’t provide all of the information I should note.  You will need to create the base class and the downgrade path).

$this->dbforge->add_field("agentid varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT ''");
$this->dbforge->add_field("officeid int(10) unsigned NOT NULL DEFAULT '0'");
$this->dbforge->add_field("firstname varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT ''");
$this->dbforge->add_field("lastname varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT ''");
$this->dbforge->add_field("agenttitle varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL");
$this->dbforge->add_field("phone1 varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL");
$this->dbforge->add_field("phone2 varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL");
$this->dbforge->add_field("mobile varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL");
$this->dbforge->add_field("fax varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL");
$this->dbforge->add_field("email varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL");
$this->dbforge->add_field("url varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL");
$this->dbforge->add_field("location varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL");
$this->dbforge->add_field("information text CHARACTER SET utf8 COLLATE utf8_unicode_ci");
$this->dbforge->add_field("testimonials text CHARACTER SET utf8 COLLATE utf8_unicode_ci");
$this->dbforge->add_field("dirname varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL");
$this->dbforge->add_field("idx text CHARACTER SET utf8 COLLATE utf8_unicode_ci");
$this->dbforge->add_field("active char(1) NOT NULL DEFAULT 'y'");
$this->dbforge->add_key('agentid', TRUE);
$this->dbforge->add_key('officeid');
$this->dbforge->create_table('agents');

That’s it. I took one of my larger CodeIgniter installs and converted it over to migrations in about 30 minutes. Pretty slick huh? If you notice any problems or have any suggestions for improvements I’d love to hear them. Please comment here.

Happy migrating (and of course coding!)!

  1. Thanks for this. I forked it and adapted it for ExpressionEngine + composite indices (didn’t do this for primary keys, though). I think later versions of CI might support composite keys/indices like their documentation has been saying for years, but I am not sure. EE uses its own version of CI so can’t really roll stuff back.

    Hopefully other add-on developers might find this useful if they have to make custom tables.

Comments are closed.