27958

Committed 21 Jul 2026 08:52AM UTC coverage: 72.493% (-0.006%) from 72.499%

Build # 27958

Build Type

push

circleci

Committed by

invalid-email-address

Commit Message

fix(matched): slice the backfill so it doesn't blow up the DB connection

The first --backfill drove buildNotifications on all ~24k open posts at once:
its long apiv2 phase idled the DB connection out and loadOpenMessages then hit
'2006 MySQL server has gone away' on a giant WHERE id IN(...). Walk the open-age
window in bounded time slices instead (backfill_slice_minutes, default 360):
freshPosts now takes an optional upper bound and buildNotifications an explicit
(since, until), so each slice drives on a small, bounded set — short apiv2
phase, small queries, and each slice's sends persist before the next (partial
progress survives a mid-run failure). Extracted dispatchNotifications() so the
scheduled run and each backfill slice share one delivery path.

Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>

Coverage Stats

13216 of 17415 branches covered (75.89%)

Branch coverage included in aggregate %.

55 of 76 new or added lines in 2 files covered. (72.37%)

10 existing lines in 4 files now uncovered.

139014 of 192578 relevant lines covered (72.19%)

37.91 hits per line

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

96.45

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

<?php

namespace App\Services;

use App\Models\Message;
use App\Models\MessageGroup;
use App\Services\Ripple\ReachBoundsService;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;

class MessageSpatialService
{
    // V1: MessageCollection::RECENTPOSTS = "Midnight 31 days ago". Public so other
    // features (e.g. the matched-posts backfill) can bound themselves to the same
    // open-age window that governs messages_spatial membership.
    public const RECENT_DAYS = 31;
    private const SRID = 3857;

    private SpatialAdminService $spatialAdmin;

    /** Prunes/restores rippling sandwich bounds when a post's outcome flips. */
    private ReachBoundsService $reachBounds;

    public function __construct(SpatialAdminService $spatialAdmin, ?ReachBoundsService $reachBounds = null)
    {
        $this->spatialAdmin = $spatialAdmin;
        $this->reachBounds = $reachBounds ?? new ReachBoundsService();
    }

    public function updateSpatialIndex(bool $dryRun = false): array
    {
        $stats = [
            'upserted_recent' => $this->upsertRecentMessages($dryRun),
            'outcomes_updated' => $this->updateOutcomesAndPromises($dryRun),
            'removed_deleted' => $this->removeDeletedMessages($dryRun),
            'removed_old' => $this->removeOldMessages($dryRun),
            'removed_non_approved' => $this->removeNonApprovedMessages($dryRun),
        ];

        $total = array_sum($stats);
        $stats['total'] = $total;

        Log::info("MessageSpatialIndex: " . ($dryRun ? 'would update ' : 'updated ') . "{$total} entries", $stats);

        return $stats;
    }

    private function upsertRecentMessages(bool $dryRun = false): int
    {
        $cutoff = date('Y-m-d', strtotime('Midnight ' . self::RECENT_DAYS . ' days ago'));

        $msgs = DB::table('messages')
            ->join('messages_groups', 'messages_groups.msgid', '=', 'messages.id')
            ->join('users', 'users.id', '=', 'messages.fromuser')
            ->leftJoin('messages_spatial', 'messages_spatial.msgid', '=', 'messages_groups.msgid')
            ->leftJoin('messages_outcomes', 'messages_outcomes.msgid', '=', 'messages.id')
            ->where('messages_groups.arrival', '>=', $cutoff)
            ->whereNotNull('messages.lat')
            ->whereNotNull('messages.lng')
            ->whereNull('messages.deleted')
            ->where('messages_groups.collection', MessageGroup::COLLECTION_APPROVED)
            // The membership itself must be live. Rippling's "removed on origin removal" (and
            // group-leave retraction) sets messages_groups.deleted=1 while leaving
            // collection=Approved and messages.deleted NULL, so without this a removed copy with
            // a recent arrival is indexed straight back into browse. (Whatever set that arrival —
            // e.g. autorepost — should not have touched a dead membership either; see
            // AutoRepostService::getCandidates.)
            ->where('messages_groups.deleted', 0)
            ->whereNull('users.deleted')
            ->where(function ($q) {
                // Include messages with no outcome, or Taken/Received (same as V1: outcome IS NULL OR outcome IN ('Taken','Received'))
                $q->whereNull('messages_outcomes.outcome')
                    ->orWhereIn('messages_outcomes.outcome', [Message::OUTCOME_TAKEN, Message::OUTCOME_RECEIVED]);
            })
            ->where(function ($q) {
                $q->whereNull('messages_spatial.msgid')
                    ->orWhereRaw('ST_X(messages_spatial.point) != messages.lng')
                    ->orWhereRaw('ST_Y(messages_spatial.point) != messages.lat')
                    ->orWhereNull('messages_spatial.groupid')
                    ->orWhereRaw('messages_spatial.groupid != messages_groups.groupid')
                    ->orWhereRaw('messages_groups.arrival != messages_spatial.arrival');
            })
            ->select(
                'messages.id',
                'messages.lat',
                'messages.lng',
                'messages_groups.groupid',
                'messages_groups.arrival',
                'messages_groups.msgtype',
            )
            ->distinct()
            ->get();

        $count = 0;
        foreach ($msgs as $msg) {
            if (!$dryRun) {
                // Coordinates come from DB, not user input — safe to embed in WKT.
                $wkt = "POINT({$msg->lng} {$msg->lat})";
                $srid = self::SRID;

                DB::statement(
                    "INSERT INTO messages_spatial (msgid, point, groupid, msgtype, arrival)
                     VALUES (?, ST_GeomFromText('$wkt', $srid), ?, ?, ?)
                     ON DUPLICATE KEY UPDATE
                       point = ST_GeomFromText('$wkt', $srid),
                       groupid = ?,
                       msgtype = ?,
                       arrival = ?",
                    [$msg->id, $msg->groupid, $msg->msgtype, $msg->arrival,
                     $msg->groupid, $msg->msgtype, $msg->arrival]
                );
            }
            $count++;
        }

        return $count;
    }

    private function updateOutcomesAndPromises(bool $dryRun = false): int
    {
        $msgs = DB::table('messages_spatial')
            ->leftJoin('messages_outcomes', 'messages_outcomes.msgid', '=', 'messages_spatial.msgid')
            ->leftJoin('messages_promises', 'messages_promises.msgid', '=', 'messages_spatial.msgid')
            ->select(
                'messages_spatial.id',
                'messages_spatial.msgid',
                'messages_spatial.successful',
                'messages_spatial.promised',
                'messages_outcomes.outcome',
                'messages_promises.promisedat',
            )
            ->orderByDesc('messages_outcomes.timestamp')
            ->get();

        $count = 0;
        $deletedMsgids = [];
        foreach ($msgs as $msg) {
            if ($msg->outcome === Message::OUTCOME_WITHDRAWN || $msg->outcome === Message::OUTCOME_EXPIRED) {
                if (!$dryRun) {
                    DB::table('messages_spatial')->where('id', $msg->id)->delete();
                    $deletedMsgids[] = $msg->msgid;
                }
                $count++;
            } elseif ($msg->outcome === Message::OUTCOME_TAKEN || $msg->outcome === Message::OUTCOME_RECEIVED) {
                if (!$msg->successful) {
                    if (!$dryRun) {
                        DB::table('messages_spatial')->where('id', $msg->id)->update(['successful' => 1]);
                        // Completed → prune the post from the cheap reach path via its
                        // BOUNDS row only; the exact polygon stays for the consumers that
                        // still need it (plans/2026-07-17-db3-cpu-reach-sql-prefilter.md).
                        $this->reachBounds->degradeForCompleted((int) $msg->msgid);
                    }
                    $count++;
                }
            } elseif ($msg->successful) {
                if (!$dryRun) {
                    DB::table('messages_spatial')->where('id', $msg->id)->update(['successful' => 0]);
                    // Reopened (outcome removed) → restore working bounds from the stored
                    // polygon, or the post would stay invisible to the cheap reach path.
                    $this->reachBounds->syncFromPolygon((int) $msg->msgid);
                }
                $count++;
            }

            if ($msg->promised && !$msg->promisedat) {
                if (!$dryRun) {
                    DB::table('messages_spatial')->where('id', $msg->id)->update(['promised' => 0]);
                }
                $count++;
            } elseif (!$msg->promised && $msg->promisedat) {
                if (!$dryRun) {
                    DB::table('messages_spatial')->where('id', $msg->id)->update(['promised' => 1]);
                }
                $count++;
            }
        }

        if (!empty($deletedMsgids)) {
            $this->spatialAdmin->removeItems('messages', $deletedMsgids);
        }

        return $count;
    }

    private function removeDeletedMessages(bool $dryRun = false): int
    {
        $rows = DB::table('messages_spatial')
            ->join('messages', 'messages_spatial.msgid', '=', 'messages.id')
            ->leftJoin('users', 'users.id', '=', 'messages.fromuser')
            ->where(function ($q) {
                $q->whereNull('messages.fromuser')
                    ->orWhereNotNull('messages.deleted')
                    ->orWhereNotNull('users.deleted');
            })
            ->select('messages_spatial.id', 'messages_spatial.msgid')
            ->get();

        if ($rows->isEmpty()) {
            return 0;
        }

        if (!$dryRun) {
            DB::table('messages_spatial')->whereIn('id', $rows->pluck('id'))->delete();
            $this->spatialAdmin->removeItems('messages', $rows->pluck('msgid')->all());
        }

        return $rows->count();
    }

    private function removeOldMessages(bool $dryRun = false): int
    {
        $cutoff = date('Y-m-d', strtotime('Midnight ' . self::RECENT_DAYS . ' days ago'));

        $rows = DB::table('messages_spatial')
            ->join('messages_groups', 'messages_groups.msgid', '=', 'messages_spatial.msgid')
            ->where('messages_groups.arrival', '<', $cutoff)
            ->select('messages_spatial.id', 'messages_spatial.msgid')
            ->get();

        if ($rows->isEmpty()) {
            return 0;
        }

        if (!$dryRun) {
            DB::table('messages_spatial')->whereIn('id', $rows->pluck('id'))->delete();
            $this->spatialAdmin->removeItems('messages', $rows->pluck('msgid')->all());
        }

        return $rows->count();
    }

    private function removeNonApprovedMessages(bool $dryRun = false): int
    {
        // Join on BOTH msgid AND groupid: messages_spatial holds one row per post (unique
        // msgid) for a specific group, so a spatial row must only be dropped when the
        // messages_groups row for ITS OWN group is non-approved. Joining on msgid alone
        // would let a rippled-in Pending row on another group (#6) delete the origin post's
        // approved spatial row, flickering it out of browse every spatial-index run.
        //
        // Also drop it when its own membership is soft-deleted (deleted=1): rippling's "removed
        // on origin removal" leaves collection=Approved but sets deleted=1, so the collection
        // check alone would leave a removed copy in browse until it ages out. upsertRecentMessages
        // runs before this pass, so a message still live on ANOTHER group has already had its
        // spatial row re-pointed to that group and is not caught here.
        $rows = DB::table('messages_spatial')
            ->join('messages_groups', function ($join) {
                $join->on('messages_groups.msgid', '=', 'messages_spatial.msgid')
                    ->on('messages_groups.groupid', '=', 'messages_spatial.groupid');
            })
            ->where(function ($q) {
                $q->where('messages_groups.collection', '!=', MessageGroup::COLLECTION_APPROVED)
                    ->orWhere('messages_groups.deleted', 1);
            })
            ->select('messages_spatial.id', 'messages_spatial.msgid')
            ->get();

        if ($rows->isEmpty()) {
            return 0;
        }

        if (!$dryRun) {
            DB::table('messages_spatial')->whereIn('id', $rows->pluck('id'))->delete();
            $this->spatialAdmin->removeItems('messages', $rows->pluck('msgid')->all());
        }

        return $rows->count();
    }

    /**
     * Add a single just-approved message to the spatial index immediately, so it
     * appears in browse/search without waiting for the every-5-minute reconciler.
     *
     * No-op unless the message is Approved, has a location, and has no outcome —
     * messages_spatial backs the public browse/map, so Pending/Spam/Rejected
     * messages must never be added here. Safe to call inside the same transaction
     * that set the collection to Approved (it reads its own uncommitted write).
     */
    public function addApprovedMessage(int $msgid): void
    {
        $msg = DB::table('messages')
            ->join('messages_groups', 'messages_groups.msgid', '=', 'messages.id')
            ->leftJoin('messages_outcomes', 'messages_outcomes.msgid', '=', 'messages.id')
            ->where('messages.id', $msgid)
            ->where('messages_groups.collection', MessageGroup::COLLECTION_APPROVED)
            ->where('messages_groups.deleted', 0)
            ->whereNull('messages.deleted')
            ->whereNotNull('messages.lat')
            ->whereNotNull('messages.lng')
            ->whereNull('messages_outcomes.id')
            ->orderByDesc('messages_groups.arrival')
            ->select(
                'messages.id',
                'messages.lat',
                'messages.lng',
                DB::raw('messages.type as msgtype'),
                'messages_groups.groupid',
                'messages_groups.arrival',
            )
            ->first();

        if (!$msg) {
            return;
        }

        // Coordinates come from the DB, not user input — safe to embed in WKT.
        $wkt  = "POINT({$msg->lng} {$msg->lat})";
        $srid = self::SRID;

        DB::statement(
            "INSERT INTO messages_spatial (msgid, point, groupid, msgtype, arrival)
             VALUES (?, ST_GeomFromText('$wkt', $srid), ?, ?, ?)
             ON DUPLICATE KEY UPDATE
               point = ST_GeomFromText('$wkt', $srid),
               groupid = ?,
               msgtype = ?,
               arrival = ?",
            [$msg->id, $msg->groupid, $msg->msgtype, $msg->arrival,
             $msg->groupid, $msg->msgtype, $msg->arrival]
        );
    }
}

1	<?php
2
3	namespace App\Services;
4
5	use App\Models\Message;
6	use App\Models\MessageGroup;
7	use App\Services\Ripple\ReachBoundsService;
8	use Illuminate\Support\Facades\DB;
9	use Illuminate\Support\Facades\Log;
10
11	class MessageSpatialService
12	{
13	// V1: MessageCollection::RECENTPOSTS = "Midnight 31 days ago". Public so other
14	// features (e.g. the matched-posts backfill) can bound themselves to the same
15	// open-age window that governs messages_spatial membership.
16	public const RECENT_DAYS = 31;
17	private const SRID = 3857;
18
19	private SpatialAdminService $spatialAdmin;
20
21	/** Prunes/restores rippling sandwich bounds when a post's outcome flips. */
22	private ReachBoundsService $reachBounds;
23
24	public function __construct(SpatialAdminService $spatialAdmin, ?ReachBoundsService $reachBounds = null)	22✔
25	{
26	$this->spatialAdmin = $spatialAdmin;	22✔
27	$this->reachBounds = $reachBounds ?? new ReachBoundsService();	22✔
28	}
29
30	public function updateSpatialIndex(bool $dryRun = false): array	16✔
31	{
32	$stats = [	16✔
33	'upserted_recent' => $this->upsertRecentMessages($dryRun),	16✔
34	'outcomes_updated' => $this->updateOutcomesAndPromises($dryRun),	16✔
35	'removed_deleted' => $this->removeDeletedMessages($dryRun),	16✔
36	'removed_old' => $this->removeOldMessages($dryRun),	16✔
37	'removed_non_approved' => $this->removeNonApprovedMessages($dryRun),	16✔
38	];	16✔
39
40	$total = array_sum($stats);	16✔
41	$stats['total'] = $total;	16✔
42
43	Log::info("MessageSpatialIndex: " . ($dryRun ? 'would update ' : 'updated ') . "{$total} entries", $stats);	16✔
44
45	return $stats;	16✔
46	}
47
48	private function upsertRecentMessages(bool $dryRun = false): int	16✔
49	{
50	$cutoff = date('Y-m-d', strtotime('Midnight ' . self::RECENT_DAYS . ' days ago'));	16✔
51
52	$msgs = DB::table('messages')	16✔
53	->join('messages_groups', 'messages_groups.msgid', '=', 'messages.id')	16✔
54	->join('users', 'users.id', '=', 'messages.fromuser')	16✔
55	->leftJoin('messages_spatial', 'messages_spatial.msgid', '=', 'messages_groups.msgid')	16✔
56	->leftJoin('messages_outcomes', 'messages_outcomes.msgid', '=', 'messages.id')	16✔
57	->where('messages_groups.arrival', '>=', $cutoff)	16✔
58	->whereNotNull('messages.lat')	16✔
59	->whereNotNull('messages.lng')	16✔
60	->whereNull('messages.deleted')	16✔
61	->where('messages_groups.collection', MessageGroup::COLLECTION_APPROVED)	16✔
62	// The membership itself must be live. Rippling's "removed on origin removal" (and	16✔
63	// group-leave retraction) sets messages_groups.deleted=1 while leaving	16✔
64	// collection=Approved and messages.deleted NULL, so without this a removed copy with	16✔
65	// a recent arrival is indexed straight back into browse. (Whatever set that arrival —	16✔
66	// e.g. autorepost — should not have touched a dead membership either; see	16✔
67	// AutoRepostService::getCandidates.)	16✔
68	->where('messages_groups.deleted', 0)	16✔
69	->whereNull('users.deleted')	16✔
70	->where(function ($q) {	16✔
71	// Include messages with no outcome, or Taken/Received (same as V1: outcome IS NULL OR outcome IN ('Taken','Received'))
72	$q->whereNull('messages_outcomes.outcome')	16✔
73	->orWhereIn('messages_outcomes.outcome', [Message::OUTCOME_TAKEN, Message::OUTCOME_RECEIVED]);	16✔
74	})	16✔
75	->where(function ($q) {	16✔
76	$q->whereNull('messages_spatial.msgid')	16✔
77	->orWhereRaw('ST_X(messages_spatial.point) != messages.lng')	16✔
78	->orWhereRaw('ST_Y(messages_spatial.point) != messages.lat')	16✔
79	->orWhereNull('messages_spatial.groupid')	16✔
80	->orWhereRaw('messages_spatial.groupid != messages_groups.groupid')	16✔
81	->orWhereRaw('messages_groups.arrival != messages_spatial.arrival');	16✔
82	})	16✔
83	->select(	16✔
84	'messages.id',	16✔
85	'messages.lat',	16✔
86	'messages.lng',	16✔
87	'messages_groups.groupid',	16✔
88	'messages_groups.arrival',	16✔
89	'messages_groups.msgtype',	16✔
90	)	16✔
91	->distinct()	16✔
92	->get();	16✔
93
94	$count = 0;	16✔
95	foreach ($msgs as $msg) {	16✔
96	if (!$dryRun) {	16✔
97	// Coordinates come from DB, not user input — safe to embed in WKT.
98	$wkt = "POINT({$msg->lng} {$msg->lat})";	16✔
99	$srid = self::SRID;	16✔
100
101	DB::statement(	16✔
102	"INSERT INTO messages_spatial (msgid, point, groupid, msgtype, arrival)	16✔
103	VALUES (?, ST_GeomFromText('$wkt', $srid), ?, ?, ?)	16✔
104	ON DUPLICATE KEY UPDATE
105	point = ST_GeomFromText('$wkt', $srid),	16✔
106	groupid = ?,
107	msgtype = ?,
108	arrival = ?",	16✔
109	[$msg->id, $msg->groupid, $msg->msgtype, $msg->arrival,	16✔
110	$msg->groupid, $msg->msgtype, $msg->arrival]	16✔
111	);	16✔
112	}
113	$count++;	16✔
114	}
115
116	return $count;	16✔
117	}
118
119	private function updateOutcomesAndPromises(bool $dryRun = false): int	16✔
120	{
121	$msgs = DB::table('messages_spatial')	16✔
122	->leftJoin('messages_outcomes', 'messages_outcomes.msgid', '=', 'messages_spatial.msgid')	16✔
123	->leftJoin('messages_promises', 'messages_promises.msgid', '=', 'messages_spatial.msgid')	16✔
124	->select(	16✔
125	'messages_spatial.id',	16✔
126	'messages_spatial.msgid',	16✔
127	'messages_spatial.successful',	16✔
128	'messages_spatial.promised',	16✔
129	'messages_outcomes.outcome',	16✔
130	'messages_promises.promisedat',	16✔
131	)	16✔
132	->orderByDesc('messages_outcomes.timestamp')	16✔
133	->get();	16✔
134
135	$count = 0;	16✔
136	$deletedMsgids = [];	16✔
137	foreach ($msgs as $msg) {	16✔
138	if ($msg->outcome === Message::OUTCOME_WITHDRAWN \|\| $msg->outcome === Message::OUTCOME_EXPIRED) {	16✔
139	if (!$dryRun) {	3✔
140	DB::table('messages_spatial')->where('id', $msg->id)->delete();	3✔
141	$deletedMsgids[] = $msg->msgid;	3✔
142	}
143	$count++;	3✔
144	} elseif ($msg->outcome === Message::OUTCOME_TAKEN \|\| $msg->outcome === Message::OUTCOME_RECEIVED) {	16✔
145	if (!$msg->successful) {	16✔
146	if (!$dryRun) {	16✔
147	DB::table('messages_spatial')->where('id', $msg->id)->update(['successful' => 1]);	16✔
148	// Completed → prune the post from the cheap reach path via its
149	// BOUNDS row only; the exact polygon stays for the consumers that
150	// still need it (plans/2026-07-17-db3-cpu-reach-sql-prefilter.md).
151	$this->reachBounds->degradeForCompleted((int) $msg->msgid);	16✔
152	}
153	$count++;	16✔
154	}
155	} elseif ($msg->successful) {	16✔
156	if (!$dryRun) {	1✔
157	DB::table('messages_spatial')->where('id', $msg->id)->update(['successful' => 0]);	1✔
158	// Reopened (outcome removed) → restore working bounds from the stored
159	// polygon, or the post would stay invisible to the cheap reach path.
160	$this->reachBounds->syncFromPolygon((int) $msg->msgid);	1✔
161	}
162	$count++;	1✔
163	}
164
165	if ($msg->promised && !$msg->promisedat) {	16✔
UNCOV 166	if (!$dryRun) {	×
167	DB::table('messages_spatial')->where('id', $msg->id)->update(['promised' => 0]);	×
168	}
169	$count++;	×
170	} elseif (!$msg->promised && $msg->promisedat) {	16✔
UNCOV 171	if (!$dryRun) {	×
172	DB::table('messages_spatial')->where('id', $msg->id)->update(['promised' => 1]);	×
173	}
UNCOV 174	$count++;	×
175	}
176	}
177
178	if (!empty($deletedMsgids)) {	16✔
179	$this->spatialAdmin->removeItems('messages', $deletedMsgids);	3✔
180	}
181
182	return $count;	16✔
183	}
184
185	private function removeDeletedMessages(bool $dryRun = false): int	16✔
186	{
187	$rows = DB::table('messages_spatial')	16✔
188	->join('messages', 'messages_spatial.msgid', '=', 'messages.id')	16✔
189	->leftJoin('users', 'users.id', '=', 'messages.fromuser')	16✔
190	->where(function ($q) {	16✔
191	$q->whereNull('messages.fromuser')	16✔
192	->orWhereNotNull('messages.deleted')	16✔
193	->orWhereNotNull('users.deleted');	16✔
194	})	16✔
195	->select('messages_spatial.id', 'messages_spatial.msgid')	16✔
196	->get();	16✔
197
198	if ($rows->isEmpty()) {	16✔
199	return 0;	14✔
200	}
201
202	if (!$dryRun) {	2✔
203	DB::table('messages_spatial')->whereIn('id', $rows->pluck('id'))->delete();	2✔
204	$this->spatialAdmin->removeItems('messages', $rows->pluck('msgid')->all());	2✔
205	}
206
207	return $rows->count();	2✔
208	}
209
210	private function removeOldMessages(bool $dryRun = false): int	16✔
211	{
212	$cutoff = date('Y-m-d', strtotime('Midnight ' . self::RECENT_DAYS . ' days ago'));	16✔
213
214	$rows = DB::table('messages_spatial')	16✔
215	->join('messages_groups', 'messages_groups.msgid', '=', 'messages_spatial.msgid')	16✔
216	->where('messages_groups.arrival', '<', $cutoff)	16✔
217	->select('messages_spatial.id', 'messages_spatial.msgid')	16✔
218	->get();	16✔
219
220	if ($rows->isEmpty()) {	16✔
221	return 0;	15✔
222	}
223
224	if (!$dryRun) {	1✔
225	DB::table('messages_spatial')->whereIn('id', $rows->pluck('id'))->delete();	1✔
226	$this->spatialAdmin->removeItems('messages', $rows->pluck('msgid')->all());	1✔
227	}
228
229	return $rows->count();	1✔
230	}
231
232	private function removeNonApprovedMessages(bool $dryRun = false): int	16✔
233	{
234	// Join on BOTH msgid AND groupid: messages_spatial holds one row per post (unique
235	// msgid) for a specific group, so a spatial row must only be dropped when the
236	// messages_groups row for ITS OWN group is non-approved. Joining on msgid alone
237	// would let a rippled-in Pending row on another group (#6) delete the origin post's
238	// approved spatial row, flickering it out of browse every spatial-index run.
239	//
240	// Also drop it when its own membership is soft-deleted (deleted=1): rippling's "removed
241	// on origin removal" leaves collection=Approved but sets deleted=1, so the collection
242	// check alone would leave a removed copy in browse until it ages out. upsertRecentMessages
243	// runs before this pass, so a message still live on ANOTHER group has already had its
244	// spatial row re-pointed to that group and is not caught here.
245	$rows = DB::table('messages_spatial')	16✔
246	->join('messages_groups', function ($join) {	16✔
247	$join->on('messages_groups.msgid', '=', 'messages_spatial.msgid')	16✔
248	->on('messages_groups.groupid', '=', 'messages_spatial.groupid');	16✔
249	})	16✔
250	->where(function ($q) {	16✔
251	$q->where('messages_groups.collection', '!=', MessageGroup::COLLECTION_APPROVED)	16✔
252	->orWhere('messages_groups.deleted', 1);	16✔
253	})	16✔
254	->select('messages_spatial.id', 'messages_spatial.msgid')	16✔
255	->get();	16✔
256
257	if ($rows->isEmpty()) {	16✔
258	return 0;	14✔
259	}
260
261	if (!$dryRun) {	2✔
262	DB::table('messages_spatial')->whereIn('id', $rows->pluck('id'))->delete();	2✔
263	$this->spatialAdmin->removeItems('messages', $rows->pluck('msgid')->all());	2✔
264	}
265
266	return $rows->count();	2✔
267	}
268
269	/**
270	* Add a single just-approved message to the spatial index immediately, so it
271	* appears in browse/search without waiting for the every-5-minute reconciler.
272	*
273	* No-op unless the message is Approved, has a location, and has no outcome —
274	* messages_spatial backs the public browse/map, so Pending/Spam/Rejected
275	* messages must never be added here. Safe to call inside the same transaction
276	* that set the collection to Approved (it reads its own uncommitted write).
277	*/
278	public function addApprovedMessage(int $msgid): void	6✔
279	{
280	$msg = DB::table('messages')	6✔
281	->join('messages_groups', 'messages_groups.msgid', '=', 'messages.id')	6✔
282	->leftJoin('messages_outcomes', 'messages_outcomes.msgid', '=', 'messages.id')	6✔
283	->where('messages.id', $msgid)	6✔
284	->where('messages_groups.collection', MessageGroup::COLLECTION_APPROVED)	6✔
285	->where('messages_groups.deleted', 0)	6✔
286	->whereNull('messages.deleted')	6✔
287	->whereNotNull('messages.lat')	6✔
288	->whereNotNull('messages.lng')	6✔
289	->whereNull('messages_outcomes.id')	6✔
290	->orderByDesc('messages_groups.arrival')	6✔
291	->select(	6✔
292	'messages.id',	6✔
293	'messages.lat',	6✔
294	'messages.lng',	6✔
295	DB::raw('messages.type as msgtype'),	6✔
296	'messages_groups.groupid',	6✔
297	'messages_groups.arrival',	6✔
298	)	6✔
299	->first();	6✔
300
301	if (!$msg) {	6✔
UNCOV 302	return;	×
303	}
304
305	// Coordinates come from the DB, not user input — safe to embed in WKT.
306	$wkt = "POINT({$msg->lng} {$msg->lat})";	6✔
307	$srid = self::SRID;	6✔
308
309	DB::statement(	6✔
310	"INSERT INTO messages_spatial (msgid, point, groupid, msgtype, arrival)	6✔
311	VALUES (?, ST_GeomFromText('$wkt', $srid), ?, ?, ?)	6✔
312	ON DUPLICATE KEY UPDATE
313	point = ST_GeomFromText('$wkt', $srid),	6✔
314	groupid = ?,
315	msgtype = ?,
316	arrival = ?",	6✔
317	[$msg->id, $msg->groupid, $msg->msgtype, $msg->arrival,	6✔
318	$msg->groupid, $msg->msgtype, $msg->arrival]	6✔
319	);	6✔
320	}
321	}

Freegle / Iznik / 27958

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