Re: Threaded Records in SQL: Advice Needed

From: mig(at)utdt(dot)edu
To: BIngram(at)sixtyfootspider(dot)com
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Threaded Records in SQL: Advice Needed
Date: 2000-04-11 21:13:03
Message-ID: 200004112113.SAA07995@ant.utdt
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

lexicographic ordering is what you want ...

So, new proposal (described from the start) which solves all this and
should (I think) be rather fast. On the other hand, the id fields get
larger and larger ...

(1) Ids of messages are of the form
/(field_0)/(field_1)/.../(field_n)
where each field counts the number of message at that level:
field_0 counts the root messages, field_1 the replies to the root
message at field_0, etc ...
(2) The field counters are numbers where
(a) the first digit gives the count of digits in the index (if you
think that you will have more than nine digits in any field,
make it a two-digit counter: 01, 02, ... 99
(b) the rest is the index
Same example as before: the second reply to the nineteenth reply
to the seventh reply to root message 25 has id
/225/17/219/12
(field_0=225 means: two digits in the counter, the counter is 25)
This does solve the issue of correct ordering: lexicographic
ordering of THESE indexes is what you want ... shorter numbers do
come before the longer ones
(3) The issuing of new indexes can be implemented in three sql functions
as follows:
create function next_reply_num(text) returns text as
'select (count(*)+1)::text from ids where id ~ ($1|| ''/[^/]*$'') '
language 'sql';
create function add_ct(text) returns text as
'select (char_length($1)::text || $1)'
language 'sql';
create function next_reply_id(text) returns text as
'select ($1 || ''/'' || add_ct(next_reply_num($1)))'
language 'sql';
The function next_reply_num gets the next counter; add_ct
prepends the digit count; next_reply_id assembles the lot.

Again, maybe this can be implemented with fewer sql function calls; it
can definitely be implemented in a single tcl or pgSQL function ...

Another remark on these functions: if you EVER delete a message from
the list, the procedure next_reply_num will wreak havoc in your
numbering! If that is a possibility, you should probably select
the largest reply number to that message, parse it, add one to the
counter, prepend the byte count and then compute the index for the new
reply.

PS: I just received your e-mail which solves these issues; here is an
alternative ... It is of course compatible with using letters
instead of numbers, as you propose - the new idea is adding a
"byte count" to each field, which can of course also be encoded in
a letter

--text follows this line--
>From: "Ingram, Bryan" <BIngram(at)sixtyfootspider(dot)com>
>Cc: pgsql-sql(at)postgresql(dot)org
>Date: Tue, 11 Apr 2000 14:57:00 -0500
>Content-Type: text/plain;
> charset="iso-8859-1"
>
>Thanks for the ideas on the functions, that'll work nicely.
>
>The only other problem I see in actually implementing this, is that the id
>column i.e. /25/10/2/ will not be ordered correctly because it relies on
>ascii values. You get alphabetic orderings, rather than numerical.
>
>Such as:
>
>1
>10
>11
>12
>14
>2
>20
>25
>3
>4
>5
>
>instead of
>
>1
>2
>3
>4
>5
>10
>11
>12
>14
>20
>25
>
>Any ideas how to get around this? I'm working on the problem right now, but
>haven't found anything yet.
>
>Bryan
>

In response to

Browse pgsql-sql by date

  From Date Subject
Next Message Michael McCarthy 2000-04-11 22:10:04 RE: Threaded Records in SQL: Advice Needed
Previous Message Ingram, Bryan 2000-04-11 20:57:35 RE: Threaded Records in SQL: Advice Needed