compare DDL with bash

Question

I need to compare two ddl, for the same table and identify the changes:

• new column
• deleted column
• datatype change

for example DDL_1 is

column_1 int
column_2 char(10)
column_3 char(20)

DDL_2 is

column_1 int
column_3 char(40)
column_4 int

so:

column_2 deleted
column_4 added
column_3 datatype change

and I need to obtain 3 files: deleted, added, changed

if I use
comm -23 DDL_1 DDL_2 > deleted
I would get column_2 (delete) but also column_3

comm -13 DDL_1 DDL_2 > added
I would get column_4 (add) but also column_3

how can I get the correct output?
many thanks
sergio

Answer 1

awk to the rescue!

$ awk 'FNR==1  {split(FILENAME,f,"_"); prefix=f[1]}
       NR==FNR {a[$1]=$2; next} 
       $1 in a {if(a[$1]!=$2) print > (prefix"_changed"); 
                delete a[$1]; next} 
               {print > (prefix_"added")} 
           END {for(k in a) print k,a[k] > (prefix_"deleted")}' prefix_file1 prefix_file2

$ head added changed deleted
==> prefix_added <==
column_4 int

==> prefix_changed <==
column_3 char(40)

==> prefix_deleted <==
column_2 char(10)