source: trunk/Mars/hawc/processing/DiskToDB/update-file-table2.sh@ 20106

#!/bin/bash

# Recursively finds all .fits.fz and .drs.fits files in DIR and extract 
# NIGHT, RUNID and if the file is a DRS file and writes an SQL query with the 
# columns: (Telescope, NIGHT, RUNID, ISDRSFILE, 
# header, calibration, auxiliary, callisto, star) to OUTPUT.
#
# Example:
#       bash update-file-table2.sh 1 /data/HE01/raw

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-OnDisk.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 DataOnDisk
(
        Telescope tinyint UNSIGNED NOT NULL,
        NIGHT int UNSIGNED NOT NULL,
        RUNID mediumint UNSIGNED NOT NULL,
        ISDRSFILE tinyint(1) NOT NULL,
        header tinyint UNSIGNED,
        calibration tinyint UNSIGNED,
        auxiliary smallint UNSIGNED,
        callisto tinyint UNSIGNED,
        star tinyint UNSIGNED,
        Updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 
                ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (Telescope, NIGHT, RUNID, ISDRSFILE)
); 

CREATE TEMPORARY TABLE DataOnDisk_tmp
(
        Telescope tinyint UNSIGNED NOT NULL,
        NIGHT int UNSIGNED NOT NULL,
        RUNID mediumint UNSIGNED NOT NULL,
        ISDRSFILE tinyint(1) NOT NULL,
        header tinyint UNSIGNED,
        calibration tinyint UNSIGNED,
        auxiliary smallint UNSIGNED,
        callisto tinyint UNSIGNED,
        star tinyint UNSIGNED,
        Updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 
                ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (Telescope, NIGHT, RUNID, ISDRSFILE)
); 

INSERT INTO DataOnDisk_tmp (Telescope, NIGHT, RUNID, ISDRSFILE)
VALUES
"\ > ${OUTPUT}

# Find query for .fits.fz files
# Crop filenames from the form /nnnnnnnn_ddd.fits.fz$ to nnnnnnnn_ddd
NRID=`find "${DIR}" -type f \
        | grep -o "/[0-9]\{8\}_[0-9]\{3\}.fits.fz$" \
        | cut -f2 -d "/" \
        | cut -f1 -d "."`

# Format to (Telescope, NIGHT, RUNID, ISDRSFILE),
FZQUERY=`paste <(for i in ${NRID}; do echo ${TEL}; done) \
        <(echo "${NRID}" | cut -f1 -d "_") \
        <(echo "${NRID}" | cut -f2 -d "_") \
        <(for i in ${NRID}; do echo "false"; done) \
        --delimiters "," \
        | sed 's/^/(/' \
        | sed 's/$/),/'`

# Find query for .drs.fits files
# Crop filenames from the form nnnnnnnn_ddd.drs.fits to nnnnnnnn_ddd
DNRID=`find "${DIR}" -type f \
        | grep -o "/[0-9]\{8\}_[0-9]\{3\}.drs.fits$" \
        | cut -f2 -d "/" \
        | cut -f1 -d "."`

# Format to (Telescope, NIGHT, RUNID, ISDRSFILE),
DRSQUERY=`paste <(for i in ${DNRID}; do echo ${TEL}; done) \
        <(echo "${DNRID}" | cut -f1 -d "_") \
        <(echo "${DNRID}" | cut -f2 -d "_") \
        <(for i in ${DNRID}; do echo "true"; done) \
        --delimiters "," \
        | sed 's/^/(/' \
        | sed 's/$/),/'`

# Write query to OUTPUT file
echo "${FZQUERY}" >> ${OUTPUT}
echo "${DRSQUERY}" >> ${OUTPUT}

# Change last comma to semicolon
sed -i '$s/,$/;/' "${OUTPUT}"

# 1) If there is data in the database that is not on disk then set 
#       header = 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
(
        Telescope tinyint UNSIGNED NOT NULL,
        NIGHT int UNSIGNED NOT NULL,
        RUNID mediumint UNSIGNED NOT NULL,
        ISDRSFILE tinyint(1) NOT NULL,
        header tinyint UNSIGNED,
        calibration tinyint UNSIGNED,
        auxiliary smallint UNSIGNED,
        callisto tinyint UNSIGNED,
        star tinyint UNSIGNED,
        Updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 
                ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (Telescope, NIGHT, RUNID, ISDRSFILE)
) SELECT * FROM DataOnDisk
        WHERE (Telescope, NIGHT, RUNID, ISDRSFILE) NOT IN 
        (SELECT Telescope, NIGHT, RUNID, ISDRSFILE FROM DataOnDisk_tmp)
        AND Telescope=${TEL};

UPDATE DataOnDisk SET header = NULL WHERE NIGHT IN 
        (SELECT NIGHT FROM TableNotDisk)
        AND Telescope=${TEL};

DELETE FROM DataOnDisk 
        WHERE (Telescope, NIGHT, RUNID, ISDRSFILE) NOT IN 
        (SELECT Telescope, NIGHT, RUNID, ISDRSFILE FROM DataOnDisk_tmp)
        AND Telescope=${TEL};

${INSERT} DataOnDisk (SELECT * FROM DataOnDisk_tmp);
"\ >> ${OUTPUT}