Subject Re: [firebird-support] Open to suggestions: building a tree
Author Helen Borrie
At 03:00 AM 23/08/2003 +0000, you wrote:
>I'm using FB and here's what I want to do:
>Table dir_tree has three fields, Dir_ID (integer, pk), Parent
>(integer, fk to Dir_ID), and This_Dir, varchar(250).
>For the insert proc, I want to bring in a string like
>"d:\top\next\xyz\abc\here is a dir"
>I want this to end up being 6 records in this table, "d", "top",
>"next", "xyz", "abc" and "here is a dir" with the parent field
>pointing to the parent, except for "d" which would have a null parent.
> Standard linked list kind of thing.
>Where I'm stuck is taking apart the incoming string and stashing it
>somehow in FB's procedure language so I can go through it and act on
>each part. I found that page that shows SQL version of pos, and I
>could easily do a Delphi thing to do pos and break it up. What I'm
>looking for is some sort or cursor or array. I can't seem to find
>anything that fits the bill. Any suggestions?

You could easily use SUBSTRING and UDFs to write a "BreakApart" procedure
similar to what every Delphi programmer keeps in her "MyUtils.pas", that
returns two strings: the leftmost dir and "the rest".. At each call, the
first return value is a node name, the rest gets recycled for further
processing unless it is an empty string.

Write BeforeInsert and BeforeUpdate triggers that pass one argument
(NEW.This_Dir, initially) and iteratively process the RETURNING_VALUES
until an empty string comes back as "the rest" (if you get my drift).

The triggers would have to test for matching nodes so you'd need to look
after existence things with unique constraints and/or EXISTS() tests to
ensure that you avoided duplicate nodes, missed renamings, recursive loops,
et al.

This is pretty broad-brush - depending on requirements, you might need to
pass some flags around as well...This stuff is always fun to play with. :-))