sql_format.php

См. документацию.

<?php
if (!defined('T_KEYWORD'))
{
    define('T_KEYWORD', 400);
}
if (!defined('T_BAD_CHARACTER'))
{
    define('T_BAD_CHARACTER', 401);
}
if (!defined('T_CHARACTER'))
{
    define('T_CHARACTER', 402);
}
 
class CSqlFormatFormatter
{
    public function format($tokens)
    {
        $result = '';
        $skipWS = false;
        foreach ($tokens as $i => $token)
        {
            if ($token[1] === ',')
            {
                $this->removeTrailingSpaces($result);
                $result .= ",\x3" . str_repeat("\x2", $token[2]);
                $skipWS = true;
            }
            elseif (
                $token[1] === '='
                || $token[1] === '-'
                || $token[1] === '+'
                || $token[1] === '*'
                || $token[1] === '/'
                || $token[1] === '<='
                || $token[1] === '>='
            )
            {
                $this->removeTrailingSpaces($result);
                $result .= "\x1" . $token[1] . "\x1";
                $skipWS = true;
            }
            elseif (
                $token[1] === 'INNER'
                || $token[1] === 'LEFT'
                || $token[1] === 'SET'
                || $token[1] === 'AND'
                || $token[1] === 'OR'
            )
            {
                $result .= "\x3" . str_repeat("\x2", $token[2]) . $token[1];
            }
            else
            {
                if ($skipWS)
                {
                    $skipWS = false;
                    if ($token[0] === T_WHITESPACE)
                    {
                        continue;
                    }
                }
                elseif ($i > 0 && ($tokens[$i - 1][2] <> $token[2]))
                {
                    $result .= "\x3" . str_repeat("\x2", $token[2]);
                    if ($token[0] === T_WHITESPACE)
                    {
                        continue;
                    }
                }
 
                if ($token[0] === T_WHITESPACE)
                {
                    $result .= "\x1";
                }
                else
                {
                    $result .= $token[1];
                }
            }
        }
 
        $result = preg_replace_callback("/(
            \\([\\x1\\x2\\x3_0-9A-Za-z.0-9_,]+\\)
            |\\([\\x1\\x2\\x3'x%0-9,]+\\)
            |\\([\\x1\\x2\\x3]*[a-zA-Z0-9_.]+[\\x1\\x2\\x3]*=[\\x1\\x2\\x3]*[a-zA-Z0-9_.']+[\\x1\\x2\\x3]*\\)
        )/x", [$this, 'removeSpaces'], $result);
 
        $result = str_replace([
            "\x1",
            "\x2",
            "\x3",
        ], [
            $this->getSpace(),
            $this->getTab(),
            $this->getEol(),
        ], $result);
 
        return $result;
    }
 
    public function removeSpaces($match)
    {
        $result = preg_replace("/^\\([\\x1\\x2\\x3]+/", '(', $match[0]);
        $result = preg_replace("/[\\x1\\x2\\x3]+\\)\$/", ')', $result);
        $result = preg_replace("/,[\\x1\\x2\\x3]+/", ', ', $result);
        return $result;
    }
 
    public function removeTrailingSpaces(&$str)
    {
        $str = rtrim($str, "\x1\x2\x3");
    }
 
    public function getEol()
    {
        return ' ';
    }
 
    public function getSpace()
    {
        return ' ';
    }
 
    public function getTab()
    {
        return ' ';
    }
}
 
class CSqlFormatText extends CSqlFormatFormatter
{
    public function getEol()
    {
        return "\n";
    }
 
    public function getSpace()
    {
        return ' ';
    }
 
    public function getTab()
    {
        return "\t";
    }
}
 
class CSqlTokenizer
{
    private $tokens = null;
    private $current = 0;
 
    public function parse($sql)
    {
        $this->tokens = token_get_all('<?' . $sql);
        array_shift($this->tokens);
        $this->current = 0;
 
        while (isset($this->tokens[$this->current]))
        {
            //Remove excessive brackets
            if (
                $this->tokens[$this->current] === '('
                && $this->lookForwardFor('(')
            )
            {
                if ($this->removeBalancedBrackets())
                {
                    continue;
                }
            }
 
            //Remove following spaces
            if ($this->tokens[$this->current][0] === T_WHITESPACE && $this->tokens[$this->current - 1][0] === T_WHITESPACE)
            {
                array_splice($this->tokens, $this->current, 1);
                continue;
            }
 
            $this->tokens[$this->current] = $this->transform($this->tokens[$this->current]);
            $this->current++;
        }
 
        //Remove leading spaces
        while (
            isset($this->tokens[0])
            && $this->tokens[0][0] === T_WHITESPACE
        )
        {
            array_splice($this->tokens, 0, 1);
        }
 
        //Remove trailing spaces
        while (
            !empty($this->tokens)
            && $this->tokens[count($this->tokens) - 1][0] === T_WHITESPACE
        )
        {
            array_splice($this->tokens, -1, 1);
        }
 
        return $this->tokens;
    }
 
    protected function transform($token)
    {
        static $keywords = 'UPDATE|SET|DELETE|SELECT|DISTINCT|INNER|LEFT|OUTER|JOIN|ON|FROM|WHERE|GROUP|BY|IN|EXISTS|HAVING|ORDER|ASC|DESC|LIMIT|AND|OR';
        static $functions = 'DATE_FORMAT|UNIX_TIMESTAMP|CONCAT|DATE_ADD|UPPER|LENGTH|IFNULL';
 
        if (isset($token[1]))
        {
            $token = [$token[0], $token[1]];
        }
        else
        {
            $token = [T_CHARACTER, $token[0]];
        }
 
        switch ($token[0])
        {
        case T_STRING:
            if (preg_match('/^(' . $keywords . ')$/i', $token[1]))
            {
                $token = [T_KEYWORD, mb_strtoupper($token[1])];
            }
            elseif (preg_match('/^(' . $functions . ')$/i', $token[1]))
            {
                $token = [T_FUNCTION, $token[1]];
            }
            break;
        case T_LOGICAL_AND:
        case T_LOGICAL_OR:
            $token = [T_KEYWORD, mb_strtoupper($token[1])];
            break;
        case T_AS:
            $token = [T_KEYWORD, $token[1]];
            break;
        case T_COMMENT:
        case T_BAD_CHARACTER:
            $token = [T_WHITESPACE, ' '];
            break;
        }
 
        return $token;
    }
 
    protected function removeBalancedBrackets()
    {
        $pos = $this->current;
        $balance = 0;
        $hasOp = [false];
        while (isset($this->tokens[$pos]))
        {
            if ($this->tokens[$pos][0] === '(')
            {
                $balance++;
                $hasOp[$balance] = false;
            }
            elseif ($this->tokens[$pos][0] === ')')
            {
                $balance--;
            }
            elseif (
                $this->tokens[$pos][0] === T_LOGICAL_AND
                || $this->tokens[$pos][0] === T_LOGICAL_OR
                || $this->tokens[$pos][0] === ','
            )
            {
                $hasOp[$balance] = true;
            }
 
            if ($balance === 0)
            {
                if (!$hasOp[$balance + 1])
                {
                    array_splice($this->tokens, $pos, 1);
                    array_splice($this->tokens, $this->current, 1);
                    return true;
                }
                else
                {
                    return false;
                }
            }
            $pos++;
        }
        return false;
    }
 
    protected function lookForwardFor($token)
    {
        $pos = $this->current + 1;
        while (isset($this->tokens[$pos]))
        {
            if ($this->tokens[$pos] == $token)
            {
                return true;
            }
            elseif ($this->tokens[$pos][0] !== T_WHITESPACE)
            {
                return false;
            }
            $pos++;
        }
        return false;
    }
}
 
class CSqlLevel
{
    private $tokens = [];
    private $balance = 0;
    private $level = 0;
    private $current = 0;
 
    public function addLevel(array $tokens)
    {
        $this->level = [];
        $this->balance = 0;
        $this->tokens = $tokens;
        $this->current = 0;
        while (isset($this->tokens[$this->current]))
        {
            if ($this->tokens[$this->current][1] === '(')
            {
                $this->balance++;
            }
            elseif ($this->tokens[$this->current][1] === ')')
            {
                $this->balance--;
            }
 
            if ($this->tokens[$this->current][0] !== T_WHITESPACE)
            {
                $this->changeLevelBefore();
            }
 
            $this->tokens[$this->current][] = array_sum($this->level);
 
            if ($this->tokens[$this->current][0] !== T_WHITESPACE)
            {
                $this->changeLevelAfter();
            }
 
            $this->current++;
        }
 
        return $this->tokens;
    }
 
    public function changeLevelBefore()
    {
        if ($this->tokens[$this->current][1] === ')')
        {
            $this->level['(']--;
            $this->level['SELECT_' . ($this->balance + 1)] = 0;
            $this->level['JOIN_' . ($this->balance + 1)] = 0;
        }
        elseif (
            $this->tokens[$this->current][1] === 'FROM'
            || $this->tokens[$this->current][1] === 'LIMIT'
        )
        {
            $this->level['SELECT_' . $this->balance]--;
        }
        elseif (
            $this->tokens[$this->current][1] === 'WHERE'
            || $this->tokens[$this->current][1] === 'GROUP'
            || $this->tokens[$this->current][1] === 'HAVING'
            || $this->tokens[$this->current][1] === 'ORDER'
        )
        {
            $this->level['SELECT_' . $this->balance]--;
            if ($this->level['JOIN_' . $this->balance] ?? 0)
            {
                $this->level['JOIN_' . $this->balance]--;
            }
        }
        elseif (
            $this->tokens[$this->current][1] === 'INNER'
            || $this->tokens[$this->current][1] === 'LEFT'
        )
        {
            if ($this->level['JOIN_' . $this->balance] > 0)
            {
                $this->level['JOIN_' . $this->balance]--;
            }
        }
    }
 
    public function changeLevelAfter()
    {
        if ($this->tokens[$this->current][1] === '(')
        {
            if (isset($this->level['(']))
            {
                $this->level['(']++;
            }
            else
            {
                $this->level['('] = 1;
            }
        }
        elseif (
            (
                $this->tokens[$this->current][1] === 'SELECT'
                && !$this->lookForwardFor('DISTINCT')
            ) || (
                $this->tokens[$this->current][1] === 'DISTINCT'
            )
        )
        {
            if (isset($this->level['SELECT_' . $this->balance]))
            {
                $this->level['SELECT_' . $this->balance]++;
            }
            else
            {
                $this->level['SELECT_' . $this->balance] = 1;
            }
        }
        elseif (
            $this->tokens[$this->current][1] === 'FROM'
            || $this->tokens[$this->current][1] === 'WHERE'
            || $this->tokens[$this->current][1] === 'BY'
            || $this->tokens[$this->current][1] === 'HAVING'
            || $this->tokens[$this->current][1] === 'SET'
        )
        {
            if (isset($this->level['SELECT_' . $this->balance]))
            {
                $this->level['SELECT_' . $this->balance]++;
            }
            else
            {
                $this->level['SELECT_' . $this->balance] = 1;
            }
        }
        elseif ($this->tokens[$this->current][1] === 'ON')
        {
            if (isset($this->level['JOIN_' . $this->balance]))
            {
                $this->level['JOIN_' . $this->balance]++;
            }
            else
            {
                $this->level['JOIN_' . $this->balance] = 1;
            }
        }
    }
 
    protected function lookForwardFor($token)
    {
        $pos = $this->current + 1;
        while (isset($this->tokens[$pos]))
        {
            if ($this->tokens[$pos][1] == $token)
            {
                return true;
            }
            elseif ($this->tokens[$pos][0] !== T_WHITESPACE)
            {
                return false;
            }
            $pos++;
        }
        return false;
    }
 
    protected function lookBackwardFor($token)
    {
        $pos = $this->current - 1;
        while (isset($this->tokens[$pos]))
        {
            if ($this->tokens[$pos][1] == $token)
            {
                return true;
            }
            elseif ($this->tokens[$pos][0] !== T_WHITESPACE)
            {
                return false;
            }
            $pos--;
        }
        return false;
    }
}
 
class CSqlFormat
{
    private $tokenizer = null;
    private $levelizer = null;
 
    private $current = null;
    private $level = 0;
    private $add = 0;
    private $result = '';
    private $formatter = null;
 
    public function __construct()
    {
        $this->tokenizer = new CSqlTokenizer;
        $this->levelizer = new CSqlLevel;
 
        $this->level = 0;
        $this->add = 0;
        $this->current = 0;
        $this->result = '';
    }
 
    public static function reformatSql($sql, CSqlFormatFormatter $formatter = null)
    {
        if (function_exists('token_get_all'))
        {
            $format = new CSqlFormat;
            $format->setFormatter($formatter ?? new CSqlFormatText);
            return $format->format($sql);
        }
        else
        {
            return $sql;
        }
    }
 
    public function setFormatter(CSqlFormatFormatter $formatter)
    {
        $this->formatter = $formatter;
    }
 
    public function format($sql)
    {
        $tokens = $this->tokenizer->parse($sql);
        $tokens = $this->levelizer->addLevel($tokens);
        return $this->formatter->format($tokens);
    }
}