Re: Happy column adding and dropping

On Tue, Jan 25, 2000 at 12:42:54PM -0800, Don Baccus wrote:
> At 02:22 PM 1/25/00 -0600, Ross J. Reedstrom wrote:
> 
> 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?

Hmm, the string 'copyright' (cas insensitive search) does not appear in 
the text at all. This draft was done at DEC, according to the title page.

So, grab it from 
      http://wallace.ece.rice.edu/sql1992.txt 
or      http://wallace.ece.rice.edu/sql1992.txt.gz 

(hmm, Netscape just decompressed that, and showed me the text!)

Perhaps this could go on postgresql.org, in the developers info section,
if, as I believe, we may act in the good-faith belief that the drafts of
the ANSI standards are actually in the public domain. Hmm, I just realized,
this is actually a draft of the ISO standard. s/ANSI/ISO/

> 
> >       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...

I think it says: make these items disappear, for the current user, by
revoking all privileges for this column (CN :== column name) on this table.

Err, are privileges supposed to be granular to the _column_? The syntax for
GRANT and REVOKE don't imply it, but some of the Rules in each do.

> 
> 
> >    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?

You mean go from <column name> to <ordinal>? I don't think so, but I
haven't grovelled through the standard enough... O.K., now I have. It's
in the defined system tables, in particular the COLUMNS table, which I
won't duplicate here, since it runs to more than a printed page! It's
in clause 21.3.10, page 580, and defines a table that includes fields

[...]             TABLE_NAME         INFORMATION_SCHEMA.SQL_IDENTIFIER,             COLUMN_NAME
INFORMATION_SCHEMA.SQL_IDENTIFIER,            ORDINAL_POSITION   INFORMATION_SCHEMA.CARDINAL_NUMBER
CONSTRAINTCOLUMN_POSITION_NOT_NULL NOT NULL,
 
[...]

So, yes, there is a standard way to get from <table name> + <ordinal> to
<column name>, if that's what you meant (since getting at the _contents_
is trivial: SELECT <ordinal> from <table name> Gack, look what you've
done, I'm starting to type in <standard complient markup> ;-)

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

I wouldn't think so! But it might help to plan for future implementation,
or at least be sure not to implement something that makes it harder to
implement the rest in the future.

Ross
-- 
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu> 
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St.,  Houston, TX 77005