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>
30 #define APP_DB_FILE ".app.db"
35 HAPI sqlite3 *db_open(void)
39 char db_file[FILE_LEN] = {0, };
42 path = app_get_data_path();
45 snprintf(db_file, sizeof(db_file), "%s/%s", path, APP_DB_FILE);
48 ret = sqlite3_open(db_file, &db);
49 if (SQLITE_OK != ret) {
50 _E("%s", sqlite3_errmsg(db));
59 HAPI void db_close(sqlite3 *db)
67 HAPI sqlite3_stmt *db_prepare(sqlite3 *db, const char *query)
69 sqlite3_stmt *stmt = NULL;
72 retv_if(!query, NULL);
74 ret = sqlite3_prepare_v2(db, query, strlen(query), &stmt, NULL);
75 if (SQLITE_OK != ret) {
76 _E("%s, %s", query, sqlite3_errmsg(db));
85 HAPI int db_next(sqlite3 *db, sqlite3_stmt *stmt)
89 retv_if(!stmt, APPL_ERROR_FAIL);
91 ret = sqlite3_step(stmt);
93 case SQLITE_ROW: /* SQLITE_ROW : 100 */
94 return APPL_ERROR_NONE;
95 case SQLITE_DONE: /* SQLITE_ROW : 101 */
96 return APPL_ERROR_NO_DATA;
98 _E("%s", sqlite3_errmsg(db));
99 return APPL_ERROR_FAIL;
102 return APPL_ERROR_NONE;
107 HAPI int db_reset(sqlite3 *db, sqlite3_stmt *stmt)
111 retv_if(!stmt, APPL_ERROR_INVALID_PARAMETER);
113 ret = sqlite3_reset(stmt);
114 if (SQLITE_OK != ret) {
115 _E("%s", sqlite3_errmsg(db));
116 return APPL_ERROR_FAIL;
119 sqlite3_clear_bindings(stmt);
121 return APPL_ERROR_NONE;
126 HAPI int db_bind_bool(sqlite3 *db, sqlite3_stmt *stmt, int idx, bool value)
130 retv_if(!stmt, APPL_ERROR_FAIL);
132 ret = sqlite3_bind_int(stmt, idx, (int) value);
133 if (SQLITE_OK != ret) {
134 _E("%s", sqlite3_errmsg(db));
135 return APPL_ERROR_FAIL;
138 return APPL_ERROR_NONE;
143 HAPI int db_bind_int(sqlite3 *db, sqlite3_stmt *stmt, int idx, int value)
147 retv_if(!stmt, APPL_ERROR_FAIL);
149 ret = sqlite3_bind_int(stmt, idx, value);
150 if (SQLITE_OK != ret) {
151 _E("%s", sqlite3_errmsg(db));
152 return APPL_ERROR_FAIL;
155 return APPL_ERROR_NONE;
160 HAPI int db_bind_double(sqlite3 *db, sqlite3_stmt *stmt, int idx, double value)
164 retv_if(!stmt, APPL_ERROR_FAIL);
166 ret = sqlite3_bind_double(stmt, idx, value);
167 if (SQLITE_OK != ret) {
168 _E("%s", sqlite3_errmsg(db));
169 return APPL_ERROR_FAIL;
172 return APPL_ERROR_NONE;
177 HAPI int db_bind_str(sqlite3 *db, sqlite3_stmt *stmt, int idx, const char *str)
181 retv_if(!stmt, APPL_ERROR_FAIL);
184 ret = sqlite3_bind_text(stmt, idx, str, -1, SQLITE_TRANSIENT);
186 ret = sqlite3_bind_null(stmt, idx);
189 if (SQLITE_OK != ret) {
190 _E("%s", sqlite3_errmsg(db));
191 return APPL_ERROR_FAIL;
194 return APPL_ERROR_NONE;
199 HAPI bool db_get_bool(sqlite3_stmt *stmt, int index)
201 retv_if(!stmt, false);
202 return (bool) (!!sqlite3_column_int(stmt, index));
207 HAPI int db_get_int(sqlite3_stmt *stmt, int index)
210 return sqlite3_column_int(stmt, index);
215 HAPI int db_get_double(sqlite3_stmt *stmt, int index)
218 return sqlite3_column_double(stmt, index);
223 HAPI const char *db_get_str(sqlite3_stmt *stmt, int index)
225 retv_if(!stmt, NULL);
226 return (const char *) sqlite3_column_text(stmt, index);
231 HAPI int db_finalize(sqlite3 *db, sqlite3_stmt *stmt)
235 retv_if(!stmt, APPL_ERROR_INVALID_PARAMETER);
237 ret = sqlite3_finalize(stmt);
238 if (SQLITE_OK != ret) {
239 _E("%s", sqlite3_errmsg(db));
240 return APPL_ERROR_FAIL;
243 return APPL_ERROR_NONE;
248 HAPI int db_exec(sqlite3 *db, const char *query)
250 sqlite3_stmt *stmt = NULL;
252 retv_if(!query, APPL_ERROR_INVALID_PARAMETER);
254 stmt = db_prepare(db, query);
255 retv_if(!stmt, APPL_ERROR_FAIL);
257 goto_if(APPL_ERROR_FAIL == db_next(db, stmt), ERROR);
258 goto_if(APPL_ERROR_FAIL == db_finalize(db, stmt), ERROR);
260 return APPL_ERROR_NONE;
263 if (stmt) db_finalize(db, stmt);
264 return APPL_ERROR_FAIL;
269 HAPI int db_create_table(sqlite3 *db)
271 const char *TABLES[] = {
272 "CREATE TABLE IF NOT EXIST db_checksum (version INT);",
273 "CREATE TABLE IF NOT EXIST path_information ("
274 "path_id INTEGER PRIMARY KEY"
275 ", user_id INTEGER NOT NULL"
276 ", latitude DOUBLE NOT NULL"
277 ", longitude DOUBLE NOT NULL"
282 ", FOREIGH KEY(user_id) REFERENCES user(user_id)"
284 "CREATE TABLE IF NOT EXIST path ("
285 "path_id INTEGER NOT NULL"
286 ", order INTEGER NOT NULL"
287 ", content_id INTENGER NOT NULL"
288 ", PRIMARY KEY(path_id, order)"
289 ", FOREIGN KEY(path_id) REFERENCES path(path_id)"
290 ", FOREIGH KEY(content_id) REFERENCES content(contend_id)",
292 "CREATE TABLE IF NOT EXIST content ("
293 "content_id INTEGER PRIMARY KEY"
294 ", user_id INTEGER NOT NULL"
295 ", latitude DOUBLE NOT NULL"
296 ", longitude DOUBLE NOT NULL"
302 ", attach_id INTEGER"
303 ", FOREIGN KEY(user_id) REFERENCES user(user_id)"
304 ", FOREIGN KEY(attach_id) REFERENCES attach(attach_id)"
306 "CREATE TABLE IF NOT EXIST attach ("
307 "attach_id INTEGER PRIMARY KEY"
308 ", path TEXT NOT NULL"
309 ", ref_count INT NOT NULL"
311 "CREATE TABLE IF NOT EXIST purchase ("
312 "user_id INTEGER NOT NULL"
313 ", content_id INTEGER NOT NULL"
314 ", PRIMARY KEY(user_id, content_id)"
315 ", FOREIGN KEY(user_id) REFERENCES user(user_id)"
316 ", FOREIGN KEY(content_id) REFERENCES content(content_id)"
318 "CREATE TABLE IF NOT EXIST user ("
319 "user_id INTEGER PRIMARY KEY"
326 count = sizeof(TABLES) / sizeof(char *);
327 for (; i < count; i++) {
328 _D("Create a table[%s]", TABLES[i]);
329 break_if(db_exec(db, TABLES[i]) != APPL_ERROR_NONE);
332 return APPL_ERROR_FAIL;
337 HAPI int db_drop_table(sqlite3 *db)
339 const char *TABLES[] = {
340 "DROP TABLE IF EXIST db_checksum;",
341 "DROP TABLE IF EXIST path_information;",
342 "DROP TABLE IF EXIST path;",
343 "DROP TABLE IF EXIST content;",
344 "DROP TABLE IF EXIST attach;",
345 "DROP TABLE IF EXIST purchase;",
346 "DROP TABLE IF EXIST user;",
351 count = sizeof(TABLES) / sizeof(char *);
352 for (; i < count; i++) {
353 _D("Drop a table[%s]", TABLES[i]);
354 break_if(db_exec(db, TABLES[i]) != APPL_ERROR_NONE);
357 return APPL_ERROR_FAIL;
362 HAPI int db_insert_version(sqlite3 *db, int version)
364 const char *QUERY_SYNTAX = "INSERT INTO db_checksum (version) values (%d);";
367 query = sqlite3_mprintf(QUERY_SYNTAX, version);
368 retv_if(!query, APPL_ERROR_FAIL);
370 if (db_exec(db, query) != APPL_ERROR_NONE) {
371 _E("Cannot execute query.[%s]", query);
373 return APPL_ERROR_FAIL;
378 /* keep the home DB opened */
380 return APPL_ERROR_NONE;
385 HAPI int db_remove_version(sqlite3 *db, int version)
387 const char *QUERY_SYNTAX = "DELETE FROM db_checksum WHERE version=%d;";
390 query = sqlite3_mprintf(QUERY_SYNTAX, version);
391 retv_if(!query, APPL_ERROR_FAIL);
393 if (db_exec(db, query) != APPL_ERROR_NONE) {
394 _E("Cannot execute query.[%s]", query);
396 return APPL_ERROR_FAIL;
401 /* keep the home DB opened */
403 return APPL_ERROR_NONE;
408 HAPI int db_update_version(sqlite3 *db, int version)
410 const char *QUERY_SYNTAX = "UPDATE db_checksum SET version=%d;";
413 query = sqlite3_mprintf(QUERY_SYNTAX, version);
414 retv_if(!query, APPL_ERROR_FAIL);
416 if (db_exec(db, query) != APPL_ERROR_NONE) {
417 _E("Cannot execute query.[%s]", query);
419 return APPL_ERROR_FAIL;
424 /* keep the home DB opened */
426 return APPL_ERROR_NONE;
431 HAPI int db_count_version(sqlite3 *db)
433 const char *QUERY_SYNTAX = "SELECT COUNT(*) FROM db_checksum;";
434 sqlite3_stmt *st = NULL;
437 st = db_prepare(db, QUERY_SYNTAX);
438 retv_if(!st, APPL_ERROR_FAIL);
440 if (db_next(db, st) == APPL_ERROR_FAIL) {
446 count = db_get_int(st, 0);
450 /* keep the home DB opened */