db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From rhille...@apache.org
Subject 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...
Date Tue, 23 Oct 2007 20:54:07 GMT
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



Mime
View raw message