SELECT coalsce((select action
from blocked
where mail='...'), 'OK') as action;
---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Rob Willett
>Sent: Wednesday, 16 May, 2018 02:22
>To: sqlite-users@mailinglists.sqlite.org
>Subject: [sqlite] Is this really the best way to do this?
>
>Hi,
>
>I'm experimenting with an email server, Mail In a Box. It's a free
>product and gives you a mail server in a box with SSL certificates,
>multiple domains and seems to work well.
>
>One thing it doesn't do is allow people to specify emails to block.
>
>It uses SQLite to store the underlying data necessary to power
>Postfix.
>Dr Richard Hipp, add another new application to your list :)
>
>I've worked out how Postfix calls SQLite (pretty easy), worked out
>what
>I want to do, and am trying to write a single line of SQL that does
>it.
>
>The need is that the SQL must generate a single string answer
>depending
>on the email address that is fed in through the query. There are no
>options here, it must return a value even if there is nothing in the
>table. It is not practical to add a table with every email address
>that
>returns OK.
>
>For this example the only strings it can return are 'OK' and
>'DISCARD',
>though the RFC allows other strings. If a query is done on an email
>and
>it is blocked then it must return DISCARD (or an action in the action
>column. If no email is in the table matching then it must return
>'OK'.
>
>As a piece of pseudo code this would ne
>
>function GetMailStatus(emailAddress)
>{
> IF emailAddress is present in blocked THEN
> return action associated with emailAddress -- Action is
>normally
>DISCARD
>
> return 'OK'
>}
>
>I've created the table
>
>CREATE TABLE blocked (id INTEGER PRIMARY KEY AUTOINCREMENT,
> email TEXT NOT NULL UNIQUE ,
> action TEXT NOT NULL DEFAULT 'DISCARD')
>
>I can insert values
>
>insert into blocked (email) values('rwillett.dr...@example.com')
>insert into blocked (email) values('rwillett+dr...@example.com')
>
>And this is the code that I have written that works but it looks poor
>to
>my untutored eyes
>
>select CASE
> WHEN EXISTS (select 1 from blocked where email =
>'rwillett.dr...@example.com')
> THEN (select action from blocked where email =
>'rwillett.dr...@example.com')
> ELSE 'OK'
> END
>
>
>In the Postfix query I'll replace
>
>email = 'rwillett.dr...@example.com'
>
>with email = '%s'
>
>so that the email address is passed in.
>
>My worry is that the query has two selects and 'feels' bad. It works
>but
>feels dirty...
>
>Is there a better way under SQLite?
>
>Any suggestions welcomed.
>
>Rob
>_______________________________________________
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
