You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by Matt Kendall <ma...@iname.com> on 2008/11/03 17:22:51 UTC

Share read-only rows across multiple databases

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

Re: Share read-only rows across multiple databases

Posted by Matt Kendall <ma...@iname.com>.
> 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?

Because nothing's ever easy :). Long story short, we're currently
developing an automatic installer for our main product that requires
this database split. Our main product is large and complex with
multiple points that would have to be altered to support the split
model. Worse, since our product is essentially a programming language,
there is user-level code that touches the database directly.  We
simply cannot predict all of the touch points that our users may have
created.

Our early work on the automated installer attempted to update the
database layer and pretty much everything in the product broke. At the
time, our only requirement was to support Oracle, and its updatable
views solved the problem nicely so that's the solution we went with.
There is potentially a new requirement to support Derby as well, so
I'm evaluating the options.

Thanks for the suggestions!

Matt

Re: Share read-only rows across multiple databases

Posted by Rick Hillegas <Ri...@Sun.COM>.
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 
data.

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?

Thanks,
-Rick

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
>   


Re: Share read-only rows across multiple databases

Posted by Knut Anders Hatlen <Kn...@Sun.COM>.
Matt Kendall <ma...@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