|
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 OCA\Music\Utility\Util; |
|
18
|
|
|
|
|
19
|
|
|
use OCP\IDBConnection; |
|
20
|
|
|
|
|
21
|
|
|
/** |
|
22
|
|
|
* @phpstan-extends BaseMapper<Artist> |
|
23
|
|
|
*/ |
|
24
|
|
|
class ArtistMapper extends BaseMapper { |
|
25
|
|
|
public function __construct(IDBConnection $db) { |
|
26
|
|
|
parent::__construct($db, 'music_artists', Artist::class, 'name'); |
|
27
|
|
|
} |
|
28
|
|
|
|
|
29
|
|
|
/** |
|
30
|
|
|
* @param string $userId |
|
31
|
|
|
* @param integer $sortBy sort order of the result set |
|
32
|
|
|
* @return Artist[] |
|
33
|
|
|
*/ |
|
34
|
|
|
public function findAllHavingAlbums(string $userId, int $sortBy=SortBy::None, |
|
35
|
|
|
?int $limit=null, ?int $offset=null, ?string $name=null, int $matchMode=MatchMode::Exact) : array { |
|
36
|
|
|
$params = [$userId]; |
|
37
|
|
|
$condition = 'EXISTS (SELECT 1 FROM `*PREFIX*music_albums` `album` WHERE `*PREFIX*music_artists`.`id` = `album`.`album_artist_id`)'; |
|
38
|
|
|
|
|
39
|
|
|
if ($name !== null) { |
|
40
|
|
|
[$nameCond, $nameParams] = $this->formatNameConditions($name, $matchMode); |
|
41
|
|
|
$condition .= " AND $nameCond"; |
|
42
|
|
|
$params = \array_merge($params, $nameParams); |
|
43
|
|
|
} |
|
44
|
|
|
|
|
45
|
|
|
$sql = $this->selectUserEntities($condition, $this->formatSortingClause($sortBy)); |
|
46
|
|
|
|
|
47
|
|
|
return $this->findEntities($sql, $params, $limit, $offset); |
|
|
|
|
|
|
48
|
|
|
} |
|
49
|
|
|
|
|
50
|
|
|
/** |
|
51
|
|
|
* @param int $genreId |
|
52
|
|
|
* @param string $userId |
|
53
|
|
|
* @param int|null $limit |
|
54
|
|
|
* @param int|null $offset |
|
55
|
|
|
* @return Artist[] |
|
56
|
|
|
*/ |
|
57
|
|
|
public function findAllByGenre(int $genreId, string $userId, ?int $limit=null, ?int $offset=null) : array { |
|
58
|
|
|
$sql = $this->selectUserEntities('EXISTS '. |
|
59
|
|
|
'(SELECT 1 FROM `*PREFIX*music_tracks` `track` |
|
60
|
|
|
WHERE `*PREFIX*music_artists`.`id` = `track`.`artist_id` |
|
61
|
|
|
AND `track`.`genre_id` = ?)'); |
|
62
|
|
|
|
|
63
|
|
|
$params = [$userId, $genreId]; |
|
64
|
|
|
return $this->findEntities($sql, $params, $limit, $offset); |
|
|
|
|
|
|
65
|
|
|
} |
|
66
|
|
|
|
|
67
|
|
|
/** |
|
68
|
|
|
* returns summed track play counts of each aritst of the user, omittig artists which have never been played |
|
69
|
|
|
* |
|
70
|
|
|
* @return array [int => int], keys are artist IDs and values are play count sums; ordered largest counts first |
|
71
|
|
|
*/ |
|
72
|
|
|
public function getArtistTracksPlayCount(string $userId, ?int $limit=null, ?int $offset=null) : array { |
|
73
|
|
|
$sql = 'SELECT `artist_id`, SUM(`play_count`) AS `sum_count` |
|
74
|
|
|
FROM `*PREFIX*music_tracks` |
|
75
|
|
|
WHERE `user_id` = ? AND `play_count` > 0 |
|
76
|
|
|
GROUP BY `artist_id` |
|
77
|
|
|
ORDER BY `sum_count` DESC, `artist_id`'; // the second criterion is just to make the order predictable on even counts |
|
78
|
|
|
|
|
79
|
|
|
$result = $this->execute($sql, [$userId], $limit, $offset); |
|
|
|
|
|
|
80
|
|
|
$playCountByArtist = []; |
|
81
|
|
|
while ($row = $result->fetch()) { |
|
82
|
|
|
$playCountByArtist[$row['artist_id']] = (int)$row['sum_count']; |
|
83
|
|
|
} |
|
84
|
|
|
return $playCountByArtist; |
|
85
|
|
|
} |
|
86
|
|
|
|
|
87
|
|
|
/** |
|
88
|
|
|
* returns the latest play time of each artist of the user, omittig artists which have never been played |
|
89
|
|
|
* |
|
90
|
|
|
* @return array [int => string], keys are artist IDs and values are date-times; ordered latest times first |
|
91
|
|
|
*/ |
|
92
|
|
|
public function getLatestArtistPlayTimes(string $userId, ?int $limit=null, ?int $offset=null) : array { |
|
93
|
|
|
$sql = 'SELECT `artist_id`, MAX(`last_played`) AS `latest_time` |
|
94
|
|
|
FROM `*PREFIX*music_tracks` |
|
95
|
|
|
WHERE `user_id` = ? AND `last_played` IS NOT NULL |
|
96
|
|
|
GROUP BY `artist_id` |
|
97
|
|
|
ORDER BY `latest_time` DESC'; |
|
98
|
|
|
|
|
99
|
|
|
$result = $this->execute($sql, [$userId], $limit, $offset); |
|
|
|
|
|
|
100
|
|
|
$latestTimeByArtist = []; |
|
101
|
|
|
while ($row = $result->fetch()) { |
|
102
|
|
|
$latestTimeByArtist[$row['artist_id']] = $row['latest_time']; |
|
103
|
|
|
} |
|
104
|
|
|
return $latestTimeByArtist; |
|
105
|
|
|
} |
|
106
|
|
|
|
|
107
|
|
|
/** |
|
108
|
|
|
* returns the latest play time of each artist of the user, including artists which have never been played |
|
109
|
|
|
* |
|
110
|
|
|
* @return array [int => ?string], keys are artist IDs and values are date-times (or null for never played); |
|
111
|
|
|
* ordered furthest times first |
|
112
|
|
|
*/ |
|
113
|
|
|
public function getFurthestArtistPlayTimes(string $userId, ?int $limit=null, ?int $offset=null) : array { |
|
114
|
|
|
$sql = 'SELECT `artist_id`, MAX(`last_played`) AS `latest_time` |
|
115
|
|
|
FROM `*PREFIX*music_tracks` |
|
116
|
|
|
WHERE `user_id` = ? |
|
117
|
|
|
GROUP BY `artist_id` |
|
118
|
|
|
ORDER BY `latest_time` ASC'; |
|
119
|
|
|
|
|
120
|
|
|
$result = $this->execute($sql, [$userId], $limit, $offset); |
|
|
|
|
|
|
121
|
|
|
$latestTimeByArtist = []; |
|
122
|
|
|
while ($row = $result->fetch()) { |
|
123
|
|
|
$latestTimeByArtist[$row['artist_id']] = $row['latest_time']; |
|
124
|
|
|
} |
|
125
|
|
|
return $latestTimeByArtist; |
|
126
|
|
|
} |
|
127
|
|
|
|
|
128
|
|
|
/** |
|
129
|
|
|
* @param integer[] $coverFileIds |
|
130
|
|
|
* @param string[]|null $userIds the users whose music library is targeted; all users are targeted if omitted |
|
131
|
|
|
* @return Artist[] artists which got modified (with incomplete data, only id and user are valid), |
|
132
|
|
|
* empty array if none |
|
133
|
|
|
*/ |
|
134
|
|
|
public function removeCovers(array $coverFileIds, ?array $userIds=null) : array { |
|
135
|
|
|
// find albums using the given file as cover |
|
136
|
|
|
$sql = 'SELECT `id`, `user_id` FROM `*PREFIX*music_artists` WHERE `cover_file_id` IN ' . |
|
137
|
|
|
$this->questionMarks(\count($coverFileIds)); |
|
138
|
|
|
$params = $coverFileIds; |
|
139
|
|
|
if ($userIds !== null) { |
|
140
|
|
|
$sql .= ' AND `user_id` IN ' . $this->questionMarks(\count($userIds)); |
|
141
|
|
|
$params = \array_merge($params, $userIds); |
|
142
|
|
|
} |
|
143
|
|
|
$artists = $this->findEntities($sql, $params); |
|
|
|
|
|
|
144
|
|
|
|
|
145
|
|
|
// if any artists found, remove the cover from those |
|
146
|
|
|
$count = \count($artists); |
|
147
|
|
|
if ($count) { |
|
148
|
|
|
$sql = 'UPDATE `*PREFIX*music_artists` |
|
149
|
|
|
SET `cover_file_id` = NULL |
|
150
|
|
|
WHERE `id` IN ' . $this->questionMarks($count); |
|
151
|
|
|
$params = Util::extractIds($artists); |
|
152
|
|
|
$this->execute($sql, $params); |
|
|
|
|
|
|
153
|
|
|
} |
|
154
|
|
|
|
|
155
|
|
|
return $artists; |
|
156
|
|
|
} |
|
157
|
|
|
|
|
158
|
|
|
/** |
|
159
|
|
|
* Overridden from the base implementation to provide support for table-specific rules |
|
160
|
|
|
* |
|
161
|
|
|
* {@inheritdoc} |
|
162
|
|
|
* @see BaseMapper::advFormatSqlCondition() |
|
163
|
|
|
*/ |
|
164
|
|
|
protected function advFormatSqlCondition(string $rule, string $sqlOp) : string { |
|
165
|
|
|
// The extra subquery "mysqlhack" seen around some nested queries is needed in order for these to not be insanely slow on MySQL. |
|
166
|
|
|
// In case of 'recent_played', the MySQL 5.5.62 errored with "1235 This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'" without the extra subquery. |
|
167
|
|
|
switch ($rule) { |
|
168
|
|
|
case 'album': return "`*PREFIX*music_artists`.`id` IN (SELECT `artist_id` FROM `*PREFIX*music_tracks` `t` JOIN `*PREFIX*music_albums` `a` ON `t`.`album_id` = `a`.`id` WHERE LOWER(`a`.`name`) $sqlOp LOWER(?))"; |
|
169
|
|
|
case 'artist': return parent::advFormatSqlCondition('name', $sqlOp); // alias |
|
170
|
|
|
case 'song': return "`*PREFIX*music_artists`.`id` IN (SELECT `artist_id` FROM `*PREFIX*music_tracks` `t` WHERE LOWER(`t`.`title`) $sqlOp LOWER(?))"; |
|
171
|
|
|
case 'songrating': return "`*PREFIX*music_artists`.`id` IN (SELECT `artist_id` FROM `*PREFIX*music_tracks` `t` WHERE `t`.`rating` $sqlOp ?)"; |
|
172
|
|
|
case 'albumrating': return "`*PREFIX*music_artists`.`id` IN (SELECT `album_artist_id` from `*PREFIX*music_albums` `al` WHERE `al`.`rating` $sqlOp ?)"; |
|
173
|
|
|
case 'played_times': return "`*PREFIX*music_artists`.`id` IN (SELECT * FROM (SELECT `artist_id` from `*PREFIX*music_tracks` GROUP BY `artist_id` HAVING SUM(`play_count`) $sqlOp ?) mysqlhack)"; |
|
174
|
|
|
case 'last_play': return "`*PREFIX*music_artists`.`id` IN (SELECT * FROM (SELECT `artist_id` from `*PREFIX*music_tracks` GROUP BY `artist_id` HAVING MAX(`last_played`) $sqlOp ?) mysqlhack)"; |
|
175
|
|
|
case 'played': // fall through, we give no access to other people's data; not part of the API spec but Ample uses this |
|
176
|
|
|
case 'myplayed': return "`*PREFIX*music_artists`.`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" |
|
177
|
|
|
case 'album_count': return "`*PREFIX*music_artists`.`id` IN (SELECT * FROM (SELECT `album_artist_id` FROM `*PREFIX*music_albums` GROUP BY `album_artist_id` HAVING COUNT(`id`) $sqlOp ?) mysqlhack)"; |
|
178
|
|
|
case 'song_count': return "`*PREFIX*music_artists`.`id` IN (SELECT * FROM (SELECT `artist_id` FROM `*PREFIX*music_tracks` GROUP BY `artist_id` HAVING COUNT(`id`) $sqlOp ?) mysqlhack)"; |
|
179
|
|
|
case 'time': return "`*PREFIX*music_artists`.`id` IN (SELECT * FROM (SELECT `artist_id` FROM `*PREFIX*music_tracks` GROUP BY `artist_id` HAVING SUM(`length`) $sqlOp ?) mysqlhack)"; |
|
180
|
|
|
case 'genre': return "`*PREFIX*music_artists`.`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 |
|
181
|
|
|
case 'song_genre': return "`*PREFIX*music_artists`.`id` IN (SELECT `artist_id` FROM `*PREFIX*music_tracks` `t` JOIN `*PREFIX*music_genres` `g` ON `t`.`genre_id` = `g`.`id` WHERE LOWER(`g`.`name`) $sqlOp LOWER(?))"; |
|
182
|
|
|
case 'no_genre': return "`*PREFIX*music_artists`.`id` IN (SELECT `artist_id` FROM `*PREFIX*music_tracks` `t` JOIN `*PREFIX*music_genres` `g` ON `t`.`genre_id` = `g`.`id` WHERE `g`.`name` " . (($sqlOp == 'IS NOT NULL') ? '=' : '!=') . ' "")'; |
|
183
|
|
|
case 'playlist': return "`*PREFIX*music_artists`.`id` IN (SELECT `artist_id` FROM `*PREFIX*music_tracks` `t` WHERE $sqlOp EXISTS (SELECT 1 from `*PREFIX*music_playlists` `p` WHERE `p`.`id` = ? AND `p`.`track_ids` LIKE CONCAT('%|',`t`.`id`, '|%')))"; |
|
184
|
|
|
case 'playlist_name': return "`*PREFIX*music_artists`.`id` IN (SELECT `artist_id` FROM `*PREFIX*music_tracks` `t` WHERE EXISTS (SELECT 1 from `*PREFIX*music_playlists` `p` WHERE `p`.`name` $sqlOp ? AND `p`.`track_ids` LIKE CONCAT('%|',`t`.`id`, '|%')))"; |
|
185
|
|
|
case 'file': return "`*PREFIX*music_artists`.`id` IN (SELECT `artist_id` FROM `*PREFIX*music_tracks` `t` JOIN `*PREFIX*filecache` `f` ON `t`.`file_id` = `f`.`fileid` WHERE LOWER(`f`.`name`) $sqlOp LOWER(?))"; |
|
186
|
|
|
case 'recent_played': return "`*PREFIX*music_artists`.`id` IN (SELECT `artist_id` FROM (SELECT `artist_id`, MAX(`last_played`) FROM `*PREFIX*music_tracks` WHERE `user_id` = ? GROUP BY `artist_id` ORDER BY MAX(`last_played`) DESC LIMIT $sqlOp) mysqlhack)"; |
|
187
|
|
|
case 'mbid_artist': return parent::advFormatSqlCondition('mbid', $sqlOp); // alias |
|
188
|
|
|
case 'mbid_song': return "`*PREFIX*music_artists`.`id` IN (SELECT `artist_id` FROM `*PREFIX*music_tracks` `t` WHERE `t`.`mbid` $sqlOp ?)"; |
|
189
|
|
|
case 'mbid_album': return "`*PREFIX*music_artists`.`id` IN (SELECT `album_artist_id` from `*PREFIX*music_albums` `al` WHERE `al`.`mbid` $sqlOp ?)"; |
|
190
|
|
|
case 'has_image': return "`cover_file_id` $sqlOp"; // operator "IS NULL" or "IS NOT NULL" |
|
191
|
|
|
default: return parent::advFormatSqlCondition($rule, $sqlOp); |
|
192
|
|
|
} |
|
193
|
|
|
} |
|
194
|
|
|
|
|
195
|
|
|
/** |
|
196
|
|
|
* {@inheritdoc} |
|
197
|
|
|
* @see \OCA\Music\Db\BaseMapper::findUniqueEntity() |
|
198
|
|
|
* @param Artist $artist |
|
199
|
|
|
* @return Artist |
|
200
|
|
|
*/ |
|
201
|
|
|
protected function findUniqueEntity(Entity $artist) : Entity { |
|
202
|
|
|
$sql = $this->selectUserEntities('`hash` = ?'); |
|
203
|
|
|
return $this->findEntity($sql, [$artist->getUserId(), $artist->getHash()]); |
|
|
|
|
|
|
204
|
|
|
} |
|
205
|
|
|
} |
|
206
|
|
|
|
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.