diff options
Diffstat (limited to 'tests/lib/db')
| -rw-r--r-- | tests/lib/db/querybuilder/expressionbuildertest.php | 338 | ||||
| -rw-r--r-- | tests/lib/db/querybuilder/querybuildertest.php | 999 | ||||
| -rw-r--r-- | tests/lib/db/querybuilder/quotehelpertest.php | 141 |
3 files changed, 1478 insertions, 0 deletions
diff --git a/tests/lib/db/querybuilder/expressionbuildertest.php b/tests/lib/db/querybuilder/expressionbuildertest.php new file mode 100644 index 00000000000..f041df01019 --- /dev/null +++ b/tests/lib/db/querybuilder/expressionbuildertest.php @@ -0,0 +1,338 @@ +<?php +/** + * @author Joas Schilling <nickvergessen@owncloud.com> + * + * @copyright Copyright (c) 2015, ownCloud, Inc. + * @license AGPL-3.0 + * + * This code is free software: you can redistribute it and/or modify + * it under the terms of the GNU Affero General Public License, version 3, + * as published by the Free Software Foundation. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + * GNU Affero General Public License for more details. + * + * You should have received a copy of the GNU Affero General Public License, version 3, + * along with this program. If not, see <http://www.gnu.org/licenses/> + * + */ + +namespace Test\DB\QueryBuilder; + +use Doctrine\DBAL\Query\Expression\ExpressionBuilder as DoctrineExpressionBuilder; +use OC\DB\QueryBuilder\ExpressionBuilder; + +class ExpressionBuilderTest extends \Test\TestCase { + /** @var ExpressionBuilder */ + protected $expressionBuilder; + + /** @var DoctrineExpressionBuilder */ + protected $doctrineExpressionBuilder; + + protected function setUp() { + parent::setUp(); + + $connection = \OC::$server->getDatabaseConnection(); + + $this->expressionBuilder = new ExpressionBuilder($connection); + + $this->doctrineExpressionBuilder = new DoctrineExpressionBuilder($connection); + } + + public function dataComparison() { + $valueSets = $this->dataComparisons(); + $comparisonOperators = ['=', '<>', '<', '>', '<=', '>=']; + + $testSets = []; + foreach ($comparisonOperators as $operator) { + foreach ($valueSets as $values) { + $testSets[] = array_merge([$operator], $values); + } + } + return $testSets; + } + + /** + * @dataProvider dataComparison + * + * @param string $comparison + * @param mixed $input1 + * @param bool $isInput1Literal + * @param mixed $input2 + * @param bool $isInput2Literal + */ + public function testComparison($comparison, $input1, $isInput1Literal, $input2, $isInput2Literal) { + list($doctrineInput1, $ocInput1) = $this->helpWithLiteral($input1, $isInput1Literal); + list($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], + ]; + } + + /** + * @dataProvider dataComparisons + * + * @param mixed $input1 + * @param bool $isInput1Literal + * @param mixed $input2 + * @param bool $isInput2Literal + */ + public function testEquals($input1, $isInput1Literal, $input2, $isInput2Literal) { + list($doctrineInput1, $ocInput1) = $this->helpWithLiteral($input1, $isInput1Literal); + list($doctrineInput2, $ocInput2) = $this->helpWithLiteral($input2, $isInput2Literal); + + $this->assertEquals( + $this->doctrineExpressionBuilder->eq($doctrineInput1, $doctrineInput2), + $this->expressionBuilder->eq($ocInput1, $ocInput2) + ); + } + + /** + * @dataProvider dataComparisons + * + * @param mixed $input1 + * @param bool $isInput1Literal + * @param mixed $input2 + * @param bool $isInput2Literal + */ + public function testNotEquals($input1, $isInput1Literal, $input2, $isInput2Literal) { + list($doctrineInput1, $ocInput1) = $this->helpWithLiteral($input1, $isInput1Literal); + list($doctrineInput2, $ocInput2) = $this->helpWithLiteral($input2, $isInput2Literal); + + $this->assertEquals( + $this->doctrineExpressionBuilder->neq($doctrineInput1, $doctrineInput2), + $this->expressionBuilder->neq($ocInput1, $ocInput2) + ); + } + + /** + * @dataProvider dataComparisons + * + * @param mixed $input1 + * @param bool $isInput1Literal + * @param mixed $input2 + * @param bool $isInput2Literal + */ + public function testLowerThan($input1, $isInput1Literal, $input2, $isInput2Literal) { + list($doctrineInput1, $ocInput1) = $this->helpWithLiteral($input1, $isInput1Literal); + list($doctrineInput2, $ocInput2) = $this->helpWithLiteral($input2, $isInput2Literal); + + $this->assertEquals( + $this->doctrineExpressionBuilder->lt($doctrineInput1, $doctrineInput2), + $this->expressionBuilder->lt($ocInput1, $ocInput2) + ); + } + + /** + * @dataProvider dataComparisons + * + * @param mixed $input1 + * @param bool $isInput1Literal + * @param mixed $input2 + * @param bool $isInput2Literal + */ + public function testLowerThanEquals($input1, $isInput1Literal, $input2, $isInput2Literal) { + list($doctrineInput1, $ocInput1) = $this->helpWithLiteral($input1, $isInput1Literal); + list($doctrineInput2, $ocInput2) = $this->helpWithLiteral($input2, $isInput2Literal); + + $this->assertEquals( + $this->doctrineExpressionBuilder->lte($doctrineInput1, $doctrineInput2), + $this->expressionBuilder->lte($ocInput1, $ocInput2) + ); + } + + /** + * @dataProvider dataComparisons + * + * @param mixed $input1 + * @param bool $isInput1Literal + * @param mixed $input2 + * @param bool $isInput2Literal + */ + public function testGreaterThan($input1, $isInput1Literal, $input2, $isInput2Literal) { + list($doctrineInput1, $ocInput1) = $this->helpWithLiteral($input1, $isInput1Literal); + list($doctrineInput2, $ocInput2) = $this->helpWithLiteral($input2, $isInput2Literal); + + $this->assertEquals( + $this->doctrineExpressionBuilder->gt($doctrineInput1, $doctrineInput2), + $this->expressionBuilder->gt($ocInput1, $ocInput2) + ); + } + + /** + * @dataProvider dataComparisons + * + * @param mixed $input1 + * @param bool $isInput1Literal + * @param mixed $input2 + * @param bool $isInput2Literal + */ + public function testGreaterThanEquals($input1, $isInput1Literal, $input2, $isInput2Literal) { + list($doctrineInput1, $ocInput1) = $this->helpWithLiteral($input1, $isInput1Literal); + list($doctrineInput2, $ocInput2) = $this->helpWithLiteral($input2, $isInput2Literal); + + $this->assertEquals( + $this->doctrineExpressionBuilder->gte($doctrineInput1, $doctrineInput2), + $this->expressionBuilder->gte($ocInput1, $ocInput2) + ); + } + + public function testIsNull() { + $this->assertEquals( + $this->doctrineExpressionBuilder->isNull('`test`'), + $this->expressionBuilder->isNull('test') + ); + } + + public function testIsNotNull() { + $this->assertEquals( + $this->doctrineExpressionBuilder->isNotNull('`test`'), + $this->expressionBuilder->isNotNull('test') + ); + } + + public function dataLike() { + return [ + ['value', false], + ['value', true], + ]; + } + + /** + * @dataProvider dataLike + * + * @param mixed $input + * @param bool $isLiteral + */ + public function testLike($input, $isLiteral) { + list($doctrineInput, $ocInput) = $this->helpWithLiteral($input, $isLiteral); + + $this->assertEquals( + $this->doctrineExpressionBuilder->like('`test`', $doctrineInput), + $this->expressionBuilder->like('test', $ocInput) + ); + } + + /** + * @dataProvider dataLike + * + * @param mixed $input + * @param bool $isLiteral + */ + public function testNotLike($input, $isLiteral) { + list($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], + ]; + } + + /** + * @dataProvider dataIn + * + * @param mixed $input + * @param bool $isLiteral + */ + public function testIn($input, $isLiteral) { + list($doctrineInput, $ocInput) = $this->helpWithLiteral($input, $isLiteral); + + $this->assertEquals( + $this->doctrineExpressionBuilder->in('`test`', $doctrineInput), + $this->expressionBuilder->in('test', $ocInput) + ); + } + + /** + * @dataProvider dataIn + * + * @param mixed $input + * @param bool $isLiteral + */ + public function testNotIn($input, $isLiteral) { + list($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, \PDO::PARAM_INT], + ]; + } + + /** + * @dataProvider dataLiteral + * + * @param mixed $input + * @param string|null $type + */ + public function testLiteral($input, $type) { + /** @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() + ); + } +} diff --git a/tests/lib/db/querybuilder/querybuildertest.php b/tests/lib/db/querybuilder/querybuildertest.php new file mode 100644 index 00000000000..02e516b7386 --- /dev/null +++ b/tests/lib/db/querybuilder/querybuildertest.php @@ -0,0 +1,999 @@ +<?php +/** + * @author Joas Schilling <nickvergessen@owncloud.com> + * + * @copyright Copyright (c) 2015, ownCloud, Inc. + * @license AGPL-3.0 + * + * This code is free software: you can redistribute it and/or modify + * it under the terms of the GNU Affero General Public License, version 3, + * as published by the Free Software Foundation. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + * GNU Affero General Public License for more details. + * + * You should have received a copy of the GNU Affero General Public License, version 3, + * along with this program. If not, see <http://www.gnu.org/licenses/> + * + */ + +namespace Test\DB\QueryBuilder; + +use Doctrine\DBAL\Query\Expression\CompositeExpression; +use OC\DB\QueryBuilder\Literal; +use OC\DB\QueryBuilder\Parameter; +use OC\DB\QueryBuilder\QueryBuilder; +use OCP\IDBConnection; + +class QueryBuilderTest extends \Test\TestCase { + /** @var QueryBuilder */ + protected $queryBuilder; + + /** @var IDBConnection */ + protected $connection; + + protected function setUp() { + parent::setUp(); + + $this->connection = \OC::$server->getDatabaseConnection(); + $this->queryBuilder = new QueryBuilder($this->connection); + } + + protected function createTestingRows() { + $qB = $this->connection->getQueryBuilder(); + for ($i = 1; $i < 10; $i++) { + $qB->insert('*PREFIX*appconfig') + ->values([ + 'appid' => $qB->expr()->literal('testFirstResult'), + '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() { + $qB = $this->connection->getQueryBuilder(); + + $qB->delete('*PREFIX*appconfig') + ->where($qB->expr()->eq('appid', $qB->expr()->literal('testFirstResult'))) + ->execute(); + } + + public function dataFirstResult() { + return [ + [null, [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]], + ]; + } + + /** + * @dataProvider dataFirstResult + * + * @param int $firstResult + * @param array $expectedSet + */ + public function testFirstResult($firstResult, $expectedSet) { + $this->deleteTestingRows(); + $this->createTestingRows(); + + if ($firstResult !== null) { + $this->queryBuilder->setFirstResult($firstResult); + + // FIXME Remove this once Doctrine/DBAL is >2.5.1: + // FIXME See https://github.com/doctrine/dbal/pull/782 + $this->queryBuilder->setMaxResults(100); + } + + $this->assertSame( + $firstResult, + $this->queryBuilder->getFirstResult() + ); + + $rows = $this->getTestingRows($this->queryBuilder); + + $this->assertCount(sizeof($expectedSet), $rows); + $this->assertEquals($expectedSet, $rows); + + $this->deleteTestingRows(); + } + + public function dataMaxResults() { + 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]], + ]; + } + + /** + * @dataProvider dataMaxResults + * + * @param int $maxResult + * @param array $expectedSet + */ + public function testMaxResults($maxResult, $expectedSet) { + $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() { + return [ + // select('column1') + [['column1'], ['`column1`'], '`column1`'], + + // select('column1', 'column2') + [['column1', 'column2'], ['`column1`', '`column2`'], '`column1`, `column2`'], + + // select(['column1', 'column2']) + [[['column1', 'column2']], ['`column1`', '`column2`'], '`column1`, `column2`'], + + // select(new Literal('column1')) + [[new Literal('column1')], ['column1'], 'column1'], + + // select('column1', 'column2') + [[new Literal('column1'), 'column2'], ['column1', '`column2`'], 'column1, `column2`'], + + // select(['column1', 'column2']) + [[[new Literal('column1'), 'column2']], ['column1', '`column2`'], 'column1, `column2`'], + ]; + } + + /** + * @dataProvider dataSelect + * + * @param array $selectArguments + * @param array $expectedQueryPart + * @param string $expectedSelect + */ + public function testSelect($selectArguments, $expectedQueryPart, $expectedSelect) { + call_user_func_array( + [$this->queryBuilder, 'select'], + $selectArguments + ); + + $this->assertSame( + $expectedQueryPart, + $this->queryBuilder->getQueryPart('select') + ); + + $this->assertSame( + 'SELECT ' . $expectedSelect . ' FROM ', + $this->queryBuilder->getSQL() + ); + } + + public function dataAddSelect() { + return [ + // addSelect('column1') + [['column1'], ['`column`', '`column1`'], '`column`, `column1`'], + + // addSelect('column1', 'column2') + [['column1', 'column2'], ['`column`', '`column1`', '`column2`'], '`column`, `column1`, `column2`'], + + // addSelect(['column1', 'column2']) + [[['column1', 'column2']], ['`column`', '`column1`', '`column2`'], '`column`, `column1`, `column2`'], + + // select(new Literal('column1')) + [[new Literal('column1')], ['`column`', 'column1'], '`column`, column1'], + + // select('column1', 'column2') + [[new Literal('column1'), 'column2'], ['`column`', 'column1', '`column2`'], '`column`, column1, `column2`'], + + // select(['column1', 'column2']) + [[[new Literal('column1'), 'column2']], ['`column`', 'column1', '`column2`'], '`column`, column1, `column2`'], + ]; + } + + /** + * @dataProvider dataAddSelect + * + * @param array $selectArguments + * @param array $expectedQueryPart + * @param string $expectedSelect + */ + public function testAddSelect($selectArguments, $expectedQueryPart, $expectedSelect) { + $this->queryBuilder->select('column'); + call_user_func_array( + [$this->queryBuilder, 'addSelect'], + $selectArguments + ); + + $this->assertSame( + $expectedQueryPart, + $this->queryBuilder->getQueryPart('select') + ); + + $this->assertSame( + 'SELECT ' . $expectedSelect . ' FROM ', + $this->queryBuilder->getSQL() + ); + } + + public function dataDelete() { + return [ + ['data', null, ['table' => '`data`', 'alias' => null], '`data`'], + ['data', 't', ['table' => '`data`', 'alias' => 't'], '`data` t'], + ]; + } + + /** + * @dataProvider dataDelete + * + * @param string $tableName + * @param string $tableAlias + * @param array $expectedQueryPart + * @param string $expectedQuery + */ + public function testDelete($tableName, $tableAlias, $expectedQueryPart, $expectedQuery) { + $this->queryBuilder->delete($tableName, $tableAlias); + + $this->assertSame( + $expectedQueryPart, + $this->queryBuilder->getQueryPart('from') + ); + + $this->assertSame( + 'DELETE FROM ' . $expectedQuery, + $this->queryBuilder->getSQL() + ); + } + + public function dataUpdate() { + return [ + ['data', null, ['table' => '`data`', 'alias' => null], '`data`'], + ['data', 't', ['table' => '`data`', 'alias' => 't'], '`data` t'], + ]; + } + + /** + * @dataProvider dataUpdate + * + * @param string $tableName + * @param string $tableAlias + * @param array $expectedQueryPart + * @param string $expectedQuery + */ + public function testUpdate($tableName, $tableAlias, $expectedQueryPart, $expectedQuery) { + $this->queryBuilder->update($tableName, $tableAlias); + + $this->assertSame( + $expectedQueryPart, + $this->queryBuilder->getQueryPart('from') + ); + + $this->assertSame( + 'UPDATE ' . $expectedQuery . ' SET ', + $this->queryBuilder->getSQL() + ); + } + + public function dataInsert() { + return [ + ['data', ['table' => '`data`'], '`data`'], + ]; + } + + /** + * @dataProvider dataInsert + * + * @param string $tableName + * @param array $expectedQueryPart + * @param string $expectedQuery + */ + public function testInsert($tableName, $expectedQueryPart, $expectedQuery) { + $this->queryBuilder->insert($tableName); + + $this->assertSame( + $expectedQueryPart, + $this->queryBuilder->getQueryPart('from') + ); + + $this->assertSame( + 'INSERT INTO ' . $expectedQuery . ' () VALUES()', + $this->queryBuilder->getSQL() + ); + } + + public function dataFrom() { + return [ + ['data', null, null, null, [['table' => '`data`', 'alias' => null]], '`data`'], + ['data', 't', null, null, [['table' => '`data`', 'alias' => 't']], '`data` t'], + ['data1', null, 'data2', null, [ + ['table' => '`data1`', 'alias' => null], + ['table' => '`data2`', 'alias' => null] + ], '`data1`, `data2`'], + ['data', 't1', 'data', 't2', [ + ['table' => '`data`', 'alias' => 't1'], + ['table' => '`data`', 'alias' => 't2'] + ], '`data` t1, `data` t2'], + ]; + } + + /** + * @dataProvider dataFrom + * + * @param string $table1Name + * @param string $table1Alias + * @param string $table2Name + * @param string $table2Alias + * @param array $expectedQueryPart + * @param string $expectedQuery + */ + public function testFrom($table1Name, $table1Alias, $table2Name, $table2Alias, $expectedQueryPart, $expectedQuery) { + $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 function dataJoin() { + return [ + [ + 'd1', 'data2', null, null, + ['d1' => [['joinType' => 'inner', 'joinTable' => '`data2`', 'joinAlias' => null, 'joinCondition' => null]]], + '`data1` d1 INNER JOIN `data2` ON ' + ], + [ + 'd1', 'data2', 'd2', null, + ['d1' => [['joinType' => 'inner', 'joinTable' => '`data2`', 'joinAlias' => 'd2', 'joinCondition' => null]]], + '`data1` d1 INNER JOIN `data2` d2 ON ' + ], + [ + 'd1', 'data2', 'd2', 'd1.`field1` = d2.`field2`', + ['d1' => [['joinType' => 'inner', 'joinTable' => '`data2`', 'joinAlias' => 'd2', 'joinCondition' => 'd1.`field1` = d2.`field2`']]], + '`data1` d1 INNER JOIN `data2` d2 ON d1.`field1` = d2.`field2`' + ], + + ]; + } + + /** + * @dataProvider dataJoin + * + * @param string $fromAlias + * @param string $tableName + * @param string $tableAlias + * @param string $condition + * @param array $expectedQueryPart + * @param string $expectedQuery + */ + public function testJoin($fromAlias, $tableName, $tableAlias, $condition, $expectedQueryPart, $expectedQuery) { + $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() + ); + } + + /** + * @dataProvider dataJoin + * + * @param string $fromAlias + * @param string $tableName + * @param string $tableAlias + * @param string $condition + * @param array $expectedQueryPart + * @param string $expectedQuery + */ + public function testInnerJoin($fromAlias, $tableName, $tableAlias, $condition, $expectedQueryPart, $expectedQuery) { + $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 function dataLeftJoin() { + return [ + [ + 'd1', 'data2', null, null, + ['d1' => [['joinType' => 'left', 'joinTable' => '`data2`', 'joinAlias' => null, 'joinCondition' => null]]], + '`data1` d1 LEFT JOIN `data2` ON ' + ], + [ + 'd1', 'data2', 'd2', null, + ['d1' => [['joinType' => 'left', 'joinTable' => '`data2`', 'joinAlias' => 'd2', 'joinCondition' => null]]], + '`data1` d1 LEFT JOIN `data2` d2 ON ' + ], + [ + 'd1', 'data2', 'd2', 'd1.`field1` = d2.`field2`', + ['d1' => [['joinType' => 'left', 'joinTable' => '`data2`', 'joinAlias' => 'd2', 'joinCondition' => 'd1.`field1` = d2.`field2`']]], + '`data1` d1 LEFT JOIN `data2` d2 ON d1.`field1` = d2.`field2`' + ], + ]; + } + + /** + * @dataProvider dataLeftJoin + * + * @param string $fromAlias + * @param string $tableName + * @param string $tableAlias + * @param string $condition + * @param array $expectedQueryPart + * @param string $expectedQuery + */ + public function testLeftJoin($fromAlias, $tableName, $tableAlias, $condition, $expectedQueryPart, $expectedQuery) { + $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 function dataRightJoin() { + return [ + [ + 'd1', 'data2', null, null, + ['d1' => [['joinType' => 'right', 'joinTable' => '`data2`', 'joinAlias' => null, 'joinCondition' => null]]], + '`data1` d1 RIGHT JOIN `data2` ON ' + ], + [ + 'd1', 'data2', 'd2', null, + ['d1' => [['joinType' => 'right', 'joinTable' => '`data2`', 'joinAlias' => 'd2', 'joinCondition' => null]]], + '`data1` d1 RIGHT JOIN `data2` d2 ON ' + ], + [ + 'd1', 'data2', 'd2', 'd1.`field1` = d2.`field2`', + ['d1' => [['joinType' => 'right', 'joinTable' => '`data2`', 'joinAlias' => 'd2', 'joinCondition' => 'd1.`field1` = d2.`field2`']]], + '`data1` d1 RIGHT JOIN `data2` d2 ON d1.`field1` = d2.`field2`' + ], + ]; + } + + /** + * @dataProvider dataRightJoin + * + * @param string $fromAlias + * @param string $tableName + * @param string $tableAlias + * @param string $condition + * @param array $expectedQueryPart + * @param string $expectedQuery + */ + public function testRightJoin($fromAlias, $tableName, $tableAlias, $condition, $expectedQueryPart, $expectedQuery) { + $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 function dataSet() { + 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'], + ]; + } + + /** + * @dataProvider dataSet + * + * @param string $partOne1 + * @param string $partOne2 + * @param string $partTwo1 + * @param string $partTwo2 + * @param array $expectedQueryPart + * @param string $expectedQuery + */ + public function testSet($partOne1, $partOne2, $partTwo1, $partTwo2, $expectedQueryPart, $expectedQuery) { + $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 `data` SET ' . $expectedQuery, + $this->queryBuilder->getSQL() + ); + } + + public function dataWhere() { + return [ + [['where1'], new CompositeExpression('AND', ['where1']), 'where1'], + [['where1', 'where2'], new CompositeExpression('AND', ['where1', 'where2']), '(where1) AND (where2)'], + ]; + } + + /** + * @dataProvider dataWhere + * + * @param array $whereArguments + * @param array $expectedQueryPart + * @param string $expectedQuery + */ + public function testWhere($whereArguments, $expectedQueryPart, $expectedQuery) { + $this->queryBuilder->select('column'); + call_user_func_array( + [$this->queryBuilder, 'where'], + $whereArguments + ); + + $this->assertEquals( + $expectedQueryPart, + $this->queryBuilder->getQueryPart('where') + ); + + $this->assertSame( + 'SELECT `column` FROM WHERE ' . $expectedQuery, + $this->queryBuilder->getSQL() + ); + } + + /** + * @dataProvider dataWhere + * + * @param array $whereArguments + * @param array $expectedQueryPart + * @param string $expectedQuery + */ + public function testAndWhere($whereArguments, $expectedQueryPart, $expectedQuery) { + $this->queryBuilder->select('column'); + call_user_func_array( + [$this->queryBuilder, 'andWhere'], + $whereArguments + ); + + $this->assertEquals( + $expectedQueryPart, + $this->queryBuilder->getQueryPart('where') + ); + + $this->assertSame( + 'SELECT `column` FROM WHERE ' . $expectedQuery, + $this->queryBuilder->getSQL() + ); + } + + public function dataOrWhere() { + return [ + [['where1'], new CompositeExpression('OR', ['where1']), 'where1'], + [['where1', 'where2'], new CompositeExpression('OR', ['where1', 'where2']), '(where1) OR (where2)'], + ]; + } + + /** + * @dataProvider dataOrWhere + * + * @param array $whereArguments + * @param array $expectedQueryPart + * @param string $expectedQuery + */ + public function testOrWhere($whereArguments, $expectedQueryPart, $expectedQuery) { + $this->queryBuilder->select('column'); + call_user_func_array( + [$this->queryBuilder, 'orWhere'], + $whereArguments + ); + + $this->assertEquals( + $expectedQueryPart, + $this->queryBuilder->getQueryPart('where') + ); + + $this->assertSame( + 'SELECT `column` FROM WHERE ' . $expectedQuery, + $this->queryBuilder->getSQL() + ); + } + + public function dataGroupBy() { + return [ + [['column1'], ['`column1`'], '`column1`'], + [['column1', 'column2'], ['`column1`', '`column2`'], '`column1`, `column2`'], + ]; + } + + /** + * @dataProvider dataGroupBy + * + * @param array $groupByArguments + * @param array $expectedQueryPart + * @param string $expectedQuery + */ + public function testGroupBy($groupByArguments, $expectedQueryPart, $expectedQuery) { + $this->queryBuilder->select('column'); + call_user_func_array( + [$this->queryBuilder, 'groupBy'], + $groupByArguments + ); + + $this->assertEquals( + $expectedQueryPart, + $this->queryBuilder->getQueryPart('groupBy') + ); + + $this->assertSame( + 'SELECT `column` FROM GROUP BY ' . $expectedQuery, + $this->queryBuilder->getSQL() + ); + } + + public function dataAddGroupBy() { + return [ + [['column2'], ['`column1`', '`column2`'], '`column1`, `column2`'], + [['column2', 'column3'], ['`column1`', '`column2`', '`column3`'], '`column1`, `column2`, `column3`'], + ]; + } + + /** + * @dataProvider dataAddGroupBy + * + * @param array $groupByArguments + * @param array $expectedQueryPart + * @param string $expectedQuery + */ + public function testAddGroupBy($groupByArguments, $expectedQueryPart, $expectedQuery) { + $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` FROM GROUP BY ' . $expectedQuery, + $this->queryBuilder->getSQL() + ); + } + + public function dataSetValue() { + return [ + ['column', 'value', ['`column`' => 'value'], '(`column`) VALUES(value)'], + ]; + } + + /** + * @dataProvider dataSetValue + * + * @param string $column + * @param string $value + * @param array $expectedQueryPart + * @param string $expectedQuery + */ + public function testSetValue($column, $value, $expectedQueryPart, $expectedQuery) { + $this->queryBuilder->insert('data'); + $this->queryBuilder->setValue($column, $value); + + $this->assertEquals( + $expectedQueryPart, + $this->queryBuilder->getQueryPart('values') + ); + + $this->assertSame( + 'INSERT INTO `data` ' . $expectedQuery, + $this->queryBuilder->getSQL() + ); + } + + /** + * @dataProvider dataSetValue + * + * @param string $column + * @param string $value + * @param array $expectedQueryPart + * @param string $expectedQuery + */ + public function testValues($column, $value, $expectedQueryPart, $expectedQuery) { + $this->queryBuilder->insert('data'); + $this->queryBuilder->values([ + $column => $value, + ]); + + $this->assertEquals( + $expectedQueryPart, + $this->queryBuilder->getQueryPart('values') + ); + + $this->assertSame( + 'INSERT INTO `data` ' . $expectedQuery, + $this->queryBuilder->getSQL() + ); + } + + public function dataHaving() { + 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)' + ], + ]; + } + + /** + * @dataProvider dataHaving + * + * @param array $havingArguments + * @param array $expectedQueryPart + * @param string $expectedQuery + */ + public function testHaving($havingArguments, $expectedQueryPart, $expectedQuery) { + call_user_func_array( + [$this->queryBuilder, 'having'], + $havingArguments + ); + + $this->assertEquals( + $expectedQueryPart, + $this->queryBuilder->getQueryPart('having') + ); + + $this->assertSame( + 'SELECT FROM ' . $expectedQuery, + $this->queryBuilder->getSQL() + ); + } + + public function dataAndHaving() { + 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))' + ], + ]; + } + + /** + * @dataProvider dataAndHaving + * + * @param array $havingArguments + * @param array $expectedQueryPart + * @param string $expectedQuery + */ + public function testAndHaving($havingArguments, $expectedQueryPart, $expectedQuery) { + $this->queryBuilder->having('condition1'); + call_user_func_array( + [$this->queryBuilder, 'andHaving'], + $havingArguments + ); + + $this->assertEquals( + $expectedQueryPart, + $this->queryBuilder->getQueryPart('having') + ); + + $this->assertSame( + 'SELECT FROM ' . $expectedQuery, + $this->queryBuilder->getSQL() + ); + } + + public function dataOrHaving() { + 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))' + ], + ]; + } + + /** + * @dataProvider dataOrHaving + * + * @param array $havingArguments + * @param array $expectedQueryPart + * @param string $expectedQuery + */ + public function testOrHaving($havingArguments, $expectedQueryPart, $expectedQuery) { + $this->queryBuilder->having('condition1'); + call_user_func_array( + [$this->queryBuilder, 'orHaving'], + $havingArguments + ); + + $this->assertEquals( + $expectedQueryPart, + $this->queryBuilder->getQueryPart('having') + ); + + $this->assertSame( + 'SELECT FROM ' . $expectedQuery, + $this->queryBuilder->getSQL() + ); + } + + public function dataOrderBy() { + 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'], + ]; + } + + /** + * @dataProvider dataOrderBy + * + * @param string $sort + * @param string $order + * @param array $expectedQueryPart + * @param string $expectedQuery + */ + public function testOrderBy($sort, $order, $expectedQueryPart, $expectedQuery) { + $this->queryBuilder->orderBy($sort, $order); + + $this->assertEquals( + $expectedQueryPart, + $this->queryBuilder->getQueryPart('orderBy') + ); + + $this->assertSame( + 'SELECT FROM ' . $expectedQuery, + $this->queryBuilder->getSQL() + ); + } + + public function dataAddOrderBy() { + 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'], + ]; + } + + /** + * @dataProvider dataAddOrderBy + * + * @param string $sort2 + * @param string $order2 + * @param string $order1 + * @param array $expectedQueryPart + * @param string $expectedQuery + */ + public function testAddOrderBy($sort2, $order2, $order1, $expectedQueryPart, $expectedQuery) { + $this->queryBuilder->orderBy('column1', $order1); + $this->queryBuilder->addOrderBy($sort2, $order2); + + $this->assertEquals( + $expectedQueryPart, + $this->queryBuilder->getQueryPart('orderBy') + ); + + $this->assertSame( + 'SELECT FROM ' . $expectedQuery, + $this->queryBuilder->getSQL() + ); + } +} diff --git a/tests/lib/db/querybuilder/quotehelpertest.php b/tests/lib/db/querybuilder/quotehelpertest.php new file mode 100644 index 00000000000..904b4c500db --- /dev/null +++ b/tests/lib/db/querybuilder/quotehelpertest.php @@ -0,0 +1,141 @@ +<?php +/** + * @author Joas Schilling <nickvergessen@owncloud.com> + * + * @copyright Copyright (c) 2015, ownCloud, Inc. + * @license AGPL-3.0 + * + * This code is free software: you can redistribute it and/or modify + * it under the terms of the GNU Affero General Public License, version 3, + * as published by the Free Software Foundation. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + * GNU Affero General Public License for more details. + * + * You should have received a copy of the GNU Affero General Public License, version 3, + * along with this program. If not, see <http://www.gnu.org/licenses/> + * + */ + +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() { + parent::setUp(); + + $this->helper = new QuoteHelper(); + } + + public function dataQuoteColumnName() { + return [ + ['column', '`column`'], + [new Literal('literal'), 'literal'], + [new Literal(1), '1'], + [new Parameter(':param'), ':param'], + ]; + } + + /** + * @dataProvider dataQuoteColumnName + * @param mixed $input + * @param string $expected + */ + public function testQuoteColumnName($input, $expected) { + $this->assertSame( + $expected, + $this->helper->quoteColumnName($input) + ); + } + + public function dataQuoteColumnNames() { + 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']], + ]; + } + + /** + * @dataProvider dataQuoteColumnNames + * @param mixed $input + * @param string $expected + */ + public function testQuoteColumnNames($input, $expected) { + $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, '.')) { + list($alias, $columnName) = explode('.', $string); + return '`' . $alias . '`.`' . $columnName . '`'; + } + + return '`' . $string . '`'; + } +} |