[teiid-commits] teiid SVN: r2684 - in trunk: build/kits/jboss-container and 14 other directories.

Author: rareddy Date: 2010-10-26 22:16:50 -0400 (Tue, 26 Oct 2010) New Revision: 2684 Added: trunk/engine/src/main/java/org/teiid/query/function/aggregate/TextAgg.java trunk/engine/src/main/java/org/teiid/query/sql/symbol/TextLine.java Modified: trunk/api/src/main/java/org/teiid/language/SQLConstants.java trunk/build/kits/jboss-container/teiid-releasenotes.html trunk/documentation/reference/src/main/docbook/en-US/content/sql_support.xml trunk/engine/src/main/java/org/teiid/query/eval/Evaluator.java trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/CriteriaCapabilityValidatorVisitor.java trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePushAggregates.java trunk/engine/src/main/java/org/teiid/query/processor/relational/GroupingNode.java trunk/engine/src/main/java/org/teiid/query/processor/xml/XMLUtil.java trunk/engine/src/main/java/org/teiid/query/sql/LanguageVisitor.java trunk/engine/src/main/java/org/teiid/query/sql/navigator/PreOrPostOrderNavigator.java trunk/engine/src/main/java/org/teiid/query/sql/symbol/AggregateSymbol.java trunk/engine/src/main/java/org/teiid/query/sql/visitor/SQLStringVisitor.java trunk/engine/src/main/java/org/teiid/query/validator/ValidationVisitor.java trunk/engine/src/main/javacc/org/teiid/query/parser/SQLParser.jj trunk/engine/src/test/java/org/teiid/query/parser/TestParser.java trunk/engine/src/test/java/org/teiid/query/processor/TestTextTable.java Log: TEIID-962: Adding TextAgg function that aggregates expression values into textline using delimiter and header. Modified: trunk/api/src/main/java/org/teiid/language/SQLConstants.java =================================================================== --- trunk/api/src/main/java/org/teiid/language/SQLConstants.java 2010-10-26 20:44:55 UTC (rev 2683) +++ trunk/api/src/main/java/org/teiid/language/SQLConstants.java 2010-10-27 02:16:50 UTC (rev 2684) @@ -319,6 +319,7 @@ public static final String SYSTEM_USER = "SYSTEM_USER"; //$NON-NLS-1$ public static final String TABLE = "TABLE"; //$NON-NLS-1$ public static final String TEMPORARY = "TEMPORARY"; //$NON-NLS-1$ + public static final String TEXTAGG = "TEXTAGG"; //$NON-NLS-1$ public static final String THEN = "THEN"; //$NON-NLS-1$ public static final String TIME = "TIME"; //$NON-NLS-1$ public static final String TIMESTAMP = "TIMESTAMP"; //$NON-NLS-1$ Modified: trunk/build/kits/jboss-container/teiid-releasenotes.html =================================================================== --- trunk/build/kits/jboss-container/teiid-releasenotes.html 2010-10-26 20:44:55 UTC (rev 2683) +++ trunk/build/kits/jboss-container/teiid-releasenotes.html 2010-10-27 02:16:50 UTC (rev 2684) @@ -42,7 +42,8 @@ <LI><B>Improved clustering support</B> - see the Admin Guide chapter on clustering. <LI><B>IPv6 support</B> - Teiid can started using IPv6 bind address and can be used with JDBC connection. <LI><B>SESSION_ID</B> - A new system function "SESSION_ID" is added to the system function library. - <LI><B>Assignment Syntax Improvements<B> - Teiid's procedure syntax for assignments was clarified so that the assignment value must be a proper expression. INSERT/UPDATE/DELETE update counts must be obtained from VARIABLES.ROWCOUNT, scalar values must be obtained via a scalar subquery. + <LI><B>Assignment Syntax Improvements<B> - Teiid's procedure syntax for assignments was clarified so that the assignment value must be a proper expression. INSERT/UPDATE/DELETE update counts must be obtained from VARIABLES.ROWCOUNT, scalar values must be obtained via a scalar subquery. + <LI><B>TEXTAGG</B> - SQL support for Text aggregation. Using this function expression values can be aggregated into a single line of text with delimiter and header, that can be optionally saved to a text file. The parser will continue to accept the old syntax and convert the query into the proper form. </UL> Modified: trunk/documentation/reference/src/main/docbook/en-US/content/sql_support.xml =================================================================== --- trunk/documentation/reference/src/main/docbook/en-US/content/sql_support.xml 2010-10-26 20:44:55 UTC (rev 2683) +++ trunk/documentation/reference/src/main/docbook/en-US/content/sql_support.xml 2010-10-27 02:16:50 UTC (rev 2684) @@ -238,8 +238,15 @@ <para>STDDEV_SAMP(x) – sample standar deviation (excluding null) logically equals SQRT(VAR_SAMP(x))</para> </listitem> <listitem> + <para>TEXTAGG((expression [as name], ... [DELIMITER char] [QUOTE char] [HEADER] + <link linkend="orderby_clause">[ORDER BY ...]</link>) – Text concatination of all expressions in a group (excluding null). + When DELIMITER is not specified, by default comma(,) is used as delimiter. Use QUOTE, to quote the each expression value with + provided character. If HEADER is specified, the result contains the header row as first line. This aggregation returns a Clob. + </para> + </listitem> + <listitem> <para>XMLAGG(xml_expr <link linkend="orderby_clause">[ORDER BY ...]</link>) – xml concatination of all xml expressions in a group (excluding null)</para> - </listitem> + </listitem> </itemizedlist> <itemizedlist> <para>Syntax Rules: Modified: trunk/engine/src/main/java/org/teiid/query/eval/Evaluator.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/eval/Evaluator.java 2010-10-26 20:44:55 UTC (rev 2683) +++ trunk/engine/src/main/java/org/teiid/query/eval/Evaluator.java 2010-10-27 02:16:50 UTC (rev 2684) @@ -98,6 +98,7 @@ import org.teiid.query.sql.symbol.ScalarSubquery; import org.teiid.query.sql.symbol.SearchedCaseExpression; import org.teiid.query.sql.symbol.SingleElementSymbol; +import org.teiid.query.sql.symbol.TextLine; import org.teiid.query.sql.symbol.XMLElement; import org.teiid.query.sql.symbol.XMLForest; import org.teiid.query.sql.symbol.XMLNamespaces; @@ -647,6 +648,8 @@ return evaluate((ScalarSubquery) expression, tuple); } else if (expression instanceof Criteria) { return evaluate((Criteria)expression, tuple); + } else if (expression instanceof TextLine){ + return evaluateTextForest(tuple, (TextLine)expression); } else if (expression instanceof XMLElement){ return evaluateXMLElement(tuple, (XMLElement)expression); } else if (expression instanceof XMLForest){ @@ -809,7 +812,14 @@ InputStreamFactory isf = getInputStreamFactory(value); return new ClobType(new ClobImpl(isf, -1)); } + + private Object evaluateTextForest(List<?> tuple, TextLine function) throws ExpressionEvaluationException, BlockedException, TeiidComponentException, FunctionExecutionException { + List<DerivedColumn> args = function.getExpressions(); + Evaluator.NameValuePair<Object>[] nameValuePairs = getNameValuePairs(tuple, args, true); + return TextLine.evaluate(nameValuePairs, function.getDelimiter(), function.getQuote()); + } + private Object evaluateXMLForest(List<?> tuple, XMLForest function) throws ExpressionEvaluationException, BlockedException, TeiidComponentException, FunctionExecutionException { Added: trunk/engine/src/main/java/org/teiid/query/function/aggregate/TextAgg.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/function/aggregate/TextAgg.java (rev 0) +++ trunk/engine/src/main/java/org/teiid/query/function/aggregate/TextAgg.java 2010-10-27 02:16:50 UTC (rev 2684) @@ -0,0 +1,119 @@ +/* + * JBoss, Home of Professional Open Source. + * See the COPYRIGHT.txt file distributed with this work for information + * regarding copyright ownership. Some portions may be licensed + * to Red Hat, Inc. under one or more contributor license agreements. + * + * This library is free software; you can redistribute it and/or + * modify it under the terms of the GNU Lesser General Public + * License as published by the Free Software Foundation; either + * version 2.1 of the License, or (at your option) any later version. + * + * This library is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU + * Lesser General Public License for more details. + * + * You should have received a copy of the GNU Lesser General Public + * License along with this library; if not, write to the Free Software + * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA + * 02110-1301 USA. + */ + +package org.teiid.query.function.aggregate; + +import java.io.IOException; +import java.io.Writer; +import java.sql.SQLException; +import java.util.List; + +import javax.sql.rowset.serial.SerialClob; + +import org.teiid.common.buffer.FileStore; +import org.teiid.common.buffer.FileStore.FileStoreOutputStream; +import org.teiid.core.TeiidComponentException; +import org.teiid.core.TeiidProcessingException; +import org.teiid.core.types.ClobImpl; +import org.teiid.core.types.ClobType; +import org.teiid.core.types.Streamable; +import org.teiid.query.processor.xml.XMLUtil.FileStoreInputStreamFactory; +import org.teiid.query.sql.symbol.TextLine; +import org.teiid.query.util.CommandContext; + +/** + * Aggregates Text entries + */ +public class TextAgg extends AggregateFunction { + + private FileStoreInputStreamFactory result; + private CommandContext context; + private TextLine textLine; + + public TextAgg(CommandContext context, TextLine textLine) { + this.context = context; + this.textLine = textLine; + } + + private FileStoreInputStreamFactory buildResult(CommandContext context, TextLine textLine) throws TeiidProcessingException { + try { + FileStore fs = context.getBufferManager().createFileStore("textagg"); //$NON-NLS-1$ + FileStoreInputStreamFactory fisf = new FileStoreInputStreamFactory(fs, Streamable.ENCODING); + Writer w = fisf.getWriter(); + if (textLine.isIncludeHeader()) { + w.write(TextLine.getHeader(textLine.getExpressions(), textLine.getDelimiter(), textLine.getQuote())); + } + w.close(); + return fisf; + } catch (IOException e) { + throw new TeiidProcessingException(e); + } + } + + public void reset() { + this.result = null; + } + + /** + * @throws TeiidProcessingException + * @throws TeiidComponentException + * @see org.teiid.query.function.aggregate.AggregateFunction#addInputDirect(Object, List) + */ + public void addInputDirect(Object input, List<?> tuple) throws TeiidComponentException, TeiidProcessingException { + try { + if (this.result == null) { + this.result = buildResult(this.context, this.textLine); + } + String in = (String)input; + Writer w = result.getWriter(); + w.write(in); + w.close(); + } catch (IOException e) { + throw new TeiidProcessingException(e); + } + } + + /** + * @see org.teiid.query.function.aggregate.AggregateFunction#getResult() + */ + public Object getResult() throws TeiidProcessingException{ + if (this.result == null) { + this.result = buildResult(this.context, this.textLine); + } + + try { + FileStoreOutputStream fs = this.result.getOuputStream(); + fs.close(); + + if (fs.bytesWritten()) { + return new ClobType(new ClobImpl(result, -1)); + } + // fun convert bytes to string to char array!! + String msg = new String(fs.getBuffer(),0, fs.getCount(), Streamable.ENCODING); + return new ClobType(new SerialClob(msg.toCharArray())); + } catch (IOException e) { + throw new TeiidProcessingException(e); + } catch (SQLException e) { + throw new TeiidProcessingException(e); + } + } +} \ No newline at end of file Property changes on: trunk/engine/src/main/java/org/teiid/query/function/aggregate/TextAgg.java ___________________________________________________________________ Name: svn:mime-type + text/plain Modified: trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/CriteriaCapabilityValidatorVisitor.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/CriteriaCapabilityValidatorVisitor.java 2010-10-26 20:44:55 UTC (rev 2683) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/CriteriaCapabilityValidatorVisitor.java 2010-10-27 02:16:50 UTC (rev 2684) @@ -65,6 +65,7 @@ import org.teiid.query.sql.symbol.QueryString; import org.teiid.query.sql.symbol.ScalarSubquery; import org.teiid.query.sql.symbol.SearchedCaseExpression; +import org.teiid.query.sql.symbol.TextLine; import org.teiid.query.sql.symbol.XMLAttributes; import org.teiid.query.sql.symbol.XMLElement; import org.teiid.query.sql.symbol.XMLForest; @@ -117,6 +118,11 @@ } @Override + public void visit(TextLine obj) { + markInvalid(obj, "Pushdown of TextForest not allowed"); //$NON-NLS-1$ + } + + @Override public void visit(XMLForest obj) { markInvalid(obj, "Pushdown of XMLForest not allowed"); //$NON-NLS-1$ } Modified: trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePushAggregates.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePushAggregates.java 2010-10-26 20:44:55 UTC (rev 2683) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePushAggregates.java 2010-10-27 02:16:50 UTC (rev 2684) @@ -741,7 +741,10 @@ nestedAggregates.add(countAgg); nestedAggregates.add(sumAgg); nestedAggregates.add(sumSqAgg); - } else { + } else if (aggFunction == Type.TEXTAGG) { + continue; + } + else { //AGG(X) -> AGG(AGG(X)) newExpression = new AggregateSymbol("stagedAgg", aggFunction.name(), false, partitionAgg); //$NON-NLS-1$ nestedAggregates.add(partitionAgg); Modified: trunk/engine/src/main/java/org/teiid/query/processor/relational/GroupingNode.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/processor/relational/GroupingNode.java 2010-10-26 20:44:55 UTC (rev 2683) +++ trunk/engine/src/main/java/org/teiid/query/processor/relational/GroupingNode.java 2010-10-27 02:16:50 UTC (rev 2684) @@ -22,7 +22,8 @@ package org.teiid.query.processor.relational; -import static org.teiid.query.analysis.AnalysisRecord.*; +import static org.teiid.query.analysis.AnalysisRecord.PROP_GROUP_COLS; +import static org.teiid.query.analysis.AnalysisRecord.PROP_SORT_MODE; import java.util.ArrayList; import java.util.Arrays; @@ -50,6 +51,7 @@ import org.teiid.query.function.aggregate.Min; import org.teiid.query.function.aggregate.StatsFunction; import org.teiid.query.function.aggregate.Sum; +import org.teiid.query.function.aggregate.TextAgg; import org.teiid.query.function.aggregate.XMLAgg; import org.teiid.query.processor.BatchCollector; import org.teiid.query.processor.ProcessorDataManager; @@ -60,6 +62,7 @@ import org.teiid.query.sql.symbol.ElementSymbol; import org.teiid.query.sql.symbol.Expression; import org.teiid.query.sql.symbol.SingleElementSymbol; +import org.teiid.query.sql.symbol.TextLine; import org.teiid.query.sql.symbol.AggregateSymbol.Type; import org.teiid.query.util.CommandContext; @@ -186,6 +189,9 @@ case XMLAGG: functions[i] = new XMLAgg(context); break; + case TEXTAGG: + functions[i] = new TextAgg(context, (TextLine)ex); + break; default: functions[i] = new StatsFunction(function); Modified: trunk/engine/src/main/java/org/teiid/query/processor/xml/XMLUtil.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/processor/xml/XMLUtil.java 2010-10-26 20:44:55 UTC (rev 2683) +++ trunk/engine/src/main/java/org/teiid/query/processor/xml/XMLUtil.java 2010-10-27 02:16:50 UTC (rev 2684) @@ -26,7 +26,6 @@ import java.io.ByteArrayInputStream; import java.io.IOException; import java.io.InputStream; -import java.io.OutputStream; import java.io.OutputStreamWriter; import java.io.Writer; import java.nio.charset.Charset; @@ -79,7 +78,7 @@ return new OutputStreamWriter(fsos, Charset.forName(encoding)); } - public OutputStream getOuputStream() { + public FileStoreOutputStream getOuputStream() { return fsos; } Modified: trunk/engine/src/main/java/org/teiid/query/sql/LanguageVisitor.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/sql/LanguageVisitor.java 2010-10-26 20:44:55 UTC (rev 2683) +++ trunk/engine/src/main/java/org/teiid/query/sql/LanguageVisitor.java 2010-10-27 02:16:50 UTC (rev 2684) @@ -129,6 +129,7 @@ public void visit(XMLForest obj) {} public void visit(XMLNamespaces obj) {} public void visit(TextTable obj) {} + public void visit(TextLine obj) {} public void visit(XMLTable obj) {} public void visit(DerivedColumn obj) {} public void visit(XMLSerialize obj) {} Modified: trunk/engine/src/main/java/org/teiid/query/sql/navigator/PreOrPostOrderNavigator.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/sql/navigator/PreOrPostOrderNavigator.java 2010-10-26 20:44:55 UTC (rev 2683) +++ trunk/engine/src/main/java/org/teiid/query/sql/navigator/PreOrPostOrderNavigator.java 2010-10-27 02:16:50 UTC (rev 2684) @@ -97,6 +97,7 @@ import org.teiid.query.sql.symbol.Reference; import org.teiid.query.sql.symbol.ScalarSubquery; import org.teiid.query.sql.symbol.SearchedCaseExpression; +import org.teiid.query.sql.symbol.TextLine; import org.teiid.query.sql.symbol.XMLAttributes; import org.teiid.query.sql.symbol.XMLElement; import org.teiid.query.sql.symbol.XMLForest; @@ -541,6 +542,13 @@ } @Override + public void visit(TextLine obj) { + preVisitVisitor(obj); + visitNodes(obj.getExpressions()); + postVisitVisitor(obj); + } + + @Override public void visit(XMLForest obj) { preVisitVisitor(obj); visitNode(obj.getNamespaces()); Modified: trunk/engine/src/main/java/org/teiid/query/sql/symbol/AggregateSymbol.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/sql/symbol/AggregateSymbol.java 2010-10-26 20:44:55 UTC (rev 2683) +++ trunk/engine/src/main/java/org/teiid/query/sql/symbol/AggregateSymbol.java 2010-10-27 02:16:50 UTC (rev 2684) @@ -60,6 +60,7 @@ MIN, MAX, XMLAGG, + TEXTAGG, ANY, SOME, EVERY, Added: trunk/engine/src/main/java/org/teiid/query/sql/symbol/TextLine.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/sql/symbol/TextLine.java (rev 0) +++ trunk/engine/src/main/java/org/teiid/query/sql/symbol/TextLine.java 2010-10-27 02:16:50 UTC (rev 2684) @@ -0,0 +1,196 @@ +/* + * JBoss, Home of Professional Open Source. + * See the COPYRIGHT.txt file distributed with this work for information + * regarding copyright ownership. Some portions may be licensed + * to Red Hat, Inc. under one or more contributor license agreements. + * + * This library is free software; you can redistribute it and/or + * modify it under the terms of the GNU Lesser General Public + * License as published by the Free Software Foundation; either + * version 2.1 of the License, or (at your option) any later version. + * + * This library is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU + * Lesser General Public License for more details. + * + * You should have received a copy of the GNU Lesser General Public + * License along with this library; if not, write to the Free Software + * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA + * 02110-1301 USA. + */ +package org.teiid.query.sql.symbol; + +import java.util.ArrayList; +import java.util.List; + +import org.teiid.core.types.DataTypeManager; +import org.teiid.core.util.EquivalenceUtil; +import org.teiid.core.util.HashCodeUtil; +import org.teiid.query.eval.Evaluator; +import org.teiid.query.sql.LanguageVisitor; +import org.teiid.query.sql.visitor.SQLStringVisitor; + +public class TextLine implements Expression { + public static String nl = System.getProperty("line.separator"); //$NON-NLS-1$ + + private Character delimiter = null; + private Character quote = null; + private boolean includeHeader; + private List<DerivedColumn> expressions; + + public Character getDelimiter() { + return delimiter; + } + + public void setDelimiter(Character delimiter) { + this.delimiter = delimiter; + } + + public Character getQuote() { + return quote; + } + + public void setQuote(Character quote) { + this.quote = quote; + } + + public boolean isIncludeHeader() { + return includeHeader; + } + + public void setIncludeHeader(boolean includeHeader) { + this.includeHeader = includeHeader; + } + + public List<DerivedColumn> getExpressions() { + return expressions; + } + + public void setExpressions(List<DerivedColumn> expressions) { + this.expressions = expressions; + } + + @Override + public Class<?> getType() { + return DataTypeManager.DefaultDataClasses.CLOB; + } + + @Override + public boolean isResolved() { + for (DerivedColumn arg : this.expressions) { + if (!arg.getExpression().isResolved()) { + return false; + } + } + return true; + } + + @Override + public void acceptVisitor(LanguageVisitor visitor) { + visitor.visit(this); + } + + @Override + public TextLine clone() { + TextLine clone = new TextLine(); + + if (this.expressions != null && !this.expressions.isEmpty()) { + List<DerivedColumn> list = new ArrayList<DerivedColumn>(); + for (DerivedColumn expr:this.expressions) { + list.add(expr.clone()); + } + clone.expressions = list; + } + + if (this.delimiter != null) { + clone.delimiter = new Character(this.delimiter); + } + + if (this.quote != null) { + clone.quote = new Character(this.quote); + } + + clone.includeHeader = this.includeHeader; + return clone; + } + + @Override + public boolean equals(Object obj) { + if (obj == this) { + return true; + } + if (!(obj instanceof TextLine)) { + return false; + } + TextLine other = (TextLine)obj; + return EquivalenceUtil.areEqual(this.expressions, other.expressions) + && EquivalenceUtil.areEqual(this.delimiter, other.delimiter) + && EquivalenceUtil.areEqual(this.quote, other.quote) + && this.includeHeader == other.includeHeader; + } + + @Override + public int hashCode() { + return HashCodeUtil.expHashCode(0, this.expressions); + } + + @Override + public String toString() { + return SQLStringVisitor.getSQLString(this); + } + + public static String evaluate(final Evaluator.NameValuePair[] values, Character delimeter, Character quote) { + + if (delimeter == null) { + delimeter = new Character(','); + } + + StringBuilder sb = new StringBuilder(); + for (int i = 0; i < values.length; i++) { + if (values[i].value != null) { + addQuote(quote, sb); + sb.append(values[i].value); + addQuote(quote, sb); + } + if (i < values.length-1) { + sb.append(delimeter); + } + } + sb.append(nl); + + return sb.toString(); + } + + public static String getHeader(List<DerivedColumn> args, Character delimeter, Character quote) { + + if (delimeter == null) { + delimeter = new Character(','); + } + + StringBuilder sb = new StringBuilder(); + for (int i = 0; i < args.size(); i++) { + DerivedColumn symbol = args.get(i); + String name = symbol.getAlias(); + Expression ex = symbol.getExpression(); + if (name == null && ex instanceof ElementSymbol) { + name = ((ElementSymbol)ex).getShortName(); + } + addQuote(quote, sb); + sb.append(name); + addQuote(quote, sb); + + if (i < args.size()-1) { + sb.append(delimeter); + } + } + sb.append(nl); + return sb.toString(); + } + + private static void addQuote(Character quote, StringBuilder sb) { + if (quote != null) { + sb.append(quote); + } + } +} Property changes on: trunk/engine/src/main/java/org/teiid/query/sql/symbol/TextLine.java ___________________________________________________________________ Name: svn:mime-type + text/plain Modified: trunk/engine/src/main/java/org/teiid/query/sql/visitor/SQLStringVisitor.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/sql/visitor/SQLStringVisitor.java 2010-10-26 20:44:55 UTC (rev 2683) +++ trunk/engine/src/main/java/org/teiid/query/sql/visitor/SQLStringVisitor.java 2010-10-27 02:16:50 UTC (rev 2684) @@ -116,6 +116,7 @@ import org.teiid.query.sql.symbol.SearchedCaseExpression; import org.teiid.query.sql.symbol.SelectSymbol; import org.teiid.query.sql.symbol.SingleElementSymbol; +import org.teiid.query.sql.symbol.TextLine; import org.teiid.query.sql.symbol.XMLAttributes; import org.teiid.query.sql.symbol.XMLElement; import org.teiid.query.sql.symbol.XMLForest; @@ -1591,6 +1592,32 @@ registerNodes(obj.getArgs(), 0); append(")"); //$NON-NLS-1$ } + + @Override + public void visit( TextLine obj ) { + append("TEXTLINE"); //$NON-NLS-1$ + append("("); //$NON-NLS-1$ + registerNodes(obj.getExpressions(), 0); + + if (obj.getDelimiter() != null) { + append(SPACE); + append(NonReserved.DELIMITER); + append(SPACE); + visitNode(new Constant(obj.getDelimiter())); + } + if (obj.getQuote() != null) { + append(SPACE); + append(NonReserved.QUOTE); + append(SPACE); + visitNode(new Constant(obj.getQuote())); + } + if (obj.isIncludeHeader()) { + append(SPACE); + append(NonReserved.HEADER); + } + + append(")"); //$NON-NLS-1$ + } @Override public void visit( XMLNamespaces obj ) { Modified: trunk/engine/src/main/java/org/teiid/query/validator/ValidationVisitor.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/validator/ValidationVisitor.java 2010-10-26 20:44:55 UTC (rev 2683) +++ trunk/engine/src/main/java/org/teiid/query/validator/ValidationVisitor.java 2010-10-27 02:16:50 UTC (rev 2684) @@ -117,6 +117,7 @@ import org.teiid.query.sql.symbol.Reference; import org.teiid.query.sql.symbol.ScalarSubquery; import org.teiid.query.sql.symbol.SingleElementSymbol; +import org.teiid.query.sql.symbol.TextLine; import org.teiid.query.sql.symbol.XMLAttributes; import org.teiid.query.sql.symbol.XMLElement; import org.teiid.query.sql.symbol.XMLForest; @@ -1238,6 +1239,18 @@ validateXMLContentTypes(dc.getExpression(), obj); } } + + @Override + public void visit(TextLine obj) { + validateDerivedColumnNames(obj, obj.getExpressions()); + for (DerivedColumn dc : obj.getExpressions()) { + if (dc.getAlias() == null) { + continue; + } + validateQName(obj, dc.getAlias()); + validateXMLContentTypes(dc.getExpression(), obj); + } + } private String[] validateQName(LanguageObject obj, String name) { try { Modified: trunk/engine/src/main/javacc/org/teiid/query/parser/SQLParser.jj =================================================================== --- trunk/engine/src/main/javacc/org/teiid/query/parser/SQLParser.jj 2010-10-26 20:44:55 UTC (rev 2683) +++ trunk/engine/src/main/javacc/org/teiid/query/parser/SQLParser.jj 2010-10-27 02:16:50 UTC (rev 2684) @@ -260,6 +260,7 @@ | <SYSTEM_USER: "system_user"> | <TABLE: "table"> | <TEMPORARY: "temporary"> +| <TEXTAGG: "textagg"> | <THEN: "then"> | <TIMEZONE_HOUR: "timezone_hour"> | <TIMEZONE_MINUTE: "timezone_minute"> @@ -1788,6 +1789,69 @@ } } + +AggregateSymbol textAgg(ParseInfo info) : +{ + DerivedColumn expression = null; + Character delimiter = null; + Character quote = null; + Integer header = null; + List<DerivedColumn> expressions = new ArrayList<DerivedColumn>(); + OrderBy orderBy = null; +} +{ + <TEXTAGG> <LPAREN> + expression = derivedColumn(info) + { + expressions.add(expression); + } + (<COMMA> + expression = derivedColumn(info) + { + expressions.add(expression); + } + )* + [ + LOOKAHEAD(<ID>, { "delimiter".equalsIgnoreCase(getToken(1).image) }) <ID> + delimiter = charVal(info, "DELMITER") + ] + [ + LOOKAHEAD(<ID>, { "quote".equalsIgnoreCase(getToken(1).image) }) + ( <ID> quote = charVal(info, "QUOTE") ) + ] + [ + LOOKAHEAD(<ID>, { "header".equalsIgnoreCase(getToken(1).image) }) <ID> + [ + header = intVal() + ] + { + if (header == null) { + header = 1; + } + } + ] + [ + orderBy = orderby(info) + ] + <RPAREN> + { + if(! info.aggregatesAllowed) { + throw new ParseException(QueryPlugin.Util.getString("SQLParser.Aggregate_only_top_level")); //$NON-NLS-1$ + } + + TextLine tf = new TextLine(); + tf.setDelimiter(delimiter); + tf.setQuote(quote); + tf.setIncludeHeader(header!=null); + tf.setExpressions(expressions); + + String name = generateFunctionName(info, "TEXTAGG"); + AggregateSymbol agg = new AggregateSymbol(name, "TEXTAGG", false, tf); + agg.setOrderBy(orderBy); + return agg; + } +} + AggregateSymbol aggregateSymbol(ParseInfo info) : { String func = null; @@ -3144,6 +3208,8 @@ | (expression=xmlAgg(info)) | + (expression=textAgg(info)) + | // Function LOOKAHEAD(2) (expression=function(info)) | Modified: trunk/engine/src/test/java/org/teiid/query/parser/TestParser.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/parser/TestParser.java 2010-10-26 20:44:55 UTC (rev 2683) +++ trunk/engine/src/test/java/org/teiid/query/parser/TestParser.java 2010-10-27 02:16:50 UTC (rev 2684) @@ -22,7 +22,9 @@ package org.teiid.query.parser; -import static org.junit.Assert.*; +import static org.junit.Assert.assertEquals; +import static org.junit.Assert.assertTrue; +import static org.junit.Assert.fail; import java.io.StringReader; import java.io.UnsupportedEncodingException; @@ -114,6 +116,7 @@ import org.teiid.query.sql.symbol.SearchedCaseExpression; import org.teiid.query.sql.symbol.TestCaseExpression; import org.teiid.query.sql.symbol.TestSearchedCaseExpression; +import org.teiid.query.sql.symbol.TextLine; import org.teiid.query.sql.symbol.XMLAttributes; import org.teiid.query.sql.symbol.XMLElement; import org.teiid.query.sql.symbol.XMLForest; @@ -6712,6 +6715,39 @@ helpTest(sql, "SELECT XMLAGG(1 ORDER BY e2)", query); } + @Test public void testTextAggWithOrderBy() throws Exception { + List<DerivedColumn> expressions = new ArrayList<DerivedColumn>(); + expressions.add(new DerivedColumn("col1", new ElementSymbol("e1"))); + expressions.add(new DerivedColumn("col2", new ElementSymbol("e2"))); + + TextLine tf = new TextLine(); + tf.setExpressions(expressions); + tf.setDelimiter(new Character(',')); + tf.setIncludeHeader(true); + + AggregateSymbol as = new AggregateSymbol("foo", Reserved.TEXTAGG, false, tf); + as.setOrderBy(new OrderBy(Arrays.asList(new ElementSymbol("e2")))); + + Query query = new Query(); + query.setSelect(new Select(Arrays.asList(as))); + + String sql = "SELECT TextAgg(e1 as col1, e2 as col2 delimiter ',' header order by e2)"; //$NON-NLS-1$ + helpTest(sql, "SELECT TEXTAGG(TEXTLINE(e1 AS col1, e2 AS col2 DELIMITER ',' HEADER) ORDER BY e2)", query); + } + +// @Test public void testTextForrest() throws Exception { +// List<DerivedColumn> expressions = new ArrayList<DerivedColumn>(); +// expressions.add(new DerivedColumn(null, new Constant(1))); +// expressions.add(new DerivedColumn("col2", new ElementSymbol("e2"))); +// +// TextForest tf = new TextForest(); +// tf.setExpressions(expressions); +// tf.setIncludeHeader(true); +// +// String sql = "textforest(1, e2 as col2 HEADER)"; //$NON-NLS-1$ +// helpTestExpression(sql, "TEXTFOREST(1, e2 AS col2 HEADER)", tf); +// } + @Test public void testNestedTable() throws Exception { String sql = "SELECT * from TABLE(exec foo()) as x"; //$NON-NLS-1$ Query query = new Query(); Modified: trunk/engine/src/test/java/org/teiid/query/processor/TestTextTable.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/processor/TestTextTable.java 2010-10-26 20:44:55 UTC (rev 2683) +++ trunk/engine/src/test/java/org/teiid/query/processor/TestTextTable.java 2010-10-27 02:16:50 UTC (rev 2684) @@ -22,8 +22,11 @@ package org.teiid.query.processor; +import static org.teiid.query.optimizer.TestOptimizer.getTypicalCapabilities; +import static org.teiid.query.optimizer.TestOptimizer.helpPlan; import static org.teiid.query.processor.TestProcessor.*; +import java.util.ArrayList; import java.util.Arrays; import java.util.List; @@ -33,7 +36,12 @@ import org.teiid.core.types.ClobType; import org.teiid.core.types.InputStreamFactory; import org.teiid.core.util.UnitTestUtil; +import org.teiid.query.optimizer.TestOptimizer.ComparisonMode; +import org.teiid.query.optimizer.capabilities.BasicSourceCapabilities; import org.teiid.query.optimizer.capabilities.DefaultCapabilitiesFinder; +import org.teiid.query.optimizer.capabilities.FakeCapabilitiesFinder; +import org.teiid.query.optimizer.capabilities.SourceCapabilities.Capability; +import org.teiid.query.unittest.FakeMetadataFacade; import org.teiid.query.unittest.FakeMetadataFactory; @SuppressWarnings({"unchecked", "nls"}) @@ -221,4 +229,31 @@ return new ClobType(new ClobImpl(new InputStreamFactory.FileInputStreamFactory(UnitTestUtil.getTestDataFile(file)), -1)); } + @Test public void testTextAgg() throws Exception { + FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); + FakeMetadataFacade metadata = FakeMetadataFactory.example1Cached(); + + BasicSourceCapabilities caps = getTypicalCapabilities(); + caps.setCapabilitySupport(Capability.QUERY_SUBQUERIES_SCALAR, false); + caps.setCapabilitySupport(Capability.QUERY_AGGREGATES, true); + caps.setCapabilitySupport(Capability.QUERY_AGGREGATES_MAX, true); + capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$ + + ProcessorPlan plan = helpPlan("select convert(textagg(pm1.g1.e1, pm1.g1.e2 header order by e2), string) as x from pm1.g1", metadata, null, capFinder, //$NON-NLS-1$ + new String[] { "SELECT g_0.e1, g_0.e2 FROM pm1.g1 AS g_0" }, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ + + HardcodedDataManager hdm = new HardcodedDataManager(); + hdm.addData("SELECT g_0.e1, g_0.e2 FROM pm1.g1 AS g_0", new List[] {Arrays.asList("z", 2), Arrays.asList("b", 1)}); + hdm.setBlockOnce(true); + + String nl = System.getProperty("line.separator"); + ArrayList list = new ArrayList(); + list.add("e1,e2"+nl+"b,1"+nl+"z,2"+nl); + List[] expected = new List[] { + list, + }; + + helpProcess(plan, hdm, expected); + } + }