From b27d1855636546a435e5f5a1618264271b2fb048 Mon Sep 17 00:00:00 2001 From: eileen Date: Sat, 31 Dec 2016 09:22:26 +1300 Subject: [PATCH] CRM-19811 comment further instances of LOWER(). Also fix historical sql to guard against copy & paste --- CRM/Contact/BAO/Query.php | 3 ++- CRM/Contribute/BAO/Query.php | 1 + CRM/Custom/Form/Field.php | 4 ++++ CRM/Mailing/BAO/Query.php | 2 ++ CRM/Upgrade/Incremental/sql/4.3.4.mysql.tpl | 6 +++--- CRM/Upgrade/Incremental/sql/4.5.alpha1.mysql.tpl | 6 +++--- CRM/Utils/System/Backdrop.php | 2 ++ CRM/Utils/System/Drupal.php | 2 ++ CRM/Utils/System/Drupal6.php | 1 + CRM/Utils/System/Joomla.php | 2 ++ 10 files changed, 22 insertions(+), 7 deletions(-) diff --git a/CRM/Contact/BAO/Query.php b/CRM/Contact/BAO/Query.php index bfe7173e00..bfdfc0e177 100644 --- a/CRM/Contact/BAO/Query.php +++ b/CRM/Contact/BAO/Query.php @@ -2182,6 +2182,7 @@ class CRM_Contact_BAO_Query { $op = 'LIKE'; $value = self::getWildCardedValue($wildcard, $op, $value); } + // LOWER roughly translates to 'hurt my database without deriving any benefit' See CRM-19811. $wc = self::caseImportant($op) ? "LOWER({$field['where']})" : "{$field['where']}"; $this->_where[$grouping][] = self::buildClause($wc, $op, "'$value'"); $this->_qill[$grouping][] = "$field[title] $op \"$value\""; @@ -2255,7 +2256,7 @@ class CRM_Contact_BAO_Query { //get the location name list($tName, $fldName) = self::getLocationTableName($field['where'], $locType); - + // LOWER roughly translates to 'hurt my database without deriving any benefit' See CRM-19811. $fieldName = "LOWER(`$tName`.$fldName)"; // we set both _tables & whereTables because whereTables doesn't seem to do what the name implies it should diff --git a/CRM/Contribute/BAO/Query.php b/CRM/Contribute/BAO/Query.php index b152dedf5c..0702247218 100644 --- a/CRM/Contribute/BAO/Query.php +++ b/CRM/Contribute/BAO/Query.php @@ -538,6 +538,7 @@ class CRM_Contribute_BAO_Query extends CRM_Core_BAO_Query { $value = "%$value%"; $op = 'LIKE'; } + // LOWER roughly translates to 'hurt my database without deriving any benefit' See CRM-19811. $wc = ($op != 'LIKE') ? "LOWER(civicrm_note.note)" : "civicrm_note.note"; $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($wc, $op, $value, "String"); $query->_qill[$grouping][] = ts('Contribution Note %1 %2', array(1 => $op, 2 => $quoteValue)); diff --git a/CRM/Custom/Form/Field.php b/CRM/Custom/Form/Field.php index 4c310f922a..f62d69df44 100644 --- a/CRM/Custom/Form/Field.php +++ b/CRM/Custom/Form/Field.php @@ -959,6 +959,8 @@ AND option_group_id = %2"; switch ($params['data_type']) { case 'StateProvince': $fieldStateProvince = $strtolower($params['default_value']); + + // LOWER in query below roughly translates to 'hurt my database without deriving any benefit' See CRM-19811. $query = " SELECT id FROM civicrm_state_province @@ -972,6 +974,8 @@ SELECT id case 'Country': $fieldCountry = $strtolower($params['default_value']); + + // LOWER in query below roughly translates to 'hurt my database without deriving any benefit' See CRM-19811. $query = " SELECT id FROM civicrm_country diff --git a/CRM/Mailing/BAO/Query.php b/CRM/Mailing/BAO/Query.php index 5e563472e2..3fc792460c 100644 --- a/CRM/Mailing/BAO/Query.php +++ b/CRM/Mailing/BAO/Query.php @@ -260,6 +260,8 @@ class CRM_Mailing_BAO_Query { $value = "%$value%"; $op = 'LIKE'; } + + // LOWER in query below roughly translates to 'hurt my database without deriving any benefit' See CRM-19811. $query->_where[$grouping][] = "LOWER(civicrm_mailing.name) $op '$value'"; $query->_qill[$grouping][] = "Mailing Namename $op \"$value\""; $query->_tables['civicrm_mailing'] = $query->_whereTables['civicrm_mailing'] = 1; diff --git a/CRM/Upgrade/Incremental/sql/4.3.4.mysql.tpl b/CRM/Upgrade/Incremental/sql/4.3.4.mysql.tpl index 626b50290e..d564c44fb7 100644 --- a/CRM/Upgrade/Incremental/sql/4.3.4.mysql.tpl +++ b/CRM/Upgrade/Incremental/sql/4.3.4.mysql.tpl @@ -38,7 +38,7 @@ SELECT @domainContactId := contact_id from civicrm_domain where id = {$domainID} -- for Accounts Receivable Account is SELECT @option_value_rel_id_ar := value FROM civicrm_option_value WHERE option_group_id = @option_group_id_arel AND name = 'Accounts Receivable Account is'; -SELECT @arAccount := id FROM civicrm_financial_account WHERE LOWER(name) = 'accounts receivable'; +SELECT @arAccount := id FROM civicrm_financial_account WHERE name = 'accounts receivable'; SELECT @arAccountEntity := financial_account_id FROM civicrm_entity_financial_account WHERE account_relationship = @option_value_rel_id_ar AND entity_table = 'civicrm_financial_type' LIMIT 1; @@ -59,7 +59,7 @@ SELECT cft.name, @domainContactId, @opval, cft.name as description, 'INC', 1 FROM civicrm_financial_type cft LEFT JOIN civicrm_entity_financial_account ceft ON ceft.entity_id = cft.id AND ceft.account_relationship = @option_value_rel_id AND ceft.entity_table = 'civicrm_financial_type' -LEFT JOIN civicrm_financial_account ca ON LOWER(ca.name) = LOWER(cft.name) +LEFT JOIN civicrm_financial_account ca ON ca.name = cft.name WHERE ceft.entity_id IS NULL AND ca.id IS NULL; INSERT INTO civicrm_entity_financial_account(entity_table, entity_id, account_relationship, financial_account_id) @@ -67,7 +67,7 @@ SELECT 'civicrm_financial_type', cft.id, @option_value_rel_id, ca.id FROM civicrm_financial_type cft LEFT JOIN civicrm_entity_financial_account ceft ON ceft.entity_id = cft.id AND ceft.account_relationship = @option_value_rel_id AND ceft.entity_table = 'civicrm_financial_type' -LEFT JOIN civicrm_financial_account ca ON LOWER(ca.name) = LOWER(cft.name) +LEFT JOIN civicrm_financial_account ca ON ca.name = cft.name WHERE ceft.entity_id IS NULL; -- for cost of sales diff --git a/CRM/Upgrade/Incremental/sql/4.5.alpha1.mysql.tpl b/CRM/Upgrade/Incremental/sql/4.5.alpha1.mysql.tpl index 14339e7ff2..540c96db3c 100644 --- a/CRM/Upgrade/Incremental/sql/4.5.alpha1.mysql.tpl +++ b/CRM/Upgrade/Incremental/sql/4.5.alpha1.mysql.tpl @@ -428,7 +428,7 @@ SELECT 'SnapChat' AS website UNION ALL SELECT 'Vine' AS website ) AS temp -LEFT JOIN civicrm_option_value co ON LOWER(co.name) = LOWER(temp.website) +LEFT JOIN civicrm_option_value co ON co.name = temp.website AND option_group_id = @option_web_id WHERE co.id IS NULL; @@ -481,7 +481,7 @@ CASE THEN 'Ineligible' ELSE 'Submitted' END -WHERE option_group_id = @option_grant_status and LOWER(name) IN ('granted', 'pending', 'approved', 'rejected'); +WHERE option_group_id = @option_grant_status and name IN ('granted', 'pending', 'approved', 'rejected'); SELECT @grant_value := max(cast(value as UNSIGNED)) FROM civicrm_option_value WHERE option_group_id = @option_grant_status; SELECT @grant_weight := max(weight) FROM civicrm_option_value WHERE option_group_id = @option_grant_status; @@ -498,7 +498,7 @@ SELECT 'Awaiting Information' AS grantstatus UNION ALL SELECT 'Withdrawn' AS grantstatus ) AS temp -LEFT JOIN civicrm_option_value co ON LOWER(co.name) = LOWER(temp.grantstatus) +LEFT JOIN civicrm_option_value co ON co.name = temp.grantstatus AND option_group_id = @option_grant_status WHERE co.id IS NULL; diff --git a/CRM/Utils/System/Backdrop.php b/CRM/Utils/System/Backdrop.php index 72c177ebe0..f740399f5e 100644 --- a/CRM/Utils/System/Backdrop.php +++ b/CRM/Utils/System/Backdrop.php @@ -336,6 +336,8 @@ class CRM_Utils_System_Backdrop extends CRM_Utils_System_DrupalBase { $strtolower = function_exists('mb_strtolower') ? 'mb_strtolower' : 'strtolower'; $name = $dbBackdrop->escapeSimple($strtolower($name)); $userFrameworkUsersTableName = $this->getUsersTableName(); + + // LOWER in query below roughly translates to 'hurt my database without deriving any benefit' See CRM-19811. $sql = " SELECT u.* FROM {$userFrameworkUsersTableName} u diff --git a/CRM/Utils/System/Drupal.php b/CRM/Utils/System/Drupal.php index 646b9250f4..c5b096b435 100644 --- a/CRM/Utils/System/Drupal.php +++ b/CRM/Utils/System/Drupal.php @@ -349,6 +349,8 @@ class CRM_Utils_System_Drupal extends CRM_Utils_System_DrupalBase { $strtolower = function_exists('mb_strtolower') ? 'mb_strtolower' : 'strtolower'; $name = $dbDrupal->escapeSimple($strtolower($name)); $userFrameworkUsersTableName = $this->getUsersTableName(); + + // LOWER in query below roughly translates to 'hurt my database without deriving any benefit' See CRM-19811. $sql = " SELECT u.* FROM {$userFrameworkUsersTableName} u diff --git a/CRM/Utils/System/Drupal6.php b/CRM/Utils/System/Drupal6.php index 435e70b282..60bf1a5c6c 100644 --- a/CRM/Utils/System/Drupal6.php +++ b/CRM/Utils/System/Drupal6.php @@ -165,6 +165,7 @@ class CRM_Utils_System_Drupal6 extends CRM_Utils_System_DrupalBase { $errors['cms_name'] = $nameError; } + // LOWER in query below roughly translates to 'hurt my database without deriving any benefit' See CRM-19811. $sql = " SELECT name, mail FROM {users} diff --git a/CRM/Utils/System/Joomla.php b/CRM/Utils/System/Joomla.php index 325a169717..ff7599cd4e 100644 --- a/CRM/Utils/System/Joomla.php +++ b/CRM/Utils/System/Joomla.php @@ -136,6 +136,8 @@ class CRM_Utils_System_Joomla extends CRM_Utils_System_Base { $query = $db->getQuery(TRUE); $query->select('username, email'); $query->from($JUserTable->getTableName()); + + // LOWER in query below roughly translates to 'hurt my database without deriving any benefit' See CRM-19811. $query->where('(LOWER(username) = LOWER(\'' . $name . '\')) OR (LOWER(email) = LOWER(\'' . $email . '\'))'); $db->setQuery($query, 0, 10); $users = $db->loadAssocList(); -- 2.25.1