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

[James Snape]

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?

[David Yu · Accepted solution]

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]

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]

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

[Greg Friedman]

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) <> 0)

[rag van]

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)) <> 0) a

[Nicholas Muldoon]

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

[Nicholas Muldoon]

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]

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?