<?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;
}
}
?>