db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <Knut.Hat...@Sun.COM>
Subject Re: Share read-only rows across multiple databases
Date Fri, 07 Nov 2008 15:52:44 GMT
Matt Kendall <matt_kendall@iname.com> writes:

> 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.

Hi Matt,

For the read part of your problem, you may be able to solve it by using
table functions.

http://db.apache.org/derby/docs/10.4/devguide/cdevspecialtabfuncs.html

For each shared table, you can create a table function that connects to
the read-only database, selects all rows and returns the ResultSet. Then
you can create views that take the union between the private tables and
the shared tables.

To make them writable would be trickier, though. Currently, neither
views or table functions are updatable in Derby, I think. But I think
there are some updatable virtual tables internally in Derby. Search the
code for VTI (which stands for virtual table interface) to find the
relevant places. If you're interested in contributing code back to
Derby, I think making the table functions updatable is the way to
go. That would be a very useful feature for others too, I'm sure.

Perhaps some of our VTI experts could provide some more pointers.

-- 
Knut Anders

Mime
View raw message