1 /**
2  *
3  * Hoel database abstraction library
4  *
5  * hoel-sqlite.c: Sqlite3 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_SQLITE
27 
28 #include <sqlite3.h>
29 #include <string.h>
30 
31 /**
32  * SQLite handle
33  */
34 struct _h_sqlite {
35   sqlite3 * db_handle;
36 };
37 
38 /**
39  * h_connect_sqlite
40  * Opens a database connection to a sqlite3 db file
41  * return pointer to a struct _h_connection * on sucess, NULL on error
42  */
h_connect_sqlite(const char * db_path)43 struct _h_connection * h_connect_sqlite(const char * db_path) {
44   struct _h_connection * conn = NULL;
45   if (db_path != NULL) {
46     conn = malloc(sizeof(struct _h_connection));
47     if (conn == NULL) {
48       y_log_message(Y_LOG_LEVEL_ERROR, "h_connect_sqlite - Error allocating resources");
49       return NULL;
50     }
51 
52     conn->type = HOEL_DB_TYPE_SQLITE;
53     conn->connection = malloc(sizeof(struct _h_sqlite));
54     if (conn->connection == NULL) {
55       y_log_message(Y_LOG_LEVEL_ERROR, "h_connect_sqlite - Error allocating resources");
56       free(conn);
57       return NULL;
58     }
59     if (sqlite3_open_v2(db_path, &((struct _h_sqlite *)conn->connection)->db_handle, SQLITE_OPEN_READWRITE|SQLITE_OPEN_FULLMUTEX, NULL) != SQLITE_OK) {
60       y_log_message(Y_LOG_LEVEL_ERROR, "Error connecting to sqlite3 database, path: %s", db_path);
61       y_log_message(Y_LOG_LEVEL_DEBUG, "Error code: %d, message: \"%s\"",
62                              sqlite3_errcode(((struct _h_sqlite *)conn->connection)->db_handle),
63                              sqlite3_errmsg(((struct _h_sqlite *)conn->connection)->db_handle));
64       sqlite3_close(((struct _h_sqlite *)conn->connection)->db_handle);
65       free(conn);
66       return NULL;
67     } else {
68       return conn;
69     }
70   }
71   return conn;
72 }
73 
74 /**
75  * close a sqlite3 connection
76  */
h_close_sqlite(struct _h_connection * conn)77 void h_close_sqlite(struct _h_connection * conn) {
78   sqlite3_close(((struct _h_sqlite *)conn->connection)->db_handle);
79 }
80 
81 /**
82  * escape a string
83  * returned value must be free'd after use
84  */
h_escape_string_sqlite(const struct _h_connection * conn,const char * unsafe)85 char * h_escape_string_sqlite(const struct _h_connection * conn, const char * unsafe) {
86   char * tmp = sqlite3_mprintf("%q", unsafe), * ret;
87   if (tmp == NULL) {
88     y_log_message(Y_LOG_LEVEL_ERROR, "Error escaping string: %s", unsafe);
89     y_log_message(Y_LOG_LEVEL_DEBUG, "Error code: %d, message: \"%s\"",
90                            sqlite3_errcode(((struct _h_sqlite *)conn->connection)->db_handle),
91                            sqlite3_errmsg(((struct _h_sqlite *)conn->connection)->db_handle));
92     return NULL;
93   }
94   ret = o_strdup(tmp);
95   sqlite3_free(tmp);
96   if (ret == NULL) {
97     y_log_message(Y_LOG_LEVEL_ERROR, "Error escaping (o_strdup)");
98   }
99   return ret;
100 }
101 
102 /**
103  * Escapes a string and returns it ready to be inserted in the query
104  * returned value must be free'd after use
105  */
h_escape_string_with_quotes_sqlite(const struct _h_connection * conn,const char * unsafe)106 char * h_escape_string_with_quotes_sqlite(const struct _h_connection * conn, const char * unsafe) {
107   char * tmp = h_escape_string_sqlite(conn, unsafe), * ret;
108   if (tmp == NULL) {
109     return NULL;
110   }
111   ret = msprintf("'%s'", tmp);
112   o_free(tmp);
113   if (ret == NULL) {
114     y_log_message(Y_LOG_LEVEL_ERROR, "Error escaping with quotes (o_strdup)");
115   }
116   return ret;
117 }
118 
119 /**
120  * Return the id of the last inserted value
121  */
h_last_insert_id_sqlite(const struct _h_connection * conn)122 long long int h_last_insert_id_sqlite(const struct _h_connection * conn) {
123   return sqlite3_last_insert_rowid(((struct _h_sqlite *)conn->connection)->db_handle);
124 }
125 
126 /**
127  * h_select_query_sqlite
128  * Execute a select query on a sqlite connection, set the result structure with the returned values
129  * Should not be executed by the user because all parameters are supposed to be correct
130  * if result is NULL, the query is executed but no value will be returned
131  * Useful for SELECT statements
132  * return H_OK on success
133  */
h_select_query_sqlite(const struct _h_connection * conn,const char * query,struct _h_result * result)134 int h_select_query_sqlite(const struct _h_connection * conn, const char * query, struct _h_result * result) {
135   sqlite3_stmt *stmt;
136   int sql_result, row_result, nb_columns, col, row, res;
137   struct _h_data * data = NULL, * cur_row = NULL;
138 
139   sql_result = sqlite3_prepare_v2(((struct _h_sqlite *)conn->connection)->db_handle, query, strlen(query)+1, &stmt, NULL);
140 
141   if (sql_result == SQLITE_OK) {
142     nb_columns = sqlite3_column_count(stmt);
143     row = 0;
144     if (result != NULL) {
145       row_result = sqlite3_step(stmt);
146       /* Filling result object with results in array format */
147       result->nb_rows = 0;
148       result->nb_columns = nb_columns;
149       result->data = NULL;
150       while (row_result == SQLITE_ROW) {
151         cur_row = NULL;
152         for (col = 0; col < nb_columns; col++) {
153           data = NULL;
154           switch (sqlite3_column_type(stmt, col)) {
155             case SQLITE_INTEGER:
156               data = h_new_data_int(sqlite3_column_int64(stmt, col));
157               break;
158             case SQLITE_FLOAT:
159               data = h_new_data_double(sqlite3_column_double(stmt, col));
160               break;
161             case SQLITE_BLOB:
162               data = h_new_data_blob(sqlite3_column_blob(stmt, col), sqlite3_column_bytes(stmt, col));
163               break;
164             case SQLITE3_TEXT:
165               data = h_new_data_text((char*)sqlite3_column_text(stmt, col), sqlite3_column_bytes(stmt, col));
166               break;
167             case SQLITE_NULL:
168               data = h_new_data_null();
169             default:
170               break;
171           }
172           if (data == NULL) {
173             sqlite3_finalize(stmt);
174             h_clean_data_full(data);
175             return H_ERROR_MEMORY;
176           }
177           res = h_row_add_data(&cur_row, data, col);
178           h_clean_data_full(data);
179           if (res != H_OK) {
180             sqlite3_finalize(stmt);
181             return res;
182           }
183         }
184         res = h_result_add_row(result, cur_row, row);
185         cur_row = NULL;
186         if (res != H_OK) {
187           sqlite3_finalize(stmt);
188           return res;
189         }
190         row_result = sqlite3_step(stmt);
191         row++;
192       }
193     }
194     sqlite3_finalize(stmt);
195     return H_OK;
196   } else {
197     y_log_message(Y_LOG_LEVEL_ERROR, "Error executing sql query");
198     y_log_message(Y_LOG_LEVEL_DEBUG, "Error code: %d, message: \"%s\"",
199                                    sqlite3_errcode(((struct _h_sqlite *)conn->connection)->db_handle),
200                                    sqlite3_errmsg(((struct _h_sqlite *)conn->connection)->db_handle));
201     y_log_message(Y_LOG_LEVEL_DEBUG, "Query: \"%s\"", query);
202     sqlite3_finalize(stmt);
203     return H_ERROR_QUERY;
204   }
205 }
206 
207 /**
208  * h_exec_query_sqlite
209  * Execute a query on a sqlite connection
210  * Should not be executed by the user because all parameters are supposed to be correct
211  * No result is returned, useful for single INSERT, UPDATE or DELETE statements
212  * return H_OK on success
213  */
h_exec_query_sqlite(const struct _h_connection * conn,const char * query)214 int h_exec_query_sqlite(const struct _h_connection * conn, const char * query) {
215   if (sqlite3_exec(((struct _h_sqlite *)conn->connection)->db_handle, query, NULL, NULL, NULL) == SQLITE_OK) {
216     return H_OK;
217   } else {
218     y_log_message(Y_LOG_LEVEL_ERROR, "Error executing sql query");
219     y_log_message(Y_LOG_LEVEL_DEBUG, "Error code: %d, message: \"%s\"",
220                                    sqlite3_errcode(((struct _h_sqlite *)conn->connection)->db_handle),
221                                    sqlite3_errmsg(((struct _h_sqlite *)conn->connection)->db_handle));
222     y_log_message(Y_LOG_LEVEL_DEBUG, "Query: \"%s\"", query);
223     return H_ERROR_QUERY;
224   }
225 
226 }
227 
228 /**
229  * h_execute_query_json_sqlite
230  * Execute a query on a sqlite connection, set the returned values in the json result
231  * Should not be executed by the user because all parameters are supposed to be correct
232  * return H_OK on success
233  */
h_execute_query_json_sqlite(const struct _h_connection * conn,const char * query,json_t ** j_result)234 int h_execute_query_json_sqlite(const struct _h_connection * conn, const char * query, json_t ** j_result) {
235   sqlite3_stmt *stmt;
236   int sql_result, row_result, nb_columns, col;
237   json_t * j_data;
238 
239   if (j_result == NULL) {
240     return H_ERROR_PARAMS;
241   }
242 
243   sql_result = sqlite3_prepare_v2(((struct _h_sqlite *)conn->connection)->db_handle, query, strlen(query)+1, &stmt, NULL);
244 
245   if (sql_result == SQLITE_OK) {
246     nb_columns = sqlite3_column_count(stmt);
247     /* Filling j_result with results in json format */
248     *j_result = json_array();
249     if (*j_result == NULL) {
250       y_log_message(Y_LOG_LEVEL_ERROR, "Hoel - Error allocating memory for *j_result");
251       sqlite3_finalize(stmt);
252       return H_ERROR_MEMORY;
253     }
254     row_result = sqlite3_step(stmt);
255     while (row_result == SQLITE_ROW) {
256       j_data = json_object();
257       if (j_data == NULL) {
258         y_log_message(Y_LOG_LEVEL_ERROR, "Hoel - Error allocating memory for j_data");
259         json_decref(*j_result);
260         return H_ERROR_MEMORY;
261       }
262       for (col = 0; col < nb_columns; col++) {
263         switch (sqlite3_column_type(stmt, col)) {
264           case SQLITE_INTEGER:
265             json_object_set_new(j_data, sqlite3_column_name(stmt, col), json_integer(sqlite3_column_int64(stmt, col)));
266             break;
267           case SQLITE_FLOAT:
268             json_object_set_new(j_data, sqlite3_column_name(stmt, col), json_real(sqlite3_column_double(stmt, col)));
269             break;
270           case SQLITE_BLOB:
271             json_object_set_new(j_data, sqlite3_column_name(stmt, col), json_stringn(sqlite3_column_blob(stmt, col), sqlite3_column_bytes(stmt, col)));
272             break;
273           case SQLITE3_TEXT:
274             json_object_set_new(j_data, sqlite3_column_name(stmt, col), json_string((char*)sqlite3_column_text(stmt, col)));
275             break;
276           case SQLITE_NULL:
277             json_object_set_new(j_data, sqlite3_column_name(stmt, col), json_null());
278           default:
279             break;
280         }
281       }
282       json_array_append_new(*j_result, j_data);
283       j_data = NULL;
284       row_result = sqlite3_step(stmt);
285     }
286     sqlite3_finalize(stmt);
287     return H_OK;
288   } else {
289     y_log_message(Y_LOG_LEVEL_ERROR, "Error executing sql query");
290     y_log_message(Y_LOG_LEVEL_DEBUG, "Error code: %d, message: \"%s\"",
291                                    sqlite3_errcode(((struct _h_sqlite *)conn->connection)->db_handle),
292                                    sqlite3_errmsg(((struct _h_sqlite *)conn->connection)->db_handle));
293     y_log_message(Y_LOG_LEVEL_DEBUG, "Query: \"%s\"", query);
294     sqlite3_finalize(stmt);
295     return H_ERROR_QUERY;
296   }
297 }
298 #else
299 
300 /**
301  * Dummy functions when Hoel is not built with SQLite
302  */
h_connect_sqlite(const char * db_path)303 struct _h_connection * h_connect_sqlite(const char * db_path) {
304   UNUSED(db_path);
305   y_log_message(Y_LOG_LEVEL_ERROR, "Hoel was not compiled with SQLite backend");
306   return NULL;
307 }
308 
h_close_sqlite(struct _h_connection * conn)309 void h_close_sqlite(struct _h_connection * conn) {
310   UNUSED(conn);
311   y_log_message(Y_LOG_LEVEL_ERROR, "Hoel was not compiled with SQLite backend");
312 }
313 
314 #endif
315