Download the PHP package rawsrc/pdo-plus-plus without Composer

PDOPlusPlus : a new generation of PDO Wrapper

2022-11-04 PHP 8.0+ v.5.0.1

A PHP full object PDO Wrapper in one class

PDOPlusPlus (alias PPP) is a single class PDO Wrapper for PHP with a revolutionary fluid SQL syntax. You do not have anymore to use PDO in classical way, you can completely omit the notions of prepare(), bindValue(), bindParam(). The usage of these mechanisms is now hidden by PDOPlusPlus. All you have to do is to write directly a clean SQL query and inject directly your values.

The engine, will automatically escape the values and will let you concentrate only on the SQL syntax.

PDOPlusPlus is totally compliant with:

• INSERT
• UPDATE
• DELETE
• SELECT
• STORED PROCEDURE
• TRANSACTIONS (EVEN NESTED ONES)
• NATIVE SQL BIGINT (OR INT8) SIGNED/UNSIGNED SUPPORT

For stored procedures, you'll be able to use any IN, OUT or INOUT params.
PDOPlusPlus is also fully compatible with those returning multiple dataset at once.

BE CAREFUL: PDOPlusPlus DOESN'T VALIDATE ANY VALUE

A true Swiss knife for PDO.

INSTALLATION

THE CONCEPT

The power of PDOPlusPlus is directly linked to the way the instance is called as a function using the PHP magic function __invoke()
All you have to choose is the right injector that will take care, in a secure way, of the values to be injected into the SQL.

To cover all use cases, there's 6 different injectors:

• getInjectorIn(): injected values are directly escaped (plain sql). THIS IS THE DEFAULT INJECTOR
• getInjectorInByVal(): injected values are escaped using the PDOStatement->bindValue() mechanism
• getInjectorInByRef(): injected values are escaped using the PDOStatement->bindParam() mechanism
• getInjectorInAsRef(): values are passed by ref and directly escaped (plain sql)
• getInjectorOut(): for stored procedure with only OUT param
• getInjectorInOut(): for stored procedure with INOUT param, IN param is directly escaped (plain sql)

Please note that by default, PDOPlusPlus will escape your values in plain sql. If you want to have another behavior, like using a PDOStatement or calling a stored procedure, then you must use a specific injector.

CHANGELOG FROM VERSION 4.0

This version 5.0.x is a major update and may slightly break the compatibility with the code based on version 4.x

NEW FEATURES:

• Full support of BIGINT/INT8 data type (SIGNED/UNSIGNED)
• New injector: getInjectorInAsRef(): values are passed by ref and directly escaped (plain sql)
• Remove some aliases for float data types: double, num, numeric, only float remain available

REMOVED:

• Defining the final data type when creating an injector
• AbstractInjector class as its code was so simple that it was meaningful to directly implement it in each injector. So now PDOPlusPlus is truly a standalone class with no other dependency

The test code is now available. All tests are written for another of my projects: Exacodis, a minimalist testing engine for PHP

AUTO-RESET FEATURE

Previously, you had to create a new instance of PDOPlusPlus for each statement you wanted to execute. With the auto-reset feature (enabled by default) you can reuse the same instance of PDOPlusPlus as many times as necessary.

The auto-reset is automatically disabled just in 2 cases:

• if the statement fails
• if there's any by ref variable

In those cases, the instance keeps the data and the parameters that were defined.
You must force the reset of the instance using: $ppp->reset();

Everything is cleaned except save points in transactions which are reset with $ppp->releaseAll();

You can activate/deactivate this feature using:

• $ppp->setAutoResetOn()
• $ppp->setAutoResetOff()

ABOUT INJECTORS

The different allowed data types are : int str float bool binary bigint

Every injector is invocable with its own parameters.

• getInjectorIn(mixed $value, string $type = 'str')
• getInjectorInAsRef(mixed &$value, string $type = 'str')
• getInjectorInByVal(mixed $value, string $type = 'str')
• getInjectorInByRef(mixed &$value, string $type = 'str')
• getInjectorOut(string $out_tag)
• getInjectorInOut(mixed $value, string $inout_tag, string $type = 'str')

Note that binary and bigint data are types like others. Just internally the engine, the process is different.

Please have a look below how to use them in a SQL context.

CONNECTION TO THE DATABASE

As written, PDOPlusPlus is as PDO Wrapper, so it will have to connect to your database using PDO of course. You can declare as many connections profiles as necessary. Each connection has a unique id:

You can define the connection for the SQL you have to execute on the server when initializing a new instance $ppp = new PDOPlusPlus('user_root'); or $ppp = new PDOPlusPlus('user_test');,
If the id is omitted then the connection by default will be used. It is also possible to change the default connection's id once defined, see: $ppp->setDefaultConnection()

LET'S PLAY A LITTLE

For the course, I will use a very simple database of one table :

SAMPLE DATASET

ADD A RECORD

Let's add the first movie into the database using PDOPlusPlus:
I will use the SQL DIRECT mode omitting the PDOStatement step.

Let's add the second movie into the database using PDOPlusPlus:
I will use a PDOStatement based on values (->bindValue()).

Let's truncate the table and then add the whole list of films at once.
This time, I will use a PDOStatement based on references (->bindParam()) as there are many iterations to do. I will use the injector returned by ->injectorInByRef();.

Please note that the previous statement has "by ref" variables and the auto-reset is disabled in that case.

UPDATE A RECORD

So, to be able to reuse the same instance of PDOPlusPlus, we must clean it first.

DELETE A RECORD

SELECT A RECORD

If you need a more powerful way of extracting data from a query, there's a specific method selectStmt() that gives you access to the PDOStatement generated by the engine.

It is also possible to have a scrollable cursor (here you also have access to the PDOStatement created by the engine):

BOUND COLUMNS

Since v.4.0.0, it is possible to define bound columns as you'd do using PDOStatement->bindColumn(...). This is useful when you work especially with binary data.

This feature only works with $ppp->selectStmt() and $ppp->selectStmtAsScrollableCursor().

BIGINT OR INT8 COLUMN

Since v.5.0.0, the engine is fully compliant with the SQL BIGINT or INT8 (signed or unsigned) data type. Internally, the engine will always send a true bigint to the sql engine even if you have to manipulate them as strings in the PHP world. This is also true for
injectors using the PDO binding mechanism. The engine implements a workaround for these specific use cases, so it's transparent for the developer who has just to declare the type bigint for any injected value.

Because of integer core limits (PHP_INT_MIN and PHP_INT_MAX), you can't define a variable like $int = 18446744073709551600;, the PHP core will automatically cast the value to a float $int = 1.844674407371E+19. Before PDOPlusPlus, unless you consider them as string, it was quite impossible to use them easily in a PHP context whereas it was possible in the SQL world.

Remember, when you select a unsigned bigint column from the database, if the value is strictly greater than PHP_INT_MAX, then you will retrieve a string, otherwise a true integer. Generally, for signed bigint columns, the SQL limits match the PHP Core limits as usually both are running a x64 architecture.

STORED PROCEDURE

Because of having the possibility to extract many datasets at once or/and also passing multiple parameters IN, OUT or INOUT, most of the time you will have to use a specific value injector as shown below.

ONE DATASET

Let's create a SP that just return a simple dataset:

And now, call it:

TWO DATASET AT ONCE

Let's create a SP that just return a double dataset at once:

And now, call it:

ONE IN PARAM

Let's create a SP with one IN Param:

Chain directly the variables within the SQL as many as IN params you have to pass to the stored procedure.

ONE OUT PARAM

Let's create a SP with an OUT Param:

And call it using the specific injector for the OUT param:

Please note that all OUT values are always stored in the result array with the key out

ONE DATASET AND TWO OUT PARAMS

It is also possible to mix dataset and OUT param:

ONE INOUT PARAM WITH TWO OUT PARAMS

Finally, let's create a SP that use a mix between INOUT and OUT params:

And call it using the specific injectors: one for INOUT and another one for OUT params.
Please be careful with the syntax for the INOUT injector.

TRANSACTIONS

PDO++ is fully compatible with the RDBS transaction mechanism.
You have several methods that will help you to manage your SQL code flow:

• setTransaction() to define the execution context of the transaction to come
• startTransaction()
• commit()
• rollback() that will just rollback to the last save point
• rollbackTo() that will just rollback to the given save point
• rollbackAll() that will rollback to the beginning
• savePoint() to create a new save point (a marker inside a flow of SQL code)
• release() to remove a save point
• releaseAll() to remove all save points

If you're familiar with the SQL transactions theory, the functions are well named and easy to understand.

Please note, that when you start a transaction, the engine disable the database AUTOCOMMIT parameter, that way, all sql statements will be saved at once on $ppp->commit();.

ERRORS

To avoid plenty of try { } catch { } blocks, I introduced a mechanism that will factorize this part of code.
As PDOPlusPlus can throw an Exception when a statement fails, you should always intercept that possible issue and use everywhere in your code a try { } catch { } block. It's pretty heavy, isn't it ?

Now you can define a closure that will embed the treatment of the exception. At the beginning, you just have to define once a unique closure that will receive and treat the thrown Exception by PDOPlusPlus

Then you can activate/deactivate this feature using:

• $ppp->setThrowOn();
• $ppp->setThrowOff();

In case of problem and if the throwing is deactivated, PDOPlusPlus will intercept as usual the Exception and will pass it to your closure. In taht case, the method will return null.

Suppose this code produces an error:

using the mechanism of exception wrapper, you can simply do:

CONCLUSION

Hope this will help you to produce in a more comfortable way a better SQL code and use PDO natively in your PHP code.

Ok guys, that's all folks. Enjoy !

rawsrc