/** * Ensure that an array of users exist in storage. Create any that don't, * return user_ids for all. * * @param array $users An array of users. Values typed as an integer * are assumed to already be an user_id. * * @return array An array of user_ids. */ public function ensureUsers($users) { if (!is_array($users)) { $users = array($users); } $userIds = array(); $userName = array(); // Anything already typed as an integer is assumed to be a user id. foreach ($users as $userIndex => $user) { if (is_int($user)) { $userIds[$userIndex] = $user; } else { $userName[$user] = $userIndex; } } // Get the ids for any users that already exist. try { if (count($userName)) { $userName; $sql = 'SELECT user_id, user_name FROM ' . $this->_t('users') . ' WHERE user_name IN (' . implode(',', array_map(array($this, 'toDriver'), array_keys($userName))) . ')'; foreach ($this->_db->select($sql) as $row) { $userIndex = $userName[$row['user_name']]; unset($userName[$row['user_name']]); $userIds[$userIndex] = $row['user_id']; } } // Create any users that didn't already exist foreach ($userName as $user => $userIndex) { $userIds[$userIndex] = $this->_db->insert('INSERT INTO ' . $this->_t('users') . ' (user_name) VALUES (' . $this->toDriver($user) . ')'); } } catch (Horde_Db_Exception $e) { throw new Content_Exception($e); } return $userIds; }
/** */ public function get($mailbox, $uids, $fields, $uidvalid) { $this->getMetaData($mailbox, $uidvalid, array('uidvalid')); $query = $this->_baseSql($mailbox, self::MSG_TABLE); $query[0] = 'SELECT t.data, t.msguid ' . $query[0]; $uid_query = array(); foreach ($uids as $val) { $uid_query[] = 't.msguid = ?'; $query[1][] = strval($val); } $query[0] .= ' AND (' . implode(' OR ', $uid_query) . ')'; $compress = new Horde_Compress_Fast(); $out = array(); try { $columns = $this->_db->columns(self::MSG_TABLE); $res = $this->_db->select($query[0], $query[1]); foreach ($res as $row) { try { $out[$row['msguid']] = @unserialize($compress->decompress($columns['data']->binaryToString($row['data']))); } catch (Exception $e) { } } } catch (Horde_Db_Exception $e) { } return $out; }
public function getMany($num = 50) { $tasks = array(); $values = array(); $query = 'SELECT * FROM horde_queue_tasks where task_queue = ? ORDER BY task_id LIMIT ?'; $values[] = $this->_queue; $values[] = $num; try { $rows = $this->_db->select($query, $values); } catch (Horde_Db_Exception $e) { throw new Horde_Queue_Exception($e); } $query = 'DELETE FROM horde_queue_tasks WHERE task_id = ?'; foreach ($rows as $row) { $tasks[] = unserialize($row['task_fields']); // TODO: Evaluate if a single call for all IDs is faster for // various scenarios try { $this->_db->delete($query, array($row['task_id'])); } catch (Horde_Db_Exception $e) { throw new Horde_Queue_Exception($e); } } return $tasks; }
/** * Retrieve an option set from the storage backend. * * @param boolean $defaults Whether to retrieve the global defaults * instead of user options. * * @return array Array of option-value pairs. * @throws Sam_Exception */ protected function _retrieve($defaults = false) { $user = $defaults ? $this->_params['global_user'] : $this->_user; try { $result = $this->_db->select('SELECT * FROM ' . $this->_params['table'] . ' WHERE username = ?', array($user)); } catch (Horde_Db_Exception $e) { throw new Sam_Exception($e); } /* Loop through rows, retrieving options. */ $return = array(); foreach ($result as $row) { $attribute = $this->_mapOptionToAttribute($row['preference']); if (isset($return[$attribute])) { if (!is_array($return[$attribute])) { $return[$attribute] = array($return[$attribute]); } if (!in_array($row['value'], $return[$attribute])) { $return[$attribute][] = $row['value']; } } else { $return[$attribute] = $row['value']; } } return $return; }
/** * Retrieves all of the notes of the current notepad from the backend. * * @throws Mnemo_Exception */ public function retrieve() { $query = 'SELECT * FROM mnemo_memos WHERE memo_owner = ?'; $values = array($this->_notepad); try { $rows = $this->_db->select($query, $values); } catch (Horde_Db_Exception $e) { throw new Mnemo_Exception($e->getMessage()); } // Store the retrieved values in a fresh list. $this->_memos = array(); foreach ($rows as $row) { $this->_memos[$row['memo_id']] = $this->_buildNote($row); } }
/** * Fetch client settings from storage. * * @param integer the client id * * @return array A hash of client settings. * @throws Hermes_Exception */ public function getClientSettings($clientID) { $clients = Hermes::listClients(); if (empty($clientID) || !isset($clients[$clientID])) { throw new Horde_Exception_NotFound('Does not exist'); } $sql = 'SELECT clientjob_id, clientjob_enterdescription,' . ' clientjob_exportid FROM hermes_clientjobs' . ' WHERE clientjob_id = ?'; $values = array($clientID); try { $rows = $this->_db->select($sql, $values); } catch (Horde_Db_Exception $e) { throw new Hermes_Exception($e); } $clientJob = array(); foreach ($rows as $row) { $clientJob[$row['clientjob_id']] = array($row['clientjob_enterdescription'], $row['clientjob_exportid']); } if (isset($clientJob[$clientID])) { $settings = array('id' => $clientID, 'enterdescription' => $clientJob[$clientID][0], 'exportid' => $this->_convertFromDriver($clientJob[$clientID][1])); } else { $settings = array('id' => $clientID, 'enterdescription' => 1, 'exportid' => null); } $settings['name'] = $clients[$clientID]; return $settings; }
/** * Return a list of valid locks with the option to limit the results * by principal, scope and/or type. * * @see Horde_Lock_Base::getLocks() */ public function getLocks($scope = null, $principal = null, $type = null) { $now = time(); $sql = 'SELECT lock_id, lock_owner, lock_scope, lock_principal, ' . 'lock_origin_timestamp, lock_update_timestamp, ' . 'lock_expiry_timestamp, lock_type FROM ' . $this->_params['table'] . ' WHERE (lock_expiry_timestamp >= ? OR lock_expiry_timestamp = ?)'; $values = array($now, Horde_Lock::PERMANENT); // Check to see if we need to filter the results if (!empty($principal)) { $sql .= ' AND lock_principal = ?'; $values[] = $principal; } if (!empty($scope)) { $sql .= ' AND lock_scope = ?'; $values[] = $scope; } if (!empty($type)) { $sql .= ' AND lock_type = ?'; $values[] = $type; } try { $result = $this->_db->select($sql, $values); } catch (Horde_Db_Exception $e) { throw new Horde_Lock_Exception($e); } $locks = array(); foreach ($result as $row) { $locks[$row['lock_id']] = $row; } return $locks; }
/** * Reads the given data from the SQL database and returns the results. * * @param string $key The primary key field to use. * @param mixed $ids The ids of the contacts to load. * @param string $owner Only return contacts owned by this user. * @param array $fields List of fields to return. * @param array $blobFields Array of fields containing binary data. * @param array $dateFields Array of fields containing date data. * @since 4.2.0 * * @return array Hash containing the search results. * @throws Turba_Exception */ protected function _read($key, $ids, $owner, array $fields, array $blobFields = array(), array $dateFields = array()) { $values = array(); $in = ''; if (is_array($ids)) { if (!count($ids)) { return array(); } foreach ($ids as $id) { $in .= empty($in) ? '?' : ', ?'; $values[] = $this->_convertToDriver($id); } $where = $key . ' IN (' . $in . ')'; } else { $where = $key . ' = ?'; $values[] = $this->_convertToDriver($ids); } if (isset($this->map['__owner'])) { $where .= ' AND ' . $this->map['__owner'] . ' = ?'; $values[] = $this->_convertToDriver($owner); } if (!empty($this->_params['filter'])) { $where .= ' AND ' . $this->_params['filter']; } $query = 'SELECT ' . implode(', ', $fields) . ' FROM ' . $this->_params['table'] . ' WHERE ' . $where; try { return $this->_parseRead($blobFields, $this->_db->select($query, $values), $dateFields); } catch (Horde_Db_Exception $e) { throw new Turba_Exception(_("Server error when performing search.")); } }
/** * Fetches the fields for a particular form. * * @param integer $form_id The form id of the form to return. * * @return array The fields. * @throws Ulaform_Exception */ public function getFields($form_id, $field_id = null) { $values = array($form_id); $sql = 'SELECT field_id, form_id, field_name, field_order, field_label, field_type, ' . ' field_params, field_required, field_readonly, field_desc FROM ulaform_fields ' . ' WHERE form_id = ?'; if (!is_null($field_id)) { $sql .= ' AND field_id = ?'; $values[] = (int) $field_id; } $sql .= ' ORDER BY field_order'; try { $results = $this->_db->select($sql, $values); } catch (Horde_Db_Exception $e) { throw new Ulaform_Exception($e); } $fields = array(); foreach ($results as $field) { /* If no internal name set, generate one using field_id. */ if (empty($field['field_name'])) { $field['field_name'] = 'field_' . $field['field_id']; } /* Check if any params and unserialize, otherwise return null. */ if (!empty($field['field_params'])) { $field['field_params'] = Horde_Serialize::unserialize($field['field_params'], Horde_Serialize::UTF7_BASIC); } else { $field['field_params'] = null; } $fields[] = $field; } return $fields; }
/** * Ensure that an array of objects exist in storage. Create any that don't, * return object_ids for all. All objects in the $objects array must be * of the same content type. * * @param mixed $objects An array of objects (or single obejct value). * Values typed as an integer are assumed to already * be an object_id. * @param mixed $type Either a string type_name or integer type_id * * @return array An array of object_ids. */ public function ensureObjects($objects, $type) { if (!is_array($objects)) { $objects = array($objects); } $objectIds = array(); $objectName = array(); $type = current($this->_typeManager->ensureTypes($type)); // Anything already typed as an integer is assumed to be an object id. foreach ($objects as $objectIndex => $object) { if (is_int($object)) { $objectIds[$objectIndex] = $object; } else { $objectName[$object] = $objectIndex; } } // Get the ids for any objects that already exist. try { if (count($objectName)) { $rows = $this->_db->select('SELECT object_id, object_name FROM ' . $this->_t('objects') . ' WHERE object_name IN (' . implode(',', array_map(array($this->_db, 'quoteString'), array_keys($objectName))) . ') AND type_id = ' . $type); foreach ($rows as $row) { $objectIndex = $objectName[$row['object_name']]; unset($objectName[$row['object_name']]); $objectIds[$objectIndex] = $row['object_id']; } } // Create any objects that didn't already exist foreach ($objectName as $object => $objectIndex) { $objectIds[$objectIndex] = $this->_db->insert('INSERT INTO ' . $this->_t('objects') . ' (object_name, type_id) VALUES (' . $this->_db->quoteString($object) . ', ' . $type . ')'); } } catch (Horde_Db_Exception $e) { throw new Content_Exception($e); } return $objectIds; }
/** */ public function get($scope_ob) { $charset = $this->_db->getOption('charset'); $query = 'SELECT pref_name, pref_value FROM ' . $this->_params['table'] . ' ' . 'WHERE pref_uid = ? AND pref_scope = ?'; $values = array($this->_params['user'], $scope_ob->scope); try { $result = $this->_db->select($query, $values); $columns = $this->_db->columns($this->_params['table']); } catch (Horde_Db_Exception $e) { throw new Horde_Prefs_Exception($e); } foreach ($result as $row) { $name = trim($row['pref_name']); $value = $columns['pref_value']->binaryToString($row['pref_value']); $scope_ob->set($name, Horde_String::convertCharset($value, $charset, 'UTF-8')); } return $scope_ob; }
/** * Return the images corresponding to the given ids. * * @param array $params function parameters: * <pre> * 'ids' - An array of image ids to fetch. * 'preserve' - Preserve the order of the image ids when returned. * 'gallery_id' - Return all images from requested gallery (ignores 'ids'). * 'from' - If passing a gallery, start at this image. * 'count' - If passing a gallery, return this many images. * </pre> * * @return array An array of Ansel_Image objects. * @throws Ansel_Exception, Horde_Exception_NotFound, InvalidArgumentException */ public function getImages(array $params = array()) { // First check if we want a specific gallery or a list of images if (!empty($params['gallery_id'])) { $sql = 'SELECT ' . $this->_getImageFields() . ' FROM ansel_images WHERE gallery_id = ' . $params['gallery_id'] . ' ORDER BY image_sort'; } elseif (!empty($params['ids']) && is_array($params['ids']) && count($params['ids']) > 0) { $sql = 'SELECT ' . $this->_getImageFields() . ' FROM ansel_images WHERE image_id IN ('; $i = 1; $cnt = count($params['ids']); foreach ($params['ids'] as $id) { $sql .= (int) $id . ($i++ < $cnt ? ',' : ');'); } } else { throw new InvalidArgumentException('Ansel_Storage::getImages requires either a gallery_id or an array of image ids'); } // Limit the query? if (isset($params['count']) && isset($params['from'])) { $sql = $this->_db->addLimitOffset($sql, array('limit' => $params['count'], 'offset' => $params['from'])); } try { $images = $this->_db->select($sql); } catch (Horde_Db_Exception $e) { throw new Ansel_Exception($images); } // Throw exception if we asked for specific image ids and not found. if (empty($images) && empty($params['gallery_id'])) { throw new Horde_Exception_NotFound(_("Images not found")); } elseif (empty($images)) { return array(); } $return = array(); foreach ($images as $image) { $image['image_filename'] = Horde_String::convertCharset($image['image_filename'], $GLOBALS['conf']['sql']['charset'], 'UTF-8'); $image['image_caption'] = Horde_String::convertCharset($image['image_caption'], $GLOBALS['conf']['sql']['charset'], 'UTF-8'); $return[$image['image_id']] = new Ansel_Image($image); $this->_images[(int) $image['image_id']] =& $return[$image['image_id']]; } // Need to get comment counts if comments are enabled $ccounts = $this->_getImageCommentCounts(array_keys($return)); if (count($ccounts)) { foreach ($return as $key => $image) { $return[$key]->commentCount = !empty($ccounts[$key]) ? $ccounts[$key] : 0; } } // Preserve the order the images_ids were passed in if (empty($params['gallery_id']) && !empty($params['preserve'])) { foreach ($params['ids'] as $id) { $ordered[$id] = $return[$id]; } return $ordered; } return $return; }
/** * Initialization tasks. * * @throws Horde_Alarm_Exception */ public function initialize() { /* Handle any database specific initialization code to run. */ switch ($this->_db->adapterName()) { case 'PDO_Oci': $query = "ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'"; $this->_db->select($query); break; case 'PDO_PostgreSQL': $query = "SET datestyle TO 'iso'"; $this->_db->select($query); break; } }
/** * Returns an array of all system shares. * * @return array All system shares. * @throws Horde_Share_Exception */ public function listSystemShares() { $query = 'SELECT * FROM ' . $this->_table . ' WHERE share_owner IS NULL'; try { $rows = $this->_db->select($query); } catch (Horde_Db_Exception $e) { throw new Horde_Share_Exception($e->getMessage()); } $sharelist = array(); foreach ($rows as $share) { $this->_convertClobs($share); $data = $this->_fromDriverCharset($share); $this->_getSharePerms($data); $sharelist[$data['share_name']] = $this->_createObject($data); } return $sharelist; }
/** * Generate a tag cloud. Same syntax as getTags, except that fetching a * cloud for a userId + objectId combination doesn't make sense - the counts * would all be one. In addition, this method returns counts for each tag. * * @param array $args Search criteria: * - limit: (integer) Maximum number of tags to return. * - offset: (integet) Offset the results. Only useful for paginating, and * not recommended. * - userId: (string) Only return tags that have been applied by a * specific user. * - typeId: (array) Only return tags that have been applied by specific * object types. * - objectId: (array) Only return tags that have been applied to specific * objects all objects must be of the same type and * specified by typeId. * - tagIds: (array) Only return information on specific tag * (an array of tag ids). * * @return array An array of hashes, each containing tag_id, tag_name, and count. */ public function getTagCloud($args = array()) { if (isset($args['objectId'])) { if (empty($args['typeId'])) { throw new Content_Exception('Specified objectId, but failed to specify typeId for getTagCloud call.'); } $args['objectId'] = $this->_objectManager->ensureObjects($args['objectId'], $args['typeId']); $sql = 'SELECT t.tag_id AS tag_id, tag_name, COUNT(*) AS count FROM ' . $this->_t('tagged') . ' tagged INNER JOIN ' . $this->_t('tags') . ' t ON tagged.tag_id = t.tag_id WHERE tagged.object_id IN (' . implode(',', $args['objectId']) . ') GROUP BY t.tag_id, t.tag_name'; } elseif (isset($args['userId']) && isset($args['typeId'])) { $args['userId'] = current($this->_userManager->ensureUsers($args['userId'])); $args['typeId'] = $this->_typeManager->ensureTypes($args['typeId']); // This doesn't use a stat table, so may be slow. $sql = 'SELECT t.tag_id AS tag_id, tag_name, COUNT(*) AS count FROM ' . $this->_t('tagged') . ' tagged INNER JOIN ' . $this->_t('objects') . ' objects ON tagged.object_id = objects.object_id AND objects.type_id IN (' . implode(',', $args['typeId']) . ') INNER JOIN ' . $this->_t('tags') . ' t ON tagged.tag_id = t.tag_id WHERE tagged.user_id = ' . (int) $args['userId'] . ' GROUP BY t.tag_id, t.tag_name'; } elseif (isset($args['userId'])) { $args['userId'] = current($this->_userManager->ensureUsers($args['userId'])); $sql = 'SELECT t.tag_id AS tag_id, tag_name, count FROM ' . $this->_t('tagged') . ' tagged INNER JOIN ' . $this->_t('tags') . ' t ON tagged.tag_id = t.tag_id INNER JOIN ' . $this->_t('user_tag_stats') . ' uts ON t.tag_id = uts.tag_id AND uts.user_id = ' . (int) $args['userId'] . ' GROUP BY t.tag_id, tag_name, count'; } elseif (isset($args['tagIds']) && isset($args['typeId'])) { $args['typeId'] = $this->_typeManager->ensureTypes($args['typeId']); // This doesn't use a stat table, so may be slow. $sql = 'SELECT t.tag_id AS tag_id, tag_name, COUNT(*) AS count FROM ' . $this->_t('tagged') . ' tagged INNER JOIN ' . $this->_t('objects') . ' objects ON tagged.object_id = objects.object_id AND objects.type_id IN(' . implode(',', $args['typeId']) . ') INNER JOIN ' . $this->_t('tags') . ' t ON tagged.tag_id = t.tag_id AND t.tag_id IN (' . implode(', ', $args['tagIds']) . ') GROUP BY t.tag_id, t.tag_name'; } elseif (isset($args['typeId'])) { $args['typeId'] = $this->_typeManager->ensureTypes($args['typeId']); // This doesn't use a stat table, so may be slow. $sql = 'SELECT t.tag_id AS tag_id, tag_name, COUNT(*) AS count FROM ' . $this->_t('tagged') . ' tagged INNER JOIN ' . $this->_t('objects') . ' objects ON tagged.object_id = objects.object_id AND objects.type_id IN(' . implode(',', $args['typeId']) . ') INNER JOIN ' . $this->_t('tags') . ' t ON tagged.tag_id = t.tag_id GROUP BY t.tag_id, t.tag_name'; } elseif (isset($args['tagIds'])) { $ids = $this->_checkTags($args['tagIds'], false); $sql = 'SELECT t.tag_id AS tag_id, tag_name, COUNT(*) AS count FROM ' . $this->_t('tagged') . ' tagged INNER JOIN ' . $this->_t('tags') . ' t ON tagged.tag_id = t.tag_id INNER JOIN ' . $this->_t('tag_stats') . ' ts ON t.tag_id = ts.tag_id WHERE t.tag_id IN (' . implode(', ', $ids) . ') GROUP BY t.tag_id, t.tag_name'; } else { $sql = 'SELECT t.tag_id AS tag_id, tag_name, COUNT(*) AS count FROM ' . $this->_t('tagged') . ' tagged INNER JOIN ' . $this->_t('tags') . ' t ON tagged.tag_id = t.tag_id INNER JOIN ' . $this->_t('tag_stats') . ' ts ON t.tag_id = ts.tag_id GROUP BY t.tag_id, t.tag_name'; } if (isset($args['limit'])) { $sql = $this->_db->addLimitOffset($sql . ' ORDER BY count DESC', array('limit' => $args['limit'], 'offset' => isset($args['offset']) ? $args['offset'] : 0)); } try { $rows = $this->_db->select($sql); $results = array(); foreach ($rows as $row) { $results[$row['tag_id']] = $row; } return $results; } catch (Exception $e) { throw new Content_Exception($e); } }
/** * Lists a slice of the image ids in the given gallery. * * @param array $params Filter parameters. *<pre> * integer|array 'gallery_id' - A gallery id to list images from * integer 'offset' - The image to start listing from * integer 'limit' - How many images to return * array|string 'fields' - The fields to return * string 'sort' - The field to sort by. * array 'filter' - Additional filters. Each element is an * array containing 'property', 'op', and * 'value' keys. Passing 'IN' as the 'op' * and an array as 'value' will produce a * SQL IN conditional. *</pre> * * @return array An array of images. Either an array of ids, or an array * of field values, keyed by id. * @throws Ansel_Exception, InvalidArgumentException */ public function listImages(array $params = array()) { $params = new Horde_Support_Array($params); if (is_array($params['fields'])) { $field_count = count($params['fields']); $params['fields'] = implode(', ', $params['fields']); } elseif ($params['fields'] == '*') { // The count is not important, as long as it's > 1 $field_count = 2; } else { $field_count = substr_count($params->get('fields', 'image_id'), ',') + 1; } if (is_array($params['sort'])) { $params['sort'] = implode(', ', $params['sort']); } if (is_array($params['gallery_id'])) { $query_where = 'WHERE gallery_id IN (' . implode(',', $params['gallery_id']) . ')'; } elseif ($params['gallery_id']) { $query_where = 'WHERE gallery_id = ' . $params['gallery_id']; } else { $query_where = ''; } if ($params['filter']) { foreach ($params['filter'] as $filter) { $query_where .= (!empty($query_where) ? ' AND ' : ' WHERE ') . $this->_toImageDriverName($filter['property']) . ' ' . $filter['op'] . ' ' . (is_array($filter['value']) ? '(' . implode(',', $filter['value']) . ')' : $filter['value']); } } $sql = 'SELECT ' . $params->get('fields', 'image_id') . ' FROM ansel_images ' . $query_where . ' ORDER BY ' . $params->get('sort', 'image_sort'); $sql = $this->_db->addLimitOffset($sql, array('limit' => $params->get('limit', 0), 'offset' => $params->get('offset', 0))); try { if ($field_count > 1) { $results = $this->_db->select($sql); $images = array(); foreach ($results as $image) { $images[$image['image_id']] = $image; } return $images; } else { return $this->_db->selectValues($sql); } } catch (Horde_Db_Exception $e) { throw new Ansel_Exception($e); } }
/** * Retrieves a set of pages matching an SQL WHERE clause. * * @param string $table Table to retrieve pages from. * @param array|string $where Where clause for sql statement (without the * 'WHERE'). If an array the 1st element is the * clause with placeholder, the 2nd element the * values. * @param string $orderBy Order results by this column. * @param integer $limit Maximum number of pages to fetch. * * @return array A list of page hashes. * @throws Wicked_Exception */ protected function _retrieve($table, $where, $orderBy = null, $limit = null) { $query = 'SELECT * FROM ' . $table; $values = array(); if (!empty($where)) { $query .= ' WHERE '; if (is_array($where)) { $query .= $where[0]; $values = $where[1]; } else { $query .= $where; } } if (!empty($orderBy)) { $query .= ' ORDER BY ' . $orderBy; } if (!empty($limit)) { try { $query = $this->_db->addLimitOffset($query, array('limit' => $limit)); } catch (Horde_Db_Exception $e) { throw new Wicked_Exception($e); } } try { $result = $this->_db->select($query, $values); } catch (Horde_Db_Exception $e) { throw new Wicked_Exception($e); } $pages = array(); foreach ($result as $row) { if (isset($row['page_name'])) { $row['page_name'] = $this->_convertFromDriver($row['page_name']); } if (isset($row['page_text'])) { $row['page_text'] = $this->_convertFromDriver($row['page_text']); } if (isset($row['change_log'])) { $row['change_log'] = $this->_convertFromDriver($row['change_log']); } $pages[] = $row; } return $pages; }
/** * Lists all alarms near $date. * * @param integer $date The unix epoch time to check for alarms. * * @return array An array of tasks that have alarms that match. * @throws Nag_Exception */ public function listAlarms($date) { // Check for non-empty alarm AND a non-empty due date. // See Bug: 14214 $q = 'SELECT * FROM nag_tasks' . ' WHERE task_owner = ?' . ' AND task_alarm > 0 AND task_due > 0' . ' AND (task_due - (task_alarm * 60) <= ?)' . ' AND task_completed = 0'; $values = array($this->_tasklist, $date); try { $result = $this->_db->select($q, $values); } catch (Horde_Db_Exception $e) { throw new Nag_Exception($e->getMessage()); } $tasks = array(); foreach ($result as $row) { $task = new Nag_Task($this, $this->_buildTask($row)); if ($task->getNextDue()->before($date + $task->alarm * 60)) { $tasks[$row['task_id']] = $task; } } return $tasks; }
/** * Searches forums for matching threads or posts. * * @param array $filter Hash of filter criteria: * 'forums' => Array of forum IDs to search. If not * present, searches all forums. * 'keywords' => Array of keywords to search for. If not * present, finds all posts/threads. * 'allkeywords' => Boolean specifying whether to find all * keywords; otherwise, wants any keyword. * False if not supplied. * 'message_author' => Name of author to find posts by. If not * present, any author. * 'searchsubjects' => Boolean specifying whether to search * subjects. True if not supplied. * 'searchcontents' => Boolean specifying whether to search * post contents. False if not supplied. * @param string $sort_by The column by which to sort. * @param integer $sort_dir The direction by which to sort: * 0 - ascending * 1 - descending * @param string $from The thread to start listing at. * @param string $count The number of threads to return. * * @return array A search result hash where: * 'results' => Array of messages. * 'total => Total message number. * @throws Agora_Exception */ public function search($filter, $sort_by = 'message_subject', $sort_dir = 0, $from = 0, $count = 0) { if (!isset($filter['allkeywords'])) { $filter['allkeywords'] = false; } if (!isset($filter['searchsubjects'])) { $filter['searchsubjects'] = true; } if (!isset($filter['searchcontents'])) { $filter['searchcontents'] = false; } /* Select forums ids to search in */ $sql = 'SELECT forum_id, forum_name FROM ' . $this->_forums_table . ' WHERE '; if (empty($filter['forums'])) { $sql .= ' active = ? AND scope = ?'; $values = array(1, $this->_scope); } else { $sql .= ' forum_id IN (' . implode(',', $filter['forums']) . ')'; $values = array(); } try { $forums = $this->_db->selectAssoc($sql, $values); } catch (Horde_Db_Exception $e) { throw new Agora_Exception($e->getMessage()); } /* Build query */ $sql = ' FROM ' . $this->_threads_table . ' WHERE forum_id IN (' . implode(',', array_keys($forums)) . ')'; if (!empty($filter['keywords'])) { $sql .= ' AND ('; if ($filter['searchsubjects']) { $keywords = ''; foreach ($filter['keywords'] as $keyword) { if (!empty($keywords)) { $keywords .= $filter['allkeywords'] ? ' AND ' : ' OR '; } $keywords .= 'message_subject LIKE ' . $this->_db->quote('%' . $keyword . '%'); } $sql .= '(' . $keywords . ')'; } if ($filter['searchcontents']) { if ($filter['searchsubjects']) { $sql .= ' OR '; } $keywords = ''; foreach ($filter['keywords'] as $keyword) { if (!empty($keywords)) { $keywords .= $filter['allkeywords'] ? ' AND ' : ' OR '; } $keywords .= 'body LIKE ' . $this->_db->quote('%' . $keyword . '%'); } $sql .= '(' . $keywords . ')'; } $sql .= ')'; } if (!empty($filter['author'])) { $sql .= ' AND message_author = ' . $this->_db->quote(Horde_String::lower($filter['author'])); } /* Sort by result column. */ $sql .= ' ORDER BY ' . $sort_by . ' ' . ($sort_dir ? 'DESC' : 'ASC'); /* Slice directly in DB. */ if ($count) { $total = $this->_db->selectValue('SELECT COUNT(*) ' . $sql); $sql = $this->_db->addLimitOffset($sql, array('limit' => $count, 'offset' => $from)); } $sql = 'SELECT message_id, forum_id, message_subject, message_author, message_timestamp ' . $sql; try { $messages = $this->_db->select($sql); } catch (Horde_Db_Exception $e) { throw new Agora_Exception($e->getMessage()); } if (empty($messages)) { return array('results' => array(), 'total' => 0); } $results = array(); $msg_url = Horde::url('messages/index.php'); $forum_url = Horde::url('threads.php'); while ($message = $messages->fetch()) { if (!isset($results[$message['forum_id']])) { $index = array('agora' => $message['forum_id'], 'scope' => $this->_scope); $results[$message['forum_id']] = array('forum_id' => $message['forum_id'], 'forum_url' => $forum_url->add($index), 'forum_name' => $this->convertFromDriver($forums[$message['forum_id']]), 'messages' => array()); } $index = array('agora' => $message['forum_id'] . '.' . $message['message_id'], 'scope' => $this->_scope); $results[$message['forum_id']]['messages'][] = array('message_id' => $message['message_id'], 'message_subject' => htmlspecialchars($this->convertFromDriver($message['message_subject'])), 'message_author' => $message['message_author'], 'message_date' => $this->dateFormat($message['message_timestamp']), 'message_url' => $msg_url->add($index)); } return array('results' => $results, 'total' => $total); }
/** * Lists all events that satisfy the given conditions. * * @param Horde_Date $startInterval Start of range date object. * @param Horde_Date $endInterval End of range data object. * @param string $conditions Conditions, given as SQL clauses. * @param array $vals SQL bind variables for use with * $conditions clauses. * * @return array Events in the given time range satisfying the given * conditions. * @throws Kronolith_Exception */ private function _listEventsConditional(Horde_Date $startInterval = null, Horde_Date $endInterval = null, $conditions = '', array $vals = array()) { if ($this->getParam('utc')) { if (!is_null($startInterval)) { $startInterval = clone $startInterval; $startInterval->setTimezone('UTC'); } if (!is_null($endInterval)) { $endInterval = clone $endInterval; $endInterval->setTimezone('UTC'); } } $q = 'SELECT event_id, event_uid, event_description, event_location,' . ' event_private, event_status, event_attendees, event_organizer,' . ' event_title, event_recurcount, event_url, event_timezone,' . ' event_recurtype, event_recurenddate, event_recurinterval,' . ' event_recurdays, event_start, event_end, event_allday,' . ' event_alarm, event_alarm_methods, event_modified,' . ' event_exceptions, event_creator_id, event_resources, event_baseid,' . ' event_exceptionoriginaldate FROM kronolith_events' . ' WHERE calendar_id = ?'; $values = array($this->calendar); if ($conditions) { $q .= ' AND ' . $conditions; $values = array_merge($values, $vals); } if (!is_null($startInterval) && !is_null($endInterval)) { $etime = $endInterval->format('Y-m-d H:i:s'); $stime = $startInterval->format('Y-m-d H:i:s'); $q .= ' AND ((event_end >= ? AND event_start <= ?) OR (event_recurenddate >= ? AND event_start <= ? AND event_recurtype <> ?))'; array_push($values, $stime, $etime, $stime, $etime, Horde_Date_Recurrence::RECUR_NONE); } elseif (!is_null($startInterval)) { $stime = $startInterval->format('Y-m-d H:i:s'); $q .= ' AND ((event_end >= ?) OR (event_recurenddate >= ? AND event_recurtype <> ?))'; array_push($values, $stime, $stime, Horde_Date_Recurrence::RECUR_NONE); } elseif (!is_null($endInterval)) { $q .= ' AND (event_start <= ?)'; $values[] = $endInterval->format('Y-m-d H:i:s'); } /* Run the query. */ try { $qr = $this->_db->select($q, $values); } catch (Horde_Db_Exception $e) { throw new Kronolith_Exception($e); } $events = array(); foreach ($qr as $row) { /* If the event did not have a UID before, we need to give it * one. */ if (empty($row['event_uid'])) { $row['event_uid'] = (string) new Horde_Support_Guid(); /* Save the new UID for data integrity. */ $query = 'UPDATE kronolith_events SET event_uid = ? WHERE event_id = ?'; $values = array($row['event_uid'], $row['event_id']); try { $this->_db->update($query, $values); } catch (Horde_Db_Exception $e) { throw new Kronolith_Exception($e); } } /* Convert TEXT/CLOB fields. */ $row = $this->convertBlobs($row); /* We have all the information we need to create an event object * for this event, so go ahead and cache it. */ $this->_cache[$this->calendar][$row['event_id']] = new $this->_eventClass($this, $row); if ($row['event_recurtype'] == Horde_Date_Recurrence::RECUR_NONE) { $events[$row['event_uid']] = $row['event_id']; } else { $next = $this->nextRecurrence($row['event_id'], $startInterval); if ($next && (is_null($endInterval) || $next->compareDateTime($endInterval) < 0)) { $events[$row['event_uid']] = $row['event_id']; } } } return $events; }
/** * Stores user preferences and default values in the backend. * * @param boolean $defaults Whether to store the global defaults instead * of user options. Unused. * * @throws Sam_Exception */ public function store($defaults = false) { /* Check if the policy already exists. */ $policyID = $this->_lookupPolicyID(); /* Delete existing policy. */ if ($policyID !== false) { try { $this->_db->delete(sprintf('DELETE FROM %s WHERE %s = ?', $this->_mapNameToTable('policies'), $this->_mapAttributeToField('policies', 'name')), array($this->_user)); } catch (Horde_Db_Exception $e) { throw new Sam_Exception($e); } } /* Insert new policy (everything but whitelists and blacklists). */ $insertKeys = $insertVals = array(); foreach ($this->_options as $attribute => $value) { if ($attribute != 'whitelist_from' && $attribute != 'blacklist_from') { $insertKeys[] = $this->_mapAttributeToField('policies', $attribute); $insertVals[] = strlen($value) ? $value : null; } } if (count($insertKeys)) { try { $this->_db->insert(sprintf('INSERT INTO %s (%s, %s) VALUES (%s)', $this->_mapNameToTable('policies'), $this->_mapAttributeToField('policies', 'name'), implode(', ', $insertKeys), implode(', ', array_fill(0, count($insertVals) + 1, '?'))), array_merge(array($this->_user), $insertVals)); } catch (Horde_Db_Exception $e) { throw new Sam_Exception($e); } } /* Get the new policy id for the recipients table. */ $policyID = $this->_lookupPolicyID(); /* Update recipients with new policy id. */ try { $this->_db->update(sprintf('UPDATE %s SET %s = ? WHERE %s = ?', $this->_mapNameToTable('recipients'), $this->_mapAttributeToField('recipients', 'policy_id'), $this->_mapAttributeToField('recipients', 'email')), array($policyID, $this->_user)); } catch (Horde_Db_Exception $e) { throw new Sam_Exception($e); } /* Find the user id. */ $userID = $this->_lookupUserID(); /* Query for whitelists and blacklists. */ try { $result = $this->_db->select(sprintf('SELECT %s, %s FROM %s WHERE %s = ?', $this->_mapAttributeToField('wblists', 'sender'), $this->_mapAttributeToField('wblists', 'type'), $this->_mapNameToTable('wblists'), $this->_mapAttributeToField('wblists', 'recipient')), array($userID)); } catch (Horde_Db_Exception $e) { throw new Sam_Exception($e); } /* Loop through results, retrieving whitelists and blacklists. */ $existing = array('whitelist_from' => array(), 'blacklist_from' => array()); foreach ($result as $row) { $type = $row[$this->_mapAttributeToField('wblists', 'type')]; $senderID = $row[$this->_mapAttributeToField('wblists', 'sender')]; /* Only proceed if sender is listed white or black. */ if (preg_match('/[WYBN]/i', $type)) { try { $sender = $this->_db->selectValue(sprintf('SELECT %s FROM %s WHERE %s = ?', $this->_mapAttributeToField('senders', 'email'), $this->_mapNameToTable('senders'), $this->_mapAttributeToField('senders', 'id')), array($senderID)); } catch (Horde_Db_Exception $e) { throw new Sam_Exception($e); } $list = preg_match('/[WY]/i', $type) ? 'whitelist_from' : 'blacklist_from'; if (isset($this->_options[$list]) && in_array($sender, $this->_options[$list])) { $existing[$list][] = $sender; } else { /* User removed an address from a list. */ try { $this->_db->delete(sprintf('DELETE FROM %s WHERE %s = ? AND %s = ?', $this->_mapNameToTable('wblists'), $this->_mapAttributeToField('wblists', 'sender'), $this->_mapAttributeToField('wblists', 'recipient')), array($senderID, $userID)); } catch (Horde_Db_Exception $e) { throw new Sam_Exception($e); } /* Check if there is anyone else using this sender * address. */ $query = sprintf('SELECT 1 FROM %s WHERE %s = ?', $this->_mapNameToTable('wblists'), $this->_mapAttributeToField('wblists', 'sender')); if (!$this->_db->selectValue($query, array($senderID))) { /* No one else needs this sender address, delete it * from senders table. */ try { $this->_db->delete(sprintf('DELETE FROM %s WHERE %s = ?', $this->_mapNameToTable('senders'), $this->_mapAttributeToField('senders', 'id')), array($senderID)); } catch (Horde_Db_Exception $e) { throw new Sam_Exception($e); } } } } } /* Check any additions to the lists. */ foreach (array('whitelist_from' => 'W', 'blacklist_from' => 'B') as $list => $type) { if (!isset($this->_options[$list])) { continue; } foreach ($this->_options[$list] as $sender) { if (in_array($sender, $existing[$list])) { continue; } /* Check if this sender address exists already. */ $wb_result = $this->_db->selectValue(sprintf('SELECT %s FROM %s WHERE %s = ?', $this->_mapAttributeToField('senders', 'id'), $this->_mapNameToTable('senders'), $this->_mapAttributeToField('senders', 'email')), array($sender)); if ($wb_result !== false) { /* Address exists, use it's ID */ $senderID = $wb_result; } else { /* Address doesn't exist, add it. */ try { $this->_db->insert(sprintf('INSERT INTO %s (%s) VALUES (?)', $this->_mapNameToTable('senders'), $this->_mapAttributeToField('senders', 'email')), array($sender)); } catch (Horde_Db_Exception $e) { throw new Sam_Exception($e); } try { $senderID = $this->_db->selectValue(sprintf('SELECT %s FROM %s WHERE %s = ?', $this->_mapAttributeToField('senders', 'id'), $this->_mapNameToTable('senders'), $this->_mapAttributeToField('senders', 'email')), array($sender)); } catch (Horde_Db_Exception $e) { throw new Sam_Exception($e); } } try { $this->_db->insert(sprintf('INSERT INTO %s (%s, %s, %s) VALUES (?, ?, ?)', $this->_mapNameToTable('wblists'), $this->_mapAttributeToField('wblists', 'recipient'), $this->_mapAttributeToField('wblists', 'sender'), $this->_mapAttributeToField('wblists', 'type')), array($userID, $senderID, $type)); } catch (Horde_Db_Exception $e) { throw new Sam_Exception($e); } } } /* Remove any disjoined sender IDs. */ try { $this->_db->delete(sprintf('DELETE FROM %s WHERE %s = ?', $this->_mapNameToTable('wblists'), $this->_mapAttributeToField('wblists', 'recipient')), array('')); } catch (Horde_Db_Exception $e) { throw new Sam_Exception($e); } }
/** * Garbage collector - clean up from previous sync requests. * * @param string $syncKey The sync key * * @throws Horde_ActiveSync_Exception */ protected function _gc($syncKey) { if (!preg_match('/^\\{([0-9A-Za-z-]+)\\}([0-9]+)$/', $syncKey, $matches)) { return; } $guid = $matches[1]; $n = $matches[2]; // Clean up all but the last 2 syncs for any given sync series, this // ensures that we can still respond to SYNC requests for the previous // key if the client never received the new key in a SYNC response. $sql = 'SELECT sync_key FROM ' . $this->_syncStateTable . ' WHERE sync_devid = ? AND sync_folderid = ? AND sync_user = ?'; $values = array($this->_deviceInfo->id, !empty($this->_collection['id']) ? $this->_collection['id'] : Horde_ActiveSync::CHANGE_TYPE_FOLDERSYNC, $this->_deviceInfo->user); try { $results = $this->_db->select($sql, $values); } catch (Horde_Db_Exception $e) { $this->_logger->err(sprintf('[%s] %s', $this->_procid, $e->getMessage())); throw new Horde_ActiveSync_Exception($e); } $remove = array(); $guids = array($guid); foreach ($results as $oldkey) { if (preg_match('/^\\{([0-9A-Za-z-]+)\\}([0-9]+)$/', $oldkey['sync_key'], $matches)) { if ($matches[1] == $guid && $matches[2] < $n - 1) { $remove[] = $oldkey['sync_key']; } } else { /* stale key from previous key series */ $remove[] = $oldkey['sync_key']; $guids[] = $matches[1]; } } if (count($remove)) { $sql = 'DELETE FROM ' . $this->_syncStateTable . ' WHERE sync_key IN (' . str_repeat('?,', count($remove) - 1) . '?)'; try { $this->_db->delete($sql, $remove); } catch (Horde_Db_Exception $e) { $this->_logger->err(sprintf('[%s] %s', $this->_procid, $e->getMessage())); throw new Horde_ActiveSync_Exception($e); } } // Also clean up the map table since this data is only needed for one // SYNC cycle. Keep the same number of old keys for the same reasons as // above. foreach (array($this->_syncMapTable, $this->_syncMailMapTable) as $table) { $remove = array(); $sql = 'SELECT DISTINCT sync_key FROM ' . $table . ' WHERE sync_devid = ? AND sync_user = ?'; try { $maps = $this->_db->selectValues($sql, array($this->_deviceInfo->id, $this->_deviceInfo->user)); } catch (Horde_Db_Exception $e) { $this->_logger->err(sprintf('[%s] %s', $this->_procid, $e->getMessage())); throw new Horde_ActiveSync_Exception($e); } foreach ($maps as $key) { if (preg_match('/^\\{([0-9A-Za-z-]+)\\}([0-9]+)$/', $key, $matches)) { if ($matches[1] == $guid && $matches[2] < $n) { $remove[] = $key; } } } if (count($remove)) { $sql = 'DELETE FROM ' . $table . ' WHERE sync_key IN (' . str_repeat('?,', count($remove) - 1) . '?)'; try { $this->_db->delete($sql, $remove); } catch (Horde_Db_Exception $e) { $this->_logger->err(sprintf('[%s] %s', $this->_procid, $e->getMessage())); throw new Horde_ActiveSync_Exception($e); } } } }
/** * Returns an array of records with the column names as keys, and * column values as values. * * @param string $sql SQL statement. * @param mixed $arg1 Either an array of bound parameters or a query * name. * @param string $arg2 If $arg1 contains bound parameters, the query * name. * * @return PDOStatement * @throws Horde_Db_Exception */ public function select($sql, $arg1 = null, $arg2 = null) { $result = $this->_read->select($sql, $arg1, $arg2); $this->_lastQuery = $this->_read->getLastQuery(); return $result; }