Path: utzoo!utgpu!news-server.csri.toronto.edu!rpi!zaphod.mps.ohio-state.edu!sol.ctr.columbia.edu!lll-winken!decwrl!deccrl!news.crl.dec.com!nntpd.lkg.dec.com!refine.enet.dec.com!saunders
From: saunders@refine.enet.dec.com (Kyle Saunders)
Newsgroups: comp.databases
Subject: Re: how to implement cursor based UPDATE/DELETE
Message-ID: <22686@shlump.lkg.dec.com>
Date: 15 May 91 14:49:01 GMT
Sender: newsdaemon@shlump.lkg.dec.com
Organization: Digital Equipment Corporation
Lines: 79


In article <22653@shlump.lkg.dec.com> jfr@locus.com (Jon Rosen) writes:
|In article <22653@shlump.lkg.dec.com> saunders@refine.enet.dec.com (Kyle Saund`
s) writes:
|>
|>In article <24334@oolong.la.locus.com>, jfr@locus.com (Jon Rosen) writes...
|>|In article <12260@mentor.cc.purdue.edu> tmp@mentor.cc.purdue.edu (Tom Putnam`
writes:
|>|>In article <22626@shlump.lkg.dec.com> saunders@refine.enet.dec.com
|>|>(Kyle Saunders) writes:
|>|>>   I was wondering how you would implement cursor based UPDATE's and
|>|>>DELETEs. I have given some thought to this and cannot see how the RDBMS can
|>|>>properly update the correct row given the fact that the table to be updated
|>|>>may not have _any_ indexes, and not all fields are guarenteed to be includ`
|>|>>in the query.  This means there is no way to uniquely determine which row `
|>|>>the original base table is being updated.
|>|><lotsa stuff deleted...>
|>
|>      Keeping an explicit pointer for each row generated by an OPEN cursor
|>to the original base table is a _major_ kludge IMHO.  That's special casing
|>this action.  What's worse is letting users access this ROWID.  If ROWID is
|>provided in any SELECT, why?!?  Users will abuse this bit of information.  The
|>user should not see the underlying physical implementation of the Relational
|>Model, if at all possible.
|
|  Why do you feel this is a kludge?  You have to do SOMETHING!  In another
|  post you mention creating some kind of index on the fly when a DECLARE
|  CURSOR is used with a FOR UPDATE clause .... Well, what else is an
|  index but a key plus a rowid (albeit a strictly internal one)  which lets
|  you get back to the original row?  If you don't have the primary key (i.e.,
|  whether that is a subset of the columns or the complete set of columns
|  is irrelevant as long as you have a primary key and no duplicate rows)
|  available in your query, you either have to grab a unique row reference
|  and include it with each row (call it what you will, a row id, a pointer,
|  a link, I don't care it still gets you back to the row) or you have to
|  supplement the query to include the entire primary key.  If the primary
|  key has lots of columns (sometimes the whole row) this can certainly
|  degrade performance more than keeping a row id.  And I don't think it
|  is any less a kludge than the row id.

	I don't see a problem with creating an index on the fly.  This should
fit nicely within the overall design of the RDBMS.  I had a problem with
adding a "hidden" column to store the base-table row id to the newly create
temporary table.  This wouldn't be done in any other case.  That's the kludgy
part I was referring to.

	[ stuff deleted that doesn't matter anymore :-) ]
|
|>|As for the duplicate row issue, don't you know that relational data
|>|bases aren't supposed to have duplicate rows??? :-)
|>
|>      I never mentioned duplicate rows.
|>
|
|  Oh... I thought you did... I looked back and you are right, you didnot.
|  If that is the case, you have an implicit primary key in all tables
|  and you are home free either with a rowid or by including all values
|  of the primary key for each table in the query.  Any other approach
|  is NOT supported by the relational model.  That is actually the main
|  point.  The relational model expressly supports updating ONLY through
|  the use of the primary key which means you have to have one and it
|  has to be available at the time of the update.  The only problem
|  with that solution is that sometimes the performance using the
|  primary key sucks.  Thus, the use of rowids (kludgy or otherwise).
|
	Ok, if it is necessary to have the primary key in the query that
defines the cursor and it does not violate the Relational Model by requiring
it, then that is the answer to my question.  You are correct in that the
Relational Model only supports unique address of rows through the primary key.
Sigh.  I guess I should have thought of that earlier.  I just wanted to be as
flexible as possible.  That, and the fact that I haven't implemented 
primary/foreign key support yet (working on the precompiler/development tools).

|  Jon Rosen
|  No flames intended :-)
	
	No flames here either, previous post included (I hope :-).

	- Kyle