Git - Database dump post-commit hook

conn

DB_HOST='server_name'
DB_USER='user'
DB_PWD='pwd'
DB_NAME='database_name'
DB_TABLE_PREFIX='pref_'

post-commit.db-backup

#!/bin/bash
#
# Creates a database backup and amends it to the previous commit.
#
# To enable this hook, rename this file to "post-commit".

# Quit if msys
if [ $OSTYPE = msys ]
then
  echo "NOTE: No database dump functionality for MSYS"
  exit 1
fi


# Dump comment keyword
NEEDLE='Database dump'

# Read database connection configuration file
CONF_PATH='db/conn'

# Dump destination directory path
DB_BACKUP_DIR_PATH='db'

# Lock file path
LOCK_PATH='.post-commit-lock'

# MySQL Dump arguments
MYSQLDUMP_ARGS="--compact --dump-date --quote-names --comments --add-drop-table"

# Alert size of lock file
LOCK_FILE_ALERT_SIZE=2

# Tables to be ignored for Drupal
DRUPAL_EXCL_TABLES=( "cache" "cache_block" "cache_.*" "watchdog" "sessions" )

# Get the config or get out.
source $CONF_PATH

if [ ! -f $CONF_PATH ]
then
  echo "Configuration file $FILE does not exist."
  exit 1
fi

# Exit if the lock file exists
if [ -e "$LOCK_PATH" ]
then
  lock_filesize=$(stat -c %s "$LOCK_PATH")
  echo "" >> $LOCK_PATH
  if (( $lock_filesize >= $LOCK_FILE_ALERT_SIZE ))
  then
    # Warn if the script has exited due to the lock file several times.
    echo """
The database dummp commit exited due to the presence of a lock file.
This could have been caused by a backup that crashed.
Remove the lock file ($LOCK_PATH) to continue and re-run the commit.
"""
  fi
  
  exit
else
  touch $LOCK_PATH
fi

# Get their existing tables
if [ -z $DB_PWD ]
then
  tables=$(mysql -u$DB_USER -h"$DB_HOST" -e"SHOW TABLES;" $DB_NAME | sed "s/(\| )*( *)\|//")
else
  tables=$(mysql -u$DB_USER -h"$DB_HOST" -p$DB_PWD -e"SHOW TABLES;" $DB_NAME | sed "s/(\| )*( *)\|//")
fi

# Get previous log message
msg=$(git log --pretty=format:%s --abbrev-commit HEAD^..HEAD)
# Dump file name
db_file_path=$DB_BACKUP_DIR_PATH/${DB_NAME}.sql

# Does the message contain the keywords
if [[ "$msg" == *"$NEEDLE"* ]]; then
  mkdir -p $DB_BACKUP_DIR_PATH
  
  rm $db_file_path
  # TODO: Skip the header of the tables list.
  # Loop through the tables.
  for table in $tables
  do
    
    # Leve the data for tables that are to be ignored. Just dump the structure.
    for ignore in "${DRUPAL_EXCL_TABLES[@]}"
    do
      if [[ "$table" =~ "${DB_TABLE_PREFIX}$ignore" ]]
      then
        if [ -z $DB_PWD ]
        then
          mysqldump -h$DB_HOST -u$DB_USER $MYSQLDUMP_ARGS --no-data $DB_NAME $table >> $db_file_path
        else
          mysqldump -h$DB_HOST -u$DB_USER -p$DB_PWD $MYSQLDUMP_ARGS --no-data $DB_NAME $table >> $db_file_path
        fi
        rc=$?
        if [[ $rc != 0 ]] ; then
          echo "Backup of table $table (excluding data) failed."
        else
          echo "Table $table (excluding data) successfully backed up."
        fi
        
        continue 2
      else
        continue
      fi
    done
    
    # If there is no ignore directive for the table, dump everything data included.
    
    if [ -z $DB_PWD ]
    then
      mysqldump -h$DB_HOST -u$DB_USER $MYSQLDUMP_ARGS --skip-extended-insert $DB_NAME $table >> $db_file_path
    else
      mysqldump -h$DB_HOST -u$DB_USER -p$DB_PWD $MYSQLDUMP_ARGS --skip-extended-insert $DB_NAME $table >> $db_file_path
    fi
    rc=$?
    if [[ $rc != 0 ]] ; then
      echo "Backup of table $table and data failed."
    else
      echo "Table $table and data successfully backed up."
    fi
  done
  
  # Commit and be out
  echo ""
  echo "Commiting $db_file_path"
  git add $db_file_path
  git commit --amend -m "$msg"
  echo ""
fi

rm $LOCK_PATH
exit 0