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.