framework/src/Dever/String/Sql.php

<?php namespace Dever\String;
class Sql
{
    public static function setColOrder($order)
    {
        $this->colOrder = $order;
    }
    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']) && $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 .= ' NULL';
        }
        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') {
                $set = $struct[$field] ?? false;
                if ($set) {
                    if ($set['type'] != $v['Type'] || (isset($set['default']) && $set['default'] != $v['Default'])) {
                        $sql[] = $alter . ' CHANGE `' . $field . '` `' . $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'] == 'cdate') {
                $state = false;
            }
        }
        $sql = '';
        if ($state) {
            return 'ALTER TABLE `' . $table . '` DROP PRIMARY KEY, ADD PRIMARY KEY (`id`, `cdate`) USING BTREE;ALTER TABLE `' . $table . '` PARTITION BY RANGE (cdate) (PARTITION p'.$partition.' VALUES LESS THAN ('.$partition.'));';
        } else {
            return 'ALTER TABLE `' . $table . '` ADD PARTITION (PARTITION p'.$partition.' VALUES LESS THAN ('.$partition.'));';
        }
    }
    public static function select($table, $param, &$bind, $set = array(), $field = array())
    {
        $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'])) {
            $rule .= ' ORDER BY ' . $set['order'];
        }
        if (isset($set['limit'])) {
            if (is_array($set['limit'])) {
                $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 {
                $rule .= ' LIMIT ' . $set['limit'];
            }
        }
        return 'SELECT ' . $col . ' FROM ' . $table . self::where($param, $bind, $field) . $rule;
    }
    public static function where($param, &$bind, $field = array())
    {
        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);
                            } else {
                                self::field($first_link, $bind, $i, $k1, '=', $v1, $field);
                            }
                        }
                        $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);
                        } else {
                            self::field($first, $bind, $i, $k, '=', $v, $field);
                        }
                    }
                }
                return ' WHERE ' . ltrim($first . $second, ' and ');
            } elseif (is_numeric($param)) {
                return ' WHERE id = ' . $param;
            } else {
                return ' WHERE ' . $param;
            }
        } else {
            return '';
        }
    }
    private static function field(&$s, &$b, &$i, $k, $e, $v, $f)
    {
        if ($f && empty($f[$k]) && strpos('id,cdate', $k) === false) {
            return;
        }
        $s .= ' and ';
        $p = ':'.$k.$i;
        $k = '`'.$k.'`';
        if (strpos($e,'in') !== false) {
            $s .= $k.$e.'('.$p.')';
        } 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;
        }
        $sql = rtrim($sql, ',');
        return $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 = $v[1];
                $v = $v[0];
            }
            $sql .= '`' . $k . '`=`' . $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, ',');
    }
}