Fisharebest\Webtrees\Schema\Migration44::upgrade()

Complexity

Conditions	4
Paths	6

Size

Total Lines	91
Code Lines	63

Duplication

Lines	0
Ratio	0 %

Importance

Changes	4
Bugs	0	Features	0

<?php
    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,
            ]);