1/*	$NetBSD: dict_sqlite.c,v 1.1.1.2 2012/02/17 08:36:07 tron Exp $	*/
2
3/*++
4/* NAME
5/*	dict_sqlite 3
6/* SUMMARY
7/*	dictionary manager interface to SQLite3 databases
8/* SYNOPSIS
9/*	#include <dict_sqlite.h>
10/*
11/*	DICT	*dict_sqlite_open(name, open_flags, dict_flags)
12/*	const char *name;
13/*	int	open_flags;
14/*	int	dict_flags;
15/* DESCRIPTION
16/*	dict_sqlite_open() creates a dictionary of type 'sqlite'.
17/*	This dictionary is an interface for the postfix key->value
18/*	mappings to SQLite.  The result is a pointer to the installed
19/*	dictionary.
20/* .PP
21/*	Arguments:
22/* .IP name
23/*	Either the path to the SQLite configuration file (if it
24/*	starts with '/' or '.'), or the prefix which will be used
25/*	to obtain main.cf configuration parameters for this search.
26/*
27/*	In the first case, the configuration parameters below are
28/*	specified in the file as \fIname\fR=\fIvalue\fR pairs.
29/*
30/*	In the second case, the configuration parameters are prefixed
31/*	with the value of \fIname\fR and an underscore, and they
32/*	are specified in main.cf.  For example, if this value is
33/*	\fIsqlitecon\fR, the parameters would look like
34/*	\fIsqlitecon_dbpath\fR, \fIsqlitecon_query\fR, and so on.
35/* .IP open_flags
36/*	Must be O_RDONLY.
37/* .IP dict_flags
38/*	See dict_open(3).
39/* .PP
40/*	Configuration parameters:
41/* .IP dbpath
42/*	Path to SQLite database
43/* .IP query
44/*	Query template. Before the query is actually issued, variable
45/*	substitutions are performed. See sqlite_table(5) for details.
46/* .IP result_format
47/*	The format used to expand results from queries.  Substitutions
48/*	are performed as described in sqlite_table(5). Defaults to
49/*	returning the lookup result unchanged.
50/* .IP expansion_limit
51/*	Limit (if any) on the total number of lookup result values.
52/*	Lookups which exceed the limit fail with dict_errno=DICT_ERR_RETRY.
53/*	Note that each non-empty (and non-NULL) column of a
54/*	multi-column result row counts as one result.
55/* .IP "select_field, where_field, additional_conditions"
56/*	Legacy query interface.
57/* SEE ALSO
58/*	dict(3) generic dictionary manager
59/* AUTHOR(S)
60/*	Axel Steiner
61/*	ast@treibsand.com
62/*
63/*	Adopted and updated by:
64/*	Wietse Venema
65/*	IBM T.J. Watson Research
66/*	P.O. Box 704
67/*	Yorktown Heights, NY 10598, USA
68/*--*/
69
70/* System library. */
71
72#include <sys_defs.h>
73#include <string.h>
74
75#ifdef HAS_SQLITE
76#include <sqlite3.h>
77
78#if !defined(SQLITE_VERSION_NUMBER) || (SQLITE_VERSION_NUMBER < 3005004)
79#define sqlite3_prepare_v2 sqlite3_prepare
80#endif
81
82/* Utility library. */
83
84#include <msg.h>
85#include <dict.h>
86#include <vstring.h>
87#include <stringops.h>
88#include <mymalloc.h>
89
90/* Global library. */
91
92#include <cfg_parser.h>
93#include <db_common.h>
94
95/* Application-specific. */
96
97#include <dict_sqlite.h>
98
99typedef struct {
100    DICT    dict;			/* generic member */
101    CFG_PARSER *parser;			/* common parameter parser */
102    sqlite3 *db;			/* sqlite handle */
103    char   *query;			/* db_common_expand() query */
104    char   *result_format;		/* db_common_expand() result_format */
105    void   *ctx;			/* db_common_parse() context */
106    char   *dbpath;			/* dbpath config attribute */
107    int     expansion_limit;		/* expansion_limit config attribute */
108} DICT_SQLITE;
109
110/* dict_sqlite_quote - escape SQL metacharacters in input string */
111
112static void dict_sqlite_quote(DICT *dict, const char *raw_text, VSTRING *result)
113{
114    char   *quoted_text;
115
116    quoted_text = sqlite3_mprintf("%q", raw_text);
117    /* Fix 20100616 */
118    if (quoted_text == 0)
119	msg_fatal("dict_sqlite_quote: out of memory");
120    vstring_strcat(result, quoted_text);
121    sqlite3_free(quoted_text);
122}
123
124/* dict_sqlite_close - close the database */
125
126static void dict_sqlite_close(DICT *dict)
127{
128    const char *myname = "dict_sqlite_close";
129    DICT_SQLITE *dict_sqlite = (DICT_SQLITE *) dict;
130
131    if (msg_verbose)
132	msg_info("%s: %s", myname, dict_sqlite->parser->name);
133
134    if (sqlite3_close(dict_sqlite->db) != SQLITE_OK)
135	msg_fatal("%s: close %s failed", myname, dict_sqlite->parser->name);
136    cfg_parser_free(dict_sqlite->parser);
137    myfree(dict_sqlite->dbpath);
138    myfree(dict_sqlite->query);
139    myfree(dict_sqlite->result_format);
140    if (dict_sqlite->ctx)
141	db_common_free_ctx(dict_sqlite->ctx);
142    if (dict->fold_buf)
143	vstring_free(dict->fold_buf);
144    dict_free(dict);
145}
146
147/* dict_sqlite_lookup - find database entry */
148
149static const char *dict_sqlite_lookup(DICT *dict, const char *name)
150{
151    const char *myname = "dict_sqlite_lookup";
152    DICT_SQLITE *dict_sqlite = (DICT_SQLITE *) dict;
153    sqlite3_stmt *sql_stmt;
154    const char *query_remainder;
155    static VSTRING *query;
156    static VSTRING *result;
157    const char *retval;
158    int     expansion = 0;
159    int     status;
160
161    /*
162     * In case of return without lookup (skipped key, etc.).
163     */
164    dict_errno = 0;
165
166    /*
167     * Don't frustrate future attempts to make Postfix UTF-8 transparent.
168     */
169    if (!valid_utf_8(name, strlen(name))) {
170	if (msg_verbose)
171	    msg_info("%s: %s: Skipping lookup of non-UTF-8 key '%s'",
172		     myname, dict_sqlite->parser->name, name);
173	return (0);
174    }
175
176    /*
177     * Optionally fold the key. Folding may be enabled on on-the-fly.
178     */
179    if (dict->flags & DICT_FLAG_FOLD_FIX) {
180	if (dict->fold_buf == 0)
181	    dict->fold_buf = vstring_alloc(100);
182	vstring_strcpy(dict->fold_buf, name);
183	name = lowercase(vstring_str(dict->fold_buf));
184    }
185
186    /*
187     * Apply the optional domain filter for email address lookups.
188     */
189    if (db_common_check_domain(dict_sqlite->ctx, name) == 0) {
190	if (msg_verbose)
191	    msg_info("%s: %s: Skipping lookup of '%s'",
192		     myname, dict_sqlite->parser->name, name);
193	return (0);
194    }
195
196    /*
197     * Expand the query and query the database.
198     */
199#define INIT_VSTR(buf, len) do { \
200	if (buf == 0) \
201		buf = vstring_alloc(len); \
202	VSTRING_RESET(buf); \
203	VSTRING_TERMINATE(buf); \
204    } while (0)
205
206    INIT_VSTR(query, 10);
207
208    if (!db_common_expand(dict_sqlite->ctx, dict_sqlite->query,
209			  name, 0, query, dict_sqlite_quote))
210	return (0);
211
212    if (msg_verbose)
213	msg_info("%s: %s: Searching with query %s",
214		 myname, dict_sqlite->parser->name, vstring_str(query));
215
216    if (sqlite3_prepare_v2(dict_sqlite->db, vstring_str(query), -1,
217			   &sql_stmt, &query_remainder) != SQLITE_OK)
218	msg_fatal("%s: %s: SQL prepare failed: %s\n",
219		  myname, dict_sqlite->parser->name,
220		  sqlite3_errmsg(dict_sqlite->db));
221
222    if (*query_remainder && msg_verbose)
223	msg_info("%s: %s: Ignoring text at end of query: %s",
224		 myname, dict_sqlite->parser->name, query_remainder);
225
226    /*
227     * Retrieve and expand the result(s).
228     */
229    INIT_VSTR(result, 10);
230    while ((status = sqlite3_step(sql_stmt)) != SQLITE_DONE) {
231	if (status == SQLITE_ROW) {
232	    if (db_common_expand(dict_sqlite->ctx, dict_sqlite->result_format,
233				 (char *) sqlite3_column_text(sql_stmt, 0),
234				 name, result, 0)
235		&& dict_sqlite->expansion_limit > 0
236		&& ++expansion > dict_sqlite->expansion_limit) {
237		msg_warn("%s: %s: Expansion limit exceeded for key '%s'",
238			 myname, dict_sqlite->parser->name, name);
239		dict_errno = DICT_ERR_RETRY;
240		break;
241	    }
242	}
243	/* Fix 20100616 */
244	else {
245	    msg_warn("%s: %s: SQL step failed for query '%s': %s\n",
246		     myname, dict_sqlite->parser->name,
247		     vstring_str(query), sqlite3_errmsg(dict_sqlite->db));
248	    dict_errno = DICT_ERR_RETRY;
249	    break;
250	}
251    }
252
253    /*
254     * Clean up.
255     */
256    if (sqlite3_finalize(sql_stmt))
257	msg_fatal("%s: %s: SQL finalize failed for query '%s': %s\n",
258		  myname, dict_sqlite->parser->name,
259		  vstring_str(query), sqlite3_errmsg(dict_sqlite->db));
260
261    return ((dict_errno == 0 && *(retval = vstring_str(result)) != 0) ?
262	    retval : 0);
263}
264
265/* sqlite_parse_config - parse sqlite configuration file */
266
267static void sqlite_parse_config(DICT_SQLITE *dict_sqlite, const char *sqlitecf)
268{
269    VSTRING *buf;
270
271    /*
272     * Parse the primary configuration parameters, and emulate the legacy
273     * query interface if necessary. This simplifies migration from one SQL
274     * database type to another.
275     */
276    dict_sqlite->parser = cfg_parser_alloc(sqlitecf);
277    dict_sqlite->dbpath = cfg_get_str(dict_sqlite->parser, "dbpath", "", 1, 0);
278    dict_sqlite->query = cfg_get_str(dict_sqlite->parser, "query", NULL, 0, 0);
279    if (dict_sqlite->query == 0) {
280	buf = vstring_alloc(100);
281	db_common_sql_build_query(buf, dict_sqlite->parser);
282	dict_sqlite->query = vstring_export(buf);
283    }
284    dict_sqlite->result_format =
285	cfg_get_str(dict_sqlite->parser, "result_format", "%s", 1, 0);
286    dict_sqlite->expansion_limit =
287	cfg_get_int(dict_sqlite->parser, "expansion_limit", 0, 0, 0);
288
289    /*
290     * Parse the query / result templates and the optional domain filter.
291     */
292    dict_sqlite->ctx = 0;
293    (void) db_common_parse(&dict_sqlite->dict, &dict_sqlite->ctx,
294			   dict_sqlite->query, 1);
295    (void) db_common_parse(0, &dict_sqlite->ctx, dict_sqlite->result_format, 0);
296    db_common_parse_domain(dict_sqlite->parser, dict_sqlite->ctx);
297
298    /*
299     * Maps that use substring keys should only be used with the full input
300     * key.
301     */
302    if (db_common_dict_partial(dict_sqlite->ctx))
303	dict_sqlite->dict.flags |= DICT_FLAG_PATTERN;
304    else
305	dict_sqlite->dict.flags |= DICT_FLAG_FIXED;
306}
307
308/* dict_sqlite_open - open sqlite database */
309
310DICT   *dict_sqlite_open(const char *name, int open_flags, int dict_flags)
311{
312    DICT_SQLITE *dict_sqlite;
313
314    /*
315     * Sanity checks.
316     */
317    if (open_flags != O_RDONLY)
318	msg_fatal("%s:%s map requires O_RDONLY access mode",
319		  DICT_TYPE_SQLITE, name);
320
321    dict_sqlite = (DICT_SQLITE *) dict_alloc(DICT_TYPE_SQLITE, name,
322					     sizeof(DICT_SQLITE));
323    dict_sqlite->dict.lookup = dict_sqlite_lookup;
324    dict_sqlite->dict.close = dict_sqlite_close;
325    dict_sqlite->dict.flags = dict_flags;
326
327    sqlite_parse_config(dict_sqlite, name);
328
329    if (sqlite3_open(dict_sqlite->dbpath, &dict_sqlite->db))
330	msg_fatal("%s:%s: Can't open database: %s\n",
331		  DICT_TYPE_SQLITE, name, sqlite3_errmsg(dict_sqlite->db));
332
333    return (DICT_DEBUG (&dict_sqlite->dict));
334}
335
336#endif
337