[Dbix-class] Representing directory structures

[Len Jaffe]

On Sun, Apr 7, 2013 at 6:19 PM, Bill Moseley <moseley at hank.org> wrote:

>
> On Sun, Apr 7, 2013 at 12:40 PM, Len Jaffe <lenjaffe at jaffesystems.com>wro=
te:
>
>> 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 =3D 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

-- =

lenjaffe at 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.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20130408/774=
3a8be/attachment.htm