2017040300_extaccount.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 ExtAccount 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"] ) );

        // check if we already made the change
        $tableName = "{$cu}extaccount";
        $exists = $this->hasTable( $tableName );
        if ( !$exists ) {
          // announce it
          print "Adding table: $cu {$tableName}\n";

          // create the SQL for the extaccount table
          $sql = "CREATE TABLE {$cu}extaccount (
                  id SERIAL PRIMARY KEY,
                  user_id integer NOT NULL,
                  display_name varchar(20),
                  status character(1),
                  remote_info varchar
                  )";

          // create the extaccount table
          $rows = $this->query( $sql );

          // add an index so the display name is unique per user
          $sql = "CREATE UNIQUE INDEX {$cu}extaccount_name_key on {$cu}extaccount USING btree ( user_id, display_name );";
          $rows = $this->query( $sql );

          // add some comments
          $sql = "COMMENT ON COLUMN {$cu}extaccount.user_id is 'User who set it up; foreign key to <cu>user.user_id';";
          $rows = $this->query( $sql );

          $sql = "COMMENT ON COLUMN {$cu}extaccount.display_name is 'Name that displays in Banking';";
          $rows = $this->query( $sql );

          $sql = "COMMENT ON COLUMN {$cu}extaccount.status is 'Pending/Active/Inactive flag';";
          $rows = $this->query( $sql );

          $sql = "COMMENT ON COLUMN {$cu}extaccount.remote_info is 'JSON object that holds the remote account information';";
          $rows = $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"] ) );

        // check if we already made the change
        $tableName = "{$cu}extaccount";
        if ( $this->hasTable( $tableName ) ) {
          // announce it
          print "Dropping $cu {$tableName}\n";
          $this->dropTable( $tableName );
        }
      }
    }
}