public static function castLink($link, array $a, Stub $stub, $isNested)
{
$a['status'] = pg_connection_status($link);
$a['status'] = new ConstStub(PGSQL_CONNECTION_OK === $a['status'] ? 'PGSQL_CONNECTION_OK' : 'PGSQL_CONNECTION_BAD', $a['status']);
$a['busy'] = pg_connection_busy($link);
$a['transaction'] = pg_transaction_status($link);
if (isset(self::$transactionStatus[$a['transaction']])) {
$a['transaction'] = new ConstStub(self::$transactionStatus[$a['transaction']], $a['transaction']);
}
$a['pid'] = pg_get_pid($link);
$a['last error'] = pg_last_error($link);
$a['last notice'] = pg_last_notice($link);
$a['host'] = pg_host($link);
$a['port'] = pg_port($link);
$a['dbname'] = pg_dbname($link);
$a['options'] = pg_options($link);
$a['version'] = pg_version($link);
foreach (self::$paramCodes as $v) {
if (false !== ($s = pg_parameter_status($link, $v))) {
$a['param'][$v] = $s;
}
}
$a['param']['client_encoding'] = pg_client_encoding($link);
$a['param'] = new EnumStub($a['param']);
return $a;
}
public function __construct()
{
if (!is_null($this->_connection)) {
return true;
}
global $conf;
switch ($conf['db']['type']) {
case "pgsql":
$this->_dbtype = 1;
break;
case "mysql":
$this->_dbtype = 2;
break;
}
if ($this->_dbtype == 1) {
$this->_connection = pg_connect(($conf['db']['host'] ? "host=" . $conf['db']['host'] : "") . ($conf['db']['port'] ? " port=" . $conf['db']['port'] : "") . ($conf['db']['name'] ? " dbname=" . $conf['db']['name'] : "") . ($conf['db']['user'] ? " user="******"") . ($conf['db']['pass'] ? " password="******""));
if (!$this->_connection) {
throw new CodeKBException(__METHOD__, "db", "failedconnect", pg_last_notice($this->_connection));
}
}
if ($this->_dbtype == 2) {
$this->_connection = mysql_connect($conf['db']['host'] . ($conf['db']['port'] ? ":" . $conf['db']['port'] : ""), $conf['db']['user'], $conf['db']['pass']);
if (!$this->_connection) {
throw new CodeKBException(__METHOD__, "db", "failedconnect", mysql_error($this->_connection));
}
mysql_select_db($conf['db']['name'], $this->_connection);
}
}
function tester()
{
$res = pg_query(dbh, 'SELECT test_notice()');
$row = pg_fetch_row($res, 0);
var_dump($row);
pg_free_result($res);
if ($row[0] == 'f') {
var_dump(pg_last_notice(dbh));
}
}
public function __construct()
{
if (!is_null($this->_connection)) {
return true;
}
global $conf;
$this->_connection = pg_connect(($conf['db']['host'] ? "host=" . $conf['db']['host'] : "") . ($conf['db']['port'] ? " port=" . $conf['db']['port'] : "") . ($conf['db']['name'] ? " dbname=" . $conf['db']['name'] : "") . ($conf['db']['user'] ? " user="******"") . ($conf['db']['pass'] ? " password="******""));
if (!$this->_connection) {
throw new CodeKBException(__METHOD__, "db", "failedconnect", pg_last_notice($this->_connection));
}
}
public function query($query)
{
$result = \pg_query($this->connection, $query);
if (($info = \pg_last_notice($this->connection)) != '') {
new \SYSTEM\LOG\INFO($info);
}
if (!$result) {
throw new \SYSTEM\LOG\ERROR('Could not query Database. Check ur Query Syntax or required Rights: ' . pg_last_error($this->connection));
}
if ($result === TRUE) {
return TRUE;
}
return new ResultPostgres($result, $this);
}
/**
* Берем таблицы, последний анализ, которых был ранее чем analyze_min_age секунд назад
* или количество изменненных строк > analyze_changed_factor * кол-во строк.
* Сначала идут по времени последнего анализа.
* Вопрос в том, что если сработал обычный автовакуум, то в autovacuum_log статистика не поменяется,
* т.е. не будет известно сколько строк в реальности изменилось с последнего анализа.
* Решение: не принимать во внимание, т.е. просто соблюдаем условия выше. Может случиться "лишний" анализ, не беда.
*/
public function analyze($db_alias, $mode = self::MODE_ANALYZE)
{
$cfg = $this->_config[$mode];
$log = new log($cfg['log_file'], 'w');
// !!! SERVER добавить (для альфы/беты).
$DB = new DB($db_alias);
$log->linePrefix = $this->_config['log_line_prefix'];
$log->writeln('Получаем список таблиц');
$last_av_col = 'COALESCE(maintenance.max(' . ($mode == self::MODE_ANALYZE ? 'pt.last_analyze, pt.last_autoanalyze, ' : '') . "pt.last_vacuum, pt.last_autovacuum), 'epoch')";
$sql = "\n WITH w_stat as (\n SELECT ts.*, pt.*, ts.relid IS NULL as _is_new, {$last_av_col} as _last_av\n FROM pg_stat_user_tables pt\n LEFT JOIN\n maintenance.table_stat ts\n ON ts.relid = pt.relid\n WHERE pt.schemaname = 'public'\n AND pt.n_live_tup + pt.n_dead_tup > 0\n )\n (\n (SELECT *, 0 as ord, 0.00 as ord2 FROM w_stat WHERE (_is_new OR _last_av + interval '?i seconds' <= now()))\n UNION ALL\n (SELECT *, 1, tup_factor FROM w_stat WHERE tup_factor >= ?f)\n )\n ORDER BY ord, ord2 DESC, _last_av\n ";
$tbls = $DB->rows($sql, $cfg['min_age'], $cfg['changed_factor']);
$acnt = 0;
$log->writeln(count($tbls) . ' таблиц');
foreach ($tbls as $t) {
if ($log->getTotalTime(null) >= $cfg['max_duration']) {
$log->writeln('Время истекло.');
break;
}
$DB->query(($mode == self::MODE_VACUUM ? 'VACUUM ' : 'ANALYZE') . " VERBOSE {$t['relname']}");
$cmpls[(int) ($t['_is_new'] == 't')][] = $t['relid'];
$log->writeln(pg_last_notice(DB::$connections[$db_alias]));
++$acnt;
}
// Обновляем статистику.
if ($cmpls[0]) {
$cols = $this->_mt_cols;
unset($cols[0], $cols[1]);
$lcols = implode(',', $cols);
$rcols = 'pt.' . implode(', pt.', $cols);
$sql = "UPDATE maintenance.table_stat ts SET ({$lcols}) = ({$rcols}) FROM pg_stat_user_tables pt WHERE pt.relid IN (?l) AND ts.relid = pt.relid";
$DB->query($sql, $cmpls[0]);
}
if ($cmpls[1]) {
$cols = implode(',', $this->_mt_cols);
$sql = "INSERT INTO maintenance.table_stat ({$cols}) SELECT {$cols} FROM pg_stat_user_tables WHERE relid IN (?l)";
$DB->query($sql, $cmpls[1]);
}
// Удаляем лишние таблицы, типа catalog_positions2 (создаются/удаляются автоматом)
$DB->query('DELETE FROM maintenance.table_stat ts WHERE NOT EXISTS (SELECT 1 FROM pg_stat_user_tables WHERE relid = ts.relid)');
$log->writeln("Обработано {$acnt} таблиц");
}
function lcm_connect_db_test($host, $login, $pass, $db, $port = 0)
{
global $link;
global $lcm_pgsql_error;
unset($link);
// Non-silent connect, should be shown in <!-- --> anyway
if ($port > 0) {
$host = "{$host}:{$port}";
}
$str = $host ? "host={$host} " : '';
$str .= $login ? "user={$login} " : '';
$str .= $pass ? "password={$pass} " : '';
$str .= $db ? "dbname={$db}" : '';
// Capture output and store it into $lcm_pgsql_error
ob_start();
$link = pg_connect($str);
// $lcm_pgsql_error = "XX" . ob_get_contents();
$lcm_pgsql_error = pg_last_notice();
ob_end_clean();
if ($link) {
pg_close($link);
return true;
} else {
return false;
}
}
/**
name:SQLX_Insert
parm:string/array table
parm:array Row
parm:bool Rewrite_Skey
parm:bool Clip_Fields
returns:int
In its most basic form, this routine accepts a [[Row Array]]
and attempts to insert it into a table. Upon success, the routine
returns the skey value of the new row.
The first entry can be either a [[Table Reference]] or the name of
a table. The second entry is always a [[Row Array]]. This function
makes use of the dictionary to determine the correct formatting of all
columns, and ignores any column in the [[Row Array]] that is not
in the table.
The third parameter is used by the framework, and should always be
false. If the third parameter is set to true, then this routine
executes a [[gpSet]] with the value of skey for the new row, making
it look like this row came from the browser.
If the fourth parameter is true, values are clipped to column width
to prevent overflows. This almost guarantees the insert will succeed,
but should only be done if it is acceptable to throw away the ends of
columns.
*/
function SQLX_Insert($table, $colvals, $rewrite_skey = true, $clip = false)
{
# KFD 6/12/08, use new and improved
errorsClear();
if (!is_array($table)) {
$table = DD_TableRef($table);
}
$table_id = $table["table_id"];
$view_id = ddTable_idResolve($table_id);
$tabflat =& $table["flat"];
$new_cols = "";
$new_vals = "";
foreach ($tabflat as $colname => $colinfo) {
if (isset($colvals[$colname])) {
//if($colvals[$colname]<>'') {
if (DD_ColInsertsOK($colinfo, 'db')) {
# KFD 6/18/08, % signs really mess things up
#if(strpos($colvals[$colname],'%')!==false) {
# ErrorAdd("The % sign may not be in a saved value");
# vgfSet('ErrorRow_'.$table_id,$colvals);
# return 0;
#}
$cliplen = $clip ? $colinfo['colprec'] : 0;
$new_cols .= ListDelim($new_cols) . " " . $colname;
$new_vals .= ListDelim($new_vals) . " " . SQL_FORMAT($colinfo["type_id"], $colvals[$colname], $cliplen);
}
//}
}
}
if (!Errors()) {
$sql = "INSERT INTO " . $view_id . " ({$new_cols}) VALUES ({$new_vals})";
}
x4Debug($sql);
x4Debug(SessionGet('UID'));
// ERRORROW CHANGE 5/30/07, big change, SQLX_* routines now save
// the row for the table if there was an error
$errflag = false;
SQL($sql, $errflag);
if ($errflag) {
vgfSet('ErrorRow_' . $table_id, $colvals);
}
$notices = pg_last_notice($GLOBALS["dbconn"]);
$retval = 0;
$matches = array();
# KFD 10/18/08. This venerable line has been quietly working forever,
# until today! The problem turned out to be the table
# name had a number in it, which screwed it up! So
# I've changed one line here.
#preg_match_all("/SKEY(\D*)(\d*);/",$notices,$matches);
preg_match_all("/SKEY(.*\\s)(\\d*);/iu", $notices, $matches);
if (isset($matches[2][0])) {
$retval = $matches[2][0];
if ($rewrite_skey) {
gpSet("gp_skey", $matches[2][0]);
gpSet("gp_action", "edit");
}
}
// Possibly cache the row
$cache_pkey0 = vgfget('cache_pkey', array());
$cache_pkey = array_flip($cache_pkey0);
if (isset($cache_pkey[$table_id])) {
CacheRowPut($table, $colvals);
}
return $retval;
}
function NoticeMsg()
{
if (!empty($this->_connectionID)) {
return @pg_last_notice($this->_connectionID);
} else {
return null;
}
}
<?php
require_once 'config.inc';
require_once 'lcmess.inc';
$dbh = @pg_connect($conn_str);
if (!$dbh) {
die("Could not connect to the server");
}
_set_lc_messages();
$res = pg_query($dbh, "CREATE OR REPLACE FUNCTION test_notice() RETURNS boolean AS '\nbegin\n RAISE NOTICE ''11111'';\n return ''f'';\nend;\n' LANGUAGE plpgsql;");
$res = pg_query($dbh, 'SET client_min_messages TO NOTICE;');
var_dump($res);
$res = pg_query($dbh, 'SELECT test_notice()');
var_dump($res);
$row = pg_fetch_row($res, 0);
var_dump($row);
pg_free_result($res);
if ($row[0] == 'f') {
var_dump(pg_last_notice($dbh));
}
pg_close($dbh);
?>
===DONE===
/**
* Gets the DBMS' native error message produced by the last query
*
* {@internal Error messages are used instead of error codes
* in order to support older versions of PostgreSQL.}}
*
* @return string the DBMS' error message
*/
function errorNative()
{
return @pg_last_notice($this->connection) . "\n\n" . @pg_errormessage($this->connection);
}
/**
* Executes the query that was previously passed to the constructor.
*
* @param mixed $arg Query arguments to escape and insert at ? placeholders in $query
* @param mixed ... Additional arguments
**/
public function execute()
{
// We can't very well perform a query wtihout an active connection.
if ($this->dbh === false || pg_connection_status($this->dbh) !== PGSQL_CONNECTION_OK) {
$this->db->error('Lost connection to database.');
return;
}
// Finish any previous statements
$this->finish();
// We need to pre-process the arguments for literals and bytefields. We
// can't safely pass them into the naitve placeholder system, so we'll
// have to stick them into the query at the right places before then.
// Additionally, PG uses the string values 't' and 'f' for boolean true
// and false, and will choke over PHP true/false when passed in this way.
$args = Database::smart_args(func_get_args());
foreach ($args as $i => $arg) {
if (is_bool($arg)) {
$args[$i] = $arg === true ? 't' : 'f';
}
}
list($query, $nargs, $nargn) = $this->reprocess_query($args);
if (!$query) {
return;
}
// Prepare the placeholders. PG uses $1, $2 .. $n as their placeholders.
// Continuing the example from Database_Query::reprocess_query, we should
// have the following $query array:
// 'SELECT * FROM table WHERE a = ',
// ' AND b < NOW() AND c > '
// which will get turned into the following string:
// SELECT * FROM table WHERE a = $1 AND b < NOW() AND c > $2
$this->last_query = '';
$placeholder_count = 1;
foreach ($query as $i => $chunk) {
$this->last_query .= $chunk;
if ($placeholder_count <= $nargn) {
$this->last_query .= '$' . $placeholder_count;
$placeholder_count++;
}
}
// Wrap the actual query execution in a bit of benchmarking.
$before = microtime(true);
// We're only using placeholders here, not prepared statements. Why not?
// The possibility of missing / pre-replaced placeholders means that the
// actual query we get to execute changes each time.
$this->sh = false;
$this->errno = null;
$state = pg_send_query_params($this->dbh, $this->last_query, $nargs);
if ($state) {
// So, here's some fun. Like PDO, PG has error reporting bits at both
// the top level and at the statement level. However, the normal
// query method returns false instead of a statement handle, and we
// need the statement handle to pull errors back. This means that
// we need to use the async query sending method and check every single
// time for an error message. We can then nuke the statement handle
// so things that try to look for it to detect errors can work.
$this->sh = pg_get_result($this->dbh);
$sqlstate = pg_result_error_field($this->sh, PGSQL_DIAG_SQLSTATE);
if ($sqlstate && $sqlstate != '00000') {
$this->errno = $sqlstate;
$this->errstr = pg_result_error_field($this->sh, PGSQL_DIAG_MESSAGE_PRIMARY) . ' [detail=' . pg_result_error_field($this->sh, PGSQL_DIAG_MESSAGE_DETAIL) . '] (hint=' . pg_result_error_field($this->sh, PGSQL_DIAG_MESSAGE_HINT) . ') at character ' . pg_result_error_field($this->sh, PGSQL_DIAG_STATEMENT_POSITION);
$this->sh = false;
}
} else {
$this->db->error('Could not send query to server.');
return;
}
$after = microtime(true);
$this->db->mysql_time += $after - $before;
// The other adapters also fetch the last insert id here, which we can't
// effectively do, as it's not exposed by PG. As an alternative, you can
// use a RETURNING clause in your query to fetch generated values:
// INSERT INTO table(foo, bar) VALUES(?, ?) RETURNING id;
if (is_bool($this->sh) || is_null($this->sh)) {
$this->affected_rows = 0;
$this->num_rows = 0;
} elseif (is_resource($this->sh)) {
$this->affected_rows = pg_affected_rows($this->sh);
$this->num_rows = pg_num_rows($this->sh);
} else {
assert('false; // statement handler was not a bool or resource, it was a: ' . gettype($this->sh));
}
// Upstream code may care about warnings. PG can return multiple notices
// for any given query. It's unclear whether or not this will work, as
// I haven't been able to actually find any query that does emit multiple
// notices. If your code suddenly launches into an infinite loop, now you
// know why. Fun times indeed.
if ($this->db->enable_warning_logging) {
$this->warnings = array();
while (($last_notice = pg_last_notice($this->dbh)) !== false) {
$this->warnings[] = $last_notice;
}
$GLOBALS['_DEBUG']['Database Warnings'][] = array('Query' => $this->last_query, 'Warnings' => $this->warnings);
}
// Finally, did it even work?
if ($this->sh === false) {
$this->db->error('SQL Error:');
return false;
}
return true;
}
function x4sqlIns($table, $row, $rowret = 0)
{
$tabdd = ddTable($table);
if (count($tabdd) == 0) {
x4Error('Cannot insert to ' . $table . ', no data dictionary');
return;
}
$flat =& $tabdd["flat"];
// Convert all row values into sql-formatted values,
// only known columns come back from this call
$sfrow = sqlFormatRow($tabdd, $row);
// Drop the columns we are not allowed to insert to
$noWrites = ddNoWrites();
foreach ($sfrow as $column => $value) {
if (in_array($flat[$column]['automation_id'], $noWrites)) {
unset($sfrow[$column]);
}
}
// Assemble and execute the SQL
$view = ddViewFromTab($table);
$sq = 'INSERT INTO ' . $view . ' (' . implode(',', array_keys($sfrow)) . ')' . ' values ' . ' (' . implode(',', $sfrow) . ')';
x4SQL($sq);
// Fetch the skey value
$notices = pg_last_notice($GLOBALS['AG']['dbconn']);
$anotices = explode(' ', $notices);
$retval = 0;
if (count($anotices) > 1) {
$retval = array_pop($anotices);
}
//$matches = array();
//preg_match_all("/SKEY(\D*)(\d*);/",$notices,$matches);
//hprint_r($matches);
//if(isset($matches[2][0])) {
// $retval = $matches[2][0];
//}
// if row return was true, and no errors, return
// the row instead of the skey value
if ($rowret == 0) {
return $retval;
} else {
if (x4Errors()) {
return array();
} else {
$sq = "SELECT * FROM {$view} WHERE skey={$retval}";
return x4sqlOneRow($sq);
}
}
}
/**
* Escape data for safety
*
* @param string $data
* @return string
*/
public function sanitize($data)
{
$link = $this->connect();
$result = !$link ? false : pg_escape_string($link, $data);
if ($result === false) {
$this->error('PostgreSQL error: ' . pg_last_notice($link));
}
pg_close($link);
return $result;
}
/**
* Get postgres last notice()
* @return string
*/
public function getLastNotice()
{
$notice = pg_last_notice($this->resource->get());
// this generic text doesn't actually tell us anything and clutters up the notice.
// remove
$notice = str_replace('LOCATION: exec_stmt_raise, pl_exec.c:2840', '', $notice);
return trim($notice);
}
<?php
include 'config.inc';
include 'lcmess.inc';
$db = pg_connect($conn_str);
_set_lc_messages();
$res = pg_query($db, 'SET client_min_messages TO NOTICE;');
var_dump($res);
pg_query($db, "BEGIN;");
pg_query($db, "BEGIN;");
$msg = pg_last_notice($db);
if ($msg === FALSE) {
echo "Cannot find notice message in hash\n";
var_dump($msg);
}
echo $msg . "\n";
echo "pg_last_notice() is Ok\n";
/**
* PgSQL Query :: Write Ignore - Catch Duplicate Key Violation or Foreign Key Violation Errors (This is the equivalent of MySQL's INSERT IGNORE / UPDATE IGNORE / DELETE IGNORE, but it can catch UNIQUE violations on both: INSERT / UPDATE / DELETE statements and also can catch FOREIGN KEY violations).
* This function is intended to be used only for write type queries like: INSERT / UPDATE / DELETE which can be ignored if unique violations or foreign key violations and will return the # of affected rows or zero if an exception raised.
* The catch of PostgreSQL exceptions is handled completely by this function so there is no need for a catch errors outside.
*
* IMPORTANT:
* This function needs the pgsql notice message tracking enabled in PHP (not ignored); This must be set in php.ini (pgsql.ignore_notice = 0).
* The internal mechanism of this function to catch UNIQUE or FOREIGN KEYS violations is that the EXCEPTIONS are catch at the PostgreSQL level in a DO block.
* This is the best approach to handle safe UPSERT or INSERT IGNORE / UPDATE IGNORE / DELETE IGNORE like queries in high load envionments or to avoid fatal errors when a INSERT / UPDATE / DELETE violates a unique key or a foreign key with PostgreSQL.
* This function can be used inside transactions blocks but never use this function to execute statements as: BEGIN, START TRANSACTION, COMMIT, ROLLBACK or SET statements, as the context is incompatible.
* HINTS:
* On PostgreSQL 9.5/later there is an alternative which can be used directly with write_data() without the need of this function as the following statement: INSERT ... ON CONFLICT DO NOTHING/UPDATE ... (as the equivalent of INSERT IGNORE / UPSERT), but the following statements are still missing (not implemented): UPDATE ... ON CONFLICT DO NOTHING / DELETE ... ON CONFLICT DO NOTHING .
* This function will remain in the future to offer backward compatibility with PostgreSQL 8.4 ... 9.5 even if PostgreSQL at some moment will have ON CONFLICT DO implemented for all 3 INSERT / UPDATE / DELETE.
*
* @param STRING $queryval :: the query
* @param STRING $params_or_title :: *optional* array of parameters ($1, $2, ... $n) or query title for easy debugging
* @param RESOURCE $y_connection :: the connection
* @return ARRAY :: [0 => 'control-message', 1 => #affected-rows]
*/
public static function write_igdata($queryval, $params_or_title = '', $y_connection = 'DEFAULT')
{
//==
$y_connection = self::check_connection($y_connection, 'WRITE-IG-DATA');
//==
//-- samples
// $queryval = 'UPDATE "tablename" SET "field" = \'value\' WHERE ("id_field" = \'val1\')';
// $queryval = 'INSERT INTO "tablename" ("desiredfield1", "desiredfield2") VALUES (\'val1\', \'val2\')';
//--
// ##### 'pgsql.ignore_notice' must be set to 0 in PHP.INI (checked via connect) #####
//--
/* PRE-CHECK (DO NOT ALLOW IN TRANSACTION BLOCKS) - No More Necessary !!, now can be safe used also in transactions as the exceptions are catch in the DO block
$transact_status = @pg_transaction_status($y_connection);
if(($transact_status === PGSQL_TRANSACTION_INTRANS) OR ($transact_status === PGSQL_TRANSACTION_INERROR)) {
self::error($y_connection, 'WRITE-IG-DATA', 'ERROR: Write Ignore cannot be used inside Transaction Blocks ...', $queryval, '');
return array('errortransact: '.'Write Ignore cannot be used inside Transaction Blocks', 0);
} //end if
*/
//--
//--
$time_start = 0;
if ((string) SMART_FRAMEWORK_DEBUG_MODE == 'yes') {
$time_start = microtime(true);
}
//end if
//--
//--
$use_param_query = false;
if (strpos((string) $queryval, '$') !== false and Smart::array_size($params_or_title) > 0) {
$use_param_query = true;
}
//end if
//--
if ($use_param_query === true) {
$the_query_title = '';
} else {
$the_query_title = (string) $params_or_title;
}
//end if else
//--
//--
/* At the moment, in PgSQL 9.5 only works ON CONFLICT DO NOTHING for INSERT (for UPDATE statements fails ...)
if(version_compare(self::check_server_version($y_connection), '9.6') >= 0) {
//--
$xmode = 'affected';
$vmode = '[ON CONFLICT DO NOTHING]';
//--
$prep_query = (string) $queryval.' ON CONFLICT DO NOTHING'; // fix for PostgreSQL >= 9.5 :: RETURNING *
//--
if($use_param_query === true) {
$result = @pg_query_params($y_connection, $prep_query, $params_or_title); // NOTICE: parameters are only allowed in ONE command not combined statements
} else {
$result = @pg_query($y_connection, $prep_query);
} //end if else
//--
} else {
*/
//--
if ((string) ini_get('pgsql.ignore_notice') != '0') {
// {{{SYNC-PGSQL-NOTIF-CHECK}}}
self::error($y_connection, 'WRITE-IG-DATA', 'Check PgSQL PHP.INI Settings', 'SETTINGS: PostgreSQL Notifications need to be ENABLED in PHP.INI !', 'SET in PHP.INI this: pgsql.ignore_notice = 0');
return array('errorinits: PostgreSQL Notifications need to be ENABLED in PHP.INI', 0);
}
//end if
//--
$xmode = 'notice';
$vmode = '[Catch EXCEPTION on Violations for: Unique / Foreign Key]';
//--
if ($use_param_query === true) {
$queryval = (string) self::prepare_param_query((string) $queryval, (array) $params_or_title, $y_connection);
}
//end if
//--
$unique_id = 'WrIgData_PgSQL_' . Smart::uuid_10_seq() . '_' . Smart::uuid_10_str() . '_' . Smart::uuid_10_num() . '_' . sha1(SmartUtils::client_ident_private_key()) . '_' . sha1(SmartUtils::get_visitor_tracking_uid() . ':' . Smart::uuid_36('pgsql-write-ig') . ':' . Smart::uuid_45('pgsql-write-ig')) . '_Func';
// this must be a unique that cannot guess to avoid dollar escaping injections
//--
$prep_query = (string) '
DO LANGUAGE plpgsql
$' . $unique_id . '$
DECLARE affected_rows BIGINT;
BEGIN
-- do the query an safe catch exceptions (unique key, foreign key)
affected_rows := 0;
' . "\t\t" . trim(rtrim($queryval, ';')) . ';' . '
GET DIAGNOSTICS affected_rows = ROW_COUNT;
RAISE NOTICE \'SMART-FRAMEWORK-PGSQL-NOTICE: AFFECTED ROWS #%\', affected_rows;
RETURN;
EXCEPTION
WHEN unique_violation THEN RAISE NOTICE \'SMART-FRAMEWORK-PGSQL-NOTICE: AFFECTED ROWS #0\';
WHEN foreign_key_violation THEN RAISE NOTICE \'SMART-FRAMEWORK-PGSQL-NOTICE: AFFECTED ROWS #0\'; -- this is a different behaviour than ON CONFLICT DO NOTHING in PgSQL 9.5 or later versions ...
END
$' . $unique_id . '$;
';
//--
$result = @pg_query($y_connection, $prep_query);
//--
//} //end if else
//--
//--
$error = '';
$affected = 0;
if (!$result) {
$error = 'Query FAILED:' . "\n" . @pg_last_error($y_connection);
} else {
//if((string)$xmode == 'notice') {
$affected = (int) self::get_notice_smart_affected_rows(@pg_last_notice($y_connection));
// in this case we can only monitor affected rows via a custom notice (the only possible way to return something from anonymous pgsql functions ...)
//} else { // affected
// $affected = @pg_affected_rows($result); // for PostgreSQL >= 9.5
//} //end if else
}
//end if else
//--
//--
$time_end = 0;
if ((string) SMART_FRAMEWORK_DEBUG_MODE == 'yes') {
$time_end = (double) (microtime(true) - (double) $time_start);
}
//end if
//--
//--
if ((string) SMART_FRAMEWORK_DEBUG_MODE == 'yes') {
//--
SmartFrameworkRegistry::setDebugMsg('db', 'pgsql|total-queries', 1, '+');
//--
SmartFrameworkRegistry::setDebugMsg('db', 'pgsql|total-time', $time_end, '+');
//--
$dbg_query_params = '';
//--
if (strtoupper(substr(trim($queryval), 0, 5)) == 'BEGIN' or strtoupper(substr(trim($queryval), 0, 17)) == 'START TRANSACTION' or strtoupper(substr(trim($queryval), 0, 6)) == 'COMMIT' or strtoupper(substr(trim($queryval), 0, 8)) == 'ROLLBACK') {
// ERROR
self::error($y_connection, 'WRITE-IG-DATA ' . $vmode, 'ERROR: This function cannot handle TRANSACTION Specific Statements ...', $queryval, $the_query_title);
return array('errorsqlstatement: ' . 'This function cannot handle TRANSACTION Specific Statements', 0);
} elseif (strtoupper(substr(trim($queryval), 0, 4)) == 'SET ') {
// ERROR
self::error($y_connection, 'WRITE-IG-DATA ' . $vmode, 'ERROR: This function cannot handle SET Statements ...', $queryval, $the_query_title);
return array('errorsqlstatement: ' . 'This function cannot handle SET Statements', 0);
} else {
SmartFrameworkRegistry::setDebugMsg('db', 'pgsql|log', ['type' => 'write', 'data' => 'WRITE / IGNORE ' . $vmode . ' :: ' . $the_query_title, 'query' => $queryval, 'params' => $dbg_query_params, 'rows' => $affected, 'time' => Smart::format_number_dec($time_end, 9, '.', ''), 'connection' => (string) $y_connection]);
}
//end if else
//--
}
//end if
//--
//--
if (strlen($error) > 0) {
//--
$message = 'errorsqlwriteoperation: ' . $error;
//--
self::error($y_connection, 'WRITE-IG-DATA ' . $vmode, $error, $queryval, $the_query_title);
return array($message, 0);
//--
} else {
//--
$record = @pg_fetch_row($result);
//--
$message = 'oksqlwriteoperation';
// this can be extended to detect extra notices
//--
}
//end else
//--
//--
if (is_resource($result)) {
// check in case of error
@pg_free_result($result);
}
//end if
//--
//--
return array($message, Smart::format_number_int($affected, '+'));
//--
}
