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
