#!/bin/bash
#
#   PHP VOMS-Admin script to update database schema to the latest version
#
#    Copyright 2010 Andrii Salnikov
#
#   Licensed under the Apache License, Version 2.0 (the "License");
#   you may not use this file except in compliance with the License.
#   You may obtain a copy of the License at
#
#       http://www.apache.org/licenses/LICENSE-2.0
#
#   Unless required by applicable law or agreed to in writing, software
#   distributed under the License is distributed on an "AS IS" BASIS,
#   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
#   See the License for the specific language governing permissions and
#   limitations under the License.
#

function upgrade_database_0_to_65 {
cat <<SQLEND
DELIMITER \$\$

-- 
-- Create institute column in memb_req table (version 0.1)
--

DROP PROCEDURE IF EXISTS add_0_1_memb_req_institute \$\$
CREATE PROCEDURE add_0_1_memb_req_institute()
BEGIN
  IF NOT EXISTS(
    SELECT * FROM information_schema.COLUMNS
    WHERE COLUMN_NAME='institute' AND TABLE_NAME='memb_req' AND TABLE_SCHEMA='${DBNAME}'
  )
  THEN
    ALTER TABLE memb_req
    ADD institute VARCHAR( 255 ) NOT NULL DEFAULT '';
  END IF;
END \$\$

-- 
-- Create phone column in memb_req table (version 0.1)
--

DROP PROCEDURE IF EXISTS add_0_1_memb_req_phone \$\$
CREATE PROCEDURE add_0_1_memb_req_phone()
BEGIN
  IF NOT EXISTS(
    SELECT * FROM information_schema.COLUMNS
    WHERE COLUMN_NAME='phone' AND TABLE_NAME='memb_req' AND TABLE_SCHEMA='${DBNAME}'
  )
  THEN
    ALTER TABLE memb_req
    ADD phone VARCHAR( 255 ) NOT NULL DEFAULT '';
  END IF;
END \$\$

-- 
-- Create comment column in memb_req table (version 0.1)
--

DROP PROCEDURE IF EXISTS add_0_1_memb_req_comment \$\$
CREATE PROCEDURE add_0_1_memb_req_comment()
BEGIN
  IF NOT EXISTS(
    SELECT * FROM information_schema.COLUMNS
    WHERE COLUMN_NAME='comment' AND TABLE_NAME='memb_req' AND TABLE_SCHEMA='${DBNAME}'
  )
  THEN
    ALTER TABLE memb_req
    ADD \`comment\` VARCHAR( 255 ) NOT NULL DEFAULT '';
  END IF;
END \$\$

-- 
-- Create pva_variables table (version 0.6rc1)
-- 

DROP PROCEDURE IF EXISTS create_0_6rc1_pva_variables \$\$
CREATE PROCEDURE create_0_6rc1_pva_variables()
BEGIN
  CREATE TABLE IF NOT EXISTS pva_variables (
    \`var\` VARCHAR(128) NOT NULL, 
    \`value\` VARCHAR(255) NOT NULL, 
    UNIQUE KEY \`var\` (\`var\`)
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
END \$\$

-- 
-- Create pva_authorized_updators table (version 0.6)
-- 

DROP PROCEDURE IF EXISTS create_0_6_pva_authorized_updators \$\$
CREATE PROCEDURE create_0_6_pva_authorized_updators()
BEGIN
  CREATE TABLE IF NOT EXISTS \`pva_authorized_updators\` (
    \`au_id\` smallint(6) NOT NULL AUTO_INCREMENT,
    \`status\` tinyint(4) NOT NULL,
    \`dn\` varchar(255) NOT NULL,
    \`cahash\` varchar(10) NOT NULL,
    \`ip\` varchar(16) NOT NULL,
    \`endpoint\` varchar(128) NOT NULL,
    \`auth_key\` varchar(64) NOT NULL,
    \`foreign_key\` varchar(64) NOT NULL,
    \`t_stamp\` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
    \`sync_time\` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (\`au_id\`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

  IF NOT EXISTS(
    SELECT * FROM pva_authorized_updators
    WHERE au_id=1
  )
  THEN
    INSERT INTO \`pva_authorized_updators\` (
      \`au_id\`, \`status\`, \`dn\`, \`ip\`, \`endpoint\`, \`auth_key\`, \`foreign_key\`, \`t_stamp\`, \`sync_time\`
    ) VALUES (
      1, 9, '/O=VOMS/O=System/CN=Local PHP VOMS-Admin', '', '', '', '', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
    );
  END IF;
END \$\$

-- 
-- Create pva_id2uuid_map table (version 0.6)
-- 

DROP PROCEDURE IF EXISTS create_0_6_pva_id2uuid_map \$\$
CREATE PROCEDURE create_0_6_pva_id2uuid_map()
BEGIN
  CREATE TABLE IF NOT EXISTS \`pva_id2uuid_map\` (
    \`id\` int(11) NOT NULL,
    \`table\` varchar(36) NOT NULL,
    \`uuid\` varchar(36) NOT NULL,
    PRIMARY KEY (\`uuid\`),
    KEY \`id\` (\`id\`),
    KEY \`table\` (\`table\`)
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
END \$\$

-- 
-- Create pva_logs table (version 0.6)
-- 

DROP PROCEDURE IF EXISTS create_0_6_pva_logs \$\$
CREATE PROCEDURE create_0_6_pva_logs()
BEGIN
  CREATE TABLE IF NOT EXISTS \`pva_logs\` (
    \`id\` int(10) unsigned NOT NULL AUTO_INCREMENT,
    \`level\` char(1) NOT NULL,
    \`subsys\` smallint(5) unsigned NOT NULL,
    \`msg_code\` int(10) unsigned NOT NULL,
    \`msg_parms\` text NOT NULL,
    \`count\` int(10) unsigned NOT NULL DEFAULT '1',
    \`first_occured\` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    \`last_occured\` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
    PRIMARY KEY (\`id\`),
    KEY \`msg_code\` (\`msg_code\`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
END \$\$

-- 
-- Create pva_transactions table (version 0.6.5 revision)
-- 

DROP PROCEDURE IF EXISTS create_0_6_pva_transactions \$\$
CREATE PROCEDURE create_0_6_pva_transactions()
BEGIN
  CREATE TABLE IF NOT EXISTS \`pva_transactions\` (
    \`t_stamp\` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    \`uuid\` char(36) NOT NULL,
    \`adminid\` varchar(255) NOT NULL,
    \`fname\` varchar(32) NOT NULL,
    \`args\` text NOT NULL,
    \`source_id\` int(11) NOT NULL,
    \`source_flavor\` VARCHAR (32) NOT NULL DEFAULT '',
    KEY \`t_stamp\` (\`t_stamp\`),
    UNIQUE KEY \`uuid\` (\`uuid\`)
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
END \$\$

-- 
-- Create source_flavor column in pva_transactions table (version 0.6.5)
-- 

DROP PROCEDURE IF EXISTS add_0_6_5_pva_transactions_source_flavor \$\$
CREATE PROCEDURE add_0_6_5_pva_transactions_source_flavor()
BEGIN
  IF NOT EXISTS(
    SELECT * FROM information_schema.COLUMNS
    WHERE COLUMN_NAME='source_flavor' AND TABLE_NAME='pva_transactions' AND TABLE_SCHEMA='${DBNAME}'
  ) 
  THEN
    ALTER TABLE pva_transactions
    ADD source_flavor VARCHAR (32) NOT NULL DEFAULT '';
  END IF;
END \$\$

-- 
-- Set database version to 065
--

DROP PROCEDURE IF EXISTS set_version_65 \$\$
CREATE PROCEDURE set_version_65()
BEGIN
  IF NOT EXISTS(
    SELECT * FROM pva_variables
    WHERE \`var\`='pva_dbschema_version'
  )
  THEN
    INSERT INTO pva_variables (\`var\`, \`value\`)
    VALUES ('pva_dbschema_version', '65');
  ELSE
    UPDATE pva_variables SET \`value\`='65'
    WHERE \`var\`='pva_dbschema_version';
  END IF;
END \$\$

CALL add_0_1_memb_req_institute \$\$
CALL add_0_1_memb_req_phone \$\$
CALL add_0_1_memb_req_comment \$\$
CALL create_0_6rc1_pva_variables \$\$
CALL create_0_6_pva_authorized_updators \$\$
CALL create_0_6_pva_id2uuid_map \$\$
CALL create_0_6_pva_logs \$\$
CALL create_0_6_pva_transactions \$\$
CALL add_0_6_5_pva_transactions_source_flavor \$\$
CALL set_version_65 \$\$

DROP PROCEDURE add_0_1_memb_req_institute \$\$
DROP PROCEDURE add_0_1_memb_req_phone \$\$
DROP PROCEDURE add_0_1_memb_req_comment \$\$
DROP PROCEDURE create_0_6rc1_pva_variables \$\$
DROP PROCEDURE create_0_6_pva_authorized_updators \$\$
DROP PROCEDURE create_0_6_pva_id2uuid_map \$\$
DROP PROCEDURE create_0_6_pva_logs \$\$
DROP PROCEDURE create_0_6_pva_transactions \$\$
DROP PROCEDURE add_0_6_5_pva_transactions_source_flavor \$\$
DROP PROCEDURE set_version_65 \$\$

DELIMITER ;
SQLEND
}

# parse options
while getopts :d:h:u:p:fh ARG; do
  case $ARG in 
    f) FORCE_UPDATE=1 ;;
    d) DBNAME=${OPTARG} ;;
    h) DBHOST=${OPTARG} ;;
    u) DBUSER=${OPTARG} ;;
    p) DBPASSWD=${OPTARG} ;;
    *) echo "Usage: pva-dbschema-update [-f] [-h <host>] -d <database> -u <user> -p <password>"; exit 1 ;;
  esac
done

# check required options
if [ -z "${DBNAME}" ]; then
  echo "Error: Database name not specified."
  exit 1
fi

if [ -z "${DBUSER}" ]; then
  echo "Error: Database user not specified."
  exit 1
fi

if [ -z "${DBPASSWD}" ]; then
  echo "Error: Database password not specified."
  exit 1
fi

DBHOST=${DBHOST:-localhost}

# determine current version of database schema
if [ -z "${FORCE_UPDATE}" ]; then
  dbschema_version=$(mysql -h ${DBHOST} -u ${DBUSER} -p${DBPASSWD} --database=${DBNAME} -N -s -r -e "SELECT value FROM pva_variables WHERE pva_variables.var='pva_dbschema_version';")
  dbschema_version=${dbschema_version:-0}
else
  dbschema_version=0
fi

# invoke database update script if required
if [ ${dbschema_version} -lt 65 ]; then
  upgrade_database_0_to_65 | mysql -h ${DBHOST} -u ${DBUSER} -p${DBPASSWD} --database=${DBNAME}
  RETVAL=$?
  [ ${RETVAL} -ne 0 ] && exit ${RETVAL}
fi

exit 0

