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 = PMA_RTE_getWord('add');
 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     $query  = "SELECT `TABLE_NAME` FROM `INFORMATION_SCHEMA`.`TABLES` ";
 290     $query .= "WHERE `TABLE_SCHEMA`='" . PMA_sqlAddSlashes($db) . "' ";
 291     $query .= "AND `TABLE_TYPE`='BASE TABLE'";
 292     $tables = PMA_DBI_fetch_result($query);
 293
 294     // Create the output
 295     $retval  = "";
 296     $retval .= "<!-- START " . strtoupper($mode) . " TRIGGER FORM -->\n\n";
 297     $retval .= "<form class='rte_form' action='db_triggers.php' method='post'>\n";
 298     $retval .= "<input name='{$mode}_item' type='hidden' value='1' />\n";
 299     $retval .= $original_data;
 300     $retval .= PMA_generate_common_hidden_inputs($db, $table) . "\n";
 301     $retval .= "<fieldset>\n";
 302     $retval .= "<legend>" . __('Details') . "</legend>\n";
 303     $retval .= "<table class='rte_table' style='width: 100%'>\n";
 304     $retval .= "<tr>\n";
 305     $retval .= "    <td style='width: 20%;'>" . __('Trigger name') . "</td>\n";
 306     $retval .= "    <td><input type='text' name='item_name' maxlength='64'\n";
 307     $retval .= "               value='{$item['item_name']}' /></td>\n";
 308     $retval .= "</tr>\n";
 309     $retval .= "<tr>\n";
 310     $retval .= "    <td>" . __('Table') . "</td>\n";
 311     $retval .= "    <td>\n";
 312     $retval .= "        <select name='item_table'>\n";
 313     foreach ($tables as $key => $value) {
 314         $selected = "";
 315         if ($mode == 'add' && $value == $table) {
 316             $selected = " selected='selected'";
 317         } else if ($mode == 'edit' && $value == $item['item_table']) {
 318             $selected = " selected='selected'";
 319         }
 320         $retval .= "            <option$selected>$value</option>\n";
 321     }
 322     $retval .= "        </select>\n";
 323     $retval .= "    </td>\n";
 324     $retval .= "</tr>\n";
 325     $retval .= "<tr>\n";
 326     $retval .= "    <td>" . __('Time') . "</td>\n";
 327     $retval .= "    <td><select name='item_timing'>\n";
 328     foreach ($action_timings as $key => $value) {
 329         $selected = "";
 330         if (! empty($item['item_action_timing'])
 331             && $item['item_action_timing'] == $value
 332         ) {
 333             $selected = " selected='selected'";
 334         }
 335         $retval .= "<option$selected>$value</option>";
 336     }
 337     $retval .= "    </select></td>\n";
 338     $retval .= "</tr>\n";
 339     $retval .= "<tr>\n";
 340     $retval .= "    <td>" . __('Event') . "</td>\n";
 341     $retval .= "    <td><select name='item_event'>\n";
 342     foreach ($event_manipulations as $key => $value) {
 343         $selected = "";
 344         if (! empty($item['item_event_manipulation'])
 345             && $item['item_event_manipulation'] == $value
 346         ) {
 347             $selected = " selected='selected'";
 348         }
 349         $retval .= "<option$selected>$value</option>";
 350     }
 351     $retval .= "    </select></td>\n";
 352     $retval .= "</tr>\n";
 353     $retval .= "<tr>\n";
 354     $retval .= "    <td>" . __('Definition') . "</td>\n";
 355     $retval .= "    <td><textarea name='item_definition' rows='15' cols='40'>";
 356     $retval .= $item['item_definition'];
 357     $retval .= "</textarea></td>\n";
 358     $retval .= "</tr>\n";
 359     $retval .= "<tr>\n";
 360     $retval .= "    <td>" . __('Definer') . "</td>\n";
 361     $retval .= "    <td><input type='text' name='item_definer'\n";
 362     $retval .= "               value='{$item['item_definer']}' /></td>\n";
 363     $retval .= "</tr>\n";
 364     $retval .= "</table>\n";
 365     $retval .= "</fieldset>\n";
 366     if ($GLOBALS['is_ajax_request']) {
 367         $retval .= "<input type='hidden' name='editor_process_{$mode}'\n";
 368         $retval .= "       value='true' />\n";
 369         $retval .= "<input type='hidden' name='ajax_request' value='true' />\n";
 370     } else {
 371         $retval .= "<fieldset class='tblFooters'>\n";
 372         $retval .= "    <input type='submit' name='editor_process_{$mode}'\n";
 373         $retval .= "           value='" . __('Go') . "' />\n";
 374         $retval .= "</fieldset>\n";
 375     }
 376     $retval .= "</form>\n\n";
 377     $retval .= "<!-- END " . strtoupper($mode) . " TRIGGER FORM -->\n\n";
 378
 379     return $retval;
 380 } // end PMA_TRI_getEditorForm()
 381
 382 /**
 383  * Composes the query necessary to create a trigger from an HTTP request.
 384  *
 385  * @return  string  The CREATE TRIGGER query.
 386  */
 387 function PMA_TRI_getQueryFromRequest()
 388 {
 389     global $_REQUEST, $db, $errors, $action_timings, $event_manipulations;
 390
 391     $query = 'CREATE ';
 392     if (! empty($_REQUEST['item_definer'])) {
 393         if (strpos($_REQUEST['item_definer'], '@') !== false) {
 394             $arr = explode('@', $_REQUEST['item_definer']);
 395             $query .= 'DEFINER=' . PMA_backquote($arr[0]);
 396             $query .= '@' . PMA_backquote($arr[1]) . ' ';
 397         } else {
 398             $errors[] = __('The definer must be in the "username@hostname" format');
 399         }
 400     }
 401     $query .= 'TRIGGER ';
 402     if (! empty($_REQUEST['item_name'])) {
 403         $query .= PMA_backquote($_REQUEST['item_name']) . ' ';
 404     } else {
 405         $errors[] = __('You must provide a trigger name');
 406     }
 407     if (! empty($_REQUEST['item_timing']) && in_array($_REQUEST['item_timing'], $action_timings)) {
 408         $query .= $_REQUEST['item_timing'] . ' ';
 409     } else {
 410         $errors[] = __('You must provide a valid timing for the trigger');
 411     }
 412     if (! empty($_REQUEST['item_event']) && in_array($_REQUEST['item_event'], $event_manipulations)) {
 413         $query .= $_REQUEST['item_event'] . ' ';
 414     } else {
 415         $errors[] = __('You must provide a valid event for the trigger');
 416     }
 417     $query .= 'ON ';
 418     if (! empty($_REQUEST['item_table']) && in_array($_REQUEST['item_table'], PMA_DBI_get_tables($db))) {
 419         $query .= PMA_backQuote($_REQUEST['item_table']);
 420     } else {
 421         $errors[] = __('You must provide a valid table name');
 422     }
 423     $query .= ' FOR EACH ROW ';
 424     if (! empty($_REQUEST['item_definition'])) {
 425         $query .= $_REQUEST['item_definition'];
 426     } else {
 427         $errors[] = __('You must provide a trigger definition.');
 428     }
 429
 430     return $query;
 431 } // end PMA_TRI_getQueryFromRequest()
 432
 433 ?>