Patch to enable Logging on multilingual site

[Matthias de MAUROY - CYIM]

Hello everyone,

I'm currently working on a multilingual (French/English) Drupal/CiviCRM site and I tried to activate the Logging feature. Due to some DB errors it was not possible and I had to modify the following files:

Code: [Select]

CRM_Core_DAO
line 1980 : adding code => $sqlString = CRM_Core_I18n_Schema::rewriteQuery($sqlString); // to rewrite trigger when site is multi-lingual

CRM_Core_DAO_AllCoreTables
line 765  : modifying civicrm_events_in_carts to civicrm_event_in_carts // to prevent mysql error when using rewriteQuery() on Logging activation

CRM_Event_Cart_DAO_EventInCart
line 45,113 and 117 : modifying civicrm_events_in_carts to civicrm_event_in_carts // to prevent mysql error when using rewriteQuery() on Logging activation


Code: [Select]

CRM_Logging_Differ :
line 313 : modifying code to $sql = "SELECT id,title";
if($tsLocale){
$sql.= "_".$tsLocale;
}
$sql .= " FROM `{$this->db}`.log_civicrm_custom_group WHERE log_date <= %2 AND table_name = %3 ORDER BY log_date DESC LIMIT 1";
// Trying to add selected language in a pretty dirty way...


Code: [Select]

CRM_Logging_Differ :
line 318 : modifying code to $sql = "SELECT column_name, data_type, label";
if($tsLocale){
$sql .= "_".$tsLocale;
}
$sql .= ", name, option_group_id
FROM   `{$this->db}`.log_civicrm_custom_field
WHERE  log_date <= %2
AND    custom_group_id = %3
ORDER BY log_date
";
// Trying to add selected language in a pretty dirty way...


Code: [Select]

CRM_Logging_Differ :
line 340 : modifying code to $sql = "
SELECT   label";
if($tsLocale){
$sql.= "_".$tsLocale;
}
$sql.=", value
FROM     `{$this->db}`.log_civicrm_option_value
WHERE    log_date <= %2
AND      option_group_id = %3
ORDER BY log_date
";
// Trying to add selected language in a pretty dirty way...


Code: [Select]

CRM_Logging_Schema
line 207 : modifying from $this->fixSchemaDifferencesForALL(); to $this->fixSchemaDifferencesForALL(TRUE); //Force trigger to rebuild themselves

civicrm.mysql
line 66,4636,4639 and 4649 : modifying civicrm_events_in_carts to civicrm_event_in_carts // to prevent mysql error when using rewriteQuery() on Logging activation

civicrm_drop.mysql
line 37 : modifying civicrm_events_in_carts to civicrm_event_in_carts // to prevent mysql error when using rewriteQuery() on Logging activation

civicrm_generated.mysql
line 467,470,471 and 472 : modifying civicrm_events_in_carts to civicrm_event_in_carts // to prevent mysql error when using rewriteQuery() on Logging activation


After modifying CiviCRM Core it's working. The only issue I still have is that modified custom labels are not displayed on the logging details report.


More over, I had huge performance problems when displaying the logging details. The SQL query for gettings the notes uses an 'OR' whereas using an 'UNION' would be much more efficient. (on my localhost request it took from 10 to 15 minutes... and now 3 seconds...)

Code: [Select]

CRM_Logging_Differ :
line 80,81 : adding code => $contactIdClause = "AND ( entity_id = %3 AND entity_table = 'civicrm_contact' ) UNION";
                            $contactNoteClose = "AND entity_id IN (SELECT note.id FROM `{$this->db}`.log_civicrm_note note WHERE note.entity_id = %3 AND note.entity_table = 'civicrm_contact') AND entity_table = 'civicrm_note'";
line 133 to 143 : adding code => if ($table == 'civicrm_note') {
if ( !$contactID )
$sql .= "UNION";
$sql .= "
SELECT DISTINCT lt.id FROM `{$this->db}`.`log_$table` lt
{$join}
WHERE lt.log_conn_id = %1 AND
lt.log_date BETWEEN DATE_SUB(%2, INTERVAL {$this->interval}) AND DATE_ADD(%2, INTERVAL {$this->interval})
{$contactNoteClose}";
}



I know that the code is pretty dirty but I'm trying to be sure that this is the way to patch it, while waiting for correction. I've read something about this being corrected on 4.6 version of CiviCRM (sorry, I can't find the source). But some people might want to correct this to be able to log everything fine.

Should I create a ticket in JIRA ?

What do you think of my correction ? Any advice and remark is welcome.

[Deepak Srivastava]

CRM_Core_DAO
line 1980 : adding code => $sqlString = CRM_Core_I18n_Schema::rewriteQuery($sqlString); // to rewrite trigger when site is multi-lingual

Assuming this you want to do for $sqlString embedded in $triggerSQL, seems fine.

CRM_Core_DAO_AllCoreTables
line 765  : modifying civicrm_events_in_carts to civicrm_event_in_carts // to prevent mysql error when using rewriteQuery() on Logging activation

CRM_Event_Cart_DAO_EventInCart
line 45,113 and 117 : modifying civicrm_events_in_carts to civicrm_event_in_carts // to prevent mysql error when using rewriteQuery() on Logging activation

For v4.5 dev, i see the table as "civicrm_events_in_carts", so might not be valid if submitting patch for 4.5. And if its same in 4.4 might want to investigate further as why that change is required.
Same comment for all civicrm_events_in_carts related changes.

Your CRM_Logging_Differ related changes looks fine.

CRM_Logging_Schema
line 207 : modifying from $this->fixSchemaDifferencesForALL(); to $this->fixSchemaDifferencesForALL(TRUE); //Force trigger to rebuild themselves

Could you explain why you had to do that? The code is like:

Code: [Select]

    if ($config->logging) {
      $this->fixSchemaDifferencesForALL();
    }
    // invoke the meta trigger creation call
    CRM_Core_DAO::triggerRebuild(NULL, TRUE);
So it takes care of rebuilding the trigger at the end.

I'm not sure about your OR vs UNION change. Both have different purpose. Might want to compare the result set of both cases and decide.

Should I create a ticket in JIRA ?

Yes, if you could provide a cleaner patch, and clear the confusion area.
The impact seems a bit bigger and would require testing the code for both default and multilingual case. Make sure you do that (on 4.5 dev).