diff options
| author | Dennis Fink | 2026-11-18 22:07:58 +0100 |
|---|---|---|
| committer | Dennis Fink | 2026-11-18 22:07:58 +0100 |
| commit | 45d3d3fde75173dfbf27e08aae8501842a9511dd (patch) | |
| tree | 96926d2ff0111e838620afdc73a66a8172bfa6c8 | |
| parent | c724c87eb5e23581732a275f1dd808da72932286 (diff) | |
| download | webmentions-ssg-45d3d3fde75173dfbf27e08aae8501842a9511dd.tar.gz webmentions-ssg-45d3d3fde75173dfbf27e08aae8501842a9511dd.zip | |
perf(sender): aggregate source statistics in SQL
Move sent Webmention counts from template iteration into a page-scoped
SQL aggregate so source listings no longer load every related delivery
row.
Include zero-target sources and expose sent, no-endpoint, failed, and
retrying counts directly to the template.
| -rw-r--r-- | tests/models/test_sources.py | 70 | ||||
| -rw-r--r-- | tests/views/test_sources.py | 31 | ||||
| -rw-r--r-- | webmentions_ssg/models/sources.py | 37 | ||||
| -rw-r--r-- | webmentions_ssg/templates/sent.html | 29 | ||||
| -rw-r--r-- | webmentions_ssg/views/sources.py | 17 |
5 files changed, 162 insertions, 22 deletions
diff --git a/tests/models/test_sources.py b/tests/models/test_sources.py index 10af3d6..100572f 100644 --- a/tests/models/test_sources.py +++ b/tests/models/test_sources.py @@ -98,6 +98,76 @@ def test_sent_webmention_pending_sql_matches_python(app: Flask) -> None: assert result.rowcount == 3 +def test_source_sent_webmention_statistics_sql(app: Flask) -> None: + """SQL counts every delivery state and includes sources with no targets.""" + now = datetime.now(UTC) + with app.app_context(): + source = SourceDocument( + path="stats/index.html", + url="https://site.example/stats/", + content_hash="0" * 32, + last_seen_at=now, + revised_at=now, + ) + empty = SourceDocument( + path="empty/index.html", + url="https://site.example/empty/", + content_hash="0" * 32, + last_seen_at=now, + revised_at=now, + ) + for index, (status, processed, attempts, retry_at) in enumerate( + [ + (SentWebmentionStatus.SENT, 1, 0, None), + (SentWebmentionStatus.SENT, 1, 0, None), + (SentWebmentionStatus.FAILED, None, 2, now), + (SentWebmentionStatus.NO_ENDPOINT, 1, 0, None), + (None, None, 1, None), + ] + ): + source.sent_webmentions.append( + SentWebmention( + target=f"https://target.example/{index}", + desired_revision=1, + processed_revision=processed, + status=status, + attempt_count=attempts, + next_retry_at=retry_at, + ) + ) + db.session.add_all([source, empty]) + db.session.commit() + + results = { + row.source_uuid: row + for row in db.session.execute( + SourceDocument.sent_webmention_statistics([source.uuid, empty.uuid]) + ) + } + assert set(results) == {source.uuid, empty.uuid} + assert ( + results[source.uuid].total, + results[source.uuid].sent, + results[source.uuid].failed, + results[source.uuid].retrying, + ) == (5, 2, 1, 1) + assert ( + results[empty.uuid].total, + results[empty.uuid].sent, + results[empty.uuid].failed, + results[empty.uuid].retrying, + ) == (0, 0, 0, 0) + + +def test_source_sent_webmention_statistics_is_scoped_to_page(app: Flask) -> None: + """An empty UUID selection must not aggregate unrelated sources.""" + with app.app_context(): + assert ( + list(db.session.execute(SourceDocument.sent_webmention_statistics([]))) + == [] + ) + + def test_source_repr() -> None: source = SourceDocument( path="example/index.html", diff --git a/tests/views/test_sources.py b/tests/views/test_sources.py index a41c18c..8f5672c 100644 --- a/tests/views/test_sources.py +++ b/tests/views/test_sources.py @@ -659,6 +659,37 @@ def test_resend_preserves_source_filter_context( send.assert_called_once_with(mention_id) +def test_sources_show_sql_statistics_with_zero_webmentions( + app: Flask, client: FlaskClient +) -> None: + log_in(app, client) + make_source( + app, + "mixed-stats", + statuses=( + SentWebmentionStatus.SENT, + SentWebmentionStatus.SENT, + SentWebmentionStatus.FAILED, + SentWebmentionStatus.NO_ENDPOINT, + ), + ) + make_source(app, "empty-stats", statuses=()) + + response = client.get("/sources", query_string={"sort": "path", "order": "asc"}) + assert response.status_code == HTTPStatus.OK + rows = BeautifulSoup(response.get_data(as_text=True), "html.parser").select( + "tbody tr" + ) + assert len(rows) == 2 + assert "0 Total" in rows[0].get_text(" ", strip=True) + assert " sent" not in rows[0].get_text(" ", strip=True) + mixed = rows[1].get_text(" ", strip=True) + assert "4 Total" in mixed + assert "2 sent" in mixed + assert "1 failed" in mixed + assert "awaiting retry" not in mixed + + def test_sources_empty_filter_result_keeps_rescan_modal( app: Flask, client: FlaskClient ) -> None: diff --git a/webmentions_ssg/models/sources.py b/webmentions_ssg/models/sources.py index f0fd618..be56e24 100644 --- a/webmentions_ssg/models/sources.py +++ b/webmentions_ssg/models/sources.py @@ -4,6 +4,7 @@ """Source documents and outgoing Webmention state.""" +from collections.abc import Collection from datetime import datetime from enum import StrEnum from uuid import UUID, uuid7 @@ -68,6 +69,42 @@ class SourceDocument(Base): """Represent the source by its public URL.""" return f"<SourceDocument {self.url!r}>" + @classmethod + def sent_webmention_statistics( + cls, source_uuids: Collection[UUID] + ) -> sa.Select[UUID, int, int, int, int, int]: + """Aggregate delivery counts for a page of sources in one SQL query. + + The outer join includes sources without Webmentions, with zero counts. + Retrying is independent of delivery status, matching the list badges. + """ + retrying = sa.and_( + SentWebmention.awaiting_processing, + SentWebmention.attempt_count > 0, + SentWebmention.next_retry_at.is_not(None), + ) + return ( + sa.select( + cls.uuid.label("source_uuid"), + sa.func.count(SentWebmention.uuid).label("total"), + sa.func.count( + sa.case((SentWebmention.status == SentWebmentionStatus.SENT, 1)) + ).label("sent"), + sa.func.count( + sa.case( + (SentWebmention.status == SentWebmentionStatus.NO_ENDPOINT, 1) + ) + ).label("no_endpoint"), + sa.func.count( + sa.case((SentWebmention.status == SentWebmentionStatus.FAILED, 1)) + ).label("failed"), + sa.func.count(sa.case((retrying, 1))).label("retrying"), + ) + .outerjoin(SentWebmention, SentWebmention.source_uuid == cls.uuid) + .where(cls.uuid.in_(source_uuids)) + .group_by(cls.uuid) + ) + @property def created_at(self) -> datetime: """Return the creation time encoded in the source UUID.""" diff --git a/webmentions_ssg/templates/sent.html b/webmentions_ssg/templates/sent.html index b613f4b..46296b8 100644 --- a/webmentions_ssg/templates/sent.html +++ b/webmentions_ssg/templates/sent.html @@ -84,17 +84,7 @@ SPDX-License-Identifier: BSD-3-Clause </thead> <tbody> {% for source in sources %} - {% set statistics = namespace(sent=0, failed=0, retrying=0) %} - {% for webmention in source.sent_webmentions %} - {% if webmention.status == "sent" %} - {% set statistics.sent = statistics.sent + 1 %} - {% elif webmention.status == "failed" %} - {% set statistics.failed = statistics.failed + 1 %} - {% endif %} - {% if webmention.awaiting_processing and webmention.attempt_count > 0 and webmention.next_retry_at is not none %} - {% set statistics.retrying = statistics.retrying + 1 %} - {% endif %} - {% endfor %} + {% set counts = statistics[source.uuid] %} {% if source.deleted_at is none %} {% set row_class = "table-success" %} {% set badge_class = "text-bg-success" %} @@ -115,15 +105,18 @@ SPDX-License-Identifier: BSD-3-Clause <a href="{{ source.url }}">{{ source.url }}</a> </td> <td> - <span class="badge text-bg-info">{{ source.sent_webmentions | length }} Total</span> - {% if statistics.sent %} - <span class="badge text-bg-success">{{ statistics.sent }} sent</span> + <span class="badge text-bg-info">{{ counts.total }} Total</span> + {% if counts.sent %} + <span class="badge text-bg-success">{{ counts.sent }} sent</span> {% endif %} - {% if statistics.failed %} - <span class="badge text-bg-danger">{{ statistics.failed }} failed</span> + {% if counts.no_endpoint %} + <span class="badge text-bg-secondary">{{ counts.no_endpoint }} no endpoint</span> {% endif %} - {% if statistics.retrying %} - <span class="badge text-bg-warning">{{ statistics.retrying }} awaiting retry</span> + {% if counts.failed %} + <span class="badge text-bg-danger">{{ counts.failed }} failed</span> + {% endif %} + {% if counts.retrying %} + <span class="badge text-bg-warning">{{ counts.retrying }} awaiting retry</span> {% endif %} </td> <td class="d-none d-md-table-cell">{{ source.revision }}</td> diff --git a/webmentions_ssg/views/sources.py b/webmentions_ssg/views/sources.py index a3dbb45..22cabb6 100644 --- a/webmentions_ssg/views/sources.py +++ b/webmentions_ssg/views/sources.py @@ -13,7 +13,6 @@ import sqlalchemy as sa from flask import abort, current_app, flash, redirect, render_template, request, url_for from flask.typing import ResponseReturnValue from flask_login import login_required -from sqlalchemy.orm import selectinload from ..extensions import db from ..forms.admin import ConfirmActionForm @@ -68,9 +67,7 @@ def source_list() -> ResponseReturnValue: """Display source documents with filters for outgoing delivery problems.""" filter_form = source_filter_form() filters = source_filter_args(filter_form) - query = sa.select(SourceDocument).options( - selectinload(SourceDocument.sent_webmentions) - ) + query = sa.select(SourceDocument) # EXISTS avoids duplicate source rows when several Webmentions match. sent = sa.select(SentWebmention.uuid).where( @@ -144,6 +141,17 @@ def source_list() -> ResponseReturnValue: max_per_page=current_app.config["SOURCES_MAX_PAGE_SIZE"], ) + statistics = {} + if sources.items: + statistics = { + row.source_uuid: row + for row in db.session.execute( + SourceDocument.sent_webmention_statistics( + [source.uuid for source in sources.items] + ) + ) + } + sort_urls = {} for key in sort_columns: if key == sort_key: @@ -157,6 +165,7 @@ def source_list() -> ResponseReturnValue: "sent.html", title="Sent Webmentions", sources=sources, + statistics=statistics, filters=filters, sort_key=sort_key, sort_order=sort_order, |
