summaryrefslogtreecommitdiffstats
path: root/library/Icinga/Data/Db/DbQuery.php
diff options
context:
space:
mode:
Diffstat (limited to 'library/Icinga/Data/Db/DbQuery.php')
-rw-r--r--library/Icinga/Data/Db/DbQuery.php565
1 files changed, 565 insertions, 0 deletions
diff --git a/library/Icinga/Data/Db/DbQuery.php b/library/Icinga/Data/Db/DbQuery.php
new file mode 100644
index 0000000..ff1d131
--- /dev/null
+++ b/library/Icinga/Data/Db/DbQuery.php
@@ -0,0 +1,565 @@
+<?php
+/* Icinga Web 2 | (c) 2014 Icinga Development Team | GPLv2+ */
+
+namespace Icinga\Data\Db;
+
+use DateInterval;
+use DateTime;
+use DateTimeZone;
+use Exception;
+use Icinga\Data\Filter\Filter;
+use Zend_Db_Adapter_Abstract;
+use Zend_Db_Expr;
+use Zend_Db_Select;
+use Icinga\Application\Logger;
+use Icinga\Data\SimpleQuery;
+use Icinga\Exception\ProgrammingError;
+use Icinga\Exception\QueryException;
+
+/**
+ * Database query class
+ */
+class DbQuery extends SimpleQuery
+{
+ /**
+ * @var Zend_Db_Adapter_Abstract
+ */
+ protected $db;
+
+ /**
+ * Whether or not the query is a sub query
+ *
+ * Sub queries are automatically wrapped in parentheses
+ *
+ * @var bool
+ */
+ protected $isSubQuery = false;
+
+ /**
+ * Select query
+ *
+ * @var Zend_Db_Select
+ */
+ protected $select;
+
+ /**
+ * Whether to use a subquery for counting
+ *
+ * When the query is distinct or has a HAVING or GROUP BY clause this must be set to true
+ *
+ * @var bool
+ */
+ protected $useSubqueryCount = false;
+
+ /**
+ * Count query result
+ *
+ * Count queries are only executed once
+ *
+ * @var int
+ */
+ protected $count;
+
+ /**
+ * GROUP BY clauses
+ *
+ * @var string|array
+ */
+ protected $group;
+
+ protected function init()
+ {
+ $this->db = $this->ds->getDbAdapter();
+ $this->select = $this->db->select();
+ parent::init();
+ }
+
+ /**
+ * Get whether or not the query is a sub query
+ */
+ public function getIsSubQuery()
+ {
+ return $this->isSubQuery;
+ }
+
+ /**
+ * Set whether or not the query is a sub query
+ *
+ * @param bool $isSubQuery
+ *
+ * @return $this
+ */
+ public function setIsSubQuery($isSubQuery = true)
+ {
+ $this->isSubQuery = (bool) $isSubQuery;
+ return $this;
+ }
+
+ public function setUseSubqueryCount($useSubqueryCount = true)
+ {
+ $this->useSubqueryCount = $useSubqueryCount;
+ return $this;
+ }
+
+ public function from($target, array $fields = null)
+ {
+ parent::from($target, $fields);
+ $this->select->from($this->target, array());
+ return $this;
+ }
+
+ public function where($condition, $value = null)
+ {
+ // $this->count = $this->select = null;
+ return parent::where($condition, $value);
+ }
+
+ public function addFilter(Filter $filter)
+ {
+ $this->expressionsToTimestamp($filter);
+ return parent::addFilter($filter);
+ }
+
+ private function expressionsToTimestamp(Filter $filter)
+ {
+ if ($filter->isChain()) {
+ foreach ($filter->filters() as $child) {
+ $this->expressionsToTimestamp($child);
+ }
+ } elseif ($this->isTimestamp($filter->getColumn())) {
+ $filter->setExpression($this->valueToTimestamp($filter->getExpression()));
+ }
+ }
+
+ protected function dbSelect()
+ {
+ return clone $this->select;
+ }
+
+ /**
+ * Return the underlying select
+ *
+ * @return Zend_Db_Select
+ */
+ public function select()
+ {
+ return $this->select;
+ }
+
+ /**
+ * Get the select query
+ *
+ * Applies order and limit if any
+ *
+ * @return Zend_Db_Select
+ */
+ public function getSelectQuery()
+ {
+ $select = $this->dbSelect();
+ // Add order fields to select for postgres distinct queries (#6351)
+ if ($this->hasOrder()
+ && $this->getDatasource()->getDbType() === 'pgsql'
+ && $select->getPart(Zend_Db_Select::DISTINCT) === true) {
+ foreach ($this->getOrder() as $fieldAndDirection) {
+ if (array_search($fieldAndDirection[0], $this->columns, true) === false) {
+ $this->columns[] = $fieldAndDirection[0];
+ }
+ }
+ }
+
+ $group = $this->getGroup();
+ if ($group) {
+ $select->group($group);
+ }
+
+ if (! empty($this->columns)) {
+ $select->columns($this->columns);
+ }
+
+ $this->applyFilterSql($select);
+
+ if ($this->hasLimit() || $this->hasOffset()) {
+ $select->limit($this->getLimit(), $this->getOffset());
+ }
+ if ($this->hasOrder()) {
+ foreach ($this->getOrder() as $fieldAndDirection) {
+ $select->order(
+ $fieldAndDirection[0] . ' ' . $fieldAndDirection[1]
+ );
+ }
+ }
+
+ return $select;
+ }
+
+ protected function applyFilterSql($select)
+ {
+ $where = $this->getDatasource()->renderFilter($this->filter);
+ if ($where !== '') {
+ $select->where($where);
+ }
+ }
+
+ protected function escapeForSql($value)
+ {
+ // bindParam? bindValue?
+ if (is_array($value)) {
+ $ret = array();
+ foreach ($value as $val) {
+ $ret[] = $this->escapeForSql($val);
+ }
+ return implode(', ', $ret);
+ } else {
+ //if (preg_match('/^\d+$/', $value)) {
+ // return $value;
+ //} else {
+ return $this->db->quote($value);
+ //}
+ }
+ }
+
+ protected function escapeWildcards($value)
+ {
+ return preg_replace('/\*/', '%', $value);
+ }
+
+ protected function valueToTimestamp($value)
+ {
+ if (is_string($value)) {
+ if (ctype_digit($value)) {
+ $value = (int) $value;
+ } else {
+ $value = strtotime($value);
+ }
+ } elseif (! is_int($value)) {
+ $value = (int) $value;
+ }
+
+ return $value;
+ }
+
+ /**
+ * Render the given timestamp based on the local timezone
+ *
+ * Since {@see DbConnection::defaultTimezoneOffset()} tells the database the timezone with just an offset,
+ * this will prepare the rendered value in a way that it plays fine with daylight savings.
+ *
+ * @param int $value
+ * @return string
+ */
+ protected function timestampForSql($value)
+ {
+ if ($this->getDatasource()->getDbType() === 'pgsql') {
+ // We don't tell PostgreSQL the user's timezone
+ $dateTime = (new DateTime())
+ ->setTimezone(new DateTimeZone('UTC'))
+ ->setTimestamp($value);
+ } else {
+ $dateTime = new DateTime();
+ // Get "current" offset the database will use
+ $offsetToUTC = $dateTime->getOffset();
+ // Set timezone to UTC and initialize it with the timestamp
+ $dateTime->setTimezone(new DateTimeZone('UTC'))->setTimestamp($value);
+ // Normalize every datetime based on the only offset the database knows about
+ if ($offsetToUTC >= 0) {
+ $dateTime->add(new DateInterval("PT{$offsetToUTC}S"));
+ } else {
+ $offsetToUTC = abs($offsetToUTC);
+ $dateTime->sub(new DateInterval("PT{$offsetToUTC}S"));
+ }
+ }
+
+ return $dateTime->format('Y-m-d H:i:s');
+ }
+
+ /**
+ * Check for timestamp fields
+ *
+ * TODO: This is not here to do automagic timestamp stuff. One may
+ * override this function for custom voodoo, IdoQuery right now
+ * does. IMO we need to split whereToSql functionality, however
+ * I'd prefer to wait with this unless we understood how other
+ * backends will work. We probably should also rename this
+ * function to isTimestampColumn().
+ *
+ * @param string $field Field Field name to checked
+ * @return bool Whether this field expects timestamps
+ */
+ public function isTimestamp($field)
+ {
+ return false;
+ }
+
+ /**
+ * Get the count query
+ *
+ * @return Zend_Db_Select
+ */
+ public function getCountQuery()
+ {
+ // TODO: there may be situations where we should clone the "select"
+ $count = $this->dbSelect();
+ $this->applyFilterSql($count);
+ $group = $this->getGroup();
+ if ($this->useSubqueryCount || $group) {
+ if (! empty($this->columns)) {
+ $count->columns($this->columns);
+ }
+ if ($group) {
+ $count->group($group);
+ }
+ $columns = array('cnt' => 'COUNT(*)');
+ return $this->db->select()->from($count, $columns);
+ }
+
+ $count->columns(array('cnt' => 'COUNT(*)'));
+ return $count;
+ }
+
+ /**
+ * Count all rows of the result set
+ *
+ * @return int
+ */
+ public function count(): int
+ {
+ if ($this->count === null) {
+ $this->count = parent::count();
+ }
+
+ return $this->count;
+ }
+
+ /**
+ * Return the select and count query as a textual representation
+ *
+ * @return string A string containing the select and count query, using unix style newlines as linebreaks
+ */
+ public function dump()
+ {
+ return "QUERY\n=====\n"
+ . $this->getSelectQuery()
+ . "\n\nCOUNT\n=====\n"
+ . $this->getCountQuery()
+ . "\n\n";
+ }
+
+ public function __clone()
+ {
+ parent::__clone();
+ $this->select = clone $this->select;
+ }
+
+ /**
+ * @return string
+ */
+ public function __toString()
+ {
+ try {
+ $select = (string) $this->getSelectQuery();
+ return $this->getIsSubQuery() ? ('(' . $select . ')') : $select;
+ } catch (Exception $e) {
+ Logger::debug('Failed to render DbQuery. An error occured: %s', $e);
+ return '';
+ }
+ }
+
+ /**
+ * Add a GROUP BY clause
+ *
+ * @param string|array $group
+ *
+ * @return $this
+ */
+ public function group($group)
+ {
+ $this->group = $group;
+ return $this;
+ }
+
+ /**
+ * Return the GROUP BY clause
+ *
+ * @return string|array
+ */
+ public function getGroup()
+ {
+ return $this->group;
+ }
+
+ /**
+ * Return whether the given table has been joined
+ *
+ * @param string $table
+ *
+ * @return bool
+ */
+ public function hasJoinedTable($table)
+ {
+ $fromPart = $this->select->getPart(Zend_Db_Select::FROM);
+ if (isset($fromPart[$table])) {
+ return true;
+ }
+
+ foreach ($fromPart as $options) {
+ if ($options['tableName'] === $table && $options['joinType'] !== Zend_Db_Select::FROM) {
+ return true;
+ }
+ }
+
+ return false;
+ }
+
+ /**
+ * Return the alias used for joining the given table
+ *
+ * @param string $table
+ *
+ * @return string|null null in case no alias is being used
+ *
+ * @throws ProgrammingError In case the given table has not been joined
+ */
+ public function getJoinedTableAlias($table)
+ {
+ $fromPart = $this->select->getPart(Zend_Db_Select::FROM);
+ if (isset($fromPart[$table])) {
+ if ($fromPart[$table]['joinType'] === Zend_Db_Select::FROM) {
+ throw new ProgrammingError('Table "%s" has not been joined', $table);
+ }
+
+ return; // No alias in use
+ }
+
+ foreach ($fromPart as $alias => $options) {
+ if ($options['tableName'] === $table && $options['joinType'] !== Zend_Db_Select::FROM) {
+ return $alias;
+ }
+ }
+
+ throw new ProgrammingError('Table "%s" has not been joined', $table);
+ }
+
+ /**
+ * Add an INNER JOIN table and colums to the query
+ *
+ * @param array|string|Zend_Db_Expr $name The table name
+ * @param string $cond Join on this condition
+ * @param array|string $cols The columns to select from the joined table
+ * @param string $schema The database name to specify, if any
+ *
+ * @return $this
+ */
+ public function join($name, $cond, $cols = Zend_Db_Select::SQL_WILDCARD, $schema = null)
+ {
+ $this->select->joinInner($name, $cond, $cols, $schema);
+ return $this;
+ }
+
+ /**
+ * Add an INNER JOIN table and colums to the query
+ *
+ * @param array|string|Zend_Db_Expr $name The table name
+ * @param string $cond Join on this condition
+ * @param array|string $cols The columns to select from the joined table
+ * @param string $schema The database name to specify, if any
+ *
+ * @return $this
+ */
+ public function joinInner($name, $cond, $cols = Zend_Db_Select::SQL_WILDCARD, $schema = null)
+ {
+ $this->select->joinInner($name, $cond, $cols, $schema);
+ return $this;
+ }
+
+ /**
+ * Add a LEFT OUTER JOIN table and colums to the query
+ *
+ * @param array|string|Zend_Db_Expr $name The table name
+ * @param string $cond Join on this condition
+ * @param array|string $cols The columns to select from the joined table
+ * @param string $schema The database name to specify, if any
+ *
+ * @return $this
+ */
+ public function joinLeft($name, $cond, $cols = Zend_Db_Select::SQL_WILDCARD, $schema = null)
+ {
+ $this->select->joinLeft($name, $cond, $cols, $schema);
+ return $this;
+ }
+
+ /**
+ * Add a RIGHT OUTER JOIN table and colums to the query
+ *
+ * @param array|string|Zend_Db_Expr $name The table name
+ * @param string $cond Join on this condition
+ * @param array|string $cols The columns to select from the joined table
+ * @param string $schema The database name to specify, if any
+ *
+ * @return $this
+ */
+ public function joinRight($name, $cond, $cols = Zend_Db_Select::SQL_WILDCARD, $schema = null)
+ {
+ $this->select->joinRight($name, $cond, $cols, $schema);
+ return $this;
+ }
+
+ /**
+ * Add a FULL OUTER JOIN table and colums to the query
+ *
+ * @param array|string|Zend_Db_Expr $name The table name
+ * @param string $cond Join on this condition
+ * @param array|string $cols The columns to select from the joined table
+ * @param string $schema The database name to specify, if any
+ *
+ * @return $this
+ */
+ public function joinFull($name, $cond, $cols = Zend_Db_Select::SQL_WILDCARD, $schema = null)
+ {
+ $this->select->joinFull($name, $cond, $cols, $schema);
+ return $this;
+ }
+
+ /**
+ * Add a CROSS JOIN table and colums to the query
+ *
+ * @param array|string|Zend_Db_Expr $name The table name
+ * @param array|string $cols The columns to select from the joined table
+ * @param string $schema The database name to specify, if any
+ *
+ * @return $this
+ */
+ public function joinCross($name, $cols = Zend_Db_Select::SQL_WILDCARD, $schema = null)
+ {
+ $this->select->joinCross($name, $cols, $schema);
+ return $this;
+ }
+
+ /**
+ * Add a NATURAL JOIN table and colums to the query
+ *
+ * @param array|string|Zend_Db_Expr $name The table name
+ * @param array|string $cols The columns to select from the joined table
+ * @param string $schema The database name to specify, if any
+ *
+ * @return $this
+ */
+ public function joinNatural($name, $cols = Zend_Db_Select::SQL_WILDCARD, $schema = null)
+ {
+ $this->select->joinNatural($name, $cols, $schema);
+ return $this;
+ }
+
+ /**
+ * Add a UNION clause to the query
+ *
+ * @param array $select Select clauses for the union
+ * @param string $type Type of UNION to use
+ *
+ * @return $this
+ */
+ public function union($select = array(), $type = Zend_Db_Select::SQL_UNION)
+ {
+ $this->select->union($select, $type);
+ return $this;
+ }
+}
generated by cgit v1.2.3 (git 2.39.1) at 2024-07-23 17:10:13 +0000