Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (asf.osuosl.org: local policy)
From: Michael Segel <msegel@segel.com>
Reply-To: msegel@segel.com
Organization: MSCC
To: "Derby Discussion" <derby-user@db.apache.org>
Subject: Re: Recursive Select for Discussion Forum
Date: Wed, 30 Nov 2005 13:51:29 -0600
User-Agent: KMail/1.8.2
References: <438344B6.2000003@Sun.COM> <438D367E.6030305@frontiernet.net>
 <438DF281.40706@sun.com>
In-Reply-To: <438DF281.40706@sun.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200511301351.29961.msegel@segel.com>

On Wednesday 30 November 2005 12:42, Rick Hillegas wrote:
> Hi Michael,
>
> You could streamline your recursive walk by using a temporary table and
> a database procedure. The temporary table would hold the ids you
> recursively harvest. It would be populated by your database procedure,
> which would walk up the levels of your hierarchy. When the procedure
> returned, you could then join the temporary table to your
> discussion_item table to get the details you needed. It's not an elegant
> solution, but by running the procedure inside the database, you would
> eliminate a lot of network handshaking.
>
> Derby does not support hierarchical queries. You're welcome to log an
> enhancement request for this feature.
>
Well if you're going to use Temp tables, why not go outside of just SQL 
statements and then use cursors?

By using prepared statements you could do this and maybe use a little bit of 
recursion. Since this is SELECT, you'd want to make sure that you do this out 
side of a Transaction. (Transactions and recursive SQL statements can get 
messy.)

Pretty straight forward from there.
You only need to prepare two statements, and each recursion requires a unique 
resultSet to be declared.

Your table would look like this:
Table foo:
	msg_id int --  (you can autogenerate or use long or something else depending 
on # of messages and retension rules.)
	parent_id int (set default to 0)
	msg_author ...
	msg_header ...
	msg_txt ... 

Then create an index on msg_id and also on parent_id;

This can be done a couple of ways or permutations.
Here's the simplest.

Make the following CLASS variables;
A database connection con;
Create a vector to store the result sets.
int rec_lvl = 0 (this is the level of recursion)
Create and prepare the following select statement:
SELECT msg_id, msg_author, msg_header, msg_txt
FROM   foo
WHERE parent_id = ?
ORDER BY msg_id;

You then have two methods to write:
1) A Method to pull the data out of the result set, and pop it on to a vector, 
including the rec_lvl variable. (This will be used to build your tree)

2) The recursive method that finds all the children to the input value of 
parent_id.

So the first time you call the method you pass in the value of 0 for the 
parent id.
Then for each record you get back, you pass the result set to the popOnVector 
and the current rec_lvl value.
You then call the recursive routine, passing in the current msg_id and 
(rec_lvl + 1);

Thats all it takes.

The actual building of this is left as an exercise to the student.

HTH,

But hey, what do I know? You initially wanted to write recursive SQL which is 
never a good idea.


> Regards,
> -Rick
>
> Michael McCutcheon wrote:
> > I need to implement an online discussion forum, similar to JavaLobby.
> >
> > Similar kinds of discussion forums tend to use recursive selects, so
> > that in a single select, all of the items for a particular discussion
> > thread (and all sub threads, and their sub threads, etc) may be
> > retrieved.
> >
> > For a table, I'm thinking of something like this:
> >
> > create table discussion_item
> > (
> >  id integer not null,
> >  parent_id integer not null,
> >  creator_user_id varchar,
> >  title varchar,
> >  message varchar
> > )
> >
> > There needs to be some kind of recursive functionality to select a
> > item, and it's parents, and it's parents, etc.
> >
> > Does Derby support any kind of recursive selecting?  I know that this
> > is supported in Oracle and other databases.
> >
> > If it does not, is it planned to have it in the future?
> >
> > If not, are there any ways of handling scenarios like this without
> > having to do a select for each item?
> >
> > Thanks for any input.
> >
> > Mike