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 ''._('Impossible de se connecter à postgreSql').'
';
echo '';
echo _("Vos paramètres sont incorrectes").":
";
echo "
";
printf (_("base de donnée = %s"), $p_dbname)."
";
printf (_("Port %s"),$p_port )."
";
printf ( _("Utilisateur : %s"),$p_user )."
";
echo '
';
die();
} else {
echo '' . _('Erreur de connexion !') . '
';
$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
", $script);
$sql = "";
$flag_function = false;
while (!feof($hf)) {
$buffer = fgets($hf);
$buffer = str_replace('$BODY$', '$_$', $buffer);
print $buffer . "
";
// 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 "
";
} // 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(); */