Topic: database migration (massive schema changes)

I'm currently redoing my entire site and a major part of that is the datamodel has changed a lot, and thus that means the database schema has changed a lot.

I think I'm beyond the realm of rails migrations to update the database. So I'm looking into more standard data migration techniques. Something I'm 100% new to.

Most tools seem to be about migrating from one database vendor to another. Surely there are established techniques for going from database A to B, where A and B are logically equivalent they just have (very) different schemas. It may just be a matter of selecting from database A and inserting into db B. Thankfully my production database is only ~150k once dumped to a sql file, so I can easily experiment with this all I want.

Anyone ever done this? Any pointers? I'm a lil overwhelmed here.

Re: database migration (massive schema changes)

This might have something useful.

http://railsforum.com/viewtopic.php?pid=17072#p17072

Look at the code that dumps a DB to a fixture file. As you dump it you should be able to inspect it and change it. If A.table then data goes to B.othertable fixture then you reload those fixtures into the new DB

Alternatively you should be able to select from DB A and insert into a table in DB B in one query.

Re: database migration (massive schema changes)

tortoise wrote:

It may just be a matter of selecting from database A and inserting into db B.

That's how I would do it, just create a script to read one database, interpret the data how you want and create a new record in the other database. If you aren't dealing with hundreds of thousands of records then you can use a Ruby script and load the Rails environment.rb file for your project. This way you have access to all of your Rails models.

Railscasts - Free Ruby on Rails Screencasts

Re: database migration (massive schema changes)

I've worked through 99% of my migrating from one schema to the other. It really wasn't that hard. I basically just did

INSERT INTO newDB.table (newCol1, newCol2, ...) SELECT oldCol1, oldCol2 ... FROM oldDB.table

And just made sure the newCol and oldCol entries matched up as needed. That did almost everything I needed to do. But I'm stuck on one thing.

Here is the difference between the two schemas in ruby-speak

#### OLD SCHEMA
class User < ActiveRecord::Base
    has_many :weigh_in_entries
end

class WeighInEntry < ActiveRecord::Base
    belongs_to :user
end

##### NEW SCHEMA
class User < ActiveRecord::Base
   has_one :weight_log
end

class WeightLog < ActiveRecord::Base
    belongs_to :user
    has_many :weigh_in_entries
end
class WeighInEntry < ActiveRecord::Base
    belongs_to :weight_log
end


So as you can see below, the weigh_in_entry now has a weight_log_id column instead of user_id

mysql> describe f3f_development.weigh_in_entries;
+---------------+---------+------+-----+------------+----------------+
| Field         | Type    | Null | Key | Default    | Extra          |
+---------------+---------+------+-----+------------+----------------+
| id            | int(11) |      | PRI | NULL       | auto_increment |
| caption       | text    | YES  |     | NULL       |                |
| date          | date    |      |     | 0000-00-00 |                |
| weight_log_id | int(11) | YES  |     | NULL       |                |
| weight        | float   | YES  |     | NULL       |                |
+---------------+---------+------+-----+------------+----------------+
5 rows in set (0.00 sec)

mysql> describe f2fprod.weigh_in_entries;
+---------+---------+------+-----+---------+----------------+
| Field   | Type    | Null | Key | Default | Extra          |
+---------+---------+------+-----+---------+----------------+
| id      | int(11) |      | PRI | NULL    | auto_increment |
| caption | text    | YES  |     | NULL    |                |
| date    | date    | YES  |     | NULL    |                |
| user_id | int(11) |      |     | 0       |                |
| weight  | float   | YES  |     | NULL    |                |
+---------+---------+------+-----+---------+----------------+


So how do I handle this extra level of indirection? I gave all the users weight_logs, so I've got all the weight_log_ids waiting to be used. Something like

INSERT INTO new.weigh_in_entries 
(id, caption, date, weight_log_id, weight)
SELECT id, caption, date, %%%%SOMEHOW SELECT WEIGHT_LOG_ID HERE%%%%, weight
from old.weigh_in_entries

Last edited by tortoise (2007-03-24 17:56:44)

Re: database migration (massive schema changes)

yay, I figured it out smile

INSERT INTO f3f_development.weight_entries
    (id, caption, date, weight, weight_log_id)
SELECT
    oldWIE.id, oldWIE.caption, oldWIE.date, oldWIE.weight * 1000, newWL.id
FROM
    f2fprod.weigh_in_entries oldWIE,
    f3f_development.weight_logs newWL
WHERE
    newWL.user_id = oldWIE.user_id

(the * 1000 is in there because the new db stores weight in grams, the old one stored it in kilograms)

Last edited by tortoise (2007-03-24 17:56:25)