source: trunk/Mars/hawc/processing/DiskToDB/update-aux-table.sh@ 20106

#!/bin/bash

# Recursively finds all FAD_CONTROL_TEMPERATURE, BIAS_CONTROL_DYNAMIC_DATA,
# FTM_CONTROL_DATA, FTU_CONTROL_DATA files in DIR and their NIGHT and writes 
# an SQL query with the columns: 
# (Telescope,
#  NIGHT, 
#  FAD_CONTROL_TEMPERATURE, 
#  BIAS_CONTROL_DYNAMIC_DATA,
#  FTM_CONTROL_DATA,
#  FTU_CONTROL_DATA)
# to OUTPUT.
#
# Example:
#       bash update-aux-table.sh 1 /data/HE01/auxil/

set -o errexit
set -o errtrace
set -o nounset
set -o pipefail

function ErrExit()
{
        echo "ERROR: Line `caller`: ${BASH_COMMAND}" >&2
        exit 1
}

function StrgCExit()
{
        echo " "
        echo "$0 was forcefully terminated" >&2
        exit 1
}

trap ErrExit ERR
trap StrgCExit INT

# define variables
OUTPUT="insert-AuxOnDisk.sql"
INSERT="INSERT INTO"
readonly PROGRAM=$0

usage()
{
        echo "usage: $PROGRAM [-hir] [-o outfile] [Telescope Dir]"
        echo "  -h              display help"
        echo "  -i              query ignores already existing rows"
        echo "  -r              query replaces already existing rows(overwrites -i)"
        echo "  -o outfile      name of the SQL query file"
        exit 1;
}

# check for flags
while getopts 'hiro:' flag
do
        case "${flag}" in
                h) usage ;;
                i) INSERT="INSERT IGNORE" ;;
                r) INSERT="REPLACE INTO" ;;
                o) OUTPUT="${OPTARG}" ;;
                *) usage ;;
        esac
done
shift $(($OPTIND-1))

# Check if at least two arguments are provided
if [ -z "${1}" ] || [ -z "${2}" ]
then
        echo "ERROR: Not enough arguments. Check -h for help!" >&2
        exit 1
fi

TEL="${1}"
DIR="${2}"


# Initialize the Table
echo \
"\
CREATE TABLE IF NOT EXISTS AuxOnDisk
(
        Telescope tinyint UNSIGNED NOT NULL,
        NIGHT int UNSIGNED NOT NULL,
        FAD_CONTROL_TEMPERATURE tinyint UNSIGNED,
        BIAS_CONTROL_DYNAMIC_DATA tinyint UNSIGNED,
        FTM_CONTROL_DATA tinyint UNSIGNED,
        FTU_CONTROL_DATA tinyint UNSIGNED,
        Updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 
                ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (Telescope, NIGHT)
);
"\ > ${OUTPUT}


echo \
"\
CREATE TEMPORARY TABLE AuxOnDisk_tmp
(
        Telescope tinyint UNSIGNED NOT NULL,
        NIGHT int UNSIGNED NOT NULL,
        FAD_CONTROL_TEMPERATURE tinyint UNSIGNED,
        BIAS_CONTROL_DYNAMIC_DATA tinyint UNSIGNED,
        FTM_CONTROL_DATA tinyint UNSIGNED,
        FTU_CONTROL_DATA tinyint UNSIGNED,
        Updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 
                ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (Telescope, NIGHT)
); 
"\ >> ${OUTPUT}

FAD="FAD_CONTROL_TEMPERATURE"
BIAS="BIAS_CONTROL_DYNAMIC_DATA"
FTM="FTM_CONTROL_DATA"
FTU="FTU_CONTROL_DATA"

AUXIS=($FAD $BIAS $FTM $FTU)

for AUX in ${AUXIS[@]}
do
        echo ${AUX}


        EXT=0
        NIGHT=`find "${DIR}"/[0-9][0-9][0-9][0-9]/ -type f \
                | grep -o "/[0-9]\{8\}.${AUX}.fits" \
                | cut -f2 -d "/" \
                | cut -f1 -d "."`|| EXT=1

        if [ ${EXT} = "0" ]
        then

                echo \
                "\
INSERT INTO AuxOnDisk_tmp (Telescope, NIGHT, ${AUX})
VALUES
"\ >> ${OUTPUT}


                # Format to (Telescope, NIGHT, FAD_CONTROL_TEMPERATURE),
                QUERY=`paste <(for i in ${NIGHT}; do echo ${TEL}; done) \
                        <(echo "${NIGHT}") \
                        <(for i in ${NIGHT}; do echo "0"; done) \
                        --delimiters "," \
                        | sed 's/^/(/' \
                        | sed 's/$/),/'`

                # Write query to OUTPUT file
                echo "${QUERY}" >> ${OUTPUT}
                # Change last comma to semicolon
                sed -i '$s/,$//' "${OUTPUT}"

                echo "ON DUPLICATE KEY UPDATE ${AUX} = 0;" >> ${OUTPUT}

                # 1) If there is data in the database that is not on disk then set 
                #       ${AUX} = NULL for that night
                # 2) Delete all entries from the database that are not on disk
                # 3) ${INSERT} all entries into the database that are on disk
                echo \
                "\
                CREATE TEMPORARY TABLE TableNotDisk_${AUX}
                (
                        Telescope tinyint UNSIGNED NOT NULL,
                        NIGHT int UNSIGNED NOT NULL,
                        FAD_CONTROL_TEMPERATURE tinyint UNSIGNED,
                        BIAS_CONTROL_DYNAMIC_DATA tinyint UNSIGNED,
                        FTM_CONTROL_DATA tinyint UNSIGNED,
                        FTU_CONTROL_DATA tinyint UNSIGNED,
                        Updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 
                                ON UPDATE CURRENT_TIMESTAMP,
                        PRIMARY KEY (Telescope, NIGHT)
                ) SELECT * FROM AuxOnDisk
                        WHERE (Telescope, NIGHT) NOT IN 
                        (SELECT Telescope, NIGHT FROM AuxOnDisk_tmp)
                        AND Telescope=${TEL};

                UPDATE DataOnDisk SET auxiliary = NULL
                        WHERE DataOnDisk.NIGHT IN 
                        (SELECT NIGHT FROM AuxOnDisk_tmp)
                        AND Telescope=${TEL};

                DELETE FROM AuxOnDisk 
                        WHERE (Telescope, NIGHT) NOT IN 
                        (SELECT Telescope, NIGHT FROM AuxOnDisk_tmp)
                        AND Telescope=${TEL};

                ${INSERT} AuxOnDisk (SELECT * FROM AuxOnDisk_tmp);
                "\ >> ${OUTPUT}

        else
                # 1) If there is data in the database that is not on disk then set 
                #       ${AUX} = NULL for that night
                # 2) Delete all entries from the database that are not on disk
                # 3) ${INSERT} all entries into the database that are on disk
                echo \
                "\
                CREATE TEMPORARY TABLE TableNotDisk_${AUX}
                (
                        Telescope tinyint UNSIGNED NOT NULL,
                        NIGHT int UNSIGNED NOT NULL,
                        FAD_CONTROL_TEMPERATURE tinyint UNSIGNED,
                        BIAS_CONTROL_DYNAMIC_DATA tinyint UNSIGNED,
                        FTM_CONTROL_DATA tinyint UNSIGNED,
                        FTU_CONTROL_DATA tinyint UNSIGNED,
                        Updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 
                                ON UPDATE CURRENT_TIMESTAMP,
                        PRIMARY KEY (Telescope, NIGHT)
                ) SELECT * FROM AuxOnDisk
                        WHERE (Telescope, NIGHT) NOT IN 
                        (SELECT Telescope, NIGHT FROM AuxOnDisk_tmp)
                        AND Telescope=${TEL};

                UPDATE DataOnDisk SET auxiliary = NULL
                        WHERE DataOnDisk.NIGHT IN 
                        (SELECT NIGHT FROM AuxOnDisk_tmp)
                        AND Telescope=${TEL};

                DELETE FROM AuxOnDisk 
                        WHERE (Telescope, NIGHT) NOT IN 
                        (SELECT Telescope, NIGHT FROM AuxOnDisk_tmp)
                        AND Telescope=${TEL};

                ${INSERT} AuxOnDisk (SELECT * FROM AuxOnDisk_tmp);
                "\ >> ${OUTPUT}


                echo "No Auxiliary files."
                continue;
        fi



done