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

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

Accepted Answer
0 votes

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)

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

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

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)

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

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?

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

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

Suggest an answer

Log in or Sign up to answer
Community showcase
Posted Tuesday in Featured Groups

Tuesday tips & tricks: What is the Atlassian Community?

It's officially Tuesday, which means it's officially time for another tip to help you better navigate this space we call the Atlassian Community. 😄 I got a great question from community member, Sa...

91 views 3 6
View post

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you