Data Model, Database Table, Value Object and Data Access Object
As I mentioned in my previous blog post, I have decided to start small - to develop functionality around player information management. At this point, a sufficient definition of a player would/could/should be something like:
"A player is a registered, identifiable user, who is playing the game. Identification is based on a username, authentication is based on a password. In addition, a player starts playing the game at a certain date and time and his initial origin in the game world is indicated by a set of x and y coordinates."
The following software development steps are very common in order to develop a working implementation:
- Design/develop data model
- Design database
- Implement database
- Implement value objects (VOs)
- Implement data access objects (DAOs)
- Implement tests
The first models that I typically document in electronic format are simplified ER models. All my previous versions are either pencil drawings or whiteboard markings. More of them if it is a group effort, less if I'm going solo.
Step 1) The data model
Based on our above definition of a player, we could come up with the following minimalistic ER model:
We have only one entity (Player) with a few attributes. This satisfies our player definition. Later on, when adding more requirements and functionality to the game, our data model will grow.
Step 2) Database design
Our database requires only one table for storing player information. I personally think that it is a good idea to add a surrogate primary key (number) to each entity. Let this be the Player ID ("id" for short). We simplify our start location by dividing it into two columns, startx (number) and starty (number). Let us allow only one player to start at any one location. The (user)name (string) must be unique. The password will be hashed with MD5 (string).
Step 3) Database implementation
Since we're using MySQL, we choose to write MySQL SQL dialect creation statements:
create table player ( id bigint not null primary key auto_increment, startdate datetime not null, startx int not null, starty int not null, name varchar(255) not null, password varchar(255) not null ); alter table player add unique index playerlocation (startx,starty); alter table player add unique index playername (name);
Step 4) Value object (VO) implementation
When it comes to PHP, I have mixed feeling about using data holder classes/beans/value objects or whatever you want to call them. In PHP, it seems that the only "natural" way to pass and hold data is a map (e.g. $_GET). Should my value objects actually be plain maps, which instead of containing attributes, would contain variables and variable values? I will monitor and analyze this while we work on other components. For now, let us create a value object for single player data. At least when using value objects, we are able to add some functionality into it, although we should keep it simple.
Player.php
class Player {
public $id = 0;
public $startDate;
public $startX;
public $startY;
public $name;
public $password;
private static $stmts = array();
function __construct() {
if (DEBUG) Logging::log("Player.__construct");
$this->startDate = time();
}
function __toString() {
return $this->id . "/" . $this->name;
}
function setNewPassword($pPassword) {
if (DEBUG) Logging::log("Player.setNewPassword $pPassword");
$this->password = md5($pPassword);
}
function verifyPassword($pPassword) {
if (DEBUG) Logging::log("Player.verifyPassword $pPassword");
if ($this->password == md5($pPassword)) {
return true;
} else {
return false;
}
}
function populate($data) {
if (DEBUG) Logging::log("PlayerDAO.populate $data");
$this->id = $data["id"];
$this->name = $data["name"];
$this->password = $data["password"];
$this->startX = $data["startx"];
$this->startY = $data["starty"];
$this->startDate = strtotime($data["startdate"]);
}
}
Notice the following convenience functions:
- setNewPassword: uses MD5 to hash player password
- verifyPassword: uses MD5 to verify given password with stored one
- populate: populates this VO from given map
Step 5) Data Access Object (DAO) implementation
We want to separate our Value Object from our persistance storage. Why? Well, at the moment we are going to use MySQL to persist our players. But who knows about tomorrow. Maybe some day we will use the file system or some other media. This way we do not need to touch our VO at all. Also, providing a clean Value Object is a simple and nice interface towards our controller and view as well. Therefore, let's keep our VO simple and implement all persistance functionality in separate DAO objects.
PlayerDAO.php:
class PlayerDAO {
private static $stmts = NULL;
// Static class
private function __construct() {}
private function __clone() {}
public static function remove(array $pIds) {
$ids = implode(",", $pIds);
$totalCnt = NULL;
try {
$dbc = getDBConnection();
// Something wrong with MySQL transactions. Let's not use them..
//$dbc->beginTransaction();
// Something wrong with the returned count on MySQL
$cnt = $dbc->exec("delete from player where id in ($ids)");
//if ($cnt == count($pIds)) {
//$dbc->commit();
$dbc = NULL;
return NULL;
/*} else {
//$dbc->rollback;
$dbc = NULL;
return "FAILED_TO_REMOVE_ALL";
}*/
} catch (Exception $e) {
$dbc = NULL;
return "REMOVE_ERROR";
}
}
public static function getById($pId) {
$p = NULL;
$dbc = NULL;
try {
$dbc = getDBConnection();
$stmt = $dbc->query("select * from player where id=$pId");
$row = $stmt->fetch();
if ($row) {
$p = new Player();
$p->populate($row);
}
} catch (Exception $e) {
}
$dbc = NULL;
return $p;
}
public static function getByName($pName) {
$p = NULL;
$dbc = NULL;
try {
$dbc = getDBConnection();
$stmt = $dbc->query("select * from player where name='$pName'");
$row = $stmt->fetch();
if ($row) {
$p = new Player();
$p->populate($row);
}
} catch (Exception $e) {
}
$dbc = NULL;
return $p;
}
public static function getAll() {
$players = NULL;
$dbc = NULL;
try {
$dbc = getDBConnection();
$rows = $dbc->query("select * from player order by name");
if ($rows) {
$players = array();
foreach($rows as $row) {
$p = new Player();
$p->populate($row);
$players[$p->name] = $p;
}
}
} catch (Exception $e) {
}
$dbc = NULL;
return $players;
}
public static function insert(&$p) {
$dbc = NULL;
try {
$dbc = getDBConnection();
$startDate = date('Y-m-d H:i:s', $p->startDate);
$dbc->beginTransaction();
$cnt = $dbc->exec("insert into player (startDate,startX,startY,name,password) values ('$startDate',$p->startX,$p->startY,'$p->name','$p->password')");
$stmt = $dbc->query("select max(id) from player");
$row = $stmt->fetch();
if ($row) {
$dbc->commit();
$p->id = $row[0];
$dbc = NULL;
return NULL;
} else {
$dbc->rollback();
$dbc = NULL;
return "UNABLE_TO_FETCH_SURROGATE";
}
} catch (Exception $e) {
$dbc = NULL;
return "INSERT_ERROR";
}
}
public static function update(&$p) {
$dbc = NULL;
try {
$dbc = getDBConnection();
$startDate = date('Y-m-d H:i:s', $p->startDate);
$cnt = $dbc->exec("update player set startDate='$startDate',startX=$p->startX,startY=$p->startY,name='$p->name',password='$p->password' where id=$p->id");
$dbc = NULL;
return NULL;
} catch (Exception $e) {
$dbc = NULL;
return "UPDATE_ERROR";
}
}
}
The function getDBConnection is just a confenience function, which provides a DB connection (nicely named, congratulations!)
As you can see, only the basic operations (select all, select one, insert, update and delete) are implemented. Later on we can expand this class to include e.g. fetching of paginating data. Also, some operations should return more detailed reasons of why they failed, e.g. INSERT_ERROR_DUPLICATE.
Step 6) Testing
Implementing our data model separately like this allows us to do extensive unit testing on each entity. Having extensive tests allows us to find bugs from both the actual VO class as well as the helper DAO class. It is up to you to create enough test cases, the test set below is just an example.
It is a good idea to revert the database as close to it's original state as possible in order to be able to run these test again and again (in case you change something in the VO or DAO). Also, I guess if you put a loop and a timer around your test cases, you could actually compare the performance of your different DAO implementation, in case you have several and wonder which persistance storage to use.
print("\nTC Create Player 1\n");
try {
$p = new Player();
$p->name = "daotest";
$p->setNewPassword("daotest");
$p->startX = 1;
$p->startY = 2;
if (PlayerDAO::insert($p)) {
print($p->__toString() . "\n");
print("**OK**\n");
} else {
print("**FAIL**\n");
}
$p = NULL;
} catch (Exception $e) {
print("**FAIL**: " . $e . "\n");
}
print("\nTC Create Player 2\n");
try {
$p2 = new Player();
$p2->name = "daotest2";
$p2->setNewPassword("daotest2");
$p2->startX = 1;
$p2->startY = 2;
if (PlayerDAO::insert($p2)) {
print($p2->__toString() . "\n");
print("**OK**\n");
} else {
print("**FAIL**\n");
}
} catch (Exception $e) {
print("**FAIL**: " . $e . "\n");
}
print("\nTC Create Player - duplicate \n");
try {
$p = new Player();
$p->name = "daotest";
$p->setNewPassword("daotest");
$p->startX = 1;
$p->startY = 2;
if (PlayerDAO::insert($p)) {
print($p->__toString() . "\n");
print("**FAIL**\n");
} else {
print("**OK**\n");
}
$p = NULL;
} catch (Exception $e) {
print("**OK**\n");
}
print("\nTC Get Player By Name \n");
$p = PlayerDAO::getByName("daotest");
if ($p == NULL) {
print("**FAIL**\n");
} else {
print($p->__toString() . "\n");
print("**OK**\n");
}
print("\nTC Modify Player\n");
if ($p != NULL) {
try {
$p->name = "daotest3";
$p->setNewPassword("daotest3");
$p->startX = 3;
$p->startY = 4;
PlayerDAO::update($p);
$p3 = PlayerDAO::getById($p->id);
print($p3->__toString() . "\n");
print("**OK**\n");
} catch (Exception $e) {
print($e);
print("**FAIL** Update failed\n");
}
} else {
print("**FAIL** No user instance\n");
}
print("\nTC Get Players\n");
try {
$ps = PlayerDAO::getAll();
if ($ps != NULL) {
foreach($ps as $p4) {
print($p4->__toString() . "\n");
}
print("**OK**\n");
} else {
print("**FAIL**: No results!\n");
}
} catch (Exception $e) {
print("**FAIL**: " . $e . "\n");
}
print("\nTC Remove Players\n");
try {
$cnt = PlayerDAO::remove(array($p->id, $p2->id));
if ($cnt == 2) {
print("**OK**\n");
} else {
print("**FAIL**: Removed $cnt\n");
}
} catch (Exception $e) {
print("**FAIL**: " . $e . "\n");
}
Happy coding!!
Let the discussion continue in the forum!
