[Dbix-class] Representing directory structures

[Bill Moseley]

On Sun, Apr 7, 2013 at 12:40 PM, Len Jaffe <lenjaffe at 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.

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.


> On Sun, Apr 7, 2013 at 2:42 PM, Bill Moseley <moseley at hank.org> wrote:
>
>> Anyone using DBIC to represent hierarchal directory (folder) structures?
>>
>> Essentially just like a file system, except users each have their own
>> root.  To add to the complexity, I need to be able to "share" folders and
>> all child elements to other users.
>>
>> My assumption is this would involve a single table where each row can
>> represent either a directory entry (e.g. a "file") or a sub-directory th=
at
>> references its parent.
>>
>> Postgresql has "WITH RECURSIVE" which DBIC has no support for (other than
>> virtual views):
>>
>> http://www.postgresql.org/docs/9.2/static/queries-with.html
>>
>>
>> But, Adjacency Lists and Nested Sets are (were?) often discussed as
>> solutions for hierarchal data in the database.  I asked on the Pg list b=
ack
>> in 2007:
>>
>>
>> http://postgresql.1045698.n5.nabble.com/Adjacency-List-or-Nested-Sets-to=
-model-file-system-hierarchy-td1876116.html
>>
>>
>> but, so far, I've happily avoided using these approaches of representing
>> hierarchal data in the database.
>>
>>
>> BTW -- I have recently written a "tag"-based system (like the familiar
>> gmail) which is easy to represent in a highly normalized schema but is a
>> fundamentally different approach.
>>
>>
>> --
>> Bill Moseley
>> moseley at hank.org
>>
>> _______________________________________________
>> 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
>>
>
>
>
> --
> 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.
>
>
> _______________________________________________
> 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
>



-- =

Bill Moseley
moseley at hank.org
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20130407/73c=
167e9/attachment.htm