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; 26use PDOException; 27 28/** 29 * Upgrade the database schema from version 44 to version 45. 30 */ 31class Migration44 implements MigrationInterface 32{ 33 /** 34 * Upgrade to to the next version 35 * 36 * @return void 37 */ 38 public function upgrade(): void 39 { 40 // It is simpler to create a new table than to update the existing one. 41 42 if (!DB::schema()->hasTable('place_location')) { 43 DB::schema()->create('place_location', static function (Blueprint $table): void { 44 $table->integer('id', true); 45 $table->integer('parent_id')->nullable(); 46 $table->string('place', 120); 47 $table->double('latitude')->nullable(); 48 $table->double('longitude')->nullable(); 49 50 $table->unique(['parent_id', 'place']); 51 $table->unique(['place', 'parent_id']); 52 53 $table->index(['latitude']); 54 $table->index(['longitude']); 55 }); 56 57 DB::schema()->table('place_location', static function (Blueprint $table): void { 58 $table->foreign(['parent_id']) 59 ->references(['id']) 60 ->on('place_location') 61 ->onDelete('CASCADE') 62 ->onUpdate('CASCADE'); 63 }); 64 } 65 66 if (DB::schema()->hasTable('placelocation')) { 67 DB::table('placelocation') 68 ->where('pl_lati', '=', '') 69 ->orWhere('pl_long', '=', '') 70 ->update([ 71 'pl_lati' => null, 72 'pl_long' => null, 73 ]); 74 75 // Missing/invalid parents? Move them to the top level 76 DB::table('placelocation AS pl1') 77 ->leftJoin('placelocation AS pl2', 'pl1.pl_parent_id', '=', 'pl2.pl_id') 78 ->whereNull('pl2.pl_id') 79 ->update([ 80 'pl1.pl_parent_id' => 0, 81 ]); 82 83 // Remove invalid values. 84 DB::table('placelocation') 85 ->where('pl_lati', 'LIKE', '%,%') 86 ->orWhere('pl_lati', 'LIKE', '%-%') 87 ->orWhere('pl_long', 'LIKE', '%,%') 88 ->orWhere('pl_long', 'LIKE', '%-%') 89 ->orWhere(function (Builder $query): void { 90 $query 91 ->where('pl_lati', 'NOT LIKE', 'N%') 92 ->where('pl_lati', 'NOT LIKE', 'S%'); 93 }) 94 ->orWhere(function (Builder $query): void { 95 $query 96 ->where('pl_long', 'NOT LIKE', 'E%') 97 ->where('pl_long', 'NOT LIKE', 'W%'); 98 }) 99 ->update([ 100 'pl_lati' => '', 101 'pl_long' => '', 102 ]); 103 104 105 // The lack of unique key constraints means that there may be duplicates... 106 while (true) { 107 // Two places with the same name and parent... 108 $row = DB::table('placelocation') 109 ->select([ 110 new Expression('MIN(pl_id) AS min'), 111 new Expression('MAX(pl_id) AS max'), 112 ]) 113 ->groupBy(['pl_parent_id', 'pl_place']) 114 ->having(new Expression('COUNT(*)'), '>', '1') 115 ->first(); 116 117 if ($row === null) { 118 break; 119 } 120 121 // ...move children to the first 122 DB::table('placelocation') 123 ->where('pl_parent_id', '=', $row->max) 124 ->update(['pl_parent_id' => $row->min]); 125 126 // ...delete the second 127 DB::table('placelocation') 128 ->where('pl_id', '=', $row->max) 129 ->delete(); 130 } 131 132 // This is the SQL standard. It works with Postgres, Sqlite and MySQL 8 133 $select1 = DB::table('placelocation') 134 ->leftJoin('place_location', 'id', '=', 'pl_id') 135 ->whereNull('id') 136 ->orderBy('pl_level') 137 ->orderBy('pl_id') 138 ->select([ 139 'pl_id', 140 new Expression('CASE pl_parent_id WHEN 0 THEN NULL ELSE pl_parent_id END'), 141 'pl_place', 142 new Expression("CAST(REPLACE(REPLACE(pl_lati, 'S', '-'), 'N', '') AS FLOAT)"), 143 new Expression("CAST(REPLACE(REPLACE(pl_long, 'W', '-'), 'E', '') AS FLOAT)"), 144 ]); 145 146 // This works for MySQL 5.7 and lower, which cannot cast to FLOAT 147 $select2 = DB::table('placelocation') 148 ->leftJoin('place_location', 'id', '=', 'pl_id') 149 ->whereNull('id') 150 ->orderBy('pl_level') 151 ->orderBy('pl_id') 152 ->select([ 153 'pl_id', 154 new Expression('CASE pl_parent_id WHEN 0 THEN NULL ELSE pl_parent_id END'), 155 'pl_place', 156 new Expression("REPLACE(REPLACE(pl_lati, 'S', '-'), 'N', '')"), 157 new Expression("REPLACE(REPLACE(pl_long, 'W', '-'), 'E', '')"), 158 ]); 159 160 try { 161 DB::table('place_location') 162 ->insertUsing(['id', 'parent_id', 'place', 'latitude', 'longitude'], $select1); 163 } catch (PDOException $ex) { 164 DB::table('place_location') 165 ->insertUsing(['id', 'parent_id', 'place', 'latitude', 'longitude'], $select2); 166 } 167 168 DB::schema()->drop('placelocation'); 169 } 170 171 // Earlier versions of webtrees used 0 and NULL interchangeably. 172 // Assume 0 at the country-level and NULL at lower levels. 173 DB::table('place_location') 174 ->whereNotNull('parent_id') 175 ->where('latitude', '=', 0) 176 ->where('longitude', '=', 0) 177 ->update([ 178 'latitude' => null, 179 'longitude' => null, 180 ]); 181 } 182} 183