2 * Copyright (c) 2019 Samsung Electronics Co., Ltd All Rights Reserved
4 * Licensed under the Apache License, Version 2.0 (the License);
5 * you may not use this file except in compliance with the License.
6 * You may obtain a copy of the License at
8 * http://www.apache.org/licenses/LICENSE-2.0
10 * Unless required by applicable law or agreed to in writing, software
11 * distributed under the License is distributed on an AS IS BASIS,
12 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13 * See the License for the specific language governing permissions and
14 * limitations under the License.
22 #include "stickerd_db_manager.h"
23 #include "stickerd_error.h"
28 #define LOG_TAG "STICKERD_DB_MANAGER"
34 * +-----------------+--------+------+------+-----------+-------------+------------+------+--------------+
35 * | INT | TEXT | INT | TEXT | TEXT | TEXT | TEXT | TEXT | INT |
36 * +-----------------+--------+------+------+-----------+-------------+------------+------+--------------+
37 * | sticker_info_id | app_id | type | uri | thumbnail | description | group_name | date | display_type |
38 * +-----------------+--------+------+------+-----------+-------------+------------+------+--------------+
40 * sticker_keyword_info
41 * +------------+-----------------+---------+
42 * | INT | INT | TEXT |
43 * +------------+-----------------+---------+
44 * | keyword_id | sticker_info_id | keyword |
45 * +------------+-----------------+---------+
47 * sticker_whitelist_info
48 * +--------------+-------------+-------------+
49 * | INT | TEXT | TEXT |
50 * +--------------+-------------+-------------+
51 * | whitelist_id | provider_id | consumer_id |
52 * +------------+---------------+-------------+
54 * sticker_recent_history_info
55 * +------------+-----------------+-------+-----------+
56 * | INT | INT | INT | TEXT |
57 * +------------+-----------------+-------+-----------+
58 * | history_id | sticker_info_id | count | timestamp |
59 * +------------+-----------------+-------+-----------+
63 #define STICKER_DB_PATH tzplatform_mkpath(TZ_SYS_DB, ".sticker_info.db")
64 #define STICKER_INFO_CREATE_TABLE "CREATE TABLE IF NOT EXISTS sticker_info(sticker_info_id INTEGER PRIMARY KEY AUTOINCREMENT, app_id TEXT NOT NULL, type INTEGER NOT NULL, uri TEXT NOT NULL, thumbnail TEXT, description TEXT, group_name TEXT NOT NULL, date TEXT NOT NULL, display_type INTEGER)"
65 #define STICKER_KEYWORD_INFO_CREATE_TABLE "CREATE TABLE IF NOT EXISTS sticker_keyword_info(keyword_id INTEGER PRIMARY KEY AUTOINCREMENT, sticker_info_id INTEGER, keyword TEXT NOT NULL, FOREIGN KEY (sticker_info_id) REFERENCES sticker_info(sticker_info_id) ON DELETE CASCADE)"
66 #define STICKER_WHITELIST_INFO_CREATE_TABLE "CREATE TABLE IF NOT EXISTS sticker_whitelist_info(whitelist_id INTEGER PRIMARY KEY AUTOINCREMENT, provider_id TEXT NOT NULL, consumer_id TEXT NOT NULL)"
67 #define STICKER_RECENT_HISTORY_INFO_CREATE_TABLE "CREATE TABLE IF NOT EXISTS sticker_recent_history_info(history_id INTEGER PRIMARY KEY AUTOINCREMENT, sticker_info_id INTEGER, count INTEGER NOT NULL, timestamp TEXT NOT NULL, FOREIGN KEY (sticker_info_id) REFERENCES sticker_info(sticker_info_id) ON DELETE CASCADE)"
69 #define STICKER_DB_INSERT_STICKER_INFO "INSERT INTO sticker_info (app_id, type, uri, thumbnail, description, group_name, date, display_type) VALUES (?, ?, ?, ?, ?, ?, DateTime('now','localtime'), ?)"
70 #define STICKER_DB_INSERT_STICKER_KEYWORD_INFO "INSERT INTO sticker_keyword_info (sticker_info_id, keyword) VALUES (?, ?)"
71 #define STICKER_DB_INSERT_RECENT_HISTORY "INSERT INTO sticker_recent_history_info (sticker_info_id, count, timestamp) VALUES (?, 1, DateTime('now','localtime'))"
73 #define STICKER_DB_DELETE_STICKER_INFO "DELETE FROM sticker_info WHERE sticker_info_id = ?"
74 #define STICKER_DB_DELETE_STICKER_KEYWORD_INFO "DELETE FROM sticker_keyword_info WHERE sticker_info_id = ?"
75 #define STICKER_DB_DELETE_STICKER_INFO_BY_URI "DELETE FROM sticker_info WHERE uri = ?"
77 #define STICKER_DB_UPDATE_STICKER_TYPE "UPDATE sticker_info SET type = ?, date = DateTime('now','localtime') WHERE sticker_info_id = ?"
78 #define STICKER_DB_UPDATE_STICKER_URI "UPDATE sticker_info SET uri = ?, date = DateTime('now','localtime') WHERE sticker_info_id = ?"
79 #define STICKER_DB_UPDATE_STICKER_THUMBNAIL "UPDATE sticker_info SET thumbnail = ?, date = DateTime('now','localtime') WHERE sticker_info_id = ?"
80 #define STICKER_DB_UPDATE_STICKER_DESCRIPTION "UPDATE sticker_info SET description = ?, date = DateTime('now','localtime') WHERE sticker_info_id = ?"
81 #define STICKER_DB_UPDATE_STICKER_GROUP "UPDATE sticker_info SET group_name = ?, date = DateTime('now','localtime') WHERE sticker_info_id = ?"
82 #define STICKER_DB_UPDATE_STICKER_DISP_TYPE "UPDATE sticker_info SET display_type = ?, date = DateTime('now','localtime') WHERE sticker_info_id = ?"
83 #define STICKER_DB_UPDATE_RECENT_HISTORY "UPDATE sticker_recent_history_info SET count = count + 1, timestamp = DateTime('now','localtime') WHERE sticker_info_id = ?"
85 #define STICKER_DB_GET_LATEST_RECORD_ID "SELECT sticker_info_id FROM sticker_info ORDER BY sticker_info_id DESC LIMIT 1"
86 #define STICKER_DB_GET_STICKER_INFO_BY_RECORD_ID "SELECT * FROM sticker_info WHERE sticker_info_id = ?"
87 #define STICKER_DB_GET_KEYWORD_INFO_BY_RECORD_ID "SELECT keyword FROM sticker_keyword_info WHERE sticker_info_id = ?"
88 #define STICKER_DB_GET_IMAGE_INFO_BY_RECORED_ID "SELECT type, uri, thumbnail FROM sticker_info WHERE sticker_info_id = ?"
89 #define STICKER_DB_GET_IMAGE_INFO_BY_URI "SELECT type, thumbnail FROM sticker_info WHERE uri = ?"
90 #define STICKER_DB_GET_ALL_GROUP_LIST "SELECT DISTINCT group_name FROM sticker_info WHERE app_id NOT IN (SELECT DISTINCT provider_id FROM sticker_whitelist_info WHERE provider_id NOT IN (SELECT provider_id FROM sticker_whitelist_info WHERE consumer_id = ?))"
91 #define STICKER_DB_GET_ALL_KEYWORD_LIST "SELECT DISTINCT keyword FROM sticker_keyword_info WHERE sticker_info_id IN (SELECT sticker_info_id from sticker_info WHERE app_id NOT IN (SELECT DISTINCT provider_id FROM sticker_whitelist_info WHERE provider_id NOT IN (SELECT provider_id FROM sticker_whitelist_info WHERE consumer_id = ?)))"
92 #define STICKER_DB_GET_STICKER_COUNT "SELECT count(*) FROM sticker_info WHERE app_id = ?"
93 #define STICKER_DB_GET_ALL_RECORD_ID "SELECT sticker_info_id FROM sticker_info WHERE app_id NOT IN (SELECT DISTINCT provider_id FROM sticker_whitelist_info WHERE provider_id NOT IN (SELECT provider_id FROM sticker_whitelist_info WHERE consumer_id = ?)) ORDER BY sticker_info_id DESC LIMIT ?, ?"
94 #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 ?, ?"
95 #define STICKER_DB_GET_RECORD_ID_BY_TYPE "SELECT sticker_info_id FROM sticker_info WHERE type = ? AND app_id NOT IN (SELECT DISTINCT provider_id FROM sticker_whitelist_info WHERE provider_id NOT IN (SELECT provider_id FROM sticker_whitelist_info WHERE consumer_id = ?)) ORDER BY sticker_info_id DESC LIMIT ?, ?"
96 #define STICKER_DB_GET_RECORD_ID_BY_GROUP "SELECT sticker_info_id from sticker_info WHERE group_name = ? AND app_id NOT IN (SELECT DISTINCT provider_id FROM sticker_whitelist_info WHERE provider_id NOT IN (SELECT provider_id FROM sticker_whitelist_info WHERE consumer_id = ?)) ORDER BY sticker_info_id DESC LIMIT ?, ?"
97 #define STICKER_DB_GET_RECORD_ID_BY_KEYWORD "SELECT sticker_info_id FROM sticker_keyword_info WHERE keyword = ? INTERSECT SELECT sticker_info_id from sticker_info WHERE app_id NOT IN (SELECT DISTINCT provider_id FROM sticker_whitelist_info WHERE provider_id NOT IN (SELECT provider_id FROM sticker_whitelist_info WHERE consumer_id = ?)) ORDER BY sticker_info_id DESC LIMIT ?, ?"
98 #define STICKER_DB_GET_RECORD_ID_BY_DISP_TYPE "SELECT sticker_info_id FROM sticker_info WHERE display_type = ? AND app_id NOT IN (SELECT DISTINCT provider_id FROM sticker_whitelist_info WHERE provider_id NOT IN (SELECT provider_id FROM sticker_whitelist_info WHERE consumer_id = ?)) ORDER BY sticker_info_id DESC LIMIT ?, ?"
99 #define STICKER_DB_GET_GROUP_LIST_BY_DISP_TYPE "SELECT DISTINCT group_name FROM sticker_info WHERE display_type = ? AND app_id NOT IN (SELECT DISTINCT provider_id FROM sticker_whitelist_info WHERE provider_id NOT IN (SELECT provider_id FROM sticker_whitelist_info WHERE consumer_id = ?))"
100 #define STICKER_DB_CHECK_FILE_EXISTS "SELECT EXISTS(SELECT 1 FROM sticker_info WHERE uri = ? LIMIT 1)"
101 #define STICKER_DB_CHECK_RECENT_HISTORY_EXISTS "SELECT EXISTS(SELECT 1 FROM sticker_recent_history_info WHERE sticker_info_id = ? LIMIT 1)"
102 #define STICKER_DB_GET_RECENT_HISTORY "SELECT sticker_info_id FROM sticker_recent_history_info ORDER BY datetime(timestamp) DESC LIMIT ?"
103 #define STICKER_DB_GET_STICKER_INFO_BY_URI "SELECT * FROM sticker_info WHERE uri = ?"
111 static gboolean is_corrupted = FALSE;
113 static const char *_db_get_query(sticker_info_db_type sticker_type, command_type cmd_type)
115 static const char* query = NULL;
117 if (cmd_type == CMD_UPDATE) {
118 switch(sticker_type) {
119 case STICKER_DB_STICKER_TYPE:
120 query = STICKER_DB_UPDATE_STICKER_TYPE;
122 case STICKER_DB_STICKER_URI:
123 query = STICKER_DB_UPDATE_STICKER_URI;
125 case STICKER_DB_STICKER_THUMBNAIL:
126 query = STICKER_DB_UPDATE_STICKER_THUMBNAIL;
128 case STICKER_DB_STICKER_DESCRIPTION:
129 query = STICKER_DB_UPDATE_STICKER_DESCRIPTION;
131 case STICKER_DB_STICKER_GROUP:
132 query = STICKER_DB_UPDATE_STICKER_GROUP;
134 case STICKER_DB_STICKER_KEYWORD:
135 query = STICKER_DB_DELETE_STICKER_KEYWORD_INFO;
137 case STICKER_DB_STICKER_DISP_TYPE:
138 query = STICKER_DB_UPDATE_STICKER_DISP_TYPE;
144 } else if (cmd_type == CMD_SELECT) {
145 switch(sticker_type) {
146 case STICKER_DB_STICKER_ALL:
147 query = STICKER_DB_GET_ALL_RECORD_ID;
149 case STICKER_DB_STICKER_APPID:
150 query = STICKER_DB_GET_RECORD_ID_BY_APP_ID;
152 case STICKER_DB_STICKER_TYPE:
153 query = STICKER_DB_GET_RECORD_ID_BY_TYPE;
155 case STICKER_DB_STICKER_GROUP:
156 query = STICKER_DB_GET_RECORD_ID_BY_GROUP;
158 case STICKER_DB_STICKER_KEYWORD:
159 query = STICKER_DB_GET_RECORD_ID_BY_KEYWORD;
161 case STICKER_DB_STICKER_DISP_TYPE:
162 query = STICKER_DB_GET_RECORD_ID_BY_DISP_TYPE;
164 case STICKER_DB_STICKER_RECENT_HISTORY:
165 query = STICKER_DB_GET_RECENT_HISTORY;
176 static int _recover_db(void)
178 int ret = STICKERD_SERVER_ERROR_NONE;
182 LOGD("Start to recover sticker db");
183 //Remove sticker database file
184 if (unlink(STICKER_DB_PATH) == -1)
185 LOGE("Failed to remove db file");
187 ret = sqlite3_open_v2(STICKER_DB_PATH, &db, SQLITE_OPEN_CREATE | SQLITE_OPEN_READWRITE, NULL);
188 if (ret != SQLITE_OK) {
189 LOGE("Failed to open db : %s", sqlite3_errmsg(db));
190 if (unlink(STICKER_DB_PATH) == -1)
191 LOGE("Failed to remove db file");
192 ret = STICKERD_SERVER_ERROR_DB_FAILED;
196 ret = sqlite3_exec(db, STICKER_INFO_CREATE_TABLE, NULL, NULL, &err);
197 if (ret != SQLITE_OK) {
198 LOGE("Failed to create sticker_info table : %s" , err);
199 ret = STICKERD_SERVER_ERROR_DB_FAILED;
203 ret = sqlite3_exec(db, STICKER_KEYWORD_INFO_CREATE_TABLE, NULL, NULL, &err);
204 if (ret != SQLITE_OK) {
205 LOGE("Failed to create sticker_keyword_info table : %s", err);
206 ret = STICKERD_SERVER_ERROR_DB_FAILED;
210 ret = sqlite3_exec(db, STICKER_WHITELIST_INFO_CREATE_TABLE, NULL, NULL, &err);
211 if (ret != SQLITE_OK) {
212 LOGE("Failed to create sticker_whitelist_info table : %s", err);
213 ret = STICKERD_SERVER_ERROR_DB_FAILED;
217 ret = sqlite3_exec(db, STICKER_RECENT_HISTORY_INFO_CREATE_TABLE, NULL, NULL, &err);
218 if (ret != SQLITE_OK) {
219 LOGE("Failed to create sticker_recent_history_info table : %s", err);
220 ret = STICKERD_SERVER_ERROR_DB_FAILED;
224 is_corrupted = FALSE;
236 static int _integrity_check_cb(void *pid, int argc, char **argv, char **notUsed)
238 if (strcmp(argv[0], "ok") != 0) {
239 LOGE("DB integrity check failed : %s", argv[0]);
244 LOGD("Result integrity : %s", argv[0]);
248 int stickerd_db_init(void)
250 int ret = STICKERD_SERVER_ERROR_NONE;
254 ret = sqlite3_open_v2(STICKER_DB_PATH, &db, SQLITE_OPEN_CREATE | SQLITE_OPEN_READWRITE, NULL);
255 if (ret != SQLITE_OK) {
256 LOGE("Failed to open db : %s", sqlite3_errmsg(db));
257 ret = STICKERD_SERVER_ERROR_DB_FAILED;
261 ret = sqlite3_exec(db, STICKER_INFO_CREATE_TABLE, NULL, NULL, &err);
262 if (ret != SQLITE_OK) {
263 LOGE("Failed to create sticker_info table : %s" , err);
264 ret = STICKERD_SERVER_ERROR_DB_FAILED;
268 ret = sqlite3_exec(db, STICKER_KEYWORD_INFO_CREATE_TABLE, NULL, NULL, &err);
269 if (ret != SQLITE_OK) {
270 LOGE("Failed to create sticker_keyword_info table : %s", err);
271 ret = STICKERD_SERVER_ERROR_DB_FAILED;
275 ret = sqlite3_exec(db, STICKER_WHITELIST_INFO_CREATE_TABLE, NULL, NULL, &err);
276 if (ret != SQLITE_OK) {
277 LOGE("Failed to create sticker_whitelist_info table : %s", err);
278 ret = STICKERD_SERVER_ERROR_DB_FAILED;
282 ret = sqlite3_exec(db, STICKER_RECENT_HISTORY_INFO_CREATE_TABLE, NULL, NULL, &err);
283 if (ret != SQLITE_OK) {
284 LOGE("Failed to create sticker_recent_history_info table : %s", err);
285 ret = STICKERD_SERVER_ERROR_DB_FAILED;
289 ret = sqlite3_exec(db, "PRAGMA journal_mode = WAL", NULL, NULL, &err);
290 if (ret != SQLITE_OK) {
291 LOGE("Failed to set journal_mode : %s", err);
292 ret = STICKERD_SERVER_ERROR_DB_FAILED;
296 ret = sqlite3_exec(db, "PRAGMA integrity_check", _integrity_check_cb, NULL, &err);
297 if (ret != SQLITE_OK) {
298 LOGE("Failed to check integrity : %s", err);
299 ret = STICKERD_SERVER_ERROR_DB_FAILED;
309 if (ret == SQLITE_CORRUPT || ret == SQLITE_NOTADB || is_corrupted)
315 static sqlite3 *_db_open(void)
321 if (is_corrupted && _recover_db() != SQLITE_OK)
324 ret = sqlite3_open(STICKER_DB_PATH, &db);
325 if (ret != SQLITE_OK) {
326 LOGE("Failed to open db : %s", sqlite3_errmsg(db));
330 ret = sqlite3_exec(db, "PRAGMA foreign_keys = ON", NULL, NULL, &err);
331 if (ret != SQLITE_OK) {
332 LOGE("Failed to turn on foreign keys : %s", err);
341 int stickerd_db_insert_sticker_info(int *record_id, sticker_info_db *sticker_info)
345 sqlite3_stmt *stmt = NULL;
349 return STICKERD_SERVER_ERROR_DB_FAILED;
351 ret = sqlite3_prepare_v2(db, STICKER_DB_INSERT_STICKER_INFO, -1, &stmt, NULL);
352 if (ret != SQLITE_OK) {
353 LOGE("fail to insert sticker information : %s", sqlite3_errmsg(db));
357 sqlite3_bind_text(stmt, 1, sticker_info->app_id, -1, SQLITE_TRANSIENT);
358 sqlite3_bind_int(stmt, 2, sticker_info->type);
359 sqlite3_bind_text(stmt, 3, sticker_info->uri, -1, SQLITE_TRANSIENT);
360 sqlite3_bind_text(stmt, 4, sticker_info->thumbnail, -1, SQLITE_TRANSIENT);
361 sqlite3_bind_text(stmt, 5, sticker_info->description, -1, SQLITE_TRANSIENT);
362 sqlite3_bind_text(stmt, 6, sticker_info->group, -1, SQLITE_TRANSIENT);
363 sqlite3_bind_int(stmt, 7, sticker_info->display_type);
365 ret = sqlite3_step(stmt);
366 if (ret != SQLITE_OK && ret != SQLITE_DONE) {
367 LOGE("sqlite3_step() failed : ret(%d)", ret);
369 } else if (sqlite3_changes(db) == 0) {
370 LOGE("No changes to DB");
374 sqlite3_finalize(stmt);
377 ret = sqlite3_prepare_v2(db, STICKER_DB_GET_LATEST_RECORD_ID, -1, &stmt, NULL);
378 if (ret != SQLITE_OK) {
379 LOGE("fail to get sticker id : %s", sqlite3_errmsg(db));
383 ret = sqlite3_step(stmt);
384 if (ret == SQLITE_ERROR) {
385 LOGE("sqlite3_step() failed : ret(%d)", ret);
389 *record_id = sqlite3_column_int(stmt, 0);
390 LOGD("record_id : %d", *record_id);
394 for(list = sticker_info->keyword; list != NULL; list=list->next) {
395 sqlite3_finalize(stmt);
398 ret = sqlite3_prepare_v2(db, STICKER_DB_INSERT_STICKER_KEYWORD_INFO, -1, &stmt, NULL);
399 if (ret != SQLITE_OK) {
400 LOGE("fail to insert sticker keyword : %s", sqlite3_errmsg(db));
404 sqlite3_bind_int(stmt, 1, *record_id);
405 sqlite3_bind_text(stmt, 2, (char *)list->data, -1, SQLITE_TRANSIENT);
407 ret = sqlite3_step(stmt);
408 if (ret != SQLITE_OK && ret != SQLITE_DONE) {
409 LOGE("sqlite3_step() failed : ret(%d)", ret);
411 } else if (sqlite3_changes(db) == 0) {
412 LOGE("No changes to DB");
417 sqlite3_finalize(stmt);
420 return STICKERD_SERVER_ERROR_NONE;
423 sqlite3_finalize(stmt);
426 return STICKERD_SERVER_ERROR_DB_FAILED;
429 int stickerd_db_delete_sticker_info(int record_id)
433 sqlite3_stmt *stmt = NULL;
437 return STICKERD_SERVER_ERROR_DB_FAILED;
439 ret = sqlite3_prepare_v2(db, STICKER_DB_GET_IMAGE_INFO_BY_RECORED_ID, -1, &stmt, NULL);
440 if (ret != SQLITE_OK) {
441 LOGE("fail to get image files : %s", sqlite3_errmsg(db));
445 sqlite3_bind_int(stmt, 1, record_id);
447 ret = sqlite3_step(stmt);
448 if (ret == SQLITE_ERROR) {
449 LOGE("sqlite3_step() failed : ret(%d)", ret);
453 int uri_type = sqlite3_column_int(stmt, 0);
454 const unsigned char *uri = sqlite3_column_text(stmt, 1);
455 const unsigned char *thumbnail = sqlite3_column_text(stmt, 2);
457 if (uri_type == 1 && unlink((const char *)uri) == -1)
458 LOGE("fail to delete sticker file");
460 if (thumbnail && unlink((const char *)thumbnail) == -1)
461 LOGE("fail to delete thumbnail image");
463 sqlite3_finalize(stmt);
466 ret = sqlite3_prepare_v2(db, STICKER_DB_DELETE_STICKER_INFO, -1, &stmt, NULL);
467 if (ret != SQLITE_OK) {
468 LOGE("fail to delete sticker information : %s", sqlite3_errmsg(db));
472 sqlite3_bind_int(stmt, 1, record_id);
474 ret = sqlite3_step(stmt);
475 if (ret != SQLITE_OK && ret != SQLITE_DONE) {
476 LOGE("sqlite3_step() failed : ret(%d)", ret);
478 } else if (sqlite3_changes(db) == 0) {
479 LOGE("No changes to DB");
483 sqlite3_finalize(stmt);
486 return STICKERD_SERVER_ERROR_NONE;
489 sqlite3_finalize(stmt);
492 return STICKERD_SERVER_ERROR_DB_FAILED;
495 int stickerd_db_delete_sticker_info_by_uri(char *uri)
499 sqlite3_stmt *stmt = NULL;
503 return STICKERD_SERVER_ERROR_DB_FAILED;
505 ret = sqlite3_prepare_v2(db, STICKER_DB_GET_IMAGE_INFO_BY_URI, -1, &stmt, NULL);
506 if (ret != SQLITE_OK) {
507 LOGE("fail to delete sticker information : %s", sqlite3_errmsg(db));
511 sqlite3_bind_text(stmt, 1, uri, -1, SQLITE_TRANSIENT);
513 ret = sqlite3_step(stmt);
514 if (ret == SQLITE_ERROR) {
515 LOGE("sqlite3_step() failed : ret(%d)", ret);
519 int uri_type = sqlite3_column_int(stmt, 0);
520 const unsigned char *thumbnail = sqlite3_column_text(stmt, 1);
522 if (uri_type == 1 && unlink((const char *)uri) == -1)
523 LOGE("fail to delete sticker file");
525 if (thumbnail && unlink((const char *)thumbnail) == -1)
526 LOGE("fail to delete thumbnail image");
528 sqlite3_finalize(stmt);
531 ret = sqlite3_prepare_v2(db, STICKER_DB_DELETE_STICKER_INFO_BY_URI, -1, &stmt, NULL);
532 if (ret != SQLITE_OK) {
533 LOGE("fail to delete sticker information : %s", sqlite3_errmsg(db));
537 sqlite3_bind_text(stmt, 1, uri, -1, SQLITE_TRANSIENT);
539 ret = sqlite3_step(stmt);
540 if (ret != SQLITE_OK && ret != SQLITE_DONE) {
541 LOGE("sqlite3_step() failed : ret(%d)", ret);
543 } else if (sqlite3_changes(db) == 0) {
544 LOGE("No changes to DB");
548 sqlite3_finalize(stmt);
551 return STICKERD_SERVER_ERROR_NONE;
554 sqlite3_finalize(stmt);
557 return STICKERD_SERVER_ERROR_DB_FAILED;
560 int stickerd_db_update_sticker_info(int record_id, sticker_info_db_type type, void *data)
564 sqlite3_stmt *stmt = NULL;
568 return STICKERD_SERVER_ERROR_DB_FAILED;
570 const char* query = _db_get_query(type, CMD_UPDATE);
571 ret = sqlite3_prepare_v2(db, query, -1, &stmt, NULL);
572 if (ret != SQLITE_OK) {
573 LOGE("fail to update sticker information : %s", sqlite3_errmsg(db));
577 if (type == STICKER_DB_STICKER_TYPE || type == STICKER_DB_STICKER_DISP_TYPE) {
578 sqlite3_bind_int(stmt, 1, *(int *)data);
579 } else if (type == STICKER_DB_STICKER_KEYWORD) {
580 sqlite3_bind_int(stmt, 1, record_id);
582 sqlite3_bind_text(stmt, 1, (char *)data, -1, SQLITE_TRANSIENT);
585 if (type != STICKER_DB_STICKER_KEYWORD)
586 sqlite3_bind_int(stmt, 2, record_id);
588 ret = sqlite3_step(stmt);
589 if (ret != SQLITE_OK && ret != SQLITE_DONE) {
590 LOGE("sqlite3_step() failed : ret(%d)", ret);
594 if (type == STICKER_DB_STICKER_KEYWORD) {
596 for(list = (GList *)data; list != NULL; list=list->next) {
597 sqlite3_finalize(stmt);
600 ret = sqlite3_prepare_v2(db, STICKER_DB_INSERT_STICKER_KEYWORD_INFO, -1, &stmt, NULL);
601 if (ret != SQLITE_OK) {
602 LOGE("fail to insert sticker information : %s", sqlite3_errmsg(db));
606 sqlite3_bind_int(stmt, 1, record_id);
607 sqlite3_bind_text(stmt, 2, (char *)list->data, -1, SQLITE_TRANSIENT);
609 ret = sqlite3_step(stmt);
610 if (ret != SQLITE_OK && ret != SQLITE_DONE) {
611 LOGE("sqlite3_step() failed : ret(%d)", ret);
613 } else if (sqlite3_changes(db) == 0) {
614 LOGE("No changes to DB");
620 sqlite3_finalize(stmt);
623 return STICKERD_SERVER_ERROR_NONE;
626 sqlite3_finalize(stmt);
629 return STICKERD_SERVER_ERROR_DB_FAILED;
632 int stickerd_db_get_sticker_info_by_record_id(int record_id, sticker_info_db *sticker_info)
636 sqlite3_stmt *stmt = NULL;
640 return STICKERD_SERVER_ERROR_DB_FAILED;
642 ret = sqlite3_prepare_v2(db, STICKER_DB_GET_STICKER_INFO_BY_RECORD_ID, -1, &stmt, NULL);
643 if (ret != SQLITE_OK) {
644 LOGE("fail to get sticker information : %s", sqlite3_errmsg(db));
648 sqlite3_bind_int(stmt, 1, record_id);
650 ret = sqlite3_step(stmt);
651 if (ret == SQLITE_ERROR) {
652 LOGE("sqlite3_step() failed : ret(%d)", ret);
656 const unsigned char *tmp_app_id = sqlite3_column_text(stmt, 1);
658 sticker_info->app_id = strdup((const char *)tmp_app_id);
660 LOGW("invalid record_id : %d", record_id);
664 sticker_info->type = sqlite3_column_int(stmt, 2);
666 const unsigned char *tmp_uri = sqlite3_column_text(stmt, 3);
668 sticker_info->uri = strdup((const char *)tmp_uri);
670 const unsigned char *tmp_thumbnail = sqlite3_column_text(stmt, 4);
672 sticker_info->thumbnail = strdup((const char *)tmp_thumbnail);
674 const unsigned char *tmp_description = sqlite3_column_text(stmt, 5);
676 sticker_info->description = strdup((const char *)tmp_description);
678 const unsigned char *tmp_group = sqlite3_column_text(stmt, 6);
680 sticker_info->group = strdup((const char *)tmp_group);
682 const unsigned char *tmp_date = sqlite3_column_text(stmt, 7);
684 sticker_info->date = strdup((const char *)tmp_date);
686 sticker_info->display_type = sqlite3_column_int(stmt, 8);
688 sqlite3_finalize(stmt);
691 ret = sqlite3_prepare_v2(db, STICKER_DB_GET_KEYWORD_INFO_BY_RECORD_ID, -1, &stmt, NULL);
692 if (ret != SQLITE_OK) {
693 LOGE("fail to get sticker keyword : %s", sqlite3_errmsg(db));
697 sqlite3_bind_int(stmt, 1, record_id);
699 while (sqlite3_step(stmt) == SQLITE_ROW) {
700 const unsigned char *keyword = sqlite3_column_text(stmt, 0);
702 sticker_info->keyword = g_list_append(sticker_info->keyword, strdup((const char *)keyword));
705 sqlite3_finalize(stmt);
708 return STICKERD_SERVER_ERROR_NONE;
711 sqlite3_finalize(stmt);
714 return STICKERD_SERVER_ERROR_DB_FAILED;
717 int stickerd_db_get_group_list(GVariantBuilder *builder, char *app_id)
721 sqlite3_stmt *stmt = NULL;
725 return STICKERD_SERVER_ERROR_DB_FAILED;
727 ret = sqlite3_prepare_v2(db, STICKER_DB_GET_ALL_GROUP_LIST, -1, &stmt, NULL);
728 if (ret != SQLITE_OK) {
729 LOGE("fail to get group list : %s", sqlite3_errmsg(db));
733 sqlite3_bind_text(stmt, 1, app_id, -1, SQLITE_TRANSIENT);
735 while (sqlite3_step(stmt) == SQLITE_ROW) {
736 const unsigned char *group = sqlite3_column_text(stmt, 0);
738 g_variant_builder_add(builder, "(s)", strdup((const char *)group));
741 sqlite3_finalize(stmt);
744 return STICKERD_SERVER_ERROR_NONE;
747 sqlite3_finalize(stmt);
750 return STICKERD_SERVER_ERROR_DB_FAILED;
753 int stickerd_db_get_keyword_list(GVariantBuilder *builder, char *app_id)
757 sqlite3_stmt *stmt = NULL;
761 return STICKERD_SERVER_ERROR_DB_FAILED;
763 ret = sqlite3_prepare_v2(db, STICKER_DB_GET_ALL_KEYWORD_LIST, -1, &stmt, NULL);
764 if (ret != SQLITE_OK) {
765 LOGE("fail to get keyword list : %s", sqlite3_errmsg(db));
769 sqlite3_bind_text(stmt, 1, app_id, -1, SQLITE_TRANSIENT);
771 while (sqlite3_step(stmt) == SQLITE_ROW) {
772 const unsigned char *keyword = sqlite3_column_text(stmt, 0);
774 g_variant_builder_add(builder, "(s)", strdup((const char *)keyword));
777 sqlite3_finalize(stmt);
780 return STICKERD_SERVER_ERROR_NONE;
783 sqlite3_finalize(stmt);
786 return STICKERD_SERVER_ERROR_DB_FAILED;
789 int stickerd_db_get_sticker_count(int *count, char *app_id)
793 sqlite3_stmt *stmt = NULL;
797 return STICKERD_SERVER_ERROR_DB_FAILED;
799 ret = sqlite3_prepare_v2(db, STICKER_DB_GET_STICKER_COUNT, -1, &stmt, NULL);
800 if (ret != SQLITE_OK) {
801 LOGE("fail to get sticker count : %s", sqlite3_errmsg(db));
805 sqlite3_bind_text(stmt, 1, app_id, -1, SQLITE_TRANSIENT);
807 ret = sqlite3_step(stmt);
808 if (ret == SQLITE_ERROR) {
809 LOGE("sqlite3_step() failed : ret(%d)", ret);
813 *count = sqlite3_column_int(stmt, 0);
815 sqlite3_finalize(stmt);
818 return STICKERD_SERVER_ERROR_NONE;
821 sqlite3_finalize(stmt);
824 return STICKERD_SERVER_ERROR_DB_FAILED;
827 int stickerd_db_get_record_id(sticker_info_db_type type, GList **id_list, void *data, char *app_id, int offset, int count)
831 sqlite3_stmt *stmt = NULL;
835 return STICKERD_SERVER_ERROR_DB_FAILED;
837 const char* query = _db_get_query(type, CMD_SELECT);
838 ret = sqlite3_prepare_v2(db, query, -1, &stmt, NULL);
839 if (ret != SQLITE_OK) {
840 LOGE("fail to get record id : %s", sqlite3_errmsg(db));
844 if (type == STICKER_DB_STICKER_ALL || type == STICKER_DB_STICKER_APPID) {
845 sqlite3_bind_text(stmt, 1, app_id, -1, SQLITE_TRANSIENT);
846 sqlite3_bind_int(stmt, 2, offset);
847 sqlite3_bind_int(stmt, 3, count);
848 } else if (type == STICKER_DB_STICKER_RECENT_HISTORY) {
849 sqlite3_bind_int(stmt, 1, count);
851 if (type == STICKER_DB_STICKER_TYPE || type == STICKER_DB_STICKER_DISP_TYPE)
852 sqlite3_bind_int(stmt, 1, *(int *)data);
854 sqlite3_bind_text(stmt, 1, (char *)data, -1, SQLITE_TRANSIENT);
856 sqlite3_bind_text(stmt, 2, app_id, -1, SQLITE_TRANSIENT);
857 sqlite3_bind_int(stmt, 3, offset);
858 sqlite3_bind_int(stmt, 4, count);
861 while (sqlite3_step(stmt) == SQLITE_ROW) {
862 const unsigned char *tmp_id = sqlite3_column_text(stmt, 0);
864 *id_list = g_list_append(*id_list, strdup((const char *)tmp_id));
867 sqlite3_finalize(stmt);
870 return STICKERD_SERVER_ERROR_NONE;
873 sqlite3_finalize(stmt);
876 return STICKERD_SERVER_ERROR_DB_FAILED;
879 int stickerd_db_get_group_list_by_display_type(GVariantBuilder *builder, char *app_id, int disp_type)
883 sqlite3_stmt *stmt = NULL;
887 return STICKERD_SERVER_ERROR_DB_FAILED;
889 ret = sqlite3_prepare_v2(db, STICKER_DB_GET_GROUP_LIST_BY_DISP_TYPE, -1, &stmt, NULL);
890 if (ret != SQLITE_OK) {
891 LOGE("fail to get group list : %s", sqlite3_errmsg(db));
895 sqlite3_bind_int(stmt, 1, disp_type);
896 sqlite3_bind_text(stmt, 2, app_id, -1, SQLITE_TRANSIENT);
898 while (sqlite3_step(stmt) == SQLITE_ROW) {
899 const unsigned char *group = sqlite3_column_text(stmt, 0);
901 g_variant_builder_add(builder, "(s)", strdup((const char *)group));
904 sqlite3_finalize(stmt);
907 return STICKERD_SERVER_ERROR_NONE;
910 sqlite3_finalize(stmt);
913 return STICKERD_SERVER_ERROR_DB_FAILED;
916 int stickerd_db_check_file_exists(int *result, char *uri)
920 sqlite3_stmt *stmt = NULL;
924 return STICKERD_SERVER_ERROR_DB_FAILED;
926 ret = sqlite3_prepare_v2(db, STICKER_DB_CHECK_FILE_EXISTS, -1, &stmt, NULL);
927 if (ret != SQLITE_OK) {
928 LOGE("fail to check file exists : %s", sqlite3_errmsg(db));
932 sqlite3_bind_text(stmt, 1, uri, -1, SQLITE_TRANSIENT);
934 ret = sqlite3_step(stmt);
935 if (ret == SQLITE_ERROR) {
936 LOGE("sqlite3_step() failed : ret(%d)", ret);
940 *result = sqlite3_column_int(stmt, 0);
942 sqlite3_finalize(stmt);
945 return STICKERD_SERVER_ERROR_NONE;
948 sqlite3_finalize(stmt);
951 return STICKERD_SERVER_ERROR_DB_FAILED;
954 int stickerd_db_insert_recent_sticker_info(int record_id)
958 sqlite3_stmt *stmt = NULL;
962 return STICKERD_SERVER_ERROR_DB_FAILED;
964 ret = sqlite3_prepare_v2(db, STICKER_DB_CHECK_RECENT_HISTORY_EXISTS, -1, &stmt, NULL);
965 if (ret != SQLITE_OK) {
966 LOGE("fail to check recent sticker exists : %s", sqlite3_errmsg(db));
970 sqlite3_bind_int(stmt, 1, record_id);
972 ret = sqlite3_step(stmt);
973 if (ret == SQLITE_ERROR) {
974 LOGE("sqlite3_step() failed : ret(%d)", ret);
978 int result = sqlite3_column_int(stmt, 0);
980 sqlite3_finalize(stmt);
984 ret = sqlite3_prepare_v2(db, STICKER_DB_UPDATE_RECENT_HISTORY, -1, &stmt, NULL);
986 ret = sqlite3_prepare_v2(db, STICKER_DB_INSERT_RECENT_HISTORY, -1, &stmt, NULL);
988 if (ret != SQLITE_OK) {
989 LOGE("fail to update recent history : %s", sqlite3_errmsg(db));
993 sqlite3_bind_int(stmt, 1, record_id);
995 ret = sqlite3_step(stmt);
996 if (ret != SQLITE_OK && ret != SQLITE_DONE) {
997 LOGE("sqlite3_step() failed : ret(%d)", ret);
999 } else if (sqlite3_changes(db) == 0) {
1000 LOGE("No changes to DB");
1004 sqlite3_finalize(stmt);
1007 return STICKERD_SERVER_ERROR_NONE;
1010 sqlite3_finalize(stmt);
1013 return STICKERD_SERVER_ERROR_DB_FAILED;
1016 int stickerd_db_get_sticker_info_by_uri(char *uri, sticker_info_db *sticker_info)
1020 sqlite3_stmt *stmt = NULL;
1024 return STICKERD_SERVER_ERROR_DB_FAILED;
1026 ret = sqlite3_prepare_v2(db, STICKER_DB_GET_STICKER_INFO_BY_URI, -1, &stmt, NULL);
1027 if (ret != SQLITE_OK) {
1028 LOGE("fail to get sticker information : %s", sqlite3_errmsg(db));
1032 sqlite3_bind_text(stmt, 1, uri, -1, SQLITE_TRANSIENT);
1034 ret = sqlite3_step(stmt);
1035 if (ret == SQLITE_ERROR) {
1036 LOGE("sqlite3_step() failed : ret(%d)", ret);
1040 sticker_info->record_id = sqlite3_column_int(stmt, 0);
1042 const unsigned char *tmp_app_id = sqlite3_column_text(stmt, 1);
1044 sticker_info->app_id = strdup((const char *)tmp_app_id);
1046 sticker_info->type = sqlite3_column_int(stmt, 2);
1048 const unsigned char *tmp_uri = sqlite3_column_text(stmt, 3);
1050 sticker_info->uri = strdup((const char *)tmp_uri);
1052 const unsigned char *tmp_thumbnail = sqlite3_column_text(stmt, 4);
1054 sticker_info->thumbnail = strdup((const char *)tmp_thumbnail);
1056 const unsigned char *tmp_description = sqlite3_column_text(stmt, 5);
1057 if (tmp_description)
1058 sticker_info->description = strdup((const char *)tmp_description);
1060 const unsigned char *tmp_group = sqlite3_column_text(stmt, 6);
1062 sticker_info->group = strdup((const char *)tmp_group);
1064 const unsigned char *tmp_date = sqlite3_column_text(stmt, 7);
1066 sticker_info->date = strdup((const char *)tmp_date);
1068 sticker_info->display_type = sqlite3_column_int(stmt, 8);
1070 sqlite3_finalize(stmt);
1073 ret = sqlite3_prepare_v2(db, STICKER_DB_GET_KEYWORD_INFO_BY_RECORD_ID, -1, &stmt, NULL);
1074 if (ret != SQLITE_OK) {
1075 LOGE("fail to get sticker keyword : %s", sqlite3_errmsg(db));
1079 sqlite3_bind_int(stmt, 1, sticker_info->record_id);
1081 while (sqlite3_step(stmt) == SQLITE_ROW) {
1082 const unsigned char *keyword = sqlite3_column_text(stmt, 0);
1084 sticker_info->keyword = g_list_append(sticker_info->keyword, strdup((const char *)keyword));
1087 sqlite3_finalize(stmt);
1090 return STICKERD_SERVER_ERROR_NONE;
1093 sqlite3_finalize(stmt);
1096 return STICKERD_SERVER_ERROR_DB_FAILED;