1
0

SearchBuilder.php 12 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349
  1. <?php
  2. /**
  3. * SPDX-FileCopyrightText: 2017 Nextcloud GmbH and Nextcloud contributors
  4. * SPDX-License-Identifier: AGPL-3.0-or-later
  5. */
  6. namespace OC\Files\Cache;
  7. use OCP\DB\QueryBuilder\IQueryBuilder;
  8. use OCP\Files\IMimeTypeLoader;
  9. use OCP\Files\Search\ISearchBinaryOperator;
  10. use OCP\Files\Search\ISearchComparison;
  11. use OCP\Files\Search\ISearchOperator;
  12. use OCP\Files\Search\ISearchOrder;
  13. use OCP\FilesMetadata\IMetadataQuery;
  14. /**
  15. * Tools for transforming search queries into database queries
  16. *
  17. * @psalm-import-type ParamSingleValue from ISearchComparison
  18. * @psalm-import-type ParamValue from ISearchComparison
  19. */
  20. class SearchBuilder {
  21. /** @var array<string, string> */
  22. protected static $searchOperatorMap = [
  23. ISearchComparison::COMPARE_LIKE => 'iLike',
  24. ISearchComparison::COMPARE_LIKE_CASE_SENSITIVE => 'like',
  25. ISearchComparison::COMPARE_EQUAL => 'eq',
  26. ISearchComparison::COMPARE_GREATER_THAN => 'gt',
  27. ISearchComparison::COMPARE_GREATER_THAN_EQUAL => 'gte',
  28. ISearchComparison::COMPARE_LESS_THAN => 'lt',
  29. ISearchComparison::COMPARE_LESS_THAN_EQUAL => 'lte',
  30. ISearchComparison::COMPARE_DEFINED => 'isNotNull',
  31. ISearchComparison::COMPARE_IN => 'in',
  32. ];
  33. /** @var array<string, string> */
  34. protected static $searchOperatorNegativeMap = [
  35. ISearchComparison::COMPARE_LIKE => 'notLike',
  36. ISearchComparison::COMPARE_LIKE_CASE_SENSITIVE => 'notLike',
  37. ISearchComparison::COMPARE_EQUAL => 'neq',
  38. ISearchComparison::COMPARE_GREATER_THAN => 'lte',
  39. ISearchComparison::COMPARE_GREATER_THAN_EQUAL => 'lt',
  40. ISearchComparison::COMPARE_LESS_THAN => 'gte',
  41. ISearchComparison::COMPARE_LESS_THAN_EQUAL => 'gt',
  42. ISearchComparison::COMPARE_DEFINED => 'isNull',
  43. ISearchComparison::COMPARE_IN => 'notIn',
  44. ];
  45. /** @var array<string, string> */
  46. protected static $fieldTypes = [
  47. 'mimetype' => 'string',
  48. 'mtime' => 'integer',
  49. 'name' => 'string',
  50. 'path' => 'string',
  51. 'size' => 'integer',
  52. 'tagname' => 'string',
  53. 'systemtag' => 'string',
  54. 'favorite' => 'boolean',
  55. 'fileid' => 'integer',
  56. 'storage' => 'integer',
  57. 'share_with' => 'string',
  58. 'share_type' => 'integer',
  59. 'owner' => 'string',
  60. ];
  61. /** @var array<string, int|string> */
  62. protected static $paramTypeMap = [
  63. 'string' => IQueryBuilder::PARAM_STR,
  64. 'integer' => IQueryBuilder::PARAM_INT,
  65. 'boolean' => IQueryBuilder::PARAM_BOOL,
  66. ];
  67. /** @var array<string, int> */
  68. protected static $paramArrayTypeMap = [
  69. 'string' => IQueryBuilder::PARAM_STR_ARRAY,
  70. 'integer' => IQueryBuilder::PARAM_INT_ARRAY,
  71. 'boolean' => IQueryBuilder::PARAM_INT_ARRAY,
  72. ];
  73. public const TAG_FAVORITE = '_$!<Favorite>!$_';
  74. /** @var IMimeTypeLoader */
  75. private $mimetypeLoader;
  76. public function __construct(
  77. IMimeTypeLoader $mimetypeLoader
  78. ) {
  79. $this->mimetypeLoader = $mimetypeLoader;
  80. }
  81. /**
  82. * @return string[]
  83. */
  84. public function extractRequestedFields(ISearchOperator $operator): array {
  85. if ($operator instanceof ISearchBinaryOperator) {
  86. return array_reduce($operator->getArguments(), function (array $fields, ISearchOperator $operator) {
  87. return array_unique(array_merge($fields, $this->extractRequestedFields($operator)));
  88. }, []);
  89. } elseif ($operator instanceof ISearchComparison && !$operator->getExtra()) {
  90. return [$operator->getField()];
  91. }
  92. return [];
  93. }
  94. /**
  95. * @param IQueryBuilder $builder
  96. * @param ISearchOperator[] $operators
  97. */
  98. public function searchOperatorArrayToDBExprArray(
  99. IQueryBuilder $builder,
  100. array $operators,
  101. ?IMetadataQuery $metadataQuery = null
  102. ) {
  103. return array_filter(array_map(function ($operator) use ($builder, $metadataQuery) {
  104. return $this->searchOperatorToDBExpr($builder, $operator, $metadataQuery);
  105. }, $operators));
  106. }
  107. public function searchOperatorToDBExpr(
  108. IQueryBuilder $builder,
  109. ISearchOperator $operator,
  110. ?IMetadataQuery $metadataQuery = null
  111. ) {
  112. $expr = $builder->expr();
  113. if ($operator instanceof ISearchBinaryOperator) {
  114. if (count($operator->getArguments()) === 0) {
  115. return null;
  116. }
  117. switch ($operator->getType()) {
  118. case ISearchBinaryOperator::OPERATOR_NOT:
  119. $negativeOperator = $operator->getArguments()[0];
  120. if ($negativeOperator instanceof ISearchComparison) {
  121. return $this->searchComparisonToDBExpr($builder, $negativeOperator, self::$searchOperatorNegativeMap, $metadataQuery);
  122. } else {
  123. throw new \InvalidArgumentException('Binary operators inside "not" is not supported');
  124. }
  125. // no break
  126. case ISearchBinaryOperator::OPERATOR_AND:
  127. return call_user_func_array([$expr, 'andX'], $this->searchOperatorArrayToDBExprArray($builder, $operator->getArguments(), $metadataQuery));
  128. case ISearchBinaryOperator::OPERATOR_OR:
  129. return call_user_func_array([$expr, 'orX'], $this->searchOperatorArrayToDBExprArray($builder, $operator->getArguments(), $metadataQuery));
  130. default:
  131. throw new \InvalidArgumentException('Invalid operator type: ' . $operator->getType());
  132. }
  133. } elseif ($operator instanceof ISearchComparison) {
  134. return $this->searchComparisonToDBExpr($builder, $operator, self::$searchOperatorMap, $metadataQuery);
  135. } else {
  136. throw new \InvalidArgumentException('Invalid operator type: ' . get_class($operator));
  137. }
  138. }
  139. private function searchComparisonToDBExpr(
  140. IQueryBuilder $builder,
  141. ISearchComparison $comparison,
  142. array $operatorMap,
  143. ?IMetadataQuery $metadataQuery = null
  144. ) {
  145. if ($comparison->getExtra()) {
  146. [$field, $value, $type, $paramType] = $this->getExtraOperatorField($comparison, $metadataQuery);
  147. } else {
  148. [$field, $value, $type, $paramType] = $this->getOperatorFieldAndValue($comparison);
  149. }
  150. if (isset($operatorMap[$type])) {
  151. $queryOperator = $operatorMap[$type];
  152. return $builder->expr()->$queryOperator($field, $this->getParameterForValue($builder, $value, $paramType));
  153. } else {
  154. throw new \InvalidArgumentException('Invalid operator type: ' . $comparison->getType());
  155. }
  156. }
  157. /**
  158. * @param ISearchComparison $operator
  159. * @return list{string, ParamValue, string, string}
  160. */
  161. private function getOperatorFieldAndValue(ISearchComparison $operator): array {
  162. $this->validateComparison($operator);
  163. $field = $operator->getField();
  164. $value = $operator->getValue();
  165. $type = $operator->getType();
  166. $pathEqHash = $operator->getQueryHint(ISearchComparison::HINT_PATH_EQ_HASH, true);
  167. return $this->getOperatorFieldAndValueInner($field, $value, $type, $pathEqHash);
  168. }
  169. /**
  170. * @param string $field
  171. * @param ParamValue $value
  172. * @param string $type
  173. * @return list{string, ParamValue, string, string}
  174. */
  175. private function getOperatorFieldAndValueInner(string $field, mixed $value, string $type, bool $pathEqHash): array {
  176. $paramType = self::$fieldTypes[$field];
  177. if ($type === ISearchComparison::COMPARE_IN) {
  178. $resultField = $field;
  179. $values = [];
  180. foreach ($value as $arrayValue) {
  181. /** @var ParamSingleValue $arrayValue */
  182. [$arrayField, $arrayValue] = $this->getOperatorFieldAndValueInner($field, $arrayValue, ISearchComparison::COMPARE_EQUAL, $pathEqHash);
  183. $resultField = $arrayField;
  184. $values[] = $arrayValue;
  185. }
  186. return [$resultField, $values, ISearchComparison::COMPARE_IN, $paramType];
  187. }
  188. if ($field === 'mimetype') {
  189. $value = (string)$value;
  190. if ($type === ISearchComparison::COMPARE_EQUAL) {
  191. $value = $this->mimetypeLoader->getId($value);
  192. } elseif ($type === ISearchComparison::COMPARE_LIKE) {
  193. // transform "mimetype='foo/%'" to "mimepart='foo'"
  194. if (preg_match('|(.+)/%|', $value, $matches)) {
  195. $field = 'mimepart';
  196. $value = $this->mimetypeLoader->getId($matches[1]);
  197. $type = ISearchComparison::COMPARE_EQUAL;
  198. } elseif (str_contains($value, '%')) {
  199. throw new \InvalidArgumentException('Unsupported query value for mimetype: ' . $value . ', only values in the format "mime/type" or "mime/%" are supported');
  200. } else {
  201. $field = 'mimetype';
  202. $value = $this->mimetypeLoader->getId($value);
  203. $type = ISearchComparison::COMPARE_EQUAL;
  204. }
  205. }
  206. } elseif ($field === 'favorite') {
  207. $field = 'tag.category';
  208. $value = self::TAG_FAVORITE;
  209. $paramType = 'string';
  210. } elseif ($field === 'name') {
  211. $field = 'file.name';
  212. } elseif ($field === 'tagname') {
  213. $field = 'tag.category';
  214. } elseif ($field === 'systemtag') {
  215. $field = 'systemtag.name';
  216. } elseif ($field === 'fileid') {
  217. $field = 'file.fileid';
  218. } elseif ($field === 'path' && $type === ISearchComparison::COMPARE_EQUAL && $pathEqHash) {
  219. $field = 'path_hash';
  220. $value = md5((string)$value);
  221. } elseif ($field === 'owner') {
  222. $field = 'uid_owner';
  223. }
  224. return [$field, $value, $type, $paramType];
  225. }
  226. private function validateComparison(ISearchComparison $operator) {
  227. $comparisons = [
  228. 'mimetype' => ['eq', 'like', 'in'],
  229. 'mtime' => ['eq', 'gt', 'lt', 'gte', 'lte'],
  230. 'name' => ['eq', 'like', 'clike', 'in'],
  231. 'path' => ['eq', 'like', 'clike', 'in'],
  232. 'size' => ['eq', 'gt', 'lt', 'gte', 'lte'],
  233. 'tagname' => ['eq', 'like'],
  234. 'systemtag' => ['eq', 'like'],
  235. 'favorite' => ['eq'],
  236. 'fileid' => ['eq', 'in'],
  237. 'storage' => ['eq', 'in'],
  238. 'share_with' => ['eq'],
  239. 'share_type' => ['eq'],
  240. 'owner' => ['eq'],
  241. ];
  242. if (!isset(self::$fieldTypes[$operator->getField()])) {
  243. throw new \InvalidArgumentException('Unsupported comparison field ' . $operator->getField());
  244. }
  245. $type = self::$fieldTypes[$operator->getField()];
  246. if ($operator->getType() === ISearchComparison::COMPARE_IN) {
  247. if (!is_array($operator->getValue())) {
  248. throw new \InvalidArgumentException('Invalid type for field ' . $operator->getField());
  249. }
  250. foreach ($operator->getValue() as $arrayValue) {
  251. if (gettype($arrayValue) !== $type) {
  252. throw new \InvalidArgumentException('Invalid type in array for field ' . $operator->getField());
  253. }
  254. }
  255. } else {
  256. if (gettype($operator->getValue()) !== $type) {
  257. throw new \InvalidArgumentException('Invalid type for field ' . $operator->getField());
  258. }
  259. }
  260. if (!in_array($operator->getType(), $comparisons[$operator->getField()])) {
  261. throw new \InvalidArgumentException('Unsupported comparison for field ' . $operator->getField() . ': ' . $operator->getType());
  262. }
  263. }
  264. private function getExtraOperatorField(ISearchComparison $operator, IMetadataQuery $metadataQuery): array {
  265. $paramType = self::$fieldTypes[$operator->getField()];
  266. $field = $operator->getField();
  267. $value = $operator->getValue();
  268. $type = $operator->getType();
  269. switch($operator->getExtra()) {
  270. case IMetadataQuery::EXTRA:
  271. $metadataQuery->joinIndex($field); // join index table if not joined yet
  272. $field = $metadataQuery->getMetadataValueField($field);
  273. break;
  274. default:
  275. throw new \InvalidArgumentException('Invalid extra type: ' . $operator->getExtra());
  276. }
  277. return [$field, $value, $type, $paramType];
  278. }
  279. private function getParameterForValue(IQueryBuilder $builder, $value, string $paramType) {
  280. if ($value instanceof \DateTime) {
  281. $value = $value->getTimestamp();
  282. }
  283. if (is_array($value)) {
  284. $type = self::$paramArrayTypeMap[$paramType];
  285. } else {
  286. $type = self::$paramTypeMap[$paramType];
  287. }
  288. return $builder->createNamedParameter($value, $type);
  289. }
  290. /**
  291. * @param IQueryBuilder $query
  292. * @param ISearchOrder[] $orders
  293. * @param IMetadataQuery|null $metadataQuery
  294. */
  295. public function addSearchOrdersToQuery(IQueryBuilder $query, array $orders, ?IMetadataQuery $metadataQuery = null): void {
  296. foreach ($orders as $order) {
  297. $field = $order->getField();
  298. switch ($order->getExtra()) {
  299. case IMetadataQuery::EXTRA:
  300. $metadataQuery->joinIndex($field); // join index table if not joined yet
  301. $field = $metadataQuery->getMetadataValueField($order->getField());
  302. break;
  303. default:
  304. if ($field === 'fileid') {
  305. $field = 'file.fileid';
  306. }
  307. // Mysql really likes to pick an index for sorting if it can't fully satisfy the where
  308. // filter with an index, since search queries pretty much never are fully filtered by index
  309. // mysql often picks an index for sorting instead of the much more useful index for filtering.
  310. //
  311. // By changing the order by to an expression, mysql isn't smart enough to see that it could still
  312. // use the index, so it instead picks an index for the filtering
  313. if ($field === 'mtime') {
  314. $field = $query->func()->add($field, $query->createNamedParameter(0));
  315. }
  316. }
  317. $query->addOrderBy($field, $order->getDirection());
  318. }
  319. }
  320. }