[teiid-issues] [JBoss JIRA] Created: (TEIID-1102) Create a replacement for the text translator

[Steve Hawkins (JIRA)]

Create a replacement for the text translator
--------------------------------------------

                 Key: TEIID-1102
                 URL: https://jira.jboss.org/browse/TEIID-1102
             Project: Teiid
          Issue Type: Feature Request
          Components: Misc. Connectors, Query Engine
    Affects Versions: 7.0
            Reporter: Steve Hawkins
            Assignee: Steve Hawkins
             Fix For: 7.0


The current solution for the text connector requires the use of an external metadata file.  This complicates the user experience with integrating text.  An alternative approach would be to offer a built-in table function for text parsing:

TEXTTABLE(expression COLUMNS column [, column]* [DELIMITER char] [(QUOTE|ESCAPE) char] [HEADER [integer]] [SKIP integer]) AS identifier
column := identifier datatype [WIDTH integer]

If width is specified for one column it must be specified for all columns.
If width is specified, then fixed width parsing is used and ESCAPE, QUOTE, and HEADER should not be specified.

expression - the text content to process, which should be convertable to CLOB.
DELIMITER sets the field delimiter character to use.  Defaults to ','.
QUOTE sets the quote, or qualifier, character used to wrap field values.  Defaults to '"'.  
ESCAPE sets the escape character to use if no quoting character is in use.  This is used in situations where the delimiter or new line characters are escaped with a preceding character, e.g. \,  
HEADER specifies the text line number (counting every new line) on which the column names occur.  All lines prior to the header will be skipped.
If HEADER is specified, then the header line will be used to determine the TEXTTABLE column position by case-insensitive name matching.  This is especially useful in situations where only a subset of the columns are needed.
If the HEADER value is not specified, it defaults to 1.  
If HEADER is not specified, then columns are expected to match positionally with the text contents. 
SKIP specifies the number of text lines (counting every new line) to skip before parsing the contents.  HEADER may still be specified with SKP.

this would be implicitly a nested table, so it could be used to process multiple files:

select x.* from t, texttable(t.clobcolumn COLUMNS first string, second date SKIP 1) x

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: https://jira.jboss.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira