It doesn't take long to get used to simple CTEs, Sean. From the top of my head (i.e. not tested at all and possibly full of syntax errors):

WITH RECURSIVE TourneySubtourney(OID, MyLevel) AS (
SELECT T.OID, cast(1 as Integer)
FROM Tourneys T
WHERE T.ParentOID is null
UNION ALL
SELECT TChild.OID, TParent.MyLevel+1
FROM TourneySubtourney TParent
JOIN Tourneys TChild on TParent.ParentOID = TChild.OID)

SELECT * FROM TourneySubtourney

The above query gets the 'original ancestor' and then, recursively, it's children, grandchildren etc. I added MyLevel so that you can see if each tournament is a parent, child or further down the hierarchy.

Look for common table expressions in the release notes of Firebird (I think it was new in Firebird 2.1), there should be one or more examples. Also, questions like yours have been answered on this support list since last year, so you could search for "WITH RECURSIVE" in the archives.

Note that you will run into trouble if you have circular references, that is if a tournament is the child of one of its own children (in nature that is physically impossible, in databases it is an error that can easily happen if not careful during data entry).

Performance would normally be good, though if you have millions of tournaments it should be possible to come up with a query that can be time consuming (e.g. I could imagine poor performance if you tried to find all tournaments where the original ancestor have no tournament at level 4 that occurs simultaneously with a tournament of another ancestor at level 5 - although that would also mean a slightly more complex query than what I wrote above).

HTH,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Leyne, Sean
Sent: 22. august 2009 00:10
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] Re-entrant Queries

Lee,

> 1. Would anyone care to provide a simple example or pseudo
> code showing the basic logic of such queries? For instance,
> off the top of my head I would probably need a query that
> tested if a particular tournament's OID was already present
> in it's parent's tournaments hirearchy, etc so that I don't
> add a tournament farther from root using a tournament that
> was created closer to root.

I think you could use the new CTE (Common Table Expressions) syntax and do this in a single SQL statement, but I don't have much experience with them.

So, I will suggest the old/tried-and-true recursive stored procedure approach:


CREATE PROCEDURE GET_CHILD_Tourneys( Parent_TourneyID)
RETURNS (CHILD_TourneyID)
AS
BEGIN
FOR
SELECT OID
FROM Tourneys
WHERE ParentOID = :Parent_TourneyID
INTO
:CHILD_TourneyID
DO
BEGIN
-- Return this Tourney
Suspend;

-- Get the child of this Tourney
FOR
SELECT CHILD_TourneyID
FROM GET_CHILD_Tourneys( :CHILD_TourneyID)
INTO
:CHILD_TourneyID
DO
BEGIN

Suspend;
END

END
END

> 2. What about performance with queries like these? I imagine
> that a recursive Store Procedure could be hard put to handle
> very complex trees but I can't imagine going more than 5
> levels deep at any node but I guess that could be a lot as
> well if some nodes hold many peer nodes.

The performance should be fine regardless of the number of levels

Sean