cronSyncDB.sh

#!/bin/bash

TODAY=$(date)

echo "Inicio de cronSyncDB.sh $TODAY"  >> /home/success.log

cd /home/

#PGHOST=1.1.1.1
PGHOST=localhost
PGUSER=user
export PGPASSWORD=123
PGDB=base_datos


# FTP LOGIN
HOST='host'
USER='user'
PASSWORD='pass'

#TARGET="/var/www/html/logs/caching"
TARGET="./logs/caching"

if [ ! -d "$TARGET" ]; then
       mkdir "$TARGET"
fi

#Funcion para verificar el archivo registrado en la Base de Datos
checkFileInDB () {
    #echo "check file '$1' in database"
   
    query="SELECT count(file) cant FROM imported WHERE file='$1'"
    #echo $query

    resultado=$(/usr/bin/psql -h$PGHOST -U$PGUSER -d $PGDB -qAt -c "$query")   
   
    echo $resultado
}


for file in $(find "$TARGET" -type f -name '*.gz');
do
   
    filename=$(basename "$file")
    dirnamefile=$(dirname "$file")

    if echo $filename | egrep -v '^OK_|^FAILED_'; then
       
        check=$(checkFileInDB $file)

        if [ "$check" -ge "1" ];then #if check
            echo "the file '$file' EXISTS"
            SRC=$file
            DST="$dirnamefile/"OK_"$filename"
           
            mv "$file"  "$dirnamefile/"OK_"$filename"               
           
            /usr/local/bin/lftp -u "$USER","$PASSWORD" $HOST <<EOF

                set sftp:auto-confirm yes
                set ftp:ssl-allow no
                cd /
                mv $SRC $DST
                exit
EOF
        else
           
            TABLE_LOG='logs'
            TABLE_IMPORTED='imported'

            # if echo $filename | grep -e 'ssl'; then
            #     TABLE_LOG='logs_ssl'
            #     TABLE_IMPORTED='imported_ssl'
            # fi
           
            #gunzip < "$file" | tail -n +3 > "$file".tmp
            gunzip < "$file" | sed '/^#/ d' > "$file".tmp

            resultado=$(/usr/bin/psql -U$PGUSER -d $PGDB  <<EOF

            \set ON_ERROR_STOP TRUE
            set client_encoding to 'latin1'

            \copy $TABLE_LOG ("date","time","cs-ip","cs-method","cs-uri","sc-status","sc-bytes","time-taken","cs(Referer)","cs(User-Agent)","cs(Cookie)","x-Custom","x-LogGroup",\
            "x-disid","x-extstatus","x-headersize") from '$file.tmp' ENCODING 'LATIN1';
EOF
    )
            ERRCODE=$?

            case $ERRCODE in
                3)
                    echo "Script error(3) loading the table."
                    DST="$dirnamefile/"FAILED_3_"$filename"
                    mv "$file"  "$dirnamefile/"FAILED_3_"$filename"
                ;;

                2)
                    echo "Connectivity error(2) loading the table."
                    DST="$dirnamefile/"FAILED_2_"$filename"
                    mv "$file"  "$dirnamefile/"FAILED_2_"$filename"
                ;;

                1)
                    echo "Fatal (out of memory, file not found) error(1) loading the table."
                    DST="$dirnamefile/"FAILED_1_"$filename"
                    mv "$file"  "$dirnamefile/"FAILED_1_"$filename"
                ;;

                0)
                    echo "Well done: "
                    DST="$dirnamefile/"OK_"$filename"
                    mv "$file"  "$dirnamefile/"OK_"$filename"

                ;;
            esac

                SRC=$file
               
                /usr/local/bin/lftp -u "$USER","$PASSWORD" $HOST <<EOF

                set sftp:auto-confirm yes
                set ftp:ssl-allow no
                cd /
                mv $SRC $DST
                exit
EOF
           /usr/bin/psql -U$PGUSER -d $PGDB  <<EOF
            insert into $TABLE_IMPORTED (file, result, code) values('$file', '$resultado', $ERRCODE)
EOF
            rm -f "$file".tmp
        fi #end if check
fi


done #end for


echo "cronSyncDB.sh completo $TODAY" >> /home/success.log

sh /home/cron.sh  > /tmp/log.log 2> /tmp/log.log