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?
