Kannel: Open Source WAP and SMS gateway  $Revision: 5037 $
dlr_oracle.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_oracle.c - Oracle dlr storage implementation.
59  *
60  * Author: Alexander Malysh <a.malysh@centrium.de>, (C) 2003
61  * Robert Gałach <robert.galach@my.tenbit.pl>
62  * 2004 Rewrited sql queries to use binding variables.
63  *
64  * Copyright: See COPYING file that comes with this distribution
65  */
66 
67 #include "gwlib/gwlib.h"
68 #include "gwlib/dbpool.h"
69 #include "dlr_p.h"
70 
71 
72 #ifdef HAVE_ORACLE
73 
74 /*
75  * Our connection pool to oracle.
76  */
77 static DBPool *pool = NULL;
78 
79 /*
80  * Database fields, which we are use.
81  */
82 static struct dlr_db_fields *fields = NULL;
83 
84 
85 static long dlr_messages_oracle()
86 {
87  List *result, *row;
88  Octstr *sql;
89  DBPoolConn *conn;
90  long msgs = -1;
91 
92  conn = dbpool_conn_consume(pool);
93  if (conn == NULL)
94  return -1;
95 
96  sql = octstr_format("SELECT count(*) FROM %S", fields->table);
97 #if defined(DLR_TRACE)
98  debug("dlr.oracle", 0, "sql: %s", octstr_get_cstr(sql));
99 #endif
100 
101  if (dbpool_conn_select(conn, sql, NULL, &result) != 0) {
102  octstr_destroy(sql);
103  dbpool_conn_produce(conn);
104  return -1;
105  }
106  dbpool_conn_produce(conn);
107  octstr_destroy(sql);
108 
109  if (gwlist_len(result) > 0) {
110  row = gwlist_extract_first(result);
111  msgs = strtol(octstr_get_cstr(gwlist_get(row,0)), NULL, 10);
113  }
114  gwlist_destroy(result, NULL);
115 
116  return msgs;
117 }
118 
119 static void dlr_shutdown_oracle()
120 {
121  dbpool_destroy(pool);
122  dlr_db_fields_destroy(fields);
123 }
124 
125 static void dlr_add_oracle(struct dlr_entry *entry)
126 {
127  Octstr *sql, *os_mask;
128  DBPoolConn *pconn;
129  List *binds = gwlist_create();
130  int res;
131 
132  debug("dlr.oracle", 0, "adding DLR entry into database");
133 
134  pconn = dbpool_conn_consume(pool);
135  /* just for sure */
136  if (pconn == NULL) {
137  dlr_entry_destroy(entry);
138  return;
139  }
140 
141  sql = octstr_format("INSERT INTO %S (%S, %S, %S, %S, %S, %S, %S, %S, %S) VALUES "
142  "(:1, :2, :3, :4, :5, :6, :7, :8, 0)",
143  fields->table, fields->field_smsc, fields->field_ts,
144  fields->field_src, fields->field_dst, fields->field_serv,
145  fields->field_url, fields->field_mask, fields->field_boxc,
146  fields->field_status);
147  os_mask = octstr_format("%d", entry->mask);
148 
149  gwlist_append(binds, entry->smsc); /* :1 */
150  gwlist_append(binds, entry->timestamp); /* :2 */
151  gwlist_append(binds, entry->source); /* :3 */
152  gwlist_append(binds, entry->destination); /* :4 */
153  gwlist_append(binds, entry->service); /* :5 */
154  gwlist_append(binds, entry->url); /* :6 */
155  gwlist_append(binds, os_mask); /* :7 */
156  gwlist_append(binds, entry->boxc_id); /* :8 */
157 #if defined(DLR_TRACE)
158  debug("dlr.oracle", 0, "sql: %s", octstr_get_cstr(sql));
159 #endif
160  if ((res = dbpool_conn_update(pconn, sql, binds)) == -1)
161  error(0, "DLR: ORACLE: Error while adding dlr entry for DST<%s>", octstr_get_cstr(entry->destination));
162  else if (!res)
163  warning(0, "DLR: ORACLE: No dlr inserted for DST<%s>", octstr_get_cstr(entry->destination));
164 
165  dbpool_conn_produce(pconn);
166  octstr_destroy(sql);
167  gwlist_destroy(binds, NULL);
168  octstr_destroy(os_mask);
169  dlr_entry_destroy(entry);
170 }
171 
172 static void dlr_remove_oracle(const Octstr *smsc, const Octstr *ts, const Octstr *dst)
173 {
174  Octstr *sql, *like;
175  DBPoolConn *pconn;
176  List *binds = gwlist_create();
177  int res;
178 
179  debug("dlr.oracle", 0, "removing DLR from database");
180 
181  pconn = dbpool_conn_consume(pool);
182  /* just for sure */
183  if (pconn == NULL)
184  return;
185 
186  if (dst)
187  like = octstr_format("AND %S LIKE CONCAT('%%', :3)", fields->field_dst);
188  else
189  like = octstr_imm("");
190 
191  sql = octstr_format("DELETE FROM %S WHERE %S=:1 AND %S=:2 %S AND ROWNUM < 2",
192  fields->table, fields->field_smsc,
193  fields->field_ts, like);
194 
195  gwlist_append(binds, (Octstr *)smsc); /* :1 */
196  gwlist_append(binds, (Octstr *)ts); /* :2 */
197  if (dst) /* :3 */
198  gwlist_append(binds, (Octstr *)dst);
199 
200 #if defined(DLR_TRACE)
201  debug("dlr.oracle", 0, "sql: %s", octstr_get_cstr(sql));
202 #endif
203 
204  if ((res = dbpool_conn_update(pconn, sql, binds)) == -1)
205  error(0, "DLR: ORACLE: Error while removing dlr entry for DST<%s>", octstr_get_cstr(dst));
206  else if (!res)
207  warning(0, "DLR: ORACLE: No dlr deleted for DST<%s>", octstr_get_cstr(dst));
208 
209  dbpool_conn_produce(pconn);
210  gwlist_destroy(binds, NULL);
211  octstr_destroy(sql);
212  octstr_destroy(like);
213 }
214 
215 static struct dlr_entry* dlr_get_oracle(const Octstr *smsc, const Octstr *ts, const Octstr *dst)
216 {
217  Octstr *sql, *like;
218  DBPoolConn *pconn;
219  List *result = NULL, *row;
220  struct dlr_entry *res = NULL;
221  List *binds = gwlist_create();
222 
223  pconn = dbpool_conn_consume(pool);
224  if (pconn == NULL) /* should not happens, but sure is sure */
225  return NULL;
226 
227  if (dst)
228  like = octstr_format("AND %S LIKE CONCAT('%%', :3)",
229  fields->field_dst);
230  else
231  like = octstr_imm("");
232 
233  sql = octstr_format("SELECT %S, %S, %S, %S, %S, %S FROM %S WHERE %S=:1 AND %S=:2 %S AND ROWNUM < 2",
234  fields->field_mask, fields->field_serv,
235  fields->field_url, fields->field_src,
236  fields->field_dst, fields->field_boxc,
237  fields->table, fields->field_smsc,
238  fields->field_ts, like);
239 
240  gwlist_append(binds, (Octstr *)smsc); /* :1 */
241  gwlist_append(binds, (Octstr *)ts); /* :2 */
242  if (dst) /* :3 */
243  gwlist_append(binds, (Octstr *)dst);
244 
245 #if defined(DLR_TRACE)
246  debug("dlr.oracle", 0, "sql: %s", octstr_get_cstr(sql));
247 #endif
248  if (dbpool_conn_select(pconn, sql, binds, &result) != 0) {
249  octstr_destroy(sql);
250  octstr_destroy(like);
251  dbpool_conn_produce(pconn);
252  return NULL;
253  }
254  octstr_destroy(sql);
255  octstr_destroy(like);
256  gwlist_destroy(binds, NULL);
257  dbpool_conn_produce(pconn);
258 
259 #define LO2CSTR(r, i) octstr_get_cstr(gwlist_get(r, i))
260 
261  if (gwlist_len(result) > 0) {
262  row = gwlist_extract_first(result);
263  res = dlr_entry_create();
264  gw_assert(res != NULL);
265  res->mask = atoi(LO2CSTR(row,0));
266  res->service = octstr_create(LO2CSTR(row, 1));
267  res->url = octstr_create(LO2CSTR(row,2));
268  res->source = octstr_create(LO2CSTR(row, 3));
269  res->destination = octstr_create(LO2CSTR(row, 4));
270  res->boxc_id = octstr_create(LO2CSTR(row, 5));
272  res->smsc = octstr_duplicate(smsc);
273  }
274  gwlist_destroy(result, NULL);
275 
276 #undef LO2CSTR
277 
278  return res;
279 }
280 
281 static void dlr_update_oracle(const Octstr *smsc, const Octstr *ts, const Octstr *dst, int status)
282 {
283  Octstr *sql, *os_status, *like;
284  DBPoolConn *pconn;
285  List *binds = gwlist_create();
286  int res;
287 
288  debug("dlr.oracle", 0, "updating DLR status in database");
289 
290  pconn = dbpool_conn_consume(pool);
291  /* just for sure */
292  if (pconn == NULL)
293  return;
294 
295  if (dst)
296  like = octstr_format("AND %S LIKE CONCAT('%%', :4)", fields->field_dst);
297  else
298  like = octstr_imm("");
299 
300  sql = octstr_format("UPDATE %S SET %S=:1 WHERE %S=:2 AND %S=:3 %S AND ROWNUM < 2",
301  fields->table, fields->field_status,
302  fields->field_smsc, fields->field_ts, like);
303 
304  os_status = octstr_format("%d", status);
305  gwlist_append(binds, (Octstr *)os_status); /* :1 */
306  gwlist_append(binds, (Octstr *)smsc); /* :2 */
307  gwlist_append(binds, (Octstr *)ts); /* :3 */
308  if (dst) /* :4 */
309  gwlist_append(binds, (Octstr *)dst);
310 
311 #if defined(DLR_TRACE)
312  debug("dlr.oracle", 0, "sql: %s", octstr_get_cstr(sql));
313 #endif
314  if ((res = dbpool_conn_update(pconn, sql, binds)) == -1)
315  error(0, "DLR: ORACLE: Error while updating dlr entry for DST<%s>", octstr_get_cstr(dst));
316  else if (!res)
317  warning(0, "DLR: ORACLE: No dlr found to update for DST<%s> (status: %d)", octstr_get_cstr(dst), status);
318 
319  dbpool_conn_produce(pconn);
320  gwlist_destroy(binds, NULL);
321  octstr_destroy(os_status);
322  octstr_destroy(sql);
323  octstr_destroy(like);
324 }
325 
326 static void dlr_flush_oracle (void)
327 {
328  Octstr *sql;
329  DBPoolConn *pconn;
330  int rows;
331 
332  pconn = dbpool_conn_consume(pool);
333  /* just for sure */
334  if (pconn == NULL)
335  return;
336 
337  sql = octstr_format("DELETE FROM %S", fields->table);
338 #if defined(DLR_TRACE)
339  debug("dlr.oracle", 0, "sql: %s", octstr_get_cstr(sql));
340 #endif
341  rows = dbpool_conn_update(pconn, sql, NULL);
342  if (rows == -1)
343  error(0, "DLR: ORACLE: Error while flushing dlr entries from database");
344  else
345  debug("dlr.oracle", 0, "Flushing %d DLR entries from database", rows);
346  dbpool_conn_produce(pconn);
347  octstr_destroy(sql);
348 }
349 
350 static struct dlr_storage handles = {
351  .type = "oracle",
352  .dlr_messages = dlr_messages_oracle,
353  .dlr_shutdown = dlr_shutdown_oracle,
354  .dlr_add = dlr_add_oracle,
355  .dlr_get = dlr_get_oracle,
356  .dlr_remove = dlr_remove_oracle,
357  .dlr_update = dlr_update_oracle,
358  .dlr_flush = dlr_flush_oracle
359 };
360 
362 {
363  CfgGroup *grp;
364  List *grplist;
365  long pool_size;
366  DBConf *db_conf = NULL;
367  Octstr *id, *username, *password, *tnsname;
368  int found;
369 
370  if ((grp = cfg_get_single_group(cfg, octstr_imm("dlr-db"))) == NULL)
371  panic(0, "DLR: ORACLE: group 'dlr-db' is not specified!");
372 
373  if (!(id = cfg_get(grp, octstr_imm("id"))))
374  panic(0, "DLR: ORACLE: directive 'id' is not specified!");
375 
376  /* initialize database fields */
377  fields = dlr_db_fields_create(grp);
378  gw_assert(fields != NULL);
379 
380  grplist = cfg_get_multi_group(cfg, octstr_imm("oracle-connection"));
381  found = 0;
382  while (grplist && (grp = gwlist_extract_first(grplist)) != NULL) {
383  Octstr *p = cfg_get(grp, octstr_imm("id"));
384  if (p != NULL && octstr_compare(p, id) == 0) {
385  found = 1;
386  }
387  if (p != NULL)
388  octstr_destroy(p);
389  if (found == 1)
390  break;
391  }
392  gwlist_destroy(grplist, NULL);
393 
394  if (found == 0)
395  panic(0, "DLR: ORACLE: connection settings for id '%s' are not specified!",
396  octstr_get_cstr(id));
397 
398  username = cfg_get(grp, octstr_imm("username"));
399  password = cfg_get(grp, octstr_imm("password"));
400  tnsname = cfg_get(grp, octstr_imm("tnsname"));
401  if (cfg_get_integer(&pool_size, grp, octstr_imm("max-connections")) == -1)
402  pool_size = 1;
403 
404  if (username == NULL || password == NULL || tnsname == NULL)
405  panic(0, "DLR: ORACLE: connection settings missing for id '%s', please"
406  " check you configuration.",octstr_get_cstr(id));
407 
408  /* ok we are ready to create dbpool */
409  db_conf = gw_malloc(sizeof(*db_conf));
410  db_conf->oracle = gw_malloc(sizeof(OracleConf));
411 
412  db_conf->oracle->username = username;
413  db_conf->oracle->password = password;
414  db_conf->oracle->tnsname = tnsname;
415 
416  pool = dbpool_create(DBPOOL_ORACLE, db_conf, pool_size);
417  gw_assert(pool != NULL);
418 
419  if (dbpool_conn_count(pool) == 0)
420  panic(0, "DLR: ORACLE: Couldnot establish oracle connection(s).");
421 
422  octstr_destroy(id);
423 
424  return &handles;
425 }
426 #else
427 /* no oracle support build in */
429 {
430  return NULL;
431 }
432 #endif /* HAVE_ORACLE */
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
Octstr * password
Definition: dbpool.h:119
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
struct dlr_storage * dlr_init_oracle(Cfg *cfg)
Definition: dlr_oracle.c:428
Octstr * field_status
Definition: dlr_p.h:159
void dbpool_conn_produce(DBPoolConn *conn)
Octstr * username
Definition: dbpool.h:118
static struct dlr_storage * handles
Definition: dlr.c:97
unsigned char * username
Definition: test_cimd2.c:99
Octstr * table
Definition: dlr_p.h:150
unsigned char * password
Definition: test_cimd2.c:100
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
#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)
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
Octstr * tnsname
Definition: dbpool.h:120
void dbpool_destroy(DBPool *p)
int dbpool_conn_update(DBPoolConn *conn, const Octstr *sql, List *binds)
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)
#define gwlist_create()
Definition: list.h:136
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
OracleConf * oracle
Definition: dbpool.h:168
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.