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