|
1
|
|
|
<?php declare(strict_types=1); |
|
2
|
|
|
|
|
3
|
|
|
/** |
|
4
|
|
|
* ownCloud - Music app |
|
5
|
|
|
* |
|
6
|
|
|
* This file is licensed under the Affero General Public License version 3 or |
|
7
|
|
|
* later. See the COPYING file. |
|
8
|
|
|
* |
|
9
|
|
|
* @author Morris Jobke <[email protected]> |
|
10
|
|
|
* @author Pauli Järvinen <[email protected]> |
|
11
|
|
|
* @copyright Morris Jobke 2013, 2014 |
|
12
|
|
|
* @copyright Pauli Järvinen 2016 - 2023 |
|
13
|
|
|
*/ |
|
14
|
|
|
|
|
15
|
|
|
namespace OCA\Music\Db; |
|
16
|
|
|
|
|
17
|
|
|
use OCP\IDBConnection; |
|
18
|
|
|
|
|
19
|
|
|
/** |
|
20
|
|
|
* @phpstan-extends BaseMapper<Track> |
|
21
|
|
|
*/ |
|
22
|
|
|
class TrackMapper extends BaseMapper { |
|
23
|
|
|
public function __construct(IDBConnection $db) { |
|
24
|
|
|
parent::__construct($db, 'music_tracks', Track::class, 'title'); |
|
25
|
|
|
} |
|
26
|
|
|
|
|
27
|
|
|
/** |
|
28
|
|
|
* Override the base implementation to include data from multiple tables |
|
29
|
|
|
* |
|
30
|
|
|
* {@inheritdoc} |
|
31
|
|
|
* @see BaseMapper::selectEntities() |
|
32
|
|
|
*/ |
|
33
|
|
|
protected function selectEntities(string $condition, string $extension=null) : string { |
|
34
|
|
|
return "SELECT `*PREFIX*music_tracks`.*, `file`.`name` AS `filename`, `file`.`size`, `file`.`mtime` AS `file_mod_time`, |
|
35
|
|
|
`album`.`name` AS `album_name`, `artist`.`name` AS `artist_name`, `genre`.`name` AS `genre_name` |
|
36
|
|
|
FROM `*PREFIX*music_tracks` |
|
37
|
|
|
INNER JOIN `*PREFIX*filecache` `file` |
|
38
|
|
|
ON `*PREFIX*music_tracks`.`file_id` = `file`.`fileid` |
|
39
|
|
|
INNER JOIN `*PREFIX*music_albums` `album` |
|
40
|
|
|
ON `*PREFIX*music_tracks`.`album_id` = `album`.`id` |
|
41
|
|
|
INNER JOIN `*PREFIX*music_artists` `artist` |
|
42
|
|
|
ON `*PREFIX*music_tracks`.`artist_id` = `artist`.`id` |
|
43
|
|
|
LEFT JOIN `*PREFIX*music_genres` `genre` |
|
44
|
|
|
ON `*PREFIX*music_tracks`.`genre_id` = `genre`.`id` |
|
45
|
|
|
WHERE $condition $extension"; |
|
46
|
|
|
} |
|
47
|
|
|
|
|
48
|
|
|
/** |
|
49
|
|
|
* Overridden from the base implementation to add support for sorting by artist, play_count, and last_played. |
|
50
|
|
|
* |
|
51
|
|
|
* {@inheritdoc} |
|
52
|
|
|
* @see BaseMapper::formatSortingClause() |
|
53
|
|
|
*/ |
|
54
|
|
|
protected function formatSortingClause(int $sortBy, bool $invertSort = false) : ?string { |
|
55
|
|
|
$dir = $invertSort ? 'DESC' : 'ASC'; |
|
56
|
|
|
switch ($sortBy) { |
|
57
|
|
|
case SortBy::Parent: |
|
58
|
|
|
// Note: the alternative form "LOWER(`artist_name`) wouldn't work on PostgreSQL, see https://github.com/owncloud/music/issues/1046 for a similar case |
|
59
|
|
|
return "ORDER BY LOWER(`artist`.`name`) $dir, LOWER(`title`) $dir"; |
|
60
|
|
|
case SortBy::PlayCount: |
|
61
|
|
|
return "ORDER BY LOWER(`play_count`) $dir"; |
|
62
|
|
|
case SortBy::LastPlayed: |
|
63
|
|
|
return "ORDER BY LOWER(`last_played`) $dir"; |
|
64
|
|
|
default: |
|
65
|
|
|
return parent::formatSortingClause($sortBy, $invertSort); |
|
66
|
|
|
} |
|
67
|
|
|
} |
|
68
|
|
|
|
|
69
|
|
|
/** |
|
70
|
|
|
* Returns all tracks of the given artist (both album and track artists are considered) |
|
71
|
|
|
* @return Track[] |
|
72
|
|
|
*/ |
|
73
|
|
|
public function findAllByArtist(int $artistId, string $userId, ?int $limit=null, ?int $offset=null) : array { |
|
74
|
|
|
$sql = $this->selectUserEntities( |
|
75
|
|
|
'`artist_id` = ? OR `album_id` IN (SELECT `id` from `*PREFIX*music_albums` WHERE `album_artist_id` = ?) ', |
|
76
|
|
|
'ORDER BY LOWER(`title`)'); |
|
77
|
|
|
$params = [$userId, $artistId, $artistId]; |
|
78
|
|
|
return $this->findEntities($sql, $params, $limit, $offset); |
|
|
|
|
|
|
79
|
|
|
} |
|
80
|
|
|
|
|
81
|
|
|
/** |
|
82
|
|
|
* @param int[] $albumIds |
|
83
|
|
|
* @return Track[] |
|
84
|
|
|
*/ |
|
85
|
|
|
public function findAllByAlbum(array $albumIds, string $userId, ?int $artistId=null, ?int $limit=null, ?int $offset=null) : array { |
|
86
|
|
|
$condition = '`album_id` IN ' . $this->questionMarks(\count($albumIds)); |
|
87
|
|
|
$params = \array_merge([$userId], $albumIds); |
|
88
|
|
|
|
|
89
|
|
|
if ($artistId !== null) { |
|
90
|
|
|
$condition .= ' AND `artist_id` = ? '; |
|
91
|
|
|
$params[] = $artistId; |
|
92
|
|
|
} |
|
93
|
|
|
|
|
94
|
|
|
$sql = $this->selectUserEntities($condition, |
|
95
|
|
|
'ORDER BY `*PREFIX*music_tracks`.`disk`, `number`, LOWER(`title`)'); |
|
96
|
|
|
return $this->findEntities($sql, $params, $limit, $offset); |
|
|
|
|
|
|
97
|
|
|
} |
|
98
|
|
|
|
|
99
|
|
|
/** |
|
100
|
|
|
* @return Track[] |
|
101
|
|
|
*/ |
|
102
|
|
|
public function findAllByFolder(int $folderId, string $userId, ?int $limit=null, ?int $offset=null) : array { |
|
103
|
|
|
$sql = $this->selectUserEntities('`file`.`parent` = ?', 'ORDER BY LOWER(`title`)'); |
|
104
|
|
|
$params = [$userId, $folderId]; |
|
105
|
|
|
return $this->findEntities($sql, $params, $limit, $offset); |
|
|
|
|
|
|
106
|
|
|
} |
|
107
|
|
|
|
|
108
|
|
|
/** |
|
109
|
|
|
* @return Track[] |
|
110
|
|
|
*/ |
|
111
|
|
|
public function findAllByGenre(int $genreId, string $userId, ?int $limit=null, ?int $offset=null) : array { |
|
112
|
|
|
$sql = $this->selectUserEntities('`genre_id` = ?', 'ORDER BY LOWER(`title`)'); |
|
113
|
|
|
$params = [$userId, $genreId]; |
|
114
|
|
|
return $this->findEntities($sql, $params, $limit, $offset); |
|
|
|
|
|
|
115
|
|
|
} |
|
116
|
|
|
|
|
117
|
|
|
/** |
|
118
|
|
|
* @param string $userId |
|
119
|
|
|
* @return int[] |
|
120
|
|
|
*/ |
|
121
|
|
|
public function findAllFileIds(string $userId) : array { |
|
122
|
|
|
$sql = 'SELECT `file_id` FROM `*PREFIX*music_tracks` WHERE `user_id` = ?'; |
|
123
|
|
|
$result = $this->execute($sql, [$userId]); |
|
|
|
|
|
|
124
|
|
|
|
|
125
|
|
|
return \array_map(function ($i) { |
|
126
|
|
|
return (int)$i['file_id']; |
|
127
|
|
|
}, $result->fetchAll()); |
|
128
|
|
|
} |
|
129
|
|
|
|
|
130
|
|
|
/** |
|
131
|
|
|
* Find a track of user matching a file ID |
|
132
|
|
|
* @throws \OCP\AppFramework\Db\DoesNotExistException if not found |
|
133
|
|
|
*/ |
|
134
|
|
|
public function findByFileId(int $fileId, string $userId) : Track { |
|
135
|
|
|
$sql = $this->selectUserEntities('`file_id` = ?'); |
|
136
|
|
|
$params = [$userId, $fileId]; |
|
137
|
|
|
return $this->findEntity($sql, $params); |
|
|
|
|
|
|
138
|
|
|
} |
|
139
|
|
|
|
|
140
|
|
|
/** |
|
141
|
|
|
* Find tracks of user with multiple file IDs |
|
142
|
|
|
* @param integer[] $fileIds |
|
143
|
|
|
* @param string[] $userIds |
|
144
|
|
|
* @return Track[] |
|
145
|
|
|
*/ |
|
146
|
|
|
public function findByFileIds(array $fileIds, array $userIds) : array { |
|
147
|
|
|
$sql = $this->selectEntities( |
|
148
|
|
|
'`*PREFIX*music_tracks`.`user_id` IN ' . $this->questionMarks(\count($userIds)) . |
|
149
|
|
|
' AND `file_id` IN '. $this->questionMarks(\count($fileIds))); |
|
150
|
|
|
$params = \array_merge($userIds, $fileIds); |
|
151
|
|
|
return $this->findEntities($sql, $params); |
|
|
|
|
|
|
152
|
|
|
} |
|
153
|
|
|
|
|
154
|
|
|
/** |
|
155
|
|
|
* Finds tracks of all users matching one or multiple file IDs |
|
156
|
|
|
* @param integer[] $fileIds |
|
157
|
|
|
* @return Track[] |
|
158
|
|
|
*/ |
|
159
|
|
|
public function findAllByFileIds(array $fileIds) : array { |
|
160
|
|
|
$sql = $this->selectEntities('`file_id` IN '. |
|
161
|
|
|
$this->questionMarks(\count($fileIds))); |
|
162
|
|
|
return $this->findEntities($sql, $fileIds); |
|
|
|
|
|
|
163
|
|
|
} |
|
164
|
|
|
|
|
165
|
|
|
public function countByArtist(int $artistId) : int { |
|
166
|
|
|
$sql = 'SELECT COUNT(*) AS `count` FROM `*PREFIX*music_tracks` WHERE `artist_id` = ?'; |
|
167
|
|
|
$result = $this->execute($sql, [$artistId]); |
|
|
|
|
|
|
168
|
|
|
$row = $result->fetch(); |
|
169
|
|
|
return (int)$row['count']; |
|
170
|
|
|
} |
|
171
|
|
|
|
|
172
|
|
|
public function countByAlbum(int $albumId) : int { |
|
173
|
|
|
$sql = 'SELECT COUNT(*) AS `count` FROM `*PREFIX*music_tracks` WHERE `album_id` = ?'; |
|
174
|
|
|
$result = $this->execute($sql, [$albumId]); |
|
|
|
|
|
|
175
|
|
|
$row = $result->fetch(); |
|
176
|
|
|
return (int)$row['count']; |
|
177
|
|
|
} |
|
178
|
|
|
|
|
179
|
|
|
/** |
|
180
|
|
|
* @return integer Duration in seconds |
|
181
|
|
|
*/ |
|
182
|
|
|
public function totalDurationOfAlbum(int $albumId) : int { |
|
183
|
|
|
$sql = 'SELECT SUM(`length`) AS `duration` FROM `*PREFIX*music_tracks` WHERE `album_id` = ?'; |
|
184
|
|
|
$result = $this->execute($sql, [$albumId]); |
|
|
|
|
|
|
185
|
|
|
$row = $result->fetch(); |
|
186
|
|
|
return (int)$row['duration']; |
|
187
|
|
|
} |
|
188
|
|
|
|
|
189
|
|
|
/** |
|
190
|
|
|
* @return integer Duration in seconds |
|
191
|
|
|
*/ |
|
192
|
|
|
public function totalDurationByArtist(int $artistId) : int { |
|
193
|
|
|
$sql = 'SELECT SUM(`length`) AS `duration` FROM `*PREFIX*music_tracks` WHERE `artist_id` = ?'; |
|
194
|
|
|
$result = $this->execute($sql, [$artistId]); |
|
|
|
|
|
|
195
|
|
|
$row = $result->fetch(); |
|
196
|
|
|
return (int)$row['duration']; |
|
197
|
|
|
} |
|
198
|
|
|
|
|
199
|
|
|
/** |
|
200
|
|
|
* Get durations of the given tracks. |
|
201
|
|
|
* @param integer[] $trackIds |
|
202
|
|
|
* @return array {int => int} where keys are track IDs and values are corresponding durations |
|
203
|
|
|
*/ |
|
204
|
|
|
public function getDurations(array $trackIds) : array { |
|
205
|
|
|
$result = []; |
|
206
|
|
|
|
|
207
|
|
|
if (!empty($trackIds)) { |
|
208
|
|
|
$sql = 'SELECT `id`, `length` FROM `*PREFIX*music_tracks` WHERE `id` IN ' . |
|
209
|
|
|
$this->questionMarks(\count($trackIds)); |
|
210
|
|
|
$rows = $this->execute($sql, $trackIds)->fetchAll(); |
|
|
|
|
|
|
211
|
|
|
foreach ($rows as $row) { |
|
212
|
|
|
$result[$row['id']] = (int)$row['length']; |
|
213
|
|
|
} |
|
214
|
|
|
} |
|
215
|
|
|
return $result; |
|
216
|
|
|
} |
|
217
|
|
|
|
|
218
|
|
|
/** |
|
219
|
|
|
* @return Track[] |
|
220
|
|
|
*/ |
|
221
|
|
|
public function findAllByNameRecursive(string $name, string $userId, ?int $limit=null, ?int $offset=null) { |
|
222
|
|
|
$condition = '( LOWER(`artist`.`name`) LIKE LOWER(?) OR |
|
223
|
|
|
LOWER(`album`.`name`) LIKE LOWER(?) OR |
|
224
|
|
|
LOWER(`title`) LIKE LOWER(?) )'; |
|
225
|
|
|
$sql = $this->selectUserEntities($condition, 'ORDER BY LOWER(`title`)'); |
|
226
|
|
|
$name = BaseMapper::prepareSubstringSearchPattern($name); |
|
227
|
|
|
$params = [$userId, $name, $name, $name]; |
|
228
|
|
|
return $this->findEntities($sql, $params, $limit, $offset); |
|
|
|
|
|
|
229
|
|
|
} |
|
230
|
|
|
|
|
231
|
|
|
/** |
|
232
|
|
|
* Returns all tracks specified by name and/or artist name |
|
233
|
|
|
* @param string|null $name the name of the track |
|
234
|
|
|
* @param string|null $artistName the name of the artist |
|
235
|
|
|
* @param string $userId the name of the user |
|
236
|
|
|
* @return Track[] Tracks matching the criteria |
|
237
|
|
|
*/ |
|
238
|
|
|
public function findAllByNameAndArtistName(?string $name, ?string $artistName, string $userId) : array { |
|
239
|
|
|
$sqlConditions = []; |
|
240
|
|
|
$params = [$userId]; |
|
241
|
|
|
|
|
242
|
|
|
if (!empty($name)) { |
|
243
|
|
|
$sqlConditions[] = '`title` = ?'; |
|
244
|
|
|
$params[] = $name; |
|
245
|
|
|
} |
|
246
|
|
|
|
|
247
|
|
|
if (!empty($artistName)) { |
|
248
|
|
|
$sqlConditions[] = '`artist`.`name` = ?'; |
|
249
|
|
|
$params[] = $artistName; |
|
250
|
|
|
} |
|
251
|
|
|
|
|
252
|
|
|
// at least one condition has to be given, otherwise return an empty set |
|
253
|
|
|
if (\count($sqlConditions) > 0) { |
|
254
|
|
|
$sql = $this->selectUserEntities(\implode(' AND ', $sqlConditions)); |
|
255
|
|
|
return $this->findEntities($sql, $params); |
|
|
|
|
|
|
256
|
|
|
} else { |
|
257
|
|
|
return []; |
|
258
|
|
|
} |
|
259
|
|
|
} |
|
260
|
|
|
|
|
261
|
|
|
/** |
|
262
|
|
|
* Returns all tracks specified by various criteria, all of which are optional |
|
263
|
|
|
* @param int[] $genres Array of genre IDs |
|
264
|
|
|
* @param int[] $artists Array of artist IDs |
|
265
|
|
|
* @param int|null $fromYear Earliest release year to include |
|
266
|
|
|
* @param int|null $toYear Latest release year to include |
|
267
|
|
|
* @param int $sortBy Sorting rule as defined in the class SortBy |
|
268
|
|
|
* @param string $userId the name of the user |
|
269
|
|
|
* @return Track[] Tracks matching the criteria |
|
270
|
|
|
*/ |
|
271
|
|
|
public function findAllByCriteria( |
|
272
|
|
|
array $genres, array $artists, ?int $fromYear, ?int $toYear, |
|
273
|
|
|
int $sortBy, bool $invertSort, string $userId, ?int $limit=null, ?int $offset=null) : array { |
|
274
|
|
|
|
|
275
|
|
|
$sqlConditions = []; |
|
276
|
|
|
$params = [$userId]; |
|
277
|
|
|
|
|
278
|
|
|
if (!empty($genres)) { |
|
279
|
|
|
$sqlConditions[] = '`genre_id` IN ' . $this->questionMarks(\count($genres)); |
|
280
|
|
|
$params = \array_merge($params, $genres); |
|
281
|
|
|
} |
|
282
|
|
|
|
|
283
|
|
|
if (!empty($artists)) { |
|
284
|
|
|
$sqlConditions[] = '`artist_id` IN ' . $this->questionMarks(\count($artists)); |
|
285
|
|
|
$params = \array_merge($params, $artists); |
|
286
|
|
|
} |
|
287
|
|
|
|
|
288
|
|
|
if (!empty($fromYear)) { |
|
289
|
|
|
$sqlConditions[] = '`year` >= ?'; |
|
290
|
|
|
$params[] = $fromYear; |
|
291
|
|
|
} |
|
292
|
|
|
|
|
293
|
|
|
if (!empty($toYear)) { |
|
294
|
|
|
$sqlConditions[] = '`year` <= ?'; |
|
295
|
|
|
$params[] = $toYear; |
|
296
|
|
|
} |
|
297
|
|
|
|
|
298
|
|
|
$sql = $this->selectUserEntities(\implode(' AND ', $sqlConditions), $this->formatSortingClause($sortBy, $invertSort)); |
|
299
|
|
|
return $this->findEntities($sql, $params, $limit, $offset); |
|
|
|
|
|
|
300
|
|
|
} |
|
301
|
|
|
|
|
302
|
|
|
/** |
|
303
|
|
|
* Find most frequently played tracks |
|
304
|
|
|
* @return Track[] |
|
305
|
|
|
*/ |
|
306
|
|
|
public function findFrequentPlay(string $userId, ?int $limit=null, ?int $offset=null) : array { |
|
307
|
|
|
$sql = $this->selectUserEntities('`play_count` > 0', 'ORDER BY `play_count` DESC, LOWER(`title`)'); |
|
308
|
|
|
return $this->findEntities($sql, [$userId], $limit, $offset); |
|
|
|
|
|
|
309
|
|
|
} |
|
310
|
|
|
|
|
311
|
|
|
/** |
|
312
|
|
|
* Find most recently played tracks |
|
313
|
|
|
* @return Track[] |
|
314
|
|
|
*/ |
|
315
|
|
|
public function findRecentPlay(string $userId, ?int $limit=null, ?int $offset=null) : array { |
|
316
|
|
|
$sql = $this->selectUserEntities('`last_played` IS NOT NULL', 'ORDER BY `last_played` DESC'); |
|
317
|
|
|
return $this->findEntities($sql, [$userId], $limit, $offset); |
|
|
|
|
|
|
318
|
|
|
} |
|
319
|
|
|
|
|
320
|
|
|
/** |
|
321
|
|
|
* Find least recently played tracks |
|
322
|
|
|
* @return Track[] |
|
323
|
|
|
*/ |
|
324
|
|
|
public function findNotRecentPlay(string $userId, ?int $limit=null, ?int $offset=null) : array { |
|
325
|
|
|
$sql = $this->selectUserEntities(null, 'ORDER BY `last_played` ASC'); |
|
326
|
|
|
return $this->findEntities($sql, [$userId], $limit, $offset); |
|
|
|
|
|
|
327
|
|
|
} |
|
328
|
|
|
|
|
329
|
|
|
/** |
|
330
|
|
|
* Finds all track IDs of the user along with the parent folder ID of each track |
|
331
|
|
|
* @return array where keys are folder IDs and values are arrays of track IDs |
|
332
|
|
|
*/ |
|
333
|
|
|
public function findTrackAndFolderIds(string $userId) : array { |
|
334
|
|
|
$sql = 'SELECT `track`.`id` AS id, `file`.`name` AS `filename`, `file`.`parent` AS parent |
|
335
|
|
|
FROM `*PREFIX*music_tracks` `track` |
|
336
|
|
|
JOIN `*PREFIX*filecache` `file` |
|
337
|
|
|
ON `track`.`file_id` = `file`.`fileid` |
|
338
|
|
|
WHERE `track`.`user_id` = ?'; |
|
339
|
|
|
|
|
340
|
|
|
$rows = $this->execute($sql, [$userId])->fetchAll(); |
|
|
|
|
|
|
341
|
|
|
|
|
342
|
|
|
// Sort the results according the file names. This can't be made using ORDERBY in the |
|
343
|
|
|
// SQL query because then we couldn't use the "natural order" comparison algorithm |
|
344
|
|
|
\usort($rows, function ($a, $b) { |
|
345
|
|
|
return \strnatcasecmp($a['filename'], $b['filename']); |
|
346
|
|
|
}); |
|
347
|
|
|
|
|
348
|
|
|
// group the files to parent folder "buckets" |
|
349
|
|
|
$result = []; |
|
350
|
|
|
foreach ($rows as $row) { |
|
351
|
|
|
$result[(int)$row['parent']][] = (int)$row['id']; |
|
352
|
|
|
} |
|
353
|
|
|
|
|
354
|
|
|
return $result; |
|
355
|
|
|
} |
|
356
|
|
|
|
|
357
|
|
|
/** |
|
358
|
|
|
* Find names and parents of the file system nodes with given IDs within the given storage |
|
359
|
|
|
* @param int[] $nodeIds |
|
360
|
|
|
* @param string $storageId |
|
361
|
|
|
* @return array where keys are the node IDs and values are associative arrays |
|
362
|
|
|
* like { 'name' => string, 'parent' => int }; |
|
363
|
|
|
*/ |
|
364
|
|
|
public function findNodeNamesAndParents(array $nodeIds, string $storageId) : array { |
|
365
|
|
|
$result = []; |
|
366
|
|
|
|
|
367
|
|
|
if (!empty($nodeIds)) { |
|
368
|
|
|
$sql = 'SELECT `fileid`, `name`, `parent` '. |
|
369
|
|
|
'FROM `*PREFIX*filecache` `filecache` '. |
|
370
|
|
|
'JOIN `*PREFIX*storages` `storages` '. |
|
371
|
|
|
'ON `filecache`.`storage` = `storages`.`numeric_id` '. |
|
372
|
|
|
'WHERE `storages`.`id` = ? '. |
|
373
|
|
|
'AND `filecache`.`fileid` IN '. $this->questionMarks(\count($nodeIds)); |
|
374
|
|
|
|
|
375
|
|
|
$rows = $this->execute($sql, \array_merge([$storageId], $nodeIds))->fetchAll(); |
|
|
|
|
|
|
376
|
|
|
|
|
377
|
|
|
foreach ($rows as $row) { |
|
378
|
|
|
$result[$row['fileid']] = [ |
|
379
|
|
|
'name' => $row['name'], |
|
380
|
|
|
'parent' => (int)$row['parent'] |
|
381
|
|
|
]; |
|
382
|
|
|
} |
|
383
|
|
|
} |
|
384
|
|
|
|
|
385
|
|
|
return $result; |
|
386
|
|
|
} |
|
387
|
|
|
|
|
388
|
|
|
/** |
|
389
|
|
|
* Returns all genre IDs associated with the given artist |
|
390
|
|
|
* @return int[] |
|
391
|
|
|
*/ |
|
392
|
|
|
public function getGenresByArtistId(int $artistId, string $userId) : array { |
|
393
|
|
|
$sql = 'SELECT DISTINCT(`genre_id`) FROM `*PREFIX*music_tracks` WHERE |
|
394
|
|
|
`genre_id` IS NOT NULL AND `user_id` = ? AND `artist_id` = ?'; |
|
395
|
|
|
$rows = $this->execute($sql, [$userId, $artistId]); |
|
|
|
|
|
|
396
|
|
|
return $rows->fetchAll(\PDO::FETCH_COLUMN); |
|
397
|
|
|
} |
|
398
|
|
|
|
|
399
|
|
|
/** |
|
400
|
|
|
* Returns all tracks IDs of the user, organized by the genre_id. |
|
401
|
|
|
* @return array where keys are genre IDs and values are arrays of track IDs |
|
402
|
|
|
*/ |
|
403
|
|
|
public function mapGenreIdsToTrackIds(string $userId) : array { |
|
404
|
|
|
$sql = 'SELECT `id`, `genre_id` FROM `*PREFIX*music_tracks` |
|
405
|
|
|
WHERE `genre_id` IS NOT NULL and `user_id` = ?'; |
|
406
|
|
|
$rows = $this->execute($sql, [$userId])->fetchAll(); |
|
|
|
|
|
|
407
|
|
|
|
|
408
|
|
|
$result = []; |
|
409
|
|
|
foreach ($rows as $row) { |
|
410
|
|
|
$result[(int)$row['genre_id']][] = (int)$row['id']; |
|
411
|
|
|
} |
|
412
|
|
|
|
|
413
|
|
|
return $result; |
|
414
|
|
|
} |
|
415
|
|
|
|
|
416
|
|
|
/** |
|
417
|
|
|
* Returns file IDs of the tracks which do not have genre scanned. This is not the same |
|
418
|
|
|
* thing as unknown genre, which means that the genre has been scanned but was not found |
|
419
|
|
|
* from the track metadata. |
|
420
|
|
|
* @return int[] |
|
421
|
|
|
*/ |
|
422
|
|
|
public function findFilesWithoutScannedGenre(string $userId) : array { |
|
423
|
|
|
$sql = 'SELECT `track`.`file_id` FROM `*PREFIX*music_tracks` `track` |
|
424
|
|
|
INNER JOIN `*PREFIX*filecache` `file` |
|
425
|
|
|
ON `track`.`file_id` = `file`.`fileid` |
|
426
|
|
|
WHERE `genre_id` IS NULL and `user_id` = ?'; |
|
427
|
|
|
$rows = $this->execute($sql, [$userId]); |
|
|
|
|
|
|
428
|
|
|
return $rows->fetchAll(\PDO::FETCH_COLUMN); |
|
429
|
|
|
} |
|
430
|
|
|
|
|
431
|
|
|
/** |
|
432
|
|
|
* Update "last played" timestamp and increment the total play count of the track. |
|
433
|
|
|
* The DB row is updated *without* updating the `updated` column. |
|
434
|
|
|
* @return bool true if the track was found and updated, false otherwise |
|
435
|
|
|
*/ |
|
436
|
|
|
public function recordTrackPlayed(int $trackId, string $userId, \DateTime $timeOfPlay) : bool { |
|
437
|
|
|
$sql = 'UPDATE `*PREFIX*music_tracks` |
|
438
|
|
|
SET `last_played` = ?, `play_count` = `play_count` + 1 |
|
439
|
|
|
WHERE `user_id` = ? AND `id` = ?'; |
|
440
|
|
|
$params = [$timeOfPlay->format(BaseMapper::SQL_DATE_FORMAT), $userId, $trackId]; |
|
441
|
|
|
$result = $this->execute($sql, $params); |
|
|
|
|
|
|
442
|
|
|
return ($result->rowCount() > 0); |
|
443
|
|
|
} |
|
444
|
|
|
|
|
445
|
|
|
/** |
|
446
|
|
|
* Overridden from the base implementation to provide support for table-specific rules |
|
447
|
|
|
* |
|
448
|
|
|
* {@inheritdoc} |
|
449
|
|
|
* @see BaseMapper::advFormatSqlCondition() |
|
450
|
|
|
*/ |
|
451
|
|
|
protected function advFormatSqlCondition(string $rule, string $sqlOp) : string { |
|
452
|
|
|
// The extra subquery "mysqlhack" seen around some nested queries is needed in order for these to not be insanely slow on MySQL. |
|
453
|
|
|
switch ($rule) { |
|
454
|
|
|
case 'anywhere': return self::formatAdvSearchAnywhereCond($sqlOp); |
|
455
|
|
|
case 'album': return "`album_id` IN (SELECT `id` from `*PREFIX*music_albums` `al` WHERE LOWER(`al`.`name`) $sqlOp LOWER(?))"; |
|
456
|
|
|
case 'artist': return "LOWER(`artist`.`name`) $sqlOp LOWER(?)"; |
|
457
|
|
|
case 'album_artist': return "`album_id` IN (SELECT `al`.`id` from `*PREFIX*music_albums` `al` JOIN `*PREFIX*music_artists` `ar` ON `al`.`album_artist_id` = `ar`.`id` WHERE LOWER(`ar`.`name`) $sqlOp LOWER(?))"; |
|
458
|
|
|
case 'track': return "`number` $sqlOp ?"; |
|
459
|
|
|
case 'year': return "`year` $sqlOp ?"; |
|
460
|
|
|
case 'albumrating': return "`album`.`rating` $sqlOp ?"; |
|
461
|
|
|
case 'artistrating': return "`artist`.`rating` $sqlOp ?"; |
|
462
|
|
|
case 'favorite_album': return "`album_id` IN (SELECT `id` from `*PREFIX*music_albums` `al` WHERE LOWER(`al`.`name`) $sqlOp LOWER(?) AND `al`.`starred` IS NOT NULL)"; |
|
463
|
|
|
case 'favorite_artist': return "`artist_id` IN (SELECT `id` from `*PREFIX*music_artists` `ar` WHERE LOWER(`ar`.`name`) $sqlOp LOWER(?) AND `ar`.`starred` IS NOT NULL)"; |
|
464
|
|
|
case 'played_times': return "`play_count` $sqlOp ?"; |
|
465
|
|
|
case 'last_play': return "`last_played` $sqlOp ?"; |
|
466
|
|
|
case 'played': // fall through, we give no access to other people's data |
|
467
|
|
|
case 'myplayed': return "`last_played` $sqlOp"; // operator "IS NULL" or "IS NOT NULL" |
|
468
|
|
|
case 'myplayedalbum': return "`album_id` IN (SELECT * FROM (SELECT `album_id` from `*PREFIX*music_tracks` GROUP BY `album_id` HAVING MAX(`last_played`) $sqlOp) mysqlhack)"; // operator "IS NULL" or "IS NOT NULL" |
|
469
|
|
|
case 'myplayedartist': return "`artist_id` IN (SELECT * FROM (SELECT `artist_id` from `*PREFIX*music_tracks` GROUP BY `artist_id` HAVING MAX(`last_played`) $sqlOp) mysqlhack)"; // operator "IS NULL" or "IS NOT NULL" |
|
470
|
|
|
case 'time': return "`length` $sqlOp ?"; |
|
471
|
|
|
case 'genre': // fall through |
|
472
|
|
|
case 'song_genre': return "LOWER(`genre`.`name`) $sqlOp LOWER(?)"; |
|
473
|
|
|
case 'album_genre': return "`album_id` IN (SELECT * FROM (SELECT `album_id` FROM `*PREFIX*music_tracks` `t` JOIN `*PREFIX*music_genres` `g` ON `t`.`genre_id` = `g`.`id` GROUP BY `album_id` HAVING LOWER(GROUP_CONCAT(`g`.`name`)) $sqlOp LOWER(?)) mysqlhack)"; // GROUP_CONCAT not available on PostgreSQL |
|
474
|
|
|
case 'artist_genre': return "`artist_id` IN (SELECT * FROM (SELECT `artist_id` FROM `*PREFIX*music_tracks` `t` JOIN `*PREFIX*music_genres` `g` ON `t`.`genre_id` = `g`.`id` GROUP BY `artist_id` HAVING LOWER(GROUP_CONCAT(`g`.`name`)) $sqlOp LOWER(?)) mysqlhack)"; // GROUP_CONCAT not available on PostgreSQL |
|
475
|
|
|
case 'no_genre': return ($sqlOp == 'IS NOT NULL') ? '`genre`.`name` = ""' : '`genre`.`name` != ""'; |
|
476
|
|
|
case 'playlist': return "$sqlOp EXISTS (SELECT 1 from `*PREFIX*music_playlists` `p` WHERE `p`.`id` = ? AND `p`.`track_ids` LIKE CONCAT('%|',`*PREFIX*music_tracks`.`id`, '|%'))"; |
|
477
|
|
|
case 'playlist_name': return "EXISTS (SELECT 1 from `*PREFIX*music_playlists` `p` WHERE `p`.`name` $sqlOp ? AND `p`.`track_ids` LIKE CONCAT('%|',`*PREFIX*music_tracks`.`id`, '|%'))"; |
|
478
|
|
|
case 'recent_played': return "`*PREFIX*music_tracks`.`id` IN (SELECT * FROM (SELECT `id` FROM `*PREFIX*music_tracks` WHERE `user_id` = ? ORDER BY `last_played` DESC LIMIT $sqlOp) mysqlhack)"; |
|
479
|
|
|
case 'file': return "LOWER(`file`.`name`) $sqlOp LOWER(?)"; |
|
480
|
|
|
case 'mbid_song': return parent::advFormatSqlCondition('mbid', $sqlOp); // alias |
|
481
|
|
|
case 'mbid_album': return "`album_id` IN (SELECT `id` from `*PREFIX*music_albums` `al` WHERE `al`.`mbid` $sqlOp ?)"; |
|
482
|
|
|
case 'mbid_artist': return "`artist`.`mbid` $sqlOp ?"; |
|
483
|
|
|
default: return parent::advFormatSqlCondition($rule, $sqlOp); |
|
484
|
|
|
} |
|
485
|
|
|
} |
|
486
|
|
|
|
|
487
|
|
|
private static function formatAdvSearchAnywhereCond(string $sqlOp) : string { |
|
488
|
|
|
$fields = [ |
|
489
|
|
|
"`*PREFIX*music_tracks`.`title`", |
|
490
|
|
|
"`file`.`name`", |
|
491
|
|
|
"`artist`.`name`", |
|
492
|
|
|
"`album`.`name`", |
|
493
|
|
|
"`genre`.`name`" |
|
494
|
|
|
]; |
|
495
|
|
|
$parts = \array_map(function(string $field) use ($sqlOp) { |
|
496
|
|
|
return "LOWER($field) $sqlOp LOWER(?)"; |
|
497
|
|
|
}, $fields); |
|
498
|
|
|
|
|
499
|
|
|
$negativeOp = \in_array($sqlOp, ['NOT LIKE', '!=', 'NOT SOUNDS LIKE', 'NOT REGEXP']); |
|
500
|
|
|
$cond = \implode($negativeOp ? ' AND ' : ' OR ', $parts); |
|
501
|
|
|
|
|
502
|
|
|
return "($cond)"; |
|
503
|
|
|
} |
|
504
|
|
|
|
|
505
|
|
|
/** |
|
506
|
|
|
* {@inheritdoc} |
|
507
|
|
|
* @see \OCA\Music\Db\BaseMapper::findUniqueEntity() |
|
508
|
|
|
* @param Track $track |
|
509
|
|
|
* @return Track |
|
510
|
|
|
*/ |
|
511
|
|
|
protected function findUniqueEntity(Entity $track) : Entity { |
|
512
|
|
|
return $this->findByFileId($track->getFileId(), $track->getUserId()); |
|
|
|
|
|
|
513
|
|
|
} |
|
514
|
|
|
} |
|
515
|
|
|
|
owncloud /
music
Loading history...
This function has been deprecated. The supplier of the function has supplied an explanatory message.
The explanatory message should give you some clue as to whether and when the function will be removed and what other function to use instead.