perf(sharing): Use oc_share.{item_type, share_type} IN instead of OR equalsrefactor/use-in-instead-of-or

to improve performance by using the db index

Signed-off-by: Marcel Klehr <mklehr@gmx.net>

4 files changed, 9 insertions, 32 deletions

diff --git a/apps/federatedfilesharing/lib/FederatedShareProvider.php b/apps/federatedfilesharing/lib/FederatedShareProvider.php
index 58eaca0361e..379cd793116 100644
--- a/apps/federatedfilesharing/lib/FederatedShareProvider.php
+++ b/apps/federatedfilesharing/lib/FederatedShareProvider.php
@@ -557,10 +557,7 @@ class FederatedShareProvider implements IShareProvider {
 		$qb = $this->dbConnection->getQueryBuilder();
 		$qb->select('*')
 			->from('share', 's')
-			->andWhere($qb->expr()->orX(
-				$qb->expr()->eq('item_type', $qb->createNamedParameter('file')),
-				$qb->expr()->eq('item_type', $qb->createNamedParameter('folder'))
-			))
+			->andWhere($qb->expr()->in('item_type', $qb->createNamedParameter(['file', 'folder'], IQueryBuilder::PARAM_STR_ARRAY)))
 			->andWhere(
 				$qb->expr()->eq('share_type', $qb->createNamedParameter(IShare::TYPE_REMOTE))
 			);
@@ -1038,10 +1035,7 @@ class FederatedShareProvider implements IShareProvider {
 			->from('share')
 			->where($qb->expr()->eq('share_type', $qb->createNamedParameter(IShare::TYPE_REMOTE)))
 			->andWhere($qb->expr()->in('file_source', $qb->createNamedParameter($ids, IQueryBuilder::PARAM_INT_ARRAY)))
-			->andWhere($qb->expr()->orX(
-				$qb->expr()->eq('item_type', $qb->createNamedParameter('file')),
-				$qb->expr()->eq('item_type', $qb->createNamedParameter('folder'))
-			));
+			->andWhere($qb->expr()->in('item_type', $qb->createNamedParameter(['file', 'folder'], IQueryBuilder::PARAM_STR_ARRAY)));
 		$cursor = $qb->executeQuery();
 
 		if ($currentAccess === false) {
@@ -1068,12 +1062,7 @@ class FederatedShareProvider implements IShareProvider {
 
 		$qb->select('*')
 			->from('share')
-			->where(
-				$qb->expr()->orX(
-					$qb->expr()->eq('share_type', $qb->createNamedParameter(IShare::TYPE_REMOTE)),
-					$qb->expr()->eq('share_type', $qb->createNamedParameter(IShare::TYPE_REMOTE_GROUP))
-				)
-			);
+			->where($qb->expr()->in('share_type', $qb->createNamedParameter([IShare::TYPE_REMOTE_GROUP, IShare::TYPE_REMOTE], IQueryBuilder::PARAM_INT_ARRAY)));
 
 		$cursor = $qb->executeQuery();
 		while ($data = $cursor->fetch()) {
diff --git a/apps/files_sharing/lib/ExpireSharesJob.php b/apps/files_sharing/lib/ExpireSharesJob.php
index 8ea6fee8a5c..d78352cfeaa 100644
--- a/apps/files_sharing/lib/ExpireSharesJob.php
+++ b/apps/files_sharing/lib/ExpireSharesJob.php
@@ -8,6 +8,7 @@ namespace OCA\Files_Sharing;
 
 use OCP\AppFramework\Utility\ITimeFactory;
 use OCP\BackgroundJob\TimedJob;
+use OCP\DB\QueryBuilder\IQueryBuilder;
 use OCP\IDBConnection;
 use OCP\Share\Exceptions\ShareNotFound;
 use OCP\Share\IManager;
@@ -49,15 +50,9 @@ class ExpireSharesJob extends TimedJob {
 			->from('share')
 			->where(
 				$qb->expr()->andX(
-					$qb->expr()->orX(
-						$qb->expr()->eq('share_type', $qb->expr()->literal(IShare::TYPE_LINK)),
-						$qb->expr()->eq('share_type', $qb->expr()->literal(IShare::TYPE_EMAIL))
-					),
+					$qb->expr()->in('share_type', $qb->createNamedParameter([IShare::TYPE_LINK, IShare::TYPE_EMAIL], IQueryBuilder::PARAM_INT_ARRAY)),
 					$qb->expr()->lte('expiration', $qb->expr()->literal($now)),
-					$qb->expr()->orX(
-						$qb->expr()->eq('item_type', $qb->expr()->literal('file')),
-						$qb->expr()->eq('item_type', $qb->expr()->literal('folder'))
-					)
+					$qb->expr()->in('item_type', $qb->createNamedParameter(['file', 'folder'], IQueryBuilder::PARAM_STR_ARRAY))
 				)
 			);
 
diff --git a/apps/files_sharing/lib/OrphanHelper.php b/apps/files_sharing/lib/OrphanHelper.php
index 4a52af0406c..6e070f1446b 100644
--- a/apps/files_sharing/lib/OrphanHelper.php
+++ b/apps/files_sharing/lib/OrphanHelper.php
@@ -58,8 +58,7 @@ class OrphanHelper {
 		$query = $this->connection->getQueryBuilder();
 		$query->select('id', 'file_source', 'uid_owner', 'file_target')
 			->from('share')
-			->where($query->expr()->eq('item_type', $query->createNamedParameter('file')))
-			->orWhere($query->expr()->eq('item_type', $query->createNamedParameter('folder')));
+			->where($query->expr()->in('item_type', $query->createNamedParameter(['file', 'folder'], IQueryBuilder::PARAM_STR_ARRAY)));
 		$result = $query->executeQuery();
 		while ($row = $result->fetch()) {
 			yield [
diff --git a/apps/sharebymail/lib/ShareByMailProvider.php b/apps/sharebymail/lib/ShareByMailProvider.php
index 7d0665ca630..e6e1cf509e9 100644
--- a/apps/sharebymail/lib/ShareByMailProvider.php
+++ b/apps/sharebymail/lib/ShareByMailProvider.php
@@ -1115,10 +1115,7 @@ class ShareByMailProvider extends DefaultShareProvider implements IShareProvider
 		$qb = $this->dbConnection->getQueryBuilder();
 		$qb->select('*')
 			->from('share', 's')
-			->andWhere($qb->expr()->orX(
-				$qb->expr()->eq('item_type', $qb->createNamedParameter('file')),
-				$qb->expr()->eq('item_type', $qb->createNamedParameter('folder'))
-			))
+			->andWhere($qb->expr()->in('item_type', $qb->createNamedParameter(['file', 'folder'], IQueryBuilder::PARAM_STR_ARRAY)))
 			->andWhere(
 				$qb->expr()->eq('share_type', $qb->createNamedParameter(IShare::TYPE_EMAIL))
 			);
@@ -1167,10 +1164,7 @@ class ShareByMailProvider extends DefaultShareProvider implements IShareProvider
 			->from('share')
 			->where($qb->expr()->eq('share_type', $qb->createNamedParameter(IShare::TYPE_EMAIL)))
 			->andWhere($qb->expr()->in('file_source', $qb->createNamedParameter($ids, IQueryBuilder::PARAM_INT_ARRAY)))
-			->andWhere($qb->expr()->orX(
-				$qb->expr()->eq('item_type', $qb->createNamedParameter('file')),
-				$qb->expr()->eq('item_type', $qb->createNamedParameter('folder'))
-			));
+			->andWhere($qb->expr()->in('item_type', $qb->createNamedParameter(['file', 'folder'], IQueryBuilder::PARAM_STR_ARRAY)));
 		$cursor = $qb->executeQuery();
 
 		$public = false;