#!/bin/bash

if [ -z "$SQLPASS" ];
then
  echo 'SQLPASS unset. exiting' >&2;
   exit 1;
fi;

#set -x;

export PID_SCRIPT=$$;

# creates named pipes for attaching to stdin and stdout of mysql
mkfifo /tmp/mysql-reader-stdin-$PID_SCRIPT.pipe /tmp/mysql-reader-stdout-$PID_SCRIPT.pipe /tmp/mysql-writer-stdin-$PID_SCRIPT.pipe /tmp/mysql-writer-stdout-$PID_SCRIPT.pipe

# unbuffered option to ensure mysql doesn't buffer the output, so we can read immediately
# batch and skip-column-names options are for ease of parsing the output
mysql --unbuffered --batch --silent --skip-column-names -hxen-mysql -ucomma -p$SQLPASS comma < /tmp/mysql-reader-stdin-$PID_SCRIPT.pipe > /tmp/mysql-reader-stdout-$PID_SCRIPT.pipe &
PID_MYSQL_READER=$!

mysql --unbuffered --batch --silent --skip-column-names -hxen-mysql -ucomma -p$SQLPASS comma < /tmp/mysql-writer-stdin-$PID_SCRIPT.pipe > /tmp/mysql-writer-stdout-$PID_SCRIPT.pipe &
PID_MYSQL_WRITER=$!


# make sure to stop mysql and remove the pipes before leaving
cleanup_proc_pipe() {
    kill $PID_MYSQL_READER $PID_MYSQL_WRITER
    rm -f /tmp/mysql-reader-stdin-$PID_SCRIPT.pipe /tmp/mysql-reader-stdout-$PID_SCRIPT.pipe /tmp/mysql-writer-stdin-$PID_SCRIPT.pipe /tmp/mysql-writer-stdout-$PID_SCRIPT.pipe
}
trap cleanup_proc_pipe EXIT

# open file descriptors for writing and reading
exec 3>/tmp/mysql-reader-stdin-$PID_SCRIPT.pipe;
exec 4</tmp/mysql-reader-stdout-$PID_SCRIPT.pipe
exec 5>/tmp/mysql-writer-stdin-$PID_SCRIPT.pipe;
exec 6</tmp/mysql-writer-stdout-$PID_SCRIPT.pipe

# update the cleanup procedure to close the file descriptors
cleanup_fd() {
    exec 3>&-
    exec 4>&-
    cleanup_proc_pipe
}
trap cleanup_fd EXIT

i=0;
while [ $i -le 5000 ];
do
  i=$(( $i + 1 ));

  # try to obtain lock with 60 seconds of timeout
  echo 'SELECT GET_LOCK( "_test_sql_insert_update", 60 );' >&5
  # read stdout of mysql with 61 seconds of timeout
  if ! read -t 61 line <&6;
  then
    echo "Timeout reading from mysql" >&2;
    break;
  fi;
  if [ "$line" != 1 ]; then
    echo "Timeout waiting for lock" >&2;
    break;
  fi;

  echo "SELECT MAX( _sq ), RIGHT( CURRENT_TIMESTAMP(6), 9 ) FROM _test_sql_insert_update;" >&3
  if ! read -t 5 EXPECTED TIMESTR <&4;
  then
    echo "Timeout reading from mysql SELECT" >&2;
    break;
  fi;
  if [ -z "$EXPECTED" ] || [ -z "$TIMESTR" ];
  then
    echo "Failed to retrieve current MAX( _sq )" >&2;
    break;
  fi;
  echo -e "pre\t$EXPECTED\t$TIMESTR";

  echo "INSERT INTO _test_sql_insert_update ( _comma_flag, doc_id, field1, created ) VALUES ( 0, 0, 'field1', CURRENT_TIMESTAMP(6) ); SELECT ROW_COUNT();" >&5;
  if ! read -t 5 ROW_COUNT <&6;
  then
    echo "Timeout reading from mysql INSERT" >&2;
    break;
  fi;
  if [ "$ROW_COUNT" != 1 ];
  then
    echo "No response/bad response from INSERT '$ROW_COUNT'" >&2;
    break;
  fi;
  echo -e "insert\t$ROW_COUNT";

  echo "SELECT COUNT( _sq ) FROM _test_sql_insert_update WHERE _sq = $(( $EXPECTED + 1 ));" >&3;
  if ! read -t 5 COUNT <&4;
  then
    echo "Timeout reading from mysql SELECT" >&2;
    break;
  fi;
  if [ -z "$COUNT" ];
  then
    echo "Failed to retrieve COUNT( _sq ) WHERE _sq = $(( $EXPECTED + 1 ))" >&2;
    break;
  fi;
  echo -e "count\t$COUNT";

  echo "SELECT MAX( _sq ), RIGHT( CURRENT_TIMESTAMP(6), 9 ) FROM _test_sql_insert_update;" >&3
  if ! read -t 5 _SQ TIMESTR <&4;
  then
    echo "Timeout reading from mysql SELECT" >&2;
    break;
  fi;
  if [ -z "$_SQ" ] || [ -z "$TIMESTR" ];
  then
    echo "Failed to retrieve current MAX( _sq )" >&2;
    break;
  fi;
  echo -e "post\t$_SQ\t$TIMESTR";


  echo "SELECT _sq, RIGHT( CURRENT_TIMESTAMP(6), 9 ) FROM _test_sql_insert_update WHERE doc_id = 0;" >&3
  if ! read -t 5 ACTUAL TIMESTR <&4;
  then
    echo "Timeout reading from mysql SELECT" >&2;
    break;
  fi;
  if [ -z "$ACTUAL" ] || [ -z "$TIMESTR" ];
  then
    echo "Failed to retrieve current MAX( _sq )" >&2;
    break;
  fi;
  echo -e "actual\t$ACTUAL\t$TIMESTR";

  echo "UPDATE _test_sql_insert_update SET doc_id = $ACTUAL WHERE doc_id = 0; SELECT ROW_COUNT();" >&5
  if ! read -t 5 ROW_COUNT <&6;
  then
    echo "Timeout reading from mysql" >&2;
    break;
  fi;
  if [ "$ROW_COUNT" != 1 ];
  then
    echo "No response/bad response from INSERT '$ROW_COUNT'";
    break;
  fi;
  echo -e "update\t$ROW_COUNT";

  echo 'DO RELEASE_LOCK( "_test_sql_insert_update" );' >&5;
done;