2018041001_localidxupdate.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 LocalIdxUpdate 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()
    {
      // get the list of credit unions
      $cuList = $this->fetchAll('SELECT cu FROM cuadmin');

      for ( $i = 0; $i < count( $cuList ); $i++ ) {
        $cu = strtolower( trim( $cuList[$i]["cu"] ) );

        // loop through tables to update
        $tableNames = array(
          "achtemplate" => array("title"=>"group_idx", "cols"=>"tmpl_id, group_id"), 
          "extkey" => array("title"=>"provider_idx", "cols"=>"providermode, accountnumber, id"),
          "transhdr" => array("title"=>"feature_idx", "cols"=>"id, feature_code"), 
          "transdtl" => array("title"=>"transhdr_idx", "cols"=>"transhdr_id"), 
          "userlogins" => array("title"=>"user_idx", "cols"=>"user_id, user_name, hcucode")
        );

        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()
    {

      // get the list of credit unions
      $cuList = $this->fetchAll('SELECT cu FROM cuadmin');

      for ( $i = 0; $i < count( $cuList ); $i++ ) {
        $cu = strtolower( trim( $cuList[$i]["cu"] ) );

        // loop through tables to update
        $tableNames = array(
          "achtemplate" => array("title"=>"group_idx", "cols"=>"tmpl_id, group_id"), 
          "extkey" => array("title"=>"provider_idx", "cols"=>"providermode, accountnumber, id"),
          "transhdr" => array("title"=>"feature_idx", "cols"=>"id, feature_code"), 
          "transdtl" => array("title"=>"transhdr_idx", "cols"=>"transhdr_id"), 
          "userlogins" => array("title"=>"user_idx", "cols"=>"user_id, user_name, hcucode")
        );

        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);
          }
        }          
      }
    }
}