app/Schema/Migration44.php
<?php
/**
* webtrees: online genealogy
* Copyright (C) 2023 webtrees development team
* This program is free software: you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published by
* the Free Software Foundation, either version 3 of the License, or
* (at your option) any later version.
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
* You should have received a copy of the GNU General Public License
* along with this program. If not, see <https://www.gnu.org/licenses/>.
*/
declare(strict_types=1);
namespace Fisharebest\Webtrees\Schema;
use Fisharebest\Webtrees\DB;
use Illuminate\Database\Query\Expression;
use Illuminate\Database\Schema\Blueprint;
use PDOException;
/**
* Upgrade the database schema from version 44 to version 45.
*/
class Migration44 implements MigrationInterface
{
public function upgrade(): void
{
// It is simpler to create a new table than to update the existing one.
if (!DB::schema()->hasTable('place_location')) {
DB::schema()->create('place_location', static function (Blueprint $table): void {
$table->integer('id', true);
$table->integer('parent_id')->nullable();
$table->string('place', 120);
$table->double('latitude')->nullable();
$table->double('longitude')->nullable();
$table->unique(['parent_id', 'place']);
$table->unique(['place', 'parent_id']);
$table->index(['latitude']);
$table->index(['longitude']);
});
// SQL-server cannot cascade-delete/update on self-relations.
// Users will need to delete all child locations before deleting the parent.
if (DB::driverName() === DB::SQL_SERVER) {
// SQL-Server doesn't support 'RESTRICT'
$action = 'NO ACTION';
} else {
$action = 'CASCADE';
}
DB::schema()->table('place_location', static function (Blueprint $table) use ($action): void {
$table->foreign(['parent_id'])
->references(['id'])
->on('place_location')
->onDelete($action)
->onUpdate($action);
});
}
// This table should only exist if we are upgrading an old installation, which would have been
// created with MySQL. Therefore we can safely use MySQL-specific SQL.
if (DB::schema()->hasTable('placelocation')) {
if (DB::driverName() === DB::MYSQL) {
DB::table('placelocation')
->where('pl_lati', '=', '')
->orWhere('pl_long', '=', '')
->update([
'pl_lati' => null,
'pl_long' => null,
]);
// Missing/invalid parents? Move them to the top level
DB::table('placelocation AS pl1')
->leftJoin('placelocation AS pl2', 'pl1.pl_parent_id', '=', 'pl2.pl_id')
->whereNull('pl2.pl_id')
->update([
'pl1.pl_parent_id' => 0,
]);
// Remove invalid values.
DB::table('placelocation')
->where('pl_lati', 'NOT REGEXP', '^[NS][0-9]+[.]?[0-9]*$')
->orWhere('pl_long', 'NOT REGEXP', '^[EW][0-9]+[.]?[0-9]*$')
->update([
'pl_lati' => null,
'pl_long' => null,
]);
// The existing data may have placenames that only differ after the first 120 chars.
// Need to remove the constraint before we truncate/merge them.
try {
DB::schema()->table('placelocation', static function (Blueprint $table): void {
$table->dropUnique(['pl_parent_id', 'pl_place']);
});
} catch (PDOException) {
// Already deleted, or does not exist;
}
DB::table('placelocation')
->update([
'pl_place' => new Expression('SUBSTRING(pl_place, 1, 120)'),
]);
// The lack of unique key constraints means that there may be duplicates...
while (true) {
// Two places with the same name and parent...
$row = DB::table('placelocation')
->select([
new Expression('MIN(pl_id) AS min'),
new Expression('MAX(pl_id) AS max'),
])
->groupBy(['pl_parent_id', 'pl_place'])
->having(new Expression('COUNT(*)'), '>', '1')
->first();
if ($row === null) {
break;
}
// ...move children to the first
DB::table('placelocation')
->where('pl_parent_id', '=', $row->max)
->update(['pl_parent_id' => $row->min]);
// ...delete the second
DB::table('placelocation')
->where('pl_id', '=', $row->max)
->delete();
}
// This is the SQL standard. It works with MySQL 8.0 and higher
$select1 = DB::table('placelocation')
->leftJoin('place_location', 'id', '=', 'pl_id')
->whereNull('id')
->orderBy('pl_level')
->orderBy('pl_id')
->select([
'pl_id',
new Expression('CASE pl_parent_id WHEN 0 THEN NULL ELSE pl_parent_id END'),
'pl_place',
new Expression("CAST(REPLACE(REPLACE(pl_lati, 'S', '-'), 'N', '') AS FLOAT)"),
new Expression("CAST(REPLACE(REPLACE(pl_long, 'W', '-'), 'E', '') AS FLOAT)"),
]);
// This works for MySQL 5.7 and lower, which cannot cast to FLOAT
$select2 = DB::table('placelocation')
->leftJoin('place_location', 'id', '=', 'pl_id')
->whereNull('id')
->orderBy('pl_level')
->orderBy('pl_id')
->select([
'pl_id',
new Expression('CASE pl_parent_id WHEN 0 THEN NULL ELSE pl_parent_id END'),
'pl_place',
new Expression("REPLACE(REPLACE(pl_lati, 'S', '-'), 'N', '')"),
new Expression("REPLACE(REPLACE(pl_long, 'W', '-'), 'E', '')"),
]);
try {
DB::table('place_location')
->insertUsing(['id', 'parent_id', 'place', 'latitude', 'longitude'], $select1);
} catch (PDOException) {
DB::table('place_location')
->insertUsing(['id', 'parent_id', 'place', 'latitude', 'longitude'], $select2);
}
}
DB::schema()->drop('placelocation');
}
// Earlier versions of webtrees used 0 and NULL interchangeably.
// Assume 0 at the country-level and NULL at lower levels.
DB::table('place_location')
->whereNotNull('parent_id')
->where('latitude', '=', 0)
->where('longitude', '=', 0)
->update([
'latitude' => null,
'longitude' => null,
]);
}
}