Now and again, I find myself wanting to store data in some kind of
variable-level hierarchy. To take a familiar example, let's say the
directory structure on my computer.
So I start to do something like:
CREATE SEQUENCE directory_id_seq;
CREATE TABLE directory {
parent INTEGER,
name TEXT,
id INTEGER
DEFAULT nextval('directory_id_seq')
PRIMARY KEY
};
INSERT INTO directory
VALUES (1, '\.');
ALTER TABLE directory
ADD FORIEGN KEY (parent)
REFERENCES directory(id)
ON DELETE CASCADE
ON UPDATE CASCADE;
Happy, happy. The problem is, while it's easy enough to define such a
data structure, support for recursive queries is rather lacking. To
unroll a directory tree, you basically have to resort to programming in
<insert your favorite language>.
Not that I really know what I'm talking about when I say 'unroll'. This
data structure is general enough to support cyclic directed graphs. So
what does it mean to 'unroll' such a beasty?
So what's my question then? Well, it seems like maybe there _should_,
or at least _could_ be support for some kinds of common problems. For
example, I would like to constrain my data structure to really be a
tree, and not a potentially cyclical graph.
Does anyone know whether there has been, or ever will be, movement in
this direction among the SQL literati? Am I asking a completely
inappropriate question?
Perhaps these types of problems are what OODB adherents are attempting
to address.
So, to sum, my question: Who plays in this space? Will the SQL
standard itself ever play in this space?
Personally, I'm really only interested in something elegant. Meaning I
don't want to mess around with a solution where this broker communicates
with that broker via an n-way blah blah blah. I can maintain literacy
in several tools at once, but not several dozen. Is my best bet simply
to accept SQL's limitations and program around them in C++ (or C, or
Python, or Perl, etc.)?
Ron Peterson
rpeterson@yellowbank.com