Converting database structure or .sql file into Yii migration

adminYii0 Comment
8 min. reading

Yii Framework 1.x migration system is very powerful and gives you much more power and control over database update process. But it becomes a horrible nightmare, if you have to deal with .sql files and convert them into Yii migration files manually.

This article should help you in that strike or at least ease your work.

If you have any doubts, why the hell migrations are better than using plan .sql files then this blog post should provide you with at least some reasons.

The easy way

If you want to convert particular database structure, saved in .sql file, or dump entire tables into Yii 1.x migrations, you can of course use fabulous schmunk42’s database-command just the way, you use each migration.

If you have only .sql file, you have to simply import it into some temporary database, dump everything using database-command to Yii migration and made possible tune-ups. You’ll find more about yiic database command in [my another post](Using yiic database command for dumping current database structure to migration.txt).

The hard way

Things get a little bit more complicated, when you’re importing data from some external sources or you want your tables to include only very selected amount of data or merge few columns or tables at source into one column, table or view at destination etc.

I’m not to experienced in area of Yii migrations, so I use the hammer-way in situations like that. By hammer, I mean phpMyAdmin! :>

Steps can include:

  1. Dump source data into .sql file and then import it into some temporary database.
  2. Open phpMyAdmin, go to SQL tab and write [a join SQL query](An example of very basic join query in MySQL.txt), that will fetch all data required by you into one single view (see below).
  3. Check Show all and confirm, that you understand, that your browser may be fired into one-way Mars mission.
  4. Scroll down to the bottom of generated page and click Export.
  5. Select PHP array as format (brilliant!).
  6. Hit Go (magic happens) and save generated .php file.

Bang! You’re done. You have a PHP array, very similar to the one used by migrations system, that you should tune-up within minutes. If you used AS clause in your SQL query (see below) to change columns names to match your destination database scheme, you can be done even without any changes.

A simple example

Here is an example, how I used above algorithm to import [Drupal users into my own application’s table](Importing content data from Drupal-based blog to own, Yii-based application.txt). This is a very simple example of only using phpMyAdmin to convert .sql file (imported into database) to .php file containing arrays ready to be used in Yii migration. This example does not contain any joins or fetching data from a few tables. You can find some of a bit more complex examples in [my article](Importing content data from Drupal-based blog to own, Yii-based application.txt) about importing data from Drupal to own application.

SELECT
    mail AS name,
    mail AS email,
    '2' AS level,
    login AS login_date,
    '1' AS login_count
FROM
    users
LIMIT
    3,100

I have executed this query and exported entire results set into .php file containing PHP arrays. Then, I opened it in Notepad++ (you can use any other text editor that supports replacements including new-line characters n) and copied entire contents of first array (i.e. ignored topmost array):

$users = array(
    //This was copied, following and preceding lines were ignored 
);

You can also simply delete first 12 and last two lines in each file generated by phpMyAdmin.

Then, I had to perform few replacements according to below replacement list

  • array( –> $this->insert('users', arrayn(n (to convert array opening to CDbMigration command’s opening; fix table name, as required).
  • => –> => (this one was cosmetic, as I hate spaces surrounding associations in arrays),
  • ',' –> ',n ' (for clarity and indent),
  • ), –> n)); (to convert array closure into CDbMigration command’s closure; replace last item manually, as it does not have ), in the end),
  • =>NULL,' –> =>'',n ' (to convert NULLs to empty strings and do last tune ups of indent).

And… I was done, having contents ready to be pasted into public function up() in my migration.

Example for queries containing strings

Unfortunately, above steps works just fine only, if your SQL query does not contain strings, that may contain characters used in above replacement list. And since most of our tables contains some strings, which can contain some periods or commas, this makes above replacement table pretty useless.

In this case you should use a bit different list:

  • n –> and `r` --> and eventually t –> “ (to get rid all the memos and long text spanning into multi lines and other white characters)
  • array( –> n$this->insert('contents', arrayn(n (same as above, but with fix to previous replacement; again keep your eye on table name).
  • => –> => (if you agree with my anti-spaced-associations madness),
  • ',' –> ',n ' (for clarity and indent),
  • ),n$this-> –> n));nn$this-> (to convert array closure into CDbMigration command’s closure; replace last item manually, as it does not have ), in the end),
  • =>NULL,' –> =>'',n ' (to convert NULLs to empty strings and do last tune ups of indent).

And what about classic associative array instead of migration?

Let’s say that you have a bit different task. Instead of full featured migration, you want an associative array, where first column in data exported by phpMyAdmin should be taken as a key for this new array. How to approach this?

Well, solution is quite similar. You only need to know name (key in original array) of the column, which should be turned into new array key and name of following column. In my case, it was nid and title. With this knowledge, engage following replacement table:

  • n –> and `r` --> and eventually t –> “ (to get rid all the memos and long text spanning into multi lines and other white characters)
  • array('nid' => –> n (get rid of entire fragment and replace it with just new-line; nid is the name of first column),
  • ','title –> '=>arrayn(n 'title (title is the name of second column),
  • => –> => (I won’t say this third time! :),
  • ',' –> ',n ' (for clarity and indent),
  • ),n –> n),n (to properly close array; replace last item manually, as it does not have ), in the end),
  • =>NULL,' –> =>'',n ' (to convert NULLs to empty strings and do last tune ups of indent).

Using column name assures you, that this replacement will take place only for first key / column in array, not for all of them.

Now, you can put your replacement’s final product into any array.

Final words

If query used in this example is too easy or not complex enough for you, then follow to [this article](Importing content data from Drupal-based blog to own, Yii-based application.txt).

Leave a Reply