1
|
|
|
<?php |
2
|
|
|
|
3
|
|
|
namespace XoopsModules\Wggithub\Common; |
4
|
|
|
|
5
|
|
|
/* |
6
|
|
|
You may not change or alter any portion of this comment or credits |
7
|
|
|
of supporting developers from this source code or any supporting source code |
8
|
|
|
which is considered copyrighted (c) material of the original comment or credit authors. |
9
|
|
|
|
10
|
|
|
This program is distributed in the hope that it will be useful, |
11
|
|
|
but WITHOUT ANY WARRANTY; without even the implied warranty of |
12
|
|
|
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. |
13
|
|
|
*/ |
14
|
|
|
|
15
|
|
|
/** |
16
|
|
|
* Class to compare current DB table structure with sql/mysql.sql |
17
|
|
|
* |
18
|
|
|
* @category Migrate Helper |
19
|
|
|
* @author Goffy <[email protected]> |
20
|
|
|
* @copyright 2021 XOOPS Project (https://xoops.org) |
21
|
|
|
* @license GNU GPL 2 or later (https://www.gnu.org/licenses/gpl-2.0.html) |
22
|
|
|
* @link https://xoops.org |
23
|
|
|
*/ |
24
|
|
|
|
25
|
|
|
class MigrateHelper |
26
|
|
|
{ |
27
|
|
|
|
28
|
|
|
/** |
29
|
|
|
* @var string |
30
|
|
|
*/ |
31
|
|
|
private $fileYaml; |
32
|
|
|
|
33
|
|
|
/** |
34
|
|
|
* @var string |
35
|
|
|
*/ |
36
|
|
|
private $fileSql; |
37
|
|
|
|
38
|
|
|
|
39
|
|
|
/** |
40
|
|
|
* @param string $fileSql |
41
|
|
|
* @param string $fileYaml |
42
|
|
|
*/ |
43
|
|
|
public function __construct(string $fileSql, string $fileYaml) |
44
|
|
|
{ |
45
|
|
|
$this->fileSql = $fileSql; |
46
|
|
|
$this->fileYaml = $fileYaml; |
47
|
|
|
} |
48
|
|
|
|
49
|
|
|
/** |
50
|
|
|
* Create a yaml file based on a sql file |
51
|
|
|
* |
52
|
|
|
* @param null |
53
|
|
|
* @return bool |
54
|
|
|
*/ |
55
|
|
|
public function createSchemaFromSqlfile(): bool |
56
|
|
|
{ |
57
|
|
|
if (!\file_exists($this->fileSql)) { |
58
|
|
|
\xoops_error('Error: Sql file not found!'); |
|
|
|
|
59
|
|
|
return false; |
60
|
|
|
} |
61
|
|
|
|
62
|
|
|
$tables = []; |
63
|
|
|
$schema = []; |
64
|
|
|
$tableName = ''; |
65
|
|
|
|
66
|
|
|
// read sql file |
67
|
|
|
$lines = \file($this->fileSql); |
68
|
|
|
|
69
|
|
|
// remove unnecessary lines |
70
|
|
|
foreach ($lines as $key => $value) { |
71
|
|
|
$line = \trim($value); |
72
|
|
|
// remove blank lines |
73
|
|
|
if ('' === $line) { |
74
|
|
|
unset($lines[$key]); |
75
|
|
|
} |
76
|
|
|
// remove comment lines |
77
|
|
|
if ('#' === \substr($line, 0, 1)) { |
78
|
|
|
unset($lines[$key]); |
79
|
|
|
} |
80
|
|
|
} |
81
|
|
|
|
82
|
|
|
$skip = true; |
83
|
|
|
$skipWords = ['CREATE DATABASE ', 'CREATE VIEW ', 'INSERT INTO ', 'SELECT ', 'DELETE ', 'UPDATE ', 'ALTER ', 'DROP ']; |
84
|
|
|
$options = ''; |
85
|
|
|
// read remaining lines line by line and create new schema |
86
|
|
|
foreach ($lines as $key => $value) { |
87
|
|
|
$line = \trim($value); |
88
|
|
|
foreach ($skipWords as $skipWord) { |
89
|
|
|
if ($skipWord === \mb_strtoupper(\substr($line, 0, \strlen($skipWord)))) { |
90
|
|
|
$skip = true; |
91
|
|
|
} |
92
|
|
|
} |
93
|
|
|
if ('CREATE TABLE' === \mb_strtoupper(\substr($line, 0, 12))) { |
94
|
|
|
$skip = false; |
95
|
|
|
$options = ''; |
96
|
|
|
// start table definition |
97
|
|
|
$tableName = $this->getTableName ($line); |
98
|
|
|
$tables[$tableName] = []; |
99
|
|
|
$tables[$tableName]['options'] = ''; |
100
|
|
|
$tables[$tableName]['columns'] = []; |
101
|
|
|
$tables[$tableName]['keys'] = []; |
102
|
|
|
} else { |
103
|
|
|
if (false == $skip) { |
|
|
|
|
104
|
|
|
if (')' === \mb_strtoupper(\substr($line, 0, 1))) { |
105
|
|
|
// end of table definition |
106
|
|
|
// get options |
107
|
|
|
$this->getOptions($line, $options); |
108
|
|
|
$tables[$tableName]['options'] = $options; |
109
|
|
|
} elseif ('ENGINE ' === \mb_strtoupper(\substr($line, 0, 7))) { |
110
|
|
|
$this->getOptions($line, $options); |
111
|
|
|
$tables[$tableName]['options'] = $options; |
112
|
|
|
} elseif ('DEFAULT CHARSET ' === \mb_strtoupper(\substr($line, 0, 16))) { |
113
|
|
|
$this->getOptions($line, $options); |
114
|
|
|
$tables[$tableName]['options'] = $options; |
115
|
|
|
} else { |
116
|
|
|
// get keys and fields |
117
|
|
|
switch (\mb_strtoupper(\substr($line, 0, 3))) { |
118
|
|
|
case 'KEY': |
119
|
|
|
case 'PRI': |
120
|
|
|
case 'UNI': |
121
|
|
|
$tables[$tableName]['keys'][] = $this->getKey($line); |
122
|
|
|
break; |
123
|
|
|
case 'else': |
124
|
|
|
default: |
125
|
|
|
$columns = $this->getColumns($line); |
126
|
|
|
$tables[$tableName]['columns'][] = $columns; |
127
|
|
|
break; |
128
|
|
|
} |
129
|
|
|
} |
130
|
|
|
} |
131
|
|
|
} |
132
|
|
|
} |
133
|
|
|
|
134
|
|
|
// create array for new schema |
135
|
|
|
$level1 = \str_repeat(' ', 4); |
136
|
|
|
$level2 = \str_repeat(' ', 8); |
137
|
|
|
$level3 = \str_repeat(' ', 12); |
138
|
|
|
|
139
|
|
|
foreach ($tables as $tkey => $table) { |
140
|
|
|
$schema[] = "{$tkey}:\n"; |
141
|
|
|
foreach ($table as $lkey => $line) { |
142
|
|
|
if ('keys' == $lkey) { |
143
|
|
|
$schema[] = $level1 . "keys:\n"; |
144
|
|
|
foreach ($line as $kkey => $kvalue) { |
145
|
|
|
foreach ($kvalue as $kkey2 => $kvalue2) { |
146
|
|
|
$schema[] = $level2 . $kkey2 . ":\n"; |
147
|
|
|
$schema[] = $level3 . 'columns: ' . $kvalue2['columns'] . "\n"; |
148
|
|
|
$schema[] = $level3 . 'unique: ' . $kvalue2['unique'] . "\n"; |
149
|
|
|
} |
150
|
|
|
} |
151
|
|
|
} elseif ('options' == $lkey) { |
152
|
|
|
$schema[] = $level1 . 'options: ' . $line . "\n"; |
153
|
|
|
} else { |
154
|
|
|
$schema[] = $level1 . 'columns: ' . "\n"; |
155
|
|
|
foreach ($line as $kkey => $kvalue) { |
156
|
|
|
$schema[] = $level2 . '-' . "\n"; |
157
|
|
|
foreach ($kvalue as $kkey2 => $kvalue2) { |
158
|
|
|
$schema[] = $level3 . $kkey2 . ": " . $kvalue2 . "\n"; |
159
|
|
|
} |
160
|
|
|
} |
161
|
|
|
} |
162
|
|
|
} |
163
|
|
|
} |
164
|
|
|
|
165
|
|
|
// create new file and write schema array into this file |
166
|
|
|
$myfile = \fopen($this->fileYaml, "w"); |
167
|
|
|
if (false == $myfile || \is_null($myfile)) { |
168
|
|
|
\xoops_error('Error: Unable to open sql file!'); |
169
|
|
|
return false; |
170
|
|
|
} |
171
|
|
|
foreach ($schema as $line) { |
172
|
|
|
\fwrite($myfile, $line); |
173
|
|
|
} |
174
|
|
|
\fclose($myfile); |
175
|
|
|
|
176
|
|
|
return true; |
177
|
|
|
|
178
|
|
|
} |
179
|
|
|
|
180
|
|
|
/** |
181
|
|
|
* Extract table name from given line |
182
|
|
|
* |
183
|
|
|
* @param string $line |
184
|
|
|
* @return string|bool |
185
|
|
|
*/ |
186
|
|
|
private function getTableName (string $line) |
187
|
|
|
{ |
188
|
|
|
|
189
|
|
|
$arrLine = \explode( '`', $line); |
190
|
|
|
if (\count($arrLine) > 0) { |
191
|
|
|
return $arrLine[1]; |
192
|
|
|
} |
193
|
|
|
|
194
|
|
|
return false; |
195
|
|
|
|
196
|
|
|
} |
197
|
|
|
|
198
|
|
|
/** |
199
|
|
|
* Extract columns/fields of table from given line |
200
|
|
|
* |
201
|
|
|
* @param string $line |
202
|
|
|
* @return array|bool |
203
|
|
|
*/ |
204
|
|
|
private function getColumns (string $line) |
205
|
|
|
{ |
206
|
|
|
|
207
|
|
|
$columns = []; |
208
|
|
|
|
209
|
|
|
$arrCol = \explode( ' ', \trim($line)); |
210
|
|
|
if (\count($arrCol) > 0) { |
211
|
|
|
$name = \str_replace(['`'], '', $arrCol[0]); |
212
|
|
|
} else { |
213
|
|
|
return false; |
214
|
|
|
} |
215
|
|
|
$attributes = \trim(\str_replace([$name, '`', ','], '', $line)); |
216
|
|
|
|
217
|
|
|
$columns['name'] = $name; |
218
|
|
|
// update quotes |
219
|
|
|
if (\strpos($attributes, "''") > 0) { |
220
|
|
|
$attributes = \trim(\str_replace("''", "''''''''" , $attributes)); |
221
|
|
|
} elseif (\strpos($attributes, "'") > 0) { |
222
|
|
|
$attributes = \trim(\str_replace("'", "''" , $attributes)); |
223
|
|
|
} |
224
|
|
|
$columns['attributes'] = "' " . $attributes . " '"; |
225
|
|
|
|
226
|
|
|
return $columns; |
227
|
|
|
|
228
|
|
|
} |
229
|
|
|
|
230
|
|
|
/** |
231
|
|
|
* Extract options of table from given line |
232
|
|
|
* |
233
|
|
|
* @param string $line |
234
|
|
|
* @param string $options |
235
|
|
|
* @return void |
236
|
|
|
*/ |
237
|
|
|
private function getOptions (string $line, string &$options): void |
238
|
|
|
{ |
239
|
|
|
|
240
|
|
|
$lineText = \trim(\str_replace([')', ';'], '', $line)); |
241
|
|
|
// remove all existing ' |
242
|
|
|
$options = \str_replace("'", '', $options); |
243
|
|
|
if ('' != $options) { |
244
|
|
|
$options .= ' '; |
245
|
|
|
} |
246
|
|
|
$options = "'" . $options . $lineText . "'"; |
247
|
|
|
|
248
|
|
|
} |
249
|
|
|
|
250
|
|
|
/** |
251
|
|
|
* Extract keys of table from given line |
252
|
|
|
* |
253
|
|
|
* @param string $line |
254
|
|
|
* @return array |
255
|
|
|
*/ |
256
|
|
|
private function getKey (string $line) |
257
|
|
|
{ |
258
|
|
|
|
259
|
|
|
$key = []; |
260
|
|
|
|
261
|
|
|
if (\strpos($line, 'RIMARY') > 0) { |
262
|
|
|
$key['PRIMARY'] = []; |
263
|
|
|
$fields = \substr($line, 13, \strlen($line) - 13); |
264
|
|
|
$key['PRIMARY']['columns'] = \str_replace(['`', '),', ')'], '', $fields); |
265
|
|
|
$key['PRIMARY']['unique'] = 'true'; |
266
|
|
|
} else { |
267
|
|
|
$unique = 'false'; |
268
|
|
|
if (\strpos($line, 'NIQUE') > 0) { |
269
|
|
|
$unique = 'true'; |
270
|
|
|
} |
271
|
|
|
$line = \trim(\str_replace(['UNIQUE KEY', 'KEY'], '', $line)); |
272
|
|
|
$arrName = \explode('(', $line); |
273
|
|
|
if (\count($arrName) > 0) { |
274
|
|
|
$name = \str_replace(['`', ' '], '', $arrName[0]); |
275
|
|
|
$columns = \str_replace(['`', '),', ')'], '', $arrName[1]); |
276
|
|
|
if ('' == $name) { |
277
|
|
|
$name = $columns; |
278
|
|
|
} |
279
|
|
|
if (\strpos($name,' ') > 0) { |
280
|
|
|
$name = "'" . $name . "'"; |
281
|
|
|
} |
282
|
|
|
$key[$name] = []; |
283
|
|
|
if (\strpos($columns,' ') > 0) { |
284
|
|
|
$columns = "'" . $columns . "'"; |
285
|
|
|
} |
286
|
|
|
$key[$name]['columns'] = $columns; |
287
|
|
|
$key[$name]['unique'] = $unique; |
288
|
|
|
} |
289
|
|
|
} |
290
|
|
|
return $key; |
291
|
|
|
} |
292
|
|
|
} |
293
|
|
|
|