Streamline doctrine/pdo/oci

[kesselb]

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 (

   server/lib/public/DB/QueryBuilder/IQueryBuilder.php

   Lines 42 to 61 in 0109249

   	/**
   	* @since 9.0.0
   	*/
   	public const PARAM_NULL = \PDO::PARAM_NULL;
   	/**
   	* @since 9.0.0
   	*/
   	public const PARAM_BOOL = \PDO::PARAM_BOOL;
   	/**
   	* @since 9.0.0
   	*/
   	public const PARAM_INT = \PDO::PARAM_INT;
   	/**
   	* @since 9.0.0
   	*/
   	public const PARAM_STR = \PDO::PARAM_STR;
   	/**
   	* @since 9.0.0
   	*/
   	public const PARAM_LOB = \PDO::PARAM_LOB;

   ) are mapped to pdo types.

2. But for oci we are not using pdo but the (recommended) native driver:

   server/lib/private/DB/ConnectionFactory.php

   Lines 61 to 66 in 0109249

   	'oci' => [
   	'adapter' => AdapterOCI8::class,
   	'charset' => 'AL32UTF8',
   	'driver' => 'oci8',
   	'wrapperClass' => OracleConnection::class,
   	],

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