framework/src/Dever/Sql.php

<?php namespace Dever;
class Sql
{
    public static function desc($table)
    {
        return 'DESC ' . $table;
    }
    public static function truncate($table)
    {
        return 'TRUNCATE TABLE `' . $table . '`';
    }
    public static function opt($table)
    {
        return 'OPTIMIZE TABLE `' . $table . '`';
    }
    public static function explain($sql)
    {
        return 'EXPLAIN ' . $sql;
    }
    public static function showIndex($table)
    {
        return 'SHOW INDEX FROM ' . $table;
    }
    public static function create($config)
    {
        $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 .= self::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 static function createField($name, $set)
    {
        $field = '`' . $name . '` ' . strtoupper($set['type']);
        if ($name == 'id') {
            $field .= 'UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT 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 static function alter($table, $struct, $data)
    {
        $sql = array();
        $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 . '` ' . self::createField($field, $set);
                    } else {
                        unset($struct[$field]);
                    }
                } else {
                    $sql[] = $alter . ' DROP `' . $field . '`';
                }
            }
        }
        if ($struct) {
            foreach ($struct as $k => $v) {
                $sql[] = $alter . ' ADD ' . self::createField($k, $v);
            }
        }
        return implode(';', $sql);
    }
    public static function index($table, $index, $del = array())
    {
        $sql = array();
        $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 static 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) {
                $sql = 'PARTITION p'.$v['name'].' VALUES IN ('.$v['value'].')';
            }
            return $alter . '('.$sql.')';
        } elseif ($partition['type'] == 'hash' || $partition['type'] == 'key') {
            if ($state) {
                return $alter . 'PARTITIONS ' . $partition['value'];
            }
            return self::desc($table);
        }
    }
    public static function select($table, $param, &$bind, $set = array(), $field = array(), $lock = false, $type = '')
    {
        $col = '*';
        $rule = '';
        if (isset($set['col'])) {
            $col = $set['col'];
        }
        if (isset($set['join'])) {
            $temp = explode('_', $table);
            $prefix = $temp[0] . '_'  . $temp[1] . '_';
            $table .= ' AS ' . $temp[2];
            foreach ($set['join'] as $k => $v) {
                $table .= ' ' . $v['type'] . ' ' . $prefix . $v['table'] . ' AS ' . $v['table'] . ' 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 . self::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 ($lock) {
            $rule .= ' FOR UPDATE';
        }
        return 'SELECT ' . $col . ' FROM ' . $table . self::where($param, $bind, $field, $type) . $rule;
    }
    public static function where($param, &$bind, $field = array(), $type = '')
    {
        if ($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)) {
                                self::field($second_link, $bind, $i, $k1, $v1[0], $v1[1], $field, $type);
                            } else {
                                self::field($first_link, $bind, $i, $k1, '=', $v1, $field, $type);
                            }
                        }
                        $first_link = ltrim($first_link, ' and ');
                        $second .= ' ' . $k . ' (' . $first_link . $second_link . ')';
                    } else {
                        if (is_array($v)) {
                            self::field($second, $bind, $i, $k, $v[0], $v[1], $field, $type);
                        } else {
                            self::field($first, $bind, $i, $k, '=', $v, $field, $type);
                        }
                    }
                }
                return ' WHERE ' . ltrim($first . $second, ' and ');
            } elseif (is_numeric($param)) {
                if ($type == 'Influxdb') {
                    return ' WHERE "id" = \'' . $param . '\'';
                } else {
                    return ' WHERE id = ' . intval($param);
                }
            } else {
                return ' WHERE ' . $param;
            }
        } else {
            return '';
        }
    }
    private static function field(&$s, &$b, &$i, $k, $e, $v, $f, $t)
    {
        $type = '';
        if (is_string($b)) {
            $type = $b;
            $b = array();
        }
        if ($f && empty($f[$k]) && strpos('id,cdate', $k) === false) {
            return;
        }
        $s .= ' and ';
        $p = ':'.$k.$i;
        $x = '`';
        if ($t == 'Influxdb') {
            $e = '=';
            if ($k == 'cdate') {
                $k = 'time';
                $v = date('Y-m-d H:i:s', $v-28800);
            }
            $x = '"';
            $p = "'".$v."'";
        }
        $k = $x.$k.$x;
        if (strpos($e,'in') !== false) {
            if (!is_array($v)) {
                $v = explode(',', $v);
            }
            $t = '';
            foreach ($v as $ti => $tj) {
                if ($ti > 0) {
                    $ti = $p.'_'.$ti;
                    $t .= ','.$ti;
                    $b[$ti] = $tj;
                } else {
                    $v = $tj;
                    $t .= $p;
                }
            }
            $s .= $k.' ' .$e.' ('.$t.')';
        } elseif ($e == 'like') {
            $s .= 'instr('.$k.','.$p.')'.' > 0';
        } elseif ($e == 'group') {
            $v = ','.$v.',';
            $s .= 'instr(concat(",",'.$k.',","),'.$p.')' . ' > 0';
        } elseif ($e == 'between') {
            $b[$p.'_e'] = $v[1];
            $v = $v[0];
            $s .= $k.' between '.$p.' and '.$p.'_e';
        } else {
            $s .= $k.$e.$p;
        }
        $b[$p] = $v;
        $i++;
    }
    public static function insert($table, $data, &$bind, $field)
    {
        $sql = 'INSERT INTO `' . $table . '` SET ';#IGNORE
        foreach ($data as $k => $v) {
            if ($field && empty($field[$k]) && strpos('id,cdate', $k) === false) {
                continue;
            }
            $sql .= '`' . $k . '`=:' . $k . ',';
            $bind[':'.$k] = $v;
        }
        return rtrim($sql, ',');
    }
    public static 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)) {
                $a = '`' . $k . '`' . $v[0];
                $v = $v[1];
            }
            $sql .= '`' . $k . '`=' . $a . ':' . $k . ',';
            $bind[':'.$k] = $v;
        }
        return rtrim($sql, ',') . self::where($param, $bind, $field);
    }
    public static function delete($table, $param, &$bind, $field)
    {
        return 'DELETE FROM `' . $table . '`' . self::where($param, $bind, $field);
    }
    public static function inserts($table, $param)
    {
        $num = $param['num'] ?? 1;
        $sql = 'INSERT INTO `' . $table . '` (' . $param['field'] . ') VALUES ';
        foreach ($param['value'] as $k => $v) {
            for ($i = 1; $i <= $num; $i++) {
                $insert[] = '(' . $v . ')';
            }
        }
        $sql .= implode(',', $insert) . ',';
        return rtrim($sql, ',');
    }
    public static function distance($lng, $lat)
    {
        return 'round((st_distance(point(lng, lat), point('.$lng.', '.$lat.'))*111195)/1000, 2) as distance';
    }
}