DataRestorer::toCsv() - Code Metrics - Inspection of "Find relation tables based on existing tables #940..." - Ecodev/felix - Measure and Improve Code Quality continuously with Scrutinizer

Failed Conditions

Push — master ( 34a97b...ee645a )

by Adrien

created 2023-01-23 06:15 UTC

DataRestorer::toCsv() A

↳ Parent: DataRestorer

Complexity

Conditions	3
Paths	3

Size

Total Lines	17
Code Lines	10

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	0
CRAP Score	12

Importance

Changes	1
Bugs	0	Features	0

Metric	Value
eloc	10
c	1
b	0
f	0
dl	0
loc	17
ccs	0
cts	11
cp	0
rs	9.9332
cc	3
nc	3
nop	1
crap	12

<?php

declare(strict_types=1);

namespace Ecodev\Felix\Service;

use Doctrine\DBAL\Connection;
use Doctrine\ORM\Mapping\NamingStrategy;
use Exception;

class DataRestorer
{
    private const NULL_TOKEN = 'MY_SECRET_NULL_TOKEN';

    private array $restoreQueries = [];

    /**
     * @var array<string, array{table1: class-string, table2: class-string}>

     */
    private array $allRelationTables = [];

    public function __construct(
        private readonly Connection $connection,
        private readonly NamingStrategy $namingStrategy,
        private readonly string $databaseToRestore,
        private readonly string $tableToRestore,
        private readonly array $idsToRestore,
    ) {
    }

    /**
     * This will connect to the given backup database to generate the SQL queries necessary to restore the given data.
     * Those queries must then be run manually on production database.
     *
     * The restored data include:
     *
     * - the objects themselves (will generate `LOAD DATA`)
     * - the oneToMany relations that might have been set NULL (will generate `UPDATE`)
     * - the manyToMany relations that might have been deleted (will generate `LOAD DATA`)
     *
     * However, this is a **best effort** so we `IGNORE` failure when restoring things.
     * So **after a restore it is possible that foreign key don't match**, unfortunately, and
     * that some data and relations are not restored due to new data inserted after the backup
     * conflicting with restored data.
     */
    public function generateQueriesToRestoreDeletedData(): void
    {
        $this->restoreQueries = [];
        $this->fetchAllRelationTables();

        $this->restoreTableData();
        $this->restoreRelations();

        if (count($this->restoreQueries)) {
            $fileName = 'restore.sql';
            file_put_contents($fileName, implode(PHP_EOL, $this->restoreQueries));

            echo <<<STRING
                
                # TODO manually 
                
                1. Copy all `restore*` files to production server
                2. On production server, run a command similar to:

                mariadb < $fileName

                STRING;
        }
    }

    /**
     * @return array{0: int, 1: array<string>}
     */
    private function exportQueryToCsv(string $query, string $fileName): array
    {
        $result = $this->connection->executeQuery($query);

        $buffer = null;
        $firstRow = true;
        $count = 0;
        $columnNames = [];
        while ($row = $result->fetchAssociative()) {
            if ($firstRow) {
                $buffer = fopen($fileName, 'w+b');
                if ($buffer === false) {
                    throw new Exception('Cannot write to ' . $fileName);
                }
                $columnNames = array_keys($row);
                fputcsv($buffer, $columnNames);
                $firstRow = false;
            }

            foreach ($row as $k => $v) {
                if ($v === null) {
                    $row[$k] = self::NULL_TOKEN;
                }
            }

            if ($buffer) {
                $line = $this->toCsv($row);
                fwrite($buffer, $line);
            }

            ++$count;
        }

        if ($buffer) {
            fclose($buffer);
        }

        return [$count, $columnNames];
    }

    private function toCsv(array $fields): string
    {
        $fp = fopen('php://temp', 'r+b');
        if ($fp === false) {
            throw new Exception('Cannot write in memory');
        }

        fputcsv($fp, $fields);
        rewind($fp);
        $data = stream_get_contents($fp);
        if ($data === false) {
            throw new Exception('Cannot read from memory');
        }

        fclose($fp);

        return str_replace(self::NULL_TOKEN, 'NULL', $data);
    }

    /**
     *  Export the result for each table to individual CSV files.
     */
    private function restoreTableData(): void
    {
        $tableSelects = $this->getTablesToRestore();

        $this->restoreQueries[] = 'SET FOREIGN_KEY_CHECKS = 0;';

        foreach ($tableSelects as $t => $queries) {
            foreach ($queries as $i => $query) {
                $fileName = 'restore-' . $t . '-' . $i . '.csv';
                [$count, $columnNames] = $this->exportQueryToCsv($query, $fileName);

                if ($count) {
                    $columns = implode(', ', array_map(fn ($name) => $this->connection->quoteIdentifier($name), $columnNames));
                    $this->restoreQueries[] = <<<STRING
                        LOAD DATA LOCAL INFILE '$fileName' INTO TABLE `$t` FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"' IGNORE 1 LINES ($columns);
                        STRING;
                    echo $count . ' records exported in ' . $fileName . PHP_EOL;
                }
            }
        }

        $this->restoreQueries[] = 'SET FOREIGN_KEY_CHECKS = 1;';
    }

    private function fetchAllRelationTables(): void
    {
        $this->allRelationTables = $this->connection->fetchAllAssociativeIndexed(
            <<<SQL
                SELECT 
                    CONCAT(t1.TABLE_NAME, '_', t2.TABLE_NAME) AS relation, 
                    t1.TABLE_NAME AS table1,
                    t2.TABLE_NAME AS table2
                FROM information_schema.TABLES AS t1
                CROSS JOIN information_schema.TABLES AS t2
                WHERE t1.TABLE_SCHEMA = '$this->databaseToRestore' AND t2.TABLE_SCHEMA = '$this->databaseToRestore'
                HAVING relation IN (SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = '$this->databaseToRestore')
                SQL
        );
    }

    private function restoreRelations(): void
    {
        // Generate UPDATE queries to recover the values that were erased by the SET NULL FK constraint
        /** @var array<array<string, string>> $foreignKeys */
        $foreignKeys = $this->connection->fetchAllAssociative(
            "SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE CONSTRAINT_SCHEMA = '$this->databaseToRestore' AND REFERENCED_TABLE_NAME = '$this->tableToRestore';"
        );

        foreach ($foreignKeys as $foreignKey) {
            foreach ($this->idsToRestore as $id) {
                $tableName = $foreignKey['TABLE_NAME'];
                $columnName = $foreignKey['COLUMN_NAME'];
                $primaryKey = $this->gePrimaryKey($tableName, $columnName);

                $query = <<<SQL
                    SELECT CONCAT("UPDATE IGNORE `$tableName` SET `$columnName` = $id WHERE `$primaryKey` IN (",GROUP_CONCAT(DISTINCT $primaryKey SEPARATOR ','),");")
                    FROM `$tableName`
                    WHERE $columnName = $id
                    GROUP BY $columnName;
                    SQL;
                $result = $this->connection->fetchOne($query);
                if ($result) {
                    $this->restoreQueries[] = $result;
                }
            }
        }
    }

    private function gePrimaryKey(string $tableName, string $columnName): string
    {
        $relation = $this->allRelationTables[$tableName] ?? null;
        if ($relation) {
            if (preg_match('/^(source|target)(.+)$/', $columnName, $m)) {
                // N-N relationship between 2 objects of the same type (ex: `document_document`)
                return ($m[1] === 'source') ? 'target' . $m[2] : 'source' . $m[2];
            }

            // normal N-N relationship
            return $this->namingStrategy->joinKeyColumnName($relation['table1']);
        }

        return 'id';
    }

    /**
     * @return array<string, array<string>>
     */
    private function getTablesToRestore(): array
    {
        $tableSelects = [];

        // Query to export the main deleted records
        $tableSelects[$this->tableToRestore][] = "SELECT * FROM `$this->databaseToRestore`.`$this->tableToRestore` WHERE id IN (" . implode(',', $this->idsToRestore) . ')';

        // Queries to export the records in other tables that were deleted via the CASCADE FK constraint
        $foreignKeysQuery = <<<EOH
            SELECT DISTINCT(u.TABLE_NAME),u.COLUMN_NAME
            FROM information_schema.KEY_COLUMN_USAGE as u INNER JOIN information_schema.REFERENTIAL_CONSTRAINTS as c
            WHERE c.CONSTRAINT_SCHEMA='$this->databaseToRestore' AND c.REFERENCED_TABLE_NAME='$this->tableToRestore' AND c.DELETE_RULE='CASCADE' AND c.CONSTRAINT_NAME=u.CONSTRAINT_NAME
            EOH;

        /** @var array<array<string, string>> $foreignKeys */
        $foreignKeys = $this->connection->fetchAllAssociative($foreignKeysQuery);

        foreach ($foreignKeys as $foreignKey) {
            $tableSelects[$foreignKey['TABLE_NAME']][] = "SELECT * FROM `$this->databaseToRestore`.`${foreignKey['TABLE_NAME']}` WHERE ${foreignKey['COLUMN_NAME']} IN (" . implode(',', $this->idsToRestore) . ')';
        }

        return $tableSelects;
    }
}


1			<?php
2
3			declare(strict_types=1);
4
5			namespace Ecodev\Felix\Service;
6
7			use Doctrine\DBAL\Connection;
8			use Doctrine\ORM\Mapping\NamingStrategy;
9			use Exception;
10
11			class DataRestorer
12			{
13			private const NULL_TOKEN = 'MY_SECRET_NULL_TOKEN';
14
15			private array $restoreQueries = [];
16
17			/**
18			* @var array<string, array{table1: class-string, table2: class-string}>
			1 ignored issue – show Documentation Bug introduced 2023-01-23 06:17 UTC by Report Bug Copy Issue Report The doc comment `array<string, array{tabl... table2: class-string}>` at position `8` could not be parsed: Unknown type name 'class-string' at position 8 in array<string, array{table1: class-string, table2: class-string}>. Loading history...
19			*/
20			private array $allRelationTables = [];
21
22			public function __construct(
23			private readonly Connection $connection,
24			private readonly NamingStrategy $namingStrategy,
25			private readonly string $databaseToRestore,
26			private readonly string $tableToRestore,
27			private readonly array $idsToRestore,
28			) {
29			}
30
31			/**
32			* This will connect to the given backup database to generate the SQL queries necessary to restore the given data.
33			* Those queries must then be run manually on production database.
34			*
35			* The restored data include:
36			*
37			* - the objects themselves (will generate `LOAD DATA`)
38			* - the oneToMany relations that might have been set NULL (will generate `UPDATE`)
39			* - the manyToMany relations that might have been deleted (will generate `LOAD DATA`)
40			*
41			* However, this is a best effort so we `IGNORE` failure when restoring things.
42			* So after a restore it is possible that foreign key don't match, unfortunately, and
43			* that some data and relations are not restored due to new data inserted after the backup
44			* conflicting with restored data.
45			*/
46			public function generateQueriesToRestoreDeletedData(): void
47			{
48			$this->restoreQueries = [];
49			$this->fetchAllRelationTables();
50
51			$this->restoreTableData();
52			$this->restoreRelations();
53
54			if (count($this->restoreQueries)) {
55			$fileName = 'restore.sql';
56			file_put_contents($fileName, implode(PHP_EOL, $this->restoreQueries));
57
58			echo <<<STRING
59
60			# TODO manually
61
62			1. Copy all `restore*` files to production server
63			2. On production server, run a command similar to:
64
65			mariadb < $fileName
66
67			STRING;
68			}
69			}
70
71			/**
72			* @return array{0: int, 1: array<string>}
73			*/
74			private function exportQueryToCsv(string $query, string $fileName): array
75			{
76			$result = $this->connection->executeQuery($query);
77
78			$buffer = null;
79			$firstRow = true;
80			$count = 0;
81			$columnNames = [];
82			while ($row = $result->fetchAssociative()) {
83			if ($firstRow) {
84			$buffer = fopen($fileName, 'w+b');
85			if ($buffer === false) {
86			throw new Exception('Cannot write to ' . $fileName);
87			}
88			$columnNames = array_keys($row);
89			fputcsv($buffer, $columnNames);
90			$firstRow = false;
91			}
92
93			foreach ($row as $k => $v) {
94			if ($v === null) {
95			$row[$k] = self::NULL_TOKEN;
96			}
97			}
98
99			if ($buffer) {
100			$line = $this->toCsv($row);
101			fwrite($buffer, $line);
102			}
103
104			++$count;
105			}
106
107			if ($buffer) {
108			fclose($buffer);
109			}
110
111			return [$count, $columnNames];
112			}
113
114			private function toCsv(array $fields): string
115			{
116			$fp = fopen('php://temp', 'r+b');
117			if ($fp === false) {
118			throw new Exception('Cannot write in memory');
119			}
120
121			fputcsv($fp, $fields);
122			rewind($fp);
123			$data = stream_get_contents($fp);
124			if ($data === false) {
125			throw new Exception('Cannot read from memory');
126			}
127
128			fclose($fp);
129
130			return str_replace(self::NULL_TOKEN, 'NULL', $data);
131			}
132
133			/**
134			* Export the result for each table to individual CSV files.
135			*/
136			private function restoreTableData(): void
137			{
138			$tableSelects = $this->getTablesToRestore();
139
140			$this->restoreQueries[] = 'SET FOREIGN_KEY_CHECKS = 0;';
141
142			foreach ($tableSelects as $t => $queries) {
143			foreach ($queries as $i => $query) {
144			$fileName = 'restore-' . $t . '-' . $i . '.csv';
145			[$count, $columnNames] = $this->exportQueryToCsv($query, $fileName);
146
147			if ($count) {
148			$columns = implode(', ', array_map(fn ($name) => $this->connection->quoteIdentifier($name), $columnNames));
149			$this->restoreQueries[] = <<<STRING
150			LOAD DATA LOCAL INFILE '$fileName' INTO TABLE `$t` FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"' IGNORE 1 LINES ($columns);
151			STRING;
152			echo $count . ' records exported in ' . $fileName . PHP_EOL;
153			}
154			}
155			}
156
157			$this->restoreQueries[] = 'SET FOREIGN_KEY_CHECKS = 1;';
158			}
159
160			private function fetchAllRelationTables(): void
161			{
162			$this->allRelationTables = $this->connection->fetchAllAssociativeIndexed(
163			<<<SQL
164			SELECT
165			CONCAT(t1.TABLE_NAME, '_', t2.TABLE_NAME) AS relation,
166			t1.TABLE_NAME AS table1,
167			t2.TABLE_NAME AS table2
168			FROM information_schema.TABLES AS t1
169			CROSS JOIN information_schema.TABLES AS t2
170			WHERE t1.TABLE_SCHEMA = '$this->databaseToRestore' AND t2.TABLE_SCHEMA = '$this->databaseToRestore'
171			HAVING relation IN (SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = '$this->databaseToRestore')
172			SQL
173			);
174			}
175
176			private function restoreRelations(): void
177			{
178			// Generate UPDATE queries to recover the values that were erased by the SET NULL FK constraint
179			/** @var array<array<string, string>> $foreignKeys */
180			$foreignKeys = $this->connection->fetchAllAssociative(
181			"SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE CONSTRAINT_SCHEMA = '$this->databaseToRestore' AND REFERENCED_TABLE_NAME = '$this->tableToRestore';"
182			);
183
184			foreach ($foreignKeys as $foreignKey) {
185			foreach ($this->idsToRestore as $id) {
186			$tableName = $foreignKey['TABLE_NAME'];
187			$columnName = $foreignKey['COLUMN_NAME'];
188			$primaryKey = $this->gePrimaryKey($tableName, $columnName);
189
190			$query = <<<SQL
191			SELECT CONCAT("UPDATE IGNORE `$tableName` SET `$columnName` = $id WHERE `$primaryKey` IN (",GROUP_CONCAT(DISTINCT $primaryKey SEPARATOR ','),");")
192			FROM `$tableName`
193			WHERE $columnName = $id
194			GROUP BY $columnName;
195			SQL;
196			$result = $this->connection->fetchOne($query);
197			if ($result) {
198			$this->restoreQueries[] = $result;
199			}
200			}
201			}
202			}
203
204			private function gePrimaryKey(string $tableName, string $columnName): string
205			{
206			$relation = $this->allRelationTables[$tableName] ?? null;
207			if ($relation) {
208			if (preg_match('/^(source\|target)(.+)$/', $columnName, $m)) {
209			// N-N relationship between 2 objects of the same type (ex: `document_document`)
210			return ($m[1] === 'source') ? 'target' . $m[2] : 'source' . $m[2];
211			}
212
213			// normal N-N relationship
214			return $this->namingStrategy->joinKeyColumnName($relation['table1']);
215			}
216
217			return 'id';
218			}
219
220			/**
221			* @return array<string, array<string>>
222			*/
223			private function getTablesToRestore(): array
224			{
225			$tableSelects = [];
226
227			// Query to export the main deleted records
228			$tableSelects[$this->tableToRestore][] = "SELECT * FROM `$this->databaseToRestore`.`$this->tableToRestore` WHERE id IN (" . implode(',', $this->idsToRestore) . ')';
229
230			// Queries to export the records in other tables that were deleted via the CASCADE FK constraint
231			$foreignKeysQuery = <<<EOH
232			SELECT DISTINCT(u.TABLE_NAME),u.COLUMN_NAME
233			FROM information_schema.KEY_COLUMN_USAGE as u INNER JOIN information_schema.REFERENTIAL_CONSTRAINTS as c
234			WHERE c.CONSTRAINT_SCHEMA='$this->databaseToRestore' AND c.REFERENCED_TABLE_NAME='$this->tableToRestore' AND c.DELETE_RULE='CASCADE' AND c.CONSTRAINT_NAME=u.CONSTRAINT_NAME
235			EOH;
236
237			/** @var array<array<string, string>> $foreignKeys */
238			$foreignKeys = $this->connection->fetchAllAssociative($foreignKeysQuery);
239
240			foreach ($foreignKeys as $foreignKey) {
241			$tableSelects[$foreignKey['TABLE_NAME']][] = "SELECT * FROM `$this->databaseToRestore`.`${foreignKey['TABLE_NAME']}` WHERE ${foreignKey['COLUMN_NAME']} IN (" . implode(',', $this->idsToRestore) . ')';
242			}
243
244			return $tableSelects;
245			}
246			}
247

Ecodev / felix

Push — master ( 34a97b...ee645a )

DataRestorer::toCsv() A

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like