How about something like SELECT CASE count(*) WHEN 0 THEN 'Discard' ELSE 'Ok' END FROM blocked WHERE email = 'rwillett.dr...@example.com';
Paul www.sandersonforensics.com SQLite Forensics Book <https://www.amazon.co.uk/dp/ASIN/1980293074> On 16 May 2018 at 10:35, Abroży Nieprzełoży < abrozynieprzelozy314...@gmail.com> wrote: > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
