db.postgres.i

<?php
/**
 * Plugin script for Postgres functions
 *
 * This file will contain the homecu database functions in relation to a postgres connection
 *
 */

/**
 * DEFINE CONSTANTS FOR DATATYPES
 *  serial
 *            integer
 *            smallint
 *            bigint
 *            numeric
 *            boolean
 *            char
 *            varchar
 *            date
 *            timestamptz
 *            timestamp
 */
  define("DBTYPE_INTEGER",     "integer");
  define("DBTYPE_SERIAL",      "serial");
  define("DBTYPE_SMALLINT",    "smallint");
  define("DBTYPE_BIGINT",      "bigint");
  define("DBTYPE_NUMERIC",     "numeric");
  define("DBTYPE_BOOLEAN",     "boolean");
  define("DBTYPE_CHAR",        "char");
  define("DBTYPE_VARCHAR",     "varchar");
  define("DBTYPE_DATE",        "date");
  define("DBTYPE_TIMESTAMPTZ", "timestamptz");
  define("DBTYPE_TIMESTAMP",   "timestamp");
  // ** this can be used to pass custom expressions as values (eg. function
  //  calls, short expressions to evaluate column values on the fly, etc.)
  define("DBTYPE_CUSTOM_EXPRESSION",   "expression");
  // ** These functions return the start time of the current transaction.
  define("DBTIMESTAMP_USENOW", "now");
  // ** Returns the actual current time, and therefore its value changes
  define("DBTIMESTAMP_USETS", "current_timestamp");
  define("DBNULL", "null");

  define ("HOMECU_WORK_BEGIN",    "BEGIN");
  define ("HOMECU_WORK_COMMIT",   "COMMIT");
  define ("HOMECU_WORK_ROLLBACK", "ROLLBACK");

//

function db_connect($pDbEnvSettings) {

   $connString = "user=" . HCU_array_key_value('user', $pDbEnvSettings) .
                  " password=" . HCU_array_key_value('password', $pDbEnvSettings) .
                  " port=" . HCU_array_key_value('port', $pDbEnvSettings) .
                  " dbname=" . HCU_array_key_value('dbname', $pDbEnvSettings) .
                  " host=" . HCU_array_key_value('host', $pDbEnvSettings) ;

                  // See if it works if localhost is specified, if not use this
                  //(HCU_array_key_value('user', $pDbEnvSettings) !== 'localhost' ? " host=" . HCU_array_key_value('host', $pDbEnvSettings) : "");

  $retConn = @pg_connect($connString);

  return $retConn;

}

/**
 *
 *
 * @param array $pDbEnvSettings  This should be the working environment
 *                  array as returned from the function LoadSystemEnv
 *                       [
 *                         'host' => GetEnvSetting('DATABASE_HOST', 'localhost'),
 *                         'port' => GetEnvSetting('DATABASE_PORT', 5342),
 *                         'dbname' => GetEnvSetting('DATABASE_NAME', 'homecu_prod1'),
 *                         'user' => GetEnvSetting('DATABASE_USER', 'postgres'),
 *                         'password' => GetEnvSetting('DATABASE_PASSWORD'),
 *                         'connect_timeout' => GetEnvSetting('DATABASE_CONNECT_TIMEOUT'),
 *                         'platform' => GetEnvSetting('DATABASE_PLATFORM')
 *                       ],
 *
 * @return mixed  Database connection reference or false
 *
 */
function db_pconnect($pDbEnvSettings) {

   $connString = "user=" . HCU_array_key_value('user', $pDbEnvSettings) .
                  " password=" . HCU_array_key_value('password', $pDbEnvSettings) .
                  " port=" . HCU_array_key_value('port', $pDbEnvSettings) .
                  " dbname=" . HCU_array_key_value('dbname', $pDbEnvSettings) .
                  " host=" . HCU_array_key_value('host', $pDbEnvSettings) ;

                  // See if it works if localhost is specified, if not use this
                  //(HCU_array_key_value('user', $pDbEnvSettings) !== 'localhost' ? " host=" . HCU_array_key_value('host', $pDbEnvSettings) : "");
  $retConn = @pg_pconnect($connString);

  return $retConn;
}

function db_query($qstring,$qhandle) {
  if ($qhandle) {
    return @pg_query ($qhandle, $qstring);
  } else {
    return 0;
  }
}

function db_num_rows($qhandle) {
  if ($qhandle) {
    return @pg_num_rows($qhandle);
  } else {
    return 0;
  }
}

function db_result($qhandle,$row,$field) {
  if ($qhandle) {
    return @pg_result($qhandle,$row,$field);
  } else {
    return 0;
  }
}

function db_num_fields($qhandle) {
  if ($qhandle) {
    return @pg_numfields($qhandle);
  } else {
    return 0;
  }
}

function db_fieldname($qhandle,$fnumber) {
  if ($qhandle) {
    return @pg_fieldname($qhandle,$fnumber);
  } else {
    return 0;
 }
}

function db_affected_rows($qhandle) {

  if ($qhandle) {
    return @pg_affected_rows($qhandle);
  } else {
    return 0;
  }
}

function db_fetch_array($qhandle, $row=null) {
  if ($qhandle) {
    return @pg_fetch_array($qhandle,$row);
  } else {
    return 0;
  }
}
function db_fetch_assoc($qhandle, $row=null) {

  if ($qhandle) {
    return @pg_fetch_assoc($qhandle,$row);
  } else {
    return 0;
  }
}


function db_fetch_object($qhandle,$row=null) {
  if ($qhandle) {
    return @pg_fetch_object($qhandle,$row);
  } else {
    return 0;
  }
}

function db_fetch_row($qhandle,$row=null) {
  if ($qhandle) {
    return @pg_fetch_row($qhandle,$row);
  } else {
    return 0;
  }
}

function db_fetch_all($qhandle) {
  global $dbplat;
  if ($qhandle) {
    return @pg_fetch_all($qhandle);
  }
}

function db_insert_id($qhandle) {
   if ($qhandle) {
    // ** I don't believe this function will return what we want.
      // * this returns the record oid, not the identity column
    return @pg_getlastoid($qhandle);
  } else {
    return 0;
  }
}

function db_error() {
  return @pg_errormessage();
}

function db_last_error() {
  return @pg_last_error();
}

function db_free_result($qhandle) {
  if ($qhandle) {
    return @pg_freeresult($qhandle);
  } else {
    return 0;
  }
}

function db_cant_exist($sql, $qhandle) {
   global $dbplat;
  $result=0;
  $keycheck=db_query($sql, $qhandle);

  if(@pg_num_rows($keycheck) == 0) {$result = 1;}

  return $result;
}

/**
 * Test the current status of the database connection
 *
 * @param resource $connection This is the current database connection that is being tested
 *
 * @return integer PGSQL_CONNECTION_OK or PGSQL_CONNECTION_BAD
 */
function db_connection_status($qhandle) {
  $retStatus;

  $retStatus = pg_connection_status($qhandle);

  return $retStatus;
}


/**
 *  What is the current connections transction status
 *
 *  @param integer $pDbh  - The current dB connection
 *
 *  @return integer       - This will return the standard php postgres constants
 *  **
 *  ** PGSQL_TRANSACTION_IDLE (currently idle)
 *  ** PGSQL_TRANSACTION_ACTIVE (a command is in progress)
 *  ** PGSQL_TRANSACTION_INTRANS (idle, in a valid transaction block)
 *  ** PGSQL_TRANSACTION_INERROR (idle, in a failed transaction block)
 *  ** PGSQL_TRANSACTION_UNKNOWN is reported if the connection is bad
 *  ** PGSQL_TRANSACTION_ACTIVE is reported only when a query has been sent to the server and not yet completed.
 *  **
 */
function db_transaction_status($pDbh) {


  if (db_connection_status($pDbh) === PGSQL_CONNECTION_OK) {
   return pg_transaction_status($pDbh);
  } else {
    return false;
  }
}

/* *** TRANSACTION FUNCTION *** */
/**
 *  Send a WORK statement to the database connection
 *  This function will be used for BEGIN, ROLLBACK, COMMIT
 *  The calling code must be smart enough to rollback if commit fails
 *  The purpose of this function is to encapsulate the WORK queries,
 *    BUT not to add logic to handle failures
 *
 * @param integer  $pDbh      -- The current database connection
 * @param constant $pWorkType -- The work query options are:
 *                                  HOMECU_WORK_BEGIN     -- BEGIN a transaction work block
 *                                  HOMECU_COMMIT_BEGIN   -- COMMIT a transaction work block  (only if currently in transaction)
 *                                  HOMECU_ROLLBACK_BEGIN -- ROLLBACK a transaction work block (only if currently in transaction)
 * @return boolean            -- {true if request successful, false if fail}
 */
function db_work ($pDbh, $pWorkType) {
  $retVal = false;      // Start with failure
  switch ($pWorkType) {
    case HOMECU_WORK_BEGIN:
      $sql = "BEGIN WORK;";
      break;
    case HOMECU_WORK_COMMIT:
      $sql = "COMMIT WORK;";
      break;
    case HOMECU_WORK_ROLLBACK:
      $sql = "ROLLBACK WORK;";
      break;
    default:
      // ** ERROR -- return false now
      return $retVal;
  }

  if ($pWorkType == HOMECU_WORK_BEGIN) {
    $workRs = db_query($sql, $pDbh);

    if ($workRs) {
      $retVal = true;
    }
  } else {
    // * For COMMIT/ROLLBACK, slightly different - be sure we are in a transaction prior
    $transStatus = db_transaction_status($pDbh);
    if ($transStatus === PGSQL_TRANSACTION_ACTIVE || $transStatus === PGSQL_TRANSACTION_INTRANS) {
      $workRs = db_query($sql, $pDbh);

      if ($workRs) {
        $retVal = true;
      }
    } else {
      // ** Since there was no active transaction all queries were committed, so this
      // ** COMMIT
            // * This function will return true in this state because the queries are being committed
            // * So the subsequent queries should act like the commit was successful, since they can't be rolled back

      // * ROLLBACK
      // * *should return false, since the rollback was not successful
      $retVal = ($pWorkType == HOMECU_WORK_COMMIT ? true : false);

    }
  }

  return $retVal;
}

function db_must_exist($sql, $qhandle) {
  $result=0;
  $keycheck = db_query($sql, $qhandle);

  if(@pg_num_rows($keycheck) > 0) {$result = 1;}

  return $result;
}

function prep_save($text, $msize=0) {
        // Currently this is ONLY setup for POSTGRES
        // What will need to be done is double up the ' or ", depending on
        // which is used to define the string

        // Before modifying the text, make sure it does NOT go past the maximum
        // NOTE: if msize is 0, then that is the same as unlimited
    if ($msize > 0) {
            $text = substr($text, 0, $msize);
  }
    $text = pg_escape_string(trim($text));
    return $text;
}

/**
 *
 * This will build the expression value for a value based on it's column data type
 *
 * @param string $pValue - This is the value that will be evaluated
 * @param array[] $pColAttr - This is the array of column attributes for the columns
 *
 * @return string  The returned string will be escaped for input into postgres
 *
 *
 */
function db_build_expr_value($pValue, $pColAttr) {
  $retStr = '';

  if (is_array($pColAttr)) {
    // ** be sure the column attr is an array
    switch (HCU_array_key_value('type', $pColAttr)) {
      case DBTYPE_SERIAL:
        $retStr = intval($pValue);
        if ($retStr == 0)
          $retStr= "default"; // Get the next in the sequence.
        break;
      case DBTYPE_INTEGER:
      case DBTYPE_SMALLINT:
      case DBTYPE_BIGINT:
        $retStr = (is_null($pValue) || trim($pValue) == "") && HCU_array_key_value('nullable', $pColAttr) ? DBNULL : intval($pValue);
        break;
      case DBTYPE_NUMERIC:
        // Evalute for the precision.
        // ** Precision attribute is optional..
            // ** If it is not there then assume 0 lenght for decimals
        $precision = explode(',', HCU_array_key_value('precision', $pColAttr));
        $decimals = ((count($precision) > 1) ? intval($precision[1]) : 0);
        $retStr = (is_null($pValue) || trim($pValue) == "") && HCU_array_key_value('nullable', $pColAttr) ? DBNULL : number_format(floatval($pValue), $decimals, '.', '');
        break;
      case DBTYPE_VARCHAR:
      case DBTYPE_CHAR:
        $retStr = "'" . prep_save($pValue, intval(HCU_array_key_value('maxlength', $pColAttr))) . "'";
        break;
      case DBTYPE_CUSTOM_EXPRESSION:
        $retStr = prep_save($pValue);
        break;
      case DBTYPE_TIMESTAMP:
      case DBTYPE_TIMESTAMPTZ:
        /**
         * If the value is set to NOW, then use the postgresql function now
         */
        if ($pValue == DBTIMESTAMP_USENOW) {
          $retStr = " now() ";
        } elseif ($pValue == DBTIMESTAMP_USETS) {
          $retStr = " clock_timestamp() ";
        } elseif ($pValue == '') {
          // ** If the value is empty, then the value saved is NULL
          $retStr = " NULL ";
        } else {
          $retStr = "'" . prep_save($pValue) . "'";
        }
        break;
      case DBTYPE_BOOLEAN:
        $retStr= $pValue === true ? "TRUE" : ($pValue === false ? "FALSE" : ""); // Simplest case is if there is already a boolean in the value.
        if ($retStr == "")
        {
          $nullable= HCU_array_key_value('nullable', $pColAttr);
          $pValue= is_null($pValue) ? "" : trim(strtolower($pValue));
          $retStr= $pValue == "" ? ($nullable ? DBNULL : "FALSE")
            : (in_array($pValue, array("true", "t", "y", "yes", "1")) ? "TRUE" :
              (in_array($pValue, array("false", "f", "n", "no", "0")) ? "FALSE" : ($nullable ? DBNULL : "FALSE")));
        }
        break;
    }
  }
  return $retStr;
}


/**
 * This function will return all the rows found in the sql.
 * If any errors are encountered, it will return empty array
 *
 *
 * @param object $pDbh - the current database handle
 * @param string $pSql - The SQL string to execute
 *
 * @return mixed[]  - return value is an array of rows from the query
 *                  ON ERROR this will return FALSE  ( === false)
 */
function GetAllRowsFromSql($pDbh, $pSql) {

  $retRows = array();

  try {
    /**
     * TEST DB CONNECTION
     */
    if (db_connection_status($pDbh) == PGSQL_CONNECTION_BAD) {
      throw new exception("Database Status Error", "901");
    }

    $dbResults = db_query($pSql, $pDbh);

    if ($dbResults !== false) {
      $retRows = db_fetch_all($dbResults);
    } else {
      throw new exception(db_last_error(), "902");
    }


  } catch (exception $e) {
    $retRows = false;
  }

  return $retRows;
}