Dave Jarvis' Repositories

<?php
namespace com\whitemagicsoftware;

require "login.php";

use PDO;
use PDOException;

/**
 * Used for interacting with the database. Usage:
 * <pre>
 * $db = Database::get();
 * $db->call( ... );
 * </pre>
 *
 * TODO: Don't fail silently.
 */
class Database extends Obj {
  private static $instance;
  private $dataStore;

  /**
   * Sets the connection that this class uses for database transactions.
   */
  public function __construct() {
    global $dbhost;
    global $dbname;
    global $dbuser;
    global $dbpass;

    try {
      $this->setDataStore(
        new PDO( "pgsql:dbname=$dbname;host=$dbhost", $dbuser, $dbpass ) );
    }
    catch( PDOException $ex ) {
      $this->log( $ex->getMessage() );
    }
  }

  /**
   * Returns the singleton database instance.
   */
  public static function get() {
    if( self::$instance === null ) {
      self::$instance = new Database();
    }

    return self::$instance;
  }

  /**
   * Call a database function and return the results. If there are
   * multiple columns to return, then the value for $params must contain
   * a comma; otherwise, without a comma, the value for $params is used
   * as the return column name. For example:
   *
   *- SELECT $params FROM $proc( ?, ? ); -- with comma
   *- SELECT $proc( ?, ? ) AS $params; -- without comma
   *- SELECT $proc( ?, ? ); -- empty
   *
   * @param $proc Name of the function or stored procedure to call.
   * @param $params Name of parameters to use as return columns.
   */
  public function call( $proc, $params = "" ) {
    $args = array();
    $count = 0;
    $placeholders = "";

    // Key is zero-based (e.g., $proc = 0, $params = 1).
    foreach( func_get_args() as $key => $parameter ) {
      // Skip the $proc and $params arguments to this method.
      if( $key < 2 ) continue;

      $count++;
      $placeholders = empty( $placeholders ) ? "?" : "$placeholders,?";
      array_push( $args, $parameter );
    }

    $sql = "";

    if( empty( $params ) ) {
      // If there are no parameters, then just make a call.
      $sql = "SELECT recipe.$proc( $placeholders )";
    }
    else if( strpos( $params, "," ) !== false ) {
      // If there is a comma, select the column names.
      $sql = "SELECT $params FROM recipe.$proc( $placeholders )";
    }
    else {
      // Otherwise, select the result into the given column name.
      $sql = "SELECT recipe.$proc( $placeholders ) AS $params";
    }

    $db = $this->getDataStore();
    $statement = $db->prepare( $sql );

    //$this->log( $sql );

    for( $i = 1; $i <= $count; $i++ ) {
      //$this->log( "Bind " . $i . " to " . $args[$i - 1] );
      $statement->bindParam( $i, $args[$i - 1] );
    }

    try {
      $result = null;

      if( $statement->execute() === true ) {
        $result = $statement->fetchAll( PDO::FETCH_ASSOC );
        $this->decodeArray( $result );
      }
      else {
        // \todo Send an e-mail.
        $info = $statement->errorInfo();
        $this->log( "SQL failed: $sql" );
        $this->log( "Error: ". $info[2] );
      }
    }
    catch( PDOException $ex ) {
      // \todo Send an e-mail.
      $this->log( $ex->getMessage() );
    }

    return $result;
  }

  /**
   * Converts an array of numbers into an array suitable for usage with
   * PostgreSQL.
   *
   * @param $array An array of integers, an empty string, or null.
   */
  public function arrayToString( $array ) {
    return "{" . implode( ",", (array)$array ) . "}";
  }

  /**
   * Given an array of items, this will convert the array to a PostgreSQL-
   * compatible string (suitable for passing as a parameter to a function
   * call).
   *
   * The primary intent of this method is to avoid multiple database calls
   * when inserting a list of instructions.
   *
   * @see http://stackoverflow.com/a/5632171/59087
   * @see http://www.youlikeprogramming.com/2013/01/interfacing-postgresqls-hstore-with-php/
   */
  public function toArray( $set ) {
    $set = (array)$set;
    $result = array();

    foreach( $set as $s ) {
      if( is_array( $s ) ) {
        $result[] = $this->toArray( $s );
      }
      else {
        // Enclose each element in double-quotes, with internal quotes
        // escaped.
        $result[] = '"' . str_replace( '"', '\\"', $s ) . '"';
      }
    }

    return '{' . implode( ",", $result ) . '}';
  }

  /**
   * Recursive method to decode a UTF8-encoded array.
   *
   * @param $array - The array to decode.
   * @param $key - Name of the function to call.
   */
  private function decodeArray( &$array ) {
    if( is_array( $array ) ) {
      array_map( array( $this, "decodeArray" ), $array );
    }
    else {
      $array = utf8_decode( $array );
    }
  }

  private function getDataStore() {
    return $this->dataStore;
  }

  private function setDataStore( $dataStore ) {
    $dataStore->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
    $this->dataStore = $dataStore;
  }
}