Streamline doctrine/pdo/oci

Describe the task

We discovered some weirdness with our groupware apps on oracle db.

Problem: Booleans are not properly stored as 0/1 but null/false. We need some hacks in our code to make it work in properly (e.g. the explicit check for true).

Example SQL

Example Code

Findings:

1. Our type definitions (https://github.com/nextcloud/server/blob/010924916c74e62089131ff9f62b8b0e96ff539b/lib/public/DB/QueryBuilder/IQueryBuilder.php#L42-L61) are mapped to pdo types.

2. But for oci we are not using pdo but the (recommended) native driver: https://github.com/nextcloud/server/blob/010924916c74e62089131ff9f62b8b0e96ff539b/lib/private/DB/ConnectionFactory.php#L61-L66

3. On read/write something to the database via doctrine/dbal we reach getBindingInfo (https://github.com/nextcloud/3rdparty/blob/2ae1a1d6f688ae8394d6559ee673fecbee975db4/doctrine/dbal/src/Connection.php#L1681-L1695). Example for a boolean value: getBindingInfo($value = true, $type = 5). 5 is not a string and not an instance of Type so type is changed to ParameterType::STRING. If we pass Types::BOOLEAN (or an instance of Type) doctrine would convert the value to the right value for the given platform (https://github.com/nextcloud/3rdparty/blob/2ae1a1d6f688ae8394d6559ee673fecbee975db4/doctrine/dbal/src/Platforms/AbstractPlatform.php#L2623-L2652).

4. Finally we end up in 3rdparty/doctrine/dbal/src/Driver/OCI8/Statement.php (https://github.com/nextcloud/3rdparty/blob/2ae1a1d6f688ae8394d6559ee673fecbee975db4/doctrine/dbal/src/Driver/OCI8/Statement.php#L108-L114) with $variable = false, $type = 5, $this->convertParameterType($type) = 1). I assume that oci driver or oracle db then fallback to some default logic as the provided input is invalid (field is number(1), value is false and type string).

Solutions

1. Update IQueryBuilder to use the type definitions from doctrine/dbal (probably via OCP\DB\Types). Risk is that apps (especially for oci compatibility) depends on the current (broken) behavior. In addition the type conversion from doctrine/dbal might be different than pdo's (this could affect all databases).

2. Check https://www.php.net/manual/en/function.oci-bind-by-name (section type). SQLT_BOL or OCI_B_BOL - for PL/SQL BOOLEANs (Requires OCI8 2.0.7 and Oracle Database 12c) it seems that newer versions support booleans. I don't see any code in doctrine/dbal to map pdo boolean to oracle boolean (this should be done in convertParameterType). If that help's we could prepare an upstream patch for doctrine/dbal. We should consider to use a newer oracle db for our ci (e.g. https://github.com/gvenzl/oci-oracle-xe) because our version 11.2.0.2.0 is old (probably eol already https://support.oracle.com/knowledge/Oracle%20Cloud/2068368_1.html / https://support.oracle.com/knowledge/Oracle%20Cloud/2761482_1.html).

Affected components

Calendar, Calendar Resource Management, Contacts, Mail

To do

• More debugging

Start with lib/public/DB/QueryBuilder/IQueryBuilder.php and lib/private/DB/ConnectionFactory.php, then trace boolean binding through doctrine/dbal/src/Connection.php to 3rdparty/doctrine/dbal/src/Driver/OCI8/Statement.php. Reproduce the Oracle boolean storage behavior and determine which proposed mapping or upstream fix is appropriate. Done means an agreed fix is implemented and the Oracle behavior is verified.