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

#!/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, ISPRECESSED) 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,
        STATUS tinyint UNSIGNED NOT NULL,
        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,
        STATUS tinyint UNSIGNED NOT NULL,
        Updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 
                ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (Telescope, NIGHT, RUNID, ISDRSFILE)
); 

INSERT INTO DataOnDisk_tmp (Telescope, NIGHT, RUNID, ISDRSFILE, STATUS)
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, ISPROCESSED),
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) \
        <(for i in ${NRID}; do echo "0"; 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, ISPROCESSED),
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) \
        <(for i in ${DNRID}; do echo "0"; 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 status = 0
#    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,
        STATUS tinyint UNSIGNED NOT NULL,
        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);

UPDATE DataOnDisk SET STATUS = 0 WHERE NIGHT IN 
        (SELECT NIGHT FROM TableNotDisk);

DELETE FROM DataOnDisk 
        WHERE (Telescope, NIGHT, RUNID, ISDRSFILE) NOT IN 
        (SELECT Telescope, NIGHT, RUNID, ISDRSFILE FROM DataOnDisk_tmp);

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