UnfoX Posted September 10, 2009 Share Posted 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 Quote Link to comment Share on other sites More sharing options...
Philip Posted September 11, 2009 Share Posted 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 Quote Link to comment Share on other sites More sharing options...
UnfoX Posted September 11, 2009 Author Share Posted 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? Quote Link to comment Share on other sites More sharing options...
448191 Posted September 12, 2009 Share Posted 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] Quote Link to comment Share on other sites More sharing options...
448191 Posted September 12, 2009 Share Posted 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. Quote Link to comment Share on other sites More sharing options...
448191 Posted September 12, 2009 Share Posted 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. Quote Link to comment Share on other sites More sharing options...
448191 Posted September 12, 2009 Share Posted 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 */; Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.