Geek Answers Handbook

Semantic equivalence of reformatted (PL /) SQL code

Background

Hundreds of database objects (views, packages, stored procedures, etc.) in the system do not have formatting and comments of the source code. We would like:

  • Automatically reformat the code (using Generic SQL Parser ).
  • Automatically copy the standard comment header to each source file of the object.

Problem

We cannot promote such radical changes in production without verification.

Question

How would you confirm that the reformatted source code is functionally identical to the unformatted code?

Thanks!

+4
source share
3 answers

Easy:

  • Run unformatted code in a new new database
  • Run the formatted code in a new new database
  • Make a complete export of both and compare two files

They must be the same.

The reason they should be identical is because postgres parses SQL in its standard canonical form, so even adding unnecessary brackets, for example, should lead to the same internal version of the code.

+2
source

I suppose that the format is required only for those objects that need to be changed, therefore I recommend the format of only the object you are working on, in the end the result should be put into operation. I use Oracle SQL Developer and is safe to work with the program block and format.

0
source

To your question: to compare formatted source with unformatted, you have to fake each of them and compare the results. It actually defeats your original goal .; -)

0
source

More articles:


All Articles