[Summary view] [Print] [Text view]

   1  <?php
   2  /**
   3   * Copyright 2013-2014 Horde LLC (http://www.horde.org/)
   4   *
   5   * See the enclosed file COPYING 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-2014 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-2014 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 extends Horde_Imap_Client_Cache_Backend
  25  {
  26      /** SQL table names. */
  27      const BASE_TABLE = 'horde_imap_client_data';
  28      const MD_TABLE = 'horde_imap_client_metadata';
  29      const MSG_TABLE = 'horde_imap_client_message';
  30  
  31      /**
  32       * Handle for the database connection.
  33       *
  34       * @var Horde_Db_Adapter
  35       */
  36      protected $_db;
  37  
  38      /**
  39       * Constructor.
  40       *
  41       * @param array $params  Configuration parameters:
  42       * <pre>
  43       *   - REQUIRED Parameters:
  44       *     - db: (Horde_Db_Adapter) DB object.
  45       * </pre>
  46       */
  47      public function __construct(array $params = array())
  48      {
  49          if (!isset($params['db'])) {
  50              throw new InvalidArgumentException('Missing db parameter.');
  51          }
  52  
  53          parent::__construct($params);
  54      }
  55  
  56      /**
  57       */
  58      protected function _initOb()
  59      {
  60          $this->_db = $this->_params['db'];
  61      }
  62  
  63      /**
  64       */
  65      public function get($mailbox, $uids, $fields, $uidvalid)
  66      {
  67          $this->getMetaData($mailbox, $uidvalid, array('uidvalid'));
  68  
  69          $query = $this->_baseSql($mailbox, self::MSG_TABLE);
  70          $query[0] = 'SELECT t.data, t.msguid ' . $query[0];
  71  
  72          $uid_query = array();
  73          foreach ($uids as $val) {
  74              $uid_query[] = 't.msguid = ?';
  75              $query[1][] = strval($val);
  76          }
  77          $query[0] .= ' AND (' . implode(' OR ', $uid_query) . ')';
  78  
  79          $compress = new Horde_Compress_Fast();
  80          $out = array();
  81  
  82          try {
  83              $columns = $this->_db->columns(self::MSG_TABLE);
  84              $res = $this->_db->select($query[0], $query[1]);
  85  
  86              foreach ($res as $row) {
  87                  $out[$row['msguid']] = @unserialize($compress->decompress(
  88                      $columns['data']->binaryToString($row['data'])
  89                  ));
  90              }
  91          } catch (Horde_Db_Exception $e) {}
  92  
  93          return $out;
  94      }
  95  
  96      /**
  97       */
  98      public function getCachedUids($mailbox, $uidvalid)
  99      {
 100          $this->getMetaData($mailbox, $uidvalid, array('uidvalid'));
 101  
 102          $query = $this->_baseSql($mailbox, self::MSG_TABLE);
 103          $query[0] = 'SELECT DISTINCT t.msguid ' . $query[0];
 104  
 105          try {
 106              return $this->_db->selectValues($query[0], $query[1]);
 107          } catch (Horde_Db_Exception $e) {
 108              return array();
 109          }
 110      }
 111  
 112      /**
 113       */
 114      public function set($mailbox, $data, $uidvalid)
 115      {
 116          if ($uid = $this->_getUid($mailbox)) {
 117              $res = $this->get($mailbox, array_keys($data), array(), $uidvalid);
 118          } else {
 119              $res = array();
 120              $uid = $this->_createUid($mailbox);
 121          }
 122  
 123          $compress = new Horde_Compress_Fast();
 124  
 125          foreach ($data as $key => $val) {
 126              if (isset($res[$key])) {
 127                  try {
 128                      /* Update */
 129                      $this->_db->update(
 130                          sprintf('UPDATE %s SET data = ? WHERE messageid = ? AND msguid = ?', self::MSG_TABLE),
 131                          array(
 132                              new Horde_Db_Value_Binary($compress->compress(serialize(array_merge($res[$key], $val)))),
 133                              $uid,
 134                              strval($key)
 135                          )
 136                      );
 137                  } catch (Horde_Db_Exception $e) {}
 138              } else {
 139                  /* Insert */
 140                  try {
 141                      $this->_db->insert(
 142                          sprintf('INSERT INTO %s (data, msguid, messageid) VALUES (?, ?, ?)', self::MSG_TABLE),
 143                          array(
 144                              new Horde_Db_Value_Binary($compress->compress(serialize($val))),
 145                              strval($key),
 146                              $uid
 147                          )
 148                      );
 149                  } catch (Horde_Db_Exception $e) {}
 150              }
 151          }
 152  
 153          /* Update modified time. */
 154          try {
 155              $this->_db->update(
 156                  sprintf(
 157                      'UPDATE %s SET modified = ? WHERE messageid = ?',
 158                      self::BASE_TABLE
 159                  ),
 160                  array(time(), $uid)
 161              );
 162          } catch (Horde_Db_Exception $e) {}
 163  
 164          /* Update uidvalidity. */
 165          $this->setMetaData($mailbox, array('uidvalid' => $uidvalid));
 166      }
 167  
 168      /**
 169       */
 170      public function getMetaData($mailbox, $uidvalid, $entries)
 171      {
 172          $query = $this->_baseSql($mailbox, self::MD_TABLE);
 173          $query[0] = 'SELECT t.field, t.data ' . $query[0];
 174  
 175          if (!empty($entries)) {
 176              $entries[] = 'uidvalid';
 177              $entry_query = array();
 178  
 179              foreach (array_unique($entries) as $val) {
 180                  $entry_query[] = 't.field = ?';
 181                  $query[1][] = $val;
 182              }
 183              $query[0] .= ' AND (' . implode(' OR ', $entry_query) . ')';
 184          }
 185  
 186          try {
 187              if ($res = $this->_db->selectAssoc($query[0], $query[1])) {
 188                  $columns = $this->_db->columns(self::MD_TABLE);
 189                  foreach ($res as $key => $val) {
 190                      switch ($key) {
 191                      case 'uidvalid':
 192                          $res[$key] = $columns['data']->binaryToString($val);
 193                          break;
 194  
 195                      default:
 196                          $res[$key] = @unserialize(
 197                              $columns['data']->binaryToString($val)
 198                          );
 199                          break;
 200                      }
 201                  }
 202  
 203                  if (is_null($uidvalid) ||
 204                      !isset($res['uidvalid']) ||
 205                      ($res['uidvalid'] == $uidvalid)) {
 206                      return $res;
 207                  }
 208  
 209                  $this->deleteMailbox($mailbox);
 210              }
 211          } catch (Horde_Db_Exception $e) {}
 212  
 213          return array();
 214      }
 215  
 216      /**
 217       */
 218      public function setMetaData($mailbox, $data)
 219      {
 220          if (!($uid = $this->_getUid($mailbox))) {
 221              $uid = $this->_createUid($mailbox);
 222          }
 223  
 224          $query = sprintf('SELECT field FROM %s where messageid = ?', self::MD_TABLE);
 225          $values = array($uid);
 226  
 227          try {
 228              $fields = $this->_db->selectValues($query, $values);
 229          } catch (Horde_Db_Exception $e) {
 230              return;
 231          }
 232  
 233          foreach ($data as $key => $val) {
 234              $val = new Horde_Db_Value_Binary(($key == 'uidvalid') ? $val : serialize($val));
 235  
 236              if (in_array($key, $fields)) {
 237                  /* Update */
 238                  try {
 239                      $this->_db->update(
 240                          sprintf(
 241                              'UPDATE %s SET data = ? WHERE field = ? AND messageid = ?',
 242                              self::MD_TABLE
 243                          ),
 244                          array($val, $key, $uid)
 245                      );
 246                  } catch (Horde_Db_Exception $e) {}
 247              } else {
 248                  /* Insert */
 249                  try {
 250                      $this->_db->insert(
 251                          sprintf(
 252                              'INSERT INTO %s (data, field, messageid) VALUES (?, ?, ?)',
 253                              self::MD_TABLE
 254                          ),
 255                          array($val, $key, $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          try {
 332              $this->_db->delete(
 333                  sprintf('DELETE FROM %s WHERE modified < ?', self::BASE_TABLE),
 334                  array($purge)
 335              );
 336          } catch (Horde_Db_Exception $e) {}
 337      }
 338  
 339      /**
 340       * Prepare the base SQL query.
 341       *
 342       * @param string $mailbox  The mailbox.
 343       * @param string $join     The table to join with the base table.
 344       *
 345       * @return array  SQL query and bound parameters.
 346       */
 347      protected function _baseSql($mailbox, $join = null)
 348      {
 349          $sql = sprintf('FROM %s d', self::BASE_TABLE);
 350  
 351          if (!is_null($join)) {
 352              $sql .= sprintf(' INNER JOIN %s t ON d.messageid = t.messageid', $join);
 353          }
 354  
 355          return array(
 356              $sql . ' WHERE d.hostspec = ? AND d.port = ? AND d.username = ? AND d.mailbox = ?',
 357              array(
 358                  $this->_params['hostspec'],
 359                  $this->_params['port'],
 360                  $this->_params['username'],
 361                  $mailbox
 362              )
 363          );
 364      }
 365  
 366      /**
 367       * @param string $mailbox
 368       *
 369       * @return string  UID from base table.
 370       */
 371      protected function _getUid($mailbox)
 372      {
 373          $query = $this->_baseSql($mailbox);
 374          $query[0] = 'SELECT d.messageid ' . $query[0];
 375  
 376          try {
 377              return $this->_db->selectValue($query[0], $query[1]);
 378          } catch (Horde_Db_Exception $e) {
 379              return null;
 380          }
 381      }
 382  
 383      /**
 384       * @param string $mailbox
 385       *
 386       * @return string  UID from base table.
 387       */
 388      protected function _createUid($mailbox)
 389      {
 390          return $this->_db->insert(
 391              sprintf(
 392                  'INSERT INTO %s (hostspec, mailbox, port, username) ' .
 393                      'VALUES (?, ?, ?, ?)',
 394                  self::BASE_TABLE
 395              ),
 396              array(
 397                  $this->_params['hostspec'],
 398                  $mailbox,
 399                  $this->_params['port'],
 400                  $this->_params['username']
 401              )
 402          );
 403      }
 404  
 405  }