Defining a mapper

A mapper or data mapper works in much the same manner as a hydrator: converting data from one model, be it array or object, into another. A critical difference is that the hydrator is generic and does not need to have object property names pre-programmed, whereas the mapper is the opposite: it needs precise information on property names for both models. In this recipe we will demonstrate the use of a mapper to convert data from one database table into another.

How to do it...

We first define a Application\Database\Mapper\FieldConfig class, which contains mapping instructions for individual fields. We also define appropriate class constants:

namespace Application\Database\Mapper;
use InvalidArgumentException;
class FieldConfig
{
  const ERROR_SOURCE = 
    'ERROR: need to specify destTable and/or source';
  const ERROR_DEST   = 'ERROR: need to specify either '
    . 'both destTable and destCol or neither';

Key properties are defined along with the appropriate class constants. $key is used to identify the object. $source represents the column from the source database table. $destTable and $destCol represent the target database table and column. $default, if defined, contains a default value or a callback that produces the appropriate value:
```
public $key;
public $source;
public $destTable;
public $destCol;
public $default;
```

We now turn our attention to the constructor, which assigns default values, builds the key, and checks to see that either or both $source or $destTable and $destCol are defined:

public function __construct($source = NULL,
                            $destTable = NULL,
                            $destCol   = NULL,
                            $default   = NULL)
{
  // generate key from source + destTable + destCol
  $this->key = $source . '.' . $destTable . '.' . $destCol;
  $this->source = $source;
  $this->destTable = $destTable;
  $this->destCol = $destCol;
  $this->default = $default;
  if (($destTable && !$destCol) || 
      (!$destTable && $destCol)) {
      throw new InvalidArgumentException(self::ERROR_DEST);
  }
  if (!$destTable && !$source) {
      throw new InvalidArgumentException(
        self::ERROR_SOURCE);
  }
}

Note

Note that we allow source and destination columns to be NULL. The reason for this is that we might have a source column that has no place in the destination table. Likewise, there might be mandatory columns in the destination table that are not represented in the source table.

In the case of defaults, we need to check to see if the value is a callback. If so, we run the callback; otherwise, we return the direct value. Note that the callbacks should be defined so that they accept a database table row as an argument:
```
public function getDefault()
{
  if (is_callable($this->default)) {
      return call_user_func($this->default, $row);
  } else {
      return $this->default;
  }
}
```

Finally, to wrap up this class, we define getters and setters for each of the five properties:

public function getKey()
{
  return $this->key;
}

public function setKey($key)
{
  $this->key = $key;
}

// etc.

Next, we define a Application\Database\Mapper\Mapping mapping class, which accepts the name of the source and destination tables as well as an array of FieldConfig objects as an argument. You will see later that we allow the destination table property to be an array, as the mapping might be to two or more destination tables:

namespace Application\Database\Mapper;
class Mapping
{
  protected $sourceTable;
  protected $destTable;
  protected $fields;
  protected $sourceCols;
  protected $destCols;

  public function __construct(
    $sourceTable, $destTable, $fields = NULL)
  {
    $this->sourceTable = $sourceTable;
    $this->destTable = $destTable;
    $this->fields = $fields;
  }

We then define getters and setters for these properties:

public function getSourceTable()
{
  return $this->sourceTable;
}
public function setSourceTable($sourceTable)
{
  $this->sourceTable = $sourceTable;
}
// etc.

For field configuration, we also need to provide the ability to add an individual field. There is no need to supply the key as a separate argument as this can be obtained from the FieldConfig instance:
```
public function addField(FieldConfig $field)
{
  $this->fields[$field->getKey()] = $field;
  return $this;
}
```

It is extremely important to obtain an array of source column names. The problem is that the source column name is a property buried in a FieldConfig object. Accordingly, when this method is called, we loop through the array of FieldConfig objects and invoke getSource() on each one to obtain the source column name:

public function getSourceColumns()
{
  if (!$this->sourceCols) {
      $this->sourceCols = array();
      foreach ($this->getFields() as $field) {
        if (!empty($field->getSource())) {
            $this->sourceCols[$field->getKey()] = 
              $field->getSource();
        }
      }
  }
  return $this->sourceCols;
}

We use a similar approach for getDestColumns(). The big difference compared to getting a list of source columns is that we only want the columns for one specific destination table, which is critical if there's more than one such table is defined. We do not need to check to see if $destCol is set as this is already taken care of in the constructor for FieldConfig:

public function getDestColumns($table)
{
  if (empty($this->destCols[$table])) {
      foreach ($this->getFields() as $field) {
        if ($field->getDestTable()) {
          if ($field->getDestTable() == $table) {
              $this->destCols[$table][$field->getKey()] = 
                $field->getDestCol();
          }
        }
      }
  }
  return $this->destCols[$table];
}

Finally, we define a method that accepts as a first argument an array representing one row of data from the source table. The second argument is the name of the destination table. The method produces an array of data ready to be inserted into the destination table.
We had to make a decision as to which would take precedence: the default value (which could be provided by a callback), or data from the source table. We decided to test for a default value first. If the default comes back NULL, data from the source is used. Note that if further processing is required, the default should be defined as a callback.
```
public function mapData($sourceData, $destTable)
{
  $dest = array();
  foreach ($this->fields as $field) {
    if ($field->getDestTable() == $destTable) {
        $dest[$field->getDestCol()] = NULL;
        $default = $field->getDefault($sourceData);
        if ($default) {
            $dest[$field->getDestCol()] = $default;
        } else {
            $dest[$field->getDestCol()] = 
                  $sourceData[$field->getSource()];
        }
    }
  }
  return $dest;
}
}
```
Note
Note that some columns will appear in the destination insert that are not present in the source row. In this case, the $source property of the FieldConfig object is left as NULL, and a default value is supplied, either as a scalar value or as a callback.

We are now ready to define two methods that will generate SQL. The first such method will generate an SQL statement to read from the source table. The statement will include placeholders to be prepared (for example, using PDO::prepare()):

public function getSourceSelect($where = NULL)
{
  $sql = 'SELECT ' 
  . implode(',', $this->getSourceColumns()) . ' ';
  $sql .= 'FROM ' . $this->getSourceTable() . ' ';
  if ($where) {
    $where = trim($where);
    if (stripos($where, 'WHERE') !== FALSE) {
        $sql .= $where;
    } else {
        $sql .= 'WHERE ' . $where;
    }
  }
  return trim($sql);
}

The other SQL generation method produces a statement to be prepared for a specific destination table. Notice that the placeholders are the same as the column names preceded by ":":

public function getDestInsert($table)
{
  $sql = 'INSERT INTO ' . $table . ' ';
  $sql .= '( ' 
  . implode(',', $this->getDestColumns($table)) 
  . ' ) ';
  $sql .= ' VALUES ';
  $sql .= '( :' 
  . implode(',:', $this->getDestColumns($table)) 
  . ' ) ';
  return trim($sql);
}

How it works...

Use the code shown in steps 1 to 5 to produce an Application\Database\Mapper\FieldConfig class. Place the code shown in steps 6 to 14 into a second Application\Database\Mapper\Mapping class.

Before defining a calling program that performs mapping, it's important to consider the source and destination database tables. The definition for the source table, prospects_11, is as follows:

CREATE TABLE `prospects_11` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(128) NOT NULL,
  `last_name` varchar(128) NOT NULL,
  `address` varchar(256) DEFAULT NULL,
  `city` varchar(64) DEFAULT NULL,
  `state_province` varchar(32) DEFAULT NULL,
  `postal_code` char(16) NOT NULL,
  `phone` varchar(16) NOT NULL,
  `country` char(2) NOT NULL,
  `email` varchar(250) NOT NULL,
  `status` char(8) DEFAULT NULL,
  `budget` decimal(10,2) DEFAULT NULL,
  `last_updated` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQ_35730C06E7927C74` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

In this example, you can use two destination tables, customer_11 and profile_11, between which there is a 1:1 relationship:

CREATE TABLE `customer_11` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(256) CHARACTER SET latin1 
     COLLATE latin1_general_cs NOT NULL,
  `balance` decimal(10,2) NOT NULL,
  `email` varchar(250) NOT NULL,
  `password` char(16) NOT NULL,
  `status` int(10) unsigned NOT NULL DEFAULT '0',
  `security_question` varchar(250) DEFAULT NULL,
  `confirm_code` varchar(32) DEFAULT NULL,
  `profile_id` int(11) DEFAULT NULL,
  `level` char(3) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQ_81398E09E7927C74` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=80 DEFAULT CHARSET=utf8 COMMENT='Customers';

CREATE TABLE `profile_11` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `address` varchar(256) NOT NULL,
  `city` varchar(64) NOT NULL,
  `state_province` varchar(32) NOT NULL,
  `postal_code` varchar(10) NOT NULL,
  `country` varchar(3) NOT NULL,
  `phone` varchar(16) NOT NULL,
  `photo` varchar(128) NOT NULL,
  `dob` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=80 DEFAULT CHARSET=utf8 COMMENT='Customers';

You can now define a calling program called chap_11_mapper.php, which sets up autoloading and uses the two classes mentioned previously. You can also use the Connection class defined in Chapter 5, Interacting with a Database:

<?php
define('DB_CONFIG_FILE', '/../config/db.config.php');
define('DEFAULT_PHOTO', 'person.gif');
require __DIR__ . '/../Application/Autoload/Loader.php';
Application\Autoload\Loader::init(__DIR__ . '/..');
use Application\Database\Mapper\ { FieldConfig, Mapping };
use Application\Database\Connection;
$conn = new Connection(include __DIR__ . DB_CONFIG_FILE);

For demonstration purposes, after having made sure the two destination tables exist, you can truncate both tables so that any data that appears is clean:

$conn->pdo->query('DELETE FROM customer_11');
$conn->pdo->query('DELETE FROM profile_11');

You are now ready to build the Mapping instance and populate it with FieldConfig objects. Each FieldConfig object represents a mapping between source and destination. In the constructor, supply the name of the source table and the two destination tables in the form of an array:

$mapper = new Mapping('prospects_11', ['customer_11','profile_11']);

You can start simply by mapping fields between prospects_11 and customer_11 where there are no defaults:

$mapper>addField(new FieldConfig('email','customer_11','email'))

Note that addField() returns the current mapping instance so there is no need to keep specifying $mapper->addField(). This technique is referred to as the fluent interface.

The name field is tricky, as in the prospects_11 table it's represented by two columns, but only one column in the customer_11 table. Accordingly, you can add a callback as default for first_name to combine the two fields into one. You will also need to define an entry for last_name but where there is no destination mapping:

->addField(new FieldConfig('first_name','customer_11','name',
  function ($row) { return trim(($row['first_name'] ?? '') 
. ' ' .  ($row['last_name'] ?? ''));}))
->addField(new FieldConfig('last_name'))

The customer_11::status field can use the null coalesce operator (??) to determine if it's set or not:

->addField(new FieldConfig('status','customer_11','status',
  function ($row) { return $row['status'] ?? 'Unknown'; }))

The customer_11::level field is not represented in the source table, thus you can make a NULL entry for the source field, but make sure the destination table and column are set. Likewise, customer_11::password is not present in the source table. In this case, the callback uses the phone number as a temporary password:

->addField(new FieldConfig(NULL,'customer_11','level','BEG'))
->addField(new FieldConfig(NULL,'customer_11','password',
  function ($row) { return $row['phone']; }))

You can also set mappings from prospects_11 to profile_11 as follows. Note that as the source photo and date of birth columns are not present in prospects_11, you can set any appropriate default:

->addField(new FieldConfig('address','profile_11','address'))
->addField(new FieldConfig('city','profile_11','city'))
->addField(new FieldConfig('state_province','profile_11', 
'state_province', function ($row) { 
  return $row['state_province'] ?? 'Unknown'; }))
->addField(new FieldConfig('postal_code','profile_11',
'postal_code'))
->addField(new FieldConfig('phone','profile_11','phone'))
->addField(new FieldConfig('country','profile_11','country'))
->addField(new FieldConfig(NULL,'profile_11','photo',
DEFAULT_PHOTO))
->addField(new FieldConfig(NULL,'profile_11','dob',
date('Y-m-d')));

In order to establish the 1:1 relationship between the profile_11 and customer_11 tables, we set the values of customer_11::id, customer_11::profile_id and profile_11::id to the value of $row['id'] using a callback:

$idCallback = function ($row) { return $row['id']; };
$mapper->addField(new FieldConfig('id','customer_11','id',
$idCallback))
->addField(new FieldConfig(NULL,'customer_11','profile_id',
$idCallback))
->addField(new FieldConfig('id','profile_11','id',$idCallback));

You can now call the appropriate methods to generate three SQL statements, one to read from the source table, and two to insert into the two destination tables:

$sourceSelect  = $mapper->getSourceSelect();
$custInsert    = $mapper->getDestInsert('customer_11');
$profileInsert = $mapper->getDestInsert('profile_11');

These three statements can immediately be prepared for later execution:

$sourceStmt  = $conn->pdo->prepare($sourceSelect);
$custStmt    = $conn->pdo->prepare($custInsert);
$profileStmt = $conn->pdo->prepare($profileInsert);

We then execute the SELECT statement, which produces rows from the source table. In a loop we then generate INSERT data for each destination table, and execute the appropriate prepared statements:

$sourceStmt->execute();
while ($row = $sourceStmt->fetch(PDO::FETCH_ASSOC)) {
  $custData = $mapper->mapData($row, 'customer_11');
  $custStmt->execute($custData);
  $profileData = $mapper->mapData($row, 'profile_11');
  $profileStmt->execute($profileData);
  echo "Processing: {$custData['name']}\n";
}

Here are the three SQL statements produced:

We can then view the data directly from the database using SQL JOIN to ensure the relationship has been maintained:

Previous Chapter

Implementing a strategy pattern

Next Chapter

Implementing object-relational mapping

Table of Contents for PHP 7: Real World Application Development

Defining a mapper

How to do it...

Note

Note

How it works...

Table of Contents for
PHP 7: Real World Application Development