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 (?, ?)"
72 #define STICKER_DB_DELETE_STICKER_INFO "DELETE FROM sticker_info WHERE sticker_info_id = ?"
73 #define STICKER_DB_DELETE_STICKER_KEYWORD_INFO "DELETE FROM sticker_keyword_info WHERE sticker_info_id = ?"
74 #define STICKER_DB_DELETE_STICKER_INFO_BY_URI "DELETE FROM sticker_info WHERE uri = ?"
76 #define STICKER_DB_UPDATE_STICKER_TYPE "UPDATE sticker_info SET type = ?, date = DateTime('now','localtime') WHERE sticker_info_id = ?"
77 #define STICKER_DB_UPDATE_STICKER_URI "UPDATE sticker_info SET uri = ?, date = DateTime('now','localtime') WHERE sticker_info_id = ?"
78 #define STICKER_DB_UPDATE_STICKER_THUMBNAIL "UPDATE sticker_info SET thumbnail = ?, date = DateTime('now','localtime') WHERE sticker_info_id = ?"
79 #define STICKER_DB_UPDATE_STICKER_DESCRIPTION "UPDATE sticker_info SET description = ?, date = DateTime('now','localtime') WHERE sticker_info_id = ?"
80 #define STICKER_DB_UPDATE_STICKER_GROUP "UPDATE sticker_info SET group_name = ?, date = DateTime('now','localtime') WHERE sticker_info_id = ?"
81 #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_GET_LATEST_RECORD_ID "SELECT sticker_info_id FROM sticker_info ORDER BY sticker_info_id DESC LIMIT 1"
84 #define STICKER_DB_GET_STICKER_INFO_BY_RECORD_ID "SELECT * FROM sticker_info WHERE sticker_info_id = ?"
85 #define STICKER_DB_GET_KEYWORD_INFO_BY_RECORD_ID "SELECT keyword FROM sticker_keyword_info WHERE sticker_info_id = ?"
86 #define STICKER_DB_GET_IMAGE_INFO_BY_RECORED_ID "SELECT type, uri, thumbnail FROM sticker_info WHERE sticker_info_id = ?"
87 #define STICKER_DB_GET_IMAGE_INFO_BY_URI "SELECT type, thumbnail FROM sticker_info WHERE uri = ?"
88 #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 = ?))"
89 #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 = ?)))"
90 #define STICKER_DB_GET_STICKER_COUNT "SELECT count(*) FROM sticker_info WHERE app_id = ?"
91 #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 ?, ?"
92 #define STICKER_DB_GET_RECORD_ID_BY_APP_ID "SELECT sticker_info_id from sticker_info WHERE app_id = ? LIMIT ?, ?"
93 #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 ?, ?"
94 #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 ?, ?"
95 #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 ?, ?"
96 #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 ?, ?"
97 #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 = ?))"
98 #define STICKER_DB_CHECK_FILE_EXISTS "SELECT EXISTS(SELECT 1 FROM sticker_info WHERE uri = ? LIMIT 1)"
106 static gboolean is_corrupted = FALSE;
108 static const char *_db_get_query(sticker_info_db_type sticker_type, command_type cmd_type)
110 static const char* query = NULL;
112 if (cmd_type == CMD_UPDATE) {
113 switch(sticker_type) {
114 case STICKER_DB_STICKER_TYPE:
115 query = STICKER_DB_UPDATE_STICKER_TYPE;
117 case STICKER_DB_STICKER_URI:
118 query = STICKER_DB_UPDATE_STICKER_URI;
120 case STICKER_DB_STICKER_THUMBNAIL:
121 query = STICKER_DB_UPDATE_STICKER_THUMBNAIL;
123 case STICKER_DB_STICKER_DESCRIPTION:
124 query = STICKER_DB_UPDATE_STICKER_DESCRIPTION;
126 case STICKER_DB_STICKER_GROUP:
127 query = STICKER_DB_UPDATE_STICKER_GROUP;
129 case STICKER_DB_STICKER_KEYWORD:
130 query = STICKER_DB_DELETE_STICKER_KEYWORD_INFO;
132 case STICKER_DB_STICKER_DISP_TYPE:
133 query = STICKER_DB_UPDATE_STICKER_DISP_TYPE;
139 } else if (cmd_type == CMD_SELECT) {
140 switch(sticker_type) {
141 case STICKER_DB_STICKER_ALL:
142 query = STICKER_DB_GET_ALL_RECORD_ID;
144 case STICKER_DB_STICKER_APPID:
145 query = STICKER_DB_GET_RECORD_ID_BY_APP_ID;
147 case STICKER_DB_STICKER_TYPE:
148 query = STICKER_DB_GET_RECORD_ID_BY_TYPE;
150 case STICKER_DB_STICKER_GROUP:
151 query = STICKER_DB_GET_RECORD_ID_BY_GROUP;
153 case STICKER_DB_STICKER_KEYWORD:
154 query = STICKER_DB_GET_RECORD_ID_BY_KEYWORD;
156 case STICKER_DB_STICKER_DISP_TYPE:
157 query = STICKER_DB_GET_RECORD_ID_BY_DISP_TYPE;
168 static int _recover_db(void)
170 int ret = STICKERD_SERVER_ERROR_NONE;
174 LOGD("Start to recover sticker db");
175 //Remove sticker database file
176 if (unlink(STICKER_DB_PATH) == -1)
177 LOGE("Failed to remove db file");
179 ret = sqlite3_open_v2(STICKER_DB_PATH, &db, SQLITE_OPEN_CREATE | SQLITE_OPEN_READWRITE, NULL);
180 if (ret != SQLITE_OK) {
181 LOGE("Failed to open db : %s", sqlite3_errmsg(db));
182 if (unlink(STICKER_DB_PATH) == -1)
183 LOGE("Failed to remove db file");
184 ret = STICKERD_SERVER_ERROR_DB_FAILED;
188 ret = sqlite3_exec(db, STICKER_INFO_CREATE_TABLE, NULL, NULL, &err);
189 if (ret != SQLITE_OK) {
190 LOGE("Failed to create sticker_info table : %s" , err);
191 ret = STICKERD_SERVER_ERROR_DB_FAILED;
195 ret = sqlite3_exec(db, STICKER_KEYWORD_INFO_CREATE_TABLE, NULL, NULL, &err);
196 if (ret != SQLITE_OK) {
197 LOGE("Failed to create sticker_keyword_info table : %s", err);
198 ret = STICKERD_SERVER_ERROR_DB_FAILED;
202 ret = sqlite3_exec(db, STICKER_WHITELIST_INFO_CREATE_TABLE, NULL, NULL, &err);
203 if (ret != SQLITE_OK) {
204 LOGE("Failed to create sticker_whitelist_info table : %s", err);
205 ret = STICKERD_SERVER_ERROR_DB_FAILED;
208 ret = sqlite3_exec(db, STICKER_RECENT_HISTORY_INFO_CREATE_TABLE, NULL, NULL, &err);
209 if (ret != SQLITE_OK) {
210 LOGE("Failed to create sticker_recent_history_info table : %s", err);
211 ret = STICKERD_SERVER_ERROR_DB_FAILED;
214 is_corrupted = FALSE;
226 static int _integrity_check_cb(void *pid, int argc, char **argv, char **notUsed)
228 if (strcmp(argv[0], "ok") != 0) {
229 LOGE("DB integrity check failed : %s", argv[0]);
234 LOGD("Result integrity : %s", argv[0]);
238 int stickerd_db_init(void)
240 int ret = STICKERD_SERVER_ERROR_NONE;
244 ret = sqlite3_open_v2(STICKER_DB_PATH, &db, SQLITE_OPEN_CREATE | SQLITE_OPEN_READWRITE, NULL);
245 if (ret != SQLITE_OK) {
246 LOGE("Failed to open db : %s", sqlite3_errmsg(db));
247 ret = STICKERD_SERVER_ERROR_DB_FAILED;
251 ret = sqlite3_exec(db, STICKER_INFO_CREATE_TABLE, NULL, NULL, &err);
252 if (ret != SQLITE_OK) {
253 LOGE("Failed to create sticker_info table : %s" , err);
254 ret = STICKERD_SERVER_ERROR_DB_FAILED;
258 ret = sqlite3_exec(db, STICKER_KEYWORD_INFO_CREATE_TABLE, NULL, NULL, &err);
259 if (ret != SQLITE_OK) {
260 LOGE("Failed to create sticker_keyword_info table : %s", err);
261 ret = STICKERD_SERVER_ERROR_DB_FAILED;
265 ret = sqlite3_exec(db, STICKER_WHITELIST_INFO_CREATE_TABLE, NULL, NULL, &err);
266 if (ret != SQLITE_OK) {
267 LOGE("Failed to create sticker_whitelist_info table : %s", err);
268 ret = STICKERD_SERVER_ERROR_DB_FAILED;
272 ret = sqlite3_exec(db, STICKER_RECENT_HISTORY_INFO_CREATE_TABLE, NULL, NULL, &err);
273 if (ret != SQLITE_OK) {
274 LOGE("Failed to create sticker_recent_history_info table : %s", err);
275 ret = STICKERD_SERVER_ERROR_DB_FAILED;
279 ret = sqlite3_exec(db, "PRAGMA journal_mode = WAL", NULL, NULL, &err);
280 if (ret != SQLITE_OK) {
281 LOGE("Failed to set journal_mode : %s", err);
282 ret = STICKERD_SERVER_ERROR_DB_FAILED;
286 ret = sqlite3_exec(db, "PRAGMA integrity_check", _integrity_check_cb, NULL, &err);
287 if (ret != SQLITE_OK) {
288 LOGE("Failed to check integrity : %s", err);
289 ret = STICKERD_SERVER_ERROR_DB_FAILED;
299 if (ret == SQLITE_CORRUPT || ret == SQLITE_NOTADB || is_corrupted)
305 static sqlite3 *_db_open(void)
311 ret = sqlite3_open(STICKER_DB_PATH, &db);
312 if (ret != SQLITE_OK) {
313 LOGE("Failed to open db : %s", sqlite3_errmsg(db));
317 ret = sqlite3_exec(db, "PRAGMA foreign_keys = ON", NULL, NULL, &err);
318 if (ret != SQLITE_OK) {
319 LOGE("Failed to turn on foreign keys : %s", err);
328 int stickerd_db_insert_sticker_info(int *record_id, sticker_info_db *sticker_info)
332 sqlite3_stmt *stmt = NULL;
336 return STICKERD_SERVER_ERROR_DB_FAILED;
338 ret = sqlite3_prepare_v2(db, STICKER_DB_INSERT_STICKER_INFO, -1, &stmt, NULL);
339 if (ret != SQLITE_OK) {
340 LOGE("fail to insert sticker information : %s", sqlite3_errmsg(db));
344 sqlite3_bind_text(stmt, 1, sticker_info->app_id, -1, SQLITE_TRANSIENT);
345 sqlite3_bind_int(stmt, 2, sticker_info->type);
346 sqlite3_bind_text(stmt, 3, sticker_info->uri, -1, SQLITE_TRANSIENT);
347 sqlite3_bind_text(stmt, 4, sticker_info->thumbnail, -1, SQLITE_TRANSIENT);
348 sqlite3_bind_text(stmt, 5, sticker_info->description, -1, SQLITE_TRANSIENT);
349 sqlite3_bind_text(stmt, 6, sticker_info->group, -1, SQLITE_TRANSIENT);
350 sqlite3_bind_int(stmt, 7, sticker_info->display_type);
352 ret = sqlite3_step(stmt);
353 if (ret != SQLITE_OK && ret != SQLITE_DONE) {
354 LOGE("sqlite3_step() failed : ret(%d)", ret);
356 } else if (sqlite3_changes(db) == 0) {
357 LOGE("No changes to DB");
361 sqlite3_finalize(stmt);
364 ret = sqlite3_prepare_v2(db, STICKER_DB_GET_LATEST_RECORD_ID, -1, &stmt, NULL);
365 if (ret != SQLITE_OK) {
366 LOGE("fail to get sticker id : %s", sqlite3_errmsg(db));
370 ret = sqlite3_step(stmt);
371 if (ret == SQLITE_ERROR) {
372 LOGE("sqlite3_step() failed : ret(%d)", ret);
376 *record_id = sqlite3_column_int(stmt, 0);
377 LOGD("record_id : %d", *record_id);
381 for(list = sticker_info->keyword; list != NULL; list=list->next) {
382 sqlite3_finalize(stmt);
385 ret = sqlite3_prepare_v2(db, STICKER_DB_INSERT_STICKER_KEYWORD_INFO, -1, &stmt, NULL);
386 if (ret != SQLITE_OK) {
387 LOGE("fail to insert sticker keyword : %s", sqlite3_errmsg(db));
391 sqlite3_bind_int(stmt, 1, *record_id);
392 sqlite3_bind_text(stmt, 2, (char *)list->data, -1, SQLITE_TRANSIENT);
394 ret = sqlite3_step(stmt);
395 if (ret != SQLITE_OK && ret != SQLITE_DONE) {
396 LOGE("sqlite3_step() failed : ret(%d)", ret);
398 } else if (sqlite3_changes(db) == 0) {
399 LOGE("No changes to DB");
404 sqlite3_finalize(stmt);
407 return STICKERD_SERVER_ERROR_NONE;
410 sqlite3_finalize(stmt);
413 return STICKERD_SERVER_ERROR_DB_FAILED;
416 int stickerd_db_delete_sticker_info(int record_id)
420 sqlite3_stmt *stmt = NULL;
424 return STICKERD_SERVER_ERROR_DB_FAILED;
426 ret = sqlite3_prepare_v2(db, STICKER_DB_GET_IMAGE_INFO_BY_RECORED_ID, -1, &stmt, NULL);
427 if (ret != SQLITE_OK) {
428 LOGE("fail to get image files : %s", sqlite3_errmsg(db));
432 sqlite3_bind_int(stmt, 1, record_id);
434 ret = sqlite3_step(stmt);
435 if (ret == SQLITE_ERROR) {
436 LOGE("sqlite3_step() failed : ret(%d)", ret);
440 int uri_type = sqlite3_column_int(stmt, 0);
441 const unsigned char *uri = sqlite3_column_text(stmt, 1);
442 const unsigned char *thumbnail = sqlite3_column_text(stmt, 2);
444 if (uri_type == 1 && unlink((const char *)uri) == -1)
445 LOGE("fail to delete sticker file");
447 if (thumbnail && unlink((const char *)thumbnail) == -1)
448 LOGE("fail to delete thumbnail image");
450 sqlite3_finalize(stmt);
453 ret = sqlite3_prepare_v2(db, STICKER_DB_DELETE_STICKER_INFO, -1, &stmt, NULL);
454 if (ret != SQLITE_OK) {
455 LOGE("fail to delete sticker information : %s", sqlite3_errmsg(db));
459 sqlite3_bind_int(stmt, 1, record_id);
461 ret = sqlite3_step(stmt);
462 if (ret != SQLITE_OK && ret != SQLITE_DONE) {
463 LOGE("sqlite3_step() failed : ret(%d)", ret);
465 } else if (sqlite3_changes(db) == 0) {
466 LOGE("No changes to DB");
470 sqlite3_finalize(stmt);
473 return STICKERD_SERVER_ERROR_NONE;
476 sqlite3_finalize(stmt);
479 return STICKERD_SERVER_ERROR_DB_FAILED;
482 int stickerd_db_delete_sticker_info_by_uri(char *uri)
486 sqlite3_stmt *stmt = NULL;
490 return STICKERD_SERVER_ERROR_DB_FAILED;
492 ret = sqlite3_prepare_v2(db, STICKER_DB_GET_IMAGE_INFO_BY_URI, -1, &stmt, NULL);
493 if (ret != SQLITE_OK) {
494 LOGE("fail to delete sticker information : %s", sqlite3_errmsg(db));
498 sqlite3_bind_text(stmt, 1, uri, -1, SQLITE_TRANSIENT);
500 ret = sqlite3_step(stmt);
501 if (ret == SQLITE_ERROR) {
502 LOGE("sqlite3_step() failed : ret(%d)", ret);
506 int uri_type = sqlite3_column_int(stmt, 0);
507 const unsigned char *thumbnail = sqlite3_column_text(stmt, 1);
509 if (uri_type == 1 && unlink((const char *)uri) == -1)
510 LOGE("fail to delete sticker file");
512 if (thumbnail && unlink((const char *)thumbnail) == -1)
513 LOGE("fail to delete thumbnail image");
515 sqlite3_finalize(stmt);
518 ret = sqlite3_prepare_v2(db, STICKER_DB_DELETE_STICKER_INFO_BY_URI, -1, &stmt, NULL);
519 if (ret != SQLITE_OK) {
520 LOGE("fail to delete sticker information : %s", sqlite3_errmsg(db));
524 sqlite3_bind_text(stmt, 1, uri, -1, SQLITE_TRANSIENT);
526 ret = sqlite3_step(stmt);
527 if (ret != SQLITE_OK && ret != SQLITE_DONE) {
528 LOGE("sqlite3_step() failed : ret(%d)", ret);
530 } else if (sqlite3_changes(db) == 0) {
531 LOGE("No changes to DB");
535 sqlite3_finalize(stmt);
538 return STICKERD_SERVER_ERROR_NONE;
541 sqlite3_finalize(stmt);
544 return STICKERD_SERVER_ERROR_DB_FAILED;
547 int stickerd_db_update_sticker_info(int record_id, sticker_info_db_type type, void *data)
551 sqlite3_stmt *stmt = NULL;
555 return STICKERD_SERVER_ERROR_DB_FAILED;
557 const char* query = _db_get_query(type, CMD_UPDATE);
558 ret = sqlite3_prepare_v2(db, query, -1, &stmt, NULL);
559 if (ret != SQLITE_OK) {
560 LOGE("fail to update sticker information : %s", sqlite3_errmsg(db));
564 if (type == STICKER_DB_STICKER_TYPE || type == STICKER_DB_STICKER_DISP_TYPE) {
565 sqlite3_bind_int(stmt, 1, *(int *)data);
566 } else if (type == STICKER_DB_STICKER_KEYWORD) {
567 sqlite3_bind_int(stmt, 1, record_id);
569 sqlite3_bind_text(stmt, 1, (char *)data, -1, SQLITE_TRANSIENT);
572 if (type != STICKER_DB_STICKER_KEYWORD)
573 sqlite3_bind_int(stmt, 2, record_id);
575 ret = sqlite3_step(stmt);
576 if (ret != SQLITE_OK && ret != SQLITE_DONE) {
577 LOGE("sqlite3_step() failed : ret(%d)", ret);
581 if (type == STICKER_DB_STICKER_KEYWORD) {
583 for(list = (GList *)data; list != NULL; list=list->next) {
584 sqlite3_finalize(stmt);
587 ret = sqlite3_prepare_v2(db, STICKER_DB_INSERT_STICKER_KEYWORD_INFO, -1, &stmt, NULL);
588 if (ret != SQLITE_OK) {
589 LOGE("fail to insert sticker information : %s", sqlite3_errmsg(db));
593 sqlite3_bind_int(stmt, 1, record_id);
594 sqlite3_bind_text(stmt, 2, (char *)list->data, -1, SQLITE_TRANSIENT);
596 ret = sqlite3_step(stmt);
597 if (ret != SQLITE_OK && ret != SQLITE_DONE) {
598 LOGE("sqlite3_step() failed : ret(%d)", ret);
600 } else if (sqlite3_changes(db) == 0) {
601 LOGE("No changes to DB");
607 sqlite3_finalize(stmt);
610 return STICKERD_SERVER_ERROR_NONE;
613 sqlite3_finalize(stmt);
616 return STICKERD_SERVER_ERROR_DB_FAILED;
619 int stickerd_db_get_sticker_info_by_record_id(int record_id, sticker_info_db *sticker_info)
623 sqlite3_stmt *stmt = NULL;
627 return STICKERD_SERVER_ERROR_DB_FAILED;
629 ret = sqlite3_prepare_v2(db, STICKER_DB_GET_STICKER_INFO_BY_RECORD_ID, -1, &stmt, NULL);
630 if (ret != SQLITE_OK) {
631 LOGE("fail to get sticker information : %s", sqlite3_errmsg(db));
635 sqlite3_bind_int(stmt, 1, record_id);
637 ret = sqlite3_step(stmt);
638 if (ret == SQLITE_ERROR) {
639 LOGE("sqlite3_step() failed : ret(%d)", ret);
643 const unsigned char *tmp_app_id = sqlite3_column_text(stmt, 1);
645 sticker_info->app_id = strdup((const char *)tmp_app_id);
647 LOGW("invalid record_id : %d", record_id);
651 sticker_info->type = sqlite3_column_int(stmt, 2);
653 const unsigned char *tmp_uri = sqlite3_column_text(stmt, 3);
655 sticker_info->uri = strdup((const char *)tmp_uri);
657 const unsigned char *tmp_thumbnail = sqlite3_column_text(stmt, 4);
659 sticker_info->thumbnail = strdup((const char *)tmp_thumbnail);
661 const unsigned char *tmp_description = sqlite3_column_text(stmt, 5);
663 sticker_info->description = strdup((const char *)tmp_description);
665 const unsigned char *tmp_group = sqlite3_column_text(stmt, 6);
667 sticker_info->group = strdup((const char *)tmp_group);
669 const unsigned char *tmp_date = sqlite3_column_text(stmt, 7);
671 sticker_info->date = strdup((const char *)tmp_date);
673 sticker_info->display_type = sqlite3_column_int(stmt, 8);
675 sqlite3_finalize(stmt);
678 ret = sqlite3_prepare_v2(db, STICKER_DB_GET_KEYWORD_INFO_BY_RECORD_ID, -1, &stmt, NULL);
679 if (ret != SQLITE_OK) {
680 LOGE("fail to get sticker keyword : %s", sqlite3_errmsg(db));
684 sqlite3_bind_int(stmt, 1, record_id);
686 while (sqlite3_step(stmt) == SQLITE_ROW) {
687 const unsigned char *keyword = sqlite3_column_text(stmt, 0);
689 sticker_info->keyword = g_list_append(sticker_info->keyword, strdup((const char *)keyword));
692 sqlite3_finalize(stmt);
695 return STICKERD_SERVER_ERROR_NONE;
698 sqlite3_finalize(stmt);
701 return STICKERD_SERVER_ERROR_DB_FAILED;
704 int stickerd_db_get_group_list(GVariantBuilder *builder, char *app_id)
708 sqlite3_stmt *stmt = NULL;
712 return STICKERD_SERVER_ERROR_DB_FAILED;
714 ret = sqlite3_prepare_v2(db, STICKER_DB_GET_ALL_GROUP_LIST, -1, &stmt, NULL);
715 if (ret != SQLITE_OK) {
716 LOGE("fail to get group list : %s", sqlite3_errmsg(db));
720 sqlite3_bind_text(stmt, 1, app_id, -1, SQLITE_TRANSIENT);
722 while (sqlite3_step(stmt) == SQLITE_ROW) {
723 const unsigned char *group = sqlite3_column_text(stmt, 0);
725 g_variant_builder_add(builder, "(s)", strdup((const char *)group));
728 sqlite3_finalize(stmt);
731 return STICKERD_SERVER_ERROR_NONE;
734 sqlite3_finalize(stmt);
737 return STICKERD_SERVER_ERROR_DB_FAILED;
740 int stickerd_db_get_keyword_list(GVariantBuilder *builder, char *app_id)
744 sqlite3_stmt *stmt = NULL;
748 return STICKERD_SERVER_ERROR_DB_FAILED;
750 ret = sqlite3_prepare_v2(db, STICKER_DB_GET_ALL_KEYWORD_LIST, -1, &stmt, NULL);
751 if (ret != SQLITE_OK) {
752 LOGE("fail to get keyword list : %s", sqlite3_errmsg(db));
756 sqlite3_bind_text(stmt, 1, app_id, -1, SQLITE_TRANSIENT);
758 while (sqlite3_step(stmt) == SQLITE_ROW) {
759 const unsigned char *keyword = sqlite3_column_text(stmt, 0);
761 g_variant_builder_add(builder, "(s)", strdup((const char *)keyword));
764 sqlite3_finalize(stmt);
767 return STICKERD_SERVER_ERROR_NONE;
770 sqlite3_finalize(stmt);
773 return STICKERD_SERVER_ERROR_DB_FAILED;
776 int stickerd_db_get_sticker_count(int *count, char *app_id)
780 sqlite3_stmt *stmt = NULL;
784 return STICKERD_SERVER_ERROR_DB_FAILED;
786 ret = sqlite3_prepare_v2(db, STICKER_DB_GET_STICKER_COUNT, -1, &stmt, NULL);
787 if (ret != SQLITE_OK) {
788 LOGE("fail to get sticker count : %s", sqlite3_errmsg(db));
792 sqlite3_bind_text(stmt, 1, app_id, -1, SQLITE_TRANSIENT);
794 ret = sqlite3_step(stmt);
795 if (ret == SQLITE_ERROR) {
796 LOGE("sqlite3_step() failed : ret(%d)", ret);
800 *count = sqlite3_column_int(stmt, 0);
802 sqlite3_finalize(stmt);
805 return STICKERD_SERVER_ERROR_NONE;
808 sqlite3_finalize(stmt);
811 return STICKERD_SERVER_ERROR_DB_FAILED;
814 int stickerd_db_get_record_id(sticker_info_db_type type, GList **id_list, void *data, char *app_id, int offset, int count)
818 sqlite3_stmt *stmt = NULL;
822 return STICKERD_SERVER_ERROR_DB_FAILED;
824 const char* query = _db_get_query(type, CMD_SELECT);
825 ret = sqlite3_prepare_v2(db, query, -1, &stmt, NULL);
826 if (ret != SQLITE_OK) {
827 LOGE("fail to get record id : %s", sqlite3_errmsg(db));
831 if (type == STICKER_DB_STICKER_ALL || type == STICKER_DB_STICKER_APPID) {
832 sqlite3_bind_text(stmt, 1, app_id, -1, SQLITE_TRANSIENT);
833 sqlite3_bind_int(stmt, 2, offset);
834 sqlite3_bind_int(stmt, 3, count);
836 if (type == STICKER_DB_STICKER_TYPE || type == STICKER_DB_STICKER_DISP_TYPE)
837 sqlite3_bind_int(stmt, 1, *(int *)data);
839 sqlite3_bind_text(stmt, 1, (char *)data, -1, SQLITE_TRANSIENT);
841 sqlite3_bind_text(stmt, 2, app_id, -1, SQLITE_TRANSIENT);
842 sqlite3_bind_int(stmt, 3, offset);
843 sqlite3_bind_int(stmt, 4, count);
846 while (sqlite3_step(stmt) == SQLITE_ROW) {
847 const unsigned char *tmp_id = sqlite3_column_text(stmt, 0);
849 *id_list = g_list_append(*id_list, strdup((const char *)tmp_id));
852 sqlite3_finalize(stmt);
855 return STICKERD_SERVER_ERROR_NONE;
858 sqlite3_finalize(stmt);
861 return STICKERD_SERVER_ERROR_DB_FAILED;
864 int stickerd_db_get_group_list_by_display_type(GVariantBuilder *builder, char *app_id, int disp_type)
868 sqlite3_stmt *stmt = NULL;
872 return STICKERD_SERVER_ERROR_DB_FAILED;
874 ret = sqlite3_prepare_v2(db, STICKER_DB_GET_GROUP_LIST_BY_DISP_TYPE, -1, &stmt, NULL);
875 if (ret != SQLITE_OK) {
876 LOGE("fail to get group list : %s", sqlite3_errmsg(db));
880 sqlite3_bind_int(stmt, 1, disp_type);
881 sqlite3_bind_text(stmt, 2, app_id, -1, SQLITE_TRANSIENT);
883 while (sqlite3_step(stmt) == SQLITE_ROW) {
884 const unsigned char *group = sqlite3_column_text(stmt, 0);
886 g_variant_builder_add(builder, "(s)", strdup((const char *)group));
889 sqlite3_finalize(stmt);
892 return STICKERD_SERVER_ERROR_NONE;
895 sqlite3_finalize(stmt);
898 return STICKERD_SERVER_ERROR_DB_FAILED;
901 int stickerd_db_check_file_exists(int *result, char *uri)
905 sqlite3_stmt *stmt = NULL;
909 return STICKERD_SERVER_ERROR_DB_FAILED;
911 ret = sqlite3_prepare_v2(db, STICKER_DB_CHECK_FILE_EXISTS, -1, &stmt, NULL);
912 if (ret != SQLITE_OK) {
913 LOGE("fail to check file exists : %s", sqlite3_errmsg(db));
917 sqlite3_bind_text(stmt, 1, uri, -1, SQLITE_TRANSIENT);
919 ret = sqlite3_step(stmt);
920 if (ret == SQLITE_ERROR) {
921 LOGE("sqlite3_step() failed : ret(%d)", ret);
925 *result = sqlite3_column_int(stmt, 0);
927 sqlite3_finalize(stmt);
930 return STICKERD_SERVER_ERROR_NONE;
933 sqlite3_finalize(stmt);
936 return STICKERD_SERVER_ERROR_DB_FAILED;