1
0

PartitionedQueryBuilderTest.php 8.7 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222
  1. <?php
  2. /**
  3. * SPDX-FileCopyrightText: 2024 Nextcloud GmbH and Nextcloud contributors
  4. * SPDX-License-Identifier: AGPL-3.0-or-later
  5. */
  6. declare(strict_types=1);
  7. namespace Test\DB\QueryBuilder\Partitioned;
  8. use OC\DB\QueryBuilder\Partitioned\PartitionedQueryBuilder;
  9. use OC\DB\QueryBuilder\Partitioned\PartitionSplit;
  10. use OC\DB\QueryBuilder\Sharded\AutoIncrementHandler;
  11. use OC\DB\QueryBuilder\Sharded\ShardConnectionManager;
  12. use OCP\DB\QueryBuilder\IQueryBuilder;
  13. use OCP\IDBConnection;
  14. use OCP\Server;
  15. use Test\TestCase;
  16. /**
  17. * @group DB
  18. */
  19. class PartitionedQueryBuilderTest extends TestCase {
  20. private IDBConnection $connection;
  21. private ShardConnectionManager $shardConnectionManager;
  22. private AutoIncrementHandler $autoIncrementHandler;
  23. protected function setUp(): void {
  24. if (PHP_INT_SIZE < 8) {
  25. $this->markTestSkipped('Test requires 64bit');
  26. }
  27. $this->connection = Server::get(IDBConnection::class);
  28. $this->shardConnectionManager = Server::get(ShardConnectionManager::class);
  29. $this->autoIncrementHandler = Server::get(AutoIncrementHandler::class);
  30. $this->setupFileCache();
  31. }
  32. protected function tearDown(): void {
  33. $this->cleanupDb();
  34. parent::tearDown();
  35. }
  36. private function getQueryBuilder(): PartitionedQueryBuilder {
  37. $builder = $this->connection->getQueryBuilder();
  38. if ($builder instanceof PartitionedQueryBuilder) {
  39. return $builder;
  40. } else {
  41. return new PartitionedQueryBuilder($builder, [], $this->shardConnectionManager, $this->autoIncrementHandler);
  42. }
  43. }
  44. private function setupFileCache(): void {
  45. $this->cleanupDb();
  46. $query = $this->getQueryBuilder();
  47. $query->insert('storages')
  48. ->values([
  49. 'numeric_id' => $query->createNamedParameter(1001001, IQueryBuilder::PARAM_INT),
  50. 'id' => $query->createNamedParameter('test1'),
  51. ]);
  52. $query->executeStatement();
  53. $query = $this->getQueryBuilder();
  54. $query->insert('filecache')
  55. ->values([
  56. 'storage' => $query->createNamedParameter(1001001, IQueryBuilder::PARAM_INT),
  57. 'path' => $query->createNamedParameter('file1'),
  58. 'path_hash' => $query->createNamedParameter(md5('file1')),
  59. ]);
  60. $query->executeStatement();
  61. $fileId = $query->getLastInsertId();
  62. $query = $this->getQueryBuilder();
  63. $query->insert('filecache_extended')
  64. ->hintShardKey('storage', 1001001)
  65. ->values([
  66. 'fileid' => $query->createNamedParameter($fileId, IQueryBuilder::PARAM_INT),
  67. 'upload_time' => $query->createNamedParameter(1234, IQueryBuilder::PARAM_INT),
  68. ]);
  69. $query->executeStatement();
  70. $query = $this->getQueryBuilder();
  71. $query->insert('mounts')
  72. ->values([
  73. 'storage_id' => $query->createNamedParameter(1001001, IQueryBuilder::PARAM_INT),
  74. 'user_id' => $query->createNamedParameter('partitioned_test'),
  75. 'mount_point' => $query->createNamedParameter('/mount/point'),
  76. 'mount_provider_class' => $query->createNamedParameter('test'),
  77. 'root_id' => $query->createNamedParameter($fileId, IQueryBuilder::PARAM_INT),
  78. ]);
  79. $query->executeStatement();
  80. }
  81. private function cleanupDb(): void {
  82. $query = $this->getQueryBuilder();
  83. $query->delete('storages')
  84. ->where($query->expr()->gt('numeric_id', $query->createNamedParameter(1000000, IQueryBuilder::PARAM_INT)));
  85. $query->executeStatement();
  86. $query = $this->getQueryBuilder();
  87. $query->delete('filecache')
  88. ->where($query->expr()->gt('storage', $query->createNamedParameter(1000000, IQueryBuilder::PARAM_INT)))
  89. ->runAcrossAllShards();
  90. $query->executeStatement();
  91. $query = $this->getQueryBuilder();
  92. $query->delete('filecache_extended')
  93. ->runAcrossAllShards();
  94. $query->executeStatement();
  95. $query = $this->getQueryBuilder();
  96. $query->delete('mounts')
  97. ->where($query->expr()->like('user_id', $query->createNamedParameter('partitioned_%')));
  98. $query->executeStatement();
  99. }
  100. public function testSimpleOnlyPartitionQuery(): void {
  101. $builder = $this->getQueryBuilder();
  102. $builder->addPartition(new PartitionSplit('filecache', ['filecache']));
  103. // query borrowed from UserMountCache
  104. $query = $builder->select('path')
  105. ->from('filecache')
  106. ->where($builder->expr()->eq('storage', $builder->createNamedParameter(1001001, IQueryBuilder::PARAM_INT)));
  107. $results = $query->executeQuery()->fetchAll();
  108. $this->assertCount(1, $results);
  109. $this->assertEquals($results[0]['path'], 'file1');
  110. }
  111. public function testSimplePartitionedQuery(): void {
  112. $builder = $this->getQueryBuilder();
  113. $builder->addPartition(new PartitionSplit('filecache', ['filecache']));
  114. // query borrowed from UserMountCache
  115. $query = $builder->select('storage_id', 'root_id', 'user_id', 'mount_point', 'mount_id', 'f.path', 'mount_provider_class')
  116. ->from('mounts', 'm')
  117. ->innerJoin('m', 'filecache', 'f', $builder->expr()->eq('m.root_id', 'f.fileid'))
  118. ->where($builder->expr()->eq('storage_id', $builder->createNamedParameter(1001001, IQueryBuilder::PARAM_INT)));
  119. $query->andWhere($builder->expr()->eq('user_id', $builder->createNamedParameter('partitioned_test')));
  120. $this->assertEquals(2, $query->getPartitionCount());
  121. $results = $query->executeQuery()->fetchAll();
  122. $this->assertCount(1, $results);
  123. $this->assertEquals($results[0]['user_id'], 'partitioned_test');
  124. $this->assertEquals($results[0]['mount_point'], '/mount/point');
  125. $this->assertEquals($results[0]['mount_provider_class'], 'test');
  126. $this->assertEquals($results[0]['path'], 'file1');
  127. }
  128. public function testMultiTablePartitionedQuery(): void {
  129. $builder = $this->getQueryBuilder();
  130. $builder->addPartition(new PartitionSplit('filecache', ['filecache', 'filecache_extended']));
  131. $query = $builder->select('storage_id', 'root_id', 'user_id', 'mount_point', 'mount_id', 'f.path', 'mount_provider_class', 'fe.upload_time')
  132. ->from('mounts', 'm')
  133. ->innerJoin('m', 'filecache', 'f', $builder->expr()->eq('m.root_id', 'f.fileid'))
  134. ->innerJoin('f', 'filecache_extended', 'fe', $builder->expr()->eq('f.fileid', 'fe.fileid'))
  135. ->where($builder->expr()->eq('storage_id', $builder->createNamedParameter(1001001, IQueryBuilder::PARAM_INT)));
  136. $query->andWhere($builder->expr()->eq('user_id', $builder->createNamedParameter('partitioned_test')));
  137. $this->assertEquals(2, $query->getPartitionCount());
  138. $results = $query->executeQuery()->fetchAll();
  139. $this->assertCount(1, $results);
  140. $this->assertEquals($results[0]['user_id'], 'partitioned_test');
  141. $this->assertEquals($results[0]['mount_point'], '/mount/point');
  142. $this->assertEquals($results[0]['mount_provider_class'], 'test');
  143. $this->assertEquals($results[0]['path'], 'file1');
  144. $this->assertEquals($results[0]['upload_time'], 1234);
  145. }
  146. public function testPartitionedQueryFromSplit(): void {
  147. $builder = $this->getQueryBuilder();
  148. $builder->addPartition(new PartitionSplit('filecache', ['filecache']));
  149. $query = $builder->select('storage', 'm.root_id', 'm.user_id', 'm.mount_point', 'm.mount_id', 'path', 'm.mount_provider_class')
  150. ->from('filecache', 'f')
  151. ->innerJoin('f', 'mounts', 'm', $builder->expr()->eq('m.root_id', 'f.fileid'));
  152. $query->where($builder->expr()->eq('storage', $builder->createNamedParameter(1001001, IQueryBuilder::PARAM_INT)));
  153. $query->andWhere($builder->expr()->eq('m.user_id', $builder->createNamedParameter('partitioned_test')));
  154. $this->assertEquals(2, $query->getPartitionCount());
  155. $results = $query->executeQuery()->fetchAll();
  156. $this->assertCount(1, $results);
  157. $this->assertEquals($results[0]['user_id'], 'partitioned_test');
  158. $this->assertEquals($results[0]['mount_point'], '/mount/point');
  159. $this->assertEquals($results[0]['mount_provider_class'], 'test');
  160. $this->assertEquals($results[0]['path'], 'file1');
  161. }
  162. public function testMultiJoinPartitionedQuery(): void {
  163. $builder = $this->getQueryBuilder();
  164. $builder->addPartition(new PartitionSplit('filecache', ['filecache']));
  165. // query borrowed from UserMountCache
  166. $query = $builder->select('storage_id', 'root_id', 'user_id', 'mount_point', 'mount_id', 'f.path', 'mount_provider_class')
  167. ->selectAlias('s.id', 'storage_string_id')
  168. ->from('mounts', 'm')
  169. ->innerJoin('m', 'filecache', 'f', $builder->expr()->eq('m.root_id', 'f.fileid'))
  170. ->innerJoin('f', 'storages', 's', $builder->expr()->eq('f.storage', 's.numeric_id'))
  171. ->where($builder->expr()->eq('storage_id', $builder->createNamedParameter(1001001, IQueryBuilder::PARAM_INT)));
  172. $query->andWhere($builder->expr()->eq('user_id', $builder->createNamedParameter('partitioned_test')));
  173. $this->assertEquals(3, $query->getPartitionCount());
  174. $results = $query->executeQuery()->fetchAll();
  175. $this->assertCount(1, $results);
  176. $this->assertEquals($results[0]['user_id'], 'partitioned_test');
  177. $this->assertEquals($results[0]['mount_point'], '/mount/point');
  178. $this->assertEquals($results[0]['mount_provider_class'], 'test');
  179. $this->assertEquals($results[0]['path'], 'file1');
  180. $this->assertEquals($results[0]['storage_string_id'], 'test1');
  181. }
  182. }