Kannel: Open Source WAP and SMS gateway  svn-r5335
sqlbox_oracle.c
Go to the documentation of this file.
1 #include "gwlib/gwlib.h"
2 #ifdef HAVE_ORACLE
3 #include "gwlib/dbpool.h"
4 #include <oci.h>
5 #include "sqlbox_oracle.h"
6 
7 #define sql_update dbpool_conn_update
8 #define sql_select dbpool_conn_select
9 
10 static Octstr *sqlbox_logtable;
11 static Octstr *sqlbox_insert_table;
12 
13 /*
14  * Our connection pool to oracle.
15  */
16 
17 static DBPool *pool = NULL;
18 
19 void sqlbox_configure_oracle(Cfg* cfg)
20 {
21  DBPoolConn *pc;
22  CfgGroup *grp;
23  Octstr *sql;
24 
25  if (!(grp = cfg_get_single_group(cfg, octstr_imm("sqlbox"))))
26  panic(0, "SQLBOX: Oracle: group 'sqlbox' is not specified!");
27 
28  sqlbox_logtable = cfg_get(grp, octstr_imm("sql-log-table"));
29  if (sqlbox_logtable == NULL) {
30  panic(0, "Parameter 'sql-log-table' not configured.");
31  }
32  sqlbox_insert_table = cfg_get(grp, octstr_imm("sql-insert-table"));
33  if (sqlbox_insert_table == NULL) {
34  panic(0, "Parameter 'sql-insert-table' not configured.");
35  }
36 
37  pc = dbpool_conn_consume(pool);
38  if (pc == NULL) {
39  error(0, "Oracle: DBPool Error!");
40  return;
41  }
42 
43  /* create send_sms && sent_sms tables if they do not exist */
44  sql = octstr_format(SQLBOX_ORACLE_CREATE_LOG_TABLE, sqlbox_logtable, sqlbox_logtable);
45 #if defined(SQLBOX_TRACE)
46  debug("SQLBOX", 0, "sql: %s", octstr_get_cstr(sql));
47 #endif
48  sql_update(pc, sql, NULL);
49  octstr_destroy(sql);
50 
51  sql = octstr_format(SQLBOX_ORACLE_CREATE_INSERT_TABLE, sqlbox_insert_table, sqlbox_insert_table);
52 #if defined(SQLBOX_TRACE)
53  debug("SQLBOX", 0, "sql: %s", octstr_get_cstr(sql));
54 #endif
55  sql_update(pc, sql, NULL);
56  octstr_destroy(sql);
57  /*
58  * Oracle implementation using a sequence and a trigger for auto_increment fields.
59  */
60  sql = octstr_format(SQLBOX_ORACLE_CREATE_LOG_SEQUENCE, sqlbox_logtable);
61 #if defined(SQLBOX_TRACE)
62  debug("SQLBOX", 0, "sql: %s", octstr_get_cstr(sql));
63 #endif
64  sql_update(pc, sql, NULL);
65  octstr_destroy(sql);
66 
67  sql = octstr_format(SQLBOX_ORACLE_CREATE_INSERT_SEQUENCE, sqlbox_insert_table);
68 #if defined(SQLBOX_TRACE)
69  debug("SQLBOX", 0, "sql: %s", octstr_get_cstr(sql));
70 #endif
71  sql_update(pc, sql, NULL);
72  octstr_destroy(sql);
73 
74  sql = octstr_format(SQLBOX_ORACLE_CREATE_LOG_TRIGGER, sqlbox_logtable, sqlbox_logtable, sqlbox_logtable);
75 #if defined(SQLBOX_TRACE)
76  debug("SQLBOX", 0, "sql: %s", octstr_get_cstr(sql));
77 #endif
78  sql_update(pc, sql, NULL);
79  octstr_destroy(sql);
80 
81  sql = octstr_format(SQLBOX_ORACLE_CREATE_INSERT_TRIGGER, sqlbox_insert_table, sqlbox_insert_table, sqlbox_insert_table);
82 #if defined(SQLBOX_TRACE)
83  debug("SQLBOX", 0, "sql: %s", octstr_get_cstr(sql));
84 #endif
85  sql_update(pc, sql, NULL);
86  octstr_destroy(sql);
87  /* end table creation */
88 
90 
91 }
92 
93 #define octstr_null_create(x) ((x != NULL) ? octstr_create(x) : octstr_create(""))
94 #define atol_null(x) ((x != NULL) ? atol(x) : -1)
95 #define get_oracle_octstr_col(x) (octstr_create(octstr_get_cstr(gwlist_get(row,x))))
96 #define get_oracle_long_col(x) (atol(octstr_get_cstr(gwlist_get(row,x))))
97 Msg *oracle_fetch_msg()
98 {
99  Msg *msg = NULL;
100  Octstr *sql, *delet, *id;
101  List *res, *row, *binds = gwlist_create();
102  int ret;
103  DBPoolConn *pc;
104 
105  pc = dbpool_conn_consume(pool);
106  if (pc == NULL) {
107  error(0, "Oracle: DBPool error!");
108  return;
109  }
110 
111  sql = octstr_format(SQLBOX_ORACLE_SELECT_QUERY, sqlbox_insert_table);
112 #if defined(SQLBOX_TRACE)
113  debug("SQLBOX", 0, "sql: %s", octstr_get_cstr(sql));
114 #endif
115  if (sql_select(pc, sql, NULL, &res) != 0) {
116  debug("sqlbox", 0, "SQL statement failed: %s", octstr_get_cstr(sql));
117  } else {
118  if (gwlist_len(res) > 0) {
119  row = gwlist_extract_first(res);
120  id = get_oracle_octstr_col(0);
121  /* save fields in this row as msg struct */
122  msg = msg_create(sms);
123  /* we abuse the foreign_id field in the message struct for our sql_id value */
124  msg->sms.foreign_id = get_oracle_octstr_col(0);
125  msg->sms.sender = get_oracle_octstr_col(2);
126  msg->sms.receiver = get_oracle_octstr_col(3);
127  msg->sms.udhdata = get_oracle_octstr_col(4);
128  msg->sms.msgdata = get_oracle_octstr_col(5);
129  msg->sms.time = get_oracle_long_col(6);
130  msg->sms.smsc_id = get_oracle_octstr_col(7);
131  msg->sms.service = get_oracle_octstr_col(8);
132  msg->sms.account = get_oracle_octstr_col(9);
133  /* msg->sms.id = get_oracle_long_col(10); */
134  msg->sms.sms_type = get_oracle_long_col(11);
135  msg->sms.mclass = get_oracle_long_col(12);
136  msg->sms.mwi = get_oracle_long_col(13);
137  msg->sms.coding = get_oracle_long_col(14);
138  msg->sms.compress = get_oracle_long_col(15);
139  msg->sms.validity = get_oracle_long_col(16);
140  msg->sms.deferred = get_oracle_long_col(17);
141  msg->sms.dlr_mask = get_oracle_long_col(18);
142  msg->sms.dlr_url = get_oracle_octstr_col(19);
143  msg->sms.pid = get_oracle_long_col(20);
144  msg->sms.alt_dcs = get_oracle_long_col(21);
145  msg->sms.rpi = get_oracle_long_col(22);
146  msg->sms.charset = get_oracle_octstr_col(23);
147  msg->sms.binfo = get_oracle_octstr_col(25);
148  msg->sms.binfo = get_oracle_octstr_col(26);
149  if (gwlist_get(row,24) == NULL) {
150  msg->sms.boxc_id= octstr_duplicate(sqlbox_id);
151  }
152  else {
153  msg->sms.boxc_id= get_oracle_octstr_col(24);
154  }
155  /* delete current row */
156  delet = octstr_format(SQLBOX_ORACLE_DELETE_QUERY, sqlbox_insert_table);
157 
158  gwlist_append(binds, id); /* :1 */
159 #if defined(SQLBOX_TRACE)
160  debug("SQLBOX", 0, "sql: %s", octstr_get_cstr(delet));
161 #endif
162  sql_update(pc, delet, binds);
163  octstr_destroy(id);
164  gwlist_destroy(binds, NULL);
165  octstr_destroy(delet);
167  }
168  gwlist_destroy(res, NULL);
169  }
171  octstr_destroy(sql);
172  return msg;
173 }
174 
175 static Octstr *get_numeric_value_or_return_null(long int num)
176 {
177  return octstr_format("%ld", num);
178 }
179 
180 static Octstr *get_string_value_or_return_null(Octstr *str)
181 {
182  return octstr_format("%S", str);
183 }
184 
185 #define st_num(x) (stuffer[stuffcount++] = get_numeric_value_or_return_null(x))
186 #define st_str(x) (stuffer[stuffcount++] = get_string_value_or_return_null(x))
187 
188 void oracle_save_msg(Msg *msg, Octstr *momt /*, Octstr smsbox_id */)
189 {
190  Octstr *sql;
191  Octstr *stuffer[30];
192  int stuffcount = 0;
193  List *binds = gwlist_create();
194  DBPoolConn *pc;
195  pc = dbpool_conn_consume(pool);
196  if (pc == NULL) {
197  error(0, "Oracle: DBPool Error!");
198  return;
199  }
200 
201  sql = octstr_format(SQLBOX_ORACLE_INSERT_QUERY, sqlbox_logtable);
202 
203  gwlist_append(binds, momt); /* :1 */
204  gwlist_append(binds, st_str(msg->sms.sender)); /* :2 */
205  gwlist_append(binds, st_str(msg->sms.receiver)); /* :3 */
206  gwlist_append(binds, st_str(msg->sms.udhdata)); /* :4 */
207  gwlist_append(binds, st_str(msg->sms.msgdata)); /* :5 */
208  gwlist_append(binds, st_num(msg->sms.time)); /* :6 */
209  gwlist_append(binds, st_str(msg->sms.smsc_id)); /* :7 */
210  gwlist_append(binds, st_str(msg->sms.service)); /* :8 */
211  gwlist_append(binds, st_str(msg->sms.account)); /* :9 */
212  gwlist_append(binds, st_num(msg->sms.sms_type)); /* :10 */
213  gwlist_append(binds, st_num(msg->sms.mclass)); /* :11 */
214  gwlist_append(binds, st_num(msg->sms.mwi)); /* :12 */
215  gwlist_append(binds, st_num(msg->sms.coding)); /* :13 */
216  gwlist_append(binds, st_num(msg->sms.compress)); /* :14 */
217  gwlist_append(binds, st_num(msg->sms.validity)); /* :15 */
218  gwlist_append(binds, st_num(msg->sms.deferred)); /* :16 */
219  gwlist_append(binds, st_num(msg->sms.dlr_mask)); /* :17 */
220  gwlist_append(binds, st_str(msg->sms.dlr_url)); /* :18 */
221  gwlist_append(binds, st_num(msg->sms.pid)); /* :19 */
222  gwlist_append(binds, st_num(msg->sms.alt_dcs)); /* :20 */
223  gwlist_append(binds, st_num(msg->sms.rpi)); /* :21 */
224  gwlist_append(binds, st_str(msg->sms.charset)); /* :22 */
225  gwlist_append(binds, st_str(msg->sms.boxc_id)); /* :23 */
226  gwlist_append(binds, st_str(msg->sms.binfo)); /* :24 */
227  gwlist_append(binds, st_str(msg->sms.meta_data)); /* :25 */
228  gwlist_append(binds, st_str(msg->sms.foreign_id)); /* :26 */
229 #if defined(SQLBOX_TRACE)
230  debug("SQLBOX", 0, "sql: %s", octstr_get_cstr(sql));
231 #endif
232  sql_update(pc, sql, binds);
233  while (stuffcount > 0) {
234  octstr_destroy(stuffer[--stuffcount]);
235  }
237  octstr_destroy(sql);
238  gwlist_destroy(binds, NULL);
239 }
240 
241 void oracle_leave()
242 {
243  dbpool_destroy(pool);
244 }
245 
246 struct server_type *sqlbox_init_oracle(Cfg* cfg)
247 {
248  CfgGroup *grp;
249  List *grplist;
250  Octstr *oracle_user, *oracle_pass, *oracle_tnsname, *oracle_id;
251  Octstr *p = NULL;
252  long pool_size;
253  DBConf *db_conf = NULL;
254  struct server_type *res = NULL;
255 
256  /*
257  * check for all mandatory directives that specify the field names
258  * of the used Oracle table
259  */
260  if (!(grp = cfg_get_single_group(cfg, octstr_imm("sqlbox"))))
261  panic(0, "SQLBOX: Oracle: group 'sqlbox' is not specified!");
262 
263  if (!(oracle_id = cfg_get(grp, octstr_imm("id"))))
264  panic(0, "SQLBOX: Oracle: directive 'id' is not specified!");
265 
266  /*
267  * now grap the required information from the 'oracle-connection' group
268  * with the oracle-id we just obtained
269  *
270  * we have to loop through all available Oracle connection definitions
271  * and search for the one we are looking for
272  */
273 
274  grplist = cfg_get_multi_group(cfg, octstr_imm("oracle-connection"));
275  while (grplist && (grp = (CfgGroup *)gwlist_extract_first(grplist)) != NULL) {
276  p = cfg_get(grp, octstr_imm("id"));
277  if (p != NULL && octstr_compare(p, oracle_id) == 0) {
278  goto found;
279  }
280  if (p != NULL) octstr_destroy(p);
281  }
282  panic(0, "SQLBOX: Oracle: connection settings for id '%s' are not specified!",
283  octstr_get_cstr(oracle_id));
284 
285 found:
286  octstr_destroy(p);
287  gwlist_destroy(grplist, NULL);
288 
289  if (cfg_get_integer(&pool_size, grp, octstr_imm("max-connections")) == -1 || pool_size == 0)
290  pool_size = 1;
291 
292  if (!(oracle_user = cfg_get(grp, octstr_imm("username"))))
293  panic(0, "SQLBOX: Oracle: directive 'username' is not specified!");
294  if (!(oracle_pass = cfg_get(grp, octstr_imm("password"))))
295  panic(0, "SQLBOX: Oracle: directive 'password' is not specified!");
296  if (!(oracle_tnsname = cfg_get(grp, octstr_imm("tnsname"))))
297  panic(0, "SQLBOX: Oracle: directive 'tnsname' is not specified!");
298 
299  /*
300  * ok, ready to connect to Oracle
301  */
302  db_conf = gw_malloc(sizeof(DBConf));
303  gw_assert(db_conf != NULL);
304 
305  db_conf->oracle = gw_malloc(sizeof(OracleConf));
306  gw_assert(db_conf->oracle != NULL);
307 
308  db_conf->oracle->username = oracle_user;
309  db_conf->oracle->password = oracle_pass;
310  db_conf->oracle->tnsname = oracle_tnsname;
311 
312  pool = dbpool_create(DBPOOL_ORACLE, db_conf, pool_size);
313  gw_assert(pool != NULL);
314 
315  /*
316  * XXX should a failing connect throw panic?!
317  */
318  if (dbpool_conn_count(pool) == 0)
319  panic(0,"SQLBOX: Oracle: database pool has no connections!");
320 
321  octstr_destroy(oracle_id);
322 
323  res = gw_malloc(sizeof(struct server_type));
324  gw_assert(res != NULL);
325 
326  res->type = octstr_create("Oracle");
327  res->sql_enter = sqlbox_configure_oracle;
328  res->sql_leave = oracle_leave;
329  res->sql_fetch_msg = oracle_fetch_msg;
330  res->sql_save_msg = oracle_save_msg;
331  res->sql_fetch_msg_list = NULL;
332  res->sql_save_list = NULL;
333  return res;
334 }
335 #endif
void error(int err, const char *fmt,...)
Definition: log.c:648
int(* sql_fetch_msg_list)(List *, long)
Definition: sqlbox_sql.h:19
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)
gw_assert(wtls_machine->packet_to_send !=NULL)
void gwlist_append(List *list, void *item)
Definition: list.c:179
long gwlist_len(List *list)
Definition: list.c:166
void * gwlist_get(List *list, long pos)
Definition: list.c:292
#define cfg_get(grp, varname)
Definition: cfg.h:86
#define msg_create(type)
Definition: msg.h:136
void(* sql_save_list)(List *, Octstr *, int)
Definition: sqlbox_sql.h:20
static Cfg * cfg
Definition: opensmppbox.c:95
void(* sql_leave)()
Definition: sqlbox_sql.h:16
#define octstr_get_cstr(ostr)
Definition: octstr.h:233
static struct pid_list * found
void(* sql_enter)(Cfg *)
Definition: sqlbox_sql.h:15
void dbpool_conn_produce(DBPoolConn *conn)
Octstr * username
Definition: dbpool.h:118
Octstr * octstr_imm(const char *cstr)
Definition: octstr.c:283
Definition: msg.h:79
Definition: cfg.c:164
void * gwlist_extract_first(List *list)
Definition: list.c:305
Msg *(* sql_fetch_msg)()
Definition: sqlbox_sql.h:17
#define octstr_duplicate(ostr)
Definition: octstr.h:187
List * cfg_get_multi_group(Cfg *cfg, Octstr *name)
Definition: cfg.c:645
Octstr * octstr_format(const char *fmt,...)
Definition: octstr.c:2464
void octstr_destroy(Octstr *ostr)
Definition: octstr.c:324
#define octstr_create(cstr)
Definition: octstr.h:125
void octstr_destroy_item(void *os)
Definition: octstr.c:336
Definition: dbpool.h:164
Octstr * tnsname
Definition: dbpool.h:120
void dbpool_destroy(DBPool *p)
Definition: octstr.c:118
void debug(const char *place, int err, const char *fmt,...)
Definition: log.c:726
int cfg_get_integer(long *n, CfgGroup *grp, Octstr *varname)
Definition: cfg.c:742
#define panic
Definition: log.h:87
Definition: cfg.c:73
#define gwlist_create()
Definition: list.h:136
DBPoolConn * dbpool_conn_consume(DBPool *p)
Octstr * type
Definition: sqlbox_sql.h:14
CfgGroup * cfg_get_single_group(Cfg *cfg, Octstr *name)
Definition: cfg.c:639
Octstr * sqlbox_id
Definition: sqlbox.c:95
Definition: list.c:102
static XMLRPCDocument * msg
Definition: test_xmlrpc.c:86
void(* sql_save_msg)(Msg *, Octstr *)
Definition: sqlbox_sql.h:18
OracleConf * oracle
Definition: dbpool.h:168
int octstr_compare(const Octstr *ostr1, const Octstr *ostr2)
Definition: octstr.c:871
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.