summaryrefslogtreecommitdiffstats
path: root/lib/db_ido_mysql
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2024-04-13 11:32:39 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2024-04-13 11:32:39 +0000
commit56ae875861ab260b80a030f50c4aff9f9dc8fff0 (patch)
tree531412110fc901a5918c7f7442202804a83cada9 /lib/db_ido_mysql
parentInitial commit. (diff)
downloadicinga2-56ae875861ab260b80a030f50c4aff9f9dc8fff0.tar.xz
icinga2-56ae875861ab260b80a030f50c4aff9f9dc8fff0.zip
Adding upstream version 2.14.2.upstream/2.14.2upstream
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'lib/db_ido_mysql')
-rw-r--r--lib/db_ido_mysql/CMakeLists.txt41
-rw-r--r--lib/db_ido_mysql/idomysqlconnection.cpp1269
-rw-r--r--lib/db_ido_mysql/idomysqlconnection.hpp114
-rw-r--r--lib/db_ido_mysql/idomysqlconnection.ti42
-rw-r--r--lib/db_ido_mysql/schema/mysql.sql1666
-rw-r--r--lib/db_ido_mysql/schema/upgrade/2.0.2.sql20
-rw-r--r--lib/db_ido_mysql/schema/upgrade/2.1.0.sql17
-rw-r--r--lib/db_ido_mysql/schema/upgrade/2.11.0.sql89
-rw-r--r--lib/db_ido_mysql/schema/upgrade/2.12.7.sql15
-rw-r--r--lib/db_ido_mysql/schema/upgrade/2.13.0.sql23
-rw-r--r--lib/db_ido_mysql/schema/upgrade/2.13.3.sql15
-rw-r--r--lib/db_ido_mysql/schema/upgrade/2.2.0.sql23
-rw-r--r--lib/db_ido_mysql/schema/upgrade/2.3.0.sql26
-rw-r--r--lib/db_ido_mysql/schema/upgrade/2.4.0.sql75
-rw-r--r--lib/db_ido_mysql/schema/upgrade/2.5.0.sql103
-rw-r--r--lib/db_ido_mysql/schema/upgrade/2.6.0.sql151
-rw-r--r--lib/db_ido_mysql/schema/upgrade/2.8.0.sql81
-rw-r--r--lib/db_ido_mysql/schema/upgrade/2.8.1.sql67
18 files changed, 3837 insertions, 0 deletions
diff --git a/lib/db_ido_mysql/CMakeLists.txt b/lib/db_ido_mysql/CMakeLists.txt
new file mode 100644
index 0000000..70cb90d
--- /dev/null
+++ b/lib/db_ido_mysql/CMakeLists.txt
@@ -0,0 +1,41 @@
+# Icinga 2 | (c) 2012 Icinga GmbH | GPLv2+
+
+mkclass_target(idomysqlconnection.ti idomysqlconnection-ti.cpp idomysqlconnection-ti.hpp)
+
+set(db_ido_mysql_SOURCES
+ idomysqlconnection.cpp idomysqlconnection.hpp idomysqlconnection-ti.hpp
+)
+
+if(ICINGA2_UNITY_BUILD)
+ mkunity_target(db_ido_mysql db_ido_mysql db_ido_mysql_SOURCES)
+endif()
+
+add_library(db_ido_mysql OBJECT ${db_ido_mysql_SOURCES})
+
+include_directories(${MYSQL_INCLUDE_DIR})
+
+add_dependencies(db_ido_mysql base config icinga db_ido)
+
+set_target_properties (
+ db_ido_mysql PROPERTIES
+ FOLDER Components
+)
+
+install_if_not_exists(
+ ${PROJECT_SOURCE_DIR}/etc/icinga2/features-available/ido-mysql.conf
+ ${ICINGA2_CONFIGDIR}/features-available
+)
+
+install(
+ DIRECTORY schema
+ DESTINATION ${CMAKE_INSTALL_DATADIR}/icinga2-ido-mysql
+ FILES_MATCHING PATTERN "*.sql"
+)
+
+install(
+ DIRECTORY schema/upgrade
+ DESTINATION ${CMAKE_INSTALL_DATADIR}/icinga2-ido-mysql/schema
+ FILES_MATCHING PATTERN "*.sql"
+)
+
+set(CPACK_NSIS_EXTRA_INSTALL_COMMANDS "${CPACK_NSIS_EXTRA_INSTALL_COMMANDS}" PARENT_SCOPE)
diff --git a/lib/db_ido_mysql/idomysqlconnection.cpp b/lib/db_ido_mysql/idomysqlconnection.cpp
new file mode 100644
index 0000000..aacb7d7
--- /dev/null
+++ b/lib/db_ido_mysql/idomysqlconnection.cpp
@@ -0,0 +1,1269 @@
+/* Icinga 2 | (c) 2012 Icinga GmbH | GPLv2+ */
+
+#include "db_ido_mysql/idomysqlconnection.hpp"
+#include "db_ido_mysql/idomysqlconnection-ti.cpp"
+#include "db_ido/dbtype.hpp"
+#include "db_ido/dbvalue.hpp"
+#include "base/logger.hpp"
+#include "base/objectlock.hpp"
+#include "base/convert.hpp"
+#include "base/utility.hpp"
+#include "base/perfdatavalue.hpp"
+#include "base/application.hpp"
+#include "base/configtype.hpp"
+#include "base/exception.hpp"
+#include "base/statsfunction.hpp"
+#include "base/defer.hpp"
+#include <utility>
+
+using namespace icinga;
+
+REGISTER_TYPE(IdoMysqlConnection);
+REGISTER_STATSFUNCTION(IdoMysqlConnection, &IdoMysqlConnection::StatsFunc);
+
+const char * IdoMysqlConnection::GetLatestSchemaVersion() const noexcept
+{
+ return "1.15.1";
+}
+
+const char * IdoMysqlConnection::GetCompatSchemaVersion() const noexcept
+{
+ return "1.14.3";
+}
+
+void IdoMysqlConnection::OnConfigLoaded()
+{
+ ObjectImpl<IdoMysqlConnection>::OnConfigLoaded();
+
+ m_QueryQueue.SetName("IdoMysqlConnection, " + GetName());
+
+ Library shimLibrary{"mysql_shim"};
+
+ auto create_mysql_shim = shimLibrary.GetSymbolAddress<create_mysql_shim_ptr>("create_mysql_shim");
+
+ m_Mysql.reset(create_mysql_shim());
+
+ std::swap(m_Library, shimLibrary);
+}
+
+void IdoMysqlConnection::StatsFunc(const Dictionary::Ptr& status, const Array::Ptr& perfdata)
+{
+ DictionaryData nodes;
+
+ for (const IdoMysqlConnection::Ptr& idomysqlconnection : ConfigType::GetObjectsByType<IdoMysqlConnection>()) {
+ size_t queryQueueItems = idomysqlconnection->m_QueryQueue.GetLength();
+ double queryQueueItemRate = idomysqlconnection->m_QueryQueue.GetTaskCount(60) / 60.0;
+
+ nodes.emplace_back(idomysqlconnection->GetName(), new Dictionary({
+ { "version", idomysqlconnection->GetSchemaVersion() },
+ { "instance_name", idomysqlconnection->GetInstanceName() },
+ { "connected", idomysqlconnection->GetConnected() },
+ { "query_queue_items", queryQueueItems },
+ { "query_queue_item_rate", queryQueueItemRate }
+ }));
+
+ perfdata->Add(new PerfdataValue("idomysqlconnection_" + idomysqlconnection->GetName() + "_queries_rate", idomysqlconnection->GetQueryCount(60) / 60.0));
+ perfdata->Add(new PerfdataValue("idomysqlconnection_" + idomysqlconnection->GetName() + "_queries_1min", idomysqlconnection->GetQueryCount(60)));
+ perfdata->Add(new PerfdataValue("idomysqlconnection_" + idomysqlconnection->GetName() + "_queries_5mins", idomysqlconnection->GetQueryCount(5 * 60)));
+ perfdata->Add(new PerfdataValue("idomysqlconnection_" + idomysqlconnection->GetName() + "_queries_15mins", idomysqlconnection->GetQueryCount(15 * 60)));
+ perfdata->Add(new PerfdataValue("idomysqlconnection_" + idomysqlconnection->GetName() + "_query_queue_items", queryQueueItems));
+ perfdata->Add(new PerfdataValue("idomysqlconnection_" + idomysqlconnection->GetName() + "_query_queue_item_rate", queryQueueItemRate));
+ }
+
+ status->Set("idomysqlconnection", new Dictionary(std::move(nodes)));
+}
+
+void IdoMysqlConnection::Resume()
+{
+ Log(LogInformation, "IdoMysqlConnection")
+ << "'" << GetName() << "' resumed.";
+
+ SetConnected(false);
+
+ m_QueryQueue.SetExceptionCallback([this](boost::exception_ptr exp) { ExceptionHandler(std::move(exp)); });
+
+ /* Immediately try to connect on Resume() without timer. */
+ m_QueryQueue.Enqueue([this]() { Reconnect(); }, PriorityImmediate);
+
+ m_TxTimer = Timer::Create();
+ m_TxTimer->SetInterval(1);
+ m_TxTimer->OnTimerExpired.connect([this](const Timer * const&) { NewTransaction(); });
+ m_TxTimer->Start();
+
+ m_ReconnectTimer = Timer::Create();
+ m_ReconnectTimer->SetInterval(10);
+ m_ReconnectTimer->OnTimerExpired.connect([this](const Timer * const&){ ReconnectTimerHandler(); });
+ m_ReconnectTimer->Start();
+
+ /* Start with queries after connect. */
+ DbConnection::Resume();
+
+ ASSERT(m_Mysql->thread_safe());
+}
+
+void IdoMysqlConnection::Pause()
+{
+ Log(LogDebug, "IdoMysqlConnection")
+ << "Attempting to pause '" << GetName() << "'.";
+
+ DbConnection::Pause();
+
+ m_ReconnectTimer->Stop(true);
+ m_TxTimer->Stop(true);
+
+#ifdef I2_DEBUG /* I2_DEBUG */
+ Log(LogDebug, "IdoMysqlConnection")
+ << "Rescheduling disconnect task.";
+#endif /* I2_DEBUG */
+
+ Log(LogInformation, "IdoMysqlConnection")
+ << "'" << GetName() << "' paused.";
+
+}
+
+void IdoMysqlConnection::ExceptionHandler(boost::exception_ptr exp)
+{
+ Log(LogCritical, "IdoMysqlConnection", "Exception during database operation: Verify that your database is operational!");
+
+ Log(LogDebug, "IdoMysqlConnection")
+ << "Exception during database operation: " << DiagnosticInformation(std::move(exp));
+
+ if (GetConnected()) {
+ m_Mysql->close(&m_Connection);
+
+ SetConnected(false);
+ }
+}
+
+void IdoMysqlConnection::AssertOnWorkQueue()
+{
+ ASSERT(m_QueryQueue.IsWorkerThread());
+}
+
+void IdoMysqlConnection::Disconnect()
+{
+ AssertOnWorkQueue();
+
+ if (!GetConnected())
+ return;
+
+ Query("COMMIT");
+ m_Mysql->close(&m_Connection);
+
+ SetConnected(false);
+
+ Log(LogInformation, "IdoMysqlConnection")
+ << "Disconnected from '" << GetName() << "' database '" << GetDatabase() << "'.";
+}
+
+void IdoMysqlConnection::NewTransaction()
+{
+ if (IsPaused() && GetPauseCalled())
+ return;
+
+#ifdef I2_DEBUG /* I2_DEBUG */
+ Log(LogDebug, "IdoMysqlConnection")
+ << "Scheduling new transaction and finishing async queries.";
+#endif /* I2_DEBUG */
+
+ m_QueryQueue.Enqueue([this]() { InternalNewTransaction(); }, PriorityHigh);
+}
+
+void IdoMysqlConnection::InternalNewTransaction()
+{
+ AssertOnWorkQueue();
+
+ if (!GetConnected())
+ return;
+
+ IncreasePendingQueries(2);
+
+ AsyncQuery("COMMIT");
+ AsyncQuery("BEGIN");
+
+ FinishAsyncQueries();
+}
+
+void IdoMysqlConnection::ReconnectTimerHandler()
+{
+#ifdef I2_DEBUG /* I2_DEBUG */
+ Log(LogDebug, "IdoMysqlConnection")
+ << "Scheduling reconnect task.";
+#endif /* I2_DEBUG */
+
+ /* Only allow Reconnect events with high priority. */
+ m_QueryQueue.Enqueue([this]() { Reconnect(); }, PriorityImmediate);
+}
+
+void IdoMysqlConnection::Reconnect()
+{
+ AssertOnWorkQueue();
+
+ if (!IsActive())
+ return;
+
+ CONTEXT("Reconnecting to MySQL IDO database '" << GetName() << "'");
+
+ double startTime = Utility::GetTime();
+
+ SetShouldConnect(true);
+
+ bool reconnect = false;
+
+ /* Ensure to close old connections first. */
+ if (GetConnected()) {
+ /* Check if we're really still connected */
+ if (m_Mysql->ping(&m_Connection) == 0)
+ return;
+
+ m_Mysql->close(&m_Connection);
+ SetConnected(false);
+ reconnect = true;
+ }
+
+ Log(LogDebug, "IdoMysqlConnection")
+ << "Reconnect: Clearing ID cache.";
+
+ ClearIDCache();
+
+ String ihost, isocket_path, iuser, ipasswd, idb;
+ String isslKey, isslCert, isslCa, isslCaPath, isslCipher;
+ const char *host, *socket_path, *user , *passwd, *db;
+ const char *sslKey, *sslCert, *sslCa, *sslCaPath, *sslCipher;
+ bool enableSsl;
+ long port;
+
+ ihost = GetHost();
+ isocket_path = GetSocketPath();
+ iuser = GetUser();
+ ipasswd = GetPassword();
+ idb = GetDatabase();
+
+ enableSsl = GetEnableSsl();
+ isslKey = GetSslKey();
+ isslCert = GetSslCert();
+ isslCa = GetSslCa();
+ isslCaPath = GetSslCapath();
+ isslCipher = GetSslCipher();
+
+ host = (!ihost.IsEmpty()) ? ihost.CStr() : nullptr;
+ port = GetPort();
+ socket_path = (!isocket_path.IsEmpty()) ? isocket_path.CStr() : nullptr;
+ user = (!iuser.IsEmpty()) ? iuser.CStr() : nullptr;
+ passwd = (!ipasswd.IsEmpty()) ? ipasswd.CStr() : nullptr;
+ db = (!idb.IsEmpty()) ? idb.CStr() : nullptr;
+
+ sslKey = (!isslKey.IsEmpty()) ? isslKey.CStr() : nullptr;
+ sslCert = (!isslCert.IsEmpty()) ? isslCert.CStr() : nullptr;
+ sslCa = (!isslCa.IsEmpty()) ? isslCa.CStr() : nullptr;
+ sslCaPath = (!isslCaPath.IsEmpty()) ? isslCaPath.CStr() : nullptr;
+ sslCipher = (!isslCipher.IsEmpty()) ? isslCipher.CStr() : nullptr;
+
+ /* connection */
+ if (!m_Mysql->init(&m_Connection)) {
+ Log(LogCritical, "IdoMysqlConnection")
+ << "mysql_init() failed: out of memory";
+
+ BOOST_THROW_EXCEPTION(std::bad_alloc());
+ }
+
+ /* Read "latin1" (here, in the schema and in Icinga Web) as "bytes".
+ Icinga 2 and Icinga Web use byte-strings everywhere and every byte-string is a valid latin1 string.
+ This way the (actually mostly UTF-8) bytes are transferred end-to-end as-is. */
+ m_Mysql->options(&m_Connection, MYSQL_SET_CHARSET_NAME, "latin1");
+
+ if (enableSsl)
+ m_Mysql->ssl_set(&m_Connection, sslKey, sslCert, sslCa, sslCaPath, sslCipher);
+
+ if (!m_Mysql->real_connect(&m_Connection, host, user, passwd, db, port, socket_path, CLIENT_FOUND_ROWS | CLIENT_MULTI_STATEMENTS)) {
+ Log(LogCritical, "IdoMysqlConnection")
+ << "Connection to database '" << db << "' with user '" << user << "' on '" << host << ":" << port
+ << "' " << (enableSsl ? "(SSL enabled) " : "") << "failed: \"" << m_Mysql->error(&m_Connection) << "\"";
+
+ BOOST_THROW_EXCEPTION(std::runtime_error(m_Mysql->error(&m_Connection)));
+ }
+
+ Log(LogNotice, "IdoMysqlConnection")
+ << "Reconnect: '" << GetName() << "' is now connected to database '" << GetDatabase() << "'.";
+
+ SetConnected(true);
+
+ IdoMysqlResult result = Query("SELECT @@global.max_allowed_packet AS max_allowed_packet");
+
+ Dictionary::Ptr row = FetchRow(result);
+
+ if (row)
+ m_MaxPacketSize = row->Get("max_allowed_packet");
+ else
+ m_MaxPacketSize = 64 * 1024;
+
+ DiscardRows(result);
+
+ String dbVersionName = "idoutils";
+ result = Query("SELECT version FROM " + GetTablePrefix() + "dbversion WHERE name='" + Escape(dbVersionName) + "'");
+
+ row = FetchRow(result);
+
+ if (!row) {
+ m_Mysql->close(&m_Connection);
+ SetConnected(false);
+
+ Log(LogCritical, "IdoMysqlConnection", "Schema does not provide any valid version! Verify your schema installation.");
+
+ BOOST_THROW_EXCEPTION(std::runtime_error("Invalid schema."));
+ }
+
+ DiscardRows(result);
+
+ String version = row->Get("version");
+
+ SetSchemaVersion(version);
+
+ if (Utility::CompareVersion(GetCompatSchemaVersion(), version) < 0) {
+ m_Mysql->close(&m_Connection);
+ SetConnected(false);
+
+ Log(LogCritical, "IdoMysqlConnection")
+ << "Schema version '" << version << "' does not match the required version '"
+ << GetCompatSchemaVersion() << "' (or newer)! Please check the upgrade documentation at "
+ << "https://icinga.com/docs/icinga2/latest/doc/16-upgrading-icinga-2/#upgrading-mysql-db";
+
+ BOOST_THROW_EXCEPTION(std::runtime_error("Schema version mismatch."));
+ }
+
+ String instanceName = GetInstanceName();
+
+ result = Query("SELECT instance_id FROM " + GetTablePrefix() + "instances WHERE instance_name = '" + Escape(instanceName) + "'");
+ row = FetchRow(result);
+
+ if (!row) {
+ Query("INSERT INTO " + GetTablePrefix() + "instances (instance_name, instance_description) VALUES ('" + Escape(instanceName) + "', '" + Escape(GetInstanceDescription()) + "')");
+ m_InstanceID = GetLastInsertID();
+ } else {
+ m_InstanceID = DbReference(row->Get("instance_id"));
+ }
+
+ DiscardRows(result);
+
+ Endpoint::Ptr my_endpoint = Endpoint::GetLocalEndpoint();
+
+ /* we have an endpoint in a cluster setup, so decide if we can proceed here */
+ if (my_endpoint && GetHAMode() == HARunOnce) {
+ /* get the current endpoint writing to programstatus table */
+ result = Query("SELECT UNIX_TIMESTAMP(status_update_time) AS status_update_time, endpoint_name FROM " +
+ GetTablePrefix() + "programstatus WHERE instance_id = " + Convert::ToString(m_InstanceID));
+ row = FetchRow(result);
+ DiscardRows(result);
+
+ String endpoint_name;
+
+ if (row)
+ endpoint_name = row->Get("endpoint_name");
+ else
+ Log(LogNotice, "IdoMysqlConnection", "Empty program status table");
+
+ /* if we did not write into the database earlier, another instance is active */
+ if (endpoint_name != my_endpoint->GetName()) {
+ double status_update_time;
+
+ if (row)
+ status_update_time = row->Get("status_update_time");
+ else
+ status_update_time = 0;
+
+ double now = Utility::GetTime();
+
+ double status_update_age = now - status_update_time;
+ double failoverTimeout = GetFailoverTimeout();
+
+ if (status_update_age < failoverTimeout) {
+ Log(LogInformation, "IdoMysqlConnection")
+ << "Last update by endpoint '" << endpoint_name << "' was "
+ << status_update_age << "s ago (< failover timeout of " << failoverTimeout << "s). Retrying.";
+
+ m_Mysql->close(&m_Connection);
+ SetConnected(false);
+ SetShouldConnect(false);
+
+ return;
+ }
+
+ /* activate the IDO only, if we're authoritative in this zone */
+ if (IsPaused()) {
+ Log(LogNotice, "IdoMysqlConnection")
+ << "Local endpoint '" << my_endpoint->GetName() << "' is not authoritative, bailing out.";
+
+ m_Mysql->close(&m_Connection);
+ SetConnected(false);
+
+ return;
+ }
+
+ SetLastFailover(now);
+
+ Log(LogInformation, "IdoMysqlConnection")
+ << "Last update by endpoint '" << endpoint_name << "' was "
+ << status_update_age << "s ago. Taking over '" << GetName() << "' in HA zone '" << Zone::GetLocalZone()->GetName() << "'.";
+ }
+
+ Log(LogNotice, "IdoMysqlConnection", "Enabling IDO connection in HA zone.");
+ }
+
+ Log(LogInformation, "IdoMysqlConnection")
+ << "MySQL IDO instance id: " << static_cast<long>(m_InstanceID) << " (schema version: '" + version + "')";
+
+ /* set session time zone to utc */
+ Query("SET SESSION TIME_ZONE='+00:00'");
+
+ Query("SET SESSION SQL_MODE='NO_AUTO_VALUE_ON_ZERO'");
+
+ Query("BEGIN");
+
+ /* update programstatus table */
+ UpdateProgramStatus();
+
+ /* record connection */
+ Query("INSERT INTO " + GetTablePrefix() + "conninfo " +
+ "(instance_id, connect_time, last_checkin_time, agent_name, agent_version, connect_type, data_start_time) VALUES ("
+ + Convert::ToString(static_cast<long>(m_InstanceID)) + ", NOW(), NOW(), 'icinga2 db_ido_mysql', '" + Escape(Application::GetAppVersion())
+ + "', '" + (reconnect ? "RECONNECT" : "INITIAL") + "', NOW())");
+
+ /* clear config tables for the initial config dump */
+ PrepareDatabase();
+
+ std::ostringstream q1buf;
+ q1buf << "SELECT object_id, objecttype_id, name1, name2, is_active FROM " + GetTablePrefix() + "objects WHERE instance_id = " << static_cast<long>(m_InstanceID);
+ result = Query(q1buf.str());
+
+ std::vector<DbObject::Ptr> activeDbObjs;
+
+ while ((row = FetchRow(result))) {
+ DbType::Ptr dbtype = DbType::GetByID(row->Get("objecttype_id"));
+
+ if (!dbtype)
+ continue;
+
+ DbObject::Ptr dbobj = dbtype->GetOrCreateObjectByName(row->Get("name1"), row->Get("name2"));
+ SetObjectID(dbobj, DbReference(row->Get("object_id")));
+ bool active = row->Get("is_active");
+ SetObjectActive(dbobj, active);
+
+ if (active)
+ activeDbObjs.emplace_back(std::move(dbobj));
+ }
+
+ SetIDCacheValid(true);
+
+ EnableActiveChangedHandler();
+
+ for (const DbObject::Ptr& dbobj : activeDbObjs) {
+ if (dbobj->GetObject())
+ continue;
+
+ Log(LogNotice, "IdoMysqlConnection")
+ << "Deactivate deleted object name1: '" << dbobj->GetName1()
+ << "' name2: '" << dbobj->GetName2() + "'.";
+ DeactivateObject(dbobj);
+ }
+
+ UpdateAllObjects();
+
+#ifdef I2_DEBUG /* I2_DEBUG */
+ Log(LogDebug, "IdoMysqlConnection")
+ << "Scheduling session table clear and finish connect task.";
+#endif /* I2_DEBUG */
+
+ m_QueryQueue.Enqueue([this]() { ClearTablesBySession(); }, PriorityNormal);
+
+ m_QueryQueue.Enqueue([this, startTime]() { FinishConnect(startTime); }, PriorityNormal);
+}
+
+void IdoMysqlConnection::FinishConnect(double startTime)
+{
+ AssertOnWorkQueue();
+
+ if (!GetConnected() || IsPaused())
+ return;
+
+ FinishAsyncQueries();
+
+ Log(LogInformation, "IdoMysqlConnection")
+ << "Finished reconnecting to '" << GetName() << "' database '" << GetDatabase() << "' in "
+ << std::setw(2) << Utility::GetTime() - startTime << " second(s).";
+
+ Query("COMMIT");
+ Query("BEGIN");
+}
+
+void IdoMysqlConnection::ClearTablesBySession()
+{
+ /* delete all comments and downtimes without current session token */
+ ClearTableBySession("comments");
+ ClearTableBySession("scheduleddowntime");
+}
+
+void IdoMysqlConnection::ClearTableBySession(const String& table)
+{
+ Query("DELETE FROM " + GetTablePrefix() + table + " WHERE instance_id = " +
+ Convert::ToString(static_cast<long>(m_InstanceID)) + " AND session_token <> " +
+ Convert::ToString(GetSessionToken()));
+}
+
+void IdoMysqlConnection::AsyncQuery(const String& query, const std::function<void (const IdoMysqlResult&)>& callback)
+{
+ AssertOnWorkQueue();
+
+ IdoAsyncQuery aq;
+ aq.Query = query;
+ /* XXX: Important: The callback must not immediately execute a query, but enqueue it!
+ * See https://github.com/Icinga/icinga2/issues/4603 for details.
+ */
+ aq.Callback = callback;
+ m_AsyncQueries.emplace_back(std::move(aq));
+}
+
+void IdoMysqlConnection::FinishAsyncQueries()
+{
+ std::vector<IdoAsyncQuery> queries;
+ m_AsyncQueries.swap(queries);
+
+ std::vector<IdoAsyncQuery>::size_type offset = 0;
+
+ // This will be executed if there is a problem with executing the queries,
+ // at which point this function throws an exception and the queries should
+ // not be listed as still pending in the queue.
+ Defer decreaseQueries ([this, &offset, &queries]() {
+ auto lostQueries = queries.size() - offset;
+
+ if (lostQueries > 0) {
+ DecreasePendingQueries(lostQueries);
+ }
+ });
+
+ while (offset < queries.size()) {
+ std::ostringstream querybuf;
+
+ std::vector<IdoAsyncQuery>::size_type count = 0;
+ size_t num_bytes = 0;
+
+ Defer decreaseQueries ([this, &offset, &count]() {
+ offset += count;
+ DecreasePendingQueries(count);
+ m_UncommittedAsyncQueries += count;
+ });
+
+ for (std::vector<IdoAsyncQuery>::size_type i = offset; i < queries.size(); i++) {
+ const IdoAsyncQuery& aq = queries[i];
+
+ size_t size_query = aq.Query.GetLength() + 1;
+
+ if (count > 0) {
+ if (num_bytes + size_query > m_MaxPacketSize - 512)
+ break;
+
+ querybuf << ";";
+ }
+
+ IncreaseQueryCount();
+ count++;
+
+ Log(LogDebug, "IdoMysqlConnection")
+ << "Query: " << aq.Query;
+
+ querybuf << aq.Query;
+ num_bytes += size_query;
+ }
+
+ String query = querybuf.str();
+
+ if (m_Mysql->query(&m_Connection, query.CStr()) != 0) {
+ std::ostringstream msgbuf;
+ String message = m_Mysql->error(&m_Connection);
+ msgbuf << "Error \"" << message << "\" when executing query \"" << query << "\"";
+ Log(LogCritical, "IdoMysqlConnection", msgbuf.str());
+
+ BOOST_THROW_EXCEPTION(
+ database_error()
+ << errinfo_message(m_Mysql->error(&m_Connection))
+ << errinfo_database_query(query)
+ );
+ }
+
+ for (std::vector<IdoAsyncQuery>::size_type i = offset; i < offset + count; i++) {
+ const IdoAsyncQuery& aq = queries[i];
+
+ MYSQL_RES *result = m_Mysql->store_result(&m_Connection);
+
+ m_AffectedRows = m_Mysql->affected_rows(&m_Connection);
+
+ IdoMysqlResult iresult;
+
+ if (!result) {
+ if (m_Mysql->field_count(&m_Connection) > 0) {
+ std::ostringstream msgbuf;
+ String message = m_Mysql->error(&m_Connection);
+ msgbuf << "Error \"" << message << "\" when executing query \"" << aq.Query << "\"";
+ Log(LogCritical, "IdoMysqlConnection", msgbuf.str());
+
+ BOOST_THROW_EXCEPTION(
+ database_error()
+ << errinfo_message(m_Mysql->error(&m_Connection))
+ << errinfo_database_query(query)
+ );
+ }
+ } else
+ iresult = IdoMysqlResult(result, [this](MYSQL_RES* result) { m_Mysql->free_result(result); });
+
+ if (aq.Callback)
+ aq.Callback(iresult);
+
+ if (m_Mysql->next_result(&m_Connection) > 0) {
+ std::ostringstream msgbuf;
+ String message = m_Mysql->error(&m_Connection);
+ msgbuf << "Error \"" << message << "\" when executing query \"" << query << "\"";
+ Log(LogCritical, "IdoMysqlConnection", msgbuf.str());
+
+ BOOST_THROW_EXCEPTION(
+ database_error()
+ << errinfo_message(m_Mysql->error(&m_Connection))
+ << errinfo_database_query(query)
+ );
+ }
+ }
+ }
+
+ if (m_UncommittedAsyncQueries > 25000) {
+ m_UncommittedAsyncQueries = 0;
+
+ Query("COMMIT");
+ Query("BEGIN");
+ }
+}
+
+IdoMysqlResult IdoMysqlConnection::Query(const String& query)
+{
+ AssertOnWorkQueue();
+
+ IncreasePendingQueries(1);
+ Defer decreaseQueries ([this]() { DecreasePendingQueries(1); });
+
+ /* finish all async queries to maintain the right order for queries */
+ FinishAsyncQueries();
+
+ Log(LogDebug, "IdoMysqlConnection")
+ << "Query: " << query;
+
+ IncreaseQueryCount();
+
+ if (m_Mysql->query(&m_Connection, query.CStr()) != 0) {
+ std::ostringstream msgbuf;
+ String message = m_Mysql->error(&m_Connection);
+ msgbuf << "Error \"" << message << "\" when executing query \"" << query << "\"";
+ Log(LogCritical, "IdoMysqlConnection", msgbuf.str());
+
+ BOOST_THROW_EXCEPTION(
+ database_error()
+ << errinfo_message(m_Mysql->error(&m_Connection))
+ << errinfo_database_query(query)
+ );
+ }
+
+ MYSQL_RES *result = m_Mysql->store_result(&m_Connection);
+
+ m_AffectedRows = m_Mysql->affected_rows(&m_Connection);
+
+ if (!result) {
+ if (m_Mysql->field_count(&m_Connection) > 0) {
+ std::ostringstream msgbuf;
+ String message = m_Mysql->error(&m_Connection);
+ msgbuf << "Error \"" << message << "\" when executing query \"" << query << "\"";
+ Log(LogCritical, "IdoMysqlConnection", msgbuf.str());
+
+ BOOST_THROW_EXCEPTION(
+ database_error()
+ << errinfo_message(m_Mysql->error(&m_Connection))
+ << errinfo_database_query(query)
+ );
+ }
+
+ return IdoMysqlResult();
+ }
+
+ return IdoMysqlResult(result, [this](MYSQL_RES* result) { m_Mysql->free_result(result); });
+}
+
+DbReference IdoMysqlConnection::GetLastInsertID()
+{
+ AssertOnWorkQueue();
+
+ return {static_cast<long>(m_Mysql->insert_id(&m_Connection))};
+}
+
+int IdoMysqlConnection::GetAffectedRows()
+{
+ AssertOnWorkQueue();
+
+ return m_AffectedRows;
+}
+
+String IdoMysqlConnection::Escape(const String& s)
+{
+ AssertOnWorkQueue();
+
+ String utf8s = Utility::ValidateUTF8(s);
+
+ size_t length = utf8s.GetLength();
+ auto *to = new char[utf8s.GetLength() * 2 + 1];
+
+ m_Mysql->real_escape_string(&m_Connection, to, utf8s.CStr(), length);
+
+ String result = String(to);
+
+ delete [] to;
+
+ return result;
+}
+
+Dictionary::Ptr IdoMysqlConnection::FetchRow(const IdoMysqlResult& result)
+{
+ AssertOnWorkQueue();
+
+ MYSQL_ROW row;
+ MYSQL_FIELD *field;
+ unsigned long *lengths, i;
+
+ row = m_Mysql->fetch_row(result.get());
+
+ if (!row)
+ return nullptr;
+
+ lengths = m_Mysql->fetch_lengths(result.get());
+
+ if (!lengths)
+ return nullptr;
+
+ Dictionary::Ptr dict = new Dictionary();
+
+ m_Mysql->field_seek(result.get(), 0);
+ for (field = m_Mysql->fetch_field(result.get()), i = 0; field; field = m_Mysql->fetch_field(result.get()), i++)
+ dict->Set(field->name, String(row[i], row[i] + lengths[i]));
+
+ return dict;
+}
+
+void IdoMysqlConnection::DiscardRows(const IdoMysqlResult& result)
+{
+ Dictionary::Ptr row;
+
+ while ((row = FetchRow(result)))
+ ; /* empty loop body */
+}
+
+void IdoMysqlConnection::ActivateObject(const DbObject::Ptr& dbobj)
+{
+ if (IsPaused())
+ return;
+
+#ifdef I2_DEBUG /* I2_DEBUG */
+ Log(LogDebug, "IdoMysqlConnection")
+ << "Scheduling object activation task for '" << dbobj->GetName1() << "!" << dbobj->GetName2() << "'.";
+#endif /* I2_DEBUG */
+
+ m_QueryQueue.Enqueue([this, dbobj]() { InternalActivateObject(dbobj); }, PriorityNormal);
+}
+
+void IdoMysqlConnection::InternalActivateObject(const DbObject::Ptr& dbobj)
+{
+ AssertOnWorkQueue();
+
+ if (IsPaused())
+ return;
+
+ if (!GetConnected())
+ return;
+
+ DbReference dbref = GetObjectID(dbobj);
+ std::ostringstream qbuf;
+
+ if (!dbref.IsValid()) {
+ if (!dbobj->GetName2().IsEmpty()) {
+ qbuf << "INSERT INTO " + GetTablePrefix() + "objects (instance_id, objecttype_id, name1, name2, is_active) VALUES ("
+ << static_cast<long>(m_InstanceID) << ", " << dbobj->GetType()->GetTypeID() << ", "
+ << "'" << Escape(dbobj->GetName1()) << "', '" << Escape(dbobj->GetName2()) << "', 1)";
+ } else {
+ qbuf << "INSERT INTO " + GetTablePrefix() + "objects (instance_id, objecttype_id, name1, is_active) VALUES ("
+ << static_cast<long>(m_InstanceID) << ", " << dbobj->GetType()->GetTypeID() << ", "
+ << "'" << Escape(dbobj->GetName1()) << "', 1)";
+ }
+
+ Query(qbuf.str());
+ SetObjectID(dbobj, GetLastInsertID());
+ } else {
+ qbuf << "UPDATE " + GetTablePrefix() + "objects SET is_active = 1 WHERE object_id = " << static_cast<long>(dbref);
+ IncreasePendingQueries(1);
+ AsyncQuery(qbuf.str());
+ }
+}
+
+void IdoMysqlConnection::DeactivateObject(const DbObject::Ptr& dbobj)
+{
+ if (IsPaused())
+ return;
+
+#ifdef I2_DEBUG /* I2_DEBUG */
+ Log(LogDebug, "IdoMysqlConnection")
+ << "Scheduling object deactivation task for '" << dbobj->GetName1() << "!" << dbobj->GetName2() << "'.";
+#endif /* I2_DEBUG */
+
+ m_QueryQueue.Enqueue([this, dbobj]() { InternalDeactivateObject(dbobj); }, PriorityNormal);
+}
+
+void IdoMysqlConnection::InternalDeactivateObject(const DbObject::Ptr& dbobj)
+{
+ AssertOnWorkQueue();
+
+ if (IsPaused())
+ return;
+
+ if (!GetConnected())
+ return;
+
+ DbReference dbref = GetObjectID(dbobj);
+
+ if (!dbref.IsValid())
+ return;
+
+ std::ostringstream qbuf;
+ qbuf << "UPDATE " + GetTablePrefix() + "objects SET is_active = 0 WHERE object_id = " << static_cast<long>(dbref);
+ IncreasePendingQueries(1);
+ AsyncQuery(qbuf.str());
+
+ /* Note that we're _NOT_ clearing the db refs via SetReference/SetConfigUpdate/SetStatusUpdate
+ * because the object is still in the database. */
+
+ SetObjectActive(dbobj, false);
+}
+
+bool IdoMysqlConnection::FieldToEscapedString(const String& key, const Value& value, Value *result)
+{
+ if (key == "instance_id") {
+ *result = static_cast<long>(m_InstanceID);
+ return true;
+ } else if (key == "session_token") {
+ *result = GetSessionToken();
+ return true;
+ }
+
+ Value rawvalue = DbValue::ExtractValue(value);
+
+ if (rawvalue.GetType() == ValueEmpty) {
+ *result = "NULL";
+ } else if (rawvalue.IsObjectType<ConfigObject>()) {
+ DbObject::Ptr dbobjcol = DbObject::GetOrCreateByObject(rawvalue);
+
+ if (!dbobjcol) {
+ *result = 0;
+ return true;
+ }
+
+ if (!IsIDCacheValid())
+ return false;
+
+ DbReference dbrefcol;
+
+ if (DbValue::IsObjectInsertID(value)) {
+ dbrefcol = GetInsertID(dbobjcol);
+
+ if (!dbrefcol.IsValid())
+ return false;
+ } else {
+ dbrefcol = GetObjectID(dbobjcol);
+
+ if (!dbrefcol.IsValid()) {
+ InternalActivateObject(dbobjcol);
+
+ dbrefcol = GetObjectID(dbobjcol);
+
+ if (!dbrefcol.IsValid())
+ return false;
+ }
+ }
+
+ *result = static_cast<long>(dbrefcol);
+ } else if (DbValue::IsTimestamp(value)) {
+ long ts = rawvalue;
+ std::ostringstream msgbuf;
+ msgbuf << "FROM_UNIXTIME(" << ts << ")";
+ *result = Value(msgbuf.str());
+ } else if (DbValue::IsObjectInsertID(value)) {
+ auto id = static_cast<long>(rawvalue);
+
+ if (id <= 0)
+ return false;
+
+ *result = id;
+ return true;
+ } else {
+ Value fvalue;
+
+ if (rawvalue.IsBoolean())
+ fvalue = Convert::ToLong(rawvalue);
+ else
+ fvalue = rawvalue;
+
+ *result = "'" + Escape(fvalue) + "'";
+ }
+
+ return true;
+}
+
+void IdoMysqlConnection::ExecuteQuery(const DbQuery& query)
+{
+ if (IsPaused() && GetPauseCalled())
+ return;
+
+ ASSERT(query.Category != DbCatInvalid);
+
+#ifdef I2_DEBUG /* I2_DEBUG */
+ Log(LogDebug, "IdoMysqlConnection")
+ << "Scheduling execute query task, type " << query.Type << ", table '" << query.Table << "'.";
+#endif /* I2_DEBUG */
+
+ IncreasePendingQueries(1);
+ m_QueryQueue.Enqueue([this, query]() { InternalExecuteQuery(query, -1); }, query.Priority, true);
+}
+
+void IdoMysqlConnection::ExecuteMultipleQueries(const std::vector<DbQuery>& queries)
+{
+ if (IsPaused())
+ return;
+
+ if (queries.empty())
+ return;
+
+#ifdef I2_DEBUG /* I2_DEBUG */
+ Log(LogDebug, "IdoMysqlConnection")
+ << "Scheduling multiple execute query task, type " << queries[0].Type << ", table '" << queries[0].Table << "'.";
+#endif /* I2_DEBUG */
+
+ IncreasePendingQueries(queries.size());
+ m_QueryQueue.Enqueue([this, queries]() { InternalExecuteMultipleQueries(queries); }, queries[0].Priority, true);
+}
+
+bool IdoMysqlConnection::CanExecuteQuery(const DbQuery& query)
+{
+ if (query.Object && !IsIDCacheValid())
+ return false;
+
+ if (query.WhereCriteria) {
+ ObjectLock olock(query.WhereCriteria);
+ Value value;
+
+ for (const Dictionary::Pair& kv : query.WhereCriteria) {
+ if (!FieldToEscapedString(kv.first, kv.second, &value))
+ return false;
+ }
+ }
+
+ if (query.Fields) {
+ ObjectLock olock(query.Fields);
+
+ for (const Dictionary::Pair& kv : query.Fields) {
+ Value value;
+
+ if (!FieldToEscapedString(kv.first, kv.second, &value))
+ return false;
+ }
+ }
+
+ return true;
+}
+
+void IdoMysqlConnection::InternalExecuteMultipleQueries(const std::vector<DbQuery>& queries)
+{
+ AssertOnWorkQueue();
+
+ if (IsPaused()) {
+ DecreasePendingQueries(queries.size());
+ return;
+ }
+
+ if (!GetConnected()) {
+ DecreasePendingQueries(queries.size());
+ return;
+ }
+
+
+ for (const DbQuery& query : queries) {
+ ASSERT(query.Type == DbQueryNewTransaction || query.Category != DbCatInvalid);
+
+ if (!CanExecuteQuery(query)) {
+
+#ifdef I2_DEBUG /* I2_DEBUG */
+ Log(LogDebug, "IdoMysqlConnection")
+ << "Scheduling multiple execute query task again: Cannot execute query now. Type '"
+ << query.Type << "', table '" << query.Table << "', queue size: '" << GetPendingQueryCount() << "'.";
+#endif /* I2_DEBUG */
+
+ m_QueryQueue.Enqueue([this, queries]() { InternalExecuteMultipleQueries(queries); }, query.Priority);
+ return;
+ }
+ }
+
+ for (const DbQuery& query : queries) {
+ InternalExecuteQuery(query);
+ }
+}
+
+void IdoMysqlConnection::InternalExecuteQuery(const DbQuery& query, int typeOverride)
+{
+ AssertOnWorkQueue();
+
+ if (IsPaused() && GetPauseCalled()) {
+ DecreasePendingQueries(1);
+ return;
+ }
+
+ if (!GetConnected()) {
+ DecreasePendingQueries(1);
+ return;
+ }
+
+ if (query.Type == DbQueryNewTransaction) {
+ DecreasePendingQueries(1);
+ InternalNewTransaction();
+ return;
+ }
+
+ /* check whether we're allowed to execute the query first */
+ if (GetCategoryFilter() != DbCatEverything && (query.Category & GetCategoryFilter()) == 0) {
+ DecreasePendingQueries(1);
+ return;
+ }
+
+ if (query.Object && query.Object->GetObject()->GetExtension("agent_check").ToBool()) {
+ DecreasePendingQueries(1);
+ return;
+ }
+
+ /* check if there are missing object/insert ids and re-enqueue the query */
+ if (!CanExecuteQuery(query)) {
+
+#ifdef I2_DEBUG /* I2_DEBUG */
+ Log(LogDebug, "IdoMysqlConnection")
+ << "Scheduling execute query task again: Cannot execute query now. Type '"
+ << typeOverride << "', table '" << query.Table << "', queue size: '" << GetPendingQueryCount() << "'.";
+#endif /* I2_DEBUG */
+
+ m_QueryQueue.Enqueue([this, query, typeOverride]() { InternalExecuteQuery(query, typeOverride); }, query.Priority);
+ return;
+ }
+
+ std::ostringstream qbuf, where;
+ int type;
+
+ if (query.WhereCriteria) {
+ where << " WHERE ";
+
+ ObjectLock olock(query.WhereCriteria);
+ Value value;
+ bool first = true;
+
+ for (const Dictionary::Pair& kv : query.WhereCriteria) {
+ if (!FieldToEscapedString(kv.first, kv.second, &value)) {
+
+#ifdef I2_DEBUG /* I2_DEBUG */
+ Log(LogDebug, "IdoMysqlConnection")
+ << "Scheduling execute query task again: Cannot execute query now. Type '"
+ << typeOverride << "', table '" << query.Table << "', queue size: '" << GetPendingQueryCount() << "'.";
+#endif /* I2_DEBUG */
+
+ m_QueryQueue.Enqueue([this, query]() { InternalExecuteQuery(query, -1); }, query.Priority);
+ return;
+ }
+
+ if (!first)
+ where << " AND ";
+
+ where << kv.first << " = " << value;
+
+ if (first)
+ first = false;
+ }
+ }
+
+ type = (typeOverride != -1) ? typeOverride : query.Type;
+
+ bool upsert = false;
+
+ if ((type & DbQueryInsert) && (type & DbQueryUpdate)) {
+ bool hasid = false;
+
+ if (query.Object) {
+ if (query.ConfigUpdate)
+ hasid = GetConfigUpdate(query.Object);
+ else if (query.StatusUpdate)
+ hasid = GetStatusUpdate(query.Object);
+ }
+
+ if (!hasid)
+ upsert = true;
+
+ type = DbQueryUpdate;
+ }
+
+ if ((type & DbQueryInsert) && (type & DbQueryDelete)) {
+ std::ostringstream qdel;
+ qdel << "DELETE FROM " << GetTablePrefix() << query.Table << where.str();
+ IncreasePendingQueries(1);
+ AsyncQuery(qdel.str());
+
+ type = DbQueryInsert;
+ }
+
+ switch (type) {
+ case DbQueryInsert:
+ qbuf << "INSERT INTO " << GetTablePrefix() << query.Table;
+ break;
+ case DbQueryUpdate:
+ qbuf << "UPDATE " << GetTablePrefix() << query.Table << " SET";
+ break;
+ case DbQueryDelete:
+ qbuf << "DELETE FROM " << GetTablePrefix() << query.Table;
+ break;
+ default:
+ VERIFY(!"Invalid query type.");
+ }
+
+ if (type == DbQueryInsert || type == DbQueryUpdate) {
+ std::ostringstream colbuf, valbuf;
+
+ if (type == DbQueryUpdate && query.Fields->GetLength() == 0)
+ return;
+
+ ObjectLock olock(query.Fields);
+
+ bool first = true;
+ for (const Dictionary::Pair& kv : query.Fields) {
+ Value value;
+
+ if (!FieldToEscapedString(kv.first, kv.second, &value)) {
+
+#ifdef I2_DEBUG /* I2_DEBUG */
+ Log(LogDebug, "IdoMysqlConnection")
+ << "Scheduling execute query task again: Cannot extract required INSERT/UPDATE fields, key '"
+ << kv.first << "', val '" << kv.second << "', type " << typeOverride << ", table '" << query.Table << "'.";
+#endif /* I2_DEBUG */
+
+ m_QueryQueue.Enqueue([this, query]() { InternalExecuteQuery(query, -1); }, query.Priority);
+ return;
+ }
+
+ if (type == DbQueryInsert) {
+ if (!first) {
+ colbuf << ", ";
+ valbuf << ", ";
+ }
+
+ colbuf << kv.first;
+ valbuf << value;
+ } else {
+ if (!first)
+ qbuf << ", ";
+
+ qbuf << " " << kv.first << " = " << value;
+ }
+
+ if (first)
+ first = false;
+ }
+
+ if (type == DbQueryInsert)
+ qbuf << " (" << colbuf.str() << ") VALUES (" << valbuf.str() << ")";
+ }
+
+ if (type != DbQueryInsert)
+ qbuf << where.str();
+
+ AsyncQuery(qbuf.str(), [this, query, type, upsert](const IdoMysqlResult&) { FinishExecuteQuery(query, type, upsert); });
+}
+
+void IdoMysqlConnection::FinishExecuteQuery(const DbQuery& query, int type, bool upsert)
+{
+ if (upsert && GetAffectedRows() == 0) {
+
+#ifdef I2_DEBUG /* I2_DEBUG */
+ Log(LogDebug, "IdoMysqlConnection")
+ << "Rescheduling DELETE/INSERT query: Upsert UPDATE did not affect rows, type " << type << ", table '" << query.Table << "'.";
+#endif /* I2_DEBUG */
+
+ IncreasePendingQueries(1);
+ m_QueryQueue.Enqueue([this, query]() { InternalExecuteQuery(query, DbQueryDelete | DbQueryInsert); }, query.Priority);
+
+ return;
+ }
+
+ if (type == DbQueryInsert && query.Object) {
+ if (query.ConfigUpdate) {
+ SetInsertID(query.Object, GetLastInsertID());
+ SetConfigUpdate(query.Object, true);
+ } else if (query.StatusUpdate)
+ SetStatusUpdate(query.Object, true);
+ }
+
+ if (type == DbQueryInsert && query.Table == "notifications" && query.NotificationInsertID)
+ query.NotificationInsertID->SetValue(static_cast<long>(GetLastInsertID()));
+}
+
+void IdoMysqlConnection::CleanUpExecuteQuery(const String& table, const String& time_column, double max_age)
+{
+ if (IsPaused())
+ return;
+
+#ifdef I2_DEBUG /* I2_DEBUG */
+ Log(LogDebug, "IdoMysqlConnection")
+ << "Rescheduling cleanup query for table '" << table << "' and column '"
+ << time_column << "'. max_age is set to '" << max_age << "'.";
+#endif /* I2_DEBUG */
+
+ IncreasePendingQueries(1);
+ m_QueryQueue.Enqueue([this, table, time_column, max_age]() { InternalCleanUpExecuteQuery(table, time_column, max_age); }, PriorityLow, true);
+}
+
+void IdoMysqlConnection::InternalCleanUpExecuteQuery(const String& table, const String& time_column, double max_age)
+{
+ AssertOnWorkQueue();
+
+ if (IsPaused()) {
+ DecreasePendingQueries(1);
+ return;
+ }
+
+ if (!GetConnected()) {
+ DecreasePendingQueries(1);
+ return;
+ }
+
+ AsyncQuery("DELETE FROM " + GetTablePrefix() + table + " WHERE instance_id = " +
+ Convert::ToString(static_cast<long>(m_InstanceID)) + " AND " + time_column +
+ " < FROM_UNIXTIME(" + Convert::ToString(static_cast<long>(max_age)) + ")");
+}
+
+void IdoMysqlConnection::FillIDCache(const DbType::Ptr& type)
+{
+ String query = "SELECT " + type->GetIDColumn() + " AS object_id, " + type->GetTable() + "_id, config_hash FROM " + GetTablePrefix() + type->GetTable() + "s";
+ IdoMysqlResult result = Query(query);
+
+ Dictionary::Ptr row;
+
+ while ((row = FetchRow(result))) {
+ DbReference dbref(row->Get("object_id"));
+ SetInsertID(type, dbref, DbReference(row->Get(type->GetTable() + "_id")));
+ SetConfigHash(type, dbref, row->Get("config_hash"));
+ }
+}
+
+int IdoMysqlConnection::GetPendingQueryCount() const
+{
+ return m_QueryQueue.GetLength();
+}
diff --git a/lib/db_ido_mysql/idomysqlconnection.hpp b/lib/db_ido_mysql/idomysqlconnection.hpp
new file mode 100644
index 0000000..5a5c120
--- /dev/null
+++ b/lib/db_ido_mysql/idomysqlconnection.hpp
@@ -0,0 +1,114 @@
+/* Icinga 2 | (c) 2012 Icinga GmbH | GPLv2+ */
+
+#ifndef IDOMYSQLCONNECTION_H
+#define IDOMYSQLCONNECTION_H
+
+#include "db_ido_mysql/idomysqlconnection-ti.hpp"
+#include "mysql_shim/mysqlinterface.hpp"
+#include "base/array.hpp"
+#include "base/timer.hpp"
+#include "base/workqueue.hpp"
+#include "base/library.hpp"
+#include <cstdint>
+
+namespace icinga
+{
+
+typedef std::shared_ptr<MYSQL_RES> IdoMysqlResult;
+
+typedef std::function<void (const IdoMysqlResult&)> IdoAsyncCallback;
+
+struct IdoAsyncQuery
+{
+ String Query;
+ IdoAsyncCallback Callback;
+};
+
+/**
+ * An IDO MySQL database connection.
+ *
+ * @ingroup ido
+ */
+class IdoMysqlConnection final : public ObjectImpl<IdoMysqlConnection>
+{
+public:
+ DECLARE_OBJECT(IdoMysqlConnection);
+ DECLARE_OBJECTNAME(IdoMysqlConnection);
+
+ static void StatsFunc(const Dictionary::Ptr& status, const Array::Ptr& perfdata);
+
+ const char * GetLatestSchemaVersion() const noexcept override;
+ const char * GetCompatSchemaVersion() const noexcept override;
+
+ int GetPendingQueryCount() const override;
+
+protected:
+ void OnConfigLoaded() override;
+ void Resume() override;
+ void Pause() override;
+
+ void ActivateObject(const DbObject::Ptr& dbobj) override;
+ void DeactivateObject(const DbObject::Ptr& dbobj) override;
+ void ExecuteQuery(const DbQuery& query) override;
+ void ExecuteMultipleQueries(const std::vector<DbQuery>& queries) override;
+ void CleanUpExecuteQuery(const String& table, const String& time_key, double time_value) override;
+ void FillIDCache(const DbType::Ptr& type) override;
+ void NewTransaction() override;
+ void Disconnect() override;
+
+private:
+ DbReference m_InstanceID;
+
+ Library m_Library;
+ std::unique_ptr<MysqlInterface, MysqlInterfaceDeleter> m_Mysql;
+
+ MYSQL m_Connection;
+ int m_AffectedRows;
+ unsigned int m_MaxPacketSize;
+
+ std::vector<IdoAsyncQuery> m_AsyncQueries;
+ uint_fast32_t m_UncommittedAsyncQueries = 0;
+
+ Timer::Ptr m_ReconnectTimer;
+ Timer::Ptr m_TxTimer;
+
+ IdoMysqlResult Query(const String& query);
+ DbReference GetLastInsertID();
+ int GetAffectedRows();
+ String Escape(const String& s);
+ Dictionary::Ptr FetchRow(const IdoMysqlResult& result);
+ void DiscardRows(const IdoMysqlResult& result);
+
+ void AsyncQuery(const String& query, const IdoAsyncCallback& callback = IdoAsyncCallback());
+ void FinishAsyncQueries();
+
+ bool FieldToEscapedString(const String& key, const Value& value, Value *result);
+ void InternalActivateObject(const DbObject::Ptr& dbobj);
+ void InternalDeactivateObject(const DbObject::Ptr& dbobj);
+
+ void Reconnect();
+
+ void AssertOnWorkQueue();
+
+ void ReconnectTimerHandler();
+
+ bool CanExecuteQuery(const DbQuery& query);
+
+ void InternalExecuteQuery(const DbQuery& query, int typeOverride = -1);
+ void InternalExecuteMultipleQueries(const std::vector<DbQuery>& queries);
+
+ void FinishExecuteQuery(const DbQuery& query, int type, bool upsert);
+ void InternalCleanUpExecuteQuery(const String& table, const String& time_key, double time_value);
+ void InternalNewTransaction();
+
+ void ClearTableBySession(const String& table);
+ void ClearTablesBySession();
+
+ void ExceptionHandler(boost::exception_ptr exp);
+
+ void FinishConnect(double startTime);
+};
+
+}
+
+#endif /* IDOMYSQLCONNECTION_H */
diff --git a/lib/db_ido_mysql/idomysqlconnection.ti b/lib/db_ido_mysql/idomysqlconnection.ti
new file mode 100644
index 0000000..681148f
--- /dev/null
+++ b/lib/db_ido_mysql/idomysqlconnection.ti
@@ -0,0 +1,42 @@
+/* Icinga 2 | (c) 2012 Icinga GmbH | GPLv2+ */
+
+#include "db_ido/dbconnection.hpp"
+
+library db_ido_mysql;
+
+namespace icinga
+{
+
+class IdoMysqlConnection : DbConnection
+{
+ activation_priority 100;
+
+ [config] String host {
+ default {{{ return "localhost"; }}}
+ };
+ [config] int port {
+ default {{{ return 3306; }}}
+ };
+ [config] String socket_path;
+ [config] String user {
+ default {{{ return "icinga"; }}}
+ };
+ [config, no_user_view, no_user_modify] String password {
+ default {{{ return "icinga"; }}}
+ };
+ [config] String database {
+ default {{{ return "icinga"; }}}
+ };
+ [config] bool enable_ssl;
+ [config] String ssl_key;
+ [config] String ssl_cert;
+ [config] String ssl_ca;
+ [config] String ssl_capath;
+ [config] String ssl_cipher;
+ [config] String instance_name {
+ default {{{ return "default"; }}}
+ };
+ [config] String instance_description;
+};
+
+}
diff --git a/lib/db_ido_mysql/schema/mysql.sql b/lib/db_ido_mysql/schema/mysql.sql
new file mode 100644
index 0000000..020ba3c
--- /dev/null
+++ b/lib/db_ido_mysql/schema/mysql.sql
@@ -0,0 +1,1666 @@
+-- --------------------------------------------------------
+-- mysql.sql
+-- DB definition for IDO MySQL
+--
+-- Icinga 2 | (c) 2012 Icinga GmbH | GPLv2+
+--
+-- -- --------------------------------------------------------
+
+/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
+/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
+/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
+/*!40101 SET NAMES utf8 */;
+
+--
+-- Database: icinga
+--
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table icinga_acknowledgements
+--
+
+CREATE TABLE IF NOT EXISTS icinga_acknowledgements (
+ acknowledgement_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ instance_id bigint unsigned default 0,
+ entry_time timestamp NULL,
+ entry_time_usec int default 0,
+ acknowledgement_type smallint default 0,
+ object_id bigint unsigned default 0,
+ state smallint default 0,
+ author_name varchar(64) character set latin1 default '',
+ comment_data TEXT character set latin1,
+ is_sticky smallint default 0,
+ persistent_comment smallint default 0,
+ notify_contacts smallint default 0,
+ end_time timestamp NULL,
+ PRIMARY KEY (acknowledgement_id)
+) ENGINE=InnoDB COMMENT='Current and historical host and service acknowledgements';
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table icinga_commands
+--
+
+CREATE TABLE IF NOT EXISTS icinga_commands (
+ command_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ instance_id bigint unsigned default 0,
+ config_type smallint default 0,
+ object_id bigint unsigned default 0,
+ command_line TEXT character set latin1,
+ config_hash varchar(64) DEFAULT NULL,
+ PRIMARY KEY (command_id),
+ UNIQUE KEY instance_id (instance_id,object_id,config_type)
+) ENGINE=InnoDB COMMENT='Command definitions';
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table icinga_commenthistory
+--
+
+CREATE TABLE IF NOT EXISTS icinga_commenthistory (
+ commenthistory_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ instance_id bigint unsigned default 0,
+ entry_time timestamp NULL,
+ entry_time_usec int default 0,
+ comment_type smallint default 0,
+ entry_type smallint default 0,
+ object_id bigint unsigned default 0,
+ comment_time timestamp NULL,
+ internal_comment_id bigint unsigned default 0,
+ author_name varchar(64) character set latin1 default '',
+ comment_data TEXT character set latin1,
+ is_persistent smallint default 0,
+ comment_source smallint default 0,
+ expires smallint default 0,
+ expiration_time timestamp NULL,
+ deletion_time timestamp NULL,
+ deletion_time_usec int default 0,
+ name TEXT character set latin1 default NULL,
+ PRIMARY KEY (commenthistory_id)
+) ENGINE=InnoDB COMMENT='Historical host and service comments';
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table icinga_comments
+--
+
+CREATE TABLE IF NOT EXISTS icinga_comments (
+ comment_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ instance_id bigint unsigned default 0,
+ entry_time timestamp NULL,
+ entry_time_usec int default 0,
+ comment_type smallint default 0,
+ entry_type smallint default 0,
+ object_id bigint unsigned default 0,
+ comment_time timestamp NULL,
+ internal_comment_id bigint unsigned default 0,
+ author_name varchar(64) character set latin1 default '',
+ comment_data TEXT character set latin1,
+ is_persistent smallint default 0,
+ comment_source smallint default 0,
+ expires smallint default 0,
+ expiration_time timestamp NULL,
+ name TEXT character set latin1 default NULL,
+ session_token int default NULL,
+ PRIMARY KEY (comment_id)
+) ENGINE=InnoDB COMMENT='Usercomments on Icinga objects';
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table icinga_configfiles
+--
+
+CREATE TABLE IF NOT EXISTS icinga_configfiles (
+ configfile_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ instance_id bigint unsigned default 0,
+ configfile_type smallint default 0,
+ configfile_path varchar(255) character set latin1 default '',
+ PRIMARY KEY (configfile_id),
+ UNIQUE KEY instance_id (instance_id,configfile_type,configfile_path)
+) ENGINE=InnoDB COMMENT='Configuration files';
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table icinga_configfilevariables
+--
+
+CREATE TABLE IF NOT EXISTS icinga_configfilevariables (
+ configfilevariable_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ instance_id bigint unsigned default 0,
+ configfile_id bigint unsigned default 0,
+ varname varchar(64) character set latin1 default '',
+ varvalue TEXT character set latin1,
+ PRIMARY KEY (configfilevariable_id)
+) ENGINE=InnoDB COMMENT='Configuration file variables';
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table icinga_conninfo
+--
+
+CREATE TABLE IF NOT EXISTS icinga_conninfo (
+ conninfo_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ instance_id bigint unsigned default 0,
+ agent_name varchar(32) character set latin1 default '',
+ agent_version varchar(32) character set latin1 default '',
+ disposition varchar(32) character set latin1 default '',
+ connect_source varchar(32) character set latin1 default '',
+ connect_type varchar(32) character set latin1 default '',
+ connect_time timestamp NULL,
+ disconnect_time timestamp NULL,
+ last_checkin_time timestamp NULL,
+ data_start_time timestamp NULL,
+ data_end_time timestamp NULL,
+ bytes_processed bigint unsigned default '0',
+ lines_processed bigint unsigned default '0',
+ entries_processed bigint unsigned default '0',
+ PRIMARY KEY (conninfo_id)
+) ENGINE=InnoDB COMMENT='IDO2DB daemon connection information';
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table icinga_contactgroups
+--
+
+CREATE TABLE IF NOT EXISTS icinga_contactgroups (
+ contactgroup_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ instance_id bigint unsigned default 0,
+ config_type smallint default 0,
+ contactgroup_object_id bigint unsigned default 0,
+ alias varchar(255) character set latin1 default '',
+ config_hash varchar(64) DEFAULT NULL,
+ PRIMARY KEY (contactgroup_id),
+ UNIQUE KEY instance_id (instance_id,config_type,contactgroup_object_id)
+) ENGINE=InnoDB COMMENT='Contactgroup definitions';
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table icinga_contactgroup_members
+--
+
+CREATE TABLE IF NOT EXISTS icinga_contactgroup_members (
+ contactgroup_member_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ instance_id bigint unsigned default 0,
+ contactgroup_id bigint unsigned default 0,
+ contact_object_id bigint unsigned default 0,
+ PRIMARY KEY (contactgroup_member_id)
+) ENGINE=InnoDB COMMENT='Contactgroup members';
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table icinga_contactnotificationmethods
+--
+
+CREATE TABLE IF NOT EXISTS icinga_contactnotificationmethods (
+ contactnotificationmethod_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ instance_id bigint unsigned default 0,
+ contactnotification_id bigint unsigned default 0,
+ start_time timestamp NULL,
+ start_time_usec int default 0,
+ end_time timestamp NULL,
+ end_time_usec int default 0,
+ command_object_id bigint unsigned default 0,
+ command_args TEXT character set latin1,
+ PRIMARY KEY (contactnotificationmethod_id),
+ UNIQUE KEY instance_id (instance_id,contactnotification_id,start_time,start_time_usec)
+) ENGINE=InnoDB COMMENT='Historical record of contact notification methods';
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table icinga_contactnotifications
+--
+
+CREATE TABLE IF NOT EXISTS icinga_contactnotifications (
+ contactnotification_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ instance_id bigint unsigned default 0,
+ notification_id bigint unsigned default 0,
+ contact_object_id bigint unsigned default 0,
+ start_time timestamp NULL,
+ start_time_usec int default 0,
+ end_time timestamp NULL,
+ end_time_usec int default 0,
+ PRIMARY KEY (contactnotification_id),
+ UNIQUE KEY instance_id (instance_id,contact_object_id,start_time,start_time_usec)
+) ENGINE=InnoDB COMMENT='Historical record of contact notifications';
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table icinga_contacts
+--
+
+CREATE TABLE IF NOT EXISTS icinga_contacts (
+ contact_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ instance_id bigint unsigned default 0,
+ config_type smallint default 0,
+ contact_object_id bigint unsigned default 0,
+ alias varchar(255) character set latin1 default '',
+ email_address varchar(255) character set latin1 default '',
+ pager_address varchar(64) character set latin1 default '',
+ host_timeperiod_object_id bigint unsigned default 0,
+ service_timeperiod_object_id bigint unsigned default 0,
+ host_notifications_enabled smallint default 0,
+ service_notifications_enabled smallint default 0,
+ can_submit_commands smallint default 0,
+ notify_service_recovery smallint default 0,
+ notify_service_warning smallint default 0,
+ notify_service_unknown smallint default 0,
+ notify_service_critical smallint default 0,
+ notify_service_flapping smallint default 0,
+ notify_service_downtime smallint default 0,
+ notify_host_recovery smallint default 0,
+ notify_host_down smallint default 0,
+ notify_host_unreachable smallint default 0,
+ notify_host_flapping smallint default 0,
+ notify_host_downtime smallint default 0,
+ config_hash varchar(64) DEFAULT NULL,
+ PRIMARY KEY (contact_id),
+ UNIQUE KEY instance_id (instance_id,config_type,contact_object_id)
+) ENGINE=InnoDB COMMENT='Contact definitions';
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table icinga_contactstatus
+--
+
+CREATE TABLE IF NOT EXISTS icinga_contactstatus (
+ contactstatus_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ instance_id bigint unsigned default 0,
+ contact_object_id bigint unsigned default 0,
+ status_update_time timestamp NULL,
+ host_notifications_enabled smallint default 0,
+ service_notifications_enabled smallint default 0,
+ last_host_notification timestamp NULL,
+ last_service_notification timestamp NULL,
+ modified_attributes int default 0,
+ modified_host_attributes int default 0,
+ modified_service_attributes int default 0,
+ PRIMARY KEY (contactstatus_id),
+ UNIQUE KEY contact_object_id (contact_object_id)
+) ENGINE=InnoDB COMMENT='Contact status';
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table icinga_contact_addresses
+--
+
+CREATE TABLE IF NOT EXISTS icinga_contact_addresses (
+ contact_address_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ instance_id bigint unsigned default 0,
+ contact_id bigint unsigned default 0,
+ address_number smallint default 0,
+ address varchar(255) character set latin1 default '',
+ PRIMARY KEY (contact_address_id),
+ UNIQUE KEY contact_id (contact_id,address_number)
+) ENGINE=InnoDB COMMENT='Contact addresses';
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table icinga_contact_notificationcommands
+--
+
+CREATE TABLE IF NOT EXISTS icinga_contact_notificationcommands (
+ contact_notificationcommand_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ instance_id bigint unsigned default 0,
+ contact_id bigint unsigned default 0,
+ notification_type smallint default 0,
+ command_object_id bigint unsigned default 0,
+ command_args varchar(255) character set latin1 default '',
+ PRIMARY KEY (contact_notificationcommand_id),
+ UNIQUE KEY contact_id (contact_id,notification_type,command_object_id,command_args)
+) ENGINE=InnoDB COMMENT='Contact host and service notification commands';
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table icinga_customvariables
+--
+
+CREATE TABLE IF NOT EXISTS icinga_customvariables (
+ customvariable_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ instance_id bigint unsigned default 0,
+ object_id bigint unsigned default 0,
+ config_type smallint default 0,
+ has_been_modified smallint default 0,
+ varname varchar(255) character set latin1 collate latin1_general_cs default NULL,
+ varvalue TEXT character set latin1,
+ is_json smallint default 0,
+ PRIMARY KEY (customvariable_id),
+ UNIQUE KEY object_id_2 (object_id,config_type,varname),
+ KEY varname (varname)
+) ENGINE=InnoDB COMMENT='Custom variables';
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table icinga_customvariablestatus
+--
+
+CREATE TABLE IF NOT EXISTS icinga_customvariablestatus (
+ customvariablestatus_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ instance_id bigint unsigned default 0,
+ object_id bigint unsigned default 0,
+ status_update_time timestamp NULL,
+ has_been_modified smallint default 0,
+ varname varchar(255) character set latin1 collate latin1_general_cs default NULL,
+ varvalue TEXT character set latin1,
+ is_json smallint default 0,
+ PRIMARY KEY (customvariablestatus_id),
+ UNIQUE KEY object_id_2 (object_id,varname),
+ KEY varname (varname)
+) ENGINE=InnoDB COMMENT='Custom variable status information';
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table icinga_dbversion
+--
+
+CREATE TABLE IF NOT EXISTS icinga_dbversion (
+ dbversion_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ name varchar(10) character set latin1 default '',
+ version varchar(10) character set latin1 default '',
+ create_time timestamp NULL,
+ modify_time timestamp NULL,
+ PRIMARY KEY (dbversion_id),
+ UNIQUE KEY dbversion (name)
+) ENGINE=InnoDB;
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table icinga_downtimehistory
+--
+
+CREATE TABLE IF NOT EXISTS icinga_downtimehistory (
+ downtimehistory_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ instance_id bigint unsigned default 0,
+ downtime_type smallint default 0,
+ object_id bigint unsigned default 0,
+ entry_time timestamp NULL,
+ author_name varchar(64) character set latin1 default '',
+ comment_data TEXT character set latin1,
+ internal_downtime_id bigint unsigned default 0,
+ triggered_by_id bigint unsigned default 0,
+ is_fixed smallint default 0,
+ duration bigint(20) default 0,
+ scheduled_start_time timestamp NULL,
+ scheduled_end_time timestamp NULL,
+ was_started smallint default 0,
+ actual_start_time timestamp NULL,
+ actual_start_time_usec int default 0,
+ actual_end_time timestamp NULL,
+ actual_end_time_usec int default 0,
+ was_cancelled smallint default 0,
+ is_in_effect smallint default 0,
+ trigger_time timestamp NULL,
+ name TEXT character set latin1 default NULL,
+ PRIMARY KEY (downtimehistory_id)
+) ENGINE=InnoDB COMMENT='Historical scheduled host and service downtime';
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table icinga_eventhandlers
+--
+
+CREATE TABLE IF NOT EXISTS icinga_eventhandlers (
+ eventhandler_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ instance_id bigint unsigned default 0,
+ eventhandler_type smallint default 0,
+ object_id bigint unsigned default 0,
+ state smallint default 0,
+ state_type smallint default 0,
+ start_time timestamp NULL,
+ start_time_usec int default 0,
+ end_time timestamp NULL,
+ end_time_usec int default 0,
+ command_object_id bigint unsigned default 0,
+ command_args TEXT character set latin1,
+ command_line TEXT character set latin1,
+ timeout smallint default 0,
+ early_timeout smallint default 0,
+ execution_time double default '0',
+ return_code smallint default 0,
+ output TEXT character set latin1,
+ long_output TEXT,
+ PRIMARY KEY (eventhandler_id),
+ UNIQUE KEY instance_id (instance_id,object_id,start_time,start_time_usec)
+) ENGINE=InnoDB COMMENT='Historical host and service event handlers';
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table icinga_externalcommands
+--
+
+CREATE TABLE IF NOT EXISTS icinga_externalcommands (
+ externalcommand_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ instance_id bigint unsigned default 0,
+ entry_time timestamp NULL,
+ command_type smallint default 0,
+ command_name varchar(128) character set latin1 default '',
+ command_args TEXT character set latin1,
+ PRIMARY KEY (externalcommand_id)
+) ENGINE=InnoDB COMMENT='Historical record of processed external commands';
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table icinga_flappinghistory
+--
+
+CREATE TABLE IF NOT EXISTS icinga_flappinghistory (
+ flappinghistory_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ instance_id bigint unsigned default 0,
+ event_time timestamp NULL,
+ event_time_usec int default 0,
+ event_type smallint default 0,
+ reason_type smallint default 0,
+ flapping_type smallint default 0,
+ object_id bigint unsigned default 0,
+ percent_state_change double default '0',
+ low_threshold double default '0',
+ high_threshold double default '0',
+ comment_time timestamp NULL,
+ internal_comment_id bigint unsigned default 0,
+ PRIMARY KEY (flappinghistory_id)
+) ENGINE=InnoDB COMMENT='Current and historical record of host and service flapping';
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table icinga_hostchecks
+--
+
+CREATE TABLE IF NOT EXISTS icinga_hostchecks (
+ hostcheck_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ instance_id bigint unsigned default 0,
+ host_object_id bigint unsigned default 0,
+ check_type smallint default 0,
+ is_raw_check smallint default 0,
+ current_check_attempt smallint default 0,
+ max_check_attempts smallint default 0,
+ state smallint default 0,
+ state_type smallint default 0,
+ start_time timestamp NULL,
+ start_time_usec int default 0,
+ end_time timestamp NULL,
+ end_time_usec int default 0,
+ command_object_id bigint unsigned default 0,
+ command_args TEXT character set latin1,
+ command_line TEXT character set latin1,
+ timeout smallint default 0,
+ early_timeout smallint default 0,
+ execution_time double default '0',
+ latency double default '0',
+ return_code smallint default 0,
+ output TEXT character set latin1,
+ long_output TEXT,
+ perfdata TEXT character set latin1,
+ PRIMARY KEY (hostcheck_id)
+) ENGINE=InnoDB COMMENT='Historical host checks';
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table icinga_hostdependencies
+--
+
+CREATE TABLE IF NOT EXISTS icinga_hostdependencies (
+ hostdependency_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ instance_id bigint unsigned default 0,
+ config_type smallint default 0,
+ host_object_id bigint unsigned default 0,
+ dependent_host_object_id bigint unsigned default 0,
+ dependency_type smallint default 0,
+ inherits_parent smallint default 0,
+ timeperiod_object_id bigint unsigned default 0,
+ fail_on_up smallint default 0,
+ fail_on_down smallint default 0,
+ fail_on_unreachable smallint default 0,
+ PRIMARY KEY (hostdependency_id),
+ KEY instance_id (instance_id,config_type,host_object_id,dependent_host_object_id,dependency_type,inherits_parent,fail_on_up,fail_on_down,fail_on_unreachable)
+) ENGINE=InnoDB COMMENT='Host dependency definitions';
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table icinga_hostescalations
+--
+
+CREATE TABLE IF NOT EXISTS icinga_hostescalations (
+ hostescalation_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ instance_id bigint unsigned default 0,
+ config_type smallint default 0,
+ host_object_id bigint unsigned default 0,
+ timeperiod_object_id bigint unsigned default 0,
+ first_notification smallint default 0,
+ last_notification smallint default 0,
+ notification_interval double default '0',
+ escalate_on_recovery smallint default 0,
+ escalate_on_down smallint default 0,
+ escalate_on_unreachable smallint default 0,
+ PRIMARY KEY (hostescalation_id),
+ UNIQUE KEY instance_id (instance_id,config_type,host_object_id,timeperiod_object_id,first_notification,last_notification)
+) ENGINE=InnoDB COMMENT='Host escalation definitions';
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table icinga_hostescalation_contactgroups
+--
+
+CREATE TABLE IF NOT EXISTS icinga_hostescalation_contactgroups (
+ hostescalation_contactgroup_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ instance_id bigint unsigned default 0,
+ hostescalation_id bigint unsigned default 0,
+ contactgroup_object_id bigint unsigned default 0,
+ PRIMARY KEY (hostescalation_contactgroup_id),
+ UNIQUE KEY instance_id (hostescalation_id,contactgroup_object_id)
+) ENGINE=InnoDB COMMENT='Host escalation contact groups';
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table icinga_hostescalation_contacts
+--
+
+CREATE TABLE IF NOT EXISTS icinga_hostescalation_contacts (
+ hostescalation_contact_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ instance_id bigint unsigned default 0,
+ hostescalation_id bigint unsigned default 0,
+ contact_object_id bigint unsigned default 0,
+ PRIMARY KEY (hostescalation_contact_id),
+ UNIQUE KEY instance_id (instance_id,hostescalation_id,contact_object_id)
+) ENGINE=InnoDB COMMENT='Host escalation contacts';
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table icinga_hostgroups
+--
+
+CREATE TABLE IF NOT EXISTS icinga_hostgroups (
+ hostgroup_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ instance_id bigint unsigned default 0,
+ config_type smallint default 0,
+ hostgroup_object_id bigint unsigned default 0,
+ alias varchar(255) character set latin1 default '',
+ notes TEXT character set latin1 default NULL,
+ notes_url TEXT character set latin1 default NULL,
+ action_url TEXT character set latin1 default NULL,
+ config_hash varchar(64) DEFAULT NULL,
+ PRIMARY KEY (hostgroup_id),
+ UNIQUE KEY instance_id (instance_id,hostgroup_object_id)
+) ENGINE=InnoDB COMMENT='Hostgroup definitions';
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table icinga_hostgroup_members
+--
+
+CREATE TABLE IF NOT EXISTS icinga_hostgroup_members (
+ hostgroup_member_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ instance_id bigint unsigned default 0,
+ hostgroup_id bigint unsigned default 0,
+ host_object_id bigint unsigned default 0,
+ PRIMARY KEY (hostgroup_member_id)
+) ENGINE=InnoDB COMMENT='Hostgroup members';
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table icinga_hosts
+--
+
+CREATE TABLE IF NOT EXISTS icinga_hosts (
+ host_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ instance_id bigint unsigned default 0,
+ config_type smallint default 0,
+ host_object_id bigint unsigned default 0,
+ alias varchar(255) character set latin1 default '',
+ display_name varchar(255) character set latin1 collate latin1_general_cs default '',
+ address varchar(128) character set latin1 default '',
+ address6 varchar(128) character set latin1 default '',
+ check_command_object_id bigint unsigned default 0,
+ check_command_args TEXT character set latin1,
+ eventhandler_command_object_id bigint unsigned default 0,
+ eventhandler_command_args TEXT character set latin1,
+ notification_timeperiod_object_id bigint unsigned default 0,
+ check_timeperiod_object_id bigint unsigned default 0,
+ failure_prediction_options varchar(128) character set latin1 default '',
+ check_interval double default '0',
+ retry_interval double default '0',
+ max_check_attempts smallint default 0,
+ first_notification_delay double default '0',
+ notification_interval double default '0',
+ notify_on_down smallint default 0,
+ notify_on_unreachable smallint default 0,
+ notify_on_recovery smallint default 0,
+ notify_on_flapping smallint default 0,
+ notify_on_downtime smallint default 0,
+ stalk_on_up smallint default 0,
+ stalk_on_down smallint default 0,
+ stalk_on_unreachable smallint default 0,
+ flap_detection_enabled smallint default 0,
+ flap_detection_on_up smallint default 0,
+ flap_detection_on_down smallint default 0,
+ flap_detection_on_unreachable smallint default 0,
+ low_flap_threshold double default '0',
+ high_flap_threshold double default '0',
+ process_performance_data smallint default 0,
+ freshness_checks_enabled smallint default 0,
+ freshness_threshold int default 0,
+ passive_checks_enabled smallint default 0,
+ event_handler_enabled smallint default 0,
+ active_checks_enabled smallint default 0,
+ retain_status_information smallint default 0,
+ retain_nonstatus_information smallint default 0,
+ notifications_enabled smallint default 0,
+ obsess_over_host smallint default 0,
+ failure_prediction_enabled smallint default 0,
+ notes TEXT character set latin1,
+ notes_url TEXT character set latin1,
+ action_url TEXT character set latin1,
+ icon_image TEXT character set latin1,
+ icon_image_alt TEXT character set latin1,
+ vrml_image TEXT character set latin1,
+ statusmap_image TEXT character set latin1,
+ have_2d_coords smallint default 0,
+ x_2d smallint default 0,
+ y_2d smallint default 0,
+ have_3d_coords smallint default 0,
+ x_3d double default '0',
+ y_3d double default '0',
+ z_3d double default '0',
+ config_hash varchar(64) DEFAULT NULL,
+ PRIMARY KEY (host_id),
+ UNIQUE KEY instance_id (instance_id,config_type,host_object_id)
+) ENGINE=InnoDB COMMENT='Host definitions';
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table icinga_hoststatus
+--
+
+CREATE TABLE IF NOT EXISTS icinga_hoststatus (
+ hoststatus_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ instance_id bigint unsigned default 0,
+ host_object_id bigint unsigned default 0,
+ status_update_time timestamp NULL,
+ output TEXT character set latin1,
+ long_output TEXT,
+ perfdata TEXT character set latin1,
+ check_source varchar(255) character set latin1 default '',
+ current_state smallint default 0,
+ has_been_checked smallint default 0,
+ should_be_scheduled smallint default 0,
+ current_check_attempt smallint default 0,
+ max_check_attempts smallint default 0,
+ last_check timestamp NULL,
+ next_check timestamp NULL,
+ check_type smallint default 0,
+ last_state_change timestamp NULL,
+ last_hard_state_change timestamp NULL,
+ last_hard_state smallint default 0,
+ last_time_up timestamp NULL,
+ last_time_down timestamp NULL,
+ last_time_unreachable timestamp NULL,
+ state_type smallint default 0,
+ last_notification timestamp NULL,
+ next_notification timestamp NULL,
+ no_more_notifications smallint default 0,
+ notifications_enabled smallint default 0,
+ problem_has_been_acknowledged smallint default 0,
+ acknowledgement_type smallint default 0,
+ current_notification_number int unsigned default 0,
+ passive_checks_enabled smallint default 0,
+ active_checks_enabled smallint default 0,
+ event_handler_enabled smallint default 0,
+ flap_detection_enabled smallint default 0,
+ is_flapping smallint default 0,
+ percent_state_change double default '0',
+ latency double default '0',
+ execution_time double default '0',
+ scheduled_downtime_depth smallint default 0,
+ failure_prediction_enabled smallint default 0,
+ process_performance_data smallint default 0,
+ obsess_over_host smallint default 0,
+ modified_host_attributes int default 0,
+ original_attributes TEXT character set latin1 default NULL,
+ event_handler TEXT character set latin1,
+ check_command TEXT character set latin1,
+ normal_check_interval double default '0',
+ retry_check_interval double default '0',
+ check_timeperiod_object_id bigint unsigned default 0,
+ is_reachable smallint default 0,
+ PRIMARY KEY (hoststatus_id),
+ UNIQUE KEY object_id (host_object_id)
+) ENGINE=InnoDB COMMENT='Current host status information';
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table icinga_host_contactgroups
+--
+
+CREATE TABLE IF NOT EXISTS icinga_host_contactgroups (
+ host_contactgroup_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ instance_id bigint unsigned default 0,
+ host_id bigint unsigned default 0,
+ contactgroup_object_id bigint unsigned default 0,
+ PRIMARY KEY (host_contactgroup_id)
+) ENGINE=InnoDB COMMENT='Host contact groups';
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table icinga_host_contacts
+--
+
+CREATE TABLE IF NOT EXISTS icinga_host_contacts (
+ host_contact_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ instance_id bigint unsigned default 0,
+ host_id bigint unsigned default 0,
+ contact_object_id bigint unsigned default 0,
+ PRIMARY KEY (host_contact_id)
+) ENGINE=InnoDB COMMENT='Host contacts';
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table icinga_host_parenthosts
+--
+
+CREATE TABLE IF NOT EXISTS icinga_host_parenthosts (
+ host_parenthost_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ instance_id bigint unsigned default 0,
+ host_id bigint unsigned default 0,
+ parent_host_object_id bigint unsigned default 0,
+ PRIMARY KEY (host_parenthost_id)
+) ENGINE=InnoDB COMMENT='Parent hosts';
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table icinga_instances
+--
+
+CREATE TABLE IF NOT EXISTS icinga_instances (
+ instance_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ instance_name varchar(64) character set latin1 default '',
+ instance_description varchar(128) character set latin1 default '',
+ PRIMARY KEY (instance_id)
+) ENGINE=InnoDB COMMENT='Location names of various Icinga installations';
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table icinga_logentries
+--
+
+CREATE TABLE IF NOT EXISTS icinga_logentries (
+ logentry_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ instance_id bigint unsigned default 0,
+ logentry_time timestamp NULL,
+ entry_time timestamp NULL,
+ entry_time_usec int default 0,
+ logentry_type int default 0,
+ logentry_data TEXT character set latin1,
+ realtime_data smallint default 0,
+ inferred_data_extracted smallint default 0,
+ object_id bigint unsigned default NULL,
+ PRIMARY KEY (logentry_id)
+) ENGINE=InnoDB COMMENT='Historical record of log entries';
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table icinga_notifications
+--
+
+CREATE TABLE IF NOT EXISTS icinga_notifications (
+ notification_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ instance_id bigint unsigned default 0,
+ notification_type smallint default 0,
+ notification_reason smallint default 0,
+ object_id bigint unsigned default 0,
+ start_time timestamp NULL,
+ start_time_usec int default 0,
+ end_time timestamp NULL,
+ end_time_usec int default 0,
+ state smallint default 0,
+ output TEXT character set latin1,
+ long_output TEXT,
+ escalated smallint default 0,
+ contacts_notified smallint default 0,
+ PRIMARY KEY (notification_id),
+ UNIQUE KEY instance_id (instance_id,object_id,start_time,start_time_usec)
+) ENGINE=InnoDB COMMENT='Historical record of host and service notifications';
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table icinga_objects
+--
+
+CREATE TABLE IF NOT EXISTS icinga_objects (
+ object_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ instance_id bigint unsigned default 0,
+ objecttype_id bigint unsigned default 0,
+ name1 varchar(255) character set latin1 collate latin1_general_cs default '',
+ name2 varchar(255) character set latin1 collate latin1_general_cs default NULL,
+ is_active smallint default 0,
+ PRIMARY KEY (object_id),
+ KEY objecttype_id (objecttype_id,name1,name2)
+) ENGINE=InnoDB COMMENT='Current and historical objects of all kinds';
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table icinga_processevents
+--
+
+CREATE TABLE IF NOT EXISTS icinga_processevents (
+ processevent_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ instance_id bigint unsigned default 0,
+ event_type smallint default 0,
+ event_time timestamp NULL,
+ event_time_usec int default 0,
+ process_id bigint unsigned default 0,
+ program_name varchar(16) character set latin1 default '',
+ program_version varchar(20) character set latin1 default '',
+ program_date varchar(10) character set latin1 default '',
+ PRIMARY KEY (processevent_id)
+) ENGINE=InnoDB COMMENT='Historical Icinga process events';
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table icinga_programstatus
+--
+
+CREATE TABLE IF NOT EXISTS icinga_programstatus (
+ programstatus_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ instance_id bigint unsigned default 0,
+ program_version varchar(64) character set latin1 collate latin1_general_cs default NULL,
+ status_update_time timestamp NULL,
+ program_start_time timestamp NULL,
+ program_end_time timestamp NULL,
+ endpoint_name varchar(255) character set latin1 collate latin1_general_cs default NULL,
+ is_currently_running smallint default 0,
+ process_id bigint unsigned default 0,
+ daemon_mode smallint default 0,
+ last_command_check timestamp NULL,
+ last_log_rotation timestamp NULL,
+ notifications_enabled smallint default 0,
+ disable_notif_expire_time timestamp NULL,
+ active_service_checks_enabled smallint default 0,
+ passive_service_checks_enabled smallint default 0,
+ active_host_checks_enabled smallint default 0,
+ passive_host_checks_enabled smallint default 0,
+ event_handlers_enabled smallint default 0,
+ flap_detection_enabled smallint default 0,
+ failure_prediction_enabled smallint default 0,
+ process_performance_data smallint default 0,
+ obsess_over_hosts smallint default 0,
+ obsess_over_services smallint default 0,
+ modified_host_attributes int default 0,
+ modified_service_attributes int default 0,
+ global_host_event_handler TEXT character set latin1,
+ global_service_event_handler TEXT character set latin1,
+ config_dump_in_progress smallint default 0,
+ PRIMARY KEY (programstatus_id),
+ UNIQUE KEY instance_id (instance_id)
+) ENGINE=InnoDB COMMENT='Current program status information';
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table icinga_runtimevariables
+--
+
+CREATE TABLE IF NOT EXISTS icinga_runtimevariables (
+ runtimevariable_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ instance_id bigint unsigned default 0,
+ varname varchar(64) character set latin1 default '',
+ varvalue TEXT character set latin1,
+ PRIMARY KEY (runtimevariable_id)
+) ENGINE=InnoDB COMMENT='Runtime variables from the Icinga daemon';
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table icinga_scheduleddowntime
+--
+
+CREATE TABLE IF NOT EXISTS icinga_scheduleddowntime (
+ scheduleddowntime_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ instance_id bigint unsigned default 0,
+ downtime_type smallint default 0,
+ object_id bigint unsigned default 0,
+ entry_time timestamp NULL,
+ author_name varchar(64) character set latin1 default '',
+ comment_data TEXT character set latin1,
+ internal_downtime_id bigint unsigned default 0,
+ triggered_by_id bigint unsigned default 0,
+ is_fixed smallint default 0,
+ duration bigint(20) default 0,
+ scheduled_start_time timestamp NULL,
+ scheduled_end_time timestamp NULL,
+ was_started smallint default 0,
+ actual_start_time timestamp NULL,
+ actual_start_time_usec int default 0,
+ is_in_effect smallint default 0,
+ trigger_time timestamp NULL,
+ name TEXT character set latin1 default NULL,
+ session_token int default NULL,
+ PRIMARY KEY (scheduleddowntime_id)
+) ENGINE=InnoDB COMMENT='Current scheduled host and service downtime';
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table icinga_servicechecks
+--
+
+CREATE TABLE IF NOT EXISTS icinga_servicechecks (
+ servicecheck_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ instance_id bigint unsigned default 0,
+ service_object_id bigint unsigned default 0,
+ check_type smallint default 0,
+ current_check_attempt smallint default 0,
+ max_check_attempts smallint default 0,
+ state smallint default 0,
+ state_type smallint default 0,
+ start_time timestamp NULL,
+ start_time_usec int default 0,
+ end_time timestamp NULL,
+ end_time_usec int default 0,
+ command_object_id bigint unsigned default 0,
+ command_args TEXT character set latin1,
+ command_line TEXT character set latin1,
+ timeout smallint default 0,
+ early_timeout smallint default 0,
+ execution_time double default '0',
+ latency double default '0',
+ return_code smallint default 0,
+ output TEXT character set latin1,
+ long_output TEXT,
+ perfdata TEXT character set latin1,
+ PRIMARY KEY (servicecheck_id)
+) ENGINE=InnoDB COMMENT='Historical service checks';
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table icinga_servicedependencies
+--
+
+CREATE TABLE IF NOT EXISTS icinga_servicedependencies (
+ servicedependency_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ instance_id bigint unsigned default 0,
+ config_type smallint default 0,
+ service_object_id bigint unsigned default 0,
+ dependent_service_object_id bigint unsigned default 0,
+ dependency_type smallint default 0,
+ inherits_parent smallint default 0,
+ timeperiod_object_id bigint unsigned default 0,
+ fail_on_ok smallint default 0,
+ fail_on_warning smallint default 0,
+ fail_on_unknown smallint default 0,
+ fail_on_critical smallint default 0,
+ PRIMARY KEY (servicedependency_id),
+ KEY instance_id (instance_id,config_type,service_object_id,dependent_service_object_id,dependency_type,inherits_parent,fail_on_ok,fail_on_warning,fail_on_unknown,fail_on_critical)
+) ENGINE=InnoDB COMMENT='Service dependency definitions';
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table icinga_serviceescalations
+--
+
+CREATE TABLE IF NOT EXISTS icinga_serviceescalations (
+ serviceescalation_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ instance_id bigint unsigned default 0,
+ config_type smallint default 0,
+ service_object_id bigint unsigned default 0,
+ timeperiod_object_id bigint unsigned default 0,
+ first_notification smallint default 0,
+ last_notification smallint default 0,
+ notification_interval double default '0',
+ escalate_on_recovery smallint default 0,
+ escalate_on_warning smallint default 0,
+ escalate_on_unknown smallint default 0,
+ escalate_on_critical smallint default 0,
+ PRIMARY KEY (serviceescalation_id),
+ UNIQUE KEY instance_id (instance_id,config_type,service_object_id,timeperiod_object_id,first_notification,last_notification)
+) ENGINE=InnoDB COMMENT='Service escalation definitions';
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table icinga_serviceescalation_contactgroups
+--
+
+CREATE TABLE IF NOT EXISTS icinga_serviceescalation_contactgroups (
+ serviceescalation_contactgroup_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ instance_id bigint unsigned default 0,
+ serviceescalation_id bigint unsigned default 0,
+ contactgroup_object_id bigint unsigned default 0,
+ PRIMARY KEY (serviceescalation_contactgroup_id),
+ UNIQUE KEY instance_id (serviceescalation_id,contactgroup_object_id)
+) ENGINE=InnoDB COMMENT='Service escalation contact groups';
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table icinga_serviceescalation_contacts
+--
+
+CREATE TABLE IF NOT EXISTS icinga_serviceescalation_contacts (
+ serviceescalation_contact_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ instance_id bigint unsigned default 0,
+ serviceescalation_id bigint unsigned default 0,
+ contact_object_id bigint unsigned default 0,
+ PRIMARY KEY (serviceescalation_contact_id),
+ UNIQUE KEY instance_id (instance_id,serviceescalation_id,contact_object_id)
+) ENGINE=InnoDB COMMENT='Service escalation contacts';
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table icinga_servicegroups
+--
+
+CREATE TABLE IF NOT EXISTS icinga_servicegroups (
+ servicegroup_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ instance_id bigint unsigned default 0,
+ config_type smallint default 0,
+ servicegroup_object_id bigint unsigned default 0,
+ alias varchar(255) character set latin1 default '',
+ notes TEXT character set latin1 default NULL,
+ notes_url TEXT character set latin1 default NULL,
+ action_url TEXT character set latin1 default NULL,
+ config_hash varchar(64) DEFAULT NULL,
+ PRIMARY KEY (servicegroup_id),
+ UNIQUE KEY instance_id (instance_id,config_type,servicegroup_object_id)
+) ENGINE=InnoDB COMMENT='Servicegroup definitions';
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table icinga_servicegroup_members
+--
+
+CREATE TABLE IF NOT EXISTS icinga_servicegroup_members (
+ servicegroup_member_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ instance_id bigint unsigned default 0,
+ servicegroup_id bigint unsigned default 0,
+ service_object_id bigint unsigned default 0,
+ PRIMARY KEY (servicegroup_member_id)
+) ENGINE=InnoDB COMMENT='Servicegroup members';
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table icinga_services
+--
+
+CREATE TABLE IF NOT EXISTS icinga_services (
+ service_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ instance_id bigint unsigned default 0,
+ config_type smallint default 0,
+ host_object_id bigint unsigned default 0,
+ service_object_id bigint unsigned default 0,
+ display_name varchar(255) character set latin1 collate latin1_general_cs default '',
+ check_command_object_id bigint unsigned default 0,
+ check_command_args TEXT character set latin1,
+ eventhandler_command_object_id bigint unsigned default 0,
+ eventhandler_command_args TEXT character set latin1,
+ notification_timeperiod_object_id bigint unsigned default 0,
+ check_timeperiod_object_id bigint unsigned default 0,
+ failure_prediction_options varchar(64) character set latin1 default '',
+ check_interval double default '0',
+ retry_interval double default '0',
+ max_check_attempts smallint default 0,
+ first_notification_delay double default '0',
+ notification_interval double default '0',
+ notify_on_warning smallint default 0,
+ notify_on_unknown smallint default 0,
+ notify_on_critical smallint default 0,
+ notify_on_recovery smallint default 0,
+ notify_on_flapping smallint default 0,
+ notify_on_downtime smallint default 0,
+ stalk_on_ok smallint default 0,
+ stalk_on_warning smallint default 0,
+ stalk_on_unknown smallint default 0,
+ stalk_on_critical smallint default 0,
+ is_volatile smallint default 0,
+ flap_detection_enabled smallint default 0,
+ flap_detection_on_ok smallint default 0,
+ flap_detection_on_warning smallint default 0,
+ flap_detection_on_unknown smallint default 0,
+ flap_detection_on_critical smallint default 0,
+ low_flap_threshold double default '0',
+ high_flap_threshold double default '0',
+ process_performance_data smallint default 0,
+ freshness_checks_enabled smallint default 0,
+ freshness_threshold int default 0,
+ passive_checks_enabled smallint default 0,
+ event_handler_enabled smallint default 0,
+ active_checks_enabled smallint default 0,
+ retain_status_information smallint default 0,
+ retain_nonstatus_information smallint default 0,
+ notifications_enabled smallint default 0,
+ obsess_over_service smallint default 0,
+ failure_prediction_enabled smallint default 0,
+ notes TEXT character set latin1,
+ notes_url TEXT character set latin1,
+ action_url TEXT character set latin1,
+ icon_image TEXT character set latin1,
+ icon_image_alt TEXT character set latin1,
+ config_hash varchar(64) DEFAULT NULL,
+ PRIMARY KEY (service_id),
+ UNIQUE KEY instance_id (instance_id,config_type,service_object_id)
+) ENGINE=InnoDB COMMENT='Service definitions';
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table icinga_servicestatus
+--
+
+CREATE TABLE IF NOT EXISTS icinga_servicestatus (
+ servicestatus_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ instance_id bigint unsigned default 0,
+ service_object_id bigint unsigned default 0,
+ status_update_time timestamp NULL,
+ output TEXT character set latin1,
+ long_output TEXT,
+ perfdata TEXT character set latin1,
+ check_source varchar(255) character set latin1 default '',
+ current_state smallint default 0,
+ has_been_checked smallint default 0,
+ should_be_scheduled smallint default 0,
+ current_check_attempt smallint default 0,
+ max_check_attempts smallint default 0,
+ last_check timestamp NULL,
+ next_check timestamp NULL,
+ check_type smallint default 0,
+ last_state_change timestamp NULL,
+ last_hard_state_change timestamp NULL,
+ last_hard_state smallint default 0,
+ last_time_ok timestamp NULL,
+ last_time_warning timestamp NULL,
+ last_time_unknown timestamp NULL,
+ last_time_critical timestamp NULL,
+ state_type smallint default 0,
+ last_notification timestamp NULL,
+ next_notification timestamp NULL,
+ no_more_notifications smallint default 0,
+ notifications_enabled smallint default 0,
+ problem_has_been_acknowledged smallint default 0,
+ acknowledgement_type smallint default 0,
+ current_notification_number int unsigned default 0,
+ passive_checks_enabled smallint default 0,
+ active_checks_enabled smallint default 0,
+ event_handler_enabled smallint default 0,
+ flap_detection_enabled smallint default 0,
+ is_flapping smallint default 0,
+ percent_state_change double default '0',
+ latency double default '0',
+ execution_time double default '0',
+ scheduled_downtime_depth smallint default 0,
+ failure_prediction_enabled smallint default 0,
+ process_performance_data smallint default 0,
+ obsess_over_service smallint default 0,
+ modified_service_attributes int default 0,
+ original_attributes TEXT character set latin1 default NULL,
+ event_handler TEXT character set latin1,
+ check_command TEXT character set latin1,
+ normal_check_interval double default '0',
+ retry_check_interval double default '0',
+ check_timeperiod_object_id bigint unsigned default 0,
+ is_reachable smallint default 0,
+ PRIMARY KEY (servicestatus_id),
+ UNIQUE KEY object_id (service_object_id)
+) ENGINE=InnoDB COMMENT='Current service status information';
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table icinga_service_contactgroups
+--
+
+CREATE TABLE IF NOT EXISTS icinga_service_contactgroups (
+ service_contactgroup_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ instance_id bigint unsigned default 0,
+ service_id bigint unsigned default 0,
+ contactgroup_object_id bigint unsigned default 0,
+ PRIMARY KEY (service_contactgroup_id)
+) ENGINE=InnoDB COMMENT='Service contact groups';
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table icinga_service_contacts
+--
+
+CREATE TABLE IF NOT EXISTS icinga_service_contacts (
+ service_contact_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ instance_id bigint unsigned default 0,
+ service_id bigint unsigned default 0,
+ contact_object_id bigint unsigned default 0,
+ PRIMARY KEY (service_contact_id)
+) ENGINE=InnoDB COMMENT='Service contacts';
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table icinga_statehistory
+--
+
+CREATE TABLE IF NOT EXISTS icinga_statehistory (
+ statehistory_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ instance_id bigint unsigned default 0,
+ state_time timestamp NULL,
+ state_time_usec int default 0,
+ object_id bigint unsigned default 0,
+ state_change smallint default 0,
+ state smallint default 0,
+ state_type smallint default 0,
+ current_check_attempt smallint default 0,
+ max_check_attempts smallint default 0,
+ last_state smallint default 0,
+ last_hard_state smallint default 0,
+ output TEXT character set latin1,
+ long_output TEXT,
+ check_source varchar(255) character set latin1 default NULL,
+ PRIMARY KEY (statehistory_id)
+) ENGINE=InnoDB COMMENT='Historical host and service state changes';
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table icinga_systemcommands
+--
+
+CREATE TABLE IF NOT EXISTS icinga_systemcommands (
+ systemcommand_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ instance_id bigint unsigned default 0,
+ start_time timestamp NULL,
+ start_time_usec int default 0,
+ end_time timestamp NULL,
+ end_time_usec int default 0,
+ command_line TEXT character set latin1,
+ timeout smallint default 0,
+ early_timeout smallint default 0,
+ execution_time double default '0',
+ return_code smallint default 0,
+ output TEXT character set latin1,
+ long_output TEXT,
+ PRIMARY KEY (systemcommand_id),
+ UNIQUE KEY instance_id (instance_id,start_time,start_time_usec)
+) ENGINE=InnoDB COMMENT='Historical system commands that are executed';
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table icinga_timeperiods
+--
+
+CREATE TABLE IF NOT EXISTS icinga_timeperiods (
+ timeperiod_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ instance_id bigint unsigned default 0,
+ config_type smallint default 0,
+ timeperiod_object_id bigint unsigned default 0,
+ alias varchar(255) character set latin1 default '',
+ config_hash varchar(64) DEFAULT NULL,
+ PRIMARY KEY (timeperiod_id),
+ UNIQUE KEY instance_id (instance_id,config_type,timeperiod_object_id)
+) ENGINE=InnoDB COMMENT='Timeperiod definitions';
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table icinga_timeperiod_timeranges
+--
+
+CREATE TABLE IF NOT EXISTS icinga_timeperiod_timeranges (
+ timeperiod_timerange_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ instance_id bigint unsigned default 0,
+ timeperiod_id bigint unsigned default 0,
+ day smallint default 0,
+ start_sec int default 0,
+ end_sec int default 0,
+ PRIMARY KEY (timeperiod_timerange_id)
+) ENGINE=InnoDB COMMENT='Timeperiod definitions';
+
+
+-- --------------------------------------------------------
+-- Icinga 2 specific schema extensions
+-- --------------------------------------------------------
+
+--
+-- Table structure for table icinga_endpoints
+--
+
+CREATE TABLE IF NOT EXISTS icinga_endpoints (
+ endpoint_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ instance_id bigint unsigned default 0,
+ endpoint_object_id bigint(20) unsigned DEFAULT '0',
+ zone_object_id bigint(20) unsigned DEFAULT '0',
+ config_type smallint(6) DEFAULT '0',
+ identity varchar(255) DEFAULT NULL,
+ node varchar(255) DEFAULT NULL,
+ config_hash varchar(64) DEFAULT NULL,
+ PRIMARY KEY (endpoint_id)
+) ENGINE=InnoDB COMMENT='Endpoint configuration';
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table icinga_endpointstatus
+--
+
+CREATE TABLE IF NOT EXISTS icinga_endpointstatus (
+ endpointstatus_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ instance_id bigint unsigned default 0,
+ endpoint_object_id bigint(20) unsigned DEFAULT '0',
+ zone_object_id bigint(20) unsigned DEFAULT '0',
+ status_update_time timestamp NULL,
+ identity varchar(255) DEFAULT NULL,
+ node varchar(255) DEFAULT NULL,
+ is_connected smallint(6),
+ PRIMARY KEY (endpointstatus_id)
+) ENGINE=InnoDB COMMENT='Endpoint status';
+
+--
+-- Table structure for table icinga_zones
+--
+
+CREATE TABLE IF NOT EXISTS icinga_zones (
+ zone_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ instance_id bigint unsigned default 0,
+ zone_object_id bigint(20) unsigned DEFAULT '0',
+ config_type smallint(6) DEFAULT '0',
+ parent_zone_object_id bigint(20) unsigned DEFAULT '0',
+ is_global smallint(6),
+ config_hash varchar(64) DEFAULT NULL,
+ PRIMARY KEY (zone_id)
+) ENGINE=InnoDB COMMENT='Zone configuration';
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table icinga_zonestatus
+--
+
+CREATE TABLE IF NOT EXISTS icinga_zonestatus (
+ zonestatus_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ instance_id bigint unsigned default 0,
+ zone_object_id bigint(20) unsigned DEFAULT '0',
+ status_update_time timestamp NULL,
+ parent_zone_object_id bigint(20) unsigned DEFAULT '0',
+ PRIMARY KEY (zonestatus_id)
+) ENGINE=InnoDB COMMENT='Zone status';
+
+
+
+
+ALTER TABLE icinga_servicestatus ADD COLUMN endpoint_object_id bigint default NULL;
+ALTER TABLE icinga_hoststatus ADD COLUMN endpoint_object_id bigint default NULL;
+ALTER TABLE icinga_contactstatus ADD COLUMN endpoint_object_id bigint default NULL;
+ALTER TABLE icinga_programstatus ADD COLUMN endpoint_object_id bigint default NULL;
+ALTER TABLE icinga_comments ADD COLUMN endpoint_object_id bigint default NULL;
+ALTER TABLE icinga_scheduleddowntime ADD COLUMN endpoint_object_id bigint default NULL;
+ALTER TABLE icinga_runtimevariables ADD COLUMN endpoint_object_id bigint default NULL;
+ALTER TABLE icinga_customvariablestatus ADD COLUMN endpoint_object_id bigint default NULL;
+
+ALTER TABLE icinga_acknowledgements ADD COLUMN endpoint_object_id bigint default NULL;
+ALTER TABLE icinga_commenthistory ADD COLUMN endpoint_object_id bigint default NULL;
+ALTER TABLE icinga_contactnotifications ADD COLUMN endpoint_object_id bigint default NULL;
+ALTER TABLE icinga_downtimehistory ADD COLUMN endpoint_object_id bigint default NULL;
+ALTER TABLE icinga_eventhandlers ADD COLUMN endpoint_object_id bigint default NULL;
+ALTER TABLE icinga_externalcommands ADD COLUMN endpoint_object_id bigint default NULL;
+ALTER TABLE icinga_flappinghistory ADD COLUMN endpoint_object_id bigint default NULL;
+ALTER TABLE icinga_hostchecks ADD COLUMN endpoint_object_id bigint default NULL;
+ALTER TABLE icinga_logentries ADD COLUMN endpoint_object_id bigint default NULL;
+ALTER TABLE icinga_notifications ADD COLUMN endpoint_object_id bigint default NULL;
+ALTER TABLE icinga_processevents ADD COLUMN endpoint_object_id bigint default NULL;
+ALTER TABLE icinga_servicechecks ADD COLUMN endpoint_object_id bigint default NULL;
+ALTER TABLE icinga_statehistory ADD COLUMN endpoint_object_id bigint default NULL;
+ALTER TABLE icinga_systemcommands ADD COLUMN endpoint_object_id bigint default NULL;
+
+-- -----------------------------------------
+-- add index (delete)
+-- -----------------------------------------
+
+-- for periodic delete
+-- instance_id and
+-- SYSTEMCOMMANDS, SERVICECHECKS, HOSTCHECKS, EVENTHANDLERS => start_time
+-- EXTERNALCOMMANDS => entry_time
+
+-- instance_id
+CREATE INDEX servicechecks_i_id_idx on icinga_servicechecks(instance_id);
+CREATE INDEX hostchecks_i_id_idx on icinga_hostchecks(instance_id);
+CREATE INDEX externalcommands_i_id_idx on icinga_externalcommands(instance_id);
+
+-- time
+CREATE INDEX systemcommands_time_id_idx on icinga_systemcommands(start_time);
+CREATE INDEX servicechecks_time_id_idx on icinga_servicechecks(start_time);
+CREATE INDEX hostchecks_time_id_idx on icinga_hostchecks(start_time);
+CREATE INDEX eventhandlers_time_id_idx on icinga_eventhandlers(start_time);
+CREATE INDEX externalcommands_time_id_idx on icinga_externalcommands(entry_time);
+
+
+-- for starting cleanup - referenced in dbhandler.c:882
+-- instance_id only
+
+-- realtime data
+CREATE INDEX hoststatus_i_id_idx on icinga_hoststatus(instance_id);
+CREATE INDEX servicestatus_i_id_idx on icinga_servicestatus(instance_id);
+CREATE INDEX contactstatus_i_id_idx on icinga_contactstatus(instance_id);
+CREATE INDEX customvariablestatus_i_id_idx on icinga_customvariablestatus(instance_id);
+
+-- config data
+CREATE INDEX configfilevariables_i_id_idx on icinga_configfilevariables(instance_id);
+CREATE INDEX customvariables_i_id_idx on icinga_customvariables(instance_id);
+CREATE INDEX timeperiod_timeranges_i_id_idx on icinga_timeperiod_timeranges(instance_id);
+CREATE INDEX contactgroup_members_i_id_idx on icinga_contactgroup_members(instance_id);
+CREATE INDEX hostgroup_members_i_id_idx on icinga_hostgroup_members(instance_id);
+CREATE INDEX servicegroup_members_i_id_idx on icinga_servicegroup_members(instance_id);
+CREATE INDEX contact_addresses_i_id_idx on icinga_contact_addresses(instance_id);
+CREATE INDEX contact_notifcommands_i_id_idx on icinga_contact_notificationcommands(instance_id);
+CREATE INDEX host_parenthosts_i_id_idx on icinga_host_parenthosts(instance_id);
+CREATE INDEX host_contacts_i_id_idx on icinga_host_contacts(instance_id);
+CREATE INDEX service_contacts_i_id_idx on icinga_service_contacts(instance_id);
+CREATE INDEX service_contactgroups_i_id_idx on icinga_service_contactgroups(instance_id);
+CREATE INDEX host_contactgroups_i_id_idx on icinga_host_contactgroups(instance_id);
+CREATE INDEX hostesc_cgroups_i_id_idx on icinga_hostescalation_contactgroups(instance_id);
+CREATE INDEX serviceesc_cgroups_i_id_idx on icinga_serviceescalation_contactgroups(instance_id);
+
+-- -----------------------------------------
+-- more index stuff (WHERE clauses)
+-- -----------------------------------------
+
+-- hosts
+CREATE INDEX hosts_host_object_id_idx on icinga_hosts(host_object_id);
+
+-- hoststatus
+CREATE INDEX hoststatus_stat_upd_time_idx on icinga_hoststatus(status_update_time);
+CREATE INDEX hoststatus_current_state_idx on icinga_hoststatus(current_state);
+CREATE INDEX hoststatus_check_type_idx on icinga_hoststatus(check_type);
+CREATE INDEX hoststatus_state_type_idx on icinga_hoststatus(state_type);
+CREATE INDEX hoststatus_last_state_chg_idx on icinga_hoststatus(last_state_change);
+CREATE INDEX hoststatus_notif_enabled_idx on icinga_hoststatus(notifications_enabled);
+CREATE INDEX hoststatus_problem_ack_idx on icinga_hoststatus(problem_has_been_acknowledged);
+CREATE INDEX hoststatus_act_chks_en_idx on icinga_hoststatus(active_checks_enabled);
+CREATE INDEX hoststatus_pas_chks_en_idx on icinga_hoststatus(passive_checks_enabled);
+CREATE INDEX hoststatus_event_hdl_en_idx on icinga_hoststatus(event_handler_enabled);
+CREATE INDEX hoststatus_flap_det_en_idx on icinga_hoststatus(flap_detection_enabled);
+CREATE INDEX hoststatus_is_flapping_idx on icinga_hoststatus(is_flapping);
+CREATE INDEX hoststatus_p_state_chg_idx on icinga_hoststatus(percent_state_change);
+CREATE INDEX hoststatus_latency_idx on icinga_hoststatus(latency);
+CREATE INDEX hoststatus_ex_time_idx on icinga_hoststatus(execution_time);
+CREATE INDEX hoststatus_sch_downt_d_idx on icinga_hoststatus(scheduled_downtime_depth);
+
+-- services
+CREATE INDEX services_host_object_id_idx on icinga_services(host_object_id);
+
+-- servicestatus
+CREATE INDEX srvcstatus_stat_upd_time_idx on icinga_servicestatus(status_update_time);
+CREATE INDEX srvcstatus_current_state_idx on icinga_servicestatus(current_state);
+CREATE INDEX srvcstatus_check_type_idx on icinga_servicestatus(check_type);
+CREATE INDEX srvcstatus_state_type_idx on icinga_servicestatus(state_type);
+CREATE INDEX srvcstatus_last_state_chg_idx on icinga_servicestatus(last_state_change);
+CREATE INDEX srvcstatus_notif_enabled_idx on icinga_servicestatus(notifications_enabled);
+CREATE INDEX srvcstatus_problem_ack_idx on icinga_servicestatus(problem_has_been_acknowledged);
+CREATE INDEX srvcstatus_act_chks_en_idx on icinga_servicestatus(active_checks_enabled);
+CREATE INDEX srvcstatus_pas_chks_en_idx on icinga_servicestatus(passive_checks_enabled);
+CREATE INDEX srvcstatus_event_hdl_en_idx on icinga_servicestatus(event_handler_enabled);
+CREATE INDEX srvcstatus_flap_det_en_idx on icinga_servicestatus(flap_detection_enabled);
+CREATE INDEX srvcstatus_is_flapping_idx on icinga_servicestatus(is_flapping);
+CREATE INDEX srvcstatus_p_state_chg_idx on icinga_servicestatus(percent_state_change);
+CREATE INDEX srvcstatus_latency_idx on icinga_servicestatus(latency);
+CREATE INDEX srvcstatus_ex_time_idx on icinga_servicestatus(execution_time);
+CREATE INDEX srvcstatus_sch_downt_d_idx on icinga_servicestatus(scheduled_downtime_depth);
+
+-- hostchecks
+CREATE INDEX hostchks_h_obj_id_idx on icinga_hostchecks(host_object_id);
+
+-- servicechecks
+CREATE INDEX servicechks_s_obj_id_idx on icinga_servicechecks(service_object_id);
+
+-- objects
+CREATE INDEX objects_name1_idx ON icinga_objects(name1);
+CREATE INDEX objects_name2_idx ON icinga_objects(name2);
+CREATE INDEX objects_inst_id_idx ON icinga_objects(instance_id);
+
+-- instances
+-- CREATE INDEX instances_name_idx on icinga_instances(instance_name);
+
+-- logentries
+-- CREATE INDEX loge_instance_id_idx on icinga_logentries(instance_id);
+-- #236
+CREATE INDEX loge_time_idx on icinga_logentries(logentry_time);
+-- CREATE INDEX loge_data_idx on icinga_logentries(logentry_data);
+CREATE INDEX loge_inst_id_time_idx on icinga_logentries (instance_id ASC, logentry_time DESC);
+
+-- commenthistory
+-- CREATE INDEX c_hist_instance_id_idx on icinga_logentries(instance_id);
+-- CREATE INDEX c_hist_c_time_idx on icinga_logentries(comment_time);
+-- CREATE INDEX c_hist_i_c_id_idx on icinga_logentries(internal_comment_id);
+
+-- downtimehistory
+-- CREATE INDEX d_t_hist_nstance_id_idx on icinga_downtimehistory(instance_id);
+-- CREATE INDEX d_t_hist_type_idx on icinga_downtimehistory(downtime_type);
+-- CREATE INDEX d_t_hist_object_id_idx on icinga_downtimehistory(object_id);
+-- CREATE INDEX d_t_hist_entry_time_idx on icinga_downtimehistory(entry_time);
+-- CREATE INDEX d_t_hist_sched_start_idx on icinga_downtimehistory(scheduled_start_time);
+-- CREATE INDEX d_t_hist_sched_end_idx on icinga_downtimehistory(scheduled_end_time);
+
+-- scheduleddowntime
+-- CREATE INDEX sched_d_t_downtime_type_idx on icinga_scheduleddowntime(downtime_type);
+-- CREATE INDEX sched_d_t_object_id_idx on icinga_scheduleddowntime(object_id);
+-- CREATE INDEX sched_d_t_entry_time_idx on icinga_scheduleddowntime(entry_time);
+-- CREATE INDEX sched_d_t_start_time_idx on icinga_scheduleddowntime(scheduled_start_time);
+-- CREATE INDEX sched_d_t_end_time_idx on icinga_scheduleddowntime(scheduled_end_time);
+
+-- statehistory
+CREATE INDEX statehist_i_id_o_id_s_ty_s_ti on icinga_statehistory(instance_id, object_id, state_type, state_time);
+-- #2274
+create index statehist_state_idx on icinga_statehistory(object_id,state);
+
+
+-- Icinga Web Notifications
+CREATE INDEX notification_idx ON icinga_notifications(notification_type, object_id, start_time);
+CREATE INDEX notification_object_id_idx ON icinga_notifications(object_id);
+CREATE INDEX contact_notification_idx ON icinga_contactnotifications(notification_id, contact_object_id);
+CREATE INDEX contacts_object_id_idx ON icinga_contacts(contact_object_id);
+CREATE INDEX contact_notif_meth_notif_idx ON icinga_contactnotificationmethods(contactnotification_id, command_object_id);
+CREATE INDEX command_object_idx ON icinga_commands(object_id);
+CREATE INDEX services_combined_object_idx ON icinga_services(service_object_id, host_object_id);
+
+
+-- #2618
+CREATE INDEX cntgrpmbrs_cgid_coid ON icinga_contactgroup_members (contactgroup_id,contact_object_id);
+CREATE INDEX hstgrpmbrs_hgid_hoid ON icinga_hostgroup_members (hostgroup_id,host_object_id);
+CREATE INDEX hstcntgrps_hid_cgoid ON icinga_host_contactgroups (host_id,contactgroup_object_id);
+CREATE INDEX hstprnthsts_hid_phoid ON icinga_host_parenthosts (host_id,parent_host_object_id);
+CREATE INDEX runtimevars_iid_varn ON icinga_runtimevariables (instance_id,varname);
+CREATE INDEX sgmbrs_sgid_soid ON icinga_servicegroup_members (servicegroup_id,service_object_id);
+CREATE INDEX scgrps_sid_cgoid ON icinga_service_contactgroups (service_id,contactgroup_object_id);
+CREATE INDEX tperiod_tid_d_ss_es ON icinga_timeperiod_timeranges (timeperiod_id,day,start_sec,end_sec);
+
+-- #3649
+CREATE INDEX sla_idx_sthist ON icinga_statehistory (object_id, state_time DESC);
+CREATE INDEX sla_idx_dohist ON icinga_downtimehistory (object_id, actual_start_time, actual_end_time);
+CREATE INDEX sla_idx_obj ON icinga_objects (objecttype_id, is_active, name1);
+
+-- #4985
+CREATE INDEX commenthistory_delete_idx ON icinga_commenthistory (instance_id, comment_time, internal_comment_id);
+
+-- #10066
+CREATE INDEX idx_endpoints_object_id on icinga_endpoints(endpoint_object_id);
+CREATE INDEX idx_endpointstatus_object_id on icinga_endpointstatus(endpoint_object_id);
+
+CREATE INDEX idx_endpoints_zone_object_id on icinga_endpoints(zone_object_id);
+CREATE INDEX idx_endpointstatus_zone_object_id on icinga_endpointstatus(zone_object_id);
+
+CREATE INDEX idx_zones_object_id on icinga_zones(zone_object_id);
+CREATE INDEX idx_zonestatus_object_id on icinga_zonestatus(zone_object_id);
+
+CREATE INDEX idx_zones_parent_object_id on icinga_zones(parent_zone_object_id);
+CREATE INDEX idx_zonestatus_parent_object_id on icinga_zonestatus(parent_zone_object_id);
+
+-- #12210
+CREATE INDEX idx_comments_session_del ON icinga_comments (instance_id, session_token);
+CREATE INDEX idx_downtimes_session_del ON icinga_scheduleddowntime (instance_id, session_token);
+
+-- #12107
+CREATE INDEX idx_statehistory_cleanup on icinga_statehistory(instance_id, state_time);
+
+-- #12435
+CREATE INDEX idx_contactgroup_members_object_id on icinga_contactgroup_members(contact_object_id);
+CREATE INDEX idx_hostgroup_members_object_id on icinga_hostgroup_members(host_object_id);
+CREATE INDEX idx_servicegroup_members_object_id on icinga_servicegroup_members(service_object_id);
+CREATE INDEX idx_servicedependencies_dependent_service_object_id on icinga_servicedependencies(dependent_service_object_id);
+CREATE INDEX idx_hostdependencies_dependent_host_object_id on icinga_hostdependencies(dependent_host_object_id);
+CREATE INDEX idx_service_contacts_service_id on icinga_service_contacts(service_id);
+CREATE INDEX idx_host_contacts_host_id on icinga_host_contacts(host_id);
+
+-- #5458
+create index idx_downtimehistory_remove on icinga_downtimehistory (object_id, entry_time, scheduled_start_time, scheduled_end_time);
+create index idx_scheduleddowntime_remove on icinga_scheduleddowntime (object_id, entry_time, scheduled_start_time, scheduled_end_time);
+
+-- #5492
+CREATE INDEX idx_commenthistory_remove ON icinga_commenthistory (object_id, entry_time);
+CREATE INDEX idx_comments_remove ON icinga_comments (object_id, entry_time);
+
+-- -----------------------------------------
+-- set dbversion
+-- -----------------------------------------
+INSERT INTO icinga_dbversion (name, version, create_time, modify_time) VALUES ('idoutils', '1.15.1', NOW(), NOW())
+ON DUPLICATE KEY UPDATE version='1.15.1', modify_time=NOW();
+
+
diff --git a/lib/db_ido_mysql/schema/upgrade/2.0.2.sql b/lib/db_ido_mysql/schema/upgrade/2.0.2.sql
new file mode 100644
index 0000000..c622ae9
--- /dev/null
+++ b/lib/db_ido_mysql/schema/upgrade/2.0.2.sql
@@ -0,0 +1,20 @@
+-- -----------------------------------------
+-- upgrade path for Icinga 2.0.2
+--
+-- -----------------------------------------
+-- Icinga 2 | (c) 2012 Icinga GmbH | GPLv2+
+--
+-- Please check https://docs.icinga.com for upgrading information!
+-- -----------------------------------------
+
+UPDATE icinga_objects SET name2 = NULL WHERE name2 = '';
+
+ALTER TABLE `icinga_customvariables` MODIFY COLUMN `varname` varchar(255) character set latin1 collate latin1_general_cs default NULL;
+ALTER TABLE `icinga_customvariablestatus` MODIFY COLUMN `varname` varchar(255) character set latin1 collate latin1_general_cs default NULL;
+
+-- -----------------------------------------
+-- update dbversion
+-- -----------------------------------------
+
+INSERT INTO icinga_dbversion (name, version, create_time, modify_time) VALUES ('idoutils', '1.11.6', NOW(), NOW()) ON DUPLICATE KEY UPDATE version='1.11.6', modify_time=NOW();
+
diff --git a/lib/db_ido_mysql/schema/upgrade/2.1.0.sql b/lib/db_ido_mysql/schema/upgrade/2.1.0.sql
new file mode 100644
index 0000000..7bbed72
--- /dev/null
+++ b/lib/db_ido_mysql/schema/upgrade/2.1.0.sql
@@ -0,0 +1,17 @@
+-- -----------------------------------------
+-- upgrade path for Icinga 2.1.0
+--
+-- -----------------------------------------
+-- Icinga 2 | (c) 2012 Icinga GmbH | GPLv2+
+--
+-- Please check https://docs.icinga.com for upgrading information!
+-- -----------------------------------------
+
+ALTER TABLE `icinga_programstatus` ADD COLUMN `endpoint_name` varchar(255) character set latin1 collate latin1_general_cs default NULL;
+
+-- -----------------------------------------
+-- update dbversion
+-- -----------------------------------------
+
+INSERT INTO icinga_dbversion (name, version, create_time, modify_time) VALUES ('idoutils', '1.11.7', NOW(), NOW()) ON DUPLICATE KEY UPDATE version='1.11.7', modify_time=NOW();
+
diff --git a/lib/db_ido_mysql/schema/upgrade/2.11.0.sql b/lib/db_ido_mysql/schema/upgrade/2.11.0.sql
new file mode 100644
index 0000000..bafa93f
--- /dev/null
+++ b/lib/db_ido_mysql/schema/upgrade/2.11.0.sql
@@ -0,0 +1,89 @@
+-- -----------------------------------------
+-- upgrade path for Icinga 2.11.0
+--
+-- -----------------------------------------
+-- Copyright (c) 2019 Icinga Development Team (https://icinga.com/)
+--
+-- Please check https://docs.icinga.com for upgrading information!
+-- -----------------------------------------
+
+SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
+
+-- --------------------------------------------------------
+-- Helper functions and procedures for DROP INDEX IF EXISTS
+-- --------------------------------------------------------
+
+DELIMITER //
+DROP FUNCTION IF EXISTS ido_index_exists //
+CREATE FUNCTION ido_index_exists(
+ f_table_name varchar(64),
+ f_index_name varchar(64)
+)
+ RETURNS BOOL
+ DETERMINISTIC
+ READS SQL DATA
+ BEGIN
+ DECLARE index_exists BOOL DEFAULT FALSE;
+ SELECT EXISTS (
+ SELECT 1
+ FROM information_schema.statistics
+ WHERE table_schema = SCHEMA()
+ AND table_name = f_table_name
+ AND index_name = f_index_name
+ ) INTO index_exists;
+ RETURN index_exists;
+ END //
+
+DROP PROCEDURE IF EXISTS ido_drop_index_if_exists //
+CREATE PROCEDURE ido_drop_index_if_exists (
+ IN p_table_name varchar(64),
+ IN p_index_name varchar(64)
+)
+ DETERMINISTIC
+ MODIFIES SQL DATA
+ BEGIN
+ IF ido_index_exists(p_table_name, p_index_name)
+ THEN
+ SET @ido_drop_index_sql = CONCAT('ALTER TABLE `', SCHEMA(), '`.`', p_table_name, '` DROP INDEX `', p_index_name, '`');
+ PREPARE stmt FROM @ido_drop_index_sql;
+ EXECUTE stmt;
+ DEALLOCATE PREPARE stmt;
+ SET @ido_drop_index_sql = NULL;
+ END IF;
+ END //
+DELIMITER ;
+
+CALL ido_drop_index_if_exists('icinga_commands', 'commands_i_id_idx');
+CALL ido_drop_index_if_exists('icinga_comments', 'idx_comments_object_id');
+CALL ido_drop_index_if_exists('icinga_comments', 'comments_i_id_idx');
+CALL ido_drop_index_if_exists('icinga_configfiles', 'configfiles_i_id_idx');
+CALL ido_drop_index_if_exists('icinga_contactgroups', 'contactgroups_i_id_idx');
+CALL ido_drop_index_if_exists('icinga_contacts', 'contacts_i_id_idx');
+CALL ido_drop_index_if_exists('icinga_customvariables', 'idx_customvariables_object_id');
+CALL ido_drop_index_if_exists('icinga_eventhandlers', 'eventhandlers_i_id_idx');
+CALL ido_drop_index_if_exists('icinga_hostdependencies', 'hostdependencies_i_id_idx');
+CALL ido_drop_index_if_exists('icinga_hostescalations', 'hostesc_i_id_idx');
+CALL ido_drop_index_if_exists('icinga_hostescalation_contacts', 'hostesc_contacts_i_id_idx');
+CALL ido_drop_index_if_exists('icinga_hostgroups', 'hostgroups_i_id_idx');
+CALL ido_drop_index_if_exists('icinga_hosts', 'host_object_id');
+CALL ido_drop_index_if_exists('icinga_hosts', 'hosts_i_id_idx');
+CALL ido_drop_index_if_exists('icinga_objects', 'objects_objtype_id_idx');
+CALL ido_drop_index_if_exists('icinga_programstatus', 'programstatus_i_id_idx');
+CALL ido_drop_index_if_exists('icinga_runtimevariables', 'runtimevariables_i_id_idx');
+CALL ido_drop_index_if_exists('icinga_scheduleddowntime', 'scheduleddowntime_i_id_idx');
+CALL ido_drop_index_if_exists('icinga_scheduleddowntime', 'idx_scheduleddowntime_object_id');
+CALL ido_drop_index_if_exists('icinga_serviceescalations', 'serviceesc_i_id_idx');
+CALL ido_drop_index_if_exists('icinga_serviceescalation_contacts', 'serviceesc_contacts_i_id_idx');
+CALL ido_drop_index_if_exists('icinga_servicegroups', 'servicegroups_i_id_idx');
+CALL ido_drop_index_if_exists('icinga_services', 'services_i_id_idx');
+CALL ido_drop_index_if_exists('icinga_services', 'service_object_id');
+CALL ido_drop_index_if_exists('icinga_systemcommands', 'systemcommands_i_id_idx');
+CALL ido_drop_index_if_exists('icinga_timeperiods', 'timeperiods_i_id_idx');
+
+DROP FUNCTION ido_index_exists;
+DROP PROCEDURE ido_drop_index_if_exists;
+
+-- -----------------------------------------
+-- set dbversion (same as 2.11.0)
+-- -----------------------------------------
+INSERT INTO icinga_dbversion (name, version, create_time, modify_time) VALUES ('idoutils', '1.15.0', NOW(), NOW()) ON DUPLICATE KEY UPDATE version='1.15.0', modify_time=NOW();
diff --git a/lib/db_ido_mysql/schema/upgrade/2.12.7.sql b/lib/db_ido_mysql/schema/upgrade/2.12.7.sql
new file mode 100644
index 0000000..6319b37
--- /dev/null
+++ b/lib/db_ido_mysql/schema/upgrade/2.12.7.sql
@@ -0,0 +1,15 @@
+-- -----------------------------------------
+-- upgrade path for Icinga 2.12.7
+--
+-- -----------------------------------------
+-- Icinga 2 | (c) 2021 Icinga GmbH | GPLv2+
+--
+-- Please check https://docs.icinga.com for upgrading information!
+-- -----------------------------------------
+
+SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
+
+-- -------------
+-- set dbversion
+-- -------------
+INSERT INTO icinga_dbversion (name, version, create_time, modify_time) VALUES ('idoutils', '1.15.0', NOW(), NOW()) ON DUPLICATE KEY UPDATE version='1.15.0', modify_time=NOW();
diff --git a/lib/db_ido_mysql/schema/upgrade/2.13.0.sql b/lib/db_ido_mysql/schema/upgrade/2.13.0.sql
new file mode 100644
index 0000000..462be6f
--- /dev/null
+++ b/lib/db_ido_mysql/schema/upgrade/2.13.0.sql
@@ -0,0 +1,23 @@
+-- -----------------------------------------
+-- upgrade path for Icinga 2.13.0
+--
+-- -----------------------------------------
+-- Icinga 2 | (c) 2021 Icinga GmbH | GPLv2+
+--
+-- Please check https://docs.icinga.com for upgrading information!
+-- -----------------------------------------
+
+SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
+
+-- ----------------------------------------
+-- #7472 Support hosts with >128 characters
+-- ----------------------------------------
+
+ALTER TABLE icinga_objects
+ MODIFY COLUMN name1 varchar(255) character set latin1 collate latin1_general_cs default '',
+ MODIFY COLUMN name2 varchar(255) character set latin1 collate latin1_general_cs default NULL;
+
+-- -------------
+-- set dbversion
+-- -------------
+INSERT INTO icinga_dbversion (name, version, create_time, modify_time) VALUES ('idoutils', '1.15.1', NOW(), NOW()) ON DUPLICATE KEY UPDATE version='1.15.1', modify_time=NOW();
diff --git a/lib/db_ido_mysql/schema/upgrade/2.13.3.sql b/lib/db_ido_mysql/schema/upgrade/2.13.3.sql
new file mode 100644
index 0000000..577eb0a
--- /dev/null
+++ b/lib/db_ido_mysql/schema/upgrade/2.13.3.sql
@@ -0,0 +1,15 @@
+-- -----------------------------------------
+-- upgrade path for Icinga 2.13.3
+--
+-- -----------------------------------------
+-- Icinga 2 | (c) 2021 Icinga GmbH | GPLv2+
+--
+-- Please check https://docs.icinga.com for upgrading information!
+-- -----------------------------------------
+
+SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
+
+-- -------------
+-- set dbversion
+-- -------------
+INSERT INTO icinga_dbversion (name, version, create_time, modify_time) VALUES ('idoutils', '1.15.1', NOW(), NOW()) ON DUPLICATE KEY UPDATE version='1.15.1', modify_time=NOW();
diff --git a/lib/db_ido_mysql/schema/upgrade/2.2.0.sql b/lib/db_ido_mysql/schema/upgrade/2.2.0.sql
new file mode 100644
index 0000000..22a6115
--- /dev/null
+++ b/lib/db_ido_mysql/schema/upgrade/2.2.0.sql
@@ -0,0 +1,23 @@
+-- -----------------------------------------
+-- upgrade path for Icinga 2.2.0
+--
+-- -----------------------------------------
+-- Icinga 2 | (c) 2012 Icinga GmbH | GPLv2+
+--
+-- Please check https://docs.icinga.com for upgrading information!
+-- -----------------------------------------
+
+ALTER TABLE `icinga_programstatus` ADD COLUMN `program_version` varchar(64) character set latin1 collate latin1_general_cs default NULL;
+
+ALTER TABLE icinga_contacts MODIFY alias TEXT character set latin1;
+ALTER TABLE icinga_hosts MODIFY alias TEXT character set latin1;
+
+ALTER TABLE icinga_customvariables ADD COLUMN is_json smallint default 0;
+ALTER TABLE icinga_customvariablestatus ADD COLUMN is_json smallint default 0;
+
+-- -----------------------------------------
+-- update dbversion
+-- -----------------------------------------
+
+INSERT INTO icinga_dbversion (name, version, create_time, modify_time) VALUES ('idoutils', '1.12.0', NOW(), NOW()) ON DUPLICATE KEY UPDATE version='1.12.0', modify_time=NOW();
+
diff --git a/lib/db_ido_mysql/schema/upgrade/2.3.0.sql b/lib/db_ido_mysql/schema/upgrade/2.3.0.sql
new file mode 100644
index 0000000..f2fe463
--- /dev/null
+++ b/lib/db_ido_mysql/schema/upgrade/2.3.0.sql
@@ -0,0 +1,26 @@
+-- -----------------------------------------
+-- upgrade path for Icinga 2.3.0
+--
+-- -----------------------------------------
+-- Icinga 2 | (c) 2012 Icinga GmbH | GPLv2+
+--
+-- Please check https://docs.icinga.com for upgrading information!
+-- -----------------------------------------
+
+-- -----------------------------------------
+-- #7765 drop unique constraint
+-- -----------------------------------------
+
+ALTER TABLE icinga_servicedependencies DROP KEY instance_id;
+ALTER TABLE icinga_hostdependencies DROP KEY instance_id;
+
+ALTER TABLE icinga_servicedependencies ADD KEY instance_id (instance_id,config_type,service_object_id,dependent_service_object_id,dependency_type,inherits_parent,fail_on_ok,fail_on_warning,fail_on_unknown,fail_on_critical);
+ALTER TABLE icinga_hostdependencies ADD KEY instance_id (instance_id,config_type,host_object_id,dependent_host_object_id,dependency_type,inherits_parent,fail_on_up,fail_on_down,fail_on_unreachable);
+
+
+-- -----------------------------------------
+-- update dbversion
+-- -----------------------------------------
+
+INSERT INTO icinga_dbversion (name, version, create_time, modify_time) VALUES ('idoutils', '1.13.0', NOW(), NOW()) ON DUPLICATE KEY UPDATE version='1.13.0', modify_time=NOW();
+
diff --git a/lib/db_ido_mysql/schema/upgrade/2.4.0.sql b/lib/db_ido_mysql/schema/upgrade/2.4.0.sql
new file mode 100644
index 0000000..f6803f7
--- /dev/null
+++ b/lib/db_ido_mysql/schema/upgrade/2.4.0.sql
@@ -0,0 +1,75 @@
+-- -----------------------------------------
+-- upgrade path for Icinga 2.4.0
+--
+-- -----------------------------------------
+-- Icinga 2 | (c) 2012 Icinga GmbH | GPLv2+
+--
+-- Please check https://docs.icinga.com for upgrading information!
+-- -----------------------------------------
+
+-- -----------------------------------------
+-- #9286 - zone tables
+-- -----------------------------------------
+
+ALTER TABLE icinga_endpoints ADD COLUMN zone_object_id bigint(20) unsigned DEFAULT '0';
+ALTER TABLE icinga_endpointstatus ADD COLUMN zone_object_id bigint(20) unsigned DEFAULT '0';
+
+CREATE TABLE IF NOT EXISTS icinga_zones (
+ zone_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ instance_id bigint unsigned default 0,
+ zone_object_id bigint(20) unsigned DEFAULT '0',
+ config_type smallint(6) DEFAULT '0',
+ parent_zone_object_id bigint(20) unsigned DEFAULT '0',
+ is_global smallint(6),
+ PRIMARY KEY (zone_id)
+) ENGINE=InnoDB COMMENT='Zone configuration';
+
+CREATE TABLE IF NOT EXISTS icinga_zonestatus (
+ zonestatus_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ instance_id bigint unsigned default 0,
+ zone_object_id bigint(20) unsigned DEFAULT '0',
+ status_update_time timestamp NOT NULL,
+ parent_zone_object_id bigint(20) unsigned DEFAULT '0',
+ PRIMARY KEY (zonestatus_id)
+) ENGINE=InnoDB COMMENT='Zone status';
+
+
+-- -----------------------------------------
+-- #9576 - freshness_threshold
+-- -----------------------------------------
+
+ALTER TABLE icinga_services MODIFY freshness_threshold int;
+ALTER TABLE icinga_hosts MODIFY freshness_threshold int;
+
+-- -----------------------------------------
+-- #10392 - original attributes
+-- -----------------------------------------
+
+ALTER TABLE icinga_servicestatus ADD COLUMN original_attributes TEXT character set latin1 default NULL;
+ALTER TABLE icinga_hoststatus ADD COLUMN original_attributes TEXT character set latin1 default NULL;
+
+-- -----------------------------------------
+-- #10436 deleted custom vars
+-- -----------------------------------------
+
+ALTER TABLE icinga_customvariables ADD COLUMN session_token int default NULL;
+ALTER TABLE icinga_customvariablestatus ADD COLUMN session_token int default NULL;
+
+CREATE INDEX cv_session_del_idx ON icinga_customvariables (session_token);
+CREATE INDEX cvs_session_del_idx ON icinga_customvariablestatus (session_token);
+
+-- -----------------------------------------
+-- #10431 comment/downtime name
+-- -----------------------------------------
+
+ALTER TABLE icinga_comments ADD COLUMN name TEXT character set latin1 default NULL;
+ALTER TABLE icinga_commenthistory ADD COLUMN name TEXT character set latin1 default NULL;
+
+ALTER TABLE icinga_scheduleddowntime ADD COLUMN name TEXT character set latin1 default NULL;
+ALTER TABLE icinga_downtimehistory ADD COLUMN name TEXT character set latin1 default NULL;
+
+-- -----------------------------------------
+-- update dbversion
+-- -----------------------------------------
+
+INSERT INTO icinga_dbversion (name, version, create_time, modify_time) VALUES ('idoutils', '1.14.0', NOW(), NOW()) ON DUPLICATE KEY UPDATE version='1.14.0', modify_time=NOW();
diff --git a/lib/db_ido_mysql/schema/upgrade/2.5.0.sql b/lib/db_ido_mysql/schema/upgrade/2.5.0.sql
new file mode 100644
index 0000000..d5714a0
--- /dev/null
+++ b/lib/db_ido_mysql/schema/upgrade/2.5.0.sql
@@ -0,0 +1,103 @@
+-- -----------------------------------------
+-- upgrade path for Icinga 2.5.0
+--
+-- -----------------------------------------
+-- Icinga 2 | (c) 2012 Icinga GmbH | GPLv2+
+--
+-- Please check https://docs.icinga.com for upgrading information!
+-- -----------------------------------------
+
+SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
+
+-- -----------------------------------------
+-- #10069 IDO: check_source should not be a TEXT field
+-- -----------------------------------------
+
+ALTER TABLE icinga_hoststatus MODIFY COLUMN check_source varchar(255) character set latin1 default '';
+ALTER TABLE icinga_servicestatus MODIFY COLUMN check_source varchar(255) character set latin1 default '';
+
+-- -----------------------------------------
+-- #10070
+-- -----------------------------------------
+
+CREATE INDEX idx_comments_object_id on icinga_comments(object_id);
+CREATE INDEX idx_scheduleddowntime_object_id on icinga_scheduleddowntime(object_id);
+
+-- -----------------------------------------
+-- #11962
+-- -----------------------------------------
+
+ALTER TABLE icinga_hoststatus MODIFY COLUMN current_notification_number int unsigned default 0;
+ALTER TABLE icinga_servicestatus MODIFY COLUMN current_notification_number int unsigned default 0;
+
+-- -----------------------------------------
+-- #10061
+-- -----------------------------------------
+
+ALTER TABLE icinga_contactgroups MODIFY COLUMN alias varchar(255) character set latin1 default '';
+ALTER TABLE icinga_contacts MODIFY COLUMN alias varchar(255) character set latin1 default '';
+ALTER TABLE icinga_hostgroups MODIFY COLUMN alias varchar(255) character set latin1 default '';
+ALTER TABLE icinga_hosts MODIFY COLUMN alias varchar(255) character set latin1 default '';
+ALTER TABLE icinga_servicegroups MODIFY COLUMN alias varchar(255) character set latin1 default '';
+ALTER TABLE icinga_timeperiods MODIFY COLUMN alias varchar(255) character set latin1 default '';
+
+-- -----------------------------------------
+-- #10066
+-- -----------------------------------------
+
+CREATE INDEX idx_endpoints_object_id on icinga_endpoints(endpoint_object_id);
+CREATE INDEX idx_endpointstatus_object_id on icinga_endpointstatus(endpoint_object_id);
+
+CREATE INDEX idx_endpoints_zone_object_id on icinga_endpoints(zone_object_id);
+CREATE INDEX idx_endpointstatus_zone_object_id on icinga_endpointstatus(zone_object_id);
+
+CREATE INDEX idx_zones_object_id on icinga_zones(zone_object_id);
+CREATE INDEX idx_zonestatus_object_id on icinga_zonestatus(zone_object_id);
+
+CREATE INDEX idx_zones_parent_object_id on icinga_zones(parent_zone_object_id);
+CREATE INDEX idx_zonestatus_parent_object_id on icinga_zonestatus(parent_zone_object_id);
+
+-- -----------------------------------------
+-- #12107
+-- -----------------------------------------
+CREATE INDEX idx_statehistory_cleanup on icinga_statehistory(instance_id, state_time);
+
+-- -----------------------------------------
+-- #12258
+-- -----------------------------------------
+ALTER TABLE icinga_comments ADD COLUMN session_token INTEGER default NULL;
+ALTER TABLE icinga_scheduleddowntime ADD COLUMN session_token INTEGER default NULL;
+
+CREATE INDEX idx_comments_session_del ON icinga_comments (instance_id, session_token);
+CREATE INDEX idx_downtimes_session_del ON icinga_scheduleddowntime (instance_id, session_token);
+
+-- -----------------------------------------
+-- #12435
+-- -----------------------------------------
+ALTER TABLE icinga_commands ADD config_hash VARCHAR(64) DEFAULT NULL;
+ALTER TABLE icinga_contactgroups ADD config_hash VARCHAR(64) DEFAULT NULL;
+ALTER TABLE icinga_contacts ADD config_hash VARCHAR(64) DEFAULT NULL;
+ALTER TABLE icinga_hostgroups ADD config_hash VARCHAR(64) DEFAULT NULL;
+ALTER TABLE icinga_hosts ADD config_hash VARCHAR(64) DEFAULT NULL;
+ALTER TABLE icinga_servicegroups ADD config_hash VARCHAR(64) DEFAULT NULL;
+ALTER TABLE icinga_services ADD config_hash VARCHAR(64) DEFAULT NULL;
+ALTER TABLE icinga_timeperiods ADD config_hash VARCHAR(64) DEFAULT NULL;
+ALTER TABLE icinga_endpoints ADD config_hash VARCHAR(64) DEFAULT NULL;
+ALTER TABLE icinga_zones ADD config_hash VARCHAR(64) DEFAULT NULL;
+
+ALTER TABLE icinga_customvariables DROP session_token;
+ALTER TABLE icinga_customvariablestatus DROP session_token;
+
+CREATE INDEX idx_customvariables_object_id on icinga_customvariables(object_id);
+CREATE INDEX idx_contactgroup_members_object_id on icinga_contactgroup_members(contact_object_id);
+CREATE INDEX idx_hostgroup_members_object_id on icinga_hostgroup_members(host_object_id);
+CREATE INDEX idx_servicegroup_members_object_id on icinga_servicegroup_members(service_object_id);
+CREATE INDEX idx_servicedependencies_dependent_service_object_id on icinga_servicedependencies(dependent_service_object_id);
+CREATE INDEX idx_hostdependencies_dependent_host_object_id on icinga_hostdependencies(dependent_host_object_id);
+CREATE INDEX idx_service_contacts_service_id on icinga_service_contacts(service_id);
+CREATE INDEX idx_host_contacts_host_id on icinga_host_contacts(host_id);
+
+-- -----------------------------------------
+-- set dbversion
+-- -----------------------------------------
+INSERT INTO icinga_dbversion (name, version, create_time, modify_time) VALUES ('idoutils', '1.14.1', NOW(), NOW()) ON DUPLICATE KEY UPDATE version='1.14.1', modify_time=NOW();
diff --git a/lib/db_ido_mysql/schema/upgrade/2.6.0.sql b/lib/db_ido_mysql/schema/upgrade/2.6.0.sql
new file mode 100644
index 0000000..33dd780
--- /dev/null
+++ b/lib/db_ido_mysql/schema/upgrade/2.6.0.sql
@@ -0,0 +1,151 @@
+-- -----------------------------------------
+-- upgrade path for Icinga 2.6.0
+--
+-- -----------------------------------------
+-- Icinga 2 | (c) 2012 Icinga GmbH | GPLv2+
+--
+-- Please check https://docs.icinga.com for upgrading information!
+-- -----------------------------------------
+
+-- -----------------------------------------
+-- #10502 IDO: Support NO_ZERO_DATE and NO_ZERO_IN_DATE SQL modes
+-- -----------------------------------------
+
+ALTER TABLE icinga_acknowledgements
+ MODIFY COLUMN entry_time timestamp NULL,
+ MODIFY COLUMN end_time timestamp NULL;
+
+ALTER TABLE icinga_commenthistory
+ MODIFY COLUMN entry_time timestamp NULL,
+ MODIFY COLUMN comment_time timestamp NULL,
+ MODIFY COLUMN expiration_time timestamp NULL,
+ MODIFY COLUMN deletion_time timestamp NULL;
+
+ALTER TABLE icinga_comments
+ MODIFY COLUMN entry_time timestamp NULL,
+ MODIFY COLUMN comment_time timestamp NULL,
+ MODIFY COLUMN expiration_time timestamp NULL;
+
+ALTER TABLE icinga_conninfo
+ MODIFY COLUMN connect_time timestamp NULL,
+ MODIFY COLUMN disconnect_time timestamp NULL,
+ MODIFY COLUMN last_checkin_time timestamp NULL,
+ MODIFY COLUMN data_start_time timestamp NULL,
+ MODIFY COLUMN data_end_time timestamp NULL;
+
+ALTER TABLE icinga_contactnotificationmethods
+ MODIFY COLUMN start_time timestamp NULL,
+ MODIFY COLUMN end_time timestamp NULL;
+
+ALTER TABLE icinga_contactnotifications
+ MODIFY COLUMN start_time timestamp NULL,
+ MODIFY COLUMN end_time timestamp NULL;
+
+ALTER TABLE icinga_contactstatus
+ MODIFY COLUMN status_update_time timestamp NULL,
+ MODIFY COLUMN last_host_notification timestamp NULL,
+ MODIFY COLUMN last_service_notification timestamp NULL;
+
+ALTER TABLE icinga_customvariablestatus
+ MODIFY COLUMN status_update_time timestamp NULL;
+
+ALTER TABLE icinga_dbversion
+ MODIFY COLUMN create_time timestamp NULL,
+ MODIFY COLUMN modify_time timestamp NULL;
+
+ALTER TABLE icinga_downtimehistory
+ MODIFY COLUMN entry_time timestamp NULL,
+ MODIFY COLUMN scheduled_start_time timestamp NULL,
+ MODIFY COLUMN scheduled_end_time timestamp NULL,
+ MODIFY COLUMN actual_start_time timestamp NULL,
+ MODIFY COLUMN actual_end_time timestamp NULL,
+ MODIFY COLUMN trigger_time timestamp NULL;
+
+ALTER TABLE icinga_eventhandlers
+ MODIFY COLUMN start_time timestamp NULL,
+ MODIFY COLUMN end_time timestamp NULL;
+
+ALTER TABLE icinga_externalcommands
+ MODIFY COLUMN entry_time timestamp NULL;
+
+ALTER TABLE icinga_flappinghistory
+ MODIFY COLUMN event_time timestamp NULL,
+ MODIFY COLUMN comment_time timestamp NULL;
+
+ALTER TABLE icinga_hostchecks
+ MODIFY COLUMN start_time timestamp NULL,
+ MODIFY COLUMN end_time timestamp NULL;
+
+ALTER TABLE icinga_hoststatus
+ MODIFY COLUMN status_update_time timestamp NULL,
+ MODIFY COLUMN last_check timestamp NULL,
+ MODIFY COLUMN next_check timestamp NULL,
+ MODIFY COLUMN last_state_change timestamp NULL,
+ MODIFY COLUMN last_hard_state_change timestamp NULL,
+ MODIFY COLUMN last_time_up timestamp NULL,
+ MODIFY COLUMN last_time_down timestamp NULL,
+ MODIFY COLUMN last_time_unreachable timestamp NULL,
+ MODIFY COLUMN last_notification timestamp NULL,
+ MODIFY COLUMN next_notification timestamp NULL;
+
+ALTER TABLE icinga_logentries
+ MODIFY COLUMN logentry_time timestamp NULL,
+ MODIFY COLUMN entry_time timestamp NULL;
+
+ALTER TABLE icinga_notifications
+ MODIFY COLUMN start_time timestamp NULL,
+ MODIFY COLUMN end_time timestamp NULL;
+
+ALTER TABLE icinga_processevents
+ MODIFY COLUMN event_time timestamp NULL;
+
+ALTER TABLE icinga_programstatus
+ MODIFY COLUMN status_update_time timestamp NULL,
+ MODIFY COLUMN program_start_time timestamp NULL,
+ MODIFY COLUMN program_end_time timestamp NULL,
+ MODIFY COLUMN last_command_check timestamp NULL,
+ MODIFY COLUMN last_log_rotation timestamp NULL,
+ MODIFY COLUMN disable_notif_expire_time timestamp NULL;
+
+ALTER TABLE icinga_scheduleddowntime
+ MODIFY COLUMN entry_time timestamp NULL,
+ MODIFY COLUMN scheduled_start_time timestamp NULL,
+ MODIFY COLUMN scheduled_end_time timestamp NULL,
+ MODIFY COLUMN actual_start_time timestamp NULL,
+ MODIFY COLUMN trigger_time timestamp NULL;
+
+ALTER TABLE icinga_servicechecks
+ MODIFY COLUMN start_time timestamp NULL,
+ MODIFY COLUMN end_time timestamp NULL;
+
+ALTER TABLE icinga_servicestatus
+ MODIFY COLUMN status_update_time timestamp NULL,
+ MODIFY COLUMN last_check timestamp NULL,
+ MODIFY COLUMN next_check timestamp NULL,
+ MODIFY COLUMN last_state_change timestamp NULL,
+ MODIFY COLUMN last_hard_state_change timestamp NULL,
+ MODIFY COLUMN last_time_ok timestamp NULL,
+ MODIFY COLUMN last_time_warning timestamp NULL,
+ MODIFY COLUMN last_time_unknown timestamp NULL,
+ MODIFY COLUMN last_time_critical timestamp NULL,
+ MODIFY COLUMN last_notification timestamp NULL,
+ MODIFY COLUMN next_notification timestamp NULL;
+
+ALTER TABLE icinga_statehistory
+ MODIFY COLUMN state_time timestamp NULL;
+
+ALTER TABLE icinga_systemcommands
+ MODIFY COLUMN start_time timestamp NULL,
+ MODIFY COLUMN end_time timestamp NULL;
+
+ALTER TABLE icinga_endpointstatus
+ MODIFY COLUMN status_update_time timestamp NULL;
+
+ALTER TABLE icinga_zonestatus
+ MODIFY COLUMN status_update_time timestamp NULL;
+
+-- -----------------------------------------
+-- set dbversion
+-- -----------------------------------------
+INSERT INTO icinga_dbversion (name, version, create_time, modify_time) VALUES ('idoutils', '1.14.2', NOW(), NOW())
+ON DUPLICATE KEY UPDATE version='1.14.2', modify_time=NOW();
diff --git a/lib/db_ido_mysql/schema/upgrade/2.8.0.sql b/lib/db_ido_mysql/schema/upgrade/2.8.0.sql
new file mode 100644
index 0000000..8d511a7
--- /dev/null
+++ b/lib/db_ido_mysql/schema/upgrade/2.8.0.sql
@@ -0,0 +1,81 @@
+-- -----------------------------------------
+-- upgrade path for Icinga 2.8.0
+--
+-- -----------------------------------------
+-- Icinga 2 | (c) 2012 Icinga GmbH | GPLv2+
+--
+-- Please check https://docs.icinga.com for upgrading information!
+-- -----------------------------------------
+
+SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
+
+-- --------------------------------------------------------
+-- Helper functions and procedures for DROP INDEX IF EXISTS
+-- --------------------------------------------------------
+
+DELIMITER //
+DROP FUNCTION IF EXISTS ido_index_exists //
+CREATE FUNCTION ido_index_exists(
+ f_table_name varchar(64),
+ f_index_name varchar(64)
+)
+ RETURNS BOOL
+ DETERMINISTIC
+ READS SQL DATA
+ BEGIN
+ DECLARE index_exists BOOL DEFAULT FALSE;
+ SELECT EXISTS (
+ SELECT 1
+ FROM information_schema.statistics
+ WHERE table_schema = SCHEMA()
+ AND table_name = f_table_name
+ AND index_name = f_index_name
+ ) INTO index_exists;
+ RETURN index_exists;
+ END //
+
+DROP PROCEDURE IF EXISTS ido_drop_index_if_exists //
+CREATE PROCEDURE ido_drop_index_if_exists (
+ IN p_table_name varchar(64),
+ IN p_index_name varchar(64)
+)
+ DETERMINISTIC
+ MODIFIES SQL DATA
+ BEGIN
+ IF ido_index_exists(p_table_name, p_index_name)
+ THEN
+ SET @ido_drop_index_sql = CONCAT('ALTER TABLE `', SCHEMA(), '`.`', p_table_name, '` DROP INDEX `', p_index_name, '`');
+ PREPARE stmt FROM @ido_drop_index_sql;
+ EXECUTE stmt;
+ DEALLOCATE PREPARE stmt;
+ SET @ido_drop_index_sql = NULL;
+ END IF;
+ END //
+DELIMITER ;
+
+CALL ido_drop_index_if_exists('icinga_downtimehistory', 'instance_id');
+CALL ido_drop_index_if_exists('icinga_scheduleddowntime', 'instance_id');
+CALL ido_drop_index_if_exists('icinga_commenthistory', 'instance_id');
+CALL ido_drop_index_if_exists('icinga_comments', 'instance_id');
+
+DROP FUNCTION ido_index_exists;
+DROP PROCEDURE ido_drop_index_if_exists;
+
+-- -----------------------------------------
+-- #5458 IDO: Improve downtime removal/cancel
+-- -----------------------------------------
+
+CREATE INDEX idx_downtimehistory_remove ON icinga_downtimehistory (object_id, entry_time, scheduled_start_time, scheduled_end_time);
+CREATE INDEX idx_scheduleddowntime_remove ON icinga_scheduleddowntime (object_id, entry_time, scheduled_start_time, scheduled_end_time);
+
+-- -----------------------------------------
+-- #5492 IDO: Improve comment removal
+-- -----------------------------------------
+
+CREATE INDEX idx_commenthistory_remove ON icinga_commenthistory (object_id, entry_time);
+CREATE INDEX idx_comments_remove ON icinga_comments (object_id, entry_time);
+
+-- -----------------------------------------
+-- set dbversion
+-- -----------------------------------------
+INSERT INTO icinga_dbversion (name, version, create_time, modify_time) VALUES ('idoutils', '1.14.3', NOW(), NOW()) ON DUPLICATE KEY UPDATE version='1.14.3', modify_time=NOW();
diff --git a/lib/db_ido_mysql/schema/upgrade/2.8.1.sql b/lib/db_ido_mysql/schema/upgrade/2.8.1.sql
new file mode 100644
index 0000000..98f8511
--- /dev/null
+++ b/lib/db_ido_mysql/schema/upgrade/2.8.1.sql
@@ -0,0 +1,67 @@
+-- -----------------------------------------
+-- upgrade path for Icinga 2.8.1 (fix for fresh 2.8.0 installation only)
+--
+-- -----------------------------------------
+-- Icinga 2 | (c) 2012 Icinga GmbH | GPLv2+
+--
+-- Please check https://docs.icinga.com for upgrading information!
+-- -----------------------------------------
+
+SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
+
+-- --------------------------------------------------------
+-- Helper functions and procedures for DROP INDEX IF EXISTS
+-- --------------------------------------------------------
+
+DELIMITER //
+DROP FUNCTION IF EXISTS ido_index_exists //
+CREATE FUNCTION ido_index_exists(
+ f_table_name varchar(64),
+ f_index_name varchar(64)
+)
+ RETURNS BOOL
+ DETERMINISTIC
+ READS SQL DATA
+ BEGIN
+ DECLARE index_exists BOOL DEFAULT FALSE;
+ SELECT EXISTS (
+ SELECT 1
+ FROM information_schema.statistics
+ WHERE table_schema = SCHEMA()
+ AND table_name = f_table_name
+ AND index_name = f_index_name
+ ) INTO index_exists;
+ RETURN index_exists;
+ END //
+
+DROP PROCEDURE IF EXISTS ido_drop_index_if_exists //
+CREATE PROCEDURE ido_drop_index_if_exists (
+ IN p_table_name varchar(64),
+ IN p_index_name varchar(64)
+)
+ DETERMINISTIC
+ MODIFIES SQL DATA
+ BEGIN
+ IF ido_index_exists(p_table_name, p_index_name)
+ THEN
+ SET @ido_drop_index_sql = CONCAT('ALTER TABLE `', SCHEMA(), '`.`', p_table_name, '` DROP INDEX `', p_index_name, '`');
+ PREPARE stmt FROM @ido_drop_index_sql;
+ EXECUTE stmt;
+ DEALLOCATE PREPARE stmt;
+ SET @ido_drop_index_sql = NULL;
+ END IF;
+ END //
+DELIMITER ;
+
+CALL ido_drop_index_if_exists('icinga_downtimehistory', 'instance_id');
+CALL ido_drop_index_if_exists('icinga_scheduleddowntime', 'instance_id');
+CALL ido_drop_index_if_exists('icinga_commenthistory', 'instance_id');
+CALL ido_drop_index_if_exists('icinga_comments', 'instance_id');
+
+DROP FUNCTION ido_index_exists;
+DROP PROCEDURE ido_drop_index_if_exists;
+
+-- -----------------------------------------
+-- set dbversion (same as 2.8.0)
+-- -----------------------------------------
+INSERT INTO icinga_dbversion (name, version, create_time, modify_time) VALUES ('idoutils', '1.14.3', NOW(), NOW()) ON DUPLICATE KEY UPDATE version='1.14.3', modify_time=NOW();