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
|
|
|
// read remaining lines line by line and create new schema |
83
|
|
|
foreach ($lines as $key => $value) { |
84
|
|
|
$line = \trim($value); |
85
|
|
|
if ('CREATE TABLE' === \mb_strtoupper(\substr($line, 0, 12))) { |
86
|
|
|
// start table definition |
87
|
|
|
$tableName = $this->getTableName ($line); |
88
|
|
|
$tables[$tableName] = []; |
89
|
|
|
$tables[$tableName]['options'] = ''; |
90
|
|
|
$tables[$tableName]['columns'] = []; |
91
|
|
|
$tables[$tableName]['keys'] = []; |
92
|
|
|
} else { |
93
|
|
|
if (')' === \mb_strtoupper(\substr($line, 0, 1))) { |
94
|
|
|
// end of table definition |
95
|
|
|
// get options |
96
|
|
|
$tables[$tableName]['options'] = $this->getOptions($line); |
97
|
|
|
} else { |
98
|
|
|
// get keys and fields |
99
|
|
|
switch (\mb_strtoupper(\substr($line, 0, 3))) { |
100
|
|
|
case 'KEY': |
101
|
|
|
case 'PRI': |
102
|
|
|
case 'UNI': |
103
|
|
|
$tables[$tableName]['keys'][] = $this->getKey($line); |
104
|
|
|
break; |
105
|
|
|
case 'else': |
106
|
|
|
default: |
107
|
|
|
$columns = $this->getColumns($line); |
108
|
|
|
$tables[$tableName]['columns'][] = $columns; |
109
|
|
|
break; |
110
|
|
|
} |
111
|
|
|
} |
112
|
|
|
} |
113
|
|
|
} |
114
|
|
|
|
115
|
|
|
// create array for new schema |
116
|
|
|
$level1 = \str_repeat(' ', 4); |
117
|
|
|
$level2 = \str_repeat(' ', 8); |
118
|
|
|
$level3 = \str_repeat(' ', 12); |
119
|
|
|
|
120
|
|
|
foreach ($tables as $tkey => $table) { |
121
|
|
|
$schema[] = "{$tkey}:\n"; |
122
|
|
|
foreach ($table as $lkey => $line) { |
123
|
|
|
if ('keys' == $lkey) { |
124
|
|
|
$schema[] = $level1 . "keys:\n"; |
125
|
|
|
foreach ($line as $kkey => $kvalue) { |
126
|
|
|
foreach ($kvalue as $kkey2 => $kvalue2) { |
127
|
|
|
$schema[] = $level2 . $kkey2 . ":\n"; |
128
|
|
|
$schema[] = $level3 . 'columns: ' . $kvalue2['columns'] . "\n"; |
129
|
|
|
$schema[] = $level3 . 'unique: ' . $kvalue2['unique'] . "\n"; |
130
|
|
|
} |
131
|
|
|
} |
132
|
|
|
} elseif ('options' == $lkey) { |
133
|
|
|
$schema[] = $level1 . 'options: ' . $line . "\n"; |
134
|
|
|
} else { |
135
|
|
|
$schema[] = $level1 . 'columns: ' . "\n"; |
136
|
|
|
foreach ($line as $kkey => $kvalue) { |
137
|
|
|
$schema[] = $level2 . '-' . "\n"; |
138
|
|
|
foreach ($kvalue as $kkey2 => $kvalue2) { |
139
|
|
|
$schema[] = $level3 . $kkey2 . ": " . $kvalue2 . "\n"; |
140
|
|
|
} |
141
|
|
|
} |
142
|
|
|
} |
143
|
|
|
} |
144
|
|
|
} |
145
|
|
|
|
146
|
|
|
// create new file and write schema array into this file |
147
|
|
|
$myfile = \fopen($this->fileYaml, "w"); |
148
|
|
|
if (false == $myfile || \is_null($myfile)) { |
149
|
|
|
\xoops_error('Error: Unable to open sql file!'); |
150
|
|
|
return false; |
151
|
|
|
} |
152
|
|
|
foreach ($schema as $line) { |
153
|
|
|
\fwrite($myfile, $line); |
154
|
|
|
} |
155
|
|
|
\fclose($myfile); |
156
|
|
|
|
157
|
|
|
return true; |
158
|
|
|
|
159
|
|
|
} |
160
|
|
|
|
161
|
|
|
/** |
162
|
|
|
* Extract table name from given line |
163
|
|
|
* |
164
|
|
|
* @param string $line |
165
|
|
|
* @return string|bool |
166
|
|
|
*/ |
167
|
|
|
private function getTableName (string $line) |
168
|
|
|
{ |
169
|
|
|
|
170
|
|
|
$arrLine = \explode( '`', $line); |
171
|
|
|
if (\count($arrLine) > 0) { |
172
|
|
|
return $arrLine[1]; |
173
|
|
|
} |
174
|
|
|
|
175
|
|
|
return false; |
176
|
|
|
|
177
|
|
|
} |
178
|
|
|
|
179
|
|
|
/** |
180
|
|
|
* Extract columns/fields of table from given line |
181
|
|
|
* |
182
|
|
|
* @param string $line |
183
|
|
|
* @return array|bool |
184
|
|
|
*/ |
185
|
|
|
private function getColumns (string $line) |
186
|
|
|
{ |
187
|
|
|
|
188
|
|
|
$columns = []; |
189
|
|
|
|
190
|
|
|
$arrCol = \explode( ' ', \trim($line)); |
191
|
|
|
if (\count($arrCol) > 0) { |
192
|
|
|
$name = \str_replace(['`'], '', $arrCol[0]); |
193
|
|
|
} else { |
194
|
|
|
return false; |
195
|
|
|
} |
196
|
|
|
$attributes = \trim(\str_replace([$name, '`', ','], '', $line)); |
197
|
|
|
|
198
|
|
|
$columns['name'] = $name; |
199
|
|
|
// update quotes |
200
|
|
|
if (\strpos($attributes, "''") > 0) { |
201
|
|
|
$attributes = \trim(\str_replace("''", "''''''''" , $attributes)); |
202
|
|
|
} elseif (\strpos($attributes, "'") > 0) { |
203
|
|
|
$attributes = \trim(\str_replace("'", "''" , $attributes)); |
204
|
|
|
} |
205
|
|
|
$columns['attributes'] = "' " . $attributes . " '"; |
206
|
|
|
|
207
|
|
|
return $columns; |
208
|
|
|
|
209
|
|
|
} |
210
|
|
|
|
211
|
|
|
/** |
212
|
|
|
* Extract options of table from given line |
213
|
|
|
* |
214
|
|
|
* @param string $line |
215
|
|
|
* @return string |
216
|
|
|
*/ |
217
|
|
|
private function getOptions (string $line): string |
218
|
|
|
{ |
219
|
|
|
|
220
|
|
|
$options = \str_replace([')', ';'], '', $line); |
221
|
|
|
$options = \trim($options); |
222
|
|
|
$options = "'" . $options . "'"; |
223
|
|
|
|
224
|
|
|
return $options; |
225
|
|
|
|
226
|
|
|
} |
227
|
|
|
|
228
|
|
|
/** |
229
|
|
|
* Extract keys of table from given line |
230
|
|
|
* |
231
|
|
|
* @param string $line |
232
|
|
|
* @return array|bool |
233
|
|
|
*/ |
234
|
|
|
private function getKey (string $line) |
235
|
|
|
{ |
236
|
|
|
|
237
|
|
|
$key = []; |
238
|
|
|
|
239
|
|
|
if (\strpos($line, 'RIMARY') > 0) { |
240
|
|
|
$key['PRIMARY'] = []; |
241
|
|
|
$fields = \substr($line, 13, \strlen($line) - 13); |
242
|
|
|
$key['PRIMARY']['columns'] = \str_replace(['`', '),', ')'], '', $fields); |
243
|
|
|
$key['PRIMARY']['unique'] = 'true'; |
244
|
|
|
} else { |
245
|
|
|
$unique = 'false'; |
246
|
|
|
if (\strpos($line, 'NIQUE') > 0) { |
247
|
|
|
$unique = 'true'; |
248
|
|
|
} |
249
|
|
|
$line = \trim(\str_replace(['UNIQUE KEY', 'KEY'], '', $line)); |
250
|
|
|
$arrName = \explode('(', $line); |
251
|
|
|
if (\count($arrName) > 0) { |
252
|
|
|
$name = \str_replace(['`', ' '], '', $arrName[0]); |
253
|
|
|
$columns = \str_replace(['`', '),', ')'], '', $arrName[1]); |
254
|
|
|
if ('' == $name) { |
255
|
|
|
$name = $columns; |
256
|
|
|
} |
257
|
|
|
if (\strpos($name,' ') > 0) { |
258
|
|
|
$name = "'" . $name . "'"; |
259
|
|
|
} |
260
|
|
|
$key[$name] = []; |
261
|
|
|
if (\strpos($columns,' ') > 0) { |
262
|
|
|
$columns = "'" . $columns . "'"; |
263
|
|
|
} |
264
|
|
|
$key[$name]['columns'] = $columns; |
265
|
|
|
$key[$name]['unique'] = $unique; |
266
|
|
|
} |
267
|
|
|
} |
268
|
|
|
return $key; |
269
|
|
|
} |
270
|
|
|
} |
271
|
|
|
|