Kannel: Open Source WAP and SMS gateway  $Revision: 5037 $
dlr_sqlite3.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_sqlite3.c - sqlite3 dlr storage implementation.
59  *
60  * Author: David Butler <gdb@dbSystems.com>
61  *
62  * Based on dlr_oracle.c
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_SQLITE3
73 
74 /*
75  * Our connection pool to sqlite3.
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_sqlite3()
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.sqlite3", 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_sqlite3()
120 {
121  dbpool_destroy(pool);
122  dlr_db_fields_destroy(fields);
123 }
124 
125 static void dlr_add_sqlite3(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.sqlite3", 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.sqlite3", 0, "sql: %s", octstr_get_cstr(sql));
159 #endif
160  if ((res = dbpool_conn_update(pconn, sql, binds)) == -1)
161  error(0, "DLR: SQLite3: Error while adding dlr entry for DST<%s>", octstr_get_cstr(entry->destination));
162  else if (!res)
163  warning(0, "DLR: SQLite3: 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_sqlite3(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  debug("dlr.sqlite3", 0, "removing DLR from database");
179 
180  pconn = dbpool_conn_consume(pool);
181  /* just for sure */
182  if (pconn == NULL)
183  return;
184 
185  if (dst)
186  like = octstr_format("AND %S LIKE '%?3'", fields->field_dst);
187  else
188  like = octstr_imm("");
189 
190  sql = octstr_format("DELETE FROM %S WHERE ROWID IN (SELECT ROWID FROM %S WHERE %S=?1 AND %S=?2 %S LIMIT 1)",
191  fields->table, fields->table,
192  fields->field_smsc, fields->field_ts, like);
193 
194  gwlist_append(binds, (Octstr *)smsc); /* ?1 */
195  gwlist_append(binds, (Octstr *)ts); /* ?2 */
196  if (dst)
197  gwlist_append(binds, (Octstr *)dst); /* ?3 */
198 
199 #if defined(DLR_TRACE)
200  debug("dlr.sqlite3", 0, "sql: %s", octstr_get_cstr(sql));
201 #endif
202 
203  if ((res = dbpool_conn_update(pconn, sql, binds)) == -1)
204  error(0, "DLR: SQLite3: Error while removing dlr entry for DST<%s>", octstr_get_cstr(dst));
205  else if (!res)
206  warning(0, "DLR: SQLite3: No dlr deleted for DST<%s>", octstr_get_cstr(dst));
207 
208  dbpool_conn_produce(pconn);
209  gwlist_destroy(binds, NULL);
210  octstr_destroy(sql);
211  octstr_destroy(like);
212 }
213 
214 static struct dlr_entry* dlr_get_sqlite3(const Octstr *smsc, const Octstr *ts, const Octstr *dst)
215 {
216  Octstr *sql, *like;
217  DBPoolConn *pconn;
218  List *result = NULL, *row;
219  struct dlr_entry *res = NULL;
220  List *binds = gwlist_create();
221 
222  pconn = dbpool_conn_consume(pool);
223  if (pconn == NULL) /* should not happens, but sure is sure */
224  return NULL;
225 
226  if (dst)
227  like = octstr_format("AND %S LIKE '%?3'", fields->field_dst);
228  else
229  like = octstr_imm("");
230 
231  sql = octstr_format("SELECT %S, %S, %S, %S, %S, %S FROM %S WHERE %S=?1 AND %S=?2 %S LIMIT 1",
232  fields->field_mask, fields->field_serv,
233  fields->field_url, fields->field_src,
234  fields->field_dst, fields->field_boxc,
235  fields->table, fields->field_smsc,
236  fields->field_ts, like);
237 
238  gwlist_append(binds, (Octstr *)smsc); /* ?1 */
239  gwlist_append(binds, (Octstr *)ts); /* ?2 */
240  if (dst)
241  gwlist_append(binds, (Octstr *)dst); /* ?3 */
242 
243 #if defined(DLR_TRACE)
244  debug("dlr.sqlite3", 0, "sql: %s", octstr_get_cstr(sql));
245 #endif
246  if (dbpool_conn_select(pconn, sql, binds, &result) != 0) {
247  octstr_destroy(sql);
248  dbpool_conn_produce(pconn);
249  return NULL;
250  }
251  octstr_destroy(sql);
252  octstr_destroy(like);
253  gwlist_destroy(binds, NULL);
254  dbpool_conn_produce(pconn);
255 
256 #define LO2CSTR(r, i) octstr_get_cstr(gwlist_get(r, i))
257 
258  if (gwlist_len(result) > 0) {
259  row = gwlist_extract_first(result);
260  res = dlr_entry_create();
261  gw_assert(res != NULL);
262  res->mask = atoi(LO2CSTR(row,0));
263  res->service = octstr_create(LO2CSTR(row, 1));
264  res->url = octstr_create(LO2CSTR(row,2));
265  res->source = octstr_create(LO2CSTR(row, 3));
266  res->destination = octstr_create(LO2CSTR(row, 4));
267  res->boxc_id = octstr_create(LO2CSTR(row, 5));
269  res->smsc = octstr_duplicate(smsc);
270  }
271  gwlist_destroy(result, NULL);
272 
273 #undef LO2CSTR
274 
275  return res;
276 }
277 
278 static void dlr_update_sqlite3(const Octstr *smsc, const Octstr *ts, const Octstr *dst, int status)
279 {
280  Octstr *sql, *os_status, *like;
281  DBPoolConn *pconn;
282  List *binds = gwlist_create();
283  int res;
284 
285  debug("dlr.sqlite3", 0, "updating DLR status in database");
286 
287  pconn = dbpool_conn_consume(pool);
288  /* just for sure */
289  if (pconn == NULL)
290  return;
291 
292  if (dst)
293  like = octstr_format("AND %S LIKE '%?4'", fields->field_dst);
294  else
295  like = octstr_imm("");
296 
297  sql = octstr_format("UPDATE %S SET %S=?1 WHERE ROWID IN (SELECT ROWID FROM %S WHERE %S=?2 AND %S=?3 %S LIMIT 1)",
298  fields->table, fields->field_status, fields->table,
299  fields->field_smsc, fields->field_ts, like);
300 
301  os_status = octstr_format("%d", status);
302  gwlist_append(binds, (Octstr *)os_status); /* ?1 */
303  gwlist_append(binds, (Octstr *)smsc); /* ?2 */
304  gwlist_append(binds, (Octstr *)ts); /* ?3 */
305  if (dst)
306  gwlist_append(binds, (Octstr *)dst); /* ?4 */
307 
308 #if defined(DLR_TRACE)
309  debug("dlr.sqlite3", 0, "sql: %s", octstr_get_cstr(sql));
310 #endif
311  if ((res = dbpool_conn_update(pconn, sql, binds)) == -1)
312  error(0, "DLR: SQLite3: Error while updating dlr entry for DST<%s>", octstr_get_cstr(dst));
313  else if (!res)
314  warning(0, "DLR: SQLite3: No dlr found to update for DST<%s> (status: %d)", octstr_get_cstr(dst), status);
315 
316  dbpool_conn_produce(pconn);
317  gwlist_destroy(binds, NULL);
318  octstr_destroy(os_status);
319  octstr_destroy(sql);
320  octstr_destroy(like);
321 }
322 
323 static void dlr_flush_sqlite3 (void)
324 {
325  Octstr *sql;
326  DBPoolConn *pconn;
327  int rows;
328 
329  pconn = dbpool_conn_consume(pool);
330  /* just for sure */
331  if (pconn == NULL)
332  return;
333 
334  sql = octstr_format("DELETE FROM %S", fields->table);
335 #if defined(DLR_TRACE)
336  debug("dlr.sqlite3", 0, "sql: %s", octstr_get_cstr(sql));
337 #endif
338  rows = dbpool_conn_update(pconn, sql, NULL);
339  if (rows == -1)
340  error(0, "DLR: SQLite3: Error while flushing dlr entries from database");
341  else
342  debug("dlr.sqlite3", 0, "Flushing %d DLR entries from database", rows);
343  dbpool_conn_produce(pconn);
344  octstr_destroy(sql);
345 }
346 
347 static struct dlr_storage handles = {
348  .type = "sqlite3",
349  .dlr_messages = dlr_messages_sqlite3,
350  .dlr_shutdown = dlr_shutdown_sqlite3,
351  .dlr_add = dlr_add_sqlite3,
352  .dlr_get = dlr_get_sqlite3,
353  .dlr_remove = dlr_remove_sqlite3,
354  .dlr_update = dlr_update_sqlite3,
355  .dlr_flush = dlr_flush_sqlite3
356 };
357 
359 {
360  CfgGroup *grp;
361  List *grplist;
362  long pool_size;
363  DBConf *db_conf = NULL;
364  Octstr *id, *file;
365  int found;
366 
367  if ((grp = cfg_get_single_group(cfg, octstr_imm("dlr-db"))) == NULL)
368  panic(0, "DLR: SQLite3: group 'dlr-db' is not specified!");
369 
370  if (!(id = cfg_get(grp, octstr_imm("id"))))
371  panic(0, "DLR: SQLite3: directive 'id' is not specified!");
372 
373  /* initialize database fields */
374  fields = dlr_db_fields_create(grp);
375  gw_assert(fields != NULL);
376 
377  grplist = cfg_get_multi_group(cfg, octstr_imm("sqlite3-connection"));
378  found = 0;
379  while (grplist && (grp = gwlist_extract_first(grplist)) != NULL) {
380  Octstr *p = cfg_get(grp, octstr_imm("id"));
381  if (p != NULL && octstr_compare(p, id) == 0) {
382  found = 1;
383  }
384  if (p != NULL)
385  octstr_destroy(p);
386  if (found == 1)
387  break;
388  }
389  gwlist_destroy(grplist, NULL);
390 
391  if (found == 0)
392  panic(0, "DLR: SQLite3: connection settings for id '%s' are not specified!",
393  octstr_get_cstr(id));
394 
395  file = cfg_get(grp, octstr_imm("database"));
396  if (cfg_get_integer(&pool_size, grp, octstr_imm("max-connections")) == -1)
397  pool_size = 1;
398 
399  if (file == NULL)
400  panic(0, "DLR: SQLite3: connection settings missing for id '%s', please"
401  " check you configuration.",octstr_get_cstr(id));
402 
403  /* ok we are ready to create dbpool */
404  db_conf = gw_malloc(sizeof(*db_conf));
405  db_conf->sqlite3 = gw_malloc(sizeof(SQLite3Conf));
406 
407  db_conf->sqlite3->file = file;
408 
409  pool = dbpool_create(DBPOOL_SQLITE3, db_conf, pool_size);
410  gw_assert(pool != NULL);
411 
412  if (dbpool_conn_count(pool) == 0)
413  panic(0, "DLR: SQLite3: Could not establish sqlite3 connection(s).");
414 
415  octstr_destroy(id);
416 
417  return &handles;
418 }
419 #else
420 /* no sqlite3 support build in */
422 {
423  return NULL;
424 }
425 #endif /* HAVE_SQLITE3 */
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
long dbpool_conn_count(DBPool *p)
SQLite3Conf * sqlite3
Definition: dbpool.h:170
DBPool * dbpool_create(enum db_type db_type, DBConf *conf, unsigned int connections)
Octstr * file
Definition: dbpool.h:133
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
FILE * file
Definition: log.c:133
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
#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
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
struct dlr_storage * dlr_init_sqlite3(Cfg *cfg)
Definition: dlr_sqlite3.c:421
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.