#!/bin/bash
#
#    This code is created to compare two DB2 databases of the same structure, 
#    and output an SQL script that, when run alter the DST database to look like
#    the source.
#
#    Created by gergely.papp@vision-it.hu
#    Copyright 2007, Vision-IT Consulting Kft.
#
#    This code can only be reproduced or used with permission from the author.

# This function creates an output file with the content of the data
# differences in the form of insert/update statements.
#
# arg1 - SRC DB alias
# arg2 - SRC DB user
# arg3 - SRC DB password
# arg4 - DST DB alias
# arg5 - DST DB user
# arg6 - DST DB password
# arg7 - schema name to compare
# arg8 - table name to compare
# arg9 - fields to use for sorting
# arg10 - filter to use for comparing the DBs
# arg11 - fields to ignore in comparison and update
# arg12 - data to use for inserting ignored fields
#
function compare_content ()
{
    SRC_DB_ALIAS=${1}
    SRC_DB_USER=${2}
    SRC_DB_PASSWORD=${3}
    DST_DB_ALIAS=${4}
    DST_DB_USER=${5}
    DST_DB_PASSWORD=${6}
    SCHEMA_NAME=${7}
    TABLE_NAME=${8}
    ID_FIELD=${9}
    FILTER_STRING=${10}
    IGNORE_FIELDS=${11}
    IGNORE_DATA_FOR_INSERT=${12}
    RETVAL=0;

    if [ "" == "$FILTER_STRING" ]; then
        FILTER_STRING='1=1'
    fi
    db2 connect to $SRC_DB_ALIAS user $SRC_DB_USER using $SRC_DB_PASSWORD >/dev/null
    if [ $? -ne 0 ]; then
        echo "Cannot connect to source DB"
        return -1;
    fi
    IGNORE_FIELDS_QUOTED=`echo "'$IGNORE_FIELDS'" | sed s/,/','/g`
    FIELDS=`db2 -x "select COLNAME from syscat.COLUMNS where tabschema='$SCHEMA_NAME' and tabname='$TABLE_NAME' and COLNAME NOT IN (${IGNORE_FIELDS_QUOTED}) order by COLNO"`
    FIELDS=`echo $FIELDS|tr ' ' ','`
    db2 "select $ID_FIELD, $FIELDS,'|~|' SEP from $SCHEMA_NAME.$TABLE_NAME where $FILTER_STRING order by $ID_FIELD" |grep -v 'record(s) selected' >_tmp_src

    db2 connect to $DST_DB_ALIAS user $DST_DB_USER using $DST_DB_PASSWORD >/dev/null
    if [ $? -ne 0 ]; then
        echo "Cannot connect to dest DB"
        return -1;
    fi
    db2 "select $ID_FIELD, $FIELDS,'|~|' SEP from $SCHEMA_NAME.$TABLE_NAME where $FILTER_STRING order by $ID_FIELD" |grep -v 'record(s) selected'>_tmp_dst

    awk '{  if (/(\|\~\||\-\-\-|SEP)$/) { printf ("%s", substr($0, 0, length - 3) "\n" ); } else { printf ( "%s", substr($0, 0, length) "<cr>" ); } }' _tmp_src >_tmp_src2
    awk '{  if (/(\|\~\||\-\-\-|SEP)$/) { printf ("%s", substr($0, 0, length - 3) "\n" ); } else { printf ( "%s", substr($0, 0, length) "</cr><cr>" ); } }' _tmp_dst >_tmp_dst2
    diff -b _tmp_src2 _tmp_dst2 > _tmp_diff
    rm _tmp_src _tmp_dst
    if [ -s _tmp_diff ]; then
        echo "-- Differences found in table $TABLE_NAME"
        DASH_ROW=`head -n2 _tmp_src2 | tail -n1`
        ROW_LEN=`echo $DASH_ROW | wc -c`
        ID_FIELD_COUNT=`echo ${ID_FIELD}',' | tr -dc ',' | wc -c`
        ID_FIELD_LENGTH=$(( `echo ${DASH_ROW} | cut -d' ' -f-${ID_FIELD_COUNT} | wc -c` - 1 ))
        I=0
        while [[ $I < $ID_FIELD_COUNT ]]; do
            FIELD_NAMES[$I]=`echo ${ID_FIELD}|cut -d',' -f$(($I+1))`
            FIELD_LENGTHS[$I]=`echo ${DASH_ROW}|cut -d' ' -f$(($I+1))|wc -c`
            FIELD_LENGTHS[$I]=$((FIELD_LENGTHS[$I]-1))
            IS_QUOTED[$I]=`db2 -x "select case when TYPENAME in ('TIME', 'DATE', 'TIMESTAMP', 'CHARACTER', 'VARCHAR', 'CLOB') then '1' else '0' end from syscat.COLUMNS where tabschema='$SCHEMA_NAME' and tabname='$TABLE_NAME' and COLNAME='${FIELD_NAMES[$I]}'"`
            I=$((I+1))
        done
        I=${ID_FIELD_COUNT}
        for FIELD_NAME in `echo ${FIELDS}|tr ',' ' '`; do
            FIELD_NAMES[$I]=$FIELD_NAME
            FIELD_LENGTHS[$I]=`echo ${DASH_ROW}|cut -d' ' -f$(($I+1))|wc -c`
            FIELD_LENGTHS[$I]=$(( ${FIELD_LENGTHS[$I]} - 1 ))
            IS_QUOTED[$I]=`db2 -x "select case when TYPENAME in ('TIME', 'DATE', 'TIMESTAMP', 'CHARACTER', 'VARCHAR', 'CLOB') then '1' else '0' end from syscat.COLUMNS where tabschema='$SCHEMA_NAME' and tabname='$TABLE_NAME' and COLNAME='${FIELD_NAMES[$I]}'"`
            #echo "${FIELD_NAMES[$FIELD_COUNT]}=${FIELD_LENGTHS[$FIELD_COUNT]}"
            I=$((I+1))
        done
        FIELD_COUNT=$I

        # select values that are new or updated in the source DB to file _tmp_new
        grep '^<' _tmp_diff | cut -c3-$((${ID_FIELD_LENGTH} + 2)) >_tmp_new
        # select values that are new or updated in the destination DB to file _tmp_old
        grep '^>' _tmp_diff | cut -c3-$((${ID_FIELD_LENGTH} + 2)) >_tmp_old

        #open file for reading
        exec 6<_tmp_new ROWNUM=0;
        while read -u6 ID; do
            #find the action to take
            NEW_ROW=`grep "^<[ ]*$ID " _tmp_diff | cut -c3- `
            FIELDNUM=0
            CUTROW=${NEW_ROW}
            while [[ $FIELDNUM -lt $FIELD_COUNT ]]; do
                FIELD[$FIELDNUM]=`echo "$CUTROW" | cut -c-$((${FIELD_LENGTHS[${FIELDNUM}]} )) |  sed "s/'/''/g"| sed "s/<CR>/'||chr(10)||'/g"| sed "s/ *$//g"` #<cr> -> chr(10) temaja
                if [[ "-" == `echo ${FIELD[$FIELDNUM]} | sed "s/^ *//g"` ]]; then
                    FIELD[$FIELDNUM]='null'
                fi
                CUTROW=`echo "$CUTROW" | cut -c$(( ${FIELD_LENGTHS[${FIELDNUM}]} + 2 ))-`
                FIELDNUM=$(($FIELDNUM+1))
            done
            if [[ `grep "^[ ]*${ID}[ ]*$" _tmp_old | wc -l` -eq 1 ]]; then
                echo '-- Update required for id ' ${ID}
                echo -n "UPDATE $SCHEMA_NAME.$TABLE_NAME SET "
                I=${ID_FIELD_COUNT}
                while (( $I < $FIELD_COUNT )); do
                    if [[ $I -gt $ID_FIELD_COUNT ]]; then echo -n ', '; fi
                    if [[ ${IS_QUOTED[$I]} -eq '1' && ! ${FIELD[$I]} == 'null' ]]; then
                        echo -n "${FIELD_NAMES[$I]}='${FIELD[$I]}'"
                    else
                        echo -n "${FIELD_NAMES[$I]}="${FIELD[$I]}
                    fi
                    I=$((I+1))
                done
                echo -n ' WHERE '
                I=0
                while (( $I < $ID_FIELD_COUNT )); do
                    if [[ $I -ne 0 ]]; then echo -n ' and '; fi
                    if [[ ${IS_QUOTED[$I]} -eq '1' ]]; then
                        echo -n "${FIELD_NAMES[$I]}='${FIELD[$I]}'"
                    else
                        echo -n "${FIELD_NAMES[$I]}="${FIELD[$I]}
                    fi
                    I=$((I+1))
                done
                echo ';'
            else
                echo "-- Insert required for id  ${ID}";
                echo -n "INSERT INTO $SCHEMA_NAME.$TABLE_NAME (";

                if [[ -n ${IGNORE_FIELDS} ]]; then echo -n "${IGNORE_FIELDS}, "; fi


                I=${ID_FIELD_COUNT}
                while (( $I < $FIELD_COUNT )); do
                    if [[ $I -gt $ID_FIELD_COUNT ]]; then echo -n ', '; fi
                    echo -n "${FIELD_NAMES[$I]}"
                    I=$((I+1))
                done
                echo -n ') VALUES ('

                if [[ -n ${IGNORE_FIELDS} ]]; then echo -n "${IGNORE_DATA_FOR_INSERT}, "; fi

                I=${ID_FIELD_COUNT}
                while (( $I < $FIELD_COUNT )); do
                    if [[ $I -gt $ID_FIELD_COUNT ]]; then echo -n ', '; fi
                    if [[ ${IS_QUOTED[$I]} -eq '1' && ! ${FIELD[$I]} == 'null' ]]; then
                        echo -n "'${FIELD[$I]}'"
                    else
                        echo -n ${FIELD[$I]}
                    fi
                    I=$((I+1))
                done
                echo ');'
            fi
            ROWNUM=$(($ROWNUM+1))
        done
        #close file after done
        exec 6<&-

        #open file for reading
        exec 6<_tmp_old
        ROWNUM=0;
        while read -u6 ID; do
            #find the action to take
            NEW_ROW=`grep "^>[ ]*$ID " _tmp_diff | cut -c3- `
            FIELDNUM=0
            CUTROW=${NEW_ROW}
            while [[ $FIELDNUM -lt $ID_FIELD_COUNT ]]; do
                FIELD[$FIELDNUM]=`echo "$CUTROW" | cut -c-$((${FIELD_LENGTHS[${FIELDNUM}]} )) |  sed "s/'/''/g"| sed "s/</cr><cr>/'||chr(10)||'/g"| sed "s/ *$//g"` #</cr><cr> -> chr(10) temaja
                if [[ "-" == `echo ${FIELD[$FIELDNUM]} | sed "s/^ *//g"` ]]; then
                    FIELD[$FIELDNUM]='null'
                fi
                CUTROW=`echo "$CUTROW" | cut -c$(( ${FIELD_LENGTHS[${FIELDNUM}]} + 2 ))-`
                FIELDNUM=$(($FIELDNUM+1))
            done
            if [[ `grep "^[ ]*${ID}[ ]*$" _tmp_new | wc -l` -eq 0 ]]; then
                echo '-- Delete required for id ' ${ID}
                echo -n "DELETE FROM $SCHEMA_NAME.$TABLE_NAME WHERE "
                I=0
                while (( $I < $ID_FIELD_COUNT )); do
                    if [[ $I -ne 0 ]]; then echo -n ' and '; fi
                    if [[ ${IS_QUOTED[$I]} -eq '1' ]]; then
                        echo -n "${FIELD_NAMES[$I]}='${FIELD[$I]}'"
                    else
                        echo -n "${FIELD_NAMES[$I]}="${FIELD[$I]}
                    fi
                    I=$((I+1))
                done
                echo ';'
            fi
            ROWNUM=$(($ROWNUM+1))
        done
        #close file after done
        exec 6<&-

        #mv _tmp_diff ${SCHEMA_NAME}.${TABLE_NAME}.data.diff
        rm _tmp_new _tmp_old
        RETVAL=-1;
    fi
    rm -f _tmp_src2 _tmp_dst2 _tmp_diff
    return $RETVAL;

}