1 /* $NetBSD: dict_sqlite.c,v 1.1.1.1 2011/03/02 19:32:14 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 64 /* System library. */ 65 66 #include <sys_defs.h> 67 #include <string.h> 68 69 #ifdef HAS_SQLITE 70 #include <sqlite3.h> 71 72 #if !defined(SQLITE_VERSION_NUMBER) || (SQLITE_VERSION_NUMBER < 3005004) 73 #define sqlite3_prepare_v2 sqlite3_prepare 74 #endif 75 76 /* Utility library. */ 77 78 #include <msg.h> 79 #include <dict.h> 80 #include <vstring.h> 81 #include <stringops.h> 82 #include <mymalloc.h> 83 84 /* Global library. */ 85 86 #include <cfg_parser.h> 87 #include <db_common.h> 88 89 /* Application-specific. */ 90 91 #include <dict_sqlite.h> 92 93 typedef struct { 94 DICT dict; /* generic member */ 95 CFG_PARSER *parser; /* common parameter parser */ 96 sqlite3 *db; /* sqlite handle */ 97 char *query; /* db_common_expand() query */ 98 char *result_format; /* db_common_expand() result_format */ 99 void *ctx; /* db_common_parse() context */ 100 char *dbpath; /* dbpath config attribute */ 101 int expansion_limit; /* expansion_limit config attribute */ 102 } DICT_SQLITE; 103 104 /* dict_sqlite_quote - escape SQL metacharacters in input string */ 105 106 static void dict_sqlite_quote(DICT *dict, const char *raw_text, VSTRING *result) 107 { 108 char *quoted_text; 109 110 quoted_text = sqlite3_mprintf("%q", raw_text); 111 /* Fix 20100616 */ 112 if (quoted_text == 0) 113 msg_fatal("dict_sqlite_quote: out of memory"); 114 vstring_strcat(result, raw_text); 115 sqlite3_free(quoted_text); 116 } 117 118 /* dict_sqlite_close - close the database */ 119 120 static void dict_sqlite_close(DICT *dict) 121 { 122 const char *myname = "dict_sqlite_close"; 123 DICT_SQLITE *dict_sqlite = (DICT_SQLITE *) dict; 124 125 if (msg_verbose) 126 msg_info("%s: %s", myname, dict_sqlite->parser->name); 127 128 if (sqlite3_close(dict_sqlite->db) != SQLITE_OK) 129 msg_fatal("%s: close %s failed", myname, dict_sqlite->parser->name); 130 cfg_parser_free(dict_sqlite->parser); 131 myfree(dict_sqlite->dbpath); 132 myfree(dict_sqlite->query); 133 myfree(dict_sqlite->result_format); 134 if (dict_sqlite->ctx) 135 db_common_free_ctx(dict_sqlite->ctx); 136 if (dict->fold_buf) 137 vstring_free(dict->fold_buf); 138 dict_free(dict); 139 } 140 141 /* dict_sqlite_lookup - find database entry */ 142 143 static const char *dict_sqlite_lookup(DICT *dict, const char *name) 144 { 145 const char *myname = "dict_sqlite_lookup"; 146 DICT_SQLITE *dict_sqlite = (DICT_SQLITE *) dict; 147 sqlite3_stmt *sql_stmt; 148 const char *query_remainder; 149 static VSTRING *query; 150 static VSTRING *result; 151 const char *retval; 152 int expansion = 0; 153 int status; 154 155 /* 156 * Don't frustrate future attempts to make Postfix UTF-8 transparent. 157 */ 158 if (!valid_utf_8(name, strlen(name))) { 159 if (msg_verbose) 160 msg_info("%s: %s: Skipping lookup of non-UTF-8 key '%s'", 161 myname, dict_sqlite->parser->name, name); 162 return (0); 163 } 164 165 /* 166 * Optionally fold the key. Folding may be enabled on on-the-fly. 167 */ 168 if (dict->flags & DICT_FLAG_FOLD_FIX) { 169 if (dict->fold_buf == 0) 170 dict->fold_buf = vstring_alloc(100); 171 vstring_strcpy(dict->fold_buf, name); 172 name = lowercase(vstring_str(dict->fold_buf)); 173 } 174 175 /* 176 * Apply the optional domain filter for email address lookups. 177 */ 178 if (db_common_check_domain(dict_sqlite->ctx, name) == 0) { 179 if (msg_verbose) 180 msg_info("%s: %s: Skipping lookup of '%s'", 181 myname, dict_sqlite->parser->name, name); 182 return (0); 183 } 184 185 /* 186 * Expand the query and query the database. 187 */ 188 #define INIT_VSTR(buf, len) do { \ 189 if (buf == 0) \ 190 buf = vstring_alloc(len); \ 191 VSTRING_RESET(buf); \ 192 VSTRING_TERMINATE(buf); \ 193 } while (0) 194 195 INIT_VSTR(query, 10); 196 197 if (!db_common_expand(dict_sqlite->ctx, dict_sqlite->query, 198 name, 0, query, dict_sqlite_quote)) 199 return (0); 200 201 if (msg_verbose) 202 msg_info("%s: %s: Searching with query %s", 203 myname, dict_sqlite->parser->name, vstring_str(query)); 204 205 if (sqlite3_prepare_v2(dict_sqlite->db, vstring_str(query), -1, 206 &sql_stmt, &query_remainder) != SQLITE_OK) 207 msg_fatal("%s: %s: SQL prepare failed: %s\n", 208 myname, dict_sqlite->parser->name, 209 sqlite3_errmsg(dict_sqlite->db)); 210 211 if (*query_remainder && msg_verbose) 212 msg_info("%s: %s: Ignoring text at end of query: %s", 213 myname, dict_sqlite->parser->name, query_remainder); 214 215 /* 216 * Retrieve and expand the result(s). 217 */ 218 INIT_VSTR(result, 10); 219 while ((status = sqlite3_step(sql_stmt)) != SQLITE_DONE) { 220 if (status == SQLITE_ROW) { 221 if (db_common_expand(dict_sqlite->ctx, dict_sqlite->result_format, 222 (char *) sqlite3_column_text(sql_stmt, 0), 223 name, result, 0) 224 && dict_sqlite->expansion_limit > 0 225 && ++expansion > dict_sqlite->expansion_limit) { 226 msg_warn("%s: %s: Expansion limit exceeded for key '%s'", 227 myname, dict_sqlite->parser->name, name); 228 dict_errno = DICT_ERR_RETRY; 229 break; 230 } 231 } 232 /* Fix 20100616 */ 233 else { 234 msg_warn("%s: %s: SQL step failed for query '%s': %s\n", 235 myname, dict_sqlite->parser->name, 236 vstring_str(query), sqlite3_errmsg(dict_sqlite->db)); 237 dict_errno = DICT_ERR_RETRY; 238 break; 239 } 240 } 241 242 /* 243 * Clean up. 244 */ 245 if (sqlite3_finalize(sql_stmt)) 246 msg_fatal("%s: %s: SQL finalize failed for query '%s': %s\n", 247 myname, dict_sqlite->parser->name, 248 vstring_str(query), sqlite3_errmsg(dict_sqlite->db)); 249 250 return ((dict_errno == 0 && *(retval = vstring_str(result)) != 0) ? 251 retval : 0); 252 } 253 254 /* sqlite_parse_config - parse sqlite configuration file */ 255 256 static void sqlite_parse_config(DICT_SQLITE *dict_sqlite, const char *sqlitecf) 257 { 258 VSTRING *buf; 259 260 /* 261 * Parse the primary configuration parameters, and emulate the legacy 262 * query interface if necessary. This simplifies migration from one SQL 263 * database type to another. 264 */ 265 dict_sqlite->parser = cfg_parser_alloc(sqlitecf); 266 dict_sqlite->dbpath = cfg_get_str(dict_sqlite->parser, "dbpath", "", 1, 0); 267 dict_sqlite->query = cfg_get_str(dict_sqlite->parser, "query", NULL, 0, 0); 268 if (dict_sqlite->query == 0) { 269 buf = vstring_alloc(100); 270 db_common_sql_build_query(buf, dict_sqlite->parser); 271 dict_sqlite->query = vstring_export(buf); 272 } 273 dict_sqlite->result_format = 274 cfg_get_str(dict_sqlite->parser, "result_format", "%s", 1, 0); 275 dict_sqlite->expansion_limit = 276 cfg_get_int(dict_sqlite->parser, "expansion_limit", 0, 0, 0); 277 278 /* 279 * Parse the query / result templates and the optional domain filter. 280 */ 281 dict_sqlite->ctx = 0; 282 (void) db_common_parse(&dict_sqlite->dict, &dict_sqlite->ctx, 283 dict_sqlite->query, 1); 284 (void) db_common_parse(0, &dict_sqlite->ctx, dict_sqlite->result_format, 0); 285 db_common_parse_domain(dict_sqlite->parser, dict_sqlite->ctx); 286 287 /* 288 * Maps that use substring keys should only be used with the full input 289 * key. 290 */ 291 if (db_common_dict_partial(dict_sqlite->ctx)) 292 dict_sqlite->dict.flags |= DICT_FLAG_PATTERN; 293 else 294 dict_sqlite->dict.flags |= DICT_FLAG_FIXED; 295 } 296 297 /* dict_sqlite_open - open sqlite database */ 298 299 DICT *dict_sqlite_open(const char *name, int open_flags, int dict_flags) 300 { 301 DICT_SQLITE *dict_sqlite; 302 303 /* 304 * Sanity checks. 305 */ 306 if (open_flags != O_RDONLY) 307 msg_fatal("%s:%s map requires O_RDONLY access mode", 308 DICT_TYPE_SQLITE, name); 309 310 dict_sqlite = (DICT_SQLITE *) dict_alloc(DICT_TYPE_SQLITE, name, 311 sizeof(DICT_SQLITE)); 312 dict_sqlite->dict.lookup = dict_sqlite_lookup; 313 dict_sqlite->dict.close = dict_sqlite_close; 314 dict_sqlite->dict.flags = dict_flags; 315 316 sqlite_parse_config(dict_sqlite, name); 317 318 if (sqlite3_open(dict_sqlite->dbpath, &dict_sqlite->db)) 319 msg_fatal("%s:%s: Can't open database: %s\n", 320 DICT_TYPE_SQLITE, name, sqlite3_errmsg(dict_sqlite->db)); 321 322 return (DICT_DEBUG (&dict_sqlite->dict)); 323 } 324 325 #endif 326