1 /* $NetBSD: dict_sqlite.c,v 1.1.1.3 2013/01/02 18:58:57 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->error=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 99 typedef 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 112 static 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 126 static 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 149 static 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 int domain_rc; 161 162 /* 163 * In case of return without lookup (skipped key, etc.). 164 */ 165 dict->error = 0; 166 167 /* 168 * Don't frustrate future attempts to make Postfix UTF-8 transparent. 169 */ 170 if (!valid_utf_8(name, strlen(name))) { 171 if (msg_verbose) 172 msg_info("%s: %s: Skipping lookup of non-UTF-8 key '%s'", 173 myname, dict_sqlite->parser->name, name); 174 return (0); 175 } 176 177 /* 178 * Optionally fold the key. Folding may be enabled on on-the-fly. 179 */ 180 if (dict->flags & DICT_FLAG_FOLD_FIX) { 181 if (dict->fold_buf == 0) 182 dict->fold_buf = vstring_alloc(100); 183 vstring_strcpy(dict->fold_buf, name); 184 name = lowercase(vstring_str(dict->fold_buf)); 185 } 186 187 /* 188 * Apply the optional domain filter for email address lookups. 189 */ 190 if ((domain_rc = db_common_check_domain(dict_sqlite->ctx, name)) == 0) { 191 if (msg_verbose) 192 msg_info("%s: %s: Skipping lookup of '%s'", 193 myname, dict_sqlite->parser->name, name); 194 return (0); 195 } 196 if (domain_rc < 0) 197 DICT_ERR_VAL_RETURN(dict, domain_rc, (char *) 0); 198 199 /* 200 * Expand the query and query the database. 201 */ 202 #define INIT_VSTR(buf, len) do { \ 203 if (buf == 0) \ 204 buf = vstring_alloc(len); \ 205 VSTRING_RESET(buf); \ 206 VSTRING_TERMINATE(buf); \ 207 } while (0) 208 209 INIT_VSTR(query, 10); 210 211 if (!db_common_expand(dict_sqlite->ctx, dict_sqlite->query, 212 name, 0, query, dict_sqlite_quote)) 213 return (0); 214 215 if (msg_verbose) 216 msg_info("%s: %s: Searching with query %s", 217 myname, dict_sqlite->parser->name, vstring_str(query)); 218 219 if (sqlite3_prepare_v2(dict_sqlite->db, vstring_str(query), -1, 220 &sql_stmt, &query_remainder) != SQLITE_OK) 221 msg_fatal("%s: %s: SQL prepare failed: %s\n", 222 myname, dict_sqlite->parser->name, 223 sqlite3_errmsg(dict_sqlite->db)); 224 225 if (*query_remainder && msg_verbose) 226 msg_info("%s: %s: Ignoring text at end of query: %s", 227 myname, dict_sqlite->parser->name, query_remainder); 228 229 /* 230 * Retrieve and expand the result(s). 231 */ 232 INIT_VSTR(result, 10); 233 while ((status = sqlite3_step(sql_stmt)) != SQLITE_DONE) { 234 if (status == SQLITE_ROW) { 235 if (db_common_expand(dict_sqlite->ctx, dict_sqlite->result_format, 236 (char *) sqlite3_column_text(sql_stmt, 0), 237 name, result, 0) 238 && dict_sqlite->expansion_limit > 0 239 && ++expansion > dict_sqlite->expansion_limit) { 240 msg_warn("%s: %s: Expansion limit exceeded for key '%s'", 241 myname, dict_sqlite->parser->name, name); 242 dict->error = DICT_ERR_RETRY; 243 break; 244 } 245 } 246 /* Fix 20100616 */ 247 else { 248 msg_warn("%s: %s: SQL step failed for query '%s': %s\n", 249 myname, dict_sqlite->parser->name, 250 vstring_str(query), sqlite3_errmsg(dict_sqlite->db)); 251 dict->error = DICT_ERR_RETRY; 252 break; 253 } 254 } 255 256 /* 257 * Clean up. 258 */ 259 if (sqlite3_finalize(sql_stmt)) 260 msg_fatal("%s: %s: SQL finalize failed for query '%s': %s\n", 261 myname, dict_sqlite->parser->name, 262 vstring_str(query), sqlite3_errmsg(dict_sqlite->db)); 263 264 return ((dict->error == 0 && *(retval = vstring_str(result)) != 0) ? 265 retval : 0); 266 } 267 268 /* sqlite_parse_config - parse sqlite configuration file */ 269 270 static void sqlite_parse_config(DICT_SQLITE *dict_sqlite, const char *sqlitecf) 271 { 272 VSTRING *buf; 273 274 /* 275 * Parse the primary configuration parameters, and emulate the legacy 276 * query interface if necessary. This simplifies migration from one SQL 277 * database type to another. 278 */ 279 dict_sqlite->dbpath = cfg_get_str(dict_sqlite->parser, "dbpath", "", 1, 0); 280 dict_sqlite->query = cfg_get_str(dict_sqlite->parser, "query", NULL, 0, 0); 281 if (dict_sqlite->query == 0) { 282 buf = vstring_alloc(100); 283 db_common_sql_build_query(buf, dict_sqlite->parser); 284 dict_sqlite->query = vstring_export(buf); 285 } 286 dict_sqlite->result_format = 287 cfg_get_str(dict_sqlite->parser, "result_format", "%s", 1, 0); 288 dict_sqlite->expansion_limit = 289 cfg_get_int(dict_sqlite->parser, "expansion_limit", 0, 0, 0); 290 291 /* 292 * Parse the query / result templates and the optional domain filter. 293 */ 294 dict_sqlite->ctx = 0; 295 (void) db_common_parse(&dict_sqlite->dict, &dict_sqlite->ctx, 296 dict_sqlite->query, 1); 297 (void) db_common_parse(0, &dict_sqlite->ctx, dict_sqlite->result_format, 0); 298 db_common_parse_domain(dict_sqlite->parser, dict_sqlite->ctx); 299 300 /* 301 * Maps that use substring keys should only be used with the full input 302 * key. 303 */ 304 if (db_common_dict_partial(dict_sqlite->ctx)) 305 dict_sqlite->dict.flags |= DICT_FLAG_PATTERN; 306 else 307 dict_sqlite->dict.flags |= DICT_FLAG_FIXED; 308 } 309 310 /* dict_sqlite_open - open sqlite database */ 311 312 DICT *dict_sqlite_open(const char *name, int open_flags, int dict_flags) 313 { 314 DICT_SQLITE *dict_sqlite; 315 CFG_PARSER *parser; 316 317 /* 318 * Sanity checks. 319 */ 320 if (open_flags != O_RDONLY) 321 return (dict_surrogate(DICT_TYPE_SQLITE, name, open_flags, dict_flags, 322 "%s:%s map requires O_RDONLY access mode", 323 DICT_TYPE_SQLITE, name)); 324 325 /* 326 * Open the configuration file. 327 */ 328 if ((parser = cfg_parser_alloc(name)) == 0) 329 return (dict_surrogate(DICT_TYPE_SQLITE, name, open_flags, dict_flags, 330 "open %s: %m", name)); 331 332 dict_sqlite = (DICT_SQLITE *) dict_alloc(DICT_TYPE_SQLITE, name, 333 sizeof(DICT_SQLITE)); 334 dict_sqlite->dict.lookup = dict_sqlite_lookup; 335 dict_sqlite->dict.close = dict_sqlite_close; 336 dict_sqlite->dict.flags = dict_flags; 337 338 dict_sqlite->parser = parser; 339 sqlite_parse_config(dict_sqlite, name); 340 341 if (sqlite3_open(dict_sqlite->dbpath, &dict_sqlite->db)) 342 msg_fatal("%s:%s: Can't open database: %s\n", 343 DICT_TYPE_SQLITE, name, sqlite3_errmsg(dict_sqlite->db)); 344 345 dict_sqlite->dict.owner = cfg_get_owner(dict_sqlite->parser); 346 347 return (DICT_DEBUG (&dict_sqlite->dict)); 348 } 349 350 #endif 351