tp5源码分析之查询构造

查询构造器，用来在数据库查询中构造SQL语句

$builder->__construct()

创建构造器，初始化构造器使用的数据库连接对象

public function __construct(Connection $connection)
    {
        $this->connection = $connection;
    }

$builder->setQuery()

设置查询构造所在的查询对象

public function setQuery(Query $query)
    {
        $this->query = $query;
    }

SQL语句合成入口

替换掉SQL语句的相关字符串为查询选项信息。

$builder->select()

合成select语句

public function select($options = [])
    {
        $sql = str_replace(
            ["%TABLE%", "%DISTINCT%", "%FIELD%", "%JOIN%", "%WHERE%", "%GROUP%", "%HAVING%", "%ORDER%", "%LIMIT%", "%UNION%", "%LOCK%", "", "%FORCE%"],
            [
                $this->parseTable($options["table"]),
                $this->parseDistinct($options["distinct"]),
                $this->parseField($options["field"]),
                $this->parseJoin($options["join"]),
                $this->parseWhere($options["where"], $options),
                $this->parseGroup($options["group"]),
                $this->parseHaving($options["having"]),
                $this->parseOrder($options["order"]),
                $this->parseLimit($options["limit"]),
                $this->parseUnion($options["union"]),
                $this->parseLock($options["lock"]),
                $this->parseComment($options["comment"]),
                $this->parseForce($options["force"]),
            ], $this->selectSql);
        return $sql;
    }

; select表达式模板

protected $selectSql    = "SELECT%DISTINCT% %FIELD% FROM %TABLE%%FORCE%%JOIN%%WHERE%%GROUP%%HAVING%%ORDER%%LIMIT% %UNION%%LOCK%";

$builder->insert()

合成insert语句

public function insert(array $data, $options = [], $replace = false)
    {
        // 分析并处理数据
        $data = $this->parseData($data, $options);
        if (empty($data)) {
            return 0;
        }
        $fields = array_keys($data);
        $values = array_values($data);

        $sql = str_replace(
            ["%INSERT%", "%TABLE%", "%FIELD%", "%DATA%", ""],
            [
                $replace ? "REPLACE" : "INSERT",
                $this->parseTable($options["table"]),
                implode(" , ", $fields),
                implode(" , ", $values),
                $this->parseComment($options["comment"]),
            ], $this->insertSql);

        return $sql;
    }

; insert表达式模板

protected $insertSql    = "%INSERT% INTO %TABLE% (%FIELD%) VALUES (%DATA%) ";

$builder->inssertAll()

合成insertAll语句

public function insertAll($dataSet, $options)
    {
        // 获取合法的字段
        if ("*" == $options["field"]) {
            $fields = array_keys($this->query->getFieldsType($options));
        } else {
            $fields = $options["field"];
        }

        foreach ($dataSet as &$data) {
            foreach ($data as $key => $val) {
                if (!in_array($key, $fields, true)) {
                    if ($options["strict"]) {
                        throw new Exception("fields not exists:[" . $key . "]");
                    }
                    unset($data[$key]);
                } elseif (is_scalar($val)) {
                    $data[$key] = $this->parseValue($val, $key);
                } else {
                    // 过滤掉非标量数据
                    unset($data[$key]);
                }
            }
            $value    = array_values($data);
            $values[] = "SELECT " . implode(",", $value);
        }
        $fields = array_map([$this, "parseKey"], array_keys(reset($dataSet)));
        $sql    = str_replace(
            ["%TABLE%", "%FIELD%", "%DATA%", ""],
            [
                $this->parseTable($options["table"]),
                implode(" , ", $fields),
                implode(" UNION ALL ", $values),
                $this->parseComment($options["comment"]),
            ], $this->insertAllSql);

        return $sql;
    }

$builder->selectInsert()

合成Selectinsert语句

public function selectInsert($fields, $table, $options)
    {
        if (is_string($fields)) {
            $fields = explode(",", $fields);
        }

        $fields = array_map([$this, "parseKey"], $fields);
        $sql    = "INSERT INTO " . $this->parseTable($table) . " (" . implode(",", $fields) . ") " . $this->select($options);
        return $sql;
    }

; selectInsert表达式模板

protected $insertAllSql = "INSERT INTO %TABLE% (%FIELD%) %DATA% ";

$builder->update()

合成Update语句

public function update($data, $options)
    {
        $table = $this->parseTable($options["table"]);
        $data  = $this->parseData($data, $options);
        if (empty($data)) {
            return "";
        }
        foreach ($data as $key => $val) {
            $set[] = $key . "=" . $val;
        }

        $sql = str_replace(
            ["%TABLE%", "%SET%", "%JOIN%", "%WHERE%", "%ORDER%", "%LIMIT%", "%LOCK%", ""],
            [
                $this->parseTable($options["table"]),
                implode(",", $set),
                $this->parseJoin($options["join"]),
                $this->parseWhere($options["where"], $options),
                $this->parseOrder($options["order"]),
                $this->parseLimit($options["limit"]),
                $this->parseLock($options["lock"]),
                $this->parseComment($options["comment"]),
            ], $this->updateSql);

        return $sql;
    }

; update表达式模板

protected $updateSql    = "UPDATE %TABLE% SET %SET% %JOIN% %WHERE% %ORDER%%LIMIT% %LOCK%";

$builder->delete()

合成Delete语句

public function delete($options)
    {
        $sql = str_replace(
            ["%TABLE%", "%USING%", "%JOIN%", "%WHERE%", "%ORDER%", "%LIMIT%", "%LOCK%", ""],
            [
                $this->parseTable($options["table"]),
                !empty($options["using"]) ? " USING " . $this->parseTable($options["using"]) . " " : "",
                $this->parseJoin($options["join"]),
                $this->parseWhere($options["where"], $options),
                $this->parseOrder($options["order"]),
                $this->parseLimit($options["limit"]),
                $this->parseLock($options["lock"]),
                $this->parseComment($options["comment"]),
            ], $this->deleteSql);

        return $sql;
    }

; delete表达式模板

protected $deleteSql    = "DELETE FROM %TABLE% %USING% %JOIN% %WHERE% %ORDER%%LIMIT% %LOCK%";

$builder->parseTable()

解析选项的 table选项

protected function parseTable($tables)
    {
        if (is_array($tables)) {
            // 支持别名定义
            foreach ($tables as $table => $alias) {
                $array[] = !is_numeric($table) ?
                $this->parseKey($table) . " " . $this->parseKey($alias) :
                $this->parseKey($alias);
            }
            $tables = $array;
        } elseif (is_string($tables)) {
            $tables = $this->parseSqlTable($tables);
            $tables = array_map([$this, "parseKey"], explode(",", $tables));
        }
        return implode(",", $tables);
    }

替换__TABLE_NAME__

protected function parseSqlTable($sql)
    {
        return $this->query->parseSqlTable($sql);
    }

$builder->parseDistinct()

protected function parseDistinct($distinct)
    {
        return !empty($distinct) ? " DISTINCT " : "";
    }

$builder->parseField()

解析 Field 选项

protected function parseField($fields)
    {
        if ("*" == $fields || empty($fields)) {
            $fieldsStr = "*";
        } elseif (is_array($fields)) {
            // 支持 "field1"=>"field2" 这样的字段别名定义
            $array = [];
            foreach ($fields as $key => $field) {
                if (!is_numeric($key)) {
                    $array[] = $this->parseKey($key) . " AS " . $this->parseKey($field);
                } else {
                    $array[] = $this->parseKey($field);
                }
            }
            $fieldsStr = implode(",", $array);
        }
        return $fieldsStr;
    }

$builder->parseJoin()

解析Join选项

protected function parseJoin($join)
    {
        $joinStr = "";
        if (!empty($join)) {
            $joinStr = " " . implode(" ", $join) . " ";
        }
        return $joinStr;
    }

$builder->parseWhere()

解析where选项入口

protected function parseWhere($where, $options)
    {
        $whereStr = $this->buildWhere($where, $options);
        return empty($whereStr) ? "" : " WHERE " . $whereStr;
    }

构造SQL查询条件

public function buildWhere($where, $options)
    {
        if (empty($where)) {
            $where = [];
        }

        if ($where instanceof Query) {
            return $this->buildWhere($where->getOptions("where"), $options);
        }

        $whereStr = "";
        $binds    = $this->query->getFieldsBind($options);
        foreach ($where as $key => $val) {
            $str = [];
            foreach ($val as $field => $value) {
                if ($value instanceof Closure) {
                    // 使用闭包查询
                    $query = new Query($this->connection);
                    call_user_func_array($value, [ & $query]);
                    $str[] = " " . $key . " ( " . $this->buildWhere($query->getOptions("where"), $options) . " )";
                } elseif (strpos($field, "|")) {
                    // 不同字段使用相同查询条件（OR）
                    $array = explode("|", $field);
                    $item  = [];
                    foreach ($array as $k) {
                        $item[] = $this->parseWhereItem($k, $value, "", $options, $binds);
                    }
                    $str[] = " " . $key . " ( " . implode(" OR ", $item) . " )";
                } elseif (strpos($field, "&")) {
                    // 不同字段使用相同查询条件（AND）
                    $array = explode("&", $field);
                    $item  = [];
                    foreach ($array as $k) {
                        $item[] = $this->parseWhereItem($k, $value, "", $options, $binds);
                    }
                    $str[] = " " . $key . " ( " . implode(" AND ", $item) . " )";
                } else {
                    // 对字段使用表达式查询
                    $field = is_string($field) ? $field : "";
                    $str[] = " " . $key . " " . $this->parseWhereItem($field, $value, $key, $options, $binds);
                }
            }

            $whereStr .= empty($whereStr) ? substr(implode(" ", $str), strlen($key) + 1) : implode(" ", $str);
        }
        return $whereStr;
    }

where子单元分析

protected function parseWhereItem($field, $val, $rule = "", $options = [], $binds = [], $bindName = null)
    {
        // 字段分析
        $key = $field ? $this->parseKey($field) : "";

        // 查询规则和条件
        if (!is_array($val)) {
            $val = ["=", $val];
        }
        list($exp, $value) = $val;

        // 对一个字段使用多个查询条件
        if (is_array($exp)) {
            $item = array_pop($val);
            // 传入 or 或者 and
            if (is_string($item) && in_array($item, ["AND", "and", "OR", "or"])) {
                $rule = $item;
            } else {
                array_push($val, $item);
            }
            foreach ($val as $k => $item) {
                $bindName = "where_" . str_replace(".", "_", $field) . "_" . $k;
                $str[]    = $this->parseWhereItem($field, $item, $rule, $options, $binds, $bindName);
            }
            return "( " . implode(" " . $rule . " ", $str) . " )";
        }

        // 检测操作符
        if (!in_array($exp, $this->exp)) {
            $exp = strtolower($exp);
            if (isset($this->exp[$exp])) {
                $exp = $this->exp[$exp];
            } else {
                throw new Exception("where express error:" . $exp);
            }
        }
        $bindName = $bindName ?: "where_" . str_replace(".", "_", $field);
        $bindType = isset($binds[$field]) ? $binds[$field] : PDO::PARAM_STR;
        if (is_scalar($value) && array_key_exists($field, $binds) && !in_array($exp, ["EXP", "NOT NULL", "NULL", "IN", "NOT IN", "BETWEEN", "NOT BETWEEN"]) && strpos($exp, "TIME") === false) {
            if (strpos($value, ":") !== 0 || !$this->query->isBind(substr($value, 1))) {
                if ($this->query->isBind($bindName)) {
                    $bindName .= "_" . uniqid();
                }
                $this->query->bind($bindName, $value, $bindType);
                $value = ":" . $bindName;
            }
        }

        $whereStr = "";
        if (in_array($exp, ["=", "<>", ">", ">=", "<", "<=", "LIKE", "NOT LIKE"])) {
            // 比较运算 及 模糊匹配
            $whereStr .= $key . " " . $exp . " " . $this->parseValue($value, $field);
        } elseif ("EXP" == $exp) {
            // 表达式查询
            $whereStr .= "( " . $key . " " . $value . " )";
        } elseif (in_array($exp, ["NOT NULL", "NULL"])) {
            // NULL 查询
            $whereStr .= $key . " IS " . $exp;
        } elseif (in_array($exp, ["NOT IN", "IN"])) {
            // IN 查询
            if ($value instanceof Closure) {
                $whereStr .= $key . " " . $exp . " " . $this->parseClosure($value);
            } else {
                $value = is_array($value) ? $value : explode(",", $value);
                if (array_key_exists($field, $binds)) {
                    $bind  = [];
                    $array = [];
                    foreach ($value as $k => $v) {
                        $bind[$bindName . "_in_" . $k] = [$v, $bindType];
                        $array[]                       = ":" . $bindName . "_in_" . $k;
                    }
                    $this->query->bind($bind);
                    $zone = implode(",", $array);
                } else {
                    $zone = implode(",", $this->parseValue($value, $field));
                }
                $whereStr .= $key . " " . $exp . " (" . $zone . ")";
            }
        } elseif (in_array($exp, ["NOT BETWEEN", "BETWEEN"])) {
            // BETWEEN 查询
            $data = is_array($value) ? $value : explode(",", $value);
            if (array_key_exists($field, $binds)) {
                $bind = [
                    $bindName . "_between_1" => [$data[0], $bindType],
                    $bindName . "_between_2" => [$data[1], $bindType],
                ];
                $this->query->bind($bind);
                $between = ":" . $bindName . "_between_1" . " AND :" . $bindName . "_between_2";
            } else {
                $between = $this->parseValue($data[0], $field) . " AND " . $this->parseValue($data[1], $field);
            }
            $whereStr .= $key . " " . $exp . " " . $between;
        } elseif (in_array($exp, ["NOT EXISTS", "EXISTS"])) {
            // EXISTS 查询
            if ($value instanceof Closure) {
                $whereStr .= $exp . " " . $this->parseClosure($value);
            } else {
                $whereStr .= $exp . " (" . $value . ")";
            }
        } elseif (in_array($exp, ["< TIME", "> TIME", "<= TIME", ">= TIME"])) {
            $whereStr .= $key . " " . substr($exp, 0, 2) . " " . $this->parseDateTime($value, $field, $options, $bindName, $bindType);
        } elseif (in_array($exp, ["BETWEEN TIME", "NOT BETWEEN TIME"])) {
            if (is_string($value)) {
                $value = explode(",", $value);
            }

            $whereStr .= $key . " " . substr($exp, 0, -4) . $this->parseDateTime($value[0], $field, $options, $bindName . "_between_1", $bindType) . " AND " . $this->parseDateTime($value[1], $field, $options, $bindName . "_between_2", $bindType);
        }
        return $whereStr;
    }

$builder->parseGroup()

解析Group选项

protected function parseGroup($group)
    {
        return !empty($group) ? " GROUP BY " . $group : "";
    }

$builder->parseHaving()

解析Having选项

protected function parseHaving($having)
    {
        return !empty($having) ? " HAVING " . $having : "";
    }

$builder->parseOrder()

解析Order选项

protected function parseOrder($order)
    {
        if (is_array($order)) {
            $array = [];
            foreach ($order as $key => $val) {
                if (is_numeric($key)) {
                    if (false === strpos($val, "(")) {
                        $array[] = $this->parseKey($val);
                    } elseif ("[rand]" == $val) {
                        $array[] = $this->parseRand();
                    }
                } else {
                    $sort    = in_array(strtolower(trim($val)), ["asc", "desc"]) ? " " . $val : "";
                    $array[] = $this->parseKey($key) . " " . $sort;
                }
            }
            $order = implode(",", $array);
        }
        return !empty($order) ? " ORDER BY " . $order : "";
    }

$builder->parseLimit()

解析Limit选项

protected function parseLimit($limit)
    {
        return (!empty($limit) && false === strpos($limit, "(")) ? " LIMIT " . $limit . " " : "";
    }

$builder->parseUnion()

解析Union选项

protected function parseUnion($union)
    {
        if (empty($union)) {
            return "";
        }
        $type = $union["type"];
        unset($union["type"]);
        foreach ($union as $u) {
            if ($u instanceof Closure) {
                $sql[] = $type . " " . $this->parseClosure($u, false);
            } elseif (is_string($u)) {
                $sql[] = $type . " " . $this->parseSqlTable($u);
            }
        }
        return implode(" ", $sql);
    }

$builder->parseLock()

解析Lock选项

protected function parseLock($lock = false)
    {
        return $lock ? " FOR UPDATE " : "";
    }

$builder->parseComment()

解析Comment选项

protected function parseComment($comment)
    {
        return !empty($comment) ? " /* " . $comment . " */" : "";
    }

$builder->parseForce()

解析index选项

protected function parseForce($index)
    {
        if (empty($index)) {
            return "";
        }

        if (is_array($index)) {
            $index = join(",", $index);
        }

        return sprintf(" FORCE INDEX ( %s ) ", $index);
    }

$builder->parseData()

protected function parseData($data, $options)
    {
        if (empty($data)) {
            return [];
        }

        // 获取绑定信息
        $bind = $this->query->getFieldsBind($options);
        if ("*" == $options["field"]) {
            $fields = array_keys($bind);
        } else {
            $fields = $options["field"];
        }

        $result = [];
        foreach ($data as $key => $val) {
            $item = $this->parseKey($key);
            if (!in_array($key, $fields, true)) {
                if ($options["strict"]) {
                    throw new Exception("fields not exists:[" . $key . "]");
                }
            } elseif (isset($val[0]) && "exp" == $val[0]) {
                $result[$item] = $val[1];
            } elseif (is_null($val)) {
                $result[$item] = "NULL";
            } elseif (is_scalar($val)) {
                // 过滤非标量数据
                if ($this->query->isBind(substr($val, 1))) {
                    $result[$item] = $val;
                } else {
                    $this->query->bind($key, $val, isset($bind[$key]) ? $bind[$key] : PDO::PARAM_STR);
                    $result[$item] = ":" . $key;
                }
            }
        }
        return $result;
    }

$builder->parseKey()

protected function parseKey($key)
    {
        return $key;
    }

$builder->parseValue()

protected function parseValue($value, $field = "")
    {
        if (is_string($value)) {
            $value = strpos($value, ":") === 0 && $this->query->isBind(substr($value, 1)) ? $value : $this->connection->quote($value);
        } elseif (is_array($value)) {
            $value = array_map([$this, "parseValue"], $value);
        } elseif (is_bool($value)) {
            $value = $value ? "1" : "0";
        } elseif (is_null($value)) {
            $value = "null";
        }
        return $value;
    }

$builder->parseClosure()

protected function parseClosure($call, $show = true)
    {
        $query = new Query($this->connection);
        call_user_func_array($call, [ & $query]);
        return $query->buildSql($show);
    }

$builder->parseDateTime()

protected function parseDateTime($value, $key, $options = [], $bindName = null, $bindType = null)
    {
        // 获取时间字段类型
        $type = $this->query->getFieldsType($options);
        if (isset($type[$key])) {
            $info = $type[$key];
        }
        if (isset($info)) {
            $value = strtotime($value) ?: $value;
            if (preg_match("/(datetime|timestamp)/is", $info)) {
                // 日期及时间戳类型
                $value = date("Y-m-d H:i:s", $value);
            } elseif (preg_match("/(date)/is", $info)) {
                // 日期及时间戳类型
                $value = date("Y-m-d", $value);
            }
        }
        $bindName = $bindName ?: $key;
        $this->query->bind($bindName, $value, $bindType);
        return ":" . $bindName;
    }