Jim C. Nasby wrote:
On Thu, Feb 16, 2006 at 08:23:43PM +0100, Philipp Kn?sel wrote:
SanjayK schrieb:
Since SQLite is perfect for use in single-user desktop utility applications
and since such applications typically store hierarchial data (tree) in a
single table, it would be nice to have support for special features like
connect by of oracle.
See:
http://www.adp-gmbh.ch/ora/sql/connect_by.html
--
View this message in context:
http://www.nabble.com/Managing-trees-in-the-database-t1135555.html#a2974277
Sent from the SQLite forum at Nabble.com.
Depending on your goals, this concept might give you another solution:
http://dev.mysql.com/tech-resources/articles/hierarchical-data.html
Yet again, examples of mis-information from MySQL...
"The first common task when dealing with hierarchical data is the
display of the entire tree, usually with some form of indentation. The
most common way of doing this is in pure SQL is through the use of a
self-join:"
Self-join maybe, but certainly not in the way they're suggesting.
Databases that support WITH make this easy (and it's technically a
self-join). The problem with what he's proposing is that it silently
limits you to (in this case) 4 levels of nesting. If you have more
levels than that you'll end up missing data from either the top or
bottom of the tree. Of course, if you think "Feb 31" is a valid date,
maybe that's OK...
Their information about using a nested set model seems accurate, though.
Another option is to use 'ltree'. There used to be an implimentation for
PostgreSQL, but it looks like it's been removed.
http://developer.postgresql.org/cvsweb.cgi/pgsql/contrib/ltree/README.ltree?rev=1.7
has more info.
I really miss the oracle "connect by" operator. I first used it for a
postal application back in 1992. I am surprised that this feature has
not made it into to the standard or any other RDBMS. Maybe we should
see about adding the extension.
