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