Civi/Core/SqlTriggers.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | Copyright CiviCRM LLC. All rights reserved.                        |
   5  |                                                                    |
   6  | This work is published under the GNU AGPLv3 license with some      |
   7  | permitted exceptions and without any warranty. For full license    |
   8  | and copyright information, see https://civicrm.org/licensing       |
   9  +--------------------------------------------------------------------+
  10  */
  11
  12 namespace Civi\Core;
  13
  14 /**
  15  * Class SqlTriggers
  16  * @package Civi\Core
  17  *
  18  * This class manages creation and destruction of SQL triggers.
  19  */
  20 class SqlTriggers {
  21
  22   /**
  23    * The name of the output file.
  24    *
  25    * @var string|null
  26    */
  27   private $file = NULL;
  28
  29   /**
  30    * Build a list of triggers via hook and add them to (err, reconcile them
  31    * with) the database.
  32    *
  33    * @param string $tableName
  34    *   the specific table requiring a rebuild; or NULL to rebuild all tables.
  35    * @param bool $force
  36    *
  37    * @see CRM-9716
  38    */
  39   public function rebuild($tableName = NULL, $force = FALSE) {
  40     $info = [];
  41
  42     $logging = new \CRM_Logging_Schema();
  43     $logging->triggerInfo($info, $tableName, $force);
  44
  45     \CRM_Core_I18n_Schema::triggerInfo($info, $tableName);
  46     \CRM_Contact_BAO_Contact::triggerInfo($info, $tableName);
  47
  48     \CRM_Utils_Hook::triggerInfo($info, $tableName);
  49
  50     // drop all existing triggers on all tables
  51     $logging->dropTriggers($tableName);
  52
  53     // now create the set of new triggers
  54     $this->createTriggers($info, $tableName);
  55   }
  56
  57   /**
  58    * @param array $info
  59    *   per hook_civicrm_triggerInfo.
  60    * @param string $onlyTableName
  61    *   the specific table requiring a rebuild; or NULL to rebuild all tables.
  62    */
  63   public function createTriggers(&$info, $onlyTableName = NULL) {
  64     // Validate info array, should probably raise errors?
  65     if (is_array($info) == FALSE) {
  66       return;
  67     }
  68
  69     $triggers = [];
  70
  71     // now enumerate the tables and the events and collect the same set in a different format
  72     foreach ($info as $value) {
  73
  74       // clean the incoming data, skip malformed entries
  75       // TODO: malformed entries should raise errors or get logged.
  76       if (isset($value['table']) == FALSE ||
  77         isset($value['event']) == FALSE ||
  78         isset($value['when']) == FALSE ||
  79         isset($value['sql']) == FALSE
  80       ) {
  81         continue;
  82       }
  83
  84       if (is_string($value['table']) == TRUE) {
  85         $tables = [$value['table']];
  86       }
  87       else {
  88         $tables = $value['table'];
  89       }
  90
  91       if (is_string($value['event']) == TRUE) {
  92         $events = [strtolower($value['event'])];
  93       }
  94       else {
  95         $events = array_map('strtolower', $value['event']);
  96       }
  97
  98       $whenName = strtolower($value['when']);
  99
 100       foreach ($tables as $tableName) {
 101         if (!isset($triggers[$tableName])) {
 102           $triggers[$tableName] = [];
 103         }
 104
 105         foreach ($events as $eventName) {
 106           $template_params = ['{tableName}', '{eventName}'];
 107           $template_values = [$tableName, $eventName];
 108
 109           $sql = str_replace($template_params,
 110             $template_values,
 111             $value['sql']
 112           );
 113           $variables = str_replace($template_params,
 114             $template_values,
 115             \CRM_Utils_Array::value('variables', $value)
 116           );
 117
 118           if (!isset($triggers[$tableName][$eventName])) {
 119             $triggers[$tableName][$eventName] = [];
 120           }
 121
 122           if (!isset($triggers[$tableName][$eventName][$whenName])) {
 123             // We're leaving out cursors, conditions, and handlers for now
 124             // they are kind of dangerous in this context anyway
 125             // better off putting them in stored procedures
 126             $triggers[$tableName][$eventName][$whenName] = [
 127               'variables' => [],
 128               'sql' => [],
 129             ];
 130           }
 131
 132           if ($variables) {
 133             $triggers[$tableName][$eventName][$whenName]['variables'][] = $variables;
 134           }
 135
 136           $triggers[$tableName][$eventName][$whenName]['sql'][] = $sql;
 137         }
 138       }
 139     }
 140
 141     // now spit out the sql
 142     foreach ($triggers as $tableName => $tables) {
 143       if ($onlyTableName != NULL && $onlyTableName != $tableName) {
 144         continue;
 145       }
 146       foreach ($tables as $eventName => $events) {
 147         foreach ($events as $whenName => $parts) {
 148           $varString = implode("\n", $parts['variables']);
 149           $sqlString = implode("\n", $parts['sql']);
 150           $validName = \CRM_Core_DAO::shortenSQLName($tableName, 48, TRUE);
 151           $triggerName = "{$validName}_{$whenName}_{$eventName}";
 152           $triggerSQL = "CREATE TRIGGER $triggerName $whenName $eventName ON $tableName FOR EACH ROW BEGIN $varString $sqlString END";
 153
 154           $this->enqueueQuery("DROP TRIGGER IF EXISTS $triggerName");
 155           $this->enqueueQuery($triggerSQL);
 156         }
 157       }
 158     }
 159   }
 160
 161   /**
 162    * Wrapper function to drop triggers.
 163    *
 164    * @param string $tableName
 165    *   the specific table requiring a rebuild; or NULL to rebuild all tables.
 166    */
 167   public function dropTriggers($tableName = NULL) {
 168     $info = [];
 169
 170     $logging = new \CRM_Logging_Schema();
 171     $logging->triggerInfo($info, $tableName);
 172
 173     // drop all existing triggers on all tables
 174     $logging->dropTriggers($tableName);
 175   }
 176
 177   /**
 178    * Enqueue a query which alters triggers.
 179    *
 180    * As this requires a high permission level we funnel the queries through here to
 181    * facilitate them being taken 'offline'.
 182    *
 183    * @param string $triggerSQL
 184    *   The sql to run to create or drop the triggers.
 185    * @param array $params
 186    *   Optional parameters to interpolate into the string.
 187    */
 188   public function enqueueQuery($triggerSQL, $params = []) {
 189     if (\Civi::settings()->get('logging_no_trigger_permission')) {
 190
 191       if (!file_exists($this->getFile())) {
 192         // Ugh. Need to let user know somehow. This is the first change.
 193         \CRM_Core_Session::setStatus(ts('The mysql commands you need to run are stored in %1', [
 194           1 => $this->getFile(),
 195         ]),
 196           '',
 197           'alert',
 198           ['expires' => 0]
 199         );
 200       }
 201
 202       $buf = "\n";
 203       $buf .= "DELIMITER //\n";
 204       $buf .= \CRM_Core_DAO::composeQuery($triggerSQL, $params) . " //\n";
 205       $buf .= "DELIMITER ;\n";
 206       file_put_contents($this->getFile(), $buf, FILE_APPEND);
 207     }
 208     else {
 209       \CRM_Core_DAO::executeQuery($triggerSQL, $params, TRUE, NULL, FALSE, FALSE);
 210     }
 211   }
 212
 213   /**
 214    * @return NULL|string
 215    */
 216   public function getFile() {
 217     if ($this->file === NULL) {
 218       $prefix = 'trigger' . \CRM_Utils_Request::id();
 219       $config = \CRM_Core_Config::singleton();
 220       $this->file = "{$config->configAndLogDir}CiviCRM." . $prefix . md5($config->dsn) . '.sql';
 221     }
 222     return $this->file;
 223   }
 224
 225 }