locomotivemtl /
charcoal-core
| Total Complexity | 120 |
| Total Lines | 966 |
| Duplicated Lines | 0 % |
| Changes | 10 | ||
| Bugs | 0 | Features | 1 |
Complex classes like DatabaseSource often do a lot of different things. To break such a class down, we need to identify a cohesive component within that class. A common approach to find such a component is to look for fields/methods that share the same prefixes, or suffixes.
Once you have determined the fields that belong together, you can apply the Extract Class refactoring. If the component makes sense as a sub-class, Extract Subclass is also a candidate, and is often faster.
While breaking up the class, it is a good idea to analyze how other classes use DatabaseSource, and based on these observations, apply Extract Interface, too.
| 1 | <?php |
||
| 27 | class DatabaseSource extends AbstractSource implements |
||
| 28 | DatabaseSourceInterface |
||
| 29 | { |
||
| 30 | const DEFAULT_DB_HOSTNAME = 'localhost'; |
||
| 31 | |||
| 32 | const DEFAULT_TABLE_ALIAS = 'objTable'; |
||
| 33 | |||
| 34 | const MYSQL_DRIVER_NAME = 'mysql'; |
||
| 35 | const SQLITE_DRIVER_NAME = 'sqlite'; |
||
| 36 | |||
| 37 | /** |
||
| 38 | * The database connector. |
||
| 39 | * |
||
| 40 | * @var PDO |
||
| 41 | */ |
||
| 42 | private $pdo; |
||
| 43 | |||
| 44 | /** |
||
| 45 | * The {@see self::$model}'s table name. |
||
| 46 | * |
||
| 47 | * @var string |
||
| 48 | */ |
||
| 49 | private $table; |
||
| 50 | |||
| 51 | /** |
||
| 52 | * Create a new database handler. |
||
| 53 | * |
||
| 54 | * @param array $data Class dependencies. |
||
| 55 | */ |
||
| 56 | public function __construct(array $data) |
||
| 57 | { |
||
| 58 | $this->pdo = $data['pdo']; |
||
| 59 | |||
| 60 | parent::__construct($data); |
||
| 61 | } |
||
| 62 | |||
| 63 | /** |
||
| 64 | * Retrieve the database connector. |
||
| 65 | * |
||
| 66 | * @throws RuntimeException If the datahase was not set. |
||
| 67 | * @return PDO |
||
| 68 | */ |
||
| 69 | public function db() |
||
| 70 | { |
||
| 71 | if ($this->pdo === null) { |
||
| 72 | throw new RuntimeException( |
||
| 73 | 'Database Connector was not set.' |
||
| 74 | ); |
||
| 75 | } |
||
| 76 | return $this->pdo; |
||
| 77 | } |
||
| 78 | |||
| 79 | /** |
||
| 80 | * Set the database's table to use. |
||
| 81 | * |
||
| 82 | * @param string $table The source table. |
||
| 83 | * @throws InvalidArgumentException If argument is not a string or alphanumeric/underscore. |
||
| 84 | * @return self |
||
| 85 | */ |
||
| 86 | public function setTable($table) |
||
| 87 | { |
||
| 88 | if (!is_string($table)) { |
||
|
introduced
by
|
|||
| 89 | throw new InvalidArgumentException(sprintf( |
||
| 90 | 'DatabaseSource::setTable() expects a string as table. (%s given). [%s]', |
||
| 91 | gettype($table), |
||
| 92 | get_class($this->model()) |
||
| 93 | )); |
||
| 94 | } |
||
| 95 | |||
| 96 | /** |
||
| 97 | * For security reason, only alphanumeric characters (+ underscores) |
||
| 98 | * are valid table names; Although SQL can support more, |
||
| 99 | * there's really no reason to. |
||
| 100 | */ |
||
| 101 | if (!preg_match('/[A-Za-z0-9_]/', $table)) { |
||
| 102 | throw new InvalidArgumentException(sprintf( |
||
| 103 | 'Table name "%s" is invalid: must be alphanumeric / underscore.', |
||
| 104 | $table |
||
| 105 | )); |
||
| 106 | } |
||
| 107 | |||
| 108 | $this->table = $table; |
||
| 109 | return $this; |
||
| 110 | } |
||
| 111 | |||
| 112 | /** |
||
| 113 | * Determine if a table is assigned. |
||
| 114 | * |
||
| 115 | * @return boolean |
||
| 116 | */ |
||
| 117 | public function hasTable() |
||
| 118 | { |
||
| 119 | return !empty($this->table); |
||
| 120 | } |
||
| 121 | |||
| 122 | /** |
||
| 123 | * Get the database's current table. |
||
| 124 | * |
||
| 125 | * @throws RuntimeException If the table was not set. |
||
| 126 | * @return string |
||
| 127 | */ |
||
| 128 | public function table() |
||
| 129 | { |
||
| 130 | if ($this->table === null) { |
||
| 131 | throw new RuntimeException( |
||
| 132 | 'Table was not set.' |
||
| 133 | ); |
||
| 134 | } |
||
| 135 | return $this->table; |
||
| 136 | } |
||
| 137 | |||
| 138 | /** |
||
| 139 | * Create a table from a model's metadata. |
||
| 140 | * |
||
| 141 | * @return boolean TRUE if the table was created, otherwise FALSE. |
||
| 142 | */ |
||
| 143 | public function createTable() |
||
| 144 | { |
||
| 145 | if ($this->tableExists() === true) { |
||
| 146 | return true; |
||
| 147 | } |
||
| 148 | |||
| 149 | $dbh = $this->db(); |
||
| 150 | $driver = $dbh->getAttribute(PDO::ATTR_DRIVER_NAME); |
||
| 151 | $model = $this->model(); |
||
| 152 | $metadata = $model->metadata(); |
||
| 153 | |||
| 154 | $table = $this->table(); |
||
| 155 | $fields = $this->getModelFields($model); |
||
| 156 | $columns = []; |
||
| 157 | foreach ($fields as $field) { |
||
| 158 | $columns[] = $field->sql(); |
||
| 159 | } |
||
| 160 | |||
| 161 | $query = 'CREATE TABLE `'.$table.'` ('."\n"; |
||
| 162 | $query .= implode(',', $columns); |
||
| 163 | |||
| 164 | $key = $model->key(); |
||
| 165 | if ($key) { |
||
| 166 | $query .= ', PRIMARY KEY (`'.$key.'`) '."\n"; |
||
| 167 | } |
||
| 168 | |||
| 169 | /** @todo Add indexes for all defined list constraints (yea... tough job...) */ |
||
| 170 | if ($driver === self::MYSQL_DRIVER_NAME) { |
||
| 171 | $engine = 'InnoDB'; |
||
| 172 | $query .= ') ENGINE='.$engine.' DEFAULT CHARSET=utf8 COMMENT="'.addslashes($metadata['name']).'";'; |
||
| 173 | } else { |
||
| 174 | $query .= ');'; |
||
| 175 | } |
||
| 176 | |||
| 177 | $this->logger->debug($query); |
||
| 178 | $dbh->query($query); |
||
| 179 | |||
| 180 | $this->setTableExists(); |
||
| 181 | |||
| 182 | return true; |
||
| 183 | } |
||
| 184 | |||
| 185 | /** |
||
| 186 | * Alter an existing table to match the model's metadata. |
||
| 187 | * |
||
| 188 | * @return boolean TRUE if the table was altered, otherwise FALSE. |
||
| 189 | */ |
||
| 190 | public function alterTable() |
||
| 191 | { |
||
| 192 | if ($this->tableExists() === false) { |
||
| 193 | return false; |
||
| 194 | } |
||
| 195 | |||
| 196 | $dbh = $this->db(); |
||
| 197 | $table = $this->table(); |
||
| 198 | $fields = $this->getModelFields($this->model()); |
||
| 199 | $cols = $this->tableStructure(); |
||
| 200 | foreach ($fields as $field) { |
||
| 201 | $ident = $field->ident(); |
||
| 202 | |||
| 203 | if (!array_key_exists($ident, $cols)) { |
||
| 204 | // The key does not exist at all. |
||
| 205 | $query = 'ALTER TABLE `'.$table.'` ADD '.$field->sql(); |
||
| 206 | $this->logger->debug($query); |
||
| 207 | $dbh->query($query); |
||
| 208 | } else { |
||
| 209 | // The key exists. Validate. |
||
| 210 | $col = $cols[$ident]; |
||
| 211 | $alter = true; |
||
| 212 | if (strtolower($col['Type']) !== strtolower($field->sqlType())) { |
||
| 213 | $alter = true; |
||
| 214 | } |
||
| 215 | |||
| 216 | if ((strtolower($col['Null']) === 'no') && !$field->allowNull()) { |
||
| 217 | $alter = true; |
||
| 218 | } |
||
| 219 | |||
| 220 | if ((strtolower($col['Null']) !== 'no') && $field->allowNull()) { |
||
| 221 | $alter = true; |
||
| 222 | } |
||
| 223 | |||
| 224 | if ($col['Default'] !== $field->defaultVal()) { |
||
| 225 | $alter = true; |
||
| 226 | } |
||
| 227 | |||
| 228 | if ($alter === true) { |
||
| 229 | $query = 'ALTER TABLE `'.$table.'` CHANGE `'.$ident.'` '.$field->sql(); |
||
| 230 | $this->logger->debug($query); |
||
| 231 | $dbh->query($query); |
||
| 232 | } |
||
| 233 | } |
||
| 234 | } |
||
| 235 | |||
| 236 | return true; |
||
| 237 | } |
||
| 238 | |||
| 239 | /** |
||
| 240 | * Determine if the source table exists. |
||
| 241 | * |
||
| 242 | * @return boolean TRUE if the table exists, otherwise FALSE. |
||
| 243 | */ |
||
| 244 | public function tableExists() |
||
| 245 | { |
||
| 246 | $dbh = $this->db(); |
||
| 247 | $table = $this->table(); |
||
| 248 | |||
| 249 | if (isset($dbh->tableExists, $dbh->tableExists[$table])) { |
||
| 250 | return $dbh->tableExists[$table]; |
||
| 251 | } |
||
| 252 | |||
| 253 | $exists = $this->performTableExists(); |
||
| 254 | $this->setTableExists($exists); |
||
| 255 | |||
| 256 | return $exists; |
||
| 257 | } |
||
| 258 | |||
| 259 | /** |
||
| 260 | * Perform a source table exists operation. |
||
| 261 | * |
||
| 262 | * @return boolean TRUE if the table exists, otherwise FALSE. |
||
| 263 | */ |
||
| 264 | protected function performTableExists() |
||
| 265 | { |
||
| 266 | $dbh = $this->db(); |
||
| 267 | $table = $this->table(); |
||
| 268 | |||
| 269 | $driver = $dbh->getAttribute(PDO::ATTR_DRIVER_NAME); |
||
| 270 | if ($driver === self::SQLITE_DRIVER_NAME) { |
||
| 271 | $query = sprintf('SELECT name FROM sqlite_master WHERE type = "table" AND name = "%s";', $table); |
||
| 272 | } else { |
||
| 273 | $query = sprintf('SHOW TABLES LIKE "%s"', $table); |
||
| 274 | } |
||
| 275 | |||
| 276 | $this->logger->debug($query); |
||
| 277 | $sth = $dbh->query($query); |
||
| 278 | $exists = $sth->fetchColumn(0); |
||
| 279 | |||
| 280 | return (bool)$exists; |
||
| 281 | } |
||
| 282 | |||
| 283 | /** |
||
| 284 | * Store a reminder whether the source's database table exists. |
||
| 285 | * |
||
| 286 | * @param boolean $exists Whether the table exists or not. |
||
| 287 | * @return void |
||
| 288 | */ |
||
| 289 | protected function setTableExists($exists = true) |
||
| 299 | } |
||
| 300 | |||
| 301 | /** |
||
| 302 | * Get the table columns information. |
||
| 303 | * |
||
| 304 | * @return array An associative array. |
||
| 305 | */ |
||
| 306 | public function tableStructure() |
||
| 307 | { |
||
| 308 | $dbh = $this->db(); |
||
| 309 | $table = $this->table(); |
||
| 310 | $driver = $dbh->getAttribute(PDO::ATTR_DRIVER_NAME); |
||
| 311 | if ($driver === self::SQLITE_DRIVER_NAME) { |
||
| 312 | $query = sprintf('PRAGMA table_info("%s") ', $table); |
||
| 313 | } else { |
||
| 314 | $query = sprintf('SHOW COLUMNS FROM `%s`', $table); |
||
| 315 | } |
||
| 316 | |||
| 317 | $this->logger->debug($query); |
||
| 318 | $sth = $dbh->query($query); |
||
| 319 | |||
| 320 | $cols = $sth->fetchAll((PDO::FETCH_GROUP | PDO::FETCH_UNIQUE | PDO::FETCH_ASSOC)); |
||
| 321 | if ($driver === self::SQLITE_DRIVER_NAME) { |
||
| 322 | $struct = []; |
||
| 323 | foreach ($cols as $col) { |
||
| 324 | // Normalize SQLite's result (PRAGMA) with mysql's (SHOW COLUMNS) |
||
| 325 | $struct[$col['name']] = [ |
||
| 326 | 'Type' => $col['type'], |
||
| 327 | 'Null' => !!$col['notnull'] ? 'NO' : 'YES', |
||
| 328 | 'Default' => $col['dflt_value'], |
||
| 329 | 'Key' => !!$col['pk'] ? 'PRI' : '', |
||
| 330 | 'Extra' => '' |
||
| 331 | ]; |
||
| 332 | } |
||
| 333 | return $struct; |
||
| 334 | } else { |
||
| 335 | return $cols; |
||
| 336 | } |
||
| 337 | } |
||
| 338 | |||
| 339 | /** |
||
| 340 | * Determine if the source table is empty or not. |
||
| 341 | * |
||
| 342 | * @return boolean TRUE if the table has no data, otherwise FALSE. |
||
| 343 | */ |
||
| 344 | public function tableIsEmpty() |
||
| 345 | { |
||
| 346 | $table = $this->table(); |
||
| 347 | $query = sprintf('SELECT NULL FROM `%s` LIMIT 1', $table); |
||
| 348 | $this->logger->debug($query); |
||
| 349 | $sth = $this->db()->query($query); |
||
| 350 | return ($sth->rowCount() === 0); |
||
| 351 | } |
||
| 352 | |||
| 353 | /** |
||
| 354 | * Retrieve all fields from a model. |
||
| 355 | * |
||
| 356 | * @todo Move this method in StorableTrait or AbstractModel |
||
| 357 | * @param ModelInterface $model The model to get fields from. |
||
| 358 | * @param array|null $properties Optional list of properties to get. |
||
| 359 | * If NULL, retrieve all (from metadata). |
||
| 360 | * @return PropertyField[] |
||
| 361 | */ |
||
| 362 | private function getModelFields(ModelInterface $model, $properties = null) |
||
| 363 | { |
||
| 364 | if ($properties === null) { |
||
| 365 | // No custom properties; use all (from model metadata) |
||
| 366 | $properties = array_keys($model->metadata()->properties()); |
||
| 367 | } else { |
||
| 368 | // Ensure the key is always in the required fields. |
||
| 369 | $properties = array_unique(array_merge([ $model->key() ], $properties)); |
||
| 370 | } |
||
| 371 | |||
| 372 | $fields = []; |
||
| 373 | foreach ($properties as $propertyIdent) { |
||
| 374 | $prop = $model->property($propertyIdent); |
||
| 375 | if (!$prop || !$prop['active'] || !$prop['storable']) { |
||
| 376 | continue; |
||
| 377 | } |
||
| 378 | |||
| 379 | $val = $model->propertyValue($propertyIdent); |
||
| 380 | foreach ($prop->fields($val) as $fieldIdent => $field) { |
||
| 381 | $fields[$field->ident()] = $field; |
||
| 382 | } |
||
| 383 | } |
||
| 384 | |||
| 385 | return $fields; |
||
| 386 | } |
||
| 387 | |||
| 388 | /** |
||
| 389 | * Load item by the primary column. |
||
| 390 | * |
||
| 391 | * @param mixed $ident Ident can be any scalar value. |
||
| 392 | * @param StorableInterface $item Optional item to load into. |
||
| 393 | * @return StorableInterface |
||
| 394 | */ |
||
| 395 | public function loadItem($ident, StorableInterface $item = null) |
||
| 396 | { |
||
| 397 | $key = $this->model()->key(); |
||
| 398 | |||
| 399 | return $this->loadItemFromKey($key, $ident, $item); |
||
| 400 | } |
||
| 401 | |||
| 402 | /** |
||
| 403 | * Load item by the given column. |
||
| 404 | * |
||
| 405 | * @param string $key Column name. |
||
| 406 | * @param mixed $ident Value of said column. |
||
| 407 | * @param StorableInterface|null $item Optional. Item (storable object) to load into. |
||
| 408 | * @throws \Exception If the query fails. |
||
| 409 | * @return StorableInterface |
||
| 410 | */ |
||
| 411 | public function loadItemFromKey($key, $ident, StorableInterface $item = null) |
||
| 412 | { |
||
| 413 | if ($item !== null) { |
||
| 414 | $this->setModel($item); |
||
| 415 | } else { |
||
| 416 | $class = get_class($this->model()); |
||
| 417 | $item = new $class; |
||
| 418 | } |
||
| 419 | |||
| 420 | $key = preg_replace('/[^\w-]+/', '', $key); |
||
| 421 | // Missing parameters |
||
| 422 | if (!$key || !$ident) { |
||
| 423 | return $item; |
||
| 424 | } |
||
| 425 | |||
| 426 | $table = $this->table(); |
||
| 427 | $query = sprintf(' |
||
| 428 | SELECT |
||
| 429 | * |
||
| 430 | FROM |
||
| 431 | `%s` |
||
| 432 | WHERE |
||
| 433 | `%s` = :ident |
||
| 434 | LIMIT |
||
| 435 | 1', $table, $key); |
||
| 436 | |||
| 437 | $binds = [ |
||
| 438 | 'ident' => $ident |
||
| 439 | ]; |
||
| 440 | |||
| 441 | return $this->loadItemFromQuery($query, $binds, $item); |
||
| 442 | } |
||
| 443 | |||
| 444 | /** |
||
| 445 | * Load item by the given query statement. |
||
| 446 | * |
||
| 447 | * @param string $query The SQL SELECT statement. |
||
| 448 | * @param array $binds Optional. The query parameters. |
||
| 449 | * @param StorableInterface $item Optional. Item (storable object) to load into. |
||
| 450 | * @throws PDOException If there is a query error. |
||
| 451 | * @return StorableInterface |
||
| 452 | */ |
||
| 453 | public function loadItemFromQuery($query, array $binds = [], StorableInterface $item = null) |
||
| 454 | { |
||
| 455 | if ($item !== null) { |
||
| 456 | $this->setModel($item); |
||
| 457 | } else { |
||
| 458 | $class = get_class($this->model()); |
||
| 459 | $item = new $class; |
||
| 460 | } |
||
| 461 | |||
| 462 | // Missing parameters |
||
| 463 | if (!$query) { |
||
| 464 | return $item; |
||
| 465 | } |
||
| 466 | |||
| 467 | $sth = $this->dbQuery($query, $binds); |
||
| 468 | if ($sth === false) { |
||
| 469 | throw new PDOException('Could not load item.'); |
||
| 470 | } |
||
| 471 | |||
| 472 | $data = $sth->fetch(PDO::FETCH_ASSOC); |
||
| 473 | if ($data) { |
||
| 474 | $item->setFlatData($data); |
||
| 475 | } |
||
| 476 | |||
| 477 | return $item; |
||
| 478 | } |
||
| 479 | |||
| 480 | /** |
||
| 481 | * Load items for the given model. |
||
| 482 | * |
||
| 483 | * @param StorableInterface|null $item Optional model. |
||
| 484 | * @return StorableInterface[] |
||
| 485 | */ |
||
| 486 | public function loadItems(StorableInterface $item = null) |
||
| 487 | { |
||
| 488 | if ($item !== null) { |
||
| 489 | $this->setModel($item); |
||
| 490 | } |
||
| 491 | |||
| 492 | $query = $this->sqlLoad(); |
||
| 493 | return $this->loadItemsFromQuery($query, [], $item); |
||
| 494 | } |
||
| 495 | |||
| 496 | /** |
||
| 497 | * Load items for the given query statement. |
||
| 498 | * |
||
| 499 | * @param string $query The SQL SELECT statement. |
||
| 500 | * @param array $binds This has to be done. |
||
| 501 | * @param StorableInterface|null $item Model Item. |
||
| 502 | * @return StorableInterface[] |
||
| 503 | */ |
||
| 504 | public function loadItemsFromQuery($query, array $binds = [], StorableInterface $item = null) |
||
| 505 | { |
||
| 506 | if ($item !== null) { |
||
| 507 | $this->setModel($item); |
||
| 508 | } |
||
| 509 | |||
| 510 | $items = []; |
||
| 511 | |||
| 512 | $model = $this->model(); |
||
| 513 | $dbh = $this->db(); |
||
| 514 | |||
| 515 | $this->logger->debug($query); |
||
| 516 | $sth = $dbh->prepare($query); |
||
| 517 | |||
| 518 | // @todo Binds |
||
| 519 | if (!empty($binds)) { |
||
| 520 | unset($binds); |
||
| 521 | } |
||
| 522 | |||
| 523 | $sth->execute(); |
||
| 524 | $sth->setFetchMode(PDO::FETCH_ASSOC); |
||
| 525 | |||
| 526 | $className = get_class($model); |
||
| 527 | while ($objData = $sth->fetch()) { |
||
| 528 | $obj = new $className; |
||
| 529 | $obj->setFlatData($objData); |
||
| 530 | $items[] = $obj; |
||
| 531 | } |
||
| 532 | |||
| 533 | return $items; |
||
| 534 | } |
||
| 535 | |||
| 536 | /** |
||
| 537 | * Save an item (create a new row) in storage. |
||
| 538 | * |
||
| 539 | * @param StorableInterface $item The object to save. |
||
| 540 | * @throws PDOException If a database error occurs. |
||
| 541 | * @return mixed The created item ID, otherwise FALSE. |
||
| 542 | */ |
||
| 543 | public function saveItem(StorableInterface $item) |
||
| 544 | { |
||
| 545 | if ($this->tableExists() === false) { |
||
| 546 | /** @todo Optionnally turn off for some models */ |
||
| 547 | $this->createTable(); |
||
| 548 | } |
||
| 549 | |||
| 550 | if ($item !== null) { |
||
| 551 | $this->setModel($item); |
||
| 552 | } |
||
| 553 | $model = $this->model(); |
||
| 554 | $table = $this->table(); |
||
| 555 | $struct = array_keys($this->tableStructure()); |
||
| 556 | $fields = $this->getModelFields($model); |
||
| 557 | |||
| 558 | $keys = []; |
||
| 559 | $values = []; |
||
| 560 | $binds = []; |
||
| 561 | $types = []; |
||
| 562 | foreach ($fields as $field) { |
||
| 563 | $key = $field->ident(); |
||
| 564 | if (in_array($key, $struct)) { |
||
| 565 | $keys[] = '`'.$key.'`'; |
||
| 566 | $values[] = ':'.$key.''; |
||
| 567 | $binds[$key] = $field->val(); |
||
| 568 | $types[$key] = $field->sqlPdoType(); |
||
| 569 | } |
||
| 570 | } |
||
| 571 | |||
| 572 | $query = ' |
||
| 573 | INSERT |
||
| 574 | INTO |
||
| 575 | `'.$table.'` |
||
| 576 | ('.implode(', ', $keys).') |
||
| 577 | VALUES |
||
| 578 | ('.implode(', ', $values).')'; |
||
| 579 | |||
| 580 | $result = $this->dbQuery($query, $binds, $types); |
||
| 581 | |||
| 582 | if ($result === false) { |
||
| 583 | throw new PDOException('Could not save item.'); |
||
| 584 | } else { |
||
| 585 | if ($model->id()) { |
||
| 586 | return $model->id(); |
||
| 587 | } else { |
||
| 588 | return $this->db()->lastInsertId(); |
||
| 589 | } |
||
| 590 | } |
||
| 591 | } |
||
| 592 | |||
| 593 | /** |
||
| 594 | * Update an item in storage. |
||
| 595 | * |
||
| 596 | * @param StorableInterface $item The object to update. |
||
| 597 | * @param array $properties The list of properties to update, if not all. |
||
| 598 | * @return boolean TRUE if the item was updated, otherwise FALSE. |
||
| 599 | */ |
||
| 600 | public function updateItem(StorableInterface $item, array $properties = null) |
||
| 601 | { |
||
| 602 | if ($item !== null) { |
||
| 603 | $this->setModel($item); |
||
| 604 | } |
||
| 605 | $model = $this->model(); |
||
| 606 | $table = $this->table(); |
||
| 607 | $struct = array_keys($this->tableStructure()); |
||
| 608 | $fields = $this->getModelFields($model, $properties); |
||
| 609 | |||
| 610 | $updates = []; |
||
| 611 | $binds = []; |
||
| 612 | $types = []; |
||
| 613 | foreach ($fields as $field) { |
||
| 614 | $key = $field->ident(); |
||
| 615 | if (in_array($key, $struct)) { |
||
| 616 | if ($key !== $model->key()) { |
||
| 617 | $param = ':'.$key; |
||
| 618 | $updates[] = '`'.$key.'` = '.$param; |
||
| 619 | } |
||
| 620 | $binds[$key] = $field->val(); |
||
| 621 | $types[$key] = $field->sqlPdoType(); |
||
| 622 | } else { |
||
| 623 | $this->logger->debug( |
||
| 624 | sprintf('Field "%s" not in table structure', $key) |
||
| 625 | ); |
||
| 626 | } |
||
| 627 | } |
||
| 628 | if (empty($updates)) { |
||
| 629 | $this->logger->warning( |
||
| 630 | 'Could not update items. No valid fields were set / available in database table.', |
||
| 631 | [ |
||
| 632 | 'properties' => $properties, |
||
| 633 | 'structure' => $struct |
||
| 634 | ] |
||
| 635 | ); |
||
| 636 | return false; |
||
| 637 | } |
||
| 638 | |||
| 639 | $binds[$model->key()] = $model->id(); |
||
| 640 | $types[$model->key()] = PDO::PARAM_STR; |
||
| 641 | |||
| 642 | $query = ' |
||
| 643 | UPDATE |
||
| 644 | `'.$table.'` |
||
| 645 | SET |
||
| 646 | '.implode(", \n\t", $updates).' |
||
| 647 | WHERE |
||
| 648 | `'.$model->key().'`=:'.$model->key().''; |
||
| 649 | |||
| 650 | $driver = $this->db()->getAttribute(PDO::ATTR_DRIVER_NAME); |
||
| 651 | if ($driver == self::MYSQL_DRIVER_NAME) { |
||
| 652 | $query .= "\n".'LIMIT 1'; |
||
| 653 | } |
||
| 654 | |||
| 655 | $result = $this->dbQuery($query, $binds, $types); |
||
| 656 | |||
| 657 | if ($result === false) { |
||
| 658 | return false; |
||
| 659 | } else { |
||
| 660 | return true; |
||
| 661 | } |
||
| 662 | } |
||
| 663 | |||
| 664 | /** |
||
| 665 | * Delete an item from storage. |
||
| 666 | * |
||
| 667 | * @param StorableInterface $item Optional item to delete. If none, the current model object will be used. |
||
| 668 | * @throws UnexpectedValueException If the item does not have an ID. |
||
| 669 | * @return boolean TRUE if the item was deleted, otherwise FALSE. |
||
| 670 | */ |
||
| 671 | public function deleteItem(StorableInterface $item = null) |
||
| 672 | { |
||
| 673 | if ($item !== null) { |
||
| 674 | $this->setModel($item); |
||
| 675 | } |
||
| 676 | |||
| 677 | $model = $this->model(); |
||
| 678 | |||
| 679 | if (!$model->id()) { |
||
| 680 | throw new UnexpectedValueException( |
||
| 681 | sprintf('Can not delete "%s" item. No ID.', get_class($model)) |
||
| 682 | ); |
||
| 683 | } |
||
| 684 | |||
| 685 | $key = $model->key(); |
||
| 686 | $table = $this->table(); |
||
| 687 | $query = ' |
||
| 688 | DELETE FROM |
||
| 689 | `'.$table.'` |
||
| 690 | WHERE |
||
| 691 | `'.$key.'` = :id'; |
||
| 692 | |||
| 693 | $driver = $this->db()->getAttribute(PDO::ATTR_DRIVER_NAME); |
||
| 694 | if ($driver == self::MYSQL_DRIVER_NAME) { |
||
| 695 | $query .= "\n".'LIMIT 1'; |
||
| 696 | } |
||
| 697 | |||
| 698 | $binds = [ |
||
| 699 | 'id' => $model->id() |
||
| 700 | ]; |
||
| 701 | |||
| 702 | $result = $this->dbQuery($query, $binds); |
||
| 703 | |||
| 704 | if ($result === false) { |
||
| 705 | return false; |
||
| 706 | } else { |
||
| 707 | return true; |
||
| 708 | } |
||
| 709 | } |
||
| 710 | |||
| 711 | /** |
||
| 712 | * Execute a SQL query, with PDO, and returns the PDOStatement. |
||
| 713 | * |
||
| 714 | * If the query fails, this method will return false. |
||
| 715 | * |
||
| 716 | * @param string $query The SQL query to executed. |
||
| 717 | * @param array $binds Optional. Query parameter binds. |
||
| 718 | * @param array $types Optional. Types of parameter bindings. |
||
| 719 | * @return \PDOStatement|false The PDOStatement, otherwise FALSE. |
||
| 720 | */ |
||
| 721 | public function dbQuery($query, array $binds = [], array $types = []) |
||
| 722 | { |
||
| 723 | $this->logger->debug($query, $binds); |
||
| 724 | |||
| 725 | $sth = $this->dbPrepare($query, $binds, $types); |
||
| 726 | if ($sth === false) { |
||
| 727 | return false; |
||
| 728 | } |
||
| 729 | |||
| 730 | $result = $sth->execute(); |
||
| 731 | if ($result === false) { |
||
| 732 | return false; |
||
| 733 | } |
||
| 734 | |||
| 735 | return $sth; |
||
| 736 | } |
||
| 737 | |||
| 738 | /** |
||
| 739 | * Prepare an SQL query, with PDO, and return the PDOStatement. |
||
| 740 | * |
||
| 741 | * If the preparation fails, this method will return false. |
||
| 742 | * |
||
| 743 | * @param string $query The SQL query to executed. |
||
| 744 | * @param array $binds Optional. Query parameter binds. |
||
| 745 | * @param array $types Optional. Types of parameter bindings. |
||
| 746 | * @return \PDOStatement|false The PDOStatement, otherwise FALSE. |
||
| 747 | */ |
||
| 748 | public function dbPrepare($query, array $binds = [], array $types = []) |
||
| 769 | } |
||
| 770 | |||
| 771 | /** |
||
| 772 | * Compile the SELECT statement for fetching one or more objects. |
||
| 773 | * |
||
| 774 | * @throws UnexpectedValueException If the source does not have a table defined. |
||
| 775 | * @return string |
||
| 776 | */ |
||
| 777 | public function sqlLoad() |
||
| 778 | { |
||
| 779 | if (!$this->hasTable()) { |
||
| 780 | throw new UnexpectedValueException( |
||
| 781 | 'Can not get SQL SELECT clause. No table defined.' |
||
| 782 | ); |
||
| 783 | } |
||
| 784 | |||
| 785 | $selects = $this->sqlSelect(); |
||
| 786 | $tables = $this->sqlFrom(); |
||
| 787 | $filters = $this->sqlFilters(); |
||
| 788 | $orders = $this->sqlOrders(); |
||
| 789 | $limits = $this->sqlPagination(); |
||
| 790 | |||
| 791 | $query = 'SELECT '.$selects.' FROM '.$tables.$filters.$orders.$limits; |
||
| 792 | return $query; |
||
| 793 | } |
||
| 794 | |||
| 795 | /** |
||
| 796 | * Compile the SELECT statement for fetching the number of objects. |
||
| 797 | * |
||
| 798 | * @throws UnexpectedValueException If the source does not have a table defined. |
||
| 799 | * @return string |
||
| 800 | */ |
||
| 801 | public function sqlLoadCount() |
||
| 802 | { |
||
| 803 | if (!$this->hasTable()) { |
||
| 804 | throw new UnexpectedValueException( |
||
| 805 | 'Can not get SQL count. No table defined.' |
||
| 806 | ); |
||
| 807 | } |
||
| 808 | |||
| 809 | $tables = $this->sqlFrom(); |
||
| 810 | $filters = $this->sqlFilters(); |
||
| 811 | |||
| 812 | $query = 'SELECT COUNT(*) FROM '.$tables.$filters; |
||
| 813 | return $query; |
||
| 814 | } |
||
| 815 | |||
| 816 | /** |
||
| 817 | * Compile the SELECT clause. |
||
| 818 | * |
||
| 819 | * @throws UnexpectedValueException If the clause has no selectable fields. |
||
| 820 | * @return string |
||
| 821 | */ |
||
| 822 | public function sqlSelect() |
||
| 823 | { |
||
| 824 | $properties = $this->properties(); |
||
| 825 | if (empty($properties)) { |
||
| 826 | return self::DEFAULT_TABLE_ALIAS.'.*'; |
||
| 827 | } |
||
| 828 | |||
| 829 | $parts = []; |
||
| 830 | foreach ($properties as $key) { |
||
| 831 | $parts[] = Expression::quoteIdentifier($key, self::DEFAULT_TABLE_ALIAS); |
||
| 832 | } |
||
| 833 | |||
| 834 | if (empty($parts)) { |
||
| 835 | throw new UnexpectedValueException( |
||
| 836 | 'Can not get SQL SELECT clause. No valid properties.' |
||
| 837 | ); |
||
| 838 | } |
||
| 839 | |||
| 840 | $clause = implode(', ', $parts); |
||
| 841 | |||
| 842 | return $clause; |
||
| 843 | } |
||
| 844 | |||
| 845 | /** |
||
| 846 | * Compile the FROM clause. |
||
| 847 | * |
||
| 848 | * @throws UnexpectedValueException If the source does not have a table defined. |
||
| 849 | * @return string |
||
| 850 | */ |
||
| 851 | public function sqlFrom() |
||
| 861 | } |
||
| 862 | |||
| 863 | /** |
||
| 864 | * Compile the WHERE clause. |
||
| 865 | * |
||
| 866 | * @todo [2016-02-19] Use bindings for filters value |
||
| 867 | * @return string |
||
| 868 | */ |
||
| 869 | public function sqlFilters() |
||
| 870 | { |
||
| 871 | if (!$this->hasFilters()) { |
||
| 872 | return ''; |
||
| 873 | } |
||
| 874 | |||
| 875 | $criteria = $this->createFilter([ |
||
| 876 | 'filters' => $this->filters() |
||
| 877 | ]); |
||
| 878 | |||
| 879 | $sql = $criteria->sql(); |
||
| 880 | if (strlen($sql) > 0) { |
||
| 881 | $sql = ' WHERE '.$sql; |
||
| 882 | } |
||
| 883 | |||
| 884 | return $sql; |
||
| 885 | } |
||
| 886 | |||
| 887 | /** |
||
| 888 | * Compile the ORDER BY clause. |
||
| 889 | * |
||
| 890 | * @return string |
||
| 891 | */ |
||
| 892 | public function sqlOrders() |
||
| 893 | { |
||
| 894 | if (!$this->hasOrders()) { |
||
| 895 | return ''; |
||
| 896 | } |
||
| 897 | |||
| 898 | $parts = []; |
||
| 899 | foreach ($this->orders() as $order) { |
||
| 900 | if (!$order instanceof DatabaseOrder) { |
||
| 901 | $order = $this->createOrder($order->data()); |
||
| 902 | } |
||
| 903 | |||
| 904 | $sql = $order->sql(); |
||
| 905 | if (strlen($sql) > 0) { |
||
| 906 | $parts[] = $sql; |
||
| 907 | } |
||
| 908 | } |
||
| 909 | |||
| 910 | if (empty($parts)) { |
||
| 911 | return ''; |
||
| 912 | } |
||
| 913 | |||
| 914 | return ' ORDER BY '.implode(', ', $parts); |
||
| 915 | } |
||
| 916 | |||
| 917 | /** |
||
| 918 | * Compile the LIMIT clause. |
||
| 919 | * |
||
| 920 | * @return string |
||
| 921 | */ |
||
| 922 | public function sqlPagination() |
||
| 923 | { |
||
| 924 | $pager = $this->pagination(); |
||
| 925 | if (!$pager instanceof DatabasePagination) { |
||
| 926 | $pager = $this->createPagination($pager->data()); |
||
| 927 | } |
||
| 928 | |||
| 929 | $sql = $pager->sql(); |
||
| 930 | if (strlen($sql) > 0) { |
||
| 931 | $sql = ' '.$sql; |
||
| 932 | } |
||
| 933 | |||
| 934 | return $sql; |
||
| 935 | } |
||
| 936 | |||
| 937 | /** |
||
| 938 | * Create a new filter expression. |
||
| 939 | * |
||
| 940 | * @param array $data Optional expression data. |
||
| 941 | * @return DatabaseFilter |
||
| 942 | */ |
||
| 943 | protected function createFilter(array $data = null) |
||
| 950 | } |
||
| 951 | |||
| 952 | /** |
||
| 953 | * Create a new order expression. |
||
| 954 | * |
||
| 955 | * @param array $data Optional expression data. |
||
| 956 | * @return DatabaseOrder |
||
| 957 | */ |
||
| 958 | protected function createOrder(array $data = null) |
||
| 959 | { |
||
| 960 | $order = new DatabaseOrder(); |
||
| 961 | if ($data !== null) { |
||
| 962 | $order->setData($data); |
||
| 963 | } |
||
| 964 | return $order; |
||
| 965 | } |
||
| 966 | |||
| 967 | /** |
||
| 968 | * Create a new pagination clause. |
||
| 969 | * |
||
| 970 | * @param array $data Optional clause data. |
||
| 971 | * @return DatabasePagination |
||
| 972 | */ |
||
| 973 | protected function createPagination(array $data = null) |
||
| 974 | { |
||
| 975 | $pagination = new DatabasePagination(); |
||
| 976 | if ($data !== null) { |
||
| 977 | $pagination->setData($data); |
||
| 978 | } |
||
| 979 | return $pagination; |
||
| 980 | } |
||
| 981 | |||
| 982 | /** |
||
| 983 | * Create a new database source config. |
||
| 984 | * |
||
| 985 | * @see \Charcoal\Config\ConfigurableTrait |
||
| 986 | * @param array $data Optional data. |
||
| 987 | * @return DatabaseSourceConfig |
||
| 988 | */ |
||
| 989 | public function createConfig(array $data = null) |
||
| 993 | } |
||
| 994 | } |
||
| 995 |
Loading history...
