0

I have a 'legacy' DB2 database that has many other applications and users. Trying to experiment with a rails app. Got everything working great with the ibm_db driver.

Problem is that I have some tables like schema1.products, schema1.sales and other tables like schema2.employees and schema2.payroll.

In the ibm_db adapter connection, I specify a schema, like schema1 or schema2, and I can work within that one schema, but I need to be able to easily (and transparently) reference both schemas basically interchangeably. I don't want to break the other apps, and the SQL I would normally write against DB2 doesn't have any of these restrictions (schemas can be mixed in SQL against DB2 without any trouble at all).

I would like to just specify table names as "schema1.products" for example and be done with it, but that doesn't seem to jive with the "rails way" of going about it.

Suggestions?

GNUMatrix
  • 3
  • 2

2 Answers2

0

How do i work with two different databases in rails with active records?

Community
  • 1
  • 1
Will
  • 8,102
  • 5
  • 30
  • 32
  • Well, not what I was expecting, but I guess that's kind of the point. This seems to work great so far, though it seems a little overkill to have a new database connection just for a different schema. But if this is the "rails way" then I simply have to get used to it. Fortunately I only have two schemas to deal with so this should work reasonably well. Thanks so much! – GNUMatrix Apr 08 '10 at 19:21
  • If you wanted you could create views from one db to the other but that defeats the purpose of splitting them up in the first place – Will Apr 08 '10 at 19:34
  • Within DB2 itself I can also use aliases, just a bit tedious to setup especially if any table names are duplicated from one schema to another. The thing that gets me is that this is all within the same database. The schemas are basically used as formal table prefixes more than anything. My guess is that in other databases they may have a more fundamental purpose but not so much within DB2. I'm trying to stick to rails conventions as best I can as straying too far from that tends to cause lots of trouble, so this multiple database connection thing will have to do for now. – GNUMatrix Apr 08 '10 at 23:48
  • Would table_name class method in your model address this? – nessence Sep 21 '10 at 06:08
0

Schemas in DB2 are a very handy way to provide separate namespace to different applications. For example, you can separate all database objects for an application called "recruiting" from say application called "payroll". You can have objects (tables, views, procedures etc.) with the same name reside in multiple schemas and not colide with one another. Having your application set a schema is a handy way for it to say "hey, I am a payroll and I only want to work with my objects". So, what happens when you want to work with objects owned by another application? Well, in traditional procedural programming languages your application code would explicitly specify the schema when referencing an object in another schema or you would just do a SET CURRENT SCHEMA to switch to another schema. The problem with ORMs like ActiveRecord in Ruby on Rails is that you are not supposed to use SQL i.e. you don't have a good "Rails way" to specify schema when referencing an object. You can use find_by_sql and qualify your objects in the SQL statement but this is not what RoR people will consider to be good Rails. You can fix things on the DB2 side. You can define a view per table in the "foreign" schema but you will have to take care to name the view so that it does not colide with what you already have in your primary schema. And, when you do that, you will undoubtedly create names that are not true Rails names. Rails people are very proud of the "Rails way". It makes it very easy to create new applications. Rails is really awesome in this space. However, when it comes to integration with what is already out there Rails ... how do we say it ... sucks. I suggest you will have to accept to do things that are not the best examples of the Rails Way if you want to work with existing database structures.

Leon Katsnelson
  • 450
  • 3
  • 5
  • 1
    (opinion piece!) That's kind of been my overall impression with Rails in general... I think I have a pretty good grasp of how they want things to be done, and just as importantly, why, and when starting new projects I tend to really agree with them. However, it is absolutely terrible at trying to integrate with anything that could possibly have come from somewhere else. The mere thought of running rake on a production database that wasn't entirely crafted from the bottom up within rails is truly terrifying. I keep coming back though as they do make progress and stuff does get fixed. – GNUMatrix Dec 05 '10 at 19:24