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 PHOTO_ALBUM_NOTIFY_FUNC = 80 "SELECT photo_album_notify_func((SELECT " + PhotoColumn::PHOTO_OWNER_ALBUM_ID + 81 " FROM " + PhotoColumn::PHOTOS_TABLE + 82 " WHERE " + MediaColumn::MEDIA_ID + " = NEW." + MediaColumn::MEDIA_ID + "));"; 83 84 const std::string CREATE_INSERT_SOURCE_PHOTO_CREATE_SOURCE_ALBUM_TRIGGER = 85 "CREATE TRIGGER IF NOT EXISTS insert_source_photo_create_source_album_trigger AFTER INSERT ON " + 86 PhotoColumn::PHOTOS_TABLE + WHEN_SOURCE_PHOTO_COUNT + " = 0 " + 87 " BEGIN INSERT INTO " + PhotoAlbumColumns::TABLE + "(" + 88 PhotoAlbumColumns::ALBUM_TYPE + " , " + 89 PhotoAlbumColumns::ALBUM_SUBTYPE + " , " + 90 PhotoAlbumColumns::ALBUM_NAME + " , " + 91 PhotoAlbumColumns::ALBUM_BUNDLE_NAME + " , " + 92 PhotoAlbumColumns::ALBUM_LPATH + " , " + 93 PhotoAlbumColumns::ALBUM_PRIORITY + " , " + 94 PhotoAlbumColumns::ALBUM_DATE_ADDED + 95 " ) VALUES ( " + 96 std::to_string(OHOS::Media::PhotoAlbumType::SOURCE) + " , " + 97 std::to_string(OHOS::Media::PhotoAlbumSubType::SOURCE_GENERIC) + " , " + 98 "NEW." + MediaColumn::MEDIA_PACKAGE_NAME + " , " + 99 "NEW." + MediaColumn::MEDIA_OWNER_PACKAGE + " , " + 100 "COALESCE((SELECT lpath from album_plugin WHERE ((bundle_name = " 101 "NEW.owner_package AND COALESCE(NEW.owner_package,'')!= '') OR album_name = NEW.package_name) " 102 "and priority ='1'), '/Pictures/'||NEW.package_name), 1, " 103 "strftime('%s000', 'now')" + 104 ");" + FILL_ALBUM_ID_FOR_PHOTOS + "; " + PHOTO_ALBUM_NOTIFY_FUNC + " END;"; 105 106 const std::string CREATE_INSERT_SOURCE_UPDATE_ALBUM_ID_TRIGGER = 107 "CREATE TRIGGER IF NOT EXISTS insert_source_photo_update_album_id_trigger AFTER INSERT ON " + 108 PhotoColumn::PHOTOS_TABLE + WHEN_SOURCE_PHOTO_COUNT + "> 0 AND NEW.owner_album_id = 0" + 109 " BEGIN " + FILL_ALBUM_ID_FOR_PHOTOS + "; END;"; 110 111 const std::string QUERY_NOT_MATCHED_DATA_IN_PHOTOMAP_BY_PAGE = 112 "SELECT " + PhotoMap::ASSET_ID + ", " + PhotoMap::ALBUM_ID + " FROM " + PhotoMap::TABLE + 113 " WHERE dirty <>4 and dirty<>6 LIMIT 0, 200"; 114 115 const std::string QUERY_NEW_NOT_MATCHED_DATA_IN_PHOTOMAP_BY_PAGE = 116 "SELECT " + PhotoMap::ASSET_ID + ", " + PhotoMap::ALBUM_ID + " FROM " + PhotoMap::TABLE + 117 " WHERE dirty <>4 LIMIT 0, 200"; 118 119 const std::string QUERY_NOT_MATCHED_COUNT_IN_PHOTOMAP = 120 "SELECT count(1) FROM PhotoMap WHERE dirty <>4 and dirty<>6"; 121 122 const std::string QUERY_NEW_NOT_MATCHED_COUNT_IN_PHOTOMAP = 123 "SELECT count(1) FROM PhotoMap WHERE dirty <>4"; 124 125 const std::string CREATE_DEFALUT_ALBUM_FOR_NO_RELATIONSHIP_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', '其它', 'com.other.album', '1', '1', strftime('%s000', 'now'), '/Pictures/其它', '1')"; 129 130 const std::string CREATE_HIDDEN_ALBUM_FOR_DUAL_ASSET = 131 "INSERT INTO " + PhotoAlbumColumns::TABLE + 132 "(album_type, album_subtype, album_name, bundle_name, dirty, is_local, date_added, lpath, priority)" 133 " Values ('2048', '2049', '.hiddenAlbum', 'com.hidden.album', '1', " 134 "'1', strftime('%s000', 'now'), '/Pictures/hiddenAlbum', '1')"; 135 136 const std::string SELECT_HIDDEN_ALBUM_ID = 137 "(SELECT " + PhotoAlbumColumns::ALBUM_ID + " FROM " + PhotoAlbumColumns::TABLE + " WHERE " + 138 PhotoAlbumColumns::ALBUM_NAME + " = '.hiddenAlbum' AND " + PhotoAlbumColumns::ALBUM_DIRTY + 139 " <> 4 AND " + PhotoAlbumColumns::ALBUM_TYPE + " = '2048')"; 140 141 const std::string SELECT_ALL_HIDDEN_ALBUM_ASSET_ID = 142 "(SELECT " + PhotoMap::ASSET_ID + " FROM " + PhotoMap::TABLE + " WHERE " + 143 PhotoMap::ALBUM_ID + " = " + SELECT_HIDDEN_ALBUM_ID + ")"; 144 145 const std::string DROP_UNWANTED_ALBUM_RELATIONSHIP_FOR_HIDDEN_ALBUM_ASSET = 146 "UPDATE " + PhotoMap::TABLE + " SET " + PhotoMap::DIRTY + " = 4 " + 147 "WHERE " + PhotoMap::ASSET_ID + " in " + SELECT_ALL_HIDDEN_ALBUM_ASSET_ID + 148 " AND " + PhotoMap::ALBUM_ID + " <> " + SELECT_HIDDEN_ALBUM_ID; 149 150 } // namespace Media 151 } // namespace OHOS 152 #endif // OHOS_MEDIALIBRARY_ALBUM_COMPATIBILITY_FUSION_DATA_SQL_H