/QueryBuilder.php
PHP | 546 lines | 290 code | 88 blank | 168 comment | 35 complexity | 02b1e689c1a146cbf772420a0f0b180d MD5 | raw file
- <?php
- /**
- *
- * @package OpenFlame Dbal
- * @copyright (c) 2011 openflame-project.org
- * @license http://opensource.org/licenses/mit-license.php The MIT License
- * @link https://github.com/OpenFlame/OpenFlame-Dbal
- *
- * Minimum Requirement: PHP 5.3.0
- */
- namespace OpenFlame\Dbal;
- /**
- * OpenFlame Dbal - Query Builder
- * Wraps around PDO to create an interface to query the database
- *
- *
- * @license http://opensource.org/licenses/mit-license.php The MIT License
- * @link https://github.com/OpenFlame/OpenFlame-Dbal
- */
- class QueryBuilder extends Query
- {
- /*
- * @var flag - Type of query (determined by the first clause)
- */
- protected $type = -1;
- /*
- * @var array - Fields to select
- */
- protected $select = array();
- /*
- * @var array - Tables that being affect in this query
- */
- protected $tables = array();
- /*
- * @var array - Sets
- */
- protected $sets = array();
- /*
- * @var array - Raw sets
- */
- protected $rawSets = array();
- /*
- * @var array - Rows for insert
- */
- protected $rows = array();
- /*
- * @var array - Complex array for wheres
- */
- protected $wheres = array();
- /*
- * Limits and offsets
- */
- protected $limit = 0;
- protected $offset = 0;
- /*
- * @var string - Fields to order by
- */
- protected $orderBy = '';
- protected $orderDirection = '';
- /*
- * consts - Query types
- */
- const TYPE_SELECT = 0;
- const TYPE_UPDATE = 1;
- const TYPE_INSERT = 2;
- const TYPE_MULTII = 3;
- const TYPE_DELETE = 4;
- const TYPE_UPSERT = 5;
- /**
- * Start a SELECT statement
- * @param mixed - Fields to select
- * @return \OpenFlame\Dbal\QueryBuilder - Provides a fluent interface.
- */
- public function select($fields)
- {
- $this->select = array_merge($this->select, $this->normalizeArray($fields));
- $this->type = static::TYPE_SELECT;
- return $this;
- }
- /**
- * Start an UPDATE statement
- * @param mixed - tables
- * @return \OpenFlame\Dbal\QueryBuilder - Provides a fluent interface.
- */
- public function update($tables)
- {
- $this->tables = array_merge($this->tables, $this->normalizeArray($tables));
- $this->type = static::TYPE_UPDATE;
- return $this;
- }
- /**
- * Start an INSERT statement
- * @param mixed - tables
- * @return \OpenFlame\Dbal\QueryBuilder - Provides a fluent interface.
- */
- public function insert($table)
- {
- $this->tables = array_slice($this->normalizeArray($table), 0, 1);
- $this->type = static::TYPE_INSERT;
- return $this;
- }
- /**
- * Start an INSERT statement
- * @param mixed - tables
- * @return \OpenFlame\Dbal\QueryBuilder - Provides a fluent interface.
- */
- public function multiInsert($table)
- {
- $this->tables = array_slice($this->normalizeArray($table), 0, 1);
- $this->type = static::TYPE_MULTII;
- return $this;
- }
- /**
- * Start an UPSERT statement
- * @param mixed - tables
- * @return \OpenFlame\Dbal\QueryBuilder - Provides a fluent interface.
- */
- public function upsert($table)
- {
- $this->tables = array_slice($this->normalizeArray($table), 0, 1);
- $this->type = static::TYPE_UPSERT;
- return $this;
- }
- /**
- * Start a DELETE statement
- * @param mixed - tables
- * @return \OpenFlame\Dbal\QueryBuilder - Provides a fluent interface.
- */
- public function delete($tables)
- {
- $this->tables = $this->normalizeArray($tables);
- $this->type = static::TYPE_DELETE;
- return $this;
- }
- /**
- * FROM clause
- * @param mixed - tables
- * @return \OpenFlame\Dbal\QueryBuilder - Provides a fluent interface.
- */
- public function from($tables)
- {
- $this->tables = array_merge($this->tables, $this->normalizeArray($tables));
- return $this;
- }
- /**
- * SET clause
- * @param mixed - key/vals
- * @return \OpenFlame\Dbal\QueryBuilder - Provides a fluent interface.
- */
- public function set()
- {
- $args = func_get_args();
- switch($this->type)
- {
- case static::TYPE_MULTII:
- if(!is_array($args[0]))
- {
- throw new \LogicException("Argument to QueryBuilder::set() must be an array when Multi-INSERTing.");
- }
- $this->rows[] = $args[0];
- break;
-
- case static::TYPE_INSERT:
- if(!is_array($args[0]))
- {
- throw new \LogicException("Argument to QueryBuilder::set() must be an array when INSERTing.");
- }
- $this->rows[0] = $args[0];
- break;
- default:
- $this->sets = array_merge($this->sets, $this->inputKeyVals($args));
- break;
- }
- return $this;
- }
- /*
- * Limit
- * @param int $limit
- * @return \OpenFlame\Dbal\QueryBuilder - Provides a fluent interface.
- */
- public function limit($limit)
- {
- $this->limit = (int) $limit;
- return $this;
- }
- /*
- * Offset
- * @param int $offset
- * @return \OpenFlame\Dbal\QueryBuilder - Provides a fluent interface.
- */
- public function offset($offset)
- {
- $this->offset = (int) $offset;
- return $this;
- }
- /*
- * Order by
- * @param string $fields - Comma separated list of fields to order by
- * @param string $direction - ASC or DESC
- * @return \OpenFlame\Dbal\QueryBuilder - Provides a fluent interface.
- */
- public function orderBy($fields, $direction = 'ASC')
- {
- $this->orderBy = (string) $fields;
- $this->orderDirection = (strtoupper($direction) == 'ASC') ? 'ASC' : 'DESC';
- return $this;
- }
- /*
- * Increment field value
- * Hackaround for set(), we can't really use it to add/subtract values from the fields
- * @param string $field - Name of the field
- * @param int $amount - Can be any signed integer, defaults to 1
- * @return \OpenFlame\Dbal\QueryBuilder - Provides a fluent interface.
- */
- public function increment($field, $amount = 1)
- {
- $this->rawSets[$field] = $field . ' + ' . (int) $amount;
- return $this;
- }
- /*
- * Decrement field value
- * Shortcut for increment()
- * @param string $field - Name of the field
- * @param int $amount - Can be any signed integer, defaults to 1
- * @return \OpenFlame\Dbal\QueryBuilder - Provides a fluent interface.
- */
- public function decrement($field, $amount = -1)
- {
- return $this->increment($field, $amount);
- }
- /*
- * WHERE clause
- * @param string $statement - PDO style prepared statement
- * @param mixed ... - Addtional params to be placed in the placeholders of the PDO statement
- * @return \OpenFlame\Dbal\QueryBuilder - Provides an fluent interface
- */
- public function where()
- {
- $args = func_get_args();
- $statement = array_shift($args);
- $this->wheres[] = array('WHERE', $statement, $args);
- return $this;
- }
- /*
- * AND clause
- * @param string $statement - PDO style prepared statement
- * @param mixed ... - Addtional params to be placed in the placeholders of the PDO statement
- * @return \OpenFlame\Dbal\QueryBuilder - Provides an fluent interface
- */
- public function andWhere()
- {
- $args = func_get_args();
- $statement = array_shift($args);
- $this->wheres[] = array('AND', $statement, $args);
- return $this;
- }
- /*
- * OR clause
- * @param string $statement - PDO style prepared statement
- * @param mixed ... - Addtional params to be placed in the placeholders of the PDO statement
- * @return \OpenFlame\Dbal\QueryBuilder - Provides an fluent interface
- */
- public function orWhere()
- {
- $args = func_get_args();
- $statement = array_shift($args);
- $this->wheres[] = array('OR', $statement, $args);
- return $this;
- }
- /*
- * Build the query
- * @return \OpenFlame\Dbal\QueryBuilder - Provides an fluent interface
- */
- public function build()
- {
- // Accumulators
- $sql = '';
- $params = array();
- $sets = $insert = $where = false;
- switch($this->type)
- {
- case static::TYPE_SELECT:
- $sql .= 'SELECT ' . implode(',', $this->select) . "\nFROM ";
- $where = true;
- break;
- case static::TYPE_UPSERT:
- case static::TYPE_UPDATE:
- $sql .= 'UPDATE ';
- $sets = true;
- $where = true;
- break;
- case static::TYPE_MULTII:
- case static::TYPE_INSERT:
- $sql .= 'INSERT INTO ';
- $insert = true;
- break;
- case static::TYPE_DELETE:
- $sql .= 'DELETE FROM ';
- $where = true;
- break;;
- }
- // Tabletime
- $sql .= implode(', ', $this->tables) . "\n";
- // For inserts
- if ($insert && sizeof($this->rows[0]))
- {
- $_rows = array();
- $sql .= '(' . implode(',', array_keys($this->rows[0])) . ")\n";
- $_row = implode(',', array_fill(0,sizeof($this->rows[0]),'?'));
- foreach($this->rows as $i => $row)
- {
- foreach($row as $val)
- {
- $params[] = $val;
- }
-
- $_rows[$i] = $_row;
- }
- $sql .= 'VALUES (' . implode("),\n(", $_rows) . ')';
- }
- // Sets and raw sets
- if ($sets && (sizeof($this->sets) || sizeof($this->rawSets)))
- {
- $temp = array();
- foreach($this->sets as $col => $val)
- {
- if (is_null($val) || !strlen($col))
- {
- continue;
- }
- $temp[] = $col . ' = ?';
- $params[] = $val;
- }
- foreach($this->rawSets as $col => $val)
- {
- $temp[] = $col . ' = ' . $val;
- }
- $sql .= 'SET ' . implode(',', $temp) . "\n";
- unset($temp);
- }
- // Where
- if ($where && sizeof($this->wheres))
- {
- foreach($this->wheres as $key => $val)
- {
- $sql .= $val[0] . ' ' . $val[1] . "\n";
- if(isset($val[2]) && is_array($val[2]) && strpos($val[1], '?'))
- {
- $params = array_merge($params, $val[2]);
- }
- }
- }
- if (strlen($this->orderBy))
- {
- $sql .= "ORDER BY {$this->orderBy} {$this->orderDirection}\n";
- }
- if ($this->limit > 0)
- {
- $sql .= "LIMIT {$this->limit}\n";
- }
- if ($this->offset > 0)
- {
- $sql .= "OFFSET {$this->offset}\n";
- }
- $this->sql($sql);
- $this->setParams($params);
- return $this;
- }
- /**
- * Excecute a query, override of Query::exec()
- * @return int - Number of rows affected
- */
- public function exec()
- {
- $this->_query();
- $count = $this->stmt->rowCount();
- if(!$count && $this->type == static::TYPE_UPSERT)
- {
- // build insert from update
- $table = is_array($this->tables) ? array_shift($this->tables) : (string) $this->tables;
- $sql = 'INSERT INTO ' . $table . "\n";
- $sql .= '(' . implode(',', array_keys($this->sets)) . ")\n";
- $qs = array_fill(0,sizeof($this->sets),'?');
- $sql .= 'VALUES (' . implode(',', $qs) . ')';
- $this->sql($sql);
- $this->setParams(array_values($this->sets));
- $this->_query(true);
- $count = $this->stmt->rowCount();
- }
- return $count;
- }
- /*
- * Excecute a query (internally) Override of Query::_query()
- * @param bool $hard - Run it even if a query has been ran for this instance.
- * @throws \PDOException, \LogicException
- */
- protected function _query($hard = false)
- {
- if (!$this->queryRan || $hard)
- {
- if(!$hard)
- {
- $this->build();
- }
- parent::_query(true);
- }
- }
- /**
- * Used to (internally) normalize statements to an array
- * @todo change foreach() to an array_map() implementation
- * @param mixed - array or commma separated data
- * @return array - Normalized data
- */
- protected function normalizeArray($items)
- {
- if (!is_array($items))
- {
- $items = explode(',', $items);
- }
- $items = array_map('trim', $items);
- return $items;
- }
- /**
- * Used to (internally) input data with a key/value relationship
- * @param mixed
- * @return array - organized key/val
- */
- protected function inputKeyVals($args)
- {
- $buffer = array();
- // We are an array('field'=>'value', ...)
- if (is_array($args[0]))
- {
- $buffer = $args[0];
- }
- // PDO prepared query style
- else if (strrchr($args[0], '?') && sizeof($args) > 1)
- {
- $sets = array_map('trim', explode(',', $args[0]));
- $matches = array();
- $i = 1;
- foreach($sets as $item)
- {
- if (!isset($args[$i]))
- {
- break;
- }
- preg_match("#^([a-z]+)[\s]*\=[\s]*\?$#i", $item, $matches);
- $buffer[$matches[1]] = $args[$i];
- $i++;
- }
- }
- // Single Key/value set
- else if (isset($args[0]) && isset($args[1]) && !isset($args[2]) && is_string($args[0]))
- {
- $buffer[$args[0]] = $args[1];
- }
- return $buffer;
- }
- }