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