PHP Classes

File: classes/db/db-table.php

Recommend this page to a friend!
  Classes of Gonzalo Chumillas   DbTable   classes/db/db-table.php   Download  
File: classes/db/db-table.php
Role: Class source
Content type: text/plain
Description: Class source
Class: DbTable
Update table records in multiple related tables
Author: By
Last change:
Date: 9 years ago
Size: 11,935 bytes
 

Contents

Class file image Download
<?php /** * This file is part of Soloproyectos common library. * * @author Gonzalo Chumillas <gchumillas@email.com> * @license https://github.com/soloproyectos/php.common-libs/blob/master/LICENSE BSD 2-Clause License * @link https://github.com/soloproyectos/php.common-libs */ namespace com\soloproyectos\common\db; use \ArrayIterator; use \Exception; use \Countable; use \IteratorAggregate; /** * Class DbTable. * * This class represents a database table. We can traverse, edit, insert or delete records. * * @package Db * @author Gonzalo Chumillas <gchumillas@email.com> * @license https://github.com/soloproyectos/php.common-libs/blob/master/LICENSE BSD 2-Clause License * @link https://github.com/soloproyectos/php.common-libs */ class DbTable implements IteratorAggregate { use DbTableColumnManager; /** * Database connector. * @var Db */ private $_db = null; /** * Datasource. * @var DbDataSource */ private $_dataSource = null; /** * List of columns. * @var array of DbColumn */ private $_columns = array(); /** * Table name. * @var string */ private $_name = ""; /** * List of filters. * @var array of string */ private $_filters = array(); /** * Order by. * @var string|null */ private $_order = null; /** * Is the table updated? * @var boolean */ private $_isUpdated = false; /** * Insertion is in progress. * @var boolean */ private $_isInsertMode = false; /** * Constructor. * * @param DbConnector $db Database connector * @param string $tableName Table name * @param scalar|DbColumn|array $filters List of filters (not required) * @param string $pkName Primary key name (default is 'id') */ public function __construct($db, $tableName, $filters = null, $pkName = "id") { $this->_db = $db; $this->_name = strtolower(trim($tableName)); $this->_primaryKey = $this->_regColumn($pkName); if ($filters !== null && !is_array($filters)) { $filters = array($pkName => $filters); } if ($filters !== null) { if (!is_array($filters)) { $filters = array($pkName => $filters); } foreach ($filters as $colName => $colValue) { $column = $this->_regColumn($colName); $leftColumn = $colValue instanceof DbColumn? $colValue : new DbColumnConstant($colValue); // links columns in both directions $column->setLeftLinkedColumn($leftColumn); $leftColumn->setRightLinkedColumn($column); } } } /** * Implements IteratorAggregate::getIterator() * * @return Traversable */ public function getIterator() { $items = array(); if (!$this->_isUpdated) { $this->refresh(); } foreach ($this->_dataSource as $row) { $pkName = $this->_primaryKey->getName(); $pkValue = $row[$pkName]; array_push($items, new DbTable($this->_db, $this->_name, array($pkName => $pkValue))); } return new ArrayIterator($items); } /** * Inserts a new row. * * @return void */ public function insert() { $this->_isInsertMode = true; // previously, inserts all linked tables foreach ($this->_columns as $column) { $rightColumn = $column->getRightLinkedColumn(); if ($rightColumn != null) { $tableLink = $rightColumn->getTable(); if ($tableLink->_hasChanged()) { $tableLink->insert(); } } } // list of columns that have changed or have 'left linked' columns $columns = array_filter( $this->_columns, function ($column) { return $column->getLeftLinkedColumn() != null || $column->hasChanged(); } ); // list of columns separated by commas $columnsList = implode( ", ", array_map( function ($column) { return DbHelper::quoteId($column->getName()); }, $columns ) ); // list of values separated by commas $valuesList = implode( ", ", array_map( function ($column) { return $this->_db->quote($column->getValue()); }, $columns ) ); // makes and executes the SQL statement $tableName = DbHelper::quoteId($this->_name); $sql = "insert into $tableName ($columnsList) values ($valuesList)"; $this->_db->exec($sql); // gets the last inserted id $insertId = $this->_primaryKey->hasChanged() ? $this->_primaryKey->getValue() : $this->_db->getLastInsertId(); $leftColumn = $this->_primaryKey->getLeftLinkedColumn(); if ($leftColumn != null) { $leftColumn->setValue($insertId); } $firstLeftColumn = $this->_primaryKey; while ($firstLeftColumn->getLeftLinkedColumn() != null) { $firstLeftColumn = $firstLeftColumn->getLeftLinkedColumn(); } $firstLeftColumn->setLeftLinkedColumn(new DbColumnConstant($insertId)); $this->_resetColumns(); $this->_isInsertMode = false; } /** * Updates the current row. * * @return void */ public function update() { // previously, saves all linked tables foreach ($this->_columns as $column) { $rightColumn = $column->getRightLinkedColumn(); if ($rightColumn != null) { $tableLink = $rightColumn->getTable(); // inserts or updates if ($tableLink->_hasChanged()) { if ($tableLink->_primaryKey->getOriginalValue() > 0) { $tableLink->update(); } else { $tableLink->insert(); } } } } // gets modified columns $columns = array_filter( $this->_columns, function ($column) { return $column->hasChanged(); } ); if (count($columns) > 0) { // pairs of columns and values $columnsValuesList = implode( ", ", array_map( function ($column) { $name = DbHelper::quoteId($column->getName()); $value = $this->_db->quote($column->getValue()); return "$name = $value"; }, $columns ) ); // makes and executes the SQL statement $tableName = DbHelper::quoteId($this->_name); $pkName = DbHelper::quoteId($this->_primaryKey->getName()); $pkValue = $this->_db->quote($this->_primaryKey->getOriginalValue()); $sql = "update $tableName set $columnsValuesList where $pkName = $pkValue"; $this->_db->exec($sql); $this->_resetColumns(); } } /** * Deletes the current row. * * @return void */ public function delete() { // makes and executes the SQL statement $tableName = DbHelper::quoteId($this->_name); $pkName = DbHelper::quoteId($this->_primaryKey->getName()); $pkValue = $this->_db->quote($this->_primaryKey->getOriginalValue()); $sql = "delete from $tableName where $pkName = $pkValue"; $this->_db->exec($sql); $this->_resetColumns(); } /** * Fetches rows from database. * * @return void */ public function _fetchRows() { // list of columns separated by commas $columnsList = implode( ", ", array_map( function ($column) { return DbHelper::quoteId($column->getName()); }, $this->_columns ) ); // filters $filters = $this->_filters; foreach ($this->_columns as $column) { $leftColumn = $column->getLeftLinkedColumn(); if ($leftColumn != null) { $col = DbHelper::quoteId($column->getName()); $val = $this->_db->quote($leftColumn->getOriginalValue()); array_push($filters, "$col = $val"); } } $filtersList = implode(" and ", $filters); // makes the SQL sentence $tableName = DbHelper::quoteId($this->_name); $sql = "select $columnsList from $tableName"; if (strlen($filtersList) > 0) { $sql .= " where $filtersList"; } if ($this->_order != null) { $sql .= " order by " . $this->_order; } $this->_dataSource = new DbDataSource($this->_db, $sql); $this->_isUpdated = true; } /** * Refreshes the table columns. * * @return void */ public function refresh() { $this->_resetColumns(); $this->_fetchRows(); } /** * Adds a filter. * * @param string $filter Filter * * @return void */ public function addFilter($filter) { array_push($this->_filters, $filter); $this->_isUpdated = false; } /** * Sets an 'order by'. * * @param string $exp 'Order by' expression * * @return void */ public function setOrder($exp) { $this->_order = $exp; $this->_isUpdated = false; } /** * Gets a column value by its name. * * @param string $columnName Column name * * @return string */ public function getColumnValue($columnName) { $ret = null; if (!$this->_isInsertMode) { if (!$this->_isUpdated) { $this->_fetchRows(); } $ret = $this->_dataSource[$columnName]; } return $ret; } /** * Resets the table columns. * * @return void */ private function _resetColumns() { $this->_isUpdated = false; foreach ($this->_columns as $column) { $column->reset(); $rightColumn = $column->getRightLinkedColumn(); if ($rightColumn != null) { $rightTable = $rightColumn->getTable(); $rightTable->_resetColumns(); } } } /** * Has the table changed? * * The table has changed if any of its columns (or right-linked columns) has changed. * * @return boolean */ private function _hasChanged() { foreach ($this->_columns as $column) { if ($column->hasChanged()) { return true; } $rightColumn = $column->getRightLinkedColumn(); if ($rightColumn != null) { $rightTable = $rightColumn->getTable(); if ($rightTable->_hasChanged()) { return true; } } } return false; } }