|
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 - 2025 |
|
13
|
|
|
*/ |
|
14
|
|
|
|
|
15
|
|
|
namespace OCA\Music\Db; |
|
16
|
|
|
|
|
17
|
|
|
use OCP\IConfig; |
|
18
|
|
|
use OCP\IDBConnection; |
|
19
|
|
|
|
|
20
|
|
|
/** |
|
21
|
|
|
* @method Track findEntity(string $sql, array $params) |
|
22
|
|
|
* @method Track[] findEntities(string $sql, array $params, ?int $limit=null, ?int $offset=null) |
|
23
|
|
|
* @phpstan-extends BaseMapper<Track> |
|
24
|
|
|
*/ |
|
25
|
|
|
class TrackMapper extends BaseMapper { |
|
26
|
|
|
public function __construct(IDBConnection $db, IConfig $config) { |
|
27
|
|
|
parent::__construct($db, $config, 'music_tracks', Track::class, 'title', ['file_id', 'user_id'], 'album_id'); |
|
28
|
|
|
} |
|
29
|
|
|
|
|
30
|
|
|
/** |
|
31
|
|
|
* Override the base implementation to include data from multiple tables |
|
32
|
|
|
* |
|
33
|
|
|
* {@inheritdoc} |
|
34
|
|
|
* @see BaseMapper::selectEntities() |
|
35
|
|
|
*/ |
|
36
|
|
|
protected function selectEntities(string $condition, ?string $extension=null) : string { |
|
37
|
|
|
return "SELECT `*PREFIX*music_tracks`.*, `file`.`name` AS `filename`, `file`.`size`, `file`.`mtime` AS `file_mod_time`, `file`.`parent` AS `folder_id`, |
|
38
|
|
|
`album`.`name` AS `album_name`, `artist`.`name` AS `artist_name`, `genre`.`name` AS `genre_name` |
|
39
|
|
|
FROM `*PREFIX*music_tracks` |
|
40
|
|
|
INNER JOIN `*PREFIX*filecache` `file` |
|
41
|
|
|
ON `*PREFIX*music_tracks`.`file_id` = `file`.`fileid` |
|
42
|
|
|
INNER JOIN `*PREFIX*music_albums` `album` |
|
43
|
|
|
ON `*PREFIX*music_tracks`.`album_id` = `album`.`id` |
|
44
|
|
|
INNER JOIN `*PREFIX*music_artists` `artist` |
|
45
|
|
|
ON `*PREFIX*music_tracks`.`artist_id` = `artist`.`id` |
|
46
|
|
|
LEFT JOIN `*PREFIX*music_genres` `genre` |
|
47
|
|
|
ON `*PREFIX*music_tracks`.`genre_id` = `genre`.`id` |
|
48
|
|
|
WHERE $condition $extension"; |
|
49
|
|
|
} |
|
50
|
|
|
|
|
51
|
|
|
/** |
|
52
|
|
|
* Overridden from the base implementation to add support for sorting by artist, play_count, and last_played. |
|
53
|
|
|
* |
|
54
|
|
|
* {@inheritdoc} |
|
55
|
|
|
* @see BaseMapper::formatSortingClause() |
|
56
|
|
|
*/ |
|
57
|
|
|
protected function formatSortingClause(int $sortBy, bool $invertSort = false) : ?string { |
|
58
|
|
|
switch ($sortBy) { |
|
59
|
|
|
case SortBy::Parent: |
|
60
|
|
|
$dir = $invertSort ? 'DESC' : 'ASC'; |
|
61
|
|
|
// Note: the alternative form "LOWER(`artist_name`) wouldn't work on PostgreSQL, see https://github.com/owncloud/music/issues/1046 for a similar case |
|
62
|
|
|
return "ORDER BY LOWER(`artist`.`name`) $dir, LOWER(`title`) $dir"; |
|
63
|
|
|
case SortBy::PlayCount: |
|
64
|
|
|
$dir = $invertSort ? 'ASC' : 'DESC'; |
|
65
|
|
|
return "ORDER BY `play_count` $dir"; |
|
66
|
|
|
case SortBy::LastPlayed: |
|
67
|
|
|
$dir = $invertSort ? 'ASC' : 'DESC'; |
|
68
|
|
|
return "ORDER BY `last_played` $dir"; |
|
69
|
|
|
default: |
|
70
|
|
|
return parent::formatSortingClause($sortBy, $invertSort); |
|
71
|
|
|
} |
|
72
|
|
|
} |
|
73
|
|
|
|
|
74
|
|
|
/** |
|
75
|
|
|
* Returns all tracks of the given artist (both album and track artists are considered) |
|
76
|
|
|
* @param int[] $artistIds |
|
77
|
|
|
* @return Track[] |
|
78
|
|
|
*/ |
|
79
|
|
|
public function findAllByArtist(array $artistIds, string $userId, ?int $limit=null, ?int $offset=null) : array { |
|
80
|
|
|
$questionMarks = $this->questionMarks(\count($artistIds)); |
|
81
|
|
|
$sql = $this->selectUserEntities( |
|
82
|
|
|
"`artist_id` IN $questionMarks OR `album_id` IN (SELECT `id` from `*PREFIX*music_albums` WHERE `album_artist_id` IN $questionMarks)", |
|
83
|
|
|
'ORDER BY LOWER(`title`)'); |
|
84
|
|
|
$params = \array_merge([$userId], $artistIds, $artistIds); |
|
85
|
|
|
return $this->findEntities($sql, $params, $limit, $offset); |
|
86
|
|
|
} |
|
87
|
|
|
|
|
88
|
|
|
/** |
|
89
|
|
|
* @param int[] $albumIds |
|
90
|
|
|
* @return Track[] |
|
91
|
|
|
*/ |
|
92
|
|
|
public function findAllByAlbum(array $albumIds, string $userId, ?int $artistId=null, ?int $limit=null, ?int $offset=null) : array { |
|
93
|
|
|
$condition = '`album_id` IN ' . $this->questionMarks(\count($albumIds)); |
|
94
|
|
|
$params = \array_merge([$userId], $albumIds); |
|
95
|
|
|
|
|
96
|
|
|
if ($artistId !== null) { |
|
97
|
|
|
$condition .= ' AND `artist_id` = ? '; |
|
98
|
|
|
$params[] = $artistId; |
|
99
|
|
|
} |
|
100
|
|
|
|
|
101
|
|
|
$sql = $this->selectUserEntities($condition, |
|
102
|
|
|
'ORDER BY `*PREFIX*music_tracks`.`disk`, `number`, LOWER(`file`.`name`)'); |
|
103
|
|
|
return $this->findEntities($sql, $params, $limit, $offset); |
|
104
|
|
|
} |
|
105
|
|
|
|
|
106
|
|
|
/** |
|
107
|
|
|
* @return Track[] |
|
108
|
|
|
*/ |
|
109
|
|
|
public function findAllByFolder(int $folderId, string $userId, ?int $limit=null, ?int $offset=null) : array { |
|
110
|
|
|
$sql = $this->selectUserEntities('`file`.`parent` = ?', 'ORDER BY LOWER(`file`.`name`)'); |
|
111
|
|
|
$params = [$userId, $folderId]; |
|
112
|
|
|
return $this->findEntities($sql, $params, $limit, $offset); |
|
113
|
|
|
} |
|
114
|
|
|
|
|
115
|
|
|
/** |
|
116
|
|
|
* @return Track[] |
|
117
|
|
|
*/ |
|
118
|
|
|
public function findAllByGenre(int $genreId, string $userId, ?int $limit=null, ?int $offset=null) : array { |
|
119
|
|
|
$sql = $this->selectUserEntities('`genre_id` = ?', 'ORDER BY LOWER(`title`)'); |
|
120
|
|
|
$params = [$userId, $genreId]; |
|
121
|
|
|
return $this->findEntities($sql, $params, $limit, $offset); |
|
122
|
|
|
} |
|
123
|
|
|
|
|
124
|
|
|
/** |
|
125
|
|
|
* Find file IDs of scanned files. Optionally, limit to files which are direct children of the given folders. |
|
126
|
|
|
* @param ?int[] $parentIds |
|
127
|
|
|
* @return int[] |
|
128
|
|
|
*/ |
|
129
|
|
|
public function findAllFileIds(string $userId, ?array $parentIds=null) : array { |
|
130
|
|
|
if (empty($parentIds)) { |
|
131
|
|
|
$sql = 'SELECT `file_id` FROM `*PREFIX*music_tracks` WHERE `user_id` = ?'; |
|
132
|
|
|
$params = [$userId]; |
|
133
|
|
|
} else { |
|
134
|
|
|
$sql = 'SELECT `track`.`file_id` |
|
135
|
|
|
FROM `*PREFIX*music_tracks` `track` |
|
136
|
|
|
INNER JOIN `*PREFIX*filecache` `file` |
|
137
|
|
|
ON `track`.`file_id` = `file`.`fileid` |
|
138
|
|
|
WHERE `track`.`user_id` = ? |
|
139
|
|
|
AND `file`.`parent` IN ' . $this->questionMarks(\count($parentIds)); |
|
140
|
|
|
$params = \array_merge([$userId], $parentIds); |
|
141
|
|
|
} |
|
142
|
|
|
$result = $this->execute($sql, $params); |
|
143
|
|
|
$rows = $result->fetchAll(\PDO::FETCH_COLUMN); |
|
144
|
|
|
$result->closeCursor(); |
|
145
|
|
|
|
|
146
|
|
|
return $rows; |
|
147
|
|
|
} |
|
148
|
|
|
|
|
149
|
|
|
/** |
|
150
|
|
|
* Find file IDs of dirty files. Optionally, limit to files which are direct children of the given folders. |
|
151
|
|
|
* @param ?int[] $parentIds |
|
152
|
|
|
* @return int[] |
|
153
|
|
|
*/ |
|
154
|
|
|
public function findDirtyFileIds(string $userId, ?array $parentIds=null) : array { |
|
155
|
|
|
$updatedEpoch = $this->sqlDateToEpoch('`track`.`updated`'); |
|
156
|
|
|
$sql = "SELECT `track`.`file_id` |
|
157
|
|
|
FROM `*PREFIX*music_tracks` `track` |
|
158
|
|
|
INNER JOIN `*PREFIX*filecache` `file` |
|
159
|
|
|
ON `track`.`file_id` = `file`.`fileid` |
|
160
|
|
|
WHERE `track`.`user_id` = ? |
|
161
|
|
|
AND (`track`.`dirty` = '1' OR $updatedEpoch < `file`.`mtime`)"; |
|
162
|
|
|
$params = [$userId]; |
|
163
|
|
|
|
|
164
|
|
|
if (!empty($parentIds)) { |
|
165
|
|
|
$sql .= ' AND `file`.`parent` IN ' . $this->questionMarks(\count($parentIds)); |
|
166
|
|
|
$params = \array_merge($params, $parentIds); |
|
167
|
|
|
} |
|
168
|
|
|
|
|
169
|
|
|
$result = $this->execute($sql, $params); |
|
170
|
|
|
$rows = $result->fetchAll(\PDO::FETCH_COLUMN); |
|
171
|
|
|
$result->closeCursor(); |
|
172
|
|
|
|
|
173
|
|
|
return $rows; |
|
174
|
|
|
} |
|
175
|
|
|
|
|
176
|
|
|
/** |
|
177
|
|
|
* Find a track of user matching a file ID |
|
178
|
|
|
* @throws \OCP\AppFramework\Db\DoesNotExistException if not found |
|
179
|
|
|
*/ |
|
180
|
|
|
public function findByFileId(int $fileId, string $userId) : Track { |
|
181
|
|
|
$sql = $this->selectUserEntities('`file_id` = ?'); |
|
182
|
|
|
$params = [$userId, $fileId]; |
|
183
|
|
|
return $this->findEntity($sql, $params); |
|
184
|
|
|
} |
|
185
|
|
|
|
|
186
|
|
|
/** |
|
187
|
|
|
* Find tracks of user with multiple file IDs |
|
188
|
|
|
* @param integer[] $fileIds |
|
189
|
|
|
* @param string[] $userIds |
|
190
|
|
|
* @return Track[] |
|
191
|
|
|
*/ |
|
192
|
|
|
public function findByFileIds(array $fileIds, array $userIds) : array { |
|
193
|
|
|
$sql = $this->selectEntities( |
|
194
|
|
|
'`*PREFIX*music_tracks`.`user_id` IN ' . $this->questionMarks(\count($userIds)) . |
|
195
|
|
|
' AND `file_id` IN '. $this->questionMarks(\count($fileIds))); |
|
196
|
|
|
$params = \array_merge($userIds, $fileIds); |
|
197
|
|
|
return $this->findEntities($sql, $params); |
|
198
|
|
|
} |
|
199
|
|
|
|
|
200
|
|
|
/** |
|
201
|
|
|
* Finds tracks of all users matching one or multiple file IDs |
|
202
|
|
|
* @param integer[] $fileIds |
|
203
|
|
|
* @return Track[] |
|
204
|
|
|
*/ |
|
205
|
|
|
public function findAllByFileIds(array $fileIds) : array { |
|
206
|
|
|
$sql = $this->selectEntities('`file_id` IN '. |
|
207
|
|
|
$this->questionMarks(\count($fileIds))); |
|
208
|
|
|
return $this->findEntities($sql, $fileIds); |
|
209
|
|
|
} |
|
210
|
|
|
|
|
211
|
|
|
public function countByArtist(int $artistId) : int { |
|
212
|
|
|
$sql = 'SELECT COUNT(*) AS `count` FROM `*PREFIX*music_tracks` WHERE `artist_id` = ?'; |
|
213
|
|
|
$result = $this->execute($sql, [$artistId]); |
|
214
|
|
|
$row = $result->fetch(); |
|
215
|
|
|
$result->closeCursor(); |
|
216
|
|
|
return (int)$row['count']; |
|
217
|
|
|
} |
|
218
|
|
|
|
|
219
|
|
|
public function countByAlbum(int $albumId) : int { |
|
220
|
|
|
$sql = 'SELECT COUNT(*) AS `count` FROM `*PREFIX*music_tracks` WHERE `album_id` = ?'; |
|
221
|
|
|
$result = $this->execute($sql, [$albumId]); |
|
222
|
|
|
$row = $result->fetch(); |
|
223
|
|
|
$result->closeCursor(); |
|
224
|
|
|
return (int)$row['count']; |
|
225
|
|
|
} |
|
226
|
|
|
|
|
227
|
|
|
/** |
|
228
|
|
|
* @return integer Duration in seconds |
|
229
|
|
|
*/ |
|
230
|
|
|
public function totalDurationOfAlbum(int $albumId) : int { |
|
231
|
|
|
$sql = 'SELECT SUM(`length`) AS `duration` FROM `*PREFIX*music_tracks` WHERE `album_id` = ?'; |
|
232
|
|
|
$result = $this->execute($sql, [$albumId]); |
|
233
|
|
|
$row = $result->fetch(); |
|
234
|
|
|
$result->closeCursor(); |
|
235
|
|
|
return (int)$row['duration']; |
|
236
|
|
|
} |
|
237
|
|
|
|
|
238
|
|
|
/** |
|
239
|
|
|
* @return integer Duration in seconds |
|
240
|
|
|
*/ |
|
241
|
|
|
public function totalDurationByArtist(int $artistId) : int { |
|
242
|
|
|
$sql = 'SELECT SUM(`length`) AS `duration` FROM `*PREFIX*music_tracks` WHERE `artist_id` = ?'; |
|
243
|
|
|
$result = $this->execute($sql, [$artistId]); |
|
244
|
|
|
$row = $result->fetch(); |
|
245
|
|
|
$result->closeCursor(); |
|
246
|
|
|
return (int)$row['duration']; |
|
247
|
|
|
} |
|
248
|
|
|
|
|
249
|
|
|
/** |
|
250
|
|
|
* Get durations of the given tracks. |
|
251
|
|
|
* @param integer[] $trackIds |
|
252
|
|
|
* @return array {int => int} where keys are track IDs and values are corresponding durations |
|
253
|
|
|
*/ |
|
254
|
|
|
public function getDurations(array $trackIds) : array { |
|
255
|
|
|
$return = []; |
|
256
|
|
|
|
|
257
|
|
|
if (!empty($trackIds)) { |
|
258
|
|
|
$sql = 'SELECT `id`, `length` FROM `*PREFIX*music_tracks` WHERE `id` IN ' . |
|
259
|
|
|
$this->questionMarks(\count($trackIds)); |
|
260
|
|
|
$result = $this->execute($sql, $trackIds); |
|
261
|
|
|
$rows = $result->fetchAll(); |
|
262
|
|
|
$result->closeCursor(); |
|
263
|
|
|
|
|
264
|
|
|
foreach ($rows as $row) { |
|
265
|
|
|
$return[$row['id']] = (int)$row['length']; |
|
266
|
|
|
} |
|
267
|
|
|
} |
|
268
|
|
|
return $return; |
|
269
|
|
|
} |
|
270
|
|
|
|
|
271
|
|
|
/** |
|
272
|
|
|
* @return Track[] |
|
273
|
|
|
*/ |
|
274
|
|
|
public function findAllByNameRecursive(string $name, string $userId, ?int $limit=null, ?int $offset=null) { |
|
275
|
|
|
$condition = '( LOWER(`artist`.`name`) LIKE LOWER(?) OR |
|
276
|
|
|
LOWER(`album`.`name`) LIKE LOWER(?) OR |
|
277
|
|
|
LOWER(`title`) LIKE LOWER(?) )'; |
|
278
|
|
|
$sql = $this->selectUserEntities($condition, 'ORDER BY LOWER(`title`)'); |
|
279
|
|
|
$name = BaseMapper::prepareSubstringSearchPattern($name); |
|
280
|
|
|
$params = [$userId, $name, $name, $name]; |
|
281
|
|
|
return $this->findEntities($sql, $params, $limit, $offset); |
|
282
|
|
|
} |
|
283
|
|
|
|
|
284
|
|
|
/** |
|
285
|
|
|
* Returns all tracks specified by name, artist name, and/or album name |
|
286
|
|
|
* @param string|null $name the name of the track |
|
287
|
|
|
* @param string|null $artistName the name of the artist |
|
288
|
|
|
* @param string $userId the name of the user |
|
289
|
|
|
* @return Track[] Tracks matching the criteria |
|
290
|
|
|
*/ |
|
291
|
|
|
public function findAllByNameArtistOrAlbum(?string $name, ?string $artistName, ?string $albumName, string $userId) : array { |
|
292
|
|
|
$sqlConditions = []; |
|
293
|
|
|
$params = [$userId]; |
|
294
|
|
|
|
|
295
|
|
|
if (!empty($name)) { |
|
296
|
|
|
$sqlConditions[] = 'LOWER(`title`) = LOWER(?)'; |
|
297
|
|
|
$params[] = $name; |
|
298
|
|
|
} |
|
299
|
|
|
|
|
300
|
|
|
if (!empty($artistName)) { |
|
301
|
|
|
$sqlConditions[] = 'LOWER(`artist`.`name`) = LOWER(?)'; |
|
302
|
|
|
$params[] = $artistName; |
|
303
|
|
|
} |
|
304
|
|
|
|
|
305
|
|
|
if (!empty($albumName)) { |
|
306
|
|
|
$sqlConditions[] = 'LOWER(`album`.`name`) = LOWER(?)'; |
|
307
|
|
|
$params[] = $albumName; |
|
308
|
|
|
} |
|
309
|
|
|
|
|
310
|
|
|
// at least one condition has to be given, otherwise return an empty set |
|
311
|
|
|
if (\count($sqlConditions) > 0) { |
|
312
|
|
|
$sql = $this->selectUserEntities(\implode(' AND ', $sqlConditions)); |
|
313
|
|
|
return $this->findEntities($sql, $params); |
|
314
|
|
|
} else { |
|
315
|
|
|
return []; |
|
316
|
|
|
} |
|
317
|
|
|
} |
|
318
|
|
|
|
|
319
|
|
|
const FAVORITE_TRACK = 0x1; |
|
320
|
|
|
const FAVORITE_ALBUM = 0x2; |
|
321
|
|
|
const FAVORITE_ARTIST = 0x4; |
|
322
|
|
|
|
|
323
|
|
|
/** |
|
324
|
|
|
* Returns all tracks specified by various criteria, all of which are optional |
|
325
|
|
|
* @param int[] $genres Array of genre IDs |
|
326
|
|
|
* @param int[] $artists Array of artist IDs |
|
327
|
|
|
* @param int|null $fromYear Earliest release year to include |
|
328
|
|
|
* @param int|null $toYear Latest release year to include |
|
329
|
|
|
* @param int|null $favorite Bit mask of FAVORITE_TRACK, FAVORITE_ALBUM, FAVORITE_ARTIST (given favorite types are ORed in the query) |
|
330
|
|
|
* @param int $sortBy Sorting rule as defined in the class SortBy |
|
331
|
|
|
* @param string $userId the name of the user |
|
332
|
|
|
* @return Track[] Tracks matching the criteria |
|
333
|
|
|
*/ |
|
334
|
|
|
public function findAllByCriteria( |
|
335
|
|
|
array $genres, array $artists, ?int $fromYear, ?int $toYear, ?int $favorite, |
|
336
|
|
|
int $sortBy, bool $invertSort, string $userId, ?int $limit=null, ?int $offset=null) : array { |
|
337
|
|
|
|
|
338
|
|
|
$sqlConditions = []; |
|
339
|
|
|
$params = [$userId]; |
|
340
|
|
|
|
|
341
|
|
|
if (!empty($genres)) { |
|
342
|
|
|
$sqlConditions[] = '`genre_id` IN ' . $this->questionMarks(\count($genres)); |
|
343
|
|
|
$params = \array_merge($params, $genres); |
|
344
|
|
|
} |
|
345
|
|
|
|
|
346
|
|
|
if (!empty($artists)) { |
|
347
|
|
|
$sqlConditions[] = '`artist_id` IN ' . $this->questionMarks(\count($artists)); |
|
348
|
|
|
$params = \array_merge($params, $artists); |
|
349
|
|
|
} |
|
350
|
|
|
|
|
351
|
|
|
if (!empty($fromYear)) { |
|
352
|
|
|
$sqlConditions[] = '`year` >= ?'; |
|
353
|
|
|
$params[] = $fromYear; |
|
354
|
|
|
} |
|
355
|
|
|
|
|
356
|
|
|
if (!empty($toYear)) { |
|
357
|
|
|
$sqlConditions[] = '`year` <= ?'; |
|
358
|
|
|
$params[] = $toYear; |
|
359
|
|
|
} |
|
360
|
|
|
|
|
361
|
|
|
if (!empty($favorite)) { |
|
362
|
|
|
$favConds = []; |
|
363
|
|
|
if ($favorite & self::FAVORITE_TRACK) { |
|
364
|
|
|
$favConds[] = '`*PREFIX*music_tracks`.`starred` IS NOT NULL'; |
|
365
|
|
|
} |
|
366
|
|
|
if ($favorite & self::FAVORITE_ALBUM) { |
|
367
|
|
|
$favConds[] = '`album`.`starred` IS NOT NULL'; |
|
368
|
|
|
} |
|
369
|
|
|
if ($favorite & self::FAVORITE_ARTIST) { |
|
370
|
|
|
$favConds[] = '`artist`.`starred` IS NOT NULL'; |
|
371
|
|
|
} |
|
372
|
|
|
$sqlConditions[] = '(' . \implode(' OR ', $favConds) . ')'; |
|
373
|
|
|
} |
|
374
|
|
|
|
|
375
|
|
|
$sql = $this->selectUserEntities(\implode(' AND ', $sqlConditions), $this->formatSortingClause($sortBy, $invertSort)); |
|
376
|
|
|
return $this->findEntities($sql, $params, $limit, $offset); |
|
377
|
|
|
} |
|
378
|
|
|
|
|
379
|
|
|
/** |
|
380
|
|
|
* Find most frequently played tracks |
|
381
|
|
|
* @return Track[] |
|
382
|
|
|
*/ |
|
383
|
|
|
public function findFrequentPlay(string $userId, ?int $limit=null, ?int $offset=null) : array { |
|
384
|
|
|
$sql = $this->selectUserEntities('`play_count` > 0', 'ORDER BY `play_count` DESC, LOWER(`title`)'); |
|
385
|
|
|
return $this->findEntities($sql, [$userId], $limit, $offset); |
|
386
|
|
|
} |
|
387
|
|
|
|
|
388
|
|
|
/** |
|
389
|
|
|
* Find most recently played tracks |
|
390
|
|
|
* @return Track[] |
|
391
|
|
|
*/ |
|
392
|
|
|
public function findRecentPlay(string $userId, ?int $limit=null, ?int $offset=null) : array { |
|
393
|
|
|
$sql = $this->selectUserEntities('`last_played` IS NOT NULL', 'ORDER BY `last_played` DESC'); |
|
394
|
|
|
return $this->findEntities($sql, [$userId], $limit, $offset); |
|
395
|
|
|
} |
|
396
|
|
|
|
|
397
|
|
|
/** |
|
398
|
|
|
* Find least recently played tracks |
|
399
|
|
|
* @return Track[] |
|
400
|
|
|
*/ |
|
401
|
|
|
public function findNotRecentPlay(string $userId, ?int $limit=null, ?int $offset=null) : array { |
|
402
|
|
|
$sql = $this->selectUserEntities(null, 'ORDER BY `last_played` ASC'); |
|
403
|
|
|
return $this->findEntities($sql, [$userId], $limit, $offset); |
|
404
|
|
|
} |
|
405
|
|
|
|
|
406
|
|
|
/** |
|
407
|
|
|
* Finds all track IDs of the user along with the parent folder ID of each track |
|
408
|
|
|
* @return array where keys are folder IDs and values are arrays of track IDs |
|
409
|
|
|
*/ |
|
410
|
|
|
public function findTrackAndFolderIds(string $userId) : array { |
|
411
|
|
|
$sql = 'SELECT `track`.`id` AS id, `file`.`name` AS `filename`, `file`.`parent` AS `parent` |
|
412
|
|
|
FROM `*PREFIX*music_tracks` `track` |
|
413
|
|
|
JOIN `*PREFIX*filecache` `file` |
|
414
|
|
|
ON `track`.`file_id` = `file`.`fileid` |
|
415
|
|
|
WHERE `track`.`user_id` = ?'; |
|
416
|
|
|
|
|
417
|
|
|
$result = $this->execute($sql, [$userId]); |
|
418
|
|
|
$rows = $result->fetchAll(); |
|
419
|
|
|
$result->closeCursor(); |
|
420
|
|
|
|
|
421
|
|
|
// Sort the results according the file names. This can't be made using ORDERBY in the |
|
422
|
|
|
// SQL query because then we couldn't use the "natural order" comparison algorithm |
|
423
|
|
|
\usort($rows, fn($a, $b) => \strnatcasecmp($a['filename'], $b['filename'])); |
|
424
|
|
|
|
|
425
|
|
|
// group the files to parent folder "buckets" |
|
426
|
|
|
$return = []; |
|
427
|
|
|
foreach ($rows as $row) { |
|
428
|
|
|
$return[(int)$row['parent']][] = (int)$row['id']; |
|
429
|
|
|
} |
|
430
|
|
|
|
|
431
|
|
|
return $return; |
|
432
|
|
|
} |
|
433
|
|
|
|
|
434
|
|
|
/** |
|
435
|
|
|
* Find names and parents of the file system nodes with given IDs |
|
436
|
|
|
* @param int[] $nodeIds |
|
437
|
|
|
* @return array where keys are the node IDs and values are associative arrays like { 'name' => string, 'parent' => int }; |
|
438
|
|
|
*/ |
|
439
|
|
|
public function findNodeNamesAndParents(array $nodeIds) : array { |
|
440
|
|
|
$return = []; |
|
441
|
|
|
|
|
442
|
|
|
if (!empty($nodeIds)) { |
|
443
|
|
|
$sql = 'SELECT `fileid`, `name`, `parent` |
|
444
|
|
|
FROM `*PREFIX*filecache` |
|
445
|
|
|
WHERE `fileid` IN '. $this->questionMarks(\count($nodeIds)); |
|
446
|
|
|
|
|
447
|
|
|
$result = $this->execute($sql, $nodeIds); |
|
448
|
|
|
$rows = $result->fetchAll(); |
|
449
|
|
|
$result->closeCursor(); |
|
450
|
|
|
|
|
451
|
|
|
foreach ($rows as $row) { |
|
452
|
|
|
$return[$row['fileid']] = [ |
|
453
|
|
|
'name' => $row['name'], |
|
454
|
|
|
'parent' => (int)$row['parent'] |
|
455
|
|
|
]; |
|
456
|
|
|
} |
|
457
|
|
|
} |
|
458
|
|
|
|
|
459
|
|
|
return $return; |
|
460
|
|
|
} |
|
461
|
|
|
|
|
462
|
|
|
/** |
|
463
|
|
|
* Find all immediate sub-folders of the given folders |
|
464
|
|
|
* @param int[] $folderIds |
|
465
|
|
|
* @return int[] |
|
466
|
|
|
*/ |
|
467
|
|
|
public function findSubFolderIds(array $folderIds) : array { |
|
468
|
|
|
$rows = []; |
|
469
|
|
|
|
|
470
|
|
|
if (!empty($folderIds)) { |
|
471
|
|
|
$sql = 'SELECT `fileid` |
|
472
|
|
|
FROM `*PREFIX*filecache` `files` |
|
473
|
|
|
INNER JOIN `*PREFIX*mimetypes` `mimes` |
|
474
|
|
|
ON `files`.`mimetype` = `mimes`.`id` |
|
475
|
|
|
WHERE `mimes`.`mimetype` = \'httpd/unix-directory\' |
|
476
|
|
|
AND `parent` IN '. $this->questionMarks(\count($folderIds)); |
|
477
|
|
|
|
|
478
|
|
|
$result = $this->execute($sql, $folderIds); |
|
479
|
|
|
$rows = $result->fetchAll(\PDO::FETCH_COLUMN); |
|
480
|
|
|
$result->closeCursor(); |
|
481
|
|
|
} |
|
482
|
|
|
|
|
483
|
|
|
return $rows; |
|
484
|
|
|
} |
|
485
|
|
|
|
|
486
|
|
|
/** |
|
487
|
|
|
* Returns all genre IDs associated with the given artist |
|
488
|
|
|
* @return int[] |
|
489
|
|
|
*/ |
|
490
|
|
|
public function getGenresByArtistId(int $artistId, string $userId) : array { |
|
491
|
|
|
$sql = 'SELECT DISTINCT(`genre_id`) FROM `*PREFIX*music_tracks` WHERE |
|
492
|
|
|
`genre_id` IS NOT NULL AND `user_id` = ? AND `artist_id` = ?'; |
|
493
|
|
|
$result = $this->execute($sql, [$userId, $artistId]); |
|
494
|
|
|
$rows = $result->fetchAll(\PDO::FETCH_COLUMN); |
|
495
|
|
|
$result->closeCursor(); |
|
496
|
|
|
return $rows; |
|
497
|
|
|
} |
|
498
|
|
|
|
|
499
|
|
|
/** |
|
500
|
|
|
* Returns all tracks IDs of the user, organized by the genre_id. |
|
501
|
|
|
* @return array where keys are genre IDs and values are arrays of track IDs |
|
502
|
|
|
*/ |
|
503
|
|
|
public function mapGenreIdsToTrackIds(string $userId) : array { |
|
504
|
|
|
$sql = 'SELECT `id`, `genre_id` FROM `*PREFIX*music_tracks` |
|
505
|
|
|
WHERE `genre_id` IS NOT NULL and `user_id` = ?'; |
|
506
|
|
|
$result = $this->execute($sql, [$userId]); |
|
507
|
|
|
$rows = $result->fetchAll(); |
|
508
|
|
|
$result->closeCursor(); |
|
509
|
|
|
|
|
510
|
|
|
$return = []; |
|
511
|
|
|
foreach ($rows as $row) { |
|
512
|
|
|
$return[(int)$row['genre_id']][] = (int)$row['id']; |
|
513
|
|
|
} |
|
514
|
|
|
|
|
515
|
|
|
return $return; |
|
516
|
|
|
} |
|
517
|
|
|
|
|
518
|
|
|
/** |
|
519
|
|
|
* Returns file IDs of the tracks which do not have genre scanned. This is not the same |
|
520
|
|
|
* thing as unknown genre, which means that the genre has been scanned but was not found |
|
521
|
|
|
* from the track metadata. |
|
522
|
|
|
* @return int[] |
|
523
|
|
|
*/ |
|
524
|
|
|
public function findFilesWithoutScannedGenre(string $userId) : array { |
|
525
|
|
|
$sql = 'SELECT `track`.`file_id` FROM `*PREFIX*music_tracks` `track` |
|
526
|
|
|
INNER JOIN `*PREFIX*filecache` `file` |
|
527
|
|
|
ON `track`.`file_id` = `file`.`fileid` |
|
528
|
|
|
WHERE `genre_id` IS NULL and `user_id` = ?'; |
|
529
|
|
|
$result = $this->execute($sql, [$userId]); |
|
530
|
|
|
$rows = $result->fetchAll(\PDO::FETCH_COLUMN); |
|
531
|
|
|
$result->closeCursor(); |
|
532
|
|
|
return $rows; |
|
533
|
|
|
} |
|
534
|
|
|
|
|
535
|
|
|
/** |
|
536
|
|
|
* Update "last played" timestamp and increment the total play count of the track. |
|
537
|
|
|
* The DB row is updated *without* updating the `updated` column. |
|
538
|
|
|
* @return bool true if the track was found and updated, false otherwise |
|
539
|
|
|
*/ |
|
540
|
|
|
public function recordTrackPlayed(int $trackId, string $userId, \DateTime $timeOfPlay) : bool { |
|
541
|
|
|
$sql = 'UPDATE `*PREFIX*music_tracks` |
|
542
|
|
|
SET `last_played` = ?, `play_count` = `play_count` + 1 |
|
543
|
|
|
WHERE `user_id` = ? AND `id` = ?'; |
|
544
|
|
|
$params = [$timeOfPlay->format(BaseMapper::SQL_DATE_FORMAT), $userId, $trackId]; |
|
545
|
|
|
|
|
546
|
|
|
$result = $this->execute($sql, $params); |
|
547
|
|
|
$updated = ($result->rowCount() > 0); |
|
548
|
|
|
$result->closeCursor(); |
|
549
|
|
|
|
|
550
|
|
|
return $updated; |
|
551
|
|
|
} |
|
552
|
|
|
|
|
553
|
|
|
/** |
|
554
|
|
|
* Marks tracks as dirty, ultimately requesting the user to rescan them |
|
555
|
|
|
* @param int[] $fileIds file IDs of the tracks to mark as dirty |
|
556
|
|
|
* @param string[]|null $userIds the target users; if omitted, the tracks matching the |
|
557
|
|
|
* $fileIds are marked for all users |
|
558
|
|
|
* @return int number of rows affected |
|
559
|
|
|
*/ |
|
560
|
|
|
public function markTracksDirty(array $fileIds, ?array $userIds=null) : int { |
|
561
|
|
|
$sql = 'UPDATE `*PREFIX*music_tracks` |
|
562
|
|
|
SET `dirty` = 1 |
|
563
|
|
|
WHERE `file_id` IN ' . $this->questionMarks(\count($fileIds)); |
|
564
|
|
|
$params = $fileIds; |
|
565
|
|
|
|
|
566
|
|
|
if (!empty($userIds)) { |
|
567
|
|
|
$sql .= ' AND `user_id` IN ' . $this->questionMarks(\count($userIds)); |
|
568
|
|
|
$params = \array_merge($params, $userIds); |
|
569
|
|
|
} |
|
570
|
|
|
|
|
571
|
|
|
$result = $this->execute($sql, $params); |
|
572
|
|
|
$updated = $result->rowCount(); |
|
573
|
|
|
$result->closeCursor(); |
|
574
|
|
|
|
|
575
|
|
|
return $updated; |
|
576
|
|
|
} |
|
577
|
|
|
|
|
578
|
|
|
/** |
|
579
|
|
|
* Overridden from the base implementation to provide support for table-specific rules |
|
580
|
|
|
* |
|
581
|
|
|
* {@inheritdoc} |
|
582
|
|
|
* @see BaseMapper::advFormatSqlCondition() |
|
583
|
|
|
*/ |
|
584
|
|
|
protected function advFormatSqlCondition(string $rule, string $sqlOp, string $conv) : string { |
|
585
|
|
|
// The extra subquery "mysqlhack" seen around some nested queries is needed in order for these to not be insanely slow on MySQL. |
|
586
|
|
|
$condForRule = [ |
|
587
|
|
|
'anywhere' => self::formatAdvSearchAnywhereCond($sqlOp, $conv), |
|
588
|
|
|
'album' => "$conv(`album`.`name`) $sqlOp $conv(?)", |
|
589
|
|
|
'artist' => "$conv(`artist`.`name`) $sqlOp $conv(?)", |
|
590
|
|
|
'album_artist' => "`album_id` IN (SELECT `al`.`id` from `*PREFIX*music_albums` `al` JOIN `*PREFIX*music_artists` `ar` ON `al`.`album_artist_id` = `ar`.`id` WHERE $conv(`ar`.`name`) $sqlOp $conv(?))", |
|
591
|
|
|
'album_artist_id' => "$sqlOp `album_id` IN (SELECT `id` from `*PREFIX*music_albums` WHERE `album_artist_id` = ?)", // our own API extension |
|
592
|
|
|
'track' => "`number` $sqlOp ?", |
|
593
|
|
|
'year' => "`year` $sqlOp ?", |
|
594
|
|
|
'albumrating' => "`album`.`rating` $sqlOp ?", |
|
595
|
|
|
'artistrating' => "`artist`.`rating` $sqlOp ?", |
|
596
|
|
|
'favorite_album' => "$conv(`album`.`name`) $sqlOp $conv(?) AND `album`.`starred` IS NOT NULL", |
|
597
|
|
|
'favorite_artist' => "$conv(`artist`.`name`) $sqlOp $conv(?) AND `artist`.`starred` IS NOT NULL", |
|
598
|
|
|
'played_times' => "`play_count` $sqlOp ?", |
|
599
|
|
|
'last_play' => "`last_played` $sqlOp ?", |
|
600
|
|
|
'myplayed' => "`last_played` $sqlOp", // operator "IS NULL" or "IS NOT NULL" |
|
601
|
|
|
'myplayedalbum' => "`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" |
|
602
|
|
|
'myplayedartist' => "`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" |
|
603
|
|
|
'time' => "`length` $sqlOp ?", |
|
604
|
|
|
'bitrate' => "`bitrate` $sqlOp ?", |
|
605
|
|
|
'song_genre' => "$conv(`genre`.`name`) $sqlOp $conv(?)", |
|
606
|
|
|
'album_genre' => "`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 $conv(" . $this->sqlGroupConcat('`g`.`name`') . ") $sqlOp $conv(?)) mysqlhack)", |
|
607
|
|
|
'artist_genre' => "`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 $conv(" . $this->sqlGroupConcat('`g`.`name`') . ") $sqlOp $conv(?)) mysqlhack)", |
|
608
|
|
|
'no_genre' => ($sqlOp == 'IS NOT NULL') ? '`genre`.`name` = ""' : '`genre`.`name` != ""', |
|
609
|
|
|
'playlist' => "$sqlOp EXISTS (SELECT 1 from `*PREFIX*music_playlists` `p` WHERE `p`.`id` = ? AND `p`.`track_ids` LIKE " . $this->sqlConcat("'%|'", "`*PREFIX*music_tracks`.`id`", "'|%'") . ')', |
|
610
|
|
|
'playlist_name' => "EXISTS (SELECT 1 from `*PREFIX*music_playlists` `p` WHERE $conv(`p`.`name`) $sqlOp $conv(?) AND `p`.`track_ids` LIKE " . $this->sqlConcat("'%|'", "`*PREFIX*music_tracks`.`id`", "'|%'") . ')', |
|
611
|
|
|
'recent_played' => "`*PREFIX*music_tracks`.`id` IN (SELECT * FROM (SELECT `id` FROM `*PREFIX*music_tracks` WHERE `user_id` = ? ORDER BY `last_played` DESC LIMIT $sqlOp) mysqlhack)", |
|
612
|
|
|
'file' => "$conv(`file`.`name`) $sqlOp $conv(?)", |
|
613
|
|
|
'mbid_album' => "`album`.`mbid` $sqlOp ?", |
|
614
|
|
|
'mbid_artist' => "`artist`.`mbid` $sqlOp ?" |
|
615
|
|
|
]; |
|
616
|
|
|
|
|
617
|
|
|
// Add alias rules |
|
618
|
|
|
$condForRule['played'] = $condForRule['myplayed']; // we give no access to other people's data; not part of the API spec but Ample uses this |
|
619
|
|
|
$condForRule['genre'] = $condForRule['song_genre']; |
|
620
|
|
|
$condForRule['song'] = parent::advFormatSqlCondition('title', $sqlOp, $conv); |
|
621
|
|
|
$condForRule['mbid_song'] = parent::advFormatSqlCondition('mbid', $sqlOp, $conv); |
|
622
|
|
|
|
|
623
|
|
|
return $condForRule[$rule] ?? parent::advFormatSqlCondition($rule, $sqlOp, $conv); |
|
624
|
|
|
} |
|
625
|
|
|
|
|
626
|
|
|
private static function formatAdvSearchAnywhereCond(string $sqlOp, string $conv) : string { |
|
627
|
|
|
$fields = [ |
|
628
|
|
|
"`*PREFIX*music_tracks`.`title`", |
|
629
|
|
|
"`file`.`name`", |
|
630
|
|
|
"`artist`.`name`", |
|
631
|
|
|
"`album`.`name`", |
|
632
|
|
|
"`genre`.`name`" |
|
633
|
|
|
]; |
|
634
|
|
|
$parts = \array_map(fn($field) => "$conv($field) $sqlOp $conv(?)", $fields); |
|
635
|
|
|
|
|
636
|
|
|
$negativeOp = \in_array($sqlOp, ['NOT LIKE', '!=', 'NOT REGEXP']); |
|
637
|
|
|
$cond = \implode($negativeOp ? ' AND ' : ' OR ', $parts); |
|
638
|
|
|
|
|
639
|
|
|
return "($cond)"; |
|
640
|
|
|
} |
|
641
|
|
|
} |
|
642
|
|
|
|
owncloud /
music
