Logo Search packages:      
Sourcecode: dacode version File versions  Download package

dbpgsql.php3

<?php
/**
 * Abstraction layer for databases.
 * Responsibility: all access to DB
 *
 * daCode http://daCode.org
 * src/phplib/dbpgsql.php3
 * $Id: dbpgsql.php3,v 1.19.2.7 2003/02/08 21:19:53 ruffy Exp $
 *
 * Depends: Config
 *
 *@author Rolland Dudemaine <karllim@apologia.cx>
 *@author Fabien Seisen <seisen@linuxfr.org>
 *@package PostgreSQL
 */
class Db {

      /**
       * Db abstraction layer instance
       *@var object Db
       */
      var $db;

      /**
       * a result index if the query has been successfully executed, false on failure.
       *@var integer 
       */
      var $results;

      /**
       * the current row is an integer between 0 and max number row
       *@var integer 
       */
      var $currentrow;

      /**
       * Class constructor
       */
      Function Db() {
            global $config;

            //echo '<br />Db'; flush();
            $this->currentrow = 0;
            if ($config->sql_persistant == 1) { 
                  $db = $this->pconnect(
                        $config->sql_host, $config->sql_port, $config->sql_user,
                        $config->sql_passwd, $config->sql_db);
            } else {
                  $db = $this->connect(
                        $config->sql_host, $config->sql_port, $config->sql_user,
                        $config->sql_passwd, $config->sql_db);
            }

            if (! $db) {
                  // echo "Db->Db() failed<br />\n";
                  $config->nodb=1;
                  return 0;
            }
            return $db;
      }
      /**
       * Duplicates a DB maintaining consistency.
       */
      Function clone() {
            $db2 = new Db("", "", "", "", "");
            $db2->db = $this->db;
            return $db2;
      }

      /**
       * Connects to the database
       *@param string host the DB host
       *@param string port the DB port
       *@param string login user login
       *@param string passwd user password
       *@param string database name to connect to.
       *@return integer the dabase link identifier or NULL on failure(?)
       */
      Function connect($host, $port='5432', $login, $passwd, $database) {
            $this->db  = pg_connect('host='.$host.' dbname='.$database.
                  ' user='.$login.' password='.$passwd.' port='.$port);
            return $this->db;
      }

      /**
       * Opens a persistent connection to the database
       *@param string host the DB host
       *@param string port the DB port
       *@param string login user login
       *@param string passwd user password
       *@param string database name to connect to.
       *@return integer the dabase link identifier or NULL on failure(?)
       */
      Function pconnect($host, $port='5432', $login, $passwd, $database) {
            $this->db  = pg_pconnect('host='.$host.' dbname='.$database.
                  ' user='.$login.' password='.$passwd.' port='.$port);
            return $this->db;
      }

      /**
       * Closes the connection to the database
       *@return integer return code of pg_close().
       */
      Function close() {
            $this->ret = pg_close($this->db);
            return $this->ret;
      }

      /**
       * Really useful???
       *@param string database the name of the database
       */
      Function select_db($database) {
            return $this->db;
      }

      /**
       * Sends an SQL query, with limitations.
       * WARNING: don't send unduly long queries with this method! That is, query 
       * length shall be less than 8000 characters long, because of a PgSQL 
       * limitation. This limit may be lowered when we add support for other databases.
       *@param string query the query to b exectuted
       *@return integer a result index if the query has been successfully executed, false on failure.
       */
      Function query($query) {
            global $config;

            $this->currentrow = 0;
            if (!empty($config->sql_logfile)) {
                  if(!($fp = fopen($config->sql_logfile,"ab"))) {
                        echo "problem...";
                  }
                  //   We do not check this writing because
                  //   of the one just below which will catch any
                  //   error
                  fputs($fp,$query."\n");
            }
            $this->lasttable = '';
            if (eregi(' *insert +into +([^ ,]+) ', $query, $args)) {
                  $this->lasttable = $args[1];
            }
            $this->results = @pg_exec($this->db,$query);
            if (!empty($config->sql_logfile)) {
                  $text = "Affected : ".$this->affected_rows()."\n";
                  $bytes_written = fputs($fp, $text);
                  fclose($fp);
                  if ($bytes_written != strlen($text)) {
                        @unlink($config->sql_logfile);
                        $config->nosave = 1;
                  }
            }
            return $this->results;
      }

      /**
       * Gets the value of fieldname in row nb.
       * Row numbering starts at 0.
       *@param integer nb the number of the row
       *@param string fieldname the field to return
       *@return mixed the value.
       */
      Function result($nb, $fieldname) {
            return @pg_result($this->results, $nb, $fieldname);
      }

      /**
       * Frees the result buffer.
       */
      Function free() {
            return pg_freeresult($this->results);
      }

      /**
       * Gets the current row in data set.
       * Automatically converts the "timestamp" field from pgsql timestamp 
       * to a MySQL one.
       *@return array the fetched row; false if no more rows.
       */
      Function fetch_array() {
            if ($this->currentrow < pg_numrows($this->results)) {
                  $ret = @pg_fetch_array($this->results, $this->currentrow );
                  if (is_array($ret)) {
                        /*
                         * Conversion from postgres timestamp into timestamp14
                         * pg timestamp: "2001-08-06 11:06:49+02"
                         * timestamp14: 2001080611064902
                         */
                        if (isset($ret["timestamp"])) {
                              $ret["timestamp"] = substr(
                                    ereg_replace("[- :]","", $ret["timestamp"]), 0, 14);
                        }
                  }
                  $this->currentrow += 1;
                  return $ret;
            } else {
                  $this->currentrow = 0;
                  return false;
            }
      }

      /**
       * Hmm, unused one?
       * Call to pg_fetch_array DOES NOT MATCH prototype
       *@deprecated unsused in dacode? May not work.
       */
      Function fetch_field() {
            return pg_fetch_array($this->results);
      }

      /**
       * Gets the number of rows in the result set
       *@return integer 
       */
      Function num_rows() {
            return pg_numrows($this->results);
      }

      /**
       * Gets the nuimber of fields in the result set.
       *@return integer (What else would you expect?)
       */
      Function num_fields() {
            return pg_numfields($this->results);
      }

      /**
       * Gets the last DBMS error message
       *@return string the clear (hopefully!) english error message
       */
      Function error() {
            return pg_errormessage($this->db);
      }

      /**
       * Gets last inserted ID
       * REALLY USEFUL?
       *@return integer 
       */
      Function last_insert_id() {
            if ($this->lasttable) {
                  $result = pg_exec($this->db,"SELECT max(id) FROM $this->lasttable");
                  $row = pg_fetch_array($result, 0);
                  return $row[0];
            } else {
                  return FALSE;
            }
      }

      /**
       * Returns the number of rows affected by last query.
       *@return int
       */
      Function affected_rows() {
            return pg_cmdtuples($this->results);
      }

      //   The compat_* functions are to handle with incompatibilities
      //   between SQL backends

      /**
       * Creates a Pgsql-compatible (specific?) LIMIT clause
       *@param integer nb the number of rows the result set should be limited to
       *@param integer offset the number of rows to be skipped before the result set.
       *@return string a valid pgsql LIMIT clause 
       */
      Function compat_limit($nb,$offset=0) {
            return 'LIMIT '.$nb.' OFFSET '.$offset;
      }

      /**
       * Creates a SQL expression for now + sec
       * Creates the SQL expression which will give a timestamp for 
       * current time + sec seconds.
       *@param integer sec the number of seconds from now.
       *@return string the SQL order.
       */
      Function compat_date_add($sec) {
            return "TIMESTAMP 'now'+'" . $sec ."s'";
      }
      /**
       * Converts a timestamp14 (YYYYMMDDhhmmss) into DBMS stamp
       * (YYYY-MM-DD hh:mm:ss for PostgreSQL).
       * @param string t timestamp14
       * @return string a PostgreSQL timestamp
       */
      Function timestamp14_to_dbms_stamp( $t) {
            // could not use utils->stamp2datetime
            return ereg_replace('([0-9]{4})([0-9]{2})([0-9]{2})([0-9]{2})([0-9]{2})([0-9]{2})', '\1-\2-\3 \4:\5:\6', $t);
      }
      /**
       * Converts a date8 (YYYYMMDD) into DBMS date (YYYY-MM-DD for PostgreSQL).
       * @param string t date8
       * @return string a PostgreSQL date
       */
      Function date8_to_dbms_date( $t) {
            return ereg_replace('([0-9]{4})([0-9]{2})([0-9]{2})', '\1-\2-\3', $t);
      }

      //Large objects interface implementaion, as this is NOT specified in any SQL standard (AFAIK)

      // IMPLEMENTATION NOTES
      // --------------------
      // LOBs are stored as OIDs in text fields for historical reasons.
      // We use programming means to convert the stored OID string back to a PG OID (implicit type 
      // cast)
      // THis has the additionnal benefit of allowing to store a filename here.

      // Important warning: all LOB manipulation must take place in one transaction 
      // (AFAIK; I may be wrong), and Postgres really doesn't like transactions hanging around.
      // Thus it is really important to take care to close the transaction whenever the functions must 
      // return, either with commit or rollback.
  

      /**
       * Inserts a LOB into the database. 
       * Takes care of any encoding needed for LOB. Other values must be valid SQL ie strings inside ' '.
       *@param string table the name of the table
       *@param array values hash (column, value) pair; valid SQL except LOB fields which are raw data.
       *@param array lobFields list of fields to be treated as LOB
       *@return integer true on success, false otherwise
       */
      Function insertLob($table, $values, $lobFields) {
            //Here we use internal PgSQL storage, no encoding needed.
            //But everything must be done INSIDE a transaction.
            //So we begin one...
            //There no protection on transactions ends...
            //If one fails, we're in deep shit whatever we do.
            $ret = $this->query("BEGIN");
            if (!$ret) echo $this->error();

            //We create the LOBs needed and write their values
            reset ($lobFields);
            while(list(, $f) = each($lobFields)) {
                  $oid = pg_locreate($this->db);

                  $fd = pg_loopen( $this->db, $oid, 'w');
                  if (!$fd)  {
                        //Error: display error message, end transaction and exit
                        echo "Database error." .
                              "<!-- Error in dbpgsql.php3, in fuction insertLOB: ". 
                              "impossible to open LOB id $oid; " .
                              $this->error() . "-->\n";
                        pg_loclose($fd);
                        $this->query("ROLLBACK");
                        return false;
                  }
                  $r = pg_lowrite($fd, $values[$f]);
                  if (!$r) {
                        echo "Database error." . 
                              "<!-- Error in dbpgsql.php3, in function insertLOB: ".
                              "impossible to write into LOB" . $this->error() . "-->";
                        pg_loclose($fd);
                        $this->query("ROLLBACK");
                        return false;
                  } 
                  $values[$f] = "'" . $oid . "'";
                  pg_loclose($fd);
            }
            
            //Now this has been saved. Create the INSERT command.
            $query= "INSERT INTO $table (";
            $flag = false;
            $columns = "";
            $vals = "";
            reset($values);
            while (list($cname, $val) = each($values)) {
                  if (!$flag) {
                        $flag = true;
                  } else {
                        $columns .= ", ";
                        $vals .= ", ";
                  }
                  $columns .= $cname;
                  $vals .= $val;
            }
            $query .= $columns . ") VALUES (" . $vals . ")";

            // OK, run the query
            $ret_val =  $this->query($query);
            if (!$ret_val) {
                  // Error message. It is reasonable to print query, since LOBs 
                  // have been replaced by OIDs
                  echo "<!-- SQL Error: " . $this->error() .
                        "; Query was:\n\t$query\n-->";
                  // Rollback...
                  $this->query("ROLLBACK");
                  return false;
            }
            //And commit...
            $this->query('COMMIT');
            return $ret_val;
      }
      
      /**
       * Gets a LOB identified by its primary key
       *@param string table name of the table...
       *@param string pkField the name of the primary key
       *@param integer pk the primary key itself
       *@param string lobField the name of the LOB field.
       *@return string the LOB
       */
      Function getLobByID($table, $pkField, $pk, $lobField) {
            //Still one unique transaction...
            //Is the transaction really ueful there?
            // -- is there a pgsql gugru reading this? 
            $this->query('BEGIN');
            
            $r = $this->query("SELECT $lobField FROM $table WHERE $pkField = $pk");
            if (!$r) {
                  echo "<!-- SQL failed: " .
                        $this->error() . " Query was:\n".
                        "SELECT $lobField FROM $table WHERE $pkField = $pk";
                  $this->query("ROLLBACK");
                  return false;
            }
            $row = $this->fetch_array();
            $oid = $row[0];
            
            $fd = pg_loopen($this->db, $oid, 'r');
            $loval = "";
            $chunk ="";
            while ($chunk = pg_read($fd, 4096)) {
                  $loval .= $chunk;
            }
            $this->query("COMMIT");
            return $loval;
      }

      /**
       * Gets a row from current dataset containing LOBs
       * Careful: you may run into inconsitencies if you use numeric indexes.
       * Always use name indexes.
       *@param array lobFields list of fields containing LOBs
       *@return array the fethed row, with LOBs...
       */
      Function fetchLOB($lobFields) {


            //Get result set
            $row = $this->fetch_array();
            
            //Premature ending, if no row (end of result set)
            if (!$row) return $row;
            //BEGIN ? (??!?)

            $this->query('BEGIN');

            //And replace OIDs by the real LOB values
            reset($lobFields);
            while(list(,$f) = each($lobFields)) {
                  $oid = $row[$f];
                  $fd = pg_loopen($this->db, $oid, "r");
                  //No need to go on: error!
                  if (!$fd) {
                        $utils = Loadclass('Utils');
                        echo "<!-- LOB manipulation failed. PGSQL said: " .
                              $this->error . "\nUse $config->debug='stdout' for more deatils.\n".
                              $utils->debug_dump($row) . " -->";
                        $this->query("ROLLBACK");
                        return false;
                  }
                  $row[$f] = "";
                  $chunk ="";
                  while ($chunk = pg_loread($fd, 4096)) {
                        $row[$f] .= $chunk;
                  }
               
            }
            $this->query('COMMIT');
            return $row;
      }

      /**
       * Deletes a row  containing a LOB
       *@param string table name of the table...
       *@param string pkField the name of the primary key
       *@param integer pk the primary key itself
       *@param string lobField the name of the LOB field.
       *@return integer true on success
       */
      Function deleteLOB($table, $pkField, $pk, $lobField) {
            //Start a transaction.
            $this->query('BEGIN');
            
            //Get OID..
            $r = $this->query("SELECT $lobField FROM $table WHERE $pkField = $pk");
            if (!$r) { //I WANT EXCEPTIONS!
                  echo "Database error." . "<!-- SQL failed in pgsql.php in function deleteLOB:\n" .
                        $this->error() . "\nquery:  SELECT $lobField FROM $table WHERE $pkField = $pk -->";
                  $this->query("ROLLBACK");
                  return false;
            }
            $row = $this->fetch_array();
            $oid = $row[0];


            //Deletes LOB
            pg_lounlink($this->db, $oid);
      
            $r = $this->query("DELETE FROM $table WHERE $pkField = $pk");
            if (!$r) {//once again bloody error code...
                  echo "Database error." . "<!-- SQL failed in pgsql.php in function deleteLOB:\n" .
                        $this->error() . "DELETE FROM $table WHERE $pkField = $pk -->";
                  $this->query("ROLLBACK");
                  return false;
            }
            $this->query("COMMIT");
            return true;
      }
}

?>

Generated by  Doxygen 1.6.0   Back to index