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 #define MLOG_TAG "DuplicatePhotoOperation"
17 
18 #include "duplicate_photo_operation.h"
19 
20 #include "media_file_utils.h"
21 #include "media_log.h"
22 #include "medialibrary_rdbstore.h"
23 #include "medialibrary_tracer.h"
24 #include "medialibrary_unistore_manager.h"
25 
26 namespace OHOS {
27 namespace Media {
28 std::once_flag DuplicatePhotoOperation::onceFlag_;
29 
30 const std::string ASTERISK = "*";
31 
32 const std::string SELECT_COLUMNS = "SELECT_COLUMNS";
33 
34 const std::string IDX_DUPLICATE_ASSETS = "\
35     CREATE INDEX \
36     IF \
37       NOT EXISTS idx_duplicate_assets ON Photos (title, size, orientation)";
38 
39 const std::string SQL_QUERY_ALL_DUPLICATE_ASSETS = "\
40     SELECT\
41       SELECT_COLUMNS \
42     FROM\
43       Photos\
44       INNER JOIN (\
45       SELECT\
46         title,\
47         size,\
48         orientation \
49       FROM\
50         Photos \
51       WHERE\
52         sync_status = 0 \
53         AND clean_flag = 0 \
54         AND date_trashed = 0 \
55         AND hidden = 0 \
56         AND time_pending = 0 \
57         AND is_temp = 0 \
58         AND burst_cover_level = 1 \
59         AND media_type = 1 \
60       GROUP BY\
61         title,\
62         size,\
63         orientation \
64       HAVING\
65         count(*) > 1 \
66       ) AS IMG ON Photos.title = IMG.title \
67       AND Photos.size = IMG.size \
68       AND Photos.orientation = IMG.orientation \
69     WHERE\
70       sync_status = 0 \
71       AND clean_flag = 0 \
72       AND date_trashed = 0 \
73       AND hidden = 0 \
74       AND time_pending = 0 \
75       AND is_temp = 0 \
76       AND burst_cover_level = 1 UNION\
77     SELECT\
78       SELECT_COLUMNS \
79     FROM\
80       Photos\
81       INNER JOIN (\
82       SELECT\
83         title,\
84         size \
85       FROM\
86         Photos \
87       WHERE\
88         sync_status = 0 \
89         AND clean_flag = 0 \
90         AND date_trashed = 0 \
91         AND hidden = 0 \
92         AND time_pending = 0 \
93         AND is_temp = 0 \
94         AND burst_cover_level = 1 \
95         AND media_type = 2 \
96       GROUP BY\
97         title,\
98         size \
99       HAVING\
100         count(*) > 1 \
101       ) AS VID ON Photos.title = VID.title \
102       AND Photos.size = VID.size \
103     WHERE\
104       sync_status = 0 \
105       AND clean_flag = 0 \
106       AND date_trashed = 0 \
107       AND hidden = 0 \
108       AND time_pending = 0 \
109       AND is_temp = 0 \
110       AND burst_cover_level = 1 \
111     ORDER BY\
112       Photos.title,\
113       Photos.size,\
114       Photos.orientation \
115       LIMIT ? OFFSET ? ";
116 
117 const std::string SQL_QUERY_ALL_DUPLICATE_ASSETS_COUNT = "\
118     SELECT\
119       count(*) \
120     FROM\
121       (\
122       SELECT\
123         file_id \
124       FROM\
125         Photos\
126         INNER JOIN (\
127         SELECT\
128           title,\
129           size,\
130           orientation \
131         FROM\
132           Photos \
133         WHERE\
134           sync_status = 0 \
135           AND clean_flag = 0 \
136           AND date_trashed = 0 \
137           AND hidden = 0 \
138           AND time_pending = 0 \
139           AND is_temp = 0 \
140           AND burst_cover_level = 1 \
141           AND media_type = 1 \
142         GROUP BY\
143           title,\
144           size,\
145           orientation \
146         HAVING\
147           count(*) > 1 \
148         ) AS IMG ON Photos.title = IMG.title \
149         AND Photos.size = IMG.size \
150         AND Photos.orientation = IMG.orientation \
151       WHERE\
152         sync_status = 0 \
153         AND clean_flag = 0 \
154         AND date_trashed = 0 \
155         AND hidden = 0 \
156         AND time_pending = 0 \
157         AND is_temp = 0 \
158         AND burst_cover_level = 1 UNION\
159       SELECT\
160         file_id \
161       FROM\
162         Photos\
163         INNER JOIN (\
164         SELECT\
165           title,\
166           size \
167         FROM\
168           Photos \
169         WHERE\
170           sync_status = 0 \
171           AND clean_flag = 0 \
172           AND date_trashed = 0 \
173           AND hidden = 0 \
174           AND time_pending = 0 \
175           AND is_temp = 0 \
176           AND burst_cover_level = 1 \
177           AND media_type = 2 \
178         GROUP BY\
179           title,\
180           size \
181         HAVING\
182           count(*) > 1 \
183         ) AS VID ON Photos.title = VID.title \
184         AND Photos.size = VID.size \
185       WHERE\
186         sync_status = 0 \
187         AND clean_flag = 0 \
188         AND date_trashed = 0 \
189         AND hidden = 0 \
190         AND time_pending = 0 \
191         AND is_temp = 0 \
192         AND burst_cover_level = 1 \
193       ) ";
194 
195 const std::string SQL_QUERY_CAN_DEL_DUPLICATE_ASSETS = "\
196     SELECT\
197       SELECT_COLUMNS \
198     FROM\
199       (\
200       SELECT\
201         SELECT_COLUMNS,\
202         ROW_NUMBER( ) OVER (\
203           PARTITION BY title,\
204           size,\
205           orientation \
206         ORDER BY\
207         CASE\
208             WHEN album_id != NULL THEN\
209             0 ELSE 1 \
210           END ASC,\
211         CASE\
212             WHEN lpath = '/DCIM/Camera' THEN\
213             0 \
214             WHEN lpath = '/Pictures/Screenshots' THEN\
215             1 \
216             WHEN lpath = '/Pictures/Screenrecords' THEN\
217             2 \
218             WHEN lpath = '/Pictures/WeiXin' THEN\
219             3 \
220             WHEN lpath IN ( '/Pictures/WeChat', '/tencent/MicroMsg/WeChat', '/Tencent/MicroMsg/WeiXin' ) THEN\
221             4 ELSE 5 \
222         END ASC \
223         ) AS img_row_num \
224       FROM\
225         Photos\
226         LEFT JOIN PhotoAlbum ON Photos.owner_album_id = PhotoAlbum.album_id \
227       WHERE\
228         sync_status = 0 \
229         AND clean_flag = 0 \
230         AND date_trashed = 0 \
231         AND hidden = 0 \
232         AND time_pending = 0 \
233         AND is_temp = 0 \
234         AND burst_cover_level = 1 \
235         AND media_type = 1 \
236       ) \
237     WHERE\
238       img_row_num > 1 UNION\
239     SELECT\
240       SELECT_COLUMNS \
241     FROM\
242       (\
243       SELECT\
244         SELECT_COLUMNS,\
245         ROW_NUMBER( ) OVER (\
246           PARTITION BY title,\
247           size \
248         ORDER BY\
249         CASE\
250             WHEN album_id != NULL THEN\
251             0 ELSE 1 \
252           END ASC,\
253         CASE\
254             WHEN lpath = '/DCIM/Camera' THEN\
255             0 \
256             WHEN lpath = '/Pictures/Screenshots' THEN\
257             1 \
258             WHEN lpath = '/Pictures/Screenrecords' THEN\
259             2 \
260             WHEN lpath = '/Pictures/WeiXin' THEN\
261             3 \
262             WHEN lpath IN ( '/Pictures/WeChat', '/tencent/MicroMsg/WeChat', '/Tencent/MicroMsg/WeiXin' ) THEN\
263             4 ELSE 5 \
264         END ASC \
265         ) AS vid_row_num \
266       FROM\
267         Photos\
268         LEFT JOIN PhotoAlbum ON Photos.owner_album_id = PhotoAlbum.album_id \
269       WHERE\
270         sync_status = 0 \
271         AND clean_flag = 0 \
272         AND date_trashed = 0 \
273         AND hidden = 0 \
274         AND time_pending = 0 \
275         AND is_temp = 0 \
276         AND burst_cover_level = 1 \
277         AND media_type = 2 \
278       ) \
279     WHERE\
280       vid_row_num > 1 \
281     ORDER BY\
282       title,\
283       size,\
284       orientation \
285       LIMIT ? OFFSET ? ";
286 
287 const std::string SQL_QUERY_CAN_DEL_DUPLICATE_ASSETS_COUNT = "\
288     SELECT\
289       count(*) \
290     FROM\
291       (\
292       SELECT\
293         file_id \
294       FROM\
295         (\
296         SELECT\
297           file_id,\
298           ROW_NUMBER( ) OVER (\
299             PARTITION BY title,\
300             size,\
301             orientation \
302           ORDER BY\
303           CASE\
304               WHEN album_id != NULL THEN\
305               0 ELSE 1 \
306             END ASC,\
307           CASE\
308               WHEN lpath = '/DCIM/Camera' THEN\
309               0 \
310               WHEN lpath = '/Pictures/Screenshots' THEN\
311               1 \
312               WHEN lpath = '/Pictures/Screenrecords' THEN\
313               2 \
314               WHEN lpath = '/Pictures/WeiXin' THEN\
315               3 \
316               WHEN lpath IN ( '/Pictures/WeChat', '/tencent/MicroMsg/WeChat', '/Tencent/MicroMsg/WeiXin' ) THEN\
317               4 ELSE 5 \
318           END ASC \
319           ) AS img_row_num \
320         FROM\
321           Photos\
322           LEFT JOIN PhotoAlbum ON Photos.owner_album_id = PhotoAlbum.album_id \
323         WHERE\
324           sync_status = 0 \
325           AND clean_flag = 0 \
326           AND date_trashed = 0 \
327           AND hidden = 0 \
328           AND time_pending = 0 \
329           AND is_temp = 0 \
330           AND burst_cover_level = 1 \
331           AND media_type = 1 \
332         ) \
333       WHERE\
334         img_row_num > 1 UNION\
335       SELECT\
336         file_id \
337       FROM\
338         (\
339         SELECT\
340           file_id,\
341           ROW_NUMBER( ) OVER (\
342             PARTITION BY title,\
343             size \
344           ORDER BY\
345           CASE\
346               WHEN album_id != NULL THEN\
347               0 ELSE 1 \
348             END ASC,\
349           CASE\
350               WHEN lpath = '/DCIM/Camera' THEN\
351               0 \
352               WHEN lpath = '/Pictures/Screenshots' THEN\
353               1 \
354               WHEN lpath = '/Pictures/Screenrecords' THEN\
355               2 \
356               WHEN lpath = '/Pictures/WeiXin' THEN\
357               3 \
358               WHEN lpath IN ( '/Pictures/WeChat', '/tencent/MicroMsg/WeChat', '/Tencent/MicroMsg/WeiXin' ) THEN\
359               4 ELSE 5 \
360           END ASC \
361           ) AS vid_row_num \
362         FROM\
363           Photos\
364           LEFT JOIN PhotoAlbum ON Photos.owner_album_id = PhotoAlbum.album_id \
365         WHERE\
366           sync_status = 0 \
367           AND clean_flag = 0 \
368           AND date_trashed = 0 \
369           AND hidden = 0 \
370           AND time_pending = 0 \
371           AND is_temp = 0 \
372           AND burst_cover_level = 1 \
373           AND media_type = 2 \
374         ) \
375       WHERE\
376       vid_row_num > 1 \
377       ) ";
378 
GetSelectColumns(const std::unordered_set<std::string> & columns)379 std::string DuplicatePhotoOperation::GetSelectColumns(const std::unordered_set<std::string> &columns)
380 {
381     if (columns.empty()) {
382         return ASTERISK;
383     }
384 
385     std::string selectColumns;
386     bool first = true;
387     for (const std::string &column : columns) {
388         if (!first) {
389             selectColumns += ", ";
390         } else {
391             first = false;
392         }
393         selectColumns += column;
394     }
395 
396     return selectColumns;
397 }
398 
GetAllDuplicateAssets(const std::vector<std::string> & columns,const int offset,const int limit)399 std::shared_ptr<NativeRdb::ResultSet> DuplicatePhotoOperation::GetAllDuplicateAssets(
400     const std::vector<std::string> &columns, const int offset, const int limit)
401 {
402     auto rdbStore = MediaLibraryUnistoreManager::GetInstance().GetRdbStore();
403     if (rdbStore == nullptr) {
404         MEDIA_ERR_LOG("GetAllDuplicateAssets failed, rdbStore is nullptr");
405         return nullptr;
406     }
407     MediaLibraryTracer tracer;
408     if (find(columns.begin(), columns.end(), MEDIA_COLUMN_COUNT) != columns.end()) {
409         tracer.Start("QueryAllDuplicateAssets_count");
410         std::call_once(onceFlag_, [&]() { rdbStore->ExecuteSql(IDX_DUPLICATE_ASSETS); });
411         return rdbStore->QueryByStep(SQL_QUERY_ALL_DUPLICATE_ASSETS_COUNT);
412     }
413 
414     tracer.Start("QueryAllDuplicateAssets_records");
415     std::unordered_set<std::string> columnSet{ "Photos.file_id", "Photos.title", "Photos.size", "Photos.orientation" };
416     for (const auto &column : columns) {
417         if (MediaFileUtils::StartsWith(column, "Photos.")) {
418             columnSet.insert(column);
419         } else {
420             columnSet.insert("Photos." + column);
421         }
422     }
423 
424     std::string selectColumns = GetSelectColumns(columnSet);
425     std::string sql = SQL_QUERY_ALL_DUPLICATE_ASSETS;
426     MediaFileUtils::ReplaceAll(sql, SELECT_COLUMNS, selectColumns);
427 
428     const std::vector<NativeRdb::ValueObject> bindArgs{ NativeRdb::ValueObject(limit), NativeRdb::ValueObject(offset) };
429     return rdbStore->QueryByStep(sql, bindArgs);
430 }
431 
GetCanDelDuplicateAssets(const std::vector<std::string> & columns,const int offset,const int limit)432 std::shared_ptr<NativeRdb::ResultSet> DuplicatePhotoOperation::GetCanDelDuplicateAssets(
433     const std::vector<std::string> &columns, const int offset, const int limit)
434 {
435     auto rdbStore = MediaLibraryUnistoreManager::GetInstance().GetRdbStore();
436     if (rdbStore == nullptr) {
437         MEDIA_ERR_LOG("GetAllDuplicateAssets failed, rdbStore is nullptr");
438         return nullptr;
439     }
440     MediaLibraryTracer tracer;
441     if (find(columns.begin(), columns.end(), MEDIA_COLUMN_COUNT) != columns.end()) {
442         tracer.Start("QueryCanDelDuplicateAssets_count");
443         return rdbStore->QueryByStep(SQL_QUERY_CAN_DEL_DUPLICATE_ASSETS_COUNT);
444     }
445 
446     tracer.Start("QueryCanDelDuplicateAssets_records");
447     std::unordered_set<std::string> columnSet{ "file_id", "title", "size", "orientation" };
448     columnSet.insert(columns.begin(), columns.end());
449 
450     std::string selectColumns = GetSelectColumns(columnSet);
451     std::string sql = SQL_QUERY_CAN_DEL_DUPLICATE_ASSETS;
452     MediaFileUtils::ReplaceAll(sql, SELECT_COLUMNS, selectColumns);
453 
454     const std::vector<NativeRdb::ValueObject> bindArgs{ NativeRdb::ValueObject(limit), NativeRdb::ValueObject(offset) };
455     return rdbStore->QueryByStep(sql, bindArgs);
456 }
457 } // namespace Media
458 } // namespace OHOS
459