1 /**
2 *
3 * Hoel database abstraction library
4 *
5 * hoel-pgsql.c: Postgre SQL 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_PGSQL
27 /* PostgreSQL library includes */
28 #include <libpq-fe.h>
29 #include <string.h>
30
31 struct _h_pg_type {
32 Oid pg_type;
33 unsigned short h_type;
34 };
35
36 /**
37 * Postgre SQL handle
38 */
39 struct _h_pgsql {
40 char * conninfo;
41 PGconn * db_handle;
42 unsigned int nb_type;
43 struct _h_pg_type * list_type;
44 pthread_mutex_t lock;
45 };
46
47 /**
48 * h_connect_pgsql
49 * Opens a database connection to a PostgreSQL server
50 * return pointer to a struct _h_connection * on sucess, NULL on error
51 */
h_connect_pgsql(const char * conninfo)52 struct _h_connection * h_connect_pgsql(const char * conninfo) {
53 struct _h_connection * conn = NULL;
54 int ntuples, i;
55 PGresult *res;
56 pthread_mutexattr_t mutexattr;
57
58 if (conninfo != NULL) {
59 conn = malloc(sizeof(struct _h_connection));
60 if (conn == NULL) {
61 y_log_message(Y_LOG_LEVEL_ERROR, "Hoel - Error allocating memory for conn");
62 return NULL;
63 }
64
65 conn->type = HOEL_DB_TYPE_PGSQL;
66 conn->connection = malloc(sizeof(struct _h_pgsql));
67 if (conn->connection == NULL) {
68 y_log_message(Y_LOG_LEVEL_ERROR, "Hoel - Error allocating memory for conn->connection");
69 free(conn);
70 return NULL;
71 }
72 ((struct _h_pgsql *)conn->connection)->db_handle = PQconnectdb(conninfo);
73 ((struct _h_pgsql *)conn->connection)->nb_type = 0;
74 ((struct _h_pgsql *)conn->connection)->list_type = NULL;
75
76 if (PQstatus(((struct _h_pgsql *)conn->connection)->db_handle) != CONNECTION_OK) {
77 y_log_message(Y_LOG_LEVEL_ERROR, "Hoel - Error connecting to PostgreSQL Database");
78 y_log_message(Y_LOG_LEVEL_DEBUG, "Hoel - Error message: \"%s\"", PQerrorMessage(((struct _h_pgsql *)conn->connection)->db_handle));
79 PQfinish(((struct _h_pgsql *)conn->connection)->db_handle);
80 free(conn->connection);
81 free(conn);
82 conn = NULL;
83 } else {
84 res = PQexec(((struct _h_pgsql *)conn->connection)->db_handle, "select oid, typname from pg_type");
85 if (PQresultStatus(res) != PGRES_TUPLES_OK && PQresultStatus(res) != PGRES_COMMAND_OK && PQnfields(res) == 2) {
86 y_log_message(Y_LOG_LEVEL_ERROR, "Error executing sql query");
87 y_log_message(Y_LOG_LEVEL_DEBUG, "Error message: \"%s\"", PQerrorMessage(((struct _h_pgsql *)conn->connection)->db_handle));
88 y_log_message(Y_LOG_LEVEL_DEBUG, "Query: \"select oid, typname from pg_type\"");
89 PQfinish(((struct _h_pgsql *)conn->connection)->db_handle);
90 free(conn->connection);
91 free(conn);
92 conn = NULL;
93 } else {
94 ntuples = PQntuples(res);
95 ((struct _h_pgsql *)conn->connection)->list_type = o_malloc((ntuples+1)*sizeof(struct _h_pg_type));
96 if (((struct _h_pgsql *)conn->connection)->list_type != NULL) {
97 ((struct _h_pgsql *)conn->connection)->nb_type = ntuples;
98 for(i = 0; i < ntuples; i++) {
99 char * cur_type_name = PQgetvalue(res, i, 1);
100 ((struct _h_pgsql *)conn->connection)->list_type[i].pg_type = strtol(PQgetvalue(res, i, 0), NULL, 10);
101 if (o_strcmp(cur_type_name, "bool") == 0) {
102 ((struct _h_pgsql *)conn->connection)->list_type[i].h_type = HOEL_COL_TYPE_BOOL;
103 } else if (o_strncmp(cur_type_name, "int", 3) == 0 || (o_strncmp(cur_type_name+1, "id", 2) == 0 && o_strlen(cur_type_name) == 3)) {
104 ((struct _h_pgsql *)conn->connection)->list_type[i].h_type = HOEL_COL_TYPE_INT;
105 } else if (o_strcmp(cur_type_name, "numeric") == 0 || o_strncmp(cur_type_name, "float", 5) == 0) {
106 ((struct _h_pgsql *)conn->connection)->list_type[i].h_type = HOEL_COL_TYPE_DOUBLE;
107 } else if (o_strcmp(cur_type_name, "date") == 0 || o_strncmp(cur_type_name, "time", 4) == 0) {
108 ((struct _h_pgsql *)conn->connection)->list_type[i].h_type = HOEL_COL_TYPE_DATE;
109 } else if (o_strcmp(cur_type_name, "bytea") == 0) {
110 ((struct _h_pgsql *)conn->connection)->list_type[i].h_type = HOEL_COL_TYPE_BLOB;
111 } else if (o_strcmp(cur_type_name, "bool") == 0) {
112 ((struct _h_pgsql *)conn->connection)->list_type[i].h_type = HOEL_COL_TYPE_BOOL;
113 } else {
114 ((struct _h_pgsql *)conn->connection)->list_type[i].h_type = HOEL_COL_TYPE_TEXT;
115 }
116 }
117 /* Initialize MUTEX for connection */
118 pthread_mutexattr_init ( &mutexattr );
119 pthread_mutexattr_settype( &mutexattr, PTHREAD_MUTEX_RECURSIVE );
120 if (pthread_mutex_init(&(((struct _h_pgsql *)conn->connection)->lock), &mutexattr) != 0) {
121 y_log_message(Y_LOG_LEVEL_ERROR, "Impossible to initialize Mutex Lock for MariaDB connection");
122 }
123 pthread_mutexattr_destroy( &mutexattr );
124 } else {
125 y_log_message(Y_LOG_LEVEL_ERROR, "Error allocating resources for list_type");
126 PQfinish(((struct _h_pgsql *)conn->connection)->db_handle);
127 free(conn->connection);
128 free(conn);
129 conn = NULL;
130 }
131 PQclear(res);
132 }
133 }
134 }
135 return conn;
136 }
137
138 /**
139 * close a pgsql connection
140 */
h_close_pgsql(struct _h_connection * conn)141 void h_close_pgsql(struct _h_connection * conn) {
142 PQfinish(((struct _h_pgsql *)conn->connection)->db_handle);
143 o_free(((struct _h_pgsql *)conn->connection)->list_type);
144 ((struct _h_pgsql *)conn->connection)->list_type = NULL;
145 ((struct _h_pgsql *)conn->connection)->nb_type = 0;
146 pthread_mutex_destroy(&((struct _h_pgsql *)conn->connection)->lock);
147 }
148
149 /**
150 * escape a string
151 * returned value must be free'd after use
152 */
h_escape_string_pgsql(const struct _h_connection * conn,const char * unsafe)153 char * h_escape_string_pgsql(const struct _h_connection * conn, const char * unsafe) {
154 char * escaped = PQescapeLiteral(((struct _h_pgsql *)conn->connection)->db_handle, unsafe, strlen(unsafe)), * to_return = NULL;
155 if (escaped != NULL) {
156 if (escaped[0] == '\'' && escaped[o_strlen(escaped)-1] == '\'') {
157 to_return = o_strndup((escaped+1), o_strlen((escaped+1))-1);
158 }
159 PQfreemem(escaped);
160 }
161 return to_return;
162 }
163
164 /**
165 * Escapes a string and returns it ready to be inserted in the query
166 * returned value must be free'd after use
167 */
h_escape_string_with_quotes_pgsql(const struct _h_connection * conn,const char * unsafe)168 char * h_escape_string_with_quotes_pgsql(const struct _h_connection * conn, const char * unsafe) {
169 char * escaped = PQescapeLiteral(((struct _h_pgsql *)conn->connection)->db_handle, unsafe, strlen(unsafe)), * to_return = NULL;
170 if (escaped != NULL) {
171 to_return = o_strdup(escaped);
172 PQfreemem(escaped);
173 }
174 return to_return;
175 }
176
177 /**
178 * Return the hoel type of a column given its Oid
179 * If type is not found, return HOEL_COL_TYPE_TEXT
180 */
h_get_type_from_oid(const struct _h_connection * conn,Oid pg_type)181 static unsigned short h_get_type_from_oid(const struct _h_connection * conn, Oid pg_type) {
182 unsigned int i;
183
184 for (i = 0; i < ((struct _h_pgsql *)conn->connection)->nb_type; i++) {
185 if (((struct _h_pgsql *)conn->connection)->list_type[i].pg_type == pg_type) {
186 return ((struct _h_pgsql *)conn->connection)->list_type[i].h_type;
187 }
188 }
189 return HOEL_COL_TYPE_TEXT;
190 }
191
192 /**
193 * h_execute_query_pgsql
194 * Execute a query on a pgsql connection, set the result structure with the returned values
195 * Should not be executed by the user because all parameters are supposed to be correct
196 * if result is NULL, the query is executed but no value will be returned
197 * return H_OK on success
198 */
h_execute_query_pgsql(const struct _h_connection * conn,const char * query,struct _h_result * result)199 int h_execute_query_pgsql(const struct _h_connection * conn, const char * query, struct _h_result * result) {
200 PGresult * res;
201 int nfields, ntuples, i, j, h_res, ret = H_OK;
202 struct _h_data * data, * cur_row = NULL;
203
204 if (pthread_mutex_lock(&(((struct _h_pgsql *)conn->connection)->lock))) {
205 ret = H_ERROR_QUERY;
206 } else {
207 res = PQexec(((struct _h_pgsql *)conn->connection)->db_handle, query);
208 if (PQresultStatus(res) != PGRES_TUPLES_OK && PQresultStatus(res) != PGRES_COMMAND_OK) {
209 y_log_message(Y_LOG_LEVEL_ERROR, "Error executing sql query");
210 y_log_message(Y_LOG_LEVEL_DEBUG, "Error message: \"%s\"", PQerrorMessage(((struct _h_pgsql *)conn->connection)->db_handle));
211 y_log_message(Y_LOG_LEVEL_DEBUG, "Query: \"%s\"", query);
212 ret = H_ERROR_QUERY;
213 } else {
214 nfields = PQnfields(res);
215 ntuples = PQntuples(res);
216
217 if (result != NULL) {
218 result->nb_rows = 0;
219 result->nb_columns = nfields;
220 result->data = NULL;
221 for(i = 0; ret == H_OK && i < ntuples; i++) {
222 cur_row = NULL;
223 for(j = 0; ret == H_OK && j < nfields; j++) {
224 char * val = PQgetvalue(res, i, j);
225 if (val == NULL) {
226 data = h_new_data_null();
227 } else {
228 switch (h_get_type_from_oid(conn, PQftype(res, j))) {
229 case HOEL_COL_TYPE_INT:
230 data = h_new_data_int(strtol(val, NULL, 10));
231 break;
232 case HOEL_COL_TYPE_DOUBLE:
233 data = h_new_data_double(strtod(val, NULL));
234 break;
235 case HOEL_COL_TYPE_BLOB:
236 data = h_new_data_blob(val, PQgetlength(res, i, j));
237 break;
238 case HOEL_COL_TYPE_BOOL:
239 if (o_strcasecmp(val, "t") == 0) {
240 data = h_new_data_int(1);
241 } else if (o_strcasecmp(val, "f") == 0) {
242 data = h_new_data_int(0);
243 } else {
244 data = h_new_data_null();
245 }
246 break;
247 case HOEL_COL_TYPE_DATE:
248 case HOEL_COL_TYPE_TEXT:
249 default:
250 data = h_new_data_text(val, PQgetlength(res, i, j));
251 break;
252 }
253 }
254 h_res = h_row_add_data(&cur_row, data, j);
255 h_clean_data_full(data);
256 if (h_res != H_OK) {
257 PQclear(res);
258 ret = h_res;
259 }
260 }
261 h_res = h_result_add_row(result, cur_row, i);
262 if (h_res != H_OK) {
263 PQclear(res);
264 ret = h_res;
265 }
266 }
267 }
268 PQclear(res);
269 }
270 pthread_mutex_unlock(&(((struct _h_pgsql *)conn->connection)->lock));
271 }
272 return ret;
273 }
274
275 /**
276 * h_execute_query_json_pgsql
277 * Execute a query on a pgsql connection, set the returned values in the json results
278 * Should not be executed by the user because all parameters are supposed to be correct
279 * return H_OK on success
280 */
h_execute_query_json_pgsql(const struct _h_connection * conn,const char * query,json_t ** j_result)281 int h_execute_query_json_pgsql(const struct _h_connection * conn, const char * query, json_t ** j_result) {
282 PGresult *res;
283 int nfields, ntuples, i, j, ret = H_OK;
284 json_t * j_data;
285
286 if (pthread_mutex_lock(&(((struct _h_pgsql *)conn->connection)->lock))) {
287 ret = H_ERROR_QUERY;
288 } else {
289 if (j_result == NULL) {
290 ret = H_ERROR_PARAMS;
291 } else {
292 *j_result = json_array();
293 if (*j_result == NULL) {
294 y_log_message(Y_LOG_LEVEL_ERROR, "Hoel - Error allocating memory for *j_result");
295 ret = H_ERROR_MEMORY;
296 } else {
297 res = PQexec(((struct _h_pgsql *)conn->connection)->db_handle, query);
298 if (PQresultStatus(res) != PGRES_TUPLES_OK && PQresultStatus(res) != PGRES_COMMAND_OK) {
299 y_log_message(Y_LOG_LEVEL_ERROR, "Error executing sql query");
300 y_log_message(Y_LOG_LEVEL_DEBUG, "Error message: \"%s\"", PQerrorMessage(((struct _h_pgsql *)conn->connection)->db_handle));
301 y_log_message(Y_LOG_LEVEL_DEBUG, "Query: \"%s\"", query);
302 ret = H_ERROR_QUERY;
303 } else {
304 nfields = PQnfields(res);
305 ntuples = PQntuples(res);
306
307 for(i = 0; ret == H_OK && i < ntuples; i++) {
308 j_data = json_object();
309 if (j_data == NULL) {
310 y_log_message(Y_LOG_LEVEL_ERROR, "Hoel - Error allocating memory for j_data");
311 PQclear(res);
312 ret = H_ERROR_MEMORY;
313 } else {
314 for(j = 0; ret == H_OK && j < nfields; j++) {
315 char * val = PQgetvalue(res, i, j);
316 if (val == NULL || PQgetisnull(res, i, j)) {
317 json_object_set_new(j_data, PQfname(res, j), json_null());
318 } else {
319 switch (h_get_type_from_oid(conn, PQftype(res, j))) {
320 case HOEL_COL_TYPE_INT:
321 json_object_set_new(j_data, PQfname(res, j), json_integer(strtoll(PQgetvalue(res, i, j), NULL, 10)));
322 break;
323 case HOEL_COL_TYPE_DOUBLE:
324 json_object_set_new(j_data, PQfname(res, j), json_real(strtod(PQgetvalue(res, i, j), NULL)));
325 break;
326 case HOEL_COL_TYPE_BLOB:
327 json_object_set_new(j_data, PQfname(res, j), json_stringn(PQgetvalue(res, i, j), PQgetlength(res, i, j)));
328 break;
329 case HOEL_COL_TYPE_BOOL:
330 if (o_strcasecmp(PQgetvalue(res, i, j), "t") == 0) {
331 json_object_set_new(j_data, PQfname(res, j), json_integer(1));
332 } else if (o_strcasecmp(PQgetvalue(res, i, j), "f") == 0) {
333 json_object_set_new(j_data, PQfname(res, j), json_integer(0));
334 } else {
335 json_object_set_new(j_data, PQfname(res, j), json_null());
336 }
337 break;
338 case HOEL_COL_TYPE_DATE:
339 case HOEL_COL_TYPE_TEXT:
340 default:
341 json_object_set_new(j_data, PQfname(res, j), json_string(PQgetvalue(res, i, j)));
342 break;
343 }
344 }
345 }
346 }
347 json_array_append_new(*j_result, j_data);
348 j_data = NULL;
349 }
350 }
351 PQclear(res);
352 }
353 }
354 pthread_mutex_unlock(&(((struct _h_pgsql *)conn->connection)->lock));
355 }
356
357 return ret;
358 }
359
360 /**
361 * Return the id of the last inserted value
362 * Assuming you use sequences for automatically generated ids
363 */
h_last_insert_id_pgsql(const struct _h_connection * conn)364 long long int h_last_insert_id_pgsql(const struct _h_connection * conn) {
365 PGresult *res;
366 long long int int_res = 0;
367 char * str_res, * endptr = NULL;
368
369 res = PQexec(((struct _h_pgsql *)conn->connection)->db_handle, "SELECT lastval()");
370 if (PQresultStatus(res) != PGRES_TUPLES_OK && PQresultStatus(res) != PGRES_COMMAND_OK) {
371 y_log_message(Y_LOG_LEVEL_ERROR, "Error executing h_last_insert_id");
372 y_log_message(Y_LOG_LEVEL_DEBUG, "Error message: \"%s\"", PQerrorMessage(((struct _h_pgsql *)conn->connection)->db_handle));
373 return H_ERROR_QUERY;
374 }
375
376 if (PQnfields(res) && PQntuples(res)) {
377 str_res = PQgetvalue(res, 0, 0);
378 if (str_res != NULL) {
379 int_res = strtol(str_res, &endptr, 10);
380 if (*endptr != '\0' || endptr == str_res) {
381 y_log_message(Y_LOG_LEVEL_ERROR, "Error h_last_insert_id, returned value can't be converted to numeric");
382 int_res = 0;
383 }
384 } else {
385 y_log_message(Y_LOG_LEVEL_ERROR, "Error h_last_insert_id, returned value is NULL");
386 }
387 } else {
388 y_log_message(Y_LOG_LEVEL_ERROR, "Error h_last_insert_id, returned value has no data available");
389 }
390 PQclear(res);
391 return int_res;
392 }
393 #else
394
395 /**
396 * Dummy functions when Hoel is not built with PostgreSQL
397 */
h_connect_pgsql(const char * conninfo)398 struct _h_connection * h_connect_pgsql(const char * conninfo) {
399 UNUSED(conninfo);
400 y_log_message(Y_LOG_LEVEL_ERROR, "Hoel was not compiled with PostgreSQL backend");
401 return NULL;
402 }
403
h_close_pgsql(struct _h_connection * conn)404 void h_close_pgsql(struct _h_connection * conn) {
405 UNUSED(conn);
406 y_log_message(Y_LOG_LEVEL_ERROR, "Hoel was not compiled with PostgreSQL backend");
407 }
408
409 #endif
410