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

This adds support for all Doctrine supported types, for the column types only the immutable variants needed to be added. But especially those types are the important ones, as our **Entity** class works by detecting changes through setters. Meaning if it is mutable, changes like `$entity->date->modfiy()` can not be detected, so the immutable types make more sense here. Similar the parameter types needed to be added. `Enity` and `QBMapper` needed to be adjusted so they support (auto map) those types, required when insert or update an entity. Also added more tests, especially to make sure the mapper really serializes the values correctly. Co-authored-by: Ferdinand Thiessen <opensource@fthiessen.de> Co-authored-by: Côme Chilliet <91878298+come-nc@users.noreply.github.com> Signed-off-by: Ferdinand Thiessen <opensource@fthiessen.de>
author: Ferdinand Thiessen <opensource@fthiessen.de> 2024-08-19 16:43:17 +0200
committer: Ferdinand Thiessen <opensource@fthiessen.de> 2024-10-17 18:31:42 +0200
commit: ee02e3246dc18a6390cd9fbfd7b66f441e71ccc1 (patch)
tree: 3ebd0a84988aa677e6d8ffc6a057079a4140c6fc
parent: 40fd76f69e601ab5579e8ce9b81318d3924dd463 (diff)
download: nextcloud-server-ee02e3246dc18a6390cd9fbfd7b66f441e71ccc1.tar.gz
nextcloud-server-ee02e3246dc18a6390cd9fbfd7b66f441e71ccc1.zip
14 files changed, 458 insertions, 105 deletions
diff --git a/apps/federatedfilesharing/lib/FederatedShareProvider.php b/apps/federatedfilesharing/lib/FederatedShareProvider.php
index da55b9a03d7..45ac8ba15de 100644
--- a/apps/federatedfilesharing/lib/FederatedShareProvider.php
+++ b/apps/federatedfilesharing/lib/FederatedShareProvider.php
@@ -303,7 +303,7 @@ class FederatedShareProvider implements IShareProvider {
 			->setValue('uid_owner', $qb->createNamedParameter($uidOwner))
 			->setValue('uid_initiator', $qb->createNamedParameter($sharedBy))
 			->setValue('permissions', $qb->createNamedParameter($permissions))
-			->setValue('expiration', $qb->createNamedParameter($expirationDate, IQueryBuilder::PARAM_DATE))
+			->setValue('expiration', $qb->createNamedParameter($expirationDate, IQueryBuilder::PARAM_DATETIME))
 			->setValue('token', $qb->createNamedParameter($token))
 			->setValue('stime', $qb->createNamedParameter(time()));
 
@@ -333,7 +333,7 @@ class FederatedShareProvider implements IShareProvider {
 			->set('permissions', $qb->createNamedParameter($share->getPermissions()))
 			->set('uid_owner', $qb->createNamedParameter($share->getShareOwner()))
 			->set('uid_initiator', $qb->createNamedParameter($share->getSharedBy()))
-			->set('expiration', $qb->createNamedParameter($share->getExpirationDate(), IQueryBuilder::PARAM_DATE))
+			->set('expiration', $qb->createNamedParameter($share->getExpirationDate(), IQueryBuilder::PARAM_DATETIME))
 			->executeStatement();
 
 		// send the updated permission to the owner/initiator, if they are not the same
diff --git a/apps/files_reminders/lib/Db/ReminderMapper.php b/apps/files_reminders/lib/Db/ReminderMapper.php
index 16859585bdf..35421656da2 100644
--- a/apps/files_reminders/lib/Db/ReminderMapper.php
+++ b/apps/files_reminders/lib/Db/ReminderMapper.php
@@ -135,7 +135,7 @@ class ReminderMapper extends QBMapper {
 		$qb->select('id', 'user_id', 'file_id', 'due_date', 'updated_at', 'created_at', 'notified')
 			->from($this->getTableName())
 			->where($qb->expr()->eq('notified', $qb->createNamedParameter(true, IQueryBuilder::PARAM_BOOL)))
-			->andWhere($qb->expr()->lt('due_date', $qb->createNamedParameter($buffer, IQueryBuilder::PARAM_DATE)))
+			->andWhere($qb->expr()->lt('due_date', $qb->createNamedParameter($buffer, IQueryBuilder::PARAM_DATETIME)))
 			->orderBy('due_date', 'ASC')
 			->setMaxResults($limit);
 
diff --git a/apps/sharebymail/lib/ShareByMailProvider.php b/apps/sharebymail/lib/ShareByMailProvider.php
index 3ec13dd3a08..77ff33cdbeb 100644
--- a/apps/sharebymail/lib/ShareByMailProvider.php
+++ b/apps/sharebymail/lib/ShareByMailProvider.php
@@ -699,7 +699,7 @@ class ShareByMailProvider extends DefaultShareProvider implements IShareProvider
 			->setValue('permissions', $qb->createNamedParameter($permissions))
 			->setValue('token', $qb->createNamedParameter($token))
 			->setValue('password', $qb->createNamedParameter($password))
-			->setValue('password_expiration_time', $qb->createNamedParameter($passwordExpirationTime, IQueryBuilder::PARAM_DATE))
+			->setValue('password_expiration_time', $qb->createNamedParameter($passwordExpirationTime, IQueryBuilder::PARAM_DATETIME))
 			->setValue('password_by_talk', $qb->createNamedParameter($sendPasswordByTalk, IQueryBuilder::PARAM_BOOL))
 			->setValue('stime', $qb->createNamedParameter(time()))
 			->setValue('hide_download', $qb->createNamedParameter((int)$hideDownload, IQueryBuilder::PARAM_INT))
@@ -712,7 +712,7 @@ class ShareByMailProvider extends DefaultShareProvider implements IShareProvider
 
 		$qb->setValue('attributes', $qb->createNamedParameter($shareAttributes));
 		if ($expirationTime !== null) {
-			$qb->setValue('expiration', $qb->createNamedParameter($expirationTime, IQueryBuilder::PARAM_DATE));
+			$qb->setValue('expiration', $qb->createNamedParameter($expirationTime, IQueryBuilder::PARAM_DATETIME));
 		}
 
 		$qb->executeStatement();
@@ -752,10 +752,10 @@ class ShareByMailProvider extends DefaultShareProvider implements IShareProvider
 			->set('uid_owner', $qb->createNamedParameter($share->getShareOwner()))
 			->set('uid_initiator', $qb->createNamedParameter($share->getSharedBy()))
 			->set('password', $qb->createNamedParameter($share->getPassword()))
-			->set('password_expiration_time', $qb->createNamedParameter($share->getPasswordExpirationTime(), IQueryBuilder::PARAM_DATE))
+			->set('password_expiration_time', $qb->createNamedParameter($share->getPasswordExpirationTime(), IQueryBuilder::PARAM_DATETIME))
 			->set('label', $qb->createNamedParameter($share->getLabel()))
 			->set('password_by_talk', $qb->createNamedParameter($share->getSendPasswordByTalk(), IQueryBuilder::PARAM_BOOL))
-			->set('expiration', $qb->createNamedParameter($share->getExpirationDate(), IQueryBuilder::PARAM_DATE))
+			->set('expiration', $qb->createNamedParameter($share->getExpirationDate(), IQueryBuilder::PARAM_DATETIME))
 			->set('note', $qb->createNamedParameter($share->getNote()))
 			->set('hide_download', $qb->createNamedParameter((int)$share->getHideDownload(), IQueryBuilder::PARAM_INT))
 			->set('attributes', $qb->createNamedParameter($shareAttributes))
diff --git a/lib/private/Comments/Manager.php b/lib/private/Comments/Manager.php
index 41e0c662212..860d3034ce8 100644
--- a/lib/private/Comments/Manager.php
+++ b/lib/private/Comments/Manager.php
@@ -440,14 +440,14 @@ class Manager implements ICommentsManager {
 					$query->expr()->orX(
 						$query->expr()->lt(
 							'creation_timestamp',
-							$query->createNamedParameter($lastKnownCommentDateTime, IQueryBuilder::PARAM_DATE),
-							IQueryBuilder::PARAM_DATE
+							$query->createNamedParameter($lastKnownCommentDateTime, IQueryBuilder::PARAM_DATETIME),
+							IQueryBuilder::PARAM_DATETIME
 						),
 						$query->expr()->andX(
 							$query->expr()->eq(
 								'creation_timestamp',
-								$query->createNamedParameter($lastKnownCommentDateTime, IQueryBuilder::PARAM_DATE),
-								IQueryBuilder::PARAM_DATE
+								$query->createNamedParameter($lastKnownCommentDateTime, IQueryBuilder::PARAM_DATETIME),
+								IQueryBuilder::PARAM_DATETIME
 							),
 							$idComparison
 						)
@@ -463,14 +463,14 @@ class Manager implements ICommentsManager {
 					$query->expr()->orX(
 						$query->expr()->gt(
 							'creation_timestamp',
-							$query->createNamedParameter($lastKnownCommentDateTime, IQueryBuilder::PARAM_DATE),
-							IQueryBuilder::PARAM_DATE
+							$query->createNamedParameter($lastKnownCommentDateTime, IQueryBuilder::PARAM_DATETIME),
+							IQueryBuilder::PARAM_DATETIME
 						),
 						$query->expr()->andX(
 							$query->expr()->eq(
 								'creation_timestamp',
-								$query->createNamedParameter($lastKnownCommentDateTime, IQueryBuilder::PARAM_DATE),
-								IQueryBuilder::PARAM_DATE
+								$query->createNamedParameter($lastKnownCommentDateTime, IQueryBuilder::PARAM_DATETIME),
+								IQueryBuilder::PARAM_DATETIME
 							),
 							$idComparison
 						)
@@ -740,7 +740,7 @@ class Manager implements ICommentsManager {
 			->from('comments')
 			->where($query->expr()->eq('object_type', $query->createNamedParameter($objectType)))
 			->andWhere($query->expr()->eq('object_id', $query->createNamedParameter($objectId)))
-			->andWhere($query->expr()->lt('creation_timestamp', $query->createNamedParameter($beforeDate, IQueryBuilder::PARAM_DATE)))
+			->andWhere($query->expr()->lt('creation_timestamp', $query->createNamedParameter($beforeDate, IQueryBuilder::PARAM_DATETIME)))
 			->orderBy('creation_timestamp', 'desc');
 
 		if ($verb !== '') {
@@ -1551,7 +1551,7 @@ class Manager implements ICommentsManager {
 		$qb = $this->dbConn->getQueryBuilder();
 		$qb->delete('comments')
 			->where($qb->expr()->lte('expire_date',
-				$qb->createNamedParameter($this->timeFactory->getDateTime(), IQueryBuilder::PARAM_DATE)))
+				$qb->createNamedParameter($this->timeFactory->getDateTime(), IQueryBuilder::PARAM_DATETIME)))
 			->andWhere($qb->expr()->eq('object_type', $qb->createNamedParameter($objectType)));
 
 		if ($objectId !== '') {
diff --git a/lib/public/AppFramework/Db/Entity.php b/lib/public/AppFramework/Db/Entity.php
index f37107ac128..882902a212e 100644
--- a/lib/public/AppFramework/Db/Entity.php
+++ b/lib/public/AppFramework/Db/Entity.php
@@ -7,6 +7,8 @@
  */
 namespace OCP\AppFramework\Db;
 
+use OCP\DB\Types;
+
 use function lcfirst;
 use function substr;
 
@@ -102,33 +104,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];
diff --git a/lib/public/AppFramework/Db/QBMapper.php b/lib/public/AppFramework/Db/QBMapper.php
index ef4516221e6..c0667d27111 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':
+			case Types::DATE:
 				return IQueryBuilder::PARAM_DATE;
-			case 'json':
+			case Types::DATETIME:
+				return IQueryBuilder::PARAM_DATETIME;
+			case Types::DATETIME_TZ:
+				return IQueryBuilder::PARAM_DATETIME_TZ;
+			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;
+			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..1ff2d4959c5 100644
--- a/lib/public/DB/QueryBuilder/IQueryBuilder.php
+++ b/lib/public/DB/QueryBuilder/IQueryBuilder.php
@@ -42,10 +42,54 @@ interface IQueryBuilder {
 	 * @since 9.0.0
 	 */
 	public const PARAM_LOB = ParameterType::LARGE_OBJECT;
+
+	/**
+	 * For passing a \DateTime instance when only interested in the time part (without timezone support)
+	 * @since 31.0.0
+	 */
+	public const PARAM_TIME = 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 = Types::DATE_MUTABLE;
+
+	/**
+	 * For passing a \DateTime instance (without timezone support)
+	 * @since 31.0.0
+	 */
+	public const PARAM_DATETIME = Types::DATETIME_MUTABLE;
+
+	/**
+	 * For passing a \DateTime instance with timezone support
+	 * @since 31.0.0
+	 */
+	public const PARAM_DATETIME_TZ = 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_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..57bea920763 100644
--- a/lib/public/Migration/Attributes/ColumnType.php
+++ b/lib/public/Migration/Attributes/ColumnType.php
@@ -8,6 +8,8 @@ declare(strict_types=1);
  */
 namespace OCP\Migration\Attributes;
 
+use OCP\DB\Types;
+
 /**
  * enum ColumnType based on OCP\DB\Types
  *
@@ -16,31 +18,45 @@ namespace OCP\Migration\Attributes;
  */
 enum ColumnType : string {
 	/** @since 30.0.0 */
-	case BIGINT = 'bigint';
-	/** @since 30.0.0 */
-	case BINARY = 'binary';
-	/** @since 30.0.0 */
-	case BLOB = 'blob';
+	case BIGINT = Types::BIGINT;
 	/** @since 30.0.0 */
-	case BOOLEAN = 'boolean';
+	case BINARY = Types::BINARY;
 	/** @since 30.0.0 */
-	case DATE = 'date';
+	case BLOB = Types::BLOB;
 	/** @since 30.0.0 */
-	case DATETIME = 'datetime';
+	case BOOLEAN = Types::BOOLEAN;
+	/**
+	 * 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 = Types::DATE;
+	/**
+	 * 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 = Types::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 = Types::DATETIME_TZ;
 	/** @since 30.0.0 */
-	case DECIMAL = 'decimal';
+	case DECIMAL = Types::DECIMAL;
 	/** @since 30.0.0 */
-	case FLOAT = 'float';
+	case FLOAT = Types::FLOAT;
 	/** @since 30.0.0 */
-	case INTEGER = 'integer';
+	case INTEGER = Types::INTEGER;
 	/** @since 30.0.0 */
-	case SMALLINT = 'smallint';
+	case SMALLINT = Types::SMALLINT;
 	/** @since 30.0.0 */
-	case STRING = 'string';
+	case STRING = Types::STRING;
 	/** @since 30.0.0 */
-	case TEXT = 'text';
+	case TEXT = Types::TEXT;
 	/** @since 30.0.0 */
-	case TIME = 'time';
+	case TIME = Types::TIME;
 	/** @since 30.0.0 */
-	case JSON = 'json';
+	case JSON = Types::JSON;
 }
diff --git a/tests/lib/AppFramework/Db/EntityTest.php b/tests/lib/AppFramework/Db/EntityTest.php
index 8e6e6dcd645..5b953a25c1a 100644
--- a/tests/lib/AppFramework/Db/EntityTest.php
+++ b/tests/lib/AppFramework/Db/EntityTest.php
@@ -9,6 +9,7 @@
 namespace Test\AppFramework\Db;
 
 use OCP\AppFramework\Db\Entity;
+use OCP\DB\Types;
 use PHPUnit\Framework\Constraint\IsType;
 
 /**
@@ -29,6 +30,10 @@ use PHPUnit\Framework\Constraint\IsType;
  * @method bool isAnotherBool()
  * @method string getLongText()
  * @method void setLongText(string $longText)
+ * @method \DateTime getTime()
+ * @method void setTime(\DateTime $time)
+ * @method \DateTimeImmutable getDatetime()
+ * @method void setDatetime(\DateTimeImmutable $datetime)
  */
 class TestEntity extends Entity {
 	protected $name;
@@ -38,12 +43,16 @@ class TestEntity extends Entity {
 	protected $trueOrFalse;
 	protected $anotherBool;
 	protected $longText;
+	protected $time;
+	protected $datetime;
 
 	public function __construct($name = null) {
-		$this->addType('testId', 'integer');
+		$this->addType('testId', Types::INTEGER);
 		$this->addType('trueOrFalse', 'bool');
-		$this->addType('anotherBool', 'boolean');
-		$this->addType('longText', 'blob');
+		$this->addType('anotherBool', Types::BOOLEAN);
+		$this->addType('longText', Types::BLOB);
+		$this->addType('time', Types::TIME);
+		$this->addType('datetime', Types::DATETIME_IMMUTABLE);
 		$this->name = $name;
 	}
 
@@ -216,15 +225,34 @@ class EntityTest extends \Test\TestCase {
 		$this->assertSame($string, $entity->getLongText());
 	}
 
+	public function testSetterConvertsDatetime() {
+		$entity = new TestEntity();
+		$entity->setDatetime('2024-08-19 15:26:00');
+		$this->assertEquals(new \DateTimeImmutable('2024-08-19 15:26:00'), $entity->getDatetime());
+	}
+
+	public function testSetterDoesNotConvertNullOnDatetime() {
+		$entity = new TestEntity();
+		$entity->setDatetime(null);
+		$this->assertNull($entity->getDatetime());
+	}
+
+	public function testSetterConvertsTime() {
+		$entity = new TestEntity();
+		$entity->setTime('15:26:00');
+		$this->assertEquals(new \DateTime('15:26:00'), $entity->getTime());
+	}
 
 	public function testGetFieldTypes(): void {
 		$entity = new TestEntity();
 		$this->assertEquals([
-			'id' => 'integer',
-			'testId' => 'integer',
+			'id' => Types::INTEGER,
+			'testId' => Types::INTEGER,
 			'trueOrFalse' => 'bool',
-			'anotherBool' => 'boolean',
-			'longText' => 'blob',
+			'anotherBool' => Types::BOOLEAN,
+			'longText' => Types::BLOB,
+			'time' => Types::TIME,
+			'datetime' => Types::DATETIME_IMMUTABLE,
 		], $entity->getFieldTypes());
 	}
 
@@ -232,7 +260,7 @@ class EntityTest extends \Test\TestCase {
 	public function testGetItInt(): void {
 		$entity = new TestEntity();
 		$entity->setId(3);
-		$this->assertEquals('integer', gettype($entity->getId()));
+		$this->assertEquals(Types::INTEGER, gettype($entity->getId()));
 	}
 
 
diff --git a/tests/lib/AppFramework/Db/QBMapperDBTest.php b/tests/lib/AppFramework/Db/QBMapperDBTest.php
new file mode 100644
index 00000000000..72bc2d956d6
--- /dev/null
+++ b/tests/lib/AppFramework/Db/QBMapperDBTest.php
@@ -0,0 +1,159 @@
+<?php
+/**
+ * SPDX-FileCopyrightText: 2024 Nextcloud GmbH and Nextcloud contributors
+ * SPDX-License-Identifier: AGPL-3.0-or-later
+ */
+
+namespace Test\AppFramework\Db;
+
+use Doctrine\DBAL\Schema\SchemaException;
+use OCP\AppFramework\Db\Entity;
+use OCP\AppFramework\Db\QBMapper;
+use OCP\DB\QueryBuilder\IQueryBuilder;
+use OCP\DB\Types;
+use OCP\IConfig;
+use OCP\IDBConnection;
+use OCP\Server;
+use Test\TestCase;
+
+/**
+ * @method void setTime(?\DateTime $time)
+ * @method ?\DateTime getTime()
+ * @method void setDatetime(?\DateTimeImmutable $datetime)
+ * @method ?\DateTimeImmutable getDatetime()
+ */
+class QBDBTestEntity extends Entity {
+	protected ?\DateTime $time = null;
+	protected ?\DateTimeImmutable $datetime = null;
+
+	public function __construct() {
+		$this->addType('time', Types::TIME);
+		$this->addType('datetime', Types::DATETIME_IMMUTABLE);
+	}
+}
+
+/**
+ * Class QBDBTestMapper
+ *
+ * @package Test\AppFramework\Db
+ */
+class QBDBTestMapper extends QBMapper {
+	public function __construct(IDBConnection $db) {
+		parent::__construct($db, 'testing', QBDBTestEntity::class);
+	}
+
+	public function getParameterTypeForPropertyForTest(Entity $entity, string $property) {
+		return parent::getParameterTypeForProperty($entity, $property);
+	}
+
+	public function getById(int $id): QBDBTestEntity {
+		$qb = $this->db->getQueryBuilder();
+		$query = $qb
+			->select('*')
+			->from($this->tableName)
+			->where(
+				$qb->expr()->eq('id', $qb->createPositionalParameter($id, IQueryBuilder::PARAM_INT)),
+			);
+		return $this->findEntity($query);
+	}
+}
+
+/**
+ * Test real database handling (serialization)
+ * @group DB
+ */
+class QBMapperDBTest extends TestCase {
+	/** @var \Doctrine\DBAL\Connection|\OCP\IDBConnection */
+	protected $connection;
+	protected $schemaSetup = false;
+
+	protected function setUp(): void {
+		parent::setUp();
+
+		$this->connection = \OCP\Server::get(IDBConnection::class);
+		$this->prepareTestingTable();
+	}
+
+	public function testInsertDateTime(): void {
+		$mapper = new QBDBTestMapper($this->connection);
+		$entity = new QBDBTestEntity();
+		$entity->setTime(new \DateTime('2003-01-01 12:34:00'));
+		$entity->setDatetime(new \DateTimeImmutable('2000-01-01 23:45:00'));
+
+		$result = $mapper->insert($entity);
+		$this->assertNotNull($result->getId());
+	}
+
+	public function testRetrieveDateTime(): void {
+		$time = new \DateTime('2000-01-01 01:01:00');
+		$datetime = new \DateTimeImmutable('2000-01-01 02:02:00');
+
+		$mapper = new QBDBTestMapper($this->connection);
+		$entity = new QBDBTestEntity();
+		$entity->setTime($time);
+		$entity->setDatetime($datetime);
+
+		$result = $mapper->insert($entity);
+		$this->assertNotNull($result->getId());
+
+		$dbEntity = $mapper->getById($result->getId());
+		$this->assertEquals($time->format('H:i:s'), $dbEntity->getTime()->format('H:i:s'));
+		$this->assertEquals($datetime->format('Y-m-d H:i:s'), $dbEntity->getDatetime()->format('Y-m-d H:i:s'));
+		// The date is not saved for "time"
+		$this->assertNotEquals($time->format('Y'), $dbEntity->getTime()->format('Y'));
+	}
+
+	public function testUpdateDateTime(): void {
+		$time = new \DateTime('2000-01-01 01:01:00');
+		$datetime = new \DateTimeImmutable('2000-01-01 02:02:00');
+
+		$mapper = new QBDBTestMapper($this->connection);
+		$entity = new QBDBTestEntity();
+		$entity->setTime('now');
+		$entity->setDatetime('now');
+
+		/** @var QBDBTestEntity */
+		$entity = $mapper->insert($entity);
+		$this->assertNotNull($entity->getId());
+
+		// Update the values
+		$entity->setTime($time);
+		$entity->setDatetime($datetime);
+		$mapper->update($entity);
+
+		$dbEntity = $mapper->getById($entity->getId());
+		$this->assertEquals($time->format('H:i:s'), $dbEntity->getTime()->format('H:i:s'));
+		$this->assertEquals($datetime->format('Y-m-d H:i:s'), $dbEntity->getDatetime()->format('Y-m-d H:i:s'));
+	}
+
+	protected function prepareTestingTable(): void {
+		if ($this->schemaSetup) {
+			$this->connection->getQueryBuilder()->delete('testing')->executeStatement();
+		}
+
+		$prefix = Server::get(IConfig::class)->getSystemValueString('dbtableprefix', 'oc_');
+		$schema = $this->connection->createSchema();
+		try {
+			$schema->getTable($prefix . 'testing');
+			$this->connection->getQueryBuilder()->delete('testing')->executeStatement();
+		} catch (SchemaException $e) {
+			$this->schemaSetup = true;
+			$table = $schema->createTable($prefix . 'testing');
+			$table->addColumn('id', Types::BIGINT, [
+				'autoincrement' => true,
+				'notnull' => true,
+			]);
+
+			$table->addColumn('time', Types::TIME, [
+				'notnull' => false,
+			]);
+
+			$table->addColumn('datetime', Types::DATETIME_IMMUTABLE, [
+				'notnull' => false,
+			]);
+
+			$table->setPrimaryKey(['id']);
+			$this->connection->migrateToSchema($schema);
+		}
+	}
+}
diff --git a/tests/lib/AppFramework/Db/QBMapperTest.php b/tests/lib/AppFramework/Db/QBMapperTest.php
index f08beca05ca..5fb46bfd668 100644
--- a/tests/lib/AppFramework/Db/QBMapperTest.php
+++ b/tests/lib/AppFramework/Db/QBMapperTest.php
@@ -10,7 +10,9 @@ use OCP\AppFramework\Db\Entity;
 use OCP\AppFramework\Db\QBMapper;
 use OCP\DB\QueryBuilder\IExpressionBuilder;
 use OCP\DB\QueryBuilder\IQueryBuilder;
+use OCP\DB\Types;
 use OCP\IDBConnection;
+use PHPUnit\Framework\MockObject\MockObject;
 
 /**
  * @method bool getBoolProp()
@@ -23,6 +25,8 @@ use OCP\IDBConnection;
  * @method void setBooleanProp(bool $booleanProp)
  * @method integer getIntegerProp()
  * @method void setIntegerProp(integer $integerProp)
+ * @method ?\DateTimeImmutable getDatetimeProp()
+ * @method void setDatetimeProp(?\DateTimeImmutable $datetime)
  */
 class QBTestEntity extends Entity {
 	protected $intProp;
@@ -31,14 +35,16 @@ class QBTestEntity extends Entity {
 	protected $integerProp;
 	protected $booleanProp;
 	protected $jsonProp;
+	protected $datetimeProp;
 
 	public function __construct() {
 		$this->addType('intProp', 'int');
 		$this->addType('boolProp', 'bool');
-		$this->addType('stringProp', 'string');
-		$this->addType('integerProp', 'integer');
-		$this->addType('booleanProp', 'boolean');
-		$this->addType('jsonProp', 'json');
+		$this->addType('stringProp', Types::STRING);
+		$this->addType('integerProp', Types::INTEGER);
+		$this->addType('booleanProp', Types::BOOLEAN);
+		$this->addType('jsonProp', Types::JSON);
+		$this->addType('datetimeProp', Types::DATETIME_IMMUTABLE);
 	}
 }
 
@@ -63,25 +69,11 @@ class QBTestMapper extends QBMapper {
  * @package Test\AppFramework\Db
  */
 class QBMapperTest extends \Test\TestCase {
-	/**
-	 * @var \PHPUnit\Framework\MockObject\MockObject|IDBConnection
-	 */
-	protected $db;
-
-	/**
-	 * @var \PHPUnit\Framework\MockObject\MockObject|IQueryBuilder
-	 */
-	protected $qb;
 
-	/**
-	 * @var \PHPUnit\Framework\MockObject\MockObject|IExpressionBuilder
-	 */
-	protected $expr;
-
-	/**
-	 * @var \Test\AppFramework\Db\QBTestMapper
-	 */
-	protected $mapper;
+	protected IDBConnection&MockObject $db;
+	protected IQueryBuilder&MockObject $qb;
+	protected IExpressionBuilder&MockObject $expr;
+	protected QBTestMapper $mapper;
 
 	/**
 	 * @throws \ReflectionException
@@ -109,36 +101,41 @@ class QBMapperTest extends \Test\TestCase {
 
 	
 	public function testInsertEntityParameterTypeMapping(): void {
+		$datetime = new \DateTimeImmutable();
 		$entity = new QBTestEntity();
 		$entity->setIntProp(123);
 		$entity->setBoolProp(true);
 		$entity->setStringProp('string');
 		$entity->setIntegerProp(456);
 		$entity->setBooleanProp(false);
+		$entity->setDatetimeProp($datetime);
 
 		$intParam = $this->qb->createNamedParameter('int_prop', IQueryBuilder::PARAM_INT);
 		$boolParam = $this->qb->createNamedParameter('bool_prop', IQueryBuilder::PARAM_BOOL);
 		$stringParam = $this->qb->createNamedParameter('string_prop', IQueryBuilder::PARAM_STR);
 		$integerParam = $this->qb->createNamedParameter('integer_prop', IQueryBuilder::PARAM_INT);
 		$booleanParam = $this->qb->createNamedParameter('boolean_prop', IQueryBuilder::PARAM_BOOL);
+		$datetimeParam = $this->qb->createNamedParameter('datetime_prop', IQueryBuilder::PARAM_DATETIME_IMMUTABLE);
 
-		$this->qb->expects($this->exactly(5))
+		$this->qb->expects($this->exactly(6))
 			->method('createNamedParameter')
 			->withConsecutive(
 				[$this->equalTo(123), $this->equalTo(IQueryBuilder::PARAM_INT)],
 				[$this->equalTo(true), $this->equalTo(IQueryBuilder::PARAM_BOOL)],
 				[$this->equalTo('string'), $this->equalTo(IQueryBuilder::PARAM_STR)],
 				[$this->equalTo(456), $this->equalTo(IQueryBuilder::PARAM_INT)],
-				[$this->equalTo(false), $this->equalTo(IQueryBuilder::PARAM_BOOL)]
+				[$this->equalTo(false), $this->equalTo(IQueryBuilder::PARAM_BOOL)],
+				[$this->equalTo($datetime), $this->equalTo(IQueryBuilder::PARAM_DATETIME_IMMUTABLE)],
 			);
-		$this->qb->expects($this->exactly(5))
+		$this->qb->expects($this->exactly(6))
 			->method('setValue')
 			->withConsecutive(
 				[$this->equalTo('int_prop'), $this->equalTo($intParam)],
 				[$this->equalTo('bool_prop'), $this->equalTo($boolParam)],
 				[$this->equalTo('string_prop'), $this->equalTo($stringParam)],
 				[$this->equalTo('integer_prop'), $this->equalTo($integerParam)],
-				[$this->equalTo('boolean_prop'), $this->equalTo($booleanParam)]
+				[$this->equalTo('boolean_prop'), $this->equalTo($booleanParam)],
+				[$this->equalTo('datetime_prop'), $this->equalTo($datetimeParam)],
 			);
 
 		$this->mapper->insert($entity);
@@ -146,6 +143,7 @@ class QBMapperTest extends \Test\TestCase {
 
 	
 	public function testUpdateEntityParameterTypeMapping(): void {
+		$datetime = new \DateTimeImmutable();
 		$entity = new QBTestEntity();
 		$entity->setId(789);
 		$entity->setIntProp(123);
@@ -153,7 +151,8 @@ class QBMapperTest extends \Test\TestCase {
 		$entity->setStringProp('string');
 		$entity->setIntegerProp(456);
 		$entity->setBooleanProp(false);
-		$entity->setJsonProp(['hello' => 'world']);
+		$entity->setJsonProp(["hello" => "world"]);
+		$entity->setDatetimeProp($datetime);
 
 		$idParam = $this->qb->createNamedParameter('id', IQueryBuilder::PARAM_INT);
 		$intParam = $this->qb->createNamedParameter('int_prop', IQueryBuilder::PARAM_INT);
@@ -162,8 +161,9 @@ class QBMapperTest extends \Test\TestCase {
 		$integerParam = $this->qb->createNamedParameter('integer_prop', IQueryBuilder::PARAM_INT);
 		$booleanParam = $this->qb->createNamedParameter('boolean_prop', IQueryBuilder::PARAM_BOOL);
 		$jsonParam = $this->qb->createNamedParameter('json_prop', IQueryBuilder::PARAM_JSON);
+		$datetimeParam = $this->qb->createNamedParameter('datetime_prop', IQueryBuilder::PARAM_DATETIME_IMMUTABLE);
 
-		$this->qb->expects($this->exactly(7))
+		$this->qb->expects($this->exactly(8))
 			->method('createNamedParameter')
 			->withConsecutive(
 				[$this->equalTo(123), $this->equalTo(IQueryBuilder::PARAM_INT)],
@@ -171,11 +171,12 @@ class QBMapperTest extends \Test\TestCase {
 				[$this->equalTo('string'), $this->equalTo(IQueryBuilder::PARAM_STR)],
 				[$this->equalTo(456), $this->equalTo(IQueryBuilder::PARAM_INT)],
 				[$this->equalTo(false), $this->equalTo(IQueryBuilder::PARAM_BOOL)],
-				[$this->equalTo(['hello' => 'world']), $this->equalTo(IQueryBuilder::PARAM_JSON)],
+				[$this->equalTo(["hello" => "world"]), $this->equalTo(IQueryBuilder::PARAM_JSON)],
+				[$this->equalTo($datetime), $this->equalTo(IQueryBuilder::PARAM_DATETIME_IMMUTABLE)],
 				[$this->equalTo(789), $this->equalTo(IQueryBuilder::PARAM_INT)],
 			);
 
-		$this->qb->expects($this->exactly(6))
+		$this->qb->expects($this->exactly(7))
 			->method('set')
 			->withConsecutive(
 				[$this->equalTo('int_prop'), $this->equalTo($intParam)],
@@ -183,7 +184,8 @@ class QBMapperTest extends \Test\TestCase {
 				[$this->equalTo('string_prop'), $this->equalTo($stringParam)],
 				[$this->equalTo('integer_prop'), $this->equalTo($integerParam)],
 				[$this->equalTo('boolean_prop'), $this->equalTo($booleanParam)],
-				[$this->equalTo('json_prop'), $this->equalTo($jsonParam)]
+				[$this->equalTo('json_prop'), $this->equalTo($jsonParam)],
+				[$this->equalTo('datetime_prop'), $this->equalTo($datetimeParam)],
 			);
 
 		$this->expr->expects($this->once())
@@ -216,6 +218,9 @@ class QBMapperTest extends \Test\TestCase {
 		$jsonType = $this->mapper->getParameterTypeForPropertyForTest($entity, 'jsonProp');
 		$this->assertEquals(IQueryBuilder::PARAM_JSON, $jsonType, 'JSON type property mapping incorrect');
 
+		$datetimeType = $this->mapper->getParameterTypeForPropertyForTest($entity, 'datetimeProp');
+		$this->assertEquals(IQueryBuilder::PARAM_DATETIME_IMMUTABLE, $datetimeType, 'DateTimeImmutable type property mapping incorrect');
+
 		$unknownType = $this->mapper->getParameterTypeForPropertyForTest($entity, 'someProp');
 		$this->assertEquals(IQueryBuilder::PARAM_STR, $unknownType, 'Unknown type property mapping incorrect');
 	}
diff --git a/tests/lib/Comments/ManagerTest.php b/tests/lib/Comments/ManagerTest.php
index 9a36182d393..44cb7c36f31 100644
--- a/tests/lib/Comments/ManagerTest.php
+++ b/tests/lib/Comments/ManagerTest.php
@@ -14,6 +14,7 @@ use OCP\Comments\IComment;
 use OCP\Comments\ICommentsEventHandler;
 use OCP\Comments\ICommentsManager;
 use OCP\Comments\NotFoundException;
+use OCP\DB\QueryBuilder\IQueryBuilder;
 use OCP\EventDispatcher\IEventDispatcher;
 use OCP\Files\Folder;
 use OCP\Files\IRootFolder;
@@ -70,11 +71,11 @@ class ManagerTest extends TestCase {
 				'actor_id' => $qb->createNamedParameter('alice'),
 				'message' => $qb->createNamedParameter('nice one'),
 				'verb' => $qb->createNamedParameter('comment'),
-				'creation_timestamp' => $qb->createNamedParameter($creationDT, 'datetime'),
-				'latest_child_timestamp' => $qb->createNamedParameter($latestChildDT, 'datetime'),
+				'creation_timestamp' => $qb->createNamedParameter($creationDT, IQueryBuilder::PARAM_DATETIME),
+				'latest_child_timestamp' => $qb->createNamedParameter($latestChildDT, IQueryBuilder::PARAM_DATETIME),
 				'object_type' => $qb->createNamedParameter('files'),
 				'object_id' => $qb->createNamedParameter($objectId),
-				'expire_date' => $qb->createNamedParameter($expireDate, 'datetime'),
+				'expire_date' => $qb->createNamedParameter($expireDate, IQueryBuilder::PARAM_DATETIME),
 				'reference_id' => $qb->createNamedParameter('referenceId'),
 				'meta_data' => $qb->createNamedParameter(json_encode(['last_edit_actor_id' => 'admin'])),
 			])
diff --git a/tests/lib/DB/QueryBuilder/ExpressionBuilderDBTest.php b/tests/lib/DB/QueryBuilder/ExpressionBuilderDBTest.php
index ed6e328881b..536e9f38405 100644
--- a/tests/lib/DB/QueryBuilder/ExpressionBuilderDBTest.php
+++ b/tests/lib/DB/QueryBuilder/ExpressionBuilderDBTest.php
@@ -7,9 +7,9 @@
 namespace Test\DB\QueryBuilder;
 
 use Doctrine\DBAL\Schema\SchemaException;
-use Doctrine\DBAL\Types\Types;
 use OC\DB\QueryBuilder\Literal;
 use OCP\DB\QueryBuilder\IQueryBuilder;
+use OCP\DB\Types;
 use OCP\IConfig;
 use OCP\Server;
 use Test\TestCase;
@@ -223,7 +223,7 @@ class ExpressionBuilderDBTest extends TestCase {
 				'notnull' => true,
 			]);
 
-			$table->addColumn('datetime', Types::DATETIME_MUTABLE, [
+			$table->addColumn('datetime', Types::DATETIME, [
 				'notnull' => false,
 			]);
author	Ferdinand Thiessen <opensource@fthiessen.de>	2024-08-19 16:43:17 +0200
committer	Ferdinand Thiessen <opensource@fthiessen.de>	2024-10-17 18:31:42 +0200
commit	ee02e3246dc18a6390cd9fbfd7b66f441e71ccc1 (patch)
tree	3ebd0a84988aa677e6d8ffc6a057079a4140c6fc
parent	40fd76f69e601ab5579e8ce9b81318d3924dd463 (diff)
download	nextcloud-server-ee02e3246dc18a6390cd9fbfd7b66f441e71ccc1.tar.gz nextcloud-server-ee02e3246dc18a6390cd9fbfd7b66f441e71ccc1.zip