API Exporter For The Done Done Issue Tracker
It all started when a number of us over at Classy Llama decided to stop using the Done Done issue tracking service and move to a self-hosted Active Collab based project management system. We weren’t merely moving to Active Collab for issue tracking, we needed a way to track time, along with a hoard of other things. Tracking time was something that we’d been doing in Unfuddle, but it wasn’t working very well because it was separate from our issue tracking software, and we couldn’t let clients onto Unfuddle due to it’s limited access control abilities. Now moving project management systems is no small hassle: clients we’d already engaged with needed to start using a new system that was entirely foreign to them, and to top it off, there isn’t generally any easy way to migrate data from one system to another. Open issues on in-progress projects were manually moved into Active Collab, permissions were set, and we jumped in head first using this new system. In short, we LOVE it. Is there room for improvement? Definitely! None of the themes available for it present the information in any sort of polished manner, but among the few themes out there, the Modern theme by CreativeWorld does the best job. So with a couple of design changes, we started using it, and are planning on letting it evolve further as time progresses.
After running on our new system for about two months, it came time to terminate the accounts with both Unfuddle and DoneDone. Being the man in charge of IT at CLS, that job fell on my shoulders, and thus began the quest of creating backups of all data past and present in both of those systems. I had already begun self-hosting our Subversion repositories about 6 months prior, including moving all of them from Unfuddle to our own dedicated server, so that made the account closure much more doable. Unfuddle provides a relatively easy way to create backups of each project, but Done Done on the other hand? Nothing… there is NO way provided by the authors of DoneDone that allows you to readly export or backup the data stored in your account! We needed a backup though, and I can’t just close an account and lose all of the highly important written communication revolving around the dozens of projects we’d used DoneDone with. Thankfully, there is a rather basic SOAP API provided by Done Done. This API is by no means all inclusive, and is rather poorly designed if I dare say so myself, it does not even provide a way to read nor modify user details. But, what it does do is allow me to programmatically pull a list of all projects and a list of issues in each of those projects, along with all of the issue’s historical (i.e. comments and status changes) data. Being as familar with PHP as I am, I sat down to write a script to load the information into four MySql tables. About two and a half hours later, I had a roughly put together and approximately 200 line script that did just that.
Being fairly certain that we are not the only ones that have ever used DoneDone before, I’m going to make the logical assumption that someone else might just find this script useful, both as a backup tool and a migration assistant, and am releasing here it under the OSL 3.0 license.
There are two things you should keep in mind when using this script.
- It’s best run using the account owners username and password, otherwise it won’t have access to all projects on Done Done.
- You must set each project in Done Done to allow API access.
You can download a zipped copy of the code or take a peek at it here:
<?php
/**
* mdd-backup.php
*
* @author http://davidalger.com/
* @copyright Copyright (c) 2011 David Alger & Classy Llama Studios, LLC
* @license http://opensource.org/licenses/osl-3.0.php Open Software License (OSL 3.0)
*/
/*
* Information needed to connect to the database the data from MyDoneDone will be stored in.
*/
define('DB_CONN', 'unix_socket=/usr/local/zend/mysql/tmp/mysql.sock'); // I'm using a socket, but you should be able to use any type of connection here.
define('DB_NAME', 'portalcl_portal_dev'); // update this with your database name
define('DB_USER', 'root'); // update this with your database user
define('DB_PASS', 'root'); // update this with the password for the given database user
/*
* Connection informtion for the MyDoneDone account owner.
*/
define('MDD_WSDL', 'https://classyllama.mydonedone.com/api/DoneDone.asmx?WSDL'); // change this to the URL for your account WSDL
define('MDD_USER', ''); // set this to the username of the account owner
define('MDD_PASS', ''); // set this to the password on your user account
/**
* Quick and dirty method to easily print an error message and die if an error occurs while reading/writing to the database.
*
* @param string $message
* @return void
*/
function db_fail($message) {
global $db;
echo "\n\nDB FAILURE: $message\n";
var_dump($db->errorInfo());
die;
}
/**
* Prints a message to the shell..
*
* @param string $message
* @return void
*/
function status($message) {
echo $message."\n";
}
/*
* Initialize the database tables that will store the data.
*/
try {
// connect to database
$db = new PDO('mysql:'.DB_CONN.';dbname='.DB_NAME, DB_USER, DB_PASS);
// drop tables if we need to
$sql = <<<SQL
DROP TABLE IF EXISTS `mdd_issue_history`;
DROP TABLE IF EXISTS `mdd_issue`;
DROP TABLE IF EXISTS `mdd_user`;
DROP TABLE IF EXISTS `mdd_project`;
SQL;
$db->exec($sql);
// create project table
$sql = <<<SQL
CREATE TABLE IF NOT EXISTS `mdd_project` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255),
PRIMARY KEY (`id`)
)ENGINE=INNODB;
SQL;
$db->exec($sql);
// create user table
$sql = <<<SQL
CREATE TABLE IF NOT EXISTS `mdd_user` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` varchar(255),
`company` varchar(255),
PRIMARY KEY (`id`)
)ENGINE=INNODB;
SQL;
$db->exec($sql);
// create issue table
$sql = <<<SQL
CREATE TABLE IF NOT EXISTS `mdd_issue` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`project_id` INT(10) UNSIGNED NOT NULL,
`created_at` DATETIME,
`updated_at` DATETIME,
`title` VARCHAR(255),
`description` TEXT,
`creator_user_id` INT(10) UNSIGNED NOT NULL,
`resolver_user_id` INT(10) UNSIGNED NOT NULL,
`STATUS` VARCHAR(255),
`priority` VARCHAR(255),
PRIMARY KEY (`id`),
FOREIGN KEY (`project_id`) REFERENCES `mdd_project` (`id`),
FOREIGN KEY (`creator_user_id`) REFERENCES `mdd_user` (`id`),
FOREIGN KEY (`resolver_user_id`) REFERENCES `mdd_user` (`id`)
)ENGINE=INNODB;
SQL;
$db->exec($sql);
// create issue history table
$sql = <<<SQL
CREATE TABLE IF NOT EXISTS `mdd_issue_history` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`issue_id` INT (10) UNSIGNED NOT NULL,
`title` VARCHAR(255),
`description` TEXT,
`created_at` DATETIME,
`creator_name` VARCHAR(255),
PRIMARY KEY (`id`),
FOREIGN KEY (`issue_id`) REFERENCES `mdd_issue` (`id`)
)ENGINE=INNODB;
SQL;
$db->exec($sql);
} catch (Exception $e) {
die($e->getMessage());
}
/*
* MyDoneDone API Connection
*/
// init wsdl
$client = new SoapClient(MDD_WSDL);
// authenticate with API
$res = $client->login(
array(
'username_or_email' => MDD_USER,
'password' => MDD_PASS,
)
);
// fail if login wasn't succesfull
if ($res->LoginResult !== true) {
die('Login failed!');
}
// user cache array
$users = array();
// load the project list
$projects = $client->getProjects();
$projects = $projects->GetProjectsResult->ProjectInfo;
// iterate all projects collecting data
foreach ($projects as $project) {
// insert project record into db
if ($db->exec("INSERT INTO `mdd_project` (`name`) VALUES (".$db->quote($project->Name).")") === false) {
db_fail('Insert to mdd_project failed.');
};
$pid = $db->lastInsertId();
status("Created project $pid with name of {$project->Name}");
// load project issues
$issues = $client->getIssuesInProject(array(
'project_id' => $project->ID,
'should_load_issue_details' => true,
));
$issues = $issues->GetIssuesInProjectResult->IssueInfo;
// skip processing if there are no issues.
if (!is_array($issues) || (is_array($issues) && count($issues) == 0)) {
continue;
}
// iterate issues
foreach ($issues as $issue) {
// create creator user if not exists
if (!isset($users[$issue->Creator->ID])) {
$sql = "INSERT INTO `mdd_user` (`name`, `company`) VALUES (".$db->quote($issue->Creator->Name).", ".$db->quote($issue->Creator->CompanyName).")";
if ($db->exec($sql) === false) {
db_fail('Insert into mdd_issue');
}
$users[$issue->Creator->ID] = $db->lastInsertId();
status("Created user {$users[$issue->Creator->ID]} with name of {$issue->Creator->Name} for {$issue->Creator->CompanyName}");
}
// create resolver user if not exists
if (!isset($users[$issue->Resolver->ID])) {
$sql = "INSERT INTO `mdd_user` (`name`, `company`) VALUES (".$db->quote($issue->Resolver->Name).", ".$db->quote($issue->Resolver->CompanyName).")";
if ($db->exec($sql) === false) {
db_fail('Insert into mdd_issue');
}
$users[$issue->Resolver->ID] = $db->lastInsertId();
status("Created user {$users[$issue->Resolver->ID]} with name of {$issue->Resolver->Name} for {$issue->Resolver->CompanyName}");
}
// insert issue id
$sql = "INSERT INTO `mdd_issue` (`project_id`, `created_at`, `updated_at`, `title`, `description`, `creator_user_id`, `resolver_user_id`, `status`, `priority`)
VALUES ('$pid', '{$issue->CreateDate}', '{$issue->UpdateDate}', ".$db->quote($issue->Title).", ".$db->quote($issue->Description).", '{$users[$issue->Creator->ID]}', '{$users[$issue->Resolver->ID]}', '{$issue->IssueStatus}', '{$issue->PriorityLevel}')";
if ($db->exec($sql) === false) {
db_fail('Insert into mdd_issue failed.');
}
$issueId = $db->lastInsertId();
status("Created issue $issueId for project $pid with title of {$issue->Title}");
// grab history into convenience var
$history = $issue->History->History;
// skip if there are no records
if (!is_array($history) || (is_array($history) && count($history) == 0)) {
continue;
}
// iterate issue history records
foreach ($history as $record) {
// insert history record
$sql = "INSERT INTO `mdd_issue_history` (`issue_id`, `title`, `description`, `created_at`, `creator_name`)
VALUES ('$issueId', ".$db->quote($record->Title).", ".$db->quote($record->Description).", ".$db->quote($record->CreateDate).", ".$db->quote($record->CreatorName).")";
if ($db->exec($sql) === false) {
db_fail('Insert into mdd_issue_history failed.');
}
status("Created history record for issue $issueId with title of {$record->Title}");
}
}
}
// deauthenticate
$client->logout();