libraries/rte/rte_triggers.lib.php

   1 <?php
   2 /* vim: set expandtab sw=4 ts=4 sts=4: */
   3 /**
   4  * Functions for trigger management.
   5  *
   6  * @package phpMyAdmin
   7  */
   8 if (! defined('PHPMYADMIN')) {
   9     exit;
  10 }
  11
  12 /**
  13  * Sets required globals
  14  */
  15 function PMA_TRI_setGlobals()
  16 {
  17     global $action_timings, $event_manipulations;
  18
  19     // Some definitions for triggers
  20     $action_timings      = array('BEFORE',
  21                                  'AFTER');
  22     $event_manipulations = array('INSERT',
  23                                  'UPDATE',
  24                                  'DELETE');
  25 }
  26
  27 /**
  28  * Main function for the triggers functionality
  29  */
  30 function PMA_TRI_main()
  31 {
  32     global $db, $table;
  33
  34     PMA_TRI_setGlobals();
  35     /**
  36      * Process all requests
  37      */
  38     PMA_TRI_handleEditor();
  39     PMA_TRI_handleExport();
  40     /**
  41      * Display a list of available triggers
  42      */
  43     $items = PMA_DBI_get_triggers($db, $table);
  44     echo PMA_RTE_getList('trigger', $items);
  45     /**
  46      * Display a link for adding a new trigger,
  47      * if the user has the necessary privileges
  48      */
  49     echo PMA_TRI_getFooterLinks();
  50 } // end PMA_TRI_main()
  51
  52 /**
  53  * Handles editor requests for adding or editing an item
  54  */
  55 function PMA_TRI_handleEditor()
  56 {
  57     global $_REQUEST, $_POST, $errors, $db, $table;
  58
  59     if (! empty($_REQUEST['editor_process_add'])
  60         || ! empty($_REQUEST['editor_process_edit'])
  61     ) {
  62         $sql_query = '';
  63
  64         $item_query = PMA_TRI_getQueryFromRequest();
  65
  66         if (! count($errors)) { // set by PMA_RTN_getQueryFromRequest()
  67             // Execute the created query
  68             if (! empty($_REQUEST['editor_process_edit'])) {
  69                 // Backup the old trigger, in case something goes wrong
  70                 $trigger = PMA_TRI_getDataFromName($_REQUEST['item_original_name']);
  71                 $create_item = $trigger['create'];
  72                 $drop_item = $trigger['drop'] . ';';
  73                 $result = PMA_DBI_try_query($drop_item);
  74                 if (! $result) {
  75                     $errors[] = sprintf(__('The following query has failed: "%s"'), $drop_item) . '<br />'
  76                                       . __('MySQL said: ') . PMA_DBI_getError(null);
  77                 } else {
  78                     $result = PMA_DBI_try_query($item_query);
  79                     if (! $result) {
  80                         $errors[] = sprintf(__('The following query has failed: "%s"'), $item_query) . '<br />'
  81                                           . __('MySQL said: ') . PMA_DBI_getError(null);
  82                         // We dropped the old item, but were unable to create the new one
  83                         // Try to restore the backup query
  84                         $result = PMA_DBI_try_query($create_item);
  85                         if (! $result) {
  86                             // OMG, this is really bad! We dropped the query, failed to create a new one
  87                             // and now even the backup query does not execute!
  88                             // This should not happen, but we better handle this just in case.
  89                             $errors[] = __('Sorry, we failed to restore the dropped trigger.') . '<br />'
  90                                               . __('The backed up query was:') . "\"$create_item\"" . '<br />'
  91                                               . __('MySQL said: ') . PMA_DBI_getError(null);
  92                         }
  93                     } else {
  94                         $message = PMA_Message::success(__('Trigger %1$s has been modified.'));
  95                         $message->addParam(PMA_backquote($_REQUEST['item_name']));
  96                         $sql_query = $drop_item . $item_query;
  97                     }
  98                 }
  99             } else {
 100                 // 'Add a new item' mode
 101                 $result = PMA_DBI_try_query($item_query);
 102                 if (! $result) {
 103                     $errors[] = sprintf(__('The following query has failed: "%s"'), $item_query) . '<br /><br />'
 104                                       . __('MySQL said: ') . PMA_DBI_getError(null);
 105                 } else {
 106                     $message = PMA_Message::success(__('Trigger %1$s has been created.'));
 107                     $message->addParam(PMA_backquote($_REQUEST['item_name']));
 108                     $sql_query = $item_query;
 109                 }
 110             }
 111         }
 112
 113         if (count($errors)) {
 114             $message = PMA_Message::error(__('<b>One or more errors have occured while processing your request:</b>'));
 115             $message->addString('<ul>');
 116             foreach ($errors as $num => $string) {
 117                 $message->addString('<li>' . $string . '</li>');
 118             }
 119             $message->addString('</ul>');
 120         }
 121
 122         $output = PMA_showMessage($message, $sql_query);
 123         if ($GLOBALS['is_ajax_request']) {
 124             $extra_data = array();
 125             if ($message->isSuccess()) {
 126                 $items = PMA_DBI_get_triggers($db, $table, '');
 127                 $trigger = false;
 128                 foreach ($items as $key => $value) {
 129                     if ($value['name'] == $_REQUEST['item_name']) {
 130                         $trigger = $value;
 131                     }
 132                 }
 133                 $extra_data['insert'] = false;
 134                 if (empty($table) || ($trigger !== false && $table == $trigger['table'])) {
 135                     $extra_data['insert'] = true;
 136                     $extra_data['new_row'] = PMA_TRI_getRowForList($trigger);
 137                     $extra_data['name'] = htmlspecialchars(
 138                         strtoupper($_REQUEST['item_name'])
 139                     );
 140                 }
 141                 $response = $output;
 142             } else {
 143                 $response = $message;
 144             }
 145             PMA_ajaxResponse($response, $message->isSuccess(), $extra_data);
 146         }
 147     }
 148
 149     /**
 150      * Display a form used to add/edit a trigger, if necessary
 151      */
 152     if (count($errors) || ( empty($_REQUEST['editor_process_add']) && empty($_REQUEST['editor_process_edit'])
 153         && (! empty($_REQUEST['add_item']) || ! empty($_REQUEST['edit_item']))) // FIXME: this must be simpler than that
 154     ) {
 155         // Get the data for the form (if any)
 156         if (! empty($_REQUEST['add_item'])) {
 157             $title = __("Create trigger");
 158             $item = PMA_TRI_getDataFromRequest();
 159             $mode = 'add';
 160         } else if (! empty($_REQUEST['edit_item'])) {
 161             $title = __("Edit trigger");
 162             if (! empty($_REQUEST['item_name'])
 163                 && empty($_REQUEST['editor_process_edit'])
 164             ) {
 165                 $item = PMA_TRI_getDataFromName($_REQUEST['item_name']);
 166                 if ($item !== false) {
 167                     $item['item_original_name'] = $item['item_name'];
 168                 }
 169             } else {
 170                 $item = PMA_TRI_getDataFromRequest();
 171             }
 172             $mode = 'edit';
 173         }
 174         if ($item !== false) {
 175             // Show form
 176             $editor = PMA_TRI_getEditorForm($mode, $item);
 177             if ($GLOBALS['is_ajax_request']) {
 178                 $extra_data = array('title' => $title);
 179                 PMA_ajaxResponse($editor, true, $extra_data);
 180             } else {
 181                 echo "\n\n<h2>$title</h2>\n\n$editor";
 182                 unset($_POST);
 183                 require './libraries/footer.inc.php';
 184             }
 185             // exit;
 186         } else {
 187             $message  = __('Error in processing request') . ' : ';
 188             $message .= sprintf(
 189                 PMA_RTE_getWord('not_found'),
 190                 htmlspecialchars(PMA_backquote($_REQUEST['item_name'])),
 191                 htmlspecialchars(PMA_backquote($db))
 192             );
 193             $message = PMA_message::error($message);
 194             if ($GLOBALS['is_ajax_request']) {
 195                 PMA_ajaxResponse($message, false);
 196             } else {
 197                 $message->display();
 198             }
 199         }
 200     }
 201 } // end PMA_TRI_handleEditor()
 202
 203 /**
 204  * This function will generate the values that are required to for the editor
 205  *
 206  * @return  array    Data necessary to create the editor.
 207  */
 208 function PMA_TRI_getDataFromRequest()
 209 {
 210     $retval = array();
 211     $indices = array('item_name',
 212                      'item_table',
 213                      'item_original_name',
 214                      'item_action_timing',
 215                      'item_event_manipulation',
 216                      'item_definition',
 217                      'item_definer');
 218     foreach ($indices as $key => $index) {
 219         $retval[$index] = isset($_REQUEST[$index]) ? $_REQUEST[$index] : '';
 220     }
 221     return $retval;
 222 } // end PMA_TRI_getDataFromRequest()
 223
 224 /**
 225  * This function will generate the values that are required to complete
 226  * the "Edit trigger" form given the name of a trigger.
 227  *
 228  * @param   string   $name   The name of the trigger.
 229  *
 230  * @return  array    Data necessary to create the editor.
 231  */
 232 function PMA_TRI_getDataFromName($name)
 233 {
 234     global $db, $table, $_REQUEST;
 235
 236     $temp = array();
 237     $items = PMA_DBI_get_triggers($db, $table, '');
 238     foreach ($items as $key => $value) {
 239         if ($value['name'] == $name) {
 240             $temp = $value;
 241         }
 242     }
 243     if (empty($temp)) {
 244         return false;
 245     } else {
 246         $retval = array();
 247         $retval['create']                  = $temp['create'];
 248         $retval['drop']                    = $temp['drop'];
 249         $retval['item_name']               = $temp['name'];
 250         $retval['item_table']              = $temp['table'];
 251         $retval['item_action_timing']      = $temp['action_timing'];
 252         $retval['item_event_manipulation'] = $temp['event_manipulation'];
 253         $retval['item_definition']         = $temp['definition'];
 254         $retval['item_definer']            = $temp['definer'];
 255         return $retval;
 256     }
 257 } // end PMA_TRI_getDataFromName()
 258
 259 /**
 260  * Displays a form used to add/edit a trigger
 261  *
 262  * @param   string   $mode         If the editor will be used edit a trigger
 263  *                                 or add a new one: 'edit' or 'add'.
 264  * @param   array    $item         Data for the trigger returned by
 265  *                                 PMA_TRI_getDataFromRequest() or
 266  *                                 PMA_TRI_getDataFromName()
 267  *
 268  * @return  string   HTML code for the editor.
 269  */
 270 function PMA_TRI_getEditorForm($mode, $item)
 271 {
 272     global $db, $table, $titles, $event_manipulations, $action_timings;
 273
 274     // Escape special characters
 275     $need_escape = array(
 276                        'item_original_name',
 277                        'item_name',
 278                        'item_definition',
 279                        'item_definer'
 280                    );
 281     foreach ($need_escape as $key => $index) {
 282         $item[$index] = htmlentities($item[$index], ENT_QUOTES);
 283     }
 284     $original_data = '';
 285     if ($mode == 'edit') {
 286         $original_data = "<input name='item_original_name' "
 287                        . "type='hidden' value='{$item['item_original_name']}'/>\n";
 288     }
 289
 290     // Create the output
 291     $retval  = "";
 292     $retval .= "<!-- START " . strtoupper($mode) . " TRIGGER FORM -->\n\n";
 293     $retval .= "<form class='rte_form' action='db_triggers.php' method='post'>\n";
 294     $retval .= "<input name='{$mode}_item' type='hidden' value='1' />\n";
 295     $retval .= $original_data;
 296     $retval .= PMA_generate_common_hidden_inputs($db, $table) . "\n";
 297     $retval .= "<fieldset>\n";
 298     $retval .= "<legend>" . __('Details') . "</legend>\n";
 299     $retval .= "<table class='rte_table' style='width: 100%'>\n";
 300     $retval .= "<tr>\n";
 301     $retval .= "    <td style='width: 20%;'>" . __('Trigger name') . "</td>\n";
 302     $retval .= "    <td><input type='text' name='item_name' maxlength='64'\n";
 303     $retval .= "               value='{$item['item_name']}' /></td>\n";
 304     $retval .= "</tr>\n";
 305     $retval .= "<tr>\n";
 306     $retval .= "    <td>" . __('Table') . "</td>\n";
 307     $retval .= "    <td>\n";
 308     $retval .= "        <select name='item_table'>\n";
 309     foreach (PMA_DBI_get_tables($db) as $key => $value) {
 310         $selected = "";
 311         if ($value == $item['item_table']) {
 312             $selected = " selected='selected'";
 313         }
 314         $retval .= "            <option$selected>$value</option>\n";
 315     }
 316     $retval .= "        </select>\n";
 317     $retval .= "    </td>\n";
 318     $retval .= "</tr>\n";
 319     $retval .= "<tr>\n";
 320     $retval .= "    <td>" . __('Time') . "</td>\n";
 321     $retval .= "    <td><select name='item_timing'>\n";
 322     foreach ($action_timings as $key => $value) {
 323         $selected = "";
 324         if (! empty($item['item_action_timing'])
 325             && $item['item_action_timing'] == $value
 326         ) {
 327             $selected = " selected='selected'";
 328         }
 329         $retval .= "<option$selected>$value</option>";
 330     }
 331     $retval .= "    </select></td>\n";
 332     $retval .= "</tr>\n";
 333     $retval .= "<tr>\n";
 334     $retval .= "    <td>" . __('Event') . "</td>\n";
 335     $retval .= "    <td><select name='item_event'>\n";
 336     foreach ($event_manipulations as $key => $value) {
 337         $selected = "";
 338         if (! empty($item['item_event_manipulation'])
 339             && $item['item_event_manipulation'] == $value
 340         ) {
 341             $selected = " selected='selected'";
 342         }
 343         $retval .= "<option$selected>$value</option>";
 344     }
 345     $retval .= "    </select></td>\n";
 346     $retval .= "</tr>\n";
 347     $retval .= "<tr>\n";
 348     $retval .= "    <td>" . __('Definition') . "</td>\n";
 349     $retval .= "    <td><textarea name='item_definition' rows='15' cols='40'>";
 350     $retval .= $item['item_definition'];
 351     $retval .= "</textarea></td>\n";
 352     $retval .= "</tr>\n";
 353     $retval .= "<tr>\n";
 354     $retval .= "    <td>" . __('Definer') . "</td>\n";
 355     $retval .= "    <td><input type='text' name='item_definer'\n";
 356     $retval .= "               value='{$item['item_definer']}' /></td>\n";
 357     $retval .= "</tr>\n";
 358     $retval .= "</table>\n";
 359     $retval .= "</fieldset>\n";
 360     if ($GLOBALS['is_ajax_request']) {
 361         $retval .= "<input type='hidden' name='editor_process_{$mode}'\n";
 362         $retval .= "       value='true' />\n";
 363         $retval .= "<input type='hidden' name='ajax_request' value='true' />\n";
 364     } else {
 365         $retval .= "<fieldset class='tblFooters'>\n";
 366         $retval .= "    <input type='submit' name='editor_process_{$mode}'\n";
 367         $retval .= "           value='" . __('Go') . "' />\n";
 368         $retval .= "</fieldset>\n";
 369     }
 370     $retval .= "</form>\n\n";
 371     $retval .= "<!-- END " . strtoupper($mode) . " TRIGGER FORM -->\n\n";
 372
 373     return $retval;
 374 } // end PMA_TRI_getEditorForm()
 375
 376 /**
 377  * Composes the query necessary to create a trigger from an HTTP request.
 378  *
 379  * @return  string  The CREATE TRIGGER query.
 380  */
 381 function PMA_TRI_getQueryFromRequest()
 382 {
 383     global $_REQUEST, $db, $errors, $action_timings, $event_manipulations;
 384
 385     $query = 'CREATE ';
 386     if (! empty($_REQUEST['item_definer'])) {
 387         if (strpos($_REQUEST['item_definer'], '@') !== false) {
 388             $arr = explode('@', $_REQUEST['item_definer']);
 389             $query .= 'DEFINER=' . PMA_backquote($arr[0]);
 390             $query .= '@' . PMA_backquote($arr[1]) . ' ';
 391         } else {
 392             $errors[] = __('The definer must be in the "username@hostname" format');
 393         }
 394     }
 395     $query .= 'TRIGGER ';
 396     if (! empty($_REQUEST['item_name'])) {
 397         $query .= PMA_backquote($_REQUEST['item_name']) . ' ';
 398     } else {
 399         $errors[] = __('You must provide a trigger name');
 400     }
 401     if (! empty($_REQUEST['item_timing']) && in_array($_REQUEST['item_timing'], $action_timings)) {
 402         $query .= $_REQUEST['item_timing'] . ' ';
 403     } else {
 404         $errors[] = __('You must provide a valid timing for the trigger');
 405     }
 406     if (! empty($_REQUEST['item_event']) && in_array($_REQUEST['item_event'], $event_manipulations)) {
 407         $query .= $_REQUEST['item_event'] . ' ';
 408     } else {
 409         $errors[] = __('You must provide a valid event for the trigger');
 410     }
 411     $query .= 'ON ';
 412     if (! empty($_REQUEST['item_table']) && in_array($_REQUEST['item_table'], PMA_DBI_get_tables($db))) {
 413         $query .= PMA_backQuote($_REQUEST['item_table']);
 414     } else {
 415         $errors[] = __('You must provide a valid table name');
 416     }
 417     $query .= ' FOR EACH ROW ';
 418     if (! empty($_REQUEST['item_definition'])) {
 419         $query .= $_REQUEST['item_definition'];
 420     } else {
 421         $errors[] = __('You must provide a trigger definition.');
 422     }
 423
 424     return $query;
 425 } // end PMA_TRI_getQueryFromRequest()
 426
 427 ?>