From: Mark Firestone Date: 2004-07-22T00:45:16+09:00 Subject: Re: ruby postgresql question ----- Original Message ----- From: "Michael Neumann" To: "ruby-talk ML" Sent: Wednesday, July 21, 2004 3:42 PM Subject: Re: ruby postgresql question > > can you describe the table layout in sql? Sure. Here is the sql statement for a message board table @db.exec("CREATE TABLE #{table} (delete boolean DEFAULT false, \ locked boolean DEFAULT false, number int PRIMARY KEY, \ m_to varchar(40), \ m_from varchar(40), msg_date timestamp, subject varchar(40),\ msg_text text, exported boolean DEFAULT false)") > what does "reading individual messages in _each direction_" mean? Has > this something to do with the hierarchy of the messages? (In-response-To > etc.)? Ok. It's an old text (telnet) based Bulletin Board System. You go to look at messages post since you last read them. The system find this point for you. Then you press enter at the prompt to get the new messages, one at a time. Or you can jump to a particular message by typing it's number, or you can go backwards, if you like. > I don't really understand :-) [about making the table...] Well, messages get deleted, but the message numbers in the table don't change. The message numbers (when you are reading them) *do* change because they are always 1.. so the system needs to know which actual message to pull up when you type it's number in. They won't be the same, after one message is deleted. So I am doing a ... for row in @db.query("SELECT number FROM #{table} ORDER BY number") hash << row[0].to_i end and making an array, full of message numbers. A lookup table. So I can look up which message to pull up. > > hm, maybe it's better to use transactions and don't cache the messages > on the client side. > I'm not caching the messages, just making the lookup table. I'm concerned about future performace problems. > > yes, but maybe SQlite is even better suited for your purposes (easier to > setup, single database file, no server, faster). > I'll look into that. I've already got this thing working though... Plus, I want to write a web front end for this one day... > 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? Yep. But text based. Have a look. Telnet to: bbs.retrobbs.org 2323 and have a look, if you like. > 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 > ); > that's pretty similar to what I've done. Thanks again! Mark "But Schindler is bueno! Senior Burns is El Diablo!" -------------------------------------------------------------- Website - http://www.retrobbs.org Tradewars - telnet tradewars.retrobbs.org BBS - http://bbs.retrobbs.org:8000 IRC - irc.retrobbs.org #main WIKI - http://www.tpoh.org/cgi-bin/tpoh-wiki