Ejegg has submitted this change and it was merged.
Change subject: Rewrite all "LIKE" queries on civicrm_prevnext_cache
......................................................................
Rewrite all "LIKE" queries on civicrm_prevnext_cache
We can't afford to be this sloppy.
Anyway, wish us luck keeping this in sync with upstream changes.
Change-Id: I2197b847ad8c272230ec4595f23efedeae8edd0c
---
M CRM/Contact/Selector.php
M CRM/Core/BAO/PrevNextCache.php
2 files changed, 43 insertions(+), 26 deletions(-)
Approvals:
Ejegg: Looks good to me, approved
jenkins-bot: Verified
diff --git a/CRM/Contact/Selector.php b/CRM/Contact/Selector.php
index 9dd2098..b0668c1 100644
--- a/CRM/Contact/Selector.php
+++ b/CRM/Contact/Selector.php
@@ -799,7 +799,7 @@
$sortByCharacter = CRM_Utils_Request::retrieve('sortByCharacter',
'String', CRM_Core_DAO::$_nullObject);
//for text field pagination selection save
- $countRow = CRM_Core_BAO_PrevNextCache::getCount("civicrm search
{$cacheKey}%", NULL, "entity_table = 'civicrm_contact'", "LIKE");
+ $countRow = CRM_Core_BAO_PrevNextCache::getCount("civicrm search
{$cacheKey}", NULL, "entity_table = 'civicrm_contact'", "LIKE");
if ((!$crmPID || $countRow == 0) && !$sortByCharacter) {
$this->fillupPrevNextCache($sort);
diff --git a/CRM/Core/BAO/PrevNextCache.php b/CRM/Core/BAO/PrevNextCache.php
index d401c05..b788122 100644
--- a/CRM/Core/BAO/PrevNextCache.php
+++ b/CRM/Core/BAO/PrevNextCache.php
@@ -116,8 +116,9 @@
}
if (isset($cacheKey)) {
- $sql .= " AND cacheKey LIKE %3";
- $params[3] = array("{$cacheKey}%", 'String');
+ $sql .= " AND ( cacheKey = %3 OR cacheKey = %4 )";
+ $params[3] = array("{$cacheKey}", 'String');
+ $params[4] = array("{$cacheKey}_alphabet", 'String');
}
CRM_Core_DAO::executeQuery($sql, $params);
}
@@ -132,8 +133,9 @@
$params[3] = array($id2, 'Integer');
if (isset($cacheKey)) {
- $sql .= " AND cacheKey LIKE %4";
- $params[4] = array("{$cacheKey}%", 'String');
+ $sql .= " AND ( cacheKey = %4 OR cacheKey = %5 )";
+ $params[4] = array("{$cacheKey}", 'String');
+ $params[5] = array("{$cacheKey}_alphabet", 'String');
}
CRM_Core_DAO::executeQuery($sql, $params);
@@ -192,13 +194,24 @@
$query = "
SELECT COUNT(*) FROM civicrm_prevnext_cache pn
{$join}
-WHERE cacheKey " . $op . " %1
";
+
+ if ( $op === 'LIKE' ) {
+ $query .= "WHERE (cacheKey = %1 OR cacheKey = %2)";
+ $params = array(
+ 1 => array($cacheKey, 'String'),
+ 2 => array($cacheKey . '_alphabet', 'String'),
+ );
+ } else {
+ $query .= "WHERE cacheKey = %1";
+ $params = array(
+ 1 => array($cacheKey, 'String'),
+ );
+ }
+
if ($where) {
$query .= " AND {$where}";
}
-
- $params = array(1 => array($cacheKey, 'String'));
return CRM_Core_DAO::singleValueQuery($query, $params);
}
@@ -217,8 +230,11 @@
}
// 1. Clear cache if any
- $sql = "DELETE FROM civicrm_prevnext_cache WHERE cacheKey LIKE %1";
- CRM_Core_DAO::executeQuery($sql, array(1 => array("{$cacheKeyString}%",
'String')));
+ $sql = "DELETE FROM civicrm_prevnext_cache WHERE ( cacheKey = %1 OR
cacheKey = %2 )";
+ CRM_Core_DAO::executeQuery($sql, array(
+ 1 => array($cacheKeyString, 'String'),
+ 2 => array("{$cacheKeyString}_alphabet", 'String')
+ ));
// FIXME: we need to start using temp tables / queries here instead of
arrays.
// And cleanup code in CRM/Contact/Page/DedupeFind.php
@@ -308,40 +324,40 @@
$entity_whereClause = " AND entity_table = '{$entity_table}'";
if ($cIds && $cacheKey && $action) {
+ $params[1] = array("{$cacheKey}", 'String');
+ $params[2] = array("{$cacheKey}_alphabet", 'String');
+
if (is_array($cIds)) {
$cIdFilter = "(" . implode(',', $cIds) . ")";
$whereClause = "
-WHERE cacheKey LIKE %1
+WHERE (cacheKey = %1 OR cacheKey = %2)
AND (entity_id1 IN {$cIdFilter} OR entity_id2 IN {$cIdFilter})
";
}
else {
$whereClause = "
-WHERE cacheKey LIKE %1
-AND (entity_id1 = %2 OR entity_id2 = %2)
+WHERE (cacheKey = %1 OR cacheKey = %2)
+AND (entity_id1 = %3 OR entity_id2 = %3)
";
- $params[2] = array("{$cIds}", 'Integer');
+ $params[3] = array("{$cIds}", 'Integer');
}
if ($action == 'select') {
$whereClause .= "AND is_selected = 0";
$sql = "
UPDATE civicrm_prevnext_cache SET is_selected = 1 " . $whereClause .
$entity_whereClause;
- $params[1] = array("{$cacheKey}%", 'String');
}
elseif ($action == 'unselect') {
$whereClause .= "AND is_selected = 1";
$sql = "
UPDATE civicrm_prevnext_cache SET is_selected = 0 " . $whereClause .
$entity_whereClause;
- $params[1] = array("{$cacheKey}%", 'String');
}
// default action is reseting
}
elseif (!$cIds && $cacheKey && $action == 'unselect') {
$sql = "
UPDATE civicrm_prevnext_cache SET is_selected = 0
-WHERE cacheKey LIKE %1 AND is_selected = 1
+WHERE (cacheKey = %1 OR cacheKey = %2) AND is_selected = 1
" . $entity_whereClause;
- $params[1] = array("{$cacheKey}%", 'String');
}
CRM_Core_DAO::executeQuery($sql, $params);
}
@@ -366,12 +382,13 @@
$actionGet = ($action == "get") ? " AND is_selected = 1 " : "";
$sql = "
SELECT entity_id1, entity_id2 FROM civicrm_prevnext_cache
-WHERE cacheKey LIKE %1
+WHERE (cacheKey = %1 OR cacheKey = %2)
$actionGet
$entity_whereClause
ORDER BY id
";
- $params[1] = array("{$cacheKey}%", 'String');
+ $params[1] = array("{$cacheKey}", 'String');
+ $params[2] = array("{$cacheKey}_alphabet", 'String');
$contactIds = array($cacheKey => array());
$cIdDao = CRM_Core_DAO::executeQuery($sql, $params);
@@ -390,10 +407,10 @@
$query = "
SELECT *
FROM civicrm_prevnext_cache
-WHERE cacheKey LIKE %1 AND is_selected=1 AND cacheKey NOT LIKE %2
+WHERE cacheKey = %1 AND is_selected=1 AND cacheKey != %2
LIMIT $offset, $rowCount";
- $params1[1] = array("{$cacheKey}%", 'String');
- $params1[2] = array("{$cacheKey}_alphabet%", 'String');
+ $params1[1] = array($cacheKey, 'String');
+ $params1[2] = array("{$cacheKey}_alphabet", 'String');
$dao = CRM_Core_DAO::executeQuery($query, $params1);
while ($dao->fetch()) {
$val[] = $dao->data;
@@ -418,9 +435,9 @@
$query = "
SELECT count(id)
FROM civicrm_prevnext_cache
-WHERE cacheKey LIKE %1 AND is_selected=1 AND cacheKey NOT LIKE %2";
- $params1[1] = array("{$cacheKey}%", 'String');
- $params1[2] = array("{$cacheKey}_alphabet%", 'String');
+WHERE cacheKey = %1 AND is_selected=1 AND cacheKey != %2";
+ $params1[1] = array($cacheKey, 'String');
+ $params1[2] = array("{$cacheKey}_alphabet", 'String');
$paramsTotal = CRM_Core_DAO::singleValueQuery($query, $params1);
$params['total'] = $paramsTotal;
$obj->_pager = new CRM_Utils_Pager($params);
--
To view, visit https://gerrit.wikimedia.org/r/180656
To unsubscribe, visit https://gerrit.wikimedia.org/r/settings
Gerrit-MessageType: merged
Gerrit-Change-Id: I2197b847ad8c272230ec4595f23efedeae8edd0c
Gerrit-PatchSet: 4
Gerrit-Project: wikimedia/fundraising/crm/civicrm
Gerrit-Branch: master
Gerrit-Owner: Awight <[email protected]>
Gerrit-Reviewer: Awight <[email protected]>
Gerrit-Reviewer: Ejegg <[email protected]>
Gerrit-Reviewer: jenkins-bot <>
_______________________________________________
MediaWiki-commits mailing list
[email protected]
https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
