On Thursday, 6th Januar 2011, 21:02:17 Victor Duchovni wrote:
> On Thu, Jan 06, 2011 at 04:56:48PM +0100, Stefan Jakobs wrote:
> > > In this case, it is not as critical to set such a flag, but it is
> > > important to allow the existing scan to continue to completion, and
> > > ignore or (just note) new requests until it does. Once a scan
> > > completes, new scans can proceed either immediately (saved flag) or
> > > when next requested.
> >
> > That's what I have implemented. If a cleanup process is already running
> > and a second cleanup process starts then the second process will quit as
> > if the database was empty and it will log a warning
>
> No warning is necessary. With a large database the cleanup thread may
> run longer than the scheduled interval between threads. This is fine.
OK, I attached the final(?) version of the mysql-write-support patch.
Is there any chance that the patch will make it into a stable Postfix release?
Regards
Stefan
diff -ur postfix-2.7.1.orig/man/man5/mysql_table.5 postfix-2.7.1/man/man5/mysql_table.5
--- postfix-2.7.1.orig/man/man5/mysql_table.5 2008-07-21 13:50:13.000000000 +0200
+++ postfix-2.7.1/man/man5/mysql_table.5 2010-12-11 20:39:20.000000000 +0100
@@ -126,6 +126,15 @@
.nf
dbname = customer_database
.fi
+.IP "\fBcache_tblname\fR"
+The name of the cache table. Postfix will perform cache cleanups
+on this table. Example:
+.nf
+ cache_tblname = verify
+.fi
+.IP
+This parameter is available with Postfix MySQL write support patch.
+
.IP "\fBquery\fR"
The SQL query template used to search the database, where \fB%s\fR
is a substitute for the address Postfix is trying to resolve,
@@ -191,6 +200,139 @@
parameter is not specified.
NOTE: DO NOT put quotes around the query parameter.
+.IP "\fBinsert\fR"
+The SQL insert template used to insert a input key, value pair into the
+database, where \fB%s\fR is a substitute for the input key and \fB%v\fR
+is a substitute for the value, e.g.
+.nf
+ insert = INSERT verify SET address='%s', data='%v'
+.fi
+
+This parameter supports the following '%' expansions:
+.RS
+.IP "\fB\fB%%\fR\fR"
+This is replaced by a literal '%' character.
+.IP "\fB\fB%s\fR\fR"
+This is replaced by the input key.
+SQL quoting is used to make sure that the input key does not
+add unexpected metacharacters.
+.IP "\fB\fB%v\fR\fR"
+This is replaced by the corresponding value.
+SQL quoting is used to make sure that the value does not
+add unexpected metacharacters.
+.IP "\fB\fB%u\fR\fR"
+When the input key is an address of the form u...@domain, \fB%u\fR
+is replaced by the SQL quoted local part of the address.
+Otherwise, \fB%u\fR is replaced by the entire search string.
+If the localpart is empty, the insert is suppressed and returns
+no results.
+.IP "\fB\fB%d\fR\fR"
+When the input key is an address of the form u...@domain, \fB%d\fR
+is replaced by the SQL quoted domain part of the address.
+Otherwise, the insert is suppressed and returns no results.
+.IP "\fB\fB%[SVUD]\fR\fR"
+The upper-case equivalents of the above expansions behave in the
+\fBinsert\fR parameter identically to their lower-case counter-parts.
+.IP "\fB\fB%[1-9]\fR\fR"
+The patterns %1, %2, ... %9 are replaced by the corresponding
+most significant component of the input key's domain. If the
+input key is \fiu...@mail.example.com\fr, then %1 is \fBcom\fR,
+%2 is \fBexample\fR and %3 is \fBmail\fR. If the input key is
+unqualified or does not have enough domain components to satisfy
+all the specified patterns, the insert is suppressed and returns
+no results.
+.RE
+.IP
+This parameter is available with Postfix MySQL write support patch.
+
+NOTE: DO NOT put quotes around the query parameter.
+.IP "\fBupdate\fR"
+The SQL update template used to update a input key, value pair in the
+database, where \fB%s\fR is a substitute for the input key and \fB%v\fR
+is a substitute for the value, e.g.
+.nf
+ update = UPDATE verify SET data='%v' WHERE address='%s'
+.fi
+
+This parameter supports the following '%' expansions:
+.RS
+.IP "\fB\fB%%\fR\fR"
+This is replaced by a literal '%' character.
+.IP "\fB\fB%s\fR\fR"
+This is replaced by the input key.
+SQL quoting is used to make sure that the input key does not
+add unexpected metacharacters.
+.IP "\fB\fB%v\fR\fR"
+This is replaced by the corresponding value.
+SQL quoting is used to make sure that the value does not
+add unexpected metacharacters.
+.IP "\fB\fB%u\fR\fR"
+When the input key is an address of the form u...@domain, \fB%u\fR
+is replaced by the SQL quoted local part of the address.
+Otherwise, \fB%u\fR is replaced by the entire search string.
+If the localpart is empty, the update is suppressed and returns
+no results.
+.IP "\fB\fB%d\fR\fR"
+When the input key is an address of the form u...@domain, \fB%d\fR
+is replaced by the SQL quoted domain part of the address.
+Otherwise, the update is suppressed and returns no results.
+.IP "\fB\fB%[SVUD]\fR\fR"
+The upper-case equivalents of the above expansions behave in the
+\fBupdate\fR parameter identically to their lower-case counter-parts.
+.IP "\fB\fB%[1-9]\fR\fR"
+The patterns %1, %2, ... %9 are replaced by the corresponding
+most significant component of the input key's domain. If the
+input key is \fiu...@mail.example.com\fr, then %1 is \fBcom\fR,
+%2 is \fBexample\fR and %3 is \fBmail\fR. If the input key is
+unqualified or does not have enough domain components to satisfy
+all the specified patterns, the update is suppressed and returns
+no results.
+.RE
+.IP
+This parameter is available with Postfix MySQL write support patch.
+
+NOTE: DO NOT put quotes around the update parameter.
+.IP "\fBdelete\fR"
+The SQL delete template used to delete a input key from the database,
+where \fB%s\fR is a substitute for the input key, e.g.
+.nf
+ delete = DELETE FROM verify WHERE address='%s'
+.fi
+
+This parameter supports the following '%' expansions:
+.RS
+.IP "\fB\fB%%\fR\fR"
+This is replaced by a literal '%' character.
+.IP "\fB\fB%s\fR\fR"
+This is replaced by the input key.
+SQL quoting is used to make sure that the input key does not
+add unexpected metacharacters.
+.IP "\fB\fB%u\fR\fR"
+When the input key is an address of the form u...@domain, \fB%u\fR
+is replaced by the SQL quoted local part of the address.
+Otherwise, \fB%u\fR is replaced by the entire search string.
+If the localpart is empty, the delete is suppressed and returns
+no results.
+.IP "\fB\fB%d\fR\fR"
+When the input key is an address of the form u...@domain, \fB%d\fR
+is replaced by the SQL quoted domain part of the address.
+Otherwise, the delete is suppressed and returns no results.
+.IP "\fB\fB%[SVUD]\fR\fR"
+The upper-case equivalents of the above expansions behave in the
+\fBdelete\fR parameter identically to their lower-case counter-parts.
+.IP "\fB\fB%[1-9]\fR\fR"
+The patterns %1, %2, ... %9 are replaced by the corresponding
+most significant component of the input key's domain. If the
+input key is \fiu...@mail.example.com\fr, then %1 is \fBcom\fR,
+%2 is \fBexample\fR and %3 is \fBmail\fR. If the input key is
+unqualified or does not have enough domain components to satisfy
+all the specified patterns, the delete is suppressed and returns
+no results.
+.RE
+.IP
+This parameter is available with Postfix MySQL write support patch.
+
+NOTE: DO NOT put quotes around the delete parameter.
.IP "\fBresult_format (default: \fB%s\fR)\fR"
Format template applied to result attributes. Most commonly used
to append (or prepend) text to the result. This parameter supports
@@ -343,3 +485,6 @@
Institute of Mathematics of the Romanian Academy
P.O. BOX 1-764
RO-014700 Bucharest, ROMANIA
+
+MySQL write support by:
+Stefan Jakobs
diff -ur postfix-2.7.1.orig/src/global/db_common.c postfix-2.7.1/src/global/db_common.c
--- postfix-2.7.1.orig/src/global/db_common.c 2009-10-05 22:33:16.000000000 +0200
+++ postfix-2.7.1/src/global/db_common.c 2010-10-21 17:48:32.000000000 +0200
@@ -23,6 +23,15 @@
/* VSTRING *buf;
/* void (*quote_func)(DICT *, const char *, VSTRING *);
/*
+/* int db_common_expand2(ctx, format, value, data, key, buf, quote_func);
+/* void *ctx;
+/* const char *format;
+/* const char *value;
+/* const char *data;
+/* const char *key;
+/* VSTRING *buf;
+/* void (*quote_func)(DICT *, const char *, VSTRING *);
+/*
/* int db_common_check_domain(domain_list, addr);
/* STRING_LIST *domain_list;
/* const char *addr;
@@ -66,6 +75,8 @@
/* A literal percent character.
/* .IP %s
/* The entire lookup key \fIaddr\fR.
+/* .IP %v
+/* The corresponding data to the lookup key \fIaddr\fR.
/* .IP %u
/* If \fBaddr\fR is a fully qualified address, the local part of the
/* address. Otherwise \fIaddr\fR.
@@ -77,6 +88,8 @@
/* The following '%' expansions are performed on the lookup \fBkey\fR:
/* .IP %S
/* The entire lookup key \fIkey\fR.
+/* .IP %V
+/* The corresponding data to the lookup key \fIaddr\fR.
/* .IP %U
/* If \fBkey\fR is a fully qualified address, the local part of the
/* address. Otherwise \fIkey\fR.
@@ -286,6 +299,15 @@
const char *key, VSTRING *result,
db_quote_callback_t quote_func)
{
+ return db_common_expand2(ctxArg, format, value, NULL, key, result, quote_func);
+}
+
+/* db_common_expand2 - expand query and result templates */
+
+int db_common_expand2(void *ctxArg, const char *format, const char *value,
+ const char *data, const char *key, VSTRING *result,
+ db_quote_callback_t quote_func)
+{
const char *myname = "db_common_expand";
DB_COMMON_CTX *ctx = (DB_COMMON_CTX *) ctxArg;
const char *vdomain = 0;
@@ -382,7 +404,8 @@
} while (0)
/*
- * Replace all instances of %s with the address to look up. Replace %u
+ * Replace all instances of %s with the address to look up. Replace %v
+ * with the data value portion. Replace %u
* with the user portion, and %d with the domain portion. "%%" expands to
* "%". lowercase -> addr, uppercase -> key
*/
@@ -398,6 +421,16 @@
QUOTE_VAL(ctx->dict, quote_func, value, result);
break;
+ case 'v':
+ /* Don't silenty skip empty query string */
+ if (*data == 0) {
+ msg_warn("table \"%s:%s\": empty query string"
+ " -- ignored", ctx->dict->type, ctx->dict->name);
+ return (0);
+ }
+ QUOTE_VAL(ctx->dict, quote_func, data, result);
+ break;
+
case 'u':
if (vdomain) {
if (vuser == 0)
@@ -424,6 +457,18 @@
QUOTE_VAL(ctx->dict, quote_func, value, result);
break;
+ case 'V':
+ if (! key) {
+ /* Don't silenty skip empty query string */
+ if (*data == 0) {
+ msg_warn("table \"%s:%s\": empty query string"
+ " -- ignored", ctx->dict->type, ctx->dict->name);
+ return (0);
+ }
+ QUOTE_VAL(ctx->dict, quote_func, data, result);
+ }
+ break;
+
case 'U':
if (key) {
if (kdomain) {
diff -ur postfix-2.7.1.orig/src/global/db_common.h postfix-2.7.1/src/global/db_common.h
--- postfix-2.7.1.orig/src/global/db_common.h 2005-09-23 01:50:50.000000000 +0200
+++ postfix-2.7.1/src/global/db_common.h 2010-10-21 17:47:24.000000000 +0200
@@ -25,6 +25,8 @@
extern int db_common_dict_partial(void *);
extern int db_common_expand(void *, const char *, const char *,
const char *, VSTRING *, db_quote_callback_t);
+extern int db_common_expand2(void *, const char *, const char *, const char *,
+ const char *, VSTRING *, db_quote_callback_t);
extern int db_common_check_domain(void *, const char *);
extern void db_common_free_ctx(void *);
extern void db_common_sql_build_query(VSTRING *query, CFG_PARSER *parser);
diff -ur postfix-2.7.1.orig/src/global/dict_mysql.c postfix-2.7.1/src/global/dict_mysql.c
--- postfix-2.7.1.orig/src/global/dict_mysql.c 2007-01-04 21:07:38.000000000 +0100
+++ postfix-2.7.1/src/global/dict_mysql.c 2010-12-17 17:46:47.000000000 +0100
@@ -42,7 +42,7 @@
/* .IP other_name
/* reference for outside use.
/* .IP open_flags
-/* Must be O_RDONLY.
+/* See open(2). Must be O_RDWR for write access.
/* .IP dict_flags
/* See dict_open(3).
/* .PP
@@ -57,6 +57,8 @@
/* Password for the above.
/* .IP \fIdbname\fR
/* Name of the database.
+/* .IP \fIcache_tblname\fR
+/* Name of the cache table. Used for automatic table cleanups.
/* .IP \fIdomain\fR
/* List of domains the queries should be restricted to. If
/* specified, only FQDN addresses whose domain parts matching this
@@ -69,6 +71,18 @@
/* No query is specified, the legacy variables \fItable\fR,
/* \fIselect_field\fR, \fIwhere_field\fR and \fIadditional_conditions\fR
/* are used to construct the query template.
+/* .IP \fIinsert\fR
+/* Insert template, before the query is actually issued, variable
+/* substitutions are performed. See mysql_table(5) for details.
+/* Legacy variables are not available for this query.
+/* .IP \fIupdate\fR
+/* Update template, before the query is actually issued, variable
+/* substitutions are performed. See mysql_table(5) for details.
+/* Legacy variables are not available for this query.
+/* .IP \fIdelete\fR
+/* Delete template, before the query is actually issued, variable
+/* substitutions are performed. See mysql_table(5) for details.
+/* Legacy variables are not available for this query.
/* .IP \fIresult_format\fR
/* The format used to expand results from queries. Substitutions
/* are performed as described in mysql_table(5). Defaults to returning
@@ -220,12 +234,17 @@
DICT dict;
CFG_PARSER *parser;
char *query;
+ char *insert;
+ char *update;
+ char *delete;
char *result_format;
void *ctx;
int expansion_limit;
char *username;
char *password;
char *dbname;
+ char *cache_tblname;
+ int handler_open; /* semaphore */
ARGV *hosts;
PLMYSQL *pldb;
#if defined(MYSQL_VERSION_ID) && MYSQL_VERSION_ID >= 40000
@@ -248,6 +267,10 @@
static PLMYSQL *plmysql_init(ARGV *);
static MYSQL_RES *plmysql_query(DICT_MYSQL *, const char *, VSTRING *, char *,
char *, char *);
+static int plmysql_update(DICT_MYSQL *, const char *, const char *,
+ const char *,VSTRING *, char *, char *, char *);
+static MYSQL_RES *plmysql_sequence(DICT_MYSQL *, int, VSTRING *, char *,
+ char *, char *);
static void plmysql_dealloc(PLMYSQL *);
static void plmysql_close_host(HOST *);
static void plmysql_down_host(HOST *);
@@ -258,6 +281,13 @@
static void mysql_parse_config(DICT_MYSQL *, const char *);
static HOST *host_init(const char *);
+#define INIT_VSTR(buf, len) do { \
+ if (buf == 0) \
+ buf = vstring_alloc(len); \
+ VSTRING_RESET(buf); \
+ VSTRING_TERMINATE(buf); \
+ } while (0)
+
/* dict_mysql_quote - escape SQL metacharacters in input string */
static void dict_mysql_quote(DICT *dict, const char *name, VSTRING *result)
@@ -326,13 +356,6 @@
return (0);
}
-#define INIT_VSTR(buf, len) do { \
- if (buf == 0) \
- buf = vstring_alloc(len); \
- VSTRING_RESET(buf); \
- VSTRING_TERMINATE(buf); \
- } while (0)
-
INIT_VSTR(query, 10);
/*
@@ -388,6 +411,261 @@
return ((dict_errno == 0 && *r) ? r : 0);
}
+/* dict_mysql_update - update/insert database entry */
+
+static void dict_mysql_update(DICT *dict, const char *name, const char *val)
+{
+ const char *myname = "dict_mysql_update";
+ DICT_MYSQL *dict_mysql = (DICT_MYSQL *)dict;
+ MYSQL_RES *query_res;
+ static VSTRING *query;
+ int numrows;
+ db_quote_callback_t quote_func = dict_mysql_quote;
+
+ dict_errno = 0;
+
+ /*
+ * Optionally fold the key.
+ */
+ if (dict->flags & DICT_FLAG_FOLD_FIX) {
+ if (dict->fold_buf == 0)
+ dict->fold_buf = vstring_alloc(10);
+ vstring_strcpy(dict->fold_buf, name);
+ name = lowercase(vstring_str(dict->fold_buf));
+ }
+
+ INIT_VSTR(query, 10);
+
+ /*
+ * Suppress the lookup if the query expansion is empty
+ *
+ * This initial expansion is outside the context of any
+ * specific host connection, we just want to check the
+ * key pre-requisites, so when quoting happens separately
+ * for each connection, we don't bother with quoting...
+ */
+#if defined(MYSQL_VERSION_ID) && MYSQL_VERSION_ID >= 40000
+ quote_func = 0;
+#endif
+ if (!db_common_expand(dict_mysql->ctx, dict_mysql->query,
+ name, 0, query, quote_func))
+ return;
+
+ /* check if name exists already */
+ /* do the query - set dict_errno & cleanup if there's an error */
+ if ((query_res = plmysql_query(dict_mysql, name, query,
+ dict_mysql->dbname,
+ dict_mysql->username,
+ dict_mysql->password)) == 0) {
+ dict_errno = DICT_ERR_RETRY;
+ return;
+ }
+
+ numrows = mysql_num_rows(query_res);
+ mysql_free_result(query_res);
+ if (msg_verbose)
+ msg_info("%s: retrieved %d rows", myname, numrows);
+ if (numrows == 0) { /* do insert */
+ if (!db_common_expand2(dict_mysql->ctx, dict_mysql->insert,
+ name, val, 0, query, quote_func))
+ return;
+
+ if ((numrows = plmysql_update(dict_mysql, name, val, dict_mysql->insert, query,
+ dict_mysql->dbname,
+ dict_mysql->username,
+ dict_mysql->password)) == 0) {
+ dict_errno = DICT_ERR_RETRY;
+ return;
+ }
+
+ } else { /* do update */
+ if (!db_common_expand2(dict_mysql->ctx, dict_mysql->update,
+ name, val, 0, query, quote_func))
+ return;
+
+ if ((numrows = plmysql_update(dict_mysql, name, val, dict_mysql->update, query,
+ dict_mysql->dbname,
+ dict_mysql->username,
+ dict_mysql->password)) == 0) {
+ dict_errno = DICT_ERR_RETRY;
+ return;
+ }
+
+ }
+ if (msg_verbose)
+ msg_info("%s: updated %d rows", myname, numrows);
+}
+
+/* dict_mysql_delete - delete database entry */
+
+static int dict_mysql_delete(DICT *dict, const char *name)
+{
+ const char *myname = "dict_mysql_delete";
+ DICT_MYSQL *dict_mysql = (DICT_MYSQL *)dict;
+ static VSTRING *query;
+ int numrows;
+ db_quote_callback_t quote_func = dict_mysql_quote;
+
+ dict_errno = 0;
+
+ /*
+ * Optionally fold the key.
+ */
+ if (dict->flags & DICT_FLAG_FOLD_FIX) {
+ if (dict->fold_buf == 0)
+ dict->fold_buf = vstring_alloc(10);
+ vstring_strcpy(dict->fold_buf, name);
+ name = lowercase(vstring_str(dict->fold_buf));
+ }
+
+ INIT_VSTR(query, 10);
+
+ /*
+ * Suppress the lookup if the query expansion is empty
+ *
+ * This initial expansion is outside the context of any
+ * specific host connection, we just want to check the
+ * key pre-requisites, so when quoting happens separately
+ * for each connection, we don't bother with quoting...
+ */
+#if defined(MYSQL_VERSION_ID) && MYSQL_VERSION_ID >= 40000
+ quote_func = 0;
+#endif
+ if (!db_common_expand(dict_mysql->ctx, dict_mysql->delete,
+ name, 0, query, quote_func))
+ return (1);
+
+ /* delete - set dict_errno & cleanup on error */
+ if ((numrows = plmysql_update(dict_mysql, name, NULL, dict_mysql->delete, query,
+ dict_mysql->dbname,
+ dict_mysql->username,
+ dict_mysql->password)) == 0) {
+ dict_errno = DICT_ERR_RETRY;
+ return (1);
+ }
+
+ if (msg_verbose)
+ msg_info("%s: deleted %d rows", myname, numrows);
+ if (numrows == 0) { /* failure */
+ msg_warn("%s: %s: delete failed, key: '%s'",
+ myname, dict_mysql->parser->name, name);
+ return (1);
+ } else { /* OK */
+ return (0);
+ }
+}
+
+/* dict_mysql_sequence - traverse the db */
+
+static int dict_mysql_sequence(DICT *dict, int function,
+ const char **key, const char **value)
+{
+ const char *myname = "dict_mysql_sequence";
+ DICT_MYSQL *dict_mysql = (DICT_MYSQL *)dict;
+ MYSQL_RES *seq_res;
+ MYSQL_ROW row;
+ static VSTRING *result;
+ static VSTRING *name;
+ const char *format = "%s";
+ VSTRING *seq = vstring_alloc(10);
+ int plmysql_errno = 0;
+ dict_errno = 0;
+
+ INIT_VSTR(name, 10);
+ INIT_VSTR(result, 10);
+
+ switch (function) {
+ case DICT_SEQ_FUN_FIRST:
+ if (dict_mysql->handler_open) {
+ /* free mem and return as finished */
+ vstring_free(seq);
+ if (msg_verbose)
+ msg_info("%s: handler still open, cleanup skipped", myname);
+ return (1);
+ } else {
+ /* initialise handler */
+ vstring_sprintf(seq, "HANDLER %s OPEN", dict_mysql->cache_tblname);
+ plmysql_sequence(dict_mysql, plmysql_errno, seq,
+ dict_mysql->dbname,
+ dict_mysql->username,
+ dict_mysql->password);
+ if (plmysql_errno > 0){
+ dict_errno = DICT_ERR_RETRY;
+ vstring_free(seq);
+ return (1);
+ }
+ /* lock */
+ dict_mysql->handler_open = 1;
+ vstring_sprintf(seq, "HANDLER %s READ FIRST", dict_mysql->cache_tblname);
+ if (msg_verbose)
+ msg_info("%s: handler opened", myname);
+ }
+ break;
+ case DICT_SEQ_FUN_NEXT:
+ vstring_sprintf(seq, "HANDLER %s READ NEXT", dict_mysql->cache_tblname);
+ break;
+ default:
+ msg_panic("%s: invalid function %d", myname, function);
+ return (1);
+ }
+
+ if (msg_verbose)
+ msg_info("%s: handler read next", myname);
+ if ((seq_res = plmysql_sequence(dict_mysql, plmysql_errno, seq,
+ dict_mysql->dbname,
+ dict_mysql->username,
+ dict_mysql->password)) == 0) {
+ dict_errno = DICT_ERR_RETRY;
+ vstring_free(seq);
+ /* unlock */
+ dict_mysql->handler_open = 0;
+ return (1);
+ }
+ row = mysql_fetch_row(seq_res);
+ if (row == NULL) {
+ /* close handler */
+ vstring_sprintf(seq, "HANDLER %s CLOSE", dict_mysql->cache_tblname);
+ plmysql_sequence(dict_mysql, plmysql_errno, seq,
+ dict_mysql->dbname,
+ dict_mysql->username,
+ dict_mysql->password);
+ if (plmysql_errno > 0)
+ dict_errno = DICT_ERR_RETRY;
+ if (msg_verbose)
+ msg_info("%s: handler closed", myname);
+ vstring_free(seq);
+ /* unlock */
+ dict_mysql->handler_open = 0;
+ return(1);
+ }
+ vstring_free(seq);
+ if (mysql_num_fields(seq_res) >= 2) {
+ if (!db_common_expand(dict_mysql->ctx, format,
+ row[0], 0, name, 0)) {
+ /* unlock */
+ dict_mysql->handler_open = 0;
+ return (1);
+ }
+ if (!db_common_expand(dict_mysql->ctx, format,
+ row[1], 0, result, 0)) {
+ /* unlock */
+ dict_mysql->handler_open = 0;
+ return (1);
+ }
+ } else {
+ msg_warn("%s: invalid number of rows in query response: %d.",
+ myname, mysql_num_fields(seq_res));
+ dict_errno = DICT_ERR_RETRY;
+ /* unlock */
+ dict_mysql->handler_open = 0;
+ return (1);
+ }
+
+ *key = vstring_str(name);
+ *value = vstring_str(result);
+ return (0);
+}
+
/* dict_mysql_check_stat - check the status of a host */
static int dict_mysql_check_stat(HOST *host, unsigned stat, unsigned type,
@@ -486,7 +764,6 @@
* on failure of all db instances, return 0;
* close unnecessary active connections
*/
-
static MYSQL_RES *plmysql_query(DICT_MYSQL *dict_mysql,
const char *name,
VSTRING *query,
@@ -514,8 +791,8 @@
#endif
if (!(mysql_query(host->db, vstring_str(query)))) {
- if ((res = mysql_store_result(host->db)) == 0) {
- msg_warn("mysql query failed: %s", mysql_error(host->db));
+ if ((res = mysql_store_result(host->db)) == 0) {
+ msg_warn("dict_mysql: query failed: %s", mysql_error(host->db));
plmysql_down_host(host);
} else {
if (msg_verbose)
@@ -533,6 +810,101 @@
}
/*
+ * plmysql_update - process a MySQL update, insert or delete query.
+ * Return number of affected rows on success.
+ * On failure, log failure and try other db instances.
+ * on failure of all db instances, return 0;
+ * close unnecessary active connections
+ */
+static int plmysql_update(DICT_MYSQL *dict_mysql,
+ const char *name,
+ const char *val,
+ const char *update,
+ VSTRING *query,
+ char *dbname,
+ char *username,
+ char *password)
+{
+ PLMYSQL *PLDB = dict_mysql->pldb;
+ HOST *host;
+ int numrows;
+
+ while ((host = dict_mysql_get_active(PLDB, dbname, username, password)) != NULL) {
+
+#if defined(MYSQL_VERSION_ID) && MYSQL_VERSION_ID >= 40000
+ /*
+ * The active host is used to escape strings in the
+ * context of the active connection's character encoding.
+ */
+ dict_mysql->active_host = host;
+ VSTRING_RESET(query);
+ VSTRING_TERMINATE(query);
+ db_common_expand2(dict_mysql->ctx, update,
+ name, val, 0, query, dict_mysql_quote);
+ dict_mysql->active_host = 0;
+#endif
+
+ if (!(mysql_query(host->db, vstring_str(query)))) {
+ if ((numrows = mysql_affected_rows(host->db)) == 0) {
+ msg_warn("dict_mysql: update failed: %s", mysql_error(host->db));
+ plmysql_down_host(host);
+ } else {
+ if (msg_verbose)
+ msg_info("dict_mysql: successful update from host %s", host->hostname);
+ event_request_timer(dict_mysql_event, (char *) host, IDLE_CONN_INTV);
+ break;
+ }
+ } else {
+ msg_warn("dict_mysql: update failed: %s", mysql_error(host->db));
+ plmysql_down_host(host);
+ numrows = 0;
+ }
+ }
+ return (numrows);
+}
+
+/*
+ * plmysql_sequence - process a MySQL sequence query. Return MYSQL_RES* on success.
+ * On failure, log failure and try other db instances.
+ * on failure of all db instances, return 0;
+ * close unnecessary active connections;
+ */
+static MYSQL_RES *plmysql_sequence(DICT_MYSQL *dict_mysql, int errno,
+ VSTRING *query,
+ char *dbname,
+ char *username,
+ char *password)
+{
+ PLMYSQL *PLDB = dict_mysql->pldb;
+ HOST *host;
+ MYSQL_RES *res = 0;
+
+ while ((host = dict_mysql_get_active(PLDB, dbname, username, password)) != NULL) {
+
+ if (!(mysql_query(host->db, vstring_str(query)))) {
+ /* an empty result set returns 0, too. Use errno to differentiate *
+ * between an error and an empty result set */
+ if ((res = mysql_store_result(host->db)) == 0 &&
+ (errno = mysql_errno(host->db)) > 0) {
+ msg_warn("dict_mysql: faulty sequence result: %s", mysql_error(host->db));
+ plmysql_down_host(host);
+ } else {
+ if (msg_verbose)
+ msg_info("dict_mysql: successful sequence query from host %s",
+ host->hostname);
+ event_request_timer(dict_mysql_event, (char *) host, IDLE_CONN_INTV);
+ break;
+ }
+ } else {
+ msg_warn("dict_mysql: sequence query failed: %s", mysql_error(host->db));
+ plmysql_down_host(host);
+ }
+ }
+
+ return res;
+}
+
+/*
* plmysql_connect_single -
* used to reconnect to a single database when one is down or none is
* connected yet. Log all errors and set the stat field of host accordingly
@@ -595,6 +967,7 @@
dict_mysql->username = cfg_get_str(p, "user", "", 0, 0);
dict_mysql->password = cfg_get_str(p, "password", "", 0, 0);
dict_mysql->dbname = cfg_get_str(p, "dbname", "", 1, 0);
+ dict_mysql->cache_tblname = cfg_get_str(p, "cache_tblname", "", 0, 0);
dict_mysql->result_format = cfg_get_str(p, "result_format", "%s", 1, 0);
/*
* XXX: The default should be non-zero for safety, but that is not
@@ -613,6 +986,10 @@
dict_mysql->query = vstring_export(buf);
}
+ dict_mysql->insert = cfg_get_str(p, "insert", NULL, 0, 0);
+ dict_mysql->update = cfg_get_str(p, "update", NULL, 0, 0);
+ dict_mysql->delete = cfg_get_str(p, "delete", NULL, 0, 0);
+
/*
* Must parse all templates before we can use db_common_expand()
*/
@@ -654,14 +1031,17 @@
/*
* Sanity checks.
- */
+ *
if (open_flags != O_RDONLY)
msg_fatal("%s:%s map requires O_RDONLY access mode",
DICT_TYPE_MYSQL, name);
-
+ */
dict_mysql = (DICT_MYSQL *) dict_alloc(DICT_TYPE_MYSQL, name,
sizeof(DICT_MYSQL));
dict_mysql->dict.lookup = dict_mysql_lookup;
+ dict_mysql->dict.update = dict_mysql_update;
+ dict_mysql->dict.delete = dict_mysql_delete;
+ dict_mysql->dict.sequence = dict_mysql_sequence;
dict_mysql->dict.close = dict_mysql_close;
dict_mysql->dict.flags = dict_flags;
mysql_parse_config(dict_mysql, name);
@@ -669,6 +1049,8 @@
dict_mysql->active_host = 0;
#endif
dict_mysql->pldb = plmysql_init(dict_mysql->hosts);
+ /* initialise semaphore */
+ dict_mysql->handler_open = 0;
if (dict_mysql->pldb == NULL)
msg_fatal("couldn't intialize pldb!\n");
return (DICT_DEBUG (&dict_mysql->dict));
