1 /* 2 * Copyright (C) 2024 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 OHOS_MEDIA_DATATRANSFER_MEDIA_LIBRARY_DB_UPGRADE_H 17 #define OHOS_MEDIA_DATATRANSFER_MEDIA_LIBRARY_DB_UPGRADE_H 18 19 #include <string> 20 21 #include "rdb_store.h" 22 #include "db_upgrade_utils.h" 23 24 namespace OHOS::Media { 25 namespace DataTransfer { 26 class MediaLibraryDbUpgrade { 27 public: 28 int32_t OnUpgrade(NativeRdb::RdbStore &store); 29 30 private: 31 int32_t UpgradeAlbumPlugin(NativeRdb::RdbStore &store); 32 int32_t UpgradePhotoAlbum(NativeRdb::RdbStore &store); 33 int32_t UpgradePhotos(NativeRdb::RdbStore &store); 34 int32_t UpgradePhotoMap(NativeRdb::RdbStore &store); 35 int32_t MergeAlbumFromOldBundleNameToNewBundleName(NativeRdb::RdbStore &store); 36 int32_t UpgradePhotosBelongsToAlbum(NativeRdb::RdbStore &store); 37 38 private: 39 int32_t AddOwnerAlbumIdColumn(NativeRdb::RdbStore &store); 40 int32_t AddlPathColumn(NativeRdb::RdbStore &store); 41 int32_t MoveSingleRelationshipToPhotos(NativeRdb::RdbStore &store); 42 int32_t DropPhotoAlbumTrigger(NativeRdb::RdbStore &store); 43 int32_t UpdatelPathColumn(NativeRdb::RdbStore &store); 44 int32_t ExecSqlsInternal(NativeRdb::RdbStore &store, const std::string &sql, 45 const std::vector<NativeRdb::ValueObject> &args = {}); 46 47 private: 48 DbUpgradeUtils dbUpgradeUtils_; 49 50 private: 51 const std::vector<std::string> SQL_PHOTO_ALBUM_TABLE_DROP_TRIGGER = {"DROP TRIGGER IF EXISTS album_delete_trigger", 52 "DROP TRIGGER IF EXISTS album_insert_cloud_sync_trigger", 53 "DROP TRIGGER IF EXISTS album_modify_trigger", 54 "DROP TRIGGER IF EXISTS album_update_search_trigger", 55 "DROP TRIGGER IF EXISTS insert_order_trigger", 56 "DROP TRIGGER IF EXISTS photo_album_clear_map", 57 "DROP TRIGGER IF EXISTS update_order_trigger"}; 58 const std::string SQL_PHOTO_ALBUM_TABLE_ADD_LPATH_COLUMN = "ALTER TABLE PhotoAlbum ADD COLUMN lpath TEXT;"; 59 const std::string SQL_PHOTO_ALBUM_TABLE_UPDATE_LPATH_COLUMN = "\ 60 UPDATE PhotoAlbum \ 61 SET lpath = \ 62 ( \ 63 SELECT \ 64 CASE \ 65 WHEN COALESCE(album_plugin.bundle_name, '') <> '' \ 66 AND COALESCE(album_plugin.lpath, '') <> '' THEN album_plugin.lpath \ 67 WHEN album_type=2048 THEN '/Pictures/'||PA.album_name \ 68 WHEN album_type=0 THEN '/Pictures/Users/'||PA.album_name \ 69 ELSE '/Pictures/其它' \ 70 END AS lpath \ 71 FROM PhotoAlbum AS PA \ 72 LEFT JOIN album_plugin \ 73 ON PA.bundle_name = album_plugin.bundle_name \ 74 WHERE PhotoAlbum.album_id=PA.album_id \ 75 ORDER BY album_plugin.priority DESC, album_plugin.lpath ASC \ 76 LIMIT 1 \ 77 ) \ 78 WHERE COALESCE(PhotoAlbum.album_name, '') <> '' AND \ 79 PhotoAlbum.album_subtype != 1024 AND \ 80 COALESCE(PhotoAlbum.lpath, '') = '';"; 81 const std::string SQL_PHOTOS_TABLE_ADD_OWNER_ALBUM_ID = "\ 82 ALTER TABLE Photos ADD COLUMN owner_album_id INT DEFAULT 0;"; 83 const std::string SQL_TEMP_PHOTO_MAP_TABLE_DROP = "DROP TABLE IF EXISTS temp_photo_map;"; 84 // 使用 group 分组查找 Photos 的一条关联关系 85 const std::string SQL_TEMP_PHOTO_MAP_TABLE_CREATE = "\ 86 CREATE TABLE IF NOT EXISTS temp_photo_map \ 87 AS \ 88 SELECT MIN(map_album) AS map_album, \ 89 map_asset \ 90 FROM PhotoMap \ 91 INNER JOIN Photos \ 92 ON PhotoMap.map_asset=Photos.file_id \ 93 WHERE position IN (1, 3) \ 94 GROUP BY map_asset;"; 95 const std::vector<std::string> SQL_TEMP_PHOTO_MAP_TABLE_CREATE_INDEX_ARRAY = { 96 "CREATE INDEX IF NOT EXISTS unique_index_temp_photo_map_map_asset ON temp_photo_map ( \ 97 map_asset \ 98 );", 99 "CREATE UNIQUE INDEX IF NOT EXISTS unique_index_temp_photo_map_all ON temp_photo_map ( \ 100 map_album, map_asset \ 101 );"}; 102 const std::vector<std::string> SQL_PHOTOS_TABLE_DROP_TRIGGER_ARRAY = { 103 "DROP TRIGGER IF EXISTS photos_delete_trigger;", 104 "DROP TRIGGER IF EXISTS photos_fdirty_trigger;", 105 "DROP TRIGGER IF EXISTS photos_mdirty_trigger;", 106 "DROP TRIGGER IF EXISTS photo_insert_cloud_sync_trigger;", 107 "DROP TRIGGER IF EXISTS photo_update_cloud_sync_trigger;", 108 "DROP TRIGGER IF EXISTS delete_photo_clear_map;", 109 "DROP TRIGGER IF EXISTS update_vision_trigger;", 110 "DROP TRIGGER IF EXISTS delete_vision_trigger;", 111 "DROP TRIGGER IF EXISTS insert_vision_trigger;", 112 "DROP TRIGGER IF EXISTS insert_photo_insert_source_album;", 113 "DROP TRIGGER IF EXISTS insert_photo_update_source_album;", 114 "DROP TRIGGER IF EXISTS insert_photo_update_album_bundlename;", 115 "DROP TRIGGER IF EXISTS insert_search_trigger;", 116 "DROP TRIGGER IF EXISTS update_search_trigger;", 117 "DROP TRIGGER IF EXISTS update_search_status_trigger;", 118 "DROP TRIGGER IF EXISTS delete_search_trigger;"}; 119 // owner_album_id 默认值是 0，使用 UNION 避免无查询结果时，被更新为 NULL 120 const std::string SQL_PHOTOS_TABLE_UPDATE_ALBUM_ID = "\ 121 UPDATE Photos SET owner_album_id = \ 122 ( \ 123 SELECT map_album \ 124 FROM temp_photo_map \ 125 WHERE file_id=map_asset \ 126 UNION \ 127 SELECT 0 AS map_album \ 128 ORDER BY map_album DESC \ 129 LIMIT 1 \ 130 ) \ 131 WHERE COALESCE(owner_album_id, 0)=0;"; 132 const std::vector<std::string> SQL_PHOTO_MAP_TABLE_DROP_TRIGGER_ARRAY = { 133 "DROP TRIGGER IF EXISTS album_map_delete_search_trigger;", 134 "DROP TRIGGER IF EXISTS album_map_delete_trigger;", 135 "DROP TRIGGER IF EXISTS album_map_insert_cloud_sync_trigger;", 136 "DROP TRIGGER IF EXISTS album_map_insert_search_trigger;"}; 137 const std::string SQL_PHOTO_MAP_TABLE_DELETE_SINGLE_RELATIONSHIP = "\ 138 DELETE FROM PhotoMap \ 139 WHERE EXISTS \ 140 ( \ 141 SELECT 1 \ 142 FROM temp_photo_map \ 143 WHERE PhotoMap.map_album=temp_photo_map.map_album AND \ 144 PhotoMap.map_asset=temp_photo_map.map_asset \ 145 );"; 146 /* Clear the cache table. */ 147 const std::string SQL_TEMP_ALBUM_BUNDLE_NAME_DELETE = "\ 148 DROP TABLE IF EXISTS temp_album_bundle_name;"; 149 /* Cache the mapping of old to new bundle names */ 150 const std::string SQL_TEMP_ALBUM_BUNDLE_NAME_CREATE = "\ 151 CREATE TABLE IF NOT EXISTS temp_album_bundle_name \ 152 AS \ 153 SELECT \ 154 'com.huawei.ohos.screenrecorder' AS bundle_name_old, \ 155 'com.huawei.hmos.screenrecorder' AS bundle_name_new \ 156 UNION \ 157 SELECT \ 158 'com.huawei.ohos.screenshot' AS bundle_name_old, \ 159 'com.huawei.hmos.screenshot' AS bundle_name_new \ 160 ;"; 161 /* Create the Album if it doesn't exist */ 162 const std::string SQL_PHOTO_ALBUM_INSERT_NEW_ALBUM = "\ 163 INSERT INTO PhotoAlbum( \ 164 album_type, \ 165 album_subtype, \ 166 album_name, \ 167 bundle_name, \ 168 lpath \ 169 ) \ 170 SELECT \ 171 PA1.album_type, \ 172 PA1.album_subtype, \ 173 PA1.album_name, \ 174 M.bundle_name_new AS bundle_name, \ 175 CASE \ 176 WHEN COALESCE(album_plugin.lpath,'') <> '' THEN album_plugin.lpath \ 177 ELSE '/Pictures/'||PA1.album_name \ 178 END AS lpath \ 179 FROM PhotoAlbum AS PA1 \ 180 INNER JOIN temp_album_bundle_name AS M \ 181 ON PA1.bundle_name=M.bundle_name_old \ 182 LEFT JOIN PhotoAlbum AS PA2 \ 183 ON M.bundle_name_new=PA2.bundle_name \ 184 LEFT JOIN album_plugin \ 185 ON M.bundle_name_new=album_plugin.bundle_name \ 186 WHERE PA2.bundle_name IS NULL;"; 187 /* Add the relationship in PhotoMap for new Album and Photo */ 188 const std::string SQL_PHOTO_MAP_INSERT_NEW_ALBUM = "\ 189 INSERT INTO PhotoMap( \ 190 map_album, \ 191 map_asset \ 192 ) \ 193 SELECT \ 194 PA2.album_id AS album_id_new, \ 195 PM.map_asset \ 196 FROM PhotoAlbum AS PA1 \ 197 INNER JOIN temp_album_bundle_name AS M \ 198 ON PA1.bundle_name=M.bundle_name_old \ 199 INNER JOIN PhotoAlbum AS PA2 \ 200 ON M.bundle_name_new=PA2.bundle_name \ 201 INNER JOIN PhotoMap AS PM \ 202 ON PA1.album_id=PM.map_album \ 203 EXCEPT \ 204 SELECT PA.album_id, \ 205 PM.map_asset \ 206 FROM PhotoAlbum AS PA \ 207 INNER JOIN temp_album_bundle_name AS M \ 208 ON PA.bundle_name=M.bundle_name_new \ 209 INNER JOIN PhotoMap AS PM \ 210 ON PA.album_id=PM.map_album \ 211 ; "; 212 /* Remove the relationship in PhotoMap for old Album and Photo */ 213 const std::string SQL_PHOTO_MAP_DELETE_OLD_ALBUM = "\ 214 DELETE FROM PhotoMap \ 215 WHERE map_album IN \ 216 ( \ 217 SELECT \ 218 PA1.album_id \ 219 FROM PhotoAlbum AS PA1 \ 220 INNER JOIN temp_album_bundle_name AS M \ 221 ON PA1.bundle_name=M.bundle_name_old \ 222 );"; 223 /* Replace the relationship in Photos for new Album and Photo */ 224 const std::string SQL_PHOTOS_UPDATE_NEW_ALBUM = "\ 225 UPDATE Photos \ 226 SET owner_album_id=( \ 227 SELECT PA2.album_id \ 228 FROM PhotoAlbum AS PA1 \ 229 INNER JOIN temp_album_bundle_name AS M \ 230 ON PA1.bundle_name=M.bundle_name_old \ 231 INNER JOIN PhotoAlbum AS PA2 \ 232 ON M.bundle_name_new=PA2.bundle_name \ 233 WHERE Photos.owner_album_id=PA1.album_id \ 234 ) \ 235 WHERE owner_album_id IN \ 236 ( \ 237 SELECT \ 238 PA1.album_id \ 239 FROM PhotoAlbum AS PA1 \ 240 INNER JOIN temp_album_bundle_name AS M \ 241 ON PA1.bundle_name=M.bundle_name_old \ 242 ); "; 243 /* Remove the relationship in Photos for old Album and Photo */ 244 const std::string SQL_PHOTO_ALBUM_DELETE_OLD_ALBUM = "\ 245 DELETE FROM PhotoAlbum \ 246 WHERE album_id IN \ 247 ( \ 248 SELECT \ 249 PA.album_id \ 250 FROM PhotoAlbum AS PA \ 251 INNER JOIN temp_album_bundle_name AS M \ 252 ON PA.bundle_name=M.bundle_name_old \ 253 ); "; 254 const std::vector<std::string> SQL_MERGE_ALBUM_FROM_OLD_BUNDLE_NAME_TO_NEW_BUNDLE_NAME = { 255 SQL_TEMP_ALBUM_BUNDLE_NAME_DELETE, 256 SQL_TEMP_ALBUM_BUNDLE_NAME_CREATE, 257 SQL_PHOTO_ALBUM_INSERT_NEW_ALBUM, 258 SQL_PHOTO_MAP_INSERT_NEW_ALBUM, 259 SQL_PHOTO_MAP_DELETE_OLD_ALBUM, 260 SQL_PHOTOS_UPDATE_NEW_ALBUM, 261 SQL_PHOTO_ALBUM_DELETE_OLD_ALBUM, 262 SQL_TEMP_ALBUM_BUNDLE_NAME_DELETE, 263 }; 264 /* Create the Album if it doesn't exist */ 265 const std::string SQL_PHOTO_ALBUM_INSERT_OTHER_ALBUM = "\ 266 INSERT INTO PhotoAlbum( \ 267 album_type, \ 268 album_subtype, \ 269 album_name, \ 270 bundle_name, \ 271 lpath \ 272 ) \ 273 SELECT \ 274 2048 AS album_type, \ 275 2049 AS album_subtype, \ 276 '其它' AS album_name, \ 277 'com.other.album' AS bundle_name, \ 278 '/Pictures/其它' AS lpath \ 279 EXCEPT \ 280 SELECT \ 281 album_type, \ 282 album_subtype, \ 283 album_name, \ 284 bundle_name, \ 285 lpath \ 286 FROM PhotoAlbum ;"; 287 /* The Photo, doesn't belong to any album, should belongs to '其它' album */ 288 const std::string SQL_PHOTOS_UPDATE_OTHER_ALBUM = "\ 289 UPDATE Photos \ 290 SET owner_album_id=( \ 291 SELECT album_id \ 292 FROM PhotoAlbum \ 293 WHERE lpath='/Pictures/其它' \ 294 LIMIT 1 \ 295 ) \ 296 WHERE file_id IN \ 297 ( \ 298 SELECT file_id \ 299 FROM Photos \ 300 LEFT JOIN PhotoMap \ 301 ON Photos.file_id = PhotoMap.map_asset \ 302 WHERE PhotoMap.map_asset IS NULL AND \ 303 COALESCE(Photos.owner_album_id, 0) = 0 AND \ 304 Photos.position IN (1, 3) \ 305 );"; 306 const std::vector<std::string> SQL_PHOTOS_NEED_TO_BELONGS_TO_ALBUM = { 307 SQL_PHOTO_ALBUM_INSERT_OTHER_ALBUM, 308 SQL_PHOTOS_UPDATE_OTHER_ALBUM, 309 }; 310 }; 311 } // namespace DataTransfer 312 } // namespace OHOS::Media 313 #endif // OHOS_MEDIA_DATATRANSFER_MEDIA_LIBRARY_DB_UPGRADE_H