Returns an array of the values of the first column in a select:
selectValues("SELECT id FROM companies LIMIT 3") => [1,2,3]
/**
* Removes a permission from the permissions system permanently.
*
* @param Horde_Perms_Permission_Sql $perm The permission to
* remove.
* @param boolean $force Force to remove every
* child.
*
* @return boolean True if permission was deleted.
* @throws Horde_Perms_Exception
*/
public function removePermission(Horde_Perms_Permission $perm, $force = false)
{
$name = $perm->getName();
$this->_cache->expire('perm_sql_' . $this->_cacheVersion . $name);
$this->_cache->expire('perm_sql_exists_' . $this->_cacheVersion . $name);
$query = 'DELETE FROM ' . $this->_params['table'] . ' WHERE perm_name = ?';
try {
$result = $this->_db->delete($query, array($name));
} catch (Horde_Db_Exception $e) {
throw new Horde_Perms_Exception($e);
}
if (!$force) {
return (bool) $result;
}
/* Need to expire cache for all sub-permissions. */
try {
$sub = $this->_db->selectValues('SELECT perm_name FROM ' . $this->_params['table'] . ' WHERE perm_name LIKE ?', array($name . ':%'));
foreach ($sub as $val) {
$this->_cache->expire('perm_sql_' . $this->_cacheVersion . $val);
$this->_cache->expire('perm_sql_exists_' . $this->_cacheVersion . $val);
}
} catch (Horde_Db_Exception $e) {
}
$query = 'DELETE FROM ' . $this->_params['table'] . ' WHERE perm_name LIKE ?';
try {
return (bool) $this->_db->delete($query, array($name . ':%'));
} catch (Horde_Db_Exception $e) {
throw new Horde_Perms_Exception($e);
}
}
/**
*/
public function setMetaData($mailbox, $data)
{
if (!($uid = $this->_getUid($mailbox))) {
$uid = $this->_createUid($mailbox);
}
$query = sprintf('SELECT field FROM %s where messageid = ?', self::MD_TABLE);
$values = array($uid);
try {
$fields = $this->_db->selectValues($query, $values);
} catch (Horde_Db_Exception $e) {
return;
}
foreach ($data as $key => $val) {
$val = new Horde_Db_Value_Binary($key == 'uidvalid' ? $val : serialize($val));
if (in_array($key, $fields)) {
/* Update */
try {
$this->_db->update(sprintf('UPDATE %s SET data = ? WHERE field = ? AND messageid = ?', self::MD_TABLE), array($val, $key, $uid));
} catch (Horde_Db_Exception $e) {
}
} else {
/* Insert */
try {
$this->_db->insert(sprintf('INSERT INTO %s (data, field, messageid) VALUES (?, ?, ?)', self::MD_TABLE), array($val, $key, $uid));
} catch (Horde_Db_Exception $e) {
}
}
}
}
/**
* Deletes all contacts from a specific address book.
*
* @param string $sourceName The source to remove all contacts from.
*
* @return array An array of UIDs
* @throws Turba_Exception
*/
protected function _deleteAll($sourceName = null)
{
if (!$GLOBALS['registry']->getAuth()) {
throw new Turba_Exception('Permission denied');
}
/* Get owner id */
$values = empty($sourceName) ? array($GLOBALS['registry']->getAuth()) : array($sourceName);
if (empty($this->map['__owner'])) {
throw new Turba_Exception(_("Unable to find __owner field. Cannot delete."));
}
$owner_field = $this->map['__owner'];
/* Need a list of UIDs so we can notify History */
$query = sprintf('SELECT %s FROM %s WHERE %s = ?', $this->map['__uid'], $this->_params['table'], $owner_field);
try {
$ids = $this->_db->selectValues($query, $values);
} catch (Horde_Db_Exception $e) {
throw new Turba_Exception(_("Server error when deleting data."));
}
/* Do the deletion */
$query = sprintf('DELETE FROM %s WHERE %s = ?', $this->_params['table'], $owner_field);
try {
$this->_db->delete($query, $values);
} catch (Horde_Db_Exception $e) {
throw new Turba_Exception(_("Server error when deleting data."));
}
return $ids;
}
/**
*/
public function setMetaData($mailbox, $data)
{
if (!($uid = $this->_getUid($mailbox))) {
$uid = $this->_createUid($mailbox);
}
$query = sprintf('SELECT field FROM %s where messageid = ?', self::MD_TABLE);
$values = array($uid);
try {
$fields = $this->_db->selectValues($query, $values);
} catch (Horde_Db_Exception $e) {
return;
}
foreach ($data as $key => $val) {
$val = new Horde_Db_Value_Binary($key == 'uidvalid' ? $val : serialize($val));
if (in_array($key, $fields)) {
/* Update */
try {
$this->_db->updateBlob(self::MD_TABLE, array('data' => $val), array('field = ? AND messageid = ?', array($key, $uid)));
} catch (Horde_Db_Exception $e) {
}
} else {
/* Insert */
try {
$this->_db->insertBlob(self::MD_TABLE, array('data' => $val, 'field' => $key, 'messageid' => $uid));
} catch (Horde_Db_Exception $e) {
}
}
}
}
/**
*/
public function favouriteRecipients($limit, $filter = null)
{
/* Build the SQL query. */
$where = '';
if (!empty($filter)) {
$filter = array_map(array($this->_db, 'quote'), $filter);
$where = sprintf(' AND sentmail_action in (%s)', implode(', ', $filter));
}
$query = sprintf('SELECT sentmail_recipient, count(*) AS sentmail_count FROM %s WHERE sentmail_who = %s AND sentmail_success = 1%s GROUP BY sentmail_recipient ORDER BY sentmail_count DESC', $this->_params['table'], $this->_db->quote($GLOBALS['registry']->getAuth()), $where);
/* Execute the query. */
try {
$query = $this->_db->addLimitOffset($query, array('limit' => $limit));
return $this->_db->selectValues($query);
} catch (Horde_Db_Exception $e) {
return array();
}
}
/**
* Returns a list of users in a group.
*
* @param mixed $gid A group ID.
*
* @return array List of group users.
* @throws Horde_Group_Exception
*/
public function listUsers($gid)
{
try {
return $this->_db->selectValues('SELECT user_uid FROM horde_groups_members WHERE group_uid = ? ORDER BY user_uid ASC', array($gid));
} catch (Horde_Db_Exception $e) {
throw new Horde_Group_Exception($e);
}
}
/**
* Get a list of all known styleHashes.
*
* @return array An array of style hashes.
*/
public function getHashes()
{
try {
return $this->_db->selectValues('SELECT style_hash FROM ansel_hashes');
} catch (Horde_Db_Exception $e) {
throw new Ansel_Exception($e);
}
}
/**
* Lists all available scopes.
*
* @return array The list of scopes stored in the backend.
*/
public function listScopes()
{
$query = 'SELECT ' . $this->_db->distinct('pref_scope') . ' FROM ' . $this->_params['table'];
try {
return $this->_db->selectValues($query);
} catch (Horde_Db_Exception $e) {
throw new Horde_Prefs_Exception($e);
}
}
/**
* Filters a list of events to return only those that belong to certain
* calendars.
*
* @param array $uids A list of event UIDs.
* @param array $calendar A list of calendar IDs.
*
* @return array Event UIDs filtered by calendar IDs.
* @throws Kronolith_Exception
*/
public function filterEventsByCalendar($uids, $calendar)
{
$sql = 'SELECT event_uid FROM kronolith_events WHERE calendar_id IN (' . str_repeat('?, ', count($calendar) - 1) . '?) ' . 'AND event_uid IN (' . str_repeat('?,', count($uids) - 1) . '?)';
try {
$result = $this->_db->selectValues($sql, array_merge($calendar, $uids));
} catch (Horde_Db_Exception $e) {
throw new Kronolith_Exception($e);
}
return $result;
}
/**
*/
public function getSessionIDs()
{
$this->open();
/* Build the SQL query. */
$query = sprintf('SELECT session_id FROM %s' . ' WHERE session_lastmodified >= ?', $this->_params['table']);
$values = array(time() - ini_get('session.gc_maxlifetime'));
/* Execute the query. */
try {
return $this->_db->selectValues($query, $values);
} catch (Horde_Db_Exception $e) {
return array();
}
}
/**
* List all users in the system.
*
* @param boolean $sort Sort the users?
*
* @return array The array of userIds.
* @throws Horde_Auth_Exception
*/
public function listUsers($sort = false)
{
/* Build the SQL query. */
$query = sprintf('SELECT %s FROM %s', $this->_params['username_field'], $this->_params['table']);
if ($sort) {
$query .= sprintf(' ORDER BY %s ASC', $this->_params['username_field']);
}
try {
return $this->_db->selectValues($query);
} catch (Horde_Db_Exception $e) {
throw new Horde_Auth_Exception($e);
}
}
/**
* Deletes all notes from the current notepad.
*
* @return array An array of uids that have been removed.
* @throws Mnemo_Exception
*/
protected function _deleteAll()
{
// Get list of notes we are removing so we can tell history about it.
$query = sprintf('SELECT memo_uid FROM %s WHERE memo_owner = ?', $this->_table);
$values = array($this->_notepad);
try {
$ids = $this->_db->selectValues($query, $values);
} catch (Horde_Db_Exception $e) {
throw new Mnemo_Exception($e->getMessage());
}
$query = sprintf('DELETE FROM %s WHERE memo_owner = ?', $this->_table);
try {
$this->_db->delete($query, $values);
} catch (Horde_Db_Exception $e) {
throw new Mnemo_Exception($e->getMessage());
}
return $ids;
}
/**
* Return a list of users who have shares with the given permissions
* for the current user.
*
* @param integer $perm The level of permissions required.
* @param mixed $parent The parent share to start looking in.
* (Horde_Share_Object, share_id, or null)
* @param boolean $allLevels Return all levels, or just the direct
* children of $parent? Defaults to all levels.
* @param integer $from The user to start listing at.
* @param integer $count The number of users to return.
*
* @return array List of users.
* @throws Horde_Share_Exception
*/
public function listOwners($perm = Horde_Perms::SHOW, $parent = null, $allLevels = true, $from = 0, $count = 0)
{
$sql = 'SELECT DISTINCT(s.share_owner) ' . $this->getShareCriteria($this->_user, $perm, null, $parent, $allLevels);
if ($count) {
$sql = $this->_db->addLimitOffset($sql, array('limit' => $count, 'offset' => $from));
}
try {
$allowners = $this->_db->selectValues($sql);
} catch (Horde_Db_Exception $e) {
throw new Horde_Share_Exception($e);
}
$owners = array();
foreach ($allowners as $owner) {
if ($this->countShares($this->_user, $perm, $owner, $parent, $allLevels)) {
$owners[] = $owner;
}
}
return $owners;
}
/**
* Deletes all tasks from the backend.
*
* @return array An array of uids that have been removed.
* @throws Nag_Exception
*/
protected function _deleteAll()
{
// Get the list of ids so we can notify History.
$query = sprintf('SELECT task_uid FROM %s WHERE task_owner = ?', $this->_params['table']);
$values = array($this->_tasklist);
try {
$ids = $this->_db->selectValues($query, $values);
} catch (Horde_Db_Exception $e) {
throw new Nag_Exception($e->getMessage());
}
// Deletion
$query = sprintf('DELETE FROM %s WHERE task_owner = ?', $this->_params['table']);
try {
$this->_db->delete($query, $values);
} catch (Horde_Db_Exception $e) {
throw new Nag_Exception($e->getMessage());
}
return $ids;
}
/**
* 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;
}
/**
* Remove all occurrences of a specific tag from an object regardless of
* the username who tagged the object originally.
*
* @param mixed $obejctId The object identifier @see Content_Tagger::tag()
* @param mixed $tags The tags to remove. @see Content_Tagger::tag()
*
* @return void
*/
public function removeTagFromObject($objectId, $tags)
{
$objectId = $this->_ensureObject($objectId);
if (!is_array($tags)) {
$tags = array($tags);
}
foreach ($this->ensureTags($tags) as $tagId) {
// Get the users who have tagged this so we can update the stats
$users = $this->_db->selectValues('SELECT user_id, tag_id FROM ' . $this->_t('tagged') . ' WHERE object_id = ? AND tag_id = ?', array($objectId, $tagId));
// Delete the tags
if ($this->_db->delete('DELETE FROM ' . $this->_t('tagged') . ' WHERE object_id = ? AND tag_id = ?', array($objectId, $tagId))) {
// Update the stats
$this->_db->update('UPDATE ' . $this->_t('tag_stats') . ' SET count = count - ' . count($users) . ' WHERE tag_id = ?', array($tagId));
$this->_db->update('UPDATE ' . $this->_t('user_tag_stats') . ' SET count = count - 1 WHERE user_id IN(' . str_repeat('?, ', count($users) - 1) . '?) AND tag_id = ?', array_merge($users, array($tagId)));
// Housekeeping
$this->_db->delete('DELETE FROM ' . $this->_t('tag_stats') . ' WHERE count = 0');
$this->_db->delete('DELETE FROM ' . $this->_t('user_tag_stats') . ' WHERE count = 0');
}
}
}
/**
* Deletes all contacts from a specific address book.
*
* @param string $sourceName The source to remove all contacts from.
*
* @return array An array of UIDs
* @throws Turba_Exception
*/
protected function _deleteAll($sourceName = null)
{
if (!$GLOBALS['registry']->getAuth()) {
throw new Turba_Exception('Permission denied');
}
/* Get owner id */
$values = empty($sourceName) ? array($GLOBALS['registry']->getAuth()) : array($sourceName);
/* Need a list of UIDs so we can notify History */
$query = 'SELECT ' . $this->map['__uid'] . ' FROM ' . $this->_params['table'] . ' WHERE owner_id = ?';
try {
$ids = $this->_db->selectValues($query, $values);
} catch (Horde_Db_Exception $e) {
throw new Turba_Exception(_("Server error when deleting data."));
}
/* Do the deletion */
$query = 'DELETE FROM ' . $this->_params['table'] . ' WHERE owner_id = ?';
try {
$this->_db->delete($query, $values);
} catch (Horde_Db_Exception $e) {
throw new Turba_Exception(_("Server error when deleting data."));
}
return $ids;
}
/**
* 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 = ?';
$values = array($this->_deviceInfo->id, !empty($this->_collection['id']) ? $this->_collection['id'] : Horde_ActiveSync::CHANGE_TYPE_FOLDERSYNC);
try {
$results = $this->_db->selectAll($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 the values of the first column in a select:
* selectValues("SELECT id FROM companies LIMIT 3") => [1,2,3]
*
* @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 selectValues($sql, $arg1 = null, $arg2 = null)
{
$result = $this->_read->selectValues($sql, $arg1, $arg2);
$this->_lastQuery = $this->_read->getLastQuery();
return $result;
}
