UpdatableCachedRowsetIssues
Issues around supporting modifiable rowsets
In Creator 2 and NB 5.5, in visualweb projects you can create a rowset based on a join of two or more tables that can then be viewed and updated by the user.
This page discusses why I believe this is an issue an we should stop supporting it for new projects in NB 6.
Initially, it seems natural enough to want to create a view of a join of two tables. It's easy enough to write a query and voila, there is the view as a table.
ID NAME ROLE DEPDATE DESTCITY --- ----- ------ --------- ------------- 1 Joe CEO January New York 1 Joe CEO March Amsterdam 1 Joe CEO June Paris 2 Jill SVP January Addis Ababa 2 Jill SVP March Khartoum
For reporting and viewing, this is probably OK. But when you start thinking about modifying the data, things get a little weird:
INSERT: Let's see, do I insert a new row in the table? What should go in the row? What if I want to add a new trip to an existing person? How do I distinguish this from inserting a new person AND a new trip? The join table muddies these semantics.
UPDATE: I discussed this already. For some columns, if you update the column, then *before* you submit only one row is changed, and then when you submit and refresh, magically the column is updated in other rows.
For other columns, you aren't actually allowed to update. I admit this is a side effect of the rowset architecture, but it's what we have, and it's non-intuitive.
DELETE: This one seems pretty manageable: deleting a row means deleting a trip, not deleting the person. But it's still a very odd view of the data.
The reason behind this ugliness is because we are displaying a virtual table, not a real table, and it doesn't display reality accurately. It's worse than a de-normalized table, because some of the fields of the table are actually *identical* even though they look like they're different, and even though the view treats them as different.
So I actually think we're doing our users a disservice by supporting updates on a join table. Instead we should be guiding them to representing their data clearly and accurately.
I think it's worthwhile to note that JPA doesn't let you create an "Entity" that is mapped to a join or even a view. I suspect this is for the same reasons I discuss above: trying to work out the semantics for this is fuzzy and unclear. Joins are useful for queries and reporting, but not for actually managing your data.
Update: Jim Davidson gave a great response which reveals this as a well known issue in the database research community:
Some elaboration on the behavior that you're seeing:
Updating through a join is an instance of the general View Update problem.
The problem is that a view is a (typically lossy) transformation. When an update occurs in the view space, it may have zero, one, or several possible interpretations in the underlying data space.
Whether the update is "safe" (i.e., unambiguous and consistent) depends in part on the functional dependencies and foreign key constraints that exist in the database. There's been some fairly complex mathematical work (and PhD theses!) to characterize the cases in which updates are safe. The updates that Creator allowed may fall into the safe set.
Bottom line is that this is an issue is not "solvable". In any case, we should take our cue from the underlying RowSet functionality; if they don't allow it, neither should we.
