TableConfig.php

<?php
namespace App\Http\Controllers;

use Illuminate\Http\Request;

use App\Http\Requests;
use App\Http\Controllers\Controller;
use Config;
use DB;
use Exception;
use Log;
use Validator;

define('TYPE_INCREMENT', 10);   // 自增量
define('TYPE_INT',       11);   // 整数
define('TYPE_UINT',      12);   // 无符号整数
define('TYPE_FLOAT',     13);   // 浮点数
define('TYPE_BOOL',      14);   // bool
define('TYPE_DATE',      20);   // 日期
define('TYPE_DATETIME',  21);   // 日期时间
define('TYPE_TIMESTAMP', 22);   // 时间戳
define('TYPE_MTIME',     23);   // 修改时间
define('TYPE_CTIME',     24);   // 创建时间
define('TYPE_STRING',    30);   // 字符串
define('TYPE_JSON',      31);   // Json
define('TYPE_TEXT',      32);   // 长文本
define('TYPE_USER',      40);   // 用户名
define('TYPE_MUSER',     41);   // 修改者
define('TYPE_CUSER',     42);   // 创建者
define('TYPE_URL',       43);   // URL
define('TYPE_IMAGE',     44);   // 图片地址; 允许上传
define('TYPE_EMAIL',     45);   // 邮箱
define('TYPE_IP',        46);   // IP地址
define('TYPE_COMPLATE',  50);   // 自动补全

class TableConfig
{
    // 转换数据库的set，enum类型的定义
    private function converEnums($v)
    {
        if (is_array($v)) {
            $str = "";
            foreach ($v as $item)
                $str .= ',' . $item;
            return substr($str, 1);
        } else {
            return $v;
        }
    }

    private function enumValues($enums)
    {
        preg_match_all("/'(([^']|'')+)',?/", substr($enums, 5, -1), $match);
        foreach ($match[1] as &$elm)
            $elm = str_replace("''", "'", $elm);

        return $match[1];
    }


    // 检查输入
    private function checkInputFormt($fielddef, $v, $raw, $limit=null)
    {
        if (($v === '' || $v === null) && !$fielddef->need)
            return true;

        switch ($fielddef->type) {
        case TYPE_INCREMENT:    // 自增变量
        case TYPE_INT:          // 整数
            if (filter_var($v, FILTER_VALIDATE_INT) === false)
                return "错误的的整数格式";
            return true;
        case TYPE_UINT:         // 无符号整数
            if (filter_var($v, FILTER_VALIDATE_INT) === false)
                return "错误的的整数格式";
            if ($v < 0)
                return "输入值是负数";
            break;
        case TYPE_FLOAT:        // 浮点数
            if (filter_var($v, FILTER_VALIDATE_FLOAT) === false)
                return "错误的的整数格式";
            break;
        case TYPE_DATE:         // 日期
            if (date('Y-m-d', strtotime($v)) != $v)
                return "错误的的日期格式";
            break;
        case TYPE_DATETIME:     // 日期时间
            if (date('Y-m-d H:i:s', strtotime($v)) != $v)
                return "错误的的日期格式";
            break;
        case TYPE_TIMESTAMP:    // 时间戳
            if (filter_var($v, FILTER_VALIDATE_INT) === false)
                return "错误的的时间戳格式";
            break;
        case TYPE_USER:
            if ($v != 'ALL' && filter_var($v, FILTER_VALIDATE_EMAIL) === false)
                return "错误的用户名";
            break;
        case TYPE_EMAIL:
            if (filter_var($v, FILTER_VALIDATE_EMAIL) === false)
                return "错误的邮箱";
            break;
        case TYPE_URL:
            if (filter_var($v, FILTER_VALIDATE_URL) === false)
                return "错误的邮箱";
            break;
        case TYPE_IMAGE:
            if (filter_var($v, FILTER_VALIDATE_URL) === false)
                return "错误的邮箱";
            break;
        case TYPE_IP:
            if (filter_var($v, FILTER_VALIDATE_IP) === false)
                return "错误的邮箱";
            break;
        }

        switch ($fielddef->type) {
        case TYPE_INT: case TYPE_UINT: case TYPE_FLOAT: case TYPE_TIMESTAMP: case TYPE_INCREMENT:
            if (isset($fielddef->max) && $v > $fielddef->max)
                return "超过了最大值" . $fielddef->max;
            if (isset($fielddef->min) && $v < $fielddef->min)
                return "小于最小值" . $fielddef->min;
            break;
        default:
            if (isset($fielddef->maxlen) && $fielddef->maxlen > 0 && strlen($v) > $fielddef->maxlen)
                return "超过了最大长度" . $fielddef->maxlen;
            if (isset($raw["maxlen"]) && strlen($v) > $raw["maxlen"])
                return "超过了系统存储大小" . $raw["maxlen"];
            if (isset($fielddef->minlen) && $fielddef->minlen > 0 && strlen($v) < $fielddef->minlen)
                return "小于最大长度" . $fielddef->minlen;
            break;
        }

        if ($raw["type"] == 'set' || $raw["type"] == 'enum') {
            if (array_search($v, $raw["enums"]) === false)
                return "字段取值只能为(" . $this->converEnums($raw["enums"]) . ")";
        }

        if ($limit) {
            $name = $fielddef->name;
            $validator = Validator::make([$name => $v], [$name => $limit]);
            if ($validator->fails())
                return json_encode($validator->errors()->all());
        }

        return true;
    }

    private function converType($elm)
    {
        $datatype = [
            'name'          => $elm->COLUMN_NAME,
            'default_val'   => $elm->COLUMN_DEFAULT,
            'extra'         => $elm->EXTRA,
            'comment'       => $elm->COLUMN_COMMENT,
            'key'           => $elm->COLUMN_KEY
        ];

        switch ($elm->DATA_TYPE) {
        case 'bit':
            $datatype['type'] = 'int';
            $bits = 1;
            if (preg_match('/bit\(([0-9]+)\)/', $elm->COLUMN_TYPE, $matchs) == 1)
                $bits = (int)$matchs[2];

            $datatype['min'] = 0;
            if ($bits < 64)
                $datatype['max'] = 1 << ($bits - 1);
            else
                $datatype['max'] = "18446744073709551615";
            break;
        case 'tinyint':
            $datatype['type'] = 'int';
            if (strstr($elm->COLUMN_TYPE, "unsigned")) {
                $datatype['min'] = 0;
                $datatype['max'] = 255;
            } else {
                $datatype['min'] = -128;
                $datatype['max'] = 127;
            }
            break;
        case 'smallint':
            $datatype['type'] = 'int';
            if (strstr($elm->COLUMN_TYPE, "unsigned")) {
                $datatype['min'] = 0;
                $datatype['max'] = 65535;
            } else {
                $datatype['min'] = -32768;
                $datatype['max'] = 32767;
            }
            break;
        case 'mediumint':
            $datatype['type'] = 'int';
            if (strstr($elm->COLUMN_TYPE, "unsigned")) {
                $datatype['min'] = 0;
                $datatype['max'] = 16777215;
            } else {
                $datatype['min'] = -8388608;
                $datatype['max'] = 8388607;
            }
            break;
        case 'int':
            $datatype['type'] = 'int';
            if (strstr($elm->COLUMN_TYPE, "unsigned")) {
                $datatype['min'] = 0;
                $datatype['max'] = 4294967295;
            } else {
                $datatype['min'] = -2147483648;
                $datatype['max'] = 2147483647;
            }
            break;
        case 'bigint':
            $datatype['type'] = 'int';
            if (strstr($elm->COLUMN_TYPE, "unsigned")) {
                $datatype['min'] = 0;
                $datatype['max'] = "18446744073709551615";
            } else {
                $datatype['min'] = "-9223372036854775808";
                $datatype['max'] = "9223372036854775807";
            }
            break;
        case 'float':
            $datatype['type'] = 'float';
            if (strstr($elm->COLUMN_TYPE, "unsigned")) {
                $datatype['min'] = 0;
                $datatype['max'] = 3.402823466E+38;
            } else {
                $datatype['min'] = -3.402823466E+38;
                $datatype['max'] = 3.402823466E+38;
            }
            break;
        case 'decimal':
        case 'double':
            $datatype['type'] = 'float';
            if (strstr($elm->COLUMN_TYPE, "unsigned")) {
                $datatype['min'] = 0;
                $datatype['max'] = 1.7976931348623157e308;
            } else {
                $datatype['min'] = -1.7976931348623157e308;
                $datatype['max'] = 1.7976931348623157e308;
            }
            break;
        case 'date':
            $datatype['type'] = 'date';
            break;
        case 'datetime':
            $datatype['type'] = 'datetime';
            break;
        case 'timestamp':
            $datatype['type'] = 'datetime';
            break;
        case 'year':
            $datatype['type'] = 'year';
            break;
        case 'time':
            $datatype['type'] = 'time';
            break;
        case 'varchar':
        case 'binary':
        case 'tinyblob':
        case 'tinytext':
        case 'blob':
        case 'text':
        case 'mediumblob':
        case 'mediumtext':
        case 'longblob':
        case 'longtext':
        default:
            $datatype['type'] = 'string';
            $datatype['maxlen'] = $elm->CHARACTER_MAXIMUM_LENGTH;
            break;
        case 'enum':
            $datatype['type'] = 'enum';
            $datatypes['enums'] = $this->enumValues($elm->COLUMN_TYPE);
            $datatype['maxlen'] = $elm->CHARACTER_MAXIMUM_LENGTH;
            break;
        case 'set':
            $datatype['type'] = 'set';
            $datatypes['enums'] = $this->enumValues($elm->COLUMN_TYPE);
            $datatype['maxlen'] = $elm->CHARACTER_MAXIMUM_LENGTH;
            break;
        };

        return $datatype;
    }

    public function schemaByTable($key, $dbname, $table)
    {
        $sql = "SELECT * FROM information_schema.columns WHERE TABLE_SCHEMA=? AND TABLE_NAME=?";

        $fields = [];
        foreach (DB::connection($key)->select($sql, [$dbname, $table]) as $elm)
            $fields[$elm->COLUMN_NAME] = $this->converType($elm);

        if (count($fields) == 0) {
            $msg = "找不到数据表 " . $dbname . '.' . $table;
            throw new Exception($msg, 404);
        }

        return $fields;
    }

    // 获取表结构
    public function getSchema($host, $port, $user, $passwd, $dbname, $table)
    {
        $confname = $host . ':' . $port;

        $conn = [
            'driver'    => 'mysql',
            'host'      => $host,
            'port'      => $port,
            'database'  => 'information_schema',
            'username'  => $user,
            'password'  => $passwd,
            'charset'   => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'prefix'    => '',
        ];

        Config::set('database.connections.' . $confname, $conn);

        return $this->schemaByTable($confname, $dbname, $table);
    }

    public function CheckTableconfig(&$input, $key, $config, $table_id=null)
    {
        $err = [];
        $raw_fileds = null;

        if (!isset($input->host) || !isset($input->port) || !isset($input->username) ||
            !isset($input->passwd) || !isset($input->dbname) || !isset($input->tablename)) {
            $err->host = "数据库配置错误";
        } else {
            try {
                $ret = DB::connection($key)->table($config->dbname . '.' . $config->tablename)
                    ->where('host', $input->host)
                    ->where('port', $input->port)
                    ->where('username', $input->username)
                    ->where('passwd', $input->passwd)
                    ->where('dbname', $input->dbname)
                    ->where('tablename', $input->tablename)->get();

                if ($ret && count($ret) > 0) {
                    // if ($table_id === null || $ret[0]->table_id != $table_id)
                        //return ["errcode" => 400, "errmsg" => "同配置id为" . $ret[0]->table_id . "的数据库配置相同, 不要重复创建配置" ];
                }

                $raw_fileds = $this->getSchema($input->host, $input->port, $input->username,
                                               $input->passwd, $input->dbname, $input->tablename);
            } catch (Exception $e) {
            }
        }

        if (!$raw_fileds) {
            $err["host"] = "数据库配置错误";
            return ["errcode" => 400, "errmsg" => "配置错误", "error" => $err];
        }

        // 检查keyfield;
        foreach ($raw_fileds as $elm) {
            if ($elm["extra"] != 'auto_increment')
                continue;
            if (!isset($input->keyfield))
                break;
            if (!in_array($elm["name"], $input->keyfield))
                break;
            if (count($input->keyfield) > 1) {
                $err["keyfield"] = "自增字段不是唯一主键字段";
                break;
            }
        }

        $fnames = ["keyfield", "sortfield", "groupfield"];
        foreach ($fnames as $fname) {
            if (!isset($input->$fname))
                continue;

            foreach ($input->$fname as $field) {
                if (!isset($raw_fileds[$field]) || !isset($input->fields->$field)) {
                    $err[$field] = "包含不存在的字段名";
                    continue 2;
                }
            }
        }

        if (isset($input->keyfield) && count($input->keyfield) > 0) {
            foreach ($input->keyfield as $kfield) {
                if (isset($input->fields->$kfield) && !$input->fields->$kfield->need) {
                    $err["fields"] = "主键字段" . $kfield . "没有设置为必填";
                    break;
                }
            }
        }

        if (!isset($input->fields)) {
            $err["fields"] = "配置为空";
            return ["errcode" => 400, "errmsg" => "配置错误", "error" => $err];
        }

        foreach ($input->fields as $name => &$field) {
            if (!isset($field->name) || $name != $field->name) {
                $err["fields"] = "字段". $name . "配置错误";
                break;
            }

            if (!isset($field->type)) {
                $err["fields"] = "字段". $name . "类型配置错误";
                break;
            }

            if (!isset($raw_fileds[$name])) {
                $err["fields"] = "字段". $name . "不存在";
                break;
            }

            $raw = $raw_fileds[$name];
            if ($raw["extra"] == "auto_increment" && $field->type != TYPE_INCREMENT) {
                $err["fields"] = "字段". $name . "数据库类型为自增变量，配置类型也需要为自增变量";
                break;
            }

            // 检查字典
            if (isset($field->dict)) {
                foreach ($field->dict as $dict) {
                    if (!isset($dict->key)) {
                        $err["fileds"] = "字段". $name . "的字典配置错误";
                        break 2;
                    }
                }
            }

            $comint = true;
            if ($raw["type"] == 'enum' || $raw["type"] == 'set') {
                if (!isset($field->dict)) {
                    foreach ($raw["enums"] as $enu)
                        $field->dict[] = ["key"=> $enu, "name"=> $enu];
                } else {
                    foreach ($field->dict as $dict) {
                        if (!in_array($dict->key, $raw["enums"])) {
                            $err["fileds"] = "字段". $name . "的字典配置和数据库定义不匹配";
                            break 2;
                        }
                    }
                }

                foreach ($raw["enums"] as $enu) {
                    if (!preg_match('/^[0-9]\+$/', $enu)) {
                        $comint = false;
                        break;
                    }
                }
            }

            // 检查类型是否匹配
            if (isset($field->islist) && $field->islist && ($raw["type"] != 'string' && $raw["type"] != 'set')) {
                $err["fileds"] = "字段". $name . "错误的设置为列表";
                break;
            }

            switch ($field->type) {
            case TYPE_INCREMENT:    // 自增
            case TYPE_INT:          // 整数
            case TYPE_BOOL:         // bool
            case TYPE_UINT:         // 无符号整数
                if ($raw["type"] != 'string' && $raw["type"] != 'int' && !(($raw["type"] == 'enum' || $raw["type"] == 'set') && $comint))
                    $err["fileds"] = "字段". $name . "类型错误, 和数据库的字段类型不匹配";
                break;
            case TYPE_FLOAT:        // 浮点数
                if ($raw["type"] != 'string' && $raw["type"] != 'float')
                    $err["fileds"] = "字段". $name . "类型错误, 和数据库的字段类型不匹配";
                break;
            case TYPE_TIMESTAMP:    // 时间戳
                if ($raw["type"] != 'string' && $raw["type"] != 'int')
                    $err["fileds"] = "字段". $name . "类型错误, 和数据库的字段类型不匹配";
                break;
            case TYPE_DATE:         // 日期
                if ($raw["type"] != 'string' && $raw["type"] != 'datetime' && $raw["type"] != 'date')
                    $err["fileds"] = "字段". $name . "类型错误, 和数据库的字段类型不匹配";
                break;
            case TYPE_DATETIME:     // 日期时间
            case TYPE_MTIME:        // 修改时间
            case TYPE_CTIME:        // 创建时间
                if ($raw["type"] != 'string' && $raw["type"] != 'datetime')
                    $err["fileds"] = "字段". $name . "类型错误, 和数据库的字段类型不匹配";
                break;
            case TYPE_STRING:       // 字符串
                if ($raw["type"] != 'string' && $raw["type"] != 'enum' && $raw["type"] != 'set')
                    $err["fileds"] = "字段". $name . "类型错误, 和数据库的字段类型不匹配";
                break;
            case TYPE_JSON:         // Json
                $field->ishide = true;
            case TYPE_TEXT:         // 长文本
                $field->ishide = true;
            case TYPE_USER:         // 用户名
            case TYPE_MUSER:        // 修改者
            case TYPE_CUSER:        // 创建者
            case TYPE_COMPLATE:     // 自动补全
            case TYPE_URL:
            case TYPE_IMAGE:
            case TYPE_EMAIL:
            case TYPE_IP:
                if ($raw["type"] != 'string')
                    $err["fileds"] = "字段". $name . "类型错误, 和数据库的字段类型不匹配";
                break;
            default:
                $err["fileds"] = "字段". $name . "未知类型";
                break 2;
            };
        }

        if (count($err) > 0)
            return ["errcode" => 400, "errmsg" => "配置错误", "error" => $err];

        $adv = new AdvConfig();

        $err = $adv->checkAdvConfig($input, $raw_fileds);
        if ($err !== true)
            return ["errcode" => 400, "errmsg" => "高级配置有错误", "error" => $err];
        return true;
    }

    // 获取最大的id
    private function getMaxId($key, $config, $field)
    {
        return DB::connection($key)->table($config->dbname . '.' . $config->tablename)->max($field) + 1;
    }

    private function checkByJsonSchema($schema, $json)
    {
        $validator = new \JsonSchema\Validator();
        $validator->check($json, $schema);

        if (!$validator->isValid())
            return ['errcode' => 400, 'errmsg' => 'Bad Request: error data param', "errors" => $validator->getErrors()];
        return true;
    }

    // 检查输入参数
    public function checkData(Request $request, $input, $config, &$data, $raw_fileds, $create, $key)
    {
        $err = [];
        foreach ($config->fields as $fielddef) {
            $name = $fielddef->name;
            $limit = (isset($config->adv_config) && $config->adv_config && isset($config->adv_config->rules) &&
                isset($config->adv_config->rules->$name)) ? $config->adv_config->rules->$name : null;

            switch ($fielddef->type) {
            case TYPE_MTIME:
                $data[$name] = date("Y-m-d H:i:s");
                continue 2;
            case TYPE_CTIME: // 创建时间不允许修改
                if ($create)
                    $data[$name] = date("Y-m-d H:i:s");
                continue 2;
            case TYPE_MUSER:
                $data[$name] = $request->user->email;
                continue 2;
            case TYPE_CUSER: // 创建人不允许修改
                if ($create)
                    $data[$name] = $request->user->email;
                continue 2;
            case TYPE_INCREMENT: // 自增量
                if ($create) {
                    if ($raw_fileds[$name]["extra"] != 'auto_increment')
                        $data[$name] = $this->getMaxId($key, $config, $name);
                    continue 2;
                }
                break;
            }

            if (!isset($input->$name)) {
                if (isset($fielddef->need) && $fielddef->need)
                    $err[$name] = "必填";
                continue;
            }

            $v = $input->$name;
            if ($fielddef->islist) {
                if (!is_array($v)) {
                    $err[$name] = "数组格式错误";
                    continue;
                }

                if (isset($config->adv_config) && isset($config->adv_config->arraySizeLimit) &&
                    isset($config->adv_config->arraySizeLimit->$name))
                {
                    $count = $config->adv_config->arraySizeLimit->$name;
                    if (isset($count->max) && count($v) > $count->max)
                        $err[$name] = "数组个数超出了最大个数限制，最多为{$count->max}个元素";
                    if (isset($count->min) && count($v) < $count->min)
                        $err[$name] = "数组个数超出了最小个数限制，最小为{$count->min}个元素";
                }

                foreach ($v as $item) {
                    $r = $this->checkInputFormt($fielddef, $item, $raw_fileds[$name], $limit);
                    if ($r !== true) {
                        $err[$name] = $r;
                        continue 2;
                    }
                }

                if ($raw_fileds[$name]["type"] == 'set') {
                    $v = $this->converEnums($v);
                } else if ($raw_fileds[$name]["type"] == 'enum') {
                    return "和数据库存储类型不匹配";
                } else {
                    $v = json_encode($v);
                    if (strlen($v) > $raw_fileds[$name]["maxlen"]) {
                        $err[$name] = "超出了存储长度";
                        continue;
                    }
                }
            } else if ($fielddef->type == TYPE_JSON) {
                if (!is_array($v) && !is_object($v)) {
                    $err[$name] = "对象格式错误";
                    continue;
                }

                if (isset($config->adv_config) && isset($config->adv_config->jsonSchema) &&
                    isset($config->adv_config->jsonSchema->$name)) {
                    $e = $this->checkByJsonSchema($config->adv_config->jsonSchema->$name, $v);
                    if ($e !== true) {
                        $err[$name] = $e;
                        continue;
                    }
                }

                $v = json_encode($v);
                if (strlen($v) > $raw_fileds[$name]["maxlen"]) {
                    $err[$name] = "超出了存储长度";
                    continue;
                }
            } else {
                $r = $this->checkInputFormt($fielddef, $v, $raw_fileds[$name], $limit);
                if ($r !== true) {
                    $err[$name] = $r;
                    continue;
                }
            }

            $data[$name] = $v;
        }

        if ($config->table_id == 0)
            $data["last_author"] = $request->user->email;

        if (count($err) > 0)
            return ["errcode" => 400, "errmsg" => "参数错误", "error" => $err];
        return true;
    }

    public function dataDecodeItem(&$data, $names)
    {
        foreach ($names as $name) {
            if (isset($data->$name))
                $data->$name = json_decode($data->$name);
        }
    }

    public function dataDecode(&$datas, $fields)
    {
        $names = [];
        foreach ($fields as $field) {
            if ($field->type == TYPE_JSON || $field->islist)
                $names[] = $field->name;
        }

        if (!is_array($datas)) {
            $this->dataDecodeItem($datas, $names);
        } else {
            foreach ($datas as &$data)
                $this->dataDecodeItem($data, $names);
        }
    }

    static function checkHistoryDepends($schema)
    {
        $type = 0;
        foreach ($schema['fields'] as $f => $def) {
            if ($def->type == TYPE_IMAGE) {
                $type |= 1;
            } else if ($def->type == TYPE_JSON) {
                if (isset($schema['adv_config']) && isset($schema['adv_config']->jsonSchema) &&
                    isset($schema['adv_config']->jsonSchema->$f))
                {
                    $type |= 2;
                } else {
                    $type |= 4;
                }
            }
        }
        if ($schema['table_id'] == 1 || $schema['table_id'] == 0)
            $type |= 4;

        return $type;

    }

    static function checkScriptDepends($schema)
    {
        $type = 0;
        foreach ($schema->fields as $f => $def) {
            if ($def->type == TYPE_IMAGE) {
                $type |= 1;
            } else if ($def->type == TYPE_JSON) {
                if (isset($schema->adv_config) && isset($schema->adv_config->jsonSchema) &&
                    isset($schema->adv_config->jsonSchema->$f))
                {
                    $type |= 2;
                } else {
                    $type |= 4;
                }
            }
        }
        if ($schema->table_id == 1 || $schema->table_id == 0)
            $type |= 4;

        return $type;
    }

    static function tableScriptDepends()
    {
        return 4;
    }
};