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\Schema; 21 22use Illuminate\Database\Capsule\Manager as DB; 23use Illuminate\Database\Query\Builder; 24use Illuminate\Database\Query\Expression; 25use Illuminate\Database\Schema\Blueprint; 26 27/** 28 * Upgrade the database schema from version 44 to version 45. 29 */ 30class Migration44 implements MigrationInterface 31{ 32 /** 33 * Upgrade to to the next version 34 * 35 * @return void 36 */ 37 public function upgrade(): void 38 { 39 // It is simpler to create a new table than to update the existing one. 40 41 if (!DB::schema()->hasTable('place_location')) { 42 DB::schema()->create('place_location', static function (Blueprint $table): void { 43 $table->integer('id', true); 44 $table->integer('parent_id')->nullable(); 45 $table->string('place', 120); 46 $table->double('latitude')->nullable(); 47 $table->double('longitude')->nullable(); 48 49 $table->unique(['parent_id', 'place']); 50 $table->unique(['place', 'parent_id']); 51 52 $table->index(['latitude']); 53 $table->index(['longitude']); 54 }); 55 56 DB::schema()->table('place_location', static function (Blueprint $table): void { 57 $table->foreign(['parent_id']) 58 ->references(['id']) 59 ->on('place_location') 60 ->onDelete('CASCADE') 61 ->onUpdate('CASCADE'); 62 }); 63 } 64 65 if (DB::schema()->hasTable('placelocation')) { 66 DB::table('placelocation') 67 ->where('pl_lati', '=', '') 68 ->orWhere('pl_long', '=', '') 69 ->update([ 70 'pl_lati' => null, 71 'pl_long' => null, 72 ]); 73 74 // Missing/invalid parents? Move them to the top level 75 DB::table('placelocation AS pl1') 76 ->leftJoin('placelocation AS pl2', 'pl1.pl_parent_id', '=', 'pl2.pl_id') 77 ->whereNull('pl2.pl_id') 78 ->update([ 79 'pl1.pl_parent_id' => 0, 80 ]); 81 82 $select = DB::table('placelocation') 83 ->leftJoin('place_location', 'id', '=', 'pl_id') 84 ->whereNull('id') 85 ->orderBy('pl_id') 86 ->select([ 87 'pl_id', 88 new Expression('CASE pl_parent_id WHEN 0 THEN NULL ELSE pl_parent_id END'), 89 'pl_place', 90 new Expression("CAST(REPLACE(REPLACE(pl_lati, 'S', '-'), 'N', '') AS FLOAT)"), 91 new Expression("CAST(REPLACE(REPLACE(pl_long, 'W', '-'), 'E', '') AS FLOAT)"), 92 ]); 93 94 DB::table('place_location') 95 ->insertUsing(['id', 'parent_id', 'place', 'latitude', 'longitude'], $select); 96 97 DB::schema()->drop('placelocation'); 98 } 99 100 // Earlier versions of webtrees used 0 and NULL interchangeably. 101 // Assume 0 at the country-level and NULL at lower levels. 102 DB::table('place_location') 103 ->whereNotNull('parent_id') 104 ->where('latitude', '=', 0) 105 ->where('longitude', '=', 0) 106 ->update([ 107 'latitude' => null, 108 'longitude' => null, 109 ]); 110 } 111} 112