Adding upstream version 2.11.4.upstream/2.11.4upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

2 files changed, 1219 insertions, 0 deletions

diff --git a/library/Icinga/Data/Db/DbConnection.php b/library/Icinga/Data/Db/DbConnection.php
new file mode 100644
index 0000000..fc6814d
--- /dev/null
+++ b/library/Icinga/Data/Db/DbConnection.php
@@ -0,0 +1,655 @@
+<?php
+/* Icinga Web 2 | (c) 2013 Icinga Development Team | GPLv2+ */
+
+namespace Icinga\Data\Db;
+
+use DateTime;
+use DateTimeZone;
+use Exception;
+use Icinga\Data\Filter\FilterEqual;
+use Icinga\Data\Filter\FilterNotEqual;
+use Icinga\Data\Inspectable;
+use Icinga\Data\Inspection;
+use PDO;
+use Iterator;
+use Zend_Db;
+use Zend_Db_Expr;
+use Icinga\Data\ConfigObject;
+use Icinga\Data\Extensible;
+use Icinga\Data\Filter\Filter;
+use Icinga\Data\Filter\FilterAnd;
+use Icinga\Data\Filter\FilterNot;
+use Icinga\Data\Filter\FilterOr;
+use Icinga\Data\Reducible;
+use Icinga\Data\ResourceFactory;
+use Icinga\Data\Selectable;
+use Icinga\Data\Updatable;
+use Icinga\Exception\ConfigurationError;
+use Icinga\Exception\ProgrammingError;
+
+/**
+ * Encapsulate database connections and query creation
+ */
+class DbConnection implements Selectable, Extensible, Updatable, Reducible, Inspectable
+{
+    /**
+     * Connection config
+     *
+     * @var ConfigObject
+     */
+    private $config;
+
+    /**
+     * Database type
+     *
+     * @var string
+     */
+    private $dbType;
+
+    /**
+     * @var \Zend_Db_Adapter_Abstract
+     */
+    private $dbAdapter;
+
+    /**
+     * Table prefix
+     *
+     * @var string
+     */
+    private $tablePrefix = '';
+
+    private static $genericAdapterOptions = array(
+        Zend_Db::AUTO_QUOTE_IDENTIFIERS => false,
+        Zend_Db::CASE_FOLDING           => Zend_Db::CASE_LOWER
+    );
+
+    private static $driverOptions = array(
+        PDO::ATTR_TIMEOUT    => 10,
+        PDO::ATTR_CASE       => PDO::CASE_LOWER,
+        PDO::ATTR_ERRMODE    => PDO::ERRMODE_EXCEPTION
+    );
+
+    /**
+     * Create a new connection object
+     *
+     * @param ConfigObject $config
+     */
+    public function __construct(ConfigObject $config = null)
+    {
+        $this->config = $config;
+        $this->connect();
+    }
+
+    /**
+     * Provide a query on this connection
+     *
+     * @return  DbQuery
+     */
+    public function select()
+    {
+        return new DbQuery($this);
+    }
+
+    /**
+     * Fetch and return all rows of the given query's result set using an iterator
+     *
+     * @param   DbQuery     $query
+     *
+     * @return  Iterator
+     */
+    public function query(DbQuery $query)
+    {
+        return $query->getSelectQuery()->query();
+    }
+
+    /**
+     * Get the connection configuration
+     *
+     * @return  ConfigObject
+     */
+    public function getConfig()
+    {
+        return $this->config;
+    }
+
+    /**
+     * Getter for database type
+     *
+     * @return string
+     */
+    public function getDbType()
+    {
+        return $this->dbType;
+    }
+
+    /**
+     * Getter for the Zend_Db_Adapter
+     *
+     * @return \Zend_Db_Adapter_Abstract
+     */
+    public function getDbAdapter()
+    {
+        return $this->dbAdapter;
+    }
+
+    /**
+     * Create a new connection
+     */
+    private function connect()
+    {
+        $genericAdapterOptions  = self::$genericAdapterOptions;
+        $driverOptions          = self::$driverOptions;
+        $adapterParamaters      = array(
+            'host'              => $this->config->host,
+            'username'          => $this->config->username,
+            'password'          => $this->config->password,
+            'dbname'            => $this->config->dbname,
+            'charset'           => $this->config->charset ?: null,
+            'options'           => & $genericAdapterOptions,
+            'driver_options'    => & $driverOptions
+        );
+        $this->dbType = strtolower($this->config->get('db', 'mysql'));
+        switch ($this->dbType) {
+            case 'mssql':
+                $adapter = 'Pdo_Mssql';
+                $pdoType = $this->config->get('pdoType');
+                if (empty($pdoType)) {
+                    if (extension_loaded('sqlsrv')) {
+                        $adapter = 'Sqlsrv';
+                    } else {
+                        $pdoType = 'dblib';
+                    }
+                }
+                if ($pdoType === 'dblib') {
+                    // Driver does not support setting attributes
+                    unset($adapterParamaters['options']);
+                    unset($adapterParamaters['driver_options']);
+                }
+                if (! empty($pdoType)) {
+                    $adapterParamaters['pdoType'] = $pdoType;
+                }
+                $defaultPort = 1433;
+                break;
+            case 'mysql':
+                $adapter = 'Pdo_Mysql';
+                if ($this->config->use_ssl) {
+                    # The presence of these keys as empty strings or null cause non-ssl connections to fail
+                    if ($this->config->ssl_key) {
+                        $adapterParamaters['driver_options'][PDO::MYSQL_ATTR_SSL_KEY] = $this->config->ssl_key;
+                    }
+                    if ($this->config->ssl_cert) {
+                        $adapterParamaters['driver_options'][PDO::MYSQL_ATTR_SSL_CERT] = $this->config->ssl_cert;
+                    }
+                    if ($this->config->ssl_ca) {
+                        $adapterParamaters['driver_options'][PDO::MYSQL_ATTR_SSL_CA] = $this->config->ssl_ca;
+                    }
+                    if ($this->config->ssl_capath) {
+                        $adapterParamaters['driver_options'][PDO::MYSQL_ATTR_SSL_CAPATH] = $this->config->ssl_capath;
+                    }
+                    if ($this->config->ssl_cipher) {
+                        $adapterParamaters['driver_options'][PDO::MYSQL_ATTR_SSL_CIPHER] = $this->config->ssl_cipher;
+                    }
+                    if (defined('PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT')
+                        && $this->config->ssl_do_not_verify_server_cert
+                    ) {
+                        $adapterParamaters['driver_options'][PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT] = false;
+                    }
+                }
+                /*
+                 * Set MySQL server SQL modes to behave as closely as possible to Oracle and PostgreSQL. Note that the
+                 * ONLY_FULL_GROUP_BY mode is left on purpose because MySQL requires you to specify all non-aggregate
+                 * columns in the group by list even if the query is grouped by the master table's primary key which is
+                 * valid ANSI SQL though. Further in that case the query plan would suffer if you add more columns to
+                 * the group by list.
+                 */
+                $driverOptions[PDO::MYSQL_ATTR_INIT_COMMAND] =
+                    'SET SESSION SQL_MODE=\'STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,'
+                    . 'ANSI_QUOTES,PIPES_AS_CONCAT,NO_ENGINE_SUBSTITUTION\'';
+                if (isset($adapterParamaters['charset'])) {
+                    $driverOptions[PDO::MYSQL_ATTR_INIT_COMMAND] .= ', NAMES ' . $adapterParamaters['charset'];
+                    if (trim($adapterParamaters['charset']) === 'latin1') {
+                        // Required for MySQL 8+ because we need PIPES_AS_CONCAT and
+                        // have several columns with explicit COLLATE instructions
+                        $driverOptions[PDO::MYSQL_ATTR_INIT_COMMAND] .= ' COLLATE latin1_general_ci';
+                    }
+
+                    unset($adapterParamaters['charset']);
+                }
+
+                $driverOptions[PDO::MYSQL_ATTR_INIT_COMMAND] .= ", time_zone='" . $this->defaultTimezoneOffset() . "'";
+                $driverOptions[PDO::MYSQL_ATTR_INIT_COMMAND] .=';';
+                $defaultPort = 3306;
+                break;
+            case 'oci':
+                $adapter = 'Oracle';
+                unset($adapterParamaters['options']);
+                unset($adapterParamaters['driver_options']);
+                $adapterParamaters['driver_options'] = array(
+                    'lob_as_string' => true
+                );
+                $defaultPort = 1521;
+                break;
+            case 'oracle':
+                $adapter = 'Pdo_Oci';
+                $defaultPort = 1521;
+
+                // remove host parameter when not configured
+                if (empty($this->config->host)) {
+                    unset($adapterParamaters['host']);
+                }
+                break;
+            case 'pgsql':
+                $adapter = 'Pdo_Pgsql';
+                $defaultPort = 5432;
+                break;
+            case 'ibm':
+                $adapter = 'Pdo_Ibm';
+                $defaultPort = 50000;
+                break;
+            case 'sqlite':
+                $adapter = 'Pdo_Sqlite';
+                $defaultPort = 0; // Dummy port because a value is required
+                break;
+            default:
+                throw new ConfigurationError(
+                    'Backend "%s" is not supported',
+                    $this->dbType
+                );
+        }
+        $adapterParamaters['port'] = $this->config->get('port', $defaultPort);
+        $this->dbAdapter = Zend_Db::factory($adapter, $adapterParamaters);
+        $this->dbAdapter->setFetchMode(Zend_Db::FETCH_OBJ);
+        // TODO(el/tg): The profiler is disabled per default, why do we disable the profiler explicitly?
+        $this->dbAdapter->getProfiler()->setEnabled(false);
+    }
+
+    public static function fromResourceName($name)
+    {
+        return new static(ResourceFactory::getResourceConfig($name));
+    }
+
+    /**
+     * Getter for the table prefix
+     *
+     * @return string
+     */
+    public function getTablePrefix()
+    {
+        return $this->tablePrefix;
+    }
+
+    /**
+     * Setter for the table prefix
+     *
+     * @param   string $prefix
+     *
+     * @return  $this
+     */
+    public function setTablePrefix($prefix)
+    {
+        $this->tablePrefix = $prefix;
+        return $this;
+    }
+
+    /**
+     * Get offset from the current default timezone to GMT
+     *
+     * @return string
+     */
+    protected function defaultTimezoneOffset()
+    {
+        $tz = new DateTimeZone(date_default_timezone_get());
+        $offset = $tz->getOffset(new DateTime());
+        $prefix = $offset >= 0 ? '+' : '-';
+        $offset = abs($offset);
+        $hours = (int) floor($offset / 3600);
+        $minutes = (int) floor(($offset % 3600) / 60);
+        return sprintf('%s%d:%02d', $prefix, $hours, $minutes);
+    }
+
+    /**
+     * Count all rows of the result set
+     *
+     * @param   DbQuery     $query
+     *
+     * @return  int
+     */
+    public function count(DbQuery $query)
+    {
+        return (int) $this->dbAdapter->fetchOne($query->getCountQuery());
+    }
+
+    /**
+     * Retrieve an array containing all rows of the result set
+     *
+     * @param   DbQuery $query
+     *
+     * @return  array
+     */
+    public function fetchAll(DbQuery $query)
+    {
+        return $this->dbAdapter->fetchAll($query->getSelectQuery());
+    }
+
+    /**
+     * Fetch the first row of the result set
+     *
+     * @param   DbQuery $query
+     *
+     * @return  mixed
+     */
+    public function fetchRow(DbQuery $query)
+    {
+        return $this->dbAdapter->fetchRow($query->getSelectQuery());
+    }
+
+    /**
+     * Fetch the first column of all rows of the result set as an array
+     *
+     * @param   DbQuery   $query
+     *
+     * @return  array
+     */
+    public function fetchColumn(DbQuery $query)
+    {
+        return $this->dbAdapter->fetchCol($query->getSelectQuery());
+    }
+
+    /**
+     * Fetch the first column of the first row of the result set
+     *
+     * @param   DbQuery $query
+     *
+     * @return  string
+     */
+    public function fetchOne(DbQuery $query)
+    {
+        return $this->dbAdapter->fetchOne($query->getSelectQuery());
+    }
+
+    /**
+     * Fetch all rows of the result set as an array of key-value pairs
+     *
+     * The first column is the key, the second column is the value.
+     *
+     * @param   DbQuery $query
+     *
+     * @return  array
+     */
+    public function fetchPairs(DbQuery $query)
+    {
+        return $this->dbAdapter->fetchPairs($query->getSelectQuery());
+    }
+
+    /**
+     * Insert a table row with the given data
+     *
+     * Note that the base implementation does not perform any quoting on the $table argument.
+     * Pass an array with a column name (the same as in $bind) and a PDO::PARAM_* constant as value
+     * as third parameter $types to define a different type than string for a particular column.
+     *
+     * @param   string  $table
+     * @param   array   $bind
+     * @param   array   $types
+     *
+     * @return  int             The number of affected rows
+     */
+    public function insert($table, array $bind, array $types = array())
+    {
+        $columns = $values = array();
+        foreach ($bind as $column => $value) {
+            $columns[] = $column;
+            if ($value instanceof Zend_Db_Expr) {
+                $values[] = (string) $value;
+                unset($bind[$column]);
+            } else {
+                $values[] = ':' . $column;
+            }
+        }
+
+        $sql = 'INSERT INTO ' . $table
+            . ' (' . join(', ', $columns) . ') '
+            . 'VALUES (' . join(', ', $values) . ')';
+        $statement = $this->dbAdapter->prepare($sql);
+
+        foreach ($bind as $column => $value) {
+            $type = isset($types[$column]) ? $types[$column] : PDO::PARAM_STR;
+            $statement->bindValue(':' . $column, $value, $type);
+        }
+
+        $statement->execute();
+        return $statement->rowCount();
+    }
+
+    /**
+     * Update table rows with the given data, optionally limited by using a filter
+     *
+     * Note that the base implementation does not perform any quoting on the $table argument.
+     * Pass an array with a column name (the same as in $bind) and a PDO::PARAM_* constant as value
+     * as fourth parameter $types to define a different type than string for a particular column.
+     *
+     * @param   string  $table
+     * @param   array   $bind
+     * @param   Filter  $filter
+     * @param   array   $types
+     *
+     * @return  int             The number of affected rows
+     */
+    public function update($table, array $bind, Filter $filter = null, array $types = array())
+    {
+        $set = array();
+        foreach ($bind as $column => $value) {
+            if ($value instanceof Zend_Db_Expr) {
+                $set[] = $column . ' = ' . $value;
+                unset($bind[$column]);
+            } else {
+                $set[] = $column . ' = :' . $column;
+            }
+        }
+
+        $sql = 'UPDATE ' . $table
+            . ' SET ' . join(', ', $set)
+            . ($filter ? ' WHERE ' . $this->renderFilter($filter) : '');
+        $statement = $this->dbAdapter->prepare($sql);
+
+        foreach ($bind as $column => $value) {
+            $type = isset($types[$column]) ? $types[$column] : PDO::PARAM_STR;
+            $statement->bindValue(':' . $column, $value, $type);
+        }
+
+        $statement->execute();
+        return $statement->rowCount();
+    }
+
+    /**
+     * Delete table rows, optionally limited by using a filter
+     *
+     * @param   string  $table
+     * @param   Filter  $filter
+     *
+     * @return  int             The number of affected rows
+     */
+    public function delete($table, Filter $filter = null)
+    {
+        return $this->dbAdapter->delete($table, $filter ? $this->renderFilter($filter) : '');
+    }
+
+    /**
+     * Render and return the given filter as SQL-WHERE clause
+     *
+     * @param   Filter  $filter
+     *
+     * @return  string
+     */
+    public function renderFilter(Filter $filter, $level = 0)
+    {
+        // TODO: This is supposed to supersede DbQuery::renderFilter()
+        $where = '';
+        if ($filter->isChain()) {
+            if ($filter instanceof FilterAnd) {
+                $operator = ' AND ';
+            } elseif ($filter instanceof FilterOr) {
+                $operator = ' OR ';
+            } elseif ($filter instanceof FilterNot) {
+                $operator = ' AND ';
+                $where .= ' NOT ';
+            } else {
+                throw new ProgrammingError('Cannot render filter: %s', get_class($filter));
+            }
+
+            if (! $filter->isEmpty()) {
+                $parts = array();
+                foreach ($filter->filters() as $filterPart) {
+                    $part = $this->renderFilter($filterPart, $level + 1);
+                    if ($part) {
+                        $parts[] = $part;
+                    }
+                }
+
+                if (! empty($parts)) {
+                    if ($level > 0) {
+                        $where .= ' (' . implode($operator, $parts) . ') ';
+                    } else {
+                        $where .= implode($operator, $parts);
+                    }
+                }
+            } else {
+                return ''; // Explicitly return the empty string due to the FilterNot case
+            }
+        } else {
+            $where .= $this->renderFilterExpression($filter);
+        }
+
+        return $where;
+    }
+
+    /**
+     * Render and return the given filter expression
+     *
+     * @param   Filter  $filter
+     *
+     * @return  string
+     */
+    protected function renderFilterExpression(Filter $filter)
+    {
+        $column = $filter->getColumn();
+        $sign = $filter->getSign();
+        $value = $filter->getExpression();
+
+        if (is_array($value)) {
+            $comp = [];
+            $pattern = [];
+            foreach ($value as $val) {
+                if (strpos($val, '*') === false) {
+                    $comp[] = $val;
+                } else {
+                    $pattern[] = $this->renderFilterExpression(Filter::expression($column, $sign, $val));
+                }
+            }
+
+            $sql = $pattern;
+            if ($sign === '=') {
+                if (! empty($comp)) {
+                    $sql[] = $column . ' IN (' . $this->dbAdapter->quote($comp) . ')';
+                }
+
+                $operator = 'OR';
+            } elseif ($sign === '!=') {
+                if (! empty($comp)) {
+                    $sql[] = sprintf(
+                        '(%1$s NOT IN (%2$s) OR %1$s IS NULL)',
+                        $column,
+                        $this->dbAdapter->quote($comp)
+                    );
+                }
+
+                $operator = 'AND';
+            } else {
+                throw new ProgrammingError(
+                    'Unable to render array expressions with operators other than equal or not equal'
+                );
+            }
+
+            return count($sql) === 1 ? $sql[0] : '(' . implode(" $operator ", $sql) . ')';
+        } elseif ($sign === '='
+            && ! $filter instanceof FilterEqual
+            && $value !== null
+            && strpos($value, '*') !== false
+        ) {
+            if ($value === '*') {
+                return $column . ' IS NOT NULL';
+            }
+
+            return $column . ' LIKE ' . $this->dbAdapter->quote(preg_replace('~\*~', '%', $value));
+        } elseif ($sign === '!='
+            && ! $filter instanceof FilterNotEqual
+            && $value !== null
+            && strpos($value, '*') !== false
+        ) {
+            if ($value === '*') {
+                return $column . ' IS NULL';
+            }
+
+            return sprintf(
+                '(%1$s NOT LIKE %2$s OR %1$s IS NULL)',
+                $column,
+                $this->dbAdapter->quote(preg_replace('~\*~', '%', $value))
+            );
+        } elseif ($sign === '!=') {
+            return sprintf('(%1$s != %2$s OR %1$s IS NULL)', $column, $this->dbAdapter->quote($value));
+        } else {
+            return sprintf('%s %s %s', $column, $sign, $this->dbAdapter->quote($value));
+        }
+    }
+
+    public function inspect()
+    {
+        $insp = new Inspection('Db Connection');
+        try {
+            $this->getDbAdapter()->getConnection();
+            $config = $this->dbAdapter->getConfig();
+            $insp->write(sprintf(
+                'Connection to %s as %s on %s:%s successful',
+                $config['dbname'],
+                $config['username'],
+                array_key_exists('host', $config) ? $config['host'] : '(none)',
+                $config['port']
+            ));
+            switch ($this->dbType) {
+                case 'mysql':
+                    $rows = $this->dbAdapter->query(
+                        'SHOW VARIABLES WHERE variable_name ' .
+                        'IN (\'version\', \'protocol_version\', \'version_compile_os\', \'have_ssl\');'
+                    )->fetchAll();
+                    $sqlinsp = new Inspection('MySQL');
+                    $hasSsl = false;
+                    foreach ($rows as $row) {
+                        $sqlinsp->write($row->variable_name . ': ' . $row->value);
+                        if ($row->variable_name === 'have_ssl' && $row->value === 'YES') {
+                            $hasSsl = true;
+                        }
+                    }
+                    if ($hasSsl) {
+                        $ssl_rows = $this->dbAdapter->query(
+                            'SHOW STATUS WHERE variable_name ' .
+                            'IN (\'Ssl_Cipher\');'
+                        )->fetchAll();
+                        foreach ($ssl_rows as $ssl_row) {
+                            $sqlinsp->write($ssl_row->variable_name . ': ' . $ssl_row->value);
+                        }
+                    }
+                    $insp->write($sqlinsp);
+                    break;
+                case 'pgsql':
+                    $row = $this->dbAdapter->query('SELECT version();')->fetchAll();
+                    $sqlinsp = new Inspection('PostgreSQL');
+                    $sqlinsp->write($row[0]->version);
+                    $insp->write($sqlinsp);
+                    break;
+            }
+        } catch (Exception $e) {
+            return $insp->error(sprintf('Connection failed %s', $e->getMessage()));
+        }
+        return $insp;
+    }
+}
diff --git a/library/Icinga/Data/Db/DbQuery.php b/library/Icinga/Data/Db/DbQuery.php
new file mode 100644
index 0000000..30816a7
--- /dev/null
+++ b/library/Icinga/Data/Db/DbQuery.php
@@ -0,0 +1,564 @@
+<?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_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;
+    }
+}