Kannel: Open Source WAP and SMS gateway  $Revision: 5037 $
dbpool_mysql.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  * dbpool_mysql.c - implement MySQL operations for generic database connection pool
59  *
60  * Stipe Tolj <stolj@wapme.de>
61  * 2003 Initial version.
62  * Alexander Malysh <a.malysh@centrium.de>
63  * 2003 Made dbpool more generic.
64  */
65 
66 #ifdef HAVE_MYSQL
67 #include <mysql.h>
68 #include <mysqld_error.h>
69 
70 
71 /*
72  * Handle temporary error codes, that will cause
73  * mysql_stmt_execute() to be retried.
74  * Add more error codes if applicable from mysql's
75  * <include>/mysq/mysqld_errno.h header file
76  */
77 static inline int mysql_er_temp(const int rc)
78 {
79  switch (rc) {
80  case ER_LOCK_WAIT_TIMEOUT:
81  case ER_LOCK_DEADLOCK:
82  case ER_GET_TEMPORARY_ERRMSG:
83  return 1;
84  break;
85  default:
86  return 0;
87  break;
88  }
89 
90  return 0;
91 }
92 
93 
94 static void *mysql_open_conn(const DBConf *db_conf)
95 {
96  MYSQL *mysql = NULL;
97  MySQLConf *conf = db_conf->mysql; /* make compiler happy */
98 
99  /* sanity check */
100  if (conf == NULL)
101  return NULL;
102 
103  /* pre-allocate */
104  mysql = gw_malloc(sizeof(MYSQL));
105  gw_assert(mysql != NULL);
106 
107  /* initialize mysql structures */
108  if (!mysql_init(mysql)) {
109  error(0, "MYSQL: init failed!");
110  error(0, "MYSQL: %s", mysql_error(mysql));
111  goto failed;
112  }
113 
114  if (!mysql_real_connect(mysql, octstr_get_cstr(conf->host),
115  octstr_get_cstr(conf->username),
116  octstr_get_cstr(conf->password),
117  octstr_get_cstr(conf->database),
118  conf->port, NULL, 0)) {
119  error(0, "MYSQL: can not connect to database!");
120  error(0, "MYSQL: %s", mysql_error(mysql));
121  goto failed;
122  }
123 
124  info(0, "MYSQL: Connected to server at %s.", octstr_get_cstr(conf->host));
125  info(0, "MYSQL: server version %s, client version %s.",
126  mysql_get_server_info(mysql), mysql_get_client_info());
127 
128  return mysql;
129 
130 failed:
131  if (mysql != NULL)
132  gw_free(mysql);
133  return NULL;
134 }
135 
136 
137 static void mysql_close_conn(void *conn)
138 {
139  if (conn == NULL)
140  return;
141 
142  mysql_close((MYSQL*) conn);
143  gw_free(conn);
144 }
145 
146 
147 static int mysql_check_conn(void *conn)
148 {
149  if (conn == NULL)
150  return -1;
151 
152  if (mysql_ping((MYSQL*) conn)) {
153  error(0, "MYSQL: database check failed!");
154  error(0, "MYSQL: %s", mysql_error(conn));
155  return -1;
156  }
157 
158  return 0;
159 }
160 
161 
162 static int mysql_select(void *conn, const Octstr *sql, List *binds, List **res)
163 {
164  MYSQL_STMT *stmt;
165  MYSQL_RES *result;
166  MYSQL_BIND *bind = NULL;
167  long i, binds_len;
168  int ret;
169 
170  *res = NULL;
171 
172  /* allocate statement handle */
173  stmt = mysql_stmt_init((MYSQL*) conn);
174  if (stmt == NULL) {
175  error(0, "MYSQL: mysql_stmt_init(), out of memory.");
176  return -1;
177  }
178  if (mysql_stmt_prepare(stmt, octstr_get_cstr(sql), octstr_len(sql))) {
179  error(0, "MYSQL: Unable to prepare statement: %s", mysql_stmt_error(stmt));
180  mysql_stmt_close(stmt);
181  return -1;
182  }
183  /* bind params if any */
184  binds_len = gwlist_len(binds);
185  if (binds_len > 0) {
186  bind = gw_malloc(sizeof(MYSQL_BIND) * binds_len);
187  memset(bind, 0, sizeof(MYSQL_BIND) * binds_len);
188  for (i = 0; i < binds_len; i++) {
189  Octstr *str = gwlist_get(binds, i);
190 
191  bind[i].buffer_type = MYSQL_TYPE_STRING;
192  bind[i].buffer = octstr_get_cstr(str);
193  bind[i].buffer_length = octstr_len(str);
194  }
195  /* Bind the buffers */
196  if (mysql_stmt_bind_param(stmt, bind)) {
197  error(0, "MYSQL: mysql_stmt_bind_param() failed: `%s'", mysql_stmt_error(stmt));
198  gw_free(bind);
199  mysql_stmt_close(stmt);
200  return -1;
201  }
202  }
203 
204  /* execute statement */
205  if (mysql_stmt_execute(stmt)) {
206  error(0, "MYSQL: mysql_stmt_execute() failed: `%s'", mysql_stmt_error(stmt));
207  gw_free(bind);
208  mysql_stmt_close(stmt);
209  return -1;
210  }
211  gw_free(bind);
212 
213 #define DESTROY_BIND(bind, binds_len) \
214  do { \
215  long i; \
216  for (i = 0; i < binds_len; i++) { \
217  gw_free(bind[i].buffer); \
218  gw_free(bind[i].length); \
219  gw_free(bind[i].is_null); \
220  } \
221  gw_free(bind); \
222  } while(0)
223 
224  /* Fetch result set meta information */
225  result = mysql_stmt_result_metadata(stmt);
226  if (res == NULL) {
227  error(0, "MYSQL: mysql_stmt_result_metadata() failed: `%s'", mysql_stmt_error(stmt));
228  mysql_stmt_close(stmt);
229  return -1;
230  }
231  /* Get total columns in the query */
232  binds_len = mysql_num_fields(result);
233  bind = gw_malloc(sizeof(MYSQL_BIND) * binds_len);
234  memset(bind, 0, sizeof(MYSQL_BIND) * binds_len);
235  /* bind result bind */
236  for (i = 0; i < binds_len; i++) {
237  MYSQL_FIELD *field = mysql_fetch_field(result); /* retrieve field metadata */
238 
239  debug("gwlib.dbpool_mysql", 0, "column=%s buffer_type=%d max_length=%ld length=%ld", field->name, field->type, field->max_length, field->length);
240 
241  switch(field->type) {
242  case MYSQL_TYPE_TIME:
243  case MYSQL_TYPE_DATE:
244  case MYSQL_TYPE_DATETIME:
245  case MYSQL_TYPE_TIMESTAMP:
246  bind[i].buffer_type = field->type;
247  bind[i].buffer = (char*)gw_malloc(sizeof(MYSQL_TIME));
248  bind[i].is_null = gw_malloc(sizeof(my_bool));
249  bind[i].length = gw_malloc(sizeof(unsigned long));
250  break;
251  default:
252  bind[i].buffer_type = MYSQL_TYPE_STRING;
253  bind[i].buffer = gw_malloc(field->length);
254  bind[i].buffer_length = field->length;
255  bind[i].length = gw_malloc(sizeof(unsigned long));
256  bind[i].is_null = gw_malloc(sizeof(my_bool));
257  break;
258  }
259  }
260  mysql_free_result(result);
261 
262  if (mysql_stmt_bind_result(stmt, bind)) {
263  error(0, "MYSQL: mysql_stmt_bind_result() failed: `%s'", mysql_stmt_error(stmt));
264  DESTROY_BIND(bind, binds_len);
265  mysql_stmt_close(stmt);
266  return -1;
267  }
268 
269  *res = gwlist_create();
270  while(!(ret = mysql_stmt_fetch(stmt))) {
271  List *row = gwlist_create();
272  for (i = 0; i < binds_len; i++) {
273  Octstr *str = NULL;
274  MYSQL_TIME *ts;
275 
276  if (*bind[i].is_null) {
277  gwlist_produce(row, octstr_create(""));
278  continue;
279  }
280 
281  switch(bind[i].buffer_type) {
282  case MYSQL_TYPE_DATE:
283  ts = bind[i].buffer;
284  str = octstr_format("%04d-%02d-%02d", ts->year, ts->month, ts->day);
285  break;
286  case MYSQL_TYPE_TIME:
287  case MYSQL_TYPE_DATETIME:
288  case MYSQL_TYPE_TIMESTAMP:
289  ts = bind[i].buffer;
290  str = octstr_format("%04d-%02d-%02d %02d:%02d:%02d", ts->year, ts->month, ts->day, ts->hour, ts->minute, ts->second);
291  break;
292  default:
293  if (bind[i].length == 0)
294  str= octstr_create("");
295  else
296  str = octstr_create_from_data(bind[i].buffer, *bind[i].length);
297  break;
298  }
299  gwlist_produce(row, str);
300  }
301  gwlist_produce(*res, row);
302  }
303  DESTROY_BIND(bind, binds_len);
304 #undef DESTROY_BIND
305 
306  /* any errors by fetch? */
307  if (ret != MYSQL_NO_DATA) {
308  List *row;
309  error(0, "MYSQL: mysql_stmt_bind_result() failed: `%s'", mysql_stmt_error(stmt));
310  mysql_stmt_close(stmt);
311  while((row = gwlist_extract_first(*res)) != NULL)
313  gwlist_destroy(*res, NULL);
314  *res = NULL;
315  return -1;
316  }
317 
318  mysql_stmt_close(stmt);
319 
320  return 0;
321 }
322 
323 
324 static int mysql_update(void *conn, const Octstr *sql, List *binds)
325 {
326  MYSQL_STMT *stmt;
327  MYSQL_BIND *bind = NULL;
328  long i, binds_len;
329  int ret;
330 
331  /* allocate statement handle */
332  stmt = mysql_stmt_init((MYSQL*) conn);
333  if (stmt == NULL) {
334  error(0, "MYSQL: mysql_stmt_init(), out of memory.");
335  return -1;
336  }
337  if (mysql_stmt_prepare(stmt, octstr_get_cstr(sql), octstr_len(sql))) {
338  error(0, "MYSQL: Unable to prepare statement: `%s'", mysql_stmt_error(stmt));
339  mysql_stmt_close(stmt);
340  return -1;
341  }
342  /* bind params if any */
343  binds_len = gwlist_len(binds);
344  if (binds_len > 0) {
345  bind = gw_malloc(sizeof(MYSQL_BIND) * binds_len);
346  memset(bind, 0, sizeof(MYSQL_BIND) * binds_len);
347  for (i = 0; i < binds_len; i++) {
348  Octstr *str = gwlist_get(binds, i);
349 
350  bind[i].buffer_type = MYSQL_TYPE_STRING;
351  bind[i].buffer = octstr_get_cstr(str);
352  bind[i].buffer_length = octstr_len(str);
353  }
354  /* Bind the buffers */
355  if (mysql_stmt_bind_param(stmt, bind)) {
356  error(0, "MYSQL: mysql_stmt_bind_param() failed: `%s'", mysql_stmt_error(stmt));
357  gw_free(bind);
358  mysql_stmt_close(stmt);
359  return -1;
360  }
361  }
362 
363  /* execute statement */
364 retry:
365  ret = mysql_stmt_execute(stmt);
366  if (ret != 0) {
367  ret = mysql_stmt_errno(stmt);
368  if (mysql_er_temp(ret)) {
369  warning(0, "MYSQL: mysql_stmt_execute() failed: %d: `%s'. Retrying.", ret, mysql_stmt_error(stmt));
370  goto retry;
371  }
372  else {
373  error(0, "MYSQL: mysql_stmt_execute() failed: %d: `%s'", ret, mysql_stmt_error(stmt));
374  gw_free(bind);
375  mysql_stmt_close(stmt);
376  return -1;
377  }
378  }
379  gw_free(bind);
380 
381  ret = mysql_stmt_affected_rows(stmt);
382  mysql_stmt_close(stmt);
383 
384  return ret;
385 }
386 
387 
388 static void mysql_conf_destroy(DBConf *db_conf)
389 {
390  MySQLConf *conf = db_conf->mysql;
391 
392  octstr_destroy(conf->host);
393  octstr_destroy(conf->username);
394  octstr_destroy(conf->password);
395  octstr_destroy(conf->database);
396 
397  gw_free(conf);
398  gw_free(db_conf);
399 }
400 
401 
402 static struct db_ops mysql_ops = {
403  .open = mysql_open_conn,
404  .close = mysql_close_conn,
405  .check = mysql_check_conn,
406  .select = mysql_select,
407  .update = mysql_update,
408  .conf_destroy = mysql_conf_destroy
409 };
410 
411 #endif /* HAVE_MYSQL */
412 
void error(int err, const char *fmt,...)
Definition: log.c:612
void info(int err, const char *fmt,...)
Definition: log.c:636
void gwlist_produce(List *list, void *item)
Definition: list.c:411
long gwlist_len(List *list)
Definition: list.c:166
void * gwlist_get(List *list, long pos)
Definition: list.c:292
#define octstr_get_cstr(ostr)
Definition: octstr.h:233
void * gwlist_extract_first(List *list)
Definition: list.c:305
Octstr * password
Definition: dbpool.h:103
void warning(int err, const char *fmt,...)
Definition: log.c:624
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)
Octstr * host
Definition: dbpool.h:100
Definition: dbpool.h:164
long octstr_len(const Octstr *ostr)
Definition: octstr.c:340
Definition: octstr.c:118
MySQLConf * mysql
Definition: dbpool.h:166
void debug(const char *place, int err, const char *fmt,...)
Definition: log.c:690
Octstr * username
Definition: dbpool.h:102
Octstr * database
Definition: dbpool.h:104
#define gwlist_create()
Definition: list.h:136
long port
Definition: dbpool.h:101
#define octstr_create_from_data(data, len)
Definition: octstr.h:134
Definition: list.c:102
void *(* open)(const DBConf *conf)
Definition: dbpool_p.h:73
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.