_res/_libs/postgresql.class.php

   1 <?php
   2
   3 class postgreSql extends interfaceSql {
   4         public          $conn,
   5                                 $link;
   6         private         $name,
   7                                 $host,
   8                                 $user,
   9                                 $pass;
  10
  11         public function __construct( $dbHost = null, $dbUser = null, $dbPass = null , $dbName = null ){
  12                 if (!empty ($dbHost))
  13                         $this->host     = $dbHost;
  14                 elseif (defined('DB_HOST'))
  15                         $this->host     = DB_HOST;
  16                 else
  17                         trigger_error('Database connection data missing!', E_USER_ERROR);
  18
  19                 if (!empty ($dbUser))
  20                         $this->user     = $dbUser;
  21                 elseif (defined('DB_USER'))
  22                         $this->user     = DB_USER;
  23                 else
  24                         trigger_error('Database connection data missing!', E_USER_ERROR);
  25
  26                 if(!empty($dbPass))
  27                         $this->pass     = $dbPass;
  28                 elseif (defined('DB_PASS'))
  29                         $this->pass     = DB_PASS;
  30                 else
  31                         trigger_error('Database connection data missing!', E_USER_ERROR);
  32
  33                 // this is needed for postgresql
  34                 if(!empty($dbName))
  35                         $this->name     = $dbName;
  36                 elseif (defined('DB_NAME'))
  37                         $this->name     = DB_NAME;
  38                 else
  39                         trigger_error('Database connection data missing!', E_USER_ERROR);
  40
  41                 if (!empty($this->host) && !empty($this->user) && !empty($this->pass)) {
  42                         $this->connect ();
  43                 }
  44         }
  45
  46         public function __destruct() {
  47 //              var_dump($this->link);
  48 //              if (!empty ($this->link) &&  $this->link  instanceof mysqli)
  49 //                      $this->close();
  50         }
  51
  52
  53         /**
  54          * wrapper for pg_connect
  55          *
  56          * @return bool
  57          */
  58         private function connect () {
  59                 $this->link     = pg_connect('host='.$this->host.' user='.$this->user.' password='.$this->pass . (!empty ($this->name) ? 'dbname='.$this->name : '' ));
  60                 $err = pg_last_error($this->link);
  61                 if ($err) {
  62                         $this->error = $err;
  63 //                      trigger_error ($this->link->error, E_USER_ERROR);
  64                         return false;
  65                 }
  66                 return true;
  67         }
  68
  69         /**
  70          * wrapper for pg_close
  71          *
  72          * @return bool
  73          */
  74         public function close (){
  75 //              if ($this->link instanceof mysqli)
  76                         pg_close($this->link);
  77                 $this->link = null;
  78                 return true;
  79         }
  80
  81         /**
  82          * @param string $incData
  83          * @return bool
  84          */
  85         public function selectDatabase ($incData){
  86                 // in postgres we don't quite need it as pg_connect handles it
  87                 return true;
  88         }
  89
  90         /**
  91          * wrapper for pg_escape_string
  92          *
  93          * @param mixed $incData
  94          * @return mixed
  95          */
  96         public function escape ($incData){
  97                 // so far no escaping on BYTEA fields
  98                 // also there's a problem with the fact that I use tdoAbstract->escape
  99                 // to enclose values in quotes for MySQL
 100                 // TODO - this fracks up the postgres stuff
 101                 if (is_string($incData))
 102                         return pg_escape_string ($this->link, $incData);
 103                 elseif (is_numeric ($incData))
 104                         return (int)$incData;
 105         }
 106
 107         /**
 108          * wrapper for mysql_query
 109          *
 110          * @param string $query
 111          * @return mixed
 112          */
 113         public function query ($query){
 114                 if (pg_connection_status($this->link) !== PGSQL_CONNECTION_OK) {
 115                         return false;
 116                 }
 117                 if (!empty($query)) {
 118                         $qst = microtime(true);
 119                         $this->conn = pg_query($this->link, $query);
 120                         $qend = microtime(true);
 121                         echo htmlentities ($query).' ['.number_format($qend-$qst, 5, ',', '.').'s]<br/>'."\n";
 122                         if (isset($GLOBALS['qCnt']))
 123                                 $GLOBALS['qCnt']++;
 124                 } else
 125                         return false;
 126
 127                 if (!$this->conn)       {
 128                         trigger_error ($this->link->error.'<br/> ' . $query);
 129                         return false;
 130                 }
 131
 132                 if (stristr('select', $query))
 133                         // mysqli result
 134                         return $this->conn;
 135                 elseif ( preg_match('/insert|update|replace|delete/i', $query) )
 136                         return pg_affected_rows ($this->conn);
 137         }
 138
 139         /**
 140          * wrapper for mysql_fetch_row
 141          *
 142          * @return array
 143          */
 144         public function getRow (){
 145                 if ($this->conn)
 146                         return pg_fetch_row ($this->conn);
 147         }
 148
 149         // FIXME: for some reason the getAssoc and getArray work differently
 150         public function getAssoc () {
 151                 if ($this->conn)
 152                         return pg_fetch_assoc ($this->conn);
 153         }
 154
 155         /**
 156          * wrapper for mysql_fetch_row
 157          *
 158          * @return array
 159          */
 160         public function getObjects () {
 161                 // pg_??
 162                 $retArr = array ();
 163 //              $i = 0;
 164 //              if ($this->conn && $this->link) {
 165 //                      while ($i < mysqli_field_count ($this->link)) {
 166 //                              $t = $this->conn->fetch_field_direct ($i++);
 167 //                              $retArr[] = $t;
 168 //                      }
 169 //              }
 170
 171                 return $retArr;
 172         }
 173
 174         /**
 175          * wrapper for mysql_fetch_assoc
 176          *
 177          * @return array
 178          */
 179         public function getArray (){
 180                 $retArr = array();
 181                 if ($this->conn)
 182                         while ( ($r = pg_fetch_assoc ($this->conn)) ){
 183                                 $retArr[] = $r;
 184                         }
 185
 186                 return $retArr;
 187         }
 188
 189         /**
 190          * getting the first result in the resultset
 191          *
 192          * @return mixed
 193          */
 194         public function getScalar() {
 195                 $retVal = $this->getRow();
 196                 if (is_array($retVal))
 197                         $retVal = current($retVal);
 198                 return $retVal;
 199         }
 200
 201         /**
 202          *
 203          * @param array $incObj = array (array('field1','alias1),array('field2','alias2),...)
 204          * @return unknown
 205          */
 206         public function _SELECT ($incObj){
 207                 if (empty ($incObj))
 208                         return '';
 209
 210                 $retStr = 'SELECT ';
 211                 return $retStr.' "'.$incObj.'" ';
 212         }
 213
 214         public function _CREATE (){
 215                 return ' CREATE ';
 216         }
 217
 218         public function _SET(){
 219                 return ' SET ';
 220         }
 221
 222         public function _INSERT ($incData){
 223                 if (empty ($incData))
 224                         return '';
 225                 return ' INSERT INTO "' . $incData . '"';
 226         }
 227
 228         public function _VALUES ($incData) {
 229                 if (empty ($incData))
 230                         return '';
 231                 else {
 232                         if (is_array ($incData)) {
 233                                 $ret = implode("', '", $incData);
 234                         }
 235                 }
 236
 237                 return ' VALUES (' . "'" . $ret . "')";
 238         }
 239
 240         public function _UPDATE ($incOb){
 241                 if (!is_array($incOb))
 242                         $incOb[] = array ($incOb);
 243                 return ' UPDATE "'.$incOb[0].(!empty($incOb[1]) ? '" AS "'.$incOb[1] : '').'"';
 244         }
 245
 246         /**
 247          * returns the FROM tabl...es part of the query
 248          *
 249          * @param string or array of strings $incData - table names
 250          * @return string
 251          */
 252         public function _FROM ($incData){
 253                 if (empty ($incData))
 254                         return '';
 255                 if (is_array($incData))
 256                         $incData = implode('", "',$incData);
 257
 258                 return ' FROM "'.$incData.'" ';
 259         }
 260
 261         /**
 262          * @return string
 263          */
 264         public function _AND (){
 265                 return ' AND ';
 266         }
 267
 268         /**
 269          * @return string
 270          */
 271         public function _OR (){
 272                 return ' OR ';
 273         }
 274         public function _JOIN ($type) {
 275
 276         }
 277
 278         /**
 279          * @return string
 280          */
 281         public function _AS ($str){
 282                 return ' AS "' . $str . '"';
 283         }
 284
 285         public function _LIMIT ($start, $end = 0){
 286                 if (!empty($end))
 287                         return ' LIMIT '.(int)$start . ', '.(int)$end;
 288                 elseif (!empty ($start))
 289                          return ' LIMIT '.(int)$start;
 290                 else
 291                         return '';
 292         }
 293
 294         /**
 295          * TODO make it receive an array of tdoHabstractFields
 296          * (see _SELECT)
 297          *
 298          * @param array of strings $colName
 299          * @return string
 300          */
 301         public function _GROUP ($incObj = null){
 302                 if (empty ($incObj))
 303                         return '';
 304
 305                 $retStr = ' GROUP BY ';
 306                 return $retStr . ' "' . $incObj . '"';
 307         }
 308
 309         /**
 310          * method that abstracts the ORDER BY clauses
 311          *
 312          * @param       string $orderBys
 313          * @return      string
 314          */
 315         public function _ORDER ($orderBys = null){
 316
 317                 if (empty($orderBys))
 318                         return '';
 319                 $retStr = ' ORDER BY ';
 320
 321                 return $retStr.$orderBys;
 322         }
 323
 324         public function _WHERE ($clause) {
 325                 return ' WHERE '.$clause;
 326         }
 327 }