DataRestorer::__construct() - Code Metrics - Ecodev/felix - Measure and Improve Code Quality continuously with Scrutinizer

DataRestorer::__construct() A
last analyzed 2025-07-08 10:28 UTC

↳ Parent: DataRestorer

Complexity

Conditions	1
Paths	1

Size

Total Lines	7
Code Lines	0

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	0
CRAP Score	2

Importance

Changes	1
Bugs	0	Features	0

Metric	Value
eloc	0
c	1
b	0
f	0
dl	0
loc	7
rs	10
ccs	0
cts	2
cp	0
cc	1
nc	1
nop	5
crap	2

<?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 `$this->databaseToRestore`.`$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'], null);
        }

        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 = <<<STRING
            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
            STRING;

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

Ecodev / felix

DataRestorer::__construct() A last analyzed 2025-07-08 10:28 UTC

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like

DataRestorer::__construct() A
last analyzed 2025-07-08 10:28 UTC