[LRUG] Multiple databases, migration, integration...

[Jocke Selin]

On 8 Jan 2010, at 11:15, Andrew McDonough wrote:
> Disclaimer: my suggestion might be a really bad idea (I only just
> tried it and have certainly never used it in production).  I'd like to
> hear what other people think though, as it's something I've considered
> in the past to port our numerous legacy PHP apps to rails.  It does
> appear to work though.
> 
> Use MySQL views:
> You could use MySQL views to create rails friendly views of the
> database tables.  You would then write the classes assuming that
> everything is named as rails likes it.  For example, assuming two
> simple tables in the old database, "user" and "dept", you could create
> views called users and departments:
> 
> CREATE TABLE user (
>  userID int(11) NOT NULL auto_increment,
>  name varchar(255),
>  deptID int(11),
>  PRIMARY KEY  (userID)
> ) TYPE=MyISAM;
> 
> CREATE TABLE dept (
>  deptID int(11) NOT NULL auto_increment,
>  name varchar(255),
>  PRIMARY KEY  (deptID)
> ) TYPE=MyISAM;
> 
> 
> CREATE VIEW users as select userID as id, name, deptID as
> department_id FROM user;
> CREATE VIEW departments as select deptID as id, name FROM dept;
> 
> Then in your model:
> 
> class Department < ActiveRecord::Base
>  has_many :users
> end
> 
> class User < ActiveRecord::Base
>  belongs_to :department
> end
> 
> I've tried creating, saving and loading both of these entities and
> querying the associations between them, and it appears to work.  The
> advantage of this is you could write clean model code, and assume well
> named tables, meaning if you ever decide to migrate the database to a
> rails naming convention, you wouldn't need to touch your code.  The
> disadvantages are that you would end up with twice as many table
> views, and you would need to maintain two things for each table (e.g.
> adding a field would mean adding it to both the table and the view).
> Again, I have only tested this for two simple tables.  It might break
> for more complex schemas, but I thought it was worth investigating.

This is the way to do it. Let the DB do the work for you.

Instead of maintaining two "things" for each table, you could script the code generation somehow. It could generate the "old" CREATE statement, the "new" CREATE VIEW statement, and possibly an ALTER statement?

Cheers,

/JS