diff options
Diffstat (limited to 'tests/lib/DB/QueryBuilder')
| -rw-r--r-- | tests/lib/DB/QueryBuilder/ExpressionBuilderDBTest.php | 236 | ||||
| -rw-r--r-- | tests/lib/DB/QueryBuilder/ExpressionBuilderTest.php | 431 | ||||
| -rw-r--r-- | tests/lib/DB/QueryBuilder/FunctionBuilderTest.php | 489 | ||||
| -rw-r--r-- | tests/lib/DB/QueryBuilder/Partitioned/JoinConditionTest.php | 74 | ||||
| -rw-r--r-- | tests/lib/DB/QueryBuilder/Partitioned/PartitionedQueryBuilderTest.php | 223 | ||||
| -rw-r--r-- | tests/lib/DB/QueryBuilder/QueryBuilderTest.php | 1484 | ||||
| -rw-r--r-- | tests/lib/DB/QueryBuilder/QuoteHelperTest.php | 132 | ||||
| -rw-r--r-- | tests/lib/DB/QueryBuilder/Sharded/SharedQueryBuilderTest.php | 128 |
8 files changed, 3197 insertions, 0 deletions
diff --git a/tests/lib/DB/QueryBuilder/ExpressionBuilderDBTest.php b/tests/lib/DB/QueryBuilder/ExpressionBuilderDBTest.php new file mode 100644 index 00000000000..153993f396e --- /dev/null +++ b/tests/lib/DB/QueryBuilder/ExpressionBuilderDBTest.php @@ -0,0 +1,236 @@ +<?php + +/** + * SPDX-FileCopyrightText: 2017 Nextcloud GmbH and Nextcloud contributors + * SPDX-License-Identifier: AGPL-3.0-or-later + */ + +namespace Test\DB\QueryBuilder; + +use Doctrine\DBAL\Schema\SchemaException; +use OC\DB\QueryBuilder\Literal; +use OCP\DB\QueryBuilder\IQueryBuilder; +use OCP\DB\Types; +use OCP\IConfig; +use OCP\IDBConnection; +use OCP\Server; +use Test\TestCase; + +/** + * @group DB + */ +class ExpressionBuilderDBTest extends TestCase { + /** @var \Doctrine\DBAL\Connection|IDBConnection */ + protected $connection; + protected $schemaSetup = false; + + protected function setUp(): void { + parent::setUp(); + + $this->connection = Server::get(IDBConnection::class); + $this->prepareTestingTable(); + } + + public static function likeProvider(): array { + $connection = Server::get(IDBConnection::class); + + return [ + ['foo', 'bar', false], + ['foo', 'foo', true], + ['foo', 'f%', true], + ['foo', '%o', true], + ['foo', '%', true], + ['foo', 'fo_', true], + ['foo', 'foo_', false], + ['foo', $connection->escapeLikeParameter('fo_'), false], + ['foo', $connection->escapeLikeParameter('f%'), false], + ]; + } + + /** + * + * @param string $param1 + * @param string $param2 + * @param boolean $match + */ + #[\PHPUnit\Framework\Attributes\DataProvider('likeProvider')] + public function testLike($param1, $param2, $match): void { + $query = $this->connection->getQueryBuilder(); + + $query->select(new Literal('1')) + ->from('users') + ->where($query->expr()->like($query->createNamedParameter($param1), $query->createNamedParameter($param2))); + + $result = $query->execute(); + $column = $result->fetchOne(); + $result->closeCursor(); + $this->assertEquals($match, $column); + } + + public static function ilikeProvider(): array { + $connection = Server::get(IDBConnection::class); + + return [ + ['foo', 'bar', false], + ['foo', 'foo', true], + ['foo', 'Foo', true], + ['foo', 'f%', true], + ['foo', '%o', true], + ['foo', '%', true], + ['foo', 'fo_', true], + ['foo', 'foo_', false], + ['foo', $connection->escapeLikeParameter('fo_'), false], + ['foo', $connection->escapeLikeParameter('f%'), false], + ]; + } + + /** + * + * @param string $param1 + * @param string $param2 + * @param boolean $match + */ + #[\PHPUnit\Framework\Attributes\DataProvider('ilikeProvider')] + public function testILike($param1, $param2, $match): void { + $query = $this->connection->getQueryBuilder(); + + $query->select(new Literal('1')) + ->from('users') + ->where($query->expr()->iLike($query->createNamedParameter($param1), $query->createNamedParameter($param2))); + + $result = $query->execute(); + $column = $result->fetchOne(); + $result->closeCursor(); + $this->assertEquals($match, $column); + } + + public function testCastColumn(): void { + $appId = $this->getUniqueID('testing'); + $this->createConfig($appId, '1', '4'); + + $query = $this->connection->getQueryBuilder(); + $query->update('appconfig') + ->set('configvalue', + $query->expr()->castColumn( + $query->createFunction( + '(' . $query->expr()->castColumn('configvalue', IQueryBuilder::PARAM_INT) + . ' + 1)' + ), IQueryBuilder::PARAM_STR + ) + ) + ->where($query->expr()->eq('appid', $query->createNamedParameter($appId))) + ->andWhere($query->expr()->eq('configkey', $query->createNamedParameter('1'))); + + $result = $query->executeStatement(); + $this->assertEquals(1, $result); + } + + public function testLongText(): void { + $appId = $this->getUniqueID('testing'); + $this->createConfig($appId, 'mykey', 'myvalue'); + + $query = $this->connection->getQueryBuilder(); + $query->select('*') + ->from('appconfig') + ->where($query->expr()->eq('appid', $query->createNamedParameter($appId))) + ->andWhere($query->expr()->eq('configkey', $query->createNamedParameter('mykey'))) + ->andWhere($query->expr()->eq('configvalue', $query->createNamedParameter('myvalue', IQueryBuilder::PARAM_STR), IQueryBuilder::PARAM_STR)); + + $result = $query->executeQuery(); + $entries = $result->fetchAll(); + $result->closeCursor(); + self::assertCount(1, $entries); + self::assertEquals('myvalue', $entries[0]['configvalue']); + } + + public function testDateTimeEquals(): void { + $dateTime = new \DateTime('2023-01-01'); + $insert = $this->connection->getQueryBuilder(); + $insert->insert('testing') + ->values(['datetime' => $insert->createNamedParameter($dateTime, IQueryBuilder::PARAM_DATETIME_MUTABLE)]) + ->executeStatement(); + + $query = $this->connection->getQueryBuilder(); + $result = $query->select('*') + ->from('testing') + ->where($query->expr()->eq('datetime', $query->createNamedParameter($dateTime, IQueryBuilder::PARAM_DATETIME_MUTABLE))) + ->executeQuery(); + $entries = $result->fetchAll(); + $result->closeCursor(); + self::assertCount(1, $entries); + } + + public function testDateTimeLess(): void { + $dateTime = new \DateTime('2022-01-01'); + $dateTimeCompare = new \DateTime('2022-01-02'); + $insert = $this->connection->getQueryBuilder(); + $insert->insert('testing') + ->values(['datetime' => $insert->createNamedParameter($dateTime, IQueryBuilder::PARAM_DATETIME_MUTABLE)]) + ->executeStatement(); + + $query = $this->connection->getQueryBuilder(); + $result = $query->select('*') + ->from('testing') + ->where($query->expr()->lt('datetime', $query->createNamedParameter($dateTimeCompare, IQueryBuilder::PARAM_DATETIME_MUTABLE))) + ->executeQuery(); + $entries = $result->fetchAll(); + $result->closeCursor(); + self::assertCount(1, $entries); + } + + public function testDateTimeGreater(): void { + $dateTime = new \DateTime('2023-01-02'); + $dateTimeCompare = new \DateTime('2023-01-01'); + $insert = $this->connection->getQueryBuilder(); + $insert->insert('testing') + ->values(['datetime' => $insert->createNamedParameter($dateTime, IQueryBuilder::PARAM_DATETIME_MUTABLE)]) + ->executeStatement(); + + $query = $this->connection->getQueryBuilder(); + $result = $query->select('*') + ->from('testing') + ->where($query->expr()->gt('datetime', $query->createNamedParameter($dateTimeCompare, IQueryBuilder::PARAM_DATETIME_MUTABLE))) + ->executeQuery(); + $entries = $result->fetchAll(); + $result->closeCursor(); + self::assertCount(1, $entries); + } + + protected function createConfig($appId, $key, $value) { + $query = $this->connection->getQueryBuilder(); + $query->insert('appconfig') + ->values([ + 'appid' => $query->createNamedParameter($appId), + 'configkey' => $query->createNamedParameter((string)$key), + 'configvalue' => $query->createNamedParameter((string)$value), + ]) + ->execute(); + } + + 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('datetime', Types::DATETIME, [ + 'notnull' => false, + ]); + + $table->setPrimaryKey(['id']); + $this->connection->migrateToSchema($schema); + } + } +} diff --git a/tests/lib/DB/QueryBuilder/ExpressionBuilderTest.php b/tests/lib/DB/QueryBuilder/ExpressionBuilderTest.php new file mode 100644 index 00000000000..1f84ebfbec1 --- /dev/null +++ b/tests/lib/DB/QueryBuilder/ExpressionBuilderTest.php @@ -0,0 +1,431 @@ +<?php + +/** + * SPDX-FileCopyrightText: 2016-2024 Nextcloud GmbH and Nextcloud contributors + * SPDX-FileCopyrightText: 2016 ownCloud, Inc. + * SPDX-License-Identifier: AGPL-3.0-only + */ + +namespace Test\DB\QueryBuilder; + +use Doctrine\DBAL\Query\Expression\ExpressionBuilder as DoctrineExpressionBuilder; +use OC\DB\QueryBuilder\ExpressionBuilder\ExpressionBuilder; +use OCP\DB\QueryBuilder\IQueryBuilder; +use OCP\IDBConnection; +use OCP\Server; +use Psr\Log\LoggerInterface; +use Test\TestCase; + +/** + * Class ExpressionBuilderTest + * + * @group DB + * + * @package Test\DB\QueryBuilder + */ +class ExpressionBuilderTest extends TestCase { + /** @var ExpressionBuilder */ + protected $expressionBuilder; + + /** @var DoctrineExpressionBuilder */ + protected $doctrineExpressionBuilder; + + /** @var IDBConnection */ + protected $connection; + + /** @var \Doctrine\DBAL\Connection */ + protected $internalConnection; + + /** @var LoggerInterface */ + protected $logger; + + protected function setUp(): void { + parent::setUp(); + + $this->connection = Server::get(IDBConnection::class); + $this->internalConnection = Server::get(\OC\DB\Connection::class); + $this->logger = $this->createMock(LoggerInterface::class); + + $queryBuilder = $this->createMock(IQueryBuilder::class); + + $this->expressionBuilder = new ExpressionBuilder($this->connection, $queryBuilder, $this->logger); + + $this->doctrineExpressionBuilder = new DoctrineExpressionBuilder($this->internalConnection); + } + + public static function dataComparison(): array { + $valueSets = self::dataComparisons(); + $comparisonOperators = ['=', '<>', '<', '>', '<=', '>=']; + + $testSets = []; + foreach ($comparisonOperators as $operator) { + foreach ($valueSets as $values) { + $testSets[] = array_merge([$operator], $values); + } + } + return $testSets; + } + + /** + * + * @param string $comparison + * @param mixed $input1 + * @param bool $isInput1Literal + * @param mixed $input2 + * @param bool $isInput2Literal + */ + #[\PHPUnit\Framework\Attributes\DataProvider('dataComparison')] + public function testComparison($comparison, $input1, $isInput1Literal, $input2, $isInput2Literal): void { + [$doctrineInput1, $ocInput1] = $this->helpWithLiteral($input1, $isInput1Literal); + [$doctrineInput2, $ocInput2] = $this->helpWithLiteral($input2, $isInput2Literal); + + $this->assertEquals( + $this->doctrineExpressionBuilder->comparison($doctrineInput1, $comparison, $doctrineInput2), + $this->expressionBuilder->comparison($ocInput1, $comparison, $ocInput2) + ); + } + + public static function dataComparisons(): array { + return [ + ['value', false, 'value', false], + ['value', false, 'value', true], + ['value', true, 'value', false], + ['value', true, 'value', true], + ]; + } + + /** + * + * @param mixed $input1 + * @param bool $isInput1Literal + * @param mixed $input2 + * @param bool $isInput2Literal + */ + #[\PHPUnit\Framework\Attributes\DataProvider('dataComparisons')] + public function testEquals($input1, $isInput1Literal, $input2, $isInput2Literal): void { + [$doctrineInput1, $ocInput1] = $this->helpWithLiteral($input1, $isInput1Literal); + [$doctrineInput2, $ocInput2] = $this->helpWithLiteral($input2, $isInput2Literal); + + $this->assertEquals( + $this->doctrineExpressionBuilder->eq($doctrineInput1, $doctrineInput2), + $this->expressionBuilder->eq($ocInput1, $ocInput2) + ); + } + + /** + * + * @param mixed $input1 + * @param bool $isInput1Literal + * @param mixed $input2 + * @param bool $isInput2Literal + */ + #[\PHPUnit\Framework\Attributes\DataProvider('dataComparisons')] + public function testNotEquals($input1, $isInput1Literal, $input2, $isInput2Literal): void { + [$doctrineInput1, $ocInput1] = $this->helpWithLiteral($input1, $isInput1Literal); + [$doctrineInput2, $ocInput2] = $this->helpWithLiteral($input2, $isInput2Literal); + + $this->assertEquals( + $this->doctrineExpressionBuilder->neq($doctrineInput1, $doctrineInput2), + $this->expressionBuilder->neq($ocInput1, $ocInput2) + ); + } + + /** + * + * @param mixed $input1 + * @param bool $isInput1Literal + * @param mixed $input2 + * @param bool $isInput2Literal + */ + #[\PHPUnit\Framework\Attributes\DataProvider('dataComparisons')] + public function testLowerThan($input1, $isInput1Literal, $input2, $isInput2Literal): void { + [$doctrineInput1, $ocInput1] = $this->helpWithLiteral($input1, $isInput1Literal); + [$doctrineInput2, $ocInput2] = $this->helpWithLiteral($input2, $isInput2Literal); + + $this->assertEquals( + $this->doctrineExpressionBuilder->lt($doctrineInput1, $doctrineInput2), + $this->expressionBuilder->lt($ocInput1, $ocInput2) + ); + } + + /** + * + * @param mixed $input1 + * @param bool $isInput1Literal + * @param mixed $input2 + * @param bool $isInput2Literal + */ + #[\PHPUnit\Framework\Attributes\DataProvider('dataComparisons')] + public function testLowerThanEquals($input1, $isInput1Literal, $input2, $isInput2Literal): void { + [$doctrineInput1, $ocInput1] = $this->helpWithLiteral($input1, $isInput1Literal); + [$doctrineInput2, $ocInput2] = $this->helpWithLiteral($input2, $isInput2Literal); + + $this->assertEquals( + $this->doctrineExpressionBuilder->lte($doctrineInput1, $doctrineInput2), + $this->expressionBuilder->lte($ocInput1, $ocInput2) + ); + } + + /** + * + * @param mixed $input1 + * @param bool $isInput1Literal + * @param mixed $input2 + * @param bool $isInput2Literal + */ + #[\PHPUnit\Framework\Attributes\DataProvider('dataComparisons')] + public function testGreaterThan($input1, $isInput1Literal, $input2, $isInput2Literal): void { + [$doctrineInput1, $ocInput1] = $this->helpWithLiteral($input1, $isInput1Literal); + [$doctrineInput2, $ocInput2] = $this->helpWithLiteral($input2, $isInput2Literal); + + $this->assertEquals( + $this->doctrineExpressionBuilder->gt($doctrineInput1, $doctrineInput2), + $this->expressionBuilder->gt($ocInput1, $ocInput2) + ); + } + + /** + * + * @param mixed $input1 + * @param bool $isInput1Literal + * @param mixed $input2 + * @param bool $isInput2Literal + */ + #[\PHPUnit\Framework\Attributes\DataProvider('dataComparisons')] + public function testGreaterThanEquals($input1, $isInput1Literal, $input2, $isInput2Literal): void { + [$doctrineInput1, $ocInput1] = $this->helpWithLiteral($input1, $isInput1Literal); + [$doctrineInput2, $ocInput2] = $this->helpWithLiteral($input2, $isInput2Literal); + + $this->assertEquals( + $this->doctrineExpressionBuilder->gte($doctrineInput1, $doctrineInput2), + $this->expressionBuilder->gte($ocInput1, $ocInput2) + ); + } + + public function testIsNull(): void { + $this->assertEquals( + $this->doctrineExpressionBuilder->isNull('`test`'), + $this->expressionBuilder->isNull('test') + ); + } + + public function testIsNotNull(): void { + $this->assertEquals( + $this->doctrineExpressionBuilder->isNotNull('`test`'), + $this->expressionBuilder->isNotNull('test') + ); + } + + public static function dataLike(): array { + return [ + ['value', false], + ['value', true], + ]; + } + + /** + * + * @param mixed $input + * @param bool $isLiteral + */ + #[\PHPUnit\Framework\Attributes\DataProvider('dataLike')] + public function testLike($input, $isLiteral): void { + [$doctrineInput, $ocInput] = $this->helpWithLiteral($input, $isLiteral); + + $this->assertEquals( + $this->doctrineExpressionBuilder->like('`test`', $doctrineInput), + $this->expressionBuilder->like('test', $ocInput) + ); + } + + /** + * + * @param mixed $input + * @param bool $isLiteral + */ + #[\PHPUnit\Framework\Attributes\DataProvider('dataLike')] + public function testNotLike($input, $isLiteral): void { + [$doctrineInput, $ocInput] = $this->helpWithLiteral($input, $isLiteral); + + $this->assertEquals( + $this->doctrineExpressionBuilder->notLike('`test`', $doctrineInput), + $this->expressionBuilder->notLike('test', $ocInput) + ); + } + + public static function dataIn(): array { + return [ + ['value', false], + ['value', true], + [['value'], false], + [['value'], true], + ]; + } + + /** + * + * @param mixed $input + * @param bool $isLiteral + */ + #[\PHPUnit\Framework\Attributes\DataProvider('dataIn')] + public function testIn($input, $isLiteral): void { + [$doctrineInput, $ocInput] = $this->helpWithLiteral($input, $isLiteral); + + $this->assertEquals( + $this->doctrineExpressionBuilder->in('`test`', $doctrineInput), + $this->expressionBuilder->in('test', $ocInput) + ); + } + + /** + * + * @param mixed $input + * @param bool $isLiteral + */ + #[\PHPUnit\Framework\Attributes\DataProvider('dataIn')] + public function testNotIn($input, $isLiteral): void { + [$doctrineInput, $ocInput] = $this->helpWithLiteral($input, $isLiteral); + + $this->assertEquals( + $this->doctrineExpressionBuilder->notIn('`test`', $doctrineInput), + $this->expressionBuilder->notIn('test', $ocInput) + ); + } + + protected function helpWithLiteral($input, $isLiteral) { + if ($isLiteral) { + if (is_array($input)) { + $doctrineInput = array_map(function ($ident) { + return $this->doctrineExpressionBuilder->literal($ident); + }, $input); + $ocInput = array_map(function ($ident) { + return $this->expressionBuilder->literal($ident); + }, $input); + } else { + $doctrineInput = $this->doctrineExpressionBuilder->literal($input); + $ocInput = $this->expressionBuilder->literal($input); + } + } else { + if (is_array($input)) { + $doctrineInput = array_map(function ($input) { + return '`' . $input . '`'; + }, $input); + $ocInput = $input; + } else { + $doctrineInput = '`' . $input . '`'; + $ocInput = $input; + } + } + + return [$doctrineInput, $ocInput]; + } + + public static function dataLiteral(): array { + return [ + ['value', null], + ['1', null], + [1, null], + [1, 'string'], + [1, 'integer'], + [1, IQueryBuilder::PARAM_INT], + ]; + } + + /** + * + * @param mixed $input + * @param string|null $type + */ + #[\PHPUnit\Framework\Attributes\DataProvider('dataLiteral')] + public function testLiteral($input, $type): void { + /** @var \OC\DB\QueryBuilder\Literal $actual */ + $actual = $this->expressionBuilder->literal($input, $type); + + $this->assertInstanceOf('\OC\DB\QueryBuilder\Literal', $actual); + $this->assertEquals( + $this->doctrineExpressionBuilder->literal($input, $type), + $actual->__toString() + ); + } + + public static function dataClobComparisons(): array { + return [ + ['eq', '5', IQueryBuilder::PARAM_STR, false, 3], + ['eq', '5', IQueryBuilder::PARAM_STR, true, 1], + ['neq', '5', IQueryBuilder::PARAM_STR, false, 8], + ['neq', '5', IQueryBuilder::PARAM_STR, true, 6], + ['lt', '5', IQueryBuilder::PARAM_STR, false, 3], + ['lt', '5', IQueryBuilder::PARAM_STR, true, 1], + ['lte', '5', IQueryBuilder::PARAM_STR, false, 6], + ['lte', '5', IQueryBuilder::PARAM_STR, true, 4], + ['gt', '5', IQueryBuilder::PARAM_STR, false, 5], + ['gt', '5', IQueryBuilder::PARAM_STR, true, 1], + ['gte', '5', IQueryBuilder::PARAM_STR, false, 8], + ['gte', '5', IQueryBuilder::PARAM_STR, true, 4], + ['like', '%5%', IQueryBuilder::PARAM_STR, false, 3], + ['like', '%5%', IQueryBuilder::PARAM_STR, true, 1], + ['like', 'under_%', IQueryBuilder::PARAM_STR, false, 2], + ['like', 'under\_%', IQueryBuilder::PARAM_STR, false, 1], + ['notLike', '%5%', IQueryBuilder::PARAM_STR, false, 8], + ['notLike', '%5%', IQueryBuilder::PARAM_STR, true, 6], + ['in', ['5'], IQueryBuilder::PARAM_STR_ARRAY, false, 3], + ['in', ['5'], IQueryBuilder::PARAM_STR_ARRAY, true, 1], + ['notIn', ['5'], IQueryBuilder::PARAM_STR_ARRAY, false, 8], + ['notIn', ['5'], IQueryBuilder::PARAM_STR_ARRAY, true, 6], + ]; + } + + /** + * @param string $function + * @param mixed $value + * @param mixed $type + * @param bool $compareKeyToValue + * @param int $expected + */ + #[\PHPUnit\Framework\Attributes\DataProvider('dataClobComparisons')] + public function testClobComparisons($function, $value, $type, $compareKeyToValue, $expected): void { + $appId = $this->getUniqueID('testing'); + $this->createConfig($appId, 1, 4); + $this->createConfig($appId, 2, 5); + $this->createConfig($appId, 3, 6); + $this->createConfig($appId, 4, 4); + $this->createConfig($appId, 5, 5); + $this->createConfig($appId, 6, 6); + $this->createConfig($appId, 7, 4); + $this->createConfig($appId, 8, 5); + $this->createConfig($appId, 9, 6); + $this->createConfig($appId, 10, 'under_score'); + $this->createConfig($appId, 11, 'underscore'); + + $query = $this->connection->getQueryBuilder(); + $query->select($query->func()->count('*', 'count')) + ->from('appconfig') + ->where($query->expr()->eq('appid', $query->createNamedParameter($appId))) + ->andWhere(call_user_func([$query->expr(), $function], 'configvalue', $query->createNamedParameter($value, $type), IQueryBuilder::PARAM_STR)); + + if ($compareKeyToValue) { + $query->andWhere(call_user_func([$query->expr(), $function], 'configkey', 'configvalue', IQueryBuilder::PARAM_STR)); + } + + $result = $query->execute(); + + $this->assertEquals(['count' => $expected], $result->fetch()); + $result->closeCursor(); + + $query = $this->connection->getQueryBuilder(); + $query->delete('appconfig') + ->where($query->expr()->eq('appid', $query->createNamedParameter($appId))) + ->execute(); + } + + protected function createConfig($appId, $key, $value) { + $query = $this->connection->getQueryBuilder(); + $query->insert('appconfig') + ->values([ + 'appid' => $query->createNamedParameter($appId), + 'configkey' => $query->createNamedParameter((string)$key), + 'configvalue' => $query->createNamedParameter((string)$value), + ]) + ->execute(); + } +} diff --git a/tests/lib/DB/QueryBuilder/FunctionBuilderTest.php b/tests/lib/DB/QueryBuilder/FunctionBuilderTest.php new file mode 100644 index 00000000000..5a111c91aa7 --- /dev/null +++ b/tests/lib/DB/QueryBuilder/FunctionBuilderTest.php @@ -0,0 +1,489 @@ +<?php + +/** + * SPDX-FileCopyrightText: 2017 Nextcloud GmbH and Nextcloud contributors + * SPDX-License-Identifier: AGPL-3.0-only + */ + +namespace Test\DB\QueryBuilder; + +use OC\DB\QueryBuilder\Literal; +use OCP\DB\QueryBuilder\IQueryBuilder; +use OCP\IDBConnection; +use OCP\Server; +use Test\TestCase; + +/** + * Class FunctionBuilderTest + * + * @group DB + * + * @package Test\DB\QueryBuilder + */ +class FunctionBuilderTest extends TestCase { + /** @var \Doctrine\DBAL\Connection|IDBConnection */ + protected $connection; + + protected function setUp(): void { + parent::setUp(); + + $this->connection = Server::get(IDBConnection::class); + } + + #[\PHPUnit\Framework\Attributes\DataProvider('providerTestConcatString')] + public function testConcatString($closure): void { + $query = $this->connection->getQueryBuilder(); + [$real, $arguments, $return] = $closure($query); + if ($real) { + $this->addDummyData(); + $query->where($query->expr()->eq('appid', $query->createNamedParameter('group_concat'))); + $query->orderBy('configkey', 'asc'); + } + + $query->select($query->func()->concat(...$arguments)); + $query->from('appconfig') + ->setMaxResults(1); + + $result = $query->execute(); + $column = $result->fetchOne(); + $result->closeCursor(); + $this->assertEquals($return, $column); + } + + public static function providerTestConcatString(): array { + return [ + '1 column: string param unicode' + => [function ($q) { + return [false, [$q->createNamedParameter('👍')], '👍']; + }], + '2 columns: string param and string param' + => [function ($q) { + return [false, [$q->createNamedParameter('foo'), $q->createNamedParameter('bar')], 'foobar']; + }], + '2 columns: string param and int literal' + => [function ($q) { + return [false, [$q->createNamedParameter('foo'), $q->expr()->literal(1)], 'foo1']; + }], + '2 columns: string param and string literal' + => [function ($q) { + return [false, [$q->createNamedParameter('foo'), $q->expr()->literal('bar')], 'foobar']; + }], + '2 columns: string real and int literal' + => [function ($q) { + return [true, ['configkey', $q->expr()->literal(2)], '12']; + }], + '4 columns: string literal' + => [function ($q) { + return [false, [$q->expr()->literal('foo'), $q->expr()->literal('bar'), $q->expr()->literal('foo'), $q->expr()->literal('bar')], 'foobarfoobar']; + }], + '4 columns: int literal' + => [function ($q) { + return [false, [$q->expr()->literal(1), $q->expr()->literal(2), $q->expr()->literal(3), $q->expr()->literal(4)], '1234']; + }], + '5 columns: string param with special chars used in the function' + => [function ($q) { + return [false, [$q->createNamedParameter('b'), $q->createNamedParameter("'"), $q->createNamedParameter('||'), $q->createNamedParameter(','), $q->createNamedParameter('a')], "b'||,a"]; + }], + ]; + } + + protected function clearDummyData(): void { + $delete = $this->connection->getQueryBuilder(); + + $delete->delete('appconfig') + ->where($delete->expr()->eq('appid', $delete->createNamedParameter('group_concat'))); + $delete->executeStatement(); + } + + protected function addDummyData(): void { + $this->clearDummyData(); + $insert = $this->connection->getQueryBuilder(); + + $insert->insert('appconfig') + ->setValue('appid', $insert->createNamedParameter('group_concat')) + ->setValue('configvalue', $insert->createNamedParameter('unittest')) + ->setValue('configkey', $insert->createParameter('value')); + + $insert->setParameter('value', '1'); + $insert->executeStatement(); + $insert->setParameter('value', '3'); + $insert->executeStatement(); + $insert->setParameter('value', '2'); + $insert->executeStatement(); + } + + public function testGroupConcatWithoutSeparator(): void { + $this->addDummyData(); + $query = $this->connection->getQueryBuilder(); + + $query->select($query->func()->groupConcat('configkey')) + ->from('appconfig') + ->where($query->expr()->eq('appid', $query->createNamedParameter('group_concat'))); + + $result = $query->execute(); + $column = $result->fetchOne(); + $result->closeCursor(); + $this->assertStringContainsString(',', $column); + $actual = explode(',', $column); + $this->assertEqualsCanonicalizing([1,2,3], $actual); + } + + public function testGroupConcatWithSeparator(): void { + $this->addDummyData(); + $query = $this->connection->getQueryBuilder(); + + $query->select($query->func()->groupConcat('configkey', '#')) + ->from('appconfig') + ->where($query->expr()->eq('appid', $query->createNamedParameter('group_concat'))); + + $result = $query->execute(); + $column = $result->fetchOne(); + $result->closeCursor(); + $this->assertStringContainsString('#', $column); + $actual = explode('#', $column); + $this->assertEqualsCanonicalizing([1,2,3], $actual); + } + + public function testGroupConcatWithSingleQuoteSeparator(): void { + $this->addDummyData(); + $query = $this->connection->getQueryBuilder(); + + $query->select($query->func()->groupConcat('configkey', '\'')) + ->from('appconfig') + ->where($query->expr()->eq('appid', $query->createNamedParameter('group_concat'))); + + $result = $query->execute(); + $column = $result->fetchOne(); + $result->closeCursor(); + $this->assertStringContainsString("'", $column); + $actual = explode("'", $column); + $this->assertEqualsCanonicalizing([1,2,3], $actual); + } + + public function testGroupConcatWithDoubleQuoteSeparator(): void { + $this->addDummyData(); + $query = $this->connection->getQueryBuilder(); + + $query->select($query->func()->groupConcat('configkey', '"')) + ->from('appconfig') + ->where($query->expr()->eq('appid', $query->createNamedParameter('group_concat'))); + + $result = $query->execute(); + $column = $result->fetchOne(); + $result->closeCursor(); + $this->assertStringContainsString('"', $column); + $actual = explode('"', $column); + $this->assertEqualsCanonicalizing([1,2,3], $actual); + } + + protected function clearIntDummyData(): void { + $delete = $this->connection->getQueryBuilder(); + + $delete->delete('systemtag') + ->where($delete->expr()->eq('name', $delete->createNamedParameter('group_concat'))); + $delete->executeStatement(); + } + + protected function addIntDummyData(): void { + $this->clearIntDummyData(); + $insert = $this->connection->getQueryBuilder(); + + $insert->insert('systemtag') + ->setValue('name', $insert->createNamedParameter('group_concat')) + ->setValue('visibility', $insert->createNamedParameter(1)) + ->setValue('editable', $insert->createParameter('value')); + + $insert->setParameter('value', 1); + $insert->executeStatement(); + $insert->setParameter('value', 2); + $insert->executeStatement(); + $insert->setParameter('value', 3); + $insert->executeStatement(); + } + + public function testIntGroupConcatWithoutSeparator(): void { + $this->addIntDummyData(); + $query = $this->connection->getQueryBuilder(); + + $query->select($query->func()->groupConcat('editable')) + ->from('systemtag') + ->where($query->expr()->eq('name', $query->createNamedParameter('group_concat'))); + + $result = $query->execute(); + $column = $result->fetchOne(); + $result->closeCursor(); + $this->assertStringContainsString(',', $column); + $actual = explode(',', $column); + $this->assertEqualsCanonicalizing([1,2,3], $actual); + } + + public function testIntGroupConcatWithSeparator(): void { + $this->addIntDummyData(); + $query = $this->connection->getQueryBuilder(); + + $query->select($query->func()->groupConcat('editable', '#')) + ->from('systemtag') + ->where($query->expr()->eq('name', $query->createNamedParameter('group_concat'))); + + $result = $query->execute(); + $column = $result->fetchOne(); + $result->closeCursor(); + $this->assertStringContainsString('#', $column); + $actual = explode('#', $column); + $this->assertEqualsCanonicalizing([1,2,3], $actual); + } + + public function testMd5(): void { + $query = $this->connection->getQueryBuilder(); + + $query->select($query->func()->md5($query->createNamedParameter('foobar'))); + $query->from('appconfig') + ->setMaxResults(1); + + $result = $query->execute(); + $column = $result->fetchOne(); + $result->closeCursor(); + $this->assertEquals(md5('foobar'), $column); + } + + public function testSubstring(): void { + $query = $this->connection->getQueryBuilder(); + + $query->select($query->func()->substring($query->createNamedParameter('foobar'), new Literal(2), $query->createNamedParameter(2))); + $query->from('appconfig') + ->setMaxResults(1); + + $result = $query->execute(); + $column = $result->fetchOne(); + $result->closeCursor(); + $this->assertEquals('oo', $column); + } + + public function testSubstringNoLength(): void { + $query = $this->connection->getQueryBuilder(); + + $query->select($query->func()->substring($query->createNamedParameter('foobar'), new Literal(2))); + $query->from('appconfig') + ->setMaxResults(1); + + $result = $query->execute(); + $column = $result->fetchOne(); + $result->closeCursor(); + $this->assertEquals('oobar', $column); + } + + public function testLower(): void { + $query = $this->connection->getQueryBuilder(); + + $query->select($query->func()->lower($query->createNamedParameter('FooBar'))); + $query->from('appconfig') + ->setMaxResults(1); + + $result = $query->execute(); + $column = $result->fetchOne(); + $result->closeCursor(); + $this->assertEquals('foobar', $column); + } + + public function testAdd(): void { + $query = $this->connection->getQueryBuilder(); + + $query->select($query->func()->add($query->createNamedParameter(2, IQueryBuilder::PARAM_INT), new Literal(1))); + $query->from('appconfig') + ->setMaxResults(1); + + $result = $query->execute(); + $column = $result->fetchOne(); + $result->closeCursor(); + $this->assertEquals(3, $column); + } + + public function testSubtract(): void { + $query = $this->connection->getQueryBuilder(); + + $query->select($query->func()->subtract($query->createNamedParameter(2, IQueryBuilder::PARAM_INT), new Literal(1))); + $query->from('appconfig') + ->setMaxResults(1); + + $result = $query->execute(); + $column = $result->fetchOne(); + $result->closeCursor(); + $this->assertEquals(1, $column); + } + + public function testCount(): void { + $query = $this->connection->getQueryBuilder(); + + $query->select($query->func()->count('appid')); + $query->from('appconfig') + ->setMaxResults(1); + + $result = $query->execute(); + $column = $result->fetchOne(); + $result->closeCursor(); + $this->assertGreaterThan(1, $column); + } + + public static function octetLengthProvider(): array { + return [ + ['', 0], + ['foobar', 6], + ['fé', 3], + ['šđčćž', 10], + ]; + } + + #[\PHPUnit\Framework\Attributes\DataProvider('octetLengthProvider')] + public function testOctetLength(string $str, int $bytes): void { + $query = $this->connection->getQueryBuilder(); + + $query->select($query->func()->octetLength($query->createNamedParameter($str, IQueryBuilder::PARAM_STR))); + $query->from('appconfig') + ->setMaxResults(1); + + $result = $query->execute(); + $column = $result->fetchOne(); + $result->closeCursor(); + $this->assertEquals($bytes, $column); + } + + public static function charLengthProvider(): array { + return [ + ['', 0], + ['foobar', 6], + ['fé', 2], + ['šđčćž', 5], + ]; + } + + #[\PHPUnit\Framework\Attributes\DataProvider('charLengthProvider')] + public function testCharLength(string $str, int $bytes): void { + $query = $this->connection->getQueryBuilder(); + + $query->select($query->func()->charLength($query->createNamedParameter($str, IQueryBuilder::PARAM_STR))); + $query->from('appconfig') + ->setMaxResults(1); + + $result = $query->execute(); + $column = $result->fetchOne(); + $result->closeCursor(); + $this->assertNotNull($column); + $this->assertEquals($bytes, $column); + } + + private function setUpMinMax($value) { + $query = $this->connection->getQueryBuilder(); + + $query->insert('appconfig') + ->values([ + 'appid' => $query->createNamedParameter('minmax'), + 'configkey' => $query->createNamedParameter(uniqid()), + 'configvalue' => $query->createNamedParameter((string)$value), + ]); + $query->execute(); + } + + private function clearMinMax() { + $query = $this->connection->getQueryBuilder(); + + $query->delete('appconfig') + ->where($query->expr()->eq('appid', $query->createNamedParameter('minmax'))); + $query->execute(); + } + + public function testMaxEmpty(): void { + $this->clearMinMax(); + + $query = $this->connection->getQueryBuilder(); + + $query->select($query->func()->max($query->expr()->castColumn('configvalue', IQueryBuilder::PARAM_INT))); + $query->from('appconfig') + ->where($query->expr()->eq('appid', $query->createNamedParameter('minmax'))) + ->setMaxResults(1); + + $result = $query->execute(); + $row = $result->fetchOne(); + $result->closeCursor(); + $this->assertEquals(null, $row); + } + + public function testMinEmpty(): void { + $this->clearMinMax(); + + $query = $this->connection->getQueryBuilder(); + + $query->select($query->func()->min($query->expr()->castColumn('configvalue', IQueryBuilder::PARAM_INT))); + $query->from('appconfig') + ->where($query->expr()->eq('appid', $query->createNamedParameter('minmax'))) + ->setMaxResults(1); + + $result = $query->execute(); + $row = $result->fetchOne(); + $result->closeCursor(); + $this->assertEquals(null, $row); + } + + public function testMax(): void { + $this->clearMinMax(); + $this->setUpMinMax(10); + $this->setUpMinMax(11); + $this->setUpMinMax(20); + + $query = $this->connection->getQueryBuilder(); + + $query->select($query->func()->max($query->expr()->castColumn('configvalue', IQueryBuilder::PARAM_INT))); + $query->from('appconfig') + ->where($query->expr()->eq('appid', $query->createNamedParameter('minmax'))) + ->setMaxResults(1); + + $result = $query->execute(); + $row = $result->fetchOne(); + $result->closeCursor(); + $this->assertEquals(20, $row); + } + + public function testMin(): void { + $this->clearMinMax(); + $this->setUpMinMax(10); + $this->setUpMinMax(11); + $this->setUpMinMax(20); + + $query = $this->connection->getQueryBuilder(); + + $query->select($query->func()->min($query->expr()->castColumn('configvalue', IQueryBuilder::PARAM_INT))); + $query->from('appconfig') + ->where($query->expr()->eq('appid', $query->createNamedParameter('minmax'))) + ->setMaxResults(1); + + $result = $query->execute(); + $row = $result->fetchOne(); + $result->closeCursor(); + $this->assertEquals(10, $row); + } + + public function testGreatest(): void { + $query = $this->connection->getQueryBuilder(); + + $query->select($query->func()->greatest($query->createNamedParameter(2, IQueryBuilder::PARAM_INT), new Literal(1))); + $query->from('appconfig') + ->setMaxResults(1); + + $result = $query->execute(); + $row = $result->fetchOne(); + $result->closeCursor(); + $this->assertEquals(2, $row); + } + + public function testLeast(): void { + $query = $this->connection->getQueryBuilder(); + + $query->select($query->func()->least($query->createNamedParameter(2, IQueryBuilder::PARAM_INT), new Literal(1))); + $query->from('appconfig') + ->setMaxResults(1); + + $result = $query->execute(); + $row = $result->fetchOne(); + $result->closeCursor(); + $this->assertEquals(1, $row); + } +} diff --git a/tests/lib/DB/QueryBuilder/Partitioned/JoinConditionTest.php b/tests/lib/DB/QueryBuilder/Partitioned/JoinConditionTest.php new file mode 100644 index 00000000000..8f84d6a0d2c --- /dev/null +++ b/tests/lib/DB/QueryBuilder/Partitioned/JoinConditionTest.php @@ -0,0 +1,74 @@ +<?php + +declare(strict_types=1); +/** + * SPDX-FileCopyrightText: 2024 Robin Appelman <robin@icewind.nl> + * SPDX-License-Identifier: AGPL-3.0-or-later + */ + +namespace lib\DB\QueryBuilder\Partitioned; + +use OC\DB\ConnectionAdapter; +use OC\DB\QueryBuilder\Partitioned\JoinCondition; +use OC\DB\QueryBuilder\QueryBuilder; +use OC\SystemConfig; +use OCP\DB\QueryBuilder\IQueryBuilder; +use OCP\IDBConnection; +use Psr\Log\LoggerInterface; +use Test\TestCase; + +class JoinConditionTest extends TestCase { + protected function setUp(): void { + parent::setUp(); + } + + public static function platformProvider(): array { + return [ + [IDBConnection::PLATFORM_SQLITE], + [IDBConnection::PLATFORM_POSTGRES], + [IDBConnection::PLATFORM_MYSQL], + [IDBConnection::PLATFORM_ORACLE], + ]; + } + + private function getBuilder(string $platform): IQueryBuilder { + $connection = $this->createMock(ConnectionAdapter::class); + $connection->method('getDatabaseProvider')->willReturn($platform); + return new QueryBuilder( + $connection, + $this->createMock(SystemConfig::class), + $this->createMock(LoggerInterface::class) + ); + } + + #[\PHPUnit\Framework\Attributes\DataProvider('platformProvider')] + public function testParseCondition(string $platform): void { + $query = $this->getBuilder($platform); + $param1 = $query->createNamedParameter('files'); + $param2 = $query->createNamedParameter('test'); + $condition = $query->expr()->andX( + $query->expr()->eq('tagmap.categoryid', 'tag.id'), + $query->expr()->eq('tag.type', $param1), + $query->expr()->eq('tag.uid', $param2) + ); + $parsed = JoinCondition::parse($condition, 'vcategory', 'tag', 'tagmap'); + $this->assertEquals('tagmap.categoryid', $parsed->fromColumn); + $this->assertEquals('tag.id', $parsed->toColumn); + $this->assertEquals([], $parsed->fromConditions); + $this->assertEquals([ + $query->expr()->eq('tag.type', $param1), + $query->expr()->eq('tag.uid', $param2), + ], $parsed->toConditions); + } + + #[\PHPUnit\Framework\Attributes\DataProvider('platformProvider')] + public function testParseCastCondition(string $platform): void { + $query = $this->getBuilder($platform); + + $condition = $query->expr()->eq($query->expr()->castColumn('m.objectid', IQueryBuilder::PARAM_INT), 'f.fileid'); + $parsed = JoinCondition::parse($condition, 'filecache', 'f', 'm'); + $this->assertEquals('m.objectid', $parsed->fromColumn); + $this->assertEquals('f.fileid', $parsed->toColumn); + $this->assertEquals([], $parsed->fromConditions); + } +} diff --git a/tests/lib/DB/QueryBuilder/Partitioned/PartitionedQueryBuilderTest.php b/tests/lib/DB/QueryBuilder/Partitioned/PartitionedQueryBuilderTest.php new file mode 100644 index 00000000000..f99adc73aa8 --- /dev/null +++ b/tests/lib/DB/QueryBuilder/Partitioned/PartitionedQueryBuilderTest.php @@ -0,0 +1,223 @@ +<?php + +/** + * SPDX-FileCopyrightText: 2024 Nextcloud GmbH and Nextcloud contributors + * SPDX-License-Identifier: AGPL-3.0-or-later + */ + +declare(strict_types=1); + +namespace Test\DB\QueryBuilder\Partitioned; + +use OC\DB\QueryBuilder\Partitioned\PartitionedQueryBuilder; +use OC\DB\QueryBuilder\Partitioned\PartitionSplit; +use OC\DB\QueryBuilder\Sharded\AutoIncrementHandler; +use OC\DB\QueryBuilder\Sharded\ShardConnectionManager; +use OCP\DB\QueryBuilder\IQueryBuilder; +use OCP\IDBConnection; +use OCP\Server; +use Test\TestCase; + +/** + * @group DB + */ +class PartitionedQueryBuilderTest extends TestCase { + private IDBConnection $connection; + private ShardConnectionManager $shardConnectionManager; + private AutoIncrementHandler $autoIncrementHandler; + + protected function setUp(): void { + if (PHP_INT_SIZE < 8) { + $this->markTestSkipped('Test requires 64bit'); + } + $this->connection = Server::get(IDBConnection::class); + $this->shardConnectionManager = Server::get(ShardConnectionManager::class); + $this->autoIncrementHandler = Server::get(AutoIncrementHandler::class); + + $this->setupFileCache(); + } + + protected function tearDown(): void { + $this->cleanupDb(); + parent::tearDown(); + } + + + private function getQueryBuilder(): PartitionedQueryBuilder { + $builder = $this->connection->getQueryBuilder(); + if ($builder instanceof PartitionedQueryBuilder) { + return $builder; + } else { + return new PartitionedQueryBuilder($builder, [], $this->shardConnectionManager, $this->autoIncrementHandler); + } + } + + private function setupFileCache(): void { + $this->cleanupDb(); + $query = $this->getQueryBuilder(); + $query->insert('storages') + ->values([ + 'numeric_id' => $query->createNamedParameter(1001001, IQueryBuilder::PARAM_INT), + 'id' => $query->createNamedParameter('test1'), + ]); + $query->executeStatement(); + + $query = $this->getQueryBuilder(); + $query->insert('filecache') + ->values([ + 'storage' => $query->createNamedParameter(1001001, IQueryBuilder::PARAM_INT), + 'path' => $query->createNamedParameter('file1'), + 'path_hash' => $query->createNamedParameter(md5('file1')), + ]); + $query->executeStatement(); + $fileId = $query->getLastInsertId(); + + $query = $this->getQueryBuilder(); + $query->insert('filecache_extended') + ->hintShardKey('storage', 1001001) + ->values([ + 'fileid' => $query->createNamedParameter($fileId, IQueryBuilder::PARAM_INT), + 'upload_time' => $query->createNamedParameter(1234, IQueryBuilder::PARAM_INT), + ]); + $query->executeStatement(); + + $query = $this->getQueryBuilder(); + $query->insert('mounts') + ->values([ + 'storage_id' => $query->createNamedParameter(1001001, IQueryBuilder::PARAM_INT), + 'user_id' => $query->createNamedParameter('partitioned_test'), + 'mount_point' => $query->createNamedParameter('/mount/point'), + 'mount_provider_class' => $query->createNamedParameter('test'), + 'root_id' => $query->createNamedParameter($fileId, IQueryBuilder::PARAM_INT), + ]); + $query->executeStatement(); + } + + private function cleanupDb(): void { + $query = $this->getQueryBuilder(); + $query->delete('storages') + ->where($query->expr()->gt('numeric_id', $query->createNamedParameter(1000000, IQueryBuilder::PARAM_INT))); + $query->executeStatement(); + + $query = $this->getQueryBuilder(); + $query->delete('filecache') + ->where($query->expr()->gt('storage', $query->createNamedParameter(1000000, IQueryBuilder::PARAM_INT))) + ->runAcrossAllShards(); + $query->executeStatement(); + + $query = $this->getQueryBuilder(); + $query->delete('filecache_extended') + ->runAcrossAllShards(); + $query->executeStatement(); + + $query = $this->getQueryBuilder(); + $query->delete('mounts') + ->where($query->expr()->like('user_id', $query->createNamedParameter('partitioned_%'))); + $query->executeStatement(); + } + + public function testSimpleOnlyPartitionQuery(): void { + $builder = $this->getQueryBuilder(); + $builder->addPartition(new PartitionSplit('filecache', ['filecache'])); + + // query borrowed from UserMountCache + $query = $builder->select('path') + ->from('filecache') + ->where($builder->expr()->eq('storage', $builder->createNamedParameter(1001001, IQueryBuilder::PARAM_INT))); + + $results = $query->executeQuery()->fetchAll(); + $this->assertCount(1, $results); + $this->assertEquals($results[0]['path'], 'file1'); + } + + public function testSimplePartitionedQuery(): void { + $builder = $this->getQueryBuilder(); + $builder->addPartition(new PartitionSplit('filecache', ['filecache'])); + + // query borrowed from UserMountCache + $query = $builder->select('storage_id', 'root_id', 'user_id', 'mount_point', 'mount_id', 'f.path', 'mount_provider_class') + ->from('mounts', 'm') + ->innerJoin('m', 'filecache', 'f', $builder->expr()->eq('m.root_id', 'f.fileid')) + ->where($builder->expr()->eq('storage_id', $builder->createNamedParameter(1001001, IQueryBuilder::PARAM_INT))); + + $query->andWhere($builder->expr()->eq('user_id', $builder->createNamedParameter('partitioned_test'))); + + $this->assertEquals(2, $query->getPartitionCount()); + + $results = $query->executeQuery()->fetchAll(); + $this->assertCount(1, $results); + $this->assertEquals($results[0]['user_id'], 'partitioned_test'); + $this->assertEquals($results[0]['mount_point'], '/mount/point'); + $this->assertEquals($results[0]['mount_provider_class'], 'test'); + $this->assertEquals($results[0]['path'], 'file1'); + } + + public function testMultiTablePartitionedQuery(): void { + $builder = $this->getQueryBuilder(); + $builder->addPartition(new PartitionSplit('filecache', ['filecache', 'filecache_extended'])); + + $query = $builder->select('storage_id', 'root_id', 'user_id', 'mount_point', 'mount_id', 'f.path', 'mount_provider_class', 'fe.upload_time') + ->from('mounts', 'm') + ->innerJoin('m', 'filecache', 'f', $builder->expr()->eq('m.root_id', 'f.fileid')) + ->innerJoin('f', 'filecache_extended', 'fe', $builder->expr()->eq('f.fileid', 'fe.fileid')) + ->where($builder->expr()->eq('storage_id', $builder->createNamedParameter(1001001, IQueryBuilder::PARAM_INT))); + + $query->andWhere($builder->expr()->eq('user_id', $builder->createNamedParameter('partitioned_test'))); + + $this->assertEquals(2, $query->getPartitionCount()); + + $results = $query->executeQuery()->fetchAll(); + $this->assertCount(1, $results); + $this->assertEquals($results[0]['user_id'], 'partitioned_test'); + $this->assertEquals($results[0]['mount_point'], '/mount/point'); + $this->assertEquals($results[0]['mount_provider_class'], 'test'); + $this->assertEquals($results[0]['path'], 'file1'); + $this->assertEquals($results[0]['upload_time'], 1234); + } + + public function testPartitionedQueryFromSplit(): void { + $builder = $this->getQueryBuilder(); + $builder->addPartition(new PartitionSplit('filecache', ['filecache'])); + + $query = $builder->select('storage', 'm.root_id', 'm.user_id', 'm.mount_point', 'm.mount_id', 'path', 'm.mount_provider_class') + ->from('filecache', 'f') + ->innerJoin('f', 'mounts', 'm', $builder->expr()->eq('m.root_id', 'f.fileid')); + $query->where($builder->expr()->eq('storage', $builder->createNamedParameter(1001001, IQueryBuilder::PARAM_INT))); + + $query->andWhere($builder->expr()->eq('m.user_id', $builder->createNamedParameter('partitioned_test'))); + + $this->assertEquals(2, $query->getPartitionCount()); + + $results = $query->executeQuery()->fetchAll(); + $this->assertCount(1, $results); + $this->assertEquals($results[0]['user_id'], 'partitioned_test'); + $this->assertEquals($results[0]['mount_point'], '/mount/point'); + $this->assertEquals($results[0]['mount_provider_class'], 'test'); + $this->assertEquals($results[0]['path'], 'file1'); + } + + public function testMultiJoinPartitionedQuery(): void { + $builder = $this->getQueryBuilder(); + $builder->addPartition(new PartitionSplit('filecache', ['filecache'])); + + // query borrowed from UserMountCache + $query = $builder->select('storage_id', 'root_id', 'user_id', 'mount_point', 'mount_id', 'f.path', 'mount_provider_class') + ->selectAlias('s.id', 'storage_string_id') + ->from('mounts', 'm') + ->innerJoin('m', 'filecache', 'f', $builder->expr()->eq('m.root_id', 'f.fileid')) + ->innerJoin('f', 'storages', 's', $builder->expr()->eq('f.storage', 's.numeric_id')) + ->where($builder->expr()->eq('storage_id', $builder->createNamedParameter(1001001, IQueryBuilder::PARAM_INT))); + + $query->andWhere($builder->expr()->eq('user_id', $builder->createNamedParameter('partitioned_test'))); + + $this->assertEquals(3, $query->getPartitionCount()); + + $results = $query->executeQuery()->fetchAll(); + $this->assertCount(1, $results); + $this->assertEquals($results[0]['user_id'], 'partitioned_test'); + $this->assertEquals($results[0]['mount_point'], '/mount/point'); + $this->assertEquals($results[0]['mount_provider_class'], 'test'); + $this->assertEquals($results[0]['path'], 'file1'); + $this->assertEquals($results[0]['storage_string_id'], 'test1'); + } +} diff --git a/tests/lib/DB/QueryBuilder/QueryBuilderTest.php b/tests/lib/DB/QueryBuilder/QueryBuilderTest.php new file mode 100644 index 00000000000..990191a9ff5 --- /dev/null +++ b/tests/lib/DB/QueryBuilder/QueryBuilderTest.php @@ -0,0 +1,1484 @@ +<?php + +/** + * SPDX-FileCopyrightText: 2016-2024 Nextcloud GmbH and Nextcloud contributors + * SPDX-FileCopyrightText: 2016 ownCloud, Inc. + * SPDX-License-Identifier: AGPL-3.0-only + */ + +namespace Test\DB\QueryBuilder; + +use Doctrine\DBAL\Query\Expression\CompositeExpression; +use Doctrine\DBAL\Query\QueryException; +use OC\DB\QueryBuilder\Literal; +use OC\DB\QueryBuilder\Parameter; +use OC\DB\QueryBuilder\QueryBuilder; +use OC\SystemConfig; +use OCP\DB\IResult; +use OCP\DB\QueryBuilder\IQueryBuilder; +use OCP\DB\QueryBuilder\IQueryFunction; +use OCP\IDBConnection; +use OCP\Server; +use Psr\Log\LoggerInterface; + +/** + * Class QueryBuilderTest + * + * @group DB + * + * @package Test\DB\QueryBuilder + */ +class QueryBuilderTest extends \Test\TestCase { + /** @var QueryBuilder */ + protected $queryBuilder; + + /** @var IDBConnection */ + protected $connection; + + /** @var SystemConfig|\PHPUnit\Framework\MockObject\MockObject */ + protected $config; + + /** @var LoggerInterface|\PHPUnit\Framework\MockObject\MockObject */ + protected $logger; + + protected function setUp(): void { + parent::setUp(); + + $this->connection = Server::get(IDBConnection::class); + $this->config = $this->createMock(SystemConfig::class); + $this->logger = $this->createMock(LoggerInterface::class); + $this->queryBuilder = new QueryBuilder($this->connection, $this->config, $this->logger); + } + + protected function createTestingRows($appId = 'testFirstResult') { + $qB = $this->connection->getQueryBuilder(); + for ($i = 1; $i < 10; $i++) { + $qB->insert('*PREFIX*appconfig') + ->values([ + 'appid' => $qB->expr()->literal($appId), + 'configkey' => $qB->expr()->literal('testing' . $i), + 'configvalue' => $qB->expr()->literal(100 - $i), + ]) + ->execute(); + } + } + + protected function getTestingRows(QueryBuilder $queryBuilder) { + $queryBuilder->select('configvalue') + ->from('*PREFIX*appconfig') + ->where($queryBuilder->expr()->eq( + 'appid', + $queryBuilder->expr()->literal('testFirstResult') + )) + ->orderBy('configkey', 'ASC'); + + $query = $queryBuilder->execute(); + $rows = []; + while ($row = $query->fetch()) { + $rows[] = $row['configvalue']; + } + $query->closeCursor(); + + return $rows; + } + + protected function deleteTestingRows($appId = 'testFirstResult') { + $qB = $this->connection->getQueryBuilder(); + + $qB->delete('*PREFIX*appconfig') + ->where($qB->expr()->eq('appid', $qB->expr()->literal($appId))) + ->execute(); + } + + public static function dataFirstResult(): array { + return [ + [0, [99, 98, 97, 96, 95, 94, 93, 92, 91]], + [0, [99, 98, 97, 96, 95, 94, 93, 92, 91]], + [1, [98, 97, 96, 95, 94, 93, 92, 91]], + [5, [94, 93, 92, 91]], + ]; + } + + /** + * + * @param int|null $firstResult + * @param array $expectedSet + */ + #[\PHPUnit\Framework\Attributes\DataProvider('dataFirstResult')] + public function testFirstResult($firstResult, $expectedSet): void { + $this->deleteTestingRows(); + $this->createTestingRows(); + + if ($firstResult !== null) { + $this->queryBuilder->setFirstResult($firstResult); + } + + $this->assertSame( + $firstResult ?? 0, + $this->queryBuilder->getFirstResult() + ); + + $rows = $this->getTestingRows($this->queryBuilder); + + $this->assertCount(sizeof($expectedSet), $rows); + $this->assertEquals($expectedSet, $rows); + + $this->deleteTestingRows(); + } + + public static function dataMaxResults(): array { + return [ + [null, [99, 98, 97, 96, 95, 94, 93, 92, 91]], + // Limit 0 gives mixed results: either all entries or none is returned + //[0, []], + [1, [99]], + [5, [99, 98, 97, 96, 95]], + ]; + } + + /** + * + * @param int $maxResult + * @param array $expectedSet + */ + #[\PHPUnit\Framework\Attributes\DataProvider('dataMaxResults')] + public function testMaxResults($maxResult, $expectedSet): void { + $this->deleteTestingRows(); + $this->createTestingRows(); + + if ($maxResult !== null) { + $this->queryBuilder->setMaxResults($maxResult); + } + + $this->assertSame( + $maxResult, + $this->queryBuilder->getMaxResults() + ); + + $rows = $this->getTestingRows($this->queryBuilder); + + $this->assertCount(sizeof($expectedSet), $rows); + $this->assertEquals($expectedSet, $rows); + + $this->deleteTestingRows(); + } + + public function dataSelect(): array { + $config = $this->createMock(SystemConfig::class); + $logger = $this->createMock(LoggerInterface::class); + $queryBuilder = new QueryBuilder(Server::get(IDBConnection::class), $config, $logger); + return [ + // select('column1') + [['configvalue'], ['configvalue' => '99']], + + // select('column1', 'column2') + [['configvalue', 'configkey'], ['configvalue' => '99', 'configkey' => 'testing1']], + + // select(['column1', 'column2']) + [[['configvalue', 'configkey']], ['configvalue' => '99', 'configkey' => 'testing1']], + + // select(new Literal('column1')) + [[$queryBuilder->expr()->literal('column1')], [], 'column1'], + + // select('column1', 'column2') + [[$queryBuilder->expr()->literal('column1'), 'configkey'], ['configkey' => 'testing1'], 'column1'], + + // select(['column1', 'column2']) + [[[$queryBuilder->expr()->literal('column1'), 'configkey']], ['configkey' => 'testing1'], 'column1'], + ]; + } + + /** + * + * @param array $selectArguments + * @param array $expected + * @param string $expectedLiteral + */ + #[\PHPUnit\Framework\Attributes\DataProvider('dataSelect')] + public function testSelect($selectArguments, $expected, $expectedLiteral = ''): void { + $this->deleteTestingRows(); + $this->createTestingRows(); + + call_user_func_array( + [$this->queryBuilder, 'select'], + $selectArguments + ); + + $this->queryBuilder->from('*PREFIX*appconfig') + ->where($this->queryBuilder->expr()->eq( + 'appid', + $this->queryBuilder->expr()->literal('testFirstResult') + )) + ->orderBy('configkey', 'ASC') + ->setMaxResults(1); + + $query = $this->queryBuilder->execute(); + $row = $query->fetch(); + $query->closeCursor(); + + foreach ($expected as $key => $value) { + $this->assertArrayHasKey($key, $row); + $this->assertEquals($value, $row[$key]); + unset($row[$key]); + } + + if ($expectedLiteral) { + $this->assertEquals([$expectedLiteral], array_values($row)); + } else { + $this->assertEmpty($row); + } + + $this->deleteTestingRows(); + } + + public function dataSelectAlias(): array { + $config = $this->createMock(SystemConfig::class); + $logger = $this->createMock(LoggerInterface::class); + $queryBuilder = new QueryBuilder(Server::get(IDBConnection::class), $config, $logger); + return [ + ['configvalue', 'cv', ['cv' => '99']], + [$queryBuilder->expr()->literal('column1'), 'thing', ['thing' => 'column1']], + ]; + } + + /** + * + * @param mixed $select + * @param array $alias + * @param array $expected + */ + #[\PHPUnit\Framework\Attributes\DataProvider('dataSelectAlias')] + public function testSelectAlias($select, $alias, $expected): void { + $this->deleteTestingRows(); + $this->createTestingRows(); + + $this->queryBuilder->selectAlias($select, $alias); + + $this->queryBuilder->from('*PREFIX*appconfig') + ->where($this->queryBuilder->expr()->eq( + 'appid', + $this->queryBuilder->expr()->literal('testFirstResult') + )) + ->orderBy('configkey', 'ASC') + ->setMaxResults(1); + + $query = $this->queryBuilder->execute(); + $row = $query->fetch(); + $query->closeCursor(); + + $this->assertEquals( + $expected, + $row + ); + + $this->deleteTestingRows(); + } + + public function testSelectDistinct(): void { + $this->deleteTestingRows('testFirstResult1'); + $this->deleteTestingRows('testFirstResult2'); + $this->createTestingRows('testFirstResult1'); + $this->createTestingRows('testFirstResult2'); + + $this->queryBuilder->selectDistinct('appid'); + + $this->queryBuilder->from('*PREFIX*appconfig') + ->where($this->queryBuilder->expr()->in( + 'appid', + [$this->queryBuilder->expr()->literal('testFirstResult1'), $this->queryBuilder->expr()->literal('testFirstResult2')] + )) + ->orderBy('appid', 'DESC'); + + $query = $this->queryBuilder->execute(); + $rows = $query->fetchAll(); + $query->closeCursor(); + + $this->assertEquals( + [['appid' => 'testFirstResult2'], ['appid' => 'testFirstResult1']], + $rows + ); + + $this->deleteTestingRows('testFirstResult1'); + $this->deleteTestingRows('testFirstResult2'); + } + + public function testSelectDistinctMultiple(): void { + $this->deleteTestingRows('testFirstResult1'); + $this->deleteTestingRows('testFirstResult2'); + $this->createTestingRows('testFirstResult1'); + $this->createTestingRows('testFirstResult2'); + + $this->queryBuilder->selectDistinct(['appid', 'configkey']); + + $this->queryBuilder->from('*PREFIX*appconfig') + ->where($this->queryBuilder->expr()->eq( + 'appid', + $this->queryBuilder->expr()->literal('testFirstResult1') + )) + ->orderBy('configkey', 'ASC'); + + $query = $this->queryBuilder->execute(); + $rows = $query->fetchAll(); + $query->closeCursor(); + + $this->assertEquals( + [ + ['appid' => 'testFirstResult1', 'configkey' => 'testing1'], + ['appid' => 'testFirstResult1', 'configkey' => 'testing2'], + ['appid' => 'testFirstResult1', 'configkey' => 'testing3'], + ['appid' => 'testFirstResult1', 'configkey' => 'testing4'], + ['appid' => 'testFirstResult1', 'configkey' => 'testing5'], + ['appid' => 'testFirstResult1', 'configkey' => 'testing6'], + ['appid' => 'testFirstResult1', 'configkey' => 'testing7'], + ['appid' => 'testFirstResult1', 'configkey' => 'testing8'], + ['appid' => 'testFirstResult1', 'configkey' => 'testing9'], + ], + $rows + ); + + $this->deleteTestingRows('testFirstResult1'); + $this->deleteTestingRows('testFirstResult2'); + } + + public function dataAddSelect(): array { + $config = $this->createMock(SystemConfig::class); + $logger = $this->createMock(LoggerInterface::class); + $queryBuilder = new QueryBuilder(Server::get(IDBConnection::class), $config, $logger); + return [ + // addSelect('column1') + [['configvalue'], ['appid' => 'testFirstResult', 'configvalue' => '99']], + + // addSelect('column1', 'column2') + [['configvalue', 'configkey'], ['appid' => 'testFirstResult', 'configvalue' => '99', 'configkey' => 'testing1']], + + // addSelect(['column1', 'column2']) + [[['configvalue', 'configkey']], ['appid' => 'testFirstResult', 'configvalue' => '99', 'configkey' => 'testing1']], + + // select(new Literal('column1')) + [[$queryBuilder->expr()->literal('column1')], ['appid' => 'testFirstResult'], 'column1'], + + // select('column1', 'column2') + [[$queryBuilder->expr()->literal('column1'), 'configkey'], ['appid' => 'testFirstResult', 'configkey' => 'testing1'], 'column1'], + + // select(['column1', 'column2']) + [[[$queryBuilder->expr()->literal('column1'), 'configkey']], ['appid' => 'testFirstResult', 'configkey' => 'testing1'], 'column1'], + ]; + } + + /** + * + * @param array $selectArguments + * @param array $expected + * @param string $expectedLiteral + */ + #[\PHPUnit\Framework\Attributes\DataProvider('dataAddSelect')] + public function testAddSelect($selectArguments, $expected, $expectedLiteral = ''): void { + $this->deleteTestingRows(); + $this->createTestingRows(); + + $this->queryBuilder->select('appid'); + + call_user_func_array( + [$this->queryBuilder, 'addSelect'], + $selectArguments + ); + + $this->queryBuilder->from('*PREFIX*appconfig') + ->where($this->queryBuilder->expr()->eq( + 'appid', + $this->queryBuilder->expr()->literal('testFirstResult') + )) + ->orderBy('configkey', 'ASC') + ->setMaxResults(1); + + $query = $this->queryBuilder->execute(); + $row = $query->fetch(); + $query->closeCursor(); + + foreach ($expected as $key => $value) { + $this->assertArrayHasKey($key, $row); + $this->assertEquals($value, $row[$key]); + unset($row[$key]); + } + + if ($expectedLiteral) { + $this->assertEquals([$expectedLiteral], array_values($row)); + } else { + $this->assertEmpty($row); + } + + $this->deleteTestingRows(); + } + + public static function dataDelete(): array { + return [ + ['data', null, ['table' => '`*PREFIX*data`', 'alias' => null], '`*PREFIX*data`'], + ['data', 't', ['table' => '`*PREFIX*data`', 'alias' => 't'], '`*PREFIX*data` t'], + ]; + } + + /** + * + * @param string $tableName + * @param string $tableAlias + * @param array $expectedQueryPart + * @param string $expectedQuery + */ + #[\PHPUnit\Framework\Attributes\DataProvider('dataDelete')] + public function testDelete($tableName, $tableAlias, $expectedQueryPart, $expectedQuery): void { + $this->queryBuilder->delete($tableName, $tableAlias); + + $this->assertSame( + $expectedQueryPart, + $this->queryBuilder->getQueryPart('from') + ); + + $this->assertSame( + 'DELETE FROM ' . $expectedQuery, + $this->queryBuilder->getSQL() + ); + } + + public static function dataUpdate(): array { + return [ + ['data', null, ['table' => '`*PREFIX*data`', 'alias' => null], '`*PREFIX*data`'], + ['data', 't', ['table' => '`*PREFIX*data`', 'alias' => 't'], '`*PREFIX*data` t'], + ]; + } + + /** + * + * @param string $tableName + * @param string $tableAlias + * @param array $expectedQueryPart + * @param string $expectedQuery + */ + #[\PHPUnit\Framework\Attributes\DataProvider('dataUpdate')] + public function testUpdate($tableName, $tableAlias, $expectedQueryPart, $expectedQuery): void { + $this->queryBuilder->update($tableName, $tableAlias); + + $this->assertSame( + $expectedQueryPart, + $this->queryBuilder->getQueryPart('from') + ); + + $this->assertSame( + 'UPDATE ' . $expectedQuery . ' SET ', + $this->queryBuilder->getSQL() + ); + } + + public static function dataInsert(): array { + return [ + ['data', ['table' => '`*PREFIX*data`'], '`*PREFIX*data`'], + ]; + } + + /** + * + * @param string $tableName + * @param array $expectedQueryPart + * @param string $expectedQuery + */ + #[\PHPUnit\Framework\Attributes\DataProvider('dataInsert')] + public function testInsert($tableName, $expectedQueryPart, $expectedQuery): void { + $this->queryBuilder->insert($tableName); + + $this->assertSame( + $expectedQueryPart, + $this->queryBuilder->getQueryPart('from') + ); + + $this->assertSame( + 'INSERT INTO ' . $expectedQuery . ' () VALUES()', + $this->queryBuilder->getSQL() + ); + } + + public function dataFrom(): array { + $config = $this->createMock(SystemConfig::class); + $logger = $this->createMock(LoggerInterface::class); + $qb = new QueryBuilder(Server::get(IDBConnection::class), $config, $logger); + return [ + [$qb->createFunction('(' . $qb->select('*')->from('test')->getSQL() . ')'), 'q', null, null, [ + ['table' => '(SELECT * FROM `*PREFIX*test`)', 'alias' => '`q`'] + ], '(SELECT * FROM `*PREFIX*test`) `q`'], + ['data', null, null, null, [['table' => '`*PREFIX*data`', 'alias' => null]], '`*PREFIX*data`'], + ['data', 't', null, null, [['table' => '`*PREFIX*data`', 'alias' => '`t`']], '`*PREFIX*data` `t`'], + ['data1', null, 'data2', null, [ + ['table' => '`*PREFIX*data1`', 'alias' => null], + ['table' => '`*PREFIX*data2`', 'alias' => null] + ], '`*PREFIX*data1`, `*PREFIX*data2`'], + ['data', 't1', 'data', 't2', [ + ['table' => '`*PREFIX*data`', 'alias' => '`t1`'], + ['table' => '`*PREFIX*data`', 'alias' => '`t2`'] + ], '`*PREFIX*data` `t1`, `*PREFIX*data` `t2`'], + ]; + } + + /** + * + * @param string|IQueryFunction $table1Name + * @param string $table1Alias + * @param string|IQueryFunction $table2Name + * @param string $table2Alias + * @param array $expectedQueryPart + * @param string $expectedQuery + */ + #[\PHPUnit\Framework\Attributes\DataProvider('dataFrom')] + public function testFrom($table1Name, $table1Alias, $table2Name, $table2Alias, $expectedQueryPart, $expectedQuery): void { + $this->queryBuilder->from($table1Name, $table1Alias); + if ($table2Name !== null) { + $this->queryBuilder->from($table2Name, $table2Alias); + } + + $this->assertSame( + $expectedQueryPart, + $this->queryBuilder->getQueryPart('from') + ); + + $this->assertSame( + 'SELECT FROM ' . $expectedQuery, + $this->queryBuilder->getSQL() + ); + } + + public static function dataJoin(): array { + return [ + [ + 'd1', 'data2', null, null, + ['`d1`' => [['joinType' => 'inner', 'joinTable' => '`*PREFIX*data2`', 'joinAlias' => null, 'joinCondition' => null]]], + '`*PREFIX*data1` `d1` INNER JOIN `*PREFIX*data2` ' + ], + [ + 'd1', 'data2', 'd2', null, + ['`d1`' => [['joinType' => 'inner', 'joinTable' => '`*PREFIX*data2`', 'joinAlias' => '`d2`', 'joinCondition' => null]]], + '`*PREFIX*data1` `d1` INNER JOIN `*PREFIX*data2` `d2`' + ], + [ + 'd1', 'data2', 'd2', '`d1`.`field1` = `d2`.`field2`', + ['`d1`' => [['joinType' => 'inner', 'joinTable' => '`*PREFIX*data2`', 'joinAlias' => '`d2`', 'joinCondition' => '`d1`.`field1` = `d2`.`field2`']]], + '`*PREFIX*data1` `d1` INNER JOIN `*PREFIX*data2` `d2` ON `d1`.`field1` = `d2`.`field2`' + ], + + ]; + } + + /** + * + * @param string $fromAlias + * @param string $tableName + * @param string $tableAlias + * @param string $condition + * @param array $expectedQueryPart + * @param string $expectedQuery + */ + #[\PHPUnit\Framework\Attributes\DataProvider('dataJoin')] + public function testJoin($fromAlias, $tableName, $tableAlias, $condition, $expectedQueryPart, $expectedQuery): void { + $this->queryBuilder->from('data1', 'd1'); + $this->queryBuilder->join( + $fromAlias, + $tableName, + $tableAlias, + $condition + ); + + $this->assertSame( + $expectedQueryPart, + $this->queryBuilder->getQueryPart('join') + ); + + $this->assertSame( + 'SELECT FROM ' . $expectedQuery, + $this->queryBuilder->getSQL() + ); + } + + /** + * + * @param string $fromAlias + * @param string $tableName + * @param string $tableAlias + * @param string $condition + * @param array $expectedQueryPart + * @param string $expectedQuery + */ + #[\PHPUnit\Framework\Attributes\DataProvider('dataJoin')] + public function testInnerJoin($fromAlias, $tableName, $tableAlias, $condition, $expectedQueryPart, $expectedQuery): void { + $this->queryBuilder->from('data1', 'd1'); + $this->queryBuilder->innerJoin( + $fromAlias, + $tableName, + $tableAlias, + $condition + ); + + $this->assertSame( + $expectedQueryPart, + $this->queryBuilder->getQueryPart('join') + ); + + $this->assertSame( + 'SELECT FROM ' . $expectedQuery, + $this->queryBuilder->getSQL() + ); + } + + public static function dataLeftJoin(): array { + return [ + [ + 'd1', 'data2', null, null, + ['`d1`' => [['joinType' => 'left', 'joinTable' => '`*PREFIX*data2`', 'joinAlias' => null, 'joinCondition' => null]]], + '`*PREFIX*data1` `d1` LEFT JOIN `*PREFIX*data2` ' + ], + [ + 'd1', 'data2', 'd2', null, + ['`d1`' => [['joinType' => 'left', 'joinTable' => '`*PREFIX*data2`', 'joinAlias' => '`d2`', 'joinCondition' => null]]], + '`*PREFIX*data1` `d1` LEFT JOIN `*PREFIX*data2` `d2`' + ], + [ + 'd1', 'data2', 'd2', '`d1`.`field1` = `d2`.`field2`', + ['`d1`' => [['joinType' => 'left', 'joinTable' => '`*PREFIX*data2`', 'joinAlias' => '`d2`', 'joinCondition' => '`d1`.`field1` = `d2`.`field2`']]], + '`*PREFIX*data1` `d1` LEFT JOIN `*PREFIX*data2` `d2` ON `d1`.`field1` = `d2`.`field2`' + ], + ]; + } + + /** + * + * @param string $fromAlias + * @param string $tableName + * @param string $tableAlias + * @param string $condition + * @param array $expectedQueryPart + * @param string $expectedQuery + */ + #[\PHPUnit\Framework\Attributes\DataProvider('dataLeftJoin')] + public function testLeftJoin($fromAlias, $tableName, $tableAlias, $condition, $expectedQueryPart, $expectedQuery): void { + $this->queryBuilder->from('data1', 'd1'); + $this->queryBuilder->leftJoin( + $fromAlias, + $tableName, + $tableAlias, + $condition + ); + + $this->assertSame( + $expectedQueryPart, + $this->queryBuilder->getQueryPart('join') + ); + + $this->assertSame( + 'SELECT FROM ' . $expectedQuery, + $this->queryBuilder->getSQL() + ); + } + + public static function dataRightJoin(): array { + return [ + [ + 'd1', 'data2', null, null, + ['`d1`' => [['joinType' => 'right', 'joinTable' => '`*PREFIX*data2`', 'joinAlias' => null, 'joinCondition' => null]]], + '`*PREFIX*data1` `d1` RIGHT JOIN `*PREFIX*data2` ' + ], + [ + 'd1', 'data2', 'd2', null, + ['`d1`' => [['joinType' => 'right', 'joinTable' => '`*PREFIX*data2`', 'joinAlias' => '`d2`', 'joinCondition' => null]]], + '`*PREFIX*data1` `d1` RIGHT JOIN `*PREFIX*data2` `d2`' + ], + [ + 'd1', 'data2', 'd2', '`d1`.`field1` = `d2`.`field2`', + ['`d1`' => [['joinType' => 'right', 'joinTable' => '`*PREFIX*data2`', 'joinAlias' => '`d2`', 'joinCondition' => '`d1`.`field1` = `d2`.`field2`']]], + '`*PREFIX*data1` `d1` RIGHT JOIN `*PREFIX*data2` `d2` ON `d1`.`field1` = `d2`.`field2`' + ], + ]; + } + + /** + * + * @param string $fromAlias + * @param string $tableName + * @param string $tableAlias + * @param string $condition + * @param array $expectedQueryPart + * @param string $expectedQuery + */ + #[\PHPUnit\Framework\Attributes\DataProvider('dataRightJoin')] + public function testRightJoin($fromAlias, $tableName, $tableAlias, $condition, $expectedQueryPart, $expectedQuery): void { + $this->queryBuilder->from('data1', 'd1'); + $this->queryBuilder->rightJoin( + $fromAlias, + $tableName, + $tableAlias, + $condition + ); + + $this->assertSame( + $expectedQueryPart, + $this->queryBuilder->getQueryPart('join') + ); + + $this->assertSame( + 'SELECT FROM ' . $expectedQuery, + $this->queryBuilder->getSQL() + ); + } + + public static function dataSet(): array { + return [ + ['column1', new Literal('value'), null, null, ['`column1` = value'], '`column1` = value'], + ['column1', new Parameter(':param'), null, null, ['`column1` = :param'], '`column1` = :param'], + ['column1', 'column2', null, null, ['`column1` = `column2`'], '`column1` = `column2`'], + ['column1', 'column2', 'column3', new Literal('value'), ['`column1` = `column2`', '`column3` = value'], '`column1` = `column2`, `column3` = value'], + ]; + } + + /** + * + * @param string $partOne1 + * @param string $partOne2 + * @param string $partTwo1 + * @param string $partTwo2 + * @param array $expectedQueryPart + * @param string $expectedQuery + */ + #[\PHPUnit\Framework\Attributes\DataProvider('dataSet')] + public function testSet($partOne1, $partOne2, $partTwo1, $partTwo2, $expectedQueryPart, $expectedQuery): void { + $this->queryBuilder->update('data'); + $this->queryBuilder->set($partOne1, $partOne2); + if ($partTwo1 !== null) { + $this->queryBuilder->set($partTwo1, $partTwo2); + } + + $this->assertSame( + $expectedQueryPart, + $this->queryBuilder->getQueryPart('set') + ); + + $this->assertSame( + 'UPDATE `*PREFIX*data` SET ' . $expectedQuery, + $this->queryBuilder->getSQL() + ); + } + + public static function dataWhere(): array { + return [ + [['where1'], new CompositeExpression('AND', ['where1']), 'where1'], + [['where1', 'where2'], new CompositeExpression('AND', ['where1', 'where2']), '(where1) AND (where2)'], + ]; + } + + /** + * + * @param array $whereArguments + * @param array $expectedQueryPart + * @param string $expectedQuery + */ + #[\PHPUnit\Framework\Attributes\DataProvider('dataWhere')] + public function testWhere($whereArguments, $expectedQueryPart, $expectedQuery): void { + $this->queryBuilder->select('column'); + call_user_func_array( + [$this->queryBuilder, 'where'], + $whereArguments + ); + + $this->assertEquals( + $expectedQueryPart, + $this->queryBuilder->getQueryPart('where') + ); + + $this->assertSame( + 'SELECT `column` WHERE ' . $expectedQuery, + $this->queryBuilder->getSQL() + ); + } + + /** + * + * @param array $whereArguments + * @param array $expectedQueryPart + * @param string $expectedQuery + */ + #[\PHPUnit\Framework\Attributes\DataProvider('dataWhere')] + public function testAndWhere($whereArguments, $expectedQueryPart, $expectedQuery): void { + $this->queryBuilder->select('column'); + call_user_func_array( + [$this->queryBuilder, 'andWhere'], + $whereArguments + ); + + $this->assertEquals( + $expectedQueryPart, + $this->queryBuilder->getQueryPart('where') + ); + + $this->assertSame( + 'SELECT `column` WHERE ' . $expectedQuery, + $this->queryBuilder->getSQL() + ); + } + + public static function dataOrWhere(): array { + return [ + [['where1'], new CompositeExpression('OR', ['where1']), 'where1'], + [['where1', 'where2'], new CompositeExpression('OR', ['where1', 'where2']), '(where1) OR (where2)'], + ]; + } + + /** + * + * @param array $whereArguments + * @param array $expectedQueryPart + * @param string $expectedQuery + */ + #[\PHPUnit\Framework\Attributes\DataProvider('dataOrWhere')] + public function testOrWhere($whereArguments, $expectedQueryPart, $expectedQuery): void { + $this->queryBuilder->select('column'); + call_user_func_array( + [$this->queryBuilder, 'orWhere'], + $whereArguments + ); + + $this->assertEquals( + $expectedQueryPart, + $this->queryBuilder->getQueryPart('where') + ); + + $this->assertSame( + 'SELECT `column` WHERE ' . $expectedQuery, + $this->queryBuilder->getSQL() + ); + } + + public static function dataGroupBy(): array { + return [ + [['column1'], ['`column1`'], '`column1`'], + [['column1', 'column2'], ['`column1`', '`column2`'], '`column1`, `column2`'], + ]; + } + + /** + * + * @param array $groupByArguments + * @param array $expectedQueryPart + * @param string $expectedQuery + */ + #[\PHPUnit\Framework\Attributes\DataProvider('dataGroupBy')] + public function testGroupBy($groupByArguments, $expectedQueryPart, $expectedQuery): void { + $this->queryBuilder->select('column'); + call_user_func_array( + [$this->queryBuilder, 'groupBy'], + $groupByArguments + ); + + $this->assertEquals( + $expectedQueryPart, + $this->queryBuilder->getQueryPart('groupBy') + ); + + $this->assertSame( + 'SELECT `column` GROUP BY ' . $expectedQuery, + $this->queryBuilder->getSQL() + ); + } + + public static function dataAddGroupBy(): array { + return [ + [['column2'], ['`column1`', '`column2`'], '`column1`, `column2`'], + [['column2', 'column3'], ['`column1`', '`column2`', '`column3`'], '`column1`, `column2`, `column3`'], + ]; + } + + /** + * + * @param array $groupByArguments + * @param array $expectedQueryPart + * @param string $expectedQuery + */ + #[\PHPUnit\Framework\Attributes\DataProvider('dataAddGroupBy')] + public function testAddGroupBy($groupByArguments, $expectedQueryPart, $expectedQuery): void { + $this->queryBuilder->select('column'); + $this->queryBuilder->groupBy('column1'); + call_user_func_array( + [$this->queryBuilder, 'addGroupBy'], + $groupByArguments + ); + + $this->assertEquals( + $expectedQueryPart, + $this->queryBuilder->getQueryPart('groupBy') + ); + + $this->assertSame( + 'SELECT `column` GROUP BY ' . $expectedQuery, + $this->queryBuilder->getSQL() + ); + } + + public static function dataSetValue(): array { + return [ + ['column', 'value', ['`column`' => 'value'], '(`column`) VALUES(value)'], + ]; + } + + /** + * + * @param string $column + * @param string $value + * @param array $expectedQueryPart + * @param string $expectedQuery + */ + #[\PHPUnit\Framework\Attributes\DataProvider('dataSetValue')] + public function testSetValue($column, $value, $expectedQueryPart, $expectedQuery): void { + $this->queryBuilder->insert('data'); + $this->queryBuilder->setValue($column, $value); + + $this->assertEquals( + $expectedQueryPart, + $this->queryBuilder->getQueryPart('values') + ); + + $this->assertSame( + 'INSERT INTO `*PREFIX*data` ' . $expectedQuery, + $this->queryBuilder->getSQL() + ); + } + + /** + * + * @param string $column + * @param string $value + * @param array $expectedQueryPart + * @param string $expectedQuery + */ + #[\PHPUnit\Framework\Attributes\DataProvider('dataSetValue')] + public function testValues($column, $value, $expectedQueryPart, $expectedQuery): void { + $this->queryBuilder->insert('data'); + $this->queryBuilder->values([ + $column => $value, + ]); + + $this->assertEquals( + $expectedQueryPart, + $this->queryBuilder->getQueryPart('values') + ); + + $this->assertSame( + 'INSERT INTO `*PREFIX*data` ' . $expectedQuery, + $this->queryBuilder->getSQL() + ); + } + + public static function dataHaving(): array { + return [ + [['condition1'], new CompositeExpression('AND', ['condition1']), 'HAVING condition1'], + [['condition1', 'condition2'], new CompositeExpression('AND', ['condition1', 'condition2']), 'HAVING (condition1) AND (condition2)'], + [ + [new CompositeExpression('OR', ['condition1', 'condition2'])], + new CompositeExpression('OR', ['condition1', 'condition2']), + 'HAVING (condition1) OR (condition2)' + ], + [ + [new CompositeExpression('AND', ['condition1', 'condition2'])], + new CompositeExpression('AND', ['condition1', 'condition2']), + 'HAVING (condition1) AND (condition2)' + ], + ]; + } + + /** + * + * @param array $havingArguments + * @param array $expectedQueryPart + * @param string $expectedQuery + */ + #[\PHPUnit\Framework\Attributes\DataProvider('dataHaving')] + public function testHaving($havingArguments, $expectedQueryPart, $expectedQuery): void { + call_user_func_array( + [$this->queryBuilder, 'having'], + $havingArguments + ); + + $this->assertEquals( + $expectedQueryPart, + $this->queryBuilder->getQueryPart('having') + ); + + $this->assertSame( + 'SELECT ' . $expectedQuery, + $this->queryBuilder->getSQL() + ); + } + + public static function dataAndHaving(): array { + return [ + [['condition2'], new CompositeExpression('AND', ['condition1', 'condition2']), 'HAVING (condition1) AND (condition2)'], + [['condition2', 'condition3'], new CompositeExpression('AND', ['condition1', 'condition2', 'condition3']), 'HAVING (condition1) AND (condition2) AND (condition3)'], + [ + [new CompositeExpression('OR', ['condition2', 'condition3'])], + new CompositeExpression('AND', ['condition1', new CompositeExpression('OR', ['condition2', 'condition3'])]), + 'HAVING (condition1) AND ((condition2) OR (condition3))' + ], + [ + [new CompositeExpression('AND', ['condition2', 'condition3'])], + new CompositeExpression('AND', ['condition1', new CompositeExpression('AND', ['condition2', 'condition3'])]), + 'HAVING (condition1) AND ((condition2) AND (condition3))' + ], + ]; + } + + /** + * + * @param array $havingArguments + * @param array $expectedQueryPart + * @param string $expectedQuery + */ + #[\PHPUnit\Framework\Attributes\DataProvider('dataAndHaving')] + public function testAndHaving($havingArguments, $expectedQueryPart, $expectedQuery): void { + $this->queryBuilder->having('condition1'); + call_user_func_array( + [$this->queryBuilder, 'andHaving'], + $havingArguments + ); + + $this->assertEquals( + $expectedQueryPart, + $this->queryBuilder->getQueryPart('having') + ); + + $this->assertSame( + 'SELECT ' . $expectedQuery, + $this->queryBuilder->getSQL() + ); + } + + public static function dataOrHaving(): array { + return [ + [['condition2'], new CompositeExpression('OR', ['condition1', 'condition2']), 'HAVING (condition1) OR (condition2)'], + [['condition2', 'condition3'], new CompositeExpression('OR', ['condition1', 'condition2', 'condition3']), 'HAVING (condition1) OR (condition2) OR (condition3)'], + [ + [new CompositeExpression('OR', ['condition2', 'condition3'])], + new CompositeExpression('OR', ['condition1', new CompositeExpression('OR', ['condition2', 'condition3'])]), + 'HAVING (condition1) OR ((condition2) OR (condition3))' + ], + [ + [new CompositeExpression('AND', ['condition2', 'condition3'])], + new CompositeExpression('OR', ['condition1', new CompositeExpression('AND', ['condition2', 'condition3'])]), + 'HAVING (condition1) OR ((condition2) AND (condition3))' + ], + ]; + } + + /** + * + * @param array $havingArguments + * @param array $expectedQueryPart + * @param string $expectedQuery + */ + #[\PHPUnit\Framework\Attributes\DataProvider('dataOrHaving')] + public function testOrHaving($havingArguments, $expectedQueryPart, $expectedQuery): void { + $this->queryBuilder->having('condition1'); + call_user_func_array( + [$this->queryBuilder, 'orHaving'], + $havingArguments + ); + + $this->assertEquals( + $expectedQueryPart, + $this->queryBuilder->getQueryPart('having') + ); + + $this->assertSame( + 'SELECT ' . $expectedQuery, + $this->queryBuilder->getSQL() + ); + } + + public static function dataOrderBy(): array { + return [ + ['column', null, ['`column` ASC'], 'ORDER BY `column` ASC'], + ['column', 'ASC', ['`column` ASC'], 'ORDER BY `column` ASC'], + ['column', 'DESC', ['`column` DESC'], 'ORDER BY `column` DESC'], + ]; + } + + /** + * + * @param string $sort + * @param string $order + * @param array $expectedQueryPart + * @param string $expectedQuery + */ + #[\PHPUnit\Framework\Attributes\DataProvider('dataOrderBy')] + public function testOrderBy($sort, $order, $expectedQueryPart, $expectedQuery): void { + $this->queryBuilder->orderBy($sort, $order); + + $this->assertEquals( + $expectedQueryPart, + $this->queryBuilder->getQueryPart('orderBy') + ); + + $this->assertSame( + 'SELECT ' . $expectedQuery, + $this->queryBuilder->getSQL() + ); + } + + public static function dataAddOrderBy(): array { + return [ + ['column2', null, null, ['`column1` ASC', '`column2` ASC'], 'ORDER BY `column1` ASC, `column2` ASC'], + ['column2', null, 'ASC', ['`column1` ASC', '`column2` ASC'], 'ORDER BY `column1` ASC, `column2` ASC'], + ['column2', null, 'DESC', ['`column1` DESC', '`column2` ASC'], 'ORDER BY `column1` DESC, `column2` ASC'], + ['column2', 'ASC', null, ['`column1` ASC', '`column2` ASC'], 'ORDER BY `column1` ASC, `column2` ASC'], + ['column2', 'ASC', 'ASC', ['`column1` ASC', '`column2` ASC'], 'ORDER BY `column1` ASC, `column2` ASC'], + ['column2', 'ASC', 'DESC', ['`column1` DESC', '`column2` ASC'], 'ORDER BY `column1` DESC, `column2` ASC'], + ['column2', 'DESC', null, ['`column1` ASC', '`column2` DESC'], 'ORDER BY `column1` ASC, `column2` DESC'], + ['column2', 'DESC', 'ASC', ['`column1` ASC', '`column2` DESC'], 'ORDER BY `column1` ASC, `column2` DESC'], + ['column2', 'DESC', 'DESC', ['`column1` DESC', '`column2` DESC'], 'ORDER BY `column1` DESC, `column2` DESC'], + ]; + } + + /** + * + * @param string $sort2 + * @param string $order2 + * @param string $order1 + * @param array $expectedQueryPart + * @param string $expectedQuery + */ + #[\PHPUnit\Framework\Attributes\DataProvider('dataAddOrderBy')] + public function testAddOrderBy($sort2, $order2, $order1, $expectedQueryPart, $expectedQuery): void { + $this->queryBuilder->orderBy('column1', $order1); + $this->queryBuilder->addOrderBy($sort2, $order2); + + $this->assertEquals( + $expectedQueryPart, + $this->queryBuilder->getQueryPart('orderBy') + ); + + $this->assertSame( + 'SELECT ' . $expectedQuery, + $this->queryBuilder->getSQL() + ); + } + + public function testGetLastInsertId(): void { + $qB = $this->connection->getQueryBuilder(); + + try { + $qB->getLastInsertId(); + $this->fail('getLastInsertId() should throw an exception, when being called before insert()'); + } catch (\BadMethodCallException $e) { + $this->addToAssertionCount(1); + } + + $qB->insert('properties') + ->values([ + 'userid' => $qB->expr()->literal('testFirstResult'), + 'propertypath' => $qB->expr()->literal('testing'), + 'propertyname' => $qB->expr()->literal('testing'), + 'propertyvalue' => $qB->expr()->literal('testing'), + ]) + ->execute(); + + $actual = $qB->getLastInsertId(); + + $this->assertNotNull($actual); + $this->assertIsInt($actual); + $this->assertEquals($this->connection->lastInsertId('*PREFIX*properties'), $actual); + + $qB->delete('properties') + ->where($qB->expr()->eq('userid', $qB->expr()->literal('testFirstResult'))) + ->execute(); + + try { + $qB->getLastInsertId(); + $this->fail('getLastInsertId() should throw an exception, when being called after delete()'); + } catch (\BadMethodCallException $e) { + $this->addToAssertionCount(1); + } + } + + public function dataGetTableName(): array { + $config = $this->createMock(SystemConfig::class); + $logger = $this->createMock(LoggerInterface::class); + $qb = new QueryBuilder(Server::get(IDBConnection::class), $config, $logger); + return [ + ['*PREFIX*table', null, '`*PREFIX*table`'], + ['*PREFIX*table', true, '`*PREFIX*table`'], + ['*PREFIX*table', false, '`*PREFIX*table`'], + + ['table', null, '`*PREFIX*table`'], + ['table', true, '`*PREFIX*table`'], + ['table', false, '`table`'], + + [$qb->createFunction('(' . $qb->select('*')->from('table')->getSQL() . ')'), null, '(SELECT * FROM `*PREFIX*table`)'], + [$qb->createFunction('(' . $qb->select('*')->from('table')->getSQL() . ')'), true, '(SELECT * FROM `*PREFIX*table`)'], + [$qb->createFunction('(' . $qb->select('*')->from('table')->getSQL() . ')'), false, '(SELECT * FROM `*PREFIX*table`)'], + ]; + } + + /** + * + * @param string|IQueryFunction $tableName + * @param bool $automatic + * @param string $expected + */ + #[\PHPUnit\Framework\Attributes\DataProvider('dataGetTableName')] + public function testGetTableName($tableName, $automatic, $expected): void { + if ($automatic !== null) { + $this->queryBuilder->automaticTablePrefix($automatic); + } + + $this->assertSame( + $expected, + $this->queryBuilder->getTableName($tableName) + ); + } + + public static function dataGetColumnName(): array { + return [ + ['column', '', '`column`'], + ['column', 'a', '`a`.`column`'], + ]; + } + + /** + * @param string $column + * @param string $prefix + * @param string $expected + */ + #[\PHPUnit\Framework\Attributes\DataProvider('dataGetColumnName')] + public function testGetColumnName($column, $prefix, $expected): void { + $this->assertSame( + $expected, + $this->queryBuilder->getColumnName($column, $prefix) + ); + } + + private function getConnection(): IDBConnection { + $connection = $this->createMock(IDBConnection::class); + $connection->method('executeStatement') + ->willReturn(3); + $connection->method('executeQuery') + ->willReturn($this->createMock(IResult::class)); + return $connection; + } + + public function testExecuteWithoutLogger(): void { + $queryBuilder = $this->createMock(\Doctrine\DBAL\Query\QueryBuilder::class); + $queryBuilder + ->method('getSQL') + ->willReturn(''); + $queryBuilder + ->method('getParameters') + ->willReturn([]); + $queryBuilder + ->method('getParameterTypes') + ->willReturn([]); + $queryBuilder + ->method('getType') + ->willReturn(\Doctrine\DBAL\Query\QueryBuilder::UPDATE); + $this->logger + ->expects($this->never()) + ->method('debug'); + $this->config + ->expects($this->once()) + ->method('getValue') + ->with('log_query', false) + ->willReturn(false); + + $this->invokePrivate($this->queryBuilder, 'queryBuilder', [$queryBuilder]); + $this->invokePrivate($this->queryBuilder, 'connection', [$this->getConnection()]); + $this->assertEquals(3, $this->queryBuilder->execute()); + } + + public function testExecuteWithLoggerAndNamedArray(): void { + $queryBuilder = $this->createMock(\Doctrine\DBAL\Query\QueryBuilder::class); + $queryBuilder + ->expects($this->any()) + ->method('getParameters') + ->willReturn([ + 'foo' => 'bar', + 'key' => 'value', + ]); + $queryBuilder + ->method('getParameterTypes') + ->willReturn([ + 'foo' => IQueryBuilder::PARAM_STR, + 'key' => IQueryBuilder::PARAM_STR, + ]); + $queryBuilder + ->method('getType') + ->willReturn(\Doctrine\DBAL\Query\QueryBuilder::UPDATE); + $queryBuilder + ->expects($this->any()) + ->method('getSQL') + ->willReturn('UPDATE FOO SET bar = 1 WHERE BAR = ?'); + $this->logger + ->expects($this->once()) + ->method('debug') + ->with( + 'DB QueryBuilder: \'{query}\' with parameters: {params}', + [ + 'query' => 'UPDATE FOO SET bar = 1 WHERE BAR = ?', + 'params' => 'foo => \'bar\', key => \'value\'', + 'app' => 'core', + ] + ); + $this->config + ->expects($this->once()) + ->method('getValue') + ->with('log_query', false) + ->willReturn(true); + + $this->invokePrivate($this->queryBuilder, 'queryBuilder', [$queryBuilder]); + $this->invokePrivate($this->queryBuilder, 'connection', [$this->getConnection()]); + $this->assertEquals(3, $this->queryBuilder->execute()); + } + + public function testExecuteWithLoggerAndUnnamedArray(): void { + $queryBuilder = $this->createMock(\Doctrine\DBAL\Query\QueryBuilder::class); + $queryBuilder + ->expects($this->any()) + ->method('getParameters') + ->willReturn(['Bar']); + $queryBuilder + ->method('getParameterTypes') + ->willReturn([IQueryBuilder::PARAM_STR]); + $queryBuilder + ->method('getType') + ->willReturn(\Doctrine\DBAL\Query\QueryBuilder::UPDATE); + $queryBuilder + ->expects($this->any()) + ->method('getSQL') + ->willReturn('UPDATE FOO SET bar = false WHERE BAR = ?'); + $this->logger + ->expects($this->once()) + ->method('debug') + ->with( + 'DB QueryBuilder: \'{query}\' with parameters: {params}', + [ + 'query' => 'UPDATE FOO SET bar = false WHERE BAR = ?', + 'params' => '0 => \'Bar\'', + 'app' => 'core', + ] + ); + $this->config + ->expects($this->once()) + ->method('getValue') + ->with('log_query', false) + ->willReturn(true); + + $this->invokePrivate($this->queryBuilder, 'queryBuilder', [$queryBuilder]); + $this->invokePrivate($this->queryBuilder, 'connection', [$this->getConnection()]); + $this->assertEquals(3, $this->queryBuilder->execute()); + } + + public function testExecuteWithLoggerAndNoParams(): void { + $queryBuilder = $this->createMock(\Doctrine\DBAL\Query\QueryBuilder::class); + $queryBuilder + ->expects($this->any()) + ->method('getParameters') + ->willReturn([]); + $queryBuilder + ->method('getParameterTypes') + ->willReturn([]); + $queryBuilder + ->method('getType') + ->willReturn(\Doctrine\DBAL\Query\QueryBuilder::UPDATE); + $queryBuilder + ->expects($this->any()) + ->method('getSQL') + ->willReturn('UPDATE FOO SET bar = false WHERE BAR = ?'); + $this->logger + ->expects($this->once()) + ->method('debug') + ->with( + 'DB QueryBuilder: \'{query}\'', + [ + 'query' => 'UPDATE FOO SET bar = false WHERE BAR = ?', + 'app' => 'core', + ] + ); + $this->config + ->expects($this->once()) + ->method('getValue') + ->with('log_query', false) + ->willReturn(true); + + $this->invokePrivate($this->queryBuilder, 'queryBuilder', [$queryBuilder]); + $this->invokePrivate($this->queryBuilder, 'connection', [$this->getConnection()]); + $this->assertEquals(3, $this->queryBuilder->execute()); + } + + public function testExecuteWithParameterTooLarge(): void { + $queryBuilder = $this->createMock(\Doctrine\DBAL\Query\QueryBuilder::class); + $p = array_fill(0, 1001, 'foo'); + $queryBuilder + ->expects($this->any()) + ->method('getParameters') + ->willReturn([$p]); + $queryBuilder + ->method('getParameterTypes') + ->willReturn([IQueryBuilder::PARAM_STR_ARRAY]); + $queryBuilder + ->expects($this->any()) + ->method('getSQL') + ->willReturn('SELECT * FROM FOO WHERE BAR IN (?)'); + $this->logger + ->expects($this->once()) + ->method('error') + ->willReturnCallback(function ($message, $parameters): void { + $this->assertInstanceOf(QueryException::class, $parameters['exception']); + $this->assertSame( + 'More than 1000 expressions in a list are not allowed on Oracle.', + $message + ); + }); + $this->config + ->expects($this->once()) + ->method('getValue') + ->with('log_query', false) + ->willReturn(false); + + $this->invokePrivate($this->queryBuilder, 'queryBuilder', [$queryBuilder]); + $this->invokePrivate($this->queryBuilder, 'connection', [$this->getConnection()]); + $this->queryBuilder->execute(); + } + + public function testExecuteWithParametersTooMany(): void { + $queryBuilder = $this->createMock(\Doctrine\DBAL\Query\QueryBuilder::class); + $p = array_fill(0, 999, 'foo'); + $queryBuilder + ->expects($this->any()) + ->method('getParameters') + ->willReturn(array_fill(0, 66, $p)); + $queryBuilder + ->method('getParameterTypes') + ->willReturn([IQueryBuilder::PARAM_STR_ARRAY]); + $queryBuilder + ->expects($this->any()) + ->method('getSQL') + ->willReturn('SELECT * FROM FOO WHERE BAR IN (?) OR BAR IN (?)'); + $this->logger + ->expects($this->once()) + ->method('error') + ->willReturnCallback(function ($message, $parameters): void { + $this->assertInstanceOf(QueryException::class, $parameters['exception']); + $this->assertSame( + 'The number of parameters must not exceed 65535. Restriction by PostgreSQL.', + $message + ); + }); + $this->config + ->expects($this->once()) + ->method('getValue') + ->with('log_query', false) + ->willReturn(false); + + $this->invokePrivate($this->queryBuilder, 'queryBuilder', [$queryBuilder]); + $this->invokePrivate($this->queryBuilder, 'connection', [$this->getConnection()]); + $this->queryBuilder->execute(); + } +} diff --git a/tests/lib/DB/QueryBuilder/QuoteHelperTest.php b/tests/lib/DB/QueryBuilder/QuoteHelperTest.php new file mode 100644 index 00000000000..6efb55708a1 --- /dev/null +++ b/tests/lib/DB/QueryBuilder/QuoteHelperTest.php @@ -0,0 +1,132 @@ +<?php + +/** + * SPDX-FileCopyrightText: 2017-2024 Nextcloud GmbH and Nextcloud contributors + * SPDX-FileCopyrightText: 2016 ownCloud, Inc. + * SPDX-License-Identifier: AGPL-3.0-only + */ + +namespace Test\DB\QueryBuilder; + +use OC\DB\QueryBuilder\Literal; +use OC\DB\QueryBuilder\Parameter; +use OC\DB\QueryBuilder\QuoteHelper; +use OCP\DB\QueryBuilder\ILiteral; +use OCP\DB\QueryBuilder\IParameter; + +class QuoteHelperTest extends \Test\TestCase { + /** @var QuoteHelper */ + protected $helper; + + protected function setUp(): void { + parent::setUp(); + + $this->helper = new QuoteHelper(); + } + + public static function dataQuoteColumnName(): array { + return [ + ['column', '`column`'], + [new Literal('literal'), 'literal'], + [new Literal(1), '1'], + [new Parameter(':param'), ':param'], + + // (string) 'null' is Doctrines way to set columns to null + // See https://github.com/owncloud/core/issues/19314 + ['null', 'null'], + ]; + } + + /** + * @param mixed $input + * @param string $expected + */ + #[\PHPUnit\Framework\Attributes\DataProvider('dataQuoteColumnName')] + public function testQuoteColumnName($input, $expected): void { + $this->assertSame( + $expected, + $this->helper->quoteColumnName($input) + ); + } + + public static function dataQuoteColumnNames(): array { + return [ + // Single case + ['d.column', '`d`.`column`'], + ['column', '`column`'], + [new Literal('literal'), 'literal'], + [new Literal(1), '1'], + [new Parameter(':param'), ':param'], + + // Array case + [['column'], ['`column`']], + [[new Literal('literal')], ['literal']], + [[new Literal(1)], ['1']], + [[new Parameter(':param')], [':param']], + + // Array mixed cases + [['column1', 'column2'], ['`column1`', '`column2`']], + [['column', new Literal('literal')], ['`column`', 'literal']], + [['column', new Literal(1)], ['`column`', '1']], + [['column', new Parameter(':param')], ['`column`', ':param']], + ]; + } + + /** + * @param mixed $input + * @param string $expected + */ + #[\PHPUnit\Framework\Attributes\DataProvider('dataQuoteColumnNames')] + public function testQuoteColumnNames($input, $expected): void { + $this->assertSame( + $expected, + $this->helper->quoteColumnNames($input) + ); + } + + /** + * @param array|string|ILiteral|IParameter $strings string, Literal or Parameter + * @return array|string + */ + public function quoteColumnNames($strings) { + if (!is_array($strings)) { + return $this->quoteColumnName($strings); + } + + $return = []; + foreach ($strings as $string) { + $return[] = $this->quoteColumnName($string); + } + + return $return; + } + + /** + * @param string|ILiteral|IParameter $string string, Literal or Parameter + * @return string + */ + public function quoteColumnName($string) { + if ($string instanceof IParameter) { + return $string->getName(); + } + + if ($string instanceof ILiteral) { + return $string->getLiteral(); + } + + if ($string === null) { + return $string; + } + + if (!is_string($string)) { + throw new \InvalidArgumentException('Only strings, Literals and Parameters are allowed'); + } + + if (substr_count($string, '.')) { + [$alias, $columnName] = explode('.', $string); + return '`' . $alias . '`.`' . $columnName . '`'; + } + + return '`' . $string . '`'; + } +} diff --git a/tests/lib/DB/QueryBuilder/Sharded/SharedQueryBuilderTest.php b/tests/lib/DB/QueryBuilder/Sharded/SharedQueryBuilderTest.php new file mode 100644 index 00000000000..d0f232cb03f --- /dev/null +++ b/tests/lib/DB/QueryBuilder/Sharded/SharedQueryBuilderTest.php @@ -0,0 +1,128 @@ +<?php + +declare(strict_types=1); +/** + * SPDX-FileCopyrightText: 2024 Robin Appelman <robin@icewind.nl> + * SPDX-License-Identifier: AGPL-3.0-or-later + */ + +namespace Test\DB\QueryBuilder\Sharded; + +use OC\DB\QueryBuilder\Sharded\AutoIncrementHandler; +use OC\DB\QueryBuilder\Sharded\InvalidShardedQueryException; +use OC\DB\QueryBuilder\Sharded\RoundRobinShardMapper; +use OC\DB\QueryBuilder\Sharded\ShardConnectionManager; +use OC\DB\QueryBuilder\Sharded\ShardDefinition; +use OC\DB\QueryBuilder\Sharded\ShardedQueryBuilder; +use OCP\DB\QueryBuilder\IQueryBuilder; +use OCP\IDBConnection; +use OCP\Server; +use Test\TestCase; + +/** + * @group DB + */ +class SharedQueryBuilderTest extends TestCase { + private IDBConnection $connection; + private AutoIncrementHandler $autoIncrementHandler; + + protected function setUp(): void { + if (PHP_INT_SIZE < 8) { + $this->markTestSkipped('Test requires 64bit'); + } + $this->connection = Server::get(IDBConnection::class); + $this->autoIncrementHandler = Server::get(AutoIncrementHandler::class); + } + + + private function getQueryBuilder(string $table, string $shardColumn, string $primaryColumn, array $companionTables = []): ShardedQueryBuilder { + return new ShardedQueryBuilder( + $this->connection->getQueryBuilder(), + [ + new ShardDefinition($table, $primaryColumn, [], $shardColumn, new RoundRobinShardMapper(), $companionTables, [], 0, 0), + ], + $this->createMock(ShardConnectionManager::class), + $this->autoIncrementHandler, + ); + } + + public function testGetShardKeySingleParam(): void { + $query = $this->getQueryBuilder('filecache', 'storage', 'fileid'); + $query->select('fileid', 'path') + ->from('filecache') + ->where($query->expr()->eq('storage', $query->createNamedParameter(10, IQueryBuilder::PARAM_INT))); + + $this->assertEquals([], $query->getPrimaryKeys()); + $this->assertEquals([10], $query->getShardKeys()); + } + + public function testGetPrimaryKeyParam(): void { + $query = $this->getQueryBuilder('filecache', 'storage', 'fileid'); + $query->select('fileid', 'path') + ->from('filecache') + ->where($query->expr()->in('fileid', $query->createNamedParameter([10, 11], IQueryBuilder::PARAM_INT))); + + $this->assertEquals([10, 11], $query->getPrimaryKeys()); + $this->assertEquals([], $query->getShardKeys()); + } + + public function testValidateWithShardKey(): void { + $query = $this->getQueryBuilder('filecache', 'storage', 'fileid'); + $query->select('fileid', 'path') + ->from('filecache') + ->where($query->expr()->eq('storage', $query->createNamedParameter(10))); + + $query->validate(); + $this->assertTrue(true); + } + + public function testValidateWithPrimaryKey(): void { + $query = $this->getQueryBuilder('filecache', 'storage', 'fileid'); + $query->select('fileid', 'path') + ->from('filecache') + ->where($query->expr()->in('fileid', $query->createNamedParameter([10, 11], IQueryBuilder::PARAM_INT))); + + $query->validate(); + $this->assertTrue(true); + } + + public function testValidateWithNoKey(): void { + $query = $this->getQueryBuilder('filecache', 'storage', 'fileid'); + $query->select('fileid', 'path') + ->from('filecache') + ->where($query->expr()->lt('size', $query->createNamedParameter(0))); + + $this->expectException(InvalidShardedQueryException::class); + $query->validate(); + $this->fail('exception expected'); + } + + public function testValidateNonSharedTable(): void { + $query = $this->getQueryBuilder('filecache', 'storage', 'fileid'); + $query->select('configvalue') + ->from('appconfig') + ->where($query->expr()->eq('configkey', $query->createNamedParameter('test'))); + + $query->validate(); + $this->assertTrue(true); + } + + public function testGetShardKeyMultipleSingleParam(): void { + $query = $this->getQueryBuilder('filecache', 'storage', 'fileid'); + $query->select('fileid', 'path') + ->from('filecache') + ->where($query->expr()->andX( + $query->expr()->gt('mtime', $query->createNamedParameter(0), IQueryBuilder::PARAM_INT), + $query->expr()->orX( + $query->expr()->eq('storage', $query->createNamedParameter(10, IQueryBuilder::PARAM_INT)), + $query->expr()->andX( + $query->expr()->eq('storage', $query->createNamedParameter(11, IQueryBuilder::PARAM_INT)), + $query->expr()->like('path', $query->createNamedParameter('foo/%')) + ) + ) + )); + + $this->assertEquals([], $query->getPrimaryKeys()); + $this->assertEquals([10, 11], $query->getShardKeys()); + } +} |