1: <?php
   2: /**
   3:  * Horde_History based state management. Needs a number of SQL tables present:
   4:  * <pre>
   5:  *    syncStateTable (horde_activesync_state):
   6:  *        sync_time:  timestamp of last sync
   7:  *        sync_key:   the syncKey for the last sync
   8:  *        sync_data:  If the last sync resulted in a MOREAVAILABLE, this contains
   9:  *                    a list of UIDs that still need to be sent to the PIM.  If
  10:  *                    this sync_key represents a FOLDERSYNC state, then this
  11:  *                    contains the current folder state on the PIM.
  12:  *        sync_devid: The device id.
  13:  *        sync_folderid: The folder id for this sync.
  14:  *        sync_user:     The user for this synckey
  15:  *
  16:  *    syncMapTable (horde_activesync_map):
  17:  *        message_uid    - The server uid for the object
  18:  *        sync_modtime   - The time the change was received from the PIM and
  19:  *                         applied to the server data store.
  20:  *        sync_key       - The syncKey that was current at the time the change
  21:  *                         was received.
  22:  *        sync_devid     - The device id this change was done on.
  23:  *        sync_user      - The user that initiated the change.
  24:  *
  25:  *    syncDeviceTable (horde_activesync_device):
  26:  *        device_id      - The unique id for this device
  27:  *        device_type    - The device type the PIM identifies itself with
  28:  *        device_agent   - The user agent string sent by the device
  29:  *        device_policykey  - The current policykey for this device
  30:  *        device_rwstatus   - The current remote wipe status for this device
  31:  *
  32:  *    syncUsersTable (horde_activesync_device_users):
  33:  *        device_user    - A username attached to the device
  34:  *        device_id      - The device id
  35:  *        device_ping    - The account's ping state
  36:  *        device_folders - Account's folder data
  37:  * </pre>
  38:  *
  39:  * Copyright 2010-2012 Horde LLC (http://www.horde.org/)
  40:  *
  41:  * @TODO: H5 This driver should be renamed to Horde_ActiveSync_State_Sql since the
  42:  *        History related changes have been refactored out to a Core library.
  43:  *
  44:  * @author Michael J. Rubinsky <mrubinsk@horde.org>
  45:  * @package ActiveSync
  46:  */
  47: class Horde_ActiveSync_State_History extends Horde_ActiveSync_State_Base
  48: {
  49:     /**
  50:      * The timestamp for the last syncKey
  51:      *
  52:      * @var timestamp
  53:      */
  54:     protected $_lastSyncTS = 0;
  55: 
  56:     /**
  57:      * The current sync timestamp
  58:      *
  59:      * @var timestamp
  60:      */
  61:     protected $_thisSyncTS = 0;
  62: 
  63:     /**
  64:      * Local cache of state.
  65:      *
  66:      * @var array
  67:      */
  68:     protected $_state;
  69: 
  70:     /**
  71:      * DB handle
  72:      *
  73:      * @var Horde_Db_Adapter
  74:      */
  75:     protected $_db;
  76: 
  77:     /* Table names */
  78:     protected $_syncStateTable;
  79:     protected $_syncMapTable;
  80:     protected $_syncDeviceTable;
  81:     protected $_syncUsersTable;
  82: 
  83:     /**
  84:      * Const'r
  85:      *
  86:      * @param array  $params   Must contain:
  87:      *      'db'  - Horde_Db
  88:      *      'syncStateTable'    - Name of table for storing syncstate
  89:      *      'syncDeviceTable'   - Name of table for storing device and ping data
  90:      *      'syncMapTable'      - Name of table for remembering what changes
  91:      *                            are due to PIM import so we don't mirror the
  92:      *                            changes back to the PIM on next Sync
  93:      *      'syncUsersTable'    - Name of table for mapping users to devices.
  94:      *
  95:      * @return Horde_ActiveSync_StateMachine_File
  96:      */
  97:     public function __construct($params = array())
  98:     {
  99:         parent::__construct($params);
 100:         if (empty($this->_params['db']) || !($this->_params['db'] instanceof Horde_Db_Adapter)) {
 101:             throw new InvalidArgumentException('Missing or invalid Horde_Db parameter.');
 102:         }
 103: 
 104:         $this->_syncStateTable = $params['statetable'];
 105:         $this->_syncMapTable = $params['maptable'];
 106:         $this->_syncDeviceTable = $params['devicetable'];
 107:         $this->_syncUsersTable = $params['userstable'];
 108:         $this->_db = $params['db'];
 109:     }
 110: 
 111:     /**
 112:      * Load the sync state
 113:      *
 114:      * @param string $syncKey   The synckey of the state to load. If empty will
 115:      *                          force a reset of the state for the class
 116:      *                          specified in $id
 117:      * @prarm string $type      The type of state (sync, foldersync).
 118:      * @param string $id        The folder id this state represents. If empty
 119:      *                          assumed to be a foldersync state.
 120:      *
 121:      * @return void
 122:      * @throws Horde_ActiveSync_Exception
 123:      */
 124:     public function loadState($syncKey, $type = null, $id = null)
 125:     {
 126:         $this->_type = $type;
 127:         if ($type == 'foldersync' && empty($id)) {
 128:             $id = 'foldersync';
 129:         }
 130:         if (empty($syncKey)) {
 131:             $this->_state = array();
 132:             $this->_resetDeviceState($id);
 133:             return;
 134:         }
 135:         $this->_logger->debug(
 136:             sprintf('[%s] Loading state for synckey %s',
 137:                 $this->_devId,
 138:                 $syncKey));
 139: 
 140:         // Check if synckey is allowed
 141:         if (!preg_match('/^s{0,1}\{([0-9A-Za-z-]+)\}([0-9]+)$/', $syncKey, $matches)) {
 142:             throw new Horde_ActiveSync_Exception('Invalid sync key');
 143:         }
 144:         $this->_syncKey = $syncKey;
 145: 
 146:         // Cleanup all older syncstates
 147:         $this->_gc($syncKey);
 148: 
 149:         // Load the previous syncState from storage
 150:         try {
 151:             $results = $this->_db->selectOne('SELECT sync_data, sync_devid, sync_time FROM '
 152:                 . $this->_syncStateTable . ' WHERE sync_key = ?', array($this->_syncKey));
 153:         } catch (Horde_Db_Exception $e) {
 154:             throw new Horde_ActiveSync_Exception($e);
 155:         }
 156:         if (!$results) {
 157:             throw new Horde_ActiveSync_Exception('Sync State Not Found.');
 158:         }
 159: 
 160:         // Load the last known sync time for this collection
 161:         $this->_lastSyncTS = !empty($results['sync_time']) ? $results['sync_time'] : 0;
 162: 
 163:         // Pre-Populate the current sync timestamp in case this is only a
 164:         // Client -> Server sync.
 165:         $this->_thisSyncTS = $this->_lastSyncTS;
 166: 
 167:         // Restore any state or pending changes
 168:         $data = unserialize($results['sync_data']);
 169:         if ($type == 'foldersync') {
 170:             $this->_state = ($data !== false) ? $data : array();
 171:             $this->_logger->debug(
 172:                 sprintf('[%s] Loading FOLDERSYNC state: %s',
 173:                 $this->_devId,
 174:                 print_r($this->_state, true)));
 175:         } elseif ($type == 'sync') {
 176:             $this->_changes = ($data !== false) ? $data : null;
 177:             if ($this->_changes) {
 178:                 $this->_logger->debug(
 179:                     sprintf('[%s] Found %d changes remaining from previous SYNC.',
 180:                     $this->_devId,
 181:                     count($this->_changes)));
 182:             }
 183:         }
 184:     }
 185: 
 186:     /**
 187:      * Determines if the server version of the message represented by $stat
 188:      * conflicts with the PIM version of the message.  For this driver, this is
 189:      * true whenever $lastSyncTime is older then $stat['mod']. Method is only
 190:      * called from the Importer during an import of a non-new change from the
 191:      * PIM.
 192:      *
 193:      * @see Horde_ActiveSync_State_Base::isConflict()
 194:      */
 195:     public function isConflict($stat, $type)
 196:     {
 197:         // $stat == server's message information
 198:          if ($stat['mod'] > $this->_lastSyncTS) {
 199:              if ($type == 'delete' || $type == 'change') {
 200:                  // changed here - deleted there
 201:                  // changed here - changed there
 202:                  return true;
 203:              } else {
 204:                  // all other remote cahnges are fine (move/flags)
 205:                  return false;
 206:              }
 207:         }
 208:     }
 209: 
 210:     /**
 211:      * Save the current state to storage
 212:      *
 213:      * @throws Horde_ActiveSync_Exception
 214:      */
 215:     public function save()
 216:     {
 217:         // Update state table to remember this last synctime and key
 218:         $sql = 'INSERT INTO ' . $this->_syncStateTable
 219:             . ' (sync_key, sync_data, sync_devid, sync_time, sync_folderid, sync_user)'
 220:             . ' VALUES (?, ?, ?, ?, ?, ?)';
 221: 
 222:         // Remember any left over changes
 223:         if ($this->_type == 'foldersync') {
 224:             $data = (isset($this->_state) ? serialize($this->_state) : '');
 225:         } elseif ($this->_type == 'sync') {
 226:             $data = (isset($this->_changes) ? serialize(array_values($this->_changes)) : '');
 227:         } else {
 228:             $data = '';
 229:         }
 230: 
 231:         $params = array(
 232:             $this->_syncKey,
 233:             $data,
 234:             $this->_devId,
 235:             $this->_thisSyncTS,
 236:             !empty($this->_collection['id']) ? $this->_collection['id'] : 'foldersync',
 237:             $this->_deviceInfo->user);
 238:         $this->_logger->debug(
 239:             sprintf('[%s] Saving state: %s', $this->_devId, print_r($params, true)));
 240:         try {
 241:             $this->_db->insert($sql, $params);
 242:         } catch (Horde_Db_Exception $e) {
 243:             // Might exist already if the last sync attempt failed.
 244:             $this->_logger->notice(
 245:                 sprintf('[%s] Error saving state for synckey %s: %s - removing previous sync state and trying again.',
 246:                         $this->_devId,
 247:                         $this->_syncKey,
 248:                         $e->getMessage()));
 249:             $this->_db->delete('DELETE FROM ' . $this->_syncStateTable . ' WHERE sync_key = ?', array($this->_syncKey));
 250:             $this->_db->insert($sql, $params);
 251:         }
 252:     }
 253: 
 254:     /**
 255:      * Update the state to reflect changes
 256:      *
 257:      * Notes: If we are importing PIM changes, need to update the syncMapTable
 258:      * so we don't mirror back the changes on next sync. If we are exporting
 259:      * server changes, we need to track which changes have been sent (by
 260:      * removing them from $this->_changes) so we know which items to send on the
 261:      * next sync if a MOREAVAILBLE response was needed.  If this is being called
 262:      * from a FOLDERSYNC command, update state accordingly. Yet another reason
 263:      * to break out state handling into different classes based on the command
 264:      * being run (Horde_ActiveSync_State_Sync, *_FolderSync, *_Ping etc...);
 265:      *
 266:      * @param string $type      The type of change (change, delete, flags or
 267:      *                          foldersync)
 268:      * @param array $change     A stat/change hash describing the change
 269:      * @param integer $origin   Flag to indicate the origin of the change.
 270:      * @param string $user      The current sync user, only needed if change
 271:      *                          origin is CHANGE_ORIGIN_PIM
 272:      * @param string $clientid  PIM clientid sent when adding a new message
 273:      *
 274:      * @return void
 275:      */
 276:     public function updateState($type, array $change,
 277:                                 $origin = Horde_ActiveSync::CHANGE_ORIGIN_NA,
 278:                                 $user = null, $clientid = '')
 279:     {
 280:         $this->_logger->debug('Updating state during ' . $type);
 281:         if ($origin == Horde_ActiveSync::CHANGE_ORIGIN_PIM) {
 282:             $sql = 'INSERT INTO ' . $this->_syncMapTable
 283:                 . ' (message_uid, sync_modtime, sync_key, sync_devid, sync_folderid, sync_user, sync_clientid) '
 284:                 . 'VALUES (?, ?, ?, ?, ?, ?, ?)';
 285:             try {
 286:                $this->_db->insert(
 287:                    $sql,
 288:                    array(
 289:                        $change['id'],
 290:                        $change['mod'],
 291:                        $this->_syncKey,
 292:                        $this->_devId,
 293:                        $change['parent'],
 294:                        $user,
 295:                        $clientid)
 296:                 );
 297:             } catch (Horde_Db_Exception $e) {
 298:                 $this->_logger->err($e->getMessage());
 299:                 throw new Horde_ActiveSync_Exception($e);
 300:             }
 301:             // @TODO: Deal with PIM generated folder changes (mail only)
 302:         } else {
 303:            // When sending server changes, $this->_changes will contain all
 304:            // changes. Need to track which ones are sent since we might not
 305:            // send all of them.
 306:             foreach ($this->_changes as $key => $value) {
 307:                if ($value['id'] == $change['id']) {
 308:                    if ($type == 'foldersync') {
 309:                        foreach ($this->_state as $fi => $state) {
 310:                            if ($state['id'] == $value['id']) {
 311:                                unset($this->_state[$fi]);
 312:                            }
 313:                        }
 314:                        // Only save what we need. Note that 'mod' is eq to the
 315:                        // folder id, since that is the only thing that can
 316:                        // change in a folder.
 317:                        $stat = array(
 318:                            'id' => $value['id'],
 319:                            'mod' => (empty($value['mod']) ? $value['id'] : $value['mod']),
 320:                            'parent' => (empty($value['parent']) ? 0 : $value['parent'])
 321:                        );
 322:                        $this->_state[] = $stat;
 323:                        $this->_state = array_values($this->_state);
 324:                    }
 325:                    unset($this->_changes[$key]);
 326:                    break;
 327:                }
 328:            }
 329:        }
 330:     }
 331: 
 332:     /**
 333:      * Save folder data for a specific device. This is needed for BC with older
 334:      * activesync versions that use GETHIERARCHY requests to get the folder info
 335:      * instead of maintaining the folder state with FOLDERSYNC requests.
 336:      *
 337:      * @param object $device  The device object
 338:      * @param array $folders  The folder data
 339:      *
 340:      * @return boolean
 341:      * @throws Horde_ActiveSync_Exception
 342:      */
 343:     public function setFolderData($device, $folders)
 344:     {
 345:         if (!is_array($folders) || empty ($folders)) {
 346:             return false;
 347:         }
 348: 
 349:         $unique_folders = array ();
 350:         foreach ($folders as $folder) {
 351:             /* don't save folder-ids for emails */
 352:             if ($folder->type == Horde_ActiveSync::FOLDER_TYPE_INBOX) {
 353:                 continue;
 354:             }
 355: 
 356:             /* no folder from that type or the default folder */
 357:             if (!array_key_exists($folder->type, $unique_folders) || $folder->parentid == 0) {
 358:                 $unique_folders[$folder->type] = $folder->serverid;
 359:             }
 360:         }
 361: 
 362:         // Treo does initial sync for calendar and contacts too, so we need to fake
 363:         // these folders if they are not supported by the backend
 364:         if (!array_key_exists(Horde_ActiveSync::FOLDER_TYPE_APPOINTMENT, $unique_folders)) {
 365:             $unique_folders[Horde_ActiveSync::FOLDER_TYPE_APPOINTMENT] = Horde_ActiveSync::FOLDER_TYPE_DUMMY;
 366:         }
 367:         if (!array_key_exists(Horde_ActiveSync::FOLDER_TYPE_CONTACT, $unique_folders)) {
 368:             $unique_folders[Horde_ActiveSync::FOLDER_TYPE_CONTACT] = Horde_ActiveSync::FOLDER_TYPE_DUMMY;
 369:         }
 370: 
 371:         /* Store it*/
 372:         $sql = 'UPDATE ' . $this->_syncUsersTable . ' SET device_folders = ? WHERE device_id = ? AND device_user = ?';
 373:         try {
 374:             return $this->_db->update($sql, array(serialize($folders), $device->id, $device->user));
 375:         } catch (Horde_Db_Exception $e) {
 376:             throw new Horde_ActiveSync_Exception($e);
 377:         }
 378:     }
 379: 
 380:     /**
 381:      * Get the folder data for a specific device
 382:      *
 383:      * @param object $device  The device object
 384:      * @param string $class   The folder class to fetch (Calendar, Contacts etc.)
 385:      *
 386:      * @return mixed  Either an array of folder data || false
 387:      */
 388:     public function getFolderData($device, $class)
 389:     {
 390:         $sql = 'SELECT device_folders FROM ' . $this->_syncUsersTable . ' WHERE device_id = ? AND device_user = ?';
 391:         try {
 392:             $folders = $this->_db->selectValue($sql, array($device->id, $device->user));
 393:         } catch (Horde_Db_Exception $e) {
 394:             throw new Horde_ActiveSync_Exception($e);
 395:         }
 396:         if ($folders) {
 397:             $folders = unserialize($folders);
 398:             if ($class == "Calendar") {
 399:                 return $folders[Horde_ActiveSync::FOLDER_TYPE_APPOINTMENT];
 400:             }
 401:             if ($class == "Contacts") {
 402:                 return $folders[Horde_ActiveSync::FOLDER_TYPE_CONTACT];
 403:             }
 404:         }
 405: 
 406:         return false;
 407:     }
 408: 
 409:     /**
 410:      * Return an array of known folders. This is essentially the state for a
 411:      * FOLDERSYNC request. AS uses a seperate synckey for FOLDERSYNC requests
 412:      * also, so need to treat it as any other collection.
 413:      *
 414:      * @return array
 415:      */
 416:     public function getKnownFolders()
 417:     {
 418:         if (!isset($this->_state)) {
 419:             throw new Horde_ActiveSync_Exception('Sync state not loaded');
 420:         }
 421:         $folders = array();
 422:         foreach ($this->_state as $folder) {
 423:             $folders[] = $folder['id'];
 424:         }
 425: 
 426:         return $folders;
 427:     }
 428: 
 429:     /**
 430:      * Perform any initialization needed to deal with pingStates for this driver
 431:      *
 432:      * @param string $devId  The device id to load pingState for
 433:      *
 434:      * @return The $collection array
 435:      */
 436:     public function initPingState($device)
 437:     {
 438:         // This would normally already be loaded by loadDeviceInfo() but we
 439:         // should verify we have the correct device loaded etc...
 440:         if (!isset($this->_pingState) || $this->_devId !== $device->id) {
 441:             throw new Horde_ActiveSync_Exception('Device not loaded');
 442:         }
 443: 
 444:         return $this->_pingState['collections'];
 445:     }
 446: 
 447:     /**
 448:      * Obtain the device object. For this driver, we also store the PING data
 449:      * in the device table.
 450:      *
 451:      * @param string $devId   The device id to obtain
 452:      * @param string $user    The user to retrieve user-specific device info for
 453:      *
 454:      * @return StdClass The device obejct
 455:      * @throws Horde_ActiveSync_Exception
 456:      */
 457:     public function loadDeviceInfo($devId, $user)
 458:     {
 459:         $this->_logger->debug('[' . $devId . '] loadDeviceInfo: ' . $user);
 460: 
 461:         // See if we already have this device, for this user loaded
 462:         if ($this->_devId == $devId && !empty($this->_deviceInfo) &&
 463:             $user == $this->_deviceInfo->user) {
 464:             return $this->_deviceInfo;
 465:         }
 466: 
 467:         $this->_devId = $devId;
 468:         $query = 'SELECT device_type, device_agent, '
 469:             . 'device_rwstatus, device_supported FROM '
 470:             . $this->_syncDeviceTable . ' WHERE device_id = ?';
 471: 
 472:         try {
 473:             $device = $this->_db->selectOne($query, array($devId));
 474:         } catch (Horde_Db_Exception $e) {
 475:             throw new Horde_ActiveSync_Exception($e);
 476:         }
 477: 
 478:         if (!empty($user)) {
 479:             $query = 'SELECT device_ping, device_policykey FROM ' . $this->_syncUsersTable
 480:                 . ' WHERE device_id = ? AND device_user = ?';
 481:             try {
 482:                 $duser = $this->_db->selectOne($query, array($devId, $user));
 483:             } catch (Horde_Db_Exception $e) {
 484:                 throw new Horde_ActiveSync_Exception($e);
 485:             }
 486:         } else {
 487:             $this->resetPingState();
 488:         }
 489: 
 490:         $this->_deviceInfo = new StdClass();
 491:         if ($device) {
 492:             $this->_deviceInfo->rwstatus = $device['device_rwstatus'];
 493:             $this->_deviceInfo->deviceType = $device['device_type'];
 494:             $this->_deviceInfo->userAgent = $device['device_agent'];
 495:             $this->_deviceInfo->id = $devId;
 496:             $this->_deviceInfo->user = $user;
 497:             $this->_deviceInfo->supported = unserialize($device['device_supported']);
 498:             if (empty($duser)) {
 499:                 $this->resetPingState();
 500:                 $this->_deviceInfo->policykey = 0;
 501:             } else {
 502:                 if (empty($duser['device_ping'])) {
 503:                     $this->resetPingState();
 504:                 } else {
 505:                     $this->_pingState = unserialize($duser['device_ping']);
 506:                 }
 507:                 $this->_deviceInfo->policykey =
 508:                     (empty($duser['device_policykey']) ?
 509:                         0 :
 510:                         $duser['device_policykey']);
 511:             }
 512:         } else {
 513:             throw new Horde_ActiveSync_Exception('Device not found.');
 514:         }
 515: 
 516:         return $this->_deviceInfo;
 517:     }
 518: 
 519:     /**
 520:      * Set new device info
 521:      *
 522:      * @param object $data  The device information
 523:      *
 524:      * @return boolean
 525:      */
 526:     public function setDeviceInfo($data)
 527:     {
 528:         /* Make sure we have the device entry */
 529:         try {
 530:             if (!$this->deviceExists($data->id)) {
 531:                 $this->_logger->debug('[' . $data->id . '] Device entry does not exist, creating it.');
 532:                 $query = 'INSERT INTO ' . $this->_syncDeviceTable
 533:                     . ' (device_type, device_agent, device_rwstatus, device_id, device_supported)'
 534:                     . ' VALUES(?, ?, ?, ?, ?)';
 535:                 $values = array(
 536:                     $data->deviceType,
 537:                     $data->userAgent,
 538:                     $data->rwstatus,
 539:                     $data->id,
 540:                     (!empty($data->supported) ? serialize($data->supported) : '')
 541:                 );
 542:                 $this->_db->execute($query, $values);
 543:             }
 544:         } catch(Horde_Db_Exception $e) {
 545:             throw new Horde_ActiveSync_Exception($e);
 546:         }
 547: 
 548:         $this->_deviceInfo = $data;
 549: 
 550:         /* See if we have the user already also */
 551:         try {
 552:             $query = 'SELECT COUNT(*) FROM ' . $this->_syncUsersTable . ' WHERE device_id = ? AND device_user = ?';
 553:             $cnt = $this->_db->selectValue($query, array($data->id, $data->user));
 554:             if (!$cnt) {
 555:                 $this->_logger->debug('[' . $data->id . '] Device entry does not exist for user ' . $data->user . ', creating it.');
 556:                 $query = 'INSERT INTO ' . $this->_syncUsersTable
 557:                     . ' (device_ping, device_id, device_user, device_policykey)'
 558:                     . ' VALUES(?, ?, ?, ?)';
 559: 
 560:                 $values = array(
 561:                     '',
 562:                     $data->id,
 563:                     $data->user,
 564:                     $data->policykey
 565:                 );
 566:                 $this->_devId = $data->id;
 567:                 return $this->_db->insert($query, $values);
 568:             } else {
 569:                 return true;
 570:             }
 571:         } catch (Horde_Db_Exception $e) {
 572:             throw new Horde_ActiveSync_Exception($e);
 573:         }
 574:     }
 575: 
 576:     /**
 577:      * Check that a given device id is known to the server. This is regardless
 578:      * of Provisioning status. If $user is provided, checks that the device
 579:      * is attached to the provided username.
 580:      *
 581:      * @param string $devId  The device id to check.
 582:      * @param string $user   The device should be owned by this user.
 583:      *
 584:      * @return boolean
 585:      */
 586:     public function deviceExists($devId, $user = null)
 587:     {
 588:         if (!empty($user)) {
 589:             $query = 'SELECT COUNT(*) FROM ' . $this->_syncUsersTable
 590:                 . ' WHERE device_id = ? AND device_user = ?';
 591:             $values = array($devId, $user);
 592:         } else {
 593:             $query = 'SELECT COUNT(*) FROM ' . $this->_syncDeviceTable . ' WHERE device_id = ?';
 594:             $values = array($devId);
 595:         }
 596: 
 597:         try {
 598:             return $this->_db->selectValue($query, $values);
 599:         } catch (Horde_Db_Exception $e) {
 600:             throw new Horde_ActiveSync_Exception($e);
 601:         }
 602:     }
 603: 
 604:     /**
 605:      * List all devices that we know about.
 606:      *
 607:      * @return array  An array of device hashes
 608:      * @throws Horde_ActiveSync_Exception
 609:      */
 610:     public function listDevices($user = null)
 611:     {
 612:         $query = 'SELECT d.device_id AS device_id, device_type, device_agent,'
 613:             . ' device_policykey, device_rwstatus, device_user FROM '
 614:             . $this->_syncDeviceTable . ' d  INNER JOIN ' . $this->_syncUsersTable
 615:             . ' u ON d.device_id = u.device_id';
 616:         $values = array();
 617:         if (!empty($user)) {
 618:             $query .= ' WHERE u.device_user = ?';
 619:             $values[] = $user;
 620:         }
 621: 
 622:         try {
 623:             return $this->_db->selectAll($query, $values);
 624:         } catch (Horde_Db_Exception $e) {
 625:             throw new Horde_ActiveSync_Exception($e);
 626:         }
 627:     }
 628: 
 629:     /**
 630:      * Get the last time the loaded device issued a SYNC request.
 631:      *
 632:      * @return integer  The timestamp of the last sync, regardless of collection
 633:      * @throws Horde_ActiveSync_Exception
 634:      */
 635:     public function getLastSyncTimestamp()
 636:     {
 637:         if (empty($this->_deviceInfo)) {
 638:             throw new Horde_ActiveSync_Exception('Device not loaded.');
 639:         }
 640: 
 641:         $sql = 'SELECT MAX(sync_time) FROM ' . $this->_syncStateTable . ' WHERE sync_devid = ? AND sync_user = ?';
 642:         try {
 643:             return $this->_db->selectValue($sql, array($this->_devId, $this->_deviceInfo->user));
 644:         } catch (Horde_Db_Exception $e) {
 645:             throw new Horde_ActiveSync_Exception($e);
 646:         }
 647:     }
 648: 
 649:     /**
 650:      * Add a collection to the PING state. Ping state must already be loaded.
 651:      *
 652:      * @param array $collections  An array of collection information to replace
 653:      *                            any existing cached ping collection state.
 654:      */
 655:     public function addPingCollections($collections)
 656:     {
 657:         if (empty($this->_pingState)) {
 658:             throw new Horde_ActiveSync_Exception('PING state not initialized');
 659:         }
 660:         $this->_pingState['collections'] = array();
 661:         foreach ($collections as $collection) {
 662:             $this->_pingState['collections'][$collection['class']] = $collection;
 663:         }
 664:     }
 665: 
 666:     /**
 667:      * Load a specific collection's ping state. Ping state must already have
 668:      * been loaded.
 669:      *
 670:      * @param array $pingCollection  The collection array from the PIM request
 671:      *
 672:      * @throws Horde_ActiveSync_Exception, Horde_ActiveSync_Exception_StateGone,
 673:      *         Horde_ActiveSync_Exception_InvalidRequest
 674:      */
 675:     public function loadPingCollectionState($pingCollection)
 676:     {
 677:         if (empty($this->_pingState)) {
 678:             throw new Horde_ActiveSync_Exception('PING state not initialized');
 679:         }
 680:         $haveState = false;
 681: 
 682:         // Load any existing state
 683:         // @TODO: I'm almost positive we need to key these by 'id', not 'class'
 684:         // but this is what z-push did so...
 685:         $this->_logger->debug('[' . $this->_devId . '] Attempting to load PING state for: ' . $pingCollection['class']);
 686: 
 687:         if (!empty($this->_pingState['collections'][$pingCollection['class']])) {
 688:             $this->_collection = $this->_pingState['collections'][$pingCollection['class']];
 689:             $this->_collection['synckey'] = $this->_devId;
 690:             if (!$this->_lastSyncTS = $this->_getLastSyncTS()) {
 691:                 throw new Horde_ActiveSync_Exception_StateGone('Previous syncstate has been removed.');
 692:             }
 693:             $this->_logger->debug('[' . $this->_devId . '] Obtained last sync time for ' . $pingCollection['class'] . ' - ' . $this->_lastSyncTS);
 694:         } else {
 695:             // Initialize the collection's state.
 696:             $this->_logger->info('[' . $this->_devId . '] Empty state for '. $pingCollection['class']);
 697: 
 698:             // Init members for the getChanges call
 699:             $this->_collection = $pingCollection;
 700:             $this->_collection['synckey'] = $this->_devId;
 701: 
 702:             // If we are here, then the pingstate was empty so prime it..
 703:             $this->_pingState['collections'][$this->_collection['class']] = $this->_collection;
 704:             $this->savePingState();
 705: 
 706:             // We MUST have a previous successful SYNC before PING.
 707:             if (!$this->_lastSyncTS = $this->_getLastSyncTS()) {
 708:                 throw new Horde_ActiveSync_Exception_InvalidRequest('No previous SYNC found for collection ' . $pingCollection['class']);
 709:             }
 710:         }
 711:     }
 712: 
 713:     /**
 714:      * Save the current ping state to storage
 715:      *
 716:      * @param string $devId      The PIM device id
 717:      * @param integer $lifetime  The ping heartbeat/lifetime interval
 718:      *
 719:      * @return boolean
 720:      * @throws Horde_ActiveSync_Exception
 721:      */
 722:     public function savePingState()
 723:     {
 724:         if (empty($this->_pingState)) {
 725:             throw new Horde_ActiveSync_Exception('PING state not initialized');
 726:         }
 727:         /* Update the ping's collection */
 728:         if (!empty($this->_collection)) {
 729:             $this->_pingState['collections'][$this->_collection['class']] = $this->_collection;
 730:         }
 731: 
 732:         $state = serialize(array('lifetime' => $this->_pingState['lifetime'], 'collections' => $this->_pingState['collections']));
 733:         $query = 'UPDATE ' . $this->_syncUsersTable . ' SET device_ping = ? WHERE device_id = ? AND device_user = ?';
 734:         $this->_logger->debug(sprintf('Saving PING state: %s', $state));
 735:         try {
 736:             return $this->_db->update($query, array($state, $this->_devId, $this->_deviceInfo->user));
 737:         } catch (Horde_Db_Exception $e) {
 738:             throw new Horde_ActiveSync_Exception($e);
 739:         }
 740:     }
 741: 
 742:     /**
 743:      * Return the heartbeat interval, or zero if we have no existing state
 744:      *
 745:      * @return integer  The hearbeat interval, or zero if not found.
 746:      * @throws Horde_ActiveSync_Exception
 747:      */
 748:     public function getHeartbeatInterval()
 749:     {
 750:         if (empty($this->_pingState)) {
 751:             throw new Horde_ActiveSync_Exception('PING state not initialized');
 752:         }
 753: 
 754:         return (!$this->_pingState) ? 0 : $this->_pingState['lifetime'];
 755:     }
 756: 
 757:     /**
 758:      * Set the device's heartbeat interval
 759:      *
 760:      * @param integer $lifetime
 761:      */
 762:     public function setHeartbeatInterval($lifetime)
 763:     {
 764:         $this->_pingState['lifetime'] = $lifetime;
 765:     }
 766: 
 767:     /**
 768:      * Get all items that have changed since the last sync time
 769:      *
 770:      * @param integer $flags
 771:      *
 772:      * @return array
 773:      */
 774:     public function getChanges($flags = 0)
 775:     {
 776:         // How far back to sync (for those collections that use this)
 777:         $cutoffdate = self::_getCutOffDate(!empty($this->_collection['filtertype'])
 778:                 ? $this->_collection['filtertype']
 779:                 : 0);
 780: 
 781:         // Get the timestamp for THIS request
 782:         $this->_thisSyncTS = time();
 783: 
 784:         if (!empty($this->_collection['id'])) {
 785:             $folderId = $this->_collection['id'];
 786:             $this->_logger->debug('[' . $this->_devId . '] Initializing message diff engine for ' . $this->_collection['id']);
 787:             // Do nothing if it is a dummy folder
 788:             if ($folderId != Horde_ActiveSync::FOLDER_TYPE_DUMMY) {
 789:                 // First, need to see if we have exising changes left over from
 790:                 // a previous sync that resulted in a MORE_AVAILABLE
 791:                 if (!empty($this->_changes)) {
 792:                     $this->_logger->debug('[' . $this->_devId . '] Returning previously found changes.');
 793:                     return $this->_changes;
 794:                 }
 795: 
 796:                 /* No existing changes, poll the backend */
 797:                 $changes = $this->_backend->getServerChanges($folderId, (int)$this->_lastSyncTS, (int)$this->_thisSyncTS, $cutoffdate);
 798:             }
 799:             // Unfortunately we can't use an empty synckey to detect an initial
 800:             // sync. The AS protocol doesn't start looking for changes until
 801:             // after the device/server negotiate a synckey. What we CAN do is
 802:             // at least query the map table to see if there are any entries at
 803:             // all for this device before going through and stating all the
 804:             // messages.
 805:             $this->_logger->debug('[' . $this->_devId . '] Found ' . count($changes) . ' message changes, checking for PIM initiated changes.');
 806:             if ($this->_havePIMChanges()) {
 807:                 $this->_changes = array();
 808:                 foreach ($changes as $change) {
 809:                     $stat = $this->_backend->statMessage($folderId, $change['id']);
 810:                     $ts = $this->_getPIMChangeTS($change['id']);
 811:                     if ($ts && $ts >= $stat['mod']) {
 812:                         $this->_logger->debug('[' . $this->_devId . '] Ignoring PIM initiated change for ' . $change['id'] . '(PIM TS: ' . $ts . ' Stat TS: ' . $stat['mod']);
 813:                     } else {
 814:                         $this->_changes[] = $change;
 815:                     }
 816:                 }
 817:             } else {
 818:                 // No known PIM originated changes
 819:                 $this->_logger->debug('[' . $this->_devId . '] No PIM changes present, returning all messages.');
 820:                 $this->_changes = $changes;
 821:             }
 822:         } else {
 823:             $this->_logger->debug('[' . $this->_devId . '] Initializing folder diff engine');
 824:             $folderlist = $this->_backend->getFolderList();
 825:             if ($folderlist === false) {
 826:                 return false;
 827:             }
 828: 
 829:             $this->_changes = $this->_getDiff((empty($this->_state) ? array() : $this->_state), $folderlist);
 830:             $this->_logger->debug('[' . $this->_devId . '] Found ' . count($this->_changes) . ' folder changes');
 831:         }
 832: 
 833:         return $this->_changes;
 834:     }
 835: 
 836:     /**
 837:      * Save a new device policy key to storage.
 838:      *
 839:      * @param string $devId  The device id
 840:      * @param integer $key   The new policy key
 841:      */
 842:     public function setPolicyKey($devId, $key)
 843:     {
 844:         if (empty($this->_deviceInfo) || $devId != $this->_deviceInfo->id) {
 845:             $this->_logger->err('Device not loaded');
 846:             throw new Horde_ActiveSync_Exception('Device not loaded');
 847:         }
 848: 
 849:         $query = 'UPDATE ' . $this->_syncUsersTable . ' SET device_policykey = ? WHERE device_id = ? AND device_user = ?';
 850:         try {
 851:             $this->_db->update($query, array($key, $devId, $this->_backend->getUser()));
 852:         } catch (Horde_Db_Exception $e) {
 853:             throw new Horde_ActiveSync_Exception($e);
 854:         }
 855:     }
 856: 
 857:     /**
 858:      * Reset ALL device policy keys. Used when server policies have changed
 859:      * and you want to force ALL devices to pick up the changes. This will
 860:      * cause all devices that support provisioning to be reprovisioned.
 861:      *
 862:      * @throws Horde_ActiveSync_Exception
 863:      *
 864:      */
 865:     public function resetAllPolicyKeys()
 866:     {
 867:         $query = 'UPDATE ' . $this->_syncUsersTable . ' SET device_policykey = 0';
 868:         try {
 869:             $this->_db->update($query);
 870:         } catch (Horde_Db_Exception $e) {
 871:             throw new Horde_ActiveSync_Exception($e);
 872:         }
 873:     }
 874: 
 875:     /**
 876:      * Set a new remotewipe status for the device
 877:      *
 878:      * @param string $devid
 879:      * @param string $status
 880:      *
 881:      * @return boolean
 882:      * @throws Horde_ActiveSync_Exception
 883:      */
 884:     public function setDeviceRWStatus($devId, $status)
 885:     {
 886:         $query = 'UPDATE ' . $this->_syncDeviceTable . ' SET device_rwstatus = ?'
 887:             . ' WHERE device_id = ?';
 888:         $values = array($status, $devId);
 889:         try {
 890:             $this->_db->update($query, $values);
 891:         } catch (Horde_Db_Exception $e) {
 892:             throw new Horde_ActiveSync_Exception($e);
 893:         }
 894: 
 895:         if ($status == Horde_ActiveSync::RWSTATUS_PENDING) {
 896:             // Need to clear the policykey to force a PROVISION. Clear ALL
 897:             // entries, to ensure the device is wiped.
 898:             $query = 'UPDATE ' . $this->_syncUsersTable
 899:                 . ' SET device_policykey = 0 WHERE device_id = ?';
 900:             try {
 901:                 $this->_db->update($query, array($devId));
 902:             } catch (Horde_Db_Exception $e) {
 903:                 throw new Horde_ActiveSync_Exception($e);
 904:             }
 905:         }
 906:     }
 907: 
 908:     /**
 909:      * Explicitly remove a state from storage.
 910:      *
 911:      * @param string $synckey  The specific state to remove
 912:      * @param string $devId    Remove all information for this device.
 913:      * @param string $user     When removing device info, restrict to removing
 914:      *                         data for this user only.
 915:      *
 916:      * @throws Horde_ActiveSyncException
 917:      */
 918:     public function removeState($synckey = null, $devId = null, $user = null)
 919:     {
 920:         $state_query = 'DELETE FROM ' . $this->_syncStateTable . ' WHERE';
 921:         $map_query = 'DELETE FROM ' . $this->_syncMapTable . ' WHERE';
 922:         if ($devId && $user) {
 923:             $state_query .= ' sync_devid = ? AND sync_user = ?';
 924:             $map_query .= ' sync_devid = ? AND sync_user = ?';
 925:             $user_query = 'DELETE FROM ' . $this->_syncUsersTable . ' WHERE device_id = ? AND device_user = ?';
 926:             $values = array($devId, $user);
 927:             $this->_logger->debug('[' . $devId . '] Removing device state for user ' . $user . '.');
 928:         } elseif ($devId){
 929:             $state_query .= ' sync_devid = ?';
 930:             $map_query .= ' sync_devid = ?';
 931:             $user_query = 'DELETE FROM ' . $this->_syncUsersTable . ' WHERE device_id = ?';
 932:             $device_query = 'DELETE FROM ' . $this->_syncDeviceTable . ' WHERE device_id = ?';
 933:             $values = array($devId);
 934:             $this->_logger->debug('[' . $devId . '] Removing all device state for device ' . $devId . '.');
 935:         } else {
 936:             $state_query .= ' sync_key = ?';
 937:             $map_query .= ' sync_key = ?';
 938:             $values = array($synckey);
 939:             $this->_logger->debug('[' . $this->_devId . '] Removing device state for sync_key ' . $synckey . ' only.');
 940:         }
 941: 
 942:         try {
 943:             $this->_db->delete($state_query, $values);
 944:             $this->_db->delete($map_query, $values);
 945:             if (!empty($user_query)) {
 946:                 $this->_db->delete($user_query, $values);
 947:             }
 948:             if (!empty($device_query)) {
 949:                 $this->_db->delete($device_query, $values);
 950:             } elseif (!empty($user_query)) {
 951:                 /* If there was a user_deletion, check if we should remove the
 952:                  * device entry as well */
 953:                 $sql = 'SELECT COUNT(*) FROM ' . $this->_syncUsersTable . ' WHERE device_id = ?';
 954:                 if (!$this->_db->selectValue($sql, array($devId))) {
 955:                     $query = 'DELETE FROM ' . $this->_syncDeviceTable . ' WHERE device_id = ?';
 956:                     $this->_db->delete($query, array($devId));
 957:                 }
 958:             }
 959:         } catch (Horde_Db_Exception $e) {
 960:             throw new Horde_ActiveSync_Exception($e);
 961:         }
 962:     }
 963: 
 964:     /**
 965:      * Check and see that we didn't already see the incoming change from the PIM.
 966:      * This would happen e.g., if the PIM failed to receive the server response
 967:      * after successfully importing new messages.
 968:      *
 969:      * @param string $id  The client id sent during message addition.
 970:      *
 971:      * @return string The UID for the given clientid, null if none found.
 972:      * @throws Horde_ActiveSync_Exception
 973:      */
 974:      public function isDuplicatePIMAddition($id)
 975:      {
 976:         $sql = 'SELECT message_uid FROM ' . $this->_syncMapTable . ' WHERE sync_clientid = ? AND sync_user = ?';
 977:         try {
 978:             $uid = $this->_db->selectValue($sql, array($id, $this->_deviceInfo->user));
 979: 
 980:             return $uid;
 981:         } catch (Horde_Db_Exception $e) {
 982:             throw new Horde_ActiveSync_Exception($e);
 983:         }
 984:      }
 985: 
 986:     /**
 987:      * Get a timestamp from the map table for the last PIM-initiated change for
 988:      * the provided uid. Used to avoid mirroring back changes to the PIM that it
 989:      * sent to the server.
 990:      *
 991:      * @param string $uid
 992:      */
 993:     protected function _getPIMChangeTS($uid)
 994:     {
 995:         $sql = 'SELECT sync_modtime FROM ' . $this->_syncMapTable . ' WHERE message_uid = ? AND sync_devid = ? AND sync_user = ?';
 996:         try {
 997:             return $this->_db->selectValue($sql, array($uid, $this->_devId, $this->_deviceInfo->user));
 998:         } catch (Horde_Db_Exception $e) {
 999:             throw new Horde_ActiveSync_Exception($e);
1000:         }
1001:     }
1002: 
1003:     /**
1004:      * Check for the existence of ANY entries in the map table for this device
1005:      * and user.
1006:      *
1007:      * An extra database query for each sync, but the payoff is that we avoid
1008:      * having to stat every message change we send to the PIM if there are no
1009:      * PIM generated changes for this sync period.
1010:      *
1011:      * @return boolean
1012:      * @throws Horde_ActiveSync_Exception
1013:      */
1014:     protected function _havePIMChanges()
1015:     {
1016:         $sql = 'SELECT COUNT(*) FROM ' . $this->_syncMapTable . ' WHERE sync_devid = ? AND sync_user = ?';
1017:         try {
1018:             return (bool)$this->_db->selectValue($sql, array($this->_devId, $this->_deviceInfo->user));
1019:         } catch (Horde_Db_Exception $e) {
1020:             throw new Horde_ActiveSync_Exception($e);
1021:         }
1022:     }
1023: 
1024:     /**
1025:      * Get the timestamp for the last successful sync for the current collection
1026:      * or specified syncKey.
1027:      *
1028:      * @param string $syncKey  The (optional) syncKey to check.
1029:      *
1030:      * @return integer  The timestamp of the last successful sync or 0 if none
1031:      */
1032:     protected function _getLastSyncTS($syncKey = 0)
1033:     {
1034:         $sql = 'SELECT MAX(sync_time) FROM ' . $this->_syncStateTable . ' WHERE sync_folderid = ? AND sync_devid = ?';
1035:         $values = array($this->_collection['id'], $this->_devId);
1036:         if (!empty($syncKey)) {
1037:             $sql .= ' AND sync_key = ?';
1038:             array_push($values, $syncKey);
1039:         }
1040:         try {
1041:             $this->_lastSyncTS = $this->_db->selectValue($sql, $values);
1042:         } catch (Horde_Db_Exception $e) {
1043:             throw new Horde_ActiveSync_Exception($e);
1044:         }
1045: 
1046:         return !empty($this->_lastSyncTS) ? $this->_lastSyncTS : 0;
1047:     }
1048: 
1049:     /**
1050:      * Garbage collector - clean up from previous sync requests.
1051:      *
1052:      * @params string $syncKey  The sync key
1053:      *
1054:      * @throws Horde_ActiveSync_Exception
1055:      * @return boolean?
1056:      */
1057:     protected function _gc($syncKey)
1058:     {
1059:         if (!preg_match('/^s{0,1}\{([0-9A-Za-z-]+)\}([0-9]+)$/', $syncKey, $matches)) {
1060:             return false;
1061:         }
1062:         $guid = $matches[1];
1063:         $n = $matches[2];
1064: 
1065:         // Clean up all but the last 2 syncs for any given sync series, this
1066:         // ensures that we can still respond to SYNC requests for the previous
1067:         // key if the PIM never received the new key in a SYNC response.
1068:         $sql = 'SELECT sync_key FROM ' . $this->_syncStateTable
1069:             . ' WHERE sync_devid = ? AND sync_folderid = ?';
1070:         $values = array(
1071:             $this->_devId,
1072:             !empty($this->_collection['id']) ?
1073:                 $this->_collection['id'] :
1074:                 'foldersync');
1075: 
1076:         $results = $this->_db->selectAll($sql, $values);
1077:         $remove = array();
1078:         $guids = array($guid);
1079:         foreach ($results as $oldkey) {
1080:             if (preg_match('/^s{0,1}\{([0-9A-Za-z-]+)\}([0-9]+)$/', $oldkey['sync_key'], $matches)) {
1081:                 if ($matches[1] == $guid && $matches[2] < $n) {
1082:                     $remove[] = $oldkey['sync_key'];
1083:                 }
1084:             } else {
1085:                 /* stale key from previous key series */
1086:                 $remove[] = $oldkey['sync_key'];
1087:                 $guids[] = $matches[1];
1088:             }
1089:         }
1090:         if (count($remove)) {
1091:             $sql = 'DELETE FROM ' . $this->_syncStateTable . ' WHERE sync_key IN ('
1092:                 . str_repeat('?,', count($remove) - 1) . '?)';
1093:             $this->_db->delete($sql, $remove);
1094:         }
1095: 
1096:         // Also clean up the map table since this data is only needed for one
1097:         // SYNC cycle. Keep the same number of old keys for the same reasons as
1098:         // above.
1099:         $sql = 'SELECT sync_key FROM ' . $this->_syncMapTable
1100:             . ' WHERE sync_devid = ? AND sync_user = ?';
1101:         $maps = $this->_db->selectValues($sql, array($this->_devId, $this->_deviceInfo->user));
1102:         foreach ($maps as $key) {
1103:             if (preg_match('/^s{0,1}\{([0-9A-Za-z-]+)\}([0-9]+)$/', $key, $matches)) {
1104:                 if ($matches[1] == $guid && $matches[2] < $n) {
1105:                     $remove[] = $key;
1106:                 }
1107:             }
1108:         }
1109:         if (count($remove)) {
1110:             $sql = 'DELETE FROM ' . $this->_syncMapTable . ' WHERE sync_key IN ('
1111:                 . str_repeat('?,', count($remove) - 1) . '?)';
1112:             $this->_db->delete($sql, $remove);
1113:         }
1114: 
1115:         return true;
1116:     }
1117: 
1118:     /**
1119:      * Reset the sync state for this device, for the specified collection.
1120:      *
1121:      * @param string $id  The collection to reset.
1122:      *
1123:      * @return void
1124:      * @throws Horde_ActiveSync_Exception
1125:      */
1126:     protected function _resetDeviceState($id)
1127:     {
1128:         $this->_logger->debug('[' . $this->_devId . '] Resetting device state.');
1129:         $state_query = 'DELETE FROM ' . $this->_syncStateTable . ' WHERE sync_devid = ? AND sync_folderid = ?';
1130:         $map_query = 'DELETE FROM ' . $this->_syncMapTable . ' WHERE sync_devid = ? AND sync_folderid = ?';
1131:         $user = 'DELETE FROM ' . $this->_syncUsersTable . ' WHERE device_id = ? AND device_user = ?';
1132:         try {
1133:             $this->_db->delete($state_query, array($this->_devId, $id));
1134:             $this->_db->delete($map_query, array($this->_devId, $id));
1135:             $this->_db->delete($user, array($this->_devId, $this->_devInfo->user));
1136:         } catch (Horde_Db_Exception $e) {
1137:             throw new Horde_ActiveSync_Exception($e);
1138:         }
1139:     }
1140: 
1141: }