[Work-around fix] Synchronise between I Doit and RT using multiple DB

[Schwick]

Ever since 0.9.6 we would have problems synchronising between I Doit and RT. In I Doit we have a database per company. In the following post I will attempt to guide you through a fix.
First however, let's explain what the problem is.

The problem:
So for example we the following databases in I Doit (which in term are companies)

• Com1
• Com2
  We also have the RT database called RTDB.

Now lets say RT has the following objects with an I Doit ID of 1,2,3,4,5,6.
Com1 has the following ID's 1,2,3,4,5,6
Com 2 has the following ID's 1,2,3,6

Upon synchronising Com 1 we get 0 added, 0 deleted and 0 altered. So far so good.
Upon synchronising Com 2 we get 0 added, 2 deleted and 0 altered. This is not good.
Upon synchronising Com 2 again, we get 2 added, 0 deleted and 0 altered. Do it again and 5 and 6 get deleted again.

A delete should only happen if I Doit deleted the object. RT should then in term delete that old object for RT.

So what's happening:
The function get_customfieldvalues in the isys_module_dao_request_tracker_remote class has the following query:

SELECT Description, id FROM CustomFieldValues WHERE Description IS NOT NULL

So if somebody would run this query, you would get all objects instead of only the desired object for a certain database (in the examples case for Com 2, I only want id 1,2,3,4,6).

The solution:
We made a workaround to fix this problem. RT's database is going to have an extra field which holds information from which database this data originates from. We also made a table within each I Doit database which holds which database this is (we couldn't find a function inside I Doit which could do this, so we would do it like this). Last but not least, we made some custom functions and altered a few codes.

isys_module_request_tracker.class.php: _\src\classes\modules_
Find:

		/**
			 * Retrieve existing custom values from RT
			 */
			$l_customfieldvalues_ids = $l_dao_rt->get_customfieldvalues_ids();

Alter with:

			/**
			 * Retrieve existing custom values from RT
			 */
            $DBname = $l_dao->get_db_info();
			$l_customfieldvalues_ids = $l_dao_rt->get_customfieldvalues_ids_where_dbname($DBname);

Find:

$l_dao_rt->insert_customfieldvalue($l_key, $l_val2["id"], $l_val2["title"]);

Alter with:

$l_dao_rt->insert_customfieldvalue2($l_key, $l_val2["id"], $l_val2["title"],$DBname);

isys_module_dao_request_tracker_remote.class.php: _\src\classes\modules\dao_
add the following:

/**
	 * Gets all Object Ids from Customfields (hack from NLCom)
	 *
     * @param String Database name
	 * @return array
	 */
	public function get_customfieldvalues_ids_where_dbname($dbname){
		$l_query = "SELECT Description, id FROM CustomFieldValues WHERE Description IS NOT NULL && FromIDOITdb='".$dbname."'";
		$l_res = $this->retrieve($l_query);
		while($row = $l_res->get_row()){
			$l_arr[$row["id"]] = $row["Description"];
		}

		return $l_arr;
	}

    	/**
	 * Inserts new Customfieldvalue dataset (hack by NLcom)
	 *
	 * @param int $p_objType_id Object Type Id
	 * @param int $p_obj_id Object Id
	 * @param String $p_obj_title Object title
     * @param String Database name
	 * @return boolean
	 */
	public function insert_customfieldvalue2($p_objType_id, $p_obj_id, $p_obj_title, $DBname){
		$l_customfield_id = $this->get_customfield_by_obj_type($p_objType_id);

		$l_query  = "SELECT id FROM Users WHERE Name = '".C__REQUEST_TRACKER_CONFIG__DEFAULT_USER."';";
		$l_res    = $this->retrieve($l_query);
		$l_row    = $l_res->get_row();
		$l_userID = $l_row["id"];

		if ($l_userID > 0) {

			$l_update = "INSERT INTO CustomFieldValues SET ".
							"CustomField   = ".$this->convert_sql_id($l_customfield_id).", ".
							"Description   = ".$this->convert_sql_id($p_obj_id).", ".
							"Name 		   = ".$this->convert_sql_text($p_obj_title).", ".
							"Creator       = ".$this->convert_sql_id($l_userID).", ".
							"Created       = NOW(), ".
							"LastUpdatedBy = ".$this->convert_sql_id($l_userID).", ".
							"LastUpdated   = NOW(), ".
                            "FromIDOITdb   = '".$DBname."';";		
			if ($this->update($l_update) && $this->apply_update())
				return true;
		}

		return false;		
	}

isys_cmdb_dao.class.php: _\src\cmdb\dao_
Add:

    /**
	 * Return the name which DB this class is connected to (NLCom hack)
	 *
	 * @return SQL results
	 */
    public function get_db_info()
    {
        $l_query = "SELECT DBname FROM isys_dbInfo";
		$result = $this->retrieve($l_query);
        $row = $result->get_row();
        return $row["DBname"];
    }

SQL I Doit:
Run the following script per database. Also alter the DATABASENAMEHERE to your database name :

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

CREATE TABLE IF NOT EXISTS `isys_dbInfo` (
  `DBname` varchar(255) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `isys_dbInfo`
--

INSERT INTO `isys_dbInfo` (`DBname`) VALUES
('DATABASENAMEHERE');

SQL RT:
Run the following SQL script for the RT database:

ALTER TABLE `CustomFieldValues` ADD `FromIDOITdb` VARCHAR( 255 ) NOT NULL;

That's it, it should work now. If you got any further questions, feel free to contact me.

[ds]

Hi,
First of all thanks for your detailed explanation.

I would like to inform you that we currently rewriting the RT integration with the intention to make much more use of the API RT has. That means that we will no longer synchronize data between the two instance but directly fetching information from each other. For getting this to work we implemented a complete new API inside i-doit which allows every 3rd party application to retrieve almost everything important from the cmdb. To make use of that API, we are developing an RT plugin which will call the corresponding API methods from i-doit to retrieve the objects. After retrieving we just store the Object and i-doit mandator ID inside a custom field, so it should be possible to connect to different i-doit databases (mandators). I think you will have to select your mandator inside the "new-ticket" form when you have more than one so that the API knows how and where to log in.. I hope it will be possible to display  the object name in this way after saving the ticket though.
Ok, lets talk about the other way around now. We created a new underlaying ticketing abstraction which defines all of the abstract ticketing core data i-doit wants to play with. That means we will also be open for other ticket systems in future (which is very sweeet ;)). Regarding RT, we are currently filling one of these abstractions with life, which means we are interacting with RT via REST and converting everything into an internal structure. So i-doit is able to retrieve tickets over a direct HTTP connection on-the-fly, instead of a synchonizing cron-job (i think this is sweet as well :)).
The features of that re-implementation will be quite the same:

• You will have a ticketing category for each object, where i-doit is fetching tickets regarding this object. (Hope this will be fast enough in large environments because the customfield reference is not indexed by default but i think mr. cache is our friend in these hard days..
• There will be a custom field inside the new-ticket form in RT with a suggestion function for cmdb objects (which will be extended by a real object browser in future)
  But these "same-looking" features are slightly different to the old implementation: Theres no redundant data anymore and we are able to extend the interface to other systems like OTRS.

Regards,
Dennis

[Schwick]

Thanks for the reply, it's good to hear changes are incoming. In regard to the information you provided I was wondering if you could answer a few questions:

• When can we expect these changes to be coming? (ETA? Version?)
• Reading from your reply I see thinks will work again with multiple databases. Just to be sure, will your solution once again work with multiple database inside I Doit when synchronising to RT?
• Will synchronising still maintain the same (easy) steps or will it require more work?
• Will additional systems be integrated into the synchronisation?
• Or can we make our own additional plugins that will handle synchronisation?

Thanks and keep up the good work!

[ds]

1. The planned version with these changes is the version after the next, which will be released aprox. in the 1st quarter of 2012.
2. What do you mean with "multiple databases inside i-doit" ? Different i-doit databases, or more than one RT installation? Different i-doit databases (mandators) will work.
3. Synchronizing is not required anymore. The configuration will be easier than the current. You will just need the IP or RT and the username and password for an API enabled user in RT. And you will need to install the new i-doit plugin for RT of course, which will implement the i-doit features inside RT.
4. Which additional systems for example?
5. Depends on what kind of plugins you are talking about. But as i mentioned in my post, it will be possible to integrate other ticket systems easily. Because all the basics and dependencies for i-doit are clearly defined.

[Schwick]

Thanks for the reply, everything is clear in terms of answers.

@2: Mandators indeed. Good stuff.
@4-5: This was just curiosity on our part. Nothing planned yet but who knows, you'll never know when another system comes along and we may want to integrate that one.