CRUD operation using Object relational model and Data Mapper pattern for a Matrimonial website

Question

Database.php

This creates a connection and executes a query. Although, I think I should make it a static/singleton class.

class Database extends PDO
{
    /**
     * constant DSN contains database driver, host and database name.
     */
    const DSN = 'mysql:host=localhost;dbname=matrimonial';

    /**
     * @var string $userName username of database.
     * @var string $passWord password of database.
     */
    private $userName;
    private $passWord;

    /**
     * @var object $dbHandler An object of PDO class contain connection
     * @var object $stHandler An object of PDOStatement class
     */
    private $dbHandler;
    private $stHandler;

    /**
     * @var boolean $connected Contain true if connection is OK otherwise false
     */
    private $connected = FALSE;

    /**
     * @var string $query contain the sql query to be performed
     */
    private $query;

    /**
     * @var mixed[]|null contain the result set returned from database or nothing/null
     */
    private $result;

    /**
     * @var array contain error code, message etc stored by $e->getMessage(); statement
     */
    private $error = array();

    /**
     * Constructor of class.
     *
     * It create a PDO (ie connection to database) object and store it to $db_handler
     * set attributes to throw exception, create a PDOStatement object $st_handler
     * and assign true or false to $connected according to established connection
     *
     * @param string $username username of database
     * @param string $password
     */
    function __construct($username = "root", $password = '')
    {
        $this->userName = $username;
        $this->passWord = $password;

        $opt = array( PDO::ATTR_PERSISTENT=>TRUE );
        try {
            $this->dbHandler = new PDO(self::DSN, $this->userName, $this->passWord, $opt);
            $this->dbHandler->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        } catch (PDOException $e) {
            $this->error = $e->getMessage();
        }

        $this->connected = TRUE;
        $this->stHandler = new PDOStatement();
    }

    /**
     * Execute the query and return the result.
     *
     * First of all prepare the given query. Query must have placeholders. Next we execute the query
     * using the $value argument which contain elements equal to placeholder in same order. Result is
     * returned based on query type if query is 'select' type then return array fetchAll otherwise
     * return number of row effected.
     *
     * @param string $query    the query to be executed
     * @param array $values    array of values, must be associative if named placeholder
     * @param mixed $fetchMode data fetch mode, default is FETCH_ASSOC
     * @return mixed
     */
    public function prepareAndExecute($query, $values = array(), $fetchMode = PDO::FETCH_ASSOC)
    {
        $this->query = $query;
        try {
            $this->stHandler = $this->dbHandler->prepare($this->query);
            $this->stHandler->execute($values);

            if (preg_match("/^select/i", $this->query)) {
                $this->result = $this->stHandler->fetchAll($fetchMode);
            } else {
                $this->result = $this->stHandler->rowCount();
            }

        } catch(PDOException $e) {
            $this->error = $e->errorInfo;
            return false;
        }
        return $this->result;
    }

    /**
     * id of last row inserted
     * @param string $name
     * @return string last inserted id if any, otherwise return false(for select statement)
     */
    public function lastInsertId($name = null)
    {
        return $this->dbHandler->lastInsertId();
    }

    /**
     * @return array return the error if registered during operation
     */
    public function getError()
    {
        return $this->error;
    }

    /**
     * A quick and dirty function to delete rows
     * @param string $query delete query to perform
     * @return mixed return numbers of row deleted, 0 if no row deleted
     */
    public function exec($query)
    {
        return $this->dbHandler->exec($query);
    }
}

MatEducation.php

class MatEducation
{
    /**
     * @var int $id id of the user/family member
     * @var int $eduId
     * @var string $degree
     * @var string $institute information of user
     * @var int $type mapper type - User/Family member
     */
    private $id;
    private $eduId;
    private $degree;
    private $institute;

    /**
     * getter and setter methods for properties
     * **************removed to make code shorter
     */

    /**
     * Convert array into object
     * @param array $arr
     * @return MatEducation object
     */
    public function arrayToObject($arr){
        $obj = new MatEducation();
        $obj->id = $this->getId();
        $obj->eduId = $arr['edu_id'];
        $obj->degree = $arr['edu_degree'];
        $obj->institute = $arr['edu_institute'];
        return $obj;
    }
}

CommonMapper.php

I think I should make some abstract methods like add, delete, and insert. But since the arguments for them are objects of different classes for different implementations, I couldn't find a way.

abstract class CommonMapper
{
    /**
     * @var array contain error code, message etc
     */
    private $error = array();

    /**
     * @var Database class object. Contain PDO connection.
     */
    private $dbObj;

    /**
     * constructor initialize $dbObj with connection.
     */
    public function __construct($dbObj)
    {
        $this->dbObj = $dbObj;
    }

    /**
     * runQuery function
     * This function is for select, delete query.
     * @param string $query
     * @param array $values
     * @return mixed For select - return in array containing rows
     *               For delete - return number of affected rows
     *                            return false in case of empty result
     */
    protected function runQuery($query, $values)
    {
        $result =  $this->dbObj->prepareAndExecute($query, $values);

        if (!$result && !empty($this->dbObj->getError())) {
            $this->error = $this->dbObj->getError();
        }
        return $result;
    }

    /**
     * runNonQuery function
     * This function is for insert query.
     * @param string $query
     * @param array $values
     * @return mixed For select - return last insert id
     *                            return false in case of empty result
     */
    protected function runNonQuery($query, $values)
    {
        $result =  $this->dbObj->prepareAndExecute($query, $values);

        if (!$result && !empty($this->dbObj->getError())) {
            $this->error = $this->dbObj->getError();
        }
        return $this->dbObj->lastInsertId();
    }

    /**
     * @return array return the error if registered during operation
     */
    public function getError()
    {
        return $this->error;
    }
}

UserEducationMapper.php

There are more classes with almost the same functionality, like UserJobMapper, MemberEducationMapper, MemberJobMapper etc.

class UserEducationMapper extends CommonMapper
{
    /**
     * @param Database $dbObj Object of Database class to perform CRUD operation
     */
    public function  __construct(Database $dbObj)
    {
        parent::__construct($dbObj);
    }

    /**
     * addEducation function
     * First add column in mat_education then add a column in mat_user_education which
     * is relation between mat_education and mat_user
     * @param MatEducation $education object of MatEducation class
     * @return boolean true if education inserted else false
     */
    public function addEducation(MatEducation $education)
    {
        $set = "";
        $values = array();
        if ($education->getDegree()) {
            $degree = $education->getDegree();
            $set .= ":degree, ";
            $values['degree'] = $degree;
        }
        if ($education->getInstitute()) {
            $institute = $education->getInstitute();
            $set .= ":institute, ";
            $values['institute'] = $institute;
        }

        $set=rtrim($set,", ");
        $query = "insert into mat_education (edu_degree, edu_institute) values($set)";

        // last insert id. we need it to add row in Mat_user_education
        $eid = parent::runNonQuery($query, $values);

        $values = array("uid"=>$education->getId(), "eid"=>$eid);
        $query = "insert into mat_user_education (user_id, edu_id) values (:uid, :eid)";

        return parent::runNonQuery($query, $values);
    }

    /**
     * @param MatEducation $education object of MatEducationMapper class having userId already set
     * @return boolean true if information is retrieved else false
     */
    public function getEducation(MatEducation $education)
    {
        $columns = "mat_education.edu_id, mat_education.edu_degree, mat_education.edu_institute";
        $id = $education->getId();

        $values = array("id"=>$id);
        $query = "SELECT $columns FROM mat_user_education
                  inner join mat_user on mat_user.user_id = mat_user_education.user_id
                  left join mat_education on mat_education.edu_id = mat_user_education.edu_id
                  WHERE mat_user_education.user_id = :id";

        // return array of rows.
        $result = parent::runQuery($query,$values);

        // return array of MatEducation Objects.
        return $this->loadEducation($education, $result);
    }

    /**
     * @param MatEducation $education object containing userId only.
     *                                we user it to access arrayToObject method since i don't want
     *                                want to create new object using 'new' keyword
     * @param array $result array of row from mat_education table
     * @return array array of MatEducation objects
     */
    private function loadEducation(MatEducation $education, $result)
    {
        $objArray = array();
        for ($i=0; $i<count($result); $i++) {
            $objArray[$i] = $education->arrayToObject($result[$i]);
        }
        return $objArray;
    }

    /**
     * removeEducation
     * Function works in two steps first delete the mat_education row and then delete
     * mat_user_education row.
     * @param MatEducation $education object containing id to be deleted
     * @return boolean true if education removed successfully
     */
    public function removeEducation(MatEducation $education)
    {
        $eid = $education->getEduId();
        $uid = $education->getId();
        $queryRelation = "DELETE FROM mat_user_education WHERE user_id = :uid AND edu_id = :eid";
        $valuesRelation = array("uid"=>$uid, "eid"=>$eid);
        $queryEducation = "DELETE FROM mat_education WHERE edu_id = :eid";
        $valueEducation = array("eid"=>$eid);

        parent::runQuery($queryRelation, $valuesRelation);
        parent::runQuery($queryEducation, $valueEducation);
        return true;
    }
}

profile.php

    //----------- delete education details-----------------
    if (isset($_GET['eidUser']) && !empty($_GET['eidUser'])) {
        $eduId = $_GET['eidUser'];
        $userId = $_SESSION['user_id'];

        $education = new MatEducation();
        $education->setEduId($eduId);
        $education->setId($userId);

        $dbObj = new Database();
        $obj = new UserEducationMapper($dbObj);

        if ($obj->removeEducation($education)) {
            if (!empty($obj->getError())) {
                var_dump($obj->getError());
            }
        }
    }
//----------- insert education details-----------------
if (isset($_GET['degree']) || isset($_GET['institute'])) {
        $education = new MatEducation();
        if (!empty($_SESSION['user_id'])) {
            $userId = $_SESSION['user_id'];
            $education->setId($userId);
        }
        if (!empty($_GET['degree'])) {
            $degree = $_GET['degree'];
            $education->setDegree($degree);
        }
        if (!empty($_GET['institute'])) {
            $institute = $_GET['institute'];
            $education->setInstitute($institute);
        }

        $dbObj = new Database();
        $obj = new UserEducationMapper($dbObj);

        if (!$obj->addEducation($education)) {
            if (!empty($obj->getError())) {
                var_dump($obj->getError());
            }
        }
    }
//----------- retrieve education details for user----------
$education = new MatEducation();
if (!empty($_SESSION['user_id'])) {
    $userId = $_SESSION['user_id'];
    $education->setId($userId);
}
$dbObj = new Database();
$obj = new UserEducationMapper($dbObj);

if (!$userEducationArray = $obj->getEducation($education)) {
    if (!empty($obj->getError())) {
        var_dump($obj->getError());
    }
}
//--------------------------------------------
if (!empty($userEducationArray)){
                foreach ($userEducationArray as $education) {
                    echo "Degree :: " . $education->getDegree() .
                        " || Institute :: " . $education->getInstitute() .
                        "<a href='./request/user_education.php?eidUser=" . $education->getEduId() .
                        "'>Delete</a><br/>";
                }
            }

I studied some of blogs and code reviews and decided to follow proper oop techniques and concepts. I tried to follow PSR standards and some SOLID concepts (as far as I could understand them). I tried to follow Data Mapper pattern so here MatEducation.php works as ORM for mat_education.

I have read about dependency injection and what I could understand was a class should not create an object of another class in it but if it try to access the services of other class the object should be given to it (correct me). I am more interested in design pattern and conceptual understanding of problem solving (error handling is not done properly in this code but that's ok for now).

I appreciate any suggestion, criticism, links to some good blogs or anything further reading.

Answer 1

Database.php is a bit confusing. The class inherits from PDO, but in the constructor it creates a direct instance of PDO. Furthermore, your Mapper classes appear to be juggling queries that are then delegated to the parent class, but do no actual object mapping as their names suggest. The MatEducation class as an arrayToObject method which does data mapping.

What you've basically created is an implementation of the Repository Design Pattern:

(Image credit: Patkos Csaba, http://code.tutsplus.com)

• Client: This is implemented by profile.php
• Repository: This is currently implemented by your Mapper classes
• Factory: This concern is split between your Mapper classes, and the arrayToObject method in MatEducation. Really, this is the "data mapper."
• Gateway: This is implemented by your Database class

Separating your Concerns

Instead, you want the following classes and interfaces:

• MatEducation (Class) This is your Domain Model and represents data from the database

• Gateway (Interface) This is the interface that all database gateways must implement. Now you can abstract away database specific code and hide it behind an interface

• MySqlGateway (Class, implements Gateway) The concrete gateway class that knows how to interact with a MySQL database

• DataMapper (Interface) The interface that all data mappers must implement, which takes the place of the "Factory" in the Repository Pattern diagram.

• MySqlDataMapper (Class, implements DataMapper) The data mapper object that knows how to map MySQL database rows to MatEducation objects (and can be expanded to other objects as well)

• MatEducationRepository (Class) The repository object allowing CRUD operations on MatEducation objects

Wiring the Concerns Together

Let's look at each component individualy:

MatEducation Domain Model

Nothing special here. Just a plain old PHP object (POPO) and some methods that operate on that data.

class MatEducation {
    private $id;
    private $eduId;
    private $degree;
    private $institute;

    public __construct($userId, $eduId) {
        $this->userId = $userId;
        $this->eduId = $eduId;
    }

    // more methods
}

The Gateway Interface and Concrete Class

The Gateway is just that, the gateway to your database. We want to decouple the database vendor from the rest of your application. What lays in MySQL today, could get thrown into PostgreSQL tomorrow. Or Oracle. Or SQL Server. We can do this by first defining an interface:

interface Gateway {
    public function prepareAndExecute($query, $values = array(), $fetchMode = PDO::FETCH_ASSOC);
    public function lastInsertId($name = null);
    public function exec($query);
}

Next, we want a concrete class that implements this interface:

class MySqlGateway implements Gateway {
    private $pdo;

    public __construct($username = null, $password = null) {
        // Get username and password from config, if null
        $this->pdo = new PDO(...);
    }

    public function prepareAndExecute($query, $values = array(), $fetchMode = PDO::FETCH_ASSOC) {
        // ...
    }

    public function lastInsertId($name = null) {
        // ...
    }

    public function exec($query) {
        // ...
    }
}

The significance of the interface will become more apparent later.

The DataMapper Interface and Concrete Class

The Data Mapper is the object that can be the "Factory" in the Repository pattern. More importantly, it knows how to translate your MySQL database schema to your domain object model. To promote decoupling your database schema from your object model, we first create an interface:

interface DataMapper {
    public function toMatEducation(MatEducation $entity, $data);
}

And then a concrete class for your MySQL database:

class MySqlDataMapper implements DataMapper {
    public function toMatEducation(MatEducation $entity, $data) {
        $entity->id = $data['id'];
        $entity->eduId = $data['edu_id'];
        $entity->degree = $data['edu_degree'];
        $entity->institute = $data['edu_institute'];

        return $entity;
    }
}

As with the Gateway interface, the significance of the DataMapper interface will become apparent in the next section.

The MatEducationRepository class

The repository class is where things are finally wired together. The repository class needs two other objects in order to do its job:

1. The Gateway object
2. The DataMapper object

Since both objects are implemented using interfaces, you can switch out database vendors and schemas without touching the code in MatEducationRepository.

class MatEducationRepository {
    private $map;
    private $gateway;

    public MatEducationRepository(DataMapper $map = null, Gateway $gateway = null) {
        $this->map = $map || new MySqlDataMapper();
        $this->gateway = $gateway || new MySqlGateway();
    }

    public function add(MatEducation $entity) {
        // Prepare and execute query using $this->gateway->prepareAndExecute(...)
    }

    public function find($userId, $eduId) {
        return $this->load(new MatEducation($userId, $eduId));
    }

    public function load(MatEducation $entity) {
        // Prepare query
        $data = $this->gateway->prepareAndExecute(/* query */);

        $this->map->toMatEducation($entity, $data);

        return $entity;
    }

    public function remove(MatEducation $entity) {
        // Prepare query
        $this->gateway->prepareAndExecute(/* query */);
    }
}

The constructor for MatEducationRepository uses PHP type hints ensuring it has the objects it needs. If they are null, intelligent defaults are used (MySqlGateway and MySqlDataMapper in this case). Passing dependencies for an object in its constructor is called Constructor Injection.

Since I'm sure MatEducation isn't the only domain model and database table that you'll deal with, it's also useful to create a database context object.

The DbContext Object Creates a Tidy Package

We will create a DbContext object that instantiates all the repository objects your web application might need. For right now, it's pretty simple:

class DbContext {
    public $educations;

    public __construct() {
        $this->educations = new MatEducationRepository();
    }
}

Now you can have one object for all your CRUD operations:

$db = new DbContext();
$db->educations->add(...);
$db->educations->find(...);
$db->educations->load(...);
$db->educations->remove(...);

Deleting a MatEducation

CRUD operations become much cleaner:

//----------- delete education details-----------------

$eduId = $_GET['eidUser'];
$userId = $_SESSION['user_id'];

if (empty($eduId)) {
    // throw an exception (fail loudly), or return a 400 Bad Request
}

$db = new DbContext();
$education = $db->educations->find($userId, $eduId);

if (isset($education)) {
    $db->educations->remove($education);
} else {
    // return a 404 Not Found response or redirect to a 404 Not Found page
}

The code itself becomes easier to read as well. You know that $db->educations->remove will remove an education from the database. The code, when read out load, does exactly what it sounds like it does.

Notice that there is no getError method on your Gateway. The key here is to have database operations fail loudly. If anything goes wrong with inserts, updates or deletes, throw an exception (see my answer for Returning status codes from business layer).

The Only Constant is Change

While this level of decoupling adds classes and interfaces, you insulate the rest of your application from database schema changes, and even changes to database vendors. You can make large changes to the foundation of your application with minimal impact as long as classes implement the proper interface:

class OracleDataMapper implements DataMapper {
    // ...
}

class OracleGateway implements Gateway {
    // ..
}

class SqlServerDataMapper implements DataMapper {
    // ..
}

class SqlServerGateway implements Gateway {
    // ..
}

And to use these classes with your MatEducationRepository class:

new MatEducationRepository(new OracleDataMapper(), new OracleGateway());
new MatEducationRepository(new SqlServerDataMapper(), new SqlServerGateway());