2018041000_globalidxupdate.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 GlobalIdxUpdate 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()
    {
      // loop through tables to update
      $tableNames = array(
        "scheduledtxn" => array("title"=>"cuuser_idx", "cols"=>"cu, user_id, id"), 
        "alerts" => array("title"=>"cuuser_idx", "cols"=>"cu, user_id, alerttype")
      );

      foreach ($tableNames as $table => $index) {
        $tableName = "cu_" . $table;
        $tableExists = $this->hasTable( $tableName );
        $indexName = "cu_" . $table . "_" . $index['title'];
        $indexColumns = $index['cols'];

        // check if index exists for this table
        $sql = "
          SELECT COUNT(*) FROM pg_class pgc 
          WHERE pgc.relname = '{$indexName}' 
            AND pgc.relkind = 'i'";

        $idxCount = $this->fetchAll($sql);
        $idxExists = intval($idxCount[0]['count']);

        if ($tableExists && $idxExists == 0) {
          $sql = "CREATE INDEX {$indexName} ON {$tableName} USING btree ($indexColumns)";
          $sqlRs = $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()
    {
      // loop through tables to update
      $tableNames = array(
        "scheduledtxn" => array("title"=>"cuuser_idx", "cols"=>"cu, user_id, id"), 
        "alerts" => array("title"=>"cuuser_idx", "cols"=>"cu, user_id, alerttype")
      );

      foreach ($tableNames as $table => $index) {
        $tableName = "cu_" . $table;
        $tableExists = $this->hasTable( $tableName );
        $indexName = "cu_" . $table . "_" . $index['title'];
        $indexColumns = $index['cols'];

        // check if index exists for this table
        $sql = "
          SELECT COUNT(*) FROM pg_class pgc 
          WHERE pgc.relname = '{$indexName}' 
            AND pgc.relkind = 'i'";

        $idxCount = $this->fetchAll($sql);
        $idxExists = intval($idxCount[0]['count']);

        if ($tableExists && $idxExists > 0) {
          $sql = "DROP INDEX {$indexName}";
          $sqlRs = $this->query($sql);
        }
      }
    }
}