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 /tests/lib/AppFramework
parent: 40fd76f69e601ab5579e8ce9b81318d3924dd463 (diff)
download: nextcloud-server-ee02e3246dc18a6390cd9fbfd7b66f441e71ccc1.tar.gz
nextcloud-server-ee02e3246dc18a6390cd9fbfd7b66f441e71ccc1.zip
3 files changed, 231 insertions, 39 deletions
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');
 	}
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 /tests/lib/AppFramework
parent	40fd76f69e601ab5579e8ce9b81318d3924dd463 (diff)
download	nextcloud-server-ee02e3246dc18a6390cd9fbfd7b66f441e71ccc1.tar.gz nextcloud-server-ee02e3246dc18a6390cd9fbfd7b66f441e71ccc1.zip