Private Messaging Structure and Usage

UnfoX · September 10, 2009

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

Philip · September 11, 2009

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

UnfoX · September 11, 2009

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?

3.5k · September 12, 2009

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]

3.5k · September 12, 2009

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.

3.5k · September 12, 2009

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

<?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.

3.5k · September 12, 2009

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 */;

Sign In

Private Messaging Structure and Usage

Recommended Posts

UnfoX

Link to comment

Share on other sites

Philip

Link to comment

Share on other sites

UnfoX

Link to comment

Share on other sites

448191

Link to comment

Share on other sites

448191

Link to comment

Share on other sites

448191

Link to comment

Share on other sites

448191

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information