      1: <?php
      2:
      3: /*
      4:  * This file is part of the CRUDlex package.
      5:  *
      6:  * (c) Philip Lehmann-Böhm <philip@philiplb.de>
      7:  *
      8:  * For the full copyright and license information, please view the LICENSE
      9:  * file that was distributed with this source code.
     10:  */
     11:
     12: namespace CRUDlex;
     13:
     14: use CRUDlex\CRUDEntity;
     15: use CRUDlex\CRUDData;
     16: use CRUDlex\CRUDFileProcessorInterface;
     17:
     18: /**
     19:  * MySQL CRUDData implementation using a given Doctrine DBAL instance.
     20:  */
     21: class CRUDMySQLData extends CRUDData {
     22:
     23:     /**
     24:      * Holds the Doctrine DBAL instance.
     25:      */
     26:     protected $db;
     27:
     28:     /**
     29:      * Constructor.
     30:      *
     31:      * @param CRUDEntityDefinition $definition
     32:      * the entity definition
     33:      * @param CRUDFileProcessorInterface $fileProcessor
     34:      * the file processor to use
     35:      * @param $db
     36:      * the Doctrine DBAL instance to use
     37:      */
     38:     public function __construct(CRUDEntityDefinition $definition, CRUDFileProcessorInterface $fileProcessor, $db) {
     39:         $this->definition = $definition;
     40:         $this->fileProcessor = $fileProcessor;
     41:         $this->db = $db;
     42:     }
     43:
     44:     /**
     45:      * {@inheritdoc}
     46:      */
     47:     public function get($id) {
     48:         $entities = $this->listEntries(array('id' => $id));
     49:         if (count($entities) == 0) {
     50:             return null;
     51:         }
     52:         return $entities[0];
     53:     }
     54:
     55:     /**
     56:      * {@inheritdoc}
     57:      */
     58:     public function listEntries(array $filter = array()) {
     59:         $fieldNames = $this->definition->getFieldNames();
     60:         $sql = 'SELECT `'.implode('`,`', $fieldNames).'`';
     61:         $sql .= ' FROM '.$this->definition->getTable().' WHERE deleted_at IS NULL';
     62:         $values = array();
     63:         foreach ($filter as $field => $value) {
     64:             $sql .= ' AND `'.$field.'` = ?';
     65:             $values[] = $value;
     66:         }
     67:         $rows = $this->db->fetchAll($sql, $values);
     68:         $entities = array();
     69:         foreach ($rows as $row) {
     70:             $entities[] = $this->hydrate($row);
     71:         }
     72:         return $entities;
     73:     }
     74:
     75:     /**
     76:      * {@inheritdoc}
     77:      */
     78:     public function create(CRUDEntity $entity) {
     79:         $formFields = $this->definition->getEditableFieldNames();
     80:         $fields = array_merge(array('created_at', 'updated_at', 'version'),
     81:                 $formFields);
     82:         $placeHolders = array();
     83:         $values = array();
     84:         for ($i = 0; $i < count($formFields); ++$i) {
     85:             $placeHolders[] = '?';
     86:             $value = $entity->get($formFields[$i]);
     87:             if ($this->definition->getType($formFields[$i]) == 'bool') {
     88:                 $value = $value ? 1 : 0;
     89:             }
     90:             $values[] = $value;
     91:         }
     92:         $sql = 'INSERT INTO '.$this->definition->getTable().' (`'.implode('`,`', $fields).'`) VALUES (NOW(), NOW(), 0, '.implode(',', $placeHolders).')';
     93:         $this->db->executeUpdate($sql, $values);
     94:
     95:         $entity->set('id', $this->db->lastInsertId());
     96:     }
     97:
     98:     /**
     99:      * {@inheritdoc}
    100:      */
    101:     public function update(CRUDEntity $entity) {
    102:         $formFields = $this->definition->getEditableFieldNames();
    103:         $fields = array_merge(array('updated_at', 'version'),
    104:                 $formFields);
    105:         $values = array();
    106:         $sets = array();
    107:         for ($i = 0; $i < count($formFields); ++$i) {
    108:             $value = $entity->get($formFields[$i]);
    109:             if ($this->definition->getType($formFields[$i]) == 'bool') {
    110:                 $value = $value ? 1 : 0;
    111:             }
    112:             $values[] = $value;
    113:             $sets[] = '`'.$formFields[$i].'`=?';
    114:         }
    115:         $values[] = $entity->get('id');
    116:         $sql = 'UPDATE '.$this->definition->getTable().' SET updated_at = NOW(), ';
    117:         $sql .= implode(',', $sets).' WHERE id=?';
    118:         $this->db->executeUpdate($sql, $values);
    119:
    120:         return $this->db->lastInsertId();
    121:     }
    122:
    123:     /**
    124:      * {@inheritdoc}
    125:      */
    126:     public function delete($id) {
    127:         foreach ($this->definition->getChildren() as $parent) {
    128:             $sql = 'SELECT COUNT(id) AS amount FROM '.$parent[0].' WHERE ';
    129:             $sql .= $parent[1].' = ? AND deleted_at IS NULL';
    130:             $result = $this->db->fetchAssoc($sql, array($id));
    131:             if ($result['amount'] > 0) {
    132:                 return false;
    133:             }
    134:         }
    135:
    136:         $sql = 'UPDATE '.$this->definition->getTable().' SET deleted_at = NOW() WHERE id = ?';
    137:         $this->db->executeUpdate($sql, array($id));
    138:         return true;
    139:     }
    140:
    141:     /**
    142:      * {@inheritdoc}
    143:      */
    144:     public function getReferences($table, $nameField) {
    145:         $sql = 'SELECT id, `'.$nameField.'` FROM '.$table.' WHERE deleted_at IS NULL ORDER BY `'.$nameField.'`';
    146:         $entries = $this->db->fetchAll($sql);
    147:         $result = array();
    148:         foreach ($entries as $entry) {
    149:             $result[$entry['id']] = $entry[$nameField];
    150:         }
    151:         return $result;
    152:     }
    153:
    154:     /**
    155:      * {@inheritdoc}
    156:      */
    157:     public function countBy($table, array $params, array $paramsOperators, $includeDeleted) {
    158:         $sql = 'SELECT COUNT(id) AS amount FROM '.$table;
    159:         $paramValues = array();
    160:         $paramSQLs = array();
    161:         foreach($params as $name => $value) {
    162:             $paramSQLs[] = '`'.$name.'`'.$paramsOperators[$name].'?';
    163:             $paramValues[] = $value;
    164:         }
    165:         $sql .= ' WHERE '.implode(' AND ', $paramSQLs);
    166:         if ($includeDeleted) {
    167:             $sql .= ' AND deleted_at IS NULL';
    168:         }
    169:         $result = $this->db->fetchAssoc($sql, $paramValues);
    170:         return intval($result['amount']);
    171:     }
    172:
    173:     /**
    174:      * {@inheritdoc}
    175:      */
    176:     public function fetchReferences(CRUDEntity $entity = null) {
    177:         if (!$entity) {
    178:             return;
    179:         }
    180:         foreach ($this->definition->getFieldNames() as $field) {
    181:             if ($this->definition->getType($field) !== 'reference') {
    182:                 continue;
    183:             }
    184:             $nameField = $this->definition->getReferenceNameField($field);
    185:             $sql = 'SELECT '.$nameField.' FROM ';
    186:             $sql .= $this->definition->getReferenceTable($field).' WHERE id = ? AND deleted_at IS NULL';
    187:             $result = $this->db->fetchAssoc($sql, array($entity->get($field)));
    188:             if ($result) {
    189:                 $entity->set($field,
    190:                     array('id' => $entity->get($field), 'name' => $result[$nameField]));
    191:             }
    192:         }
    193:     }
    194:
    195: }
    196: