Re: more on delete from join

From: paul c <toledobythesea_at_oohay.ac>
Date: Fri, 28 Aug 2009 17:42:38 GMT
Message-ID: <iGUlm.41206$Db2.30628_at_edtnps83>


Kevin Kirkpatrick wrote:
> On Aug 26, 8:34 pm, paul c <toledobythe..._at_oohay.ac> wrote:

>> paul c wrote:
>>> Kevin Kirkpatrick wrote:
>>> ...
>>>> In short, before delving into, "*how* should the DBMS handle view
>>>> updates?", I'd like to see a discussion about the question, "*should*
>>>> the DBMS handle view updates?".
>>> Wow, that's far out.  To want a dbms never to update views might be
>>> something an ostrich would want but not I.  Better to want a logic that
>>> permits view updates.  Lots of pragmatic reasons, security, archiving,
>>> optimization and data re-arrangement are just a few off the top of my
>>> head.  ...
>> Oh, integrity too, say to keep people out of last year's account after
>> the books close!

>
> First of all, I'm using the Google Groups web-based interface, and it
> appears that this thread has gotten a bit garbled in tracking who is
> replying to whom. So I had no idea until minutes ago that I'd had the
> above messages addressed to me in the first place... anyway, I
> apologize in advance if I'm slow to reply to messages addressed to me,
> or reply to them out of order.
>
> Now, I'm going to start responding to your comments by pointing out
> that I take your first sentence as a compliment. It is incredibly
> rare that ideas are given such distinction on cdt without immediately
> (and rightfully!!) being relegated to crank-dom. Having said that, as
> this is comp.database.*theory*, I'd ask that we not dismiss ideas
> simply because they have immediate pragmatic ramifications to the way
> today's DBMS's operate. After all, there are lots of nifty and
> "useful" things that can be done with table triggers, and database
> designers who rely heavily on table triggers may scoff at the idea of
> building a DBMS which lacks trigger facilities, saying "maybe it's
> something an ostrich would want..." ;-)
>
> I guess my point here is, if updating virtual relvars is not on sound
> theoretic footing (and until somebody can point to a sound theory that
> accommodates the assertion of conclusions in order to derive
> propositions, it remains there in my mind), then perhaps can we take
> the things it is traditionally used to address and work out a more
> theoretically sound approach (and then perhaps compare the approaches
> from a pragmatism perspective).
>
>
> The first thing you mention that updatable views address: security.
> Let's jump right into it with a sample data model (given as base
> relvars and associated predicates):
>
> EMP {ENO, ENAME, SAL} -- "<ENO> is named <ENAME> and has salary
> <SAL>"
> BOSS {ENO, BOSS_ENO} -- "<ENO> has boss <BOSS_ENO>"
> CURRENT_USER -- "The current database connection has been
> authenticated as <ENO>" -- note, this is a view (not updatable, of
> course!)
>
> And business rules:
> 1) Employees can view ENO and ENAME for everybody
> 2) Employees can update their own ENAME.
> 3) Employees can view their own SAL
> 4) Bosses can view and update ENAME and SAL of employees who report to
> them.
>
>
> The challenge - can these business rules be enforced w/out using
> updatable views? I'd argue, "yes", but only with the introduction of
> some new features.
>
> First, for reasons I'll make clear later, let's put this into 6th
> normal form:
>
> EMP_NAME{ENO, ENAME} "<ENO> is named <ENAME>"
> EMP_SAL {ENO, SAL} "<ENO> has salary <SAL>"
> BOSS {ENO, BOSS_ENO} "<ENO> works for <BOSS_ENO>"
> CURRENT_USER "The current database connection has been authenticated
> as <ENO>" -- note, this is a view (not updatable, of course!)
>
>
> Now, some "standard" grants:
>
> GRANT SELECT ON EMP_NAME TO PUBLIC
>
> GRANT SELECT ON BOSS TO PUBLIC
>
> And here, I introduce a new feature: "restriction grants". Basic
> syntax:
> GRANT <OPERATION_LIST> ON <TUPLE_SOURCE> TO <ROLE> [<RESTRICTION>]
>
> Essentially, the restriction is applied to all operand tuples of the
> operation at hand. The restriction will be applied at runtime, and it
> could either return a runtime error or simply filter the operand
> tuples before performing the operation. My *strong* preference is for
> the runtime error.
>
> So, in this case, we could have restriction grants as follows:
> GRANT SELECT ON EMP_SAL TO PUBLIC WHERE ENO MATCHES (CURRENT_USER
> UNION {BOSS_ENO} JOIN (CURRENT_USER, BOSS))
>
> GRANT INSERT, DELETE ON EMP_NAME TO PUBLIC WHERE ENO MATCHES
> (CURRENT_USER UNION {BOSS_ENO} JOIN (CURRENT_USER, BOSS))
>
> GRANT INSERT, DELETE ON EMP_SAL TO PUBLIC WHERE ENO MATCHES
> ({BOSS_ENO} JOIN (CURRENT_USER, BOSS))
>
> Note, I do not specify UPDATE access - from a privilege perspective,
> UPDATE should be treated as a INSERT/DELETE combo (hence, the move to
> 6th NF). This is mostly because I do *not* want or intend to present
> a solution which conflicts with any earlier stances I may have taken
> on the topic of "transition constraints".
>
> Hopefully things aren't so muddled as to obscure what this approach
> accomplishes. Effectively, we've left the data modeling and
> enforcement of business rules in the domain of the data modeler, and
> views and application code in the realm of application designers. A
> quick swipe at "updatable views": ever stopped to wonder why the data
> modeler would or should pick view names, much less decide how updates
> to those views should be interpreted, instead of the application
> designer?
>
> Anyway, I'll stop here - is this going in a direction that is even
> remotely interesting?

It might be interesting for defining an environment that allows the language features mentioned to reference an algebra or calculus (which don't have a notion of update in the first place), but I don't see why normalization needs to be introduced to implement updating of bases nor of views. If it does need to be, then presumably the updating of any relation somehow depends on how 'normal' it is. I don't see what triggers have to do with updating either, even though some implementations require them. By the way, why assume that CURRENT_USER is not updateable?

When it comes to updating, I'd prefer to use as few concepts as possible, named sets of tuples, algebraically expressible constraints and a set of algebra operators. Design matters might determine particular results but a logical engine shouldn't distinguish between designs, that's what the old hierachical and network systems did. Regarding an UPDATE verb, it is probably simpler to do what TTM does and assume an algebra that has extend and rename operators. I suppose a dbms that follows a mere algebra could distinguish 6NF relations, but I think it would be ponderous to use. Received on Fri Aug 28 2009 - 19:42:38 CEST

Original text of this message