Re: O'Reilly interview with Date

Marshall Spight wrote:

> Kenneth Downs wrote:

>> Marshall  Spight wrote:
>>
>> >>
>> >> SELECT child.* from parent join child key KEYNAME
>> >
>> > Okay, but this doesn't address the namespace unification issues
>> > that I raised previously.
>>
>> Why not?

I think we were. Andromeda is layered on top of SQL. I cannot actually write a SQL query as written above, and in fact I don't want to. I just tell it the columns I want and it figures out how to do the join.

However, the implementation could be moved down into the server (using an Open Source server like Postgres) so that the server held an Andromeda-style data dictionary and it could then implement queries as described.

So much for the layering/implementation.

>
> Recall that I was originally responding to David Cressey's
> critique of natural join. I said "you necessarily
> have to unify the column namespaces when you join,
> unless you don't want to enforce any column naming discipline
> at all (that is, allow columns with no names, or illegal names,
> or two columns with the same name) which I don't think is tenable."
>
> Note that SQL falls squarely into the "[doesn't] enforce any
> columning naming discipline at all" case. See any of a dozen
> essays by Date et. al. on why this is bad. :-)

Theoretically I think it is better that two columns of the same name must always have the same domain. It seems that those who would allow the opposite have the burden of proving the benefit. Alas, this would break all of that sofware out there written by people who pay good money for db servers to Oracle, MS, IBM, and friends, so it is just not commercially viable at this time to introduce this restriction.

Moreover, when "versions" of a column appear in other tables, such as "ssn_self" and "ssn_spouse", the column definition must be tied back to the original "ssn" domain, but that goes into other areas.

>
>
> Let me give a concrete example, and you can tell me how you
> would handle it in Andromeda.
>
> I have a table for software defect reports, called Bugs, and
> a table of Users.
>
> create table Bugs(
> BugId int primary key,
> Reporter int references Users(UserId),
> Owner int references Users(UserId),
> Title varchar(255)
> );
>
> create table Users(
> UserId int primary key,
> Login varchar(32)
> );

Personally i would never do that UserID thing, but I will leave it in to keep it closer to your example.

column UserID { description: User ID; type_id: int; } column Login { description: User Login; type_id: vchar; colprec: 23;} table users {
  description: System Users;   

  column UserID { primary_key: Y; }
  column login { uisearch: Y; }

  unique anynamehere { columns { login; }} }

column BugID { description: Bug ID; type_id: int;

   automation_id: SEQUENCE; }
table bugs {
  description: Bugs;  

  foreign_key users { suffix: _rep; }
  foreign_key users { suffix: _own; }
}

which would be eqivalent to this DDL for bugs (I left out title):

create table bugs(

   BugID int IDENTITY --SQL Server syntax    userid_rep int references users(userid),    userid_own int references users(userid) );

As a convention I don't allow the complete renaming of the columns when put into foreign keys. You can give them prefixes and suffixes, but you cannot rename them into things like "owner" and "reporter". Also of course my own preference is for terse abbreviations like "rep" and "own".

BTW, why do you have two unique columns, do you allow people to change their logins? Otherwise I would think they are redundant.

But anyway, moving forward, a query is just a list of desired columns (we apply to user's filters like defect=123 at runtime) from tables, so the query would be:

query Myquery {
  table bugs {
    column { bugid; }
  }
  foreign_key users {
    suffix: _rep;
    column { login; }
  }
  foreign_key users {
    suffix: _own;
    column { login; }
  }
}

-- 
Kenneth Downs
Secure Data Software, Inc.
(Ken)nneth_at_(Sec)ure(Dat)a(.com)