[
https://issues.apache.org/jira/browse/DERBY-6825?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Rick Hillegas updated DERBY-6825:
---------------------------------
Attachment: derby-6825-01-aa-optionalTool.diff
Attaching derby-6825-01-aa-optionalTool.diff. This patch adds a new Derby
optional tool: simpleJson. This tool creates a UDT for the json-simple
JSONArray type. The tool also creates a function (toJSON()), which returns
query results packed into a JSONArray. The toJSON() function has this
signature...
{noformat}
create function toJSON
(
queryString varchar( 32672 ),
queryArgs varchar( 32672 ) ...
)
returns JSONArray
language java parameter style derby reads sql data
external name 'org.apache.derby.optional.json.SimpleJsonTool.toJSON'
{noformat}
...where queryString is the text of a query and queryArgs are optional values
to be plugged into the query's ? parameters.
Here is a sample script showing the tool in action:
{noformat}
connect 'jdbc:derby:memory:db;create=true';
-- load the simpleJson tool
call syscs_util.syscs_register_tool( 'simpleJson', true );
create table thermostatReadings
(
"id" int,
"temperature" double,
"fanOn" boolean,
"sampleTime" timestamp,
primary key( "id", "sampleTime" )
);
insert into thermostatReadings values
( 1, 65.5, false, timestamp( '2015-07-08 04:03:20') ),
( 1, 70.1, true, timestamp( '2015-07-08 13:03:20') ),
( 2, 64.5, false, timestamp( '2015-07-08 03:03:20') ),
( 2, 72.1, true, timestamp( '2015-07-08 16:03:20') )
;
-- create a function to pretty print a JSONArray
create function prettyPrint( doc JSONArray ) returns varchar( 32672 )
language java parameter style java no sql
external name
'org.apache.derbyTesting.functionTests.tests.lang.SimpleJsonTest.prettyPrint';
-- now pack a ResultSet into a JSON document
MAXIMUMDISPLAYWIDTH 600;
values prettyPrint( toJSON( 'select * from thermostatReadings order by "id",
"sampleTime"' ) );
drop function prettyPrint;
-- unload the simpleJson tool
call syscs_util.syscs_register_tool( 'simpleJson', false );
{noformat}
The result of the pretty-printed query is...
{noformat}
[
{
"fanOn" : false,
"id" : 1,
"sampleTime" : "2015-07-08 04:03:20.0",
"temperature" : 65.5
},
{
"fanOn" : true,
"id" : 1,
"sampleTime" : "2015-07-08 13:03:20.0",
"temperature" : 70.1
},
{
"fanOn" : false,
"id" : 2,
"sampleTime" : "2015-07-08 03:03:20.0",
"temperature" : 64.5
},
{
"fanOn" : true,
"id" : 2,
"sampleTime" : "2015-07-08 16:03:20.0",
"temperature" : 72.1
}
]
{noformat}
All of the code lives in the derbyoptionaltools jar file. In addition to this
server-side tool, there is a public static toJSON() method which can be called
client-side or server-side in order to pack a ResultSet into a JSONArray. The
server-side tool is useful if you want to process JSON documents server-side.
The client-side packing method can be used on any ResultSet produced by any
RDBMS. In that scenario it may be more efficient to stream the ResultSet to the
client rather than assembling the JSONArray server-side. That is because JSON
is a more verbose serialization format than the network protocol of an RDBMS.
I need to write a battery of tests for this functionality. I welcome the
community's feedback.
Touches the following files:
------------------
A tools/release/notices/simpleJson.txt
A tools/java/json_simple-1.1.jar
Add the simple json jar file to the Derby source tree and add a corresponding
attribution blurb for Derby's generated NOTICE file.
------------------
M
java/build/org/apache/derbyBuild/lastgoodjarcontents/insane.derbyoptionaltools.jar.lastcontents
M
java/build/org/apache/derbyBuild/lastgoodjarcontents/sane.derbyTesting.jar.lastcontents
M
java/build/org/apache/derbyBuild/lastgoodjarcontents/insane.derbyTesting.jar.lastcontents
M
java/build/org/apache/derbyBuild/lastgoodjarcontents/sane.derbyoptionaltools.jar.lastcontents
M java/optional/build.xml
M build.xml
M tools/ant/properties/extrapath.properties
Support for building this tool.
------------------
A java/optional/org/apache/derby/optional/api/SimpleJsonUtils.java
M tools/javadoc/publishedapi.ant
Add the client-callable toJSON() method to Derby's public API.
------------------
M java/engine/org/apache/derby/impl/sql/compile/StaticMethodCallNode.java
M java/engine/org/apache/derby/catalog/Java5SystemProcedures.java
A java/optional/org/apache/derby/optional/json
A java/optional/org/apache/derby/optional/json/SimpleJsonTool.java
Machinery for the optional tool.
------------------
A
java/testing/org/apache/derbyTesting/functionTests/tests/lang/SimpleJsonTest.java
M java/testing/org/apache/derbyTesting/functionTests/tests/lang/build.xml
A pretty-printing method which will be useful for writing regression tests.
> Add basic JSON support to Derby.
> --------------------------------
>
> Key: DERBY-6825
> URL: https://issues.apache.org/jira/browse/DERBY-6825
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Affects Versions: 10.12.0.0
> Reporter: Rick Hillegas
> Attachments: SimpleJsonVTI.java, derby-6825-01-aa-optionalTool.diff
>
>
> Other database are adding support for the popular JSON data interchange
> format defined by https://www.ietf.org/rfc/rfc4627.txt. We could add a VTI to
> Derby which would unpack a JSON document into a set of rows. We could also
> add a reverse transformation, which turns a query result into a JSON document.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
