1<?php 2 3/** 4 * webtrees: online genealogy 5 * Copyright (C) 2022 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\Expression; 24use Illuminate\Database\Schema\Blueprint; 25use PDOException; 26 27/** 28 * Upgrade the database schema from version 44 to version 45. 29 */ 30class Migration44 implements MigrationInterface 31{ 32 /** 33 * Upgrade 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 // SQL-server cannot cascade-delete/update on self-relations. 57 // Users will need to delete all child locations before deleting the parent. 58 if (DB::connection()->getDriverName() === 'sqlsrv') { 59 // SQL-Server doesn't support 'RESTRICT' 60 $action = 'NO ACTION'; 61 } else { 62 $action = 'CASCADE'; 63 } 64 65 DB::schema()->table('place_location', static function (Blueprint $table) use ($action): void { 66 $table->foreign(['parent_id']) 67 ->references(['id']) 68 ->on('place_location') 69 ->onDelete($action) 70 ->onUpdate($action); 71 }); 72 } 73 74 if (DB::schema()->hasTable('placelocation')) { 75 DB::table('placelocation') 76 ->where('pl_lati', '=', '') 77 ->orWhere('pl_long', '=', '') 78 ->update([ 79 'pl_lati' => null, 80 'pl_long' => null, 81 ]); 82 83 // Missing/invalid parents? Move them to the top level 84 DB::table('placelocation AS pl1') 85 ->leftJoin('placelocation AS pl2', 'pl1.pl_parent_id', '=', 'pl2.pl_id') 86 ->whereNull('pl2.pl_id') 87 ->update([ 88 'pl1.pl_parent_id' => 0, 89 ]); 90 91 // Remove invalid values. 92 if (DB::connection()->getDriverName() === 'mysql') { 93 DB::table('placelocation') 94 ->where('pl_lati', 'NOT REGEXP', '^[NS][0-9]+[.]?[0-9]*$') 95 ->orWhere('pl_long', 'NOT REGEXP', '^[EW][0-9]+[.]?[0-9]*$') 96 ->update([ 97 'pl_lati' => null, 98 'pl_long' => null, 99 ]); 100 } 101 102 // The existing data may have placenames that only differ after the first 120 chars. 103 // Need to remove the constraint before we truncate/merge them. 104 try { 105 DB::schema()->table('placelocation', static function (Blueprint $table): void { 106 $table->dropUnique(['pl_parent_id', 'pl_place']); 107 }); 108 } catch (PDOException) { 109 // Already deleted, or does not exist; 110 } 111 112 $substring_function = DB::connection()->getDriverName() === 'sqlite' ? 'SUBSTR' : 'SUBSTRING'; 113 114 DB::table('placelocation') 115 ->update([ 116 'pl_place' => new Expression($substring_function . '(pl_place, 1, 120)'), 117 ]); 118 119 // The lack of unique key constraints means that there may be duplicates... 120 while (true) { 121 // Two places with the same name and parent... 122 $row = DB::table('placelocation') 123 ->select([ 124 new Expression('MIN(pl_id) AS min'), 125 new Expression('MAX(pl_id) AS max'), 126 ]) 127 ->groupBy(['pl_parent_id', 'pl_place']) 128 ->having(new Expression('COUNT(*)'), '>', '1') 129 ->first(); 130 131 if ($row === null) { 132 break; 133 } 134 135 // ...move children to the first 136 DB::table('placelocation') 137 ->where('pl_parent_id', '=', $row->max) 138 ->update(['pl_parent_id' => $row->min]); 139 140 // ...delete the second 141 DB::table('placelocation') 142 ->where('pl_id', '=', $row->max) 143 ->delete(); 144 } 145 146 // This is the SQL standard. It works with Postgres, Sqlite and MySQL 8 147 $select1 = 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("CAST(REPLACE(REPLACE(pl_lati, 'S', '-'), 'N', '') AS FLOAT)"), 157 new Expression("CAST(REPLACE(REPLACE(pl_long, 'W', '-'), 'E', '') AS FLOAT)"), 158 ]); 159 160 // This works for MySQL 5.7 and lower, which cannot cast to FLOAT 161 $select2 = DB::table('placelocation') 162 ->leftJoin('place_location', 'id', '=', 'pl_id') 163 ->whereNull('id') 164 ->orderBy('pl_level') 165 ->orderBy('pl_id') 166 ->select([ 167 'pl_id', 168 new Expression('CASE pl_parent_id WHEN 0 THEN NULL ELSE pl_parent_id END'), 169 'pl_place', 170 new Expression("REPLACE(REPLACE(pl_lati, 'S', '-'), 'N', '')"), 171 new Expression("REPLACE(REPLACE(pl_long, 'W', '-'), 'E', '')"), 172 ]); 173 174 // SQL-server needs to be told to insert values into auto-generated columns. 175 if (DB::connection()->getDriverName() === 'sqlsrv') { 176 $prefix = DB::connection()->getTablePrefix(); 177 $statement = 'SET IDENTITY_INSERT [' . $prefix . 'place_location] ON'; 178 DB::connection()->statement($statement); 179 } 180 181 try { 182 DB::table('place_location') 183 ->insertUsing(['id', 'parent_id', 'place', 'latitude', 'longitude'], $select1); 184 } catch (PDOException) { 185 DB::table('place_location') 186 ->insertUsing(['id', 'parent_id', 'place', 'latitude', 'longitude'], $select2); 187 } 188 189 DB::schema()->drop('placelocation'); 190 } 191 192 // Earlier versions of webtrees used 0 and NULL interchangeably. 193 // Assume 0 at the country-level and NULL at lower levels. 194 DB::table('place_location') 195 ->whereNotNull('parent_id') 196 ->where('latitude', '=', 0) 197 ->where('longitude', '=', 0) 198 ->update([ 199 'latitude' => null, 200 'longitude' => null, 201 ]); 202 } 203} 204