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