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_IMAGE_INFO_BY_RECORED_ID "SELECT type, uri, thumbnail FROM sticker_info WHERE sticker_info_id = ?"
79 #define STICKER_DB_GET_IMAGE_INFO_BY_URI "SELECT type, thumbnail FROM sticker_info WHERE uri = ?"
80 #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 = ?))"
81 #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 = ?)))"
82 #define STICKER_DB_GET_STICKER_COUNT "SELECT count(*) FROM sticker_info WHERE app_id = ?"
83 #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 ?, ?"
84 #define STICKER_DB_GET_RECORD_ID_BY_APP_ID "SELECT sticker_info_id from sticker_info WHERE app_id = ? LIMIT ?, ?"
85 #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 ?, ?"
86 #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 ?, ?"
87 #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 ?, ?"
88 #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 ?, ?"
89 #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 = ?))"
90 #define STICKER_DB_CHECK_FILE_EXISTS "SELECT EXISTS(SELECT 1 FROM sticker_info WHERE uri = ? LIMIT 1)"
98 static gboolean is_corrupted = FALSE;
100 static const char *_db_get_query(sticker_info_db_type sticker_type, command_type cmd_type)
102 static const char* query = NULL;
104 if (cmd_type == CMD_UPDATE) {
105 switch(sticker_type) {
106 case STICKER_DB_STICKER_TYPE:
107 query = STICKER_DB_UPDATE_STICKER_TYPE;
109 case STICKER_DB_STICKER_URI:
110 query = STICKER_DB_UPDATE_STICKER_URI;
112 case STICKER_DB_STICKER_THUMBNAIL:
113 query = STICKER_DB_UPDATE_STICKER_THUMBNAIL;
115 case STICKER_DB_STICKER_DESCRIPTION:
116 query = STICKER_DB_UPDATE_STICKER_DESCRIPTION;
118 case STICKER_DB_STICKER_GROUP:
119 query = STICKER_DB_UPDATE_STICKER_GROUP;
121 case STICKER_DB_STICKER_KEYWORD:
122 query = STICKER_DB_DELETE_STICKER_KEYWORD_INFO;
124 case STICKER_DB_STICKER_DISP_TYPE:
125 query = STICKER_DB_UPDATE_STICKER_DISP_TYPE;
131 } else if (cmd_type == CMD_SELECT) {
132 switch(sticker_type) {
133 case STICKER_DB_STICKER_ALL:
134 query = STICKER_DB_GET_ALL_RECORD_ID;
136 case STICKER_DB_STICKER_APPID:
137 query = STICKER_DB_GET_RECORD_ID_BY_APP_ID;
139 case STICKER_DB_STICKER_TYPE:
140 query = STICKER_DB_GET_RECORD_ID_BY_TYPE;
142 case STICKER_DB_STICKER_GROUP:
143 query = STICKER_DB_GET_RECORD_ID_BY_GROUP;
145 case STICKER_DB_STICKER_KEYWORD:
146 query = STICKER_DB_GET_RECORD_ID_BY_KEYWORD;
148 case STICKER_DB_STICKER_DISP_TYPE:
149 query = STICKER_DB_GET_RECORD_ID_BY_DISP_TYPE;
160 static int _recover_db(void)
162 int ret = STICKERD_SERVER_ERROR_NONE;
166 LOGD("Start to recover sticker db");
167 //Remove sticker database file
168 if (unlink(STICKER_DB_PATH) == -1)
169 LOGE("Failed to remove db file");
171 ret = sqlite3_open_v2(STICKER_DB_PATH, &db, SQLITE_OPEN_CREATE | SQLITE_OPEN_READWRITE, NULL);
172 if (ret != SQLITE_OK) {
173 LOGE("Failed to open db : %s", sqlite3_errmsg(db));
174 if (unlink(STICKER_DB_PATH) == -1)
175 LOGE("Failed to remove db file");
176 ret = STICKERD_SERVER_ERROR_DB_FAILED;
180 ret = sqlite3_exec(db, STICKER_INFO_CREATE_TABLE, NULL, NULL, &err);
181 if (ret != SQLITE_OK) {
182 LOGE("Failed to create sticker_info table : %s" , err);
183 ret = STICKERD_SERVER_ERROR_DB_FAILED;
187 ret = sqlite3_exec(db, STICKER_KEYWORD_INFO_CREATE_TABLE, NULL, NULL, &err);
188 if (ret != SQLITE_OK) {
189 LOGE("Failed to create sticker_keyword_info table : %s", err);
190 ret = STICKERD_SERVER_ERROR_DB_FAILED;
194 ret = sqlite3_exec(db, STICKER_WHITELIST_INFO_CREATE_TABLE, NULL, NULL, &err);
195 if (ret != SQLITE_OK) {
196 LOGE("Failed to create sticker_whitelist_info table : %s", err);
197 ret = STICKERD_SERVER_ERROR_DB_FAILED;
200 is_corrupted = FALSE;
212 static int _integrity_check_cb(void *pid, int argc, char **argv, char **notUsed)
214 if (strcmp(argv[0], "ok") != 0) {
215 LOGE("DB integrity check failed : %s", argv[0]);
220 LOGD("Result integrity : %s", argv[0]);
224 int stickerd_db_init(void)
226 int ret = STICKERD_SERVER_ERROR_NONE;
230 ret = sqlite3_open_v2(STICKER_DB_PATH, &db, SQLITE_OPEN_CREATE | SQLITE_OPEN_READWRITE, NULL);
231 if (ret != SQLITE_OK) {
232 LOGE("Failed to open db : %s", sqlite3_errmsg(db));
233 ret = STICKERD_SERVER_ERROR_DB_FAILED;
237 ret = sqlite3_exec(db, STICKER_INFO_CREATE_TABLE, NULL, NULL, &err);
238 if (ret != SQLITE_OK) {
239 LOGE("Failed to create sticker_info table : %s" , err);
240 ret = STICKERD_SERVER_ERROR_DB_FAILED;
244 ret = sqlite3_exec(db, STICKER_KEYWORD_INFO_CREATE_TABLE, NULL, NULL, &err);
245 if (ret != SQLITE_OK) {
246 LOGE("Failed to create sticker_keyword_info table : %s", err);
247 ret = STICKERD_SERVER_ERROR_DB_FAILED;
251 ret = sqlite3_exec(db, STICKER_WHITELIST_INFO_CREATE_TABLE, NULL, NULL, &err);
252 if (ret != SQLITE_OK) {
253 LOGE("Failed to create sticker_whitelist_info table : %s", err);
254 ret = STICKERD_SERVER_ERROR_DB_FAILED;
258 ret = sqlite3_exec(db, "PRAGMA journal_mode = WAL", NULL, NULL, &err);
259 if (ret != SQLITE_OK) {
260 LOGE("Failed to set journal_mode : %s", err);
261 ret = STICKERD_SERVER_ERROR_DB_FAILED;
265 ret = sqlite3_exec(db, "PRAGMA integrity_check", _integrity_check_cb, NULL, &err);
266 if (ret != SQLITE_OK) {
267 LOGE("Failed to check integrity : %s", err);
268 ret = STICKERD_SERVER_ERROR_DB_FAILED;
278 if (ret == SQLITE_CORRUPT || ret == SQLITE_NOTADB || is_corrupted)
284 static sqlite3 *_db_open(void)
290 ret = sqlite3_open(STICKER_DB_PATH, &db);
291 if (ret != SQLITE_OK) {
292 LOGE("Failed to open db : %s", sqlite3_errmsg(db));
296 ret = sqlite3_exec(db, "PRAGMA foreign_keys = ON", NULL, NULL, &err);
297 if (ret != SQLITE_OK) {
298 LOGE("Failed to turn on foreign keys : %s", err);
307 int stickerd_db_insert_sticker_info(int *record_id, sticker_info_db *sticker_info)
311 sqlite3_stmt *stmt = NULL;
315 return STICKERD_SERVER_ERROR_DB_FAILED;
317 ret = sqlite3_prepare_v2(db, STICKER_DB_INSERT_STICKER_INFO, -1, &stmt, NULL);
318 if (ret != SQLITE_OK) {
319 LOGE("fail to insert sticker information : %s", sqlite3_errmsg(db));
323 sqlite3_bind_text(stmt, 1, sticker_info->app_id, -1, SQLITE_TRANSIENT);
324 sqlite3_bind_int(stmt, 2, sticker_info->type);
325 sqlite3_bind_text(stmt, 3, sticker_info->uri, -1, SQLITE_TRANSIENT);
326 sqlite3_bind_text(stmt, 4, sticker_info->thumbnail, -1, SQLITE_TRANSIENT);
327 sqlite3_bind_text(stmt, 5, sticker_info->description, -1, SQLITE_TRANSIENT);
328 sqlite3_bind_text(stmt, 6, sticker_info->group, -1, SQLITE_TRANSIENT);
329 sqlite3_bind_int(stmt, 7, sticker_info->display_type);
331 ret = sqlite3_step(stmt);
332 if (ret != SQLITE_OK && ret != SQLITE_DONE) {
333 LOGE("sqlite3_step() failed : ret(%d)", ret);
335 } else if (sqlite3_changes(db) == 0) {
336 LOGE("No changes to DB");
340 sqlite3_finalize(stmt);
343 ret = sqlite3_prepare_v2(db, STICKER_DB_GET_LATEST_RECORD_ID, -1, &stmt, NULL);
344 if (ret != SQLITE_OK) {
345 LOGE("fail to get sticker id : %s", sqlite3_errmsg(db));
349 ret = sqlite3_step(stmt);
350 if (ret == SQLITE_ERROR) {
351 LOGE("sqlite3_step() failed : ret(%d)", ret);
355 *record_id = sqlite3_column_int(stmt, 0);
356 LOGD("record_id : %d", *record_id);
360 for(list = sticker_info->keyword; list != NULL; list=list->next) {
361 sqlite3_finalize(stmt);
364 ret = sqlite3_prepare_v2(db, STICKER_DB_INSERT_STICKER_KEYWORD_INFO, -1, &stmt, NULL);
365 if (ret != SQLITE_OK) {
366 LOGE("fail to insert sticker keyword : %s", sqlite3_errmsg(db));
370 sqlite3_bind_int(stmt, 1, *record_id);
371 sqlite3_bind_text(stmt, 2, (char *)list->data, -1, SQLITE_TRANSIENT);
373 ret = sqlite3_step(stmt);
374 if (ret != SQLITE_OK && ret != SQLITE_DONE) {
375 LOGE("sqlite3_step() failed : ret(%d)", ret);
377 } else if (sqlite3_changes(db) == 0) {
378 LOGE("No changes to DB");
383 sqlite3_finalize(stmt);
386 return STICKERD_SERVER_ERROR_NONE;
389 sqlite3_finalize(stmt);
392 return STICKERD_SERVER_ERROR_DB_FAILED;
395 int stickerd_db_delete_sticker_info(int record_id)
399 sqlite3_stmt *stmt = NULL;
403 return STICKERD_SERVER_ERROR_DB_FAILED;
405 ret = sqlite3_prepare_v2(db, STICKER_DB_GET_IMAGE_INFO_BY_RECORED_ID, -1, &stmt, NULL);
406 if (ret != SQLITE_OK) {
407 LOGE("fail to get image files : %s", sqlite3_errmsg(db));
411 sqlite3_bind_int(stmt, 1, record_id);
413 ret = sqlite3_step(stmt);
414 if (ret == SQLITE_ERROR) {
415 LOGE("sqlite3_step() failed : ret(%d)", ret);
419 int uri_type = sqlite3_column_int(stmt, 0);
420 const unsigned char *uri = sqlite3_column_text(stmt, 1);
421 const unsigned char *thumbnail = sqlite3_column_text(stmt, 2);
423 if (uri_type == 1 && unlink((const char *)uri) == -1)
424 LOGE("fail to delete sticker file");
426 if (thumbnail && unlink((const char *)thumbnail) == -1)
427 LOGE("fail to delete thumbnail image");
429 sqlite3_finalize(stmt);
432 ret = sqlite3_prepare_v2(db, STICKER_DB_DELETE_STICKER_INFO, -1, &stmt, NULL);
433 if (ret != SQLITE_OK) {
434 LOGE("fail to delete sticker information : %s", sqlite3_errmsg(db));
438 sqlite3_bind_int(stmt, 1, record_id);
440 ret = sqlite3_step(stmt);
441 if (ret != SQLITE_OK && ret != SQLITE_DONE) {
442 LOGE("sqlite3_step() failed : ret(%d)", ret);
444 } else if (sqlite3_changes(db) == 0) {
445 LOGE("No changes to DB");
449 sqlite3_finalize(stmt);
452 return STICKERD_SERVER_ERROR_NONE;
455 sqlite3_finalize(stmt);
458 return STICKERD_SERVER_ERROR_DB_FAILED;
461 int stickerd_db_delete_sticker_info_by_uri(char *uri)
465 sqlite3_stmt *stmt = NULL;
469 return STICKERD_SERVER_ERROR_DB_FAILED;
471 ret = sqlite3_prepare_v2(db, STICKER_DB_GET_IMAGE_INFO_BY_URI, -1, &stmt, NULL);
472 if (ret != SQLITE_OK) {
473 LOGE("fail to delete sticker information : %s", sqlite3_errmsg(db));
477 sqlite3_bind_text(stmt, 1, uri, -1, SQLITE_TRANSIENT);
479 ret = sqlite3_step(stmt);
480 if (ret == SQLITE_ERROR) {
481 LOGE("sqlite3_step() failed : ret(%d)", ret);
485 int uri_type = sqlite3_column_int(stmt, 0);
486 const unsigned char *thumbnail = sqlite3_column_text(stmt, 1);
488 if (uri_type == 1 && unlink((const char *)uri) == -1)
489 LOGE("fail to delete sticker file");
491 if (thumbnail && unlink((const char *)thumbnail) == -1)
492 LOGE("fail to delete thumbnail image");
494 sqlite3_finalize(stmt);
497 ret = sqlite3_prepare_v2(db, STICKER_DB_DELETE_STICKER_INFO_BY_URI, -1, &stmt, NULL);
498 if (ret != SQLITE_OK) {
499 LOGE("fail to delete sticker information : %s", sqlite3_errmsg(db));
503 sqlite3_bind_text(stmt, 1, uri, -1, SQLITE_TRANSIENT);
505 ret = sqlite3_step(stmt);
506 if (ret != SQLITE_OK && ret != SQLITE_DONE) {
507 LOGE("sqlite3_step() failed : ret(%d)", ret);
509 } else if (sqlite3_changes(db) == 0) {
510 LOGE("No changes to DB");
514 sqlite3_finalize(stmt);
517 return STICKERD_SERVER_ERROR_NONE;
520 sqlite3_finalize(stmt);
523 return STICKERD_SERVER_ERROR_DB_FAILED;
526 int stickerd_db_update_sticker_info(int record_id, sticker_info_db_type type, void *data)
530 sqlite3_stmt *stmt = NULL;
534 return STICKERD_SERVER_ERROR_DB_FAILED;
536 const char* query = _db_get_query(type, CMD_UPDATE);
537 ret = sqlite3_prepare_v2(db, query, -1, &stmt, NULL);
538 if (ret != SQLITE_OK) {
539 LOGE("fail to update sticker information : %s", sqlite3_errmsg(db));
543 if (type == STICKER_DB_STICKER_TYPE || type == STICKER_DB_STICKER_DISP_TYPE) {
544 sqlite3_bind_int(stmt, 1, *(int *)data);
545 } else if (type == STICKER_DB_STICKER_KEYWORD) {
546 sqlite3_bind_int(stmt, 1, record_id);
548 sqlite3_bind_text(stmt, 1, (char *)data, -1, SQLITE_TRANSIENT);
551 if (type != STICKER_DB_STICKER_KEYWORD)
552 sqlite3_bind_int(stmt, 2, record_id);
554 ret = sqlite3_step(stmt);
555 if (ret != SQLITE_OK && ret != SQLITE_DONE) {
556 LOGE("sqlite3_step() failed : ret(%d)", ret);
560 if (type == STICKER_DB_STICKER_KEYWORD) {
562 for(list = (GList *)data; list != NULL; list=list->next) {
563 sqlite3_finalize(stmt);
566 ret = sqlite3_prepare_v2(db, STICKER_DB_INSERT_STICKER_KEYWORD_INFO, -1, &stmt, NULL);
567 if (ret != SQLITE_OK) {
568 LOGE("fail to insert sticker information : %s", sqlite3_errmsg(db));
572 sqlite3_bind_int(stmt, 1, record_id);
573 sqlite3_bind_text(stmt, 2, (char *)list->data, -1, SQLITE_TRANSIENT);
575 ret = sqlite3_step(stmt);
576 if (ret != SQLITE_OK && ret != SQLITE_DONE) {
577 LOGE("sqlite3_step() failed : ret(%d)", ret);
579 } else if (sqlite3_changes(db) == 0) {
580 LOGE("No changes to DB");
586 sqlite3_finalize(stmt);
589 return STICKERD_SERVER_ERROR_NONE;
592 sqlite3_finalize(stmt);
595 return STICKERD_SERVER_ERROR_DB_FAILED;
598 int stickerd_db_get_sticker_info_by_record_id(int record_id, sticker_info_db *sticker_info)
602 sqlite3_stmt *stmt = NULL;
606 return STICKERD_SERVER_ERROR_DB_FAILED;
608 ret = sqlite3_prepare_v2(db, STICKER_DB_GET_STICKER_INFO_BY_RECORD_ID, -1, &stmt, NULL);
609 if (ret != SQLITE_OK) {
610 LOGE("fail to get sticker information : %s", sqlite3_errmsg(db));
614 sqlite3_bind_int(stmt, 1, record_id);
616 ret = sqlite3_step(stmt);
617 if (ret == SQLITE_ERROR) {
618 LOGE("sqlite3_step() failed : ret(%d)", ret);
622 const unsigned char *tmp_app_id = sqlite3_column_text(stmt, 1);
624 sticker_info->app_id = strdup((const char *)tmp_app_id);
626 LOGW("invalid record_id : %d", record_id);
630 sticker_info->type = sqlite3_column_int(stmt, 2);
632 const unsigned char *tmp_uri = sqlite3_column_text(stmt, 3);
634 sticker_info->uri = strdup((const char *)tmp_uri);
636 const unsigned char *tmp_thumbnail = sqlite3_column_text(stmt, 4);
638 sticker_info->thumbnail = strdup((const char *)tmp_thumbnail);
640 const unsigned char *tmp_description = sqlite3_column_text(stmt, 5);
642 sticker_info->description = strdup((const char *)tmp_description);
644 const unsigned char *tmp_group = sqlite3_column_text(stmt, 6);
646 sticker_info->group = strdup((const char *)tmp_group);
648 const unsigned char *tmp_date = sqlite3_column_text(stmt, 7);
650 sticker_info->date = strdup((const char *)tmp_date);
652 sticker_info->display_type = sqlite3_column_int(stmt, 8);
654 sqlite3_finalize(stmt);
657 ret = sqlite3_prepare_v2(db, STICKER_DB_GET_KEYWORD_INFO_BY_RECORD_ID, -1, &stmt, NULL);
658 if (ret != SQLITE_OK) {
659 LOGE("fail to get sticker keyword : %s", sqlite3_errmsg(db));
663 sqlite3_bind_int(stmt, 1, record_id);
665 while (sqlite3_step(stmt) == SQLITE_ROW) {
666 const unsigned char *keyword = sqlite3_column_text(stmt, 0);
668 sticker_info->keyword = g_list_append(sticker_info->keyword, strdup((const char *)keyword));
671 sqlite3_finalize(stmt);
674 return STICKERD_SERVER_ERROR_NONE;
677 sqlite3_finalize(stmt);
680 return STICKERD_SERVER_ERROR_DB_FAILED;
683 int stickerd_db_get_group_list(GVariantBuilder *builder, char *app_id)
687 sqlite3_stmt *stmt = NULL;
691 return STICKERD_SERVER_ERROR_DB_FAILED;
693 ret = sqlite3_prepare_v2(db, STICKER_DB_GET_ALL_GROUP_LIST, -1, &stmt, NULL);
694 if (ret != SQLITE_OK) {
695 LOGE("fail to get group list : %s", sqlite3_errmsg(db));
699 sqlite3_bind_text(stmt, 1, app_id, -1, SQLITE_TRANSIENT);
701 while (sqlite3_step(stmt) == SQLITE_ROW) {
702 const unsigned char *group = sqlite3_column_text(stmt, 0);
704 g_variant_builder_add(builder, "(s)", strdup((const char *)group));
707 sqlite3_finalize(stmt);
710 return STICKERD_SERVER_ERROR_NONE;
713 sqlite3_finalize(stmt);
716 return STICKERD_SERVER_ERROR_DB_FAILED;
719 int stickerd_db_get_keyword_list(GVariantBuilder *builder, char *app_id)
723 sqlite3_stmt *stmt = NULL;
727 return STICKERD_SERVER_ERROR_DB_FAILED;
729 ret = sqlite3_prepare_v2(db, STICKER_DB_GET_ALL_KEYWORD_LIST, -1, &stmt, NULL);
730 if (ret != SQLITE_OK) {
731 LOGE("fail to get keyword list : %s", sqlite3_errmsg(db));
735 sqlite3_bind_text(stmt, 1, app_id, -1, SQLITE_TRANSIENT);
737 while (sqlite3_step(stmt) == SQLITE_ROW) {
738 const unsigned char *keyword = sqlite3_column_text(stmt, 0);
740 g_variant_builder_add(builder, "(s)", strdup((const char *)keyword));
743 sqlite3_finalize(stmt);
746 return STICKERD_SERVER_ERROR_NONE;
749 sqlite3_finalize(stmt);
752 return STICKERD_SERVER_ERROR_DB_FAILED;
755 int stickerd_db_get_sticker_count(int *count, char *app_id)
759 sqlite3_stmt *stmt = NULL;
763 return STICKERD_SERVER_ERROR_DB_FAILED;
765 ret = sqlite3_prepare_v2(db, STICKER_DB_GET_STICKER_COUNT, -1, &stmt, NULL);
766 if (ret != SQLITE_OK) {
767 LOGE("fail to get sticker count : %s", sqlite3_errmsg(db));
771 sqlite3_bind_text(stmt, 1, app_id, -1, SQLITE_TRANSIENT);
773 ret = sqlite3_step(stmt);
774 if (ret == SQLITE_ERROR) {
775 LOGE("sqlite3_step() failed : ret(%d)", ret);
779 *count = sqlite3_column_int(stmt, 0);
781 sqlite3_finalize(stmt);
784 return STICKERD_SERVER_ERROR_NONE;
787 sqlite3_finalize(stmt);
790 return STICKERD_SERVER_ERROR_DB_FAILED;
793 int stickerd_db_get_record_id(sticker_info_db_type type, GList **id_list, void *data, char *app_id, int offset, int count)
797 sqlite3_stmt *stmt = NULL;
801 return STICKERD_SERVER_ERROR_DB_FAILED;
803 const char* query = _db_get_query(type, CMD_SELECT);
804 ret = sqlite3_prepare_v2(db, query, -1, &stmt, NULL);
805 if (ret != SQLITE_OK) {
806 LOGE("fail to get record id : %s", sqlite3_errmsg(db));
810 if (type == STICKER_DB_STICKER_ALL || type == STICKER_DB_STICKER_APPID) {
811 sqlite3_bind_text(stmt, 1, app_id, -1, SQLITE_TRANSIENT);
812 sqlite3_bind_int(stmt, 2, offset);
813 sqlite3_bind_int(stmt, 3, count);
815 if (type == STICKER_DB_STICKER_TYPE || type == STICKER_DB_STICKER_DISP_TYPE)
816 sqlite3_bind_int(stmt, 1, *(int *)data);
818 sqlite3_bind_text(stmt, 1, (char *)data, -1, SQLITE_TRANSIENT);
820 sqlite3_bind_text(stmt, 2, app_id, -1, SQLITE_TRANSIENT);
821 sqlite3_bind_int(stmt, 3, offset);
822 sqlite3_bind_int(stmt, 4, count);
825 while (sqlite3_step(stmt) == SQLITE_ROW) {
826 const unsigned char *tmp_id = sqlite3_column_text(stmt, 0);
828 *id_list = g_list_append(*id_list, strdup((const char *)tmp_id));
831 sqlite3_finalize(stmt);
834 return STICKERD_SERVER_ERROR_NONE;
837 sqlite3_finalize(stmt);
840 return STICKERD_SERVER_ERROR_DB_FAILED;
843 int stickerd_db_get_group_list_by_display_type(GVariantBuilder *builder, char *app_id, int disp_type)
847 sqlite3_stmt *stmt = NULL;
851 return STICKERD_SERVER_ERROR_DB_FAILED;
853 ret = sqlite3_prepare_v2(db, STICKER_DB_GET_GROUP_LIST_BY_DISP_TYPE, -1, &stmt, NULL);
854 if (ret != SQLITE_OK) {
855 LOGE("fail to get group list : %s", sqlite3_errmsg(db));
859 sqlite3_bind_int(stmt, 1, disp_type);
860 sqlite3_bind_text(stmt, 2, app_id, -1, SQLITE_TRANSIENT);
862 while (sqlite3_step(stmt) == SQLITE_ROW) {
863 const unsigned char *group = sqlite3_column_text(stmt, 0);
865 g_variant_builder_add(builder, "(s)", strdup((const char *)group));
868 sqlite3_finalize(stmt);
871 return STICKERD_SERVER_ERROR_NONE;
874 sqlite3_finalize(stmt);
877 return STICKERD_SERVER_ERROR_DB_FAILED;
880 int stickerd_db_check_file_exists(int *result, char *uri)
884 sqlite3_stmt *stmt = NULL;
888 return STICKERD_SERVER_ERROR_DB_FAILED;
890 ret = sqlite3_prepare_v2(db, STICKER_DB_CHECK_FILE_EXISTS, -1, &stmt, NULL);
891 if (ret != SQLITE_OK) {
892 LOGE("fail to check file exists : %s", sqlite3_errmsg(db));
896 sqlite3_bind_text(stmt, 1, uri, -1, SQLITE_TRANSIENT);
898 ret = sqlite3_step(stmt);
899 if (ret == SQLITE_ERROR) {
900 LOGE("sqlite3_step() failed : ret(%d)", ret);
904 *result = sqlite3_column_int(stmt, 0);
906 sqlite3_finalize(stmt);
909 return STICKERD_SERVER_ERROR_NONE;
912 sqlite3_finalize(stmt);
915 return STICKERD_SERVER_ERROR_DB_FAILED;