1<?php 2/** 3 * webtrees: online genealogy 4 * Copyright (C) 2019 webtrees development team 5 * This program is free software: you can redistribute it and/or modify 6 * it under the terms of the GNU General Public License as published by 7 * the Free Software Foundation, either version 3 of the License, or 8 * (at your option) any later version. 9 * This program is distributed in the hope that it will be useful, 10 * but WITHOUT ANY WARRANTY; without even the implied warranty of 11 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 12 * GNU General Public License for more details. 13 * You should have received a copy of the GNU General Public License 14 * along with this program. If not, see <http://www.gnu.org/licenses/>. 15 */ 16declare(strict_types=1); 17 18namespace Fisharebest\Webtrees\Services; 19 20use Closure; 21use Fisharebest\Localization\Locale\LocaleInterface; 22use Fisharebest\Webtrees\Family; 23use Fisharebest\Webtrees\Gedcom; 24use Fisharebest\Webtrees\GedcomRecord; 25use Fisharebest\Webtrees\Individual; 26use Fisharebest\Webtrees\Media; 27use Fisharebest\Webtrees\Note; 28use Fisharebest\Webtrees\Place; 29use Fisharebest\Webtrees\Repository; 30use Fisharebest\Webtrees\Source; 31use Fisharebest\Webtrees\Tree; 32use Illuminate\Database\Capsule\Manager as DB; 33use Illuminate\Database\Query\Builder; 34use Illuminate\Database\Query\Expression; 35use Illuminate\Database\Query\JoinClause; 36use Illuminate\Support\Collection; 37use stdClass; 38use function mb_stripos; 39 40/** 41 * Search trees for genealogy records. 42 */ 43class SearchService 44{ 45 /** @var LocaleInterface */ 46 private $locale; 47 48 /** 49 * SearchService constructor. 50 * 51 * @param LocaleInterface $locale 52 */ 53 public function __construct(LocaleInterface $locale) 54 { 55 $this->locale = $locale; 56 } 57 58 /** 59 * @param Tree[] $trees 60 * @param string[] $search 61 * 62 * @return Collection|Family[] 63 */ 64 public function searchFamilies(array $trees, array $search): Collection 65 { 66 $query = DB::table('families'); 67 68 $this->whereTrees($query, 'f_file', $trees); 69 $this->whereSearch($query, 'f_gedcom', $search); 70 71 return $query 72 ->get() 73 ->map(Family::rowMapper()) 74 ->filter(GedcomRecord::accessFilter()) 75 ->filter(self::rawGedcomFilter($search)); 76 } 77 78 /** 79 * Search for families by name. 80 * 81 * @param Tree[] $trees 82 * @param string[] $search 83 * @param int $offset 84 * @param int $limit 85 * 86 * @return Collection|Family[] 87 */ 88 public function searchFamilyNames(array $trees, array $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection 89 { 90 $query = DB::table('families') 91 ->join('name AS husb_name', function (JoinClause $join): void { 92 $join 93 ->on('husb_name.n_file', '=', 'families.f_file') 94 ->on('husb_name.n_id', '=', 'families.f_husb'); 95 }) 96 ->join('name AS wife_name', function (JoinClause $join): void { 97 $join 98 ->on('wife_name.n_file', '=', 'families.f_file') 99 ->on('wife_name.n_id', '=', 'families.f_wife'); 100 }) 101 ->where('wife_name.n_type', '<>', '_MARNM') 102 ->where('husb_name.n_type', '<>', '_MARNM'); 103 104 $prefix = DB::connection()->getTablePrefix(); 105 $field = DB::raw($prefix . 'husb_name.n_full || ' . $prefix . 'wife_name.n_full'); 106 107 $this->whereTrees($query, 'f_file', $trees); 108 $this->whereSearch($query, $field, $search); 109 110 $query 111 ->orderBy('husb_name.n_sort') 112 ->orderBy('wife_name.n_sort') 113 ->select(['families.*', 'husb_name.n_sort', 'wife_name.n_sort']) 114 ->distinct(); 115 116 return $this->paginateQuery($query, Family::rowMapper(), GedcomRecord::accessFilter(), $offset, $limit); 117 } 118 119 /** 120 * @param Tree[] $trees 121 * @param string[] $search 122 * 123 * @return Collection|Individual[] 124 */ 125 public function searchIndividuals(array $trees, array $search): Collection 126 { 127 $query = DB::table('individuals'); 128 129 $this->whereTrees($query, 'i_file', $trees); 130 $this->whereSearch($query, 'i_gedcom', $search); 131 132 return $query 133 ->get() 134 ->map(Individual::rowMapper()) 135 ->filter(GedcomRecord::accessFilter()) 136 ->filter(self::rawGedcomFilter($search)); 137 } 138 139 /** 140 * Search for individuals by name. 141 * 142 * @param Tree[] $trees 143 * @param string[] $search 144 * @param int $offset 145 * @param int $limit 146 * 147 * @return Collection|Individual[] 148 */ 149 public function searchIndividualNames(array $trees, array $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection 150 { 151 $query = DB::table('individuals') 152 ->join('name', function (JoinClause $join): void { 153 $join 154 ->on('name.n_file', '=', 'individuals.i_file') 155 ->on('name.n_id', '=', 'individuals.i_id'); 156 }) 157 ->orderBy('n_sort') 158 ->select(['individuals.*', 'n_sort', 'n_num']) 159 ->distinct(); 160 161 $this->whereTrees($query, 'i_file', $trees); 162 $this->whereSearch($query, 'n_full', $search); 163 164 return $this->paginateQuery($query, Individual::rowMapper(), GedcomRecord::accessFilter(), $offset, $limit); 165 } 166 167 /** 168 * Search for media objects. 169 * 170 * @param Tree[] $trees 171 * @param string[] $search 172 * @param int $offset 173 * @param int $limit 174 * 175 * @return Collection|Media[] 176 */ 177 public function searchMedia(array $trees, array $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection 178 { 179 $query = DB::table('media'); 180 181 $this->whereTrees($query, 'media.m_file', $trees); 182 $this->whereSearch($query, 'm_gedcom', $search); 183 184 return $this->paginateQuery($query, Media::rowMapper(), GedcomRecord::accessFilter(), $offset, $limit); 185 } 186 187 /** 188 * Search for notes. 189 * 190 * @param Tree[] $trees 191 * @param string[] $search 192 * @param int $offset 193 * @param int $limit 194 * 195 * @return Collection|Note[] 196 */ 197 public function searchNotes(array $trees, array $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection 198 { 199 $query = DB::table('other') 200 ->where('o_type', '=', 'NOTE'); 201 202 $this->whereTrees($query, 'o_file', $trees); 203 $this->whereSearch($query, 'o_gedcom', $search); 204 205 return $this->paginateQuery($query, Note::rowMapper(), GedcomRecord::accessFilter(), $offset, $limit); 206 } 207 208 /** 209 * Search for repositories. 210 * 211 * @param Tree[] $trees 212 * @param string[] $search 213 * @param int $offset 214 * @param int $limit 215 * 216 * @return Collection|Repository[] 217 */ 218 public function searchRepositories(array $trees, array $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection 219 { 220 $query = DB::table('other') 221 ->where('o_type', '=', 'REPO'); 222 223 $this->whereTrees($query, 'o_file', $trees); 224 $this->whereSearch($query, 'o_gedcom', $search); 225 226 return $this->paginateQuery($query, Repository::rowMapper(), GedcomRecord::accessFilter(), $offset, $limit); 227 } 228 229 /** 230 * Search for sources. 231 * 232 * @param Tree[] $trees 233 * @param string[] $search 234 * @param int $offset 235 * @param int $limit 236 * 237 * @return Collection|Source[] 238 */ 239 public function searchSources(array $trees, array $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection 240 { 241 $query = DB::table('sources'); 242 243 $this->whereTrees($query, 's_file', $trees); 244 $this->whereSearch($query, 's_gedcom', $search); 245 246 return $this->paginateQuery($query, Source::rowMapper(), GedcomRecord::accessFilter(), $offset, $limit); 247 } 248 249 /** 250 * Search for sources by name. 251 * 252 * @param Tree[] $trees 253 * @param string[] $search 254 * @param int $offset 255 * @param int $limit 256 * 257 * @return Collection|Source[] 258 */ 259 public function searchSourcesByName(array $trees, array $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection 260 { 261 $query = DB::table('sources') 262 ->orderBy('s_name'); 263 264 $this->whereTrees($query, 's_file', $trees); 265 $this->whereSearch($query, 's_name', $search); 266 267 return $this->paginateQuery($query, Source::rowMapper(), GedcomRecord::accessFilter(), $offset, $limit); 268 } 269 270 /** 271 * Search for submitters. 272 * 273 * @param Tree[] $trees 274 * @param string[] $search 275 * @param int $offset 276 * @param int $limit 277 * 278 * @return Collection|GedcomRecord[] 279 */ 280 public function searchSubmitters(array $trees, array $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection 281 { 282 $query = DB::table('other') 283 ->where('o_type', '=', 'SUBM'); 284 285 $this->whereTrees($query, 'o_file', $trees); 286 $this->whereSearch($query, 'o_gedcom', $search); 287 288 return $this->paginateQuery($query, GedcomRecord::rowMapper(), GedcomRecord::accessFilter(), $offset, $limit); 289 } 290 291 /** 292 * Search for places. 293 * 294 * @param Tree $tree 295 * @param string $search 296 * @param int $offset 297 * @param int $limit 298 * 299 * @return Collection|Place[] 300 */ 301 public function searchPlaces(Tree $tree, string $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection 302 { 303 $query = DB::table('places AS p0') 304 ->where('p0.p_file', '=', $tree->id()) 305 ->leftJoin('places AS p1', 'p1.p_id', '=', 'p0.p_parent_id') 306 ->leftJoin('places AS p2', 'p2.p_id', '=', 'p1.p_parent_id') 307 ->leftJoin('places AS p3', 'p3.p_id', '=', 'p2.p_parent_id') 308 ->leftJoin('places AS p4', 'p4.p_id', '=', 'p3.p_parent_id') 309 ->leftJoin('places AS p5', 'p5.p_id', '=', 'p4.p_parent_id') 310 ->leftJoin('places AS p6', 'p6.p_id', '=', 'p5.p_parent_id') 311 ->leftJoin('places AS p7', 'p7.p_id', '=', 'p6.p_parent_id') 312 ->leftJoin('places AS p8', 'p8.p_id', '=', 'p7.p_parent_id') 313 ->orderBy('p0.p_place') 314 ->orderBy('p1.p_place') 315 ->orderBy('p2.p_place') 316 ->orderBy('p3.p_place') 317 ->orderBy('p4.p_place') 318 ->orderBy('p5.p_place') 319 ->orderBy('p6.p_place') 320 ->orderBy('p7.p_place') 321 ->orderBy('p8.p_place') 322 ->select([ 323 'p0.p_place AS place0', 324 'p1.p_place AS place1', 325 'p2.p_place AS place2', 326 'p3.p_place AS place3', 327 'p4.p_place AS place4', 328 'p5.p_place AS place5', 329 'p6.p_place AS place6', 330 'p7.p_place AS place7', 331 'p8.p_place AS place8', 332 ]); 333 334 // Filter each level of the hierarchy. 335 foreach (explode(',', $search, 9) as $level => $string) { 336 $query->whereContains('p' . $level . '.p_place', $string); 337 } 338 339 $row_mapper = function (stdClass $row) use ($tree): Place { 340 $place = implode(', ', array_filter((array) $row)); 341 342 return new Place($place, $tree); 343 }; 344 345 $filter = function (): bool { 346 return true; 347 }; 348 349 return $this->paginateQuery($query, $row_mapper, $filter, $offset, $limit); 350 } 351 352 /** 353 * Paginate a search query. 354 * 355 * @param Builder $query Searches the database for the desired records. 356 * @param Closure $row_mapper Converts a row from the query into a record. 357 * @param Closure $row_filter 358 * @param int $offset Skip this many rows. 359 * @param int $limit Take this many rows. 360 * 361 * @return Collection 362 */ 363 private function paginateQuery(Builder $query, Closure $row_mapper, Closure $row_filter, int $offset, int $limit): Collection 364 { 365 $collection = new Collection(); 366 367 foreach ($query->cursor() as $row) { 368 $record = $row_mapper($row); 369 // If the object has a method "canShow()", then use it to filter for privacy. 370 if ($row_filter($record)) { 371 if ($offset > 0) { 372 $offset--; 373 } else { 374 if ($limit > 0) { 375 $collection->push($record); 376 } 377 378 $limit--; 379 380 if ($limit === 0) { 381 break; 382 } 383 } 384 } 385 } 386 387 return $collection; 388 } 389 390 /** 391 * Apply search filters to a SQL query column. Apply collation rules to MySQL. 392 * 393 * @param Builder $query 394 * @param Expression|string $field 395 * @param string[] $search_terms 396 */ 397 private function whereSearch(Builder $query, $field, array $search_terms): void 398 { 399 if ($field instanceof Expression) { 400 $field = $field->getValue(); 401 } 402 403 $field = DB::raw($field . ' /*! COLLATE ' . 'utf8_' . $this->locale->collation() . ' */'); 404 405 foreach ($search_terms as $search_term) { 406 $query->whereContains($field, $search_term); 407 } 408 } 409 410 /** 411 * @param Builder $query 412 * @param string $tree_id_field 413 * @param Tree[] $trees 414 */ 415 private function whereTrees(Builder $query, string $tree_id_field, array $trees): void 416 { 417 $tree_ids = array_map(function (Tree $tree) { 418 return $tree->id(); 419 }, $trees); 420 421 $query->whereIn($tree_id_field, $tree_ids); 422 } 423 424 /** 425 * A closure to filter records by privacy-filtered GEDCOM data. 426 * 427 * @param array $search_terms 428 * 429 * @return Closure 430 */ 431 private function rawGedcomFilter(array $search_terms): Closure 432 { 433 return function (GedcomRecord $record) use ($search_terms): bool { 434 // Ignore non-genealogy fields 435 $gedcom = preg_replace('/\n\d (?:_UID) .*/', '', $record->gedcom()); 436 437 // Ignore matches in links 438 $gedcom = preg_replace('/\n\d ' . Gedcom::REGEX_TAG . '( @' . Gedcom::REGEX_XREF . '@)?/', '', $gedcom); 439 440 // Re-apply the filtering 441 foreach ($search_terms as $search_term) { 442 if (mb_stripos($gedcom, $search_term) === false) { 443 return false; 444 } 445 } 446 447 return true; 448 }; 449 } 450} 451