make_db

#!/bin/bash

# This file is a part of the LinkAhead Project.
#
# Copyright (C) 2021, 2024 Indiscale GmbH <info@indiscale.com>
# Copyright (C) 2019, 2020, 2021, 2024 Daniel Hornung <d.hornung@indiscale.com>
# Copyright (C) 2020 Timm Fitschen <t.fitschen@indiscale.com>
# Copyright (C) 2020 Henrik tom Wörden <h.tomwoerden@indiscale.com>
# Copyright (C) 2020 IndiScale <info@indiscale.com>
#
# This program is free software: you can redistribute it and/or modify
# it under the terms of the GNU Affero General Public License as
# published by the Free Software Foundation, either version 3 of the
# License, or (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU Affero General Public License for more details.
#
# You should have received a copy of the GNU Affero General Public License
# along with this program. If not, see <https://www.gnu.org/licenses/>.
#

# Although some sanity checks are performed, this script still allows lots of SQL injection
# possibilities.


set -e

INSTALL_SQL_FILE="db_5_0.sql"

if [ -z "$UTILSPATH" ]; then
    UTILSPATH="$(realpath $(dirname $0))"
    export UTILSPATH
fi

source $UTILSPATH/load_settings.sh
source $UTILSPATH/helpers.sh

UNITTEST_DATABASE=${UNITTEST_DATABASE-_caosdb_schema_unit_tests}

# optional parameter: [--fresh] for installing a fresh data base. Otherwise an existing one would be reused.
function runtests() {
    DATABASE_NAME=$UNITTEST_DATABASE
    _setup_mytap

    _install_unit_test_database $@

    _execute_tests || ( failure )
    echo "[PASS]"
}

function _execute_tests () {
    pushd tests
    TESTS="./test*.sql"

    rm -f .TEST_RESULTS
    for tfile in $TESTS ; do
        echo "Running $tfile"
        echo "----- $tfile -----" >> .TEST_RESULTS
        < $tfile $MYSQL_CMD \
          -D $UNITTEST_DATABASE $(get_db_args_nodb) --disable-pager \
          --batch --raw --skip-column-names --unbuffered \
          >> .TEST_RESULTS 2>&1
    done;

    popd
    cat tests/.TEST_RESULTS
    grep -c -i "failed" tests/.TEST_RESULTS > /dev/null && return 1
    grep -c -i "ERROR" tests/.TEST_RESULTS > /dev/null && return 1
    return 0
}

# install/reset database for unit tests.
# optional parameter: [--fresh] for installing a fresh data base. Otherwise an existing one would be reused.
function _install_unit_test_database () {
    DATABASE_NAME=$UNITTEST_DATABASE
    if [[ $1 = "--fresh" ]] ; then
        if _db_exists "$DATABASE_NAME"; then
            drop "$DATABASE_NAME" ;
        fi
    fi

    if _db_exists "$DATABASE_NAME"; then
        echo "using $DATABASE_NAME"
    else
        sed "s/db_5_0/$DATABASE_NAME/g" "$INSTALL_SQL_FILE" | $MYSQL_CMD $(get_db_args_nodb)

        # create test user
        grant
    fi


    echo "DATABASE_NAME=\"$UNITTEST_DATABASE\"" >> .test_config
    pushd patches > /dev/null
    export ENV_FILE=$(realpath ../.test_config)
    ./applyPatches.sh
    unset ENV_FILE
    popd > /dev/null
    rm .test_config
}

# install test framework MyTAP if not installed
function _setup_mytap() {
    if _db_exists "tap" ; then
        echo MyTAB framework is already installed [OK]
        return 0
    fi
    echo -n "Installing MyTAP framework ... "
    pushd libs > /dev/null
    unzip -u mytap*.zip > /dev/null
    pushd mytap*/ > /dev/null
    $MYSQL_CMD $(get_db_args_nodb) < mytap.sql > /dev/null || exit 1
    popd > /dev/null
    rm -r mytap*/
    popd > /dev/null
    echo "[DONE]"
}

function install_db() {
    if _db_exists ; then
        echo -e "\n
A database with with the name '$DATABASE_NAME' exists already.
Call 'make drop-$DATABASE_NAME' to delete that database or change the database
name in your .config file "
        exit 0
    fi

    sed "s/db_5_0/$DATABASE_NAME/g" "$INSTALL_SQL_FILE" | $MYSQL_CMD $(get_db_args_nodb)
}

function sanity_check() {
    trap "$(shopt -po errexit)" RETURN
    set +e
    echo -n "running sanity checks ..."
    msg="$($MYSQL_CMD $(get_db_args) < "utils/sanity_check.sql" 2>/dev/null)"
    code="$?"
    if [ "$code" -eq "0" ] ; then
        echo " [OK]"
    else
        echo " [FAILED]"
        echo "$msg"
        exit 1
    fi
}

# Inserts the dump (arg 1) into the database
function restore_db() {
    SQL_FILE="$1"
    $MYSQL_CMD $(get_db_args) < "$SQL_FILE"
    cat <<EOF
If this is not the same SQL server where the SQL dump was originally created
from, make sure that a user with sufficient permissions exists.  Note that you
can use the 'grant' action of this script to do this.
EOF
}

function test-connection() {
    $MYSQL_CMD $(get_db_args_nodb) -e "select 0;"
}

# Creates a user and grants it sufficient rights.
# If called with --strict, the user is not allowed to exist previously.
function grant() {
    if [ -z "$DATABASE_USER_PW" ] ; then
        while true; do
            read -p "Please enter the password for ${DATABASE_USER}: " \
                 -s password
            echo
            read -p "Please repeat the password: " -s password2
            echo
            [[ "$password" = "$password2" ]] && {
                DATABASE_USER_PW="$password"
                break
            }
            echo -e "\nThe passwords didn't match. Try again."
        done
    fi

    if [[ $1 = "--strict" ]] ; then
        for host in ${DATABASE_USER_HOST_LIST//,/ } ; do
            CMD="SELECT COUNT(*) FROM mysql.user WHERE user='${DATABASE_USER}' AND host='${host}';"
            [[ $($MYSQL_CMD $(get_db_args_nodb) -s -N -e "$CMD") = 0 ]] || {
                echo "The user '${DATABASE_USER}@${host}' is already in the database."
                echo "Please use another user or delete it, e.g. with"
                echo "'mysql -u ${MYSQL_USER} -p -e \"DROP USER ${DATABASE_USER}@${host};\"'"
                exit 1
            }
        done
    fi

    for host in ${DATABASE_USER_HOST_LIST//,/ } ; do
        echo "Granting admin privileges to '$DATABASE_USER'@'$host'"
        # FIXME Are all these permissions necessary? See
        # https://gitlab.indiscale.com/caosdb/src/caosdb-mysqlbackend/-/issues/28 "Default
        # installation target does not work for existing databases"
        $MYSQL_CMD $(get_db_args_nodb) <<EOF
CREATE USER IF NOT EXISTS
    '$DATABASE_USER'@'$host' identified by '$DATABASE_USER_PW';
GRANT USAGE ON *.* TO '$DATABASE_USER'@'$host';
GRANT ALL PRIVILEGES ON *.* TO '$DATABASE_USER'@'$host' WITH GRANT OPTION;
GRANT EXECUTE ON *.* TO '$DATABASE_USER'@'$host';
EOF
    done
}

# Drops the caosdb user plus a given database from all listed database hosts.
# The first argument to this function is the database that shal be dropped.
function drop() {
    DROPDB="$1"
    for host in ${DATABASE_USER_HOST_LIST//,/ } ; do
        $MYSQL_CMD $(get_db_args_nodb) -e "DROP USER '${DATABASE_USER}'@'${host}';" || true
    done
    "$MYSQLADMIN_CMD" $(get_db_args_nodb) -f drop "$DROPDB"
}


# Returns 0 or non-zero, depending on whether the database exists already.
# Optional parameter: [DATABASE_NAME], else $DATABASE_NAME is used.
function _db_exists() {
    $MYSQL_CMD $(get_db_args_nodb) -D "${1-${DATABASE_NAME}}" -e "show tables;" > /dev/null 2>&1 \
        && return 0 || return 1
}


# Grant the given permissions to the given role.
#
# Arguments
# ---------
# role : str
# The role, may consist of alphanumerical letters plus `.`, `_`, `-`.  The role must exist in the
# `roles` table.
#
# permissions : str
# The permissions string.  May not contain single quotes, should be similar to:
# [{"grant":"true","priority":"true","permission":"*"}]
function grant-permission() {
    role="$1"
    permissions="$2"
    if echo -n "$role" | grep -v -q "^[[:alnum:]._-]*$" ; then
        echo "Role contains invalid character(s)!"
        exit 1
    fi
    if [[ $permissions == "'" ]]; then
        echo "Permissions string contains single quote!"
        exit 1
    fi

    cmd="SELECT COUNT(1) from roles where name='${role}';"
    count=$($MYSQL_CMD $(get_db_args) -AN -e "$cmd")
    if [[ $count == "0" ]]; then
        echo "Role not found!"
        exit 1
    fi

    cmd="INSERT INTO permissions (role, permissions) VALUE ('${role}', '${permissions}')"
    cmd+="ON DUPLICATE KEY UPDATE role='${role}', permissions='${permissions}'"
    cmd+=";"
    $MYSQL_CMD $(get_db_args) -e "$cmd"
}


case $1 in
    "drop") drop $2 ;;
    "grant") grant $2 ;;
    "grant-permission") grant-permission $2 $3 ;; # Args: role, permissions
    "test") shift ; runtests $@ ;;
    "test-connection") test-connection ;;
    "install_db") install_db ;;
    "restore_db") restore_db $2 ;;
    "sanity_check") sanity_check ;;
    *) echo "Unknown action: $1"; exit 32
esac