[Summary view]

   1  <?php
   2  /**
   3   * Copyright 2013-2017 Horde LLC (http://www.horde.org/)
   4   *
   5   * See the enclosed file LICENSE for license information (LGPL). If you
   6   * did not receive this file, see http://www.horde.org/licenses/lgpl21.
   7   *
   8   * @category  Horde
   9   * @copyright 2013-2017 Horde LLC
  10   * @license   http://www.horde.org/licenses/lgpl21 LGPL 2.1
  11   * @package   Imap_Client
  12   */
  13  
  14  /**
  15   * A SQL database implementation for caching IMAP/POP data.
  16   * Requires the Horde_Db package.
  17   *
  18   * @author    Michael Slusarz <slusarz@horde.org>
  19   * @category  Horde
  20   * @copyright 2013-2017 Horde LLC
  21   * @license   http://www.horde.org/licenses/lgpl21 LGPL 2.1
  22   * @package   Imap_Client
  23   */
  24  class Horde_Imap_Client_Cache_Backend_Db
  25  extends Horde_Imap_Client_Cache_Backend
  26  {
  27      /** SQL table names. */
  28      const BASE_TABLE = 'horde_imap_client_data';
  29      const MD_TABLE = 'horde_imap_client_metadata';
  30      const MSG_TABLE = 'horde_imap_client_message';
  31  
  32      /**
  33       * Handle for the database connection.
  34       *
  35       * @var Horde_Db_Adapter
  36       */
  37      protected $_db;
  38  
  39      /**
  40       * Constructor.
  41       *
  42       * @param array $params  Configuration parameters:
  43       * <pre>
  44       *   - REQUIRED Parameters:
  45       *     - db: (Horde_Db_Adapter) DB object.
  46       * </pre>
  47       */
  48      public function __construct(array $params = array())
  49      {
  50          if (!isset($params['db'])) {
  51              throw new InvalidArgumentException('Missing db parameter.');
  52          }
  53  
  54          parent::__construct($params);
  55      }
  56  
  57      /**
  58       */
  59      protected function _initOb()
  60      {
  61          $this->_db = $this->_params['db'];
  62      }
  63  
  64      /**
  65       */
  66      public function get($mailbox, $uids, $fields, $uidvalid)
  67      {
  68          $this->getMetaData($mailbox, $uidvalid, array('uidvalid'));
  69  
  70          $query = $this->_baseSql($mailbox, self::MSG_TABLE);
  71          $query[0] = 'SELECT t.data, t.msguid ' . $query[0];
  72  
  73          $uid_query = array();
  74          foreach ($uids as $val) {
  75              $uid_query[] = 't.msguid = ?';
  76              $query[1][] = strval($val);
  77          }
  78          $query[0] .= ' AND (' . implode(' OR ', $uid_query) . ')';
  79  
  80          $compress = new Horde_Compress_Fast();
  81          $out = array();
  82  
  83          try {
  84              $columns = $this->_db->columns(self::MSG_TABLE);
  85              $res = $this->_db->select($query[0], $query[1]);
  86  
  87              foreach ($res as $row) {
  88                  try {
  89                      $out[$row['msguid']] = @unserialize($compress->decompress(
  90                          $columns['data']->binaryToString($row['data'])
  91                      ));
  92                  } catch (Exception $e) {}
  93              }
  94          } catch (Horde_Db_Exception $e) {}
  95  
  96          return $out;
  97      }
  98  
  99      /**
 100       */
 101      public function getCachedUids($mailbox, $uidvalid)
 102      {
 103          $this->getMetaData($mailbox, $uidvalid, array('uidvalid'));
 104  
 105          $query = $this->_baseSql($mailbox, self::MSG_TABLE);
 106          $query[0] = 'SELECT DISTINCT t.msguid ' . $query[0];
 107  
 108          try {
 109              return $this->_db->selectValues($query[0], $query[1]);
 110          } catch (Horde_Db_Exception $e) {
 111              return array();
 112          }
 113      }
 114  
 115      /**
 116       */
 117      public function set($mailbox, $data, $uidvalid)
 118      {
 119          if ($uid = $this->_getUid($mailbox)) {
 120              $res = $this->get($mailbox, array_keys($data), array(), $uidvalid);
 121          } else {
 122              $res = array();
 123              $uid = $this->_createUid($mailbox);
 124          }
 125  
 126          $compress = new Horde_Compress_Fast();
 127  
 128          foreach ($data as $key => $val) {
 129              if (isset($res[$key])) {
 130                  try {
 131                      /* Update */
 132                      $this->_db->updateBlob(
 133                          self::MSG_TABLE,
 134                          array('data' => new Horde_Db_Value_Binary($compress->compress(serialize(array_merge($res[$key], $val))))),
 135                          array(
 136                              'messageid = ? AND msguid = ?',
 137                              array($uid, strval($key))
 138                          )
 139                      );
 140                  } catch (Horde_Db_Exception $e) {}
 141              } else {
 142                  /* Insert */
 143                  try {
 144                      $this->_db->insertBlob(
 145                          self::MSG_TABLE,
 146                          array(
 147                              'data' => new Horde_Db_Value_Binary($compress->compress(serialize($val))),
 148                              'msguid' => strval($key),
 149                              'messageid' => $uid
 150                          )
 151                      );
 152                  } catch (Horde_Db_Exception $e) {}
 153              }
 154          }
 155  
 156          /* Update modified time. */
 157          try {
 158              $this->_db->update(
 159                  sprintf(
 160                      'UPDATE %s SET modified = ? WHERE messageid = ?',
 161                      self::BASE_TABLE
 162                  ),
 163                  array(time(), $uid)
 164              );
 165          } catch (Horde_Db_Exception $e) {}
 166  
 167          /* Update uidvalidity. */
 168          $this->setMetaData($mailbox, array('uidvalid' => $uidvalid));
 169      }
 170  
 171      /**
 172       */
 173      public function getMetaData($mailbox, $uidvalid, $entries)
 174      {
 175          $query = $this->_baseSql($mailbox, self::MD_TABLE);
 176          $query[0] = 'SELECT t.field, t.data ' . $query[0];
 177  
 178          if (!empty($entries)) {
 179              $entries[] = 'uidvalid';
 180              $entry_query = array();
 181  
 182              foreach (array_unique($entries) as $val) {
 183                  $entry_query[] = 't.field = ?';
 184                  $query[1][] = $val;
 185              }
 186              $query[0] .= ' AND (' . implode(' OR ', $entry_query) . ')';
 187          }
 188  
 189          try {
 190              if ($res = $this->_db->selectAssoc($query[0], $query[1])) {
 191                  $columns = $this->_db->columns(self::MD_TABLE);
 192                  foreach ($res as $key => $val) {
 193                      switch ($key) {
 194                      case 'uidvalid':
 195                          $res[$key] = $columns['data']->binaryToString($val);
 196                          break;
 197  
 198                      default:
 199                          try {
 200                              $res[$key] = @unserialize(
 201                                  $columns['data']->binaryToString($val)
 202                              );
 203                          } catch (Exception $e) {}
 204                          break;
 205                      }
 206                  }
 207  
 208                  if (is_null($uidvalid) ||
 209                      !isset($res['uidvalid']) ||
 210                      ($res['uidvalid'] == $uidvalid)) {
 211                      return $res;
 212                  }
 213  
 214                  $this->deleteMailbox($mailbox);
 215              }
 216          } catch (Horde_Db_Exception $e) {}
 217  
 218          return array();
 219      }
 220  
 221      /**
 222       */
 223      public function setMetaData($mailbox, $data)
 224      {
 225          if (!($uid = $this->_getUid($mailbox))) {
 226              $uid = $this->_createUid($mailbox);
 227          }
 228  
 229          $query = sprintf('SELECT field FROM %s where messageid = ?', self::MD_TABLE);
 230          $values = array($uid);
 231  
 232          try {
 233              $fields = $this->_db->selectValues($query, $values);
 234          } catch (Horde_Db_Exception $e) {
 235              return;
 236          }
 237  
 238          foreach ($data as $key => $val) {
 239              $val = new Horde_Db_Value_Binary(($key == 'uidvalid') ? $val : serialize($val));
 240  
 241              if (in_array($key, $fields)) {
 242                  /* Update */
 243                  try {
 244                      $this->_db->updateBlob(
 245                          self::MD_TABLE,
 246                          array('data' => $val),
 247                          array('field = ? AND messageid = ?', array($key, $uid))
 248                      );
 249                  } catch (Horde_Db_Exception $e) {}
 250              } else {
 251                  /* Insert */
 252                  try {
 253                      $this->_db->insertBlob(
 254                          self::MD_TABLE,
 255                          array('data' => $val, 'field' => $key, 'messageid' => $uid)
 256                      );
 257                  } catch (Horde_Db_Exception $e) {}
 258              }
 259          }
 260      }
 261  
 262      /**
 263       */
 264      public function deleteMsgs($mailbox, $uids)
 265      {
 266          if (empty($uids)) {
 267              return;
 268          }
 269  
 270          $query = $this->_baseSql($mailbox);
 271          $query[0] = sprintf(
 272              'DELETE FROM %s WHERE messageid IN (SELECT messageid ' . $query[0] . ')',
 273              self::MSG_TABLE
 274          );
 275  
 276          $uid_query = array();
 277          foreach ($uids as $val) {
 278              $uid_query[] = 'msguid = ?';
 279              $query[1][] = strval($val);
 280          }
 281          $query[0] .= ' AND (' . implode(' OR ', $uid_query) . ')';
 282  
 283          try {
 284              $this->_db->delete($query[0], $query[1]);
 285          } catch (Horde_Db_Exception $e) {}
 286      }
 287  
 288      /**
 289       */
 290      public function deleteMailbox($mailbox)
 291      {
 292          if (is_null($uid = $this->_getUid($mailbox))) {
 293              return;
 294          }
 295  
 296          foreach (array(self::BASE_TABLE, self::MD_TABLE, self::MSG_TABLE) as $val) {
 297              try {
 298                  $this->_db->delete(
 299                      sprintf('DELETE FROM %s WHERE messageid = ?', $val),
 300                      array($uid)
 301                  );
 302              } catch (Horde_Db_Exception $e) {}
 303          }
 304      }
 305  
 306      /**
 307       */
 308      public function clear($lifetime)
 309      {
 310          if (is_null($lifetime)) {
 311              try {
 312                  $this->_db->delete(sprintf('DELETE FROM %s', self::BASE_TABLE));
 313                  $this->_db->delete(sprintf('DELETE FROM %s', self::MD_TABLE));
 314                  $this->_db->delete(sprintf('DELETE FROM %s', self::MSG_TABLE));
 315              } catch (Horde_Db_Exception $e) {}
 316              return;
 317          }
 318  
 319          $purge = time() - $lifetime;
 320          $sql = 'DELETE FROM %s WHERE messageid IN (SELECT messageid FROM %s WHERE modified < ?)';
 321  
 322          foreach (array(self::MD_TABLE, self::MSG_TABLE) as $val) {
 323              try {
 324                  $this->_db->delete(
 325                      sprintf($sql, $val, self::BASE_TABLE),
 326                      array($purge)
 327                  );
 328              } catch (Horde_Db_Exception $e) {
 329              }
 330          }
 331  
 332          try {
 333              $this->_db->delete(
 334                  sprintf('DELETE FROM %s WHERE modified < ?', self::BASE_TABLE),
 335                  array($purge)
 336              );
 337          } catch (Horde_Db_Exception $e) {
 338          }
 339      }
 340  
 341      /**
 342       * Prepare the base SQL query.
 343       *
 344       * @param string $mailbox  The mailbox.
 345       * @param string $join     The table to join with the base table.
 346       *
 347       * @return array  SQL query and bound parameters.
 348       */
 349      protected function _baseSql($mailbox, $join = null)
 350      {
 351          $sql = sprintf('FROM %s d', self::BASE_TABLE);
 352  
 353          if (!is_null($join)) {
 354              $sql .= sprintf(' INNER JOIN %s t ON d.messageid = t.messageid', $join);
 355          }
 356  
 357          return array(
 358              $sql . ' WHERE d.hostspec = ? AND d.port = ? AND d.username = ? AND d.mailbox = ?',
 359              array(
 360                  $this->_params['hostspec'],
 361                  $this->_params['port'],
 362                  $this->_params['username'],
 363                  $mailbox
 364              )
 365          );
 366      }
 367  
 368      /**
 369       * @param string $mailbox
 370       *
 371       * @return string  UID from base table.
 372       */
 373      protected function _getUid($mailbox)
 374      {
 375          $query = $this->_baseSql($mailbox);
 376          $query[0] = 'SELECT d.messageid ' . $query[0];
 377  
 378          try {
 379              return $this->_db->selectValue($query[0], $query[1]);
 380          } catch (Horde_Db_Exception $e) {
 381              return null;
 382          }
 383      }
 384  
 385      /**
 386       * @param string $mailbox
 387       *
 388       * @return string  UID from base table.
 389       */
 390      protected function _createUid($mailbox)
 391      {
 392          return $this->_db->insert(
 393              sprintf(
 394                  'INSERT INTO %s (hostspec, mailbox, port, username) ' .
 395                      'VALUES (?, ?, ?, ?)',
 396                  self::BASE_TABLE
 397              ),
 398              array(
 399                  $this->_params['hostspec'],
 400                  $mailbox,
 401                  $this->_params['port'],
 402                  $this->_params['username']
 403              )
 404          );
 405      }
 406  
 407  }