Login | Register For Free | Help
Search for: (Advanced)

Mailing List Archive: exim: dev

sqlite lookup support.

 

 

exim dev RSS feed   Index | Next | Previous | View Threaded


dwmw2 at infradead

Jul 4, 2005, 8:15 AM

Post #1 of 18 (2035 views)
Permalink
sqlite lookup support.

I really shouldn't be using lsearch and ${run sh -c "echo blah >> dbfile"}
for my greylisting. Here's sqlite3 support.

--- exim-4.52/src/lookups/sqlite.c~ 2005-07-04 15:22:00.000000000 +0100
+++ exim-4.52/src/lookups/sqlite.c 2005-07-04 15:16:24.000000000 +0100
@@ -0,0 +1,103 @@
+/* $Cambridge$ */
+
+/*************************************************
+* Exim - an Internet mail transport agent *
+*************************************************/
+
+/* Copyright (c) University of Cambridge 1995 - 2005 */
+/* See the file NOTICE for conditions of use and distribution. */
+
+#include "../exim.h"
+#include "lf_functions.h"
+#include "sqlite.h"
+
+#ifndef LOOKUP_SQLITE
+static void dummy(int x) { dummy(x-1); }
+#else
+#include <sqlite3.h>
+
+/*************************************************
+* Open entry point *
+*************************************************/
+
+/* See local README for interface description. */
+
+void *
+sqlite_open(uschar *filename, uschar **errmsg)
+{
+sqlite3 *db = NULL;
+int ret;
+
+ret = sqlite3_open((char *)filename, &db);
+if (ret) {
+ *errmsg = (void *)sqlite3_errmsg(db);
+ debug_printf("Error opening database: %s\n", *errmsg);
+}
+
+return db;
+}
+
+
+/*************************************************
+* Find entry point *
+*************************************************/
+
+/* See local README for interface description. */
+
+struct strbuf {
+ uschar *string;
+ int size;
+ int len;
+};
+
+static int sqlite_callback(void *arg, int argc, char **argv, char **azColName)
+{
+struct strbuf *res = arg;
+int i;
+
+/* For second and subsequent results, insert \n */
+if (res->string)
+ res->string = string_cat(res->string, &res->size, &res->len, US"\n", 1);
+
+if (argc > 1)
+ {
+ /* For multiple fields, include the field name too */
+ for (i=0; i<argc-1; i++)
+ res->string = string_append(res->string, &res->size, &res->len, 4,
+ azColName[i], US"=", argv[i]?argv[i]:"<NULL>", " ");
+ res->string = string_append(res->string, &res->size, &res->len, 3,
+ azColName[i], US"=", argv[i]?argv[i]:"<NULL>");
+ }
+else
+ res->string = string_append(res->string, &res->size, &res->len, 1, argv[0]?argv[0]:"<NULL>");
+
+return 0;
+}
+
+int
+sqlite_find(void *handle, uschar *filename, uschar *query, int length,
+ uschar **result, uschar **errmsg, BOOL *do_cache)
+{
+int ret;
+struct strbuf res = { NULL, 0, 0 };
+
+ret = sqlite3_exec(handle, (char *)query, sqlite_callback, &res, (char **)errmsg);
+if (ret != SQLITE_OK)
+ {
+ debug_printf("sqlite3_exec failed: %s\n", *errmsg);
+ return FAIL;
+ }
+if (!res.string)
+ *do_cache = FALSE;
+
+*result = res.string;
+return OK;
+}
+
+void sqlite_close(void *handle)
+{
+sqlite3_close(handle);
+}
+#endif /* LOOKUP_SQLITE */
+
+/* End of lookups/sqlite.c */
--- exim-4.52/src/lookups/sqlite.h~ 2005-07-04 15:22:02.000000000 +0100
+++ exim-4.52/src/lookups/sqlite.h 2005-07-04 15:12:12.000000000 +0100
@@ -0,0 +1,17 @@
+/* $Cambridge$ */
+
+/*************************************************
+* Exim - an Internet mail transport agent *
+*************************************************/
+
+/* Copyright (c) University of Cambridge 1995 - 2005 */
+/* See the file NOTICE for conditions of use and distribution. */
+
+/* Header for the sqlite lookup */
+
+extern void *sqlite_open(uschar *, uschar **);
+extern int sqlite_find(void *, uschar *, uschar *, int, uschar **, uschar **,
+ BOOL *);
+extern void sqlite_close(void *);
+
+/* End of lookups/sqlite.h */
--- exim-4.52/src/lookups/Makefile~ 2005-07-01 12:09:15.000000000 +0100
+++ exim-4.52/src/lookups/Makefile 2005-07-04 15:15:03.000000000 +0100
@@ -6,8 +6,8 @@
# defined, dummy modules get compiled.

OBJ = cdb.o dbmdb.o dnsdb.o dsearch.o ibase.o ldap.o lsearch.o mysql.o nis.o \
- nisplus.o oracle.o passwd.o pgsql.o spf.o testdb.o whoson.o lf_check_file.o \
- lf_quote.o
+ nisplus.o oracle.o passwd.o pgsql.o spf.o sqlite.o testdb.o whoson.o \
+ lf_check_file.o lf_quote.o

lookups.a: $(OBJ)
@/bin/rm -f lookups.a
@@ -37,6 +37,7 @@ oracle.o: $(HDRS) oracle.c ora
passwd.o: $(HDRS) passwd.c passwd.h
pgsql.o: $(HDRS) pgsql.c pgsql.h
spf.o: $(HDRS) spf.c spf.h
+sqlite.o: $(HDRS) sqlite.c sqlite.h
testdb.o: $(HDRS) testdb.c testdb.h
whoson.o: $(HDRS) whoson.c whoson.h

--- exim-4.52/src/EDITME~ 2005-07-01 16:29:09.000000000 +0100
+++ exim-4.52/src/EDITME 2005-07-04 15:14:17.000000000 +0100
@@ -266,6 +266,7 @@ LOOKUP_LSEARCH=yes
# LOOKUP_ORACLE=yes
# LOOKUP_PASSWD=yes
# LOOKUP_PGSQL=yes
+# LOOKUP_SQLITE=yes
# LOOKUP_WHOSON=yes

# These two settings are obsolete; all three lookups are compiled when
--- exim-4.52/src/drtables.c~ 2005-07-01 12:09:15.000000000 +0100
+++ exim-4.52/src/drtables.c 2005-07-04 15:13:57.000000000 +0100
@@ -93,6 +93,10 @@ be NULL for methods that don't need them
#include "lookups/spf.h"
#endif

+#ifdef LOOKUP_SQLITE
+#include "lookups/sqlite.h"
+#endif
+
#ifdef LOOKUP_TESTDB
#include "lookups/testdb.h"
#endif
@@ -458,6 +462,23 @@ Shares many functions with lsearch. */
#endif
},

+/* sqlite lookup */
+
+ {
+ US"sqlite", /* lookup name */
+ lookup_absfile, /* not query style */
+#ifdef LOOKUP_SQLITE
+ sqlite_open, /* open function */
+ NULL, /* no check function */
+ sqlite_find, /* find function */
+ sqlite_close, /* close function */
+ NULL, /* no tidy function */
+ NULL /* no quoting function */
+#else
+ NULL, NULL, NULL, NULL, NULL, NULL /* lookup not present */
+#endif
+ },
+
/* Testdb lookup is for testing Exim, not useful for normal running.
For that reason, we omit the entry entirely when not building it into
the binary, so that attempts to use it give "unknown lookup type" instead
--- exim-4.52/scripts/MakeLinks~ 2005-07-01 12:09:15.000000000 +0100
+++ exim-4.52/scripts/MakeLinks 2005-07-04 15:27:16.000000000 +0100
@@ -84,6 +84,8 @@ ln -s ../../src/lookups/pgsql.h
ln -s ../../src/lookups/pgsql.c pgsql.c
ln -s ../../src/lookups/spf.h spf.h
ln -s ../../src/lookups/spf.c spf.c
+ln -s ../../src/lookups/sqlite.h sqlite.h
+ln -s ../../src/lookups/sqlite.c sqlite.c
ln -s ../../src/lookups/testdb.h testdb.h
ln -s ../../src/lookups/testdb.c testdb.c
ln -s ../../src/lookups/whoson.h whoson.h
--- exim-4.52/src/exim.c~ 2005-07-01 12:09:15.000000000 +0100
+++ exim-4.52/src/exim.c 2005-07-04 15:27:39.000000000 +0100
@@ -918,6 +918,9 @@ fprintf(f, "Lookups:");
#ifdef LOOKUP_PGSQL
fprintf(f, " pgsql");
#endif
+#ifdef LOOKUP_SQLITE
+ fprintf(f, " sqlite");
+#endif
#ifdef LOOKUP_TESTDB
fprintf(f, " testdb");
#endif
--- exim-4.52/src/config.h.defaults~ 2005-07-04 15:25:25.000000000 +0100
+++ exim-4.52/src/config.h.defaults 2005-07-04 15:29:06.000000000 +0100
@@ -80,6 +80,7 @@ in config.h unless some value is defined
#define LOOKUP_ORACLE
#define LOOKUP_PASSWD
#define LOOKUP_PGSQL
+#define LOOKUP_SQLITE
#define LOOKUP_TESTDB
#define LOOKUP_WHOSON
#define LOOKUP_WILDLSEARCH


--
dwmw2


ph10 at cus

Jul 5, 2005, 1:22 AM

Post #2 of 18 (1987 views)
Permalink
Re: sqlite lookup support. [In reply to]

On Mon, 4 Jul 2005, David Woodhouse wrote:

> I really shouldn't be using lsearch and ${run sh -c "echo blah >> dbfile"}
> for my greylisting. Here's sqlite3 support.

Thank you. However, I'm afraid I'm unlikely be able to look at it until
August. Next week is the Exim course in Cambridge and then I'm off to
Bhutan for the SANOG meeting (and a few days touristing). And this week?
Well, I'm preparing for all of that...

--
Philip Hazel University of Cambridge Computing Service,
ph10 [at] cus Cambridge, England. Phone: +44 1223 334714.
Get the Exim 4 book: http://www.uit.co.uk/exim-book


jh at plonk

Jul 5, 2005, 1:44 AM

Post #3 of 18 (2015 views)
Permalink
Re: sqlite lookup support. [In reply to]

David Woodhouse wrote:

> I really shouldn't be using lsearch and ${run sh -c "echo blah >> dbfile"}
> for my greylisting. Here's sqlite3 support.

very nice, thanks!

> + NULL /* no quoting function */

Shouldn't the single quote character ' be quoted, at least?


dot at dotat

Jul 5, 2005, 2:25 AM

Post #4 of 18 (2013 views)
Permalink
Re: sqlite lookup support. [In reply to]

On Tue, 5 Jul 2005, Jakob Hirsch wrote:
> David Woodhouse wrote:
>
> > I really shouldn't be using lsearch and ${run sh -c "echo blah >> dbfile"}
> > for my greylisting. Here's sqlite3 support.
>
> very nice, thanks!

Yes, but it could do with documentation :-)

> > + NULL /* no quoting function */
>
> Shouldn't the single quote character ' be quoted, at least?

I suspect it could do with a SQL-quoting function similar to those for
MySQL/Oracle/Postgres. I wonder why those functions are different...

Tony.
--
<fanf [at] exim> <dot [at] dotat> http://dotat.at/ ${sg{\N${sg{\
N\}{([^N]*)(.)(.)(.*)}{\$1\$3\$2\$1\$3\n\$2\$3\$4\$3\n\$3\$2\$4}}\
\N}{([^N]*)(.)(.)(.*)}{\$1\$3\$2\$1\$3\n\$2\$3\$4\$3\n\$3\$2\$4}}


dwmw2 at infradead

Jul 5, 2005, 2:36 AM

Post #5 of 18 (1990 views)
Permalink
Re: sqlite lookup support. [In reply to]

On Tue, 2005-07-05 at 10:25 +0100, Tony Finch wrote:
> Yes, but it could do with documentation :-)

I thought about that but didn't know what form to provide documentation
patches in. I sort of tuned out of the docs discussion when it started
to mention XML.

> > > + NULL /* no quoting function */
> >
> > Shouldn't the single quote character ' be quoted, at least?
>
> I suspect it could do with a SQL-quoting function similar to those for
> MySQL/Oracle/Postgres. I wonder why those functions are different...

Yeah. It could probably also do with being marked as a query-style
lookup, but you can't use filenames with query-style lookups at the
moment; I suspect we should have a separate flag to indicate whether a
filename is required or not.

--
dwmw2


dot at dotat

Jul 5, 2005, 2:48 AM

Post #6 of 18 (2009 views)
Permalink
Re: sqlite lookup support. [In reply to]

On Tue, 5 Jul 2005, David Woodhouse wrote:
>
> I thought about that but didn't know what form to provide documentation
> patches in. I sort of tuned out of the docs discussion when it started
> to mention XML.

:-)

It's sufficient to provide a patch for the NewStuff file which is plain
text.

> Yeah. It could probably also do with being marked as a query-style
> lookup, but you can't use filenames with query-style lookups at the
> moment; I suspect we should have a separate flag to indicate whether a
> filename is required or not.

Yes, it's a bit odd that it's a hybrid between a query-styl and single-key
lookup. Judging from the patch this is a documentation problem rather than
an implementation problem, but I only skimmed it.

Tony.
--
<fanf [at] exim> <dot [at] dotat> http://dotat.at/ ${sg{\N${sg{\
N\}{([^N]*)(.)(.)(.*)}{\$1\$3\$2\$1\$3\n\$2\$3\$4\$3\n\$3\$2\$4}}\
\N}{([^N]*)(.)(.)(.*)}{\$1\$3\$2\$1\$3\n\$2\$3\$4\$3\n\$3\$2\$4}}


ph10 at cus

Jul 5, 2005, 3:00 AM

Post #7 of 18 (2007 views)
Permalink
Re: sqlite lookup support. [In reply to]

On Tue, 5 Jul 2005, Tony Finch wrote:

> I suspect it could do with a SQL-quoting function similar to those for
> MySQL/Oracle/Postgres. I wonder why those functions are different...

Because the <expletive> quoting rules are different!

--
Philip Hazel University of Cambridge Computing Service,
ph10 [at] cus Cambridge, England. Phone: +44 1223 334714.
Get the Exim 4 book: http://www.uit.co.uk/exim-book


dwmw2 at infradead

Jul 5, 2005, 3:02 AM

Post #8 of 18 (1983 views)
Permalink
Re: sqlite lookup support. [In reply to]

On Tue, 2005-07-05 at 10:48 +0100, Tony Finch wrote:
> Yes, it's a bit odd that it's a hybrid between a query-styl and single-key
> lookup. Judging from the patch this is a documentation problem rather than
> an implementation problem, but I only skimmed it.

But then you have the temptation to do...

accept hosts = sqlite;/var/spool/exim/greylist-known-resenders

That form isn't allowed with query-style lookups, is it?

--
dwmw2


dot at dotat

Jul 5, 2005, 3:18 AM

Post #9 of 18 (1986 views)
Permalink
Re: sqlite lookup support. [In reply to]

On Tue, 5 Jul 2005, David Woodhouse wrote:
>
> But then you have the temptation to do...
> accept hosts = sqlite;/var/spool/exim/greylist-known-resenders
> That form isn't allowed with query-style lookups, is it?

Good point. You clearly need another kind of lookup.

Tony.
--
<fanf [at] exim> <dot [at] dotat> http://dotat.at/ ${sg{\N${sg{\
N\}{([^N]*)(.)(.)(.*)}{\$1\$3\$2\$1\$3\n\$2\$3\$4\$3\n\$3\$2\$4}}\
\N}{([^N]*)(.)(.)(.*)}{\$1\$3\$2\$1\$3\n\$2\$3\$4\$3\n\$3\$2\$4}}


dwmw2 at infradead

Jul 5, 2005, 3:34 AM

Post #10 of 18 (1986 views)
Permalink
Re: sqlite lookup support. [In reply to]

On Tue, 2005-07-05 at 11:18 +0100, Tony Finch wrote:
> Good point. You clearly need another kind of lookup.

Just separating the 'query-style' vs. 'single-key' from the
'takes a filename' flag ought to suffice.

In fact, I don't see why other sql-type lookups shouldn't be permitted
to take a server address in the lookup statement rather than having to
configure just _one_ server globally.

--
dwmw2


jh at plonk

Jul 5, 2005, 4:33 AM

Post #11 of 18 (1990 views)
Permalink
Re: sqlite lookup support. [In reply to]

Tony Finch wrote:

>>>I really shouldn't be using lsearch and ${run sh -c "echo blah >> dbfile"}
>>>for my greylisting. Here's sqlite3 support.
>>very nice, thanks!
> Yes, but it could do with documentation :-)

Indeed. It took a little time to find out I have to use lsearch-style
lookups. Makes sense, though.

>>>+ NULL /* no quoting function */
>>Shouldn't the single quote character ' be quoted, at least?
> I suspect it could do with a SQL-quoting function similar to those for
> MySQL/Oracle/Postgres. I wonder why those functions are different...

Because quoting rules are different.
And with sqlite it's tricky, because quoting depends on the quotes
around the quote_xxx and there is no \ escaping. If you have
'${quote_sqlite:$somethin}', every (and only!) ' has to be doubled, if
you used "...", every ". As the quote function does not know what is
around it, there should be a warning in the spec to use only single
quotes and quote_sqlite only handle them.


dwmw2 at infradead

Jul 9, 2005, 7:28 AM

Post #12 of 18 (2002 views)
Permalink
Re: sqlite lookup support. [In reply to]

On Tue, 2005-07-05 at 13:33 +0200, Jakob Hirsch wrote:
> Because quoting rules are different.
> And with sqlite it's tricky, because quoting depends on the quotes
> around the quote_xxx and there is no \ escaping. If you have
> '${quote_sqlite:$somethin}', every (and only!) ' has to be doubled, if
> you used "...", every ". As the quote function does not know what is
> around it, there should be a warning in the spec to use only single
> quotes and quote_sqlite only handle them.

Surely the warning should be not to quote it yourself at all -- isn't
that what you'd be using the quote_sqlite function _for_?

--
dwmw2


jh at plonk

Jul 9, 2005, 10:25 AM

Post #13 of 18 (1981 views)
Permalink
Re: sqlite lookup support. [In reply to]

David Woodhouse wrote:

>>you used "...", every ". As the quote function does not know what is
>>around it, there should be a warning in the spec to use only single
>>quotes and quote_sqlite only handle them.
> Surely the warning should be not to quote it yourself at all -- isn't
> that what you'd be using the quote_sqlite function _for_?

No, you still need a quoting character around it, like
... WHERE name='${quote_mysql:bla"blub'}'
Doing this differently with a quote_sqlite operator would be
inconsistent and even more confusing, I think. But at least people would
notice the difference immediatly, because it would not work at all.


ph10 at cus

Jul 29, 2005, 8:01 AM

Post #14 of 18 (2007 views)
Permalink
Re: sqlite lookup support. [In reply to]

On Mon, 4 Jul 2005, David Woodhouse wrote:

> I really shouldn't be using lsearch and ${run sh -c "echo blah >> dbfile"}
> for my greylisting. Here's sqlite3 support.

That's all good stuff, but I'm going to change the way you do it because
otherwise sqlite queries cannot be used in domain lists, host lists,
etc.

Instead of making it a rather odd "single-key" type lookup, I will make
it into a true "query-style" lookup, with this syntax:

${lookup sqlite{/file/name <sql query>}}

This means you can then do things like

domains = sqlite;/my/db select ... where domain='$domain' : ....

which your code does not support, because the single-key lookups assume
that the key is the domain name.

Also, you didn't supply a quoting function. Is is correct that the only
character that needs to be quoted (by doubling) is the single quote?

Oh, and I found a bug (in case you are using this for real). You forgot
to add the terminating zero byte to the result data. (The
string_append() function doesn't.) And I'll put the results through the
lf_quote() function so that empty fields etc. get quoted.

Philip

--
Philip Hazel University of Cambridge Computing Service,
ph10 [at] cus Cambridge, England. Phone: +44 1223 334714.


wakko at animx

Jul 29, 2005, 3:44 PM

Post #15 of 18 (1985 views)
Permalink
Re: sqlite lookup support. [In reply to]

Philip Hazel wrote:
> On Mon, 4 Jul 2005, David Woodhouse wrote:
> Instead of making it a rather odd "single-key" type lookup, I will make
> it into a true "query-style" lookup, with this syntax:
>
> ${lookup sqlite{/file/name <sql query>}}
>
> This means you can then do things like
>
> domains = sqlite;/my/db select ... where domain='$domain' : ....

Will it handle special characters in the filename? IE a space. (Not that
I'd actually do something like that. =)

--
Lab tests show that use of micro$oft causes cancer in lab animals


ph10 at cus

Jul 31, 2005, 6:26 AM

Post #16 of 18 (2008 views)
Permalink
Re: sqlite lookup support. [In reply to]

On Fri, 29 Jul 2005, Wakko Warner wrote:

> > This means you can then do things like
> >
> > domains = sqlite;/my/db select ... where domain='$domain' : ....
>
> Will it handle special characters in the filename? IE a space. (Not that
> I'd actually do something like that. =)

I was not intending to allow spaces in the file name. This seems to me
to be a reasonable restriction.

--
Philip Hazel University of Cambridge Computing Service,
ph10 [at] cus Cambridge, England. Phone: +44 1223 334714.
Get the Exim 4 book: http://www.uit.co.uk/exim-book


wakko at animx

Jul 31, 2005, 7:05 AM

Post #17 of 18 (1990 views)
Permalink
Re: sqlite lookup support. [In reply to]

Philip Hazel wrote:
> On Fri, 29 Jul 2005, Wakko Warner wrote:
> > > This means you can then do things like
> > >
> > > domains = sqlite;/my/db select ... where domain='$domain' : ....
> >
> > Will it handle special characters in the filename? IE a space. (Not that
> > I'd actually do something like that. =)
>
> I was not intending to allow spaces in the file name. This seems to me
> to be a reasonable restriction.

That would be fine to me. I'm currently not an sqlite user. None of my
system files have spaces in them. Please mention this in the docs though.

--
Lab tests show that use of micro$oft causes cancer in lab animals


jh at plonk

Jul 31, 2005, 9:47 AM

Post #18 of 18 (2014 views)
Permalink
Re: sqlite lookup support. [In reply to]

Philip Hazel wrote:

> Instead of making it a rather odd "single-key" type lookup, I will make
> it into a true "query-style" lookup, with this syntax:
>
> ${lookup sqlite{/file/name <sql query>}}

That sounds quite sensible to me.

> Also, you didn't supply a quoting function. Is is correct that the only
> character that needs to be quoted (by doubling) is the single quote?

I will ask on the sqlite mailing list, but as far as I know (and
tested), yes. But only if the value is sourrended by single quotes. If
you use double quotes, then they (and only they) have to be quoted. So
the behaviour of the quoting function depends on the quotes. That's bad,
but I think it's ok to restrict it to single quotes and put a warning
into the spec. If somebody wants double quotes, he can still use ${sg.

exim dev RSS feed   Index | Next | Previous | View Threaded
 
 


Interested in having your list archived? Contact Gossamer Threads
 
  Web Applications & Managed Hosting Powered by Gossamer Threads Inc.