Re: Aggregating multiple databases

[prev] [thread] [next] [Date index for 2004/10/17]

Simon Cozens wrote:
> I don't know if this is possible, but I thought I'd ask to see if anyone has
> any ideas about how to do it.
> 
> I have an application, Songbee, which organises songs and song lyrics. It uses
> an SQLite database with CDBI, and the all-important song table (Songbee::Song
> class) holds information about title, lyrics, key, copyright information and
> so on.
> 
> One of the users has asked for the ability to use multiple databases of songs; 
> so, for instance, to have an english.db and a japanese.db and a spanish.db,
> and if they choose to use all three at once, have the contents of the song
> table from all three available from Songbee::Song->retrieve_all, as thought
> they were all in the one table. 
> 
> I'm guessing I may well have to write Songbee::Song to inherit from an
> aggregator class that collects data from multiple Song classes generated on
> the fly with Class::DBI::Loader, but that's as far as I can get. Can anyone
> think of how this might be achieved?

Couldn't you use Class::DBI::View and a SQL UNION?

   __PACKAGE__->setup_view(<<SQL);
     SELECT id, title, etc
     FROM songs_english
     UNION
     SELECT id, title, etc
     FROM songs_japanese
     etc...
   SQL

?