[DISCUSSION] Valentina SQL -- recursive search

[Ruslan Zasukhin]

Hi Paul, Bart,
Hi All,

We have implement three new methods on low level of C++
    
    http://www.valentina-db.com/bt/view.php?id=3958.
    http://www.valentina-db.com/bt/view.php?id=3989.
    http://www.valentina-db.com/bt/view.php?id=3960

Now lets try all together invent nice SQL form around this.


=======================================================================
AS PREDICATE:

SELECT * FROM PERSON WHERE recid IN DESCENDANTS OF 5

    WHERE recid IN DESCENDANTS OF 5 TO LEVEL 3
    WHERE recid IN DESCENDANTS OF 5 ON LEVEL 3
    WHERE recid IN DESCENDANTS OF (recID = 5) TO LEVEL 3
    WHERE recid IN DESCENDANTS OF (id = 'fgdsfg') TO LEVEL 3
    WHERE recid IN DESCENDANTS OF (recID = 5) TO LEVEL 3 AND Name = 'Maria


=======================================================================
AS TABLE CONSTRUCTOR:

SELECT * FROM (DESCENDANTS OF PERSON TO LEVEL 3)
SELECT * FROM (DESCENDANTS OF PERSON TO LEVEL 3 WHERE recid = 5)

SELECT * FROM (DESCENDANTS OF PERSON TO LEVEL 3 WHERE recid = 5)
          WHERE Name = 'Maria'


=======================
PROBLEMS ARE:

* we must specify START record from which we go down.
    btw, for now we think only ONE  record, but may be SET ??

* then we need find nice and clear and flexible syntax


========================
IT SEEMS we like better predicate way

    WHERE recid IN DESCENDANTS OF (recID = 5) TO LEVEL 3


But it still sounds somehow not nice ..

* what I do not like is that we must always write
    RECID IN prefix

    RECID IN DESCENDANTS
    ^^^^^^^^^^^^^^^^^^^^^   looks like ATOMIC construction


Also we need specify start record,
                (RecID = 5)
                (PkID = 'social_number')

In general THIS syntax may work if close eyes on above points ...

Then we get this variants:

all descendants:
     WHERE (recid IN DESCENDANTS OF (recID = 5))

All descendants To level 3:
     WHERE (recid IN DESCENDANTS OF (recID = 5) TO LEVEL 3) ...

Descendants ONLY on level 3
     WHERE (recid IN DESCENDANTS OF (recID = 5) ON LEVEL 3) ...





-- 
Best regards,

Ruslan Zasukhin
VP Engineering and New Technology
Paradigma Software, Inc

Valentina - Joining Worlds of Information
http://www.paradigmasoft.com

[I feel the need: the need for speed]