123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428 |
- <?php
- namespace Test\DB\QueryBuilder;
- use Doctrine\DBAL\Query\Expression\ExpressionBuilder as DoctrineExpressionBuilder;
- use OC\DB\QueryBuilder\ExpressionBuilder\ExpressionBuilder;
- use OCP\DB\QueryBuilder\IQueryBuilder;
- use Psr\Log\LoggerInterface;
- use Test\TestCase;
- class ExpressionBuilderTest extends TestCase {
-
- protected $expressionBuilder;
-
- protected $doctrineExpressionBuilder;
-
- protected $connection;
-
- protected $internalConnection;
-
- protected $logger;
- protected function setUp(): void {
- parent::setUp();
- $this->connection = \OC::$server->getDatabaseConnection();
- $this->internalConnection = \OC::$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 function dataComparison() {
- $valueSets = $this->dataComparisons();
- $comparisonOperators = ['=', '<>', '<', '>', '<=', '>='];
- $testSets = [];
- foreach ($comparisonOperators as $operator) {
- foreach ($valueSets as $values) {
- $testSets[] = array_merge([$operator], $values);
- }
- }
- return $testSets;
- }
-
- 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 function dataComparisons() {
- return [
- ['value', false, 'value', false],
- ['value', false, 'value', true],
- ['value', true, 'value', false],
- ['value', true, 'value', true],
- ];
- }
-
- 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)
- );
- }
-
- 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)
- );
- }
-
- 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)
- );
- }
-
- 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)
- );
- }
-
- 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)
- );
- }
-
- 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 function dataLike() {
- return [
- ['value', false],
- ['value', true],
- ];
- }
-
- public function testLike($input, $isLiteral): void {
- [$doctrineInput, $ocInput] = $this->helpWithLiteral($input, $isLiteral);
- $this->assertEquals(
- $this->doctrineExpressionBuilder->like('`test`', $doctrineInput),
- $this->expressionBuilder->like('test', $ocInput)
- );
- }
-
- 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 function dataIn() {
- return [
- ['value', false],
- ['value', true],
- [['value'], false],
- [['value'], true],
- ];
- }
-
- public function testIn($input, $isLiteral): void {
- [$doctrineInput, $ocInput] = $this->helpWithLiteral($input, $isLiteral);
- $this->assertEquals(
- $this->doctrineExpressionBuilder->in('`test`', $doctrineInput),
- $this->expressionBuilder->in('test', $ocInput)
- );
- }
-
- 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 function dataLiteral() {
- return [
- ['value', null],
- ['1', null],
- [1, null],
- [1, 'string'],
- [1, 'integer'],
- [1, IQueryBuilder::PARAM_INT],
- ];
- }
-
- public function testLiteral($input, $type): void {
-
- $actual = $this->expressionBuilder->literal($input, $type);
- $this->assertInstanceOf('\OC\DB\QueryBuilder\Literal', $actual);
- $this->assertEquals(
- $this->doctrineExpressionBuilder->literal($input, $type),
- $actual->__toString()
- );
- }
- public function dataClobComparisons() {
- 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],
- ];
- }
-
- 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();
- }
- }
|