db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rick Hillegas (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (DERBY-6825) Add basic JSON support to Derby.
Date Thu, 09 Jul 2015 01:08:06 GMT

     [ 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)

Mime
View raw message