Normalize SQL to the same format

Question

I want to write a package that will manage mysql/pgsql views, but for that I need to check if the view defined in the code is the same as the view in the database which I get with show create table command.

The problem is that database parse query and might slightly change it with adding aliases, etc. Is there a parser that could normalize views to the same format so I could compare them like strings?

Right now I use approach with creating tmp view, getting the definition and dropping the view. It works, but looks not good.

Answer 1

Apache Foundation maintains a SQL parser called Calcite. It can comprehend several dialects including MySQL and Postgres. You could use it to translate a view from both sources to their tokenised parse trees, then compare the trees for interesting differences. Even easier would be to convert the trees back to SQL in common dialect and perform a string compare on the two outputs.