CRM/Core/PrevNextCache/Sql.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 /**
  13  * Class CRM_Core_PrevNextCache_Sql
  14  *
  15  * Store the previous/next cache in a special-purpose SQL table.
  16  */
  17 class CRM_Core_PrevNextCache_Sql implements CRM_Core_PrevNextCache_Interface {
  18
  19   /**
  20    * @var int
  21    * Days for cache to llast forr
  22    */
  23   const cacheDays = 2;
  24
  25   /**
  26    * Store the results of a SQL query in the cache.
  27    * @param string $cacheKey
  28    * @param string $sql
  29    *   A SQL query. The query *MUST* be a SELECT statement which yields
  30    *   the following columns (in order): cachekey, entity_id1, data
  31    * @param array $sqlParams
  32    *   An array of parameters to be used with $sql.
  33    *   Use the same interpolation format as CRM_Core_DAO (composeQuery/executeQuery).
  34    *   Ex: [1 => ['foo', 'String']]
  35    * @return bool
  36    * @throws CRM_Core_Exception
  37    * @see CRM_Core_DAO::composeQuery
  38    */
  39   public function fillWithSql($cacheKey, $sql, $sqlParams = []) {
  40     $insertSQL = "
  41 INSERT INTO civicrm_prevnext_cache (cachekey, entity_id1, data)
  42 ";
  43     $result = CRM_Core_DAO::executeQuery($insertSQL . $sql, $sqlParams, FALSE);
  44     if (is_a($result, 'DB_Error')) {
  45       CRM_Core_Error::deprecatedFunctionWarning('errors are not expected to be returned');
  46       throw new CRM_Core_Exception($result->message);
  47     }
  48     return TRUE;
  49   }
  50
  51   public function fillWithArray($cacheKey, $rows) {
  52     if (empty($rows)) {
  53       return;
  54     }
  55
  56     $insert = CRM_Utils_SQL_Insert::into('civicrm_prevnext_cache')
  57       ->columns([
  58         'entity_id1',
  59         'cachekey',
  60         'data',
  61       ]);
  62
  63     foreach ($rows as &$row) {
  64       $insert->row($row + ['cachekey' => $cacheKey]);
  65     }
  66
  67     CRM_Core_DAO::executeQuery($insert->toSQL());
  68     return TRUE;
  69   }
  70
  71   /**
  72    * Save checkbox selections.
  73    *
  74    * @param string $cacheKey
  75    * @param string $action
  76    *   Ex: 'select', 'unselect'.
  77    * @param array|int|null $ids
  78    *   A list of contact IDs to (un)select.
  79    *   To unselect all contact IDs, use NULL.
  80    */
  81   public function markSelection($cacheKey, $action, $ids = NULL) {
  82     if (!$cacheKey) {
  83       return;
  84     }
  85     $params = [];
  86
  87     if ($ids && $cacheKey && $action) {
  88       if (is_array($ids)) {
  89         $cIdFilter = "(" . implode(',', $ids) . ")";
  90         $whereClause = "
  91 WHERE cachekey = %1
  92 AND (entity_id1 IN {$cIdFilter} OR entity_id2 IN {$cIdFilter})
  93 ";
  94       }
  95       else {
  96         $whereClause = "
  97 WHERE cachekey = %1
  98 AND (entity_id1 = %2 OR entity_id2 = %2)
  99 ";
 100         $params[2] = ["{$ids}", 'Integer'];
 101       }
 102       if ($action == 'select') {
 103         $whereClause .= "AND is_selected = 0";
 104         $sql = "UPDATE civicrm_prevnext_cache SET is_selected = 1 {$whereClause}";
 105         $params[1] = [$cacheKey, 'String'];
 106       }
 107       elseif ($action == 'unselect') {
 108         $whereClause .= "AND is_selected = 1";
 109         $sql = "UPDATE civicrm_prevnext_cache SET is_selected = 0 {$whereClause}";
 110         $params[1] = [$cacheKey, 'String'];
 111       }
 112       // default action is reseting
 113     }
 114     elseif (!$ids && $cacheKey && $action == 'unselect') {
 115       $sql = "
 116 UPDATE civicrm_prevnext_cache
 117 SET    is_selected = 0
 118 WHERE  cachekey = %1 AND is_selected = 1
 119 ";
 120       $params[1] = [$cacheKey, 'String'];
 121     }
 122     CRM_Core_DAO::executeQuery($sql, $params);
 123   }
 124
 125   /**
 126    * Get the selections.
 127    *
 128    * @param string $cacheKey
 129    *   Cache key.
 130    * @param string $action
 131    *   One of the following:
 132    *   - 'get' - get only selection records
 133    *   - 'getall' - get all the records of the specified cache key
 134    *
 135    * @return array|NULL
 136    */
 137   public function getSelection($cacheKey, $action = 'get') {
 138     if (!$cacheKey) {
 139       return NULL;
 140     }
 141     $params = [];
 142
 143     if ($cacheKey && ($action == 'get' || $action == 'getall')) {
 144       $actionGet = ($action == "get") ? " AND is_selected = 1 " : "";
 145       $sql = "
 146 SELECT entity_id1 FROM civicrm_prevnext_cache
 147 WHERE cachekey = %1
 148       $actionGet
 149 ORDER BY id
 150 ";
 151       $params[1] = [$cacheKey, 'String'];
 152
 153       $contactIds = [$cacheKey => []];
 154       $cIdDao = CRM_Core_DAO::executeQuery($sql, $params);
 155       while ($cIdDao->fetch()) {
 156         $contactIds[$cacheKey][$cIdDao->entity_id1] = 1;
 157       }
 158       return $contactIds;
 159     }
 160   }
 161
 162   /**
 163    * Get the previous and next keys.
 164    *
 165    * @param string $cacheKey
 166    * @param int $id1
 167    *
 168    * @return array
 169    */
 170   public function getPositions($cacheKey, $id1) {
 171     $mergeId = CRM_Core_DAO::singleValueQuery(
 172       "SELECT id FROM civicrm_prevnext_cache WHERE cachekey = %2 AND entity_id1 = %1",
 173       [
 174         1 => [$id1, 'Integer'],
 175         2 => [$cacheKey, 'String'],
 176       ]
 177     );
 178
 179     $pos = ['foundEntry' => 0];
 180     if ($mergeId) {
 181       $pos['foundEntry'] = 1;
 182
 183       $sql = "SELECT pn.id, pn.entity_id1, pn.entity_id2, pn.data FROM civicrm_prevnext_cache pn ";
 184       $wherePrev = " WHERE pn.id < %1 AND pn.cachekey = %2 ORDER BY ID DESC LIMIT 1";
 185       $whereNext = " WHERE pn.id > %1 AND pn.cachekey = %2 ORDER BY ID ASC LIMIT 1";
 186       $p = [
 187         1 => [$mergeId, 'Integer'],
 188         2 => [$cacheKey, 'String'],
 189       ];
 190
 191       $dao = CRM_Core_DAO::executeQuery($sql . $wherePrev, $p);
 192       if ($dao->fetch()) {
 193         $pos['prev']['id1'] = $dao->entity_id1;
 194         $pos['prev']['mergeId'] = $dao->id;
 195         $pos['prev']['data'] = $dao->data;
 196       }
 197
 198       $dao = CRM_Core_DAO::executeQuery($sql . $whereNext, $p);
 199       if ($dao->fetch()) {
 200         $pos['next']['id1'] = $dao->entity_id1;
 201         $pos['next']['mergeId'] = $dao->id;
 202         $pos['next']['data'] = $dao->data;
 203       }
 204     }
 205     return $pos;
 206
 207   }
 208
 209   /**
 210    * Delete an item from the prevnext cache table based on the entity.
 211    *
 212    * @param int $id
 213    * @param string $cacheKey
 214    */
 215   public function deleteItem($id = NULL, $cacheKey = NULL) {
 216     $sql = "DELETE FROM civicrm_prevnext_cache WHERE (1)";
 217     $params = [];
 218
 219     if (is_numeric($id)) {
 220       $sql .= " AND ( entity_id1 = %2 OR entity_id2 = %2 )";
 221       $params[2] = [$id, 'Integer'];
 222     }
 223
 224     if (isset($cacheKey)) {
 225       $sql .= " AND cachekey = %3";
 226       $params[3] = [$cacheKey, 'String'];
 227     }
 228     CRM_Core_DAO::executeQuery($sql, $params);
 229   }
 230
 231   /**
 232    * Get count of matching rows.
 233    *
 234    * @param string $cacheKey
 235    * @return int
 236    */
 237   public function getCount($cacheKey) {
 238     $query = "SELECT COUNT(*) FROM civicrm_prevnext_cache pn WHERE pn.cachekey = %1";
 239     $params = [1 => [$cacheKey, 'String']];
 240     return (int) CRM_Core_DAO::singleValueQuery($query, $params, TRUE, FALSE);
 241   }
 242
 243   /**
 244    * Fetch a list of contacts from the prev/next cache for displaying a search results page
 245    *
 246    * @param string $cacheKey
 247    * @param int $offset
 248    * @param int $rowCount
 249    * @return array
 250    *   List of contact IDs.
 251    */
 252   public function fetch($cacheKey, $offset, $rowCount) {
 253     $cids = [];
 254     $dao = CRM_Utils_SQL_Select::from('civicrm_prevnext_cache pnc')
 255       ->where('pnc.cachekey = @cacheKey', ['cacheKey' => $cacheKey])
 256       ->select('pnc.entity_id1 as cid')
 257       ->orderBy('pnc.id')
 258       ->limit($rowCount, $offset)
 259       ->execute();
 260     while ($dao->fetch()) {
 261       $cids[] = $dao->cid;
 262     }
 263     return $cids;
 264   }
 265
 266   /**
 267    * @inheritDoc
 268    */
 269   public function cleanup() {
 270     // clean up all prev next caches older than $cacheTimeIntervalDays days
 271     // first find all the cacheKeys that match this
 272     $sql = "
 273       DELETE     pn, c
 274       FROM       civicrm_cache c
 275       INNER JOIN civicrm_prevnext_cache pn ON c.path = pn.cachekey
 276       WHERE      c.group_name = %1
 277       AND        c.created_date < date_sub( NOW( ), INTERVAL %2 day )
 278     ";
 279     $params = [
 280       1 => [CRM_Core_BAO_Cache::cleanKey('CiviCRM Search PrevNextCache'), 'String'],
 281       2 => [self::cacheDays, 'Integer'],
 282     ];
 283     CRM_Core_DAO::executeQuery($sql, $params);
 284   }
 285
 286 }