Linking multi-database information

Question

What are your methods of linking data spread over multiple databases architectures (think MySQL vs PostgreSQL etc), into a single application?

Would you create giant hashtables/arrays to match content against one another? Are there other, more effective and less memory-consuming options for doing this?

If you were to use data both from a MySQL & PostgreSQL source, with no way of converting one DB to the other (application constraints, lack of time, lack of knowledge, ... ), how would you go about it?

Answer 1

At least in the case of MySQL, you can use data from multiple databases in a single query anyway, provided the databases are hosted by the same MySQL Server instance. You can distinguish tables from different databases by qualifying the table with a schema name:

CREATE TABLE test.foo (id SERIAL PRIMARY KEY) TYPE=InnoDB;

CREATE DATABASE test2;
CREATE TABLE test2.bar (foo_id BIGINT UNSIGNED, 
    FOREIGN KEY (foo_id) REFERENCES test.foo(id)) TYPE=InnoDB;

SELECT * FROM test.foo f JOIN test2.bar b ON (f.id = b.foo_id);

In PostgreSQL, you can also qualify table references with a schema name. I'm not sure if you can create foreign key constraints across databases, though.

Answer 2

SQL Relay or another sql proxy. http://sqlrelay.sourceforge.net/

Answer 3

If you're looking to create constraints across RDBMSes - you can't.

I'm facing the same issue with running part of an application off PostgreSQL for where it will benefit, and the rest of MySQL where it's better.

I'm doing multiple inserts keyed off the same format of primary information (in my case a generic user ID), so I'm letting the application handle the logic of making sure to ask for the same ID from both DBs.

There's not really a clean way to do this outside of abstracting it to a class or utility function, though, that I've found.