PHP Horde_Db_Adapter::select方法代码示例

 /**
  * Ensure that an array of users exist in storage. Create any that don't,
  * return user_ids for all.
  *
  * @param array $users  An array of users. Values typed as an integer
  *                        are assumed to already be an user_id.
  *
  * @return array  An array of user_ids.
  */
 public function ensureUsers($users)
 {
     if (!is_array($users)) {
         $users = array($users);
     }
     $userIds = array();
     $userName = array();
     // Anything already typed as an integer is assumed to be a user id.
     foreach ($users as $userIndex => $user) {
         if (is_int($user)) {
             $userIds[$userIndex] = $user;
         } else {
             $userName[$user] = $userIndex;
         }
     }
     // Get the ids for any users that already exist.
     try {
         if (count($userName)) {
             $userName;
             $sql = 'SELECT user_id, user_name FROM ' . $this->_t('users') . ' WHERE user_name IN (' . implode(',', array_map(array($this, 'toDriver'), array_keys($userName))) . ')';
             foreach ($this->_db->select($sql) as $row) {
                 $userIndex = $userName[$row['user_name']];
                 unset($userName[$row['user_name']]);
                 $userIds[$userIndex] = $row['user_id'];
             }
         }
         // Create any users that didn't already exist
         foreach ($userName as $user => $userIndex) {
             $userIds[$userIndex] = $this->_db->insert('INSERT INTO ' . $this->_t('users') . ' (user_name) VALUES (' . $this->toDriver($user) . ')');
         }
     } catch (Horde_Db_Exception $e) {
         throw new Content_Exception($e);
     }
     return $userIds;
 }

 /**
  */
 public function get($mailbox, $uids, $fields, $uidvalid)
 {
     $this->getMetaData($mailbox, $uidvalid, array('uidvalid'));
     $query = $this->_baseSql($mailbox, self::MSG_TABLE);
     $query[0] = 'SELECT t.data, t.msguid ' . $query[0];
     $uid_query = array();
     foreach ($uids as $val) {
         $uid_query[] = 't.msguid = ?';
         $query[1][] = strval($val);
     }
     $query[0] .= ' AND (' . implode(' OR ', $uid_query) . ')';
     $compress = new Horde_Compress_Fast();
     $out = array();
     try {
         $columns = $this->_db->columns(self::MSG_TABLE);
         $res = $this->_db->select($query[0], $query[1]);
         foreach ($res as $row) {
             try {
                 $out[$row['msguid']] = @unserialize($compress->decompress($columns['data']->binaryToString($row['data'])));
             } catch (Exception $e) {
             }
         }
     } catch (Horde_Db_Exception $e) {
     }
     return $out;
 }

 public function getMany($num = 50)
 {
     $tasks = array();
     $values = array();
     $query = 'SELECT * FROM horde_queue_tasks where task_queue = ? ORDER BY task_id LIMIT ?';
     $values[] = $this->_queue;
     $values[] = $num;
     try {
         $rows = $this->_db->select($query, $values);
     } catch (Horde_Db_Exception $e) {
         throw new Horde_Queue_Exception($e);
     }
     $query = 'DELETE FROM horde_queue_tasks WHERE task_id = ?';
     foreach ($rows as $row) {
         $tasks[] = unserialize($row['task_fields']);
         // TODO: Evaluate if a single call for all IDs is faster for
         // various scenarios
         try {
             $this->_db->delete($query, array($row['task_id']));
         } catch (Horde_Db_Exception $e) {
             throw new Horde_Queue_Exception($e);
         }
     }
     return $tasks;
 }

 /**
  * Retrieve an option set from the storage backend.
  *
  * @param boolean $defaults  Whether to retrieve the global defaults
  *                           instead of user options.
  *
  * @return array  Array of option-value pairs.
  * @throws Sam_Exception
  */
 protected function _retrieve($defaults = false)
 {
     $user = $defaults ? $this->_params['global_user'] : $this->_user;
     try {
         $result = $this->_db->select('SELECT * FROM ' . $this->_params['table'] . ' WHERE username = ?', array($user));
     } catch (Horde_Db_Exception $e) {
         throw new Sam_Exception($e);
     }
     /* Loop through rows, retrieving options. */
     $return = array();
     foreach ($result as $row) {
         $attribute = $this->_mapOptionToAttribute($row['preference']);
         if (isset($return[$attribute])) {
             if (!is_array($return[$attribute])) {
                 $return[$attribute] = array($return[$attribute]);
             }
             if (!in_array($row['value'], $return[$attribute])) {
                 $return[$attribute][] = $row['value'];
             }
         } else {
             $return[$attribute] = $row['value'];
         }
     }
     return $return;
 }

 /**
  * Retrieves all of the notes of the current notepad from the backend.
  *
  * @throws Mnemo_Exception
  */
 public function retrieve()
 {
     $query = 'SELECT * FROM mnemo_memos WHERE memo_owner = ?';
     $values = array($this->_notepad);
     try {
         $rows = $this->_db->select($query, $values);
     } catch (Horde_Db_Exception $e) {
         throw new Mnemo_Exception($e->getMessage());
     }
     // Store the retrieved values in a fresh list.
     $this->_memos = array();
     foreach ($rows as $row) {
         $this->_memos[$row['memo_id']] = $this->_buildNote($row);
     }
 }

 /**
  * Fetch client settings from storage.
  *
  * @param integer the client id
  *
  * @return array  A hash of client settings.
  * @throws Hermes_Exception
  */
 public function getClientSettings($clientID)
 {
     $clients = Hermes::listClients();
     if (empty($clientID) || !isset($clients[$clientID])) {
         throw new Horde_Exception_NotFound('Does not exist');
     }
     $sql = 'SELECT clientjob_id, clientjob_enterdescription,' . ' clientjob_exportid FROM hermes_clientjobs' . ' WHERE clientjob_id = ?';
     $values = array($clientID);
     try {
         $rows = $this->_db->select($sql, $values);
     } catch (Horde_Db_Exception $e) {
         throw new Hermes_Exception($e);
     }
     $clientJob = array();
     foreach ($rows as $row) {
         $clientJob[$row['clientjob_id']] = array($row['clientjob_enterdescription'], $row['clientjob_exportid']);
     }
     if (isset($clientJob[$clientID])) {
         $settings = array('id' => $clientID, 'enterdescription' => $clientJob[$clientID][0], 'exportid' => $this->_convertFromDriver($clientJob[$clientID][1]));
     } else {
         $settings = array('id' => $clientID, 'enterdescription' => 1, 'exportid' => null);
     }
     $settings['name'] = $clients[$clientID];
     return $settings;
 }

 /**
  * Return a list of valid locks with the option to limit the results
  * by principal, scope and/or type.
  *
  * @see Horde_Lock_Base::getLocks()
  */
 public function getLocks($scope = null, $principal = null, $type = null)
 {
     $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_expiry_timestamp >= ? OR lock_expiry_timestamp = ?)';
     $values = array($now, Horde_Lock::PERMANENT);
     // Check to see if we need to filter the results
     if (!empty($principal)) {
         $sql .= ' AND lock_principal = ?';
         $values[] = $principal;
     }
     if (!empty($scope)) {
         $sql .= ' AND lock_scope = ?';
         $values[] = $scope;
     }
     if (!empty($type)) {
         $sql .= ' AND lock_type = ?';
         $values[] = $type;
     }
     try {
         $result = $this->_db->select($sql, $values);
     } catch (Horde_Db_Exception $e) {
         throw new Horde_Lock_Exception($e);
     }
     $locks = array();
     foreach ($result as $row) {
         $locks[$row['lock_id']] = $row;
     }
     return $locks;
 }

 /**
  * Reads the given data from the SQL database and returns the results.
  *
  * @param string $key        The primary key field to use.
  * @param mixed $ids         The ids of the contacts to load.
  * @param string $owner      Only return contacts owned by this user.
  * @param array $fields      List of fields to return.
  * @param array $blobFields  Array of fields containing binary data.
  * @param array $dateFields  Array of fields containing date data.
  *                           @since 4.2.0
  *
  * @return array  Hash containing the search results.
  * @throws Turba_Exception
  */
 protected function _read($key, $ids, $owner, array $fields, array $blobFields = array(), array $dateFields = array())
 {
     $values = array();
     $in = '';
     if (is_array($ids)) {
         if (!count($ids)) {
             return array();
         }
         foreach ($ids as $id) {
             $in .= empty($in) ? '?' : ', ?';
             $values[] = $this->_convertToDriver($id);
         }
         $where = $key . ' IN (' . $in . ')';
     } else {
         $where = $key . ' = ?';
         $values[] = $this->_convertToDriver($ids);
     }
     if (isset($this->map['__owner'])) {
         $where .= ' AND ' . $this->map['__owner'] . ' = ?';
         $values[] = $this->_convertToDriver($owner);
     }
     if (!empty($this->_params['filter'])) {
         $where .= ' AND ' . $this->_params['filter'];
     }
     $query = 'SELECT ' . implode(', ', $fields) . ' FROM ' . $this->_params['table'] . ' WHERE ' . $where;
     try {
         return $this->_parseRead($blobFields, $this->_db->select($query, $values), $dateFields);
     } catch (Horde_Db_Exception $e) {
         throw new Turba_Exception(_("Server error when performing search."));
     }
 }

 /**
  * Fetches the fields for a particular form.
  *
  * @param integer $form_id  The form id of the form to return.
  *
  * @return array  The fields.
  * @throws Ulaform_Exception
  */
 public function getFields($form_id, $field_id = null)
 {
     $values = array($form_id);
     $sql = 'SELECT field_id, form_id, field_name, field_order, field_label, field_type, ' . ' field_params, field_required, field_readonly, field_desc FROM ulaform_fields ' . ' WHERE form_id = ?';
     if (!is_null($field_id)) {
         $sql .= ' AND field_id = ?';
         $values[] = (int) $field_id;
     }
     $sql .= ' ORDER BY field_order';
     try {
         $results = $this->_db->select($sql, $values);
     } catch (Horde_Db_Exception $e) {
         throw new Ulaform_Exception($e);
     }
     $fields = array();
     foreach ($results as $field) {
         /* If no internal name set, generate one using field_id. */
         if (empty($field['field_name'])) {
             $field['field_name'] = 'field_' . $field['field_id'];
         }
         /* Check if any params and unserialize, otherwise return null. */
         if (!empty($field['field_params'])) {
             $field['field_params'] = Horde_Serialize::unserialize($field['field_params'], Horde_Serialize::UTF7_BASIC);
         } else {
             $field['field_params'] = null;
         }
         $fields[] = $field;
     }
     return $fields;
 }

 /**
  * Ensure that an array of objects exist in storage. Create any that don't,
  * return object_ids for all. All objects in the $objects array must be
  * of the same content type.
  *
  * @param mixed $objects  An array of objects (or single obejct value).
  *                        Values typed as an integer are assumed to already
  *                        be an object_id.
  * @param mixed $type     Either a string type_name or integer type_id
  *
  * @return array  An array of object_ids.
  */
 public function ensureObjects($objects, $type)
 {
     if (!is_array($objects)) {
         $objects = array($objects);
     }
     $objectIds = array();
     $objectName = array();
     $type = current($this->_typeManager->ensureTypes($type));
     // Anything already typed as an integer is assumed to be an object id.
     foreach ($objects as $objectIndex => $object) {
         if (is_int($object)) {
             $objectIds[$objectIndex] = $object;
         } else {
             $objectName[$object] = $objectIndex;
         }
     }
     // Get the ids for any objects that already exist.
     try {
         if (count($objectName)) {
             $rows = $this->_db->select('SELECT object_id, object_name FROM ' . $this->_t('objects') . ' WHERE object_name IN (' . implode(',', array_map(array($this->_db, 'quoteString'), array_keys($objectName))) . ') AND type_id = ' . $type);
             foreach ($rows as $row) {
                 $objectIndex = $objectName[$row['object_name']];
                 unset($objectName[$row['object_name']]);
                 $objectIds[$objectIndex] = $row['object_id'];
             }
         }
         // Create any objects that didn't already exist
         foreach ($objectName as $object => $objectIndex) {
             $objectIds[$objectIndex] = $this->_db->insert('INSERT INTO ' . $this->_t('objects') . ' (object_name, type_id) VALUES (' . $this->_db->quoteString($object) . ', ' . $type . ')');
         }
     } catch (Horde_Db_Exception $e) {
         throw new Content_Exception($e);
     }
     return $objectIds;
 }

 /**
  */
 public function get($scope_ob)
 {
     $charset = $this->_db->getOption('charset');
     $query = 'SELECT pref_name, pref_value FROM ' . $this->_params['table'] . ' ' . 'WHERE pref_uid = ? AND pref_scope = ?';
     $values = array($this->_params['user'], $scope_ob->scope);
     try {
         $result = $this->_db->select($query, $values);
         $columns = $this->_db->columns($this->_params['table']);
     } catch (Horde_Db_Exception $e) {
         throw new Horde_Prefs_Exception($e);
     }
     foreach ($result as $row) {
         $name = trim($row['pref_name']);
         $value = $columns['pref_value']->binaryToString($row['pref_value']);
         $scope_ob->set($name, Horde_String::convertCharset($value, $charset, 'UTF-8'));
     }
     return $scope_ob;
 }

 /**
  * Return the images corresponding to the given ids.
  *
  * @param array $params function parameters:
  *  <pre>
  *    'ids'        - An array of image ids to fetch.
  *    'preserve'   - Preserve the order of the image ids when returned.
  *    'gallery_id' - Return all images from requested gallery (ignores 'ids').
  *    'from'       - If passing a gallery, start at this image.
  *    'count'      - If passing a gallery, return this many images.
  *  </pre>
  *
  * @return array An array of Ansel_Image objects.
  * @throws Ansel_Exception, Horde_Exception_NotFound, InvalidArgumentException
  */
 public function getImages(array $params = array())
 {
     // First check if we want a specific gallery or a list of images
     if (!empty($params['gallery_id'])) {
         $sql = 'SELECT ' . $this->_getImageFields() . ' FROM ansel_images WHERE gallery_id = ' . $params['gallery_id'] . ' ORDER BY image_sort';
     } elseif (!empty($params['ids']) && is_array($params['ids']) && count($params['ids']) > 0) {
         $sql = 'SELECT ' . $this->_getImageFields() . ' FROM ansel_images WHERE image_id IN (';
         $i = 1;
         $cnt = count($params['ids']);
         foreach ($params['ids'] as $id) {
             $sql .= (int) $id . ($i++ < $cnt ? ',' : ');');
         }
     } else {
         throw new InvalidArgumentException('Ansel_Storage::getImages requires either a gallery_id or an array of image ids');
     }
     // Limit the query?
     if (isset($params['count']) && isset($params['from'])) {
         $sql = $this->_db->addLimitOffset($sql, array('limit' => $params['count'], 'offset' => $params['from']));
     }
     try {
         $images = $this->_db->select($sql);
     } catch (Horde_Db_Exception $e) {
         throw new Ansel_Exception($images);
     }
     // Throw exception if we asked for specific image ids and not found.
     if (empty($images) && empty($params['gallery_id'])) {
         throw new Horde_Exception_NotFound(_("Images not found"));
     } elseif (empty($images)) {
         return array();
     }
     $return = array();
     foreach ($images as $image) {
         $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');
         $return[$image['image_id']] = new Ansel_Image($image);
         $this->_images[(int) $image['image_id']] =& $return[$image['image_id']];
     }
     // Need to get comment counts if comments are enabled
     $ccounts = $this->_getImageCommentCounts(array_keys($return));
     if (count($ccounts)) {
         foreach ($return as $key => $image) {
             $return[$key]->commentCount = !empty($ccounts[$key]) ? $ccounts[$key] : 0;
         }
     }
     // Preserve the order the images_ids were passed in
     if (empty($params['gallery_id']) && !empty($params['preserve'])) {
         foreach ($params['ids'] as $id) {
             $ordered[$id] = $return[$id];
         }
         return $ordered;
     }
     return $return;
 }

 /**
  * Initialization tasks.
  *
  * @throws Horde_Alarm_Exception
  */
 public function initialize()
 {
     /* Handle any database specific initialization code to run. */
     switch ($this->_db->adapterName()) {
         case 'PDO_Oci':
             $query = "ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'";
             $this->_db->select($query);
             break;
         case 'PDO_PostgreSQL':
             $query = "SET datestyle TO 'iso'";
             $this->_db->select($query);
             break;
     }
 }

 /**
  * Returns an array of all system shares.
  *
  * @return array  All system shares.
  * @throws Horde_Share_Exception
  */
 public function listSystemShares()
 {
     $query = 'SELECT * FROM ' . $this->_table . ' WHERE share_owner IS NULL';
     try {
         $rows = $this->_db->select($query);
     } catch (Horde_Db_Exception $e) {
         throw new Horde_Share_Exception($e->getMessage());
     }
     $sharelist = array();
     foreach ($rows as $share) {
         $this->_convertClobs($share);
         $data = $this->_fromDriverCharset($share);
         $this->_getSharePerms($data);
         $sharelist[$data['share_name']] = $this->_createObject($data);
     }
     return $sharelist;
 }

 /**
  * Generate a tag cloud. Same syntax as getTags, except that fetching a
  * cloud for a userId + objectId combination doesn't make sense - the counts
  * would all be one. In addition, this method returns counts for each tag.
  *
  * @param array  $args  Search criteria:
  *   - limit: (integer)  Maximum number of tags to return.
  *   - offset: (integet) Offset the results. Only useful for paginating, and
  *                       not recommended.
  *   - userId: (string)  Only return tags that have been applied by a
  *                       specific user.
  *   - typeId: (array)   Only return tags that have been applied by specific
  *                       object types.
  *   - objectId: (array) Only return tags that have been applied to specific
  *                       objects all objects must be of the same type and
  *                       specified by typeId.
  *   - tagIds: (array)   Only return information on specific tag
  *                       (an array of tag ids).
  *
  * @return array  An array of hashes, each containing tag_id, tag_name, and count.
  */
 public function getTagCloud($args = array())
 {
     if (isset($args['objectId'])) {
         if (empty($args['typeId'])) {
             throw new Content_Exception('Specified objectId, but failed to specify typeId for getTagCloud call.');
         }
         $args['objectId'] = $this->_objectManager->ensureObjects($args['objectId'], $args['typeId']);
         $sql = 'SELECT t.tag_id AS tag_id, tag_name, COUNT(*) AS count FROM ' . $this->_t('tagged') . ' tagged INNER JOIN ' . $this->_t('tags') . ' t ON tagged.tag_id = t.tag_id WHERE tagged.object_id IN (' . implode(',', $args['objectId']) . ') GROUP BY t.tag_id, t.tag_name';
     } elseif (isset($args['userId']) && isset($args['typeId'])) {
         $args['userId'] = current($this->_userManager->ensureUsers($args['userId']));
         $args['typeId'] = $this->_typeManager->ensureTypes($args['typeId']);
         // This doesn't use a stat table, so may be slow.
         $sql = 'SELECT t.tag_id AS tag_id, tag_name, COUNT(*) AS count FROM ' . $this->_t('tagged') . ' tagged INNER JOIN ' . $this->_t('objects') . ' objects ON tagged.object_id = objects.object_id AND objects.type_id IN (' . implode(',', $args['typeId']) . ') INNER JOIN ' . $this->_t('tags') . ' t ON tagged.tag_id = t.tag_id WHERE tagged.user_id = ' . (int) $args['userId'] . ' GROUP BY t.tag_id, t.tag_name';
     } elseif (isset($args['userId'])) {
         $args['userId'] = current($this->_userManager->ensureUsers($args['userId']));
         $sql = 'SELECT t.tag_id AS tag_id, tag_name, count FROM ' . $this->_t('tagged') . ' tagged INNER JOIN ' . $this->_t('tags') . ' t ON tagged.tag_id = t.tag_id INNER JOIN ' . $this->_t('user_tag_stats') . ' uts ON t.tag_id = uts.tag_id AND uts.user_id = ' . (int) $args['userId'] . ' GROUP BY t.tag_id, tag_name, count';
     } elseif (isset($args['tagIds']) && isset($args['typeId'])) {
         $args['typeId'] = $this->_typeManager->ensureTypes($args['typeId']);
         // This doesn't use a stat table, so may be slow.
         $sql = 'SELECT t.tag_id AS tag_id, tag_name, COUNT(*) AS count FROM ' . $this->_t('tagged') . ' tagged INNER JOIN ' . $this->_t('objects') . ' objects ON tagged.object_id = objects.object_id AND objects.type_id IN(' . implode(',', $args['typeId']) . ') INNER JOIN ' . $this->_t('tags') . ' t ON tagged.tag_id = t.tag_id AND t.tag_id IN (' . implode(', ', $args['tagIds']) . ') GROUP BY t.tag_id, t.tag_name';
     } elseif (isset($args['typeId'])) {
         $args['typeId'] = $this->_typeManager->ensureTypes($args['typeId']);
         // This doesn't use a stat table, so may be slow.
         $sql = 'SELECT t.tag_id AS tag_id, tag_name, COUNT(*) AS count FROM ' . $this->_t('tagged') . ' tagged INNER JOIN ' . $this->_t('objects') . ' objects ON tagged.object_id = objects.object_id AND objects.type_id IN(' . implode(',', $args['typeId']) . ') INNER JOIN ' . $this->_t('tags') . '  t ON tagged.tag_id = t.tag_id GROUP BY t.tag_id, t.tag_name';
     } elseif (isset($args['tagIds'])) {
         $ids = $this->_checkTags($args['tagIds'], false);
         $sql = 'SELECT t.tag_id AS tag_id, tag_name, COUNT(*) AS count FROM ' . $this->_t('tagged') . ' tagged INNER JOIN ' . $this->_t('tags') . ' t ON tagged.tag_id = t.tag_id INNER JOIN ' . $this->_t('tag_stats') . ' ts ON t.tag_id = ts.tag_id WHERE t.tag_id IN (' . implode(', ', $ids) . ') GROUP BY t.tag_id, t.tag_name';
     } else {
         $sql = 'SELECT t.tag_id AS tag_id, tag_name, COUNT(*) AS count FROM ' . $this->_t('tagged') . ' tagged INNER JOIN ' . $this->_t('tags') . ' t ON tagged.tag_id = t.tag_id INNER JOIN ' . $this->_t('tag_stats') . ' ts ON t.tag_id = ts.tag_id GROUP BY t.tag_id, t.tag_name';
     }
     if (isset($args['limit'])) {
         $sql = $this->_db->addLimitOffset($sql . ' ORDER BY count DESC', array('limit' => $args['limit'], 'offset' => isset($args['offset']) ? $args['offset'] : 0));
     }
     try {
         $rows = $this->_db->select($sql);
         $results = array();
         foreach ($rows as $row) {
             $results[$row['tag_id']] = $row;
         }
         return $results;
     } catch (Exception $e) {
         throw new Content_Exception($e);
     }
 }