RE: Threaded Records in SQL: Advice Needed

In reference to the ascii/numeric ordering problem ..

But first let me say ..if someone knows of a way to get ascii values to
order as if they were numerics ..please let me in on the secret ..

Instead of using numbers, if letters were used the ordering would be
correct.

The id string would become something like: A/A/A for the 1st reply to the
1st reply of the 1st root message.

This will work fine for threads with relatively few replies on the same
level. For instance, the number 100 would take only 4 characters to encode,
however, the number 1000 would need 39 characters to encode.

For my purpose 100-200 replies on any given level will suffice nicely.

Even though text fields are limited to 4096 characters, even if each level
needed 40 characters for encoding, I would still have room for approximately
100 levels (plenty) ..

Adding a step to the procedure you developed, we could convert the returned
"next reply number" into its alphabetic equivalent.

e.g. 1 = A
2 = B
26 = Z
27 = ZA
28 = ZB
52 = ZZ
53 = ZZA

The procedure is this:

next_id/26 = number of Z's
mod of next_id/26 = numeric position within alphabet

So .. if you wanted to add the 100th reply to the 2nd root topic ...

100/26 = 3 (ZZZ) (drop everything but the integer)
mod of 100/26 = 12 (L)

So we'd have 3 Z's and an L for the second root topic.

The fully assembled ID would be /B/ZZZL

Then a reply to this would become /B/ZZZL/A

So on, and so forth ..

This is verging on getting kludgey, but it still looks like it meets all of
the criteria:

1) Fast inserts
2) Fast selects of a thread or part of a thread
Actually this criterion hinges on the quality of the indexing
3) Rows are returned in order, with only one select.

So ..I really wish there was a better way ..but this isn't too big of a
price to pay.

Thanks,
Bryan

-----Original Message-----
From: Ingram, Bryan
Sent: Tuesday, April 11, 2000 3:05 PM
To: 'mig(at)utdt(dot)edu'
Cc: pgsql-sql(at)postgresql(dot)org
Subject: RE: [SQL] Threaded Records in SQL: Advice Needed

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