

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

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' );

--
-- Register a table function which reads the output of an 'svn log' command
--
create function svnLogReader( logFileName varchar( 32672 ) )
returns TABLE
  (
     XID varchar( 15 ),
     committer    varchar( 20 ),
     commit_time  timestamp,
     line_count   varchar( 10 ),
     description  varchar( 32672 )
  )
language java
parameter style DERBY_JDBC_RESULT_SET
no sql
external name 'org.apache.derbyDemo.vtis.example.SubversionLogVTI.subversionLogVTI'
;

--
-- Register a table function to read a Derby message file
--
create function propertyFileVTI( fileName varchar( 32672 ) )
returns TABLE
  (
     messageID  varchar( 10 ),
     messageText varchar( 1000 )
  )
language java
parameter style DERBY_JDBC_RESULT_SET
no sql
external name 'org.apache.derbyDemo.vtis.example.PropertyFileVTI.propertyFileVTI'
;

----------------------------------------------------------------------------------------
--
-- Read a log file dumped as a flat file
--
----------------------------------------------------------------------------------------

-- how active were the committers in 2006?
select committer, count(*) as commits
from table( svnLogReader( '/opt/DerbyTrunk/java/demo/vtis/data/svn_log.txt' ) ) s
where commit_time between timestamp( '2006-01-01 00:00:00' ) and timestamp( '2007-01-01 00:00:00' )
group by committer
;

----------------------------------------------------------------------------------------
--
-- Read a property file of Derby messages
--
----------------------------------------------------------------------------------------

-- find the messages which have not been translated into french
select *
from table( propertyFileVTI( '/opt/DerbyTrunk/generated/java/org/apache/derby/loc/messages_en.properties' ) ) m_english
where m_english.messageID not in
(
    select m_french.messageID
    from table( propertyFileVTI( '/opt/DerbyTrunk/java/engine/org/apache/derby/loc/messages_fr.properties' ) ) m_french
);


----------------------------------------------------------------------------------------
--
-- XML VTIs
--
----------------------------------------------------------------------------------------

--
-- Read from the XML log file produced by an Apache web server
--

-- this vti treats the oddly formatted accessDate and fileSize fields as varchars
select s.*
from table( "apacheVanillaLogFile"( 'file:///opt/DerbyTrunk/java/demo/vtis/data/ApacheServerLog.xml' ) ) s
;

-- this vti treats accessDate as a timestamp and fileSize as an int
select s.*
from table( "apacheNaturalLogFile"( 'file:///opt/DerbyTrunk/java/demo/vtis/data/ApacheServerLog.xml' ) ) s
;

-- look for relevant status codes
select s.*
from table( "apacheNaturalLogFile"( 'file:///opt/DerbyTrunk/java/demo/vtis/data/ApacheServerLog.xml' ) ) s
where s."statusCode" = 206
;

-- look for relevant IP addresses
select s.*
from table( "apacheNaturalLogFile"( 'file:///opt/DerbyTrunk/java/demo/vtis/data/ApacheServerLog.xml' ) ) s
where "IP" like '208%'
;

-- look for log records in a time range
select s.*
from table( "apacheNaturalLogFile"( 'file:///opt/DerbyTrunk/java/demo/vtis/data/ApacheServerLog.xml' ) ) s
where "accessDate" between timestamp( '2002-07-01 08:40:56.0' ) and timestamp( '2002-07-01 08:42:56.0' )
;

--
-- Read from the XML log file produced by a JIRA report
--

-- report on Derby JIRAs
select s.*
from table( "apacheVanillaJiraReport"( 'file:///opt/DerbyTrunk/java/demo/vtis/data/DerbyJiraReport.xml' ) ) s
where s."key" between 'DERBY-2800' and 'DERBY-2950'
;

-- treat keys as ints and sort Derby JIRAs by key
select s.*
from table( "apacheNaturalJiraReport"( 'file:///opt/DerbyTrunk/java/demo/vtis/data/DerbyJiraReport.xml' ) ) s
where s."key" between 2800 and 2950
order by "key"
;

-- eliminate uninteresting Derby JIRAs
select s.*
from table( "apacheNaturalJiraReport"( 'file:///opt/DerbyTrunk/java/demo/vtis/data/DerbyJiraReport.xml' ) ) s
where "type" != 'Sub-task'
order by "key"
;
