From ee02e3246dc18a6390cd9fbfd7b66f441e71ccc1 Mon Sep 17 00:00:00 2001 From: Ferdinand Thiessen Date: Mon, 19 Aug 2024 16:43:17 +0200 Subject: [PATCH] feat(AppFramework): Add full support for date / time / datetime columns MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit 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 Co-authored-by: Côme Chilliet <91878298+come-nc@users.noreply.github.com> Signed-off-by: Ferdinand Thiessen --- .../lib/FederatedShareProvider.php | 4 +- .../files_reminders/lib/Db/ReminderMapper.php | 2 +- apps/sharebymail/lib/ShareByMailProvider.php | 8 +- lib/private/Comments/Manager.php | 20 +-- lib/public/AppFramework/Db/Entity.php | 49 +++--- lib/public/AppFramework/Db/QBMapper.php | 28 ++- lib/public/DB/QueryBuilder/IQueryBuilder.php | 46 ++++- lib/public/DB/Types.php | 77 +++++++++ .../Migration/Attributes/ColumnType.php | 48 ++++-- tests/lib/AppFramework/Db/EntityTest.php | 44 ++++- tests/lib/AppFramework/Db/QBMapperDBTest.php | 159 ++++++++++++++++++ tests/lib/AppFramework/Db/QBMapperTest.php | 67 ++++---- tests/lib/Comments/ManagerTest.php | 7 +- .../QueryBuilder/ExpressionBuilderDBTest.php | 4 +- 14 files changed, 458 insertions(+), 105 deletions(-) create mode 100644 tests/lib/AppFramework/Db/QBMapperDBTest.php 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,17 +41,76 @@ 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,11 +148,29 @@ 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 @@ +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, ]); -- 2.39.5