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