2017111500_ilikeIndex.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 IlikeIndex 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() {

    print "ilike index up\n";

    // check if we already made the change
    $indexname= "_username_1";
    $tablename= "user"; // Without CU of course.

    // This query is going through all CUs and seeing if the index has been applied.
    $query= "select a.cu, u.*, lower(indexdef) like '%lower%' as islower from cuadmin a
    left join (select t.relname as table_name, i.relname as index_name from pg_class t inner join pg_index ix on t.oid = ix.indrelid inner join pg_class i on ix.indexrelid= i.oid
    where t.relkind = 'r') u on lower(trim(a.cu) || '$tablename') = lower(trim(u.table_name)) and lower(trim(replace(u.index_name, u.table_name, ''))) = '$indexname'
    left join pg_indexes ids on u.table_name = ids.tablename and u.index_name = ids.indexname order by a.cu, u.table_name, u.index_name";

    $rows= $this->fetchAll($query);

    for ( $i = 0, $count= count($rows); $i < $count; $i++ )
    {
      $row= $rows[$i];

      $removeIndex= isset($row["index_name"]) && !$row["islower"];
      $addIndex= !isset($row["index_name"]) || !$row["islower"];

      $cu= strtolower(trim($row["cu"]));

      if ($removeIndex)
      {
        print "Removing previous username index for " . $cu . "...\n";
        $this->query("drop index " . $cu . $tablename . $indexname);
      }

      if ($addIndex)
      {

        print "Adding ilike index for " . $cu . "...\n";
        $this->query("create unique index " . $cu . $tablename . $indexname . " on " . $cu . $tablename . " ((lower(user_name)))");
      }
    }

  }

  /* 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 "ilike index up\n";

    // check if we already made the change
    $indexname= "_username_1";
    $tablename= "user"; // Without CU of course.

    // This query is going through all CUs and seeing if the index has been applied.
    $query= "select a.cu, u.*, lower(indexdef) like '%lower%' as islower from cuadmin a
    left join (select t.relname as table_name, i.relname as index_name from pg_class t inner join pg_index ix on t.oid = ix.indrelid inner join pg_class i on ix.indexrelid= i.oid
    where t.relkind = 'r') u on lower(trim(a.cu) || '$tablename') = lower(trim(u.table_name)) and lower(trim(replace(u.index_name, u.table_name, ''))) = '$indexname'
    left join pg_indexes ids on u.table_name = ids.tablename and u.index_name = ids.indexname order by a.cu, u.table_name, u.index_name";

    $rows= $this->fetchAll($query);

    print "tablename: $tablename";

    for ( $i = 0, $count= count($rows); $i < $count; $i++ )
    {
      $row= $rows[$i];

      $removeIndex= isset($row["index_name"]) && $row["islower"];
      $addIndex= !isset($row["index_name"]) || $row["islower"];

      $cu= strtolower(trim($row["cu"]));

      if ($removeIndex)
      {
        print "Removing ilike index for " . $cu . "...\n";
        $this->query("drop index " . $cu . $tablename . $indexname);
      }

      if ($addIndex)
      {

        print "Adding previous username index for " . $cu . "...\n";
        $this->query("create unique index " . $cu . $tablename . $indexname . " on " . $cu . $tablename . " (user_name)");
      }
    }
  }
}

?>