TrackMapper::formatSortingClause() - Code Metrics - Inspection of "Ampache refactoring: Add method `toAmpacheApi` to..." - owncloud/music - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — master ( 4e74cd...0361f1 )

by Pauli

created 2021-08-24 20:35 UTC

TrackMapper::formatSortingClause() A

↳ Parent: TrackMapper

Complexity

Conditions	2
Paths	2

Size

Total Lines	5
Code Lines	4

Duplication

Lines	0
Ratio	0 %

Importance

Changes	1
Bugs	0	Features	0

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

<?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 \OCP\AppFramework\Db\Entity;
use \OCP\IDBConnection;

class TrackMapper extends BaseMapper {
	public function __construct(IDBConnection $db) {
		parent::__construct($db, 'music_tracks', '\OCA\Music\Db\Track', 'title');
	}

	/**
	 * 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_tracks`.*, `file`.`name` AS `filename`, `file`.`size`,
						`album`.`name` AS `album_name`, `artist`.`name` AS `artist_name`, `genre`.`name` AS `genre_name`
				FROM `*PREFIX*music_tracks`
				INNER JOIN `*PREFIX*filecache` `file`
				ON `*PREFIX*music_tracks`.`file_id` = `file`.`fileid`
				INNER JOIN `*PREFIX*music_albums` `album`
				ON `*PREFIX*music_tracks`.`album_id` = `album`.`id`
				INNER JOIN `*PREFIX*music_artists` `artist`
				ON `*PREFIX*music_tracks`.`artist_id` = `artist`.`id`
				LEFT JOIN `*PREFIX*music_genres` `genre`
				ON `*PREFIX*music_tracks`.`genre_id` = `genre`.`id`
				WHERE $condition $extension";
	}

	/**
	 * Overridden from the base implementation 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(`artist_name`), LOWER(`title`)';
		} else {
			return parent::formatSortingClause($sortBy);
		}
	}

	/**
	 * Returns all tracks of the given artist (both album and track artists are considered)
	 * @param integer $artistId
	 * @param string $userId
	 * @return Track[]
	 */
	public function findAllByArtist($artistId, $userId) {
		$sql = $this->selectUserEntities(
				'`artist_id` = ? OR `album_id` IN (SELECT `id` from `*PREFIX*music_albums` WHERE `album_artist_id` = ?) ',
				'ORDER BY LOWER(`title`)');
		$params = [$userId, $artistId, $artistId];
		return $this->findEntities($sql, $params);
	}

	/**
	 * @param integer $albumId
	 * @param string $userId
	 * @param integer|null $artistId
	 * @return Track[]
	 */
	public function findAllByAlbum($albumId, $userId, $artistId = null) {
		$condition = '`album_id` = ?';
		$params = [$userId, $albumId];

		if ($artistId !== null) {
			$condition .= ' AND `artist_id` = ? ';
			$params[] = $artistId;
		}

		$sql = $this->selectUserEntities($condition,
				'ORDER BY `*PREFIX*music_tracks`.`disk`, `number`, LOWER(`title`)');
		return $this->findEntities($sql, $params);
	}

	/**
	 * @param integer $folderId
	 * @param string $userId
	 * @return Track[]
	 */
	public function findAllByFolder($folderId, $userId) {
		$sql = $this->selectUserEntities('`file`.`parent` = ?', 'ORDER BY LOWER(`title`)');
		$params = [$userId, $folderId];
		return $this->findEntities($sql, $params);
	}

	/**
	 * @param int $genreId
	 * @param string $userId
	 * @param int|null $limit
	 * @param int|null $offset
	 * @return Track[] tracks
	 */
	public function findAllByGenre($genreId, $userId, $limit=null, $offset=null) {
		$sql = $this->selectUserEntities('`genre_id` = ?', 'ORDER BY LOWER(`title`)');
		$params = [$userId, $genreId];
		return $this->findEntities($sql, $params, $limit, $offset);
	}

	/**
	 * @param string $userId
	 * @return int[]
	 */
	public function findAllFileIds($userId) {
		$sql = 'SELECT `file_id` FROM `*PREFIX*music_tracks` WHERE `user_id` = ?';
		$result = $this->execute($sql, [$userId]);

		return \array_map(function ($i) {
			return (int)$i['file_id'];
		}, $result->fetchAll());
	}

	/**
	 * Find a track of user matching a file ID
	 * @param integer $fileId
	 * @param string $userId
	 * @return Track
	 * @throws \OCP\AppFramework\Db\DoesNotExistException if not found
	 */
	public function findByFileId($fileId, $userId) {
		$sql = $this->selectUserEntities('`file_id` = ?');
		$params = [$userId, $fileId];
		return $this->findEntity($sql, $params);
	}

	/**
	 * Find tracks of user with multiple file IDs
	 * @param integer[] $fileIds
	 * @param string[] $userIds
	 * @return Track[]
	 */
	public function findByFileIds($fileIds, $userIds) {
		$sql = $this->selectEntities(
				'`*PREFIX*music_tracks`.`user_id` IN ' . $this->questionMarks(\count($userIds)) .
				' AND `file_id` IN '. $this->questionMarks(\count($fileIds)));
		$params = \array_merge($userIds, $fileIds);
		return $this->findEntities($sql, $params);
	}

	/**
	 * Finds tracks of all users matching one or multiple file IDs
	 * @param integer[] $fileIds
	 * @return Track[]
	 */
	public function findAllByFileIds($fileIds) {
		$sql = $this->selectEntities('`file_id` IN '.
				$this->questionMarks(\count($fileIds)));
		return $this->findEntities($sql, $fileIds);
	}

	/**
	 * @param integer $artistId
	 * @return integer
	 */
	public function countByArtist($artistId) {
		$sql = 'SELECT COUNT(*) AS `count` FROM `*PREFIX*music_tracks` WHERE `artist_id` = ?';
		$result = $this->execute($sql, [$artistId]);
		$row = $result->fetch();
		return (int)$row['count'];
	}

	/**
	 * @param integer $albumId
	 * @return integer
	 */
	public function countByAlbum($albumId) {
		$sql = 'SELECT COUNT(*) AS `count` FROM `*PREFIX*music_tracks` WHERE `album_id` = ?';
		$result = $this->execute($sql, [$albumId]);
		$row = $result->fetch();
		return (int)$row['count'];
	}

	/**
	 * @param integer $albumId
	 * @return integer Duration in seconds
	 */
	public function totalDurationOfAlbum($albumId) {
		$sql = 'SELECT SUM(`length`) AS `duration` FROM `*PREFIX*music_tracks` WHERE `album_id` = ?';
		$result = $this->execute($sql, [$albumId]);
		$row = $result->fetch();
		return (int)$row['duration'];
	}

	/**
	 * Get durations of the given tracks.
	 * @param integer[] $trackIds
	 * @return array {int => int} where keys are track IDs and values are corresponding durations
	 */
	public function getDurations($trackIds) {
		$result = [];

		if (!empty($trackIds)) {
			$sql = 'SELECT `id`, `length` FROM `*PREFIX*music_tracks` WHERE `id` IN ' .
						$this->questionMarks(\count($trackIds));
			$rows = $this->execute($sql, $trackIds)->fetchAll();
			foreach ($rows as $row) {
				$result[$row['id']] = (int)$row['length'];
			}
		}
		return $result;
	}

	/**
	 * @param string $name
	 * @param string $userId
	 * @return Track[]
	 */
	public function findAllByNameRecursive($name, $userId) {
		$condition = '( LOWER(`artist`.`name`) LIKE LOWER(?) OR
						LOWER(`album`.`name`) LIKE LOWER(?) OR
						LOWER(`title`) LIKE LOWER(?) )';
		$sql = $this->selectUserEntities($condition, 'ORDER BY LOWER(`title`)');
		$name = '%' . $name . '%';
		$params = [$userId, $name, $name, $name];
		return $this->findEntities($sql, $params);
	}

	/**
	 * Returns all tracks specified by name and/or artist name
	 * @param string|null $name the name of the track
	 * @param string|null $artistName the name of the artist
	 * @param bool $fuzzy match names using case-insensitive substring search
	 * @param string $userId the name of the user
	 * @return \OCA\Music\Db\Track[] Tracks matching the criteria
	 */
	public function findAllByNameAndArtistName($name, $artistName, $fuzzy, $userId) {
		$sqlConditions = [];
		$params = [$userId];

		if (!empty($name)) {
			if ($fuzzy) {
				$sqlConditions[] = 'LOWER(`title`) LIKE LOWER(?)';
				$params[] = "%$name%";
			} else {
				$sqlConditions[] = '`title` = ?';
				$params[] = $name;
			}
		}

		if (!empty($artistName)) {
			if ($fuzzy) {
				$sqlConditions[] = 'LOWER(`artist`.`name`) LIKE LOWER(?)';
				$params[] = "%$artistName%";
			} else {
				$sqlConditions[] = '`artist`.`name` = ?';
				$params[] = $artistName;
			}
		}

		// at least one condition has to be given, otherwise return an empty set
		if (\count($sqlConditions) > 0) {
			$sql = $this->selectUserEntities(\implode(' AND ', $sqlConditions));
			return $this->findEntities($sql, $params);
		} else {
			return [];
		}
	}

	/**
	 * Finds all track IDs of the user along with the parent folder ID of each track
	 * @param string $userId
	 * @return array where keys are folder IDs and values are arrays of track IDs
	 */
	public function findTrackAndFolderIds($userId) {
		$sql = 'SELECT `track`.`id` AS id, `file`.`name` AS `filename`, `file`.`parent` AS parent
				FROM `*PREFIX*music_tracks` `track`
				JOIN `*PREFIX*filecache` `file`
				ON `track`.`file_id` = `file`.`fileid`
				WHERE `track`.`user_id` = ?';

		$rows = $this->execute($sql, [$userId])->fetchAll();

		// Sort the results according the file names. This can't be made using ORDERBY in the
		// SQL query because then we couldn't use the "natural order" comparison algorithm
		\usort($rows, function ($a, $b) {
			return \strnatcasecmp($a['filename'], $b['filename']);
		});

		// group the files to parent folder "buckets"
		$result = [];
		foreach ($rows as $row) {
			$result[(int)$row['parent']][] = (int)$row['id'];
		}

		return $result;
	}

	/**
	 * Find names and paths of the file system nodes with given IDs within the given storage
	 * @param int[] $nodeIds
	 * @param string $storageId
	 * @return array where keys are the node IDs and values are associative arrays
	 *         like { 'name' => string, 'path' => string };
	 */
	public function findNodeNamesAndPaths($nodeIds, $storageId) {
		$result = [];

		if (!empty($nodeIds)) {
			$sql = 'SELECT `fileid`, `name`, `path` '.
					'FROM `*PREFIX*filecache` `filecache` '.
					'JOIN `*PREFIX*storages` `storages` '.
					'ON `filecache`.`storage` = `storages`.`numeric_id` '.
					'WHERE `storages`.`id` = ? '.
					'AND `filecache`.`fileid` IN '. $this->questionMarks(\count($nodeIds));

			$rows = $this->execute($sql, \array_merge([$storageId], $nodeIds))->fetchAll();

			foreach ($rows as $row) {
				$result[$row['fileid']] = [
					'name' => $row['name'],
					'path' => $row['path']
				];
			}
		}

		return $result;
	}

	/**
	 * Returns all genre IDs associated with the given artist
	 * @param int $artistId
	 * @param string $userId
	 * @return int[]
	 */
	public function getGenresByArtistId($artistId, $userId) {
		$sql = 'SELECT DISTINCT(`genre_id`) FROM `*PREFIX*music_tracks` WHERE
				`genre_id` IS NOT NULL AND `user_id` = ? AND `artist_id` = ?';
		$rows = $this->execute($sql, [$userId, $artistId]);
		return $rows->fetchAll(\PDO::FETCH_COLUMN);
	}

	/**
	 * Returns all tracks IDs of the user, organized by the genre_id.
	 * @param string $userId
	 * @return array where keys are genre IDs and values are arrays of track IDs
	 */
	public function mapGenreIdsToTrackIds($userId) {
		$sql = 'SELECT `id`, `genre_id` FROM `*PREFIX*music_tracks`
				WHERE `genre_id` IS NOT NULL and `user_id` = ?';
		$rows = $this->execute($sql, [$userId])->fetchAll();

		$result = [];
		foreach ($rows as $row) {
			$result[(int)$row['genre_id']][] = (int)$row['id'];
		}

		return $result;
	}

	/**
	 * Returns file IDs of the tracks which do not have genre scanned. This is not the same
	 * thing as unknown genre, which means that the genre has been scanned but was not found
	 * from the track metadata.
	 * @param string $userId
	 * @return int[]
	 */
	public function findFilesWithoutScannedGenre($userId) {
		$sql = 'SELECT `track`.`file_id` FROM `*PREFIX*music_tracks` `track`
				INNER JOIN `*PREFIX*filecache` `file`
				ON `track`.`file_id` = `file`.`fileid`
				WHERE `genre_id` IS NULL and `user_id` = ?';
		$rows = $this->execute($sql, [$userId]);
		return $rows->fetchAll(\PDO::FETCH_COLUMN);
	}

	/**
	 * @see \OCA\Music\Db\BaseMapper::findUniqueEntity()
	 * @param Track $track
	 * @return Track
	 */
	protected function findUniqueEntity(Entity $track) : Entity {
		return $this->findByFileId($track->getFileId(), $track->getUserId());
	}
}


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 \OCP\AppFramework\Db\Entity;
18			use \OCP\IDBConnection;
19
20			class TrackMapper extends BaseMapper {
21			public function __construct(IDBConnection $db) {
22			parent::__construct($db, 'music_tracks', '\OCA\Music\Db\Track', 'title');
23			}
24
25			/**
26			* Override the base implementation to include data from multiple tables
27			*
28			* {@inheritdoc}
29			* @see BaseMapper::selectEntities()
30			*/
31			protected function selectEntities(string $condition, string $extension=null) : string {
32			return "SELECT `PREFIXmusic_tracks`.*, `file`.`name` AS `filename`, `file`.`size`,
33			`album`.`name` AS `album_name`, `artist`.`name` AS `artist_name`, `genre`.`name` AS `genre_name`
34			FROM `PREFIXmusic_tracks`
35			INNER JOIN `PREFIXfilecache` `file`
36			ON `PREFIXmusic_tracks`.`file_id` = `file`.`fileid`
37			INNER JOIN `PREFIXmusic_albums` `album`
38			ON `PREFIXmusic_tracks`.`album_id` = `album`.`id`
39			INNER JOIN `PREFIXmusic_artists` `artist`
40			ON `PREFIXmusic_tracks`.`artist_id` = `artist`.`id`
41			LEFT JOIN `PREFIXmusic_genres` `genre`
42			ON `PREFIXmusic_tracks`.`genre_id` = `genre`.`id`
43			WHERE $condition $extension";
44			}
45
46			/**
47			* Overridden from the base implementation to add support for sorting by artist.
48			*
49			* {@inheritdoc}
50			* @see BaseMapper::formatSortingClause()
51			*/
52			protected function formatSortingClause(int $sortBy) : ?string {
53			if ($sortBy === SortBy::Parent) {
54			return 'ORDER BY LOWER(`artist_name`), LOWER(`title`)';
55			} else {
56			return parent::formatSortingClause($sortBy);
57			}
58			}
59
60			/**
61			* Returns all tracks of the given artist (both album and track artists are considered)
62			* @param integer $artistId
63			* @param string $userId
64			* @return Track[]
65			*/
66			public function findAllByArtist($artistId, $userId) {
67			$sql = $this->selectUserEntities(
68			'`artist_id` = ? OR `album_id` IN (SELECT `id` from `PREFIXmusic_albums` WHERE `album_artist_id` = ?) ',
69			'ORDER BY LOWER(`title`)');
70			$params = [$userId, $artistId, $artistId];
71			return $this->findEntities($sql, $params);
72			}
73
74			/**
75			* @param integer $albumId
76			* @param string $userId
77			* @param integer\|null $artistId
78			* @return Track[]
79			*/
80			public function findAllByAlbum($albumId, $userId, $artistId = null) {
81			$condition = '`album_id` = ?';
82			$params = [$userId, $albumId];
83
84			if ($artistId !== null) {
85			$condition .= ' AND `artist_id` = ? ';
86			$params[] = $artistId;
87			}
88
89			$sql = $this->selectUserEntities($condition,
90			'ORDER BY `PREFIXmusic_tracks`.`disk`, `number`, LOWER(`title`)');
91			return $this->findEntities($sql, $params);
92			}
93
94			/**
95			* @param integer $folderId
96			* @param string $userId
97			* @return Track[]
98			*/
99			public function findAllByFolder($folderId, $userId) {
100			$sql = $this->selectUserEntities('`file`.`parent` = ?', 'ORDER BY LOWER(`title`)');
101			$params = [$userId, $folderId];
102			return $this->findEntities($sql, $params);
103			}
104
105			/**
106			* @param int $genreId
107			* @param string $userId
108			* @param int\|null $limit
109			* @param int\|null $offset
110			* @return Track[] tracks
111			*/
112			public function findAllByGenre($genreId, $userId, $limit=null, $offset=null) {
113			$sql = $this->selectUserEntities('`genre_id` = ?', 'ORDER BY LOWER(`title`)');
114			$params = [$userId, $genreId];
115			return $this->findEntities($sql, $params, $limit, $offset);
116			}
117
118			/**
119			* @param string $userId
120			* @return int[]
121			*/
122			public function findAllFileIds($userId) {
123			$sql = 'SELECT `file_id` FROM `PREFIXmusic_tracks` WHERE `user_id` = ?';
124			$result = $this->execute($sql, [$userId]);
125
126			return \array_map(function ($i) {
127			return (int)$i['file_id'];
128			}, $result->fetchAll());
129			}
130
131			/**
132			* Find a track of user matching a file ID
133			* @param integer $fileId
134			* @param string $userId
135			* @return Track
136			* @throws \OCP\AppFramework\Db\DoesNotExistException if not found
137			*/
138			public function findByFileId($fileId, $userId) {
139			$sql = $this->selectUserEntities('`file_id` = ?');
140			$params = [$userId, $fileId];
141			return $this->findEntity($sql, $params);
142			}
143
144			/**
145			* Find tracks of user with multiple file IDs
146			* @param integer[] $fileIds
147			* @param string[] $userIds
148			* @return Track[]
149			*/
150			public function findByFileIds($fileIds, $userIds) {
151			$sql = $this->selectEntities(
152			'`PREFIXmusic_tracks`.`user_id` IN ' . $this->questionMarks(\count($userIds)) .
153			' AND `file_id` IN '. $this->questionMarks(\count($fileIds)));
154			$params = \array_merge($userIds, $fileIds);
155			return $this->findEntities($sql, $params);
156			}
157
158			/**
159			* Finds tracks of all users matching one or multiple file IDs
160			* @param integer[] $fileIds
161			* @return Track[]
162			*/
163			public function findAllByFileIds($fileIds) {
164			$sql = $this->selectEntities('`file_id` IN '.
165			$this->questionMarks(\count($fileIds)));
166			return $this->findEntities($sql, $fileIds);
167			}
168
169			/**
170			* @param integer $artistId
171			* @return integer
172			*/
173			public function countByArtist($artistId) {
174			$sql = 'SELECT COUNT() AS `count` FROM `PREFIX*music_tracks` WHERE `artist_id` = ?';
175			$result = $this->execute($sql, [$artistId]);
176			$row = $result->fetch();
177			return (int)$row['count'];
178			}
179
180			/**
181			* @param integer $albumId
182			* @return integer
183			*/
184			public function countByAlbum($albumId) {
185			$sql = 'SELECT COUNT() AS `count` FROM `PREFIX*music_tracks` WHERE `album_id` = ?';
186			$result = $this->execute($sql, [$albumId]);
187			$row = $result->fetch();
188			return (int)$row['count'];
189			}
190
191			/**
192			* @param integer $albumId
193			* @return integer Duration in seconds
194			*/
195			public function totalDurationOfAlbum($albumId) {
196			$sql = 'SELECT SUM(`length`) AS `duration` FROM `PREFIXmusic_tracks` WHERE `album_id` = ?';
197			$result = $this->execute($sql, [$albumId]);
198			$row = $result->fetch();
199			return (int)$row['duration'];
200			}
201
202			/**
203			* Get durations of the given tracks.
204			* @param integer[] $trackIds
205			* @return array {int => int} where keys are track IDs and values are corresponding durations
206			*/
207			public function getDurations($trackIds) {
208			$result = [];
209
210			if (!empty($trackIds)) {
211			$sql = 'SELECT `id`, `length` FROM `PREFIXmusic_tracks` WHERE `id` IN ' .
212			$this->questionMarks(\count($trackIds));
213			$rows = $this->execute($sql, $trackIds)->fetchAll();
214			foreach ($rows as $row) {
215			$result[$row['id']] = (int)$row['length'];
216			}
217			}
218			return $result;
219			}
220
221			/**
222			* @param string $name
223			* @param string $userId
224			* @return Track[]
225			*/
226			public function findAllByNameRecursive($name, $userId) {
227			$condition = '( LOWER(`artist`.`name`) LIKE LOWER(?) OR
228			LOWER(`album`.`name`) LIKE LOWER(?) OR
229			LOWER(`title`) LIKE LOWER(?) )';
230			$sql = $this->selectUserEntities($condition, 'ORDER BY LOWER(`title`)');
231			$name = '%' . $name . '%';
232			$params = [$userId, $name, $name, $name];
233			return $this->findEntities($sql, $params);
234			}
235
236			/**
237			* Returns all tracks specified by name and/or artist name
238			* @param string\|null $name the name of the track
239			* @param string\|null $artistName the name of the artist
240			* @param bool $fuzzy match names using case-insensitive substring search
241			* @param string $userId the name of the user
242			* @return \OCA\Music\Db\Track[] Tracks matching the criteria
243			*/
244			public function findAllByNameAndArtistName($name, $artistName, $fuzzy, $userId) {
245			$sqlConditions = [];
246			$params = [$userId];
247
248			if (!empty($name)) {
249			if ($fuzzy) {
250			$sqlConditions[] = 'LOWER(`title`) LIKE LOWER(?)';
251			$params[] = "%$name%";
252			} else {
253			$sqlConditions[] = '`title` = ?';
254			$params[] = $name;
255			}
256			}
257
258			if (!empty($artistName)) {
259			if ($fuzzy) {
260			$sqlConditions[] = 'LOWER(`artist`.`name`) LIKE LOWER(?)';
261			$params[] = "%$artistName%";
262			} else {
263			$sqlConditions[] = '`artist`.`name` = ?';
264			$params[] = $artistName;
265			}
266			}
267
268			// at least one condition has to be given, otherwise return an empty set
269			if (\count($sqlConditions) > 0) {
270			$sql = $this->selectUserEntities(\implode(' AND ', $sqlConditions));
271			return $this->findEntities($sql, $params);
272			} else {
273			return [];
274			}
275			}
276
277			/**
278			* Finds all track IDs of the user along with the parent folder ID of each track
279			* @param string $userId
280			* @return array where keys are folder IDs and values are arrays of track IDs
281			*/
282			public function findTrackAndFolderIds($userId) {
283			$sql = 'SELECT `track`.`id` AS id, `file`.`name` AS `filename`, `file`.`parent` AS parent
284			FROM `PREFIXmusic_tracks` `track`
285			JOIN `PREFIXfilecache` `file`
286			ON `track`.`file_id` = `file`.`fileid`
287			WHERE `track`.`user_id` = ?';
288
289			$rows = $this->execute($sql, [$userId])->fetchAll();
290
291			// Sort the results according the file names. This can't be made using ORDERBY in the
292			// SQL query because then we couldn't use the "natural order" comparison algorithm
293			\usort($rows, function ($a, $b) {
294			return \strnatcasecmp($a['filename'], $b['filename']);
295			});
296
297			// group the files to parent folder "buckets"
298			$result = [];
299			foreach ($rows as $row) {
300			$result[(int)$row['parent']][] = (int)$row['id'];
301			}
302
303			return $result;
304			}
305
306			/**
307			* Find names and paths of the file system nodes with given IDs within the given storage
308			* @param int[] $nodeIds
309			* @param string $storageId
310			* @return array where keys are the node IDs and values are associative arrays
311			* like { 'name' => string, 'path' => string };
312			*/
313			public function findNodeNamesAndPaths($nodeIds, $storageId) {
314			$result = [];
315
316			if (!empty($nodeIds)) {
317			$sql = 'SELECT `fileid`, `name`, `path` '.
318			'FROM `PREFIXfilecache` `filecache` '.
319			'JOIN `PREFIXstorages` `storages` '.
320			'ON `filecache`.`storage` = `storages`.`numeric_id` '.
321			'WHERE `storages`.`id` = ? '.
322			'AND `filecache`.`fileid` IN '. $this->questionMarks(\count($nodeIds));
323
324			$rows = $this->execute($sql, \array_merge([$storageId], $nodeIds))->fetchAll();
325
326			foreach ($rows as $row) {
327			$result[$row['fileid']] = [
328			'name' => $row['name'],
329			'path' => $row['path']
330			];
331			}
332			}
333
334			return $result;
335			}
336
337			/**
338			* Returns all genre IDs associated with the given artist
339			* @param int $artistId
340			* @param string $userId
341			* @return int[]
342			*/
343			public function getGenresByArtistId($artistId, $userId) {
344			$sql = 'SELECT DISTINCT(`genre_id`) FROM `PREFIXmusic_tracks` WHERE
345			`genre_id` IS NOT NULL AND `user_id` = ? AND `artist_id` = ?';
346			$rows = $this->execute($sql, [$userId, $artistId]);
347			return $rows->fetchAll(\PDO::FETCH_COLUMN);
348			}
349
350			/**
351			* Returns all tracks IDs of the user, organized by the genre_id.
352			* @param string $userId
353			* @return array where keys are genre IDs and values are arrays of track IDs
354			*/
355			public function mapGenreIdsToTrackIds($userId) {
356			$sql = 'SELECT `id`, `genre_id` FROM `PREFIXmusic_tracks`
357			WHERE `genre_id` IS NOT NULL and `user_id` = ?';
358			$rows = $this->execute($sql, [$userId])->fetchAll();
359
360			$result = [];
361			foreach ($rows as $row) {
362			$result[(int)$row['genre_id']][] = (int)$row['id'];
363			}
364
365			return $result;
366			}
367
368			/**
369			* Returns file IDs of the tracks which do not have genre scanned. This is not the same
370			* thing as unknown genre, which means that the genre has been scanned but was not found
371			* from the track metadata.
372			* @param string $userId
373			* @return int[]
374			*/
375			public function findFilesWithoutScannedGenre($userId) {
376			$sql = 'SELECT `track`.`file_id` FROM `PREFIXmusic_tracks` `track`
377			INNER JOIN `PREFIXfilecache` `file`
378			ON `track`.`file_id` = `file`.`fileid`
379			WHERE `genre_id` IS NULL and `user_id` = ?';
380			$rows = $this->execute($sql, [$userId]);
381			return $rows->fetchAll(\PDO::FETCH_COLUMN);
382			}
383
384			/**
385			* @see \OCA\Music\Db\BaseMapper::findUniqueEntity()
386			* @param Track $track
387			* @return Track
388			*/
389			protected function findUniqueEntity(Entity $track) : Entity {
390			return $this->findByFileId($track->getFileId(), $track->getUserId());
391			}
392			}
393

owncloud / music

Push — master ( 4e74cd...0361f1 )

TrackMapper::formatSortingClause() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like