create_dm_db.sh

#!/bin/sh

#
# Script used for creating DM database
# Deployment configuration can be set in etc/$DM_DB_NAME.deploy.conf file
#
# Usage:
#
# $0 [DM_DB_NAME [DM_DB_SCRIPTS_DIR]]
#

DM_DB_NAME=dm
DM_DB_USER=dm
DM_DB_PASSWORD=dm
DM_DB_HOST=127.0.0.1
DM_DB_PORT=11136
DM_DB_ADMIN_USER=postgres
DM_DB_ADMIN_HOSTS="127.0.0.1 bluegill1.aps.anl.gov visa%.aps.anl.gov"
DM_DB_ADMIN_PASSWORD=
DM_DB_CHARACTER_SET=utf8


CURRENT_DIR=`pwd`
MY_DIR=`dirname $0` && cd $MY_DIR && MY_DIR=`pwd`
cd $CURRENT_DIR

if [ -z "${DM_ROOT_DIR}" ]; then
    DM_ROOT_DIR=$MY_DIR/..
fi
DM_SQL_DIR=$DM_ROOT_DIR/db
DM_RUN_DIR=$DM_ROOT_DIR/var/run
DM_ENV_FILE=${DM_ROOT_DIR}/setup.sh
if [ ! -f ${DM_ENV_FILE} ]; then
    echo "Environment file ${DM_ENV_FILE} does not exist." 
    exit 1
fi
. ${DM_ENV_FILE} > /dev/null 

# DM_SUPPORT_DIR should now be defined.
if [ -z "$DM_SUPPORT_DIR" ]; then
    echo "Invalid environment file ${DM_ENV_FILE}: DM_SUPPORT_DIR is not defined."
    exit 1
fi
PG_DIR=$DM_SUPPORT_DIR/postgresql/$DM_HOST_ARCH
PG_CONTROL_SCRIPT=$DM_ROOT_DIR/etc/init.d/dm-postgresql
PG_DATA_DIR=$PG_DIR/data
PG_HBA_CONF=$PG_DATA_DIR/pg_hba.conf
DB_LOCK_FILE=${DM_RUN_DIR}/${DM_DB_NAME}.db.lock
DB_CREATE_SCRIPT=${DM_ROOT_DIR}/db/create_${DM_DB_NAME}_db.sql

# Use first argument as db name, if provided
if [ ! -z "$1" ]; then
    DM_DB_NAME=$1
fi
echo "Using DB name: $DM_DB_NAME"

# Look for deployment file in etc directory, and use it to override
# default entries
deployConfigFile=$DM_ROOT_DIR/etc/${DM_DB_NAME}.deploy.conf
if [ -f $deployConfigFile ]; then
    echo "Using deployment config file: $deployConfigFile"
    . $deployConfigFile
else
    echo "Deployment config file $deployConfigFile not found, using defaults"
fi

# Second argument overrides directory with db population scripts
DM_DB_SCRIPTS_DIR=${DM_DB_SCRIPTS_DIR:=$DM_SQL_DIR}
if [ ! -z "$2" ]; then
    DM_DB_SCRIPTS_DIR=$2
fi
if [ ! -d $DM_DB_SCRIPTS_DIR ]; then
    echo "DB Scripts directory $DM_DB_SCRIPTS_DIR does not exist."
    exit 1
fi

echo "Using DB scripts directory: $DM_DB_SCRIPTS_DIR"

execute() {
    msg="$@"
    if [ ! -z "$DM_DB_ADMIN_PASSWORD" ]; then
        sedCmd="s?$DM_DB_ADMIN_PASSWORD?\\*\\*\\*\\*\\*\\*?g"
        echo "Executing: $@" | sed -e $sedCmd
    else
        echo "Executing: $@"
    fi
    eval "$@"
}

createDb() {
    if [ -f $DB_LOCK_FILE ]; then
        echo "Database lock file $DB_LOCK_FILE already exists, will not proceed."
        return 1
    fi
    echo "Creating database $DM_DB_NAME"
    ${PG_DIR}/bin/createdb -U $DM_DB_USER -h $DM_DB_HOST -p $DM_DB_PORT $DM_DB_NAME || return 1
    echo "Creating database schema for $DM_DB_NAME"
    ${PG_DIR}/bin/psql -U $DM_DB_USER -h $DM_DB_HOST -p $DM_DB_PORT -d $DM_DB_NAME -f $DB_CREATE_SCRIPT || return 1
    touch $DB_LOCK_FILE
}

createDbUser() {
    _dbUser=$1
    shift 1
    _createFlags=$@

    # create user
    ${PG_DIR}/bin/createuser -h $DM_DB_HOST -p $DM_DB_PORT $_createFlags $_dbUser || return 1
}

modifyDbUserPassword() {
    _dbUser=$1
    _dbPassword=$2
    _tmpFile=/tmp/$$.sql
    echo "alter user $_dbUser with password '$_dbPassword';" > $_tmpFile
    $PG_DIR/bin/psql -h $DM_DB_HOST -p $DM_DB_PORT -d postgres -f $_tmpFile
    rm -f $_tmpFile
}

# Check for failed earlier attempt
if [ -f $PG_HBA_CONF.orig ]; then
    echo "File $PG_HBA_CONF.orig exists, refusing to proceed."
    exit 1
fi

# Initialize db if needed
dbAction=restart
createDbRootUser=false
if [ ! -f $PG_HBA_CONF ]; then
    $PG_CONTROL_SCRIPT initdb || exit 1
    dbAction=start
    createDbRootUser=true
fi

# Modify db permissions, restart db.
echo "Modifying db access permissions"
mv $PG_HBA_CONF $PG_HBA_CONF.orig
cat $PG_HBA_CONF.orig | sed 's?host.*all.*all.*127.*?host all all 127.0.0.1/32 trust?g' | sed 's?host.*all.*all.*::1/128.*?host all all ::1/128 trust?g' > $PG_HBA_CONF || exit 1
echo "Restarting database server"
$PG_CONTROL_SCRIPT $dbAction

# Create db root user if needed
sttyOrig=`stty -g`
if [ $createDbRootUser = "true" ]; then
    # Read db password if needed 
    if [ -z "$DM_DB_ADMIN_PASSWORD" ]; then
        stty -echo
        read -p "Enter DB password for the $DM_DB_ADMIN_USER (DB root) user: " DM_DB_ADMIN_PASSWORD
        echo
        stty $sttyOrig
    fi

    # Create postgres user 
    echo "Creating $DM_DB_ADMIN_USER"
    createDbUser $DM_DB_ADMIN_USER -E -s || exit 1
    modifyDbUserPassword $DM_DB_ADMIN_USER $DM_DB_ADMIN_PASSWORD || exit 1
fi

# Read user db password if needed 
if [ -z "$DM_DB_USER_PASSWORD" ]; then
    stty -echo
    read -p "Enter DB password for the $DM_DB_USER user: " DM_DB_USER_PASSWORD
    echo
    stty $sttyOrig
fi

# Create db user
echo "Creating $DM_DB_USER"
createDbUser $DM_DB_USER -E -d || exit 1
modifyDbUserPassword $DM_DB_USER $DM_DB_USER_PASSWORD || exit 1

# Create db
echo "Creating database: $DM_DB_NAME"
createDb || exit 1

# populate db
cd $CURRENT_DIR && cd $DM_DB_SCRIPTS_DIR
DM_DB_TABLES="\
"
#for dbTable in $DM_DB_TABLES; do
#    dbFile=populate_$dbTable.sql
#    if [ -f $dbFile ]; then
#        echo "Populating $dbTable using $dbFile script"
#        execute $mysqlCmd $dbFile
#    else
#        echo "$dbFile not found, skipping $dbTable update"
#    fi
#done

# cleanup
echo "Restoring db access permissions"
if [ $createDbRootUser = "true" ]; then
    # db was just created, enable password access
    cat $PG_HBA_CONF.orig | sed 's?host.*all.*all.*127.*?host all all 127.0.0.1/32 md5?g' | sed 's?host.*all.*all.*::1/128.*?host all all ::1/128 md5?g' > $PG_HBA_CONF || exit 1
    rm -f $PG_HBA_CONF.orig 
else
    # no changes needed to original hba file
    mv $PG_HBA_CONF.orig $PG_HBA_CONF
fi

echo "Restarting database server"
$PG_CONTROL_SCRIPT restart

echo "Database $DM_DN_NAME created successfully"
echo