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 * +------------+---------------+-------------+
56 #define STICKER_DB_PATH tzplatform_mkpath(TZ_SYS_DB, ".sticker_info.db")
57 #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)"
58 #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)"
59 #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)"
61 #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'), ?)"
62 #define STICKER_DB_INSERT_STICKER_KEYWORD_INFO "INSERT INTO sticker_keyword_info (sticker_info_id, keyword) VALUES (?, ?)"
64 #define STICKER_DB_DELETE_STICKER_INFO "DELETE FROM sticker_info WHERE sticker_info_id = ?"
65 #define STICKER_DB_DELETE_STICKER_KEYWORD_INFO "DELETE FROM sticker_keyword_info WHERE sticker_info_id = ?"
66 #define STICKER_DB_DELETE_STICKER_INFO_BY_URI "DELETE FROM sticker_info WHERE uri = ?"
68 #define STICKER_DB_UPDATE_STICKER_TYPE "UPDATE sticker_info SET type = ?, date = DateTime('now','localtime') WHERE sticker_info_id = ?"
69 #define STICKER_DB_UPDATE_STICKER_URI "UPDATE sticker_info SET uri = ?, date = DateTime('now','localtime') WHERE sticker_info_id = ?"
70 #define STICKER_DB_UPDATE_STICKER_THUMBNAIL "UPDATE sticker_info SET thumbnail = ?, date = DateTime('now','localtime') WHERE sticker_info_id = ?"
71 #define STICKER_DB_UPDATE_STICKER_DESCRIPTION "UPDATE sticker_info SET description = ?, date = DateTime('now','localtime') WHERE sticker_info_id = ?"
72 #define STICKER_DB_UPDATE_STICKER_GROUP "UPDATE sticker_info SET group_name = ?, date = DateTime('now','localtime') WHERE sticker_info_id = ?"
73 #define STICKER_DB_UPDATE_STICKER_DISP_TYPE "UPDATE sticker_info SET display_type = ?, date = DateTime('now','localtime') WHERE sticker_info_id = ?"
75 #define STICKER_DB_GET_LATEST_RECORD_ID "SELECT sticker_info_id FROM sticker_info ORDER BY sticker_info_id DESC LIMIT 1"
76 #define STICKER_DB_GET_STICKER_INFO_BY_RECORD_ID "SELECT * FROM sticker_info WHERE sticker_info_id = ?"
77 #define STICKER_DB_GET_KEYWORD_INFO_BY_RECORD_ID "SELECT keyword FROM sticker_keyword_info WHERE sticker_info_id = ?"
78 #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 = ?))"
79 #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 = ?)))"
80 #define STICKER_DB_GET_STICKER_COUNT "SELECT count(*) FROM sticker_info WHERE app_id = ?"
81 #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 ?, ?"
82 #define STICKER_DB_GET_RECORD_ID_BY_APP_ID "SELECT sticker_info_id from sticker_info WHERE app_id = ? LIMIT ?, ?"
83 #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 ?, ?"
84 #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 ?, ?"
85 #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 ?, ?"
86 #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 ?, ?"
87 #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 = ?))"
88 #define STICKER_DB_CHECK_FILE_EXISTS "SELECT EXISTS(SELECT 1 FROM sticker_info WHERE uri = ? LIMIT 1)"
96 static gboolean is_corrupted = FALSE;
98 static const char *_db_get_query(sticker_info_db_type sticker_type, command_type cmd_type)
100 static const char* query = NULL;
102 if (cmd_type == CMD_UPDATE) {
103 switch(sticker_type) {
104 case STICKER_DB_STICKER_TYPE:
105 query = STICKER_DB_UPDATE_STICKER_TYPE;
107 case STICKER_DB_STICKER_URI:
108 query = STICKER_DB_UPDATE_STICKER_URI;
110 case STICKER_DB_STICKER_THUMBNAIL:
111 query = STICKER_DB_UPDATE_STICKER_THUMBNAIL;
113 case STICKER_DB_STICKER_DESCRIPTION:
114 query = STICKER_DB_UPDATE_STICKER_DESCRIPTION;
116 case STICKER_DB_STICKER_GROUP:
117 query = STICKER_DB_UPDATE_STICKER_GROUP;
119 case STICKER_DB_STICKER_KEYWORD:
120 query = STICKER_DB_DELETE_STICKER_KEYWORD_INFO;
122 case STICKER_DB_STICKER_DISP_TYPE:
123 query = STICKER_DB_UPDATE_STICKER_DISP_TYPE;
129 } else if (cmd_type == CMD_SELECT) {
130 switch(sticker_type) {
131 case STICKER_DB_STICKER_ALL:
132 query = STICKER_DB_GET_ALL_RECORD_ID;
134 case STICKER_DB_STICKER_APPID:
135 query = STICKER_DB_GET_RECORD_ID_BY_APP_ID;
137 case STICKER_DB_STICKER_TYPE:
138 query = STICKER_DB_GET_RECORD_ID_BY_TYPE;
140 case STICKER_DB_STICKER_GROUP:
141 query = STICKER_DB_GET_RECORD_ID_BY_GROUP;
143 case STICKER_DB_STICKER_KEYWORD:
144 query = STICKER_DB_GET_RECORD_ID_BY_KEYWORD;
146 case STICKER_DB_STICKER_DISP_TYPE:
147 query = STICKER_DB_GET_RECORD_ID_BY_DISP_TYPE;
158 static int _recover_db(void)
160 int ret = STICKERD_SERVER_ERROR_NONE;
164 LOGD("Start to recover sticker db");
165 //Remove sticker database file
166 if (unlink(STICKER_DB_PATH) == -1)
167 LOGE("Failed to remove db file");
169 ret = sqlite3_open_v2(STICKER_DB_PATH, &db, SQLITE_OPEN_CREATE | SQLITE_OPEN_READWRITE, NULL);
170 if (ret != SQLITE_OK) {
171 LOGE("Failed to open db : %s", sqlite3_errmsg(db));
172 if (unlink(STICKER_DB_PATH) == -1)
173 LOGE("Failed to remove db file");
174 ret = STICKERD_SERVER_ERROR_DB_FAILED;
178 ret = sqlite3_exec(db, STICKER_INFO_CREATE_TABLE, NULL, NULL, &err);
179 if (ret != SQLITE_OK) {
180 LOGE("Failed to create sticker_info table : %s" , err);
181 ret = STICKERD_SERVER_ERROR_DB_FAILED;
185 ret = sqlite3_exec(db, STICKER_KEYWORD_INFO_CREATE_TABLE, NULL, NULL, &err);
186 if (ret != SQLITE_OK) {
187 LOGE("Failed to create sticker_keyword_info table : %s", err);
188 ret = STICKERD_SERVER_ERROR_DB_FAILED;
191 ret = sqlite3_exec(db, STICKER_WHITELIST_INFO_CREATE_TABLE, NULL, NULL, &err);
192 if (ret != SQLITE_OK) {
193 LOGE("Failed to create sticker_whitelist_info table : %s", err);
194 ret = STICKERD_SERVER_ERROR_DB_FAILED;
197 is_corrupted = FALSE;
209 static int _integrity_check_cb(void *pid, int argc, char **argv, char **notUsed)
211 if (strcmp(argv[0], "ok") != 0) {
212 LOGE("DB integrity check failed : %s", argv[0]);
217 LOGD("Result integrity : %s", argv[0]);
221 int stickerd_db_init(void)
223 int ret = STICKERD_SERVER_ERROR_NONE;
227 ret = sqlite3_open_v2(STICKER_DB_PATH, &db, SQLITE_OPEN_CREATE | SQLITE_OPEN_READWRITE, NULL);
228 if (ret != SQLITE_OK) {
229 LOGE("Failed to open db : %s", sqlite3_errmsg(db));
230 ret = STICKERD_SERVER_ERROR_DB_FAILED;
234 ret = sqlite3_exec(db, STICKER_INFO_CREATE_TABLE, NULL, NULL, &err);
235 if (ret != SQLITE_OK) {
236 LOGE("Failed to create sticker_info table : %s" , err);
237 ret = STICKERD_SERVER_ERROR_DB_FAILED;
241 ret = sqlite3_exec(db, STICKER_KEYWORD_INFO_CREATE_TABLE, NULL, NULL, &err);
242 if (ret != SQLITE_OK) {
243 LOGE("Failed to create sticker_keyword_info table : %s", err);
244 ret = STICKERD_SERVER_ERROR_DB_FAILED;
248 ret = sqlite3_exec(db, STICKER_WHITELIST_INFO_CREATE_TABLE, NULL, NULL, &err);
249 if (ret != SQLITE_OK) {
250 LOGE("Failed to create sticker_whitelist_info table : %s", err);
251 ret = STICKERD_SERVER_ERROR_DB_FAILED;
254 ret = sqlite3_exec(db, "PRAGMA journal_mode = WAL", NULL, NULL, &err);
255 if (ret != SQLITE_OK) {
256 LOGE("Failed to set journal_mode : %s", err);
257 ret = STICKERD_SERVER_ERROR_DB_FAILED;
261 ret = sqlite3_exec(db, "PRAGMA integrity_check", _integrity_check_cb, NULL, &err);
262 if (ret != SQLITE_OK) {
263 LOGE("Failed to check integrity : %s", err);
264 ret = STICKERD_SERVER_ERROR_DB_FAILED;
274 if (ret == SQLITE_CORRUPT || ret == SQLITE_NOTADB || is_corrupted)
280 static sqlite3 *_db_open(void)
286 ret = sqlite3_open(STICKER_DB_PATH, &db);
287 if (ret != SQLITE_OK) {
288 LOGE("Failed to open db : %s", sqlite3_errmsg(db));
292 ret = sqlite3_exec(db, "PRAGMA foreign_keys = ON", NULL, NULL, &err);
293 if (ret != SQLITE_OK) {
294 LOGE("Failed to turn on foreign keys : %s", err);
303 int stickerd_db_insert_sticker_info(int *record_id, sticker_info_db *sticker_info)
307 sqlite3_stmt *stmt = NULL;
311 return STICKERD_SERVER_ERROR_DB_FAILED;
313 ret = sqlite3_prepare_v2(db, STICKER_DB_INSERT_STICKER_INFO, -1, &stmt, NULL);
314 if (ret != SQLITE_OK) {
315 LOGE("fail to insert sticker information : %s", sqlite3_errmsg(db));
319 sqlite3_bind_text(stmt, 1, sticker_info->app_id, -1, SQLITE_TRANSIENT);
320 sqlite3_bind_int(stmt, 2, sticker_info->type);
321 sqlite3_bind_text(stmt, 3, sticker_info->uri, -1, SQLITE_TRANSIENT);
322 sqlite3_bind_text(stmt, 4, sticker_info->thumbnail, -1, SQLITE_TRANSIENT);
323 sqlite3_bind_text(stmt, 5, sticker_info->description, -1, SQLITE_TRANSIENT);
324 sqlite3_bind_text(stmt, 6, sticker_info->group, -1, SQLITE_TRANSIENT);
325 sqlite3_bind_int(stmt, 7, sticker_info->display_type);
327 ret = sqlite3_step(stmt);
328 if (ret != SQLITE_OK && ret != SQLITE_DONE) {
329 LOGE("sqlite3_step() failed : ret(%d)", ret);
331 } else if (sqlite3_changes(db) == 0) {
332 LOGE("No changes to DB");
336 sqlite3_finalize(stmt);
339 ret = sqlite3_prepare_v2(db, STICKER_DB_GET_LATEST_RECORD_ID, -1, &stmt, NULL);
340 if (ret != SQLITE_OK) {
341 LOGE("fail to get sticker id : %s", sqlite3_errmsg(db));
345 ret = sqlite3_step(stmt);
346 if (ret == SQLITE_ERROR) {
347 LOGE("sqlite3_step() failed : ret(%d)", ret);
351 *record_id = sqlite3_column_int(stmt, 0);
352 LOGD("record_id : %d", *record_id);
356 for(list = sticker_info->keyword; list != NULL; list=list->next) {
357 sqlite3_finalize(stmt);
360 ret = sqlite3_prepare_v2(db, STICKER_DB_INSERT_STICKER_KEYWORD_INFO, -1, &stmt, NULL);
361 if (ret != SQLITE_OK) {
362 LOGE("fail to insert sticker keyword : %s", sqlite3_errmsg(db));
366 sqlite3_bind_int(stmt, 1, *record_id);
367 sqlite3_bind_text(stmt, 2, (char *)list->data, -1, SQLITE_TRANSIENT);
369 ret = sqlite3_step(stmt);
370 if (ret != SQLITE_OK && ret != SQLITE_DONE) {
371 LOGE("sqlite3_step() failed : ret(%d)", ret);
373 } else if (sqlite3_changes(db) == 0) {
374 LOGE("No changes to DB");
379 sqlite3_finalize(stmt);
382 return STICKERD_SERVER_ERROR_NONE;
385 sqlite3_finalize(stmt);
388 return STICKERD_SERVER_ERROR_DB_FAILED;
391 int stickerd_db_delete_sticker_info(int record_id)
395 sqlite3_stmt *stmt = NULL;
399 return STICKERD_SERVER_ERROR_DB_FAILED;
401 ret = sqlite3_prepare_v2(db, STICKER_DB_DELETE_STICKER_INFO, -1, &stmt, NULL);
402 if (ret != SQLITE_OK) {
403 LOGE("fail to delete sticker information : %s", sqlite3_errmsg(db));
407 sqlite3_bind_int(stmt, 1, record_id);
409 ret = sqlite3_step(stmt);
410 if (ret != SQLITE_OK && ret != SQLITE_DONE) {
411 LOGE("sqlite3_step() failed : ret(%d)", ret);
413 } else if (sqlite3_changes(db) == 0) {
414 LOGE("No changes to DB");
418 sqlite3_finalize(stmt);
421 return STICKERD_SERVER_ERROR_NONE;
424 sqlite3_finalize(stmt);
427 return STICKERD_SERVER_ERROR_DB_FAILED;
430 int stickerd_db_delete_sticker_info_by_uri(char *uri)
434 sqlite3_stmt *stmt = NULL;
438 return STICKERD_SERVER_ERROR_DB_FAILED;
440 ret = sqlite3_prepare_v2(db, STICKER_DB_DELETE_STICKER_INFO_BY_URI, -1, &stmt, NULL);
441 if (ret != SQLITE_OK) {
442 LOGE("fail to delete sticker information : %s", sqlite3_errmsg(db));
446 sqlite3_bind_text(stmt, 1, uri, -1, SQLITE_TRANSIENT);
448 ret = sqlite3_step(stmt);
449 if (ret != SQLITE_OK && ret != SQLITE_DONE) {
450 LOGE("sqlite3_step() failed : ret(%d)", ret);
452 } else if (sqlite3_changes(db) == 0) {
453 LOGE("No changes to DB");
457 sqlite3_finalize(stmt);
460 return STICKERD_SERVER_ERROR_NONE;
463 sqlite3_finalize(stmt);
466 return STICKERD_SERVER_ERROR_DB_FAILED;
469 int stickerd_db_update_sticker_info(int record_id, sticker_info_db_type type, void *data)
473 sqlite3_stmt *stmt = NULL;
477 return STICKERD_SERVER_ERROR_DB_FAILED;
479 const char* query = _db_get_query(type, CMD_UPDATE);
480 ret = sqlite3_prepare_v2(db, query, -1, &stmt, NULL);
481 if (ret != SQLITE_OK) {
482 LOGE("fail to update sticker information : %s", sqlite3_errmsg(db));
486 if (type == STICKER_DB_STICKER_TYPE || type == STICKER_DB_STICKER_DISP_TYPE) {
487 sqlite3_bind_int(stmt, 1, *(int *)data);
488 } else if (type == STICKER_DB_STICKER_KEYWORD) {
489 sqlite3_bind_int(stmt, 1, record_id);
491 sqlite3_bind_text(stmt, 1, (char *)data, -1, SQLITE_TRANSIENT);
494 if (type != STICKER_DB_STICKER_KEYWORD)
495 sqlite3_bind_int(stmt, 2, record_id);
497 ret = sqlite3_step(stmt);
498 if (ret != SQLITE_OK && ret != SQLITE_DONE) {
499 LOGE("sqlite3_step() failed : ret(%d)", ret);
503 if (type == STICKER_DB_STICKER_KEYWORD) {
505 for(list = (GList *)data; list != NULL; list=list->next) {
506 sqlite3_finalize(stmt);
509 ret = sqlite3_prepare_v2(db, STICKER_DB_INSERT_STICKER_KEYWORD_INFO, -1, &stmt, NULL);
510 if (ret != SQLITE_OK) {
511 LOGE("fail to insert sticker information : %s", sqlite3_errmsg(db));
515 sqlite3_bind_int(stmt, 1, record_id);
516 sqlite3_bind_text(stmt, 2, (char *)list->data, -1, SQLITE_TRANSIENT);
518 ret = sqlite3_step(stmt);
519 if (ret != SQLITE_OK && ret != SQLITE_DONE) {
520 LOGE("sqlite3_step() failed : ret(%d)", ret);
522 } else if (sqlite3_changes(db) == 0) {
523 LOGE("No changes to DB");
529 sqlite3_finalize(stmt);
532 return STICKERD_SERVER_ERROR_NONE;
535 sqlite3_finalize(stmt);
538 return STICKERD_SERVER_ERROR_DB_FAILED;
541 int stickerd_db_get_sticker_info_by_record_id(int record_id, sticker_info_db *sticker_info)
545 sqlite3_stmt *stmt = NULL;
549 return STICKERD_SERVER_ERROR_DB_FAILED;
551 ret = sqlite3_prepare_v2(db, STICKER_DB_GET_STICKER_INFO_BY_RECORD_ID, -1, &stmt, NULL);
552 if (ret != SQLITE_OK) {
553 LOGE("fail to get sticker information : %s", sqlite3_errmsg(db));
557 sqlite3_bind_int(stmt, 1, record_id);
559 ret = sqlite3_step(stmt);
560 if (ret == SQLITE_ERROR) {
561 LOGE("sqlite3_step() failed : ret(%d)", ret);
565 const unsigned char *tmp_app_id = sqlite3_column_text(stmt, 1);
567 sticker_info->app_id = strdup((const char *)tmp_app_id);
569 LOGW("invalid record_id : %d", record_id);
573 sticker_info->type = sqlite3_column_int(stmt, 2);
575 const unsigned char *tmp_uri = sqlite3_column_text(stmt, 3);
577 sticker_info->uri = strdup((const char *)tmp_uri);
579 const unsigned char *tmp_thumbnail = sqlite3_column_text(stmt, 4);
581 sticker_info->thumbnail = strdup((const char *)tmp_thumbnail);
583 const unsigned char *tmp_description = sqlite3_column_text(stmt, 5);
585 sticker_info->description = strdup((const char *)tmp_description);
587 const unsigned char *tmp_group = sqlite3_column_text(stmt, 6);
589 sticker_info->group = strdup((const char *)tmp_group);
591 const unsigned char *tmp_date = sqlite3_column_text(stmt, 7);
593 sticker_info->date = strdup((const char *)tmp_date);
595 sticker_info->display_type = sqlite3_column_int(stmt, 8);
597 sqlite3_finalize(stmt);
600 ret = sqlite3_prepare_v2(db, STICKER_DB_GET_KEYWORD_INFO_BY_RECORD_ID, -1, &stmt, NULL);
601 if (ret != SQLITE_OK) {
602 LOGE("fail to get sticker keyword : %s", sqlite3_errmsg(db));
606 sqlite3_bind_int(stmt, 1, record_id);
608 while (sqlite3_step(stmt) == SQLITE_ROW) {
609 const unsigned char *keyword = sqlite3_column_text(stmt, 0);
611 sticker_info->keyword = g_list_append(sticker_info->keyword, strdup((const char *)keyword));
614 sqlite3_finalize(stmt);
617 return STICKERD_SERVER_ERROR_NONE;
620 sqlite3_finalize(stmt);
623 return STICKERD_SERVER_ERROR_DB_FAILED;
626 int stickerd_db_get_group_list(GVariantBuilder *builder, char *app_id)
630 sqlite3_stmt *stmt = NULL;
634 return STICKERD_SERVER_ERROR_DB_FAILED;
636 ret = sqlite3_prepare_v2(db, STICKER_DB_GET_ALL_GROUP_LIST, -1, &stmt, NULL);
637 if (ret != SQLITE_OK) {
638 LOGE("fail to get group list : %s", sqlite3_errmsg(db));
642 sqlite3_bind_text(stmt, 1, app_id, -1, SQLITE_TRANSIENT);
644 while (sqlite3_step(stmt) == SQLITE_ROW) {
645 const unsigned char *group = sqlite3_column_text(stmt, 0);
647 g_variant_builder_add(builder, "(s)", strdup((const char *)group));
650 sqlite3_finalize(stmt);
653 return STICKERD_SERVER_ERROR_NONE;
656 sqlite3_finalize(stmt);
659 return STICKERD_SERVER_ERROR_DB_FAILED;
662 int stickerd_db_get_keyword_list(GVariantBuilder *builder, char *app_id)
666 sqlite3_stmt *stmt = NULL;
670 return STICKERD_SERVER_ERROR_DB_FAILED;
672 ret = sqlite3_prepare_v2(db, STICKER_DB_GET_ALL_KEYWORD_LIST, -1, &stmt, NULL);
673 if (ret != SQLITE_OK) {
674 LOGE("fail to get keyword list : %s", sqlite3_errmsg(db));
678 sqlite3_bind_text(stmt, 1, app_id, -1, SQLITE_TRANSIENT);
680 while (sqlite3_step(stmt) == SQLITE_ROW) {
681 const unsigned char *keyword = sqlite3_column_text(stmt, 0);
683 g_variant_builder_add(builder, "(s)", strdup((const char *)keyword));
686 sqlite3_finalize(stmt);
689 return STICKERD_SERVER_ERROR_NONE;
692 sqlite3_finalize(stmt);
695 return STICKERD_SERVER_ERROR_DB_FAILED;
698 int stickerd_db_get_sticker_count(int *count, char *app_id)
702 sqlite3_stmt *stmt = NULL;
706 return STICKERD_SERVER_ERROR_DB_FAILED;
708 ret = sqlite3_prepare_v2(db, STICKER_DB_GET_STICKER_COUNT, -1, &stmt, NULL);
709 if (ret != SQLITE_OK) {
710 LOGE("fail to get sticker count : %s", sqlite3_errmsg(db));
714 sqlite3_bind_text(stmt, 1, app_id, -1, SQLITE_TRANSIENT);
716 ret = sqlite3_step(stmt);
717 if (ret == SQLITE_ERROR) {
718 LOGE("sqlite3_step() failed : ret(%d)", ret);
722 *count = sqlite3_column_int(stmt, 0);
724 sqlite3_finalize(stmt);
727 return STICKERD_SERVER_ERROR_NONE;
730 sqlite3_finalize(stmt);
733 return STICKERD_SERVER_ERROR_DB_FAILED;
736 int stickerd_db_get_record_id(sticker_info_db_type type, GList **id_list, void *data, char *app_id, int offset, int count)
740 sqlite3_stmt *stmt = NULL;
744 return STICKERD_SERVER_ERROR_DB_FAILED;
746 const char* query = _db_get_query(type, CMD_SELECT);
747 ret = sqlite3_prepare_v2(db, query, -1, &stmt, NULL);
748 if (ret != SQLITE_OK) {
749 LOGE("fail to get record id : %s", sqlite3_errmsg(db));
753 if (type == STICKER_DB_STICKER_ALL || type == STICKER_DB_STICKER_APPID) {
754 sqlite3_bind_text(stmt, 1, app_id, -1, SQLITE_TRANSIENT);
755 sqlite3_bind_int(stmt, 2, offset);
756 sqlite3_bind_int(stmt, 3, count);
758 if (type == STICKER_DB_STICKER_TYPE || type == STICKER_DB_STICKER_DISP_TYPE)
759 sqlite3_bind_int(stmt, 1, *(int *)data);
761 sqlite3_bind_text(stmt, 1, (char *)data, -1, SQLITE_TRANSIENT);
763 sqlite3_bind_text(stmt, 2, app_id, -1, SQLITE_TRANSIENT);
764 sqlite3_bind_int(stmt, 3, offset);
765 sqlite3_bind_int(stmt, 4, count);
768 while (sqlite3_step(stmt) == SQLITE_ROW) {
769 const unsigned char *tmp_id = sqlite3_column_text(stmt, 0);
771 *id_list = g_list_append(*id_list, strdup((const char *)tmp_id));
774 sqlite3_finalize(stmt);
777 return STICKERD_SERVER_ERROR_NONE;
780 sqlite3_finalize(stmt);
783 return STICKERD_SERVER_ERROR_DB_FAILED;
786 int stickerd_db_get_group_list_by_display_type(GVariantBuilder *builder, char *app_id, int disp_type)
790 sqlite3_stmt *stmt = NULL;
794 return STICKERD_SERVER_ERROR_DB_FAILED;
796 ret = sqlite3_prepare_v2(db, STICKER_DB_GET_GROUP_LIST_BY_DISP_TYPE, -1, &stmt, NULL);
797 if (ret != SQLITE_OK) {
798 LOGE("fail to get group list : %s", sqlite3_errmsg(db));
802 sqlite3_bind_int(stmt, 1, disp_type);
803 sqlite3_bind_text(stmt, 2, app_id, -1, SQLITE_TRANSIENT);
805 while (sqlite3_step(stmt) == SQLITE_ROW) {
806 const unsigned char *group = sqlite3_column_text(stmt, 0);
808 g_variant_builder_add(builder, "(s)", strdup((const char *)group));
811 sqlite3_finalize(stmt);
814 return STICKERD_SERVER_ERROR_NONE;
817 sqlite3_finalize(stmt);
820 return STICKERD_SERVER_ERROR_DB_FAILED;
823 int stickerd_db_check_file_exists(int *result, char *uri)
827 sqlite3_stmt *stmt = NULL;
831 return STICKERD_SERVER_ERROR_DB_FAILED;
833 ret = sqlite3_prepare_v2(db, STICKER_DB_CHECK_FILE_EXISTS, -1, &stmt, NULL);
834 if (ret != SQLITE_OK) {
835 LOGE("fail to check file exists : %s", sqlite3_errmsg(db));
839 sqlite3_bind_text(stmt, 1, uri, -1, SQLITE_TRANSIENT);
841 ret = sqlite3_step(stmt);
842 if (ret == SQLITE_ERROR) {
843 LOGE("sqlite3_step() failed : ret(%d)", ret);
847 *result = sqlite3_column_int(stmt, 0);
849 sqlite3_finalize(stmt);
852 return STICKERD_SERVER_ERROR_NONE;
855 sqlite3_finalize(stmt);
858 return STICKERD_SERVER_ERROR_DB_FAILED;