MysqlAdapter::showColumns() - Code Metrics - dimtrovich/php-db-dumper - Measure and Improve Code Quality continuously with Scrutinizer

MysqlAdapter::showColumns() A
last analyzed 2025-02-05 09:03 UTC

↳ Parent: MysqlAdapter

Complexity

Conditions	1
Paths	1

Size

Total Lines	3
Code Lines	1

Duplication

Lines	0
Ratio	0 %

Importance

Changes	1
Bugs	0	Features	0

Metric	Value
cc	1
eloc	1
c	1
b	0
f	0
nc	1
nop	1
dl	0
loc	3
rs	10

<?php

/**
 * This file is part of dimtrovich/db-dumper".
 *
 * (c) 2024 Dimitri Sitchet Tomkeu <[email protected]>
 *
 * For the full copyright and license information, please view
 * the LICENSE file that was distributed with this source code.
 */

namespace Dimtrovich\DbDumper\Adapters;

use Dimtrovich\DbDumper\Exceptions\Exception;

class MysqlAdapter extends Factory
{
    public const DEFINER_RE = 'DEFINER=`(?:[^`]|``)*`@`(?:[^`]|``)*`';

    // Numerical Mysql types
    public $mysqlTypes = [
        'numerical' => [
            'bit',
            'tinyint',
            'smallint',
            'mediumint',
            'int',
            'integer',
            'bigint',
            'real',
            'double',
            'float',
            'decimal',
            'numeric',
        ],
        'blob' => [
            'tinyblob',
            'blob',
            'mediumblob',
            'longblob',
            'binary',
            'varbinary',
            'bit',
            'geometry', // http://bugs.mysql.com/bug.php?id=43544
            'point',
            'linestring',
            'polygon',
            'multipoint',
            'multilinestring',
            'multipolygon',
            'geometrycollection',
        ],
    ];

    /**
     * {@inheritDoc}
     */
    public function databases(string $databaseName): string
    {
        if ($this->option->no_create_db) {
            return '';
        }

        $resultSet    = $this->pdo->query("SHOW VARIABLES LIKE 'character_set_database';");
        $characterSet = $resultSet->fetchColumn(1);
        $resultSet->closeCursor();

        $resultSet   = $this->pdo->query("SHOW VARIABLES LIKE 'collation_database';");
        $collationDb = $resultSet->fetchColumn(1);
        $resultSet->closeCursor();

        return "CREATE DATABASE /*!32312 IF NOT EXISTS*/ `{$databaseName}`" .
            " /*!40100 DEFAULT CHARACTER SET {$characterSet} " .
            " COLLATE {$collationDb} */;" . PHP_EOL . PHP_EOL .
            "USE `{$databaseName}`;" . PHP_EOL . PHP_EOL;
    }

    /**
     * {@inheritDoc}
     */
    public function showCreateTable(string $tableName): string
    {
        return "SHOW CREATE TABLE `{$tableName}`";
    }

    /**
     * {@inheritDoc}
     */
    public function createTable(array $row): string
    {
        if (! isset($row['Create Table'])) {
            throw new Exception('Error getting table code, unknown output');
        }

        $createTable = $row['Create Table'];

        if ($this->option->reset_auto_increment) {
            $match       = '/AUTO_INCREMENT=[0-9]+/s';
            $replace     = '';
            $createTable = preg_replace($match, $replace, $createTable);
        }

        if ($this->option->if_not_exists) {
            $createTable = preg_replace('/^CREATE TABLE/', 'CREATE TABLE IF NOT EXISTS', $createTable);
        }

        return '/*!40101 SET @saved_cs_client     = @@character_set_client */;' . PHP_EOL .
            '/*!40101 SET character_set_client = ' . $this->option->default_character_set . ' */;' . PHP_EOL .
            $createTable . ';' . PHP_EOL .
            '/*!40101 SET character_set_client = @saved_cs_client */;' . PHP_EOL .
            PHP_EOL;
    }

    /**
     * {@inheritDoc}
     */
    public function showCreateView(string $viewName): string
    {
        return "SHOW CREATE VIEW `{$viewName}`";
    }

    /**
     * {@inheritDoc}
     */
    public function showCreateTrigger(string $triggerName): string
    {
        return "SHOW CREATE TRIGGER `{$triggerName}`";
    }

    /**
     * {@inheritDoc}
     */
    public function showCreateProcedure(string $procedureName): string
    {
        return "SHOW CREATE PROCEDURE `{$procedureName}`";
    }

    /**
     * {@inheritDoc}
     */
    public function showCreateFunction(string $functionName): string
    {
        return "SHOW CREATE FUNCTION `{$functionName}`";
    }

    /**
     * {@inheritDoc}
     */
    public function showCreateEvent(string $eventName): string
    {
        return "SHOW CREATE EVENT `{$eventName}`";
    }

    /**
     * {@inheritDoc}
     */
    public function createView(array $row): string
    {
        if (! isset($row['Create View'])) {
            throw new Exception('Error getting view structure, unknown output');
        }

        $viewStmt = $row['Create View'];

        $definerStr = $this->option->skip_definer ? '' : '/*!50013 \2 */' . PHP_EOL;

        if ($viewStmtReplaced = preg_replace(
            '/^(CREATE(?:\s+ALGORITHM=(?:UNDEFINED|MERGE|TEMPTABLE))?)\s+('
            . self::DEFINER_RE . '(?:\s+SQL SECURITY (?:DEFINER|INVOKER))?)?\s+(VIEW .+)$/',
            '/*!50001 \1 */' . PHP_EOL . $definerStr . '/*!50001 \3 */',
            $viewStmt,
            1
        )) {
            $viewStmt = $viewStmtReplaced;
        }

        return $viewStmt . ';' . PHP_EOL . PHP_EOL;
    }

    /**
     * {@inheritDoc}
     */
    public function createTrigger(array $row): string
    {
        if (! isset($row['SQL Original Statement'])) {
            throw new Exception('Error getting trigger code, unknown output');
        }

        $triggerStmt = $row['SQL Original Statement'];
        $definerStr  = $this->option->skip_definer ? '' : '/*!50017 \2*/ ';
        if ($triggerStmtReplaced = preg_replace(
            '/^(CREATE)\s+(' . self::DEFINER_RE . ')?\s+(TRIGGER\s.*)$/s',
            '/*!50003 \1*/ ' . $definerStr . '/*!50003 \3 */',
            $triggerStmt,
            1
        )) {
            $triggerStmt = $triggerStmtReplaced;
        }

        return 'DELIMITER ;;' . PHP_EOL .
            $triggerStmt . ';;' . PHP_EOL .
            'DELIMITER ;' . PHP_EOL . PHP_EOL;
    }

    /**
     * {@inheritDoc}
     */
    public function createProcedure(array $row): string
    {
        if (! isset($row['Create Procedure'])) {
            throw new Exception('Error getting procedure code, unknown output. ' .
                "Please check 'https://bugs.mysql.com/bug.php?id=14564'");
        }

        $procedureStmt = $row['Create Procedure'];

        if ($this->option->skip_definer) {
            if ($procedureStmtReplaced = preg_replace(
                '/^(CREATE)\s+(' . self::DEFINER_RE . ')?\s+(PROCEDURE\s.*)$/s',
                '\1 \3',
                $procedureStmt,
                1
            )) {
                $procedureStmt = $procedureStmtReplaced;
            }
        }

        return '/*!50003 DROP PROCEDURE IF EXISTS `' .
            $row['Procedure'] . '` */;' . PHP_EOL .
            '/*!40101 SET @saved_cs_client     = @@character_set_client */;' . PHP_EOL .
            '/*!40101 SET character_set_client = ' . $this->option->default_character_set . ' */;' . PHP_EOL .
            'DELIMITER ;;' . PHP_EOL .
            $procedureStmt . ' ;;' . PHP_EOL .
            'DELIMITER ;' . PHP_EOL .
            '/*!40101 SET character_set_client = @saved_cs_client */;' . PHP_EOL . PHP_EOL;
    }

    /**
     * {@inheritDoc}
     */
    public function createFunction(array $row): string
    {
        if (! isset($row['Create Function'])) {
            throw new Exception('Error getting function code, unknown output. ' .
                "Please check 'https://bugs.mysql.com/bug.php?id=14564'");
        }

        $functionStmt        = $row['Create Function'];
        $characterSetClient  = $row['character_set_client'];
        $collationConnection = $row['collation_connection'];
        $sqlMode             = $row['sql_mode'];

        if ($this->option->skip_definer) {
            if ($functionStmtReplaced = preg_replace(
                '/^(CREATE)\s+(' . self::DEFINER_RE . ')?\s+(FUNCTION\s.*)$/s',
                '\1 \3',
                $functionStmt,
                1
            )) {
                $functionStmt = $functionStmtReplaced;
            }
        }

        return '/*!50003 DROP FUNCTION IF EXISTS `' .
            $row['Function'] . '` */;' . PHP_EOL .
            '/*!40101 SET @saved_cs_client     = @@character_set_client */;' . PHP_EOL .
            '/*!50003 SET @saved_cs_results     = @@character_set_results */ ;' . PHP_EOL .
            '/*!50003 SET @saved_col_connection = @@collation_connection */ ;' . PHP_EOL .
            '/*!40101 SET character_set_client = ' . $characterSetClient . ' */;' . PHP_EOL .
            '/*!40101 SET character_set_results = ' . $characterSetClient . ' */;' . PHP_EOL .
            '/*!50003 SET collation_connection  = ' . $collationConnection . ' */ ;' . PHP_EOL .
            '/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;;' . PHP_EOL .
            "/*!50003 SET sql_mode              = '" . $sqlMode . "' */ ;;" . PHP_EOL .
            '/*!50003 SET @saved_time_zone      = @@time_zone */ ;;' . PHP_EOL .
            "/*!50003 SET time_zone             = 'SYSTEM' */ ;;" . PHP_EOL .
            'DELIMITER ;;' . PHP_EOL .
            $functionStmt . ' ;;' . PHP_EOL .
            'DELIMITER ;' . PHP_EOL .
            '/*!50003 SET sql_mode              = @saved_sql_mode */ ;' . PHP_EOL .
            '/*!50003 SET character_set_client  = @saved_cs_client */ ;' . PHP_EOL .
            '/*!50003 SET character_set_results = @saved_cs_results */ ;' . PHP_EOL .
            '/*!50003 SET collation_connection  = @saved_col_connection */ ;' . PHP_EOL .
            '/*!50106 SET TIME_ZONE= @saved_time_zone */ ;' . PHP_EOL . PHP_EOL;
    }

    /**
     * {@inheritDoc}
     */
    public function createEvent(array $row): string
    {
        if (! isset($row['Create Event'])) {
            throw new Exception('Error getting event code, unknown output. ' .
                "Please check 'http://stackoverflow.com/questions/10853826/mysql-5-5-create-event-gives-syntax-error'");
        }

        $eventName  = $row['Event'];
        $eventStmt  = $row['Create Event'];
        $sqlMode    = $row['sql_mode'];
        $definerStr = $this->option->skip_definer ? '' : '/*!50117 \2*/ ';

        if ($eventStmtReplaced = preg_replace(
            '/^(CREATE)\s+(' . self::DEFINER_RE . ')?\s+(EVENT\s.*)$/s',
            '/*!50106 \1*/ ' . $definerStr . '/*!50106 \3 */',
            $eventStmt,
            1
        )) {
            $eventStmt = $eventStmtReplaced;
        }

        return '/*!50106 SET @save_time_zone= @@TIME_ZONE */ ;' . PHP_EOL .
            '/*!50106 DROP EVENT IF EXISTS `' . $eventName . '` */;' . PHP_EOL .
            'DELIMITER ;;' . PHP_EOL .
            '/*!50003 SET @saved_cs_client      = @@character_set_client */ ;;' . PHP_EOL .
            '/*!50003 SET @saved_cs_results     = @@character_set_results */ ;;' . PHP_EOL .
            '/*!50003 SET @saved_col_connection = @@collation_connection */ ;;' . PHP_EOL .
            '/*!50003 SET character_set_client  = utf8 */ ;;' . PHP_EOL .
            '/*!50003 SET character_set_results = utf8 */ ;;' . PHP_EOL .
            '/*!50003 SET collation_connection  = utf8_general_ci */ ;;' . PHP_EOL .
            '/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;;' . PHP_EOL .
            "/*!50003 SET sql_mode              = '" . $sqlMode . "' */ ;;" . PHP_EOL .
            '/*!50003 SET @saved_time_zone      = @@time_zone */ ;;' . PHP_EOL .
            "/*!50003 SET time_zone             = 'SYSTEM' */ ;;" . PHP_EOL .
            $eventStmt . ' ;;' . PHP_EOL .
            '/*!50003 SET time_zone             = @saved_time_zone */ ;;' . PHP_EOL .
            '/*!50003 SET sql_mode              = @saved_sql_mode */ ;;' . PHP_EOL .
            '/*!50003 SET character_set_client  = @saved_cs_client */ ;;' . PHP_EOL .
            '/*!50003 SET character_set_results = @saved_cs_results */ ;;' . PHP_EOL .
            '/*!50003 SET collation_connection  = @saved_col_connection */ ;;' . PHP_EOL .
            'DELIMITER ;' . PHP_EOL .
            '/*!50106 SET TIME_ZONE= @save_time_zone */ ;' . PHP_EOL . PHP_EOL;
        // Commented because we are doing this in restore_parameters()
        // "/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;" . PHP_EOL . PHP_EOL;
    }

    /**
     * {@inheritDoc}
     */
    public function showTables(string $database): string
    {
        return 'SELECT TABLE_NAME AS tbl_name ' .
            'FROM INFORMATION_SCHEMA.TABLES ' .
            "WHERE TABLE_TYPE='BASE TABLE' AND TABLE_SCHEMA='{$database}' " .
            'ORDER BY TABLE_NAME';
    }

    /**
     * {@inheritDoc}
     */
    public function showViews(string $database): string
    {
        return 'SELECT TABLE_NAME AS tbl_name ' .
            'FROM INFORMATION_SCHEMA.TABLES ' .
            "WHERE TABLE_TYPE='VIEW' AND TABLE_SCHEMA='{$database}' " .
            'ORDER BY TABLE_NAME';
    }

    /**
     * {@inheritDoc}
     */
    public function showTriggers(string $database): string
    {
        return "SHOW TRIGGERS FROM `{$database}`;";
    }

    /**
     * {@inheritDoc}
     */
    public function showColumns(string $table): string
    {
        return "SHOW COLUMNS FROM `{$table}`;";
    }

    /**
     * {@inheritDoc}
     */
    public function showProcedures(string $database): string
    {
        return 'SELECT SPECIFIC_NAME AS procedure_name ' .
            'FROM INFORMATION_SCHEMA.ROUTINES ' .
            "WHERE ROUTINE_TYPE='PROCEDURE' AND ROUTINE_SCHEMA='{$database}'";
    }

    /**
     * {@inheritDoc}
     */
    public function showFunctions(string $database): string
    {
        return 'SELECT SPECIFIC_NAME AS function_name ' .
            'FROM INFORMATION_SCHEMA.ROUTINES ' .
            "WHERE ROUTINE_TYPE='FUNCTION' AND ROUTINE_SCHEMA='{$database}'";
    }

    /**
     * {@inheritDoc}
     */
    public function showEvents(string $database): string
    {
        return 'SELECT EVENT_NAME AS event_name ' .
            'FROM INFORMATION_SCHEMA.EVENTS ' .
            "WHERE EVENT_SCHEMA='{$database}'";
    }

    /**
     * {@inheritDoc}
     */
    public function setupTransaction(): string
    {
        return 'SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ';
    }

    /**
     * {@inheritDoc}
     */
    public function startTransaction(): string
    {
        return 'START TRANSACTION ' .
            '/*!40100 WITH CONSISTENT SNAPSHOT */';
    }

    /**
     * {@inheritDoc}
     */
    public function commitTransaction(): string
    {
        return 'COMMIT';
    }

    /**
     * {@inheritDoc}
     */
    public function lockTable(string $table)
    {
        $this->checkParameters(func_num_args(), $expected_num_args = 1, __METHOD__);
        $table = func_get_arg(0);

        return $this->pdo->exec("LOCK TABLES `{$table}` READ LOCAL");
    }

    /**
     * {@inheritDoc}
     */
    public function unlockTable(string $table)
    {
        return $this->pdo->exec('UNLOCK TABLES');
    }

    /**
     * {@inheritDoc}
     */
    public function startAddLockTable(string $table): string
    {
        return "LOCK TABLES `{$table}` WRITE;" . PHP_EOL;
    }

    /**
     * {@inheritDoc}
     */
    public function endAddLockTable(string $table): string
    {
        return 'UNLOCK TABLES;' . PHP_EOL;
    }

    /**
     * {@inheritDoc}
     */
    public function startAddDisableKeys(string $table): string
    {
        return "/*!40000 ALTER TABLE `{$table}` DISABLE KEYS */;" .
            PHP_EOL;
    }

    /**
     * {@inheritDoc}
     */
    public function endAddDisableKeys(string $table): string
    {
        return "/*!40000 ALTER TABLE `{$table}` ENABLE KEYS */;" .
            PHP_EOL;
    }

    /**
     * {@inheritDoc}
     */
    public function startDisableForeignKeysCheck(): string
    {
        return 'SET foreign_key_checks = 0';
    }

    /**
     * {@inheritDoc}
     */
    public function endDisableForeignKeysCheck(): string
    {
        return 'SET foreign_key_checks = 1';
    }

    /**
     * {@inheritDoc}
     */
    public function startDisableAutocommit(): string
    {
        return 'SET autocommit=0;' . PHP_EOL;
    }

    /**
     * {@inheritDoc}
     */
    public function endDisableAutocommit(): string
    {
        return 'COMMIT;' . PHP_EOL;
    }

    /**
     * {@inheritDoc}
     */
    public function addDropDatabase(string $database): string
    {
        return "/*!40000 DROP DATABASE IF EXISTS `{$database}`*/;" .
            PHP_EOL . PHP_EOL;
    }

    /**
     * {@inheritDoc}
     */
    public function addDropTrigger(string $trigger): string
    {
        return "DROP TRIGGER IF EXISTS `{$trigger}`;" . PHP_EOL;
    }

    /**
     * {@inheritDoc}
     */
    public function dropView(string $view): string
    {
        return "DROP TABLE IF EXISTS `{$view}`;" . PHP_EOL .
                "/*!50001 DROP VIEW IF EXISTS `{$view}`*/;" . PHP_EOL;
    }

    /**
     * {@inheritDoc}
     */
    public function parseColumnType(array $colType): array
    {
        $colInfo = parent::_parseColumnType($colType, $this->mysqlTypes);
        // for virtual columns that are of type 'Extra', column type
        // could by "STORED GENERATED" or "VIRTUAL GENERATED"
        // MySQL reference: https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html
        $colInfo['is_virtual'] = str_contains($colType['Extra'], 'VIRTUAL GENERATED') || str_contains($colType['Extra'], 'STORED GENERATED');

        return $colInfo;
    }

    /**
     * {@inheritDoc}
     */
    public function backupParameters(): string
    {
        $ret = '/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;' . PHP_EOL .
            '/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;' . PHP_EOL .
            '/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;' . PHP_EOL .
            '/*!40101 SET NAMES ' . $this->option->default_character_set . ' */;' . PHP_EOL;

        if (false === $this->option->skip_tz_utc) {
            $ret .= '/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;' . PHP_EOL .
                "/*!40103 SET TIME_ZONE='+00:00' */;" . PHP_EOL;
        }

        if ($this->option->no_autocommit) {
            $ret .= '/*!40101 SET @OLD_AUTOCOMMIT=@@AUTOCOMMIT */;' . PHP_EOL;
        }

        $ret .= '/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;' . PHP_EOL .
            '/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;' . PHP_EOL .
            "/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;" . PHP_EOL .
            '/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;' . PHP_EOL . PHP_EOL;

        return $ret;
    }

    /**
     * {@inheritDoc}
     */
    public function restoreParameters(): string
    {
        $ret = '';

        if (false === $this->option->skip_tz_utc) {
            $ret .= '/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;' . PHP_EOL;
        }

        if ($this->option->no_autocommit) {
            $ret .= '/*!40101 SET AUTOCOMMIT=@OLD_AUTOCOMMIT */;' . PHP_EOL;
        }

        $ret .= '/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;' . PHP_EOL .
            '/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;' . PHP_EOL .
            '/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;' . PHP_EOL .
            '/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;' . PHP_EOL .
            '/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;' . PHP_EOL .
            '/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;' . PHP_EOL .
            '/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;' . PHP_EOL . PHP_EOL;

        return $ret;
    }
}


1			<?php
2
3			/**
4			* This file is part of dimtrovich/db-dumper".
5			*
6			* (c) 2024 Dimitri Sitchet Tomkeu <[email protected]>
7			*
8			* For the full copyright and license information, please view
9			* the LICENSE file that was distributed with this source code.
10			*/
11
12			namespace Dimtrovich\DbDumper\Adapters;
13
14			use Dimtrovich\DbDumper\Exceptions\Exception;
15
16			class MysqlAdapter extends Factory
17			{
18			public const DEFINER_RE = 'DEFINER=`(?:[^`]\|``)`@`(?:[^`]\|``)`';
19
20			// Numerical Mysql types
21			public $mysqlTypes = [
22			'numerical' => [
23			'bit',
24			'tinyint',
25			'smallint',
26			'mediumint',
27			'int',
28			'integer',
29			'bigint',
30			'real',
31			'double',
32			'float',
33			'decimal',
34			'numeric',
35			],
36			'blob' => [
37			'tinyblob',
38			'blob',
39			'mediumblob',
40			'longblob',
41			'binary',
42			'varbinary',
43			'bit',
44			'geometry', // http://bugs.mysql.com/bug.php?id=43544
45			'point',
46			'linestring',
47			'polygon',
48			'multipoint',
49			'multilinestring',
50			'multipolygon',
51			'geometrycollection',
52			],
53			];
54
55			/**
56			* {@inheritDoc}
57			*/
58			public function databases(string $databaseName): string
59			{
60			if ($this->option->no_create_db) {
61			return '';
62			}
63
64			$resultSet = $this->pdo->query("SHOW VARIABLES LIKE 'character_set_database';");
65			$characterSet = $resultSet->fetchColumn(1);
66			$resultSet->closeCursor();
67
68			$resultSet = $this->pdo->query("SHOW VARIABLES LIKE 'collation_database';");
69			$collationDb = $resultSet->fetchColumn(1);
70			$resultSet->closeCursor();
71
72			return "CREATE DATABASE /!32312 IF NOT EXISTS/ `{$databaseName}`" .
73			" /*!40100 DEFAULT CHARACTER SET {$characterSet} " .
74			" COLLATE {$collationDb} */;" . PHP_EOL . PHP_EOL .
75			"USE `{$databaseName}`;" . PHP_EOL . PHP_EOL;
76			}
77
78			/**
79			* {@inheritDoc}
80			*/
81			public function showCreateTable(string $tableName): string
82			{
83			return "SHOW CREATE TABLE `{$tableName}`";
84			}
85
86			/**
87			* {@inheritDoc}
88			*/
89			public function createTable(array $row): string
90			{
91			if (! isset($row['Create Table'])) {
92			throw new Exception('Error getting table code, unknown output');
93			}
94
95			$createTable = $row['Create Table'];
96
97			if ($this->option->reset_auto_increment) {
98			$match = '/AUTO_INCREMENT=[0-9]+/s';
99			$replace = '';
100			$createTable = preg_replace($match, $replace, $createTable);
101			}
102
103			if ($this->option->if_not_exists) {
104			$createTable = preg_replace('/^CREATE TABLE/', 'CREATE TABLE IF NOT EXISTS', $createTable);
105			}
106
107			return '/!40101 SET @saved_cs_client = @@character_set_client /;' . PHP_EOL .
108			'/!40101 SET character_set_client = ' . $this->option->default_character_set . ' /;' . PHP_EOL .
109			$createTable . ';' . PHP_EOL .
110			'/!40101 SET character_set_client = @saved_cs_client /;' . PHP_EOL .
111			PHP_EOL;
112			}
113
114			/**
115			* {@inheritDoc}
116			*/
117			public function showCreateView(string $viewName): string
118			{
119			return "SHOW CREATE VIEW `{$viewName}`";
120			}
121
122			/**
123			* {@inheritDoc}
124			*/
125			public function showCreateTrigger(string $triggerName): string
126			{
127			return "SHOW CREATE TRIGGER `{$triggerName}`";
128			}
129
130			/**
131			* {@inheritDoc}
132			*/
133			public function showCreateProcedure(string $procedureName): string
134			{
135			return "SHOW CREATE PROCEDURE `{$procedureName}`";
136			}
137
138			/**
139			* {@inheritDoc}
140			*/
141			public function showCreateFunction(string $functionName): string
142			{
143			return "SHOW CREATE FUNCTION `{$functionName}`";
144			}
145
146			/**
147			* {@inheritDoc}
148			*/
149			public function showCreateEvent(string $eventName): string
150			{
151			return "SHOW CREATE EVENT `{$eventName}`";
152			}
153
154			/**
155			* {@inheritDoc}
156			*/
157			public function createView(array $row): string
158			{
159			if (! isset($row['Create View'])) {
160			throw new Exception('Error getting view structure, unknown output');
161			}
162
163			$viewStmt = $row['Create View'];
164
165			$definerStr = $this->option->skip_definer ? '' : '/!50013 \2 /' . PHP_EOL;
166
167			if ($viewStmtReplaced = preg_replace(
168			'/^(CREATE(?:\s+ALGORITHM=(?:UNDEFINED\|MERGE\|TEMPTABLE))?)\s+('
169			. self::DEFINER_RE . '(?:\s+SQL SECURITY (?:DEFINER\|INVOKER))?)?\s+(VIEW .+)$/',
170			'/!50001 \1 /' . PHP_EOL . $definerStr . '/!50001 \3 /',
171			$viewStmt,
172			1
173			)) {
174			$viewStmt = $viewStmtReplaced;
175			}
176
177			return $viewStmt . ';' . PHP_EOL . PHP_EOL;
178			}
179
180			/**
181			* {@inheritDoc}
182			*/
183			public function createTrigger(array $row): string
184			{
185			if (! isset($row['SQL Original Statement'])) {
186			throw new Exception('Error getting trigger code, unknown output');
187			}
188
189			$triggerStmt = $row['SQL Original Statement'];
190			$definerStr = $this->option->skip_definer ? '' : '/!50017 \2/ ';
191			if ($triggerStmtReplaced = preg_replace(
192			'/^(CREATE)\s+(' . self::DEFINER_RE . ')?\s+(TRIGGER\s.*)$/s',
193			'/!50003 \1/ ' . $definerStr . '/!50003 \3 /',
194			$triggerStmt,
195			1
196			)) {
197			$triggerStmt = $triggerStmtReplaced;
198			}
199
200			return 'DELIMITER ;;' . PHP_EOL .
201			$triggerStmt . ';;' . PHP_EOL .
202			'DELIMITER ;' . PHP_EOL . PHP_EOL;
203			}
204
205			/**
206			* {@inheritDoc}
207			*/
208			public function createProcedure(array $row): string
209			{
210			if (! isset($row['Create Procedure'])) {
211			throw new Exception('Error getting procedure code, unknown output. ' .
212			"Please check 'https://bugs.mysql.com/bug.php?id=14564'");
213			}
214
215			$procedureStmt = $row['Create Procedure'];
216
217			if ($this->option->skip_definer) {
218			if ($procedureStmtReplaced = preg_replace(
219			'/^(CREATE)\s+(' . self::DEFINER_RE . ')?\s+(PROCEDURE\s.*)$/s',
220			'\1 \3',
221			$procedureStmt,
222			1
223			)) {
224			$procedureStmt = $procedureStmtReplaced;
225			}
226			}
227
228			return '/*!50003 DROP PROCEDURE IF EXISTS `' .
229			$row['Procedure'] . '` */;' . PHP_EOL .
230			'/!40101 SET @saved_cs_client = @@character_set_client /;' . PHP_EOL .
231			'/!40101 SET character_set_client = ' . $this->option->default_character_set . ' /;' . PHP_EOL .
232			'DELIMITER ;;' . PHP_EOL .
233			$procedureStmt . ' ;;' . PHP_EOL .
234			'DELIMITER ;' . PHP_EOL .
235			'/!40101 SET character_set_client = @saved_cs_client /;' . PHP_EOL . PHP_EOL;
236			}
237
238			/**
239			* {@inheritDoc}
240			*/
241			public function createFunction(array $row): string
242			{
243			if (! isset($row['Create Function'])) {
244			throw new Exception('Error getting function code, unknown output. ' .
245			"Please check 'https://bugs.mysql.com/bug.php?id=14564'");
246			}
247
248			$functionStmt = $row['Create Function'];
249			$characterSetClient = $row['character_set_client'];
250			$collationConnection = $row['collation_connection'];
251			$sqlMode = $row['sql_mode'];
252
253			if ($this->option->skip_definer) {
254			if ($functionStmtReplaced = preg_replace(
255			'/^(CREATE)\s+(' . self::DEFINER_RE . ')?\s+(FUNCTION\s.*)$/s',
256			'\1 \3',
257			$functionStmt,
258			1
259			)) {
260			$functionStmt = $functionStmtReplaced;
261			}
262			}
263
264			return '/*!50003 DROP FUNCTION IF EXISTS `' .
265			$row['Function'] . '` */;' . PHP_EOL .
266			'/!40101 SET @saved_cs_client = @@character_set_client /;' . PHP_EOL .
267			'/!50003 SET @saved_cs_results = @@character_set_results / ;' . PHP_EOL .
268			'/!50003 SET @saved_col_connection = @@collation_connection / ;' . PHP_EOL .
269			'/!40101 SET character_set_client = ' . $characterSetClient . ' /;' . PHP_EOL .
270			'/!40101 SET character_set_results = ' . $characterSetClient . ' /;' . PHP_EOL .
271			'/!50003 SET collation_connection = ' . $collationConnection . ' / ;' . PHP_EOL .
272			'/!50003 SET @saved_sql_mode = @@sql_mode / ;;' . PHP_EOL .
273			"/!50003 SET sql_mode = '" . $sqlMode . "' / ;;" . PHP_EOL .
274			'/!50003 SET @saved_time_zone = @@time_zone / ;;' . PHP_EOL .
275			"/!50003 SET time_zone = 'SYSTEM' / ;;" . PHP_EOL .
276			'DELIMITER ;;' . PHP_EOL .
277			$functionStmt . ' ;;' . PHP_EOL .
278			'DELIMITER ;' . PHP_EOL .
279			'/!50003 SET sql_mode = @saved_sql_mode / ;' . PHP_EOL .
280			'/!50003 SET character_set_client = @saved_cs_client / ;' . PHP_EOL .
281			'/!50003 SET character_set_results = @saved_cs_results / ;' . PHP_EOL .
282			'/!50003 SET collation_connection = @saved_col_connection / ;' . PHP_EOL .
283			'/!50106 SET TIME_ZONE= @saved_time_zone / ;' . PHP_EOL . PHP_EOL;
284			}
285
286			/**
287			* {@inheritDoc}
288			*/
289			public function createEvent(array $row): string
290			{
291			if (! isset($row['Create Event'])) {
292			throw new Exception('Error getting event code, unknown output. ' .
293			"Please check 'http://stackoverflow.com/questions/10853826/mysql-5-5-create-event-gives-syntax-error'");
294			}
295
296			$eventName = $row['Event'];
297			$eventStmt = $row['Create Event'];
298			$sqlMode = $row['sql_mode'];
299			$definerStr = $this->option->skip_definer ? '' : '/!50117 \2/ ';
300
301			if ($eventStmtReplaced = preg_replace(
302			'/^(CREATE)\s+(' . self::DEFINER_RE . ')?\s+(EVENT\s.*)$/s',
303			'/!50106 \1/ ' . $definerStr . '/!50106 \3 /',
304			$eventStmt,
305			1
306			)) {
307			$eventStmt = $eventStmtReplaced;
308			}
309
310			return '/!50106 SET @save_time_zone= @@TIME_ZONE / ;' . PHP_EOL .
311			'/!50106 DROP EVENT IF EXISTS `' . $eventName . '` /;' . PHP_EOL .
312			'DELIMITER ;;' . PHP_EOL .
313			'/!50003 SET @saved_cs_client = @@character_set_client / ;;' . PHP_EOL .
314			'/!50003 SET @saved_cs_results = @@character_set_results / ;;' . PHP_EOL .
315			'/!50003 SET @saved_col_connection = @@collation_connection / ;;' . PHP_EOL .
316			'/!50003 SET character_set_client = utf8 / ;;' . PHP_EOL .
317			'/!50003 SET character_set_results = utf8 / ;;' . PHP_EOL .
318			'/!50003 SET collation_connection = utf8_general_ci / ;;' . PHP_EOL .
319			'/!50003 SET @saved_sql_mode = @@sql_mode / ;;' . PHP_EOL .
320			"/!50003 SET sql_mode = '" . $sqlMode . "' / ;;" . PHP_EOL .
321			'/!50003 SET @saved_time_zone = @@time_zone / ;;' . PHP_EOL .
322			"/!50003 SET time_zone = 'SYSTEM' / ;;" . PHP_EOL .
323			$eventStmt . ' ;;' . PHP_EOL .
324			'/!50003 SET time_zone = @saved_time_zone / ;;' . PHP_EOL .
325			'/!50003 SET sql_mode = @saved_sql_mode / ;;' . PHP_EOL .
326			'/!50003 SET character_set_client = @saved_cs_client / ;;' . PHP_EOL .
327			'/!50003 SET character_set_results = @saved_cs_results / ;;' . PHP_EOL .
328			'/!50003 SET collation_connection = @saved_col_connection / ;;' . PHP_EOL .
329			'DELIMITER ;' . PHP_EOL .
330			'/!50106 SET TIME_ZONE= @save_time_zone / ;' . PHP_EOL . PHP_EOL;
331			// Commented because we are doing this in restore_parameters()
332			// "/!40103 SET TIME_ZONE=@OLD_TIME_ZONE /;" . PHP_EOL . PHP_EOL;
333			}
334
335			/**
336			* {@inheritDoc}
337			*/
338			public function showTables(string $database): string
339			{
340			return 'SELECT TABLE_NAME AS tbl_name ' .
341			'FROM INFORMATION_SCHEMA.TABLES ' .
342			"WHERE TABLE_TYPE='BASE TABLE' AND TABLE_SCHEMA='{$database}' " .
343			'ORDER BY TABLE_NAME';
344			}
345
346			/**
347			* {@inheritDoc}
348			*/
349			public function showViews(string $database): string
350			{
351			return 'SELECT TABLE_NAME AS tbl_name ' .
352			'FROM INFORMATION_SCHEMA.TABLES ' .
353			"WHERE TABLE_TYPE='VIEW' AND TABLE_SCHEMA='{$database}' " .
354			'ORDER BY TABLE_NAME';
355			}
356
357			/**
358			* {@inheritDoc}
359			*/
360			public function showTriggers(string $database): string
361			{
362			return "SHOW TRIGGERS FROM `{$database}`;";
363			}
364
365			/**
366			* {@inheritDoc}
367			*/
368			public function showColumns(string $table): string
369			{
370			return "SHOW COLUMNS FROM `{$table}`;";
371			}
372
373			/**
374			* {@inheritDoc}
375			*/
376			public function showProcedures(string $database): string
377			{
378			return 'SELECT SPECIFIC_NAME AS procedure_name ' .
379			'FROM INFORMATION_SCHEMA.ROUTINES ' .
380			"WHERE ROUTINE_TYPE='PROCEDURE' AND ROUTINE_SCHEMA='{$database}'";
381			}
382
383			/**
384			* {@inheritDoc}
385			*/
386			public function showFunctions(string $database): string
387			{
388			return 'SELECT SPECIFIC_NAME AS function_name ' .
389			'FROM INFORMATION_SCHEMA.ROUTINES ' .
390			"WHERE ROUTINE_TYPE='FUNCTION' AND ROUTINE_SCHEMA='{$database}'";
391			}
392
393			/**
394			* {@inheritDoc}
395			*/
396			public function showEvents(string $database): string
397			{
398			return 'SELECT EVENT_NAME AS event_name ' .
399			'FROM INFORMATION_SCHEMA.EVENTS ' .
400			"WHERE EVENT_SCHEMA='{$database}'";
401			}
402
403			/**
404			* {@inheritDoc}
405			*/
406			public function setupTransaction(): string
407			{
408			return 'SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ';
409			}
410
411			/**
412			* {@inheritDoc}
413			*/
414			public function startTransaction(): string
415			{
416			return 'START TRANSACTION ' .
417			'/!40100 WITH CONSISTENT SNAPSHOT /';
418			}
419
420			/**
421			* {@inheritDoc}
422			*/
423			public function commitTransaction(): string
424			{
425			return 'COMMIT';
426			}
427
428			/**
429			* {@inheritDoc}
430			*/
431			public function lockTable(string $table)
432			{
433			$this->checkParameters(func_num_args(), $expected_num_args = 1, __METHOD__);
434			$table = func_get_arg(0);
435
436			return $this->pdo->exec("LOCK TABLES `{$table}` READ LOCAL");
437			}
438
439			/**
440			* {@inheritDoc}
441			*/
442			public function unlockTable(string $table)
443			{
444			return $this->pdo->exec('UNLOCK TABLES');
445			}
446
447			/**
448			* {@inheritDoc}
449			*/
450			public function startAddLockTable(string $table): string
451			{
452			return "LOCK TABLES `{$table}` WRITE;" . PHP_EOL;
453			}
454
455			/**
456			* {@inheritDoc}
457			*/
458			public function endAddLockTable(string $table): string
459			{
460			return 'UNLOCK TABLES;' . PHP_EOL;
461			}
462
463			/**
464			* {@inheritDoc}
465			*/
466			public function startAddDisableKeys(string $table): string
467			{
468			return "/!40000 ALTER TABLE `{$table}` DISABLE KEYS /;" .
469			PHP_EOL;
470			}
471
472			/**
473			* {@inheritDoc}
474			*/
475			public function endAddDisableKeys(string $table): string
476			{
477			return "/!40000 ALTER TABLE `{$table}` ENABLE KEYS /;" .
478			PHP_EOL;
479			}
480
481			/**
482			* {@inheritDoc}
483			*/
484			public function startDisableForeignKeysCheck(): string
485			{
486			return 'SET foreign_key_checks = 0';
487			}
488
489			/**
490			* {@inheritDoc}
491			*/
492			public function endDisableForeignKeysCheck(): string
493			{
494			return 'SET foreign_key_checks = 1';
495			}
496
497			/**
498			* {@inheritDoc}
499			*/
500			public function startDisableAutocommit(): string
501			{
502			return 'SET autocommit=0;' . PHP_EOL;
503			}
504
505			/**
506			* {@inheritDoc}
507			*/
508			public function endDisableAutocommit(): string
509			{
510			return 'COMMIT;' . PHP_EOL;
511			}
512
513			/**
514			* {@inheritDoc}
515			*/
516			public function addDropDatabase(string $database): string
517			{
518			return "/!40000 DROP DATABASE IF EXISTS `{$database}`/;" .
519			PHP_EOL . PHP_EOL;
520			}
521
522			/**
523			* {@inheritDoc}
524			*/
525			public function addDropTrigger(string $trigger): string
526			{
527			return "DROP TRIGGER IF EXISTS `{$trigger}`;" . PHP_EOL;
528			}
529
530			/**
531			* {@inheritDoc}
532			*/
533			public function dropView(string $view): string
534			{
535			return "DROP TABLE IF EXISTS `{$view}`;" . PHP_EOL .
536			"/!50001 DROP VIEW IF EXISTS `{$view}`/;" . PHP_EOL;
537			}
538
539			/**
540			* {@inheritDoc}
541			*/
542			public function parseColumnType(array $colType): array
543			{
544			$colInfo = parent::_parseColumnType($colType, $this->mysqlTypes);
545			// for virtual columns that are of type 'Extra', column type
546			// could by "STORED GENERATED" or "VIRTUAL GENERATED"
547			// MySQL reference: https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html
548			$colInfo['is_virtual'] = str_contains($colType['Extra'], 'VIRTUAL GENERATED') \|\| str_contains($colType['Extra'], 'STORED GENERATED');
549
550			return $colInfo;
551			}
552
553			/**
554			* {@inheritDoc}
555			*/
556			public function backupParameters(): string
557			{
558			$ret = '/!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT /;' . PHP_EOL .
559			'/!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS /;' . PHP_EOL .
560			'/!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION /;' . PHP_EOL .
561			'/!40101 SET NAMES ' . $this->option->default_character_set . ' /;' . PHP_EOL;
562
563			if (false === $this->option->skip_tz_utc) {
564			$ret .= '/!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE /;' . PHP_EOL .
565			"/!40103 SET TIME_ZONE='+00:00' /;" . PHP_EOL;
566			}
567
568			if ($this->option->no_autocommit) {
569			$ret .= '/!40101 SET @OLD_AUTOCOMMIT=@@AUTOCOMMIT /;' . PHP_EOL;
570			}
571
572			$ret .= '/!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 /;' . PHP_EOL .
573			'/!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 /;' . PHP_EOL .
574			"/!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' /;" . PHP_EOL .
575			'/!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 /;' . PHP_EOL . PHP_EOL;
576
577			return $ret;
578			}
579
580			/**
581			* {@inheritDoc}
582			*/
583			public function restoreParameters(): string
584			{
585			$ret = '';
586
587			if (false === $this->option->skip_tz_utc) {
588			$ret .= '/!40103 SET TIME_ZONE=@OLD_TIME_ZONE /;' . PHP_EOL;
589			}
590
591			if ($this->option->no_autocommit) {
592			$ret .= '/!40101 SET AUTOCOMMIT=@OLD_AUTOCOMMIT /;' . PHP_EOL;
593			}
594
595			$ret .= '/!40101 SET SQL_MODE=@OLD_SQL_MODE /;' . PHP_EOL .
596			'/!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS /;' . PHP_EOL .
597			'/!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS /;' . PHP_EOL .
598			'/!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT /;' . PHP_EOL .
599			'/!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS /;' . PHP_EOL .
600			'/!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION /;' . PHP_EOL .
601			'/!40111 SET SQL_NOTES=@OLD_SQL_NOTES /;' . PHP_EOL . PHP_EOL;
602
603			return $ret;
604			}
605			}
606

dimtrovich / php-db-dumper

MysqlAdapter::showColumns() A last analyzed 2025-02-05 09:03 UTC

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like

MysqlAdapter::showColumns() A
last analyzed 2025-02-05 09:03 UTC