Re: Dreaming About Redesigning SQL

cdp.pick: Talking about RVAs (sung to the tune of Talking about my generation..joyfully, one hopes...):

andrewst wrote:
> Originally posted by Mike Preece
>
>>Jonathan Leffler <jleffler_at_earthlink.net> wrote in message
>>news:<Jy0qb.549$Z25.266_at_newsread4.news.pas.earthlink.net>...
>
>>>Mike Preece wrote:
>
> They have, kind of. But being market driven they have done it as
> part of making their DBMSs "object oriented". For example, Oracle
> lets you do this:
> CREATE TYPE phone_info
> ( phone_no VARCHAR2(20)
> , phone_type VARCHAR2(1)
> , phone_preference VARCHAR2(1)
> );
>
> CREATE TYPE phone_list AS TABLE OF phone_info;
>
> CREATE TABLE person
> ( person_id INTEGER
> , person_name VARCHAR2(35)
> , phones phone_list
> );
>
> INSERT INTO person VALUES
> ( 123> , 'Andrews'
> , phone_list( phone_info('123-123 1234', 'H', 'E'), phone_info('123-456
> 7890', 'W', 'D') )
> );

If you accept this, you should embrace the mv concept in Pick. We just don't have the nice syntax.

However, the point seems to be RVA's 1) were 'anointed' acceptable by the relational gods sometime in the near past 2) are not universally accepted (or maybe disdained?) and 3) Oracle at least has implemented them in a nice, easy-to-understand syntax. Where's the beef?

In a previous post, it was implied that RVA's are more complex than mv's. It is true that normally mv's contain only data, but the data could be a foreign key or for that matter anything, so the distinction seems moot. The main difference I see is that the structure of the RVA is explicit in the CREATETABLE and therefore integrity is maintained by the dbms. In Pick, only the best RAD's do something similar (and, for what it's worth, this inability in most Pick RAD's to structure the RVA-equivalent has been my constant complaint over the years.)

Normally the individual mv's are just that-individual fields (phone_no, phone_type and phone-preference in the example above)-and the integrity relationship is procedurally maintained. That said, such maintenance is pretty easy and clear in the procedural language, mvBasic.

eg: for an Insert:

PersonRec<PhoneNumber,-1> ='123-123 1234'
PersonRec<PhoneType,-1> = 'H'
PersonRec<PhonePreference,-1> = 'D'   etc.

> I may not have the syntax quite right, I never actually do this, but it
> is right in principle

Do you not do this because you disagree with the principle?

>>1) List Persons Name with PhoneNumber "12345"
>>and
>>2) List Persons "Mike" PhoneNumber
> i.e. syntactically different:
>
> List <table> <attribute1> with <attribute2> <value>
> and
> List <table> <Value> <attribute2>
>
> In SQL:
>
> select person_name from person_phones where phone_number='12345';
> and
> select phone_number from person_phones where person_name=Mike';
  i.e. in both cases:
>
> select <attribute1> from <table> where <attribute2> = <vaue>;
>
But you could just as easily say in Pick:

List PhoneNumbers Name with Name "Mike"

which makes it the same syntax as 1)

Chandru Murthi Received on Thu Nov 06 2003 - 17:32:19 CET