internals:pdo:brainstorming

Thoughts and ideas about improving PDO. Wez's PDOv1 spec could be useful: http://www.php.net/~wez/pdo/pdo-spec.html

Relevant RFC

Questions

  • What have we learned about the PDO architecture in the past years (that needs fixing)?
  • How do we deal with features that only related to one (or few) drivers? What if its impossible to fit a similar feature under a single API? Currently we have a few driver specific attributes (like the unbuffered query attribute for MySQL).
  • How do we deal with different error handling? Like some RDBMS create throw errors during the prepare stage, others at the first execute.
  • How do we deal with different behavior like transaction errors causing a rollback on some RDBMS but not on others.
  • How to deal with missing functionality available inside the native extensions? Is the goal to eventually replace the native extensions? Is there any chance to share connections between PDO and the native extensions?
  • How do we handle cross connection transactions? ( 2 phase commit across multiple databases - need to add more information later )

PDO Bugs / PDO2 Improvements

PDO

Documentation

  • FETCH_SERIALIZE description is wrong, it works like FETCH_CLASS not FETCH_INTO
  • document finally remaining FETCH constants like FETCH_KEY_VALUE, FETCH_FUNC
  • better document fetchAll() constants
  • add support table for ATTR_FETCH_TABLE_NAMES (sqlite still has a pending patch)
  • ATTR_FETCH_CATALOG_NAMES unused?
  • FETCH_FUNC should mention it is only allowed with fetchAll
  • Mention PDOStatement can be used in foreach, but won't call an overloaded fetch as the internal iterator is used.

Parameter binding & explicit data type set

  • bindValue() & bindParam() should enforce the type giving, currently it uses the current zval type for MySQL Bug bug #44639 / PDO: Fixing bug #44639 for mysql
    • PARAM_INT with strings ⇒ it should try to convert to the type, otherwise throw an Exception or create an error.
    • PARAM_BOOLEAN with strings ⇒ Exception or Error.
    • a new PARAM_AUTO could be used to determine the type by zval (old mysql behavior)
  • execute() could use an automatic approach PARAM_AUTO, could be a BC break http://news.php.net/php.pdo/235
    • allows automatic convert to PARAM_INT if zval is integer / float
  • prepared statement emulation (placeholder rewriting) is fragile, cannot deal well with strings that look like placeholders inside quoted strings like “WHERE foo = 'check ?'” or serialized data
  • different behavior when reusing the same oracle style named placeholder multiple times

fetchObject

  • Bug#49521 “PDO fetchObject sets values before calling constructor”
    • add note to FETCH_PROPS_LATE
    • allow setting FETCH_PROPS_LATE or assume it as default?

Unified API meta data access

see http://news.php.net/php.pdo/233

  • PDOStatement::getColumnMeta with a new parameter to get only one meta type back ml#123127892009423 wouldn't break the current api.
    • META_NAME ⇒ column name
    • META_TABLENAME ⇒ table name

Asynchronous requests

see http://news.php.net/php.pdo/268

For features not yet finalized: see http://news.php.net/php.pdo/286 PDO_Driver::getNative()::asyncQuery();

Most drivers allow some kind of async query except MySQL, additional connections could be created to allow concurrent queries.

see http://news.php.net/php.pdo/330

PDO Persistent Connections

see http://news.php.net/php.pdo/318 see http://news.php.net/php.pdo/319

  • Defaults to be portable as much as possible
  • Allow to disable certain connection reset/cleanup features to gain even more speed.

PDO2

fetchAll

  • add fetchAll() FETCH_UNIQUE without FETCH_GROUP (PHP5.3 seems to support it already?)
    • allow the column id to be set
    • allow to receive the key column in the result
    • allow with FETCH_CLASS

XML Support

Transaction

PDO transaction object see http://news.php.net/php.pdo/322

Possible problems:

  • error handling
  • nested transaction (emulation)

Parameter Binding without Preparing

Allow prepareless binding of parameters for queries, like PostgreSQL PQexecParams.

  • queryParam() ?

PDO Binding behaviour (5.3)

MySQL SQLite Firebird ODBC PostgreSQL
PDO_PARAM_STR zval type convert_to_string/null zval type/null convert_to_string/ convert_to_string
PDO_PARAM_INT convert_to_long/null convert_to_string convert_to_string
PDO_PARAM_BOOL convert_to_long/null convert_to_string convert_to_string
PDO_PARAM_NULL always null always null if zval IS_NULL
PDO_PARAM_LOB “ * convert_to_string*/null convert_to_string* convert_to_string *
stream support * Supports streams * Supports streams * Supports streams * Supports streams

mysql:

firebird: Check if field allows null, for empty strings and field type is SQL_SHORT, SQL_LONG, SQL_INT64, SQL_FLOAT, SQL_DOUBLE, SQL_TIMESTAMP, SQL_TYPE_DATE, SQL_TYPE_TIME null is assumed as input

odbc: zval == IS_NULL or PDO_PARAM_NULL ⇒ always SQL_NULL_DATA

PostgreSQL: zval == IS_BOOL ⇒ always bool except if set to PDO_PARAM_NULL (Does reference check, and zval separation before convert_to_string if it isn't a reference)

PDO Binding behaviour RFC

for all bindValue/bindParam/execute input

Why do we need improvements to binding?

Currently has no standard or agreement how parameters like PDO_PARAM_BOOL should be handled on the user side or the internal PDO drivers. There are even disagreements between PDOs code comment, explaining PDO_PARAM_INT is the internal PHP type, and the manual telling us it's the database native type. The result is there is no agreement how even simple parameters should be bound properly and so each driver does it's own guessing how parameters are handled.

The current situation

There are two basic categories how currently driver handle bindings: Trying to guess the right meaning by the zval type (even if PDO is told to use a certain type) Or try to convert it to string and let the database handle most stuff.

So each driver needs some special care when binding, generally for PDO mysql you need to cast to the right type as setting the binding type constant has no affect. The PDO core already convert doubles to string in the pdo_stmt.c really_register_bound_param function. The current code may or may not separate the zval, so the contents of the variable may change after a successful binding or executing of a statement.

Proposal

PDO should enforce the zval type in really_register_bound_param so drivers will get a pre-filtered parameters

This would mean: PDO_PARAM_* will map to internal PHP types, as the code comments in the enum pdo_param_type already tells us. PDO_PARAM_ZVAL will be introduced to user space to let zvals pass directly.

If the zval type isn't right, PDO should do a zval separation before converting so no user space variables will be altered.

PDO_PARAM_DOUBLE should be introduced later to complete the current support.

Problems

PostgreSQL: Currently it's possible to pass a string like “NO” with PDO::PARAM_BOOL, the string contents will be evaluated by the pdo driver, so “no” gets the equivalent of zend false.

As soon as PDO Core enforces types and simply use convert_to_boolean bindValue(':foo', 'NO', PDO::PARAM_BOOL) would result into zend true, passed to pdo postgre it would store true!

But as bindValue defaults to PDO::PARAM_STR and so currently with PostgreSQL you don't need PDO::PARAM_BOOL the impact on current software shouldn't be big.

Workarounds

Possible workaround: A special convert_to_boolean function to support SQL FALSE and NO as zend false, however this would be unnatural to PHP core.

Matrix

zval/PDO PDO_PARAM_NULL PDO_PARAM_BOOL PDO_PARAM_INT PDO_PARAM_STR PDO_PARAM_LOB PDO_PARAM_AUTO (new)
IS_NULL - no convert - SQL_NULL SQL_NULL SQL_NULL SQL_NULL SQL_NULL
IS_LONG SQL_NULL convert_to_boolean - no convert - convert_to_string convert_to_string zval type
IS_DOUBLE SQL_NULL convert_to_boolean - no convert - convert_to_string convert_to_string zval type
IS_BOOL SQL_NULL - no convert - convert_to_long convert_to_string convert_to_string zval type
IS_ARRAY Exception Exception (2) Exception (2) Exception (2) Exception Exception/zval type(2)
IS_OBJECT Exception Exception (1) Exception (1) _toString or Exception _toString or Exception Exception
IS_STRING empty ⇒ Null Exception convert_numeric or Exception - no convert - - no convert - zval type
IS_RESSOURCE Exception Exception Exception? convert_to_string(3) convert_to_string(3) convert_to_string(3)

(1) there is no _toType yet

(2) An Array of PDO_PARAM_AUTO could be added for Databases allowing Arrays? PostgreSQL?

(3) Allowing streams

For compatibility the execute() function could be changed to PDO_PARAM_ZVAL for less BC breaks on certain drivers.

internals/pdo/brainstorming.txt · Last modified: 2017/09/22 13:28 (external edit)