Formulating Expressions a Step at a Time: Lazy Evaluation

I am reading this book, and I found the section with the title “Formulating Expressions a Step at a Time” particularly interesting:

First, the book shows you this the query for the sentence "Get pairs of supplier numbers such that the suppliers concerned are collocated (i.e., are in the same city)" written in Tutorial D:

( ( ( S RENAME ( SNO AS SA ) ) { SA , CITY } JOIN
   ( S RENAME ( SNO AS SB ) ) { SB , CITY } )
       WHERE SA < SB ) { SA , SB }

And then it proceeds to show you how to write this query in a more readable (step by step) way:

WITH ( S RENAME ( SNO AS SA ) ) { SA , CITY } AS R1 ,
    ( S RENAME ( SNO AS SB ) ) { SB , CITY } AS R2 ,
    R1 JOIN R2 AS R3 ,
    R3 WHERE SA < SB AS R4 :
    R4 { SA, SB }

Finally, it shows you how to write this query in SQL:

WITH T1 AS ( SELECT SNO AS SA , CITY
            FROM   S ) ,
    T2 AS ( SELECT SNO AS SB , CITY
            FROM   S ) ,
    T3 AS ( SELECT *
            FROM   T1 NATURAL JOIN T2 ) ,
    T4 AS ( SELECT *
            FROM   T3
            WHERE  SA < SB )
    SELECT SA , SB
    FROM   T4

Thanks to the “with” keyword, both in SQL and in Tutorial D, it is possible to deal with this query in a “step by step” way, instead of having to deal with it a single hard to write and hard to read expression (note that this is not a recursive query, so the with keyword is not being used for that in this examples)

Sadly, so far I have been unable to find a equivalent for this syntax in Dataphor… While it is possible to write something like (I am not 100% confident the syntax is right, but I think it should give you the general idea):

var R1 := S {SNO SA}
var R2 := S {SNO SB}
var R3 := R2 JOIN R3
var R4 := R3 WHERE SA < SB
select R4 {SA, SB}

In Dataphor, the variables (R1, R2, etc) are not lazily evaluated, and therefore the performance is not as good, as in, for example, the SQL case (I ran a similar example in SqlServer, and the expressions were evaluated lazily, at the end, instead of one by one, resulting in far better performance). Maybe I am doing something wrong?

I wonder how hard would it be to make Dataphor generate SQL using the WITH keyword with those databases that support it (so far the latest versions of SQLServer (2008) and Oracle (10 & 11) seem to support this syntax)… I guess it is time to ask the Dataphor authors…

Pagination in Dataphor

First I created this operator:

create operator GetNext(var AID :Integer) : System.Integer 
begin 
AID:= AID+1; 
result:=AID; 
end ;

and then I used it:

var id := 0; 
select SomeTable add {GetNext(id) rowid}; 

It is nice, but it would be better if there were a way to do this without having to create an operator myself (it should already include one that does this)

Now If I want to do pagination, all I have to do is write:

var id := 0; 
select SomeTable add {GetNext(id) rowid} where rowid between 2 and 5;

That will return rows between 2 and 5 from the “SomeTable” table.

Now one of the most interesting feaures about Dataphor, are relvars (relational variables), that make it possible to do this:

var SomeComplexQuery := SomeTable where …...
var id := 0;
select SomeComplexQuery add {GetNext(id) rowid} where rowid between 2 and 5;

That way, pagination can be added to a query of arbitrary complexity with minimum effort.

Dataphor: How to create a table with a primary key

Creating a table in Dataphor is easy:

create table Debt  
{  
    ID : Integer,

    key { ID },
};

That translates (when using MSSQL as storage) into (if we ask MSSQL to provide us with the SQL DDL):

CREATE TABLE [dbo].[Debt](   
    [ID] [int] NOT NULL 

)
 
CREATE UNIQUE CLUSTERED INDEX [UIDX_Debt_ID] ON [dbo].[Debt] 
( 
    [ID] ASC 

)

It is important to note that D4 does not have the concept of a primary key, and that the “key” keyword results in a unique index, that while for all uses and purposes can work as a primary key, it is not recognized as such by MSSQL. It is possible to have several different “keys” in a Dataphor table.

Alphora Dataphor is OpenSource!

Now that AlphoraDataphor is opensource, I believe a relational reborn could come in the near future... I have been reading the manuals and, while I am an object weenie let me tell you all that I think Dataphor is a very exciting technology I hope it finds the way to make True relational database a buzz word like Ruby and that finally forces big companies like Oracle to make databases truly relational.