selectAssoc("SELECT id, name FROM companies LIMIT 3") => [1 => 'Ford', 2 => 'GM', 3 => 'Chrysler']
/**
* 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;
}
