Jump to content

Private Messaging Structure and Usage


UnfoX

Recommended Posts

Hi all,

 

I'm after a complete and practical private messaging system which has a thread feature. There are some topics in this forum about pm systems but none of them satisfied me enough and they are only related to the database structure. I want to talk about usage also (SQL queries etc.).

 

The best database design I can find is from this forum (http://www.phpfreaks.com/forums/index.php/topic,262543.msg1237675.html#msg1237675). It has 2 seperate tables. One for private message itself, and second for its relations to the receiver etc.

 

1st table:

CREATE TABLE IF NOT EXISTS `pm` (

  `pmID` int(11) NOT NULL auto_increment,

  `parentID` int(11) NOT NULL default '0',

  `senderID` int(11) NOT NULL,

  `title` varchar(100) default NULL,

  `content` text,

  `dateSent` datetime default NULL,

  `senderDelete` tinyint(4) NOT NULL,

  PRIMARY KEY  (`pmID`)

) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

 

2nd table:

CREATE TABLE IF NOT EXISTS `pmInfo` (

  `messageID` int(11) NOT NULL auto_increment,

  `receiverID` int(11) NOT NULL,

  `receiverRead` tinyint(4) NOT NULL,

  `dateRead` datetime default NULL,

  `receiverDelete` tinyint(4) NOT NULL,

  PRIMARY KEY  (`messageID`)

) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

 

Are we all clear on this database structure? If not, please suggest.

 

Lets say that we all agreed on this db structure. Now what about the usage?

As I know, best practice would be minimizing the SQL queries ran.

 

Here we go :)

 

POSTING MESSAGE

There are two types of posting messages. 1- New thread, 2- Reply

 

When a new message is sent I call 3 SQL queries:

1- INSERT query to pm table

2- INSERT query to pmInfo table

3- UPDATE query to make parentID = pmID

 

When a new reply is sent I call 2 SQL queries:

1- INSERT query to pm table

2- INSERT query to pmInfo table

 

In the PHP side, for a new thread there is no problem. User selects receiver (so receiverID), and parentID is set to pmID. But for the reply, I have some problems.

In the reply form I'm using a hidden input which holds the parentID for that thread. But with this usage, how can I get the receiverID? If I use hidden parentID to get receiverID I need one more SELECT query (which is unwanted) and I may not find the correct receiverID since I may send the main thread to multi-users.

 

Lets have a table example:

USERS TABLE

userID    username

1          Joe

2          Paul

3          Nick

 

 

PM TABLE

pmID    parentID    senderID    title        content

1      1            1        Hi!          Whats up?    -> pm(1)

2      2            3        Hello        Hi there..    -> pm(2)

3      1            2        null        Fine, you?    -> this is a reply to pm(1)

4      1            1        null        Fine          -> this is a reply to the reply above

5      1            2        null        Thanks dude  -> this is a reply to pm(1)

 

 

 

 

PMINFO TABLE

messageID      receiverID

1              3            -> message sent by Joe to Nick

1              2            -> same message sent to Paul also

2              2            -> a message sent by Nick to Paul

3              1            -> reply sent by Nick to Joe

4              3            -> reply sent by Joe to Nick

5              1            -> reply sent by Paul to Joe

 

When Joe replies to Nick with the pmID 4, how can I get the Nick's userID from the table? If I select with respect to parentID, Joe sent the same message to Paul also, it can be confused.

Since I'm listing a thread using the parentID in a page, I have no info about the next receiverID.

 

This is the first question, I wish it is clear enough for you :) Do you have any suggestions for this issue?

 

And all other suggestions to any piece of this topic are welcomed

 

Thanks

Link to comment
Share on other sites

tinyint(4)

Should be tinyint(1) [basically making them boolean

 

 PRIMARY KEY  (`messageID`)

Should be INDEX (`messageID`)

 

`receiverRead`/ `receiverDelete` should have default values (or null) of 0

 

When a new message is sent I call 3 SQL queries:

1- INSERT query to pm table

2- INSERT query to pmInfo table

3- UPDATE query to make parentID = pmID

 

Instead of making that 3rd query (the update), use 0/NULL as a parentID instead. That'll save a query, and you can quickly see what messages are parents and what aren't. Plus, that's why I made that a 0 as a default on that column ;)

 

 

Link to comment
Share on other sites

KingPhilip, thank you for your reply :) I made the necessery changes in my database. But now I have a question.

 

When listing the threads, lets say that I'm the user Nick and I'm using such a query:

SELECT * FROM `pm` INNER JOIN pmInfo ON pm.pmID = pmInfo.messageID WHERE receiverID = 3 ORDER BY dateSent DESC

 

After this query I will have 2 rows:

pmID    parentID    senderID    title        content          messageID      receiverID
4       1             1         null         Fine             4              3           
1       0             1         Hi!          Whats up?        1              3         

 

Since I'm listing the threads, I need to show the last message of the thread. So I need to get rid of the second row.

  • I can not use LIMIT 1 since there may be other threads. LIMIT returns always 1 message
  • I can not use parentID <> 0 since the thread may be unreplied. In this case I need to show the parent message

 

Any suggestions? :)

Link to comment
Share on other sites

New message, from Joe to Paul and Nick:

 

INSERT INTO message (id, text, author_id) VALUES (1, 'Hi there', 1);

INSERT INTO inbox (id, message_id, user_id) VALUES (1, 1, 2),  (1, 1, 3);

 

Paul replies privately:

 

INSERT INTO message (id, text, author_id) VALUES (2, 'Hi. I don't like Nick very much.', 2);

INSERT INTO inbox (id, parent_message_id, message_id, user_id) VALUES (2, 1, 2, 1);

 

Nick replies to the thread:

 

INSERT INTO message (id, text, author_id) VALUES (3, 'Hey! I see Paul is in this thread as well! Hey Paul, you rule!', 3);

INSERT INTO inbox (id, parent_message_id, message_id, user_id) VALUES (3, 1, 3, 1),  (3, 1, 3, 2);

 

Pretty basic stuff.. The PK values are just for illustration. Normally you would use an AUTO_INCREMENT column.

 

[attachment deleted by admin]

Link to comment
Share on other sites

For your convenience..

 

-- -----------------------------------------------------
-- Table `mydb`.`user`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`user` (
  `id` INT NOT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`message`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`message` (
  `id` INT NOT NULL ,
  `text` TEXT NULL ,
  `author_id` INT NOT NULL ,
  PRIMARY KEY (`id`) ,
  INDEX `fk_message_user1` (`author_id` ASC) ,
  CONSTRAINT `fk_message_user1`
    FOREIGN KEY (`author_id` )
    REFERENCES `mydb`.`user` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`inbox`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`inbox` (
  `id` INT NOT NULL ,
  `message_id` INT NOT NULL ,
  `parent_message_id` INT NOT NULL ,
  `user_id` INT NOT NULL ,
  PRIMARY KEY (`id`) ,
  INDEX `fk_inbox_message1` (`message_id` ASC) ,
  INDEX `fk_inbox_message2` (`parent_message_id` ASC) ,
  INDEX `fk_inbox_user1` (`user_id` ASC) ,
  CONSTRAINT `fk_inbox_message1`
    FOREIGN KEY (`message_id` )
    REFERENCES `mydb`.`message` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_inbox_message2`
    FOREIGN KEY (`parent_message_id` )
    REFERENCES `mydb`.`message` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_inbox_user1`
    FOREIGN KEY (`user_id` )
    REFERENCES `mydb`.`user` (`id` )
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

 

Since I'm listing the threads, I need to show the last message of the thread. So I need to get rid of the second row.

 

    * I can not use LIMIT 1 since there may be other threads. LIMIT returns always 1 message

    * I can not use parentID <> 0 since the thread may be unreplied. In this case I need to show the parent message

 

 

Any suggestions? :)

 

WHERE parent_message_id IS NULL will work just fine for selecting the OPs. A message without parent is a root message, i.e. a thread OP.

Link to comment
Share on other sites

Hmokay, so I thought I'd write up a quick example, and ended up spending a couple of hours writing half an ORM :P

 

<?php
class Pm_Exception extends Exception {}

class Pm_DataMapper_Statement
{
private $_literal;

public function __construct($literal)
{
	$this->_literal = $literal;
}	

public function __toString()
{
	return (string)$this->_literal;
}
}

class Pm_DataMapper
{
/**
 * @var PDO
 */
private $_pdo;

private $_identityMap = array();

private $_map = array(
	'Pm_User' => array(
		'table' => 'user',
		'properties' => array(
			'_name' => array(
				'type' => PDO::PARAM_STR,
				'column' => 'name'
			)
		)
	),
	'Pm_Message' => array(
		'table' => 'message',
		'properties' => array(
			'_message' => array(
				'type' => PDO::PARAM_STR,
				'column' => 'text'
			),
			'_author' => array(
				'class' => 'Pm_User',
				'type' => PDO::PARAM_INT,
				'column' => 'author_id'
			)
		)
	),
	'Pm_InboxMessage' => array(
		'table' => 'inbox',
		'properties' => array(
			'_message' => array(
				'class' => 'Pm_Message',
				'type' => PDO::PARAM_INT,
				'column' => 'message_id'
			),
			'_parentMessage' => array(
				'class' => 'Pm_Message',
				'type' => PDO::PARAM_INT,
				'column' => 'parent_message_id'
			),
			'_user' => array(
				'class' => 'Pm_User',
				'type' => PDO::PARAM_INT,
				'column' => 'user_id'
			)
		)
	)
);

public function __construct(PDO $dbh)
{
	$this->_pdo = $dbh;	
}

private function _get($class, $id)
{
	return isset($this->_identityMap[$class][$id]) ? $this->_identityMap[$class][$id] : null;
}

private function _set(Pm_DomainObject $object)
{
	$this->_identityMap[get_class($object)][$object->getId()] = $object;
}

private function _getColumns($class)
{
	$spec = $this->_map[$class];

	$columns = array();

	foreach($spec['properties'] as $propSpec)
	{
		$columns[$propSpec['column']] = ":{$propSpec['column']}";
	}

	return $columns;
}

public function factory($class, array $record)
{
	$spec = $this->_map[$class];

	$refl = new ReflectionClass($class);

	$values = array();

	foreach($refl->getMethod('__construct')->getParameters() as $reflParameter)
	{
		$propertySpec = $spec['properties']["_{$reflParameter->getName()}"];

		$value = $record[$propertySpec['column']];

		if(isset($propertySpec['class']) && $value)
		{
			$value = $this->findOneBy($propertySpec['class'], 'id', $value);

			if(!$value)
			{
				throw new Pm_Exception("{$propertySpec['class']}:{$record[$propertySpec['column']]} not found");
			}

		}

		$values[$reflParameter->getPosition()] = $value;
	}

	ksort($values);

	$object = $refl->newInstanceArgs($values);

	$object->setId($record['id']);

	return $object;
}

public function find($class, $id)
{
	return $this->findOneBy($class, 'id', $id);
}

public function findOneBy($class, $argumentName, $propertyValue)
{
	$results = $this->findBy($class, $argumentName, $propertyValue);

	return reset($results);
}

public function findBy($class, $argumentName, $propertyValue)
{
	if($argumentName == 'id' && $this->_get($class, $propertyValue))
	{
		return $this->_get($class, $propertyValue);
	}

	$spec = $this->_map[$class];

	if($propertyValue instanceof Pm_DataMapper_Statement)
	{
		$stmt = $this->_pdo->prepare(
			"SELECT id, $propertyValue AS __statement, " . implode(', ', array_keys($this->_getColumns($class))) . " FROM {$spec['table']} WHERE $argumentName = $propertyValue"
		);

		if(!$stmt->execute())
		{
			throw new Pm_Exception("Execute failed: " . implode(', ', $this->_pdo->errorInfo()));
		}

		return $this->hydrate($class, $stmt);
	}

	if($propertyValue === null)
	{
		$conditionValueString = "IS NULL";
	}
	else
	{
		$conditionValueString = "= :$argumentName";
	}

	$column = isset($spec['properties']["_$argumentName"]['column']) ? $spec['properties']["_$argumentName"]['column'] : $argumentName;

	$stmt = $this->_pdo->prepare(
		"SELECT id, " . implode(', ', array_keys($this->_getColumns($class))) . " FROM {$spec['table']} WHERE $column $conditionValueString"
	);

	$stmt->bindParam(":$argumentName", $propertyValue, $spec['properties']["_$argumentName"]['type']);

	if(!$stmt->execute())
	{
		throw new Pm_Exception("Execute failed: " . implode(', ', $this->_pdo->errorInfo()));
	}

	return $this->hydrate($class, $stmt);
}

public function hydrate($class, PDOStatement $stmt)
{
	$result = array();

	foreach($stmt->fetchAll(PDO::FETCH_ASSOC) as $record)
	{
		if($this->_get($class, $record['id']))
		{
			$result[] = $this->_get($class, $record['id']);

			continue;
		}

		$result[] = $this->factory($class, $record);
	}

	return $result;
}

public function save(Pm_DomainObject $object)
{
	$spec = $this->_map[get_class($object)];

	$props = array();

	foreach((array)$object as $prop => $value)
	{
		$props[substr($prop, strrpos($prop, '_'))] = $value;
	}

	$values = array_intersect_key($props, $spec['properties']);

	foreach($spec['properties'] as $propName => $propSpec)
	{
		if($values[$propName] instanceof Pm_DomainObject)
		{
			if(!$values[$propName]->isStored())
			{
				$this->save($values[$propName]);
			}

			$values[$propName] = $values[$propName]->getId();
		}
	}

	$columns = $this->_getColumns(get_class($object));

	if(!$object->isStored())
	{
		$stmt = $this->_pdo->prepare("INSERT INTO {$spec['table']} (" . implode(', ', array_keys($columns)) . ") VALUES (" . implode(', ', $columns) . ")");

		foreach($spec['properties'] as $propName => $propSpec)
		{
			$stmt->bindParam(":{$propSpec['column']}", $values[$propName], $propSpec['type']);
		}

		if(!$stmt->execute())
		{
			throw new Pm_Exception("Execute failed ({$this->_pdo->errorCode()}): " . implode(', ', $this->_pdo->errorInfo()));
		}

		$object->setId((int)$this->_pdo->lastInsertId());	
	}
	else 
	{
		$sets = array();

		foreach($columns as $name => $mark)
		{
			$sets[] = "$name = $mark";
		}

		$stmt = $this->_pdo->prepare("UPDATE {$spec['table']} SET " . implode(', ', $sets) . " WHERE id = " . (int)$object->getId());

		foreach($spec['properties'] as $propName => $propSpec)
		{
			$stmt->bindParam(":{$propSpec['column']}", $values[$propName], $propSpec['type']);
		}

		if(!$stmt->execute())
		{
			throw new Pm_Exception("Execute failed: " . implode(', ', $this->_pdo->errorInfo()));
		}
	}
}
}
abstract class Pm_DomainObject
{
/**
 * @var int
 */
private $_id;

public function getId()
{
	return $this->_id;
}

public function isStored()
{
	return (bool)$this->_id;
}

public function setId($id)
{
	if($this->_id)
	{
		throw new Pm_Exception("ID already set");
	}

	$this->_id = $id;
}
}

class Pm_User extends Pm_DomainObject
{
/**
 * @var string
 */
private $_name;	

public function __construct($name)
{
	$this->_name = $name;
}
}
class Pm_Message extends Pm_DomainObject
{
/**
 * @var string
 */
private $_message;

/**
 * @var User
 */
private $_author;


public function __construct($message, Pm_User $author)
{
	$this->_author = $author;

	$this->_message = $message;
}
}
class Pm_InboxMessage extends Pm_DomainObject
{
/**
 * @var Pm_Message
 */
private $_message;

/**
 * @var Pm_Message
 */
private $_parentMessage;

/**
 * @var User
 */
private $_user;


public function __construct(Pm_Message $message, Pm_Message $parentMessage = null, Pm_User $user)
{
	$this->_user = $user;

	$this->_message = $message;

	$this->_parentMessage = $parentMessage;
}
}
class Pm_Service
{
/**
 * @var Pm_DataMapper
 */
private $_mapper;

public function __construct(Pm_DataMapper $mapper)
{
	$this->_mapper = $mapper;
}

public function send(Pm_Message $message, array $users, Pm_Message $parentMessage = null)
{
	foreach($users as $user)
	{
		$inboxMessage = new Pm_InboxMessage($message, $parentMessage, $user);

		$this->_mapper->save($inboxMessage);

		if(!$inboxMessage->getId())
		{
			return false;
		}
	}

	return true;

}
}

/**
* Use case
*/
$mapper = new Pm_DataMapper(new Pdo('mysql:dbname=pm;host=127.0.0.1;port=3306', 'root', '40333'));

$service = new Pm_Service($mapper);

$joe = ($user = $mapper->findOneBy('Pm_User', 'name', 'Joe')) ? $user : new Pm_User('Joe');
$paul = ($user = $mapper->findOneBy('Pm_User', 'name', 'Paul')) ? $user : new Pm_User('Paul');
$nick = ($user = $mapper->findOneBy('Pm_User', 'name', 'Nick')) ? $user : new Pm_User('Nick');

/**
* Joe starts conversation
*/
$op = new Pm_Message('Hi there', $joe);

/**
* Sends it to both Paul and Nick
*/
$service->send($op, array($paul, $nick));

/**
* When Paul finds the message, he responds to Joe, but not to Nick
*/
$service->send(new Pm_Message('Hi. I don\'t like Nick very much.', $paul), array($joe), $op);

/**
* But Nick thinks Paul is awesome, so he responds to both
*/
$service->send(new Pm_Message('Hey! I see Paul is in this thread as well! Hey Paul, you rule!', $nick), array($joe, $paul), $op);

/**
* If we want the ops in a mailbox we look for NULL values
*/
echo '<pre>'; 
var_dump($mapper->findBy('Pm_InboxMessage', 'parentMessage', null));
echo '</pre>'; 

 

Since I'm listing the threads, I need to show the last message of the thread. So I need to get rid of the second row.

 

    * I can not use LIMIT 1 since there may be other threads. LIMIT returns always 1 message

    * I can not use parentID <> 0 since the thread may be unreplied. In this case I need to show the parent message

 

 

Any suggestions?

 

I'm sorry, I misread this (I read what I want to read sometimes). I don't think there is a way with the adjacency list model (the parent_id thing) to do this, but it is trivial with nested set: WHERE 0 = (right – left - 1) / 2. Nested Set (Modified Preorder Tree Traversal) is a little more work to implement though.

Link to comment
Share on other sites

Oh and here's an updated schema:

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

CREATE TABLE  `inbox` (
  `id` int(11) NOT NULL auto_increment,
  `message_id` int(11) NOT NULL,
  `parent_message_id` int(11) default NULL,
  `user_id` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `fk_inbox_message1` (`message_id`),
  KEY `fk_inbox_message2` (`parent_message_id`),
  KEY `fk_inbox_user1` (`user_id`),
  CONSTRAINT `fk_inbox_message1` FOREIGN KEY (`message_id`) REFERENCES `message` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_inbox_message2` FOREIGN KEY (`parent_message_id`) REFERENCES `message` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_inbox_user1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE  `message` (
  `id` int(11) NOT NULL auto_increment,
  `text` text NOT NULL,
  `author_id` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `fk_message_user1` (`author_id`),
  CONSTRAINT `fk_message_user1` FOREIGN KEY (`author_id`) REFERENCES `user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE  `user` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.