searchlto

#!/usr/bin/env bash

# searchlto
# This script performs searches against the LTO schema information stored in the
# database.

SCRIPTDIR=$(dirname $(command -v "${0}"))
RED="\033[1;31m"
GREEN="\033[1;92m"
NC="\033[0m"

. "${SCRIPTDIR}/mmfunctions" || { echo "Missing '${SCRIPTDIR}/mmfunctions'. Exiting." ; exit 1 ; }

unset VERSION
if [[ "${SCRIPTDIR}" = "/usr/local/bin" || "${SCRIPTDIR}" = "${HOME}/.linuxbrew/bin" ]] ; then
    VERSION=$(TMP=$(brew info ltopers | grep ".*\*$" | grep -Eo "/ltopers/.* \(") ; echo "${TMP:9:(${#TMP}-11)}")
fi

_usage(){
    cat <<EOF
$(basename "${0}") ${VERSION}

Usage: $(basename "${0}") [-c] [-f] [-m] {QUERY} | -h
  -c  compare input to database
  -f  show full results (as opposed to only .mov files)
  -m  search by checksums
  -h  display this help
EOF
}

_compare_file(){
    if [ -f "${INPUT}" ] ; then
        DATE=$(date -u -r "$INPUT" +%Y-%m-%dT%H:%M:%S.000000000Z)
        SIZE=$(wc -c "${INPUT}")
        SQL_RESULTS=$(mysql --login-path="${PREMIS_PROFILE}" "${PREMIS_NAME}" -B -e "SELECT modifyTime,fileSize,fileName FROM ltoSchema WHERE fileName LIKE '%${SEARCH_TERM}%'")
        LTO_SIZE=$(echo "${SQL_RESULTS}" | cut -d ' ' -f5)
        LTO_DATE=$(echo "${SQL_RESULTS}" | cut -d ' ' -f4)

        if [ -n "${SQL_RESULTS}" ] ; then
            if [ "${DATE}" != "${LTO_DATE}" ] ; then
                echo -e "${RED}WARNING: Date mismatch between file data and file records in LTO database${NC}"
            fi
            if [ $(echo "${SIZE}" | sed -e 's/^[[:space:]]*//' | cut -d' ' -f1) != "${LTO_SIZE}" ] ; then
                echo -e "${RED}WARNING: Size mismatch between file data and file records in LTO database${NC}"
            fi
            if [ "${DATE}" = "${LTO_DATE}" ] && [ $(echo "${SIZE}" | sed -e 's/^[[:space:]]*//' | cut -d' ' -f1) = "${LTO_SIZE}" ] ; then
                echo -e "${GREEN}Matching File(s) Found${NC}"
            fi
            echo "Information from Schema"
            echo "------"
            echo "${SQL_RESULTS}"
            echo
            echo "Information from File"
            echo "------"
            echo "${DATE} ${SIZE}"
        else
            echo -e "${RED}File with that name not found. Checking for files of similar size and modification date${NC}"
            SQL_RESULTS=$(mysql --login-path="${PREMIS_PROFILE}" "${PREMIS_NAME}" -B -e "SELECT modifyTime,fileSize,fileName FROM ltoSchema WHERE fileSize='$(echo "${SIZE}" | sed -e 's/^[[:space:]]*//' | cut -d' ' -f1)' OR modifyTime='${DATE}'")
            if [ -n "${SQL_RESULTS}" ] ; then
                echo -e "${GREEN}Following possible matches were found:${NC}"
                echo "${SQL_RESULTS}"
                echo "Information from file"
                echo "${DATE} ${SIZE}"
            else
                echo -e "${RED}No potential matches found${NC}"
            fi
        fi

    else
        echo "Input is not a valid file. Exiting"
        exit 1
    fi
}

OPTIND=1
while getopts ":cfmh" OPT; do
    case "${OPT}" in
        c) RUNMODE="compare" ;;
        f) RUNMODE="full" ;;
        m) RUNMODE="md5" ;;
        h) _usage ; exit 0 ;;
        *) echo "bad option -${OPTARG}" ; _usage ; exit 1 ;;
    esac
done
shift "$((OPTIND-1))"

while ! [[ "${*}" = "" ]] ; do
    SEARCH_TERM="${1}"
    shift
    if [ "${RUNMODE}" = "compare" ] ; then
        SEARCH_TERM=$(basename "${INPUT}")
        _compare_file
    elif [ "${RUNMODE}" = "md5" ] ; then
        SQL_RESULTS=$(mysql --login-path="${PREMIS_PROFILE}" "${PREMIS_NAME}" -B -e "SELECT eventDetail,messageDigestHASH,objectIdentifierValue,messageDigestPATH,messageDigestSOURCE FROM fixity WHERE messageDigestHASH='${SEARCH_TERM}'")
    else
        SQL_RESULTS=$(mysql --login-path="${PREMIS_PROFILE}" "${PREMIS_NAME}" -B -e "SELECT ltoID,filePath FROM ltoSchema WHERE ltoID='${SEARCH_TERM}'")
        if [ -z "${SQL_RESULTS}" ] ; then
            BOOLEAN_TERM=$(echo "+${SEARCH_TERM}" | sed -e "s/-/ /g" | tr -s ' ' | sed -e "s/ / +/g" )
            SQL_RESULTS=$(mysql --login-path="${PREMIS_PROFILE}" "${PREMIS_NAME}" -B -e "SELECT ltoID,filePath FROM ltoSchema WHERE MATCH(filePath) AGAINST ('${BOOLEAN_TERM}' IN BOOLEAN MODE)")
        fi
    fi
    if [ "${RUNMODE}" = "full" ] ; then
        echo "${SQL_RESULTS}"
        echo
    elif [ "${RUNMODE}" = "md5" ] ; then
        READBACK_SOURCE_CHECK=$(echo "${SQL_RESULTS}" | tr '\t' ' ' | cut -d' ' -f5 | grep ReadBack)
        if [ "${READBACK_SOURCE_CHECK}" ] ; then
            echo "${SQL_RESULTS}" | tr '\t' ' ' | cut -d' ' -f1-4 | sed -e"s/ingestcollectionchecksum/ingestcollectionchecksum(readback)/g"
        else
            echo "${SQL_RESULTS}" | tr '\t' ' ' | cut -d' ' -f1-4
        fi
        echo
    else
        echo "${SQL_RESULTS}" | grep ".mov" | grep -v "fileMeta" | grep -v "access"
        echo
    fi
done