private function _generate_iv()
{
// Initialize pseudo random generator
// seed rand: (double)microtime()*1000000 // no more needed
// Collect very random data.
// Add as many "pseudo" random sources as you can find.
// Possible sources: Memory usage, diskusage, file and directory content...
$iv = Smart::random_number();
$iv .= Smart::unique_entropy();
$iv .= SmartUtils::get_visitor_tracking_uid();
$iv .= implode("\r", (array) $_SERVER);
$iv .= implode("\r", (array) $_COOKIES);
return $this->_hash($iv);
}
/**
* 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, '+'));
//--
}
