[Debezium] Syncronizing ANTLR PlSql grammar with Debezium PlSql grammar

[ani-sha]

Hi team,

The Debezium PlSql grammar was initially forked from the ANTLR grammar. However, over the past year, significant divergence has occurred between the two, making maintenance challenging. To improve maintainability and ensure consistency, we propose synchronizing our PlSql grammar with the upstream ANTLR module. Our grammar includes legacy and logminer specific extensions. We are interested in contributing these extensions back to the ANTLR module. Would this be beneficial or acceptable to the community? Thanks.

cc: @Naros @jpechane

[kaby76]

We have two MySQL grammars: the "official" Oracle MySQL by Mike Lischke; and the Positive Tech grammar. Please leave the "official" grammar as is because the grammar is/going to be derived from the official source. Unfortunately, Mike is only writing for TypeScript, not for the other targets, which is why it exists here. I haven't written the scripts that will scrape the grammar from the Oracle Github repo yet, but that will be coming. And, when it does, everything that was done to the Oracle-based grammar here will be overwritten.

The other MySQL grammar is already being extended in non-standard MySQL ways. I have no problem doing that, but the changes must be gated with semantic predicates. A semantic predicate must be at the beginning of an "alt" in order to allow the parser to function correctly.

[jpechane]

@kaby76 Hi, this is a misunderstanding. @ani-sha is writing about Oracle DB grammar, not MySQL.

[kaby76]

Sorry, I misread PL/SQL. In the future, for my benefit, please give a GitHub "permalink" to the actual grammar you are working on so there is no confusion. E.g., the sql/plsql grammar.

That said, my main point is this: It would be best to have extensions guarded using semantic predicates so there is no confusion as to what the extension is and allow it to be turned on or off for the target server environment.

If we do this, then the issue will be how to test and document it for people to use. As far as testing goes, we could just enable every extension in the driver. But, I'd be interested to see how extensions impact ambiguity and SLL fallbacks.

[Naros]

Hi @kaby76, I want to clarify when @ani-sha mentions Logminer-specific extensions, we're not referring to driver-specific things but rather use cases based on Oracle-provided database packages, tooling, or custom user code. In all circumstances, Debezium must successfully parse these DDL statements, regardless of their origin.

Some examples include:

• We use the Oracle PL/SQL package DBMS_METADATA.GET_DDL to retrieve CREATE TABLE statements. Depending on the features used by the table and how the user has defined the table, this package may provide specific clauses in an order that does not directly align with Oracle's published grammar. Yet, we still need to parse these successfully.

• Certain SQL syntax for Oracle data pump and Oracle SQL loader have not been part of the upstream ANTLR Oracle PL/SQL grammar, and we've added a fair amount of support for this syntax as our users often use this. Would it be okay to add these rules to the grammar like any other SQL rule?

• Our users may often rely on older PL/SQL user code that still works from older database versions. Some older syntaxes may have been deprecated or removed from the "official" grammar documented in Oracle's documentation; however, the SQL engine continues to accept such statements. Since Oracle records the DDL as supplied by the user, we need to parse these successfully. If some rules or clauses don't align with the "official" documented Oracle grammar, how would you prefer we handle these? We could keep these downstream, but I could also see this being useful for the broader community.

[kaby76]

Generally, if the syntax is not in the "official," it's best to add a semantic predicate to it. I would prefer not to go to the PL/SQL specs, see a diff in the syntax between the one here and the specs, and not know why it's in there. I do know Oracle has a hard time keeping up with the doc, though. On retiring old syntax, Mike added semantic predicates for different versions in the "official" MySQL grammar. We should do that for the plsql grammar, e.g., here. Forking the plsql grammar is going to lead to maintenance issues.

[Naros]

Hi @kaby76 so to be clear:

• Anything that is related older Oracle syntax that isn't documented any longer should be version predicated
• Rules around data-pump should be predicated by a feature predicate, i.e. dataPumpEnabled boolean
• Rules around sql-loader should be predicated by a feature predicate, i.e. sqlLoaderEnabled boolean
• Any rules that are related to DBMS_METADATA.GET_DDL that generate SQL differently from the documented SQL should be predicated, i.e. dbmsMetadataGetDdl boolean

Does that sound sufficient to you?

[kaby76]

Sounds good.