July 31st, 2003, 04:38 PM
Tree Structure. How?
I just wondered what would be the best way to make a tree structure (filesystem) in database (postgresql in my case, but it's a general question).
The best way I can think of for now is to do a structure like that:
I want it (structure) to be optimalised for the things like that:
name VARCHAR( LIMIT )
type BIT(2) -- 00 for file, 01 for directory, (links are optional) 10 for link to file and 11 for link to directory
links: -- optional as I said
select all from a directory with a specified path/id
insert/select a file using path/id
read a whole directory and all subdirectories and all their subdiretories etc. etc. using path/id
I won't be using any of listed below often so they don't need to be vary fast:
getting path using id
It's the best way to do this if you ask me, but maybe I'm just too tired so say what do you think.
July 31st, 2003, 07:06 PM
Check out our discussion of Graph representation in database. In it, we also discussed trees. Especially of interest to you should be the "materialized path" approach. Also, there is apparently an add-on to PostgreSQL in the 'contrib' section to imitate Oracle/MSSQL CONNECT BY syntax for adjacency list handling of tree data.
August 1st, 2003, 07:25 AM