PostgresDb

This project is a PostgreSQL version of ThingEngineer's MysqliDb Class, that supports the basic functionality and syntax provided by said class, tailored specifically to PostgreSQL.

Installation

This class requires PHP 5.4 or 7+ to work. You can either place PostgresDb.php in your project and require/include it, or use Composer (recommended)

composer require seinopsys/postgresql-database-class:^2.0

Usage

require "PostgresDb.php";
$Database = new PostgresDb($database_name, $host, $username, $password);

For a more in-depth description see USAGE.md

Upgrading from 1.x

1. where() and arrays

   From 2.0 onwards the following syntax for the where method has been removed.

   $Database->where('id', ['>=' => 1]); // WHERE id >= 1

   The new behavior expects a 1-dimensional array of values to use with an IN clause. The keys are ignored.

   $Database->where('id', [1, 2, 3]); // WHERE id IN (1, 2, 3)
   $Database->where('id', ['>=' => 1]); // WHERE id IN (1)

   This removes the ability to use an array to specify a comparison operator and a value at the same time. Instead, use the 3rd parameter to specify an operator other than =, like this:

   $Database->where('id', 1, '<=');

2. orderBy() default direction

   Since the default in PostgreSQL is ASC I've updated the default sort order of said method to reflect that.

   You should check for any usages of the orderBy() method where the second parameter is not set, and set it to the old default: DESC.

3. Column name transformation changes

   Starting with version 2.0 column names are escaped/handled differently. The PostgreSQL docs state that all column names are lowercase by default when unquoted, but when quoted the casing should be preserved. The old version of the library converted column names to lowercase in some cases but not in others, leading to inconsistencies.

   A uniform escape function has been introduced internally for column names which causes anything that doesn't fit the unquoted restrictions (keywords & uppercase/invalid characters) will cause the column names to be quoted. Check the examples below to see if you need to make any changes based on how you refer to database columns in your code.

   Database column	Referred to as (in code)	Code change
   column_name	column_name	✔️ No action needed These can safely be used as-is
   order user select	order user select	✔️ No action needed Keywords are automatically quoted
   áccénts	áccénts	✔️ No action needed They will still be quoted, but it should not affect the behavior
   sp3cǐaŧ_¢h4r$	sp3cǐaŧ_¢h4r$	✔️ No action needed But why?
   column_name áccénts_ümläut_ëtc	Column_Name Áccénts_Ümläut_Ëtc	✖️ Use lowercase letters The changes will now cause your column name to become quoted and refer to a column that does not exist.
   Column_Name Áccénts	column_name áccénts	✖️ Use Column_Name/Áccénts instead Column names with uppercase letters can only ever be accessed if the name is double-quoted. The changes will cause valid lowercase column names to be left unquoted, breaking this approach (if it even worked in the first place).

4. Remove defunct delete() $numRows argument

   This method previously accepted an array as it second parameter which it passed along to the LIMIT query generator, but since this syntax is not supported by PostgreSQL this argument has been removed & replaced with the $returnColumn argument

5. query() removal, rawQuery()+rawQuerySingle() rename & behavior change

   The query() method was ambiguous and there was no point in keeping it around in its original form, so the rawQuery*() methods have dropped the raw prefix and are now simply called query() and querySingle(). The old method names have been tagged with @deprecated and will now emit E_USER_DEPRECATED errors when run.