Re: Storing a chain

Just quickly here (it's a while since I've needed to do this!), consider a linked list of records of your own creation. Each record would have (besides your core data) pointers as necessary, one to the previous record (i.e. before "me") and one to the next record (i.e. after "me"). The first record in the chain would therefore have a null previous record and the last would have no following record. Inserting a record in the tree then becomes a matter of updating just two other records, inserting the new record between them. The pointers are probably just a sequence number, since it only has to be unique.

HTH

Tim Clarke

-----Original Message-----
From: Johan Fredrik Øhman [mailto:johanfo(at)ohman(dot)no]
Sent: 19 November 2003 15:09
To: pgsql-general(at)postgresql(dot)org
Subject: [GENERAL] Storing a chain

Hi,
I'm seeking some opinion on the following problem

For those familiar with "iptables/netfilter", I am actually storing firewall
rules in the database. The order of these rules is critical, so there has to
be some kind of system to how they are ordered. As I see it there are at
least 2 approaches.

1) Use a Sequence number in the table. This way it is possible to use ORDER
BY sequencenumber to retrive the correct list. This works, but you have to
do some massive updating when you what to insert a rule between i.e "12" and
"13".

CREATE TABLE FW_CHAIN (
FW_CHAIN_ID INT4 not null,
PARENT_ID INT4 null,
.... fields ..............
constraint PK_FW_CHAIN primary key (FW_CHAIN_ID)
);

2) Use a Parent_Id, as described intended in the table above. I feel that
this is a little prettier, even if it might be somewhat less efficient.
Unfortunately, I'm really not sure how to perform the queries. As I see it
some kind of recursive select statement is needed. Cursors ? Stored
Procedure?

If somebody has the time, I'd really appreciate some links to relevant
examples, documentation or advice on how to solve this.

--
Johan Fredrik Øhman

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
message can get through to the mailing list cleanly