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 |
36 * +-----------------+--------+------+------+-----------+-------------+------------+------+
37 * | sticker_info_id | app_id | type | uri | thumbnail | description | group_name | date |
38 * +-----------------+--------+------+------+-----------+-------------+------------+------+
40 * sticker_keyword_info
41 * +------------+-----------------+---------+
42 * | INT | INT | TEXT |
43 * +------------+-----------------+---------+
44 * | keyword_id | sticker_info_id | keyword |
45 * +------------+-----------------+---------+
49 #define STICKER_DB_PATH tzplatform_mkpath(TZ_SYS_DB, ".sticker_info.db")
50 #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);"
51 #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)"
53 #define STICKER_DB_INSERT_STICKER_INFO "INSERT INTO sticker_info (app_id, type, uri, thumbnail, description, group_name, date) VALUES (?, ?, ?, ?, ?, ?, DateTime('now','localtime'))"
54 #define STICKER_DB_INSERT_STICKER_KEYWORD_INFO "INSERT INTO sticker_keyword_info (sticker_info_id, keyword) VALUES (?, ?)"
56 #define STICKER_DB_DELETE_STICKER_INFO "DELETE FROM sticker_info WHERE sticker_info_id = ?"
57 #define STICKER_DB_DELETE_STICKER_KEYWORD_INFO "DELETE FROM sticker_keyword_info WHERE sticker_info_id = ?"
59 #define STICKER_DB_UPDATE_STICKER_TYPE "UPDATE sticker_info SET type = ?, date = DateTime('now','localtime') WHERE sticker_info_id = ?"
60 #define STICKER_DB_UPDATE_STICKER_URI "UPDATE sticker_info SET uri = ?, date = DateTime('now','localtime') WHERE sticker_info_id = ?"
61 #define STICKER_DB_UPDATE_STICKER_THUMBNAIL "UPDATE sticker_info SET thumbnail = ?, date = DateTime('now','localtime') WHERE sticker_info_id = ?"
62 #define STICKER_DB_UPDATE_STICKER_DESCRIPTION "UPDATE sticker_info SET description = ?, date = DateTime('now','localtime') WHERE sticker_info_id = ?"
63 #define STICKER_DB_UPDATE_STICKER_GROUP "UPDATE sticker_info SET group_name = ?, date = DateTime('now','localtime') WHERE sticker_info_id = ?"
65 #define STICKER_DB_GET_LATEST_RECORD_ID "SELECT sticker_info_id FROM sticker_info ORDER BY sticker_info_id DESC LIMIT 1"
66 #define STICKER_DB_GET_STICKER_INFO_BY_RECORD_ID "SELECT * FROM sticker_info WHERE sticker_info_id = ?"
67 #define STICKER_DB_GET_KEYWORD_INFO_BY_RECORD_ID "SELECT keyword FROM sticker_keyword_info WHERE sticker_info_id = ?"
68 #define STICKER_DB_GET_ALL_GROUP_LIST "SELECT group_name from (SELECT DISTINCT group_name from sticker_info)"
69 #define STICKER_DB_GET_ALL_KEYWORD_LIST "SELECT keyword from (SELECT DISTINCT keyword from sticker_keyword_info)"
70 #define STICKER_DB_GET_STICKER_COUNT "SELECT count(*) FROM sticker_info WHERE app_id = ?"
71 #define STICKER_DB_GET_ALL_RECORD_ID "SELECT sticker_info_id from sticker_info LIMIT ?, ?"
72 #define STICKER_DB_GET_RECORD_ID_BY_APP_ID "SELECT sticker_info_id from sticker_info WHERE app_id = ? LIMIT ?, ?"
73 #define STICKER_DB_GET_RECORD_ID_BY_TYPE "SELECT sticker_info_id from sticker_info WHERE type = ? LIMIT ?, ?"
74 #define STICKER_DB_GET_RECORD_ID_BY_GROUP "SELECT sticker_info_id from sticker_info WHERE group_name = ? LIMIT ?, ?"
75 #define STICKER_DB_GET_RECORD_ID_BY_KEYWORD "SELECT sticker_info_id from sticker_keyword_info WHERE keyword = ? LIMIT ?, ?"
83 static gboolean is_corrupted = FALSE;
85 static const char *_db_get_query(sticker_info_db_type sticker_type, command_type cmd_type)
87 static const char* query = NULL;
89 if (cmd_type == CMD_UPDATE) {
90 switch(sticker_type) {
91 case STICKER_DB_STICKER_TYPE:
92 query = STICKER_DB_UPDATE_STICKER_TYPE;
94 case STICKER_DB_STICKER_URI:
95 query = STICKER_DB_UPDATE_STICKER_URI;
97 case STICKER_DB_STICKER_THUMBNAIL:
98 query = STICKER_DB_UPDATE_STICKER_THUMBNAIL;
100 case STICKER_DB_STICKER_DESCRIPTION:
101 query = STICKER_DB_UPDATE_STICKER_DESCRIPTION;
103 case STICKER_DB_STICKER_GROUP:
104 query = STICKER_DB_UPDATE_STICKER_GROUP;
106 case STICKER_DB_STICKER_KEYWORD:
107 query = STICKER_DB_DELETE_STICKER_KEYWORD_INFO;
113 } else if (cmd_type == CMD_SELECT) {
114 switch(sticker_type) {
115 case STICKER_DB_STICKER_ALL:
116 query = STICKER_DB_GET_ALL_RECORD_ID;
118 case STICKER_DB_STICKER_APPID:
119 query = STICKER_DB_GET_RECORD_ID_BY_APP_ID;
121 case STICKER_DB_STICKER_TYPE:
122 query = STICKER_DB_GET_RECORD_ID_BY_TYPE;
124 case STICKER_DB_STICKER_GROUP:
125 query = STICKER_DB_GET_RECORD_ID_BY_GROUP;
127 case STICKER_DB_STICKER_KEYWORD:
128 query = STICKER_DB_GET_RECORD_ID_BY_KEYWORD;
139 static int _recover_db(void)
141 int ret = STICKERD_SERVER_ERROR_NONE;
145 LOGD("Start to recover sticker db");
146 //Remove sticker database file
147 if (unlink(STICKER_DB_PATH) == -1)
148 LOGE("Failed to remove db file");
150 ret = sqlite3_open_v2(STICKER_DB_PATH, &db, SQLITE_OPEN_CREATE | SQLITE_OPEN_READWRITE, NULL);
151 if (ret != SQLITE_OK) {
152 LOGE("Failed to open db : %s", sqlite3_errmsg(db));
153 if (unlink(STICKER_DB_PATH) == -1)
154 LOGE("Failed to remove db file");
155 ret = STICKERD_SERVER_ERROR_DB_FAILED;
159 ret = sqlite3_exec(db, STICKER_INFO_CREATE_TABLE, NULL, NULL, &err);
160 if (ret != SQLITE_OK) {
161 LOGE("Failed to create sticker_info table : %s" , err);
162 ret = STICKERD_SERVER_ERROR_DB_FAILED;
166 ret = sqlite3_exec(db, STICKER_KEYWORD_INFO_CREATE_TABLE, NULL, NULL, &err);
167 if (ret != SQLITE_OK) {
168 LOGE("Failed to create sticker_keyword_info table : %s", err);
169 ret = STICKERD_SERVER_ERROR_DB_FAILED;
172 is_corrupted = FALSE;
184 static int _integrity_check_cb(void *pid, int argc, char **argv, char **notUsed)
186 if (strcmp(argv[0], "ok") != 0) {
187 LOGE("DB integrity check failed : %s", argv[0]);
192 LOGD("Result integrity : %s", argv[0]);
196 int stickerd_db_init(void)
198 int ret = STICKERD_SERVER_ERROR_NONE;
202 ret = sqlite3_open_v2(STICKER_DB_PATH, &db, SQLITE_OPEN_CREATE | SQLITE_OPEN_READWRITE, NULL);
203 if (ret != SQLITE_OK) {
204 LOGE("Failed to open db : %s", sqlite3_errmsg(db));
205 ret = STICKERD_SERVER_ERROR_DB_FAILED;
209 ret = sqlite3_exec(db, STICKER_INFO_CREATE_TABLE, NULL, NULL, &err);
210 if (ret != SQLITE_OK) {
211 LOGE("Failed to create sticker_info table : %s" , err);
212 ret = STICKERD_SERVER_ERROR_DB_FAILED;
216 ret = sqlite3_exec(db, STICKER_KEYWORD_INFO_CREATE_TABLE, NULL, NULL, &err);
217 if (ret != SQLITE_OK) {
218 LOGE("Failed to create sticker_keyword_info table : %s", err);
219 ret = STICKERD_SERVER_ERROR_DB_FAILED;
223 ret = sqlite3_exec(db, "PRAGMA journal_mode = WAL", NULL, NULL, &err);
224 if (ret != SQLITE_OK) {
225 LOGE("Failed to set journal_mode : %s", err);
226 ret = STICKERD_SERVER_ERROR_DB_FAILED;
230 ret = sqlite3_exec(db, "PRAGMA integrity_check", _integrity_check_cb, NULL, &err);
231 if (ret != SQLITE_OK) {
232 LOGE("Failed to check integrity : %s", err);
233 ret = STICKERD_SERVER_ERROR_DB_FAILED;
243 if (ret == SQLITE_CORRUPT || ret == SQLITE_NOTADB || is_corrupted)
249 static sqlite3 *_db_open(void)
255 ret = sqlite3_open(STICKER_DB_PATH, &db);
256 if (ret != SQLITE_OK) {
257 LOGE("Failed to open db : %s", sqlite3_errmsg(db));
261 ret = sqlite3_exec(db, "PRAGMA foreign_keys = ON", NULL, NULL, &err);
262 if (ret != SQLITE_OK) {
263 LOGE("Failed to turn on foreign keys : %s", err);
269 int stickerd_db_insert_sticker_info(int *record_id, sticker_info_db *sticker_info)
273 sqlite3_stmt *stmt = NULL;
277 return STICKERD_SERVER_ERROR_DB_FAILED;
279 ret = sqlite3_prepare_v2(db, STICKER_DB_INSERT_STICKER_INFO, -1, &stmt, NULL);
280 if (ret != SQLITE_OK) {
281 LOGE("fail to insert sticker information : %s", sqlite3_errmsg(db));
285 sqlite3_bind_text(stmt, 1, sticker_info->app_id, -1, SQLITE_TRANSIENT);
286 sqlite3_bind_int(stmt, 2, sticker_info->type);
287 sqlite3_bind_text(stmt, 3, sticker_info->uri, -1, SQLITE_TRANSIENT);
288 sqlite3_bind_text(stmt, 4, sticker_info->thumbnail, -1, SQLITE_TRANSIENT);
289 sqlite3_bind_text(stmt, 5, sticker_info->description, -1, SQLITE_TRANSIENT);
290 sqlite3_bind_text(stmt, 6, sticker_info->group, -1, SQLITE_TRANSIENT);
292 ret = sqlite3_step(stmt);
293 if (ret != SQLITE_OK && ret != SQLITE_DONE) {
294 LOGE("sqlite3_step() failed : ret(%d)", ret);
296 } else if (sqlite3_changes(db) == 0) {
297 LOGE("No changes to DB");
301 sqlite3_finalize(stmt);
304 ret = sqlite3_prepare_v2(db, STICKER_DB_GET_LATEST_RECORD_ID, -1, &stmt, NULL);
305 if (ret != SQLITE_OK) {
306 LOGE("fail to get sticker id : %s", sqlite3_errmsg(db));
310 ret = sqlite3_step(stmt);
311 if (ret == SQLITE_ERROR) {
312 LOGE("sqlite3_step() failed : ret(%d)", ret);
316 *record_id = sqlite3_column_int(stmt, 0);
317 LOGD("record_id : %d", *record_id);
321 for(list = sticker_info->keyword; list != NULL; list=list->next) {
322 sqlite3_finalize(stmt);
325 ret = sqlite3_prepare_v2(db, STICKER_DB_INSERT_STICKER_KEYWORD_INFO, -1, &stmt, NULL);
326 if (ret != SQLITE_OK) {
327 LOGE("fail to insert sticker keyword : %s", sqlite3_errmsg(db));
331 sqlite3_bind_int(stmt, 1, *record_id);
332 sqlite3_bind_text(stmt, 2, (char *)list->data, -1, SQLITE_TRANSIENT);
334 ret = sqlite3_step(stmt);
335 if (ret != SQLITE_OK && ret != SQLITE_DONE) {
336 LOGE("sqlite3_step() failed : ret(%d)", ret);
338 } else if (sqlite3_changes(db) == 0) {
339 LOGE("No changes to DB");
344 sqlite3_finalize(stmt);
347 return STICKERD_SERVER_ERROR_NONE;
350 sqlite3_finalize(stmt);
353 return STICKERD_SERVER_ERROR_DB_FAILED;
356 int stickerd_db_delete_sticker_info(int record_id)
360 sqlite3_stmt *stmt = NULL;
364 return STICKERD_SERVER_ERROR_DB_FAILED;
366 ret = sqlite3_prepare_v2(db, STICKER_DB_DELETE_STICKER_INFO, -1, &stmt, NULL);
367 if (ret != SQLITE_OK) {
368 LOGE("fail to delete sticker information : %s", sqlite3_errmsg(db));
372 sqlite3_bind_int(stmt, 1, record_id);
374 ret = sqlite3_step(stmt);
375 if (ret != SQLITE_OK && ret != SQLITE_DONE) {
376 LOGE("sqlite3_step() failed : ret(%d)", ret);
378 } else if (sqlite3_changes(db) == 0) {
379 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_update_sticker_info(int record_id, sticker_info_db_type type, void *data)
399 sqlite3_stmt *stmt = NULL;
403 return STICKERD_SERVER_ERROR_DB_FAILED;
405 const char* query = _db_get_query(type, CMD_UPDATE);
406 ret = sqlite3_prepare_v2(db, query, -1, &stmt, NULL);
407 if (ret != SQLITE_OK) {
408 LOGE("fail to update sticker information : %s", sqlite3_errmsg(db));
412 if (type == STICKER_DB_STICKER_TYPE) {
413 sqlite3_bind_int(stmt, 1, *(int *)data);
414 } else if (type == STICKER_DB_STICKER_KEYWORD) {
415 sqlite3_bind_int(stmt, 1, record_id);
417 sqlite3_bind_text(stmt, 1, (char *)data, -1, SQLITE_TRANSIENT);
420 if (type != STICKER_DB_STICKER_KEYWORD)
421 sqlite3_bind_int(stmt, 2, record_id);
423 ret = sqlite3_step(stmt);
424 if (ret != SQLITE_OK && ret != SQLITE_DONE) {
425 LOGE("sqlite3_step() failed : ret(%d)", ret);
429 if (type == STICKER_DB_STICKER_KEYWORD) {
431 for(list = (GList *)data; list != NULL; list=list->next) {
432 sqlite3_finalize(stmt);
435 ret = sqlite3_prepare_v2(db, STICKER_DB_INSERT_STICKER_KEYWORD_INFO, -1, &stmt, NULL);
436 if (ret != SQLITE_OK) {
437 LOGE("fail to insert sticker information : %s", sqlite3_errmsg(db));
441 sqlite3_bind_int(stmt, 1, record_id);
442 sqlite3_bind_text(stmt, 2, (char *)list->data, -1, SQLITE_TRANSIENT);
444 ret = sqlite3_step(stmt);
445 if (ret != SQLITE_OK && ret != SQLITE_DONE) {
446 LOGE("sqlite3_step() failed : ret(%d)", ret);
448 } else if (sqlite3_changes(db) == 0) {
449 LOGE("No changes to DB");
455 sqlite3_finalize(stmt);
458 return STICKERD_SERVER_ERROR_NONE;
461 sqlite3_finalize(stmt);
464 return STICKERD_SERVER_ERROR_DB_FAILED;
467 int stickerd_db_get_sticker_info_by_record_id(int record_id, sticker_info_db *sticker_info)
471 sqlite3_stmt *stmt = NULL;
475 return STICKERD_SERVER_ERROR_DB_FAILED;
477 ret = sqlite3_prepare_v2(db, STICKER_DB_GET_STICKER_INFO_BY_RECORD_ID, -1, &stmt, NULL);
478 if (ret != SQLITE_OK) {
479 LOGE("fail to get sticker information : %s", sqlite3_errmsg(db));
483 sqlite3_bind_int(stmt, 1, record_id);
485 ret = sqlite3_step(stmt);
486 if (ret == SQLITE_ERROR) {
487 LOGE("sqlite3_step() failed : ret(%d)", ret);
491 const unsigned char *tmp_app_id = sqlite3_column_text(stmt, 1);
493 sticker_info->app_id = strdup((const char *)tmp_app_id);
495 LOGW("invalid record_id : %d", record_id);
499 sticker_info->type = sqlite3_column_int(stmt, 2);
501 const unsigned char *tmp_uri = sqlite3_column_text(stmt, 3);
503 sticker_info->uri = strdup((const char *)tmp_uri);
505 const unsigned char *tmp_thumbnail = sqlite3_column_text(stmt, 4);
507 sticker_info->thumbnail = strdup((const char *)tmp_thumbnail);
509 const unsigned char *tmp_description = sqlite3_column_text(stmt, 5);
511 sticker_info->description = strdup((const char *)tmp_description);
513 const unsigned char *tmp_group = sqlite3_column_text(stmt, 6);
515 sticker_info->group = strdup((const char *)tmp_group);
517 const unsigned char *tmp_date = sqlite3_column_text(stmt, 7);
519 sticker_info->date = strdup((const char *)tmp_date);
521 sqlite3_finalize(stmt);
524 ret = sqlite3_prepare_v2(db, STICKER_DB_GET_KEYWORD_INFO_BY_RECORD_ID, -1, &stmt, NULL);
525 if (ret != SQLITE_OK) {
526 LOGE("fail to get sticker keyword : %s", sqlite3_errmsg(db));
530 sqlite3_bind_int(stmt, 1, record_id);
532 while (sqlite3_step(stmt) == SQLITE_ROW) {
533 const unsigned char *keyword = sqlite3_column_text(stmt, 0);
535 sticker_info->keyword = g_list_append(sticker_info->keyword, strdup((const char *)keyword));
538 sqlite3_finalize(stmt);
541 return STICKERD_SERVER_ERROR_NONE;
544 sqlite3_finalize(stmt);
547 return STICKERD_SERVER_ERROR_DB_FAILED;
550 int stickerd_db_get_group_list(GVariantBuilder *builder)
554 sqlite3_stmt *stmt = NULL;
558 return STICKERD_SERVER_ERROR_DB_FAILED;
560 ret = sqlite3_prepare_v2(db, STICKER_DB_GET_ALL_GROUP_LIST, -1, &stmt, NULL);
561 if (ret != SQLITE_OK) {
562 LOGE("fail to get group list : %s", sqlite3_errmsg(db));
566 while (sqlite3_step(stmt) == SQLITE_ROW) {
567 const unsigned char *group = sqlite3_column_text(stmt, 0);
569 g_variant_builder_add(builder, "(s)", strdup((const char *)group));
572 sqlite3_finalize(stmt);
575 return STICKERD_SERVER_ERROR_NONE;
578 sqlite3_finalize(stmt);
581 return STICKERD_SERVER_ERROR_DB_FAILED;
584 int stickerd_db_get_keyword_list(GVariantBuilder *builder)
588 sqlite3_stmt *stmt = NULL;
592 return STICKERD_SERVER_ERROR_DB_FAILED;
594 ret = sqlite3_prepare_v2(db, STICKER_DB_GET_ALL_KEYWORD_LIST, -1, &stmt, NULL);
595 if (ret != SQLITE_OK) {
596 LOGE("fail to get keyword list : %s", sqlite3_errmsg(db));
600 while (sqlite3_step(stmt) == SQLITE_ROW) {
601 const unsigned char *keyword = sqlite3_column_text(stmt, 0);
603 g_variant_builder_add(builder, "(s)", strdup((const char *)keyword));
606 sqlite3_finalize(stmt);
609 return STICKERD_SERVER_ERROR_NONE;
612 sqlite3_finalize(stmt);
615 return STICKERD_SERVER_ERROR_DB_FAILED;
618 int stickerd_db_get_sticker_count(int *count, char *app_id)
622 sqlite3_stmt *stmt = NULL;
626 return STICKERD_SERVER_ERROR_DB_FAILED;
628 ret = sqlite3_prepare_v2(db, STICKER_DB_GET_STICKER_COUNT, -1, &stmt, NULL);
629 if (ret != SQLITE_OK) {
630 LOGE("fail to get sticker count : %s", sqlite3_errmsg(db));
634 sqlite3_bind_text(stmt, 1, app_id, -1, SQLITE_TRANSIENT);
636 ret = sqlite3_step(stmt);
637 if (ret == SQLITE_ERROR) {
638 LOGE("sqlite3_step() failed : ret(%d)", ret);
642 *count = sqlite3_column_int(stmt, 0);
644 sqlite3_finalize(stmt);
647 return STICKERD_SERVER_ERROR_NONE;
650 sqlite3_finalize(stmt);
653 return STICKERD_SERVER_ERROR_DB_FAILED;
656 int stickerd_db_get_record_id(sticker_info_db_type type, GList **id_list, void *data, int offset, int count)
660 sqlite3_stmt *stmt = NULL;
664 return STICKERD_SERVER_ERROR_DB_FAILED;
666 const char* query = _db_get_query(type, CMD_SELECT);
667 ret = sqlite3_prepare_v2(db, query, -1, &stmt, NULL);
668 if (ret != SQLITE_OK) {
669 LOGE("fail to get record id : %s", sqlite3_errmsg(db));
673 if (type == STICKER_DB_STICKER_TYPE)
674 sqlite3_bind_int(stmt, 1, *(int *)data);
675 else if (type != STICKER_DB_STICKER_ALL)
676 sqlite3_bind_text(stmt, 1, (char *)data, -1, SQLITE_TRANSIENT);
678 if (type == STICKER_DB_STICKER_ALL) {
679 sqlite3_bind_int(stmt, 1, offset);
680 sqlite3_bind_int(stmt, 2, count);
682 sqlite3_bind_int(stmt, 2, offset);
683 sqlite3_bind_int(stmt, 3, count);
686 while (sqlite3_step(stmt) == SQLITE_ROW) {
687 const unsigned char *tmp_id = sqlite3_column_text(stmt, 0);
689 *id_list = g_list_append(*id_list, strdup((const char *)tmp_id));
692 sqlite3_finalize(stmt);
695 return STICKERD_SERVER_ERROR_NONE;
698 sqlite3_finalize(stmt);
701 return STICKERD_SERVER_ERROR_DB_FAILED;