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 2015/07/30 23:28:02 UTC
svn commit: r1693506 - in /db/derby/code/trunk: ./
java/build/org/apache/derbyBuild/lastgoodjarcontents/
java/optional/org/apache/derby/optional/api/
java/optional/org/apache/derby/optional/json/
java/testing/org/apache/derbyTesting/functionTests/tests...
Author: rhillegas
Date: Thu Jul 30 21:28:02 2015
New Revision: 1693506
URL: http://svn.apache.org/r1693506
Log:
DERBY-6825: Add SimpleJsonVTI and helper functions to the simpleJson optional tool; commit derby-6825-02-aa-vti.diff.
Added:
db/derby/code/trunk/java/optional/org/apache/derby/optional/api/SimpleJsonVTI.java (with props)
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/thermostatReadings.dat (with props)
Modified:
db/derby/code/trunk/build.xml
db/derby/code/trunk/java/build/org/apache/derbyBuild/lastgoodjarcontents/insane.derbyoptionaltools.jar.lastcontents
db/derby/code/trunk/java/build/org/apache/derbyBuild/lastgoodjarcontents/sane.derbyoptionaltools.jar.lastcontents
db/derby/code/trunk/java/optional/org/apache/derby/optional/api/SimpleJsonUtils.java
db/derby/code/trunk/java/optional/org/apache/derby/optional/json/SimpleJsonTool.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SimpleJsonTest.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/util/PrivilegedFileOpsForTests.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/util/derby_tests.policy
db/derby/code/trunk/tools/javadoc/publishedapi.ant
Modified: db/derby/code/trunk/build.xml
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/build.xml?rev=1693506&r1=1693505&r2=1693506&view=diff
==============================================================================
--- db/derby/code/trunk/build.xml (original)
+++ db/derby/code/trunk/build.xml Thu Jul 30 21:28:02 2015
@@ -684,7 +684,8 @@
</target>
<!-- Run the RefreshJarDriftTest -->
- <target name="refreshjardriftcheck">
+ <target name="refreshjardriftcheck"
+ description="Fix discrepancies between the actual and expected contents of the Derby jar files.">
<java
fork="true"
classname="org.apache.derbyBuild.JarDriftTest"
@@ -695,7 +696,8 @@
</target>
<!-- Run the JarDriftTest -->
- <target name="jardriftcheck">
+ <target name="jardriftcheck"
+ description="Report discrepancies between the actual and expected contents of the Derby jar files.">
<java
fork="true"
classname="org.apache.derbyBuild.JarDriftTest"
Modified: db/derby/code/trunk/java/build/org/apache/derbyBuild/lastgoodjarcontents/insane.derbyoptionaltools.jar.lastcontents
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/build/org/apache/derbyBuild/lastgoodjarcontents/insane.derbyoptionaltools.jar.lastcontents?rev=1693506&r1=1693505&r2=1693506&view=diff
==============================================================================
--- db/derby/code/trunk/java/build/org/apache/derbyBuild/lastgoodjarcontents/insane.derbyoptionaltools.jar.lastcontents (original)
+++ db/derby/code/trunk/java/build/org/apache/derbyBuild/lastgoodjarcontents/insane.derbyoptionaltools.jar.lastcontents Thu Jul 30 21:28:02 2015
@@ -1,6 +1,7 @@
org.apache.derby.optional.api.LuceneIndexDescriptor.class
org.apache.derby.optional.api.LuceneUtils.class
org.apache.derby.optional.api.SimpleJsonUtils.class
+org.apache.derby.optional.api.SimpleJsonVTI.class
org.apache.derby.optional.json.SimpleJsonTool.class
org.apache.derby.optional.lucene.DerbyIndexInput.class
org.apache.derby.optional.lucene.DerbyIndexOutput.class
Modified: db/derby/code/trunk/java/build/org/apache/derbyBuild/lastgoodjarcontents/sane.derbyoptionaltools.jar.lastcontents
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/build/org/apache/derbyBuild/lastgoodjarcontents/sane.derbyoptionaltools.jar.lastcontents?rev=1693506&r1=1693505&r2=1693506&view=diff
==============================================================================
--- db/derby/code/trunk/java/build/org/apache/derbyBuild/lastgoodjarcontents/sane.derbyoptionaltools.jar.lastcontents (original)
+++ db/derby/code/trunk/java/build/org/apache/derbyBuild/lastgoodjarcontents/sane.derbyoptionaltools.jar.lastcontents Thu Jul 30 21:28:02 2015
@@ -1,6 +1,7 @@
org.apache.derby.optional.api.LuceneIndexDescriptor.class
org.apache.derby.optional.api.LuceneUtils.class
org.apache.derby.optional.api.SimpleJsonUtils.class
+org.apache.derby.optional.api.SimpleJsonVTI.class
org.apache.derby.optional.json.SimpleJsonTool.class
org.apache.derby.optional.lucene.DerbyIndexInput.class
org.apache.derby.optional.lucene.DerbyIndexOutput.class
Modified: db/derby/code/trunk/java/optional/org/apache/derby/optional/api/SimpleJsonUtils.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/optional/org/apache/derby/optional/api/SimpleJsonUtils.java?rev=1693506&r1=1693505&r2=1693506&view=diff
==============================================================================
--- db/derby/code/trunk/java/optional/org/apache/derby/optional/api/SimpleJsonUtils.java (original)
+++ db/derby/code/trunk/java/optional/org/apache/derby/optional/api/SimpleJsonUtils.java Thu Jul 30 21:28:02 2015
@@ -21,6 +21,18 @@
package org.apache.derby.optional.api;
+import java.io.FileInputStream;
+import java.io.InputStream;
+import java.io.InputStreamReader;
+import java.io.IOException;
+import java.io.Reader;
+import java.io.StringReader;
+import java.io.UnsupportedEncodingException;
+import java.net.MalformedURLException;
+import java.net.URL;
+import java.security.AccessController;
+import java.security.PrivilegedActionException;
+import java.security.PrivilegedExceptionAction;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
@@ -32,6 +44,7 @@ import java.sql.SQLException;
import org.json.simple.JSONArray;
import org.json.simple.JSONObject;
+import org.json.simple.parser.JSONParser;
import org.apache.derby.iapi.util.StringUtil;
@@ -117,6 +130,111 @@ public abstract class SimpleJsonUtils
return result;
}
+ /**
+ * Construct a JSONArray from a Reader.
+ */
+ public static JSONArray readArray( Reader reader )
+ throws SQLException
+ {
+ JSONParser parser = new JSONParser();
+
+ Object obj = null;
+ try {
+ obj = parser.parse( reader );
+ }
+ catch( Throwable t) { throw wrap( t ); }
+
+ if ( (obj == null) || !(obj instanceof JSONArray) )
+ {
+ throw new SQLException( "Document is not a JSON array." );
+ }
+
+ return (JSONArray) obj;
+ }
+
+ /**
+ * <p>
+ * SQL FUNCTION to convert a JSON document string into a JSONArray.
+ * This function is registered by the simpleJson optional tool.
+ * </p>
+ */
+ public static JSONArray readArrayFromString( String document )
+ throws SQLException
+ {
+ if ( document == null ) { document = ""; }
+
+ return readArray( new StringReader( document ) );
+ }
+
+ /**
+ * Read a JSONArray from an InputStream.
+ */
+ public static JSONArray readArrayFromStream
+ ( InputStream inputStream, String characterSetName )
+ throws SQLException
+ {
+ try {
+ return readArray( new InputStreamReader( inputStream, characterSetName ) );
+ }
+ catch (UnsupportedEncodingException uee) { throw wrap( uee ); }
+ }
+
+ /**
+ * SQL FUNCTION to read a JSONArray from a File. This function
+ * is registered by the simpleJson optional tool.
+ */
+ public static JSONArray readArrayFromFile
+ ( String fileName, String characterSetName )
+ throws SQLException
+ {
+ FileInputStream fis = null;
+ final String name_of_file = fileName;
+
+ try {
+ fis = AccessController.doPrivileged(
+ new PrivilegedExceptionAction<FileInputStream>()
+ {
+ public FileInputStream run() throws IOException
+ {
+ return new FileInputStream( name_of_file );
+ }
+ }
+ );
+ }
+ catch (PrivilegedActionException pae) { throw wrap( pae ); }
+
+ return readArrayFromStream( fis, characterSetName );
+ }
+
+ /**
+ * SQL FUNCTION to read a JSONArray from an URL address.
+ * This function is registered by the simpleJson optional tool.
+ */
+ public static JSONArray readArrayFromURL
+ ( String urlString, String characterSetName )
+ throws SQLException
+ {
+ InputStream inputStream = null;
+ final String url_string = urlString;
+
+ try {
+ inputStream = AccessController.doPrivileged(
+ new PrivilegedExceptionAction<InputStream>()
+ {
+ public InputStream run() throws IOException, MalformedURLException
+ {
+ URL url = new URL( url_string );
+ return url.openStream();
+ }
+ }
+ );
+ }
+ catch (PrivilegedActionException pae) { throw wrap( pae ); }
+
+ return readArrayFromStream( inputStream, characterSetName );
+ }
+
+
/////////////////////////////////////////////////////////////////
//
// MINIONS
@@ -181,4 +299,21 @@ public abstract class SimpleJsonUtils
{
return DriverManager.getConnection( "jdbc:default:connection" );
}
+
+ /**
+ * <p>
+ * Wrap an exception in a SQLException.
+ * </p>
+ */
+ static SQLException wrap( Throwable t )
+ {
+ String message = t.getMessage();
+ if ( (message == null) || (message.length() == 0) )
+ {
+ message = t.toString();
+ }
+
+ return new SQLException( message, t );
+ }
+
}
Added: db/derby/code/trunk/java/optional/org/apache/derby/optional/api/SimpleJsonVTI.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/optional/org/apache/derby/optional/api/SimpleJsonVTI.java?rev=1693506&view=auto
==============================================================================
--- db/derby/code/trunk/java/optional/org/apache/derby/optional/api/SimpleJsonVTI.java (added)
+++ db/derby/code/trunk/java/optional/org/apache/derby/optional/api/SimpleJsonVTI.java Thu Jul 30 21:28:02 2015
@@ -0,0 +1,398 @@
+/*
+
+Derby - Class org.apache.derby.optional.api.SimpleJsonVTI
+
+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.derby.optional.api;
+
+import java.math.BigDecimal;
+import java.net.URL;
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.SQLException;
+import java.sql.SQLWarning;
+
+import org.json.simple.JSONArray;
+import org.json.simple.JSONObject;
+
+import org.apache.derby.vti.VTITemplate;
+
+/**
+ * <p>
+ * This is a table function which turns a JSON array into a relational
+ * ResultSet. This table function relies on the JSON.simple JSONArray class
+ * found at https://code.google.com/p/json-simple/.
+ * Each object in the array is turned into a row.
+ * The shape of the row is declared by the CREATE FUNCTION ddl
+ * and the shape corresponds to the key names found in the row objects.
+ * Provided that the values in those objects have the expected type,
+ * the following ResultSet accessors can be called:
+ * </p>
+ *
+ * <ul>
+ * <li>getString()</li>
+ * <li>getBoolean()</li>
+ * <li>getByte()</li>
+ * <li>getShort()</li>
+ * <li>getInt()</li>
+ * <li>getLong()</li>
+ * <li>getFloat()</li>
+ * <li>getDouble()</li>
+ * <li>getObject()</li>
+ * <li>getBigDecimal()</li>
+ * </ul>
+ *
+ * <p>
+ * This table function relies on the JSONArray type loaded by the simpleJson optional
+ * tool. This table function can be combined with other JSONArray-creating
+ * functions provided by that tool.
+ * </p>
+ *
+ * <p>
+ * Here's an example of how to use this VTI on a JSON document read across
+ * the network using the readArrayFromURL function provided by the simpleJson tool:
+ * </p>
+ *
+ * <pre>
+ * call syscs_util.syscs_register_tool( 'simpleJson', true );
+ *
+ * create function thermostatReadings( jsonDocument JSONArray )
+ * returns table
+ * (
+ * "id" int,
+ * "temperature" float,
+ * "fanOn" boolean
+ * )
+ * language java parameter style derby_jdbc_result_set contains sql
+ * external name 'org.apache.derby.optional.api.SimpleJsonVTI.readArray';
+ *
+ * select * from table
+ * (
+ * thermostatReadings
+ * (
+ * readArrayFromURL( 'https://thermostat.feed.org', 'UTF-8' )
+ * )
+ * ) t;
+ * </pre>
+ *
+ * <p>
+ * That returns a table like this:
+ * </p>
+ *
+ * <pre>
+ * id |temperature |fanOn
+ * ------------------------------------------
+ * 1 |70.3 |true
+ * 2 |65.5 |false
+ * </pre>
+ *
+ * <p>
+ * Here's an example of how to use this VTI on a JSON document string
+ * with the assistance of the readArrayFromString function provided by the simpleJson tool:
+ * </p>
+ *
+ * <pre>
+ * select * from table
+ * (
+ * thermostatReadings
+ * (
+ * readArrayFromString
+ * (
+ * '[ { "id": 1, "temperature": 70.3, "fanOn": true }, { "id": 2, "temperature": 65.5, "fanOn": false } ]'
+ * )
+ * )
+ * ) t;
+ * </pre>
+ */
+public class SimpleJsonVTI extends VTITemplate
+{
+ ////////////////////////////////////////////////////////////////////////
+ //
+ // CONSTANTS
+ //
+ ////////////////////////////////////////////////////////////////////////
+
+ ////////////////////////////////////////////////////////////////////////
+ //
+ // STATE
+ //
+ ////////////////////////////////////////////////////////////////////////
+
+ private JSONArray _topArray = null;
+ private int _nextIdx = 0;
+ private JSONObject _currentRow = null;
+ private boolean _wasNull = true;
+
+ private Connection _connection;
+ private VTITemplate.ColumnDescriptor[] _returnColumns;
+ private SQLWarning _warning = null;
+
+ ////////////////////////////////////////////////////////////////////////
+ //
+ // CONSTRUCTOR
+ //
+ ////////////////////////////////////////////////////////////////////////
+
+ /**
+ * Construct from a JSONArray object.
+ */
+ private SimpleJsonVTI( JSONArray array )
+ throws SQLException
+ {
+ _topArray = (JSONArray) array;
+ _connection = DriverManager.getConnection( "jdbc:default:connection" );
+ }
+
+ ////////////////////////////////////////////////////////////////////////
+ //
+ // TABLE FUNCTIONS (to be bound by CREATE FUNCTION ddl)
+ //
+ ////////////////////////////////////////////////////////////////////////
+
+ /**
+ * <p>
+ * Create a SimpleJsonVTI from a JSONArray object.
+ * </p>
+ */
+ public static SimpleJsonVTI readArray( JSONArray array )
+ throws SQLException
+ {
+ return new SimpleJsonVTI( array );
+ }
+
+ ////////////////////////////////////////////////////////////////////////
+ //
+ // ResultSet BEHAVIOR
+ //
+ ////////////////////////////////////////////////////////////////////////
+
+ public void close() throws SQLException
+ {
+ _connection = null;
+ _returnColumns = null;
+ _topArray = null;
+ _currentRow = null;
+ }
+
+ public boolean next() throws SQLException
+ {
+ if ( _topArray == null ) { return false; }
+ if ( _nextIdx >= _topArray.size() ) { return false; }
+
+ if ( _returnColumns == null ) { _returnColumns = getReturnTableSignature( _connection ); }
+
+ Object obj = _topArray.get( _nextIdx );
+
+ if ( (obj == null) || !(obj instanceof JSONObject) )
+ {
+ _currentRow = null;
+ String cellType = (obj == null) ? "NULL" : obj.getClass().getName();
+ addWarning( "Row " + _nextIdx + " is not a JSON object. It is a " + cellType );
+ }
+ else
+ {
+ _currentRow = (JSONObject) obj;
+ }
+
+ _nextIdx++;
+ return true;
+ }
+
+ public boolean wasNull() { return _wasNull; }
+
+ public SQLWarning getWarnings() throws SQLException { return null; }
+ public void clearWarnings() throws SQLException { throw notImplemented( "clearWarnings" ); }
+
+ ////////////////////////////////////////////////////////////////////////
+ //
+ // TYPE-SPECIFIC ACCESSORS
+ //
+ ////////////////////////////////////////////////////////////////////////
+
+ public String getString(int columnIndex) throws SQLException
+ {
+ Object obj = getColumn( columnIndex );
+ if ( _wasNull ) { return null; }
+
+ return obj.toString();
+ }
+
+ public boolean getBoolean(int columnIndex) throws SQLException
+ {
+ Object obj = getColumn( columnIndex );
+ if ( nullOrWrongType( Boolean.class, columnIndex, obj ) ) { return false; }
+
+ return ((Boolean) obj).booleanValue();
+ }
+
+ public byte getByte(int columnIndex) throws SQLException
+ {
+ Number number = getNumber( columnIndex );
+ if ( _wasNull ) { return (byte) 0; }
+
+ return number.byteValue();
+ }
+
+ public short getShort(int columnIndex) throws SQLException
+ {
+ Number number = getNumber( columnIndex );
+ if ( _wasNull ) { return (byte) 0; }
+
+ return number.shortValue();
+ }
+
+ public int getInt(int columnIndex) throws SQLException
+ {
+ Number number = getNumber( columnIndex );
+ if ( _wasNull ) { return (byte) 0; }
+
+ return number.intValue();
+ }
+
+ public long getLong(int columnIndex) throws SQLException
+ {
+ Number number = getNumber( columnIndex );
+ if ( _wasNull ) { return (byte) 0; }
+
+ return number.longValue();
+ }
+
+ public float getFloat(int columnIndex) throws SQLException
+ {
+ Number number = getNumber( columnIndex );
+ if ( _wasNull ) { return (byte) 0; }
+
+ return number.floatValue();
+ }
+
+ public double getDouble(int columnIndex) throws SQLException
+ {
+ Number number = getNumber( columnIndex );
+ if ( _wasNull ) { return (byte) 0; }
+
+ return number.doubleValue();
+ }
+
+ public Object getObject(int columnIndex) throws SQLException
+ {
+ return getColumn( columnIndex );
+ }
+
+ public BigDecimal getBigDecimal(int columnIndex) throws SQLException
+ {
+ String stringValue = getString( columnIndex );
+ if ( _wasNull ) { return null; }
+
+ try {
+ return new BigDecimal( stringValue );
+ }
+ catch (Throwable t) { throw SimpleJsonUtils.wrap( t ); }
+ }
+
+ ////////////////////////////////////////////////////////////////////////
+ //
+ // MINIONS
+ //
+ ////////////////////////////////////////////////////////////////////////
+
+ /**
+ * <p>
+ * Get the i-th (1-based) column as a Number.
+ * </p>
+ */
+ private Number getNumber( int idx )
+ throws SQLException
+ {
+ Object obj = getColumn( idx );
+
+ if ( nullOrWrongType( Number.class, idx, obj ) ) { return null; }
+
+ return (Number) obj;
+ }
+
+ /**
+ * <p>
+ * Get the i-th (1-based) column as an object.
+ * </p>
+ */
+ private Object getColumn( int idx )
+ throws SQLException
+ {
+ if ( (idx < 1) || (idx > _returnColumns.length) )
+ {
+ throw new SQLException( "Column index " + idx + " is out of bounds." );
+ }
+
+ if ( _currentRow == null )
+ {
+ _wasNull = true;
+ return null;
+ }
+
+ Object value = _currentRow.get( _returnColumns[ idx - 1 ].columnName );
+
+ _wasNull = (value == null);
+
+ return value;
+ }
+
+ /**
+ * <p>
+ * Add a "wrong type" warning and return true if the object
+ * has the wrong type. Return true if the object is null.
+ * Otherwise, return false.
+ * </p>
+ */
+ private boolean nullOrWrongType( Class correctType, int columnIdx, Object obj )
+ {
+ if ( _wasNull ) { return true; }
+
+ if ( !correctType.isInstance( obj ) )
+ {
+ String desiredType = correctType.getName();
+ String valueType = obj.getClass().getName();
+ addWarning
+ (
+ "Column " + columnIdx +
+ " in row " + _nextIdx +
+ " is not a " + desiredType +
+ ". It is a " + valueType + "."
+ );
+
+ _wasNull = true;
+ return true;
+ }
+
+ return false;
+ }
+
+ /**
+ * <p>
+ * Add a warning to the connection.
+ * </p>
+ */
+ private void addWarning( String warningText )
+ {
+ SQLWarning warning = new SQLWarning( warningText );
+
+ if ( _warning == null ) { _warning = warning; }
+ else { _warning.setNextWarning( warning ); }
+ }
+
+}
Propchange: db/derby/code/trunk/java/optional/org/apache/derby/optional/api/SimpleJsonVTI.java
------------------------------------------------------------------------------
svn:eol-style = native
Modified: db/derby/code/trunk/java/optional/org/apache/derby/optional/json/SimpleJsonTool.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/optional/org/apache/derby/optional/json/SimpleJsonTool.java?rev=1693506&r1=1693505&r2=1693506&view=diff
==============================================================================
--- db/derby/code/trunk/java/optional/org/apache/derby/optional/json/SimpleJsonTool.java (original)
+++ db/derby/code/trunk/java/optional/org/apache/derby/optional/json/SimpleJsonTool.java Thu Jul 30 21:28:02 2015
@@ -38,12 +38,23 @@ import org.apache.derby.optional.api.Sim
/**
* <p>
- * OptionalTool which adds a function for turning a query result
- * into a JSON array. Each cell in the array is a row. Each row
- * has key/value pairs for all columns returned by the query.
- * This tool binds a user-defined type to the simple-json JSONArray
- * class.
+ * OptionalTool which adds support types and functions for
+ * using the JSON.simple toolkit at https://code.google.com/p/json-simple/.
+ * Creates the following schema objects in the current schema:
* </p>
+ *
+ * <ul>
+ * <li><b>JSONArray</b> - A UDT bound to the JSON.simple JSONArray class.</li>
+ * <li><b>toJSON</b> - A function for packaging up query results as a
+ * JSONArray. Each cell in the array is a row. Each row
+ * has key/value pairs for all columns returned by the query.</li>
+ * <li><b>readArrayFromString</b> - A function which turns a JSON document
+ * string into a JSONArray.</li>
+ * <li><b>readArrayFromFile</b> - A function which reads a file containing a
+ * JSON document and turns it into a JSONArray.</li>
+ * <li><b>readArrayFromURL</b> - A function which reads a JSON document from an
+ * URL address and turns it into a JSONArray.</li>
+ * </ul>
*/
public class SimpleJsonTool implements OptionalTool
{
@@ -53,12 +64,68 @@ public class SimpleJsonTool implement
//
////////////////////////////////////////////////////////////////////////
+ private FunctionDescriptor[] _functionDescriptors =
+ {
+ new FunctionDescriptor
+ (
+ "toJSON",
+ "create function toJSON" +
+ "\n(" +
+ "\n\tqueryString varchar( 32672 )," +
+ "\n\tqueryArgs varchar( 32672 ) ..." +
+ "\n)\nreturns JSONArray\n" +
+ "\nlanguage java parameter style derby reads sql data" +
+ "\nexternal name 'org.apache.derby.optional.json.SimpleJsonTool.toJSON'\n"
+ ),
+
+ new FunctionDescriptor
+ (
+ "readArrayFromString",
+ "create function readArrayFromString( document varchar( 32672 ) )\n" +
+ "returns JSONArray\n" +
+ "language java parameter style java contains sql\n" +
+ "external name 'org.apache.derby.optional.api.SimpleJsonUtils.readArrayFromString'\n"
+ ),
+
+ new FunctionDescriptor
+ (
+ "readArrayFromFile",
+ "create function readArrayFromFile\n" +
+ "( fileName varchar( 32672 ), characterSetName varchar( 100 ) )\n" +
+ "returns JSONArray\n" +
+ "language java parameter style java contains sql\n" +
+ "external name 'org.apache.derby.optional.api.SimpleJsonUtils.readArrayFromFile'\n"
+ ),
+
+ new FunctionDescriptor
+ (
+ "readArrayFromURL",
+ "create function readArrayFromURL\n" +
+ "( urlString varchar( 32672 ), characterSetName varchar( 100 ) )\n" +
+ "returns JSONArray\n" +
+ "language java parameter style java contains sql\n" +
+ "external name 'org.apache.derby.optional.api.SimpleJsonUtils.readArrayFromURL'\n"
+ ),
+ };
+
////////////////////////////////////////////////////////////////////////
//
// STATE
//
////////////////////////////////////////////////////////////////////////
+ public static final class FunctionDescriptor
+ {
+ public final String functionName;
+ public final String creationDDL;
+
+ public FunctionDescriptor( String functionName, String creationDDL )
+ {
+ this.functionName = functionName;
+ this.creationDDL = creationDDL;
+ }
+ }
+
////////////////////////////////////////////////////////////////////////
//
// CONSTRUCTOR
@@ -91,7 +158,7 @@ public class SimpleJsonTool implement
Connection derbyConn = getDerbyConnection();
createUDT( derbyConn );
- createFunction( derbyConn );
+ createFunctions( derbyConn );
}
/**
@@ -110,7 +177,7 @@ public class SimpleJsonTool implement
Connection derbyConn = getDerbyConnection();
- dropFunction( derbyConn );
+ dropFunctions( derbyConn );
dropUDT( derbyConn );
}
@@ -192,34 +259,30 @@ public class SimpleJsonTool implement
executeDDL( derbyConn, createString );
}
- private void createFunction
+ private void createFunctions
(
Connection derbyConn
)
throws SQLException
{
- String createString =
- "create function toJSON" +
- "\n(" +
- "\n\tqueryString varchar( 32672 )," +
- "\n\tqueryArgs varchar( 32672 ) ..." +
- "\n)\nreturns JSONArray\n" +
- "\nlanguage java parameter style derby reads sql data" +
- "\nexternal name 'org.apache.derby.optional.json.SimpleJsonTool.toJSON'";
-
- executeDDL( derbyConn, createString );
+ for ( FunctionDescriptor desc : _functionDescriptors )
+ {
+ executeDDL( derbyConn, desc.creationDDL );
+ }
}
- private void dropFunction
+ private void dropFunctions
(
Connection derbyConn
)
throws SQLException
{
- String createString =
- "drop function toJSON";
-
- executeDDL( derbyConn, createString );
+ for ( FunctionDescriptor desc : _functionDescriptors )
+ {
+ String dropString = "drop function " + desc.functionName;
+
+ executeDDL( derbyConn, dropString );
+ }
}
////////////////////////////////////////////////////////////////////////
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SimpleJsonTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SimpleJsonTest.java?rev=1693506&r1=1693505&r2=1693506&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SimpleJsonTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SimpleJsonTest.java Thu Jul 30 21:28:02 2015
@@ -20,14 +20,19 @@
*/
package org.apache.derbyTesting.functionTests.tests.lang;
+import java.io.File;
import java.sql.Connection;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
import java.util.Arrays;
import junit.framework.Test;
import org.apache.derbyTesting.junit.BaseJDBCTestCase;
import org.apache.derbyTesting.junit.BaseTestSuite;
import org.apache.derbyTesting.junit.JDBC;
+import org.apache.derbyTesting.junit.SupportFilesSetup;
import org.apache.derbyTesting.junit.TestConfiguration;
+import org.apache.derbyTesting.functionTests.util.PrivilegedFileOpsForTests;
import org.json.simple.JSONArray;
import org.json.simple.JSONObject;
@@ -49,6 +54,21 @@ public class SimpleJsonTest extends Base
private static final String TAB = " ";
private static final String USER_ERROR = "38000";
+ private static final String THERMOSTAT_READINGS =
+ "[\n" +
+ " {\n" +
+ " \"id\": 1,\n" +
+ " \"temperature\": 70.3,\n" +
+ " \"fanOn\": true\n" +
+ " },\n" +
+ " {\n" +
+ " \"id\": 2,\n" +
+ " \"temperature\": 65.5,\n" +
+ " \"fanOn\": false\n" +
+ " }\n" +
+ "]";
+
+
///////////////////////////////////////////////////////////////////////////////////
//
// CONSTRUCTORS
@@ -71,7 +91,14 @@ public class SimpleJsonTest extends Base
suite.addTest( TestConfiguration.defaultSuite( SimpleJsonTest.class ) );
- return suite;
+ return new SupportFilesSetup
+ (
+ suite,
+ new String[]
+ {
+ "functionTests/tests/lang/thermostatReadings.dat",
+ }
+ );
}
///////////////////////////////////////////////////////////////////////////////////
@@ -499,6 +526,94 @@ public class SimpleJsonTest extends Base
goodStatement( conn, "call syscs_util.syscs_register_tool( 'simpleJson', false )" );
}
+ ///////////////////////////////////////////////////////////////////////////////////
+
+ /**
+ * <p>
+ * Test the SimpleJsonVTI.
+ * </p>
+ */
+ public void testVTI_004() throws Exception
+ {
+ Connection conn = getConnection();
+
+ goodStatement( conn, "call syscs_util.syscs_register_tool( 'simpleJson', true )" );
+
+ // declare a table function for reading a string
+ goodStatement
+ (
+ conn,
+ "create function thermostatReadings( jsonDocument JSONArray )\n" +
+ "returns table\n" +
+ "(\n" +
+ "\"id\" int,\n" +
+ "\"temperature\" float,\n" +
+ "\"fanOn\" boolean\n" +
+ ")\n" +
+ "language java parameter style derby_jdbc_result_set contains sql\n" +
+ "external name 'org.apache.derby.optional.api.SimpleJsonVTI.readArray'\n"
+ );
+
+ PreparedStatement ps;
+ ResultSet rs;
+
+ // turn a JSON document string into a ResultSet
+ ps = conn.prepareStatement
+ (
+ "select * from table\n" +
+ "( thermostatReadings( readArrayFromString(?) ) ) t order by \"id\""
+ );
+ ps.setString( 1, THERMOSTAT_READINGS );
+ rs = ps.executeQuery();
+ assertResults
+ (
+ rs,
+ new String[][]
+ {
+ { "1", "70.3", "true" },
+ { "2", "65.5", "false" }
+ },
+ true
+ );
+ rs.close();
+ ps.close();
+
+ // make a ResultSet out of a file containing JSON text
+ File inputFile = SupportFilesSetup.getReadOnly( "thermostatReadings.dat" );
+ String[][] fileReadings = new String[][]
+ {
+ { "1", "70.3", "true" },
+ { "2", "65.5", "false" },
+ { "3", "60.5", "false" },
+ };
+ ps = conn.prepareStatement
+ (
+ "select * from table\n" +
+ "( thermostatReadings( readArrayFromFile( ?, 'UTF-8' ) ) ) t order by \"id\""
+ );
+ ps.setString( 1, PrivilegedFileOpsForTests.getAbsolutePath( inputFile ) );
+ rs = ps.executeQuery();
+ assertResults(rs, fileReadings, true );
+ rs.close();
+ ps.close();
+
+ // make a ResultSet out of an URL which points to a file containing JSON text
+ ps = conn.prepareStatement
+ (
+ "select * from table\n" +
+ "( thermostatReadings( readArrayFromURL( ?, 'UTF-8' ) ) ) t order by \"id\""
+ );
+ String inputFileURL = PrivilegedFileOpsForTests.toURI(inputFile ).toURL().toString();
+ ps.setString( 1, inputFileURL);
+ rs = ps.executeQuery();
+ assertResults(rs, fileReadings, true );
+ rs.close();
+ ps.close();
+
+ goodStatement( conn, "drop function thermostatReadings" );
+ goodStatement( conn, "call syscs_util.syscs_register_tool( 'simpleJson', false )" );
+ }
+
///////////////////////////////////////////////////////////////////////////////////
//
// FUNCTIONS
Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/thermostatReadings.dat
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/thermostatReadings.dat?rev=1693506&view=auto
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/thermostatReadings.dat (added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/thermostatReadings.dat Thu Jul 30 21:28:02 2015
@@ -0,0 +1,5 @@
+[
+ { "id": 1, "temperature": 70.3, "fanOn": true },
+ { "id": 2, "temperature": 65.5, "fanOn": false },
+ { "id": 3, "temperature": 60.5, "fanOn": false },
+]
Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/thermostatReadings.dat
------------------------------------------------------------------------------
svn:eol-style = native
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/util/PrivilegedFileOpsForTests.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/util/PrivilegedFileOpsForTests.java?rev=1693506&r1=1693505&r2=1693506&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/util/PrivilegedFileOpsForTests.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/util/PrivilegedFileOpsForTests.java Thu Jul 30 21:28:02 2015
@@ -29,6 +29,7 @@ import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
+import java.net.URI;
import java.security.AccessController;
import java.security.PrivilegedAction;
import java.security.PrivilegedActionException;
@@ -90,6 +91,27 @@ public class PrivilegedFileOpsForTests {
}});
}
+ /**
+ * Get the URI for a file.
+ *
+ * @param file File to be queried
+ * @return Its URI
+ * @throws SecurityException if the test lacks the required permissions to access the file,
+ *
+ * @see File#getAbsolutePath
+ */
+ public static URI toURI(final File file)
+ throws SecurityException {
+ if (file == null) {
+ throw new IllegalArgumentException("file cannot be <null>");
+ }
+ return AccessController.doPrivileged(
+ new PrivilegedAction<URI>() {
+ public URI run() throws SecurityException {
+ return file.toURI();
+ }});
+ }
+
/**
* Create a temporary file.
*
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/util/derby_tests.policy
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/util/derby_tests.policy?rev=1693506&r1=1693505&r2=1693506&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/util/derby_tests.policy (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/util/derby_tests.policy Thu Jul 30 21:28:02 2015
@@ -235,6 +235,16 @@ grant codeBase "${derbyTesting.codejar}d
};
//
+// Permissions for the optional tools (derbyoptionaltools.jar)
+//
+grant codeBase "${derbyTesting.codejar}derbyoptionaltools.jar" {
+
+ // Needed by SimpleJsonTest
+ permission java.io.FilePermission "${user.dir}${/}extin${/}-", "read";
+
+};
+
+//
// Permissions for the tests (derbyTesting.jar)
// We are liberal here, it's not a goal to make the test harness
// or tests secure.
Modified: db/derby/code/trunk/tools/javadoc/publishedapi.ant
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/tools/javadoc/publishedapi.ant?rev=1693506&r1=1693505&r2=1693506&view=diff
==============================================================================
--- db/derby/code/trunk/tools/javadoc/publishedapi.ant (original)
+++ db/derby/code/trunk/tools/javadoc/publishedapi.ant Thu Jul 30 21:28:02 2015
@@ -82,4 +82,5 @@ org/apache/derby/security/SystemPermissi
org/apache/derby/optional/api/LuceneIndexDescriptor.java
org/apache/derby/optional/api/LuceneUtils.java
org/apache/derby/optional/api/SimpleJsonUtils.java
+org/apache/derby/optional/api/SimpleJsonVTI.java