5611

Committed 23 Apr 2026 09:53AM UTC coverage: 71.534% (+3.7%) from 67.791%

Build # 5611

Build Type

Pull #234

circleci

Committed by

edwh

Commit Message

fix(tests): fix PR #234 CI failures — Playwright wrong groupId + Coveralls vitest coverage

Root causes and fixes:

1. Playwright test-modtools-edits-flow (timeout):
   postMessage() picks a group from the postcode area, not necessarily
   testEnv.group.id. Using testEnv.group.id in the Approve API call
   silently updated 0 rows (wrong groupid), leaving collection='Pending',
   which prevented edit review creation and caused the 202.5s timeout.
   Fix: fetch the message first to get actualGroupId, use that in both
   the Approve and PATCH /memberships calls.

2. Playwright test-repost-group-change (wrong group ID 69615 vs 55):
   Used .first() to find rejected message cards, picking stale messages
   from previous CI runs that belonged to group 69615. Fix: target the
   specific rejected message by data-message-id attribute so only the
   message created by this test run is found.

3. Coveralls vitest coverage decrease:
   auth.js disableGoogleAutoselect had 5 uncovered lines (window guard,
   happy-path disableAutoSelect, catch block) and a bare `window`
   reference that throws ReferenceError in SSR / post-teardown contexts.
   Fix: rewrite guard as typeof window === 'undefined' check and add
   3-test describe block covering undefined-window, happy-path, and
   error-catch branches.

Co-authored-by: Claude Sonnet 4.6 <noreply@anthropic.com>

Pull Request Pull Request #234: test(coverage): message package pure-function unit tests (0 → 47 tests)

Coverage Stats

13433 of 20451 branches covered (65.68%)

Branch coverage included in aggregate %.

7 of 7 new or added lines in 1 file covered. (100.0%)

339 existing lines in 5 files now uncovered.

96200 of 132809 relevant lines covered (72.43%)

21.47 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

61.61

/iznik-batch/app/Services/PostcodeRemapService.php

<?php

namespace App\Services;

use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;

/**
 * Remaps postcodes to their nearest enclosing area using PostGIS KNN queries.
 *
 * When a location area's geometry is created or modified, postcodes within
 * that geometry need to be reassigned to the correct (smallest, nearest) area.
 * This mirrors the V1 PHP Location::remapPostcodes() logic.
 *
 * Uses PostgreSQL/PostGIS for efficient K-nearest-neighbor spatial queries,
 * and MySQL for reading/writing the canonical locations data.
 */
class PostcodeRemapService
{
    /**
     * SRID used for spatial operations (Web Mercator).
     */
    private int $srid;

    public function __construct()
    {
        $this->srid = (int) config('freegle.srid', 3857);
    }

    /**
     * Remap postcodes within a given WKT polygon to their nearest area.
     *
     * Syncs the affected location to PostgreSQL first, then queries PostGIS
     * KNN to find the best area for each postcode.
     *
     * @param int|null $locationId The location that was modified (for incremental sync).
     * @param string|null $polygon WKT polygon to scope the remap. NULL = remap all.
     * @return int Number of postcodes remapped.
     */
    public function remapPostcodes(?int $locationId = NULL, ?string $polygon = NULL): int
    {
        if (! $this->postgresAvailable()) {
            Log::warning('PostcodeRemapService: PostgreSQL connection not available, skipping remap');

            return 0;
        }

        // Ensure PostgreSQL schema exists and sync location data.
        $this->ensurePostgresSchema();

        if ($polygon) {
            // Sync all locations within the polygon scope to PostgreSQL.
            // This is critical: the location_id in the task is the location that was edited,
            // but other locations within the polygon (e.g. newly created areas) may also need
            // syncing. V1 synced the edited location inline in setGeometry() before calling
            // remapPostcodes(), and relied on a nightly cron for everything else. We do better
            // by syncing all locations in the affected area.
            $this->syncLocationsInPolygon($polygon);
        } elseif ($locationId) {
            $this->syncSingleLocation($locationId);
        } else {
            $this->syncAllLocations();
        }

        $geomFilter = '';
        $params = [];

        if ($polygon) {
            $geomFilter = "ST_Contains(ST_GeomFromText(?, {$this->srid}), locations_spatial.geometry) AND";
            $params[] = $polygon;
        }

        // Fetch all full postcodes (contain a space) within the scope.
        $postcodes = DB::select("
            SELECT DISTINCT locations_spatial.locationid, locations.name,
                   locations.lat, locations.lng, locations.areaid
            FROM locations_spatial
            INNER JOIN locations ON locations_spatial.locationid = locations.id
            WHERE {$geomFilter} locations.type = 'Postcode'
            AND LOCATE(' ', locations.name) > 0
        ", $params);

        $count = 0;
        $updated = 0;

        foreach ($postcodes as $pc) {
            $newAreaId = $this->findNearestArea($pc->lng, $pc->lat);

            if ($newAreaId && $newAreaId != $pc->areaid) {
                DB::update('UPDATE locations SET areaid = ? WHERE id = ?', [
                    $newAreaId,
                    $pc->locationid,
                ]);
                $updated++;
            }

            $count++;

            if ($count % 1000 === 0) {
                Log::info("PostcodeRemapService: processed {$count}/" . count($postcodes) . ", updated {$updated}");
            }
        }

        Log::info("PostcodeRemapService: remapped {$updated}/{$count} postcodes");

        return $updated;
    }

    /**
     * Find the nearest area location for a given point using PostGIS KNN.
     *
     * Uses expanding buffer intersection levels (matching V1 algorithm) to find
     * the smallest area that contains or is very close to the point.
     *
     * @param float $lng Longitude
     * @param float $lat Latitude
     * @return int|null Location ID of the best matching area, or null.
     */
    public function findNearestArea(float $lng, float $lat): ?int
    {
        $result = DB::connection('pgsql')->select("
            WITH ourpoint AS (
                SELECT ST_MakePoint(?, ?) AS p
            )
            SELECT
                locationid,
                name,
                ST_Area(location) AS area,
                dist,
                CASE
                    WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.00015625), ?)) THEN 1
                    WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.0003125), ?)) THEN 2
                    WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.000625), ?)) THEN 3
                    WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.00125), ?)) THEN 4
                    WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.0025), ?)) THEN 5
                    WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.005), ?)) THEN 6
                    WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.01), ?)) THEN 7
                    WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.02), ?)) THEN 8
                    WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.04), ?)) THEN 9
                    WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.08), ?)) THEN 10
                    WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.16), ?)) THEN 11
                    WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.32), ?)) THEN 12
                END AS intersects
            FROM (
                SELECT locationid,
                       name,
                       location,
                       location <-> ST_SetSRID((SELECT p FROM ourpoint), ?) AS dist
                FROM locations
                WHERE ST_Area(location) BETWEEN 0.00001 AND 0.15
                ORDER BY location <-> ST_SetSRID((SELECT p FROM ourpoint), ?)
                LIMIT 10
            ) q
            ORDER BY intersects ASC, area ASC
            LIMIT 1
        ", [
            $lng, $lat,
            // 12 SRID params for the buffer intersections
            $this->srid, $this->srid, $this->srid, $this->srid,
            $this->srid, $this->srid, $this->srid, $this->srid,
            $this->srid, $this->srid, $this->srid, $this->srid,
            // 2 SRID params for the KNN subquery
            $this->srid, $this->srid,
        ]);

        if (count($result) > 0) {
            return (int) $result[0]->locationid;
        }

        return NULL;
    }

    /**
     * Ensure the PostgreSQL locations table and indexes exist.
     */
    public function ensurePostgresSchema(): void
    {
        $pgsql = DB::connection('pgsql');

        $pgsql->statement('CREATE EXTENSION IF NOT EXISTS postgis');
        $pgsql->statement('CREATE EXTENSION IF NOT EXISTS btree_gist');

        // Create the location_type enum if it doesn't exist.
        $typeExists = $pgsql->selectOne("SELECT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'location_type')");
        if (! $typeExists->exists) {
            $pgsql->statement("CREATE TYPE location_type AS ENUM('Road','Polygon','Line','Point','Postcode')");
        }

        // Create the locations table if it doesn't exist.
        $tableExists = $pgsql->selectOne("SELECT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'locations')");
        if (! $tableExists->exists) {
            $pgsql->statement('CREATE TABLE locations (
                id serial PRIMARY KEY,
                locationid bigint UNIQUE NOT NULL,
                name text,
                type location_type,
                area numeric,
                location geometry
            )');
            $pgsql->statement('CREATE INDEX idx_locations_location ON locations USING gist (location)');
        }
    }

    /**
     * Sync a single location from MySQL to PostgreSQL (upsert).
     *
     * Matches V1's per-location sync in setGeometry() — fast for single updates.
     */
    private function syncSingleLocation(int $locationId): void
    {
        $loc = DB::selectOne("
            SELECT locations.id, name, type,
                   ST_AsText(CASE WHEN ourgeometry IS NOT NULL THEN ourgeometry ELSE geometry END) AS geom
            FROM locations
            LEFT JOIN locations_excluded le ON locations.id = le.locationid
            WHERE locations.id = ?
            AND le.locationid IS NULL
            AND ST_Dimension(CASE WHEN ourgeometry IS NOT NULL THEN ourgeometry ELSE geometry END) = 2
            AND type != 'Postcode'
        ", [$locationId]);

        if (! $loc || ! $loc->geom) {
            return;
        }

        DB::connection('pgsql')->statement(
            "INSERT INTO locations (locationid, name, type, area, location)
             VALUES (?, ?, ?, ST_Area(ST_GeomFromText(?, ?)), ST_GeomFromText(?, ?))
             ON CONFLICT (locationid) DO UPDATE SET
                 name = EXCLUDED.name,
                 type = EXCLUDED.type,
                 area = EXCLUDED.area,
                 location = EXCLUDED.location",
            [$loc->id, $loc->name, $loc->type, $loc->geom, $this->srid, $loc->geom, $this->srid]
        );
    }

    /**
     * Sync all non-postcode polygon locations within a WKT polygon to PostgreSQL.
     *
     * When a location is created or its geometry is changed, the remap task carries
     * the polygon scope. We need to sync all locations intersecting that scope —
     * not just the edited location — because newly created or recently modified
     * locations in the area may not yet be in PostgreSQL.
     */
    private function syncLocationsInPolygon(string $polygon): void
    {
        $locations = DB::select("
            SELECT locations.id, locations.name, locations.type,
                   ST_AsText(CASE WHEN ourgeometry IS NOT NULL THEN ourgeometry ELSE ls.geometry END) AS geom
            FROM locations_spatial ls
            INNER JOIN locations ON ls.locationid = locations.id
            LEFT JOIN locations_excluded le ON locations.id = le.locationid
            WHERE le.locationid IS NULL
            AND ST_Intersects(ls.geometry, ST_GeomFromText(?, {$this->srid}))
            AND ST_Dimension(CASE WHEN ourgeometry IS NOT NULL THEN ourgeometry ELSE ls.geometry END) = 2
            AND locations.type != 'Postcode'
        ", [$polygon]);

        $synced = 0;

        foreach ($locations as $loc) {
            if (! $loc->geom) {
                continue;
            }

            DB::connection('pgsql')->statement(
                "INSERT INTO locations (locationid, name, type, area, location)
                 VALUES (?, ?, ?, ST_Area(ST_GeomFromText(?, ?)), ST_GeomFromText(?, ?))
                 ON CONFLICT (locationid) DO UPDATE SET
                     name = EXCLUDED.name,
                     type = EXCLUDED.type,
                     area = EXCLUDED.area,
                     location = EXCLUDED.location",
                [$loc->id, $loc->name, $loc->type, $loc->geom, $this->srid, $loc->geom, $this->srid]
            );

            $synced++;
        }

        Log::info("PostcodeRemapService: synced {$synced} locations in polygon scope to PostgreSQL");
    }

    /**
     * Full sync of all non-postcode polygon locations from MySQL to PostgreSQL.
     *
     * Uses a temp table + atomic swap matching V1's copyLocationsToPostgresql().
     * Used when no specific location ID is provided (full remap).
     */
    private function syncAllLocations(): void
    {
        $pgsql = DB::connection('pgsql');
        $uniq = '_' . uniqid();

        $pgsql->statement("DROP TABLE IF EXISTS locations_tmp{$uniq}");
        $pgsql->statement("CREATE TABLE locations_tmp{$uniq} (
            id serial PRIMARY KEY,
            locationid bigint UNIQUE NOT NULL,
            name text,
            type location_type,
            area numeric,
            location geometry
        )");
        $pgsql->statement("ALTER TABLE locations_tmp{$uniq} SET UNLOGGED");

        // Fetch non-excluded polygon locations from MySQL.
        $locations = DB::select("
            SELECT locations.id, name, type,
                   ST_AsText(CASE WHEN ourgeometry IS NOT NULL THEN ourgeometry ELSE geometry END) AS geom
            FROM locations
            LEFT JOIN locations_excluded le ON locations.id = le.locationid
            WHERE le.locationid IS NULL
            AND ST_Dimension(CASE WHEN ourgeometry IS NOT NULL THEN ourgeometry ELSE geometry END) = 2
            AND type != 'Postcode'
        ");

        foreach ($locations as $loc) {
            if (! $loc->geom) {
                continue;
            }

            $pgsql->insert(
                "INSERT INTO locations_tmp{$uniq} (locationid, name, type, area, location)
                 VALUES (?, ?, ?, ST_Area(ST_GeomFromText(?, ?)), ST_GeomFromText(?, ?))",
                [$loc->id, $loc->name, $loc->type, $loc->geom, $this->srid, $loc->geom, $this->srid]
            );
        }

        // Build index on temp table before swap.
        $pgsql->statement("CREATE INDEX idx_loc_tmp{$uniq} ON locations_tmp{$uniq} USING gist (location)");

        // Atomic swap: rename current to old, temp to current, drop old.
        $pgsql->statement("ALTER TABLE IF EXISTS locations RENAME TO locations_old{$uniq}");
        $pgsql->statement("ALTER TABLE locations_tmp{$uniq} RENAME TO locations");
        $pgsql->statement("ALTER INDEX idx_loc_tmp{$uniq} RENAME TO idx_locations_location");
        $pgsql->statement("DROP TABLE IF EXISTS locations_old{$uniq}");

        Log::info('PostcodeRemapService: synced ' . count($locations) . ' locations to PostgreSQL');
    }

    /**
     * Check if the PostgreSQL connection is available.
     */
    public function postgresAvailable(): bool
    {
        try {
            DB::connection('pgsql')->getPdo();

            return TRUE;
        } catch (\Throwable $e) {
            return FALSE;
        }
    }
}

1	<?php
2
3	namespace App\Services;
4
5	use Illuminate\Support\Facades\DB;
6	use Illuminate\Support\Facades\Log;
7
8	/**
9	* Remaps postcodes to their nearest enclosing area using PostGIS KNN queries.
10	*
11	* When a location area's geometry is created or modified, postcodes within
12	* that geometry need to be reassigned to the correct (smallest, nearest) area.
13	* This mirrors the V1 PHP Location::remapPostcodes() logic.
14	*
15	* Uses PostgreSQL/PostGIS for efficient K-nearest-neighbor spatial queries,
16	* and MySQL for reading/writing the canonical locations data.
17	*/
18	class PostcodeRemapService
19	{
20	/**
21	* SRID used for spatial operations (Web Mercator).
22	*/
23	private int $srid;
24
25	public function __construct()	5✔
26	{
27	$this->srid = (int) config('freegle.srid', 3857);	5✔
28	}
29
30	/**
31	* Remap postcodes within a given WKT polygon to their nearest area.
32	*
33	* Syncs the affected location to PostgreSQL first, then queries PostGIS
34	* KNN to find the best area for each postcode.
35	*
36	* @param int\|null $locationId The location that was modified (for incremental sync).
37	* @param string\|null $polygon WKT polygon to scope the remap. NULL = remap all.
38	* @return int Number of postcodes remapped.
39	*/
40	public function remapPostcodes(?int $locationId = NULL, ?string $polygon = NULL): int	3✔
41	{
42	if (! $this->postgresAvailable()) {	3✔
43	Log::warning('PostcodeRemapService: PostgreSQL connection not available, skipping remap');	1✔
44
45	return 0;	1✔
46	}
47
48	// Ensure PostgreSQL schema exists and sync location data.
49	$this->ensurePostgresSchema();	2✔
50
51	if ($polygon) {	2✔
52	// Sync all locations within the polygon scope to PostgreSQL.
53	// This is critical: the location_id in the task is the location that was edited,
54	// but other locations within the polygon (e.g. newly created areas) may also need
55	// syncing. V1 synced the edited location inline in setGeometry() before calling
56	// remapPostcodes(), and relied on a nightly cron for everything else. We do better
57	// by syncing all locations in the affected area.
58	$this->syncLocationsInPolygon($polygon);	2✔
59	} elseif ($locationId) {	×
60	$this->syncSingleLocation($locationId);	×
61	} else {
62	$this->syncAllLocations();	×
63	}
64
65	$geomFilter = '';	2✔
66	$params = [];	2✔
67
68	if ($polygon) {	2✔
69	$geomFilter = "ST_Contains(ST_GeomFromText(?, {$this->srid}), locations_spatial.geometry) AND";	2✔
70	$params[] = $polygon;	2✔
71	}
72
73	// Fetch all full postcodes (contain a space) within the scope.
74	$postcodes = DB::select("	2✔
75	SELECT DISTINCT locations_spatial.locationid, locations.name,
76	locations.lat, locations.lng, locations.areaid
77	FROM locations_spatial
78	INNER JOIN locations ON locations_spatial.locationid = locations.id
79	WHERE {$geomFilter} locations.type = 'Postcode'	2✔
80	AND LOCATE(' ', locations.name) > 0
81	", $params);	2✔
82
83	$count = 0;	2✔
84	$updated = 0;	2✔
85
86	foreach ($postcodes as $pc) {	2✔
87	$newAreaId = $this->findNearestArea($pc->lng, $pc->lat);	2✔
88
89	if ($newAreaId && $newAreaId != $pc->areaid) {	2✔
90	DB::update('UPDATE locations SET areaid = ? WHERE id = ?', [	2✔
91	$newAreaId,	2✔
92	$pc->locationid,	2✔
93	]);	2✔
94	$updated++;	2✔
95	}
96
97	$count++;	2✔
98
99	if ($count % 1000 === 0) {	2✔
UNCOV 100	Log::info("PostcodeRemapService: processed {$count}/" . count($postcodes) . ", updated {$updated}");	×
101	}
102	}
103
104	Log::info("PostcodeRemapService: remapped {$updated}/{$count} postcodes");	2✔
105
106	return $updated;	2✔
107	}
108
109	/**
110	* Find the nearest area location for a given point using PostGIS KNN.
111	*
112	* Uses expanding buffer intersection levels (matching V1 algorithm) to find
113	* the smallest area that contains or is very close to the point.
114	*
115	* @param float $lng Longitude
116	* @param float $lat Latitude
117	* @return int\|null Location ID of the best matching area, or null.
118	*/
119	public function findNearestArea(float $lng, float $lat): ?int	3✔
120	{
121	$result = DB::connection('pgsql')->select("	3✔
122	WITH ourpoint AS (
123	SELECT ST_MakePoint(?, ?) AS p
124	)
125	SELECT
126	locationid,
127	name,
128	ST_Area(location) AS area,
129	dist,
130	CASE
131	WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.00015625), ?)) THEN 1
132	WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.0003125), ?)) THEN 2
133	WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.000625), ?)) THEN 3
134	WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.00125), ?)) THEN 4
135	WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.0025), ?)) THEN 5
136	WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.005), ?)) THEN 6
137	WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.01), ?)) THEN 7
138	WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.02), ?)) THEN 8
139	WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.04), ?)) THEN 9
140	WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.08), ?)) THEN 10
141	WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.16), ?)) THEN 11
142	WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.32), ?)) THEN 12
143	END AS intersects
144	FROM (
145	SELECT locationid,
146	name,
147	location,
148	location <-> ST_SetSRID((SELECT p FROM ourpoint), ?) AS dist
149	FROM locations
150	WHERE ST_Area(location) BETWEEN 0.00001 AND 0.15
151	ORDER BY location <-> ST_SetSRID((SELECT p FROM ourpoint), ?)
152	LIMIT 10
153	) q
154	ORDER BY intersects ASC, area ASC
155	LIMIT 1
156	", [	3✔
157	$lng, $lat,	3✔
158	// 12 SRID params for the buffer intersections
159	$this->srid, $this->srid, $this->srid, $this->srid,	3✔
160	$this->srid, $this->srid, $this->srid, $this->srid,	3✔
161	$this->srid, $this->srid, $this->srid, $this->srid,	3✔
162	// 2 SRID params for the KNN subquery
163	$this->srid, $this->srid,	3✔
164	]);	3✔
165
166	if (count($result) > 0) {	3✔
167	return (int) $result[0]->locationid;	3✔
168	}
169
UNCOV 170	return NULL;	×
171	}
172
173	/**
174	* Ensure the PostgreSQL locations table and indexes exist.
175	*/
176	public function ensurePostgresSchema(): void	2✔
177	{
178	$pgsql = DB::connection('pgsql');	2✔
179
180	$pgsql->statement('CREATE EXTENSION IF NOT EXISTS postgis');	2✔
181	$pgsql->statement('CREATE EXTENSION IF NOT EXISTS btree_gist');	2✔
182
183	// Create the location_type enum if it doesn't exist.
184	$typeExists = $pgsql->selectOne("SELECT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'location_type')");	2✔
185	if (! $typeExists->exists) {	2✔
UNCOV 186	$pgsql->statement("CREATE TYPE location_type AS ENUM('Road','Polygon','Line','Point','Postcode')");	×
187	}
188
189	// Create the locations table if it doesn't exist.
190	$tableExists = $pgsql->selectOne("SELECT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'locations')");	2✔
191	if (! $tableExists->exists) {	2✔
UNCOV 192	$pgsql->statement('CREATE TABLE locations (	×
193	id serial PRIMARY KEY,
194	locationid bigint UNIQUE NOT NULL,
195	name text,
196	type location_type,
197	area numeric,
198	location geometry
UNCOV 199	)');	×
UNCOV 200	$pgsql->statement('CREATE INDEX idx_locations_location ON locations USING gist (location)');	×
201	}
202	}
203
204	/**
205	* Sync a single location from MySQL to PostgreSQL (upsert).
206	*
207	* Matches V1's per-location sync in setGeometry() — fast for single updates.
208	*/
UNCOV 209	private function syncSingleLocation(int $locationId): void	×
210	{
211	$loc = DB::selectOne("	×
212	SELECT locations.id, name, type,
213	ST_AsText(CASE WHEN ourgeometry IS NOT NULL THEN ourgeometry ELSE geometry END) AS geom
214	FROM locations
215	LEFT JOIN locations_excluded le ON locations.id = le.locationid
216	WHERE locations.id = ?
217	AND le.locationid IS NULL
218	AND ST_Dimension(CASE WHEN ourgeometry IS NOT NULL THEN ourgeometry ELSE geometry END) = 2
219	AND type != 'Postcode'
220	", [$locationId]);	×
221
222	if (! $loc \|\| ! $loc->geom) {	×
UNCOV 223	return;	×
224	}
225
UNCOV 226	DB::connection('pgsql')->statement(	×
UNCOV 227	"INSERT INTO locations (locationid, name, type, area, location)	×
228	VALUES (?, ?, ?, ST_Area(ST_GeomFromText(?, ?)), ST_GeomFromText(?, ?))
229	ON CONFLICT (locationid) DO UPDATE SET
230	name = EXCLUDED.name,
231	type = EXCLUDED.type,
232	area = EXCLUDED.area,
233	location = EXCLUDED.location",	×
234	[$loc->id, $loc->name, $loc->type, $loc->geom, $this->srid, $loc->geom, $this->srid]	×
UNCOV 235	);	×
236	}
237
238	/**
239	* Sync all non-postcode polygon locations within a WKT polygon to PostgreSQL.
240	*
241	* When a location is created or its geometry is changed, the remap task carries
242	* the polygon scope. We need to sync all locations intersecting that scope —
243	* not just the edited location — because newly created or recently modified
244	* locations in the area may not yet be in PostgreSQL.
245	*/
246	private function syncLocationsInPolygon(string $polygon): void	2✔
247	{
248	$locations = DB::select("	2✔
249	SELECT locations.id, locations.name, locations.type,
250	ST_AsText(CASE WHEN ourgeometry IS NOT NULL THEN ourgeometry ELSE ls.geometry END) AS geom
251	FROM locations_spatial ls
252	INNER JOIN locations ON ls.locationid = locations.id
253	LEFT JOIN locations_excluded le ON locations.id = le.locationid
254	WHERE le.locationid IS NULL
255	AND ST_Intersects(ls.geometry, ST_GeomFromText(?, {$this->srid}))	2✔
256	AND ST_Dimension(CASE WHEN ourgeometry IS NOT NULL THEN ourgeometry ELSE ls.geometry END) = 2
257	AND locations.type != 'Postcode'
258	", [$polygon]);	2✔
259
260	$synced = 0;	2✔
261
262	foreach ($locations as $loc) {	2✔
263	if (! $loc->geom) {	2✔
UNCOV 264	continue;	×
265	}
266
267	DB::connection('pgsql')->statement(	2✔
268	"INSERT INTO locations (locationid, name, type, area, location)	2✔
269	VALUES (?, ?, ?, ST_Area(ST_GeomFromText(?, ?)), ST_GeomFromText(?, ?))
270	ON CONFLICT (locationid) DO UPDATE SET
271	name = EXCLUDED.name,
272	type = EXCLUDED.type,
273	area = EXCLUDED.area,
274	location = EXCLUDED.location",	2✔
275	[$loc->id, $loc->name, $loc->type, $loc->geom, $this->srid, $loc->geom, $this->srid]	2✔
276	);	2✔
277
278	$synced++;	2✔
279	}
280
281	Log::info("PostcodeRemapService: synced {$synced} locations in polygon scope to PostgreSQL");	2✔
282	}
283
284	/**
285	* Full sync of all non-postcode polygon locations from MySQL to PostgreSQL.
286	*
287	* Uses a temp table + atomic swap matching V1's copyLocationsToPostgresql().
288	* Used when no specific location ID is provided (full remap).
289	*/
UNCOV 290	private function syncAllLocations(): void	×
291	{
UNCOV 292	$pgsql = DB::connection('pgsql');	×
UNCOV 293	$uniq = '_' . uniqid();	×
294
UNCOV 295	$pgsql->statement("DROP TABLE IF EXISTS locations_tmp{$uniq}");	×
UNCOV 296	$pgsql->statement("CREATE TABLE locations_tmp{$uniq} (	×
297	id serial PRIMARY KEY,
298	locationid bigint UNIQUE NOT NULL,
299	name text,
300	type location_type,
301	area numeric,
302	location geometry
UNCOV 303	)");	×
UNCOV 304	$pgsql->statement("ALTER TABLE locations_tmp{$uniq} SET UNLOGGED");	×
305
306	// Fetch non-excluded polygon locations from MySQL.
UNCOV 307	$locations = DB::select("	×
308	SELECT locations.id, name, type,
309	ST_AsText(CASE WHEN ourgeometry IS NOT NULL THEN ourgeometry ELSE geometry END) AS geom
310	FROM locations
311	LEFT JOIN locations_excluded le ON locations.id = le.locationid
312	WHERE le.locationid IS NULL
313	AND ST_Dimension(CASE WHEN ourgeometry IS NOT NULL THEN ourgeometry ELSE geometry END) = 2
314	AND type != 'Postcode'
UNCOV 315	");	×
316
UNCOV 317	foreach ($locations as $loc) {	×
UNCOV 318	if (! $loc->geom) {	×
UNCOV 319	continue;	×
320	}
321
322	$pgsql->insert(	×
323	"INSERT INTO locations_tmp{$uniq} (locationid, name, type, area, location)	×
UNCOV 324	VALUES (?, ?, ?, ST_Area(ST_GeomFromText(?, ?)), ST_GeomFromText(?, ?))",	×
325	[$loc->id, $loc->name, $loc->type, $loc->geom, $this->srid, $loc->geom, $this->srid]	×
326	);	×
327	}
328
329	// Build index on temp table before swap.
UNCOV 330	$pgsql->statement("CREATE INDEX idx_loc_tmp{$uniq} ON locations_tmp{$uniq} USING gist (location)");	×
331
332	// Atomic swap: rename current to old, temp to current, drop old.
333	$pgsql->statement("ALTER TABLE IF EXISTS locations RENAME TO locations_old{$uniq}");	×
334	$pgsql->statement("ALTER TABLE locations_tmp{$uniq} RENAME TO locations");	×
UNCOV 335	$pgsql->statement("ALTER INDEX idx_loc_tmp{$uniq} RENAME TO idx_locations_location");	×
UNCOV 336	$pgsql->statement("DROP TABLE IF EXISTS locations_old{$uniq}");	×
337
UNCOV 338	Log::info('PostcodeRemapService: synced ' . count($locations) . ' locations to PostgreSQL');	×
339	}
340
341	/**
342	* Check if the PostgreSQL connection is available.
343	*/
344	public function postgresAvailable(): bool	3✔
345	{
346	try {
347	DB::connection('pgsql')->getPdo();	3✔
348
349	return TRUE;	2✔
350	} catch (\Throwable $e) {	1✔
351	return FALSE;	1✔
352	}
353	}
354	}

Freegle / Iznik / 5611

Source File Press 'n' to go to next uncovered line, 'b' for previous

Source File
Press 'n' to go to next uncovered line, 'b' for previous