db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jeremy Boynes <jboy...@apache.org>
Subject Re: Tree structure query
Date Fri, 18 Mar 2005 16:51:39 GMT
A technique I have used for modeling filesystems and ldap structures is 
to split the tree into "folder" and "file" entities with the folder 
table containing denormalized paths. The denormalized path allows you to 
locate sub-trees with a LIKE match, with the pattern at the end so this 
is indexable.

Just like in your model, each entity refers back to the folder that 
contains it giving you an arbitrary depth (up to the limit of the 
denormalized path).

This allows you to query the folder structure easily (for a tree view) 
or to get the files in a folder fairly efficiently (leaf view). Moving 
folders is expensive as you need to rebuild the folder paths, but that 
tends to happen less frequently than file operations.


Clive Borrageiro wrote:
> Hi,
> I have a table that stores my filesystem entries in a structure that
> contains parent and child entries and the child entry contains its parent
> id.
> Eg.
> id	id_parent	path
> 1	0		c:
> 2	1		folder1
> 3	2		file1.txt
> 4	2		file2.txt
> 5	2		folder1_1
> 6	1		folder2
> I need a query to do the Oracle equivalent of:
> "SELECT name, id, id_parent FROM table1 START WITH id = 1 CONNECT BY id =
> id_parent" -or something like that.
> To get the result like this:
> C:\
> C:\folder1\
> C:\folder1\file1.txt
> C:\folder1\file2.txt
> C:\folder1\folder1_1\
> C:\folder2\
> Any help will be very much appreciated!
> Clive

View raw message