2018121800_addCucorerequests.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 AddCucorerequests 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() {

    // create cucorerequests table if it doesn't exist
    $exists = $this->hasTable("cucorerequests");

    if (!$exists) {
      $sql = "
        CREATE TABLE cucorerequests (
        id integer NOT NULL,
        cu character(12),
        appliance_ip character varying(100),
        accountnumber character(12),
        request_type character varying(20),
        request_url character varying,
        request_start timestamp without time zone,
        request_end timestamp without time zone,
        request_elapsed integer,
        request_status character varying,
        remote_ip character(15)
        );";
      $sqlRs = $this->query($sql);

      // add comments for table and columns
      $sql = "COMMENT ON TABLE cucorerequests IS 'Store status info of the requests made to a live credit union core.';";
      $sqlRs = $this->query($sql);

      $sql = "COMMENT ON COLUMN cucorerequests.cu IS 'The Credit Union Code.';";
      $sqlRs = $this->query($sql);

      $sql = "COMMENT ON COLUMN cucorerequests.appliance_ip IS 'The target appliance ip or dns name.';";
      $sqlRs = $this->query($sql);

      $sql = "COMMENT ON COLUMN cucorerequests.accountnumber IS 'Associated member accountnumber.';";
      $sqlRs = $this->query($sql);

      $sql = "COMMENT ON COLUMN cucorerequests.request_type IS 'Type of core request made.';";
      $sqlRs = $this->query($sql);

      $sql = "COMMENT ON COLUMN cucorerequests.request_url IS 'Request URL to core vendor appliance.';";
      $sqlRs = $this->query($sql);

      $sql = "COMMENT ON COLUMN cucorerequests.request_start IS 'Request start timestamp.';";
      $sqlRs = $this->query($sql);

      $sql = "COMMENT ON COLUMN cucorerequests.request_end IS 'Request end timestamp.';";
      $sqlRs = $this->query($sql);

      $sql = "COMMENT ON COLUMN cucorerequests.request_elapsed IS 'Duration of request in seconds.';";
      $sqlRs = $this->query($sql);

      $sql = "COMMENT ON COLUMN cucorerequests.request_status IS 'Status composed as a JSON string.';";
      $sqlRs = $this->query($sql);

      $sql = "COMMENT ON COLUMN cucorerequests.remote_ip IS 'Remote IP Address.';";
      $sqlRs = $this->query($sql);

      // create sequence
      $sql = "
        CREATE SEQUENCE cucorerequests_id_seq
        START WITH 1
        INCREMENT BY 1
        NO MINVALUE
        NO MAXVALUE
        CACHE 1;";
      $sqlRs = $this->query($sql);

      // set sequence owner
      $sql = "ALTER SEQUENCE cucorerequests_id_seq OWNED BY cucorerequests.id;";
      $sqlRs = $this->query($sql);

      $sql = "ALTER TABLE ONLY cucorerequests ALTER COLUMN id SET DEFAULT nextval('cucorerequests_id_seq'::regclass);";
      $sqlRs = $this->query($sql);

      // set primary key constraint on id column
      $sql = "ALTER TABLE ONLY cucorerequests ADD CONSTRAINT pk_cucorerequests PRIMARY KEY (id);";
      $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() {
    // drop cucorerequests table
    $this->dropTable("cucorerequests");
  }
}

?>