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
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
|
<?php
/**
* webtrees: online genealogy
* Copyright (C) 2026 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']);
if (DB::driverName() != DB::FIREBIRD) {
// since unique index exists for fields firebird will not add duplicate
$table->unique(['place', 'parent_id']);
}
$table->index(['latitude']);
$table->index(['longitude']);
});
// SqlServer 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) {
DB::schema()->table('place_location', static function (Blueprint $table): void {
$table->foreign(['parent_id'])
->references(['id'])
->on('place_location')
->cascadeOnDelete()
->cascadeOnUpdate();
});
}
}
// 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,
]);
}
}
|
