gensfw_session_procs

#!/bin/bash

MISSING_DATABASE()
{
    local -i result
    local query=(
        "SELECT COUNT(*)"
        "FROM information_schema.SCHEMATA"
        "WHERE SCHEMA_NAME='${database}'")

    result=$(mysql -BN -e "${query[*]}" )

    [ $result -eq 0 ]
}

MISSING_TABLE()
{
    local -i result
    local query=(
        "SELECT COUNT(*)"
        "FROM information_schema.TABLES"
        "WHERE TABLE_SCHEMA='${database}'"
        "AND TABLE_NAME='${table}'" )

    result=$(mysql -BN -e "${query[*]}" )

    [ $result -eq 0 ]
}

show_table_list()
{
    local -a table_array
    local query=(
        "SELECT TABLE_NAME"
        "  FROM information_schema.TABLES"
        " WHERE TABLE_SCHEMA='${database}'")

    local IFS=$'\n'
    table_array=( $(mysql -BN -e "${query[*]}" ) )
    
    if [ $? -eq 0 ]; then
        echo "${#table_array[@]} available tables:" >&2
        printf "   '%s'\n" "${table_array[@]}" >&2
        echo >&2
    fi
}

show_table_fields()
{
    local table="$1"
    local -a fields_array field_array
    local -a query=(
        "SELECT COLUMN_NAME, COLUMN_TYPE"
        "  FROM information_schema.COLUMNS"
        " WHERE TABLE_SCHEMA='${database}'"
        "   AND TABLE_NAME='${table}'"
    )

    local IFS=$'\n'
    fields_array=( $( mysql -BN -e "${query[*]}" ) )

    echo >&2
    echo "Fields of table '$table'" >&2

    IFS=$'\t'

    # get max length of field names:
    local -i tflen flen=0
    for field in "${fields_array[@]}"; do
        field_array=( $field )
        tflen="${#field_array[0]}"
        if [ "${tflen}" -gt "$flen" ]; then
            flen="${tflen}"
        fi
    done

    local format="%-${flen}s : %s"

    for field in "${fields_array[@]}"; do
        field_array=( $field )
        fname="${field_array[0]}"
        ftype="${field_array[1]}"
        printf "$format\n" "$fname" "$ftype" >&2
    done
}

get_source_path()
{
    # Must include both the colon AND the space to trim
    # the spaces from the path in the array:
    local IFS=": "
    local -a arr=( $( whereis gensfw_session_procs ) )
    zpath=$( readlink "${arr[1]}" )
    zpath="${zpath%%gensfw_session_procs}"
}

write_table_procs()
{
    local -a aquery=(
        "SELECT"
        "COLUMN_NAME, DATA_TYPE, COLUMN_KEY, COLUMN_TYPE"
        "FROM information_schema.COLUMNS"
        "WHERE TABLE_SCHEMA='${database}'"
        "AND TABLE_NAME='${table}'"
    )

    mysql -X -e "${aquery[*]}" | xsltproc "${xslpath}" -
}

show_usage()
{
    echo "Usage:" >&2
    echo "$ gensfw_session_procs [database name] [session information table name]" >&2
    echo >&2
    echo "Calling the command with a valid database name and a missing" >&2
    echo "or invalid table name will display the list of table in the" >&2
    echo "database." >&2
    echo >&2
}

get_source_path
xslpath="${zpath}gensfw_session_procs.xsl"

if [ $# -lt 1 ]; then
    show_usage
else
    database="$1"

    if MISSING_DATABASE; then
        echo "Database '$database' does not exist on this server." >&2
        echo >&2
    elif [ $# -lt 2 ]; then
        show_usage
        echo "No table requested from database '$database'" >&2
        show_table_list
    else
        table="$2"
        if MISSING_TABLE; then
            echo "Table '$table' does not exist in database '$database'" >&2
            show_table_list
        else
            show_table_fields "$table"
            write_table_procs
            exit 0
        fi
    fi
fi

exit 1