Forum OpenACS Q&A: New documentation for tree

Posted by Jade Rubick on 07/24/03 11:34 PM

I couldn't find any documentation for tree_sortkey, so I made it up myself, culling from whatever postings I could find.

It is available from here:

http://rubick.com/openacs/tree_sortkey or
http://rubick.com:8002/openacs/tree_sortkey

If you already know how to use tree_sortkey, please let me know if there are any errors in this document. It will be nice to be able to refer people to this in the future.

2: Re: New documentation for tree_sortkey (response to 1)

Posted by Mark Aufflick on 07/25/03 01:45 PM

Jade,

That is a great summary - I have always been a bit scared of an api that was emulating an Oracle feature I was scared of in the first place ;)

Your archive of documentation is truly prodigious!

3: Re: New documentation for tree_sortkey (response to 1)

Posted by Don Baccus on 07/26/03 12:44 AM

Here's some background information on using strings to model hierarchies in SQL.

Unfortunately LIKE doesn't utilize indexes in PostgreSQL if the installation's collation sequence isn't set to "C", as is true for folks using non-Latin character sets. So my implementation uses BIT VARYING which are treated like VARCHARs restricted to '0' and '1'. In Oracle, the Forums package implements tree_sortkeys using the RAW datatype.

You might want to reference that article in your doc.

And my name's spelled "Baccus" :)

4: Re: New documentation for tree_sortkey (response to 1)

Posted by Jade Rubick on 07/26/03 01:49 AM

Thanks for the reference, Don. Sorry I mispelled your name

The document has been updated, and also now includes an example that has a join to another table.

I believe you can't do that in Oracle, right? tree_sortkey seems like a really slick solution.

5: Re: New documentation for tree_sortkey (response to 1)

Posted by Randy Ferrer on 07/26/03 03:30 AM

Very helpful reference Jade. Thanks for making this available. Great background document Don - thanks for this as well.

I've seen a few patches that claim to give PG the ability to perform Oracle like hierarchical queries. Does anyone have any knowledge on any of these?

6: Re: New documentation for tree_sortkey (response to 5)

Posted by Roberto Mello on 07/26/03 05:54 PM

There's a patch that gives PG the Oracle CONNECT BY statement. That patch was refused by the PG core team because Oracle's CONNECT BY is:

1) Non-standard
2) Not as functional as the SQL 99 statements for hierarchical queries.

Or as Tom Lane would put it "Non-standard and broken."

So the PG team will implement the SQL 99 syntax. The Red Hat DB team (which Tom Lane belongs to) will do it, I think for 7.5 (don't think it made it to 7.4).

-Roberto

7: Re: New documentation for tree_sortkey (response to 6)

Posted by Randy Ferrer on 07/27/03 01:27 AM

I thought as much. I've never been a fan of CONNECT BY which so many people seem so enamoured with. Apart from the fact that it is rather limited in its use, it also violates relational closure. This is a problem since there is a high probability of information loss as you query further - just the behaviour you need when working with trees right? One of my professors at school was very keen on demonstrating the problems and it can get very messy.

There is an excellent chapter in "Practical Issues in Database Management" by F. Pascal if anyone is interested in more details. There are other issues as well, but suffice it to say that Connect by is far from an ideal solution. I couldn't imagine anything attempting to clone it's behaviour any better. Thanks for the info Roberto.

8: Re: New documentation for tree_sortkey (response to 1)

Posted by Butch Decuire on 09/02/04 06:00 PM

Jade,

I cannot open either link to the documentation. Can you send the documentation directly to me? This functionality will help resolve some coding issues for a new application.

Thanks,

Butch

butch.decuire@elpaso.com

9: Re: New documentation for tree_sortkey (response to 1)

Posted by Jade Rubick on 09/02/04 06:26 PM

This is now part of the documentation, in the advanced tutorial section.

10: Re: New documentation for tree_sortkey (response to 1)

Posted by paul giarrusso on 10/28/04 04:56 PM

Sorry, but I can not get to the link is it possible to send me the info via email. pgiarrusso@mccarter.com
Thanks for you help.

11: Re: New documentation for tree_sortkey (response to 1)

Posted by Jade Rubick on 10/28/04 06:46 PM

I personally am not going to send it by email, but the new link is at:

http://openacs.org/doc/current/tutorial-hierarchical.html

Forum OpenACS Q&A: New documentation for tree_sortkey