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