3 * Copyright (c) 2009-2015 Samsung Electronics Co., Ltd.
5 * Licensed under the Apache License, Version 2.0 (the License);
6 * you may not use this file except in compliance with the License.
7 * You may obtain a copy of the License at
9 * http://www.apache.org/license/
11 * Unless required by applicable law or agreed to in writing, software
12 * distributed under the License is distributed on an AS IS BASIS,
13 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
14 * See the License for the specific language governing permissions and
15 * limitations under the License.
24 #include <app_common.h>
29 #include "group_info.h"
31 #define APP_DB_FILE ".app.db"
36 HAPI sqlite3 *db_open(void)
40 char db_file[FILE_LEN] = {0, };
43 path = app_get_data_path();
46 snprintf(db_file, sizeof(db_file), "%s/%s", path, APP_DB_FILE);
49 ret = sqlite3_open(db_file, &db);
50 if (SQLITE_OK != ret) {
51 _E("%s", sqlite3_errmsg(db));
60 HAPI void db_close(sqlite3 *db)
68 HAPI sqlite3_stmt *db_prepare(sqlite3 *db, const char *query)
70 sqlite3_stmt *stmt = NULL;
73 retv_if(!query, NULL);
75 ret = sqlite3_prepare_v2(db, query, strlen(query), &stmt, NULL);
76 if (SQLITE_OK != ret) {
77 _E("%s, %s", query, sqlite3_errmsg(db));
86 HAPI int db_next(sqlite3 *db, sqlite3_stmt *stmt)
90 retv_if(!stmt, APPL_ERROR_FAIL);
92 ret = sqlite3_step(stmt);
94 case SQLITE_ROW: /* SQLITE_ROW : 100 */
95 return APPL_ERROR_NONE;
96 case SQLITE_DONE: /* SQLITE_ROW : 101 */
97 return APPL_ERROR_NO_DATA;
99 _E("%s", sqlite3_errmsg(db));
100 return APPL_ERROR_FAIL;
103 return APPL_ERROR_NONE;
108 HAPI int db_reset(sqlite3 *db, sqlite3_stmt *stmt)
112 retv_if(!stmt, APPL_ERROR_INVALID_PARAMETER);
114 ret = sqlite3_reset(stmt);
115 if (SQLITE_OK != ret) {
116 _E("%s", sqlite3_errmsg(db));
117 return APPL_ERROR_FAIL;
120 sqlite3_clear_bindings(stmt);
122 return APPL_ERROR_NONE;
127 HAPI int db_bind_bool(sqlite3 *db, sqlite3_stmt *stmt, int idx, bool value)
131 retv_if(!stmt, APPL_ERROR_FAIL);
133 ret = sqlite3_bind_int(stmt, idx, (int) value);
134 if (SQLITE_OK != ret) {
135 _E("%s", sqlite3_errmsg(db));
136 return APPL_ERROR_FAIL;
139 return APPL_ERROR_NONE;
144 HAPI int db_bind_int(sqlite3 *db, sqlite3_stmt *stmt, int idx, int value)
148 retv_if(!stmt, APPL_ERROR_FAIL);
150 ret = sqlite3_bind_int(stmt, idx, value);
151 if (SQLITE_OK != ret) {
152 _E("%s", sqlite3_errmsg(db));
153 return APPL_ERROR_FAIL;
156 return APPL_ERROR_NONE;
161 HAPI int db_bind_double(sqlite3 *db, sqlite3_stmt *stmt, int idx, double value)
165 retv_if(!stmt, APPL_ERROR_FAIL);
167 ret = sqlite3_bind_double(stmt, idx, value);
168 if (SQLITE_OK != ret) {
169 _E("%s", sqlite3_errmsg(db));
170 return APPL_ERROR_FAIL;
173 return APPL_ERROR_NONE;
178 HAPI int db_bind_str(sqlite3 *db, sqlite3_stmt *stmt, int idx, const char *str)
182 retv_if(!stmt, APPL_ERROR_FAIL);
185 ret = sqlite3_bind_text(stmt, idx, str, -1, SQLITE_TRANSIENT);
187 ret = sqlite3_bind_null(stmt, idx);
190 if (SQLITE_OK != ret) {
191 _E("%s", sqlite3_errmsg(db));
192 return APPL_ERROR_FAIL;
195 return APPL_ERROR_NONE;
200 HAPI bool db_get_bool(sqlite3_stmt *stmt, int index)
202 retv_if(!stmt, false);
203 return (bool) (!!sqlite3_column_int(stmt, index));
208 HAPI int db_get_int(sqlite3_stmt *stmt, int index)
211 return sqlite3_column_int(stmt, index);
216 HAPI int db_get_double(sqlite3_stmt *stmt, int index)
219 return sqlite3_column_double(stmt, index);
224 HAPI const char *db_get_str(sqlite3_stmt *stmt, int index)
226 retv_if(!stmt, NULL);
227 return (const char *) sqlite3_column_text(stmt, index);
232 HAPI int db_finalize(sqlite3 *db, sqlite3_stmt *stmt)
236 retv_if(!stmt, APPL_ERROR_INVALID_PARAMETER);
238 ret = sqlite3_finalize(stmt);
239 if (SQLITE_OK != ret) {
240 _E("%s", sqlite3_errmsg(db));
241 return APPL_ERROR_FAIL;
244 return APPL_ERROR_NONE;
249 HAPI int db_exec(sqlite3 *db, const char *query)
251 sqlite3_stmt *stmt = NULL;
253 retv_if(!query, APPL_ERROR_INVALID_PARAMETER);
255 stmt = db_prepare(db, query);
256 retv_if(!stmt, APPL_ERROR_FAIL);
258 goto_if(APPL_ERROR_FAIL == db_next(db, stmt), ERROR);
259 goto_if(APPL_ERROR_FAIL == db_finalize(db, stmt), ERROR);
261 return APPL_ERROR_NONE;
264 if (stmt) db_finalize(db, stmt);
265 return APPL_ERROR_FAIL;
270 HAPI int db_create_table(sqlite3 *db)
272 const char *TABLES[] = {
273 "CREATE TABLE IF NOT EXIST db_checksum (version INT);",
274 "CREATE TABLE IF NOT EXIST group ("
275 "time INTEGER PRIMARY KEY"
287 count = sizeof(TABLES) / sizeof(char *);
288 for (; i < count; i++) {
289 _D("Create a table[%s]", TABLES[i]);
290 break_if(db_exec(db, TABLES[i]) != APPL_ERROR_NONE);
293 return APPL_ERROR_FAIL;
298 HAPI int db_drop_table(sqlite3 *db)
300 const char *TABLES[] = {
301 "DROP TABLE IF EXIST db_checksum;",
302 "DROP TABLE IF EXIST group;",
307 count = sizeof(TABLES) / sizeof(char *);
308 for (; i < count; i++) {
309 _D("Drop a table[%s]", TABLES[i]);
310 break_if(db_exec(db, TABLES[i]) != APPL_ERROR_NONE);
313 return APPL_ERROR_FAIL;
318 HAPI int db_insert_version(sqlite3 *db, int version)
320 const char *const QUERY_SYNTAX = "INSERT INTO db_checksum (version) VALUES (?);";
321 sqlite3_stmt *st = NULL;
323 st = db_prepare(db, QUERY_SYNTAX);
324 retv_if(!st, APPL_ERROR_DB_FAILED);
326 goto_if(db_bind_int(db, st, 1, version) != APPL_ERROR_NONE, error);
327 goto_if(db_next(db, st) == -1, error);
332 /* keep this DB opened */
334 return APPL_ERROR_NONE;
338 return APPL_ERROR_DB_FAILED;
343 HAPI int db_remove_version(sqlite3 *db, int version)
345 const char *const QUERY_SYNTAX = "DELETE FROM db_checksum WHERE version = ?;";
346 sqlite3_stmt *st = NULL;
348 st = db_prepare(db, QUERY_SYNTAX);
349 retv_if(!st, APPL_ERROR_DB_FAILED);
351 goto_if(db_bind_int(db, st, 1, version) != APPL_ERROR_NONE, error);
352 goto_if(db_next(db, st) == -1, error);
357 /* keep this DB opened */
359 return APPL_ERROR_NONE;
363 return APPL_ERROR_DB_FAILED;
368 HAPI int db_update_version(sqlite3 *db, int version)
370 const char *const QUERY_SYNTAX = "UPDATE db_checksum SET version = ?;";
371 sqlite3_stmt *st = NULL;
373 st = db_prepare(db, QUERY_SYNTAX);
374 retv_if(!st, APPL_ERROR_DB_FAILED);
376 goto_if(db_bind_int(db, st, 1, version) != APPL_ERROR_NONE, error);
377 goto_if(db_next(db, st) == -1, error);
382 /* keep this DB opened */
384 return APPL_ERROR_NONE;
388 return APPL_ERROR_DB_FAILED;
393 HAPI int db_count_version(sqlite3 *db)
395 const char *const QUERY_SYNTAX = "SELECT COUNT(*) FROM db_checksum;";
396 sqlite3_stmt *st = NULL;
399 st = db_prepare(db, QUERY_SYNTAX);
400 retv_if(!st, APPL_ERROR_DB_FAILED);
402 if (db_next(db, st) == -1) {
408 count = db_get_int(st, 0);
412 /* keep this DB opened */
419 HAPI int db_count_group(sqlite3 *db, int *count)
421 const char *const QUERY_SYNTAX = "SELECT COUNT(*) FROM group;";
422 sqlite3_stmt *st = NULL;
424 st = db_prepare(db, QUERY_SYNTAX);
425 retv_if(!st, APPL_ERROR_FAIL);
427 goto_if(db_next(db, st) == -1, error);
429 *count = db_get_int(st, 0);
434 /* keep this DB opened */
436 return APPL_ERROR_NONE;
440 return APPL_ERROR_FAIL;
445 HAPI int db_insert_group(sqlite3 *db, const char *title, int city1, int city2, int city3, int city4, int city5)
447 const char *const QUERY_INSERT = "INSERT INTO group (time, title, city1, city2, city3, city4, city5) VALUES (DATETIME('now'), ?, ?, ?, ?, ?, ?);";
448 sqlite3_stmt *st = NULL;
450 st = db_prepare(db, QUERY_INSERT);
451 retv_if(!st, APPL_ERROR_FAIL);
453 goto_if(db_bind_str(db, st, 1, title) != APPL_ERROR_NONE, error);
454 goto_if(db_bind_int(db, st, 2, city1) != APPL_ERROR_NONE, error);
455 goto_if(db_bind_int(db, st, 3, city2) != APPL_ERROR_NONE, error);
456 goto_if(db_bind_int(db, st, 4, city3) != APPL_ERROR_NONE, error);
457 goto_if(db_bind_int(db, st, 5, city4) != APPL_ERROR_NONE, error);
458 goto_if(db_bind_int(db, st, 6, city5) != APPL_ERROR_NONE, error);
459 goto_if(db_next(db, st) == -1, error);
464 /* keep the sticker panel DB opened */
466 return APPL_ERROR_NONE;
470 return APPL_ERROR_FAIL;
475 HAPI int db_update_group(sqlite3 *db, int time, const char *title, int city1, int city2, int city3, int city4, int city5)
477 const char *const QUERY_UPDATE = "UPDATE group SET title = ?, city1 = ?, city2 = ?, city3 = ?, city4 = ?, city5 = ? WHERE time = ?;";
478 sqlite3_stmt *st = NULL;
480 st = db_prepare(db, QUERY_UPDATE);
481 retv_if(!st, APPL_ERROR_FAIL);
483 goto_if(db_bind_str(db, st, 1, title) != APPL_ERROR_NONE, error);
484 goto_if(db_bind_int(db, st, 2, city1) != APPL_ERROR_NONE, error);
485 goto_if(db_bind_int(db, st, 3, city2) != APPL_ERROR_NONE, error);
486 goto_if(db_bind_int(db, st, 4, city3) != APPL_ERROR_NONE, error);
487 goto_if(db_bind_int(db, st, 5, city4) != APPL_ERROR_NONE, error);
488 goto_if(db_bind_int(db, st, 6, city5) != APPL_ERROR_NONE, error);
489 goto_if(db_bind_int(db, st, 7, time) != APPL_ERROR_NONE, error);
490 goto_if(db_next(db, st) == -1, error);
495 /* keep the sticker panel DB opened */
497 return APPL_ERROR_NONE;
501 return APPL_ERROR_FAIL;
506 HAPI int db_delete_group(sqlite3 *db, int time)
508 const char *const QUERY_SYNTAX = "DELETE FROM group WHERE time = ?;";
509 sqlite3_stmt *st = NULL;
511 st = db_prepare(db, QUERY_SYNTAX);
512 retv_if(!st, APPL_ERROR_FAIL);
514 goto_if(db_bind_int(db, st, 1, time) != APPL_ERROR_NONE, error);
515 goto_if(db_next(db, st) == -1, error);
520 /* keep the sticker panel DB opened */
522 return APPL_ERROR_NONE;
526 return APPL_ERROR_FAIL;
540 typedef struct _group_s group_s;
544 HAPI int db_list_group(sqlite3 *db, Eina_List **group_list, int limit)
546 const char *const QUERY_LIST = "SELECT time, title, city1, city2, city3, city4, city5 FROM group ORDER BY time ASC";
547 sqlite3_stmt *st = NULL;
548 group_info_s *group_info = NULL;
552 st = db_prepare(db, QUERY_LIST);
553 retv_if(!st, APPL_ERROR_FAIL);
557 const char *title = NULL;
558 int city1 = 0, city2 = 0, city3 = 0, city4 = 0, city5 = 0;
559 ret = db_next(db, st);
560 if (SQLITE_DONE == ret) {
562 } else if (-1 == ret) {
563 _E("db_next() error");
567 time = db_get_int(st, 0);
568 title = db_get_str(st, 1);
569 city1 = db_get_int(st, 2);
570 city2 = db_get_int(st, 3);
571 city3 = db_get_int(st, 4);
572 city4 = db_get_int(st, 5);
573 city5 = db_get_int(st, 6);
575 group_info = group_info_create(time, title, city1, city2, city3, city4, city5);
576 continue_if(!group_info);
578 *group_list = eina_list_append(*group_list, group_info);
579 } while (SQLITE_ROW == ret);
584 /* keep the sticker panel DB opened */
586 return APPL_ERROR_NONE;
589 EINA_LIST_FREE(*group_list, group_info) {
590 group_info_destroy(group_info);
594 return APPL_ERROR_FAIL;