Intention is to run MySQL query against PostgreSQL. This adds double quotes to each query fieldName, tableName, tableName, so PostgreSQL doesn't lower() names; It also encloses strings within single quotes (and removes double quotes); It encloses integers within single quotes (in case you insert/compare it against boolean); This is something whi…

ConvertMySQLtoPgSQL.php

<?php

class ConvertMySQLToPgSQL {

    /**
     * @var \PHPSQLParser\PHPSQLParser
     */
    protected $parser;

    /**
     * @var \Monolog\Logger
     */
    protected $logger;

    public function __construct() {
        $this->parser = new \PHPSQLParser\PHPSQLParser();
        $this->logger = LoggerProvider::getLogger();
    }

    public function convert($sql, $parsed = false) {

        if ($parsed) {
            $parsedSql = $sql;
        } else {
            $parsedSql = $this->parser->parse($sql);
        }

        if (isset($parsedSql['UNION'])) {

            $convertedSQL = '';
            $unionWord = ' UNION ';
            $unionWordLength = strlen($unionWord);

            foreach ($parsedSql['UNION'] as $key => $queryPart) {
                $convertedSQL .= $this->convert($queryPart, true) . $unionWord;
            }

            $convertedSQL = substr($convertedSQL, 0, -1*$unionWordLength); // Removed UNION word from the end.

            return $convertedSQL;

        }


        if (isset($parsedSql['SELECT'])) {
            foreach ($parsedSql['SELECT'] as $key => &$expression) {
                if ($expression['expr_type'] === 'colref') {
                    $expression = $this->convertColref($expression);
                }
            }
        }

        if (isset($parsedSql['FROM'])) {
            foreach ($parsedSql['FROM'] as $key => &$expression) {
                if ($expression['expr_type'] === 'table') {
                    $expression['table'] = $this->encloseOnce($expression['table']);

                    if (isset($expression['alias']) && $expression['alias']) {
                        $expression['alias']['name'] = $this->encloseOnce($expression['alias']['name']);
                    }

                    if (isset($expression['ref_clause']) && $expression['ref_clause']) {

                        foreach ($expression['ref_clause'] as &$joinExpression) {
                            if ($joinExpression['expr_type'] === 'colref') {
                                $joinExpression = $this->convertColref($joinExpression);
                            }
                        }
                    }
                }
            }
        }

        if (isset($parsedSql['WHERE'])) {
            foreach ($parsedSql['WHERE'] as $key => &$expression) {
                if ($expression['expr_type'] === 'colref') {
                    $expression = $this->convertColref($expression);
                }

                if ($expression['expr_type'] === 'const') {
                    $expression = $this->convertConst($expression);
                }
            }
        }

        if (isset($parsedSql['SET'])) {
            foreach ($parsedSql['SET'] as $setKey => &$setExpression) {
                if ($setExpression['expr_type'] === 'expression' && $setExpression['sub_tree']) {
                    foreach ($setExpression['sub_tree'] as $key => &$expression) {
                        if ($expression['expr_type'] === 'colref') {
                            $expression = $this->convertColref($expression);
                        }

                        if ($expression['expr_type'] === 'const') {
                            $expression = $this->convertConst($expression);
                        }
                    }

                }
            }
        }



        $convertedSQL = new \PHPSQLParser\PHPSQLCreator($parsedSql);

        if (!$convertedSQL->created) {
            $this->logger->error("Failed to create SQL from Parsed", ['sql' => $sql]);
            return false;
        }

        return $convertedSQL->created;
    }

    protected function convertColref($expression) {

        // Aliased column e.g. `r.ISOcode`
        if (isset($expression['no_quotes']) && $expression['no_quotes']['delim']) {
            $alias = $this->encloseOnce($expression['no_quotes']['parts'][0]);
            $delimiter = $expression['no_quotes']['delim'];
            $field = $this->encloseOnce($expression['no_quotes']['parts'][1]);
            $expression['base_expr'] = $alias.$delimiter.$field;
        } else { // Non-aliased column e.g. `userName`
            $expression['base_expr'] = $this->encloseOnce($expression['base_expr']);
        }

        if (isset($expression['alias']) && $expression['alias']) {
            $expression['alias']['name'] = $this->encloseOnce($expression['alias']['name']);
        }

        return $expression;
    }

    protected function convertConst($expression) {
        $trimmedBaseExpression = trim($expression['base_expr'], '"'); // Remove double quotes -> it's likely to be a string

       /* if ($trimmedBaseExpression !== $expression['base_expr']) { // Check if they were removed -> it's likely to be a string
            $expression['base_expr'] = $this->encloseOnce($trimmedBaseExpression, "'"); // Add single quotes for strings.
        }*/

        // Enclose everything; PostgreSQL can deal with e.g. string<>boolean, but can't with integer<>boolean
        $expression['base_expr'] = $this->encloseOnce($trimmedBaseExpression, "'");

        return $expression;
    }

    protected function encloseOnce($string, $enclosure = '"') {

        // Don't enclose star
        if ($string === '*') {
            return $string;
        }

        $string = trim($string, '`'); // Get rid of ` enclosure

        return $enclosure.trim($string, $enclosure).$enclosure;
    }
}