You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by rh...@apache.org on 2014/02/14 21:29:39 UTC
svn commit: r1568509 - in /db/derby/code/trunk/java/demo/vtis: ./
java/org/apache/derbyDemo/vtis/core/ java/org/apache/derbyDemo/vtis/example/
java/org/apache/derbyDemo/vtis/snapshot/ sql/
Author: rhillegas
Date: Fri Feb 14 20:29:39 2014
New Revision: 1568509
URL: http://svn.apache.org/r1568509
Log:
DERBY-6256: Modernize the vti demos and eliminate many obsolete classes; commit derby-6256-06-aa-revampVTIdemos.diff.
Removed:
db/derby/code/trunk/java/demo/vtis/java/org/apache/derbyDemo/vtis/core/QueryRow.java
db/derby/code/trunk/java/demo/vtis/java/org/apache/derbyDemo/vtis/core/QueryVTIHelper.java
db/derby/code/trunk/java/demo/vtis/java/org/apache/derbyDemo/vtis/core/VTIHelper.java
db/derby/code/trunk/java/demo/vtis/java/org/apache/derbyDemo/vtis/core/VTITemplate.java
db/derby/code/trunk/java/demo/vtis/java/org/apache/derbyDemo/vtis/core/XMLRow.java
db/derby/code/trunk/java/demo/vtis/java/org/apache/derbyDemo/vtis/core/XmlVTI.java
db/derby/code/trunk/java/demo/vtis/java/org/apache/derbyDemo/vtis/example/VTIs.java
db/derby/code/trunk/java/demo/vtis/java/org/apache/derbyDemo/vtis/example/WorldDBSnapshot.java
db/derby/code/trunk/java/demo/vtis/java/org/apache/derbyDemo/vtis/snapshot/
db/derby/code/trunk/java/demo/vtis/sql/demoForeignDbmsVtis.sql
Modified:
db/derby/code/trunk/java/demo/vtis/README
db/derby/code/trunk/java/demo/vtis/java/org/apache/derbyDemo/vtis/example/ApacheServerLogVTI.java
db/derby/code/trunk/java/demo/vtis/java/org/apache/derbyDemo/vtis/example/DerbyJiraReportVTI.java
db/derby/code/trunk/java/demo/vtis/sql/demoFileVtis.sql
Modified: db/derby/code/trunk/java/demo/vtis/README
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/demo/vtis/README?rev=1568509&r1=1568508&r2=1568509&view=diff
==============================================================================
--- db/derby/code/trunk/java/demo/vtis/README (original)
+++ db/derby/code/trunk/java/demo/vtis/README Fri Feb 14 20:29:39 2014
@@ -21,42 +21,20 @@ This code is now built as part of the st
To build the javadoc, invoke the demodocs target in the top level
build script.
-Two sql scripts are provided to test-drive these table functions.
-Use Derby's ij tool to run these scripts:
+An sql script (sql/demoFileVtis.sql) is provided to test-drive these table functions.
+Use Derby's ij tool to run this script. The script needs to be customized as explained in its
+header.
-1) sql/demoFileVtis.sql
-2) sql/demoForeignDbmsVtis.sql
-
-1) The first script needs to be customized as explained in the
-script's header.
-
-This first script exercises the following table functions:
+This script exercises the following table functions:
a) PropertyFileVTI - a table function for presenting a property file
as a table.
-b) XmlVTI - a table function for presenting an arbitrary XML file as
-a table.
-
-c) ApacheServerLogVTI - an example of an XmlVTI which makes a table
+b) ApacheServerLogVTI - an example of an org.apache.derby.vti.XmlVTI which makes a table
out of an Apache web server log.
-d) DerbyJiraReportVTI - an example of an XmlVTI which makes a table
+c) DerbyJiraReportVTI - an example of an org.apache.derby.vti.XmlVTI which makes a table
out of a JIRA report.
-e) SubversionLogVTI - a table function for making a table from the
+d) SubversionLogVTI - a table function for making a table from the
output of the "svn log" command.
-
-
-2) The second script runs table functions against the sample MySQL
-database called "world". To run this script, you must be running a
-MySQL server which manages the world database. You will also need
-to customize this script as its header explains. The second script
-exercises the following machinery:
-
-a) A table function which queries the CountryLanguage table in the
-MySQL database.
-
-b) A parameterized subscription from several tables in the MySQL
-database. This subscription is defined by an annotated class called
-WorldDBSnapshot.
Modified: db/derby/code/trunk/java/demo/vtis/java/org/apache/derbyDemo/vtis/example/ApacheServerLogVTI.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/demo/vtis/java/org/apache/derbyDemo/vtis/example/ApacheServerLogVTI.java?rev=1568509&r1=1568508&r2=1568509&view=diff
==============================================================================
--- db/derby/code/trunk/java/demo/vtis/java/org/apache/derbyDemo/vtis/example/ApacheServerLogVTI.java (original)
+++ db/derby/code/trunk/java/demo/vtis/java/org/apache/derbyDemo/vtis/example/ApacheServerLogVTI.java Fri Feb 14 20:29:39 2014
@@ -21,9 +21,12 @@ limitations under the License.
package org.apache.derbyDemo.vtis.example;
+import java.io.InputStream;
+import java.net.URL;
import java.sql.*;
import java.text.SimpleDateFormat;
+import org.apache.derby.vti.XmlVTI;
import org.apache.derbyDemo.vtis.core.*;
/**
@@ -61,11 +64,32 @@ public class ApacheServerLogVTI exte
* Construct from the same arguments as our superclass.
* </p>
*/
- public ApacheServerLogVTI( String xmlResourceName, String rowTag, String[] childTags )
+ public ApacheServerLogVTI( InputStream is )
+ throws Exception
+ {
+ super
+ (
+ is,
+ "Visitor",
+ 0,
+ "IP", "accessDate", "request", "statusCode", "fileSize", "referrer", "userAgent"
+ );
+ }
+
+ ///////////////////////////////////////////////////////////////////////////////////
+ //
+ // FUNCTION ENTRY POINT (BOUND BY THE CREATE FUNCTION STATEMENT)
+ //
+ ///////////////////////////////////////////////////////////////////////////////////
+
+ /** Create from an URL string identifying the server log file */
+ public static ApacheServerLogVTI apacheNaturalLogFile( String xmlResourceName )
+ throws Exception
{
- super( xmlResourceName, rowTag, childTags );
+ return new ApacheServerLogVTI( (new URL( xmlResourceName )).openStream() );
}
+
///////////////////////////////////////////////////////////////////////////////////
//
// OVERRIDES
Modified: db/derby/code/trunk/java/demo/vtis/java/org/apache/derbyDemo/vtis/example/DerbyJiraReportVTI.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/demo/vtis/java/org/apache/derbyDemo/vtis/example/DerbyJiraReportVTI.java?rev=1568509&r1=1568508&r2=1568509&view=diff
==============================================================================
--- db/derby/code/trunk/java/demo/vtis/java/org/apache/derbyDemo/vtis/example/DerbyJiraReportVTI.java (original)
+++ db/derby/code/trunk/java/demo/vtis/java/org/apache/derbyDemo/vtis/example/DerbyJiraReportVTI.java Fri Feb 14 20:29:39 2014
@@ -21,9 +21,12 @@ limitations under the License.
package org.apache.derbyDemo.vtis.example;
+import java.io.InputStream;
+import java.net.URL;
import java.sql.*;
import java.text.SimpleDateFormat;
+import org.apache.derby.vti.XmlVTI;
import org.apache.derbyDemo.vtis.core.*;
/**
@@ -60,9 +63,28 @@ public class DerbyJiraReportVTI exte
* Construct from the same arguments as our superclass.
* </p>
*/
- public DerbyJiraReportVTI( String xmlResourceName, String rowTag, String[] childTags )
+ public DerbyJiraReportVTI( InputStream is )
{
- super( xmlResourceName, rowTag, childTags );
+ super
+ (
+ is,
+ "item",
+ 0,
+ "key", "type", "priority", "status", "component", "customfieldvalue", "title"
+ );
+ }
+
+ ///////////////////////////////////////////////////////////////////////////////////
+ //
+ // FUNCTION ENTRY POINT (BOUND BY THE CREATE FUNCTION STATEMENT)
+ //
+ ///////////////////////////////////////////////////////////////////////////////////
+
+ /** Create from an URL string identifying the server log file */
+ public static DerbyJiraReportVTI apacheNaturalJiraReport( String xmlResourceName )
+ throws Exception
+ {
+ return new DerbyJiraReportVTI( (new URL( xmlResourceName )).openStream() );
}
///////////////////////////////////////////////////////////////////////////////////
Modified: db/derby/code/trunk/java/demo/vtis/sql/demoFileVtis.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/demo/vtis/sql/demoFileVtis.sql?rev=1568509&r1=1568508&r2=1568509&view=diff
==============================================================================
--- db/derby/code/trunk/java/demo/vtis/sql/demoFileVtis.sql (original)
+++ db/derby/code/trunk/java/demo/vtis/sql/demoFileVtis.sql Fri Feb 14 20:29:39 2014
@@ -34,31 +34,44 @@ connect 'jdbc:derby:memory:vtitest;creat
----------------------------------------------------------------------------------------
--
--- Create the database procedures and tables needed
--- by this demonstration script.
+-- Declare the table functions.
--
----------------------------------------------------------------------------------------
-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.
+-- Declare a table function which reads an Apache server log.
--
-----------------------------------------------------------------------------------------
+create function apacheNaturalLogFile( fileURL varchar( 32672 ) ) returns table
+(
+ IP varchar(100),
+ accessDate timestamp,
+ request clob,
+ statusCode int,
+ fileSize int,
+ referrer varchar(200),
+ userAgent clob
+)
+language java parameter style derby_jdbc_result_set no sql
+external name 'org.apache.derbyDemo.vtis.example.ApacheServerLogVTI.apacheNaturalLogFile';
--
--- Register the table functions in the VTIs class
+-- Declare a table function which reads a Derby JIRA report
--
-call registerXMLRowVTIs( 'org.apache.derbyDemo.vtis.example.VTIs' );
+create function apacheNaturalJiraReport( fileURL varchar( 32672 ) ) returns table
+(
+ keyCol int,
+ type varchar(20),
+ priority varchar(10),
+ status varchar(20),
+ component varchar(50),
+ customfieldvalue varchar(200),
+ title varchar(500)
+)
+language java parameter style derby_jdbc_result_set no sql
+external name 'org.apache.derbyDemo.vtis.example.DerbyJiraReportVTI.apacheNaturalJiraReport';
--
--- Register a table function which reads the output of an 'svn log' command
+-- Declare a table function which reads the output of an 'svn log' command
--
create function svnLogReader( logFileName varchar( 32672 ) )
returns TABLE
@@ -81,8 +94,8 @@ external name 'org.apache.derbyDemo.vtis
create function propertyFileVTI( fileName varchar( 32672 ) )
returns TABLE
(
- messageID varchar( 10 ),
- messageText varchar( 1000 )
+ messageID varchar( 20 ),
+ messageText varchar( 32672 )
)
language java
parameter style DERBY_JDBC_RESULT_SET
@@ -110,7 +123,7 @@ group by committer
----------------------------------------------------------------------------------------
-- find the messages which have not been translated into french
-select *
+select messageID, substr( m_english.messageText, 1, 100 )
from table( propertyFileVTI( '/opt/DerbyTrunk/generated/java/org/apache/derby/loc/messages_en.properties' ) ) m_english
where m_english.messageID not in
(
@@ -129,54 +142,43 @@ where m_english.messageID not in
-- 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
+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
+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%'
+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' )
+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"
+from table( apacheNaturalJiraReport( 'file:///opt/DerbyTrunk/java/demo/vtis/data/DerbyJiraReport.xml' ) ) s
+where s.keyCol between 2800 and 2950
+order by keyCol
;
-- 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"
+from table( apacheNaturalJiraReport( 'file:///opt/DerbyTrunk/java/demo/vtis/data/DerbyJiraReport.xml' ) ) s
+where type != 'Sub-task'
+order by keyCol
;