Класс Sql

class Sql extends \Cms\Site\Base { … }

    var $query; // SELECT | INSERT | UPDATE | DELETE

    var $what; // *

    var $table; // FROM ...

    var $join; // JOIN ...

    var $where; // WHERE ...

    var $group; // GROUP BY ...

    var $having; // HAVING ...

    var $order; // ORDER BY ...

    var $limit; // LIMIT ...

    function select($what = "*", $table = "item", $where = null, $order = null, $limit = null) {
        return $this->clear()
            ->setQuery("SELECT")
            ->setWhat($what)
            ->setTable($table)
            ->setWhere($where)
            ->setOrder($order)
            ->setLimit($limit)
        ;
    }

    function insert($table, $what) {
        return $this->clear()
            ->setQuery("INSERT")
            ->setWhat($what)
        ;
    }

    function update($table, $what, $where = null) {
        return $this
            ->setQuery("UPDATE")
            ->setWhat($what)
            ->setWhere($where)
        ;
    }

    function delete($table, $where) {
        return $this
            ->setQuery("DELETE")
            ->setWhere($where)
        ;
    }

    function replace($table, $what) {
        return $this->clear()
            ->setQuery("REPLACE")
            ->setWhat($what)
        ;
    }

    function clear() {
        $this->query = $this->what = $this->table = $this->join = $this->where = $this->group = $this->having = $this->order = $this->limit = null;
        return $this;
    }

    function setQuery($query) {
        $query = mb_strtoupper($query);
        if (in_array($query, array( "SELECT", "INSERT", "UPDATE", "DELETE", "REPLACE" ))) $this->query = $query;
        else $this->query = null;
        return $this;
    }

    function setWhat($what) {
        $this->what = array();
        $this->addWhat($what);
        return $this;
    }

    function addWhat($field) {
        if (is_string($field)) {
            if (strpos($field, "`") === false) {
                $field = preg_split("~\s*,\s*~", $field, PREG_SPLIT_NO_EMPTY);
            } else {
                $field = array( $field );
            }
        }
        if (is_array($field)) foreach ($field as $key => $field) {
            if (is_int($key)) $this->what[] = $field;
            else $this->what[$key] = $field;
        }
        return $this;
    }

    function setTable($table) {
        $this->table = trim($table, "`");
        return $this;
    }

    function setWhere($where) {
        $this->where = array();
        $this->addWhere($where);
        return $this;
    }

    function addWhere($where) {
        if (is_string($where)) {
            $where = array( $where );
        }
        if (is_array($where)) foreach ($where as $key => $where) {
            if (is_int($key)) $this->where[] = $where;
            else $this->where[$key] = $where;
        }
        return $this;
    }

    function setHaving($having) {
        $this->having = array();
        $this->addHaving($having);
        return $this;
    }

    function addHaving($having) {
        if (is_string($having)) {
            $having = array( $having );
        }
        if (is_array($having)) foreach ($having as $key => $having) {
            if (is_int($key)) $this->having[] = $having;
            else $this->having[$key] = $having;
        }
        return $this;
    }

    function setOrder($order) {
        $this->order = array();
        $this->addOrder($order);
        return $this;
    }

    function addOrder($order) {
        if (is_string($order)) {
            if (strpos($order, "`") === false) {
                $order = preg_split("~\s*,\s*~", $order, PREG_SPLIT_NO_EMPTY);
            } else {
                $order = array( $order );
            }
        }
        if (is_array($order)) foreach ($order as $k => $v) {
            if (is_int($k)) $this->order[$v] = "";
            elseif ($v == "ASC" || $v == "asc") $this->order[$k] = "ASC";
            elseif ($v == "DESC" || $v == "desc") $this->order[$k] = "DESC";
            else $this->order[$v] = $k;
        }
        return $this;
    }

    function setLimit($limit) {
        if (is_array($limit)) {
            $this->limit = $limit;
        } else {
            $this->limit = intval($limit);
        }
        return $this;
    }

    function setGroup($group) {
        $this->group = null;
        $this->addGroup($group);
        return $this;
    }

    function addInfo($info, $param = array()) {
        $alias = $param['alias'] ? $param['alias'] : "`$info`";
        $this->addWhat(($param['number'] ? "0+" : "") . "`info_$info`.`value` AS $alias");
        $this->addJoin("`info` `info_$info`", "`{$this->table}`.`id`=`info_$info`.`id` AND `info_$info`.`key`='$info'", $param);
        $this->addGroup(array( "id" => "id" ));
        return $this;
    }

    function addMenu($menu, $param = array()) {
        $this->addWhat("`menu`.`order` AS `menu_order`");
        $this->addJoin("`menu`", "`{$this->table}`.`id`=`menu`.`id` AND `menu`.`menu`='$menu'", $param);
        $this->addGroup(array( "id" => "id" ));
        return $this;
    }

    function addPath($parent) {
        $Data = $this->Data();
        $this->addJoin("`path`", "`{$this->table}`.`id`=`path`.`id`");
        $this->addWhere("`path`.`parent`=" . $Data->quote($parent));
        $this->addGroup(array( "id" => "id" ));
        return $this;
    }

    function addJoin($table, $on, $param = array()) {
        if (!isset($this->join)) $this->join = array();
        array_unshift($param, "$table ON $on");
        $this->join[] = $param;
        return $this;
    }

    function addGroup($field) {
        if (is_string($field)) {
            if (strpos($field, "`") === false) {
                $field = preg_split("~\s*,\s*~", $field, PREG_SPLIT_NO_EMPTY);
            } else {
                $field = array( $field );
            }
        }
        if (is_array($field)) foreach ($field as $key => $field) {
            if (is_int($key)) $this->group[] = $field;
            else $this->group[$key] = $field;
        }
        return $this;
    }

    function sqlField($field) {
        $prefix = $this->table ? "`{$this->table}`." : "";
        if ($field == "*") return $prefix . $field;
        elseif (preg_match("~^[a-zA-Z][_\\d\\w]*$~", $field)) return "$prefix`$field`";
        else return $field;
    }

    function sqlSelect() {
        $Data = $this->Data();
        $query = array();
        if (is_array($this->what)) {
            $what = array();
            foreach ($this->what as $field) {
                $what[] = $this->sqlField($field);
            }
            if ($what) $query['what'] = "SELECT " . implode(", ", $what);
            else $query['what'] = "SELECT *";
        } else {
            $query['what'] = "SELECT *";
        }
        if ($this->table) {
            $query['table'] = "FROM `{$this->table}`";
        }
        if (is_array($this->join)) {
            $join = array();
            foreach ($this->join as $param) {
                $join[] = ($param['left'] ? "LEFT " : "") . "JOIN {$param[0]}";
            }
            if ($join) $query['join'] = implode("\n", $join);
        }
        if (is_array($this->where)) {
            $where = array();
            foreach ($this->where as $w) {
                if (is_array($w)) {
                    if (!$w) continue;
                    if (isset($w[0])) $w[0] = $this->sqlField($w[0]);
                    if (isset($w[0]) && count($w) > 1 && strpos($w[0], "?") === false) $w[0] = "{$w[0]}=?";
                    $where[] = "(" . $Data->queryParamsArray(array_shift($w), $w) . ")";
                } else {
                    if (!strlen($w)) continue;
                    $where[] = "($w)";
                }
            }
            if ($where) $query['where'] = "WHERE " . implode(" AND ", $where);
        }
        if (is_array($this->group)) {
            $group = array();
            foreach ($this->group as $field) $group[] = $this->sqlField($field);
            if ($group) $query['group'] = "GROUP BY " . implode(", ", $group);
        }
        if (is_array($this->having)) {
            $having = array();
            foreach ($this->having as $w) if (strlen($w)) $having[] = "($w)";
            if ($having) $query['having'] = "HAVING " . implode(" AND ", $having);
        }
        if (is_array($this->order)) {
            $order = array();
            foreach ($this->order as $field => $_asc_desc) {
                $order[] = $this->sqlField($field) . (strlen($_asc_desc) ? " " . $_asc_desc : "");
            }
            if ($order) $query['order'] = "ORDER BY " . implode(", ", $order);
        }
        if (is_array($this->limit)) {
            $query['limit'] = "LIMIT " . intval($this->limit[0]) . ", " . intval($this->limit[1]);
        } elseif ($this->limit) {
            $query['limit'] = "LIMIT " . intval($this->limit);
        }
        return implode("\n", $query);
    }

    function sqlDelete() {
        $Data = $this->Data();
        $query = array();
        $query[''] = "DELETE";
        if ($this->table) {
            $query['table'] = "FROM `{$this->table}`";
        }
        if (is_array($this->where)) {
            $where = array();
            foreach ($this->where as $w) {
                if (is_array($w)) {
                    if (!$w) continue;
                    if (isset($w[0])) $w[0] = $this->sqlField($w[0]);
                    if (isset($w[0]) && count($w) > 1 && strpos($w[0], "?") === false) $w[0] = "{$w[0]}=?";
                    $where[] = "(" . $Data->queryParamsArray(array_shift($w), $w) . ")";
                } else {
                    if (!strlen($w)) continue;
                    $where[] = "($w)";
                }
            }
            if ($where) $query['where'] = "WHERE " . implode(" AND ", $where);
        }
        return implode("\n", $query);
    }

    function __toString() {
        if ($this->query == "SELECT") return $this->sqlSelect();
        if ($this->query == "INSERT") return $this->sqlInsert();
        if ($this->query == "UPDATE") return $this->sqlUpdate();
        if ($this->query == "DELETE") return $this->sqlDelete();
        if ($this->query == "REPLACE") return $this->sqlReplace();
    }

    function execute() {
        return $this->Data()->query(strval($this));
    }

    function dump($data = null) {
        print "<pre>" . $this . "</pre>";
    }

    function parse($sql) {
        $App = $this->App();
        $class = $App->getClass("data_parser");
        $parser = new $class;
        $parser->parse($sql, $this);
        return $this;
    }