/**
* 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;
}
