diff options
Diffstat (limited to '')
| -rw-r--r-- | library/Director/Db.php | 755 |
1 files changed, 755 insertions, 0 deletions
diff --git a/library/Director/Db.php b/library/Director/Db.php new file mode 100644 index 0000000..2a8d6c0 --- /dev/null +++ b/library/Director/Db.php @@ -0,0 +1,755 @@ +<?php + +namespace Icinga\Module\Director; + +use DateTime; +use DateTimeZone; +use Exception; +use Icinga\Data\ResourceFactory; +use Icinga\Exception\ConfigurationError; +use Icinga\Module\Director\Data\Db\DbConnection; +use Icinga\Module\Director\Objects\IcingaEndpoint; +use Icinga\Module\Director\Objects\IcingaObject; +use RuntimeException; +use Zend_Db_Select; + +class Db extends DbConnection +{ + /** @var Settings */ + protected $settings; + + /** @var string */ + protected $masterZoneName; + + protected function db() + { + return $this->getDbAdapter(); + } + + /** + * @param $callable + * @return $this + * @throws Exception + */ + public function runFailSafeTransaction($callable) + { + if (! is_callable($callable)) { + throw new RuntimeException(__METHOD__ . ' needs a Callable'); + } + + $db = $this->db(); + $db->beginTransaction(); + try { + $callable(); + $db->commit(); + } catch (Exception $e) { + try { + $db->rollback(); + } catch (Exception $e) { + // Well... there is nothing we can do here. + } + throw $e; + } + + return $this; + } + + public static function fromResourceName($name) + { + $connection = new static(ResourceFactory::getResourceConfig($name)); + + if ($connection->isMysql()) { + $connection->setClientTimezoneForMysql(); + } elseif ($connection->isPgsql()) { + $connection->setClientTimezoneForPgsql(); + } + + return $connection; + } + + protected function getTimezoneOffset() + { + $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); + } + + protected function setClientTimezoneForMysql() + { + $db = $this->getDbAdapter(); + $db->query($db->quoteInto('SET time_zone = ?', $this->getTimezoneOffset())); + } + + protected function setClientTimezoneForPgsql() + { + $db = $this->getDbAdapter(); + $db->query($db->quoteInto('SET TIME ZONE INTERVAL ? HOUR TO MINUTE', $this->getTimezoneOffset())); + } + + public function countActivitiesSinceLastDeployedConfig(IcingaObject $object = null) + { + $db = $this->db(); + + $query = 'SELECT COUNT(*) FROM director_activity_log WHERE id > COALESCE((' + . ' SELECT id FROM director_activity_log WHERE checksum = (' + . ' SELECT last_activity_checksum FROM director_generated_config WHERE checksum = (' + . ' SELECT config_checksum FROM director_deployment_log ORDER by id desc limit 1' + . ' )' + . ' )' + . '), 0)'; + + if ($object !== null) { + $query .= $db->quoteInto(' AND object_type = ?', $object->getTableName()); + $query .= $db->quoteInto(' AND object_name = ?', $object->getObjectName()); + } + return (int) $db->fetchOne($query); + } + + // TODO: use running config?! + public function getLastDeploymentActivityLogId() + { + $db = $this->db(); + + $query = ' SELECT COALESCE(id, 0) AS id FROM director_activity_log WHERE checksum = (' + . ' SELECT last_activity_checksum FROM director_generated_config WHERE checksum = (' + . ' SELECT config_checksum FROM director_deployment_log ORDER by id desc limit 1' + . ' )' + . ')'; + + return (int) $db->fetchOne($query); + } + + public function settings() + { + if ($this->settings === null) { + $this->settings = new Settings($this); + } + + return $this->settings; + } + + public function getMasterZoneName() + { + if ($this->masterZoneName === null) { + $this->masterZoneName = $this->detectMasterZoneName(); + } + + return $this->masterZoneName; + } + + protected function detectMasterZoneName() + { + if ($zone = $this->settings()->master_zone) { + return $zone; + } + + $db = $this->db(); + $query = $db->select() + ->from('icinga_zone', 'object_name') + ->where('parent_id IS NULL') + ->where('is_global = ?', 'n'); + + $zones = $db->fetchCol($query); + + if (count($zones) === 1) { + return $zones[0]; + } + + return 'master'; + } + + public function getDefaultGlobalZoneName() + { + return $this->settings()->default_global_zone; + } + + public function hasDeploymentEndpoint() + { + $db = $this->db(); + $query = $db->select()->from( + array('z' => 'icinga_zone'), + array('cnt' => 'COUNT(*)') + )->join( + array('e' => 'icinga_endpoint'), + 'e.zone_id = z.id', + array() + )->join( + array('au' => 'icinga_apiuser'), + 'e.apiuser_id = au.id', + array() + )->where('z.object_name = ?', $this->getMasterZoneName()); + + return $db->fetchOne($query) > 0; + } + + public function getEndpointNamesInDeploymentZone() + { + $db = $this->db(); + $query = $db->select()->from( + array('z' => 'icinga_zone'), + array('object_name' => 'e.object_name') + )->join( + array('e' => 'icinga_endpoint'), + 'e.zone_id = z.id', + array() + )->join( + array('au' => 'icinga_apiuser'), + 'e.apiuser_id = au.id', + array() + )->where('z.object_name = ?', $this->getMasterZoneName()) + ->order('e.object_name ASC'); + + return $db->fetchCol($query) ?: []; + } + + public function getDeploymentEndpointName() + { + $db = $this->db(); + $query = $db->select()->from( + array('z' => 'icinga_zone'), + array('object_name' => 'e.object_name') + )->join( + array('e' => 'icinga_endpoint'), + 'e.zone_id = z.id', + array() + )->join( + array('au' => 'icinga_apiuser'), + 'e.apiuser_id = au.id', + array() + )->where('z.object_name = ?', $this->getMasterZoneName()) + ->order('e.object_name ASC') + ->limit(1); + + $name = $db->fetchOne($query); + + if (! $name) { + throw new ConfigurationError( + 'Unable to detect your deployment endpoint. I was looking for' + . ' the first endpoint configured with an assigned API user' + . ' in the "%s" zone.', + $this->getMasterZoneName() + ); + } + + return $name; + } + + /** + * @return IcingaEndpoint + */ + public function getDeploymentEndpoint() + { + return IcingaEndpoint::load($this->getDeploymentEndpointName(), $this); + } + + public function getActivitylogNeighbors($id, $type = null, $name = null) + { + $db = $this->db(); + + $greater = $db->select()->from( + array('g' => 'director_activity_log'), + array('id' => 'MIN(g.id)') + )->where('id > ?', (int) $id); + + $smaller = $db->select()->from( + array('l' => 'director_activity_log'), + array('id' => 'MAX(l.id)') + )->where('id < ?', (int) $id); + + if ($type !== null) { + $greater->where('object_type = ?', $type); + $smaller->where('object_type = ?', $type); + } + + if ($name !== null) { + $greater->where('object_name = ?', $name); + $smaller->where('object_name = ?', $name); + } + + $query = $db->select()->from( + array('gt' => $greater), + array( + 'prev' => 'lt.id', + 'next' => 'gt.id' + ) + )->join( + array('lt' => $smaller), + '1 = 1', + array() + ); + + return $db->fetchRow($query); + } + + public function fetchActivityLogEntryById($id) + { + $sql = 'SELECT id, object_type, object_name, action_name,' + . ' old_properties, new_properties, author, change_time,' + . ' UNIX_TIMESTAMP(change_time) AS change_time_ts,' + . ' %s AS checksum, %s AS parent_checksum' + . ' FROM director_activity_log WHERE id = %d'; + + $sql = sprintf( + $sql, + $this->dbHexFunc('checksum'), + $this->dbHexFunc('parent_checksum'), + $id + ); + + return $this->db()->fetchRow($sql); + } + + public function fetchActivityLogChecksumById($id, $binary = true) + { + $sql = sprintf( + 'SELECT' . ' %s AS checksum FROM director_activity_log WHERE id = %d', + $this->dbHexFunc('checksum'), + (int) $id + ); + + $result = $this->db()->fetchOne($sql); + + if ($binary) { + return hex2bin($result); + } else { + return $result; + } + } + + public function fetchActivityLogIdByChecksum($checksum) + { + $sql = 'SELECT id FROM director_activity_log WHERE checksum = ?'; + return $this->db()->fetchOne( + $this->db()->quoteInto($sql, $this->quoteBinary($checksum)) + ); + } + + public function fetchActivityLogEntry($checksum) + { + $db = $this->db(); + + $sql = 'SELECT id, object_type, object_name, action_name,' + . ' old_properties, new_properties, author, change_time,' + . ' UNIX_TIMESTAMP(change_time) AS change_time_ts,' + . ' %s AS checksum, %s AS parent_checksum' + . ' FROM director_activity_log WHERE checksum = ?'; + + $sql = sprintf( + $sql, + $this->dbHexFunc('checksum'), + $this->dbHexFunc('parent_checksum') + ); + + return $db->fetchRow( + $db->quoteInto($sql, $this->quoteBinary(hex2bin($checksum))) + ); + } + + public function getLastActivityChecksum() + { + $select = "SELECT checksum FROM (SELECT * FROM (SELECT 1 AS pos, " + . $this->dbHexFunc('checksum') + . " AS checksum" + . " FROM director_activity_log ORDER BY id DESC LIMIT 1) a" + . " UNION SELECT 2 AS pos, '' AS checksum) u ORDER BY pos LIMIT 1"; + + return $this->db()->fetchOne($select); + } + + public function fetchImportStatistics() + { + $query = "SELECT 'imported_properties' AS stat_name, COUNT(*) AS stat_value" + . " FROM import_run i" + . " JOIN imported_rowset_row rs ON i.rowset_checksum = rs.rowset_checksum" + . " JOIN imported_row_property rp ON rp.row_checksum = rs.row_checksum" + . " UNION ALL" + . " SELECT 'imported_rows' AS stat_name, COUNT(*) AS stat_value" + . " FROM import_run i" + . " JOIN imported_rowset_row rs ON i.rowset_checksum = rs.rowset_checksum" + . " UNION ALL" + . " SELECT 'unique_rows' AS stat_name, COUNT(*) AS stat_value" + . " FROM imported_row" + . " UNION ALL" + . " SELECT 'unique_properties' AS stat_name, COUNT(*) AS stat_value" + . " FROM imported_property" + ; + return $this->db()->fetchPairs($query); + } + + public function getImportrunRowsetChecksum($id) + { + $db = $this->db(); + $query = $db->select() + ->from(array('r' => 'import_run'), $this->dbHexFunc('r.rowset_checksum')) + ->where('r.id = ?', $id); + + return $db->fetchOne($query); + } + + protected function fetchTemplateRelations($type) + { + $db = $this->db(); + $query = $db->select()->from( + array('p' => 'icinga_' . $type), + array( + 'name' => 'o.object_name', + 'parent' => 'p.object_name' + ) + )->join( + array('i' => 'icinga_' . $type . '_inheritance'), + 'p.id = i.parent_' . $type . '_id', + array() + )->join( + array('o' => 'icinga_' . $type), + 'o.id = i.' . $type . '_id', + array() + )->where("o.object_type = 'template'") + ->order('p.object_name') + ->order('o.object_name'); + + return $db->fetchAll($query); + } + + public function fetchTemplateTree($type) + { + $relations = $this->fetchTemplateRelations($type); + $children = array(); + $objects = array(); + foreach ($relations as $rel) { + foreach (array('name', 'parent') as $col) { + if (! array_key_exists($rel->$col, $objects)) { + $objects[$rel->$col] = (object) array( + 'name' => $rel->$col, + 'children' => array() + ); + } + } + } + + foreach ($relations as $rel) { + $objects[$rel->parent]->children[$rel->name] = $objects[$rel->name]; + $children[$rel->name] = $rel->parent; + } + + foreach ($children as $name => $object) { + unset($objects[$name]); + } + + return $objects; + } + + public function getLatestImportedChecksum($source) + { + $db = $this->db(); + $lastRun = $db->select()->from( + array('r' => 'import_run'), + array('last_checksum' => $this->dbHexFunc('r.rowset_checksum')) + ); + + if (is_int($source) || ctype_digit($source)) { + $lastRun->where('source_id = ?', (int) $source); + } else { + $lastRun->where('source_name = ?', $source); + } + + $lastRun->order('start_time DESC')->limit(1); + return $db->fetchOne($lastRun); + } + + public function getObjectSummary() + { + $types = array( + 'host', + 'hostgroup', + 'service', + 'servicegroup', + 'user', + 'usergroup', + 'command', + 'timeperiod', + 'scheduled_downtime', + 'notification', + 'apiuser', + 'endpoint', + 'zone', + 'dependency', + ); + + $queries = array(); + $db = $this->db(); + $cnt = "COALESCE(SUM(CASE WHEN o.object_type = '%s' THEN 1 ELSE 0 END), 0)"; + + foreach ($types as $type) { + $queries[] = $db->select()->from( + array('o' => 'icinga_' . $type), + array( + 'icinga_type' => "('" . $type . "')", + 'cnt_object' => sprintf($cnt, 'object'), + 'cnt_template' => sprintf($cnt, 'template'), + 'cnt_external' => sprintf($cnt, 'external_object'), + 'cnt_total' => 'COUNT(*)', + ) + ); + } + + $query = $this->db()->select()->union($queries, Zend_Db_Select::SQL_UNION_ALL); + + $result = array(); + + foreach ($db->fetchAll($query) as $row) { + $result[$row->icinga_type] = $row; + } + + return $result; + } + + public function enumCommands() + { + return $this->enumIcingaObjects('command'); + } + + public function enumCommandTemplates() + { + return $this->enumIcingaTemplates('command'); + } + + public function enumTimeperiods() + { + return $this->enumIcingaObjects('timeperiod'); + } + + public function enumCheckcommands() + { + $filters = array( + 'methods_execute IN (?)' => array('PluginCheck', 'IcingaCheck'), + + ); + return $this->enumIcingaObjects('command', $filters); + } + + public function enumEventcommands() + { + $filters = array( + 'methods_execute = ?' => 'PluginEvent', + + ); + return $this->enumIcingaObjects('command', $filters); + } + + public function enumNotificationCommands() + { + $filters = array( + 'methods_execute IN (?)' => array('PluginNotification'), + ); + return $this->enumIcingaObjects('command', $filters); + } + + public function getZoneName($id) + { + $objects = $this->enumZones(); + return $objects[$id]; + } + + public function getCommandName($id) + { + $objects = $this->enumCommands(); + return $objects[$id]; + } + + public function enumZones() + { + return $this->enumIcingaObjects('zone'); + } + + public function enumNonglobalZones() + { + $filters = array('is_global = ?' => 'n'); + return $this->enumIcingaObjects('zone', $filters); + } + + public function enumZoneTemplates() + { + return $this->enumIcingaTemplates('zone'); + } + + public function enumHosts() + { + return $this->enumIcingaObjects('host'); + } + + public function enumHostTemplates() + { + return $this->enumIcingaTemplates('host'); + } + + public function enumHostgroups() + { + return $this->enumIcingaObjects('hostgroup'); + } + + public function enumServices() + { + return $this->enumIcingaObjects('service'); + } + + public function enumServiceTemplates() + { + return $this->enumIcingaTemplates('service'); + } + + public function enumServicegroups() + { + return $this->enumIcingaObjects('servicegroup'); + } + + public function enumUsers() + { + return $this->enumIcingaObjects('user'); + } + + public function enumUserTemplates() + { + return $this->enumIcingaTemplates('user'); + } + + public function enumUsergroups() + { + return $this->enumIcingaObjects('usergroup'); + } + + public function enumApiUsers() + { + return $this->enumIcingaObjects('apiuser'); + } + + public function enumSyncRule() + { + return $this->enum('sync_rule', array('id', 'rule_name')); + } + + public function enumImportSource() + { + return $this->enum('import_source', array('id', 'source_name')); + } + + public function enumDatalist() + { + return $this->enum('director_datalist', array('id', 'list_name')); + } + + public function enumDatafields() + { + return $this->enum('director_datafield', array( + 'id', + "caption || ' (' || varname || ')'", + )); + } + + public function enum($table, $columns = null, $filters = array()) + { + if ($columns === null) { + $columns = array('id', 'object_name'); + } + + $select = $this->db()->select()->from($table, $columns)->order($columns[1]); + foreach ($filters as $key => $val) { + $select->where($key, $val); + } + + return $this->db()->fetchPairs($select); + } + + public function enumIcingaObjects($type, $filters = array()) + { + $filters = array( + 'object_type IN (?)' => array('object', 'external_object') + ) + $filters; + + return $this->enum('icinga_' . $type, null, $filters); + } + + public function enumIcingaTemplates($type, $filters = array()) + { + $filters = array('object_type = ?' => 'template') + $filters; + return $this->enum('icinga_' . $type, null, $filters); + } + + public function fetchDistinctHostVars() + { + $select = $this->db()->select()->distinct()->from( + array('hv' => 'icinga_host_var'), + array( + 'varname' => 'hv.varname', + 'format' => 'hv.format', + 'caption' => 'df.caption', + 'datatype' => 'df.datatype' + ) + )->joinLeft( + array('df' => 'director_datafield'), + 'df.varname = hv.varname', + array() + )->order('varname'); + + return $this->db()->fetchAll($select); + } + + public function fetchDistinctServiceVars() + { + $select = $this->db()->select()->distinct()->from( + array('sv' => 'icinga_service_var'), + array( + 'varname' => 'sv.varname', + 'format' => 'sv.format', + 'caption' => 'df.caption', + 'datatype' => 'df.datatype' + ) + )->joinLeft( + array('df' => 'director_datafield'), + 'df.varname = sv.varname', + array() + )->order('varname'); + + return $this->db()->fetchAll($select); + } + + public function dbHexFunc($column) + { + if ($this->isPgsql()) { + return sprintf("LOWER(ENCODE(%s, 'hex'))", $column); + } else { + return sprintf("LOWER(HEX(%s))", $column); + } + } + + public function enumDeployedConfigs() + { + $db = $this->db(); + + $columns = array( + 'checksum' => $this->dbHexFunc('c.checksum'), + ); + + if ($this->isPgsql()) { + $columns['caption'] = 'SUBSTRING(' . $columns['checksum'] . ' FROM 1 FOR 7)'; + } else { + $columns['caption'] = 'SUBSTRING(' . $columns['checksum'] . ', 1, 7)'; + } + + $query = $db->select()->from( + array('l' => 'director_deployment_log'), + $columns + )->joinLeft( + array('c' => 'director_generated_config'), + 'c.checksum = l.config_checksum', + array() + )->order('l.start_time DESC'); + + return $db->fetchPairs($query); + } +} |