Merge pull request #47329 from nextcloud/feat/add-datetime-qbmapper-support

feat(AppFramework): Add full support for date / time / datetime columns

5 files changed, 198 insertions, 35 deletions

diff --git a/lib/public/AppFramework/Db/Entity.php b/lib/public/AppFramework/Db/Entity.php
index f37107ac128..cd15df134f1 100644
--- a/lib/public/AppFramework/Db/Entity.php
+++ b/lib/public/AppFramework/Db/Entity.php
@@ -7,13 +7,14 @@
  */
 namespace OCP\AppFramework\Db;
 
+use OCP\DB\Types;
+
 use function lcfirst;
 use function substr;
 
 /**
  * @method int getId()
  * @method void setId(int $id)
- * @psalm-type AllowedTypes = 'json'|'blob'|'datetime'|'string'|'int'|'integer'|'bool'|'boolean'|'float'|'double'|'array'|'object'
  * @since 7.0.0
  * @psalm-consistent-constructor
  */
@@ -24,7 +25,7 @@ abstract class Entity {
 	public $id;
 
 	private array $_updatedFields = [];
-	/** @var array<string, AllowedTypes> */
+	/** @var array<string, \OCP\DB\Types::*> */
 	private array $_fieldTypes = ['id' => 'integer'];
 
 	/**
@@ -65,7 +66,7 @@ abstract class Entity {
 
 
 	/**
-	 * @return array<string, AllowedTypes> with attribute and type
+	 * @return array<string, \OCP\DB\Types::*> with attribute and type
 	 * @since 7.0.0
 	 */
 	public function getFieldTypes(): array {
@@ -102,33 +103,38 @@ abstract class Entity {
 		// if type definition exists, cast to correct type
 		if ($args[0] !== null && array_key_exists($name, $this->_fieldTypes)) {
 			$type = $this->_fieldTypes[$name];
-			if ($type === 'blob') {
+			if ($type === Types::BLOB) {
 				// (B)LOB is treated as string when we read from the DB
 				if (is_resource($args[0])) {
 					$args[0] = stream_get_contents($args[0]);
 				}
-				$type = 'string';
+				$type = Types::STRING;
 			}
 
-			if ($type === 'datetime') {
-				if (!$args[0] instanceof \DateTime) {
-					$args[0] = new \DateTime($args[0]);
-				}
-			} elseif ($type === 'json') {
-				if (!is_array($args[0])) {
-					$args[0] = json_decode($args[0], true);
-				}
-			} else {
-				$args[0] = match($type) {
-					'string' => (string)$args[0],
-					'bool', 'boolean', => (bool)$args[0],
-					'int', 'integer', => (int)$args[0],
-					'float' => (float)$args[0],
-					'double' => (float)$args[0],
-					'array' => (array)$args[0],
-					'object' => (object)$args[0],
-					default => new \InvalidArgumentException()
-				};
+			switch ($type) {
+				case Types::TIME:
+				case Types::DATE:
+				case Types::DATETIME:
+				case Types::DATETIME_TZ:
+					if (!$args[0] instanceof \DateTime) {
+						$args[0] = new \DateTime($args[0]);
+					}
+					break;
+				case Types::TIME_IMMUTABLE:
+				case Types::DATE_IMMUTABLE:
+				case Types::DATETIME_IMMUTABLE:
+				case Types::DATETIME_TZ_IMMUTABLE:
+					if (!$args[0] instanceof \DateTimeImmutable) {
+						$args[0] = new \DateTimeImmutable($args[0]);
+					}
+					break;
+				case Types::JSON:
+					if (!is_array($args[0])) {
+						$args[0] = json_decode($args[0], true);
+					}
+					break;
+				default:
+					settype($args[0], $type);
 			}
 		}
 		$this->$name = $args[0];
@@ -253,7 +259,7 @@ abstract class Entity {
 	 * that value once its being returned from the database
 	 *
 	 * @param string $fieldName the name of the attribute
-	 * @param AllowedTypes $type the type which will be used to match a cast
+	 * @param \OCP\DB\Types::* $type the type which will be used to match a cast
 	 * @since 7.0.0
 	 */
 	protected function addType(string $fieldName, string $type): void {
diff --git a/lib/public/AppFramework/Db/QBMapper.php b/lib/public/AppFramework/Db/QBMapper.php
index ef4516221e6..243310fe933 100644
--- a/lib/public/AppFramework/Db/QBMapper.php
+++ b/lib/public/AppFramework/Db/QBMapper.php
@@ -10,6 +10,7 @@ namespace OCP\AppFramework\Db;
 use Generator;
 use OCP\DB\Exception;
 use OCP\DB\QueryBuilder\IQueryBuilder;
+use OCP\DB\Types;
 use OCP\IDBConnection;
 
 /**
@@ -218,18 +219,33 @@ abstract class QBMapper {
 
 		switch ($types[ $property ]) {
 			case 'int':
-			case 'integer':
+			case Types::INTEGER:
+			case Types::SMALLINT:
 				return IQueryBuilder::PARAM_INT;
-			case 'string':
+			case Types::STRING:
 				return IQueryBuilder::PARAM_STR;
 			case 'bool':
-			case 'boolean':
+			case Types::BOOLEAN:
 				return IQueryBuilder::PARAM_BOOL;
-			case 'blob':
+			case Types::BLOB:
 				return IQueryBuilder::PARAM_LOB;
-			case 'datetime':
-				return IQueryBuilder::PARAM_DATE;
-			case 'json':
+			case Types::DATE:
+				return IQueryBuilder::PARAM_DATETIME_MUTABLE;
+			case Types::DATETIME:
+				return IQueryBuilder::PARAM_DATETIME_MUTABLE;
+			case Types::DATETIME_TZ:
+				return IQueryBuilder::PARAM_DATETIME_TZ_MUTABLE;
+			case Types::DATE_IMMUTABLE:
+				return IQueryBuilder::PARAM_DATE_IMMUTABLE;
+			case Types::DATETIME_IMMUTABLE:
+				return IQueryBuilder::PARAM_DATETIME_IMMUTABLE;
+			case Types::DATETIME_TZ_IMMUTABLE:
+				return IQueryBuilder::PARAM_DATETIME_TZ_IMMUTABLE;
+			case Types::TIME:
+				return IQueryBuilder::PARAM_TIME_MUTABLE;
+			case Types::TIME_IMMUTABLE:
+				return IQueryBuilder::PARAM_TIME_IMMUTABLE;
+			case Types::JSON:
 				return IQueryBuilder::PARAM_JSON;
 		}
 
diff --git a/lib/public/DB/QueryBuilder/IQueryBuilder.php b/lib/public/DB/QueryBuilder/IQueryBuilder.php
index 72b2ccbecff..4794e7e8877 100644
--- a/lib/public/DB/QueryBuilder/IQueryBuilder.php
+++ b/lib/public/DB/QueryBuilder/IQueryBuilder.php
@@ -42,10 +42,60 @@ interface IQueryBuilder {
 	 * @since 9.0.0
 	 */
 	public const PARAM_LOB = ParameterType::LARGE_OBJECT;
+
+	/**
+	 * @since 9.0.0
+	 * @deprecated 31.0.0 - use PARAM_DATETIME_MUTABLE instead
+	 */
+	public const PARAM_DATE = Types::DATETIME_MUTABLE;
+
+	/**
+	 * For passing a \DateTime instance when only interested in the time part (without timezone support)
+	 * @since 31.0.0
+	 */
+	public const PARAM_TIME_MUTABLE = Types::TIME_MUTABLE;
+
+	/**
+	 * For passing a \DateTime instance when only interested in the date part (without timezone support)
+	 * @since 31.0.0
+	 */
+	public const PARAM_DATE_MUTABLE = Types::DATE_MUTABLE;
+
+	/**
+	 * For passing a \DateTime instance (without timezone support)
+	 * @since 31.0.0
+	 */
+	public const PARAM_DATETIME_MUTABLE = Types::DATETIME_MUTABLE;
+
+	/**
+	 * For passing a \DateTime instance with timezone support
+	 * @since 31.0.0
+	 */
+	public const PARAM_DATETIME_TZ_MUTABLE = Types::DATETIMETZ_MUTABLE;
+
+	/**
+	 * For passing a \DateTimeImmutable instance when only interested in the time part (without timezone support)
+	 * @since 31.0.0
+	 */
+	public const PARAM_TIME_IMMUTABLE = Types::TIME_MUTABLE;
+
 	/**
+	 * For passing a \DateTime instance when only interested in the date part (without timezone support)
 	 * @since 9.0.0
 	 */
-	public const PARAM_DATE = 'datetime';
+	public const PARAM_DATE_IMMUTABLE = Types::DATE_IMMUTABLE;
+
+	/**
+	 * For passing a \DateTime instance (without timezone support)
+	 * @since 31.0.0
+	 */
+	public const PARAM_DATETIME_IMMUTABLE = Types::DATETIME_IMMUTABLE;
+
+	/**
+	 * For passing a \DateTime instance with timezone support
+	 * @since 31.0.0
+	 */
+	public const PARAM_DATETIME_TZ_IMMUTABLE = Types::DATETIMETZ_IMMUTABLE;
 
 	/**
 	 * @since 24.0.0
diff --git a/lib/public/DB/Types.php b/lib/public/DB/Types.php
index 414d81a24c8..969ec5e6611 100644
--- a/lib/public/DB/Types.php
+++ b/lib/public/DB/Types.php
@@ -41,18 +41,77 @@ final class Types {
 	public const BOOLEAN = 'boolean';
 
 	/**
+	 * A datetime instance with only the date set.
+	 * This will be (de)serialized into a \DateTime instance,
+	 * it is recommended to instead use the `DATE_IMMUTABLE` instead.
+	 *
+	 * Warning: When deserialized the timezone will be set to UTC.
 	 * @var string
 	 * @since 21.0.0
 	 */
 	public const DATE = 'date';
 
 	/**
+	 * An immutable datetime instance with only the date set.
+	 * This will be (de)serialized into a \DateTimeImmutable instance,
+	 * It is recommended to use this over the `DATE` type because
+	 * out `Entity` class works detecting changes through the setter,
+	 * changes on mutable objects can not be detected.
+	 *
+	 * Warning: When deserialized the timezone will be set to UTC.
+	 * @var string
+	 * @since 31.0.0
+	 */
+	public const DATE_IMMUTABLE = 'date_immutable';
+
+	/**
+	 * A datetime instance with date and time support.
+	 * This will be (de)serialized into a \DateTime instance,
+	 * it is recommended to instead use the `DATETIME_IMMUTABLE` instead.
+	 *
+	 * Warning: When deserialized the timezone will be set to UTC.
 	 * @var string
 	 * @since 21.0.0
 	 */
 	public const DATETIME = 'datetime';
 
 	/**
+	 * An immutable datetime instance with date and time set.
+	 * This will be (de)serialized into a \DateTimeImmutable instance,
+	 * It is recommended to use this over the `DATETIME` type because
+	 * out `Entity` class works detecting changes through the setter,
+	 * changes on mutable objects can not be detected.
+	 *
+	 * Warning: When deserialized the timezone will be set to UTC.
+	 * @var string
+	 * @since 31.0.0
+	 */
+	public const DATETIME_IMMUTABLE = 'datetime_immutable';
+
+
+	/**
+	 * A datetime instance with timezone support
+	 * This will be (de)serialized into a \DateTime instance,
+	 * it is recommended to instead use the `DATETIME_TZ_IMMUTABLE` instead.
+	 *
+	 * @var string
+	 * @since 31.0.0
+	 */
+	public const DATETIME_TZ = 'datetimetz';
+
+	/**
+	 * An immutable timezone aware datetime instance with date and time set.
+	 * This will be (de)serialized into a \DateTimeImmutable instance,
+	 * It is recommended to use this over the `DATETIME_TZ` type because
+	 * out `Entity` class works detecting changes through the setter,
+	 * changes on mutable objects can not be detected.
+	 *
+	 * @var string
+	 * @since 31.0.0
+	 */
+	public const DATETIME_TZ_IMMUTABLE = 'datetimetz_immutable';
+
+	/**
 	 * @var string
 	 * @since 21.0.0
 	 */
@@ -89,12 +148,30 @@ final class Types {
 	public const TEXT = 'text';
 
 	/**
+	 * A datetime instance with only the time set.
+	 * This will be (de)serialized into a \DateTime instance,
+	 * it is recommended to instead use the `TIME_IMMUTABLE` instead.
+	 *
+	 * Warning: When deserialized the timezone will be set to UTC.
 	 * @var string
 	 * @since 21.0.0
 	 */
 	public const TIME = 'time';
 
 	/**
+	 * A datetime instance with only the time set.
+	 * This will be (de)serialized into a \DateTime instance.
+	 *
+	 * It is recommended to use this over the `DATETIME_TZ` type because
+	 * out `Entity` class works detecting changes through the setter,
+	 * changes on mutable objects can not be detected.
+	 *
+	 * @var string
+	 * @since 31.0.0
+	 */
+	public const TIME_IMMUTABLE = 'time_immutable';
+
+	/**
 	 * @var string
 	 * @since 24.0.0
 	 */
diff --git a/lib/public/Migration/Attributes/ColumnType.php b/lib/public/Migration/Attributes/ColumnType.php
index 23445e822b6..ab6044e3ae2 100644
--- a/lib/public/Migration/Attributes/ColumnType.php
+++ b/lib/public/Migration/Attributes/ColumnType.php
@@ -23,10 +23,24 @@ enum ColumnType : string {
 	case BLOB = 'blob';
 	/** @since 30.0.0 */
 	case BOOLEAN = 'boolean';
-	/** @since 30.0.0 */
+	/**
+	 * A column created with `DATE` can be used for both `DATE` and `DATE_IMMUTABLE`
+	 * on the `\OCP\AppFramework\Db\Entity`.
+	 * @since 30.0.0
+	 */
 	case DATE = 'date';
-	/** @since 30.0.0 */
+	/**
+	 * A column created with `DATETIME` can be used for both `DATETIME` and `DATETIME_IMMUTABLE`
+	 * on the `\OCP\AppFramework\Db\Entity`.
+	 * @since 30.0.0
+	 */
 	case DATETIME = 'datetime';
+	/**
+	 * A column created with `DATETIME_TZ` can be used for both `DATETIME_TZ` and `DATETIME_TZ_IMMUTABLE`
+	 * on the `\OCP\AppFramework\Db\Entity`.
+	 * @since 31.0.0
+	 */
+	case DATETIME_TZ = 'datetimetz';
 	/** @since 30.0.0 */
 	case DECIMAL = 'decimal';
 	/** @since 30.0.0 */