Location: PHPKode > scripts > phpSQL > phpsql/phpSQL.php
<?php

/**
 * phpSQL
 * 
 * Generates a SQL query based on PHP object relation.
 * 
 * Requires {@link http://adodb.sourceforge.net/ ADOdb}.
 * 
 * Designed for MySQL, but can work on any database compilant with the LEFT JOIN statement.
 * 
 * TODO:
 * 	* Make it compatible with OCI8
 * 	* Dependency system to order the joins
 * 	* Some way to use a different database on the select
 * 	* Better configuration system to the temporal settings (maybe a setter)
 * 	* Better way to check the last argument of switch_case to decide if they should be the ELSE or the function alias when only one of them supplied.
 *	* INSERT, UPDATE and DELETE
 * 
 * @version 0.1 build 3
 * @author Wesley de Souza <hide@address.com>
 * @license http://www.gnu.org/licenses/gpl.txt GNU GPL
 * @package phpSQL
 */

/**
 * Constants to be used under join.
 */
define( "PHPSQL_JOIN_BY_PK", 1 );
define( "PHPSQL_JOIN_BY_FK", 2 );

/**
 * Main query class
 *
 * This class is the heart of phpSQL.
 * 
 * @package phpSQL
 */
class Query
{
	/**
	 * Query's unique ID.
	 * @var string
	 */
	var $id = null;
	
	/**
	 * ADOdb connection.
	 * @var object
	 */
	var $db = null;
	
	/**
	 * Selected columns (SELECT).
	 * @var array
	 */
	var $select = array();
	
	/**
	 * Reference to the primary table used on the query (FROM).
	 * @var object
	 */
	var $primary_table = null;
	
	/**
	 * Additional tables to be user on the query (LEFT JOIN).
	 * @var array
	 */
	var $joins = array();
	
	/**
	 * Fitlers to be applied on the result (WHERE).
	 * @var object
	 */
	var $filter = null;
	
	/**
	 * Columns that will be grouped (GROUP BY).
	 * @var array
	 */
	var $group_by = array();
	
	/**
	 * Filters to be applied after grouping (HAVING).
	 * @var object
	 */
	var $filter_aggregate = null;
	
	/**
	 * Result order (ORDER BY).
	 * @var array
	 */
	var $sort = array();
	
	/**
	 * Offset of the first result (LIMIT).
	 * @var integer
	 */
	var $offset = 0;
	
	/**
	 * Number of rows to fetch (LIMIT).
	 * @var integer
	 */
	var $num_rows = -1;
	
	/**
	 * Resultset that will be returned by {@link execute}
	 * @var object
	 */
	var $rs = null;
	
	/**
	 * Specific data to guide the temporal functions.
	 * 
	 * TODO: Make a better way to change these settings.
	 * 
	 * @var array
	 */
	var $temporal_configuration = array
	(
		"table_column_separator" => "_t_",
		"use_start" => "us",
		"use_end" => "ue",
		"is_in_use" => "iu",
		"transaction_start" => "ts",
		"transaction_end" => "te",
	);
	
	/**
	 * Class constructor
	 *
	 * @param object $db Reference to ADOdb
	 */
	function Query ( &$db )
	{
		$this->db = &$db;
		$this->id = md5( uniqid( rand(), true ) );
	}
	
	/**
	 * Internal join
	 * 
	 * Internal function called by {@link Query_Table} {@link join}.
	 *
	 * @param object $table Reference to the table that will be joined.
	 * @param object $condition Condition to build the ON clause.
	 */
	function __join ( &$table, $condition )
	{
		$this->joins[] = (object) array( "table" => &$table, "condition" => $condition );
	}
	
	/**
	 * Get an argument's content
	 * 
	 * Internal function to retrieve the correctly formatted argument content.
	 *
	 * @param mixed $argument Can be a function, a table's column or raw text
	 * @return string The formatted argument
	 */
	function __get_content ( $argument )
	{
		$db = &$this->db;
		
		$content = "";
		
		if ( is_object( $argument['table'] ) )
			$content = $db->nameQuote . $argument['table']->__name . $db->nameQuote .".". $db->nameQuote . $argument['name'] . $db->nameQuote;
		else if ( $argument->type == "function" )
			$content = $argument->content;
		else if ( is_object( $argument ) )
			return $this->error( "unexpected_argument_type" );
		else if ( is_numeric( $argument ) )
			$content = $argument;
		else
			$content = $db->qstr( $argument );
			
		return $content;
	}
	
	/**
	 * Dump an error
	 * 
	 * Dump an error to the screen.
	 * 
	 * TODO: Create an error dictionary and adjust the error codes.
	 * 
	 * @param string $code Error code
	 */
	function error ( $code = null )
	{
		$msg = "[phpSQL] ". $code ." ";
		$backtrace = debug_backtrace();
		foreach ( $backtrace as $step )
		{
			if ( $step['file'] == __FILE__ )
				continue;
			
			$msg .= "in ". $step['file'] ." (line ". $step['line'] .")";
			break;
		}
		trigger_error( $msg, E_USER_ERROR );
		exit;
	}
	
	/**
	 * Condition builder
	 * 
	 * Create a SQL condition to be used in WHERE, ON and CASE.
	 * The behaviour of the arguments depends on the second argument.
	 *
	 * The second parameter can be:
	 * <ul>
	 * 	<li><b>&lt;=, =, &gt;=, !=, like</b> Compare the first parameter against the third parameter using the second parameter</li>
	 * 	<li><b>between</b> Check if the first parameter is between the third and fourth parameters</li>
	 * 	<li><b>(not) in</b> Check if the first parameter is (not) among the items of the third parameter that must be an array</li>
	 * 	<li><b>or, ||</b> Check if any of the parameters is true (except for the second)</li>
	 * 	<li><b>and, &&</b> Check if all of the parameters are true (except for the second)</li>
	 * </ul>
	 *
	 * @return object Condition object
	 */
	function i ( )
	{
		$db = &$this->db;
		
		$condition_template = "";
		$condition_length = 0;
		$condition_boolean = false;
		
		// Check if the source of the arguments is a single array
		$arguments = func_get_args();
		if ( count( $arguments ) == 1 && is_array( $arguments[0] ) )
			$arguments = $arguments[0];
		
		// Check the second argument, which defines what should be done
		switch ( strtolower( $arguments[1] ) )
		{
			case "<":
			case "<=":
			case "==":
			case "=":
			case ">":
			case ">=":
			case "!=":
			case "like":
				if ( !isset( $arguments[2] ) )
					return $this->error( "i_missing_argument" );
				
				if ( $arguments[1] == "==" )
					$arguments[1] = "=";
				if ( $arguments[1] == "like" )
					$arguments[1] = "LIKE";
				
				if ( $arguments[2] === null )
				{
					if ( $arguments[1] == "=" )
						$arguments[1] = "IS";
					else if ( $argumentss[1] == "!=" )
						$arguments[1] = "IS NOT";
					else
						return $this->error( "i_invalid_null_condition" );
					
					$condition_template = "{argument} ". $arguments[1] ." NULL";
					$condition_length = 1;
				}
				else
				{
					$condition_template = "{argument} ". $arguments[1] ." {argument}";
					$condition_length = 2;
				}
				break;
			
			case "between":
				if ( !isset( $arguments[2] ) || !isset( $arguments[3] ) )
					return $this->error( "i_missing_argument" );
				
				if ( ( is_string( $arguments[2] ) || is_numeric( $arguments[2] ) ) && ( is_string( $arguments[3] ) || is_numeric( $arguments[3] ) ) && $arguments[2] > $arguments[3] )
					return $this->error( "i_between_inverse" );
				
				$condition_template = "{argument} BETWEEN {argument} AND {argument}";
				$condition_length = 3;
				
				break;
			
			case "in":
				if ( !isset( $arguments[2] ) )
					return $this->error( "i_missing_argument" );
				
				$condition_template = "{argument} IN ( ". implode( ", ", array_fill( 0, count( $arguments ) - 2, "{argument}" ) ) ." )";
				$condition_length = count( $arguments ) - 1;
				
				break;
			
			case "or":
				if ( !isset( $arguments[2] ) )
					return $this->error( "i_missing_argument" );
				
				$condition_template = "( ". implode( " OR ", array_fill( 0, count( $arguments ) - 1, "{argument}" ) ) ." )";
				$condition_length = count( $arguments ) - 1;
				
				$condition_boolean = true;
				
				break;
			
			case "and":
				if ( !isset( $arguments[2] ) )
					return $this->error( "i_missing_argument" );
				
				$condition_template = "( ". implode( " AND ", array_fill( 0, count( $arguments ) - 1, "{argument}" ) ) ." )";
				$condition_length = count( $arguments ) - 1;
				
				$condition_boolean = true;
				
				break;
		}
		
		$condition = $condition_template;
		
		$condition_type = null;
		$condition_query_id = null;
		$argument_counter = 0;
		$condition_scope = null;
		
		foreach ( $arguments as $i => $argument )
		{
			if ( $argument_counter >= $condition_length )
				break;
			if ( $i == 1 )
				continue;
			
			if ( is_array( $argument ) && !is_object( $argument['table'] ) )
				$argument = $this->i( $argument );
			
			if ( !$condition_boolean )
			{
				if ( $condition_type === null && $argument['meta_type'] )
					$condition_type = $argument['meta_type'];
				if ( $condition_query_id === null && $argument['table']->__query->id )
					$condition_query_id = $argument['table']->__query->id;
			}
			
			$replace = "";
			
			if ( is_object( $argument['table'] ) )
			{
				if ( $condition_query_id && $argument['table']->__query->id != $condition_query_id )
					return $this->error( "illegal_mixing_of_different_queries" );
				
				if ( $condition_scope === null )
					$condition_scope = "usual";
				
				$replace = $db->nameQuote . $argument['table']->__name . $db->nameQuote .".". $db->nameQuote . $argument['name'] . $db->nameQuote;
			}
			else if ( is_object( $argument ) && $argument->type == "function" )
			{
				if ( $condition_scope === null )
					$condition_scope = $argument->scope;
				else if ( $condition_scope != $argument->scope )
					return $this->error( "illegal_mixing_of_scopes" );
				
				$replace = $argument->content;
			}
			else if ( is_object( $argument ) && $argument->type == "condition" )
			{
				if ( $condition_scope === null )
					$condition_scope = $argument->scope;
				else if ( $condition_scope != $argument->scope )
					return $this->error( "illegal_mixing_of_scopes" );
				
				$replace = $argument->content;
			}
			else
			{
				if ( $condition_type == "T" )
					$replace = $db->DBTimeStamp( $argument );
				else if ( $condition_type == "D" )
					$replace = $db->DBDate( $argument );
				else if ( is_numeric( $argument ) )
					$replace = $argument;
				else
					$replace = $db->qstr( $argument );
			}
			
			$condition = preg_replace( "/\{argument\}/", $replace, $condition, 1 );
		}
		
		return (object) array( "type" => "condition", "content" => $condition, "scope" => $condition_scope );
	}
	
	/**
	 * AND condition
	 * 
	 * Shortcut to {@link i} with and. Make an AND condition with all the arguments.
	 * 
	 * @return object Condition object
	 */
	function i_and ( )
	{
		$db = &$this->db;
		
		$condition_arguments = array();
		
		// Check if the source of the arguments is a single array
		$arguments = func_get_args();
		if ( count( $arguments ) == 1 && is_array( $arguments[0] ) )
			$arguments = $arguments[0];
		
		if ( count( $arguments ) == 1 && $arguments[0]->type == "condition" )
			return $arguments[0];
		
		foreach ( $arguments as $i => $argument )
		{
			if ( $i == 1 )
				$condition_arguments[] = "and";
			
			$condition_arguments[] = $argument;
		}
		
		return $this->i( $condition_arguments );
	}
	
	/**
	 * OR condition
	 * 
	 * Shortcut to {@link i} with or. Make an OR condition with all the arguments.
	 * 
	 * @return object Condition object
	 */
	function i_or ( )
	{
		$db = &$this->db;
		
		$condition_arguments = array();
		
		// Check if the source of the arguments is a single array
		$arguments = func_get_args();
		if ( count( $arguments ) == 1 && is_array( $arguments[0] ) )
			$arguments = $arguments[0];
		
		if ( count( $arguments ) == 1 && $arguments[0]->type == "condition" )
			return $arguments[0];
		
		foreach ( $arguments as $i => $argument )
		{
			if ( $i == 1 )
				$condition_arguments[] = "or";
			
			$condition_arguments[] = $argument;
		}
		
		return $this->i( $condition_arguments );
	}
	
	/**
	 * Alias
	 * 
	 * Create a simple alias function to be used by {@link select}.
	 *
	 * @param array $column Column reference created by {@link new_table}
	 * @param string $alias Alias of the result
	 * @return object Returns a 'internal function' object
	 */
	function alias ( $column, $alias = "" )
	{
		$db = &$this->db;
		
		$content = $this->__get_content( $column );
		
		return (object) array( "type" => "function", "content" => $content, "scope" => ( ( $column->scope ) ? $column->scope : "usual" ), "alias" => $alias );
	}
	
	/**
	 * DATE_FORMAT()
	 * 
	 * Create a date format function to be used by {@link select} or {@link i}.
	 *
	 * @param array $column Column reference created by {@link new_table}
	 * @param string $format How to format the date, as in ADOdb's function SQLDate
	 * @param string $alias Alias of the result, only with {@link select}
	 * @return object Returns a 'internal function' object
	 */
	function date_format ( $column, $format, $alias = "" )
	{
		$db = &$this->db;
		
		if ( is_object( $column['table'] ) )
			$content = $db->nameQuote . $column['table']->__name . $db->nameQuote .".". $db->nameQuote . $column['name'] . $db->nameQuote;
		else if ( $column->type == "function" )
			$content = $column->content;
		else
			return $this->error( "invalid_column_reference" );
			
		return (object) array( "type" => "function", "content" => $db->SQLDate( $format, $content ), "scope" => "usual", "alias" => $alias );
	}
	
	/**
	 * CASE()
	 * 
	 * Create a case function to be used by {@link select} or {@link i}.
	 *
	 * The parameters should alternate between a condition object and a value.
	 * The penultimate is the value assumed if every other case fails (ELSE).
	 * The last is the function alias.
	 * @return object Returns a 'internal function' object
	 */
	function switch_case ( )
	{
		$db = &$this->db;
		
		$content = "CASE ";
		$alias = "";
		
		$arguments = func_get_args();
		$arguments_count = count( $arguments );
		if ( $arguments_count == 1 && is_array( $arguments[0] ) )
			$arguments = $arguments[0];
		
		$force_value = 0;
		$end = 0;
		
		$last = "value";
		foreach ( $arguments as $i => $argument )
		{
			if ( $last == "value" )
			{
				if ( $argument->type == "condition" )
					$content .= "WHEN ". $argument->content ." THEN ";
				else if ( $arguments_count - 2 == $i || !is_string( $argument ) )
				{
					$content .= "ELSE ";
					$force_value = 1;
					$last = "value";
				}
				else if ( $arguments_count - 1 == $i )
				{
					$alias = $argument;
					$force_value = 0;
					$end = 1;
				}
				else
					return $this->error( "condition_expected" );
				
				$next = false;
				$last = "condition";
			}
			if ( ( $last == "condition" && $next ) || $force_value )
			{
				if ( $argument->type == "condition" )
					return $this->error( "condition_unexpected" );
				else if ( is_object( $argument['table'] ) )
					$content .= $db->nameQuote . $argument['table']->__name . $db->nameQuote .".". $db->nameQuote . $argument['name'] . $db->nameQuote ." ";
				else if ( $argument->type == "function" )
					$content .= $argument->content ." ";
				else if ( is_numeric( $argument ) )
					$content .= $argument ." ";
				else
					$content .= $db->qstr( $argument ) ." ";
				
				$last = "value";
			}
			if ( $end )
				break;
			$next = true;
		}
		$content .= "END";
		
		return (object) array( "type" => "function", "content" => $content, "scope" => "usual", "alias" => $alias );
	}
	
	/**
	 * Mathematical operation
	 * 
	 * Create a math function to be used by {@link select} or {@link i}.
	 *
	 * @param mixed $left Left side
	 * @param string $operator Mathematical operator (+, -, *, /)
	 * @param mixed $right Right side
	 * @param string $alias Function alias
	 * @return object Returns a 'internal function' object
	 */
	function math ( $left, $operator, $right, $alias = "" )
	{
		$db = &$this->db;
		
		switch ( $operator )
		{
			case "+":
			case "-":
			case "*":
			case "/":
				break;
			default:
				return $this->error( "unknown_operator" );
		}
		
		$scope = ( $left->scope == "aggregate" || $right->scope == "aggregate" ) ? "aggregate" : "usual";
		$content = "( ". $this->__get_content( $left ) ." ) ". $operator ." ( ". $this->__get_content( $right ) ." )";
		return (object) array( "type" => "function", "content" => $content, "scope" => $scope, "alias" => $alias );
	}
	
	/**
	 * NOW()
	 * 
	 * Create a now function to be used by {@link select} or {@link i}.
	 *
	 * @param string $alias Alias of the result, only with {@link select}
	 * @return object Returns a 'internal function' object
	 */
	function now ( $alias = "" )
	{
		return (object) array( "type" => "function", "content" => "NOW()", "scope" => "usual", "alias" => $alias );
	}
	
	/**
	 * SUM()
	 * 
	 * Create a sum function to be used by {@link select} or {@link i}.
	 *
	 * @param array $column Column reference created by {@link new_table}
	 * @param string $alias Alias of the result, only with {@link select}
	 * @return object Returns a 'internal function' object
	 */
	function sum ( $column, $alias = "" )
	{
		$column = $this->__get_content( $column );
		return (object) array( "type" => "function", "content" => "SUM( ". $column ." )", "scope" => "aggregate", "alias" => $alias );
	}
	
	/**
	 * COUNT()
	 * 
	 * Create a count function to be used by {@link select} or {@link i}.
	 *
	 * @param string $alias Alias of the result, only with {@link select}
	 * @return object Returns a 'internal function' object
	 */
	function count ( $alias = "" )
	{
		return (object) array( "type" => "function", "content" => "COUNT(*)", "scope" => "aggregate", "alias" => $alias );
	}
	
	/**
	 * AVG()
	 * 
	 * Create an avg function to be used by {@link select} or {@link i}.
	 *
	 * @param array $column Column reference created by {@link new_table}
	 * @param string $alias Alias of the result, only with {@link select}
	 * @return object Returns a 'internal function' object
	 */
	function avg ( $column, $alias = "" )
	{
		$column = $this->__get_content( $column );
		return (object) array( "type" => "function", "content" => "AVG( ". $column ." )", "scope" => "aggregate", "alias" => $alias );
	}
	
	/**
	 * MIN()
	 * 
	 * Create a min function to be used by {@link select} or {@link i}.
	 *
	 * @param array $column Column reference created by {@link new_table}
	 * @param string $alias Alias of the result, only with {@link select}
	 * @return object Returns a 'internal function' object
	 */
	function min ( $column, $alias = "" )
	{
		$column = $this->__get_content( $column );
		return (object) array( "type" => "function", "content" => "MIN( ". $column ." )", "scope" => "aggregate", "alias" => $alias );
	}
	
	/**
	 * MAX()
	 * 
	 * Create a max function to be used by {@link select} or {@link i}.
	 *
	 * @param array $column Column reference created by {@link new_table}
	 * @param string $alias Alias of the result, only with {@link select}
	 * @return object Returns a 'internal function' object
	 */
	function max ( $column, $alias = "" )
	{
		$column = $this->__get_content( $column );
		return (object) array( "type" => "function", "content" => "MAX( ". $column ." )", "scope" => "aggregate", "alias" => $alias );
	}
	
	/**
	 * Table creator
	 * 
	 * Create a new table.
	 *
	 * @param string $name Table name
	 * @param string $prefix Text to prefix the column names on the resultset. If omitted uses the table's name followed by __.
	 */
	function new_table ( $name, $prefix = null, $temporal = false )
	{
		$table = new Query_Table( $this, $name, $prefix, $temporal );
		return $table;
	}
	
	/**
	 * Set data
	 * 
	 * Set the query's origin of data.
	 * 
	 * @param object $table Reference to Query_Table object creted by new_table
	 */
	function set_data ( &$table )
	{
		if ( !is_object( $table ) )
			return $this->error( "invalid_table" );
		
		if ( $table->__query->id != $this->id )
			return $this->error( "illegal_mixing_of_different_queries" );
		
		$this->primary_table = &$table;
	}
	
	/**
	 * Selector
	 * 
	 * Set the columns to be selected.
	 *
	 * Each parameter can be a column array defined when creating the table, just the table object to select all columns or * to select all columns from all tables.
	 */
	function select ( )
	{
		// Check if the source of the arguments is a single array
		$arguments = func_get_args();
		if ( count( $arguments ) == 1 && is_array( $arguments[0] ) && !$arguments[0]['table'] )
			$arguments = $arguments[0];
		
		if ( count( $arguments ) == 1 && $arguments[0] == "*" )
			$this->select = "*";
		else
		{
			if ( $this->select == "*" )
				return $this->error( "already_selecting_everything" );
			
			foreach ( $arguments as $i => $argument )
			{
				if ( is_object( $argument ) && $argument->__name )
				{
					foreach ( $argument->__columns as $column )
						$this->select[] = $argument->$column;
				}
				else if ( $argument['table']->__name || $argument->type == "function" )
					$this->select[] = $argument;
				else
					return $this->error( "invalid_select_argument" );
			}
		}
	}
	
	/**
	 * Data filter
	 * 
	 * Filters the data.
	 * 
	 * @param object $condition Condition generated by the {@link i} function
	 */
	function filter ( $condition )
	{
		if ( $condition->type != "condition" )
			return $this->error( "invalid_condition" );
		
		switch ( $condition->scope )
		{
			case "aggregate":
				if ( $this->filter_aggregate !== null )
					return $this->error( "filter_already_set" );
				
				$this->filter_aggregate = $condition->content;
				break;
			default:
				if ( $this->filter !== null )
					return $this->error( "filter_already_set" );
				
				$this->filter = $condition->content;
				break;
		}
	}
	
	/**
	 * Data organizer
	 * 
	 * Sort the data.
	 * 
	 * Each two parameters must be a column array defined when creating the table and either "asc" or "desc".
	 */
	function sort ( )
	{
		// Check if the source of the arguments is a single array
		$arguments = func_get_args();
		if ( count( $arguments ) == 1 && is_array( $arguments[0] ) )
			$arguments = $arguments[0];
		
		$count = count( $arguments );
		if ( $count % 2 != 0 )
			return $this->error( "invalid_sort_arguments" );
		
		for ( $i = 0; $i < $count; $i += 2 )
		{
			$column = $arguments[ $i ];
			$direction = $arguments[ ( $i + 1 ) ];
			
			if ( !is_object( $column['table'] ) && !$column['table']->__name )
				return $this->error( "invalid_column" );
			
			switch ( strtolower( $direction[0] ) )
			{
				case "a":
				case "c":
					$direction = "ASC";
					break;
				case "d":
					$direction = "DESC";
					break;
				default:
					return $this->error( "invalid_sort_direction" );
					break;
			}
			
			$this->sort[] = (object) array( "column" => $column, "direction" => $direction );
		}
	}
	
	/**
	 * Query builder
	 * 
	 * Build and return the query.
	 *
	 * @return string Built SQL
	 */
	function get_sql ( )
	{
		$db = &$this->db;
		
		$query = "";
		
		$query .= "SELECT" ."\n";
		
		$select = array();
		$group_by = array();
		$group = false;
		
		if ( $this->select == "*" )
		{
			foreach ( $this->primary_table->__columns as $column )
			{
				$select[] = $db->nameQuote . eval( "return \$this->primary_table->". $column ."['table']->__name; " ) . $db->nameQuote .".". $db->nameQuote . $column . $db->nameQuote . " AS ". $db->qstr( eval( "return \$this->primary_table->". $column ."['table']->__prefix; " ) . $column );
				$group_by[] = $db->nameQuote . eval( "return \$this->primary_table->". $column ."['table']->__name; " ) . $db->nameQuote .".". $db->nameQuote . $column . $db->nameQuote;
			}
			foreach ( $this->joins as $join )
			{
				if ( $join->table->__temporal )
					continue;
				
				foreach ( $join->table->__columns as $column )
				{
					$select[] = $db->nameQuote . eval( "return \$join->table->". $column ."['table']->__name; " ) . $db->nameQuote .".". $db->nameQuote . $column . $db->nameQuote . " AS ". $db->qstr( eval( "return \$join->table->". $column ."['table']->__prefix; " ) . $column );
					$group_by[] = $db->nameQuote . eval( "return \$join->table->". $column ."['table']->__name; " ) . $db->nameQuote .".". $db->nameQuote . $column . $db->nameQuote;
				}
			}
		}
		else
			foreach ( $this->select as $argument )
			{
				if ( $argument->type == "function" )
				{
					$select[] = $argument->content . ( ( $argument->alias ) ? " AS ". $db->qstr( $argument->alias ) : "" );
					
					if ( $argument->scope == "aggregate" )
						$group = true;
					else
						$group_by[] = $argument->content;
				}
				else
				{
					$select[] = $db->nameQuote . $argument['table']->__name . $db->nameQuote .".". $db->nameQuote . $argument['name'] . $db->nameQuote . " AS ". $db->qstr( $argument['table']->__prefix . $argument['name'] );
					$group_by[] = $db->nameQuote . $argument['table']->__name . $db->nameQuote .".". $db->nameQuote . $argument['name'] . $db->nameQuote;
				}
			}
		$query .= "	". implode( ",\n	", $select ) ."\n";
		
		$query .= "FROM" ."\n";
		$query .= "	". $db->nameQuote . $this->primary_table->__name . $db->nameQuote ."\n";
		foreach ( $this->joins as $join )
			$query .= "	LEFT JOIN ". $db->nameQuote . $join->table->__name . $db->nameQuote ." ON ". $join->condition->content ."\n";
		
		if ( $this->filter )
			$query .= "WHERE\n	". $this->filter ."\n";
		
		if ( $group && count( $group_by ) )
			$query .= "GROUP BY\n	". implode( ",\n	", $group_by ) ."\n";
		
		if ( $this->filter_aggregate )
			$query .= "HAVING\n	". $this->filter_aggregate ."\n";
		
		if ( count( $this->sort ) > 0 )
		{
			$query .= "ORDER BY" ."\n";
			$query_sort = array();
			foreach ( $this->sort as $sort )
				$query_sort[] = $db->nameQuote . $sort->column['table']->__name . $db->nameQuote .".". $db->nameQuote . $sort->column['name'] . $db->nameQuote ." ". $sort->direction;
			$query .= "	". implode( ",\n	", $query_sort ) ."\n";
			unset( $query_sort );
		}
		
		return $query;
	}
	
	/**
	 * Set the offset
	 * 
	 * Set the offset of the first result.
	 * Only works with {@link execute}.
	 * 
	 * @param integer $offset Offset
	 */
	function set_offset( $offset )
	{
		$this->offset = $offset;
	}
	
	/**
	 * Set the number of rows
	 * 
	 * Set the number of rows of the resultset.
	 * Only works with {@link execute}.
	 * 
	 * @param integer $num_rows Number of rows
	 */
	function set_num_rows( $num_rows )
	{
		$this->num_rows = $num_rows;
	}
	
	/**
	 * Query execute
	 * 
	 * Execute the query and return the ADOdb resultset.
	 *
	 * @return object ADOdb resultset object
	 */
	function execute ( )
	{
		if ( $this->offset != 0 || $this->num_rows != -1 )
			$this->rs = &$this->db->SelectLimit( $this->get_sql(), $this->num_rows, $this->offset );
		else
			$this->rs = &$this->db->Execute( $this->get_sql() );
		return $this->rs;
	}
	
	
}

/**
 * Table class
 *
 * Used when defining a new table to work under phpSQL.
 * *NOT* directly called.
 * 
 * @package phpSQL
 */
class Query_Table
{
	/**
	 * Reference to the originating query.
	 * @var object
	 */
	var $__query = null;
	
	/**
	 * Table's name.
	 * @var string
	 */
	var $__name = null;
	
	/**
	 * Table's prefix to be used on SELECT.
	 * @var string
	 */
	var $__prefix = null;
	
	/**
	 * Flag to tell if the table is an internal join to temporal columns.
	 * @var boolean
	 */
	var $__temporal = null;
	
	/**
	 * Tablel's columns
	 * @var array
	 */
	var $__columns = array();
	
	/**
	 * Table's primary keys
	 * @var array
	 */
	var $__pk = null;
	
	/**
	 * Table's foreign keys
	 * @var array
	 */
	var $__fk = null;
	
	/**
	 * Class constructor
	 *
	 * @param object $query Reference to Query object (I consider it the parent object)
	 * @param string $name Table name
	 */
	function Query_Table ( &$query, $name, $prefix, $temporal )
	{
		$this->__query = &$query;
		$this->__name = $name;
		$this->__prefix = ( $prefix !== null ) ? $prefix : $name ."__" ;
		$this->__temporal = $temporal;
		
		$db = &$query->db;
		
		// Obtain the table's columns
		$columns = $db->MetaColumns( $this->__name );
		foreach ( $columns as $name => $column )
		{
			$name = strtolower( $name );
			$this->__columns[] = $name;
			$column->table = &$this;
			$column->meta_type = $db->MetaType( $column->type );
			$this->$name = (array) $column;
		}
		
		// Obtain the table's primary keys
		$this->__pk = $db->MetaPrimaryKeys( $this->__name );
		if ( !$this->__pk )
			$this->__pk = array();
		
		// Obtain the table's foreign keys
		$fk = $db->MetaForeignKeys( $this->__name );
		if ( $fk )
			foreach ( $fk as $table => $foreign_keys )
				foreach ( $foreign_keys as $i => $relation )
					if ( strpos( $relation, "=" ) === false )
						$this->__fk[ $table ][] = array( $relation, $i );
					else
						$this->__fk[ $table ][] = explode( "=", $relation, 2 );
					
		else
			$this->__fk = array();
	}
	
	/**
	 * Table join
	 * 
	 * Join another table to the current one.
	 * The condition can be:
	 * 	* *PHPSQL_JOIN_BY_PK* to use the standard table__column for considering only the primary keys to join the tables;
	 * 	* *PHPSQL_JOIN_BY_FK* to use the foreign keys to join the tables;
	 * 	* A condition generated by {@link @Query} {@link i} function.
	 * 
	 * @param object $table Reference to Query_Table object
	 * @param object $condition Condition to filter the join
	 */
	function join ( &$table, $condition )
	{
		$query = &$this->__query;
		
		if ( $query->id != $table->__query->id )
			return $query->error( "illegal_mixing_of_different_queries" );
		
		switch ( $condition )
		{
			case PHPSQL_JOIN_BY_PK:
				$condition = array();
				foreach ( $table->__pk as $key )
				{
					if ( count( $condition ) > 1 )
						$condition[] = "AND";
					$condition[] = $query->i( $table->$key, "=", eval( "return \$this->". $table->__name ."__". $key .";" ) );
				}
				if ( count( $condition ) > 1 )
					$condition = $query->i( $condition );
				else
					$condition = $condition[0];
				break;
			case PHPSQL_JOIN_BY_FK:
				$condition = array();
				if ( !$this->__fk[ $table->__name ] )
					return $query->error( "foreing_key_missing" );
				
				foreach ( $this->__fk[ $table->__name ] as $keys )
				{
					list( $key_this, $key_table ) = $keys;
					if ( count( $condition ) > 1 )
						$condition[] = "AND";
					$condition[] = $query->i( $table->$key_table, "=", $this->$key_this );
				}
				if ( count( $condition ) > 1 )
					$condition = $query->i( $condition );
				else
					$condition = $condition[0];
				break;
			default:
				if ( $condition->scope == "aggregate" )
					return $query->error( "invalid_condition_for_join" );
				break;
		}
		
		$query->__join( $table, $condition );
	}
	
	/**
	 * Temporal column definition
	 * 
	 * Used to tell phpSQL that the table has a temporal column.
	 * More information can be found on the documentation.
	 * 
	 * @param string $column_name Temporal column's name
	 * @param mixed $when Timestamp, date or a reference to a column to set when in time the set should be retrieved
	 */
	function temporal_column ( $column_name, $when = null )
	{
		if ( $when === null )
			$when = $this->__query->now();
		
		$use_start = $this->__query->temporal_configuration['use_start'];
		$use_end = $this->__query->temporal_configuration['use_end'];
		$is_in_use = $this->__query->temporal_configuration['is_in_use'];
		
		$temporal = $this->__query->new_table( $this->__name . $this->__query->temporal_configuration['table_column_separator'] . $column_name, $this->__prefix, true );
		$condition = array( $this->__query->i( $when, "between", $temporal->$use_start, $temporal->$use_end ), $this->__query->i( $temporal->$is_in_use, "=", 1 ) );
		foreach ( $this->__pk as $pk_column )
			$condition[] = $this->__query->i( $this->$pk_column, "=", eval( "return \$temporal->". $this->__name ."__". $pk_column .";" ) );
		$this->join( $temporal, $this->__query->i_and( $condition ) );
		
		$name = strtolower( $column_name );
		$this->__columns[] = $name;
		$this->$name = $temporal->$name;
	}
}

?>
Return current item: phpSQL