framework/src/Dever/Sql.php

<?php namespace Dever;
use Dever;
class Sql
{
    public function desc($table)
    {
        return 'DESC ' . $table;
    }
    public function truncate($table)
    {
        return 'TRUNCATE TABLE `' . $table . '`';
    }
    public function optimize($table)
    {
        return 'OPTIMIZE TABLE `' . $table . '`';
    }
    public function analyze($table)
    {
        return 'ANALYZE TABLE `' . $table . '`';
    }
    public function explain($sql)
    {
        return 'EXPLAIN ' . $sql;
    }
    public function showIndex($table)
    {
        return 'SHOW INDEX FROM ' . $table;
    }
    public function create($config)
    {
        if (isset(Dever::config('setting')['database']['create']) && !Dever::config('setting')['database']['create']) {
            return;
        }
        $sql = 'DROP TABLE IF EXISTS `' . $config['table'] . '`;CREATE TABLE IF NOT EXISTS `' . $config['table'] . '`(';
        $struct = array('id' => array('name' => 'ID', 'type' => 'int(11)'),'cdate' => array('name' => 'cdate', 'type' => 'int(11)'));
        $struct = array_merge($struct, $config['struct']);
        foreach ($struct as $k => $v) {
            $sql .= $this->createField($k, $v) . ',';
        }
        $sql = rtrim($sql, ',') . ')';
        if (isset($config['auto'])) {
            $sql .= ' AUTO_INCREMENT = ' . $config['auto'];
        }
        if (isset($config['type'])) {
            $sql .= ' ENGINE = ' . $config['type'] . ' ';
        }
        return $sql . ' COMMENT="'.$config['name'].'"';
    }
    public function createField($name, $set)
    {
        $field = '`' . $name . '` ' . strtoupper($set['type']);
        if ($name == 'id') {
            $field .= ' UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL';
        } elseif (strpos($set['type'], 'text') !== false) {
            $field .= ' NULL';
        } elseif (isset($set['default'])) {
            $field .= ' NOT NULL DEFAULT "' . $set['default'] . '"';
        } elseif (strpos($set['type'], 'int') !== false || strpos($set['type'], 'float') !== false || strpos($set['type'], 'decimal') !== false || strpos($set['type'], 'double') !== false) {
            $field .= ' NOT NULL DEFAULT 0';
        } else {
            $field .= ' NOT NULL DEFAULT ""';
        }
        if (isset($set['name'])) {
            $field .= ' COMMENT \'' . $set['name'] . '\'';
        }
        return $field;
    }
    public function alter($table, $struct, $data)
    {
        $sql = [];
        $alter = 'ALTER TABLE `' . $table . '` ';
        foreach ($data as $v) {
            $field = $v['Field'];
            if ($field != 'id' && $field != 'cdate') {
                $set = $struct[$field] ?? false;
                if ($set) {
                    if ($set['type'] != $v['Type'] || (isset($set['default']) && $set['default'] != $v['Default'])) {
                        $sql[] = $alter . ' CHANGE `' . $field . '` ' . $this->createField($field, $set);
                    } else {
                        unset($struct[$field]);
                    }
                } else {
                    $sql[] = $alter . ' DROP `' . $field . '`';
                }
            }
        }
        if ($struct) {
            foreach ($struct as $k => $v) {
                $sql[] = $alter . ' ADD ' . $this->createField($k, $v);
            }
        }
        return implode(';', $sql);
    }
    public function index($table, $index, $del = [])
    {
        $sql = [];
        $alter = 'ALTER TABLE `' . $table . '` ';
        foreach ($del as $v) {
            if ($v['Key_name'] != 'PRIMARY') {
                $sql[] = $alter . ' DROP INDEX ' . $v['Key_name'];
            }
        }
        if ($index) {
            foreach ($index as $k => $v) {
                $t = 'INDEX';
                if (strpos($v, '.')) {
                    list($v, $t) = explode('.', $v);
                }
                $sql[] = $alter . ' ADD ' . strtoupper($t) . ' ' . $k . ' (' . $v . ')';
            }
        }
        return implode(';', $sql);
    }
    public function partition($table, $partition, $index)
    {
        $state = true;
        foreach ($index as $k => $v) {
            if ($v['Key_name'] == 'PRIMARY' && $v['Column_name'] == $partition['field']) {
                $state = false;
            }
        }
        $alter = '';
        if ($state) {
            $type = $partition['type'];
            if ($partition['type'] == 'time') {
                $type = 'range';
            }
            $alter = 'ALTER TABLE `' . $table . '` DROP PRIMARY KEY, ADD PRIMARY KEY (`id`, `'.$partition['field'].'`) USING BTREE;ALTER TABLE `' . $table . '` PARTITION BY '.strtoupper($type).' ('.$partition['field'].') ';
        } else {
            $alter = 'ALTER TABLE `' . $table . '` ADD PARTITION ';
        }
        if ($partition['type'] == 'range' || $partition['type'] == 'time') {
            $name = $partition['value'];
            if ($partition['type'] == 'time') {
                $name = date('Ymd', $name - 86400);
            }
            $sql = 'PARTITION p'.$name.' VALUES LESS THAN ('.$partition['value'].')';
            return $alter . '('.$sql.')';
        } elseif ($partition['type'] == 'list') {
            $sql = [];
            foreach ($partition['value'] as $k => $v) {
                $k = str_replace('-', '_', $v);
                $sql[] = 'PARTITION p'.$k.' VALUES IN ('.$v.')';
            }
            $sql = implode(',', $sql);
            return $alter . '('.$sql.')';
        } elseif ($partition['type'] == 'hash' || $partition['type'] == 'key') {
            if ($state) {
                return $alter . 'PARTITIONS ' . $partition['value'];
            }
            return $this->desc($table);
        }
    }
    public function select($table, $param, &$bind, $set = [], $field = [], $version = false, $type = '')
    {
        $col = '*';
        $rule = '';
        if (isset($set['col'])) {
            $col = $set['col'];
        }
        if (isset($set['join'])) {
            $table .= ' AS main';
            foreach ($set['join'] as $k => $v) {
                $table .= ' ' . $v['type'] . ' ' . DEVER_PROJECT . '_' . $v['table'] . ' AS t' . $k . ' ON ' . $v['on'];
            }
        }
        if (isset($set['group'])) {
            $rule .= ' GROUP BY ' . $set['group'];
        }
        if (isset($set['order'])) {
            if ($type == 'Influxdb') {
                $set['order'] = ' time desc';
            }
            $rule .= ' ORDER BY ' . $set['order'];
        }
        if (isset($set['limit'])) {
            if (is_array($set['limit']) && !$type) {
                $table .= ' inner join (select id from ' . $table . $this->where($param, $bind, $field) . $rule . ' limit ' . $set['limit'][0].','.$set['limit'][1].') as t on '.$table.'.id=t.id';
                $rule = '';
                $param = false;
            } else {
                if ($type == 'Influxdb' && strpos($set['limit'], ',')) {
                    $temp = explode(',', $set['limit']);
                    $rule .= ' LIMIT ' . $temp[1] . ' OFFSET ' . $temp[0];
                } else {
                    $rule .= ' LIMIT ' . $set['limit'];
                }
            }
        }
        if ($version) {
            $rule .= ' FOR UPDATE';
        }
        return 'SELECT ' . $col . ' FROM ' . $table . $this->where($param, $bind, $field, $type) . $rule;
    }
    public function where($param, &$bind, $field = [], $type = '')
    {
        if ($param || is_numeric($param)) {
            $first = $second = '';
            if (is_array($param)) {
                $i = 0;
                foreach ($param as $k => $v) {
                    if (strpos($k, '#')) {
                        $k = trim($k, '#');
                    }
                    if ($k == 'or' || $k == 'and') {
                        $first_link = $second_link = '';
                        foreach ($v as $k1 => $v1) {
                            if (is_array($v1)) {
                                $this->field($second_link, $bind, $i, $k1, $v1[0], $v1[1], $field, $type);
                            } else {
                                $this->field($first_link, $bind, $i, $k1, '=', $v1, $field, $type);
                            }
                        }
                        $second .= ' ' . $k . ' (' . $this->replace($first_link) . $second_link . ')';
                    } else {
                        if (is_array($v)) {
                            $this->field($second, $bind, $i, $k, $v[0], $v[1], $field, $type);
                        } else {
                            $this->field($first, $bind, $i, $k, '=', $v, $field, $type);
                        }
                    }
                }
                return ' WHERE ' . $this->replace($first . $second);
            } elseif (is_numeric($param)) {
                if ($type == 'Influxdb') {
                    return ' WHERE "id" = \'' . $param . '\'';
                } else {
                    return ' WHERE id = ' . intval($param);
                }
            } else {
                return ' WHERE ' . $param;
            }
        } else {
            return '';
        }
    }
    private function field(&$sql, &$bind, &$num, $key, $symbol, $value, $field, $type)
    {
        $prefix = '';
        if (strstr($key, '.')) {
            $temp = explode('.', $key);
            $key = $temp[1];
            $prefix = $temp[0] . '.';
        } elseif ($field && empty($field[$key]) && strpos('id,cdate', $key) === false) {
            return;
        }
        $sql .= ' and ';

        $state = false;
        $index = '';
        $link = '`';
        if ($type == 'Influxdb') {
            
            if ($key == 'cdate') {
                $key = 'time';
                $value = date('Y-m-d H:i:s', $value-28800);
            }
            $link = '"';
            if (strpos($symbol, 'in') !== false) {
                $symbol = '=';
            }
        } elseif (is_string($value) && stristr($value, 'select ')) {
            $index = $value;
        } elseif (is_array($bind)) {
            $state = true;
            $index = ':'.$key.$num;
        }
        if ($state == false && $index == '') {
            $index = "'".$value."'";
        }
        $key = $prefix.$link.$key.$link;
        if (strpos($symbol, 'in') !== false) {
            if ($state) {
                if (!is_array($value)) {
                    $value = explode(',', $value);
                }
                $in = '';
                foreach ($value as $k => $v) {
                    if ($k > 0) {
                        $k = $index.'_'.$k;
                        $in .= ','.$k;
                        $state && $bind[$k] = $v;
                    } else {
                        $value = $v;
                        $in .= $index;
                    }
                }
            } else {
                $in = $index;
            }
            $sql .= $key.' ' .$symbol.' ('.$in.')';
        } elseif ($symbol == 'like') {
            $sql .= 'instr('.$key.','.$index.')'.' > 0';
        } elseif ($symbol == 'group') {
            $value = ','.$value.',';
            $sql .= 'instr(concat(",",'.$key.',","),'.$index.')' . ' = 1';
            //$sql .= 'FIND_IN_SET("'.$index.'", '.$key.') = 1';
        } elseif ($symbol == 'JSON_CONTAINS') {
            $sql .= 'JSON_CONTAINS('.$key.', "'.$index.'", "$")';
        } elseif ($symbol == 'between') {
            $state && $bind[$index.'_e'] = $value[1];
            $value = $value[0];
            $sql .= $key.' between '.$index.' and '.$index.'_e';
        } else {
            $sql .= $key.$symbol.$index;
        }
        $state && $bind[$index] = $value;
        $num++;
    }
    public function insert($table, $data, &$bind, $field)
    {
        $sql = 'INSERT INTO `' . $table . '` SET ';#IGNORE
        foreach ($data as $k => $v) {
            if (!$v && $v !== 0) {
                continue;
            }
            if ($field && empty($field[$k]) && strpos('id,cdate', $k) === false) {
                continue;
            }
            $sql .= '`' . $k . '`=:' . $k . ',';
            $bind[':'.$k] = $v;
        }
        return rtrim($sql, ',');
    }
    public function inserts($table, $param)
    {
        $num = $param['num'] ?? 1;
        $sql = 'INSERT IGNORE INTO `' . $table . '` (' . $param['field'] . ') VALUES ';
        foreach ($param['value'] as $k => $v) {
            if (is_array($v)) {
                $v = '"' . implode('","', $v) . '"';
            }
            for ($i = 1; $i <= $num; $i++) {
                $insert[] = '(' . $v . ')';
            }
        }
        $sql .= implode(',', $insert) . ',';
        return rtrim($sql, ',');
    }
    public function update($table, $param, $data, &$bind, $field)
    {
        $i = 0;
        $sql = 'UPDATE `' . $table . '` SET ';
        foreach ($data as $k => $v) {
            if ($field && empty($field[$k]) && strpos('id,cdate', $k) === false) {
                continue;
            }
            $a = '';
            if (is_array($v)) {
                if (isset($v[2])) {
                    $a = '`' . $v[0] . '`' . $v[1] . '`' . $v[2] . '`';
                    $sql .= '`' . $k . '`=' . $a . ',';
                    continue;
                } elseif (isset($v[1])) {
                    $a = '`' . $k . '`' . $v[0];
                    $v = $v[1];
                } else {
                    $v = json_encode($v, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
                }
            }
            $sql .= '`' . $k . '`=' . $a . ':' . $k . ',';
            $bind[':'.$k] = $v;
        }
        return rtrim($sql, ',') . $this->where($param, $bind, $field);
    }
    public function delete($table, $param, &$bind, $field)
    {
        return 'DELETE FROM `' . $table . '`' . $this->where($param, $bind, $field);
    }
    public function copy($table, $dest, $param, &$bind, $field)
    {
        $on = 'a.`' . $field[0] . '` = b.`' . $field[0] . '`';
        $prefix = 'a.';
        $insert = $keys = [];
        foreach ($field as $v) {
            $insert[] = '`' . $v . '`';
            $keys[] = 'a.`' . $v . '`';
        }
        $keys = implode(',', $keys);
        $insert = implode(',', $insert);
        $dest = DEVER_PROJECT . '_' . $dest;
        $sql = 'INSERT INTO `' . $table . '`('.$insert.') SELECT '.$keys.' FROM ' . $dest . ' a LEFT JOIN '.$table.' b ON '.$on.' ' . $this->where($param, $bind, []) . ' AND b.`'.$field[0].'` IS NULL';

        return $sql;
    }
    public function distance($lng, $lat)
    {
        return 'round((st_distance(point(lng, lat), point('.$lng.', '.$lat.'))*111195)/1000, 2) as distance';
    }
    private function replace($string)
    {
        if (strpos($string, ' and ') === 0) {
            $string = substr($string, 5);
        }
        return $string;
    }
}