2019061100_addcorerequestsidx.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 AddCoreRequestsIDX 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 indexes to update
      $indexNames = array(
        "cucorerequests_idx01" => array("cols"=>"cu, accountnumber, request_start, request_end"),
        "cucorerequests_idx02" => array("cols"=>"appliance_ip, accountnumber, request_start, request_end"),
        "cucorerequests_idx03" => array("cols"=>"cu, request_type, request_start, request_end")
      );

      $tableName = "cucorerequests";
      $tableExists = $this->hasTable( $tableName );

      foreach ($indexNames as $index => $cols) {
        $indexName = $tableName . "_" . $index;
        $indexColumns = $cols['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 indexes to update
      $indexNames = array(
        "cucorerequests_idx01" => array("cols"=>"cu, accountnumber, request_start, request_end"),
        "cucorerequests_idx02" => array("cols"=>"appliance_ip, accountnumber, request_start, request_end"),
        "cucorerequests_idx03" => array("cols"=>"cu, request_type, request_start, request_end")
      );

      $tableName = "cucorerequests";
      $tableExists = $this->hasTable( $tableName );

      foreach ($indexNames as $index => $cols) {
        $indexName = $tableName . "_" . $index;
        $indexColumns = $cols['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);
        }
      }
    }
}