Matt Kendall <[EMAIL PROTECTED]> 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
