From: Michael Neumann <mneumann@...>
Date: 2004-07-21T23:42:56+09:00
Subject: Re: ruby postgresql question

Mark Firestone wrote:
> Well, I've written re-written an old BBS program.  Right now it holds it's
> messages in individual text files (really marshaled object files).
> 
> I can't begin to tell you how much this sucks.
> 
> So, I'm moving it all to sql.  I have a table which is a message board.  the
> messages are records.  each message has a unique number used as a new
> message index.  each user has a pointer to that record.

can you describe the table layout in sql?

> I need to be able to go up and down the sql table, reading individual
> messages in each direction, and be able to jump to a message by number, etc.

what does "reading individual messages in _each direction_" mean? Has 
this something to do with the hierarchy of the messages? (In-response-To 
etc.)?

> Right now I am making a array of the message numbers, and looking up the
> messages that way.
> 
> So if I want message 4 in the list, which is really message 3219, because
> the ones before it have been deleted, then I ask the array...

I don't really understand :-)

> index_tbl[message] = whatever the number I should pull out of the table is.
> 
> I got the index_tbl by doing a select on the board table, ordered by the
> message number.
> 
> Anyway, before each operation, I have to rebuild the table, because the
> whole thing is multi-user... and someone else might have done  something
> like add or delete a message.  it works fine when there are a few messages,
> but I'm not sure how it will perform when it hits 1000's of messages.

hm, maybe it's better to use transactions and don't cache the messages 
on the client side.

> My friend pointed me at postresql... it seems lean and fast.

yes, but maybe SQlite is even better suited for your purposes (easier to 
setup, single database file, no server, faster).

I probably don't understand what a BBS is, but isn't that something like 
a "forum" where you can post messages and respond to others' messages?

Here's how I would design the messages table:

   create table messages (
     id          integer primary key,
     original_id integer,    /* to be backwards compatible with old 
messages */
     parent      integer null references messages (id),
     .... /* other attributes */
     body        text
   );


Regards,

   Michael