or more in tune with the default value in the schema
SELECT CASE count(*) WHEN 0 THEN 'Ok' ELSE action 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 09:22, Rob Willett <rob.sql...@robertwillett.com> wrote: > 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
