0

We are automating the process of creating/modifying tables on our database. We keep our ddls in github repo. Our objective is to drop and create the table again if the definition has changed. Otherwise, no change.

Lets say we have a table named table1

Steps:

1. Query database to get ddl for table1.
2. Get ddl from github repo, check if there is any difference between github & ddl from database server.
3. If there is a difference, drop and create again
4. Else, no change.

For comparing, doing string comparison is very naive ( change in space(s) doesn't mean change in schema).

Is there any API for comparison? I am specifically looking for python APIs. Standard diff utility doesn't do a good job of comparing 2 sql files. It will create a diff if the order the fields are different but the overall ddl may be same.

snakecharmerb
  • 47,570
  • 11
  • 100
  • 153
SunilS
  • 2,030
  • 5
  • 34
  • 62

2 Answers2

0

Since you are looking for a Python API you may want to consider using something like GitPython

For the comparison (your step 2), commit the file and let git determine if there were any changes. EDIT: Adding to using Git for comparison (git diff) given your comment on white-space, see this answer for guidance.

Morpheus
  • 1,616
  • 1
  • 21
  • 31
  • It will not help if there are spaces, etc – SunilS Feb 20 '20 at 17:44
  • @user1768610 I have updated my answer to address your concern regarding spaces. There is a similar question here: https://stackoverflow.com/q/33159394/3854195 – Morpheus Feb 26 '20 at 18:08
0

Consider running both versions through a SQL parser (e.g., https://pypi.org/project/sqlparse/ -- no endorsement is implied, there may be other alternatives) and comparing elements of the two parse trees.

rd_nielsen
  • 2,407
  • 2
  • 11
  • 18