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 _D("db_file : %s", db_file);
50 ret = sqlite3_open(db_file, &db);
51 if (SQLITE_OK != ret) {
52 _E("%s", sqlite3_errmsg(db));
61 HAPI void db_close(sqlite3 *db)
69 HAPI sqlite3_stmt *db_prepare(sqlite3 *db, const char *query)
71 sqlite3_stmt *stmt = NULL;
74 retv_if(!query, NULL);
76 ret = sqlite3_prepare_v2(db, query, strlen(query), &stmt, NULL);
77 if (SQLITE_OK != ret) {
78 _E("%s, %s", query, sqlite3_errmsg(db));
87 HAPI int db_next(sqlite3 *db, sqlite3_stmt *stmt)
91 retv_if(!stmt, APPL_ERROR_FAIL);
93 ret = sqlite3_step(stmt);
95 case SQLITE_ROW: /* SQLITE_ROW : 100 */
97 case SQLITE_DONE: /* SQLITE_ROW : 101 */
100 _E("%s", sqlite3_errmsg(db));
109 HAPI int db_reset(sqlite3 *db, sqlite3_stmt *stmt)
113 retv_if(!stmt, APPL_ERROR_INVALID_PARAMETER);
115 ret = sqlite3_reset(stmt);
116 if (SQLITE_OK != ret) {
117 _E("%s", sqlite3_errmsg(db));
118 return APPL_ERROR_FAIL;
121 sqlite3_clear_bindings(stmt);
123 return APPL_ERROR_NONE;
128 HAPI int db_bind_bool(sqlite3 *db, sqlite3_stmt *stmt, int idx, bool value)
132 retv_if(!stmt, APPL_ERROR_FAIL);
134 ret = sqlite3_bind_int(stmt, idx, (int) value);
135 if (SQLITE_OK != ret) {
136 _E("%s", sqlite3_errmsg(db));
137 return APPL_ERROR_FAIL;
140 return APPL_ERROR_NONE;
145 HAPI int db_bind_int(sqlite3 *db, sqlite3_stmt *stmt, int idx, int value)
149 retv_if(!stmt, APPL_ERROR_FAIL);
151 ret = sqlite3_bind_int(stmt, idx, value);
152 if (SQLITE_OK != ret) {
153 _E("%s", sqlite3_errmsg(db));
154 return APPL_ERROR_FAIL;
157 return APPL_ERROR_NONE;
162 HAPI int db_bind_long(sqlite3 *db, sqlite3_stmt *stmt, int idx, long value)
166 retv_if(!stmt, APPL_ERROR_FAIL);
168 ret = sqlite3_bind_int64(stmt, idx, value);
169 if (SQLITE_OK != ret) {
170 _E("%s", sqlite3_errmsg(db));
171 return APPL_ERROR_FAIL;
174 return APPL_ERROR_NONE;
179 HAPI int db_bind_double(sqlite3 *db, sqlite3_stmt *stmt, int idx, double value)
183 retv_if(!stmt, APPL_ERROR_FAIL);
185 ret = sqlite3_bind_double(stmt, idx, value);
186 if (SQLITE_OK != ret) {
187 _E("%s", sqlite3_errmsg(db));
188 return APPL_ERROR_FAIL;
191 return APPL_ERROR_NONE;
196 HAPI int db_bind_str(sqlite3 *db, sqlite3_stmt *stmt, int idx, const char *str)
200 retv_if(!stmt, APPL_ERROR_FAIL);
203 ret = sqlite3_bind_text(stmt, idx, str, -1, SQLITE_TRANSIENT);
205 ret = sqlite3_bind_null(stmt, idx);
208 if (SQLITE_OK != ret) {
209 _E("%s", sqlite3_errmsg(db));
210 return APPL_ERROR_FAIL;
213 return APPL_ERROR_NONE;
218 HAPI bool db_get_bool(sqlite3_stmt *stmt, int index)
220 retv_if(!stmt, false);
221 return (bool) (!!sqlite3_column_int(stmt, index));
226 HAPI int db_get_int(sqlite3_stmt *stmt, int index)
229 return sqlite3_column_int(stmt, index);
234 HAPI int db_get_long(sqlite3_stmt *stmt, int index)
237 return sqlite3_column_int64(stmt, index);
242 HAPI int db_get_double(sqlite3_stmt *stmt, int index)
245 return sqlite3_column_double(stmt, index);
250 HAPI const char *db_get_str(sqlite3_stmt *stmt, int index)
252 retv_if(!stmt, NULL);
253 return (const char *) sqlite3_column_text(stmt, index);
258 HAPI int db_finalize(sqlite3 *db, sqlite3_stmt *stmt)
262 retv_if(!stmt, APPL_ERROR_INVALID_PARAMETER);
264 ret = sqlite3_finalize(stmt);
265 if (SQLITE_OK != ret) {
266 _E("%s", sqlite3_errmsg(db));
267 return APPL_ERROR_FAIL;
270 return APPL_ERROR_NONE;
275 HAPI int db_exec(sqlite3 *db, const char *query)
277 sqlite3_stmt *stmt = NULL;
279 retv_if(!query, APPL_ERROR_INVALID_PARAMETER);
281 stmt = db_prepare(db, query);
282 retv_if(!stmt, APPL_ERROR_FAIL);
284 goto_if(APPL_ERROR_FAIL == db_next(db, stmt), ERROR);
285 goto_if(APPL_ERROR_FAIL == db_finalize(db, stmt), ERROR);
287 return APPL_ERROR_NONE;
290 if (stmt) db_finalize(db, stmt);
291 return APPL_ERROR_FAIL;
296 HAPI int db_create_table(sqlite3 *db)
298 const char *TABLES[] = {
299 "CREATE TABLE IF NOT EXISTS db_checksum (version INT);",
300 "CREATE TABLE IF NOT EXISTS gr ("
301 "id INTEGER PRIMARY KEY AUTOINCREMENT"
313 count = sizeof(TABLES) / sizeof(char *);
314 for (; i < count; i++) {
315 _D("Create a table[%s]", TABLES[i]);
316 break_if(db_exec(db, TABLES[i]) != APPL_ERROR_NONE);
319 return APPL_ERROR_FAIL;
324 HAPI int db_drop_table(sqlite3 *db)
326 const char *TABLES[] = {
327 "DROP TABLE IF EXISTS db_checksum;",
328 "DROP TABLE IF EXISTS gr;",
333 count = sizeof(TABLES) / sizeof(char *);
334 for (; i < count; i++) {
335 _D("Drop a table[%s]", TABLES[i]);
336 break_if(db_exec(db, TABLES[i]) != APPL_ERROR_NONE);
339 return APPL_ERROR_FAIL;
344 HAPI int db_insert_version(sqlite3 *db, int version)
346 const char *const QUERY_SYNTAX = "INSERT INTO db_checksum (version) VALUES (?);";
347 sqlite3_stmt *st = NULL;
349 st = db_prepare(db, QUERY_SYNTAX);
350 retv_if(!st, APPL_ERROR_DB_FAILED);
352 goto_if(db_bind_int(db, st, 1, version) != APPL_ERROR_NONE, error);
353 goto_if(db_next(db, st) == -1, error);
358 /* keep this DB opened */
360 return APPL_ERROR_NONE;
364 return APPL_ERROR_DB_FAILED;
369 HAPI int db_remove_version(sqlite3 *db, int version)
371 const char *const QUERY_SYNTAX = "DELETE FROM db_checksum WHERE version = ?;";
372 sqlite3_stmt *st = NULL;
374 st = db_prepare(db, QUERY_SYNTAX);
375 retv_if(!st, APPL_ERROR_DB_FAILED);
377 goto_if(db_bind_int(db, st, 1, version) != APPL_ERROR_NONE, error);
378 goto_if(db_next(db, st) == -1, error);
383 /* keep this DB opened */
385 return APPL_ERROR_NONE;
389 return APPL_ERROR_DB_FAILED;
394 HAPI int db_update_version(sqlite3 *db, int version)
396 const char *const QUERY_SYNTAX = "UPDATE db_checksum SET version = ?;";
397 sqlite3_stmt *st = NULL;
399 st = db_prepare(db, QUERY_SYNTAX);
400 retv_if(!st, APPL_ERROR_DB_FAILED);
402 goto_if(db_bind_int(db, st, 1, version) != APPL_ERROR_NONE, error);
403 goto_if(db_next(db, st) == -1, error);
408 /* keep this DB opened */
410 return APPL_ERROR_NONE;
414 return APPL_ERROR_DB_FAILED;
419 HAPI int db_count_version(sqlite3 *db)
421 const char *const QUERY_SYNTAX = "SELECT COUNT(*) FROM db_checksum;";
422 sqlite3_stmt *st = NULL;
425 st = db_prepare(db, QUERY_SYNTAX);
426 retv_if(!st, APPL_ERROR_DB_FAILED);
428 if (db_next(db, st) == -1) {
434 count = db_get_int(st, 0);
438 /* keep this DB opened */
445 HAPI int db_count_group(sqlite3 *db, int *count)
447 const char *const QUERY_SYNTAX = "SELECT COUNT(*) FROM gr;";
448 sqlite3_stmt *st = NULL;
450 st = db_prepare(db, QUERY_SYNTAX);
451 retv_if(!st, APPL_ERROR_FAIL);
453 goto_if(db_next(db, st) == -1, error);
455 *count = db_get_int(st, 0);
460 /* keep this DB opened */
462 return APPL_ERROR_NONE;
466 return APPL_ERROR_FAIL;
471 HAPI int db_max_group(sqlite3 *db, int *max)
473 const char *const QUERY_SYNTAX = "SELECT MAX(id) FROM gr;";
474 sqlite3_stmt *st = NULL;
476 st = db_prepare(db, QUERY_SYNTAX);
477 retv_if(!st, APPL_ERROR_FAIL);
479 goto_if(db_next(db, st) == -1, error);
481 *max = db_get_int(st, 0);
486 /* keep this DB opened */
488 return APPL_ERROR_NONE;
492 return APPL_ERROR_FAIL;
497 HAPI int db_insert_group(sqlite3 *db, const char *title, int city1, int city2, int city3, int city4, int city5)
499 const char *const QUERY_INSERT = "INSERT INTO gr (id, title, city1, city2, city3, city4, city5) VALUES (?, ?, ?, ?, ?, ?, ?);";
500 sqlite3_stmt *st = NULL;
503 db_max_group(db, &max);
506 st = db_prepare(db, QUERY_INSERT);
509 goto_if(db_bind_int(db, st, 1, max) != APPL_ERROR_NONE, error);
510 goto_if(db_bind_str(db, st, 2, title) != APPL_ERROR_NONE, error);
511 goto_if(db_bind_int(db, st, 3, city1) != APPL_ERROR_NONE, error);
512 goto_if(db_bind_int(db, st, 4, city2) != APPL_ERROR_NONE, error);
513 goto_if(db_bind_int(db, st, 5, city3) != APPL_ERROR_NONE, error);
514 goto_if(db_bind_int(db, st, 6, city4) != APPL_ERROR_NONE, error);
515 goto_if(db_bind_int(db, st, 7, city5) != APPL_ERROR_NONE, error);
516 goto_if(db_next(db, st) == -1, error);
521 /* keep the sticker panel DB opened */
532 HAPI int db_update_group(sqlite3 *db, int id, const char *title, int city1, int city2, int city3, int city4, int city5)
534 const char *const QUERY_UPDATE = "UPDATE gr SET title = ?, city1 = ?, city2 = ?, city3 = ?, city4 = ?, city5 = ? WHERE id = ?;";
535 sqlite3_stmt *st = NULL;
537 st = db_prepare(db, QUERY_UPDATE);
538 retv_if(!st, APPL_ERROR_FAIL);
540 goto_if(db_bind_str(db, st, 1, title) != APPL_ERROR_NONE, error);
541 goto_if(db_bind_int(db, st, 2, city1) != APPL_ERROR_NONE, error);
542 goto_if(db_bind_int(db, st, 3, city2) != APPL_ERROR_NONE, error);
543 goto_if(db_bind_int(db, st, 4, city3) != APPL_ERROR_NONE, error);
544 goto_if(db_bind_int(db, st, 5, city4) != APPL_ERROR_NONE, error);
545 goto_if(db_bind_int(db, st, 6, city5) != APPL_ERROR_NONE, error);
546 goto_if(db_bind_int(db, st, 7, id) != APPL_ERROR_NONE, error);
547 goto_if(db_next(db, st) == -1, error);
552 /* keep the sticker panel DB opened */
554 return APPL_ERROR_NONE;
558 return APPL_ERROR_FAIL;
563 HAPI int db_delete_group(sqlite3 *db, int id)
565 const char *const QUERY_SYNTAX = "DELETE FROM gr WHERE id = ?;";
566 sqlite3_stmt *st = NULL;
568 st = db_prepare(db, QUERY_SYNTAX);
569 retv_if(!st, APPL_ERROR_FAIL);
571 goto_if(db_bind_int(db, st, 1, id) != APPL_ERROR_NONE, error);
572 goto_if(db_next(db, st) == -1, error);
577 /* keep the sticker panel DB opened */
579 return APPL_ERROR_NONE;
583 return APPL_ERROR_FAIL;
588 HAPI int db_list_group(sqlite3 *db, Eina_List **group_list)
590 const char *const QUERY_LIST = "SELECT id, title, city1, city2, city3, city4, city5 FROM gr ORDER BY id ASC";
591 sqlite3_stmt *st = NULL;
592 group_info_s *group_info = NULL;
596 st = db_prepare(db, QUERY_LIST);
597 retv_if(!st, APPL_ERROR_FAIL);
601 const char *title = NULL;
602 int city1 = 0, city2 = 0, city3 = 0, city4 = 0, city5 = 0;
603 ret = db_next(db, st);
604 if (SQLITE_DONE == ret) {
606 } else if (-1 == ret) {
607 _E("db_next() error");
611 id = db_get_int(st, 0);
612 title = db_get_str(st, 1);
613 city1 = db_get_int(st, 2);
614 city2 = db_get_int(st, 3);
615 city3 = db_get_int(st, 4);
616 city4 = db_get_int(st, 5);
617 city5 = db_get_int(st, 6);
619 group_info = group_info_create(id, title, city1, city2, city3, city4, city5);
620 continue_if(!group_info);
622 *group_list = eina_list_append(*group_list, group_info);
623 } while (SQLITE_ROW == ret);
628 /* keep the sticker panel DB opened */
630 return APPL_ERROR_NONE;
633 EINA_LIST_FREE(*group_list, group_info) {
634 group_info_destroy(group_info);
638 return APPL_ERROR_FAIL;