final class \SmartPgsqlExtDb
{ } ->

Class: SmartPgsqlExtDb - provides a Dynamic (Extended) PostgreSQL DB Server Client that can be used with custom made connections.
Tested and Stable on PostgreSQL versions: 9.0.x / 9.1.x / 9.2.x / 9.3.x / 9.4.x / 9.5.x / 9.6.x / 10.x / 11.x / 12.x / 13.x
Tested and Stable with PgPool-II versions: 3.0.x / 3.1.x / 3.2.x / 3.3.x / 3.4.x / 3.5.x / 3.6.x / 3.7.x / 4.0.x / 4.1.x
Tested and Stable with PgBouncer: all versions
This class is made to be used with custom made PostgreSQL connections (other servers than default).

Language: PHP
Located at: lib/framework/plugins/lib_db_pgsql.php
Package: Plugins:Database:PostgreSQL
Namespace: \
Class Name: SmartPgsqlExtDb
Version: v.20230915
Depends: extensions: PHP PostgreSQL ; classes: Smart, SmartEnvironment, SmartUnicode, SmartComponents (optional) ; constants: SMART_FRAMEWORK_SQL_CHARSET
Hints: This class have no catcheable exception because the ONLY errors will raise are when the server returns an ERROR regarding a malformed SQL Statement, which is not acceptable to be just exception, so will raise a fatal error !
Usage: dynamic object: (new Class())->method() - This class provides only DYNAMIC methods

class Methods

public function __construct ( array $y_configs_arr ) {} @

@param: {ARRAY} $y_configs_arr: The Array of Configuration parameters for the connection - the ARRAY STRUCTURE should be identical with the default config.php: $configs['pgsql'].

Class Constructor
It will initiate also the custom connection
or will re-use an existing connection (if the same connection parameters are provided)
for a PostgreSQL Server.

public function getConnection ( ) {} ->

Returns the connection resource of the current PostgreSQL Server.

public function quote_likes ( STRING $y_string ) {} ->

@param: {STRING} $y_string: A String or a Number to be Quoted for LIKES

Fix a string to be compliant with PgSQL LIKE / ILIKE / SIMILAR syntax.
It will use special quotes for the LIKE / ILIKE / SIMILAR special characters: % _
This function IS NOT INTENDED TO ESCAPE AGAINST SQL INJECTIONS ; USE IT ONLY WITH PREPARED PARAMS OR USE escape_str() with mode 'likes' / escape_literal() with mode 'likes'

public function quote_regex ( STRING $y_string ) {} ->

@param: {STRING} $y_string: A String or a Number to be Quoted for REGEX

Fix a string to be compliant with PgSQL REGEX syntax.
It will use special quotes for the REGEX special characters: . \ + * ? [ ^ ] $ ( ) { } = ! < > | : -
This function IS NOT INTENDED TO ESCAPE AGAINST SQL INJECTIONS ; USE IT ONLY WITH PREPARED PARAMS OR USE escape_str() with mode 'regex' / escape_literal() with mode 'regex'

public function escape_str ( STRING $y_string, ENUM $y_mode = "" ) {} -> STRING

@return: {STRING} The Escaped String / Number
@param: {STRING} $y_string: A String or a Number to be Escaped
@param: {ENUM} $y_mode: '' = default ; 'likes' = Escape LIKE / ILIKE / SIMILAR Syntax (% _) ; :: '' = default ; 'regex' = Escape ~ ~* !~ !~* Syntax

Escape a string to be compliant and Safe (against SQL Injection) with PgSQL standards.
This function will not add the (single) quotes arround the string, but just will just escape it to be safe.

public function escape_literal ( STRING $y_string, ENUM $y_mode = "" ) {} -> STRING

Escape a variable in the literal way to be compliant and Safe (against SQL Injection) with PgSQL standards.
This function will add the (single) quotes arround the string as needed and will escape expressions containing backslashes \ in the postgresql way using E'' escapes.
This is the preferred way to escape variables inside PostgreSQL SQL Statements, and is better than escape_str().

public function escape_identifier ( STRING $y_identifier ) {} -> STRING

@return: {STRING} The Escaped Identifier as: "field" / "table"
@param: {STRING} $y_identifier: The Identifier to be Escaped: field / table

Escape an identifier to be compliant and Safe (against SQL Injection) with PgSQL standards.
This function will add the (double) quotes arround the identifiers (fields / table names) as needed.

public function json_encode ( STRING $y_mixed_content, int $y_depth = 512 ) {} -> STRING

@return: {STRING} JSON string
@param: {STRING} $y_mixed_content: A mixed variable

PostgreSQL compliant and Safe Json Encode.
This should be used with PostgreSQL json / jsonb fields.

public function check_if_schema_exists ( STRING $y_schema ) {} -> 0/1

@return: {0/1} 1 if exists ; 0 if not
@param: {STRING} $y_schema: The Schema Name

Check if a Schema Exists in the current Database.

public function check_if_table_exists ( STRING $y_table, STRING $y_schema = "public" ) {} -> 0/1

@return: {0/1} 1 if exists ; 0 if not
@param: {STRING} $y_table: The Table Name
@param: {STRING} $y_schema: The Schema Name

Check if a Table Exists in the current Database.

public function count_data ( STRING $queryval, STRING $params_or_title = "" ) {} -> INTEGER

@return: {INTEGER} the result of COUNT()
@param: {STRING} $queryval: the query
@param: {STRING} $params_or_title: *optional* array of parameters ($1, $2, ... $n) or query title for easy debugging

PgSQL Query -> Count
This function is intended to be used for count type queries: SELECT COUNT().

public function read_data ( STRING $queryval, STRING $params_or_title = "" ) {} -> ARRAY

@return: {ARRAY} (non-asociative) of results :: array('column-0-0', 'column-0-1', null, ..., 'column-0-n', 'column-1-0', 'column-1-1', ... 'column-1-n', ..., 'column-m-0', 'column-m-1', ..., 'column-m-n')
@param: {STRING} $queryval: the query
@param: {STRING} $params_or_title: *optional* array of parameters ($1, $2, ... $n) or query title for easy debugging

PgSQL Query -> Read (Non-Associative) one or multiple rows.
This function is intended to be used for read type queries: SELECT.

public function read_adata ( STRING $queryval, STRING $params_or_title = "" ) {} -> ARRAY

@return: {ARRAY} (asociative) of results :: array(0 => array('column1' => 'val1', 'column2' => null, ... 'column-n' => 't'), 1 => array('column1' => 'val2', 'column2' => 'val2', ... 'column-n' => 'f'), ..., m => array('column1' => 'valM', 'column2' => 'xyz', ... 'column-n' => 't'))
@param: {STRING} $queryval: the query
@param: {STRING} $params_or_title: *optional* array of parameters ($1, $2, ... $n) or query title for easy debugging

PgSQL Query -> Read (Associative) one or multiple rows.
This function is intended to be used for read type queries: SELECT.

public function read_asdata ( STRING $queryval, STRING $params_or_title = "" ) {} -> ARRAY

@hints: ALWAYS use a LIMIT 1 OFFSET 0 with all queries using this function to avoid situations that will return more than 1 rows and will raise ERROR with this function.
@return: {ARRAY} (asociative) of results :: Returns just a SINGLE ROW as: array('column1' => 'val1', 'column2' => null, ... 'column-n' => 't')
@param: {STRING} $queryval: the query
@param: {STRING} $params_or_title: *optional* array of parameters ($1, $2, ... $n) or query title for easy debugging

PgSQL Query -> Read (Associative) - Single Row (just for 1 row, to easy the use of data from queries).
!!! This will raise an error if more than one row(s) are returned !!!
This function does not support multiple rows because the associative data is structured without row iterator.
For queries that return more than one row use: read_adata() or read_data().
This function is intended to be used for read type queries: SELECT.

public function write_data ( STRING $queryval, STRING $params_or_title = "" ) {} -> ARRAY

@return: {ARRAY} [ 0 => 'control-message', 1 => #affected-rows, 2 => returning[0,..n]|bool|null ]
@param: {STRING} $queryval: the query
@param: {STRING} $params_or_title: *optional* array of parameters ($1, $2, ... $n) or query title for easy debugging

PgSQL Query -> Write.
This function is intended to be used for write type queries: BEGIN (TRANSACTION) ; COMMIT ; ROLLBACK ; INSERT ; UPDATE ; CREATE SCHEMAS ; CALLING STORED PROCEDURES ...

public function write_igdata ( STRING $queryval, STRING $params_or_title = "" ) {} -> ARRAY

@return: {ARRAY} [ 0 => 'control-message', 1 => #affected-rows ]
@param: {STRING} $queryval: the query
@param: {STRING} $params_or_title: *optional* array of parameters ($1, $2, ... $n) or query title for easy debugging

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 any of: 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.

public function prepare_statement ( ARRAY-associative $arrdata, ENUM $mode ) {} -> STRING

@return: {STRING} The SQL partial Statement
@param: {ARRAY-associative} $arrdata: associative array: array of form data as $arr=array(); $arr['field1'] = 'a string'; $arr['field2'] = 100; | non-associative array $arr[] = 'some value'; $arr[] = 'other-value', ...
@param: {ENUM} $mode: mode: 'insert' | 'insert-subselect' | 'update' | 'in-select', 'data-array'

Create Escaped Write SQL Statements from Data - to be used with PgSQL for: INSERT ; INSERT-SUBSELECT ; UPDATE ; IN-SELECT ; DATA-ARRAY
Can be used with: write_data() or write_igdata() to build INSERT / INSERT (SELECT) / UPDATE queries from an associative array
or can be used with read_data(), read_adata(), read_asdata(), count_data() to build IN-SELECT / DATA-ARRAY queries from a non-associative array

public function prepare_param_query ( STRING $query, ARRAY $arrdata ) {} -> STRING

@return: {STRING} The SQL processed (partial/full) Statement
@param: {STRING} $query: SQL Statement to process like ' WHERE ("id" = $1)'
@param: {ARRAY} $arrdata: The non-associative array as of: $arr=array('a');

Create Escaped SQL Statements from Parameters and Array of Data by replacing $# params
This can be used for a full SQL statement or just for a part.
The statement must not contain any Single Quotes to prevent SQL injections which are unpredictable if mixing several statements at once !

public function new_safe_id ( ENUM $y_mode, $y_id_field, STRING $y_table_name, STRING $y_schema = "public" ) {} -> STRING

@return: {STRING} the generated Unique ID
@param: {ENUM} $y_mode: mode: uid10str | uid10num | uid10seq | uid12seq | uid13seq | uid15seq | uid32 | uid34 | uid35 | uid37 | uid36 | uid45
@param: {STRING} $y_field_name: the field name
@param: {STRING} $y_table_name: the table name
@param: {STRING} $y_schema: the schema (default is: public)

Get A UNIQUE (SAFE) ID for DB Tables / Schema

class Properties

class Constants

Sample code: PHP


<?php

// Sample config array for this class constructor:
 $custom_pgsql = array();
 $custom_pgsql['type']         = 'postgresql';            // postgresql / pgpool2
 $custom_pgsql['server-host']  = '127.0.0.1';             // database host (default is 127.0.0.1)
 $custom_pgsql['server-port']  = '5432';                  // database port (default is 5432)
 $custom_pgsql['dbname']       = 'smart_framework';       // database name
 $custom_pgsql['username']     = 'pgsql';                 // sql server user name
 $custom_pgsql['password']     = base64_encode('pgsql');  // sql server Base64-Encoded password for that user name B64
 $custom_pgsql['timeout']      = 30;                      // connection timeout (how many seconds to wait for a valid PgSQL Connection)
 $custom_pgsql['slowtime']     = 0.0050;                  // 0.0025 .. 0.0090 slow query time (for debugging)
 $custom_pgsql['transact']     = 'READ COMMITTED';        // Default Transaction Level: 'READ COMMITTED' | 'REPEATABLE READ' | 'SERIALIZABLE' | '' to leave it as default
 // sample usage:
 $pgsql = new SmartPgsqlExtDb($custom_pgsql);
 $pgsql->read_adata('SELECT * FROM "my_table" LIMIT 100 OFFSET 0');
 //... for other hints look to the samples of the class: SmartPgsqlDb::*

// #end php code

documentation generated on: 2023-10-19 23:15:47 +0000

final class \SmartPgsqlExtDb { } ->

class Methods

class Properties

class Constants

Sample code: PHP

final class \SmartPgsqlExtDb
{ } ->