Monday, April 26, 2010

Gantt Project and Bugzilla "Integration" part 2 with gpath

A number of people (okay one person) asked for the code behind my bugzilla gantt project update script. So here is a re-written and simplified script. I tried out the gpath notation within groovy as well, which is quite nice. It is very quick and natural to code in once you get the hang of it. One thing that I found lacking documentation was how to take a groovy node and output it as xml here is a code snippet that renders the Node variable "theNode" into a file called "test.xml" which I hope is helpful:
def formatter = new XmlNodePrinter(new PrintWriter(new File("test.xml")))
formatter.setPreserveWhitespace true
formatter.print(theNode)

Here is the groovy script to update a gantt project file from a Bugzilla database. You will need to update the section under "user defined variables" with your project name, the new project file name, and connection information to your Bugzilla database. The script requires that you map your tasks to Bugzilla bugs with a custom column (called "Bugs" by default) and that you have a custom column called "Bug Status", this is where the bug status information will be placed (it will overwrite whatever is in there now). Other than that, add the mysql driver to your class-path, make sure you have groovy installed and away you go.
Again this is a rudimentary solution but....well read my previous post for that story.

import groovy.sql.Sql

//user definable variables
def sql = Sql.newInstance("jdbc:mysql://HOST/BUGZILLA USER", "BUGZILLA USER","BUGZILLA PASS", "com.mysql.jdbc.Driver") //enter params to connect to bugs database
def filename = "proj.gan" //file name of the source project plan
def target ="rev_proj.gan"//target name for the new project plan
def bugPropName = "Bug" //what custom column the "bug" number is in
def bugStatusPropName = "Bug Status" //what custom column the "bug" number is in


//parse the file so we can use gpath
def xml=new File(filename).getText()
def root = new XmlParser().parseText(xml)

def bugProp
def bugStatusProp
//map the bug properties into their internal ids
for (aTaskProp in root.tasks.taskproperties.taskproperty) {
if(bugPropName.equalsIgnoreCase(aTaskProp['@name'])){
bugProp=aTaskProp['@id']
}else if(bugStatusPropName.equalsIgnoreCase(aTaskProp['@name'])){
bugStatusProp=aTaskProp['@id']
}
}


if(bugProp == null || bugStatusProp == null){
println("Must have a custom property setup called 'Bug' and one for 'Bug Status' in your project")
return
}

//closure used to set the custom property
def setOrAddCustomProperty = { taskNode, propId, value ->
//loop through again from the begining if we found the bug and look for the
//property we need to set. It may be before the bug, order is not guaranteed
foundIt = false
for (aCustompropertySub in taskNode.customproperty) {
if(aCustompropertySub['@taskproperty-id'].equals(propId)){
aCustompropertySub['@value'] = value
foundIt = true
}
}
//Add in the property to the XML if we didn't find it
if(!foundIt){
new Node(taskNode,"customproperty",["taskproperty-id":propId,"value":value])
}
}

//Update the tasks with bugzilla info (status)
for (aTask in root.tasks.task) {
//loop through the customproperty elements where we store bug numbers and bug status
for (aCustomproperty in aTask.customproperty) {
if(aCustomproperty['@taskproperty-id'].equals(bugProp)){
def bugNum = aCustomproperty['@value']
//This is where you would add additional updates from Bugzilla
def bugStatus=""
sql.eachRow("select bug_status from bugs where bug_id = ${bugNum}") { row ->
bugStatus = row[0]
}
setOrAddCustomProperty(aTask, bugStatusProp,bugStatus)
break
}
}
}

//output the xml to the file specified in the user set variables
def formatter = new XmlNodePrinter(new PrintWriter(new File(target)))
formatter.setPreserveWhitespace true
formatter.print(root)