diff options
Diffstat (limited to 'modules/database')
| -rw-r--r-- | modules/database/classes/database/query.php | 49 | ||||
| -rw-r--r-- | modules/database/classes/driver/pdo/query.php | 80 |
2 files changed, 115 insertions, 14 deletions
diff --git a/modules/database/classes/database/query.php b/modules/database/classes/database/query.php index 981a33c..c48407c 100644 --- a/modules/database/classes/database/query.php +++ b/modules/database/classes/database/query.php @@ -5,8 +5,6 @@ * Database drivers extend this class so that they can generate database specific queries. * The idea is to provide a database agnostic interface to query writing. * - * @method mixed table(string $table = null) Set table to query. - * Without arguments returns current table, returns self otherwise. * * @method mixed data(array $data = null) Set data for insert or update queries. * Without arguments returns current data, returns self otherwise. @@ -125,8 +123,15 @@ abstract class Query_Database { * @var array * @access protected */ - protected $methods = array('table' => 'string','data' => 'array','limit' => array('integer','NULL'),'offset' => array('integer','NULL'),'group_by' => array('string','NULL'),'type' => 'string'); + protected $methods = array('data' => 'array','limit' => array('integer','NULL'),'offset' => array('integer','NULL'),'group_by' => array('string','NULL'),'type' => 'string'); + /** + * UNION queries + * @var array + * @access protected + */ + protected $_union = array(); + /** * Generates a query in format that can be executed on current database implementation * @@ -169,7 +174,26 @@ abstract class Query_Database { } return $this; } - + + /** + * Sets the table to perform operations on, also supports subqueries + * + * @param string|Query_database|Expression_database $table table to select from + * @param string $alias Alias for this table + * @return mixed Returns self if a table is passed, otherwise returns the table + * @access public + */ + public function table($table=null,$alias=null) { + if ($table == null) + return is_array($this->_table)?$this->_table[1]:$this->_table; + + if (!is_string($table) && $alias==null) + $alias = $this->add_alias(); + $this->_table = $alias == null?$table:array($table, $alias); + + return $this; + } + /** * Magic methods to create methods for all generic query parts * @@ -352,7 +376,20 @@ abstract class Query_Database { throw new Exception('Incorrect conditional statement passed'); } - + + /** + * Adds a UNION to the query + * + * @param Query_Database|Expression_Database $query Query for the UNION + * @param string $all whether to do a UNION ALL, e.g. keep duplicate rows + * @return Query_Database Returns self + * @access public + */ + public function union($query,$all=true) { + $this->_union[] = array($query,$all); + return $this; + } + /** * Gets last generated alias * @@ -361,7 +398,7 @@ abstract class Query_Database { */ public function last_alias() { if ($this->_alias === null) - return $this->_table; + return $this->table(); return 'a'.$this->_alias; } diff --git a/modules/database/classes/driver/pdo/query.php b/modules/database/classes/driver/pdo/query.php index f23e28b..8d3148d 100644 --- a/modules/database/classes/driver/pdo/query.php +++ b/modules/database/classes/driver/pdo/query.php @@ -78,12 +78,63 @@ class Query_PDO_Driver extends Query_Database { * @access public */ public function escape_value($val,&$params) { - if (is_object($val) && get_class($val) == 'Expression_Database') + if ($val instanceof Expression_Database) return $val->value; + if ($val instanceof Query_Database) + return $this->subquery($val,$params); $params[] = $val; return '?'; } + /** + * Gets the SQL for a subquery and appends its parameters to current ones + * + * @param Query_Database $query Query builder for the subquery + * @param array &$params Reference to parameters array + * @return string Subquery SQL + * @access public + */ + protected function subquery($query, &$params) { + $query = $query->query(); + $params = array_merge($params, $query[1]); + return "({$query[0]}) "; + } + + /** + * Gets the SQL for a table to select from + * + * @param string|Expression_Database|Query_Database|array $table Table representation + * @param array &$params Reference to parameters array + * @param string &alias Alias for this table + * @return string Table SQL + * @access public + */ + public function escape_table($table, &$params) { + $alias=null; + if (is_array($table)) { + $alias = $table[1]; + $table = $table[0]; + } + + if (is_string($table)){ + $table = $this->quote($table); + if ($alias != null) + $table.= " AS {$alias}"; + return $table; + } + + if ($alias == null) + $alias = $this->last_alias(); + + if($table instanceof Query_Database) + return "{$this->subquery($table,$params)} AS {$alias}"; + + if($table instanceof Expression_Database) + return "({$table->value}) AS {$alias}"; + + throw new Exception("Parameter type {get_class($table)} cannot be used as a table"); + } + /** * Builds a query and fills the $params array with parameter values * @@ -94,6 +145,7 @@ class Query_PDO_Driver extends Query_Database { $query = ''; $params = array(); + if ($this->_type == 'insert') { $query.= "INSERT INTO {$this->quote($this->_table)} "; if (empty($this->_data) && $this->_db_type == 'pgsql'){ @@ -134,11 +186,12 @@ class Query_PDO_Driver extends Query_Database { } } } - $query.= "FROM {$this->quote($this->_table)} "; + $query.="FROM {$this->escape_table($this->_table,$params)} "; } if ($this->_type == 'count') { - $query.= "SELECT COUNT(*) as {$this->quote('count')} FROM {$this->quote($this->_table)} "; + $query.= "SELECT COUNT(*) as {$this->quote('count')} FROM {$this->escape_table($this->_table,$params)} "; } + if ($this->_type == 'delete') { if($this->_db_type!='sqlite'){ $query.= "DELETE {$this->last_alias()}.* FROM {$this->quote($this->_table)} "; @@ -164,11 +217,7 @@ class Query_PDO_Driver extends Query_Database { foreach($this->_joins as $join) { $table = $join[0]; - if (is_array($table)){ - $table = "{$this->quote($table[0])} as {$this->quote($table[1])}"; - }else { - $table="{$this->quote($table)}"; - } + $table = $this->escape_table($table,$params); $query.= strtoupper($join[1])." JOIN {$table} ON {$this->get_condition_query($join[2],$params,true,true)} "; } @@ -198,6 +247,21 @@ class Query_PDO_Driver extends Query_Database { } } } + + if (count($this->_union) > 0 && ($this->_type == 'select')) { + $query="({$query}) "; + foreach($this->_union as $union) { + $query.= $union[1]?"UNION ALL ":"UNION "; + if (is_subclass_of($union[0], 'Query_Database')) { + $query.=$this->subquery($union[0],$params); + }elseif(is_subclass_of($union[0], 'Expression_Database')) { + $query.="({$union[0]->value}) "; + }else { + throw new Exception("You can only use query builder instances or DB::expr for unions"); + } + } + } + if($this->_type != 'count'){ if ($this->_limit != null) $query.= "LIMIT {$this->_limit} "; |