Author: shawkins Date: 2011-08-11 14:49:54 -0400 (Thu, 11 Aug 2011) New Revision: 3377 Modified: trunk/build/kits/jboss-container/teiid-releasenotes.html trunk/documentation/reference/src/main/docbook/en-US/content/grammar.xml trunk/documentation/reference/src/main/docbook/en-US/content/sql_clauses.xml trunk/engine/src/main/java/org/teiid/query/processor/relational/TextTableNode.java trunk/engine/src/main/java/org/teiid/query/sql/lang/TextTable.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/main/resources/org/teiid/query/i18n.properties trunk/engine/src/test/java/org/teiid/query/parser/TestParser.java trunk/engine/src/test/java/org/teiid/query/processor/TestTextTable.java trunk/engine/src/test/java/org/teiid/query/sql/visitor/TestSQLStringVisitor.java trunk/engine/src/test/java/org/teiid/query/validator/TestValidator.java Log: TEIID-1703 adding more flexibility to fixed width parsing Modified: trunk/build/kits/jboss-container/teiid-releasenotes.html =================================================================== --- trunk/build/kits/jboss-container/teiid-releasenotes.html 2011-08-11 15:59:42 UTC (rev 3376) +++ trunk/build/kits/jboss-container/teiid-releasenotes.html 2011-08-11 18:49:54 UTC (rev 3377) @@ -28,6 +28,7 @@ <UL> <LI><B>Procedure language features</B> - Added support for compound/block statements, BEGIN [[NOT] ATOMIC], loop/block labels, and the leave statement. See the reference for more. <LI><B>File Enhancements</B> - the file translator can now optionally (via the ExceptionIfFileNotFound property) throw an exception if the path refers to a file that doesn't exist. The file resource adapter can be configured to map file names and can prevent parent path .. references. See the Admin Guide or the file-ds.xml template for more. + <LI><B>TEXTTABLE Enhancements</B> - TEXTTABLE can now parse fixed width files that do not use a row delimiter and can optionally produce fixed values that haven't been trimmed. </UL> <h2><a name="Compatibility">Compatibility Issues</a></h2> Modified: trunk/documentation/reference/src/main/docbook/en-US/content/grammar.xml =================================================================== --- trunk/documentation/reference/src/main/docbook/en-US/content/grammar.xml 2011-08-11 15:59:42 UTC (rev 3376) +++ trunk/documentation/reference/src/main/docbook/en-US/content/grammar.xml 2011-08-11 18:49:54 UTC (rev 3377) @@ -664,11 +664,11 @@ <row> <entry align="right" valign="top"><para><anchor id="prod76" xreflabel="textTable"/>textTable</para></entry> <entry align="left" valign="top"><para>::= -&lt;ID&gt; &lt;LPAREN&gt; <link linkend="prod24">expression</link> <link linkend="prod11">nonReserved</link> <link linkend="prod82">textColumn</link> ( &lt;COMMA&gt; <link linkend="prod82">textColumn</link> )* ( &lt;ID&gt; <link linkend="prod68">charVal</link> )? ( ( &lt;ESCAPE&gt; <link linkend="prod68">charVal</link> ) | ( &lt;ID&gt; <link linkend="prod68">charVal</link> ) )? ( &lt;ID&gt; ( <link linkend="prod83">intVal</link> )? )? ( &lt;ID&gt; <link linkend="prod83">intVal</link> )? &lt;RPAREN&gt; ( &lt;AS&gt; )? <link linkend="prod2">id</link></para></entry></row> +&lt;ID&gt; &lt;LPAREN&gt; <link linkend="prod24">expression</link> <link linkend="prod11">nonReserved</link> <link linkend="prod82">textColumn</link> ( &lt;COMMA&gt; <link linkend="prod82">textColumn</link> )* ( &lt;NO&gt; &lt;ROW&gt; <link linkend="prod11">nonReserved</link> )? ( &lt;ID&gt; <link linkend="prod68">charVal</link> )? ( ( &lt;ESCAPE&gt; <link linkend="prod68">charVal</link> ) | ( &lt;ID&gt; <link linkend="prod68">charVal</link> ) )? ( &lt;ID&gt; ( <link linkend="prod83">intVal</link> )? )? ( &lt;ID&gt; <link linkend="prod83">intVal</link> )? &lt;RPAREN&gt; ( &lt;AS&gt; )? <link linkend="prod2">id</link></para></entry></row> <row> <entry align="right" valign="top"><para><anchor id="prod82" xreflabel="textColumn"/>textColumn</para></entry> <entry align="left" valign="top"><para>::= -<link linkend="prod2">id</link> <link linkend="prod37">dataType</link> ( &lt;ID&gt; <link linkend="prod83">intVal</link> )?</para></entry></row> +<link linkend="prod2">id</link> <link linkend="prod37">dataType</link> ( &lt;ID&gt; <link linkend="prod83">intVal</link> ( &lt;NO&gt; <link linkend="prod11">nonReserved</link> )? )?</para></entry></row> <row> <entry align="right" valign="top"><para><anchor id="prod84" xreflabel="xmlQuery"/>xmlQuery</para></entry> <entry align="left" valign="top"><para>::= Modified: trunk/documentation/reference/src/main/docbook/en-US/content/sql_clauses.xml =================================================================== --- trunk/documentation/reference/src/main/docbook/en-US/content/sql_clauses.xml 2011-08-11 15:59:42 UTC (rev 3376) +++ trunk/documentation/reference/src/main/docbook/en-US/content/sql_clauses.xml 2011-08-11 18:49:54 UTC (rev 3377) @@ -98,8 +98,8 @@ </para> <para> Usage: - <synopsis label="Usage">TEXTTABLE(expression COLUMNS &lt;COLUMN&gt;, ... [DELIMITER char] [(QUOTE|ESCAPE) char] [HEADER [integer]] [SKIP integer]) AS name</synopsis> - <synopsis label="Usage">COLUMN := name datatype [WIDTH integer]</synopsis> + <synopsis label="Usage">TEXTTABLE(expression COLUMNS &lt;COLUMN&gt;, ... [NO ROW DELIMITER] [DELIMITER char] [(QUOTE|ESCAPE) char] [HEADER [integer]] [SKIP integer]) AS name</synopsis> + <synopsis label="Usage">COLUMN := name datatype [WIDTH integer [NO TRIM]]</synopsis> </para> <itemizedlist> <para>Parameters</para> @@ -108,6 +108,10 @@ </para> </listitem> <listitem> + <para>NO ROW DELIMITER indicates that fixed parsing should not assume the presense of newline row delimiters. + </para> + </listitem> + <listitem> <para>DELIMITER sets the field delimiter character to use. Defaults to ','. </para> </listitem> @@ -131,12 +135,20 @@ <para>SKIP specifies the number of text lines (counting every new line) to skip before parsing the contents. HEADER may still be specified with SKP. </para> </listitem> + <listitem> + <para>WIDTH indicates the fixed-width length of a column in characters - not bytes. The CR NL newline value counts as a single character. + </para> + </listitem> + <listitem> + <para>NO TRIM specifies that the text value should not be trimmed of all leading and trailing white space. + </para> + </listitem> </itemizedlist> <itemizedlist> <para>Syntax Rules: </para> <listitem> - <para>If width is specified for one column it must be specified for all columns. + <para>If width is specified for one column it must be specified for all columns and be a non-negative integer. </para> </listitem> <listitem> @@ -144,6 +156,10 @@ </para> </listitem> <listitem> + <para>If width is not specified, then NO ROW DELIMITER cannot be used. + </para> + </listitem> + <listitem> <para>The columns names must be not contain duplicates. </para> </listitem> @@ -151,21 +167,25 @@ <itemizedlist> <para>Examples</para> <listitem> - <para>Use of the HEADER parameter, returns 1 row ['b']: <programlisting>select * from texttable('col1,col2,col3\na,b,c' COLUMNS col2 string HEADER) x</programlisting> + <para>Use of the HEADER parameter, returns 1 row ['b']: <programlisting>SELECT * FROM TEXTTABLE(UNESCAPE('col1,col2,col3\na,b,c') COLUMNS col2 string HEADER) x</programlisting> </para> </listitem> <listitem> - <para>Use of fixed width, returns 1 row ['a', 'b', 'c']: <programlisting>select * from texttable('abc' COLUMNS col1 string width 1, col2 string width 1, col3 string width 1) x</programlisting> + <para>Use of fixed width, returns 2 rows ['a', 'b', 'c'], ['d', 'e', 'f']: <programlisting>SELECT * FROM TEXTTABLE(UNESCAPE('abc\ndef') COLUMNS col1 string width 1, col2 string width 1, col3 string width 1) x</programlisting> </para> </listitem> <listitem> - <para>Use of ESCAPE parameter, returns 1 row ['a,', 'b']: <programlisting>select * from texttable('a:,,b' COLUMNS col1 string, col2 string ESCAPE ':') x</programlisting> + <para>Use of fixed width without a row delimiter, returns 3 rows ['a'], ['b'], ['c']: <programlisting>SELECT * FROM TEXTTABLE('abc' COLUMNS col1 string width 1 NO ROW DELIMITER) x</programlisting> </para> </listitem> <listitem> - <para>As a nested table: <programlisting>select x.* from t, texttable(t.clobcolumn COLUMNS first string, second date SKIP 1) x</programlisting> + <para>Use of ESCAPE parameter, returns 1 row ['a,', 'b']: <programlisting>SELECT * FROM TEXTTABLE('a:,,b' COLUMNS col1 string, col2 string ESCAPE ':') x</programlisting> </para> </listitem> + <listitem> + <para>As a nested table: <programlisting>SELECT x.* FROM t, TEXTTABLE(t.clobcolumn COLUMNS first string, second date SKIP 1) x</programlisting> + </para> + </listitem> </itemizedlist> </section> <section id="xmltable"> Modified: trunk/engine/src/main/java/org/teiid/query/processor/relational/TextTableNode.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/processor/relational/TextTableNode.java 2011-08-11 15:59:42 UTC (rev 3376) +++ trunk/engine/src/main/java/org/teiid/query/processor/relational/TextTableNode.java 2011-08-11 18:49:54 UTC (rev 3377) @@ -51,9 +51,6 @@ /** * Handles text file processing. * - * TODO: unix style escape handling \t \n, etc. - see also the unescape function - * TODO: allow for escaping with fixed parsing - * TODO: allow for fixed parsing without new lines * TODO: allow for a configurable line terminator */ public class TextTableNode extends SubqueryAwareRelationalNode { @@ -211,14 +208,21 @@ if (eof) { return null; } - continue; //skip empty lines + if (table.isUsingRowDelimiter()) { + continue; //skip empty lines + } } - if (exact && sb.length() < lineWidth) { - throw new TeiidProcessingException(QueryPlugin.Util.getString("TextTableNode.invalid_width", sb.length(), lineWidth, textLine, systemId)); //$NON-NLS-1$ - } - return sb.toString(); + if (table.isUsingRowDelimiter()) { + if (exact && sb.length() < lineWidth) { + throw new TeiidProcessingException(QueryPlugin.Util.getString("TextTableNode.invalid_width", sb.length(), lineWidth, textLine, systemId)); //$NON-NLS-1$ + } + return sb.toString(); + } } sb.append(c); + if (exact && sb.length() == maxLength && !table.isUsingRowDelimiter()) { + return sb.toString(); + } if (sb.length() > maxLength) { if (exact) { sb.deleteCharAt(sb.length() - 1); @@ -433,7 +437,7 @@ int beginIndex = 0; for (TextColumn col : table.getColumns()) { String val = new String(line.substring(beginIndex, beginIndex + col.getWidth())); - addValue(result, false, val); + addValue(result, col.isNoTrim(), val); beginIndex += col.getWidth(); } return result; Modified: trunk/engine/src/main/java/org/teiid/query/sql/lang/TextTable.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/sql/lang/TextTable.java 2011-08-11 15:59:42 UTC (rev 3376) +++ trunk/engine/src/main/java/org/teiid/query/sql/lang/TextTable.java 2011-08-11 18:49:54 UTC (rev 3377) @@ -36,10 +36,12 @@ public static class TextColumn extends ProjectedColumn { private Integer width; + private boolean noTrim; - public TextColumn(String name, String type, Integer width) { + public TextColumn(String name, String type, Integer width, boolean noTrim) { super(name, type); this.width = width; + this.noTrim = noTrim; } protected TextColumn() { @@ -54,6 +56,14 @@ this.width = width; } + public boolean isNoTrim() { + return noTrim; + } + + public void setNoTrim(boolean noTrim) { + this.noTrim = noTrim; + } + @Override public boolean equals(Object obj) { if (obj == this) { @@ -63,13 +73,15 @@ return false; } TextColumn other = (TextColumn)obj; - return EquivalenceUtil.areEqual(width, other.width); + return EquivalenceUtil.areEqual(width, other.width) + && noTrim == other.noTrim; } @Override public TextColumn clone() { TextColumn clone = new TextColumn(); clone.width = this.width; + clone.noTrim = this.noTrim; this.copyTo(clone); return clone; } @@ -82,6 +94,7 @@ private boolean escape; private Integer header; private Integer skip; + private boolean usingRowDelimiter; private boolean fixedWidth; @@ -148,6 +161,14 @@ public void setFile(Expression file) { this.file = file; } + + public boolean isUsingRowDelimiter() { + return usingRowDelimiter; + } + + public void setUsingRowDelimiter(boolean usingRowDelimiter) { + this.usingRowDelimiter = usingRowDelimiter; + } @Override public void acceptVisitor(LanguageVisitor visitor) { @@ -168,6 +189,7 @@ clone.getColumns().add(column.clone()); } clone.fixedWidth = this.fixedWidth; + clone.usingRowDelimiter = this.usingRowDelimiter; return clone; } @@ -186,7 +208,8 @@ && EquivalenceUtil.areEqual(escape, other.escape) && EquivalenceUtil.areEqual(quote, other.quote) && EquivalenceUtil.areEqual(header, other.header) - && EquivalenceUtil.areEqual(skip, other.skip); + && EquivalenceUtil.areEqual(skip, other.skip) + && usingRowDelimiter == other.usingRowDelimiter; } } 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 2011-08-11 15:59:42 UTC (rev 3376) +++ trunk/engine/src/main/java/org/teiid/query/sql/visitor/SQLStringVisitor.java 2011-08-11 18:49:54 UTC (rev 3377) @@ -1277,11 +1277,7 @@ append(" "); //$NON-NLS-1$ } - if (args.length < 2 || args[1] == null || !(args[1] instanceof Constant)) { - append(UNDEFINED); - } else { - append(((Constant)args[1]).getValue()); - } + append(((Constant)args[1]).getValue()); } append(")"); //$NON-NLS-1$ @@ -1811,10 +1807,24 @@ append(SPACE); append(col.getWidth()); } + if (col.isNoTrim()) { + append(SPACE); + append(NO); + append(SPACE); + append(NonReserved.TRIM); + } if (cols.hasNext()) { append(","); //$NON-NLS-1$ } } + if (!obj.isUsingRowDelimiter()) { + append(SPACE); + append(NO); + append(SPACE); + append(ROW); + append(SPACE); + append(NonReserved.DELIMITER); + } if (obj.getDelimiter() != null) { append(SPACE); append(NonReserved.DELIMITER); Modified: trunk/engine/src/main/java/org/teiid/query/validator/ValidationVisitor.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/validator/ValidationVisitor.java 2011-08-11 15:59:42 UTC (rev 3376) +++ trunk/engine/src/main/java/org/teiid/query/validator/ValidationVisitor.java 2011-08-11 18:49:54 UTC (rev 3377) @@ -1474,6 +1474,9 @@ if (obj.getHeader() != null && obj.getHeader() < 0) { handleValidationError(QueryPlugin.Util.getString("ValidationVisitor.text_table_negative"), obj); //$NON-NLS-1$ } + if (!obj.isUsingRowDelimiter()) { + handleValidationError(QueryPlugin.Util.getString("ValidationVisitor.fixed_option"), obj); //$NON-NLS-1$ + } delimiter = obj.getDelimiter(); quote = obj.getQuote(); validateTextOptions(obj, delimiter, quote); Modified: trunk/engine/src/main/javacc/org/teiid/query/parser/SQLParser.jj =================================================================== --- trunk/engine/src/main/javacc/org/teiid/query/parser/SQLParser.jj 2011-08-11 15:59:42 UTC (rev 3376) +++ trunk/engine/src/main/javacc/org/teiid/query/parser/SQLParser.jj 2011-08-11 18:49:54 UTC (rev 3377) @@ -2287,6 +2287,7 @@ Expression file = null; TextTable.TextColumn column = null; List<TextTable.TextColumn> columns = new ArrayList<TextTable.TextColumn>(); + boolean useRowDelimiter = true; Character delimiter = null; boolean escape = false; Character quote = null; @@ -2308,6 +2309,12 @@ } )* [ + <NO> <ROW> nonReserved("DELIMITER") + { + useRowDelimiter = false; + } + ] + [ LOOKAHEAD(<ID>, { "delimiter".equalsIgnoreCase(getToken(1).image) }) <ID> delimiter = charVal(info, "DELMITER") ] @@ -2344,6 +2351,7 @@ result.setSkip(skip); result.setName(validateAlias(aliasID)); result.setQuote(quote); + result.setUsingRowDelimiter(useRowDelimiter); return result; } } @@ -2353,6 +2361,7 @@ String name = null; Constant datatype = null; Integer width = null; + boolean noTrim = false; } { name = id() @@ -2360,9 +2369,15 @@ [ LOOKAHEAD(<ID>, { "width".equalsIgnoreCase(getToken(1).image) }) <ID> width = intVal() + [ LOOKAHEAD(2) + <NO> nonReserved("TRIM") + { + noTrim = true; + } + ] ] { - return new TextTable.TextColumn(validateElementName(name), (String)datatype.getValue(), width); + return new TextTable.TextColumn(validateElementName(name), (String)datatype.getValue(), width, noTrim); } } Modified: trunk/engine/src/main/resources/org/teiid/query/i18n.properties =================================================================== --- trunk/engine/src/main/resources/org/teiid/query/i18n.properties 2011-08-11 15:59:42 UTC (rev 3376) +++ trunk/engine/src/main/resources/org/teiid/query/i18n.properties 2011-08-11 18:49:54 UTC (rev 3377) @@ -816,7 +816,8 @@ TextTableNode.character_not_allowed=Text parse error: Non-whitespace character found between the qualifier and the delimiter in text line {0} in {1}. TextTableNode.unknown_escape=Text parse error: Unknown escape sequence \\{0} in text line {1} in {2}. TextTableNode.invalid_width=Text parse error: Fixed width line width {0} is smaller than the expected {1} on text line {2} in {3}. -TextTableNode.line_too_long=Text parse error: Delimited line is longer than the expected max of {2} on text line {0} in {1}. +TextTableNode.line_too_long=Text parse error: Delimited line is longer than the expected max of {2} on text line {0} in {1}. +ValidationVisitor.fixed_option=NO ROW DELIMITER can only be used in fixed parsing mode. XMLTableNode.error=Error evaluating XQuery row context for XMLTable: {0} XMLTableNode.path_error=Error evaluating XMLTable column path expression for column: {0} Modified: trunk/engine/src/test/java/org/teiid/query/parser/TestParser.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/parser/TestParser.java 2011-08-11 15:59:42 UTC (rev 3376) +++ trunk/engine/src/test/java/org/teiid/query/parser/TestParser.java 2011-08-11 18:49:54 UTC (rev 3377) @@ -6710,8 +6710,8 @@ TextTable tt = new TextTable(); tt.setFile(new ElementSymbol("file")); List<TextTable.TextColumn> columns = new ArrayList<TextTable.TextColumn>(); - columns.add(new TextTable.TextColumn("x", "string", 1)); - columns.add(new TextTable.TextColumn("y", "date", 10)); + columns.add(new TextTable.TextColumn("x", "string", 1, false)); + columns.add(new TextTable.TextColumn("y", "date", 10, false)); tt.setColumns(columns); tt.setSkip(10); tt.setName("x"); Modified: trunk/engine/src/test/java/org/teiid/query/processor/TestTextTable.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/processor/TestTextTable.java 2011-08-11 15:59:42 UTC (rev 3376) +++ trunk/engine/src/test/java/org/teiid/query/processor/TestTextTable.java 2011-08-11 18:49:54 UTC (rev 3377) @@ -159,6 +159,29 @@ process(sql, expected); } + @Test public void testNoRowDelimiter() throws Exception { + String sql = "select * from texttable('abcdef' COLUMNS c1 string width 1, c2 string width 1 no row delimiter) x"; //$NON-NLS-1$ + + List[] expected = new List[] { + Arrays.asList("a","b"), + Arrays.asList("c","d"), + Arrays.asList("e","f"), + }; + + process(sql, expected); + } + + @Test public void testNoTrim() throws Exception { + String sql = "select * from texttable('a b \nc d' COLUMNS c1 string width 2, c2 string width 2 no trim) x"; //$NON-NLS-1$ + + List[] expected = new List[] { + Arrays.asList("a","b "), + Arrays.asList("c"," d"), + }; + + process(sql, expected); + } + @Test public void testNoRows() throws Exception { String sql = "select * from texttable('' COLUMNS c1 string, c2 string SKIP 3) x"; //$NON-NLS-1$ Modified: trunk/engine/src/test/java/org/teiid/query/sql/visitor/TestSQLStringVisitor.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/sql/visitor/TestSQLStringVisitor.java 2011-08-11 15:59:42 UTC (rev 3376) +++ trunk/engine/src/test/java/org/teiid/query/sql/visitor/TestSQLStringVisitor.java 2011-08-11 18:49:54 UTC (rev 3377) @@ -1901,5 +1901,10 @@ @Test public void testSimilar() throws Exception { helpTestExpression("x similar to 'b' escape 'c'", "x SIMILAR TO 'b' ESCAPE 'c'"); } + + @Test public void testTextTable() throws Exception { + String sql = "SELECT * from texttable(file columns x string WIDTH 1 NO TRIM NO ROW DELIMITER) as x"; //$NON-NLS-1$ + helpTest(QueryParser.getQueryParser().parseCommand(sql), "SELECT * FROM TEXTTABLE(file COLUMNS x string WIDTH 1 NO TRIM NO ROW DELIMITER) AS x"); + } } Modified: trunk/engine/src/test/java/org/teiid/query/validator/TestValidator.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/validator/TestValidator.java 2011-08-11 15:59:42 UTC (rev 3376) +++ trunk/engine/src/test/java/org/teiid/query/validator/TestValidator.java 2011-08-11 18:49:54 UTC (rev 3377) @@ -1772,6 +1772,10 @@ helpValidate("SELECT * from texttable(null columns x string width 1 DELIMITER 'z') as x", new String[] {"TEXTTABLE(null COLUMNS x string WIDTH 1 DELIMITER 'z') AS x"}, RealMetadataFactory.exampleBQTCached()); } + @Test public void testTextTableNoRowDelimiter() { + helpValidate("SELECT * from texttable(null columns x string NO ROW DELIMITER) as x", new String[] {"TEXTTABLE(null COLUMNS x string NO ROW DELIMITER) AS x"}, RealMetadataFactory.exampleBQTCached()); + } + @Test public void testXMLNamespaces() { helpValidate("select xmlforest(xmlnamespaces(no default, default 'http://foo'), e1 as \"table\") from pm1.g1", new String[] {"XMLNAMESPACES(NO DEFAULT, DEFAULT 'http://foo')"}, RealMetadataFactory.example1Cached()); }