Location: PHPKode > scripts > tgcSqlBuilder > include/tgcSqlBuilder.php
<?php
/**
 * $Id: tgcSqlBuilder.php,v 1.1.1.1 2004/02/14 01:43:19 luckec Exp $
 *
 * Class that helps you to generate sql-statements without handling string concatenation.
 *
 * @package     tgcSqlBuilder
 * @author      Carsten Lucke <hide@address.com>
 * @copyright   Carsten Lucke <http://www.tool-garage.de>
 */

 
 
/**
 * Uses PEAR error-management
 */
require_once 'PEAR.php';

/**
 * Uses PEAR::DB
 */
require_once 'DB.php';



/**
 * sorting direction: ascending
 *
 * @access      public
 */
define('SQLBUILDER_ORDER_ASC', 'ASC');

/**
 * sorting direction: descending
 *
 * @access      public
 */
define('SQLBUILDER_ORDER_DESC', 'DESC');

/**
 * equal
 *
 * @access	public
 */
define( 'SQLBUILDER_COMP_EQUAL', '=' );

/**
 * equal
 *
 * @access	public
 */
define( 'SQLBUILDER_COMP_NOT_EQUAL', '<>' );

/**
 * lesser than
 *
 * @access	public
 */
define( 'SQLBUILDER_COMP_LESSER_THAN', '<' );

/**
 * lesser equal
 *
 * @access	public
 */
define( 'SQLBUILDER_COMP_LESSER_EQUAL', '<=' );

/**
 * greater equal
 *
 * @access	public
 */
define( 'SQLBUILDER_COMP_GREATER_EQUAL', '>=' );

/**
 * greater than
 *
 * @access	public
 */
define( 'SQLBUILDER_COMP_GREATER_THAN', '>' );

/**
 * starts with
 *
 * @access	public
 */
define( 'SQLBUILDER_COMP_STARTSWITH', 'LIKE_' );

/**
 * contains
 *
 * @access	public
 */
define( 'SQLBUILDER_COMP_CONTAINS', '_LIKE_' );

/**
 * ends with
 *
 * @access	public
 */
define( 'SQLBUILDER_COMP_ENDSWITH', '_LIKE' );

/**
 * BETWEEN
 *
 * @access	public
 */
define( 'SQLBUILDER_COMP_BETWEEN', 'BETWEEN' );

/**
 * logical AND
 *
 * @access      public
 */
define('SQLBUILDER_LOGICAL_AND', 'AND');

/**
 * logical OR
 *
 * @access      public
 */
define('SQLBUILDER_LOGICAL_OR', 'OR');

/**
 * Invalid parameter cobination
 *
 * @access	public
 */
define( 'SQLBUILDER_ERROR_INVALID_PARAM_COMBO', 1 );

/**
 * Wrong parameter for a method
 *
 * @access      public
 */
define('SQLBUILDER_ERROR_INVALID_PARAMETER', 2);

/**
 * Wrong parametercount for a method
 *
 * @access      public
 */
define('SQLBUILDER_ERROR_INVALID_PARAMETERCOUNT', 3);

 /**
 * operation on a column that does not exist in object data (i.e. remove)
 *
 * @access      public
 */
define('SQLBUILDER_ERROR_COLUMN_DOES_NOT_EXIST', 4);

 /**
 * the user specified an invalid logical operator
 *
 * @access      public
 */
define('SQLBUILDER_ERROR_INVALID_LOGICAL_OPERATOR', 5);

/**
 * No table has been specified
 *
 * @access	public
 */
define( 'SQLBUILDER_ERROR_NO_TABLE_FOUND', 6 );




/**
 * Class that helps you to generate sql-statements without handling string concatenation.
 *
 * @package     tgcSqlBuilder
 * @access      public
 * @version     1.0.0
 * @author      Carsten Lucke <hide@address.com>
 * @abstract
 */
class tgcSqlBuilder
{
    
   /**
    * PEAR::DB object
    *
    * @access       private
    * @var          object          PEAR::DB connection object
    */
    var $_dbc;
    
   /**
    * Tablename on which a sql-statement concerns.
    *
    * It's a associative array, that contains the tablenames as keys and aliases as values.
    * If no alias is specified the value is NULL.
    *
    * @access       private
    * @var          array           tablename
    */
    var $_tables = array();

    
    
   /**
    * Constructor
    *
    * @access       public
    * @param        object          $dbc            PEAR::DB connection object
    * @return       mixed           PEAR_Error or an sqlBuilder object
    */
    function tgcSqlBuilder(&$dbc)
    {
        $this->_dbc     =   $dbc;
    }
    
    
   /**
    * Add the statements table.
    *
    * <code>
    * $sql      =   new tgcSqlBuilder_Select($dbc);
    * $sql->addTable('users');
    * // now the generated sql-statement would look like: SELECT ... FROM users ...
    * </code>
    *
    * @access       public
    * @param        string          $tableName      tablename
    * @param        string          $alias          aliasname
    */
    function addTable($tableName, $alias = null)
    {
        $this->_tables[$tableName]  =   $alias;
    }
    
   /**
    * Remove one or all tablename(s).
    *
    * If you specify a tablename, then this one will be removed. 
    * If you don't specify a param, then all tablenames will be removed.
    *
    * <code>
    * // remove a specific tablename
    * $sql      =   new tgcSqlBuilder_Select($dbc);
    * $sql->removeTable('users');
    *
    * // remove all tablenames
    * $sql      =   new tgcSqlBuilder_Select($dbc);
    * $sql->removeTable();
    * </code>
    *
    * @access       public
    * @param        string          $tableName      tablename
    */
    function removeTable($tableName = null)
    {
        // remove a specific table
        if(! is_null($tableName)) {
            if (isset($this->_tables[$tableName])) {
                unset($this->_tables[$tableName]);
            }
            return;
        }
        
        // remove all tables
        $this->_tables      =   array();
    }
    
   /**
    * Generate the sql-statement.
    *
    * This method generates a query based on the object-information and returns it as a string.
    *
    * <code>
    * $sql      =   new tgcSqlBuilder_Select($dbc);
    * $query    =   $sql->generateQuery();
    * </code>
    *
    * @access       public
    * @return       string          sql-statement
    * @abstract
    */
    function generateQuery()
    {
    }
    
   /**
    * Escapes a variable appropriate for the used database-system.
    *
    * @access       public
    * @param        mixed           $var            variable you want to be escaped
    * @return       mixed           escaped variable
    */
    function escape($var)
    {
        return $this->_dbc->quote($var);
    }
    
   /**
    * Reset the object's whole information.
    *
    * @access       public
    * @abstract
    */
    function reset()
    {
    }
    
   /**
    * Generates a string based on the objects WHERE information.
    *
    * Must only be called from objects that contain a WHERE attribute.
    *
    * @access       private
    * @param        array           $where           WHERE attribute
    * @param        array           $rawWhere        raw WHERE attribute
    */
    function _generateWhereInformation(&$where, &$rawWhere)
    {
        $statement      =       '';
        
        // WHERE['AND']
        if (count($where[SQLBUILDER_LOGICAL_AND])) {
            foreach ($where[SQLBUILDER_LOGICAL_AND] as $statementData) {
                if ($statement != '') {
                    $statement  .=  ' ' . SQLBUILDER_LOGICAL_AND . ' ';
                }
                $statement  .=  sprintf ("%s.%s %s %s",     $statementData['table'],
                                                            $statementData['column'],
                                                            str_replace('_', '', $statementData['compOp']),
                                                            $this->_createFilteredValue($statementData['value'], $statementData['compOp'])
                                        );
            }
        }
        
        // raw WHERE['AND']
        if (count($rawWhere[SQLBUILDER_LOGICAL_AND])) {
            foreach ($rawWhere[SQLBUILDER_LOGICAL_AND] as $stmnt) {
                if ($statement != '') {
                    $statement  .=  ' ' . SQLBUILDER_LOGICAL_AND . ' ';
                }
                $statement  .=  $stmnt;
            }
        }
        
        // WHERE['OR']
        if (count($where[SQLBUILDER_LOGICAL_OR])) {
            foreach ($where[SQLBUILDER_LOGICAL_OR] as $statementData) {
                if ($statement != '') {
                    $statement  .=  ' ' . SQLBUILDER_LOGICAL_OR . ' ';
                }
                $statement  .=  sprintf ("%s.%s %s %s",     $statementData['table'],
                                                            $statementData['column'],
                                                            str_replace('_', '', $statementData['compOp']),
                                                            $this->_createFilteredValue($statementData['value'], $statementData['compOp'])
                                        );
            }
        }
        
        // raw WHERE['OR']
        if (count($rawWhere[SQLBUILDER_LOGICAL_OR])) {
            foreach ($rawWhere[SQLBUILDER_LOGICAL_OR] as $stmnt) {
                if ($statement != '') {
                    $statement  .=  ' ' . SQLBUILDER_LOGICAL_OR . ' ';
                }
                $statement  .=  $stmnt;
            }
        }
        
        return $statement;
    }
    
   /**
    * Gets a value and a filtername and returns the value with applied filter-characters, if necessary.
    * The value that will be returned is also being qouted appropriate to your database system.
    *
    * Example: If the value would be (string) "foobar" and the filter SQLBUILDER_COMP_CONTAINS, then 
    * the returned value would be (string) "%foobar%".
    *
    * @access       private
    * @param        mixed           $value          value on which the filter has to be applied
    * @param        string          $filter         constant representing a filter
    * @return       mixed           filtered value
    */
    function _createFilteredValue($value, $filter)
    {
        $return = null;
        
        switch ($filter) {
            case SQLBUILDER_COMP_STARTSWITH:
                $return =   $this->escape($value . '%');
                break;
                
            case SQLBUILDER_COMP_CONTAINS:
                $return =   $this->escape('%' . $value . '%');
                break;
                
            case SQLBUILDER_COMP_ENDSWITH:
                $return =   $this->escape('%' . $value);
                break;
            
            case SQLBUILDER_COMP_BETWEEN:
                $return =   $this->escape($value[0]) . ' AND ' . $this->escape($value[1]);
                break;
                
            default:
                $return =   $this->escape($value);
                break;
        }
        
        return $return;
    }
    
   /**
    * Generates a string based on the objects ORDER BY information.
    *
    * Must only be called from objects that contain a ORDER BY attribute.
    *
    * @access       private
    * @param        mixed           $att           ORDER BY attribute
    */
    function _generateOrderByInformation(&$att)
    {
        $statements     =       array();
        
        foreach ($att as $orderByData) {
            if (! is_null($orderByData['column'])) {
                array_push  ($statements, sprintf("%s.%s %s",   $orderByData['table'], 
                                                                $orderByData['column'], 
                                                                $orderByData['direction'])
                            );
            } else {
                array_push  ($statements, sprintf("%s %s",      $orderByData['table'], 
                                                                $orderByData['direction'])
                            );
            }
        }
        
        return implode(', ', $statements);
    }
    
   /**
    * Generates a string based on the objects LIMIT information.
    *
    * Must only be called from objects that contain a LIMIT attribute.
    *
    * @access       private
    * @param        mixed           $att           LIMIT attribute
    */
    function _generateLimitInformation(&$att)
    {
        if (! isset($att['rows'])) {
            return $att['offset'];
        }
        
        return sprintf("%d, %d", $att['offset'], $att['rows']);
    }
    
   /**
    * Adds the data for a WHERE or HAVING statement
    *
    * @access       private
    * @param        array       $att        the object's WHERE or HAVING attribute
    * @param        string      $table      tablename
    * @param        string      $column     columnname
    * @param        mixed       $value      value(s)
    * @param        string      $compOp     comparison operator
    * @param        string      $logic      logical linkup
    */
    function _addWhereHaving(&$att, $table, $column, $value, $compOp = null, $logic = null)
    {
        $where['table']     =   $table;
        $where['column']    =   $column;
        $where['value']     =   $value;
        $where['compOp']    =   is_null($compOp)    ? SQLBUILDER_COMP_EQUAL     : $compOp;
        $logic              =   is_null($logic)     ? SQLBUILDER_LOGICAL_AND    : $logic;
        
        array_push($att[$logic], $where);
    }
    
   /**
    * Remove a WHERE or HAVING statement
    *
    * @access       private
    * @param        array       $att        the object's WHERE or HAVING attribute
    * @param        string      $table      tablename
    * @param        string      $column     columnname
    * @param        string      $logic      logical operation (possible values: SQLBUILDER_LOGICAL_AND, SQLBUILDER_LOGICAL_OR)
    * @return       mixed       true on success or PEAR_Error (possible error(s): SQLBUILDER_ERROR_INVALID_PARAM_COMBO)
    */
    function _removeWhereHaving(&$att, $table = null, $column = null, $logic = null)
    {
        if (is_null($table) && is_null($column)) {
            $att    =   array
                        (
                                SQLBUILDER_LOGICAL_AND  =>  array(),
                                SQLBUILDER_LOGICAL_OR   =>  array()
                        );
            return true;
        }
        
        if (! is_null($table) && ! is_null($column)) {
            if (is_null($logic)) {
                foreach ($att[SQLBUILDER_LOGICAL_AND] as $index => $whereData) {
                    if ($whereData['table'] == $table && $whereData['column'] == $column) {
                        unset($att[SQLBUILDER_LOGICAL_AND][$index]);
                    }
                }
                foreach ($att[SQLBUILDER_LOGICAL_OR] as $index => $whereData) {
                    if ($whereData['table'] == $table && $whereData['column'] == $column) {
                        unset($att[SQLBUILDER_LOGICAL_OR][$index]);
                    }
                }
                return true;
            }
            foreach ($att[$logic] as $index => $whereData) {
                if ($whereData['table'] == $table && $whereData['column'] == $column) {
                    unset($att[$logic][$index]);
                }
            }
            return true;
        }
        
        return PEAR::raiseError (
                                    'The combination of parameters is invalid',
                                    SQLBUILDER_ERROR_INVALID_PARAM_COMBO,
                                    null,
                                    null,
                                    'You have to call the method with none or two parameters'
                                );
    }

   /**
    * Add a raw WHERE or HAVING statement.
    *
    * @access       private
    * @param        array       $att            the object's WHERE or HAVING attribute
    * @param        string      $statement      raw HAVING statement
    * @param        string      $logic          SQLBUILDER_LOGICAL_AND or SQLBUILDER_LOGICAL_OR
    */
    function _addRawWhereHaving(&$att, $statement, $logic = SQLBUILDER_LOGICAL_AND)
    {
        array_push($att[$logic], $statement);
    }
    
   /**
    * Remove a raw WHERE or HAVING statement.
    *
    * @access       private
    * @param        array       $att            the object's WHERE or HAVING attribute
    * @param        string      $logic          SQLBUILDER_LOGICAL_AND or SQLBUILDER_LOGICAL_OR
    * @return       mixed       true on success, else PEAR_Error
    */
    function _removeRawWhereHaving(&$att, $logic = null)
    {
        // check $logic, if specified
        if (! is_null($logic) && ($logic != SQLBUILDER_LOGICAL_AND && $logic != SQLBUILDER_LOGICAL_OR)) {
            return PEAR::raiseError (
                                        'Invalid logical operator',
                                        SQLBUILDER_ERROR_INVALID_LOGICAL_OPERATOR
                                    );
        }
        
        // remove all raw WHERE statements
        if (is_null($logic)) {
            $att            =       array
                                    (
                                        SQLBUILDER_LOGICAL_AND  =>  array(),
                                        SQLBUILDER_LOGICAL_OR   =>  array()
                                    );
            return true;
        }
        
        // delete depending on $logic
        $att[$logic]        =       array();
        return true;
    }
    
}
?>
Return current item: tgcSqlBuilder