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;
  28
  29   /**
  30    * Queries written to file when the class is destructed.
  31    *
  32    * @var array
  33    */
  34   private $enqueuedQueries = [];
  35
  36   /**
  37    * Build a list of triggers via hook and add them to (err, reconcile them
  38    * with) the database.
  39    *
  40    * @param string $tableName
  41    *   the specific table requiring a rebuild; or NULL to rebuild all tables.
  42    * @param bool $force
  43    *
  44    * @see CRM-9716
  45    */
  46   public function rebuild($tableName = NULL, $force = FALSE) {
  47     $info = [];
  48
  49     $logging = new \CRM_Logging_Schema();
  50     $logging->triggerInfo($info, $tableName, $force);
  51
  52     \CRM_Core_I18n_Schema::triggerInfo($info, $tableName);
  53     \CRM_Contact_BAO_Contact::triggerInfo($info, $tableName);
  54
  55     \CRM_Utils_Hook::triggerInfo($info, $tableName);
  56
  57     // drop all existing triggers on all tables
  58     $logging->dropTriggers($tableName);
  59
  60     // now create the set of new triggers
  61     $this->createTriggers($info, $tableName);
  62     $this->writeEnqueuedQueriesToFile();
  63   }
  64
  65   /**
  66    * @param array $info
  67    *   per hook_civicrm_triggerInfo.
  68    * @param string $onlyTableName
  69    *   the specific table requiring a rebuild; or NULL to rebuild all tables.
  70    */
  71   public function createTriggers($info, $onlyTableName = NULL) {
  72     // Validate info array, should probably raise errors?
  73     if (is_array($info) == FALSE) {
  74       return;
  75     }
  76
  77     $triggers = [];
  78
  79     // now enumerate the tables and the events and collect the same set in a different format
  80     foreach ($info as $value) {
  81
  82       // clean the incoming data, skip malformed entries
  83       // TODO: malformed entries should raise errors or get logged.
  84       if (isset($value['table']) == FALSE ||
  85         isset($value['event']) == FALSE ||
  86         isset($value['when']) == FALSE ||
  87         isset($value['sql']) == FALSE
  88       ) {
  89         continue;
  90       }
  91
  92       if (is_string($value['table']) == TRUE) {
  93         $tables = [$value['table']];
  94       }
  95       else {
  96         $tables = $value['table'];
  97       }
  98
  99       if (is_string($value['event']) == TRUE) {
 100         $events = [strtolower($value['event'])];
 101       }
 102       else {
 103         $events = array_map('strtolower', $value['event']);
 104       }
 105
 106       $whenName = strtolower($value['when']);
 107
 108       foreach ($tables as $tableName) {
 109         if (!isset($triggers[$tableName])) {
 110           $triggers[$tableName] = [];
 111         }
 112
 113         foreach ($events as $eventName) {
 114           $template_params = ['{tableName}', '{eventName}'];
 115           $template_values = [$tableName, $eventName];
 116
 117           $sql = str_replace($template_params,
 118             $template_values,
 119             $value['sql']
 120           );
 121           $variables = str_replace($template_params,
 122             $template_values,
 123             \CRM_Utils_Array::value('variables', $value)
 124           );
 125
 126           if (!isset($triggers[$tableName][$eventName])) {
 127             $triggers[$tableName][$eventName] = [];
 128           }
 129
 130           if (!isset($triggers[$tableName][$eventName][$whenName])) {
 131             // We're leaving out cursors, conditions, and handlers for now
 132             // they are kind of dangerous in this context anyway
 133             // better off putting them in stored procedures
 134             $triggers[$tableName][$eventName][$whenName] = [
 135               'variables' => [],
 136               'sql' => [],
 137             ];
 138           }
 139
 140           if ($variables) {
 141             $triggers[$tableName][$eventName][$whenName]['variables'][] = $variables;
 142           }
 143
 144           $triggers[$tableName][$eventName][$whenName]['sql'][] = $sql;
 145         }
 146       }
 147     }
 148
 149     // Sort tables alphabetically in order to output in a consistent order
 150     // for sites that like to diff this output over time
 151     // (ie. with the logging_no_trigger_permission setting in place).
 152     ksort($triggers);
 153     // now spit out the sql
 154     foreach ($triggers as $tableName => $tables) {
 155       if ($onlyTableName != NULL && $onlyTableName != $tableName) {
 156         continue;
 157       }
 158       foreach ($tables as $eventName => $events) {
 159         foreach ($events as $whenName => $parts) {
 160           $varString = implode("\n", $parts['variables']);
 161           $sqlString = implode("\n", $parts['sql']);
 162           $validName = \CRM_Core_DAO::shortenSQLName($tableName, 48, TRUE);
 163           $triggerName = "{$validName}_{$whenName}_{$eventName}";
 164           $triggerSQL = "CREATE TRIGGER $triggerName $whenName $eventName ON $tableName FOR EACH ROW BEGIN $varString $sqlString END";
 165
 166           $this->enqueueQuery("DROP TRIGGER IF EXISTS $triggerName");
 167           $this->enqueueQuery($triggerSQL);
 168         }
 169       }
 170     }
 171   }
 172
 173   /**
 174    * Wrapper function to drop triggers.
 175    *
 176    * @param string $tableName
 177    *   the specific table requiring a rebuild; or NULL to rebuild all tables.
 178    */
 179   public function dropTriggers($tableName = NULL) {
 180     $info = [];
 181
 182     $logging = new \CRM_Logging_Schema();
 183     $logging->triggerInfo($info, $tableName);
 184
 185     // drop all existing triggers on all tables
 186     $logging->dropTriggers($tableName);
 187   }
 188
 189   /**
 190    * Enqueue a query which alters triggers.
 191    *
 192    * As this requires a high permission level we funnel the queries through here to
 193    * facilitate them being taken 'offline'.
 194    *
 195    * @param string $triggerSQL
 196    *   The sql to run to create or drop the triggers.
 197    * @param array $params
 198    *   Optional parameters to interpolate into the string.
 199    */
 200   public function enqueueQuery($triggerSQL, $params = []) {
 201     if (\Civi::settings()->get('logging_no_trigger_permission')) {
 202
 203       if (!file_exists($this->getFile())) {
 204         // Ugh. Need to let user know somehow. This is the first change.
 205         \CRM_Core_Session::setStatus(ts('The mysql commands you need to run are stored in %1', [
 206           1 => $this->getFile(),
 207         ]),
 208           '',
 209           'alert',
 210           ['expires' => 0]
 211         );
 212       }
 213       $query = \CRM_Core_DAO::composeQuery($triggerSQL, $params);
 214       $this->enqueuedQueries[$query] = $query;
 215     }
 216     else {
 217       \CRM_Core_DAO::executeQuery($triggerSQL, $params, TRUE, NULL, FALSE, FALSE);
 218     }
 219   }
 220
 221   /**
 222    * @return NULL|string
 223    */
 224   public function getFile() {
 225     if ($this->file === NULL) {
 226       $prefix = 'trigger' . \CRM_Utils_Request::id();
 227       $config = \CRM_Core_Config::singleton();
 228       $this->file = "{$config->configAndLogDir}CiviCRM." . $prefix . md5($config->dsn) . '.sql';
 229     }
 230     return $this->file;
 231   }
 232
 233   /**
 234    * Write queries to file when the class is destructed.
 235    *
 236    * Note this is already written out for a full rebuild but it is
 237    * possible (at least in terms of what is public) to call drop & create
 238    * separately so this ensures they are output.
 239    */
 240   public function __destruct() {
 241     $this->writeEnqueuedQueriesToFile();
 242   }
 243
 244   /**
 245    * Write queries queued for write-to-file.
 246    */
 247   protected function writeEnqueuedQueriesToFile(): void {
 248     if (!empty($this->enqueuedQueries) && $this->getFile()) {
 249       $buf = "DELIMITER //\n";
 250       foreach ($this->enqueuedQueries as $query) {
 251         if (strpos($query, 'CREATE TRIGGER') === 0) {
 252           // The create triggers are long so put spaces between them. For the drops
 253           // condensed is more readable.
 254           $buf .= "\n";
 255         }
 256         $buf .= $query . " //\n";
 257       }
 258       $buf .= "DELIMITER ;\n";
 259       file_put_contents($this->getFile(), $buf, FILE_APPEND);
 260       $this->enqueuedQueries = [];
 261     }
 262   }
 263
 264 }