Forum OpenACS Q&A: New documentation for tree_sortkey

Collapse
Posted by Jade Rubick on
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.

Collapse
Posted by Mark Aufflick on

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!

Collapse
Posted by Don Baccus on
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" :)

Collapse
Posted by Jade Rubick on
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.

Collapse
Posted by Randy Ferrer on

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?

Collapse
Posted by Roberto Mello on
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

Collapse
Posted by Randy Ferrer on

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.

Collapse
Posted by Butch Decuire on
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

mailto:butch.decuire@elpaso.com

Collapse
Posted by Jade Rubick on
This is now part of the documentation, in the advanced tutorial section.
Collapse
Posted by paul giarrusso on
Sorry, but I can not get to the link is it possible to send me the info via email. mailto:pgiarrusso@mccarter.com
Thanks for you help.
Collapse
Posted by Jade Rubick on
I personally am not going to send it by email, but the new link is at:

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