db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: Share read-only rows across multiple databases
Date Fri, 07 Nov 2008 16:19:43 GMT
Hi Matt,

Knut has just suggested a candidate solution involving table functions.

At first blush it seems to me that your problem has two pieces to it: 
the SELECT piece and the INSERT/UPDATE/DELETE piece.

For the SELECT piece, it seems to me that your current solution will 
work on Derby as well as Oracle. You should be able to declare 
schema-specific views which UNION the global data with the user-specific 

I don't understand the complexity of the INSERT/UPDATE/DELETE piece. Why 
does the application need to issue INSERT/UPDATE/DELETE against the view 
rather than directly against the user-specific table?


Matt Kendall wrote:
> I need functionality in Derby that I don't think it currently
> provides, so I'm looking to extend Derby to provide it. As part of the
> product that I'm working on, I'll be automatically installing Derby
> databases in a standalone server mode. Each database will contain one
> schema with "shared" data, and multiple user schemas. Each user schema
> is expected to be able to union their own data with the read-only
> shared data, but no user schema should be able to write to the shared
> schema, only their own "private" version of the table.
> We have already accomplished this functionality in Oracle 11g using a
> combination of views and INSTEAD OF triggers. We have replaced the
> tables that contain shared data with views that UNION ALL across a
> read-only base table (shared by multiple schema) and a modifiable
> table per schema. We then use INSTEAD OF triggers to redirect writes
> to the modifiable table. This gives us the effect we are looking for:
> Reads from the view transparently merge our read-only and write-able
> data while Writes to the view only alter the write-able data. We
> understand and accept the performance implications of this approach.
> My ultimate goal would be to add INSTEAD OF triggers to derby so that
> we would have parity with our Oracle 11g work. However this
> requirement is coming in late in the game, so my realistic first pass
> will probably be somewhat specific to our product. My hope would be
> that I could keep INSTEAD OF functionality in mind during
> design/coding and contribute that as a patch during a future revision
> of our code.
> I've read the architecture documents on the derby site and started to
> review the code. It seems to me like I should focus my attention on
> the access layer, but I'm not 100% sure. If anyone has any suggestions
> as to where I should start reading and understanding the code, or if
> there is functionality in derby that I missed that will provide what
> I'm looking for, please let me know. Also, any suggestions as to how
> we can provide this split-table functionality would be much
> appreciated.
> Thanks,
> Matt Kendall

View raw message