[dev] postgres ltree and DataTree/sql.php

[Matt]

Hi all,

A while back I worked on a project that stored tree-like data (a web
site's directory structure) in postgres, and ran across an extension
called ltree (it's in the /contrib directory if you've done a source
install). I'm interested in using it for a DataTree driver (erm...
pgltree.php?), but have more questions than answers at present.

If you're not familiar with it, ltree is written in C, and originally
designed for Open Directory-like classification trees. It allows you to
create tree structures using dot-separated names like: 
root.myhome.mydir.mypage
root.myhome.another_dir.mypage

Ltrees can be efficiently indexed, there's no column-length
restrictions, and it gives a pretty comprehensive set of query tools for
yanking out all children, all parents, all 5th-level nodes called 'foo',
etc.

Seeing as DataTree is so central to Horde, it seems like an excellent
fit. But I'm a little stuck on the structure.

Ideally the tree column would contain as much info as possible that gets
searched on. Right now horde_categories has:
group_uid | user_uid | category_name

I think these should get rolled into one ltree column, creating a
structure something like:
horde.groups.user1.sales 
or
horde.shares.ansel.user2.mygallery

But there seems to be a fair amount of inconsistency about how that
group_uid column is actually used. Most apps seem to create a level
under horde.shares, but some like giatepo go completely their own way.
Am I missing something here? And does this also touch on the guid
discussion from a few days ago?

If there were a basic structure like [app].[share].[user].[blah]...,
querying an app part for a specific user (or all users or set of users)
would be simple and fast. Are there situations where that _wouldn't_
work?

I think I should be able to work around the existing structure without
breaking the apps which rely on it, and provide a conversion script for
existing db's, but I'd be interested to hear if anyone has other ideas
on/plans for the existing group_uid col structure.

It's probably pretty much a minority addition - you've got to have built
postgres from source and compile and install the extension on your
database before you can use it: not mission impossible; definitely not
plug-and-pray. But I know from the past it would be many benefits:
triggers to handle moving/deleting nodes (code for doing that doesn't
have to be written in Horde then again other app that wants to
manipulate the table); a single column to index and search which'll be
pretty fast, particularly when doing 'all parents of...' and 'all
children of...' type queries.

Got some other stuff to finish before I start coding, but any feedback
at this point would be great!

Regards,

Matt