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. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
