Re: [sqlite] Is this really the best way to do this?

[Keith Medcalf]

There is no difference.  The problem statement was:

  Look up the mail address (which is constrained unique) and return the defined 
action (which is constrained NOT NULL).
  If there is no "action" (which can be only because the mail does not exist) 
return OK.

Unless, of course, the initial problem was misstated.

---
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 Dominique Devienne
>Sent: Thursday, 17 May, 2018 02:41
>To: General Discussion of SQLite Database
>Subject: Re: [sqlite] Is this really the best way to do this?
>
>On Wed, May 16, 2018 at 8:33 PM Keith Medcalf 
>wrote:
>
>>
>> SELECT coalsce((select action
>>   from blocked
>>  where mail='...'), 'OK') as action;
>>
>
>Nice one Keith. Works (see below), but I find it a bit intuitive,
>since returning no row is somehow not intuitively equivalent (in my
>mind at
>least)
>to a scalar row with a NULL value. Your query makes no distinction
>between
>these two cases.
>
>PS: Thanks Ryan, that's what I thought too, and would have been my
>work-around too.
>
>C:\Users\ddevienne>sqlite3
>SQLite version 3.20.1 2017-08-24 16:21:36
>Enter ".help" for usage hints.
>Connected to a transient in-memory database.
>Use ".open FILENAME" to reopen on a persistent database.
>sqlite> create table blocked (mail text primary key, action text);
>sqlite> insert into blocked values ('f...@acme.com', 'DISCARD');
>sqlite> select coalesce((select action from blocked where
>mail='f...@acm.com'),
>'OK') as action;
>OK
>sqlite> select coalesce((select action from blocked where
>mail='f...@acme.com'),
>'OK') as action;
>DISCARD
>sqlite> select action from blocked where mail='f...@acm.com';
>sqlite> select action from blocked where mail='f...@acme.com';
>DISCARD
>sqlite> insert into blocked values ('b...@acme.com', NULL);
>sqlite> select coalesce((select action from blocked where
>mail='b...@acme.com'),
>'OK') as action;
>OK
>sqlite> select action from blocked where mail='b...@acme.com';
>
>sqlite>
>___
>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

Re: [sqlite] Is this really the best way to do this?

[Cezary H. Noweta]

Hello,

On 2018-05-17 10:40, Dominique Devienne wrote:

On Wed, May 16, 2018 at 8:33 PM Keith Medcalf  wrote:



SELECT coalsce((select action
   from blocked
  where mail='...'), 'OK') as action;



Nice one Keith. Works (see below), but I find it a bit intuitive,
since returning no row is somehow not intuitively equivalent (in my mind at
least)
to a scalar row with a NULL value. Your query makes no distinction between
these two cases.


Quite justly -- let us not introduce multi levels of non--existence (a 
source of many bugs):


(1) semi non--existence / a value ``NULL'' / a person saying ``Hello, I 
do not exist'';


(2) virtual non--existence / a value ``UNDEFINED'' / a person not 
existing virtually, because it is silenced (though breathing);


(3) literal non--existence / a value not existing literally / a person 
not existing literally;


A NULL column (blocking) ``action'' in a table ``blocked'' is an error 
and should be avoided -- it means ``you are blocked, but I do not know 
how''. If someone wants a (lately bound) default action, let the name be 
``DEFAULT'' -- not NULL.


-- best regards

Cezary H. Noweta

P.S. I know it is hard to return to a life after a programming in some 
very flexible lang, in which every even simplest task can be done in a 
trillion ways :)

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is this really the best way to do this?

[Dominique Devienne]

On Wed, May 16, 2018 at 8:33 PM Keith Medcalf  wrote:

>
> SELECT coalsce((select action
>   from blocked
>  where mail='...'), 'OK') as action;
>

Nice one Keith. Works (see below), but I find it a bit intuitive,
since returning no row is somehow not intuitively equivalent (in my mind at
least)
to a scalar row with a NULL value. Your query makes no distinction between
these two cases.

PS: Thanks Ryan, that's what I thought too, and would have been my
work-around too.

C:\Users\ddevienne>sqlite3
SQLite version 3.20.1 2017-08-24 16:21:36
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table blocked (mail text primary key, action text);
sqlite> insert into blocked values ('f...@acme.com', 'DISCARD');
sqlite> select coalesce((select action from blocked where mail='f...@acm.com'),
'OK') as action;
OK
sqlite> select coalesce((select action from blocked where mail='f...@acme.com'),
'OK') as action;
DISCARD
sqlite> select action from blocked where mail='f...@acm.com';
sqlite> select action from blocked where mail='f...@acme.com';
DISCARD
sqlite> insert into blocked values ('b...@acme.com', NULL);
sqlite> select coalesce((select action from blocked where mail='b...@acme.com'),
'OK') as action;
OK
sqlite> select action from blocked where mail='b...@acme.com';

sqlite>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is this really the best way to do this?

[Keith Medcalf]

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

Re: [sqlite] Is this really the best way to do this?

[R Smith]

On 2018/05/16 6:21 PM, Dominique Devienne wrote:

On Wed, May 16, 2018 at 6:12 PM Clemens Ladisch  wrote:


Stephen Chrzanowski wrote:

On Wed, May 16, 2018 at 6:25 AM, Clemens Ladisch 

wrote:

SELECT action FROM blocked WHERE email = ?
UNION ALL
SELECT 'OK'
LIMIT 1;

Out of curiosity, where's the guarantee that the OK won't be displayed if
an email is found?

The "LIMIT 1" makes the DB stop after the first returned row.
It applies to the entire compound query, not only to the second SELECT.


But does SQL guarantee the first result-set (above the UNION ALL)
must be before the second one (below UNION ALL)? --DD


It always does for a UNION ALL query (not necessarily for UNION only) as 
long as there is no ORDER BY clause, BUT, this is not guaranteed 
behaviour, just happy accident.


To quote the COMPOUND SELECT documentation:
"When three or more simple SELECTs are connected into a compound SELECT, 
they group from left to right. In other words, if "A", "B" and "C" are 
all simple SELECT statements, (A op B op C) is processed as ((A op B) op 
C)."


But then the Order-By documentation states:
"If a SELECT statement that returns more than one row does not have an 
ORDER BY clause, the order in which the rows are returned is undefined. 
Or, if a SELECT statement does have an ORDER BY clause, then the list of 
expressions attached to the ORDER BY determine the order in which rows 
are returned to the user."


The way I do these kinds of queries is force the order, like so:


SELECT 1 AS Idx, action FROM blocked WHERE email = ?
UNION ALL
SELECT 2, 'OK'
ORDER BY 1 LIMIT 1;


This is guaranteed to work always.



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is this really the best way to do this?

[Dominique Devienne]

On Wed, May 16, 2018 at 6:12 PM Clemens Ladisch  wrote:

> Stephen Chrzanowski wrote:
> > On Wed, May 16, 2018 at 6:25 AM, Clemens Ladisch 
> wrote:
> >> SELECT action FROM blocked WHERE email = ?
> >> UNION ALL
> >> SELECT 'OK'
> >> LIMIT 1;
> >
> > Out of curiosity, where's the guarantee that the OK won't be displayed if
> > an email is found?
>
> The "LIMIT 1" makes the DB stop after the first returned row.
> It applies to the entire compound query, not only to the second SELECT.
>

But does SQL guarantee the first result-set (above the UNION ALL)
must be before the second one (below UNION ALL)? --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is this really the best way to do this?

[Clemens Ladisch]

Stephen Chrzanowski wrote:
> On Wed, May 16, 2018 at 6:25 AM, Clemens Ladisch  wrote:
>> SELECT action FROM blocked WHERE email = ?
>> UNION ALL
>> SELECT 'OK'
>> LIMIT 1;
>
> Out of curiosity, where's the guarantee that the OK won't be displayed if
> an email is found?

The "LIMIT 1" makes the DB stop after the first returned row.
It applies to the entire compound query, not only to the second SELECT.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is this really the best way to do this?

[Rob Willett]

Clemens, Paul S, Abroży,

Thank you for taking the time to reply. I'll look at each of them and 
see what makes the most sense to me.


It's always good to ask the people here as there is a wealth of 
experience.


As suggestions popped in, I kept thinking go the unix competition which 
was to list all the ways to accomplish printing something out, there's 
always another to do it. I will learn here as well.


Many thanks to everybody,

Rob

On 16 May 2018, at 11:25, Clemens Ladisch wrote:


Rob Willett wrote:

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


SELECT action FROM blocked WHERE email = ?
UNION ALL
SELECT 'OK'
LIMIT 1;


Regards,
Clemens
___
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

Re: [sqlite] Is this really the best way to do this?

[Stephen Chrzanowski]

Out of curiosity, where's the guarantee that the OK won't be displayed if
an email is found?  Do all unions show up in the return result based on
where in the query they're called?

On Wed, May 16, 2018 at 6:25 AM, Clemens Ladisch  wrote:

>
> SELECT action FROM blocked WHERE email = ?
> UNION ALL
> SELECT 'OK'
> LIMIT 1;
>
>
> Regards,
> Clemens
> ___
> 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

Re: [sqlite] Is this really the best way to do this?

[Clemens Ladisch]

Rob Willett wrote:
> 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

SELECT action FROM blocked WHERE email = ?
UNION ALL
SELECT 'OK'
LIMIT 1;


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is this really the best way to do this?

[Paul Sanderson]

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 

On 16 May 2018 at 09:22, Rob Willett  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

Re: [sqlite] Is this really the best way to do this?

[Paul Sanderson]

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 

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 :
> > 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

Re: [sqlite] Is this really the best way to do this?

[Abroży Nieprzełoży]

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 :
> 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] Is this really the best way to do this?

[Rob Willett]

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