1
|
|
|
<?php |
2
|
|
|
|
3
|
|
|
namespace XoopsModules\Modulebuilder\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
|
|
|
use XoopsModules\Modulebuilder\Common; |
16
|
|
|
|
17
|
|
|
/** |
18
|
|
|
* Class to compare current DB table structure with sql/mysql.sql |
19
|
|
|
* |
20
|
|
|
* @category Table Checker |
21
|
|
|
* @author Goffy <[email protected]> |
22
|
|
|
* @copyright 2021 XOOPS Project (https://xoops.org) |
23
|
|
|
* @license GNU GPL 2 or later (https://www.gnu.org/licenses/gpl-2.0.html) |
24
|
|
|
* @link https://xoops.org |
25
|
|
|
*/ |
26
|
|
|
|
27
|
|
|
class TableChecker extends \XoopsObject |
28
|
|
|
{ |
29
|
|
|
|
30
|
|
|
/** |
31
|
|
|
* @var mixed |
32
|
|
|
*/ |
33
|
|
|
private $after = null; |
34
|
|
|
|
35
|
|
|
/** |
36
|
|
|
* @var mixed |
37
|
|
|
*/ |
38
|
|
|
private $mydirname = null; |
39
|
|
|
|
40
|
|
|
/** |
41
|
|
|
* @var mixed |
42
|
|
|
*/ |
43
|
|
|
private $result = []; |
44
|
|
|
|
45
|
|
|
/** |
46
|
|
|
* @var mixed |
47
|
|
|
*/ |
48
|
|
|
private $checktype = null; |
49
|
|
|
public const CHECKTYPE_REPORT = 0; //report only |
50
|
|
|
public const CHECKTYPE_UPDATE = 1; //update only |
51
|
|
|
public const CHECKTYPE_UPDATE_REPORT = 2; //update and report |
52
|
|
|
|
53
|
|
|
|
54
|
|
|
/** |
55
|
|
|
* @param \XoopsModules\Modulebuilder\Common\TableChecker|null |
56
|
|
|
*/ |
57
|
|
|
public function __construct($mydirname, $checktype = 0) |
58
|
|
|
{ |
59
|
|
|
$this->mydirname = $mydirname; |
60
|
|
|
$this->checktype = $checktype; |
61
|
|
|
$this->result = []; |
62
|
|
|
} |
63
|
|
|
|
64
|
|
|
/** |
65
|
|
|
* |
66
|
|
|
*/ |
67
|
|
|
public function processSQL() |
68
|
|
|
{ |
69
|
|
|
|
70
|
|
|
$tabledefs = $this->readSQLFile(); |
71
|
|
|
|
72
|
|
|
$this->result[] = 'Tables found in sql:' . \count($tabledefs); |
73
|
|
|
|
74
|
|
|
foreach ($tabledefs as $tabledef) { |
75
|
|
|
//echo '<br>' . $tabledef['name']; |
76
|
|
|
//check whether table exist or not |
77
|
|
|
$table = $tabledef['name']; |
78
|
|
|
$check = $GLOBALS['xoopsDB']->queryF("SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='$table'"); |
79
|
|
|
$numRows = $GLOBALS['xoopsDB']->getRowsNum($check); |
80
|
|
|
if ($numRows) { |
81
|
|
|
//table exist |
82
|
|
|
$this->result[] = 'Table exist:' . $table; |
83
|
|
|
$ret = $this->checkTableFields($table, $tabledef['fields']); |
|
|
|
|
84
|
|
|
} else { |
85
|
|
|
if ($this::CHECKTYPE_UPDATE == $this->checktype || $this::CHECKTYPE_UPDATE_REPORT == $this->checktype) { |
86
|
|
|
// create new table |
87
|
|
|
$sql = $tabledef['sql']; |
88
|
|
|
if ($this->result = $GLOBALS['xoopsDB']->queryF($sql)) { |
89
|
|
|
$this->result[] = 'Table created:' . $table; |
90
|
|
|
} else { |
91
|
|
|
\xoops_error($GLOBALS['xoopsDB']->error() . '<br>' . $sql); |
92
|
|
|
$this->result[] = 'Error creating table:' . $table; |
93
|
|
|
} |
94
|
|
|
} else { |
95
|
|
|
$this->result[] = 'Table do not exist:' . $table . ' (creation not activated)'; |
96
|
|
|
} |
97
|
|
|
} |
98
|
|
|
} |
99
|
|
|
|
100
|
|
|
if (self::CHECKTYPE_REPORT == $this->checktype || self::CHECKTYPE_UPDATE_REPORT == $this->checktype) { |
101
|
|
|
return $this->result; |
102
|
|
|
} |
103
|
|
|
} |
104
|
|
|
|
105
|
|
|
/** |
106
|
|
|
* |
107
|
|
|
*/ |
108
|
|
|
private function readSQLFile() |
109
|
|
|
{ |
110
|
|
|
$tabledefs = []; |
111
|
|
|
|
112
|
|
|
$moduleHandler = \xoops_getHandler('module'); |
113
|
|
|
$module = $moduleHandler->getByDirname($this->mydirname); |
|
|
|
|
114
|
|
|
$module->loadInfoAsVar($this->mydirname); |
115
|
|
|
$sqlfile = $module->getInfo('sqlfile'); |
116
|
|
|
$sql_file_path = \XOOPS_ROOT_PATH . '/modules/' . $this->mydirname . '/' . $sqlfile[\XOOPS_DB_TYPE]; |
117
|
|
|
|
118
|
|
|
if (\file_exists($sql_file_path)) { |
119
|
|
|
require_once \XOOPS_ROOT_PATH . '/class/database/sqlutility.php'; |
120
|
|
|
$sqlutil = new \SqlUtility(); |
121
|
|
|
$pieces = []; |
122
|
|
|
$sql_query = \trim(file_get_contents($sql_file_path)); |
123
|
|
|
$sqlutil->splitMySqlFile($pieces, $sql_query); |
124
|
|
|
|
125
|
|
|
$countTable = 0; |
126
|
|
|
foreach ($pieces as $piece) { |
127
|
|
|
$singleSql = $sqlutil->prefixQuery($piece, $GLOBALS['xoopsDB']->prefix()); |
128
|
|
|
$lines = \preg_split('/\r\n|\n|\r/', $piece); |
129
|
|
|
//var_dump($lines); |
130
|
|
|
$needle1 = 'create table'; |
131
|
|
|
if ($needle1 == \mb_strtolower($singleSql[1])) { |
132
|
|
|
$countLine = 0; |
133
|
|
|
$tabledefs[$countTable]['sql'] = $singleSql[0]; |
134
|
|
|
$tabledefs[$countTable]['name'] = $GLOBALS['xoopsDB']->prefix() . '_' . $singleSql[4]; |
135
|
|
|
$this->after = ''; |
136
|
|
|
foreach($lines as $line) { |
137
|
|
|
if ($countLine > 0) { |
138
|
|
|
$needle2 = 'primary key'; |
139
|
|
|
$needle3 = 'unique key'; |
140
|
|
|
$needle4 = 'key'; |
141
|
|
|
if (0 === \stripos(\trim($line), $needle2)) { |
142
|
|
|
$tabledefs[$countTable][$needle2] = $line; |
143
|
|
|
} elseif (0 === \stripos(\trim($line), $needle3)) { |
144
|
|
|
$tabledefs[$countTable][$needle3] = $line; |
145
|
|
|
} elseif (0 === \stripos(\trim($line), $needle4)) { |
146
|
|
|
$tabledefs[$countTable][$needle4] = $line; |
147
|
|
|
} else { |
148
|
|
|
if (\strpos($line, '`') > 0) { |
149
|
|
|
$tabledefs[$countTable]['fields'][] = $this->extractField($line); |
150
|
|
|
} |
151
|
|
|
} |
152
|
|
|
} |
153
|
|
|
$countLine++; |
154
|
|
|
} |
155
|
|
|
$countTable++; |
156
|
|
|
} |
157
|
|
|
} |
158
|
|
|
//var_dump($tabledefs); |
159
|
|
|
} else { |
160
|
|
|
$this->result[] = 'File do not exist:' . $sql_file_path; |
161
|
|
|
} |
162
|
|
|
|
163
|
|
|
return $tabledefs; |
164
|
|
|
} |
165
|
|
|
|
166
|
|
|
|
167
|
|
|
private function extractKey($line) { |
|
|
|
|
168
|
|
|
//todo: split string into single keys |
169
|
|
|
$needle = '('; |
170
|
|
|
$key_text = \substr($line, \strpos($line, $needle, 0) + 1); |
171
|
|
|
$needle = ')'; |
172
|
|
|
$key_text = \substr($key_text, 0, \strpos($key_text, $needle, 0)); |
173
|
|
|
|
174
|
|
|
return $key_text; |
175
|
|
|
|
176
|
|
|
} |
177
|
|
|
|
178
|
|
|
private function extractField($line) { |
179
|
|
|
//todo |
180
|
|
|
$counter = 0; |
181
|
|
|
$clean = mb_substr(\trim($line), 0, -1); |
182
|
|
|
$params = \array_values(\array_filter(\explode(' ', $clean))); |
183
|
|
|
$field['sql'] = $clean; |
|
|
|
|
184
|
|
|
$field['name'] = \trim($params[$counter], '`'); |
185
|
|
|
$counter++; |
186
|
|
|
$field['type'] = $params[$counter]; |
187
|
|
|
$counter++; |
188
|
|
|
if ('unsigned' == \mb_strtolower($params[$counter])) { |
189
|
|
|
$field['unsigned'] = $params[$counter]; |
190
|
|
|
$counter++; |
191
|
|
|
} |
192
|
|
|
if ('not' == \mb_strtolower($params[$counter]) && 'null' == \mb_strtolower($params[$counter + 1])) { |
193
|
|
|
$field['null'] = $params[$counter] . ' ' . $params[$counter + 1]; |
194
|
|
|
$counter = $counter+2; |
195
|
|
|
} |
196
|
|
|
if (\count($params) > $counter) { |
197
|
|
|
if ('auto_increment' == \mb_strtolower($params[$counter])) { |
198
|
|
|
$field['auto_increment'] = $params[$counter]; |
199
|
|
|
$counter++; |
200
|
|
|
} |
201
|
|
|
} |
202
|
|
|
if (\count($params) > $counter) { |
203
|
|
|
if ('default' == \mb_strtolower($params[$counter])) { |
204
|
|
|
$field['default'] = $params[$counter] . ' ' . $params[$counter + 1]; |
205
|
|
|
$counter = $counter + 2; |
|
|
|
|
206
|
|
|
} |
207
|
|
|
} |
208
|
|
|
|
209
|
|
|
$field['after'] = $this->after; |
210
|
|
|
$this->after = $field['name']; |
211
|
|
|
|
212
|
|
|
return $field; |
213
|
|
|
|
214
|
|
|
} |
215
|
|
|
|
216
|
|
|
private function checkTableFields($table, $fields) |
217
|
|
|
{ |
218
|
|
|
//to be created |
219
|
|
|
foreach ($fields as $field) { |
220
|
|
|
//check whether column exist or not |
221
|
|
|
$fieldname = $field['name']; |
222
|
|
|
$check = $GLOBALS['xoopsDB']->queryF("SHOW COLUMNS FROM `$table` LIKE '$fieldname'"); |
223
|
|
|
$numRows = $GLOBALS['xoopsDB']->getRowsNum($check); |
224
|
|
|
if ($numRows) { |
225
|
|
|
//field exist |
226
|
|
|
$this->checkField($table, $field); |
227
|
|
|
} else { |
228
|
|
|
if (self::CHECKTYPE_UPDATE == $this->checktype || self::CHECKTYPE_UPDATE_REPORT == $this->checktype) { |
229
|
|
|
// create new field |
230
|
|
|
$sql = "ALTER TABLE `$table` ADD " . $field['sql']; |
231
|
|
|
if ('' !== (string)$field['after']) { |
232
|
|
|
$sql .= ' AFTER `' . $field['after'] . '`;'; |
233
|
|
|
} |
234
|
|
|
if ($result = $GLOBALS['xoopsDB']->queryF($sql)) { |
|
|
|
|
235
|
|
|
$this->result[] = 'Field added:' . $fieldname; |
236
|
|
|
} else { |
237
|
|
|
\xoops_error($GLOBALS['xoopsDB']->error() . '<br>' . $sql); |
238
|
|
|
$this->result[] = "Error when adding '$fieldname' to table '$table'."; |
239
|
|
|
} |
240
|
|
|
} else { |
241
|
|
|
$this->result[] = 'Field do not exist:' . $fieldname . ' (creation not activated)'; |
242
|
|
|
} |
243
|
|
|
} |
244
|
|
|
} |
245
|
|
|
|
246
|
|
|
return true; |
247
|
|
|
} |
248
|
|
|
|
249
|
|
|
private function checkField($table, $field) |
|
|
|
|
250
|
|
|
{ |
251
|
|
|
//to be created |
252
|
|
|
$this->result[] = 'Field exist:' . $field['name'] . ' - no changes'; |
253
|
|
|
|
254
|
|
|
return true; |
255
|
|
|
} |
256
|
|
|
} |
257
|
|
|
|