[teiid-issues] [JBoss JIRA] (TEIID-4452) Pushdown SQL/XML

[Steven Hawkins (JIRA)]

     [ https://issues.jboss.org/browse/TEIID-4452?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Steven Hawkins reassigned TEIID-4452:
-------------------------------------

    Fix Version/s: 9.2
         Assignee:     (was: Steven Hawkins)


I realized I hadn't answered your question from before:

>  Do you have an idea of what work would be involved? I can probably work on it if it doesn't fit on your roadmap.

The work at a high level would be to:
- Add relevant capabilities methods to the ExecutionFactory
- Allow those methods to be converted into Capability enums.
- Change the CriteriaCapabilityValidationVisitor (that's a legacy misnomer, as it should be ExpressionCapabilityValidationVisitor) to check the capabilities.
- Add org.teiid.language objects that represent the relevant sql/xml constructs - this is necessary as most of the sql/xml functions use special constructs that are forced fit for just the standard Function with a list of Expression arguments.
- Have the LanguageBridgeFactory translate into the translator language objects
- Add supporting logic in the translator api for visitation and SQL stringification
- Finally handle any translator specific modification for the generated SQL.

As for the roadmap, we can tentatively look at this for 9.2, but it won't be a high priority.

> Pushdown SQL/XML
> ----------------
>
>                 Key: TEIID-4452
>                 URL: https://issues.jboss.org/browse/TEIID-4452
>             Project: Teiid
>          Issue Type: Enhancement
>          Components: JDBC Connector
>    Affects Versions: 9.x
>            Reporter: Tom Arnold
>             Fix For: 9.2
>
>
> It would be helpful to pushdown SQL/XML functions for JDBC databases that support them. 
> Databases:
> - Oracle
> - Postgres
> Functions:
> - XMLELEMENT
> - XMLATTRIBUTES
> - XMLAGG
> - XMLCONCAT
> We have an application that uses these functions and we support Oracle, Postgres, and Teiid (to integrate external data sources). Here's an example query generated by this application:
> {code:xml}
> select
> "ITEMS"."ITEM_ID" "ITEM_ID",
> (
>     select xmlelement (
>         name "attributes",
>         xmlagg(
>             xmlelement(
>                 name "attribute", 
>                 xmlattributes(
>                     "ITEM_ATTRIBUTES"."ATTR_NAME" as "name", 
>                     "ITEM_ATTRIBUTES"."ATTR_VALUE" as "value"
>                 )
>             )
>         )
>     )
>     from "ITEM_ATTRIBUTES" "ITEM_ATTRIBUTES"
>     where (
>         "ITEMS"."ITEM_ID" = "ITEM_ATTRIBUTES"."ITEM_ID"
>     )
> ) "ITEM_ATTRIBUTES"
> {code}
> {code}
> ITEM_ID    ITEM_ATTRIBUTES
> 3          <attributes><attribute name="foo" value="bar"></attribute><attribute name="baz" value="blah"></attribute></attributes>
> 4          <attributes><attribute name="foo" value="123"></attribute><attribute name="baz" value="456"></attribute></attributes>
> {code}
> When we are using Teiid queries like this do not work well because they cannot be pushed down, so we get N-many queries (one per row) and the query ends up taking much longer than if performed directly against Oracle/Postgres. 
> Since SQL/XML is standard and supported by both Oracle/Postgres it would be good if queries like this could be pushed in their entirety so that there would be no performance difference between going through Teiid and native.



--
This message was sent by Atlassian JIRA
(v6.4.11#64026)