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_MEDIALIBRARY_ALBUM_COMPATIBILITY_FUSION_DATA_SQL_H 17 #define OHOS_MEDIALIBRARY_ALBUM_COMPATIBILITY_FUSION_DATA_SQL_H 18 19 #include "media_column.h" 20 #include "photo_map_column.h" 21 #include "source_album.h" 22 23 namespace OHOS { 24 namespace Media { 25 26 const std::string CREATE_TEMP_UPGRADE_PHOTO_MAP_TABLE = 27 "CREATE TABLE IF NOT EXISTS temp_upgrade_photo_map AS SELECT MIN(map_album) AS map_album, map_asset FROM PhotoMap " 28 "INNER JOIN Photos ON PhotoMap.map_asset=Photos.file_id where COALESCE(owner_album_id, 0) = 0 GROUP BY map_asset;"; 29 30 const std::string QUERY_MATCHED_COUNT = 31 "SELECT COUNT(1) from temp_upgrade_photo_map"; 32 33 const std::string QUERY_SUCCESS_MATCHED_COUNT = 34 "SELECT COUNT(1) from Photos where owner_album_id != 0"; 35 36 const std::string CREATE_UNIQUE_TEMP_UPGRADE_INDEX_ON_MAP_ASSET = 37 "CREATE INDEX IF NOT EXISTS unique_temp_upgrade_index_on_map_asset ON temp_upgrade_photo_map (map_asset);"; 38 39 const std::string CREATE_UNIQUE_TEMP_UPGRADE_INDEX_ON_PHOTO_MAP = 40 "CREATE UNIQUE INDEX IF NOT EXISTS unique_temp_upgrade_index_on_photo_map ON " 41 "temp_upgrade_photo_map (map_album, map_asset);"; 42 43 const std::string UPDATE_ALBUM_ASSET_MAPPING_CONSISTENCY_DATA_SQL = 44 "UPDATE Photos SET owner_album_id =(SELECT map_album FROM temp_upgrade_photo_map WHERE file_id=map_asset " 45 "UNION SELECT 0 AS map_album ORDER BY map_album DESC LIMIT 1) WHERE COALESCE(owner_album_id, 0)=0;"; 46 47 const std::string DROP_PHOTO_ALBUM_CLEAR_MAP_SQL = 48 "DROP TRIGGER IF EXISTS photo_album_clear_map"; 49 const std::string DROP_INSERT_PHOTO_INSERT_SOURCE_ALBUM_SQL = 50 "DROP TRIGGER IF EXISTS insert_photo_insert_source_album"; 51 const std::string DROP_INSERT_PHOTO_UPDATE_SOURCE_ALBUM_SQL = 52 "DROP TRIGGER IF EXISTS insert_photo_update_source_album"; 53 54 const std::string DROP_INSERT_SOURCE_PHOTO_CREATE_SOURCE_ALBUM_TRIGGER = 55 "DROP TRIGGER IF EXISTS insert_source_photo_create_source_album_trigger"; 56 const std::string DROP_INSERT_SOURCE_PHOTO_UPDATE_ALBUM_ID_TRIGGER = 57 "DROP TRIGGER IF EXISTS insert_source_photo_update_album_id_trigger"; 58 59 const std::string DROP_INDEX_SOURCE_ALBUM_INDEX = 60 "DROP INDEX IF EXISTS " + SOURCE_ALBUM_INDEX; 61 62 const std::string DELETE_MATCHED_RELATIONSHIP_IN_PHOTOMAP_SQL = 63 "UPDATE PhotoMap SET dirty = '4' WHERE EXISTS (SELECT 1 FROM temp_upgrade_photo_map " 64 " WHERE PhotoMap.map_album=temp_upgrade_photo_map.map_album AND " 65 " PhotoMap.map_asset=temp_upgrade_photo_map.map_asset);"; 66 67 const std::string DROP_TEMP_UPGRADE_PHOTO_MAP_TABLE = 68 "DROP TABLE IF EXISTS temp_upgrade_photo_map;"; 69 70 const std::string FILL_ALBUM_ID_FOR_PHOTOS = 71 "UPDATE " + PhotoColumn::PHOTOS_TABLE + " SET " + PhotoColumn::PHOTO_OWNER_ALBUM_ID + " = " + 72 "(SELECT " + PhotoAlbumColumns::ALBUM_ID + " FROM " + PhotoAlbumColumns::TABLE + 73 " WHERE (" + PhotoAlbumColumns::ALBUM_NAME + " = NEW." + MediaColumn::MEDIA_PACKAGE_NAME + 74 " OR bundle_name = NEW.owner_package) AND " + 75 PhotoAlbumColumns::ALBUM_TYPE + " = " + std::to_string(OHOS::Media::PhotoAlbumType::SOURCE) + " AND " + 76 PhotoAlbumColumns::ALBUM_SUBTYPE + " = " + std::to_string(OHOS::Media::PhotoAlbumSubType::SOURCE_GENERIC) + 77 " AND dirty != 4 ORDER BY priority DESC LIMIT 1) WHERE file_id = new.file_id"; 78 79 const std::string CREATE_INSERT_SOURCE_PHOTO_CREATE_SOURCE_ALBUM_TRIGGER = 80 "CREATE TRIGGER IF NOT EXISTS insert_source_photo_create_source_album_trigger AFTER INSERT ON " + 81 PhotoColumn::PHOTOS_TABLE + WHEN_SOURCE_PHOTO_COUNT + " = 0 " + 82 " BEGIN INSERT INTO " + PhotoAlbumColumns::TABLE + "(" + 83 PhotoAlbumColumns::ALBUM_TYPE + " , " + 84 PhotoAlbumColumns::ALBUM_SUBTYPE + " , " + 85 PhotoAlbumColumns::ALBUM_NAME + " , " + 86 PhotoAlbumColumns::ALBUM_BUNDLE_NAME + " , " + 87 PhotoAlbumColumns::ALBUM_LPATH + " , " + 88 PhotoAlbumColumns::ALBUM_PRIORITY + " , " + 89 PhotoAlbumColumns::ALBUM_DATE_ADDED + 90 " ) VALUES ( " + 91 std::to_string(OHOS::Media::PhotoAlbumType::SOURCE) + " , " + 92 std::to_string(OHOS::Media::PhotoAlbumSubType::SOURCE_GENERIC) + " , " + 93 "NEW." + MediaColumn::MEDIA_PACKAGE_NAME + " , " + 94 "NEW." + MediaColumn::MEDIA_OWNER_PACKAGE + " , " + 95 "COALESCE((SELECT lpath from album_plugin WHERE ((bundle_name = " 96 "NEW.owner_package AND COALESCE(NEW.owner_package,'')!= '') OR album_name = NEW.package_name) " 97 "and priority ='1'), '/Pictures/'||NEW.package_name), 1, " 98 "strftime('%s000', 'now')" + 99 ");" + FILL_ALBUM_ID_FOR_PHOTOS + "; END;"; 100 101 const std::string CREATE_INSERT_SOURCE_UPDATE_ALBUM_ID_TRIGGER = 102 "CREATE TRIGGER IF NOT EXISTS insert_source_photo_update_album_id_trigger AFTER INSERT ON " + 103 PhotoColumn::PHOTOS_TABLE + WHEN_SOURCE_PHOTO_COUNT + "> 0 AND NEW.owner_album_id = 0" + 104 " BEGIN " + FILL_ALBUM_ID_FOR_PHOTOS + "; END;"; 105 106 const std::string QUERY_NOT_MATCHED_DATA_IN_PHOTOMAP_BY_PAGE = 107 "SELECT " + PhotoMap::ASSET_ID + ", " + PhotoMap::ALBUM_ID + " FROM " + PhotoMap::TABLE + 108 " WHERE dirty <>4 and dirty<>6 LIMIT 0, 200"; 109 110 const std::string QUERY_NEW_NOT_MATCHED_DATA_IN_PHOTOMAP_BY_PAGE = 111 "SELECT " + PhotoMap::ASSET_ID + ", " + PhotoMap::ALBUM_ID + " FROM " + PhotoMap::TABLE + 112 " WHERE dirty <>4 LIMIT 0, 200"; 113 114 const std::string QUERY_NOT_MATCHED_COUNT_IN_PHOTOMAP = 115 "SELECT count(1) FROM PhotoMap WHERE dirty <>4 and dirty<>6"; 116 117 const std::string QUERY_NEW_NOT_MATCHED_COUNT_IN_PHOTOMAP = 118 "SELECT count(1) FROM PhotoMap WHERE dirty <>4"; 119 120 const std::string CREATE_DEFALUT_ALBUM_FOR_NO_RELATIONSHIP_ASSET = 121 "INSERT INTO " + PhotoAlbumColumns::TABLE + 122 "(album_type, album_subtype, album_name,bundle_name, dirty, is_local, date_added, lpath, priority)" 123 " Values ('2048', '2049', '其它', 'com.other.album', '1', '1', strftime('%s000', 'now'), '/Pictures/其它', '1')"; 124 125 const std::string CREATE_HIDDEN_ALBUM_FOR_DUAL_ASSET = 126 "INSERT INTO " + PhotoAlbumColumns::TABLE + 127 "(album_type, album_subtype, album_name, bundle_name, dirty, is_local, date_added, lpath, priority)" 128 " Values ('2048', '2049', '.hiddenAlbum', 'com.hidden.album', '1', " 129 "'1', strftime('%s000', 'now'), '/Pictures/hiddenAlbum', '1')"; 130 131 const std::string SELECT_HIDDEN_ALBUM_ID = 132 "(SELECT " + PhotoAlbumColumns::ALBUM_ID + " FROM " + PhotoAlbumColumns::TABLE + " WHERE " + 133 PhotoAlbumColumns::ALBUM_NAME + " = '.hiddenAlbum' AND " + PhotoAlbumColumns::ALBUM_DIRTY + 134 " <> 4 AND " + PhotoAlbumColumns::ALBUM_TYPE + " = '2048')"; 135 136 const std::string SELECT_ALL_HIDDEN_ALBUM_ASSET_ID = 137 "(SELECT " + PhotoMap::ASSET_ID + " FROM " + PhotoMap::TABLE + " WHERE " + 138 PhotoMap::ALBUM_ID + " = " + SELECT_HIDDEN_ALBUM_ID + ")"; 139 140 const std::string DROP_UNWANTED_ALBUM_RELATIONSHIP_FOR_HIDDEN_ALBUM_ASSET = 141 "UPDATE " + PhotoMap::TABLE + " SET " + PhotoMap::DIRTY + " = 4 " + 142 "WHERE " + PhotoMap::ASSET_ID + " in " + SELECT_ALL_HIDDEN_ALBUM_ASSET_ID + 143 " AND " + PhotoMap::ALBUM_ID + " <> " + SELECT_HIDDEN_ALBUM_ID; 144 145 } // namespace Media 146 } // namespace OHOS 147 #endif // OHOS_MEDIALIBRARY_ALBUM_COMPATIBILITY_FUSION_DATA_SQL_H