How To Run Reports Using the Jira VTI Demo

Below are instructions on how to run Jira reports using the XML VTI available in the demo directory. This is sometimes useful if you want to run more sophisticated queries against Jira than the web interface provides. The instructions assume you have a derby development environment set up.

1. Build Derby and set your CLASSPATH.

   • ant all
     Point your CLASSPATH to the classes directory. (The demo programs are not part of the jars.)

2. Export Jira issues to XML. There are three ways to do this.
   1. Export a subset of issues from the web browser.

      • If you want to export a subset of the Jira issues to query, you can export from within the Jira web interface. Select [Find Issues] and do your query. Right click on [XML] and select [save link as] and then save to file. This method will sometimes set a tempMax of 1000 so will not export more than 1000 issues.

      b.Export all the issues using JiraConnector

      • Use the program org.apache.derbyBuild.JiraConnector. Do not do this too often as it is hard on Apache infrastructure. To export all the issues run:
        java aorg.apache.derbyBuild.JiraConnector all
        The program will create a file called all_JIRA_ISSUES.xml.

      c. Export a subset of the issues using JiraConnector

      • Modify the JiraConnector program or add more options.

3. Setup VTI to run.
   Run the sql script below to setup the VTI. This will create a database 'vtitest' which has the VTIs declared.

connect 'jdbc:derby:vtitest;create=true';

----------------------------------------------------------------------------------------
--
-- Drop and recreate the database procedures and tables needed
-- by this demonstration script.
--
----------------------------------------------------------------------------------------

--
-- Drop procedures and tables
--
drop procedure registerXMLRowVTIs;



--
-- Recreate procedures
--
create procedure registerXMLRowVTIs( className varchar( 32672 ) )
language java
parameter style java
modifies sql data
external name 'org.apache.derbyDemo.vtis.core.XmlVTI.registerXMLRowVTIs'
;

----------------------------------------------------------------------------------------
--
-- Declare the table functions.
--
----------------------------------------------------------------------------------------

--
-- Register the table functions in the VTIs class
--
call registerXMLRowVTIs( 'org.apache.derbyDemo.vtis.example.VTIs' );

--

1. Run reports.
   Use the apacheVanillaReport VTI to query the issues. The columns that can be selected are:

key, type, priority, status, component, title, reporter, assignee, resolution, created, updated, votes, version, fixVersion Below are a couple sample reports that have been run in the past. Replace the file name with the location of your exported XML.

connect 'jdbc:derby:vtitest';

-- report on Derby JIRAs fixed in the 10.5 or 10.4 but not in 10.3

select s."key", s."title", s."assignee" 
from table( "apacheVanillaJiraReport"( 'file:///kmarsden/projects/jirareports/all_JIRA_ISSUES.xml' ) ) s  where  "type" = 'Bug' AND "version" LIKE '%10.3%' AND ("fixVersion" LIKE '%10.4%' OR "fixVersion" LIKE '%10.5%')  AND "component" != 'Documentation'  AND NOT "fixVersion" LIKE '%10.3%'  AND "resolution" = 'Fixed';


-- end of 2008  report to see what assigned issues haven't been worked on this year.

select s."key", s."assignee", s."updated" , s."title"
from table( "apacheVanillaJiraReport"( 'file:///kmarsden/projects/jirareports/all_JIRA_ISSUES.xml' ) ) s  where "resolution" = 'Unresolved' and  "assignee" != 'Unassigned' and NOT "updated" like '%2008%' order by "assignee";