Kannel: Open Source WAP and SMS gateway  $Revision: 5037 $
dlr_mssql.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_mssql.c - MSSQL dlr storage implementation.
59  *
60  * Author: Alejandro Guerrieri <aguerrieri at kannel dot org>
61  *
62  * Based on dlr_oracle.c
63  * Alexander Malysh <a.malysh@centrium.de>
64  * Robert Gałach <robert.galach@my.tenbit.pl>
65  *
66  * Copyright: See COPYING file that comes with this distribution
67  */
68 
69 #include "gwlib/gwlib.h"
70 #include "gwlib/dbpool.h"
71 #include "dlr_p.h"
72 
73 
74 #ifdef HAVE_MSSQL
75 
76 /*
77  * Our connection pool to mssql.
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 long dlr_messages_mssql()
88 {
89  List *result, *row;
90  Octstr *sql;
91  DBPoolConn *conn;
92  long msgs = -1;
93 
94  conn = dbpool_conn_consume(pool);
95  if (conn == NULL)
96  return -1;
97 
98  sql = octstr_format("SELECT COUNT(*) FROM %S", fields->table);
99 #if defined(DLR_TRACE)
100  debug("dlr.mssql", 0, "sql: %s", octstr_get_cstr(sql));
101 #endif
102 
103  if (dbpool_conn_select(conn, sql, NULL, &result) != 0) {
104  octstr_destroy(sql);
105  dbpool_conn_produce(conn);
106  return -1;
107  }
108  dbpool_conn_produce(conn);
109  octstr_destroy(sql);
110 
111  if (gwlist_len(result) > 0) {
112  row = gwlist_extract_first(result);
113  msgs = strtol(octstr_get_cstr(gwlist_get(row,0)), NULL, 10);
115  }
116  gwlist_destroy(result, NULL);
117 
118  return msgs;
119 }
120 
121 static void dlr_shutdown_mssql()
122 {
123  dbpool_destroy(pool);
124  dlr_db_fields_destroy(fields);
125 }
126 
127 static void dlr_add_mssql(struct dlr_entry *entry)
128 {
129  Octstr *sql;
130  DBPoolConn *pconn;
131  debug("dlr.mssql", 0, "adding DLR entry into database");
132  int res;
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  "('%S', '%S', '%S', '%S', '%S', '%S', '%d', '%S', '%d')",
143  fields->table, fields->field_smsc, fields->field_ts, fields->field_src,
144  fields->field_dst, fields->field_serv, fields->field_url,
145  fields->field_mask, fields->field_boxc, fields->field_status,
146  entry->smsc, entry->timestamp, entry->source, entry->destination,
147  entry->service, entry->url, entry->mask, entry->boxc_id, 0);
148 
149 #if defined(DLR_TRACE)
150  debug("dlr.mssql", 0, "sql: %s", octstr_get_cstr(sql));
151 #endif
152  if ((res = dbpool_conn_update(pconn, sql, NULL)) == -1)
153  error(0, "DLR: MSSQL: Error while adding dlr entry for DST<%s>", octstr_get_cstr(entry->destination));
154  else if (!res)
155  warning(0, "DLR: MSSQL: No dlr inserted for DST<%s>", octstr_get_cstr(entry->destination));
156 
157  dbpool_conn_produce(pconn);
158  octstr_destroy(sql);
159  dlr_entry_destroy(entry);
160 }
161 
162 static void dlr_remove_mssql(const Octstr *smsc, const Octstr *ts, const Octstr *dst)
163 {
164  Octstr *sql, *like;
165  DBPoolConn *pconn;
166  int res;
167 
168  debug("dlr.mssql", 0, "removing DLR from database");
169 
170  pconn = dbpool_conn_consume(pool);
171  /* just for sure */
172  if (pconn == NULL)
173  return;
174 
175  if (dst)
176  like = octstr_format("AND %S LIKE '%%%S'", fields->field_dst, dst);
177  else
178  like = octstr_imm("");
179 
180  sql = octstr_format("SET ROWCOUNT 1\nDELETE FROM %S WHERE %S='%S' AND "
181  "%S='%S' %S \nSET ROWCOUNT 0", fields->table, fields->field_smsc,
182  smsc, fields->field_ts, ts, like);
183 
184 #if defined(DLR_TRACE)
185  debug("dlr.mssql", 0, "sql: %s", octstr_get_cstr(sql));
186 #endif
187 
188  if ((res = dbpool_conn_update(pconn, sql, NULL)) == -1)
189  error(0, "DLR: MSSQL: Error while removing dlr entry for DST<%s>", octstr_get_cstr(dst));
190  else if (!res)
191  warning(0, "DLR: MSSQL: No dlr deleted for DST<%s>", octstr_get_cstr(dst));
192 
193  dbpool_conn_produce(pconn);
194  octstr_destroy(sql);
195  octstr_destroy(like);
196 }
197 
198 static struct dlr_entry* dlr_get_mssql(const Octstr *smsc, const Octstr *ts, const Octstr *dst)
199 {
200  Octstr *sql, *like;
201  DBPoolConn *pconn;
202  List *result = NULL, *row;
203  struct dlr_entry *res = NULL;
204 
205  pconn = dbpool_conn_consume(pool);
206  if (pconn == NULL) /* should not happens, but sure is sure */
207  return NULL;
208 
209  if (dst)
210  like = octstr_format("AND %S LIKE '%%%S'", fields->field_dst, dst);
211  else
212  like = octstr_imm("");
213 
214  sql = octstr_format("SELECT %S, %S, %S, %S, %S, %S FROM %S WHERE %S='%S'"
215  " AND %S='%S' %S", fields->field_mask, fields->field_serv,
216  fields->field_url, fields->field_src, fields->field_dst,
217  fields->field_boxc, fields->table, fields->field_smsc, smsc,
218  fields->field_ts, ts, like);
219 
220 #if defined(DLR_TRACE)
221  debug("dlr.mssql", 0, "sql: %s", octstr_get_cstr(sql));
222 #endif
223  if (dbpool_conn_select(pconn, sql, NULL, &result) != 0) {
224  octstr_destroy(sql);
225  dbpool_conn_produce(pconn);
226  return NULL;
227  }
228  octstr_destroy(sql);
229  octstr_destroy(like);
230  dbpool_conn_produce(pconn);
231 
232 #define LO2CSTR(r, i) octstr_get_cstr(gwlist_get(r, i))
233 
234  if (gwlist_len(result) > 0) {
235  row = gwlist_extract_first(result);
236  res = dlr_entry_create();
237  gw_assert(res != NULL);
238  res->mask = atoi(LO2CSTR(row,0));
239  res->service = octstr_create(LO2CSTR(row, 1));
240  res->url = octstr_create(LO2CSTR(row,2));
241  res->source = octstr_create(LO2CSTR(row, 3));
242  res->destination = octstr_create(LO2CSTR(row, 4));
243  res->boxc_id = octstr_create(LO2CSTR(row, 5));
245  res->smsc = octstr_duplicate(smsc);
246  }
247  gwlist_destroy(result, NULL);
248 
249 #undef LO2CSTR
250 
251  return res;
252 }
253 
254 static void dlr_update_mssql(const Octstr *smsc, const Octstr *ts, const Octstr *dst, int status)
255 {
256  Octstr *sql, *like;
257  DBPoolConn *pconn;
258  int res;
259 
260  debug("dlr.mssql", 0, "updating DLR status in database");
261 
262  pconn = dbpool_conn_consume(pool);
263  /* just for sure */
264  if (pconn == NULL)
265  return;
266 
267  if (dst)
268  like = octstr_format("AND %S LIKE '%%%S'", fields->field_dst, dst);
269  else
270  like = octstr_imm("");
271 
272  sql = octstr_format("SET ROWCOUNT 1\nUPDATE %S SET %S=%d WHERE %S='%S' "
273  "AND %S='%S' %S\nSET ROWCOUNT 0",
274  fields->table, fields->field_status, status, fields->field_smsc, smsc,
275  fields->field_ts, ts, like);
276 
277 #if defined(DLR_TRACE)
278  debug("dlr.mssql", 0, "sql: %s", octstr_get_cstr(sql));
279 #endif
280  if ((res = dbpool_conn_update(pconn, sql, NULL)) == -1)
281  error(0, "DLR: MSSQL: Error while updating dlr entry for DST<%s>", octstr_get_cstr(dst));
282  else if (!res)
283  warning(0, "DLR: MSSQL: No dlr found to update for DST<%s> (status: %d)", octstr_get_cstr(dst), status);
284 
285  dbpool_conn_produce(pconn);
286  octstr_destroy(sql);
287  octstr_destroy(like);
288 }
289 
290 static void dlr_flush_mssql (void)
291 {
292  Octstr *sql;
293  DBPoolConn *pconn;
294  int rows;
295 
296  pconn = dbpool_conn_consume(pool);
297  /* just for sure */
298  if (pconn == NULL)
299  return;
300 
301  sql = octstr_format("DELETE FROM %S", fields->table);
302 #if defined(DLR_TRACE)
303  debug("dlr.mssql", 0, "sql: %s", octstr_get_cstr(sql));
304 #endif
305  rows = dbpool_conn_update(pconn, sql, NULL);
306  if (rows == -1)
307  error(0, "DLR: MSSQL: Error while flushing dlr entries from database");
308  else
309  debug("dlr.mssql", 0, "Flushing %d DLR entries from database", rows);
310  dbpool_conn_produce(pconn);
311  octstr_destroy(sql);
312 }
313 
314 static struct dlr_storage handles = {
315  .type = "mssql",
316  .dlr_messages = dlr_messages_mssql,
317  .dlr_shutdown = dlr_shutdown_mssql,
318  .dlr_add = dlr_add_mssql,
319  .dlr_get = dlr_get_mssql,
320  .dlr_remove = dlr_remove_mssql,
321  .dlr_update = dlr_update_mssql,
322  .dlr_flush = dlr_flush_mssql
323 };
324 
326 {
327  CfgGroup *grp;
328  List *grplist;
329  long pool_size;
330  DBConf *db_conf = NULL;
331  Octstr *id, *username, *password, *server, *database;
332  int found;
333 
334  if ((grp = cfg_get_single_group(cfg, octstr_imm("dlr-db"))) == NULL)
335  panic(0, "DLR: MSSQL: group 'dlr-db' is not specified!");
336 
337  if (!(id = cfg_get(grp, octstr_imm("id"))))
338  panic(0, "DLR: MSSQL: directive 'id' is not specified!");
339 
340  /* initialize database fields */
341  fields = dlr_db_fields_create(grp);
342  gw_assert(fields != NULL);
343 
344  grplist = cfg_get_multi_group(cfg, octstr_imm("mssql-connection"));
345  found = 0;
346  while (grplist && (grp = gwlist_extract_first(grplist)) != NULL) {
347  Octstr *p = cfg_get(grp, octstr_imm("id"));
348  if (p != NULL && octstr_compare(p, id) == 0) {
349  found = 1;
350  }
351  if (p != NULL)
352  octstr_destroy(p);
353  if (found == 1)
354  break;
355  }
356  gwlist_destroy(grplist, NULL);
357 
358  if (found == 0)
359  panic(0, "DLR: MSSQL: connection settings for id '%s' are not specified!",
360  octstr_get_cstr(id));
361 
362  username = cfg_get(grp, octstr_imm("username"));
363  password = cfg_get(grp, octstr_imm("password"));
364  server = cfg_get(grp, octstr_imm("server"));
365  database = cfg_get(grp, octstr_imm("database"));
366  if (cfg_get_integer(&pool_size, grp, octstr_imm("max-connections")) == -1)
367  pool_size = 1;
368 
369  if (username == NULL || password == NULL || server == NULL || database == NULL) {
370  panic(0, "DLR: MSSQL: connection settings missing for id '%s'. "
371  "Please check you configuration.", octstr_get_cstr(id));
372  }
373  /* ok we are ready to create dbpool */
374  db_conf = gw_malloc(sizeof(*db_conf));
375  db_conf->mssql = gw_malloc(sizeof(MSSQLConf));
376 
377  db_conf->mssql->username = username;
378  db_conf->mssql->password = password;
379  db_conf->mssql->server = server;
380  db_conf->mssql->database = database;
381 
382  pool = dbpool_create(DBPOOL_MSSQL, db_conf, pool_size);
383  gw_assert(pool != NULL);
384 
385  if (dbpool_conn_count(pool) == 0)
386  panic(0, "DLR: MSSQL: Could not establish mssql connection(s).");
387 
388  octstr_destroy(id);
389 
390  return &handles;
391 }
392 #else
393 /* no mssql support build in */
395 {
396  return NULL;
397 }
398 #endif /* HAVE_MSSQL */
void error(int err, const char *fmt,...)
Definition: log.c:612
const char * type
Definition: dlr_p.h:112
MSSQLConf * mssql
Definition: dbpool.h:165
Octstr * url
Definition: dlr_p.h:84
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
Octstr * server
Definition: dbpool.h:113
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
Octstr * database
Definition: dbpool.h:114
#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
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
Octstr * password
Definition: dbpool.h:112
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
Octstr * username
Definition: dbpool.h:111
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
struct dlr_storage * dlr_init_mssql(Cfg *cfg)
Definition: dlr_mssql.c:394
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)
static void server(int lport, int pport)
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.