Re: Threaded Records in SQL: Advice Needed

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
atthat 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 youthink that
youwill 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
correctordering: lexicographic   ordering of THESE indexes is what you want ... shorter numbers do   come before the
longerones
 
(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
assemblesthe 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
usingletters   instead of numbers, as you propose - the new idea is adding a   "byte count" to each field, which can of
coursealso be encoded in   a letter
 
      

--text follows this line--
>From: "Ingram, Bryan" <BIngram@sixtyfootspider.com>
>Cc: pgsql-sql@postgresql.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
>