Dump Database Using Doctrine -> Without `MYSQL DUMP`

DatabaseBackup.php

<?php
/**
 * (MYSQL EXPORT WITHOUT DUMP USE DOCTRINE DBAL)
 * 
 * 
 *
 * NOTE : ONLY WORKS WITH `MYSQL` ONLY
 *
 * This Class Using Doctrine DBAL to instantiate connection
 * {@link : https://github.com/doctrine/dbal}
 *
 * Tested Using DBAL 2.5
 * But Just Tes To Export Simple Database, and does not guarantee to make all valid,
 * Just Improve The Script to get Better result.
 *
 * @license MIT
 * @version 1.0.0
 *
 * DONATE
 * ----------------------------------------
 * paypal: nawa@yahoo.com
 * bitcoin: 1J3XQSkkkVDbV9Xaohxpg5hdLxMENwCZ23
 */

namespace Pentagonal;

use InvalidArgumentException;
use Doctrine\DBAL\Connection;
use Doctrine\DBAL\Types\Type;

/**
 * Class DatabaseBackup
 * @package Pentagonal
 */
class DatabaseBackup
{
    const PROCEDURE_SELECTOR = 'PROCEDURES';
    const FUNCTION_SELECTOR  = 'FUNCTIONS';

    /**
     * @var string
     */
    protected $newLine = "\n\n";

    /**
     * @var string
     */
    protected $commentAfter = '--------------------------------------------------------';

    /**
     * @var Connection
     */
    protected $database;

    /**
     * Limit for Insert Values MYSQL
     *
     * @var int
     */
    protected $limitInsert = 500;

    /**
     * @var array
     */
    protected $routines;

    /**
     * @var array[]
     */
    protected $triggers;

    /**
     * @var array
     */
    protected $tables = [];

    /**
     * @var array
     */
    protected $cacheCreateTables = [];

    /**
     * DatabaseBackup constructor.
     * @param Connection $database
     */
    public function __construct(Connection $database)
    {
        $this->database = $database;
        foreach ($this->database->getSchemaManager()->listTables() as $key => $table) {
            $this->tables[$key] = $table->getName();
        }
    }

    /**
     * @param string $comments
     * @return string
     */
    protected function addComment(string $comments) : string
    {
        return "-- {$this->commentAfter}\n--\n-- {$comments}\n--\n-- {$this->commentAfter}";
    }

    /**
     * @return string
     */
    public function getSQLFullBackup() : string
    {
        $returnValue = '';

        $databaseName = $this->database->quoteIdentifier($this->database->getDatabase());
        foreach ($this->getCreateProcedures() as $procedureName => $procedure) {
            $returnValue .= $this->addComment(
                "Database {$databaseName}\n-- Function Name : {$this->database->quoteIdentifier($procedureName)}"
            );
            $returnValue .= $this->newLine;
            $returnValue .= $procedure;
            $returnValue .= $this->newLine;
        }

        foreach ($this->getCreateFunctions() as $functionName => $function) {
            $returnValue .= $this->addComment(
                "Database {$databaseName}\n-- Trigger Name : {$this->database->quoteIdentifier($functionName)}"
            );
            $returnValue .= $this->newLine;
            $returnValue .= $function;
            $returnValue .= $this->newLine;
        }

        foreach ($this->tables as $tableName) {
            $returnValue .= $this->addComment(
                "Table Structure For {$this->database->quoteIdentifier($tableName)}"
            );

            $returnValue .= $this->newLine;
            $returnValue .= $this->getSQLCreateTable($tableName);
            $returnValue .= $this->newLine;
            $tableNameQuoted = $this->database->quoteIdentifier($tableName);
            foreach ((array) $this->getSQLCreateTrigger($tableName) as $triggerName => $trigger) {
                $returnValue .= $this->addComment(
                    "Table {$tableNameQuoted}\n-- Trigger Name : {$this->database->quoteIdentifier($triggerName)}"
                );
                $returnValue .= $this->newLine;
                $returnValue .= $trigger;
                $returnValue .= $this->newLine;
            }
            if ($data = $this->getSQLInsertData($tableName)) {
                $returnValue .= $this->addComment(
                    "Dumping data for table {$this->database->quoteIdentifier($tableName)}"
                );
                $returnValue .= $this->newLine;
                $returnValue .= $data;
                $returnValue .= $this->newLine;
            }
        }

        return $returnValue;
    }

    /**
     * Get Create Procedures
     *
     * @return array
     */
    public function getCreateProcedures() : array
    {
        if (isset($this->routines[self::PROCEDURE_SELECTOR])) {
            return $this->routines[self::PROCEDURE_SELECTOR];
        }

        $this->routines[self::PROCEDURE_SELECTOR] = [];
        $sth = $this
            ->database
            ->query(
                'SHOW PROCEDURE STATUS'
            );
        while ($row = $sth->fetch(\PDO::FETCH_ASSOC)) {
            $name = $row['Name'];
            $statement = $this
                ->database
                ->query("SHOW CREATE PROCEDURE {$this->database->quoteIdentifier($row['Name'])}");
            $data = $statement->fetch(\PDO::FETCH_ASSOC);
            if (!empty($data['Create Procedure'])) {
                $this->routines[self::PROCEDURE_SELECTOR][$name] = "DELIMITER \$\$\n";
                $this->routines[self::PROCEDURE_SELECTOR][$name] .= $data['Create Procedure'] ."\$\$\n";
                $this->routines[self::PROCEDURE_SELECTOR][$name] .= "DELIMITER ;\n";
            }
            $statement->closeCursor();
        }

        return $this->routines[self::PROCEDURE_SELECTOR];
    }

    /**
     * Get Create Functions
     *
     * @return array
     */
    public function getCreateFunctions() : array
    {
        if (isset($this->routines[self::FUNCTION_SELECTOR])) {
            return $this->routines[self::FUNCTION_SELECTOR];
        }
        $this->routines[self::FUNCTION_SELECTOR] = [];
        $sth = $this
            ->database
            ->query(
                'SHOW FUNCTION STATUS'
            );
        while ($row = $sth->fetch(\PDO::FETCH_ASSOC)) {
            $name = $row['Name'];
            $statement = $this
                ->database
                ->query("SHOW CREATE FUNCTION {$this->database->quoteIdentifier($row['Name'])}");
            $data = $statement->fetch(\PDO::FETCH_ASSOC);
            if (!empty($data['Create Function'])) {
                $this->routines[self::FUNCTION_SELECTOR][$name] =  "DELIMITER \$\$\n";
                $this->routines[self::FUNCTION_SELECTOR][$name] .= $data['Create Function'] ."\$\$\n";
                $this->routines[self::FUNCTION_SELECTOR][$name] .=  "DELIMITER ;\n";
            }
            $statement->closeCursor();
        }

        return $this->routines[self::FUNCTION_SELECTOR];
    }

    /**
     * @return array
     */
    public function getCreateRoutines() : array
    {
        $this->getCreateFunctions();
        $this->getCreateProcedures();
        return $this->routines;
    }

    /**
     * @param string $tableName
     * @return string
     */
    public function getSQLBackupTable(string $tableName) : string
    {
        return $this
            ->getSQLCreateTable($tableName)
            . "\n"
            . $this->getSQLInsertData($tableName);
    }

    /**
     * @return array
     */
    public function getSQLCreateTriggers()
    {
        if (!isset($this->triggers)) {
            $sth = $this->database->query('SHOW TRIGGERS');
            while ($row = $sth->fetch(\PDO::FETCH_ASSOC)) {
                if (!isset($this->triggers[$row['Table']])) {
                    $this->triggers[$row['Table']] = [];
                }

                $name = $row['Trigger'];
                $statement = $this
                    ->database
                    ->query("SHOW CREATE TRIGGER {$this->database->quoteIdentifier($name)}");
                $data = $statement->fetch(\PDO::FETCH_ASSOC);
                if (!empty($data['SQL Original Statement'])) {
                    $this->triggers[$row['Table']][$name] = "DELIMITER \$\$\n";
                    $this->triggers[$row['Table']][$name] .= $data['SQL Original Statement'] ."\$\$\n";
                    $this->triggers[$row['Table']][$name] .= 'DELIMITER ;';
                }
                $statement->closeCursor();
            }
        }

        return $this->triggers;
    }

    /**
     * @param string $tableName
     * @return array|null
     */
    public function getSQLCreateTrigger(string $tableName)
    {
        if (!in_array($tableName, $this->tables)) {
            throw new InvalidArgumentException(
                sprintf(
                    'Tabe %1$s does not exist on database %2$s',
                    $tableName,
                    $this->database->getDatabase()
                )
            );
        }

        $this->getSQLCreateTriggers();

        return isset($this->triggers[$tableName])
            ? $this->triggers[$tableName]
            : null;
    }

    /**
     * Get Create Table
     *
     * @param string $tableName
     * @return string
     * @throws InvalidArgumentException
     */
    public function getSQLCreateTable(string $tableName) :string
    {
        if (!in_array($tableName, $this->tables)) {
            throw new InvalidArgumentException(
                sprintf(
                    'Tabe %1$s does not exist on database %2$s',
                    $tableName,
                    $this->database->getDatabase()
                )
            );
        }

        if (!isset($this->cacheCreateTables[$tableName])) {
            $this->cacheCreateTables[$tableName] = $this->formatCreateTable(
                $this
                    ->database
                    ->getDatabasePlatform()
                    ->getCreateTableSQL(
                        $this
                            ->database
                            ->getSchemaManager()
                            ->listTableDetails($tableName)
                    )[0]
            );
        }

        return $this->cacheCreateTables[$tableName];
    }

    /**
     * Get Insert Statement
     *
     * @param string $tableName
     * @return string
     */
    protected function getSQLInsertData(string $tableName) :string
    {
        $tableObject = $this->database->getSchemaManager()->listTableDetails($tableName);
        $quotedTable = $this->database->quoteIdentifier($tableName);
        $isMysql = stripos($this->database->getDriver()->getName(), 'mysql') !== false;
        $statement = $this
            ->database
            ->createQueryBuilder()
            ->select('*')
            ->from($tableName)
            ->execute();
        $noNeedToQuote = [];
        $counted = 0;
        $returnValue = '';
        $selectorInsert = null;
        while ($row = $statement->fetch(\PDO::FETCH_ASSOC)) {
            $counted++;
            foreach ($row as $tableName => $value) {
                if (!isset($noNeedToQuote[$tableName])) {
                    $noNeedToQuote[$tableName] = in_array(
                        $tableObject->getColumn($tableName)->getType()->getName(),
                        [Type::BIGINT, Type::INTEGER, Type::SMALLINT]
                    );
                }
                if (empty($noNeedToQuote[$tableName])) {
                    $row[$tableName] = $this->database->quote($value);
                }
            }

            if ($isMysql) {
                if (!$selectorInsert) {
                    $selectorInsert = '('.implode(
                        ', ',
                        array_map([$this->database, 'quoteIdentifier'], array_keys($row))
                    ).')';
                }

                if ($counted === 1) {
                    $returnValue .= "INSERT INTO {$quotedTable} {$selectorInsert}\n  VALUES";
                    $returnValue .= ' (' . implode(', ', $row) . ')';
                } else {
                    $returnValue .= '         (' . implode(', ', $row) . ')';
                }
                if ($counted === $this->limitInsert) {
                    $returnValue .= ";\n";
                    $counted = 0;
                } else {
                    $returnValue .= ",\n";
                }
            } else {
                if (!$selectorInsert) {
                    $selectorInsert = array_map([$this->database, 'quoteIdentifier'], array_keys($row));
                }
                $qb = $this->database
                    ->createQueryBuilder()
                    ->insert($quotedTable);
                $row = array_values($row);
                foreach ($selectorInsert as $key => $tableName) {
                    $qb->setValue(
                        $tableName,
                        $row[$key]
                    );
                }
                $returnValue .= rtrim($qb->getSQL(), "\n\;\,") .";\n";
            }
        }

        if ($isMysql) {
            $returnValue = rtrim($returnValue, "\n,;");
            if (trim($returnValue) != '') {
                $returnValue .= ';';
            }
        }

        return $returnValue;
    }

    /**
     * Format Prettify Create Table
     *
     * @param string $createTable
     * @return string
     */
    protected function formatCreateTable(string $createTable) : string
    {
        preg_match(
            '~
                (?P<createTable>CREATE\s+TABLE)\s* # create table
                (?P<tableName>[^\s\(]+)\s*
                \(
                    (?P<container>.+)
                \)
                (?P<properties>[^\)\(]+)$
            ~xi',
            $createTable,
            $match
        );

        if (empty($match)) {
            return $createTable;
        }

        preg_match_all(
            '~(?P<tableName>[^\s\(]+)(?P<prop>[^\,]+)\,?~xi',
            $match['container'],
            $matchAll,
            PREG_PATTERN_ORDER
        );

        if (empty($matchAll)) {
            return $createTable;
        }
        $columns = array_keys(
            $this
            ->database
            ->getSchemaManager()
            ->listTableDetails($match['tableName'])
            ->getColumns()
        );
        $match['tableName'] = $this->database->quoteIdentifier($match['tableName']);
        $match['container'] = '';
        $columnSet = [];
        foreach ($matchAll['tableName'] as $key => $column) {
            if (in_array($column, $columns) &&  !in_array($column, $columnSet)) {
                $columnSet[] = $column;
                $column = $this->database->quoteIdentifier($column);
            } else {
                $matchAll['prop'][$key] = preg_replace_callback(
                    '~(?<key>KEY)\s*(?P<name>[^\(]+)?\((?P<selector>[^\s\)]+)\)~xi',
                    function ($match) {
                        $selector = !empty($match['selector']) && trim($match['selector'])
                            ? $match['selector']
                            : null;
                        if ($selector) {
                            $selector = explode(',', $match['selector']);
                            $selector = array_map(function ($data) {
                                return $this->database->quoteIdentifier(trim($data));
                            }, $selector);
                            $selector = implode(', ', $selector);
                            $selector = "({$selector})";
                        }
                        $name = !empty($match['name']) && trim($match['name'])
                            ? $this->database->quoteIdentifier($match['name'])
                            : '';
                        $key = trim($match['key']);
                        return "{$key} {$name} {$selector}";
                    },
                    $matchAll['prop'][$key]
                );
            }
            $match['container'] .= "    {$column} {$matchAll['prop'][$key]},\n";
        }

        $match['container'] = rtrim($match['container'], "\n,");
        return "{$match['createTable']} {$match['tableName']}(\n{$match['container']}\n){$match['properties']};";
    }
}