2 * Copyright (c) 2019 Samsung Electronics Co., Ltd All Rights Reserved
4 * Licensed under the Apache License, Version 2.0 (the License);
5 * you may not use this file except in compliance with the License.
6 * You may obtain a copy of the License at
8 * http://www.apache.org/licenses/LICENSE-2.0
10 * Unless required by applicable law or agreed to in writing, software
11 * distributed under the License is distributed on an AS IS BASIS,
12 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13 * See the License for the specific language governing permissions and
14 * limitations under the License.
22 #include "stickerd_db_manager.h"
23 #include "stickerd_error.h"
28 #define LOG_TAG "STICKERD_DB_MANAGER"
34 * +-----------------+--------+------+------+-----------+-------------+------------+------+--------------+
35 * | INT | TEXT | INT | TEXT | TEXT | TEXT | TEXT | TEXT | INT |
36 * +-----------------+--------+------+------+-----------+-------------+------------+------+--------------+
37 * | sticker_info_id | app_id | type | uri | thumbnail | description | group_name | date | display_type |
38 * +-----------------+--------+------+------+-----------+-------------+------------+------+--------------+
40 * sticker_keyword_info
41 * +------------+-----------------+---------+
42 * | INT | INT | TEXT |
43 * +------------+-----------------+---------+
44 * | keyword_id | sticker_info_id | keyword |
45 * +------------+-----------------+---------+
47 * sticker_whitelist_info
48 * +--------------+-------------+-------------+
49 * | INT | TEXT | TEXT |
50 * +--------------+-------------+-------------+
51 * | whitelist_id | provider_id | consumer_id |
52 * +------------+---------------+-------------+
54 * sticker_recent_history_info
55 * +------------+-----------------+-------+-----------+
56 * | INT | INT | INT | TEXT |
57 * +------------+-----------------+-------+-----------+
58 * | history_id | sticker_info_id | count | timestamp |
59 * +------------+-----------------+-------+-----------+
63 #define STICKER_DB_PATH tzplatform_mkpath(TZ_SYS_DB, ".sticker_info.db")
64 #define STICKER_INFO_CREATE_TABLE "CREATE TABLE IF NOT EXISTS sticker_info(sticker_info_id INTEGER PRIMARY KEY AUTOINCREMENT, app_id TEXT NOT NULL, type INTEGER NOT NULL, uri TEXT NOT NULL, thumbnail TEXT, description TEXT, group_name TEXT NOT NULL, date TEXT NOT NULL, display_type INTEGER)"
65 #define STICKER_KEYWORD_INFO_CREATE_TABLE "CREATE TABLE IF NOT EXISTS sticker_keyword_info(keyword_id INTEGER PRIMARY KEY AUTOINCREMENT, sticker_info_id INTEGER, keyword TEXT NOT NULL, FOREIGN KEY (sticker_info_id) REFERENCES sticker_info(sticker_info_id) ON DELETE CASCADE)"
66 #define STICKER_WHITELIST_INFO_CREATE_TABLE "CREATE TABLE IF NOT EXISTS sticker_whitelist_info(whitelist_id INTEGER PRIMARY KEY AUTOINCREMENT, provider_id TEXT NOT NULL, consumer_id TEXT NOT NULL)"
67 #define STICKER_RECENT_HISTORY_INFO_CREATE_TABLE "CREATE TABLE IF NOT EXISTS sticker_recent_history_info(history_id INTEGER PRIMARY KEY AUTOINCREMENT, sticker_info_id INTEGER, count INTEGER NOT NULL, timestamp TEXT NOT NULL, FOREIGN KEY (sticker_info_id) REFERENCES sticker_info(sticker_info_id) ON DELETE CASCADE)"
69 #define STICKER_DB_INSERT_STICKER_INFO "INSERT INTO sticker_info (app_id, type, uri, thumbnail, description, group_name, date, display_type) VALUES (?, ?, ?, ?, ?, ?, DateTime('now','localtime'), ?)"
70 #define STICKER_DB_INSERT_STICKER_KEYWORD_INFO "INSERT INTO sticker_keyword_info (sticker_info_id, keyword) VALUES (?, ?)"
71 #define STICKER_DB_INSERT_RECENT_HISTORY "INSERT INTO sticker_recent_history_info (sticker_info_id, count, timestamp) VALUES (?, 1, DateTime('now','localtime'))"
73 #define STICKER_DB_DELETE_STICKER_INFO "DELETE FROM sticker_info WHERE sticker_info_id = ?"
74 #define STICKER_DB_DELETE_STICKER_KEYWORD_INFO "DELETE FROM sticker_keyword_info WHERE sticker_info_id = ?"
75 #define STICKER_DB_DELETE_STICKER_INFO_BY_URI "DELETE FROM sticker_info WHERE uri = ?"
77 #define STICKER_DB_UPDATE_STICKER_TYPE "UPDATE sticker_info SET type = ?, date = DateTime('now','localtime') WHERE sticker_info_id = ?"
78 #define STICKER_DB_UPDATE_STICKER_URI "UPDATE sticker_info SET uri = ?, date = DateTime('now','localtime') WHERE sticker_info_id = ?"
79 #define STICKER_DB_UPDATE_STICKER_THUMBNAIL "UPDATE sticker_info SET thumbnail = ?, date = DateTime('now','localtime') WHERE sticker_info_id = ?"
80 #define STICKER_DB_UPDATE_STICKER_DESCRIPTION "UPDATE sticker_info SET description = ?, date = DateTime('now','localtime') WHERE sticker_info_id = ?"
81 #define STICKER_DB_UPDATE_STICKER_GROUP "UPDATE sticker_info SET group_name = ?, date = DateTime('now','localtime') WHERE sticker_info_id = ?"
82 #define STICKER_DB_UPDATE_STICKER_DISP_TYPE "UPDATE sticker_info SET display_type = ?, date = DateTime('now','localtime') WHERE sticker_info_id = ?"
83 #define STICKER_DB_UPDATE_RECENT_HISTORY "UPDATE sticker_recent_history_info SET count = count + 1, timestamp = DateTime('now','localtime') WHERE sticker_info_id = ?"
85 #define STICKER_DB_GET_LATEST_RECORD_ID "SELECT sticker_info_id FROM sticker_info ORDER BY sticker_info_id DESC LIMIT 1"
86 #define STICKER_DB_GET_STICKER_INFO_BY_RECORD_ID "SELECT * FROM sticker_info WHERE sticker_info_id = ?"
87 #define STICKER_DB_GET_KEYWORD_INFO_BY_RECORD_ID "SELECT keyword FROM sticker_keyword_info WHERE sticker_info_id = ?"
88 #define STICKER_DB_GET_IMAGE_INFO_BY_RECORED_ID "SELECT type, uri, thumbnail FROM sticker_info WHERE sticker_info_id = ?"
89 #define STICKER_DB_GET_IMAGE_INFO_BY_URI "SELECT type, thumbnail FROM sticker_info WHERE uri = ?"
90 #define STICKER_DB_GET_ALL_GROUP_LIST "SELECT DISTINCT group_name FROM sticker_info WHERE app_id NOT IN (SELECT DISTINCT provider_id FROM sticker_whitelist_info WHERE provider_id NOT IN (SELECT provider_id FROM sticker_whitelist_info WHERE consumer_id = ?))"
91 #define STICKER_DB_GET_ALL_KEYWORD_LIST "SELECT DISTINCT keyword FROM sticker_keyword_info WHERE sticker_info_id IN (SELECT sticker_info_id from sticker_info WHERE app_id NOT IN (SELECT DISTINCT provider_id FROM sticker_whitelist_info WHERE provider_id NOT IN (SELECT provider_id FROM sticker_whitelist_info WHERE consumer_id = ?)))"
92 #define STICKER_DB_GET_STICKER_COUNT "SELECT count(*) FROM sticker_info WHERE app_id = ?"
93 #define STICKER_DB_GET_ALL_RECORD_ID "SELECT sticker_info_id FROM sticker_info WHERE app_id NOT IN (SELECT DISTINCT provider_id FROM sticker_whitelist_info WHERE provider_id NOT IN (SELECT provider_id FROM sticker_whitelist_info WHERE consumer_id = ?)) LIMIT ?, ?"
94 #define STICKER_DB_GET_RECORD_ID_BY_APP_ID "SELECT sticker_info_id from sticker_info WHERE app_id = ? LIMIT ?, ?"
95 #define STICKER_DB_GET_RECORD_ID_BY_TYPE "SELECT sticker_info_id FROM sticker_info WHERE type = ? AND app_id NOT IN (SELECT DISTINCT provider_id FROM sticker_whitelist_info WHERE provider_id NOT IN (SELECT provider_id FROM sticker_whitelist_info WHERE consumer_id = ?)) LIMIT ?, ?"
96 #define STICKER_DB_GET_RECORD_ID_BY_GROUP "SELECT sticker_info_id from sticker_info WHERE group_name = ? AND app_id NOT IN (SELECT DISTINCT provider_id FROM sticker_whitelist_info WHERE provider_id NOT IN (SELECT provider_id FROM sticker_whitelist_info WHERE consumer_id = ?)) LIMIT ?, ?"
97 #define STICKER_DB_GET_RECORD_ID_BY_KEYWORD "SELECT sticker_info_id FROM sticker_keyword_info WHERE keyword = ? INTERSECT SELECT sticker_info_id from sticker_info WHERE app_id NOT IN (SELECT DISTINCT provider_id FROM sticker_whitelist_info WHERE provider_id NOT IN (SELECT provider_id FROM sticker_whitelist_info WHERE consumer_id = ?)) LIMIT ?, ?"
98 #define STICKER_DB_GET_RECORD_ID_BY_DISP_TYPE "SELECT sticker_info_id FROM sticker_info WHERE display_type = ? AND app_id NOT IN (SELECT DISTINCT provider_id FROM sticker_whitelist_info WHERE provider_id NOT IN (SELECT provider_id FROM sticker_whitelist_info WHERE consumer_id = ?)) LIMIT ?, ?"
99 #define STICKER_DB_GET_GROUP_LIST_BY_DISP_TYPE "SELECT DISTINCT group_name FROM sticker_info WHERE display_type = ? AND app_id NOT IN (SELECT DISTINCT provider_id FROM sticker_whitelist_info WHERE provider_id NOT IN (SELECT provider_id FROM sticker_whitelist_info WHERE consumer_id = ?))"
100 #define STICKER_DB_CHECK_FILE_EXISTS "SELECT EXISTS(SELECT 1 FROM sticker_info WHERE uri = ? LIMIT 1)"
101 #define STICKER_DB_CHECK_RECENT_HISTORY_EXISTS "SELECT EXISTS(SELECT 1 FROM sticker_recent_history_info WHERE sticker_info_id = ? LIMIT 1)"
102 #define STICKER_DB_GET_RECENT_HISTORY "SELECT sticker_info_id FROM sticker_recent_history_info ORDER BY datetime(timestamp) DESC LIMIT ?"
110 static gboolean is_corrupted = FALSE;
112 static const char *_db_get_query(sticker_info_db_type sticker_type, command_type cmd_type)
114 static const char* query = NULL;
116 if (cmd_type == CMD_UPDATE) {
117 switch(sticker_type) {
118 case STICKER_DB_STICKER_TYPE:
119 query = STICKER_DB_UPDATE_STICKER_TYPE;
121 case STICKER_DB_STICKER_URI:
122 query = STICKER_DB_UPDATE_STICKER_URI;
124 case STICKER_DB_STICKER_THUMBNAIL:
125 query = STICKER_DB_UPDATE_STICKER_THUMBNAIL;
127 case STICKER_DB_STICKER_DESCRIPTION:
128 query = STICKER_DB_UPDATE_STICKER_DESCRIPTION;
130 case STICKER_DB_STICKER_GROUP:
131 query = STICKER_DB_UPDATE_STICKER_GROUP;
133 case STICKER_DB_STICKER_KEYWORD:
134 query = STICKER_DB_DELETE_STICKER_KEYWORD_INFO;
136 case STICKER_DB_STICKER_DISP_TYPE:
137 query = STICKER_DB_UPDATE_STICKER_DISP_TYPE;
143 } else if (cmd_type == CMD_SELECT) {
144 switch(sticker_type) {
145 case STICKER_DB_STICKER_ALL:
146 query = STICKER_DB_GET_ALL_RECORD_ID;
148 case STICKER_DB_STICKER_APPID:
149 query = STICKER_DB_GET_RECORD_ID_BY_APP_ID;
151 case STICKER_DB_STICKER_TYPE:
152 query = STICKER_DB_GET_RECORD_ID_BY_TYPE;
154 case STICKER_DB_STICKER_GROUP:
155 query = STICKER_DB_GET_RECORD_ID_BY_GROUP;
157 case STICKER_DB_STICKER_KEYWORD:
158 query = STICKER_DB_GET_RECORD_ID_BY_KEYWORD;
160 case STICKER_DB_STICKER_DISP_TYPE:
161 query = STICKER_DB_GET_RECORD_ID_BY_DISP_TYPE;
162 case STICKER_DB_STICKER_RECENT_HISTORY:
163 query = STICKER_DB_GET_RECENT_HISTORY;
174 static int _recover_db(void)
176 int ret = STICKERD_SERVER_ERROR_NONE;
180 LOGD("Start to recover sticker db");
181 //Remove sticker database file
182 if (unlink(STICKER_DB_PATH) == -1)
183 LOGE("Failed to remove db file");
185 ret = sqlite3_open_v2(STICKER_DB_PATH, &db, SQLITE_OPEN_CREATE | SQLITE_OPEN_READWRITE, NULL);
186 if (ret != SQLITE_OK) {
187 LOGE("Failed to open db : %s", sqlite3_errmsg(db));
188 if (unlink(STICKER_DB_PATH) == -1)
189 LOGE("Failed to remove db file");
190 ret = STICKERD_SERVER_ERROR_DB_FAILED;
194 ret = sqlite3_exec(db, STICKER_INFO_CREATE_TABLE, NULL, NULL, &err);
195 if (ret != SQLITE_OK) {
196 LOGE("Failed to create sticker_info table : %s" , err);
197 ret = STICKERD_SERVER_ERROR_DB_FAILED;
201 ret = sqlite3_exec(db, STICKER_KEYWORD_INFO_CREATE_TABLE, NULL, NULL, &err);
202 if (ret != SQLITE_OK) {
203 LOGE("Failed to create sticker_keyword_info table : %s", err);
204 ret = STICKERD_SERVER_ERROR_DB_FAILED;
208 ret = sqlite3_exec(db, STICKER_WHITELIST_INFO_CREATE_TABLE, NULL, NULL, &err);
209 if (ret != SQLITE_OK) {
210 LOGE("Failed to create sticker_whitelist_info table : %s", err);
211 ret = STICKERD_SERVER_ERROR_DB_FAILED;
214 ret = sqlite3_exec(db, STICKER_RECENT_HISTORY_INFO_CREATE_TABLE, NULL, NULL, &err);
215 if (ret != SQLITE_OK) {
216 LOGE("Failed to create sticker_recent_history_info table : %s", err);
217 ret = STICKERD_SERVER_ERROR_DB_FAILED;
220 is_corrupted = FALSE;
232 static int _integrity_check_cb(void *pid, int argc, char **argv, char **notUsed)
234 if (strcmp(argv[0], "ok") != 0) {
235 LOGE("DB integrity check failed : %s", argv[0]);
240 LOGD("Result integrity : %s", argv[0]);
244 int stickerd_db_init(void)
246 int ret = STICKERD_SERVER_ERROR_NONE;
250 ret = sqlite3_open_v2(STICKER_DB_PATH, &db, SQLITE_OPEN_CREATE | SQLITE_OPEN_READWRITE, NULL);
251 if (ret != SQLITE_OK) {
252 LOGE("Failed to open db : %s", sqlite3_errmsg(db));
253 ret = STICKERD_SERVER_ERROR_DB_FAILED;
257 ret = sqlite3_exec(db, STICKER_INFO_CREATE_TABLE, NULL, NULL, &err);
258 if (ret != SQLITE_OK) {
259 LOGE("Failed to create sticker_info table : %s" , err);
260 ret = STICKERD_SERVER_ERROR_DB_FAILED;
264 ret = sqlite3_exec(db, STICKER_KEYWORD_INFO_CREATE_TABLE, NULL, NULL, &err);
265 if (ret != SQLITE_OK) {
266 LOGE("Failed to create sticker_keyword_info table : %s", err);
267 ret = STICKERD_SERVER_ERROR_DB_FAILED;
271 ret = sqlite3_exec(db, STICKER_WHITELIST_INFO_CREATE_TABLE, NULL, NULL, &err);
272 if (ret != SQLITE_OK) {
273 LOGE("Failed to create sticker_whitelist_info table : %s", err);
274 ret = STICKERD_SERVER_ERROR_DB_FAILED;
278 ret = sqlite3_exec(db, STICKER_RECENT_HISTORY_INFO_CREATE_TABLE, NULL, NULL, &err);
279 if (ret != SQLITE_OK) {
280 LOGE("Failed to create sticker_recent_history_info table : %s", err);
281 ret = STICKERD_SERVER_ERROR_DB_FAILED;
285 ret = sqlite3_exec(db, "PRAGMA journal_mode = WAL", NULL, NULL, &err);
286 if (ret != SQLITE_OK) {
287 LOGE("Failed to set journal_mode : %s", err);
288 ret = STICKERD_SERVER_ERROR_DB_FAILED;
292 ret = sqlite3_exec(db, "PRAGMA integrity_check", _integrity_check_cb, NULL, &err);
293 if (ret != SQLITE_OK) {
294 LOGE("Failed to check integrity : %s", err);
295 ret = STICKERD_SERVER_ERROR_DB_FAILED;
305 if (ret == SQLITE_CORRUPT || ret == SQLITE_NOTADB || is_corrupted)
311 static sqlite3 *_db_open(void)
317 ret = sqlite3_open(STICKER_DB_PATH, &db);
318 if (ret != SQLITE_OK) {
319 LOGE("Failed to open db : %s", sqlite3_errmsg(db));
323 ret = sqlite3_exec(db, "PRAGMA foreign_keys = ON", NULL, NULL, &err);
324 if (ret != SQLITE_OK) {
325 LOGE("Failed to turn on foreign keys : %s", err);
334 int stickerd_db_insert_sticker_info(int *record_id, sticker_info_db *sticker_info)
338 sqlite3_stmt *stmt = NULL;
342 return STICKERD_SERVER_ERROR_DB_FAILED;
344 ret = sqlite3_prepare_v2(db, STICKER_DB_INSERT_STICKER_INFO, -1, &stmt, NULL);
345 if (ret != SQLITE_OK) {
346 LOGE("fail to insert sticker information : %s", sqlite3_errmsg(db));
350 sqlite3_bind_text(stmt, 1, sticker_info->app_id, -1, SQLITE_TRANSIENT);
351 sqlite3_bind_int(stmt, 2, sticker_info->type);
352 sqlite3_bind_text(stmt, 3, sticker_info->uri, -1, SQLITE_TRANSIENT);
353 sqlite3_bind_text(stmt, 4, sticker_info->thumbnail, -1, SQLITE_TRANSIENT);
354 sqlite3_bind_text(stmt, 5, sticker_info->description, -1, SQLITE_TRANSIENT);
355 sqlite3_bind_text(stmt, 6, sticker_info->group, -1, SQLITE_TRANSIENT);
356 sqlite3_bind_int(stmt, 7, sticker_info->display_type);
358 ret = sqlite3_step(stmt);
359 if (ret != SQLITE_OK && ret != SQLITE_DONE) {
360 LOGE("sqlite3_step() failed : ret(%d)", ret);
362 } else if (sqlite3_changes(db) == 0) {
363 LOGE("No changes to DB");
367 sqlite3_finalize(stmt);
370 ret = sqlite3_prepare_v2(db, STICKER_DB_GET_LATEST_RECORD_ID, -1, &stmt, NULL);
371 if (ret != SQLITE_OK) {
372 LOGE("fail to get sticker id : %s", sqlite3_errmsg(db));
376 ret = sqlite3_step(stmt);
377 if (ret == SQLITE_ERROR) {
378 LOGE("sqlite3_step() failed : ret(%d)", ret);
382 *record_id = sqlite3_column_int(stmt, 0);
383 LOGD("record_id : %d", *record_id);
387 for(list = sticker_info->keyword; list != NULL; list=list->next) {
388 sqlite3_finalize(stmt);
391 ret = sqlite3_prepare_v2(db, STICKER_DB_INSERT_STICKER_KEYWORD_INFO, -1, &stmt, NULL);
392 if (ret != SQLITE_OK) {
393 LOGE("fail to insert sticker keyword : %s", sqlite3_errmsg(db));
397 sqlite3_bind_int(stmt, 1, *record_id);
398 sqlite3_bind_text(stmt, 2, (char *)list->data, -1, SQLITE_TRANSIENT);
400 ret = sqlite3_step(stmt);
401 if (ret != SQLITE_OK && ret != SQLITE_DONE) {
402 LOGE("sqlite3_step() failed : ret(%d)", ret);
404 } else if (sqlite3_changes(db) == 0) {
405 LOGE("No changes to DB");
410 sqlite3_finalize(stmt);
413 return STICKERD_SERVER_ERROR_NONE;
416 sqlite3_finalize(stmt);
419 return STICKERD_SERVER_ERROR_DB_FAILED;
422 int stickerd_db_delete_sticker_info(int record_id)
426 sqlite3_stmt *stmt = NULL;
430 return STICKERD_SERVER_ERROR_DB_FAILED;
432 ret = sqlite3_prepare_v2(db, STICKER_DB_GET_IMAGE_INFO_BY_RECORED_ID, -1, &stmt, NULL);
433 if (ret != SQLITE_OK) {
434 LOGE("fail to get image files : %s", sqlite3_errmsg(db));
438 sqlite3_bind_int(stmt, 1, record_id);
440 ret = sqlite3_step(stmt);
441 if (ret == SQLITE_ERROR) {
442 LOGE("sqlite3_step() failed : ret(%d)", ret);
446 int uri_type = sqlite3_column_int(stmt, 0);
447 const unsigned char *uri = sqlite3_column_text(stmt, 1);
448 const unsigned char *thumbnail = sqlite3_column_text(stmt, 2);
450 if (uri_type == 1 && unlink((const char *)uri) == -1)
451 LOGE("fail to delete sticker file");
453 if (thumbnail && unlink((const char *)thumbnail) == -1)
454 LOGE("fail to delete thumbnail image");
456 sqlite3_finalize(stmt);
459 ret = sqlite3_prepare_v2(db, STICKER_DB_DELETE_STICKER_INFO, -1, &stmt, NULL);
460 if (ret != SQLITE_OK) {
461 LOGE("fail to delete sticker information : %s", sqlite3_errmsg(db));
465 sqlite3_bind_int(stmt, 1, record_id);
467 ret = sqlite3_step(stmt);
468 if (ret != SQLITE_OK && ret != SQLITE_DONE) {
469 LOGE("sqlite3_step() failed : ret(%d)", ret);
471 } else if (sqlite3_changes(db) == 0) {
472 LOGE("No changes to DB");
476 sqlite3_finalize(stmt);
479 return STICKERD_SERVER_ERROR_NONE;
482 sqlite3_finalize(stmt);
485 return STICKERD_SERVER_ERROR_DB_FAILED;
488 int stickerd_db_delete_sticker_info_by_uri(char *uri)
492 sqlite3_stmt *stmt = NULL;
496 return STICKERD_SERVER_ERROR_DB_FAILED;
498 ret = sqlite3_prepare_v2(db, STICKER_DB_GET_IMAGE_INFO_BY_URI, -1, &stmt, NULL);
499 if (ret != SQLITE_OK) {
500 LOGE("fail to delete sticker information : %s", sqlite3_errmsg(db));
504 sqlite3_bind_text(stmt, 1, uri, -1, SQLITE_TRANSIENT);
506 ret = sqlite3_step(stmt);
507 if (ret == SQLITE_ERROR) {
508 LOGE("sqlite3_step() failed : ret(%d)", ret);
512 int uri_type = sqlite3_column_int(stmt, 0);
513 const unsigned char *thumbnail = sqlite3_column_text(stmt, 1);
515 if (uri_type == 1 && unlink((const char *)uri) == -1)
516 LOGE("fail to delete sticker file");
518 if (thumbnail && unlink((const char *)thumbnail) == -1)
519 LOGE("fail to delete thumbnail image");
521 sqlite3_finalize(stmt);
524 ret = sqlite3_prepare_v2(db, STICKER_DB_DELETE_STICKER_INFO_BY_URI, -1, &stmt, NULL);
525 if (ret != SQLITE_OK) {
526 LOGE("fail to delete sticker information : %s", sqlite3_errmsg(db));
530 sqlite3_bind_text(stmt, 1, uri, -1, SQLITE_TRANSIENT);
532 ret = sqlite3_step(stmt);
533 if (ret != SQLITE_OK && ret != SQLITE_DONE) {
534 LOGE("sqlite3_step() failed : ret(%d)", ret);
536 } else if (sqlite3_changes(db) == 0) {
537 LOGE("No changes to DB");
541 sqlite3_finalize(stmt);
544 return STICKERD_SERVER_ERROR_NONE;
547 sqlite3_finalize(stmt);
550 return STICKERD_SERVER_ERROR_DB_FAILED;
553 int stickerd_db_update_sticker_info(int record_id, sticker_info_db_type type, void *data)
557 sqlite3_stmt *stmt = NULL;
561 return STICKERD_SERVER_ERROR_DB_FAILED;
563 const char* query = _db_get_query(type, CMD_UPDATE);
564 ret = sqlite3_prepare_v2(db, query, -1, &stmt, NULL);
565 if (ret != SQLITE_OK) {
566 LOGE("fail to update sticker information : %s", sqlite3_errmsg(db));
570 if (type == STICKER_DB_STICKER_TYPE || type == STICKER_DB_STICKER_DISP_TYPE) {
571 sqlite3_bind_int(stmt, 1, *(int *)data);
572 } else if (type == STICKER_DB_STICKER_KEYWORD) {
573 sqlite3_bind_int(stmt, 1, record_id);
575 sqlite3_bind_text(stmt, 1, (char *)data, -1, SQLITE_TRANSIENT);
578 if (type != STICKER_DB_STICKER_KEYWORD)
579 sqlite3_bind_int(stmt, 2, record_id);
581 ret = sqlite3_step(stmt);
582 if (ret != SQLITE_OK && ret != SQLITE_DONE) {
583 LOGE("sqlite3_step() failed : ret(%d)", ret);
587 if (type == STICKER_DB_STICKER_KEYWORD) {
589 for(list = (GList *)data; list != NULL; list=list->next) {
590 sqlite3_finalize(stmt);
593 ret = sqlite3_prepare_v2(db, STICKER_DB_INSERT_STICKER_KEYWORD_INFO, -1, &stmt, NULL);
594 if (ret != SQLITE_OK) {
595 LOGE("fail to insert sticker information : %s", sqlite3_errmsg(db));
599 sqlite3_bind_int(stmt, 1, record_id);
600 sqlite3_bind_text(stmt, 2, (char *)list->data, -1, SQLITE_TRANSIENT);
602 ret = sqlite3_step(stmt);
603 if (ret != SQLITE_OK && ret != SQLITE_DONE) {
604 LOGE("sqlite3_step() failed : ret(%d)", ret);
606 } else if (sqlite3_changes(db) == 0) {
607 LOGE("No changes to DB");
613 sqlite3_finalize(stmt);
616 return STICKERD_SERVER_ERROR_NONE;
619 sqlite3_finalize(stmt);
622 return STICKERD_SERVER_ERROR_DB_FAILED;
625 int stickerd_db_get_sticker_info_by_record_id(int record_id, sticker_info_db *sticker_info)
629 sqlite3_stmt *stmt = NULL;
633 return STICKERD_SERVER_ERROR_DB_FAILED;
635 ret = sqlite3_prepare_v2(db, STICKER_DB_GET_STICKER_INFO_BY_RECORD_ID, -1, &stmt, NULL);
636 if (ret != SQLITE_OK) {
637 LOGE("fail to get sticker information : %s", sqlite3_errmsg(db));
641 sqlite3_bind_int(stmt, 1, record_id);
643 ret = sqlite3_step(stmt);
644 if (ret == SQLITE_ERROR) {
645 LOGE("sqlite3_step() failed : ret(%d)", ret);
649 const unsigned char *tmp_app_id = sqlite3_column_text(stmt, 1);
651 sticker_info->app_id = strdup((const char *)tmp_app_id);
653 LOGW("invalid record_id : %d", record_id);
657 sticker_info->type = sqlite3_column_int(stmt, 2);
659 const unsigned char *tmp_uri = sqlite3_column_text(stmt, 3);
661 sticker_info->uri = strdup((const char *)tmp_uri);
663 const unsigned char *tmp_thumbnail = sqlite3_column_text(stmt, 4);
665 sticker_info->thumbnail = strdup((const char *)tmp_thumbnail);
667 const unsigned char *tmp_description = sqlite3_column_text(stmt, 5);
669 sticker_info->description = strdup((const char *)tmp_description);
671 const unsigned char *tmp_group = sqlite3_column_text(stmt, 6);
673 sticker_info->group = strdup((const char *)tmp_group);
675 const unsigned char *tmp_date = sqlite3_column_text(stmt, 7);
677 sticker_info->date = strdup((const char *)tmp_date);
679 sticker_info->display_type = sqlite3_column_int(stmt, 8);
681 sqlite3_finalize(stmt);
684 ret = sqlite3_prepare_v2(db, STICKER_DB_GET_KEYWORD_INFO_BY_RECORD_ID, -1, &stmt, NULL);
685 if (ret != SQLITE_OK) {
686 LOGE("fail to get sticker keyword : %s", sqlite3_errmsg(db));
690 sqlite3_bind_int(stmt, 1, record_id);
692 while (sqlite3_step(stmt) == SQLITE_ROW) {
693 const unsigned char *keyword = sqlite3_column_text(stmt, 0);
695 sticker_info->keyword = g_list_append(sticker_info->keyword, strdup((const char *)keyword));
698 sqlite3_finalize(stmt);
701 return STICKERD_SERVER_ERROR_NONE;
704 sqlite3_finalize(stmt);
707 return STICKERD_SERVER_ERROR_DB_FAILED;
710 int stickerd_db_get_group_list(GVariantBuilder *builder, char *app_id)
714 sqlite3_stmt *stmt = NULL;
718 return STICKERD_SERVER_ERROR_DB_FAILED;
720 ret = sqlite3_prepare_v2(db, STICKER_DB_GET_ALL_GROUP_LIST, -1, &stmt, NULL);
721 if (ret != SQLITE_OK) {
722 LOGE("fail to get group list : %s", sqlite3_errmsg(db));
726 sqlite3_bind_text(stmt, 1, app_id, -1, SQLITE_TRANSIENT);
728 while (sqlite3_step(stmt) == SQLITE_ROW) {
729 const unsigned char *group = sqlite3_column_text(stmt, 0);
731 g_variant_builder_add(builder, "(s)", strdup((const char *)group));
734 sqlite3_finalize(stmt);
737 return STICKERD_SERVER_ERROR_NONE;
740 sqlite3_finalize(stmt);
743 return STICKERD_SERVER_ERROR_DB_FAILED;
746 int stickerd_db_get_keyword_list(GVariantBuilder *builder, char *app_id)
750 sqlite3_stmt *stmt = NULL;
754 return STICKERD_SERVER_ERROR_DB_FAILED;
756 ret = sqlite3_prepare_v2(db, STICKER_DB_GET_ALL_KEYWORD_LIST, -1, &stmt, NULL);
757 if (ret != SQLITE_OK) {
758 LOGE("fail to get keyword list : %s", sqlite3_errmsg(db));
762 sqlite3_bind_text(stmt, 1, app_id, -1, SQLITE_TRANSIENT);
764 while (sqlite3_step(stmt) == SQLITE_ROW) {
765 const unsigned char *keyword = sqlite3_column_text(stmt, 0);
767 g_variant_builder_add(builder, "(s)", strdup((const char *)keyword));
770 sqlite3_finalize(stmt);
773 return STICKERD_SERVER_ERROR_NONE;
776 sqlite3_finalize(stmt);
779 return STICKERD_SERVER_ERROR_DB_FAILED;
782 int stickerd_db_get_sticker_count(int *count, char *app_id)
786 sqlite3_stmt *stmt = NULL;
790 return STICKERD_SERVER_ERROR_DB_FAILED;
792 ret = sqlite3_prepare_v2(db, STICKER_DB_GET_STICKER_COUNT, -1, &stmt, NULL);
793 if (ret != SQLITE_OK) {
794 LOGE("fail to get sticker count : %s", sqlite3_errmsg(db));
798 sqlite3_bind_text(stmt, 1, app_id, -1, SQLITE_TRANSIENT);
800 ret = sqlite3_step(stmt);
801 if (ret == SQLITE_ERROR) {
802 LOGE("sqlite3_step() failed : ret(%d)", ret);
806 *count = sqlite3_column_int(stmt, 0);
808 sqlite3_finalize(stmt);
811 return STICKERD_SERVER_ERROR_NONE;
814 sqlite3_finalize(stmt);
817 return STICKERD_SERVER_ERROR_DB_FAILED;
820 int stickerd_db_get_record_id(sticker_info_db_type type, GList **id_list, void *data, char *app_id, int offset, int count)
824 sqlite3_stmt *stmt = NULL;
828 return STICKERD_SERVER_ERROR_DB_FAILED;
830 const char* query = _db_get_query(type, CMD_SELECT);
831 ret = sqlite3_prepare_v2(db, query, -1, &stmt, NULL);
832 if (ret != SQLITE_OK) {
833 LOGE("fail to get record id : %s", sqlite3_errmsg(db));
837 if (type == STICKER_DB_STICKER_ALL || type == STICKER_DB_STICKER_APPID) {
838 sqlite3_bind_text(stmt, 1, app_id, -1, SQLITE_TRANSIENT);
839 sqlite3_bind_int(stmt, 2, offset);
840 sqlite3_bind_int(stmt, 3, count);
841 } else if (type == STICKER_DB_STICKER_RECENT_HISTORY) {
842 sqlite3_bind_int(stmt, 1, count);
844 if (type == STICKER_DB_STICKER_TYPE || type == STICKER_DB_STICKER_DISP_TYPE)
845 sqlite3_bind_int(stmt, 1, *(int *)data);
847 sqlite3_bind_text(stmt, 1, (char *)data, -1, SQLITE_TRANSIENT);
849 sqlite3_bind_text(stmt, 2, app_id, -1, SQLITE_TRANSIENT);
850 sqlite3_bind_int(stmt, 3, offset);
851 sqlite3_bind_int(stmt, 4, count);
854 while (sqlite3_step(stmt) == SQLITE_ROW) {
855 const unsigned char *tmp_id = sqlite3_column_text(stmt, 0);
857 *id_list = g_list_append(*id_list, strdup((const char *)tmp_id));
860 sqlite3_finalize(stmt);
863 return STICKERD_SERVER_ERROR_NONE;
866 sqlite3_finalize(stmt);
869 return STICKERD_SERVER_ERROR_DB_FAILED;
872 int stickerd_db_get_group_list_by_display_type(GVariantBuilder *builder, char *app_id, int disp_type)
876 sqlite3_stmt *stmt = NULL;
880 return STICKERD_SERVER_ERROR_DB_FAILED;
882 ret = sqlite3_prepare_v2(db, STICKER_DB_GET_GROUP_LIST_BY_DISP_TYPE, -1, &stmt, NULL);
883 if (ret != SQLITE_OK) {
884 LOGE("fail to get group list : %s", sqlite3_errmsg(db));
888 sqlite3_bind_int(stmt, 1, disp_type);
889 sqlite3_bind_text(stmt, 2, app_id, -1, SQLITE_TRANSIENT);
891 while (sqlite3_step(stmt) == SQLITE_ROW) {
892 const unsigned char *group = sqlite3_column_text(stmt, 0);
894 g_variant_builder_add(builder, "(s)", strdup((const char *)group));
897 sqlite3_finalize(stmt);
900 return STICKERD_SERVER_ERROR_NONE;
903 sqlite3_finalize(stmt);
906 return STICKERD_SERVER_ERROR_DB_FAILED;
909 int stickerd_db_check_file_exists(int *result, char *uri)
913 sqlite3_stmt *stmt = NULL;
917 return STICKERD_SERVER_ERROR_DB_FAILED;
919 ret = sqlite3_prepare_v2(db, STICKER_DB_CHECK_FILE_EXISTS, -1, &stmt, NULL);
920 if (ret != SQLITE_OK) {
921 LOGE("fail to check file exists : %s", sqlite3_errmsg(db));
925 sqlite3_bind_text(stmt, 1, uri, -1, SQLITE_TRANSIENT);
927 ret = sqlite3_step(stmt);
928 if (ret == SQLITE_ERROR) {
929 LOGE("sqlite3_step() failed : ret(%d)", ret);
933 *result = sqlite3_column_int(stmt, 0);
935 sqlite3_finalize(stmt);
938 return STICKERD_SERVER_ERROR_NONE;
941 sqlite3_finalize(stmt);
944 return STICKERD_SERVER_ERROR_DB_FAILED;
947 int stickerd_db_insert_recent_sticker_info(int record_id)
951 sqlite3_stmt *stmt = NULL;
955 return STICKERD_SERVER_ERROR_DB_FAILED;
957 ret = sqlite3_prepare_v2(db, STICKER_DB_CHECK_RECENT_HISTORY_EXISTS, -1, &stmt, NULL);
958 if (ret != SQLITE_OK) {
959 LOGE("fail to check recent sticker exists : %s", sqlite3_errmsg(db));
963 sqlite3_bind_int(stmt, 1, record_id);
965 ret = sqlite3_step(stmt);
966 if (ret == SQLITE_ERROR) {
967 LOGE("sqlite3_step() failed : ret(%d)", ret);
971 int result = sqlite3_column_int(stmt, 0);
973 sqlite3_finalize(stmt);
977 ret = sqlite3_prepare_v2(db, STICKER_DB_UPDATE_RECENT_HISTORY, -1, &stmt, NULL);
979 ret = sqlite3_prepare_v2(db, STICKER_DB_INSERT_RECENT_HISTORY, -1, &stmt, NULL);
981 if (ret != SQLITE_OK) {
982 LOGE("fail to update recent history : %s", sqlite3_errmsg(db));
986 sqlite3_bind_int(stmt, 1, record_id);
988 ret = sqlite3_step(stmt);
989 if (ret != SQLITE_OK && ret != SQLITE_DONE) {
990 LOGE("sqlite3_step() failed : ret(%d)", ret);
992 } else if (sqlite3_changes(db) == 0) {
993 LOGE("No changes to DB");
997 sqlite3_finalize(stmt);
1000 return STICKERD_SERVER_ERROR_NONE;
1003 sqlite3_finalize(stmt);
1006 return STICKERD_SERVER_ERROR_DB_FAILED;