I would make something like this:
CREATE TABLE blocked(
mail TEXT PRIMARY KEY,
action TEXT NOT NULL DEFAULT 'DISCARD'
) WITHOUT ROWID;
INSERT INTO blocked(mail) VALUES('badm...@example.com');
SELECT coalesce(action, 'OK') AS action
FROM (SELECT 'goodm...@example.com' AS mail)
LEFT JOIN blocked USING(mail);
SELECT coalesce(action, 'OK') AS action
FROM (SELECT 'badm...@example.com' AS mail)
LEFT JOIN blocked USING(mail);
2018-05-16 10:22 GMT+02:00, Rob Willett <rob.sql...@robertwillett.com>:
> 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
