|
1
|
|
|
<?php |
|
2
|
|
|
|
|
3
|
|
|
namespace N98\Magento\Command\Database\Maintain; |
|
4
|
|
|
|
|
5
|
|
|
use InvalidArgumentException; |
|
6
|
|
|
use N98\Magento\Command\AbstractMagentoCommand; |
|
7
|
|
|
use N98\Util\Console\Helper\Table\Renderer\RendererFactory; |
|
8
|
|
|
use Symfony\Component\Console\Input\InputInterface; |
|
9
|
|
|
use Symfony\Component\Console\Input\InputOption; |
|
10
|
|
|
use Symfony\Component\Console\Output\OutputInterface; |
|
11
|
|
|
|
|
12
|
|
|
class CheckTablesCommand extends AbstractMagentoCommand |
|
13
|
|
|
{ |
|
14
|
|
|
const MESSAGE_CHECK_NOT_SUPPORTED = 'The storage engine for the table doesn\'t support check'; |
|
15
|
|
|
const MESSAGE_REPAIR_NOT_SUPPORTED = 'The storage engine for the table doesn\'t support repair'; |
|
16
|
|
|
|
|
17
|
|
|
/** |
|
18
|
|
|
* @var InputInterface |
|
19
|
|
|
*/ |
|
20
|
|
|
protected $input = null; |
|
21
|
|
|
|
|
22
|
|
|
/** |
|
23
|
|
|
* @var OutputInterface |
|
24
|
|
|
*/ |
|
25
|
|
|
protected $output = null; |
|
26
|
|
|
|
|
27
|
|
|
/** |
|
28
|
|
|
* @var \N98\Util\Console\Helper\DatabaseHelper |
|
29
|
|
|
*/ |
|
30
|
|
|
protected $dbHelper = null; |
|
31
|
|
|
|
|
32
|
|
|
/** |
|
33
|
|
|
* @var bool |
|
34
|
|
|
*/ |
|
35
|
|
|
protected $showProgress = false; |
|
36
|
|
|
|
|
37
|
|
|
/** |
|
38
|
|
|
* @var array |
|
39
|
|
|
*/ |
|
40
|
|
|
protected $allowedTypes = array( |
|
41
|
|
|
'QUICK', |
|
42
|
|
|
'FAST', |
|
43
|
|
|
'CHANGED', |
|
44
|
|
|
'MEDIUM', |
|
45
|
|
|
'EXTENDED', |
|
46
|
|
|
); |
|
47
|
|
|
|
|
48
|
|
View Code Duplication |
protected function configure() |
|
|
|
|
|
|
49
|
|
|
{ |
|
50
|
|
|
$help = <<<'HELP' |
|
51
|
|
|
<comment>TYPE OPTIONS</comment> |
|
52
|
|
|
|
|
53
|
|
|
<info>QUICK</info> |
|
54
|
|
|
Do not scan the rows to check for incorrect links. |
|
55
|
|
|
Applies to InnoDB and MyISAM tables and views. |
|
56
|
|
|
<info>FAST</info> |
|
57
|
|
|
Check only tables that have not been closed properly. |
|
58
|
|
|
Applies only to MyISAM tables and views; ignored for InnoDB. |
|
59
|
|
|
<info>CHANGED</info> |
|
60
|
|
|
Check only tables that have been changed since the last check or that |
|
61
|
|
|
have not been closed properly. Applies only to MyISAM tables and views; |
|
62
|
|
|
ignored for InnoDB. |
|
63
|
|
|
<info>MEDIUM</info> |
|
64
|
|
|
Scan rows to verify that deleted links are valid. |
|
65
|
|
|
This also calculates a key checksum for the rows and verifies this with a |
|
66
|
|
|
calculated checksum for the keys. Applies only to MyISAM tables and views; |
|
67
|
|
|
ignored for InnoDB. |
|
68
|
|
|
<info>EXTENDED</info> |
|
69
|
|
|
Do a full key lookup for all keys for each row. This ensures that the table |
|
70
|
|
|
is 100% consistent, but takes a long time. |
|
71
|
|
|
Applies only to MyISAM tables and views; ignored for InnoDB. |
|
72
|
|
|
|
|
73
|
|
|
<comment>InnoDB</comment> |
|
74
|
|
|
InnoDB tables will be optimized with the ALTER TABLE ... ENGINE=InnoDB statement. |
|
75
|
|
|
The options above do not apply to them. |
|
76
|
|
|
HELP; |
|
77
|
|
|
|
|
78
|
|
|
$this |
|
79
|
|
|
->setName('db:maintain:check-tables') |
|
80
|
|
|
->setDescription('Check database tables') |
|
81
|
|
|
->addOption( |
|
82
|
|
|
'type', |
|
83
|
|
|
null, |
|
84
|
|
|
InputOption::VALUE_OPTIONAL, |
|
85
|
|
|
'Check type (one of QUICK, FAST, MEDIUM, EXTENDED, CHANGED)', |
|
86
|
|
|
'MEDIUM' |
|
87
|
|
|
) |
|
88
|
|
|
->addOption('repair', null, InputOption::VALUE_NONE, 'Repair tables (only MyISAM)') |
|
89
|
|
|
->addOption( |
|
90
|
|
|
'table', |
|
91
|
|
|
null, |
|
92
|
|
|
InputOption::VALUE_OPTIONAL, |
|
93
|
|
|
'Process only given table (wildcards are supported)' |
|
94
|
|
|
) |
|
95
|
|
|
->addOption( |
|
96
|
|
|
'format', |
|
97
|
|
|
null, |
|
98
|
|
|
InputOption::VALUE_OPTIONAL, |
|
99
|
|
|
'Output Format. One of [' . implode(',', RendererFactory::getFormats()) . ']' |
|
100
|
|
|
) |
|
101
|
|
|
->setHelp($help); |
|
102
|
|
|
} |
|
103
|
|
|
|
|
104
|
|
|
/** |
|
105
|
|
|
* @throws InvalidArgumentException |
|
106
|
|
|
* |
|
107
|
|
|
*/ |
|
108
|
|
|
protected function isTypeAllowed() |
|
109
|
|
|
{ |
|
110
|
|
|
$type = $this->input->getOption('type'); |
|
111
|
|
|
$type = strtoupper($type); |
|
112
|
|
|
if ($type && !in_array($type, $this->allowedTypes)) { |
|
113
|
|
|
throw new InvalidArgumentException('Invalid type was given'); |
|
114
|
|
|
} |
|
115
|
|
|
} |
|
116
|
|
|
|
|
117
|
|
|
protected function progressAdvance() |
|
118
|
|
|
{ |
|
119
|
|
|
if ($this->showProgress) { |
|
120
|
|
|
$this->getHelper('progress')->advance(); |
|
121
|
|
|
} |
|
122
|
|
|
} |
|
123
|
|
|
|
|
124
|
|
|
/** |
|
125
|
|
|
* @param InputInterface $input |
|
126
|
|
|
* @param OutputInterface $output |
|
127
|
|
|
* |
|
128
|
|
|
* @throws InvalidArgumentException |
|
129
|
|
|
* @return int|void |
|
130
|
|
|
*/ |
|
131
|
|
|
protected function execute(InputInterface $input, OutputInterface $output) |
|
132
|
|
|
{ |
|
133
|
|
|
$this->input = $input; |
|
134
|
|
|
$this->output = $output; |
|
135
|
|
|
$this->isTypeAllowed(); |
|
136
|
|
|
$this->detectMagento($output); |
|
137
|
|
|
$this->dbHelper = $this->getHelper('database'); |
|
138
|
|
|
$this->showProgress = $input->getOption('format') == null; |
|
139
|
|
|
|
|
140
|
|
|
if ($input->getOption('table')) { |
|
141
|
|
|
$resolvedTables = array( |
|
142
|
|
|
$this->dbHelper->resolveTables( |
|
143
|
|
|
array('@check'), |
|
144
|
|
|
array( |
|
145
|
|
|
'check' => array( |
|
146
|
|
|
'tables' => $input->getOption('table') |
|
147
|
|
|
) |
|
148
|
|
|
) |
|
149
|
|
|
) |
|
150
|
|
|
); |
|
151
|
|
|
$tables = $resolvedTables[0]; |
|
152
|
|
|
} else { |
|
153
|
|
|
$tables = $this->dbHelper->getTables(); |
|
154
|
|
|
} |
|
155
|
|
|
|
|
156
|
|
|
$allTableStatus = $this->dbHelper->getTablesStatus(); |
|
157
|
|
|
|
|
158
|
|
|
$tableOutput = array(); |
|
159
|
|
|
/** @var \Symfony\Component\Console\Helper\ProgressHelper $progress */ |
|
160
|
|
|
$progress = $this->getHelper('progress'); |
|
161
|
|
|
if ($this->showProgress) { |
|
162
|
|
|
$progress->start($output, count($tables)); |
|
163
|
|
|
} |
|
164
|
|
|
|
|
165
|
|
|
$methods = array( |
|
166
|
|
|
'InnoDB' => 1, |
|
167
|
|
|
'MEMORY' => 1, |
|
168
|
|
|
'MyISAM' => 1, |
|
169
|
|
|
); |
|
170
|
|
|
|
|
171
|
|
|
foreach ($tables as $tableName) { |
|
172
|
|
|
if (isset($allTableStatus[$tableName]) && isset($methods[$allTableStatus[$tableName]['Engine']])) { |
|
173
|
|
|
$m = '_check' . $allTableStatus[$tableName]['Engine']; |
|
174
|
|
|
$tableOutput = array_merge($tableOutput, $this->$m($tableName)); |
|
175
|
|
|
} else { |
|
176
|
|
|
$tableOutput[] = array( |
|
177
|
|
|
'table' => $tableName, |
|
178
|
|
|
'operation' => 'not supported', |
|
179
|
|
|
'type' => '', |
|
180
|
|
|
'status' => '', |
|
181
|
|
|
); |
|
182
|
|
|
} |
|
183
|
|
|
$this->progressAdvance(); |
|
184
|
|
|
} |
|
185
|
|
|
|
|
186
|
|
|
if ($this->showProgress) { |
|
187
|
|
|
$progress->finish(); |
|
188
|
|
|
} |
|
189
|
|
|
|
|
190
|
|
|
$this->getHelper('table') |
|
191
|
|
|
->setHeaders(array('Table', 'Operation', 'Type', 'Status')) |
|
192
|
|
|
->renderByFormat($this->output, $tableOutput, $this->input->getOption('format')); |
|
193
|
|
|
} |
|
194
|
|
|
|
|
195
|
|
|
/** |
|
196
|
|
|
* @param string $tableName |
|
197
|
|
|
* @param string $engine |
|
198
|
|
|
* |
|
199
|
|
|
* @return array |
|
200
|
|
|
*/ |
|
201
|
|
|
protected function _queryAlterTable($tableName, $engine) |
|
202
|
|
|
{ |
|
203
|
|
|
/** @var \PDO $connection */ |
|
204
|
|
|
$connection = $this->dbHelper->getConnection($this->output); |
|
205
|
|
|
$start = microtime(true); |
|
206
|
|
|
$affectedRows = $connection->exec(sprintf('ALTER TABLE %s ENGINE=%s', $tableName, $engine)); |
|
207
|
|
|
|
|
208
|
|
|
return array( |
|
209
|
|
|
array( |
|
210
|
|
|
'table' => $tableName, |
|
211
|
|
|
'operation' => 'ENGINE ' . $engine, |
|
212
|
|
|
'type' => sprintf('%15s rows', (string)$affectedRows), |
|
213
|
|
|
'status' => sprintf('%.3f secs', microtime(true) - $start), |
|
214
|
|
|
) |
|
215
|
|
|
); |
|
216
|
|
|
} |
|
217
|
|
|
|
|
218
|
|
|
/** |
|
219
|
|
|
* @param string $tableName |
|
220
|
|
|
* |
|
221
|
|
|
* @return array |
|
222
|
|
|
*/ |
|
223
|
|
|
protected function _checkInnoDB($tableName) |
|
224
|
|
|
{ |
|
225
|
|
|
return $this->_queryAlterTable($tableName, 'InnoDB'); |
|
226
|
|
|
} |
|
227
|
|
|
|
|
228
|
|
|
/** |
|
229
|
|
|
* @param string $tableName |
|
230
|
|
|
* |
|
231
|
|
|
* @return array |
|
232
|
|
|
*/ |
|
233
|
|
|
protected function _checkMEMORY($tableName) |
|
234
|
|
|
{ |
|
235
|
|
|
return $this->_queryAlterTable($tableName, 'MEMORY'); |
|
236
|
|
|
} |
|
237
|
|
|
|
|
238
|
|
|
/** |
|
239
|
|
|
* @param string $tableName |
|
240
|
|
|
* |
|
241
|
|
|
* @return array |
|
242
|
|
|
*/ |
|
243
|
|
|
protected function _checkMyISAM($tableName) |
|
244
|
|
|
{ |
|
245
|
|
|
$table = array(); |
|
246
|
|
|
$type = $this->input->getOption('type'); |
|
247
|
|
|
$result = $this->_query(sprintf('CHECK TABLE %s %s', $tableName, $type)); |
|
248
|
|
|
if ($result['Msg_text'] == self::MESSAGE_CHECK_NOT_SUPPORTED) { |
|
249
|
|
|
return array(); |
|
250
|
|
|
} |
|
251
|
|
|
|
|
252
|
|
|
$table[] = array( |
|
253
|
|
|
'table' => $tableName, |
|
254
|
|
|
'operation' => $result['Op'], |
|
255
|
|
|
'type' => $type, |
|
256
|
|
|
'status' => $result['Msg_text'], |
|
257
|
|
|
); |
|
258
|
|
|
|
|
259
|
|
|
if ($result['Msg_text'] != 'OK' |
|
260
|
|
|
&& $this->input->getOption('repair') |
|
261
|
|
|
) { |
|
262
|
|
|
$result = $this->_query(sprintf('REPAIR TABLE %s %s', $tableName, $type)); |
|
263
|
|
|
if ($result['Msg_text'] != self::MESSAGE_REPAIR_NOT_SUPPORTED) { |
|
264
|
|
|
$table[] = array( |
|
265
|
|
|
'table' => $tableName, |
|
266
|
|
|
'operation' => $result['Op'], |
|
267
|
|
|
'type' => $type, |
|
268
|
|
|
'status' => $result['Msg_text'], |
|
269
|
|
|
); |
|
270
|
|
|
} |
|
271
|
|
|
} |
|
272
|
|
|
|
|
273
|
|
|
return $table; |
|
274
|
|
|
} |
|
275
|
|
|
|
|
276
|
|
|
/** |
|
277
|
|
|
* @param string $sql |
|
278
|
|
|
* |
|
279
|
|
|
* @return array|bool |
|
280
|
|
|
*/ |
|
281
|
|
|
protected function _query($sql) |
|
282
|
|
|
{ |
|
283
|
|
|
/** @var \PDO $connection */ |
|
284
|
|
|
$connection = $this->dbHelper->getConnection($this->output); |
|
285
|
|
|
$query = $connection->prepare($sql); |
|
286
|
|
|
$query->execute(); |
|
287
|
|
|
$result = $query->fetch(\PDO::FETCH_ASSOC); |
|
288
|
|
|
|
|
289
|
|
|
return $result; |
|
290
|
|
|
} |
|
291
|
|
|
} |
|
292
|
|
|
|
netz98 /
n98-magerun
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.