[teiid-commits] teiid SVN: r2679 - in trunk: api/src/main/java/org/teiid/translator and 6 other directories.

Author: shawkins Date: 2010-10-25 12:56:38 -0400 (Mon, 25 Oct 2010) New Revision: 2679 Modified: trunk/api/src/main/java/org/teiid/translator/SourceSystemFunctions.java trunk/build/kits/jboss-container/teiid-releasenotes.html trunk/documentation/reference/src/main/docbook/en-US/content/scalar_functions.xml trunk/engine/pom.xml trunk/engine/src/main/java/org/teiid/query/function/source/SystemSource.java trunk/engine/src/main/java/org/teiid/query/function/source/XMLSystemFunctions.java trunk/engine/src/main/resources/org/teiid/query/i18n.properties trunk/engine/src/test/java/org/teiid/query/function/source/TestXMLSystemFunctions.java trunk/pom.xml Log: TEIID-1306 adding json to xml support for json integration. this adds the asl 2 json simple library from google code. Modified: trunk/api/src/main/java/org/teiid/translator/SourceSystemFunctions.java =================================================================== --- trunk/api/src/main/java/org/teiid/translator/SourceSystemFunctions.java 2010-10-25 16:31:58 UTC (rev 2678) +++ trunk/api/src/main/java/org/teiid/translator/SourceSystemFunctions.java 2010-10-25 16:56:38 UTC (rev 2679) @@ -142,5 +142,7 @@ public static final String XMLCONCAT = "xmlconcat"; //$NON-NLS-1$ public static final String XMLCOMMENT = "xmlcomment"; //$NON-NLS-1$ public static final String XMLPI = "xmlpi"; //$NON-NLS-1$ + + public static final String JSONTOXML = "jsontoxml"; //$NON-NLS-1$ } Modified: trunk/build/kits/jboss-container/teiid-releasenotes.html =================================================================== --- trunk/build/kits/jboss-container/teiid-releasenotes.html 2010-10-25 16:31:58 UTC (rev 2678) +++ trunk/build/kits/jboss-container/teiid-releasenotes.html 2010-10-25 16:56:38 UTC (rev 2679) @@ -31,6 +31,7 @@ <LI><B>Non-Recursive Common Table Expressions</B> - The WITH clause (and associated pushdown) for non-recursive queries is now supported. <LI><B>Explicit Table Syntax</B> - TABLE x can now be used as a shortcut for SELECT * FROM x </UL> + <LI><B>JSON Support</B> - JSON, which could be obtained through the ws-translator using http calls or other methods, can be converted to XML using the system function jsonToXml. XMLTABLE and other integration logic can then be used on the JSON results. <LI><B>Transaction Statements</B> - JDBC/ODBC now accepts START TRANSACTION, COMMIT, and ROLLBACK statements to control local transactions. <LI><B>Procedure Result Caching</B> - virtual procedure definitions may use a cache hint to cache results in the result set cache. <LI><B>Improved Plan Caching</B> - plans used by internal materialization and stored procedure plans will be automatically cached in the prepared plan cache. Improvements were also made to reduce the memory footprint of the plans. @@ -52,7 +53,7 @@ <h4>from 7.1</h4> <ul> <li>Subqueries are no longer allowed to be SELECT INTO. - <li>INSERT/UPDATE/DELETE cannot be used to create implicit return cursors in non-update virtual procedures. You can instead use "integer_var = UPDATE ...; SELECT integer_var;". + <li>INSERT/UPDATE/DELETE cannot be used to create implicit return cursors in non-update virtual procedures. You can instead use "UPDATE ...; SELECT VARIABLES.ROWCOUNT;". <li>The SYSADMIN schema was created to hold procedures and tables that should not be generally accessible. SYS and pg_catalog are now always accessible - permissions do not apply to these schemas. The SYS.getBinaryVDBResource, SYS.getCharacterVDBResource, and SYS.getVDBResourcePaths have been replaced with the SYSADMIN.VDBResources table. The Matviews table and the refreshMatView/refreshMatViewRow procedures were also moved into SYSADMIN. <li>Overwriting an existing VDB will cause old connections to be terminated. Production systems should rely on VDB versioning. @@ -60,8 +61,8 @@ <li>Model visibility no longer restricts access to tables and procedures. Setting visible to false will only hide entries from system tables. Data roles should be used to restrict data access. <li>Admin API "getWorkManagerStats" methods renamed to "getWorkerPoolStats". Also, "setRuntimeProperty" and "getProcesses" methods were removed. <li>By default the "ENV" system function is now turned off. To enable it, edit the teiid-jboss-beans.xml configuration file. - <li>The use of VARIABLES.ROWCOUNT is now reserved. Use a different - <li>Direct assignments in virtual procedures using stored procedures (e.g. var = EXEC foo()) are only valid if the procedure has a return parameter and no result set. + <li>The use of VARIABLES.ROWCOUNT is now reserved. + <li>Exec statements of the form "var = EXEC foo()" are only valid if the procedure foo has a return parameter. </ul> <h4>from 7.0</h4> <ul> @@ -144,6 +145,7 @@ The following components have been updated: <h4>From 7.1</h4> <ul> + <li>json-simple 1.1 was added. <li>Netty was upgraded to 3.2.1 </ul> <h4>From 7.0</h4> Modified: trunk/documentation/reference/src/main/docbook/en-US/content/scalar_functions.xml =================================================================== --- trunk/documentation/reference/src/main/docbook/en-US/content/scalar_functions.xml 2010-10-25 16:31:58 UTC (rev 2678) +++ trunk/documentation/reference/src/main/docbook/en-US/content/scalar_functions.xml 2010-10-25 16:56:38 UTC (rev 2679) @@ -1916,6 +1916,44 @@ <title>XML Functions</title> <para>XML functions provide functionality for working with XML data. </para> <section> + <title>JSONTOXML</title> + <para>Returns an xml document from JSON.</para> + <para><synopsis>JSONTOXML(rootElementName, json)</synopsis></para> + <para>rootElementName is a string, json is in {clob, blob}. Return value is xml.</para> + <para>The appropriate UTF encoding (8, 16LE. 16BE, 32LE, 32BE) will be detected for JSON blobs. If another encoding is used, see the to_chars function.</para> + <para>The result is always a well-formed XML document.</para> + <para>The mapping to XML uses the following rules: + <itemizedlist> + <listitem> + <para>The current element name is initially the rootElementName, and becomes the object value name as the JSON structure is traversed.</para> + </listitem> + <listitem> + <para>All element names must be valid xml 1.1 names. Invalid names are fully escaped according to the SQLXML specification.</para> + </listitem> + <listitem> + <para>Each object or primitive value will be enclosed in an element with the current name.</para> + </listitem> + <listitem> + <para>Unless an array value is the root, it will not be enclosed in an additional element.</para> + </listitem> + </itemizedlist> + </para> + <example> + <title>Sample JSON to XML for jsonToXml('person', x)</title> + <para>JSON:</para> + <programlisting language="JSON">{ "firstName" : "John" , "children" : [ "Randy", "Judy" ] }</programlisting> + <para>XML:</para> + <programlisting language="XML"><![CDATA[<?xml version="1.0" ?><person><firstName>John</firstName><children>Randy</children><children>Judy<children></person>]]></programlisting> + </example> + <example> + <title>Sample JSON to XML for jsonToXml('person', x) with a root array.</title> + <para>JSON:</para> + <programlisting language="JSON">[{ "firstName" : "George" }, { "firstName" : "Jerry" }]</programlisting> + <para>XML (Notice there is an extra "person" wrapping element to keep the XML well-formed):</para> + <programlisting language="XML"><![CDATA[<?xml version="1.0" ?><person><person><firstName>George</firstName></person><person><firstName>Jerry</firstName></person></person>]]></programlisting> + </example> + </section> + <section> <title>XMLCOMMENT</title> <para>Returns an xml comment.</para> <para><synopsis>XMLCOMMENT(comment)</synopsis></para> Modified: trunk/engine/pom.xml =================================================================== --- trunk/engine/pom.xml 2010-10-25 16:31:58 UTC (rev 2678) +++ trunk/engine/pom.xml 2010-10-25 16:56:38 UTC (rev 2679) @@ -86,6 +86,11 @@ <classifier>dom</classifier> <artifactId>saxon</artifactId> </dependency> + + <dependency> + <groupId>com.googlecode.json-simple</groupId> + <artifactId>json-simple</artifactId> + </dependency> </dependencies> Modified: trunk/engine/src/main/java/org/teiid/query/function/source/SystemSource.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/function/source/SystemSource.java 2010-10-25 16:31:58 UTC (rev 2678) +++ trunk/engine/src/main/java/org/teiid/query/function/source/SystemSource.java 2010-10-25 16:56:38 UTC (rev 2679) @@ -179,6 +179,7 @@ addXmlConcat(); addXmlComment(); addXmlPi(); + addJsonToXml(); addSecurityFunctions(); @@ -970,6 +971,19 @@ new FunctionParameter("result", DataTypeManager.DefaultDataTypes.XML, QueryPlugin.Util.getString("SystemSource.xmlpi_result")) ) ); //$NON-NLS-1$ //$NON-NLS-2$ } + private void addJsonToXml() { + functions.add(new FunctionMethod(SourceSystemFunctions.JSONTOXML, QueryPlugin.Util.getString("SystemSource.jsonToXml_description"), XML, XML_FUNCTION_CLASS, "jsonToXml", //$NON-NLS-1$ //$NON-NLS-2$ + new FunctionParameter[] { + new FunctionParameter("rootElementName", DataTypeManager.DefaultDataTypes.STRING, QueryPlugin.Util.getString("SystemSource.jsonToXml_param1")), //$NON-NLS-1$ //$NON-NLS-2$ + new FunctionParameter("json", DataTypeManager.DefaultDataTypes.CLOB, QueryPlugin.Util.getString("SystemSource.jsonToXml_param2"))}, //$NON-NLS-1$ //$NON-NLS-2$ + new FunctionParameter("result", DataTypeManager.DefaultDataTypes.XML, QueryPlugin.Util.getString("SystemSource.jsonToXml_result")) ) ); //$NON-NLS-1$ //$NON-NLS-2$ + functions.add(new FunctionMethod(SourceSystemFunctions.JSONTOXML, QueryPlugin.Util.getString("SystemSource.jsonToXml_description"), XML, XML_FUNCTION_CLASS, "jsonToXml", //$NON-NLS-1$ //$NON-NLS-2$ + new FunctionParameter[] { + new FunctionParameter("rootElementName", DataTypeManager.DefaultDataTypes.STRING, QueryPlugin.Util.getString("SystemSource.jsonToXml_param1")), //$NON-NLS-1$ //$NON-NLS-2$ + new FunctionParameter("json", DataTypeManager.DefaultDataTypes.BLOB, QueryPlugin.Util.getString("SystemSource.jsonToXml_param2"))}, //$NON-NLS-1$ //$NON-NLS-2$ + new FunctionParameter("result", DataTypeManager.DefaultDataTypes.XML, QueryPlugin.Util.getString("SystemSource.jsonToXml_result")) ) ); //$NON-NLS-1$ //$NON-NLS-2$ + } + private void addXmlConcat() { functions.add(new FunctionMethod(SourceSystemFunctions.XMLCONCAT, QueryPlugin.Util.getString("SystemSource.xmlconcat_description"), XML, FunctionMethod.CAN_PUSHDOWN, XML_FUNCTION_CLASS, "xmlConcat", //$NON-NLS-1$ //$NON-NLS-2$ new FunctionParameter[] { Modified: trunk/engine/src/main/java/org/teiid/query/function/source/XMLSystemFunctions.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/function/source/XMLSystemFunctions.java 2010-10-25 16:31:58 UTC (rev 2678) +++ trunk/engine/src/main/java/org/teiid/query/function/source/XMLSystemFunctions.java 2010-10-25 16:56:38 UTC (rev 2679) @@ -24,10 +24,14 @@ import java.io.BufferedReader; import java.io.IOException; +import java.io.InputStream; +import java.io.InputStreamReader; +import java.io.PushbackInputStream; import java.io.Reader; import java.io.StringReader; import java.io.Writer; import java.nio.CharBuffer; +import java.nio.charset.Charset; import java.sql.Blob; import java.sql.Clob; import java.sql.Date; @@ -36,6 +40,7 @@ import java.sql.Time; import java.sql.Timestamp; import java.util.Collections; +import java.util.LinkedList; import java.util.List; import javax.xml.XMLConstants; @@ -48,6 +53,7 @@ import javax.xml.stream.XMLInputFactory; import javax.xml.stream.XMLOutputFactory; import javax.xml.stream.XMLStreamException; +import javax.xml.stream.XMLStreamWriter; import javax.xml.stream.events.XMLEvent; import javax.xml.transform.Source; import javax.xml.transform.Transformer; @@ -69,6 +75,9 @@ import net.sf.saxon.value.DayTimeDurationValue; import net.sf.saxon.value.TimeValue; +import org.json.simple.parser.ContentHandler; +import org.json.simple.parser.JSONParser; +import org.json.simple.parser.ParseException; import org.teiid.core.TeiidComponentException; import org.teiid.core.TeiidProcessingException; import org.teiid.core.types.ClobImpl; @@ -91,6 +100,135 @@ */ public class XMLSystemFunctions { + private static final Charset UTF_32BE = Charset.forName("UTF-32BE"); //$NON-NLS-1$ + private static final Charset UTF_16BE = Charset.forName("UTF-16BE"); //$NON-NLS-1$ + private static final Charset UTF_32LE = Charset.forName("UTF-32LE"); //$NON-NLS-1$ + private static final Charset UTF_16LE = Charset.forName("UTF-16LE"); //$NON-NLS-1$ + private static final Charset UTF_8 = Charset.forName("UTF-8"); //$NON-NLS-1$ + + //TODO: this could be done fully streaming without holding the intermediate xml output + private static final class JsonToXmlContentHandler implements + ContentHandler { + private final XMLStreamWriter streamWriter; + private String currentName; + private LinkedList<Boolean> inArray = new LinkedList<Boolean>(); + + private JsonToXmlContentHandler(String rootName, + XMLStreamWriter streamWriter) { + this.streamWriter = streamWriter; + this.currentName = rootName; + } + + @Override + public boolean startObjectEntry(String key) + throws org.json.simple.parser.ParseException, IOException { + currentName = key; + start(); + return true; + } + + @Override + public boolean startObject() throws org.json.simple.parser.ParseException, + IOException { + if (inArray.peek()) { + start(); + } + inArray.push(false); + return true; + } + + private void start() + throws IOException { + try { + streamWriter.writeStartElement(escapeName(currentName, true)); + } catch (XMLStreamException e) { + throw new IOException(e); + } + } + + @Override + public void startJSON() throws org.json.simple.parser.ParseException, + IOException { + try { + streamWriter.writeStartDocument(); + } catch (XMLStreamException e) { + throw new IOException(e); + } + inArray.push(false); + start(); + } + + @Override + public boolean startArray() throws org.json.simple.parser.ParseException, + IOException { + inArray.push(true); + return true; + } + + @Override + public boolean primitive(Object value) + throws org.json.simple.parser.ParseException, IOException { + if (inArray.peek()) { + start(); + } + if (value != null) { + try { + streamWriter.writeCharacters(value.toString()); + } catch (XMLStreamException e) { + throw new IOException(e); + } + } + if (inArray.peek()) { + end(); + } + return true; + } + + private void end() + throws IOException { + try { + streamWriter.writeEndElement(); + } catch (XMLStreamException e) { + throw new IOException(e); + } + } + + @Override + public boolean endObjectEntry() + throws org.json.simple.parser.ParseException, IOException { + end(); + return true; + } + + @Override + public boolean endObject() throws org.json.simple.parser.ParseException, + IOException { + inArray.pop(); + if (inArray.peek()) { + end(); + } + return true; + } + + @Override + public void endJSON() throws org.json.simple.parser.ParseException, + IOException { + end(); + try { + streamWriter.writeEndDocument(); + } catch (XMLStreamException e) { + throw new IOException(e); + } + } + + @Override + public boolean endArray() throws org.json.simple.parser.ParseException, + IOException { + inArray.pop(); + return true; + } + } + private static final String P_OUTPUT_VALIDATE_STRUCTURE = "com.ctc.wstx.outputValidateStructure"; //$NON-NLS-1$ public static ClobType xslTransform(CommandContext context, Object xml, Object styleSheet) throws Exception { @@ -496,5 +634,72 @@ CharsetUtils.toHex(cb, (byte)chr); return cb.append("_").flip().toString(); //$NON-NLS-1$ } + + public static SQLXML jsonToXml(CommandContext context, final String rootName, final Blob json) throws TeiidComponentException, TeiidProcessingException, SQLException, IOException { + InputStream is = json.getBinaryStream(); + PushbackInputStream pStream = new PushbackInputStream(is, 4); + byte[] encoding = new byte[3]; + int read = pStream.read(encoding); + pStream.unread(encoding, 0, read); + Charset charset = UTF_8; + if (read > 2) { + if (encoding[0] == 0) { + if (encoding[1] == 0) { + charset = UTF_32BE; + } else { + charset = UTF_16BE; + } + } else if (encoding[1] == 0) { + if (encoding[2] == 0) { + charset = UTF_32LE; + } else { + charset = UTF_16LE; + } + } + } + Reader r = new InputStreamReader(pStream, charset); + return jsonToXml(context, rootName, r); + } + public static SQLXML jsonToXml(CommandContext context, final String rootName, final Clob json) throws TeiidComponentException, TeiidProcessingException, SQLException { + return jsonToXml(context, rootName, json.getCharacterStream()); + } + + private static SQLXML jsonToXml(CommandContext context, + final String rootName, final Reader r) throws TeiidComponentException, + TeiidProcessingException { + XMLType result = new XMLType(XMLUtil.saveToBufferManager(context.getBufferManager(), new XMLTranslator() { + + @Override + public void translate(Writer writer) throws TransformerException, + IOException { + try { + JSONParser parser = new JSONParser(); + XMLOutputFactory factory = getOutputFactory(); + final XMLStreamWriter streamWriter = factory.createXMLStreamWriter(writer); + + parser.parse(r, new JsonToXmlContentHandler(escapeName(rootName, true), streamWriter)); + + streamWriter.flush(); //woodstox needs a flush rather than a close + } catch (XMLStreamException e) { + throw new TransformerException(e); + } catch (ParseException e) { + throw new TransformerException(e); + } finally { + try { + r.close(); + } catch (IOException e) { + + } + } + } + })); + result.setType(Type.DOCUMENT); + return result; + } + + public static void main(String[] args) { + System.out.println(Charset.availableCharsets()); + } + } Modified: trunk/engine/src/main/resources/org/teiid/query/i18n.properties =================================================================== --- trunk/engine/src/main/resources/org/teiid/query/i18n.properties 2010-10-25 16:31:58 UTC (rev 2678) +++ trunk/engine/src/main/resources/org/teiid/query/i18n.properties 2010-10-25 16:56:38 UTC (rev 2679) @@ -593,6 +593,10 @@ SystemSource.xmlpi_param1=Target SystemSource.xmlpi_param2=Content SystemSource.xmlpi_result=XML result +SystemSource.jsonToXml_description=Create an XML document representing the given JSON. +SystemSource.jsonToXml_param1=Root element name +SystemSource.jsonToXml_param2=JSON +SystemSource.jsonToXml_result=XML result SystemSource.modifyTimeZone_description=Modify the time zone of this timestamp by adding or subtracting time SystemSource.modifyTimeZone_param1=Timestamp SystemSource.modifyTimeZone_param2=Starting time zone Modified: trunk/engine/src/test/java/org/teiid/query/function/source/TestXMLSystemFunctions.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/function/source/TestXMLSystemFunctions.java 2010-10-25 16:31:58 UTC (rev 2678) +++ trunk/engine/src/test/java/org/teiid/query/function/source/TestXMLSystemFunctions.java 2010-10-25 16:56:38 UTC (rev 2679) @@ -26,19 +26,29 @@ import java.io.File; import java.io.IOException; +import java.nio.charset.Charset; +import java.sql.SQLException; +import java.sql.SQLXML; import java.util.TimeZone; +import javax.sql.rowset.serial.SerialBlob; +import javax.sql.rowset.serial.SerialClob; +import javax.sql.rowset.serial.SerialException; + import net.sf.saxon.trans.XPathException; import org.junit.AfterClass; import org.junit.BeforeClass; import org.junit.Test; +import org.teiid.common.buffer.BufferManagerFactory; +import org.teiid.core.TeiidComponentException; import org.teiid.core.TeiidProcessingException; import org.teiid.core.types.SQLXMLImpl; import org.teiid.core.types.XMLType; import org.teiid.core.util.ObjectConverterUtil; import org.teiid.core.util.UnitTestUtil; import org.teiid.query.unittest.TimestampUtil; +import org.teiid.query.util.CommandContext; @SuppressWarnings("nls") public class TestXMLSystemFunctions { @@ -182,6 +192,32 @@ assertEquals("_u000A_", XMLSystemFunctions.escapeName(new String(new char[] {10}), true)); } + @Test public void testJsonToXml() throws Exception { + String json = "[0,{\"1\":{\"2\":{\"3\":{\"4\":[5,{\"6\":7}]}}}}]"; + String expected = "<?xml version=\"1.0\" ?><Array><Array>0</Array><Array><_u0031_><_u0032_><_u0033_><_u0034_><_u0034_>5</_u0034_><_u0034_><_u0036_>7</_u0036_></_u0034_></_u0034_></_u0033_></_u0032_></_u0031_></Array></Array>"; + helpTestJson(json, "Array", expected); + } + + private void helpTestJson(String json, String rootName, String expected) + throws SQLException, TeiidComponentException, + TeiidProcessingException, SerialException, IOException { + CommandContext cc = new CommandContext(); + cc.setBufferManager(BufferManagerFactory.getStandaloneBufferManager()); + SQLXML xml = XMLSystemFunctions.jsonToXml(cc, rootName, new SerialClob(json.toCharArray())); + assertEquals(expected, xml.getString()); + xml = XMLSystemFunctions.jsonToXml(cc, rootName, new SerialBlob(json.getBytes(Charset.forName("UTF-8")))); + assertEquals(expected, xml.getString()); + xml = XMLSystemFunctions.jsonToXml(cc, rootName, new SerialBlob(json.getBytes(Charset.forName("UTF-32BE")))); + assertEquals(expected, xml.getString()); + } + + @Test public void testJsonToXml1() throws Exception { + String json = "{ \"firstName\": \"John\", \"lastName\": \"Smith\", \"age\": 25, \"address\": { \"streetAddress\": \"21 2nd Street\", \"city\": \"New York\", \"state\": \"NY\", "+ + "\"postalCode\": \"10021\" }, \"phoneNumber\": [ { \"type\": \"home\", \"number\": \"212 555-1234\" }, { \"type\": \"fax\", \"number\": \"646 555-4567\" } ] }"; + String expected = "<?xml version=\"1.0\" ?><Person><firstName>John</firstName><lastName>Smith</lastName><age>25</age><address><streetAddress>21 2nd Street</streetAddress><city>New York</city><state>NY</state><postalCode>10021</postalCode></address><phoneNumber><phoneNumber><type>home</type><number>212 555-1234</number></phoneNumber><number><type>fax</type><number>646 555-4567</number></number></phoneNumber></Person>"; + helpTestJson(json, "Person", expected); + } + @BeforeClass static public void setUpOnce() { TimeZone.setDefault(TimeZone.getTimeZone("GMT-6:00")); } Modified: trunk/pom.xml =================================================================== --- trunk/pom.xml 2010-10-25 16:31:58 UTC (rev 2678) +++ trunk/pom.xml 2010-10-25 16:56:38 UTC (rev 2679) @@ -456,6 +456,11 @@ <artifactId>ant</artifactId> <version>${ant.version}</version> </dependency> + <dependency> + <groupId>com.googlecode.json-simple</groupId> + <artifactId>json-simple</artifactId> + <version>1.1</version> + </dependency> </dependencies> </dependencyManagement> <modules>