Dear all,

As there weren't any posts correcting my 2nd description
of how the RSE module works, I assume the description is
near enough to being correct.

Please find a project plan for getting CONNECT BY syntax
implemented in Firebird.

I would look forward to all input on improving it.

Design background
=================

Based on feedback received from Jim and Nicolay, and
analyzing the source, I've come to the conclusion that
they are right: implementing the "connect by" functionality
as a funny sort order is not going to work, period.

They only way to implement it properly is through a
recursive union view, like the SQL99 "WITH" clause. In
this system the query:

SELECT * FROM <basestream>
START WITH <parent-condition>
CONNECT BY <prior-condition>;

is basically rewritten as:

WITH {
SELECT * FROM <basestream> WHERE <parent-condition>
UNION
SELECT * FROM <basestream> WHERE <prior-condition>
}

BLR changes
===========

Change definition of stream-clause from (see OSRI
manual page 315):

stream-clause ::= blr_relation name context-var |
blr_rid id context-var |
aggregate-expression |
union-expression;

to

stream-clause ::= blr_relation name context-var |
blr_rid id context-var |
aggregate-expression |
union-expression |
with-expression;

with-expression ::= blr_with
rse-1 [mapping-expression]
rse-2 [mapping-expression]
;

The first rse is the master select from the with block,
the second is the child select from that block. PRIOR
fields in the condition refer to fields in rse-1

RSE pseudo code
===============

Add another rse_type, rse_with. The rse_with node has
two subrse's. The first is rse-1 from the BLR, the
second is rse-2. The rse_next field is NULL.

Recursion is implemented by making a binary copy of
the requst struct, including the impure area. This is
similar to how recursive PSQL procedure calls are
implemented.

RSEWith::open()
{
irsb->mode = master;
irsb->level = 0;
irsb->subrequest = NULL;
irsb->root = request;
sub_rse[0]->open();
}

bool RSEWith::next()
{
if( irsb->mode==master ) {
if( sub_rse[0]->next() ) {
copy data from sub_rse[0] to own data area

irsb->subrequest = clone_request(self);
irsb->subrequest->irsb->mode = recurse;

irsb->mode = child;
return TRUE;
}
else {
// signal EOF
return FALSE;
}
}

if( irsb->mode==recurse ) {
sub_rse[1]->close();
sub_rse[1]->open();
irsb->root->irsb->level++;
if( sub_rse[1]->next() ) {
copy data from sub_rse[1] to own data area
irsb->mode = child;
return TRUE;
}
else {
sub_rse[1]->close();
irsb->root->irsb->level--;
return FALSE;
}
}

if( irsb->mode==child ) {
if( subrequest->next() ) {
copy data from subrequest to own data area;
return TRUE;
}
else {
sub_rse[1]->close();
irsb->root->irsb->level--;
return FALSE;
}
}
}

RSEWith::close()
{
sub_rse[0]->close();
sub_rse[1]->close();
}