Re: Happy column adding and dropping

At 02:22 PM 1/25/00 -0600, Ross J. Reedstrom wrote:

>Gee, Don, that's a good idea, I should have thought of it myself! BTW,
>what I have is labelled "Second Informal Review Draft" and is dated July
>30, 1992. I don't know how it differs from the final standard, if at all.

>From the date, it should be quite close. From my experience, by this
point in the process changes are mostly editorial, not substantial
in the sense of adding or dropping columns...I mean features. :)

>I orginally got it from someone on this list, I forget who. Anyone
>want a copy, I'll happily email it to you, or throw it on a website:
>it's 1.6M of ASCII, ready for the line printer ;-) (273K gzipped.)

I'd love a copy and have a DSL line, so the size doesn't bother me.

As far as putting it on a web site, Ansi drafts are generally available
for public comment so it might be OK to do so. Is there a copyright
statement attached?

>That said, ALTER is in fact described, and the ADD COLUMN case agrees
>with Tom's expectations, rather than mine, see General Rule 2, below:

So it goes :)

>For what it's worth, here's what it says about DROP COLUMN. Note that
>the question of what to do about references to columns: standard says,
>throw and error, unless the DBA really means it, with the CASCADE option,
>except for VIEWs, which get dropped, unless the DBA is careful to say
>RESTRICT.

> Note: A <drop column definition> that does not specify CASCADE
> will fail if there are any references to that column resulting
> from the use of CORRESPONDING, NATURAL, SELECT * (except where
> contained in an exists predicate>), or REFERENCES without a
> <reference column list> in its <referenced table and columns>.

This is interesting, because it implies that select * can or should
be expanded when a view is created, rather than when a view is queried.
Except for "exists" where the "*" target list can be thought of as being
a dummy as sorts.

Also, the "natural" refers to a "natural join". A "natural join", you'll
recall, is one where you supply two table names and the tables are joined
by those columns they share in common. "drop column" is supposed to
catch such cases.

Ditto the form "references tablename" where the foreign key is determined
by inspecting "tablename".

Cool! They don't expect much, do they? :)

All the above with the caveat "if I'm reading this correctly".

> Note: If CASCADE is specified, then any such dependent object
> will be dropped by the execution of the <revoke statement> spec-
> ified in the General Rules of this Subclause.

...

> 1) Let A be the current <authorization identifier>. The following
> <revoke statement> is effectively executed with a current <au-
> thorization identifier> of "_SYSTEM" and without further Access
> Rule checking:
>
> REVOKE INSERT(CN), UPDATE(CN), REFERENCES(CN) ON TABLE TN
> FROM A CASCADE

I don't know exactly what this sez offhand...

>
> 2) Let VN be the name of any view that contains a reference to
> column C of table T. The following <drop view statement> is
> effectively executed with a current <authorization identifier>
> of "_SYSTEM" and without further Access Rule checking:
>
> DROP VIEW VN CASCADE

This is clear enough.

...

> 6) The degree of T is reduced by 1. The ordinal position of all
> columns having an ordinal position greater than the ordinal
> position of C is reduced by 1.

Does the standard give a way to access the ordinal position of
columns?

My guess is that any preliminary implementation of drop column won't
be implementing everything spelled out in the standard!

- Don Baccus, Portland OR <dhogaza(at)pacifier(dot)com>
Nature photos, on-line guides, Pacific Northwest
Rare Bird Alert Service and other goodies at
http://donb.photo.net.