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 INTERFACES_INNERAPI_MEDIA_LIBRARY_HELPER_INCLUDE_SOURCE_ALBUM_H 17 #define INTERFACES_INNERAPI_MEDIA_LIBRARY_HELPER_INCLUDE_SOURCE_ALBUM_H 18 19 #include "media_column.h" 20 #include "photo_album_column.h" 21 #include "photo_map_column.h" 22 #include "vision_album_column.h" 23 #include "vision_column.h" 24 #include "vision_photo_map_column.h" 25 26 namespace OHOS { 27 namespace Media { 28 const std::string COVER_URI_VALUE_INSERT = 29 " (SELECT '" + PhotoColumn::PHOTO_URI_PREFIX + "'||NEW." + MediaColumn::MEDIA_ID + "||" + 30 "(SELECT SUBSTR(NEW." + MediaColumn::MEDIA_FILE_PATH + 31 ", (SELECT LENGTH(NEW." + MediaColumn::MEDIA_FILE_PATH + 32 ") - INSTR(reverseStr, '/') + 1) , (SELECT (SELECT LENGTH(NEW." + 33 MediaColumn::MEDIA_FILE_PATH + ") - INSTR(reverseStr, '.')) - (SELECT LENGTH(NEW." + 34 MediaColumn::MEDIA_FILE_PATH + ") - INSTR(reverseStr, '/')))) from (select " + 35 " (WITH RECURSIVE reverse_string(str, revstr) AS ( SELECT NEW." + 36 MediaColumn::MEDIA_FILE_PATH + ", '' UNION ALL SELECT SUBSTR(str, 1, LENGTH(str) - 1), " + 37 "revstr || SUBSTR(str, LENGTH(str), 1) FROM reverse_string WHERE LENGTH(str) > 1 ) " + 38 " SELECT revstr || str FROM reverse_string WHERE LENGTH(str) = 1) as reverseStr)) ||'/'||NEW." + 39 MediaColumn::MEDIA_NAME + ")"; 40 41 const std::string COVER_URI_VALUE_UPDATE = 42 "( SELECT '" + PhotoColumn::PHOTO_URI_PREFIX + "' || fileId ||" + 43 " ( SELECT SUBSTR( filePath, ( SELECT LENGTH( filePath ) - INSTR ( reverseStr, '/' ) + 1 )," + 44 " ( SELECT ( SELECT LENGTH( filePath ) - INSTR ( reverseStr, '.' ) ) -" + 45 " ( SELECT LENGTH( filePath ) - INSTR ( reverseStr, '/' ) ) ) ) ) || '/' || fileName" + 46 " FROM ( SELECT fileId, filePath, fileName, ( WITH RECURSIVE reverse_string ( str, revstr ) AS" + 47 " ( SELECT filePath, '' UNION ALL SELECT SUBSTR( str, 1, LENGTH( str ) - 1 ), revstr ||" + 48 " SUBSTR( str, LENGTH( str ), 1 ) FROM reverse_string WHERE LENGTH( str ) > 1 ) SELECT revstr ||" + 49 " str FROM reverse_string WHERE LENGTH( str ) = 1 ) AS reverseStr FROM ( SELECT " + 50 MediaColumn::MEDIA_ID + " AS fileId, " + 51 MediaColumn::MEDIA_FILE_PATH + " AS filePath, " + 52 MediaColumn::MEDIA_NAME + " AS fileName FROM " + 53 PhotoColumn::PHOTOS_TABLE + 54 " WHERE " + 55 MediaColumn::MEDIA_PACKAGE_NAME + " = OLD." + MediaColumn::MEDIA_PACKAGE_NAME + " AND " + 56 PhotoColumn::PHOTOS_QUERY_FILTER + " ORDER BY " + 57 MediaColumn::MEDIA_DATE_MODIFIED + " DESC LIMIT 1 ) ) )"; 58 59 const std::string COUNT_VALUE_INSERT = 60 " (SELECT COUNT(1) FROM " + PhotoColumn::PHOTOS_TABLE + 61 " WHERE " + 62 MediaColumn::MEDIA_PACKAGE_NAME + " = NEW." + MediaColumn::MEDIA_PACKAGE_NAME + " AND " + 63 PhotoColumn::PHOTOS_QUERY_FILTER + " )"; 64 65 const std::string COUNT_VALUE_UPDATE = 66 " (SELECT COUNT(1) FROM " + PhotoColumn::PHOTOS_TABLE + 67 " WHERE " + 68 MediaColumn::MEDIA_PACKAGE_NAME + " = OLD." + MediaColumn::MEDIA_PACKAGE_NAME + " AND " + 69 PhotoColumn::PHOTOS_QUERY_FILTER + " )"; 70 71 const std::string INSERT_PHOTO_MAP = 72 " INSERT INTO " + PhotoMap::TABLE + 73 " (" + PhotoMap::ALBUM_ID + " , " + PhotoMap::ASSET_ID + " )" + 74 " VALUES " + 75 " ( ( SELECT " + PhotoAlbumColumns::ALBUM_ID + " FROM " + PhotoAlbumColumns::TABLE + 76 " WHERE " + PhotoAlbumColumns::ALBUM_NAME + " = NEW." + MediaColumn::MEDIA_PACKAGE_NAME + " AND " + 77 PhotoAlbumColumns::ALBUM_TYPE + " = "+ std::to_string(OHOS::Media::PhotoAlbumType::SOURCE) + " AND " + 78 PhotoAlbumColumns::ALBUM_SUBTYPE + " = "+ std::to_string(OHOS::Media::PhotoAlbumSubType::SOURCE_GENERIC) + ")," + 79 " NEW." + MediaColumn::MEDIA_ID + " );"; 80 81 const std::string UPDATE_ALBUM_BUNDLENAME = 82 " UPDATE " + PhotoAlbumColumns::TABLE + 83 " SET " + PhotoAlbumColumns::ALBUM_BUNDLE_NAME + " = NEW." + MediaColumn::MEDIA_OWNER_PACKAGE + 84 " WHERE " + PhotoAlbumColumns::ALBUM_NAME + " = NEW." + MediaColumn::MEDIA_PACKAGE_NAME + " AND " + 85 PhotoAlbumColumns::ALBUM_TYPE + " = " + std::to_string(OHOS::Media::PhotoAlbumType::SOURCE) + " AND " + 86 PhotoAlbumColumns::ALBUM_SUBTYPE + " = " + std::to_string(OHOS::Media::PhotoAlbumSubType::SOURCE_GENERIC) + 87 " AND " + PhotoAlbumColumns::ALBUM_BUNDLE_NAME + " IS NULL;"; 88 89 const std::string SOURCE_ALBUM_WHERE = 90 " WHERE (" + PhotoAlbumColumns::ALBUM_NAME + " = NEW." + MediaColumn::MEDIA_PACKAGE_NAME + 91 " OR (bundle_name = NEW.owner_package and COALESCE(NEW.owner_package, '') <>'')) AND " + 92 PhotoAlbumColumns::ALBUM_TYPE + " = " + std::to_string(OHOS::Media::PhotoAlbumType::SOURCE) + " AND " + 93 PhotoAlbumColumns::ALBUM_SUBTYPE + " = " + std::to_string(OHOS::Media::PhotoAlbumSubType::SOURCE_GENERIC); 94 95 const std::string SOURCE_ALBUM_WHERE_UPDATE = 96 " WHERE " + PhotoAlbumColumns::ALBUM_NAME + " = OLD." + MediaColumn::MEDIA_PACKAGE_NAME + 97 " AND " + PhotoAlbumColumns::ALBUM_TYPE + " = " + std::to_string(OHOS::Media::PhotoAlbumType::SOURCE) + " AND " + 98 PhotoAlbumColumns::ALBUM_SUBTYPE + " = " + std::to_string(OHOS::Media::PhotoAlbumSubType::SOURCE_GENERIC) + 99 " AND dirty != 4"; 100 101 const std::string WHEN_SOURCE_PHOTO_COUNT = 102 " WHEN NEW." + MediaColumn::MEDIA_PACKAGE_NAME + " IS NOT NULL AND NEW." + MediaColumn::MEDIA_PACKAGE_NAME + 103 " != '' AND ( SELECT COUNT(1) FROM " + PhotoAlbumColumns::TABLE + SOURCE_ALBUM_WHERE + " )"; 104 105 const std::string WHEN_SOURCE_PHOTO_COUNT_FOR_BUNDLENAME = 106 " WHEN NEW." + MediaColumn::MEDIA_PACKAGE_NAME + " IS NOT NULL AND NEW." + MediaColumn::MEDIA_PACKAGE_NAME + 107 " != '' AND ( SELECT COUNT(1) FROM " + PhotoAlbumColumns::TABLE + SOURCE_ALBUM_WHERE + " AND " + 108 PhotoAlbumColumns::ALBUM_BUNDLE_NAME + " IS NULL" + " )"; 109 110 const std::string WHEN_UPDATE_AND_DELETE = " WHEN OLD." + MediaColumn::MEDIA_PACKAGE_NAME + " IS NOT NULL "; 111 112 const std::string TRIGGER_CODE_UPDATE_AND_DELETE = 113 WHEN_UPDATE_AND_DELETE + 114 " BEGIN UPDATE " + PhotoAlbumColumns::TABLE + 115 " SET " + PhotoAlbumColumns::ALBUM_COUNT + " = " + COUNT_VALUE_UPDATE + SOURCE_ALBUM_WHERE_UPDATE + ";" + 116 " UPDATE " + PhotoAlbumColumns::TABLE + 117 " SET " + PhotoAlbumColumns::ALBUM_COVER_URI + " = '' " + SOURCE_ALBUM_WHERE_UPDATE + 118 " AND " + PhotoAlbumColumns::ALBUM_COUNT + " = 0;" + 119 " UPDATE " + PhotoAlbumColumns::TABLE + 120 " SET " + PhotoAlbumColumns::ALBUM_COVER_URI + " = " + COVER_URI_VALUE_UPDATE + SOURCE_ALBUM_WHERE_UPDATE + 121 " AND " + PhotoAlbumColumns::ALBUM_COUNT + " > 0;" + " END;"; 122 123 const std::string DROP_INSERT_PHOTO_INSERT_SOURCE_ALBUM = "DROP TRIGGER IF EXISTS insert_photo_insert_source_album"; 124 125 const std::string DROP_INSERT_PHOTO_UPDATE_SOURCE_ALBUM = "DROP TRIGGER IF EXISTS insert_photo_update_source_album"; 126 127 const std::string DROP_UPDATE_PHOTO_UPDATE_SOURCE_ALBUM = "DROP TRIGGER IF EXISTS update_photo_update_source_album"; 128 129 const std::string DROP_DELETE_PHOTO_UPDATE_SOURCE_ALBUM = "DROP TRIGGER IF EXISTS delete_photo_update_source_album"; 130 131 const std::string DROP_INSERT_PHOTO_UPDATE_ALBUM_BUNDLENAME = 132 "DROP TRIGGER IF EXISTS insert_photo_update_album_bundlename"; 133 134 const std::string CLEAR_SOURCE_ALBUM_PHOTO_MAP = "DELETE FROM " + PhotoMap::TABLE + " WHERE " + PhotoMap::ASSET_ID + 135 " in (SELECT " + MediaColumn::MEDIA_ID + " FROM "+ PhotoColumn::PHOTOS_TABLE +")"; 136 137 const std::string CLEAR_SYSTEM_SOURCE_ALBUM = "DELETE FROM " + PhotoAlbumColumns::TABLE + " WHERE " + 138 PhotoAlbumColumns::ALBUM_TYPE + " = " + std::to_string(PhotoAlbumType::SYSTEM) + " AND " + 139 PhotoAlbumColumns::ALBUM_SUBTYPE + " = 1032"; 140 141 const std::string SOURCE_ALBUM_TO_CLEAR_WHERE = " WHERE " + 142 PhotoAlbumColumns::ALBUM_TYPE + " = " + std::to_string(PhotoAlbumType::SMART) + " AND " + 143 PhotoAlbumColumns::ALBUM_SUBTYPE + " = 4098"; 144 145 const std::string CLEAR_SOURCE_ALBUM_ANALYSIS_PHOTO_MAP = "DELETE FROM " + ANALYSIS_PHOTO_MAP_TABLE + " WHERE " + 146 MAP_ALBUM + " in (SELECT " + ALBUM_ID + " FROM "+ ANALYSIS_ALBUM_TABLE + SOURCE_ALBUM_TO_CLEAR_WHERE + " ) "; 147 148 const std::string CLEAR_ANALYSIS_SOURCE_ALBUM = "DELETE FROM " + ANALYSIS_ALBUM_TABLE + SOURCE_ALBUM_TO_CLEAR_WHERE; 149 150 const std::string INSERT_PHOTO_INSERT_SOURCE_ALBUM = 151 "CREATE TRIGGER IF NOT EXISTS insert_photo_insert_source_album AFTER INSERT ON " + PhotoColumn::PHOTOS_TABLE + 152 WHEN_SOURCE_PHOTO_COUNT + " = 0 " + 153 " BEGIN INSERT INTO " + PhotoAlbumColumns::TABLE + "(" + 154 PhotoAlbumColumns::ALBUM_TYPE + " , " + 155 PhotoAlbumColumns::ALBUM_SUBTYPE + " , " + 156 PhotoAlbumColumns::ALBUM_NAME + " , " + 157 PhotoAlbumColumns::ALBUM_BUNDLE_NAME + 158 " ) VALUES ( " + 159 std::to_string(OHOS::Media::PhotoAlbumType::SOURCE) + " , " + 160 std::to_string(OHOS::Media::PhotoAlbumSubType::SOURCE_GENERIC) + " , " + 161 "NEW." + MediaColumn::MEDIA_PACKAGE_NAME + " , " + 162 "NEW." + MediaColumn::MEDIA_OWNER_PACKAGE + 163 ");" + INSERT_PHOTO_MAP + "END;"; 164 165 const std::string INSERT_PHOTO_UPDATE_SOURCE_ALBUM = 166 "CREATE TRIGGER IF NOT EXISTS insert_photo_update_source_album AFTER INSERT ON " + PhotoColumn::PHOTOS_TABLE + 167 WHEN_SOURCE_PHOTO_COUNT + "> 0 " + 168 " BEGIN " + INSERT_PHOTO_MAP + " END;"; 169 170 const std::string INSERT_PHOTO_UPDATE_ALBUM_BUNDLENAME = 171 "CREATE TRIGGER IF NOT EXISTS insert_photo_update_album_bundlename AFTER INSERT ON " + PhotoColumn::PHOTOS_TABLE + 172 WHEN_SOURCE_PHOTO_COUNT_FOR_BUNDLENAME + " > 0 " + 173 " BEGIN " + UPDATE_ALBUM_BUNDLENAME + " END;"; 174 175 const std::string UPDATE_PHOTO_UPDATE_SOURCE_ALBUM = 176 "CREATE TRIGGER IF NOT EXISTS update_photo_update_source_album AFTER UPDATE ON " + 177 PhotoColumn::PHOTOS_TABLE + TRIGGER_CODE_UPDATE_AND_DELETE; 178 179 const std::string DELETE_PHOTO_UPDATE_SOURCE_ALBUM = 180 "CREATE TRIGGER IF NOT EXISTS delete_photo_update_source_album AFTER DELETE ON " + 181 PhotoColumn::PHOTOS_TABLE + TRIGGER_CODE_UPDATE_AND_DELETE; 182 183 const std::string ADD_SOURCE_ALBUM_BUNDLE_NAME = "ALTER TABLE " + PhotoAlbumColumns::TABLE + " ADD COLUMN " + 184 PhotoAlbumColumns::ALBUM_BUNDLE_NAME + " TEXT"; 185 186 const std::string PHOTOS_WHERE = MediaColumn::MEDIA_ID + " IN (SELECT " + MAP_ASSET + " FROM " + 187 ANALYSIS_PHOTO_MAP_TABLE + ") AND " + MediaColumn::MEDIA_PACKAGE_NAME + " IS NOT NULL"; 188 189 const std::string INSERT_SOURCE_ALBUMS_FROM_PHOTOS = "INSERT INTO " + PhotoAlbumColumns::TABLE + "(" + 190 PhotoAlbumColumns::ALBUM_NAME + ", " + 191 PhotoAlbumColumns::ALBUM_BUNDLE_NAME + ", " + 192 PhotoAlbumColumns::ALBUM_TYPE + ", " + 193 PhotoAlbumColumns::ALBUM_SUBTYPE + ")" + 194 " SELECT DISTINCT " + MediaColumn::MEDIA_PACKAGE_NAME + ", " + 195 MediaColumn::MEDIA_OWNER_PACKAGE + ", " + 196 std::to_string(PhotoAlbumType::SOURCE) + ", " + 197 std::to_string(PhotoAlbumSubType::SOURCE_GENERIC) + 198 " FROM " + PhotoColumn::PHOTOS_TABLE + " WHERE " + PHOTOS_WHERE; 199 200 const std::string INSERT_SOURCE_ALBUM_MAP_FROM_PHOTOS = "INSERT INTO " + PhotoMap::TABLE + "(" + 201 PhotoMap::ALBUM_ID + ", " + PhotoMap::ASSET_ID + ")" + 202 " SELECT PA." + PhotoAlbumColumns::ALBUM_ID + ", P." + MediaColumn::MEDIA_ID + 203 " FROM " + PhotoColumn::PHOTOS_TABLE + " AS P INNER JOIN " + PhotoAlbumColumns::TABLE + " AS PA" + 204 " ON P." + MediaColumn::MEDIA_PACKAGE_NAME + " = PA." + PhotoAlbumColumns::ALBUM_NAME + " WHERE " + PHOTOS_WHERE; 205 206 const std::string ADD_SOURCE_ALBUM_LOCAL_LANGUAGE = "ALTER TABLE " + PhotoAlbumColumns::TABLE + " ADD COLUMN " + 207 PhotoAlbumColumns::ALBUM_LOCAL_LANGUAGE + " TEXT"; 208 209 const std::string SOURCE_ALBUM_INDEX = "source_album_index"; 210 const std::string CREATE_SOURCE_ALBUM_INDEX = "CREATE UNIQUE INDEX IF NOT EXISTS " + SOURCE_ALBUM_INDEX + " ON " + 211 PhotoAlbumColumns::TABLE + " (" + PhotoAlbumColumns::ALBUM_TYPE + ", " + PhotoAlbumColumns::ALBUM_SUBTYPE + ", " + 212 PhotoAlbumColumns::ALBUM_NAME + ", " + PhotoAlbumColumns::ALBUM_BUNDLE_NAME + ", " + 213 PhotoAlbumColumns::ALBUM_LPATH + ") "; 214 215 const std::string DROP_SOURCE_ALBUM_INDEX = "DROP INDEX IF EXISTS " + SOURCE_ALBUM_INDEX; 216 217 const std::string INSERT_SOURCE_ALBUMS_FROM_PHOTOS_FULL = "INSERT INTO " + PhotoAlbumColumns::TABLE + "(" + 218 PhotoAlbumColumns::ALBUM_NAME + ", " + PhotoAlbumColumns::ALBUM_BUNDLE_NAME + ", " + 219 PhotoAlbumColumns::ALBUM_TYPE + ", " + PhotoAlbumColumns::ALBUM_SUBTYPE + ")" + 220 " SELECT " + MediaColumn::MEDIA_PACKAGE_NAME + ", " + MediaColumn::MEDIA_OWNER_PACKAGE + ", " + 221 std::to_string(PhotoAlbumType::SOURCE) + ", " + std::to_string(PhotoAlbumSubType::SOURCE_GENERIC) + 222 " FROM " + PhotoColumn::PHOTOS_TABLE + " WHERE " + 223 MediaColumn::MEDIA_PACKAGE_NAME + " IS NOT NULL AND " + MediaColumn::MEDIA_OWNER_PACKAGE + " IS NOT NULL " + 224 " GROUP BY " + MediaColumn::MEDIA_PACKAGE_NAME + ", " + MediaColumn::MEDIA_OWNER_PACKAGE; 225 226 const std::string INSERT_SOURCE_ALBUM_MAP_FROM_PHOTOS_FULL = "INSERT INTO " + PhotoMap::TABLE + "(" + 227 PhotoMap::ALBUM_ID + ", " + PhotoMap::ASSET_ID + ")" + 228 " SELECT PA." + PhotoAlbumColumns::ALBUM_ID + ", P." + MediaColumn::MEDIA_ID + 229 " FROM " + PhotoColumn::PHOTOS_TABLE + " AS P INNER JOIN " + PhotoAlbumColumns::TABLE + " AS PA" + 230 " ON P." + MediaColumn::MEDIA_PACKAGE_NAME + " = PA." + PhotoAlbumColumns::ALBUM_NAME + 231 " AND P." + MediaColumn::MEDIA_OWNER_PACKAGE + " = PA." + PhotoAlbumColumns::ALBUM_BUNDLE_NAME + 232 " WHERE PA." + PhotoAlbumColumns::ALBUM_TYPE + " = " + std::to_string(PhotoAlbumType::SOURCE) + 233 " AND PA." + PhotoAlbumColumns::ALBUM_SUBTYPE + " = " + std::to_string(PhotoAlbumSubType::SOURCE_GENERIC); 234 235 const std::string ADD_PHOTO_ALBUM_IS_LOCAL = "ALTER TABLE " + PhotoAlbumColumns::TABLE + " ADD COLUMN " + 236 PhotoAlbumColumns::ALBUM_IS_LOCAL + " INT"; 237 } // namespace Media 238 } // namespace OHOS 239 #endif // INTERFACES_INNERAPI_MEDIA_LIBRARY_HELPER_INCLUDE_SOURCE_ALBUM_H