/** * Gets a recordset of tag dataobjects. * * @param SwatDBRange $range optional. Range of tags to retrieve. If not * specified, all tags are loaded. * @param string $order_by_clause optional. SQL order by clause of the tag * list. * * @return PinholeTagDataObjectWrapper */ private function getSubTagDataObjects(SwatDBRange $range = null, $order_by_clause = null) { $args = func_get_args(); $cache_key = $this->getCacheKey(__FUNCTION__, $args); $value = $this->app->getCacheRecordset($cache_key, 'PinholeTagDataObjectWrapper', 'photos'); if ($value !== false) { return $value; } if ($order_by_clause === null) { $order_by_clause = 'PinholeTagDateView.first_modified desc'; } $sql = sprintf('select PinholeTag.*, PinholeTagDateView.first_modified, PinholeTagDateView.last_modified from PinholeTag inner join PinholeTagDateView on PinholeTagDateView.tag = PinholeTag.id where %s order by %s', $this->getSubTagWhereClause(), $order_by_clause); if ($range !== null) { $this->db->setLimit($range->getLimit(), $range->getOffset()); } $tag_data_objects = SwatDB::query($this->db, $sql, 'PinholeTagDataObjectWrapper'); $this->app->addCacheRecordset($tag_data_objects, $cache_key, 'photos'); return $tag_data_objects; }
/** * @brief Prepare a SQL query * @param string $query Query string * @param int $limit * @param int $offset * @return MDB2_Statement_Common prepared SQL query * * SQL query via MDB2 prepare(), needs to be execute()'d! */ public static function prepare($query, $limit = null, $offset = null) { if (!is_null($limit) && $limit != -1) { if (self::$backend == self::BACKEND_MDB2) { //MDB2 uses or emulates limits & offset internally self::$MDB2->setLimit($limit, $offset); } else { //PDO does not handle limit and offset. //FIXME: check limit notation for other dbs //the following sql thus might needs to take into account db ways of representing it //(oracle has no LIMIT / OFFSET) $limit = (int) $limit; $limitsql = ' LIMIT ' . $limit; if (!is_null($offset)) { $offset = (int) $offset; $limitsql .= ' OFFSET ' . $offset; } //insert limitsql if (substr($query, -1) == ';') { //if query ends with ; $query = substr($query, 0, -1) . $limitsql . ';'; } else { $query .= $limitsql; } } } // Optimize the query $query = self::processQuery($query); self::connect(); // return the result if (self::$backend == self::BACKEND_MDB2) { $result = self::$connection->prepare($query); // Die if we have an error (error means: bad query, not 0 results!) if (PEAR::isError($result)) { $entry = 'DB Error: "' . $result->getMessage() . '"<br />'; $entry .= 'Offending command was: ' . htmlentities($query) . '<br />'; OC_Log::write('core', $entry, OC_Log::FATAL); error_log('DB error: ' . $entry); OC_Template::printErrorPage($entry); } } else { try { $result = self::$connection->prepare($query); } catch (PDOException $e) { $entry = 'DB Error: "' . $e->getMessage() . '"<br />'; $entry .= 'Offending command was: ' . htmlentities($query) . '<br />'; OC_Log::write('core', $entry, OC_Log::FATAL); error_log('DB error: ' . $entry); OC_Template::printErrorPage($entry); } $result = new PDOStatementWrapper($result); } return $result; }
/** * Private method that will use MDB2_Driver_Common::query() for simple and * MDB2_Driver_Common::prepare() & MDB2_Statement_Common::execute() for complex * query specifications. * * @param mixed $sql A string or an array. * @param string $configPath The config path used for exception messages. * * @return MDB2_Result * @throws XML_Query2XML_DBException If a database related error occures. */ private function _prepareAndExecute($sql, $configPath) { $preparedQuery = $sql['query']; if (isset($sql['limit'])) { $preparedQuery .= '; LIMIT:' . $sql['limit']; $preparedQuery .= '; OFFSET:' . $sql['offset']; $this->_db->setLimit($sql['limit'], $sql['offset']); } if (isset($this->_preparedQueries[$preparedQuery])) { $queryHandle = $this->_preparedQueries[$preparedQuery]; } else { // PREPARE $queryHandle = $this->_db->prepare($sql['query']); if (PEAR::isError($queryHandle)) { /* * unit tests: (only if mysql or pgsql is used) * MDB2/_prepareAndExecute/throwDBException_complexQuery.phpt */ throw new XML_Query2XML_DBException($configPath . ': Could not prepare the following SQL query: ' . $sql['query'] . '; ' . $queryHandle->toString()); } $this->_preparedQueries[$preparedQuery] =& $queryHandle; } // EXECUTE if (isset($sql['data'])) { $result = $queryHandle->execute($sql['data']); } else { $result = $queryHandle->execute(); } if (PEAR::isError($result)) { /* * unit tests: * if sqlite is used: MDB2/_prepareAndExecute/ * throwDBException_complexQuery.phpt * if sqlite or mysql is sued: MDB2/getXML/ * throwDBException_nullResultSet_complexQuery_multipleRecords.phpt * throwDBException_nullResultSet_complexQuery_singleRecord.phpt */ throw new XML_Query2XML_DBException($configPath . ': Could not execute the following SQL query: ' . $sql['query'] . '; ' . $result->toString()); } return $result; }
public function getPostByDateAndShortname(SwatDate $date, $shortname) { $post = false; if ($this->memcache !== null) { $key = $date->formatLikeIntl('yyyyMMdd') . $shortname; $key = $this->getPostCacheKey($key); $post = $this->memcache->getNs('posts', $key); } if ($post === false) { $sql = $this->getSelectClause(); $sql .= $this->getWhereClause(); $sql .= sprintf(' and BlorgPost.shortname = %s and date_trunc(\'month\', convertTZ(publish_date, %s)) = date_trunc(\'month\', timestamp %s)', $this->db->quote($shortname, 'text'), $this->db->quote($date->getTimezone()->getName(), 'text'), $this->db->quote($date->getDate(), 'date')); $this->db->setLimit(1, 0); $post_wrapper = SwatDBClassMap::get('BlorgPostWrapper'); $posts = SwatDB::query($this->db, $sql, $post_wrapper); if (in_array('author', $this->fields)) { $this->loadPostAuthors($posts); } if ($this->load_files) { $this->loadPostFiles($posts); } if ($this->load_tags) { $this->loadPostTags($posts); } $post = $posts->getFirst(); if ($this->memcache !== null) { $this->memcache->setNs('posts', $key, $post); } } else { if ($post !== null) { $post->setDatabase($this->db); } } return $post; }
/** * @brief Prepare a SQL query * @param string $query Query string * @param int $limit * @param int $offset * @param bool $isManipulation * @return MDB2_Statement_Common prepared SQL query * * SQL query via MDB2 prepare(), needs to be execute()'d! */ public static function prepare($query, $limit = null, $offset = null, $isManipulation = null) { if (!is_null($limit) && $limit != -1) { if (self::$backend == self::BACKEND_MDB2) { //MDB2 uses or emulates limits & offset internally self::$MDB2->setLimit($limit, $offset); } else { //PDO does not handle limit and offset. //FIXME: check limit notation for other dbs //the following sql thus might needs to take into account db ways of representing it //(oracle has no LIMIT / OFFSET) $limit = (int) $limit; $limitsql = ' LIMIT ' . $limit; if (!is_null($offset)) { $offset = (int) $offset; $limitsql .= ' OFFSET ' . $offset; } //insert limitsql if (substr($query, -1) == ';') { //if query ends with ; $query = substr($query, 0, -1) . $limitsql . ';'; } else { $query .= $limitsql; } } } else { if (isset(self::$preparedQueries[$query]) and self::$cachingEnabled) { return self::$preparedQueries[$query]; } } $rawQuery = $query; // Optimize the query $query = self::processQuery($query); self::connect(); if ($isManipulation === null) { //try to guess, so we return the number of rows on manipulations $isManipulation = self::isManipulation($query); } // return the result if (self::$backend == self::BACKEND_MDB2) { // differentiate between query and manipulation if ($isManipulation) { $result = self::$connection->prepare($query, null, MDB2_PREPARE_MANIP); } else { $result = self::$connection->prepare($query, null, MDB2_PREPARE_RESULT); } // Die if we have an error (error means: bad query, not 0 results!) if (self::isError($result)) { throw new DatabaseException($result->getMessage(), $query); } } else { try { $result = self::$connection->prepare($query); } catch (PDOException $e) { throw new DatabaseException($e->getMessage(), $query); } // differentiate between query and manipulation $result = new PDOStatementWrapper($result, $isManipulation); } if (is_null($limit) || $limit == -1 and self::$cachingEnabled) { $type = OC_Config::getValue("dbtype", "sqlite"); if ($type != 'sqlite' && $type != 'sqlite3') { self::$preparedQueries[$rawQuery] = $result; } } return $result; }