On Sun, Apr 7, 2013 at 6:19 PM, Bill Moseley <mose...@hank.org> wrote:
> > On Sun, Apr 7, 2013 at 12:40 PM, Len Jaffe <lenja...@jaffesystems.com>wrote: > >> In my opinion, the best way to model a tree in an RDBMS is to use >> Materialized Path. >> >> I found the amount of work involved in each insert/delete of a nested set >> to be too great, and adjacency lists (parent pointer) fails to allow for >> retrieving all of a tree or subtree with one round-trip to the db. >> Materialized Path does not suffer these drawbacks. >> >> I recall there being a couple of MP modules available for DBI and DBIC. >> > > Thanks, I will look at these again. When I looked at using Materialized > Path before the expense of updates (e.g. moving a node from one location to > another) for a very large tree could be prohibitive. That is, moving a > parent involved updating every child item's path. > > I agree. But when you compare that cost to the cost of doing any insert/delete/move using nested sets, MP looks like a clear winner. Plus, the good** RDBMSes have functions that will allow you to implement updates of the form s/XXXX/YYYY/ (syntax will vary). In postgres you can say update something set mpath = regexp_replace(mpath, oldprefix, new prefix) where mpath like 'old_prefix%'; ** for various values of good > For representing a "file system" I would normally only need to show the > contents of a single node (folder) at any giving time which would be a > single query for Adjacency model -- but to show the path to any given node > would require a recursive query up the tree -- e.g. to fetch the path to > the current node. > This is where materialized path excels. len -- lenja...@jaffesystems.com 614-404-4214 www.lenjaffe.com Proprietor: http://www.theycomewithcheese.com/ - An Homage to Fromage Perl Advent Planet <http://www.lenjaffe.com/PerlAdventPlanet/> - Advent Calendars: Perlish and otherwise. Greenbar <http://www.greenbartraining.org/>: Grubmaster: 2012-2009, Grub Asst: 2008, Trained: 2007.
_______________________________________________ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://firstname.lastname@example.org