1
0

ExpressionBuilderDBTest.php 4.7 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163
  1. <?php
  2. /**
  3. * @copyright Copyright (c) 2017 Robin Appelman <robin@icewind.nl>
  4. *
  5. * @license GNU AGPL version 3 or any later version
  6. *
  7. * This program is free software: you can redistribute it and/or modify
  8. * it under the terms of the GNU Affero General Public License as
  9. * published by the Free Software Foundation, either version 3 of the
  10. * License, or (at your option) any later version.
  11. *
  12. * This program is distributed in the hope that it will be useful,
  13. * but WITHOUT ANY WARRANTY; without even the implied warranty of
  14. * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
  15. * GNU Affero General Public License for more details.
  16. *
  17. * You should have received a copy of the GNU Affero General Public License
  18. * along with this program. If not, see <http://www.gnu.org/licenses/>.
  19. *
  20. */
  21. namespace Test\DB\QueryBuilder;
  22. use OC\DB\QueryBuilder\Literal;
  23. use OCP\DB\QueryBuilder\IQueryBuilder;
  24. use Test\TestCase;
  25. /**
  26. * @group DB
  27. */
  28. class ExpressionBuilderDBTest extends TestCase {
  29. /** @var \Doctrine\DBAL\Connection|\OCP\IDBConnection */
  30. protected $connection;
  31. protected function setUp(): void {
  32. parent::setUp();
  33. $this->connection = \OC::$server->getDatabaseConnection();
  34. }
  35. public function likeProvider() {
  36. $connection = \OC::$server->getDatabaseConnection();
  37. return [
  38. ['foo', 'bar', false],
  39. ['foo', 'foo', true],
  40. ['foo', 'f%', true],
  41. ['foo', '%o', true],
  42. ['foo', '%', true],
  43. ['foo', 'fo_', true],
  44. ['foo', 'foo_', false],
  45. ['foo', $connection->escapeLikeParameter('fo_'), false],
  46. ['foo', $connection->escapeLikeParameter('f%'), false],
  47. ];
  48. }
  49. /**
  50. * @dataProvider likeProvider
  51. *
  52. * @param string $param1
  53. * @param string $param2
  54. * @param boolean $match
  55. */
  56. public function testLike($param1, $param2, $match) {
  57. $query = $this->connection->getQueryBuilder();
  58. $query->select(new Literal('1'))
  59. ->from('users')
  60. ->where($query->expr()->like($query->createNamedParameter($param1), $query->createNamedParameter($param2)));
  61. $result = $query->execute();
  62. $column = $result->fetchOne();
  63. $result->closeCursor();
  64. $this->assertEquals($match, $column);
  65. }
  66. public function ilikeProvider() {
  67. $connection = \OC::$server->getDatabaseConnection();
  68. return [
  69. ['foo', 'bar', false],
  70. ['foo', 'foo', true],
  71. ['foo', 'Foo', true],
  72. ['foo', 'f%', true],
  73. ['foo', '%o', true],
  74. ['foo', '%', true],
  75. ['foo', 'fo_', true],
  76. ['foo', 'foo_', false],
  77. ['foo', $connection->escapeLikeParameter('fo_'), false],
  78. ['foo', $connection->escapeLikeParameter('f%'), false],
  79. ];
  80. }
  81. /**
  82. * @dataProvider ilikeProvider
  83. *
  84. * @param string $param1
  85. * @param string $param2
  86. * @param boolean $match
  87. */
  88. public function testILike($param1, $param2, $match) {
  89. $query = $this->connection->getQueryBuilder();
  90. $query->select(new Literal('1'))
  91. ->from('users')
  92. ->where($query->expr()->iLike($query->createNamedParameter($param1), $query->createNamedParameter($param2)));
  93. $result = $query->execute();
  94. $column = $result->fetchOne();
  95. $result->closeCursor();
  96. $this->assertEquals($match, $column);
  97. }
  98. public function testCastColumn(): void {
  99. $appId = $this->getUniqueID('testing');
  100. $this->createConfig($appId, '1', '4');
  101. $query = $this->connection->getQueryBuilder();
  102. $query->update('appconfig')
  103. ->set('configvalue',
  104. $query->expr()->castColumn(
  105. $query->createFunction(
  106. '(' . $query->expr()->castColumn('configvalue', IQueryBuilder::PARAM_INT)
  107. . ' + 1)'
  108. ), IQueryBuilder::PARAM_STR
  109. )
  110. )
  111. ->where($query->expr()->eq('appid', $query->createNamedParameter($appId)))
  112. ->andWhere($query->expr()->eq('configkey', $query->createNamedParameter('1')));
  113. $result = $query->executeStatement();
  114. $this->assertEquals(1, $result);
  115. }
  116. public function testLongText(): void {
  117. $appId = $this->getUniqueID('testing');
  118. $this->createConfig($appId, 'mykey', 'myvalue');
  119. $query = $this->connection->getQueryBuilder();
  120. $query->select('*')
  121. ->from('appconfig')
  122. ->where($query->expr()->eq('appid', $query->createNamedParameter($appId)))
  123. ->andWhere($query->expr()->eq('configkey', $query->createNamedParameter('mykey')))
  124. ->andWhere($query->expr()->eq('configvalue', $query->createNamedParameter('myvalue', IQueryBuilder::PARAM_STR), IQueryBuilder::PARAM_STR));
  125. $result = $query->executeQuery();
  126. $entries = $result->fetchAll();
  127. $result->closeCursor();
  128. self::assertCount(1, $entries);
  129. self::assertEquals('myvalue', $entries[0]['configvalue']);
  130. }
  131. protected function createConfig($appId, $key, $value) {
  132. $query = $this->connection->getQueryBuilder();
  133. $query->insert('appconfig')
  134. ->values([
  135. 'appid' => $query->createNamedParameter($appId),
  136. 'configkey' => $query->createNamedParameter((string) $key),
  137. 'configvalue' => $query->createNamedParameter((string) $value),
  138. ])
  139. ->execute();
  140. }
  141. }