Tuple visibility through a view
I realize now that my questions about updating through a view were seriously off-piste, because what I was actually interested in has nothing to do with that. What I'm interested in is this: given a base relvar, a tuple that is a candidate for insertion, and a representation of a view on that relvar (not the view itself, but its definition), what is the most suitable way to alter the tuple so that when it is inserted into the base relvar it will be guaranteed to be visible through the view? Similarly, what is the most suitable way to update an existing tuple of the base relvar so that it is guaranteed not to be visible through the view? Parsimony counts: obviously the latter case can be reduced to just deleting the tuple, but that's not what I want.
I am willing to confine my concerns to views that are defined solely by projection over selection, and selections to those defined by Boolean expressions involving only attributes and values, without reference to any other relvars.
What does "alter the tuple" mean?
To me, it sounds rather like, "given the expression 3 + 4, what is the best way to alter the 3 to make it visible in the result."
Eh?
By altering the tuple, I mean computing a new tuple which satisfies the requirements to be visible in the view but is otherwise as similar as possible to the original tuple. Crudely, if a tuple {A: 1, B: "foo"} is to be inserted, but the view specifies a selection clause B = "bar", then the simplest altered tuple would be {A: 1, B: "bar"}.
Quote from johnwcowan on October 9, 2019, 3:42 pmBy altering the tuple, I mean computing a new tuple which satisfies the requirements to be visible in the view but is otherwise as similar as possible to the original tuple. Crudely, if a tuple {A: 1, B: "foo"} is to be inserted, but the view specifies a selection clause B = "bar", then the simplest altered tuple would be {A: 1, B: "bar"}.
None of the academic work on update-through-view wants to support that. I'm wondering what is your use case?
Crudely, the view definition acts as a constraint: only tuples that satisfy the constraint are visible through the view. Ergo only tuples that satisfy the constraint can be inserted through the view or deleted through the view. If by 'alter' a tuple you mean delete some tuple value and insert a different tuple value, see above.
Tuples don't have identity; we can't talk of the 'same' tuple with some attribute values altered. There's a deep pathology in the way Entity-Relationship modelling thinks that a tuple represents an entity, and that a Primary Key is some sort of persistent identifier pointer to the entity.
Perhaps if you give an example, we can unwind the thinking that's got you into this blind alley.
Very concretely, then, and talking wlg only about the visible-insert case:
I have a relvar holding an EMPLOYEEs relation. There is a field SENIORITY, and I have a view SENIOR_EMPLOYEES defined as EMPLOYEES WHERE SENIORITY = "senior". I have constructed a tuple T representing a new employee, and I wish to insert T through the SENIOR_EMPLOYEES view.
If the attribute SENIORITY in T = "senior", all is well. If not, the database engine has three choices:
- Refuse to insert T through SENIOR_EMPLOYEES and raise an exception.
- Insert T anyway, and accept that this employee is not senior and will not appear in the view it was inserted through.
- Insert T', where T' is equal to T except in the SENIORITY field. This requires access to the definition of SENIOR_EMPLOYEES, but I posit that.
It's case 3 that interests me. When the selection predicate is this simple, it's obvious what to do. But I want to be able to do the same thing for at least somewhat more complex views, which requires a procedure for computing T' from T. What is the Right Thing?
As I said above, I'm willing to restrict my attention to projections and selections that are arbitrarily complicated Boolean expressions but don't refer to any value outside the tuple being selected or not selected except a constant: A < B is fine, so is A = 3, but not A < AVG(A).
Quote from johnwcowan on October 9, 2019, 8:55 pmVery concretely, then, and talking wlg only about the visible-insert case:
I have a relvar holding an EMPLOYEEs relation. There is a field SENIORITY, and I have a view SENIOR_EMPLOYEES defined as EMPLOYEES WHERE SENIORITY = "senior". I have constructed a tuple T representing a new employee, and I wish to insert T through the SENIOR_EMPLOYEES view.
If the attribute SENIORITY in T = "senior", all is well. If not, the database engine has three choices:
- Refuse to insert T through SENIOR_EMPLOYEES and raise an exception.
- Insert T anyway, and accept that this employee is not senior and will not appear in the view it was inserted through.
- Insert T', where T' is equal to T except in the SENIORITY field. This requires access to the definition of SENIOR_EMPLOYEES, but I posit that.
It's case 3 that interests me. ...
I'm relatively sorry to have to say, but this post betrays that you don't understand the CWA and/or predicates aka set membership conditions (and the role they play in the how and why the RM actually works).
The only reasonable option is 1.
That's because asserting that someone is a [member of the] SENIOR_EMPLOYEES (-and making that assertion is exactly what you semantically mean by inserting into this view-) despite the fact that it has been formally stated that being a member of the SENIOR_EMPLOYEES ***requires*** seniority == "senior" and despite the fact that you are asserting that for this particular employee, that is not the case.
In logic that is plain and simply called a contradiction, and a DBMS [supposed to] being a dependable system of logical inference, it ***CANNOT AFFORD TO ACCEPT ANY CONTRADICTIONS***.
Quote from johnwcowan on October 9, 2019, 8:55 pmVery concretely, then, and talking wlg only about the visible-insert case:
I have a relvar holding an EMPLOYEEs relation. There is a field SENIORITY, and I have a view SENIOR_EMPLOYEES defined as EMPLOYEES WHERE SENIORITY = "senior". I have constructed a tuple T representing a new employee, and I wish to insert T through the SENIOR_EMPLOYEES view.
Stop! Do not go there. That way lies madness.
Quote from johnwcowan on October 9, 2019, 8:55 pmVery concretely, then, and talking wlg only about the visible-insert case:
I have a relvar holding an EMPLOYEEs relation. There is a field SENIORITY, and I have a view SENIOR_EMPLOYEES defined as EMPLOYEES WHERE SENIORITY = "senior". I have constructed a tuple T representing a new employee, and I wish to insert T through the SENIOR_EMPLOYEES view.
If the attribute SENIORITY in T = "senior", all is well. If not, the database engine has three choices:
- Refuse to insert T through SENIOR_EMPLOYEES and raise an exception.
That is the most sane option in a world you've already made mad.
Better would be to disallow any form of "insert through" and only permit updates to base relvars.
Quote from Erwin on October 9, 2019, 9:29 pm
The only reasonable option is 1.
I grant that databases should not be allowed to represent contradictions. But that does not mean that when faced with a contradiction, there is nothing the total system can do to resolve it. Imagine that you are doing the insert interactively. A user-hostile system would just say "Can't do that, contradiction". A more friendly system, however, might present a dialogue box like this:
You have attempted to insert a tuple through the view SENIOR_EMPLOYEES that violates the definition of the view. What would you like to do?
- Do nothing.
- Insert the tuple anyway, as if you had inserted it into the base relvar EMPLOYEES.
- Change the SENIORITY attribute of the tuple to "senior".
Is there anything inherently wrong with that? And if not, is there anything inherently wrong with being allowed to make the decision between 1, 2, and 3 in advance in all such cases, thus avoiding the tuple-by-tuple interaction?
Quote from Dave Voorhis on October 9, 2019, 9:48 pm
Quote from johnwcowan on October 9, 2019, 8:55 pm
- Refuse to insert T through SENIOR_EMPLOYEES and raise an exception.
That is the most sane option in a world you've already made mad.
Better would be to disallow any form of "insert through" and only permit updates to base relvars.
If you do not like crottled greeps, by all means do not eat them. But I do: see above.
Quote from johnwcowan on October 9, 2019, 10:01 pmQuote from Erwin on October 9, 2019, 9:29 pmThe only reasonable option is 1.
I grant that databases should not be allowed to represent contradictions. But that does not mean that when faced with a contradiction, there is nothing the total system can do to resolve it. Imagine that you are doing the insert interactively. A user-hostile system would just say "Can't do that, contradiction". A more friendly system, however, might present a dialogue box like this:
You have attempted to insert a tuple through the view SENIOR_EMPLOYEES that violates the definition of the view. What would you like to do?
- Do nothing.
- Insert the tuple anyway, as if you had inserted it into the base relvar EMPLOYEES.
- Change the SENIORITY attribute of the tuple to "senior".
The DBMS autonomously taking the decision to do 3. represents a contradiction too. It boils down to the DBMS deciding that the user meant "senior" when he actually said "junior" (and the two are mutually exclusive which makes it a contradiction). That's "I'll accept it, but I'll assume it was a lie and I forgive you in my own crazy way".
(And the DBMS autonomously taking the decision to do 2. is an obvious blatant mistake because it means ignoring that part of the set membership condition (/predicate) for the view which ***makes*** the assertion as made by the user a contradiction.)
Dialog boxes are not the responsibility of the DBMS, they are the responsibility of the application, and they ***could*** be triggered by the DBMS refusing, but that still leaves "the DBMS refusing" the only sensible option to even facilitate any reasonable repair tactic.
Did I say "autonomously"? I did not. I suggested that it was not unreasonable for the application to tell the DBMS in advance what general approach to take in cases like this. Or if you're allergic to that for some reason, what about the application? Is the user allowed to tell the application what recovery strategy is to be employed? Or must we send a letter to the employee saying "Your promotion has been canceled due to a contradiction in the database"?
Now that all the preliminary cruft has been cleared away, let me try again. Suppose the Powers That Be have decided that if the actions taken by the code (to wit, inserting into a specific relvar) contradict what the data says, then the fault is in the data and it must be repaired. What now is the most appropriate general strategy to minimally repair the data so that it meets the constraints? This question is actually just as appropriate for base tables as for views, so Dave's objection is no longer to the point.
Data, after all, despite its etymology, is not given to us from Heaven on tablets of gold, or even stone. It is collected by fallible human beings who make mistakes of all kinds. If you want to say this isn't database theory, fine. It's data theory, then. Or something.