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.
Eachrecord would have (besides your core data) pointers as necessary, one to the previous record (i.e. before "me") and
oneto the next record (i.e. after "me"). The first record in the chain would therefore have a null previous record and
thelast 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
beunique. 

HTH

Tim Clarke

-----Original Message-----
From: Johan Fredrik Øhman [mailto:johanfo@ohman.no]
Sent: 19 November 2003 15:09
To: pgsql-general@postgresql.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@postgresql.org so that your
      message can get through to the mailing list cleanly