tbl_relation.php

   1 <?php
   2 /* vim: set expandtab sw=4 ts=4 sts=4: */
   3 /**
   4  * Display table relations for viewing and editing
   5  *
   6  * includes phpMyAdmin relations and InnoDB relations
   7  *
   8  * @todo fix name handling: currently names with dots (.) are not properly handled
   9  * for internal relations (but foreign keys relations are correct)
  10  * @todo foreign key constraints require both fields being of equal type and size
  11  * @todo check foreign fields to be from same type and size, all other makes no sense
  12  * @todo if above todos are fullfilled we can add all fields meet requirements
  13  * in the select dropdown
  14  * @package PhpMyAdmin
  15  */
  16
  17 /**
  18  * Gets some core libraries
  19  */
  20 require_once 'libraries/common.inc.php';
  21 require_once 'libraries/index.lib.php';
  22 require_once 'libraries/Template.class.php';
  23 require_once 'libraries/Table.class.php';
  24 require_once 'libraries/structure.lib.php';
  25
  26 $response = PMA_Response::getInstance();
  27
  28 // Send table of column names to populate corresponding dropdowns depending
  29 // on the current selection
  30 if (isset($_REQUEST['getDropdownValues'])
  31     && $_REQUEST['getDropdownValues'] === 'true'
  32 ) {
  33     if (isset($_REQUEST['foreignTable'])) { // if both db and table are selected
  34         $foreignTable = $_REQUEST['foreignTable'];
  35         $table_obj = new PMA_Table($foreignTable, $_REQUEST['foreignDb']);
  36         // Since views do not have keys defined on them provide the full list of columns
  37         if (PMA_Table::isView($_REQUEST['foreignDb'], $foreignTable)) {
  38             $columnList = $table_obj->getColumns(false, false);
  39         } else {
  40             $columnList = $table_obj->getIndexedColumns(false, false);
  41         }
  42         $columns = array();
  43         foreach ($columnList as $column) {
  44             $columns[] = htmlspecialchars($column);
  45         }
  46         $response->addJSON('columns', $columns);
  47
  48         // @todo should be: $server->db($db)->table($table)->primary()
  49         $primary = PMA_Index::getPrimary($foreignTable, $_REQUEST['foreignDb']);
  50         if (false === $primary) {
  51             return;
  52         }
  53
  54         $primarycols = array_keys($primary->getColumns());
  55         $response->addJSON('primary', $primarycols);
  56     } else { // if only the db is selected
  57         $tables = array();
  58         $foreign = isset($_REQUEST['foreign']) && $_REQUEST['foreign'] === 'true';
  59         if ($foreign) {
  60             $tbl_storage_engine = /*overload*/mb_strtoupper(
  61                 PMA_Table::sGetStatusInfo(
  62                     $_REQUEST['db'],
  63                     $_REQUEST['table'],
  64                     'Engine'
  65                 )
  66             );
  67         }
  68
  69         // In Drizzle, 'SHOW TABLE STATUS' will show status only for the tables
  70         // which are currently in the table cache. Hence we have to use 'SHOW TABLES'
  71         // and manually retrieve table engine values.
  72         if ($foreign && ! PMA_DRIZZLE) {
  73             $query = 'SHOW TABLE STATUS FROM '
  74                 . PMA_Util::backquote($_REQUEST['foreignDb']);
  75             $tables_rs = $GLOBALS['dbi']->query(
  76                 $query,
  77                 null,
  78                 PMA_DatabaseInterface::QUERY_STORE
  79             );
  80
  81             while ($row = $GLOBALS['dbi']->fetchArray($tables_rs)) {
  82                 if (isset($row['Engine'])
  83                     && /*overload*/mb_strtoupper($row['Engine']) == $tbl_storage_engine
  84                 ) {
  85                     $tables[] = htmlspecialchars($row['Name']);
  86                 }
  87             }
  88         } else {
  89             $query = 'SHOW TABLES FROM '
  90                 . PMA_Util::backquote($_REQUEST['foreignDb']);
  91             $tables_rs = $GLOBALS['dbi']->query(
  92                 $query,
  93                 null,
  94                 PMA_DatabaseInterface::QUERY_STORE
  95             );
  96             while ($row = $GLOBALS['dbi']->fetchArray($tables_rs)) {
  97                 if ($foreign && PMA_DRIZZLE) {
  98                     $engine = /*overload*/mb_strtoupper(
  99                         PMA_Table::sGetStatusInfo(
 100                             $_REQUEST['foreignDb'],
 101                             $row[0],
 102                             'Engine'
 103                         )
 104                     );
 105                     if (isset($engine) && $engine == $tbl_storage_engine) {
 106                         $tables[] = htmlspecialchars($row[0]);
 107                     }
 108                 } else {
 109                     $tables[] = htmlspecialchars($row[0]);
 110                 }
 111             }
 112         }
 113         $response->addJSON('tables', $tables);
 114     }
 115     exit;
 116 }
 117
 118 $header   = $response->getHeader();
 119 $scripts  = $header->getScripts();
 120 $scripts->addFile('tbl_relation.js');
 121 $scripts->addFile('indexes.js');
 122
 123 /**
 124  * Gets tables information
 125  */
 126 require_once 'libraries/tbl_info.inc.php';
 127
 128 $options_array = array(
 129     'CASCADE'   => 'CASCADE',
 130     'SET_NULL'  => 'SET NULL',
 131     'NO_ACTION' => 'NO ACTION',
 132     'RESTRICT'  => 'RESTRICT',
 133 );
 134
 135 /**
 136  * Gets the relation settings
 137  */
 138 $cfgRelation = PMA_getRelationsParam();
 139
 140 /**
 141  * Updates
 142  */
 143 if ($cfgRelation['relwork']) {
 144     $existrel = PMA_getForeigners($db, $table, '', 'internal');
 145 }
 146 if (PMA_Util::isForeignKeySupported($tbl_storage_engine)) {
 147     $existrel_foreign = PMA_getForeigners($db, $table, '', 'foreign');
 148 }
 149 if ($cfgRelation['displaywork']) {
 150     $disp     = PMA_getDisplayField($db, $table);
 151 } else {
 152     $disp = '';
 153 }
 154
 155 // will be used in the logic for internal relations and foreign keys:
 156 $multi_edit_columns_name = isset($_REQUEST['fields_name'])
 157     ? $_REQUEST['fields_name']
 158     : null;
 159
 160
 161 $html_output = '';
 162 $upd_query = new PMA_Table($table, $db, $GLOBALS['dbi']);
 163
 164 // u p d a t e s   f o r   I n t e r n a l    r e l a t i o n s
 165 if (isset($_POST['destination_db']) && $cfgRelation['relwork']) {
 166     if ($upd_query->updateInternalRelations(
 167         $multi_edit_columns_name, $_POST['destination_db'], $_POST['destination_table'],
 168         $_POST['destination_column'], $cfgRelation, isset($existrel) ? $existrel : null
 169     )) {
 170         $html_output .= PMA_Util::getMessage(
 171             __('Internal relations were successfully updated.'),
 172             '', 'success'
 173         );
 174     }
 175 } // end if (updates for internal relations)
 176
 177 $multi_edit_columns_name = isset($_REQUEST['foreign_key_fields_name'])
 178     ? $_REQUEST['foreign_key_fields_name']
 179     : null;
 180
 181 // u p d a t e s    f o r    f o r e i g n    k e y s
 182 // (for now, one index name only; we keep the definitions if the
 183 // foreign db is not the same)
 184 if (isset($_POST['destination_foreign_db'])) {
 185     list($html, $preview_sql_data, $display_query, $seen_error) = $upd_query->updateForeignKeys(
 186         $_POST['destination_foreign_db'],
 187         $multi_edit_columns_name, $_POST['destination_foreign_table'],
 188         $_POST['destination_foreign_column'], $options_array, $table,
 189         isset($existrel_foreign) ? $existrel_foreign['foreign_keys_data'] : null
 190     );
 191     $html_output .= $html;
 192
 193     // If there is a request for SQL previewing.
 194     if (isset($_REQUEST['preview_sql'])) {
 195         PMA_previewSQL($preview_sql_data);
 196     }
 197
 198     if (! empty($display_query) && ! $seen_error) {
 199         $GLOBALS['display_query'] = $display_query;
 200         $html_output .= PMA_Util::getMessage(
 201             __('Your SQL query has been executed successfully.'),
 202             null, 'success'
 203         );
 204     }
 205 } // end if isset($destination_foreign)
 206
 207 // U p d a t e s   f o r   d i s p l a y   f i e l d
 208 if ($cfgRelation['displaywork'] && isset($_POST['display_field'])) {
 209     if ($upd_query->updateDisplayField($disp, $_POST['display_field'], $cfgRelation)) {
 210         $html_output .= PMA_Util::getMessage(
 211             __('Display column was successfully updated.'),
 212             '', 'success'
 213         );
 214     }
 215 } // end if
 216
 217 // If we did an update, refresh our data
 218 if (isset($_POST['destination_db']) && $cfgRelation['relwork']) {
 219     $existrel = PMA_getForeigners($db, $table, '', 'internal');
 220 }
 221 if (isset($_POST['destination_foreign_db'])
 222     && PMA_Util::isForeignKeySupported($tbl_storage_engine)
 223 ) {
 224     $existrel_foreign = PMA_getForeigners($db, $table, '', 'foreign');
 225 }
 226
 227 if ($cfgRelation['displaywork']) {
 228     $disp     = PMA_getDisplayField($db, $table);
 229 }
 230
 231
 232 // display secondary level tabs if necessary
 233 $engine = PMA_Table::sGetStatusInfo($db, $table, 'ENGINE');
 234 $response->addHTML(PMA_getStructureSecondaryTabs($engine));
 235 $response->addHTML('<div id="structure_content">');
 236
 237 /**
 238  * Dialog
 239  */
 240 // Now find out the columns of our $table
 241 // need to use PMA_DatabaseInterface::QUERY_STORE with $GLOBALS['dbi']->numRows()
 242 // in mysqli
 243 $columns = $GLOBALS['dbi']->getColumns($db, $table);
 244
 245 // common form
 246 $html_output .= PMA\Template::get('tbl_relation/common_form')->render(
 247     array(
 248         'db' => $db,
 249         'table' => $table,
 250         'columns' => $columns,
 251         'cfgRelation' => $cfgRelation,
 252         'tbl_storage_engine' => $tbl_storage_engine,
 253         'existrel' => isset($existrel) ? $existrel : array(),
 254         'existrel_foreign' => isset($existrel_foreign) ? $existrel_foreign['foreign_keys_data'] : array(),
 255         'options_array' => $options_array
 256     )
 257 );
 258
 259 if (PMA_Util::isForeignKeySupported($tbl_storage_engine)) {
 260     $html_output .= PMA_getHtmlForDisplayIndexes();
 261 }
 262 // Render HTML output
 263 $response->addHTML($html_output);
 264
 265 $response->addHTML('</div>');