db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: Overriding one database's content with another
Date Mon, 14 Sep 2009 13:11:44 GMT
Hi Brian,

Combining data from two databases can be done by using database 
procedures or table functions, but you will probably get better 
performance if you can just maintain two separate schemas in the same 
database.

What you are trying to accomplish sounds a lot like an outer join. You 
might want to read about this kind of join here: 
http://en.wikipedia.org/wiki/Join_%28SQL%29

The following script may help you understand more about how to use outer 
joins:

connect 'jdbc:derby:memory:dummy;create=true';

create schema data;
create schema user_data;

create table data.t( a int );
create table user_data.t( a int );

insert into data.t( a ) values ( 1 ), ( 2 );
insert into user_data.t( a ) values ( 2 ), ( 3 );

  select *
  from data.t left outer join user_data.t
  on data.t.a = user_data.t.a
union
  select *
  from data.t right outer join user_data.t
  on data.t.a = user_data.t.a
;

Hope this helps,
-Rick

Brian Shields wrote:
> I'm currently developing a Java version of Mids' Hero Designer 
> <http://www.cohplanner.com/>, using Derby as my data 
> storage/retrieval. The data in the database is representative of the 
> archetypes, powersets, powers, and enhancements in the online game 
> City of Heroes <http://www.cityofheroes.com/>. Updates to the program 
> would include updated values in the database to reflect changes to the 
> game.
>
> One of the features I'd like for the program is to allow user 
> customization of the database without interfering with the "official" 
> values, and without updates to the official values interfering with 
> user changes. User customization would be used, for example, to input 
> upcoming changes to the game by the user, before an official program 
> update is created. Alternatively, as a way to show the effects of 
> hypothetical changes when making a suggestion for the game.
>
> My idea for achieving this was to maintain two databases: 'Data', 
> storing the official information, and 'User_Data', storing the 
> user-generated changes from Data. What I want is for any SELECT 
> statement to be passed to both DBs; results in User_Data which are not 
> in Data would be returned (information added by the user), results in 
> Data which are not in User_Data would be returned (information not 
> touched by the user), and results in both databases would return the 
> version from User_Data (information modified by the user).
>
> The problem is, I'm not exactly certain how to go about it. I suppose 
> I need to create some sort of abstraction layer between my queries and 
> that actual database access, but how would I combine the ResultSets?


Mime
View raw message