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: