[icinga-checkins] icinga.org: icinga2/master: DB IDO: Use upsert and session token for comment/downtime updates

git at icinga.org git at icinga.org
Tue Aug 2 14:38:30 CEST 2016


Module: icinga2
Branch: master
Commit: cd5c9366cf80f24d6e5cc0a3426109b26f47b98f
URL:    https://git.icinga.org/?p=icinga2.git;a=commit;h=cd5c9366cf80f24d6e5cc0a3426109b26f47b98f

Author: Michael Friedrich <michael.friedrich at netways.de>
Date:   Mon Aug  1 17:54:03 2016 +0200

DB IDO: Use upsert and session token for comment/downtime updates

refs #12258
fixes #12288

---

 lib/db_ido/dbevents.cpp                   |   39 +++++++++++++++++------------
 lib/db_ido_mysql/idomysqlconnection.cpp   |    2 ++
 lib/db_ido_mysql/schema/mysql.sql         |    3 +++
 lib/db_ido_mysql/schema/upgrade/2.5.0.sql |    9 +++++++
 lib/db_ido_pgsql/idopgsqlconnection.cpp   |    2 ++
 lib/db_ido_pgsql/schema/pgsql.sql         |    5 ++++
 lib/db_ido_pgsql/schema/upgrade/2.5.0.sql |    9 +++++++
 7 files changed, 53 insertions(+), 16 deletions(-)

diff --git a/lib/db_ido/dbevents.cpp b/lib/db_ido/dbevents.cpp
index 709cc8a..847a760 100644
--- a/lib/db_ido/dbevents.cpp
+++ b/lib/db_ido/dbevents.cpp
@@ -305,17 +305,6 @@ void DbEvents::AddComments(const Checkable::Ptr& checkable)
 
 	std::vector<DbQuery> queries;
 
-	/* Ensure to delete all comments and then insert any or none.
-	 * We must purge obsolete comments in the database at all cost. */
-
-	DbQuery query1;
-	query1.Table = "comments";
-	query1.Type = DbQueryDelete;
-	query1.Category = DbCatComment;
-	query1.WhereCriteria = new Dictionary();
-	query1.WhereCriteria->Set("object_id", checkable);
-	queries.push_back(query1);
-
 	BOOST_FOREACH(const Comment::Ptr& comment, comments) {
 		AddCommentInternal(queries, comment, false);
 	}
@@ -326,7 +315,6 @@ void DbEvents::AddComments(const Checkable::Ptr& checkable)
 void DbEvents::AddComment(const Comment::Ptr& comment)
 {
 	std::vector<DbQuery> queries;
-	RemoveCommentInternal(queries, comment);
 	AddCommentInternal(queries, comment, false);
 	DbObject::OnMultipleQueries(queries);
 }
@@ -382,10 +370,19 @@ void DbEvents::AddCommentInternal(std::vector<DbQuery>& queries, const Comment::
 	DbQuery query1;
 	if (!historical) {
 		query1.Table = "comments";
+		query1.Type = DbQueryInsert | DbQueryUpdate;
+
+		fields1->Set("session_token", 0); /* DbConnection class fills in real ID */
+
+		query1.WhereCriteria = new Dictionary();
+		query1.WhereCriteria->Set("internal_comment_id", comment->GetLegacyId());
+		query1.WhereCriteria->Set("object_id", checkable);
+		query1.WhereCriteria->Set("comment_time", DbValue::FromTimestamp(entry_time));
+		query1.WhereCriteria->Set("instance_id", 0); /* DbConnection class fills in real ID */
 	} else {
 		query1.Table = "commenthistory";
+		query1.Type = DbQueryInsert;
 	}
-	query1.Type = DbQueryInsert;
 	query1.Category = DbCatComment;
 	query1.Fields = fields1;
 
@@ -520,12 +517,22 @@ void DbEvents::AddDowntimeInternal(std::vector<DbQuery>& queries, const Downtime
 
 	DbQuery query1;
 
-	if (!historical)
+	if (!historical) {
 		query1.Table = "scheduleddowntime";
-	else
+		query1.Type = DbQueryInsert | DbQueryUpdate;
+
+		fields1->Set("session_token", 0); /* DbConnection class fills in real ID */
+
+		query1.WhereCriteria = new Dictionary();
+		query1.WhereCriteria->Set("object_id", checkable);
+		query1.WhereCriteria->Set("internal_downtime_id", downtime->GetLegacyId());
+		query1.WhereCriteria->Set("entry_time", DbValue::FromTimestamp(downtime->GetEntryTime()));
+		query1.WhereCriteria->Set("instance_id", 0); /* DbConnection class fills in real ID */
+	} else {
 		query1.Table = "downtimehistory";
+		query1.Type = DbQueryInsert;
+	}
 
-	query1.Type = DbQueryInsert;
 	query1.Category = DbCatDowntime;
 	query1.Fields = fields1;
 
diff --git a/lib/db_ido_mysql/idomysqlconnection.cpp b/lib/db_ido_mysql/idomysqlconnection.cpp
index c781d8f..0c7165a 100644
--- a/lib/db_ido_mysql/idomysqlconnection.cpp
+++ b/lib/db_ido_mysql/idomysqlconnection.cpp
@@ -442,6 +442,8 @@ void IdoMysqlConnection::ClearTablesBySession(void)
 	ClearTableBySession("hostgroup_members");
 	ClearTableBySession("servicegroup_members");
 	ClearTableBySession("contactgroup_members");
+	ClearTableBySession("comments");
+	ClearTableBySession("scheduleddowntime");
 }
 
 void IdoMysqlConnection::ClearTableBySession(const String& table)
diff --git a/lib/db_ido_mysql/schema/mysql.sql b/lib/db_ido_mysql/schema/mysql.sql
index 24c8be8..b9142c7 100644
--- a/lib/db_ido_mysql/schema/mysql.sql
+++ b/lib/db_ido_mysql/schema/mysql.sql
@@ -110,6 +110,7 @@ CREATE TABLE IF NOT EXISTS icinga_comments (
   expires smallint default 0,
   expiration_time timestamp  default '0000-00-00 00:00:00',
   name TEXT character set latin1 default NULL,
+  session_token int default NULL,
   PRIMARY KEY  (comment_id),
   UNIQUE KEY instance_id (instance_id,object_id,comment_time,internal_comment_id)
 ) ENGINE=InnoDB  COMMENT='Usercomments on Icinga objects';
@@ -1669,6 +1670,8 @@ CREATE INDEX idx_cg_session_del ON icinga_contactgroup_members (instance_id, ses
 CREATE INDEX idx_cv_session_del ON icinga_customvariables (instance_id, session_token);
 CREATE INDEX idx_cvs_session_del ON icinga_customvariablestatus (instance_id, session_token);
 
+CREATE INDEX idx_comments_session_del ON icinga_comments (instance_id, session_token);
+
 -- #12107
 CREATE INDEX idx_statehistory_cleanup on icinga_statehistory(instance_id, state_time);
 
diff --git a/lib/db_ido_mysql/schema/upgrade/2.5.0.sql b/lib/db_ido_mysql/schema/upgrade/2.5.0.sql
index 89fc0d8..d1ee47b 100644
--- a/lib/db_ido_mysql/schema/upgrade/2.5.0.sql
+++ b/lib/db_ido_mysql/schema/upgrade/2.5.0.sql
@@ -79,6 +79,15 @@ CREATE INDEX idx_cv_session_del ON icinga_customvariables (instance_id, session_
 CREATE INDEX idx_cvs_session_del ON icinga_customvariablestatus (instance_id, session_token);
 
 -- -----------------------------------------
+-- #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);
+
+-- -----------------------------------------
 -- 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_pgsql/idopgsqlconnection.cpp b/lib/db_ido_pgsql/idopgsqlconnection.cpp
index 71a9709..55e2b98 100644
--- a/lib/db_ido_pgsql/idopgsqlconnection.cpp
+++ b/lib/db_ido_pgsql/idopgsqlconnection.cpp
@@ -413,6 +413,8 @@ void IdoPgsqlConnection::ClearTablesBySession(void)
 	ClearTableBySession("hostgroup_members");
 	ClearTableBySession("servicegroup_members");
 	ClearTableBySession("contactgroup_members");
+	ClearTableBySession("comments");
+	ClearTableBySession("scheduleddowntime");
 }
 
 void IdoPgsqlConnection::ClearTableBySession(const String& table)
diff --git a/lib/db_ido_pgsql/schema/pgsql.sql b/lib/db_ido_pgsql/schema/pgsql.sql
index e56f4f9..afbdbe0 100644
--- a/lib/db_ido_pgsql/schema/pgsql.sql
+++ b/lib/db_ido_pgsql/schema/pgsql.sql
@@ -136,6 +136,7 @@ CREATE TABLE  icinga_comments (
   expires INTEGER  default 0,
   expiration_time timestamp with time zone default '1970-01-01 00:00:00+00',
   name TEXT default NULL,
+  session_token INTEGER default NULL,
   CONSTRAINT PK_comment_id PRIMARY KEY (comment_id) ,
   CONSTRAINT UQ_comments UNIQUE (instance_id,object_id,comment_time,internal_comment_id)
 )  ;
@@ -1005,6 +1006,7 @@ CREATE TABLE  icinga_scheduleddowntime (
   is_in_effect INTEGER  default 0,
   trigger_time timestamp with time zone default '1970-01-01 00:00:00+00',
   name TEXT default NULL,
+  session_token INTEGER default NULL,
   CONSTRAINT PK_scheduleddowntime_id PRIMARY KEY (scheduleddowntime_id) ,
   CONSTRAINT UQ_scheduleddowntime UNIQUE (instance_id,object_id,entry_time,internal_downtime_id)
 ) ;
@@ -1695,6 +1697,9 @@ CREATE INDEX idx_hg_session_del ON icinga_hostgroup_members (instance_id, sessio
 CREATE INDEX idx_sg_session_del ON icinga_servicegroup_members (instance_id, session_token);
 CREATE INDEX idx_cg_session_del ON icinga_contactgroup_members (instance_id, session_token);
 
+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);
+
 CREATE INDEX idx_cv_session_del ON icinga_customvariables (instance_id, session_token);
 CREATE INDEX idx_cvs_session_del ON icinga_customvariablestatus (instance_id, session_token);
 
diff --git a/lib/db_ido_pgsql/schema/upgrade/2.5.0.sql b/lib/db_ido_pgsql/schema/upgrade/2.5.0.sql
index ed52b2b..a5c2b58 100644
--- a/lib/db_ido_pgsql/schema/upgrade/2.5.0.sql
+++ b/lib/db_ido_pgsql/schema/upgrade/2.5.0.sql
@@ -57,6 +57,15 @@ CREATE INDEX idx_cv_session_del ON icinga_customvariables (instance_id, session_
 CREATE INDEX idx_cvs_session_del ON icinga_customvariablestatus (instance_id, session_token);
 
 -- -----------------------------------------
+-- #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);
+
+-- -----------------------------------------
 -- #12107
 -- -----------------------------------------
 CREATE INDEX idx_statehistory_cleanup on icinga_statehistory(instance_id, state_time);



More information about the icinga-checkins mailing list