[Agents] Oracle SQL parsing improvements
- Dominant language
- Gherkin
- Stars
- 427
- Forks
- 125
- PR merge metrics
- No merged PRs in 30d
Description
## Description of the issue
In https://github.com/elastic/apm-agent-java/pull/696 there are several further improvements made to SQL parsing which are relevant to all agents, relating to parsing Oracle SQL:
- support for [MERGE](https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm#SQLRF01606) statements
- support for extracting [Database Links](https://docs.oracle.com/cd/B28359_01/server.111/b28310/ds_concepts002.htm#ADMIN12083) from table/object identifiers
## Proposed solution
### MERGE statements
For a statement like `MERGE INTO USING ...`, we would set the span name to `MERGE INTO `.
### Database links
Anywhere we extract a table name (e.g. in SELECT, UPDATE, INSERT, etc.), we should also look for a database link in the form of two consecutive tokens: a `@` character, followed by an identifier (possibly double-quoted). If we find this sequence immediately following a table name, we'll assume it is a database link.
The database link should not be recorded in span names, but instead recorded as a separate field per the proposal in https://github.com/elastic/apm/issues/107. This would permit aggregation of all like queries regardless of destination database, in addition to a breakdown by destination database. Until that proposal is accepted, we will not record the link anywhere.
## Acceptance criteria
Additional JSON test cases, from https://github.com/elastic/apm-agent-java/pull/696
{
"input": "MERGE INTO TEST USING (SELECT * FROM DUAL) SOURCE on (TEST.X=SOURCE.DUMMY) WHEN NOT MATCHED THEN INSERT VALUES(SOURCE.DUMMY)",
"output": "MERGE INTO TEST"
},
{
"input": "MERGE INTO TEST ALIAS USING (SELECT * FROM DUAL) SOURCE on (TEST.X=SOURCE.DUMMY) WHEN NOT MATCHED THEN INSERT VALUES(SOURCE.DUMMY)",
"output": "MERGE INTO TEST"
},
{
"input": "MERGE INTO SCHEMA.TEST USING (SELECT * FROM DUAL) SOURCE on (TEST.X=SOURCE.DUMMY) WHEN NOT MATCHED THEN INSERT VALUES(SOURCE.DUMMY)",
"output": "MERGE INTO SCHEMA.TEST"
},
{
"input": "MERGE INTO SCHEMA.TEST@DBLINK USING (SELECT * FROM DUAL) SOURCE on (TEST.X=SOURCE.DUMMY) WHEN NOT MATCHED THEN INSERT VALUES(SOURCE.DUMMY)",
"output": "MERGE INTO SCHEMA.TEST"
},
{
"input": "MERGE INTO SCHEMA.TEST@\"DBLINK.FQDN.COM\" USING (SELECT * FROM DUAL) SOURCE on (TEST.X=SOURCE.DUMMY) WHEN NOT MATCHED THEN INSERT VALUES(SOURCE.DUMMY)",
"output": "MERGE INTO SCHEMA.TEST"
},
{
"input": "MERGE INTO SCHEMA.TEST@\"DBLINK.FQDN.COM@USER\" USING (SELECT * FROM DUAL) SOURCE on (TEST.X=SOURCE.DUMMY) WHEN NOT MATCHED THEN INSERT VALUES(SOURCE.DUMMY)",
"output": "MERGE INTO SCHEMA.TEST"
},
{
"input": "MERGE",
"output": "MERGE"
},
## What we are voting on
@elastic/apm-agent-devs Tick the N/A box if your agent isn't affected (e.g. there is no Oracle database driver). Otherwise, please create and link to an issue or PR and tick "Yes". If you have concerns about the proposed solution, let's discuss.
## Vote
| Agent | Yes | No | Indifferent | N/A | Link to agent issue
| --------|:----:|:---:|:-----------:|:----:|:-------------------:|
| .NET |
- [x]
- [ ]
- [ ]
- [ ]
| Go |
- [ ]
- [ ]
- [ ]
- [ ]
| Java |
- [x]
- [ ]
- [ ]
- [ ]
| Node.js |
- [ ]
- [ ]
- [ ]
- [x]
| Python |
- [ ]
- [ ]
- [ ]
- [ ]
| Ruby |
- [ ]
- [ ]
- [x]
- [ ]
Contributor guide
No contributing guide indexed for this repository
Assessment
This issue has not been assessed yet.