A small unofficial script to check data mappings in LA collectory

check-mappings

#!/bin/bash
#
# A small unofficial script to check data mappings in LA collectory
#
# Installation:
# 
#   - Copy this script in /usr/local/bin of a collectory host (for instance)
#   - Put 'docopts' in /usr/local/bin (See INSTALL of: https://github.com/docopt/docopts), or just
#     download https://github.com/Sylvain303/docopts/releases/download/v0.6.3-alpha1/docopts and put
#     in /usr/local/bin of a collectory
#   - Create a /etc/mysql/default-extra-login-collectory.conf file with:
#
#     [client]
#     user = collectory
#     password = itspassword
#     host = localhost
#
#  Usage example:
#  $ check-mappings -d -e dr420

NAME=$(basename $0)
ERRORS=0

FIND_DOC_OPTS=$(which docopts)

if [[ $? != 0 ]]
then
    echo "ERROR: Please install docopts https://github.com/docopt/docopts an copy in your PATH"
    exit 1
fi

eval "$(docopts -V - -h - : "$@" <<EOF
Usage:
       $NAME [options] <resource>...
       $NAME [options] --all
       $NAME [options] --limit=<n>
       $NAME -h | --help
       $NAME -v | --version

Options:
  -h --help            Show help options.
  -d --debug           Show debug info.
  -e --expand          Show de debug info expanded
  -m --mysqlverbose    Show de mysql queries
  -v --version         Show $NAME version.
----
$NAME 0.1.0
Copyright (C) 2018 gbif.es
License GPLv3
EOF
)"

function prop {
  grep "${1}" /data/ala-collectory/config/ala-collectory-config.properties|cut -d'=' -f2
}

function my {
    if ${mysqlverbose} ; then (>&2 echo "${1}"); fi
    echo $(mysql --defaults-extra-file=/etc/mysql/default-extra-login-collectory.conf collectory -s -N -e "${1}")
}

function echop {
    printf "${1}\n"
}

# TODO use collectory dataSource.url
# echo $(prop dataSource.user)
# echo $(prop dataSource.password)

if ${expand} ; then EOPS="\n" ; else EOPS=""; fi

if ${all}
then
    DRS=$(my "select uid from data_resource")
else
    if [[ ${limit} -gt 0 ]]
    then
	DRS=$(my "select uid from data_resource limit ${limit}")	
    else
	DRS=${resource[@]}
    fi
fi

for DR in $DRS
do

    DR_EXISTS=$(my "select count(*) from data_resource where uid='$DR' LIMIT 1")
    if [[ $DR_EXISTS -ne 1 ]]  ; then echo "ERROR: $DR not found" ; ERRORS=$[ERRORS + 1]; continue; fi
    
    MSG="INFO: Resource $DR"
    ACRO=$(my "select acronym from data_resource where uid='$DR' LIMIT 1")
    if [ "${ACRO}" == "NULL" ]; then ACRO="None"; fi
    MSG="${MSG},${EOPS} res. acronym '$ACRO'"

    INST_ID=$(my "select institution_id from data_resource where uid='$DR' LIMIT 1")
    INST=$(my "select uid from institution where id='$INST_ID'")
    MSG="${MSG},${EOPS} institution '${INST}'"
    
    AINST=$(my "select acronym from institution where uid='$INST' LIMIT 1")
    MSG="${MSG},${EOPS} inst. acronym '${AINST}'"

    COLP_COUNT=$(my "select count(*) from data_link where provider='$DR' and consumer LIKE 'co%'")
    INSTP_COUNT=$(my "select count(*) from data_link where provider='$DR' and consumer LIKE 'in%'")

    if [[ $COLP_COUNT -ne 1 ]]  ; then echo "ERROR: No collection consumer for $DR" ; ERRORS=$[ERRORS + 1]; fi
    if [[ $INSTP_COUNT -ne 1 ]] ; then echo "ERROR: No institution consumer for $DR $(echo $INST $AINST| xargs)"; ERRORS=$[ERRORS + 1]; fi
    if [[ $INSTP_COUNT -ne 1 || $COLP_COUNT -ne 1 ]] ; then echop "$MSG";  continue; fi

    COLP=$(my "select consumer from data_link where provider='$DR' and consumer LIKE 'co%'")
    INSTP=$(my "select consumer from data_link where provider='$DR' and consumer LIKE 'in%'")
    MSG="${MSG},${EOPS} collection consumer '${COLP}', institution consumer '${INSTP}'"

    COL_ID=$(my "select id from collection where uid='$COLP'");
    COLACRO=$(my "select acronym from collection where id='$COL_ID'")

    INST_ID=$(my "select id from institution where uid='$INSTP'");
    MSG="${MSG},${EOPS} institution id '${INST_ID}'"
    #TODO check INST_ID is valid
    
    if [ "NULL" == "$COLACRO" ] ; then COLACRO=""; fi

    if [[ -z "$COLACRO" ]] ; then echo "ERROR: No acronym for collection $COLP"; ERRORS=$[ERRORS + 1]; echop "$MSG"; continue ; fi
    MSG="${MSG},${EOPS} collection id '${COL_ID}' acronym '$COLACRO'"

    INST_MAP_ID=$(my "select id from provider_map where institution_id='$INST_ID'")
    INST_MAP_COUNT=$(my "select count(*) from provider_map where institution_id='$INST_ID'")
    
    if [[ $INST_MAP_COUNT -lt 1 ]] ; then echo "ERROR: Wrong number of institution map providers ($INST_MAP_ID)"; ERRORS=$[ERRORS + 1]; fi
					  
    PROV_MAP_ID=$(my "select id from provider_map where collection_id='$COL_ID'")
    if [[ -z "$PROV_MAP_ID" ]] ; then echo "ERROR: No provider map for collection $COLP ($COL_ID)"; ERRORS=$[ERRORS + 1]; echop "$MSG"; continue ; fi
    MSG="${MSG},${EOPS} provider map id for this col '${PROV_MAP_ID}'"

    # Institutions
    PROVS_ID=$(my "SELECT distinct provider_code_id FROM provider_map_provider_code WHERE exists (SELECT 1 FROM provider_map WHERE provider_map.institution_id='$INST_ID' AND provider_map.id=provider_map_provider_code.provider_map_institution_codes_id)")		               
    MSG="${MSG},${EOPS} institution providers map ids ('${PROVS_ID}')"
    INST_MAP_MATCH=0 
    for PROV_ID in $PROVS_ID
    do
	PROV_CODE=$(my "select code from provider_code where id=${PROV_ID}")
	MSG="${MSG},${EOPS} institution providers map acronym '${PROV_CODE}'"
	if [[ $PROV_CODE == $AINST ]] ; then INST_MAP_MATCH=1; fi
    done
    if [[ $INST_MAP_MATCH -eq 0 ]] ; then echo "ERROR: we cannot find a provider map for resource institution acronym '$AINST'"; ERRORS=$[ERRORS + 1]; echop "$MSG"; continue ; fi
    
    # Collections
    PROVS_ID=$(my "select distinct provider_code_id from provider_map_provider_code where provider_map_collection_codes_id=${PROV_MAP_ID}")
    MSG="${MSG},${EOPS} collection providers map ids ('${PROVS_ID}')"
    for PROV_ID in $PROVS_ID
    do
	PROV_CODE=$(my "select code from provider_code where id=${PROV_ID}")
	MSG="${MSG},${EOPS} collection providers map acronym '${PROV_CODE}'"
    done
    
    if ${debug} ; then echop "$MSG"; fi    
	   
done

if [[ $ERRORS -gt 0 ]] ; then exit 1; else exit 0; fi