Re: Updatable views

"Rick Elbers" <rick.elbers_at_chello.nl> wrote in message news:qbiep11f3eg8h1mmkqhqskc77ai1r7boq7_at_4ax.com...
> Probably in vain but I will give you answers.
>
> On Wed, 7 Dec 2005 11:08:57 +0200, "x" <x_at_not-exists.org> wrote:
>
> >
> >"Rick Elbers" <rick.elbers_at_chello.nl> wrote in message
> >news:3189p1dlv9qpofqai5nq8v32keao7amiph_at_4ax.com...
> >> Dear database experts,
> >
> >> Can somebody enumerate the problems with an updatable view for me ?
> >> With an updatebleview:= joined set of related tables and relations
> >> from which rows can be updated, inserted and deleted like you
> >> can in a simple table or master-detail relation.
> >
> >What is an updatable view in your view ?

> Defined that above. See above

If it was defined, I wouldn't have asked.

> >What do you mean by joined set of related tables and relations ?
>
> A list of tables which are joined( flattened if you want) on their
> related identifiers( keys if you want). In fact your regular join
>
> >Maybe you mean just a set of tables ?

> Yes, I think so

If you are not sure, who is then ?
It is your definition. You should be sure about it before you do anything else.

> >What do you mean by related ?

> With a foreign key-primary key relation
Ok.

> >There is any constraint definition ?

> Could be. Couldnt be. Should both be possible
There are other kind of constraints besides the referential FK-PK ones ?

> >Who is enforcing the constraints ? The DBMS or the application ?
> the DBMS.

Ok. Then the DBMS is the only one who knows if the view is updatable or not.

> >Who is doing the translation of the update requests ? The application or
the
> >DBMS ?

> Thats the point. As much as possible the class UpdatableView.
How could this class know if the update is possible if the constraints are complex and are handled by the DBMS ?

> >How you deal with the errors ?
>
> That too is responsibility of UpdatableView

Ok.

> >Are we talking about SQL DBMS or something else ?
>
> Yes we are talking about sql server to be precise( although I doubt it
> matters)
>
> >etc. etc. etc.
>
> Etc etc trivial answers
>
> >
> >> My first try is to find hierarchy in de database by finding the
> >> absolute parents assigning them level 0. Parents of my table are
> >> considered those relations in which my foreign key doesnt allow
> >> DBNull.
> >
> >What if do not find any parent this way ?

> Then you are safe to insert/update anything in that table
Really ?

> >And what is the purpose of building the hierarchy ?
>
> See above. Do you really understand the problem or not ?

I don't understand it because even you don't understand it.

> >> Then from there on proceed to give every table that has no parents but
> >> level 0 parents a level 1 etc.
> >>
> >> Problems seem to be autojoins and multiple relations between tables.
> >> Those problem can be solved to use every table only once in the scan.
> >>
> >> So the first question is: will this way of hierarchising the
> >> relational db always valid ?
> >
> >> Then secondly given the hierarchy what problems remain when we talk
> >> about insert/update/delete and all kinds of combinations of those ?
> >
> >Gee! Let me think. The problems are ... what is the problem again ?
> >
>
> Funny guy. The problem is the database. How to get rid of its messing
> with good OO code.

I think you meant "the problem is the DBMS" instead of "the problem is the database" :-)
Well, the problem could be in many places...

Define what is a "view" for your application. Define what is an atomic update for your "view". Define the types of constraints you want to handle. Then see if you can find a plan for performing the updates. Received on Mon Dec 12 2005 - 10:24:25 CET