Returns a record hash with the column names as keys and column values
as values.
/**
* Finds out if a username and password is valid.
*
* @param string $user The username to check.
* @param string $oldpass An old password to check.
*
* @throws Passwd_Exception
*/
protected function _lookup($user, $oldpass)
{
/* Only split up username if domain is set in backend configuration. */
if (!empty($this->_params['domain'])) {
list($name, $domain) = explode('@', $user);
} else {
$name = $user;
}
/* Build the SQL query. */
$sql = 'SELECT ' . $this->_params['passwd'] . ' FROM ' . $this->_params['table'] . ' WHERE ' . $this->_params['name'] . ' = ?';
$values = array($name);
if ($this->_params['domain']) {
$sql .= ' AND ' . $this->_params['domain'] . ' = ?';
$values[] = $domain;
}
/* Execute the query. */
try {
$result = $this->_db->selectOne($sql, $values);
} catch (Horde_Db_Exception $e) {
throw new Passwd_Exception($e);
}
if (!is_array($result)) {
throw new Passwd_Exception(_("User not found"));
}
/* Check the passwords match. */
$this->_comparePasswords($result[$this->_params['passwd']], $oldpass);
}
/**
* Get the location of the provided event_id.
*
* @see Kronolith_Geo_Base#getLocation()
* @throws Kronolith_Exception
*/
public function getLocation($event_id)
{
$sql = 'SELECT event_lat as lat, event_lon as lon, event_zoom as zoom FROM kronolith_events_geo WHERE event_id = ?';
try {
return $this->_db->selectOne($sql, array($event_id));
} catch (Horde_Db_Exception $e) {
throw new Kronolith_Exception($e);
}
}
/**
* Return an array of information about the requested lock.
*
* @see Horde_Lock_Base::getLockInfo()
*/
public function getLockInfo($lockid)
{
$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_id = ? AND ' . '(lock_expiry_timestamp >= ? OR lock_expiry_timestamp = ?)';
$values = array($lockid, $now, Horde_Lock::PERMANENT);
try {
return $this->_db->selectOne($sql, $values);
} catch (Horde_Db_Exception $e) {
throw new Horde_Lock_Exception($e);
}
}
/**
* Retrieves user preferences from the backend.
*
* @throws Sam_Exception
*/
public function retrieve()
{
/* Find the user id. */
$userID = $this->_lookupUserID();
/* Find the policy id. */
if ($policyID = $this->_lookupPolicyID()) {
/* Query for SPAM policy. */
try {
$result = $this->_db->selectOne(sprintf('SELECT * FROM %s WHERE %s = ?', $this->_mapNameToTable('policies'), $this->_mapAttributeToField('policies', 'id')), array($policyID));
} catch (Horde_Db_Exception $e) {
throw new Sam_Exception($e);
}
}
/* Loop through elements of the result, retrieving options. */
if (!empty($result)) {
foreach ($result as $field => $value) {
$attribute = $this->_mapFieldToAttribute('policies', $field);
if ($this->hasCapability($attribute) && !is_null($value)) {
$this->_options[$attribute] = $value;
}
}
}
/* 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. */
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])) {
if (!in_array($sender, $this->_options[$list])) {
$this->_options[$list][] = $sender;
}
} else {
$this->_options[$list] = array($sender);
}
}
}
}
/**
* Adds a permission to the permissions system. The permission must first
* be created with newPermission(), and have any initial users added to
* it, before this function is called.
*
* @param Horde_Perms_Permission_Sql $perm The perm object.
*
* @return integer Permission ID in the database.
* @throws Horde_Perms_Exception
*/
public function addPermission(Horde_Perms_Permission $perm)
{
$name = $perm->getName();
if (empty($name)) {
throw new Horde_Perms_Exception('Permission name must be non-empty.');
}
$this->_cache->expire('perm_sql_' . $this->_cacheVersion . $name);
$this->_cache->expire('perm_sql_exists_' . $this->_cacheVersion . $name);
// remove root from the name
$root = Horde_Perms::ROOT . ':';
if (substr($name, 0, strlen($root)) == $root) {
$name = substr($name, strlen($root));
}
// build parents
$parents = '';
if (($pos = strrpos($name, ':')) !== false) {
$parent_name = substr($name, 0, $pos);
$query = 'SELECT perm_id, perm_parents FROM ' . $this->_params['table'] . ' WHERE perm_name = ?';
$result = $this->_db->selectOne($query, array($parent_name));
if (empty($result)) {
throw new Horde_Perms_Exception(Horde_Perms_Translation::t("Trying to create sub permission of non-existent parent permission. Create parent permission(s) first."));
}
$parents = $result['perm_parents'] . ':' . $result['perm_id'];
}
$query = 'INSERT INTO ' . $this->_params['table'] . ' (perm_name, perm_parents) VALUES (?, ?)';
try {
$id = $this->_db->insert($query, array($name, $parents));
} catch (Horde_Db_Exception $e) {
throw new Horde_Perms_Exception($e);
}
$perm->setId($id);
$perm->save();
return $id;
}
/**
* Check that a share id exists in the system.
*
* @param integer $id The share id
*
* @return boolean True if the share exists.
*/
protected function _idExists($id)
{
try {
return (bool) $this->_db->selectOne('SELECT 1 FROM ' . $this->_table . ' WHERE share_id = ?', array($id));
} catch (Horde_Db_Exception $e) {
throw new Horde_Share_Exception($e);
}
}
/**
* Finds out if a username and password is valid.
*
* @param string $userID The userID to check.
* @param string $oldpass An old password to check.
*
* @throws Passwd_Exception
*/
protected function _lookup($user, $oldpass)
{
if (!empty($this->_params['query_lookup'])) {
list($sql, $values) = $this->_parseQuery($this->_params['query_lookup'], $user, $oldpass);
} else {
/* Build the SQL query. */
$sql = 'SELECT ' . $this->_params['pass_col'] . ' FROM ' . $this->_params['table'] . ' WHERE ' . $this->_params['user_col'] . ' = ?';
$values = array($user);
}
/* Run query. */
try {
$result = $this->_db->selectOne($sql, $values);
} catch (Horde_Db_Exception $e) {
throw new Passwd_Exception($e);
}
if (!is_array($result)) {
throw new Passwd_Exception(_("User not found"));
}
/* Check the passwords match. */
$this->_comparePasswords($result[$this->_params['pass_col']], $oldpass);
}
/**
* Returns an alarm hash from the backend.
*
* @param string $id The alarm's unique id.
* @param string $user The alarm's user
*
* @return array An alarm hash.
* @throws Horde_Alarm_Exception
*/
protected function _get($id, $user)
{
$query = sprintf('SELECT alarm_id, alarm_uid, alarm_start, alarm_end, alarm_methods, alarm_params, alarm_title, alarm_text, alarm_snooze, alarm_internal FROM %s WHERE alarm_id = ? AND %s', $this->_params['table'], !empty($user) ? 'alarm_uid = ?' : '(alarm_uid = ? OR alarm_uid IS NULL)');
try {
$alarm = $this->_db->selectOne($query, array($id, $user));
} catch (Horde_Db_Exception $e) {
throw new Horde_Alarm_Exception(Horde_Alarm_Translation::t("Server error when querying database."));
}
if (empty($alarm)) {
throw new Horde_Alarm_Exception('Alarm not found');
}
return $this->_getHash($alarm);
}
/**
* Retrieves one note from the backend by UID.
*
* @param string $uid The UID of the note to retrieve.
* @param string $passphrase A passphrase with which this note was
* supposed to be encrypted.
*
* @return array The array of note attributes.
* @throws Mnemo_Exception
* @throws Horde_Exception_NotFound
*/
public function getByUID($uid, $passphrase = null)
{
$query = 'SELECT * FROM ' . $this->_table . ' WHERE memo_uid = ?';
$values = array($uid);
try {
$row = $this->_db->selectOne($query, $values);
} catch (Horde_Db_Exception $e) {
throw new Mnemo_Exception($e->getMessage());
}
if (!count($row)) {
throw new Horde_Exception_NotFound('Not found');
}
$this->_notepad = $row['memo_owner'];
return $this->_buildNote($row, $passphrase);
}
/**
* Gets the latest entry of $guid
*
* @param string $guid The name of the history entry to retrieve.
* @param boolean $use_ts If false we use the 'modseq' field to determine
* the latest entry. If true we use the timestamp
* instead of modseq to determine the latest entry.
* Note: Only 'modseq' can give a definitive answer.
*
* @return array|boolean The latest history entry, or false if $guid does not exist.
*
* @throws Horde_History_Exception If the input parameters are not of type string.
* @since 2.2.0
*/
public function getLatestEntry($guid, $use_ts = false)
{
$query = 'SELECT * from horde_histories WHERE object_uid = ? ORDER BY ';
if ($use_ts) {
$query .= 'history_ts ';
} else {
$query .= 'history_modseq ';
}
$query .= 'DESC LIMIT 1';
$row = $this->_db->selectOne($query, array($guid));
if (empty($row['history_id'])) {
return false;
}
$log = new Horde_History_Log($guid, array($row));
return $log[0];
}
/**
* Returns all available attributes of a group.
*
* @param mixed $gid A group ID.
*
* @return array The group's date.
* @throws Horde_Group_Exception
* @throws Horde_Exception_NotFound
*/
public function getData($gid)
{
try {
$result = $this->_db->selectOne('SELECT * FROM horde_groups WHERE group_uid = ?', array($gid));
if (!$result) {
throw new Horde_Exception_NotFound('Group with the ID ' . $gid . ' not found');
}
} catch (Horde_Db_Exception $e) {
throw new Horde_Group_Exception($e);
}
$data = array();
foreach ($result as $attribute => $value) {
$data[preg_replace('/^group_/', '', $attribute)] = $value;
}
return $data;
}
/**
* Fetches a forum data.
*
* @param integer $forum_id The ID of the forum to fetch.
*
* @return array The forum hash or a PEAR_Error on failure.
* @throws Horde_Exception_NotFound
* @throws Agora_Exception
*/
public function getForum($forum_id = 0)
{
if (!$forum_id) {
$forum_id = $this->_forum_id;
} elseif ($forum_id instanceof PEAR_Error) {
return $forum_id;
}
// Make the requested forum the current forum
$this->_forum_id = $forum_id;
/* Check if we can read messages in this forum */
if (!$this->hasPermission(Horde_Perms::SHOW, $forum_id)) {
return PEAR::raiseError(sprintf(_("You don't have permission to access messages in forum %s."), $forum_id));
}
$forum = $this->_cache->get('agora_forum_' . $forum_id, $GLOBALS['conf']['cache']['default_lifetime']);
if ($forum) {
return unserialize($forum);
}
$sql = 'SELECT forum_id, forum_name, scope, active, forum_description, ' . 'forum_parent_id, forum_moderated, forum_attachments, ' . 'forum_distribution_address, author, message_count, thread_count ' . 'FROM ' . $this->_forums_table . ' WHERE forum_id = ?';
try {
$forum = $this->_db->selectOne($sql, array($forum_id));
} catch (Horde_Db_Exception $e) {
throw new Agora_Exception($e->getMessage());
}
if (empty($forum)) {
throw new Horde_Exception_NotFound(sprintf(_("Forum %s does not exist."), $forum_id));
}
$forum['forum_name'] = $this->convertFromDriver($forum['forum_name']);
$forum['forum_description'] = $this->convertFromDriver($forum['forum_description']);
$forum['forum_distribution_address'] = $this->convertFromDriver($forum['forum_distribution_address']);
/* Get moderators */
$sql = 'SELECT horde_uid FROM agora_moderators WHERE forum_id = ?';
try {
$moderators = $this->_db->selectValues($sql, array($forum_id));
} catch (Horde_Db_Exception $e) {
throw new Agora_Exception($e->getMessage());
}
if (!empty($moderators)) {
$forum['moderators'] = $moderators;
}
$this->_cache->set('agora_forum_' . $forum_id, serialize($forum));
return $forum;
}
/**
* Returns the image corresponding to the given id.
*
* @param integer $id The image_id of the image to retrieve.
*
* @return Ansel_Image The image object requested..
* @throws Ansel_Exception, Horde_Exception_NotFound
*/
public function &getImage($id)
{
if (isset($this->_images[$id])) {
return $this->_images[$id];
}
$q = 'SELECT ' . $this->_getImageFields() . ' FROM ansel_images WHERE image_id = ?';
try {
$image = $this->_db->selectOne($q, array((int) $id));
} catch (Horde_Db_Exception $e) {
throw new Ansel_Exception($e);
}
if (!$image) {
throw new Horde_Exception_NotFound(_("Photo not found"));
} else {
$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');
$this->_images[$id] = new Ansel_Image($image);
return $this->_images[$id];
}
}
/**
* Fetches all the data specific to the supplied form id.
*
* @param integer $form_id The form id of the form to return.
*
* @return array The form data.
* @throws Horde_Exception_PermissionDenied
* @throws Horde_Exception_NotFound
* @throws Ulaform_Exception
*/
public function getForm($form_id, $permission = Horde_Perms::SHOW)
{
/* Check permissions */
if (!parent::hasPermission($permission, $form_id)) {
throw new Horde_Exception_PermissionDenied(_("You don't have the right permission to access this form."));
}
/* Get the main form data. */
$sql = 'SELECT form_id, user_uid, form_name, form_action, form_params,' . ' form_onsubmit FROM ulaform_forms WHERE form_id = ?';
try {
$form = $this->_db->selectOne($sql, array((int) $form_id));
} catch (Horde_Db_Exception $e) {
throw new Ulaform_Exception($e->getMessage());
}
/* Check if the form exists. */
if (empty($form)) {
throw new Horde_Exception_NotFound(sprintf(_("No such form ID \"%s\"."), $form_id));
}
/* Unserialize the form params. */
$form['form_params'] = Horde_Serialize::unserialize($form['form_params'], Horde_Serialize::UTF7_BASIC);
return $form;
}
/**
* Load the device object.
*
* @param string $devId The device id to obtain
* @param string $user The user to retrieve user-specific device info for
*
* @return Horde_ActiveSync_Device The device object
* @throws Horde_ActiveSync_Exception
*/
public function loadDeviceInfo($devId, $user = null)
{
// See if we already have this device, for this user loaded
if (!empty($this->_deviceInfo) && $this->_deviceInfo->id == $devId && !empty($this->_deviceInfo) && $user == $this->_deviceInfo->user) {
return $this->_deviceInfo;
}
$query = 'SELECT device_type, device_agent, ' . 'device_rwstatus, device_supported, device_properties FROM ' . $this->_syncDeviceTable . ' WHERE device_id = ?';
try {
if (!($device = $this->_db->selectOne($query, array($devId)))) {
throw new Horde_ActiveSync_Exception('Device not found.');
}
$columns = $this->_db->columns($this->_syncDeviceTable);
$device['device_properties'] = $columns['device_properties']->binaryToString($device['device_properties']);
$device['device_supported'] = $columns['device_supported']->binaryToString($device['device_supported']);
} catch (Horde_Db_Exception $e) {
throw new Horde_ActiveSync_Exception($e);
}
if (!empty($user)) {
$query = 'SELECT device_policykey FROM ' . $this->_syncUsersTable . ' WHERE device_id = ? AND device_user = ?';
try {
$duser = $this->_db->selectOne($query, array($devId, $user));
} catch (Horde_Db_Exception $e) {
throw new Horde_ActiveSync_Exception($e);
}
}
$this->_deviceInfo = new Horde_ActiveSync_Device($this);
$this->_deviceInfo->rwstatus = $device['device_rwstatus'];
$this->_deviceInfo->deviceType = $device['device_type'];
$this->_deviceInfo->userAgent = $device['device_agent'];
$this->_deviceInfo->id = $devId;
$this->_deviceInfo->user = $user;
$this->_deviceInfo->supported = unserialize($device['device_supported']);
if (empty($duser)) {
$this->_deviceInfo->policykey = 0;
} else {
$this->_deviceInfo->policykey = empty($duser['device_policykey']) ? 0 : $duser['device_policykey'];
}
$this->_deviceInfo->properties = unserialize($device['device_properties']);
return $this->_deviceInfo;
}
/**
* Find out if a set of login credentials are valid.
*
* @param string $userId The userId to check.
* @param array $credentials The credentials to use.
*
* @throws Horde_Auth_Exception
*/
protected function _authenticate($userId, $credentials)
{
/* Build the SQL query. */
$query = sprintf('SELECT * FROM %s WHERE %s = ?', $this->_params['table'], $this->_params['username_field']);
$values = array($userId);
try {
$row = $this->_db->selectOne($query, $values);
} catch (Horde_Db_Exception $e) {
throw new Horde_Auth_Exception('', Horde_Auth::REASON_FAILED);
}
if (!$row || !$this->_comparePasswords($row[$this->_params['password_field']], $credentials['password'])) {
throw new Horde_Auth_Exception('', Horde_Auth::REASON_BADLOGIN);
}
$now = time();
if (!empty($this->_params['hard_expiration_field']) && !empty($row[$this->_params['hard_expiration_field']]) && $now > $row[$this->_params['hard_expiration_field']]) {
throw new Horde_Auth_Exception('', Horde_Auth::REASON_EXPIRED);
}
if (!empty($this->_params['soft_expiration_field']) && !empty($row[$this->_params['soft_expiration_field']]) && $now > $row[$this->_params['soft_expiration_field']]) {
$this->setCredential('change', true);
$this->setCredential('expire', $now);
}
}
/**
*/
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);
}
}
/**
* @throws Kronolith_Exception
* @throws Horde_Exception_NotFound
*/
public function getEvent($eventId = null)
{
if (!strlen($eventId)) {
return new $this->_eventClass($this);
}
if (isset($this->_cache[$this->calendar][$eventId])) {
return $this->_cache[$this->calendar][$eventId];
}
$query = 'SELECT event_id, event_uid, event_description,' . ' event_location, event_private, event_status, event_attendees,' . ' 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, event_organizer FROM ' . 'kronolith_events WHERE event_id = ? AND calendar_id = ?';
$values = array($eventId, $this->calendar);
try {
$event = $this->_db->selectOne($query, $values);
} catch (Horde_Db_Exception $e) {
throw new Kronolith_Exception($e);
}
if ($event) {
/* Convert TEXT/CLOB fields. */
$event = $this->convertBlobs($event);
$this->_cache[$this->calendar][$eventId] = new $this->_eventClass($this, $event);
return $this->_cache[$this->calendar][$eventId];
}
throw new Horde_Exception_NotFound(_("Event not found"));
}
/**
* Returns a record hash 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 array
* @throws Horde_Db_Exception
*/
public function selectOne($sql, $arg1 = null, $arg2 = null)
{
$result = $this->_read->selectOne($sql, $arg1, $arg2);
$this->_lastQuery = $this->_read->getLastQuery();
return $result;
}
