1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
|
<?php
/**
* webtrees: online genealogy
* Copyright (C) 2021 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 Illuminate\Database\Capsule\Manager as DB;
use Illuminate\Database\Query\Builder;
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
{
/**
* Upgrade to to the next version
*
* @return void
*/
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']);
});
DB::schema()->table('place_location', static function (Blueprint $table): void {
$table->foreign(['parent_id'])
->references(['id'])
->on('place_location')
->onDelete('CASCADE')
->onUpdate('CASCADE');
});
}
if (DB::schema()->hasTable('placelocation')) {
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,
]);
// This is the SQL standard. It works with Postgres, Sqlite and MySQL 8
$select1 = DB::table('placelocation')
->leftJoin('place_location', 'id', '=', 'pl_id')
->whereNull('id')
->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_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 $ex) {
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,
]);
}
}
|
