database_core.class.php

Go to the documentation of this file.

<?php
 
/*
 *   This file is part of NOALYSS.
 *
 *   NOALYSS is free software; you can redistribute it and/or modify
 *   it under the terms of the GNU General Public License as published by
 *   the Free Software Foundation; either version 2 of the License, or
 *   (at your option) any later version.
 *
 *   NOALYSS is distributed in the hope that it will be useful,
 *   but WITHOUT ANY WARRANTY; without even the implied warranty of
 *   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *   GNU General Public License for more details.
 *
 *   You should have received a copy of the GNU General Public License
 *   along with NOALYSS; if not, write to the Free Software
 *   Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
 */
 
// Copyright Author Dany De Bontridder danydb@aevalys.eu
 
/**
 * \file
 * \brief contains the class for connecting to a postgresql database
 */
require_once NOALYSS_INCLUDE . '/constant.php';
require_once NOALYSS_INCLUDE . '/lib/ac_common.php';
 
/**
 * \brief
 * This class allow you to connect to the postgresql database, execute sql, retrieve data
 *
 */
class DatabaseCore
{
 
    protected $db;
    /**< database connection */
    protected $ret;
    /**< return value  */
    protected $is_open;                   /*!< true is connected */
    public $sql;     //!< last SQL stmt executed
    public $array;
    protected $dbname; ///!< $dbname (string) Database name
    protected $dbport;///!< $dbport (int) Database port
    protected $dbhost;///!< $dbhost(string) Database host
    protected $dbuser;///!< $dbuser(string) Database user
    /*** 
     * @brief Connect to a database return an connx to db or false if it fails
     *
     * @param string $p_user Username
     * @param type $p_password User's password
     * @param $p_dbname name of the database to connect
     * @param type $p_host Host of DB
     * @param type $p_port Port of DB
     */
 
    function __construct($p_user, $p_password, $p_dbname, $p_host, $p_port)
    {
        $this->db = @pg_connect("dbname=$p_dbname host='$p_host' user='$p_user'
                     password='$p_password' port=$p_port");
        if ($this->db == false) {
            if ( DEBUGNOALYSS > 0 ) {
 
                echo '<h2 class="error">'._('Impossible de se connecter à postgreSql').'</h2>';
                echo '<p>';
                echo _("Vos paramètres sont incorrectes").": <br>";
                echo "<br>";
                printf (_("base de donnée  = %s"), $p_dbname)."<br>";
                printf (_("Port %s"),$p_port )."<br>";
                printf (  _("Utilisateur : %s"),$p_user )."<br>";
                echo '</p>';
 
                die();
            } else {
                echo '<h2 class="error">' . _('Erreur de connexion !') . '</h2>';
                $this->is_open = false;
                throw new Exception(_('Erreur Connexion'));
            }
        }
        $this->dbport=$p_port;
        $this->dbname=$p_dbname;
        $this->dbhost=$p_host;
        $this->dbuser=$p_user;
        $this->is_open = TRUE;
        $this->sql="";
 
    }
    public function get_dbname() {
        return $this->dbname;
    }
 
    public function get_dbport() {
        return $this->dbport;
    }
 
    public function get_dbhost() {
        return $this->dbhost;
    }
 
    public function get_dbuser() {
        return $this->dbuser;
    }
 
        /**
     * return the name of the current database
     * @return false|string
     */
    public function get_name () {
        return pg_dbname($this->db);
    }
    
    public function verify()
    {
        // Verify that the elt we want to add is correct
    }
 
    function set_encoding($p_charset)
    {
        pg_set_client_encoding($this->db, $p_charset);
    }
 
    function get_encoding()
    {
        return pg_client_encoding($this->db);
    }
 
 
    /**
     * @return mixed
     */
    public function get_db()
    {
        return $this->db;
    }
 
    /**
     * @param mixed $db
     */
    public function set_db($db)
    {
        $this->db = $db;
    }
 
    /**
     * @return mixed
     */
    public function get_ret()
    {
        return $this->ret;
    }
 
    /**
     * @param mixed $ret
     */
    public function set_ret($ret)
    {
        $this->ret = $ret;
    }
 
    /**
     * @return mixed
     */
    public function get_is_open()
    {
        return $this->is_open;
    }
 
    /**
     * @param mixed $is_open
     */
    public function set_is_open($is_open)
    {
        $this->is_open = $is_open;
    }
     /**
     * last SQL stmt executed
     * @param string $sql
     */
    public function get_sql()
    {
        return $this->sql;
    }
    /**
     * last SQL stmt executed
     * @param string $sql
     */
    public function set_sql($sql)
    {
        $this->sql=$sql;
        return $this;
    }
 
    /**
     * \brief send a sql string to the database
     * \param $p_string     sql string
     * \param $p_array array for the SQL string (see pg_query_params)
     * \return the result of the query, a resource or false if an
     * error occured
     */
 
    function exec_sql($p_string, $p_array = null)
    {
        try {
            if (!$this->is_open) throw new Exception(' Database is closed');
            $this->sql = $p_string;
            $this->array = $p_array;
 
            if ($p_array == null) {
                if ( DEBUGNOALYSS == 0 )
                    $this->ret = pg_query($this->db, $p_string);
                else
                    $this->ret = @pg_query($this->db, $p_string);
            } else {
                $a = is_array($p_array);
                if (!is_array($p_array)) {
                    throw new Exception(_("Erreur : exec_sql attend un array"));
                }
                if ( DEBUGNOALYSS == 0 )
                    $this->ret =@pg_query_params($this->db, $p_string, $p_array);
                else
                    $this->ret = pg_query_params($this->db, $p_string, $p_array);
            }
            if ($this->ret == false) {
                $str_error = pg_last_error($this->db) ;
                throw new Exception("  SQL ERROR $p_string " . $str_error, 1);
            }
        } catch (Exception $a) {
            if ( DEBUGNOALYSS > 0 ) {
                print_r($p_string);
                print_r($p_array);
                echo $a->getMessage();
                echo $a->getTraceAsString();
                echo pg_last_error($this->db);
            }
            record_log($a);
            record_log($p_string);
            record_log($p_array);
            $this->rollback();
 
            throw new \Exception("exec_sql fails",242,$a);
        }
 
        return $this->ret;
    }
 
    /**
     * \brief Count the number of row returned by a sql statement
     *
     * \param $p_sql sql string
     * \param $p_array if not null we use the safer pg_query_params
     */
 
    function count_sql($p_sql, $p_array = null)
    {
        $r_sql = $this->exec_sql($p_sql, $p_array);
        return pg_num_rows($r_sql);
    }
 
    /**
     * \brief get the current sequence value
     */
 
    function get_current_seq($p_seq)
    {
        $Res = $this->get_value("select currval('$p_seq') as seq");
        return $Res;
    }
 
    /**
     * \brief  get the next sequence value
     */
 
    function get_next_seq($p_seq)
    {
        $Res = $this->exec_sql("select nextval('$p_seq') as seq");
        $seq = pg_fetch_array($Res, 0);
        return $seq['seq'];
    }
 
    /**
     * @brief : start a transaction
     *
     */
    function start()
    {
        $Res = $this->exec_sql("start transaction");
    }
 
    /**
     * Commit the transaction
     *
     */
    function commit()
    {
        if (!$this->is_open) return;
        $Res = $this->exec_sql("commit");
    }
 
    /**
     * rollback the current transaction
     */
    function rollback()
    {
        if (!$this->is_open) return;
        $Res = $this->exec_sql("rollback");
    }
 
    /**
     * @brief alter the sequence value
     * @param $p_name name of the sequence
     * @param $min the start value of the sequence
     */
    function alter_seq($p_name, $min)
    {
        if ($min < 1)
            $min = 1;
        $Res = $this->exec_sql("alter sequence $p_name restart $min");
    }
 
    /**
     * \brief Execute a sql script
     * \param $script script name
     */
 
    function execute_script($script)
    {
 
        if ( DEBUGNOALYSS == 0 ) {
            ob_start();
        } else {
            $debug = fopen("/tmp/debug_execute_script".uniqid().".log", "w+");
        }
        $hf = fopen($script, 'r');
        if ($hf == false) {
            throw new Exception ('Ne peut ouvrir ' . $script);
        }
        printf (" open %s <br>", $script);
        $sql = "";
        $flag_function = false;
        while (!feof($hf)) {
            $buffer = fgets($hf);
            $buffer = str_replace('$BODY$', '$_$', $buffer);
            print $buffer . "<br>";
            // comment are not execute
            if (substr($buffer, 0, 2) == "--") {
                //echo "comment $buffer";
                continue;
            }
            // Blank Lines Are Skipped
            If (Strlen($buffer) == 0) {
                //echo "Blank $buffer";
                Continue;
            }
            if (strpos(strtolower($buffer), "create function") === 0) {
                echo "found a function";
                $flag_function = true;
                $sql = $buffer;
                continue;
            }
            if (strpos(strtolower($buffer), "create or replace function") === 0) {
                echo "found a function";
                $flag_function = true;
                $sql = $buffer;
                continue;
            }
            // No semi colon -> multiline command
            if ($flag_function == false && strpos($buffer, ';') == false) {
                $sql .= $buffer;
                continue;
            }
            if ($flag_function) {
                if (strpos(strtolower($buffer), "$$;") === false &&
                    strpos(strtolower($buffer), '$_$;') === false &&
                    strpos(strtolower($buffer), '$function$;') === false &&
                    strpos(strtolower($buffer), 'language plpgsql;') === false &&
                    strpos(strtolower($buffer), 'language plpgsql ;') === false
                ) {
                    $sql .= $buffer;
                    continue;
                }
            } else {
                // cut the semi colon
                $buffer = str_replace(';', '', $buffer);
            }
            $sql .= $buffer;
            if ( DEBUGNOALYSS > 0 ) fwrite($debug, $sql);
            if ($this->exec_sql($sql) == false) {
 
                $this->rollback();
                if ( DEBUGNOALYSS == 0 )
                    ob_end_clean();
                print "ERROR : $sql";
                throw new Exception("ERROR : $sql");
            }
            $sql = "";
            $flag_function = false;
            print "<hr>";
        } // while (feof)
        fclose($hf);
        if ( DEBUGNOALYSS == 0 )
            ob_end_clean();
    }
 
 
    /**
     * @brief fetch the $p_indice array from the last query
     * @param $p_mode is PGSQL_ASSOC,  PGSQL_BOTH or PGSQL_NUM  (default PGSQL_ASSOC)
     * @param $p_indice index
     *
     */
    function fetch($p_indice,$p_mode= PGSQL_ASSOC)
    {
        if ($this->ret == false)
            throw new Exception('this->ret is empty');
        return pg_fetch_array($this->ret, $p_indice,$p_mode);
    }
 
    /**
     *
     * @brief return the number of rows found by the last query, or the number
     * of rows from $p_ret
     * @param $p_ret is the result of a query, the default value is null, in that case
     * it is related to the last query
     * @note synomym for count()
     */
 
    function size($p_ret = null)
    {
        if ($p_ret == null)
            return pg_num_rows($this->ret);
        else
            return pg_num_rows($p_ret);
    }
 
    /**
     * @brief       synomym for size()
     */
 
    function count($p_ret = null)
    {
        return $this->size($p_ret);
    }
 
 
    /**
     *
     * \brief return the value of the sql, the sql will return only one value
     *        with the value
     * \param $p_sql the sql stmt example :select s_value from
     * document_state where s_id=2
     * \param $p_array if array is not null we use the ExecSqlParm (safer)
     * \see exec_sql
     * \note print a warning if several value are found, if only the first value is needed
     * consider using a LIMIT clause
     * \return only the first value or an empty string if nothing is found
     */
 
    function get_value($p_sql, $p_array = null)
    {
        try {
            $this->ret = $this->exec_sql($p_sql, $p_array);
            $r = pg_num_rows($this->ret);
            if ($r == 0)
                return "";
            if ($r > 1) {
                $array = pg_fetch_all($this->ret);
                throw new Exception("Attention $p_sql retourne " . pg_num_rows($this->ret) . "  valeurs " .
                    var_export($p_array, true) . " values=" . var_export($array, true));
            }
            $r = pg_fetch_row($this->ret, 0);
            return $r[0];
 
        } catch (Exception $ex) {
            throw($ex);
        }
    }
 
    /**
     * @brief return the number of rows affected by the previous query
     */
    function get_affected()
    {
        return Database::num_row($this->ret);
    }
 
    /**
     * \brief  purpose return the result of a sql statment
     * in a array
     * \param $p_sql sql query
     * \param $p_array if not null we use ExecSqlParam
     * \param $p_mode is PGSQL_ASSOC,  PGSQL_BOTH or PGSQL_NUM  (default PGSQL_ASSOC) 
     * \return false if nothing is found
     */
 
    function get_array($p_sql, $p_array = null,$p_mode=PGSQL_ASSOC)
    {
        $r = $this->exec_sql($p_sql, $p_array,$p_mode);
 
        if (pg_num_rows($r) == 0)
            return array();
        $array = pg_fetch_all($r,$p_mode);
        return $array;
    }
 
    /**
     * @brief  Returns only one row from a query
     * @param string $p_sql
     * @param array $p_array
     * @return array , idx = column of the table or null if nothing is found
     * @throws Exception if too many rows are found code 100
     */
    function get_row($p_sql, $p_array = NULL)
    {
        $array = $this->get_array($p_sql, $p_array);
        if (empty($array)) return null;
        if (count($array) == 1) return $array[0];
        throw new Exception(_("Database:get_row retourne trop de lignes"), 100);
    }
 
    /**
     * @brief Create a sequence
     * @param string $p_name Sequence Name
     * @param int $min starting value
     */
    function create_sequence($p_name, $min = 1)
    {
        if ($min < 1)
            $min = 1;
        $sql = "create sequence " . $p_name . " minvalue $min";
        $this->exec_sql($sql);
    }
 
    /**
     * \brief test if a sequence exist */
    /* \return true if the seq. exist otherwise false
     */
 
    function exist_sequence($p_name)
    {
        $r = $this->count_sql("select relname from pg_class where relname=lower($1)", array($p_name));
        if ($r == 0)
            return false;
        return true;
    }
 
    /**
     * \brief test if a table exist
     * \param $p_name table name
     * \param  $schema name of the schema default public
     * \return true if a table exist otherwise false
     */
 
    function exist_table($p_name, $p_schema = 'public')
    {
        $r = $this->count_sql("select table_name from information_schema.tables where table_schema=$1 and table_name=lower($2)", array($p_schema, $p_name));
        if ($r == 0)
            return false;
        return true;
    }
 
    /**
     * Check if a column exists in a table
     * @param $col : column name
     * @param $table :table name
     * @param $schema :schema name, default public
     * @return true or false
     */
    function exist_column($col, $table, $schema)
    {
        $r = $this->get_value('select count(*) from information_schema.columns where table_name=lower($1) and column_name=lower($2) and table_schema=lower($3)', array($col, $table, $schema));
        if ($r > 0)
            return true;
        return false;
    }
 
 
    /**
     * Count the database name in a system view
     * @param $p_name string database name
     * @return number of database found (normally 0 or 1)
     */
    function exist_database($p_name)
    {
        $database_exist = $this->get_value('select count(*)
                from pg_catalog.pg_database where datname = lower($1)', array($p_name));
        return $database_exist;
    }
 
    /**
     * @brief check if the large object exists
     * @param $p_oid of the large object
     * @return return true if the large obj exist or false if not
     */
    function exist_blob($p_oid)
    {
        $r = $this->get_value('select count(*) from pg_largeobject_metadata where oid=$1'
            , array($p_oid));
        if ($r > 0)
            return true;
        else
            return false;
    }
 
    /*
     *!\brief test if a view exist
     * \return true if the view. exist otherwise false
     */
 
    function exist_view($p_name)
    {
        $r = $this->count_sql("select viewname from pg_views where viewname=lower($1)", array($p_name));
        if ($r == 0)
            return false;
        return true;
    }
 
    /***
     * @brief test if a schema exists
     * @return true if the schemas exists otherwise false
     */
 
    function exist_schema($p_name)
    {
        $r = $this->count_sql("select nspname from pg_namespace where nspname=lower($1)", array($p_name));
        if ($r == 0)
            return false;
        return true;
    }
 
    /**
     * \brief create a string containing the value separated by comma
     * for use in a SQL in statement
     * \return the string or empty if nothing is found
     * \see fid_card.php
     */
 
    function make_list($sql, $p_array = null)
    {
        if ($p_array == null) {
            $aArray = $this->get_array($sql);
        } else {
            $aArray = $this->get_array($sql, $p_array);
        }
        if (empty($aArray))
            return "";
        $aIdx = array_keys($aArray[0]);
        $idx = $aIdx[0];
        $ret = "";
        $f = "";
        for ($i = 0; $i < count($aArray); $i++) {
            $row = $aArray[$i];
            $ret .= $f . $aArray[$i][$idx];
            $f = ',';
        }
        $ret = trim($ret, ',');
        return $ret;
    }
 
    /**
     * \brief make a array with the sql.
     *
     * \param $p_sql  sql statement, only the first two column will be returned in
     *  an array. The first col. is the label and the second the value
     *  \param $p_null if the array start with a null value Yes = 1 , No=0
     *  \param $p_array is the array with the bind value
     * \note this function is used with ISelect when it is needed to have a list of
     * options.
     * \return: a double array like
     * \verbatim
     * Array
     * (
     * [0] => Array
     * (
     * [value] => 1
     * [label] => Marchandise A
     * )
     *
     * [1] => Array
     * (
     * [value] => 2
     * [label] => Marchandise B
     * )
     *
     * [2] => Array
     * (
     * [value] => 3
     * [label] => Marchandise C
     * )
     * )
     * \endverbatim
     * \see ISelect
     */
 
    function make_array($p_sql, $p_null = 0, $p_array = null)
    {
        $a = $this->exec_sql($p_sql, $p_array);
        $max = pg_num_rows($a);
        if ($max == 0 && $p_null == 0)
            return null;
        for ($i = 0; $i < $max; $i++) {
            $row = pg_fetch_row($a);
            $r[$i]['value'] = $row[0];
            $r[$i]['label'] = h($row[1]);
        }
        // add a blank item ?
        if ($p_null == 1) {
            for ($i = $max; $i != 0; $i--) {
                $r[$i]['value'] = $r[$i - 1]['value'];
                $r[$i]['label'] = $r[$i - 1]['label'];
            }
            $r[0]['value'] = -1;
            $r[0]['label'] = " ";
        } //   if ( $p_null == 1 )
 
        return $r;
    }
 
    /***
     * \brief Save one or several documents into the database , it just puts the file in the database
     * and returns the corresponding OID , the mimetype , size ... of the document
     * must be set in the calling function.
     *
     * \param name of the variable in $_FILES
     * \param $only_oid (bool) (default :true) false : return filename and oid in an array , true only OID, 
     * \return $oid of the lob file if success
     *         false if a error occurs or if there is no file to upload
     *         array(oid, filename) if $only_oid is true
     *
     */
 
    function upload($p_name,$only_oid = false)
    {
 
          //var $a : 0 we're in a transaction, 1 we are not in a transaction
        $a=0;
        if ( $this->status() !== PGSQL_TRANSACTION_INTRANS ) {
            $a=1;
            $this->start();
        }
            
        /* there is          no file to          upload */
        if ($_FILES[$p_name]["error"] == UPLOAD_ERR_NO_FILE) {
            \record_log("DC759: error upload file".var_export($_FILES, true));
            if ( $a==1) { $this->rollback(); }
            return false;
        }
 
        $new_name = tempnam($_ENV['TMP'], $p_name);
        if ($_FILES[$p_name]["error"] > 0) {
            \record_log("DC740: error upload file".var_export($_FILES, true));
            if ( $a==1) { $this->rollback(); }
            return false;
        }
        if (strlen($_FILES[$p_name]['tmp_name']) != 0) {
            if (move_uploaded_file($_FILES[$p_name]['tmp_name'], $new_name)) {
                // echo "Image saved";
                $oid = pg_lo_import($this->db, $new_name);
                if ($oid == false) {
                    \record_log("DC747: error upload file".var_export($_FILES, true). "SQL MESSAGE". pg_last_error($this->db));
                    $this->rollback();
                    return false;
                }
                if ( $a == 1 ) { $this->commit(); }
                if ($only_oid ){
                    return $oid;
                }else {
                    return ["oid"=>$oid,'filename'=>$new_name];
                }
            } else {
                \record_log("DC754: move_uploaded fails".var_export($_FILES, true));
                $this->rollback();
                return false;
            }
        }
 
        \record_log("DC576: Files error names empty".var_export($_FILES, true));
        if ( $a == 1) { $this->commit(); }
        return false;
    }
    /**
     * @brief large_object writee: create a Large object if oid is not given 
     * with data content in a binaray
     * @param $binary_data (raw data) binary
     * @returns $oid of the LO, false if it fails
     */
    function lo_write($binary_data)
    {
        //var $a : 0 where in a transaction, 1 we are not in a transaction
        $a=0;
        if ( $this->status() !== PGSQL_TRANSACTION_INTRANS ) {
            $a=1;
            $this->start();
        }
 
        $oid= pg_lo_create($this->db);
        
        if ( ($handle=pg_lo_open($this->db,$oid,"w")) == false  ) { return false ;}
        pg_lo_write($handle, $binary_data);
        pg_lo_close($handle);
        if ( $a==1) { $this->commit(); }
        return $oid;
        
    }
     /**
     * @brief read a Large object with data content in a binary
     * @param $oid (int8) oid of the large object
     * @returns $binary_data (raw data) binary
     */
    function lo_read($oid)
    {
        //var $a : 0 where in a transaction, 1 we are not in a transaction
        $a=0;
        if ( $this->status() !== PGSQL_TRANSACTION_INTRANS ) {
            $a=1;
            $this->start();
        }
 
        $handle=pg_lo_open($this->db,$oid,"r");
        if ( $handle == false ) { return false ;}
        // set position end of the LO
        pg_lo_seek($handle, 0, PGSQL_SEEK_END);
        // get the size 
        $size= pg_lo_tell($handle);
        // set position to start
        pg_lo_seek($handle, 0, PGSQL_SEEK_SET);
        // read the comùplete LOB
        $binary_data = pg_lo_read($handle,$size );
        pg_lo_close($handle);
        if ( $a==1) { $this->commit(); }
        return $binary_data;
    }
     /**
     * @brief replace  a Large object with data content in a binary
     * @param $oid (int8) oid of the large object
     * @param $binary_data (raw data) binary
     * @returns $oid of the LO, false if it fails
     */
    function lo_replace($binary_data, $oid) {
        $a = 0;
        if ($this->status() !== PGSQL_TRANSACTION_INTRANS) {
            $a = 1;
            $this->start();
        }
        $handle = pg_lo_open($this->db, $oid, "w");
        if ( $handle == false ) { return false ;}
        pg_lo_truncate($handle, 0);
        pg_lo_write($handle, $binary_data);
        pg_lo_close($handle);
        if ($a == 1) {
            $this->commit();
        }
        return $oid;
    }
 
    /**
     * \brief wrapper for the function pg_num_rows
     * \param $ret is the result of a exec_sql
     * \return number of line affected
     */
 
    static function num_row($ret)
    {
        return pg_num_rows($ret);
    }
 
    /**
     * \brief wrapper for the function pg_fetch_array
     * \param $ret is the result of a pg_exec
     * \param $p_indice is the index
     * \param $p_mode is PGSQL_ASSOC,  PGSQL_BOTH or PGSQL_NUM  (default PGSQL_ASSOC)
     * \return $array of column
     */
 
    static function fetch_array($ret, $p_indice = 0,$p_mode=PGSQL_ASSOC)
    {
        return pg_fetch_array($ret, $p_indice,$p_mode);
    }
 
    /**
     * \brief wrapper for the function pg_fetch_all
     * \param $ret is the result of pg_exec (exec_sql)
     * \param $p_mode is PGSQL_ASSOC,  PGSQL_BOTH or PGSQL_NUM  (default PGSQL_ASSOC)
     * \return double array (row x col ) or false
     */
 
    static function fetch_all($ret,$p_mode= PGSQL_ASSOC)
    {
        return pg_fetch_all($ret,$p_mode);
    }
 
    /**
     * \brief wrapper for the function pg_fetch_all
     * \param $ret is the result of pg_exec (exec_sql)
     * \param $p_row is the indice of the row
     * \param $p_col is the indice of the col
     * \return a string or an integer
     */
 
    static function fetch_result($ret, $p_row = 0, $p_col = 0)
    {
        return pg_fetch_result($ret, $p_row, $p_col);
    }
 
    /**
     * \brief wrapper for the function pg_fetch_row
     * \param $ret is the result of pg_exec (exec_sql)
     * \param $p_row is the indice of the row
     * \return an array indexed from 0
     */
    static function fetch_row($ret, $p_row)
    {
        return pg_fetch_row($ret, $p_row);
    }
 
    /**
     * \brief wrapper for the function pg_lo_unlink
     * \param $p_oid is the of oid
     * \return return the result of the operation : false == fails
     */
 
    function lo_unlink($p_oid)
    {
        if (!$this->exist_blob($p_oid)) return;
        return pg_lo_unlink($this->db, $p_oid);
    }
 
    /**\brief wrapper for the function pg_prepare
     * \param $p_string string name for pg_prepare function
     * \param $p_sql  is the sql to prepare
     * \return return the result of the operation
     */
 
    function prepare($p_string, $p_sql)
    {
        return pg_prepare($this->db, $p_string, $p_sql);
    }
 
    /**
     * \brief wrapper for the function pg_execute
     * \param $p_string string name of the stmt given in pg_prepare function
     * \param $p_array contains the variables
     * \note set this->ret to the return of pg_execute
     * \return return the result of the operation,
     */
 
    function execute($p_string, $p_array)
    {
        $this->ret = pg_execute($this->db, $p_string, $p_array);
        return $this->ret;
    }
 
    /**
     * \brief wrapper for the function pg_lo_export
     * \param $p_oid is the oid of the log
     * \param $tmp_file  is the file
     * \return result of the operation
     */
 
    function lo_export($p_oid, $tmp_file)
    {
        return pg_lo_export($this->db, $p_oid, $tmp_file);
    }
 
    /**
     * \brief wrapper for the function pg_lo_export
     * \param $p_filename is the filename
     * \param $tmp  is the file
     * \return result of the operation
     */
 
    function lo_import($p_filename)
    {
        return pg_lo_import($this->db, $p_filename);
    }
 
    /**
     * \brief wrapper for the function pg_escape_string
     * \param $p_string is the string to escape
     * \return escaped string
     */
 
    static function escape_string($p_string)
    {
        static $cn=null;
        if ( $cn==null) $cn=new Database();
        return pg_escape_string($cn->db,$p_string);
    }
 
    /**
     * \brief wrapper for the function pg_close
     */
 
    function close()
    {
        if ($this->is_open) pg_close($this->db);
        $this->is_open = FALSE;
    }
 
    /**
     * \brief
     * \param
     * \return
     * \note
     * \see
     */
 
    function __toString()
    {
        return "database ";
    }
 
    static function test_me()
    {
 
    }
    /**
     * @brief get the transaction status : 
     * The status can be 
     *    -  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.      
     * * @return  PGSQL_TRANSACTION_IDLE | PGSQL_TRANSACTION_ACTIVE | PGSQL_TRANSACTION_INTRANS | PGSQL_TRANSACTION_INERROR | PGSQL_TRANSACTION_UNKNOWN | PGSQL_TRANSACTION_ACTIVE
     */
    function status()
    {
        return pg_transaction_status($this->db);
    }
 
 
    /**
     * @brief Find all lob and remove those which are not used by any tables
     *
     */
    function clean_orphan_lob()
    {
        // find all columns of type lob
        $sql = " 
                select table_schema,table_name,column_name 
                from 
                    information_schema.columns 
                where table_schema not in ('information_schema','pg_catalog') 
                and data_type='oid'";
        $all_lob = "
            select oid,'N' as used from pg_largeobject_metadata
            ";
        $a_table = $this->get_array($sql);
        $a_lob = $this->get_array($all_lob);
        if ($a_table == false || $a_lob == false) return;
        // for each lob
        $nb_lob = count($a_lob);
        $nb_table = count($a_table);
        for ($i = 0; $i < $nb_lob; $i++) {
            $lob = $a_lob[$i]['oid'];
            if ($a_lob[$i]['used'] == 'Y') continue;
            for ($j = 0; $j < $nb_table; $j++) {
                if ($a_lob[$i]['used'] == 'Y') continue;
                $check = $this->get_value(" select count(*) from " .
                    $a_table[$j]['table_schema'] . "." . $a_table[$j]['table_name'] .
                    " where " .
                    $a_table[$j]['column_name'] . "=$1", array($lob));
                if ($check != 0)
                    $a_lob[$i]['used'] = 'Y';
 
            }
        }
        for ($i = 0; $i < $nb_lob; $i++) {
            if ($a_lob[$i]['used'] == 'Y') continue;
            $this->lo_unlink($a_lob[$i]['oid']);
        }
    }
 
    /**
     * @brief Check if a prepared statement already exists or not
     * @param string $query_name name of the prepared query
     * @return boolean false is not yet prepared
     */
    function is_prepare($query_name)
    {
        $nb_prepared = $this->get_value("select count(*) from pg_prepared_statements where name=$1", [$query_name]);
        if ($nb_prepared == 0) return FALSE;
        return TRUE;
    }
 
    /**
     * @brief with the handle of a successull query, echo each row into CSV and
     * send it directly to output. To save it into a file, it is needed to send first the HEADER as CSV. If there are
     *less column than column defined in $aheader, then the columns not included in the header are not displaid
     *
     * @param handle  $ret handle to a query
     * @param array $aheader double array, each item of the array contains (idx : title, type)
     * a key type (num) and a key title
     */
    function query_to_csv($ret, $aheader)
    {
        $csv = new Noalyss_Csv("db-query");
        $a_header = [];
        for ($i = 0; $i < count($aheader); $i++) {
            $a_header[] = $aheader[$i]['title'];
        }
        $csv->write_header($a_header);
 
        // fetch all the rows
        $nb = Database::num_row($ret);
        for ($i = 0; $i < $nb; $i++) {
            $row = Database::fetch_array($ret, $i);
            // for each rows, for each value
            $e=0;
            foreach ($row as $row_item){
                if ( $e >= count($a_header)) break;
                switch ($aheader[$e]['type']) {
                    case 'num':
                        $csv->add($row_item, "number");
                        break;
                    default:
                        $csv->add($row_item);
                }
                $e++;
            }
            $csv->write();
        }
    }
    /**
     * @brief Returns the number of columns in a ret
     * @param handler $p_ret handler to a query
     */
    static  function nb_column($p_ret) {
        return pg_num_fields($p_ret);
    }
    /**
     * @brief FInd if a SQL Select has a SQL stmt to inject or damage Data
     * When a SELECT SQL string is build, this string could contain a SQL attempt to damage data,
     *so the statement DELETE TRUNCATE ... are forbidden. Throw an exception EXC_INVALID
     *
     */
    function search_sql_inject($p_sql)
    {
        $forbid_sql=array("update","delete","truncate","insert");
        // protect against SQL inject
        foreach ($forbid_sql as $forbid_key) {
            if (stripos($p_sql,$forbid_key) !== false)
            {
                throw new Exception(_("Possible SQL inject"),EXC_INVALID);
            }
 
        }
    }
 
    /**
     * @brief clear a prepare stmt
     * @see DatabaseCore::is_prepare
     * @see DatabaseCore::execute
     * @see DatabaseCore::prepare
     * @param $sql_name name of the prepare SQL
     */
    function clear_prepare($sql_name)
    {
        pg_exec($this->db,sprintf('DEALLOCATE "%s"'),DatabaseCore::escape_string($sql_name));
    }
    /**
     * @brief clear all prepare stmt
     * @see DatabaseCore::is_prepare
     * @see DatabaseCore::execute
     * @see DatabaseCore::prepare
     */
    function clear_all_prepare()
    {
        pg_exec($this->db,'DEALLOCATE ALL');
    }
}
 
/* test::test_me(); */