Legacy databases and migrating

Working on a project where the database will be migrated from MS SQL Server to MySQL. All fine, however the client is requesting that the schema remains the same. This means that table names are not pluralized and id is not just ‘id’ but the table name and id (i.e. table_id) All in all it’s not a huge database, I’m looking at 30 tables total, but want to consider first the extra work I’ll have to do to make it all play together nicely. Any opinions ?

and if I remember correctly from AWDWR the id column is probably not a big deal, but the singular table names disturb me.

TIA Stuart

http://en.wikipedia.org/wiki/Dark_ambient

In my case I made a small Ruby script who uses DBI interface. It keeps connection to old and new databases ( in may case that was the same flavor - PgSQL ) and recreates the model from legacy MixedCase way to RoR way, including data migration.

This script somewhat generalized, but this is possible to do more generalization to make it possible to port from one DB flavor to other.

PS: I spent few hours for that script and it saves me a days of hassle :slight_smile:

All the Best! Sergey.

Dark Ambient wrote:

Since I am always having to work with some old, legacy schema, I do this as a matter of course on all my model files:

class MyClass < ActiveRecord::Base   set_table_name 'whatevernameIwant'   set_primary_key 'nameOfPrimaryKeyColNeedntBeid' end

Then you;re good to go. -Ralph

Ike wrote:

Since I am always having to work with some old, legacy schema, I do this as a matter of course on all my model files:

class MyClass < ActiveRecord::Base   set_table_name 'whatevernameIwant'   set_primary_key 'nameOfPrimaryKeyColNeedntBeid' end

Then you;re good to go. -Ralph    Not true. It works quite good for most of the cases but not all of them. If your column name starts with capital letter, then it starts to srew up a lot of plagins that expecting behavior like record.filedName or record.field_name because in this case Ruby has conversation that if first letter is capital then it's a constant. in this case you can get it only by record['FieldName'], so you see my point here.

That was my reason to make a script that converts tables from MixedCase to lower_case

All the Best! Sergey.

Wait -- Isnt MySQL not case sensitive (at least <5.0, and I believe

Ike wrote:

Wait -- Isnt MySQL not case sensitive (at least <5.0, and I believe   

=5.0 that can be specified) so why not make them all be lowercase in your rails app? -Ralph     

Because I am using PostgreSQL, and this RDBMS is case sensitive. So the same can be true for other flavors such as Oracle.

All the Best! Sergey.

Sergey,

Would you mind posting the script here, or is it proprietary? Thanks.

Sergey Kuznetsov wrote: