1
0

FunctionBuilderTest.php 15 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491
  1. <?php
  2. /**
  3. * SPDX-FileCopyrightText: 2017 Nextcloud GmbH and Nextcloud contributors
  4. * SPDX-License-Identifier: AGPL-3.0-only
  5. */
  6. namespace Test\DB\QueryBuilder;
  7. use OC\DB\QueryBuilder\Literal;
  8. use OCP\DB\QueryBuilder\IQueryBuilder;
  9. use Test\TestCase;
  10. /**
  11. * Class FunctionBuilderTest
  12. *
  13. * @group DB
  14. *
  15. * @package Test\DB\QueryBuilder
  16. */
  17. class FunctionBuilderTest extends TestCase {
  18. /** @var \Doctrine\DBAL\Connection|\OCP\IDBConnection */
  19. protected $connection;
  20. protected function setUp(): void {
  21. parent::setUp();
  22. $this->connection = \OC::$server->getDatabaseConnection();
  23. }
  24. /**
  25. * @dataProvider providerTestConcatString
  26. */
  27. public function testConcatString($closure): void {
  28. $query = $this->connection->getQueryBuilder();
  29. [$real, $arguments, $return] = $closure($query);
  30. if ($real) {
  31. $this->addDummyData();
  32. $query->where($query->expr()->eq('appid', $query->createNamedParameter('group_concat')));
  33. $query->orderBy('configkey', 'asc');
  34. }
  35. $query->select($query->func()->concat(...$arguments));
  36. $query->from('appconfig')
  37. ->setMaxResults(1);
  38. $result = $query->execute();
  39. $column = $result->fetchOne();
  40. $result->closeCursor();
  41. $this->assertEquals($return, $column);
  42. }
  43. public function providerTestConcatString(): array {
  44. return [
  45. '1 column: string param unicode' =>
  46. [function ($q) {
  47. return [false, [$q->createNamedParameter('👍')], '👍'];
  48. }],
  49. '2 columns: string param and string param' =>
  50. [function ($q) {
  51. return [false, [$q->createNamedParameter('foo'), $q->createNamedParameter('bar')], 'foobar'];
  52. }],
  53. '2 columns: string param and int literal' =>
  54. [function ($q) {
  55. return [false, [$q->createNamedParameter('foo'), $q->expr()->literal(1)], 'foo1'];
  56. }],
  57. '2 columns: string param and string literal' =>
  58. [function ($q) {
  59. return [false, [$q->createNamedParameter('foo'), $q->expr()->literal('bar')], 'foobar'];
  60. }],
  61. '2 columns: string real and int literal' =>
  62. [function ($q) {
  63. return [true, ['configkey', $q->expr()->literal(2)], '12'];
  64. }],
  65. '4 columns: string literal' =>
  66. [function ($q) {
  67. return [false, [$q->expr()->literal('foo'), $q->expr()->literal('bar'), $q->expr()->literal('foo'), $q->expr()->literal('bar')], 'foobarfoobar'];
  68. }],
  69. '4 columns: int literal' =>
  70. [function ($q) {
  71. return [false, [$q->expr()->literal(1), $q->expr()->literal(2), $q->expr()->literal(3), $q->expr()->literal(4)], '1234'];
  72. }],
  73. '5 columns: string param with special chars used in the function' =>
  74. [function ($q) {
  75. return [false, [$q->createNamedParameter('b'), $q->createNamedParameter("'"), $q->createNamedParameter('||'), $q->createNamedParameter(','), $q->createNamedParameter('a')], "b'||,a"];
  76. }],
  77. ];
  78. }
  79. protected function clearDummyData(): void {
  80. $delete = $this->connection->getQueryBuilder();
  81. $delete->delete('appconfig')
  82. ->where($delete->expr()->eq('appid', $delete->createNamedParameter('group_concat')));
  83. $delete->executeStatement();
  84. }
  85. protected function addDummyData(): void {
  86. $this->clearDummyData();
  87. $insert = $this->connection->getQueryBuilder();
  88. $insert->insert('appconfig')
  89. ->setValue('appid', $insert->createNamedParameter('group_concat'))
  90. ->setValue('configvalue', $insert->createNamedParameter('unittest'))
  91. ->setValue('configkey', $insert->createParameter('value'));
  92. $insert->setParameter('value', '1');
  93. $insert->executeStatement();
  94. $insert->setParameter('value', '3');
  95. $insert->executeStatement();
  96. $insert->setParameter('value', '2');
  97. $insert->executeStatement();
  98. }
  99. public function testGroupConcatWithoutSeparator(): void {
  100. $this->addDummyData();
  101. $query = $this->connection->getQueryBuilder();
  102. $query->select($query->func()->groupConcat('configkey'))
  103. ->from('appconfig')
  104. ->where($query->expr()->eq('appid', $query->createNamedParameter('group_concat')));
  105. $result = $query->execute();
  106. $column = $result->fetchOne();
  107. $result->closeCursor();
  108. $this->assertStringContainsString(',', $column);
  109. $actual = explode(',', $column);
  110. $this->assertEqualsCanonicalizing([1,2,3], $actual);
  111. }
  112. public function testGroupConcatWithSeparator(): void {
  113. $this->addDummyData();
  114. $query = $this->connection->getQueryBuilder();
  115. $query->select($query->func()->groupConcat('configkey', '#'))
  116. ->from('appconfig')
  117. ->where($query->expr()->eq('appid', $query->createNamedParameter('group_concat')));
  118. $result = $query->execute();
  119. $column = $result->fetchOne();
  120. $result->closeCursor();
  121. $this->assertStringContainsString('#', $column);
  122. $actual = explode('#', $column);
  123. $this->assertEqualsCanonicalizing([1,2,3], $actual);
  124. }
  125. public function testGroupConcatWithSingleQuoteSeparator(): void {
  126. $this->addDummyData();
  127. $query = $this->connection->getQueryBuilder();
  128. $query->select($query->func()->groupConcat('configkey', '\''))
  129. ->from('appconfig')
  130. ->where($query->expr()->eq('appid', $query->createNamedParameter('group_concat')));
  131. $result = $query->execute();
  132. $column = $result->fetchOne();
  133. $result->closeCursor();
  134. $this->assertStringContainsString("'", $column);
  135. $actual = explode("'", $column);
  136. $this->assertEqualsCanonicalizing([1,2,3], $actual);
  137. }
  138. public function testGroupConcatWithDoubleQuoteSeparator(): void {
  139. $this->addDummyData();
  140. $query = $this->connection->getQueryBuilder();
  141. $query->select($query->func()->groupConcat('configkey', '"'))
  142. ->from('appconfig')
  143. ->where($query->expr()->eq('appid', $query->createNamedParameter('group_concat')));
  144. $result = $query->execute();
  145. $column = $result->fetchOne();
  146. $result->closeCursor();
  147. $this->assertStringContainsString('"', $column);
  148. $actual = explode('"', $column);
  149. $this->assertEqualsCanonicalizing([1,2,3], $actual);
  150. }
  151. protected function clearIntDummyData(): void {
  152. $delete = $this->connection->getQueryBuilder();
  153. $delete->delete('systemtag')
  154. ->where($delete->expr()->eq('name', $delete->createNamedParameter('group_concat')));
  155. $delete->executeStatement();
  156. }
  157. protected function addIntDummyData(): void {
  158. $this->clearIntDummyData();
  159. $insert = $this->connection->getQueryBuilder();
  160. $insert->insert('systemtag')
  161. ->setValue('name', $insert->createNamedParameter('group_concat'))
  162. ->setValue('visibility', $insert->createNamedParameter(1))
  163. ->setValue('editable', $insert->createParameter('value'));
  164. $insert->setParameter('value', 1);
  165. $insert->executeStatement();
  166. $insert->setParameter('value', 2);
  167. $insert->executeStatement();
  168. $insert->setParameter('value', 3);
  169. $insert->executeStatement();
  170. }
  171. public function testIntGroupConcatWithoutSeparator(): void {
  172. $this->addIntDummyData();
  173. $query = $this->connection->getQueryBuilder();
  174. $query->select($query->func()->groupConcat('editable'))
  175. ->from('systemtag')
  176. ->where($query->expr()->eq('name', $query->createNamedParameter('group_concat')));
  177. $result = $query->execute();
  178. $column = $result->fetchOne();
  179. $result->closeCursor();
  180. $this->assertStringContainsString(',', $column);
  181. $actual = explode(',', $column);
  182. $this->assertEqualsCanonicalizing([1,2,3], $actual);
  183. }
  184. public function testIntGroupConcatWithSeparator(): void {
  185. $this->addIntDummyData();
  186. $query = $this->connection->getQueryBuilder();
  187. $query->select($query->func()->groupConcat('editable', '#'))
  188. ->from('systemtag')
  189. ->where($query->expr()->eq('name', $query->createNamedParameter('group_concat')));
  190. $result = $query->execute();
  191. $column = $result->fetchOne();
  192. $result->closeCursor();
  193. $this->assertStringContainsString('#', $column);
  194. $actual = explode('#', $column);
  195. $this->assertEqualsCanonicalizing([1,2,3], $actual);
  196. }
  197. public function testMd5(): void {
  198. $query = $this->connection->getQueryBuilder();
  199. $query->select($query->func()->md5($query->createNamedParameter('foobar')));
  200. $query->from('appconfig')
  201. ->setMaxResults(1);
  202. $result = $query->execute();
  203. $column = $result->fetchOne();
  204. $result->closeCursor();
  205. $this->assertEquals(md5('foobar'), $column);
  206. }
  207. public function testSubstring(): void {
  208. $query = $this->connection->getQueryBuilder();
  209. $query->select($query->func()->substring($query->createNamedParameter('foobar'), new Literal(2), $query->createNamedParameter(2)));
  210. $query->from('appconfig')
  211. ->setMaxResults(1);
  212. $result = $query->execute();
  213. $column = $result->fetchOne();
  214. $result->closeCursor();
  215. $this->assertEquals('oo', $column);
  216. }
  217. public function testSubstringNoLength(): void {
  218. $query = $this->connection->getQueryBuilder();
  219. $query->select($query->func()->substring($query->createNamedParameter('foobar'), new Literal(2)));
  220. $query->from('appconfig')
  221. ->setMaxResults(1);
  222. $result = $query->execute();
  223. $column = $result->fetchOne();
  224. $result->closeCursor();
  225. $this->assertEquals('oobar', $column);
  226. }
  227. public function testLower(): void {
  228. $query = $this->connection->getQueryBuilder();
  229. $query->select($query->func()->lower($query->createNamedParameter('FooBar')));
  230. $query->from('appconfig')
  231. ->setMaxResults(1);
  232. $result = $query->execute();
  233. $column = $result->fetchOne();
  234. $result->closeCursor();
  235. $this->assertEquals('foobar', $column);
  236. }
  237. public function testAdd(): void {
  238. $query = $this->connection->getQueryBuilder();
  239. $query->select($query->func()->add($query->createNamedParameter(2, IQueryBuilder::PARAM_INT), new Literal(1)));
  240. $query->from('appconfig')
  241. ->setMaxResults(1);
  242. $result = $query->execute();
  243. $column = $result->fetchOne();
  244. $result->closeCursor();
  245. $this->assertEquals(3, $column);
  246. }
  247. public function testSubtract(): void {
  248. $query = $this->connection->getQueryBuilder();
  249. $query->select($query->func()->subtract($query->createNamedParameter(2, IQueryBuilder::PARAM_INT), new Literal(1)));
  250. $query->from('appconfig')
  251. ->setMaxResults(1);
  252. $result = $query->execute();
  253. $column = $result->fetchOne();
  254. $result->closeCursor();
  255. $this->assertEquals(1, $column);
  256. }
  257. public function testCount(): void {
  258. $query = $this->connection->getQueryBuilder();
  259. $query->select($query->func()->count('appid'));
  260. $query->from('appconfig')
  261. ->setMaxResults(1);
  262. $result = $query->execute();
  263. $column = $result->fetchOne();
  264. $result->closeCursor();
  265. $this->assertGreaterThan(1, $column);
  266. }
  267. public function octetLengthProvider() {
  268. return [
  269. ['', 0],
  270. ['foobar', 6],
  271. ['fé', 3],
  272. ['šđčćž', 10],
  273. ];
  274. }
  275. /**
  276. * @dataProvider octetLengthProvider
  277. */
  278. public function testOctetLength(string $str, int $bytes): void {
  279. $query = $this->connection->getQueryBuilder();
  280. $query->select($query->func()->octetLength($query->createNamedParameter($str, IQueryBuilder::PARAM_STR)));
  281. $query->from('appconfig')
  282. ->setMaxResults(1);
  283. $result = $query->execute();
  284. $column = $result->fetchOne();
  285. $result->closeCursor();
  286. $this->assertEquals($bytes, $column);
  287. }
  288. public function charLengthProvider() {
  289. return [
  290. ['', 0],
  291. ['foobar', 6],
  292. ['fé', 2],
  293. ['šđčćž', 5],
  294. ];
  295. }
  296. /**
  297. * @dataProvider charLengthProvider
  298. */
  299. public function testCharLength(string $str, int $bytes): void {
  300. $query = $this->connection->getQueryBuilder();
  301. $query->select($query->func()->charLength($query->createNamedParameter($str, IQueryBuilder::PARAM_STR)));
  302. $query->from('appconfig')
  303. ->setMaxResults(1);
  304. $result = $query->execute();
  305. $column = $result->fetchOne();
  306. $result->closeCursor();
  307. $this->assertEquals($bytes, $column);
  308. }
  309. private function setUpMinMax($value) {
  310. $query = $this->connection->getQueryBuilder();
  311. $query->insert('appconfig')
  312. ->values([
  313. 'appid' => $query->createNamedParameter('minmax'),
  314. 'configkey' => $query->createNamedParameter(uniqid()),
  315. 'configvalue' => $query->createNamedParameter((string)$value),
  316. ]);
  317. $query->execute();
  318. }
  319. private function clearMinMax() {
  320. $query = $this->connection->getQueryBuilder();
  321. $query->delete('appconfig')
  322. ->where($query->expr()->eq('appid', $query->createNamedParameter('minmax')));
  323. $query->execute();
  324. }
  325. public function testMaxEmpty(): void {
  326. $this->clearMinMax();
  327. $query = $this->connection->getQueryBuilder();
  328. $query->select($query->func()->max($query->expr()->castColumn('configvalue', IQueryBuilder::PARAM_INT)));
  329. $query->from('appconfig')
  330. ->where($query->expr()->eq('appid', $query->createNamedParameter('minmax')))
  331. ->setMaxResults(1);
  332. $result = $query->execute();
  333. $row = $result->fetchOne();
  334. $result->closeCursor();
  335. $this->assertEquals(null, $row);
  336. }
  337. public function testMinEmpty(): void {
  338. $this->clearMinMax();
  339. $query = $this->connection->getQueryBuilder();
  340. $query->select($query->func()->min($query->expr()->castColumn('configvalue', IQueryBuilder::PARAM_INT)));
  341. $query->from('appconfig')
  342. ->where($query->expr()->eq('appid', $query->createNamedParameter('minmax')))
  343. ->setMaxResults(1);
  344. $result = $query->execute();
  345. $row = $result->fetchOne();
  346. $result->closeCursor();
  347. $this->assertEquals(null, $row);
  348. }
  349. public function testMax(): void {
  350. $this->clearMinMax();
  351. $this->setUpMinMax(10);
  352. $this->setUpMinMax(11);
  353. $this->setUpMinMax(20);
  354. $query = $this->connection->getQueryBuilder();
  355. $query->select($query->func()->max($query->expr()->castColumn('configvalue', IQueryBuilder::PARAM_INT)));
  356. $query->from('appconfig')
  357. ->where($query->expr()->eq('appid', $query->createNamedParameter('minmax')))
  358. ->setMaxResults(1);
  359. $result = $query->execute();
  360. $row = $result->fetchOne();
  361. $result->closeCursor();
  362. $this->assertEquals(20, $row);
  363. }
  364. public function testMin(): void {
  365. $this->clearMinMax();
  366. $this->setUpMinMax(10);
  367. $this->setUpMinMax(11);
  368. $this->setUpMinMax(20);
  369. $query = $this->connection->getQueryBuilder();
  370. $query->select($query->func()->min($query->expr()->castColumn('configvalue', IQueryBuilder::PARAM_INT)));
  371. $query->from('appconfig')
  372. ->where($query->expr()->eq('appid', $query->createNamedParameter('minmax')))
  373. ->setMaxResults(1);
  374. $result = $query->execute();
  375. $row = $result->fetchOne();
  376. $result->closeCursor();
  377. $this->assertEquals(10, $row);
  378. }
  379. public function testGreatest(): void {
  380. $query = $this->connection->getQueryBuilder();
  381. $query->select($query->func()->greatest($query->createNamedParameter(2, IQueryBuilder::PARAM_INT), new Literal(1)));
  382. $query->from('appconfig')
  383. ->setMaxResults(1);
  384. $result = $query->execute();
  385. $row = $result->fetchOne();
  386. $result->closeCursor();
  387. $this->assertEquals(2, $row);
  388. }
  389. public function testLeast(): void {
  390. $query = $this->connection->getQueryBuilder();
  391. $query->select($query->func()->least($query->createNamedParameter(2, IQueryBuilder::PARAM_INT), new Literal(1)));
  392. $query->from('appconfig')
  393. ->setMaxResults(1);
  394. $result = $query->execute();
  395. $row = $result->fetchOne();
  396. $result->closeCursor();
  397. $this->assertEquals(1, $row);
  398. }
  399. }