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
