Hi list,
I'm in the process of adding write support to postfix's mysql client (you will
find a patch against postfix-2.7.1 in the appendix). But I have two problems:
1) the dict_cache_clean_event writes _LAST_CACHE_CLEANUP_COMPLETED_ to the
database. Is this the intended behaviour?
2) If I'm guessing right then the dict_cache_clean_event will iterate with
help of dict->sequence through the database and will look for keys to expire.
But I don't know how to implement this iteration/traverse process with mysql.
My first thought was to use "SELECT * FROM verify" and mysql_use_result() but
I'm wondering if there is a better solution.
Has anyone an idea of how to do this?
Thanks for your help and best regards
Stefan
> > by Stefan Jakobs on 2010-06-13T19:43:00+00:00
> > Hello list,
> > I refer to my question of august 2008
> > (http://archives.neohapsis.com/archives/postfix/2008-08/0747.html, and see
> > below).
> > What are the necessary steps to add update support to the mysql client
> > (Postfix 2.5.6 or newer)?
> > Has someone already done this and is willing to share the code?
> > Thanks for your help and kind regards
> > Stefan
> Wietse wrote on August 22nd 2008:
> Stefan Jakobs:
> I think this involves writing, testing, and documenting code. The
> design stage can pretty much be skipped for this fill-in-the-blanks
> exercise.
> Wietse
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-09-24 13:51:27.000000000 +0200
@@ -283,7 +283,7 @@
/* db_common_expand - expand query and result templates */
int db_common_expand(void *ctxArg, const char *format, const char *value,
- const char *key, VSTRING *result,
+ const char *data, const char *key, VSTRING *result,
db_quote_callback_t quote_func)
{
const char *myname = "db_common_expand";
@@ -382,7 +382,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 +399,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 +435,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-09-24 13:51:27.000000000 +0200
@@ -23,7 +23,7 @@
extern int db_common_parse(DICT *, void **, const char *, int);
extern void db_common_parse_domain(CFG_PARSER *, void *);
extern int db_common_dict_partial(void *);
-extern int db_common_expand(void *, const char *, const char *,
+extern int db_common_expand(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 *);
diff -ur postfix-2.7.1.orig/src/global/dict_ldap.c postfix-2.7.1/src/global/dict_ldap.c
--- postfix-2.7.1.orig/src/global/dict_ldap.c 2010-05-30 00:08:26.000000000 +0200
+++ postfix-2.7.1/src/global/dict_ldap.c 2010-09-24 13:51:27.000000000 +0200
@@ -978,7 +978,7 @@
if (db_common_expand(dict_ldap->ctx,
dict_ldap->result_format,
vals[i]->bv_val,
- name, result, 0)
+ NULL, name, result, 0)
&& dict_ldap->expansion_limit > 0
&& ++expansion > dict_ldap->expansion_limit) {
msg_warn("%s[%d]: %s: Expansion limit exceeded "
@@ -1182,14 +1182,14 @@
* rather than a soft error.
*/
if (!db_common_expand(dict_ldap->ctx, dict_ldap->search_base,
- name, 0, base, rfc2253_quote)) {
+ name, NULL, 0, base, rfc2253_quote)) {
if (msg_verbose > 1)
msg_info("%s: %s: Empty expansion for %s", myname,
dict_ldap->parser->name, dict_ldap->search_base);
return (0);
}
if (!db_common_expand(dict_ldap->ctx, dict_ldap->query,
- name, 0, query, rfc2254_quote)) {
+ name, NULL, 0, query, rfc2254_quote)) {
if (msg_verbose > 1)
msg_info("%s: %s: Empty expansion for %s", myname,
dict_ldap->parser->name, dict_ldap->query);
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-10-01 18:00:31.000000000 +0200
@@ -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
@@ -69,6 +69,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,6 +232,9 @@
DICT dict;
CFG_PARSER *parser;
char *query;
+ char *insert;
+ char *update;
+ char *delete;
char *result_format;
void *ctx;
int expansion_limit;
@@ -248,6 +263,8 @@
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 void plmysql_dealloc(PLMYSQL *);
static void plmysql_close_host(HOST *);
static void plmysql_down_host(HOST *);
@@ -347,7 +364,7 @@
quote_func = 0;
#endif
if (!db_common_expand(dict_mysql->ctx, dict_mysql->query,
- name, 0, query, quote_func))
+ name, NULL, 0, query, quote_func))
return (0);
/* do the query - set dict_errno & cleanup if there's an error */
@@ -373,7 +390,7 @@
row = mysql_fetch_row(query_res);
for (j = 0; j < mysql_num_fields(query_res); j++) {
if (db_common_expand(dict_mysql->ctx, dict_mysql->result_format,
- row[j], name, result, 0)
+ row[j], NULL, name, result, 0)
&& dict_mysql->expansion_limit > 0
&& ++expansion > dict_mysql->expansion_limit) {
msg_warn("%s: %s: Expansion limit exceeded for key: '%s'",
@@ -388,6 +405,190 @@
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));
+ }
+
+#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);
+
+ /*
+ * 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, NULL, 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_expand(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_expand(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));
+ }
+
+#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);
+
+ /*
+ * 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, NULL, 0, query, quote_func))
+ return (0);
+
+ /* do the delete - set dict_errno & cleanup if there's an 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 (0);
+ }
+
+ 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);
+ } /* OK */
+ return(numrows);
+}
+
+/* dict_mysql_sequence - ??? */
+
+static int dict_mysql_sequence(DICT *dict, int function,
+ const char **key, const char **value)
+{
+ const char *myname = "dict_mysql_sequence";
+ int status;
+
+ switch (function) {
+ case DICT_SEQ_FUN_FIRST:
+ *value = dict_mysql_lookup(dict, *key);
+ msg_warn("%s: cal DICT_SEQ_FUN_FIRST", myname);
+ status = 1;
+ break;
+ case DICT_SEQ_FUN_NEXT:
+ *value = NULL;
+ *key = NULL;
+ msg_warn("%s: cal DICT_SEQ_FUN_NEXT", myname);
+ status = 0;
+ break;
+ default:
+ msg_panic("%s: invalid function %d", myname, function);
+ }
+
+ return(status);
+
+}
+
/* dict_mysql_check_stat - check the status of a host */
static int dict_mysql_check_stat(HOST *host, unsigned stat, unsigned type,
@@ -509,7 +710,7 @@
VSTRING_RESET(query);
VSTRING_TERMINATE(query);
db_common_expand(dict_mysql->ctx, dict_mysql->query,
- name, 0, query, dict_mysql_quote);
+ name, NULL, 0, query, dict_mysql_quote);
dict_mysql->active_host = 0;
#endif
@@ -533,6 +734,61 @@
}
/*
+ * 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_expand(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("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("mysql update failed: %s", mysql_error(host->db));
+ plmysql_down_host(host);
+ numrows = 0;
+ }
+ }
+ return (numrows);
+}
+
+/*
* 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
@@ -613,6 +869,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 +914,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);
diff -ur postfix-2.7.1.orig/src/global/dict_pgsql.c postfix-2.7.1/src/global/dict_pgsql.c
--- postfix-2.7.1.orig/src/global/dict_pgsql.c 2007-01-04 21:07:59.000000000 +0100
+++ postfix-2.7.1/src/global/dict_pgsql.c 2010-09-24 13:51:27.000000000 +0200
@@ -378,7 +378,7 @@
* for each connection, we don't bother with quoting...
*/
if (!db_common_expand(dict_pgsql->ctx, dict_pgsql->query,
- name, 0, query, 0))
+ name, NULL, 0, query, 0))
return (0);
/* do the query - set dict_errno & cleanup if there's an error */
@@ -403,7 +403,7 @@
for (j = 0; j < numcols; j++) {
r = PQgetvalue(query_res, i, j);
if (db_common_expand(dict_pgsql->ctx, dict_pgsql->result_format,
- r, name, result, 0)
+ r, NULL, name, result, 0)
&& dict_pgsql->expansion_limit > 0
&& ++expansion > dict_pgsql->expansion_limit) {
msg_warn("%s: %s: Expansion limit exceeded for key: '%s'",
@@ -538,7 +538,7 @@
VSTRING_RESET(query);
VSTRING_TERMINATE(query);
db_common_expand(dict_pgsql->ctx, dict_pgsql->query,
- name, 0, query, dict_pgsql_quote);
+ name, NULL, 0, query, dict_pgsql_quote);
dict_pgsql->active_host = 0;
/* Check for potential dict_pgsql_quote() failure. */
