|
<?php |
|
|
|
namespace PicoDb; |
|
|
|
use PDO; |
|
use Closure; |
|
use PicoDb\Builder\ConditionBuilder; |
|
use PicoDb\Builder\InsertBuilder; |
|
use PicoDb\Builder\UpdateBuilder; |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
class Table |
|
{ |
|
|
|
|
|
|
|
|
|
|
|
|
|
const SORT_ASC = 'ASC'; |
|
const SORT_DESC = 'DESC'; |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
protected $conditionBuilder; |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
protected $db; |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
protected $name = ''; |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
private $columns = array(); |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
private $sumColumns = array(); |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
private $sqlLimit = ''; |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
private $sqlOffset = ''; |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
private $sqlOrder = ''; |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
private $sqlSelect = ''; |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
private $sqlTop = ''; |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
private $joins = array(); |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
private $distinct = false; |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
private $groupBy = array(); |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
private $callback = null; |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
public function __construct(Database $db, $name) |
|
{ |
|
$this->db = $db; |
|
$this->name = $name; |
|
$this->conditionBuilder = new ConditionBuilder($db); |
|
} |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
public function getName() |
|
{ |
|
return $this->name; |
|
} |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
public function getConditionBuilder() |
|
{ |
|
return $this->conditionBuilder; |
|
} |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
public function save(array $data) |
|
{ |
|
return $this->conditionBuilder->hasCondition() ? $this->update($data) : $this->insert($data); |
|
} |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
public function update(array $data = array()) |
|
{ |
|
$values = array_merge(array_values($data), array_values($this->sumColumns), $this->conditionBuilder->getValues()); |
|
$sql = UpdateBuilder::getInstance($this->db, $this->conditionBuilder) |
|
->withTable($this->name) |
|
->withColumns(array_keys($data)) |
|
->withSumColumns(array_keys($this->sumColumns)) |
|
->build(); |
|
|
|
return $this->db->execute($sql, $values) !== false; |
|
} |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
public function insert(array $data) |
|
{ |
|
return $this->db->getStatementHandler() |
|
->withSql(InsertBuilder::getInstance($this->db, $this->conditionBuilder) |
|
->withTable($this->name) |
|
->withColumns(array_keys($data)) |
|
->build() |
|
) |
|
->withNamedParams($data) |
|
->execute() !== false; |
|
} |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
public function persist(array $data) |
|
{ |
|
if ($this->insert($data)) { |
|
return $this->db->getLastId(); |
|
} |
|
|
|
return false; |
|
} |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
public function remove() |
|
{ |
|
$sql = sprintf( |
|
'DELETE FROM %s %s', |
|
$this->db->escapeIdentifier($this->name), |
|
$this->conditionBuilder->build() |
|
); |
|
|
|
$result = $this->db->execute($sql, $this->conditionBuilder->getValues()); |
|
return $result->rowCount() > 0; |
|
} |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
public function findAll() |
|
{ |
|
$rq = $this->db->execute($this->buildSelectQuery(), $this->conditionBuilder->getValues()); |
|
$results = $rq->fetchAll(PDO::FETCH_ASSOC); |
|
|
|
if (is_callable($this->callback) && ! empty($results)) { |
|
return call_user_func($this->callback, $results); |
|
} |
|
|
|
return $results; |
|
} |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
public function findAllByColumn($column) |
|
{ |
|
$this->columns = array($column); |
|
$rq = $this->db->execute($this->buildSelectQuery(), $this->conditionBuilder->getValues()); |
|
|
|
return $rq->fetchAll(PDO::FETCH_COLUMN, 0); |
|
} |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
public function findOne() |
|
{ |
|
$this->limit(1); |
|
$result = $this->findAll(); |
|
|
|
return isset($result[0]) ? $result[0] : null; |
|
} |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
public function findOneColumn($column) |
|
{ |
|
$this->limit(1); |
|
$this->columns = array($column); |
|
|
|
return $this->db->execute($this->buildSelectQuery(), $this->conditionBuilder->getValues())->fetchColumn(); |
|
} |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
public function subquery($sql, $alias) |
|
{ |
|
$this->columns[] = '('.$sql.') AS '.$this->db->escapeIdentifier($alias); |
|
return $this; |
|
} |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
public function exists() |
|
{ |
|
$sql = sprintf( |
|
'SELECT 1 FROM %s '.implode(' ', $this->joins).$this->conditionBuilder->build(), |
|
$this->db->escapeIdentifier($this->name) |
|
); |
|
|
|
$rq = $this->db->execute($sql, $this->conditionBuilder->getValues()); |
|
$result = $rq->fetchColumn(); |
|
|
|
return $result ? true : false; |
|
} |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
public function count() |
|
{ |
|
$sql = sprintf( |
|
'SELECT COUNT(*) FROM %s '.implode(' ', $this->joins).$this->conditionBuilder->build().$this->sqlOrder.$this->sqlLimit.$this->sqlOffset, |
|
$this->db->escapeIdentifier($this->name) |
|
); |
|
|
|
$rq = $this->db->execute($sql, $this->conditionBuilder->getValues()); |
|
$result = $rq->fetchColumn(); |
|
|
|
return $result ? (int) $result : 0; |
|
} |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
public function sum($column) |
|
{ |
|
$sql = sprintf( |
|
'SELECT SUM(%s) FROM %s '.implode(' ', $this->joins).$this->conditionBuilder->build().$this->sqlOrder.$this->sqlLimit.$this->sqlOffset, |
|
$this->db->escapeIdentifier($column), |
|
$this->db->escapeIdentifier($this->name) |
|
); |
|
|
|
$rq = $this->db->execute($sql, $this->conditionBuilder->getValues()); |
|
$result = $rq->fetchColumn(); |
|
|
|
return $result ? (float) $result : 0; |
|
} |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
public function increment($column, $value) |
|
{ |
|
$sql = sprintf( |
|
'UPDATE %s SET %s=%s+%d '.$this->conditionBuilder->build(), |
|
$this->db->escapeIdentifier($this->name), |
|
$this->db->escapeIdentifier($column), |
|
$this->db->escapeIdentifier($column), |
|
$value |
|
); |
|
|
|
return $this->db->execute($sql, $this->conditionBuilder->getValues()) !== false; |
|
} |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
public function decrement($column, $value) |
|
{ |
|
$sql = sprintf( |
|
'UPDATE %s SET %s=%s-%d '.$this->conditionBuilder->build(), |
|
$this->db->escapeIdentifier($this->name), |
|
$this->db->escapeIdentifier($column), |
|
$this->db->escapeIdentifier($column), |
|
$value |
|
); |
|
|
|
return $this->db->execute($sql, $this->conditionBuilder->getValues()) !== false; |
|
} |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
public function join($table, $foreign_column, $local_column, $local_table = '', $alias = '') |
|
{ |
|
$this->joins[] = sprintf( |
|
'LEFT JOIN %s ON %s=%s', |
|
$this->db->escapeIdentifier($table), |
|
$this->db->escapeIdentifier($alias ?: $table).'.'.$this->db->escapeIdentifier($foreign_column), |
|
$this->db->escapeIdentifier($local_table ?: $this->name).'.'.$this->db->escapeIdentifier($local_column) |
|
); |
|
|
|
return $this; |
|
} |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
public function left($table1, $alias1, $column1, $table2, $column2) |
|
{ |
|
$this->joins[] = sprintf( |
|
'LEFT JOIN %s AS %s ON %s=%s', |
|
$this->db->escapeIdentifier($table1), |
|
$this->db->escapeIdentifier($alias1), |
|
$this->db->escapeIdentifier($alias1).'.'.$this->db->escapeIdentifier($column1), |
|
$this->db->escapeIdentifier($table2).'.'.$this->db->escapeIdentifier($column2) |
|
); |
|
|
|
return $this; |
|
} |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
public function inner($table1, $alias1, $column1, $table2, $column2) |
|
{ |
|
$this->joins[] = sprintf( |
|
'JOIN %s AS %s ON %s=%s', |
|
$this->db->escapeIdentifier($table1), |
|
$this->db->escapeIdentifier($alias1), |
|
$this->db->escapeIdentifier($alias1).'.'.$this->db->escapeIdentifier($column1), |
|
$this->db->escapeIdentifier($table2).'.'.$this->db->escapeIdentifier($column2) |
|
); |
|
|
|
return $this; |
|
} |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
public function orderBy($column, $order = self::SORT_ASC) |
|
{ |
|
$order = strtoupper($order); |
|
$order = $order === self::SORT_ASC || $order === self::SORT_DESC ? $order : self::SORT_ASC; |
|
|
|
if ($this->sqlOrder === '') { |
|
$this->sqlOrder = ' ORDER BY '.$this->db->escapeIdentifier($column).' '.$order; |
|
} |
|
else { |
|
$this->sqlOrder .= ', '.$this->db->escapeIdentifier($column).' '.$order; |
|
} |
|
|
|
return $this; |
|
} |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
public function asc($column) |
|
{ |
|
$this->orderBy($column, self::SORT_ASC); |
|
return $this; |
|
} |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
public function desc($column) |
|
{ |
|
$this->orderBy($column, self::SORT_DESC); |
|
return $this; |
|
} |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
public function limit($value) |
|
{ |
|
if (! is_null($value)) { |
|
if ($this->db->getDriver()->useTop) { |
|
$this->sqlTop = ' TOP ('.(int) $value.') '; |
|
} else { |
|
$this->sqlLimit = ' LIMIT '.(int) $value; |
|
} |
|
} |
|
|
|
return $this; |
|
} |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
public function offset($value) |
|
{ |
|
if (! is_null($value) && is_int($value) && $value > 0) { |
|
$this->sqlOffset = ' OFFSET '.(int) $value; |
|
} |
|
|
|
return $this; |
|
} |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
public function groupBy() |
|
{ |
|
$this->groupBy = func_get_args(); |
|
return $this; |
|
} |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
public function select($select) |
|
{ |
|
$this->sqlSelect = $select; |
|
return $this; |
|
} |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
public function columns() |
|
{ |
|
$this->columns = func_get_args(); |
|
return $this; |
|
} |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
public function sumColumn($column, $value) |
|
{ |
|
$this->sumColumns[$column] = $value; |
|
return $this; |
|
} |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
public function distinct() |
|
{ |
|
$this->columns = func_get_args(); |
|
$this->distinct = true; |
|
return $this; |
|
} |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
public function callback($callback) |
|
{ |
|
$this->callback = $callback; |
|
return $this; |
|
} |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
public function buildSelectQuery() |
|
{ |
|
if (empty($this->sqlSelect)) { |
|
$this->columns = $this->db->escapeIdentifierList($this->columns, $this->name); |
|
$this->sqlSelect = ($this->distinct ? 'DISTINCT ' : '').(empty($this->columns) ? '*' : implode(', ', $this->columns)); |
|
} |
|
|
|
$this->groupBy = $this->db->escapeIdentifierList($this->groupBy); |
|
|
|
return trim(sprintf( |
|
'SELECT %s %s FROM %s %s %s %s %s %s %s', |
|
$this->sqlTop, |
|
$this->sqlSelect, |
|
$this->db->escapeIdentifier($this->name), |
|
implode(' ', $this->joins), |
|
$this->conditionBuilder->build(), |
|
empty($this->groupBy) ? '' : 'GROUP BY '.implode(', ', $this->groupBy), |
|
$this->sqlOrder, |
|
$this->sqlLimit, |
|
$this->sqlOffset |
|
)); |
|
} |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
public function __call($name, array $arguments) |
|
{ |
|
call_user_func_array(array($this->conditionBuilder, $name), $arguments); |
|
return $this; |
|
} |
|
|
|
|
|
|
|
|
|
public function __clone() |
|
{ |
|
$this->conditionBuilder = clone $this->conditionBuilder; |
|
} |
|
} |
|
|