5
/* dictionary manager interface to SQLite3 databases
7
/* #include <dict_sqlite.h>
9
/* DICT *dict_sqlite_open(name, open_flags, dict_flags)
14
/* dict_sqlite_open() creates a dictionary of type 'sqlite'.
15
/* This dictionary is an interface for the postfix key->value
16
/* mappings to SQLite. The result is a pointer to the installed
21
/* Either the path to the SQLite configuration file (if it
22
/* starts with '/' or '.'), or the prefix which will be used
23
/* to obtain main.cf configuration parameters for this search.
25
/* In the first case, the configuration parameters below are
26
/* specified in the file as \fIname\fR=\fIvalue\fR pairs.
28
/* In the second case, the configuration parameters are prefixed
29
/* with the value of \fIname\fR and an underscore, and they
30
/* are specified in main.cf. For example, if this value is
31
/* \fIsqlitecon\fR, the parameters would look like
32
/* \fIsqlitecon_dbpath\fR, \fIsqlitecon_query\fR, and so on.
38
/* Configuration parameters:
40
/* Path to SQLite database
42
/* Query template. Before the query is actually issued, variable
43
/* substitutions are performed. See sqlite_table(5) for details.
45
/* The format used to expand results from queries. Substitutions
46
/* are performed as described in sqlite_table(5). Defaults to
47
/* returning the lookup result unchanged.
48
/* .IP expansion_limit
49
/* Limit (if any) on the total number of lookup result values.
50
/* Lookups which exceed the limit fail with dict_errno=DICT_ERR_RETRY.
51
/* Note that each non-empty (and non-NULL) column of a
52
/* multi-column result row counts as one result.
53
/* .IP "select_field, where_field, additional_conditions"
54
/* Legacy query interface.
56
/* dict(3) generic dictionary manager
70
#if !defined(SQLITE_VERSION_NUMBER) || (SQLITE_VERSION_NUMBER < 3005004)
71
#define sqlite3_prepare_v2 sqlite3_prepare
74
/* Utility library. */
79
#include <stringops.h>
84
#include <cfg_parser.h>
85
#include <db_common.h>
87
/* Application-specific. */
89
#include <dict_sqlite.h>
92
DICT dict; /* generic member */
93
CFG_PARSER *parser; /* common parameter parser */
94
sqlite3 *db; /* sqlite handle */
95
char *query; /* db_common_expand() query */
96
char *result_format; /* db_common_expand() result_format */
97
void *ctx; /* db_common_parse() context */
98
char *dbpath; /* dbpath config attribute */
99
int expansion_limit; /* expansion_limit config attribute */
102
/* dict_sqlite_quote - escape SQL metacharacters in input string */
104
static void dict_sqlite_quote(DICT *dict, const char *raw_text, VSTRING *result)
108
quoted_text = sqlite3_mprintf("%q", raw_text);
110
if (quoted_text == 0)
111
msg_fatal("dict_sqlite_quote: out of memory");
112
vstring_strcat(result, raw_text);
113
sqlite3_free(quoted_text);
116
/* dict_sqlite_close - close the database */
118
static void dict_sqlite_close(DICT *dict)
120
const char *myname = "dict_sqlite_close";
121
DICT_SQLITE *dict_sqlite = (DICT_SQLITE *) dict;
124
msg_info("%s: %s", myname, dict_sqlite->parser->name);
126
if (sqlite3_close(dict_sqlite->db) != SQLITE_OK)
127
msg_fatal("%s: close %s failed", myname, dict_sqlite->parser->name);
128
cfg_parser_free(dict_sqlite->parser);
129
myfree(dict_sqlite->dbpath);
130
myfree(dict_sqlite->query);
131
myfree(dict_sqlite->result_format);
132
if (dict_sqlite->ctx)
133
db_common_free_ctx(dict_sqlite->ctx);
135
vstring_free(dict->fold_buf);
139
/* dict_sqlite_lookup - find database entry */
141
static const char *dict_sqlite_lookup(DICT *dict, const char *name)
143
const char *myname = "dict_sqlite_lookup";
144
DICT_SQLITE *dict_sqlite = (DICT_SQLITE *) dict;
145
sqlite3_stmt *sql_stmt;
146
const char *query_remainder;
147
static VSTRING *query;
148
static VSTRING *result;
154
* Don't frustrate future attempts to make Postfix UTF-8 transparent.
156
if (!valid_utf_8(name, strlen(name))) {
158
msg_info("%s: %s: Skipping lookup of non-UTF-8 key '%s'",
159
myname, dict_sqlite->parser->name, name);
164
* Optionally fold the key. Folding may be enabled on on-the-fly.
166
if (dict->flags & DICT_FLAG_FOLD_FIX) {
167
if (dict->fold_buf == 0)
168
dict->fold_buf = vstring_alloc(100);
169
vstring_strcpy(dict->fold_buf, name);
170
name = lowercase(vstring_str(dict->fold_buf));
174
* Apply the optional domain filter for email address lookups.
176
if (db_common_check_domain(dict_sqlite->ctx, name) == 0) {
178
msg_info("%s: %s: Skipping lookup of '%s'",
179
myname, dict_sqlite->parser->name, name);
184
* Expand the query and query the database.
186
#define INIT_VSTR(buf, len) do { \
188
buf = vstring_alloc(len); \
189
VSTRING_RESET(buf); \
190
VSTRING_TERMINATE(buf); \
193
INIT_VSTR(query, 10);
195
if (!db_common_expand(dict_sqlite->ctx, dict_sqlite->query,
196
name, 0, query, dict_sqlite_quote))
200
msg_info("%s: %s: Searching with query %s",
201
myname, dict_sqlite->parser->name, vstring_str(query));
203
if (sqlite3_prepare_v2(dict_sqlite->db, vstring_str(query), -1,
204
&sql_stmt, &query_remainder) != SQLITE_OK)
205
msg_fatal("%s: %s: SQL prepare failed: %s\n",
206
myname, dict_sqlite->parser->name,
207
sqlite3_errmsg(dict_sqlite->db));
209
if (*query_remainder && msg_verbose)
210
msg_info("%s: %s: Ignoring text at end of query: %s",
211
myname, dict_sqlite->parser->name, query_remainder);
214
* Retrieve and expand the result(s).
216
INIT_VSTR(result, 10);
217
while ((status = sqlite3_step(sql_stmt)) != SQLITE_DONE) {
218
if (status == SQLITE_ROW) {
219
if (db_common_expand(dict_sqlite->ctx, dict_sqlite->result_format,
220
(char *) sqlite3_column_text(sql_stmt, 0),
222
&& dict_sqlite->expansion_limit > 0
223
&& ++expansion > dict_sqlite->expansion_limit) {
224
msg_warn("%s: %s: Expansion limit exceeded for key '%s'",
225
myname, dict_sqlite->parser->name, name);
226
dict_errno = DICT_ERR_RETRY;
232
msg_warn("%s: %s: SQL step failed for query '%s': %s\n",
233
myname, dict_sqlite->parser->name,
234
vstring_str(query), sqlite3_errmsg(dict_sqlite->db));
235
dict_errno = DICT_ERR_RETRY;
243
if (sqlite3_finalize(sql_stmt))
244
msg_fatal("%s: %s: SQL finalize failed for query '%s': %s\n",
245
myname, dict_sqlite->parser->name,
246
vstring_str(query), sqlite3_errmsg(dict_sqlite->db));
248
return ((dict_errno == 0 && *(retval = vstring_str(result)) != 0) ?
252
/* sqlite_parse_config - parse sqlite configuration file */
254
static void sqlite_parse_config(DICT_SQLITE *dict_sqlite, const char *sqlitecf)
259
* Parse the primary configuration parameters, and emulate the legacy
260
* query interface if necessary. This simplifies migration from one SQL
261
* database type to another.
263
dict_sqlite->parser = cfg_parser_alloc(sqlitecf);
264
dict_sqlite->dbpath = cfg_get_str(dict_sqlite->parser, "dbpath", "", 1, 0);
265
dict_sqlite->query = cfg_get_str(dict_sqlite->parser, "query", NULL, 0, 0);
266
if (dict_sqlite->query == 0) {
267
buf = vstring_alloc(100);
268
db_common_sql_build_query(buf, dict_sqlite->parser);
269
dict_sqlite->query = vstring_export(buf);
271
dict_sqlite->result_format =
272
cfg_get_str(dict_sqlite->parser, "result_format", "%s", 1, 0);
273
dict_sqlite->expansion_limit =
274
cfg_get_int(dict_sqlite->parser, "expansion_limit", 0, 0, 0);
277
* Parse the query / result templates and the optional domain filter.
279
dict_sqlite->ctx = 0;
280
(void) db_common_parse(&dict_sqlite->dict, &dict_sqlite->ctx,
281
dict_sqlite->query, 1);
282
(void) db_common_parse(0, &dict_sqlite->ctx, dict_sqlite->result_format, 0);
283
db_common_parse_domain(dict_sqlite->parser, dict_sqlite->ctx);
286
* Maps that use substring keys should only be used with the full input
289
if (db_common_dict_partial(dict_sqlite->ctx))
290
dict_sqlite->dict.flags |= DICT_FLAG_PATTERN;
292
dict_sqlite->dict.flags |= DICT_FLAG_FIXED;
295
/* dict_sqlite_open - open sqlite database */
297
DICT *dict_sqlite_open(const char *name, int open_flags, int dict_flags)
299
DICT_SQLITE *dict_sqlite;
304
if (open_flags != O_RDONLY)
305
msg_fatal("%s:%s map requires O_RDONLY access mode",
306
DICT_TYPE_SQLITE, name);
308
dict_sqlite = (DICT_SQLITE *) dict_alloc(DICT_TYPE_SQLITE, name,
309
sizeof(DICT_SQLITE));
310
dict_sqlite->dict.lookup = dict_sqlite_lookup;
311
dict_sqlite->dict.close = dict_sqlite_close;
312
dict_sqlite->dict.flags = dict_flags;
314
sqlite_parse_config(dict_sqlite, name);
316
if (sqlite3_open(dict_sqlite->dbpath, &dict_sqlite->db))
317
msg_fatal("%s:%s: Can't open database: %s\n",
318
DICT_TYPE_SQLITE, name, sqlite3_errmsg(dict_sqlite->db));
320
return (DICT_DEBUG (&dict_sqlite->dict));