Recursive Queries

From: Paul Schrum <Paul_at_geopak.com>
Date: Tue, 27 Apr 1999 20:25:54 GMT
Message-ID: <37261D84.19D7AF13@geopak.com>

I have a data structure which is a hierarchical tree represented in a relationional database. Without going into the specifics of my application, a directory/file structure is an exact cognate for it.

Imagine something like File Manager or Windows Explorer in which there is a hierarchy of directories, and files under the directories. A given directory may have any number of files and any number of sub-directories. To represent this I have two tables: DIRECTORY and FILE. They appear as follows:

---

DESC DIRECTORY

DIRECTORY_ID     NUMBER(38)      PRIMARY KEY
NAME             VARCHAR2(256)
PARENT_DIR_ID    NUMBER(38)      FOREIGN KEY IS DIRECTORY.DIRECTORY_ID
=======================

DESC FILE

FILE_ID          NUMBER(38)      PRIMARY KEY
NAME             VARCHAR2(256)
PARENT_DIRID     NUMBER(38)      FOREIGN KEY IS DIRECTORY.DIRECTORY_ID
=======================

A sample of the data is as follows:

Root
|--config.sys
|--Program Files

|  |--OraNT
|  |  |--Oracle.exe
|  |--Games
|  |  |--Freecell
|  |     |--freecell.exe
|  |     |--freecell.ini
|  |--Microsoft
|     |--word.exe

|--autoexec.bat

(Assume that any item with a file extension is a file. Else it is a directoy)
Any directory can have an unlimited number of files and directories underneath it. There is no limit to the depth of directory nesting.

SELECT * FROM DIRECTORY

                          returns
DIRECTORY_ID   NAME            PARENT_DIR_ID
------------   --------------  --------------
1              Root            NULL
2              Program Files   1
3              OraNT           2
4              Games           2
5              Microsoft       2
6              Freecell        4


SELECT * FROM FILE
                     returns
FILE_ID     NAME                 PARENT_DIRID
--------    -------------------  -------------
1           config.sys           1
2           Oracle.exe           3
3           freecell.exe         6
4           freecell.ini         6
5           word.exe             5
6           autoexec.bat         1

The Question:
How do I compose a query which, given a DIRECTORY.NAME, returns all files underneath that directory at all depths? I want only the files. I do not need the subdirectories in the final resutl.

As I understand it, a simple subquery is not adequate because of the constraints I set above -- no limit to nesting depth or number of subitems.

BTW, when I populate these tables on the client side in C, I use a recursive function call. This question, then, could be viewed as asking whether there is an sql syntax for a recursive query, and what would that be.

To all who have made it this far in my post, I thank you. If you are able to respond, either here on usenet or directly to me via email is ok.

-Paul Schrum
GEOPAK Corp.

paul_at_geopak.com Received on Tue Apr 27 1999 - 15:25:54 CDT