Martin A has asked for the wisdom of the Perl Monks concerning the following question:

Hi fellow monks.

I'm currently working on a web site (community) that will need some kind of chatterbox/short messaging system. The idea of the chatterbox is to give the members that have logged in the opportunity to send short messages to each other. (Just like the perlmonks chatterbox). As I started to implant this to my community i ran into a problem:

Witch is the wisest way to store the messages in my DB?(MySQL v3.23.31)

I thought of just having a table for all messages with columns that hold the sender, receiver and the message. But then I realized that if the user where to delete a message the messages need some kind of unique id. I thought of just using the AUTO_INCREMENT option on my message id column, but since MySQL doesn't reuse numbers and these messages will be sent and deleted pretty often I would at some point run out of numbers. I know that I can use a really big INT to store the ID, but it still doesn't feel right.

Then i thought of just randomizing a ID (e.g Rd42dXf2). But that would force me to scan trough the table to see if the ID that I just randomly picked is in use, and that doesn't seem like a very speedy solution.

Now, since I'm kind of stuck. Any solutions or pointers in the right directions would be really helpful.

// Martin

Replies are listed 'Best First'.
Re: Message storing problem.
by nate (Monk) on Jan 21, 2001 at 00:00 UTC
    In case you care, here's the table structure that the Everything Chatterbox system uses: 
    CREATE TABLE message (
  message_id int(11) DEFAULT '0' NOT NULL auto_increment,
  msgtext char(255) DEFAULT '' NOT NULL,
  author_user int(11) DEFAULT '0' NOT NULL,
  tstamp timestamp(14),
  for_user int(11) DEFAULT '0' NOT NULL,
  PRIMARY KEY (message_id),
  KEY for_user (for_user)
);

    [download]

    This basically breaks down to:

  • message_id -- arbitrary AUTO_INC primary key
  • msgtext -- 255 chars of communication
  • author_user -- reference to authors's unique ID
  • tstamp -- timestamp set on insertion of message, used for ordering
  • for_user -- optional field, if left blank it's considered "chat" if set, it becomes a "message" for whatever user ID it references

    For chat, your sql query looks something like: 

      my $expiration_seconds = 500;
  $dbh->prepare("SELECT * FROM message 
     WHERE for_user=0 AND unix_timestamp(now())-unix_timestamp(tstamp)
+ < $expiration_seconds ORDER BY tstamp");

    [download]

    then for viewing a user's specific messages: 

      my $UID = getUserIdSomehow();
  $dbh->prepare("SELECT * FROM message 
     WHERE for_user=$UID ORDER BY tstamp");

    [download]

    It's really a pretty simple system, but it does depend on having numeric IDs that you can look up your users with. And don't worry too much about running out of INT -- 32-bits gives you lots of room to talk.

    -nate

[reply]
[d/l]
[select]
      my $UID = getUserIdSomehow();
  $dbh->prepare("SELECT * FROM message 
     WHERE for_user=$UID ORDER BY tstamp");

      [download]

      Surely you don't prepare the query with a variable in it? You should be using placeholders: 

      my $UID = getUserIdSomehow();
my $sth = $dbh->prepare("SELECT * FROM message 
     WHERE for_user=? ORDER BY tstamp");

      [download]
      which then gets executed with $sth->execute($UID)

      Tony

[reply]
[d/l]
[select]
Re: Message storing problem.
by mp3car-2001 (Scribe) on Jan 20, 2001 at 20:48 UTC
    If you need to keep a message ID, you need them to be unique so it should be a primary key. I usually use an INT for this, since it is easiest to work with and integer comparisons and searches are the fastest. I wouldn't worry too much about running out of numbers. By the time you get through all the values of a BIGINT, posts made at the lower numbers probably won't be needed any more and you can just start over at 0. I'd also make your key unsigned so that you have twice as many available numbers without dealing with negatives.
    Good luck,
        Joe
[reply]
Re: Message storing problem.
by flocto (Pilgrim) on Jan 20, 2001 at 21:22 UTC
    I am not a database-expert, but here's a solution that might work:
    Since you already have sender and receiver in your table, you already have some kind of ID. You could use timestamps to identify messages that come from the same sender to the same receiver. That has some really cool effects: Even if there is more than one user sending messages, you're still able to print them in the right order. And you don't have to worry about deleted messages. What type of timestamp you use (EPOCH or human-readable) shouldn't really matter. The important part is the timespan they describe. And that depends on how often your users log in. If you use timestamps that identify the month that should work fine.

    I hope that could help..
    Regards, octopus
    --
    GED/CC d-- s:- a--- C++(+++) UL+++ P++++$ L++>++++ E--- W+++@ N o? K? w-- O- M-(+) V? !PS !PE !Y PGP+(++) t-- 5 X+ R+(+++) tv+(++) b++@ DI+() D+ G++ e->+++ h!++ r+(++) y+
[reply]
Re: Message storing problem.
by extremely (Priest) on Jan 21, 2001 at 03:16 UTC
    If you use a nice INT to store the message_id, that is 4_294_967_296 messages till you run out of IDs. If you use a bigger INT, like the 8 byte int, that is 18_446_744_073_709_551_616 messages.

    Let's to a little math:
    IDs/sec2**32 runout2**64 runout
    1136 years584 billion years
    1013.6 years58.4 billion years
    1001.36 years5.84 billion years
    Honestly, INT is fine for chatterbox! =)

    --
    $you = new YOU;
    honk() if $you->love(perl)

[reply]

Back to Seekers of Perl Wisdom