<?php
/*********************************************************************************************************
  "Ensemble" is the proprietary property of The Regents of the University of California ("The Regents.")

  Copyright (c) 2005-10 The Regents of the University of California, Davis campus. All Rights Reserved.

  Redistribution and use in source and binary forms, with or without modification, are permitted by
  nonprofit, research institutions for research use only, provided the conditions in the included
  license agreement are met.

  Refer to the file "ensemble_license.txt" for the license agreement,
  located in the top level directory of this distribution.

**********************************************************************************************************/

  // ENSEMBLE UPGRADE -- v 1.5
  // Oct 2, 2009 - Stefan Tomic, Original Version
  // Nov 16, 2009 - Stefan Tomic, Changed location of ENSEMBLE_VERSION variable to <include_dir>/version_info.php


$versionInfo = 'v1.5';

print "\n\n------------------ ENSEMBLE UPDATE: OCTOBER 2, 2009, $versionInfo ----------------------\n";
print "--------------------------------------------------------------------------------\n\n";

function rollback() {

  global $dbConn,$validDB,$backupDir,$versionInfo,$subTableBackupFpath;

  print "ATTEMPTING TO ROLLBACK CHANGES. THE ROLLBACK MAY NOT BE COMPLETE, AND YOU SHOULD INSPECT\n";
  print "ENSEMBLE FILES AND THE DATABASE TO ENSURE EVERYTHING IS INTACT\n\n";

  //There shouldn't be a need to rollback changes to variables_config.php or to the 
  //crypt key file. These will simply be detected and used the next time the update is run
  //We should delete the subject_tmp table, however


  //if we don't have a valid database connection
  //because the db connection failed, try and enstablish one
  if(!isset($dbConn) || !$dbConn || !$validDB) {
    print "ROLLBACK: Restablishing database connection";
    list($dbConn,$dbName,$muser,$mpass,$mhost) = mysqlConn();
  }
    
  //Do a dump of the subject table again.
  //if the current dump and the original backup dump are the same,
  //then we can just delete the subject_tmp table.
  //If not, then something crashed while manipulating the subject table
  //and we should just issue a big warning message and exit
  if(isset($backupDir) && isset($subTableBackupFpath)) {
    $tmpSubTableBackupFpath =  backupDB($backupDir,$versionInfo.'tmp','subject',0);
    
    //read in both subject dumps (before upgrade script and after)
    //and delete any comment lines
    $tmpContents = file($tmpSubTableBackupFpath);
    $tmpContents = preg_replace("|--.*|","",$tmpContents);
    $tmpContents = implode("",$tmpContents);
    $origContents = file($subTableBackupFpath);
    $origContents = preg_replace("|--.*|","",$origContents);
    $origContents = implode("",$origContents);
    if(empty($tmpContents) || empty($origContents)) {
      print "ERROR: unable to verify whether subject table was altered.\n";
      print "You should inspect the subject table to verify.\n";
    }


    if(strcmp($tmpContents,$origContents) == 0) {
      //current dump is the same as original dump
      //so drop the subject_tmp table
      print "ROLLBACK: original subject table is unaltered.\n";
      $dropSQL = "drop table if exists subject_tmp";
      print "ROLLBACK: Trying to drop table subject_tmp (if it exists).\n";
      mysql_query($dropSQL,$dbConn) or die(mysql_error());
      unlink($tmpSubTableBackupFpath); //delete the temporary dump
    }
    else {
      //this clause should hopefully never execute
      print "WARNING!!! THE SUBJECT TABLE APPEARS TO HAVE BEEN ALTERED BY THE UPGRADE SCRIPT!!!\n";
      print "YOU WILL NEED TO INSPECT YOUR SUBJECT TABLE TO MAKE SURE THAT YOUR DATA IS STILL INTACT!!!\n";
    }

  }
  
  //stop execution of the update script
  print "ROLLBACK: Exiting script.\n";
  die();
  
}

/*********************************************************************************************************
   PREPARE UPGRADE
   Obtain ensemble root path, establish database connection, backup database
*/

include_once('../../include/variables.php');
include_once('./upgrade_functions.php');


// initialize variables and lists for the upgrade
$ensroot = $ENSEMBLE_QEI_QPI_DIR;
$svn_public_path = 'https://atonal.ucdavis.edu/repos/public';
$svn_formprocess = $svn_public_path.'/ensemble/release/v1/form_processing';


// print standard upgrade warnings and provide option to exit script
$addToWarning = "".
  "It is also imperative that you have applied the previous update (upgrade_20090909.php)\n".
  "before proceeding. This script will make a new copy of the subject table, with sensitive\n".
  "information (name, dob, email, etc) encrypted. If the copy is successful, the old copy \n".
  "of your subject table will be deleted, and replaced with the new copy.\n".
  "You will be asked for an encryption key and a location to store your encryption key.\n\n".
  "You should run this script as \"root\" user.\n\n";

upgrade_warning($addToWarning);

print "\n\nENSEMBLE INSTALLATION ROOT DIRECTORY\n";
print "\nWhere is your ensemble installation located?\n";
$ensroot = checkDirectoryInput($ensroot);


print "\n\nESTABLISHING DATABASE CONNECTION\n\n";

list($dbConn,$dbName,$muser,$mpass,$mhost) = mysqlConn();

print "\nDirectory to save database backup ";
$backupDir = checkDirectoryInput($_ENV['HOME']);

// BACKUP THE DATABASE
$dbBackupFpath = backupDB($backupDir,$versionInfo);
$subTableBackupFpath = backupDB($backupDir,$versionInfo,'subject');

/********************************************************************************************************/

//ENCRYPTION KEY PATH AND VARIABLE UPDATES
//These are small changes, so it is better to try this first
//in case the update script fails and we need to roll back

$versionInfoFile = $ensroot."/include/version_info.php";
if(!file_exists($versionInfoFile)) {
  print "\n\nFile $versionInfoFile does not exist.\n";
  print "If you have installed ensemble with svn, quit this upgrade script now,\n";
  print "make sure to perform an 'svn update' on the ensemble root directory\n";
  print "to obtain $versionInfoFile,\n";
  print "then rerun this upgrade script. If you installed Ensemble via tarball download\n";
  print "you may proceed. This script will create $versionInfoFile.\n";
  print "Proceed with upgrade script ";
  $doProceed = userConfirm();
  
  if($doProceed) {
    file_put_contents($versionInfoFile,"<?php\n//Version Info for Ensemble\n\n\$ENSEMBLE_VERSION='1.5.0';\n?>");
  }
  else
    die();
 }

$variables_contents = file_get_contents($ensroot."/include/variables.php");
if(strstr($variables_contents,"version_info.php")) {
 print "\nReference to version_info.php found in variables.php. Leaving variables.php alone.\n\n";
}
 else {
 changeConfigFile($ensroot."/include/variables.php",'include_once("version_info.php");','',true,"code");
 }

$cryptFields = array("name_last","name_first","name_middle","name_suffix","email","dob","phone1","phone2","address1","address2","address3","city","state","postal_code");

$cryptFieldList = implode(',',$cryptFields);
//enclose the fields with single quotes
$cryptFieldList = preg_replace("|([,]?)([^,]+)([,]?)|","\\1'\\2'\\3",$cryptFieldList);

//See if update was already performed (subject names and birthday are blob fields)
$descTableSQL=sprintf('show columns from `%s`.`subject` where Field in (%s)',$dbName,$cryptFieldList);
$descQuery = mysql_query($descTableSQL,$dbConn) or error_condition(mysql_error());
while($descResult = mysql_fetch_assoc($descQuery)) {
  $isBlob = (strcmp($descResult['Type'],'blob') == 0);
  if($isBlob) {
    error_condition("One or more of the fields to be converted in the subject table are already of type blob\nThe update script is aborting.");
  }
 }

$setCryptFile = false;
while(!$setCryptFile) {

  //prompt for file to store encryption key
  print "\n\nFile path to store the encryption key for sensitive subject data\n";
  print "This should be in a directory that is NOT under your web root and \n";
  print "is only readable by administrators and the apache user\n";

  $privatePathInfo = pathinfo($SUBJECT_MYSQL_LOGIN_FILE);
  $privateDir = $privatePathInfo['dirname'];
  $cryptFpath = $privateDir . "/ensemble_encryption_key.txt";
  $cryptFpath = getUserInput($cryptFpath);

  if(is_file($cryptFpath)) {
    print "The file \"$cryptFpath\" exists!\nDo you want to use this file (and its contents) for your encryption key (y/n)?\n";
    $userConfirm = "";
    while(!preg_match("|[YNyn]|",$userConfirm)) {
      $userConfirm = getUserInput("y");
    }
    if(preg_match("|[yY]|",$userConfirm)) {
      $cryptKeyExists = true;
      $setCryptFile = true;
    }
    else { //userConfirm
      print "User indicated not to use the above path.\nProviding the option to enter a different path.\n";
      $setCryptFile = false;
    }
  }
  else{ //cryptFpath
    $setCryptFile = true;
    $cryptKeyExists = false;
  }
 }

 
if(!$cryptKeyExists) {
  //prompt for a key string
  $cryptPhrase = "";
  while(empty($cryptPhrase)) {
    print "\nEnter an encryption key (word or phrase) for subject data.\n16 or more characters recommended: ";
    $cryptPhrase=trim(fgets(STDIN,256));
  }

  printf("Saving the encryption key to %s\n",$cryptFpath);
  $cryptFid = fopen($cryptFpath,'w');
  fwrite($cryptFid,$cryptPhrase);
  fclose($cryptFid);

  //The following warnings are not error conditions. We simply should report them to the user and continue
  if(!chown($cryptFpath,'root'))
    print "\nWARNING: Failed to set ownership of file $cryptFpath to root user.\nbe sure to set the proper ownership after the upgrade script finishes.\n";
  
  $apacheGrp = "apache";
  print "\nName of group of that the apache user is a member of\n(file group of $cryptFpath will be set to this)\n";
  $apacheGrp = getUserInput($apacheGrp);

  if(!chgrp($cryptFpath,$apacheGrp))
    print "\nWARNING: Failed to set group of file $cryptFpath to $apacheGrp.\nBe sure to set the proper ownership after the upgrade script finishes.\n";
  
  if(!chmod($cryptFpath,0750))
    print "\nWARNING: Failed to chmod on file $cryptFpath\n Be sure to set the proper permissions after the upgrade script finishes.\n";
 }

//try and read the passphrase back in to make sure it is readable.
$cryptFid = fopen($cryptFpath,'r') or error_condition('Failed to read encryption key.');
$readCryptPhrase = fgets($cryptFid) or error_condition('Failed to read encryption key.');
fclose($cryptFid);

print "\nUsing passphrase: \"".$readCryptPhrase."\"\n";

changeConfigFile($ensroot."/include/variables_config.php","ENCRYPTION_KEY_PATH",$cryptFpath,false);

//No longer adding ENSEMBLE_VERSION to variables_config.php, since this file will not be updated
//through SVN. $ENSEMBLE_VERSION is now located in <ensemble_dir>/include/version_info.php
//S.T. 11/16/09
//changeConfigFile($ensroot."/include/variables_config.php","ENSEMBLE_VERSION",'1.5.0',false);


//DATABASE UPDATES

// create a copy of the subject table
// all manipulations will be performed on the copy
// this will make rollback easier if an error kills the script
mysql_query(sprintf('create table `%s`.subject_tmp like subject',$dbName),$dbConn) or error_condition(mysql_error());
mysql_query(sprintf('insert into `%s`.`subject_tmp` select * from subject',$dbName),$dbConn) or error_condition(mysql_error());

//create temporary blob fields for each field in $cryptFields
print "\n\nCREATING TEMPORARY FIELDS TO SUBJECT_TMP TABLE FOR ENCRYPTION\n";
foreach($cryptFields as $fld) {
  $tmpField = $fld . "_blob";
  printf("Adding temporary field %s\n",$tmpField);
  $alterSQL = sprintf('alter table `%s`.`subject_tmp` add `%s` blob NULL after `%s`',$dbName,$tmpField,$fld);
  $alterQuery = mysql_query($alterSQL,$dbConn) or error_condition(mysql_error());
}

print "\nENCRYPTING DATA AND COPYING TO TEMPORARY FIELDS\n";
//copy values to their tmp blob field counterparts
foreach($cryptFields as $fld) {

  print "Copying data from $fld\n";
  $tmpField = $fld . "_blob";
  $updateSQL =  sprintf("update `%s`.`subject_tmp` set %s = aes_encrypt(%s,'%s')",$dbName,$tmpField,$fld,$readCryptPhrase);
  $updateQuery = mysql_query($updateSQL,$dbConn) or error_condition(mysql_error());

  $checkSQL = sprintf("select %s,aes_decrypt(%s,'%s') as encrypted from `%s`.`subject_tmp`",$fld,$tmpField,$readCryptPhrase,$dbName);
  $checkQuery = mysql_query($checkSQL,$dbConn) or error_condition(mysql_error());

  print "Verifying that copied data from $fld is valid...";
  //check to see that all values are equal to their encrypted ones
  while($checkVal = mysql_fetch_assoc($checkQuery)) {
    if(strcmp($checkVal[$fld],$checkVal['encrypted']) != 0)
      error_condition(sprintf('An error occurred while encrypting %s',$fld));
  }
  print "verified.\n";
}

//delete the unencrypted fields, rename the encrypted ones to the proper names
$dropArray = preg_replace("|.+|","drop `$0`",$cryptFields);
$dropString = implode(",",$dropArray);
$deleteFieldSQL = sprintf('alter table `%s`.`subject_tmp` %s',$dbName,$dropString);
print $deleteFieldSQL."\n";
$deleteFieldQuery = mysql_query($deleteFieldSQL,$dbConn) or error_condition(mysql_error());

foreach($cryptFields as $fld) {
  $tmpField = $fld . "_blob";
  $renameFieldSQL = sprintf('alter table `%s`.`subject_tmp` change %s %s blob',$dbName,$tmpField,$fld);
  print $renameFieldSQL."\n";
  $renameFieldQuery = mysql_query($renameFieldSQL,$dbConn) or error_condition(mysql_error());
}

//drop the subject table, rename subject_tmp to subject
$dropSubjectSQL = sprintf("drop table `%s`.`subject`",$dbName);
print $dropSubjectSQL."\n";
mysql_query($dropSubjectSQL,$dbConn) or error_condition(mysql_error());
$renameSubjectTmpSQL = sprintf("rename table `%s`.`subject_tmp` to `%s`.`subject`",$dbName,$dbName);
print $renameSubjectTmpSQL."\n";
mysql_query($renameSubjectTmpSQL,$dbConn) or error_condition(mysql_error());

print "DONE UPDATING!\n\n";
print "Please test your installation by verifying that old experiments still run\n";

?>