summaryrefslogtreecommitdiffstats
path: root/library/Icinga/Data/Db/DbConnection.php
diff options
context:
space:
mode:
Diffstat (limited to 'library/Icinga/Data/Db/DbConnection.php')
-rw-r--r--library/Icinga/Data/Db/DbConnection.php655
1 files changed, 655 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;
+ }
+}
generated by cgit v1.2.3 (git 2.39.1) at 2024-11-25 19:12:19 +0000