1 /**
2 *
3 * Hoel database abstraction library
4 *
5 * hoel-mariadb.c: Maria DB/Mysql specific functions
6 *
7 * Copyright 2015-2016 Nicolas Mora <mail@babelouest.org>
8 *
9 * This program is free software; you can redistribute it and/or
10 * modify it under the terms of the GNU Lesser General Public License
11 * as published by the Free Software Foundation;
12 * version 2.1 of the License.
13 *
14 * This library is distributed in the hope that it will be useful,
15 * but WITHOUT ANY WARRANTY; without even the implied warranty of
16 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
17 * GNU GENERAL PUBLIC LICENSE for more details.
18 *
19 * You should have received a copy of the GNU General Public
20 * License along with this library. If not, see <http://www.gnu.org/licenses/>.
21 *
22 */
23 #include "hoel.h"
24 #include "h-private.h"
25
26 #ifdef _HOEL_MARIADB
27 /* MariaDB library Includes */
28 #include <mysql.h>
29 #include <string.h>
30
31 /**
32 * MariaDB handle
33 */
34 struct _h_mariadb {
35 char * host;
36 char * user;
37 char * passwd;
38 char * db;
39 unsigned int port;
40 char * unix_socket;
41 unsigned long flags;
42 MYSQL * db_handle;
43 pthread_mutex_t lock;
44 };
45
46 /**
47 * h_connect_mariadb
48 * Opens a database connection to a mariadb server
49 * return pointer to a struct _h_connection * on sucess, NULL on error
50 */
h_connect_mariadb(const char * host,const char * user,const char * passwd,const char * db,const unsigned int port,const char * unix_socket)51 struct _h_connection * h_connect_mariadb(const char * host, const char * user, const char * passwd, const char * db, const unsigned int port, const char * unix_socket) {
52 struct _h_connection * conn = NULL;
53 pthread_mutexattr_t mutexattr;
54 my_bool reconnect = 1;
55
56 if (host != NULL && db != NULL) {
57 conn = malloc(sizeof(struct _h_connection));
58 if (conn == NULL) {
59 y_log_message(Y_LOG_LEVEL_ERROR, "Hoel - Error allocating memory for conn");
60 return NULL;
61 }
62
63 conn->type = HOEL_DB_TYPE_MARIADB;
64 conn->connection = malloc(sizeof(struct _h_mariadb));
65 if (conn->connection == NULL) {
66 y_log_message(Y_LOG_LEVEL_ERROR, "Hoel - Error allocating memory for conn->connection");
67 free(conn);
68 return NULL;
69 }
70 if (mysql_library_init(0, NULL, NULL)) {
71 y_log_message(Y_LOG_LEVEL_ERROR, "mysql_library_init error, aborting");
72 free(conn);
73 return NULL;
74 }
75 ((struct _h_mariadb *)conn->connection)->db_handle = mysql_init(NULL);
76 if (((struct _h_mariadb *)conn->connection)->db_handle == NULL) {
77 y_log_message(Y_LOG_LEVEL_ERROR, "mysql_init error, aborting");
78 free(conn);
79 return NULL;
80 }
81 if (mysql_real_connect(((struct _h_mariadb *)conn->connection)->db_handle,
82 host, user, passwd, db, port, unix_socket, CLIENT_COMPRESS) == NULL) {
83 y_log_message(Y_LOG_LEVEL_ERROR, "Error connecting to mariadb database %s", db);
84 y_log_message(Y_LOG_LEVEL_DEBUG, "Error message: \"%s\"", mysql_error(((struct _h_mariadb *)conn->connection)->db_handle));
85 mysql_close(((struct _h_mariadb *)conn->connection)->db_handle);
86 free(conn);
87 return NULL;
88 } else {
89 /* Set MYSQL_OPT_RECONNECT to true to reconnect automatically when connection is closed by the server (to avoid CR_SERVER_GONE_ERROR) */
90 mysql_options(((struct _h_mariadb *)conn->connection)->db_handle, MYSQL_OPT_RECONNECT, &reconnect);
91 /* Initialize MUTEX for connection */
92 pthread_mutexattr_init ( &mutexattr );
93 pthread_mutexattr_settype( &mutexattr, PTHREAD_MUTEX_RECURSIVE );
94 if (pthread_mutex_init(&(((struct _h_mariadb *)conn->connection)->lock), &mutexattr) != 0) {
95 y_log_message(Y_LOG_LEVEL_ERROR, "Impossible to initialize Mutex Lock for MariaDB connection");
96 }
97 pthread_mutexattr_destroy( &mutexattr );
98 return conn;
99 }
100 }
101 return conn;
102 }
103
104 /**
105 * close connection to database
106 */
h_close_mariadb(struct _h_connection * conn)107 void h_close_mariadb(struct _h_connection * conn) {
108 mysql_close(((struct _h_mariadb *)conn->connection)->db_handle);
109 mysql_library_end();
110 pthread_mutex_destroy(&((struct _h_mariadb *)conn->connection)->lock);
111 }
112
113 /**
114 * escape a string
115 * returned value must be free'd after use
116 */
h_escape_string_mariadb(const struct _h_connection * conn,const char * unsafe)117 char * h_escape_string_mariadb(const struct _h_connection * conn, const char * unsafe) {
118 char * escaped = malloc(2 * strlen(unsafe) + sizeof(char));
119 if (escaped == NULL) {
120 y_log_message(Y_LOG_LEVEL_ERROR, "Hoel - Error allocating memory for escaped");
121 return NULL;
122 }
123 mysql_real_escape_string(((struct _h_mariadb *)conn->connection)->db_handle, escaped, unsafe, strlen(unsafe));
124 return escaped;
125 }
126
127 /**
128 * Escapes a string and returns it ready to be inserted in the query
129 * returned value must be free'd after use
130 */
h_escape_string_with_quotes_mariadb(const struct _h_connection * conn,const char * unsafe)131 char * h_escape_string_with_quotes_mariadb(const struct _h_connection * conn, const char * unsafe) {
132 char * escaped = h_escape_string_mariadb(conn, unsafe), * escaped_returned = NULL;
133 if (escaped == NULL) {
134 y_log_message(Y_LOG_LEVEL_ERROR, "Hoel - Error allocating memory for escaped");
135 return NULL;
136 }
137 escaped_returned = msprintf("'%s'", escaped);
138 o_free(escaped);
139 return escaped_returned;
140 }
141
142 /**
143 * Return the id of the last inserted value
144 */
h_last_insert_id_mariadb(const struct _h_connection * conn)145 long long int h_last_insert_id_mariadb(const struct _h_connection * conn) {
146 long long int id = mysql_insert_id(((struct _h_mariadb *)conn->connection)->db_handle);
147 if (id <= 0) {
148 y_log_message(Y_LOG_LEVEL_ERROR, "Error executing mysql_insert_id");
149 y_log_message(Y_LOG_LEVEL_DEBUG, "Error message: \"%s\"", mysql_error(((struct _h_mariadb *)conn->connection)->db_handle));
150 }
151 return id;
152 }
153
154 /**
155 * h_execute_query_mariadb
156 * Execute a query on a mariadb connection, set the result structure with the returned values
157 * Should not be executed by the user because all parameters are supposed to be correct
158 * if result is NULL, the query is executed but no value will be returned
159 * return H_OK on success
160 */
h_execute_query_mariadb(const struct _h_connection * conn,const char * query,struct _h_result * h_result)161 int h_execute_query_mariadb(const struct _h_connection * conn, const char * query, struct _h_result * h_result) {
162 MYSQL_RES * result;
163 uint num_fields, col, row;
164 MYSQL_ROW m_row;
165 MYSQL_FIELD * fields;
166 struct _h_data * data, * cur_row = NULL;
167 unsigned long * lengths;
168 int res;
169
170 if (pthread_mutex_lock(&(((struct _h_mariadb *)conn->connection)->lock))) {
171 return H_ERROR_QUERY;
172 }
173 if (mysql_query(((struct _h_mariadb *)conn->connection)->db_handle, query)) {
174 y_log_message(Y_LOG_LEVEL_ERROR, "Error executing sql query");
175 y_log_message(Y_LOG_LEVEL_DEBUG, "Error message: \"%s\"", mysql_error(((struct _h_mariadb *)conn->connection)->db_handle));
176 y_log_message(Y_LOG_LEVEL_DEBUG, "Query: \"%s\"", query);
177 pthread_mutex_unlock(&(((struct _h_mariadb *)conn->connection)->lock));
178 return H_ERROR_QUERY;
179 }
180
181 if (h_result != NULL) {
182 result = mysql_store_result(((struct _h_mariadb *)conn->connection)->db_handle);
183
184 if (result == NULL) {
185 y_log_message(Y_LOG_LEVEL_ERROR, "Error executing mysql_store_result");
186 y_log_message(Y_LOG_LEVEL_DEBUG, "Error message: \"%s\"", mysql_error(((struct _h_mariadb *)conn->connection)->db_handle));
187 pthread_mutex_unlock(&(((struct _h_mariadb *)conn->connection)->lock));
188 return H_ERROR_QUERY;
189 }
190
191 num_fields = mysql_num_fields(result);
192 fields = mysql_fetch_fields(result);
193
194 h_result->nb_rows = 0;
195 h_result->nb_columns = num_fields;
196 h_result->data = NULL;
197 for (row = 0; (m_row = mysql_fetch_row(result)) != NULL; row++) {
198 cur_row = NULL;
199 lengths = mysql_fetch_lengths(result);
200 for (col=0; col<num_fields; col++) {
201 data = h_get_mariadb_value(m_row[col], lengths[col], fields[col].type);
202 res = h_row_add_data(&cur_row, data, col);
203 h_clean_data_full(data);
204 if (res != H_OK) {
205 mysql_free_result(result);
206 pthread_mutex_unlock(&(((struct _h_mariadb *)conn->connection)->lock));
207 return res;
208 }
209 }
210 res = h_result_add_row(h_result, cur_row, row);
211 if (res != H_OK) {
212 mysql_free_result(result);
213 pthread_mutex_unlock(&(((struct _h_mariadb *)conn->connection)->lock));
214 return res;
215 }
216 }
217 mysql_free_result(result);
218 }
219
220 pthread_mutex_unlock(&(((struct _h_mariadb *)conn->connection)->lock));
221 return H_OK;
222 }
223
224 /**
225 * h_execute_query_json_mariadb
226 * Execute a query on a mariadb connection, set the returned values in the json result
227 * Should not be executed by the user because all parameters are supposed to be correct
228 * return H_OK on success
229 */
h_execute_query_json_mariadb(const struct _h_connection * conn,const char * query,json_t ** j_result)230 int h_execute_query_json_mariadb(const struct _h_connection * conn, const char * query, json_t ** j_result) {
231 MYSQL_RES * result;
232 uint num_fields, col, row;
233 MYSQL_ROW m_row;
234 MYSQL_FIELD * fields;
235 unsigned long * lengths;
236 json_t * j_data;
237 struct _h_data * h_data;
238 char date_stamp[20];
239
240 if (pthread_mutex_lock(&(((struct _h_mariadb *)conn->connection)->lock))) {
241 return H_ERROR_QUERY;
242 }
243
244 if (j_result == NULL) {
245 pthread_mutex_unlock(&(((struct _h_mariadb *)conn->connection)->lock));
246 return H_ERROR_PARAMS;
247 }
248
249 *j_result = json_array();
250 if (*j_result == NULL) {
251 y_log_message(Y_LOG_LEVEL_ERROR, "Hoel - Error allocating memory for *j_result");
252 pthread_mutex_unlock(&(((struct _h_mariadb *)conn->connection)->lock));
253 return H_ERROR_MEMORY;
254 }
255
256 if (mysql_query(((struct _h_mariadb *)conn->connection)->db_handle, query)) {
257 y_log_message(Y_LOG_LEVEL_ERROR, "Error executing sql query");
258 y_log_message(Y_LOG_LEVEL_DEBUG, "Error message: \"%s\"", mysql_error(((struct _h_mariadb *)conn->connection)->db_handle));
259 y_log_message(Y_LOG_LEVEL_DEBUG, "Query: \"%s\"", query);
260 pthread_mutex_unlock(&(((struct _h_mariadb *)conn->connection)->lock));
261 json_decref(*j_result);
262 return H_ERROR_QUERY;
263 }
264
265 result = mysql_store_result(((struct _h_mariadb *)conn->connection)->db_handle);
266
267 if (result == NULL) {
268 y_log_message(Y_LOG_LEVEL_ERROR, "Error executing mysql_store_result");
269 y_log_message(Y_LOG_LEVEL_DEBUG, "Error message: \"%s\"", mysql_error(((struct _h_mariadb *)conn->connection)->db_handle));
270 pthread_mutex_unlock(&(((struct _h_mariadb *)conn->connection)->lock));
271 json_decref(*j_result);
272 return H_ERROR_QUERY;
273 }
274
275 num_fields = mysql_num_fields(result);
276 fields = mysql_fetch_fields(result);
277
278 for (row = 0; (m_row = mysql_fetch_row(result)) != NULL; row++) {
279 j_data = json_object();
280 if (j_data == NULL) {
281 y_log_message(Y_LOG_LEVEL_ERROR, "Hoel - Error allocating memory for j_data");
282 json_decref(*j_result);
283 pthread_mutex_unlock(&(((struct _h_mariadb *)conn->connection)->lock));
284 json_decref(*j_result);
285 return H_ERROR_MEMORY;
286 }
287 lengths = mysql_fetch_lengths(result);
288 for (col=0; col<num_fields; col++) {
289 h_data = h_get_mariadb_value(m_row[col], lengths[col], fields[col].type);
290 switch (h_data->type) {
291 case HOEL_COL_TYPE_INT:
292 json_object_set_new(j_data, fields[col].name, json_integer(((struct _h_type_int *)h_data->t_data)->value));
293 break;
294 case HOEL_COL_TYPE_DOUBLE:
295 json_object_set_new(j_data, fields[col].name, json_real(((struct _h_type_double *)h_data->t_data)->value));
296 break;
297 case HOEL_COL_TYPE_TEXT:
298 json_object_set_new(j_data, fields[col].name, json_string(((struct _h_type_text *)h_data->t_data)->value));
299 break;
300 case HOEL_COL_TYPE_DATE:
301 strftime (date_stamp, sizeof(date_stamp), "%FT%TZ", &((struct _h_type_datetime *)h_data->t_data)->value);
302 json_object_set_new(j_data, fields[col].name, json_string(date_stamp));
303 break;
304 case HOEL_COL_TYPE_BLOB:
305 json_object_set_new(j_data, fields[col].name, json_stringn(((struct _h_type_blob *)h_data->t_data)->value, ((struct _h_type_blob *)h_data->t_data)->length));
306 break;
307 case HOEL_COL_TYPE_NULL:
308 json_object_set_new(j_data, fields[col].name, json_null());
309 break;
310 }
311 h_clean_data_full(h_data);
312 }
313 json_array_append_new(*j_result, j_data);
314 j_data = NULL;
315 }
316 mysql_free_result(result);
317 pthread_mutex_unlock(&(((struct _h_mariadb *)conn->connection)->lock));
318
319 return H_OK;
320 }
321
322 /**
323 * h_get_mariadb_value
324 * convert value into a struct _h_data * depening on the m_type given
325 * returned value must be free'd with h_clean_data_full after use
326 */
h_get_mariadb_value(const char * value,const unsigned long length,const int m_type)327 struct _h_data * h_get_mariadb_value(const char * value, const unsigned long length, const int m_type) {
328 struct _h_data * data = NULL;
329 long long int i_value;
330 double d_value;
331 struct tm tm_value;
332 char * endptr;
333
334 if (value != NULL) {
335 switch (m_type) {
336 case FIELD_TYPE_DECIMAL:
337 case FIELD_TYPE_NEWDECIMAL:
338 case FIELD_TYPE_TINY:
339 case FIELD_TYPE_SHORT:
340 case FIELD_TYPE_LONG:
341 case FIELD_TYPE_LONGLONG:
342 case FIELD_TYPE_INT24:
343 case FIELD_TYPE_YEAR:
344 i_value = strtoll(value, &endptr, 10);
345 if (endptr != value) {
346 data = h_new_data_int(i_value);
347 } else {
348 data = h_new_data_null();
349 }
350 break;
351 case FIELD_TYPE_BIT:
352 i_value = strtol(value, &endptr, 2);
353 if (endptr != value) {
354 data = h_new_data_int(i_value);
355 } else {
356 data = h_new_data_null();
357 }
358 break;
359 case FIELD_TYPE_FLOAT:
360 case FIELD_TYPE_DOUBLE:
361 d_value = strtod(value, &endptr);
362 if (endptr != value) {
363 data = h_new_data_double(d_value);
364 } else {
365 data = h_new_data_null();
366 }
367 break;
368 case FIELD_TYPE_NULL:
369 data = h_new_data_null();
370 break;
371 case FIELD_TYPE_DATE:
372 if (strptime(value, "%F", &tm_value) == NULL) {
373 data = h_new_data_null();
374 } else {
375 data = h_new_data_datetime(&tm_value);
376 }
377 break;
378 case FIELD_TYPE_TIME:
379 if (strptime(value, "%T", &tm_value) == NULL) {
380 data = h_new_data_null();
381 } else {
382 data = h_new_data_datetime(&tm_value);
383 }
384 break;
385 case FIELD_TYPE_TIMESTAMP:
386 case FIELD_TYPE_DATETIME:
387 case FIELD_TYPE_NEWDATE:
388 if (strptime(value, "%F %T", &tm_value) == NULL) {
389 data = h_new_data_null();
390 } else {
391 data = h_new_data_datetime(&tm_value);
392 }
393 break;
394 case FIELD_TYPE_TINY_BLOB:
395 case FIELD_TYPE_MEDIUM_BLOB:
396 case FIELD_TYPE_LONG_BLOB:
397 case FIELD_TYPE_BLOB:
398 if (length > 0) {
399 data = h_new_data_blob(value, length);
400 } else {
401 data = h_new_data_null();
402 }
403 break;
404 case FIELD_TYPE_VAR_STRING:
405 case FIELD_TYPE_ENUM:
406 case FIELD_TYPE_SET:
407 case FIELD_TYPE_GEOMETRY:
408 default:
409 data = h_new_data_text(value, length);
410 break;
411 }
412 } else {
413 data = h_new_data_null();
414 }
415 return data;
416 }
417 #else
418
419 /**
420 * Dummy functions when Hoel is not built with MariaDB
421 */
h_connect_mariadb(const char * host,const char * user,const char * passwd,const char * db,const unsigned int port,const char * unix_socket)422 struct _h_connection * h_connect_mariadb(const char * host, const char * user, const char * passwd, const char * db, const unsigned int port, const char * unix_socket) {
423 UNUSED(host);
424 UNUSED(user);
425 UNUSED(passwd);
426 UNUSED(db);
427 UNUSED(port);
428 UNUSED(unix_socket);
429 y_log_message(Y_LOG_LEVEL_ERROR, "Hoel was not compiled with MariaDB backend");
430 return NULL;
431 }
432
h_close_mariadb(struct _h_connection * conn)433 void h_close_mariadb(struct _h_connection * conn) {
434 UNUSED(conn);
435 y_log_message(Y_LOG_LEVEL_ERROR, "Hoel was not compiled with MariaDB backend");
436 }
437
438 #endif
439