Stephen Viles

The following Bash script is designed to be run from a crontab entry. It performs a database update (using Oracle's sqlplus), checks the output for errors, and adds a success or failure comment to a JIRA ticket by sending an email to the JIRA instance.

A JIRA instance only accepts email from the addresses of its registered users, so I had to use sendmail with an email constructed from scratch in order to control the From: address. The issue key is specified in the Subject: line.

Note the signature block of the email (after the -- line), which identifies where the script is running, and as which user — crucial information if you want to modify its behaviour.

Caution: You don't want to use too many of these out-of-band database updates. Like database triggers, they have their uses, but do not scale well. A system with more than a few of these scripts will be hard to understand, and harder to modify. Temporary solutions often become permanent, and permanent solutions should be inside your application, not outside it.

#! /bin/bash
set -u
SCRIPT_NAME=$(basename $BASH_SOURCE)
SCRIPT_DIR=$(dirname $(readlink -f $0))
SQL_OUTPUT=/tmp/$SCRIPT_NAME-$$.out
DB_CREDENTIALS=

# Do the update
sqlplus $DB_CREDENTIALS 2>&1 >> $SQL_OUTPUT << EOF
set echo on
@$SCRIPT_DIR/do-it.sql
EOF

# Look for Oracle errors
ERROR_LIST=/tmp/$SCRIPT_NAME-$$.err
grep 'ORA-' $SQL_OUTPUT >> $ERROR_LIST
grep 'SP2-' $SQL_OUTPUT >> $ERROR_LIST

# Look for success messages
function look_for() {
    grep --silent "$1" $SQL_OUTPUT
    RETURNVAL=$?
    if [[ $RETURNVAL -ne 0 ]] ; then
        echo "Did not find expected output: $1" >> $ERROR_LIST
    fi
}

look_for '1 row updated.'
look_for 'Commit complete.'

# Start the log, which is in email format
SCRIPT_LOG=/tmp/$SCRIPT_NAME-$$.log
cat >> $SCRIPT_LOG << EOF
To: jira@example.com,me@example.com
From: me@example.com
Subject: PRJ-12345

EOF

# Build body of log
if [ -s "$ERROR_LIST" ] ; then
    echo "Errors found while attempting to do it:" >> $SCRIPT_LOG
    cat $ERROR_LIST >> $SCRIPT_LOG
    echo >> $SCRIPT_LOG
    echo "Full command output:" >> $SCRIPT_LOG
    cat $SQL_OUTPUT >> $SCRIPT_LOG
else
    echo "Done it" >> $SCRIPT_LOG
fi

# Add script detail to log and send it to JIRA ticket
cat >> $SCRIPT_LOG << EOF

--
Sent by script $SCRIPT_DIR/$SCRIPT_NAME running on host `hostname`.`dnsdomainname` as user `whoami`
EOF
/usr/lib/sendmail -t < $SCRIPT_LOG >> $ERROR_LIST 2>&1

RETURNVAL=$?
if [[ $RETURNVAL -ne 0 ]] ; then
    echo "sendmail had a non-zero exit code, see above for output" >> $ERROR_LIST
else
    rm $SQL_OUTPUT $ERROR_LIST $SCRIPT_LOG
fi

Tags: jira, shell