qii/src/Driver/TraitDatabase.php

<?php
namespace Qii\Driver;

trait TraitDatabase
{

    /**
     * 获取数据库中所有的数据表
     * @return array
     */
    public function getAllDatabases()
    {
        $sql = "SHOW DATABASES";
        $rs = $this->setQuery($sql);

        $database = array();
        while ($row = $rs->fetch()) {
            $database[] = $row['Database'];
        }
        return $database;
    }

    /**
     * 数据库中是否包含指定库
     * @param string $database 数据库名
     * @return bool
     */
    public function hasDatabase($database)
    {
        if (!$database) return false;
        $sql = "SHOW DATABASES LIKE '" . $database . "'";
        $rs = $this->setQuery($sql);
        while ($row = $rs->fetch()) {
            $val = array_values($row);
            if (in_array($database, $val)) {
                return true;
            }
        }
        return false;
    }

    /**
     * 获取当前数据库中所有的表
     * @param null|string $database 数据库
     * @return array
     */
    public function getAllTables($database = null)
    {
        if ($database) {
            $this->setQuery('USE ' . $database);
        }
        $sql = "SHOW TABLES";
        $rs = $this->setQuery($sql);
        $tables = array();
        while ($row = $rs->fetch()) {
            if (is_array($row)) {
                foreach ($row AS $val) {
                    $tables[] = $val;
                }
            }
        }
        return $tables;
    }

    /**
     * 获取表的索引
     *
     * @param string $table 表名
     * @param string $database 数据库名称
     * @return array
     */
    public function getTableIndex($table, $database = null) {
        if (!$database) $database = $this->currentDB;
        $sql = 'SHOW INDEX FROM '. $database .'.'. $table;
        $rs = $this->setQuery($sql);
        $index = array();
        //Non_unique 0 是唯一 或主键 (0 + Key_name=(PRIMARY))  1 是索引
        while ($row = $rs->fetch()) {
            if($row['Key_name'] == 'PRIMARY') {
                $index['PRIMARY'][] = $row['Column_name'];
                continue;
            }
            if($row['Non_unique'] == '0') {
                $index['UNIQUE'][$row['Key_name']][] = $row['Column_name'];
                continue;
            }
            $index['INDEX'][$row['Key_name']][] = $row['Column_name'];
        }
        return $index;
    }

    /**
     * 获取表的备注
     *
     * @param string $table 表名
     * @param string $database 数据库名称
     * @return array|mixed
     */
    public function getTableComment($table = '', $database = null) {
        if (!$database) $database = $this->currentDB;
        $sql = "
                    SELECT TABLE_NAME AS table_name, TABLE_COMMENT AS comment
                    FROM INFORMATION_SCHEMA.TABLES 
                    WHERE TABLE_SCHEMA = '{$database}'";
        if($table != '') {
            $sql .= " AND TABLE_NAME = '{$table}'";
        }

        $rs = $this->setQuery($sql);
        $comment = array();
        while ($row = $rs->fetch()) {
            $comment[$row['table_name']] = $row['comment'];
        }

        if($table != '' && isset($comment[$table])) {
            return $comment[$table];
        }
        return $comment;
    }
    /**
     * 获取指定数据表的所有字段
     * @param string $table 表名
     * @param string $database 数据库名
     * @param string $autoIncr 自动增长的序号
     * @return array
     */
    public function getTableInfo($table, $database = null, $autoIncr = null)
    {
        static $tableInfo;
        if(isset($tableInfo[$table])) {
            return $tableInfo[$table];
        }
        if (!$database) $database = $this->currentDB;
        $sql = "SELECT * from information_schema.COLUMNS where table_name = '" . $table . "' and table_schema = '" . $database . "' ORDER BY ORDINAL_POSITION ASC";
        $data = array(
            'table' => $table,
            'fields' => array(),
            'fields_type' => array(),
            'rules' => array(
                'pri' => array(),
                'required' => array()
            )
        );
        $rs = $this->setQuery($sql);
        $maxRange = [
            'bigint' => [-1 * pow(2, 63), pow(2, 63)-1],
            'int' => [-1 * pow(2, 31), pow(2, 31)-1],
            'mediumint' => [-8388608, 8388607],
            'smallint' => [-1 * pow(2, 15), pow(2, 15)-1],
            'tinyint' => [-128, 127],
            'integer' => [-1 * pow(2, 31), pow(2, 31)-1]
        ];
        $data['type'] = [];
        while ($row = $rs->fetch()) {
            $data['fields'][] = $row['COLUMN_NAME'];
            $data['type'][$row['DATA_TYPE']][] = $row['COLUMN_NAME'];
            $fieldsType = array();
            $fieldsType['name'] = $row['COLUMN_NAME'];
            $fieldsType['type'] = strtolower($row['DATA_TYPE']);
            $fieldsType['is_pri'] = false;
            $fieldsType['is_uni'] = false;
            $fieldsType['is_null'] = true;
            $fieldsType['is_number'] = false;
            $fieldsType['is_float'] = false;
            $fieldsType['is_decimal'] = false;
            $fieldsType['is_timestamp'] = false;
            $fieldsType['is_set'] = false;
            $fieldsType['default'] = false;
            $fieldsType['comment'] = '';
            if ($row['EXTRA']) {
                $data['rules']['extra'][$row['EXTRA']][] = $row['COLUMN_NAME'];
                $fieldsType['extra'] = $row['EXTRA'];
            }
            if ($row['COLUMN_KEY'] == 'PRI') {
                $data['rules']['pri'][] = $row['COLUMN_NAME'];
                $data['rules']['required'][] = $row['COLUMN_NAME'];
                $fieldsType['is_pri'] = true;
            } else if($row['COLUMN_KEY'] == 'UNI'){
                //$data['rules']['uni'][] = $row['COLUMN_NAME'];
                $data['rules']['required'][] = $row['COLUMN_NAME'];
                $fieldsType['is_uni'] = true;
            }else if ($row['IS_NULLABLE'] == 'NO') {
                $data['rules']['required'][] = $row['COLUMN_NAME'];
                $fieldsType['is_null'] = false;
            }
            if (in_array($row['DATA_TYPE'], array('varchar', 'char'))) {
                $data['rules']['maxlength'][$row['COLUMN_NAME']] = $row['CHARACTER_MAXIMUM_LENGTH'];
                $fieldsType['maxlength'] = $row['CHARACTER_MAXIMUM_LENGTH'];
            }
            if (in_array($row['DATA_TYPE'], array('mediumtext', 'tinytext', 'text', 'longtext'))) {
                $data['rules']['text'][] = $row['COLUMN_NAME'];
            }
            if (in_array($row['DATA_TYPE'], array('bigint', 'int', 'mediumint', 'smallint', 'tinyint', 'integer'))) {
                preg_match('/[\d]{0,}/', $row['COLUMN_TYPE'], $matches);
                if(!empty($matches)) {
                    $data['rules']['int'][$row['COLUMN_NAME']] = isset($matches[0]) && $matches[0] ? $matches[0] : $maxRange[$row['DATA_TYPE']];
                    $data['rules']['number'][] = $row['COLUMN_NAME'];
                    $fieldsType['is_number'] = true;
                }
            }
            if (in_array($row['DATA_TYPE'], array('float', 'double'))) {
                $data['rules']['float'][$row['COLUMN_NAME']] = $this->getValueFromBrackets($row['COLUMN_TYPE']);
                $fieldsType['is_float'] = true;
            }
            if (in_array($row['DATA_TYPE'], array('decimal'))) {
                $data['rules']['decimal'][$row['COLUMN_NAME']] = $this->getValueFromBrackets($row['COLUMN_TYPE']);
                $fieldsType['is_decimal'] = true;
            }
            if (in_array($row['DATA_TYPE'], array('timestamp', 'datetime'))) {
                $data['rules']['timestamp'][] = $row['COLUMN_NAME'];
                $fieldsType['is_timestamp'] = true;
            }
            if (in_array($row['DATA_TYPE'], array('enum', 'set'))) {
                $data['rules']['sets'][$row['COLUMN_NAME']] = $this->getValueFromBrackets($row['COLUMN_TYPE']);
                $fieldsType['is_set'] = true;
            }
            if (isset($row['COLUMN_DEFAULT'])) {
                $data['rules']['default'][$row['COLUMN_NAME']] = $row['COLUMN_DEFAULT'];
                $fieldsType['default'] = $row['COLUMN_DEFAULT'];
            }
            if(isset($row['COLUMN_COMMENT'])) {
                $data['comment'][$row['COLUMN_NAME']] = $row['COLUMN_COMMENT'];
                $fieldsType['comment'] = $row['COLUMN_COMMENT'];
            }
            $data['fields_type'][$row['COLUMN_NAME']] = $fieldsType;
        }

        $sql = "SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE table_name = '" . $table . "'";
        $rs = $this->setQuery($sql);
        $data['sql'] = $this->getTableSQL($table, $database, $autoIncr);
        preg_match_all("/UNIQUE\sKEY\s`(.*?)`\s\(`(.*?)`\)/", $data['sql'], $matches);
        $uniqKeys = [];
        if(count($matches) == 3) {
            foreach ($matches[2] as $key => $match) {
                //$uniqKeys[$matches[1][$key]] = explode("`,`", $match);
                $arr = explode("`,`", $match);
                if(count($arr) > 0)  $uniqKeys[] = join(',', $arr);
            }
        }
        if(count($uniqKeys) > 0) {
            $data['rules']['uniq'] = $uniqKeys;
        }
        $tableInfo[$table] = $data;
        return $data;
    }

    /**
     * 从括号中获取指定的值
     * @param string $str 需要获取的内容
     * @return array
     */
    public function getValueFromBrackets($str)
    {
        preg_match("/(?:\()(.*)(?:\))/i", $str, $matches);
        if(!$matches) {
            return $str;
        }
        $str = $matches[1];
        $a = explode(",", $str);
        for ($i = 0; $i < count($a); $i++) {
            $this->removeQuote($a[$i]);//从字符串中去除单引号
        }
        return $a;
    }

    /**
     * 去除双引号
     * @param string $str 去除双引号
     */
    public function removeQuote(&$str)
    {
        if (preg_match("/^\'/", $str)) {
            $str = substr($str, 1, strlen($str) - 1);
        }
        if (preg_match("/\'$/", $str)) {
            $str = substr($str, 0, strlen($str) - 1);
        }
        return $str;
    }

    /**
     * 查询数据库中是否有指定的表
     * @param string $tableName 表名
     * @param null|string $database 数据库
     * @return bool
     */
    public function hasTable($tableName, $database = null)
    {
        if ($database) {
            $this->setQuery('USE ' . $database);
        }
        $sql = "SHOW TABLES LIKE '" . $tableName . "'";
        $rs = $this->setQuery($sql);

        $tables = array();
        while ($row = $this->fetch($rs)) {
            if (is_array($row)) {
                foreach ($row AS $val) {
                    if ($val == $tableName) return true;
                }
            }
        }
        return false;
    }

    /**
     * 获取创建表的SQL语句
     * @param string $tableName 表名
     * @param null|string $database 数据库
     * @param int|null $autoIncr 自增长的值，null的话就不使用
     * @return string
     */
    public function getTableSQL($tableName, $database = null, $autoIncr = null)
    {
        if ($database) {
            $this->setQuery('USE ' . $database);
        }
        $row = $this->getRow("SHOW CREATE TABLE `" . $tableName . "`");
        if (!$row) {
            throw new \Exception('数据表不存在', __LINE__);
        }
        $sql = $row['Create Table'];
        if ($autoIncr === null) {
            return $sql;
        }
        return preg_replace("/AUTO_INCREMENT=[\d]{1,}/", "AUTO_INCREMENT=" . intval($autoIncr), $sql);
    }

    /**
     * 通过数据表名称获取规则
     * @param string $table 表名
     * @param string $database 数据库名
     */
    public function buildRulesForTable($table, $database = null)
    {
        if (!$database) $database = $this->currentDB;
        $tableInfo = $this->getTableInfo($table, $database);
        $rules = new \Qii\Base\Rules();
        $rules->addFields($tableInfo['fields']);
        if ($tableInfo['rules']['required']) {
            $rules->addForceValidKey($tableInfo['rules']['required']);
        }
        if (isset($tableInfo['rules']['number'])) {
            foreach ($tableInfo['rules']['number'] as $key => $value) {
                $rules->addRules($value, 'number', true, $value . '字段必须是数字');
            }
        }
        if (isset($tableInfo['rules']['maxlength'])) {
            foreach ($tableInfo['rules']['maxlength'] as $key => $value) {
                $rules->addRules($key, 'maxlength', $value, $key . '字段内容长度不能大于' . $value . '个字符');
            }
        }
        if (isset($tableInfo['rules']['timestamp'])) {
            foreach ($tableInfo['rules']['timestamp'] as $key => $value) {
                $rules->addRules($value, 'datetime', true, $value . '字段必须为日期格式');
            }
        }
        return $rules;
    }
    /**
     * 获取表的名称
     * @param String $table
     * @return String
     */
    public function getTable($table)
    {
        //去掉表名及数据库名的`符号，重新添加，避免重复
        $table = str_replace("`", '', $table);
        list($database, $tableName) = array_pad(explode('.', $table), 2, '');
        if ($tableName) {
            return "`{$database}`.`{$tableName}`";
        }
        return '`'. $table .'`';
    }
}