db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Apache Wiki <wikidi...@apache.org>
Subject [Db-derby Wiki] Update of "HowToRunJiraVtiReports" by KatheyMarsden
Date Wed, 14 Jan 2009 19:32:48 GMT
Dear Wiki user,

You have subscribed to a wiki page or wiki category on "Db-derby Wiki" for change notification.

The following page has been changed by KatheyMarsden:

New page:
= 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}}}[[BR]]
    Point your CLASSPATH to the classes directory. (The demo programs are not part of the

 2. Export Jira issues to XML.  There are three ways to do this.
  a.  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:[[BR]] {{{java aorg.apache.derbyBuild.JiraConnector
all}}}[[BR]] 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.  [[BR]]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' );



 4. Run reports. [[BR]] 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";


View raw message