Dataphor SQL RAC (Relational Application Companion)


A site of hope for those looking for a true relational database system

Thursday, August 10, 2006

First Sql now Dataphor

Yeah, come on all of you, big bright men,
Data management needs your help again.
He's got himself in a terrible jam
Way down yonder in sql land
So put down your server and pick up some rum,
We're gonna have a whole lotta fun.

To paraphrase William F. Buckley Jr.'s quote from Austrian Willi Schlamm:
The trouble with sql is sql.
The trouble with database management is sql.

I'm an advocate of something wonder-filled called 'Dataphor' (www.alphora.com)
If your into database management, data modeling and sql programming
I have big news for you. So if your working with MS Sql Server, Oracle,
DB2 etc. etc. here's a news flash:
You've have been waiting for Dataphor forever but you just don't know it!

Take a gander at these 'select' statements:

Dataphor supports a table 'type'. So you can represent a
table in a 'select' statement using keywords that define
a table, ie. row, key and of course table. 

select
table
{ row {1 ID,10 ID2,'AA' stringvalue},
row {1,20,'BB'},
row {1,30,'DD'},
row {1,40,'DD'},
row {1,50,'EE'},
row {2,60,'DD'},
row {2,70,'AA'},
row {2,80,'EE'},
row {2,90,'BB'},
row {2,100,'BB'},
row {2,110,'FF'},
key {ID2}
}

ID ID2 stringvalue
-- --- -----------
1  10  AA
1  20  BB
1  30  DD
1  40  DD
1  50  EE
2  60  DD
2  70  AA
2  80  EE
2  90  BB
2 100  BB
2 110  FF

A rose is a rose is a rose. Tables in Dataphor are
'always' a table, ie. a row is always unique and
the table has a key. If a key isn't specified all
the columns of the table will make up the key.
Therefore there is no DISTINCT keyword in Dataphor.
This is a 'BIG,BIG' deal as you will see.

select
(
table
{ row {1 ID,10 ID2,'AA' stringvalue},
row {1,20,'BB'},
row {1,30,'DD'},
row {1,40,'DD'},
row {1,50,'EE'},
row {2,60,'DD'},
row {2,70,'AA'},
row {2,80,'EE'},
row {2,90,'BB'},
row {2,100,'BB'},
row {2,110,'FF'},
key {ID2}
}
) over {ID,stringvalue}

The above query returns only ID and stringvalue columns using over.
Do you see that duplicate rows are eliiminated.
You sent in a real table, you got a real table returned.

ID stringvalue
-- -----------
1 AA
1 BB
1 DD
1 EE
2 AA
2 BB
2 DD
2 EE
2 FF

Now lets return ID by removing the other columns.
Why doesn't sql have a 'remove', especially when you  
want to get rid of one column and you got a bunch.

select
(
table
{ row {1 ID,10 ID2,'AA' stringvalue},
row {1,20,'BB'},
row {1,30,'DD'},
row {1,40,'DD'},
row {1,50,'EE'},
row {2,60,'DD'},
row {2,70,'AA'},
row {2,80,'EE'},
row {2,90,'BB'},
row {2,100,'BB'},
row {2,110,'FF'},
key {ID2}
}
)
remove{ID2,stringvalue}

ID
--
1
2

Get it! :) 
Ok here's a little more nudging. Think of the verb to 'dup', as in
'de-duping' a table using GROUP BY, JOINS, DISTINCT. All that craziness
of getting rid of duplicates is history. Good riddance. And note that
'dup' can also be an adjective:)

Do these predicates look familiar:

=, <>, <, <=, >, >=,

well you can now apply them to tables! Is this a step forward?
What do you think!:)

Next some kewl examples of how to do stuff that usually drives you nuts.

2 comments:

Anonymous said...

Test comment

Steve said...

It passed :-)

About Me

My photo
Phoenix, Arizona, United States