Thread: storing a tree-like structure and selecting path from leaf to root
Hi,
( this msg has 2 questions )
I would like to create a db of all files (home + office + backups +
friends_backups from floppies, tapes, CDs, etc), and would like to store
it in pg. Now every row (a file or a directory) will have an node ID, name
(filename / dirname) and a parent node ID, except for root which will have
no parent, or be parent for self.
This will create a tree-like "filesystem" representation, with total
leaf count probably 10million, and depth up to 20 or 25.
In light of these estimates, I suspect that it will take a looooong
time to display all full paths from the root to the leaf for a set of
leafs (suppose that my WHERE clause will be satisfied by 50,000 files). I
expect that leafs' average depth will be 8.
Q1: What is a good way to store this tree in ? (This is somewhat generic
question, so it may be a good FAQ candidate.) I want SELECTs to be fast,
and INSERTs/UPDATEs I don't care. Would making custom datatype help ? How?
Q2: What would be a good (fast) SELECT statement to show these 50,000
selected rows, but have each row of the result contain *the full path* of
the found file instead of just the file's name.
Thanx a bunch,
Jan
-- Gospel of Jesus is the saving power of God for all who believe --
## To some, nothing is impossible. ##
http://Vicherek.Waterloo.on.ca/
Re: [GENERAL] storing a tree-like structure and selecting path from leaf to root
From
Herouth Maoz
Date:
At 09:44 +0300 on 15/08/1999, Jan Vicherek wrote: > Q1: What is a good way to store this tree in ? (This is somewhat generic > question, so it may be a good FAQ candidate.) I want SELECTs to be fast, > and INSERTs/UPDATEs I don't care. Would making custom datatype help ? How? About a year and a half ago, there was a book recommendation about this issue. The book discusses advanced data structures representation with SQL. Now that PostgreSQL has subqueries and unions, it becomes more relevant. The book was: Joe Celko's SQL for Smarties Advanced SQL Programming The publisher: Morgan Kaufmann Publishers, Inc 340 Pine St 6th Floor San Francisco CA 94104-33205 USA 415-392-2665 mkp@mkp.com http://www.mkp.com The original poster of this recommendation was Terry Harple, and it was on the (now defunct) QUESTIONS list. Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma