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 * +------------+-----------------+-------+-----------+
61 * * sticker_group_info
62 * +----------+--------+------------+------+------+
63 * | INT | TEXT | TEXT | INT | TEXT |
64 * +----------+--------+------------+------+------+
65 * | group_id | app_id | group_name | type | uri |
66 * +----------+--------+------------+------+------+
70 #define STICKER_DB_PATH tzplatform_mkpath(TZ_SYS_DB, ".sticker_info.db")
71 #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)"
72 #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)"
73 #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)"
74 #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)"
75 #define STICKER_GROUP_INFO_CREATE_TABLE "CREATE TABLE IF NOT EXISTS sticker_group_info(group_id INTEGER PRIMARY KEY AUTOINCREMENT, app_id TEXT NOT NULL, group_name TEXT NOT NULL, type INTEGER NOT NULL, uri TEXT NOT NULL)"
77 #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'), ?)"
78 #define STICKER_DB_INSERT_STICKER_KEYWORD_INFO "INSERT INTO sticker_keyword_info (sticker_info_id, keyword) VALUES (?, ?)"
79 #define STICKER_DB_INSERT_RECENT_HISTORY "INSERT INTO sticker_recent_history_info (sticker_info_id, count, timestamp) VALUES (?, 1, DateTime('now','localtime'))"
80 #define STICKER_DB_INSERT_GROUP_IMAGE "INSERT INTO sticker_group_info (app_id, group_name, type, uri) VALUES (?, ?, ?, ?)"
82 #define STICKER_DB_DELETE_STICKER_INFO "DELETE FROM sticker_info WHERE sticker_info_id = ?"
83 #define STICKER_DB_DELETE_STICKER_KEYWORD_INFO "DELETE FROM sticker_keyword_info WHERE sticker_info_id = ?"
84 #define STICKER_DB_DELETE_STICKER_INFO_BY_URI "DELETE FROM sticker_info WHERE uri = ?"
86 #define STICKER_DB_UPDATE_STICKER_TYPE "UPDATE sticker_info SET type = ?, date = DateTime('now','localtime') WHERE sticker_info_id = ?"
87 #define STICKER_DB_UPDATE_STICKER_URI "UPDATE sticker_info SET uri = ?, date = DateTime('now','localtime') WHERE sticker_info_id = ?"
88 #define STICKER_DB_UPDATE_STICKER_THUMBNAIL "UPDATE sticker_info SET thumbnail = ?, date = DateTime('now','localtime') WHERE sticker_info_id = ?"
89 #define STICKER_DB_UPDATE_STICKER_DESCRIPTION "UPDATE sticker_info SET description = ?, date = DateTime('now','localtime') WHERE sticker_info_id = ?"
90 #define STICKER_DB_UPDATE_STICKER_GROUP "UPDATE sticker_info SET group_name = ?, date = DateTime('now','localtime') WHERE sticker_info_id = ?"
91 #define STICKER_DB_UPDATE_STICKER_DISP_TYPE "UPDATE sticker_info SET display_type = ?, date = DateTime('now','localtime') WHERE sticker_info_id = ?"
92 #define STICKER_DB_UPDATE_RECENT_HISTORY "UPDATE sticker_recent_history_info SET count = count + 1, timestamp = DateTime('now','localtime') WHERE sticker_info_id = ?"
93 #define STICKER_DB_UPDATE_GROUP_IMAGE "UPDATE sticker_group_info SET type = ?, uri = ? WHERE app_id = ? AND group_name = ?"
95 #define STICKER_DB_GET_LATEST_RECORD_ID "SELECT sticker_info_id FROM sticker_info ORDER BY sticker_info_id DESC LIMIT 1"
96 #define STICKER_DB_GET_STICKER_INFO_BY_RECORD_ID "SELECT * FROM sticker_info WHERE sticker_info_id = ?"
97 #define STICKER_DB_GET_KEYWORD_INFO_BY_RECORD_ID "SELECT keyword FROM sticker_keyword_info WHERE sticker_info_id = ?"
98 #define STICKER_DB_GET_IMAGE_INFO_BY_RECORD_ID "SELECT type, uri, thumbnail FROM sticker_info WHERE sticker_info_id = ?"
99 #define STICKER_DB_GET_IMAGE_INFO_BY_URI "SELECT type, thumbnail FROM sticker_info WHERE uri = ?"
100 #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 = ?))"
101 #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 = ?)))"
102 #define STICKER_DB_GET_STICKER_COUNT "SELECT count(*) FROM sticker_info WHERE app_id = ?"
103 #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 = ?)) ORDER BY sticker_info_id DESC LIMIT ?, ?"
104 #define STICKER_DB_GET_RECORD_ID_BY_APP_ID "SELECT sticker_info_id from sticker_info WHERE app_id = ? ORDER BY sticker_info_id DESC LIMIT ?, ?"
105 #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 = ?)) ORDER BY sticker_info_id DESC LIMIT ?, ?"
106 #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 = ?)) ORDER BY sticker_info_id DESC LIMIT ?, ?"
107 #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 = ?)) ORDER BY sticker_info_id DESC LIMIT ?, ?"
108 #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 = ?)) ORDER BY sticker_info_id DESC LIMIT ?, ?"
109 #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 = ?))"
110 #define STICKER_DB_CHECK_FILE_EXISTS "SELECT EXISTS(SELECT 1 FROM sticker_info WHERE uri = ? LIMIT 1)"
111 #define STICKER_DB_CHECK_RECENT_HISTORY_EXISTS "SELECT EXISTS(SELECT 1 FROM sticker_recent_history_info WHERE sticker_info_id = ? LIMIT 1)"
112 #define STICKER_DB_GET_RECENT_HISTORY "SELECT sticker_info_id FROM sticker_recent_history_info ORDER BY datetime(timestamp) DESC LIMIT ?"
113 #define STICKER_DB_GET_STICKER_INFO_BY_URI "SELECT * FROM sticker_info WHERE uri = ?"
114 #define STICKER_DB_CHECK_GROUP_EXISTS_IN_STICKER_INFO "SELECT EXISTS(SELECT 1 FROM sticker_info WHERE group_name = ? AND app_id = ? LIMIT 1)"
115 #define STICKER_DB_CHECK_GROUP_EXISTS_IN_GROUP_INFO "SELECT EXISTS(SELECT 1 FROM sticker_group_info WHERE group_name = ? AND app_id = ? LIMIT 1)"
116 #define STICKER_DB_GET_ALL_GROUP_IMAGE_LIST "SELECT group_name, type, uri FROM sticker_group_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 = ?))"
124 static gboolean is_corrupted = FALSE;
126 static const char *_db_get_query(sticker_info_db_type sticker_type, command_type cmd_type)
128 static const char* query = NULL;
130 if (cmd_type == CMD_UPDATE) {
131 switch(sticker_type) {
132 case STICKER_DB_STICKER_TYPE:
133 query = STICKER_DB_UPDATE_STICKER_TYPE;
135 case STICKER_DB_STICKER_URI:
136 query = STICKER_DB_UPDATE_STICKER_URI;
138 case STICKER_DB_STICKER_THUMBNAIL:
139 query = STICKER_DB_UPDATE_STICKER_THUMBNAIL;
141 case STICKER_DB_STICKER_DESCRIPTION:
142 query = STICKER_DB_UPDATE_STICKER_DESCRIPTION;
144 case STICKER_DB_STICKER_GROUP:
145 query = STICKER_DB_UPDATE_STICKER_GROUP;
147 case STICKER_DB_STICKER_KEYWORD:
148 query = STICKER_DB_DELETE_STICKER_KEYWORD_INFO;
150 case STICKER_DB_STICKER_DISP_TYPE:
151 query = STICKER_DB_UPDATE_STICKER_DISP_TYPE;
157 } else if (cmd_type == CMD_SELECT) {
158 switch(sticker_type) {
159 case STICKER_DB_STICKER_ALL:
160 query = STICKER_DB_GET_ALL_RECORD_ID;
162 case STICKER_DB_STICKER_APPID:
163 query = STICKER_DB_GET_RECORD_ID_BY_APP_ID;
165 case STICKER_DB_STICKER_TYPE:
166 query = STICKER_DB_GET_RECORD_ID_BY_TYPE;
168 case STICKER_DB_STICKER_GROUP:
169 query = STICKER_DB_GET_RECORD_ID_BY_GROUP;
171 case STICKER_DB_STICKER_KEYWORD:
172 query = STICKER_DB_GET_RECORD_ID_BY_KEYWORD;
174 case STICKER_DB_STICKER_DISP_TYPE:
175 query = STICKER_DB_GET_RECORD_ID_BY_DISP_TYPE;
177 case STICKER_DB_STICKER_RECENT_HISTORY:
178 query = STICKER_DB_GET_RECENT_HISTORY;
189 static int _create_db_table(sqlite3 *db)
192 int ret = STICKERD_SERVER_ERROR_NONE;
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;
215 ret = sqlite3_exec(db, STICKER_RECENT_HISTORY_INFO_CREATE_TABLE, NULL, NULL, &err);
216 if (ret != SQLITE_OK) {
217 LOGE("Failed to create sticker_recent_history_info table : %s", err);
218 ret = STICKERD_SERVER_ERROR_DB_FAILED;
222 ret = sqlite3_exec(db, STICKER_GROUP_INFO_CREATE_TABLE, NULL, NULL, &err);
223 if (ret != SQLITE_OK) {
224 LOGE("Failed to create sticker_group_info table : %s", err);
225 ret = STICKERD_SERVER_ERROR_DB_FAILED;
235 static int _recover_db(void)
237 int ret = STICKERD_SERVER_ERROR_NONE;
240 LOGD("Start to recover sticker db");
241 //Remove sticker database file
242 if (unlink(STICKER_DB_PATH) == -1)
243 LOGE("Failed to remove db file");
245 ret = sqlite3_open_v2(STICKER_DB_PATH, &db, SQLITE_OPEN_CREATE | SQLITE_OPEN_READWRITE, NULL);
246 if (ret != SQLITE_OK) {
247 LOGE("Failed to open db : %s", sqlite3_errmsg(db));
248 if (unlink(STICKER_DB_PATH) == -1)
249 LOGE("Failed to remove db file");
250 ret = STICKERD_SERVER_ERROR_DB_FAILED;
254 ret = _create_db_table(db);
255 if (ret != SQLITE_OK)
258 is_corrupted = FALSE;
267 static int _integrity_check_cb(void *pid, int argc, char **argv, char **notUsed)
269 if (strcmp(argv[0], "ok") != 0) {
270 LOGE("DB integrity check failed : %s", argv[0]);
275 LOGD("Result integrity : %s", argv[0]);
279 int stickerd_db_init(void)
281 int ret = STICKERD_SERVER_ERROR_NONE;
285 ret = sqlite3_open_v2(STICKER_DB_PATH, &db, SQLITE_OPEN_CREATE | SQLITE_OPEN_READWRITE, NULL);
286 if (ret != SQLITE_OK) {
287 LOGE("Failed to open db : %s", sqlite3_errmsg(db));
288 ret = STICKERD_SERVER_ERROR_DB_FAILED;
292 ret = _create_db_table(db);
293 if (ret != SQLITE_OK)
296 ret = sqlite3_exec(db, "PRAGMA journal_mode = WAL", NULL, NULL, &err);
297 if (ret != SQLITE_OK) {
298 LOGE("Failed to set journal_mode : %s", err);
299 ret = STICKERD_SERVER_ERROR_DB_FAILED;
303 ret = sqlite3_exec(db, "PRAGMA integrity_check", _integrity_check_cb, NULL, &err);
304 if (ret != SQLITE_OK) {
305 LOGE("Failed to check integrity : %s", err);
306 ret = STICKERD_SERVER_ERROR_DB_FAILED;
316 if (ret == SQLITE_CORRUPT || ret == SQLITE_NOTADB || is_corrupted)
322 static sqlite3 *_db_open(void)
328 if (is_corrupted && _recover_db() != SQLITE_OK)
331 ret = sqlite3_open(STICKER_DB_PATH, &db);
332 if (ret != SQLITE_OK) {
333 LOGE("Failed to open db : %s", sqlite3_errmsg(db));
337 ret = sqlite3_exec(db, "PRAGMA foreign_keys = ON", NULL, NULL, &err);
338 if (ret != SQLITE_OK) {
339 LOGE("Failed to turn on foreign keys : %s", err);
348 static gboolean _check_group_exists(char *app_id, char *group)
351 gboolean result = FALSE;
353 sqlite3_stmt *stmt = NULL;
359 ret = sqlite3_prepare_v2(db, STICKER_DB_CHECK_GROUP_EXISTS_IN_GROUP_INFO, -1, &stmt, NULL);
360 if (ret != SQLITE_OK) {
361 LOGE("fail to check group exists : %s", sqlite3_errmsg(db));
365 sqlite3_bind_text(stmt, 1, group, -1, SQLITE_TRANSIENT);
366 sqlite3_bind_text(stmt, 2, app_id, -1, SQLITE_TRANSIENT);
368 ret = sqlite3_step(stmt);
369 if (ret == SQLITE_ERROR) {
370 LOGE("sqlite3_step() failed : ret(%d)", ret);
374 result = sqlite3_column_int(stmt, 0);
376 sqlite3_finalize(stmt);
382 sqlite3_finalize(stmt);
388 int stickerd_db_insert_sticker_info(int *record_id, sticker_info_db *sticker_info)
392 sqlite3_stmt *stmt = NULL;
396 return STICKERD_SERVER_ERROR_DB_FAILED;
398 ret = sqlite3_prepare_v2(db, STICKER_DB_INSERT_STICKER_INFO, -1, &stmt, NULL);
399 if (ret != SQLITE_OK) {
400 LOGE("fail to insert sticker information : %s", sqlite3_errmsg(db));
404 sqlite3_bind_text(stmt, 1, sticker_info->app_id, -1, SQLITE_TRANSIENT);
405 sqlite3_bind_int(stmt, 2, sticker_info->type);
406 sqlite3_bind_text(stmt, 3, sticker_info->uri, -1, SQLITE_TRANSIENT);
407 sqlite3_bind_text(stmt, 4, sticker_info->thumbnail, -1, SQLITE_TRANSIENT);
408 sqlite3_bind_text(stmt, 5, sticker_info->description, -1, SQLITE_TRANSIENT);
409 sqlite3_bind_text(stmt, 6, sticker_info->group, -1, SQLITE_TRANSIENT);
410 sqlite3_bind_int(stmt, 7, sticker_info->display_type);
412 ret = sqlite3_step(stmt);
413 if (ret != SQLITE_OK && ret != SQLITE_DONE) {
414 LOGE("sqlite3_step() failed : ret(%d)", ret);
416 } else if (sqlite3_changes(db) == 0) {
417 LOGE("No changes to DB");
421 sqlite3_finalize(stmt);
424 ret = sqlite3_prepare_v2(db, STICKER_DB_GET_LATEST_RECORD_ID, -1, &stmt, NULL);
425 if (ret != SQLITE_OK) {
426 LOGE("fail to get sticker id : %s", sqlite3_errmsg(db));
430 ret = sqlite3_step(stmt);
431 if (ret == SQLITE_ERROR) {
432 LOGE("sqlite3_step() failed : ret(%d)", ret);
436 *record_id = sqlite3_column_int(stmt, 0);
437 LOGD("record_id : %d", *record_id);
441 for(list = sticker_info->keyword; list != NULL; list=list->next) {
442 sqlite3_finalize(stmt);
445 ret = sqlite3_prepare_v2(db, STICKER_DB_INSERT_STICKER_KEYWORD_INFO, -1, &stmt, NULL);
446 if (ret != SQLITE_OK) {
447 LOGE("fail to insert sticker keyword : %s", sqlite3_errmsg(db));
451 sqlite3_bind_int(stmt, 1, *record_id);
452 sqlite3_bind_text(stmt, 2, (char *)list->data, -1, SQLITE_TRANSIENT);
454 ret = sqlite3_step(stmt);
455 if (ret != SQLITE_OK && ret != SQLITE_DONE) {
456 LOGE("sqlite3_step() failed : ret(%d)", ret);
458 } else if (sqlite3_changes(db) == 0) {
459 LOGE("No changes to DB");
464 sqlite3_finalize(stmt);
467 return STICKERD_SERVER_ERROR_NONE;
470 sqlite3_finalize(stmt);
473 return STICKERD_SERVER_ERROR_DB_FAILED;
476 int stickerd_db_delete_sticker_info(int record_id)
481 sqlite3_stmt *stmt = NULL;
482 const unsigned char *uri = NULL;
483 const unsigned char *thumbnail = NULL;
487 return STICKERD_SERVER_ERROR_DB_FAILED;
489 ret = sqlite3_prepare_v2(db, STICKER_DB_GET_IMAGE_INFO_BY_RECORD_ID, -1, &stmt, NULL);
490 if (ret != SQLITE_OK) {
491 LOGE("fail to get image files : %s", sqlite3_errmsg(db));
495 sqlite3_bind_int(stmt, 1, record_id);
497 ret = sqlite3_step(stmt);
498 if (ret == SQLITE_ERROR) {
499 LOGE("sqlite3_step() failed : ret(%d)", ret);
503 uri_type = sqlite3_column_int(stmt, 0);
504 uri = sqlite3_column_text(stmt, 1);
505 thumbnail = sqlite3_column_text(stmt, 2);
507 if (uri_type == 1 && uri && unlink((const char *)uri) == -1)
508 LOGE("fail to delete sticker file");
510 if (thumbnail && unlink((const char *)thumbnail) == -1)
511 LOGE("fail to delete thumbnail image");
513 sqlite3_finalize(stmt);
516 ret = sqlite3_prepare_v2(db, STICKER_DB_DELETE_STICKER_INFO, -1, &stmt, NULL);
517 if (ret != SQLITE_OK) {
518 LOGE("fail to delete sticker information : %s", sqlite3_errmsg(db));
522 sqlite3_bind_int(stmt, 1, record_id);
524 ret = sqlite3_step(stmt);
525 if (ret != SQLITE_OK && ret != SQLITE_DONE) {
526 LOGE("sqlite3_step() failed : ret(%d)", ret);
528 } else if (sqlite3_changes(db) == 0) {
529 LOGE("No changes to DB");
533 sqlite3_finalize(stmt);
536 return STICKERD_SERVER_ERROR_NONE;
539 sqlite3_finalize(stmt);
542 return STICKERD_SERVER_ERROR_DB_FAILED;
545 int stickerd_db_delete_sticker_info_by_uri(char *uri)
549 sqlite3_stmt *stmt = NULL;
552 const unsigned char *thumbnail = NULL;
556 return STICKERD_SERVER_ERROR_DB_FAILED;
558 ret = sqlite3_prepare_v2(db, STICKER_DB_GET_IMAGE_INFO_BY_URI, -1, &stmt, NULL);
559 if (ret != SQLITE_OK) {
560 LOGE("fail to delete sticker information : %s", sqlite3_errmsg(db));
564 sqlite3_bind_text(stmt, 1, uri, -1, SQLITE_TRANSIENT);
566 ret = sqlite3_step(stmt);
567 if (ret == SQLITE_ERROR) {
568 LOGE("sqlite3_step() failed : ret(%d)", ret);
572 uri_type = sqlite3_column_int(stmt, 0);
573 thumbnail = sqlite3_column_text(stmt, 1);
575 if (uri_type == 1 && unlink((const char *)uri) == -1)
576 LOGE("fail to delete sticker file");
578 if (thumbnail && unlink((const char *)thumbnail) == -1)
579 LOGE("fail to delete thumbnail image");
581 sqlite3_finalize(stmt);
584 ret = sqlite3_prepare_v2(db, STICKER_DB_DELETE_STICKER_INFO_BY_URI, -1, &stmt, NULL);
585 if (ret != SQLITE_OK) {
586 LOGE("fail to delete sticker information : %s", sqlite3_errmsg(db));
590 sqlite3_bind_text(stmt, 1, uri, -1, SQLITE_TRANSIENT);
592 ret = sqlite3_step(stmt);
593 if (ret != SQLITE_OK && ret != SQLITE_DONE) {
594 LOGE("sqlite3_step() failed : ret(%d)", ret);
596 } else if (sqlite3_changes(db) == 0) {
597 LOGE("No changes to DB");
601 sqlite3_finalize(stmt);
604 return STICKERD_SERVER_ERROR_NONE;
607 sqlite3_finalize(stmt);
610 return STICKERD_SERVER_ERROR_DB_FAILED;
613 int stickerd_db_update_sticker_info(int record_id, sticker_info_db_type type, void *data)
617 sqlite3_stmt *stmt = NULL;
621 return STICKERD_SERVER_ERROR_DB_FAILED;
623 const char* query = _db_get_query(type, CMD_UPDATE);
624 ret = sqlite3_prepare_v2(db, query, -1, &stmt, NULL);
625 if (ret != SQLITE_OK) {
626 LOGE("fail to update sticker information : %s", sqlite3_errmsg(db));
630 if (type == STICKER_DB_STICKER_TYPE || type == STICKER_DB_STICKER_DISP_TYPE) {
631 sqlite3_bind_int(stmt, 1, *(int *)data);
632 } else if (type == STICKER_DB_STICKER_KEYWORD) {
633 sqlite3_bind_int(stmt, 1, record_id);
635 sqlite3_bind_text(stmt, 1, (char *)data, -1, SQLITE_TRANSIENT);
638 if (type != STICKER_DB_STICKER_KEYWORD)
639 sqlite3_bind_int(stmt, 2, record_id);
641 ret = sqlite3_step(stmt);
642 if (ret != SQLITE_OK && ret != SQLITE_DONE) {
643 LOGE("sqlite3_step() failed : ret(%d)", ret);
647 if (type == STICKER_DB_STICKER_KEYWORD) {
649 for(list = (GList *)data; list != NULL; list=list->next) {
650 sqlite3_finalize(stmt);
653 ret = sqlite3_prepare_v2(db, STICKER_DB_INSERT_STICKER_KEYWORD_INFO, -1, &stmt, NULL);
654 if (ret != SQLITE_OK) {
655 LOGE("fail to insert sticker information : %s", sqlite3_errmsg(db));
659 sqlite3_bind_int(stmt, 1, record_id);
660 sqlite3_bind_text(stmt, 2, (char *)list->data, -1, SQLITE_TRANSIENT);
662 ret = sqlite3_step(stmt);
663 if (ret != SQLITE_OK && ret != SQLITE_DONE) {
664 LOGE("sqlite3_step() failed : ret(%d)", ret);
666 } else if (sqlite3_changes(db) == 0) {
667 LOGE("No changes to DB");
673 sqlite3_finalize(stmt);
676 return STICKERD_SERVER_ERROR_NONE;
679 sqlite3_finalize(stmt);
682 return STICKERD_SERVER_ERROR_DB_FAILED;
685 int stickerd_db_get_sticker_info_by_record_id(int record_id, sticker_info_db *sticker_info)
689 sqlite3_stmt *stmt = NULL;
691 const unsigned char *tmp_app_id = NULL;
692 const unsigned char *tmp_uri = NULL;
693 const unsigned char *tmp_thumbnail = NULL;
694 const unsigned char *tmp_description = NULL;
695 const unsigned char *tmp_group = NULL;
696 const unsigned char *tmp_date = NULL;
700 return STICKERD_SERVER_ERROR_DB_FAILED;
702 ret = sqlite3_prepare_v2(db, STICKER_DB_GET_STICKER_INFO_BY_RECORD_ID, -1, &stmt, NULL);
703 if (ret != SQLITE_OK) {
704 LOGE("fail to get sticker information : %s", sqlite3_errmsg(db));
708 sqlite3_bind_int(stmt, 1, record_id);
710 ret = sqlite3_step(stmt);
711 if (ret == SQLITE_ERROR) {
712 LOGE("sqlite3_step() failed : ret(%d)", ret);
716 tmp_app_id = sqlite3_column_text(stmt, 1);
718 sticker_info->app_id = strdup((const char *)tmp_app_id);
720 LOGW("invalid record_id : %d", record_id);
724 sticker_info->type = sqlite3_column_int(stmt, 2);
726 tmp_uri = sqlite3_column_text(stmt, 3);
728 sticker_info->uri = strdup((const char *)tmp_uri);
730 tmp_thumbnail = sqlite3_column_text(stmt, 4);
732 sticker_info->thumbnail = strdup((const char *)tmp_thumbnail);
734 tmp_description = sqlite3_column_text(stmt, 5);
736 sticker_info->description = strdup((const char *)tmp_description);
738 tmp_group = sqlite3_column_text(stmt, 6);
740 sticker_info->group = strdup((const char *)tmp_group);
742 tmp_date = sqlite3_column_text(stmt, 7);
744 sticker_info->date = strdup((const char *)tmp_date);
746 sticker_info->display_type = sqlite3_column_int(stmt, 8);
748 sqlite3_finalize(stmt);
751 ret = sqlite3_prepare_v2(db, STICKER_DB_GET_KEYWORD_INFO_BY_RECORD_ID, -1, &stmt, NULL);
752 if (ret != SQLITE_OK) {
753 LOGE("fail to get sticker keyword : %s", sqlite3_errmsg(db));
757 sqlite3_bind_int(stmt, 1, record_id);
759 while (sqlite3_step(stmt) == SQLITE_ROW) {
760 const unsigned char *keyword = sqlite3_column_text(stmt, 0);
762 sticker_info->keyword = g_list_append(sticker_info->keyword, strdup((const char *)keyword));
765 sqlite3_finalize(stmt);
768 return STICKERD_SERVER_ERROR_NONE;
771 sqlite3_finalize(stmt);
774 return STICKERD_SERVER_ERROR_DB_FAILED;
777 int stickerd_db_get_group_list(GVariantBuilder *builder, char *app_id)
781 sqlite3_stmt *stmt = NULL;
785 return STICKERD_SERVER_ERROR_DB_FAILED;
787 ret = sqlite3_prepare_v2(db, STICKER_DB_GET_ALL_GROUP_LIST, -1, &stmt, NULL);
788 if (ret != SQLITE_OK) {
789 LOGE("fail to get group list : %s", sqlite3_errmsg(db));
793 sqlite3_bind_text(stmt, 1, app_id, -1, SQLITE_TRANSIENT);
795 while (sqlite3_step(stmt) == SQLITE_ROW) {
796 const unsigned char *group = sqlite3_column_text(stmt, 0);
798 g_variant_builder_add(builder, "(s)", strdup((const char *)group));
801 sqlite3_finalize(stmt);
804 return STICKERD_SERVER_ERROR_NONE;
807 sqlite3_finalize(stmt);
810 return STICKERD_SERVER_ERROR_DB_FAILED;
813 int stickerd_db_get_keyword_list(GVariantBuilder *builder, char *app_id)
817 sqlite3_stmt *stmt = NULL;
821 return STICKERD_SERVER_ERROR_DB_FAILED;
823 ret = sqlite3_prepare_v2(db, STICKER_DB_GET_ALL_KEYWORD_LIST, -1, &stmt, NULL);
824 if (ret != SQLITE_OK) {
825 LOGE("fail to get keyword list : %s", sqlite3_errmsg(db));
829 sqlite3_bind_text(stmt, 1, app_id, -1, SQLITE_TRANSIENT);
831 while (sqlite3_step(stmt) == SQLITE_ROW) {
832 const unsigned char *keyword = sqlite3_column_text(stmt, 0);
834 g_variant_builder_add(builder, "(s)", strdup((const char *)keyword));
837 sqlite3_finalize(stmt);
840 return STICKERD_SERVER_ERROR_NONE;
843 sqlite3_finalize(stmt);
846 return STICKERD_SERVER_ERROR_DB_FAILED;
849 int stickerd_db_get_sticker_count(int *count, char *app_id)
853 sqlite3_stmt *stmt = NULL;
857 return STICKERD_SERVER_ERROR_DB_FAILED;
859 ret = sqlite3_prepare_v2(db, STICKER_DB_GET_STICKER_COUNT, -1, &stmt, NULL);
860 if (ret != SQLITE_OK) {
861 LOGE("fail to get sticker count : %s", sqlite3_errmsg(db));
865 sqlite3_bind_text(stmt, 1, app_id, -1, SQLITE_TRANSIENT);
867 ret = sqlite3_step(stmt);
868 if (ret == SQLITE_ERROR) {
869 LOGE("sqlite3_step() failed : ret(%d)", ret);
873 *count = sqlite3_column_int(stmt, 0);
875 sqlite3_finalize(stmt);
878 return STICKERD_SERVER_ERROR_NONE;
881 sqlite3_finalize(stmt);
884 return STICKERD_SERVER_ERROR_DB_FAILED;
887 int stickerd_db_get_record_id(sticker_info_db_type type, GList **id_list, void *data, char *app_id, int offset, int count)
891 sqlite3_stmt *stmt = NULL;
895 return STICKERD_SERVER_ERROR_DB_FAILED;
897 const char* query = _db_get_query(type, CMD_SELECT);
898 ret = sqlite3_prepare_v2(db, query, -1, &stmt, NULL);
899 if (ret != SQLITE_OK) {
900 LOGE("fail to get record id : %s", sqlite3_errmsg(db));
904 if (type == STICKER_DB_STICKER_ALL || type == STICKER_DB_STICKER_APPID) {
905 sqlite3_bind_text(stmt, 1, app_id, -1, SQLITE_TRANSIENT);
906 sqlite3_bind_int(stmt, 2, offset);
907 sqlite3_bind_int(stmt, 3, count);
908 } else if (type == STICKER_DB_STICKER_RECENT_HISTORY) {
909 sqlite3_bind_int(stmt, 1, count);
911 if (type == STICKER_DB_STICKER_TYPE || type == STICKER_DB_STICKER_DISP_TYPE)
912 sqlite3_bind_int(stmt, 1, *(int *)data);
914 sqlite3_bind_text(stmt, 1, (char *)data, -1, SQLITE_TRANSIENT);
916 sqlite3_bind_text(stmt, 2, app_id, -1, SQLITE_TRANSIENT);
917 sqlite3_bind_int(stmt, 3, offset);
918 sqlite3_bind_int(stmt, 4, count);
921 while (sqlite3_step(stmt) == SQLITE_ROW) {
922 const unsigned char *tmp_id = sqlite3_column_text(stmt, 0);
924 *id_list = g_list_append(*id_list, strdup((const char *)tmp_id));
927 sqlite3_finalize(stmt);
930 return STICKERD_SERVER_ERROR_NONE;
933 sqlite3_finalize(stmt);
936 return STICKERD_SERVER_ERROR_DB_FAILED;
939 int stickerd_db_get_group_list_by_display_type(GVariantBuilder *builder, char *app_id, int disp_type)
943 sqlite3_stmt *stmt = NULL;
947 return STICKERD_SERVER_ERROR_DB_FAILED;
949 ret = sqlite3_prepare_v2(db, STICKER_DB_GET_GROUP_LIST_BY_DISP_TYPE, -1, &stmt, NULL);
950 if (ret != SQLITE_OK) {
951 LOGE("fail to get group list : %s", sqlite3_errmsg(db));
955 sqlite3_bind_int(stmt, 1, disp_type);
956 sqlite3_bind_text(stmt, 2, app_id, -1, SQLITE_TRANSIENT);
958 while (sqlite3_step(stmt) == SQLITE_ROW) {
959 const unsigned char *group = sqlite3_column_text(stmt, 0);
961 g_variant_builder_add(builder, "(s)", strdup((const char *)group));
964 sqlite3_finalize(stmt);
967 return STICKERD_SERVER_ERROR_NONE;
970 sqlite3_finalize(stmt);
973 return STICKERD_SERVER_ERROR_DB_FAILED;
976 int stickerd_db_check_file_exists(int *result, char *uri)
980 sqlite3_stmt *stmt = NULL;
984 return STICKERD_SERVER_ERROR_DB_FAILED;
986 ret = sqlite3_prepare_v2(db, STICKER_DB_CHECK_FILE_EXISTS, -1, &stmt, NULL);
987 if (ret != SQLITE_OK) {
988 LOGE("fail to check file exists : %s", sqlite3_errmsg(db));
992 sqlite3_bind_text(stmt, 1, uri, -1, SQLITE_TRANSIENT);
994 ret = sqlite3_step(stmt);
995 if (ret == SQLITE_ERROR) {
996 LOGE("sqlite3_step() failed : ret(%d)", ret);
1000 *result = sqlite3_column_int(stmt, 0);
1002 sqlite3_finalize(stmt);
1005 return STICKERD_SERVER_ERROR_NONE;
1008 sqlite3_finalize(stmt);
1011 return STICKERD_SERVER_ERROR_DB_FAILED;
1014 int stickerd_db_insert_recent_sticker_info(int record_id)
1018 sqlite3_stmt *stmt = NULL;
1023 return STICKERD_SERVER_ERROR_DB_FAILED;
1025 ret = sqlite3_prepare_v2(db, STICKER_DB_CHECK_RECENT_HISTORY_EXISTS, -1, &stmt, NULL);
1026 if (ret != SQLITE_OK) {
1027 LOGE("fail to check recent sticker exists : %s", sqlite3_errmsg(db));
1031 sqlite3_bind_int(stmt, 1, record_id);
1033 ret = sqlite3_step(stmt);
1034 if (ret == SQLITE_ERROR) {
1035 LOGE("sqlite3_step() failed : ret(%d)", ret);
1039 result = sqlite3_column_int(stmt, 0);
1041 sqlite3_finalize(stmt);
1045 ret = sqlite3_prepare_v2(db, STICKER_DB_UPDATE_RECENT_HISTORY, -1, &stmt, NULL);
1047 ret = sqlite3_prepare_v2(db, STICKER_DB_INSERT_RECENT_HISTORY, -1, &stmt, NULL);
1049 if (ret != SQLITE_OK) {
1050 LOGE("fail to update recent history : %s", sqlite3_errmsg(db));
1054 sqlite3_bind_int(stmt, 1, record_id);
1056 ret = sqlite3_step(stmt);
1057 if (ret != SQLITE_OK && ret != SQLITE_DONE) {
1058 LOGE("sqlite3_step() failed : ret(%d)", ret);
1060 } else if (sqlite3_changes(db) == 0) {
1061 LOGE("No changes to DB");
1065 sqlite3_finalize(stmt);
1068 return STICKERD_SERVER_ERROR_NONE;
1071 sqlite3_finalize(stmt);
1074 return STICKERD_SERVER_ERROR_DB_FAILED;
1077 int stickerd_db_get_sticker_info_by_uri(char *uri, sticker_info_db *sticker_info)
1081 sqlite3_stmt *stmt = NULL;
1083 const unsigned char *tmp_app_id = NULL;
1084 const unsigned char *tmp_uri = NULL;
1085 const unsigned char *tmp_thumbnail = NULL;
1086 const unsigned char *tmp_description = NULL;
1087 const unsigned char *tmp_group = NULL;
1088 const unsigned char *tmp_date = NULL;
1092 return STICKERD_SERVER_ERROR_DB_FAILED;
1094 ret = sqlite3_prepare_v2(db, STICKER_DB_GET_STICKER_INFO_BY_URI, -1, &stmt, NULL);
1095 if (ret != SQLITE_OK) {
1096 LOGE("fail to get sticker information : %s", sqlite3_errmsg(db));
1100 sqlite3_bind_text(stmt, 1, uri, -1, SQLITE_TRANSIENT);
1102 ret = sqlite3_step(stmt);
1103 if (ret == SQLITE_ERROR) {
1104 LOGE("sqlite3_step() failed : ret(%d)", ret);
1108 sticker_info->record_id = sqlite3_column_int(stmt, 0);
1110 tmp_app_id = sqlite3_column_text(stmt, 1);
1112 sticker_info->app_id = strdup((const char *)tmp_app_id);
1114 sticker_info->type = sqlite3_column_int(stmt, 2);
1116 tmp_uri = sqlite3_column_text(stmt, 3);
1118 sticker_info->uri = strdup((const char *)tmp_uri);
1120 tmp_thumbnail = sqlite3_column_text(stmt, 4);
1122 sticker_info->thumbnail = strdup((const char *)tmp_thumbnail);
1124 tmp_description = sqlite3_column_text(stmt, 5);
1125 if (tmp_description)
1126 sticker_info->description = strdup((const char *)tmp_description);
1128 tmp_group = sqlite3_column_text(stmt, 6);
1130 sticker_info->group = strdup((const char *)tmp_group);
1132 tmp_date = sqlite3_column_text(stmt, 7);
1134 sticker_info->date = strdup((const char *)tmp_date);
1136 sticker_info->display_type = sqlite3_column_int(stmt, 8);
1138 sqlite3_finalize(stmt);
1141 ret = sqlite3_prepare_v2(db, STICKER_DB_GET_KEYWORD_INFO_BY_RECORD_ID, -1, &stmt, NULL);
1142 if (ret != SQLITE_OK) {
1143 LOGE("fail to get sticker keyword : %s", sqlite3_errmsg(db));
1147 sqlite3_bind_int(stmt, 1, sticker_info->record_id);
1149 while (sqlite3_step(stmt) == SQLITE_ROW) {
1150 const unsigned char *keyword = sqlite3_column_text(stmt, 0);
1152 sticker_info->keyword = g_list_append(sticker_info->keyword, strdup((const char *)keyword));
1155 sqlite3_finalize(stmt);
1158 return STICKERD_SERVER_ERROR_NONE;
1161 sqlite3_finalize(stmt);
1164 return STICKERD_SERVER_ERROR_DB_FAILED;
1167 int stickerd_db_check_group_exists(int *result, char *app_id, char *group)
1171 sqlite3_stmt *stmt = NULL;
1175 return STICKERD_SERVER_ERROR_DB_FAILED;
1177 ret = sqlite3_prepare_v2(db, STICKER_DB_CHECK_GROUP_EXISTS_IN_STICKER_INFO, -1, &stmt, NULL);
1178 if (ret != SQLITE_OK) {
1179 LOGE("fail to check group exists : %s", sqlite3_errmsg(db));
1183 sqlite3_bind_text(stmt, 1, group, -1, SQLITE_TRANSIENT);
1184 sqlite3_bind_text(stmt, 2, app_id, -1, SQLITE_TRANSIENT);
1186 ret = sqlite3_step(stmt);
1187 if (ret == SQLITE_ERROR) {
1188 LOGE("sqlite3_step() failed : ret(%d)", ret);
1192 *result = sqlite3_column_int(stmt, 0);
1194 sqlite3_finalize(stmt);
1197 return STICKERD_SERVER_ERROR_NONE;
1200 sqlite3_finalize(stmt);
1203 return STICKERD_SERVER_ERROR_DB_FAILED;
1206 int stickerd_db_set_group_image(char *app_id, char *group, int type, char *uri)
1209 gboolean is_exist = FALSE;
1211 sqlite3_stmt *stmt = NULL;
1215 return STICKERD_SERVER_ERROR_DB_FAILED;
1217 is_exist = _check_group_exists(app_id, group);
1218 const char* query = is_exist ? STICKER_DB_UPDATE_GROUP_IMAGE : STICKER_DB_INSERT_GROUP_IMAGE;
1219 ret = sqlite3_prepare_v2(db, query, -1, &stmt, NULL);
1220 if (ret != SQLITE_OK) {
1221 LOGE("fail to set group image : %s", sqlite3_errmsg(db));
1226 sqlite3_bind_int(stmt, 1, type);
1227 sqlite3_bind_text(stmt, 2, uri, -1, SQLITE_TRANSIENT);
1228 sqlite3_bind_text(stmt, 3, app_id, -1, SQLITE_TRANSIENT);
1229 sqlite3_bind_text(stmt, 4, group, -1, SQLITE_TRANSIENT);
1231 sqlite3_bind_text(stmt, 1, app_id, -1, SQLITE_TRANSIENT);
1232 sqlite3_bind_text(stmt, 2, group, -1, SQLITE_TRANSIENT);
1233 sqlite3_bind_int(stmt, 3, type);
1234 sqlite3_bind_text(stmt, 4, uri, -1, SQLITE_TRANSIENT);
1237 ret = sqlite3_step(stmt);
1238 if (ret == SQLITE_ERROR) {
1239 LOGE("sqlite3_step() failed : ret(%d)", ret);
1243 sqlite3_finalize(stmt);
1246 return STICKERD_SERVER_ERROR_NONE;
1249 sqlite3_finalize(stmt);
1252 return STICKERD_SERVER_ERROR_DB_FAILED;
1255 int stickerd_db_get_group_image_list(GVariantBuilder *builder, char *app_id)
1259 sqlite3_stmt *stmt = NULL;
1263 return STICKERD_SERVER_ERROR_DB_FAILED;
1265 ret = sqlite3_prepare_v2(db, STICKER_DB_GET_ALL_GROUP_IMAGE_LIST, -1, &stmt, NULL);
1266 if (ret != SQLITE_OK) {
1267 LOGE("fail to get group image list : %s", sqlite3_errmsg(db));
1271 sqlite3_bind_text(stmt, 1, app_id, -1, SQLITE_TRANSIENT);
1273 while (sqlite3_step(stmt) == SQLITE_ROW) {
1274 const unsigned char *group = sqlite3_column_text(stmt, 0);
1275 int uri_type = sqlite3_column_int(stmt, 1);
1276 const unsigned char *uri = sqlite3_column_text(stmt, 2);
1278 g_variant_builder_add(builder, "(sis)", strdup((const char *)group), uri_type, strdup((const char *)uri));
1281 sqlite3_finalize(stmt);
1284 return STICKERD_SERVER_ERROR_NONE;
1287 sqlite3_finalize(stmt);
1290 return STICKERD_SERVER_ERROR_DB_FAILED;