1 | <?php |
||
2 | /* vim: set expandtab sw=4 ts=4 sts=4: */ |
||
3 | /** |
||
4 | * Holds the PhpMyAdmin\Database\Designer\Common class |
||
5 | * |
||
6 | * @package PhpMyAdmin-Designer |
||
7 | */ |
||
8 | declare(strict_types=1); |
||
9 | |||
10 | namespace PhpMyAdmin\Database\Designer; |
||
11 | |||
12 | use PhpMyAdmin\DatabaseInterface; |
||
13 | use PhpMyAdmin\Index; |
||
14 | use PhpMyAdmin\Relation; |
||
15 | use PhpMyAdmin\Table; |
||
16 | use PhpMyAdmin\Util; |
||
17 | |||
18 | /** |
||
19 | * Common functions for Designer |
||
20 | * |
||
21 | * @package PhpMyAdmin-Designer |
||
22 | */ |
||
23 | class Common |
||
24 | { |
||
25 | /** |
||
26 | * @var Relation |
||
27 | */ |
||
28 | private $relation; |
||
29 | |||
30 | /** |
||
31 | * @var \PhpMyAdmin\DatabaseInterface |
||
32 | */ |
||
33 | private $dbi; |
||
34 | |||
35 | /** |
||
36 | * Common constructor. |
||
37 | * |
||
38 | * @param DatabaseInterface $dbi DatabaseInterface object |
||
39 | */ |
||
40 | public function __construct(DatabaseInterface $dbi) |
||
41 | { |
||
42 | $this->dbi = $dbi; |
||
43 | $this->relation = new Relation($this->dbi); |
||
44 | } |
||
45 | |||
46 | /** |
||
47 | * Retrieves table info and stores it in $GLOBALS['designer'] |
||
48 | * |
||
49 | * @return array with table info |
||
50 | */ |
||
51 | public function getTablesInfo() |
||
52 | { |
||
53 | $retval = []; |
||
54 | |||
55 | $GLOBALS['designer']['TABLE_NAME'] = [];// that foreach no error |
||
56 | $GLOBALS['designer']['OWNER'] = []; |
||
57 | $GLOBALS['designer']['TABLE_NAME_SMALL'] = []; |
||
58 | $GLOBALS['designer']['TABLE_TYPE'] = []; |
||
59 | |||
60 | $GLOBALS['designer_url']['TABLE_NAME'] = []; |
||
61 | $GLOBALS['designer_url']['OWNER'] = []; |
||
62 | $GLOBALS['designer_url']['TABLE_NAME_SMALL'] = []; |
||
63 | |||
64 | $GLOBALS['designer_out']['TABLE_NAME'] = []; |
||
65 | $GLOBALS['designer_out']['OWNER'] = []; |
||
66 | $GLOBALS['designer_out']['TABLE_NAME_SMALL'] = []; |
||
67 | $tables = $this->dbi->getTablesFull($GLOBALS['db']); |
||
68 | // seems to be needed later |
||
69 | $this->dbi->selectDb($GLOBALS['db']); |
||
70 | $i = 0; |
||
71 | foreach ($tables as $one_table) { |
||
72 | $GLOBALS['designer']['TABLE_NAME'][$i] |
||
73 | = $GLOBALS['db'] . "." . $one_table['TABLE_NAME']; |
||
74 | $GLOBALS['designer']['OWNER'][$i] = $GLOBALS['db']; |
||
75 | $GLOBALS['designer']['TABLE_NAME_SMALL'][$i] = htmlspecialchars( |
||
76 | $one_table['TABLE_NAME'], |
||
77 | ENT_QUOTES |
||
78 | ); |
||
79 | |||
80 | $GLOBALS['designer_url']['TABLE_NAME'][$i] |
||
81 | = $GLOBALS['db'] . "." . $one_table['TABLE_NAME']; |
||
82 | $GLOBALS['designer_url']['OWNER'][$i] = $GLOBALS['db']; |
||
83 | $GLOBALS['designer_url']['TABLE_NAME_SMALL'][$i] |
||
84 | = $one_table['TABLE_NAME']; |
||
85 | |||
86 | $GLOBALS['designer_out']['TABLE_NAME'][$i] = htmlspecialchars( |
||
87 | $GLOBALS['db'] . "." . $one_table['TABLE_NAME'], |
||
88 | ENT_QUOTES |
||
89 | ); |
||
90 | $GLOBALS['designer_out']['OWNER'][$i] = htmlspecialchars( |
||
91 | $GLOBALS['db'], |
||
92 | ENT_QUOTES |
||
93 | ); |
||
94 | $GLOBALS['designer_out']['TABLE_NAME_SMALL'][$i] = htmlspecialchars( |
||
95 | $one_table['TABLE_NAME'], |
||
96 | ENT_QUOTES |
||
97 | ); |
||
98 | |||
99 | $GLOBALS['designer']['TABLE_TYPE'][$i] = mb_strtoupper( |
||
100 | (string) $one_table['ENGINE'] |
||
101 | ); |
||
102 | |||
103 | $DF = $this->relation->getDisplayField($GLOBALS['db'], $one_table['TABLE_NAME']); |
||
104 | if ($DF != '') { |
||
105 | $DF = rawurlencode((string)$DF); |
||
106 | $retval[rawurlencode($GLOBALS['designer_url']["TABLE_NAME_SMALL"][$i])] = $DF; |
||
107 | } |
||
108 | |||
109 | $i++; |
||
110 | } |
||
111 | |||
112 | return $retval; |
||
113 | } |
||
114 | |||
115 | /** |
||
116 | * Retrieves table column info |
||
117 | * |
||
118 | * @return array table column nfo |
||
119 | */ |
||
120 | public function getColumnsInfo() |
||
121 | { |
||
122 | $this->dbi->selectDb($GLOBALS['db']); |
||
123 | $tab_column = []; |
||
124 | for ($i = 0, $cnt = count($GLOBALS['designer']["TABLE_NAME"]); $i < $cnt; $i++) { |
||
125 | $fields_rs = $this->dbi->query( |
||
126 | $this->dbi->getColumnsSql( |
||
127 | $GLOBALS['db'], |
||
128 | $GLOBALS['designer_url']["TABLE_NAME_SMALL"][$i], |
||
129 | null, |
||
130 | true |
||
131 | ), |
||
132 | DatabaseInterface::CONNECT_USER, |
||
133 | DatabaseInterface::QUERY_STORE |
||
134 | ); |
||
135 | $tbl_name_i = $GLOBALS['designer']['TABLE_NAME'][$i]; |
||
136 | $j = 0; |
||
137 | while ($row = $this->dbi->fetchAssoc($fields_rs)) { |
||
138 | $tab_column[$tbl_name_i]['COLUMN_ID'][$j] = $j; |
||
139 | $tab_column[$tbl_name_i]['COLUMN_NAME'][$j] = $row['Field']; |
||
140 | $tab_column[$tbl_name_i]['TYPE'][$j] = $row['Type']; |
||
141 | $tab_column[$tbl_name_i]['NULLABLE'][$j] = $row['Null']; |
||
142 | $j++; |
||
143 | } |
||
144 | } |
||
145 | return $tab_column; |
||
146 | } |
||
147 | |||
148 | /** |
||
149 | * Returns JavaScript code for initializing vars |
||
150 | * |
||
151 | * @return string JavaScript code |
||
152 | */ |
||
153 | public function getScriptContr() |
||
154 | { |
||
155 | $this->dbi->selectDb($GLOBALS['db']); |
||
156 | $con = []; |
||
157 | $con["C_NAME"] = []; |
||
158 | $i = 0; |
||
159 | $alltab_rs = $this->dbi->query( |
||
160 | 'SHOW TABLES FROM ' . Util::backquote($GLOBALS['db']), |
||
161 | DatabaseInterface::CONNECT_USER, |
||
162 | DatabaseInterface::QUERY_STORE |
||
163 | ); |
||
164 | while ($val = @$this->dbi->fetchRow($alltab_rs)) { |
||
165 | $row = $this->relation->getForeigners($GLOBALS['db'], $val[0], '', 'internal'); |
||
166 | |||
167 | if ($row !== false) { |
||
168 | foreach ($row as $field => $value) { |
||
169 | $con['C_NAME'][$i] = ''; |
||
170 | $con['DTN'][$i] = rawurlencode($GLOBALS['db'] . "." . $val[0]); |
||
171 | $con['DCN'][$i] = rawurlencode($field); |
||
172 | $con['STN'][$i] = rawurlencode( |
||
173 | $value['foreign_db'] . "." . $value['foreign_table'] |
||
174 | ); |
||
175 | $con['SCN'][$i] = rawurlencode($value['foreign_field']); |
||
176 | $i++; |
||
177 | } |
||
178 | } |
||
179 | $row = $this->relation->getForeigners($GLOBALS['db'], $val[0], '', 'foreign'); |
||
180 | |||
181 | if ($row !== false) { |
||
182 | foreach ($row['foreign_keys_data'] as $one_key) { |
||
183 | foreach ($one_key['index_list'] as $index => $one_field) { |
||
184 | $con['C_NAME'][$i] = rawurlencode($one_key['constraint']); |
||
185 | $con['DTN'][$i] = rawurlencode($GLOBALS['db'] . "." . $val[0]); |
||
186 | $con['DCN'][$i] = rawurlencode($one_field); |
||
187 | $con['STN'][$i] = rawurlencode( |
||
188 | (isset($one_key['ref_db_name']) ? |
||
189 | $one_key['ref_db_name'] : $GLOBALS['db']) |
||
190 | . "." . $one_key['ref_table_name'] |
||
191 | ); |
||
192 | $con['SCN'][$i] = rawurlencode($one_key['ref_index_list'][$index]); |
||
193 | $i++; |
||
194 | } |
||
195 | } |
||
196 | } |
||
197 | } |
||
198 | |||
199 | $ti = 0; |
||
200 | $retval = []; |
||
201 | for ($i = 0, $cnt = count($con["C_NAME"]); $i < $cnt; $i++) { |
||
202 | $c_name_i = $con['C_NAME'][$i]; |
||
203 | $dtn_i = $con['DTN'][$i]; |
||
204 | $retval[$ti] = []; |
||
205 | $retval[$ti][$c_name_i] = []; |
||
206 | if (in_array(rawurldecode($dtn_i), $GLOBALS['designer_url']["TABLE_NAME"]) |
||
207 | && in_array(rawurldecode($con['STN'][$i]), $GLOBALS['designer_url']["TABLE_NAME"]) |
||
208 | ) { |
||
209 | $retval[$ti][$c_name_i][$dtn_i] = []; |
||
210 | $retval[$ti][$c_name_i][$dtn_i][$con['DCN'][$i]] = [ |
||
211 | 0 => $con['STN'][$i], |
||
212 | 1 => $con['SCN'][$i] |
||
213 | ]; |
||
214 | } |
||
215 | $ti++; |
||
216 | } |
||
217 | return $retval; |
||
218 | } |
||
219 | |||
220 | /** |
||
221 | * Returns UNIQUE and PRIMARY indices |
||
222 | * |
||
223 | * @return array unique or primary indices |
||
224 | */ |
||
225 | public function getPkOrUniqueKeys() |
||
226 | { |
||
227 | return $this->getAllKeys(true); |
||
228 | } |
||
229 | |||
230 | /** |
||
231 | * Returns all indices |
||
232 | * |
||
233 | * @param bool $unique_only whether to include only unique ones |
||
234 | * |
||
235 | * @return array indices |
||
236 | */ |
||
237 | public function getAllKeys($unique_only = false) |
||
238 | { |
||
239 | $keys = []; |
||
240 | |||
241 | foreach ($GLOBALS['designer']['TABLE_NAME_SMALL'] as $I => $table) { |
||
242 | $schema = $GLOBALS['designer']['OWNER'][$I]; |
||
243 | // for now, take into account only the first index segment |
||
244 | foreach (Index::getFromTable($table, $schema) as $index) { |
||
245 | if ($unique_only && ! $index->isUnique()) { |
||
246 | continue; |
||
247 | } |
||
248 | $columns = $index->getColumns(); |
||
249 | foreach ($columns as $column_name => $dummy) { |
||
250 | $keys[$schema . '.' . $table . '.' . $column_name] = 1; |
||
251 | } |
||
252 | } |
||
253 | } |
||
254 | return $keys; |
||
255 | } |
||
256 | |||
257 | /** |
||
258 | * Return script to create j_tab and h_tab arrays |
||
259 | * |
||
260 | * @return string |
||
261 | */ |
||
262 | public function getScriptTabs() |
||
263 | { |
||
264 | $retval = [ |
||
265 | 'j_tabs' => [], |
||
266 | 'h_tabs' => [] |
||
267 | ]; |
||
268 | |||
269 | for ($i = 0, $cnt = count($GLOBALS['designer']['TABLE_NAME']); $i < $cnt; $i++) { |
||
270 | $j = 0; |
||
271 | if (Util::isForeignKeySupported($GLOBALS['designer']['TABLE_TYPE'][$i])) { |
||
272 | $j = 1; |
||
273 | } |
||
274 | $retval['j_tabs'][\rawurlencode($GLOBALS['designer_url']['TABLE_NAME'][$i])] = $j; |
||
275 | $retval['h_tabs'][\rawurlencode($GLOBALS['designer_url']['TABLE_NAME'][$i])] = 1; |
||
276 | } |
||
277 | return $retval; |
||
278 | } |
||
279 | |||
280 | /** |
||
281 | * Returns table positions of a given pdf page |
||
282 | * |
||
283 | * @param int $pg pdf page id |
||
284 | * |
||
285 | * @return array|null of table positions |
||
286 | */ |
||
287 | public function getTablePositions($pg): ?array |
||
288 | { |
||
289 | $cfgRelation = $this->relation->getRelationsParam(); |
||
290 | if (! $cfgRelation['pdfwork']) { |
||
291 | return array(); |
||
292 | } |
||
293 | |||
294 | $query = " |
||
295 | SELECT CONCAT_WS('.', `db_name`, `table_name`) AS `name`, |
||
296 | `x` AS `X`, |
||
297 | `y` AS `Y`, |
||
298 | 1 AS `V`, |
||
299 | 1 AS `H` |
||
300 | FROM " . Util::backquote($cfgRelation['db']) |
||
301 | . "." . Util::backquote($cfgRelation['table_coords']) . " |
||
302 | WHERE pdf_page_number = " . intval($pg); |
||
303 | |||
304 | return $this->dbi->fetchResult( |
||
305 | $query, |
||
306 | 'name', |
||
307 | null, |
||
308 | DatabaseInterface::CONNECT_CONTROL, |
||
309 | DatabaseInterface::QUERY_STORE |
||
310 | ); |
||
311 | } |
||
312 | |||
313 | /** |
||
314 | * Returns page name of a given pdf page |
||
315 | * |
||
316 | * @param int $pg pdf page id |
||
317 | * |
||
318 | * @return string table name |
||
319 | */ |
||
320 | public function getPageName($pg) |
||
321 | { |
||
322 | $cfgRelation = $this->relation->getRelationsParam(); |
||
323 | if (! $cfgRelation['pdfwork']) { |
||
324 | return null; |
||
325 | } |
||
326 | |||
327 | $query = "SELECT `page_descr`" |
||
328 | . " FROM " . Util::backquote($cfgRelation['db']) |
||
329 | . "." . Util::backquote($cfgRelation['pdf_pages']) |
||
330 | . " WHERE " . Util::backquote('page_nr') . " = " . intval($pg); |
||
331 | $page_name = $this->dbi->fetchResult( |
||
332 | $query, |
||
333 | null, |
||
334 | null, |
||
335 | DatabaseInterface::CONNECT_CONTROL, |
||
336 | DatabaseInterface::QUERY_STORE |
||
337 | ); |
||
338 | return count($page_name) ? $page_name[0] : null; |
||
339 | } |
||
340 | |||
341 | /** |
||
342 | * Deletes a given pdf page and its corresponding coordinates |
||
343 | * |
||
344 | * @param int $pg page id |
||
345 | * |
||
346 | * @return boolean success/failure |
||
347 | */ |
||
348 | public function deletePage($pg) |
||
349 | { |
||
350 | $cfgRelation = $this->relation->getRelationsParam(); |
||
351 | if (! $cfgRelation['pdfwork']) { |
||
352 | return false; |
||
353 | } |
||
354 | |||
355 | $query = "DELETE FROM " . Util::backquote($cfgRelation['db']) |
||
356 | . "." . Util::backquote($cfgRelation['table_coords']) |
||
357 | . " WHERE " . Util::backquote('pdf_page_number') . " = " . intval($pg); |
||
358 | $success = $this->relation->queryAsControlUser( |
||
359 | $query, |
||
360 | true, |
||
361 | DatabaseInterface::QUERY_STORE |
||
362 | ); |
||
363 | |||
364 | if ($success) { |
||
365 | $query = "DELETE FROM " . Util::backquote($cfgRelation['db']) |
||
366 | . "." . Util::backquote($cfgRelation['pdf_pages']) |
||
367 | . " WHERE " . Util::backquote('page_nr') . " = " . intval($pg); |
||
368 | $success = $this->relation->queryAsControlUser( |
||
369 | $query, |
||
370 | true, |
||
371 | DatabaseInterface::QUERY_STORE |
||
372 | ); |
||
373 | } |
||
374 | |||
375 | return (bool) $success; |
||
376 | } |
||
377 | |||
378 | /** |
||
379 | * Returns the id of the default pdf page of the database. |
||
380 | * Default page is the one which has the same name as the database. |
||
381 | * |
||
382 | * @param string $db database |
||
383 | * |
||
384 | * @return int id of the default pdf page for the database |
||
385 | */ |
||
386 | public function getDefaultPage($db) |
||
387 | { |
||
388 | $cfgRelation = $this->relation->getRelationsParam(); |
||
389 | if (! $cfgRelation['pdfwork']) { |
||
390 | return null; |
||
391 | } |
||
392 | |||
393 | $query = "SELECT `page_nr`" |
||
394 | . " FROM " . Util::backquote($cfgRelation['db']) |
||
395 | . "." . Util::backquote($cfgRelation['pdf_pages']) |
||
396 | . " WHERE `db_name` = '" . $this->dbi->escapeString($db) . "'" |
||
397 | . " AND `page_descr` = '" . $this->dbi->escapeString($db) . "'"; |
||
398 | |||
399 | $default_page_no = $this->dbi->fetchResult( |
||
400 | $query, |
||
401 | null, |
||
402 | null, |
||
403 | DatabaseInterface::CONNECT_CONTROL, |
||
404 | DatabaseInterface::QUERY_STORE |
||
405 | ); |
||
406 | |||
407 | if (! is_null($default_page_no) && count($default_page_no)) { |
||
0 ignored issues
–
show
introduced
by
Loading history...
|
|||
408 | return intval($default_page_no[0]); |
||
409 | } |
||
410 | return -1; |
||
411 | } |
||
412 | |||
413 | /** |
||
414 | * Get the id of the page to load. If a default page exists it will be returned. |
||
415 | * If no such exists, returns the id of the first page of the database. |
||
416 | * |
||
417 | * @param string $db database |
||
418 | * |
||
419 | * @return int id of the page to load |
||
420 | */ |
||
421 | public function getLoadingPage($db) |
||
422 | { |
||
423 | $cfgRelation = $this->relation->getRelationsParam(); |
||
424 | if (! $cfgRelation['pdfwork']) { |
||
425 | return null; |
||
426 | } |
||
427 | |||
428 | $page_no = -1; |
||
429 | |||
430 | $default_page_no = $this->getDefaultPage($db); |
||
431 | if ($default_page_no != -1) { |
||
432 | $page_no = $default_page_no; |
||
433 | } else { |
||
434 | $query = "SELECT MIN(`page_nr`)" |
||
435 | . " FROM " . Util::backquote($cfgRelation['db']) |
||
436 | . "." . Util::backquote($cfgRelation['pdf_pages']) |
||
437 | . " WHERE `db_name` = '" . $this->dbi->escapeString($db) . "'"; |
||
438 | |||
439 | $min_page_no = $this->dbi->fetchResult( |
||
440 | $query, |
||
441 | null, |
||
442 | null, |
||
443 | DatabaseInterface::CONNECT_CONTROL, |
||
444 | DatabaseInterface::QUERY_STORE |
||
445 | ); |
||
446 | if (isset($min_page_no[0]) && count($min_page_no[0])) { |
||
447 | $page_no = $min_page_no[0]; |
||
448 | } |
||
449 | } |
||
450 | return intval($page_no); |
||
451 | } |
||
452 | |||
453 | /** |
||
454 | * Creates a new page and returns its auto-incrementing id |
||
455 | * |
||
456 | * @param string $pageName name of the page |
||
457 | * @param string $db name of the database |
||
458 | * |
||
459 | * @return int|null |
||
460 | */ |
||
461 | public function createNewPage($pageName, $db) |
||
462 | { |
||
463 | $cfgRelation = $this->relation->getRelationsParam(); |
||
464 | if ($cfgRelation['pdfwork']) { |
||
465 | return $this->relation->createPage( |
||
466 | $pageName, |
||
467 | $cfgRelation, |
||
468 | $db |
||
469 | ); |
||
470 | } |
||
471 | return null; |
||
472 | } |
||
473 | |||
474 | /** |
||
475 | * Saves positions of table(s) of a given pdf page |
||
476 | * |
||
477 | * @param int $pg pdf page id |
||
478 | * |
||
479 | * @return boolean success/failure |
||
480 | */ |
||
481 | public function saveTablePositions($pg) |
||
482 | { |
||
483 | $cfgRelation = $this->relation->getRelationsParam(); |
||
484 | if (! $cfgRelation['pdfwork']) { |
||
485 | return false; |
||
486 | } |
||
487 | |||
488 | $query = "DELETE FROM " |
||
489 | . Util::backquote($GLOBALS['cfgRelation']['db']) |
||
490 | . "." . Util::backquote( |
||
491 | $GLOBALS['cfgRelation']['table_coords'] |
||
492 | ) |
||
493 | . " WHERE `db_name` = '" . $this->dbi->escapeString($_REQUEST['db']) |
||
494 | . "'" |
||
495 | . " AND `pdf_page_number` = '" . $this->dbi->escapeString($pg) |
||
496 | . "'"; |
||
497 | |||
498 | $res = $this->relation->queryAsControlUser( |
||
499 | $query, |
||
500 | true, |
||
501 | DatabaseInterface::QUERY_STORE |
||
502 | ); |
||
503 | |||
504 | if (!$res) { |
||
505 | return (bool)$res; |
||
506 | } |
||
507 | |||
508 | foreach ($_REQUEST['t_h'] as $key => $value) { |
||
509 | list($DB, $TAB) = explode(".", $key); |
||
510 | if (!$value) { |
||
511 | continue; |
||
512 | } |
||
513 | |||
514 | $query = "INSERT INTO " |
||
515 | . Util::backquote($GLOBALS['cfgRelation']['db']) . "." |
||
516 | . Util::backquote($GLOBALS['cfgRelation']['table_coords']) |
||
517 | . " (`db_name`, `table_name`, `pdf_page_number`, `x`, `y`)" |
||
518 | . " VALUES (" |
||
519 | . "'" . $this->dbi->escapeString($DB) . "', " |
||
520 | . "'" . $this->dbi->escapeString($TAB) . "', " |
||
521 | . "'" . $this->dbi->escapeString($pg) . "', " |
||
522 | . "'" . $this->dbi->escapeString($_REQUEST['t_x'][$key]) . "', " |
||
523 | . "'" . $this->dbi->escapeString($_REQUEST['t_y'][$key]) . "')"; |
||
524 | |||
525 | $res = $this->relation->queryAsControlUser( |
||
526 | $query, |
||
527 | true, |
||
528 | DatabaseInterface::QUERY_STORE |
||
529 | ); |
||
530 | } |
||
531 | |||
532 | return (bool) $res; |
||
533 | } |
||
534 | |||
535 | /** |
||
536 | * Saves the display field for a table. |
||
537 | * |
||
538 | * @param string $db database name |
||
539 | * @param string $table table name |
||
540 | * @param string $field display field name |
||
541 | * |
||
542 | * @return boolean |
||
543 | */ |
||
544 | public function saveDisplayField($db, $table, $field) |
||
545 | { |
||
546 | $cfgRelation = $this->relation->getRelationsParam(); |
||
547 | if (!$cfgRelation['displaywork']) { |
||
548 | return false; |
||
549 | } |
||
550 | |||
551 | $upd_query = new Table($table, $db, $this->dbi); |
||
552 | $upd_query->updateDisplayField($field, $cfgRelation); |
||
553 | |||
554 | return true; |
||
555 | } |
||
556 | |||
557 | /** |
||
558 | * Adds a new foreign relation |
||
559 | * |
||
560 | * @param string $db database name |
||
561 | * @param string $T1 foreign table |
||
562 | * @param string $F1 foreign field |
||
563 | * @param string $T2 master table |
||
564 | * @param string $F2 master field |
||
565 | * @param string $on_delete on delete action |
||
566 | * @param string $on_update on update action |
||
567 | * @param string $DB1 database |
||
568 | * @param string $DB2 database |
||
569 | * |
||
570 | * @return array array of success/failure and message |
||
571 | */ |
||
572 | public function addNewRelation($db, $T1, $F1, $T2, $F2, $on_delete, $on_update, $DB1, $DB2) |
||
573 | { |
||
574 | $tables = $this->dbi->getTablesFull($DB1, $T1); |
||
575 | $type_T1 = mb_strtoupper($tables[$T1]['ENGINE']); |
||
576 | $tables = $this->dbi->getTablesFull($DB2, $T2); |
||
577 | $type_T2 = mb_strtoupper($tables[$T2]['ENGINE']); |
||
578 | |||
579 | // native foreign key |
||
580 | if (Util::isForeignKeySupported($type_T1) |
||
581 | && Util::isForeignKeySupported($type_T2) |
||
582 | && $type_T1 == $type_T2 |
||
583 | ) { |
||
584 | // relation exists? |
||
585 | $existrel_foreign = $this->relation->getForeigners($DB2, $T2, '', 'foreign'); |
||
586 | $foreigner = $this->relation->searchColumnInForeigners($existrel_foreign, $F2); |
||
587 | if ($foreigner |
||
588 | && isset($foreigner['constraint']) |
||
589 | ) { |
||
590 | return [false, __('Error: relationship already exists.')]; |
||
591 | } |
||
592 | // note: in InnoDB, the index does not requires to be on a PRIMARY |
||
593 | // or UNIQUE key |
||
594 | // improve: check all other requirements for InnoDB relations |
||
595 | $result = $this->dbi->query( |
||
596 | 'SHOW INDEX FROM ' . Util::backquote($DB1) |
||
597 | . '.' . Util::backquote($T1) . ';' |
||
598 | ); |
||
599 | |||
600 | // will be use to emphasis prim. keys in the table view |
||
601 | $index_array1 = []; |
||
602 | while ($row = $this->dbi->fetchAssoc($result)) { |
||
603 | $index_array1[$row['Column_name']] = 1; |
||
604 | } |
||
605 | $this->dbi->freeResult($result); |
||
606 | |||
607 | $result = $this->dbi->query( |
||
608 | 'SHOW INDEX FROM ' . Util::backquote($DB2) |
||
609 | . '.' . Util::backquote($T2) . ';' |
||
610 | ); |
||
611 | // will be used to emphasis prim. keys in the table view |
||
612 | $index_array2 = []; |
||
613 | while ($row = $this->dbi->fetchAssoc($result)) { |
||
614 | $index_array2[$row['Column_name']] = 1; |
||
615 | } |
||
616 | $this->dbi->freeResult($result); |
||
617 | |||
618 | if (! empty($index_array1[$F1]) && ! empty($index_array2[$F2])) { |
||
619 | $upd_query = 'ALTER TABLE ' . Util::backquote($DB2) |
||
620 | . '.' . Util::backquote($T2) |
||
621 | . ' ADD FOREIGN KEY (' |
||
622 | . Util::backquote($F2) . ')' |
||
623 | . ' REFERENCES ' |
||
624 | . Util::backquote($DB1) . '.' |
||
625 | . Util::backquote($T1) . '(' |
||
626 | . Util::backquote($F1) . ')'; |
||
627 | |||
628 | if ($on_delete != 'nix') { |
||
629 | $upd_query .= ' ON DELETE ' . $on_delete; |
||
630 | } |
||
631 | if ($on_update != 'nix') { |
||
632 | $upd_query .= ' ON UPDATE ' . $on_update; |
||
633 | } |
||
634 | $upd_query .= ';'; |
||
635 | if ($this->dbi->tryQuery($upd_query)) { |
||
636 | return [true, __('FOREIGN KEY relationship has been added.')]; |
||
637 | } |
||
638 | |||
639 | $error = $this->dbi->getError(); |
||
640 | return [ |
||
641 | false, |
||
642 | __('Error: FOREIGN KEY relationship could not be added!') |
||
643 | . "<br/>" . $error |
||
644 | ]; |
||
645 | } |
||
646 | |||
647 | return [false, __('Error: Missing index on column(s).')]; |
||
648 | } |
||
649 | |||
650 | // internal (pmadb) relation |
||
651 | if ($GLOBALS['cfgRelation']['relwork'] == false) { |
||
652 | return [false, __('Error: Relational features are disabled!')]; |
||
653 | } |
||
654 | |||
655 | // no need to recheck if the keys are primary or unique at this point, |
||
656 | // this was checked on the interface part |
||
657 | |||
658 | $q = "INSERT INTO " |
||
659 | . Util::backquote($GLOBALS['cfgRelation']['db']) |
||
660 | . "." |
||
661 | . Util::backquote($GLOBALS['cfgRelation']['relation']) |
||
662 | . "(master_db, master_table, master_field, " |
||
663 | . "foreign_db, foreign_table, foreign_field)" |
||
664 | . " values(" |
||
665 | . "'" . $this->dbi->escapeString($DB2) . "', " |
||
666 | . "'" . $this->dbi->escapeString($T2) . "', " |
||
667 | . "'" . $this->dbi->escapeString($F2) . "', " |
||
668 | . "'" . $this->dbi->escapeString($DB1) . "', " |
||
669 | . "'" . $this->dbi->escapeString($T1) . "', " |
||
670 | . "'" . $this->dbi->escapeString($F1) . "')"; |
||
671 | |||
672 | if ($this->relation->queryAsControlUser($q, false, DatabaseInterface::QUERY_STORE) |
||
673 | ) { |
||
674 | return [true, __('Internal relationship has been added.')]; |
||
675 | } |
||
676 | |||
677 | $error = $this->dbi->getError(DatabaseInterface::CONNECT_CONTROL); |
||
678 | return [ |
||
679 | false, |
||
680 | __('Error: Internal relationship could not be added!') |
||
681 | . "<br/>" . $error |
||
682 | ]; |
||
683 | } |
||
684 | |||
685 | /** |
||
686 | * Removes a foreign relation |
||
687 | * |
||
688 | * @param string $T1 foreign db.table |
||
689 | * @param string $F1 foreign field |
||
690 | * @param string $T2 master db.table |
||
691 | * @param string $F2 master field |
||
692 | * |
||
693 | * @return array array of success/failure and message |
||
694 | */ |
||
695 | public function removeRelation($T1, $F1, $T2, $F2) |
||
696 | { |
||
697 | list($DB1, $T1) = explode(".", $T1); |
||
698 | list($DB2, $T2) = explode(".", $T2); |
||
699 | |||
700 | $tables = $this->dbi->getTablesFull($DB1, $T1); |
||
701 | $type_T1 = mb_strtoupper($tables[$T1]['ENGINE']); |
||
702 | $tables = $this->dbi->getTablesFull($DB2, $T2); |
||
703 | $type_T2 = mb_strtoupper($tables[$T2]['ENGINE']); |
||
704 | |||
705 | if (Util::isForeignKeySupported($type_T1) |
||
706 | && Util::isForeignKeySupported($type_T2) |
||
707 | && $type_T1 == $type_T2 |
||
708 | ) { |
||
709 | // InnoDB |
||
710 | $existrel_foreign = $this->relation->getForeigners($DB2, $T2, '', 'foreign'); |
||
711 | $foreigner = $this->relation->searchColumnInForeigners($existrel_foreign, $F2); |
||
712 | |||
713 | if (isset($foreigner['constraint'])) { |
||
714 | $upd_query = 'ALTER TABLE ' . Util::backquote($DB2) |
||
715 | . '.' . Util::backquote($T2) . ' DROP FOREIGN KEY ' |
||
716 | . Util::backquote($foreigner['constraint']) . ';'; |
||
717 | if ($this->dbi->query($upd_query)) { |
||
718 | return [true, __('FOREIGN KEY relationship has been removed.')]; |
||
719 | } |
||
720 | |||
721 | $error = $this->dbi->getError(); |
||
722 | return [ |
||
723 | false, |
||
724 | __('Error: FOREIGN KEY relationship could not be removed!') |
||
725 | . "<br/>" . $error |
||
726 | ]; |
||
727 | } |
||
728 | } |
||
729 | |||
730 | // internal relations |
||
731 | $delete_query = "DELETE FROM " |
||
732 | . Util::backquote($GLOBALS['cfgRelation']['db']) . "." |
||
733 | . $GLOBALS['cfgRelation']['relation'] . " WHERE " |
||
734 | . "master_db = '" . $this->dbi->escapeString($DB2) . "'" |
||
735 | . " AND master_table = '" . $this->dbi->escapeString($T2) . "'" |
||
736 | . " AND master_field = '" . $this->dbi->escapeString($F2) . "'" |
||
737 | . " AND foreign_db = '" . $this->dbi->escapeString($DB1) . "'" |
||
738 | . " AND foreign_table = '" . $this->dbi->escapeString($T1) . "'" |
||
739 | . " AND foreign_field = '" . $this->dbi->escapeString($F1) . "'"; |
||
740 | |||
741 | $result = $this->relation->queryAsControlUser( |
||
742 | $delete_query, |
||
743 | false, |
||
744 | DatabaseInterface::QUERY_STORE |
||
745 | ); |
||
746 | |||
747 | if (!$result) { |
||
748 | $error = $this->dbi->getError(DatabaseInterface::CONNECT_CONTROL); |
||
749 | return [ |
||
750 | false, |
||
751 | __('Error: Internal relationship could not be removed!') . "<br/>" . $error |
||
752 | ]; |
||
753 | } |
||
754 | |||
755 | return [true, __('Internal relationship has been removed.')]; |
||
756 | } |
||
757 | |||
758 | /** |
||
759 | * Save value for a designer setting |
||
760 | * |
||
761 | * @param string $index setting |
||
762 | * @param string $value value |
||
763 | * |
||
764 | * @return bool whether the operation succeeded |
||
765 | */ |
||
766 | public function saveSetting($index, $value) |
||
767 | { |
||
768 | $cfgRelation = $this->relation->getRelationsParam(); |
||
769 | $cfgDesigner = [ |
||
770 | 'user' => $GLOBALS['cfg']['Server']['user'], |
||
771 | 'db' => $cfgRelation['db'], |
||
772 | 'table' => $cfgRelation['designer_settings'] |
||
773 | ]; |
||
774 | |||
775 | $success = true; |
||
776 | if ($GLOBALS['cfgRelation']['designersettingswork']) { |
||
777 | $orig_data_query = "SELECT settings_data" |
||
778 | . " FROM " . Util::backquote($cfgDesigner['db']) |
||
779 | . "." . Util::backquote($cfgDesigner['table']) |
||
780 | . " WHERE username = '" |
||
781 | . $this->dbi->escapeString($cfgDesigner['user']) . "';"; |
||
782 | |||
783 | $orig_data = $this->dbi->fetchSingleRow( |
||
784 | $orig_data_query, |
||
785 | 'ASSOC', |
||
786 | DatabaseInterface::CONNECT_CONTROL |
||
787 | ); |
||
788 | |||
789 | if (! empty($orig_data)) { |
||
790 | $orig_data = json_decode($orig_data['settings_data'], true); |
||
791 | $orig_data[$index] = $value; |
||
792 | $orig_data = json_encode($orig_data); |
||
793 | |||
794 | $save_query = "UPDATE " |
||
795 | . Util::backquote($cfgDesigner['db']) |
||
796 | . "." . Util::backquote($cfgDesigner['table']) |
||
797 | . " SET settings_data = '" . $orig_data . "'" |
||
798 | . " WHERE username = '" |
||
799 | . $this->dbi->escapeString($cfgDesigner['user']) . "';"; |
||
800 | |||
801 | $success = $this->relation->queryAsControlUser($save_query); |
||
802 | } else { |
||
803 | $save_data = [$index => $value]; |
||
804 | |||
805 | $query = "INSERT INTO " |
||
806 | . Util::backquote($cfgDesigner['db']) |
||
807 | . "." . Util::backquote($cfgDesigner['table']) |
||
808 | . " (username, settings_data)" |
||
809 | . " VALUES('" . $cfgDesigner['user'] . "'," |
||
810 | . " '" . json_encode($save_data) . "');"; |
||
811 | |||
812 | $success = $this->relation->queryAsControlUser($query); |
||
813 | } |
||
814 | } |
||
815 | |||
816 | return (bool) $success; |
||
817 | } |
||
818 | } |
||
819 |