/include/engine/core/iwQueryBuilder.class.php

<?php

/*

 * Nexus is a web-based file management application.

 * Copyright (C) Karim Shehadeh

 *

 * This program is free software; you can redistribute it and/or

 * modify it under the terms of the GNU General Public License

 * as published by the Free Software Foundation; either version 2

 * of the License, or (at your option) any later version.

 *

 * This program is distributed in the hope that it will be useful,

 * but WITHOUT ANY WARRANTY; without even the implied warranty of

 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the

 * GNU General Public License for more details.

 *

 * You should have received a copy of the GNU General Public License

 * along with this program; if not, write to the Free Software

 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.

 */



class iwCriteria

{

    /**

    * An array of where clauses that are contained within this

    * higher-level where clause.

    * 

    * @var array

    */

    protected $_nestedCriteria = array();

    

    /**

     * The left operand (field name)

     */

    protected $_field = '';

    

    /**

     * The parameterized field value (as in :value)

     * @var string 

     */

    protected $_fieldParam = '';

    

    /**

    * The right operand (value)

    * 

    * @var mixed

    */

    protected $_value = '';

    

    /**

    * This can be =,!=,<,>,<=,>=,LIKE, etc.

    * 

    * @var string

    */

    protected $_equality = '';

    

    /**

    * The automatically generated set of 

    * parameters to be used with parameterized queries.

    * 

    * @var array

    */

    protected $_generatedParameters = array();

    

    /**

    * How this attached to clauses that come before this one.  As in

    * OR, AND, etc.

    * 

    * @var string

    */

    protected $_glue = '';    



    /**

    * The glue used to attach the top-level criteria to the set of nested criteria

    *     

    * @var string

    */

    protected $_nestedGlue = '';    

    

    /**

    * Indicates whether or not this criterium's value should be 

    * parameterized when getParameters is called.

    * 

    * @var mixed

    */

    protected $_parameterize = true;

    

    /**

    * Constructs a basic where clause with a left operand, a right operand

    * an operator and the glue that attaches this clause to the previous.

    * 

    * @param string $field The field name to compare against.

    * @param string $equality The operator

    * @param mixed $value The value to compare

    * @param string $glue The left-side attachment operator (AND, OR, etc) or empty for none.

    * @param bool $parameterize True to mark as parameterized, false otherwise.

    */

    public function  __construct($field='',$equality='',$value='',$glue='',$parameterize=true)

    {

        $this->setField($field);

        $this->_equality = $equality;

        $this->_value = $value;

        $this->_glue = $glue;

        $this->_parameterize = $parameterize;

    }

    

    /**

    * Indicate whether or not the value should be parameterized

    * 

    * @param bool $p true to parameterize, false otherwise.

    */

    public function setParameterize($p)

    {

        $this->_parameterize = $p;

    }

    

    /**

    * Indicates whether or not this criteria will be parameterized

    * @return bool Returns true to parameterize.

    * 

    */

    public function isParameterized()

    {

        return $this->_parameterize;

    }

    

    /**

    * Sets the field name for this criteria.  Use this 

    * in order to automatically genearate a parameter name

    * when using parameterized queries.

    * 

    * @param string $field The field name

    */

    public function setField($field)

    {

        $this->_field = $field;

		

		$prefix = ':'.str_replace('.','',$this->_field).'_';

		

        $this->_fieldParam = uniqid($prefix);    

    }

    

    /**

    * Adds a new inner clause to this higher-level clause

    * 

    * @param string $glue 

    * @param iwCriteria $inner The new sublevel criteria

    */

    public function addNestedCriteria(iwCriteria $inner)

    {

        $this->_nestedCriteria[] = $inner;

    }



    public function setNestedCriteriaGlue($glue)

    {

        $this->_nestedGlue = $glue;

    }

    

    /**

     * Gets the array of parameters to pass to a parameterized 

     * PDO method.  Returns in the following format:

     *     :param => value

     * @return array The associative array of parameters

     */

    public function getParameters()

    {

        $params = array();

        

        if ($this->_parameterize && ($this->_field != ''))

        {

            $params[$this->_fieldParam] = $this->_value;        

        }



        foreach($this->_nestedCriteria as $criteria)

        {

            $params = array_merge($params,$criteria->getParameters());

        }

        

        return $params;

    }

    

    

    /**

    * This will turn the where clause into a string that can be added 

    * to a query.

    * @param bool $suppressGlue Suppress the glue even if one exists

    */

    public function toString($suppressGlue=false)

    {

    	$clause = '';

		

        // If no top-level criteria is given then skip this part and 

        //  output only the sublevel criteria.  This can happen when the 

        //  user wnats to produce something that looks like this:

        //  "AND (val=1 OR val=2 OR val=3)" where 'AND' is the nested glue

        //  and "val=X" are each a nested criteria

        if ($this->_field != '')

        {

            $clause = sprintf("%s %s %s %s ",

                            $suppressGlue ? '' : strtoupper($this->_glue),

                            $this->_field,

                            strtoupper($this->_equality),

                            $this->_parameterize ? $this->_fieldParam : $this->_value);

        }

             

        

        if (count($this->_nestedCriteria) > 0)        

        {

            $clause .= strtoupper($this->_nestedGlue) . ' ( ';

            $first = true;

            foreach ($this->_nestedCriteria as $criteria)

            {

                $clause .= $criteria->toString($first);

                $first = false;

            }

            $clause .= ' ) ';

        }

                

        return $clause;

    }

}  







class iwJoinClause

{

    /**

     * The type of join expression (e.g. INNER, LEFT, RIGHT, LEFT OUTER, RIGHT OUTER, etc.

     * @var string

     */

    protected $_joinType;

    

    /**

     * The ON criteria

     * @var array

     */

    protected $_critera;



        

    public function __construct($joinType,iwCriteria $criteria)

    {

        $this->_critera = array();

        

        $this->_joinType = $joinType;        

        if ($criteria != null)

        {

            $this->_critera[] = $criteria;

        }

    }



    

    /**

     * Adds a new inner clause to this higher-level clause

     * 

     * @param iwCriteria $criteria The new criteria

     */

    public function addCriteria(iwCriteria $criteria)

    {

        $this->_critera[] = $criteria;

    }

        

    public function getParameters()

    {

        $params = array();

        

        foreach($this->_criteria as $c)

        {

            $params = array_merge($params,$c->getParmaeters());

        }

        

        return $params;

    }

    

    /**

     * Converts the current properties of the join object into

     * a string that can be given to a parameterized execution

     * method.

     * @return mixed If all properties valid, returns the join string 

     */

    public function toString()

    {

        if (count($this->_critera) == 0)

        {

            return false;

        }

        

        $final = sprintf ("%s ON ", strtoupper($this->_joinType));        

        

        foreach($this->_critera as $c)

        {

            $final .= ' '.$c->toString();

        }

        

        return $final;

    }    

}





class iwWhereClause

{    

    /**

     * The ON criteria array

     * @var array

     */

    protected $_criteria;

    

    public function __construct(iwCriteria $criteria=null)

    {

        $this->_criteria = array();

		

		if ($criteria)

		{

        	$this->addCriteria($criteria);

		}

    }

    

    public function addCriteria(iwCriteria $criteria)

    {

        if ($criteria)

        {

            $this->_criteria[] = $criteria;

        }

    }

    

    public function getParameters()

    {

        $params = array();

        

        foreach($this->_criteria as $c)

        {

            $params = array_merge($params,$c->getParameters());

        }

        

        return $params;

    }

    

    /**

     * Converts the current properties of the join object into

     * a string that can be given to a parameterized execution

     * method.

     * @return mixed If all properties valid, returns the join string 

     */

    public function toString()

    {

        if (count($this->_criteria) == 0)

        {

            return false;

        }

        

        $final = sprintf ("WHERE ");

        $first = true;

        foreach($this->_criteria as $c)

        {            

            $final .= ' '.$c->toString($first);

            $first = false;

        }

        

        return $final;

    }    

}  





class iwQueryBuilder

{

    protected $_sourceTables = array();

    protected $_outputFields = array();

    protected $_whereClause = null;

	protected $_joinClauses = array(); 

	protected $_orderBy = array();

	protected $_groupBy = array();

	protected $_limitOffset = 0;

	protected $_limitRowCount= 0;

	

    public function __construct()

    {

        $this->clear();

    }

    

	public function addTable($table,$as='')

	{

		$this->_sourceTables[$table] = $as;

	}

	

	public function addOutputField($field, $as='')

	{

		$this->_outputFields[$field] = $as;

	}

	

	public function setWhere(iwWhereClause $where)

	{

		$this->_whereClause = $where;

	}

	

	public function addJoin(iwJoinClause $join)

	{

		$this->_joinClauses[] = $join;

	}

	

	public function addOrderBy($field,$direction)

	{

		$this->_orderBy[$field] = $direction;

	}

	

	public function addGroupBy($field)

	{

		$this->_groupBy[] = $field;

	}



	public function setLimit($rowCount,$offset=0)

	{

		$this->_limitRowCount = $rowCount;

		$this->_limitOffset = $offset;

	}

	

	public function getParameters()

	{

		$params = array();

		

		if (count($this->_joinClauses) > 0)

		{

			foreach($this->_joinClauses as $clause)

			{

				$params = array_merge($params,$clause->getParameters());

			}	

		}

		

		if ($this->_whereClause != null)

		{

			$params = array_merge($params,$this->_whereClause->getParameters());

		}



		return $params;				

	}	

	

    public function clear()

    {

        $this->_joinClauses = array();

        $this->_whereClause = null;

        $this->_orderBy = array();

        $this->_groupBy = array();

        $this->_limitOffset = 0;

        $this->_limitRowCount = 0;

        $this->_outputFields = array();

        $this->_sourceTables = array();        

    }

    

	public function toString()

	{		

		$outputFields = array();

		foreach($this->_outputFields as $field=>$as)

		{

			$outputFields[] = ($as != '') ? sprintf('%s as %s',$field,$as) : $field;

		}

		

		$sourceTables = array();

		foreach($this->_sourceTables as $table=>$as)

		{

			$sourceTables[] = ($as != '') ? sprintf('%s as %s',$table,$as) : $table;

		}

		

		$select = 'SELECT '.implode(',',$outputFields);

		$select .= ' FROM '.implode(',',$sourceTables);

		

		$params = array();

		if (count($this->_joinClauses) > 0)

		{

			$clauseStrings = array();

			foreach($this->_joinClauses as $clause)

			{

				$params = array_merge($params,$clause->getParameters());

				$clauseStrings[] = $clause->toString();

			}	

			

			$select .= ' '.implode(' ',$clauseStrings);

		}

		

		if ($this->_whereClause != null)

		{

			$params = array_merge($params,$this->_whereClause->getParameters());

			$select .= ' '.$this->_whereClause->toString();

		}

		

		if (count($this->_groupBy) > 0)

		{

			$select .= ' GROUP BY '.implode(',',$this->_groupBy);

		}



		if (count($this->_orderBy) > 0)

		{

			$clauseStrings = array();

			foreach($this->_orderBy as $field=>$direction)

			{

				$clauseStrings[] = $field.' '.$direction;

			}				

			$select .= ' ORDER BY '.implode(',',$clauseStrings);

		}

		

		if ($this->_limitRowCount > 0)

		{

			$select .= ' LIMIT ';

			if ($this->_limitOffset > 0)

			{

				$select .= $this->_limitOffset.',';

			}

			

			$select .= $this->_limitRowCount;

		}

		

		return $select;

	}

}

  

?>