Maintenance::removeStrayScanningStatus() - Code Metrics - Inspection of "Skip background cleanup while any scan job is runn..." - owncloud/music - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — master ( c32d01...1b0ec6 )

by Pauli

created 2024-11-28 20:36 UTC

Maintenance::removeStrayScanningStatus() A

↳ Parent: Maintenance

Complexity

Conditions	3
Paths	3

Size

Total Lines	20
Code Lines	13

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
cc	3
eloc	13
nc	3
nop	0
dl	0
loc	20
rs	9.8333
c	0
b	0
f	0

<?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 2014
 * @copyright Pauli Järvinen 2017 - 2024
 */

namespace OCA\Music\Db;

use OCP\IDBConnection;

use OCA\Music\AppFramework\Core\Logger;

class Maintenance {

	private IDBConnection $db;
	private Logger $logger;

	public function __construct(IDBConnection $db, Logger $logger) {
		$this->db = $db;
		$this->logger = $logger;
	}

	/**
	 * Remove 'scanning' flags with timestamp older than one minute. These have been probably left over
	 * when the scanning of some file has terminated unexpectedly.
	 */
	private function removeStrayScanningStatus() : int {
		$sql = 'SELECT `user_id`, `data` FROM `*PREFIX*music_cache`
				WHERE `key` = "scanning"';
		$result = $this->db->executeQuery($sql);
		$rows = $result->fetchAll();
		$result->closeCursor();

		$now = \time();
		$modRows = 0;
		foreach ($rows as $row) {
			$timestamp = (int)$row['data'];
			if ($now - $timestamp > 60) {
				$modRows += $this->db->executeUpdate(
					'DELETE FROM `*PREFIX*music_cache` WHERE `key` = "scanning" AND `user_id` = ?',
					[$row['user_id']]
				);
			}
		}
	
		return $modRows;
	}

	/**
	 * @return bool true if at least one user has an ongoing scanning job
	 */
	private function scanningInProgress() : bool {
		$sql = 'SELECT 1 FROM `*PREFIX*music_cache`	WHERE `key` = "scanning"';
		$result = $this->db->executeQuery($sql);
		return ($result->rowCount() > 0);
	}

	/**
	 * Remove cover_file_id from album if the corresponding file does not exist
	 */
	private function removeObsoleteCoverImagesFromTable(string $table) : int {
		return $this->db->executeUpdate(
			"UPDATE `*PREFIX*$table` SET `cover_file_id` = NULL
			WHERE `cover_file_id` IS NOT NULL AND `cover_file_id` IN (
				SELECT `cover_file_id` FROM (
					SELECT `cover_file_id` FROM `*PREFIX*$table`
					LEFT JOIN `*PREFIX*filecache`
						ON `cover_file_id`=`fileid`
					WHERE `fileid` IS NULL
				) mysqlhack
			)"
		);
	}

	/**
	 * Remove cover_file_id from album if the corresponding file does not exist
	 */
	private function removeObsoleteAlbumCoverImages() : int {
		return $this->removeObsoleteCoverImagesFromTable('music_albums');
	}

	/**
	 * Remove cover_file_id from artist if the corresponding file does not exist
	 */
	private function removeObsoleteArtistCoverImages() : int {
		return $this->removeObsoleteCoverImagesFromTable('music_artists');
	}

	/**
	 * Remove all such rows from $tgtTable which don't have corresponding rows in $refTable
	 * so that $tgtTableKey = $refTableKey.
	 * @param string $tgtTable
	 * @param string $refTable
	 * @param string $tgtTableKey
	 * @param string $refTableKey
	 * @param string|null $extraCond
	 * @return int Number of removed rows
	 */
	private function removeUnreferencedDbRows(string $tgtTable, string $refTable, string $tgtTableKey, string $refTableKey, ?string $extraCond=null) : int {
		$tgtTable = '*PREFIX*' . $tgtTable;
		$refTable = '*PREFIX*' . $refTable;

		return $this->db->executeUpdate(
			"DELETE FROM `$tgtTable` WHERE `id` IN (
				SELECT `id` FROM (
					SELECT `$tgtTable`.`id`
					FROM `$tgtTable` LEFT JOIN `$refTable`
					ON `$tgtTable`.`$tgtTableKey` = `$refTable`.`$refTableKey`
					WHERE `$refTable`.`$refTableKey` IS NULL
				) mysqlhack
			)"
			.
			(empty($extraCond) ? '' : " AND $extraCond")
		);
	}

	/**
	 * Remove tracks which do not have corresponding file in the file system
	 * @return int Number of removed tracks
	 */
	private function removeObsoleteTracks() : int {
		return $this->removeUnreferencedDbRows('music_tracks', 'filecache', 'file_id', 'fileid');
	}

	/**
	 * Remove tracks which belong to non-existing album
	 * @return int Number of removed tracks
	 */
	private function removeTracksWithNoAlbum() : int {
		return $this->removeUnreferencedDbRows('music_tracks', 'music_albums', 'album_id', 'id');
	}

	/**
	 * Remove tracks which are performed by non-existing artist
	 * @return int Number of removed tracks
	 */
	private function removeTracksWithNoArtist() : int {
		return $this->removeUnreferencedDbRows('music_tracks', 'music_artists', 'artist_id', 'id');
	}

	/**
	 * Remove albums which have no tracks
	 * @return int Number of removed albums
	 */
	private function removeObsoleteAlbums() : int {
		return $this->removeUnreferencedDbRows('music_albums', 'music_tracks', 'id', 'album_id');
	}

	/**
	 * Remove albums which have a non-existing album artist
	 * @return int Number of removed albums
	 */
	private function removeAlbumsWithNoArtist() : int {
		return $this->removeUnreferencedDbRows('music_albums', 'music_artists', 'album_artist_id', 'id');
	}

	/**
	 * Remove artists which have no albums and no tracks
	 * @return int Number of removed artists
	 */
	private function removeObsoleteArtists() : int {
		// Note: This originally used the NOT IN operation but that was terribly inefficient on PostgreSQL,
		// see https://github.com/owncloud/music/issues/997
		return $this->db->executeUpdate(
			'DELETE FROM `*PREFIX*music_artists`
				WHERE NOT EXISTS (SELECT 1 FROM `*PREFIX*music_albums` WHERE `*PREFIX*music_artists`.`id` = `album_artist_id` LIMIT 1)
				AND   NOT EXISTS (SELECT 1 FROM `*PREFIX*music_tracks` WHERE `*PREFIX*music_artists`.`id` = `artist_id` LIMIT 1)'
		);
	}

	/**
	 * Remove bookmarks referring tracks which do not exist
	 * @return int Number of removed bookmarks
	 */
	private function removeObsoleteBookmarks() : int {
		return $this->removeUnreferencedDbRows('music_bookmarks', 'music_tracks', 'entry_id', 'id', '`type` = 1')
			+ $this->removeUnreferencedDbRows('music_bookmarks', 'music_podcast_episodes', 'entry_id', 'id', '`type` = 2');
	}

	/**
	 * Remove podcast episodes which have a non-existing podcast channel
	 * @return int Number of removed albums
	 */
	private function removeObsoletePodcastEpisodes() : int {
		return $this->removeUnreferencedDbRows('music_podcast_episodes', 'music_podcast_channels', 'channel_id', 'id');
	}

	/**
	 * Removes orphaned data from the database
	 * @return array describing the number of removed entries per type
	 */
	public function cleanUp() : array {
		$removedScanFlags = $this->removeStrayScanningStatus();

		// Don't clean during an ongoing scan. This may cause the scanning to fail with a deadlock error on MariaDB,
		// see https://github.com/owncloud/music/issues/918. It could also remove a just scanned album row before the
		// contained track rows have been added to the DB, which would have happened a few milliseconds later.
		$skipDuringScan = $this->scanningInProgress();
		if (!$skipDuringScan) {
			$removedCovers = $this->removeObsoleteAlbumCoverImages();
			$removedCovers += $this->removeObsoleteArtistCoverImages();
	
			$removedTracks = $this->removeObsoleteTracks();
			$removedAlbums = $this->removeObsoleteAlbums();
			$removedArtists = $this->removeObsoleteArtists();
			$removedBookmarks = $this->removeObsoleteBookmarks();
			$removedEpisodes = $this->removeObsoletePodcastEpisodes();
	
			$removedAlbums += $this->removeAlbumsWithNoArtist();
			$removedTracks += $this->removeTracksWithNoAlbum();
			$removedTracks += $this->removeTracksWithNoArtist();
		}

		return [
			'scanFlags' => $removedScanFlags,
			'covers' => $removedCovers ?? 0,
			'artists' => $removedArtists ?? 0,
			'albums' => $removedAlbums ?? 0,
			'tracks' => $removedTracks ?? 0,
			'bookmarks' => $removedBookmarks ?? 0,
			'podcast_episodes' => $removedEpisodes ?? 0,
			'skipped_because_scan_in_progress' => $skipDuringScan
		];
	}

	/**
	 * Wipe clean the given table, either targeting a specific user all users
	 * @param string $table Name of the table, _excluding_ the prefix *PREFIX*music_
	 * @param ?string $userId
	 * @param bool $allUsers
	 * @throws \InvalidArgumentException
	 */
	private function resetTable(string $table, ?string $userId, bool $allUsers = false) : void {
		if ($userId && $allUsers) {
			throw new \InvalidArgumentException('userId should be null if allUsers targeted');
		}

		$params = [];
		$sql = "DELETE FROM `*PREFIX*music_$table`";
		if (!$allUsers) {
			$sql .=  ' WHERE `user_id` = ?';
			$params[] = $userId;
		}
		$this->db->executeUpdate($sql, $params);

	}

	/**
	 * Wipe clean the music library of the given user, or all users
	 */
	public function resetLibrary(?string $userId, bool $allUsers = false) : void {
		$tables = [
			'tracks',
			'albums',
			'artists',
			'playlists',
			'genres',
			'bookmarks',
			'cache'
		];

		foreach ($tables as $table) {
			$this->resetTable($table, $userId, $allUsers);
		}

		if ($allUsers) {
			$this->logger->log("Erased music databases of all users", 'info');
		} else {
			$this->logger->log("Erased music database of user $userId", 'info');
		}
	}

	/**
	 * Wipe clean all the music of the given user, including the library, podcasts, radio, Ampache/Subsonic keys
	 */
	public function resetAllData(string $userId) : void {
		$this->resetLibrary($userId);

		$tables = [
			'ampache_sessions',
			'ampache_users',
			'podcast_channels',
			'podcast_episodes',
			'radio_stations'
		];

		foreach ($tables as $table) {
			$this->resetTable($table, $userId);
		}
	}
}


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 2014
12			* @copyright Pauli Järvinen 2017 - 2024
13			*/
14
15			namespace OCA\Music\Db;
16
17			use OCP\IDBConnection;
18
19			use OCA\Music\AppFramework\Core\Logger;
20
21			class Maintenance {
22
23			private IDBConnection $db;
24			private Logger $logger;
25
26			public function __construct(IDBConnection $db, Logger $logger) {
27			$this->db = $db;
28			$this->logger = $logger;
29			}
30
31			/**
32			* Remove 'scanning' flags with timestamp older than one minute. These have been probably left over
33			* when the scanning of some file has terminated unexpectedly.
34			*/
35			private function removeStrayScanningStatus() : int {
36			$sql = 'SELECT `user_id`, `data` FROM `PREFIXmusic_cache`
37			WHERE `key` = "scanning"';
38			$result = $this->db->executeQuery($sql);
39			$rows = $result->fetchAll();
40			$result->closeCursor();
41
42			$now = \time();
43			$modRows = 0;
44			foreach ($rows as $row) {
45			$timestamp = (int)$row['data'];
46			if ($now - $timestamp > 60) {
47			$modRows += $this->db->executeUpdate(
48			'DELETE FROM `PREFIXmusic_cache` WHERE `key` = "scanning" AND `user_id` = ?',
49			[$row['user_id']]
50			);
51			}
52			}
53
54			return $modRows;
55			}
56
57			/**
58			* @return bool true if at least one user has an ongoing scanning job
59			*/
60			private function scanningInProgress() : bool {
61			$sql = 'SELECT 1 FROM `PREFIXmusic_cache` WHERE `key` = "scanning"';
62			$result = $this->db->executeQuery($sql);
63			return ($result->rowCount() > 0);
64			}
65
66			/**
67			* Remove cover_file_id from album if the corresponding file does not exist
68			*/
69			private function removeObsoleteCoverImagesFromTable(string $table) : int {
70			return $this->db->executeUpdate(
71			"UPDATE `PREFIX$table` SET `cover_file_id` = NULL
72			WHERE `cover_file_id` IS NOT NULL AND `cover_file_id` IN (
73			SELECT `cover_file_id` FROM (
74			SELECT `cover_file_id` FROM `PREFIX$table`
75			LEFT JOIN `PREFIXfilecache`
76			ON `cover_file_id`=`fileid`
77			WHERE `fileid` IS NULL
78			) mysqlhack
79			)"
80			);
81			}
82
83			/**
84			* Remove cover_file_id from album if the corresponding file does not exist
85			*/
86			private function removeObsoleteAlbumCoverImages() : int {
87			return $this->removeObsoleteCoverImagesFromTable('music_albums');
88			}
89
90			/**
91			* Remove cover_file_id from artist if the corresponding file does not exist
92			*/
93			private function removeObsoleteArtistCoverImages() : int {
94			return $this->removeObsoleteCoverImagesFromTable('music_artists');
95			}
96
97			/**
98			* Remove all such rows from $tgtTable which don't have corresponding rows in $refTable
99			* so that $tgtTableKey = $refTableKey.
100			* @param string $tgtTable
101			* @param string $refTable
102			* @param string $tgtTableKey
103			* @param string $refTableKey
104			* @param string\|null $extraCond
105			* @return int Number of removed rows
106			*/
107			private function removeUnreferencedDbRows(string $tgtTable, string $refTable, string $tgtTableKey, string $refTableKey, ?string $extraCond=null) : int {
108			$tgtTable = 'PREFIX' . $tgtTable;
109			$refTable = 'PREFIX' . $refTable;
110
111			return $this->db->executeUpdate(
112			"DELETE FROM `$tgtTable` WHERE `id` IN (
113			SELECT `id` FROM (
114			SELECT `$tgtTable`.`id`
115			FROM `$tgtTable` LEFT JOIN `$refTable`
116			ON `$tgtTable`.`$tgtTableKey` = `$refTable`.`$refTableKey`
117			WHERE `$refTable`.`$refTableKey` IS NULL
118			) mysqlhack
119			)"
120			.
121			(empty($extraCond) ? '' : " AND $extraCond")
122			);
123			}
124
125			/**
126			* Remove tracks which do not have corresponding file in the file system
127			* @return int Number of removed tracks
128			*/
129			private function removeObsoleteTracks() : int {
130			return $this->removeUnreferencedDbRows('music_tracks', 'filecache', 'file_id', 'fileid');
131			}
132
133			/**
134			* Remove tracks which belong to non-existing album
135			* @return int Number of removed tracks
136			*/
137			private function removeTracksWithNoAlbum() : int {
138			return $this->removeUnreferencedDbRows('music_tracks', 'music_albums', 'album_id', 'id');
139			}
140
141			/**
142			* Remove tracks which are performed by non-existing artist
143			* @return int Number of removed tracks
144			*/
145			private function removeTracksWithNoArtist() : int {
146			return $this->removeUnreferencedDbRows('music_tracks', 'music_artists', 'artist_id', 'id');
147			}
148
149			/**
150			* Remove albums which have no tracks
151			* @return int Number of removed albums
152			*/
153			private function removeObsoleteAlbums() : int {
154			return $this->removeUnreferencedDbRows('music_albums', 'music_tracks', 'id', 'album_id');
155			}
156
157			/**
158			* Remove albums which have a non-existing album artist
159			* @return int Number of removed albums
160			*/
161			private function removeAlbumsWithNoArtist() : int {
162			return $this->removeUnreferencedDbRows('music_albums', 'music_artists', 'album_artist_id', 'id');
163			}
164
165			/**
166			* Remove artists which have no albums and no tracks
167			* @return int Number of removed artists
168			*/
169			private function removeObsoleteArtists() : int {
170			// Note: This originally used the NOT IN operation but that was terribly inefficient on PostgreSQL,
171			// see https://github.com/owncloud/music/issues/997
172			return $this->db->executeUpdate(
173			'DELETE FROM `PREFIXmusic_artists`
174			WHERE NOT EXISTS (SELECT 1 FROM `PREFIXmusic_albums` WHERE `PREFIXmusic_artists`.`id` = `album_artist_id` LIMIT 1)
175			AND NOT EXISTS (SELECT 1 FROM `PREFIXmusic_tracks` WHERE `PREFIXmusic_artists`.`id` = `artist_id` LIMIT 1)'
176			);
177			}
178
179			/**
180			* Remove bookmarks referring tracks which do not exist
181			* @return int Number of removed bookmarks
182			*/
183			private function removeObsoleteBookmarks() : int {
184			return $this->removeUnreferencedDbRows('music_bookmarks', 'music_tracks', 'entry_id', 'id', '`type` = 1')
185			+ $this->removeUnreferencedDbRows('music_bookmarks', 'music_podcast_episodes', 'entry_id', 'id', '`type` = 2');
186			}
187
188			/**
189			* Remove podcast episodes which have a non-existing podcast channel
190			* @return int Number of removed albums
191			*/
192			private function removeObsoletePodcastEpisodes() : int {
193			return $this->removeUnreferencedDbRows('music_podcast_episodes', 'music_podcast_channels', 'channel_id', 'id');
194			}
195
196			/**
197			* Removes orphaned data from the database
198			* @return array describing the number of removed entries per type
199			*/
200			public function cleanUp() : array {
201			$removedScanFlags = $this->removeStrayScanningStatus();
202
203			// Don't clean during an ongoing scan. This may cause the scanning to fail with a deadlock error on MariaDB,
204			// see https://github.com/owncloud/music/issues/918. It could also remove a just scanned album row before the
205			// contained track rows have been added to the DB, which would have happened a few milliseconds later.
206			$skipDuringScan = $this->scanningInProgress();
207			if (!$skipDuringScan) {
208			$removedCovers = $this->removeObsoleteAlbumCoverImages();
209			$removedCovers += $this->removeObsoleteArtistCoverImages();
210
211			$removedTracks = $this->removeObsoleteTracks();
212			$removedAlbums = $this->removeObsoleteAlbums();
213			$removedArtists = $this->removeObsoleteArtists();
214			$removedBookmarks = $this->removeObsoleteBookmarks();
215			$removedEpisodes = $this->removeObsoletePodcastEpisodes();
216
217			$removedAlbums += $this->removeAlbumsWithNoArtist();
218			$removedTracks += $this->removeTracksWithNoAlbum();
219			$removedTracks += $this->removeTracksWithNoArtist();
220			}
221
222			return [
223			'scanFlags' => $removedScanFlags,
224			'covers' => $removedCovers ?? 0,
225			'artists' => $removedArtists ?? 0,
226			'albums' => $removedAlbums ?? 0,
227			'tracks' => $removedTracks ?? 0,
228			'bookmarks' => $removedBookmarks ?? 0,
229			'podcast_episodes' => $removedEpisodes ?? 0,
230			'skipped_because_scan_in_progress' => $skipDuringScan
231			];
232			}
233
234			/**
235			* Wipe clean the given table, either targeting a specific user all users
236			* @param string $table Name of the table, _excluding_ the prefix PREFIXmusic_
237			* @param ?string $userId
238			* @param bool $allUsers
239			* @throws \InvalidArgumentException
240			*/
241			private function resetTable(string $table, ?string $userId, bool $allUsers = false) : void {
242			if ($userId && $allUsers) {
243			throw new \InvalidArgumentException('userId should be null if allUsers targeted');
244			}
245
246			$params = [];
247			$sql = "DELETE FROM `PREFIXmusic_$table`";
248			if (!$allUsers) {
249			$sql .= ' WHERE `user_id` = ?';
250			$params[] = $userId;
251			}
252			$this->db->executeUpdate($sql, $params);
253
254			}
255
256			/**
257			* Wipe clean the music library of the given user, or all users
258			*/
259			public function resetLibrary(?string $userId, bool $allUsers = false) : void {
260			$tables = [
261			'tracks',
262			'albums',
263			'artists',
264			'playlists',
265			'genres',
266			'bookmarks',
267			'cache'
268			];
269
270			foreach ($tables as $table) {
271			$this->resetTable($table, $userId, $allUsers);
272			}
273
274			if ($allUsers) {
275			$this->logger->log("Erased music databases of all users", 'info');
276			} else {
277			$this->logger->log("Erased music database of user $userId", 'info');
278			}
279			}
280
281			/**
282			* Wipe clean all the music of the given user, including the library, podcasts, radio, Ampache/Subsonic keys
283			*/
284			public function resetAllData(string $userId) : void {
285			$this->resetLibrary($userId);
286
287			$tables = [
288			'ampache_sessions',
289			'ampache_users',
290			'podcast_channels',
291			'podcast_episodes',
292			'radio_stations'
293			];
294
295			foreach ($tables as $table) {
296			$this->resetTable($table, $userId);
297			}
298			}
299			}
300

owncloud / music

Push — master ( c32d01...1b0ec6 )

Maintenance::removeStrayScanningStatus() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like