/**
* Drop triggers for all logged tables.
*/
public function dropTriggers($tableName = NULL)
{
$dao = new CRM_Core_DAO();
if ($tableName) {
$tableNames = array($tableName);
} else {
$tableNames = $this->tables;
}
foreach ($tableNames as $table) {
$validName = CRM_Core_DAO::shortenSQLName($table, 48, TRUE);
// before triggers
$dao->executeQuery("DROP TRIGGER IF EXISTS {$validName}_before_insert");
$dao->executeQuery("DROP TRIGGER IF EXISTS {$validName}_before_update");
$dao->executeQuery("DROP TRIGGER IF EXISTS {$validName}_before_delete");
// after triggers
$dao->executeQuery("DROP TRIGGER IF EXISTS {$validName}_after_insert");
$dao->executeQuery("DROP TRIGGER IF EXISTS {$validName}_after_update");
$dao->executeQuery("DROP TRIGGER IF EXISTS {$validName}_after_delete");
}
// now lets also be safe and drop all triggers that start with
// civicrm_ if we are dropping all triggers
// we need to do this to capture all the leftover triggers since
// we did the shortening trigger name for CRM-11794
if ($tableName === NULL) {
$triggers = $dao->executeQuery("SHOW TRIGGERS LIKE 'civicrm_%'");
while ($triggers->fetch()) {
// note that drop trigger has a weird syntax and hence we do not
// send the trigger name as a string (i.e. its not quoted
$dao->executeQuery("DROP TRIGGER IF EXISTS {$triggers->Trigger}");
}
}
}
/**
* @dataProvider sqlNameDataProvider
*/
function testShortenSQLName($inputData, $length, $makeRandom, $expectedResult)
{
$this->assertEquals($expectedResult, CRM_Core_DAO::shortenSQLName($inputData, $length, $makeRandom));
}
/**
* @param array $info
* per hook_civicrm_triggerInfo.
* @param string $onlyTableName
* the specific table requiring a rebuild; or NULL to rebuild all tables.
*/
public static function createTriggers(&$info, $onlyTableName = NULL)
{
// Validate info array, should probably raise errors?
if (is_array($info) == FALSE) {
return;
}
$triggers = array();
// now enumerate the tables and the events and collect the same set in a different format
foreach ($info as $value) {
// clean the incoming data, skip malformed entries
// TODO: malformed entries should raise errors or get logged.
if (isset($value['table']) == FALSE || isset($value['event']) == FALSE || isset($value['when']) == FALSE || isset($value['sql']) == FALSE) {
continue;
}
if (is_string($value['table']) == TRUE) {
$tables = array($value['table']);
} else {
$tables = $value['table'];
}
if (is_string($value['event']) == TRUE) {
$events = array(strtolower($value['event']));
} else {
$events = array_map('strtolower', $value['event']);
}
$whenName = strtolower($value['when']);
foreach ($tables as $tableName) {
if (!isset($triggers[$tableName])) {
$triggers[$tableName] = array();
}
foreach ($events as $eventName) {
$template_params = array('{tableName}', '{eventName}');
$template_values = array($tableName, $eventName);
$sql = str_replace($template_params, $template_values, $value['sql']);
$variables = str_replace($template_params, $template_values, CRM_Utils_Array::value('variables', $value));
if (!isset($triggers[$tableName][$eventName])) {
$triggers[$tableName][$eventName] = array();
}
if (!isset($triggers[$tableName][$eventName][$whenName])) {
// We're leaving out cursors, conditions, and handlers for now
// they are kind of dangerous in this context anyway
// better off putting them in stored procedures
$triggers[$tableName][$eventName][$whenName] = array('variables' => array(), 'sql' => array());
}
if ($variables) {
$triggers[$tableName][$eventName][$whenName]['variables'][] = $variables;
}
$triggers[$tableName][$eventName][$whenName]['sql'][] = $sql;
}
}
}
// now spit out the sql
foreach ($triggers as $tableName => $tables) {
if ($onlyTableName != NULL && $onlyTableName != $tableName) {
continue;
}
foreach ($tables as $eventName => $events) {
foreach ($events as $whenName => $parts) {
$varString = implode("\n", $parts['variables']);
$sqlString = implode("\n", $parts['sql']);
$validName = CRM_Core_DAO::shortenSQLName($tableName, 48, TRUE);
$triggerName = "{$validName}_{$whenName}_{$eventName}";
$triggerSQL = "CREATE TRIGGER {$triggerName} {$whenName} {$eventName} ON {$tableName} FOR EACH ROW BEGIN {$varString} {$sqlString} END";
CRM_Core_DAO::executeQuery("DROP TRIGGER IF EXISTS {$triggerName}");
CRM_Core_DAO::executeQuery($triggerSQL, array(), TRUE, NULL, FALSE, FALSE);
}
}
}
}
/**
* Drop triggers for all logged tables.
*
* @param string $tableName
*/
public function dropTriggers($tableName = NULL)
{
/** @var \Civi\Core\SqlTriggers $sqlTriggers */
$sqlTriggers = Civi::service('sql_triggers');
$dao = new CRM_Core_DAO();
if ($tableName) {
$tableNames = array($tableName);
} else {
$tableNames = $this->tables;
}
foreach ($tableNames as $table) {
$validName = CRM_Core_DAO::shortenSQLName($table, 48, TRUE);
// before triggers
$sqlTriggers->enqueueQuery("DROP TRIGGER IF EXISTS {$validName}_before_insert");
$sqlTriggers->enqueueQuery("DROP TRIGGER IF EXISTS {$validName}_before_update");
$sqlTriggers->enqueueQuery("DROP TRIGGER IF EXISTS {$validName}_before_delete");
// after triggers
$sqlTriggers->enqueueQuery("DROP TRIGGER IF EXISTS {$validName}_after_insert");
$sqlTriggers->enqueueQuery("DROP TRIGGER IF EXISTS {$validName}_after_update");
$sqlTriggers->enqueueQuery("DROP TRIGGER IF EXISTS {$validName}_after_delete");
}
// now lets also be safe and drop all triggers that start with
// civicrm_ if we are dropping all triggers
// we need to do this to capture all the leftover triggers since
// we did the shortening trigger name for CRM-11794
if ($tableName === NULL) {
$triggers = $dao->executeQuery("SHOW TRIGGERS LIKE 'civicrm_%'");
while ($triggers->fetch()) {
$sqlTriggers->enqueueQuery("DROP TRIGGER IF EXISTS {$triggers->Trigger}");
}
}
}
