Kannel: Open Source WAP and SMS gateway  $Revision: 5037 $
dlr_pgsql.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_pgsql.c
59  *
60  * Implementation of handling delivery reports (DLRs)
61  * for PostgreSQL database
62  *
63  * modeled after dlr_mysql.c
64  *
65  * Alexander Malysh <a.malysh@centrium.de>, cleanup 2004
66  */
67 
68 #include "gwlib/gwlib.h"
69 #include "gwlib/dbpool.h"
70 #include "dlr_p.h"
71 
72 
73 #ifdef HAVE_PGSQL
74 #include <libpq-fe.h>
75 
76 /*
77  * Our connection pool to pgsql.
78  */
79 static DBPool *pool = NULL;
80 
81 /*
82  * Database fields, which we are use.
83  */
84 static struct dlr_db_fields *fields = NULL;
85 
86 
87 static inline int pgsql_update(const Octstr *sql)
88 {
89  DBPoolConn *pc;
90  int ret = 0;
91 
92 #if defined(DLR_TRACE)
93  debug("dlr.pgsql", 0, "sql: %s", octstr_get_cstr(sql));
94 #endif
95 
96  pc = dbpool_conn_consume(pool);
97  if (pc == NULL) {
98  error(0, "PGSQL: Database pool got no connection! DB update failed!");
99  return -1;
100  }
101 
102  if ((ret = dbpool_conn_update(pc, sql, NULL)) == -1)
103  error(0, "PGSQL: DB update failed!");
104 
106  return ret;
107 }
108 
109 
110 static inline List *pgsql_select(const Octstr *sql)
111 {
112  DBPoolConn *pc;
113  List *ret = NULL;
114 
115 #if defined(DLR_TRACE)
116  debug("dlr.pgsql", 0, "sql: %s", octstr_get_cstr(sql));
117 #endif
118 
119  pc = dbpool_conn_consume(pool);
120  if (pc == NULL) {
121  error(0, "PGSQL: Database pool got no connection! DB operation failed!");
122  return NULL;
123  }
124 
125  if (dbpool_conn_select(pc, sql, NULL, &ret) == -1)
126  error(0, "PGSQL: Select failed!");
127 
129  return ret;
130 }
131 
132 
133 static void dlr_pgsql_shutdown()
134 {
135  dbpool_destroy(pool);
136  dlr_db_fields_destroy(fields);
137 }
138 
139 
140 static void dlr_pgsql_add(struct dlr_entry *entry)
141 {
142  Octstr *sql;
143 
144  sql = octstr_format("INSERT INTO \"%s\" (\"%s\", \"%s\", \"%s\", \"%s\", \"%s\", \"%s\", \"%s\", \"%s\", \"%s\") VALUES "
145  "('%s', '%s', '%s', '%s', '%s', '%s', '%d', '%s', '%d');",
146  octstr_get_cstr(fields->table), octstr_get_cstr(fields->field_smsc),
147  octstr_get_cstr(fields->field_ts),
151  octstr_get_cstr(fields->field_status),
154  entry->mask, octstr_get_cstr(entry->boxc_id), 0);
155 
156 
157  if (!pgsql_update(sql))
158  warning(0, "DLR: PGSQL: No dlr inserted for DST<%s>", octstr_get_cstr(entry->destination));
159 
160  octstr_destroy(sql);
161  dlr_entry_destroy(entry);
162 }
163 
164 
165 static struct dlr_entry *dlr_pgsql_get(const Octstr *smsc, const Octstr *ts, const Octstr *dst)
166 {
167  struct dlr_entry *res = NULL;
168  Octstr *sql, *like;
169  List *result, *row;
170 
171  if (dst)
172  like = octstr_format("AND \"%S\" LIKE '%%%S'", fields->field_dst, dst);
173  else
174  like = octstr_imm("");
175 
176  sql = octstr_format("SELECT \"%S\", \"%S\", \"%S\", \"%S\", \"%S\", "
177  "\"%S\" FROM \"%S\" WHERE \"%S\"='%S' AND \"%S\"='%S' %S LIMIT 1;",
178  fields->field_mask, fields->field_serv, fields->field_url,
179  fields->field_src, fields->field_dst, fields->field_boxc,
180  fields->table, fields->field_smsc, smsc, fields->field_ts, ts, like);
181 
182  result = pgsql_select(sql);
183  octstr_destroy(sql);
184  octstr_destroy(like);
185 
186  if (result == NULL || gwlist_len(result) < 1) {
187  debug("dlr.pgsql", 0, "no rows found");
188  gwlist_destroy(result, NULL);
189  return NULL;
190  }
191 
192  row = gwlist_get(result, 0);
193 
194  debug("dlr.pgsql", 0, "Found entry, col1=%s, col2=%s, col3=%s, col4=%s, col5=%s col6=%s",
195  octstr_get_cstr(gwlist_get(row, 0)),
196  octstr_get_cstr(gwlist_get(row, 1)),
197  octstr_get_cstr(gwlist_get(row, 2)),
198  octstr_get_cstr(gwlist_get(row, 3)),
199  octstr_get_cstr(gwlist_get(row, 4)),
200  octstr_get_cstr(gwlist_get(row, 5))
201  );
202 
203  res = dlr_entry_create();
204  gw_assert(res != NULL);
205  res->mask = atoi(octstr_get_cstr(gwlist_get(row, 0)));
206  res->service = octstr_duplicate(gwlist_get(row, 1));
207  res->url = octstr_duplicate(gwlist_get(row, 2));
208  res->source = octstr_duplicate(gwlist_get(row, 3));
209  res->destination = octstr_duplicate(gwlist_get(row, 4));
210  res->boxc_id = octstr_duplicate(gwlist_get(row, 5));
211  res->smsc = octstr_duplicate(smsc);
212 
213  while((row = gwlist_extract_first(result)))
215  gwlist_destroy(result, NULL);
216 
217  return res;
218 }
219 
220 
221 static void dlr_pgsql_remove(const Octstr *smsc, const Octstr *ts, const Octstr *dst)
222 {
223  Octstr *sql, *like;
224 
225  debug("dlr.pgsql", 0, "removing DLR from database");
226  if (dst)
227  like = octstr_format("AND \"%S\" LIKE '%%%S'", fields->field_dst, dst);
228  else
229  like = octstr_imm("");
230 
231  sql = octstr_format("DELETE FROM \"%S\" WHERE oid = (SELECT oid FROM "
232  "\"%S\" WHERE \"%S\"='%S' AND \"%S\"='%S' %S LIMIT 1);",
233  fields->table, fields->table, fields->field_smsc, smsc,
234  fields->field_ts, ts, like);
235 
236  if (!pgsql_update(sql))
237  warning(0, "DLR: PGSQL: No dlr deleted for DST<%s>", octstr_get_cstr(dst));
238  octstr_destroy(sql);
239  octstr_destroy(like);
240 }
241 
242 
243 static void dlr_pgsql_update(const Octstr *smsc, const Octstr *ts, const Octstr *dst, int status)
244 {
245  Octstr *sql, *like;
246 
247  debug("dlr.pgsql", 0, "updating DLR status in database");
248  if (dst)
249  like = octstr_format("AND \"%S\" LIKE '%%%S'", fields->field_dst, dst);
250  else
251  like = octstr_imm("");
252 
253  sql = octstr_format("UPDATE \"%S\" SET \"%S\"=%d WHERE oid = (SELECT "
254  "oid FROM \"%S\" WHERE \"%S\"='%S' AND \"%S\"='%S' %S LIMIT 1);",
255  fields->table, fields->field_status, status, fields->table,
256  fields->field_smsc, smsc, fields->field_ts, ts, like);
257 
258  if (!pgsql_update(sql))
259  warning(0, "DLR: PGSQL: No dlr updated for DST<%s> (status: %d)", octstr_get_cstr(dst), status);
260  octstr_destroy(sql);
261  octstr_destroy(like);
262 }
263 
264 
265 static long dlr_pgsql_messages(void)
266 {
267  Octstr *sql;
268  long ret;
269  List *res;
270 
271  sql = octstr_format("SELECT count(*) FROM \"%s\";", octstr_get_cstr(fields->table));
272 
273  res = pgsql_select(sql);
274  octstr_destroy(sql);
275 
276  if (res == NULL || gwlist_len(res) < 1) {
277  error(0, "PGSQL: Could not get count of DLR table");
278  ret = -1;
279  } else {
280  ret = atol(octstr_get_cstr(gwlist_get(gwlist_get(res, 0), 0)));
282  }
283 
284  gwlist_destroy(res, NULL);
285 
286  return ret;
287 }
288 
289 
290 static void dlr_pgsql_flush(void)
291 {
292  Octstr *sql;
293 
294  sql = octstr_format("DELETE FROM \"%s\";", octstr_get_cstr(fields->table));
295 
296  pgsql_update(sql);
297  octstr_destroy(sql);
298 }
299 
300 
301 static struct dlr_storage handles = {
302  .type = "pgsql",
303  .dlr_add = dlr_pgsql_add,
304  .dlr_get = dlr_pgsql_get,
305  .dlr_update = dlr_pgsql_update,
306  .dlr_remove = dlr_pgsql_remove,
307  .dlr_shutdown = dlr_pgsql_shutdown,
308  .dlr_messages = dlr_pgsql_messages,
309  .dlr_flush = dlr_pgsql_flush
310 };
311 
312 
314 {
315  CfgGroup *grp;
316  List *grplist;
317  Octstr *pgsql_host, *pgsql_user, *pgsql_pass, *pgsql_db, *pgsql_id;
318  long pgsql_port = 0;
319  Octstr *p = NULL;
320  long pool_size;
321  DBConf *db_conf = NULL;
322 
323  /*
324  * check for all mandatory directives that specify the field names
325  * of the table used
326  */
327  if (!(grp = cfg_get_single_group(cfg, octstr_imm("dlr-db"))))
328  panic(0, "DLR: PgSQL: group 'dlr-db' is not specified!");
329 
330  if (!(pgsql_id = cfg_get(grp, octstr_imm("id"))))
331  panic(0, "DLR: PgSQL: directive 'id' is not specified!");
332 
333  fields = dlr_db_fields_create(grp);
334  gw_assert(fields != NULL);
335 
336  /*
337  * Escaping special quotes for field/table names
338  */
339  octstr_replace(fields->table, octstr_imm("\""), octstr_imm("\"\""));
340  octstr_replace(fields->field_smsc, octstr_imm("\""), octstr_imm("\"\""));
341  octstr_replace(fields->field_ts, octstr_imm("\""), octstr_imm("\"\""));
342  octstr_replace(fields->field_src, octstr_imm("\""), octstr_imm("\"\""));
343  octstr_replace(fields->field_dst, octstr_imm("\""), octstr_imm("\"\""));
344  octstr_replace(fields->field_serv, octstr_imm("\""), octstr_imm("\"\""));
345  octstr_replace(fields->field_url, octstr_imm("\""), octstr_imm("\"\""));
346  octstr_replace(fields->field_mask, octstr_imm("\""), octstr_imm("\"\""));
347  octstr_replace(fields->field_status, octstr_imm("\""), octstr_imm("\"\""));
348  octstr_replace(fields->field_boxc, octstr_imm("\""), octstr_imm("\"\""));
349 
350  /*
351  * now grap the required information from the 'pgsql-connection' group
352  * with the pgsql-id we just obtained
353  *
354  * we have to loop through all available PostgreSQL connection definitions
355  * and search for the one we are looking for
356  */
357 
358  grplist = cfg_get_multi_group(cfg, octstr_imm("pgsql-connection"));
359  while (grplist && (grp = gwlist_extract_first(grplist)) != NULL) {
360  p = cfg_get(grp, octstr_imm("id"));
361  if (p != NULL && octstr_compare(p, pgsql_id) == 0) {
362  goto found;
363  }
364  if (p != NULL)
365  octstr_destroy(p);
366  }
367  panic(0, "DLR: PgSQL: connection settings for id '%s' are not specified!",
368  octstr_get_cstr(pgsql_id));
369 
370 found:
371  octstr_destroy(p);
372  gwlist_destroy(grplist, NULL);
373 
374  if (cfg_get_integer(&pool_size, grp, octstr_imm("max-connections")) == -1 || pool_size == 0)
375  pool_size = 1;
376 
377  if (!(pgsql_host = cfg_get(grp, octstr_imm("host"))))
378  panic(0, "DLR: PgSQL: directive 'host' is not specified!");
379  if (!(pgsql_user = cfg_get(grp, octstr_imm("username"))))
380  panic(0, "DLR: PgSQL: directive 'username' is not specified!");
381  if (!(pgsql_pass = cfg_get(grp, octstr_imm("password"))))
382  panic(0, "DLR: PgSQL: directive 'password' is not specified!");
383  if (!(pgsql_db = cfg_get(grp, octstr_imm("database"))))
384  panic(0, "DLR: PgSQL: directive 'database' is not specified!");
385  cfg_get_integer(&pgsql_port, grp, octstr_imm("port")); /* optional */
386 
387  /*
388  * ok, ready to connect to the database
389  */
390  db_conf = gw_malloc(sizeof(DBConf));
391  gw_assert(db_conf != NULL);
392 
393  db_conf->pgsql = gw_malloc(sizeof(PgSQLConf));
394  gw_assert(db_conf->pgsql != NULL);
395 
396  db_conf->pgsql->host = pgsql_host;
397  db_conf->pgsql->port = pgsql_port;
398  db_conf->pgsql->username = pgsql_user;
399  db_conf->pgsql->password = pgsql_pass;
400  db_conf->pgsql->database = pgsql_db;
401 
402  pool = dbpool_create(DBPOOL_PGSQL, db_conf, pool_size);
403  gw_assert(pool != NULL);
404 
405  /*
406  * XXX should a failing connect throw panic?!
407  */
408  if (dbpool_conn_count(pool) == 0)
409  panic(0,"DLR: PgSQL: database pool has no connections!");
410 
411  octstr_destroy(pgsql_id);
412 
413  return &handles;
414 }
415 #else
416 /*
417  * Return NULL , so we point dlr-core that we were
418  * not compiled in.
419  */
421 {
422  return NULL;
423 }
424 #endif /* HAVE_PGSQL */
425 
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
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 * username
Definition: dbpool.h:140
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
PgSQLConf * pgsql
Definition: dbpool.h:171
void * gwlist_extract_first(List *list)
Definition: list.c:305
struct dlr_storage * dlr_init_pgsql(Cfg *cfg)
Definition: dlr_pgsql.c:420
Octstr * source
Definition: dlr_p.h:81
#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
void octstr_destroy_item(void *os)
Definition: octstr.c:334
gw_assert(wtls_machine->packet_to_send!=NULL)
Octstr * password
Definition: dbpool.h:141
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)
Octstr * database
Definition: dbpool.h:142
Definition: octstr.c:118
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
int dbpool_conn_select(DBPoolConn *conn, const Octstr *sql, List *binds, List **result)
long port
Definition: dbpool.h:139
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 * host
Definition: dbpool.h:138
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.