Class: SmartPgsqlDb - provides a Static PostgreSQL DB Server Client that can be used just with the DEFAULT connection from configs.
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 / 14.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 / 4.2.x
Tested and Stable with PgBouncer: all versions
This class provides an easy and convenient way to work with the PostgreSQL DEFAULT connection, as all methods are static.
It can be used just with the DEFAULT connection which must be set in etc/config.php: $configs['pgsql'].
It connects automatically, when needed (the connection is lazy, and is made just when is needed to avoid permanent connections to PgSQL which slower down the app and takes busy the slots).
NOTICE: You should never modify the (optional) connection parameter which should always have the value of (string) 'DEFAULT' for this static class to work.
Actually you should not use this parameter at all as it is optional ... This parameter is reserved for advanced usage to implement derived classes like SmartPgsqlExtDb !
Language: PHP
Located at: lib/framework/plugins/lib_db_pgsql.php
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: static object: Class::method() - This class provides only STATIC methods
class Methods
public staticfunctiondefault_connect (
) {} ::
Pre-connects manually to the Default PostgreSQL Server.
This function is OPTIONAL as the connection on the DEFAULT PostgreSQL Server will be done automatically when needed.
Anyway, if there is a need to create an explicit connection to the DEFAULT PostgreSQL server earlier, this function can be used by example in App Bootstrap.
public staticfunctionquote_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: % _ and \
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 staticfunctionquote_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'
@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
@param: {RESOURCE} $y_connection: the connection
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.
@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
@param: {RESOURCE} $y_connection: the connection
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().
@return: {STRING} The Escaped Identifier as: "field" / "table"
@param: {STRING} $y_identifier: The Identifier to be Escaped: field / table
@param: {RESOURCE} $y_connection: the connection
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.
@return: {0/1} 1 if exists ; 0 if not
@param: {STRING} $y_table: The Table Name
@param: {STRING} $y_schema: The Schema Name
@param: {RESOURCE} $y_connection: The connection to PgSQL server
@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
@param: {RESOURCE} $y_connection: the connection
PgSQL Query :: Count
This function is intended to be used for count type queries: SELECT COUNT().
@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
@param: {RESOURCE} $y_connection: the connection
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.
@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
@param: {RESOURCE} $y_connection: the connection
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 ...
@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
@param: {RESOURCE} $y_connection: the connection
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.
@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'
@param: {RESOURCE} $y_connection: the connection to pgsql server
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
@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');
@param: {RESOURCE} $y_connection: the connection to pgsql server
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 !
@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
@param: {RESOURCE} $y_connection: pgsql connection
Get A UNIQUE (SAFE) ID for DB Tables / Schema
class Properties
class Constants
Sample code: PHP
<?php
// The connection to the DEFAULT PostgreSQL Server will be done automatically, when needed, using the config parameters ; but if you want to pre-connect, use SmartPgsqlDb::default_connect() ... $count = (int) SmartPgsqlDb::count_data('SELECT COUNT("id") FROM "table" WHERE ("active" = \''.SmartPgsqlDb::escape_str('some-id').'\')'); $non_associative_read_multi_records = (array) SmartPgsqlDb::read_data('SELECT * FROM "table" WHERE "id" = '.SmartPgsqlDb::escape_literal(3)); $associative_read_multi_records = (array) SmartPgsqlDb::read_adata('SELECT * FROM "table" WHERE "id" = $1', array('other-id')); $associative_read_for_just_one_record = (array) SmartPgsqlDb::read_asdata('SELECT * FROM "table" WHERE "id" = $1 LIMIT 1 OFFSET 0', array(99)); // NOTICE: this function will return just one record, so always use LIMIT 1 OFFSET 0 (or LIMIT 0,1) ; if the query will return more records will raise an error $update = (array) SmartPgsqlDb::write_data('UPDATE "table" SET "active" = 1 WHERE "id" = $1', array(55)); // will return an array[ 0 => message, 1 => (integer) affected rows ] $arr_insert = array( 'id' => 100, 'active' => 1, 'name' => 'Test Record' ); $insert = (array) SmartPgsqlDb::write_data('INSERT INTO "table" '.SmartPgsqlDb::prepare_statement($arr_insert, 'insert')); $prepared_sql = SmartPgsqlDb::prepare_param_query('SELECT * FROM "table" WHERE "id" = $1', [99]);
// #end php code
documentation generated on: 2023-10-19 23:15:47 +0000
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 / 14.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 / 4.2.x
Tested and Stable with PgBouncer: all versions
This class provides an easy and convenient way to work with the PostgreSQL DEFAULT connection, as all methods are static.
It can be used just with the DEFAULT connection which must be set in etc/config.php: $configs['pgsql'].
It connects automatically, when needed (the connection is lazy, and is made just when is needed to avoid permanent connections to PgSQL which slower down the app and takes busy the slots).
NOTICE: You should never modify the (optional) connection parameter which should always have the value of (string) 'DEFAULT' for this static class to work.
Actually you should not use this parameter at all as it is optional ... This parameter is reserved for advanced usage to implement derived classes like SmartPgsqlExtDb !