~sircmpwn/lists.sr.ht

Fix routing with slashes in message ID
Add unique constraint on (list_id, message_id)
patches: assert patch-email relationship invariant
settings-delete.html: fix typo in url
Don't strip timezone information on import

datetime.replace discards the previous timezone. Instead, convert to UTC.

This fixes ordering of messages in a thread when each has a different
timezone.
archive.html: only include form on patchsets page
Allow list slugs to break

Ref: ~sircmpwn/sr.ht#245
Add constraints to subscription table
process: eat SMTP errors

These are almost always caused by the remote side.
Bail on send_error_for() if mail has no From:

If mail["From"] is None, then parseaddr() returns ("", ""),
which would try to send with a "RCPT TO:<>" command, which Postfix does
not like:
-- >8 --
Sep 02 00:52:17 tarta postfix/submission/smtpd[26766]: connect from tarta[192.168.1.250]
Sep 02 00:52:17 tarta postfix/submission/smtpd[26766]: warning: Illegal address syntax from tarta[192.168.1.250] in RCPT command: <>
Sep 02 00:52:17 tarta postfix/submission/smtpd[26766]: lost connection after RSET from tarta[192.168.1.250]
Sep 02 00:52:17 tarta postfix/submission/smtpd[26766]: disconnect from tarta[192.168.1.250] ehlo=2 starttls=1 auth=1 mail=1 rcpt=0/1 rset=1 commands=6/7
-- >8 --

autosub is only used at the top, no reason to get it down there
process: s/Access/ListAccess/g

Mistake from an earlier commit
.builds/alpine.yml: increase specificity of sed
process: check ACLs before (dis)allowing subscribe
process: catch & email exceptions
Make mailing list deletion asyncronous

It can take a while to clear out all of the cascades.
Fix error in reply to stripping
Handle In-Reply-To with comment
add database stamp for core.sr.ht
Prevent before_update from changing List.updated in archives.subscribe()

Ref: ~sircmpwn/lists.sr.ht#45
Handle from/cc/to:[~]username in searches

Adding
-- >8 --
from sqlalchemy.dialects import postgresql

def apply_terms(query, terms, default_fn, key_fns={}, fallback_fn=None):
    # ...
    qq = query.filter(filters)
    with open("/tmp/shirt", "a") as f:
        print("", file=f)
        print(qq.statement.compile(dialect=postgresql.dialect(), compile_kwargs={"literal_binds": True}), file=f)
    return qq
-- >8 --
to srht.search writes out this query for "from:nab cc:nabijaczleweli2":
-- >8 --
SELECT email.id, email.created, email.updated, email.subject, email.message_id, email.in_reply_to, email.headers, email.body, email.envelope, email.is_patch, email.is_request_pull, email.message_date, email.list_id, email.parent_id, email.thread_id, email.nreplies, email.nparticipants, email.sender_id, email.patch_index, email.patch_count, email.patch_version, email.patch_prefix, email.patch_subject, email.superseded_by_id, email.patchset_id
FROM email
WHERE email.list_id = 1 AND CAST(email.headers -> 'From' AS VARCHAR) ILIKE coalesce((SELECT '%%' || "user".email || '%%' AS anon_1
FROM "user"
WHERE "user".username = 'nab'), '%%nab%%') AND CAST(email.headers -> 'Cc' AS VARCHAR) ILIKE coalesce((SELECT '%%' || "user".email || '%%' AS anon_2
FROM "user"
WHERE "user".username = 'nabijaczleweli2'), '%%nabijaczleweli2%%') ORDER BY email.updated DESC;
-- >8 --

EXPLAIN ANALYZEing it yields:
-- >8 --
                                                                                                               QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=27.62..27.63 rows=1 width=1782) (actual time=0.259..0.259 rows=2 loops=1)
   Sort Key: email.updated DESC
   Sort Method: quicksort  Memory: 28kB
   InitPlan 1 (returns $0)
     ->  Index Scan using ix_user_username on "user"  (cost=0.14..8.16 rows=1 width=32) (actual time=0.011..0.011 rows=0 loops=1)
           Index Cond: ((username)::text = 'nab'::text)
   InitPlan 2 (returns $1)
     ->  Index Scan using ix_user_username on "user" user_1  (cost=0.14..8.16 rows=1 width=32) (actual time=0.033..0.034 rows=1 loops=1)
           Index Cond: ((username)::text = 'nabijaczleweli'::text)
   ->  Seq Scan on email  (cost=0.00..11.30 rows=1 width=1782) (actual time=0.154..0.203 rows=2 loops=1)
         Filter: ((list_id = 1) AND ((((headers -> 'From'::text))::character varying)::text ~~* COALESCE($0, '%%nab%%'::text)) AND ((((headers -> 'Cc'::text))::character varying)::text ~~* COALESCE($1, '%%nabijaczleweli%%'::text)))
         Rows Removed by Filter: 4
 Planning Time: 1.136 ms
 Execution Time: 0.402 ms
(14 rows)
-- >8 --

For comparison, the equivalent query a it would be before, i.e.
-- >8 --
SELECT email.id, email.created, email.updated, email.subject, email.message_id, email.in_reply_to, email.headers, email.body, email.envelope, email.is_patch, email.is_request_pull, email.message_date, email.list_id, email.parent_id, email.thread_id, email.nreplies, email.nparticipants, email.sender_id, email.patch_index, email.patch_count, email.patch_version, email.patch_prefix, email.patch_subject, email.superseded_by_id, email.patchset_id
FROM email
WHERE email.list_id = 1 AND CAST(email.headers -> 'From' AS VARCHAR) ILIKE '%%nab%%' AND CAST(email.headers -> 'Cc' AS VARCHAR) ILIKE '%%nabijaczleweli%%' ORDER BY email.updated DESC;
-- >8 --
Yields
-- >8 --
                                                                                                 QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=11.31..11.32 rows=1 width=1782) (actual time=0.367..0.368 rows=2 loops=1)
   Sort Key: updated DESC
   Sort Method: quicksort  Memory: 28kB
   ->  Seq Scan on email  (cost=0.00..11.30 rows=1 width=1782) (actual time=0.146..0.225 rows=2 loops=1)
         Filter: ((list_id = 1) AND ((((headers -> 'From'::text))::character varying)::text ~~* '%%nab%%'::text) AND ((((headers -> 'Cc'::text))::character varying)::text ~~* '%%nabijaczleweli%%'::text))
         Rows Removed by Filter: 4
 Planning Time: 1.495 ms
 Execution Time: 0.543 ms
(8 rows)
-- >8 --
Which is worse, though my tiny dataset might not be entirely
representative.

For an OUTER-JOIN-based approach which didn't work for multi-user
searches see this msgid:
  <20200802235726.5xdgdekquq7rokkk@tarta.local.nabijaczleweli.xyz>

Also avoid double-%ing non-me-non-username values
Next