msql.class

Ir para a documentação deste ficheiro.

<?php
 
class MSQL
{
    var $db;
 
    var $distinct;
 
    var $columns;
 
    var $tables;
 
    var $where;
 
    var $groupBy;
 
    var $having;
 
    var $orderBy;
 
    var $join;
 
    var $parameters;
 
    var $command;
 
    var $range;
 
    var $bind;
    
    public $limit;
    
    public $offsetSQL;
    
    private $newJoins = array();
 
    public $considerarTabelasSubSelects = false;
 
    function __construct($columns = '', $tables = '', $where = '', $orderBy = '', $groupBy = '', $having = '')
    {
        $this->clear();
        $this->setColumns($columns);
        $this->setTables($tables);
        $this->setWhere($where);
        $this->setGroupBy($groupBy);
        $this->setHaving($having);
        $this->setOrderBy($orderBy);
        $this->join = null;
        $this->parameters = null;
        $this->range = null;
        $this->db = null;
        $this->bind = false;
        $this->stmt = NULL;
    }
 
    private function getTokens1($string, &$array)
    {
        $tok = strtok($string, ",");
 
        while ($tok)
        {
            $tok = trim($tok);
            $array[$tok] = $tok;
            $tok = strtok(",");
        }
    }
 
    private function getTokens($string, &$array)
    {        
        if ($string == '')
            return;
 
        // clear() seta todos os atributos como uma string vazia. Com isso, ao
        // tentar utilizar a variável como um array, um warning é gerado.
        // Em vez de corrigir o clear(), e provavelmente quebrar alguma coisa,
        // achei melhor só garatir o tipo aqui.
        if (!$array)
        {
            $array = array();
        }
 
        // Suporte a passagem de parametros como array no setColumns() e outros
        if ( is_array($string) )
        {
            $string = implode(',', $string);
        }
        
        $source = str_split($string . ',');
        $tok = '';
        $l = count($source);
        $can = 0;
 
        for ($i = 0; $i < $l; $i++)
        {
            $c = $source[$i];
 
            if (!$can)
            {
                if ($c == ',')
                {
                    $tok = trim($tok);
                    $array[$tok] = $tok;
                    $tok = '';
                }
                else
                {
                    $tok .= $c;
                }
            }
            else
            {
                $tok .= $c;
            }
 
            if ($c == '(')
                $can++;
 
            if ($c == ')')
                $can--;
        }
    }
 
    private function getJoin()
    {
        global $MIOLO;
        $MIOLO->Uses('database/' . $this->db->system . '/msqljoin.class');
        $className = "{$this->db->system}SqlJoin";
        $join = new $className();
        $join->_sqlJoin($this);
    }
 
    function setDb($db)
    {
        $this->db = $db;
        
        return $this;
    }
 
    function setColumns($string, $distinct = false)
    {
        $this->getTokens($string, $this->columns);
        $this->distinct = $distinct;
        
        return $this;
    }
    
    public function setColumnsOverride(array $columns)
    {
        return $this->clearColumns()->setColumns($columns);
    }
    
    public function clearColumns()
    {
        $this->columns = null;
        
        return $this;
    }
    
    public function getColumns()
    {
        return $this->columns;
    }
 
    function setTables($string)
    {
        $this->getTokens($string, $this->tables);
 
        $subSelect = strstr(strtoupper($string), 'SELECT ');
        if ( $this->considerarTabelasSubSelects && $subSelect && empty($this->tables) ) {
            $this->tables[$string] = $string;
        }
        
        return $this;
    }
    
    public function clearTables()
    {
        $this->tables = null;
        
        return $this;
    }
 
    function setGroupBy($string)
    {
        $this->getTokens($string, $this->groupBy);
        
        return $this;
    }
    
    public function clearGroupBy()
    {
        $this->groupBy = null;
        
        return $this;
    }
 
    function setOrderBy($string)
    {
        $this->getTokens($string, $this->orderBy);
        
        return $this;
    }
    
    public function getOrderBy()
    {
        return $this->orderBy;
    }
    
    public function clearOrderBy()
    {
        $this->orderBy = null;
        
        return $this;
    }
    
    public function addEqualCondition($column, $value)
    {
        $this->setWhere($column . ' = ?', array($value));
        
        return $this;
    }
    
    public function addNotEqualCondition($column, $value)
    {
        $this->setWhere($column . ' <> ?', array($value));
        
        return $this;
    }
    
    public function addBetweenCondition($column, $value1, $value2)
    {
        $this->setWhere($column . ' BETWEEN ? AND ?', array($value1, $value2));
        
        return $this;
    }
    
    public function addGreaterCondition($column, $value)
    {
        $this->setWhere($column . ' > ?', array($value));
        
        return $this;
    }
    
    public function addGreaterEqualCondition($column, $value)
    {
        $this->setWhere($column . ' >= ?', array($value));
        
        return $this;
    }
    
    public function addSmallerCondition($column, $value)
    {
        $this->setWhere($column . ' < ?', array($value));
        
        return $this;
    }
    
    public function addSmallerEqualCondition($column, $value)
    {
        $this->setWhere($column . ' <= ?', array($value));
        
        return $this;
    }
    
    public function addNotIlikeCondition($column, $value)
    {
        $this->setWhere($column . ' NOT ILIKE ?', array($value));
        
        return $this;
    }
    
    public function addNotLikeCondition($column, $value)
    {
        $this->setWhere($column . ' NOT LIKE ?', array($value));
        
        return $this;
    }
    
    public function addLikeCondition($column, $value)
    {
        $this->setWhere($column . ' LIKE ?', array($value));
        
        return $this;
    }
    
    public function addLikeConditionUnaccent($column, $value)
    {
        $this->setWhere('UNACCENT(' . $column . ') LIKE UNACCENT(?)', array($value));
        
        return $this;
    }
    
    public function addIlikeCondition($column, $value)
    {
        $this->setWhere($column . ' ILIKE ?', array($value));
        
        return $this;
    }
    
    public function addIlikeConditionUnaccent($column, $value)
    {
        $this->setWhere('UNACCENT(' . $column . ') ILIKE UNACCENT(?)', array($value));
 
        return $this;
    }
    
    public function addWhereIn($column, array $values)
    {
        $this->setWhere($column . ' IN ' . $this->convertArrayToIn($values));
        
        return $this;
    }
    
    public function addWhereNotIn($column, array $values)
    {
        $this->setWhere($column . ' NOT IN ' . $this->convertArrayToIn($values));
        
        return $this;
    }
 
    public function setWhere($string, array $parameters = null)
    {
        $this->where .= (($this->where != '') && ($string != '') ? " and " : "") . $string;
     
        foreach ( (array) $parameters as $pValue )
        {
            $this->addParameter($pValue);
        }
        
        return $this;
    }
    
    public function getWhere()
    {
        return $this->where;
    }
 
    function setWhereAnd($string, array $parameters = null)
    {
        $this->where .= (($this->where != '') && ($string != '') ? " and " : "") . $string;
        
        if ( $parameters )
        {
            foreach ( $parameters as $pValue )
            {
                $this->addParameter($pValue);
            }
        }
        
        return $this;
    }
 
    function setWhereOr($string, array $parameters = null)
    {
        $this->where .= (($this->where != '') && ($string != '') ? " or " : "") . $string;
        
        foreach ( $parameters as $pValue )
        {
            $this->addParameter($pValue);
        }
        
        return $this;
    }
    
    public function getLimit()
    {
        return $this->limit;
    }
 
    public function setLimit($limit)
    {
        if ( is_numeric($limit) || strlen($limit) == 0 )
        {
            $this->limit = $limit;
        }
        
        return $this;
    }
    
    public function clearLimit()
    {
        $this->limit = null;
        
        return $this;
    }
 
    public function getOffsetSQL()
    {
        return $this->offsetSQL;
    }
 
    public function setOffsetSQL($offsetSQL)
    {
        if ( is_numeric($offsetSQL) || strlen($offsetSQL) == 0 )
        {
            $this->offsetSQL = $offsetSQL;
        }
        
        return $this;
    }
 
    public function clearOffsetSQL()
    {
        $this->offsetSQL = null;
        
        return $this;
    }
    
    function setHaving($string)
    {
        $this->having .= (($this->having != '') && ($string != '') ? " and " : "") . $string;
        
        return $this;
    }
 
    function setHavingAnd($string)
    {
        $this->having .= (($this->having != '') && ($string != '') ? " and " : "") . $string;
        
        return $this;
    }
 
    function setHavingOr($string)
    {
        $this->having .= (($this->having != '') && ($string != '') ? " or " : "") . $string;
        
        return $this;
    }
 
    function setJoin($table1, $table2, $cond, $type = 'INNER')
    {
        $this->join[] = array
            (
            $table1,
            $table2,
            $cond,
            $type
            );
        
        return $this;
    }
 
    function setLeftJoin($table1, $table2, $cond)
    {
        $this->SetJoin($table1, $table2, $cond, 'LEFT');
        
        return $this;
    }
 
    function setRightJoin($table1, $table2, $cond)
    {
        $this->SetJoin($table1, $table2, $cond, 'RIGHT');
        
        return $this;
    }
 
    function bind($parameters = null)
    {
        $this->bind = true;
        $this->SetParameters($parameters);
    }
 
    function prepare($parameters = null)
    {
        global $MIOLO;
 
        if ($this->bind)
            return;
 
        if (!$parameters)
            return;
 
        if (!is_array($parameters))
        {
            $parameters = array($parameters);
        }
 
        $prepared = '';
        $sqlText = $this->command;
        $i = 0;
 
        while (true)
        {
            $pos = strpos($sqlText, '?');
 
            if ($pos == false)
            {
                $prepared .= $sqlText;
                break;
            }
            else
            {
                if ($pos > 0)
                {
                    $prepared .= substr($sqlText, 0, $pos);
                }
 
                $value = $parameters[ $i++ ];
                
                if ( substr($value, 0, 1) == ':' )
                {
                    $prepared .= substr($value, 1);
                }
                else if ( $value instanceof MSQLExpr )
                {
                    $prepared .= $value->getValue();
                }
                else if ( is_array($value) )
                {
                    $prepared .= $this->convertArrayToIn($value);
                }
                else
                {
                    $prepared .= is_null( $p = $value ) ? 'NULL' : "'" . str_replace('\\', '\\\\', str_replace("'", "''", $p)) . "'";
                    //$prepared .= "'" . addslashes($parameters[$i++]) . "'";
                }
 
                $sqlText = substr($sqlText, $pos + 1);
            }
        }
 
        $MIOLO->Assert($i == count($parameters), "SQL PREPARE: Parâmetros inconsistentes! SQL: $sqlText");
        $this->command = $prepared;
        return $prepared;
    }
    
    public static function deleteTable($tableName, array $where)
    {
        $msql = new MSQL();
        $msql->setTables($tableName);
 
        foreach ( $where as $column => $value )
        {
            $msql->addEqualCondition($column, $value);
        }
        
        return $msql->delete();
    }
    
    public static function insertTable($tableName, array $values)
    {
        $msql = new MSQL();
        $msql->setColumns( array_keys($values) );
        $msql->setParameters( array_values($values) );
        $msql->setTables($tableName);
        
        return $msql->insert();
    }
    
    public static function updateTable($tableName, array $values, array $where)
    {
        $msql = new MSQL();
        $msql->setColumns( array_keys($values) );
        $msql->setParameters( array_values($values) );
        $msql->setTables($tableName);
        
        foreach ( $where as $key => $val )
        {
            $msql->addEqualCondition($key, $val);
        }
        
        return $msql->update();
    }
 
    public function insert($parameters = null)
    {        
        $sqlText = 'INSERT INTO ' . implode($this->tables, ',') . ' ( ' . implode($this->columns, ',') . ' ) VALUES ( ';
 
        for ($i = 0; $i < count($this->columns); $i++)
            $par[] = '?';
 
        $sqlText .= implode($par, ',') . ' )';
        $this->command = $sqlText;
 
        if (isset($parameters))
            $this->setParameters($parameters);
 
        $this->prepare($this->parameters);
        return $this->command;
    }
 
    function insertFrom($sql)
    {
        $sqlText = 'INSERT INTO ' . implode($this->tables, ',') . ' ( ' . implode($this->columns, ',') . ' ) ';
        $sqlText .= $sql;
        $this->command = $sqlText;
        return $this->command;
    }
 
    public function delete($parameters = null)
    {
        global $MIOLO;
        $sqlText = 'DELETE FROM ' . implode($this->tables, ',');
        $MIOLO->Assert($this->where != '', "SQL DELETE: Condição não informada!");
        $sqlText .= ' WHERE ' . $this->where;
        $this->command = $sqlText;
 
        if (isset($parameters))
            $this->SetParameters($parameters);
 
        $this->Prepare($this->parameters);
        return $this->command;
    }
 
    public function update($parameters = null, array $values = null)
    { 
        global $MIOLO;
        $sqlText = 'UPDATE ' . implode($this->tables, ',') . ' SET ';
 
        foreach ($this->columns as $c)
            $par[] = $c . '= ?';
 
        $sqlText .= implode($par, ',');
        $MIOLO->Assert($this->where != '', "SQL UPDATE: Condição não informada!");
        $sqlText .= ' WHERE ' . $this->where;
        $this->command = $sqlText;
 
        if (isset($parameters))
            $this->SetParameters($parameters);
 
        $this->Prepare($this->parameters);
        return $this->command;
    }
 
    function select($parameters = null)
    {
        if ($this->join != NULL)
            $this->getJoin();
        
        //$this->join != null ? die(var_export( $this->join )) : 'batata';
        $sqlText = 'SELECT ' . ($this->distinct ? 'DISTINCT ' : '') . implode($this->columns, ',');
 
        if ($this->tables != '')
        {
            $sqlText .= ' FROM   ' . implode($this->tables, ',') . ' ' . $this->getNewJoinsSQL();
        }
 
        if ($this->where != '')
        {
            $sqlText .= ' WHERE ' . $this->where;
        }
 
        if ($this->groupBy != '')
        {
            $sqlText .= ' GROUP BY ' . implode($this->groupBy, ',');
        }
 
        if ($this->having != '')
        {
            $sqlText .= ' HAVING ' . $this->having;
        }
 
        if ($this->orderBy != '')
        {
            $sqlText .= ' ORDER BY ' . implode($this->orderBy, ',');
        }
        
        if ($this->limit != '')
        {
            $sqlText .= ' LIMIT ' . $this->limit;
        }
        
        if ($this->offsetSQL != '')
        {
            $sqlText .= ' OFFSET ' . $this->offsetSQL;
        }
 
        $this->command = $sqlText;
 
        if (isset($parameters))
            $this->SetParameters($parameters);
 
        $this->Prepare($this->parameters);        
        return $this->command;
    }
    
    public function selectCount()
    {
        $new = clone($this);
        $new instanceof MSQL;
        $new->clearColumns()
                ->clearGroupBy()
                ->clearOrderBy()
                ->clearLimit()
                ->clearOffsetSQL()
                ->setColumns('COUNT(*)');
        
        return $new->select();
    }
 
    function clear()
    {
        $this->columns = '';
        $this->tables = '';
        $this->where = '';
        $this->groupBy = '';
        $this->having = '';
        $this->orderBy = '';
        $this->parameters = null;
        $this->command = '';
    }
 
    public function setParameters()
    {
        $numargs = func_num_args();
 
        if ($numargs == 1)
        {
            if (!is_array($parameters = func_get_arg(0)))
            {
                if ($parameters == null)
                    return;
 
                $parameters = array($parameters);
            }
        }
        else
        {
            $parameters = func_get_args();
        }
 
        foreach ( (array) $parameters as $parameter )
        {
            $this->addParameter($parameter);
        }
        
        return $this;
    }
    
    
 
    public function addParameter($value)
    {
        $this->parameters[] = $value;
    }
 
    function setRange()
    {
        $numargs = func_num_args();
 
        if ($numargs == 1)
        {
            $this->range = func_get_arg(0);
        }
        elseif ($numargs == 2)
        {
            $page = func_get_arg(0);
            $rows = func_get_arg(1);
            $this->range = new QueryRange($page, $rows);
        }
        
        return $this;
    }
 
    function setOffset($offset, $rows)
    {
        if (!$this->range)
        {
            $this->range = new MQueryRange(0,0);
        }
        $this->range->offset = $offset;
        $this->range->rows = $rows;
        
        return $this;
    }
 
    function findStr($target, $source)
    {
        $fim = false;
        $pos = 0;
        
        $split = preg_split('/(\‍(|\‍))/i', $source, -1, PREG_SPLIT_NO_EMPTY | PREG_SPLIT_DELIM_CAPTURE);
        $parenteses = 0;
        foreach ($split as $key=>$s)
        {
            if ( $s == '(' ) //Identifica se há parenteses para que não pesquise em subconsultas
            {
                $parenteses++;
            }
            
            if ($parenteses == 0 ) //significa que está na raiz e não dentro de parenteses
            {
                if ( preg_match('/' . preg_quote($target) . '/', $s) )
                {
                    $pos += mb_strpos($s, $target);
                    
                    $fim = true;
                    break; //quando encontra o target na raiz para o laço e retorna a posição do target
                }
            }
            
            if ( $s == ')' ) //Identifica o fechamento do parenteses para que volte a pesquisar na raiz
            {
                $parenteses--;
            }
            
            $pos += mb_strlen($s); //Mantem a posição atualizada
        }
    
        return ($fim ? $pos : -1);
    }
 
    function parseSqlCommand(&$cmd, $clause, $delimiters)
    {
        if (mb_substr($cmd, 0, mb_strlen($clause)) != $clause)
            return false;
 
        $cmd = mb_substr($cmd, mb_strlen($clause));
        $n = count($delimiters);
        $i = 0;
        $pos = -1;
 
        while (($pos < 0) && ($i < $n))
            $pos = $this->FindStr($delimiters[$i++], $cmd);
 
        if ($pos > 0)
        {
            $r = mb_substr($cmd, 0, $pos);
            $cmd = mb_substr($cmd, $pos);
        }
 
        return $r;
    }
 
    function createFrom($sqltext, $params=array())
    {
        $this->command = $sqltext;
        $this->setParameters($params);
        $sqltext = trim($sqltext) . " #";
        $sqltext = preg_replace("/(?i)select /", "select ", $sqltext);
        $sqltext = preg_replace("/(?i) from /", " from ", $sqltext);
        $sqltext = preg_replace("/(?i) where /", " where ", $sqltext);
        $sqltext = preg_replace("/(?i) order by /", " order by ", $sqltext);
        $sqltext = preg_replace("/(?i) group by /", " group by ", $sqltext);
        $sqltext = preg_replace("/(?i) having /", " having ", $sqltext);
        $this->SetColumns($this->ParseSqlCommand($sqltext, "select", array("from")));
 
        if ($this->FindStr('JOIN', $sqltext) < 0)
        {
            $this->SetTables($this->ParseSqlCommand($sqltext, "from", array("where", "group by", "order by", "#")));
        }
        else
        {
            $this->join = $this->ParseSqlCommand($sqltext, "from", array("where", "group by", "order by", "#"));
        }
 
        $this->SetWhere($this->ParseSqlCommand($sqltext, "where", array("group by", "order by", "#")));
        $this->SetGroupBy($this->ParseSqlCommand($sqltext, "group by", array("having", "order by", "#")));
        $this->SetHaving($this->ParseSqlCommand($sqltext, "having", array("order by", "#")));
        $this->SetOrderBy($this->ParseSqlCommand($sqltext, "order by", array("#")));
        
        return $this;
    }
    
    public function addInnerJoin($table, $cond)
    {
        $join = new MSQLNewJoin();
        $join->setTable($table)->setType(MSQLNewJoin::TYPE_INNER)->setCondition($cond);
        $this->addNewJoin($join);
 
        return $this;
    }
 
    public function addLeftJoin($table, $cond)
    {
        $join = new MSQLNewJoin();
        $join->setTable($table)->setType(MSQLNewJoin::TYPE_LEFT)->setCondition($cond);
        $this->addNewJoin($join);
 
        return $this;
    }
    
    public function getNewJoins()
    {
        return $this->newJoins;
    }
 
    private function setNewJoins(array $newJoins)
    {
        $this->newJoins = $newJoins;
    }
    
    private function addNewJoin(MSQLNewJoin $join)
    {
        $this->newJoins[] = $join;
    }
    
    private function getNewJoinsSQL()
    {
        $out = '';
        
        foreach ( $this->getNewJoins() as $join )
        {
            $join instanceof MSQLNewJoin;
            
            $out .= ' ' . $join->generateSQL();
        }
        
        return $out;
    }
    
    public function convertArrayToIn(array $values = null)
    {
        $out = array();
        
        foreach ( $values as $value )
        {
            if ( $value instanceof MSQLExpr )
            {
                $out[] = $value;
            }
            else
            {
                $out[] = "'" . str_replace("'", "''", $value) . "'";
            }
        }
        
        return '(' . implode(', ', $out) . ')';
    }
}
 
class MSQLExpr
{
    private $value;
    
    public function __construct($value)
    {
        $this->value = $value;
    }
    
    public function getValue()
    {
        return $this->value;
    }
 
    public function setValue($value)
    {
        $this->value = $value;
    }
}
 
class MSQLNewJoin
{
    const TYPE_INNER = 'INNER';
    const TYPE_LEFT = 'LEFT';
    
    private $table;
    
    private $type;
    
    private $condition;
    
    public function getTable()
    {
        return $this->table;
    }
 
    public function setTable($table)
    {
        $this->table = $table;
        
        return $this;
    }
 
    public function getType()
    {
        return $this->type;
    }
 
    public function setType($type)
    {
        $this->type = $type;
        
        return $this;
    }
 
    public function getCondition()
    {
        return $this->condition;
    }
 
    public function setCondition($condition)
    {
        $this->condition = $condition;
        
        return $this;
    }
 
    public function generateSQL()
    {
        return $this->getType() . ' JOIN ' . $this->getTable() . ' ON ' . $this->getCondition();
    }
}
?>