AlbumMapper::findAlbumsWithCoversForTracks() - Code Metrics - Inspection of "Direct the Subsonic and Ampache base URLs to the M..." - owncloud/music - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — master ( b806b4...5b46ab )

by Pauli

created 2021-09-19 19:08 UTC

AlbumMapper::findAlbumsWithCoversForTracks() A

↳ Parent: AlbumMapper

Complexity

Conditions	1
Paths	1

Size

Total Lines	10
Code Lines	4

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
eloc	4
c	0
b	0
f	0
dl	0
loc	10
rs	10
cc	1
nc	1
nop	3

<?php declare(strict_types=1);

/**
 * ownCloud - Music app
 *
 * This file is licensed under the Affero General Public License version 3 or
 * later. See the COPYING file.
 *
 * @author Morris Jobke <[email protected]>
 * @author Pauli Järvinen <[email protected]>
 * @copyright Morris Jobke 2013, 2014
 * @copyright Pauli Järvinen 2016 - 2021
 */

namespace OCA\Music\Db;

use \OCA\Music\Utility\Util;

use \OCP\AppFramework\Db\Entity;
use \OCP\IDBConnection;

/**
 * Type hint a base class methdo to help Scrutinizer
 * @method Album updateOrInsert(Album $album)
 */
class AlbumMapper extends BaseMapper {
	public function __construct(IDBConnection $db) {
		parent::__construct($db, 'music_albums', Album::class, 'name');
	}

	/**
	 * Override the base implementation to include data from multiple tables
	 *
	 * {@inheritdoc}
	 * @see BaseMapper::selectEntities()
	 */
	protected function selectEntities(string $condition, string $extension=null) : string {
		return "SELECT `*PREFIX*music_albums`.*, `artist`.`name` AS `album_artist_name`
				FROM `*PREFIX*music_albums`
				INNER JOIN `*PREFIX*music_artists` `artist`
				ON `*PREFIX*music_albums`.`album_artist_id` = `artist`.`id`
				WHERE $condition $extension";
	}

	/**
	 * Overridden from \OCA\Music\Db\BaseMapper to add support for sorting by artist.
	 *
	 * {@inheritdoc}
	 * @see BaseMapper::formatSortingClause()
	 */
	protected function formatSortingClause(int $sortBy) : ?string {
		if ($sortBy === SortBy::Parent) {
			return 'ORDER BY LOWER(`album_artist_name`), LOWER(`*PREFIX*music_albums`.`name`)';
		} else {
			return parent::formatSortingClause($sortBy);
		}
	}

	/**
	 * returns artist IDs mapped to album IDs
	 * does not include album_artist_id
	 *
	 * @param integer[]|null $albumIds IDs of the albums; get all albums of the user if null given
	 * @param string $userId the user ID
	 * @return array int => int[], keys are albums IDs and values are arrays of artist IDs
	 */
	public function getPerformingArtistsByAlbumId(?array $albumIds, string $userId) : array {
		$sql = 'SELECT DISTINCT `track`.`album_id`, `track`.`artist_id`
				FROM `*PREFIX*music_tracks` `track`
				WHERE `track`.`user_id` = ? ';
		$params = [$userId];

		if ($albumIds !== null) {

			$sql .= 'AND `track`.`album_id` IN ' . $this->questionMarks(\count($albumIds));
			$params = \array_merge($params, $albumIds);
		}

		$result = $this->execute($sql, $params);
		$artistIds = [];
		while ($row = $result->fetch()) {
			$artistIds[$row['album_id']][] = (int)$row['artist_id'];
		}
		return $artistIds;
	}

	/**
	 * returns release years mapped to album IDs
	 *
	 * @param integer[]|null $albumIds IDs of the albums; get all albums of the user if null given
	 * @param string $userId the user ID
	 * @return array int => int[], keys are albums IDs and values are arrays of years
	 */
	public function getYearsByAlbumId(?array $albumIds, string $userId) : array {
		$sql = 'SELECT DISTINCT `track`.`album_id`, `track`.`year`
				FROM `*PREFIX*music_tracks` `track`
				WHERE `track`.`user_id` = ?
				AND `track`.`year` IS NOT NULL ';
		$params = [$userId];

		if ($albumIds !== null) {

			$sql .= 'AND `track`.`album_id` IN ' . $this->questionMarks(\count($albumIds));
			$params = \array_merge($params, $albumIds);
		}

		$result = $this->execute($sql, $params);
		$years = [];
		while ($row = $result->fetch()) {
			$years[$row['album_id']][] = (int)$row['year'];
		}
		return $years;
	}

	/**
	 * returns genres mapped to album IDs
	 *
	 * @param integer[]|null $albumIds IDs of the albums; get all albums of the user if null given
	 * @param string $userId the user ID
	 * @return array int => Genre[], keys are albums IDs and values are arrays of *partial* Genre objects (only id and name properties set)
	 */
	public function getGenresByAlbumId(?array $albumIds, string $userId) : array {
		$sql = 'SELECT DISTINCT `album_id`, `genre_id`, `*PREFIX*music_genres`.`name` AS `genre_name`
				FROM `*PREFIX*music_tracks`
				LEFT JOIN `*PREFIX*music_genres`
				ON `genre_id` = `*PREFIX*music_genres`.`id`
				WHERE `*PREFIX*music_tracks`.`user_id` = ?
				AND `genre_id` IS NOT NULL ';
		$params = [$userId];

		if ($albumIds !== null) {

			$sql .= 'AND `album_id` IN ' . $this->questionMarks(\count($albumIds));
			$params = \array_merge($params, $albumIds);
		}

		$result = $this->execute($sql, $params);
		$genres = [];
		while ($row = $result->fetch()) {
			$genre = new Genre();
			$genre->setUserId($userId);
			$genre->setId((int)$row['genre_id']);
			$genre->setName($row['genre_name']);
			$genres[$row['album_id']][] = $genre;
		}
		return $genres;
	}

	/**
	 * returns number of disks per album ID
	 *
	 * @param integer[]|null $albumIds IDs of the albums; get all albums of the user if null given
	 * @param string $userId the user ID
	 * @return array int => int, keys are albums IDs and values are disk counts
	 */
	public function getDiscCountByAlbumId(?array $albumIds, string $userId) : array {
		$sql = 'SELECT `album_id`, MAX(`disk`) AS `disc_count`
				FROM `*PREFIX*music_tracks`
				WHERE `user_id` = ?
				GROUP BY `album_id` ';
		$params = [$userId];

		if ($albumIds !== null) {

			$sql .= 'HAVING `album_id` IN ' . $this->questionMarks(\count($albumIds));
			$params = \array_merge($params, $albumIds);
		}

		$result = $this->execute($sql, $params);
		$diskCountByAlbum = [];
		while ($row = $result->fetch()) {
			$diskCountByAlbum[$row['album_id']] = (int)$row['disc_count'];
		}
		return $diskCountByAlbum;
	}

	/**
	 * returns albums of a specified artist
	 * The artist may be an album_artist or the artist of a track
	 *
	 * @param integer $artistId ID of the artist
	 * @param string $userId the user ID
	 * @return Album[]
	 */
	public function findAllByArtist(int $artistId, string $userId) : array {
		$sql = $this->selectEntities(
				'`*PREFIX*music_albums`.`id` IN (
					SELECT DISTINCT `album`.`id`
					FROM `*PREFIX*music_albums` `album`
					WHERE `album`.`album_artist_id` = ? AND `album`.`user_id` = ?
						UNION
					SELECT DISTINCT `track`.`album_id`
					FROM `*PREFIX*music_tracks` `track`
					WHERE `track`.`artist_id` = ? AND `track`.`user_id` = ?
				)',
				'ORDER BY LOWER(`*PREFIX*music_albums`.`name`)');
		$params = [$artistId, $userId, $artistId, $userId];
		return $this->findEntities($sql, $params);
	}

	/**
	 * returns albums of a specified artist
	 * The artist must album_artist on the album, artists of individual tracks are not considered
	 *
	 * @param integer $artistId ID of the artist
	 * @param string $userId the user ID
	 * @return Album[]
	 */
	public function findAllByAlbumArtist(int $artistId, string $userId) : array {
		$sql = $this->selectUserEntities('`album_artist_id` = ?');
		$params = [$userId, $artistId];
		return $this->findEntities($sql, $params);
	}

	/**
	 * @return Album[]
	 */
	public function findAllByGenre(int $genreId, string $userId, int $limit=null, int $offset=null) : array {
		$sql = $this->selectUserEntities('EXISTS '.
				'(SELECT 1 FROM `*PREFIX*music_tracks` `track`
				  WHERE `*PREFIX*music_albums`.`id` = `track`.`album_id`
				  AND `track`.`genre_id` = ?)');

		$params = [$userId, $genreId];
		return $this->findEntities($sql, $params, $limit, $offset);
	}

	/**
	 * @return boolean True if one or more albums were influenced
	 */
	public function updateFolderCover(int $coverFileId, int $folderId) : bool {
		$sql = 'SELECT DISTINCT `tracks`.`album_id`
				FROM `*PREFIX*music_tracks` `tracks`
				JOIN `*PREFIX*filecache` `files` ON `tracks`.`file_id` = `files`.`fileid`
				WHERE `files`.`parent` = ?';
		$params = [$folderId];
		$result = $this->execute($sql, $params);

		$updated = false;
		if ($result->rowCount()) {
			$sql = 'UPDATE `*PREFIX*music_albums`
					SET `cover_file_id` = ?
					WHERE `cover_file_id` IS NULL AND `id` IN (?)';
			$params = [$coverFileId, \join(",", $result->fetchAll(\PDO::FETCH_COLUMN))];
			$result = $this->execute($sql, $params);
			$updated = $result->rowCount() > 0;
		}

		return $updated;
	}

	/**
	 * Set file ID to be used as cover for an album
	 */
	public function setCover(?int $coverFileId, int $albumId) : void {
		$sql = 'UPDATE `*PREFIX*music_albums`
				SET `cover_file_id` = ?
				WHERE `id` = ?';
		$params = [$coverFileId, $albumId];
		$this->execute($sql, $params);
	}

	/**
	 * @param integer[] $coverFileIds
	 * @param string[]|null $userIds the users whose music library is targeted; all users are targeted if omitted
	 * @return Album[] albums which got modified (with incomplete data, only id and user are valid),
	 *         empty array if none
	 */
	public function removeCovers(array $coverFileIds, array $userIds=null) : array {
		// find albums using the given file as cover
		$sql = 'SELECT `id`, `user_id` FROM `*PREFIX*music_albums` WHERE `cover_file_id` IN ' .
			$this->questionMarks(\count($coverFileIds));
		$params = $coverFileIds;
		if ($userIds !== null) {
			$sql .= ' AND `user_id` IN ' . $this->questionMarks(\count($userIds));
			$params = \array_merge($params, $userIds);
		}
		$albums = $this->findEntities($sql, $params);

		// if any albums found, remove the cover from those
		$count = \count($albums);
		if ($count) {
			$sql = 'UPDATE `*PREFIX*music_albums`
				SET `cover_file_id` = NULL
				WHERE `id` IN ' . $this->questionMarks($count);
			$params = Util::extractIds($albums);
			$this->execute($sql, $params);
		}

		return $albums;
	}

	/**
	 * @param string|null $userId target user; omit to target all users
	 * @return array of dictionaries with keys [albumId, userId, parentFolderId]
	 */
	public function getAlbumsWithoutCover(string $userId = null) : array {
		$sql = 'SELECT DISTINCT `albums`.`id`, `albums`.`user_id`, `files`.`parent`
				FROM `*PREFIX*music_albums` `albums`
				JOIN `*PREFIX*music_tracks` `tracks` ON `albums`.`id` = `tracks`.`album_id`
				JOIN `*PREFIX*filecache` `files` ON `tracks`.`file_id` = `files`.`fileid`
				WHERE `albums`.`cover_file_id` IS NULL';
		$params = [];
		if ($userId !== null) {
			$sql .= ' AND `albums`.`user_id` = ?';
			$params[] = $userId;
		}
		$result = $this->execute($sql, $params);
		$return = [];
		while ($row = $result->fetch()) {
			$return[] = [
				'albumId' => (int)$row['id'],
				'userId' => $row['user_id'],
				'parentFolderId' => (int)$row['parent']
			];
		}
		return $return;
	}

	/**
	 * @return boolean True if a cover image was found and added for the album
	 */
	public function findAlbumCover(int $albumId, int $parentFolderId) : bool {
		$return = false;
		$imagesSql = 'SELECT `fileid`, `name`
					FROM `*PREFIX*filecache`
					JOIN `*PREFIX*mimetypes` ON `*PREFIX*mimetypes`.`id` = `*PREFIX*filecache`.`mimetype`
					WHERE `parent` = ? AND `*PREFIX*mimetypes`.`mimetype` LIKE \'image%\'';
		$params = [$parentFolderId];
		$result = $this->execute($imagesSql, $params);
		$images = $result->fetchAll();
		if (\count($images) > 0) {
			$getImageRank = function($imageName) {
				$coverNames = ['cover', 'albumart', 'album', 'front', 'folder'];
				foreach ($coverNames as $i => $coverName) {
					if (Util::startsWith($imageName, $coverName, /*$ignoreCase=*/true)) {
						return $i;
					}
				}
				return \count($coverNames);
			};

			\usort($images, function ($imageA, $imageB) use ($getImageRank) {
				return $getImageRank($imageA['name']) <=> $getImageRank($imageB['name']);
			});
			$imageId = (int)$images[0]['fileid'];
			$this->setCover($imageId, $albumId);
			$return = true;
		}
		return $return;
	}

	/**
	 * Given an array of track IDs, find corresponding uniqu album IDs, including only
	 * those album which have a cover art set.
	 * @param int[] $trackIds
	 * @return Album[]
	 */
	public function findAlbumsWithCoversForTracks(array $trackIds, string $userId, int $limit) : array {
		$sql = 'SELECT DISTINCT `albums`.*
				FROM `*PREFIX*music_albums` `albums`
				JOIN `*PREFIX*music_tracks` `tracks` ON `albums`.`id` = `tracks`.`album_id`
				WHERE `albums`.`cover_file_id` IS NOT NULL
				AND `albums`.`user_id` = ?
				AND `tracks`.`id` IN ' . $this->questionMarks(\count($trackIds));
		$params = \array_merge([$userId], $trackIds);

		return $this->findEntities($sql, $params, $limit);
	}

	/**
	 * Returns the count of albums where the given Artist is featured in
	 * @param integer $artistId
	 * @return integer
	 */
	public function countByArtist(int $artistId) : int {
		$sql = 'SELECT COUNT(*) AS count FROM (
					SELECT DISTINCT `track`.`album_id`
					FROM `*PREFIX*music_tracks` `track`
					WHERE `track`.`artist_id` = ?
						UNION
					SELECT `album`.`id`
					FROM `*PREFIX*music_albums` `album`
					WHERE `album`.`album_artist_id` = ?
				) tmp';
		$params = [$artistId, $artistId];
		$result = $this->execute($sql, $params);
		$row = $result->fetch();
		return (int)$row['count'];
	}

	/**
	 * Returns the count of albums where the given artist is the album artist
	 * @param integer $artistId
	 * @return integer
	 */
	public function countByAlbumArtist(int $artistId) : int {
		$sql = 'SELECT COUNT(*) AS count
				FROM `*PREFIX*music_albums` `album`
				WHERE `album`.`album_artist_id` = ?';
		$params = [$artistId];
		$result = $this->execute($sql, $params);
		$row = $result->fetch();
		return (int)$row['count'];
	}

	/**
	 * @see \OCA\Music\Db\BaseMapper::findUniqueEntity()
	 * @param Album $album
	 * @return Album
	 */
	protected function findUniqueEntity(Entity $album) : Entity {
		$sql = $this->selectUserEntities('`*PREFIX*music_albums`.`hash` = ?');
		return $this->findEntity($sql, [$album->getUserId(), $album->getHash()]);
	}
}


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 - 2021
13			*/
14
15			namespace OCA\Music\Db;
16
17			use \OCA\Music\Utility\Util;
18
19			use \OCP\AppFramework\Db\Entity;
20			use \OCP\IDBConnection;
21
22			/**
23			* Type hint a base class methdo to help Scrutinizer
24			* @method Album updateOrInsert(Album $album)
25			*/
26			class AlbumMapper extends BaseMapper {
27			public function __construct(IDBConnection $db) {
28			parent::__construct($db, 'music_albums', Album::class, 'name');
29			}
30
31			/**
32			* Override the base implementation to include data from multiple tables
33			*
34			* {@inheritdoc}
35			* @see BaseMapper::selectEntities()
36			*/
37			protected function selectEntities(string $condition, string $extension=null) : string {
38			return "SELECT `PREFIXmusic_albums`.*, `artist`.`name` AS `album_artist_name`
39			FROM `PREFIXmusic_albums`
40			INNER JOIN `PREFIXmusic_artists` `artist`
41			ON `PREFIXmusic_albums`.`album_artist_id` = `artist`.`id`
42			WHERE $condition $extension";
43			}
44
45			/**
46			* Overridden from \OCA\Music\Db\BaseMapper to add support for sorting by artist.
47			*
48			* {@inheritdoc}
49			* @see BaseMapper::formatSortingClause()
50			*/
51			protected function formatSortingClause(int $sortBy) : ?string {
52			if ($sortBy === SortBy::Parent) {
53			return 'ORDER BY LOWER(`album_artist_name`), LOWER(`PREFIXmusic_albums`.`name`)';
54			} else {
55			return parent::formatSortingClause($sortBy);
56			}
57			}
58
59			/**
60			* returns artist IDs mapped to album IDs
61			* does not include album_artist_id
62			*
63			* @param integer[]\|null $albumIds IDs of the albums; get all albums of the user if null given
64			* @param string $userId the user ID
65			* @return array int => int[], keys are albums IDs and values are arrays of artist IDs
66			*/
67			public function getPerformingArtistsByAlbumId(?array $albumIds, string $userId) : array {
68			$sql = 'SELECT DISTINCT `track`.`album_id`, `track`.`artist_id`
69			FROM `PREFIXmusic_tracks` `track`
70			WHERE `track`.`user_id` = ? ';
71			$params = [$userId];
72
73			if ($albumIds !== null) {
			0 ignored issues – show introduced 2020-11-23 02:39 UTC by Report Bug Copy Issue Report The condition `$albumIds !== null` is always `true`. Loading history...
74			$sql .= 'AND `track`.`album_id` IN ' . $this->questionMarks(\count($albumIds));
75			$params = \array_merge($params, $albumIds);
76			}
77
78			$result = $this->execute($sql, $params);
79			$artistIds = [];
80			while ($row = $result->fetch()) {
81			$artistIds[$row['album_id']][] = (int)$row['artist_id'];
82			}
83			return $artistIds;
84			}
85
86			/**
87			* returns release years mapped to album IDs
88			*
89			* @param integer[]\|null $albumIds IDs of the albums; get all albums of the user if null given
90			* @param string $userId the user ID
91			* @return array int => int[], keys are albums IDs and values are arrays of years
92			*/
93			public function getYearsByAlbumId(?array $albumIds, string $userId) : array {
94			$sql = 'SELECT DISTINCT `track`.`album_id`, `track`.`year`
95			FROM `PREFIXmusic_tracks` `track`
96			WHERE `track`.`user_id` = ?
97			AND `track`.`year` IS NOT NULL ';
98			$params = [$userId];
99
100			if ($albumIds !== null) {
			0 ignored issues – show introduced 2020-11-23 02:39 UTC by Report Bug Copy Issue Report The condition `$albumIds !== null` is always `true`. Loading history...
101			$sql .= 'AND `track`.`album_id` IN ' . $this->questionMarks(\count($albumIds));
102			$params = \array_merge($params, $albumIds);
103			}
104
105			$result = $this->execute($sql, $params);
106			$years = [];
107			while ($row = $result->fetch()) {
108			$years[$row['album_id']][] = (int)$row['year'];
109			}
110			return $years;
111			}
112
113			/**
114			* returns genres mapped to album IDs
115			*
116			* @param integer[]\|null $albumIds IDs of the albums; get all albums of the user if null given
117			* @param string $userId the user ID
118			* @return array int => Genre[], keys are albums IDs and values are arrays of partial Genre objects (only id and name properties set)
119			*/
120			public function getGenresByAlbumId(?array $albumIds, string $userId) : array {
121			$sql = 'SELECT DISTINCT `album_id`, `genre_id`, `PREFIXmusic_genres`.`name` AS `genre_name`
122			FROM `PREFIXmusic_tracks`
123			LEFT JOIN `PREFIXmusic_genres`
124			ON `genre_id` = `PREFIXmusic_genres`.`id`
125			WHERE `PREFIXmusic_tracks`.`user_id` = ?
126			AND `genre_id` IS NOT NULL ';
127			$params = [$userId];
128
129			if ($albumIds !== null) {
			0 ignored issues – show introduced 2020-11-23 02:39 UTC by Report Bug Copy Issue Report The condition `$albumIds !== null` is always `true`. Loading history...
130			$sql .= 'AND `album_id` IN ' . $this->questionMarks(\count($albumIds));
131			$params = \array_merge($params, $albumIds);
132			}
133
134			$result = $this->execute($sql, $params);
135			$genres = [];
136			while ($row = $result->fetch()) {
137			$genre = new Genre();
138			$genre->setUserId($userId);
139			$genre->setId((int)$row['genre_id']);
140			$genre->setName($row['genre_name']);
141			$genres[$row['album_id']][] = $genre;
142			}
143			return $genres;
144			}
145
146			/**
147			* returns number of disks per album ID
148			*
149			* @param integer[]\|null $albumIds IDs of the albums; get all albums of the user if null given
150			* @param string $userId the user ID
151			* @return array int => int, keys are albums IDs and values are disk counts
152			*/
153			public function getDiscCountByAlbumId(?array $albumIds, string $userId) : array {
154			$sql = 'SELECT `album_id`, MAX(`disk`) AS `disc_count`
155			FROM `PREFIXmusic_tracks`
156			WHERE `user_id` = ?
157			GROUP BY `album_id` ';
158			$params = [$userId];
159
160			if ($albumIds !== null) {
			0 ignored issues – show introduced 2020-11-23 02:39 UTC by Report Bug Copy Issue Report The condition `$albumIds !== null` is always `true`. Loading history...
161			$sql .= 'HAVING `album_id` IN ' . $this->questionMarks(\count($albumIds));
162			$params = \array_merge($params, $albumIds);
163			}
164
165			$result = $this->execute($sql, $params);
166			$diskCountByAlbum = [];
167			while ($row = $result->fetch()) {
168			$diskCountByAlbum[$row['album_id']] = (int)$row['disc_count'];
169			}
170			return $diskCountByAlbum;
171			}
172
173			/**
174			* returns albums of a specified artist
175			* The artist may be an album_artist or the artist of a track
176			*
177			* @param integer $artistId ID of the artist
178			* @param string $userId the user ID
179			* @return Album[]
180			*/
181			public function findAllByArtist(int $artistId, string $userId) : array {
182			$sql = $this->selectEntities(
183			'`PREFIXmusic_albums`.`id` IN (
184			SELECT DISTINCT `album`.`id`
185			FROM `PREFIXmusic_albums` `album`
186			WHERE `album`.`album_artist_id` = ? AND `album`.`user_id` = ?
187			UNION
188			SELECT DISTINCT `track`.`album_id`
189			FROM `PREFIXmusic_tracks` `track`
190			WHERE `track`.`artist_id` = ? AND `track`.`user_id` = ?
191			)',
192			'ORDER BY LOWER(`PREFIXmusic_albums`.`name`)');
193			$params = [$artistId, $userId, $artistId, $userId];
194			return $this->findEntities($sql, $params);
195			}
196
197			/**
198			* returns albums of a specified artist
199			* The artist must album_artist on the album, artists of individual tracks are not considered
200			*
201			* @param integer $artistId ID of the artist
202			* @param string $userId the user ID
203			* @return Album[]
204			*/
205			public function findAllByAlbumArtist(int $artistId, string $userId) : array {
206			$sql = $this->selectUserEntities('`album_artist_id` = ?');
207			$params = [$userId, $artistId];
208			return $this->findEntities($sql, $params);
209			}
210
211			/**
212			* @return Album[]
213			*/
214			public function findAllByGenre(int $genreId, string $userId, int $limit=null, int $offset=null) : array {
215			$sql = $this->selectUserEntities('EXISTS '.
216			'(SELECT 1 FROM `PREFIXmusic_tracks` `track`
217			WHERE `PREFIXmusic_albums`.`id` = `track`.`album_id`
218			AND `track`.`genre_id` = ?)');
219
220			$params = [$userId, $genreId];
221			return $this->findEntities($sql, $params, $limit, $offset);
222			}
223
224			/**
225			* @return boolean True if one or more albums were influenced
226			*/
227			public function updateFolderCover(int $coverFileId, int $folderId) : bool {
228			$sql = 'SELECT DISTINCT `tracks`.`album_id`
229			FROM `PREFIXmusic_tracks` `tracks`
230			JOIN `PREFIXfilecache` `files` ON `tracks`.`file_id` = `files`.`fileid`
231			WHERE `files`.`parent` = ?';
232			$params = [$folderId];
233			$result = $this->execute($sql, $params);
234
235			$updated = false;
236			if ($result->rowCount()) {
237			$sql = 'UPDATE `PREFIXmusic_albums`
238			SET `cover_file_id` = ?
239			WHERE `cover_file_id` IS NULL AND `id` IN (?)';
240			$params = [$coverFileId, \join(",", $result->fetchAll(\PDO::FETCH_COLUMN))];
241			$result = $this->execute($sql, $params);
242			$updated = $result->rowCount() > 0;
243			}
244
245			return $updated;
246			}
247
248			/**
249			* Set file ID to be used as cover for an album
250			*/
251			public function setCover(?int $coverFileId, int $albumId) : void {
252			$sql = 'UPDATE `PREFIXmusic_albums`
253			SET `cover_file_id` = ?
254			WHERE `id` = ?';
255			$params = [$coverFileId, $albumId];
256			$this->execute($sql, $params);
257			}
258
259			/**
260			* @param integer[] $coverFileIds
261			* @param string[]\|null $userIds the users whose music library is targeted; all users are targeted if omitted
262			* @return Album[] albums which got modified (with incomplete data, only id and user are valid),
263			* empty array if none
264			*/
265			public function removeCovers(array $coverFileIds, array $userIds=null) : array {
266			// find albums using the given file as cover
267			$sql = 'SELECT `id`, `user_id` FROM `PREFIXmusic_albums` WHERE `cover_file_id` IN ' .
268			$this->questionMarks(\count($coverFileIds));
269			$params = $coverFileIds;
270			if ($userIds !== null) {
271			$sql .= ' AND `user_id` IN ' . $this->questionMarks(\count($userIds));
272			$params = \array_merge($params, $userIds);
273			}
274			$albums = $this->findEntities($sql, $params);
275
276			// if any albums found, remove the cover from those
277			$count = \count($albums);
278			if ($count) {
279			$sql = 'UPDATE `PREFIXmusic_albums`
280			SET `cover_file_id` = NULL
281			WHERE `id` IN ' . $this->questionMarks($count);
282			$params = Util::extractIds($albums);
283			$this->execute($sql, $params);
284			}
285
286			return $albums;
287			}
288
289			/**
290			* @param string\|null $userId target user; omit to target all users
291			* @return array of dictionaries with keys [albumId, userId, parentFolderId]
292			*/
293			public function getAlbumsWithoutCover(string $userId = null) : array {
294			$sql = 'SELECT DISTINCT `albums`.`id`, `albums`.`user_id`, `files`.`parent`
295			FROM `PREFIXmusic_albums` `albums`
296			JOIN `PREFIXmusic_tracks` `tracks` ON `albums`.`id` = `tracks`.`album_id`
297			JOIN `PREFIXfilecache` `files` ON `tracks`.`file_id` = `files`.`fileid`
298			WHERE `albums`.`cover_file_id` IS NULL';
299			$params = [];
300			if ($userId !== null) {
301			$sql .= ' AND `albums`.`user_id` = ?';
302			$params[] = $userId;
303			}
304			$result = $this->execute($sql, $params);
305			$return = [];
306			while ($row = $result->fetch()) {
307			$return[] = [
308			'albumId' => (int)$row['id'],
309			'userId' => $row['user_id'],
310			'parentFolderId' => (int)$row['parent']
311			];
312			}
313			return $return;
314			}
315
316			/**
317			* @return boolean True if a cover image was found and added for the album
318			*/
319			public function findAlbumCover(int $albumId, int $parentFolderId) : bool {
320			$return = false;
321			$imagesSql = 'SELECT `fileid`, `name`
322			FROM `PREFIXfilecache`
323			JOIN `PREFIXmimetypes` ON `PREFIXmimetypes`.`id` = `PREFIXfilecache`.`mimetype`
324			WHERE `parent` = ? AND `PREFIXmimetypes`.`mimetype` LIKE \'image%\'';
325			$params = [$parentFolderId];
326			$result = $this->execute($imagesSql, $params);
327			$images = $result->fetchAll();
328			if (\count($images) > 0) {
329			$getImageRank = function($imageName) {
330			$coverNames = ['cover', 'albumart', 'album', 'front', 'folder'];
331			foreach ($coverNames as $i => $coverName) {
332			if (Util::startsWith($imageName, $coverName, /$ignoreCase=/true)) {
333			return $i;
334			}
335			}
336			return \count($coverNames);
337			};
338
339			\usort($images, function ($imageA, $imageB) use ($getImageRank) {
340			return $getImageRank($imageA['name']) <=> $getImageRank($imageB['name']);
341			});
342			$imageId = (int)$images[0]['fileid'];
343			$this->setCover($imageId, $albumId);
344			$return = true;
345			}
346			return $return;
347			}
348
349			/**
350			* Given an array of track IDs, find corresponding uniqu album IDs, including only
351			* those album which have a cover art set.
352			* @param int[] $trackIds
353			* @return Album[]
354			*/
355			public function findAlbumsWithCoversForTracks(array $trackIds, string $userId, int $limit) : array {
356			$sql = 'SELECT DISTINCT `albums`.*
357			FROM `PREFIXmusic_albums` `albums`
358			JOIN `PREFIXmusic_tracks` `tracks` ON `albums`.`id` = `tracks`.`album_id`
359			WHERE `albums`.`cover_file_id` IS NOT NULL
360			AND `albums`.`user_id` = ?
361			AND `tracks`.`id` IN ' . $this->questionMarks(\count($trackIds));
362			$params = \array_merge([$userId], $trackIds);
363
364			return $this->findEntities($sql, $params, $limit);
365			}
366
367			/**
368			* Returns the count of albums where the given Artist is featured in
369			* @param integer $artistId
370			* @return integer
371			*/
372			public function countByArtist(int $artistId) : int {
373			$sql = 'SELECT COUNT(*) AS count FROM (
374			SELECT DISTINCT `track`.`album_id`
375			FROM `PREFIXmusic_tracks` `track`
376			WHERE `track`.`artist_id` = ?
377			UNION
378			SELECT `album`.`id`
379			FROM `PREFIXmusic_albums` `album`
380			WHERE `album`.`album_artist_id` = ?
381			) tmp';
382			$params = [$artistId, $artistId];
383			$result = $this->execute($sql, $params);
384			$row = $result->fetch();
385			return (int)$row['count'];
386			}
387
388			/**
389			* Returns the count of albums where the given artist is the album artist
390			* @param integer $artistId
391			* @return integer
392			*/
393			public function countByAlbumArtist(int $artistId) : int {
394			$sql = 'SELECT COUNT(*) AS count
395			FROM `PREFIXmusic_albums` `album`
396			WHERE `album`.`album_artist_id` = ?';
397			$params = [$artistId];
398			$result = $this->execute($sql, $params);
399			$row = $result->fetch();
400			return (int)$row['count'];
401			}
402
403			/**
404			* @see \OCA\Music\Db\BaseMapper::findUniqueEntity()
405			* @param Album $album
406			* @return Album
407			*/
408			protected function findUniqueEntity(Entity $album) : Entity {
409			$sql = $this->selectUserEntities('`PREFIXmusic_albums`.`hash` = ?');
410			return $this->findEntity($sql, [$album->getUserId(), $album->getHash()]);
411			}
412			}
413

owncloud / music

Push — master ( b806b4...5b46ab )

AlbumMapper::findAlbumsWithCoversForTracks() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like