Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-sql by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group