|
1
|
|
|
<?php |
|
2
|
|
|
declare(strict_types=1); |
|
3
|
|
|
|
|
4
|
|
|
namespace Maba\DatabaseInconsistencyFinder\Database; |
|
5
|
|
|
|
|
6
|
|
|
use Doctrine\DBAL\FetchMode; |
|
7
|
|
|
use Maba\DatabaseInconsistencyFinder\Entity\Interval; |
|
8
|
|
|
use Maba\DatabaseInconsistencyFinder\Entity\ReferencedColumn; |
|
9
|
|
|
use Maba\DatabaseInconsistencyFinder\Entity\TableReferences; |
|
10
|
|
|
|
|
11
|
|
|
class QueryExecutor |
|
12
|
|
|
{ |
|
13
|
8 |
|
public function calculateHashForInterval( |
|
14
|
|
|
ReferencedColumn $referencedColumn, |
|
15
|
|
|
Interval $interval |
|
16
|
|
|
): int { |
|
17
|
8 |
|
return (int)$referencedColumn->getConnection()->fetchColumn( |
|
18
|
8 |
|
strtr( |
|
19
|
8 |
|
' |
|
20
|
|
|
SELECT SUM(CRC32(:idColumn) * :countColumn) |
|
21
|
|
|
FROM :table |
|
22
|
|
|
WHERE :intervalQuery |
|
23
|
|
|
', |
|
24
|
|
|
[ |
|
25
|
8 |
|
':table' => $referencedColumn->getTableName(), |
|
26
|
8 |
|
':idColumn' => $referencedColumn->getIdColumnName(), |
|
27
|
8 |
|
':countColumn' => $referencedColumn->getReferenceNumberColumnName(), |
|
28
|
8 |
|
':intervalQuery' => $this->prepareIntervalQuery($interval, $referencedColumn->getIdColumnName()), |
|
29
|
|
|
] |
|
30
|
|
|
), |
|
31
|
|
|
[ |
|
32
|
8 |
|
':idLargerThan' => $interval->getFrom(), |
|
33
|
8 |
|
':idUntil' => $interval->getUntil(), |
|
34
|
|
|
] |
|
35
|
|
|
); |
|
36
|
|
|
} |
|
37
|
|
|
|
|
38
|
8 |
|
public function calculateHashInRelatedTablesForInterval( |
|
39
|
|
|
TableReferences $tableReferences, |
|
40
|
|
|
Interval $interval |
|
41
|
|
|
): int { |
|
42
|
8 |
|
$connection = $tableReferences->getConnection(); |
|
43
|
8 |
|
$total = 0; |
|
44
|
8 |
|
foreach ($tableReferences->getColumnNames() as $columnName) { |
|
45
|
8 |
|
$total += (int)$connection->fetchColumn( |
|
46
|
8 |
|
strtr( |
|
47
|
8 |
|
' |
|
48
|
|
|
SELECT SUM(CRC32(:idColumn)) |
|
49
|
|
|
FROM :table |
|
50
|
|
|
WHERE :intervalQuery |
|
51
|
|
|
', |
|
52
|
|
|
[ |
|
53
|
8 |
|
':table' => $tableReferences->getTableName(), |
|
54
|
8 |
|
':idColumn' => $columnName, |
|
55
|
8 |
|
':intervalQuery' => $this->prepareIntervalQuery($interval, $columnName), |
|
56
|
|
|
] |
|
57
|
|
|
), |
|
58
|
|
|
[ |
|
59
|
8 |
|
':idLargerThan' => $interval->getFrom(), |
|
60
|
8 |
|
':idUntil' => $interval->getUntil(), |
|
61
|
|
|
] |
|
62
|
|
|
); |
|
63
|
|
|
} |
|
64
|
|
|
|
|
65
|
8 |
|
return $total; |
|
66
|
|
|
} |
|
67
|
|
|
|
|
68
|
7 |
|
public function findAllReferencedByInterval(ReferencedColumn $referencedColumn, Interval $interval): array |
|
69
|
|
|
{ |
|
70
|
7 |
|
$statement = $referencedColumn->getConnection()->executeQuery( |
|
71
|
7 |
|
strtr( |
|
72
|
7 |
|
' |
|
73
|
|
|
SELECT :idColumn, :countColumn |
|
74
|
|
|
FROM :table |
|
75
|
|
|
WHERE :intervalQuery |
|
76
|
|
|
', |
|
77
|
|
|
[ |
|
78
|
7 |
|
':table' => $referencedColumn->getTableName(), |
|
79
|
7 |
|
':idColumn' => $referencedColumn->getIdColumnName(), |
|
80
|
7 |
|
':countColumn' => $referencedColumn->getReferenceNumberColumnName(), |
|
81
|
7 |
|
':intervalQuery' => $this->prepareIntervalQuery($interval, $referencedColumn->getIdColumnName()), |
|
82
|
|
|
] |
|
83
|
|
|
), |
|
84
|
|
|
[ |
|
85
|
7 |
|
':idLargerThan' => $interval->getFrom(), |
|
86
|
7 |
|
':idUntil' => $interval->getUntil(), |
|
87
|
|
|
] |
|
88
|
|
|
); |
|
89
|
|
|
|
|
90
|
7 |
|
$result = []; |
|
91
|
7 |
View Code Duplication |
while (true) { |
|
|
|
|
|
|
92
|
7 |
|
$row = $statement->fetch(FetchMode::NUMERIC); |
|
93
|
7 |
|
if ($row === false) { |
|
94
|
7 |
|
break; |
|
95
|
|
|
} |
|
96
|
7 |
|
$result[$row[0]] = (int)$row[1]; |
|
97
|
|
|
} |
|
98
|
|
|
|
|
99
|
7 |
|
return $result; |
|
100
|
|
|
} |
|
101
|
|
|
|
|
102
|
|
|
/** |
|
103
|
|
|
* @param array|TableReferences[] $tableReferencesList |
|
104
|
|
|
* @param Interval $interval |
|
105
|
|
|
* @return array |
|
106
|
|
|
*/ |
|
107
|
7 |
|
public function aggregateReferencesByInterval(array $tableReferencesList, Interval $interval): array |
|
108
|
|
|
{ |
|
109
|
7 |
|
$result = []; |
|
110
|
7 |
|
foreach ($tableReferencesList as $tableReferences) { |
|
111
|
7 |
|
$connection = $tableReferences->getConnection(); |
|
112
|
7 |
|
foreach ($tableReferences->getColumnNames() as $columnName) { |
|
113
|
7 |
|
$statement = $connection->executeQuery( |
|
114
|
7 |
|
strtr( |
|
115
|
7 |
|
' |
|
116
|
|
|
SELECT :idColumn |
|
117
|
|
|
FROM :table |
|
118
|
|
|
WHERE :intervalQuery |
|
119
|
|
|
', |
|
120
|
|
|
[ |
|
121
|
7 |
|
':table' => $tableReferences->getTableName(), |
|
122
|
7 |
|
':idColumn' => $columnName, |
|
123
|
7 |
|
':intervalQuery' => $this->prepareIntervalQuery($interval, $columnName), |
|
124
|
|
|
] |
|
125
|
|
|
), |
|
126
|
|
|
[ |
|
127
|
7 |
|
':idLargerThan' => $interval->getFrom(), |
|
128
|
7 |
|
':idUntil' => $interval->getUntil(), |
|
129
|
|
|
] |
|
130
|
|
|
); |
|
131
|
7 |
View Code Duplication |
while (true) { |
|
|
|
|
|
|
132
|
7 |
|
$id = $statement->fetch(FetchMode::COLUMN); |
|
133
|
7 |
|
if ($id === false) { |
|
134
|
7 |
|
break; |
|
135
|
|
|
} |
|
136
|
7 |
|
$result[$id] = ($result[$id] ?? 0) + 1; |
|
137
|
|
|
} |
|
138
|
|
|
} |
|
139
|
|
|
} |
|
140
|
|
|
|
|
141
|
7 |
|
return $result; |
|
142
|
|
|
} |
|
143
|
|
|
|
|
144
|
12 |
|
private function prepareIntervalQuery(Interval $interval, string $idColumn): string |
|
145
|
|
|
{ |
|
146
|
12 |
|
$parts = []; |
|
147
|
12 |
|
if ($interval->getFrom() !== null) { |
|
148
|
6 |
|
$parts[] = sprintf('%s > :idLargerThan', $idColumn); |
|
149
|
|
|
} |
|
150
|
12 |
|
if ($interval->getUntil() !== null) { |
|
151
|
6 |
|
$parts[] = sprintf('%s <= :idUntil', $idColumn); |
|
152
|
|
|
} |
|
153
|
|
|
|
|
154
|
12 |
|
return count($parts) > 0 ? implode(' AND ', $parts) : 'TRUE'; |
|
155
|
|
|
} |
|
156
|
|
|
|
|
157
|
4 |
|
public function getIdRange(ReferencedColumn $referencedColumn): Interval |
|
158
|
|
|
{ |
|
159
|
4 |
|
$result = $referencedColumn->getConnection()->fetchArray( |
|
160
|
4 |
|
strtr( |
|
161
|
4 |
|
' |
|
162
|
|
|
SELECT MIN(:idColumn), MAX(:idColumn) |
|
163
|
|
|
FROM :table |
|
164
|
|
|
', |
|
165
|
|
|
[ |
|
166
|
4 |
|
':table' => $referencedColumn->getTableName(), |
|
167
|
4 |
|
':idColumn' => $referencedColumn->getIdColumnName(), |
|
168
|
|
|
] |
|
169
|
|
|
) |
|
170
|
|
|
); |
|
171
|
4 |
|
return (new Interval())->setFrom((int)$result[0])->setUntil((int)$result[1]); |
|
172
|
|
|
} |
|
173
|
|
|
} |
|
174
|
|
|
|
mariusbalcytis /
database-inconsistency-finder
Loading history...
Duplicated code is one of the most pungent code smells. If you need to duplicate the same code in three or more different places, we strongly encourage you to look into extracting the code into a single class or operation.
You can also find more detailed suggestions in the “Code” section of your repository.