1<?php 2 3/** 4 * webtrees: online genealogy 5 * Copyright (C) 2021 webtrees development team 6 * This program is free software: you can redistribute it and/or modify 7 * it under the terms of the GNU General Public License as published by 8 * the Free Software Foundation, either version 3 of the License, or 9 * (at your option) any later version. 10 * This program is distributed in the hope that it will be useful, 11 * but WITHOUT ANY WARRANTY; without even the implied warranty of 12 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 13 * GNU General Public License for more details. 14 * You should have received a copy of the GNU General Public License 15 * along with this program. If not, see <https://www.gnu.org/licenses/>. 16 */ 17 18declare(strict_types=1); 19 20namespace Fisharebest\Webtrees\Http\RequestHandlers; 21 22use Fisharebest\Webtrees\Contracts\UserInterface; 23use Fisharebest\Webtrees\Family; 24use Fisharebest\Webtrees\FlashMessages; 25use Fisharebest\Webtrees\I18N; 26use Fisharebest\Webtrees\Individual; 27use Fisharebest\Webtrees\Media; 28use Fisharebest\Webtrees\Note; 29use Fisharebest\Webtrees\Registry; 30use Fisharebest\Webtrees\Repository; 31use Fisharebest\Webtrees\Services\AdminService; 32use Fisharebest\Webtrees\Services\TimeoutService; 33use Fisharebest\Webtrees\Source; 34use Fisharebest\Webtrees\Tree; 35use Illuminate\Database\Capsule\Manager as DB; 36use Illuminate\Database\Query\Expression; 37use Illuminate\Database\Query\JoinClause; 38use Psr\Http\Message\ResponseInterface; 39use Psr\Http\Message\ServerRequestInterface; 40use Psr\Http\Server\RequestHandlerInterface; 41 42use function assert; 43use function redirect; 44use function route; 45 46/** 47 * Renumber the XREFs in a family tree. 48 */ 49class RenumberTreeAction implements RequestHandlerInterface 50{ 51 private AdminService $admin_service; 52 53 private TimeoutService $timeout_service; 54 55 /** 56 * @param AdminService $admin_service 57 * @param TimeoutService $timeout_service 58 */ 59 public function __construct(AdminService $admin_service, TimeoutService $timeout_service) 60 { 61 $this->admin_service = $admin_service; 62 $this->timeout_service = $timeout_service; 63 } 64 65 /** 66 * @param ServerRequestInterface $request 67 * 68 * @return ResponseInterface 69 */ 70 public function handle(ServerRequestInterface $request): ResponseInterface 71 { 72 $tree = $request->getAttribute('tree'); 73 assert($tree instanceof Tree); 74 75 $xrefs = $this->admin_service->duplicateXrefs($tree); 76 77 foreach ($xrefs as $old_xref => $type) { 78 $new_xref = Registry::xrefFactory()->make($type); 79 switch ($type) { 80 case Individual::RECORD_TYPE: 81 DB::table('individuals') 82 ->where('i_file', '=', $tree->id()) 83 ->where('i_id', '=', $old_xref) 84 ->update([ 85 'i_id' => $new_xref, 86 'i_gedcom' => new Expression("REPLACE(i_gedcom, '0 @$old_xref@ INDI', '0 @$new_xref@ INDI')"), 87 ]); 88 89 DB::table('families') 90 ->where('f_husb', '=', $old_xref) 91 ->where('f_file', '=', $tree->id()) 92 ->update([ 93 'f_husb' => $new_xref, 94 'f_gedcom' => new Expression("REPLACE(f_gedcom, ' HUSB @$old_xref@', ' HUSB @$new_xref@')"), 95 ]); 96 97 DB::table('families') 98 ->where('f_wife', '=', $old_xref) 99 ->where('f_file', '=', $tree->id()) 100 ->update([ 101 'f_wife' => $new_xref, 102 'f_gedcom' => new Expression("REPLACE(f_gedcom, ' WIFE @$old_xref@', ' WIFE @$new_xref@')"), 103 ]); 104 105 // Other links from families to individuals 106 foreach (['CHIL', 'ASSO', '_ASSO'] as $tag) { 107 DB::table('families') 108 ->join('link', static function (JoinClause $join): void { 109 $join 110 ->on('l_file', '=', 'f_file') 111 ->on('l_from', '=', 'f_id'); 112 }) 113 ->where('l_to', '=', $old_xref) 114 ->where('l_type', '=', $tag) 115 ->where('f_file', '=', $tree->id()) 116 ->update([ 117 'f_gedcom' => new Expression("REPLACE(f_gedcom, ' $tag @$old_xref@', ' $tag @$new_xref@')"), 118 ]); 119 } 120 121 // Links from individuals to individuals 122 foreach (['ALIA', 'ASSO', '_ASSO'] as $tag) { 123 DB::table('individuals') 124 ->join('link', static function (JoinClause $join): void { 125 $join 126 ->on('l_file', '=', 'i_file') 127 ->on('l_from', '=', 'i_id'); 128 }) 129 ->where('link.l_to', '=', $old_xref) 130 ->where('link.l_type', '=', $tag) 131 ->where('i_file', '=', $tree->id()) 132 ->update([ 133 'i_gedcom' => new Expression("REPLACE(i_gedcom, ' $tag @$old_xref@', ' $tag @$new_xref@')"), 134 ]); 135 } 136 137 DB::table('placelinks') 138 ->where('pl_file', '=', $tree->id()) 139 ->where('pl_gid', '=', $old_xref) 140 ->update([ 141 'pl_gid' => $new_xref, 142 ]); 143 144 DB::table('dates') 145 ->where('d_file', '=', $tree->id()) 146 ->where('d_gid', '=', $old_xref) 147 ->update([ 148 'd_gid' => $new_xref, 149 ]); 150 151 DB::table('user_gedcom_setting') 152 ->where('gedcom_id', '=', $tree->id()) 153 ->where('setting_value', '=', $old_xref) 154 ->whereIn('setting_name', [UserInterface::PREF_TREE_ACCOUNT_XREF, UserInterface::PREF_TREE_DEFAULT_XREF]) 155 ->update([ 156 'setting_value' => $new_xref, 157 ]); 158 break; 159 160 case Family::RECORD_TYPE: 161 DB::table('families') 162 ->where('f_file', '=', $tree->id()) 163 ->where('f_id', '=', $old_xref) 164 ->update([ 165 'f_id' => $new_xref, 166 'f_gedcom' => new Expression("REPLACE(f_gedcom, '0 @$old_xref@ FAM', '0 @$new_xref@ FAM')"), 167 ]); 168 169 // Links from individuals to families 170 foreach (['FAMC', 'FAMS'] as $tag) { 171 DB::table('individuals') 172 ->join('link', static function (JoinClause $join): void { 173 $join 174 ->on('l_file', '=', 'i_file') 175 ->on('l_from', '=', 'i_id'); 176 }) 177 ->where('l_to', '=', $old_xref) 178 ->where('l_type', '=', $tag) 179 ->where('i_file', '=', $tree->id()) 180 ->update([ 181 'i_gedcom' => new Expression("REPLACE(i_gedcom, ' $tag @$old_xref@', ' $tag @$new_xref@')"), 182 ]); 183 } 184 185 DB::table('placelinks') 186 ->where('pl_file', '=', $tree->id()) 187 ->where('pl_gid', '=', $old_xref) 188 ->update([ 189 'pl_gid' => $new_xref, 190 ]); 191 192 DB::table('dates') 193 ->where('d_file', '=', $tree->id()) 194 ->where('d_gid', '=', $old_xref) 195 ->update([ 196 'd_gid' => $new_xref, 197 ]); 198 break; 199 200 case Source::RECORD_TYPE: 201 DB::table('sources') 202 ->where('s_file', '=', $tree->id()) 203 ->where('s_id', '=', $old_xref) 204 ->update([ 205 's_id' => $new_xref, 206 's_gedcom' => new Expression("REPLACE(s_gedcom, '0 @$old_xref@ SOUR', '0 @$new_xref@ SOUR')"), 207 ]); 208 209 DB::table('individuals') 210 ->join('link', static function (JoinClause $join): void { 211 $join 212 ->on('l_file', '=', 'i_file') 213 ->on('l_from', '=', 'i_id'); 214 }) 215 ->where('l_to', '=', $old_xref) 216 ->where('l_type', '=', 'SOUR') 217 ->where('i_file', '=', $tree->id()) 218 ->update([ 219 'i_gedcom' => new Expression("REPLACE(i_gedcom, ' SOUR @$old_xref@', ' SOUR @$new_xref@')"), 220 ]); 221 222 DB::table('families') 223 ->join('link', static function (JoinClause $join): void { 224 $join 225 ->on('l_file', '=', 'f_file') 226 ->on('l_from', '=', 'f_id'); 227 }) 228 ->where('l_to', '=', $old_xref) 229 ->where('l_type', '=', 'SOUR') 230 ->where('f_file', '=', $tree->id()) 231 ->update([ 232 'f_gedcom' => new Expression("REPLACE(f_gedcom, ' SOUR @$old_xref@', ' SOUR @$new_xref@')"), 233 ]); 234 235 DB::table('media') 236 ->join('link', static function (JoinClause $join): void { 237 $join 238 ->on('l_file', '=', 'm_file') 239 ->on('l_from', '=', 'm_id'); 240 }) 241 ->where('l_to', '=', $old_xref) 242 ->where('l_type', '=', 'SOUR') 243 ->where('m_file', '=', $tree->id()) 244 ->update([ 245 'm_gedcom' => new Expression("REPLACE(m_gedcom, ' SOUR @$old_xref@', ' SOUR @$new_xref@')"), 246 ]); 247 248 DB::table('other') 249 ->join('link', static function (JoinClause $join): void { 250 $join 251 ->on('l_file', '=', 'o_file') 252 ->on('l_from', '=', 'o_id'); 253 }) 254 ->where('l_to', '=', $old_xref) 255 ->where('l_type', '=', 'SOUR') 256 ->where('o_file', '=', $tree->id()) 257 ->update([ 258 'o_gedcom' => new Expression("REPLACE(o_gedcom, ' SOUR @$old_xref@', ' SOUR @$new_xref@')"), 259 ]); 260 break; 261 262 case Repository::RECORD_TYPE: 263 DB::table('other') 264 ->where('o_file', '=', $tree->id()) 265 ->where('o_id', '=', $old_xref) 266 ->where('o_type', '=', 'REPO') 267 ->update([ 268 'o_id' => $new_xref, 269 'o_gedcom' => new Expression("REPLACE(o_gedcom, '0 @$old_xref@ REPO', '0 @$new_xref@ REPO')"), 270 ]); 271 272 DB::table('sources') 273 ->join('link', static function (JoinClause $join): void { 274 $join 275 ->on('l_file', '=', 's_file') 276 ->on('l_from', '=', 's_id'); 277 }) 278 ->where('l_to', '=', $old_xref) 279 ->where('l_type', '=', 'REPO') 280 ->where('s_file', '=', $tree->id()) 281 ->update([ 282 's_gedcom' => new Expression("REPLACE(s_gedcom, ' REPO @$old_xref@', ' REPO @$new_xref@')"), 283 ]); 284 break; 285 286 case Note::RECORD_TYPE: 287 DB::table('other') 288 ->where('o_file', '=', $tree->id()) 289 ->where('o_id', '=', $old_xref) 290 ->where('o_type', '=', 'NOTE') 291 ->update([ 292 'o_id' => $new_xref, 293 'o_gedcom' => new Expression("REPLACE(o_gedcom, '0 @$old_xref@ NOTE', '0 @$new_xref@ NOTE')"), 294 ]); 295 296 DB::table('individuals') 297 ->join('link', static function (JoinClause $join): void { 298 $join 299 ->on('l_file', '=', 'i_file') 300 ->on('l_from', '=', 'i_id'); 301 }) 302 ->where('l_to', '=', $old_xref) 303 ->where('l_type', '=', 'NOTE') 304 ->where('i_file', '=', $tree->id()) 305 ->update([ 306 'i_gedcom' => new Expression("REPLACE(i_gedcom, ' NOTE @$old_xref@', ' NOTE @$new_xref@')"), 307 ]); 308 309 DB::table('families') 310 ->join('link', static function (JoinClause $join): void { 311 $join 312 ->on('l_file', '=', 'f_file') 313 ->on('l_from', '=', 'f_id'); 314 }) 315 ->where('l_to', '=', $old_xref) 316 ->where('l_type', '=', 'NOTE') 317 ->where('f_file', '=', $tree->id()) 318 ->update([ 319 'f_gedcom' => new Expression("REPLACE(f_gedcom, ' NOTE @$old_xref@', ' NOTE @$new_xref@')"), 320 ]); 321 322 DB::table('media') 323 ->join('link', static function (JoinClause $join): void { 324 $join 325 ->on('l_file', '=', 'm_file') 326 ->on('l_from', '=', 'm_id'); 327 }) 328 ->where('l_to', '=', $old_xref) 329 ->where('l_type', '=', 'NOTE') 330 ->where('m_file', '=', $tree->id()) 331 ->update([ 332 'm_gedcom' => new Expression("REPLACE(m_gedcom, ' NOTE @$old_xref@', ' NOTE @$new_xref@')"), 333 ]); 334 335 DB::table('sources') 336 ->join('link', static function (JoinClause $join): void { 337 $join 338 ->on('l_file', '=', 's_file') 339 ->on('l_from', '=', 's_id'); 340 }) 341 ->where('l_to', '=', $old_xref) 342 ->where('l_type', '=', 'NOTE') 343 ->where('s_file', '=', $tree->id()) 344 ->update([ 345 's_gedcom' => new Expression("REPLACE(s_gedcom, ' NOTE @$old_xref@', ' NOTE @$new_xref@')"), 346 ]); 347 348 DB::table('other') 349 ->join('link', static function (JoinClause $join): void { 350 $join 351 ->on('l_file', '=', 'o_file') 352 ->on('l_from', '=', 'o_id'); 353 }) 354 ->where('l_to', '=', $old_xref) 355 ->where('l_type', '=', 'NOTE') 356 ->where('o_file', '=', $tree->id()) 357 ->update([ 358 'o_gedcom' => new Expression("REPLACE(o_gedcom, ' NOTE @$old_xref@', ' NOTE @$new_xref@')"), 359 ]); 360 break; 361 362 case Media::RECORD_TYPE: 363 DB::table('media') 364 ->where('m_file', '=', $tree->id()) 365 ->where('m_id', '=', $old_xref) 366 ->update([ 367 'm_id' => $new_xref, 368 'm_gedcom' => new Expression("REPLACE(m_gedcom, '0 @$old_xref@ OBJE', '0 @$new_xref@ OBJE')"), 369 ]); 370 371 DB::table('media_file') 372 ->where('m_file', '=', $tree->id()) 373 ->where('m_id', '=', $old_xref) 374 ->update([ 375 'm_id' => $new_xref, 376 ]); 377 378 DB::table('individuals') 379 ->join('link', static function (JoinClause $join): void { 380 $join 381 ->on('l_file', '=', 'i_file') 382 ->on('l_from', '=', 'i_id'); 383 }) 384 ->where('l_to', '=', $old_xref) 385 ->where('l_type', '=', 'OBJE') 386 ->where('i_file', '=', $tree->id()) 387 ->update([ 388 'i_gedcom' => new Expression("REPLACE(i_gedcom, ' OBJE @$old_xref@', ' OBJE @$new_xref@')"), 389 ]); 390 391 DB::table('families') 392 ->join('link', static function (JoinClause $join): void { 393 $join 394 ->on('l_file', '=', 'f_file') 395 ->on('l_from', '=', 'f_id'); 396 }) 397 ->where('l_to', '=', $old_xref) 398 ->where('l_type', '=', 'OBJE') 399 ->where('f_file', '=', $tree->id()) 400 ->update([ 401 'f_gedcom' => new Expression("REPLACE(f_gedcom, ' OBJE @$old_xref@', ' OBJE @$new_xref@')"), 402 ]); 403 404 DB::table('sources') 405 ->join('link', static function (JoinClause $join): void { 406 $join 407 ->on('l_file', '=', 's_file') 408 ->on('l_from', '=', 's_id'); 409 }) 410 ->where('l_to', '=', $old_xref) 411 ->where('l_type', '=', 'OBJE') 412 ->where('s_file', '=', $tree->id()) 413 ->update([ 414 's_gedcom' => new Expression("REPLACE(s_gedcom, ' OBJE @$old_xref@', ' OBJE @$new_xref@')"), 415 ]); 416 417 DB::table('other') 418 ->join('link', static function (JoinClause $join): void { 419 $join 420 ->on('l_file', '=', 'o_file') 421 ->on('l_from', '=', 'o_id'); 422 }) 423 ->where('l_to', '=', $old_xref) 424 ->where('l_type', '=', 'OBJE') 425 ->where('o_file', '=', $tree->id()) 426 ->update([ 427 'o_gedcom' => new Expression("REPLACE(o_gedcom, ' OBJE @$old_xref@', ' OBJE @$new_xref@')"), 428 ]); 429 break; 430 431 default: 432 DB::table('other') 433 ->where('o_file', '=', $tree->id()) 434 ->where('o_id', '=', $old_xref) 435 ->where('o_type', '=', $type) 436 ->update([ 437 'o_id' => $new_xref, 438 'o_gedcom' => new Expression("REPLACE(o_gedcom, '0 @$old_xref@ $type', '0 @$new_xref@ $type')"), 439 ]); 440 441 DB::table('individuals') 442 ->join('link', static function (JoinClause $join): void { 443 $join 444 ->on('l_file', '=', 'i_file') 445 ->on('l_from', '=', 'i_id'); 446 }) 447 ->where('l_to', '=', $old_xref) 448 ->where('l_type', '=', $type) 449 ->where('i_file', '=', $tree->id()) 450 ->update([ 451 'i_gedcom' => new Expression("REPLACE(i_gedcom, ' $type @$old_xref@', ' $type @$new_xref@')"), 452 ]); 453 454 DB::table('families') 455 ->join('link', static function (JoinClause $join): void { 456 $join 457 ->on('l_file', '=', 'f_file') 458 ->on('l_from', '=', 'f_id'); 459 }) 460 ->where('l_to', '=', $old_xref) 461 ->where('l_type', '=', $type) 462 ->where('f_file', '=', $tree->id()) 463 ->update([ 464 'f_gedcom' => new Expression("REPLACE(f_gedcom, ' $type @$old_xref@', ' $type @$new_xref@')"), 465 ]); 466 467 DB::table('media') 468 ->join('link', static function (JoinClause $join): void { 469 $join 470 ->on('l_file', '=', 'm_file') 471 ->on('l_from', '=', 'm_id'); 472 }) 473 ->where('l_to', '=', $old_xref) 474 ->where('l_type', '=', $type) 475 ->where('m_file', '=', $tree->id()) 476 ->update([ 477 'm_gedcom' => new Expression("REPLACE(m_gedcom, ' $type @$old_xref@', ' $type @$new_xref@')"), 478 ]); 479 480 DB::table('sources') 481 ->join('link', static function (JoinClause $join): void { 482 $join 483 ->on('l_file', '=', 's_file') 484 ->on('l_from', '=', 's_id'); 485 }) 486 ->where('l_to', '=', $old_xref) 487 ->where('l_type', '=', $type) 488 ->where('s_file', '=', $tree->id()) 489 ->update([ 490 's_gedcom' => new Expression("REPLACE(s_gedcom, ' $type @$old_xref@', ' $type @$new_xref@')"), 491 ]); 492 493 DB::table('other') 494 ->join('link', static function (JoinClause $join): void { 495 $join 496 ->on('l_file', '=', 'o_file') 497 ->on('l_from', '=', 'o_id'); 498 }) 499 ->where('l_to', '=', $old_xref) 500 ->where('l_type', '=', $type) 501 ->where('o_file', '=', $tree->id()) 502 ->update([ 503 'o_gedcom' => new Expression("REPLACE(o_gedcom, ' $type @$old_xref@', ' $type @$new_xref@')"), 504 ]); 505 break; 506 } 507 508 DB::table('name') 509 ->where('n_file', '=', $tree->id()) 510 ->where('n_id', '=', $old_xref) 511 ->update([ 512 'n_id' => $new_xref, 513 ]); 514 515 DB::table('default_resn') 516 ->where('gedcom_id', '=', $tree->id()) 517 ->where('xref', '=', $old_xref) 518 ->update([ 519 'xref' => $new_xref, 520 ]); 521 522 DB::table('hit_counter') 523 ->where('gedcom_id', '=', $tree->id()) 524 ->where('page_parameter', '=', $old_xref) 525 ->update([ 526 'page_parameter' => $new_xref, 527 ]); 528 529 DB::table('link') 530 ->where('l_file', '=', $tree->id()) 531 ->where('l_from', '=', $old_xref) 532 ->update([ 533 'l_from' => $new_xref, 534 ]); 535 536 DB::table('link') 537 ->where('l_file', '=', $tree->id()) 538 ->where('l_to', '=', $old_xref) 539 ->update([ 540 'l_to' => $new_xref, 541 ]); 542 543 DB::table('favorite') 544 ->where('gedcom_id', '=', $tree->id()) 545 ->where('xref', '=', $old_xref) 546 ->update([ 547 'xref' => $new_xref, 548 ]); 549 550 unset($xrefs[$old_xref]); 551 552 // How much time do we have left? 553 if ($this->timeout_service->isTimeNearlyUp()) { 554 FlashMessages::addMessage(I18N::translate('The server’s time limit has been reached.'), 'warning'); 555 break; 556 } 557 } 558 559 $url = route(RenumberTreePage::class, ['tree' => $tree->name()]); 560 561 return redirect($url); 562 } 563} 564