Import sql(.gz) table dump into database and filter table according to symfony doctrine project

import_mysql_table_from_symfony.sh

#!/usr/bin/env bash
#
# Import sql and/or sql.gz table dump into database
#
# This script looks will import table defined in a symfony/doctrine project directory
# with mapping define with annotation.
# please consider using the --help option to read more about all the options available.
#

#
# Parse options from the command line
#
function parse_options
{
    DIR="$( cd "$( dirname "${BASH_SOURCE[0]}" )" && pwd )"

    # default values

    # list of table that you are using but are not defined as entity
    EXTRA_TABLE=(
        geopc
        geopc_uk
    )
    # modify this if you use another user and/or a password
    MYSQL_CMD="mysql -u root"
    PROJECT_DIR=$(realpath ${DIR})
    SQL_DATA_DIR=$(realpath ${DIR}/data)
    MIGRATION_SCRIPT=$(realpath ${DIR}/migration.sql)
    DATABASE_NAME=mydb
    DEBUG=false
    VERBOSE=true
    FORCE=false

    while [[ $# -ge 1 ]]
    do
        key="$1"

        case $key in
            -f|--force)
                FORCE=true
            ;;
            -q|--quiet)
                VERBOSE=false
            ;;
            -d|--debug)
                DEBUG=true
            ;;
            -h|--help)
                usage
                exit 0
            ;;
            --project-dir)
                PROJECT_DIR="$(realpath $2)"
                shift
            ;;
            --sql-data-dir)
                SQL_DATA_DIR="$(realpath $2)"
                shift
            ;;
            --db-name)
                DATABASE_NAME="$2"
                shift
            ;;
            *)
                usage

                echo >&2 "Unknown option name $1"
                exit 1
            ;;
        esac
        shift
    done
}

#
# Usage displayed when a wrong option is given or when the --help is provided
#
function usage
{
    local bold="\e[1m"
    local underline="\e[4m"
    local color_reset="\e[0m"
    printf "
SYNOPSIS
  $0 [options]

OPTIONS
       ${bold}-h|--help${color_reset}     display this output
       ${bold}-f|--force${color_reset}    overwrite existing table data
       ${bold}-q|--quiet${color_reset}    disable output
       ${bold}-d|--debug${color_reset}    display debug information

       ${bold}--project-dir${color_reset} ${underline}dir${color_reset}
                specify the project directory from where we'll look for ${bold}doctrine entities${color_reset} (default: ${PROJECT_DIR})

       ${bold}--sql-data-dir${color_reset} ${underline}dir${color_reset}
                specify the directory where .sql and/or .sql.gz files are located (default: ${SQL_DATA_DIR})

       ${bold}--db-name${color_reset} ${underline}name${color_reset}
                specify the database name where the data will be stored (default: ${DATABASE_NAME})
"
}

#
# Search for table from symfony/doctrine project directory
# Output the list of uniq table name
#
function list_table_to_import
{
    local tmp=$(mktemp)
    local tmp2=$(mktemp)

    grep -ri -A 1 '@ORM\\\(Join\)\?Table' ${PROJECT_DIR}/src | grep -o 'name=".*"' | sed s/name=\"//g | sed s/\"//g | sort > "${tmp}"

    # tables used but not defined as entities
    for extra_table in ${EXTRA_TABLE[@]}
    do
        if ! grep -q ^${extra_table}$ "${tmp}"
        then
            echo "${extra_table}" >> "${tmp}"
        fi
    done

    sort ${tmp} | uniq > "${tmp2}"
    cat ${tmp2}

    rm "${tmp}" "${tmp2}"
}

#
# List tables present in the SQL_DATA_DIR that won't be imported
# Output: <sql(.gz) file size> <file name>
#
function list_non_imported_table
{
    local tmp=$(mktemp)
    local tablename=

    list_table_to_import > "${tmp}"
    for table in $(ls -1 ${SQL_DATA_DIR}/*.sql)
    do
        tablename=$(basename ${table})
        if ! grep -q ${tablename%.sql} "${tmp}"
        then
            du -hs ${table}
        fi
    done

    rm "${tmp}"
}

#
# Import one table
#
function import_table
{
    local table="$1"
    local sql_file="${SQL_DATA_DIR}/${table}.sql"
    local color_bold="\e[1m"
    local color_red="\e[31m"
    local color_green="\e[32m"
    local color_yellow="\e[33m"
    local color_reset="\e[0m"

    if [ -f "${sql_file}" ]
    then
        CAT=$(which cat)
    elif [ -f "${sql_file}.gz" ]
    then
        sql_file="${sql_file}.gz"
        CAT=$(which zcat)
    else
        echo -e >&2 "${color_red}${color_bold}${table}${color_reset}${color_red}... Unable to find a suitable file${color_reset}"
        continue
    fi

    ${DEBUG} && echo -e "using ${color_bold}${CAT}${color_reset} binary on ${color_bold}${sql_file}${color_reset}"

    ${VERBOSE} && echo -ne "${color_bold}${table}${color_reset}... "
    if ! ${FORCE} && [ -n "$(${MYSQL_CMD} -e "SELECT * FROM information_schema.tables WHERE table_schema = '${DATABASE_NAME}' AND table_name = '${table}'")" ]
    then
        FORCE_REQUIRED=true
        ${VERBOSE} && echo -e "${color_yellow}skipped${color_reset}"
    else
        ${CAT} "${sql_file}" | ${MYSQL_CMD} ${DATABASE_NAME}
        ${VERBOSE} && echo -e "${color_green}done${color_reset}"
    fi
}

function run_migration_script
{
    if [ -f "${MIGRATION_SCRIPT}" ]
    then
        ${VERBOSE} && echo -n "running post import migration script..."
        cat ${MIGRATION_SCRIPT} | ${MYSQL_CMD} ${DATABASE_NAME}
        ${VERBOSE} && echo "done"
    fi
}

function main
{
    parse_options $@

    FORCE_REQUIRED=false

    ${DEBUG} && echo "Project directory: "${PROJECT_DIR}
    ${DEBUG} && echo "SQL data directory: "${SQL_DATA_DIR}
    ${DEBUG} && echo "Disk usage before: "
    ${DEBUG} && df -h /

    if [ ! -d "${PROJECT_DIR}" ]
    then
        echo >&2 "${PROJECT_DIR} does not correspond to a valid directory"
        exit 1
    fi

    if [ ! -d "${SQL_DATA_DIR}" ]
    then
        echo >&2 "${SQL_DATA_DIR} does not correspond to a valid directory"
        exit 1
    fi

    if [ -z "$(find ${SQL_DATA_DIR} -name "*.sql")" ] && [ -z "$(find ${SQL_DATA_DIR} -name "*.sql.gz")" ]
    then
        echo >&2 "You need to download <table>.sql(.gz) files and put them in ${SQL_DATA_DIR} directory"
        exit 1
    fi

    list_table_to_import | while read table
    do
        import_table "${table}"
    done

    run_migration_script

    if ${FORCE_REQUIRED}
    then
        echo -e "\e[33mOne or more tables already exists and were skipped. You can run this script with the option \e[1;93m--force\e[0;33m to include them\e[0m"
    fi

    ${DEBUG} && echo "Disk usage after: "
    ${DEBUG} && df -h /
}

main $@