<?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><=, =, >=, !=, 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;
}
}
?>