2 /* vim: set expandtab sw=4 ts=4 sts=4: */
4 * Functions for the table relation page
10 * Generate dropdown choices
12 * @param string $dropdown_question Message to display
13 * @param string $select_name Name of the <select> field
14 * @param array $choices Choices for dropdown
15 * @param string $selected_value Selected value
17 * @return string The html code for existing value (for selected)
21 function PMA_generateDropdown(
22 $dropdown_question, $select_name, $choices, $selected_value
24 $html_output = (! empty($dropdown_question) ?
25 htmlspecialchars($dropdown_question) . ' ' : '')
26 . '<select name="' . htmlspecialchars($select_name) . '">' . "\n";
28 foreach ($choices as $one_value => $one_label) {
29 $html_output .= '<option value="' . htmlspecialchars($one_value) . '"';
30 if ($selected_value == $one_value) {
31 $html_output .= ' selected="selected" ';
33 $html_output .= '>' . htmlspecialchars($one_label) . '</option>' . "\n";
35 $html_output .= '</select>' . "\n";
41 * Split a string on backquote pairs
43 * @param string $text original string
45 * @return array containing the elements (and their surrounding backquotes)
49 function PMA_backquoteSplit($text)
52 $final_pos = /*overload*/mb_strlen($text) - 1;
54 while ($pos <= $final_pos) {
55 $first_backquote = /*overload*/mb_strpos($text, '`', $pos);
56 $second_backquote = /*overload*/mb_strpos($text, '`', $first_backquote +
1);
57 // after the second one, there might be another one which means
58 // this is an escaped backquote
59 if ($second_backquote < $final_pos && '`' == $text[$second_backquote +
1]) {
61 = /*overload*/mb_strpos($text, '`', $second_backquote +
2);
63 if (false === $first_backquote ||
false === $second_backquote) {
66 $elements[] = /*overload*/mb_substr(
67 $text, $first_backquote, $second_backquote - $first_backquote +
1
69 $pos = $second_backquote +
1;
75 * Returns the DROP query for a foreign key constraint
77 * @param string $table table of the foreign key
78 * @param string $fk foreign key name
80 * @return string DROP query for the foreign key constraint
82 function PMA_getSQLToDropForeignKey($table, $fk)
84 return 'ALTER TABLE ' . PMA_Util
::backquote($table)
85 . ' DROP FOREIGN KEY ' . PMA_Util
::backquote($fk) . ';';
89 * Returns the SQL query for foreign key constraint creation
91 * @param string $table table name
92 * @param array $field field names
93 * @param string $foreignDb foreign database name
94 * @param string $foreignTable foreign table name
95 * @param array $foreignField foreign field names
96 * @param string $name name of the constraint
97 * @param string $onDelete on delete action
98 * @param string $onUpdate on update action
100 * @return string SQL query for foreign key constraint creation
102 function PMA_getSQLToCreateForeignKey($table, $field, $foreignDb, $foreignTable,
103 $foreignField, $name = null, $onDelete = null, $onUpdate = null
105 $sql_query = 'ALTER TABLE ' . PMA_Util
::backquote($table) . ' ADD ';
106 // if user entered a constraint name
107 if (! empty($name)) {
108 $sql_query .= ' CONSTRAINT ' . PMA_Util
::backquote($name);
111 foreach ($field as $key => $one_field) {
112 $field[$key] = PMA_Util
::backquote($one_field);
114 foreach ($foreignField as $key => $one_field) {
115 $foreignField[$key] = PMA_Util
::backquote($one_field);
117 $sql_query .= ' FOREIGN KEY (' . implode(', ', $field) . ')'
118 . ' REFERENCES ' . PMA_Util
::backquote($foreignDb)
119 . '.' . PMA_Util
::backquote($foreignTable)
120 . '(' . implode(', ', $foreignField) . ')';
122 if (! empty($onDelete)) {
123 $sql_query .= ' ON DELETE ' . $onDelete;
125 if (! empty($onUpdate)) {
126 $sql_query .= ' ON UPDATE ' . $onUpdate;
134 * Creates and populates dropdowns to select foreign db/table/column
136 * @param string $name name of the dropdowns
137 * @param array $values dropdown values
138 * @param string|boolean $foreign value of the item to be selected
139 * @param string $title title to show on hovering the dropdown
141 * @return string HTML for the dropdown
143 function PMA_generateRelationalDropdown(
144 $name, $values = array(), $foreign = false, $title = ''
146 $html_output = '<select name="' . $name . '" title="' . $title . '">';
147 $html_output .= '<option value=""></option>';
150 foreach ($values as $value) {
151 $html_output .= '<option value="' . htmlspecialchars($value) . '"';
152 if ($foreign && $value == $foreign) {
153 $html_output .= ' selected="selected"';
156 $html_output .= '>' . htmlspecialchars($value) . '</option>';
159 if (is_string($foreign) && ! $seen_key) {
160 $html_output .= '<option value="' . htmlspecialchars($foreign) . '"'
161 . ' selected="selected">' . htmlspecialchars($foreign) . '</option>';
163 $html_output .= '</select>';
168 * Function to get html for the common form
170 * @param string $db current database
171 * @param string $table current table
172 * @param array $columns columns
173 * @param array $cfgRelation configuration relation
174 * @param string $tbl_storage_engine table storage engine
175 * @param array $existrel db, table, column
176 * @param array $existrel_foreign db, table, column
177 * @param array $options_array options array
181 function PMA_getHtmlForCommonForm($db, $table, $columns, $cfgRelation,
182 $tbl_storage_engine, $existrel, $existrel_foreign, $options_array
184 $html_output = PMA_getHtmlForCommonFormHeader($db, $table);
185 $html_output .= '<fieldset>'
186 . '<legend>' . __('Relations') . '</legend>';
188 if ($cfgRelation['relwork']) {
189 $html_output .= PMA_getHtmlForInternalRelationForm(
190 $columns, $tbl_storage_engine, $existrel, $db
194 if (PMA_Util
::isForeignKeySupported($tbl_storage_engine)) {
195 $html_output .= PMA_getHtmlForForeignKeyForm(
196 $columns, $existrel_foreign,
197 $db, $tbl_storage_engine, $options_array
201 $html_output .= '</fieldset>';
203 if ($cfgRelation['displaywork']) {
204 $html_output .= PMA_getHtmlForDisplayFieldInfos(
206 array_values($columns)
210 $html_output .= PMA_getHtmlForCommonFormFooter();
216 * Function to get html for Internal relations form
218 * @param array $columns columns
219 * @param string $tbl_storage_engine table storage engine
220 * @param array $existrel db, table, column
221 * @param string $db current database
225 function PMA_getHtmlForInternalRelationForm($columns, $tbl_storage_engine,
228 $save_row = array_values($columns);
229 $saved_row_cnt = count($save_row);
231 $html_output = '<fieldset>'
232 . '<legend>' . __('Internal relations') . '</legend>'
233 . '<table id="internal_relations" class="relationalTable">';
235 $html_output .= '<tr><th>' . __('Column') . '</th>';
237 $html_output .= '<th>' . __('Internal relation');
238 if (PMA_Util
::isForeignKeySupported($tbl_storage_engine)) {
239 $html_output .= PMA_Util
::showHint(
241 'An internal relation is not necessary when a corresponding'
242 . ' FOREIGN KEY relation exists.'
247 $html_output .= '</th>';
250 for ($i = 0; $i < $saved_row_cnt; $i++
) {
251 $html_output .= PMA_getHtmlForInternalRelationRow(
252 $save_row, $i, $odd_row,
255 $odd_row = ! $odd_row;
258 $html_output .= '</table>';
259 $html_output .= '</fieldset>';
265 * Function to get html for an entire row in common form
267 * @param array $save_row save row
268 * @param int $i counter
269 * @param bool $odd_row whether odd row or not
270 * @param array $existrel db, table, column
271 * @param string $db current db
275 function PMA_getHtmlForInternalRelationRow($save_row, $i, $odd_row,
278 $myfield = $save_row[$i]['Field'];
279 // Use an md5 as array index to avoid having special characters
280 // in the name attribute (see bug #1746964 )
281 $myfield_md5 = md5($myfield);
282 $myfield_html = htmlspecialchars($myfield);
284 $html_output = '<tr class="' . ($odd_row ?
'odd' : 'even') . '">'
285 . '<td class="center">'
286 . '<strong>' . $myfield_html . '</strong>'
287 . '<input type="hidden" name="fields_name[' . $myfield_md5 . ']"'
288 . ' value="' . $myfield_html . '"/>'
291 $html_output .= '<td>';
293 $foreign_table = false;
294 $foreign_column = false;
297 if (isset($existrel[$myfield])) {
298 $foreign_db = $existrel[$myfield]['foreign_db'];
302 $html_output .= PMA_generateRelationalDropdown(
303 'destination_db[' . $myfield_md5 . ']',
304 $GLOBALS['pma']->databases
,
308 // end of database dropdown
313 if (isset($existrel[$myfield])) {
314 $foreign_table = $existrel[$myfield]['foreign_table'];
316 $tables_rs = $GLOBALS['dbi']->query(
317 'SHOW TABLES FROM ' . PMA_Util
::backquote($foreign_db),
319 PMA_DatabaseInterface
::QUERY_STORE
321 while ($row = $GLOBALS['dbi']->fetchRow($tables_rs)) {
325 $html_output .= PMA_generateRelationalDropdown(
326 'destination_table[' . $myfield_md5 . ']',
331 // end of table dropdown
335 if ($foreign_db && $foreign_table) {
336 if (isset($existrel[$myfield])) {
337 $foreign_column = $existrel[$myfield]['foreign_field'];
339 $table_obj = new PMA_Table($foreign_table, $foreign_db);
340 $columns = $table_obj->getUniqueColumns(false, false);
342 $html_output .= PMA_generateRelationalDropdown(
343 'destination_column[' . $myfield_md5 . ']',
348 // end of column dropdown
350 $html_output .= '</td>';
351 $html_output .= '</tr>';
357 * Function to get html for Foreign key form
359 * @param array $columns columns
360 * @param array $existrel_foreign db, table, column
361 * @param string $db current database
362 * @param string $tbl_storage_engine table storage engine
363 * @param array $options_array options array
367 function PMA_getHtmlForForeignKeyForm($columns, $existrel_foreign, $db,
368 $tbl_storage_engine, $options_array
370 $html_output = '<fieldset>'
371 . '<legend>' . __('Foreign key constraints') . '</legend>'
372 . '<table id="foreign_keys" class="relationalTable">';
374 $html_output .= '<tr><th>' . __('Actions') . '</th>';
375 $html_output .= '<th>' . __('Constraint properties') . '</th>'
378 . PMA_Util
::showHint(
380 'Only columns with index will be displayed. You can define an'
385 $html_output .= '<th colspan="3">' . __('Foreign key constraint')
386 . ' (' . $tbl_storage_engine . ')';
387 $html_output .= '</th></tr>';
391 foreach ($existrel_foreign as $key => $one_key) {
392 $html_output .= PMA_getHtmlForForeignKeyRow(
393 $one_key, $odd_row, $columns, $i++
, $options_array, $tbl_storage_engine,
396 $odd_row = ! $odd_row;
398 $html_output .= PMA_getHtmlForForeignKeyRow(
399 array(), $odd_row, $columns, $i++
, $options_array, $tbl_storage_engine,
403 $html_output .= '<tr>'
404 . '<td colspan="5"><a class="formelement clearfloat'
405 . ' add_foreign_key" href="">'
406 . __('+ Add constraint')
408 $html_output .= '</tr>';
409 $html_output .= '</table>'
416 * Function to get html for an entire row in foreign key form
418 * @param array $one_key Single foreign key constraint
419 * @param bool $odd_row whether odd or even row
420 * @param array $columns Array of table columns
421 * @param int $i Row number
422 * @param array $options_array Options array
423 * @param string $tbl_storage_engine table storage engine
424 * @param string $db Database
426 * @return string html
428 function PMA_getHtmlForForeignKeyRow($one_key, $odd_row, $columns, $i,
429 $options_array, $tbl_storage_engine, $db
431 $html_output = '<tr class="' . ($odd_row ?
'odd' : 'even') . '">';
433 $html_output .= '<td>';
434 if (isset($one_key['constraint'])) {
435 $drop_fk_query = 'ALTER TABLE ' . PMA_Util
::backquote($GLOBALS['table'])
436 . ' DROP FOREIGN KEY '
437 . PMA_Util
::backquote($one_key['constraint']) . ';';
438 $this_params = $GLOBALS['url_params'];
439 $this_params['goto'] = 'tbl_relation.php';
440 $this_params['back'] = 'tbl_relation.php';
441 $this_params['sql_query'] = $drop_fk_query;
442 $this_params['message_to_show'] = sprintf(
443 __('Foreign key constraint %s has been dropped'),
444 $one_key['constraint']
446 $js_msg = PMA_jsFormat(
447 'ALTER TABLE ' . $GLOBALS['table']
448 . ' DROP FOREIGN KEY '
449 . $one_key['constraint'] . ';'
452 $html_output .= '<input type="hidden" class="drop_foreign_key_msg"'
453 . ' value="' . $js_msg . '" />';
454 $html_output .= ' <a class="drop_foreign_key_anchor';
455 $html_output .= ' ajax';
456 $html_output .= '" href="sql.php' . PMA_URL_getCommon($this_params)
458 . PMA_Util
::getIcon('b_drop.png', __('Drop')) . '</a>';
460 $html_output .= '</td>';
461 $html_output .= '<td>';
462 $html_output .= '<span class="formelement clearfloat">';
463 $constraint_name = isset($one_key['constraint'])
464 ?
$one_key['constraint'] : '';
465 $html_output .= '<input type="text" name="constraint_name[' . $i . ']"'
466 . ' value="' . htmlspecialchars($constraint_name) . '"'
467 . ' placeholder="' . __('Constraint name') . '" />';
468 $html_output .= '</span>' . "\n";
470 $html_output .= '<span class="formelement clearfloat">';
471 // For ON DELETE and ON UPDATE, the default action
472 // is RESTRICT as per MySQL doc; however, a SHOW CREATE TABLE
473 // won't display the clause if it's set as RESTRICT.
474 $on_delete = isset($one_key['on_delete'])
475 ?
$one_key['on_delete'] : 'RESTRICT';
476 $html_output .= PMA_generateDropdown(
478 'on_delete[' . $i . ']',
482 $html_output .= '</span>' . "\n";
484 $html_output .= '<span class="formelement clearfloat">' . "\n";
485 $on_update = isset($one_key['on_update'])
486 ?
$one_key['on_update'] : 'RESTRICT';
487 $html_output .= PMA_generateDropdown(
489 'on_update[' . $i . ']',
493 $html_output .= '</span>';
495 $column_array = array();
496 $column_array[''] = '';
497 foreach ($columns as $column) {
498 if (! empty($column['Key'])) {
499 $column_array[$column['Field']] = $column['Field'];
503 $html_output .= '</span>' . "\n";
504 $html_output .= '</td>';
505 $html_output .= '<td>';
506 if (isset($one_key['index_list'])) {
507 foreach ($one_key['index_list'] as $key => $column) {
508 $html_output .= '<span class="formelement clearfloat">';
509 $html_output .= PMA_generateDropdown(
511 'foreign_key_fields_name[' . $i . '][]',
515 $html_output .= '</span>';
518 $html_output .= '<span class="formelement clearfloat">';
519 $html_output .= PMA_generateDropdown(
521 'foreign_key_fields_name[' . $i . '][]',
525 $html_output .= '</span>';
528 $html_output .= '<a class="formelement clearfloat add_foreign_key_field"'
529 . ' href="" data-index="' . $i . '">'
532 $html_output .= '</td>';
533 $html_output .= '<td>';
534 $foreign_table = false;
536 // foreign database dropdown
537 $foreign_db = (isset($one_key['ref_db_name'])) ?
$one_key['ref_db_name'] : $db;
538 $html_output .= '<span class="formelement clearfloat">';
539 $html_output .= PMA_generateRelationalDropdown(
540 'destination_foreign_db[' . $i . ']',
541 $GLOBALS['pma']->databases
,
545 // end of foreign database dropdown
546 $html_output .= '</td>';
547 $html_output .= '<td>';
548 // foreign table dropdown
551 $foreign_table = isset($one_key['ref_table_name'])
552 ?
$one_key['ref_table_name'] : '';
554 // In Drizzle, 'SHOW TABLE STATUS' will show status only for the tables
555 // which are currently in the table cache. Hence we have to use
556 // 'SHOW TABLES' and manualy retrieve table engine values.
558 $tables_rs = $GLOBALS['dbi']->query(
559 'SHOW TABLES FROM ' . PMA_Util
::backquote($foreign_db),
561 PMA_DatabaseInterface
::QUERY_STORE
563 while ($row = $GLOBALS['dbi']->fetchArray($tables_rs)) {
564 $engine = PMA_Table
::sGetStatusInfo(
570 && /*overload*/mb_strtoupper($engine) == $tbl_storage_engine
576 $tables_rs = $GLOBALS['dbi']->query(
577 'SHOW TABLE STATUS FROM ' . PMA_Util
::backquote($foreign_db),
579 PMA_DatabaseInterface
::QUERY_STORE
581 while ($row = $GLOBALS['dbi']->fetchRow($tables_rs)) {
583 && /*overload*/mb_strtoupper($row[1]) == $tbl_storage_engine
590 $html_output .= '<span class="formelement clearfloat">';
591 $html_output .= PMA_generateRelationalDropdown(
592 'destination_foreign_table[' . $i . ']',
597 $html_output .= '</span>';
598 // end of foreign table dropdown
599 $html_output .= '</td>';
600 $html_output .= '<td>';
601 // foreign column dropdown
602 if ($foreign_db && $foreign_table) {
603 foreach ($one_key['ref_index_list'] as $foreign_column) {
604 $table_obj = new PMA_Table($foreign_table, $foreign_db);
605 $columns = $table_obj->getUniqueColumns(false, false);
606 $html_output .= '<span class="formelement clearfloat">';
607 $html_output .= PMA_generateRelationalDropdown(
608 'destination_foreign_column[' . $i . '][]',
613 $html_output .= '</span>';
616 $html_output .= '<span class="formelement clearfloat">';
617 $html_output .= PMA_generateRelationalDropdown(
618 'destination_foreign_column[' . $i . '][]',
623 $html_output .= '</span>';
625 // end of foreign column dropdown
626 $html_output .= '</td>';
627 $html_output .= '</tr>';
633 * Function to get html for the common form header
635 * @param string $db current database
636 * @param string $table current table
640 function PMA_getHtmlForCommonFormHeader($db, $table)
642 return '<form method="post" action="tbl_relation.php">' . "\n"
643 . PMA_URL_getHiddenInputs($db, $table);
647 * Function to get html for the common form footer
651 function PMA_getHtmlForCommonFormFooter()
653 return '<fieldset class="tblFooters">'
654 . '<input type="button" class="preview_sql" value="'
655 . __('Preview SQL') . '" />'
656 . '<input type="submit" value="' . __('Save') . '" />'
662 * Function to get html for display field infos
664 * @param string $db current database
665 * @param string $table current table
666 * @param array $save_row save row
670 function PMA_getHtmlForDisplayFieldInfos($db, $table, $save_row)
672 $disp = PMA_getDisplayField($db, $table);
673 $html_output = '<fieldset>'
674 . '<label>' . __('Choose column to display:') . '</label>'
675 . '<select name="display_field">'
676 . '<option value="">---</option>';
678 foreach ($save_row as $row) {
679 $html_output .= '<option value="'
680 . htmlspecialchars($row['Field']) . '"';
681 if (isset($disp) && $row['Field'] == $disp) {
682 $html_output .= ' selected="selected"';
684 $html_output .= '>' . htmlspecialchars($row['Field'])
685 . '</option>' . "\n";
688 $html_output .= '</select>'
695 * Function to send html for table or column dropdown list
699 function PMA_sendHtmlForTableOrColumnDropdownList()
701 if (isset($_REQUEST['foreignTable'])) { // if both db and table are selected
702 PMA_sendHtmlForColumnDropdownList();
703 } else { // if only the db is selected
704 PMA_sendHtmlForTableDropdownList();
710 * Function to send html for column dropdown list
714 function PMA_sendHtmlForColumnDropdownList()
716 $response = PMA_Response
::getInstance();
718 $foreignTable = $_REQUEST['foreignTable'];
719 $table_obj = new PMA_Table($foreignTable, $_REQUEST['foreignDb']);
721 foreach ($table_obj->getIndexedColumns(false, false) as $column) {
722 $columns[] = htmlspecialchars($column);
724 $response->addJSON('columns', $columns);
726 // @todo should be: $server->db($db)->table($table)->primary()
727 $primary = PMA_Index
::getPrimary($foreignTable, $_REQUEST['foreignDb']);
728 if (false === $primary) {
732 $primarycols = array_keys($primary->getColumns());
733 $response->addJSON('primary', $primarycols);
737 * Function to send html for table dropdown list
741 function PMA_sendHtmlForTableDropdownList()
743 $response = PMA_Response
::getInstance();
746 $foreign = isset($_REQUEST['foreign']) && $_REQUEST['foreign'] === 'true';
748 $tbl_storage_engine = /*overload*/mb_strtoupper(
749 PMA_Table
::sGetStatusInfo(
757 // In Drizzle, 'SHOW TABLE STATUS' will show status only for the tables
758 // which are currently in the table cache. Hence we have to use 'SHOW TABLES'
759 // and manually retrieve table engine values.
760 if ($foreign && ! PMA_DRIZZLE
) {
761 $query = 'SHOW TABLE STATUS FROM '
762 . PMA_Util
::backquote($_REQUEST['foreignDb']);
763 $tables_rs = $GLOBALS['dbi']->query(
766 PMA_DatabaseInterface
::QUERY_STORE
769 while ($row = $GLOBALS['dbi']->fetchArray($tables_rs)) {
770 if (isset($row['Engine'])
771 && /*overload*/mb_strtoupper($row['Engine']) == $tbl_storage_engine
773 $tables[] = htmlspecialchars($row['Name']);
777 $query = 'SHOW TABLES FROM '
778 . PMA_Util
::backquote($_REQUEST['foreignDb']);
779 $tables_rs = $GLOBALS['dbi']->query(
782 PMA_DatabaseInterface
::QUERY_STORE
784 while ($row = $GLOBALS['dbi']->fetchArray($tables_rs)) {
785 if ($foreign && PMA_DRIZZLE
) {
786 $engine = /*overload*/mb_strtoupper(
787 PMA_Table
::sGetStatusInfo(
788 $_REQUEST['foreignDb'],
793 if (isset($engine) && $engine == $tbl_storage_engine) {
794 $tables[] = htmlspecialchars($row[0]);
797 $tables[] = htmlspecialchars($row[0]);
801 $response->addJSON('tables', $tables);
805 * Function to handle update for display field
807 * @param string $disp current display field
808 * @param string $display_field display field
809 * @param string $db current database
810 * @param string $table current table
811 * @param array $cfgRelation configuration relation
815 function PMA_handleUpdateForDisplayField($disp, $display_field, $db, $table,
819 $upd_query = PMA_getQueryForDisplayUpdate(
820 $disp, $display_field, $db, $table, $cfgRelation
823 PMA_queryAsControlUser($upd_query);
824 $html_output = PMA_Util
::getMessage(
825 __('Display column was successfully updated.'),
833 * Function to get display query for handlingdisplay update
835 * @param string $disp current display field
836 * @param string $display_field display field
837 * @param string $db current database
838 * @param string $table current table
839 * @param array $cfgRelation configuration relation
843 function PMA_getQueryForDisplayUpdate($disp, $display_field, $db, $table,
848 if ($display_field == '') {
849 $upd_query = 'DELETE FROM '
850 . PMA_Util
::backquote($GLOBALS['cfgRelation']['db'])
851 . '.' . PMA_Util
::backquote($cfgRelation['table_info'])
852 . ' WHERE db_name = \'' . PMA_Util
::sqlAddSlashes($db) . '\''
853 . ' AND table_name = \'' . PMA_Util
::sqlAddSlashes($table) . '\'';
854 } elseif ($disp != $display_field) {
855 $upd_query = 'UPDATE '
856 . PMA_Util
::backquote($GLOBALS['cfgRelation']['db'])
857 . '.' . PMA_Util
::backquote($cfgRelation['table_info'])
858 . ' SET display_field = \''
859 . PMA_Util
::sqlAddSlashes($display_field) . '\''
860 . ' WHERE db_name = \'' . PMA_Util
::sqlAddSlashes($db) . '\''
861 . ' AND table_name = \'' . PMA_Util
::sqlAddSlashes($table) . '\'';
863 } elseif ($display_field != '') {
864 $upd_query = 'INSERT INTO '
865 . PMA_Util
::backquote($GLOBALS['cfgRelation']['db'])
866 . '.' . PMA_Util
::backquote($cfgRelation['table_info'])
867 . '(db_name, table_name, display_field) VALUES('
868 . '\'' . PMA_Util
::sqlAddSlashes($db) . '\','
869 . '\'' . PMA_Util
::sqlAddSlashes($table) . '\','
870 . '\'' . PMA_Util
::sqlAddSlashes($display_field) . '\')';
877 * Function to handle updates for internal relations
879 * @param string $destination_db destination database
880 * @param string $multi_edit_columns_name multi edit column name
881 * @param string $destination_table destination table
882 * @param string $destination_column destination column
883 * @param array $cfgRelation configuration relation
884 * @param string $db current database
885 * @param string $table current table
886 * @param array|null $existrel db, table, column
890 function PMA_handleUpdatesForInternalRelations($destination_db,
891 $multi_edit_columns_name, $destination_table, $destination_column, $cfgRelation,
892 $db, $table, $existrel
896 foreach ($destination_db as $master_field_md5 => $foreign_db) {
897 $upd_query = PMA_getQueryForInternalRelationUpdate(
898 $multi_edit_columns_name,
899 $master_field_md5, $foreign_db, $destination_table, $destination_column,
900 $cfgRelation, $db, $table, isset($existrel) ?
$existrel : null
903 PMA_queryAsControlUser($upd_query);
908 $html_output = PMA_Util
::getMessage(
909 __('Internal relations were successfully updated.'),
917 * Function to get update query for updating internal relations
919 * @param string $multi_edit_columns_name multi edit column names
920 * @param string $master_field_md5 master field md5
921 * @param string $foreign_db foreign database
922 * @param string $destination_table destination table
923 * @param string $destination_column destination column
924 * @param array $cfgRelation configuration relation
925 * @param string $db current database
926 * @param string $table current table
927 * @param array|null $existrel db, table, column
931 function PMA_getQueryForInternalRelationUpdate($multi_edit_columns_name,
932 $master_field_md5, $foreign_db, $destination_table, $destination_column,
933 $cfgRelation, $db, $table, $existrel
937 // Map the fieldname's md5 back to its real name
938 $master_field = $multi_edit_columns_name[$master_field_md5];
940 $foreign_table = $destination_table[$master_field_md5];
941 $foreign_field = $destination_column[$master_field_md5];
942 if (! empty($foreign_db)
943 && ! empty($foreign_table)
944 && ! empty($foreign_field)
946 if (! isset($existrel[$master_field])) {
947 $upd_query = 'INSERT INTO '
948 . PMA_Util
::backquote($GLOBALS['cfgRelation']['db'])
949 . '.' . PMA_Util
::backquote($cfgRelation['relation'])
950 . '(master_db, master_table, master_field, foreign_db,'
951 . ' foreign_table, foreign_field)'
953 . '\'' . PMA_Util
::sqlAddSlashes($db) . '\', '
954 . '\'' . PMA_Util
::sqlAddSlashes($table) . '\', '
955 . '\'' . PMA_Util
::sqlAddSlashes($master_field) . '\', '
956 . '\'' . PMA_Util
::sqlAddSlashes($foreign_db) . '\', '
957 . '\'' . PMA_Util
::sqlAddSlashes($foreign_table) . '\','
958 . '\'' . PMA_Util
::sqlAddSlashes($foreign_field) . '\')';
960 } elseif ($existrel[$master_field]['foreign_db'] != $foreign_db
961 ||
$existrel[$master_field]['foreign_table'] != $foreign_table
962 ||
$existrel[$master_field]['foreign_field'] != $foreign_field
964 $upd_query = 'UPDATE '
965 . PMA_Util
::backquote($GLOBALS['cfgRelation']['db'])
966 . '.' . PMA_Util
::backquote($cfgRelation['relation']) . ' SET'
968 . PMA_Util
::sqlAddSlashes($foreign_db) . '\', '
969 . ' foreign_table = \''
970 . PMA_Util
::sqlAddSlashes($foreign_table) . '\', '
971 . ' foreign_field = \''
972 . PMA_Util
::sqlAddSlashes($foreign_field) . '\' '
973 . ' WHERE master_db = \''
974 . PMA_Util
::sqlAddSlashes($db) . '\''
975 . ' AND master_table = \''
976 . PMA_Util
::sqlAddSlashes($table) . '\''
977 . ' AND master_field = \''
978 . PMA_Util
::sqlAddSlashes($master_field) . '\'';
979 } // end if... else....
980 } elseif (isset($existrel[$master_field])) {
981 $upd_query = 'DELETE FROM '
982 . PMA_Util
::backquote($GLOBALS['cfgRelation']['db'])
983 . '.' . PMA_Util
::backquote($cfgRelation['relation'])
984 . ' WHERE master_db = \'' . PMA_Util
::sqlAddSlashes($db) . '\''
985 . ' AND master_table = \'' . PMA_Util
::sqlAddSlashes($table) . '\''
986 . ' AND master_field = \'' . PMA_Util
::sqlAddSlashes($master_field)
988 } // end if... else....
994 * Function to handle foreign key updates
996 * @param string $destination_foreign_db destination foreign database
997 * @param array $multi_edit_columns_name multi edit column names
998 * @param array $destination_foreign_table destination foreign table
999 * @param array $destination_foreign_column destination foreign column
1000 * @param array $options_array options array
1001 * @param string $table current table
1002 * @param array $existrel_foreign db, table, column
1006 function PMA_handleUpdatesForForeignKeys($destination_foreign_db,
1007 $multi_edit_columns_name, $destination_foreign_table,
1008 $destination_foreign_column, $options_array, $table, $existrel_foreign
1011 $preview_sql_data = '';
1012 $display_query = '';
1013 $seen_error = false;
1014 $preview_sql = (isset($_REQUEST['preview_sql'])) ?
true : false;
1015 foreach ($destination_foreign_db as $master_field_md5 => $foreign_db) {
1016 list($html, $sql_data) = PMA_handleUpdateForForeignKey(
1017 $multi_edit_columns_name, $master_field_md5,
1018 $destination_foreign_table, $destination_foreign_column, $options_array,
1019 $existrel_foreign, $table, $seen_error, $display_query, $foreign_db,
1022 $html_output .= $html;
1023 $preview_sql_data .= $sql_data;
1026 // If there is a request for SQL previewing.
1028 PMA_previewSQL($preview_sql_data);
1031 if (! empty($display_query) && ! $seen_error) {
1032 $GLOBALS['display_query'] = $display_query;
1033 $html_output = PMA_Util
::getMessage(
1034 __('Your SQL query has been executed successfully.'),
1039 return $html_output;
1043 * Function to handle update for a foreign key
1045 * @param array $multi_edit_columns_name multi edit columns name
1046 * @param string $master_field_md5 master field md5
1047 * @param array $destination_foreign_table destination foreign table
1048 * @param array $destination_foreign_column destination foreign column
1049 * @param array $options_array options array
1050 * @param array $existrel_foreign db, table, column
1051 * @param string $table current table
1052 * @param bool &$seen_error whether seen error
1053 * @param string &$display_query display query
1054 * @param string $foreign_db foreign database
1055 * @param bool $preview_sql preview sql before executing
1059 function PMA_handleUpdateForForeignKey($multi_edit_columns_name, $master_field_md5,
1060 $destination_foreign_table, $destination_foreign_column, $options_array,
1061 $existrel_foreign, $table, &$seen_error, &$display_query,
1062 $foreign_db, $preview_sql
1065 $preview_sql_data = '';
1069 // Map the fieldname's md5 back to its real name
1070 $master_field = $multi_edit_columns_name[$master_field_md5];
1072 $foreign_table = $destination_foreign_table[$master_field_md5];
1073 $foreign_field = $destination_foreign_column[$master_field_md5];
1075 if (isset($existrel_foreign[$master_field_md5]['ref_db_name'])) {
1076 $ref_db_name = $existrel_foreign[$master_field_md5]['ref_db_name'];
1078 $ref_db_name = $GLOBALS['db'];
1081 $empty_fields = false;
1082 foreach ($master_field as $key => $one_field) {
1083 if ((! empty($one_field) && empty($foreign_field[$key]))
1084 ||
(empty($one_field) && ! empty($foreign_field[$key]))
1086 $empty_fields = true;
1089 if (empty($one_field) && empty($foreign_field[$key])) {
1090 unset($master_field[$key]);
1091 unset($foreign_field[$key]);
1095 if (! empty($foreign_db)
1096 && ! empty($foreign_table)
1099 if (isset($existrel_foreign[$master_field_md5])) {
1100 $constraint_name = $existrel_foreign[$master_field_md5]['constraint'];
1101 $on_delete = ! empty(
1102 $existrel_foreign[$master_field_md5]['on_delete'])
1103 ?
$existrel_foreign[$master_field_md5]['on_delete']
1105 $on_update = ! empty(
1106 $existrel_foreign[$master_field_md5]['on_update'])
1107 ?
$existrel_foreign[$master_field_md5]['on_update']
1111 if (! isset($existrel_foreign[$master_field_md5])) {
1112 // no key defined for this field(s)
1114 } elseif ($ref_db_name != $foreign_db
1115 ||
$existrel_foreign[$master_field_md5]['ref_table_name'] != $foreign_table
1116 ||
$existrel_foreign[$master_field_md5]['ref_index_list'] != $foreign_field
1117 ||
$existrel_foreign[$master_field_md5]['index_list'] != $master_field
1118 ||
$_REQUEST['constraint_name'][$master_field_md5] != $constraint_name
1119 ||
($_REQUEST['on_delete'][$master_field_md5] != $on_delete)
1120 ||
($_REQUEST['on_update'][$master_field_md5] != $on_update)
1122 // another foreign key is already defined for this field
1123 // or an option has been changed for ON DELETE or ON UPDATE
1126 } // end if... else....
1127 } elseif (isset($existrel_foreign[$master_field_md5])) {
1129 } // end if... else....
1131 $tmp_error_drop = false;
1133 $drop_query = PMA_getSQLToDropForeignKey(
1134 $table, $existrel_foreign[$master_field_md5]['constraint']
1137 if (! $preview_sql) {
1138 $display_query .= $drop_query . "\n";
1139 $GLOBALS['dbi']->tryQuery($drop_query);
1140 $tmp_error_drop = $GLOBALS['dbi']->getError();
1142 if (! empty($tmp_error_drop)) {
1144 $html_output .= PMA_Util
::mysqlDie(
1145 $tmp_error_drop, $drop_query, false, '', false
1147 return $html_output;
1150 $preview_sql_data .= $drop_query . "\n";
1153 $tmp_error_create = false;
1155 return array($html_output, $preview_sql_data);
1158 $create_query = PMA_getSQLToCreateForeignKey(
1159 $table, $master_field, $foreign_db, $foreign_table, $foreign_field,
1160 $_REQUEST['constraint_name'][$master_field_md5],
1161 $options_array[$_REQUEST['on_delete'][$master_field_md5]],
1162 $options_array[$_REQUEST['on_update'][$master_field_md5]]
1165 if (! $preview_sql) {
1166 $display_query .= $create_query . "\n";
1167 $GLOBALS['dbi']->tryQuery($create_query);
1168 $tmp_error_create = $GLOBALS['dbi']->getError();
1169 if (! empty($tmp_error_create)) {
1172 if (substr($tmp_error_create, 1, 4) == '1005') {
1173 $message = PMA_Message
::error(
1174 __('Error creating foreign key on %1$s (check data types)')
1176 $message->addParam(implode(', ', $master_field));
1177 $html_output .= $message->getDisplay();
1179 $html_output .= PMA_Util
::mysqlDie(
1180 $tmp_error_create, $create_query, false, '', false
1183 $html_output .= PMA_Util
::showMySQLDocu(
1184 'InnoDB_foreign_key_constraints'
1188 $preview_sql_data .= $create_query . "\n";
1191 // this is an alteration and the old constraint has been dropped
1192 // without creation of a new one
1193 if ($drop && $create && empty($tmp_error_drop)
1194 && ! empty($tmp_error_create)
1196 // a rollback may be better here
1197 $sql_query_recreate = '# Restoring the dropped constraint...' . "\n";
1198 $sql_query_recreate .= PMA_getSQLToCreateForeignKey(
1201 $existrel_foreign[$master_field_md5]['ref_db_name'],
1202 $existrel_foreign[$master_field_md5]['ref_table_name'],
1203 $existrel_foreign[$master_field_md5]['ref_index_list'],
1204 $existrel_foreign[$master_field_md5]['constraint'],
1205 $options_array[$existrel_foreign[$master_field_md5]['on_delete']],
1206 $options_array[$existrel_foreign[$master_field_md5]['on_update']]
1208 if (! $preview_sql) {
1209 $display_query .= $sql_query_recreate . "\n";
1210 $GLOBALS['dbi']->tryQuery($sql_query_recreate);
1212 $preview_sql_data .= $sql_query_recreate;
1216 return array($html_output, $preview_sql_data);