xref: /webtrees/app/Schema/Migration44.php (revision 8155639ada601d420a9fdb23f244fc0be2786250) 
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            // This is the SQL standard.  It works with Postgres, Sqlite and MySQL 8
84            $select1 = DB::table('placelocation')
85                ->leftJoin('place_location', 'id', '=', 'pl_id')
86                ->whereNull('id')
87                ->orderBy('pl_id')
88                ->select([
89                    'pl_id',
90                    new Expression('CASE pl_parent_id WHEN 0 THEN NULL ELSE pl_parent_id END'),
91                    'pl_place',
92                    new Expression("CAST(REPLACE(REPLACE(pl_lati, 'S', '-'), 'N', '') AS FLOAT)"),
93                    new Expression("CAST(REPLACE(REPLACE(pl_long, 'W', '-'), 'E', '') AS FLOAT)"),
94                ]);
95
96            // This works for MySQL 5.7 and lower, which cannot cast to FLOAT
97            $select2 = DB::table('placelocation')
98                ->leftJoin('place_location', 'id', '=', 'pl_id')
99                ->whereNull('id')
100                ->orderBy('pl_id')
101                ->select([
102                    'pl_id',
103                    new Expression('CASE pl_parent_id WHEN 0 THEN NULL ELSE pl_parent_id END'),
104                    'pl_place',
105                    new Expression("REPLACE(REPLACE(pl_lati, 'S', '-'), 'N', '')"),
106                    new Expression("REPLACE(REPLACE(pl_long, 'W', '-'), 'E', '')"),
107                ]);
108
109            try {
110                DB::table('place_location')
111                    ->insertUsing(['id', 'parent_id', 'place', 'latitude', 'longitude'], $select1);
112            } catch (PDOException $ex) {
113                DB::table('place_location')
114                    ->insertUsing(['id', 'parent_id', 'place', 'latitude', 'longitude'], $select2);
115            }
116
117            DB::schema()->drop('placelocation');
118        }
119
120        // Earlier versions of webtrees used 0 and NULL interchangeably.
121        // Assume 0 at the country-level and NULL at lower levels.
122        DB::table('place_location')
123            ->whereNotNull('parent_id')
124            ->where('latitude', '=', 0)
125            ->where('longitude', '=', 0)
126            ->update([
127                'latitude'  => null,
128                'longitude' => null,
129            ]);
130    }
131}
132