Merged upstream fixes

3 files changed, 312 insertions, 152 deletions

diff --git a/modules/database/classes/database/query.php b/modules/database/classes/database/query.php
index 5c905ae..23b9876 100644
--- a/modules/database/classes/database/query.php
+++ b/modules/database/classes/database/query.php
@@ -5,7 +5,7 @@
  * 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.
+ * @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.
@@ -125,8 +125,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
      *
@@ -175,6 +182,31 @@ 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
      *
diff --git a/modules/database/classes/driver/pdo/query.php b/modules/database/classes/driver/pdo/query.php
index d37e459..d78316d 100644
--- a/modules/database/classes/driver/pdo/query.php
+++ b/modules/database/classes/driver/pdo/query.php
@@ -87,15 +87,68 @@ class Query_PDO_Driver extends Query_Database
      * @return string  Escaped value representation
      * @access public 
      */
-    public function escape_value($val, &$params)
+	public function escape_value($val,&$params)
     {
-        if (is_object($val) && get_class($val) == 'Expression_Database')
+		if ($val instanceof Expression_Database)
         {
-            return $val->value;
+			return $val->value;
         }
-        $params[] = $val;
-        return '?';
-    }
+		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
@@ -103,172 +156,194 @@ class Query_PDO_Driver extends Query_Database
      * @return array     An array with a prepared query string and an array of parameters
      * @access public    
      */
-    public function query()
+	public function query()
     {
-
-        $query = '';
-        $params = array();
-        if ($this->_type == 'insert')
+		
+		$query = '';
+		$params = array();
+		
+		if ($this->_type == 'insert')
         {
-            $query.= "INSERT INTO {$this->quote($this->_table)} ";
-            if (empty($this->_data) && $this->_db_type == 'pgsql')
+			$query .= "INSERT INTO {$this->quote($this->_table)} ";
+			if (empty($this->_data) && $this->_db_type == 'pgsql')
             {
-                $query.= "DEFAULT VALUES ";
-            }
+				$query.= "DEFAULT VALUES ";
+			}
             else
             {
-                $columns = '';
-                $values = '';
-                $first = true;
-                foreach ($this->_data as $key => $val)
+				$columns = '';
+				$values = '';
+				$first = true;
+				foreach($this->_data as $key => $val)
                 {
-                    if (!$first)
+					if (!$first)
                     {
-                        $values .= ', ';
-                        $columns .= ', ';
-                    }
+						$values .= ', ';
+						$columns .= ', ';
+					}
                     else
                     {
-                        $first = false;
-                    }
-                    $columns .= $this->quote($key);
-                    $values .= $this->escape_value($val, $params);
-                }
-                $query .= "({$columns}) VALUES({$values})";
-            }
+						$first = false;
+					}
+					$columns .= $this->quote($key);
+					$values .= $this->escape_value($val,$params);
+				}
+				$query .= "({$columns}) VALUES({$values})";
+			}
         }
         else
         {
-            if ($this->_type == 'select')
+			if ($this->_type == 'select')
             {
-                $query .= "SELECT ";
-                if ($this->_fields == null)
+				$query .= "SELECT ";
+				if ($this->_fields==null)
                 {
-                    $query .= "* ";
-                }
+					$query .= "* ";
+				}
                 else
                 {
-                    $first = true;
-                    foreach ($this->_fields as $f)
+					$first = true;
+					foreach ($this->_fields as $f)
                     {
-                        if (!$first)
+						if (!$first)
                         {
-                            $query .= ", ";
-                        }
+							$query .= ", ";
+						}
                         else
                         {
-                            $first = false;
-                        }
-                        if (is_array($f))
+							$first = false;
+						}
+						if (is_array($f))
                         {
-                            $query .= "{$this->escape_field($f[0])} AS {$f[1]} ";
-                        }
+							$query .= "{$this->escape_field($f[0])} AS {$f[1]} ";
+						}
                         else
                         {
-                            $query .= "{$this->escape_field($f)} ";
-                        }
-                    }
-                }
-                $query .= "FROM {$this->quote($this->_table)} ";
-            }
-            if ($this->_type == 'count')
+							$query .= "{$this->escape_field($f)} ";
+						}
+					}
+				}
+				$query .= "FROM {$this->escape_table($this->_table,$params)} ";
+			}
+			if ($this->_type == 'count')
             {
-                $query .= "SELECT COUNT(*) as {$this->quote('count')} FROM {$this->quote($this->_table)} ";
-            }
-            if ($this->_type == 'delete')
+				$query .= "SELECT COUNT(*) as {$this->quote('count')} FROM {$this->escape_table($this->_table,$params)} ";	
+			}
+			
+			if ($this->_type == 'delete')
             {
-                if ($this->_db_type != 'sqlite')
+				if ($this->_db_type!='sqlite')
                 {
-                    $query .= "DELETE {$this->last_alias()}.* FROM {$this->quote($this->_table)} ";
-                }
+					$query .= "DELETE {$this->last_alias()}.* FROM {$this->quote($this->_table)} ";
+				}
                 else
                 {
-                    if (!empty($this->_joins))
+					if (!empty($this->_joins))
                     {
-                        throw new Exception("SQLite doesn't support deleting a table with JOIN in the query");
+						throw new Exception("SQLite doesn't support deleting a table with JOIN in the query");
                     }
-                    $query .= "DELETE FROM {$this->quote($this->_table)} ";
-                }
-            }
-            if ($this->_type == 'update')
+					$query .= "DELETE FROM {$this->quote($this->_table)} ";
+				}
+			}
+			if ($this->_type=='update')
             {
-                $query .= "UPDATE {$this->quote($this->_table)} SET ";
-                $first = true;
-                foreach ($this->_data as $key => $val)
-                {
-                    if (!$first)
+				$query .= "UPDATE {$this->quote($this->_table)} SET ";
+				$first = true;
+				foreach ($this->_data as $key=>$val){
+					if (!$first)
                     {
-                        $query .= ", ";
-                    }
+						$query.=", ";
+					}
                     else
                     {
-                        $first = false;
-                    }
-                    $query .= "{$this->quote($key)} = {$this->escape_value($val, $params)}";
-                }
-                $query .= " ";
-            }
-
-            foreach ($this->_joins as $join)
+						$first=false;
+					}
+					$query .= "{$this->quote($key)} = {$this->escape_value($val,$params)}";
+				}
+				$query .= " ";
+			}
+			
+			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)}";
-                }
-                $query .= strtoupper($join[1])." JOIN {$table} ON {$this->get_condition_query($join[2], $params, true, true)} ";
-            }
+				$table = $join[0];
+				$table = $this->escape_table($table,$params);
+				$query .= strtoupper($join[1])." JOIN {$table} ON {$this->get_condition_query($join[2],$params,true,true)} ";
+			}
 
-            if (!empty($this->_conditions))
+			if (!empty($this->_conditions))
             {
-                $query .= "WHERE {$this->get_condition_query($this->_conditions, $params, true)} ";
-            }
-            if (($this->_type == 'select' || $this->_type == 'count') && $this->_group_by != null)
+				$query .= "WHERE {$this->get_condition_query($this->_conditions,$params,true)} ";
+			}
+			if (($this->_type == 'select' ||  $this->_type == 'count') && $this->_group_by!=null)
             {
-                $query .= "GROUP BY {$this->escape_field($this->_group_by)} ";
-            }
-            if (($this->_type == 'select' || $this->_type == 'count') && !empty($this->_having))
+				$query .= "GROUP BY {$this->escape_field($this->_group_by)} ";
+			}
+			if (($this->_type == 'select' ||  $this->_type == 'count') && !empty($this->_having))
             {
-                $query .= "HAVING {$this->get_condition_query($this->_having, $params, true)} ";
-            }
-
-            if ($this->_type == 'select' && !empty($this->_orderby))
+				$query .= "HAVING {$this->get_condition_query($this->_having,$params,true)} ";
+			}
+			
+			if ($this->_type == 'select' && !empty($this->_orderby))
             {
-                $query .= "ORDER BY ";
-                $first = true;
-                foreach ($this->_orderby as $order)
+				$query .= "ORDER BY ";
+				$first = true;
+				foreach ($this->_orderby as $order)
                 {
-                    if (!$first)
+					if (!$first)
                     {
-                        $query .= ',';
-                    }
+						$query .= ',';
+					}
                     else
                     {
-                        $first = false;
-                    }
-                    $query .= $this->escape_field($order[0])." ";
-                    if (isset($order[1]))
+						$first = false;
+					}
+					$query .= $this->escape_field($order[0])." ";
+					if (isset($order[1]))
                     {
-                        $dir = strtoupper($order[1]);
-                        $query .= $dir." ";
-                    }
-                }
-            }
-            if ($this->_type != 'count')
+						$dir = strtoupper($order[1]);
+						$query .= $dir." ";
+					}
+				}
+			}
+			
+			if (count($this->_union) > 0 && ($this->_type == 'select'))
             {
-                if ($this->_limit != null)
-                    $query .= "LIMIT {$this->_limit} ";
-                if ($this->_offset != null)
-                    $query .= "OFFSET {$this->_offset} ";
-            }
-        }
+				$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} ";
+                }
+				if ($this->_offset != null)
+                {
+					$query .= "OFFSET {$this->_offset} ";
+                }
+			}
+			
+		}
+		
+		return array($query,$params);
+	}
 
-        return array($query, $params);
-    }
 
     /**
      * Recursively parses conditions array into a query string
@@ -284,39 +359,42 @@ class Query_PDO_Driver extends Query_Database
      * @access public    
      * @throws Exception If condition cannot be parsed
      */
-    public function get_condition_query($p, &$params, $skip_first_operator, $value_is_field = false)
+	public function get_condition_query($p,&$params,$skip_first_operator,$value_is_field = false)
     {
-        if (isset($p['field']))
+		if (isset($p['field']))
         {
-            if ($value_is_field)
+			if ($value_is_field)
             {
-                $param = $this->escape_field($p['value']);
-            }
+				$param = $this->escape_field($p['value']);
+			}
             else
             {
-                $param = $this->escape_value($p['value'], $params);
-            }
-            return $this->escape_field($p['field']).' '.$p['operator'].' '.$param;
-        }
-        if (isset($p['logic']))
+				$param = $this->escape_value($p['value'], $params);
+			}
+			return $this->escape_field($p['field']).' '.$p['operator'].' '.$param;
+		}
+		if (isset($p['logic']))
         {
-            return ($skip_first_operator ? '' : strtoupper($p['logic']).' ').$this->get_condition_query($p['conditions'], $params, false, $value_is_field);
-        }
-
-        $conds = '';
-        $skip = $skip_first_operator || (count($p) > 1);
-        foreach ($p as $q)
+			return ($skip_first_operator ? '' : strtoupper($p['logic']).' ')
+					.$this->get_condition_query($p['conditions'], $params, false, $value_is_field);
+		}
+		
+		$conds = '';
+		$skip = $skip_first_operator || (count($p) > 1);
+		foreach($p as $q)
         {
-            $conds .= $this->get_condition_query($q, $params, $skip, $value_is_field).' ';
-            $skip = false;
-        }
-        if (count($p) > 1 && !$skip_first_operator)
+			$conds .= $this->get_condition_query($q,$params,$skip,$value_is_field).' ';
+			$skip = false;
+		}
+		if (count($p) > 1 && !$skip_first_operator)
         {
-            return "( ".$conds.")";
+			return "( ".$conds.")";
         }
-        return $conds;
+		return $conds;
+
+		throw new Exception("Cannot parse condition:\n".var_export($p, true));
+	}
+
 
-        throw new Exception("Cannot parse condition:\n".var_export($p, true));
-    }
 
 }
\ No newline at end of file
diff --git a/tests/modules/database/driver/pdo/queryTest.php b/tests/modules/database/driver/pdo/queryTest.php
index 591b029..158a0f7 100644
--- a/tests/modules/database/driver/pdo/queryTest.php
+++ b/tests/modules/database/driver/pdo/queryTest.php
@@ -204,5 +204,55 @@ class Query_PDO_DriverTest extends PHPUnit_Framework_TestCase
 		}
 		$this->assertEquals(true,$except);
     }
-    
+	
+	public function testUnion()
+	{
+		$stub=(object)array('db_type'=>'mysql');
+		$subquery = new Query_PDO_Driver($stub, 'select');
+		$subquery->table('fairies');
+		$this->object->table('fairies');
+		$this->object->where('id', '>', 7);
+		$this->object->union($subquery);
+		$this->object->union($subquery);
+		$this->assertEquals('(SELECT * FROM `fairies` WHERE `fairies`.`id` > ?  ) UNION ALL (SELECT * FROM `fairies` ) UNION ALL (SELECT * FROM `fairies` ) ',current($this->object->query()));
+	}
+	
+	public function testSubselect()
+	{
+		$stub=(object)array('db_type'=>'mysql');
+		$subquery = new Query_PDO_Driver($stub, 'select');
+		$subquery->fields('id')->table('fairies');
+		$this->object->table('fairies')->where('id', 7)->where('id', 'in', $subquery);
+		$this->assertEquals('SELECT * FROM `fairies` WHERE `fairies`.`id` = ? AND `fairies`.`id` in (SELECT `fairies`.`id` FROM `fairies` )   ',current($this->object->query()));
+	}
+	
+	public function testSubtable()
+	{
+		$stub=(object)array('db_type'=>'mysql');
+		$subquery = new Query_PDO_Driver($stub, 'select');
+		$subquery->fields('id')->table('fairies');
+		$this->object->table($subquery)->where('id', 7);
+		$this->assertEquals('SELECT * FROM (SELECT `fairies`.`id` FROM `fairies` )  AS a0 WHERE `a0`.`id` = ?  ',current($this->object->query()));
+	}
+	
+	public function testJoinSubtable()
+	{
+		$stub=(object)array('db_type'=>'mysql');
+		$subquery = new Query_PDO_Driver($stub, 'select');
+		$subquery->table('fairies');
+		$this->object->table($subquery);
+		$this->object->join('pixies', array('fairies.id', '=', 'pixie.id'));
+		$this->object->join(array('fairies', 'fae'), array('fairies.id', '=', 'fae.id'));
+		$this->object->join(array($subquery, 'fae2'), array('fairies.id', '=', 'fae2.id'));
+		
+		
+		$this->assertEquals('SELECT * FROM (SELECT * FROM `fairies` )  AS a0 LEFT JOIN `pixies` ON `fairies`.`id` = `pixie`.`id` LEFT JOIN `fairies` AS fae ON `fairies`.`id` = `fae`.`id` LEFT JOIN (SELECT * FROM `fairies` )  AS fae2 ON `fairies`.`id` = `fae2`.`id` ',current($this->object->query()));
+	}
+	
+	public function testExpressionSelect()
+	{
+		$this->object->table('fairies')->where('id','in',DB::expr("(SELECT id from fairies)"));
+		$this->assertEquals('SELECT * FROM `fairies` WHERE `fairies`.`id` in (SELECT id from fairies)  ',current($this->object->query()));
+	}
+	
 }