Where does Greenhopper store version start and end dates in the underlying database?

James Snape November 1, 2011

I'm using Reporting Services to write a number of project reports e.g. current status, sprint planning etc. These directly query the underlying database tables (well I use ETL to extract data then query it but the effect is the same).

I can see that Project Versions have a release date but the start and end dates available in Greenhopper are not there. I've also had a look at the propertyentry table but can't see anything obvious.

So where is Greenhopper storing it's extra data? If it is in the propertyenty table then what are the ENTITY_NAME and PROPERTY_KEY values I should search for?

5 answers

1 accepted

0 votes
Answer accepted
David Yu
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
November 10, 2011

Update: Woops, sorry, this query won't do--it misses a few values in between...

I hope this gets easier in the future, but the data is buried inside a XML string.

Here's a query that works for postgres to extract the version start dates:

select regexp_replace ( propertyvalue,E'.*START_DATE_\(.*?\)</string>.*<long>\(.*?\)</long>.*', E'\\1') as versionid, to_timestamp( substr( regexp_replace ( propertyvalue,E'.*START_DATE_\(.*?\)</string>.*<long>\(.*?\)</long>.*', E'\\2'), 0, 11)::INT) as start_date from propertytext where propertyvalue ~ 'START_DATE';

versionid |       start_date       
-----------+------------------------
 10550     | 2011-01-30 21:00:00-08
 10619     | 2011-10-10 00:00:00-07
 10531     | 2011-01-30 21:00:00-08
 10612     | 2011-03-27 21:00:00-07
 10370     | 2010-05-31 21:00:00-07
 10907     | 2011-09-26 00:00:00-07
 10875     | 2011-08-05 00:00:00-07
 10193     | 2010-02-21 21:00:00-08
 10781     | 2011-09-04 00:00:00-07
 10640     | 2011-04-03 21:00:00-07
 10697     | 2011-05-23 00:00:00-07
 10797     | 2011-07-14 00:00:00-07
 10613     | 2011-01-02 21:00:00-08
(13 rows)

David Yu
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
November 10, 2011

Not happy with this query but maybe someone can do better--I know it's not MSSQL specific but maybe it'll give you some ideas. To get end date, just modify the word START below. (Hope the formatting isn't too mangled here)

select vname,start_date,releasedate from projectversion, 
(select substr( regexp_replace ( data ,E'(.*?)</string>.*<long>(.*?)</(.*)',
E'\\1 '), 0,6) as versionid,
to_timestamp( substr( regexp_replace ( data ,E'(.*?)</string>.*<long>(.*?)</(.*)', E'\\2 '), 0,11)::INT) as start_date
from (select regexp_split_to_table(propertyvalue, E'START_DATE_','n') as data
from propertytext where propertyvalue ~ 'START_DATE') as t1
where data ~ '^[0-9]')
as greenhopper where greenhopper.versionid::INT = projectversion.id
order by vname



vname | start_date | releasedate ------------------------+------------------------+------------------------ 1.0 | 2011-04-10 21:00:00-07 | 2.0 | 2011-04-03 21:00:00-07 | 2011-04-28 21:00:00-07 3.0 | 2011-03-27 21:00:00-07 | 2011-06-05 21:00:00-07

James Snape November 10, 2011

For SQL Server this may be a better query:

select
			case 
				when RawProjectVersion like 'BURNDOWN_START_DATE%' then 'startdate'
				when RawProjectVersion like 'BURNDOWN_END_DATE%' then 'enddate'
				else null
			end as DateType,
			dateadd(s, RawTimestamp, cast('1970-01-01' as datetime2)) as TimeStamp,
			substring(RawProjectVersion, VersionOffset, len(RawProjectVersion)) as ProjectVersion
		from (
			select 
				x.ENTITY_ID,
				CONFIG_XML,
				y.n.value(N'./string[1]', 'nvarchar(50)') as RawProjectVersion,
				patindex(N'%DATE[_]%', y.n.value(N'./string[1]', 'nvarchar(50)')) + len(N'DATE_') as VersionOffset,
				cast(y.n.value(N'./long[1]', 'bigint')/1000 as int) as RawTimestamp
			from ( 
				select e.ENTITY_ID, cast(propertyvalue as xml) as CONFIG_XML
				from jiraschema.propertytext t
				join jiraschema.propertyentry e on e.ID = t.ID
				where e.ENTITY_NAME = 'GreenHopper'
				and e.PROPERTY_KEY = 'CONFIGURATION'
				and (propertyvalue like N'%START_DATE%' or propertyvalue like N'%END_DATE%')
			) x
			cross apply CONFIG_XML.nodes(N'/map/entry') as y(n)
			where y.n.value(N'./string[1]', N'nvarchar(50)') like N'BURNDOWN%'
		) z

1 vote
Greg Friedman April 10, 2012

Since I landed here after searching quite a bit for a solution to this challenge and couldn't get one of the proposed solutions to work with my Oracle instance I thought I would post my working code that took me a while to figure out. I hope this is useful to someone as it has been for me:

The following query returns all versions from the Jira DB and the associated start, end, and release date. Start and End are from the XML data in the propertytext table. The solution was from various sources as well as quite a bit of trial and error myself. Note: This solution is for Oracle, but can likely be modified for other DBs. This could be done via one SQL statement rather than two, but it seemed cleaner to me this way for some reason.

SELECT projectname,
       prjid,
       versionid,
       versionname,
       versiondescription,
       (to_date('01-JAN-70')+ (dbms_lob.substr(alldata,10,(dbms_lob.instr(alldata,concat('BURNDOWN_START_DATE_', versionid),1) + 25)))/86400) bdstartdate,
       CASE WHEN (dbms_lob.substr(alldata,33,(dbms_lob.instr(alldata,concat('BURNDOWN_END_DATE_', 
versionid),1)))) IS NULL 
	    THEN NULL
            ELSE (to_date('01-JAN-70')+ (dbms_lob.substr(alldata,10,(dbms_lob.instr(alldata,concat('BURNDOWN_END_DATE_', versionid),1) + 23)))/86400)
            END bdenddate,
      versionreleasedate,
      versionreleased
FROM
(SELECT p.pname projectname, 
        pv.project prjid, 
        pv.id versionid, 
        pv.vname versionname, 
        pv.description versiondescription, 
        Extract(xmltype(pt.propertyvalue), '/map/entry/*/text()').getClobVal() alldata,
        pv.releasedate versionreleasedate,
        pv.released versionreleased
FROM propertytext pt 
  left join propertyentry pe on pt.id = pe.id 
  left join project p on pe.entity_id = p.id
  left join projectversion pv on p.id = pv.project 
  WHERE pe.entity_name= 'GreenHopper' AND pe.property_key ='CONFIGURATION'
  AND dbms_lob.instr(Extract(xmltype(pt.propertyvalue), '/map/entry/*/text()').getClobVal(),concat
('BURNDOWN_START_DATE_', pv.id),1) &lt;&gt; 0)

0 votes
rag van March 6, 2013

Thank you all. It helped us a lot. here is the changed query for MYSQL

SELECT projectname,
       prjid,
       versionid,
       versionname,
       versiondescription,
       DATE_FORMAT( '1970-01-01' + INTERVAL (substr(alldata,(instr(alldata,concat('BURNDOWN_START_DATE_', versionid)) + 25),11)/86400) DAY,'%Y-%m-%d ') bdstartdate,
       CASE WHEN (substr(alldata,(instr(alldata,concat('BURNDOWN_END_DATE_', 
versionid))),33)) =''
        THEN NULL
            ELSE DATE_FORMAT('1970-01-01' + INTERVAL (substr(alldata,(instr(alldata,concat('BURNDOWN_END_DATE_', versionid)) + 23),11)/86400)DAY,'%Y-%m-%d ')
            END bdenddate,

      versionreleasedate,
      versionreleased
FROM
(SELECT p.pname projectname, 
        pv.project prjid, 
        pv.id versionid, 
        pv.vname versionname, 
        pv.description versiondescription, 
        ExtractValue((pt.propertyvalue), '/map/entry/*/text()') alldata,
        pv.releasedate versionreleasedate,
        pv.released versionreleased
FROM propertytext pt 
  left join propertyentry pe on pt.id = pe.id 
  left join project p on pe.entity_id = p.id
  left join projectversion pv on p.id = pv.project 
  WHERE pe.entity_name= 'GreenHopper' AND pv.project='10499' and pe.property_key ='CONFIGURATION'
  AND instr(ExtractValue((pt.propertyvalue), '/map/entry/*/text()'),concat
('BURNDOWN_START_DATE_', pv.id)) &lt;&gt; 0) a

0 votes
Nicholas Muldoon
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
November 3, 2011

Sorry James, I am not sure about the SQL for this.

0 votes
Nicholas Muldoon
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
November 2, 2011

Hi James,

GreenHopper stores the start and end date for a particular fix version in propertysets. Check out this post for more information:

https://answers.atlassian.com/questions/7912/getting-sprint-s-start-date-and-end-date-programatically

Thanks,
Nicholas

James Snape November 2, 2011

I saw that article but it doesn't give any detail to where in the database the start/end dates are stored. Also, you mention the propertysets but I can't find anything relevant.

Do you have a sample SQL query I can try to confirm your answer?

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events