#!/usr/bin/env bash
# Evan Wilde <etcwilde@uvic.ca>
# July 20, 2017

# defaults
user="postgres"
passwd=""
host="localhost"
db="ghtorrent"
tmpdir='/tmp'


usage()
{
  echo "Usage: $0 [-u dbuser ] [-p dbpasswd ] [-h dbhost ] [-d database] [-t tmpdir] dump_dir"
  echo
  echo "Restore a database from CSV and SQL files in dump_dir"
  echo "    -u database user (default: $user)"
  echo "    -p database passwd (default: $passwd)"
  echo "    -h database host (default: $host)"
  echo "    -d database to restore to. Must exist. (default: $db)"
  echo "    -t temporary direcotry (should be at least 50 gb) (default: $tmpdir)"
}

if [ -z $1 ]
then
  usage
  exit 1
fi

# Load arguments
while getopts "u:p:h:d:t:" o
do
  case $o in
    u) user=$OPTARG ;;
    p) passwd=$OPTARG ;;
    h) host=$OPTARG ;;
    d) db=$OPTARG ;;
    t) tmpdir=$OPTARG ;;
    \?) echo "Invalid option -$OPTARG" >&2
      usage
      exit 1
      ;;
  esac
done

# Set up psql command line
if [ -z $passwd ]; then
  psql="psql -q --dbname $db -U $user -h $host --no-password"
else # should hide the password in the cmdline
  psql=" psql -q --dbname $db -U $user -h $host --password=$passwd"
fi

shift $(expr $OPTIND - 1)
dumpDir=$1

if [ ! -e $dumpDir/psql_schema.sql ]; then
  echo "Cannot find $dumpDir/psql_schema.sql to create DB schema"
  exit 1
fi

if [ ! -e $dumpDir/ORDER ]; then
  echo "Cannot find $dumpDir/ORDER to build tables"
  exit 1
fi

# 1. Create database schema
echo "`date` Creating DB schema" >&2
cat $dumpDir/psql_schema.sql |
sed -e "/USE/d" |
sed -e "/COMMENT/s/COMMENT '',/,/" |
sed -e "/COMMENT/s/COMMENT '')/)/" |
sed -e "/SET/d" |
sed -e "/AUTO_INCREMENT/s/AUTO_INCREMENT/SERIAL/" |
sed -E -e "s/ghtorrent\.(.*)/\1/" |
sed -e "s/ghtorrent/$db/" |
sed -E -e "s/DECIMAL\(([0-9]+,[0-9]+)\)/numeric(\1)/" |
sed -e "/TINYINT/s/TINYINT([0-9]*)/int/" |
sed -e "/INT/s/INT([0-9]*)/int/" |
sed -e "/MEDIUMTEXT/s/MEDIUMTEXT/text/" |
sed -e "/VARCHAR/s/VARCHAR([0-9]*)/text/" |
grep -v "^--" | $psql

# 2. Disable FK and load data
while read table; do
  file=`readlink -f "$dumpDir/$table.csv"`
  tmpf=`readlink -f $tmpdir`/$table.csv
  echo "`date` Restoring table $table" >&2
  echo "`date` Removing 0000-00-00 00:00:00 from timestamps ($tmpf)" >&2
  sed -e "s/\"0000-00-00 00:00:00\"/\\\N/g" $file> $tmpf
  mv $tmpf $file
  echo "`date` Disabling Foreign Key Constraints on $table" >&2
  echo "ALTER TABLE $table DISABLE TRIGGER ALL;" | $psql
  echo "\copy $table FROM '$file' WITH DELIMITER AS ',' NULL AS '\\N' ESCAPE '\\' CSV;" | $psql
done < "`readlink -f $dumpDir/ORDER`"

# 3. Reenable FK constraints
while read table; do
  echo "ALTER TABLE $table ENABLE TRIGGER ALL;" | $psql
  echo "`date` Reenabled Foreign Key Constraints on $table" >&2
done < "`readlink -f $dumpDir/ORDER`"

# 4. Create Indices
echo "`date` Creating Indices"
cat $dumpDir/indexes.sql |
sed -E -e "s/\`([^ \n\t\`]*)\`/\1/g" |
sed -E -e "s/ghtorrent\.(.*)/\1/" |
sed -e "/COMMENT/s/COMMENT '')/)/" |
sed -e "/COMMENT/s/COMMENT '',/,/" |
sed -e "/COMMENT/s/COMMENT '';/;/" | $psql