/vendor/php-activerecord/php-activerecord/lib/SQLBuilder.php
PHP | 423 lines | 285 code | 72 blank | 66 comment | 39 complexity | d73184cd3c9f23a556c364ac404e6777 MD5 | raw file
- <?php
- /**
- * @package ActiveRecord
- */
- namespace ActiveRecord;
- /**
- * Helper class for building sql statements progmatically.
- *
- * @package ActiveRecord
- */
- class SQLBuilder
- {
- private $connection;
- private $operation = 'SELECT';
- private $table;
- private $select = '*';
- private $joins;
- private $order;
- private $limit;
- private $offset;
- private $group;
- private $having;
- private $update;
- // for where
- private $where;
- private $where_values = array();
- // for insert/update
- private $data;
- private $sequence;
- /**
- * Constructor.
- *
- * @param Connection $connection A database connection object
- * @param string $table Name of a table
- * @return SQLBuilder
- * @throws ActiveRecordException if connection was invalid
- */
- public function __construct($connection, $table)
- {
- if (!$connection)
- throw new ActiveRecordException('A valid database connection is required.');
- $this->connection = $connection;
- $this->table = $table;
- }
- /**
- * Returns the SQL string.
- *
- * @return string
- */
- public function __toString()
- {
- return $this->to_s();
- }
- /**
- * Returns the SQL string.
- *
- * @see __toString
- * @return string
- */
- public function to_s()
- {
- $func = 'build_' . strtolower($this->operation);
- return $this->$func();
- }
- /**
- * Returns the bind values.
- *
- * @return array
- */
- public function bind_values()
- {
- $ret = array();
- if ($this->data)
- $ret = array_values($this->data);
- if ($this->get_where_values())
- $ret = array_merge($ret,$this->get_where_values());
- return array_flatten($ret);
- }
- public function get_where_values()
- {
- return $this->where_values;
- }
- public function where(/* (conditions, values) || (hash) */)
- {
- $this->apply_where_conditions(func_get_args());
- return $this;
- }
- public function order($order)
- {
- $this->order = $order;
- return $this;
- }
- public function group($group)
- {
- $this->group = $group;
- return $this;
- }
- public function having($having)
- {
- $this->having = $having;
- return $this;
- }
- public function limit($limit)
- {
- $this->limit = intval($limit);
- return $this;
- }
- public function offset($offset)
- {
- $this->offset = intval($offset);
- return $this;
- }
- public function select($select)
- {
- $this->operation = 'SELECT';
- $this->select = $select;
- return $this;
- }
- public function joins($joins)
- {
- $this->joins = $joins;
- return $this;
- }
- public function insert($hash, $pk=null, $sequence_name=null)
- {
- if (!is_hash($hash))
- throw new ActiveRecordException('Inserting requires a hash.');
- $this->operation = 'INSERT';
- $this->data = $hash;
- if ($pk && $sequence_name)
- $this->sequence = array($pk,$sequence_name);
- return $this;
- }
- public function update($mixed)
- {
- $this->operation = 'UPDATE';
- if (is_hash($mixed))
- $this->data = $mixed;
- elseif (is_string($mixed))
- $this->update = $mixed;
- else
- throw new ActiveRecordException('Updating requires a hash or string.');
- return $this;
- }
- public function delete()
- {
- $this->operation = 'DELETE';
- $this->apply_where_conditions(func_get_args());
- return $this;
- }
- /**
- * Reverses an order clause.
- */
- public static function reverse_order($order)
- {
- if (!trim($order))
- return $order;
- $parts = explode(',',$order);
- for ($i=0,$n=count($parts); $i<$n; ++$i)
- {
- $v = strtolower($parts[$i]);
- if (strpos($v,' asc') !== false)
- $parts[$i] = preg_replace('/asc/i','DESC',$parts[$i]);
- elseif (strpos($v,' desc') !== false)
- $parts[$i] = preg_replace('/desc/i','ASC',$parts[$i]);
- else
- $parts[$i] .= ' DESC';
- }
- return join(',',$parts);
- }
- /**
- * Converts a string like "id_and_name_or_z" into a conditions value like array("id=? AND name=? OR z=?", values, ...).
- *
- * @param Connection $connection
- * @param $name Underscored string
- * @param $values Array of values for the field names. This is used
- * to determine what kind of bind marker to use: =?, IN(?), IS NULL
- * @param $map A hash of "mapped_column_name" => "real_column_name"
- * @return A conditions array in the form array(sql_string, value1, value2,...)
- */
- public static function create_conditions_from_underscored_string(Connection $connection, $name, &$values=array(), &$map=null)
- {
- if (!$name)
- return null;
- $parts = preg_split('/(_and_|_or_)/i',$name,-1,PREG_SPLIT_DELIM_CAPTURE);
- $num_values = count($values);
- $conditions = array('');
- for ($i=0,$j=0,$n=count($parts); $i<$n; $i+=2,++$j)
- {
- if ($i >= 2)
- $conditions[0] .= preg_replace(array('/_and_/i','/_or_/i'),array(' AND ',' OR '),$parts[$i-1]);
- if ($j < $num_values)
- {
- if (!is_null($values[$j]))
- {
- $bind = is_array($values[$j]) ? ' IN(?)' : '=?';
- $conditions[] = $values[$j];
- }
- else
- $bind = ' IS NULL';
- }
- else
- $bind = ' IS NULL';
- // map to correct name if $map was supplied
- $name = $map && isset($map[$parts[$i]]) ? $map[$parts[$i]] : $parts[$i];
- $conditions[0] .= $connection->quote_name($name) . $bind;
- }
- return $conditions;
- }
- /**
- * Like create_conditions_from_underscored_string but returns a hash of name => value array instead.
- *
- * @param string $name A string containing attribute names connected with _and_ or _or_
- * @param $args Array of values for each attribute in $name
- * @param $map A hash of "mapped_column_name" => "real_column_name"
- * @return array A hash of array(name => value, ...)
- */
- public static function create_hash_from_underscored_string($name, &$values=array(), &$map=null)
- {
- $parts = preg_split('/(_and_|_or_)/i',$name);
- $hash = array();
- for ($i=0,$n=count($parts); $i<$n; ++$i)
- {
- // map to correct name if $map was supplied
- $name = $map && isset($map[$parts[$i]]) ? $map[$parts[$i]] : $parts[$i];
- $hash[$name] = $values[$i];
- }
- return $hash;
- }
- /**
- * prepends table name to hash of field names to get around ambiguous fields when SQL builder
- * has joins
- *
- * @param array $hash
- * @return array $new
- */
- private function prepend_table_name_to_fields($hash=array())
- {
- $new = array();
- $table = $this->connection->quote_name($this->table);
- foreach ($hash as $key => $value)
- {
- $k = $this->connection->quote_name($key);
- $new[$table.'.'.$k] = $value;
- }
- return $new;
- }
- private function apply_where_conditions($args)
- {
- require_once 'Expressions.php';
- $num_args = count($args);
- if ($num_args == 1 && is_hash($args[0]))
- {
- $hash = is_null($this->joins) ? $args[0] : $this->prepend_table_name_to_fields($args[0]);
- $e = new Expressions($this->connection,$hash);
- $this->where = $e->to_s();
- $this->where_values = array_flatten($e->values());
- }
- elseif ($num_args > 0)
- {
- // if the values has a nested array then we'll need to use Expressions to expand the bind marker for us
- $values = array_slice($args,1);
- foreach ($values as $name => &$value)
- {
- if (is_array($value))
- {
- $e = new Expressions($this->connection,$args[0]);
- $e->bind_values($values);
- $this->where = $e->to_s();
- $this->where_values = array_flatten($e->values());
- return;
- }
- }
- // no nested array so nothing special to do
- $this->where = $args[0];
- $this->where_values = &$values;
- }
- }
- private function build_delete()
- {
- $sql = "DELETE FROM $this->table";
- if ($this->where)
- $sql .= " WHERE $this->where";
- if ($this->connection->accepts_limit_and_order_for_update_and_delete())
- {
- if ($this->order)
- $sql .= " ORDER BY $this->order";
- if ($this->limit)
- $sql = $this->connection->limit($sql,null,$this->limit);
- }
- return $sql;
- }
- private function build_insert()
- {
- require_once 'Expressions.php';
- $keys = join(',',$this->quoted_key_names());
- if ($this->sequence)
- {
- $sql =
- "INSERT INTO $this->table($keys," . $this->connection->quote_name($this->sequence[0]) .
- ") VALUES(?," . $this->connection->next_sequence_value($this->sequence[1]) . ")";
- }
- else
- $sql = "INSERT INTO $this->table($keys) VALUES(?)";
- $e = new Expressions($this->connection,$sql,array_values($this->data));
- return $e->to_s();
- }
- private function build_select()
- {
- $sql = "SELECT $this->select FROM $this->table";
- if ($this->joins)
- $sql .= ' ' . $this->joins;
- if ($this->where)
- $sql .= " WHERE $this->where";
- if ($this->group)
- $sql .= " GROUP BY $this->group";
- if ($this->having)
- $sql .= " HAVING $this->having";
- if ($this->order)
- $sql .= " ORDER BY $this->order";
- if ($this->limit || $this->offset)
- $sql = $this->connection->limit($sql,$this->offset,$this->limit);
- return $sql;
- }
- private function build_update()
- {
- if (strlen($this->update) > 0)
- $set = $this->update;
- else
- $set = join('=?, ', $this->quoted_key_names()) . '=?';
- $sql = "UPDATE $this->table SET $set";
- if ($this->where)
- $sql .= " WHERE $this->where";
- if ($this->connection->accepts_limit_and_order_for_update_and_delete())
- {
- if ($this->order)
- $sql .= " ORDER BY $this->order";
- if ($this->limit)
- $sql = $this->connection->limit($sql,null,$this->limit);
- }
- return $sql;
- }
- private function quoted_key_names()
- {
- $keys = array();
- foreach ($this->data as $key => $value)
- $keys[] = $this->connection->quote_name($key);
- return $keys;
- }
- }
- ?>