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 2007/10/23 22:54:07 UTC
svn commit: r587652 [6/6] - in /db/derby/code/trunk: ./ java/demo/
java/demo/vtis/ java/demo/vtis/data/ java/demo/vtis/java/
java/demo/vtis/java/org/ java/demo/vtis/java/org/apache/
java/demo/vtis/java/org/apache/derbyDemo/ java/demo/vtis/java/org/apac...
Added: db/derby/code/trunk/java/demo/vtis/java/org/apache/derbyDemo/vtis/core/XmlVTI.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/demo/vtis/java/org/apache/derbyDemo/vtis/core/XmlVTI.java?rev=587652&view=auto
==============================================================================
--- db/derby/code/trunk/java/demo/vtis/java/org/apache/derbyDemo/vtis/core/XmlVTI.java (added)
+++ db/derby/code/trunk/java/demo/vtis/java/org/apache/derbyDemo/vtis/core/XmlVTI.java Tue Oct 23 13:54:05 2007
@@ -0,0 +1,363 @@
+/*
+
+Derby - Class org.apache.derbyDemo.vtis.core.XmlVTI
+
+Licensed to the Apache Software Foundation (ASF) under one or more
+contributor license agreements. See the NOTICE file distributed with
+this work for additional information regarding copyright ownership.
+The ASF licenses this file to You under the Apache License, Version 2.0
+(the "License"); you may not use this file except in compliance with
+the License. You may obtain a copy of the License at
+
+http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing, software
+distributed under the License is distributed on an "AS IS" BASIS,
+WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+See the License for the specific language governing permissions and
+limitations under the License.
+
+*/
+
+package org.apache.derbyDemo.vtis.core;
+
+import java.io.*;
+import java.lang.reflect.*;
+import java.net.URL;
+import java.sql.*;
+import java.text.DateFormat;
+import java.text.ParseException;
+import javax.xml.parsers.*;
+import org.w3c.dom.*;
+
+/**
+ * <p>
+ * This is a VTI designed to read XML files which are structured like row sets.
+ * </p>
+ *
+ */
+public class XmlVTI extends StringColumnVTI
+{
+ ///////////////////////////////////////////////////////////////////////////////////
+ //
+ // CONSTANTS
+ //
+ ///////////////////////////////////////////////////////////////////////////////////
+
+ ///////////////////////////////////////////////////////////////////////////////////
+ //
+ // STATE
+ //
+ ///////////////////////////////////////////////////////////////////////////////////
+
+ private String _rowTag;
+ private String _xmlResourceName;
+
+ private int _rowIdx = -1;
+ private int _rowCount = -1;
+ private String[] _currentRow;
+
+ private DocumentBuilder _builder;
+ private NodeList _rawRows;
+
+ ///////////////////////////////////////////////////////////////////////////////////
+ //
+ // CONSTRUCTORS
+ //
+ ///////////////////////////////////////////////////////////////////////////////////
+
+ /**
+ * <p>
+ * Build a XmlVTI given the name of an xml resource, the tag of the row
+ * element, and an array of attribute-names/element-tags underneath the row element
+ * </p>
+ */
+ public XmlVTI( String xmlResourceName, String rowTag, String[] childTags )
+ {
+ super( childTags );
+
+ _xmlResourceName = xmlResourceName;
+ _rowTag = rowTag;
+ }
+
+ ///////////////////////////////////////////////////////////////////////////////////
+ //
+ // DATABASE PROCEDURES
+ //
+ ///////////////////////////////////////////////////////////////////////////////////
+
+ /**
+ * <p>
+ * Register all of the XMLRow table functions in a class. This method is exposed as a
+ * database procedure.
+ * </p>
+ */
+ public static void registerXMLRowVTIs( String className )
+ throws Exception
+ {
+ // find public static methods which return ResultSet
+ Class theClass = Class.forName( className );
+ Method[] methods = theClass.getMethods();
+ int count = methods.length;
+ Method candidate = null;
+ XMLRow xmlRowAnnotation = null;
+
+ for ( int i = 0; i < count; i++ )
+ {
+ candidate = methods[ i ];
+
+ int modifiers = candidate.getModifiers();
+
+ if (
+ Modifier.isPublic( modifiers ) &&
+ Modifier.isStatic( modifiers ) &&
+ candidate.getReturnType() == ResultSet.class
+ )
+ {
+ xmlRowAnnotation = candidate.getAnnotation( XMLRow.class );
+
+ if ( xmlRowAnnotation != null )
+ {
+ VTIHelper.unregisterVTI( candidate );
+
+ registerVTI( candidate );
+ }
+ }
+ }
+ }
+
+ ///////////////////////////////////////////////////////////////////////////////////
+ //
+ // StringColumnVTI BEHAVIOR TO BE IMPLEMENTED BY SUBCLASSES
+ //
+ ///////////////////////////////////////////////////////////////////////////////////
+
+ /**
+ * <p>
+ * Get the string value of the column in the current row identified by the 1-based columnNumber.
+ * </p>
+ */
+ protected String getRawColumn( int columnNumber ) throws SQLException
+ {
+ try {
+ return _currentRow[ columnNumber - 1 ];
+ } catch (Throwable t) { throw new SQLException( t.getMessage() ); }
+ }
+
+ ///////////////////////////////////////////////////////////////////////////////////
+ //
+ // ResultSet BEHAVIOR
+ //
+ ///////////////////////////////////////////////////////////////////////////////////
+
+ public void close() throws SQLException
+ {
+ _builder = null;
+ _rawRows = null;
+ }
+
+ public ResultSetMetaData getMetaData() throws SQLException
+ {
+ throw new SQLException( "Not implemented." );
+ }
+
+ public boolean next() throws SQLException
+ {
+ try {
+ if ( _rowIdx < 0 ) { readRows(); }
+
+ if ( ++_rowIdx < _rowCount )
+ {
+ parseRow( _rowIdx );
+ return true;
+ }
+ else { return false; }
+ } catch (Throwable t)
+ {
+ t.printStackTrace( System.out );
+ throw new SQLException( t.getMessage() );
+ }
+ }
+
+ ///////////////////////////////////////////////////////////////////////////////////
+ //
+ // PUBLIC BEHAVIOR
+ //
+ ///////////////////////////////////////////////////////////////////////////////////
+
+ /**
+ * <p>
+ * Register a method as a Derby Table Function. We assume the following:
+ * </p>
+ *
+ * <ul>
+ * <li>The method is public and static.</li>
+ * <li>The method returns a ResultSet.</li>
+ * <li>The method is annotated as an XMLRow.</li>
+ * </ul>
+ *
+ */
+ public static void registerVTI( Method method )
+ throws Exception
+ {
+ XMLRow annotation = method.getAnnotation( XMLRow.class );
+
+ String methodName = method.getName();
+ String sqlName = methodName;
+ Class methodClass = method.getDeclaringClass();
+ Class[] parameterTypes = method.getParameterTypes();
+ int parameterCount = parameterTypes.length;
+ String[] columnNames = annotation.childTags();
+ String[] columnTypes = annotation.childTypes();
+ int columnCount = columnNames.length;
+ int typeCount = columnTypes.length;
+ StringBuilder buffer = new StringBuilder();
+
+ if ( columnCount != typeCount )
+ {
+ throw new Exception
+ (
+ "Bad XMLRow annotation for " +
+ methodName +
+ ". The number of childTags (" +
+ columnCount +
+ ") does not equal the number of childTypes (" +
+ typeCount +
+ ")."
+ );
+ }
+
+ VTIHelper.registerVTI( method, columnNames, columnTypes, false );
+ }
+
+ /**
+ * <p>
+ * Create a VTI ResultSet. It is assumed that our caller is an
+ * XMLRow-annotated method with one String argument. It
+ * is assumed that ResultSet is an instance of vtiClass and that
+ * vtiClass extends XmlVTI and has a constructor with the same
+ * arguments as the constructor of XmlVTI.
+ * </p>
+ *
+ */
+ public static ResultSet instantiateVTI( String xmlResourceName )
+ throws SQLException
+ {
+ try {
+ // look up the method on top of us
+ StackTraceElement[] stack = (new Throwable()).getStackTrace();
+ StackTraceElement caller = stack[ 1 ];
+ Class callerClass = Class.forName( caller.getClassName() );
+ String methodName = caller.getMethodName();
+ Method method = callerClass.getMethod
+ ( methodName, new Class[] { String.class } );
+ XMLRow annotation = method.getAnnotation( XMLRow.class );
+ String rowTag = annotation.rowTag();
+ String[] childTags = annotation.childTags();
+ String vtiClassName = annotation.vtiClassName();
+ Class vtiClass = Class.forName( vtiClassName );
+ Constructor constructor = vtiClass.getConstructor
+ ( new Class[] { String.class, String.class, String[].class } );
+
+ return (ResultSet) constructor.newInstance( xmlResourceName, rowTag, childTags );
+
+ } catch (Throwable t) { throw new SQLException( t.getMessage() ); }
+ }
+
+ ///////////////////////////////////////////////////////////////////////////////////
+ //
+ // MINIONS
+ //
+ ///////////////////////////////////////////////////////////////////////////////////
+
+ //////////////////////////
+ //
+ // XML MINIONS
+ //
+ //////////////////////////
+
+ /**
+ * <p>
+ * Fault in the list of rows.
+ * </p>
+ */
+ private void readRows() throws Exception
+ {
+ DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
+
+ _builder = factory.newDocumentBuilder();
+
+ URL url = new URL( _xmlResourceName );
+ InputStream is = url.openStream();
+ Document doc = _builder.parse( is );
+ Element root = doc.getDocumentElement();
+
+ _rawRows = root.getElementsByTagName( _rowTag );
+ _rowCount = _rawRows.getLength();
+
+ is.close();
+ }
+
+ /**
+ * <p>
+ * Parse a row into columns.
+ * </p>
+ */
+ private void parseRow( int rowNumber ) throws Exception
+ {
+ Element rawRow = (Element) _rawRows.item( rowNumber );
+ String[] columnNames = getColumnNames();
+ int columnCount = columnNames.length;
+
+ _currentRow = new String[ columnCount ];
+
+ for ( int i = 0; i < columnCount; i++ )
+ {
+ // first look for an attribute by the column name
+ String columnName = columnNames[ i ];
+ String contents = rawRow.getAttribute( columnName );
+
+ // if there is not attribute by that name, then look for descendent elements by
+ // that name. concatenate them all.
+ if ( (contents == null) || "".equals( contents ) )
+ {
+ NodeList children = rawRow.getElementsByTagName( columnName );
+
+ if ( (children != null) && (children.getLength() > 0) )
+ {
+ int childCount = children.getLength();
+ StringBuffer buffer = new StringBuffer();
+
+ for ( int j = 0; j < childCount; j++ )
+ {
+ Element child = (Element) children.item( j );
+
+ buffer.append( squeezeText( child ) );
+ }
+ contents = buffer.toString();
+ }
+ }
+
+ _currentRow[ i ] = contents;
+ }
+ }
+
+ /**
+ * <p>
+ * Squeeze the text out of an Element.
+ * </p>
+ */
+ private String squeezeText( Element node )
+ throws Exception
+ {
+ String text = null;
+ Node textChild = node.getFirstChild();
+
+ if ( textChild != null ) { text = textChild.getNodeValue(); }
+
+ return text;
+ }
+
+
+}
+
Propchange: db/derby/code/trunk/java/demo/vtis/java/org/apache/derbyDemo/vtis/core/XmlVTI.java
------------------------------------------------------------------------------
svn:eol-style = native
Added: 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=587652&view=auto
==============================================================================
--- db/derby/code/trunk/java/demo/vtis/java/org/apache/derbyDemo/vtis/example/ApacheServerLogVTI.java (added)
+++ db/derby/code/trunk/java/demo/vtis/java/org/apache/derbyDemo/vtis/example/ApacheServerLogVTI.java Tue Oct 23 13:54:05 2007
@@ -0,0 +1,133 @@
+/*
+
+Derby - Class org.apache.derbyDemo.vtis.example.ApacheServerLogVTI
+
+Licensed to the Apache Software Foundation (ASF) under one or more
+contributor license agreements. See the NOTICE file distributed with
+this work for additional information regarding copyright ownership.
+The ASF licenses this file to You under the Apache License, Version 2.0
+(the "License"); you may not use this file except in compliance with
+the License. You may obtain a copy of the License at
+
+http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing, software
+distributed under the License is distributed on an "AS IS" BASIS,
+WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+See the License for the specific language governing permissions and
+limitations under the License.
+
+*/
+
+package org.apache.derbyDemo.vtis.example;
+
+import java.sql.*;
+import java.text.SimpleDateFormat;
+
+import org.apache.derbyDemo.vtis.core.*;
+
+/**
+ * <p>
+ * This is an XML-reading VTI which has been tweaked to handle
+ * the formatting of timestamps and nulls found in Apache
+ * server logs.
+ * </p>
+ *
+ */
+public class ApacheServerLogVTI extends XmlVTI
+{
+ ///////////////////////////////////////////////////////////////////////////////////
+ //
+ // CONSTANTS
+ //
+ ///////////////////////////////////////////////////////////////////////////////////
+
+ ///////////////////////////////////////////////////////////////////////////////////
+ //
+ // STATE
+ //
+ ///////////////////////////////////////////////////////////////////////////////////
+
+ private SimpleDateFormat _dateFormatter;
+
+ ///////////////////////////////////////////////////////////////////////////////////
+ //
+ // CONSTRUCTORS
+ //
+ ///////////////////////////////////////////////////////////////////////////////////
+
+ /**
+ * <p>
+ * Construct from the same arguments as our superclass.
+ * </p>
+ */
+ public ApacheServerLogVTI( String xmlResourceName, String rowTag, String[] childTags )
+ {
+ super( xmlResourceName, rowTag, childTags );
+ }
+
+ ///////////////////////////////////////////////////////////////////////////////////
+ //
+ // ResultSet BEHAVIOR
+ //
+ ///////////////////////////////////////////////////////////////////////////////////
+
+ /**
+ * <p>
+ * The Apache Server's logs represent nulls as "-".
+ * </p>
+ */
+ public String getString(int columnIndex) throws SQLException
+ {
+ String columnValue = super.getString( columnIndex );
+
+ if ( "-".equals( columnValue ) )
+ {
+ setWasNull();
+ return null;
+ }
+ else { return columnValue; }
+ }
+
+ /**
+ * <p>
+ * The Apache Server's logs format timestamps thusly: "01/Jul/2002:17:31:19 +0200"
+ * </p>
+ */
+ public java.sql.Timestamp getTimestamp(int columnIndex) throws SQLException
+ {
+ String columnValue = getString( columnIndex );
+
+ try {
+ SimpleDateFormat dateFormatter = getDateFormatter();
+ java.util.Date rawDate = dateFormatter.parse( columnValue );
+ long time = rawDate.getTime();
+
+ return new java.sql.Timestamp( time );
+
+ } catch (Throwable t) { throw new SQLException( t.getMessage() ); }
+ }
+
+ ///////////////////////////////////////////////////////////////////////////////////
+ //
+ // MINIONS
+ //
+ ///////////////////////////////////////////////////////////////////////////////////
+
+ /**
+ * <p>
+ * The Apache Server's logs format timestamps thusly: "01/Jul/2002:17:31:19 +0200"
+ * </p>
+ */
+ private SimpleDateFormat getDateFormatter()
+ {
+ if ( _dateFormatter == null )
+ {
+ _dateFormatter = new SimpleDateFormat( "dd/MMM/yyyy:HH:mm:ss Z" );
+ }
+
+ return _dateFormatter;
+ }
+
+
+}
Propchange: db/derby/code/trunk/java/demo/vtis/java/org/apache/derbyDemo/vtis/example/ApacheServerLogVTI.java
------------------------------------------------------------------------------
svn:eol-style = native
Added: 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=587652&view=auto
==============================================================================
--- db/derby/code/trunk/java/demo/vtis/java/org/apache/derbyDemo/vtis/example/DerbyJiraReportVTI.java (added)
+++ db/derby/code/trunk/java/demo/vtis/java/org/apache/derbyDemo/vtis/example/DerbyJiraReportVTI.java Tue Oct 23 13:54:05 2007
@@ -0,0 +1,96 @@
+/*
+
+Derby - Class org.apache.derbyDemo.vtis.example.DerbyJiraReportVTI
+
+Licensed to the Apache Software Foundation (ASF) under one or more
+contributor license agreements. See the NOTICE file distributed with
+this work for additional information regarding copyright ownership.
+The ASF licenses this file to You under the Apache License, Version 2.0
+(the "License"); you may not use this file except in compliance with
+the License. You may obtain a copy of the License at
+
+http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing, software
+distributed under the License is distributed on an "AS IS" BASIS,
+WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+See the License for the specific language governing permissions and
+limitations under the License.
+
+*/
+
+package org.apache.derbyDemo.vtis.example;
+
+import java.sql.*;
+import java.text.SimpleDateFormat;
+
+import org.apache.derbyDemo.vtis.core.*;
+
+/**
+ * <p>
+ * This is an XML-reading VTI which has been tweaked to handle
+ * the formatting of JIRA ids found in Derby JIRA reports.
+ * </p>
+ *
+ */
+public class DerbyJiraReportVTI extends XmlVTI
+{
+ ///////////////////////////////////////////////////////////////////////////////////
+ //
+ // CONSTANTS
+ //
+ ///////////////////////////////////////////////////////////////////////////////////
+
+ ///////////////////////////////////////////////////////////////////////////////////
+ //
+ // STATE
+ //
+ ///////////////////////////////////////////////////////////////////////////////////
+
+ private SimpleDateFormat _dateFormatter;
+
+ ///////////////////////////////////////////////////////////////////////////////////
+ //
+ // CONSTRUCTORS
+ //
+ ///////////////////////////////////////////////////////////////////////////////////
+
+ /**
+ * <p>
+ * Construct from the same arguments as our superclass.
+ * </p>
+ */
+ public DerbyJiraReportVTI( String xmlResourceName, String rowTag, String[] childTags )
+ {
+ super( xmlResourceName, rowTag, childTags );
+ }
+
+ ///////////////////////////////////////////////////////////////////////////////////
+ //
+ // ResultSet BEHAVIOR
+ //
+ ///////////////////////////////////////////////////////////////////////////////////
+
+ /**
+ * <p>
+ * JIRA prepends "DERBY-" to the issue key. Strip off this prefix so that we
+ * can sort the key as an integer value.
+ * </p>
+ */
+ public String getString( int columnIndex ) throws SQLException
+ {
+ String rawValue = super.getString( columnIndex );
+
+ if ( !"key".equals( getColumnNames()[ columnIndex - 1 ] ) )
+ { return rawValue; }
+ else
+ { return rawValue.substring( 6 ); }
+ }
+
+ ///////////////////////////////////////////////////////////////////////////////////
+ //
+ // MINIONS
+ //
+ ///////////////////////////////////////////////////////////////////////////////////
+
+}
Propchange: db/derby/code/trunk/java/demo/vtis/java/org/apache/derbyDemo/vtis/example/DerbyJiraReportVTI.java
------------------------------------------------------------------------------
svn:eol-style = native
Added: db/derby/code/trunk/java/demo/vtis/java/org/apache/derbyDemo/vtis/example/PropertyFileVTI.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/demo/vtis/java/org/apache/derbyDemo/vtis/example/PropertyFileVTI.java?rev=587652&view=auto
==============================================================================
--- db/derby/code/trunk/java/demo/vtis/java/org/apache/derbyDemo/vtis/example/PropertyFileVTI.java (added)
+++ db/derby/code/trunk/java/demo/vtis/java/org/apache/derbyDemo/vtis/example/PropertyFileVTI.java Tue Oct 23 13:54:05 2007
@@ -0,0 +1,157 @@
+/*
+
+Derby - Class org.apache.derbyDemo.vtis.example.PropertyFileVTI
+
+Licensed to the Apache Software Foundation (ASF) under one or more
+contributor license agreements. See the NOTICE file distributed with
+this work for additional information regarding copyright ownership.
+The ASF licenses this file to You under the Apache License, Version 2.0
+(the "License"); you may not use this file except in compliance with
+the License. You may obtain a copy of the License at
+
+http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing, software
+distributed under the License is distributed on an "AS IS" BASIS,
+WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+See the License for the specific language governing permissions and
+limitations under the License.
+
+*/
+
+package org.apache.derbyDemo.vtis.example;
+
+import java.io.*;
+import java.sql.*;
+import java.text.SimpleDateFormat;
+
+import org.apache.derbyDemo.vtis.core.*;
+
+/**
+ * <p>
+ * This VTI makes a table out of a property file.
+ * </p>
+ */
+public class PropertyFileVTI extends FlatFileVTI
+{
+ ///////////////////////////////////////////////////////////////////////////////////
+ //
+ // CONSTANTS
+ //
+ ///////////////////////////////////////////////////////////////////////////////////
+
+ private static final String[] COLUMN_NAMES =
+ {
+ "propKey", "propValue"
+ };
+
+ private static final int PROPERTY_KEY = 0;
+ private static final int PROPERTY_VALUE = PROPERTY_KEY + 1;
+
+ ///////////////////////////////////////////////////////////////////////////////////
+ //
+ // STATE
+ //
+ ///////////////////////////////////////////////////////////////////////////////////
+
+ ///////////////////////////////////////////////////////////////////////////////////
+ //
+ // CONSTRUCTORS
+ //
+ ///////////////////////////////////////////////////////////////////////////////////
+
+ /**
+ * <p>
+ * Build a PropertyFileVTI given the name of a Derby message file in the
+ * source tree.
+ * </p>
+ */
+ public PropertyFileVTI( String propertyFileName )
+ {
+ super( COLUMN_NAMES, propertyFileName );
+ }
+
+ ///////////////////////////////////////////////////////////////////////////////////
+ //
+ // TABLE FUNCTION METHOD
+ //
+ ///////////////////////////////////////////////////////////////////////////////////
+
+
+ /**
+ * <p>
+ * This is the method which is registered as a table function.
+ * </p>
+ */
+ public static ResultSet propertyFileVTI( String propertyFileName )
+ throws SQLException
+ {
+ return new PropertyFileVTI( propertyFileName );
+ }
+
+ ///////////////////////////////////////////////////////////////////////////////////
+ //
+ // FlatFileVTI BEHAVIOR TO BE IMPLEMENTED BY SUBCLASSES
+ //
+ ///////////////////////////////////////////////////////////////////////////////////
+
+ /**
+ * <p>
+ * Parse the next chunk of text, using readLine(), and return the next row.
+ * Returns null if the file is exhausted.
+ * </p>
+ */
+ protected String[] parseRow( ) throws SQLException
+ {
+ String[] newRow = new String[ COLUMN_NAMES.length ];
+ String nextLine = null;
+
+ while( true )
+ {
+ nextLine = readLine();
+
+ // if at EOF, get out of here
+ if ( nextLine == null ) { return null; }
+
+ nextLine = nextLine.trim();
+
+ // skip blank lines and lines which start with the comment character
+ if ( nextLine.startsWith( "#" ) ) { continue; }
+ else if ( nextLine.length() == 0 ) { continue; }
+ else { break; }
+ }
+
+ int equalsIdx = nextLine.indexOf( '=' );
+
+ try {
+ newRow[ PROPERTY_KEY ] = nextLine.substring( 0, equalsIdx );
+ newRow[ PROPERTY_VALUE ] = nextLine.substring( equalsIdx, nextLine.length() );
+ }
+ catch (Throwable t)
+ {
+ SQLException se = new SQLException
+ (
+ "Unparseable line number " + getLineNumber() + " in file " + getTextFileName() + ": " + nextLine
+ );
+ se.initCause( t );
+
+ throw se;
+ }
+
+ return newRow;
+ }
+
+ ///////////////////////////////////////////////////////////////////////////////////
+ //
+ // ResultSet METHODS
+ //
+ ///////////////////////////////////////////////////////////////////////////////////
+
+ ///////////////////////////////////////////////////////////////////////////////////
+ //
+ // MINIONS
+ //
+ ///////////////////////////////////////////////////////////////////////////////////
+
+
+}
Propchange: db/derby/code/trunk/java/demo/vtis/java/org/apache/derbyDemo/vtis/example/PropertyFileVTI.java
------------------------------------------------------------------------------
svn:eol-style = native
Added: db/derby/code/trunk/java/demo/vtis/java/org/apache/derbyDemo/vtis/example/SubversionLogVTI.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/demo/vtis/java/org/apache/derbyDemo/vtis/example/SubversionLogVTI.java?rev=587652&view=auto
==============================================================================
--- db/derby/code/trunk/java/demo/vtis/java/org/apache/derbyDemo/vtis/example/SubversionLogVTI.java (added)
+++ db/derby/code/trunk/java/demo/vtis/java/org/apache/derbyDemo/vtis/example/SubversionLogVTI.java Tue Oct 23 13:54:05 2007
@@ -0,0 +1,280 @@
+/*
+
+Derby - Class org.apache.derbyDemo.vtis.example.SubversionLogVTI
+
+Licensed to the Apache Software Foundation (ASF) under one or more
+contributor license agreements. See the NOTICE file distributed with
+this work for additional information regarding copyright ownership.
+The ASF licenses this file to You under the Apache License, Version 2.0
+(the "License"); you may not use this file except in compliance with
+the License. You may obtain a copy of the License at
+
+http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing, software
+distributed under the License is distributed on an "AS IS" BASIS,
+WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+See the License for the specific language governing permissions and
+limitations under the License.
+
+*/
+
+package org.apache.derbyDemo.vtis.example;
+
+import java.io.*;
+import java.sql.*;
+import java.text.SimpleDateFormat;
+
+import org.apache.derbyDemo.vtis.core.*;
+
+/**
+ * <p>
+ * This VTI makes a table out of the output of the subversion log ("svn log") command.
+ * </p>
+ */
+public class SubversionLogVTI extends FlatFileVTI
+{
+ ///////////////////////////////////////////////////////////////////////////////////
+ //
+ // CONSTANTS
+ //
+ ///////////////////////////////////////////////////////////////////////////////////
+
+ private static final String[] COLUMN_NAMES =
+ {
+ "XID", "committer", "commit_time", "line_count", "description"
+ };
+
+ private static final int XID = 0;
+ private static final int COMMITTER = XID + 1;
+ private static final int COMMIT_TIME = COMMITTER + 1;
+ private static final int LINE_COUNT = COMMIT_TIME + 1;
+ private static final int DESCRIPTION = LINE_COUNT + 1;
+
+ private static final String RECORD_HEADER = "------------------------------------------------------------------------";
+
+
+ ///////////////////////////////////////////////////////////////////////////////////
+ //
+ // STATE
+ //
+ ///////////////////////////////////////////////////////////////////////////////////
+
+ private SimpleDateFormat _dateFormatter;
+
+ ///////////////////////////////////////////////////////////////////////////////////
+ //
+ // CONSTRUCTORS
+ //
+ ///////////////////////////////////////////////////////////////////////////////////
+
+ /**
+ * <p>
+ * Build a SubversionLogVTI given the name of the output file created by the
+ * "svn log" command.
+ * </p>
+ */
+ public SubversionLogVTI( String logFileName )
+ {
+ super( COLUMN_NAMES, logFileName );
+ }
+
+ ///////////////////////////////////////////////////////////////////////////////////
+ //
+ // TABLE FUNCTION METHOD
+ //
+ ///////////////////////////////////////////////////////////////////////////////////
+
+
+ /**
+ * <p>
+ * This is the method which is registered as a table function.
+ * </p>
+ */
+ public static ResultSet subversionLogVTI( String logFileName )
+ throws SQLException
+ {
+ return new SubversionLogVTI( logFileName );
+ }
+
+ ///////////////////////////////////////////////////////////////////////////////////
+ //
+ // FlatFileVTI BEHAVIOR TO BE IMPLEMENTED BY SUBCLASSES
+ //
+ ///////////////////////////////////////////////////////////////////////////////////
+
+ /**
+ * <p>
+ * Parse the next chunk of text, using readLine(), and return the next row.
+ * Returns null if the file is exhausted.
+ * </p>
+ */
+ protected String[] parseRow( ) throws SQLException
+ {
+ String[] newRow = new String[ COLUMN_NAMES.length ];
+
+ String headerLine = readNextLine();
+
+ if ( headerLine == null ) { return null; }
+ if ( !isRecordHeader( headerLine ) )
+ {
+ throw new SQLException( "Badly formatted record header: " + headerLine );
+ }
+
+ // the next line has all of the columns except for DESCRIPTION
+
+ String mainline = readNextLine();
+
+ if ( mainline == null ) { return null; }
+
+ int oldIdx[] = new int[ 1 ];
+
+ oldIdx[ 0 ] = 0;
+
+ for ( int i = 0; i < DESCRIPTION; i++ ) { newRow[ i ] = readField( mainline, oldIdx ); }
+
+ // get the number of lines in the DESCRIPTION
+ int descriptionLineCount = 0;
+
+ try {
+ String lineCountField = newRow[ LINE_COUNT ];
+
+ if ( lineCountField != null )
+ {
+ lineCountField = lineCountField.trim();
+ String numberString = lineCountField.substring( 0, lineCountField.indexOf( ' ' ) );
+
+ descriptionLineCount = Integer.parseInt( numberString );
+ }
+ }
+ catch ( Throwable t )
+ {
+ throw wrap( "Error parsing description line count at line " + getLineNumber() + ": " + mainline, t );
+ }
+
+ // account for extra blank line
+ descriptionLineCount++;
+
+ // the rest of the record is the DESCRIPTION
+
+ StringBuffer buffer = new StringBuffer();
+
+ for ( int i = 0; i < descriptionLineCount; i++ )
+ {
+ String nextLine = readNextLine();
+
+ buffer.append( nextLine );
+ }
+
+ newRow[ DESCRIPTION ] = buffer.toString();
+
+ return newRow;
+ }
+
+ ///////////////////////////////////////////////////////////////////////////////////
+ //
+ // ResultSet METHOD
+ //
+ ///////////////////////////////////////////////////////////////////////////////////
+
+ /**
+ * <p>
+ * Subversion formats timestamps thusly: "2007-09-16 11:17:37 -0700 (Sun, 16 Sep 2007)"
+ * </p>
+ */
+ public java.sql.Timestamp getTimestamp(int columnIndex) throws SQLException
+ {
+ String columnValue = getString( columnIndex ).trim();
+
+ try {
+ SimpleDateFormat dateFormatter = getDateFormatter();
+ java.util.Date rawDate = dateFormatter.parse( columnValue );
+ long time = rawDate.getTime();
+
+ return new java.sql.Timestamp( time );
+
+ } catch (Throwable t) { throw new SQLException( t.getMessage() ); }
+ }
+
+ ///////////////////////////////////////////////////////////////////////////////////
+ //
+ // MINIONS
+ //
+ ///////////////////////////////////////////////////////////////////////////////////
+
+ /**
+ * <p>
+ * Read the next field in the main line of the record. Fields are delimited
+ * by | or line-end.
+ * </p>
+ */
+ private String readField( String mainline, int[] oldIdx )
+ throws SQLException
+ {
+ String result = null;
+ int fieldStart = oldIdx[ 0 ];
+
+ int fieldEnd = mainline.indexOf( "|", fieldStart );
+
+ if ( fieldEnd < 0 ) { fieldEnd = mainline.length(); }
+
+ // this can happen, if for instance, we encounter a badly formatted record
+ if ( fieldStart > fieldEnd ) { return null; }
+
+ try {
+ result = mainline.substring( fieldStart, fieldEnd );
+
+ if ( result != null ) { result = result.trim(); }
+
+ } catch (Throwable t)
+ {
+ throw wrap( "Bad record at line " + getLineNumber() + ". Field start = " + fieldStart + ", fieldEnd = " + fieldEnd + ": " + mainline, t );
+ }
+
+ oldIdx[ 0 ] = fieldEnd + 1;
+
+ return result;
+ }
+
+ /**
+ * <p>
+ * Returns true if a line is a record header.
+ * </p>
+ */
+ private boolean isRecordHeader( String line )
+ {
+ if ( line.startsWith( RECORD_HEADER ) ) { return true; }
+ else { return false; }
+ }
+
+ /**
+ * <p>
+ * Read a line, possibly just using the last line that was pushed back.
+ * </p>
+ */
+ private String readNextLine() throws SQLException
+ {
+ String retval;
+
+ retval = readLine();
+
+ return retval;
+ }
+
+ /**
+ * <p>
+ * Subversion formats timestamps thusly: "2007-09-16 11:17:37 -0700 (Sun, 16 Sep 2007)"
+ * </p>
+ */
+ private SimpleDateFormat getDateFormatter()
+ {
+ if ( _dateFormatter == null )
+ {
+ _dateFormatter = new SimpleDateFormat( "yyyy-MM-dd HH:mm:ss Z (EEE, dd MMM yyyy)" );
+ }
+
+ return _dateFormatter;
+ }
+
+
+}
Propchange: db/derby/code/trunk/java/demo/vtis/java/org/apache/derbyDemo/vtis/example/SubversionLogVTI.java
------------------------------------------------------------------------------
svn:eol-style = native
Added: db/derby/code/trunk/java/demo/vtis/java/org/apache/derbyDemo/vtis/example/VTIs.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/demo/vtis/java/org/apache/derbyDemo/vtis/example/VTIs.java?rev=587652&view=auto
==============================================================================
--- db/derby/code/trunk/java/demo/vtis/java/org/apache/derbyDemo/vtis/example/VTIs.java (added)
+++ db/derby/code/trunk/java/demo/vtis/java/org/apache/derbyDemo/vtis/example/VTIs.java Tue Oct 23 13:54:05 2007
@@ -0,0 +1,145 @@
+/*
+
+Derby - Class org.apache.derbyDemo.vtis.example.VTIs
+
+Licensed to the Apache Software Foundation (ASF) under one or more
+contributor license agreements. See the NOTICE file distributed with
+this work for additional information regarding copyright ownership.
+The ASF licenses this file to You under the Apache License, Version 2.0
+(the "License"); you may not use this file except in compliance with
+the License. You may obtain a copy of the License at
+
+http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing, software
+distributed under the License is distributed on an "AS IS" BASIS,
+WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+See the License for the specific language governing permissions and
+limitations under the License.
+
+*/
+
+package org.apache.derbyDemo.vtis.example;
+
+import java.lang.reflect.*;
+import java.sql.*;
+
+import org.apache.derbyDemo.vtis.core.*;
+
+/**
+ * <p>
+ * This is a set of table functions based on the annotations and helper logic
+ * provided with this demo code.
+ * </p>
+ *
+ */
+public class VTIs
+{
+ ///////////////////////////////////////////////////////////////////////////////////
+ //
+ // CONSTANTS
+ //
+ ///////////////////////////////////////////////////////////////////////////////////
+
+ ///////////////////////////////////////////////////////////////////////////////////
+ //
+ // STATE
+ //
+ ///////////////////////////////////////////////////////////////////////////////////
+
+ ///////////////////////////////////////////////////////////////////////////////////
+ //
+ // XML VTIs
+ //
+ ///////////////////////////////////////////////////////////////////////////////////
+
+ /**
+ * <p>
+ * This is a vanilla XML VTI with no special processing for the formatting
+ * of accessDates or nulls.
+ * </p>
+ */
+ @XMLRow
+ (
+ rowTag = "Visitor",
+ childTags = { "IP", "accessDate", "request", "statusCode", "fileSize", "referrer", "userAgent" },
+ childTypes = { "varchar(30)", "varchar(30)", "clob", "int", "varchar( 10 )", "varchar(30)", "clob" },
+ vtiClassName = "org.apache.derbyDemo.vtis.core.XmlVTI"
+ )
+ public static ResultSet apacheVanillaLogFile( String xmlResource ) throws SQLException
+ { return XmlVTI.instantiateVTI( xmlResource ); }
+
+ /**
+ * <p>
+ * This is an XML VTI which handles the Apache server's formatting of accessDate and
+ * nulls. This allows us to represent accessDate as a timestamp and to
+ * expose nulls in the log.
+ * </p>
+ */
+ @XMLRow
+ (
+ rowTag = "Visitor",
+ childTags = { "IP", "accessDate", "request", "statusCode", "fileSize", "referrer", "userAgent" },
+ childTypes = { "varchar(30)", "timestamp", "clob", "int", "int", "varchar(30)", "clob" },
+ vtiClassName = "org.apache.derbyDemo.vtis.example.ApacheServerLogVTI"
+ )
+ public static ResultSet apacheNaturalLogFile( String xmlResource ) throws SQLException
+ { return XmlVTI.instantiateVTI( xmlResource ); }
+
+ /**
+ * <p>
+ * This is a vanilla XML VTI for reading a Derby JIRA report.
+ * </p>
+ */
+ @XMLRow
+ (
+ rowTag = "item",
+ childTags = { "key", "type", "priority", "status", "component", "title" },
+ childTypes = { "varchar(12)", "varchar(10)", "varchar(10)", "varchar(10)", "varchar(50)", "varchar(200)" },
+ vtiClassName = "org.apache.derbyDemo.vtis.core.XmlVTI"
+ )
+ public static ResultSet apacheVanillaJiraReport( String xmlResource ) throws SQLException
+ { return XmlVTI.instantiateVTI( xmlResource ); }
+
+ /**
+ * <p>
+ * ThisXML VTI treats keys as integers when parsing Derby JIRA reports.
+ * </p>
+ */
+ @XMLRow
+ (
+ rowTag = "item",
+ childTags = { "key", "type", "priority", "status", "component", "customfieldvalue", "title" },
+ childTypes = { "int", "varchar(10)", "varchar(10)", "varchar(10)", "varchar(50)", "varchar(200)", "varchar(200)" },
+ vtiClassName = "org.apache.derbyDemo.vtis.example.DerbyJiraReportVTI"
+ )
+ public static ResultSet apacheNaturalJiraReport( String xmlResource ) throws SQLException
+ { return XmlVTI.instantiateVTI( xmlResource ); }
+
+ ///////////////////////////////////////////////////////////////////////////////////
+ //
+ // Query VTIs
+ //
+ ///////////////////////////////////////////////////////////////////////////////////
+
+ /**
+ * <p>
+ * This simple VTI siphons a table out of a MySQL database.
+ * </p>
+ */
+ @QueryRow
+ (
+ jdbcDriverName = "com.mysql.jdbc.Driver",
+ query = "select * from CountryLanguage"
+ )
+ public static ResultSet countryLanguage( String connectionURL ) throws SQLException
+ { return QueryVTIHelper.instantiateQueryRowVTI( connectionURL ); }
+
+ ///////////////////////////////////////////////////////////////////////////////////
+ //
+ // MINIONS
+ //
+ ///////////////////////////////////////////////////////////////////////////////////
+
+
+}
Propchange: db/derby/code/trunk/java/demo/vtis/java/org/apache/derbyDemo/vtis/example/VTIs.java
------------------------------------------------------------------------------
svn:eol-style = native
Added: db/derby/code/trunk/java/demo/vtis/java/org/apache/derbyDemo/vtis/example/WorldDBSnapshot.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/demo/vtis/java/org/apache/derbyDemo/vtis/example/WorldDBSnapshot.java?rev=587652&view=auto
==============================================================================
--- db/derby/code/trunk/java/demo/vtis/java/org/apache/derbyDemo/vtis/example/WorldDBSnapshot.java (added)
+++ db/derby/code/trunk/java/demo/vtis/java/org/apache/derbyDemo/vtis/example/WorldDBSnapshot.java Tue Oct 23 13:54:05 2007
@@ -0,0 +1,66 @@
+/*
+
+Derby - Class org.apache.derbyDemo.vtis.example.WorldDBSnapshot
+
+Licensed to the Apache Software Foundation (ASF) under one or more
+contributor license agreements. See the NOTICE file distributed with
+this work for additional information regarding copyright ownership.
+The ASF licenses this file to You under the Apache License, Version 2.0
+(the "License"); you may not use this file except in compliance with
+the License. You may obtain a copy of the License at
+
+http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing, software
+distributed under the License is distributed on an "AS IS" BASIS,
+WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+See the License for the specific language governing permissions and
+limitations under the License.
+
+*/
+
+package org.apache.derbyDemo.vtis.example;
+
+import java.sql.*;
+
+import org.apache.derbyDemo.vtis.snapshot.*;
+
+/**
+ * <p>
+ * This is a parameterized subscription to a slice of the world database managed
+ * by a MySQL server.
+ * </p>
+ *
+ */
+@SubscriptionSignature
+ (
+ jdbcDriverName = "com.mysql.jdbc.Driver",
+ parameters = { "populationMin", "populationMax" },
+ refreshProcedureName = "refreshWorldDB"
+ )
+public class WorldDBSnapshot extends Subscription
+{
+ @SnapshotQuery
+ (
+ parameters = { "populationMin", "populationMax" },
+ query = "select * from City where Population between ? and ?"
+ )
+ public static ResultSet City() throws SQLException { return instantiateSnapshotQueryVTI(); }
+
+ @SnapshotQuery
+ (
+ parameters = { "populationMin", "populationMax" },
+ query = "select * from Country where Code in ( select CountryCode from City where Population between ? and ? )"
+ )
+ public static ResultSet Country() throws SQLException { return instantiateSnapshotQueryVTI(); }
+
+ @SnapshotQuery
+ (
+ parameters = { "populationMin", "populationMax" },
+ query = "select * from CountryLanguage where CountryCode in ( select CountryCode from City where Population between ? and ? )"
+ )
+ public static ResultSet CountryLanguage() throws SQLException { return instantiateSnapshotQueryVTI(); }
+
+}
+
+
Propchange: db/derby/code/trunk/java/demo/vtis/java/org/apache/derbyDemo/vtis/example/WorldDBSnapshot.java
------------------------------------------------------------------------------
svn:eol-style = native
Added: db/derby/code/trunk/java/demo/vtis/java/org/apache/derbyDemo/vtis/snapshot/SnapshotQuery.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/demo/vtis/java/org/apache/derbyDemo/vtis/snapshot/SnapshotQuery.java?rev=587652&view=auto
==============================================================================
--- db/derby/code/trunk/java/demo/vtis/java/org/apache/derbyDemo/vtis/snapshot/SnapshotQuery.java (added)
+++ db/derby/code/trunk/java/demo/vtis/java/org/apache/derbyDemo/vtis/snapshot/SnapshotQuery.java Tue Oct 23 13:54:05 2007
@@ -0,0 +1,58 @@
+/*
+
+Derby - Class org.apache.derbyDemo.vtis.snapshot.SnapshotQuery
+
+Licensed to the Apache Software Foundation (ASF) under one or more
+contributor license agreements. See the NOTICE file distributed with
+this work for additional information regarding copyright ownership.
+The ASF licenses this file to You under the Apache License, Version 2.0
+(the "License"); you may not use this file except in compliance with
+the License. You may obtain a copy of the License at
+
+http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing, software
+distributed under the License is distributed on an "AS IS" BASIS,
+WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+See the License for the specific language governing permissions and
+limitations under the License.
+
+*/
+
+package org.apache.derbyDemo.vtis.snapshot;
+
+import java.lang.annotation.*;
+
+/**
+ * <p>
+ * This is an Annotation describing the query needed to materialize a ResultSet
+ * from a foreign database. The driver name and connection url must still be
+ * specified at run time.
+ * </p>
+ *
+ */
+@Retention( value=RetentionPolicy.RUNTIME )
+public @interface SnapshotQuery
+{
+ ///////////////////////////////////////////////////////////////////////////////////
+ //
+ // CONSTANTS
+ //
+ ///////////////////////////////////////////////////////////////////////////////////
+
+ ///////////////////////////////////////////////////////////////////////////////////
+ //
+ // BEHAVIOR
+ //
+ ///////////////////////////////////////////////////////////////////////////////////
+
+ /** The names of the parameters passed to the query. These are parameter
+ * names mentioned in the SubscriptionSignature of the enclosing
+ * Subscription class */
+ String[] parameters();
+
+ /** The query string that is passed to the foreign database */
+ String query();
+
+
+}
Propchange: db/derby/code/trunk/java/demo/vtis/java/org/apache/derbyDemo/vtis/snapshot/SnapshotQuery.java
------------------------------------------------------------------------------
svn:eol-style = native
Added: db/derby/code/trunk/java/demo/vtis/java/org/apache/derbyDemo/vtis/snapshot/Subscription.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/demo/vtis/java/org/apache/derbyDemo/vtis/snapshot/Subscription.java?rev=587652&view=auto
==============================================================================
--- db/derby/code/trunk/java/demo/vtis/java/org/apache/derbyDemo/vtis/snapshot/Subscription.java (added)
+++ db/derby/code/trunk/java/demo/vtis/java/org/apache/derbyDemo/vtis/snapshot/Subscription.java Tue Oct 23 13:54:05 2007
@@ -0,0 +1,610 @@
+/*
+
+Derby - Class org.apache.derbyDemo.vtis.snapshot.Subscription
+
+Licensed to the Apache Software Foundation (ASF) under one or more
+contributor license agreements. See the NOTICE file distributed with
+this work for additional information regarding copyright ownership.
+The ASF licenses this file to You under the Apache License, Version 2.0
+(the "License"); you may not use this file except in compliance with
+the License. You may obtain a copy of the License at
+
+http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing, software
+distributed under the License is distributed on an "AS IS" BASIS,
+WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+See the License for the specific language governing permissions and
+limitations under the License.
+
+*/
+
+package org.apache.derbyDemo.vtis.snapshot;
+
+import java.io.*;
+import java.lang.reflect.*;
+import java.sql.*;
+import java.util.*;
+
+import org.apache.derbyDemo.vtis.core.*;
+
+/**
+ * <p>
+ * This is the superclass of parameterized subscriptions to foreign data. This
+ * provides the machinery to drop/create a subscription and to refresh it with
+ * the latest foreign data filtered according to the subscription parameters.
+ * </p>
+ *
+ */
+public abstract class Subscription extends QueryVTIHelper
+{
+ ///////////////////////////////////////////////////////////////////////////////////
+ //
+ // CONSTANTS
+ //
+ ///////////////////////////////////////////////////////////////////////////////////
+
+ ///////////////////////////////////////////////////////////////////////////////////
+ //
+ // INNER CLASSES
+ //
+ ///////////////////////////////////////////////////////////////////////////////////
+
+ /**
+ * <p>
+ * This is the state variable used by the logic which creates and refreshes
+ * a Subscription. This state is shared across all of the queries against
+ * the foreign database.
+ * </p>
+ *
+ */
+ public static final class SubscriptionContext
+ {
+ private SubscriptionSignature _signature;
+ private HashMap<String, String> _parameterValues;
+ private String _connectionURL;
+
+ public SubscriptionContext( SubscriptionSignature signature, HashMap<String, String> parameterValues, String connectionURL )
+ {
+ _signature = signature;
+ _parameterValues = parameterValues;
+ _connectionURL = connectionURL;
+ }
+
+ public SubscriptionSignature getSubscriptionSignature() { return _signature; }
+ public HashMap<String, String> getParameterValues() { return _parameterValues; }
+ public String getConnectionURL() { return _connectionURL; }
+
+ public String toString()
+ {
+ StringBuffer buffer = new StringBuffer();
+
+ buffer.append( "SubscriptionContext( " );
+ buffer.append( " signature = " + _signature );
+ buffer.append( ", parameterValues = " + _parameterValues );
+ buffer.append( ", connectionURL = " + _connectionURL );
+ buffer.append( " )" );
+
+ return buffer.toString();
+ }
+ }
+
+ ///////////////////////////////////////////////////////////////////////////////////
+ //
+ // STATE
+ //
+ ///////////////////////////////////////////////////////////////////////////////////
+
+ private static HashMap<String, SubscriptionContext> _contexts = new HashMap<String, SubscriptionContext>();
+
+ ///////////////////////////////////////////////////////////////////////////////////
+ //
+ // CONSTRUCTORS
+ //
+ ///////////////////////////////////////////////////////////////////////////////////
+
+ ///////////////////////////////////////////////////////////////////////////////////
+ //
+ // PUBLIC PROCEDURES
+ //
+ ///////////////////////////////////////////////////////////////////////////////////
+
+ /**
+ * <p>
+ * Create an empty subscription. You must refresh it later on to actually
+ * populate it with data. This is registered with Derby as the
+ * "createSubscription" procedure.
+ * </p>
+ *
+ */
+ public static void createSubscription( String subscriptionClassName, String connectionURL )
+ throws Exception
+ {
+ Class subscriptionClass = Class.forName( subscriptionClassName );
+ SubscriptionSignature subscriptionSignature = (SubscriptionSignature) subscriptionClass.getAnnotation( SubscriptionSignature.class );
+ String jdbcDriverName = subscriptionSignature.jdbcDriverName();
+ String[] subscriptionParameters = subscriptionSignature.parameters();
+ Method[] methods = subscriptionClass.getMethods();
+ int methodCount = methods.length;
+ Method candidate = null;
+ int paramCount = subscriptionParameters.length;
+ HashSet<String> parameterMap = new HashSet<String>();
+
+ for ( int i = 0; i < paramCount; i++ )
+ {
+ parameterMap.add( subscriptionParameters[ i ] );
+ }
+
+ createContext( subscriptionClassName, null, connectionURL );
+
+ try {
+ for ( int i = 0; i < methodCount; i++ )
+ {
+ candidate = methods[ i ];
+
+ if ( isSnapshotQuery( candidate ) )
+ {
+ createVTIAndEmptyTable( subscriptionClassName, candidate, jdbcDriverName, connectionURL, parameterMap );
+ }
+ }
+
+ registerRefreshProcedure( subscriptionSignature );
+ }
+ finally
+ {
+ dropContext( subscriptionClassName );
+ }
+ }
+
+ /**
+ * <p>
+ * Drop a subscription. This is registered with Derby as the
+ * "dropSubscription" procedure.
+ * </p>
+ *
+ */
+ public static void dropSubscription( String subscriptionClassName )
+ throws Exception
+ {
+ Class subscriptionClass = Class.forName( subscriptionClassName );
+ SubscriptionSignature subscriptionSignature = (SubscriptionSignature) subscriptionClass.getAnnotation( SubscriptionSignature.class );
+ Method[] methods = subscriptionClass.getMethods();
+ int methodCount = methods.length;
+ Method candidate = null;
+ SnapshotQuery snapshotQueryAnnotation = null;
+
+ createContext( subscriptionClassName, null, null );
+
+ try {
+ for ( int i = 0; i < methodCount; i++ )
+ {
+ candidate = methods[ i ];
+
+ if ( isSnapshotQuery( candidate ) ) { dropVTIAndTable( candidate ); }
+ }
+
+ unregisterRefreshProcedure( subscriptionSignature );
+ }
+ finally
+ {
+ dropContext( subscriptionClassName );
+ }
+ }
+
+ /**
+ * <p>
+ * Refresh a subscription. This is called by the
+ * refresh procedure whose name is the refreshProcedureName from the
+ * subscription's SubscriptionSignature. The trailing varargs are the
+ * parameter values.
+ * </p>
+ *
+ */
+ public static void refreshSubscription( String subscriptionClassName, String connectionURL, String... parameterValues )
+ throws Exception
+ {
+ Class subscriptionClass = Class.forName( subscriptionClassName );
+ SubscriptionSignature subscriptionSignature = (SubscriptionSignature) subscriptionClass.getAnnotation( SubscriptionSignature.class );
+ String jdbcDriverName = subscriptionSignature.jdbcDriverName();
+ String[] parameterNames = subscriptionSignature.parameters();
+ Method[] methods = subscriptionClass.getMethods();
+ int methodCount = methods.length;
+ Method candidate = null;
+ ArrayList<Method> snapshotQueries = new ArrayList<Method>();
+ Connection foreignConnection = getConnection( jdbcDriverName, connectionURL );
+ Connection localConnection = VTIHelper.getLocalConnection();
+ boolean oldLocalAutoCommitState = localConnection.getAutoCommit();
+
+ createContext( subscriptionClassName, parameterValues, connectionURL );
+
+ try {
+ // turn off autocommit so that the whole batch occurs in one transaction
+ foreignConnection.setAutoCommit( false );
+ localConnection.setAutoCommit( false );
+
+ // find all the snapshot queries
+ for ( int i = 0; i < methodCount; i++ )
+ {
+ candidate = methods[ i ];
+
+ if ( isSnapshotQuery( candidate ) ) { snapshotQueries.add( candidate ); }
+ }
+
+ truncateTables( snapshotQueries );
+ fillTables( snapshotQueries );
+
+ // commit foreign and local transactions
+ foreignConnection.commit();
+ localConnection.commit();
+
+ // return autocommit to its previous state
+ localConnection.setAutoCommit( oldLocalAutoCommitState );
+
+ // now release the foreign connection
+ closeConnection( connectionURL );
+ }
+ finally
+ {
+ dropContext( subscriptionClassName );
+ }
+ }
+
+ ///////////////////////////////////////////////////////////////////////////////////
+ //
+ // PROTECTED BEHAVIOR
+ //
+ ///////////////////////////////////////////////////////////////////////////////////
+
+ /**
+ * <p>
+ * Create a VTI ResultSet. It is assumed that our caller is a
+ * SnapshotQuery-annotated method with no arguments.
+ * </p>
+ *
+ */
+ protected static ResultSet instantiateSnapshotQueryVTI()
+ throws SQLException
+ {
+ String subscriptionClassName = null;
+ SnapshotQuery annotation = null;
+
+ try {
+ // look up the method on top of us
+ StackTraceElement[] stack = (new Throwable()).getStackTrace();
+ StackTraceElement caller = stack[ 1 ];
+ Class callerClass = Class.forName( caller.getClassName() );
+ String methodName = caller.getMethodName();
+ Method method = callerClass.getMethod
+ ( methodName, new Class[] {} );
+
+ subscriptionClassName = callerClass.getName();
+ annotation = method.getAnnotation( SnapshotQuery.class );
+ } catch (Throwable t) { throw new SQLException( t.getMessage() ); }
+
+ SubscriptionContext context = getContext( subscriptionClassName, true );
+
+ String jdbcDriverName = context.getSubscriptionSignature().jdbcDriverName();
+ String connectionURL = context.getConnectionURL();
+ String query = annotation.query();
+ String[] queryParameterNames = annotation.parameters();
+ int count = queryParameterNames.length;
+ String[] params = new String[ count ];
+ HashMap<String, String> parameterValues = context.getParameterValues();
+
+ if ( parameterValues != null )
+ {
+ for ( int i = 0; i < count; i++ )
+ {
+ params[ i ] = parameterValues.get( queryParameterNames[ i ] );
+ }
+
+ }
+
+ return instantiateVTI( jdbcDriverName, connectionURL, query, params );
+ }
+
+ ///////////////////////////////////////////////////////////////////////////////////
+ //
+ // MINIONS
+ //
+ ///////////////////////////////////////////////////////////////////////////////////
+
+ /**
+ * <p>
+ * Returns true if the method is a snapshot query.
+ * </p>
+ *
+ */
+ private static boolean isSnapshotQuery( Method candidate )
+ {
+ int modifiers = candidate.getModifiers();
+
+ return
+ (
+ Modifier.isPublic( modifiers ) &&
+ Modifier.isStatic( modifiers ) &&
+ candidate.getReturnType() == ResultSet.class &&
+ ( candidate.getAnnotation( SnapshotQuery.class ) != null )
+ );
+ }
+
+
+ /**
+ * <p>
+ * Create a VTI to grab data from a foreign data source. Also create an
+ * empty Derby table to hold its results.
+ * </p>
+ *
+ */
+ private static void createVTIAndEmptyTable
+ ( String subscriptionClassName, Method method, String jdbcDriverName, String connectionURL, HashSet<String> parameterMap )
+ throws Exception
+ {
+ SnapshotQuery details = method.getAnnotation( SnapshotQuery.class );
+ String query = details.query();
+ String[] queryParameters = details.parameters();
+ int paramCount = queryParameters.length;
+ // placeholders just so that we can determine the query's shape
+ String[] argValues = new String[ paramCount ];
+ String functionName = getFunctionName( method );
+ String tableName = getTableName( method );
+
+ for ( int i = 0; i < paramCount; i++ )
+ {
+ String paramName = queryParameters[ i ];
+
+ if ( !parameterMap.contains( paramName ) )
+ {
+ throw new SQLException( paramName + " is not a parameter defined for subscription " + subscriptionClassName );
+ }
+ }
+
+ // first create the table function to read from the foreign database
+ registerVTI( method, jdbcDriverName, connectionURL, query, argValues );
+
+ // now create a table based on the shape of the query
+ createEmptyTable( tableName, functionName );
+ }
+
+ /**
+ * <p>
+ * Drop a snapshot VTI and the table where its results are dumped.
+ * </p>
+ *
+ */
+ private static void dropVTIAndTable
+ ( Method method )
+ throws Exception
+ {
+ String functionName = getFunctionName( method );
+ String tableName = getTableName( method );
+
+ VTIHelper.dropObject( "function", functionName, false );
+ VTIHelper.dropObject( "table", tableName, false );
+ }
+
+ /**
+ * <p>
+ * Create an empty table based on the shape of a table function.
+ * </p>
+ *
+ */
+ private static void createEmptyTable
+ ( String tableName, String functionName )
+ throws SQLException
+ {
+ StringBuilder buffer = new StringBuilder();
+
+ buffer.append( "create table " ); buffer.append( tableName ); buffer.append( "\n" );
+ buffer.append( "as select s.* from table( " + functionName + "( ) ) s\n" );
+ buffer.append( "with no data\n" );
+
+ VTIHelper.executeDDL( buffer.toString() );
+ }
+
+ /**
+ * <p>
+ * Declare the refresh procedure for the subscription.
+ * </p>
+ *
+ */
+ private static void registerRefreshProcedure( SubscriptionSignature subscriptionSignature )
+ throws Exception
+ {
+ String refreshProcedureName = subscriptionSignature.refreshProcedureName();
+ String[] subscriptionParameters = subscriptionSignature.parameters();
+ int parameterCount = subscriptionParameters.length;
+ StringBuffer buffer = new StringBuffer();
+
+ buffer.append( "create procedure " + refreshProcedureName + "\n" );
+ buffer.append( "(\n" );
+ buffer.append( "\tsubscriptionClassName varchar( 32672 ),\n" );
+ buffer.append( "\tconnectionURL varchar( 32672 )\n" );
+ for ( int i = 0; i < parameterCount; i++ )
+ {
+ buffer.append( ", arg" + i + " varchar( 32672 )\n" );
+ }
+ buffer.append( ")\n" );
+ buffer.append( "language java\n" );
+ buffer.append( "parameter style java\n" );
+ buffer.append( "modifies sql data\n" );
+ buffer.append( "external name 'org.apache.derbyDemo.vtis.snapshot.Subscription.refreshSubscription'\n" );
+
+ VTIHelper.executeDDL( buffer.toString() );
+ }
+
+ /**
+ * <p>
+ * Drop the refresh procedure for a subscription.
+ * </p>
+ *
+ */
+ private static void unregisterRefreshProcedure( SubscriptionSignature signature )
+ throws Exception
+ {
+ VTIHelper.dropObject( "procedure", signature.refreshProcedureName(), false );
+ }
+
+ /**
+ * <p>
+ * Empty all of the subscribed tables.
+ * </p>
+ *
+ */
+ private static void truncateTables( ArrayList<Method> snapshotQueries )
+ throws Exception
+ {
+ Connection conn = VTIHelper.getLocalConnection();
+ int count = snapshotQueries.size();
+
+ for ( int i = count - 1; i > -1; i-- )
+ {
+ Method method = snapshotQueries.get( i );
+ String tableName = getTableName( method );
+ String sql = "delete from " + tableName;
+
+ VTIHelper.print( sql );
+
+ PreparedStatement ps = conn.prepareStatement( sql );
+
+ ps.execute();
+ ps.close();
+ }
+ }
+
+ /**
+ * <p>
+ * Fill all of the subscribed tables.
+ * </p>
+ *
+ */
+ private static void fillTables( ArrayList<Method> snapshotQueries )
+ throws Exception
+ {
+ Connection conn = VTIHelper.getLocalConnection();
+ int count = snapshotQueries.size();
+
+ for ( int i = 0; i < count; i++ )
+ {
+ Method method = snapshotQueries.get( i );
+ String tableName = getTableName( method );
+ String functionName = getFunctionName( method );
+ String alias = "xxx";
+ String sql =
+ ( "insert into " + tableName + " select " + alias + ".* from table( " + functionName + "() ) " + alias );
+
+ VTIHelper.print( sql );
+
+ PreparedStatement ps = conn.prepareStatement( sql );
+
+ ps.execute();
+ ps.close();
+ }
+ }
+
+ /**
+ * <p>
+ * Create a table name from a method name.
+ * </p>
+ *
+ */
+ private static String getTableName( Method method )
+ {
+ return VTIHelper.doubleQuote( method.getName() );
+ }
+
+ /**
+ * <p>
+ * Create a function name from a method name.
+ * </p>
+ *
+ */
+ private static String getFunctionName( Method method )
+ {
+ return VTIHelper.doubleQuote( method.getName() );
+ }
+
+ ///////////////////////////////////////////////////////////////////////////////////
+ //
+ // MANAGING THE SUBSCRIPTION CONTEXTS
+ //
+ ///////////////////////////////////////////////////////////////////////////////////
+
+ /**
+ * <p>
+ * Create a new subscription context for creating or refreshing a subscription.
+ * </p>
+ *
+ */
+ private static void createContext
+ ( String subscriptionClassName, String[] parameterValues, String connectionURL )
+ throws Exception
+ {
+ Class subscriptionClass = Class.forName( subscriptionClassName );
+ SubscriptionSignature subscriptionSignature = (SubscriptionSignature) subscriptionClass.getAnnotation( SubscriptionSignature.class );
+ HashMap<String, String> parameterMap = null;
+ String[] parameterNames = subscriptionSignature.parameters();
+
+ if ( parameterValues != null )
+ {
+ parameterMap = new HashMap<String, String>();
+ int count = parameterNames.length;
+ int actual = parameterValues.length;
+
+ if ( count != actual )
+ {
+ throw new SQLException( "Expected " + count + " parameters, but saw " + actual );
+ }
+
+ for ( int i = 0; i < count; i++ )
+ {
+ parameterMap.put( parameterNames[ i ], parameterValues[ i ] );
+ }
+ }
+
+ SubscriptionContext newContext = new SubscriptionContext( subscriptionSignature, parameterMap, connectionURL );
+ SubscriptionContext oldContext = getContext( subscriptionClassName, false );
+
+ if ( oldContext != null )
+ {
+ throw new SQLException( subscriptionClassName + " already in use. Try again later." );
+ }
+
+ _contexts.put( subscriptionClassName, newContext );
+ }
+
+ /**
+ * <p>
+ * Drop a subscription context.
+ * </p>
+ *
+ */
+ private static void dropContext
+ ( String subscriptionClassName )
+ {
+ _contexts.remove( subscriptionClassName );
+ }
+
+ /**
+ * <p>
+ * Get a subscription context.
+ * </p>
+ *
+ */
+ private static SubscriptionContext getContext
+ ( String subscriptionClassName, boolean shouldExist )
+ throws SQLException
+ {
+ SubscriptionContext context = _contexts.get( subscriptionClassName );
+
+ if ( shouldExist && (context == null) )
+ {
+ throw new SQLException
+ ( "Could not find execution context for " + subscriptionClassName + ". Maybe you are trying to invoke a snapshot table function outside of the refresh procedure?" );
+ }
+
+ return context;
+ }
+
+
+}
Propchange: db/derby/code/trunk/java/demo/vtis/java/org/apache/derbyDemo/vtis/snapshot/Subscription.java
------------------------------------------------------------------------------
svn:eol-style = native
Added: db/derby/code/trunk/java/demo/vtis/java/org/apache/derbyDemo/vtis/snapshot/SubscriptionSignature.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/demo/vtis/java/org/apache/derbyDemo/vtis/snapshot/SubscriptionSignature.java?rev=587652&view=auto
==============================================================================
--- db/derby/code/trunk/java/demo/vtis/java/org/apache/derbyDemo/vtis/snapshot/SubscriptionSignature.java (added)
+++ db/derby/code/trunk/java/demo/vtis/java/org/apache/derbyDemo/vtis/snapshot/SubscriptionSignature.java Tue Oct 23 13:54:05 2007
@@ -0,0 +1,57 @@
+/*
+
+Derby - Class org.apache.derbyDemo.vtis.snapshot.SubscriptionSignature
+
+Licensed to the Apache Software Foundation (ASF) under one or more
+contributor license agreements. See the NOTICE file distributed with
+this work for additional information regarding copyright ownership.
+The ASF licenses this file to You under the Apache License, Version 2.0
+(the "License"); you may not use this file except in compliance with
+the License. You may obtain a copy of the License at
+
+http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing, software
+distributed under the License is distributed on an "AS IS" BASIS,
+WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+See the License for the specific language governing permissions and
+limitations under the License.
+
+*/
+
+package org.apache.derbyDemo.vtis.snapshot;
+
+import java.lang.annotation.*;
+
+/**
+ * <p>
+ * This is an Annotation describing the details of a Subscription which are
+ * shared by all of its enclosed snapshots.
+ * </p>
+ *
+ */
+@Retention( value=RetentionPolicy.RUNTIME )
+public @interface SubscriptionSignature
+{
+ ///////////////////////////////////////////////////////////////////////////////////
+ //
+ // CONSTANTS
+ //
+ ///////////////////////////////////////////////////////////////////////////////////
+
+ ///////////////////////////////////////////////////////////////////////////////////
+ //
+ // BEHAVIOR
+ //
+ ///////////////////////////////////////////////////////////////////////////////////
+
+ /** The name of the JDBC driver used to access the external DB */
+ String jdbcDriverName();
+
+ /** The names of all the optional parameters to the refresh command */
+ String[] parameters();
+
+ /** Name to give to the refresh procedure */
+ String refreshProcedureName();
+
+}
Propchange: db/derby/code/trunk/java/demo/vtis/java/org/apache/derbyDemo/vtis/snapshot/SubscriptionSignature.java
------------------------------------------------------------------------------
svn:eol-style = native
Added: 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=587652&view=auto
==============================================================================
--- db/derby/code/trunk/java/demo/vtis/sql/demoFileVtis.sql (added)
+++ db/derby/code/trunk/java/demo/vtis/sql/demoFileVtis.sql Tue Oct 23 13:54:05 2007
@@ -0,0 +1,180 @@
+----------------------------------------------------------------------------------------
+--
+-- This script demonstrates how to declare and use several sample
+-- table functions.
+--
+-- Several of the function calls in this script assume that your
+-- Derby code client can be found at:
+--
+-- /opt/DerbyTrunk
+--
+-- If that is not the case, then you will need to adjust this
+-- script accordingly.
+--
+----------------------------------------------------------------------------------------
+
+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' );
+
+--
+-- 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 whihc have not been translated into french
+select m_english.messageID
+from table( propertyFileVTI( '/opt/DerbyTrunk/java/engine/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"
+;
Propchange: db/derby/code/trunk/java/demo/vtis/sql/demoFileVtis.sql
------------------------------------------------------------------------------
svn:eol-style = native
Added: db/derby/code/trunk/java/demo/vtis/sql/demoForeignDbmsVtis.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/demo/vtis/sql/demoForeignDbmsVtis.sql?rev=587652&view=auto
==============================================================================
--- db/derby/code/trunk/java/demo/vtis/sql/demoForeignDbmsVtis.sql (added)
+++ db/derby/code/trunk/java/demo/vtis/sql/demoForeignDbmsVtis.sql Tue Oct 23 13:54:05 2007
@@ -0,0 +1,157 @@
+----------------------------------------------------------------------------------------
+--
+-- This script demonstrates how to use VTIs to access data in foreign
+-- RDBMSes.
+--
+-- Several of the function calls in this script assume that MySQL
+-- is running on your machine, loaded with MySQL's sample "world" database.
+-- You will need to change the hard-coded connection URL which occurs
+-- throughout this script and which is used to connect to the "world"
+-- database:
+--
+-- jdbc:mysql://localhost/world?user=root&password=mysql-passwd
+--
+----------------------------------------------------------------------------------------
+
+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 registerQueryRowVTIs;
+
+drop procedure closeConnection;
+
+drop procedure createSubscription;
+drop procedure dropSubscription;
+
+drop table countryLanguage;
+
+--
+-- Recreate procedures
+--
+create procedure registerQueryRowVTIs
+( className varchar( 32672 ), connectionURL varchar( 32672 ) )
+language java
+parameter style java
+modifies sql data
+external name 'org.apache.derbyDemo.vtis.core.QueryVTIHelper.registerQueryRowVTIs'
+;
+create procedure closeConnection( connectionURL varchar( 32672 ) )
+language java
+parameter style java
+modifies sql data
+external name 'org.apache.derbyDemo.vtis.core.QueryVTIHelper.closeConnection'
+;
+create procedure createSubscription
+( subscriptionClassName varchar( 32672 ), connectionURL varchar( 32672 ) )
+language java
+parameter style java
+modifies sql data
+external name 'org.apache.derbyDemo.vtis.snapshot.Subscription.createSubscription'
+;
+create procedure dropSubscription
+( subscriptionClassName varchar( 32672 ) )
+language java
+parameter style java
+modifies sql data
+external name 'org.apache.derbyDemo.vtis.snapshot.Subscription.dropSubscription'
+;
+
+----------------------------------------------------------------------------------------
+--
+-- Declare the table functions.
+--
+----------------------------------------------------------------------------------------
+
+--
+-- Register the table functions in the VTIs class
+--
+call registerQueryRowVTIs( 'org.apache.derbyDemo.vtis.example.VTIs', 'jdbc:mysql://localhost/world?user=root&password=mysql-passwd' );
+
+----------------------------------------------------------------------------------------
+--
+-- External Database VTIs
+--
+----------------------------------------------------------------------------------------
+
+select s.*
+from table( "countryLanguage"( 'jdbc:mysql://localhost/world?user=root&password=mysql-passwd' ) ) s
+where "CountryCode" between 'E' and 'F'
+order by "CountryCode"
+;
+
+create table countryLanguage
+as select s.*
+from table( "countryLanguage"( 'jdbc:mysql://localhost/world?user=root&password=mysql-passwd' ) ) s
+with no data
+;
+
+insert into countryLanguage
+select s.*
+from table( "countryLanguage"( 'jdbc:mysql://localhost/world?user=root&password=mysql-passwd' ) ) s
+;
+
+select * from countryLanguage
+where "Percentage" > 75.0
+and "CountryCode" between 'E' and 'F'
+order by "CountryCode"
+;
+
+--
+-- Don't forget to clean up.
+--
+call closeConnection( 'jdbc:mysql://localhost/world?user=root&password=mysql-passwd' );
+
+----------------------------------------------------------------------------------------
+--
+-- Parameterized Subscription to Foreign Data
+--
+----------------------------------------------------------------------------------------
+
+call dropSubscription( 'org.apache.derbyDemo.vtis.example.WorldDBSnapshot' );
+
+call createSubscription
+(
+ 'org.apache.derbyDemo.vtis.example.WorldDBSnapshot',
+ 'jdbc:mysql://localhost/world?user=root&password=mysql-passwd'
+);
+
+-- now tear off a parameterized subscription:
+--
+-- all data related to cities with more than 9M people
+call refreshWorldDB
+(
+ 'org.apache.derbyDemo.vtis.example.WorldDBSnapshot',
+ 'jdbc:mysql://localhost/world?user=root&password=mysql-passwd',
+ '9000000', -- populationMin
+ '30000000' -- populationMax
+);
+
+--inspect the tear-off
+select * from "City";
+select * from "Country";
+select * from "CountryLanguage";
+
+-- now recalculate the subscription
+--
+-- all data related to cities in a narrower population range: 9-10M people
+call refreshWorldDB
+(
+ 'org.apache.derbyDemo.vtis.example.WorldDBSnapshot',
+ 'jdbc:mysql://localhost/world?user=root&password=mysql-passwd',
+ '9000000', -- populationMin
+ '10000000' -- populationMax
+);
+
+--inspect the tear-off
+select * from "City";
+select * from "Country";
+select * from "CountryLanguage";
Propchange: db/derby/code/trunk/java/demo/vtis/sql/demoForeignDbmsVtis.sql
------------------------------------------------------------------------------
svn:eol-style = native