[Dbix-class] Relationships across different databases

[Darren Duncan]

At 12:20 AM -0800 11/22/06, apv wrote:
>I ask to save myself a couple of hours of playing with code in case 
>it won't work; just thinking out loud and it seemed like an 
>interesting point either way; sort of forced pair coding.
>
>Can I have a relationship across databases?
>
>What I'm considering doing is using SQLite for a chat db/table but 
>I'd like it to be able to reference users (with belongs_to) from the 
>"real" MySQL db the rest of the site runs on.
>
>Since it's a table that will get permanent churn (chats won't be 
>saved for more than x-lines or hours or whatever) I thought it might 
>be better to set it up this way and never worry again instead of the 
>possibility of a MySQL table running into a few million lines of 
>deleted rows and gumming up the works. In the distant past I did 
>this same sort of thing with Storable files and it worked great.
>
>Also considering doing it for site metrics as those definitely go 
>into millions of rows quickly.

Are you wanting to do just read-only queries against both databases 
together, or also writing queries?  In any event, I think the easiest 
way to do what you want is to use some federated/proxying type of 
database driver, where in the virtual database it connects to, some 
tables can be from one actual source, and some from others.  If it 
looks like the things are in the same database, doing a relationship 
between them, for read-only at least, should be easy.  Although it 
may not perform quickly, depending on how smart the driver is.

On the other hand, why not just put everything in either MySQL or 
SQLite?  That's a much simpler solution, I think.  Also, if rows have 
been deleted, why should they gum up anything?  They aren't there to 
have any effect.

  -- Darren Duncan