Login   Register  
PHP Classes
elePHPant
Icontem

File: extended_pgsql.class.php

Recommend this page to a friend!
Stumble It! Stumble It! Bookmark in del.icio.us Bookmark in del.icio.us
  Classes of Camilo Sperberg  >  Extended PGSQL  >  extended_pgsql.class.php  >  Download  
File: extended_pgsql.class.php
Role: Class source
Content type: text/plain
Description: The main class
Class: Extended PGSQL
Access PostGreSQL databases using PDO
Author: By
Last change: listing priority
Date: 3 years ago
Size: 16,316 bytes
 

Contents

Class file image Download
<?php
/** 
 * Extended PGsql, an database wrapper for PostGreSQL using PDO-pgsql
 * 
 * @package Database
 * @version 1.1.0 $Rev: 8 $
 * @copyright 2009 - 2011 Camilo Sperberg -- $Date: 2011-07-20 23:40:13 -0400 (Wed, 20 Jul 2011) $
 * @author Camilo Sperberg, http://unreal4u.com/ $Author: unreal4u $
 * @license BSD License
 */

if (empty($proc)) die('No direct access allowed');

class extended_pgsql {
  /**
   * Whether to cache the query or not
   * @var boolean $cache_query Defaults to FALSE
   */
  public  $cache_query = FALSE;
  /**
   * Maintains statistics of the executed queries
   * @var array $LiveStats 
   */
  public  $LiveStats = array();
  /**
   * Maintains statistics exclusively from the errors in SQL
   * @var array $errors
   */
  public  $errors = array();
  /**
   * Contains the actual DB connection
   * @var object $db
   */
  private $db = null;
  private $stmt = null;
  private $connected = FALSE;
  private $stats = array();
  private $error = FALSE;
  private $cache_recreate = FALSE;
  private $load_from_cache = FALSE;
  private $rows_from_cache = -1;
  private $num_rows = 0;
  private $in_transaction = FALSE;
  private $rollback = FALSE;
  private $insert_id = NULL;
  private $db_version = '';
  
	public function __construct($in_transaction = FALSE) {
	  if (version_compare(PHP_VERSION,'5.1.5','<')) die('Sorry, class only valid for PHP &gt; 5.1.5, please consider upgrading to the latest version');
	  if ($in_transaction === TRUE) $this->begin_transaction();
  }
	
	public function __destruct() {
    if ($this->in_transaction === TRUE AND $this->connected === TRUE) $this->end_transaction();
    if (DB_LOG_XML) $this->db_log();
	}
	
	public function __call($func,$arg_array) {
	  $this->stats = array('time' => time() + microtime(), 'memory' => memory_get_usage());
	  $this->error = FALSE;
    $this->load_from_cache = FALSE;

    if ($this->cache_query === FALSE) $this->cache_recreate = FALSE;
    elseif (!$this->valid_cache($arg_array)) $this->cache_recreate = TRUE;
    else {
      $this->cache_recreate  = FALSE;
      $this->load_from_cache = TRUE;
    }

    $log = TRUE;
    switch ($func) {
      case 'num_rows':
        $this->execute_query($arg_array);
        $num_rows = $this->execute_result_info($arg_array);
        $result   = $num_rows['num_rows'];
        break;
      case 'insert_id':
        $this->execute_query($arg_array);
        $num_rows = $this->execute_result_info();
        $result   = $num_rows['insert_id'];
        break;
      case 'query':
        $this->execute_query($arg_array);
        if (!$result = $this->execute_result_array($arg_array)) $result = FALSE;
        break;
      case 'begin_transaction':
        $this->connect_to_db();
        if ($this->in_transaction === FALSE) {
          $this->in_transaction = TRUE;
          $this->db->beginTransaction();
        }
        $log = FALSE;
        $result = TRUE;
        break;
      case 'end_transaction':
        $result = TRUE;
        if ($this->in_transaction) {
          $this->in_transaction = FALSE;
          if ($this->rollback === FALSE) $this->db->commit();
          else {
            $this->db->rollBack();
            $this->rollback = FALSE;
            $result = FALSE;
          }
        }
        $log = FALSE;
        break;
      case 'version':
        if (empty($this->db_version)) {
          $temp = $this->query('SELECT VERSION() AS v');
          $this->db_version = $temp[0]['v'];
          unset($temp);
        }
        if (!empty($arg_array[0])) {
          $temp = explode(' ',$this->db_version);
          $result = $temp[1];
        }
        else $result = $this->db_version;
        return $result;
      default:
        return 'Method not supported!';
        break;
    }
    if ($log) $this->logMe($arg_array,$result,$this->error,$this->load_from_cache);
    return $result;
  }
  
  public function __get($v) {
    $num_rows = $this->execute_result_info();
    if (!isset($num_rows[$v])) $num_rows[$v] = 'Method not supported';
    return $num_rows[$v];
  }
  
/**************************************************************************/
/*              DIRECT DATABASE RELATED                                   */
/**************************************************************************/
  /**
   * Connect with a singleton pattern to the DB
   */
  private function connect_to_db() {
    if ($this->connected === FALSE) {
      $db_connect   = pgsql_connect::singleton();
      $this->db     = $db_connect->db;
      $this->connected = TRUE;
    }
    return $this->connected;
  }
    
  private function execute_query($arg_array) {
    $execute_query = FALSE;
    $output = TRUE;
    if ($this->cache_query === FALSE OR $this->cache_recreate === TRUE) {
      $sql_query = array_shift($arg_array);
      $this->connect_to_db();

      if (isset($this->stmt)) unset($this->stmt);
      $this->stmt = $this->db->prepare($sql_query);
      if (!is_object($this->stmt)) $this->logError($sql_query,$this->stmt->errorCode(),'fatal',$this->stmt->errorInfo());
      
      if (!$this->error) {
        try {
          if (!$this->stmt->execute($arg_array)) throw new Exception('Problems with the query',E_USER_WARNING);
          else $this->insert_id = $this->get_last_insert_id($sql_query);
        }
        catch (Exception $e) {
          $this->logError($sql_query, $this->stmt->errorCode(), 'fatal', $this->stmt->errorInfo());
          $output = FALSE;
        }
      }
    }  
    return $output;
  }
  
  private function get_last_insert_id($query) {
    $id[0] = NULL;
    if(preg_match("/^INSERT[\t\n ]+INTO[\t\n ]+([a-z0-9\_\-]+)/is", $query, $tablename) AND stripos($query,' returning ') !== FALSE) $id = $this->stmt->fetch(PDO::FETCH_NUM);
    return $id[0];
  }
  
  private function execute_result_array($arg_array) {
    if (!$this->error) {
      if ($this->cache_query === FALSE OR $this->cache_recreate === TRUE) {
        $result = $this->stmt->fetchAll();
        if ($this->cache_recreate === TRUE AND !empty($result)) $this->create_cache($arg_array,$result);
      }
      else $result = $this->get_cache($arg_array);
    }
    else $result = 0;

    return $result;    
  }
  
  private function execute_result_info($arg_array = NULL) {
    return array('num_rows' => $this->stmt->rowCount(),'insert_id' => $this->insert_id);
  }
  
/**************************************************************************/
/*                     DATABASE CACHE RELATED                             */
/**************************************************************************/
  /**
   * Function that establish the cache filename
   * @param array $arg_array Used to create the filename
   * @return string Returns the filename of the cache file
   */
  private function filename($arg_array = NULL) {
    $filename = '0';
    if (!empty($arg_array)) {
      foreach ($arg_array AS $a) $filename .= $a;
      $filename = 'db_'.md5($filename);
    }
    return DB_CACHE_LOCATION.$filename.'.xml';
  }

  /**
   * Checks whether our cache file is still valid.
   * @param array $arg_array Used to create the filename
   * @return boolean TRUE if cache file is still valid, FALSE otherwise
   */
  private function valid_cache($arg_array = NULL) {
    $filename = '';
    $is_valid = FALSE;
    if (is_array($arg_array)) {
      $filename = $this->filename($arg_array);
      if (file_exists($filename)) {
        if (filemtime($filename) > time() - DB_CACHE_EXPIRE) $is_valid = TRUE;
        else if (!@unlink($filename)) $this->logError($arg_array[0],0,'non-fatal','Couldn\'t delete old cache file! Check permissions');
      }
    }
    return $is_valid;
  }

  /**
   * Function that creates a valid XML file.
   * 
   * I'm not using SimpleXML here because of speed.
   * Using SimpleXML, with 100.000 records, it takes 26 seconds, this 
   * way, only 1 second (on my test server)
   * 
   * @param array $arg_array Used to create the filename
   * @param array $result Used to replicate the result in the XML file.
   * @return boolean Returns always TRUE.
   */
  private function create_cache($arg_array,$result) {
    $i = 0;
    $xml = ''; $done = TRUE;
    foreach($result AS $r) {
      $xml .= '<r'.$i.'>';
      foreach($r AS $key => $value) $xml .= '<'.$key.'>'.$value.'</'.$key.'>';
      $xml .= '</r'.$i.'>';
      $i++;
    }
    if (!@file_put_contents($this->filename($arg_array),'<?xml version="1.0" encoding="UTF-8"?>'."\n".'<db>'.$xml.'</db>')) {
      echo $this->filename($arg_array);
      $this->logError($arg_array[0],0,'non-fatal','Couldn\'t create cache file!');
      $this->cache_query = FALSE;
      $this->cache_recreate = FALSE;
      $done = FALSE;
    }
    unset($xml);
    return $done;
  }

  /**
   * Returns number of rows in the XML.
   * @return int Number of rows
   */
  private function get_cache_meta() {
    return $this->rows_from_cache;
  }

  /**
   * Parses and returns the XML in an array.
   * @param array $arg_array Used to create the filename
   * @return array The result set rescued from the cache file
   */
  private function get_cache($arg_array = NULL) {
    $i = 0;
    $xml = simplexml_load_file($this->filename($arg_array));
    foreach($xml AS $x => $value) {
      foreach($value AS $v => $s) $bTemp[$v] = (string)$s;
      $r[$i] = $bTemp;
      $i++;
      $bTemp = NULL;
    }
    $this->rows_from_cache = $i;
    unset($xml,$i,$bTemp,$value,$x,$v,$s);
    return $r;
  }
  
/**************************************************************************/
/*             LOGGING AND DEBUGGING                                      */
/**************************************************************************/
  /**
   * Function that logs all errors
   * @param string $query The query to log
   * @param int $errno The error number to log
   * @param string $type Whether the error is fatal or non-fatal
   * @param string $error The error description
   * @return boolean Always returns TRUE.
   */
  private function logError($query,$errno,$type='non-fatal',$error) {
    $query_num = count($this->LiveStats);
    if (is_array($error)) $error_string = $error[2];
    else $error_string = $error;
    if (empty($error_string)) $error_string = '(not specified)';
    else if ($type == 'non-fatal') $complete_error = '[NOTICE] '.$error_string;
         else {
           $complete_error = '[ERROR] '.$error_string;
           $this->rollback = TRUE;
         }
    if ($this->in_transaction === TRUE) $in_transaction = 'TRUE';
    else $in_transaction = 'FALSE';
    $this->errors[$query_num] = array('query_number' => $query_num,'query' => $query,'in_transaction' => $in_transaction, 'errno' => $errno,'type'  => $type,'error' => $complete_error);
    if ($type == 'fatal') { 
      $this->error = '['.$errno.'] '.$error[2];
      $this->results = 0;
    }
    return TRUE;
  }

  /**
   * Function that executes after each query and also acumulates data for the XML log
   * @param array $stats
   * @param array $arg_array
   * @param array $result
   * @param boolean $error
   * @param boolean $from_cache
   * @return boolean Always returns TRUE.
   */
  private function logMe($arg_array,$result,$error,$from_cache) {
    $this->cache_query = FALSE;

    $this->stats = array('memory' => memory_get_usage() - $this->stats['memory'], 'time' => number_format((time()+microtime()) - $this->stats['time'],5,',','.'));

    $this->liveStats($arg_array,$this->stats,$error,$from_cache);
    if (isset($arg_array[0])) $query = $arg_array[0];
    else $query = '';
    return TRUE;
  }

  /**
   * Live Statistics, can be embedded in source code to quickly check some things
   * @param string $query
   * @param array $stats
   * @param boolean $error
   * @param boolean $from_cache
   * @return boolean Always returns TRUE.
   */
  private function liveStats ($query, $stats = NULL, $error = FALSE, $from_cache = FALSE) {
    if ($error == FALSE) $error = 'FALSE';
    if (!is_array($stats) OR empty($stats)) $stats = array('time' => 0,'memory' =>0);
    if ($from_cache === TRUE) $valid_cache = 'TRUE';
    else $valid_cache = 'FALSE';
    if ($this->in_transaction === TRUE) $in_trans = 'TRUE';
    else $in_trans = 'FALSE';

    $results = $this->num_rows;
    if ($this->cache_query === TRUE) $this->rows_from_cache = $results;

    $this->LiveStats[] = array('query' => $query,'number_results' => $results,'time'  => $stats['time'].' (seg)','memory' => $stats['memory'].' (bytes)','error' => $error,'from_cache' => $valid_cache,'within_transaction' => $in_trans);
    return TRUE;
  }
  
  /**
   * Function that creates a log in XML format
   * @param array $query_arr
   * @return boolean Returns always TRUE.
   */
  private function db_log() {
    $error = FALSE;
    $num_queries = count($this->LiveStats);
    if ($num_queries > 0) {
      if (!empty($_SERVER['HTTP_REFERER'])) $referer = $_SERVER['HTTP_REFERER'];
      else $referer = 'None';

      if (!is_writable(dirname(DB_URL_XML))) $error = TRUE;
      else {
        if (!is_readable(DB_URL_XML)) {
          if (file_exists(DB_URL_XML)) $errors = TRUE;
          $xml = simplexml_load_string('<?xml version="1.0" encoding="UTF-8"?><db_log></db_log>');
        }
        else $xml = simplexml_load_file(DB_URL_XML);
      }
      if (!$error) {
        $i = 0;
        $final = $xml->addChild('pageview');
        $final->addChild('nQueries',$num_queries);
        $final->addChild('dDateTime',date('d-m-Y, h:i'));
        $final->addChild('sIP',$_SERVER['REMOTE_ADDR']);
        $final->addChild('sBrowser',$_SERVER['HTTP_USER_AGENT']);
        $final->addChild('sUrl',htmlentities($_SERVER['REQUEST_URI']));
        $final->addChild('sRef',htmlentities($referer));
        $consultas = $final->addChild('myquery');
        foreach($this->LiveStats AS $k => $q) {
          if ($q['error'] == FALSE) $q['error'] = 'FALSE';
          $detalle[$i] = $consultas->addChild('query_'.$i);
          $detalle[$i]->addChild('sSql',$q['query'][0]);
          $detalle[$i]->addChild('nResults',$q['number_results']);
          $detalle[$i]->addChild('fTime',$q['time'].' (seg)');
          $detalle[$i]->addChild('iMemory',$q['memory'].' (bytes)');
          $detalle[$i]->addChild('iError',$q['error']);
          $i++;
        }
        if (!$xml->asXML(DB_URL_XML)) $error = TRUE;
      }
      unset($referer,$detalle,$final,$consultas,$xml,$q,$k,$i,$num_queries);
    }
  }
}

/**************************************************************************/
/*                      SECONDARY CLASS                                   */
/**************************************************************************/
/**
 * Singleton class to connect to DB
 * 
 * @author Camilo Sperberg
 */
class pgsql_connect {
  private static $instance;
  private $connected = FALSE;

  public static function singleton() {
    if (!isset(self::$instance)) {
       $c              = __CLASS__;
       self::$instance = new $c;
    }
    return self::$instance;
  }

  public function __clone() {
    if (DB_SHOW_ERRORS === TRUE) trigger_error('We can only declare this once!', E_USER_ERROR);
    else die();
  }

  public function __construct() {
    $this->connected = TRUE;
    try {
      if (PGSQL_HOST == 'localhost') $host = '';
      else $host = 'host='.PGSQL_HOST;
      if (PGSQL_PORT == 5432) $port = '';
      else $port = ';port='.PGSQL_PORT.';';
      $this->db = new PDO('pgsql:'.$host.'dbname='.PGSQL_NAME.$port,PGSQL_USER,PGSQL_PASS);
      $this->db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PGSQL_FETCH_MODE); 
    }
    catch(Exception $e) {
      $this->connected = FALSE;
      if (DB_SHOW_ERRORS === TRUE) trigger_error($e->getMessage(), E_USER_ERROR);
      else die();
    }
  }

  public function __destruct() {
    if ($this->connected === TRUE) $this->db = NULL;
    $this->connected = FALSE;
  }
}