Returns an array of records with the column names as keys, and
column values as values.
/**
* 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;
}
