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.


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://www.grokbase.com/group/dbix-class@lists.scsys.co.uk

Reply via email to