1 /* 2 * Copyright (C) 2023 Huawei Device Co., Ltd. 3 * Licensed under the Apache License, Version 2.0 (the "License"); 4 * you may not use this file except in compliance with the License. 5 * You may obtain a copy of the License at 6 * 7 * http://www.apache.org/licenses/LICENSE-2.0 8 * 9 * Unless required by applicable law or agreed to in writing, software 10 * distributed under the License is distributed on an "AS IS" BASIS, 11 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 12 * See the License for the specific language governing permissions and 13 * limitations under the License. 14 */ 15 16 #ifndef MEDIALIBRARY_SEARCH_COLUMN_H 17 #define MEDIALIBRARY_SEARCH_COLUMN_H 18 19 #include "media_column.h" 20 #include "photo_album_column.h" 21 #include "photo_map_column.h" 22 #include "vision_column.h" 23 #include "userfilemgr_uri.h" 24 25 namespace OHOS { 26 namespace Media { 27 // table name 28 const std::string SEARCH_TOTAL_TABLE = "tab_analysis_search_index"; 29 30 // uri 31 const std::string URI_SEARCH_INDEX = MEDIALIBRARY_DATA_URI + "/" + SEARCH_TOTAL_TABLE; 32 33 // create search table 34 const std::string TBL_SEARCH_ID = "id"; 35 const std::string TBL_SEARCH_FILE_ID = "file_id"; 36 const std::string TBL_SEARCH_DATA = "data"; 37 const std::string TBL_SEARCH_DISPLAYNAME = "display_name"; 38 const std::string TBL_SEARCH_LATITUDE = "latitude"; 39 const std::string TBL_SEARCH_LONGITUDE = "longitude"; 40 const std::string TBL_SEARCH_DATE_MODIFIED = "date_modified"; 41 const std::string TBL_SEARCH_PHOTO_STATUS = "photo_status"; 42 const std::string TBL_SEARCH_CV_STATUS = "cv_status"; 43 const std::string TBL_SEARCH_GEO_STATUS = "geo_status"; 44 const std::string TBL_SEARCH_VERSION = "version"; 45 const std::string TBL_SEARCH_SYSTEM_LANGUAGE = "system_language"; 46 47 // Number of completed and total progress in image and video indexing construction 48 const std::string PHOTO_COMPLETE_NUM = "finishedImageCount"; 49 const std::string PHOTO_TOTAL_NUM = "totalImageCount"; 50 const std::string VIDEO_COMPLETE_NUM = "finishedVideoCount"; 51 const std::string VIDEO_TOTAL_NUM = "totalVideoCount"; 52 53 // field status enum 54 enum TblSearchPhotoStatus { 55 INSERT_FAIL = -3, 56 DELETE_FAIL = -2, 57 NEED_DELETE = -1, 58 NO_INDEX = 0, 59 INDEXED = 1, 60 NEED_UPDATE = 2, 61 INDEXED_NEW = 3, 62 }; 63 64 const std::string CREATE_SEARCH_TOTAL_TABLE = "CREATE TABLE IF NOT EXISTS " + SEARCH_TOTAL_TABLE + " (" + 65 TBL_SEARCH_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + 66 TBL_SEARCH_FILE_ID + " INT UNIQUE, " + 67 TBL_SEARCH_DATA + " TEXT, " + 68 TBL_SEARCH_DISPLAYNAME + " TEXT, " + 69 TBL_SEARCH_LATITUDE + " DOUBLE, " + 70 TBL_SEARCH_LONGITUDE + " DOUBLE, " + 71 TBL_SEARCH_DATE_MODIFIED + " BIGINT DEFAULT 0, " + 72 TBL_SEARCH_PHOTO_STATUS + " INT DEFAULT 0, " + 73 TBL_SEARCH_CV_STATUS + " INT DEFAULT 0, " + 74 TBL_SEARCH_GEO_STATUS + " INT DEFAULT 0, " + 75 TBL_SEARCH_VERSION + " INT DEFAULT 0, " + 76 TBL_SEARCH_SYSTEM_LANGUAGE + " TEXT) "; 77 78 // trigger 79 // Listening of Photos: basic information 80 const std::string INSERT_SEARCH_TRIGGER = "insert_search_trigger"; 81 const std::string CREATE_SEARCH_INSERT_TRIGGER = 82 std::string("CREATE TRIGGER IF NOT EXISTS insert_search_trigger AFTER INSERT ON ") + 83 PhotoColumn::PHOTOS_TABLE + " FOR EACH ROW " + 84 " WHEN (NEW.media_type = 1 OR NEW.media_type = 2)" + 85 " BEGIN " + 86 " INSERT INTO " + SEARCH_TOTAL_TABLE + 87 " (" + TBL_SEARCH_FILE_ID + ", " + TBL_SEARCH_DATA + ", " + TBL_SEARCH_DATE_MODIFIED + ", " + 88 TBL_SEARCH_DISPLAYNAME + ", " + TBL_SEARCH_LATITUDE + ", " + TBL_SEARCH_LONGITUDE + " )" + 89 " VALUES ( NEW.file_id, NEW.data, NEW.date_modified, NEW.display_name, NEW.latitude, NEW.longitude );" + 90 " END;"; 91 92 // Listening of Photos: update(date_modified, latitude, longitude) 93 const std::string UPDATE_SEARCH_TRIGGER = "update_search_trigger"; 94 const std::string CREATE_SEARCH_UPDATE_TRIGGER = 95 std::string("CREATE TRIGGER IF NOT EXISTS update_search_trigger AFTER UPDATE") + 96 " OF data, date_modified, latitude, longitude " + 97 " ON " + PhotoColumn::PHOTOS_TABLE + " FOR EACH ROW " + 98 " BEGIN " + 99 " UPDATE " + SEARCH_TOTAL_TABLE + 100 " SET " + " ( data, date_modified, latitude, longitude ) = " + 101 " ( NEW.data, NEW.date_modified, NEW.latitude, NEW.longitude ) " + 102 " WHERE " + TBL_SEARCH_FILE_ID + " = OLD.file_id;" + 103 " END;"; 104 105 // Listening of Photos: update (title,date_modified,latitude,longitude 106 // date_day, date_month, date_year, shooting_mode, date_taken, hidden, date_trashed) 107 const std::string UPDATE_SEARCH_STATUS_TRIGGER = "update_search_status_trigger"; 108 const std::string CREATE_SEARCH_UPDATE_STATUS_TRIGGER = 109 std::string("CREATE TRIGGER IF NOT EXISTS update_search_status_trigger AFTER UPDATE") + 110 " OF title, date_modified, latitude, longitude, date_day, " + 111 " date_month, date_year, shooting_mode, date_taken, hidden, date_trashed, user_comment, clean_flag " + 112 " ON " + PhotoColumn::PHOTOS_TABLE + " FOR EACH ROW " + 113 " BEGIN " + 114 " UPDATE " + SEARCH_TOTAL_TABLE + 115 " SET " + TBL_SEARCH_PHOTO_STATUS + " = " + std::to_string(TblSearchPhotoStatus::NEED_UPDATE) + 116 " WHERE " + " (" + TBL_SEARCH_FILE_ID + " = OLD.file_id" + 117 " AND (" + TBL_SEARCH_PHOTO_STATUS + " = " + std::to_string(TblSearchPhotoStatus::INDEXED) + 118 " OR " + TBL_SEARCH_PHOTO_STATUS + " = " + std::to_string(TblSearchPhotoStatus::INDEXED_NEW) + "));" + 119 " END;"; 120 121 // Listening of Photos: delete 122 const std::string DELETE_SEARCH_TRIGGER = "delete_search_trigger"; 123 const std::string CREATE_SEARCH_DELETE_TRIGGER = 124 std::string("CREATE TRIGGER IF NOT EXISTS delete_search_trigger AFTER DELETE ON ") + 125 PhotoColumn::PHOTOS_TABLE + " FOR EACH ROW " + 126 " BEGIN " + 127 " UPDATE " + SEARCH_TOTAL_TABLE + 128 " SET " + TBL_SEARCH_PHOTO_STATUS + " = " + std::to_string(TblSearchPhotoStatus::NEED_DELETE) + 129 " WHERE " + TBL_SEARCH_FILE_ID + " = OLD.file_id;" + 130 " END;"; 131 132 // Listening of photoMap: insert 133 const std::string ALBUM_MAP_INSERT_SEARCH_TRIGGER = "album_map_insert_search_trigger"; 134 const std::string CREATE_ALBUM_MAP_INSERT_SEARCH_TRIGGER = 135 std::string("CREATE TRIGGER IF NOT EXISTS album_map_insert_search_trigger AFTER INSERT ON ") + 136 PhotoMap::TABLE + " FOR EACH ROW " + 137 " BEGIN " + 138 " UPDATE " + SEARCH_TOTAL_TABLE + 139 " SET " + TBL_SEARCH_PHOTO_STATUS + " = " + std::to_string(TblSearchPhotoStatus::NEED_UPDATE) + 140 " WHERE " + " (" + TBL_SEARCH_FILE_ID + " = NEW.map_asset " + 141 " AND (" + TBL_SEARCH_PHOTO_STATUS + " = " + std::to_string(TblSearchPhotoStatus::INDEXED) + 142 " OR " + TBL_SEARCH_PHOTO_STATUS + " = " + std::to_string(TblSearchPhotoStatus::INDEXED_NEW) + "));" + 143 " END;"; 144 145 // Listening of photoMap: delete 146 const std::string ALBUM_MAP_DELETE_SEARCH_TRIGGER = "album_map_delete_search_trigger"; 147 const std::string CREATE_ALBUM_MAP_DELETE_SEARCH_TRIGGER = 148 std::string("CREATE TRIGGER IF NOT EXISTS album_map_delete_search_trigger AFTER DELETE ON ") + 149 PhotoMap::TABLE + " FOR EACH ROW " + 150 " BEGIN " + 151 " UPDATE " + SEARCH_TOTAL_TABLE + 152 " SET " + TBL_SEARCH_PHOTO_STATUS + " = " + std::to_string(TblSearchPhotoStatus::NEED_UPDATE) + 153 " WHERE " + " (" + TBL_SEARCH_FILE_ID + " = old.map_asset " + 154 " AND (" + TBL_SEARCH_PHOTO_STATUS + " = " + std::to_string(TblSearchPhotoStatus::INDEXED) + 155 " OR " + TBL_SEARCH_PHOTO_STATUS + " = " + std::to_string(TblSearchPhotoStatus::INDEXED_NEW) + "));" + 156 " END;"; 157 158 // Listening of photoAlbum: update of(album_name) 159 const std::string ALBUM_UPDATE_SEARCH_TRIGGER = "album_update_search_trigger"; 160 const std::string CREATE_ALBUM_UPDATE_SEARCH_TRIGGER = 161 std::string("CREATE TRIGGER IF NOT EXISTS album_update_search_trigger AFTER UPDATE ") + 162 " OF album_name " + 163 " ON " + PhotoAlbumColumns::TABLE + " FOR EACH ROW " + 164 " BEGIN " + 165 " UPDATE " + SEARCH_TOTAL_TABLE + 166 " SET " + TBL_SEARCH_PHOTO_STATUS + " = " + std::to_string(TblSearchPhotoStatus::NEED_UPDATE) + 167 " WHERE " + " (" + 168 TBL_SEARCH_FILE_ID + " IN" + " (" + 169 "SELECT " + PhotoMap::ASSET_ID + " FROM " + PhotoMap::TABLE + 170 " WHERE " + " ( old.album_id = PhotoMap.map_album ) " + 171 " ) " + 172 " );" + 173 " END;"; 174 175 // Listening of cv tab_analysis_total: update of(status) ,update cv_status 176 const std::string ANALYSIS_UPDATE_SEARCH_TRIGGER = "analysis_update_search_trigger"; 177 const std::string CREATE_ANALYSIS_UPDATE_SEARCH_TRIGGER = 178 std::string("CREATE TRIGGER IF NOT EXISTS analysis_update_search_trigger AFTER UPDATE ") + 179 " OF status " + 180 " ON " + VISION_TOTAL_TABLE + " FOR EACH ROW " + 181 " WHEN (NEW.status = 1)" + 182 " BEGIN " + 183 " UPDATE " + SEARCH_TOTAL_TABLE + 184 " SET " + TBL_SEARCH_CV_STATUS + " = " + std::to_string(TblSearchPhotoStatus::NO_INDEX) + 185 " WHERE " + " (" + TBL_SEARCH_FILE_ID + " = old.file_id " + 186 " AND " + TBL_SEARCH_CV_STATUS + " = " + std::to_string(TblSearchPhotoStatus::INDEXED) + ");" + 187 " END;"; 188 189 const std::string IDX_FILEID_FOR_SEARCH_INDEX = "idx_fileid_for_search_index"; 190 const std::string CREATE_IDX_FILEID_FOR_SEARCH_INDEX = "CREATE INDEX IF NOT EXISTS " + 191 IDX_FILEID_FOR_SEARCH_INDEX + " ON " + SEARCH_TOTAL_TABLE + " ( file_id );"; 192 } // namespace Media 193 } // namespace OHOS 194 #endif // MEDIALIBRARY_SEARCH_COLUMN_H