One of my current projects is getting data out of Jira into a DataMart to allow fast (and easy) analysis. A library such as
TechTalk.JiraRestClient provides a basic foundation but there is a nasty gotcha. Jira can be heavily customized, often with different projects having dozen of different and unique custom fields. So how can you do one size fits all?
You could go down path of modifying the above code to enumerate all of the custom fields (and then have continuous work keeping them in sync) or try something like what I do below: exploiting that JSON and XML are interchangeable and XML in a SQL Server database can actually be real sweet to use.
Modifying JiraRestClient
The first step requires downloading the code from GitHub and modifying it.
In JiraClient.cs method
EnumerateIssuesByQueryInternal add the following code.
var issues = data.issues ?? Enumerable.Empty<Issue>();
var xml=JsonConvert.DeserializeXmlNode( response.Content,"json");
// Insert all of the XML-JSON into
foreach (var issue in issues)
{
var testNode=xml.SelectSingleNode(string.Format("//issues/key[text()='{0}']/..", issue.key));
if(testNode !=null)
{
issue.xml = testNode.OuterXml;
};
}
You will also need to modify the issue class to include a string, "xml". The result is an issue class containing all of the information from the REST response.
Moving Issues into a Data Table
Once you have the issue by issue REST JSON response converted to XML, we need to move it to our storage. My destination is SQL server and I will exploit SQL Table variables to make the process simple and use set operations. In short, I move the enumeration of issues into a C# data table so I may pass the data to SQL Server.
var upload = new DataTable();
// defining columns omitted
var data = client.GetIssues(branch);
foreach (var issue in data)
try
{
var newRow = upload.NewRow();
newRow[Key] = issue.key;
newRow[Self] = issue.self;
// Other columns extracted
newRow[Xml] = issue.xml;
upload.Rows.Add(newRow);
}
catch (Exception exc)
{
Console.WriteLine(exc);
}
The upload code is also clean and simple:
using (var cmd = new SqlCommand { CommandType = CommandType.StoredProcedure, CommandText ="Jira.Upload1"})
using (cmd.Connection = MyDataConnection)
{
cmd.Parameters.AddWithValue("@Data", upload);
cmd.ExecuteNonQuery();
}
SQL Code
For many C# developers, SQL is an unknown country, so I will go into some detail. First, we need to define the table in SQL, just match the DataTable in C# above (same column names in same sequence is best)
CREATE TYPE [Jira].[JiraUpload1Type] AS TABLE(
[Key] [varchar](max) NULL,
[Assignee] [varchar](max) NULL,
[Description] [varchar](max) NULL,
[Reporter] [varchar](max) NULL,
[Status] [varchar](max) NULL,
[Summary] [varchar](max) NULL,
[OriginalEstimate] [varchar](max) NULL,
[Labels] [varchar](max) NULL,
[Self] [varchar](max) NULL,
[XmlData] [Xml] Null
)
Note: that I use (max) always -- which is pretty much how the C# datatable sees each column. Any data conversion to decimals will be done by SQL itself.
Second, we create the stored procedure. We want to update existing records and insert missing records. The code is simple and clean
CREATE PROC [Jira].[Upload1] @Data [Jira].[JiraUpload1Type] READONLY
AS
Update Jira.Issue SET
[Assignee] = D.Assignee
,[Description] = D.Description
,[Reporter] = D.Reporter
,[Status] = D.Status
,[Summary] = D.Summary
,[OriginalEstimate] = D.OriginalEstimate
,[Labels] = D.Labels
,[XmlData] = D.XmlData
From @Data D
JOIN Jira.Issue S ON D.[Key]=S.[Key]
INSERT INTO [Jira].[Issue]
([Key]
,[Assignee]
,[Description]
,[Reporter]
,[Status]
,[Summary]
,[OriginalEstimate]
,[Labels]
,[XmlData])
SELECT D.[Key]
,D.[Assignee]
,D.[Description]
,D.[Reporter]
,D.[Status]
,D.[Summary]
,D.[OriginalEstimate]
,D.[Labels]
,D.[XmlData]
From @Data D
LEFT JOIN Jira.Issue S ON D.[Key]=S.[Key]
WHERE S.[Key] Is Null
All of the Json is now in XML and can be search by Xpath
Upon executing the above, we see our table is populated as shown below. The far right column is XML.This is the SQL Xml data type and contains the REST JSON converted to XML for each issue.
The next step is often to add computed columns using the SQL XML and a xpath. An example of a generic solution is below.
So what is the advantage?
No matter how many additional fields are added to Jira, you have 100% data capture here. There is no need to touch the Extract Transform Load (ETL) job. You can create (and index) the data in the XML in SQL server, or just hand back the XML to whatever is calling it. While SQL Server 2016 supports JSON, XML is superior because of the ability to do XPaths into it as well as indices.
In many implementations of JIRA, the number of fields can get unreal.. as shown below
With the same data table, you could create multiple views that contain computed columns showing precisely the data that you are interested in.
Example of Computed column definitions
[ProductionReleaseDate] AS ([dbo].[GetCustomField]('customfield_10705',[XmlData])),
[EpicName] AS ([dbo].[GetCustomField]('customfield_10009',[XmlData])),
[Sprint] AS ([dbo].[GetCustomField]('customfield_10007',[XmlData])),
With this Sql Function doing all of the work:
CREATE FUNCTION [dbo].[GetCustomField]
(
@Name varchar(32),
@Data Xml
)
RETURNS varchar(max)
AS
BEGIN
DECLARE @ResultVar varchar(max)
SELECT @ResultVar = c.value('customfieldvalues[1]','varchar(max)') FROM @Data.nodes('//customfield[@id]') as t(c)
WHERE c.value('@id','varchar(50)')=@Name
RETURN @ResultVar
END
The net result is clean flexible code feeding into a database with very quick ability to extend.
You want to expose a new field? it's literally a one liner to add it as a column to the SQL Server table or view. Consider creating custom views on top of the table as a clean organized solution.
Posts
