[teiid-issues] [JBoss JIRA] (TEIID-4452) Pushdown SQL/XML
[
https://issues.jboss.org/browse/TEIID-4452?page=com.atlassian.jira.plugin...
]
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)