Re: Adjacency List or Nested Sets to model file system hierarchy?

On 2/12/07, Bill Moseley <moseley@hank.org> wrote:
> On Mon, Feb 12, 2007 at 10:53:53AM -0500, Merlin Moncure wrote:
> > On 2/12/07, Richard Broersma Jr <rabroersma@yahoo.com> wrote:
> > >> Can you describe in a little bit more detail about what you mean by
> > >> 'Adjaceny LIst'?
> > >
> > >Adjaceny list is the term used in the celko book to refer to a table that
> > >is recurively related to
> > >itself.
> > >
> > >create table foo (
> > >id        integer  primary key,
> > >parentid  integer references foo (id),
> > >name      varchar not null,
> > >);
> >
> > Above approach is ok but I can think of at least two other methods
> > that are probably better.  First approach is to just store the whole
> > path in every record for each file.  Yes, this is a pain for updates
> > but searching and children discovery is simple.  in that case I would
> > define pkey as (path, file).
>
> Yes, that's what I meant by using a de-normalized table -- including
> the full path in the row.  That would provide fast access to each row
> via a path name.  And the parent id makes it easy to find all children
> of a given node and, well, the parent too.
>
> Separating the path and file as you suggest would make finding all
> "files" at a given directory level simple, too.
>
> But, I'm not thrilled about the possibility of the hard-coded path not
> matching the path up the tree to the root node, though.  Which, of
> course, is why I posted.  But, I'll give it a test.

The way I do it is to update the path to the parent's path, plus my id
on insert or update with a before trigger.  I have an after trigger
that simply updates any child record's parent_id, which forces an
update of the path, which forces update of their children, and so on.

You can, of course, cause a recursion problem if you're not careful...
Best to have a check for that too.

- Ian
>
> Thanks,
>
>
>
>
> --
> Bill Moseley
> moseley@hank.org
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly
>