/usr/local/miolo2/classes/database/msql.class

Go to the documentation of this file.

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

    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;

        $source = $string . ',';
        $tok = '';
        $l = strlen($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--;
        }
    //       $array[$tok] = $tok;
    }

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

    function SetColumns($string, $distinct = false)
    {
        $this->getTokens($string, $this->columns);
        $this->distinct = $distinct;
    }

    function SetTables($string)
    {
        $this->getTokens($string, $this->tables);
    }

    function SetGroupBy($string)
    {
        $this->getTokens($string, $this->groupBy);
    }

    function SetOrderBy($string)
    {
        $this->getTokens($string, $this->orderBy);
    }

    function SetWhere($string)
    {
        $this->where .= (($this->where != '') && ($string != '') ? " and " : "") . $string;
    }

    function SetWhereAnd($string)
    {
        $this->where .= (($this->where != '') && ($string != '') ? " and " : "") . $string;
    }

    function SetWhereOr($string)
    {
        $this->where .= (($this->where != '') && ($string != '') ? " or " : "") . $string;
    }

    function SetHaving($string)
    {
        $this->having .= (($this->having != '') && ($string != '') ? " and " : "") . $string;
    }

    function SetHavingAnd($string)
    {
        $this->having .= (($this->having != '') && ($string != '') ? " and " : "") . $string;
    }

    function SetHavingOr($string)
    {
        $this->having .= (($this->having != '') && ($string != '') ? " or " : "") . $string;
    }

    function SetJoin($table1, $table2, $cond, $type = 'INNER')
    {
        $this->join[] = array
            (
            $table1,
            $table2,
            $cond,
            $type
            );
    }

    function SetLeftJoin($table1, $table2, $cond)
    {
        $this->SetJoin($table1, $table2, $cond, 'LEFT');
    }

    function SetRightJoin($table1, $table2, $cond)
    {
        $this->SetJoin($table1, $table2, $cond, 'RIGHT');
    }

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

                if (substr($parameters[$i], 0, 1) == ':')
                {
                    $prepared .= substr($parameters[$i++], 1);
                }
                else
                {
                    $prepared .= is_null($p=$parameters[$i++]) ? 'NULL' : "'" . 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;
    }

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

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

    function Update($parameters = 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();

        $sqlText = 'SELECT ' . ($this->distinct ? 'DISTINCT ' : '') . implode($this->columns, ',');

        if ($this->tables != '')
        {
            $sqlText .= ' FROM   ' . implode($this->tables, ',');
        }

        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, ',');
        }

        $this->command = $sqlText;

        if (isset($parameters))
            $this->SetParameters($parameters);

        $this->Prepare($this->parameters);
        return $this->command;
    }

    function Clear()
    {
        $this->columns = '';
        $this->tables = '';
        $this->where = '';
        $this->groupBy = '';
        $this->having = '';
        $this->orderBy = '';
        $this->parameters = null;
        $this->command = '';
    }

    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();
        }

        $this->parameters = $parameters;
    }

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

    function setOffset($offset, $rows)
    {
        if (!$this->range)
        {
            $this->range = new MQueryRange(0,0);
        }
        $this->range->offset = $offset;
        $this->range->rows = $rows;
    }

    function FindStr($target, $source)
    {
        $l = strlen($target);
        $lsource = strlen($source);
        $pos = 0;

        while (($pos < $lsource) && (!$fim))
        {
            if ($source[$pos] == "(")
            {
                $p = $this->FindStr(")", substr($source, $pos + 1));

                if ($p > 0)
                    $pos += $p + 3;
            }

            $fim = ($target == substr($source, $pos, $l));

            if (!$fim)
                $pos++;
        }

        return ($fim ? $pos : -1);
    }

    function ParseSqlCommand(&$cmd, $clause, $delimiters)
    {
        if (substr($cmd, 0, strlen($clause)) != $clause)
            return false;

        $cmd = substr($cmd, strlen($clause));
        $n = count($delimiters);
        $i = 0;
        $pos = -1;

        while (($pos < 0) && ($i < $n))
            $pos = $this->FindStr($delimiters[$i++], $cmd);

        if ($pos > 0)
        {
            $r = substr($cmd, 0, $pos);
            $cmd = 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("#")));
    }
}
?>

CopyLeft (L) 2001-2006 - [MIOLO Development Team] SOLIS - Cooperativa de Soluções Livres - Lajeado/RS - Brasil