Myrna asked me how I generated this report.
1) I patched the demo to include a few extra columns.
Index: java/demo/vtis/java/org/apache/derbyDemo/vtis/example/VTIs.java
===================================================================
--- java/demo/vtis/java/org/apache/derbyDemo/vtis/example/VTIs.java
(revision 618330)
+++ java/demo/vtis/java/org/apache/derbyDemo/vtis/example/VTIs.java
(working copy)
@@ -94,8 +94,8 @@
@XMLRow
(
rowTag = "item",
- childTags = { "key", "type", "priority", "status",
"component", "title" },
- childTypes = { "varchar(12)", "varchar(10)", "varchar(10)",
"varchar(10)", "varchar(50)", "varchar(200)" },
+ childTags = { "key", "type", "assignee", "priority", "status",
"component", "version","fixVersion", "title","resolution" },
+ childTypes = { "varchar(12)", "varchar(10)", "varchar(50)",
"varchar(10)", "varchar(10)", "varchar(50)", "varchar(150)",
"varchar(150)", "varchar(200)","varchar(20)" },
vtiClassName = "org.apache.derbyDemo.vtis.core.XmlVTI"
)
public static ResultSet apacheVanillaJiraReport( String
xmlResource ) throws SQLException
I had to build the demo with javac -g -d $WS/classes. It did not seem to
build automatically as part of the build. I don't know if that is a problem.
2) I ran the part of the demoFileVtis.sql file related to the JiraVTI
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;
--
-- Drop miscellaneous table functions
--
drop function svnLogReader;
drop function propertyFileVTI;
--
-- 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' );
--
3) I ran the report.
select s."key", s."title", s."assignee"
from table( "apacheVanillaJiraReport"(
'file:///kmarsden/projects/tablefunction/all_JIRA_ISSUES.xml' ) ) s
where "type" = 'Bug' AND "version" LIKE '%10.3%' AND "fixVersion" LIKE
'%10.4%' AND NOT "fixVersion" LIKE '%10.3%' AND "resolution" = 'Fixed';
The version and the fixversion values are kind of weird with all the
versions smashed together, but the worked ok for my purposes. I don't
know whether it would be appropriate to add them to the demo though in
that format.
Kathey
