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.

  • The problem comes that when there are 2 devs made changes to the same view in their feature branches, and merge it, changes could be overwritten. – ogbofjnr Jun 14 '20 at 22:15
  • 1
    That’s a problem that you should solve procedurally and through communication. A good source control system will help you with this. – Colin 't Hart Jun 14 '20 at 22:19
  • Yeah we did it, throughout the communication, but human factor tend to make mistakes, someone somewhere will/did forget to tell to others, and then we'll have a fail. I believe that there is a way to automate routine tasks. – ogbofjnr Jun 15 '20 at 7:30
  • There should only be one file with the view definition. It sounds like each developer is making “alter” scripts only. – Colin 't Hart Jun 15 '20 at 7:32

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.