NYCPHP Meetup

[csnyder]

On 7/26/06, Hans Zaunere <lists at zaunere.com> wrote:
>
>
> Jim Hendricks wrote on Wednesday, July 26, 2006 9:32 PM:
> > I've got somewhere in my library of routines a set of routines for
> > trees using nested subsets.  It worked with mysql and if I recall
> > correctly the queries were all simple enough without the need for
> > subqueries.  I also seem to recall Hans working on a nested subset
> > lib and discussing it on this list.
>
> An NSM related presentation a while back:
>
> http://www.nyphp.org/content/presentations/nyphp/index.php?slide=2
>
>
> Old school code:
>
> http://cvs.nyphp.org/cvsweb.cgi/clew/Attic/pnsm.pcom
>
> http://cvs.nyphp.org/cvsweb.cgi/clew/lib/
>
>
> And search here:
>
> http://www.nyphp.org/google.php
>
> for things like 'nsm' for a lot of discussion.
>


The NSM works great (I use it everywhere), but beware of long insert
times in tables larger than 50,000 nodes.

The Clew code referenced above does not include methods for moving
nodes, unfortunately, and that's one of the harder operations to get
your head around. It involves 3 updates.

Pretend this is a nested set, where each letter is a node:
xxxmmmzzzzxxxx

The nodes marked m are the ones you want to move, and you want to move
them so that they are to the right of the nodes marked z.

1) right-shift the m nodes past the end of the nested set, which
creates a 3-node gap
2) left-shift the z nodes by 3 so that they fill the gap to the left,
and create a corresponding gap to the right
3) left-shift the m nodes into the new gap, to the right of the last z

The result:
xxxzzzzmmmxxxx

As Cliff pointed out, be sure to use an InnoDB table so that you can
combine the updates into a single transaction.

Also, it's really important from a performance point of view to limit
the table structure to integers only. Fixed-width columns speed the
update operation that increments al of the left and right values in
the table. That's why the node and content tables are kept separate,
and share a nodeid key.


-- 
Chris Snyder
http://chxo.com/