You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Michael McCutcheon <mi...@frontiernet.net> on 2005/11/30 06:19:58 UTC
Recursive Select for Discussion Forum
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
Re: Recursive Select for Discussion Forum
Posted by Michael Segel <ms...@segel.com>.
On Thursday 01 December 2005 7:01 am, Oyvind.Bakksjo@sun.com wrote:
> Michael Segel wrote:
> > 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.
>
> When presenting a discussion thread, there are typically two things you
> want to take into account:
> * Ordering of messages
> * Indentation
>
> Ordering:
> * All replies to a specific message (messages having a 'parent') should
> be displayed directly under that parent message.
> * Within a sub-group of messages, sharing the same parent, messages
> should be ordered on its timestamp.
>
> Indentation:
> * Every message which has a parent should be more indented than the parent.
> * Every message having the same distance to the "root" message (the one
> without a parent) should have the same indentation.
>
> With the current table definitions I have seen suggested here, neither
> of these can be computed dynamically with a single, non-recursive SQL
> query.
>
> The indentation part is easy: Don't compute it dynamically, store it in
> the table. Whenever you add a new record to the table, you know the id
> of the parent message. The indentation level for the record to be
> inserted is the parent's indentation level plus one.
>
> The ordering part is more tricky. What information do we have in each
> record that can be used for ordering.
>
> message id - most likely monotonically increasing with each message
> added (chronologically)
> parent id
> indentation level
> timestamp - this one is, like the message id, monotonically increasing,
> so it does not add any information for us to use when sorting
>
> So the table is (wrt sorting) a set of triplets: (message_id, parent_id,
> indentation). For a database to sort this set, it must be able to look
> at two random triplets and determine which of those comes first, without
> considering other triplets that may be in the set. Is this possible?
>
> As an example, look at these two triplets:
>
> (8,3,2)
> (7,6,3)
>
> Which comes first?
>
> It's impossible to tell, since it depends on the values of other rows.
> Consider these two message trees:
>
> (1,null,0) <- root message
> (2,1,1) <- 2nd message received, 1 is parent, indentation is 1
> (4,2,2) < - Received after message 3 below, but is reply to 2
> (3,1,1)
> (8,3,2)
> (5,1,1)
> (6,5,2)
> (7,6,3)
>
> Here, (8,3,2) comes before (7,6,3), because of the subgroups they belong
> to.
>
> (1,null,0)
> (2,1,1)
> (6,2,2)
> (7,6,3)
> (3,1,1)
> (8,3,2)
> (4,1,1)
> (5,1,1)
>
> Here, (7,6,3) comes before (8,3,2).
>
> So I think you don't get the complete ordering for free from SQL, you'll
> have to do some of the sorting in your java code. It should be possible
> to do with a 1-pass algorithm, I think.
>
Sigh.
Since this really sounds more like a trivial class exercise on recursive
programming, I didn't give away anything more than the basic structure.
But since you've asked... ;-)
1) Recursive SQL
Maybe I'm a tad slow since I've never attempted to write an SQL statement that
called itself. And while you could write a recursive stored procedure, I
wouldn't recommend it, unless you had to...
So, does the initial poster mean that he doesn't want to write a recursive
java class or a complex SQL statement?
If you're going to use temp tables, then you're not writing a single SQL
statement, are you?
So my solution is really a recursive java class that builds a vector that can
be used to build the tree structure.
2) Assumptions:
a) That all root discussions have a value of 0 for their parent_id.
b) How you want to order discussion threads is up to the student.
Note: message_id will increment in FIFO fashion therefore
they will most likely appear in time ordered fashion. ;-)
c) The popOnVector method will add to the end of the vector
To refresh your memory:
msg_id int
parent_id int (set default to 0)
msg_author ...
msg_header ...
msg_txt ...
Since this example deals with how to build the tree and not the details of
timestamps, author, header, actual text... I leave that up to the student.
The point is that with the tupple msg_id and parent_id I can build a vector
using a single SQL statement and a recursive java app.
For example, our message data set has the following:
{(1,0),(2,0),(3,1),(4,0),(5,3),(6,2)}
Note: the actual tuple would look like (1,0,'Fred','How about those White
Sox!', 'Blah blah blah') but since we're not really looking at the whole
statement but how to create an ordered tree, we can forget about everything
past the first two values.
The select statement is as follows:
SELECT msg_id, parent_id, ...
FROM foo
WHERE parent_id = ?
ORDER BY msg_id;
Since this is a simple class, lets do the base stuff in the main:
(This is psuedo code)
class retx{
// class variables here
Connection con;
Vector victor;
String stmt = " SELECT msg_id, parent_id, ... "+
" FROM foo " +
" WHERE parent_id = ? " +
" ORDER BY msg_id; "
PreparedStatement pstmt;
main(){
// Get database connection
con = getConnection();
// prepare the statement
pstmt = con.prepareStatement(stmt);
// now fetch the data and put it on the vector
fetchTree(0,0);
// the vector is build, so go do whatever --
}
fetchTree(int rec_lvl, int parentID){
// create current result set
pstmt.setInt(1,parentID);
ResultSet rs = pstmt.executeQuery()
while(rs.next()) {
// Put the result set on the vector along with the recursion
PopOnVector(rs,rec_lvl);
// check to see if this message has any children
fetchTree(rec_lvl +1, rs.getInt(1)); // we know that msg_id is first
}
}
Ok so you should be able to figure out how to pop the resultset and the
recursion level on to the vector...
So here's what you get:
The first select will return:
{(1,0),(2,0),(4,0)}
Since we're being recursive:
After we fetch (1,0) we then see if there are any children.
{(3,1)}
and so on...
Once we hit a node with no child, we return to the previous resultSet and we
then get the next value and start the recursion all over.
So when you pop it on to the vector you will see the following pattern:
(note v[n] is the vector where n is the integer number of the node. each node
contains the result set and the recursion level)
{(1,0),(2,0),(3,1),(4,0),(5,3),(6,2)}
v[1]:(1,0),0
v[2]:(3,1),1
v[3]:(5,3),2
v[4]:(2,0),0
v[5]:(6,2),1
v[6]:(4,0),0
Note: I didn't need to select the parent_id. Just makes it easier to see and
match visually.
From this vector you can build your tree in a single pass:
Topic:
1
+ 3
+ 5
2
+ 6
4
Having said all of this, why not just do a select, ordering by parent_id and
then msg_id and build your tree using a tree model? Since you're ordering the
select by parent, and then message id, you'll always know that the parent
node has already been created, so then you're just changing it from a leaf to
a branch. You can always find the node because you know its id in the tree.
But does this defeat the purpose of the assignment?
Again, in both of these examples, you don't need to sort within your code.
Or did I miss something?
;-)
Re: Recursive Select for Discussion Forum
Posted by Oy...@Sun.COM.
Michael Segel wrote:
> 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.
When presenting a discussion thread, there are typically two things you
want to take into account:
* Ordering of messages
* Indentation
Ordering:
* All replies to a specific message (messages having a 'parent') should
be displayed directly under that parent message.
* Within a sub-group of messages, sharing the same parent, messages
should be ordered on its timestamp.
Indentation:
* Every message which has a parent should be more indented than the parent.
* Every message having the same distance to the "root" message (the one
without a parent) should have the same indentation.
With the current table definitions I have seen suggested here, neither
of these can be computed dynamically with a single, non-recursive SQL query.
The indentation part is easy: Don't compute it dynamically, store it in
the table. Whenever you add a new record to the table, you know the id
of the parent message. The indentation level for the record to be
inserted is the parent's indentation level plus one.
The ordering part is more tricky. What information do we have in each
record that can be used for ordering.
message id - most likely monotonically increasing with each message
added (chronologically)
parent id
indentation level
timestamp - this one is, like the message id, monotonically increasing,
so it does not add any information for us to use when sorting
So the table is (wrt sorting) a set of triplets: (message_id, parent_id,
indentation). For a database to sort this set, it must be able to look
at two random triplets and determine which of those comes first, without
considering other triplets that may be in the set. Is this possible?
As an example, look at these two triplets:
(8,3,2)
(7,6,3)
Which comes first?
It's impossible to tell, since it depends on the values of other rows.
Consider these two message trees:
(1,null,0) <- root message
(2,1,1) <- 2nd message received, 1 is parent, indentation is 1
(4,2,2) < - Received after message 3 below, but is reply to 2
(3,1,1)
(8,3,2)
(5,1,1)
(6,5,2)
(7,6,3)
Here, (8,3,2) comes before (7,6,3), because of the subgroups they belong to.
(1,null,0)
(2,1,1)
(6,2,2)
(7,6,3)
(3,1,1)
(8,3,2)
(4,1,1)
(5,1,1)
Here, (7,6,3) comes before (8,3,2).
So I think you don't get the complete ordering for free from SQL, you'll
have to do some of the sorting in your java code. It should be possible
to do with a 1-pass algorithm, I think.
--
Oyvind Bakksjo
Sun Microsystems, Database Technology Group
Trondheim, Norway
http://weblogs.java.net/blog/bakksjo/
Re: Recursive Select for Discussion Forum
Posted by Michael Segel <ms...@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
Re: insert timestamp upon insert?
Posted by Michael McCutcheon <mi...@frontiernet.net>.
Satheesh Bandaram wrote:
> You can also define a default on the timestamp column... Derby allows
> references to special registers in default clause.
>
> Like: creation_date timestamp not null *default current timestamp*,
>
> Satheesh
>
> Mike Matrigali wrote:
>
>> you probably can use an insert trigger to get the desired result.
>>
>> Michael McCutcheon wrote:
>>
>>> Suppose I have a table like this:
>>>
>>> CREATE TABLE discussion_thread
>>> (
>>> discussion_thread_id INTEGER NOT NULL GENERATED ALWAYS AS
>>> IDENTITY (START WITH 1, INCREMENT BY 1),
>>> title VARCHAR(50) NOT NULL,
>>> creation_date TIMESTAMP NOT NULL,
>>> creator_user_id VARCHAR(32) NOT NULL
>>> );
>>>
>>> Is there a way to have derby automatically populate the
>>> creation_date column with the current date/time upon insert?
>>>
>>> Mike
>>>
>>>
>>>
>>
>>
>>
Thanks...this is just what I was looking for!
Re: insert timestamp upon insert?
Posted by Mike Matrigali <mi...@sbcglobal.net>.
you probably can use an insert trigger to get the desired result.
Michael McCutcheon wrote:
> Suppose I have a table like this:
>
> CREATE TABLE discussion_thread
> (
> discussion_thread_id INTEGER NOT NULL GENERATED ALWAYS AS
> IDENTITY (START WITH 1, INCREMENT BY 1),
> title VARCHAR(50) NOT NULL,
> creation_date TIMESTAMP NOT NULL,
> creator_user_id VARCHAR(32) NOT NULL
> );
>
> Is there a way to have derby automatically populate the creation_date
> column with the current date/time upon insert?
>
> Mike
>
>
>
Re: insert timestamp upon insert?
Posted by Rajesh Kartha <ka...@Source-Zone.Org>.
Hi,
Derby supports:
CURRENT DATE -
http://db.apache.org/derby/docs/10.0/manuals/reference/sqlj69.html
CURRENT TIME -
http://db.apache.org/derby/docs/10.0/manuals/reference/sqlj73.html
CURRENT TIMESTAMP -
http://db.apache.org/derby/docs/10.0/manuals/reference/sqlj75.html
Example:
ij> create table t(td date, ts timestamp);
0 rows inserted/updated/deleted
ij> insert into t values (current date, current timestamp);
1 row inserted/updated/deleted
ij> insert into t values(current_date, current_timestamp);
1 row inserted/updated/deleted
For more info please refer the Derby Reference manual at:
http://db.apache.org/derby/manuals/index.html
-Rajesh
Michael McCutcheon wrote:
> Suppose I have a table like this:
>
> CREATE TABLE discussion_thread
> (
> discussion_thread_id INTEGER NOT NULL GENERATED ALWAYS AS
> IDENTITY (START WITH 1, INCREMENT BY 1),
> title VARCHAR(50) NOT NULL,
> creation_date TIMESTAMP NOT NULL,
> creator_user_id VARCHAR(32) NOT NULL
> );
>
> Is there a way to have derby automatically populate the creation_date
> column with the current date/time upon insert?
>
> Mike
>
insert timestamp upon insert?
Posted by Michael McCutcheon <mi...@frontiernet.net>.
Suppose I have a table like this:
CREATE TABLE discussion_thread
(
discussion_thread_id INTEGER NOT NULL GENERATED ALWAYS AS
IDENTITY (START WITH 1, INCREMENT BY 1),
title VARCHAR(50) NOT NULL,
creation_date TIMESTAMP NOT NULL,
creator_user_id VARCHAR(32) NOT NULL
);
Is there a way to have derby automatically populate the creation_date
column with the current date/time upon insert?
Mike
Re: Recursive Select for Discussion Forum
Posted by Craig L Russell <Cr...@Sun.COM>.
Hi Michael,
There are two different issues. The one I heard you start asking
about is how to select all of the items in a thread. This can be done
by selecting all items that match a discussion_id/thread id. The
second is how to present them so their relationship to each other is
clear. This can be done by making sure that there is a parent_id
column that refers to the parent item and returning the parent_id in
the selection. I don't see a requirement to perform your selection
using parent_id.
Bottom line: use a thread_id column to select by; use a parent_id to
render showing parent relationship.
Craig
On Nov 29, 2005, at 9:36 PM, Michael McCutcheon wrote:
> Daniel Noll wrote:
>
>> Michael McCutcheon wrote:
>>
>>> <snip>
>>> If not, are there any ways of handling scenarios like this
>>> without having to do a select for each item?
>>
>>
>> I would have done this by having a discussion_id / thread_id column.
>>
>> A discussion generally has properties of its own, so in a full
>> application you would presumably have a discussion or thread table
>> already. Selecting all messages in that discussion then becomes a
>> trivial matter.
>>
>> Daniel
>>
> But then how do you maintain the hierarchy like javalobby? i.e.:
>
> Topic Begin
> |
> response
> response
> |
> response
> |
> response
> |
> response
> response
> |
> response
> |
> response
> response
>
>
> Don't you have to have a parent_id on each item?
>
> It seems like what you are suggesting is a 'flat' type of forum
> (which I did not think about) where all items have the same parent
> (for a top-level thread), and are shown sorted by date, not by
> hierarchy. Is that what you are suggesting?
>
> I'm really trying to emulate the behavior of javalobby.
>
> Thanks,
>
> Mike
>
Craig Russell
Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
408 276-5638 mailto:Craig.Russell@sun.com
P.S. A good JDO? O, Gasp!
Re: Recursive Select for Discussion Forum
Posted by Michael McCutcheon <mi...@frontiernet.net>.
Daniel Noll wrote:
> Michael McCutcheon wrote:
>
>>> I would have done this by having a discussion_id / thread_id column.
>>>
>>> A discussion generally has properties of its own, so in a full
>>> application you would presumably have a discussion or thread table
>>> already. Selecting all messages in that discussion then becomes a
>>> trivial matter.
>>>
>>> Daniel
>>>
>> But then how do you maintain the hierarchy like javalobby? i.e.:
>>
>> <snip>
>>
>> Don't you have to have a parent_id on each item?
>
>
> Yes, you have both. A parent_id for threading the conversation, _and_
> a thread_id to make querying for the entire tree more convenient.
>
> Daniel
>
Ahhh...I think I see it now. With the thread_id on each record, I could
select them all, then do whatever recursion necessarry in the java code
to line everything up...
Thanks for all the suggestions everyone, it really helps.
Mike
Re: Recursive Select for Discussion Forum
Posted by Daniel Noll <da...@nuix.com.au>.
Michael McCutcheon wrote:
>> I would have done this by having a discussion_id / thread_id column.
>>
>> A discussion generally has properties of its own, so in a full
>> application you would presumably have a discussion or thread table
>> already. Selecting all messages in that discussion then becomes a
>> trivial matter.
>>
>> Daniel
>>
> But then how do you maintain the hierarchy like javalobby? i.e.:
>
> <snip>
>
> Don't you have to have a parent_id on each item?
Yes, you have both. A parent_id for threading the conversation, _and_ a
thread_id to make querying for the entire tree more convenient.
Daniel
--
Daniel Noll
NUIX Pty Ltd
Level 8, 143 York Street, Sydney 2000
Phone: (02) 9283 9010
Fax: (02) 9283 9020
This message is intended only for the named recipient. If you are not
the intended recipient you are notified that disclosing, copying,
distributing or taking any action in reliance on the contents of this
message or attachment is strictly prohibited.
Re: Recursive Select for Discussion Forum
Posted by Michael McCutcheon <mi...@frontiernet.net>.
Daniel Noll wrote:
> Michael McCutcheon wrote:
>
>> <snip>
>> If not, are there any ways of handling scenarios like this without
>> having to do a select for each item?
>
>
> I would have done this by having a discussion_id / thread_id column.
>
> A discussion generally has properties of its own, so in a full
> application you would presumably have a discussion or thread table
> already. Selecting all messages in that discussion then becomes a
> trivial matter.
>
> Daniel
>
But then how do you maintain the hierarchy like javalobby? i.e.:
Topic Begin
|
response
response
|
response
|
response
|
response
response
|
response
|
response
response
Don't you have to have a parent_id on each item?
It seems like what you are suggesting is a 'flat' type of forum (which I
did not think about) where all items have the same parent (for a
top-level thread), and are shown sorted by date, not by hierarchy. Is
that what you are suggesting?
I'm really trying to emulate the behavior of javalobby.
Thanks,
Mike
Re: Recursive Select for Discussion Forum
Posted by Daniel Noll <da...@nuix.com.au>.
Michael McCutcheon wrote:
> <snip>
> If not, are there any ways of handling scenarios like this without
> having to do a select for each item?
I would have done this by having a discussion_id / thread_id column.
A discussion generally has properties of its own, so in a full
application you would presumably have a discussion or thread table
already. Selecting all messages in that discussion then becomes a
trivial matter.
Daniel
--
Daniel Noll
NUIX Pty Ltd
Level 8, 143 York Street, Sydney 2000
Phone: (02) 9283 9010
Fax: (02) 9283 9020
This message is intended only for the named recipient. If you are not
the intended recipient you are notified that disclosing, copying,
distributing or taking any action in reliance on the contents of this
message or attachment is strictly prohibited.
Re: Recursive Select for Discussion Forum
Posted by Rick Hillegas <Ri...@Sun.COM>.
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.
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
>
Re: Derby with beans and null primitives
Posted by Thomas Dudziak <to...@gmail.com>.
On 11/30/05, Michael McCutcheon <mi...@frontiernet.net> wrote:
> Yes, I have heard of these, but there is a HUGE learning curve to
> these. What I need to do is fairly simple and does not require a
> complete 'persistence solution'. I just need to populate beans from
> resultsets and need to know how to handle null database integers with
> java primitives.
>
> How does hibernate do this?
Look here for something simple that does this:
http://jdbi.codehaus.org/
http://jakarta.apache.org/commons/dbutils/examples.html
Tom
Re: Derby with beans and null primitives
Posted by Thomas Dudziak <to...@gmail.com>.
On 11/30/05, Bryan Pendleton <bp...@amberpoint.com> wrote:
> Michael McCutcheon wrote:
> > I'm implementing a utility class that populates 'beans' with data from
> > tables in derby.
>
> Aren't you sort of re-inventing the wheel? It seems like there are a
> lot of libraries already out there which do this already. Surely
> it would be easier to just use an existing library?
>
> Try Google-searching for strings like JDO, Hibernate, Castor, etc.
Or perhaps jDBI or commons-dbutils, might be easier for his purposes.
Tom
Re: Derby with beans and null primitives
Posted by Michael McCutcheon <mi...@frontiernet.net>.
Bryan Pendleton wrote:
> Michael McCutcheon wrote:
>
>> I'm implementing a utility class that populates 'beans' with data
>> from tables in derby.
>
>
> Aren't you sort of re-inventing the wheel? It seems like there are a
> lot of libraries already out there which do this already. Surely
> it would be easier to just use an existing library?
>
> Try Google-searching for strings like JDO, Hibernate, Castor, etc.
>
> thanks,
>
> bryan
>
>
>
>
Yes, I have heard of these, but there is a HUGE learning curve to
these. What I need to do is fairly simple and does not require a
complete 'persistence solution'. I just need to populate beans from
resultsets and need to know how to handle null database integers with
java primitives.
How does hibernate do this?
Returning 0 will not work because 0 is different than null.
Thanks
Mike
Re: Derby with beans and null primitives
Posted by Bryan Pendleton <bp...@amberpoint.com>.
Michael McCutcheon wrote:
> I'm implementing a utility class that populates 'beans' with data from
> tables in derby.
Aren't you sort of re-inventing the wheel? It seems like there are a
lot of libraries already out there which do this already. Surely
it would be easier to just use an existing library?
Try Google-searching for strings like JDO, Hibernate, Castor, etc.
thanks,
bryan
Re: Derby with beans and null primitives
Posted by Lars Clausen <lc...@statsbiblioteket.dk>.
On Wed, 2005-11-30 at 06:28, Michael McCutcheon wrote:
> I'm implementing a utility class that populates 'beans' with data from
> tables in derby. Suppose I have the following 'bean':
>
> public class Person
> {
> private String firstName;
> private String lastName;
> private int weight;
>
> public void setWeight(int inWeight)
> {
> this.weight = inWeight;
> }
> public int getWeight()
> {
> return this.weight;
> }
> }
>
> I select out of a Person table to get the information to populate this bean.
>
> But what happens if weight is nullable in the table?
>
> Since you can't put nulls into primitives in Java, how is this handled?
>
> This is a real problem because 0 is definately different than no value.
>
> Also is there some standard for how JSP's deal with beans with primitive
> types?
>
> How should null primitives be handled in beans like this?
In Java 1.5, you could declare weight as an Integer and make use of the
autoboxing feature. Don't know how that interacts with beans, though.
-Lars
Re: Derby with beans and null primitives
Posted by Craig L Russell <Cr...@Sun.COM>.
Hi,
I think your issue is that you are using the wrong representation for
the data.
If you have nullable columns, map them to Integer.
If you have non-nullable columns, map them to int.
Otherwise, you will have this issue. If you are stuck with an
inflexible object model and an inflexible database schema, then you
have to have a policy in place for handling the mismatch. That is,
how to store a null Integer into a non-nullable column, and how to
store a null column value into an int. Your choices typically are to
throw an exception or to represent null as zero. If you choose to
represent null as zero you lose information. But this is an
application policy choice.
Craig
On Nov 29, 2005, at 9:28 PM, Michael McCutcheon wrote:
> I'm implementing a utility class that populates 'beans' with data
> from tables in derby. Suppose I have the following 'bean':
>
> public class Person
> {
> private String firstName;
> private String lastName;
> private int weight;
> public void setWeight(int inWeight)
> {
> this.weight = inWeight;
> }
> public int getWeight()
> {
> return this.weight;
> }
> }
>
> I select out of a Person table to get the information to populate
> this bean.
>
> But what happens if weight is nullable in the table?
>
> Since you can't put nulls into primitives in Java, how is this
> handled?
> This is a real problem because 0 is definately different than no
> value.
>
> Also is there some standard for how JSP's deal with beans with
> primitive types?
>
> How should null primitives be handled in beans like this?
>
> Thanks
>
> Mike
>
Craig Russell
Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
408 276-5638 mailto:Craig.Russell@sun.com
P.S. A good JDO? O, Gasp!
Derby with beans and null primitives
Posted by Michael McCutcheon <mi...@frontiernet.net>.
I'm implementing a utility class that populates 'beans' with data from
tables in derby. Suppose I have the following 'bean':
public class Person
{
private String firstName;
private String lastName;
private int weight;
public void setWeight(int inWeight)
{
this.weight = inWeight;
}
public int getWeight()
{
return this.weight;
}
}
I select out of a Person table to get the information to populate this bean.
But what happens if weight is nullable in the table?
Since you can't put nulls into primitives in Java, how is this handled?
This is a real problem because 0 is definately different than no value.
Also is there some standard for how JSP's deal with beans with primitive
types?
How should null primitives be handled in beans like this?
Thanks
Mike
Re: Recursive Select for Discussion Forum
Posted by Suavi Ali Demir <de...@yahoo.com>.
You do not necessarily need recursion to do that.
Check out Ceolko's book for more info:
http://www.elsevier.com/wps/find/bookdescription.cws_home/702605/description#description
This small article has nice images to give you ideas:
http://www.codeproject.com/database/nestedsets.asp?df=100&forumid=15522&exp=0&select=505582
Here is an article by Joe Celko on Hiearchical data and sql:
http://www.intelligententerprise.com/001020/celko.jhtml?_requestid=273960
There are other ways too, just search on yahoo for hierarchical data sql relational database
Regards,
Ali
Michael McCutcheon <mi...@frontiernet.net> 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