[CRITIQUE REQUEST] Migration Practices

[eluser]Lewis Cowles[/eluser]
Hi all,

I am very new to the CodeIgniter framework and wanted to ask about something I am now doing in migrations.

Code:

<?php

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

class Migration_Schema extends CI_Migration {
protected $schematic = array(
  'users' => array(
   'fields' => array(
    'id' => array('type' => 'INT','constraint' => 20,'unsigned' => TRUE,'auto_increment' => TRUE,'null' => FALSE),
    'username' => array('type' => 'VARCHAR','constraint' => '64','null' => FALSE),
    'password' => array('type' =>'VARCHAR','constraint' => '64','null' => FALSE),
    'person_id' => array('type' => 'INT','constraint' => 20,'unsigned' => TRUE,'null' => FALSE),
    'role_id' => array('type' => 'INT','constraint' => 20,'unsigned' => TRUE,'null' => FALSE),
    'linked_id' => array('type' => 'INT','constraint' => 20,'unsigned' => TRUE,'null' => TRUE),
    'active' => array('type' => 'BOOLEAN','default' => FALSE,'null' => FALSE),
    'created_by' => array('type' => 'INT','constraint' => 20,'unsigned' => TRUE,'null' => TRUE),
    'modified_by' => array('type' => 'INT','constraint' => 20,'unsigned' => TRUE,'null' => TRUE),
    'created_on' => array('type' => 'DATE','null' => FALSE),
    'modified_on' => array('type' => 'DATE','null' => FALSE),
   ),
   'keys' => array('id')
  ),
  // ...
);
public function up()
{
  foreach($this->$schematic as $table => $config_options)
  {
   foreach($config_options['fields'] as $field)
   {
    $this->dbforge->add_field($field);
   }
   foreach($config_options['keys'] as $key)
   {
    $this->dbforge->add_key($key, TRUE);
   }
   $this->dbforge->create_table($table);
  }
}

public function down()
{
  foreach($this->$schematic as $table => $config_options)
  {
   $this->dbforge->drop_table($table);
  }
}
}

is this "naughty" coding? or is this acceptable?

[eluser]PhilTem[/eluser]
Have a look at the migration topic in the user's guide, it will show you that you can have code that's easier to read

http://ellislab.com/codeigniter/user-gui...ation.html

though your code is actually pretty similar to what you would get using the db-forge class


Additionally you may want to have a look at the database forge class which can be found here

http://ellislab.com/codeigniter/user-gui...forge.html

[eluser]Lewis Cowles[/eluser]
I have read the docs for both, thanks. I hope you can see how my code is similar to that in the docs, but saves me rewriting for every new table by looping through a variable, this should possibly be a const, but for now it works as-is.

I have since made the code shorter to type, by defining some class constants, to fill in the arrays for types like the id field and some fixed length fields including the date and time-stamp fields I intend on using.

The main reason for my question is that I tend to write very data-centric apps with lots of inter-connected relational tables. For now, rather than try to make CI guess if the table is ISAM or INNO, I have been soft-relating tables. Also the system I am developing has to connect to an MSSQL DB & a MYSQL DB... But thats another story.

I did try separating the code into different files, one for each table. Unfortunately I get an error when having one file per db_model per revision.

I also considered writing the code into each model that extends my base class for using the database from a model (aptly named DB_Model), but it seemed to me to be superfluous to the model and a violation of the separation of concerns.

I was just wondering if anyone else has a "neater" more "correct" way to do this.

On a separate note, has anyone else noticed that if there is a problem with migrating the db the migration table is still set up, which is a pain as it means that I have to manually remove it. I suppose I could just use the dbforge library, without the migration library,

After thinking long and hard, I concluded using migrations for this type of tasks is the "CodeIgniter" way to do these things, especially as writing the raw SQL would take more planning and this seems to fit into my AGILE work-flow.

I have looked into the Migration class and can only assume that

[eluser]jmadsen[/eluser]
Hi Lewis,

I think your last quote - "After thinking long and hard, I concluded using migrations for this type of tasks is the “CodeIgniter” way to do these things, especially as writing the raw SQL would take more planning and this seems to fit into my AGILE work-flow." - is about spot on.

we tried using it for a few projects, but found that they were really cumbersome to set up, didn't seem to handle a few basics like composite keys, weren't "self healing", and generally were too much bother.

What they ARE good for is:

-- After the basic schema is set up, they allow a team of devs to make small adjustments and not worry about others having an updated schema.

-- If you are rolling out a product with an installation process, like a CMS or CI Bonfire

[eluser]Lewis Cowles[/eluser]
I have composite primary keys using migrations... at least I think I do.

I'm sure I do on some of my tables as I have defined several many-to-many relationships (not in any posted due to limit). anyway migrations are likewise slowing my project and I may have to pull the plug on them very soon!

For me the problem is mainly that it's easier for me to have an install.php, but if I am using a codeIgniter System I would prefer to use codeIgniter for everything, it's really bugging me that all these php frameworks seem to be geared around a pre-built SQL db

just checked http://ellislab.com/codeigniter/user-gui...ml#add_key

Code:

$this->dbforge->add_key('blog_id', TRUE);
$this->dbforge->add_key('site_id', TRUE);
// gives PRIMARY KEY `blog_id_site_id` (`blog_id`, `site_id`)

and you can using the above code have composite primary keys

[eluser]jmadsen[/eluser]
ah, you are right - can't recall what it was we had to workaround.

in any case, it was just too slow to turn all our db schema into the db_forge code. perhaps we weren't going about it very well, but didn't seem worth the time.

[eluser]Lewis Cowles[/eluser]
The strange part for me about these migrations, is that I feel, they should not be a separate file prefixed with the version number. SVN, GIT etc all deal with versions so us programmers don't have to...

Also I cannot see the benefit of using the migrations, over simply checking for a tables existence and creating it if it does not exist? perhaps an Ellislabs employee can shed some light on this matter as effectively even using CI_Migration as a base class, we are using dbforge anyway

[eluser]WanWizard[/eluser]
The version number is not meant to indicate a version of the migration file, but it's place in the order of migrations. It makes sure that if you install an application that has lots of migration files, they are all executed in thr correct order. Any vcs system in use has nothing to do with this mechanism.

The reason of using migrations is that during the evolution of an application, database changes are introduced with new versions of the application.

If you, like in any other proper development environment, use dev/local, testing, staging, deployment and production environments, you've got servers running different versions of the application. When you move something from one environment to the next (i.e. you upgrade or downgrade an installation), you need to adjust the database schema accordingly. Migrations will take care of this for you.

This gives a good indication of how the migrations work.

[eluser]Lewis Cowles[/eluser]
Hi Wanwizzard,

Thanks for clearing that up ;-) . I always build my DB's once and extend / enhance them by adding extra tables for extended features. I'm not sure about having different versions between servers, that sounds like a lot of extra work, but each to their own eh.

I Suppose migrations are not for me or people that like to do things once. I have just switched my code from using migrations to a more appropriate setting for my application and dev setup. I even saved myself some memory and processing time, thanks all

[eluser]jmadsen[/eluser]
So, what about the initial database schema? Do you recommend building that from a migration file as well for an initial "install", or only use it for the subsequent changes?

We were trying to build the entire thing from a migration and found that just to be a huge waste of time, and so changed to using an initial build sql file 6 then migrations for later changes

I would like to hear different ways people have used migrations in their apps