/** * Set the location of the specified event _id * * @see Kronolith_Geo_Base#setLocation() * @throws Kronolith_Exception */ public function setLocation($event_id, $point) { /* First make sure it doesn't already exist */ $sql = 'SELECT COUNT(*) FROM kronolith_events_geo WHERE event_id = ?'; try { $count = $this->_db->selectValue($sql, array($event_id)); } catch (Horde_Db_Exception $e) { throw new Kronolith_Exception($e); } /* Do we actually have data? If not, see if we are deleting an * existing entry. */ if ((empty($point['lat']) || empty($point['lon'])) && $count) { // Delete the record. $this->deleteLocation($event_id); return; } elseif (empty($point['lat']) || empty($point['lon'])) { return; } if (empty($point['zoom'])) { $point['zoom'] = 0; } /* INSERT or UPDATE */ $params = array($point['lat'], $point['lon'], $point['zoom'], $event_id); try { if ($count) { $sql = 'UPDATE kronolith_events_geo SET event_lat = ?, event_lon = ?, event_zoom = ? WHERE event_id = ?'; $this->_db->update($sql, $params); } else { $sql = 'INSERT into kronolith_events_geo (event_lat, event_lon, event_zoom, event_id) VALUES(?, ?, ?, ?)'; $this->_db->insert($sql, $params); } } catch (Horde_Db_Exception $e) { throw new Horde_Exception($e); } }
/** * Check if tags exists, optionally create them if they don't and return ids * for all that exist (including those that are optionally created). * * @param string|array $tags The tag names to check. * @param boolean $create If true, create the tag in the tags table. * * @return array A hash of tag_name => tag_id values. */ protected function _checkTags($tags, $create = true) { if (empty($tags)) { return array(); } if (!is_array($tags)) { $tags = is_int($tags) ? array($tags) : $this->splitTags($tags); } $tagIds = array(); // Anything already typed as an integer is assumed to be a tag id. foreach ($tags as $tag) { if (is_int($tag)) { $tagIds[$tag] = $tag; continue; } // Don't attempt to tag with an empty value if (!strlen(trim($tag))) { continue; } // Get the ids for any tags that already exist. $sql = 'SELECT tag_id FROM ' . $this->_t('tags') . ' WHERE LOWER(tag_name) = LOWER(' . $this->toDriver($tag) . ')'; if ($id = $this->_db->selectValue($sql)) { $tagIds[$tag] = (int) $id; } elseif ($create) { // Create any tags that didn't already exist $tagIds[$tag] = (int) $this->_db->insert('INSERT INTO ' . $this->_t('tags') . ' (tag_name) VALUES (' . $this->toDriver($tag) . ')'); } } return $tagIds; }
/** * Return the current value of the modseq. We take the MAX of the * horde_histories table instead of the value of the horde_histories_modseq * table to ensure we never miss an entry if we query the history system * between the time we call nextModSeq() and the time the new entry is * written. * * @param string $parent Restrict to entries a specific parent. * * @return integer|boolean The highest used modseq value, false if no history. */ public function getHighestModSeq($parent = null) { $sql = 'SELECT history_modseq FROM horde_histories'; if (!empty($parent)) { $sql .= ' WHERE object_uid LIKE ' . $this->_db->quote($parent . ':%'); } $sql .= ' ORDER BY history_modseq DESC'; $sql = $this->_db->addLimitOffset($sql, array('limit' => 1)); try { $modseq = $this->_db->selectValue($sql); } catch (Horde_Db_Exception $e) { throw new Horde_History_Exception($e); } if (is_null($modseq) || $modseq === false) { try { $modseq = $this->_db->selectValue('SELECT MAX(history_modseq) FROM horde_histories_modseq'); } catch (Horde_Db_Exception $e) { throw new Horde_History_Exception($e); } if (!empty($modseq)) { return $modseq; } else { return false; } } return $modseq; }
/** */ public function write($id, $session_data) { if (!$this->_db->isActive()) { $this->_db->reconnect(); $this->_db->beginDbTransaction(); } /* Check if session exists. */ try { $exists = $this->_db->selectValue(sprintf('SELECT 1 FROM %s WHERE session_id = ?', $this->_params['table']), array($id)); } catch (Horde_Db_Exception $e) { return false; } /* Update or insert session data. */ $values = array('session_data' => new Horde_Db_Value_Binary($session_data), 'session_lastmodified' => time()); try { if ($exists) { $this->_db->updateBlob($this->_params['table'], $values, array('session_id = ?', array($id))); } else { $this->_db->insertBlob($this->_params['table'], array_merge(array('session_id' => $id), $values)); } $this->_db->commitDbTransaction(); } catch (Horde_Db_Exception $e) { try { $this->_db->rollbackDbTransaction(); } catch (Horde_Db_Exception $e) { } return false; } return true; }
/** * @TODO * * @param <type> $clientID * @param <type> $enterDescription * @param string $exportID * @return <type> */ public function updateClientSettings($clientID, $enterDescription = 1, $exportID = null) { if (empty($exportID)) { $exportID = null; } $sql = 'SELECT clientjob_id FROM hermes_clientjobs WHERE clientjob_id = ?'; $values = array($clientID); if ($this->_db->selectValue($sql, $values) !== $clientID) { $sql = 'INSERT INTO hermes_clientjobs (clientjob_id,' . ' clientjob_enterdescription, clientjob_exportid)' . ' VALUES (?, ?, ?)'; $values = array($clientID, (int) $enterDescription, $this->_convertToDriver($exportID)); try { return $this->_db->insert($sql, $values); } catch (Horde_Db_Exception $e) { throw new Hermes_Exception($e); } } else { $sql = 'UPDATE hermes_clientjobs SET' . ' clientjob_exportid = ?, clientjob_enterdescription = ?' . ' WHERE clientjob_id = ?'; $values = array($this->_convertToDriver($exportID), (int) $enterDescription, $clientID); try { return $this->_db->update($sql, $values); } catch (Horde_Db_Exception $e) { throw new Hermes_Exception($e); } } }
/** */ public function exists($key, $lifetime = 0) { $okey = $key; $key = hash('md5', $key); /* Build SQL query. */ $query = 'SELECT 1 FROM ' . $this->_params['table'] . ' WHERE cache_id = ?'; $values = array($key); // 0 lifetime checks for objects which have no expiration if ($lifetime != 0) { $query .= ' AND cache_timestamp >= ?'; $values[] = time() - $lifetime; } try { $result = $this->_db->selectValue($query, $values); } catch (Horde_Db_Exception $e) { return false; } $timestamp = time(); if (empty($result)) { if ($this->_logger) { $this->_logger->log(sprintf('Cache exists() miss: %s (Id %s newer than %d)', $okey, $key, $timestamp), 'DEBUG'); } return false; } if ($this->_logger) { $this->_logger->log(sprintf('Cache exists() hit: %s (Id %s newer than %d)', $okey, $key, $timestamp), 'DEBUG'); } return true; }
/** */ public function write($id, $session_data) { if (!$this->_db->isActive()) { $this->_db->reconnect(); $this->_db->beginDbTransaction(); } /* Check if session exists. */ try { $exists = $this->_db->selectValue(sprintf('SELECT 1 FROM %s WHERE session_id = ?', $this->_params['table']), array($id)); } catch (Horde_Db_Exception $e) { return false; } /* Update or insert session data. */ $session_data = new Horde_Db_Value_Binary($session_data); try { if ($exists) { $query = sprintf('UPDATE %s ' . 'SET session_data = ?, session_lastmodified = ? ' . 'WHERE session_id = ?', $this->_params['table']); $values = array($session_data, time(), $id); $this->_db->update($query, $values); } else { $query = sprintf('INSERT INTO %s ' . '(session_id, session_data, session_lastmodified) ' . 'VALUES (?, ?, ?)', $this->_params['table']); $values = array($id, $session_data, time()); $this->_db->insert($query, $values); } $this->_db->commitDbTransaction(); } catch (Horde_Db_Exception $e) { try { $this->_db->rollbackDbTransaction(); } catch (Horde_Db_Exception $e) { } return false; } return true; }
/** * Returns an Amavisd-new policy for storage and retrieval. * * @return string The results of the of the policy lookup. Can be the ID * of the policy, false if not found. * @throws Sam_Exception */ protected function _lookupPolicyID() { try { return $this->_db->selectValue(sprintf('SELECT %s FROM %s WHERE %s = ?', $this->_mapAttributeToField('policies', 'id'), $this->_mapNameToTable('policies'), $this->_mapAttributeToField('policies', 'name')), array($this->_user)); } catch (Horde_Db_Exception $e) { return false; } }
/** * Returns an interface name from a stored collection ID map. * * @param string $external An external collection ID. * * @return string The collection's application. * * @throws Horde_Dav_Exception */ public function getCollectionInterface($external) { try { return $this->_db->selectValue('SELECT id_interface FROM horde_dav_collections ' . 'WHERE id_external = ?', array($external)); } catch (Horde_Db_Exception $e) { throw new Horde_Dav_Exception($e); } }
/** * Returns a group name. * * @param mixed $gid A group ID. * * @return string The group's name. * @throws Horde_Group_Exception */ public function getName($gid) { try { return $this->_db->selectValue('SELECT group_name FROM horde_groups WHERE group_uid = ?', array($gid)); } catch (Horde_Db_Exception $e) { throw new Horde_Group_Exception($e); } }
/** * Returns whether an alarm is snoozed. * * @param string $id The alarm's unique id. * @param string $user The alarm's user * @param Horde_Date $time The time when the alarm may be snoozed. * * @return boolean True if the alarm is snoozed. * @throws Horde_Alarm_Exception */ protected function _isSnoozed($id, $user, Horde_Date $time) { $query = sprintf('SELECT 1 FROM %s WHERE alarm_id = ? AND %s AND (alarm_dismissed = 1 OR (alarm_snooze IS NOT NULL AND alarm_snooze >= ?))', $this->_params['table'], !empty($user) ? 'alarm_uid = ?' : '(alarm_uid = ? OR alarm_uid IS NULL)'); try { return $this->_db->selectValue($query, array($id, $user, $time->setTimezone('UTC')->format(Horde_Date::DATE_DEFAULT))); } catch (Horde_Db_Exception $e) { throw new Horde_Alarm_Exception(Horde_Alarm_Translation::t("Server error when querying database.")); } }
/** * Gets the next field order position within a form. * * @param integer $form_id * * @return integer * @throws Ulaform_Exception */ protected function _nextFieldOrder($form_id) { $sql = 'SELECT MAX(field_order) FROM ulaform_fields WHERE form_id = ?'; try { return $this->_db->selectValue($sql, array($form_id)) + 1; } catch (Horde_Db_Exception $e) { throw new Ulaform_Exception($e->getMessage); } }
/** * @param string $mailbox * * @return string UID from base table. */ protected function _getUid($mailbox) { $query = $this->_baseSql($mailbox); $query[0] = 'SELECT d.messageid ' . $query[0]; try { return $this->_db->selectValue($query[0], $query[1]); } catch (Horde_Db_Exception $e) { return null; } }
/** * Returns the count of all shares that $userid has access to. * * @param string $userid The userid of the user to check access for. * @param integer $perm The level of permissions required. * @param mixed $attributes Restrict the shares counted to those * matching $attributes. An array of * attribute/values pairs or a share owner * username. * @param mixed $parent The share to start searching from * (Horde_Share_Object, share_id, or null) * @param boolean $allLevels Return all levels, or just the direct * children of $parent? * * @return integer Number of shares the user has access to. * @throws Horde_Share_Exception */ public function countShares($userid, $perm = Horde_Perms::SHOW, $attributes = null, $parent = null, $allLevels = true) { $query = 'SELECT COUNT(DISTINCT s.share_id) ' . $this->getShareCriteria($userid, $perm, $attributes, $parent, $allLevels); try { $this->_db->selectValue($query); } catch (Horde_Db_Exception $e) { throw new Horde_Share_Exception($e); } return $this->_db->selectValue($query); }
/** * Stores user preferences and default values in the backend. * * @param boolean $defaults Whether to store the global defaults instead * of user options. * * @throws Sam_Exception */ public function store($defaults = false) { if ($defaults) { $store = $this->_defaults; $user = $this->_params['global_user']; } else { $store = $this->_options; $user = $this->_user; } foreach ($store as $attribute => $value) { $option = $this->_mapAttributeToOption($attribute); /* Delete the option if it is the same as the default */ if (!$defaults && isset($this->_defaults[$attribute]) && $this->_defaults[$attribute] === $value) { try { $this->_db->delete('DELETE FROM ' . $this->_params['table'] . ' WHERE username = ? AND preference = ?', array($user, $option)); } catch (Horde_Db_Exception $e) { throw new Sam_Exception($e); } continue; } if (is_array($value)) { try { $this->_db->delete('DELETE FROM ' . $this->_params['table'] . ' WHERE username = ? AND preference = ?', array($user, $option)); } catch (Horde_Db_Exception $e) { throw new Sam_Exception($e); } foreach ($value as $address) { /* Don't save email addresses already in defaults. */ if (!$defaults && isset($this->_defaults[$attribute]) && (is_array($this->_defaults[$attribute]) && in_array($address, $this->_defaults[$attribute]) || $this->_defaults[$attribute] === $address)) { continue; } try { $this->_db->insert('INSERT INTO ' . $this->_params['table'] . ' (username, preference, value)' . ' VALUES (?, ?, ?)', array($user, $option, $address)); } catch (Horde_Db_Exception $e) { throw new Sam_Exception($e); } } } else { try { $result = $this->_db->selectValue('SELECT 1 FROM ' . $this->_params['table'] . ' WHERE username = ? AND preference = ?', array($user, $option)); } catch (Horde_Db_Exception $e) { throw new Sam_Exception($e); } try { if (!$result) { $this->_db->insert('INSERT INTO ' . $this->_params['table'] . ' (username, preference, value)' . ' VALUES (?, ?, ?)', array($user, $option, $value)); } else { $this->_db->insert('UPDATE ' . $this->_params['table'] . ' SET value = ?' . ' WHERE username = ? AND preference = ?', array($value, $user, $option)); } } catch (Horde_Db_Exception $e) { throw new Sam_Exception($e); } } } }
/** * Check for the (rare) possibility of a synckey collision between * collections. * * @param string $guid The GUID portion of the synckey to check. * * @return boolean true if there was a collision. */ protected function _checkCollision($guid) { $sql = 'SELECT COUNT(sync_key) FROM ' . $this->_syncStateTable . ' WHERE sync_devid = ? AND sync_folderid != ? AND sync_key LIKE ?'; $values = array($this->_deviceInfo->id, $this->_collection['id'], '{' . $guid . '}%'); try { return (bool) $this->_db->selectValue($sql, $values); } catch (Horde_Db_Exception $e) { $this->_logger->err(sprintf('[%s] %s', $this->_procid, $e->getMessage())); throw new Horde_ActiveSync_Exception($e); } }
/** * Does the token exist? * * @param string $tokenID Token ID. * * @return boolean True if the token exists. * @throws Horde_Token_Exception */ public function exists($tokenID) { /* Build SQL query. */ $query = 'SELECT token_id FROM ' . $this->_params['table'] . ' WHERE token_address = ? AND token_id = ?'; $values = array($this->_encodeRemoteAddress(), $tokenID); try { return $this->_db->selectValue($query, $values); } catch (Horde_Db_Exception $e) { return false; } }
/** * Returns the number of events in the current calendar. * * @return integer The number of events. * @throws Kronolith_Exception */ public function countEvents() { $query = 'SELECT count(*) FROM kronolith_events WHERE calendar_id = ?'; /* Run the query. */ try { $result = $this->_db->selectValue($query, array($this->calendar)); } catch (Horde_Db_Exception $e) { throw new Kronolith_Exception($e); } return $result; }
/** * Checks if a userId exists in the system. * * @param string $userId User ID for which to check * * @return boolean Whether or not the userId already exists. */ public function exists($userId) { /* Build the SQL query. */ $query = sprintf('SELECT 1 FROM %s WHERE %s = ?', $this->_params['table'], $this->_params['username_field']); $values = array($userId); try { return (bool) $this->_db->selectValue($query, $values); } catch (Horde_Db_Exception $e) { return false; } }
/** * Search for a user's free/busy information. * * @param string $email The email address to lookup * @param boolean $private_only (optional) Only return free/busy * information owned by this used. * * @return Horde_Icalendar_Vfreebusy * @throws Kronolith_Exception */ public function search($email, $private_only = false) { /* Build the SQL query. */ $query = sprintf('SELECT vfb_serialized FROM %s WHERE vfb_email = ? AND (vfb_owner = ?', $this->_params['table']); $values = array($email, $this->_user); if ($private_only) { $query .= ')'; } else { $query .= " OR vfb_owner = '')"; } /* Execute the query. */ try { $result = $this->_db->selectValue($query, $values); if (empty($result)) { throw new Horde_Exception_NotFound(); } return Horde_Serialize::unserialize($result, Horde_Serialize::BASIC); } catch (Horde_Db_Exception $e) { throw new Kronolith_Exception($e); } }
/** * Check for the existence of ANY entries in the map table for this device * and user. * * An extra database query for each sync, but the payoff is that we avoid * having to stat every message change we send to the client if there are no * client generated changes for this sync period. * * @return boolean * @throws Horde_ActiveSync_Exception */ protected function _havePIMChanges() { // No benefit from making this extra query for email collections. if ($this->_collection['class'] == Horde_ActiveSync::CLASS_EMAIL) { return true; } $sql = 'SELECT COUNT(*) FROM ' . $this->_syncMapTable . ' WHERE sync_devid = ? AND sync_user = ? AND sync_folderid = ?'; try { return (bool) $this->_db->selectValue($sql, array($this->_deviceInfo->id, $this->_deviceInfo->user, $this->_collection['serverid'])); } catch (Horde_Db_Exception $e) { throw new Horde_ActiveSync_Exception($e); } }
/** * Returns a list of parent permissions. * * @param string $child The name of the child to retrieve parents for. * * @return array A hash with all parents in a tree format. * @throws Horde_Perms_Exception */ public function getParents($child) { $query = 'SELECT perm_parents FROM ' . $this->_params['table'] . ' WHERE perm_name = ?'; try { $result = $this->_db->selectValue($query, array($child)); } catch (Horde_Db_Exception $e) { throw new Horde_Perms_Exception($e); } if (empty($result)) { throw new Horde_Perms_Exception('Does not exist', Horde_Perms_Exception::NOT_EXIST); } return $this->_getParents($result); }
/** */ public function numberOfRecipients($hours, $user = false) { /* Build the SQL query. */ $query = sprintf('SELECT COUNT(*) FROM %s WHERE sentmail_ts > ? AND sentmail_success = 1', $this->_params['table']); if ($user) { $query .= sprintf(' AND sentmail_who = %s', $this->_db->quote($GLOBALS['registry']->getAuth())); } /* Execute the query. */ try { return $this->_db->selectValue($query, array(time() - $hours * 3600)); } catch (Horde_Db_Exception $e) { return 0; } }
/** * Ensure the style hash is recorded in the database. * * @param string $hash The hash to record. */ public function ensureHash($hash) { $query = 'SELECT COUNT(*) FROM ansel_hashes WHERE style_hash = ?'; try { $results = $this->_db->selectValue($query, array($hash)); } catch (Horde_Db_Exception $e) { throw new Ansel_Exception($e); } if (!$results) { try { $this->_db->insert('INSERT INTO ansel_hashes (style_hash) VALUES(?)', array($hash)); } catch (Horde_Db_Exception $e) { throw new Ansel_Exception($e); } } }
/** */ public function store($scope_ob) { if (!$this->_db->isActive()) { $this->_db->reconnect(); } $charset = $this->_db->getOption('charset'); // For each preference, check for an existing table row and // update it if it's there, or create a new one if it's not. foreach ($scope_ob->getDirty() as $name) { $value = $scope_ob->get($name); if (is_null($value)) { $this->remove($scope_ob->scope, $name); } else { $values = array($this->_params['user'], $name, $scope_ob->scope); // Does a row already exist for this preference? $query = 'SELECT 1 FROM ' . $this->_params['table'] . ' WHERE pref_uid = ? AND pref_name = ?' . ' AND pref_scope = ?'; try { $check = $this->_db->selectValue($query, $values); } catch (Horde_Db_Exception $e) { throw new Horde_Prefs_Exception($e); } /* Driver has no support for storing locked status. */ $value = Horde_String::convertCharset($value, 'UTF-8', $charset); $value = new Horde_Db_Value_Binary($value); if (empty($check)) { // Insert a new row. $query = 'INSERT INTO ' . $this->_params['table'] . ' ' . '(pref_uid, pref_scope, pref_name, pref_value) VALUES' . '(?, ?, ?, ?)'; $values = array($this->_params['user'], $scope_ob->scope, $name, $value); try { $this->_db->insert($query, $values); } catch (Horde_Db_Exception $e) { throw new Horde_Prefs_Exception($e); } } else { // Update the existing row. $query = 'UPDATE ' . $this->_params['table'] . ' SET pref_value = ?' . ' WHERE pref_uid = ?' . ' AND pref_name = ?' . ' AND pref_scope = ?'; $values = array($value, $this->_params['user'], $name, $scope_ob->scope); try { $this->_db->update($query, $values); } catch (Horde_Db_Exception $e) { throw new Horde_Prefs_Exception($e); } } } } }
/** * Searches the SQL database with the given criteria and returns a * filtered list of results. If the criteria parameter is an empty array, * all records will be returned. * * @param array $criteria Array containing the search criteria. * @param array $fields List of fields to return. * @param array $blobFields TODO * @param array $appendWhere An additional where clause to append. * Array should contain 'sql' and 'params' * params are used as bind parameters. * @param boolean $count_only Only return the count of matching entries, * not the entries themselves. * * @return mixed array|integer Hash containing the search results or the * count of matching entries. * @throws Turba_Exception */ protected function _internalSearch(array $criteria, array $fields, $blobFields = array(), $appendWhere = array(), $count_only = false) { /* Build the WHERE clause. */ $where = ''; $values = array(); if (count($criteria) || !empty($this->_params['filter'])) { foreach ($criteria as $key => $vals) { if ($key == 'OR' || $key == 'AND') { if (!empty($where)) { $where .= ' ' . $key . ' '; } $binds = $this->_buildSearchQuery($key, $vals); $where .= '(' . $binds[0] . ')'; $values += $binds[1]; } } $where = ' WHERE ' . $where; if (count($criteria) && !empty($this->_params['filter'])) { $where .= ' AND '; } if (!empty($this->_params['filter'])) { $where .= $this->_params['filter']; } if (count($appendWhere)) { $where .= ' AND ' . $appendWhere['sql']; $values = array_merge($values, $appendWhere['params']); } } elseif (count($appendWhere)) { $where = ' WHERE ' . $appendWhere['sql']; $values = array_merge($values, $appendWhere['params']); } /* Build up the full query. */ try { if ($count_only) { return $this->_db->selectValue('SELECT COUNT(*) FROM ' . $this->_params['table'] . $where, $values); } return $this->_parseRead($blobFields, $this->_db->selectAll('SELECT ' . implode(', ', $fields) . ' FROM ' . $this->_params['table'] . $where, $values)); } catch (Horde_Db_Exception $e) { throw new Turba_Exception(_("Server error when performing search.")); } }
/** */ public function removeVersion($pagename, $version) { $values = array($this->_convertToDriver($pagename), (int) $version); /* We need to know if we're deleting the current version. */ try { $result = $this->_db->selectValue('SELECT 1 FROM ' . $this->_params['table'] . ' WHERE page_name = ? AND page_version = ?', $values); } catch (Horde_Db_Exception $e) { $result = false; } if (!$result) { /* Removing a historical revision - we can just slice it out of the * history table. $values is unchanged. */ try { $this->_db->delete('DELETE FROM ' . $this->_params['historytable'] . ' WHERE page_name = ? and page_version = ?', $values); } catch (Horde_Db_Exception $e) { throw new Wicked_Exception($e); } return; } /* We're deleting the current version. Have to promote the next-most * revision from the history table. */ try { $query = 'SELECT * FROM ' . $this->_params['historytable'] . ' WHERE page_name = ? ORDER BY page_version DESC'; $query = $this->_db->addLimitOffset($query, array('limit' => 1)); $revision = $this->_db->selectOne($query, array($this->_convertToDriver($pagename))); /* Replace the current version of the page with the version being * promoted. */ $this->_db->beginDbTransaction(); $this->_db->update('UPDATE ' . $this->_params['table'] . ' SET' . ' page_text = ?, page_version = ?,' . ' version_created = ?, change_author = ?, change_log = ?' . ' WHERE page_name = ?', array($revision['page_text'], (int) $revision['page_version'], (int) $revision['version_created'], $revision['change_author'], $revision['change_log'], $this->_convertToDriver($pagename))); /* Finally, remove the version that we promoted from the history * table. */ $this->_db->delete('DELETE FROM ' . $this->_params['historytable'] . ' WHERE page_name = ? and page_version = ?', array($this->_convertToDriver($pagename), (int) $revision['page_version'])); $this->_db->commitDbTransaction(); } catch (Horde_Db_Exception $e) { $this->_db->rollbackDbTransaction(); throw new Wicked_Exception($e); } }
/** * 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 a single value from a record * * @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 string * @throws Horde_Db_Exception */ public function selectValue($sql, $arg1 = null, $arg2 = null) { $result = $this->_read->selectValue($sql, $arg1, $arg2); $this->_lastQuery = $this->_read->getLastQuery(); return $result; }