/** * Check for object existence without causing the objects to be created. * Helps save queries for things like tags when we already know the object * doesn't yet exist in rampage tables. * * @param mixed string|array $objects Either an object identifier or an * array of them. * @param mixed $type A type identifier. Either a string * type name or the integer type_id. * * @return mixed Either a hash of object_id => object_names or false if * the object(s) do not exist. * @throws InvalidArgumentException, Content_Exception */ public function exists($objects, $type) { $type = current($this->_typeManager->ensureTypes($type)); if (!is_array($objects)) { $objects = array($objects); } if (!count($objects)) { return array(); } // Ensure we take the object as a string indentifier. foreach ($objects as &$object) { $object = strval($object); } $params = $objects; $params[] = $type; try { $ids = $this->_db->selectAssoc('SELECT object_id, object_name FROM ' . $this->_t('objects') . ' WHERE object_name IN (' . str_repeat('?,', count($objects) - 1) . '?)' . ' AND type_id = ?', $params); if ($ids) { return $ids; } } catch (Horde_Db_Exception $e) { throw new Content_Exception($e); } return false; }
/** * Ensure that an array of types exist in storage. Create any that don't, * return type_ids for all. * * @param mixed $types An array of types or single type value. Values typed * as an integer are assumed to already be an type_id. * * @return array An array of type_ids. * @throws Content_Exception */ public function ensureTypes($types) { if (!is_array($types)) { $types = array($types); } $typeIds = array(); $typeName = array(); // Anything already typed as an integer is assumed to be a type id. foreach ($types as $typeIndex => $type) { if (is_int($type)) { $typeIds[$typeIndex] = $type; } else { $typeName[$type] = $typeIndex; } } try { // Get the ids for any types that already exist. if (count($typeName)) { $rows = $this->_db->selectAssoc('SELECT type_id, type_name FROM ' . $this->_t('types') . ' WHERE type_name IN (' . implode(',', array_map(array($this->_db, 'quoteString'), array_keys($typeName))) . ')'); foreach ($rows as $id => $type) { $typeIndex = $typeName[$type]; unset($typeName[$type]); $typeIds[$typeIndex] = (int) $id; } } // Create any types that didn't already exist foreach ($typeName as $type => $typeIndex) { $typeIds[$typeIndex] = intval($this->_db->insert('INSERT INTO ' . $this->_t('types') . ' (type_name) VALUES (' . $this->_db->quoteString($type) . ')')); } } catch (Horde_Db_Exception $e) { throw new Content_Exception($e); } return $typeIds; }
/** * Return an array of timestamps from the map table for the last * client-initiated change for the provided uid. Used to avoid mirroring back * changes to the client that it sent to the server. * * @param array $changes The changes array, each entry a hash containing * 'id' and 'type' keys. * * @return array An array of UID -> timestamp of the last client-initiated * change for the specified UIDs, or null if none found. */ protected function _getPIMChangeTS(array $changes) { $sql = 'SELECT message_uid, MAX(sync_modtime) FROM ' . $this->_syncMapTable . ' WHERE sync_devid = ? AND sync_user = ? AND sync_key IN (?, ?) '; // Get the allowed synckeys to include. $uuid = self::getSyncKeyUid($this->_syncKey); $cnt = self::getSyncKeyCounter($this->_syncKey); $values = array($this->_deviceInfo->id, $this->_deviceInfo->user); foreach (array($uuid . $cnt, $uuid . ($cnt - 1)) as $v) { $values[] = $v; } $conditions = array(); foreach ($changes as $change) { $d = $change['type'] == Horde_ActiveSync::CHANGE_TYPE_DELETE; $conditions[] = '(message_uid = ?' . ($d ? ' AND sync_deleted = ?) ' : ') '); $values[] = $change['id']; if ($d) { $values[] = $d; } } $sql .= 'AND (' . implode('OR ', $conditions) . ') GROUP BY message_uid'; try { return $this->_db->selectAssoc($sql, $values); } catch (Horde_Db_Exception $e) { throw new Horde_ActiveSync_Exception($e); } }
/** */ public function getMetaData($mailbox, $uidvalid, $entries) { $query = $this->_baseSql($mailbox, self::MD_TABLE); $query[0] = 'SELECT t.field, t.data ' . $query[0]; if (!empty($entries)) { $entries[] = 'uidvalid'; $entry_query = array(); foreach (array_unique($entries) as $val) { $entry_query[] = 't.field = ?'; $query[1][] = $val; } $query[0] .= ' AND (' . implode(' OR ', $entry_query) . ')'; } try { if ($res = $this->_db->selectAssoc($query[0], $query[1])) { $columns = $this->_db->columns(self::MD_TABLE); foreach ($res as $key => $val) { switch ($key) { case 'uidvalid': $res[$key] = $columns['data']->binaryToString($val); break; default: $res[$key] = @unserialize($columns['data']->binaryToString($val)); break; } } if (is_null($uidvalid) || !isset($res['uidvalid']) || $res['uidvalid'] == $uidvalid) { return $res; } $this->deleteMailbox($mailbox); } } catch (Horde_Db_Exception $e) { } return array(); }
/** * Get image's attribtues from storage * * @param int $image_id The image id * * @return array A image attribute hash * @throws Horde_Exception */ public function getImageAttributes($image_id) { try { return $this->_db->selectAssoc('SELECT attr_name, attr_value FROM ansel_image_attributes WHERE ' . ' image_id = ' . (int) $image_id); } catch (Horde_Db_Exception $e) { throw new Ansel_Exception($e); } }
/** * Searches for group names. * * @param string $name A search string. * * @return array A list of matching groups, with IDs as keys and names as * values. * @throws Horde_Group_Exception */ public function search($name) { try { return $this->_db->selectAssoc('SELECT group_uid, group_name FROM horde_groups WHERE group_name LIKE ?', array('%' . $name . '%')); } catch (Horde_Db_Exception $e) { throw new Horde_Group_Exception($e); } }
/** * Returns all permissions of the system in a tree format. * * @return array A hash with all permissions in a tree format. * @throws Horde_Perms_Exception */ public function getTree() { $query = 'SELECT perm_id, perm_name FROM ' . $this->_params['table'] . ' ORDER BY perm_name ASC'; try { $tree = $this->_db->selectAssoc($query); } catch (Horde_Db_Exception $e) { throw new Horde_Perms_Exception($e); } $tree[Horde_Perms::ROOT] = Horde_Perms::ROOT; return $tree; }
/** * Return history objects with changes during a modseq interval, and * optionally filtered on other fields as well. * * @param integer $start The (exclusive) start of the modseq range. * @param integer $end The (inclusive) end of the modseq range. * @param array $filters An array of additional (ANDed) criteria. * Each array value should be an array with 3 * entries: * - field: the history field being compared (i.e. * 'action'). * - op: the operator to compare this field with. * - value: the value to check for (i.e. 'add'). * @param string $parent The parent history to start searching at. If * non-empty, will be searched for with a LIKE * '$parent:%' clause. * * @return array An array of history object ids that have had at least one * match for the given $filters. Will return empty array if * none matched the criteria. If the same GUID has multiple * matches withing the range requested, there is no guarantee * which entry will be returned. * * Note: For BC reasons, the results are returned keyed by the object UID, * with a (fairly useless) history_id as the value. @todo This * should be changed for Horde 6. */ protected function _getByModSeq($start, $end, $filters = array(), $parent = null) { // Build the modseq test. $where = array(sprintf('history_modseq > %d AND history_modseq <= %d', $start, $end)); // Add additional filters, if there are any. if ($filters) { foreach ($filters as $filter) { $where[] = 'history_' . $filter['field'] . ' ' . $filter['op'] . ' ' . $this->_db->quote($filter['value']); } } if ($parent) { $where[] = 'object_uid LIKE ' . $this->_db->quote($parent . ':%'); } return $this->_db->selectAssoc('SELECT DISTINCT object_uid, history_id FROM horde_histories WHERE ' . implode(' AND ', $where)); }
public function getPages($special = true, $no_cache = false) { if (!isset($this->_pageNames) || $no_cache) { try { $result = $this->_db->selectAssoc('SELECT page_id, page_name FROM ' . $this->_params['table']); } catch (Horde_Db_Exception $e) { throw new Wicked_Exception($e); } $this->_pageNames = $this->_convertFromDriver($result); } if ($special) { return $this->_pageNames + $this->getSpecialPages(); } return $this->_pageNames; }
/** * Retrieve a set of tags with relationships to the specified set * of tags. * * @param array $ids An array of tag_ids. * @param integer $object The object type to limit to. * @param string $user The user to limit to. * * @return array A hash of tag_id -> tag_name */ public function browseTags($ids, $object_type, $user) { if (!count($ids)) { return array(); } $sql = 'SELECT DISTINCT t.tag_id, t.tag_name FROM ' . $this->_t('tagged') . ' as r, ' . $this->_t('objects') . ' as i, ' . $this->_t('tags') . ' as t'; for ($i = 0; $i < count($ids); $i++) { $sql .= ',' . $this->_t('tagged') . ' as r' . $i; } $sql .= ' WHERE r.tag_id = t.tag_id AND r.object_id = i.object_id'; for ($i = 0; $i < count($ids); $i++) { $sql .= ' AND r' . $i . '.object_id = r.object_id AND r.tag_id != ' . (int) $ids[$i] . ' AND r' . $i . '.tag_id = ' . (int) $ids[$i]; } /* Note that we don't convertCharset here, it's done in listTagInfo */ $tags = $this->_db->selectAssoc($sql); foreach ($tags as $key => &$value) { $value = Horde_String::convertCharset($value, $this->_db->getOption('charset'), 'UTF-8'); } return $tags; }
/** * 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); }
/** * Returns an array where the keys are the first column of a select, and the * values are the second column: * * selectAssoc("SELECT id, name FROM companies LIMIT 3") => [1 => 'Ford', 2 => 'GM', 3 => 'Chrysler'] * * @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 array * @throws Horde_Db_Exception */ public function selectAssoc($sql, $arg1 = null, $arg2 = null) { $result = $this->_read->selectAssoc($sql, $arg1, $arg2); $this->_lastQuery = $this->_read->getLastQuery(); return $result; }