<?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;
}
}
|
Download
