Kannel: Open Source WAP and SMS gateway  $Revision: 5037 $
dlr_mysql.c
Go to the documentation of this file.
1 /* ====================================================================
2  * The Kannel Software License, Version 1.0
3  *
4  * Copyright (c) 2001-2016 Kannel Group
5  * Copyright (c) 1998-2001 WapIT Ltd.
6  * All rights reserved.
7  *
8  * Redistribution and use in source and binary forms, with or without
9  * modification, are permitted provided that the following conditions
10  * are met:
11  *
12  * 1. Redistributions of source code must retain the above copyright
13  * notice, this list of conditions and the following disclaimer.
14  *
15  * 2. Redistributions in binary form must reproduce the above copyright
16  * notice, this list of conditions and the following disclaimer in
17  * the documentation and/or other materials provided with the
18  * distribution.
19  *
20  * 3. The end-user documentation included with the redistribution,
21  * if any, must include the following acknowledgment:
22  * "This product includes software developed by the
23  * Kannel Group (http://www.kannel.org/)."
24  * Alternately, this acknowledgment may appear in the software itself,
25  * if and wherever such third-party acknowledgments normally appear.
26  *
27  * 4. The names "Kannel" and "Kannel Group" must not be used to
28  * endorse or promote products derived from this software without
29  * prior written permission. For written permission, please
30  * contact org@kannel.org.
31  *
32  * 5. Products derived from this software may not be called "Kannel",
33  * nor may "Kannel" appear in their name, without prior written
34  * permission of the Kannel Group.
35  *
36  * THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESSED OR IMPLIED
37  * WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES
38  * OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
39  * DISCLAIMED. IN NO EVENT SHALL THE KANNEL GROUP OR ITS CONTRIBUTORS
40  * BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY,
41  * OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT
42  * OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR
43  * BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY,
44  * WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE
45  * OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE,
46  * EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
47  * ====================================================================
48  *
49  * This software consists of voluntary contributions made by many
50  * individuals on behalf of the Kannel Group. For more information on
51  * the Kannel Group, please see <http://www.kannel.org/>.
52  *
53  * Portions of this software are based upon software originally written at
54  * WapIT Ltd., Helsinki, Finland for the Kannel project.
55  */
56 
57 /*
58  * dlr_mysql.c
59  *
60  * Implementation of handling delivery reports (DLRs)
61  * for MySql database
62  *
63  * Andreas Fink <andreas@fink.org>, 18.08.2001
64  * Stipe Tolj <stolj@wapme.de>, 22.03.2002
65  * Alexander Malysh <a.malysh@centrium.de> 2003
66 */
67 
68 #include "gwlib/gwlib.h"
69 #include "gwlib/dbpool.h"
70 #include "dlr_p.h"
71 
72 
73 #ifdef HAVE_MYSQL
74 
75 /*
76  * Our connection pool to mysql.
77  */
78 static DBPool *pool = NULL;
79 
80 /*
81  * Database fields, which we are use.
82  */
83 static struct dlr_db_fields *fields = NULL;
84 
85 
86 static void dlr_mysql_shutdown()
87 {
88  dbpool_destroy(pool);
89  dlr_db_fields_destroy(fields);
90 }
91 
92 static void dlr_mysql_add(struct dlr_entry *entry)
93 {
94  Octstr *sql, *os_mask;
95  DBPoolConn *pconn;
96  List *binds = gwlist_create();
97  int res;
98 
99  debug("dlr.mysql", 0, "adding DLR entry into database");
100 
101  pconn = dbpool_conn_consume(pool);
102  /* just for sure */
103  if (pconn == NULL) {
104  dlr_entry_destroy(entry);
105  return;
106  }
107 
108  sql = octstr_format("INSERT INTO `%S` (`%S`, `%S`, `%S`, `%S`, `%S`, `%S`, `%S`, `%S`, `%S`) VALUES "
109  "(?, ?, ?, ?, ?, ?, ?, ?, 0)",
110  fields->table, fields->field_smsc, fields->field_ts,
111  fields->field_src, fields->field_dst, fields->field_serv,
112  fields->field_url, fields->field_mask, fields->field_boxc,
113  fields->field_status);
114  os_mask = octstr_format("%d", entry->mask);
115  gwlist_append(binds, entry->smsc);
116  gwlist_append(binds, entry->timestamp);
117  gwlist_append(binds, entry->source);
118  gwlist_append(binds, entry->destination);
119  gwlist_append(binds, entry->service);
120  gwlist_append(binds, entry->url);
121  gwlist_append(binds, os_mask);
122  gwlist_append(binds, entry->boxc_id);
123 
124 #if defined(DLR_TRACE)
125  debug("dlr.mysql", 0, "sql: %s", octstr_get_cstr(sql));
126 #endif
127  if ((res = dbpool_conn_update(pconn, sql, binds)) == -1)
128  error(0, "DLR: MYSQL: Error while adding dlr entry for DST<%s>", octstr_get_cstr(entry->destination));
129  else if (!res)
130  warning(0, "DLR: MYSQL: No dlr inserted for DST<%s>", octstr_get_cstr(entry->destination));
131 
132  dbpool_conn_produce(pconn);
133  octstr_destroy(sql);
134  gwlist_destroy(binds, NULL);
135  octstr_destroy(os_mask);
136  dlr_entry_destroy(entry);
137 }
138 
139 static struct dlr_entry* dlr_mysql_get(const Octstr *smsc, const Octstr *ts, const Octstr *dst)
140 {
141  Octstr *sql, *like;
142  DBPoolConn *pconn;
143  List *result = NULL, *row;
144  struct dlr_entry *res = NULL;
145  List *binds = gwlist_create();
146 
147  pconn = dbpool_conn_consume(pool);
148  if (pconn == NULL) /* should not happens, but sure is sure */
149  return NULL;
150 
151  if (dst)
152  like = octstr_format("AND `%S` LIKE CONCAT('%%', ?)", fields->field_dst);
153  else
154  like = octstr_imm("");
155 
156  sql = octstr_format("SELECT `%S`, `%S`, `%S`, `%S`, `%S`, `%S` FROM `%S` WHERE `%S`=? AND `%S`=? %S LIMIT 1",
157  fields->field_mask, fields->field_serv,
158  fields->field_url, fields->field_src,
159  fields->field_dst, fields->field_boxc,
160  fields->table, fields->field_smsc,
161  fields->field_ts, like);
162 
163  gwlist_append(binds, (Octstr *)smsc);
164  gwlist_append(binds, (Octstr *)ts);
165  if (dst)
166  gwlist_append(binds, (Octstr *)dst);
167 
168 #if defined(DLR_TRACE)
169  debug("dlr.mysql", 0, "sql: %s", octstr_get_cstr(sql));
170 #endif
171 
172  if (dbpool_conn_select(pconn, sql, binds, &result) != 0) {
173  octstr_destroy(sql);
174  octstr_destroy(like);
175  gwlist_destroy(binds, NULL);
176  dbpool_conn_produce(pconn);
177  return NULL;
178  }
179  octstr_destroy(sql);
180  octstr_destroy(like);
181  gwlist_destroy(binds, NULL);
182  dbpool_conn_produce(pconn);
183 
184 #define LO2CSTR(r, i) octstr_get_cstr(gwlist_get(r, i))
185 
186  if (gwlist_len(result) > 0) {
187  row = gwlist_extract_first(result);
188  res = dlr_entry_create();
189  gw_assert(res != NULL);
190  res->mask = atoi(LO2CSTR(row,0));
191  res->service = octstr_create(LO2CSTR(row, 1));
192  res->url = octstr_create(LO2CSTR(row,2));
193  res->source = octstr_create(LO2CSTR(row, 3));
194  res->destination = octstr_create(LO2CSTR(row, 4));
195  res->boxc_id = octstr_create(LO2CSTR(row, 5));
197  res->smsc = octstr_duplicate(smsc);
198  }
199  gwlist_destroy(result, NULL);
200 
201 #undef LO2CSTR
202 
203  return res;
204 }
205 
206 static void dlr_mysql_remove(const Octstr *smsc, const Octstr *ts, const Octstr *dst)
207 {
208  Octstr *sql, *like;
209  DBPoolConn *pconn;
210  List *binds = gwlist_create();
211  int res;
212 
213  debug("dlr.mysql", 0, "removing DLR from database");
214 
215  pconn = dbpool_conn_consume(pool);
216  /* just for sure */
217  if (pconn == NULL)
218  return;
219 
220  if (dst)
221  like = octstr_format("AND `%S` LIKE CONCAT('%%', ?)", fields->field_dst);
222  else
223  like = octstr_imm("");
224 
225  sql = octstr_format("DELETE FROM `%S` WHERE `%S`=? AND `%S`=? %S LIMIT 1",
226  fields->table, fields->field_smsc,
227  fields->field_ts, like);
228 
229  gwlist_append(binds, (Octstr *)smsc);
230  gwlist_append(binds, (Octstr *)ts);
231  if (dst)
232  gwlist_append(binds, (Octstr *)dst);
233 
234 #if defined(DLR_TRACE)
235  debug("dlr.mysql", 0, "sql: %s", octstr_get_cstr(sql));
236 #endif
237 
238  if ((res = dbpool_conn_update(pconn, sql, binds)) == -1)
239  error(0, "DLR: MYSQL: Error while removing dlr entry for DST<%s>", octstr_get_cstr(dst));
240  else if (!res)
241  warning(0, "DLR: MYSQL: No dlr deleted for DST<%s>", octstr_get_cstr(dst));
242 
243  dbpool_conn_produce(pconn);
244  gwlist_destroy(binds, NULL);
245  octstr_destroy(sql);
246  octstr_destroy(like);
247 }
248 
249 static void dlr_mysql_update(const Octstr *smsc, const Octstr *ts, const Octstr *dst, int status)
250 {
251  Octstr *sql, *os_status, *like;
252  DBPoolConn *pconn;
253  List *binds = gwlist_create();
254  int res;
255 
256  debug("dlr.mysql", 0, "updating DLR status in database");
257 
258  pconn = dbpool_conn_consume(pool);
259  /* just for sure */
260  if (pconn == NULL)
261  return;
262 
263  if (dst)
264  like = octstr_format("AND `%S` LIKE CONCAT('%%', ?)", fields->field_dst);
265  else
266  like = octstr_imm("");
267 
268  sql = octstr_format("UPDATE `%S` SET `%S`=? WHERE `%S`=? AND `%S`=? %S LIMIT 1",
269  fields->table, fields->field_status,
270  fields->field_smsc, fields->field_ts,
271  like);
272 
273  os_status = octstr_format("%d", status);
274  gwlist_append(binds, (Octstr *)os_status);
275  gwlist_append(binds, (Octstr *)smsc);
276  gwlist_append(binds, (Octstr *)ts);
277  if (dst)
278  gwlist_append(binds, (Octstr *)dst);
279 
280 #if defined(DLR_TRACE)
281  debug("dlr.mysql", 0, "sql: %s", octstr_get_cstr(sql));
282 #endif
283  if ((res = dbpool_conn_update(pconn, sql, binds)) == -1)
284  error(0, "DLR: MYSQL: Error while updating dlr entry for DST<%s>", octstr_get_cstr(dst));
285  else if (!res)
286  warning(0, "DLR: MYSQL: No dlr found to update for DST<%s>, (status %d)", octstr_get_cstr(dst), status);
287 
288  dbpool_conn_produce(pconn);
289  gwlist_destroy(binds, NULL);
290  octstr_destroy(os_status);
291  octstr_destroy(sql);
292  octstr_destroy(like);
293 }
294 
295 static long dlr_mysql_messages(void)
296 {
297  List *result, *row;
298  Octstr *sql;
299  DBPoolConn *conn;
300  long msgs = -1;
301 
302  conn = dbpool_conn_consume(pool);
303  if (conn == NULL)
304  return -1;
305 
306  sql = octstr_format("SELECT count(*) FROM `%S`", fields->table);
307 #if defined(DLR_TRACE)
308  debug("dlr.mysql", 0, "sql: %s", octstr_get_cstr(sql));
309 #endif
310 
311  if (dbpool_conn_select(conn, sql, NULL, &result) != 0) {
312  octstr_destroy(sql);
313  dbpool_conn_produce(conn);
314  return -1;
315  }
316  dbpool_conn_produce(conn);
317  octstr_destroy(sql);
318 
319  if (gwlist_len(result) > 0) {
320  row = gwlist_extract_first(result);
321  msgs = strtol(octstr_get_cstr(gwlist_get(row,0)), NULL, 10);
323  }
324  gwlist_destroy(result, NULL);
325 
326  return msgs;
327 }
328 
329 static void dlr_mysql_flush(void)
330 {
331  Octstr *sql;
332  DBPoolConn *pconn;
333  int rows;
334 
335  pconn = dbpool_conn_consume(pool);
336  /* just for sure */
337  if (pconn == NULL)
338  return;
339 
340  sql = octstr_format("DELETE FROM `%S`", fields->table);
341 #if defined(DLR_TRACE)
342  debug("dlr.mysql", 0, "sql: %s", octstr_get_cstr(sql));
343 #endif
344  rows = dbpool_conn_update(pconn, sql, NULL);
345  if (rows == -1)
346  error(0, "DLR: MYSQL: Error while flushing dlr entries from database");
347  else
348  debug("dlr.mysql", 0, "Flushing %d DLR entries from database", rows);
349  dbpool_conn_produce(pconn);
350  octstr_destroy(sql);
351 }
352 
353 static struct dlr_storage handles = {
354  .type = "mysql",
355  .dlr_add = dlr_mysql_add,
356  .dlr_get = dlr_mysql_get,
357  .dlr_update = dlr_mysql_update,
358  .dlr_remove = dlr_mysql_remove,
359  .dlr_shutdown = dlr_mysql_shutdown,
360  .dlr_messages = dlr_mysql_messages,
361  .dlr_flush = dlr_mysql_flush
362 };
363 
365 {
366  CfgGroup *grp;
367  List *grplist;
368  Octstr *mysql_host, *mysql_user, *mysql_pass, *mysql_db, *mysql_id;
369  long mysql_port = 0;
370  Octstr *p = NULL;
371  long pool_size;
372  DBConf *db_conf = NULL;
373 
374  /*
375  * check for all mandatory directives that specify the field names
376  * of the used MySQL table
377  */
378  if (!(grp = cfg_get_single_group(cfg, octstr_imm("dlr-db"))))
379  panic(0, "DLR: MySQL: group 'dlr-db' is not specified!");
380 
381  if (!(mysql_id = cfg_get(grp, octstr_imm("id"))))
382  panic(0, "DLR: MySQL: directive 'id' is not specified!");
383 
384  fields = dlr_db_fields_create(grp);
385  gw_assert(fields != NULL);
386 
387  /*
388  * Escaping special quotes for field/table names
389  */
390  octstr_replace(fields->table, octstr_imm("`"), octstr_imm("``"));
391  octstr_replace(fields->field_smsc, octstr_imm("`"), octstr_imm("``"));
392  octstr_replace(fields->field_ts, octstr_imm("`"), octstr_imm("``"));
393  octstr_replace(fields->field_src, octstr_imm("`"), octstr_imm("``"));
394  octstr_replace(fields->field_dst, octstr_imm("`"), octstr_imm("``"));
395  octstr_replace(fields->field_serv, octstr_imm("`"), octstr_imm("``"));
396  octstr_replace(fields->field_url, octstr_imm("`"), octstr_imm("``"));
397  octstr_replace(fields->field_mask, octstr_imm("`"), octstr_imm("``"));
398  octstr_replace(fields->field_status, octstr_imm("`"), octstr_imm("``"));
399  octstr_replace(fields->field_boxc, octstr_imm("`"), octstr_imm("``"));
400 
401  /*
402  * now grap the required information from the 'mysql-connection' group
403  * with the mysql-id we just obtained
404  *
405  * we have to loop through all available MySQL connection definitions
406  * and search for the one we are looking for
407  */
408 
409  grplist = cfg_get_multi_group(cfg, octstr_imm("mysql-connection"));
410  while (grplist && (grp = gwlist_extract_first(grplist)) != NULL) {
411  p = cfg_get(grp, octstr_imm("id"));
412  if (p != NULL && octstr_compare(p, mysql_id) == 0) {
413  goto found;
414  }
415  if (p != NULL) octstr_destroy(p);
416  }
417  panic(0, "DLR: MySQL: connection settings for id '%s' are not specified!",
418  octstr_get_cstr(mysql_id));
419 
420 found:
421  octstr_destroy(p);
422  gwlist_destroy(grplist, NULL);
423 
424  if (cfg_get_integer(&pool_size, grp, octstr_imm("max-connections")) == -1 || pool_size == 0)
425  pool_size = 1;
426 
427  if (!(mysql_host = cfg_get(grp, octstr_imm("host"))))
428  panic(0, "DLR: MySQL: directive 'host' is not specified!");
429  if (!(mysql_user = cfg_get(grp, octstr_imm("username"))))
430  panic(0, "DLR: MySQL: directive 'username' is not specified!");
431  if (!(mysql_pass = cfg_get(grp, octstr_imm("password"))))
432  panic(0, "DLR: MySQL: directive 'password' is not specified!");
433  if (!(mysql_db = cfg_get(grp, octstr_imm("database"))))
434  panic(0, "DLR: MySQL: directive 'database' is not specified!");
435  cfg_get_integer(&mysql_port, grp, octstr_imm("port")); /* optional */
436 
437  /*
438  * ok, ready to connect to MySQL
439  */
440  db_conf = gw_malloc(sizeof(DBConf));
441  gw_assert(db_conf != NULL);
442 
443  db_conf->mysql = gw_malloc(sizeof(MySQLConf));
444  gw_assert(db_conf->mysql != NULL);
445 
446  db_conf->mysql->host = mysql_host;
447  db_conf->mysql->port = mysql_port;
448  db_conf->mysql->username = mysql_user;
449  db_conf->mysql->password = mysql_pass;
450  db_conf->mysql->database = mysql_db;
451 
452  pool = dbpool_create(DBPOOL_MYSQL, db_conf, pool_size);
453  gw_assert(pool != NULL);
454 
455  /*
456  * XXX should a failing connect throw panic?!
457  */
458  if (dbpool_conn_count(pool) == 0)
459  panic(0,"DLR: MySQL: database pool has no connections!");
460 
461  octstr_destroy(mysql_id);
462 
463  return &handles;
464 }
465 #else
466 /*
467  * Return NULL , so we point dlr-core that we were
468  * not compiled in.
469  */
471 {
472  return NULL;
473 }
474 #endif /* HAVE_MYSQL */
void error(int err, const char *fmt,...)
Definition: log.c:612
const char * type
Definition: dlr_p.h:112
Octstr * url
Definition: dlr_p.h:84
void octstr_replace(Octstr *haystack, Octstr *needle, Octstr *repl)
Definition: octstr.c:2647
long dbpool_conn_count(DBPool *p)
DBPool * dbpool_create(enum db_type db_type, DBConf *conf, unsigned int connections)
Octstr * field_boxc
Definition: dlr_p.h:160
void gwlist_append(List *list, void *item)
Definition: list.c:179
Octstr * service
Definition: dlr_p.h:83
void dlr_db_fields_destroy(struct dlr_db_fields *fields)
Definition: dlr.c:204
struct dlr_entry * dlr_entry_create(void)
Definition: dlr.c:103
long gwlist_len(List *list)
Definition: list.c:166
Octstr * boxc_id
Definition: dlr_p.h:85
void * gwlist_get(List *list, long pos)
Definition: list.c:292
#define cfg_get(grp, varname)
Definition: cfg.h:86
Octstr * field_src
Definition: dlr_p.h:154
Octstr * field_url
Definition: dlr_p.h:157
#define octstr_get_cstr(ostr)
Definition: octstr.h:233
static struct pid_list * found
Octstr * field_status
Definition: dlr_p.h:159
void dbpool_conn_produce(DBPoolConn *conn)
static struct dlr_storage * handles
Definition: dlr.c:97
Octstr * table
Definition: dlr_p.h:150
Octstr * octstr_imm(const char *cstr)
Definition: octstr.c:281
Definition: cfg.c:164
void * gwlist_extract_first(List *list)
Definition: list.c:305
Octstr * source
Definition: dlr_p.h:81
Octstr * password
Definition: dbpool.h:103
#define octstr_duplicate(ostr)
Definition: octstr.h:187
List * cfg_get_multi_group(Cfg *cfg, Octstr *name)
Definition: cfg.c:642
void warning(int err, const char *fmt,...)
Definition: log.c:624
Octstr * timestamp
Definition: dlr_p.h:80
Octstr * field_serv
Definition: dlr_p.h:156
Octstr * octstr_format(const char *fmt,...)
Definition: octstr.c:2462
void octstr_destroy(Octstr *ostr)
Definition: octstr.c:322
#define octstr_create(cstr)
Definition: octstr.h:125
void octstr_destroy_item(void *os)
Definition: octstr.c:334
gw_assert(wtls_machine->packet_to_send!=NULL)
struct dlr_storage * dlr_init_mysql(Cfg *cfg)
Definition: dlr_mysql.c:470
Octstr * host
Definition: dbpool.h:100
Definition: dbpool.h:164
Octstr * field_smsc
Definition: dlr_p.h:152
static Cfg * cfg
Definition: smsbox.c:115
Octstr * destination
Definition: dlr_p.h:82
void dbpool_destroy(DBPool *p)
int dbpool_conn_update(DBPoolConn *conn, const Octstr *sql, List *binds)
Definition: octstr.c:118
MySQLConf * mysql
Definition: dbpool.h:166
void dlr_entry_destroy(struct dlr_entry *dlr)
Definition: dlr.c:142
void debug(const char *place, int err, const char *fmt,...)
Definition: log.c:690
int cfg_get_integer(long *n, CfgGroup *grp, Octstr *varname)
Definition: cfg.c:739
#define panic
Definition: log.h:87
Definition: cfg.c:73
Octstr * username
Definition: dbpool.h:102
Octstr * database
Definition: dbpool.h:104
int dbpool_conn_select(DBPoolConn *conn, const Octstr *sql, List *binds, List **result)
#define gwlist_create()
Definition: list.h:136
long port
Definition: dbpool.h:101
Definition: dlr_p.h:78
DBPoolConn * dbpool_conn_consume(DBPool *p)
Octstr * smsc
Definition: dlr_p.h:79
int mask
Definition: dlr_p.h:86
CfgGroup * cfg_get_single_group(Cfg *cfg, Octstr *name)
Definition: cfg.c:636
struct dlr_db_fields * dlr_db_fields_create(CfgGroup *grp)
Definition: dlr.c:169
Octstr * field_ts
Definition: dlr_p.h:153
Definition: list.c:102
Octstr * field_dst
Definition: dlr_p.h:155
Octstr * field_mask
Definition: dlr_p.h:158
int octstr_compare(const Octstr *ostr1, const Octstr *ostr2)
Definition: octstr.c:869
void gwlist_destroy(List *list, gwlist_item_destructor_t *destructor)
Definition: list.c:145
See file LICENSE for details about the license agreement for using, modifying, copying or deriving work from this software.