2017112700_adminAlwaysLower.php

<?php
/* Notes:
 * 1. Anything complicated needs to be done with SQL.
 * 2. Always have a check to know if the migration needs to occur (up or down).
 * 3. Use up() and down(), not change(), because of the SQL
 */
use Phinx\Migration\AbstractMigration;

class AdminAlwaysLower extends AbstractMigration
{
    /* This is the process for moving forward. We are re-creating the table so if there is
     * existing data will need to create a temporary table, copy the data, drop the old table,
     * rename the temporary table.
     */
    public function up()
    {
      // List of columns in tables to update.  Just going to update them to their lowercase equivalent.
      $columnsToCheck= array(
          array("table" => "cuauditadmin", "column" => "user_name"),
          array("table" => "cuadmeco", "column" => "admin"),
          array("table" => "cuadminallow", "column" => "user_name"),
          array("table" => "cuadminexclude", "column" => "user_name"),
          array("table" => "cuadminusers", "column" => "user_name")
        );

      $sql= array();
      foreach($columnsToCheck as $colArray)
      {
        $column= $colArray["column"];
        $table= $colArray["table"];
        $sql[]= "(select coalesce(bool_or($column != lower($column)), false) from $table) as $table";
        // Bool_or to be true if the query needs to be run.
      }

      $sql= "select " . implode(",", $sql);
      $row= $this->fetchRow($sql);

      // Check if there aren't changes and if so, print out no changes.
      $changes= false;
      foreach($columnsToCheck as $colArray)
      {
        if ($row[$colArray["table"]])
        {
          $changes= true;
          break;
        }
      }

      if (!$changes)
        print "All username values are already lowercase.  Nothing is done.\n";
      else
      {
        foreach($columnsToCheck as $colArray)
        {
          $column= $colArray["column"];
          $table= $colArray["table"];
          if ($row[$table])
          {
            print "Changing usernames in table $table...\n";
            $sql= "update $table set $column = lower($column) where $column != lower($column)";
            $this->query($sql);
          }
        }
      }

    }

    /* To execute this you need to run the following command:
     * php vendor/bin/phinx rollback (using correct path to phinx)
     * Or, to roll back to a specific time:
     * php vendor/bin/phinx rollback -t 20151123211207
     */
    public function down()
    {
      print "Not implementing the 'down'";
    }
}