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