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

[Andrew McDonough]

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.

Regards,

Andrew McDonough



2010/1/8 Andrew Stewart <boss at airbladesoftware.com>:
>
> On 8 Jan 2010, at 10:33, Riccardo Tacconi wrote:
>>
>> Yesterday I decided to refactor a page developed in PHP with Rails. I am
>> using two MySql databases, one for the PHP, one for Rails. Now I am not sure
>> if I should use only one database or two. The DB for PHP has name in the
>> singular form (dept, user, article...), the primary keys are something like
>> 'deptID', 'user_id', in few words is an old rubbish inherited DB.
>
> I am working in a similar situation where I am running an old Mambo (PHP)
> site alongside a new Rails site, while slowly moving functionality from the
> former to the latter.
>
> I have a single MySQL database containing both the PHP system's tables and
> the Rails app's tables.  This way I only need to connect to a single
> database, which is the way Rails likes it.  To avoid table name clashes I
> prefixed all the Rails app's tables with a simple prefix, configuring
> ActiveRecord appropriately (c.f. the table_name_prefix method).
>
> Have a class for each PHP table you need to use, and use ActiveRecord's
> table_name and primary_key methods to cope with the old table names.  I do
> this and it all works perfectly.
>
> When I want to move functionality and data from PHP-land to Rails-land, I
> use Tim Riley's marvellous acts_as_importable plugin.  He came up with an
> excellent pattern for migrating data, and for my system it works very well.
>
> http://github.com/timriley/acts-as-importable
>
> Regards,
>
> Andy Stewart
> -------
> http://airbladesoftware.com
>
> _______________________________________________
> Chat mailing list
> Chat at lists.lrug.org
> http://lists.lrug.org/listinfo.cgi/chat-lrug.org
>